数据库增删查改进阶

数据库约束
null约束
创建表时，可以指定某列不为空：
id int not null,

unique：唯一约束
指定sn列为唯一的、不重复的：
sn int unique,

default：默认值约束
–指定插入数据时，name列为空，默认值unkown：
name varchar(20) default ‘unkown’,

primary key：主键约束
指定id列为主键：
id int not null primary key,
– 主键是 not null 和 unique的结合，可以不用 not null
可以是1个或多个字段

对于整数类型的主键，常配搭自增长auto_increment来使用，插入数据对应字段不给值时，使用最大值+1
id int primary key auto_increment,

foreign key：外键约束
外键用于关联其他表的主键或唯一键，起到数据关系存储的作用
foreign key (字段名) references 主表(列)
创建学生表student，一个学生对应一个班级，一个班级对应多个学生，使用id为主键，classes_id为外键，关联班级表id
foreign key (classes_id) references classes(id)

表的设计
三大范式
一对一 外键在两张表都可以用
一对多 外键在多的关系所在的表中，例如学生表存储班级表信息
多对多 需要有中间表，外键在中间表

新增
– 将学生表中的所有数据复制到用户表
insert into test_user(name, email) select name, qq_mail from student;

聚合查询
count
– 统计班级收集的 qq_mail 有多少个，qq_mail 为 null的数据不会计入结果
select count(qq_mail) from student;
sum
– 统计数学成绩总分
select sum(math) from exam_result;
avg
– 统计平均总分
select avg(chinese + math + english) 平均总分 from exam_result;
max
– 返回英语最高分
select max(english) from exam_result;
min
返回 > 70 分以上的数学最低分
select min(math) from exam_result where math > 70;

group by
select 中使用 group by 子句可以对指定列进行分组查询。需要满足：使用 group by进行分组查询时，select指定的字段必须是“分组依据字段”，其他字段若想出现在select中则必须包含在聚合函数中

–查询每个角色的最高工资、最低工资和平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role;

group by 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 where 语句，而需要用 having
–显示平均工资低于1500的角色和它的平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)<1500;

联合查询
笛卡尔积：多张表关联，结果集的字段是多张表字段合集，结果集的数据是多张表的数据排列组合
关联查询可以对关联表使用别名

内连接
取笛卡尔积，经过链接条件过滤后的结果集
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;
必须满足连接条件

– 学生表、成绩表、课程表3张表关联查询
select stu.id,stu.sn,stu.name,stu.qq_mail,sco.score,sco.course_id,cou.name
from student stu join score sco on stu.id = sco.student_id join course cou on sco.course_id = cou.id order by stu.id;

外连接
外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示就说是左外连接；右侧的表完全显示就说是右外连接。不以连接条件为依据
没有的显示为null

自连接
自连接是指在同一张表连接自身进行查询
可以自己比较自己

子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
单行子查询：返回一行记录的子查询
多行子查询：返回多行记录的子查询

合并查询
union
该操作符用于取得两个结果集的并集，当使用该操作符时，会自动去掉结果集中的重复行
union all 不去重
用于取得两个结果集的并集，当使用该操作符时，不会去掉结果集中的重复行