Mysql的基本操作二-CSDN博客

本文链接：https://blog.csdn.net/m0_73844340/article/details/141788320

四约束constraint（重点掌握）

4.1 概念

完整性约束条件，用于对表中的字段值进行限制，必须随时遵守指定的约束规则。这样可以保证数据的一致性和正确性。

完整性约束条件，简称约束。
是一种强制性的校验手段
只针对于DML操作。不符合约束条件，直接报错不执行。
这些约束，尽量在建表期间指定好，避免在表中已经有数据的情况下修改(可能修改失败)

定义期间，约束定义在指定列的后面，称之为列级约束，定义在表的最后，称之为表级约束

4.2 默认值约束

在建表期间，可以给字段设置有默认值。这样在插入数据时，该字段如果没有指定新的值，数据库就会自动为这个字段插入默认值。

default value
建表时写法

create table 表名(
...
字段名字段类型 default 值,
...
);
建表后修改

alter table 表名 modify 字段名字段类型 default value;
建表后取消默认值约束

alter table 表名 modify 字段名字段类型;

4.3 非空约束

当想要限定字段的值不能为null时，可以使用非空约束

not null ，简称NN
建表写法

create table 表名(
...
字段名字段类型 not null,
...
);
表后修改

alter table 表名 modify 字段名字段类型 not null;

4.4 唯一性约束

当想要限定字段的值不能重复时，可以使用唯一性约束，不过可以为null(无穷大不等于无穷大)

unique，简称UK
建表时列级约束写法

create table 表名(
...
字段名字段类型 unique,
...
);
建表时表级约束写法

create table table_UK_1(
...
...
constraint 约束名字 unique(字段,...)
);
多个字段组合成唯一约束

create table user(
...
...
constraint 约束名字 unique(字段1,字段2)
);
建表后修改

alter table 表名 modify 字段名字段类型 unique;
取消唯一索引

alter table 表名 drop index 唯一索引名称
查看唯一索引的名字

show create table 表名

4.5 主键约束

主键约束，用于标识表中的每一条记录都是唯一的。主键和记录的关系，如同身份证和人的关系。主键用来标识每个记录，每个记录的主键值都不同。身份证用来表明人的身份，每个人都具有唯一的身份证号。

主键的主要目的是帮助数据库管理系统以最快的速度查找到表的某一条信息。

primary key，简称PK
唯一且非空
主键可以由一个字段组成，也可以由多个字段组成
如果主键可以由一个字段组成既可以添加到列级也可以添加到表级，但是如果由多个字段组成只能添加到表级

建表时列级约束语法：

create table 表名(
...
字段名 primary key,
...
);

建表时表级约束语法

create table 表名(
...
...
constraint 主键约束名字 primary key(字段1,....)
);

建表后语法：

alter table 表面 add primary key(字段名1,....);
alter table 表名 modify 字段名字段类型 primary key;

刪除主键约束，注意非空约束还在

alter table tableName drop primary key;

选择主键约束的字段要求：

1.对业务需求没有意义的字段，比如序号
2.不建议对动态赋值的字段进行设置，比如时间戳。
3.如果设置了主键约束，那么此字段最好不好人为的修改。而是自动生成。使用auto_increment（自增序列）

4.6 自增键约束

在MySQL中，可通过关键字auto_increment为列设置自增约束。能设置自增约束的条件如下:

列的类型必须是整型
该列必须设置为主键约束或者唯一性约束
向数据库表中插入新记录时，字段上的值默认的初始值1，每增加一条记录，该字段的值会增加1;
一个表中只能有一个自增约束

create table 表名(
...
字段名 int primary key auto_increment,
...
);

建表后语法：

alter table 表名 modify 字段名字段类型 auto_increment;

取消自增约束

alter table 表名 modify 字段名字段类型;

设置初始值

alter table tableName auto_increment = 8;

4.7 外键约束

外键约束是为了保证多个表（通常为两个表）之间的参照完整性，即构建两个表的字段之间的参照关系。

外键约束，指字段A的值，依赖于字段B的值。这两个字段可以在同一张表中，也可以在不同的表中。字段A所在的表称之为子表，字段B所在的表称之为父表。字段A的值也可以为null. 字段B必须为主键约束。

foreign key,简称FK
建表时写法

create table tableName(
...
constraint 外键约束名称 foreign key(字段A) references 表名2(字段B)
);
建表后写法

alter table 表名1 add constraint 约束名称 foreign key(字段A) references 表名2(字段B)

注意：

目前外键，存储函数，存储过程，触发器…这些在后台开发过程中基本是不用的，因为这些限制逻辑或者代码逻辑是由mysql本身控制的，一个后端服务器的性能首先到瓶颈的是存储层模块，所以要把核心逻辑给mysql作，各个表的关系要放到业务层，不给mysql增加负担，外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。

4.8 检查约束

检查性约束，用于对表中的字段进行条件限制。比如余额不能小于0，性别必须是f和m等。mysql8.0.15以前是不支持这个约束的，从8.0.16这个版本才开始支持。

check，简称ck。
建表时语法

create table tableName(
tid int,
tname varchar(20),
tgender char(1) check(tgender in('f','m'))
);

之前的版本可以使用枚举代替

