目錄

一、本文前言

二、高效查詢方式

1）指定指字段進(jìn)行查看

2）對(duì)字段進(jìn)行去重查看

3）where條件查詢

4）and 和 or 進(jìn)行邏輯關(guān)系的增加

5）查詢?nèi)≈盗斜碇械臄?shù)據(jù)

6）between的引用?

7）like的查詢方式?

8）排序方式進(jìn)行查詢

三、運(yùn)用函數(shù)查詢?

1）數(shù)據(jù)庫(kù)中常用數(shù)學(xué)的函數(shù)

2）聚合函數(shù)

3）字符串函數(shù)

（1）去除字符?trim?

（2）?截取?substr?

（3）字段拼接?

（4）返回字符長(zhǎng)度 length

（5）替換 replace?

四、高級(jí)查詢語(yǔ)句?

1）GROUP BY（用于分組和匯總）?

（1）匯總統(tǒng)計(jì)

（2）匯總并對(duì)其指定字段（數(shù)字類）進(jìn)行累加

（3）匯總并對(duì)其指定字段（數(shù)字類）進(jìn)行累加，再進(jìn)行降序?

2）HAVING 過(guò)濾

3）別名設(shè)置查詢

語(yǔ)法格式：

（1）字段別名

?編輯

（2）表別名

4）表的自我連接?

（1）無(wú)重復(fù)數(shù)值排名?

（2）有重復(fù)數(shù)值排名?

5）子查詢語(yǔ)句?

子查詢運(yùn)用升級(jí)

6）EXISTS?

格式：

五、表連接查詢?

（1）?內(nèi)連接 inner join?

（2）左連接 left join

（3）右連接 right join?

六、view 視圖的運(yùn)用?

1）視圖的創(chuàng)建?

2） 視圖提供的后續(xù)便捷操作?

3）經(jīng)典定義問(wèn)題：視圖能否插入數(shù)據(jù)??

七、UNION 聯(lián)級(jí)?

1）UNION（合并后去重）

2）UNION ALL（合并后不去重）?

八、多種方式求表與表的交集值?

1）聯(lián)級(jí)視圖求交集值

2）內(nèi)連接求交集值??

（1）不去重求交集?

（2）去重求交集

3）使用左連接求交集值

4）使用右連接求交集?

5）使用子查詢的方式求交集值?

6）取非交集值?

九、case 條件選擇查詢語(yǔ)句

十、正則表達(dá)式的運(yùn)用

1）sql正則表達(dá)式的常見(jiàn)種類?

2）sql正則運(yùn)用

探究：空值（NULL）和無(wú)值（' '）的區(qū)別?

---

一、本文前言

數(shù)據(jù)庫(kù)是用來(lái)存儲(chǔ)數(shù)據(jù)，更新，查詢數(shù)據(jù)的工具，而查詢數(shù)據(jù)是一個(gè)數(shù)據(jù)庫(kù)最為核心的功能，數(shù)據(jù)庫(kù)是用來(lái)承載信息，而信息是用來(lái)分析和查看的。所以掌握更為精細(xì)化的查詢方式是很有必要的。本文將圍繞數(shù)據(jù)的高級(jí)查詢語(yǔ)句展開(kāi)

為了下面查詢語(yǔ)句的運(yùn)用，事先準(zhǔn)備了兩張表，后續(xù)也會(huì)根據(jù)查詢功能的運(yùn)用會(huì)對(duì)表進(jìn)行一些變動(dòng)，或則創(chuàng)建新表：?

CREATE TABLE info (id int(4) ,name char(4),height double 
) ;CREATE TABLE info2 (name char(4) hobby char(10) date char(10)
) ;

二、高效查詢方式

1）指定指字段進(jìn)行查看

select 字段1，字段2 from 表名;

2）對(duì)字段進(jìn)行去重查看

 SELECT DISTINCT "字段" FROM "表名";

