單表查詢的語法及關鍵字執行的優先級

單表查詢語法

select distinct 字段一,字段二,... from 表名
                                where 條件
                                group by field
                                having 篩選
                                order by field
                                limit 限制條數

 關鍵字執行的優先級

　　1. 找到表: from

　　2. 拿著where指定的約束條件,去文件 / 表中取出一條條記錄

　　3. 將取出的一條條記錄進行分組group by , 如果沒有group by ,則整體作為一組

　　4. 執行select  (distinct --> 去重)

　　5.  將分組的結果進行having過濾

　　6. 將結果按條件排序: order by

　　7. 限制結果的顯示條數

 建表和數據的準備:

#創建表
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
);


#插入記錄
#三個部門：教學，銷售，運營
insert into employee(emp_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),
('成龍','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. select 語句:

• 基礎命令:
  • select * from 表名;    查看表中的所有數據
  • select 字段名,字段名,... from 表名;    查看表中的某幾列數據
  • insert into 已建好的表名(字段,...) select 字段,... from 另一張表名;    從另一張表中導入需要的數據
• 重命名字段
  • select 字段名 as 新名字 from 表名;
  • select 字段名 新名字 from 表名;     
  • 因此加不加 as 的效果一樣
• 去重:
  • select distinct 字段 from 表名;
  • 當查詢的字段中存在相同的數據時,只會查詢到不重復的數據
• 定義顯示格式:
  • 使用函數
    • concat()  用于連接字符串
      • select concat('要拼接的字符串',字段名,'要拼接的字符串',字段名) from 表名;    
    • concat ws()  第一個參數為分隔符
      • select concat ws('類似于 | : - 的分隔符',字段名,字段名) from 表名;    將兩個或多個字段通過分隔符連接起來
  • 結合case語句    (判斷邏輯,相當于if條件判斷句)

    •  
      select (
      case
      when 字段名1 = 'aaa' then
          字段名                                    # 如果字段名1的內容為aaa時,就返回字段名原來的數據
      when 字段名1 = 'bbb' then
          concat(字段名1,'wahaha')         # 如果字段名1的內容為bbb時,就將字段名和wahaha拼接起來返回
      else
          concat(字段名1,'shuangwaiwai')  # 如果字段名1的內容不為上面兩個時,就將字段名和shuangwaiwai拼接起來返回
      end
      )   as new_name    # 給這字段起一個新名字
      from 表名 

 2. 通過四則運算查詢

• 從一張員工月薪表中查詢員工的年薪
  • select name,salary*12 from 表名;    乘法運算

3. where 篩選出所有符合條件的行

• 比較運算符:  < , > , <= , >= , <> , !=   (后兩個為不等于)
• 關鍵字is : 判斷某個字段是否為null,不能用等號,要用is
  • where 字段名 is null;    篩選字段為null的數據
  • where 字段名 is not null;    篩選字段名不為null的數據
• 關鍵字between 值 and 值
  • where 字段名 between 90 and 100;    篩選字段值在90到100的數據
  • 不支持這種寫法: 90 < 字段名> 100
• 關鍵字in查詢
  • where 字段名 in(80,90,100);    篩選字段值為80或90或100的數據
• 關鍵字like查詢
  • % : 通配符,表示可以匹配任意長度的任意內容
    • select * from 表名 where 字段名 like 'xx%';    查詢字段名是以xx開頭的數據
    • select * from 表名 where 字段名 like '%xx';    查詢字段名是以xx結尾的數據
  • _ : 通配符,表示可以匹配一個字符長度的任意內容
    • select * from 表名 where 字段名 like '林__';    查詢出字段名是以 '林'開頭,并且字符長度是3的數據

4. 邏輯運算 (與 或 非)

• 在多個條件下可以直接使用邏輯運算符: 優先級:  not > and > or

二 . 分組聚合

1. 分組:  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;#按照崗位分組，并查看每個組有多少人

2. 聚合函數

• 強調: 聚合函數聚合的是組的內容,若是沒有組,則默認表為一組
• count(字段名)  求個數
  • select count(*) from 表名;    查詢表中有多少行數據
• max  求最大值
  • select 字段名,max(age) from 表名 group by 字段名; 根據字段名分組,然后查詢每個分組中年齡最大的一個
• min  求最小值
  • select min(age) from 表名;    查詢表中年齡最小的一個
• sum  求和
  • select sum(age) from 表名;    對表中age這一列的數據求和
• avg  求平均值
  • select avg(age) from 表名;    查詢表中年齡的平均值

三. having 過濾語句

• 執行優先級: where > group by > having
• where發生在group by之前,ywhere中可以有任意字段,所以不能用分組聚合函數
• 適合去篩選符合條件的某一組數據,而不是某一行數據
• having發生在分組group by之后,因而having中可以使用分組的字段,無法直接獲取到其他字段,可以通過聚合函數來獲取

  mysql> select post,group_concat(emp_name) from emp group by post having salary > 10000;
  ERROR 1054 (42S22): Unknown column 'salary' in 'having clause' #錯誤，分組后無法直接取到salary字段
      
  # 通過分組聚合獲取值:
  查詢各崗位平均薪資大于10000且小于20000的崗位名、平均工資
  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 排列語句

• 默認是升序(從小到大)  --> asc
• 降序(從大到小) --> desc

  # 按多列排序:先按照age排序，如果年紀相同，則按照薪資排序
  select * from employee order by age,salary desc;
  
  # 查詢各崗位平均薪資大于10000的崗位名、平均工資,結果按平均薪資升序排列
  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)

   

五. limit 限制查詢的記錄條數

• limit m,n : 從 m+1 項開始,取n項,如果不寫m,m默認為0
• 另一種寫法: limit n offset m

  # 從第0開始，即先查詢出第0+1條，然后包含這一條在內往后查3條
  select * from employee order by salary desc limit 3;
  
  # 從第5開始，即先查詢出第6條，然后包含這一條在內往后查5條
  select * from employee order by salary desc limit 5,5;