MySQL基本知识

MySQL

中英对照

auto_increment 自动增长

primary key 主键

comment 注释

id 字段

default默认值

DDL

data definition language:create , alter, drop,show

DML

data manipulate language:insert, update, delete,select

DCL

data control language

数据类型

1. 整数型 tinyint smallint int bigint
2. 浮点型 float（M，N）double 备注：M必须大于N
3. 定点型 decimal 备注：无精度损失
4. 枚举型 enum
5. 创建数据库，指定字符编码

create database school charset=gbk;

##表的操作

###创建一个表

create table if not exists student( 
id int auto_increment primary key comment '主键id',
name varchar(30) not null comment '姓名',
phone varchar(20) comment '电话',
address varchar(100) default '暂时未知' comment '住址'
)engine=innodb;

显示表

所有表

show tables

表结构

desc teacher;

show create table teacher详细看表

删除一个表

drop table if exists teacher;

修改表

修改表名

alter table table_name rename new_table_name;

字段的操作

####添加字段

alter table table_name add 字段名 数据类型 约束 after 某字段;

删除字段

alter table table_name drop 字段名;

修改字段

修改字段名

alter table table_name change 原字段名 新字段名 数据类型 约束条件

修改字段属性

alter table table_name modify 字段名 字段类型 约束条件(如果想要保存原有的约束条件，就必须加上；否则修改属性后会丢失)

-- 将学生姓名从varchar(20)修改为varchar(40)
alter table student modify sname varchar(40) not null;

数据的操作

在表中插入数据

insert into <表名> values(),(),(),();

//这里是默认按照其字段的顺序来进行的

###查看表中的数据

select * from teacher;

这里的*号是指所有的数据

select 字段1，字段2 from teacher;

删除表中的数据

delete from teacher where id =1(条件);

清空表

truncate table teacher;

更新数据

update table teacher set name = ‘frank’ where id = 1;

主键

不能为NULL

查询主键的好处：保证数据的完整性，加快查询数据的速度

记录数据的唯一性

被其他表应用

唯一键 unique

它不是用来区分数据

唯一键可以为空

一个表里能够有多个唯一键

保证数据不能重复

不能被其他表引用

外键

约束的操作

添加主键

alter table 表名 add primary key(字段1…);

删除主键

alter table 表名 drop primary key;

添加默认值

alter table table_name alter 列名 set default ‘值’

删除默认值

alter table table_name alter 列名 drop default

添加唯一键

alter table 表名 modify 字段名 数据类型 unique

添加外键

alter table 表名 add foreign key(外键名) references 所引用字段的表名(字段名)

on update set null(置空) on delete set cascade(级联);

删除外键

外键在SQL语句中

alter table 表名 drop foriegn key 外键字段名;

SQL语句注释

单行注释：#

多行注释：/**/

数据冗余

解决并发

缺点：数据过多不利于维护

Codd范式

保持每列原子性

错误范例

上课时间段
2018-2019

正确范例

开始时间	结束时间
2018	2019

非键字段必须依赖于键字段

减少传递依赖

不必教条地遵守范式，应根据项目需求来设计数据库

确定还应判断是否已经添加过

单表查询

select

1、使用select进行计算，表达式作为字段名，结果作为值

2、select 原来的字段名 as 别名

  ```mysql

select S_name as ‘姓名’ from student;
```

from

返回多表的笛卡尔积

select * from student, course;

dual

默认的尾表

如果使用select关键字时没有使用from 默认会从dual中查

select 2*7 as res;

where

逻辑词： and or not != =

条件查询

in

确定是否在集合中

select * from student where sex in ('男','女')

between and

简化 大于和小于的条件查询

注：between and 是闭区间

is null

查看 某个字段是否为空

聚合函数

count(*)不常用	统计元组个数
count(列名)	统计一列中值的个数
sum(列名)	统计一列值的总和
avg(列名)	统计一列值的平均值
max(列名)	统计一列中的最大值
min(列名)	统计一列中的最小值

like

模糊查询

通配符

%代表任意长度的字符串

_代表任意单个字符

select * from student where S_name like '张%';

order by

asc升序：从小到大

desc降序：从大到小

group by

Sno	Sname	Ssex	Sage	Sdept
1	张三	男	19	软件工程
2	李四	女	20	计算机科学
4	张五	男	20	网络工程
5	张一	女	18	大数据
7	王丽二	女	20	地理信息科学
9	张七七	女	23	软件工程

分别求男生和女生的平均年龄

select avg(Sage),Ssex from student group by Ssex;

年龄	性别
19.5000	男
20.2500	女

group_concat

将满足条件的结果聚合到一个单元格中

having

作用于查询过后生成的表，从表中选择满足条件的组

select avg(Sage),Ssex from student group by Ssex having avg(Sage) > 100;

limit

默认从0开始

select * from student limit 1,3;

distinct

取消列中的重复值

多表查询

union

union操作符用于连接两个以上的 select 语句的结果组合到一个结果集合中。多个select 语句会删除重复的数据。

注意查询的字段个数要一致

inner join

内连接：需要有公共字段。

select 字段1… from 表1 inner join 表2名 on 表1.公共字段 = 表2.公共字段

方法2：

select 字段1… from 表1，表2 where 表1.公共字段 = 表2.公共字段

left join

左连接：左表查询结果为空依然保存

right join

右连接：右表查询结果为空依然保存

cross join

返回两个表的笛卡尔积，保留了公共字段

natural join

公共字段必须保证字段名一致

如果无公共同名字段。则返回表的笛卡尔积

缺点：不容易获取表的结构

using

如果碰到全为同名公共字段的情况，则使用using选择一个主字段进行连接

子查询

缺点：效率较低

从第一个表查到另一个表，把第一个表作为子查询语句(子查询语句可以看作一个set)

不相关子查询

子查询不依赖父查询

in

查询分数大于90的学生的姓名

select S_name from student where S_no in(select S_no from choice where score > 86);

相关子查询

exists

只要子查询语句成立，那么父查询就执行

视图

通过查询生成子表，隐藏父表中的重要数据。保证了数据库的安全

生成视图

create view 视图名 as 查询语句

更新视图

alter view 视图名 as 查询语句

删除视图

drop view 视图名

事务

四大特性

atomicity

consistency

isolation

durability