11.17

数据库基础

数据库：是一个“结构化的数据”的集合。

SQL：结构化查询语言(Structured Query Language)。SQL是专门用于访问数据库的标准化语言。

SQL包括三部分：

1. 数据定义语言（DDL）：包含定义数据库及其对象的语句，例如表，视图，触发器，存储过程等，DDL主要用在定义或改变表的结构、数据类型，表之间的链接和约束等初始化工作上。主要有CREATE、ALTER、DROP等语句。
2. 数据操作语言（DML）：对数据库中的数据进行管理，即INSERT、DELETE、UPDATE、SELECT语句。
3. 数据控制语言（DCL）：用来设置和更改数据库用户及其权限（访问数据库中特定数据的权限）的语句。包括GRANT、DENY、REVOKE等

MySQL：一个数据库管理系统（DBMS），是一种关系数据库（RDB）。

• 开源软件。可以在源代码基础上二次开发。
• 可以在UNIX，Linux，Windows等各种平台上运行。
• 可靠，可扩展和快速的。
• 开发网站或Web应用程序，MySQL是一个不错的选择(强烈建议使用)。

示例数据库

“exampleDB.sql”：汽车零售商数据库

MySQL示例数据库模式由以下表组成：

• customers: 存储客户的数据。
• products: 存储产品的数据。
• productLines: 存储产品类别数据。
• orders: 存储客户订购的销售订单。
• orderDetails: 存储每个销售订单的详细订单产品数据项。
• payments: 存储客户订单的付款数据信息。
• employees: 存储所有员工信息以及组织结构，例如，直接上级(谁向谁报告工作)。
• offices: 存储销售处数据，类似于各个分公司。

查询数据——SELECT语句

SELECT语句从 表或视图 获取数据，并控制要查询哪些列（需要字段）哪些行（符合条件）。即通常只希望看到的“符合条件的行的子集”，“特定字段的子集”或“两者的组合”。

SELECT语句的结果称为结果集（无特定顺序），它是行列表，每行由相同数量的列组成。

注意：关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义。

SELECT的语法

SELECT
column_1, column_2, …
FROM
table_1
[INNER | LEFT |RIGHT] JOIN table_2 ON conditions
WHERE
conditions
GROUP BY column_1， column2， …
HAVING group_conditions
ORDER BY column_1
LIMIT offset, length;

语句中的SELECT和FROM语句是必须的，其他部分是可选的。

注意：不建议使用星号(*)获取所有列，建议显式获取数据的列。原因如下：

• 使用星号(*)可能会返回不使用的列的数据。 它在MySQL数据库服务器和应用程序之间产生不必要的I/O磁盘和网络流量。
• 如果明确指定列，则结果集更可预测并且更易于管理。 比如，在使用星号(*)并且其他用户添加更多列更改了表格数据时，将会得到一个与预期完全不同的结果集。
• 使用星号(*)可能会将敏感信息暴露给未经授权的用户。

排序数据——ORDER BY子句

SELECT语句查询得到的结果集不按任何顺序进行排序。要对结果集进行排序，使用ORDER BY子句。

ORDER BY子句——按排序列对结果集排序

• 对单个列或多个列排序结果集。
• 按升序（ASC，默认排序顺序）或降序（DESC）对不同列的结果集进行排序。

ORDER BY子句的语法：

SELECT column1, column2,…
FROM tablename
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC],…

ORDER BY子句使用细节/注意事项

• 使用多个列排序时，按照ORDER BY之后的顺序进行排序——先按照第一个排序列排序，有多个相同的第一排序列时再使用第二个排序列排序。
• 使用的排序列可以是SELECT的非检索列，并且仍然遵循“依次按照排序列顺序排序”。
• DESC和ASC关键字只作用于其前边的列，因此要对多个列按照降序排序，需要对每个排序列使用关键字。

ORDER BY子句按照表达式排序

SELECT
ordernumber,
orderlinenumber,
quantityOrdered * priceEach AS subtotal
FROM
orderdetails
ORDER BY
ordernumber,
orderLineNumber,
subtotal;

quantityOrdered * priceEach即是MySQL表达式

ORDER BY子句按照自定义顺序排序

ORDER BY子句允许使用FIELD()函数为列中的值定义自己的自定义排序顺序。

例如：orders表中的status字段所有行具有这6种

