MySQL数据库基础(二)

发表于

分组数据

数据分组

分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。WHERE过滤指定的是行而不是分组。事实
上，WHERE没有分组的概念。

创建分组

分组是在SELECT语句的GROUP BY子句中建立的。

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

上面的SELECT语句指定了两个列，vend_id和num_prods为计算字段（用COUNT(*)函数建立），GROUP BY子句指示MySQL按vend_id排序并分组数据。这导致对每个vend_id而不是整个表计算num_prods一次。

因为使用了GROUP BY，就不必指定要计算和估值的每个组了。系统会自动完成。GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集。

在具体使用GROUP BY子句前，需要知道一些重要的规定:

  • GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
  • 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
  • GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
  • 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
  • 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
  • GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

ROLLUP： 使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值，如下所示：

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

过滤分组

除了能用GROUP BY分组数据外，MySQL还允许过滤分组，规定包括哪些分组，排除哪些分组。

MySQL提供了HAVING子句，HAVING非常类似于WHERE。唯一的差别是WHERE过滤行，而HAVING过滤分组。

1
2
3
SELECT cust_id, COUNT(*) AS orders FROM orders 
GROUP BY cust_id 
HAVING COUNT(*) >= 2;

同时使用WHERE和HAVING子句：

下面的语句，列出具有2个（含）以上、prod_price为10（含）以上的产品的vend_id：

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

WHERE子句过滤所有prod_price至少为10的行。然后按vend_id分组数据，HAVING子句过滤计数为2或2以上的分组。

分组和排序

我们经常发现用GROUP BY分组的数据确实是以分组顺序输出的。但情况并不总是这样，它并不是SQL规范所要求的。

一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY排序数据。

下面的SELECT语句，它检索总计订单价格大于等于50的订单的order_num和总计订单价格：

1
2
3
4
SELECT order_num, SUM(quantity*item_price) AS ordertotal 
FROM orderitems 
GROUP BY(order_num) 
HAVING SUM(quantity*item_price) >= 50;

为按总计订单价格排序输出，需要添加ORDER BY子句，如下所示：

1
2
3
4
5
SELECT order_num, SUM(quantity*item_price) AS ordertotal 
FROM orderitems 
GROUP BY(order_num) 
HAVING SUM(quantity*item_price) >= 50
ORDER BY ordertotal;

SELECT子句顺序

SELECT语句中使用时必须遵循的次序：

字句 说明 是否必须使用
SELECT 要返回的列或表达式 是
FROM 从中检索数据的表 仅在从表选择数据时使用
WHERE 行级过滤 否
GROUP BY 分组说明 仅在按组计算聚集时使用
HAVING 组级过滤 否
ORDER BY 输出排序顺序 否
LIMIT 要检索的行数 否

使用子查询

子查询

SQL还允许创建子查询（subquery），即嵌套在其他查询中的查询。

利用子查询进行过滤

订单存储在两个表中。对于包含订单号、客户ID、订单日期的每个订单，orders表存储一行。各订单的物品存储在相关的orderitems表中。orders表不存储客户信息。它只存储客户的ID。实际的客户信息存储在customers表中。

假如需要列出订购物品TNT2的所有客户：

  1. 检索包含物品TNT2的所有订单的编号：

    1
    SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';

  2. 检索具有前一步骤列出的订单编号(20005, 20007)的所有客户的ID：

    1
    SELECT cust_id FROM orders WHERE order_num IN (20005, 20007)

  3. 检索前一步骤返回的所有客户ID(10001, 10004)的客户信息：

    1
    SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (10001, 10004);

可以将上面的WHERE字句转换为子查询：

1
2
3
4
SELECT cust_name, cust_contact FROM customers 
WHERE cust_id IN (SELECT cust_id FROM orders 
WHERE order_num IN (SELECT order_num FROM orderitems 
WHERE prod_id = 'TNT2'));

