第二天MySQL

2020/5/5

一、 DQL條件查詢

語法：（執行順序已標出）

SELECT

　　　　　查詢串列                      3

FORM

　　　　　表名                             1

WHERE

                 篩選條件; 　　　　　   2

分類：

1. 按條件運算式篩選

條件運算子：>  <  =  !=  <>  >=  <=

#查找員工月薪>12000的員工資訊

SELECT

               *

FROM

               employees

WHERE

               salary>12000;

2. 按邏輯運算式篩選

邏輯運算子：and  or  not

#查找員工編號在90至120的員工名、薪水以及員工編號

SELECT

       last_name,

       salary,

       employee_id

FROM

       employees

WHERE

               employee_id >= 90 AND employee_id <= 120;

3. 模糊查詢

關鍵字：

3.1   like

特點：一般和通配符搭配使用，可以判斷字符型或數值型，

通配符

%  可帶表任意多個字符（包括空字符）

_  只能代表任意一個字符

案例一：查找員工名中含有字符a的員工名和部門編號

 運行結果:

案例二：查找員工名中第二個字符為'a'，第五個字符為'o'的員工名、部門編號和薪水

運行結果;

注意：若想查找的字符本身就為特殊的字符如_或%，則需要通過對該字符轉義來實作，共有倆種方式

方式一：使用’\’來轉義

方式二: 使用ESCAPE關鍵字配合

如：#查找員工名第二個字符為'_'的員工名、月薪以及獎金率

SELECT

       last_name,

       salary,

       commission_pct

FROM

       employees

WHERE

       last_name LIKE '_\_%';

-----------------------------------------------------

SELECT

       last_name,salary,

       commission_pct

FROM

       employees

WHERE

        last_name

 LIKE

　　'_$_%' ESCAPE  '$';  (相當于把‘$‘附上轉義的功能)

3.2   between and

優點：

1. 能夠提高陳述句的簡潔度

2. 包含臨界值

3. 兩個臨界值不要調換順序

案例： 查找員工部門編號在90到120之間的員工名、部門編號、作業編號

運行結果：

以上兩種方法回傳的查詢結果一樣，兩種方式的表達意義完全等價！

3.3   in

特點：

1. 可以提高陳述句簡潔度
2. in 串列的值型別必須一致或兼容（可以隱式的轉換）
3. 不支持使用通配符表示的的模糊字符

#查找員工部門編號是'AD_VP'、'FI_MGR'、'FI_ACCOUNT'的員工名、薪水、部門編號以及部門編號

SELECT

       last_name,

       salary,

       job_id,

       department_id

FROM

       employees

WHERE

       job_id = 'AD_VP' OR job_id = 'FI_MGR' OR job_id = 'FI_ACCOUNT';

---------------------------------------------------------------------

SELECT

       last_name,

       salary,

       job_id,

       department_id

FROM

       employees

WHERE

       job_id IN ('AD_VP','FI_MGR','FI_ACCOUNT');

上面這兩種方式完全等價

3.4   is null 、is not null

特點：=或<>不能用于判斷null值，而is null 或is not null可以判斷null值（is只能用于判斷null，與null搭配，不可以判斷數值如12000）

注意：安全等于<=>即可以判斷NULL值，也可以判斷其他普通的數值，可讀性較差，

#查找獎金率為null的員工名、月薪以及獎金率

SELECT

       last_name,

       salary,

       commission_pct

FROM

       employees

WHERE

       commission_pct IS NULL;（可讀性很好！）

--------------------------------------------------------

SELECT

       last_name,

       salary,

       commission_pct

FROM

       employees

WHERE

       commission_pct <=> NULL;（可讀性較差）

以上兩種方式等價，回傳查詢結果一致！！！

#查找獎金率不為null的員工名、月薪、獎金率以及年薪

SELECT

       last_name,

       salary,

       commission_pct,

       salary * (1 + IFNULL(commission_pct, 0)) * 12 AS 年薪

FROM

       employees

WHERE

       commission_pct IS NOT NULL;

注意，有個IFNULL()函式，原型為IFNULL(expression, alt_value)，如果第一個引數的值expression為null，則替換它并回傳第二個引數alt_value，

二、 DOL排序查詢

特點：

