mysql必知必会笔记

mysql官方文档

前言 表结构

我在最初阅读此书的时候，不知道书中的数据表结构是怎样的，因此对文中许多复杂查询语句，比如联结，很是困惑。因此建议读者在学习本书时，先把数据表结构弄清楚。
本书中使用的样例表为一个想象的随身物品推销商使用的订单录入系统，这些表用来完成以下几个任务：
 管理供应商；管理产品目录；管理顾客列表；录入顾客订单；
要完成这几个任务需要作为关系数据库设计成分的紧密联系的6个表。

第一章 mysql数据库简介

1 数据库是什么？

数据库是一个以某种有组织的方式存储的数据集合。
表是一种结构化的文件，可用来存储某种特定类型的数据。
主键（primary key）一列（或一组列），其值能够唯一区分表中每个行。
SQL（发音为字母S-Q-L或sequel）是结构化查询语言（Structured Query Language）的缩写。SQL是一种专门用来与数据库通信的语言。

2 Mysql简介及使用

DBMS可分为两类：一类为基于共享文件系统的DBMS，另一类为基于客户机—服务器的DBMS。MySQL、Oracle以及Microsoft SQL Server等数据库是基于客户机—服务器的数据库。
连接数据库
第三方工具
主机名
端口
用户名
密码

命令行登录数据库 mysql -u -root -p
使用数据库
show databases 显示数据库
use 数据库名 选择一个数据库使用

show tables 显示数据表
show columns from tablename 显示表列
MySQL支持用DESCRIBE作为SHOW COLUMNS FROM的一种快捷方式。

show status 显示mysql状态
sql语言
工作中我们常用的sql语言主要分三类。其中数据定义语言DDL主要负责表结构和视图等的创建及改变，比如create/alter table/drop table等等。
数据操纵语言DML负责数据的处理，比如数据的查询及更新，即常说的增删改查。如果你会这个，恭喜你成为一名合格的crud boy。
数据库操纵语言DCL，主要涉及数据库权限管理方面，比如权限的授予grant和撤回revoke。

3 Mysql权限管理

使用mysql数据库必须通过用户登录数据库，而且授予权限之后用户才能使用数据库。可以直接给用户授予权限，也可以通过角色给用户授予权限。
角色是一个强大的工具，使您得以将用户集中到一个单元中，然后对该单元应用权限。对一个角bai色授予、拒绝或废除的权限也适用于该角色的任何成员。可以建立一个角色来代表单位中一类工作人员所执行的工作，然后给这个角色授予适当的权限。当工作人员开始工作时，只须将他们添加为该角色成员，当他们离开工作时，将他们从该角色中删除。而不必在每个人接受或离开工作时，反复授予、拒绝和废除其权限。权限在用户成为角色成员时自动生效。
如果根据工作职能定义了一系列角色，并给每个角色指派了适合这项工作的权限，则很容易在数据库中管理这些权限。之后，不用管理各个用户的权限，而只须在角色之间移动用户即可。如果工作职能发生改变，则只须更改一次角色的权限，并使更改自动应用于角色的所有成员，操作比较容易。
Mysql–8.0以后版本才支持角色。我的腾讯与服务器上装的是5.0版本的，所以无法创建角色。
2.1 创建及删除用户

/*创建用户，*/
create user 'user'@'%' identified by 'password';
CREATE USER 'wendy'@'localhost' IDENTIFIED BY '52wendyma';
-- 删除用户
DROP USER 'wendy'@'localhost';

在这里说明几点：
1、user：新创建用户的用户名
2、%：新创建用户的允许登录IP，%允许在任何主机登录，localhost只允许在本地登录，IP允许登录的IP（eg.127.0.0.1）
3、password：新建用户的登录密码
4、这样创建之后，注意是没有任何权限的，只能登录，除此之外什么都干不了。
那怎么样有权限呢？接下来就是给新用户授予权限了。

2.2 授予用户权限

GRANT ALL PRIVILEGES ON *.* TO 'wendy'@'%';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON *.* TO 'wendy'@'%';

– 查看用户权限

SHOW GRANTS FOR 'wendy'@'%';

2.3 创建角色及授予权限

CREATE ROLE 'app_developer', 'app_read', 'app_write';   --创建了3个角色