执行上述SELECT语句，MySQL实际上必须执行3条SELECT语句。最里边的子查询返回order_num列表，此列表用于其外面的子查询的WHERE子句。外面的子查询返回cust_id列表，此cust_id列表用于最外层查询的WHERE子句。最外层查询确实返回所需的数据。

在WHERE子句中使用子查询能够编写出功能很强并且很灵活的SQL语句。对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。

作为计算字段使用子查询

使用子查询的另一方法是创建计算字段。假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。

为了执行这个操作，遵循下面的步骤:

  1. 从customers表中检索客户列表。
  2. 对于检索出的每个客户，统计其在orders表中的订单数目。

可使用SELECT COUNT(*)对表中的行进行计数，并且通过提供一条WHERE子句来过滤某个特定的客户ID，可仅对该客户的订单进行计数。例如，下面的代码对客户10001的订单进行计数：

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

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

1
2
3
4
5
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 语句对customers 表中每个客户返回3 列：cust_name、cust_state和orders。orders是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。

联结表

联结

SQL最强大的功能之一就是能在数据检索查询的执行中联结（join）表。

关系表

关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值互相关联。

外键： 为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

为什么要使用联结

联结是一种机制，用来在一条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;

SELECT语句指定的两个列（prod_name和prod_price）在一个表中，而另一个列（vend_name）在另一个表中。FROM子句列出了两个表，分别是vendors和products。WHERE子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id。

匹配的两个列以vendors.vend_id 和products.vend_id指定。这里需要这种完全限定列名，因为如果只给出vend_id，则MySQL不知道指的是哪一个（它们有两个，每个表中一个）。

完全限定列名： 在引用的列可能出现二义性时，必须使用完全限定列名（用一个点分隔的表名和列名）。如果引用一个没有用表名限制的具有二义性的列名，MySQL将返回错误。

不要忘了WHERE子句： 应该保证所有联结都有WHERE子句，否则MySQL将返回比想要的数据多得多的数据。同理，应该保证WHERE子句的正确性。不正确的过滤条件将导致MySQL返回不正确的数据。

内部联结

目前为止所用的联结称为 等值联结 （equijoin），它基于两个表之间的相等测试。这种联结也称为 内部联结 。其实，对于这种联结可以使用稍微不同的语法来明确指定联结的类型。下面的SELECT语句返回与前面例子完全相同的数据：

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

两个表之间的关系是FROM子句的组成部分，以INNER JOIN 指定。在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的相同。

使用哪种语法： ANSI SQL规范首选INNER JOIN语法。此外，尽管使用WHERE子句定义联结的确比较简单，但是使用明确的联结语法能够确保不会忘记联结条件，有时候这样做也能影响性能。

联结多个表

SQL对一条SELECT语句中可以联结的表的数目没有限制。创建联结的基本规则也相同。首先列出所有表，然后定义表之间的关系。

1
2
3
4
5
SELECT prod_name, vend_name, prod_price, quantity 
FROM orderitems, products, vendors 
WHERE products.vend_id = vendors.vend_id 
AND orderitems.prod_id = products.prod_id 
AND order_num = 20005;

此例子显示编号为20005的订单中的物品。订单物品存储在orderitems表中。每个产品按其产品ID存储，它引用products表中的产品。这些产品通过供应商ID联结到vendors表中相应的供应商，供应商ID存储在每个产品的记录中。

性能考虑： MySQL在运行时关联指定的每个表以处理联结。这种处理可能是非常耗费资源的，因此不要联结不必要的表。联结的表越多，性能下降越厉害。

创建高级联结

使用表别名

别名除了用于列名和计算字段外，SQL还允许给表名起别名。这样做有两个主要理由：

  • 缩短SQL语句
  • 允许在单条SELECT语句中多次使用相同的表
1
2
3
4
5
SELECT cust_name, cust_contact 
FROM customers 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 = 'TNT2';

FROM子句中3个表全都具有别名。customers AS c建立c作为customers的别名，等等。这使得能使用省写的c而不是全名customers。

应该注意，表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户机。

使用不同类型的联结

查询要求首vend_id为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。

  1. 使用子查询：

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

  2. 使用联结查询：

    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';

