一 了解SQL

1 数据库基础数据库（database）：保存有组织的数据容器。表（table）：某种特定类型数据的结构化清单。表名实际由数据库名和表名共同组成唯一字符串。列（column）：表中的一个字段。所有表都是由一个或多个列组成。数据类型（datatype）：所允许的数据类型。每个列都有相应的数据类型，限制或允许了该列中存储的数据。行（row）：表中的一个记录。主键（primary key）：一列或一组列，值能够唯一标识表中的每一行。唯一：任意两行不能具有相同主键值非空：每一行都必须有一个主键值且不为NULLfinal：主键列的值不允许修改或更新非重用：主键值不能重用2 什么是SQL

SQL为结构化查询语言（Structured Query Language）的缩写。SQL是一种专门用来与数据库沟通的语言。

二 检索数据1 相关概念关键字（keyword）：作为SQL组成部分的保留字。不能作为表或列名。大小写：SQL语句不区分大小写，可以将关键字用大写表示区分。结束：SQL语句的结束为分号(；)。2 检索列

-- 检索单个列SELECT prod_name FROM Products;-- 检索多个列SELECT prod_id,prod_name,prod_price FROM Products;-- 检索所有列SELECT * FROM Products;

注：检索所有列会降低检索和应用程序的性能。

3 检索不同的值（去重）

SELECT DISTINCT vend_id FROM Products;-- DISTINCT不能部分使用,下面的SQL只有当vend_id和prod_name都相同的两行才不会被检出。SELECT DISTINCT vend_id,prod_name FROM Products;

4 限制结果（各种数据库的实现不相同）

-- 前5条-- MYSQL SQLite PostgreSQLSELECT prod_name FROM Products LIMIT 5;-- OracleSELECT prod_name FROM Products WHERE ROWNUM <= 5/*	分隔条*/-- 跳过前5条-- MYSQL SQLite PostgreSQLSELECT prod_name FROM Products LIMIT 5 OFFSET 5;-- MYSQLSELECT prod_name FROM Products LIMIT 5,	5;-- OracleSELECT prod_name FROM Products WHERE ROWNUM > 5 and ROWNUM <= 10;

三 排序检索数据1相关概念子句（clause）：有些子句是必需的（SELECT ... FROM tableName;）,有些则不是（ORDER BY）。2 排序数据（默认升序）

SELECT prod_name FROM Products ORDER BY prod_name;

注1：ORDER BY只能在SELECT语句最后一条子句。注2：ORDER BY所使用的列不需要是显示而选择的列，可以选择非检索的列。

3 按照多个列排序

-- 先按照prod_price排序，当prod_price相同时，才按照prod_name排序。SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price,prod_name；-- 列位置为检出的位置。SELECT prod_id,prod_price,prod_name FROM Products ORDER BY 2,3；-- 先按照prod_price降序排，当prod_price相同时，才按照prod_name升序排。SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price DESC,prod_name；

四 过滤数据1 相关概念过滤条件：数据库中包含大量数据，很少需要检索表中的所有行，所以通常会只检索部分数据，检索部分数据的条件。2 WHERE子句

SELECT prod_name,prod_price FROM Products WHERE prod_price = 3.49;

3 WHERE子句操作符（1）子句操作符

操作符说明=等于<> 或 !=不等于<小于>大于!<不小于!>不大于>=大于等于<=小于等于BETWEEN两值之间IS NULL为NULL

（2）过滤数据

-- 检查单个值SELECT prod_name,prod_price FROM Products WHERE prod_price < 10;-- 不匹配检查SELECT vend_id,prod_name FROM Products WHERE vend_id != 'DLL01';-- 范围检查SELECT prod_name,prod_price FROM Products WHERE prod_price BETWEEN 5 and 10;-- 空值检查SELECT prod_name FROM Products WHERE prod_price IS NULL;

五 高级数据过滤1 相关概念操作符（operator）：用来连接或改变WHERE子句中子句的关键字。AND：检索满足所有给定过滤条件的行。OR：检索满足任一给定过滤条件的行。圆括号：任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。IN：WHERE子句中用来指定要匹配值的清单的关键字。NOT：WHERE子句中用来指定非匹配关键字。2 组合WHERE语句

