MySQL之单表多表查询

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

1.单表查询

#单表查询语法
select <字段1，字段2....> from <表名>
where <表达式>
group by field 分组
having 筛选
order by fileld 排序
limit 限制条数#关键字的优先级
from>where>group by>having>select>distinct>orderby>limit#说明
1.from #找到表
2.where #通过where指定的约束条件，去表中提取记录
3.group by #进行分组，如果没有group by，则按整体为一组
4.having #将分组的结果进行过滤
5.select #执行select
6.distinct #去重
7.order by #进行排序
8.limit #限制结果的显示条数

#查看帮助：help select

mysql> help select
Name: 'SELECT'
Description:
Syntax:
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr] ...
[into_option]
[FROM table_references
[PARTITION partition_list]]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[into_option]
[FOR UPDATE | LOCK IN SHARE MODE]into_option: {
INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name] ...
}

1.1.where约束条件

#where约束条件中可以使用

#1.比较运算符：>、<、 >=、 <>、!=

#例子：

#1.查看id大于5的员工姓名
mysql> select id,name from staff where id > ;#2.查看id小于且等于10的员工姓名
mysql> select id,name from staff where id <= ;

#2.between 10 and 20 ：值在10到20之间

#例子：

#1.查看工资在1万到2万之间的员工
mysql> select name,salary from staff where salary between and ;
#2.查看工资不在1万到2万之间的员工
mysql> select name,salary from staff where salary not between and ;

#3.in(10,20,30) :值是10或20或30

#例子：

#.查看工资是3000，或5000，或4000，或9000的员工
mysql> select name,salary from staff where salary= or salary= or salary= or salary=;#.查看工资不是3000，，4000的员工
mysql> select name,staff from staff where salary not in(,,);

#4.like 'fdfdsf': parttern可以是%或_。 %表示任意多字符，_表示一个字符

#例子：

#查看wu后面有任意字符的列
mysql> select * from staff where name like 'wu%';

#5.逻辑运算符，在多个条件直接可以使用逻辑运算符 and(且) or(或) not(不)

#例子：

#.查看岗位是IT部门，且薪资在10000或9000或30000的员工姓名、年龄
mysql> select name,age,salary from staff where post='IT' and salary= or salary= or salary=;#.查看岗位是IT部门，且薪资不在11000的员工姓名、年龄
mysql> select name,age,salary from staff where post='IT' and not salary=

#练习

. 查看岗位是teacher的员工姓名、年龄
mysql> select name,age from staff where post='teacher';
. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
mysql> select name,age from staff where post='teacher' and age > ;
. 查看岗位是teacher且薪资在9000-10000范围内的员工姓名、年龄、薪资
mysql> select name,age,salary from staff where post='teacher' and salary betweeen and
. 查看岗位描述不为NULL的员工信息
mysql> select * from staff where post_comment is not null;
. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
mysql> select name,age,salary from staff where post='teacher' and salary= or salary= or salary=;
. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
mysql> select name,age,salary from staff where post='teacher' and not salary= or salary= or salary=
. 查看岗位是teacher且名字是wu开头的员工姓名、年薪
mysql> select name,salary* from staff where post='teacher' and name like 'wu%';

1.2.group by分组查询

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

#注意1：分组是发生在where之后，即分组是基于where之后得到的记录而进行的

#注意2：进行分组后，如group by post,只能查看post字段，如果要查看组内信息，需要借助聚合函数

#为何要进行分组呢？

.获取每个部门的员工数
.获取每个部门的最高工资
.获取男生人数和女生人数

#提示：如果先分组，必须要把全局的sql模块改为ONLY_FULL_GROUP_BY

#修改方法：

1.登录进去改mysql>set global sql_mode='ONLY_FULL_GROUP_BY';

2.进入/etc/my.cnf配置文件设置sql_mode='ONLY_FULL_GROUP_BY'

#设置之后查看

mysql> select @@global.sql_mode;
+--------------------+
| @@global.sql_mode |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
row in set (0.00 sec)

#设置分组之后只能查看当前字段，如果看全部字段就会报错，需要借助聚合函数查看

mysql> select * from staff group by post; #报错
mysql> select post from staff group by post; #查看当前字段不报错

#聚合函数

#提示：聚合函数聚合的是组的内容，如果没有分组，默认为一组

.max() #求最大值
.min() #求最小值
.avg() #求平均值
.sum() #求和
.count() #求总个数
.group_concat() #查字段

#例子：