mysql> select status from orders group by status;+------------+| status     |+------------+| Cancelled  || Disputed   || In Process || On Hold    || Resolved   || Shipped    |+------------+6 rows in set (0.00 sec)

1、对列的所有行分组完全自定义顺序

mysql> select orderNumber,status from orders order by field(status,'In Process','On Hold','Cancelled','Resolved','Disputed','Shipped');+-------------+------------+| orderNumber | status     |+-------------+------------+|       10420 | In Process ||       10421 | In Process ||       10422 | In Process ||       10423 | In Process ||       10424 | In Process ||       10425 | In Process ||       10334 | On Hold    ||       10401 | On Hold    ||       10407 | On Hold    ||       10414 | On Hold    ||       10167 | Cancelled  ||       10179 | Cancelled  ||       10248 | Cancelled  ||       10253 | Cancelled  ||       10260 | Cancelled  ||       10262 | Cancelled  ||       10164 | Resolved   ||       10327 | Resolved   ||       10367 | Resolved   ||       10386 | Resolved   ||       10406 | Disputed   ||       10415 | Disputed   ||       10417 | Disputed   ||       10100 | Shipped    ||       10101 | Shipped    ||       10102 | Shipped    |

则按照指定的顺序对该列完全自定义排序。

2、列的不完全自定义排序

mysql> select orderNumber,status from orders order by field(status,'Cancelled','Disputed','Shipped');+-------------+------------+| orderNumber | status     |+-------------+------------+|       10164 | Resolved   ||       10327 | Resolved   ||       10334 | On Hold    ||       10367 | Resolved   ||       10386 | Resolved   ||       10401 | On Hold    ||       10407 | On Hold    ||       10414 | On Hold    ||       10420 | In Process ||       10421 | In Process ||       10422 | In Process ||       10423 | In Process ||       10424 | In Process ||       10425 | In Process ||       10167 | Cancelled  ||       10179 | Cancelled  ||       10248 | Cancelled  ||       10253 | Cancelled  ||       10260 | Cancelled  ||       10262 | Cancelled  ||       10406 | Disputed   ||       10415 | Disputed   ||       10417 | Disputed   ||       10100 | Shipped    ||       10101 | Shipped    ||       10102 | Shipped    |

可以看到field()列出的行分组出现在自定义排序末尾，而未指定的行分组则是以“未指定顺序”（存储顺序——此处即是订单号自增的顺序）在指定自定义排序行分组之前出现。

并且未指定排序行分组总是在指定行分组之前出现，无论是否指定DESC或ASC。

过滤数据——WHERE子句

WHERE子句——过滤结果集中的行记录

查询数据通常只需要获得特定的行，因此通过where子句根据指定的表达式或条件指定要选择的行。（MySQL默认使用直接相等匹配，比较匹配也是通过相等匹配得来的）

通过WHERE子句操作符指定复杂的条件：=,！=,<=； AND,OR,NOT； BETWEEN,IN； LIKE； IS NULL。

BETWEEN运算符——匹配范围之内的行记录

BETWEEN运算符与日期

例如，orders表的requiredDate列的数据类型是DATE，所以应该使用类型转换将列或表达式（此处列为文字字符串“2013-01-01”和“2013-12-31”）转换为DATE数据类型。

IN运算符——匹配值列表或者子查询中的任何一个值

• IN操作符具有和OR操作符相同的功能。
• BETWEEN、IN操作符可以用在SELECT，INSERT，UPDATE，DELETE等的WHERE子句中。

IN运算符与子查询

mysql> desc orders;+----------------+-------------+------+-----+---------+-------+| Field          | Type        | Null | Key | Default | Extra |+----------------+-------------+------+-----+---------+-------+| orderNumber    | int(11)     | NO   | PRI | NULL    |       || orderDate      | date        | NO   |     | NULL    |       || requiredDate   | date        | NO   |     | NULL    |       || shippedDate    | date        | YES  |     | NULL    |       || status         | varchar(15) | NO   |     | NULL    |       || comments       | text        | YES  |     | NULL    |       || customerNumber | int(11)     | NO   | MUL | NULL    |       |+----------------+-------------+------+-----+---------+-------+7 rows in set (0.00 sec)mysql> desc orderdetails;+-----------------+---------------+------+-----+---------+-------+| Field           | Type          | Null | Key | Default | Extra |+-----------------+---------------+------+-----+---------+-------+| orderNumber     | int(11)       | NO   | PRI | NULL    |       || productCode     | varchar(15)   | NO   | PRI | NULL    |       || quantityOrdered | int(11)       | NO   |     | NULL    |       || priceEach       | decimal(10,2) | NO   |     | NULL    |       || orderLineNumber | smallint(6)   | NO   |     | NULL    |       |+-----------------+---------------+------+-----+---------+-------+5 rows in set (0.01 sec)