-- ANDSELECT prod_id,prod_price,prod_name FROM Products WHERE vend_id = 'DLLL01' AND prod_price <=4;-- ORSELECT prod_id,prod_price,prod_name FROM Products WHERE vend_id = 'DLLL01' OR vend_id = 'BRS01';-- 求值顺序SELECT prod_name,prod_price WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >=10;/*   使用IN的好处   1.在很多合法选项时，IN操作符更清晰。   2.在很多AND和OR操作组合使用IN时，求值顺序更容器管理   3.IN操作符比一组OR操作符执行更快   4.IN可以包含其他SELECT语句，能够更动态建立WHERE语句。*/-- INSELECT prod_name,prod_price FROM Products WHERE vend_id IN ('DLL01','BRS01') ORDER BY prod_name;-- NOTSELECT prod_name,prod_price FROM Products WHERE vend_id NOT IN ('DLL01','BRS01') ORDER BY prod_name;

六 用通配符进行过滤1 相关概念通配符（wildcard）：用于匹配值得一部分的特殊字符。搜索模式（search pattern）：由字面量、通配符或两者组合构成的搜索条件。%通配符：0、1或多个字符，不匹配NULL。_通配符：只匹配单个字符。[]通配符：指定一个字符集，必需匹配指定位置的一个字符。2 LIKE操作符

-- % 通配符:0、1或多个字符，不匹配NULLSELECT prod_id,prod_name FROM Products WHERE prod_name LIKE 'Fish%';SELECT prod_id, prod_name ROM Products WHERE prod_name LIKE '%bean bag%';-- 有些数据库会用空格来填补字段的内容。如：prod_name为5个字符，'Fly' -> 'Fly  ',这样'F%y'就无法匹配到，解决方法为函数去除空格或'F%y%'SELECT prod_name FROM Products WHERE prod_name LIKE 'F%y';-- 搜索电子邮件地址WHERE email LIKE 'b%@gmail.com';-- _通配符：只匹配单个字符。SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '__ inch teddy bear';-- []通配符SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[JM]%' ORDER BY cust_contact;

七 创建计算字段1 相关概念字段（field）：基本上与列的意思相同，字段一般与计算字段一起使用。拼接（concatenate）：将值联结到一起（将一个值附加到另一个值）构成单个值。删除空格函数TRIM()：删除左右空格LTRIM()：删除左空格RTRIM()：删除右空格AS：别名的关键字2 拼接字段

-- 大部分数据库SELECT vend_name + ' (' + vend_country + ')' FROM Vendors ORDER BY vend_name;SELECT vend_name || ' (' || vend_country || ')' FROM Vendors ORDER BY vend_name;-- MYSQLSELECT Concat(vend_name,'(',vend_country,')') FROM Vendors ORDER BY vend_name;

3 别名

SELECT Concat(vend_name,'(',vend_country,')') AS vend_title FROM Vendors ORDER BY vend_name;

注1：别名可以是字符串，如：‘vend title’，不够最后不使用多单词的字符串，而是使用_分隔的单词。

4 执行算术计算

-- 汇总价格SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;

八 使用数据处理函数1 相关概念可移植性（portable）：所编写的代码可以在多个系统上运行。函数是否应该使用？可以使用，但需要有相应的注释。2 函数差异

函数语法截取字符串Oracle使用SUBSTR()；MySQL使用SUBSTRING(str,start,end)数据类型转换Oracle使用多个函数；MySQL使用CONVERT()获取当前时间Oracle使用SYSDATE；MySQL使用CURDATE()

3 使用（1）文本处理函数

函数说明LEFT(str,len)返回字符串左边字符LENGTH(str) 或 DATALENGTH() 或 LEN()返回字符串的长度LOWER(st) UPPER(str)小写 大写LTRIM(str) TRIM(str) RTRIM(str)删除左空格 删除左右空格 删除右空格SOUNDEX()返回字符串的SOUNDEX值

注1：上述加粗的部分代表MySQL可用函数。注2：soundex是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。

SELECT SUBSTRING(prod_name,5,6) AS prod_new_id FROM products;SELECT LEFT(prod_name,2) AS left_name FROM products;SELECT LENGTH(prod_name) AS name_length FROM products;SELECT LTRIM(prod_name) FROM products;-- 寻找顾客联系方式发音为Y San的顾客SELECT * FROM customers WHERE soundex(cust_contact) = soundex('Y San');

（2）日期和时间处理函数

-- 寻找order_date为2012年的数据-- Oracle-- 方法1SELECT order_num FROM Orders WHERE to_number(to_char(order_date, 'YYYY')) = 2012;-- 方法2SELECT order_num FROM Orders WHERE order_date BETWEEN to_date('01-01-2012') AND to_date('12-31-2012');-- MySQLSELECT order_num FROM Orders WHERE YEAR(order_date) = 2012;

具体列出MySQL的部分日期函数

