typora-copy-images-to: img

day02-MySQL进阶

学习目标

• 能够使用SQL语句进行排序

• 能够使用聚合函数

• 能够使用SQL语句进行分组查询

• 能够使用SQL语句进行分页查询

• 能够理解并添加数据库的约束

• 能够说出多表之间的关系及其建表原则

• 能够完成数据的备份和恢复

• 了解三大范式

第一章-查询记录【重点】

环境准备

# 创建学生表(有sid,学生姓名,学生性别,学生年龄,分数列)
CREATE TABLE student(
	sid INT ,
	sname VARCHAR(40),
	sex VARCHAR(10),
	age INT,
    score DOUBLE
);

INSERT INTO student VALUES(1,'姚明','男',18,98.5);
INSERT INTO student VALUES(2,'高圆圆','女',18,96.5);
INSERT INTO student VALUES(3,'蔡徐坤','男',15,50.5);
INSERT INTO student VALUES(4,'贾静雯','女',20,98.5);
INSERT INTO student VALUES(5,'朱茵','女',18,60.5);
INSERT INTO student VALUES(6,'周杰伦','男',38,98.5);
INSERT INTO student VALUES(7,'易建联','男',18,100);
INSERT INTO student VALUES(8,'赵金麦','女',28,28);
INSERT INTO student VALUES(9,'张子枫','女',21,95);

知识点-排序查询

1.目标

• 能够使用SQL语句进行排序

2.分析

​ 有时候我们需要对查询出来的结果排序显示，那么就可以通过ORDER BY子句将查询出的结果进行排序。排序可以根据一个字段排，也可以根据多个字段排序，排序只是对查询的结果集排序，并不会影响表中数据的顺序。

3.讲解

3.1单列排序

1. 语法: 只按某一个字段进行排序，单列排序

SELECT 字段名 FROM 表名 [WHERE 条件] ORDER BY 字段名 [ASC|DESC];  //ASC: 升序，默认值; DESC: 降序

2. 练习: 以分数降序查询所有的学生

3.2组合排序

1. 语法: 同时对多个字段进行排序，如果第1个字段相等，则按第2个字段排序，依次类推

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];

2. 练习: 以分数降序查询所有的学生, 如果分数一致,再以age降序

4.小结

• order by 排序字段 排序规则
• 应用场景:
  • 电商: 销量, 价格
  • 社交: 距离, 附近的人

知识点-聚合函数

1.目标

• 能够使用聚合函数

2.分析

​ 之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个结果值。

3.讲解

聚合函数	作用
max(列名)	求这一列的最大值
min(列名)	求这一列的最小值
avg(列名)	求这一列的平均值
count(列名)	统计这一列有多少条记录
sum(列名)	对这一列求总和

1. 语法

SELECT 聚合函数(列名) FROM 表名 [where 条件];

2. 练习

-- 求出学生表里面的分数的最高分数
SELECT MAX(score) FROM student;
--  求出学生表前5个人里面的分数的最高分数
SELECT MAX(score) FROM student WHERE sid <= 5;
-- 求出学生表里面的分数的最低分数
SELECT MIN(score) FROM student;
-- 求出学生表里面的分数的总和
SELECT SUM(score) FROM student;
-- 求出学生表里面的分数的平均分
SELECT AVG(score) FROM student;
-- 统计学生的总人数
SELECT COUNT(*) FROM student;


-- 求出学生表里面的分数的平均分
SELECT AVG(IFNULL(score, 0)) '平均分' FROM student;

MySQL中的一个函数: ifnull(字段, 默认值)

4.小结

• 用来操作一列

知识点-分组查询

1.目标

• 能够使用SQL语句进行分组查询

2.分析

​ 分组查询是指使用 GROUP BY语句对查询信息进行分组

​ GROUP BY怎么分组的？ 将分组字段结果中相同内容作为一组，如按性别将学生分成两组

3.讲解

-- 查询所有数据, 按照性别分组
select * from student group by sex;

[外链图片转存失败(img-iKRkAdMZ-1566739467108)(assets/1566287462586.png)]

• 问题: 查询出来的结果是分组后, 每组的第一条数据. 所以分组查询单独使用是没有意义的, 我们一般会将分组和聚合函数配合使用.