1. ASC代表升序，DESC代表降序（若不寫，則默認為升序）

2. order by 字句中可以支持單個欄位，多個欄位，運算式、函式、別名

3. order by 字句一般放在查詢陳述句的最后面，LIMIT子句除外

語法：（執行順序已標出）

SELECT

              查詢串列        　　　　　3

FROM

              表名         　　　　　　  1

[WHERE

              篩選條件]   　　　　　　2

ORDER BY

              排序串列 ASC|DESC;     4

案例一: #查詢員工資訊，要求工資從高到低排序

SELECT * FROM employees ORDER BY salary DESC;

案例二：#查詢部門編號>=90的員工資訊，按入職時間的先后順序排序

SELECT

       *

FROM

       employees

WHERE

       department_id >= 90

ORDER BY

       hiredate DESC;

案例三：#按年薪的高低顯示員工的資訊和年薪【按運算式排序】

SELECT

       *,salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪

FROM

       employees

ORDER BY

              salary * 12 * (1 + IFNULL(commission_pct, 0))  DESC;

案例四：#按年薪的高低顯示員工的資訊和年薪【按別名排序】

SELECT

       *, salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪

FROM

       employees

ORDER BY

              年薪 DESC;

案例五：#按姓名的長度顯示員工的姓名和工資【按函式排序】

SELECT

       last_name,

       salary,

       LENGTH(last_name) 姓名長度

FROM

       employees

ORDER BY

              LENGTH(last_name) ASC;  （LENGTH（str）函式->回傳str的位元組長度）

案例六：#查詢員工資訊，要求先按工資排序，再按員工編號排序【按多個欄位排序】

SELECT

       *

FROM

       employees

ORDER BY

       salary DESC, employee_id DESC;

三、 常見函式介紹

呼叫方法：SELECT 函式名（實參串列）[from 表名];

分類：

1.單行函式

如：concat、length、ifnull、isnull等

2.分組函式

功能：做統計使用，又稱為統計函式、聚合函式、組函式

       單行函式分類：

1．  字符函式

# length函式->回傳當前引數的位元組數(若字符集使用的是gbk則一個漢字占2個位元組，若是utf8則為3個位元組)

SELECT LENGTH('jane'); ->4

SELECT LENGTH(last_name) AS 姓名長度 FROM employees;

#concat函式->拼接字符

SELECT  CONCAT(last_name,'_',first_name)  AS 姓名 FROM employees;

#upper、lower函式（類似于python中的str.lower()與str.upper()函式,UPPER(str)->將str轉換為大寫字母回傳，LOWER(str)-> 將str轉換為小寫字母回傳）

SELECT UPPER(last_name) FROM employees;

SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) AS 姓名 FROM employees;

 注意！！！索引是從1開始的

#substr,substring(截斷字串)

截取從指定索引處后面的所有字符

SELECT SUBSTR('abcefghijk',2) AS output;->'cefghijk'

截取從指定索引處指定”字符“長度的字符

SELECT SUBSTR('abcdefghijk',2,3) AS output;->'cde'

SELECT SUBSTR('呀呀呀',2,1) AS output;'呀'

#instr(回傳子串第一次出現的索引，如果找不到則回傳0)

SELECT INSTR(‘我去上學啦’,’上學啦’);->3

#trim(去”前后”的制定字符，若不指定則默認為去掉空格，注意，字串中間的內容不會去除)

SELECT TRIM('      lalalla      ')  AS output;  ->’lalalla’

SELECT TRIM( 'a'  FROM  'aaaaaaaabbbbbabbbaaabbbbaaaaa');-> bbbbbabbbaaabbbb

#lpad(在左邊用指定字符進行填充并且回傳對應長度的字符結果)

SELECT LPAD('aaabbb',10,'c');->'ccccaaabbb'

SELECT LPAD('aaabbb',4,'c');->'aaab'

#rpad(在右邊用指定字符進行填充并且回傳對應長度的字符結果)

SELECT RPAD('aaabbb',10,'c');->'aaabbbcccc'

SELECT RPAD('aaabbb',4,'c');->'aaab'

#replace(用指定字符值替換指定字符值)

REPLACE(str,from_str,to_str)（str->作用的字串，from_str->要被替換的字串，to_str->用來替換的字串）

