【MySQL】part2_create table players-CSDN博客

本文链接：https://blog.csdn.net/weixin_46932548/article/details/106389025

【MySQL】part2

DQL：查询语句【重点】

语法：

select
    字段列表
from
    表名列表
where
    进入分组的条件
group by
    分组字段
having
    分组之后的条件
order by
    排序
limit
    分页限定

基础查询

多个字段的查询：如：select name,age from student;– 字段之间用逗号分隔

注意：如果查询所有字段，则可以使用*代替字段列表
去除重复，使用DISTINCT关键字：如：select distinct gender from student;
计算列：如：select name,english,math,math+english from student;
注意：如果有null参与运算，计算结果都为null，这时候需要ifnull(表达式1,表达式2)

ifnull(表达式1,表达式2)：
- 表达式1：哪个字段需要判断是否为null
- 表达式2：如果该字段为null后的替换值
起别名：在字段后加AS 别名或者直接省略AS，即在字段后加(空格)别名，方便阅读

如：select name as 名字,age 年龄 from student;

条件查询

where子句后跟条件；

运算符：

比较运算符	说明
`>、<、>=、<=、=、<>`	`<>`在SQL中表示不等于，在MySQL中也可以使用`!=`，没有`==`
`BETWEEN...AND...`	在一个范围之内，如`between 100 and 200`，相当于条件在100到200之间，包含首尾
`IN(集合)`	集合表示多个值，使用逗号分隔
`LIKE "张%"`	模糊查询
`IS NULL`	查询某一列为NULL的值，注：不能写=NULL

逻辑运算符	说明
`and` 或 `&&`	与，SQL中建议使用前者，后者并不通用
`or` 或 `\|\|`	或
`not` 或 `!`	非

模糊查询（Like）

例如：要查询班里所有姓马的同学有哪些，可以用到模糊查询，关键字Like。

占位符：

_：表示一个任意字符的占位
%：多个任意字符的占位（包括0个字符）

实例：

-- 查询姓赵的有哪些：
select * from student where name like "赵%";

-- 查询名字中第二个字是阳的人：
select * from student where name like "_阳%";

-- 查询名字由三个字组成的有哪些：
select * from student where name like "___";

-- 查询名字中有过的人：
select * from student where name like "%过%";

排序查询

语法：order by 排序字段1 排序方式1,排序字段2 排序方式2,...排序字段n 排序方式n;

默认升序语法：order by 排序字段1;

排序方式：

ASC：升序，默认的(ascend)；
DESC：降序（descend）。

注意：如果有多个排序条件，则当前面的条件值一样时，才会判断第二条件。

聚合函数

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

语法：select 聚合函数(列名) from 表名;

主要有以下几种集合函数：

count：计算个数
- 一般选择非空的列：主键
- count(*)：只要这一行有一个数据不为NULL，就计算
max：计算最大值
min：计算最小值
sum：计算和
avg：计算平均值

注意：聚合函数的计算会排除NULL值。

解决方案：

选择不包含NULL的列进行计算；
使用IFNULL函数，语法：select 聚合函数(ifnull(列名,0)) from 表名;

分组查询

语法：group by 分组字段;

注意：

分组之后查询的字段：分组字段、聚合函数；
- 如：select gender,avg(score),count(gender) from student group by gender;
WHERE和HAVING的区别：
- where在分组之前进行限定，如果不满足条件，则不参与分组；having在分组之后进行限定，如果不满足结果，则不会被查询出来；
- where后不可以跟聚合函数，而having可以进行聚合函数的判断。
- 如：select gender,avg(score),count(gender) number from student where score > 70 group by gender having number >= 1;

分页查询

语法：limit 开始的索引，每页查询的条数;

公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数

注意：limit语法是一个MySQL的方言。

约束

约束：对表中的数据进行限定，保证数据的正确性、有效性和完整性。

分类：

主键约束：primary key
非空约束：not null
唯一约束：unique
外键约束：foreign key

非空约束not null

创建表时添加非空约束：

