MySQL的查詢(xún)

（1）概覽SELECT語(yǔ)句的語(yǔ)法：

SELECT
    列1 as '別名1',
    列2 as '別名2',
    ....
FROM
    table_1（表1）
WHERE
    條件1 AND/OR 條件2
GROUP BY    子句
ORDER BY    子句
HIVING      子句
LIMT 子句

（2） SELECT語(yǔ)句各子句的描述：

SELECT  
    之后是逗號(hào)分隔列或星號(hào)(*)的列表，表示要返回所有列。
FROM    
    指定要查詢(xún)數(shù)據(jù)的表或視圖。
    JOIN    根據(jù)某些連接條件從其他表中獲取數(shù)據(jù)。
WHERE   
    過(guò)濾結(jié)果集中的行。
GROUP BY    將一組行組合成小分組，并對(duì)每個(gè)小分組應(yīng)用聚合函數(shù)。
HAVING      過(guò)濾器基于GROUP BY子句定義的小分組。
ORDER BY    指定用于排序的列的列表。
LIMIT       限制返回行的數(shù)量。

具體釋義：

圖片.png

一：select語(yǔ)句：

下載示例數(shù)據(jù)庫(kù)：http://www.yiibai.com/downloads/yiibaidb.zip

1.1：查詢(xún)名字，姓氏，職位，并設(shè)置別名

SELECT
    employees.lastName  AS '名',
    employees.firstName AS  '姓',   
    employees.jobTitle  AS  '職位'
FROM
    employees;

1.2：select 用戶(hù)case替換查詢(xún)結(jié)果中的數(shù)據(jù)

SELECT
    CASE
        WHEN employees.employeeNumber <= 1100   THEN    '老員工'
        WHEN employees.employeeNumber BETWEEN 1100 AND 1500 THEN    '新員工'
    ELSE    '實(shí)習(xí)生'
    END AS '員工編號(hào)',
  employees.lastName AS '姓',
    employees.firstName AS '名'
FROM
    employees;

可以和其他語(yǔ)句搭配，語(yǔ)法遵循：

case 
    when 條件1 then 執(zhí)行結(jié)果1 
    when 條件2 then 執(zhí)行結(jié)果2 
    else 執(zhí)行結(jié)果3 
    end

1. 3：計(jì)算列的值

SELECT
    CASE
        WHEN employees.employeeNumber <= 1100 THEN  '老員工'
        WHEN employees.employeeNumber BETWEEN 1100 AND 1500 THEN    '新員工'
    ELSE    '實(shí)習(xí)生'
    END  AS '員工編號(hào)',
employees.lastName AS '姓',
employees.firstName AS '名',
employees.officeCode as '舊辦公室編號(hào)',
employees.officeCode + 100 as   '新辦公室編號(hào)' #對(duì)字段進(jìn)行計(jì)算
FROM
    employees;

運(yùn)行結(jié)果:

image

1.4：消除結(jié)果中重復(fù)的行

SELECT
    DISTINCT    employees.officeCode  AS '辦公室'

FROM
    employees;

只能單獨(dú)使用，不能和其他查詢(xún)條件一起使用。

1.5：聚合函數(shù)

聚合函數(shù)對(duì)一列或者一組值進(jìn)行計(jì)算，然后返回單個(gè)值，其中除了COUNT函數(shù)外，其他函數(shù)都會(huì)忽略空值。

補(bǔ)充：

如果SELECT代碼中有GROUP BY語(yǔ)句，那么聚合函數(shù)會(huì)依次對(duì)每一分組都產(chǎn)生作用，
如果沒(méi)有那么只會(huì)生成一行作為結(jié)果。

示例：

(1)：無(wú)GROUP BY語(yǔ)句

image

(2)：：有GROUP BY語(yǔ)句

image

如果使用count(*)結(jié)果就是23，因?yàn)榘薔ULL

1.6：聚合函數(shù)的基本計(jì)算（SUM、AVG、MAX、MIN）

公式為：MAX / MIN ALL / DISTINCT 表達(dá)式:

SELECT
    COUNT(DISTINCT products.productCode) AS '去重商品數(shù)',
    COUNT(products.productCode) AS '商品數(shù)',
    MAX(products.buyPrice) AS '最大商品價(jià)格',
    MIN(products.buyPrice) AS '最小商品價(jià)格',
    AVG(products.buyPrice) AS '商品價(jià)格平均值'