SELECT REPLACE('哈哈哈，我不知道','我不知道','我知道')；->'哈哈哈，我知道'

2．  數字函式

#round(對傳入的引數四舍五入并回傳)

SELECT ROUND(8.9);->9

SELECT ROUND(-8.9);->-9

SELECT ROUND(1.567,2);->1.57(2代表保留小數點后2位)

#ceil(向上取整，回傳>=該引數的最小整數)

SELECT CEIL(1.002);->2

SELECT CEIL(1.00);->1

SELECT CEIL(-1.002);->-1

#floor(向下取整，回傳<=該引數的最小整數)

SELECT CEIL(1.002);->1

SELECT CEIL(1.00);->1

SELECT CEIL(-1.002);->-2

#truncate 截斷

TRUNCATE(X,D)->(X->要被處理的引數，D->截斷后的小樹點位數)

SELECT TRUNCATE(1.69999,1);->1.6

#mod（取余）

MOD(a,b): a-a/b*b(回傳的取余結果的正負值取決于引數a)

SELECT MOD(10,3);->1

SELECT MOD(10,-3);->1

SELECT MOD(-10,-3);->-1

SELECT MOD(-10,3);->-1

3．  日期函式

#now (回傳當前系統日期+時間)

SELECT NOW();->2020-05-05 20:20:15

#curdate(回傳當前系統日期,不包含時間)

SELECT CURDATE();->2020-05-05

#curtime(回傳當前系統時間,不包含日期)

SELECT CURTIME();->20:21:53

#year、month、day、hour、minute、second(用于獲取指定的部分，年、月、日、時、分、秒)

SELECT YEAR(NOW());->2020

SELECT MONTH(NOW());->5

SELECT DAY(NOW());->5

SELECT HOUR(NOW());->20

SELECT MINUTE(NOW());->25

SELECT SECOND(NOW());->4

#str_to_date（將日期格式的字符轉換成指定格式的日期）

相對應的格式符如下:

SELECT STR_TO_DATE('9-13 1999','%c-%d %Y');-> 1999-09-13

#date_format（將日期轉換成字符）

SELECT DATE_FORMAT('2018/6/6','%Y年%m月%d日');-> 2018年06月06日

4．  其他函式

#version(回傳當前mysql的版本)

SELECT VERSION();->5.7.25-log

#database（回傳當前打開的資料庫）

SELECT DATABASE();->myemployees

#user（回傳當前的用戶）

SELECT USER();->root@localhost

5．  流程控制函式

#if函式

IF(expr1,expr2,expr3)->(expr1:條件或邏輯運算式，若真則回傳expr2,假則回傳expr3)

SELECT IF(5>2,'大','小');->'大'

#case函式

使用一：（switch case的效果）

case 要判斷的欄位或運算式

when 常量1 then 要顯示的值1或陳述句1;

when 常量2 then 要顯示的值2或陳述句2;

…

else常量n then 要顯示的值n或陳述句n;

end

案例：查詢員工的工資，要求：

部門號=30，顯示的工資為1.1倍

部門號=40，顯示的工資為1.2倍

部門號=50，顯示的工資為1.3倍

SELECT

       salary 原始工資,

       department_id,

       CASE department_id

WHEN 30 THEN

       salary * 1.1

WHEN 40 THEN

       salary * 1.2

WHEN 50 THEN

       salary * 1.3

ELSE

       salary

END AS 新工資

FROM

       employees;

  使用二：（類似于多重if）

case

when 條件1 then 要顯示的值1陳述句1;

when 條件2 then 要顯示的值2或陳述句2;

…

else條件n then 要顯示的值n或陳述句n;

end

案例：查詢員工的工資，要求：

如果工資>20000,顯示級別A

如果工資>15000,顯示級別B

如果工資>10000,顯示級別C

否則，顯示級別D

SELECT

   salary 原始工資,

   CASE

WHEN salary > 20000 THEN

   'A'

WHEN salary > 15000 THEN

   'B'

WHEN salary > 10000 THEN

   'C'

ELSE

   'D'

END AS 獎金級別

FROM

   employees;

標籤：MySQL

上一篇：pb漢王人臉通

下一篇：pb11 webservice ds.dataobject賦值失敗