**查詢數(shù)據(jù)記錄操作: **
- 簡(jiǎn)單數(shù)據(jù)記錄查詢
- 條件數(shù)據(jù)記錄查詢
- 排序數(shù)據(jù)記錄查詢
- 限制數(shù)據(jù)記錄查詢
- 統(tǒng)計(jì)函數(shù)和分組數(shù)據(jù)記錄查詢
測(cè)試表
# 創(chuàng)建雇員表
create table t_employee(
empno int(11),
ename varchar(20),
job varchar(20),
MGR int(11),
Miredate date,
sal double(10,2),
comm double(10,2),
deptno int(11)
);
- 簡(jiǎn)單數(shù)據(jù)記錄查詢
簡(jiǎn)單數(shù)據(jù)記錄查詢: 簡(jiǎn)單數(shù)據(jù)查詢、避免重復(fù)數(shù)據(jù)查詢斑举、實(shí)現(xiàn)數(shù)據(jù)四則運(yùn)算數(shù)據(jù)查詢濒翻、設(shè)置顯示格式數(shù)據(jù)查詢。
(1) 簡(jiǎn)單數(shù)據(jù)查詢
簡(jiǎn)單數(shù)據(jù)查詢: 查詢所有字段數(shù)據(jù)热幔、查詢指定字段數(shù)據(jù)。
- 查詢所有字段數(shù)據(jù)
select *
from t_employee;
- 查詢指定字段數(shù)據(jù)
select empno, ename, sal
from t_employee;
(2)避免重復(fù)數(shù)據(jù)查詢(DISTINCT)
select distinct job
from t_employee;
(3)實(shí)現(xiàn)數(shù)據(jù)四則運(yùn)算數(shù)據(jù)查詢
select ename, sal*12
from t_employee;
修改顯示字段名(AS)
select ename, sal*12 as yearsalary
from t_employee;
(4) 設(shè)置顯示格式數(shù)據(jù)查詢
# CONCAT函數(shù)連接字符串
select concat(ename,'雇員的年薪為: ', sal*12) yearsalary
from t_employee;
- 條件數(shù)據(jù)記錄查詢
條件語(yǔ)句包含: 帶關(guān)系運(yùn)算符和邏輯運(yùn)算符的條件數(shù)據(jù)查詢畏纲、帶BETWEEN AND關(guān)鍵字的條件數(shù)據(jù)查詢独柑、帶IS NULL關(guān)鍵字的條件數(shù)據(jù)查詢、帶IN關(guān)鍵字的條件數(shù)據(jù)查詢缝其、帶LIKE關(guān)鍵字的條件數(shù)據(jù)查詢挎塌。
(1)帶關(guān)系運(yùn)算符和邏輯運(yùn)算符的條件數(shù)據(jù)查詢
- 單條件數(shù)據(jù)查詢
select ename
from t_employee
where job='clerk';
- 多條件語(yǔ)句查詢
select ename,sal
from t_employee
where job='clerk'
and sal>800;
(2) 帶BETWEEN AND關(guān)鍵字的條件數(shù)據(jù)查詢
- 符合范圍的數(shù)據(jù)記錄查詢
select ename,sal
from t_employee
where sal
between 1000 and 2000;
- 不符合范圍的數(shù)據(jù)記錄查詢
select ename,sal
from t_employee
where sal not between 1000 and 2000;
(3)帶IS NULL關(guān)鍵字的條件數(shù)據(jù)查詢
- 空值數(shù)據(jù)記錄查詢
select ename, comm
from t_employee
where comm is null;
- 非空值數(shù)據(jù)記錄查詢
select ename, comm
from t_employee
where not comm is null;
(4)帶IN關(guān)鍵字的條件數(shù)據(jù)查詢
IN--判斷字段的數(shù)值是否在指定的集合中
- 在集合中數(shù)據(jù)記錄查詢
select ename, empno
from t_employee
where empno in (7521,7780,7566,7788);
- 不在集合中數(shù)據(jù)記錄查詢
select ename, empno
from t_employee
where empno not in (7521,7780,7566,7788);
(5)帶LIKE關(guān)鍵字的條件數(shù)據(jù)查詢
LIKE--模糊查詢，支持的通配符如下：
-“_”通配符内边，該通配符值能匹配單個(gè)字符榴都。
-“%”通配符，該通配符值可以匹配任意長(zhǎng)度的字符串漠其，既可以是0個(gè)字符嘴高，也可以是1個(gè)字符，也可以是很多個(gè)字符和屎。在后邊表示向后模糊拴驮，在前面表示向前模糊，前后可以同時(shí)模糊柴信。
- 帶有“%”通配符的查詢
select ename
from t_employee
where ename like 'a%';
- 帶有“_”通配符的查詢
# 匹配出第二個(gè)字母是a的
select ename
from t_employee
where ename like '_a%';
- 排序數(shù)據(jù)記錄查詢
查詢時(shí)默認(rèn)為升序套啤。
排序數(shù)據(jù)查詢結(jié)果: 單字段排序、多字段排序随常。
(1) 按照單字段排序
- 升序排序
select *
from t_employee
order by sal asc;
- 降序排序
select *
from t_employee
order by mgr desc;
(2) 按照多字段排序
select *
from t_employee
order by sal asc,
Miredate desc;
- 限制數(shù)據(jù)記錄查詢數(shù)量
限制數(shù)據(jù)查詢結(jié)果數(shù)量語(yǔ)句: 不指定初始位置方式潜沦、指定初始位置方式萄涯。
(1) 不指定初始位置
- 顯示記錄數(shù)小于查詢結(jié)果
select *
from t_employee
where comm is null limit 2;
- 顯示記錄數(shù)大于查詢結(jié)果
select *
from t_employee
where comm is null limit 11;
(2) 指定初始位置
select *
from t_employee
where comm is null
order by miredate limit 0,5;
- 統(tǒng)計(jì)函數(shù)和分組數(shù)據(jù)記錄查詢
統(tǒng)計(jì)函數(shù):
- COUNT()函數(shù): 該統(tǒng)計(jì)函數(shù)實(shí)現(xiàn)統(tǒng)計(jì)表中記錄的條數(shù)。
- AVG()函數(shù): 該統(tǒng)計(jì)函數(shù)實(shí)現(xiàn)計(jì)算字段值的平均值唆鸡。
- SUM()函數(shù): 該統(tǒng)計(jì)函數(shù)實(shí)現(xiàn)計(jì)算字段值的總和窃判。
- MAX()函數(shù): 該統(tǒng)計(jì)函數(shù)實(shí)現(xiàn)查詢字段值的最大值。
- MIN()函數(shù): 該統(tǒng)計(jì)函數(shù)實(shí)現(xiàn)查詢字段值的最小值喇闸。
(1) 統(tǒng)計(jì)函數(shù) - 統(tǒng)計(jì)數(shù)據(jù)記錄條數(shù)
# 為count(*)設(shè)置別名為number
select count(*) number from t_employee;
(2) 統(tǒng)計(jì)計(jì)算平均值
select avg(comm) average from t_employee;
(3) 統(tǒng)計(jì)計(jì)算求和
select sum(sal) sumvalue from t_employee;
(4) 統(tǒng)計(jì)計(jì)算最大值和最小值
select max(sal) maxval, min(sal) minval from t_employee;
(5) 關(guān)于統(tǒng)計(jì)函數(shù)注意點(diǎn)
如果操作的表中沒(méi)有任何數(shù)據(jù)記錄袄琳，則COUNT()函數(shù)返回?cái)?shù)據(jù)0， 而其他函數(shù)則返回NULL燃乍。
(6) 分組數(shù)據(jù)查詢---簡(jiǎn)單分組查詢
select *
from t_employee
group by deptno;
(7) 分組數(shù)據(jù)查詢---實(shí)現(xiàn)統(tǒng)計(jì)功能分組查詢(GROUP_CONCAT)
GROUP_CONCAT函數(shù)實(shí)現(xiàn)顯示每個(gè)分組中的字段唆樊。
select deptno, group_concat(ename) enames
from t_employee
group by deptno;
(8) 分組數(shù)據(jù)查詢---實(shí)現(xiàn)多個(gè)字段分組查詢
select deptno, miredate, group_concat(ename) enames, count(ename)
from t_employee
group by deptno, miredate;
(9) 分組數(shù)據(jù)查詢---實(shí)現(xiàn)HAVING子句限定分組查詢
select deptno, avg(sal) average,group_concat(ename) enames, count(ename) number
from t_employee
group by deptno
having avg(sal)>2000;