GRANT ALL ON app_db.* TO 'app_developer';   -- 给app_db数据库中所有表的所有权限
GRANT SELECT ON app_db.* TO 'app_read';     -- app_db数据库中所有表的查询权限
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';  --app_db数据库中所有表的修改权限

2.4 创建用户

CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'dev1pass';
CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'read_user1pass';
CREATE USER 'read_user2'@'localhost' IDENTIFIED BY 'read_user2pass';
CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'rw_user1pass';

2.5 给用户赋予角色

GRANT 'app_developer' TO 'dev1'@'localhost';
GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';
GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';

第二章 数据的查询

4 检索数据

检索单个列 select prod_name from products（数据未过滤，也未排序）
检索多个列 select prod_id, prod_name, prod_price from products
检索所有列 select * from products （如果给定一个通配符（*），则返回表中所有列。列的顺序一般 是列在表定义中出现的顺序。）

检索不同的行（去重） select DISTINCT vend_id from products
限制结果数量 select vend_id from products LIMIT 5;（显示前5个）
select vend_id from products LIMIT 5, 5;（LIMIT 5, 5指示MySQL返回从行5开始的5行。）
使用完全限定的表名 select products.prod_name from products

5 排序检索数据

Order by排序数据（默认升序ASC） select prod_name from products ORDER BY prod_name;

按多个列排序 select prod_id, prod_name, prod_price from products ORDER BY prod_price, prod_name;（下面的代码检索3个列，并按其中两个列对结果进行排序——首先按 价格，然后再按名称排序。）

DESC指定排序方向 select prod_name from products ORDER BY prod_name DESC;
但是，如果打算用多个列排序怎么办？下面的例子以降序排序产品 （最贵的在最前面），然后再对产品名排序：

select prod_id, prod_name, prod_price  from products ORDER BY prod_price DESC, prod_name;

6 where过滤数据

Where子句

select  prod_name, prod_price from products WHERE prod_price = 2.5;
           select  prod_name, prod_price from products WHERE prod_price < 10;

select prod_name, prod_price from products WHERE prod_name = ‘fuses’;（MySQL在执行匹配时默认不区分大小写，所 以fuses与Fuses匹配。）

不匹配检查（<>） select vebd_id,prod_name from products WHERE vend_id <> 1003;

范围值检查（between）

select prod_name, prod_price from products WHERE prod_price BETWEEN 5 AND 10;

空值检查

select cust_id from customers where cust_email IS NULL;

7 数据过滤NOT与IN

本章讲授如何组合WHERE子句以建立功能更强的更高级的搜索条件。 我们还将学习如何使用NOT和IN操作符。

AND操作符

select prod_id, prod_name, prod_price from products WHERE vent_id=1003 AND prod_price<=10;

OR操作符

select prod_name, prod_price from products WHERE vent_id=1002 OR vent_id=1003;

操作符计算次序

  select prod_name, prod_price from products WHERE vent_id=1002 OR vent_id=1003 AND prod_price>=10;

（优先计算AND操作符，再OR.当SQL看到上述WHERE子句时，它理解为由供应商1003制造的任何价格为10美元（含）以上的产品，或者由供应商1002制造的任何产品，
而不管其价格如何。）可用圆括号解决上面的次序问题

select prod_name, prod_price from products WHERE (vent_id=1002 OR vent_id=1003) AND prod_price>=10;

IN操作符
IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。

select prod_name, prod_price from products WHERE vent_id IN (1002,1003) ORDER BY prod_name;

如果你认为IN操作符完成与OR相同的功能，那么你的这种猜测是对的。

NOT操作符
WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后所 跟的任何条件。

select prod_name, prod_price from products WHERE vent_id NOT IN (1002,1003) ORDER BY prod_name;

8 用通配符进行过滤

本章介绍什么是通配符、如何使用通配符以及怎样使用LIKE操作符进行通配搜索，以便对数据进行复杂过滤。

为在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示MySQL，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

百分号（%）通配符

Select prod_id,prod_name from products WHERE prod_name LIKE ‘jet%’;

（此例子使用了搜索模式’jet%’。在执行这条子句时，将检索任 意以jet起头的词。%告诉MySQL接受jet之后的任意字符，不 管它有多少字符。）

通配符可在搜索模式中任意位置使用，并且可以使用多个通配符。

select prod_id,prod_name from products WHERE prod_name LIKE ‘%anvil%’;

