MySQL数据库从小白到小菜04

• • MySQL进阶补充
  • • • 插入
      • 聚合查询
      • • • COUNT
          • SUM
          • AVG
          • MAX
          • MIN
          • GROUP BY
          • HAVING
      • 联合查询
      • • • 内连接
          • 外连接

MySQL进阶补充

插入

在表中插入数据时用的是INSERT，在MySQL中，是可以插入(查找结果表)的数据，但是结果表每一列的顺序类型都必须与要插入的表每一列顺序类型一样， 如：

--创建一个名为jstudents的表，存储id与name信息
create table students (id int,name varchar(50));

--在students表中插入staff表中的id与name信息
insert into students select id,name from staff;

--在students表中插入staff表中的id信息，且每一条信息的name置为默认值
insert into students select id,null from staff;
--或者为：
insert into students(id) select id from staff;

聚合查询

之前的查询提到的是列和列的运算，而聚合查询就是行和行之间的运算，如之前的成绩表算每个人的总分就是列和列的查询，而算每一门科目的总分就是行和行之间的查询，就需要用到聚合函数。
常见的聚合函数：

COUNT

count 返回查询数据的数量（有多少行）

--统计本表有多少条数据
select count(*) from students;

--统计本表name有多少条数据（不包含null）
select count(name) from students;

--统计本表id有多少条数据（不包含null）
select count(id) from students;

--查询本表id<1的学生有几个
select count(id) from students where id<1;

SUM

sum 返回查询数据的总合

--统计本表id列总和(不包括null)
select sum(id) from students;

--统计本表id列id<4的总和（不包括null）
select sum(id) from students where id<4;

AVG

avg 返回查询数据的平均值

--统计本表中id列平均值（不包括null）
select avg(id) from students;

--统计本表中ld列id<4的平均值（不包括null）
select avg(id) from students where id<4;

MAX

max 返回查询数据的最大值

--统计本表中id列最大值
select max(id) from students;

--统计本表中ld列id不等于4的最大值
select max(id) from students where id != 4;

MIN

min 返回查询数据的最小值

--统计本表中id列最小值
select min(id) from students;

--统计本表中ld列id不等于1的最小值
select min(id) from students where id != 1;

GROUP BY

group 关于某一列进行分组

--创建一个名为staff2的表，表中有主键自增id、姓名name、职位role、工资salary
create table staff2(id int primary key auto_increment,name varchar(20),role varchar(29),salary int); 

--插入一些数据
insert into staff2 values 
(null,'TOM1','CAT',8000),
(null,'JERRY1','MOUTH',3000),
(null,'TOM2','CAT',9000),
(null,'JERRY2','MOUTH',4000),
(null,'TOM3','CAT',10000),
(null,'JERRY3','MOUTH',5000);

--查找每个职位的平均工资
select role,avg(salary) from staff2 group by role;
select role,avg(salary),count(*),sum(salary) from staff2 group by role;

HAVING

如果在分组之后还需要进行条件筛选时，用HAVING而不是WHERE

--查找平均工资高于5000的职位
select role,avg(salary) from staff2 group by role having avg(salary)>5000;

联合查询

联合查询也可以理解为多表查询，实际开发中往往数据都会来自不同的表，所以就会用到联合查询
多表查询的性能非常低，尤其是表的内容较多时
在多表查询中，会将两张表（或多张表）的数据进行排列组合。这就叫做笛卡尔积。如：
针对A,B两张表进行查询，得到的结果表的列数就是A表的列数+B表的列数，行数就是A表的行数×B表的行数
三张表同理，得到的列数就是AB结果表列数+C表的列数，行数就是AB结果表的行数×C表的行数

--创建一个people表和一个city表
create table people(people_id int,name varchar(50),city_id int);
create table city(id int,city_name varchar(50));

--在两个表内插入相关信息
insert into people values (1,'people1',1),(2,'people2',1),(3,'people3',1),(4,'people4',2),(5,'people5',2),(6,'people6',2);
insert into city values (1,'city1'),(2,'city2');

--查看两个表的笛卡尔积(我们发现，这张表中有太多不需要的信息：结果表中有people表中cityid不等于city表中city的信息)
select * from people, city;

--在上面查询结果表中，有两列是重复的，分别是city_id 与 id列，所以我们可以写成这样
select people.people_id, people.name, city.id, city.city_name from people, city; 

内连接

针对两个表来寻找满足要求的记录，如图：

--查看无多余信息的两个表的合并表
select people.people_id, people.name, city.id, city.city_name from people, city where people.city_id = city.id; 
select people.people_id, people.name, city.id, city.city_name from people join city on people.city_id = city.id; 

--查看people_id=1的人所在的城市
select people.people_id, people.name, city.id, city.city_name from people, city where people.city_id = city.id and people.people_id = 1;
select people.people_id, people.name, city.id, city.city_name from people join city on people.city_id = city.id and people.people_id = 1;

外连接

insert into people values (7,'people7',3),(8,'people8',3);
insert into city values (4,'city4');

左外连接：以左边的表为基准寻找记录，如果右表中的记录不符合左表，则不显示，左表会全部显示，如图：

select people.name, city.city_name from people left join city on people.city_id = city.id;

右外链接，同理，右表会全部显示，如图：

select people.name, city.city_name from people right join city on people.city_id = city.id;

自链接：有些查询，是希望把行和行的数据按照一定的条件进行比较
可以理解为在A和A的笛卡尔积结果表中筛选
子查询：可以理解为嵌套查询，也就是在查找结果表里再进行查找，或把查询结果做为一个条件

--把查询结果作为一个条件，此时括号内的结果表只有一行
select * from staff2 where name =(select name from staff2 where salary = 8000);
--此时括号内的结果表有多行，反之可以用not in
select * from staff2 where name in(select name from staff2 where salary > 8000);

--如果要在结果表里查询，则结果表必须有别名
select * from (select * from staff2 where salary > 8000) as qqq where role = 'CAT';