3）where條件查詢

 SELECT "字段" FROM 表名" WHERE "條件";

4）and 和 or 進(jìn)行邏輯關(guān)系的增加

SELECT "字段" FROM "表名" WHERE "條件1"  AND "條件2";

 SELECT "字段" FROM "表名" WHERE "條件1"  OR "條件2";

5）查詢?nèi)≈盗斜碇械臄?shù)據(jù)

 SELECT "字段" FROM "表名" WHERE "字段" IN ('值1'， '值2'， ...);   #in,遍歷一個(gè)取值列表

6）between的引用?

 SELECT "字段" FROM "表名" WHERE "字段" BETWEEN '值1' AND '值2';

7）like的查詢方式?

like查詢通常會(huì)與通配符配合使用

%：百分號(hào)表示零個(gè)、一一個(gè)或多個(gè)字符

?_：劃線表示單個(gè)字符

select * from info2 where hobby like '%ing';
select * from info2 where name like '小_';

select * from info2 where name like '_剛';select * from info2 where hobby like '%ay%';

8）排序方式進(jìn)行查詢

order by，按關(guān)鍵字排序

注意：

• 一般對(duì)數(shù)值字段進(jìn)行排序
• 如果對(duì)字符類型的字段進(jìn)行排序，則會(huì)按首字母排序

 SELECT "字段" FROM "表名" [WHERE "條件"] ORDER BY "字段" [ASC|DESC] ;#ASC是按照升序進(jìn)行排序的，是默認(rèn)的排序方式。#DESC是按降序方式進(jìn)行排序。

  select * from info  order by height;select * from info  order by height asc;select * from info  order by height desc;

三、運(yùn)用函數(shù)查詢?

1）數(shù)據(jù)庫(kù)中常用數(shù)學(xué)的函數(shù)

數(shù)學(xué)函數(shù)	作用
abs(x)	返回x的絕對(duì)值
rand()	返回0到1的隨機(jī)數(shù)
mod(x, y)	返回x除以y以后的余數(shù)
power(x, y)	返回x的y次方
round(x)	返回離x最近的整數(shù)
round(x, y)	保留x的y位小數(shù)四舍五入后的值
sqrt(x)	返回x的平方根
truncate(x, y)	返回?cái)?shù)字x截?cái)酁閥位小數(shù)的值 #不四舍五入
ceil(x)	返回大于或等于x的最小整數(shù)
floor(x)	返回小于或等于x的最大整數(shù)
greatest(x1,x2,...)	返回集合中最大的值
least(x1,x2,...)	返回集合中最小的值

SELECT abs(-1),rand(), mod(5,3) ,power(2,3);

SELECT truncate(1.89,2);
SELECT truncate(1.89,1);

select ceil(1.76);
select floor(1.76);

select greatest(1,2,3,55,12,55,61);
select least(1,2,3,55,12,55,61);

2）聚合函數(shù)

聚合函數(shù)	含義
avg()	返回指定列的平均值
count()	返回指定列中非 NULL 值的個(gè)數(shù)
min()	返回指定列的最小值
max()	返回指定列的最大值
sum(字段)	返回指定列的所有值之和

select avg(height) from info;

select count(name) from info;
select count(*) from info;

select max(height) from info;
select min(height) from info;
select sum(height) from info;

3）字符串函數(shù)

字符串函數(shù)	作用
trim()	返回去除指定格式的值
concat(x,y)	將提供的參數(shù) x 和 y 拼接成一個(gè)字符串
substr(x,y)	獲取從字符串 x 中的第 y 個(gè)位置開(kāi)始的字符串，跟substring()函數(shù)作用相同
substr(x,y,z)	獲取從字符串 x 中的第 y 個(gè)位置開(kāi)始長(zhǎng)度為 z 的字符串
length(x)	返回字符串 x 的長(zhǎng)度
replace(x,y,z)	將字符串 z 替代字符串 x 中的字符串 y
upper(x)	將字符串 x 的所有字母變成大寫(xiě)字母
lower(x)	將字符串 x 的所有字母變成小寫(xiě)字母
left(x,y)	返回字符串 x 的前 y 個(gè)字符
right(x,y)	返回字符串 x 的后 y 個(gè)字符
repeat(x,y)	將字符串 x 重復(fù) y 次
space(x)	返回 x 個(gè)空格
strcmp(x,y)	比較 x 和 y，返回的值可以為-1,0,1
reverse(x)	將字符串 x 反轉(zhuǎn)

