1.查詢語句的基本操作         - select         - from         - where:約束條件         - group by:分組         - having：過濾         - distinct：去重         - order by：排序         - limit：限制查詢記錄的數(shù)量         - 聚合函數(shù): count（計(jì)數(shù)）                    max（最大值）                    min（最小值）                    avg（平均值）                    sum(求和)

# 創(chuàng)建一張部門表 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, # 一個(gè)部門一個(gè)屋子   depart_id int );    # 插入記錄 # 三個(gè)部門：教學(xué)，銷售，運(yùn)營 insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values ('tank','male',17,'20170301','張江第一帥形象代言部門',7300.33,401,1), # 以下是教學(xué)部 ('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), # 以下是運(yùn)營部門 ('程咬金','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系統(tǒng)中，插入中文字符，select的結(jié)果為空白，可以將所有字符編碼統(tǒng)一設(shè)置成gbk   - select * from emp;  # 若數(shù)據(jù)比較多，比較凌亂，可以在表后面+ G - select * from empG

- select * from emp;  # 若數(shù)據(jù)比較多，比較凌亂，可以在表后面+ G     - select * from empG

 寫SQL語句必須遵循兩點(diǎn):  - 書寫順序: # 獲取id為 4、5的兩條記錄 select * from emp where id > 3 and id < 6;      - select     - from     - where        - 執(zhí)行順序:     比如: 圖書管理員，得先找到是哪一個(gè)圖書館(哪張表),    再找這本書在圖書館的哪個(gè)位置(哪一條記錄), 最后查找這個(gè)本書中某一頁(哪些字段值);     select * from emp where id > 3 and id < 6;     - from ---> 找到圖書館     - where ---> 找到書的位置     - select ---> 找到書本中的某一頁       注意: 必須記住SQL語句的 書寫順序 與 執(zhí)行順序(*******);

# PS: 根據(jù)執(zhí)行順序來書寫 SQL語句，一步一步來寫;  # 1.查詢id大于等于3小于等于6的數(shù)據(jù)(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的數(shù)據(jù) # 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個(gè)或多個(gè)任意字符(若前后都有%(%o%),查所有帶有索引字符的記錄,若頭有%而尾沒有(%o),則查所有尾帶有索引字符的記錄,若尾有%而頭沒有(o%),則查所有頭帶有索引字符的記錄,若前后都沒有%(o),則只會(huì)查詢只有索引字符的字符) # _: 匹配一個(gè)任意字符 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.查找名字個(gè)數(shù)為3個(gè)的員工 名字 與 薪資 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): 計(jì)算名字字符的長度 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的數(shù)據(jù) # 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范圍的數(shù)據(jù) select * from emp where salary not in (20000, 18000, 17000);    # 7.查詢崗位描述為空的 員工名 與 崗位名 post_comment # 用等于號(hào)無法判定空字段 select name, post from emp where post_comment = null;  select * from emp where post_comment = null; Empty set (0.00 sec)  # 注意: 針對(duì)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              - 執(zhí)行順序:                 - from                 - where                 - group by                 - select # 什么叫分組                # 比如: 一張員工表中有性別字段，可以根據(jù)性別分組，一組是男性，一組是女性，或者是根據(jù)部門分組，有教學(xué)部、銷售部等...       # 1.根據(jù)部門分組  # 非嚴(yán)格模式下可以獲取 分組條件post 以外的字段數(shù)據(jù) select post, salary from emp group by post;  # 設(shè)置嚴(yán)格模式: show variables like "%mode%"; # 全局設(shè)置: 永久有效 set global sql_mode="strict_trans_tables,only_full_group_by";   """ 設(shè)置sql_mode為only_full_group_by，意味著以后但凡分組，只能取到分組的依據(jù)， 不應(yīng)該在去取組里面的單個(gè)元素的值，那樣的話分組就沒有意義了，并且會(huì)報(bào)錯(cuò),因?yàn)椴环纸M就是對(duì)單個(gè)元素信息的隨意獲取 """ mysql> select post, salary from emp group by post; ERROR 1046 (3D000): No database selected      # 可以同聚合函數(shù)，間接獲取其他字段數(shù)據(jù)  聚合函數(shù):    count: 計(jì)數(shù)    max: 最大值    min: 最小值    avg: 平均值    sum: 求和    group_concat(name): 可以將分組后的 所有名字獲取并進(jìn)行拼接     # 指定以:拼接 select post, group_concat(name) from emp group by post; select post, group_concat(name, ':') from emp group by post;          2.獲取每個(gè) 部門 的最高工資  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)  補(bǔ)充: as 別名: 可以給字段 加一個(gè) 別名 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.每個(gè)部門的最低工資 select post, min(salary) from emp group by post;  4.每個(gè)部門的平均工資 select post, avg(salary) from emp group by post;  5.每個(gè)部門的工資總和 select post, sum(salary) from emp group by post;  6.每個(gè)部門的員工個(gè)數(shù) # count(): 括號(hào)中可以填任意非空值 select post, count(salary) from emp group by post; select post, count(post_comment) from emp group by post;   查詢崗位名以及各崗位內(nèi)包含的員工個(gè)數(shù) select post, count(id) from emp group by post; 查詢公司內(nèi)男員工和女員工的個(gè)數(shù) select sex, count(*) from emp group by sex;  查詢男員工與男員工的平均薪資，女員工與女員工的平均薪資 select sex, avg(salary) from emp group by sex;  .統(tǒng)計(jì)各部門年齡在30歲以上的員工平均工資: # 步驟: 先找到表，再找年齡30歲以上，再根據(jù)部門分組，最后求平均薪資; select post, avg(salary) from emp where age > 30 group by post;

