SELECT

  1. -- 选择数据库;
  2. USE <dadtabase>;
  3. # 查找数据；
  4. SELECT <column>,… FROM <table>;

子查询

基本用法

  1. SELECT name
  2. FROM products
  3. WHERE unit_price > (
  4.     SELECT unit_price
  5.     FROM products
  6.     WHERE product_id = 3
  7.     )

IN用法

  1. SELECT *
  2. FROM products
  3. WHERE product_id NOT In (
  4.     SELECT DISTINCT product_id
  5.     FROM order_items
  6. )

ALL

下面两种写法的结果相同，都是查询invoice_total大于所以client_id=3的invoice_total。

  1. SELECT *
  2. FROM invoices
  3. WHERE invoice_total > (
  4.     SELECT MAX(invoice_total)
  5.     FROM invoices
  6.     WHERE client_id = 3
  7. )
  9. SELECT *
  10. FROM invoices
  11. WHERE invoice_total > ALL (
  12.     SELECT  invoice_total
  13.     FROM invoices
  14.     WHERE client_id = 3
  15. )

ANY

表示查询的invoic_total大于一个或多个client_id=3的invoice_total。

  1. SELECT *
  2. FROM invoices
  3. WHERE invoice_total > ANY (
  4.     SELECT  invoice_total
  5.     FROM invoices
  6.     WHERE client_id = 3
  7. )

相关子查询

  1. SELECT *
  2. FROM employees e
  3. WHERE salary > (
  4.     SELECT AVG(salary)
  5.     FROM employees
  6.     WHERE e.office_id = office_id
  7.     )

EXISTS

EXISTS的作用就是“判断是否存在满足某种条件的记录”。如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。

  1. SELECT *
  2. FROM clients
  3. WHERE client_id in (
  4.     SELECT DISTINCT client_id
  5.     FROM invoices
  6.     )
  8. SELECT *
  9. FROM clients c
  10. WHERE EXISTS(
  11.     SELECT client_id
  12.     FROM invoices
  13.     WHERE client_id = c.client_id
  14.           )

INSERT

  1. INSERT INTO customers (
  2.         first_name,
  3.         last_name,
  4.         birth_date,
  5.         address,
  6.         city,
  7.         state)
  8. VALUES (
  9.         'John',
  10.         'Smith',
  11.         '1990-01-01',
  12.         'address',
  13.         'city',
  14.         'CA')

插入多行

  1. INSERT INTO shippers (name)
  2. VALUES ('rook'),
  3.       ('jfk'),
  4.       ('jack')

插入分层行

image.pngimage.png
在order_items和orders表中，有一个相同的列叫做order_id，如果希望在orders表中插入一条记录，同时在order_items表中插入一个相同order_id的记录，MySQL提供了一个函数LAST_INSERT_ID()，用于获取上一条插入语句的主键，可以通过这个函数实现插入相同order_id的记录。

  1. INSERT INTO orders (customer_id,order_date,status)
  2. VALUES (1,'2019-01-02',1);
  4. INSERT INTO order_items
  5. VALUES
  6.        (LAST_INSERT_ID(),1,1,2.95),
  7.        (LAST_INSERT_ID(),2,1,3.95);

创建表复制

SELECT * FROM orders为子查询语句。

  1. CREATE TABLE orders_archived AS
  2.     SELECT * FROM orders;
  4. USE sql_invoicing;
  5. CREATE TABLE invoices_archived AS
  6.     SELECT
  7.         invoice_id, number, invoice_total, payment_total, invoice_date, due_date, 
  8.     FROM invoices i
  9.     JOIN clients c on i.client_id = c.client_id
  10.     WHERE payment_date IS NOT NULL;

UPDATE

基本用法

  1. UPDATE invoices
  2. SET payment_total = 10,payment_date = '2019-03-01'
  3. WHERE invoice_id = 1

更新多行

  1. UPDATE invoices
  2. SET 
  3.     payment_total = 10,
  4.     payment_date = '2019-03-01'
  5. WHERE client_id IN (3,4)

UPDATE 中的子查询

  1. UPDATE invoices
  2. SET
  3.     payment_total = 10,
  4.     payment_date = '2019-03-01'
  5. WHERE client_id IN 
  6.       ( SELECT client_id
  7.         FROM clients
  8.         WHERE state IN ('CA','NY'))

DELETE

  1. DELETE FROM invoices
  2. WHERE client_id = (
  3.     SELECT clients.client_id
  4.     FROM clients
  5.     WHERE name = 'Myworks'
  6.     )

where

基本用法

  1. SELECT * FROM customers WHERE birth_date > '1990-01-01';

AND、OR 和 NOT

  1. -- 必须同时满足AND两边的条件；
  2. SELECT * FROM customers WHERE birth_date > '1990-01-01' AND point > 1000;
  3. -- 满足OR两边的一个条件即可；
  4. SELECT * FROM customers WHERE birth_date > '1990-01-01' OR point > 1000;
  5. -- 不满足条件；
  6. SELECT * FROM customers WHERE NOT birth_date > '1990-01-01';

