SQL基础与高级查询-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_46653783/article/details/110523406

1. 数据库约束条件

约束类型	说明	示例
NULL约束	使用NOT NULL指定列不为空	name varchar(20) not null,
UNIQUE唯一约束	指定列为唯一的、不重复的	name varchar(20) unique,
DEFAULT默认值约束	指定列为空时的默认值	age int default 20,
主键约束	NOT NULL 和 UNIQUE 的结合	id int primary key,
外键约束	关联其他表的主键或唯一键	foreign key (字段名) references 主表(列)
CHECK约束）	保证列中的值符合指定的条件	check (sex =‘男’ or sex=‘女’)

2. 三大范式(以及BC范式，第四、五范式)

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

第一范式（1NF）：

要求数据库表的每一列都是不可分割的原子数据项。

第二范式（2NF）：

在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

第三范式（3NF）：

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
、

BC范式（BCNF）：

符合3NF，并且，主属性不依赖于主属性。若关系模式R属于第一范式，且每个属性都不传递依赖于键码，则R属于BC范式。

第四范式：

要求把同一表内的多对多关系删除。

第五范式：

从最终结构重新建立原始结构。

3. 新增查询结果

语法：

INSERT INTO table_name [(column [, column ...])] SELECT ...

案例：创建一张用户表，设计有name姓名、email邮箱、sex性别、mobile手机号字段。需要把已有的
学生数据复制进来，可以复制的字段为name、qq_mail

-- 创建用户表
DROP TABLE IF EXISTS test_user;
CREATE TABLE test_user (
	id INT primary key auto_increment,
	name VARCHAR(20) comment '姓名',
	age INT comment '年龄',
	email VARCHAR(20) comment '邮箱',
	sex varchar(1) comment '性别',
	mobile varchar(20) comment '手机号'
);

-- 将学生表中的所有数据复制到用户表
insert into test_user(name, email) select name, qq_mail from student;

4. 查询

4.1 聚合函数

函数	说明	示例
count	返回查询到的数据的数量	SELECT COUNT(qq_mail) FROM student;
sum	返回查询到的数据的总和，不是数字没有意义	SELECT SUM(math) FROM exam_result;
avg	返回查询到的数据的平均值，不是数字没有意义	SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;
max	返回查询到的数据的最大值，不是数字没有意义	SELECT MAX(english) FROM exam_result;
min	返回查询到的数据的最小值，不是数字没有意义	SELECT MIN(math) FROM exam_result WHERE math > 70;

扩展：count(*) count(1) count(0)

count(0)=count(1)=count(*)

count(指定的有效值)–执行计划都会转化为count(*)
如果指定的是列名，会判断是否有null，null不计算

4.2 分组操作

select column1, sum(column2), .. from table group by column1,column3;

分组可以对多个字段分组，表示多个字段不唯一，才算一个分组。
分组操作中，查询字段中允许的是分组字段，聚合函数，其他非分组字段需要保证分组后没有多行（如学生id分组，查询字段可以有学生姓名，因为学生id分组以后，只有一行）。
分组前条件过滤使用where，分组后条件查询使用having。

4.3 内连接

select [查询内容] from [表1] join [表2] on 连接条件1 and 其他条件3 where 其他条件1 and 其他条件2；

select [查询内容] from [表一]，[表二] where 连接条件 and 其他条件；

4.4 外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。

左外连接，表一完全显示

select [查询内容] from [表一] left join [表二] on 连接条件；

右外连接，表二完全显示

select [查询内容] from [表一] right join [表二] on 连接条件；

内、外连接区别

内连接：查询信息必须完全符合连接条件。
外连接：左/右表的数据不依赖连接条件能够完全显现（其他条件还是可以过滤的）。

建议使用方式

内连接：使用join…on…and… 和使用join…on…where……没有区别
外链接：from 左表 left join

4.5 自连接

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

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

4.6 子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。

 -- 单行子查询
select ... from 表1 where 字段1 = (select ... from ...);

-- [NOT] IN
select ... from 表1 where 字段1 in (select ... from ...); 

-- [NOT] EXISTS
select ... from 表1 where exists (select ... from ... where 条件);

-- 临时表：form子句中的子查询
select ... from 表1， (select ... from ...) as tmp where 条件

4.7 合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用union
和union all 时，前后查询的结果集中，字段需要一致。

-- union：去除重复数据
select ... from ... where 条件
union
select ... from ... where 条件

-- union all：不去重
select ... from ... where 条件
union all 
select ... from ... where 条件