（1）去除字符?trim?

 SELECT TRIM ([ [位置] [要移除的字符串] FROM ] 字符串);?#[位置]：值可以為 LEADING (起頭), TRAILING (結(jié)尾), BOTH (起頭及結(jié)尾)。 #[要移除的字符串]：從字串的起頭、結(jié)尾，或起頭及結(jié)尾移除的字符串。缺省時(shí)為空格。

select trim(leading 's' from 'swmming' );
select trim(trailing 'g' from 'swmming' );
select trim(both 'l' from 'lol' );

（2）?截取?substr?

substr(x,y)    #截取x字符串 從第y個(gè)開(kāi)始，截取到末尾substr(x,y,z)  #截取x字符串 從第y個(gè)開(kāi)始截取 ，截取長(zhǎng)度為z

select substr(hobby,2) from info2;
select substr(hobby,3) from info2;

 select substr(hobby,2,5) from info2;select substr(hobby,4,6) from info2;

（3）字段拼接?

① concat(x,y)

select concat(name,height) from info;

② 使用 || 符號(hào)

 #將info表中，name字段值和height字段值拼接在一起。select name || height from info;

 #將info表中，name字段值和height字段值拼接在一起，且中間加空格。select name || ' ' || height from info;

（4）返回字符長(zhǎng)度 length

 select length(hobby) from info2;

（5）替換 replace?

select replace(name,'小','大') from info2;

四、高級(jí)查詢語(yǔ)句?

1）GROUP BY（用于分組和匯總）?

對(duì)GROUPBY后面的字段的查詢結(jié)果進(jìn)行匯總分組，通常是結(jié)合聚合函數(shù)一起使用的

• "GROUP BY"有一個(gè)原則，凡是在"GROUP BY"后面出現(xiàn)的字段，必須在SELECT 后面出現(xiàn)
• 凡是在SELECT 后面出現(xiàn)的、且未在聚合函數(shù)中出現(xiàn)的字段，必須出現(xiàn)在"GROUP BY"后面

（1）匯總統(tǒng)計(jì)

select name, count(name) from info group by name;

（2）匯總并對(duì)其指定字段（數(shù)字類）進(jìn)行累加

select name,sum(saving) from info3 group by name;

（3）匯總并對(duì)其指定字段（數(shù)字類）進(jìn)行累加，再進(jìn)行降序?

select name,sum(saving) from info3 group by name order by sum(saving) desc;

2）HAVING 過(guò)濾

• 用來(lái)過(guò)濾由"GROUP BY"語(yǔ)句返回的記錄集，通常與"GROUP BY"語(yǔ)句聯(lián)合使用
• HAVING語(yǔ)句的存在彌補(bǔ)了WHERE 關(guān)鍵字不能與聚合函數(shù)聯(lián)合使用的不足
• where只能對(duì)原表中的字段進(jìn)行篩選，不能對(duì)group by后的結(jié)果進(jìn)行篩選

 SELECT 字段1,SUM(字段2) FROM "表格名" GROUP BY 字段1 HAVING(函數(shù)條件) ;

select name,sum(saving) from info3 group by name having sum(saving)>1500;

3）別名設(shè)置查詢

語(yǔ)法格式：

 SELECT 字段1,字段2 AS 字段2的別名 from 表名;   #AS可以省略不寫(xiě)

（1）字段別名