-- 查询男女各多少人
select count(*) from student group by sex; -- 不能区别是哪一组的数据
-- 以哪个字段进行分组, 就把哪个字段查询出来.
select sex, count(*) from student group by sex;

3.1分组

1. 语法

SELECT 字段1,字段2... FROM 表名  [where 条件] GROUP BY 列 [HAVING 条件];

2. 练习:根据性别分组, 统计每一组学生的总人数

SELECT sex, COUNT(*) FROM student GROUP BY sex;

3.2 分组后筛选 having

• 练习根据性别分组, 统计每一组学生的总人数> 5的(分组后筛选)

SELECT sex, COUNT(*) FROM student GROUP BY sex HAVING COUNT(*) > 5;

4.小结

• group by 分组字段 having 过滤条件

having和where的区别:

1. where在分组前进行过滤
2. having在分组后进行过滤
3. where中不能使用聚合函数
4. having中可以使用聚合函数

知识点-分页查询

1.目标

• 掌握分页查询

2.分析

​ LIMIT是限制的意思，所以LIMIT的作用就是限制查询记录的条数. 经常用来做分页查询

3.讲解

1. 语法

select ... from .... limit a ,b.

[外链图片转存失败(img-sWdwwhvV-1566739467109)(img/tu_3.png)]

注意事项

• 如果没有数据了, 有多少显示多少

4.小结

• limit a, b
  • a: 开始的索引
  • b: 显示的个数

查询的语法小结

-- 写法
select *|字段 from 表 where 过滤条件 group by 分组字段 having 分组后的过滤条件 order by 排序字段 排序规则 limit a, b;
-- 执行
from >> where >> group by >> select >> having >> order by >> limit

第二章-约束

知识点-主键约束

1. 目标

• 知道什么是约束
• 掌握主键约束的使用

2. 分析

• 约束: 对表中的数据进行限制, 保证数据的正确性, 有效性, 完整性;
• 使用约束后, 我们可以让表中的数据必须满足某种要求, 否则会报错.

3. 讲解

3.1 主键的概念

主键

• 主键: 表中一条数据的唯一标识, 每个表都应该有且只有一个主键.
• 哪种字段应该作为主键? 通常不用业务字段作为主键，单独给每张表设计一个id的字段，把id作为主键。
• 主键的特点: 非空, 唯一

主键约束

• 格式: 在创建表的时候添加: 字段名 字段类型 primary key

  ​ 在已有表中添加: alter table 表名 add primary key(字段名)

• 作用: 添加了主键约束的字段, 要求值必须是非空唯一的.

主键自增

• 如果我们自己添加主键, 很有可能是重复的, 所以我们可以添加主键自增, 让主键自动生成(自动增长)
• 格式: 字段名 字段类型 primary key auto_increment

3.2 主键的使用

• 创建学生表

CREATE TABLE st2 (
    # 设置id字段为主键自增
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    age INT
);

• 插入数据

# 主键默认从1开始自动增长
INSERT INTO st2 (NAME, age) VALUES ('唐僧', 22);
INSERT INTO st2 (NAME, age) VALUES ('孙悟空', 26);
INSERT INTO st2 (NAME, age) VALUES ('猪八戒', 25);
INSERT INTO st2 (NAME, age) VALUES ('沙僧', 20);

• 尝试插入重复主键, 插入null主键

4. 小结

• 主键: 表中一条数据的唯一标识, 一般不会将业务字段作为主键, 会创建id当做主键
• 格式: primary key auto_increment

知识点-非空,唯一,默认值约束

1. 目标

• 知道什么是非空约束, 唯一约束和默认值约束

• 掌握非空约束, 唯一约束和默认值约束的使用

2. 分析

• 非空约束: 添加了非空约束的字段, 要求值必须不能为NULL
• 唯一约束: 添加了唯一约束的字段, 要求值必须是唯一不重复的
• 默认值约束: 添加了默认值约束的字段, 如果没有设置值, 那么会自动设置为默认值.

3. 讲解

非空约束

• 格式

  字段名 字段类型 NOT NULL

唯一约束

• 格式

  字段名 字段类型 UNIQUE

默认值约束

• 格式

  字段名 字段类型 DEFAULT 默认值

约束的演示

