資料庫 -- 單表的數據查詢_ZenDei技術網路在線

單表查詢的語法及關鍵字執行的優先順序單表查詢語法關鍵字執行的優先順序 1. 找到表: from 2. 拿著where指定的約束條件,去文件 / 表中取出一條條記錄 3. 將取出的一條條記錄進行分組group by , 如果沒有group by ,則整體作為一組 4. 執行select (distin ...

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

單表查詢語法

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;