表的约束
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