products表在FROM子句中出现了两次。使用了表别名。products的第一次出现为别名p1，第二次出现为别名p2。SELECT语句使用p1前缀明确地给出所需列的全名。如果不这样，MySQL将返回错误，因为分别存在两个名为prod_id、prod_name的列。MySQL不知道想要的是哪一个列（即使它们事实上是同一个列）。WHERE（通过匹配p1中的vend_id和p2中的vend_id）首先联结两个表，然后按第二个表中的prod_id过滤数据，返回所需的数据。

外部联结

许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。这种类型的联结称为 外部联结 。

下面的SELECT语句给出一个简单的内部联结。它检索所有客户及其订单：

1
2
3
SELECT customers.cust_id, orders.order_num 
FROM customers INNER JOIN orders 
ON customers.cust_id = orders.cust_id;

外部联结语法类似。为了检索所有客户，包括那些没有订单的客户，可如下进行：

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

这条SELECT语句使用了关键字OUTER JOIN来指定联结的类型。但是，与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。上面的例子使用LEFT OUTER JOIN从FROM子句的左边表（customers表）中选择所有行。

使用带聚集函数的联结

聚集函数可以与联结一起使用。

1
2
3
4
5
SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) 
AS num_ord 
FROM customers INNER JOIN orders 
ON customers.cust_id = orders.cust_id 
GROUP BY customers.cust_id;

此SELECT语句使用INNER JOIN将customers和orders表互相关联。GROUP BY 子句按客户分组数据， 因此， 函数调用COUNT(orders.order_num)对每个客户的订单计数，将它作为num_ord返回。

组合查询

MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为 并 （union）或 复合查询 （compound query）。

有两种基本情况，其中需要使用组合查询：

  • 单个查询中从不同的表返回类似结构的数据
  • 对单个表执行多个查询，按单个查询返回数据

组合查询和多个WHERE条件： 多数情况下，组合相同表的两个查询完成的工作与具有多个WHERE子句条件的单条查询完成的工作相同。

创建组合查询

利用UNION，可给出多条SELECT语句，将它们的结果组合成单个结果集。

假如需要prod_price小于等于5的所有物品的一个列表，而且还想包括vend_id为1001和1002生产的所有物品（不考虑价格）。

使用UNION:

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

使用WHERE:

1
2
3
4
SELECT vend_id, prod_id, prod_price 
FROM products 
WHERE prod_price <= 5 
OR vend_id IN (1001, 1002);

在这个简单的例子中，使用UNION可能比使用WHERE子句更为复杂。但对于更复杂的过滤条件，或者从多个表（而不是单个表）中检索数据的情形，使用UNION可能会使处理更简单。

UNION规则

进行UNION时有几条规则需要注意:

  • UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）
  • UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）
  • 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型
包含或取消重复的行

UNION从查询结果集中自动去除了重复的行，如果想不取消重复的行，可使用UNION ALL而不是UNION。

1
2
3
4
5
6
7
SELECT vend_id, prod_id, prod_price 
FROM products 
WHERE prod_price <= 5 
UNION ALL
SELECT vend_id, prod_id, prod_price 
FROM products 
WHERE vend_id IN (1001, 1002);
对组合查询结果排序

SELECT语句的输出用ORDER BY子句排序。在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条ORDER BY子句。

1
2
3
4
5
6
7
8
SELECT vend_id, prod_id, prod_price 
FROM products  
WHERE prod_price <= 5 
UNION  
SELECT vend_id, prod_id, prod_price 
FROM products 
WHERE vend_id IN (1001, 1002) 
ORDER BY vend_id, prod_price;

全文本搜索

MyISAM引擎： 支持全文检索
InnoDB： 不支持全文检索

在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。

使用全文本搜索

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。在对表列进行适当设计后，MySQL会自动进行所有的索引和重新索引。

启用全文本搜索支持

一般在创建表时启用全文本搜索。CREATE TABLE语句接受FULLTEXT子句，它给出被索引列的一个逗号分隔的列表。

