一.查询语句的基本操作

1.查询语句的基本操作
        - select
        - from
        - where:约束条件
        - group by:分组
        - having：过滤
        - distinct：去重
        - order by：排序
        - limit：限制查询记录的数量
        - 聚合函数: count（计数）
                   max（最大值）
                   min（最小值）
                   avg（平均值）
                   sum(求和)

二.单表查询

1、前期表与数据准备

# 创建一张部门表
create table emp(
  id int not null unique 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
('tank','male',17,'20170301','张江第一帅形象代言部门',7300.33,401,1), # 以下是教学部
('egon','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('jason','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jerry','female',18,'20110211','teacher',9000,401,1),
('大饼','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


- select * from emp;  # 若数据比较多，比较凌乱，可以在表后面+ \G
- select * from emp\G


- select * from emp;  # 若数据比较多，比较凌乱，可以在表后面+ \G    
- select * from emp\G

MySQL之查询语句的基本操作

重点：

 写SQL语句必须遵循两点:
 - 书写顺序:
# 获取id为 4、5的两条记录
select * from emp where id > 3 and id < 6;

    - select
    - from
    - where
    
  - 执行顺序:

   比如: 图书管理员，得先找到是哪一个图书馆(哪张表),
   再找这本书在图书馆的哪个位置(哪一条记录), 最后查找这个本书中某一页(哪些字段值);

   select * from emp where id > 3 and id < 6;
    - from ---> 找到图书馆
    - where ---> 找到书的位置
    - select ---> 找到书本中的某一页
    
 注意: 必须记住SQL语句的 书写顺序 与 执行顺序(*******);

1.where(约束条件)

# PS: 根据执行顺序来书写 SQL语句，一步一步来写;

# 1.查询id大于等于3小于等于6的数据(and:与)

select * from emp where id >= 3 and id <= 6;

mysql> SELECT * FROM EMP WHERE ID > 3 AND ID <= 6;
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name  | sex    | age | hire_date  | post    | post_comment | salary  | office | depart_id |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
|  4 | jason | 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 |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
3 rows in set (0.00 sec)


#可以使用between()and()  :两者之间


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 | jason | 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.查询薪资是20000或者18000或者17000的数据
# or:  或者
select * from emp where salary=20000 or salary=18000 or salary=17000;
# in: 在什么里
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.00 sec)

# 也可以使用in(3,4,5,6)
 select * from emp where id in(3,4,5,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 | jason | 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)


# 3.查询员工姓名中包含o字母 的 员工姓名和薪资
# like: 模糊匹配
# %: 匹配0个或多个任意字符(若前后都有%(%o%),查所有带有索引字符的记录,若头有%而尾没有(%o),则查所有尾带有索引字符的记录,若尾有%而头没有(o%),则查所有头带有索引字符的记录,若前后都没有%(o),则只会查询只有索引字符的字符)
# _: 匹配一个任意字符
select name, salary from emp where name like "%o%";
+-------+------------+
| name  | salary     |
+-------+------------+
| egon  | 1000000.31 |
| jason |    3500.00 |
| owen  |    2100.00 |
+-------+------------+
3 rows in set (0.00 sec)


mysql> select name, salary from emp where name like "o";
+------+--------+
| name | salary |
+------+--------+
| o    |   NULL |
+------+--------+
1 row in set (0.00 sec)


# 4.查找名字个数为3个的员工 名字 与 薪资
select name, salary from emp where name like "___";
+-----------+----------+
| name      | salary   |
+-----------+----------+
| 程咬金    | 20000.00 |
| 程咬银    | 19000.00 |
| 程咬铜    | 18000.00 |
| 程咬铁    | 17000.00 |
+-----------+----------+
4 rows in set (0.00 sec)

# 或者 # char_length(name): 计算名字字符的长度
select name, salary from emp where char_length(name) = 4;
    
mysql> select name, salary from emp where char_length(name) = 3;
+-----------+----------+
| name      | salary   |
+-----------+----------+
| 程咬金    | 20000.00 |
| 程咬银    | 19000.00 |
| 程咬铜    | 18000.00 |
| 程咬铁    | 17000.00 |
+-----------+----------+
4 rows in set (0.00 sec)


# 5.查询id小于3或者大于6的数据
# not in: 不再什么什么中
select * from emp where id not in (3, 4, 5, 6);
select * from emp where id not between 3 and 6;


# 6.查询薪资不在20000，18000，17000范围的数据
select * from emp where salary not in (20000, 18000, 17000);


 # 7.查询岗位描述为空的 员工名 与 岗位名 post_comment
# 用等于号无法判定空字段
select name, post from emp where post_comment = null;

select * from emp where post_comment = null;
Empty set (0.00 sec)

# 注意: 针对null的值 需要使用 is
select name, post from emp where post_comment is null;

select name, post_comment from emp where post_comment is null;
+-----------+--------------+
| name      | post_comment |
+-----------+--------------+
| tank      | NULL         |
| egon      | NULL         |
| kevin     | NULL         |
| jason     | NULL         |
| owen      | NULL         |
| jerry     | NULL         |
| 大饼      | NULL         |
| sean      | NULL         |
| 歪歪      | NULL         |
| 丫丫      | NULL         |
| 丁丁      | NULL         |
| 星星      | NULL         |
| 格格      | NULL         |
| 张野      | NULL         |
| 程咬金    | NULL         |
| 程咬银    | NULL         |
| 程咬铜    | NULL         |
| 程咬铁    | NULL         |
| o         | NULL         |
+-----------+--------------+
19 rows in set (0.00 sec)

2.group by(分组)

  - 书写顺序:
                - select
                - from
                - where
                - group by

            - 执行顺序:
                - from
                - where
                - group by
                - select
# 什么叫分组               
# 比如: 一张员工表中有性别字段，可以根据性别分组，一组是男性，一组是女性，或者是根据部门分组，有教学部、销售部等...    

 # 1.根据部门分组
 # 非严格模式下可以获取 分组条件post 以外的字段数据
select post, salary from emp group by post;

# 设置严格模式:
show variables like "%mode%";
# 全局设置: 永久有效
set global sql_mode="strict_trans_tables,only_full_group_by";


"""
设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据，
不应该在去取组里面的单个元素的值，那样的话分组就没有意义了，并且会报错,因为不分组就是对单个元素信息的随意获取
"""
mysql> select post, salary from emp group by post;
ERROR 1046 (3D000): No database selected
    
# 可以同聚合函数，间接获取其他字段数据
 聚合函数:
   count: 计数
   max: 最大值
   min: 最小值
   avg: 平均值
   sum: 求和
   group_concat(name): 可以将分组后的 所有名字获取并进行拼接
   
# 指定以:拼接
select post, group_concat(name) from emp group by post;
select post, group_concat(name, ':') from emp group by post;
    
  
 2.获取每个 部门 的最高工资
 select post,max(salary) from emp group by post;
mysql>  select post,max(salary) from emp group by post;
+-----------------------------------+-------------+
| post                              | max(salary) |
+-----------------------------------+-------------+
| NULL                              |        NULL |
| operation                         |    20000.00 |
| sale                              |     4000.33 |
| teacher                           |  1000000.31 |
| 张江第一帅形象代言部门            |     7300.33 |
+-----------------------------------+-------------+
5 rows in set (0.00 sec)

补充: as 别名: 可以给字段 加一个 别名
select post as '部门', max(salary) as '薪资'
from emp group by post;

# 也可以简写,但是不推荐
select post '部门', max(salary) '薪资'
from emp group by post;

mysql> select post as '部门', max(salary) as'薪资'
    -> from emp group by post;
+-----------------------------------+------------+
| 部门                              | 薪资       |
+-----------------------------------+------------+
| NULL                              |       NULL |
| operation                         |   20000.00 |
| sale                              |    4000.33 |
| teacher                           | 1000000.31 |
| 张江第一帅形象代言部门            |    7300.33 |
+-----------------------------------+------------+
5 rows in set (0.00 sec)


3.每个部门的最低工资
select post, min(salary) from emp group by post;

4.每个部门的平均工资
select post, avg(salary) from emp group by post;

5.每个部门的工资总和
select post, sum(salary) from emp group by post;

6.每个部门的员工个数
# count(): 括号中可以填任意非空值
select post, count(salary) from emp group by post;
select post, count(post_comment) from emp group by post;


查询岗位名以及各岗位内包含的员工个数
select post, count(id) from emp group by post;
查询公司内男员工和女员工的个数
select sex, count(*) from emp group by sex;

查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex, avg(salary) from emp group by sex;

.统计各部门年龄在30岁以上的员工平均工资:
# 步骤: 先找到表，再找年龄30岁以上，再根据部门分组，最后求平均薪资;
select post, avg(salary) from emp where age > 30 group by post;

总结: 聚合函数，必须跟在group by 后面(执行顺序);

# 聚合函数: 若没有group by 分组，默认将查出来的数据当做一个分组, 也能使用;
select max(salary) from emp;



 # group_concat(name): 可以将分组后的 所有名字获取并进行拼接
 # 默认以, 拼接
select post, group_concat(name) from emp group by post;
 # 指定以:拼接
select post, group_concat(name, ':') from emp group by post;
# 
select post, group_concat('Name: ', name) from emp group by post;


select post, group_coucat(name) from emp group by post;

mysql> select post, group_concat(name) from emp group by post;
+-----------------------------------+------------------------------------------------+
| post                              | group_concat(name)                             |
+-----------------------------------+------------------------------------------------+
| NULL                              | o                                              |
| operation                         | 程咬铁,程咬铜,程咬银,程咬金,张野               |
| sale                              | 格格,星星,丁丁,丫丫,歪歪                       |
| teacher                           | sean,大饼,jerry,owen,jason,kevin,egon          |
| 张江第一帅形象代言部门            | tank                                           |
+-----------------------------------+------------------------------------------------+
5 rows in set (0.00 sec)

# 给每个部门的员工名字前 + NB_
select post, group_concat('NB_', name) from emp group by post;
# 拼接部门员工名字+薪资
select post, group_concat(name, ":", salary) from emp group by post;

补充:concat

# 4.补充concat（不分组时用）拼接字符串达到更好的显示效果 as语法并用
select concat('Name: ', name) as '名字', concat('Sal: ', salary) as '薪资' from emp;


mysql> select concat('name: ', name) as '名字',concat('sal: ',salary) as '薪资' from emp;
+-----------------+-----------------+
| 名字            | 薪资            |
+-----------------+-----------------+
| name: tank      | sal: 7300.33    |
| name: egon      | sal: 1000000.31 |
| name: kevin     | sal: 8300.00    |
| name: jason     | sal: 3500.00    |
| name: owen      | sal: 2100.00    |
| name: jerry     | sal: 9000.00    |
| name: 大饼      | 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   |
| name: o         | NULL            |
+-----------------+-----------------+
19 rows in set (0.00 sec)

3.having(过滤)

1.having与where语法一样，只不过having必需要在group by后使用;
2.where 不能使用聚合函数，但having可以;

            - 书写顺序:
                - select
                - from
                - where
                - group by
                - having

            - 执行顺序:
                - from
                - where
                - group by
                - having
                - select

 1、统计各 部门 年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门;
select post, avg(salary) from emp where age > 30 group by post having avg(salary) > 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)

4.distinct(去重)

# distinct: 去重

- 书写顺序:
         - select
         - distinct
         - from
         - where
         - group by
         - having

执行顺序:
        - from
        - where 
        - group by 
        - having
        - select 
        - distinct
        
# 注意: 查询的字段值必须是重复的才有效，只要有一个字段值是不重复的就没有效果。

# 若所查字段内有不重复的字段记录就不会去重
select distinct id, post from emp;

mysql> select distinct id, post from emp;
+----+-----------------------------------+
| id | post                              |
+----+-----------------------------------+
|  1 | 张江第一帅形象代言部门            |
|  2 | teacher                           |
|  3 | teacher                           |
|  4 | teacher                           |
|  5 | teacher                           |
|  6 | teacher                           |
|  7 | teacher                           |
|  8 | teacher                           |
|  9 | sale                              |
| 10 | sale                              |
| 11 | sale                              |
| 12 | sale                              |
| 13 | sale                              |
| 14 | operation                         |
| 15 | operation                         |
| 16 | operation                         |
| 17 | operation                         |
| 18 | operation                         |
| 19 | NULL                              |
+----+-----------------------------------+
19 rows in set (0.00 sec)




select distinct post from emp;

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

5.order by(排序)

- 书写顺序:
        - select
        - from
        - where
        - group by
        - having
        - order by

执行顺序:
        - from
        - where 
        - group by 
        - having 
        - select 
    - order by  # 通过select 查出来的数据再进行排序
    
# order by 默认升序
# asc升序
# desc降序

# 1、根据薪资进行升序
select name, salary from emp order by salary;  # 默认升序

mysql> select name, salary from emp order by salary;
+-----------+------------+
| name      | salary     |
+-----------+------------+
| o         |       NULL |
| 丁丁      |    1000.37 |
| 丫丫      |    2000.35 |
| owen      |    2100.00 |
| 歪歪      |    3000.13 |
| 星星      |    3000.29 |
| jason     |    3500.00 |
| 格格      |    4000.33 |
| tank      |    7300.33 |
| kevin     |    8300.00 |
| jerry     |    9000.00 |
| sean      |   10000.00 |
| 张野      |   10000.13 |
| 程咬铁    |   17000.00 |
| 程咬铜    |   18000.00 |
| 程咬银    |   19000.00 |
| 程咬金    |   20000.00 |
| 大饼      |   30000.00 |
| egon      | 1000000.31 |
+-----------+------------+
19 rows in set (0.00 sec)



2.select * from emp order by salary asc;  # 指定升序

3.select name, salary from emp order by salary desc;  # 指定降序

 
mysql> select name, salary from emp order by salary desc;
+-----------+------------+
| name      | salary     |
+-----------+------------+
| egon      | 1000000.31 |
| 大饼      |   30000.00 |
| 程咬金    |   20000.00 |
| 程咬银    |   19000.00 |
| 程咬铜    |   18000.00 |
| 程咬铁    |   17000.00 |
| 张野      |   10000.13 |
| sean      |   10000.00 |
| jerry     |    9000.00 |
| kevin     |    8300.00 |
| tank      |    7300.33 |
| 格格      |    4000.33 |
| jason     |    3500.00 |
| 星星      |    3000.29 |
| 歪歪      |    3000.13 |
| owen      |    2100.00 |
| 丫丫      |    2000.35 |
| 丁丁      |    1000.37 |
| o         |       NULL |
+-----------+------------+
19 rows in set (0.00 sec)

# 先按照age升序，再按照salary降序
select age, salary from emp order by age asc, salary desc;


# 统计 各部门(分组) 年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行升序序
select post, avg(salary) from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary);

