单表查询进阶与多表查询

目录

• 单表操作
  • 分组查询
    • group by
  • having
  • 升序降序
    • order by
  • limit
  • 总结
• 多表操作
  • 什么是外键
  • 为什么要使用外键
  • 使用外键的三种关系
    • 一对多
    • 多对多
    • 一对一
  • 多表联查

单表操作

分组查询

group by

分组指的是将所有记录按照某个相同的字段进行分类，比如针对员工信息表的职位分组，或者按照性别进行分组。

用法：

select 聚合函数，选取的字段 from 表名 group by 分组的字段

group by：是分组的关键字，必须和聚合函数一起出现

where 条件语句和 group by 分组语句的先后顺序

​ where > group by > having

聚合函数有：

• count：求数量
• sum：求和
• max：求最大数
• min：求最小数
• avg：求平均数

例子：

查询表中所有记录：

mysql> select * from userinfo;
+----+----------+-----+-----------+---------+
| id | name     | age | sarlay    | job     |
+----+----------+-----+-----------+---------+
|  1 | qinyj    |  18 |      0.00 | student |
|  2 | tank     |  18 |  10000.11 | teacher |
|  3 | jack     |  33 |  10000.33 | teacher |
|  4 | jing     |  20 |      1.33 | null    |
|  5 | xxx      |  20 | 100000.33 | teacher |
|  6 | xxx      |  20 |   1000.33 | teacher |
|  7 | xxx      |  20 | 100000.00 | teacher |
|  8 | xxx      |  20 |  10000.00 | teacher |
|  9 | jinggggg |  20 |  10000.00 | teacher |
+----+----------+-----+-----------+---------+
9 rows in set (0.00 sec)

• 以职业为例进行分组，统计一下teacher和student有多少人

select job,count(*) from userinfo group by job;

select job,count(*) as count from userinfo group by job; 

• 对职业进行分组，求出每个职业中年龄最大的那个人

select name,max(age) from userinfo group by job;

select name,job,max(age) from userinfo group by job;

• 求出每个职业中最小年龄的人

select name,job,min(age) from userinfo group by job;

• 求出每个职业中的年龄总和

select job,sum(age) from userinfo group by job;

• 求出每个职业中的年龄平均数

select job,avg(age) from userinfo group by job;

having

表示对group by 之后的数据，进行二次筛选

• 求出每个职业中年龄平均数超过30岁的

select job,avg(age) from userinfo group by job having avg(age) > 20;

升序降序

order by

用法：order by 字段名 asc（升序） desc（降序）

• 对年龄降序排序

select * from userinfo order by age desc;

• 对id升序排序

select * from userinfo order by id;

• 如果对多个字段进行排序，aes,desc：表示先对前者字段进行升序，如果前者字段有相同的行，再对后者字段降序

limit

分页处理

用法：limit offset, size

offset：行数据索引

size：取多少条数据

• 取5条数据

select * from userinfo limit 0,5;

• 取第一条数据

select * from userinfo limit 1;

• 取最后一条数据

select * from userinfo limit 9;

• 如果不知道有多少行取还最后一条数据

select * from pymysql_test order by id desc limit 1;

总结

单表查询关键字使用顺序：

select * from 表名
	where 条件
	group by 条件
	having 条件
	order by 条件
	limit 条件;
	
where > group by > having > order by > limit

多表操作

什么是外键

外键即一个表的字段关联另一个表的字段

为什么要使用外键

1、 减少内存占用的空间

2、 方便修改，只需要修改一张表，其他的表也会跟着修改

外键创建方法：

constraint 外键名 foreign key (被约束的字段) references 约束的表(约束的字段)

使用外键的三种关系

一对多

一对多的关系 即 员工--部门

一个部门可以有多个员工

一个员工隶属于一个部门

我们拆分出来两张表：部门表和员工表，那么其中肯定要一种关系将两个表联系起来，那么这个关系就是员工隶属于部门的关系，就需要在员工表中标明是属于哪个部门的

先创建部门表

# 创建部门表
create table department(
	id int auto_increment primary key,
    name varchar(32) not null default ''
)charset=utf8;



# 插入部门表数据
insert into department (name) values ("运维部"),("财务部"),("研发部"),("行政部");

select * from department;

# 创建员工表
create table user(
	id int auto_increment primary key,
    name varchar(32) not null default ''
    depart_id int not null default 1,	# 创建外键字段
    
    constraint user_depart foreign key(depart_id) references department(id)	# 关联部门表
)charset=utf8;

# 插入员工表数据
insert into user (name,depart_id) values ("qinyj",1);
insert into user (name,depart_id) values ("qinyj2",2);
insert into user (name,depart_id) values ("qinyj3",3);
insert into user (name,depart_id) values ("qinyj4",4);
insert into user (name,depart_id) values ("qinyj5",1);

# 一个部门可以有多个员工，如果在外键插入不存在的部门id则会报错
insert into user (name,depart_id) values ("qiny6",5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`school`.`user`, CONSTRAINT `user_depart` FOREIGN KEY (`depart_id`) REFERENCES `department` (`id`))

