MySQL表的增删改查（进阶）

目录

1. 数据库约束

1.1 约束类型

1.2 NULL约束

1.3 UNIQUE：唯一约束

1.4 DEFAULT：默认值约束

1.5 PRIMARY KEY：主键约束

1.6 FOREIGN KEY：外键约束

2. 表的设计

2.1 一对一

2.2 一对多

2.3 多对多

3. 新增

4. 查询

4.1 聚合查询

4.1.1 聚合函数

1.count统计所有行

2.SUM（列名）求和

3.AVG()

4.MAX(),MIN()

4.1.2 GROUP BY子句

4.1.3 HAVING

4.2 联合查询

4.2.1 内连接

1.取多张表的笛卡尔积

2.通过连接条件过滤无效数据

3.通过指定列精简结果集​​​​​​​

4.2.2 外连接

4.2.3 自连接

自连接步骤

4.2.4 子查询

单行子查询

多行子查询

【NOT】EXISTS 关键字

在FROM子句中使用子查询

4.2.5 合并查询

---

1. 数据库约束

1.1 约束类型

NOT NULL - 指示某列不能存储 NULL 值。

UNIQUE - 保证某列的每行必须有唯一的值。

DEFAULT - 规定没有给列赋值时的默认值。

PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标        识，有助于更容易更快速地找到表中的一个特定的记录。

FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。

CHECK - 保证列中的值符合指定的条件。对于 MySQL 数据库，对 CHECK 子句进行分析，但是忽略CHECK子句。

1.2 NULL约束

作用：

创建表时，可以指定某列不为空

示例，创建一个学生表，使id字段不为空：

查看表结构：

1.3 UNIQUE：唯一约束

作用：

指定 sn 列为唯一的、不重复的

示例，使student表的sn字段为唯一：

查看表结构：

如果往被unique约束的字段中插入重复的数据就会报错：

1.4 DEFAULT：默认值约束

作用：

没有指定插入数据时，MySQL将自动插入设置好的默认值。

示例，把student表中name字段的默认值设置为unkown：

查看表结构：

使用了默认值约束后，如果我们没有指定插入name数据，就会用unkown来默认替代：

1.5 PRIMARY KEY：主键约束

作用：

主键是 NOT NULL 和 UNIQUE 的结合，意义为非空且唯一。

示例，将student表的id列指定为主键，且对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大值+1：

查看表结构：

当id列没有数据插入时，该列会自动插入该列的最大数字（如果没有则为0）+1：

插入一个id为3的数据：

再使用自增插入，则自动插入的id值为4：

1.6 FOREIGN KEY：外键约束

 外键用于关联其他表的主键或唯一键，语法：

foreign key ( 字段名 ) references 主表 ( 列 )

【案例】

先创建一个主表class:

创建一个student表，使class_id字段作为外键关联主表class的id字段：

当student的class_id与class的id字段成外键关系时，student表中class_id列中插入的数据只能为class表中id字段中的值，否则会报错：

class表：

往student表中插入数据，其中class_id列中只能插入值为class表中id列里有的数据：

若插入class表中id列里不存在的数据，则报错：

当子表中存在对主表的依赖时，不可直接删除主表数据，否则会报错：

正确的删法：先删除子表中存在对主表依赖的数据，然后再删除主表的数据：

2. 表的设计

2.1 一对一

假设有两个实体：一个是用户（包括了姓名，手机号，id），一个是账号（包括 登录名，密码）。

针对1对1关系，设计表时有两种设计方式：

①把两个实体的所有信息都放在一个表里：

user（user_id，name，phone_num,username,password）;

②创建两张表，分别记录用户信息和账号信息，然后再关联起来：

user(user_id,name,phone_num);

account(username,password,user_id);

2.2 一对多

比如学生与班级的关系

一个学生只能存在一个班级，一个班级可以存在多个学生。

class（class_id，name）;

student(student_id,name,class_id);

2.3 多对多

一个学生可以选修多门课程

一个课程也可以被多名学生选修

①分别创建实体表：

course(course_id,name);

student(student_id,name,age);

②创建关系表，关联两个实体表：

student_course(id,student_id,course_id);

3. 新增

语法：

INSERT INTO table_name [( column [, column ...])] SELECT   column [, column ...] from ...

现有两个表

student表：

student2表：

其中student2表结构与student表相同，且数据为空。

将student表中的数据拷贝到student2表中：

4. 查询

4.1 聚合查询

4.1.1 聚合函数

函数	说明
COUNT(DISTINCT expr)	返回查询到的数据的数量（有多少个数据行）
SUM(DISTINCT expr)	返回查询到的数据的总和，不是数字没有意义
AVG(DISTINCT expr)	返回查询到的数据的平均值，不是数字没有意义
MAX(DISTINCT expr)	返回查询到的数据的最大值，不是数字没有意义
MIN(DISTINCT expr)	返回查询到的数据的最小值，不是数字没有意义

1.count统计所有行

count（列名），如果列中有null值，则不会统计在内：

2.SUM（列名）求和

作用：

把查询结果中所有行中的指定列进行相加

注意：列的数据类型必须是数值型，不能是字符或者日期

返回的结果在一个临时表中，结果不受表中字段长度约束(比如表中字段为decimal(5,2),返回的临时表不受此限制)

