mysql 关联关系
生活随笔
收集整理的這篇文章主要介紹了
mysql 关联关系
小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.
一 單表查詢的語法
SELECT 字段1,字段2... FROM 表名WHERE 條件GROUP BY fieldHAVING 篩選ORDER BY fieldLIMIT 限制條數(shù)二 關(guān)鍵字的執(zhí)行優(yōu)先級(jí)(重點(diǎn))
重點(diǎn)中的重點(diǎn)：關(guān)鍵字的執(zhí)行優(yōu)先級(jí) from where group by having select distinct order by limit1.找到表:from
2.拿著where指定的約束條件，去文件/表中取出一條條記錄
3.將取出的一條條記錄進(jìn)行分組group by，如果沒有g(shù)roup by，則整體作為一組
4.將分組的結(jié)果進(jìn)行having過濾
5.執(zhí)行select
6.去重
7.將結(jié)果按條件排序：order by
8.限制結(jié)果的顯示條數(shù)
http://www.cnblogs.com/panfb/p/8043563.html
三 簡(jiǎn)單查詢
company.employee員工id id int 姓名 emp_name varchar性別 sex enum年齡 age int入職日期 hire_date date崗位 post varchar職位描述 post_comment varchar薪水 salary double辦公室 office int部門編號(hào) depart_id int#創(chuàng)建表 create table employee( 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 ); #查看表結(jié)構(gòu) mysql> desc employee; +--------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | sex | enum('male','female') | NO | | male | | | age | int(3) unsigned | NO | | 28 | | | hire_date | date | NO | | NULL | | | post | varchar(50) | YES | | NULL | | | post_comment | varchar(100) | YES | | NULL | | | salary | double(15,2) | YES | | NULL | | | office | int(11) | YES | | NULL | | | depart_id | int(11) | YES | | NULL | | +--------------+-----------------------+------+-----+---------+----------------+ #插入記錄 #三個(gè)部門：教學(xué)，銷售，運(yùn)營(yíng) insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values ('egon','male',18,'20170301','老男孩駐沙河辦事處外交大使',7300.33,401,1), #以下是教學(xué)部 ('alex','male',78,'20150302','teacher',1000000.31,401,1), ('wupeiqi','male',81,'20130305','teacher',8300,401,1), ('yuanhao','male',73,'20140701','teacher',3500,401,1), ('liwenzhou','male',28,'20121101','teacher',2100,401,1), ('jingliyang','female',18,'20110211','teacher',9000,401,1), ('jinxin','male',18,'19000301','teacher',30000,401,1), ('成龍','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)營(yíng)部門 ('程咬金','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 #簡(jiǎn)單查詢 SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id FROM employee;SELECT * FROM employee;SELECT name,salary FROM employee;#避免重復(fù)DISTINCT SELECT DISTINCT post FROM employee; #通過四則運(yùn)算查詢 SELECT name, salary*12 FROM employee; SELECT name, salary*12 AS Annual_salary FROM employee; SELECT name, salary*12 Annual_salary FROM employee; #定義顯示格式 CONCAT() 函數(shù)用于連接字符串 SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary FROM employee; CONCAT_WS() 第一個(gè)參數(shù)為分隔符 SELECT CONCAT_WS(':',name,salary*12) AS Annual_salary FROM employee;小練習(xí)：
1 查出所有員工的名字，薪資,格式為<名字:egon> <薪資:3000> 2 查出所有的崗位（去掉重復(fù)） 3 查出所有員工名字，以及他們的年薪,年薪的字段名為annual_year select concat('<名字:',name,'> ','<薪資:',salary,'>') from employee; select distinct depart_id from employee; select name,salary*12 annual_salary from employee; ?四 WHERE約束
where字句中可以使用：
1. 比較運(yùn)算符：> < >= <= <> !=
2. between 80 and 100 值在10到20之間
3. in(80,90,100) 值是10或20或30
4. like 'egon%'
? ? pattern可以是%或_，
? ? %表示任意多字符
? ? _表示一個(gè)字符
5. 邏輯運(yùn)算符：在多個(gè)條件直接可以使用邏輯運(yùn)算符 and or not
小練習(xí)：
1. 查看崗位是teacher的員工姓名、年齡 2. 查看崗位是teacher且年齡大于30歲的員工姓名、年齡 3. 查看崗位是teacher且薪資在9000-1000范圍內(nèi)的員工姓名、年齡、薪資 4. 查看崗位描述不為NULL的員工信息 5. 查看崗位是teacher且薪資是10000或9000或30000的員工姓名、年齡、薪資 6. 查看崗位是teacher且薪資不是10000或9000或30000的員工姓名、年齡、薪資 7. 查看崗位是teacher且名字是jin開頭的員工姓名、年薪 select name,age from employee where post = 'teacher'; select name,age from employee where post='teacher' and age > 30; select name,age,salary from employee where post='teacher' and salary between 9000 and 10000; select * from employee where post_comment is not null; select name,age,salary from employee where post='teacher' and salary in (10000,9000,30000); select name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000); select name,salary*12 from employee where post='teacher' and name like 'jin%'; ?五 分組查詢:GROUP BY
一 什么是分組？為什么要分組？
#1、首先明確一點(diǎn)：分組發(fā)生在where之后，即分組是基于where之后得到的記錄而進(jìn)行的#2、分組指的是：將所有記錄按照某個(gè)相同字段進(jìn)行歸類，比如針對(duì)員工信息表的職位分組，或者按照性別進(jìn)行分組等#3、為何要分組呢？ 取每個(gè)部門的最高工資 取每個(gè)部門的員工數(shù) 取男人數(shù)和女人數(shù) 小竅門：‘每’這個(gè)字后面的字段，就是我們分組的依據(jù) #4、大前提： 可以按照任意字段分組，但是分組完畢后，比如group by post，只能查看post字段，如果想查看組內(nèi)信息，需要借助于聚合函數(shù)二 ONLY_FULL_GROUP_BY
#查看MySQL 5.7默認(rèn)的sql_mode如下： mysql> select @@global.sql_mode; ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION#！！！注意 ONLY_FULL_GROUP_BY的語義就是確定select target list中的所有列的值都是明確語義，簡(jiǎn)單的說來，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是來自于聚集函數(shù)的結(jié)果，要么是來自于group by list中的表達(dá)式的值。 #設(shè)置sql_mole如下操作(我們可以去掉ONLY_FULL_GROUP_BY模式)： mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; mysql> select @@global.sql_mode; +-------------------+ | @@global.sql_mode | +-------------------+ | | +-------------------+ 1 row in set (0.00 sec)mysql> select * from emp group by post; +----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+ | 14 | 張野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 1 | egon | male | 18 | 2017-03-01 | 老男孩駐沙河辦事處外交大使 | NULL | 7300.33 | 401 | 1 | +----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+ 4 rows in set (0.00 sec) #由于沒有設(shè)置ONLY_FULL_GROUP_BY,于是也可以有結(jié)果，默認(rèn)都是組內(nèi)的第一條記錄，但其實(shí)這是沒有意義的 mysql> set global sql_mode='ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec) mysql> quit #設(shè)置成功后，一定要退出，然后重新登錄方可生效 Bye mysql> use db1; Database changed mysql> select * from emp group by post; #報(bào)錯(cuò) ERROR 1055 (42000): 'db1.emp.id' isn't in GROUP BY mysql> select post,count(id) from emp group by post; #只能查看分組依據(jù)和使用聚合函數(shù) +----------------------------+-----------+ | post | count(id) | +----------------------------+-----------+ | operation | 5 | | sale | 5 | | teacher | 7 | | 老男孩駐沙河辦事處外交大使 | 1 | +----------------------------+-----------+ 4 rows in set (0.00 sec)三 GROUP BY
單獨(dú)使用GROUP BY關(guān)鍵字分組SELECT post FROM employee GROUP BY post;注意：我們按照post字段分組，那么select查詢的字段只能是post，想要獲取組內(nèi)的其他相關(guān)信息，需要借助函數(shù)GROUP BY關(guān)鍵字和GROUP_CONCAT()函數(shù)一起使用SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照崗位分組，并查看組內(nèi)成員名 SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;GROUP BY與聚合函數(shù)一起使用select post,count(id) as count from employee group by post;#按照崗位分組，并查看每個(gè)組有多少人強(qiáng)調(diào)：
如果我們用unique的字段作為分組的依據(jù)，則每一條記錄自成一組，這種分組沒有意義 多條記錄之間的某個(gè)字段值相同，該字段通常用來作為分組的依據(jù)四 聚合函數(shù)
#強(qiáng)調(diào)：聚合函數(shù)聚合的是組的內(nèi)容，若是沒有分組，則默認(rèn)一組 示例：SELECT COUNT(*) FROM employee;SELECT COUNT(*) FROM employee WHERE depart_id=1;SELECT MAX(salary) FROM employee;SELECT MIN(salary) FROM employee;SELECT AVG(salary) FROM employee;SELECT SUM(salary) FROM employee;SELECT SUM(salary) FROM employee WHERE depart_id=3;五 小練習(xí)：
1. 查詢崗位名以及崗位包含的所有員工名字 2. 查詢崗位名以及各崗位內(nèi)包含的員工個(gè)數(shù) 3. 查詢公司內(nèi)男員工和女員工的個(gè)數(shù) 4. 查詢崗位名以及各崗位的平均薪資 5. 查詢崗位名以及各崗位的最高薪資 6. 查詢崗位名以及各崗位的最低薪資 7.?查詢男員工與男員工的平均薪資，女員工與女員工的平均薪資 #題1：分組 mysql> select post,group_concat(name) from employee group by post; +-----------------------------------------+---------------------------------------------------------+ | post | group_concat(name) | +-----------------------------------------+---------------------------------------------------------+ | operation | 張野,程咬金,程咬銀,程咬銅,程咬鐵 | | sale | 歪歪,丫丫,丁丁,星星,格格 | | teacher | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龍 | | 老男孩駐沙河辦事處外交大使 | egon | +-----------------------------------------+---------------------------------------------------------+#題目2： mysql> select post,count(id) from employee group by post; +-----------------------------------------+-----------+ | post | count(id) | +-----------------------------------------+-----------+ | operation | 5 | | sale | 5 | | teacher | 7 | | 老男孩駐沙河辦事處外交大使 | 1 | +-----------------------------------------+-----------+ #題目3： mysql> select sex,count(id) from employee group by sex; +--------+-----------+ | sex | count(id) | +--------+-----------+ | male | 10 | | female | 8 | +--------+-----------+ #題目4： mysql> select post,avg(salary) from employee group by post; +-----------------------------------------+---------------+ | post | avg(salary) | +-----------------------------------------+---------------+ | operation | 16800.026000 | | sale | 2600.294000 | | teacher | 151842.901429 | | 老男孩駐沙河辦事處外交大使 | 7300.330000 | +-----------------------------------------+---------------+ #題目5 mysql> select post,max(salary) from employee group by post; +-----------------------------------------+-------------+ | post | max(salary) | +-----------------------------------------+-------------+ | operation | 20000.00 | | sale | 4000.33 | | teacher | 1000000.31 | | 老男孩駐沙河辦事處外交大使 | 7300.33 | +-----------------------------------------+-------------+ #題目6 mysql> select post,min(salary) from employee group by post; +-----------------------------------------+-------------+ | post | min(salary) | +-----------------------------------------+-------------+ | operation | 10000.13 | | sale | 1000.37 | | teacher | 2100.00 | | 老男孩駐沙河辦事處外交大使 | 7300.33 | +-----------------------------------------+-------------+ #題目七 mysql> select sex,avg(salary) from employee group by sex; +--------+---------------+ | sex | avg(salary) | +--------+---------------+ | male | 110920.077000 | | female | 7250.183750 | +--------+---------------+六 HAVING過濾
HAVING與WHERE不一樣的地方在于!!!!!!
#！！！執(zhí)行優(yōu)先級(jí)從高到低：where > group by > having #1. Where 發(fā)生在分組group by之前，因而Where中可以有任意字段，但是絕對(duì)不能使用聚合函數(shù)。#2. Having發(fā)生在分組group by之后，因而Having中可以使用分組的字段，無法直接取到其他字段,可以使用聚合函數(shù) mysql> select @@sql_mode; +--------------------+ | @@sql_mode | +--------------------+ | ONLY_FULL_GROUP_BY | +--------------------+ 1 row in set (0.00 sec)mysql> select * from emp where salary > 100000; +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ 1 row in set (0.00 sec) mysql> select * from emp having salary > 100000; ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clause mysql> select post,group_concat(name) from emp group by post having salary > 10000;#錯(cuò)誤，分組后無法直接取到salary字段 ERROR 1054 (42S22): Unknown column 'salary' in 'having clause' mysql> select post,group_concat(name) from emp group by post having avg(salary) > 10000; +-----------+-------------------------------------------------------+ | post | group_concat(name) | +-----------+-------------------------------------------------------+ | operation | 程咬鐵,程咬銅,程咬銀,程咬金,張野 | | teacher | 成龍,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex | +-----------+-------------------------------------------------------+ 2 rows in set (0.00 sec)小練習(xí)：
1. 查詢各崗位內(nèi)包含的員工個(gè)數(shù)小于2的崗位名、崗位內(nèi)包含員工名字、個(gè)數(shù) 3. 查詢各崗位平均薪資大于10000的崗位名、平均工資 4. 查詢各崗位平均薪資大于10000且小于20000的崗位名、平均工資 #題1： mysql> select post,group_concat(name),count(id) from employee group by post having count(id) < 2; +-----------------------------------------+--------------------+-----------+ | post | group_concat(name) | count(id) | +-----------------------------------------+--------------------+-----------+ | 老男孩駐沙河辦事處外交大使 | egon | 1 | +-----------------------------------------+--------------------+-----------+#題目2： mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | operation | 16800.026000 | | teacher | 151842.901429 | +-----------+---------------+ #題目3： mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000; +-----------+--------------+ | post | avg(salary) | +-----------+--------------+ | operation | 16800.026000 | +-----------+--------------+ ?七 查詢排序:ORDER BY
按單列排序SELECT * FROM employee ORDER BY salary;SELECT * FROM employee ORDER BY salary ASC;SELECT * FROM employee ORDER BY salary DESC;按多列排序:先按照age排序，如果年紀(jì)相同，則按照薪資排序SELECT * from employee ORDER BY age, salary DESC;小練習(xí)：
1. 查詢所有員工信息，先按照age升序排序，如果age相同則按照hire_date降序排序 2. 查詢各崗位平均薪資大于10000的崗位名、平均工資,結(jié)果按平均薪資升序排列 3. 查詢各崗位平均薪資大于10000的崗位名、平均工資,結(jié)果按平均薪資降序排列 #題目1 mysql> select * from employee ORDER BY age asc,hire_date desc;#題目2 mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | operation | 16800.026000 | | teacher | 151842.901429 | +-----------+---------------+ #題目3 mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | teacher | 151842.901429 | | operation | 16800.026000 | +-----------+---------------+八 限制查詢的記錄數(shù):LIMIT
示例：SELECT * FROM employee ORDER BY salary DESC LIMIT 3; #默認(rèn)初始位置為0 SELECT * FROM employee ORDER BY salary DESC LIMIT 0,5; #從第0開始，即先查詢出第一條，然后包含這一條在內(nèi)往后查5條 SELECT * FROM employee ORDER BY salary DESC LIMIT 5,5; #從第5開始，即先查詢出第6條，然后包含這一條在內(nèi)往后查5條小練習(xí)：
1. 分頁(yè)顯示，每頁(yè)5條 mysql> select * from employee limit 0,5; +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩駐沙河辦事處外交大使 | NULL | 7300.33 | 401 | 1 | | 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ 5 rows in set (0.00 sec)mysql> select * from employee limit 5,5; +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 8 | 成龍 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ 5 rows in set (0.00 sec) mysql> select * from employee limit 10,5; +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 14 | 張野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ 5 rows in set (0.00 sec) ?九 使用正則表達(dá)式查詢
SELECT * FROM employee WHERE name REGEXP '^ale';SELECT * FROM employee WHERE name REGEXP 'on$'; SELECT * FROM employee WHERE name REGEXP 'm{2}'; 小結(jié)：對(duì)字符串匹配的方式 WHERE name = 'egon'; WHERE name LIKE 'yua%'; WHERE name REGEXP 'on$';小練習(xí)：
查看所有員工中名字是jin開頭，n或者g結(jié)果的員工信息?
select * from employee where name regexp '^jin.*[gn]$';一 介紹
本節(jié)主題
- 多表連接查詢
- 復(fù)合條件連接查詢
- 子查詢
準(zhǔn)備表
#建表 create table department( id int, name varchar(20) );create table employee( 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 department values (200,'技術(shù)'), (201,'人力資源'), (202,'銷售'), (203,'運(yùn)營(yíng)'); insert into employee(name,sex,age,dep_id) values ('egon','male',18,200), ('alex','female',48,201), ('wupeiqi','male',38,201), ('yuanhao','female',28,202), ('liwenzhou','male',18,200), ('jingliyang','female',18,204) ; #查看表結(jié)構(gòu)和數(shù)據(jù) mysql> desc department; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ mysql> desc employee; +--------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | sex | enum('male','female') | NO | | male | | | age | int(11) | YES | | NULL | | | dep_id | int(11) | YES | | NULL | | +--------+-----------------------+------+-----+---------+----------------+ mysql> select * from department; +------+--------------+ | id | name | +------+--------------+ | 200 | 技術(shù) | | 201 | 人力資源 | | 202 | 銷售 | | 203 | 運(yùn)營(yíng) | +------+--------------+ mysql> select * from employee; +----+------------+--------+------+--------+ | id | name | sex | age | dep_id | +----+------------+--------+------+--------+ | 1 | egon | male | 18 | 200 | | 2 | alex | female | 48 | 201 | | 3 | wupeiqi | male | 38 | 201 | | 4 | yuanhao | female | 28 | 202 | | 5 | liwenzhou | male | 18 | 200 | | 6 | jingliyang | female | 18 | 204 | +----+------------+--------+------+--------+二 多表連接查詢
#重點(diǎn)：外鏈接語法 SELECT 字段列表FROM 表1 INNER|LEFT|RIGHT JOIN 表2ON 表1.字段 = 表2.字段;1 交叉連接：不適用任何匹配條件。生成笛卡爾積
mysql> select * from employee,department; +----+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+------------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技術(shù) | | 1 | egon | male | 18 | 200 | 201 | 人力資源 | | 1 | egon | male | 18 | 200 | 202 | 銷售 | | 1 | egon | male | 18 | 200 | 203 | 運(yùn)營(yíng) | | 2 | alex | female | 48 | 201 | 200 | 技術(shù) | | 2 | alex | female | 48 | 201 | 201 | 人力資源 | | 2 | alex | female | 48 | 201 | 202 | 銷售 | | 2 | alex | female | 48 | 201 | 203 | 運(yùn)營(yíng) | | 3 | wupeiqi | male | 38 | 201 | 200 | 技術(shù) | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力資源 | | 3 | wupeiqi | male | 38 | 201 | 202 | 銷售 | | 3 | wupeiqi | male | 38 | 201 | 203 | 運(yùn)營(yíng) | | 4 | yuanhao | female | 28 | 202 | 200 | 技術(shù) | | 4 | yuanhao | female | 28 | 202 | 201 | 人力資源 | | 4 | yuanhao | female | 28 | 202 | 202 | 銷售 | | 4 | yuanhao | female | 28 | 202 | 203 | 運(yùn)營(yíng) | | 5 | liwenzhou | male | 18 | 200 | 200 | 技術(shù) | | 5 | liwenzhou | male | 18 | 200 | 201 | 人力資源 | | 5 | liwenzhou | male | 18 | 200 | 202 | 銷售 | | 5 | liwenzhou | male | 18 | 200 | 203 | 運(yùn)營(yíng) | | 6 | jingliyang | female | 18 | 204 | 200 | 技術(shù) | | 6 | jingliyang | female | 18 | 204 | 201 | 人力資源 | | 6 | jingliyang | female | 18 | 204 | 202 | 銷售 | | 6 | jingliyang | female | 18 | 204 | 203 | 運(yùn)營(yíng) | +----+------------+--------+------+--------+------+--------------+2 內(nèi)連接：只連接匹配的行
#找兩張表共有的部分，相當(dāng)于利用條件從笛卡爾積結(jié)果中篩選出了正確的結(jié)果 #department沒有204這個(gè)部門，因而employee表中關(guān)于204這條員工信息沒有匹配出來 mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id; +----+-----------+------+--------+--------------+ | id | name | age | sex | name | +----+-----------+------+--------+--------------+ | 1 | egon | 18 | male | 技術(shù) | | 2 | alex | 48 | female | 人力資源 | | 3 | wupeiqi | 38 | male | 人力資源 | | 4 | yuanhao | 28 | female | 銷售 | | 5 | liwenzhou | 18 | male | 技術(shù) | +----+-----------+------+--------+--------------+ #上述sql等同于 mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;3 外鏈接之左連接：優(yōu)先顯示左表全部記錄
#以左表為準(zhǔn)，即找出所有員工信息，當(dāng)然包括沒有部門的員工 #本質(zhì)就是：在內(nèi)連接的基礎(chǔ)上增加左邊有右邊沒有的結(jié)果 mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id; +----+------------+--------------+ | id | name | depart_name | +----+------------+--------------+ | 1 | egon | 技術(shù) | | 5 | liwenzhou | 技術(shù) | | 2 | alex | 人力資源 | | 3 | wupeiqi | 人力資源 | | 4 | yuanhao | 銷售 | | 6 | jingliyang | NULL | +----+------------+--------------+4 外鏈接之右連接：優(yōu)先顯示右表全部記錄
#以右表為準(zhǔn)，即找出所有部門信息，包括沒有員工的部門 #本質(zhì)就是：在內(nèi)連接的基礎(chǔ)上增加右邊有左邊沒有的結(jié)果 mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id; +------+-----------+--------------+ | id | name | depart_name | +------+-----------+--------------+ | 1 | egon | 技術(shù) | | 2 | alex | 人力資源 | | 3 | wupeiqi | 人力資源 | | 4 | yuanhao | 銷售 | | 5 | liwenzhou | 技術(shù) | | NULL | NULL | 運(yùn)營(yíng) | +------+-----------+--------------+5 全外連接：顯示左右兩個(gè)表全部記錄
全外連接：在內(nèi)連接的基礎(chǔ)上增加左邊有右邊沒有的和右邊有左邊沒有的結(jié)果 #注意：mysql不支持全外連接 full JOIN #強(qiáng)調(diào)：mysql可以使用此種方式間接實(shí)現(xiàn)全外連接 select * from employee left join department on employee.dep_id = department.id union select * from employee right join department on employee.dep_id = department.id ; #查看結(jié)果 +------+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+------------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技術(shù) | | 5 | liwenzhou | male | 18 | 200 | 200 | 技術(shù) | | 2 | alex | female | 48 | 201 | 201 | 人力資源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力資源 | | 4 | yuanhao | female | 28 | 202 | 202 | 銷售 | | 6 | jingliyang | female | 18 | 204 | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | 203 | 運(yùn)營(yíng) | +------+------------+--------+------+--------+------+--------------+ #注意 union與union all的區(qū)別：union會(huì)去掉相同的紀(jì)錄三 符合條件連接查詢
#示例1：以內(nèi)連接的方式查詢employee和department表，并且employee表中的age字段值必須大于25,即找出年齡大于25歲的員工以及員工所在的部門 select employee.name,department.name from employee inner join departmenton employee.dep_id = department.idwhere age > 25; #示例2：以內(nèi)連接的方式查詢employee和department表，并且以age字段的升序方式顯示 select employee.id,employee.name,employee.age,department.name from employee,department where employee.dep_id = department.id and age > 25 order by age asc;四 子查詢
#1：子查詢是將一個(gè)查詢語句嵌套在另一個(gè)查詢語句中。 #2：內(nèi)層查詢語句的查詢結(jié)果，可以為外層查詢語句提供查詢條件。 #3：子查詢中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等關(guān)鍵字 #4：還可以包含比較運(yùn)算符：= 、 !=、> 、<等1 帶IN關(guān)鍵字的子查詢
#查詢平均年齡在25歲以上的部門名 select id,name from departmentwhere id in (select dep_id from employee group by dep_id having avg(age) > 25); #查看技術(shù)部員工姓名 select name from employee where dep_id in (select id from department where name='技術(shù)'); #查看不足1人的部門名 select name from department where id in (select dep_id from employee group by dep_id having count(id) <=1);2 帶比較運(yùn)算符的子查詢
#比較運(yùn)算符：=、!=、>、>=、<、<=、<> #查詢大于所有人平均年齡的員工名與年齡 mysql> select name,age from emp where age > (select avg(age) from emp); +---------+------+ | name | age | +---------+------+ | alex | 48 | | wupeiqi | 38 | +---------+------+ 2 rows in set (0.00 sec) #查詢大于部門內(nèi)平均年齡的員工名、年齡 select t1.name,t1.age from emp t1 inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;?3 帶EXISTS關(guān)鍵字的子查詢
EXISTS關(guān)字鍵字表示存在。在使用EXISTS關(guān)鍵字時(shí)，內(nèi)層查詢語句不返回查詢的記錄。
而是返回一個(gè)真假值。True或False
當(dāng)返回True時(shí)，外層查詢語句將進(jìn)行查詢；當(dāng)返回值為False時(shí)，外層查詢語句不進(jìn)行查詢
?
練習(xí)：查詢每個(gè)部門最新入職的那位員工
company.employee員工id id int 姓名 emp_name varchar性別 sex enum年齡 age int入職日期 hire_date date崗位 post varchar職位描述 post_comment varchar薪水 salary double辦公室 office int部門編號(hào) depart_id int#創(chuàng)建表 create table employee( 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 ); #查看表結(jié)構(gòu) mysql> desc employee; +--------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | sex | enum('male','female') | NO | | male | | | age | int(3) unsigned | NO | | 28 | | | hire_date | date | NO | | NULL | | | post | varchar(50) | YES | | NULL | | | post_comment | varchar(100) | YES | | NULL | | | salary | double(15,2) | YES | | NULL | | | office | int(11) | YES | | NULL | | | depart_id | int(11) | YES | | NULL | | +--------------+-----------------------+------+-----+---------+----------------+ #插入記錄 #三個(gè)部門：教學(xué)，銷售，運(yùn)營(yíng) insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values ('egon','male',18,'20170301','老男孩駐沙河辦事處外交大使',7300.33,401,1), #以下是教學(xué)部 ('alex','male',78,'20150302','teacher',1000000.31,401,1), ('wupeiqi','male',81,'20130305','teacher',8300,401,1), ('yuanhao','male',73,'20140701','teacher',3500,401,1), ('liwenzhou','male',28,'20121101','teacher',2100,401,1), ('jingliyang','female',18,'20110211','teacher',9000,401,1), ('jinxin','male',18,'19000301','teacher',30000,401,1), ('成龍','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)營(yíng)部門 ('程咬金','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* FROMemp AS t1 INNER JOIN (SELECTpost,max(hire_date) max_dateFROMempGROUP BYpost ) AS t2 ON t1.post = t2.post WHEREt1.hire_date = t2.max_date; mysql> select (select t2.name from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) from emp as t1 group by post; +---------------------------------------------------------------------------------------+ | (select t2.name from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) | +---------------------------------------------------------------------------------------+ | 張野 | | 格格 | | alex | | egon | +---------------------------------------------------------------------------------------+ rows in set (0.00 sec) mysql> select (select t2.id from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) from emp as t1 group by post; +-------------------------------------------------------------------------------------+ | (select t2.id from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) | +-------------------------------------------------------------------------------------+ | 14 | | 13 | | 2 | | 1 | +-------------------------------------------------------------------------------------+ rows in set (0.00 sec) #正確答案 mysql> select t3.name,t3.post,t3.hire_date from emp as t3 where id in (select (select id from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) from emp as t1 group by post); +--------+-----------------------------------------+------------+ | name | post | hire_date | +--------+-----------------------------------------+------------+ | egon | 老男孩駐沙河辦事處外交大使 | 2017-03-01 | | alex | teacher | 2015-03-02 | | 格格 | sale | 2017-01-27 | | 張野 | operation | 2016-03-11 | +--------+-----------------------------------------+------------+ rows in set (0.00 sec)答案一為正確答案，答案二中的limit 1有問題（每個(gè)部門可能有>1個(gè)為同一時(shí)間入職的新員工），我只是想用該例子來說明可以在select后使用子查詢
可以基于上述方法解決：比如某網(wǎng)站在全國(guó)各個(gè)市都有站點(diǎn)，每個(gè)站點(diǎn)一條數(shù)據(jù)，想取每個(gè)省下最新的那一條市的網(wǎng)站質(zhì)量信息
五 綜合練習(xí)
init.sql文件內(nèi)容
/*數(shù)據(jù)導(dǎo)入：Navicat Premium Data TransferSource Server : localhostSource Server Type : MySQLSource Server Version : 50624Source Host : localhostSource Database : sqlexamTarget Server Type : MySQLTarget Server Version : 50624File Encoding : utf-8Date: 10/21/2016 06:46:46 AM */ SET NAMES utf8; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for `class` -- ---------------------------- DROP TABLE IF EXISTS `class`; CREATE TABLE `class` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `caption` varchar(32) NOT NULL, PRIMARY KEY (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `class` -- ---------------------------- BEGIN; INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班'); COMMIT; -- ---------------------------- -- Table structure for `course` -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `cname` varchar(32) NOT NULL, `teacher_id` int(11) NOT NULL, PRIMARY KEY (`cid`), KEY `fk_course_teacher` (`teacher_id`), CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `course` -- ---------------------------- BEGIN; INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '體育', '3'), ('4', '美術(shù)', '2'); COMMIT; -- ---------------------------- -- Table structure for `score` -- ---------------------------- DROP TABLE IF EXISTS `score`; CREATE TABLE `score` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `student_id` int(11) NOT NULL, `course_id` int(11) NOT NULL, `num` int(11) NOT NULL, PRIMARY KEY (`sid`), KEY `fk_score_student` (`student_id`), KEY `fk_score_course` (`course_id`), CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`), CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `score` -- ---------------------------- BEGIN; INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87'); COMMIT; -- ---------------------------- -- Table structure for `student` -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `gender` char(1) NOT NULL, `class_id` int(11) NOT NULL, `sname` varchar(32) NOT NULL, PRIMARY KEY (`sid`), KEY `fk_class` (`class_id`), CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `student` -- ---------------------------- BEGIN; INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '鋼蛋'), ('3', '男', '1', '張三'), ('4', '男', '1', '張一'), ('5', '女', '1', '張二'), ('6', '男', '1', '張四'), ('7', '女', '2', '鐵錘'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '劉三'), ('14', '男', '3', '劉一'), ('15', '女', '3', '劉二'), ('16', '男', '3', '劉四'); COMMIT; -- ---------------------------- -- Table structure for `teacher` -- ---------------------------- DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` ( `tid` int(11) NOT NULL AUTO_INCREMENT, `tname` varchar(32) NOT NULL, PRIMARY KEY (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `teacher` -- ---------------------------- BEGIN; INSERT INTO `teacher` VALUES ('1', '張磊老師'), ('2', '李平老師'), ('3', '劉海燕老師'), ('4', '朱云海老師'), ('5', '李杰老師'); COMMIT; SET FOREIGN_KEY_CHECKS = 1;從init.sql文件中導(dǎo)入數(shù)據(jù)
#準(zhǔn)備表、記錄 mysql> create database db1; mysql> use db1; mysql> source /root/init.sql?
！！！重中之重：練習(xí)之前務(wù)必搞清楚sql邏輯查詢語句的執(zhí)行順序
鏈接：http://www.cnblogs.com/panfb/p/8043563.html
1、查詢所有的課程的名稱以及對(duì)應(yīng)的任課老師姓名2、查詢學(xué)生表中男女生各有多少人3、查詢物理成績(jī)等于100的學(xué)生的姓名4、查詢平均成績(jī)大于八十分的同學(xué)的姓名和平均成績(jī)5、查詢所有學(xué)生的學(xué)號(hào)，姓名，選課數(shù)，總成績(jī)6、 查詢姓李老師的個(gè)數(shù) 7、 查詢沒有報(bào)李平老師課的學(xué)生姓名 8、 查詢物理課程比生物課程高的學(xué)生的學(xué)號(hào) 9、 查詢沒有同時(shí)選修物理課程和體育課程的學(xué)生姓名 10、查詢掛科超過兩門(包括兩門)的學(xué)生姓名和班級(jí) 11 、查詢選修了所有課程的學(xué)生姓名 12、查詢李平老師教的課程的所有成績(jī)記錄 13、查詢?nèi)繉W(xué)生都選修了的課程號(hào)和課程名 14、查詢每門課程被選修的次數(shù) 15、查詢之選修了一門課程的學(xué)生姓名和學(xué)號(hào) 16、查詢所有學(xué)生考出的成績(jī)并按從高到低排序（成績(jī)?nèi)ブ?#xff09; 17、查詢平均成績(jī)大于85的學(xué)生姓名和平均成績(jī) 18、查詢生物成績(jī)不及格的學(xué)生姓名和對(duì)應(yīng)生物分?jǐn)?shù) 19、查詢?cè)谒羞x修了李平老師課程的學(xué)生中，這些課程(李平老師的課程，不是所有課程)平均成績(jī)最高的學(xué)生姓名 20、查詢每門課程成績(jī)最好的前兩名學(xué)生姓名 21、查詢不同課程但成績(jī)相同的學(xué)號(hào)，課程號(hào)，成績(jī) 22、查詢沒學(xué)過“葉平”老師課程的學(xué)生姓名以及選修的課程名稱； 23、查詢所有選修了學(xué)號(hào)為1的同學(xué)選修過的一門或者多門課程的同學(xué)學(xué)號(hào)和姓名； 24、任課最多的老師中學(xué)生單科成績(jī)最高的學(xué)生姓名參考答案:http://www.cnblogs.com/panfb/p/8043651.html
?
一 介紹
本節(jié)主題
- 多表連接查詢
- 復(fù)合條件連接查詢
- 子查詢
準(zhǔn)備表
#建表 create table department( id int, name varchar(20) );create table employee( 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 department values (200,'技術(shù)'), (201,'人力資源'), (202,'銷售'), (203,'運(yùn)營(yíng)');insert into employee(name,sex,age,dep_id) values ('egon','male',18,200), ('alex','female',48,201), ('wupeiqi','male',38,201), ('yuanhao','female',28,202), ('liwenzhou','male',18,200), ('jingliyang','female',18,204) ;#查看表結(jié)構(gòu)和數(shù)據(jù) mysql> desc department; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+mysql> desc employee; +--------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | sex | enum('male','female') | NO | | male | | | age | int(11) | YES | | NULL | | | dep_id | int(11) | YES | | NULL | | +--------+-----------------------+------+-----+---------+----------------+mysql> select * from department; +------+--------------+ | id | name | +------+--------------+ | 200 | 技術(shù) | | 201 | 人力資源 | | 202 | 銷售 | | 203 | 運(yùn)營(yíng) | +------+--------------+mysql> select * from employee; +----+------------+--------+------+--------+ | id | name | sex | age | dep_id | +----+------------+--------+------+--------+ | 1 | egon | male | 18 | 200 | | 2 | alex | female | 48 | 201 | | 3 | wupeiqi | male | 38 | 201 | | 4 | yuanhao | female | 28 | 202 | | 5 | liwenzhou | male | 18 | 200 | | 6 | jingliyang | female | 18 | 204 | +----+------------+--------+------+--------+二 多表連接查詢
#重點(diǎn)：外鏈接語法 SELECT 字段列表FROM 表1 INNER|LEFT|RIGHT JOIN 表2ON 表1.字段 = 表2.字段;1 交叉連接：不適用任何匹配條件。生成笛卡爾積
mysql> select * from employee,department; +----+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+------------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技術(shù) | | 1 | egon | male | 18 | 200 | 201 | 人力資源 | | 1 | egon | male | 18 | 200 | 202 | 銷售 | | 1 | egon | male | 18 | 200 | 203 | 運(yùn)營(yíng) | | 2 | alex | female | 48 | 201 | 200 | 技術(shù) | | 2 | alex | female | 48 | 201 | 201 | 人力資源 | | 2 | alex | female | 48 | 201 | 202 | 銷售 | | 2 | alex | female | 48 | 201 | 203 | 運(yùn)營(yíng) | | 3 | wupeiqi | male | 38 | 201 | 200 | 技術(shù) | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力資源 | | 3 | wupeiqi | male | 38 | 201 | 202 | 銷售 | | 3 | wupeiqi | male | 38 | 201 | 203 | 運(yùn)營(yíng) | | 4 | yuanhao | female | 28 | 202 | 200 | 技術(shù) | | 4 | yuanhao | female | 28 | 202 | 201 | 人力資源 | | 4 | yuanhao | female | 28 | 202 | 202 | 銷售 | | 4 | yuanhao | female | 28 | 202 | 203 | 運(yùn)營(yíng) | | 5 | liwenzhou | male | 18 | 200 | 200 | 技術(shù) | | 5 | liwenzhou | male | 18 | 200 | 201 | 人力資源 | | 5 | liwenzhou | male | 18 | 200 | 202 | 銷售 | | 5 | liwenzhou | male | 18 | 200 | 203 | 運(yùn)營(yíng) | | 6 | jingliyang | female | 18 | 204 | 200 | 技術(shù) | | 6 | jingliyang | female | 18 | 204 | 201 | 人力資源 | | 6 | jingliyang | female | 18 | 204 | 202 | 銷售 | | 6 | jingliyang | female | 18 | 204 | 203 | 運(yùn)營(yíng) | +----+------------+--------+------+--------+------+--------------+2 內(nèi)連接：只連接匹配的行
#找兩張表共有的部分，相當(dāng)于利用條件從笛卡爾積結(jié)果中篩選出了正確的結(jié)果 #department沒有204這個(gè)部門，因而employee表中關(guān)于204這條員工信息沒有匹配出來 mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id; +----+-----------+------+--------+--------------+ | id | name | age | sex | name | +----+-----------+------+--------+--------------+ | 1 | egon | 18 | male | 技術(shù) | | 2 | alex | 48 | female | 人力資源 | | 3 | wupeiqi | 38 | male | 人力資源 | | 4 | yuanhao | 28 | female | 銷售 | | 5 | liwenzhou | 18 | male | 技術(shù) | +----+-----------+------+--------+--------------+#上述sql等同于 mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;3 外鏈接之左連接：優(yōu)先顯示左表全部記錄
#以左表為準(zhǔn)，即找出所有員工信息，當(dāng)然包括沒有部門的員工 #本質(zhì)就是：在內(nèi)連接的基礎(chǔ)上增加左邊有右邊沒有的結(jié)果 mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id; +----+------------+--------------+ | id | name | depart_name | +----+------------+--------------+ | 1 | egon | 技術(shù) | | 5 | liwenzhou | 技術(shù) | | 2 | alex | 人力資源 | | 3 | wupeiqi | 人力資源 | | 4 | yuanhao | 銷售 | | 6 | jingliyang | NULL | +----+------------+--------------+4 外鏈接之右連接：優(yōu)先顯示右表全部記錄
#以右表為準(zhǔn)，即找出所有部門信息，包括沒有員工的部門 #本質(zhì)就是：在內(nèi)連接的基礎(chǔ)上增加右邊有左邊沒有的結(jié)果 mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id; +------+-----------+--------------+ | id | name | depart_name | +------+-----------+--------------+ | 1 | egon | 技術(shù) | | 2 | alex | 人力資源 | | 3 | wupeiqi | 人力資源 | | 4 | yuanhao | 銷售 | | 5 | liwenzhou | 技術(shù) | | NULL | NULL | 運(yùn)營(yíng) | +------+-----------+--------------+5 全外連接：顯示左右兩個(gè)表全部記錄
全外連接：在內(nèi)連接的基礎(chǔ)上增加左邊有右邊沒有的和右邊有左邊沒有的結(jié)果 #注意：mysql不支持全外連接 full JOIN #強(qiáng)調(diào)：mysql可以使用此種方式間接實(shí)現(xiàn)全外連接 select * from employee left join department on employee.dep_id = department.id union select * from employee right join department on employee.dep_id = department.id ; #查看結(jié)果 +------+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+------------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技術(shù) | | 5 | liwenzhou | male | 18 | 200 | 200 | 技術(shù) | | 2 | alex | female | 48 | 201 | 201 | 人力資源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力資源 | | 4 | yuanhao | female | 28 | 202 | 202 | 銷售 | | 6 | jingliyang | female | 18 | 204 | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | 203 | 運(yùn)營(yíng) | +------+------------+--------+------+--------+------+--------------+#注意 union與union all的區(qū)別：union會(huì)去掉相同的紀(jì)錄三 符合條件連接查詢
#示例1：以內(nèi)連接的方式查詢employee和department表，并且employee表中的age字段值必須大于25,即找出年齡大于25歲的員工以及員工所在的部門 select employee.name,department.name from employee inner join departmenton employee.dep_id = department.idwhere age > 25;#示例2：以內(nèi)連接的方式查詢employee和department表，并且以age字段的升序方式顯示 select employee.id,employee.name,employee.age,department.name from employee,departmentwhere employee.dep_id = department.idand age > 25order by age asc;四 子查詢
#1：子查詢是將一個(gè)查詢語句嵌套在另一個(gè)查詢語句中。 #2：內(nèi)層查詢語句的查詢結(jié)果，可以為外層查詢語句提供查詢條件。 #3：子查詢中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等關(guān)鍵字 #4：還可以包含比較運(yùn)算符：= 、 !=、> 、<等1 帶IN關(guān)鍵字的子查詢
#查詢平均年齡在25歲以上的部門名 select id,name from departmentwhere id in (select dep_id from employee group by dep_id having avg(age) > 25);#查看技術(shù)部員工姓名 select name from employeewhere dep_id in (select id from department where name='技術(shù)');#查看不足1人的部門名 select name from departmentwhere id in (select dep_id from employee group by dep_id having count(id) <=1);2 帶比較運(yùn)算符的子查詢
#比較運(yùn)算符：=、!=、>、>=、<、<=、<> #查詢大于所有人平均年齡的員工名與年齡 mysql> select name,age from emp where age > (select avg(age) from emp); +---------+------+ | name | age | +---------+------+ | alex | 48 | | wupeiqi | 38 | +---------+------+ 2 rows in set (0.00 sec)#查詢大于部門內(nèi)平均年齡的員工名、年齡 select t1.name,t1.age from emp t1 inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;?3 帶EXISTS關(guān)鍵字的子查詢
EXISTS關(guān)字鍵字表示存在。在使用EXISTS關(guān)鍵字時(shí)，內(nèi)層查詢語句不返回查詢的記錄。
而是返回一個(gè)真假值。True或False
當(dāng)返回True時(shí)，外層查詢語句將進(jìn)行查詢；當(dāng)返回值為False時(shí)，外層查詢語句不進(jìn)行查詢
?
練習(xí)：查詢每個(gè)部門最新入職的那位員工
company.employee員工id id int 姓名 emp_name varchar性別 sex enum年齡 age int入職日期 hire_date date崗位 post varchar職位描述 post_comment varchar薪水 salary double辦公室 office int部門編號(hào) depart_id int#創(chuàng)建表 create table employee( 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 );#查看表結(jié)構(gòu) mysql> desc employee; +--------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | sex | enum('male','female') | NO | | male | | | age | int(3) unsigned | NO | | 28 | | | hire_date | date | NO | | NULL | | | post | varchar(50) | YES | | NULL | | | post_comment | varchar(100) | YES | | NULL | | | salary | double(15,2) | YES | | NULL | | | office | int(11) | YES | | NULL | | | depart_id | int(11) | YES | | NULL | | +--------------+-----------------------+------+-----+---------+----------------+#插入記錄 #三個(gè)部門：教學(xué)，銷售，運(yùn)營(yíng) insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values ('egon','male',18,'20170301','老男孩駐沙河辦事處外交大使',7300.33,401,1), #以下是教學(xué)部 ('alex','male',78,'20150302','teacher',1000000.31,401,1), ('wupeiqi','male',81,'20130305','teacher',8300,401,1), ('yuanhao','male',73,'20140701','teacher',3500,401,1), ('liwenzhou','male',28,'20121101','teacher',2100,401,1), ('jingliyang','female',18,'20110211','teacher',9000,401,1), ('jinxin','male',18,'19000301','teacher',30000,401,1), ('成龍','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)營(yíng)部門 ('程咬金','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* FROMemp AS t1 INNER JOIN (SELECTpost,max(hire_date) max_dateFROMempGROUP BYpost ) AS t2 ON t1.post = t2.post WHEREt1.hire_date = t2.max_date; mysql> select (select t2.name from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) from emp as t1 group by post; +---------------------------------------------------------------------------------------+ | (select t2.name from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) | +---------------------------------------------------------------------------------------+ | 張野 | | 格格 | | alex | | egon | +---------------------------------------------------------------------------------------+ rows in set (0.00 sec)mysql> select (select t2.id from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) from emp as t1 group by post; +-------------------------------------------------------------------------------------+ | (select t2.id from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) | +-------------------------------------------------------------------------------------+ | 14 | | 13 | | 2 | | 1 | +-------------------------------------------------------------------------------------+ rows in set (0.00 sec)#正確答案 mysql> select t3.name,t3.post,t3.hire_date from emp as t3 where id in (select (select id from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) from emp as t1 group by post); +--------+-----------------------------------------+------------+ | name | post | hire_date | +--------+-----------------------------------------+------------+ | egon | 老男孩駐沙河辦事處外交大使 | 2017-03-01 | | alex | teacher | 2015-03-02 | | 格格 | sale | 2017-01-27 | | 張野 | operation | 2016-03-11 | +--------+-----------------------------------------+------------+ rows in set (0.00 sec)答案一為正確答案，答案二中的limit 1有問題（每個(gè)部門可能有>1個(gè)為同一時(shí)間入職的新員工），我只是想用該例子來說明可以在select后使用子查詢
可以基于上述方法解決：比如某網(wǎng)站在全國(guó)各個(gè)市都有站點(diǎn)，每個(gè)站點(diǎn)一條數(shù)據(jù)，想取每個(gè)省下最新的那一條市的網(wǎng)站質(zhì)量信息
五 綜合練習(xí)
init.sql文件內(nèi)容
/*數(shù)據(jù)導(dǎo)入：Navicat Premium Data TransferSource Server : localhostSource Server Type : MySQLSource Server Version : 50624Source Host : localhostSource Database : sqlexamTarget Server Type : MySQLTarget Server Version : 50624File Encoding : utf-8Date: 10/21/2016 06:46:46 AM */SET NAMES utf8; SET FOREIGN_KEY_CHECKS = 0;-- ---------------------------- -- Table structure for `class` -- ---------------------------- DROP TABLE IF EXISTS `class`; CREATE TABLE `class` (`cid` int(11) NOT NULL AUTO_INCREMENT,`caption` varchar(32) NOT NULL,PRIMARY KEY (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ---------------------------- -- Records of `class` -- ---------------------------- BEGIN; INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班'); COMMIT;-- ---------------------------- -- Table structure for `course` -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` (`cid` int(11) NOT NULL AUTO_INCREMENT,`cname` varchar(32) NOT NULL,`teacher_id` int(11) NOT NULL,PRIMARY KEY (`cid`),KEY `fk_course_teacher` (`teacher_id`),CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ---------------------------- -- Records of `course` -- ---------------------------- BEGIN; INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '體育', '3'), ('4', '美術(shù)', '2'); COMMIT;-- ---------------------------- -- Table structure for `score` -- ---------------------------- DROP TABLE IF EXISTS `score`; CREATE TABLE `score` (`sid` int(11) NOT NULL AUTO_INCREMENT,`student_id` int(11) NOT NULL,`course_id` int(11) NOT NULL,`num` int(11) NOT NULL,PRIMARY KEY (`sid`),KEY `fk_score_student` (`student_id`),KEY `fk_score_course` (`course_id`),CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;-- ---------------------------- -- Records of `score` -- ---------------------------- BEGIN; INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87'); COMMIT;-- ---------------------------- -- Table structure for `student` -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` (`sid` int(11) NOT NULL AUTO_INCREMENT,`gender` char(1) NOT NULL,`class_id` int(11) NOT NULL,`sname` varchar(32) NOT NULL,PRIMARY KEY (`sid`),KEY `fk_class` (`class_id`),CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;-- ---------------------------- -- Records of `student` -- ---------------------------- BEGIN; INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '鋼蛋'), ('3', '男', '1', '張三'), ('4', '男', '1', '張一'), ('5', '女', '1', '張二'), ('6', '男', '1', '張四'), ('7', '女', '2', '鐵錘'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '劉三'), ('14', '男', '3', '劉一'), ('15', '女', '3', '劉二'), ('16', '男', '3', '劉四'); COMMIT;-- ---------------------------- -- Table structure for `teacher` -- ---------------------------- DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` (`tid` int(11) NOT NULL AUTO_INCREMENT,`tname` varchar(32) NOT NULL,PRIMARY KEY (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-- ---------------------------- -- Records of `teacher` -- ---------------------------- BEGIN; INSERT INTO `teacher` VALUES ('1', '張磊老師'), ('2', '李平老師'), ('3', '劉海燕老師'), ('4', '朱云海老師'), ('5', '李杰老師'); COMMIT;SET FOREIGN_KEY_CHECKS = 1;從init.sql文件中導(dǎo)入數(shù)據(jù)
#準(zhǔn)備表、記錄 mysql> create database db1; mysql> use db1; mysql> source /root/init.sql?
！！！重中之重：練習(xí)之前務(wù)必搞清楚sql邏輯查詢語句的執(zhí)行順序
鏈接：http://www.cnblogs.com/linhaifeng/articles/7372774.html
1、查詢所有的課程的名稱以及對(duì)應(yīng)的任課老師姓名2、查詢學(xué)生表中男女生各有多少人3、查詢物理成績(jī)等于100的學(xué)生的姓名4、查詢平均成績(jī)大于八十分的同學(xué)的姓名和平均成績(jī)5、查詢所有學(xué)生的學(xué)號(hào)，姓名，選課數(shù)，總成績(jī)6、 查詢姓李老師的個(gè)數(shù)7、 查詢沒有報(bào)李平老師課的學(xué)生姓名8、 查詢物理課程比生物課程高的學(xué)生的學(xué)號(hào)9、 查詢沒有同時(shí)選修物理課程和體育課程的學(xué)生姓名10、查詢掛科超過兩門(包括兩門)的學(xué)生姓名和班級(jí)11 、查詢選修了所有課程的學(xué)生姓名12、查詢李平老師教的課程的所有成績(jī)記錄13、查詢?nèi)繉W(xué)生都選修了的課程號(hào)和課程名14、查詢每門課程被選修的次數(shù)15、查詢之選修了一門課程的學(xué)生姓名和學(xué)號(hào)16、查詢所有學(xué)生考出的成績(jī)并按從高到低排序（成績(jī)?nèi)ブ?#xff09;17、查詢平均成績(jī)大于85的學(xué)生姓名和平均成績(jī)18、查詢生物成績(jī)不及格的學(xué)生姓名和對(duì)應(yīng)生物分?jǐn)?shù)19、查詢?cè)谒羞x修了李平老師課程的學(xué)生中，這些課程(李平老師的課程，不是所有課程)平均成績(jī)最高的學(xué)生姓名20、查詢每門課程成績(jī)最好的前兩名學(xué)生姓名21、查詢不同課程但成績(jī)相同的學(xué)號(hào)，課程號(hào)，成績(jī)22、查詢沒學(xué)過“葉平”老師課程的學(xué)生姓名以及選修的課程名稱；23、查詢所有選修了學(xué)號(hào)為1的同學(xué)選修過的一門或者多門課程的同學(xué)學(xué)號(hào)和姓名；24、任課最多的老師中學(xué)生單科成績(jī)最高的學(xué)生姓名參考答案:http://www.cnblogs.com/linhaifeng/articles/7895711.html
轉(zhuǎn)載于:https://www.cnblogs.com/panfb/p/8043540.html
總結(jié)
以上是生活随笔為你收集整理的mysql 关联关系的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 《软件过程改进》练习题
- 下一篇： 从nginx日志原始二进制数据还原文件