mysql之DQL(Data Query Language)

文章目錄

• 普通查詢語句
• 常用字段
• • like(模糊查詢)
  • \\與escape(轉義字元)
  • between and
  • in
  • null
  • order by（排序查詢）
  • limit（限制輸出結果）
• 常用函數
• • 單行函數
  • • 字元函數
    • 數學函數
    • 日期函數
    • 流程控制函數
  • 分組函數
• 分組查詢
• 聯合查詢
• • 内連接配接
  • • 等值連接配接
    • 非等值連接配接
    • 自然連接配接
  • 外連接配接
  • • 左外連接配接
    • 右外連接配接
    • 全外連接配接
  • 交叉連接配接
• 聯合查詢(union)
• 子查詢
• • 标量子查詢
  • 列子查詢
  • 表子查詢

普通查詢語句

 其中字段名代表表中的列名。

常用字段

  常用字段經常出現在查詢中，包括like，distinct，between and，in，is null等。

like(模糊查詢)

 模糊查詢一般和通配符使用，通配符一般有2種，百分号(%)和下劃線(_)。

1. 百分号(%)代表任意多個字元，包括0個字元。
2. 下劃線(_)代表任意一個字元。

例子：

• 查找名字包含t的員工。

• 查找名字第二個字元為t的員工。

\與escape(轉義字元)

 在字元串中，某些序列具有特殊含義。這些序列均用反斜線(‘\’)開始，即所謂的轉義字元。MySQL識别下面的轉義序列

轉義字元的為\，如果想替換轉義字元可以使用escape關鍵字。

字元	含義
\0	ASCII 0(NUL)字元
\’	單引号
\"	雙引号
\b	倒退符
\n	換行符
\r	回車符
\t	tab字元
\\	反斜線字元
\%	‘%’字元
_	‘_’字元

例子：

• 查找名字第二個字元為_的員工。

同時也可以使用escape來替換轉義字元。

between and

 一般加在where中，表示變量在某範圍之間。

例如：

• 查找姓名在[20,30]歲的的員工。

• 等價于

in

 一般加在where中，表示變量在某個有限集合内。

例如：

• 查找姓名在集合(10,20,30)中的的員工。

• 等價于

null

1. 比較字元 ‘=’’>’ ‘<’ ‘<>’是不能用于查詢null。如果需要查詢空值（null），需使用is null 和is not null。
2. 空值（null）是不能參與任何計算，因為空值參與任何計算都為空。是以，當程式業務中存在計算的時候，需要特别注意。如果非要參與計算，需使用ifnull函數，将null轉換為”才能正常計算。
3. null是尚未定義的值，表示未知。而”确定為一個空字元串。是以未知的值，無法進行各種比較（大于，小于，等于），也不能用于計算（加減乘除）。

例如：

• 查找年齡非空的的員工。

order by（排序查詢）

 該字段可以将結果集中的查詢結果按照順序排序。

• order by 字段 [asc] 按照字段升序排序輸出。
• order by 字段 [desc] 按照字段降序排序輸出。

例如：

• 查找所有員工，按照年齡降序排序。

limit（限制輸出結果）

 該字段可以查詢結果集中的前幾條資訊，一般搭配order by使用。

例如：

• 查找所有員工，按照年齡降序排序，且顯示前10條資訊。

• 查找所有員工，按照年齡降序排序，且顯示前6-15條資訊。

• 查找所有員工，按照年齡降序排序，且顯示前6-最後1條資訊。

常用函數

 常用函數分為單行函數和分組函數。

單行函數

 最常見的普通函數，輸入+輸出。

字元函數

 對字元串操作常用函數。

函數	作用
lenth(str)	傳回字元串的長度
concat(str1,str2,…)	拼接字元串
upper(str)	将字元串所有字元大寫
lower(str)	将字元串所有字元小寫
substr(str,pos,len)	截取str從pos開始的len字元長度的字元串(索引從1開始)
instr(str1,str2)	傳回str2在str1中起始索引
trim(str)	去掉前後空格
trim(str1 from str)	去掉前後指定字元串str1
lpad(str,len,char)	用指定字元(char)左填充指定長度(len)
rpad(str,len,char)	用指定字元(char)右填充指定長度(len)
replace(str1,str2,str3)	在str1中将所有str2的子字元串替換成str3