函数说明now()获取当前日期+时间(date + time)current_timestamp()获取当前时间戳date_format(str,format)；str_to_date(str,format)date -> str；str -> dateto_days(date)；from_days(days)date -> 天数；天数 -> datetime_to_sec(time)；sec_to_time(sec)time -> sec；sec -> timetimediff(time1,time2) datediff(date1,date2)日期相减date_add(date,interval expr UNIT)加exper UNIT

SELECT now()SELECT CURRENT_TIMESTAMP-- 2019-02-14 15:22:00SELECT DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s')SELECT STR_TO_DATE('20190214 15:22:00','%Y%m%d %H:%i:%s');SELECT DATE_ADD(now(),INTERVAL 1 day)

（3）数值处理函数

函数说明ABS(x)绝对值COS(x) SIN(x)TAN(x)余弦值 正弦值 正切值EXP(x)指数值PI()圆周率SQRT(x)平方根CEIL(x) FLOOR(x) ROUND(x)最大整数值 最小整数值 四舍五入TRUNCATE(x,y)保留y位小数值RAND()0~1随机数MOD(x,y)x mod y

SELECT ABS(-11);-- 0.5SELECT COS(PI()/180*60) SELECT SIN(PI()/180*30)-- 6SELECT ceil(5.1)-- 5SELECT FLOOR(5.1)-- 5SELECT ROUND(5.1)-- 1.12SELECT TRUNCATE(1.12345,2)

九 汇总数据1 聚合函数

函数说明AVG()某列的平均值，忽略NULL值COUNT()某列的行数MAX() MIN()某列最大值 最小值SUM()某列求和

SELECT AVG(prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';-- COUNT(*)：计算NULL值-- COUNT(column)：忽略NULL值SELECT COUNT(*) AS num_cust FROM Customers;-- MAX用于找出最大数值或日期值，如果用于文本则找出排序后的最后一行，忽略NULL值。SELECT MAX(prod_price) AS max_price FROM Products;-- 返回订单中所有物品数量之和，忽略NULL值。SELECT SUM(quantity) AS items_ordered FROM OrderItems WHERE order_num = 20005;

2 聚合不同值默认聚合所有值DISTINCT则聚合不同的值

SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';

3 组合聚合函数

SELECT COUNT(*) AS num_items,MIN(prod_price) AS price_min,MAX(prod_price) AS price_max, AVG(prod_price) AS price_avgFROM Products;

十 分组数据1 创建分组GROUP BY子句可包含任意数目的列，因而可以对分组进行嵌套，进行更细致的分组。如果在GROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总。GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。大多数SQL实现不允许GROUP BY列带有长度可变的数据类型。SELECT语句中的每一列都必须在GROUP BY子句中给出。如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

-- 安装vend_id分组SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id;SELECT vend_id,prod_name,COUNT(vend_id) AS count FROM products GROUP BY vend_id,prod_name;

2 过滤分组

-- 对分组后的结果进行排序SELECT cust_id, COUNT(*) AS orders FROM Orders WHERE prod_price >= 4 GROUP BY cust_id HAVING COUNT(*) >= 2;

3 分组和排序

ORDER BYGROUP BY对产出的输出排序对行分组，但输出可能不是分组的顺序任意列都可以(甚至非检出的列也行)只可能使用选择列或表达式列，而且必须使用每个选择列表达式不一定需要如果与聚集函数一起使用列（或表达式），则必须使用

-- 由于分组后的顺序不确定，所以最后加上ORDER BY排序SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items, order_num;

4 SELECT子句顺序

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

十一 子查询1 相关概念查询（Query）：任何SQL语句都是查询，一般指的是SELECT。子查询（SubQuery）：嵌套在其他查询中的查询。完全限定列名：表明.列名2 子查询

-- 查看哪位顾客购买了RGAN01商品SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num    FROM OrderItems    WHERE prod_id = 'RGAN01');

注1：子查询的SELECT语句只能查询单个列。注2:子查询可能影响性能。

3 计算字段使用子查询

-- 查询顾客购下了多少订单SELECT cust_name,	   cust_state,	  (SELECT COUNT(*)       FROM Orders        WHERE Orders.cust_id = Customers.cust_id) AS ordersFROM CustomersORDER BY cust_name;

十二 联结表1 相关概念（1）关系表Vendors表包含所有供应商信息，每个供应商占一行，具有唯一的标识。此标识称为主键（primary key），可以是供应商ID或任何其他唯一值。Products表只存储产品信息，除了存储供应商ID（Vendors表的主键）外，它不存储其他有关供应商的信息。Vendors表的主键将Vendors表与Products表关联，利用供应商ID能从Vendors表中找出相应供应商的详细信息。好处：供应商信息不重复，节省时间和空间。如果供应商信息变动只需要更新Venders表中的单个记录。由于数据不重复，数据一致，处理数据和生成报表更简单。（2）可伸缩

