目录：

3 使用MySQL

使用数据库：

1
> USE crashcourse;

返回可用的数据库：

1
> SHOW DATABASES;

返回数据库内的表：

1
> SHOW TABLES;

显示表中的列：

1
> SHOW COLUMNS FROM customers

交接 SHOW 命令，可以使用:

1
> HELP SHOW;

4 检索数据

检索不同的行

1
2
SELECT DISTINCT vend_id
FROM products;

DISTINCT 应用所有的列，不能不分指定。

限制结果

使用 LIMIT 限定输出的结果数量：

1
2
3
SELECT prod_name
FROM products
LIMIT 5, 5;

第一个数为开始的位置，第二个数为检索的行数。

完全限定表名

1
2
SELECT products.prod_name
FROM products;


5 排序检索数据

子句：SQL 语句由子句构成，包括一个关键字和所提供的的数据。子句如 SELECT 子句，FROM 子句。

可以使用 ORDER BY 子句取一个或多个列名字,对输出进行排序。

SQL 的输出结果默认是升序 ASC，可以使用 DESC进行降序输出。

一个例子：

1
2
3
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC, prod_name;

对 prod_price 降序，对 prod_name 升序。

ORDER BY 位于 FROM 之后，LIMIT 之前。


6 过滤数据

数据库一般包含大量数据，使用时很少需要检索表中所有行，通常检索所需数据需要指定过滤条件。

WHERE子句操作符

WHERE 子句的操作符包括：

1
2
3
4
5
6
- =
- <>
- != 
- <
- <=
- BETWEEN

空值检查，在建表时,表设计人员可以指定其中的列是否可以不包含。在一个列不包含值时,称其为包含空值 NULL。

可以使用 IS NULL 子句来判断：

1
2
3
SELECT prod_name
FROM products
WHERE prod_price IS NULL;

在过滤不具有特定值的行时，NULL 不会返回，必须指定。


7 数据过滤

操作符：用来联结或改变 WHERE 子句中的子句的关键字,也称逻辑操作符。

组合 WHERE 子句

使用的操作符包括 AND ，OR，它们的计算次序是 AND 高于 OR ，可以使用括号来明确执行顺序。

IN 操作符

IN 操作符与 OR 有相似的功能，但是有如下优点：

  • 比 OR 执行的更快。
  • 可以包含其他 SELECT 子句，能够动态的建立 WHERE 子句。

NOT 操作符

NOT支持对 IN、BETWEEN、EXISTS 取反。


8 用通配符进行过滤

通配符：用来匹配值的一部分的特殊字符。
搜索模式：由字面值、通配符组合而成。

在子句中使用通配符，需使用 LIKE 操作符，指示 MySQL 根据后端的通配符进行匹配而不是等值匹配。

LIKE 操作符

% 表示任意字符出现的次数。
_ 表示只匹配单个字符。

例子：

1
2
3
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil';

使用通配符的技巧

通配符效率较低，有以下技巧：

  • 不要放在搜索的开始位置，因为会令后面的索引失效。


9 用正则表达式进行搜索

随着过滤条件的复杂性增加，WHERE 子句的复杂性也会增加，可以使用正则表达式。

使用 REGEXP 操作符进行基本的字符匹配:

1
2
3
4
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;

一个区分：LIKE 是匹配整个列，REGEXP 是在列值内进行匹配，详细请看书解释。


12 汇总数据

聚集函数：运行在行组上，计算和返回单个值的函数。

聚集函数

  • AVG()
  • COUNT()
  • MAX()
  • MIN()
  • SUM()

聚集不同值

1
2
3
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

组合聚集函数

1
2
3
4
5
SELECT COUNT(*) AS num_items,
       MIN(prod_price) AS price_min,
       MAX(prod_price) AS price_max,
       AVG(prod_price) AS price_avg
FROM products;


13 分组数据

如果要查询每个产品的供应商供应数目，WHERE 子句不能满足要求，可以使用分组，把数据分成多个逻辑组，对每个组进行计算。

创建分组

分组使用 GROUP BY 子句。

1
2
3
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

使用 GROUP BY 子句的一些重要规定：

  • GROUP BY 子句中列出的每个列必须是检索列或有效表达式。如果在 SELECT 中使用表达式，则必须在 GROUP BY 子句中指定相同的表达式，不能使用别名。
  • 除聚集计算外，SELECT 语句中的每个列都必须在 GROUP BY 子句给出。
  • 如果分组中具有 NULL 值，则 NULL 将作为一个分组返回，如果列中有多行 NULL 值，它们将分为一组。
  • GROUP BY 子句必须出现在 WHERE 子句之后，ORDER BY 子句之前。