IN

  1. -- 返回state为VA或FL或GA的数据；
  2. SELECT * FROM customers WHERE state in ('VA','FL','GA')
  3. -- 返回point在1000~3000的数据；
  4. SELECT * FROM customers WHERE points BETWEEN 1000 AND 3000

匹配字符串

  • LIKE
    1. -- % 匹配不确定长度的字符串，_ 匹配一个字符
    2. -- 'b%'查找以b开头的字符串，'%b'查找以b结束的字符串，'%b%'表示b的前后可以有任意字符串，不管在开头还是中间结尾；
    3. -- 'b_'表示b后只有一个字符。
    4. SELECT * FROM Customers WHERE last_name LIKE 'b%'

    REGEXP

    1. -- 'field'字符串中有field即可，无论在开头还是中间结尾；
    2. --  '^field'表示字符串必须以field开头；
    3. -- 'field$'表示字符串必须以field结尾；
    4. -- 'field|mac'查找多个单词，表示字符串中有field或mac其中一个即可；
    5. -- '[gm]e' 查找的字符串中有e，而且e的前面有g或m或gm；
    6. -- '[a-h]e' e的前面一个字符在a·h的范围内；
    7. SELECT * FROM customers WHERE last_name REGEXP 'field'

    IS NULL

    1. SELECT * FROM customers WHERE phone IS NULL
    2. -- 匹配NULL值

    OEDER BY

    1. SELECT * FROM customers ORDER BY  first_name
    2. -- 按某一列排序

    LIMIT

    1. SELECT * FROM customers LIMIT 3
    2. -- 筛选出前3行数据
    3. SELECT * FROM customers LIMIT 6, 3
    4. -- 6是偏移量，指的是跳过前6行数据，在返回3行数据

联结（连接）

INNER JOIN（内连接）

所谓内联结运算，一言以蔽之，就是“以A中的列作为桥梁，将B中满足同样条件的列汇集到同一结果之中”，其中的条件是A和B中有相同的一列。

本数据库连接
  1. SELECT p.product_id, name
  2. FROM order_items o
  3. -- 别名，o是order_items的别名，使用order_items的地方都可以用o；
  4. JOIN  products p
  5.     ON o.product_id = p.product_id
  6.     -- 指定两个表联结所用的列（联结列）；

跨数据库连接
  1. SELECT *
  2. FROM order_items oi
  3. JOIN sql_inventory.products p
  4. -- 跨数据库联结，联结sql_store和sql_inventory的products表；
  5.     on oi.product_id = p.product_id

自连接

在sql_hr库的employees表中，存在库员的id employee_id，和对应的管理人员id report_to，同时雇员和管理人员都在employee的信息这个表里，如果需要同时返回雇员的信息和管理人员的信息并且在同一行显示，可以使用自连接。
image.png

  1. USE sql_hr;
  3. SELECT
  4.     e.employee_id,
  5.     e.first_name,
  6.     m.first_name AS mmanager
  7. FROM  employees e
  8. JOIN employees m
  9.     on e.reports_to =m.employee_id

多表连接
  1. USE sql_store;
  3. SELECT
  4.     o.order_id,
  5.     o.order_date,
  6.     c.first_name,
  7.     c.last_name,
  8.     os.order_status_id
  9. FROM orders o
  10. JOIN customers c
  11.     on o.customer_id = c.customer_id
  12. JOIN order_statuses os
  13.     on o.status = os.order_status_id

复合连接
  1. SELECT *
  2. FROM order_items oi
  3. JOIN order_item_notes oin
  4.     on oi.order_id = oin.order_Id
  5.     AND oi.product_id = oin.product_id

OUTER JOIN（外连接）

外连接也是通过ON子句的联结键将两张表进行连接，并从两张表中同时选取相应的列的。基本的使用方法并没有什么不同，只是结果却有所不同。内联结只能选取出同时存在于两张表中的数据，而对于外联结来说，只要数据存在于某一张表当中，就能够读取出来。

  1. SELECT
  2.     c.customer_id,
  3.     c.first_name,
  4.     o.order_id
  5. FROM customers c
  6. RIGHT JOIN orders o on c.customer_id = o.customer_id
  7. ORDER BY c.customer_id

RIGHT 和 LEFT
外连接有两个关键字RIGHT和LEFT，它们的区别在于要把哪张表作为主表，使用LEFT时FROM子句中写在左侧的表是主表，使用RIGHT时右侧的表是主表。这里的左侧住的是FROM后面接的表名，右侧指的是JOIN后面接的表名。使用RIGHT时，在打印结果是右侧表的所有行都会打印出来，同样的，使用LEFT是，打印的结果包含左表的所有行。