能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称为可伸缩性好（scale well）。

2 创建联结（1）笛卡尔积笛卡儿积（cartesian product）：由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

SELECT vend_name,prod_name,prod_priceFROM Venders,ProductsWHERE Venders.vend_id = Products.vend_id;

（2）内联积

-- 等同于上面，只是语法不同SELECT vend_name, prod_name, prod_priceFROM Vendors INNER JOIN ProductsON Vendors.vend_id = Products.vend_id;

（3）联结多个表

-- 显示订单20007中的物品SELECT prod_name, vend_name, prod_price, quantityFROM OrderItems, Products, VendorsWHERE Products.vend_id = Vendors.vend_idAND OrderItems.prod_id = Products.prod_idAND order_num = 20007;-- 子查询SELECT cust_name, cust_contactFROM CustomersWHERE cust_id IN (SELECT cust_id                  FROM Orders                  WHERE order_num IN (SELECT order_num                  				     FROM OrderItems                  					 WHERE prod_id = 'RGAN01'));-- 联结方式消除子查询                  SELECT cust_name,cust_contactFROM Customers,Orders,OrderItemsWHERE Customers.cust_id = Orders.cust_id  AND Orders.order_num = OrderItems.order_num  AND OrderItems.prod_id = 'RGAN01

注1：性能：DBMS在运行时关联指定的每个表，以处理联结。这种处理可能非常耗费资源，因此应该注意，不要联结不必要的表。注2：联结中表的最大数目：实际上许多DBMS都有限制

十三 创建高级联结1 概念表别名：使用AS关键字。联结（join）：自联结（self-join）、自然联结（natural join）和外联结（outer join）。2 自联结

-- 子查询SELECT cust_id, cust_name, cust_contactFROM CustomersWHERE cust_name = (SELECT cust_name                    FROM Customers                    WHERE cust_contact = 'Jim Jones');                    -- 子联结SELECT cust_id,cust_name,cust_contactFROM Customers AS c1,Customers AS c2WHERE c1.cust_name = c2.cust_name AND  c2.cust_contact = 'Jim Jones';

注：子联结比子查询查询快得多。

3 自然联结

-- 去除重复列SELECT C.*, O.order_num, O.order_date,	   OI.prod_id, OI.quantity, OI.item_priceFROM Customers AS C, Orders AS O, OrderItems AS OIWHERE C.cust_id = O.cust_id AND OI.order_num = O.order_num AND prod_id = 'RGAN01';

4 外联结

-- 对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客；-- 列出所有产品以及订购数量，包括没有人订购的产品；-- 计算平均销售规模，包括那些至今尚未下订单的顾客。-- 检索所有顾客及其订单，不包含没有订单的顾客SELECT Customers.cust_id, Orders.order_numFROM Customers INNER JOIN Orders ON Customers.cust_id = Orders.cust_id;-- 要检索包括没有订单顾客在内的所有顾客SELECT Customers.cust_id, Orders.order_numFROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id; -- 全外联结（full outer join），它检索两个表中的所有行并关联那些可以关联的行（可惜MySQL不支持）SELECT Customers.cust_id, Orders.order_numFROM Orders FULL OUTER JOIN Customers ON Orders.cust_id = Customers.cust_id;

5 集合函数的联结

-- 检索所有顾客及每个顾客所下的订单数SELECT Customers.cust_id,       COUNT(Orders.order_num) AS num_ordFROM Customers INNER JOIN Orders ON Customers.cust_id = Orders.cust_idGROUP BY Customers.cust_id;

十四 组合查询1 相关概念并（union）：SQL也允许执行多个查询（多条SELECT语句），并将结果作为一个查询结果集返回。任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询。2 创建组合查询（1）使用UNION

SELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_state IN ('IL','IN','MI') OR cust_name = 'Fun4All';-- 修改为UNIONSELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_state IN ('IL','IN','MI')UNIONSELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_name = 'Fun4All';

（2）UNION规则UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔。UNION中的每个查询必须包含相同的列、表达式或聚集函数。列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含转换的类型。（3）包含或取消重复的行

-- 取消重复：UNIONSELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_state IN ('IL','IN','MI')UNIONSELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_name = 'Fun4All';-- 包含重复：UNION ALLSELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_state IN ('IL','IN','MI')UNION ALLSELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_name = 'Fun4All';