9.limit(限制查询记录的数量)

 书写顺序:
                - select
                - from
                - order by
                - limit

            执行顺序:
                - from
                - select
                - order by
                - limit

                
                
# 1、从第一条开始，获取4条记录;
select * from emp limit 4;

mysql> select * from emp limit 4;
+----+-------+------+-----+------------+-----------------------------------+--------------+------------+--------+-----------+
| id | name  | sex  | age | hire_date  | post                              | post_comment | salary     | office | depart_id |
+----+-------+------+-----+------------+-----------------------------------+--------------+------------+--------+-----------+
|  1 | tank  | male |  17 | 2017-03-01 | 张江第一帅形象代言部门            | NULL         |    7300.33 |    401 |         1 |
|  2 | egon  | male |  78 | 2015-03-02 | teacher                           | NULL         | 1000000.31 |    401 |         1 |
|  3 | kevin | male |  81 | 2013-03-05 | teacher                           | NULL         |    8300.00 |    401 |         1 |
|  4 | jason | male |  73 | 2014-07-01 | teacher                           | NULL         |    3500.00 |    401 |         1 |
+----+-------+------+-----+------------+-----------------------------------+--------------+------------+--------+-----------+
4 rows in set (0.00 sec)



# 2、limit可以有两个参数, 参数1:是限制的开始位置, 参数2:是从开始位置展示的条数;
select * from emp limit 0, 4;
select * from emp limit 4, 4;