多对多

多对多的关系 即 相亲系统，男孩和女孩约会

一个女嘉宾可以和多个男嘉宾约会

一个男嘉宾可以和多个女嘉宾约会

我们拆分出来两张表：男嘉宾表和女嘉宾表，那么其中肯定要一种关系将两个表联系起来，那么这个关系就是男女嘉宾约会的信息的关系，就需要来记录这么个东西，那么这个信息在哪个表中记录？

我们可以再创建出来一个表，存放约会信息

# 创建男嘉宾表
create table boy(
	id int auto_increment primary key,
    boy_name varchar(32) not null default ''
)charset=utf8;


# 插入男嘉宾数据
insert into boy (boy_name) values ("张三"), ("李四"), ("老王");

# 创建女嘉宾表
create table girl(
	id int auto_increment primary key,
    girl_name varchar(32) not null default ''
)charset=utf8;

# 插入女嘉宾数据
insert into girl (girl_name) values ("丽丽"),("翠花"),("小红");

# 创建约会信息表
create table b_g_info(
	id int auto_increment primary key,
    boy_id int not null default 1,
    girl_id int not null default 1,
    
    # 创建约会对应信息
    constraint b_g_info_boy foreign key(boy_id) references boy(id),	# 关联男嘉宾id
    constraint b_g_info_girl foreign key(girl_id) references girl(id)	# 关联女嘉宾id
)charset=utf8;

# 插入约会信息数据
insert into b_g_info (boy_id,girl_id) values (1,1),(1,2),(2,2),(2,3);

一对一

一对一的关系 即 人--薪资

一个人有薪资

薪资属于一个人

薪资属于隐私信息，可以写在一张表中，但是隐私信息如果某人去查这个人的信息的话，薪资也会查出来，就会看到这个隐私信息，那么我们为了隐藏这个因此数据，我们可以使用另一张表来保存薪资

user表:
id name

薪资信息是比较敏感的字段，我们需要将此字段单独拆出来，变成一张独立的表priv

priv表：
id salary uid (外键 + unique)

# 创建用户表：
create table usertable(
	id int auto_increment primary key,
    name varchar(32) not null default ''
)charset=utf8;


# 插入用户数据
insert into usertable (name) values ("张三"),("李四"),("老王");

# 创建薪资表
create table privtable(
	id int auto_increment primary key,
    salary int not null default 0,
    uid int unique not null default 1,
    constraint privt_user foreign key (uid) references usertable(id)
)charset=utf8;

# 插入薪资数据
insert into privtable (salary,uid) values (2000,1),(3000,2),(4000,3);

多表联查

• left join ... on ...
• right join ... on ...
• inner join ... on ...

我们按照以上的关系分成了多个表之后，那么我们如何去查询数据呢？

设计到多个表的查询我们可以使用left join

例子（两表查询）：

mysql> select * from department;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 运维部    |
|  2 | 财务部    |
|  3 | 研发部    |
|  4 | 行政部    |
+----+-----------+
4 rows in set (0.00 sec)

mysql> select * from user;
+----+--------+-----------+
| id | name   | depart_id |
+----+--------+-----------+
|  1 | qinyj  |         1 |
|  2 | qinyj2 |         2 |
|  3 | qinyj3 |         3 |
|  4 | qinyj4 |         4 |
|  5 | qinyj5 |         1 |
+----+--------+-----------+
5 rows in set (0.00 sec)

• left join ... on ... 查询员工名称和隶属的部门

select * from department 
	left join user on department.id=depart_id;

select user.name,department.name from department 
	left join user on department.id=depart_id;

select user.name,department.name from department 
	left join user on department.id=depart_id 
	where user.name="qinyj4";

• right join ... on ... 基本和left join ... on ...使用方式一样
• inner join ... on ... 基本和left join ... on ...使用方式一样

例子（三表/多表查询）：

mysql> select * from boy;
+----+----------+
| id | boy_name |
+----+----------+
|  1 | 张三     |
|  2 | 李四     |
|  3 | 老王     |
+----+----------+
3 rows in set (0.00 sec)

mysql> select * from girl;
+----+-----------+
| id | girl_name |
+----+-----------+
|  1 | 丽丽      |
|  2 | 翠花      |
|  3 | 小红      |
+----+-----------+
3 rows in set (0.00 sec)

mysql> select * from b_g_info;
+----+--------+---------+
| id | boy_id | girl_id |
+----+--------+---------+
|  1 |      1 |       1 |
|  2 |      1 |       2 |
|  3 |      2 |       2 |
|  4 |      2 |       3 |
+----+--------+---------+
4 rows in set (0.00 sec)

• 查询男女嘉宾约会的姓名

select * from b_g_info 
	left join boy on b_g_info.boy_id=boy.id 
	left join girl on b_g_info.girl_id=girl.id;
	
	
select boy_name,girl_name from b_g_info 
	left join boy on b_g_info.boy_id=boy.id 
	left join girl on b_g_info.girl_id=girl.id;