MySQL_3_DQL语句和约束

DQL语句和约束

1、DQL–数据查询语言

我们之前在《MySQL_2_SQL语句基础》已经简单介绍了SQL语句，其中就包括DQL（数据查询语言）和基础查询和条件查询，接着我们再来进一步的介绍DQL语言。

1.1、排序查询

1. 语法： order by 子句
	order by 排序字段1 排序方式1, ……排序字段n 排序方式n;
	
2. 排序方式
	1. ASC：升序，默认的。
	2. DESC：降序。



3. 聚合函数：将一列数据作为一个整体，进行纵向的计算。
	1. count：计算个数
		1. 一般选择非空的列：主键
		2. count(*)
	2. max：计算最大值
	3. min：计算最小值
	4. sum：计算和
	5. avg：计算平均值

4. 注意
	1. 如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。
	2. 聚合函数的计算，排除null值。
		解决方案：
			1. 选择不包含非空的列进行计算
			2. IFNULL函数 

---

1.2、分组查询

1. 语法： group by 子句

2. 注意点
	1. 分组之后查询的字段：分组字段、聚合函数
	2. where 和 having 的区别？
			1. where 在分组之前进行限定，如果不满足条件，则不参与分组。而having在分组之后进行限定，如果不满足结果，则不会被查询出来
			2. where 后不可以跟聚合函数，having可以进行聚合函数的判断。

接下来我们来举几个分组查询的例子

-- 按照性别分组。分别查询男、女同学数学的平均分。
select `sex`, avg(`math`) 
from `student` 
group by `sex`;

-- 按照性别分组。分别查询男、女同学的数学平均分、人数。
select `sex`, avg(`math`), count(`id`)
from `student`
group by `sex`;

-- 按照性别分组。分别查询男、女同学的数学平均分、人数。
-- 要求：分数低于70分的人，不参与分组
select `sex`, avg(`math`), count(`id`)
from `student`
where `math` >= 70
group by `sex`;

-- 按照性别分组。分别查询男、女同学的数学平均分、人数。
-- 要求：分数低于70分的人，不参与分组，分组之后。人数要大于2个人
select `sex`, avg(`math`), count(`id`)
from `student`
where `math` >= 70
group by `sex`
having count(`id`) > 2;

select `sex` as '性别', avg(`math`) as '数学平均分', count(`id`) as '人数'
from `student`
where `math` >= 70
group by `sex`
having '人数' > 2;

---

1.3、分页查询

1. 语法： 
	1. limit 开始的索引, 每页查询的条数
	1. limit 每页查询的条数 offset 开始的索引 

2. 公式：开始的索引 = (当前的页码 - 1) * 每页显示的条数
	-- 从第2行开始，每页显示3条记录
	-- 注意：索引是从0开始的，所以第二行索引是1
	select * from student limit 1, 3;
	select * from student limit 3 offset 1;
	
	-- 每页显示3条记录
	SELECT * FROM student LIMIT 0, 3; -- 第1页
	SELECT * FROM student LIMIT 3, 3; -- 第2页
	SELECT * FROM student LIMIT 6, 3; -- 第3页

3. limit 是一个MySQL"方言"

---

2、约束

1. 概念： 对表中的数据进行限定，保证数据的正确性、有效性和完整性。	
2. 分类：
	1. 主键约束：primary KEY
	2. 非空约束：not null
	3. 唯一约束：unique
	4. 外键约束：FOREIGN KEY

---

2.1、非空约束

非空约束： not null，值不能为null

1. 创建表时，添加非空约束-- 在字段后面添加约束
	CREATE TABLE `stu`(
    	id int,
        name varchar(20) not null -- name为非空
    );
    
2. 创建完表后，添加非空约束
alter TABLE `stu` modify name varchar(20) not null;

3. 删除name的非空约束
alter TABLE stu modify name varchar(20);

---

2.2、唯一约束

唯一约束： unique，值不能重复

1. 创建表时，添加唯一约束-- 在字段后面添加约束
	CREATE TABLE `stu`(
    	id int,
        phone varchar(20) unique -- phone值不能重复
    );
    
    # 注意MySQL中，唯一约束限定的列的值可以有多个null
    
2. 在创建表后，添加唯一约束
alter TABLE `stu` modify phone varchar(20) unique;

3. 删除唯一约束
alter TABLE `stu` drop i

2.3、主键约束

主键约束：primary KEY