create table temp_6(
tid int,
tname varchar(20),
tgender enum('f','m') #枚举写法
);
insert into temp_6 values (1001,'zs','m');

删除检查性约束：

alter table temp_5 drop check ;

五关联查询（多表查询）《重点掌握》

5.1 概念：

有的时候，我们的业务需求的数据不只是在一张表中，而是在两张或两张以上的表中，而这些表中通常都会存在着“有关系"的字段。那么此时的查询操作涉及到多表查询，我们称之为关联查询。

案例：最简单的关联查询

select * from emp,dept;
select * from emp join dept;

笛卡尔积

- 当做关联查询时，如果两张表中不存在关联字段，或者忘记写关联条件。那么会出现如下图所示的结果。
- 即表A中的每一条记录都回与表B中的所有记录进行匹配组合。例如表A中有m条记录。表B中有N条件。那么匹配组合的记录数目为M*N，此值被称之为笛卡儿积。
- 通常没有意义。

5.2 写法分类

在进行多表关联查询时，通常有两种写法：

第一种：在from子句中，直接写多个表名，表名之间使用逗号分隔开，如：

select A.*,B.*,C.* from A,B,C where 关联条件

第二种：在from子句中,多个表名之间使用join关键字连接，并在on关键字后面添加关联条件。

select A.*,B.*,C.* from A join B on 关联条件
select A.*,B.*,C.* from A join B on AB关联条件 join C on AC或BC关联条件
select A.*,B.*,C.* from A join B join C on AB的关联条件 and BC或AC关联条件

5.3 join连接分类

join关联查询操作分为两大类：内连接和外连接，而外连接有细分为三种类型。参考下图

1. 内连接: [inner] join
2. 外连接 (outer join)：（引出一个驱动表的概念：驱动表里的数据全部显示)
- 左外连接:left [outer] join, 左表是驱动表
- 右外连接:right [outer] join, 右表是驱动表
- 全外连接:full [outer] join, mysql不支持.两张表里的数据全部显示出来
3. 注意： join连接只支持等值连接

六高级关联查询（子查询）

6.1 简介

有的时候，当一个查询语句A所需要的数据，不是直观在表中体现，而是由另外一个查询语句B查询出来的结果，那么查询语句A就是主查询语句，查询语句B就是子查询语句。这种查询我们称之为高级关联查询，也叫做子查询。

子查询语句的返回数据形式：

- 返回单行单列
- 返回多行单列
- 返回单行多列
- 返回多行多列

子查询语句的位置可以在以下几个子句中：

- 在where子句中:    子查询的结果可用作条件筛选时使用的值。
- 在from子句中:     子查询的结果可充当一张表或视图，需要使用表别名。
- 在having子句中:   子查询的结果可用作分组查询再次条件过滤时使用的值
- 在select子句中:   子查询的结果可充当一个字段。仅限子查询返回单行单列的情况。

6.2 在where子句中

# 需求：查询工资大于员工编号为7369这个员工的所有员工信息。
解析：
第一步：目的是查询工资大于某一个数num的所有员工信息
select * from emp where sal>num
第二步：num的值7369员工的工资
select sal from emp where empno = 7369;
第三步：将主查询中的代词使用子查询语句替换
select * from emp where sal>(select sal from emp where empno = 7369);
# 需求：查询工资大于10号部门的平均工资的所有员工信息
select * from emp

where sal>(select avg(ifnull(sal,0)) from emp where deptno=10);
# 需求：查询工资大于10号部门的平均工资的非10号部门的员工信息。
select * from emp

where sal>(select avg(ifnull(sal,0)) from emp where deptno=10) and deptno<>10;
# 需求：查询与7369同部门的同事信息。
select * from emp

where deptno=(select deptno from emp where empno=7369) and empno<>7369;

6.3 在from子句中

# 需求：查询员工的姓名，工资，及其部门的平均工资。
解析：
第一步：先查询每个部门的平均工资
select deptno,avg(ifnull(sal,0)) from emp group by deptno;
第二步：将上一个查询语句的返回结果当成一张表，与员工表进行关联查询
select A.ename,A.sal,B.avg_sal
from emp A join (select deptno,avg(ifnull(sal,0)) avg_sal from emp group by deptno) B on A.deptno = B.deptno

6.4 在having子句中

# 需求：查询平均工资大于30号部门的平均工资的部门号，和平均工资
select deptno,avg(ifnull(sal,0))

from emp

group by deptno

having avg(ifnull(sal,0))>(select avg(ifnull(sal,0)) from emp where deptno=30);

6.5 在select子句中

相当于外连接的另外一种写法
# 查询每个员工的信息及其部门的平均工资，工资之和，部门人数
select A.empno,A.ename,A.sal,
(select avg(ifnull(sal,0)) from emp B where B.deptno=A.deptno) avg_sal,
(select sum(sal) from emp C where C.deptno=A.deptno) sum_sal,
(select count(*) from emp D where D.deptno=A.deptno) count_
from emp A;

6.6 sql完整执行顺序

select distinct..from t1 [inner|left|right] join t2 on 条件
where...group by...having...order by...limit

1. from t1
2. on 条件
3. [inner|left|right] join t2
4. where...
5. group by...
6. having...
7. select...
8. distinct...
9. order by...
10. limit....