查询

查询步骤：

  1. 选择要查询的库 USE db
  2. 使用 SELECT 语句

基本语句：

  • SELECT 查询的列
  • FROM 查询的表
  • WHERE 筛选条件
  • ORDER BY 排序条件
1
2

USE sql_store;
SELECT customer_id, first_name FROM customers WHERE customer_id = 1 ORDER BY first_name;
customer_idfirst_name
1Babara

SELECT 子句

选择查询的列， * 表示查询所有列

1
2

USE sql_store
SELECT * FROM customers WHERE customer_id = 2
customer_idfirst_namelast_namebirth_datephoneaddresscitystatepoints
2InesBrushfield1986-04-13804-427-945614187 Commercial TrailHamptonVA947

SELECT 可以对列进行算数运算

1
2

USE sql_store
SELECT points, points * 10 + 100 FROM customers WHERE customer_id = 3
pointspoints * 10 + 100
296729770

列可以使用别名，使用 AS 关键字，有空格的列名使用引号包裹

1
2

USE sql_store
SELECT points, points * 0.9 AS "points discount" FROM customers WHERE customer_id = 3
pointspoints discount
29672670.3

使用 DISTINCT 关键字可以删除列中的重复项

1
2

USE sql_store
SELECT DISTINCT points, points * 0.9 AS "points discount" FROM customers WHERE customer_id = 5
pointspoints discount
36753307.5

WHERE 子句

条件筛选

1
2

USE sql_store
SELECT * FROM customers WHERE points > 3000
customer_idfirst_namelast_namebirth_datephoneaddresscitystatepoints
5ClemmieBetchley1973-11-07NULL5 Spohn CircleArlingtonTX3675
6ElkaTwiddell1991-09-04312-480-84987 Manley DriveChicagoIL3073

组合条件，支持 AND , OR , NOT ， 多个逻辑运算符时， AND 优先级更高，最好用括号显示表达优先级

1
2

USE sql_store
SELECT * FROM customers WHERE points > 3000 AND birth_date >= "1991-01-01"
customer_idfirst_namelast_namebirth_datephoneaddresscitystatepoints
6ElkaTwiddell1991-09-04312-480-84987 Manley DriveChicagoIL3073
1
2

USE sql_store
SELECT * FROM order_items WHERE order_id = 6 AND (quantity * unit_price) > 30
order_idproduct_idquantityunit_price
6148.65

IN 运算符判断是否在集合中

1
2

USE sql_store
SELECT * FROM customers WHERE state in ("VA", "FL")
customer_idfirst_namelast_namebirth_datephoneaddresscitystatepoints
2InesBrushfield1986-04-13804-427-945614187 Commercial TrailHamptonVA947
4AmburRoseburgh1974-04-14407-231-801730 Arapahoe TerraceOrlandoFL457
8ThacherNaseby1993-07-17941-527-3977538 Mosinee CenterSarasotaFL205

BETWEEN 运算符判断是否在闭区间中

1
2

USE sql_store
SELECT * FROM customers WHERE points BETWEEN 2000 AND 3000
customer_idfirst_namelast_namebirth_datephoneaddresscitystatepoints
1BabaraMacCaffrey1986-03-28781-932-97540 Sage TerraceWalthamMA2273
3FreddiBoagey1985-02-07719-724-7869251 Springs JunctionColorado SpringsCO2967

LIKE 运算符进行字符串模式匹配， % 匹配任意字符， _ 匹配单个任意字符

1
2

USE sql_store
SELECT * FROM customers WHERE last_name LIKE 'B%'
customer_idfirst_namelast_namebirth_datephoneaddresscitystatepoints
2InesBrushfield1986-04-13804-427-945614187 Commercial TrailHamptonVA947
3FreddiBoagey1985-02-07719-724-7869251 Springs JunctionColorado SpringsCO2967
5ClemmieBetchley1973-11-07NULL5 Spohn CircleArlingtonTX3675

REGEXP 运算符，进行正则匹配，匹配相关可以看 正则表达式

1
2

