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

# 创建一张部门表 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 empG

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

 写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语句的 书写顺序 与 执行顺序(*******);

# 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)

  - 书写顺序:                 - 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 分组，默认将查出来的数据当做一个分组, 也能使用; 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;

# 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)

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)

# 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)

- 书写顺序:         - 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);

 书写顺序:                 - 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)

# 在编程中，凡是看到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 | +----+-------+--------+------+--------+  # 指到了如何根据表关系对字段进行拆分，目的是为了更好的管理，表数据都存放在硬盘中，存不是目的，目的是为了取，所以我们将数据从硬盘读到内存中，接下来我们因应该将他们拼成一张表来查询更加合理;  # 注意: 将拆分的表，再拼接到一起进行查询, 可以通过一张表查另一张表的数据;

# 左表的一条记录与右表的一条记录都对应一遍称之为 --> "笛卡尔积"   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;

# 子查询就是将一个查询语句的结果用括号括起来，当做另一个查询语句的条件去用  # 查询部门是技术或者人力资源的员工信息 ''' 先获取技术部和人力资源的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;