select name,sum(saving) as total_saving  from info3  group by name having sum(saving)>1000;
select name,sum(saving) as total_saving  from info3  group by name having total_saving>1000;

（2）表別名

SELECT 表格別名.字段1 [AS] 字段別名  FROM 表格名 [AS] 表格別名; #AS可以省略不寫(xiě)

4）表的自我連接?

（1）無(wú)重復(fù)數(shù)值排名?

對(duì)下面的表進(jìn)行saving比較并且進(jìn)行排名通過(guò)表的自我連接進(jìn)行實(shí)現(xiàn)

?表的自我連接達(dá)到排名的原理分析及操作思路：

1. 以上面的數(shù)據(jù)表為例，假設(shè)共有四個(gè)人，他們手中的金額各不相同。我們已經(jīng)進(jìn)行表的自我連接
2. 使用count計(jì)數(shù)，只計(jì)數(shù)大于等于自身手上金額的人數(shù)，比如2000的小明，大于等于他的人數(shù)只有1個(gè)，就計(jì)數(shù)值也可以當(dāng)作他的排名
3. 再比如800的小紅，大于等于她的有4個(gè)人，就該計(jì)數(shù)值為4，同理可以證明她排名第四

select A.name,A.saving,count(A.saving) as rank from info3 as A,info3 as B where A.saving <=B.saving group by A.name,A.saving  order by rank asc;

（2）有重復(fù)數(shù)值排名?

新需求表：

select A.name,A.saving,count(A.saving) as rank from info3 as A,info3 as B where A.saving < B.saving or (A.name=B.nameme and A.saving=B.saving) group by A.name,A.saving  order by rank asc;

5）子查詢語(yǔ)句?

子查詢：連接表格，在WHERE 子句或HAVING 子句中插入另一個(gè)SQL語(yǔ)句

 SELECT "字段1" FROM "表格1" WHERE "字段2" [比較運(yùn)算符]     #外查詢(SELECT "字段1" FROM "表格2" WHERE "條件") ;              #內(nèi)查詢

普通的表數(shù)據(jù)連接：?

select * from info as A, info3 as B where A.name=B.name;

子查詢加入表連接 ：?

select * from info where name in(select name from info3 where saving > 1000);

子查詢運(yùn)用升級(jí)

求“北京地區(qū)”的所有saving值之和

select sum(saving) from info3   where name in (select name from info  where address='北京');

6）EXISTS?

• 用來(lái)測(cè)試內(nèi)查詢有沒(méi)有產(chǎn)生任何結(jié)果，類似布爾值是否為真。
• 如果內(nèi)查詢有結(jié)果的話，系統(tǒng)就會(huì)執(zhí)行外查詢中的SQL語(yǔ)句。若是沒(méi)有結(jié)果的話，那整個(gè)SQL語(yǔ)句就不會(huì)產(chǎn)生任何結(jié)果

格式：

 SELECT "字段1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "條件");

五、表連接查詢?

MYSQL數(shù)據(jù)庫(kù)中常用的表連接有三種：?

• inner join(內(nèi)連接)：只返回兩個(gè)表中聯(lián)結(jié)字段相等的行（有交集的值

• left join(左連接)：返回包括左表中的所有記錄和右表中聯(lián)結(jié)字段相等的記錄
• A? left? join? B? ： A為左表，B為右表

• right join(右連接)：返回包括右表中的所有記錄和左表中聯(lián)結(jié)字段相等的記錄
• A? right join? B：? A為左表 ，B為右表

（1）?內(nèi)連接 inner join?

select * from info A inner join info3 B on A.name = B.name;

其他實(shí)現(xiàn)內(nèi)連接的方式：

 select * from info A, info3 B where A.name=B.name;select * from info A inner join info3 B using(name);

（2）左連接 left join

select * from info A LEFT JOIN info3 B on A.name=B.name;

（3）右連接 right join?

select * from info3 A right join info B on A.name=B.name ;

六、view 視圖的運(yùn)用?

