這里寫目錄標題

語言
查詢
- 基礎查詢
- - select 查詢串列 from 表名;
  - 查詢函式（呼叫函式，獲取回傳值）
  - 起別名
  - +的作用
  - distinct的使用（去重）
  - 查看表的結構
- 條件查詢
- - 按關系運算式篩選
  - 按邏輯運算式篩選
  - 模糊查詢
  - - like
    - in
    - between and
    - is null

語言

DQL(data query language)（資料查詢語言）
SQL(s=select)（選擇查詢語言）
DML(data manipulation language)(資料操作語言）
DDL(data define language)(資料定義語言)
TCL(transaction control language)(事務控制語言)

查詢

基礎查詢

select 查詢串列 from 表名;

特點：查詢的結果集是一個虛擬表（列印內容）

select后面跟的查詢串列，可以有多個部分組成，中間用逗號隔開：select 欄位1,欄位2,運算式 from 表;

執行順序
① from子句
② select子句

查詢串列可以是：欄位、運算式、常量、函式等
USE myemployees;

一、查詢常量
SELECT 100 ;

二、查詢運算式
SELECT 100%3;

三、查詢單個欄位
SELECT last_name FROM employees;

四、查詢多個欄位
SELECT last_name,email,employee_id FROM employees;

五、查詢所有欄位
SELECT * FROM employees;

查詢函式（呼叫函式，獲取回傳值）

SELECT DATABASE();
SELECT VERSION();
SELECT USER();

起別名

方式一：使用as關鍵字
SELECT USER() AS 用戶名;
SELECT USER() AS “用戶名”;
SELECT USER() AS ‘用戶名’;

SELECT last_name AS “姓名” FROM employees;

方式二：使用空格
SELECT USER() 用戶名;
SELECT USER() “用戶名”;
SELECT USER() ‘用戶名’;

SELECT last_name “姓名” FROM employees;

mysql> select last_name as 'xing ming' from employees;
+-------------+
| xing ming   |
+-------------+
| K_ing       |
| Kochhar     |
| De Haan     |
| Hunold      |

+的作用

-需求：查詢 first_name 和last_name 拼接成的全名，最終起別名為：姓名

方案1：使用+ pass×
SELECT first_name+last_name AS “姓名”
FROM employees;

方案2：使用concat拼接函式
SELECT CONCAT(first_name,last_name) AS “姓名”
FROM employees;

mysql> select concat(first_name,last_name) as 'xing ming' from employees;
+------------------+
| xing ming        |
+------------------+
| StevenK_ing      |
| NeenaKochhar     |
| LexDe Haan       |
| AlexanderHunold  |
| BruceErnst       |
| DavidAustin      |
| ValliPataballa   |
| DianaLorentz     |
| NancyGreenberg   |

distinct的使用（去重）

mysql> select department_id from employees;  #有重復
+---------------+
| department_id |
+---------------+
|          NULL |
|            10 |
|            20 |
|            20 |
|            30 |
|            30 |
|            30 |
|            30 |
|            30 |
|            30 |

distinct adj. 明顯的；獨特的；清楚的；有區別的
查詢員工涉及到的部門編號有哪些
SELECT DISTINCT department_id FROM employees;

mysql> select distinct department_id from employees; #無重復
+---------------+
| department_id |
+---------------+
|          NULL |
|            10 |
|            20 |
|            30 |
|            40 |
|            50 |
|            60 |
|            70 |
|            80 |
|            90 |
|           100 |
|           110 |
+---------------+
12 rows in set (0.00 sec)

查看表的結構

DESC employees;

列 columns
SHOW COLUMNS FROM employees;

條件查詢

select 查詢串列
from 表名
where 篩選條件;

執行順序：
①from子句
②where子句
③select子句

select last_name,first_name from employees where salary>20000;

按關系運算式篩選

關系運算子：> < >= <= = <> (或 != )(不等于)

mysql> select *                          #要查的東西
    -> from employees                    #表名
    -> where department_id <> 100;       #條件
+-------------+-------------+-------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name  | last_name   | email    | phone_number       | job_id     | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+-------------+-------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
|         100 | Steven      | K_ing       | SKING    | 515.123.4567       | AD_PRES    | 24000.00 |           NULL |       NULL |            90 | 1992-04-03 00:00:00 |
|         101 | Neena       | Kochhar     | NKOCHHAR | 515.123.4568       | AD_VP      | 17000.00 |           NULL |        100 |            90 | 1992-04-03 00:00:00 |
|         102 | Lex         | De Haan     | LDEHAAN  | 515.123.4569       | AD_VP      | 17000.00 |           NULL |        100 |            90 | 1992-04-03 00:00:00 |
|         103 | Alexander   | Hunold      | AHUNOLD  | 590.423.4567       | IT_PROG    |  9000.00 |           NULL |        102 |            60 | 1992-04-03 00:00:00 |
|         104 | Bruce       | Ernst       | BERNST   | 590.423.4568       | IT_PROG    |  6000.00 |           NULL |        103 |            60 | 1992-04-03 00:00:00 |
|         105 | David       | Austin      | DAUSTIN  | 590.423.4569       | IT_PROG    |  4800.00 |           NULL |        103 |            60 | 1998-03-03 00:00:00 |
|         106 | Valli       | Pataballa   | VPATABAL | 590.423.4560       | IT_PROG    |  4800.00 |           NULL |        103 |            60 | 1998-03-03 00:00:00 |
|         107 | Diana       | Lorentz     | DLORENTZ | 590.423.5567       | IT_PROG    |  4200.00 |           NULL |        103 |            60 | 1998-03-03 00:00:00 |
|         114 | Den         | Raphaely    | DRAPHEAL | 515.127.4561       | PU_MAN     | 11000.00 |           NULL |        100 |            30 | 2000-09-09 00:00:00 |
|         115 | Alexander   | Khoo        | AKHOO    | 515.127.4562       | PU_CLERK   |  3100.00 |           NULL |        114 |            30 | 2000-09-09 00:00:00 |
|         116 | Shelli      | Baida       | SBAIDA   | 515.127.4563       | PU_CLERK   |  2900.00 |           NULL |        114 |            30 | 2000-09-09 00:00:00 |
|         117 | Sigal       | Tobias      | STOBIAS  | 515.127.4564       | PU_CLERK   |  2800.00 |           NULL |        114 |            30 | 2000-09-09 00:00:00 |
|         118 | Guy         | Himuro      | GHIMURO  | 515.127.4565       | PU_CLERK   |  2600.00 |           NULL |

案例2：查詢工資<15000的姓名、工資

mysql> select last_name,salary  #要查的東西
    -> from employees           #表名
    -> where salary<15000;      #篩選條件
+-------------+----------+
| last_name   | salary   |
+-------------+----------+
| Hunold      |  9000.00 |
| Ernst       |  6000.00 |
| Austin      |  4800.00 |

按邏輯運算式篩選

邏輯運算子：and or not 也可以使用&& || !

案例：查詢獎金率>0.03 或者員工編號在60-110之間的員工資訊

mysql> select *
    -> from employees
    -> where commission_pct >0.03 or(employee_id >=60 and employee_id<=110);
+-------------+-------------+------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name  | last_name  | email    | phone_number       | job_id     | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+-------------+------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
|         100 | Steven      | K_ing      | SKING    | 515.123.4567       | AD_PRES    | 24000.00 |           NULL |       NULL |            90 | 1992-04-03 00:00:00 |
|         101 | Neena       | Kochhar    | NKOCHHAR | 515.123.4568       | AD_VP      | 17000.00 |           NULL |        100 |            90 | 1992-04-03 00:00:00 |
|         102 | Lex         | De Haan    | LDEHAAN  | 515.123.4569       | AD_VP      | 17000.00 |           NULL |        100 |            90 | 1992-04-03 00:00:00 |
|         103 | Alexander

模糊查詢

like

功能：一般和通配符搭配使用，對字符型資料進行部分匹配查詢
常見的通配符：
_ 任意單個字符
% 任意多個字符,支持0-多個
like/not like

案例1：查詢姓名中包含字符a的員工資訊

mysql> select *
    -> from employees
    -> where last_name like '%a%';
+-------------+-------------+------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name  | last_name  | email    | phone_number       | job_id     | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+-------------+------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
|         101 | Neena       | Kochhar    | NKOCHHAR | 515.123.4568       | AD_VP      | 17000.00 |           NULL |        100 |            90 | 1992-04-03 00:00:00 |
|         102 | Lex         | De Haan    | LDEHAAN  | 515.123.4569       | AD_VP      | 17000.00 |           NULL |        100 |            90 | 1992-04-03 00:00:00 |
|         105 | David       | Austin     | DAUSTIN  | 590.423.4569       | IT_PROG    |  4800.00 |           NULL |        103 |

案例2：查詢姓名中包含第三個字符為x的員工資訊

mysql> select *
    -> from employees
    -> where last_name like '__x%';
+-------------+------------+-----------+-------+--------------------+--------+---------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email | phone_number       | job_id | salary  | commission_pct | manager_id | department_id | hiredate            |
+-------------+------------+-----------+-------+--------------------+--------+---------+----------------+------------+---------------+---------------------+
|         170 | Tayler     | Fox       | TFOX  | 011.44.1343.729268 | SA_REP | 9600.00 |           0.20 |        148 |            80 | 2014-03-05 00:00:00 |
+-------------+------------+-----------+-------+--------------------+--------+---------+----------------+------------+---------------+---------------------+
1 row in set (0.00 sec)

案例3：查詢姓名中包含第二個字符為_的員工資訊

mysql> select *
    -> from employees
    -> where last_name like '_\_%';
+-------------+------------+-----------+-------+--------------------+---------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email | phone_number       | job_id  | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+------------+-----------+-------+--------------------+---------+----------+----------------+------------+---------------+---------------------+
|         100 | Steven     | K_ing     | SKING | 515.123.4567       | AD_PRES | 24000.00 |           NULL |       NULL |            90 | 1992-04-03 00:00:00 |
|         156 | Janette    | K_ing     | JKING | 011.44.1345.429268 | SA_REP  | 10000.00 |           0.35 |        146 |            80 | 2014-03-05 00:00:00 |
+-------------+------------+-----------+-------+--------------------+---------+----------+----------------+------------+---------------+---------------------+
2 rows in set (0.00 sec)

SELECT *
FROM employees
WHERE last_name LIKE '_$_%' ESCAPE '$'; #將￥定義為轉義字符

escape
vt. 逃避，避開，避免；被忘掉；被忽視
vi. 逃脫；避開；溜走；

in

查詢某欄位的值是否屬于指定的串列之內
a in(常量值1,常量值2,常量值3,…)
a not in(常量值1,常量值2,常量值3,…)

in/not in

案例1：查詢部門編號是30/50/90的員工名、部門編號

#方式1：
SELECT last_name,department_id
FROM employees
WHERE department_id IN(30,50,90);

#方式2：

SELECT last_name,department_id
FROM employees
WHERE department_id = 30
OR department_id = 50
OR department_id = 90;

案例2：查詢工種編號不是SH_CLERK或IT_PROG的員工資訊

#方式1：
SELECT *
FROM employees
WHERE job_id NOT IN('SH_CLERK','IT_PROG');

#方式2：
SELECT *
FROM employees
WHERE NOT(job_id ='SH_CLERK'
OR job_id = 'IT_PROG');

mysql> select *
    -> from employees
    -> where not(job_id ='SH_CLERK'
    -> or job_id ='IT_PROG');
+-------------+-------------+-------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name  | last_name   | email    | phone_number       | job_id     | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+-------------+-------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
|         100 | Steven      | K_ing       | SKING    | 515.123.4567       | AD_PRES    | 24000.00 |           NULL |       NULL |            90 | 1992-04-03 00:00:00 |
|         101 | Neena       | Kochhar     | NKOCHHAR | 515.123.4568       | AD_VP      | 17000.00 |           NULL |        100 |            90 | 1992-04-03 00:00:00 |
|         10

between and

判斷某個欄位的值是否介于xx之間
between and/not between and

案例1：查詢部門編號是30-90之間的部門編號、員工姓名

#方式1：
SELECT department_id,last_name
FROM employees
WHERE department_id BETWEEN 30 AND 90;

#方式2：

SELECT department_id,last_name
FROM employees
WHERE department_id>=30 AND department_id<=90;

案例2：查詢年薪不是100000-200000之間的員工姓名、工資、年薪

SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE salary*12*(1+IFNULL(commission_pct,0))<100000 OR salary*12*(1+IFNULL(commission_pct,0))>200000;



SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE salary*12*(1+IFNULL(commission_pct,0)) NOT BETWEEN 100000 AND 200000;

mysql> SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0))
    -> from employees
    -> WHERE salary*12*(1+IFNULL(commission_pct,0)) NOT BETWEEN 100000 AND 200000;
+-------------+----------+----------------------------------------+
| last_name   | salary   | salary*12*(1+IFNULL(commission_pct,0)) |
+-------------+----------+----------------------------------------+
| K_ing       | 24000.00 |                              288000.00 |
| Kochhar     | 17000.00 |                              204000.00 |
| De Haan     | 17000.00 |                              204000.00 |
| Ernst       |  6000.00 |                               72000.00 |
| Austin      |  4800.00 |                               57600.00 |

is null

案例1：查詢沒有獎金的員工資訊
SELECT *
FROM employees
WHERE commission_pct IS NULL;
案例2：查詢有獎金的員工資訊
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL;

= 只能判斷普通的內容

IS 只能判斷NULL值

<=> 安全等于，既能判斷普通內容，又能判斷NULL值

SELECT *
FROM employees
WHERE salary <=> 10000;

SELECT *
FROM employees
WHERE commission_pct <=> NULL;

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/255280.html

標籤：其他

上一篇：抖音資料采集教程，unidbg Ollvm 字串加密決議

下一篇：HR常用增強

mysql的sqlyog學習筆記（代碼用命令列顯示 ）