过滤分组

使用 HAVING 过滤分组。WHERE 过滤行，HAVING 过滤分组。

HAVING 和 WHERE 的区别，WHERE 在分组前进行过滤，HAVING 在分组后进行过滤。

一个例子，过滤 12 个月内两个以上的订单的顾客：

1
2
3
4
5
SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >=2;

分组和排序

GROUP BY 具有排序功能，但如果是有排序需求的话，必须加上 ORDER BY。

SELECT 子句顺序

1
2
3
4
5
6
7
1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY
7. LIMIT


子查询

子查询即嵌套在其他查询中的查询。

利用子查询进行过滤

一个例子，列出订购物品 TNT2 的所有客户：

1
2
3
4
5
SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
                    FROM orderitems
                    WHERE prod_id = 'TNT2');

查询由内向外进行处理。

作为计算字段使用子查询

一个例子，求 customers 表中每个客户的订单总数。订单与客户id 存储在 order 表中。

首先对客户 10001 的订单进行计数：

1
2
3
SELECT COUNT(*)
FROM orders
WHERE cust_id = 10001;

为了对每个客户执行 COUNT() 计算,应该将 COUNT() 作为一个字查询:

1
2
3
4
5
6
7
SELECT cust_name,
       cust_state,
       (SELECT COUNT(*)
        FROM orders
        WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;


联结表

联结是一种机制，用来在一条 SELECT 语句中关联表，可以联结多个表返回一组输出。

联结在实际的数据库表中不存在，存在于查询的执行当中。

创建联结

创建一个简单联结：

1
2
3
4
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;

注意 SQL 语句中 WHERE 子句的重要性，如果没有 WHERE 子句，那么将会导致笛卡尔积数量的结果。

内联结：

1
2
3
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

相比上面的查询，具有明确的语法，联结条件使用 ON 子句而不是使用 WHERE。

表联结和子查询可以实现相同的功能。


16 创建高级联结

自联结

使用表别名的原因是能在单条 SELECT 语句中不止一次引用相同的表。

如果物品（ID 为 DTNTR）存在问题，想知道该供应商的其它物品是否有问题。可以使用如下子查询：

1
2
3
4
5
SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id
                 FROM products
                 WHERE prod_id = 'DTNTR');

使用自联结同样可以实现该查询：

1
2
3
4
SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
  AND p2.prod_id = 'DTNTR';

自然联结

标准的联结返回所有的数据，甚至相同的列多次出现。自然联结使每个列只返回一次。

一个例子：

1
2
3
4
5
6
SELECT c.* o.order_num, o.order_date,
       oi.prod_id, oi.quantity, OI.item_price
FROM customer AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
  AND oi.order_num = o.order_num
  AND prod_id = 'FB';

外部联结

选择左边表选择所有行。

1
2
3
SELECT customer.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
  ON orders.cust_id = customers.cust_id;


17 组合查询

复合查询：MySQL 允许执行多个查询，并将结果作为单个查询返回。

使用复合查询的两种基本情况：

  1. 不同表的单个查询。
  2. 相同表的多个查询按单个返回。

组合查询可以和多个 WHERE 条件的语句由相同的功能。

创建组合查询

使用 UNION 完成组合工作。

1
2
3
4
5
6
SELECT vend_id, prod_id, prod_price
FROM products
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002);

使用 UNION 的规则：

  • 两条以上 SELECT 语句。
  • 每个查询必须包含相同的列，表达式，聚集函数。
  • 类型必须兼容，可以进行转换。

UNION 默认去重，如果不想去重，可以使用 UNION ALL。

如果想对结果排序，可以在最后加 ORDER BY。


创建和操纵表

创建表

使用 NULL 值

每列后添加 NOT NULL 关键字。

主键

多个列也可以做为主键，但必须唯一。

1
2
3
4
5
6
CREATE TABLE order
(
    order_num  int NOT NULL,
    order_item int NOT NULL,
    PRIMARY KEY(order_num, order_item)
) ENGINE=InnoDB;

使用 AUTO_INCREMENT

每执行 INSERT 操作后，会对该列自动增量，给出下一个可用的值。这样给每行唯一的一个 id ，可用作主键。

1
cust_id int NOT NULL AUTO_INCREMENT,