FROM
products

運(yùn)行結(jié)果：

image

1.7：聚合函數(shù)計(jì)算標(biāo)準(zhǔn)差，方差

SELECT
    COUNT(DISTINCT products.productCode) AS '去重商品數(shù)',
    COUNT(products.productCode) AS '商品數(shù)',
    VARIANCE(products.buyPrice) AS '商品價(jià)格方差',
    STDDEV(products.buyPrice) AS '商品價(jià)格標(biāo)準(zhǔn)差',
    AVG(products.buyPrice) AS '商品價(jià)格平均值'
FROM
    products

variance:方差，
stddev：標(biāo)準(zhǔn)偏差
運(yùn)行結(jié)果：

image

二：FROM子句

select看的查詢(xún)表對(duì)象由from子句指定

2.1：全鏈接

基本語(yǔ)法："左表 ,右邊"

#查詢(xún)每個(gè)辦公室的員工名字和辦公室電話(huà)和地址
SELECT
    offices.officeCode AS '辦公室名字',
    employees.firstName AS '姓',
    employees.lastName AS '名',
    offices.phone AS '電話(huà)',
    offices.addressLine1 AS '地址1',
    offices.addressLine2 AS '地址2'
FROM
    employees,
    offices

2.2：交叉鏈接

基本語(yǔ)法：左表 cross join 右邊;


SELECT
     employees.employeeNumber,
FROM
    items cross join employees;

最終結(jié)果：

圖片.png

交叉連接和全連接都是一樣的，原理是：從第一張表中循環(huán)取出第一行，都去另外一張表的每一行進(jìn)行匹配，匹配的結(jié)果都保留，最終中間表會(huì)變得非常大，這樣的結(jié)果成為笛卡爾積。

可以看到第一行的Diane員工跟6個(gè)辦公室信息發(fā)生了匹配，也就是23行 *6行剛好是161條信息。

這樣的結(jié)果是沒(méi)有實(shí)際意義的。我們需要的是編號(hào)相等的部分。
emp.deptno = dept.deptno

設(shè)定emp.deptno = dept.deptno，又叫等值鏈接


#查詢(xún)每個(gè)辦公室的員工名字和辦公室電話(huà)和地址
SELECT
    offices.officeCode AS '辦公室名字',
    employees.firstName AS '姓',
    employees.lastName AS '名',
    offices.phone AS '電話(huà)',
    offices.addressLine1 AS '地址1',
    offices.addressLine2 AS '地址2'
FROM
    employees cross join offices
WHERE
    employees.officeCode = offices.officeCode
···

通過(guò)where條件篩選剔除了很多無(wú)效的值，雖然這樣能用，但是where條件效率沒(méi)有內(nèi)連接高。
運(yùn)行結(jié)果：

image

2.3：內(nèi)連接

基本語(yǔ)法：左表 + [inner + join]+ 右表 + on + 左表.字段 = 右表.字段;

用內(nèi)連接查詢(xún)每個(gè)辦公室的員工名字和辦公室電話(huà)和地址

···
SELECT
    offices.officeCode AS '辦公室名字',
    employees.firstName AS '姓',
    employees.lastName AS '名',
    offices.phone AS '電話(huà)',
    offices.addressLine1 AS '地址1',
    offices.addressLine2 AS '地址2'
FROM
    employees
INNER JOIN offices ON employees.officeCode = offices.officeCode
····

內(nèi)連接是將左邊的表的一行取出，然后和右表的每一行匹配，可以想象是生成了一個(gè)中間表，如果ON的條件是相等的，就將兩個(gè)表匹配行留下，不相等就都丟棄。跟上面的等值鏈接一個(gè)道理，歸根結(jié)底都是笛卡爾積，都是不保留字段值不相等的部分，但是效率高。

運(yùn)行結(jié)果：

image

2. 4：外連接

基本語(yǔ)法：
左查詢(xún)：
左表 + [left join ] + 右表 + on + 左表 . 字段 + 右表 . 字段；