USING

  1. SELECT
  2.     o.order_id,
  3.     c.first_name,
  4.     shippers.name AS shipper
  5. FROM orders o
  6. JOIN customers c
  7.     -- ON o.customer_id = c.customer_id
  8.     USING (customer_id)
  9. -- 当列名相等时，可以使用USING语句代替ON语句

自然连接

  1. SELECT
  2.     o.order_id,
  3.     c.first_name
  4. FROM orders o
  5. NATURAL JOIN customers c
  6. -- 这个语句会自动根据两个表相同的列进行连接

CROSS JOIN（交叉连接）

使用这个语句可以连接第一个表的每条记录和第二个表的每条记录，对两张表中的全部记录进行交叉组合，因此结果中的记录数通常是两张表中行数的乘积。
内联结是交叉联结的一部分，“内”也可以理解为“包含在交叉联结结果中的部分”。相反，外联结的“外”可以理解为“交叉联结结果之外的部分”。

  1. SELECT
  2.     c.first_name AS customer,
  3.     p.name AS product
  4. FROM customers c
  5. CROSS JOIN products p
  6. -- FROM customers c，products p

集合运算

集合运算，就是对满足同一规则的记录进行的加减等四则运算。通过集合运算，可以得到两张表中记录的集合或者公共记录的集合，又或者其中某张表中的记录的集合。像这样用来进行集合运算的运算符称为集合运算符。

  • 集合运算符会除去重复的记录。

    联合（UNIONS）

  1. SELECT
  2.     order_id,
  3.     order_date,
  4.        'Active' AS status
  5. FROM orders
  6. WHERE order_date >= '2019-01-01'
  7. UNION
  8. SELECT
  9.     order_id,
  10.     order_date,
  11.     'Archived' AS status
  12. FROM orders
  13. WHERE order_date < '2019-01-01'
  14. -- UNION是并集，进行记录加法运算，将两个语句的结果加起来显示；

image.png
注意：

  • 作为运算对象的记录的列数必须相同；
  • 作为运算对象的记录中列的类型必须一致；
  • 可以使用任何SELECT语句，但ORDER BY子句只能在最后使用一次。

ALL

保留重复行

  1. SELECT
  2.     order_id,
  3.     order_date,
  4.     'Active' AS status
  5. FROM orders
  6. WHERE order_date >= '2019-01-01'
  7. UNION ALL
  8. SELECT
  9.     order_id,
  10.     order_date,
  11.     'Archived' AS status
  12. FROM orders
  13. WHERE order_date < '2019-01-01'

表分组

GROUP BY

在GROUP BY子句中指定的列称为聚合键或者分组列。不使用GROUP BY子句时，是将表中的所有数据作为一组来对待的，而使用GROUP BY子句时，会将表中的数据分为多个组进行处理。会将查询结果按指定分组列划分。
GROUP BY子句的书写位置也有严格要求，一定要写在FROM语句之后（如果有WHERE子句的话需要写在WHERE子句之后）。

  1. SELECT
  2.        client_id,
  3.     SUM(invoice_total) AS total_sales
  4. FROM invoices
  5. GROUP BY client_id
  6. ORDER BY total_sales DESC;
  9. SELECT
  10.     state,
  11.     city,
  12.     SUM(invoice_total) AS total_sales
  13. FROM invoices
  14. JOIN clients c on invoices.client_id = c.client_id
  15. GROUP BY state,city
  16. ORDER BY total_sales DESC

image.pngimage.png

HAVING（为聚合结果指定条件）

为GROUP BY得到的表分组后的结果指定条件。WHERE子句只能指定记录（行）的条件，而不能用来指定组的条件。
HAVING子句必须写在GROUPBY子句之后，其在DBMS内部的执行顺序也排在GROUP BY子句之后。
HAVING的条件语句中的条件主体必须是SELECT的列（即SELECT后接的列名）。

  1. SELECT
  2.       client_id,
  3.     SUM(invoice_total) AS total_sales,
  4.     COUNT(*) AS number_of_invoices
  5. FROM invoices
  6. GROUP BY client_id
  7. HAVING total_sales > 500 AND number_of_invoices > 5;

ROLLUP

一次计算出不同聚合键组合的结果,对聚合键有效，如SUM(invoice_total)。

  1. SELECT
  2.        client_id,
  3.     SUM(invoice_total) AS total_sales
  4. FROM invoices
  5. GROUP BY client_id WITH ROLLUP ;

image.png

基本函数

数值函数

ROUND（对象数值，保留小数位数），ROUND函数用来进行四舍五入操作。
SELECT ROUND(5.73,1)image.png
TRUNCATE（对象数值，保留小数位）,TRUNCATE函数用于截断数字。
SELECT TRUNCATE(5.73,1)image.png
CEILILNG（对象数值），用于找到大于或等于这个数字的最小整数。
SELECT CEILING(5.7)image.png
FLOOR（对象数字），找到小于或等于这个数字的最大整数。
SELECT FLOOR(5.7)image.png
RAND()，返回一个在【0.1】区间的随机数。
SELECT RAND()image.png