注意： 
	1. 含义：非空且唯一 
	2. 一张表只能有一个字段为主键 
	3. 主键就是表中记录的唯一标识

1. 添加主键约束（创建表）
CREATE TABLE stu(
	id int primary KEY,	-- 给id添加主键约束 
	name varchar(20)
); 
                 
2. 删除主键 
-- 错误 alter TABLE stu modify id int ; 
ALTER TABLE stu DROP PRIMARY KEY; 

4. 创建完表后，添加主键 ALTER TABLE stu MODIFY id INT PRIMARY KEY; 

5. 自动增长： 
	1. 概念：如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长 
	2. 在创建表时，添加主键约束，并且完成主键自增长
	CREATE TABLE stu(
    	id int primary KEY auto_increment, -- 给id添加主键约束 
		name varchar(20)
    );
    3. 删除自动增长 ALTER TABLE stu MODIFY id INT; 4. 添加自动增长 ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
    

---

2.4、外键约束

外键约束：FOREIGN KEY

注：让表于表产生关系，从而保证数据的正确性。

1. 添加外键（创建表）
	CREATE TABLE 表名(
        .... 
        外键列 
        CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表 列名称)
    ); 

2. 删除外键 
	ALTER TABLE 表名 DROP FOREIGN KEY 外键名称; 

3. 添加外键（创建表之后）
	ALTER TABLE 表名 
	ADD CONSTRAINT 外键名称 
	FOREIGN KEY (外键字段名称) 
	REFERENCES 主表名称(主表列名称); 

4. 级联操作 
	1. 添加级联操作
	ALTER TABLE 表名 
	ADD CONSTRAINT 外键名称 
	FOREIGN KEY (外键字段名称) 
	REFERENCES 主表名称(主表列名称) 
	ON UPDATE CASCADE ON DELETE CASCADE ; 
	2. 分类： 
		1. 级联更新：ON UPDATE CASCADE 
		2. 级联删除：ON DELETE CASCADE

---

3、数据库设计

3.1、多表之间的关系

1. 分类

1. 一对一： 
	* 如：人和身份证 
	* 分析：一个人只有一个身份证，一个身份证只能对应一个人 

2. 一对多(多对一)： 
	* 如：部门和员工 
	* 分析：一个部门有多个员工，一个员工只能对应一个部门 

3. 多对多： 
	* 如：学生和课程 
	* 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择

2. 实现关系

1. 一对一： 
	* 如：人和身份证 
	* 实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。

2. 一对多(多对一)： 
	* 如：部门和员工 
	* 实现方式：在多的一方建立外键，指向一的一方的主键。 

3. 多对多： 
	* 如：学生和课程 
	* 实现方式：多对多关系实现需要借助第三张中间表。
	  注：中间表至少包含两个字段，这两个字段 作为第三张表的外键，分别指向两张表的主键。

---

3.2、数据库设计的范式

概念：设计数据库时，需要遵循的一些规范。

要遵循后边的范式要求，必须先遵循前边的所有范式要求设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称 为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

目前关系数据库有六种范式：

1. 第一范式（1NF）
2. 第二范式（2NF）
3. 第三范式（3NF）
4. 巴斯-科德范式（BCNF）
5. 第四范式(4NF
6. 和第五范式（5NF，又称完美范式）

1. 第一范式（1NF）：每一列都是不可分割的原子数据项 

2. 第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于码。
   注：（在1NF基础上消除非主属性 对主码的部分函数依赖） 
	* 几个概念： 
	1. 函数依赖：A-->B
		如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A。
		例如：学号-->姓名。 （学号，课程名称） --> 分数 
	2. 完全函数依赖：A-->B
		如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。
		例如：（学号，课程名称） --> 分数 
	3. 部分函数依赖：A-->B
		如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。 
		例如：（学号，课程名称） --> 姓名 
	4. 传递函数依赖：A-->B, B -->C
		如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A 
		例如：学号-->系名，系名-->系主任
	5. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属 性(属性组)为该表的码 		 例如：该表中码为：（学号，课程名称） 
		* 主属性：码属性组中的所有属性 
		* 非主属性：除过码属性组的属性

3. 第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

---

4、数据库备份与还原

命令行语法： 
	* 备份： mysqldump -u用户名 -p密码 数据库名称 > 保存的路径 
	* 还原：
		1. 登录数据库 
		2. 创建数据库 
		3. 使用数据库 
		4. 执行文件：source 文件路径

---

注：如有错误，敬请指正！