MySQL_约束与进阶查询

目录

一、数据库约束

二、插入查询结果

三、聚合查询

3.1 聚合函数

3.2 group by 字句

3.3 having 

四、多表联查

4.1 内连接

4.2 外连接

4.3 自连接

4.4 子查询

4.5 合并查询

总结

---

一、数据库约束

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 不会自动去掉重复行。