（搜索模式’%anvil%'表示匹配任何位置包含文本anvil的值，而 不论它之前或之后出现什么字符。）

下划线（_）通配符
下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符。

select prod_id,prod_name from products WHERE prod_name LIKE ‘_ ton anvil’;

使用通配符的技巧
正如所见，MySQL的通配符很有用。但这种功能是有代价的：通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。

9 正则表达式

10 创建计算字段

Concat拼接字段

select Concat(vend_name,’(‘,vend_country,’)’) from vendors;

（Concat()拼接串，即把多个串连接起来形成一个较长的串。）

select Concat(RTrim(vend_name),’(‘,RTrim(vend_country),’)’) from vendors;

删除数据右侧多余的空格来整理数据，这可以 使用MySQL的RTrim()函数来完成

AS使用别名
从前面的输出中可以看到，SELECT语句拼接地址字段工作得很好。但此新计算列的名字是什么呢？实际上它没有名字，它只是一个值。如果仅在SQL查询工具中查看一下结果，这样没有什么不好。但是，一个未命名的列不能用于客户机应用中，因为客户机没有办法引用它。使用别名可以解决这个问题。

select Concat(vend_name,’(‘,vend_country,’)’) AS vend_title from vendors;

算术运算
计算字段的另一常见用途是对检索出的数据进行算术计算,输出中显示的expanded_price列为一个计算字段。

 Select prod_id,quantity,item_price ,quantity*item_price AS expanded_price from order_items WHERE order_num = 2005;

11 常用数据处理函数

字符串处理函数
有时我们单单查询出数据还不够，还需要对其进行一定的处理，比如你想获得商贩名字的大写格式。

 Select vendor_name, Upper(vendor) AS vendor_name_upcase from vendors;

（Upper()将文本转换为大写，因此本例子中每个供应商都列出两次，第一次为vendors表中存储的值，第二次作为列vend_name_upcase转换为大写。）
下面是一些常用的字符串处理函数

trim()             #返回去除字符串两边的空格
rtrim()            #去除字符串右边的空格
concat(x,y)        #将提供的参数x和y拼接成一个字符串
substr(x,y)        #获取从字符串x中的第y个位置开始的字符串，跟substring()函数作用相同
substr(x,y,z)      #获取从字符串x中的第y个位置开始长度为z的字符串
length(x)          #返回字符串x的长度
replace(x,y,z)     #用字符串z替代字符串x中的字符串y
upper(x)           #将字符串x的所有字母变成大写字母
lower(x)           #将字符串x的所有字母变成小写字母
left(x,y)          #返回字符串x的前y个字符
right(x,y)         #返回字符串x的后y个字符
repeat(x,y)        #将字符串x重复y次
space(x)           #返回x个空格
strcmp (x,y)       #比较x和y，返回的值可以为-1,0,1
reverse(x)         #将字符串x反转

日期处理函数

Select cust_id,order_num from orders WHERE Date(order_date) = '2005-09-01';

常用数学函数
数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算。

abs(x)                #返回x的绝对值
rand()                #返回0到1的随机数
mod(x,y)              #返回x除以y以后的余数
power(x,y)            #返回x的y次方
round(x)              #返回离x最近的整数
round(x,y)            #保留x的y位小数四舍五入后的值
sqrt(x)               #返回x的平方根
truncate(x,y)         #返回数字x截断为y位小数的值
ceil(×)               #返回大于或等于x的最小整数
floor(x)              #返回小于或等于x的最大整数
greatest(x1,x2...)    #返回集合中最大的值
least(x1,x2...)       #返回集合中最小的值

12 汇总数据

聚集函数（aggregate function） 运行在行组上，计算和返回单个值的函数。
我们经常需要汇总数据而不用把它们实际检索出来，为此MySQL提供了专门的函数。使用这些函数，MySQL查询可用于检索数据，以便分析和报表生成。

Select AVG(prod_price)  AS avg_price from products;

AVG也可以用来确定特定列或行的平均值。下面的例子返回特定供应商所提供产品的平均价格：

Select AVG(prod_price)  AS avg_price from products where vend_id = 1003;

Select COUNT(*) AS num_cust from customers;（返回customers表中客户的总数）

Select MAX(prod_price) as max_price from products;

Select SUM(quantity) as items_ordered from orderitems WHERE order_num = 2005;（检索所订购物品的总数）

