第3章基礎(chǔ)查詢語法

1. 數(shù)據(jù)查詢語句的基礎(chǔ)語法

1.1 基礎(chǔ)語法

在MySQL中 select 表示查詢，select可以單獨(dú)成句眨攘，是MySQL的方言
比如查詢系統(tǒng)時(shí)間： select now();
now() 表示當(dāng)前系統(tǒng)時(shí)間 MySQL特有的一個(gè)方法
在Oracle中系統(tǒng)時(shí)間： select sysdate from dual; 是Oracle的方言
dual表時(shí)Oracle特有的一張表搀矫，主要目的就是為了補(bǔ)充SQL語句中的from**
在Oracle的語法中玻褪，select之后必須要有from
MySQL中有DUAL關(guān)鍵字件缸，所以SELECT NOW()也可以寫作SELECT NOW() FROM DUAL;
但MySQL中的DUAL不是表執(zhí)行SELECT * FROM DUAL;會報(bào)錯(cuò)屏轰，而Oracle中DUAL是一張實(shí)際存在的表颊郎，執(zhí)行SELECT * FROM DUAL;會查詢一個(gè)叫做DUMMY的結(jié)果

標(biāo)準(zhǔn)語法：select...from...
語法格式：

從某張表中查詢特定列的數(shù)據(jù)

select 列1,列2,....列n from 表

從某張表中查詢其全部列的數(shù)據(jù)

select * from 表

開發(fā)中建議以查詢特定列的方式進(jìn)行查詢，前者的執(zhí)行效率更快

1.2 表達(dá)式和別名

表結(jié)構(gòu)

示例1：查詢員工編號霎苗，員工姓名姆吭，年薪（sal表示的是月薪）

select empno, ename, sal*12
from emp;

查詢結(jié)果

注意年薪的表頭顯示的是“sal*12”

可以使用as或者空格，為查詢結(jié)果的列起別名

select empno, ename, sal*12 income
from emp;

或

select empno, ename, sal*12  as income
from emp;

查詢結(jié)果

1.3 數(shù)值運(yùn)算中的null問題

當(dāng)數(shù)據(jù)運(yùn)算中出現(xiàn)null值時(shí)唁盏，結(jié)果一定是null
MySQL解決問題是用ifnull()函數(shù)
Oracle解決問題是用nvl()函數(shù)
ifnull(expr1,expr2)
nvl(expr1,expr2)
當(dāng)expr1的結(jié)果是null時(shí)内狸，使用expr2的結(jié)果
當(dāng)expr1的結(jié)果不是null時(shí)检眯，使用expr1的結(jié)果
示例2：查詢員工姓名和員工的月收入（月薪+獎(jiǎng)金）

select ename, sal+comm
from emp;

此時(shí)原始數(shù)據(jù)中如果comm中有null值，查詢結(jié)果不正確

查詢結(jié)果-沒有對null值進(jìn)行處理

select ename, sal, comm, sal+ifnull(comm,0)
from emp;

查詢結(jié)果-對null值進(jìn)行處理

1.4 重復(fù)數(shù)據(jù)的處理

使用distinct去掉查詢結(jié)果中的重復(fù)記錄
基礎(chǔ)語法

select distinct 列1, 列2, ... 列n from 表;

查詢列1昆淡，列2直至列n的數(shù)據(jù)锰瘸，并去掉其中重復(fù)的組合

2. 數(shù)據(jù)的條件篩選

2.1 語法格式：

select ... from...where 查詢條件
示例3：查詢所有在部門編號是1工作的員工記錄

SELECT * FROM emp WHERE deptno = 1;

2.2 運(yùn)算符

數(shù)值比較運(yùn)算：
deptno = 1 ( 在1號部門)
deptno <> 1 或 deptno != 1 (不在1號部門)
sal > 2000 (月薪高于2000)
sal >= 2000 (月薪大于等于2000)
sal < 2000 (月薪小于2000)
sal <= 2000 (月薪不高于2000 月薪小于等于2000)
文本比較運(yùn)算
ename = '趙四' (名字是趙四)
ename <> '趙四' 或 ename != '趙四' (名字不是趙四)

在windows中，比較英文文本時(shí)昂灵，MySQL會忽略大小寫

select * from emp where ename = 'tom'