left：以左表為主表里面的記錄全部取出，將右表拿去匹配，如果字段 ON相等則留下，如果匹配失敗則留NULL。
左表 + [ right join ] + 右表 + on + 左表 . 字段 + 右表 . 字段
right：與left相反。

（1）左查詢(xún)示例：

image

（2）右查詢(xún)示例：

image

三：where限制查詢(xún)：

3.1單一條件限制查詢(xún)

查詢(xún)銷(xiāo)售崗位的員工名字

SELECT 
    employees.lastName,employees.firstName,employees.jobTitle
FROM    
    employees
WHERE   
    jobTitle = 'sales Rep'

where 不能條件語(yǔ)句中不能使用別名，因?yàn)樗侵苯訌拇疟P(pán)中讀取，不要的丟棄，只有HIVING GROUP 才可以。
運(yùn)行結(jié)果：

image

3.2 多條件限制查詢(xún)

在語(yǔ)句中使用 ’AND‘或者 'OR'連接起來(lái)。

查詢(xún)銷(xiāo)售崗位并且辦公室代碼為1的員工。

SELECT 
    lastname, firstname, jobtitle
FROM
    employees
WHERE
    jobtitle = 'Sales Rep' AND officeCode = 1;

運(yùn)行結(jié)果：

image

3.3運(yùn)算符查詢(xún)

運(yùn)算符語(yǔ)法：

image

（1）查詢(xún)使用不等于(!=)運(yùn)算符來(lái)獲取不是銷(xiāo)售代表的其它所有員工：

SELECT 
    lastname, firstname, jobtitle
FROM
    employees
WHERE
    jobtitle != 'Sales Rep';

運(yùn)行結(jié)果：

image

（2）替換查詢(xún)結(jié)果中的數(shù)據(jù)

SELECT
    employeeNumber AS '員工編號(hào)',
    lastName AS '姓',
    firstName AS '名',
    officeCode AS '辦公室名稱(chēng)',
    CASE
WHEN employees.officeCode IS NULL THEN
    '未分配'
WHEN employees.officeCode = 1 THEN
    '第一間'
WHEN employees.officeCode = 2 THEN
    '第二間'
WHEN employees.officeCode = 3 THEN
    '第三間'
ELSE
    '第四間'
END AS '辦公室名稱(chēng)',
    jobTitle AS '職位'
FROM
    employees
WHERE
    jobTitle = 'sales Rep'

運(yùn)行結(jié)果：

image

擴(kuò)展查詢(xún)：

BETWEEN選擇在給定范圍值內(nèi)的值。

LIKE匹配基于模式匹配的值。

IN指定值是否匹配列表中的任何值。

IS NULL檢查該值是否為NULL。

Between查詢(xún)

（3）查找價(jià)格在90和100(含90和100)元范圍內(nèi)的商品

SELECT 
    productCode, productName, buyPrice
FROM
    products
WHERE
    buyPrice BETWEEN 90 AND 100;

運(yùn)行結(jié)果：

image

（4）查找購(gòu)買(mǎi)價(jià)格不在20到100(含20到100)之間的產(chǎn)品，可將BETWEEN運(yùn)算符與NOT運(yùn)算符組合使用，如下：


SELECT 
    productCode, productName, buyPrice
FROM
    products
WHERE
    buyPrice NOT BETWEEN 20 AND 100;

運(yùn)行結(jié)果：

image

（5）計(jì)算2013-01-01到2013-01-31日期之間的訂單。

當(dāng)使用 BETWEEN運(yùn)算符 計(jì)算 DATE數(shù)據(jù)類(lèi)型時(shí)，應(yīng)把計(jì)算的字符串類(lèi)型轉(zhuǎn)換成DATE類(lèi)型

select 
orders.orderNumber,orders.requiredDate,orders.`status`
FROM    
orders
WHERE
        requiredDate BETWEEN CAST('2013-01-01' AS DATE) AND  CAST('2013-01-31' AS DATE);

#值得注意的是在這里，運(yùn)行的時(shí)候報(bào)錯(cuò)，排查時(shí)發(fā)現(xiàn)原來(lái)status在MySQL中是關(guān)鍵字。

運(yùn)行結(jié)果：

image

四：MySQL的相似查詢(xún)'LIKE'

