DQL查询语句、约束、多表之间的关系、范式、数据库的备份与还原

本文链接：https://blog.youkuaiyun.com/weixin_45291606/article/details/104123363

DQL查询语句

1. 排序查询

* 语法：order by 子句
	- order by 排序字段1 排序方式1， 排序字段2排序方式2 ...
* 排序方式：
	- ASC ：升序，默认的。
	- DESC ：降序。
* 注意 ：
	- 如果有多个

排序条件，则当前的条件值一样时，才会判断第二条件。

	-- 按照数学成绩排名，ASC升序，DESC降序。
 SELECT * FROM student ORDER BY math ASC;
 SELECT * FROM student ORDER BY math DESC;
 
	-- 按照数学成绩排名，如果数学成绩一样，则按照英语成绩升序排名
 SELECT * FROM student ORDER BY math ASC, english ASC;

2. 聚合函数：将一列数据作为一个整体，进行纵向的计算。

1. count ：计算个数
	1. 一般选择非空列 ：主键
		2. count(*) 
2. max ：计算最大值
3. min ：计算最小值
4. sum ：求和
5. avg ：计算平均值
* 注意 ：聚合函数的计算，排除 null 值
	* 解决方案：
		1. 选择不包含非空的列进行计算
		2. IFNULL函数

	-- 计算某列共有多少个元素
 SELECT COUNT(NAME) FROM student;
 SELECT COUNT(IFNULL(english,0))FROM student;
 
 SELECT COUNT(*) FROM student;	-- *表示只要这一行中某一列不为null，那么他就能够算一条数据。
 
	-- 求最大值、最小值、和、平均值
 SELECT MAX(math) FROM student;
 SELECT MIN(math) FROM studnet;
 SELECT SUM(math) FROM student;
 SELECT SUM(english) FROM student;
 SELECT AVG(math) FROM student;

3. 分组查询：

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分的人，不参与分组, 分组之后，人数要大于两个人。(小于两人的组不显示)
 SELECT sex, AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
	-- 优化-聚合函数起别名
 SELECT sex, AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;

4.分页查询

	1. 语法 ：limit 开始的索引, 每页查询的条数；
	2. 公式：开始的索引 = （当前的页码-1）* 每页显示的条数
	3. 分页操作是一个“方言”

-- 每页显示3条记录
 SELECT * FROM student LIMIT 0,3;	-- 第1页
 SELECT * FROM student LIMIT 3,3;	-- 第2页
 SELECT * FROM student LIMIT 6,3;	-- 第3页

约束

* 概念：对表中数据进行限定，保证数据的正确性、有效性和完整性。
* 分类：
	1. 主键约束：primary key
	2. 非空约束：not null
	3. 唯一约束：unique
	4. 外键约束：foreign key
* 非空约束 ：not null
	1. 创建表时添加约束
			CREATE TABLE stu(
				id INT,
				NAME VARCHAR(20) NOT NULL  
			);	
	2. 创建完表后，添加非空约束
			ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
 	3. 删除非空约束
			ALTER TABLE stu MODIFY NAME VARCHAR(20);
* 唯一约束：unique，某一列的值不能重复
	1. 注意：
			* 唯一约束可以有null值，但是只能有一条记录为null
	2. 在创建表时，创建唯一约束
    		CREATE TABLE stu(
				id INT,
				phone_number VARCHAR(20) UNIQUE  -- 手机号
			);
	3. 删除唯一约束：
			ALTER TABLE stu DROP INDEX phone_number; 
 	4. 在表创建完之后，添加唯一约束。
 			 ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
* 主键约束 ：primary key.
	1. 注意：
			1. 含义：非空且唯一
			2. 一张表只能有一个字段为主键
			3. 主键就是表中记录的唯一标识
	2. 在创建表时，添加主键约束
			create table stu(
					id int primary key,		-- 给id添加主键约束
					name varchar(20)
			); 
	3. 删除主键约束
			 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;
			5. 使用自动增长功能创建一条数据
						INSERT INTO stu VALUE(NULL,'ccc');
* 外键约束 : foreign key, 让表与表产生关系，从而保证数据的正确性。
	1. 在创建表时，可以添加外键
		create table 表名(
				...
				外键列
				constraint 外键名称 foreign key	(外键列名称) references 主表名称(主表列名称)
		);
	2. 删除外键
			AlTER TABLE 表名 DROP FOREING KEY 外键名称;
	3. 创建表之后，添加外键
			AlTER TABLE 表名 DROP FOREING KEY 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

-- 解决方案：分成2张表 -- 创建部门表(id,dep_name,dep_location) 
-- 一方，主表 
CREATE TABLE department(  
	id INT PRIMARY KEY AUTO_INCREMENT,  
	dep_name VARCHAR(20),  
	dep_location VARCHAR(20) 
); 
 
-- 创建员工表(id,name,age,dep_id) 
-- 多方，从表 
CREATE TABLE employee(  
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),  
	age INT,  
	dep_id INT,  -- 外键对应主表的主键 
	CONSTRAINT em_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
); 
 
-- 添加2个部门 
INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳');
 
-- 添加员工,dep_id表示员工所在的部门 
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1); 
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1); 
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1); 
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2); 
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2); 
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);     

SELECT * FROM employee;

