MySQL單表查詢(分組-篩選-過濾-去重-排序）

目錄

一:單表查詢

1.單表查詢(前期準備)
create table emp(
  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,  # 用戶如不輸入 默認28
  hire_date date not null,  # 僱傭日期
  post varchar(50),  # 職業
  post_comment varchar(100),  # 員工描述
  salary double(15,2),  # 薪水
  office int,  #一個部門一個屋子
  depart_id int  # 編號
);
2.插入記錄(寫入數據)
  • 三個部門：
    教學，銷售，運營
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','張江第一帥形象代言',7300.33,401,1), 
# 以下是教學部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','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);

image

3.查詢關鍵字
select
	控制查詢表中的哪些字段對應的數據
from
	控制查詢的表
  • 結合使用:
select * from t1;
作用:
    查詢t1表內所以記錄
        
select name from t1;
作用:
    查詢t1表內name字段

二:查詢關鍵字之where

關鍵字: where
作用:
	其實就是對數據進行篩選
1.查詢id大於等於3小於等於6的數據
select id,name from emp where id >= 3 and id <= 6;
select id,name from emp where id between 3 and 6;  # 簡寫

between :選取介於兩個值之間的數據範圍內的值

2.查詢薪資是20000或者18000或者17000的數據
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000,18000,17000);  # 簡寫
3.模糊查詢(like)
模糊查詢
關鍵字       like

模糊查詢應用場景:
	當查詢對象(名稱不全)(數字不全)(不確定內容)時，可以使用模糊查詢。

關鍵符號:
%		: 匹配任意個數的任意字符
_		: 匹配單個 個數的任意字符
4.查詢員工姓名中包含o字母的員工姓名和薪資
select name,salary from emp where name like '%o%';
5.查詢員工姓名為四個字符組成的員工姓名和薪資
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) = 4;
6.查詢id小於3或者大於6的數據
select *  from emp where id not between 3 and 6;
7.查詢薪資不在20000，18000，17000範圍的數據
select * from emp where salary not in (20000,18000,17000);
8.(查詢崗位描述為空的員工名與崗位名) 針對null不能用等號，只能用is(才能查詢到)
select name,post from emp where post_comment = NULL;  # 查詢為空！

select name,post from emp where post_comment is NULL;
select name,post from emp where post_comment is not NULL;

三:查詢關鍵字之group by分組

1.什麼是分組？
按照某個指定的條件將單個單個的數據分為一個個整體
  • 分組
咱班按照座位橫向分組
咱班按照年齡分組
咱班按照省份分組
2.應用場景
求每個部門的平均薪資
求每個國家的人均GDP
求男女平均薪資
3.如何對數據進行分組?
關鍵字		group by 條件
4.實現分組
分組之後不再以單個個體為研究對象 也無法直接再獲取單個個體的數據
研究對象應該是分組的整體
解析:
	分組之後獲取是(部門整體)而不是(個體)

分組之後默認只能直接獲取到分組的依據 其他字段數據無法直接獲取
解析:
	使用(post/部門)進行分組的，使用slect只能以post來做分組

image

如果需要實現上述要求 還是修改sql_mode
set global sql_mode='only_full_group_by';
修改完後重新登錄MySQL
exit

注意:
分組之後默認只能直接獲取到分組的依據 其他字段數據無法直接獲取

image

5.聚合函數
max()		: 求最大值
min()		: 求最小值
sum()		: 求合
count()		: 計數
avg()		: 平均值
# 上述聚合函數都是在分組之後使用 用於操作整體數據
6.as語法(起別名)
as語法在查看結果的時候可以給字段起別名
格式:
	select post as '部門',max(salary) as '最高薪資' from emp group by post;
省略as:
	select post '部門',max(salary) '最高薪資' from emp group by post;
	
注意:
	as可以省略但是為了語義更加明確建議不要省略

四:分組實戰案例

1.獲取每個部門的最大薪資
select post '部門',max(salary) '最高薪資' from emp group by post;

image

獲取每個部門的最低薪資
select post '部門',min(salary) '最低薪資' from emp group by post;

統計每個部門的平均薪資(平均薪資不客觀 客觀表現(中位數))
select post '部門',avg(salary) '平均薪資' from emp group by post;

image

2.統計每個部門的人數
select post,count(id) from emp group by post;
count(id)	: count()只是計數 不是針對括號內的id字段

image

3.獲取每個部門的員工姓名(拼接)
select post,group_concat(name) from emp group by post;

image

獲取每個部門的員工姓名(分組之後拼接)
select post,group_concat(name,'|',salary) from emp group by post;

group_concat	用於分組之後獲取分組以外的字段數據並支持拼接(間接拿)

image

獲取員工姓名(分組之前拼接)
select id,concat(name,'|',salary) from emp;

concat			用於分組之前的拼接操作

image

獲取多個字段(簡寫分隔符)分組前
select id,concat_ws('|',name,sex,salary,age) from emp;
concat_ws		當多個字段連接符相同的情況下推薦使用

image

五；查詢關鍵字之having過濾

1.where與having區別
where與having都是用來篩選數據的
但是where用於分組之前的篩選
having用於分組之後的篩選
為了人為的區分開 我們將where用篩選來形容 having用過濾來形容
2.having過濾案例
統計各部門年齡在30歲以上的員工平均工資，並且保留平均工資大於10000的部門


將一個複雜的查詢題拆分成多個簡單的小題:

1.查看整張表的內容: select * from emp;

2.統計年齡在30歲以上的員工: select * from emp where age > 30;
   
3.給各個部門進行分組: select post from emp group by post;
    
4.計算各部門的平均薪資: select post,avg(salary) from emp group by post
    
5.各部門30歲以上的平均薪資: select post,avg(salary) from emp where age > 30 group by post;
    
6.使用having(分組之後)過濾，並且保留平均工資大於10000的部門:
select post,avg(salary) from emp where age > 30 group by post having avg(salary)>10000;

image

六:查詢關鍵字之distinct去重

1.distinct去重
1.去重的前提示是存在一模一樣的數據
2.如果存在主鍵肯定無法去重(主鍵是 非空且唯一)
2.對有重複的展示數據進行去重操作 一定要是重複的數據
select distinct id,age from emp;  # 無效果
select distinct id,age distinct from emp;  # 報錯

select distinct age from emp;

image

七:查詢關鍵字之order by排序

1.關鍵字order by 排序
order by默認是升序  默認的關鍵字是asc

升序	: asc
降序	: desc
2.薪資由低到高排序(升序)
select * from emp order by salary asc;  # 也可以不寫 默認升序

image

3.薪資由高到低排序(降序)
select * from emp order by salary desc;  # 降序

image

4.order by排序支持多個字段組合(第一個不行 就往後繼續排)
解析:
    第一個字段排序出現重複時，會從第二個字段排序進行升序比較
    
select * from emp order by age,salary;

image

5.order by(多段排序 指定排序)
作用:
可以多段排序，可以給多段排序指定(升序 或 降序)

select * from emp order by age asc,salary desc;

image

Tags:

VirMach 便宜 VPS

QNews

QNews