MySQL單表查詢

目錄

• 一、單表查詢的語法及關鍵字執行的優先順序
  • 1.1 單表查詢語法
  • 1.2 關鍵字執行的優先順序
• 二、簡單查詢
  • 2.1 建表和資料準備
  • 2.2 練習
• 三、約束條件（where）
  • 3.1 練習
• 四、分組（group by）
• 五、聚合函式
  • 5.1 練習
• 六、過濾（having）
  • 6.1 where和having的區別
    • 6.1.1 驗證
  • 6.2 練習
• 七、查詢排序（order by）
  • 7.1 練習
• 八、限制查詢的記錄數（limit）
  • 8.1 練習
• 九、使用正規表示式查詢
  • 9.1 練習

一、單表查詢的語法及關鍵字執行的優先順序

1.1 單表查詢語法

SELECT DISTINCT 欄位1,欄位2... FROM 表名
                              WHERE 條件
                              GROUP BY field
                              HAVING 篩選
                              ORDER BY field
                              LIMIT 限制條數

1.2 關鍵字執行的優先順序

1. from：找到表
2. where：拿著where指定的約束條件，去檔案/表中取出一條條記錄
3. group by：將取出的一條條記錄進行分組group by，如果沒有group by，則整體作為一組
4. select：執行select
5. distinct：去重
6. having：將分組的結果進行having過濾
7. order by：將結果按條件排序：order by
8. limit：限制結果的顯示條數

二、簡單查詢

2.1 建表和資料準備

company.employee
    員工id      id                  int             
    姓名        emp_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 not null unique auto_increment,
emp_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    |                |
| salary       | double(15,2)          | YES  |     | NULL    |                |
| office       | int(11)               | YES  |     | NULL    |                |
| depart_id    | int(11)               | YES  |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+

# 插入記錄
# 三個部門：教學，銷售，運營
insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('nick','male',18,'20170301','老男孩駐上海虹橋最帥',7300.33,401,1),  #  以下是教學部
('jason','male',78,'20150302','teacher',1000000.31,401,1),
('sean','male',81,'20130305','teacher',8300,401,1),
('tank','male',73,'20140701','teacher',3500,401,1),
('oscar','male',28,'20121101','teacher',2100,401,1),
('mac','female',18,'20110211','teacher',9000,401,1),
('rocky','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),  #  以下是運營部門
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬銀','female',18,'20130311','operation',19000,403,3),
('程咬銅','male',18,'20150411','operation',18000,403,3),
('程咬鐵','female',18,'20140512','operation',17000,403,3)
;

#  ps：如果在windows系統中，插入中文字元，select的結果為空白，可以將所有字元編碼統一設定成gbk

# 簡單查詢
    SELECT id,emp_name,sex,age,hire_date,post,post_comment,salary,office,depart_id 
    FROM employee;

    SELECT * FROM employee;

    SELECT emp_name,salary FROM employee;

# 避免重複DISTINCT
    SELECT DISTINCT post FROM employee;    

# 通過四則運算查詢
    SELECT emp_name, salary*12 FROM employee;
    SELECT emp_name, salary*12 AS Annual_salary FROM employee;
    SELECT emp_name, salary*12 Annual_salary FROM employee;

# 定義顯示格式
   CONCAT() 函式用於連線字串
   SELECT CONCAT('姓名: ',emp_name,'  年薪: ', salary*12)  AS Annual_salary 
   FROM employee;
   
   CONCAT_WS() 第一個引數為分隔符
   SELECT CONCAT_WS(':',emp_name,salary*12)  AS Annual_salary 
   FROM employee;

   結合CASE語句：
   SELECT
       (
           CASE
           WHEN emp_name = 'mac' THEN
               emp_name
           WHEN emp_name = 'jason' THEN
               CONCAT(emp_name,'_BIGSB')
           ELSE
               concat(emp_name, 'SB')
           END
       ) as new_name
   FROM
       employee;

2.2 練習

1. 查出所有員工的名字，薪資，格式為<名字:nick> <薪資:3000>
2. 查出所有的崗位（去掉重複）
3. 查出所有員工名字，以及他們的年薪，年薪的欄位名為annual_year

select concat('<名字:',emp_name,'>    ','<薪資:',salary,'>') from employee;
select distinct depart_id from employee;
select emp_name,salary*12 annual_salary from employee;

三、約束條件（where）

where子句中可以使用：

1. 比較運算子：> < >= <= <> !=
2. between 80 and 100 值在80到100之間
3. in(80,90,100) 值是80或90或100
4. like 'n%'
   • 萬用字元可以是%或_，
     • %表示任意多字元
     • _表示一個字元
5. 邏輯運算子：在多個條件直接可以使用邏輯運算子 and or not

1. 單條件查詢
    SELECT emp_name FROM employee
        WHERE post='sale';
        
2. 多條件查詢
    SELECT emp_name,salary FROM employee
        WHERE post='teacher' AND salary>10000;

3. 關鍵字BETWEEN AND
    SELECT emp_name,salary FROM employee 
        WHERE salary BETWEEN 10000 AND 20000;

    SELECT emp_name,salary FROM employee 
        WHERE salary NOT BETWEEN 10000 AND 20000;
    
4. 關鍵字IS NULL(判斷某個欄位是否為NULL不能用等號，需要用IS)
    SELECT emp_name,post_comment FROM employee 
        WHERE post_comment IS NULL;

    SELECT emp_name,post_comment FROM employee 
        WHERE post_comment IS NOT NULL;
        
    SELECT emp_name,post_comment FROM employee 
        WHERE post_comment=''; 注意''是空字串，不是null
    ps：
        執行
        update employee set post_comment='' where id=2;
        再用上條檢視，就會有結果了

5. 關鍵字IN集合查詢
    SELECT emp_name,salary FROM employee 
        WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
    
    SELECT emp_name,salary FROM employee 
        WHERE salary IN (3000,3500,4000,9000) ;

    SELECT emp_name,salary FROM employee 
        WHERE salary NOT IN (3000,3500,4000,9000) ;

6. 關鍵字LIKE模糊查詢
    萬用字元’%’
    SELECT * FROM employee 
            WHERE emp_name LIKE 'ni%';

    萬用字元’_’
    SELECT * FROM employee 
            WHERE emp_name LIKE 'ja__';

3.1 練習

1. 檢視崗位是teacher的員工姓名、年齡
2. 檢視崗位是teacher且年齡大於30歲的員工姓名、年齡
3. 檢視崗位是teacher且薪資在9000-1000範圍內的員工姓名、年齡、薪資
4. 檢視崗位描述不為NULL的員工資訊
5. 檢視崗位是teacher且薪資是10000或9000或30000的員工姓名、年齡、薪資
6. 檢視崗位是teacher且薪資不是10000或9000或30000的員工姓名、年齡、薪資
7. 檢視崗位是teacher且名字是jin開頭的員工姓名、年薪

select emp_name,age from employee where post = 'teacher';
select emp_name,age from employee where post='teacher' and age > 30; 
select emp_name,age,salary from employee where post='teacher' and salary between 9000 and 10000;
select * from employee where post_comment is not null;
select emp_name,age,salary from employee where post='teacher' and salary in (10000,9000,30000);
select emp_name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000);
select emp_name,salary*12 from employee where post='teacher' and emp_name like 'mac%';

