一、单表查询

先准备好实验用的表

create table emp(
id int primary key auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);
--插入记录
--三个部门：教学，销售，运营
--以下是教学部
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1),
('egon','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tank','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jerry','female',18,'20110211','teacher',9000,401,1),
('nick','male',18,'19000301','teacher',30000,401,1),
('sean','male',48,'20101111','teacher',10000,401,1),
--以下是销售部门
('歪歪','female',48,'20150311','sale',3000.13,402,2),
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),
--以下是运营部门
('张野','male',28,'20160311','operation',10000.13,403,3),
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;

ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk

1、初始sql的执行顺序

select id,name from emp where id >= 3 and id <= 6;
# 先后顺序
from       # 先确定是哪张表
where    # 再确定是否有过滤条件
select    # 最后确定要过滤出来的数据的哪些字段

2、where约束条件

2.1、查询id大于等于3小于等于6的数据

# select id,name from emp where id >= 3 and id <= 6;
# select *
from emp where id between 3 and 6;
mysql> select * from emp where id>=3 and id <=6;
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name
| sex
| age | hire_date
| post
| post_comment | salary
| office | depart_id |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
|
3 | kevin | male
|
81 | 2013-03-05 | teacher | NULL
| 8300.00 |
401 |
1 |
|
4 | tank
| male
|
73 | 2014-07-01 | teacher | NULL
| 3500.00 |
401 |
1 |
|
5 | owen
| male
|
28 | 2012-11-01 | teacher | NULL
| 2100.00 |
401 |
1 |
|
6 | jerry | female |
18 | 2011-02-11 | teacher | NULL
| 9000.00 |
401 |
1 |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
4 rows in set (0.00 sec)
mysql> select * from emp where id between 3 and 6;
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name
| sex
| age | hire_date
| post
| post_comment | salary
| office | depart_id |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
|
3 | kevin | male
|
81 | 2013-03-05 | teacher | NULL
| 8300.00 |
401 |
1 |
|
4 | tank
| male
|
73 | 2014-07-01 | teacher | NULL
| 3500.00 |
401 |
1 |
|
5 | owen
| male
|
28 | 2012-11-01 | teacher | NULL
| 2100.00 |
401 |
1 |
|
6 | jerry | female |
18 | 2011-02-11 | teacher | NULL
| 9000.00 |
401 |
1 |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
4 rows in set (0.00 sec)

2.2、查询薪资是20000或者18000或者17000的数据

# select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
# select * from emp where salary in (20000,18000,17000);
# 简写
mysql> select * from emp where salary=20000 or salary=18000 or salary=17000;
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name
| sex
| age | hire_date
| post
| post_comment | salary
| office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 15 | 程咬金
| male
|
18 | 1997-03-12 | operation | NULL
| 20000.00 |
403 |
3 |
| 17 | 程咬铜
| male
|
18 | 2015-04-11 | operation | NULL
| 18000.00 |
403 |
3 |
| 18 | 程咬铁
| female |
18 | 2014-05-12 | operation | NULL
| 17000.00 |
403 |
3 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
3 rows in set (0.04 sec)
mysql> select * from emp where salary in (20000,18000,17000);
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name
| sex
| age | hire_date
| post
| post_comment | salary
| office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 15 | 程咬金
| male
|
18 | 1997-03-12 | operation | NULL
| 20000.00 |
403 |
3 |
| 17 | 程咬铜
| male
|
18 | 2015-04-11 | operation | NULL
| 18000.00 |
403 |
3 |
| 18 | 程咬铁
| female |
18 | 2014-05-12 | operation | NULL
| 17000.00 |
403 |
3 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
3 rows in set (0.06 sec)

2.3、查询员工姓名中包含o字母的员工姓名和薪资

mysql> select name,salary from emp where name like '%o%';
+-------+------------+
| name
| salary
|
+-------+------------+
| jason |
7300.33 |
| egon
| 1000000.31 |
| owen
|
2100.00 |
+-------+------------+

在刚刚开始接触mysql查询时，建议按照查询的优先级顺序拼接处sql语句。

先是查哪张表   from emp

接着是根据什么条件查   where name like '%o%'