create table stu(
    id int,
    name varchar(20） not null -- 添加了非空约束
   );

删除字段的非空约束

 alter table stu modify name varchar(20);

创建表之后，添加非空约束

alter table stu modify name varchar(20) not null;

唯一约束unique

创建表时添加唯一约束：

create table stu(
    id int,
    phone_number varchar(20) unique -- 添加了唯一约束
    );

删除字段的唯一约束：

alter table stu drop index phone_number; --删除唯一约束（唯一索引）

创建表之后，添加唯一约束：

alter table stu modify phone_number varchar(20) unique;

注意：在MySQL中，唯一约束限定的列的值可以有多个NULL。

主键约束primary key

创建表时添加主键约束：

create table stu(
    id int primary key, -- 给id添加了主键约束
    name varchar(20)
    );

删除字段的唯一约束：

alter table stu drop primary key; -- 一张表中只有一个字段为主键，所以不需要指定字段

创建表之后，添加唯一约束：

alter table stu modify id int primary key;

注意：

含义：表示非空且唯一；
一张表只能由一个字段为主键；
主键就是表中记录的唯一标识；

自动增长auto_increment

概念：如果某一列是数值类型的，使用auto_increment可以来完成值的自动增长。

创建表时，添加主键约束，并完成主键自增长：

create table stu(
    id int primary key auto_increment,
    name varchar(20)
	);

删除字段的自动增长：
```
alter table stu modify id int;
```

创建表之后，添加唯一约束：

alter table stu modify id int auto_increment;

外键约束foreign key

外键约束：foreign key，让表与表产生关系，从而保证数据的正确性。

创建一个队伍表：

create table teams(
    id int primary key auto_increment,
    name varchar(30) unique,
    city varchar(30) not null
);

向队伍表中添加球队：

insert into teams values(null,'Lakers','Los Angeles');
insert into teams values(null,'Nets','Brooklyn');

展示队伍表中的球队数据：

select * from teams;
+----+--------+-------------+
| id | name   | city        |
+----+--------+-------------+
|  1 | Lakers | Los Angeles |
|  2 | Nets   | Brooklyn    |
+----+--------+-------------+

现在新建一个球员表，想将球员表中添加字段与队伍表关联：

create table players(
    -> id int primary key auto_increment,
    -> name varchar(20) not null,
    -> number int not null,
    -> team_id int
    -> );

这时虽然球员表中有字段team_id记录球员的球队信息，但是此字段跟球队表中的id并无关联。当球队表中的球队解散时，球员表中的球队信息仍指向该球队。存在问题。

所以我们需要学习外键约束。

语法：

constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)

创建表时添加外键约束：

create table players(
    id int primary key auto_increment,
    name varchar(30) not null,
    number int not null,
    team_id int,
    constraint teams_players_fk foreign key (team_id) references teams(id)
    );

删除字段的外键约束：

alter table players drop foreign key teams_players_fk;

创建表之后，添加外键约束：

alter table players add constraint fk foreign key (team_id) references teams(id);

级联操作

在外键约束相关联的两个表中，若主表中外键约束所指向的列数据想要更改，需要将外键约束的子表中对应数据设置为null，列数据更改后再将子表中对应数据从null改回修改成的值，这么做非常的麻烦。

外键约束设置时，可以设置级联更新，语句为：

constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) on update cascade;

相应的，级联删除的操作为：

constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) on delete cascade;

级联更新与级联删除可同时设置：

alter table players add constraint fk foreign key (team_id) references teams(id) on update cascade on delete cascade;

数据库的设计

多表之间的关系

数据库中表与表之间存在三种关系：

**一对一【了解】：**如人与身份证之间的关系，一一对应的关系。
一对多（或多对一）【重要】:如部门和员工，一个部门有多个员工，一个员工只能对应一个部门。
**多对多：**如学生和课程的关系，一个学生可以选择很多门课程，一个课程也可以被很多学生选择。

一对多关系实现

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

如前面外键约束中的例子，球队表与球员表之间的关系就是一对多的关系。

一个球队可以有多个球员，一个球员只能在一只球队中。

因此，可以在球员表中添加外键约束指向球队表的主键。

多对多关系实现

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

如，学生与课程。

一对一关系实现【了解】

实现方式：可以在任意一方添加外键指向另一方的主键，并让外键唯一（unique）。

多表关系_案例

案例：在浏览旅游网站时，网站中会对旅游线路有一些分类。每个分类内会对应一些旅游线路。我们（用户）可以对自己喜欢的旅游线路进行收藏。

在这个案例中，主要有三个表，分别是：

旅游线路分类表；
旅游线路表；
用户表。

其中，旅游线路分类表与旅游线路表是一对多的关系，旅游线路表与用户表是多对多的关系。