mysql> select * from emp limit 4, 4;
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name   | sex    | age | hire_date  | post    | post_comment | salary   | office | depart_id |
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
|  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 |
|  7 | 大饼   | male   |  18 | 1900-03-01 | teacher | NULL         | 30000.00 |    401 |         1 |
|  8 | sean   | male   |  48 | 2010-11-11 | teacher | NULL         | 10000.00 |    401 |         1 |
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
4 rows in set (0.00 sec)

# 3、查询工资最高的人的详细信息
select * from emp order by salary limit 1;


mysql> select * from emp order by salary limit 1;
+----+------+------+-----+------------+------+--------------+--------+--------+-----------+
| id | name | sex  | age | hire_date  | post | post_comment | salary | office | depart_id |
+----+------+------+-----+------------+------+--------------+--------+--------+-----------+
| 19 | o    | male |  28 | 0000-00-00 | NULL | NULL         |   NULL |   NULL |      NULL |
+----+------+------+-----+------------+------+--------------+--------+--------+-----------+
1 row in set (0.00 sec)

10.正则

# 在编程中，凡是看到reg开头的，基本上都是跟正则有关
  # *: 代表 0 或 多个
select * from emp where name regexp '^程.*(金|银|铜|铁)$';

mysql> select * from emp where name regexp '^程.*(金|银|铜|铁)$';
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 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 |
| 16 | 程咬银    | female |  18 | 2013-03-11 | operation | NULL         | 19000.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 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
4 rows in set (0.00 sec)