再是对查询出来的数据筛选展示部分   select name,salary

2.4、查询员工姓名是由四个字符组成的员工姓名和其薪资

mysql> select name,salary from emp where name like '____';
+------+------------+
| name | salary
|
+------+------------+
| egon | 1000000.31 |
| tank |
3500.00 |
| owen |
2100.00 |
| nick |
30000.00 |
| sean |
10000.00 |
+------+------------+
5 rows in set (0.00 sec)
mysql> select name,salary from emp where char_length(name)=4;
+------+------------+
| name | salary
|
+------+------------+
| egon | 1000000.31 |
| tank |
3500.00 |
| owen |
2100.00 |
| nick |
30000.00 |
| sean |
10000.00 |
+------+------------+

2.5、查询id小于3或者大于6的数据

select *
from emp where id not between 3 and 6;

2.6、查询薪资不在20000，18000，17000范围的数据

select * from emp where salary not in (20000,18000,17000);

2.7、查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用is

mysql> select name,post from emp where post_comment=null;
Empty set (0.00 sec)
mysql> select name,post from emp where post_comment is null;
+-----------+-----------------------------+
| name
| post
|
+-----------+-----------------------------+
| jason
| 张江第一帅形象代言
|
| egon
| teacher
|
| kevin
| teacher
|
| tank
| teacher
|
| owen
| teacher
|
| jerry
| teacher
|
| nick
| teacher
|
| sean
| teacher
|
| 歪歪
| sale
|
| 丫丫
| sale
|
| 丁丁
| sale
|
| 星星
| sale
|
| 格格
| sale
|
| 张野
| operation
|
| 程咬金
| operation
|
| 程咬银
| operation
|
| 程咬铜
| operation
|
| 程咬铁
| operation
|
+-----------+-----------------------------+
18 rows in set (0.00 sec)
mysql> select name,post from emp where post_comment is not null;
Empty set (0.00 sec)

3、group by + 分组依据

数据分组的应用场景：每个部门的平均薪资，男女比例等

3.1、按部门分组

mysql> select * from emp group by post;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'day41.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select id,name,sex from emp group by post;
# 验证
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'day41.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

如果没有设置严格模式的话，上面的查询语句会执行，输出的是每个组的第一条数据，那么我们需要设置严格模式

设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据，不应该在去取组里面的单个元素的值，那样的话分组就没有意义了，因为不分组就是对单个元素信息的随意获取

# 设置严格模式
set global sql_mode="strict_trans_tables,only_full_group_by";

设置完后重新连接客户端

mysql> select post from emp group by post;
+-----------------------------+
| post
|
+-----------------------------+
| operation
|
| sale
|
| teacher
|
| 张江第一帅形象代言
|
+-----------------------------+

需要强调的是，一旦分组了，就不能再直接查找到单个的数据信息了，只能获取到组名。

3.2、获取每个部门的信息（运用聚合函数）

以组为单位统计组内数据>>>>>>聚合查询（聚集到一起合成为一个结果）

3.2.1、每个部门的最高工资

select post,max(salary) from
emp group by post;

3.2.2、每个部门的最低工资

select post,min(salary) from emp group by post;

3.2.3、每个部门的平均工资

select post,avg(salary) from emp group by post;

3.2.4、每个部门的工资总和

select post,sum(salary) from emp group by post;

3.2.5、每个部门的人数

select post,count(id) from emp group by post;

3.3、查询分组之后的部门名称和每个部门下的所有姓名（聚合函数）

group_concat(分组之后用)不仅可以用来显示除分组外字段还有拼接字符串的作用