每个表只允许一个 AUTO INCREMENT 列，并且必须被索引（设置主键）。

获得下一个可用的 id：

1
SELECT last_insert_id()

指定默认值
在 CREATE TABLE 语句的列定义中使用 DEFAULT 关键字。

引擎类型
在建表语句 CREATE_TABLE 后添加，ENGINE=InnDB 。


22 使用视图

视图是虚拟的表。只包含使用时动态检索数据的查询。

视图

一个例子，可以把如下查询：

1
2
3
4
5
SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
  AND orderitems.order_num = order.order_num
  AND prod_id = 'abc';

包装为一个名为 productcustomers 的虚拟表：

1
2
3
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'abc';

productcustomers 是一个视图，它不包含表中应该有的任何列或数据，它包含的是一个 SQL 查询（与上面用以正确联结表的相同的查询）。

视图的常见应用：

  • 重用 SQL 语句。
  • 使用表的组成部分而不是整个表。
  • 保护数据。只给予表的特定部分的访问权限。
  • 更改数据格式和表示。
  • 极客时间里的，事务的隔离级别的实现。

可以用与表的相同的方式利用它们，SELECT、INSERT、UPDATE。

重要的是知道，视图仅仅是查看存储在别处的数据的一种设施，它们的数据都是从别的表检索出来的。

视图的规则和限制：

  • 唯一命名。
  • 可以嵌套。
  • 视图的检索 SELECT 中也有 ORDER BY ，那么视图的 ORDER BY 将被覆盖。
  • 视图不能索引。
  • 可以和表一起使用。

使用视图

使用视图与计算字段：

1
2
3
4
5
6
7
CREATE VIEW orderitemsexpanded AS
SELECT order_num,
       prod_id,
       quantity,
       item_price,
       quantity * item_price AS expanded_price
FROM orderitems;

使用：

1
2
3
SELECT *
FROM orderitemsexpanded
WHERE order_num = 25;

更新视图，视图可更新，会对基表进行操作。


26 管理事务处理

事务是一种机制，事务处理可以用来维护数据库的完整性，它保证成批处理的 MySQL 操作要么完全执行，要么完全不执行。

事务处理

事务涉及的几个术语：

  • 事务（transaction）：指一组 SQL 语句。
  • 回退（rollback）：指撤销指定 SQL 语句的过程。
  • 提交（commit）：指将未存储的 SQL 语句结果写入数据库表。
  • 保留点（savepoint）：指事务处理中设置的临时占位符，你可以对它发布回退。

控制事务处理

事务开始语句：

1
START TRANSACTION

事务回退语句：

1
ROLLBACK

例子：

1
2
3
4
5
SELECT * FROM ordertotals;
START TRANSACTION;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

哪些语句可以回退？
可以回退 INSERT、DELETE、UPDATE 语句。但是不可以回退 SELECT、CREATE、DROP 语句。

一般的 MySQL 语句都是直接针对数据库表执行，会进行隐含提交。而事务必须明确使用 COMMIT 语句进行提交。

1
2
3
4
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num =20010;
COMMIT;

如果其中一条 DELETE 执行失败，则另一条会被自动撤销。

隐含事务关闭：当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭。

使用保留点：

1
2
SAVEPOINT delete;
ROLLBACK TO delete1;

为指示 MySQL 不自动提交更改，可以使用以下语句：

1
SET autocommit =0;

autocommit 针对的是每个连接而不是服务器。


30 改善性能

MySQL 是用一系列的默认设置预先配置的，但使用过程中需要经常调整内存、缓冲区大小等。为查看当前设置，可使用：

1
SHOW VARIABLES;

或

1
SHOW STATUS;

MySQL 是一个多用户多线程的 DBMS，经常同时执行多个任务，如果某一个执行缓慢，则所有都会执行缓慢，可使用：

1
SHOW PROCESSLIST;

显示所有活动线程（数据库实例是一个进程，每个进程又有许多线程连接），还可使用 KILL 命令终结特定贤线程。
具体语法可以参考 MySQL 官网。

使用 EXPLAIN 语句让 MySQL 解释它将如何执行一条 SELECT 语句。

决不要检索比需求还多的数据，如 SELECT *。

使用正确的数据类型。

导入数据时应该关闭自动提交。

可以使用 UNION 代替 OR。

索引改善查询的性能，伤害插入、删除的性能，不经常被搜索不需加索引。

最重要，每条规则都在某些条件下会打破。