1. DQL 查询语句的使用

Data Query Language(DQL 数据查询语言)，如：对表中的查询操作。关键字：select、where。

1.1 排序查询

语法：order by 子句

1. order by   排序字段1 排序方式1 , 排序字段2  排序方式2, ..... , 排序字段n  排序方式 n 
2. 排序方式 ： 
  * ASC 升序：默认；
  * DESC降序。
3. 注意：如果有多个排序条件则当第一个条件一样时才会去判断第二个条件。

-- 排序查询 开始 

-- 1. 按照数学成绩排序
SELECT * FROM  student3 ORDER BY math ;

-- 2. 按照数据成绩排名如果，数学成绩一样，则按照英语成绩排名
SELECT *FROM student3 ORDER BY math ASC,english ASC;

-- 排序查询 结束

1.2 聚合查询

聚合函数：将一列数据作为一个整体，进行纵向的计算。计算数学列的平均分数。
sql中的1聚合函数:

1. count: 计算个数；
2. max：计算最大值；
3. min：计算最小值;
4. sum:计算和;
5. avg：计算平均数;

注意：

1. 聚合函数会排除null值。
2. 解决聚合函数排除null值的问题，选择不包含null的列（主键、或者count(*)）；或者使用IFNULL() 函数。

案例：

-- count 开始

-- 1. 根据name查询数据的总条数
SELECT COUNT(NAME) FROM student3;

-- 2. 如果行中有值为null的列，计算时不会包含null 。
SELECT COUNT(english) AS 数据总条数 FROM student3;
SELECT COUNT(IFNULL(english,0)) AS 数据总条数 FROM student3;

-- count 结束

-- max 最大值
-- 查询数学成绩的最高分 
SELECT MAX(math) FROM student3;

-- min 最小值
-- 查询英语成绩的最低分
SELECT MIN(english) FROM student3;

-- sum求和函数 
-- 计算当前数学成绩的总分 
SELECT SUM(math) FROM student3;

-- 计算英语成绩的总分
SELECT SUM(english) FROM student3;
SELECT * FROM student3;

-- avg 求平均数

-- 计算数学成绩的平均分
SELECT AVG(math) FROM student3;

-- 计算英语成绩的平均分 
SELECT AVG(english) FROM student3;

1.3 分组查询

语法：

1. group by 分组字段。
2. 注意 ：分组之后查询的字段是分组字段、聚合函数。
3. 注意：where 和 having的区别 : a.作用位置不一样，where 在分组之前进行限定，如果不满足条件则不参与分组。having是在分组之后进行限定，如果不满足结果则不会被查询出来。where 后不能跟聚合函数的判断。having 后可以跟聚合函数的判断。

案例:

-- 分组查询 开始 

-- 1. 按照性别分组。分别查询男、女同学的平均分
SELECT sex , AVG(math) FROM student3 GROUP BY sex;

-- 2. 按照性别分组。分别查询男、女同学的平均分
SELECT sex 性别  , AVG(math)平均分 ,COUNT(id) 人数 FROM student3 GROUP BY sex;

-- 3. 按照性别分组。分别查询男、女同学的平均分 .要求低于70分的人不参与分组
SELECT sex 性别 , AVG(math)平均分 ,COUNT(id) 人数 FROM student3 WHERE math > 70 GROUP BY sex;

-- 4. 按照性别分组。分别查询男、女同学的平均分 .要求低于70分的人不参与分组。并要求分组之后的人数需要大于2个人
SELECT sex 性别 , AVG(math) 平均分 , COUNT(id) 人数 FROM student3 WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;

-- having 关键字之后新写法 
SELECT sex 性别 , AVG(math) 平均分,COUNT(id) 人数 FROM student3 WHERE math > 70 GROUP BY sex HAVING 人数 > 2;

-- 分组查询结束

1.4 分页查询

语法：

1. limit 开始的索引 每页显示的条数 ；
2. 公式： a. 开始的索引 = （当前的页码 - 1）* 没显示条数 ； 
3. 注意事项 ：limit 分页操作是一个mysql的方言。

案例：


-- 分页查询 开始 