LIKE運(yùn)算符有兩種通配符：
"%" 匹配若干字符
“_” 匹配單個(gè)字符

4.1 搜索名字以字符a開(kāi)頭的員工信息

SELECT 
    employeeNumber, lastName, firstName
FROM
    employees
WHERE
    firstName LIKE 'a%';

運(yùn)行結(jié)果：

image

4.2 查找名字以arry結(jié)尾的員工

SELECT employees.employeeNumber,employees.lastName,employees.firstName
FROM
        employees
WHERE
    employees.firstName LIKE '_arry';

運(yùn)行結(jié)果：

image

4.3 通配符的轉(zhuǎn)義：

有時(shí)想要匹配的數(shù)據(jù)包含通配符，例如10%，_20等這樣的字符串時(shí)?？梢允褂?strong>“\”進(jìn)行轉(zhuǎn)義，如要轉(zhuǎn)移成特定數(shù)據(jù)類(lèi)型，可以使用ESCAPE語(yǔ)句。

不指定類(lèi)型：

select products.productCode,products.productName
from 
        products
WHERE
        productCode LIKE '%\_20%';

運(yùn)行結(jié)果：

image

4.4 使用ECASPE指定新的轉(zhuǎn)義字符"$"

查詢(xún)包含“_20”的商品編號(hào)：

select products.productCode,products.productName
from 
        products
WHERE
        productCode LIKE '%$_20%' ESCAPE '$';

運(yùn)行結(jié)果：

image

4.4 “IN”查詢(xún)

比OR代碼更簡(jiǎn)潔

SELECT  offices.officeCode,offices.city,offices.phone,offices.country
from 
    offices
WHERE
        country   IN ('USA','china');

運(yùn)行結(jié)果：

image

五：分組語(yǔ)句 GROUP BY

GROUP BY語(yǔ)句是根據(jù)表中字段進(jìn)行分組，如果字段的值相同，那么就聚合在一個(gè)組，不同的放在另一個(gè)組，也就是按值分組。與count，max，min,avg，sum等統(tǒng)計(jì)函數(shù)組合使用。

    cout()：統(tǒng)計(jì)分組后，每組的總記錄數(shù)；
    max()：統(tǒng)計(jì)每組中的最大值；
    min()：統(tǒng)計(jì)每組中的最小值；
    avg()：統(tǒng)計(jì)每組中的平均值；
    sum()：統(tǒng)計(jì)每組中的數(shù)據(jù)總和。

圖片.png

六：HIVING 語(yǔ)句

HIVING能做where的所有事情，并且能使用別名：

#查詢(xún)辦公室中人數(shù)大于4人的編號(hào)
SELECT
    employees.officeCode AS '辦公室編號(hào)',
    COUNT(DISTINCT employeeNumber) AS '人數(shù)'
FROM
    employees
GROUP BY
    officeCode
HAVING 人數(shù)> 4

運(yùn)行結(jié)果：

圖片.png

七：排序語(yǔ)句 ORDER BY

SELECT
    payments.customerNumber AS '顧客編號(hào)',
    COUNT(payments.customerNumber) AS '顧客購(gòu)買(mǎi)次數(shù)',
    avg(payments.amount) '顧客購(gòu)買(mǎi)單價(jià)',
    sum(payments.amount) '顧客購(gòu)買(mǎi)總數(shù)'
FROM
    payments
GROUP BY
    payments.customerNumber 
ORDER BY
    顧客購(gòu)買(mǎi)次數(shù) DESC,customerNumber DESC;

升序：ASC（ascend：上升），降序DESC（descend：下降）
運(yùn)行結(jié)果：

image

八：查詢(xún)范圍語(yǔ)句 LIMT

限制輸出的范圍，降低服務(wù)器的消耗。
（1）限制多少條結(jié)果：

-- 查詢(xún)表 student 中的全部記錄
select * from student;
-- 查詢(xún)表 student 中的 3 條記錄
select * from student limit 3;

運(yùn)行結(jié)果：

image

（2）限制范圍：

-- 查詢(xún)表 student 中的記錄
select * from student limit 0,2;
-- 查詢(xún)表 student 中的記錄
select * from student limit 2,2;

運(yùn)行結(jié)果：

image

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

MySQL的查詢(xún)語(yǔ)句詳解