1
2
3
4
5
6
7
8
9
CREATE TABLE productnotes
(
    note_id int NOT NULL AUTO_INCREMENT,
    prod_id char(10) NOT NULL,
    note_date datetime NOT NULL,
    note_text text NULL,
    PRIMARY KEY(note_id),
    FULLTEXT(note_text)
) ENGINE=MyISAM;

有一个名为note_text的列，为了进行全文本搜索，MySQL根据子句FULLTEXT(note_text)的指示对它进行索引。这里的FULLTEXT索引单个列，如果需要也可以指定多个列。

在定义之后，MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新。

进行全文本搜索

在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。

1
2
SELECT note_text FROM productnotes 
WHERE Match(note_text) Against('rabbit');

Match(note_text)指示MySQL针对指定的列进行搜索，Against('rabbit')指定词rabbit作为搜索文本。

传递给Match()的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。

全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回。等级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来。

1
2
SELECT note_text, Match(note_text) Against('rabbit') 
AS rank FROM productnotes;

计算列（别名为rank），此列包含全文本搜索计算出的等级值。不包含词rabbit的行等级为0。

布尔文本搜索

MySQL支持全文本搜索的另外一种形式，称为布尔方式。

下表列出支持的所有布尔操作符：

布尔操作符 说明
+ 包含，词必须存在
- 排除，词必须不出现
> 包含，而且增加等级值
< 包含，且减少等级值
() 把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
~ 取消一个词的排序值
* 词尾的通配符
“” 定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）

匹配包含heavy但不包含任意以rope开始的词的行:

1
2
SELECT note_text FROM productnotes 
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

搜索匹配包含词rabbit和bait的行:

1
2
SELECT note_text FROM productnotes 
WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);

没有指定操作符，这个搜索匹配包含rabbit和bait中的至少一个词的行:

1
2
SELECT note_text FROM productnotes 
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);

匹配短语rabbit bait而不是匹配两个词rabbit和bait:

1
2
SELECT note_text FROM productnotes 
WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);

匹配rabbit和carrot，增加前者的等级，降低后者的等级:

1
2
SELECT note_text FROM productnotes 
WHERE Match(note_text) Against('>rabbit <carrot' IN BOOLEAN MODE);

搜索匹配词safe和combination，降低后者的等级:

1
2
SELECT note_text FROM productnotes 
WHERE Match(note_text) Against('+safe +(combination)' IN BOOLEAN MODE);

插入数据

插入完整的行

1
2
INSERT INTO customers 
VALUES(NULL, 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);

虽然这种语法很简单，但并不安全，应该尽量避免使用。上面的SQL语句高度依赖于表中列的定义次序，并且还依赖于其次序容易获得的信息。即使可得到这种次序信息，也不能保证下一次表结构变动后各个列保持完全相同的次序。

编写INSERT语句的更安全的方法如下：

