目录
一、数据库约束
1、NOT NULL:不能存储 null 值。
【例子】
moon 表中的 id 不能是 null 值。
CREATE TABLE moon(
id INT NOT NULL
);
2、UNIQUE:保证某列的每行必须有唯一的值。
加上 unique 约束后,后续每次进行插入/修改时,都会先查询当前值是否已存在。
【例子】
moon 表中的 id 是唯一的、不重复的。
CREATE TABLE moon(
id INT UNIQUE
);
3、DEFAULT:规定列的默认值。
【例子】
moon 表中的 name 默认值为 "sun"。
CREATE TABLE moon(
id INT,
name VARCHAR(20) DEFAULT 'sun'
);
4、PRIMARY KEY:主键,NOT NULL 和 UNIQUE 的结合。确保每行有唯一标识。
对于整型的主键,常搭配自增 auto_increment 来使用,即最大值+1。
【例子】
指定 moon 表中的 id 为主键,插入记录时,若未指定 id,使用最大值+1。
CREATE TABLE moon(
id INT PRIMARY KEY auto_increment,
name VARCHAR(20)
);
5、FOREIGN KEY:外键,保证一个表中的数据匹配另一个表中的值的参照完整性。
foreign key (列名) references 主表名(列名)
【例子】
student 表中的 class_id 关联 class 表中的 id,class_id 是 student 表的外键。
CREATE TABLE class(
id INT PRIMARY KEY,
name VARCHAR(20),
);
CREATE TABLE studnet(
id INT PRIMARY KEY,
name VARCHAR(20),
class_id int,
foreign key (class_id) references class(id)
);
6、CHECK:保证列中的值符合指定的条件。(MySQL 不支持,但忽略)
【例子】
moon 表中的 id 不能是 null 值。
create table moon(
id int,
name varchar(20),
sex varchar(1),
check (sex = '男' or sex = '女')
);
二、插入查询结果
将表1查询出来的记录插入表2,前提是保证查询结果与表2结构匹配。
可以理解为将查询出的"临时数据"转换成"永久数据"。
insert into 表2 select …… from 表1;
三、聚合查询
3.1 聚合函数
1、COUNT:返回查询到的数据数量。
【例子】
统计班级有多少同学。
select count(*) from student;
2、SUM:返回查询到的数据的总和。
【例子】
统计数学成绩总分。
select sum(math) from class;
3、AVG:返回查询到的数据的平均值。
【例子】
统计平均总分。
select avg(chinese + math + english) as 平均总分 from class;
4、MAX:返回查询到的数据的最大值。
【例子】
查询数学最高分。
select max(math) from class;
5、MIN:返回查询到的数据的最小值。
【例子】
查询 > 60 分以上数学最低分。
select min(math) from class where math > 60;
3.2 group by 字句
SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。
需要满足:使用 GROUP BY 进行分组查询时,SELECT 指定的字段必须是"分组依据字段",其他字段若想出现在 SELECT 中则必须包含在聚合函数中。
【例子】
查询每个老师的最高工资、最低工资和平均工资。
select teacher, max(salary), min(salary), avg(salary) from school group by teacher;
3.3 having
GROUP BY 子句进行分组以后,需要对分组结果再进行条件过滤时,不能使用 WHERE 语句,而需要用 HAVING。
【例子】
查询平均工资低于 3000 的老师其最高工资、最低工资和平均工资。
select teacher, max(salary), min(salary), avg(salary) from school group by teacher having avg(salary)<3000;
四、多表联查
多表联合查询是对多张表的数据取笛卡尔积。
两个表进行笛卡尔积之前,一定要确保这两个表至少有一个列是关联的。
4.1 内连接
select …… from 表1 别名1, 表2 别名2 where 连接条件 and 其他条件;
select …… from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
【例子】
select * from student, class where student.class_id = class.id;
select * from student join class on student.class_id = class.id;
4.2 外连接
外连接分为左外连接和右外连接。如果联合查询,左侧的表完全显示我们就说是左外连接;右侧的表完全显示我们就说是右外连接。一个表完全显示,另一个没有对应记录即显示 NULL。
-- 左外连接,表1完全显示
select …… from 表1 left join 表2 on 连接条件;
-- 右外连接,表2完全显示
select …… from 表1 right join 表2 on 连接条件;
4.3 自连接
自连接是指在同一张表连接自身进行查询。
select …… from 表1 别名1, 表1 别名2 where 连接条件;
select …… from 表1 别名1 join 表1 别名2 on 连接条件;
【例子】
select * from class c1, class c2 where c1.id = c2.id;
select * from class c1 join class c2 on c1.id = c2.id;
4.4 子查询
子查询是指嵌入在其他 sql 语句中的 select 语句,也叫嵌套查询。
【例子】
查询和"懒羊羊"一个村的羊。
select * from sheep_village where sheep_id=(select sheep_id from sheep_village where name='懒羊羊');
4.5 合并查询
在实际应用中,为了合并多个 select 的执行结果,可以使用集合操作符 union,union all。使用 union 和 union all 时,前后查询的结果集中,字段需要一致。
union:用于取得两个结果集的并集。该操作符会自动去掉结果集中的重复行。
union all:用于取得两个结果集的并集。该操作符不会去掉结果集中的重复行。
【例子】
查询 id 小于 3,或者名字叫"懒羊羊"的同学。
select * from student where id<3 union [all] select * from student where name='懒羊羊';
注:使用 union 或 union all 时,可以跨表查询,但字段需一致。
总结
1、分组之前条件语句用 where,分组之后条件语句用 having。
2、两个表进行笛卡尔积之前,一定要确保这两个表至少有一个列是关联的。
3、union 会自动去掉重复行,union all 不会自动去掉重复行。