# 聚合函數(shù): 若沒有g(shù)roup by 分組，默認(rèn)將查出來的數(shù)據(jù)當(dāng)做一個(gè)分組, 也能使用; select max(salary) from emp;

 # group_concat(name): 可以將分組后的 所有名字獲取并進(jìn)行拼接  # 默認(rèn)以, 拼接 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)  # 給每個(gè)部門的員工名字前 + NB_ select post, group_concat('NB_', name) from emp group by post; # 拼接部門員工名字+薪資 select post, group_concat(name, ":", salary) from emp group by post;

# 4.補(bǔ)充concat（不分組時(shí)用）拼接字符串達(dá)到更好的顯示效果 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 不能使用聚合函數(shù)，但having可以;              - 書寫順序:                 - select                 - from                 - where                 - group by                 - having              - 執(zhí)行順序:                 - from                 - where                 - group by                 - having                 - select   1、統(tǒng)計(jì)各 部門 年齡在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  執(zhí)行順序: 		- from 		- where  		- group by  		- having 		- select  		- distinct          # 注意: 查詢的字段值必須是重復(fù)的才有效，只要有一個(gè)字段值是不重復(fù)的就沒有效果。  # 若所查字段內(nèi)有不重復(fù)的字段記錄就不會(huì)去重 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  執(zhí)行順序: 		- from 		- where  		- group by  		- having  		- select      - order by  # 通過select 查出來的數(shù)據(jù)再進(jìn)行排序      # order by 默認(rèn)升序 # asc升序 # desc降序  # 1、根據(jù)薪資進(jìn)行升序 select name, salary from emp order by salary;  # 默認(rèn)升序  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;   # 統(tǒng)計(jì) 各部門(分組) 年齡在10歲以上的員工平均工資，并且保留平均工資大于1000的部門，然后對(duì)平均工資進(jìn)行升序序 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              執(zhí)行順序:                 - 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可以有兩個(gè)參數(shù), 參數(shù)1:是限制的開始位置, 參數(shù)2:是從開始位置展示的條數(shù); 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、查詢工資最高的人的詳細(xì)信息 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開頭的，基本上都是跟正則有關(guān)   # *: 代表 0 或 多個(gè) 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 );  #插入數(shù)據(jù) insert into dep2 values (200,'技術(shù)'), (201,'人力資源'), (202,'銷售'), (203,'運(yùn)營');  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 | 技術(shù)         | |  201 | 人力資源     | |  202 | 銷售         | |  203 | 運(yùn)營         | +------+--------------+ 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 | +----+-------+--------+------+--------+  # 指到了如何根據(jù)表關(guān)系對(duì)字段進(jìn)行拆分，目的是為了更好的管理，表數(shù)據(jù)都存放在硬盤中，存不是目的，目的是為了取，所以我們將數(shù)據(jù)從硬盤讀到內(nèi)存中，接下來我們因應(yīng)該將他們拼成一張表來查詢更加合理;  # 注意: 將拆分的表，再拼接到一起進(jìn)行查詢, 可以通過一張表查另一張表的數(shù)據(jù);