查找总金额大于60000的订单

SELECT
orderNumber, customerNumber, status, shippedDate
FROM
orders
WHERE
orderNumber IN (SELECT
orderNumber
FROM
orderdetails
GROUP BY orderNumber
HAVING SUM(quantityOrdered * priceEach) 60000);

上面的整个查询可以分为2个查询。

首先，子查询使用orderdetails表中的GROUP BY和HAVING子句返回总额大于60000的订单号列表。
其次，主查询从orders表中获取数据，并在WHERE子句中应用IN运算符。

LIKE操作符——基于搜索模式匹配行记录

要使用通配符构成搜索模式搜索，必须用LIKE操作符告知MySQL使用通配符匹配，而不是直接相等匹配。

• 通配符：用来匹配值的一部分的特殊字符——%通配符（0,1,或多个字符），_通配符（只匹配1个字符）。
• 搜索模式：由字面值、通配符或两者组合构成的搜索条件。

LIKE操作符匹配含有%或_的行记录

可以使用默认转义字符或者使用ESCAPE指定自定义转义字符。

SELECT
productCode, productName
FROM
products
WHERE
productCode LIKE ‘%_20%’;

或者

SELECT
productCode, productName
FROM
products
WHERE
productCode LIKE ‘%20’;

通配符使用技巧

因为LIKE操作符强制MySQL扫描整个表以找到匹配的行记录，同时通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长，因此：

• 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
• 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
• 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。
• 不允许数据库引擎使用索引进行快速搜索。

SQL顺序

即使WHERE子句出现在语句的末尾，但MySQL会首先使用WHERE子句中的表达式来选择匹配的行。

过滤数据——LIMIT子句

LIMIT子句——约束结果集中的行

LIMIT子句接受一个或两个参数。两个参数的值必须为零或正整数。（一个参数的即是约束为结果集的前count行，两个参数的约束为结果集的offset+1开始的count行）

两个参数的LIMIT子句语法：

SELECT
column1,column2,…
FROM
table
LIMIT offset , count;

• offset参数指定要返回的第一行的偏移量。（注意：第一行的偏移量为0，而不是1）。
• count指定要返回的最大行数。

LIMIT子句常用用法：

1、查询显示结果集中的前N行，或者从某行开始的N行。

SELECT customernumber, customername, creditlimit FROM customers LIMIT 5;

SELECT customernumber, customername, creditlimit FROM customers LIMIT 0,5;

2、查询显示最大值或最小值。——先使用ORDER BY子句按照要获得最大/小值得列字段排序，再使用LIMIT子句获得最大/小值

mysql> SELECT customernumber, customername, creditlimitFROM customersORDER BY creditlimit DESCLIMIT 5;+----------------+------------------------------+-------------+| customernumber | customername                 | creditlimit |+----------------+------------------------------+-------------+|            141 | Euro+ Shopping Channel       | 227600      ||            124 | Mini Gifts Distributors Ltd. | 210500      ||            298 | Vida Sport, Ltd              | 141300      ||            151 | Muscle Machine Inc           | 138500      ||            187 | AV Stores, Co.               | 136800      |+----------------+------------------------------+-------------+5 rows in set

即可获得creditlimit的最大值。若使用ASC即可获得最小值

3、查询显示第n大/小的值——先使用OEDER BY子句排序所需字段，再使用LIMIT约束得出第n大/小的行

此处肯定不能使用MAX()或MIN()函数。

mysql> SELECT productCode, productName, buypriceFROM productsORDER BY buyprice DESC;+-------------+--------------------------------------+----------+| productCode | productName                          | buyprice |+-------------+--------------------------------------+----------+| S10_4962    | 1962 LanciaA Delta 16V               | 103.42   || S18_2238    | 1998 Chrysler Plymouth Prowler       | 101.51   || S10_1949    | 1952 Alpine Renault 1300             | 98.58    || S24_3856    | 1956 Porsche 356A Coupe              | 98.3     || S12_1108    | 2001 Ferrari Enzo                    | 95.59    || S12_1099    | 1968 Ford Mustang                    | 95.34    |... ....+-------------+--------------------------------------+----------+110 rows in set