USE sql_store
SELECT * FROM customers WHERE last_name REGEXP "field"
customer_idfirst_namelast_namebirth_datephoneaddresscitystatepoints
2InesBrushfield1986-04-13804-427-945614187 Commercial TrailHamptonVA947

IS NULL 运算符，判断是否为 NULL

1
2

USE sql_store
SELECT * FROM customers WHERE phone IS NULL
customer_idfirst_namelast_namebirth_datephoneaddresscitystatepoints
5ClemmieBetchley1973-11-07NULL5 Spohn CircleArlingtonTX3675

ORDER BY 子句

ORDER BY 子句指定一列或多列排序，每列后面可跟 DESC 表示降序

1
2

USE sql_store
SELECT * FROM customers ORDER BY state DESC, last_name
customer_idfirst_namelast_namebirth_datephoneaddresscitystatepoints
2InesBrushfield1986-04-13804-427-945614187 Commercial TrailHamptonVA947
5ClemmieBetchley1973-11-07NULL5 Spohn CircleArlingtonTX3675
7IleneDowson1964-08-30615-641-475950 Lillian CrossingNashvilleTN1672
1BabaraMacCaffrey1986-03-28781-932-97540 Sage TerraceWalthamMA2273
6ElkaTwiddell1991-09-04312-480-84987 Manley DriveChicagoIL3073
10LevyMynett1969-10-13404-246-337068 Lawn AvenueAtlantaGA796
4AmburRoseburgh1974-04-14407-231-801730 Arapahoe TerraceOrlandoFL457
8ThacherNaseby1993-07-17941-527-3977538 Mosinee CenterSarasotaFL205
3FreddiBoagey1985-02-07719-724-7869251 Springs JunctionColorado SpringsCO2967
9RomolaRumgay1992-05-23559-181-37443520 Ohio TrailVisaliaCA1486

MySQL 支持 ORDER BY 使用不在 SELECT 里的列进行排序，支持别名列，列做算数运算

1
2

USE sql_store
SELECT *, unit_price * quantity as total_price FROM order_items WHERE order_id = 2 ORDER BY total_price DESC;
order_idproduct_idquantityunit_pricetotal_price
2129.1018.20
2441.666.64
2622.945.88

LIMIT 子句

LIMIT 子句限制返回的行数

单个数字表示从第一行开始显示 n 行

1
2

USE sql_store
SELECT customer_id, first_name, last_name FROM customers LIMIT 3
customer_idfirst_namelast_name
1BabaraMacCaffrey
2InesBrushfield
3FreddiBoagey

LIMIT 可以指定跳过多少行

1
2

USE sql_store
SELECT customer_id, first_name, last_name FROM customers LIMIT 6, 3
customer_idfirst_namelast_name
7IleneDowson
8ThacherNaseby
9RomolaRumgay

连接

连接相关的原理参考 数据库中的连接操作

两表内连接

JOIN 子句连接两个表， ON 表示连接条件

1
2

USE sql_store
SELECT order_id, first_name, last_name FROM orders JOIN customers ON orders.customer_id = customers.customer_id
order_idfirst_namelast_name
4InesBrushfield
7InesBrushfield
5ClemmieBetchley
8ClemmieBetchley
1ElkaTwiddell
10ElkaTwiddell
2IleneDowson
3ThacherNaseby
6LevyMynett
9LevyMynett

给列加上数据库名称前缀就能跨数据库连接

1
2

USE sql_store
SELECT order_id, oi.product_id FROM order_items oi JOIN sql_inventory.products p ON oi.product_id = p.product_id LIMIT 2
order_idproduct_id
21
61

自连接

表可以自连接，同一个表要取不同的别名

1
2

USE sql_hr
SELECT e.employee_id, e.reports_to, e.first_name , m.first_name as manager FROM employees e JOIN employees m ON e.reports_to = m.employee_id LIMIT 3
employee_idreports_tofirst_namemanager
3339137270D’arcyYovonnda
3785137270SayerYovonnda
4044837270MindyYovonnda

多表连接

使用多个 join 句子

1
2
3
4
5
6
7
8
9

USE sql_store;