#.查询每个部门有多少个员工
select post,count(id) from staff group by post;#.查询每个部门的最高薪水
select post,max(salary) from staff group by post;#.查询每个部门的最低薪水
select post,min(salary) from staff group by post;#.查询每个部门的平均薪水
select post,avg(salary) from staff group by post;#.查询每个部门的所有薪水
select post,sum(salary) from staff group by post;

#练习

. 查询岗位名以及岗位包含的所有员工名字
mysql> select post,group_concat(name) from staff group by post;. 查询岗位名以及各岗位内包含的员工个数
mysql> select post,count() from staff group by post;. 查询公司内男员工和女员工的个数
mysql> select group_concat(sex) from staff;. 查询岗位名以及各岗位的平均薪资
mysql> select post,avg(salary) from staff group by post;. 查询岗位名以及各岗位的最高薪资
mysql> select post,max(salary) from staff group by post;. 查询岗位名以及各岗位的最低薪资
mysql> select post,min(salary) from staff group by post;

1.3.having过滤

#注意点：

.执行优先级从高到低：where > group by > having
.where发生在分组group by之前，因而where中可以有任意字段，但是不能使用聚合函数
.having发生在分组group by之后，因而having中可以使用分组的字段，无法直接取到其他字段，可以使用聚合函数

#例子：

.过滤工资大于10000的员工
mysql> select * from staff having salary>;

#练习

. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,group_concat(name),count() from staff group by post having count() < ;. 查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) from staff group by post having avg(salary) > ;. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from staff group by post having avg(salary) between and ;

1.4.order by查询排序

#提示：asc:升序，desc:降序

#按单列排序(默认是升序)

#例子：

#1.按年龄大小排序(默认是asc升序)
mysql> select id,name,age from employee order by age;#2.按年龄排序，desc倒序，倒过来排
mysql> select id,name,age from employee order by age desc;

#按多列排序

#例子：

#先按照age(年龄)升序排序，如果年纪相同，则按照id降序
mysql> select id,name,age from staff order by age asc,id desc;

1.5.limit限制查询的记录条数

#例子：

#.查看id列按升序排序前3列，默认从0开始，查询出第一条
mysql> select id,name from staff order by id asc limit ;
+----+---------+
| id | name |
+----+---------+
| | guo |
| | ke |
| | wu |
+----+---------+
rows in set (0.00 sec)#.查看第1到5列
mysql> select id,name from staff order by id asc limit ,;
+----+-----------+
| id | name |
+----+-----------+
| | guo |
| | ke |
| | wu |
| | yan |
| | liu |
+----+-----------+
rows in set (0.00 sec)
##从0开始，先查询出第一条，然后包含这一条在内往后查5条，也就是1-#.查看6-10列
mysql> select id,name from staff order by id asc limit ,;
+----+------------+
| id | name |
+----+------------+
| | jing |
| | xin |
| | xiae |
| | 歪个 |
| | 丫的 |
+----+------------+
rows in set (0.00 sec)
#从5开始，即先查询出第6条，然后包含在这一条在内让后查5条，也就是6-

2.多表查询

#多表查询的语法

SELECT 字段列表
FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;

#数据准备：准备两张表，部门表（department）、员工表（employee）

create table department(
id int,
name varchar()
);create table employee(
id int primary key auto_increment,
name varchar(),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);#插入数据
insert into department values
(,'技术'),
(,'人力资源'),
(,'销售'),
(,'运营');insert into employee(name,sex,age,dep_id) values
('guo','male',,),
('ke','female',,),
('wu','male',,),
('liu','female',,),
('zhang','male',,),
('xiao','female',,)
;#查看表结构
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int() | YES | | NULL | |
| name | varchar() | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int() | NO | PRI | NULL | auto_increment |
| name | varchar() | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int() | YES | | NULL | |
| dep_id | int() | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+
rows in set (0.00 sec)mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| | 技术 |
| | 人力资源 |
| | 销售 |
| | 运营 |
+------+--------------+
rows in set (0.00 sec)mysql> select * from employee;
+----+-------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+-------+--------+------+--------+
| | guo | male | | |
| | ke | female | | |
| | wu | male | | |
| | liu | female | | |
| | zhang | male | | |
| | xiao | female | | |
+----+-------+--------+------+--------+
rows in set (0.00 sec)

#提示：观察两张表，发现department表中id=203部门在employee中没有对应的员工，发现employee中id=6的员工在department表中没有对应关系

#查看两个表的交叉连接

mysql> select * from employee,department

2.1.外链接操作

#包括：内连接、左连接、右连接、全外连接

#1.内连接：符合条件查询，只连接匹配的行