SELECT * FROM student3 LIMIT 0 , 3;  -- 第一页 

SELECT * FROM student3  LIMIT 3 , 3;  -- 第二页

SELECT * FROM student3  LIMIT 6 , 3; -- 第三页 

-- 分页查询 结束

2. 数据库的约束

概念：对表中的数据进行限定，保证数据的正确性、有效性、完整性。
分类： a.主键约束 primary key ；b.非空约束 not null; c.唯一约束 unique; d. 外键约束 foreign key 。

2.1 非空约束

某一列的值不能为null 。
在创建表时添加约束 :

-- 非空约束 开始 
-- 创建表并为name字段添加非空约束 
CREATE TABLE stu (
    id INT  , 
    NAME VARCHAR(20) NOT NULL
) ;

-- 删除name字段的非空约束 
ALTER TABLE stu MODIFY NAME VARCHAR(20);

ALTER TABLE stu CHANGE NAME NAME VARCHAR(20); -- 修改表字段的另一种写法 

-- 创建完成表之后为某个字段添加非空约束 
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
ALTER TABLE stu CHANGE NAME NAME VARCHAR(20) NOT NULL;
-- 非空约束 结束

2.2 唯一约束 unique

唯一约束：unique，某一列的值不能重复。
语法：

create table 表名 ( 字段1 数据类型1, 字段2 数据类型2 unique , .... , 字段n 数据类型n);

注意：

1. 唯一约束可以有null值，但是只能有一条数据为null。

案例：

-- 唯一约束 开始

-- 1. 在创建表时添加约束 
CREATE TABLE stu (
  id INT , 
  phone_num VARCHAR(20) UNIQUE
);

-- 2. 删除表中字段的唯一约束 
ALTER TABLE stu MODIFY phone_num VARCHAR(20); -- 唯一约束通过这样的方式无法删除  无效
SELECT *FROM stu ;
ALTER TABLE stu DROP INDEX phone_num; -- 正确删除唯一约束的方式

-- 唯一约束 结束

2.3 主键约束

注意：

- primary key ： 非空且唯一；
- 一张表只能有一个字段为主键；
- 主键就是一张表中记录的唯一标识；

语法:

create table 表名 (
  id int primary key ,
  name varchar(20)
);

案例：

-- 主键约束 开始 

-- 1. 创建表时添加主键 
DROP TABLE stu;

CREATE TABLE stu (
    id INT PRIMARY KEY ,
    NAME VARCHAR(20)
);

SELECT * FROM stu;

-- 2. 删除主键 
 -- 错误写法 alter table stu modify id int;
 
ALTER TABLE stu DROP PRIMARY KEY;

-- 3. 创建完成表之后添加主键 
ALTER TABLE stu  MODIFY id INT PRIMARY KEY;

-- 主键约束 结束

2.4 自动增长

概念：如果某一列的值是数字类型的，使用 auto_increment 可以完成值的自动增长。主要配合int 类型的主键一起使用。
创建表时添加主键约束，完成主键的自动增长：

create table stu (
  id int primary key auto_increment, -- 给id添加主键约束并完成主键自动增长
  name varchar(20)
);

注意:自动增长的时候自动增长的值只和上一条数据有关系，将会读取上一条数据id的值，然后进行加一操作。
案例：

 -- 自动增长 开始 
DROP TABLE stu;
-- 在创建表时 添加主键约束 并完成主键自动增长 
CREATE TABLE stu (
  id INT PRIMARY KEY AUTO_INCREMENT, -- 给id添加主键约束并完成主键自动增长
  NAME VARCHAR(20)
);

SELECT * FROM stu;

INSERT INTO stu VALUES(NULL , '张三');

-- 删除自动增长 
ALTER TABLE stu MODIFY id INT;

INSERT INTO stu VALUES(20 , '李四'); 

-- 创建完成表之后 设置自动增长 
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

INSERT INTO stu VALUE(NULL , '王五'); -- 这里的id 将会是多少呢 ？ 这里的id将会根据上一个的id进行加 一 就是 21
-- 自动增长 结束

2.5 外键约束 foreign key