SELECT order_id, o.customer_id, status, first_name, last_name
FROM orders o
JOIN customers c
     ON o.customer_id = c.customer_id
JOIN order_statuses os
     ON o.status = os.order_status_id
LIMIT 3
order_idcustomer_idstatusfirst_namelast_name
161ElkaTwiddell
272IleneDowson
381ThacherNaseby

复合连接条件

当表中有复合主键时，可以用复合连接条件确定具体的列

1
2
3
4
5
6
7

USE sql_store;

SELECT *
FROM order_items oi
JOIN order_item_notes oin
     ON oi.order_id = oin.order_id
     AND oi.product_id = oin.product_id

外连接

左外连接：除了满足连接条件的数据，还包括左表的所有数据右外连接：除了满足连接条件的数据，还包括右表的所有数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

USE sql_store;

SELECT
    c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
LEFT JOIN orders o
     ON c.customer_id = o.customer_id
ORDER BY c.customer_id
LIMIT 3
customer_idfirst_nameorder_id
1BabaraNULL
2Ines4
2Ines7

1 不满足连接条件，但是也在查询结果中

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

USE sql_store;

SELECT
    c.customer_id,
    c.first_name,
    o.order_id
FROM orders o
RIGHT JOIN customers c
     ON c.customer_id = o.customer_id
ORDER BY c.customer_id
LIMIT 3
customer_idfirst_nameorder_id
1BabaraNULL
2Ines4
2Ines7

右外连接，换了查表顺序得到相同结果

外连接同样可以多表连接

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

USE sql_store;

SELECT
    c.customer_id,
    c.first_name,
    o.order_id,
    s.name AS shipper
FROM customers c
LEFT JOIN orders o
     ON c.customer_id = o.customer_id
LEFT JOIN shippers s
     ON o.shipper_id = s.shipper_id
ORDER BY c.customer_id
LIMIT 10
customer_idfirst_nameorder_idshipper
1BabaraNULLNULL
2Ines4NULL
2Ines7Mraz, Renner and Nolan
3FreddiNULLNULL
4AmburNULLNULL
5Clemmie5Satterfield LLC
5Clemmie8NULL
6Elka1NULL
6Elka10Schinner-Predovic
7Ilene2Mraz, Renner and Nolan

外连接同样可以自连接

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

USE sql_hr;

SELECT
    e.employee_id,
    e.first_name,
    m.first_name as manager
FROM employees e
LEFT JOIN employees m
     ON e.reports_to = m.employee_id
LIMIT 3
employee_idfirst_namemanager
33391D’arcyYovonnda
37270YovonndaNULL
37851SayerYovonnda

USING 子句

如果 JOIN 使用的连接条件列名相同，可以使用 USING 代替 ON

1
2
3
4
5
6
7
8

USE sql_store;

SELECT order_id, first_name, last_name
FROM orders
JOIN customers
     -- ON orders.customer_id = customers.customer_id
     USING (customer_id)
LIMIT 3
order_idfirst_namelast_name
4InesBrushfield
7InesBrushfield
5ClemmieBetchley

自然连接

NATURE JOIN 不指定连接条件，由数据库自己决定

交叉连接

CROSS JOIN 两张表做笛卡尔积，适合列出所有的排列组合，比如有大中小的型号表和各种颜色的颜色表，查出型号和颜色的所有组合

联合

UNION 可以将多个 SELECT 查询结果合并到一张表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

USE sql_store;

SELECT
    c.customer_id,
    c.first_name,
    c.points,
    "Bronze" as type
FROM customers c
WHERE c.points < 2000

UNION

SELECT
    c.customer_id,
    c.first_name,
    c.points,
    "Silver" as type
FROM customers c
WHERE c.points >= 2000 AND c.points < 3000

UNION

SELECT
    c.customer_id,
    c.first_name,
    c.points,
    "Gold" as type
FROM customers c
WHERE c.points >= 3000

ORDER BY first_name
customer_idfirst_namepointstype
4Ambur457Bronze
1Babara2273Silver
5Clemmie3675Gold
6Elka3073Gold
3Freddi2967Silver
7Ilene1672Bronze
2Ines947Bronze
10Levy796Bronze
9Romola1486Bronze
8Thacher205Bronze

