本節重點： 單表查詢 語法： 1.找到表:from 2.拿著where指定的約束條件，去文件/表中取出一條條記錄 3.將取出的一條條記錄進行分組group by，如果沒有group by，則整體作為一組 4.將分組的結果進行having過濾 5.執行select 6.去重 7.將結果按條件排序：or ...
本節重點：
單表查詢
語法：
一、單表查詢的語法
SELECT 欄位1,欄位2... FROM 表名
WHERE 條件
GROUP BY field
HAVING 篩選
ORDER BY field
LIMIT 限制條數
二、關鍵字的執行優先順序（重點）
重點中的重點：關鍵字的執行優先順序
from
where
group by
having
select
distinct
order by
limit
1.找到表:from
2.拿著where指定的約束條件，去文件/表中取出一條條記錄
3.將取出的一條條記錄進行分組group by，如果沒有group by，則整體作為一組
4.將分組的結果進行having過濾
5.執行select
6.去重
7.將結果按條件排序：order by
8.限制結果的顯示條數
創建公司員工表，表的欄位和數據類型
company.employee
員工id id int
姓名 name varchar
性別 sex enum
年齡 age int
入職日期 hire_date date
崗位 post varchar
職位描述 post_comment varchar
薪水 salary double
辦公室 office int
部門編號 depart_id int
#創建表,設置欄位的約束條件
create table employee(
id int primary key auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int,#一個部門一個屋
depart_id int
);
# 查看表結構
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| emp_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 | |
| salart | double(15,2) | YES | | NULL | |
| office | int(11) | YES | | NULL | |
| depart_id | int(11) | YES | | NULL | |
+--------------+-----------------------+------+-----+---------+----------------+
10 rows in set (0.08 sec)
#插入記錄
#三個部門：教學，銷售，運營
insert into employee(name ,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩駐沙河辦事處外交大使',7300.33,401,1), #以下是教學部
('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),
('xiaomage','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)
;
創建員工表，並插入記錄
（1）where 約束
where子句中可以使用 1.比較運算符：>、<、>=、<=、<>、!= 2.between 80 and 100 ：值在80到100之間 3.in(80,90,100)值是10或20或30 4.like 'xiaomagepattern': pattern可以是%或者_。%小時任意多字元，_表示一個字元 5.邏輯運算符：在多個條件直接可以使用邏輯運算符 and or not
驗證結果：
#1 :單條件查詢
mysql> select id,emp_name from employee where id > 5;
+----+------------+
| id | emp_name |
+----+------------+
| 6 | jingliyang |
| 7 | jinxin |
| 8 | xiaomage |
| 9 | 歪歪 |
| 10 | 丫丫 |
| 11 | 丁丁 |
| 12 | 星星 |
| 13 | 格格 |
| 14 | 張野 |
| 15 | 程咬金 |
| 16 | 程咬銀 |
| 17 | 程咬銅 |
| 18 | 程咬鐵 |
#2 多條件查詢
mysql> select emp_name from employee where post='teacher' and salary>10000;
+----------+
| emp_name |
+----------+
| alex |
| jinxin |
+----------+
#3.關鍵字BETWEEN AND
SELECT name,salary FROM employee
WHERE salary BETWEEN 10000 AND 20000;
SELECT name,salary FROM employee
WHERE salary NOT BETWEEN 10000 AND 20000;
#註意''是空字元串，不是null
SELECT name,post_comment FROM employee WHERE post_comment='';
ps：
執行
update employee set post_comment='' where id=2;
再用上條查看，就會有結果了
#5:關鍵字IN集合查詢
mysql> SELECT name,salary FROM employee WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
+------------+---------+
| name | salary |
+------------+---------+
| yuanhao | 3500.00 |
| jingliyang | 9000.00 |
+------------+---------+
2 rows in set (0.00 sec)
mysql> SELECT name,salary FROM employee WHERE salary IN (3000,3500,4000,9000) ;
+------------+---------+
| name | salary |
+------------+---------+
| yuanhao | 3500.00 |
| jingliyang | 9000.00 |
+------------+---------+
mysql> SELECT name,salary FROM employee WHERE salary NOT IN (3000,3500,4000,9000) ;
+-----------+------------+
| name | salary |
+-----------+------------+
| egon | 7300.33 |
| alex | 1000000.31 |
| wupeiqi | 8300.00 |
| liwenzhou | 2100.00 |
| jinxin | 30000.00 |
| xiaomage | 10000.00 |
| 歪歪 | 3000.13 |
| 丫丫 | 2000.35 |
| 丁丁 | 1000.37 |
| 星星 | 3000.29 |
| 格格 | 4000.33 |
| 張野 | 10000.13 |
| 程咬金 | 20000.00 |
| 程咬銀 | 19000.00 |
| 程咬銅 | 18000.00 |
| 程咬鐵 | 17000.00 |
+-----------+------------+
16 rows in set (0.00 sec)
#6:關鍵字LIKE模糊查詢
通配符’%’
mysql> SELECT * FROM employee WHERE name LIKE 'jin%';
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| 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 |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
2 rows in set (0.00 sec)
通配符'_'
mysql> SELECT age FROM employee WHERE name LIKE 'ale_';
+-----+
| age |
+-----+
| 78 |
+-----+
1 row in set (0.00 sec)
練習：
1. 查看崗位是teacher的員工姓名、年齡
2. 查看崗位是teacher且年齡大於30歲的員工姓名、年齡
3. 查看崗位是teacher且薪資在9000-1000範圍內的員工姓名、年齡、薪資
4. 查看崗位描述不為NULL的員工信息
5. 查看崗位是teacher且薪資是10000或9000或30000的員工姓名、年齡、薪資
6. 查看崗位是teacher且薪資不是10000或9000或30000的員工姓名、年齡、薪資
7. 查看崗位是teacher且名字是jin開頭的員工姓名、年薪
#對應的sql語句
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%';
where約束
（2）group by 分組查詢
#1、首先明確一點：分組發生在where之後，即分組是基於where之後得到的記錄而進行的
#2、分組指的是：將所有記錄按照某個相同欄位進行歸類，比如針對員工信息表的職位分組，或者按照性別進行分組等
#3、為何要分組呢？
取每個部門的最高工資
取每個部門的員工數
取男人數和女人數
小竅門：‘每’這個字後面的欄位，就是我們分組的依據
#4、大前提：
可以按照任意欄位分組，但是分組完畢後，比如group by post，只能查看post欄位，如果想查看組內信息，需要藉助於聚合函數
當執行以下sql語句的時候，是以post欄位查詢了組中的第一條數據，沒有任何意義，因為我們現在想查出當前組的多條記錄。
mysql> select * from employee 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 | | 1000000.31 | 401 | 1 | | 1 | egon | male | 18 | 2017-03-01 | 老男孩駐沙河辦事處外交大使 | NULL | 7300.33 | 401 | 1 | +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ 4 rows in set (0.00 sec) #由於沒有設置ONLY_FULL_GROUP_BY,於是也可以有結果，預設都是組內的第一條記錄，但其實這是沒有意義的 如果想分組，則必須要設置全局的sql的模式為ONLY_FULL_GROUP_BY mysql> set global sql_mode='ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec) #查看MySQL 5.7預設的sql_mode如下： mysql> select @@global.sql_mode; +--------------------+ | @@global.sql_mode | +--------------------+ | ONLY_FULL_GROUP_BY | +--------------------+ 1 row in set (0.00 sec) mysql> exit;#設置成功後，一定要退出，然後重新登錄方可生效 Bye
繼續驗證通過group by分組之後，只能查看當前欄位,如果想查看組內信息，需要藉助於聚合函數
mysql> select * from emp group by post;# 報錯 ERROR 1054 (42S22): Unknown column 'post' in 'group statement' mysql> select post from employee group by post; +-----------------------------------------+ | post | +-----------------------------------------+ | operation | | sale | | teacher | | 老男孩駐沙河辦事處外交大使 | +-----------------------------------------+ 4 rows in set (0.00 sec)
（3）聚合函數
max()求最大值 min()求最小值 avg()求平均值 sum() 求和 count() 求總個數 #強調：聚合函數聚合的是組的內容，若是沒有分組，則預設一組 # 每個部門有多少個員工 select post,count(id) from employee group by post; # 每個部門的最高薪水 select post,max(salary) from employee group by post; # 每個部門的最低薪水 select post,min(salary) from employee group by post; # 每個部門的平均薪水 select post,avg(salary) from employee group by post; # 每個部門的所有薪水 select post,sum(age) from employee group by post;
1. 查詢崗位名以及崗位包含的所有員工名字 2. 查詢崗位名以及各崗位內包含的員工個數 3. 查詢公司內男員工和女員工的個數 4. 查詢崗位名以及各崗位的平均薪資 5. 查詢崗位名以及各崗位的最高薪資 6. 查詢崗位名以及各崗位的最低薪資 7. 查詢男員工與男員工的平均薪資，女員工與女員工的平均薪資小練習
（4）HAVING過濾
HAVING與WHERE不一樣的地方在於 #！！！執行優先順序從高到低：where > group by > having #1. Where 發生在分組group by之前，因而Where中可以有任意欄位，但是絕對不能使用聚合函數。 #2. Having發生在分組group by之後，因而Having中可以使用分組的欄位，無法直接取到其他欄位,可以使用聚合函數
驗證：
驗證： mysql> select * from employee where salary>1000000; +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ 1 row in set (0.00 sec) mysql> select * from employee having salary>1000000; ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clause # 必須使用group by才能使用group_concat()函數，將所有的name值連接 mysql> select post,group_concat(name) from emp group by post having salary > 10000; ##錯誤，分組後無法直接取到salary欄位 ERROR 1054 (42S22): Unknown column 'post' in 'field list'View Code
小練習：
1. 查詢各崗位內包含的員工個數小於2的崗位名、崗位內包含員工名字、個數 2. 查詢各崗位平均薪資大於10000的崗位名、平均工資 3. 查詢各崗位平均薪資大於10000且小於20000的崗位名、平均工資
小練習答案：
# 題1： mysql> select post,group_concat(name),count(id) from employee group by post; +-----------------------------------------+-----------------------------------------------------------+-----------+ | post | group_concat(name) | count(id) | +-----------------------------------------+-----------------------------------------------------------+-----------+ | operation | 程咬鐵,程咬銅,程咬銀,程咬金,張野 | 5 | | sale | 格格,星星,丁丁,丫丫,歪歪 | 5 | | teacher | xiaomage,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex | 7 | | 老男孩駐沙河辦事處外交大使 | egon | 1 | +-----------------------------------------+-----------------------------------------------------------+-----------+ 4 rows in set (0.00 sec) 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 | +-----------------------------------------+--------------------+-----------+ 1 row in set (0.00 sec) #題2： mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | operation | 16800.026000 | | teacher | 151842.901429 | +-----------+---------------+ 2 rows in set (0.00 sec) #題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 | +-----------+--------------+ 1 row in set (0.00 sec)View Code
（5）order by 查詢排序
按單列排序
SELECT * FROM employee ORDER BY age;
SELECT * FROM employee ORDER BY age ASC;
SELECT * FROM employee ORDER BY age DESC;
按多列排序:先按照age升序排序，如果年紀相同，則按照id降序
SELECT * from employee
ORDER BY age ASC,
id DESC;
驗證多列排序： SELECT * from employee ORDER BY age ASC,id DESC; mysql> SELECT * from employee ORDER BY age ASC,id DESC; +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 18 | 程咬鐵 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | | 17 | 程咬銅 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 16 | 程咬銀 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 1 | egon | male | 18 | 2017-03-01 | 老男孩駐沙河辦事處外交大使 | NULL | 7300.33 | 401 | 1 | | 14 | 張野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 8 | xiaomage | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 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 | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ 18 rows in set (0.01 sec) mysql>驗證多列排序
小練習：
1. 查詢所有員工信息，先按照age升序排序，如果age相同則按照hire_date降序排序 2. 查詢各崗位平均薪資大於10000的崗位名、平均工資,結果按平均薪資升序排列 3. 查詢各崗位平均薪資大於10000的崗位名、平均工資,結果按平均薪資降序排列
# 題目1 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 | +-----------+---------------+ 2 rows in set (0.00 sec) # 題目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 | +-----------+---------------+ 2 rows in set (0.00 sec) mysql>小練習答案
（5）limit 限制查詢的記錄數:
示例：
SELECT * FROM employee ORDER BY salary DESC
LIMIT 3; #預設初始位置為0
SELECT * FROM employee ORDER BY salary DESC
LIMIT 0,5; #從第0開始，即先查詢出第一條，然後包含這一條在內往後查5條
SELECT * FROM employee ORDER BY salary DESC
LIMIT 5,5; #從第5開始，即先查詢出第6條，然後包含這一條在內往後查5條
小練習：
分頁顯示，每頁5條
# 第1頁數據 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) # 第2頁數據 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 | xiaomage | 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) # 第3頁數據 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)小練習答案