視圖：可以被當(dāng)作是虛擬表或存儲(chǔ)查詢

• 視圖跟表格的不同是，表格中有實(shí)際儲(chǔ)存數(shù)據(jù)記錄，而視圖是建立在表格之上的一個(gè)架構(gòu)，它本身并不實(shí)際儲(chǔ)存數(shù)據(jù)記錄
• 臨時(shí)表在用戶退出或同數(shù)據(jù)庫(kù)的連接斷開(kāi)后就自動(dòng)消失了，而視圖不會(huì)消失
• 視圖不含有數(shù)據(jù)，只存儲(chǔ)它的定義，它的用途一般可以簡(jiǎn)化復(fù)雜的查詢。 比如你要對(duì)幾個(gè)表進(jìn)行連接查詢，而且還要進(jìn)行統(tǒng)計(jì)排序等操作，寫(xiě)SQL語(yǔ)句會(huì)很麻煩的，用視圖將幾個(gè)表聯(lián)結(jié)起來(lái)，然后對(duì)這個(gè)視圖進(jìn)行查詢操作，就和對(duì)一個(gè)表查詢一樣，很方便

格式：

CREATE VIEW "視圖表名" AS "SELECT 語(yǔ)句";   #創(chuàng)建視圖表?
DROP VIEW "視圖表名";                     #刪除視圖表

1）視圖的創(chuàng)建?

創(chuàng)建需求：獨(dú)立創(chuàng)建一個(gè)視圖，用來(lái)統(tǒng)計(jì)info和info3 兩張表之間，先對(duì)name進(jìn)行address的匯總，再計(jì)算地區(qū)的saving之和 。要求體現(xiàn)出address? 和sum（sving） 兩個(gè)字段

create view v_address_saving as select A.address,sum(B.saving) total_saving from info A inner join info3 B on A.name=B.name group by address;

視圖創(chuàng)建的數(shù)據(jù)驗(yàn)證：

2） 視圖提供的后續(xù)便捷操作?

視圖的好處：創(chuàng)建視圖的過(guò)程雖然和高級(jí)查詢語(yǔ)句（通過(guò)兩個(gè)select語(yǔ)句進(jìn)行組合條件劃分生成派生表）一樣，過(guò)程是復(fù)雜的，但是如果該查詢操作是需要經(jīng)常使用的，創(chuàng)建視圖就很有必要，不僅能簡(jiǎn)化查詢過(guò)程，還能對(duì)該查詢進(jìn)行進(jìn)一步操作，而且十分簡(jiǎn)便

進(jìn)一步需求：?需要計(jì)算出蘇州和上海兩個(gè)地區(qū)的saving之和

select sum(total_saving) as suzhou_shanghai_saving from v_address_saving where address='蘇州'or address='上海';

3）經(jīng)典定義問(wèn)題：視圖能否插入數(shù)據(jù)??

視圖能否插入數(shù)據(jù)，要看情況而定：?

（1）如果視圖表是兩個(gè)表的連接查詢（比如視圖的A字段來(lái)自A表，B字段來(lái)自B表，數(shù)據(jù)是無(wú)法插入的）。因?yàn)楸斫Y(jié)構(gòu)和原表不一致。視圖中的字段是根據(jù)原表中某個(gè)字段，通過(guò)函數(shù)運(yùn)算，產(chǎn)生的新字段，而沒(méi)有真正能夠存儲(chǔ)的字段，所以該數(shù)據(jù)是無(wú)法插入的

（2）如果視圖表結(jié)構(gòu)與原表保持一致，數(shù)據(jù)是可以插入的，插入的數(shù)據(jù)是存儲(chǔ)在原表中，視圖所更新出的數(shù)據(jù)，其實(shí)是映射原表的數(shù)據(jù)

show create view? 分析創(chuàng)建視圖的過(guò)程：??

七、UNION 聯(lián)級(jí)?