mysql> select post,group_concat(name) from emp group by post;
# 查询部门内人的名字
+-----------------------------+------------------------------------------------+
| post
| group_concat(name)
|
+-----------------------------+------------------------------------------------+
| operation
| 张野,程咬金,程咬银,程咬铜,程咬铁
|
| sale
| 歪歪,丫丫,丁丁,星星,格格
|
| teacher
| egon,kevin,tank,owen,jerry,nick,sean
|
| 张江第一帅形象代言
| jason
|
+-----------------------------+------------------------------------------------+
4 rows in set (0.00 sec)
mysql> select post,group_concat(name,"_SB") from emp group by post;
# 给查出的人名拼接帅比字段
+-----------------------------+---------------------------------------------------------------+
| post
| group_concat(name,"_SB")
|
+-----------------------------+---------------------------------------------------------------+
| operation
| 张野_SB,程咬金_SB,程咬银_SB,程咬铜_SB,程咬铁_SB
|
| sale
| 歪歪_SB,丫丫_SB,丁丁_SB,星星_SB,格格_SB
|
| teacher
| egon_SB,kevin_SB,tank_SB,owen_SB,jerry_SB,nick_SB,sean_SB
|
| 张江第一帅形象代言
| jason_SB
|
+-----------------------------+---------------------------------------------------------------+
4 rows in set (0.00 sec)
mysql> select post,group_concat(name,':',salary) from emp group by post;
# 查出 姓名：工资
+-----------------------------+---------------------------------------------------------------------------------------------------+
| post
| group_concat(name,':',salary)
|
+-----------------------------+---------------------------------------------------------------------------------------------------+
| operation
| 张野:10000.13,程咬金:20000.00,程咬银:19000.00,程咬铜:18000.00,程咬铁:17000.00
|
| sale
| 歪歪:3000.13,丫丫:2000.35,丁丁:1000.37,星星:3000.29,格格:4000.33
|
| teacher
| egon:1000000.31,kevin:8300.00,tank:3500.00,owen:2100.00,jerry:9000.00,nick:30000.00,sean:10000.00 |
| 张江第一帅形象代言
| jason:7300.33
|
+-----------------------------+---------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
mysql> select post,group_concat(salary) from emp group by post;
# 查出部门内的工资
+-----------------------------+--------------------------------------------------------------+
| post
| group_concat(salary)
|
+-----------------------------+--------------------------------------------------------------+
| operation
| 10000.13,20000.00,19000.00,18000.00,17000.00
|
| sale
| 3000.13,2000.35,1000.37,3000.29,4000.33
|
| teacher
| 1000000.31,8300.00,3500.00,2100.00,9000.00,30000.00,10000.00 |
| 张江第一帅形象代言
| 7300.33
|
+-----------------------------+--------------------------------------------------------------+

3.4、补充concat（不分组的时候用）：拼接字符串达到更好的显示效果，as语法

mysql> select name as 姓名,salary as 薪资 from emp;
# as起别名
+-----------+------------+
| 姓名
| 薪资
|
+-----------+------------+
| jason
|
7300.33 |
| egon
| 1000000.31 |
| kevin
|
8300.00 |
| tank
|
3500.00 |
| owen
|
2100.00 |
| jerry
|
9000.00 |
| nick
|
30000.00 |
| sean
|
10000.00 |
| 歪歪
|
3000.13 |
| 丫丫
|
2000.35 |
| 丁丁
|
1000.37 |
| 星星
|
3000.29 |
| 格格
|
4000.33 |
| 张野
|
10000.13 |
| 程咬金
|
20000.00 |
| 程咬银
|
19000.00 |
| 程咬铜
|
18000.00 |
| 程咬铁
|
17000.00 |
+-----------+------------+
18 rows in set (0.13 sec)
mysql> select concat('NAME:',name) as 姓名,concat('SAL:',salary) as 薪资 from emp;
# 拼接显示
+----------------+----------------+
| 姓名
| 薪资
|
+----------------+----------------+
| NAME:jason
| SAL:7300.33
|
| NAME:egon
| SAL:1000000.31 |
| NAME:kevin
| SAL:8300.00
|
| NAME:tank
| SAL:3500.00
|
| NAME:owen
| SAL:2100.00
|
| NAME:jerry
| SAL:9000.00
|
| NAME:nick
| SAL:30000.00
|
| NAME:sean
| SAL:10000.00
|
| NAME:歪歪
| SAL:3000.13
|
| NAME:丫丫
| SAL:2000.35
|
| NAME:丁丁
| SAL:1000.37
|
| NAME:星星
| SAL:3000.29
|
| NAME:格格
| SAL:4000.33
|
| NAME:张野
| SAL:10000.13
|
| NAME:程咬金
| SAL:20000.00
|
| NAME:程咬银
| SAL:19000.00
|
| NAME:程咬铜
| SAL:18000.00
|
| NAME:程咬铁
| SAL:17000.00
|
+----------------+----------------+