SUM算术运算

Select SUM(quantity*item_price) as total_price from orderitems;（得出总的订单金额）

DISTINCT聚集不同值

Select AVG(DISTINCT prod_price)  AS avg_price from products where vend_id = 1003;（去掉重复的价格再计算平均值）

组合聚集函数

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 分组数据

如果要返回每个供应商提供的产品数目怎么办？

GROUP BY创建分组

Select vend_id, COUNT(*) 
AS num_prods from products 
GROUP BY vend_id;

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

HAVING过滤分组

Select cust_id, COUNT(*) 
AS orders from orders 
GROUP BY cust_id 
HAVING COUNT(*)>=2;

（列出至少有两个订单的所有顾客。这里WHERE子句不起作用，因为过滤是基于分组聚集值而不是特定行值的。）

分组和排序的区别

Select order_num, SUM(quantity*item_price) AS ordertotal 
from orderitems 
GROUP BY order_num 
HAVING SUM(quantity*item_price)>=50;

（它检索总计订单价格大于等于50的订
单的订单号和总计订单价格）

Select order_num, SUM(quantity*item_price) AS ordertotal 
from orderitems 
GROUP BY order_num HAVING SUM(quantity*item_price)>=50 
ORDER BY ordertotal;

（分组数据，以便SUM(*)函数能够返回总计订单价格。HAVING子句过滤数据，使得只返回总计订单价格大于等于50的订单。最后，用ORDER BY子句排序输出。）

14 子查询

假如需要列出订购物品TNT2的所有客户，应该怎样检索？下面列出具体的步骤
(1) 检索包含物品TNT2的所有订单的编号。

Select order_num
From orderitems
Where prod_id = “TNT2”;

(2) 检索具有前一步骤列出的订单编号的所有客户的ID。

Select cust_id 
From orders
Where order_num in(Select order_num
                  From orderitems
                  Where prod_id = “TNT2”);

(3) 检索前一步骤返回的所有客户ID的客户信息。

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”));

子查询作为计算字段
下面的语句告诉SQL比较orders表中的cust_id与当前正从customers表中检索的cust_id：这种类型的子查询称为相关子查询。任何时候只要列名可能有多义性，就必须使用这种语法（表名和列名由一个句点分隔）。

Select cust_name, cust_state,
       (select count(*) from orders
        Where orders.cust_id = customers.cust_id) AS orders
From customers
Order by cust_name;

15 联结表

为什么要用联结表？
假如有由同一供应商生产的多种物品，那么在何处存储供应商信息（如，供应商名、地址、联系方法等）呢？如果在产品信息中存储供应商信息，显然会造成不必要的浪费。

关键是，相同数据出现多次决不是一件好事，此因素是关系数据库设计的基础。关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值（即关系设计中的关系（relational））互相关联。
在这个例子中，可建立两个表，一个存储供应商信息，另一个存储产品信息。vendors表包含所有供应商信息，每个供应商占一行，每个供应商具有唯一的标识。此标识称为主键（primary key）（在第1章中首次提到），可以是供应商ID或任何其他唯一值。

products表只存储产品信息，它除了存储供应商ID（vendors表的主键）外不存储其他供应商信息。vendors表的主键又叫作products的外键，它将vendors表与products表关联，利用供应商ID能从vendors表中找出相应供应商的详细信息。

外键（foreign key） 外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
创建联结

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子句。与以前的SELECT语句不一样，这条语句的FROM子句列出了两个表，分别是vendors和products。它们就是这条SELECT语句联结的两个表的名字。这两个表用WHERE子句正确联结，WHERE子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id。

我理解的是查询vendor.vend_id时，在products表中查询每一列的vend_id,直到找到products.vend_id与vendor.vend_id相等，联结成功。
笛卡儿积（cartesian product） 由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

INNER JOIN内部联结（配合ON）
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

Customer顾客表 order订单表 products产品表 vendors商贩表 orderitems订单物品

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;

我理解的是order只存放订单交易的信息，比如订单号、交易时间、金钱。每一个订单具体包括哪些物品存放在orderitems表。orderitems存放订单物品的id，每个订单物品不必有一表列指明价格（订单物品不存放物品具体信息）。订单物品orderitems可以外联到产品表products，产品表中有产品的基本信息，如价格、尺寸、产品类型等。