-- 创建相亲网站的用户表
CREATE TABLE xq_user (
	-- 主键, 自增
	id INT PRIMARY KEY AUTO_INCREMENT,
	-- 姓名, 不能为null
	NAME VARCHAR(20) NOT NULL,
	-- 电话号, 唯一不能重复
	phone_num VARCHAR(20) UNIQUE,
	-- 年龄 , 默认值
	age INT DEFAULT 80
);

4. 小结

• 非空约束: not null
• 唯一约束: unique
• 默认值约束: default 默认值

第三章-多表间的关系

知识点-为什么要拆表?

1.目标

• 知道拆表原因

2.分析

• 单表情况下, 对数据的操作是有问题的, 所以需要拆表

3.讲解

单表的情况

• 商品表, product

商品编号pid	商品名称pname	商品价格price	商品数量pcount	商品种类category
1	iphoneXsMax	10888.88	30	手机数码
2	华为P30	5688.99	70	手机数码
3	小牛N系列电动车	6999.88	20	交通工具
4	捷安特自行车	2599	10	交通工具

• 单表的弊端

  • 只想添加一个商品种类 (鞋靴箱包) >>> 拆表

• 将上面的商品表, 拆分成两张表, 一个是商品表, 一个是分类表

• 商品表, product

商品编号pid	商品名称pname	商品价格price	商品数量pcount	商品种类cid
1	iphoneXsMax	10888.88	30	1
2	华为P30	5688.99	70	1
3	小牛N系列电动车	6999.88	20	2
4	捷安特自行车	2599	10	2

• 分类表, category

分类编号cid	分类名称cname
1	手机数码
2	交通工具

• 拆表之后的问题

  • 添加新的商品, 而这个商品所属的分类信息,在分类表中不存在.

    例如: 添加商品 "AJ17"篮球鞋, 种类应该是鞋靴箱包, 这条数据就变成脏数据

  • 删除一个分类, 商品表中的数据就有一些的种类id没有对应的种类信息

    例如: 删除交通工具, 3.4这两条数据就变成了脏数据

• 解决方案:

  • 如果: 添加新商品, 而这个商品的分类信息不存在, 则添加失败
  • 如果: 删除一个分类, 而这个分类信息在商品表中在使用, 则删除失败

4.小结

知识点-外键与外键约束【重点】

1.目标

• 掌握外键的使用

2.分析

​ 表和表之间存在一种关系，但是这个关系需要谁来维护和约束？

3.讲解

3.1主表和从表

• 当表①中的数据是来源于表②时, 表①为从表, 表②为主表.

3.2 外键

1. 外键: 从表中的某个字段是来源于主表的, 那么这个字段称之为外键

2. 添加外键: 从表中添加一个外键, 指向主表的主键.

• 创建表时添加

create table 表 (
	... ,
    CONSTRAINT 外键名 foreign key(字段) references 主表(主键字段)
    
)

• 修改表时添加

alter table 表 add CONSTRAINT 外键名 foreign key(字段) references 表（字段);

3. 删除外键

ALTER TABLE 表 drop foreign key 外键名称;

3.3 外键的练习

-- 创建主表
CREATE TABLE category (
	cid INT PRIMARY KEY AUTO_INCREMENT,
	cname VARCHAR(20)
)

-- 创建从表
CREATE TABLE product (
	pid INT PRIMARY KEY AUTO_INCREMENT,
	pname VARCHAR(20),
	price DOUBLE,
	pcount INT,
	cid INT,
	CONSTRAINT pro_cid_cat_cid_fk FOREIGN KEY(cid) REFERENCES category(cid)
)

-- 添加数据
INSERT INTO category (cname) VALUES ('手机数码'), ('交通工具');

INSERT INTO product VALUES (NULL, 'iphoneX', 5399, 40, 1);
INSERT INTO product VALUES (NULL, '华为P30', 8499, 100, 1);
INSERT INTO product VALUES (NULL, '小牛电动车', 8988, 10, 2);
INSERT INTO product VALUES (NULL, '捷安特自行车', 4288, 30, 2);

-- 以下的操作都会报错
-- 添加商品信息, 这个商品信息的分类不存在
INSERT INTO product VALUES (NULL, 'AJ8', 26999, 1, 3);
-- 修改商品表中分类信息, 改为不存在的分类
UPDATE product SET cid = 3 WHERE cid = 2;
-- 修改分类表中的分类信息
UPDATE category SET cid = 3 WHERE cid = 2;
-- 删除主表中的分类信息
DELETE FROM category WHERE cid = 2;