三.多表查询

多表查询

-关联查询

-子查询

创建表与插入数据准备

#建表
create table dep2(
id int,
name varchar(20) 
);

create table emp2(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into dep2 values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into emp2(name,sex,age,dep_id) values
('tank','male',17,200),
('egon','female',48,201),
('kevin','male',38,201),
('jason','female',28,202),
('owen','male',18,200),
('sean','female',18,204);



mysql> select * from dep2;
+------+--------------+
| id   | name         |
+------+--------------+
|  200 | 技术         |
|  201 | 人力资源     |
|  202 | 销售         |
|  203 | 运营         |
+------+--------------+
4 rows in set (0.00 sec)



mysql> select * from emp2;
+----+-------+--------+------+--------+
| id | name  | sex    | age  | dep_id |
+----+-------+--------+------+--------+
|  1 | tank  | male   |   17 |    200 |
|  2 | egon  | female |   48 |    201 |
|  3 | kevin | male   |   38 |    201 |
|  4 | jason | female |   28 |    202 |
|  5 | owen  | male   |   18 |    200 |
|  6 | sean  | female |   18 |    204 |
+----+-------+--------+------+--------+

# 指到了如何根据表关系对字段进行拆分，目的是为了更好的管理，表数据都存放在硬盘中，存不是目的，目的是为了取，所以我们将数据从硬盘读到内存中，接下来我们因应该将他们拼成一张表来查询更加合理;

# 注意: 将拆分的表，再拼接到一起进行查询, 可以通过一张表查另一张表的数据;

1.关联查询

# 左表的一条记录与右表的一条记录都对应一遍称之为 --> "笛卡尔积"   PS: 百度科普
# 将所有的数据都对应了一遍，虽然不合理但是其中有合理的数据，现在我们需要做的就是找出合理的数据



举例:通过之前准备的数据进行多表操作
    
1、查询 员工 以及所在 部门 的信息;
# 通过where 约束条件
select * from emp2, dep2 where emp2.dep_id = dep2.id;

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

select * from emp2, dep2 where emp2.dep_id = dep2.id and dep2.name = '技术';


联表查询:
   -内链接:只去两张表有对应关系的记录
select * from emp2 inner join dep2 on emp2.dep_id = dep2.id;

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

2、left join
# 2、左连接: 在内连接的基础上保留左表没有对应关系的记录(以null补全)
select * from emp2 left join dep2 on emp2.dep_id = dep2.id;

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

   
# 3、右连接: 在内连接的基础上保留右表没有对应关系的记录
select * from emp2 right join dep2 on emp2.dep_id = dep2.id;


mysql> select * from emp2 right join dep2 on emp2.dep_id = dep2.id;
+------+-------+--------+------+--------+------+--------------+
| id   | name  | sex    | age  | dep_id | id   | name         |
+------+-------+--------+------+--------+------+--------------+
|    1 | tank  | male   |   17 |    200 |  200 | 技术         |
|    2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|    3 | kevin | male   |   38 |    201 |  201 | 人力资源     |
|    4 | jason | female |   28 |    202 |  202 | 销售         |
|    5 | owen  | male   |   18 |    200 |  200 | 技术         |
| NULL | NULL  | NULL   | NULL |   NULL |  203 | 运营         |
+------+-------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)