# 左表的一條記錄與右表的一條記錄都對(duì)應(yīng)一遍稱之為 --> "笛卡爾積"   PS: 百度科普 # 將所有的數(shù)據(jù)都對(duì)應(yīng)了一遍，雖然不合理但是其中有合理的數(shù)據(jù)，現(xiàn)在我們需要做的就是找出合理的數(shù)據(jù)

舉例:通過之前準(zhǔn)備的數(shù)據(jù)進(jìn)行多表操作      1、查詢 員工 以及所在 部門 的信息; # 通過where 約束條件 select * from emp2, dep2 where emp2.dep_id = dep2.id;  2、查詢 部門 為 技術(shù)部 的 員工 及 部門信息  select * from emp2, dep2 where emp2.dep_id = dep2.id and dep2.name = '技術(shù)';

聯(lián)表查詢:    -內(nèi)鏈接:只去兩張表有對(duì)應(yīng)關(guān)系的記錄 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 | 技術(shù)         | |  2 | egon  | female |   48 |    201 |  201 | 人力資源     | |  3 | kevin | male   |   38 |    201 |  201 | 人力資源     | |  4 | jason | female |   28 |    202 |  202 | 銷售         | |  5 | owen  | male   |   18 |    200 |  200 | 技術(shù)         | +----+-------+--------+------+--------+------+--------------+ 5 rows in set (0.05 sec)  2、left join # 2、左連接: 在內(nèi)連接的基礎(chǔ)上保留左表沒有對(duì)應(yīng)關(guān)系的記錄(以null補(bǔ)全) 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 | 技術(shù)         | |  5 | owen  | male   |   18 |    200 |  200 | 技術(shù)         | |  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、右連接: 在內(nèi)連接的基礎(chǔ)上保留右表沒有對(duì)應(yīng)關(guān)系的記錄 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 | 技術(shù)         | |    2 | egon  | female |   48 |    201 |  201 | 人力資源     | |    3 | kevin | male   |   38 |    201 |  201 | 人力資源     | |    4 | jason | female |   28 |    202 |  202 | 銷售         | |    5 | owen  | male   |   18 |    200 |  200 | 技術(shù)         | | NULL | NULL  | NULL   | NULL |   NULL |  203 | 運(yùn)營         | +------+-------+--------+------+--------+------+--------------+ 6 rows in set (0.00 sec)   - 全連接: union # 4、全連接: 在內(nèi)連接的基礎(chǔ)上 保留左、右表沒有對(duì)應(yīng)關(guān)系的記錄 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;

# 子查詢就是將一個(gè)查詢語句的結(jié)果用括號(hào)括起來，當(dāng)做另一個(gè)查詢語句的條件去用  # 查詢部門是技術(shù)或者人力資源的員工信息 ''' 先獲取技術(shù)部和人力資源的id號(hào)，再去員工表里根據(jù)前面的id篩選出符合要求的員工信息; ''' select * from emp2 where dep_id in (select id from dep2 where name='技術(shù)' or name='人力資源');   # 2.每個(gè)部門最新入職的員工 思路：先查每個(gè)部門最新入職的員工，再按部門對(duì)應(yīng)上聯(lián)表查詢  # 查第一張emp表 # 第四步 select t1.id, t1.name, t1.hire_date, t2.* from # 第二步 # 拼接了 t1（emp） 與 t2（各部門最新入職的員工數(shù)據(jù)虛擬表）表的數(shù)據(jù)  emp as t1 inner join  # 第一步 # 子查詢獲取emp表中的  部門名稱與最新入職的時(shí)間字段值 生成一張?zhí)摂M表 (select post, max(hire_date) as max_date from emp group by post) as t2 on # 若 t1.post 的值與 t2.post 條件成立，則拼接對(duì)應(yīng)的記錄; t1.post = t2.post # 第三步 where t1.hire_date = t2.max_date;