（4）对组合结果排序

在用UNION组合查询时，只能使用一条ORDER BY子句，它必须位于最后一条SELECT语句之后。

SELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_state IN ('IL','IN','MI')UNIONSELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_name = 'Fun4All'ORDER BY cust_name, cust_contact;

十五 插入数据1 插入数据

-- 插入完整行INSERT INTO Customers VALUES('1000000006',       'Toy Land',       '123 Any Street'       'New York',       'NY',       '11111',       'USA',       NULL,       NULL);-- 更安全的方式，同样是填充所有列INSERT INTO Customers(cust_id,          cust_name,          cust_address,          cust_city,          cust_state,          cust_zip,          cust_country,          cust_contact,          cust_email)VALUES('1000000006',       'Toy Land',       '123 Any Street',       'New York',       'NY',       '11111',       'USA',       NULL,       NULL);-- 插入部分行-- 省略了cust_contact和cust_email，这两列允许为NULL或者有默认值。INSERT INTO Customers(cust_id,          cust_name,          cust_address,          cust_city,          cust_state,          cust_zip,          cust_country)VALUES('1000000006',       'Toy Land',       '123 Any Street',       'New York',       'NY',       '11111',       'USA');       -- 插入检索出的数据，从CustNew检索出来并插入到CustomersINSERT INTOCustomers(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_countryFROM CustNew;

2 从一个表复制到另一个表

当需要测试新的SQL时，可以进行该操作复制一张新表进行测试。

-- 创建一个名为CustCopy的新表，并把Customers表的整个内容复制到新表中CREATE TABLE CustCopy ASSELECT * FROM Customers;-- 只复制表结构CREATE TABLE CustCopy LIKE Customers;CREATE TABLE CustCopy AS SELECT * FROM Customers where 1=2

十六 更新和删除数据1 更新

UPDATE Customers SET cust_contact = 'Sam Roberts',cust_email = 'kim@thetoystore.com'WHERE cust_id = '100000000005';

注：更新时不要省略过滤条件，除非你确定要更新所有行。

2 删除

DELETE FROM CustomersWHERE cust_id = '100000000005';-- 清空表TRUNCATE TABLE Customers;

注：删除时不要省略过滤条件，除非你确定要删除所有行。

十七 创建和操纵表1 创建表（1）MySQL数据格式

MySQL数据类型含义tinyint(m)1个字节 范围(-128~127)smallint(m)2个字节 范围(-32768~32767)mediumint(m)3个字节 范围(-8388608~8388607)int(m)4个字节 范围(-2147483648~2147483647)float(m.d)单精度浮点型 8位精度(4字节) m总个数，d小数位，如：float(5,3) 123.4567 => 123.457double(m,d)双精度浮点型 16位精度(8字节) m总个数，d小数位decimal(m,d)定点数为精确值，浮点值为近似值，m<65 是总个数，d<30 且 d<m 是小数位char(n)固定长度，最多255个字符，空格填充，查询时取出空格，占n字节。varchar(n)可变长度，最多65535个字符，占实际+1(<=255)或2(大于255)字节但不超过n，可直接创建索引。tinytext可变长度，最多255个字符text可变长度，最多65535个字符，占实际+2字节，需要指定前多少字符创建索引。mediumtext可变长度，最多2的24次方-1个字符longtext可变长度，最多2的32次方-1个字符date日期 '2008-12-2'time时间 '12:25:36'datetime日期时间 '2008-12-2 22:06:44'timestamp自动存储记录修改时间

注：整型取值范围如果加了 unsigned，则最大值翻倍

（2）创建

-- 基础CREATE TABLE Products(    prod_id CHAR(10) NOT NULL,    vend_id CHAR(10) NOT NULL,    prod_name CHAR(254) NOT NULL,    prod_price DECIMAL(8,2) NOT NULL,    prod_des TEXT NULL);-- 使用NULLCREATE TABLE Orders(    order_num INT NOT NULL,    order_date DATETIME NOT NULL,    cust_id CHAR(10) NOT NULL);CREATE TABLE Vendors(    vend_id CHAR(10) NOT NULL,    vend_name CHAR(50) NOT NULL,    vend_address CHAR(50) ,    vend_city CHAR(50) ,    vend_state CHAR(5) ,    vend_zip CHAR(10) ,    vend_country CHAR(50));-- 指定默认值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);-- 创建Tasks表CREATE TABLE IF NOT EXISTS Tasks (    task_id INT(11) NOT NULL AUTO_INCREMENT,    subject VARCHAR(45),    start_date DATE,    end_date DATE,    description VARCHAR(200),    PRIMARY KEY (task_id)) ENGINE = InnoDB;