如果对非数值类型的列进行运算，会得到一些警告：

3.AVG()

对所有行的指定列进行求平均值运算

-对所有同学的语文成绩求平均值：

-求语文 数学 英语三门课的总分

-可以使用别名

4.MAX(),MIN()

求所有行中指定列的最大值，最小值

-找出语文成绩的最高分和英语成绩的最低分（多个聚合函数可以同时使用）

4.1.2 GROUP BY子句

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT中则必须包含在聚合函数中。

计算不同角色（role）工资的平均值：

ROUND（数值，小数点位数）

group by 之后可以跟order by子句

4.1.3 HAVING

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用 HAVING

where 是对表中的每一行的真实数据进行过滤的

having 是对group by之后计算出来的结果进行过滤的

找出平均工资大于一万，小于两百万的角色：

where用在form表名之后，也就是分组之前

having跟在group by子句之后

如果需求要对真实数据进行过滤，同时也需要对分组的结果进行过滤，

那么在合适的位置写where和having即可

4.2 联合查询

4.2.1 内连接

语法：（两种都可以）

select 字段 from 表 1 别名 1 [inner] join 表 2 别名 2 on 连接条件 and 其他条件 ;

select 字段 from 表 1 别名 1, 表 2 别名 2 where 连接条件 and 其他条件;

1.取多张表的笛卡尔积

语法：

select * from 表名,表名;

现有两个表：

取这两个表的笛卡尔积，最后得到的结果就是一个全排列结果集：

2.通过连接条件过滤无效数据

3.通过指定列精简结果集

可以通过起别名来精简代码量：

用第一种语法来写：

4.2.2 外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；

右侧的表完全显示我们就说是右外连接。

【案例演示】

依旧是这两个表：

现在往class表中插入一个新的班级：

由于没有学生是三班的，因此使用内连接时没用三班的数据：

使用右连接，是以join右边的表为基准，这个表中的数据会全部显示出来，左边的表没有与之匹配的记录全部用null去填充：

左外连接同理。

4.2.3 自连接

这样的表设计，可以比较一个人的各科目成绩，即表的列与列之间可以比较。

但是同样的方法，在这样的表设计中就行不通了，因为表的行与行之间不可比较：

要解决这个问题，就要用到自连接：自己与自己进行表连接。

自连接步骤

1.取笛卡尔积

2.连接条件是student_id相等

3.最后加入条件，查出计算机原理成绩大于JAVA成绩的记录

4.2.4 子查询

子查询也叫嵌套查询

子查询是把一条SQL的查询结果，当作另一条SQL的查询条件，可以嵌套很多层。

例如：select*from table1 where table1.id= (select id from table where name=...);

单行子查询

单行子查询：返回一行记录的子查询

【案例】

查出“不想毕业”同学的同班同学：

1.涉及的表

学生表：

2.先查出“不想毕业”同学的班级ID:

3.然后将这个查询结果作为条件来查询：

多行子查询

多行子查询：返回多行记录的子查询

例如：select*from table1 where table1.id IN(select id from table2 where ...);

【案例】

查询“语文”或者“英语”课程的成绩信息：

1.涉及的表

课程表 成绩表：

2.在课程表中查找“语文”和“英文”的课程编号：

3.将查询结果作为子查询的条件进行查询：

【NOT】EXISTS 关键字

语法:

select * from 表名 where exists(select*from 表名1);

其中，exists后面括号中的查询语句，如果有结果返回，则执行外层查询

如果返回的是一个空结果集，则不执行外层的查询

【案例】

这是一个结果集为空查询：

当子查询的内层返回的结果集为空，外层也返回空结果集，也可以说外层查询没有执行：

【注意】

null不等于空结果集，当查询null时，返回的结果集是一个非空的，只不过列名为null，值也为null：

当查询null作为子查询的内层时，因为内层返回了一个非空结果集，所以外层查询会执行：

在FROM子句中使用子查询

在from子句中使用子查询：把一个子查询当作临时表使用。

【案例】

查询所有比“中文系2019级3班”平均分高的成绩信息：

1.确定涉及的表：

班级表 成绩表 分数表

2.算出中文系2019级3班的平均分

3.用成绩表中的数据与子查询的平均分做比较：

4.2.5 合并查询

关键字：union，union all

作用：合并多个查询结果到一个结果集中。

例如：

上面这种情况使用or也可以达到同样的效果：

【注意】

在单表中还是推荐使用or去连接不同的查询条件

在多表中就没法用or了，必须要用union来进行合并

---

根据一张表的结构创建新表：

给student2插入两条记录：

通过union把两张表中的数据显示在一个结果集中：

当union两边的列名不匹配时则返回错误的结果集：

---

union  该操作符用于取得两个结果集的并集。当使用该操作时符时，会自动去掉结果集中的重复行

【案例】

1.涉及的表：

student表 student2表

给student2增加两条student表中的数据行：

使用union操作符合并两个表，会发现student2表中student_id为3和4（重复项）的记录行被去掉了：

---

union all  该操作符用于取得两个结果集的并集。当使用该操作时符时，不会去掉结果集中的重复行

---

完

如果哪里有疑问的话欢迎来评论区指出和讨论，如果觉得文章有价值的话就请给我点个关注还有免费的收藏和赞吧，谢谢大家！