外键约束 foreigin key ，让表与表之间产生关系，从而保证数据的正确性。外键值可以为null，但是不可以为不存在的值。

语法：

create table 表名 (
    字段1 数据类型 1,
   ... ,
  字段n 数据类型n ,
constraint 外键名称 foreign key （外键列名称） references 主表名称 （主表列的名称）
);

案例：


-- 外键约束 开始 

CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_name VARCHAR(30),
dep_location VARCHAR(30)
);
-- 添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');

-- 上表中的数据出现了冗余 


-- 解决 
-- 解决方案：分成 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 -- 外键对应主表的主键
);

-- 添加 2 个部门
INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳');
SELECT * FROM department;
-- 添加员工,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;

-- 1. 在创建表时可以添加外键 
 DROP TABLE emp;
 DROP TABLE employee;
 DROP TABLE department;
 
-- 需要被设置为外键的表一定需要先存在  所以需要先创建 部门表 
 CREATE TABLE department(
    id INT PRIMARY KEY AUTO_INCREMENT,
    dep_name VARCHAR(20),
    dep_location VARCHAR(20)
);

CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT, -- 外键对应主表的主键
CONSTRAINT emp_dept_fk FOREIGN  KEY (dep_id) REFERENCES department (id)
);

-- 添加 2 个部门
INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳');
SELECT * FROM department;
-- 添加员工,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);
 
-- 2. 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk; 

-- 3. 在创建完成表之后添加外键 
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department (id);

-- 外键约束 结束

2.6 外键约束级联操作

语法：

- 级联更新 ： create table (
    字段1 数据类型1 , 
     ... ,
    字段n 数据类型n ,
    constraint 外键名称 foreign  key (外键列名称)  references 主表名称 （主表列的名称）  on update cascade
); 

- 在创建完成表之后设置表的级联更新操作 、级联删除 
alter table 表名  add constraint 外键名称 foreign key （外键字段名称）references 主表名称 （主表列名称）级联更新 （ON UPDATE CASCADE） 级联删除（ ON DELETE  CASCADE）

案例:

-- 级联操作 开始

-- 删除员工表的外键 
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

-- 1. 添加外键之后，设置级联更新操作
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department (id) ON UPDATE CASCADE;

SELECT * FROM department;
SELECT * FROM employee;

-- 2. 设置 级联删除操作  和 级联更新操作 
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department (id) ON DELETE CASCADE ON UPDATE CASCADE;

-- 级联操作 结束

3. 多表之间的关系

一对一：

- 一个人只有一张身份证。

一对多（多对一）：

- 部门和员工 的关系。

多对多的关系：

- 学生和课程的关系。

3.1 实现一对多和多对一的关系

实现方式：在多的一方的建立外键指向一的一方的主键即可。

一对多关系

3.2 实现多对多的关系

实现多对多的关系需要建立一张中间表；
多对多的关系实现需要借助第三张中间表，中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键；
中间表中的，每一条数据是不能重复的。所以第三张中间表叫做联合主键。

多对多关系

3.3 一对一的关系

实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方主键。需要让外键唯一，加一个约束 unique。

一对一关系表

3.4 多表关系案例

分类表；

-- 旅游线路分类表 
-- 创建旅游线路分类表 tab_category
-- cid 旅游线路分类主键，自动增长
-- cname 旅游线路分类名称非空，唯一，字符串 100
CREATE TABLE tab_category (
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(100) NOT NULL UNIQUE
);

线路表；

-- 旅游线路表 
/*
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)
);

用户表；

-- 用户表 
/* 创建用户表 tab_user
uid 用户主键，自增长
username 用户名长度 100，唯一，非空
password 密码长度 30，非空
name 真实姓名长度 100
birthday 生日
sex 性别，定长字符串 1
telephone 手机号，字符串 11
email 邮箱，字符串长度 100
*/
CREATE TABLE tab_user (
uid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(100) UNIQUE NOT NULL,
PASSWORD VARCHAR(30) NOT NULL,
NAME VARCHAR(100),
birthday DATE,
sex CHAR(1) DEFAULT '男',
telephone VARCHAR(11),
email VARCHAR(100)
);

收藏表;

