目录
一、新增
把查询的结果新增到一张表中
示例:
创建一张学生表,包含字段:姓名,学号,班级id;再创建一张班级表,包含字段:班级id,学生学号,学生姓名。现在需要把学生信息复制到班级表中:
-
-- 创建班级表
-
create
table class(
-
id
int,
-
studentId
int,
-
studentName
varchar(
20)
-
);
-
-- 创建学生表
-
create
table student(
-
id
int
primary key auto_increment,
-
name
varchar(
20),
-
classId
int
-
);
-
-
-- 给学生表中插入数据
-
insert
into student
values
-
(
null,
'张三',
1),
-
(
null,
'李四',
1),
-
(
null,
'王五',
2),
-
(
null,
'赵六',
2);
-
-
-- 把学生信息复制到班级表中
-
insert
into class(id,studentId,studentName)
select classId,id,name
from student;
代码运行结果:
注意:
(1)查询的字段类型和数量要和插入的字段的类型和数量相匹配,否则可能会插入失败;
(2)也可以当前表中查询的结果插入到当前表中。
insert into student(name,classId) select name,classId from student;
代码运行结果:
(当前表中的id是自增主键,不可重复,因此id不可复制)
二、查询
2.1 聚合查询
2.1.1 聚合函数
常用的聚合函数:
(在SQL中,数字和NULL的运算结果是NULL,所以SUM,AVG等聚合函数查询到NULL时会自动跳过,否则计算结果没有意义)
注意:
COUNT函数中COUNT(*)和COUNT(字段)的区别:
COUNT(*)会返回当前表中行数之和(即使整行数据都为NULL也会被算作一行);
COUNT(字段)会返回当前表中当前字段的行数之和(如果当前字段中某一行的数据为NULL,则不算作一行)。
示例:
2.1.2 group by子句
group by子句可以对指定列进行分组查询。
示例:通过班级id对学生进行分组
此时,表中显示的是分组之后每组的第一个数据:
2.1.3 having
使用group by进行分组查询时,也可以通过条件筛选进行分组:
分组前进行条件筛选,使用where关键字;
例如:通过班级id对除了张三之外的同学进行分组:
分组后再进行条件筛选的话,需要使用having关键字。
例如:根据学生姓名分组,查询姓名为张三的同学:
注意:
(1)where用在group by之前,having用在group by之后;
(2)where 和 having 可以同时使用。
2.2 联合查询
实际开发中可能需要通过多张表来查询一组数据,所以需要用到多表联合查询,联合查询是对多张表的数据取笛卡尔积。
2.2.1 内连接
内连接类似于查询两个集合的交集部分,语法为:
select 字段 from 表1,表2 where 条件;
select 字段 from 表1 join 表2 on 条件;
示例:现在有一张学生表和一张成绩表,查询每个学生的分数:
学生信息和分数在两张不同的表中,但是两张表中都存有学生的id,此时就可以用联合查询:
注意:
如果两个表中的字段名相同,需要使用表名.字段名来区分,否则会报错;如果字段名不同,则可用也可不用。
2.2.2 外连接
外连接分为左外连接和右外连接。如果在联合查询时,既要显示两个表中的交集,也要完全显示左侧的表,就是左外连接;同理,既显示交集,也完全显示右侧的表,则为右外连接。
语法:
左外连接:select 字段 from 表1 left join 表2 on 条件;
右外连接:select 字段 from 表1 right join 表2 on 条件;
示例:现在有一张学生表和一张成绩表,查询学生表中每位学生的成绩:
观察学生表可以发现,阿瞒同学可能缺考,没有成绩,但是又要查询阿瞒同学的成绩该怎么办呢?左外连接!
左边表中的信息完全显示,并显示两个表的交集部分,阿瞒在分数表中没有数据,所以为NULL。
注意:
MySQL中没有全外连接,即同时查询两个表的所有信息。
2.2.3 自连接
自连接就是指一张表连接自己本身进行查询。
语法:
select 字段 from 表名 as 别名1,表名 as 别名2 where 条件;
select 字段 from 表名 as 别名1 join 表名 as 别名2 on 条件;
示例:使用自连接方式查询货物表中利润不低于10的货物:
注意:
自连接需要使用别名,否则会报错。
自连接只有在特殊情况会用到,上述示例如果直接使用条件查询会更简单易懂。
2.2.4 子查询
子查询,解释为两个字就是——套娃,把一次查询的结果作为另一次查询的条件。
示例:使用子查询的方式查询和刘备同班的同学;
注意:
子查询尽量不要写太多层,否则会大大降低代码的可读性。
2.2.5 合并查询
合并查询可以合并多个select的执行结果,关键字:UNION、UNION ALL。
UNION会取得两个结果集的并集,并且自动去掉结果集中的重复行。
示例:查询id < 5 或者 班级id < 3的学生信息:
UNION ALL也是取得两个结果集的并集,但不会去掉结果集中的重复行。
UNION 和 OR的区别在于:
UNION可以合并查询不同表的结果,前提是两个表中的字段需要一致;
OR只能用于查询同一个表时的筛选条件。