在这里插入图片描述

范式

范式：数据库设计的准则（范式）。

分类：

第一范式（First Normal Form)：每一列都是不可分割的原子数据项

第二范式（Second Normal Form)：在1NF基础上消除非主属性对主码的部分函数依赖

第三范式（Third Normal Form)：在2NF基础上消除传递依赖

三大范式详解

学号	姓名	系名	系主任	课程名称	分数
10010	张无忌	经济系	张三丰	高等数学	95
10010	张无忌	经济系	张三丰	大学英语	87
10010	张无忌	经济系	张三丰	计算机基础	65
10011	令狐冲	法律系	任我行	高等数学	83
10011	令狐冲	法律系	任我行	大学英语	87
10011	令狐冲	法律系	任我行	计算机基础	65
10012	杨过	法律系	任我行	高等数学	95
10012	杨过	法律系	任我行	大学英语	87
10012	杨过	法律系	任我行	计算机基础	89

以上表格存在的问题：

存在非常严重的数据冗余（重复）：姓名、系名、系主任
数据添加存在问题：添加新开设的系和系主任时，数据不合法
数据删除存在问题：张无忌同学毕业了，删除数据，会将系的数据也一起删除

关于三大范式的几个概念：

函数依赖：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。
- 例如：学号 --> 系名，系名 --> 系主任
码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性（属性组）为该表的码
- 例如：该表中的码：（学号，课程名称）
  - 主属性：码属性组中的所有属性
  - 非主属性：除去码属性组的属性

因此，根据第二范式，在1NF基础上消除非主属性对主码的部分函数依赖，改为完全函数依赖：

选课表

学号	课程名称	分数
10010	高等数学	95
10010	大学英语	87
10010	计算机基础	65
10011	高等数学	83
10011	大学英语	87
10011	计算机基础	80
10012	高等数学	77
10012	大学英语	76
10012	计算机基础	89

学生表

学号	姓名	系名	系主任
10010	张无忌	经济系	张三丰
10010	张无忌	经济系	张三丰
10010	张无忌	经济系	张三丰
10011	令狐冲	法律系	任我行
10011	令狐冲	法律系	任我行
10011	令狐冲	法律系	任我行
10012	杨过	法律系	任我行
10012	杨过	法律系	任我行
10012	杨过	法律系	任我行

这就解决了上面的第一个问题，但是在学生表中，后面的两个问题依然没有解决。

这时候就要用到第三范式：在2NF基础上消除传递依赖：系主任传递依赖于学号，因此要做以下改进：

学生表

学号	姓名	系名
10010	张无忌	经济系
10010	张无忌	经济系
10010	张无忌	经济系
10011	令狐冲	法律系
10011	令狐冲	法律系
10011	令狐冲	法律系
10012	杨过	法律系
10012	杨过	法律系
10012	杨过	法律系

系表

系名	系主任
经济系	张三丰
经济系	张三丰
经济系	张三丰
法律系	任我行
法律系	任我行
法律系	任我行
法律系	任我行
法律系	任我行
法律系	任我行

这就完全解决了上面的三个问题。

数据库的备份和还原

命令行方式：
- 备份语法：mysqldump -u用户名 -p密码数据库名称 > 保存的路径
- 还原：
  1. 登录数据库
  2. 创建数据库
  3. 使用数据库
  4. 执行文件，语法：source 文件路径
图形化工具：
- 备份：对要进行备份的数据库右击点备份\导出
  狐冲 | 法律系 |
  | 10011 | 令狐冲 | 法律系 |
  | 10011 | 令狐冲 | 法律系 |
  | 10012 | 杨过 | 法律系 |
  | 10012 | 杨过 | 法律系 |
  | 10012 | 杨过 | 法律系 |

系表

系名	系主任
经济系	张三丰
经济系	张三丰
经济系	张三丰
法律系	任我行
法律系	任我行
法律系	任我行
法律系	任我行
法律系	任我行
法律系	任我行

这就完全解决了上面的三个问题。

数据库的备份和还原

命令行方式：
- 备份语法：mysqldump -u用户名 -p密码数据库名称 > 保存的路径
- 还原：
  1. 登录数据库
  2. 创建数据库
  3. 使用数据库
  4. 执行文件，语法：source 文件路径
图形化工具：
- 备份：对要进行备份的数据库右击点备份\导出
- 还原：执行SQL脚本。