SQL基础知识

一 了解SQL

1 数据库基础

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

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 PostgreSQL
SELECT prod_name FROM Products LIMIT 5;

-- Oracle
SELECT prod_name FROM Products WHERE ROWNUM <= 5

/*
	分隔条
*/

-- 跳过前5条
-- MYSQL SQLite PostgreSQL
SELECT prod_name FROM Products LIMIT 5 OFFSET 5;

-- MYSQL
SELECT prod_name FROM Products LIMIT 5,	5;

-- Oracle
SELECT 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语句

-- AND
SELECT prod_id,prod_price,prod_name FROM Products WHERE vend_id = 'DLLL01' AND prod_price <=4;

-- OR
SELECT 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语句。
*/
-- IN
SELECT prod_name,prod_price FROM Products WHERE vend_id IN ('DLL01','BRS01') ORDER BY prod_name;

-- NOT
SELECT 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或多个字符，不匹配NULL
SELECT 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;

-- MYSQL
SELECT 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
-- 方法1
SELECT order_num FROM Orders WHERE to_number(to_char(order_date, 'YYYY')) = 2012;
-- 方法2
SELECT order_num FROM Orders WHERE order_date BETWEEN to_date('01-01-2012') AND to_date('12-31-2012');

-- MySQL
SELECT 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 -> date
to_days(date)；from_days(days)	date -> 天数；天数 -> date
time_to_sec(time)；sec_to_time(sec)	time -> sec；sec -> time
timediff(time1,time2) datediff(date1,date2)	日期相减
date_add(date,interval expr UNIT)	加exper UNIT

SELECT now()

SELECT CURRENT_TIMESTAMP

-- 2019-02-14 15:22:00
SELECT 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.5
SELECT COS(PI()/180*60) 
SELECT SIN(PI()/180*30)

-- 6
SELECT ceil(5.1)
-- 5
SELECT FLOOR(5.1)
-- 5
SELECT ROUND(5.1)

-- 1.12
SELECT 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 BY	GROUP 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 orders
FROM Customers
ORDER 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_price
FROM Venders,Products
WHERE Venders.vend_id = Products.vend_id;

（2）内联积

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

（3）联结多个表

-- 显示订单20007中的物品
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 = 20007;

-- 子查询
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 = 'RGAN01'));
-- 联结方式消除子查询                  
SELECT cust_name,cust_contact
FROM Customers,Orders,OrderItems
WHERE 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_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
                    FROM Customers
                    WHERE cust_contact = 'Jim Jones');
                    
-- 子联结
SELECT cust_id,cust_name,cust_contact
FROM Customers AS c1,Customers AS c2
WHERE 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_price
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 = 'RGAN01';

4 外联结

-- 对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客；
-- 列出所有产品以及订购数量，包括没有人订购的产品；
-- 计算平均销售规模，包括那些至今尚未下订单的顾客。

-- 检索所有顾客及其订单，不包含没有订单的顾客
SELECT Customers.cust_id, Orders.order_num
FROM Customers INNER JOIN Orders
 ON Customers.cust_id = Orders.cust_id;

-- 要检索包括没有订单顾客在内的所有顾客
SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
 ON Customers.cust_id = Orders.cust_id;
 
-- 全外联结（full outer join），它检索两个表中的所有行并关联那些可以关联的行（可惜MySQL不支持）
SELECT Customers.cust_id, Orders.order_num
FROM Orders FULL OUTER JOIN Customers
 ON Orders.cust_id = Customers.cust_id;

5 集合函数的联结

-- 检索所有顾客及每个顾客所下的订单数
SELECT 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;

十四 组合查询

1 相关概念

• 并（union）：SQL也允许执行多个查询（多条SELECT语句），并将结果作为一个查询结果集返回。
• 任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询。

2 创建组合查询

（1）使用UNION

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
 OR cust_name = 'Fun4All';

-- 修改为UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

（2）UNION规则

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

（3）包含或取消重复的行

-- 取消重复：UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

-- 包含重复：UNION ALL
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION ALL
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

（4）对组合结果排序

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

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE 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检索出来并插入到Customers
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;

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

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

-- 创建一个名为CustCopy的新表，并把Customers表的整个内容复制到新表中
CREATE TABLE CustCopy AS
SELECT * 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 Customers
WHERE 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.457
double(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
);

-- 使用NULL
CREATE 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 Venders
ADD vend_phone CHAR(20);

-- 删除字段
ALTER TABLE Venders
DROP COLUMN vend_phone;

3 删除表

DROP TABLE CustCopy;

4 重命名

ALTER TABLE CustCopy RENAME TO CustCopyTest;

十八 使用视图

1 视图

（1）基础

SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
 AND OrderItems.order_num = Orders.order_num
 AND prod_id = 'RGAN01';
 
-- ProductCustomers为视图
SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

（2）好处

• 重用SQL语句
• 简化复杂的SQL操作。
• 使用表的一部分而不是整个表。
• 保护数据。授予用户访问表的特定部分的权限，而不是整个表的访问权限。
• 更改数据格式和表示。

（3）视图的规则和限制

• 与表一样，视图必须唯一命名
• 创建视图，必须具有足够的访问权限。
• 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造视图。
• 许多DBMS禁止在视图查询中使用ORDER BY子句。
• 有些DBMS要求对返回的所有列进行命名，如果列是计算字段，则需要使用别名。
• 视图不能索引，也不能有关联的触发器或默认值
• 有些DBMS把视图作为只读的查询，这表示可以从视图检索数据，但不能将数据写回底层表。
• 有些DBMS允许创建这样的视图，它不能进行导致行不再属于视图的插入或更新。

2 创建视图

-- 创建视图简化联结
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; 

SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

-- 重新格式化检索出的数据
SELECT CONCAT(RTRIM(vend_name),' (',RTRIM(vend_country),')') 
AS vend_title
FROM Vendors
ORDER BY vend_name;

SELECT *
FROM VendorLocations;

-- 使用视图和计算字段
CREATE VIEW OrderItemsExpanded AS
SELECT order_num,
        prod_id,
        quantity,
        item_price,
        quantity*item_price AS expanded_price
FROM OrderItems;

SELECT *
FROM OrderItemsExpanded
WHERE 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 ] 参数名 数据类形...])

-- demo
mysql > 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); // 100
mysql > 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 CURSOR
FOR
SELECT * FROM Customers
WHERE 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 CustCursor
INTO @cust_id, @cust_name, @cust_address,
    @cust_city, @cust_state, @cust_zip,
    @cust_country, @cust_contact, @cust_email
WHILE @@FETCH_STATUS = 0	

BEGIN
FETCH NEXT FROM CustCursor
    INTO @cust_id, @cust_name, @cust_address,
        @cust_city, @cust_state, @cust_zip,
        @cust_country, @cust_contact, @cust_email
END
-- 关闭游标
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_ind
ON 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 tableName
for 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;

参考：

• MySQL的四种事务隔离级别

• 《SQL必知必会》

• Runoob的MySQL教程