3.5、补充as语法：可以给字段或表起别名

mysql> select emp.id,emp.name from emp as t1;
# 报错是因为表名已经暂时用了别名t1
ERROR 1054 (42S22): Unknown column 'emp.id' in 'field list'
mysql> select t1.id,t1.name from emp as t1;
+----+-----------+
| id | name
|
+----+-----------+
|
1 | jason
|
|
2 | egon
|
|
3 | kevin
|
|
4 | tank
|
|
5 | owen
|
|
6 | jerry
|
|
7 | nick
|
|
8 | sean
|
|
9 | 歪歪
|
| 10 | 丫丫
|
| 11 | 丁丁
|
| 12 | 星星
|
| 13 | 格格
|
| 14 | 张野
|
| 15 | 程咬金
|
| 16 | 程咬银
|
| 17 | 程咬铜
|
| 18 | 程咬铁
|
+----+-----------+

3.6、查询四则运算

查询每个人的年薪

mysql> select name,salary*12 as annual_salary from emp;
+-----------+---------------+
| name
| annual_salary |
+-----------+---------------+
| jason
|
87603.96 |
| egon
|
12000003.72 |
| kevin
|
99600.00 |
| tank
|
42000.00 |
| owen
|
25200.00 |
| jerry
|
108000.00 |
| nick
|
360000.00 |
| sean
|
120000.00 |
| 歪歪
|
36001.56 |
| 丫丫
|
24004.20 |
| 丁丁
|
12004.44 |
| 星星
|
36003.48 |
| 格格
|
48003.96 |
| 张野
|
120001.56 |
| 程咬金
|
240000.00 |
| 程咬银
|
228000.00 |
| 程咬铜
|
216000.00 |
| 程咬铁
|
204000.00 |
+-----------+---------------+
18 rows in set (0.07 sec)
mysql> select name,salary*12 annual_salary from emp;
# as可以省略
+-----------+---------------+
| name
| annual_salary |
+-----------+---------------+
| jason
|
87603.96 |
| egon
|
12000003.72 |
| kevin
|
99600.00 |
| tank
|
42000.00 |
| owen
|
25200.00 |
| jerry
|
108000.00 |
| nick
|
360000.00 |
| sean
|
120000.00 |
| 歪歪
|
36001.56 |
| 丫丫
|
24004.20 |
| 丁丁
|
12004.44 |
| 星星
|
36003.48 |
| 格格
|
48003.96 |
| 张野
|
120001.56 |
| 程咬金
|
240000.00 |
| 程咬银
|
228000.00 |
| 程咬铜
|
216000.00 |
| 程咬铁
|
204000.00 |
+-----------+---------------+

3.7、练习

刚开始查询表，一定要按照最基本的步骤，先确定是哪张表，再确定查这张表也没有限制条件，再确定是否需要分类，最后再确定需要什么字段对应的信息
 

--1. 查询岗位名以及岗位包含的所有员工名字
select post,group_concat(name) from emp group by post;
--2. 查询岗位名以及各岗位内包含的员工个数
select post,count(id) from emp group by post;
--3. 查询公司内男员工和女员工的个数
select sex,count(id) from emp group by sex;
--4. 查询岗位名以及各岗位的平均薪资
select post,avg(salary) from emp group by post;
--5. 查询岗位名以及各岗位的最高薪资
select post,max(salary) from emp group by post;
--6. 查询岗位名以及各岗位的最低薪资
select post,min(salary) from emp group by post;
--7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex,avg(salary) from emp group by sex;
--关键字where和group by 同时出现的情况下，group by必须在where之后
--where先对整张表进行一次筛选，然后group by再对筛选过后的表进行分组
--如何验证where是在group by之前执行而不是之后？ 利用聚合函数 因为聚合函数只能在分组之后才能使用
mysql> select id,name,age from emp where max(salary)>3000;
# 报错
ERROR 1111 (HY000): Invalid use of group function
mysql> select max(salary) from emp;
+-------------+
| max(salary) |
+-------------+
|
1000000.31 |
+-------------+
# 正常运行，不分组意味着每一个人都是一组，等运行到max(salary)的时候已经经过where，group by操作了，只不过我们都没有写这些条件
# 语法顺序
select
from
where
group by
# 执行顺序
from
where
group by
select
--8、统计各部门年龄在30岁以上的员工平均工资
select post,avg(salary) from emp where age>30 group by post;
# 对where过滤出来的虚拟表进行一个分组