UNION聯(lián)集：將兩個(gè)SQL語(yǔ)句的結(jié)果合并起來(lái)，兩個(gè)SQL語(yǔ)句所產(chǎn)生的字段需要是同樣的數(shù)據(jù)記錄種類

1）UNION（合并后去重）

生成結(jié)果的數(shù)據(jù)記錄值將沒(méi)有重復(fù)，且按照字段的順序進(jìn)行排序。#合并后去重

格式：[select 語(yǔ)句1] UNION [select 語(yǔ)句2];

select name from info union select name from  info3;

2）UNION ALL（合并后不去重）?

select name from info union all select name from  info3;

八、多種方式求表與表的交集值?

1）聯(lián)級(jí)視圖求交集值

create view v_info as select distinct name from info union all select distinct name from info3;?
select name,count(*) from v_info group by name;?select name from v_info group by name having count(*) >1;

2）內(nèi)連接求交集值??

（1）不去重求交集?

select A.name from info A inner join info3 B on A.name=B.name;?
select A.name from info A inner join info3 B using(name);

（2）去重求交集

select distinct A.name from info A inner join info3 B using(name);

3）使用左連接求交集值

select * from info A left join info3 B using(name);
select distinct A.name from info A left join info3 B using(name) where B.name is not null;

4）使用右連接求交集?

 #使用右連接查出store_name字段的交集值，之后去重select * from info A right join info3 B using(name);select distinct A.name from info A right join info3 B using(name) where A.name is not null;?或select distinct A.name from info A right join info3 B on A.name=B.name where A.name is not null;

5）使用子查詢的方式求交集值?

select distinct name from info where name in (select name from info3);

6）取非交集值?

（1）聯(lián)級(jí)方法中 count（*）<=1

? (2）左右內(nèi)連接? 將is not null? 改為? is? null

（3）子查詢 外連接查詢 not in （內(nèi)連接查詢）

九、case 條件選擇查詢語(yǔ)句

 SELECT CASE ("字段名")WHEN "條件1" THEN "結(jié)果1"WHEN "條件2" THEN "結(jié)果2"[ELSE "結(jié)果N"]ENDFROM "表名";# "條件"可以是一個(gè)數(shù)值或是公式。ELSE子句則并不是必須的。

mysql> select address,case address-> when '上海' then height-10-> when '北京' then height+5-> else height+10-> end-> "new_height",name-> from info;

十、正則表達(dá)式的運(yùn)用

1）sql正則表達(dá)式的常見(jiàn)種類?

正則符號(hào)	作用
^	匹配文本的開(kāi)始字符
$	匹配文本的結(jié)束字符
.	匹配任何單個(gè)字符
*	匹配零個(gè)或多個(gè)在它前面的字符
+	匹配前面的字符 1 次或多次
字符串	匹配包含指定的字符串
l	或，“|”前面的不成立時(shí)，就匹配后面的字符串
[...]	匹配字符集合中的任意一個(gè)字符
[^...]	匹配不在括號(hào)中的任何字符
{n}	匹配前面的字符串 n 次
{n,m}	匹配前面的字符串至少 n 次，至多m 次

2）sql正則運(yùn)用

格式：

select "字段" from "表名" where "字段" regexp '正則表達(dá)式';

探究：空值（NULL）和無(wú)值（' '）的區(qū)別?

無(wú)值的長(zhǎng)度為0，不占用空間；而NULL值的長(zhǎng)度是NULL，是占用空間的

IS NULL或者IS NOT NULL，是用來(lái)判斷字段是不是為NULL或者不是NULL，不能查出是不是無(wú)值的

無(wú)值的判斷使用=' '或者< >' '來(lái)處理。<>代表不等于

在通過(guò) count ()指定字段統(tǒng)計(jì)有多少行數(shù)時(shí)，如果遇到NULL值會(huì)自動(dòng)忽略掉，遇到無(wú)值會(huì)加入到記錄中進(jìn)行計(jì)算