mysql语句的复杂使用

将查询到的数据插入到另一张表中

要求查询到的列数和类型要与另一张表相匹配
如:insert into 表1 select * from 表2;
查询语句可以嵌套order by; where等

聚合查询

所谓聚合查询就是进行行与行之间的运算,之前的带有表达式的查询进行的是列于列之间的运算
要想完成聚合查询需要用到一些函数:

聚合函数

1. count(列名) 计算该列共有多少行(如果查询的是count(单列), 则该列中值为null的那一行不会参与到计算中;如果查询的是count(*),即使某一行中值全为null,该行也会被加到计算结果中)
   
2. sum(列名) 计算该列中所有行的和 (忽略null值, null值不参与运算)
   
3. max(列名) 计算该列中所有值的最大值
4. min(列名) 计算该列中所有值的最小值
5. avg(列名) 计算该列中所有值的平均值

分组查询(group by)

分组查询一般搭配聚合函数一起查询, 所谓分组查询就是将所有行进行分组, 不加group by 默认所有行都为同一组. 分组的依据是列名, 列名相同的为同一组, 如:

1. 分组查询可以指定查询条件,搭配order by, where 使用
2. 分组查询可以分为三种:

(1) 在分组查询之前进行条件筛选

(2)先分组查询,再进行条件筛选

(3)分组查询之前之后都进行条件筛选

联合查询/多表查询

1. 多表查询的基本执行过程又称笛卡尔积
2. 所谓多表查询, 就是同时查询并显示多个表的信息
3. 表1的每一行分别与表2的每一行记录进行拼接(并不是一行对一行拼接, 表1的某一行要与表2的每一行都要拼接), 形成一个新的表.
4. 笛卡尔积是进行全排列得到的一张更大的表, 其列数是两张表的列数之和, 其行数是两张表行数之积

select * from 表1, 表2;

select * from 表1, 表2 where 连接条件;

多表查询会产生很多的无效组合, 用来筛选有效数据的条件叫连接条件

多表查询的一般步骤:

1. 查询笛卡尔积

2. 输入连接条件

3. 根据需求,输入筛选条件

4. 去除不需要的列

对于联合查询还有另一种写法:

select * from 表名inner join 表名 on 连接条件 进一步筛选条件

与之前写法不同之处在于之前的写法只能实现内连接,而这种写法还能实现外连接

复杂查询练习:

1. 查询成绩总分和个人信息(聚合查询和多表查询结合使用)
   

2. 查询个人信息和课程以及课程成绩
   

内连接和外连接

1. 当几个表中的数据是一 一对应的,则内连接和外连接没有区别,以上练习中表都是一 一对应的
2. 当几个表中的数据不是一一对应时,则有内连接和外连接, 内连接只能查询到几个表中对应的数据, 不对应的数据则查询不到, 但是外连接可以查询到
3. 外连接分为左外连接(left join 表名 on)和右外连接(right join 表名 on)
4. 左外连接会尽量显示left之前的那个表的数据, left之后的表的数据则用null填充, 右外连接同理

由上图可知两个表中的数据并不是一一对应的

上图为内连接查询的结果

上图也是内连接查询的结果

上图为左连接查询的结果

上图为右连接查询的结果

自连接

自连接可用来解决一些特殊的问题, 如比较行与行之间的数值的大小, sql语句只能比较同行不同列的数据,想要比较同列不同行的数据,只能将不同行的数据转换成同一行, 即将行与列进行转换, 此时可用自连接,自连接相当于一个奇淫巧计
如下题:
要查询出java成绩比计算机原理高的同学信息:

第一步,自连接:

第二步, 添加连接条件,去除无效数据:

第三步, 根据需求添加必要筛选条件:

第四步, 筛选java比计算机原理成绩高的结果:

子查询

1. 所谓子查询就是将一个查询结果作为查询条件进行查询(套娃)
2. 子查询的代码可读性和执行效率非常低
3. 子查询分为单行子查询(查询结果为一条记录)和多行子查询(查询结果为多条记录)

单行子查询

查询许仙的同班同学:

多行子查询

1. 多行子查询可以用两个关键字来实现, in或者exists.exists的可读性和执行效率都不如in,只是在特殊情况使用
2. 所谓特殊情况就是查询的数据太大了,使用in时查询结果存储在内存中,如果数据过大内存不够时就不可以使用in
3. 查询英语和语文的成绩:

合并查询

1. 合并查询可以将不同表的查询结果结合到一张表中显示, 要求不同表的查询结果列的类型, 列的个数, 列的名字都相等
2. 合并查询可以使用union或者union all关键字.不同在于union 会将重复的两行去重, 而union all 不会
3. union在c语言中出现过, 当时为联合体, 本质是多种类型的数据共享同一块内存, 一块内存可以保存多种类型的数据, 对于这些类型,同一块内存有多种理解方式, 主要是将内存的作用发挥到极致
4. 例:查询id<3, 且科目为英语的课程