-- 收藏表 
/*
创建收藏表 tab_favorite
rid 旅游线路 id，外键
date 收藏时间
uid 用户 id，外键
rid 和 uid 不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
*/
CREATE TABLE tab_favorite (
rid INT,
DATE DATETIME,
uid INT,
-- 创建复合主键
PRIMARY KEY(rid,uid),
FOREIGN KEY (rid) REFERENCES tab_route(rid),
FOREIGN KEY(uid) REFERENCES tab_user(uid)
);

多表之间关系图

多表关系案例

4. 范式

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

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

分类：第一范式、第二范式、第三范式；

范式样表

4.1 第一范式

第一范式（1NF）：每一列都是不可分割的原子数据项；
存在问题：存在非常严重的数据冗余。数据添加存在问题，添加新开设系和系主任时，数据不合法。数据删除存在问题。
第一范式存在的问题：

- 数据存在严重的冗余。
- 数据添加存在问题，添加新开设的系和系主任时，数据不合法；
- 删除数据存在问题，某位同学毕业了，删除数据将会将该同学所在系一起删除。

第一范式样表

4.2 第二范式

第二范式：在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）；
存在问题 :

 - 数据添加存在问题，添加新开设的系和系主任时，数据不合法；
-  删除数据存在问题，某位同学毕业了，删除数据将会将该同学所在系一起删除。

4.2.1 几个概念

函数依赖：A-> B，如果通过A属性的值，可以确定唯一B属性的值。则称B依赖于A。例如：学号 --> 姓名。（学号，课程名称） -- > 分数
完全函数依赖: A---> B 如果A是一个属性组 ,则B属性值的确定需要依赖于 A 属性组中所有的属性值。例如：（学号，课程名称） --> 分数学号和课程名称被分数完全函数依赖。
部分函数依赖，A-->B 如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中的某一些值即可。例如：（学号，课程）--> 姓名
传递函数依赖:A-->B B --> C .如果通过A属性（属性组）的值，可以唯一确定B属性的值，再通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递依赖于A。例如：学号 --> 系名系名 --> 系主任.
码：如果在一张表中，一个属性或属性组，被其他所有属性完全依赖，则称这个属性（属性组）为该表的码。例如：该表中码为（学号，课程名称）
候选码：码属性组中的所有属性。
非码属性：除过码属性组中的属性。

满足第二范式

4.3 第三范式

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

第三范式设计

第三范式主要是要消除 函数依赖；

5. 数据库的备份与还原

命令行的方式；

备份语法: mysqldump -u用户名 -p密码 需要备份的数据库名称  > 保存的路径
还原语法：登录数据库； 创建数据库；使用数据库；执行文件。source 文件的路径

图形化界面的方式；

【MySQL】02 - MySQL约束

【MySQL】02 - MySQL约束

1. DQL 查询语句的使用

1.1 排序查询

1.2 聚合查询

1.3 分组查询

1.4 分页查询

2. 数据库的约束

2.1 非空约束

2.2 唯一约束 unique

2.3 主键约束

2.4 自动增长

2.5 外键约束 foreign key

2.6 外键约束级联操作

3. 多表之间的关系

3.1 实现一对多和多对一的关系

3.2 实现多对多的关系

3.3 一对一的关系

3.4 多表关系案例

4. 范式

4.1 第一范式

4.2 第二范式

4.2.1 几个概念

4.3 第三范式

5. 数据库的备份与还原

【MySQL】02 - MySQL约束

1. DQL 查询语句的使用

1.1 排序查询

1.2 聚合查询

1.3 分组查询

1.4 分页查询

2. 数据库的约束

2.1 非空约束

2.2 唯一约束 unique

2.3 主键约束

2.4 自动增长

2.5 外键约束 foreign key

2.6 外键约束级联操作

3. 多表之间的关系

3.1 实现一对多 和 多对一的关系

3.2 实现 多对多的关系

3.3 一对一的关系

3.4 多表关系案例

4. 范式

4.1 第一范式

4.2 第二范式

4.2.1 几个概念

4.3 第三范式

5. 数据库的备份与还原

3.1 实现一对多和多对一的关系

3.2 实现多对多的关系