插入

插入单行

使用 INSERT INTO 语句插入单行

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

USE sql_store;

INSERT INTO customers(
       first_name,
       last_name,
       birth_date,
       address,
       city,
       state
)

VALUES (
       'xiao',
       'ming',
       '1990-01-01',
       'address',
       'Los',
       'CA'
)

插入多行

VALUES 后跟多个括号

1
2
3
4
5
6
7
8

USE sql_store;

INSERT INTO shippers
    (name)
VALUES
    ("name1"),
    ("name2"),
    ("name3")

多表插入

使用 LAST_INSERT_ID() 函数可以获取最后自增的 ID

1
2
3
4
5
6
7
8
9

USE sql_store;

INSERT INTO orders (customer_id, order_date, status)
VALUES (1, "2010-01-01", 1);

INSERT INTO order_items
VALUES
    (LAST_INSERT_ID(), 1, 1, 3.55),
    (LAST_INSERT_ID(), 2, 1, 5.55)

复制表

使用 CREATE TABLE name AS

1
2
3
4

USE sql_store;

CREATE TABLE orders_archive AS
SELECT * from orders

第二个 SELECT 称为子查询，也可以用在 INSERT 语句中，将查询到达数据插入 orders_archive 中

1
2
3
4
5

use sql_store;

INSERT INTO orders_archive
SELECT * FROM orders
WHERE order_date < '2019-01-01'

更新

使用 UPDATE 语句更新一行或多行数据

1
2
3
4
5

USE sql_invoicing;

UPDATE invoices
SET payment_total = 10, payment_date = '2020-02-02'
WHERE invoice_id = 1

多行数据时使用 WHERE 语句选出多行即可，比如 WHERE invoice_id IN (3, 4, 5)

同样可以用 SELECT 作为子查询条件

1
2
3
4
5
6
7
8
9

USE sql_invoicing;

UPDATE invoices
SET payment_total = 10, payment_date = '2020-02-02'
WHERE client_id IN (
SELECT client_id
FROM clients
WHERE name = 'Myworks'
)

删除

使用 DELETE FROM 语句删除表中的数据，用 WHERE 选择要删除的行

1
2
3
4

USE sql_invoicing;

DELETE FROM invoices
WHERE invoice_id = 1

同样可以使用子查询

1
2
3
4
5
6
7
8

USE sql_invoicing;

DELETE FROM invoices
WHERE client_id IN (
SELECT client_id
FROM clients
WHERE name = 'Myworks'
)

聚合函数

聚合函数可以对查询的数据进行计算并产生一个值，比如 MAX() MIN() AVG() SUM() COUNT() 等，只会计算非空的数据

1
2
3
4
5
6
7
8
9

USE sql_invoicing;

SELECT
    MIN(invoice_total) AS MIN,
    MAX(invoice_total) AS MAX,
    AVG(invoice_total) AS AVG,
    SUM(invoice_total) AS SUM,
    COUNT(invoice_total) AS COUNT
FROM invoices
MINMAXAVGSUMCOUNT
101.79189.12152.3882352590.6017

GROUP BY

GROUP BY 可以将数据安装指定列分组，分组后每一组进行聚合

1
2
3
4
5
6
7
8

USE sql_invoicing;

SELECT
    client_id,
    SUM(invoice_total) AS total
FROM invoices
GROUP BY client_id
ORDER BY total DESC
client_idtotal
5980.02
1802.89
3705.90
2101.79

也可以多列分组，结果是每一列的组合

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

USE sql_invoicing;

SELECT
    p.date,
    pm.name,
    SUM(p.amount) as total_payments
FROM payments p
JOIN payment_methods pm
     ON p.payment_method = pm.payment_method_id
GROUP BY p.date, pm.name
ORDER BY p.date
datenametotal_payments
2019-01-03Credit Card74.55
2019-01-08Cash10.00
2019-01-08Credit Card32.77
2019-01-11Credit Card0.03
2019-01-15Credit Card148.41
2019-01-26Credit Card87.44
2019-02-12Credit Card8.18