4、having+限制条件

having的语法格式与where一致，只不过having是在分组之后进行的过滤，即where虽然不能用聚合函数，但是having可以！

4.1、统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门

mysql> select post,avg(salary) from emp where age>30 group by post having avg(salary)>10000;
+---------+---------------+
| post
| avg(salary)
|
+---------+---------------+
| teacher | 255450.077500 |
+---------+---------------+
1 row in set (0.00 sec)
# 强调：having必须在group by后面使用
mysql> select * from emp having avg(salary) > 10000;
# 报错
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'day41.emp.id'; this is incompatible with sql_mode=only_full_group_by

5、distinct：去重

对有重复的展示数据进行去重操作

mysql> select post from emp;
+-----------------------------+
| post
|
+-----------------------------+
| 张江第一帅形象代言
|
| teacher
|
| teacher
|
| teacher
|
| teacher
|
| teacher
|
| teacher
|
| teacher
|
| sale
|
| sale
|
| sale
|
| sale
|
| sale
|
| operation
|
| operation
|
| operation
|
| operation
|
| operation
|
+-----------------------------+
18 rows in set (0.00 sec)
mysql> select distinct post from emp;
+-----------------------------+
| post
|
+-----------------------------+
| 张江第一帅形象代言
|
| teacher
|
| sale
|
| operation
|
+-----------------------------+

6、order by：排序，asc--升序   desc--降序       默认是asc升序

select * from emp order by salary asc;
--默认升序
select * from emp order by salary desc; --以工资降序
select * from emp order by age desc; --以年龄降序排
--先按照age降序排，在年龄相同的情况下再按照薪资升序排
select * from emp order by age desc,salary asc;
--统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) from emp
where age > 10
group by post
having avg(salary) > 1000
order by avg(salary)
;

7、limit

--限制展示条数
select * from emp limit 3;
--查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;
--分页显示
select * from emp limit 0,5;
--第一个参数表示起始位置,不包含写的0，第二个参数表示的是条数，不是索引位置
select * from emp limit 5,5; --不包含5

8、正则

.*   --贪婪匹配

.*?  --非贪婪匹配

mysql> select * from emp where name regexp '^j.*(n|y)$';
+----+-------+--------+-----+------------+-----------------------------+--------------+---------+--------+-----------+
| id | name
| sex
| age | hire_date
| post
| post_comment | salary
| office | depart_id |
+----+-------+--------+-----+------------+-----------------------------+--------------+---------+--------+-----------+
|
1 | jason | male
|
18 | 2017-03-01 | 张江第一帅形象代言
| NULL
| 7300.33 |
401 |
1 |
|
6 | jerry | female |
18 | 2011-02-11 | teacher
| NULL
| 9000.00 |
401 |
1 |
+----+-------+--------+-----+------------+-----------------------------+--------------+---------+--------+-----------+

9、书写顺序

select distinct * from 表名
where 限制条件  
group by 分组依据
having 过滤条件
order by  排序依据
limit 限制起始位置和展示条数

执行顺序
from
where
group by
having
order by
limit
distinct
select

10、exists

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，
而是返回一个真假值，True或False。
当返回True时，外层查询语句将进行查询
当返回值为False时，外层查询语句不进行查询。

select * from emp where exists (select * from dep where 1=2);
select * from emp where exists (select * from dep where id >1);

11、查出部门中工资大于部门平均工资的员工及工资

select name,salary from
(select name,salary,post from emp) as t1
inner join
(select post as post1,avg(salary) as avg from emp group by post) as t2
on t1.post=t2.post1
where salary>avg;

