数据库SQL语句（进阶篇）

1. 数据库约束

NULL约束：创建表时，可以指定某列不为空

UNIQUE：唯一约束，指定sn列为唯一的、不重复的

DEFAULT：默认值约束，指定插入数据时，name列为空，默认值unkown

PRIMARY KEY：主键约束，指定id列为主键
（对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大值+1。主键是 NOT NULL 和 UNIQUE 的结合，可以不用 NOT NULL）

FOREIGN KEY：外键约束，用于关联其他表的主键或唯一键

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
 id INT PRIMARY KEY auto_increment,
 sn INT UNIQUE,
 name VARCHAR(20) DEFAULT 'unkown',
 qq_mail VARCHAR(20),
classes_id int,
FOREIGN KEY (classes_id) REFERENCES classes(id)
);

2. 聚合查询

COUNT

-- 统计班级共有多少同学
SELECT COUNT(*) FROM student;
SELECT COUNT(0) FROM student;
-- 统计班级收集的 qq_mail 有多少个，qq_mail 为 NULL 的数据不会计入结果
SELECT COUNT(qq_mail) FROM student;

SUM

-- 统计数学成绩总分
SELECT SUM(math) FROM exam_result;
-- 不及格 < 60 的总分，没有结果，返回 NULL
SELECT SUM(math) FROM exam_result WHERE math < 60;

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 column1, sum(column2), .. from table group by column1,column3；

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

HAVING（GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用
HAVING）

--显示平均工资低于1500的角色和它的平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role
having avg(salary)<1500;

having与where的区别：where先过滤后分组；having先分组后过滤

3. 联合查询

内连接 【自连接（自连接是指在同一张表连接自身进行查询）】

--语法
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件
--案例  查询张三的成绩
select sco.score from student stu inner join score sco on stu.id=sco.student_id
and stu.name='张三';
-- 或者
select sco.score from student stu, score sco where stu.id=sco.student_id and
stu.name='张三';

外连接（外连接分为左外连接和右外连接）

-- 左外连接，表1完全显示
select 字段名  from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;

子查询（嵌入在其他sql语句中的select语句，也叫嵌套查询）

-- 查询张三的同班同学
select * from student where classes_id=(select classes_id from student where
name='张三');
--关键字 IN / NOT IN
select * from score where course_id 【not】in (select id from course where
name='语文 ' or name = '英文');
--关键字EXISTS / NOT EXISTS
select * from score sco where not exists (select sco.id from course cou
where (name!='语文' and name!='英文') and cou.id = sco.course_id);

合并查询

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

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

select * from course where id<3
union
select * from course where name='英文';
-- 或者使用or来实现
select * from course where id<3 or name='英文'；