2 更新表

-- 新增字段ALTER TABLE VendersADD vend_phone CHAR(20);-- 删除字段ALTER TABLE VendersDROP COLUMN vend_phone;

3 删除表

DROP TABLE CustCopy;

4 重命名

ALTER TABLE CustCopy RENAME TO CustCopyTest;

十八 使用视图1 视图（1）基础

SELECT cust_name, cust_contactFROM Customers, Orders, OrderItemsWHERE Customers.cust_id = Orders.cust_id AND OrderItems.order_num = Orders.order_num AND prod_id = 'RGAN01'; -- ProductCustomers为视图SELECT cust_name, cust_contactFROM ProductCustomersWHERE prod_id = 'RGAN01';

（2）好处重用SQL语句简化复杂的SQL操作。使用表的一部分而不是整个表。保护数据。授予用户访问表的特定部分的权限，而不是整个表的访问权限。更改数据格式和表示。（3）视图的规则和限制与表一样，视图必须唯一命名创建视图，必须具有足够的访问权限。视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造视图。许多DBMS禁止在视图查询中使用ORDER BY子句。有些DBMS要求对返回的所有列进行命名，如果列是计算字段，则需要使用别名。视图不能索引，也不能有关联的触发器或默认值有些DBMS把视图作为只读的查询，这表示可以从视图检索数据，但不能将数据写回底层表。有些DBMS允许创建这样的视图，它不能进行导致行不再属于视图的插入或更新。2 创建视图

-- 创建视图简化联结CREATE VIEW ProductCustomers ASSELECT cust_name, cust_contact, prod_idFROM Customers, Orders, OrderItemsWHERE Customers.cust_id = Orders.cust_id AND OrderItems.order_num = Orders.order_num; SELECT cust_name, cust_contactFROM ProductCustomersWHERE prod_id = 'RGAN01';-- 重新格式化检索出的数据SELECT CONCAT(RTRIM(vend_name),' (',RTRIM(vend_country),')') AS vend_titleFROM VendorsORDER BY vend_name;SELECT *FROM VendorLocations;-- 使用视图和计算字段CREATE VIEW OrderItemsExpanded ASSELECT order_num,        prod_id,        quantity,        item_price,        quantity*item_price AS expanded_priceFROM OrderItems;SELECT *FROM OrderItemsExpandedWHERE order_num = 20008;

十九 存储过程1 使用场景

经常会有一些复杂的操作需要多条语句才能完成。例如以下的情形：

为了处理订单，必须核对以保证库存中有相应的物品。如果物品有库存，需要预定，不再出售给别的人，并且减少物品数据以反映正确的库存量。库存中没有的物品需要订购，这需要与供应商进行某种交互。关于哪些物品入库（并且可以立即发货）和哪些物品退订，需要通知相应的顾客。2 好处通过把处理封装在一个易用的单元中，可以简化复杂的操作。由于不要求反复建立一系列处理步骤，因而保证了数据的一致性。简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，那么只需要更改存储过程的代码。因为存储过程通常以编译过的形式存储，所以DBMS处理命令的工作较少，提高了性能。存在一些只能用在单个请求中的SQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

简言之，简单、安全、高性能。

3 执行存储过程

-- EXECUTE 存储过程(args)-- 验证传递数据、生成主键唯一ID、将新产品插入到Products表中EXECUTE AddNewProduct(     'JTS01',    'Stuffed Eiffel Tower',    6.49,    'Plush stuffed toy with the text La➥Tour Eiffel in red white and blue' );

4 创建存储过程（1）基础创建和调用

-- 创建删除商品的存储过程mysql> delimiter $$　mysql> CREATE PROCEDURE delete_product(IN p_prod_id int)    -> BEGIN    ->  	DELETE FROM products    ->  	WHERE prod_id = p_prod_id;    -> END $$-- 调用存储过程mysql> CALL delete_product(1);

（2）进阶