二、多表查询

1、建表

create table dep(
id int,
name varchar(20)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
--插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',38,201),
('nick','female',28,202),
('owen','male',18,200),
('jerry','female',18,204)
;

当初为什么我们要分表，就是为了方便管理，在硬盘上确实是多张表，但是到了内存中我们应该把他们再拼成一张表进行查询才合理

2、表查询

select * from emp,dep;

 左表一条记录与右表所有记录都对应一遍>>>笛卡尔积

将所有的数据都对应了一遍，虽然不合理但是其中有合理的数据，现在我们需要做的就是找出合理的数据

# 查询员工及所在部门的信息

mysql> select * from emp;
+----+-------+--------+------+--------+
| id | name
| sex
| age
| dep_id |
+----+-------+--------+------+--------+
|
1 | jason | male
|
18 |
200 |
|
2 | egon
| female |
48 |
201 |
|
3 | kevin | male
|
38 |
201 |
|
4 | nick
| female |
28 |
202 |
|
5 | owen
| male
|
18 |
200 |
|
6 | jerry | female |
18 |
204 |
+----+-------+--------+------+--------+
6 rows in set (0.00 sec)
mysql> select * from dep;
+------+--------------+
| id
| name
|
+------+--------------+
|
200 | 技术
|
|
201 | 人力资源
|
|
202 | 销售
|
|
203 | 运营
|
+------+--------------+
4 rows in set (0.00 sec)
mysql> select * from emp,dep where emp.dep_id = dep.id;
+----+-------+--------+------+--------+------+--------------+
| id | name
| sex
| age
| dep_id | id
| name
|
+----+-------+--------+------+--------+------+--------------+
|
1 | jason | male
|
18 |
200 |
200 | 技术
|
|
2 | egon
| female |
48 |
201 |
201 | 人力资源
|
|
3 | kevin | male
|
38 |
201 |
201 | 人力资源
|
|
4 | nick
| female |
28 |
202 |
202 | 销售
|
|
5 | owen
| male
|
18 |
200 |
200 | 技术
|
+----+-------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)

# 查询部门为技术部的员工及部门信息

mysql> select * from emp,dep where emp.dep_id=dep.id and dep.name='技术';
+----+-------+------+------+--------+------+--------+
| id | name
| sex
| age
| dep_id | id
| name
|
+----+-------+------+------+--------+------+--------+
|
1 | jason | male |
18 |
200 |
200 | 技术
|
|
5 | owen
| male |
18 |
200 |
200 | 技术
|
+----+-------+------+------+--------+------+--------+

像上面的方法也可以完成我们的需求，但是mysql中有专门对应的方法。

2.1、内连接：只取到两张表有对应关系的记录

mysql> select * from emp inner join dep on emp.dep_id=dep.id;
+----+-------+--------+------+--------+------+--------------+
| id | name
| sex
| age
| dep_id | id
| name
|
+----+-------+--------+------+--------+------+--------------+
|
1 | jason | male
|
18 |
200 |
200 | 技术
|
|
2 | egon
| female |
48 |
201 |
201 | 人力资源
|
|
3 | kevin | male
|
38 |
201 |
201 | 人力资源
|
|
4 | nick
| female |
28 |
202 |
202 | 销售
|
|
5 | owen
| male
|
18 |
200 |
200 | 技术
|
+----+-------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)
mysql> select * from emp inner join dep on emp.dep_id=dep.id where dep.name='技术';
+----+-------+------+------+--------+------+--------+
| id | name
| sex
| age
| dep_id | id
| name
|
+----+-------+------+------+--------+------+--------+
|
1 | jason | male |
18 |
200 |
200 | 技术
|
|
5 | owen
| male |
18 |
200 |
200 | 技术
|
+----+-------+------+------+--------+------+--------+

 dep表中没有id=204的数据，因此只取到两张表共有对应的记录

2.2、左连接：在内连接的基础上保留左表没有对应关系的记录，右表对应的给null