1
2
3
INSERT INTO customers(cust_name, 
cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) 
VALUES ('Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);

在表名后的括号里明确地给出了列名。在插入行时，MySQL将用VALUES列表中的相应值填入列表中的对应项。VALUES中的第一个值对应于第一个指定的列名。第二个值对应于第二个列名，如此等等。

省略列： 如果表的定义允许，则可以在INSERT操作中省略某些列。省略的列必须满足以下某个条件。

  • 该列定义为允许NULL值（无值或空值）。
  • 在表定义中给出默认值。这表示如果不给出值，将使用默认值。

插入多个行

1
2
3
4
INSERT INTO customers(cust_name, 
cust_address, cust_city, cust_state, cust_zip, cust_country) 
VALUES( 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA'), 
('M.Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA');

INSERT语句有多组值，每组值用一对圆括号括起来，用逗号分隔。

插入检索出的数据

INSERT还存在另一种形式，可以利用它将一条SELECT语句的结果插入表中。

1
2
3
INSERT INTO customers(cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
SELECT cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country
FROM custnew;

INSERT SELECT中的列名： 在INSERT和SELECT语句中不一定要求列名匹配。它使用的是列的位置，因此SELECT中的第一列（不管其列名）将用来填充表列中指定的第一个列，第二列将用来填充表列中指定的第二个列，如此等等。

更新和删除数据

更新数据

为了更新（修改）表中的数据，可使用UPDATE语句。

1
2
UPDATE customers SET cust_email = 'elmer@fudd.com' 
WHERE cust_id=10005;

更新多个列：

1
2
UPDATE customers SET cust_name = 'The Fudds', cust_email = 'elmer@fudd.com' 
WHERE cust_id=10005;

为了删除某个列的值，可设置它为NULL:

1
2
UPDATE customers SET cust_email = NULL 
WHERE cust_id = 10005;

删除数据

为了从一个表中删除（去掉）数据，使用DELETE语句。

1
DELETE FROM customers WHERE cust_id = 10006;

如果执行UPDATE而不带WHERE子句，则表中每个行都将用新值更新。类似地，如果执行DELETE语句而不带WHERE子句，表的所有数据都将被删除。

创建和操纵表

CREATE TABLE创建表，必须给出下列信息：

  • 新表的名字，在关键字CREATE TABLE之后给出
  • 表列的名字和定义，用逗号分隔。
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE customers 
(    
    cust_id int NOT NULL AUTO_INCREMENT, 
    cust_name char(50) NOT NULL, 
    cust_address char(50) NULL, 
    cust_city char(50) NULL,
    cust_state char(5) NULL, 
    cust_zip char(10) NULL, 
    cust_country char(50) NULL, 
    cust_contact char(50) NULL, 
    cust_email char(255) NULL, 
    PRIMARY KEY(cust_id) 
) ENGINE=InnoDB;

每列的定义以列名（它在表中必须是唯一的）开始，后跟列的数据类型，表的主键可以在创建表时用PRIMARY KEY关键字指定。这里，列cust_id指定作为主键列。

使用NULL值

NULL值就是没有值或缺值。允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列，在插入或更新行时，该列必须有值。

主键

表中的每个行必须具有唯一的主键值。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。

主键中只能使用不允许NULL值的列。允许NULL值的列不能作为唯一标识。

使用AUTO_INCREMENT

AUTO_INCREMENT告诉MySQL，自动对该列增量。每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）。

指定默认值

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

1
2
3
4
5
6
7
8
9
CREATE TABLE orderitems
(
    order_num int NOT NULL,
    order_item int NOT NULL,
    prod_id char(10) NOT NULL,
    quantity int NOT NULL DEFAULT 1,
    item_price decimal(8, 2) NOT NULL,
    PRIMARY KEY (order_num, order_item)
)ENGINE=InnoDB;

引擎类型

MySQL有一个具体管理和处理数据的内部引擎。在使用CREATE TABLE语句时，该引擎具体创建表，而在使用SELECT语句或进行其他数据库处理时，该引擎在内部处理你的请求。多数时候，此引擎都隐藏在DBMS内，不需要过多关注它。

  • InnoDB是一个可靠的事务处理引擎,它不支持全文本搜索
  • MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）
  • MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

外键不能跨引擎： 外键不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

更新表

更新表

为更新表定义，可使用ALTER TABLE语句。

给表添加一个列：

1
ALTER TABLE vendors ADD vend_phone CHAR(20);

删除列:

1
ALTER TABLE vendors DROP COLUMN vend_phone;

ALTER TABLE的一种常见用途是定义外键:

1
2
3
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY (order_num) REFERENCES orders (order_num);

删除表

删除表使用DROP TABLE语句:

1
DROP TABLE customers;

重命名表

1
RENAME TABLE customers2 TO customers;

使用视图

视图

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

为什么使用视图

  • 重用SQL语句。
  • 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。
  • 使用表的组成部分而不是整个表。
  • 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
  • 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

使用视图

  • 视图用CREATE VIEW语句来创建。
  • 使用SHOW CREATE VIEW viewname；来查看创建视图的语句。
  • 用DROP删除视图，其语法为DROP VIEW viewname;
  • 更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE ORREPLACE VIEW。
利用视图简化复杂的联结
1
2
3
4
5
CREATE VIEW productcustomers AS 
SELECT cust_name, cust_contact, prod_id 
FROM customers, orders, orderitems 
WHERE customers.cust_id = orders.cust_id 
AND orderitems.order_num = orders.order_num;

这条语句创建一个名为productcustomers的视图，它联结三个表，以返回已订购了任意产品的所有客户的列表。如果执行SELECT * FROM productcustomers，将列出订购了任意产品的客户。

为检索订购了产品TNT2的客户，可如下进行：

1
2
3
SELECT cust_name, cust_contact 
FROM productcustomers 
WHERE prod_id = 'TNT2';
用视图重新格式化检索出的数据

下面的SELECT语句在单个组合计算列中返回供应商名和位置：

1
2
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title 
FROM vendors ORDER BY vend_name;

假如经常需要这个格式的结果。不必在每次需要时执行联结，创建一个视图，每次需要时使用它即可。

1
2
3
CREATE VIEW vendorlocations AS 
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title 
FROM vendors ORDER BY vend_name;

使用SELECT语句相查询的视图：

1
SELECT * FROM vendorlocations;
用视图过滤不想要的数据

可以定义customeremaillist视图，它过滤没有电子邮件地址的客户:

1
2
3
4
CREATE VIEW customeremaillist AS  
SELECT cust_id, cust_name, cust_email 
FROM customers 
WHERE cust_email IS NOT NULL;
使用视图与计算字段

下面SELECT语句。它检索某个特定订单中的物品，计算每种物品的总价格：

1
2
3
4
SELECT order_num, prod_id, quantity, item_price, 
quantity*item_price AS expanded_price 
FROM orderitems 
WHERE order_num = 20005;

将其转换为一个视图:

1
2
3
4
CREATE VIEW orderitemsexpanded AS 
SELECT order_num, prod_id, quantity, item_price, 
quantity*item_price AS expanded_price 
FROM orderitems WHERE order_num = 20005;

检索订单20005的详细内容:

1
2
SELECT * FROM orderitemsexpanded 
WHERE order_num = 20005;

更新视图

视图主要用于数据检索。对视图增加或删除行，实际上是对其基表增加或删除行。

并非所有视图都是可更新的。基本上可以说，如果MySQL不能正确地确定被更新的基数据，则不允许更新（包括插入和删除）。这实际上意味着，如果视图定义中有以下操作，则不能进行视图的更新：

  • 分组（使用GROUP BY和HAVING）
  • 联结
  • 子查询
  • 并
  • 聚集函数（Min()、Count()、Sum()等）
  • DISTINCT
  • 导出（计算）

使用存储过程

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。

使用存储过程有3个主要的好处，即简单、安全、高性能。

创建存储过程

1
2
3
4
5
6
7
DELIMITER //

CREATE PROCEDURE productpricing() 
BEGIN 
    SELECT Avg(prod_price) AS priceaverage 
    FROM products; 
END //

此存储过程名为productpricing，用CREATE PROCEDURE productpricing()语句定义。如果存储过程接受参数，它们将在()中列举出来。此存储过程没有参数，但后跟的()仍然需要。BEGIN和END语句用来限定存储过程体，过程体本身仅是一个简单的SELECT语句。

mysql命令行客户机的分隔符： mysql命令行实用程序使用;作为语句分隔符。如果命令行实用程序要解释存储过程自身内的;字符，则它们最终不会成为存储过程的成分，这会使存储过程中的SQL出现句法错误。 解决办法是临时更改命令行实用程序的语句分隔符，DELIMITER //。为恢复为原来的语句分隔符，可使用DELIMITER ;。

使用存储过程

1
CALL productpricing();

CALL productpricing();执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数，所以存储过程名后需要有()符号（即使不传递参数也需要）。

删除存储过程

存储过程在创建之后，被保存在服务器上以供使用，直至被删除。删除命令从服务器中删除存储过程。

1
DROP PROCEDURE productpricing;

使用参数

一般，存储过程并不显示结果，而是把结果返回给你指定的变量。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE PROCEDURE productpricing(     
    OUT pl DECIMAL(8, 2),     
    OUT ph DECIMAL(8, 2),     
    OUT pa DECIMAL(8, 2) 
) 
BEGIN 
    SELECT Min(prod_price)     
    INTO pl     
    FROM products;     
    SELECT Max(prod_price)      
    INTO ph     
    FROM products;     
    SELECT Avg(prod_price)     
    INTO pa     
    FROM products; 
END //

此存储过程接受3个参数：pl存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格。每个参数必须具有指定的类型，这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）。MySQL支持IN（传递给存储过程）、OUT（从存储过程传出，如这里所用）和INOUT（对存储过程传入和传出）类型的参数。存储过程的代码位于BEGIN和END语句内，它们是一系列SELECT语句，用来检索值，然后保存到相应的变量（通过指定INTO关键字）。

参数的数据类型： 存储过程的参数允许的数据类型与表中使用的数据类型相同。注意，记录集不是允许的类型，因此，不能通过一个参数返回多个行和列。这就是前面的例子为什么要使用3个参数（和3条SELECT语句）的原因。

为调用此修改过的存储过程，必须指定3个变量名，如下所示：

1
CALL productpricing(@pricelow, @pricehigh, @priceaverage);

由于此存储过程要求3个参数，因此必须正好传递3个参数，不多也不少。所以，这条CALL语句给出3个参数。它们是存储过程将保存结果的3个变量的名字。

变量名： 所有MySQL变量都必须以@开始。

在调用时，这条语句并不显示任何数据。它返回以后可以显示的变量。

为了显示检索出的产品平均价格，可如下进行：

1
SELECT @priceaverage;

为了获得3个值，可使用以下语句：

1
SELECT @priceaverage @pricehigh, @pricelow

下面是另外一个例子，这次使用IN和OUT参数。ordertotal接受订单号并返回该订单的合计：

1
2
3
4
5
6
7
8
9
10
CREATE PROCEDURE ordertotal(     
    IN onnumber INT,     
    OUT ototal DECIMAL(8, 2) 
) 
BEGIN 
    SELECT Sum(item_price*quantity) 
    FROM orderitems     
    WHERE order_num = onnumber     
    INTO ototal; 
END//

onumber定义为IN，因为订单号被传入存储过程。ototal定义为OUT，因为要从存储过程返回合计。SELECT语句使用这两个参数，WHERE子句使用onumber选择正确的行，INTO使用ototal存储计算出来的合计。

为调用这个新存储过程，可使用以下语句：

1
CALL ordertotal(20005, @total);

必须给ordertotal传递两个参数；第一个参数为订单号，第二个参数为包含计算出来的合计的变量名。

为了显示此合计，可如下进行：

1
SELECT @total;

检查存储过程

为显示用来创建一个存储过程的CREATE语句，使用SHOW CREATE PROCEDURE语句：

1
SHOW CREATE PROCEDURE ordertotal

为了获得包括何时、由谁创建等详细信息的存储过程列表，使用SHOW PROCEDURE STATUS。

使用游标

游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

不像多数DBMS，MySQL游标只能用于存储过程（和函数）。

创建游标

游标用DECLARE语句创建。DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE和其他子句。

1
2
3
4
5
6
 CREATE PROCEDURE processorders()
 BEGIN
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
END;

DECLARE语句用来定义和命名游标，这里为ordernumbers。存储过程处理完成后，游标就消失（因为它局限于存储过程）。

打开和关闭游标

游标用OPEN CURSOR语句来打开：

1
OPEN ordernumbers;

在处理OPEN语句时执行查询，存储检索出的数据以供浏览和滚动。

游标处理完成后，应当使用如下语句关闭游标：

1
CLOSE ordernumbers;

CLOSE释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。

隐含关闭： 如果你不明确关闭游标，MySQL将会在到达END语句时自动关闭它。

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE PROCEDURE processorders()
BEGIN
    -- Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    -- Open the cursor
    OPEN ordernumbers;

    -- Close the cursor
    CLOSE ordernumbers;
END;

使用游标数据

在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据（所需的列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行（不重复读取同一行）。

1
2
3
4
5
6
7
8
9
10
CREATE PROCEDURE processorders() 
BEGIN 
    DECLARE o INT;     
    DECLARE ordernumbers CURSOR      
    FOR     
    SELECT order_num FROM orders;     
    OPEN ordernumbers;     
    FETCH ordernumbers INTO o;     
    CLOSE ordernumbers; 
END;

其中FETCH用来检索当前行的order_num列（将自动从第一行开始）到一个名为o的局部声明的变量中。对检索出的数据不做任何处理。

循环检索数据，从第一行到最后一行：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE PROCEDURE processorders() 
BEGIN 
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT; 

    DECLARE ordernumbers CURSOR     
    FOR      
    SELECT order_num FROM orders; 
    
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
    
    OPEN ordernumbers; 
    
        REPEAT     
            FETCH ordernumbers INTO o; 
        UNTIL done END REPEAT;    
    
    CLOSE ordernumbers; 
END ;

FETCH是在REPEAT内，因此它反复执行直到done为真（由UNTIL done END REPEAT;规定）。为使它起作用，用一个DEFAULT 0（假，不结束）定义变量done。

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

这条语句定义了一个CONTINUE HANDLER，它是在条件出现时被执行的代码。这里，它指出当SQLSTATE '02000'出现时，SET done=1。 SQLSTATE '02000'是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。

如果一切正常，可以在循环内放入任意需要的处理（在FETCH语句之后，循环结束之前）。

使用触发器

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句：

  • DELETE
  • INSERT
  • UPDATE

其他MySQL语句不支持触发器。

只有表才支持触发器，视图不支持（临时表也不支持）。

创建触发器

触发器用CREATE TRIGGER语句创建:

1
2
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';

CREATE TRIGGER用来创建名为newproduct的新触发器。触发器可在一个操作发生之前或之后执行，这里给出了AFTER INSERT，所以此触发器将在INSERT语句成功执行后执行。这个触发器还指定FOR EACH ROW，因此代码对每个插入行执行。在这个例子中，文本Product added将对每个插入的行显示一次。

触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）。单一触发器不能与多个事件或多个表关联，所以，如果需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

删除触发器

1
DROP TRIGGER newproduct;

管理事务处理

事务处理

事务处理（transaction processing）可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。

事务处理的几个术语：

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

控制事务处理

MySQL使用下面的语句来标识事务的开始：

1
START TRANSACTION;
使用ROLLBACK

MySQL的ROLLBACK命令用来回退（撤销）MySQL语句

哪些语句可以回退： 事务处理用来管理INSERT、UPDATE和DELETE语句。不能回退SELECT语句。（这样做也没有什么意义。）不能回退CREATE或DROP操作。事务处理块中可以使用这两条语句，但如果执行回退，它们不会被撤销。

使用COMMIT

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交（implicit commit），即提交（写或保存）操作是自动进行的。

但是，在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用COMMIT语句。

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

从系统中完全删除订单20010。因为涉及更新两个数据库表orders和orderItems，所以使用事务处理块来保证订单不被部分删除。最后的COMMIT语句仅在不出错时写出更改。如果第一条DELETE起作用，但第二条失败，则DELETE不会提交（实际上，它是被自动撤销的）。

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

使用保留点

简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理。但是，只是对简单的事务处理才能这样做，更复杂的事务处理可能需要部分提交或回退。

为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。

这些占位符称为保留点。为了创建占位符，可如下使用SAVEPOINT语句：

1
SAVEPOINT delete1;

每个保留点都取标识它的唯一名字，以便在回退时，MySQL知道要回退到何处。为了回退到给出的保留点，可如下进行：

1
ROLLBACK TO delete1;

释放保留点： 保留点在事务处理完成（执行一条ROLLBACK或COMMIT）后自动释放。