無(wú)條件簡(jiǎn)單查詢方法

虛擬數(shù)據(jù)準(zhǔn)備

-- [創(chuàng)建表] --
DROP TABLE IF EXISTS `company_staff`;
CREATE TABLE `company_staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(125) NOT NULL,
  `age` tinyint(4) DEFAULT '0',
  `sex` enum('男','女','保密') NOT NULL DEFAULT '保密',
  `salary` decimal(10,3) NOT NULL DEFAULT '5000.000',
  `hire_date` date NOT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

-- [創(chuàng)建數(shù)據(jù)] --

-- [軟件開(kāi)發(fā)部]  --
INSERT INTO `company_staff` VALUES ('1', 'Tom', '25', '男', '27000.000', '2019-04-21', '1');
INSERT INTO `company_staff` VALUES ('2', 'Roudo', '27', '男', '38000.000', '2020-02-21', '1');
INSERT INTO `company_staff` VALUES ('3', 'Aaron', '30', '男', '26500.000', '2017-03-11', '1');
INSERT INTO `company_staff` VALUES ('4', 'Lance', '48', '男', '46680.000', '2003-05-20', '1');

-- [國(guó)際事務(wù)]  --
INSERT INTO `company_staff` VALUES ('5', 'Leo', '23', '男', '13600.000', '2017-06-21', '2');
INSERT INTO `company_staff` VALUES ('6', 'Lee', '24', '男', '12400.000', '2013-06-30', '2');
INSERT INTO `company_staff` VALUES ('7', 'lvan', '26', '女', '12500.000', '2014-03-27', '2');
INSERT INTO `company_staff` VALUES ('8', 'Jack', '29', '女', '22300.000', '2003-07-21', '2');

-- [法律部] --
INSERT INTO `company_staff` VALUES ('9', '張飛', '41', '女', '24000.000', '2003-05-21', '3');
INSERT INTO `company_staff` VALUES ('10', '李逵', '42', '女', '34000.000', '2005-04-15', '3');
INSERT INTO `company_staff` VALUES ('10', '羅翔', '45', '男', '54000.000', '2005-03-15', '3');

-- [銷售部] --
INSERT INTO `company_staff` VALUES ('11', '孫權(quán)', '37', '女', '65000.000', '2000-06-21', '4');

簡(jiǎn)單查詢方法格式

#查詢語(yǔ)法: 
select [distinct]*(所有)|字段名,...字段名 from 表名;
 
#查詢所有字段信息
select * from company_staff;
 
#查詢指定字段信息
select id,name from company_staff;
 
#別名查詢，使用的as關(guān)鍵字，as可以省略的
select name,age as'年齡',salary '工資' from company_staff;
 
#刪除重復(fù)查詢--[distinct]
select distinct age from company_staff;

按條件查詢方法

條件查詢:使用 WHERE 關(guān)鍵字 對(duì)簡(jiǎn)單查詢的結(jié)果集 進(jìn)行過(guò)濾

1. 比較運(yùn)算符: > < >= <= = <>(!=)
2. null 關(guān)鍵字: is null , not null
3. 邏輯運(yùn)算符: 與 and 或 or (多個(gè)條件時(shí),需要使用邏輯運(yùn)算符進(jìn)行連接)

#查詢格式:
select [distinct]*(所有)|字段名,...字段名 from 表名 [where 條件過(guò)濾]

#比較運(yùn)算符: > < >= <= = <>(!=)    is null 是否為null
select * from company_staff where age = 33;
select * from company_staff where age < 23;
select * from company_staff where age is null;
select * from company_staff where age is not null;

#邏輯運(yùn)算符: 與 and 或 or
select * from company_staff where age = 23 and salary =29000;
select * from company_staff where age = 35 or salary =29000;

按照區(qū)間查詢方法

關(guān)鍵字 between 1 and 5 :表示 獲得1 到 5 區(qū)間的內(nèi)容

# 應(yīng)用  between...and  進(jìn)行區(qū)間 查詢
select * from company_staff where salary between 14000 and 23000;
# PS: between...and 前后包含所指定的值
等價(jià)于 select * from company_staff where salary >= 14000 and salary <= 23000;

按照集合查詢方法

關(guān)鍵字: in, not null

#使用 in 集合(多個(gè)字段)查詢
select * from company_staff where age in(23,29,41);
等價(jià)于: select * from company_staff where  age =23 or age = 29 or age =41;

#使用 in 集合 排除指定值查詢
select * from company_staff where age not in(23,29,41);

應(yīng)用模糊查詢

關(guān)鍵字 like , not like

%: 任意多個(gè)字符

_ : 只能是單個(gè)字符

#模糊查詢  like %:任意多個(gè)字符,  _:單個(gè)字符

#查詢姓名以"L"字開(kāi)頭的
select * from company_staff where name like 'L%';
#查詢姓名以"L"字結(jié)尾的
select * from company_staff where name like '%L';
#查詢姓名中含有"L"字的
select * from company_staff where name like '%L%';

#查詢 name 名稱 是三個(gè)字符的人
select * from company_staff where name like '___';
#查詢 name 名稱 的第二個(gè)字符是 'o'的人
select * from company_staff where name like '_o%';

#排除名字帶 a的人
select * from company_staff where name not like 'a%'

應(yīng)用排序查詢

關(guān)鍵字: ORDER BY 字段1 DESC, 字段2 ASC

#排序查詢格式:
select 字段|* from 表名 [where 條件過(guò)濾] [order by 字段[ASC][DESC]]
 
升序:ASC 默認(rèn)為升序
降序:DESC
PS:排序order by 要寫(xiě)在select語(yǔ)句末尾
 
#按人員工資正序排列,注意:此處可以省略 ASC關(guān)鍵字
select * from company_staff order by salary ASC;
select * from company_staff order by salary;
 
#工資大于12000的人,按工資倒序排列
select * from company_staff where salary >12000 order by salary DESC;
 
#按中文排序
select * from company_staff order by name;
 
#強(qiáng)制中文排序
select * from company_staff order by CONVERT(name USING gbk);
ps:UTF8 默認(rèn)校對(duì)集是 utf8_general_ci , 它不是按照中文來(lái)的。你需要強(qiáng)制讓MySQL按中文來(lái)排序

聚合函數(shù)匯總

聚合: 將分散的聚集到一起. 　　

聚合函數(shù): 對(duì)列進(jìn)行操作,返回的結(jié)果是一個(gè)單一的值,除了 COUNT 以外，都會(huì)忽略空值

• COUNT：統(tǒng)計(jì)指定列不為NULL的記錄行數(shù)； 　　
• SUM：計(jì)算指定列的數(shù)值和，如果指定列類型不是數(shù)值類型，那么計(jì)算結(jié)果為0； 　　
• MAX：計(jì)算指定列的最大值，如果指定列是字符串類型，那么使用字符串排序運(yùn)算； 　　
• MIN：計(jì)算指定列的最小值，如果指定列是字符串類型，那么使用字符串排序運(yùn)算； 　　
• AVG：計(jì)算指定列的平均值，如果指定列類型不是數(shù)值類型，那么計(jì)算結(jié)果為0；

#格式:
select 聚合函數(shù)(字段) from 表名;
 
#統(tǒng)計(jì)人員中最大年齡、最小年齡，平均年齡分別是多少
select max(age),min(age),avg(age) from company_staff;

分組查詢方法

分組的含義: 將一些具有相同特征的數(shù)據(jù) 進(jìn)行歸類.比如:性別,部門,崗位等等

怎么區(qū)分什么時(shí)候需要分組呢?

套路: 遇到 "每" 字,一般需要進(jìn)行分組操作.

例如: 1. 公司每個(gè)部門有多少人.

2. 公司中有 多少男員工 和 多少女員工.

#分組查詢格式:
select 被分組的字段 from 表名 group by 分組字段 [having 條件字段]
ps: 分組查詢可以與 聚合函數(shù) 組合使用.

#查詢每個(gè)部門的平均工資
select avg(salary),dept from company_staff  GROUP BY dept;

#查詢每個(gè)部門的平均薪資 并且看看這個(gè)部門的員工都有誰(shuí)?
select avg(salary),dept,GROUP_CONCAT(name) from company_staff GROUP BY dept;
#GROUP_CONCAT(expr):按照分組，將expr字符串按逗號(hào)分隔，組合起來(lái)

#查詢平均薪資大于15000的部門, 并且看看這個(gè)部門的員工都有誰(shuí)?  
select avg(salary),dept,GROUP_CONCAT(name) from company_staff GROUP BY dept; having avg(salary)>10000;

where 與 having區(qū)別
執(zhí)行優(yōu)先級(jí)從高到低：where > group by > having
Where 發(fā)生在分組group by之前，因而Where中可以有任意字段，但是絕對(duì)不能使用聚合函數(shù)。
Having發(fā)生在分組group by之后，因而Having中可以使用分組的字段，無(wú)法直接取到其他字段,可以使用聚合函數(shù)

分頁(yè)查詢方法

好處:限制查詢數(shù)據(jù)條數(shù),提高查詢效率

#查詢前2條數(shù)據(jù)
select * from company_staff limit 2;
 
#查詢第3條到第7條數(shù)據(jù)
select * from company_staff limit 3,7;
 
#查詢第10條到第15條數(shù)據(jù)
select * from company_staff limit 10,5;
 
ps: limit (起始條數(shù)),(查詢多少條數(shù));

正則表達(dá)式方法

MySQL中使用 REGEXP 操作符來(lái)進(jìn)行正則表達(dá)式匹配。

# ^  匹配 name 名稱 以 "L" 開(kāi)頭的數(shù)據(jù)
select * from company_staff where name REGEXP '^L';
 
# $  匹配 name 名稱 以 "m" 結(jié)尾的數(shù)據(jù)
select * from company_staff where name REGEXP 'm$';
 
# . 匹配 name 名稱 第二位后包含"f"的人員 "."表示任意字符
select * from company_staff where name REGEXP '.f';
 
# [abci] 匹配 name 名稱中含有指定集合內(nèi)容的人員
select * from company_staff where name REGEXP '[lmns]';
 
# [^Tom] 匹配 不符合集合中條件的內(nèi)容 , ^表示取反
select * from company_staff where name REGEXP '[^Tom]';
#注意1：^只有在[]內(nèi)才是取反的意思，在別的地方都是表示開(kāi)始處匹配
#注意2 : 簡(jiǎn)單理解 name  REGEXP '[^Tom]' 等價(jià)于 name != 'Tom'
 
# 'l|s' 匹配 條件中的任意值
select * from company_staff where name REGEXP 'l|s';　　
 
#查詢以L開(kāi)頭以s結(jié)尾的數(shù)據(jù)
select * from company_staff where name regexp '^L.*s$';
#注意:^w 表示w開(kāi)頭, .*表示中間可以有任意多個(gè)字符, i$表示以 i結(jié)尾

SQL 語(yǔ)句關(guān)鍵字的執(zhí)行順序

查詢:姓名不同人員的最高工資,并且要求大于12500元,同時(shí)按最高工資進(jìn)行排序并取出前3條.

select name, max(salary)
   
from company_staff  
   
where name is not null  
   
group by name  
   
havng max(salary) > 12500
   
order by max(salary)
limit 0,3

在上面的示例中 SQL 語(yǔ)句的執(zhí)行順序如下

(1). 首先執(zhí)行 FROM 子句, 從 company_staff 表 組裝數(shù)據(jù)源的數(shù)據(jù)

(2). 執(zhí)行 WHERE 子句, 篩選 company_staff 表中 name 不為 NULL 的數(shù)據(jù)

(3). 執(zhí)行 GROUP BY 子句, 把 company_staff 表按 "name" 列進(jìn)行分組

(4). 計(jì)算 max() 聚集函數(shù), 按 "工資" 求出工資中最大的一些數(shù)值

(5). 執(zhí)行 HAVING 子句, 篩選工資大于 12500的人員.

(7). 執(zhí)行 ORDER BY 子句, 把最后的結(jié)果按 "Max 工資" 進(jìn)行排序.

(8). 最后執(zhí)行 LIMIT 子句, . 進(jìn)行分頁(yè)查詢

執(zhí)行順序: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY ->limit