#查询员工对应的部门
#找两张表共有的部分
mysql> select * from employee inner join department on employee.dep_id = department.id;
+----+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-------+--------+------+--------+------+--------------+
| | guo | male | | | | 技术 |
| | ke | female | | | | 人力资源 |
| | wu | male | | | | 人力资源 |
| | liu | female | | | | 销售 |
| | zhang | male | | | | 技术 |
+----+-------+--------+------+--------+------+--------------+
rows in set (0.00 sec)

#2.左连接：优先显示左边记录

#找出左表所有员工信息，包括没有部门的员工
mysql> select * from employee left join department on employee.dep_id = department.id;
+----+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-------+--------+------+--------+------+--------------+
| | guo | male | | | | 技术 |
| | zhang | male | | | | 技术 |
| | ke | female | | | | 人力资源 |
| | wu | male | | | | 人力资源 |
| | liu | female | | | | 销售 |
| | xiao | female | | | NULL | NULL |
+----+-------+--------+------+--------+------+--------------+
rows in set (0.00 sec)

#3.右连接：优先显示右边记录

#以右表为准，找出所有部门信息，包括没有员工的部门
mysql> select * from employee right join department on employee.dep_id = department.id;
+------+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+-------+--------+------+--------+------+--------------+
| | guo | male | | | | 技术 |
| | ke | female | | | | 人力资源 |
| | wu | male | | | | 人力资源 |
| | liu | female | | | | 销售 |
| | zhang | male | | | | 技术 |
| NULL | NULL | NULL | NULL | NULL | | 运营 |
+------+-------+--------+------+--------+------+--------------+
rows in set (0.00 sec)

#4.全外连接：使用union连接

2.2.符合条件查询

#例一：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25，即找出年龄大于25岁的员工以及员工所在的部门

mysql> select employee.name,department.name from employee,department where employee.dep_id = department.id and age > ;
+------+--------------+
| name | name |
+------+--------------+
| ke | 人力资源 |
| wu | 人力资源 |
| liu | 销售 |
+------+--------------+
rows in set (0.01 sec)

#例二：以内连接的方式查询employee和department表，并且以age字段的升序方式显示。

mysql> select employee.name,department.name from employee,department where employee.dep_id = department.id and age > order by age asc;
+------+--------------+
| name | name |
+------+--------------+
| liu | 销售 |
| wu | 人力资源 |
| ke | 人力资源 |
+------+--------------+
rows in set (0.00 sec)

2.3.子查询

.子查询是将一个查询语句嵌套到另一个查询语句中
.内层查询语句的查询结果，可以为外层查询语句提供查询条件
.子查询中可以包含：IN,NOT IN,ANY,ALL,EXISTS和NOT EXISTS等关键字
.还可以包含比较运算符：=,!=,>,<等

#1.带in关键字的子查询

#例子：

#.查询平均年龄在25岁以上的部门名
#思路：看到查平均年龄就是要分组，然后先查询出员工表里面大于25岁的，再作为结果给外层查询语句作为条件
mysql> select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > );
+------+--------------+
| id | name |
+------+--------------+
| | 人力资源 |
| | 销售 |
+------+--------------+
rows in set (0.01 sec)#.查看技术部员工姓名
#思路；先查部门表找出技术部，再作为条件查员工表,
mysql> select name from employee where dep_id in (select id from department where name='技术');
+-------+
| name |
+-------+
| guo |
| zhang |
+-------+
rows in set (0.00 sec)#.查看不足1人的部门名
mysql> select name from department where id not in(select dep_id from employee grroup by dep_id);
+--------+
| name |
+--------+
| 运营 |
+--------+
row in set (0.00 sec)

#2.带比较运算符的字查询

#例子：

#.查询大于所有人平均年龄的员工名与年龄
#思路：先查询出所有人的平均年龄，然后再和原来的员工表进行比较
mysql> select name,age from employee where age > (select avg(age) from employee);
+------+------+
| name | age |
+------+------+
| ke | |
| wu | |
+------+------+
rows in set (0.00 sec)#.查询大于部门内平均年龄的员工名、年龄
思路：
（）先对员工表(employee)中的人员分组(group by)，查询出dep_id以及平均年龄。
()将查出的结果作为临时表,再对根据临时表的dep_id和employee的dep_id作为筛选条件将employee表和临时表进行内连接。
()最后再将employee员工的年龄是大于平均年龄的员工名字和年龄筛选。
mysql> select t1.name,t1.age from employee as t1
inner join
(select dep_id,avg(age) as avg_age from employee group by dep_id) as t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age;