數學函數

 常用對數值型資料進行操作的函數。

函數	作用
round(num,n)	四舍五入，保留n位
truncate(num,n)	截斷n位
ceil(num)	向上取整
Floor(num)	向下取整
Mod(a,b)	a%b

日期函數

 常用對數日期進行操作的函數。

• 字元串轉時間類型

  str_to_date(日期字元串,解析格式)

  例子：

  str_to_date(1997-10-23 10:10:10,%Y-%m-%d %H:%i:%s)

• 時間類型轉字元串

  date_format(日期，轉換出來的字元串格式)

  例子:

  date_format(date,%Y-%m-%d %H:%i:%s)

• 日期格式符

  

• 常見函數

函數	作用
now()	傳回目前時間類型
curdate()	傳回目前日期
curtime()	傳回目前時間
year(data)	輸入時間類型，傳回目前年份(int)
month(data)	輸入時間類型，傳回目前月份(int)
day(data)	輸入時間類型，傳回目前天數(int)
hour(data)	輸入時間類型，傳回目前小時數(int)
minute(data)	輸入時間類型，傳回目前分鐘數(int)
second(data)	輸入時間類型，傳回目前秒數(int)

流程控制函數

• if 函數

  if(condition, value_if_true, value_if_false)

參數	描述
condition	必須，判斷條件
value_if_true	可選，當條件為true值傳回的值
value_if_false	可選，當條件為false值傳回的值

• ifnull(expression, alt_value)

參數	描述
expression	必須，要測試的值
alt_value	必須，expression 表達式為 NULL 時傳回的值

• case

case
when 條件1 then 要顯示的内容1
when 條件2 then 要顯示的内容2
when 條件3 then 要顯示的内容3
...
else 要顯示的内容
end
           

例子:

select salary, 
case
when salary<5000 then '低'
when salary>=5000 and salary<30000 then '中'
else '高'
end
from employee
           

分組函數

 作為統計使用，将多行的結果聚合成一行。

函數	作用
sum(列名)	求和，忽略nul
avg()	求平均值，忽略null
min()	求最小值，忽略null
max()	求最大值，忽略null
count()	計算不為null的個數

• count(1)和count(*)的差別

myisam存儲引擎下，count()效率高

innode存儲引擎下，count()和count(1)一樣

分組查詢

 在進行分組統計時會用到分組查詢，比如按部門統計人數、按工種統計工資情況等。分組查詢一定會使用到分組函數，也一定會用到group by子句。

select 分組函數,列(必須出現在group by後面)
[where condition]
from 表
group by 列
having 分組後篩選條件
[order by]
[limit]
           

例子:

• 統計部門的人數大于2的部門

select count(*), department
from employee
group by department
having count(*)>2.
           

注意:

• 分組前篩選：用where,對原始表篩選。
• 分組後篩選：用having，對分組後表進行篩選。

聯合查詢

 在查詢時有時候我們需要對多張表的不同字段進行查詢，這時候就要進行聯合查詢。

舉例說明:

現有2張表，一張gril表，一張boy表。

• girl表

id	name	age	boyid
王昭君	19	2
1	花木蘭	18	1

• boy表

id	name	age
程咬金	13
1	蘭陵王	25
2	李白	22

内連接配接

 inner join子句将一個表中的行與其他表中的行進行比對，并允許從兩個表中查詢包含列的行記錄。

使用inner join子句注意點：

• 在from子句中指定主表。
• 表中要連接配接的主表應該出現在inner join子句中。理論上說，可以連接配接多個其他表。 但是，為了獲得更好的性能，應該限制要連接配接的表的數量(最好不要超過三個表)。
• 連接配接條件或連接配接謂詞。連接配接條件出現在inner join子句的on關鍵字之後。連接配接條件是将主表中的行與其他表中的行進行比對的規則。

INNER JOIN子句的文法如下：

