MySQL表的增删改查（进阶）

博主主页: 码农派大星.

**数据库专栏:**MySQL数据库

关注博主带你了解更多MySQL数据库知识

1. 新增

 create table student (id int, name varchar(20));
 create table student2 (id int, name varchar(20));
 insert into student values(1,'张三'),(2,'李四'),(3,'王五'),(100,'赵六');
 insert into student2 select *from student where id<50;

2. 查询

1 聚合查询

1.1 聚合函数

count:

select count(*) from exam;

插入一行空值,查询也会算一行,所以查询为8

如果查询实在的属性 ,null则不算一行.

还有以下操作,大家可以看看:

sum:

SELECT SUM(列名) FROM exam;

必须确保求和的每一列为数字.

求和不为数字时为0,并报告有警告:

并且在sum求和中,遇见null会直接跳过.

sum也会逐个列相加求总和:

avg:

SELECT avg(列名) FROM exam;

max和min也是如此操作.

2 GROUP BY子句

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

统计员工类型与人数:

求各个岗位平均薪资;

求各个岗位平均,最高,最低工资:

求各个岗位平均工资并按降序排序:

搭配条件来使用:

分组之前的条件查询:

除去张三后的平均工资查询:

3.HAVING

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

分组之后的条件查询:

查询平均薪资,排除超过5w的记录:

一个查询,可以同时包含分组之前,分组之后的条件

统计平均薪资,除去张三也排除超过5w的:

3.联合查询

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：

1.内连接

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

（1）查询“许仙”同学的 成绩

判断完,进行笛卡尔积:

指定条件,筛选无用信息:

进一步指定条件,再筛选:

针对查询的列,精简:

另一个写法(join):

（2）查询所有同学的总成绩，及同学的个人信息：

先明确信息来自那些表?

再笛卡尔积:

筛选:

分组聚合: 完成:

join写法:

（3）查询所有同学的成绩，及同学的个人信息：

根据需求,查看需要查询哪些信息;

课程的名字 ,成绩 和学生的姓名

再进行笛卡尔积

指定连接条件: 进行精简:

通过join 写:

-- 左外连接，表1完全显示
select 字段名  from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;

2.外连接

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

-- 左外连接，表1完全显示
select 字段名  from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;

我们修改一下值就会发现,有一条数据已经不是内链接了.

外连接就是join这样的写法(不支持from多个表)

给join前面加上left/right 就是外连接了

左外连接:

就是以左表为基准,确保左表中的每个数据出现在最终结果中

如果左表中的记录,再对应右表中没有数据,就会显示右表为NULL.

右外连接:

就是以右表为基准,确保右表中的每个数据出现在最终结果中

如果右表中的记录,再对应左表中没有数据,就会显示左表为NULL.

3 自连接

自连接是指在同一张表连接自身进行查询

显示所有“计算机原理”成绩比“Java”成绩高的成绩信息(行与行之间比较):

这里我们需查询行与行之间的关系发现会报错

其实我们各取别名就可以了

然后指定连接条件,去掉无效数据

这样的结果说明只有三个同学同时具有计算机原理和java课程.

最终我们就查出来了

4 子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

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

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

select * from student where classes_id=(select classes_id from student where
name='不想毕业');

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

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

-- 使用IN
select * from score where course_id in (select id from course where
name='语文' or name='英文');
-- 使用 NOT IN
select * from score where course_id not in (select id from course where
name!= '语文' and name!='英文');

4 合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION 和UNION ALL时，前后查询的结果集中，字段需要一致。

union

虽然结果一样,但是这是在相同的表中,union也可在不同表中操作

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

union all

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