-- 存储过程体label1: BEGIN　　label2: BEGIN　　　　label3: BEGIN　　　　　　statements; 　　　　END label3 ;　　END label2;END label1-- 参数说明CREATE PROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])-- demomysql > DELIMITER // mysql > CREATE PROCEDURE update_and_return(INOUT p_prod_id int,IN p_test int)     -> BEGIN     ->	UPDATE products      ->	SET vend_id = p_test     ->	WHERE prod_id = p_prod_id;     ->	SELECT vend_id      ->	FROM products      ->	WHERE prod_id = p_prod_id;     ->	SET p_prod_id = 4;     -> END     -> //mysql > DELIMITER ;mysql > SET @Y = 3;mysql > CALL update_and_return(@Y,100); // 100mysql > SELECT @Y; // 4-- 条件语句mysql > DELIMITER //  mysql > CREATE PROCEDURE proc2(IN parameter int)       -> begin      -- declare 声明变量     -> declare var int;       -> set var=parameter+1;       -> if var=0 then      -> insert into t values(17);       -> end if;       -> if parameter=0 then      -> update t set s1=s1+1;       -> else      -> update t set s1=s1+2;       -> end if;       -> end;       -> //  mysql > DELIMITER ;-- 循环语句mysql > DELIMITER //  mysql > CREATE PROCEDURE proc4()       -> begin      ->   declare var int;       ->   set var=0;       ->   while var<6 do       ->     insert into t values(var);       ->     set var=var+1;       ->   end while;       -> end;       -> //  mysql > DELIMITER ;

二十 管理事务处理1 事务处理（1）定义

事务处理是一种机制，用来管理必须成批执行的SQL操作，保证数据库不包含不完整的操作结果。

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

事务处理用来管理INSERT、UPDATE和DELETE语句。不能回退SELECT语句，也不能回退CREATE或DROP操作。

（4）事务的隔离级别原子性：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。（5）事务并发问题脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表（设置串行化隔离级别）。

（6）MySQL事务隔离级别

InnoDB支持下列四种隔离级别；Myisam不支持事务，只支持表锁。

事务隔离级别脏读不可重复读幻读读未提交（read-uncommitted）是是是不可重复读（read-committed）否是是可重复读（repeatable-read）否否是串行化（serializable）否否否

（7）InnoDB引擎锁机制表锁：写锁：用写锁锁表，会阻塞其他事务读和写。读锁：用读锁锁表，会阻塞其他事务修改表数据。行锁：共享锁(S)：允许事务去读一行，阻止其他事务对该数据进行修改，select ... lock in share mode排它锁(X)：允许事务去读取更新数据，阻止其他事务对数据进行查询或者修改，select ... for update意向锁：搭配行锁，阻塞表锁；申请S锁时，先申请IS锁；申请X锁，先申请IS锁，由数据库自动完成。意向共享锁(IS)：当一个事务要给一条数据加S锁的时候，会先对数据所在的表先加上IS锁，成功后才能加上S锁意向排它锁(IX)：当一个事务要给一条数据加X锁的时候，会先对数据所在的表先加上IX锁，成功后才能加上X锁

注：意向锁搭配行锁使用来阻塞表锁，如：

会话A申请了S锁。会话B希望申请整个表的写锁。但是由于S锁会申请IS锁，所以会话B发现有IS锁存在则阻塞直到会话A结束。（8）锁算法Record Lock（以下简称RL）：单行锁定。Gap Lock（以下简称GL）：范围锁定，不包括当前行。Next-Key Lock（以下简称NKL）：Record+Gap，锁定一个范围，包括范围本身。2 控制事务处理BEGIN或START TRANSACTION：显式开启一个事务COMMIT：提交事务，并已对数据库进行的所有修改成为永久性的ROLLBACK：回滚将结束事务并撤销正在进行的所有未提交的修改SAVEPOINT identifier：事务中创建保留点RELEASE SAVEPOINT identifier：删除一个事务的保存点ROLLBACK TO identifier：回滚到一个事务保存点SET TRANSACTION：设置事务的隔离级别。其中innoDB存储引擎的隔离级别4个。

/*BEGIN：开启一个事务ROLLBACK：发生异常回滚COMMIT：事务正常提交*//*SET AUTOCOMMIT=0：禁止自动提交SET AUTOCOMMIT=1：开启自动提交*/

二十一 使用游标1 游标

游标（cursor）是一个存储在DBMS服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。

特性：

能够标记游标为只读。能控制可以执行的定向操作（向前、向后、第一、最后、绝对位置、相对位置等）。能标记某些列为可编辑，某些列为不可编辑。规定范围，使游标对创建它的特定请求（如存储过程）或对所有请求可访问。指示DBMS对检索出的数据（而不是指出表中活动数据）进行复制，使数据在游标打开和访问期间不变化。2 使用游标