select column_list
from 表1
inner join 表2 on join_condition1
inner join 表3 on join_condition2
...
[where where_conditions];
           

 其中join_condition可以是多個條件，比如 gril.boyid=boy.id and gril.age=boy.age。本質其實就是兩表做笛卡爾積，保留滿足條件的資訊。

根據join_condition的不同又分為等值連接配接、非等值連接配接、自然連接配接。

等值連接配接

 join_condition中是等值條件的，比如gril.boyid=boy.id。

例子:

• 查詢所有女生對應的男朋友所有資訊。

select g.name as gril_name,b.name as boy_name
from gril as g
inner join boy as b on g.boyid=b.id;
           

最終結果:

gril_name	boy_name
王昭君	李白
花木蘭	蘭陵王

非等值連接配接

 join_condition中是非等值條件的，比如gril.age<boy.age。

• 将所有男生年齡大于女生配對。

select g.name as gril_name,b.name as boy_name
from gril as g
inner join boy as b on g.age<b.age;
           

最終結果:

gril_name	boy_name
王昭君	蘭陵王
王昭君	李白
花木蘭	蘭陵王
花木蘭	李白

自然連接配接

 隻考慮那些在兩個關系模式中都出現的屬性上取值相同的元組對。它不能加連接配接條件，使用兩個表共有的字段id來“自然”地連結，同時會省略共有的字段。

• 查詢兩表自然連接配接的結果。

select g.name as gril_name,b.name as boy_name
from gril as g
natural join boy as b;
           

結果為空集，這句話其實等價于:

select g.name as gril_name,b.name as boy_name
from gril as g
inner join boy as b on g.name=b.name and g.age=b.age;
           

因為name和age兩表字段相同。

外連接配接

左外連接配接

left join=inner join+主表剩餘部分右邊補null

left join子句的文法如下：

select column_list
from 主表
left join 從表 on join_condition1
...
[where where_conditions];
           

例子:

select * from boy
left join gril on gril.id=boy.id;
           

結果:

右外連接配接

right join=inner join+主表剩餘部分左邊補null

left join子句的文法如下：

select column_list
from 主表
right join 從表 on join_condition1
...
[where where_conditions];
           

例子:

select * from boy
left join gril on gril.id=boy.id;
           

結果:

全外連接配接

MySQL目前不支援此種方式，可以用其他方式替代解決。

交叉連接配接

也就是笛卡爾積連接配接。

select column_list
from 主表
cross join 從表
...
[where where_conditions];
           

例子:

select *
from gril
cross join boy;
           

結果:

聯合查詢(union)

 如果我們需要将兩個select語句的結果作為一個整體顯示出來，我們就需要用到union或者union all關鍵字。union(或稱為聯合)的作用是将多個結果合并在一起顯示出來。

Union：對兩個結果集進行并集操作，不包括重複行，同時進行預設規則的排序。Union在進行表連結後會篩選掉重複的記錄，是以在表連結後會對所産生的結果集進行排序運算，删除重複的記錄再傳回結果。實際大部分應用中是不會産生重複的記錄，最常見的是過程表與曆史表Union。

Union All：對兩個結果集進行并集操作，包括重複行，不進行排序。

子查詢

 子查詢的意思是一個select語句查詢出來的結果集還可以作為另一個查詢的條件使用。

按結果集生成的行列不同分為以下3中子查詢:

• 标量子查詢（一行一列）
• 列子查詢（一列多行）
• 表子查詢（多行多列或一行多列）

标量子查詢

 一般用于where中，可以跟在<, >, =, <>号後面當數字使用。

例如:

列出所有工資大于平均工資的員工。

select *
from employee
where salary>(
	select avg(salary)
	from employee
)
           

列子查詢

 一般用于where中，可以跟在in, any, all後面使用。

操作符	含義
in/not in	等于清單中任意一個
some/any	和傳回的某個值比較
all	和列中所有元素比較

• not in 是 “<>all”的别名，用法相同。
• 語句in 與“=any”是相同的。

表子查詢

 一般接在from後面，代表從子表中再次篩選。