要找出结果集中价格第二高的产品。可以使用LIMIT子句来选择出第二行(注意：偏移量从0开始，所以要指定从1开始，然后取一行记录)：

mysql> SELECT productCode, productName, buyprice FROM  productsORDER BY buyprice DESCLIMIT 1, 1;+-------------+--------------------------------+----------+| productCode | productName                    | buyprice |+-------------+--------------------------------+----------+| S18_2238    | 1998 Chrysler Plymouth Prowler | 101.51   |+-------------+--------------------------------+----------+1 row in set

问题：如果有重复值时，LIMIT子句如何获得第n大/小值，和GROUP BY子句如何工作？？？

过滤数据——DISTINCT关键字

DISTINCT关键字——消除结果集中的重复行

SELECT语句中使用DISTINCT关键字，用来消除结果集中的重复行。（**注意：**DISTINCT关键字应用于它之后的所有列而不仅是一列）

DISTINCT语法：

SELECT DISTINCT
[column | column_list]
FROM
table_name
WHERE
where_conditions;

distinct关键字在单列上使用

在单列上使用，比如使用SELECT语句从employees表中查询员工的所有姓氏(lastName)：

SELECT
lastname
FROM
employees
ORDER BY lastname;

即是：结果集为一列23行，则组合对应的distinct子句使用可以消除结果集中lastname列的重复行。

但如果是：

SELECT
firstname,lastname
FROM
employees
ORDER BY lastname;

则结果集总共有两列，并且两列的组合行并没有重复，因此使用distinct子句未消除重复行。

从这个例子可以加深 “distinct子句是对结果集的重复行的消除” 的理解。

distinct关键字在多列上使用

distinct子句在多列上使用，MySQL**通过结果集中所有列的组合来确定行的唯一性。**

例如：从customers表中获取城市(city)和州(state)的 唯一组合，可以使用以下查询：

SELECT DISTINCT
state, city
FROM
customers
WHERE
state IS NOT NULL
ORDER BY state , city;

distinct关键字与NULL

如果列具有NULL值，则DISTINCT子句将所有NULL值视为相同的值。

若distinct子句在单列上使用，则MySQL将保留一个NULL值，并删除其它的NULL值。

例如，在customers表中，有很多行的州(state)列是NULL值。 当使用DISTINCT子句来查询客户所在的州时，我们将看到唯一的州和NULL值，如下查询所示：

SELECT DISTINCT
state
FROM
customers;

注意：此处也只对一列使用了distinct子句。

若distinct子句在多列上使用，则各自列的NULL值即是一类（相同）值。同样遵循“结果集中的所有列的组合确定行的唯一性”

distinct关键字与group by子句

一般而言，DISTINCT子句是GROUP BY子句的特殊情况。

• 如果在SELECT语句中使用GROUP BY子句，而不使用聚合函数，则GROUP BY子句的行为与DISTINCT子句类似。————因为distinct子句消除结果集中的重复行，group by子句合并分组结果集中的重复行（“将结果集按行分组”）

SELECT
state
FROM
customers
GROUP BY state;

DISTINCT子句和GROUP BY子句之间的区别是GROUP BY子句可对结果集进行了排序，而DISTINCT子句不进行排序。

• 如果将ORDER BY子句添加到使用DISTINCT子句的语句中，则结果集将被排序，并且与使用GROUP BY子句的语句返回的结果集相同。

distinct关键字和聚合函数

可以在DISTINCT子句中使用聚合函数(例如SUM，AVG和COUNT)，MySQL先用distinct子句消除结果集中的重复行，再使用聚合函数应用于上一步的结果集。

例如，要计算美国客户的唯一state列的值，可以使用以下查询：

SELECT
COUNT(DISTINCT state)
FROM
customers
WHERE
country = ‘USA’;

注意：聚合函数统计时不计入NULL。

distinct关键字和limit子句

如果要将DISTINCT子句与LIMIT子句一起使用，MySQL会在查找LIMIT子句中指定的唯一行数时立即停止搜索。