mysql> select * from emp left join dep on emp.dep_id=dep.id;
+----+-------+--------+------+--------+------+--------------+
| id | name
| sex
| age
| dep_id | id
| name
|
+----+-------+--------+------+--------+------+--------------+
|
1 | jason | male
|
18 |
200 |
200 | 技术
|
|
5 | owen
| male
|
18 |
200 |
200 | 技术
|
|
2 | egon
| female |
48 |
201 |
201 | 人力资源
|
|
3 | kevin | male
|
38 |
201 |
201 | 人力资源
|
|
4 | nick
| female |
28 |
202 |
202 | 销售
|
|
6 | jerry | female |
18 |
204 | NULL | NULL
|
+----+-------+--------+------+--------+------+--------------+

2.3、右连接：在内连接的基础上保留右表没有对应关系的记录，左表给null

mysql> select * from emp right join dep on emp.dep_id=dep.id;
+------+-------+--------+------+--------+------+--------------+
| id
| name
| sex
| age
| dep_id | id
| name
|
+------+-------+--------+------+--------+------+--------------+
|
1 | jason | male
|
18 |
200 |
200 | 技术
|
|
2 | egon
| female |
48 |
201 |
201 | 人力资源
|
|
3 | kevin | male
|
38 |
201 |
201 | 人力资源
|
|
4 | nick
| female |
28 |
202 |
202 | 销售
|
|
5 | owen
| male
|
18 |
200 |
200 | 技术
|
| NULL | NULL
| NULL
| NULL |
NULL |
203 | 运营
|
+------+-------+--------+------+--------+------+--------------+

2.4、全连接：在内连接的基础上保留左、右两表所有记录（没有对应关系的也保留）

mysql> select * from emp left join dep on emp.dep_id=dep.id
-> union
-> select * from emp right join dep on emp.dep_id=dep.id;
+------+-------+--------+------+--------+------+--------------+
| id
| name
| sex
| age
| dep_id | id
| name
|
+------+-------+--------+------+--------+------+--------------+
|
1 | jason | male
|
18 |
200 |
200 | 技术
|
|
5 | owen
| male
|
18 |
200 |
200 | 技术
|
|
2 | egon
| female |
48 |
201 |
201 | 人力资源
|
|
3 | kevin | male
|
38 |
201 |
201 | 人力资源
|
|
4 | nick
| female |
28 |
202 |
202 | 销售
|
|
6 | jerry | female |
18 |
204 | NULL | NULL
| NULL | NULL
| NULL
| NULL |
NULL |
203 | 运营
|
+------+-------+--------+------+--------+------+--------------+

3、子查询

就是将一个查询语句的结果用括号括起来当作另外一个查询语句的条件去用

3.1、查询部门是技术或者人力资源的员工信息

首先获取技术部和人力资源部的id号，再去员工表里筛选出符合要求的员工信息

 select * from emp where dep_id in (select id from dep where name in ('技术','人力资源'));

mysql> select * from emp where dep_id in (select id from dep where name in ('技术','人力资源'));
+----+-------+--------+------+--------+
| id | name
| sex
| age
| dep_id |
+----+-------+--------+------+--------+
|
1 | jason | male
|
18 |
200 |
|
2 | egon
| female |
48 |
201 |
|
3 | kevin | male
|
38 |
201 |
|
5 | owen
| male
|
18 |
200 |
+----+-------+--------+------+--------+

3.2、每个部门最新入职的员工 思路：先查每个部门最新入职的员工，再按部门对应上联表查询

select t1.id,t1.name,t1.hire_date,t1.post,t2.* from emp as t1
inner join
(select post,max(hire_date) as max_date from emp group by post) as t2
on t1.post = t2.post
where t1.hire_date = t2.max_date
;

记住一个规律，表的查询结果可以作为其他表的查询条件，也可以通过其别名的方式把它作为一张虚拟表去跟其他表做关联查询

最后

以上就是等待巨人为你收集整理的mysql之单表查询(group by/having/order by/limit/聚合函数)与多表联查(内连接/左连接/右连接/全连接/子查询)一、单表查询二、多表查询的全部内容，希望文章能够帮你解决mysql之单表查询(group by/having/order by/limit/聚合函数)与多表联查(内连接/左连接/右连接/全连接/子查询)一、单表查询二、多表查询所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。