四、分組（group by）

單獨使用GROUP BY關鍵字分組
    SELECT post FROM employee GROUP BY post;
    注意：我們按照post欄位分組，那麼select查詢的欄位只能是post，想要獲取組內的其他相關資訊，需要藉助函式

GROUP BY關鍵字和GROUP_CONCAT()函式一起使用
    SELECT post,GROUP_CONCAT(emp_name) FROM employee GROUP BY post;  # 按照崗位分組，並檢視組內成員名
    SELECT post,GROUP_CONCAT(emp_name) as emp_members FROM employee GROUP BY post;

GROUP BY與聚合函式一起使用
    select post,count(id) as count from employee group by post;  #  按照崗位分組，並檢視每個組有多少人

注意：如果我們用unique的欄位作為分組的依據，則每一條記錄自成一組，這種分組沒有意義；多條記錄之間的某個欄位值相同，該欄位通常用來作為分組的依據。

五、聚合函式

強調：聚合函式聚合的是組的內容，若是沒有分組，則預設一組。

示例：

    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;

5.1 練習

1. 查詢崗位名以及崗位包含的所有員工名字
2. 查詢崗位名以及各崗位內包含的員工個數
3. 查詢公司內男員工和女員工的個數
4. 查詢崗位名以及各崗位的平均薪資
5. 查詢崗位名以及各崗位的最高薪資
6. 查詢崗位名以及各崗位的最低薪資
7. 查詢男員工與男員工的平均薪資，女員工與女員工的平均薪資

題目1
mysql> select post,group_concat(emp_name) from employee group by post;
+-----------------------------------------+---------------------------------------------------------+
| post                                    | group_concat(emp_name)                                      |
+-----------------------------------------+---------------------------------------------------------+
| operation                               | 張野,程咬金,程咬銀,程咬銅,程咬鐵                        |
| sale                                    | 歪歪,丫丫,丁丁,星星,格格                                |
| teacher                                 | jason,sean,tank,oscar,mac,rocky,成龍   |
| 老男孩駐上海虹橋最帥              | nick                                                    |
+-----------------------------------------+---------------------------------------------------------+