3.4 外键约束(了解)

外键的级联操作

• 如果修改或者删除了主表中带有外键约束的字段, 会将对应的内容都修改或删除

• 语法:

  创建表时, 直接在外键后面添加: on delete cascade on update cascade
  添加外键约束时添加: alter table 表 add constraint ... on delete cascade on update cascade
  

4.小结

• 主表和从表: 表①中的数据是来源于表②, 表①是从表, 表②是主表
• 外键: 从表中来源于主表的字段, 指向主表中的主键(具有唯一约束的字段)
• 格式: constraint 外建名 foreign key(外键字段) references 主表(主键字段)

知识点-多表间关系

1.目标

• 能够说出多表之间的关系及其建表原则

2.分析

​ 现实生活中，实体与实体之间肯定是有关系的，比如：老公和老婆，部门和员工，老师和学生等。那么我们在设计表的时候，就应该体现出表与表之间的这种关系！分成三种：

1. 一对多
2. 多对多
3. 一对一

3.讲解

3.1 一对多【重点】

• 一对多(1 : n): 例如: 班级和学生, 部门和员工, 客户和订单, 分类和商品

[外链图片转存失败(img-MC63m9xw-1566739467110)(assets/1566296705976.png)]

3.2 多对多【重点】

• 多对多(n : m) 例如: 老师和学生, 学生和课程, 用户和角色

[外链图片转存失败(img-miA9jnWN-1566739467110)(assets/1566296716242.png)]

3.3一对一【了解】

• 一对一(1:1) 例如: 老公和老婆, 用户和用户信息

4.小结

• 一对多: 在多的一方创建一个外键, 指向一的一方的主键
• 多对多: 创建一张第三方表, 该表至少有两个字段,这两个字段分别作为外键, 指向另外两张表的主键

第四章-数据的备份和还原

实操-数据的备份和还原

1.目标

• 掌握数据的备份和还原

2.分析

​ 在服务器进行数据传输、数据存储和数据交换，就有可能产生数据故障。比如发生意外停机或存储介质损坏。这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的。

3.讲解

3.1命令行方式

1. 备份格式

mysqldump -u用户名 -p密码 数据库 > 文件的路径

2. 还原格式

SOURCE 导入文件的路径;

注意：还原的时候需要先登录MySQL,并选中对应的数据库

3.2.使用软件备份和还原

1. 备份

   [外链图片转存失败(img-KoblILOu-1566739467111)(assets/1566227606839.png)]

2. 还原

   [外链图片转存失败(img-2CWnn98f-1566739467111)(assets/1566227638541.png)]

4.小结

第五章-数据库设计三大范式【了解】

知识点-数据库设计三大范式

1.目标

• 能够理解三大范式

2.分析

​ 好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。

​ 建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式。

3.讲解

• 1NF: 要求所有列都是不可分割的

[外链图片转存失败(img-d2z5WJ7c-1566739467112)(assets/1566228054051.png)]

• 2NF: 要求有主键, 所有字段都依赖于主键

[外链图片转存失败(img-LLesIwXG-1566739467113)(assets/1566228483108.png)]

• 3NF: 消除依赖传递

[外链图片转存失败(img-VHuZPIsB-1566739467113)(assets/1566228524641.png)]

总结

1.查询【重点】

• 排序查询

order by 列 [asc/desc], 列 [asc/desc]

• 聚合函数(count, max,min,avg sum )

select 聚合函数 from 表

• 分组查询

group by 列 [having]

• 分页查询

limit a ,b; 

• 整个查询语法

select... from ...where...group...having...order...limit

2.约束

• 主键约束: primary key auto_increment

• 唯一约束: unique

• 非空约束: not null

• 默认值约束: default 默认值

• 外键

1. 作用: 维护多表间的关系, 保证数据的完整性
2. 语法

alter table 表 add constraint 外键约束名 foreign key(列/外键) references 表(列/主键)

3.多表的关系

1. 1对多: 在多方创建一个列作为外键指向一方的主键
2. 多对多: 新建一张中间表, 至少包含两个列都作为外键, 指向各自主键