数据库表的进阶

表的约束

NOT NULL - 指示某列不能存储 NULL 值。

创建表时，可以指定某列不为空

CREATE TABLE student (
   id INT NOT NULL,
   sn INT,
   name VARCHAR(20),
   qq_mail VARCHAR(20)
);

UNIQUE - 保证某列的每行必须有唯一的值。

指定sn列为唯一的、不重复的：

DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT NOT NULL,
   sn INT UNIQUE,
   name VARCHAR(20),
   qq_mail VARCHAR(20)
);

DEFAULT - 规定没有给列赋值时的默认值。

PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

指定id列为主键：
对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大值+1。

id INT PRIMARY KEY auto_increment

FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。

外键用于关联其他表的主键或唯一键

foreign key (字段名) references 主表(列)

聚合查询

聚合函数

常见的统计总数、计算平局值等操作，可以使用聚合函数来实现，常见的聚合函数有

COUNT([DISTINCT] expr) 返回查询到的数据的 数量
SUM([DISTINCT] expr) 返回查询到的数据的 总和，不是数字没有意义
AVG([DISTINCT] expr) 返回查询到的数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr) 返回查询到的数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr) 返回查询到的数据的 最小值，不是数字没有意义

SELECT COUNT(*) FROM student;
SELECT SUM(math) FROM exam_result;
-- 统计平均总分
SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;
-- 返回英语最高分
SELECT MAX(english) FROM exam_result;
-- 返回 > 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;

HAVING

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

select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)<1500;

联合查询

内连接 2种

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 连接条件;

自连接
自连接是指在同一张表连接自身进行查询

select ... from 表1 ,表1 where 条件
select ... from 表1 join 表1 on 条件

SQL查询中各个关键字的执行先后顺序： from > on> join > where > group by > with > having >
select > distinct > order by > limit