HAVING 子句

HAVING 子句在 GROUP BY 分组后进行筛选

1
2
3
4
5
6
7
8
9

USE sql_invoicing

SELECT
    client_id,
    SUM(invoice_total) AS total
FROM invoices
GROUP BY client_id
HAVING total > 500
ORDER BY total DESC
client_idtotal
5980.02
1802.89
3705.90

WITH ROLLUP

增加一行，对分组的数据做一次总计，只有 Mysql 有

1
2
3
4
5
6
7
8
9

USE sql_invoicing

SELECT pm.name,
       SUM(p.amount) AS total_amount
FROM payments p
JOIN payment_methods pm
     ON p.payment_method = pm.payment_method_id
GROUP BY pm.name WITH ROLLUP
ORDER BY total_amount
nametotal_amount
Cash10.00
Credit Card351.38
NULL361.38

复杂查询

子查询返回值用于比较

1
2
3
4
5
6
7
8
9

USE sql_hr;
-- 超过平均薪水的员工
SELECT salary
FROM employees
WHERE salary > (
      SELECT AVG(salary)
      FROM employees
)
LIMIT 3
salary
98926
94860
110150

子查询返回一列用 IN 判断

1
2
3
4
5
6
7
8

USE sql_invoicing;

SELECT client_id
FROM clients
WHERE client_id NOT IN (
      SELECT DISTINCT client_id
      FROM invoices
)
client_id
4

子查询返回一列值用 ALL 判断

1
2
3
4
5
6
7
8
9

USE sql_invoicing;

SELECT invoice_id, invoice_total
FROM invoices
WHERE invoice_total > ALL (
      SELECT invoice_total
      FROM invoices
      WHERE client_id = 3
)
invoice_idinvoice_total
2175.32
5169.36
8189.12
9172.17
18180.17

上面的查询可以使用 MAX() 改写

类似还有 ANY 和 SOME 关键字，比如 = ANY(1, 2, 3) 表示等于 1,2,3 中的任意一个

相关子查询

子查询用到了父查询的列信息

1
2
3
4
5
6
7
8
9

USE sql_invoicing;

SELECT invoice_id
FROM invoices as i
WHERE invoice_total > (
      SELECT AVG(invoice_total)
      FROM invoices
      WHERE client_id = i.client_id
)
invoice_id
2
4
5
8
9
15
16
18

EXIST 运算符

EXIST 判断是否存在

1
2
3
4
5
6
7
8
9

USE sql_invoicing;

SELECT c.client_id, c.name
FROM clients c
WHERE EXISTS (
      SELECT client_id
      FROM invoices
      WHERE client_id = c.client_id
)
client_idname
1Vinte
2Myworks
3Yadel
5Topiclounge

SELECT 中的子查询

子查询也可以用在 SELECT 列中

1
2
3
4
5
6
7
8
9

USE sql_invoicing;

SELECT
    client_id,
    c.name,
    (SELECT SUM(invoice_total) FROM invoices WHERE client_id = c.client_id) AS total,
    (SELECT AVG(invoice_total) FROM invoices) AS avger,
    (SELECT total - avger) AS diff
FROM clients c
client_idnametotalavgerdiff
1Vinte802.89152.388235650.501765
2Myworks101.79152.388235-50.598235
3Yadel705.90152.388235553.511765
4KwideoNULL152.388235NULL
5Topiclounge980.02152.388235827.631765

FROM 中的子查询

子查询可以用在 FROM 中，当成一个虚拟的表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

USE sql_invoicing;

SELECT client_id, name, diff
FROM (
SELECT
    client_id,
    c.name,
    (SELECT SUM(invoice_total) FROM invoices WHERE client_id = c.client_id) AS total,
    (SELECT AVG(invoice_total) FROM invoices) AS avger,
    (SELECT total - avger) AS diff
FROM clients c
) AS summary
WHERE diff IS NOT NULL
client_idnamediff
1Vinte650.501765
2Myworks-50.598235
3Yadel553.511765
5Topiclounge827.631765