SELECT * FROM department;

	-- 删除外键
 ALTER TABLE employee DROP FOREIGN KEY em_dept_fk;
 
	-- 添加外键
 ALTER TABLE employee ADD CONSTRAINT em_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id);

级联更新

* 级联更新：外键表中数据修改后，主表数据也得到更新
* 需求： 含有外键约束的两个表，修改外键数据
	* 方法一 
		  	UPDATE employee SET dep_id = NULL WHERE dep_id = 1;
		 ①首先把 employee表中的   dep_id = 1； 改为dep_id = null；
		 ②此时 employee 表与 department表中null 的部分以没有约束关系
		 ③再修改null为5
	  		UPDATE employee SET dep_id = 5 WHERE dep_id IS NULL;
	* 方法二
		-- 删除外键
		 ALTER TABLE employee DROP FOREIGN KEY em_dept_fk;
		-- 添加外键,并设置级联更新
			ALTER TABLE employee ADD CONSTRAINT em_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE;

如下图所示，当department表中id的数据更改之后，employee表中的dep_id 会立即得到更新。

在这里插入图片描述

* 级联删除
	* -- 删除外键
			ALTER TABLE employee DROP FOREIGN KEY em_dept_fk;
	  -- 添加外键,并设置级联更新,设置级联删除
			ALTER TABLE employee ADD CONSTRAINT em_dept_fk FOREIGN KEY 
			(dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE;

效果：当department表中 id=5 的行被删除之后，employee表中 dep_id = 5 的数据会被自动删除。

在这里插入图片描述

数据库的设计

1. 多表之间的关系
		1. 一对一（了解）
				* 如：身份证
				* 分析：一个人只有一张身份证，一个身份证只能对应一个人。
		2. 一对多（多对一）
				* 如：部门和员工
				* 分析：一个部门对应多个员工，一个员工只能对应一个部门
		3. 多对多 
				* 如：学生和课程
				* 分析：一个学生可以选择很多门课程，一个课程可以被很多学生选择。
2. 实现关系：
	1. 一对多(多对一)
				* 如：部门和员工
				* 实现方式：在多地一方建立外键，指向一的一方的主键。
	2. 多对多 
				* 如：学生和课程
				* 分析：一个学生可以选择很多门课程，一个课程可以被很多学生选择。 
	3. 一对一（了解）
				* 如：身份证
				* 分析：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。

在这里插入图片描述

4. 练习

-- 创建旅游线路分类表tab_category 
-- cid旅游线路分类主键，自动增长 
-- cname旅游线路分类名称非空，唯一，字符串100 
 CREATE TABLE tab_category (   
	cid INT PRIMARY KEY AUTO_INCREMENT,   
	cname VARCHAR(100) NOT NULL UNIQUE 
 ) 
 
-- 添加旅游线路分类数据： 
 INSERT INTO tab_category (cname) VALUES ('周边游'),('出境游'),('国内游'),('港澳游'); 
 SELECT * FROM tab_category; 
 
-- 创建旅游线路表tab_route
 /*
  rid旅游线路主键，自动增长
  rname旅游线路名称非空，唯一，字符串100 
  price价格 
  rdate 上架时间，日期类型 
  cid 外键，所属分类 
  */ 
  CREATE TABLE tab_route(   
	  rid INT PRIMARY KEY AUTO_INCREMENT,   
	  rname VARCHAR(100) NOT NULL UNIQUE,   
	  price DOUBLE,   
	  rdate DATE,   
	  cid INT,   
	  FOREIGN KEY (cid) REFERENCES tab_category(cid) 
  ) 
 
-- 添加旅游线路数据 
INSERT INTO tab_route VALUES 
(NULL,'【厦门+鼓浪屿+南普陀寺+曾厝垵 高铁3天 惠贵团】尝味友鸭面线 住1晚鼓浪屿', 1499, '2018-01-27', 1), 
(NULL,'【浪漫桂林 阳朔西街高铁3天纯玩 高级团】城徽象鼻山 兴坪漓江 西山公园', 699, '2018-02-22', 3), 
(NULL,'【爆款￥1699秒杀】泰国 曼谷 芭堤雅 金沙岛 杜拉拉水上市场 双飞六天【含送签费 泰风情 广州 往返 特价团】', 1699, '2018-01-27', 2), 
(NULL,'【经典•狮航 ￥2399秒杀】巴厘岛双飞五天 抵玩【广州往返 特价团】', 2399, '2017-12-23', 2), 
(NULL,'香港迪士尼乐园自由行2天【永东跨境巴士广东至迪士尼去程交通+迪士尼一日门票+香港如心海景酒店 暨会议中心标准房1晚住宿】', 799, '2018-04-10', 4); 
 SELECT * FROM tab_route;

2. 数据库设计的范式
* 分类：
	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基础上消除传递依赖)

0NF
非范式
1NF
1NF
2NF
2NF
3NF
3NF

数据库的备份和还原

1. 命令行
	* 语法 ：
		* 备份 ：mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
		* 还原 ：
			* 登录数据库：	mysql -u用户名 -p密码 
			* 创建数据库：	create 数据库名称
			* 使用数据库：	use 数据库名称
			* 执行文件：		source 文件路径
2. 图形化工具 ： sqlyog