-- 创建游标DECLARE CustCursor CURSORFORSELECT * FROM CustomersWHERE cust_email IS NULL-- 使用游标DECLARE @cust_id CHAR(10),    @cust_name CHAR(50),    @cust_address CHAR(50),    @cust_city CHAR(50),    @cust_state CHAR(5),    @cust_zip CHAR(10),    @cust_country CHAR(50),    @cust_contact CHAR(50),    @cust_email CHAR(255)-- 开启游标OPEN CustCursor    -- 获取下一行FETCH NEXT FROM CustCursorINTO @cust_id, @cust_name, @cust_address,    @cust_city, @cust_state, @cust_zip,    @cust_country, @cust_contact, @cust_emailWHILE @@FETCH_STATUS = 0	BEGINFETCH NEXT FROM CustCursor    INTO @cust_id, @cust_name, @cust_address,        @cust_city, @cust_state, @cust_zip,        @cust_country, @cust_contact, @cust_emailEND-- 关闭游标CLOSE CustCursor

二十二 高级SQL特性1 约束（1）主键任意两行的主键值不相同每行都具有一个主键值（不为NULL）包含主机键值得列不修改或更新。主键值不重用。（2）外键外键为表中一列，值为另一个表的主键外键可以防止意外删除，不过部分DBMS支持级联删除（cascading delete）

create table stu(sid int UNSIGNED primary key auto_increment,name varchar(20) not null)TYPE=InnoDB charset=utf8;create table sc(    scid int UNSIGNED primary key auto_increment,    sid int UNSIGNED not null,    score varchar(20) default '0',    index (sid),   --外键必须加索引    FOREIGN KEY (sid) REFERENCES stu(sid)         ON DELETE CASCADE -- 级联删除        ON UPDATE CASCADE -- 级联更新)TYPE=InnoDB charset=utf8;

（3）唯一约束（UNIQUE）表中包含多个唯一约束唯一约束列可以包含NULL唯一约束列可修改或更新唯一约束列的值可重复使用（4）默认值约束（DEFAULT value）

指定列的默认值

（5）非空约束（NOT NULL）

指定列不为空

2 索引（1）概述

索引用来排序数据以加快搜索和排序操作的速度。

可以在一个或多个列上定义索引，使DBMS保存其内容的一个排过序的列表。

开始创建索引前，应该记住以下内容：

索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。索引数据可能要占用大量的存储空间。并非所有数据都适合做索引。取值不多的数据（如州）不如具有更多可能值的数据（如姓或名），能通过索引得到那么多的好处。索引用于数据过滤和数据排序。可以在索引中定义多个列（例如，州 + 城市）。这样的索引仅在以州加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。

注：索引的效率随表数据的增加或改变而变化。许多数据库管理员发现，过去创建的某个理想的索引经过几个月的数据处理后可能变得不再理想了。最好定期检查索引，并根据需要对索引进行调整。

（2）使用

CREATE INDEX prod_name_indON PRODUCTS (prod_name);

（3）索引分类普通索引index :加速查找唯一索引主键索引：primary key ：加速查找+约束（不为空且唯一）唯一索引：unique：加速查找+约束 （唯一）联合索引primary key(id,name):联合主键索引unique(id,name):联合唯一索引index(id,name):联合普通索引全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。（4）索引类型hash类型的索引：查询单条快，范围查询慢btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

注：指定全文索引时，无需指定索引类型。

3 触发器

触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。触发器可以与特定表上的INSERT、UPDATE和DELETE操作（或组合）相关联。

触发器内的代码具有以下数据的访问权：

INSERT操作中的所有新数据；UPDATE操作中的所有新数据和旧数据；DELETE操作中删除的数据。

常见用途：

保证数据一致基于某个表的变动在其他表上执行活动进行额外的验证并根据需要回退数据计算 计算列的值或更新时间戳等

-- 模板DROP TRIGGER IF EXISTS triggerName; create trigger triggerName after/before insert/update/delete on tableNamefor each row begin  sql语句; end; -- 例：更新时间戳DROP TRIGGER IF EXISTS `upd_info`; create trigger upd_info after insert on StuCost for each row begin  update StuCostbyHour set HourCost = HourCost + new.Cost  where (TimeJD = hour(new.RecordTime) + 1) and date_format(new.RecordTime, '%Y-%m-%d') = date_format(RecordTime, '%Y-%m-%d'); end; 

4 安全对数据库管理功能（创建表、更改或删除已存在的表等）的访问；对特定数据库或表的访问；访问的类型（只读、对特定列的访问等）；仅通过视图或存储过程对表进行访问；创建多层次的安全措施，从而允许多种基于登录的访问和控制；限制管理用户账号的能力。二十三 更多

-- 删除重复行：只有last_name、first_name和sex都相同才视为相同CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl GROUP BY last_name, first_name, sex;DROP TABLE person_tbl;ALTER TABLE tmp RENAME TO person_tbl;