子查询一联结表的关系
14章中的子查询可以用联结表实现

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 = “TNT2”;

16 高级联结

使用表别名

SELECT cust_name, cust_contact
FROM cust_name 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 = ‘TNTS’;

假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。下面是解决此问题的一种方法：
子查询

SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id
                 FROM products
                 WHERE pro_id = ‘DTNTR’);

也可使用自联结（自己联结自己）解决上面问题

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

自然联结
无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被联结的列）。

内联结
联结将一个表中的行与另一个表中的行相关联，没有对应的行则不显示。其实上一章所使用的联结就是内联结，只不过没有采用inner join 关键字。

SELECT customers.cust_id, orders.order_num
FROM customers INNER JOIN orders
  ON customers.cust_id = orders_cust_id;

外部联结
许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行（即customers中存在的cust_id在order中找不到对应的），此时需要用到外联结。
左外联结

SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
  ON customers.cust_id = order.cust_id;

该结果会查询出所有customers表中的数据，如果orders表中没有对应的cust_id，则显示空值。并且要知道left outer join 可以缩写为left join，后者默认是Outer属性的。

右外联结

SELECT customers.cust_id, orders.order_num
FROM customers RIGHT OUTER JOIN orders
  ON orders.cust_id = customers.cust_id;

上面的例子使用LEFT OUTER JOIN从FROM子句的左边表（customers表）中选择所有行。为了从右边的表中选择所有行，应该使用RIGHT OUTER JOIN，

带聚集函数的联结

SELECT customers.cust_name,
      customers.cust_id,
      COUNT(orders.order_num) AS num_order
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返回。

17 组合查询

多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。

假如需要价格小于等于5的所有物品的一个列表，而且还想包括供应商1001和1002生产的所有物品，如下两次查询即可，更好的方法是使用下面的组合查询。

UNION组合查询

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;

UNION从查询结果集中自动去除了重复的行（换句话说，它的行为与单条SELECT语句中使用多个WHERE子句条件一样）。UNION组合查询可用多条WHERE子句实现。

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

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

18 创建表

CREATE TABLE
为利用CREATE TABLE创建表，必须给出下列信息：
新表的名字，在关键字CREATE TABLE之后给出；
表列的名字和定义，用逗号分隔

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;

Not null规定字段不能为空，null说明子段可以为空。PRIMARY KEY(cust_id)指定数据表表的主键，主键不能存在重复值，主键概念可以参考数据库三大范式。AUTO_INCREMENT,规定cust_id列数字是自动增长的。ENGINE选择数据的引擎，通常我们使用innodb即可，它属于较高级的内容，本文暂不深入讨论。

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

CREATE TABLE orders(
  order_num INT   NOT NULL AUTO_INCREMENT,
  order_date DATETIME  NOT NULL,
  cust_id  INT  NOT NULL,
  PRIMARY KEY(order_num)
)ENGINE=INNODB;

CREATE TABLE vendors(
  vend_id INT NOT NULL AUTO_INCREMENT,
  vend_name  CHAR(50) NOT NULL,
  vend_address  CHAR(50) NULL,
  vend_city  CHAR(50) NULL,
  vend_state CHAR(5)  NULL,
  vend_zip  CHAR(10)  NULL,
  vend_country  CHAR(50) NULL,
  PRIMARY KEY(vend_id)
)ENGINE=INNODB;

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

CREATE TABLE orderitems(
  order_num INT NOT NULL,
  order_item INT NOT NULL,
  prod_id CHAR(10) NOT NULL,
  quantity INT NOT NULL,
  item_price  DECIMAL(8,2)  NOT NULL,
  PRIMARY KEY(order_num,order_item)
)ENGINE=INNODB;

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;

更新表
增加列

ALTER table vendors ADD vend_phone CHAR(20) null;

删除列

ALTER table vendors DROP COLUMN vend_phone;

删除表

DROP table customers;

重命名表

RENAME table customers TO customers2;

重命名多个表

RENAME TABLE backup_customers TO customers,
             backup_vendors TO vendors,
             backup_products TO products;

定义外键
以下是本文中所使用的表中，存在的外键关系。

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

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_products
FOREIGN KEY (prod_id) REFERENCES products(prod_id);

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (cust_id) REFERENCES customers(cust_id);