題目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 |
+-----------------------------------------+-------------+

題目7
mysql> select sex,avg(salary) from employee group by sex;
+--------+---------------+
| sex    | avg(salary)   |
+--------+---------------+
| male   | 110920.077000 |
| female |   7250.183750 |
+--------+---------------+

六、過濾（having）

6.1 where和having的區別

執行優先順序從高到低：where > group by > having

1. Where 發生在分組group by之前，因而Where中可以有任意欄位，但是絕對不能使用聚合函式。
2. Having發生在分組group by之後，因而Having中可以使用分組的欄位，無法直接取到其他欄位,可以使用聚合函式

6.1.1 驗證

mysql> select @@sql_mode;
+--------------------+
| @@sql_mode         |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
row in set (0.00 sec)

mysql> select * from emp where salary > 100000;
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | emp_name | sex  | age | hire_date  | post    | post_comment | salary     | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
|  2 | jason | male |  78 | 2015-03-02 | teacher | NULL         | 1000000.31 |    401 |         1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
row in set (0.00 sec)

mysql> select post,group_concat(emp_name) from emp group by post having salary > 10000;#錯誤，分組後無法直接取到salary欄位
ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'
mysql> select post,group_concat(emp_name) from emp group by post having avg(salary) > 10000;
+-----------+-------------------------------------------------------+
| post | group_concat(emp_name) |
+-----------+-------------------------------------------------------+
| operation | 程咬鐵,程咬銅,程咬銀,程咬金,張野 |
| teacher | 成龍,rocky,mac,oscar,tank,sean,jason |
+-----------+-------------------------------------------------------+
rows in set (0.00 sec)

6.2 練習

1. 查詢各崗位內包含的員工個數小於2的崗位名、崗位內包含員工名字、個數
2. 查詢各崗位平均薪資大於10000的崗位名、平均工資
3. 查詢各崗位平均薪資大於10000且小於20000的崗位名、平均工資

題目1
mysql> select post,group_concat(emp_name),count(id) from employee group by post having count(id) < 2;
+-----------------------------------------+--------------------+-----------+
| post                                    | group_concat(emp_name) | count(id) |
+-----------------------------------------+--------------------+-----------+
| 老男孩駐上海虹橋最帥              | nick               |         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排序，如果年紀相同，則按照薪資排序
    SELECT * from employee
        ORDER BY age,
        salary DESC;

7.1 練習

1. 查詢所有員工資訊，先按照age升序排序，如果age相同則按照hire_date降序排序
2. 查詢各崗位平均薪資大於10000的崗位名、平均工資,結果按平均薪資升序排列
3. 查詢各崗位平均薪資大於10000的崗位名、平均工資,結果按平均薪資降序排列

題目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 |
+-----------+---------------+

八、限制查詢的記錄數（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條

8.1 練習

1. 分頁顯示，每頁5條

mysql> select * from  employee limit 0,5;
+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | emp_name      | sex  | age | hire_date  | post                                    | post_comment | salary     | office | depart_id |
+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
|  1 | nick      | male |  18 | 2017-03-01 | 老男孩駐上海虹橋最帥              | NULL         |    7300.33 |    401 |         1 |
|  2 | jason      | male |  78 | 2015-03-02 | teacher                                 |              | 1000000.31 |    401 |         1 |
|  3 | sean   | male |  81 | 2013-03-05 | teacher                                 | NULL         |    8300.00 |    401 |         1 |
|  4 | tank   | male |  73 | 2014-07-01 | teacher                                 | NULL         |    3500.00 |    401 |         1 |
|  5 | oscar | male |  28 | 2012-11-01 | teacher                                 | NULL         |    2100.00 |    401 |         1 |
+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
rows in set (0.00 sec)

mysql> select * from  employee limit 5,5;
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | emp_name       | sex    | age | hire_date  | post    | post_comment | salary   | office | depart_id |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
|  6 | mac | female |  18 | 2011-02-11 | teacher | NULL         |  9000.00 |    401 |         1 |
|  7 | rocky     | 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 |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
rows in set (0.00 sec)

mysql> select * from  employee limit 10,5;
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | emp_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 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
rows in set (0.00 sec)

九、使用正規表示式查詢

SELECT * FROM employee WHERE emp_name REGEXP '^jas';

SELECT * FROM employee WHERE emp_name REGEXP 'on$';

SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';


小結：對字串匹配的方式
WHERE emp_name = 'nick';
WHERE emp_name LIKE 'sea%';
WHERE emp_name REGEXP 'on$';

9.1 練習

1. 檢視所有員工中名字是mac開頭，n或者g結果的員工資訊

select * from employee where emp_name regexp '^mac.*[gn]$';