select * from emp where ename = 'TOM'

的查詢結(jié)果是一樣的避凝，數(shù)據(jù)中Tom，tom眨补，TOM恕曲，tOm...等都會被查詢出
如果需要嚴(yán)格控制大小寫字母敏感，查詢語句需要在列前加binary

select * from emp where binary ename = 'TOM'

這樣只能查詢出TOM

時(shí)間比較運(yùn)算
hiredate = now() (入職日期是今天)
hiredate <> now() (入職日期不是今天)
hiredate < now() (入職日期在今天之前)
hiredate >= now() (入職日期在今天之后,含今天)

2.3 邏輯關(guān)系

2.3.1 AND 邏輯與

X AND Y 必須同時(shí)滿足條件X和條件Y

示例4：查詢在1號部門工作渤涌，且月薪不低于2500的員工信息

SELECT * FROM emp WHERE deptno = 1 AND sal >= 2500;

示例5：查詢在1號部門工作，且月薪不低于2500的保潔信息

SELECT * FROM emp WHERE deptno = 1 AND sal >= 2500 AND job = '保潔';

2.3.2 OR 邏輯或

X OR Y 滿足條件X或條件Y其一即可

示例6：查詢在1號部門或2號部門工作的員工信息

SELECT * FROM emp WHERE deptno = 1 OR deptno = 2

2.3.3 NOT 邏輯非

NOT(X) 不是條件X
主要使用在特殊的比較運(yùn)算符中

2.3.4 AND和OR的優(yōu)先級比較

AND語句的優(yōu)先級高于OR

SELECT * FROM emp
WHERE deptno = 3 OR sal < 2500 AND deptno = 1

先執(zhí)行sal < 2500 AND deptno = 1 ---- X
再執(zhí)行deptno = 3 OR X

如果使用括號控制優(yōu)先級

SELECT * FROM emp
WHERE (deptno = 3 OR sal < 2500) AND deptno = 1

先執(zhí)行deptno = 3 OR sal < 2500 ---- X
再執(zhí)行 X AND deptno = 1

SQL注入問題：利用了AND和OR優(yōu)先級破壞系統(tǒng)中的SQL語句邏輯

userinfo表

正常訪問邏輯把还，用戶輸入admin和123 正確賬號和密碼可以登錄

SELECT * FROM userinfo WHERE lname = 'admin' AND lpass = '123';

SQL注入通過輸入正確的賬號实蓬，和一個(gè)帶有or邏輯的SQL片段將原來邏輯破壞掉，進(jìn)行非法登錄
輸入的是賬號：admin 密碼：' OR 1=1 AND lname='admin
這個(gè)SQL語句就變成了

SELECT * FROM userinfo WHERE lname = 'admin' AND lpass = '' OR 1=1 AND lname='admin';

lname = 'admin' AND lpass = '' -- X 假查不出結(jié)果
1=1 AND lname='admin' -- Y 真查出admin的數(shù)據(jù)
X OR Y -- 真驗(yàn)證通過訪問了admin的賬號

3. 特殊的比較運(yùn)算符

3.1 between...and...

X between A and B 表示X在[A,B]之間
相當(dāng)于 X >= A and X <= B
一般情況下： B應(yīng)該大于等于A
如果B小于A語法也沒錯(cuò)誤吊履，邏輯上沖突安皱，不會由查詢結(jié)果

示例1：查詢月薪在[2000,2500]區(qū)間的員工信息

select *
from emp
where sal between 2000 and 2500;

示例2：查詢在20號部門入職日期在1994年的員工信息

select *
from emp
where hiredate between '1994-01-01' and '1994-12-31' and deptno = 20;

3.2 like (非常重要)

用于查詢數(shù)據(jù)時(shí)進(jìn)行模糊匹配(模糊查詢)，只適用于文本查詢
語法規(guī)則

相當(dāng)于ename = 'A'

ename like 'A'

ename中含有A字母艇炎。 %表示此處可能有[0,n]個(gè)字符

ename like '%A%'

ename中倒數(shù)第二個(gè)字符是A字母酌伊。 _表示此處有1個(gè)字符

ename like '%A_'

ename中含有兩個(gè)O字母

ename like '%O%O%'

-ename中含有字符% 使用\做為轉(zhuǎn)義字符
將'\%'看做一個(gè)%字符
MySQL中