- 全连接: union
# 4、全连接: 在内连接的基础上 保留左、右表没有对应关系的记录
select * from emp2 left join dep2 on emp2.dep_id = dep2.id 
union 
select * from emp2 right join dep2 on emp2.dep_id = dep2.id;

2.子查询

# 子查询就是将一个查询语句的结果用括号括起来，当做另一个查询语句的条件去用

# 查询部门是技术或者人力资源的员工信息
'''
先获取技术部和人力资源的id号，再去员工表里根据前面的id筛选出符合要求的员工信息;
'''
select * from emp2 where dep_id in (select id from dep2 where name='技术' or name='人力资源');

 # 2.每个部门最新入职的员工 思路：先查每个部门最新入职的员工，再按部门对应上联表查询
 # 查第一张emp表
# 第四步
select t1.id, t1.name, t1.hire_date, t2.*
from
# 第二步
# 拼接了 t1（emp） 与 t2（各部门最新入职的员工数据虚拟表）表的数据

emp as t1
inner join

# 第一步
# 子查询获取emp表中的  部门名称与最新入职的时间字段值 生成一张虚拟表
(select post, max(hire_date) as max_date from emp group by post) as t2
on
# 若 t1.post 的值与 t2.post 条件成立，则拼接对应的记录;
t1.post = t2.post
# 第三步
where t1.hire_date = t2.max_date;