ALTER TABLE products
ADD CONSTRAINT fk_orderitems_vendors
FOREIGN KEY (vend_id) REFERENCES vendors(vend_id);

19 插入数据

Insert into Customers VALUES(NULL, ’Pep E. LaPew’, ‘100 Main Street’, Los Angelel’, ‘CA’, ‘90046’, ‘USA’, NULL, NULL);

存储到每个表列中的数据在VALUES子句中给出，对每个列必须提供一个值。如果某个列没有值（如上面的cust_contact和cust_email列），应该使用NULL值（假定表允许对该列指定空值）。各个列必须以它们在表定义中出现的次序填充。第一列cust_id也为NULL。这是因为每次插入一个新行时，该列由MySQL自动增量。你不想给出一个值（这是MySQL的工作），又不能省略此列（如前所述，必须给出每个列），所以指定一个NULL值（它被MySQL忽略，MySQL在这里插入下一个可用的cust_id值）。

上面的SQL语句高度依赖于表中列的定义次序，并且还依赖于其次序容易获得的信息。即使可得到这种次序信息，也不能保证下一次表结构变动后各个列保持完全相同的次序。因此，编写依赖于特定列次序的SQL语句是很不安全的。如果这样做，有时难免会出问题。

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

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 Angelel’, ‘CA’, ‘90046’, ‘USA’, NULL, NULL);

因为提供了列名，VALUES必须以其指定的次序匹配指定的列名，列名不一定按各个列出现在实际表中的次序。

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

Insert into Customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) VALUES(’Pep E. LaPew’, ‘100 Main Street’, Los Angelel’, ‘CA’, ‘90046’, ‘USA’), 
(‘M. Martian’, ‘42 Galaxy Way’, ‘New York’, ‘NY’, ‘11213’, ‘USA’);

插入检索出的数据
假如你想从另一表中合并客户列表到你的customers表。不需要每次读取一行，然后再将它用INSERT插入，可以如下进行：

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

20 更新和删除数据

更新单列

UPDATE customers
SET cust_email = ‘elmer@fudd.com’
WHERE cust_id = 10005;

更新多列
在更新多个列时，只需要使用单个SET命令，每个“列=值”对之间用逗号分隔（最后一列之后不用逗号）。

UPDATE customers
SET cust_name = “The Fudds”,
cust_email = “elmer@fudd.com”
WHERE cust_id = 10005;

删除单行

Delete from customers WHERE cust_id = 10006;

删除表中所有行

TRUNCATE TABLE;

（TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）

注意：使用强制实施引用完整性的数据库（关于这个内容，请参阅原书第15 章），这样MySQL将不允许删除具有与其他表相关联的数据的行。

21 视图

本章将介绍视图究竟是什么，它们怎样工作，何时使用它们。我们还将看到如何利用视图简化前面章节中执行的某些SQL操作。
视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
重要的是知道视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。

 视图用CREATE VIEW语句来创建。
 使用SHOW CREATE VIEW viewname；来查看创建视图的语句。
 用DROP删除视图，其语法为DROP VIEW viewname;。
 更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。利用视图简化复杂的联结

CREATE VIEW productcustpmers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = order.cust_id
  AND orderitems.order_num = orders.order_num;

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

SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = “TNT2”;

这条语句通过WHERE子句从视图中检索特定数据。在MySQL处理此查询时，它将指定的WHERE子句添加到视图查询中的已有WHERE子句中，以便正确过滤数据。
可以看出，视图极大地简化了复杂SQL语句的使用。利用视图，可一次性编写基础的SQL，然后根据需要多次使用。

用视图重新格式化检索出的数据
假如想获得供应商与位置组合，不必每次都执行联结，可直接从视图得到结果。

CREATE VIEW wendorlocations AS
SELECT CONCAT(RTRIM(vend_name), ’(‘, RTRIM(vend_country), ‘)’)
AS vend_title 
FROM vendors
ORDER BY vend_name; 

有了上述视图，再查询数据不用再建立联结了。

Select * from vendorlocations;

用视图过滤数据
例如，可以定义customeremaillist视图，它过滤没有电子邮件地址的客户

CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;

视图更新数据
视图也可以更新，不过有很多限制，此处不讨论视图的更新操作。这听上去好像是一个严重的限制，但实际上不是，因为视图主要用于数据检索。

22 存储过程