select *
from emp
where ename like '%\%%';

Oracle中

select *
from emp
where ename like '%\%%' escape '\';

3.3 in

相當(dāng)于or
語法規(guī)則

deptno = 10

deptno in (10)

deptno是10或者20

deptno in (10,20)

相當(dāng)于

(deptno = 10 or deptno = 20)

示例3：查詢在10號部門或20部門工作，且職位是CLERK的員工信息

select *
from emp
where deptno in(10,20) and job = 'CLERK';

示例4：查詢員工SMITH和AMY的員工信息

select *
from emp
where ename in('SMITH','AMY');

示例5：查詢在'1992-01-15'或'1994-12-22'或'1994-07-05'入職的員工信息

select *
from emp
where hiredate in('1992-01-15','1994-12-22','1994-07-05');

如果in中的數(shù)據(jù)有null值缀踪，null值不會被查詢出

3.4 is null

專門針對null值進(jìn)行篩選

示例6：查詢所有獎(jiǎng)金是null的員工信息

select *
from emp
where comm is null

不能使用comm = null查詢居砖，這樣查詢沒有結(jié)果

3.5 not

not表示特殊比較運(yùn)算符邏輯取反
示例7：查詢月薪不在[2000,2500]區(qū)間的員工信息

select *
from emp
where sal not between 2000 and 2500;

示例8：查詢員工名字中不包含字母A的員工信息

select *
from emp
where ename not like '%A%';

示例9：查詢不在10號或20號部門工作的，職位是CLERK的員工信息

select *
from emp
where deptno not in(10,20) and job = 'CLERK';

示例10：查詢獎(jiǎng)金不是null的員工信息

select *
from emp
where comm is not null;

4. 排序

排序是對查詢結(jié)果進(jìn)行排序顯示

4.1 排序規(guī)則

排序規(guī)則

升序（由小到大）
數(shù)值 0驴娃，1奏候，2，3....
文本 A, B, C .... 漢字遵循字典順序
時(shí)間 1972-01-01 ... 1978-05-22
降序（由大到写匠ā）
數(shù)值 3蔗草，2，1疆柔，0 ...
文本 Z咒精，Y，X .... 漢字遵循字典順序的反向
時(shí)間 1978-05-22 ... 1972-01-01

4.2 語法規(guī)則

select...from...where...order by...

示例11：查詢所有的員工信息旷档，按照月薪升序排序模叙。
asc表示升序，排序默認(rèn)就是升序彬犯，asc可以省略向楼。

select * from emp order by sal;

select * from emp order by sal asc;

示例12：查詢所有的員工信息查吊，按照月薪降序排序。
desc表示降序

select * from emp order by sal desc;

4.3 多個(gè)列進(jìn)行排序

order by 列1 排序方式,  列2  排序方式,....  列n 排序方式;

在列1的排序基礎(chǔ)上湖蜕，進(jìn)行列2排序逻卖，.... 在之前所有排序基礎(chǔ)上進(jìn)行列n的排序

示例13 ：查詢所有員工信息，按照部門編號升序排序昭抒，按照月薪降序排序
先按照deptno進(jìn)行升序排序评也，在deptno排序的基礎(chǔ)上，再進(jìn)行sal的降序排序

select *
from emp
order by deptno asc, sal desc;

4.4 表達(dá)式或列別名

示例14：查詢20號部門員工姓名灭返，月薪盗迟，獎(jiǎng)金，月收入熙含，按照月收入的降序排序

select ename, sal, comm, sal+IFNULL(comm,0)
from emp
where deptno = 20
order by sal+IFNULL(comm,0) desc;

可以使用列別名

select ename, sal, comm, sal+IFNULL(comm,0) as income
from emp
where deptno = 20
order by income desc;

4.5 列的序號

按照查詢結(jié)果的第3列進(jìn)行升序排序罚缕，在排序基礎(chǔ)上以第2列進(jìn)行降序排序
實(shí)際：deptno升序后，sal降序

select ename,sal,deptno
from emp
order by 3 asc, 2 desc;

5. 查詢語句的編寫順序和執(zhí)行順序

編寫順序： select...from...where...order by...
執(zhí)行順序： from...where...select...order by...

第3章 基礎(chǔ)查詢語法