SQL必知必会梳理。

第1课 SQL基础

一、什么是SQL

1.数据库(database)

保存有组织的数据的容器(通常是一个文件或者一组文件)

2.数据库软件(DBMS,数据库管理系统)

用来创建和操控数据库

3.表(table)

某种特定类型数据的结构化清单,同一个数据库中表名唯一

4.模式(schema)

关于数据库和表的布局及特性的信息

5.列(column)

表中的一个字段,所有表都是由一个或者多个列组成(将数据库想象成一个表格,网格中的每一列存储着一条特定的信息)

6.数据类型(datatype)

所容许的数据类型。每个表列都有相对应的数据类型,它限制或容许该列中存储的数

7.行(row)

表中的一个记录

8.主键(primary key)

一列(或者一组列),其值能够唯一区分开每个行

9.SQL(structured query language)

结构化查询语言,专门用来与数据库通信

二、MYSQL简介及一些基础语句

1.MySQL

MySQL是一种DBMS,database management systerm,数据库软件,基于客户机-服务器运行

2.Navicat For MySQL

一款简单好用的数据库可视化软件(方便,点击连接,输入主机名、端口、用户名、密码等即可,点击表及可视),也可以用Sequel Pro

3.关键字(key word)

作为Mysql语言组成部分的一个保留字,不允许用关键字命名一个表或一个列。

4.选择数据库
USE crashcource
# 选择使用crashcource数据库
5.返回可用数据库的一个列表
SHOW DATABASES
6.获取一个数据库内的表的列表
SHOW TABLES
7.获取表列名
SHOW COLUMNS FROM CUSTOMERS
# 返回字段名、数据类型、是否允许NULL、键信息、默认值以及其他信息
8.自动增量

MySQL可以自动为下一个行分配下一个可用编号,不用在添加一行时手动分配唯一值。启用需在用CREATE语句创建表时将它作为表定义的组成部分

9.DESCRIBE语句

DESCRIBE作为SHOW COLUMNS FROM的一种快捷方式。 比如DESCRIBE customers;SHOW COLUMNS FROM customers;的一种快捷方式

10.支持的其他SHOW语句
SHOW STATUS# 显示广泛的服务器状态信息
SHOW CREATE DATABASE
SHOW CREATE TABLES
# 分别用来显示创建特定数据库或者表的MySql语句
SHOW GRANTS
# 显示授予用户(所有用户或特定用户)的安全权限
SHOW ERRORS
SHOW WARNINGS
# 用来显示服务器错误或者警告

第2课 检索数据

2.3 检索多个列
SELECT column1, column2, column3 
FROM tableName;
2.4 检索所有的列
SELECT * FROM products;

注: 1. 缺点:使用通配符(*),除非确实需要查询表的每一列,否则可能会降低检索和应用的性能. 2. 优点:能检索出表的不明确的列。

2.5 检索不同的值
SELECT DISTINCT vend_id FROM products;

注: 1. DISTINCT关键字作用于作用于其后的所有的列,不仅仅是其后的一列。 2. 如:

SELECT DISTINCT vend_id, prod_price FROM products;

那么这两列vend_id, prod_price都相同,才会合并。

2.6 限制结果
SELECT prod_name 
FROM products 
LIMIT 5; 
# 返回结果只有5行。

SELECT prod_name 
FROM products 
LIMIT 5 
OFFSET 5;
# 返回6,7,8,9,10这5行数据。

注: 1. 第一个被检索的行是第0行,而不是第1行。因此,LIMIT 1 OFFSET 1 会检索第2行,而不是第1行; 2. MySQL和MariaDB支持简化版的LIMIT 4 OFFSET 3语句,即LIMIT 3,4

2.7 使用注释
  1. 单行注释: #
  2. 多行注释: /*. ... */

第3课 排序检索数据

使用SELECT子句的ORDER BY子句,根据需要检索排序出的数据。

在同时使用ORDER BY和WHERE子句时,应该让ORDER BY位于WHERE之后,否则会产生错误。

3.1 排序数据

使用Order by语句排序Select检索出来的语句, Order by子句取一个或多个列的名字。

SELECT prod_name, prod_price 
FROM products 
ORDER BY prod_price;

注:Order by子句, 必须是select的最后一条语句。

3.2 按多个列排序
SELECT prod_name, prod_price, prod_id 
FROM products 
ORDER BY prod_price, prod_name;
3.3 按列位置排序
SELECT prod_name, prod_price, prod_id 
FROM products 
ORDER BY 2, 3;

注: order by 后面可以接数字,比如:2代表的是prod_price, 3代表的是prod_id。不推荐使用这种方式。

3.4 指定排序方向

ASC 升序(默认),DESC 降序。

SELECT prod_name, prod_price, prod_id 
FROM products 
ORDER BY prod_price DESC, prod_name;

第4课 过滤数据

常见的WHERE子句操作符

= 等于    
<> 不等于   
!= 不等于  
=< 小(等)于  
>= 大(等)于  
BETWEEN AND 两者之间
注意:在执行匹配操作时候不区分大小写
4.2.3 范围值检查
SELECT prod_name, prod_price, prod_id 
FROM products 
WHERE prod_price BETWEEN 4 AND 10;
4.2.4 空值检查
SELECT * FROM customers WHERE cust_email IS NULL; # 空判断过滤数据
SELECT * FROM customers where cust_email IS NOT NULL; # 非空判断过滤数据

第5课 高级数据过滤

5.1.3 组合WHERE子句
  1. OR操作符和AND操作符。

    SELECT prod_name, prod_price, vend_id 
    FROM products 
    WHERE vend_id = '1001' OR vend_id = '1005' AND prod_price >= 10;

结果:

prod_name prod_price vend_id
.5 ton anvil 5.99 1001
1 ton anvil 9.99 1001
2 ton anvil 14.99 1001
JetPack 1000 35.00 1005
JetPack 2000 55.00 1005

分析:

OR操作符和AND操作符优先级不同。AND操作符的优先级 >OR操作符的优先级。所以上面的sql变成了:

SELECT prod_name, prod_price, vend_id 
FROM products 
WHERE vend_id = '1001' OR (vend_id = '1005' AND prod_price >= 10 );
5.2 IN操作符
SELECT prod_name, prod_price, vend_id 
FROM products 
WHERE vend_id IN ('1001', '1003') 
ORDER BY prod_price DESC;

IN操作符的优点 1. IN操作符一般比一组OR操作符更快 2. IN最大的优点是:可以包含其他SELECT语句,能够动态的建立WHERE子句

5.3 NOT操作符

NOT操作符用作否定其后所跟的任何条件。

SELECT * from products 
WHERE NOT vend_id = 1001 
AND prod_name = 'Safe';
prod_id vend_id prod_name prod_price prod_desc
SAFE 1003 Safe 50.00 Safe with combination lock

注:NOT在这里只能作用于vend_id

第6课 使用通配符(wildcard)进行过滤

6.1.1 百分号(%)通配符

在搜索串中,%表示任何字符出现任意次数。 代表搜索模式中给定位置0个、1个或多个字符。 % 不能匹配NULL的行。

SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE 'jet%';
# 检索任意jet开头的词,且搜索模式区分大小写,与Jet并不匹配
SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE '%anvil%';
# 包括anvil的所有值
SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE 'a%s';
# %代表搜索模式中给定位置的0个、1个或多个字符
6.1.2 下划线(_)通配符

在搜索串中,下划线(_)只匹配单个字符,而不是多个字符。只能是一个字符。

SELECT prod_id, prod_name 
FROM products 
WHERE prod_name 
LIKE 'JetPack _000';
6.2 使用通配符的技巧
  1. 要知道,使用通配符可能比其他的搜索耗费更长的时间,对数据库的压力更大。
  2. 不要过度使用通配符,在其他操作符能达到效果的情况下,尽量不要使用通配符。
  3. 尽量不要把通配符放在搜索模式的开始处,把通配符置于开始处,搜索起来最慢的。

第6+课 正则表达式搜索

1.正则表达式

用来匹配文本的特殊的串;所有种类的程序设计语言、文本编辑器、操作系统等都支持正则表达式。

2.基本字符匹配

SELECT prod_name
FROM products
WHERE prod_name REGEXP'1000'
ORDER BY prod_name;
# REGEXP后面跟的就是正则表达式(与正文1000匹配的一个正则表达式)处理

SELECT prod_name
FROM products
WHERE prod_name REGEXP'.000'
ORDER BY prod_name;
# 其中.表示匹配任意一个字符

3.使用BINMARY区分大小写

where prod_name REGEXP BINMAY 'JetPack  .000'
# MySQL本身不区分大小写

4.进行OR匹配

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;

5.匹配几个字符之一

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name;
# 意思是匹配1或者2或者3,[]是另一种形式的OR语句,与[1|2|3]一致
# [^123]匹配除这些字符以外的东西

6.匹配范围

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name;

7.匹配特殊字符

SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\.'
ORDER BY prod_name;
# \\为前导,转义符
# 转义符的其他用处:
# \\f  换页   \\n  换行  \\r  换车   
# \\t  制表   \\v   纵向制表   \\\  转义\

8.匹配字符类(character class)

9.匹配多个实例

10.定位符

定位元字符:

第7课 创建计算字段

7.1 计算字段

计算字段是运行时在SELECT语句内创建的。不同于数据库中表的列。基本上与列(column)的意思相同,经常互换使用,术语字段通常用在计算字段的连接上。

7.2 拼接字段

将表中的列数据,拼接到一起。将值联结在一起(将一个值附加到另一个值), 构成单个值。如:prod_name(prod_price).

拼接的操作符: concat(prod_name, '(', prod_price, ')')

SELECT Concat(vend_name,'  (',vend_country, ')')
FROM vendors
ORDER BY vend_name;

Trim函数: 1. RTRIM(String)函数去掉字符串右边的空格。 2. LTRIM(String)函数去掉字符串左边的空格。 3. TRIM(String)函数去掉字符串左边和右边空格。

SELECT Concat(RTrim(vend_name),'  (',RTrim(vend_country), ')')
FROM vendors
ORDER BY vend_name;
# LTrim()去掉左边多余的空格

# AS别名。重新为列起一个新的名字。
SELECT concat(prod_name, '(', vend_id, ')') 
AS price_info 
FROM products;

SELECT Concat(RTrim(vend_name),'  (',RTrim(vend_country), ')')
AS vend_title
FROM vendors
ORDER BY vend_name;
7.3 执行算术计算

算术运算符:加,减,乘,除

# 计算商品共卖出多少钱:
SELECT prod_id, quantity, item_price, quantity * item_price 
AS expanded_price 
FROM orderitems 
WHERE order_num = 20005;

省略FROM语句可以简单地访问和处理表达式,如SELECT 3*2;将返回6,SELECT Trim('abc ');将返回abc,SELECT Now();返回当前地日期和时间。

第8课 使用数据处理函数

8.1 函数

SQL中函数,各个DBMS所支持的都不同,如果使用过多的函数,可能会带来可移植问题。

8.2 使用函数

大部分SQL实现支持以下类型的函数 1. 用于处理文本字符串(如删除,填充值,转换大小写等)的文本函数。 2. 用于在数值数据上进行的算术操作(如返回绝对值,进行代数运算)的数值函数。 3. 用于处理日期和时间值,并从这些值中提取特定成分(如返回两个日期之差,检查日期有效性)的日期和时间函数。 4. 返回DBMS正使用的特殊信息(如返回用户登录信息)的系统函数。

8.2.1 文本处理函数

常见的文本处理函数

函数 说明
LEFT()(或使用子字符串函数) 返回字符串左边的字符
RIGHT()(或使用子字符串函数) 返回字符串右边的字符
LOWER() 将字符串转换为小写
UPPER() 将字符串转换为大写
LTRIM() 去掉字符串左边的空格
RTRIM() 去掉字符串右边的空格
TRIM() 去掉字符串左边和右边的空格
SOUNDEX() 返回字符串的SOUNDEX值(读音相同)
LENGTH()(也使用DATALENGTH()LEN()) 返回字符串长度

解释文本处理函数: SOUNDEX()

# SOUNDEX()函数是一种和根据读音相同来比较的,
# 是一个将任何文本串转换成其语音表示的字母数字模式的算法,姓名模糊查询)
SELECT cust_name, cust_contact 
FROM Customers 
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');
8.2.2 日期和时间处理函数

非常遗憾的是:日期和时间处理函数在SQL实现中差别很大,可移植性非常差。所以,关于具体DBMS支持的日期-时间处理函数,请参阅相应的文档。

AddDate() 增加一个日期(天、周等)
AddTime() 增加一个时间(时、分等)
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期运算函数
Date_Format()返回一个格式化的日期或时间串
Day() 返回一个日期的天数部分
DayDfWeek()对于一个日期,对应返回星期几
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Month() 返回一个时间的月份部分
Now() 返回当前时间
Second() 返回一个时间的秒部分
Time() 返回一个日期时间的时间部分
Year() 返回一个日期的年份部分
8.2.3 数值处理函数

数值处理函数仅处理数值数据,这些函数一般主要用于代数、三角或几何运算。数值处理函数在SQL差别不大。

函数 说明
ABS() 返回一个数的绝对值
COS() 返回一个角度的余弦
EXP() 返回一个数的指数值
PI() 返回圆周率
SIN() 返回一个角度的正弦
SQRT() 返回一个数的平方根
TAN() 返回一个角度的正切

第9课 汇总数据

9.1 聚集函数
函数 说明
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和
9.1.1 AVG()函数

AVG()函数计算表中列的平均值。AVG()只能用来确定特定数值列的平均值。AVG()函数忽略列值为NULL的行。

SELECT avg(prod_price) 
AS avg_price 
FROM products;
9.1.2 COUNT()函数

COUNT() 函数有两种实现方式: 1. 使用count(*),对表中行的数目进行计数。无论是空值(NULL)或非空值。 2. 使用count(columnName)对表特定列具有值进行计数,忽略NULL值。 3. SELECT count(vend_state) from vendors; 语句忽略NULL值。

9.1.3 MAX()函数

MAX()返回指定列中的最大值。

select max(prod_price) as max_price from products;
9.1.4 MIN()函数

MIN()的功能正好与MAX()功能相反,它返回指定列的最小值。

9.1.5 SUM()函数

SUM()用来返回指定列值的和(总计)。

SELECT SUM(item_price) AS sum_price FROM orderitems WHERE order_num = 20005;

SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;

注: 利用标准的算术运算符,所有的聚集函数都可以执行多个列上的计算。

9.2 聚集不同的值

使用关键字:DISTINCT关键字,过滤相同的值。

SELECT AVG(distinct prod_price) 
AS avg_price 
FROM products 
WHERE vend_id = 'DLL01';
9.3 组合聚集函数

select 语句可以根据需要包含多个聚集函数。

SELECT COUNT(*) AS num_items, 
MIN(prod_price) AS min_price, 
MAX(prod_price) AS max_price, 
SUM(prod_price) AS sum_price 
FROM products;

第10课 分组数据

将数据分组,使用GROUP BY子句和HAVING子句。

10.1 数据分组

目前为止的所有计算都是在表的所有数据或匹配特定的WHERE子句的数据上进行的。没有对应的分组概念。

SELECT COUNT(*) 
AS num_prods 
FROM products 
WHERE vend_id = 'DLL01';

如果查询每个供应商提供的商品数,需要使用分组,GROUP BY子句和HAVING子句

10.2 创建分组

分组是使用SELECT语句的GROUP BY实现的。

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

使用GROUP BY子句分组数据,然后对每个组而不是整个结果集进行聚集。

使用GROUP BY子句前,需要了解一些重要的规定:
10.3 过滤分组

GROUP BY新建分组,使用HAVING子句过滤分组。如:查询至少有两个订单的顾客。

WHERE子句和HAVING子句的区别:

分析:首先,WHERE语句先选出价格大于4的商品,然后按照vend_id来进行分组,再对分组进行过滤。

10.4 分组和排序
ORDER BY GROUP BY
对产生的输出排序 对行分组,但输出可能不是分组的顺序
任意列都可以使用 只可能使用选择列或表达式列,而且必须使用每个选择列表达式
不一定需要 如果与聚集函数一起使用列(或表达式),则必须使用

一般使用GROUP BY子句后,也应该使用ORDER BY子句。

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

第11课 使用子查询

11.2 利用子查询进行过滤

假如需要列出订购物品RGAN01的所有顾客,应该怎样检索? 1. 从订单详情表(OrderItems)中查询订购物品GRANO1的所有订单编号。 2. 根据订单编号,从表(Orders)中查询顾客ID 3. 根据顾客ID,从表(Customers)查询顾客信息。

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 = 'GRANO1'));

作为子查询的SELECT语句只能查询单个列。企图查询多个列,是错误的。

11.3 作为计算字段使用子查询

假如需要显示Customers表中每个顾客的订单总数,应该怎样写? 1. 从Customers表中检索顾客列表。 2. 对于检索的每个顾客,统计在Orders表中的数目。

SELECT cust_name, cust_state, 
(SELECT COUNT(*) FROM Orders 
WHERE Orders.cust_id = Customers.cust_id) 
AS orders
FROM Customers ORDER BY cust_name;
# 该子查询检索到每个顾客执行一次

第12课 联结表

12.1.1 关系表

关系表的设计就是把信息分成多个表,一类数据一个表。各个表通过共同的值进行关联。

12.2 创建联结
12.2.3 联结多个表
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;

第13课 创建高级联结

13.1 使用表别名

使用表别名优点: 1. 缩短SQL语句 2. 允许在一条SELECT语句中多次使用相同的表

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 = 'RGAN01';
13.2 使用不同类型的联结

四种联结: 1. 内联结或等值联结 2. 自联结(self-join) 3. 自然联结(natural join) 4. 外联结(outer join)

SELECT * FROM A  
INNER JOIN B ON A.book_id=B.book_id;

SELECT * FROM A  
LEFT JOIN B ON A.book_id=B.book_id;

SELECT * FROM A  
RIGHT JOIN B ON A.book_id=B.book_id;

SELECT * FROM A  
FULL JOIN B ON A.book_id=B.book_id;

SELECT pv.pageid, u.age FROM page_view pv JOIN user u ON (pv.userid = u.userid);
13.2.1 自联结

问:查询与Y Lee同一公司的所有顾客。 1. 使用子查询

SELECT cust_name, cust_address 
FROM customers 
WHERE cust_name = (SELECT cust_name FROM customers WHERE cust_contact = 'Y Lee');
  1. 使用自联结

    SELECT C1.cust_name, C2.cust_address 
    FROM customers AS C1, customers AS C2 
    WHERE C1.cust_name = C2.cust_name AND C1.cust_contact = 'Y Lee';

用自联结而不用子查询: 许多DBMS处理联结远比处理子查询快得多。

13.2.2 自然联结
13.2.3 外联结

许多联结将一个表中的行和另一个表中的行相关联,但有时候需要包含没有关联行的那些行。如以下工作: 1. 对每个顾客订单进行计数,包括至今尚未下订单的顾客。 2. 列出所有产品以及订购数量,包括没人订购的产品。

上述举例,包括了那些相关表中没有关联的行。这种联结称为外联结。

检索所有顾客及其订单

  1. 内联结(inner join)

    SELECT Customers.cust_id, Orders.order_num 
    FROM Customers 
    INNER JOIN Orders 
    ON Customers.cust_id = Orders.cust_id;
  2. 外联结(left outer join)

    SELECT Customers.cust_id, Orders.order_num 
    FROM Customers 
    LEFT OUTER JOIN Orders
    ON Customers.cust_id = Orders.cust_id;

外联结的使用的是left outer join是从左边的表(Customers)中选择行,所以如果右表没有对应的id则补充。要注意on后面的表Customers和Orders顺序。 3. 外联结(right outer join)

SELECT Customers.cust_id, Orders.order_num 
FROM Customers 
RIGHT OUTER JOIN Orders 
ON Orders.cust_id = Customers.cust_id;
  1. 全外联结(full outer join)

    SELECT Customers.cust_id, Orders.order_num 
    FROM Orders 
    FULL OUTER JOIN Customers
    ON Orders.cust_id = Customers.cust_id;

两个表的行的最大集合。

13.3 使用带聚集函数的联结

检索所有顾客及每个顾客所下的订单数

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;

13.4 使用联结和联结条件

联结和联结的使用要点: 1. 注意使用的联结类型。有内联结,外联结等 2. 联结语法每个DBMS可能不一样。 3. 保证使用正确的联结条件,否则返回不正确的数据。 4. 应该总是使用联结条件,否则会得到笛卡尔积。 5. 在一个联结中可以包含多个表,甚至可以对每个联结采用不同的联结类型。虽然这样做是合法的,一般也很有用,但应该在一起测试它们 前分别测试每个联结。这会使故障排除更为简单。

第14课 组合查询

利用UNION操作符将多条SELECT语句合成一个结果集。

14.1 组合查询

SQL允许执行多条查询语句(多条SELECT语句),并将结果作为一个查询结果集返回。

主要有两种情况需要使用组合查询: 1. 在一个查询中,从不同的表返回结构数据。 2. 对一个表执行多个查询,按一个查询返回数据。

注:一般多个Where子句的Select语句都可以作为一个组合查询。也就是说将Where子句拆分开来。

14.2 创建组合查询

用操作符UNION操作符组合多条SELECT语句,将他们的结果组合成一个结果集。

14.2.1 使用UNION操作符

查询Illinois、Indiana和Michigan等美国几个州的所有顾客的报表,和不管位于哪个州的所有的Fun4All

  1. 使用UNION操作符查询

    SELECT cust_name, cust_contact, cust_email FROM Customers
    WHERE cust_state IN ('Illinois','Indiana','Michigan')
    UNION
    SELECT cust_name, cust_contact, cust_email FROM Customers
    WHERE cust_name = 'Fun4All';
  2. 使用WHERE子句

    SELECT cust_name, cust_contact, cust_email FROM Customers
    WHERE cust_state IN ('Illinois','Indiana','Michigan') OR cust_name = 'Fun4All';

UNIONWHERE子句比较:

  1. 对于较复杂的过滤条件,或者从多个表(而不是一个表)中检索数据的情形,使用UNION可能会使处理更简单。
  2. 多数DBMS使用内部查询优化程序,使用Union关键字会在内部组合它们,所以性能几乎无差别。但使用Union操作符也请注意下性能问题。
14.2.2 UNION规则

Union非常好用,但使用组合前请注意下以下规则: 1. UNION必须由两条或两条以上的SELECT语句组成,语句之间用关键字UNION分隔。 2. UNION每次查询必须包含相同的列,表达式,聚集函数。(各个列不需要以相同的次序列出)。 3. 列数据类型必须兼容:类型不必完全相同,但必须是DBMS可以隐含转换的类型。

14.2.3 包含或取消重复的行

UNION从查询结果集中自动去除了重复的行。

使用关键字: Union All的会返回所有的匹配行(不进行去除重复的行)。 Union All操作符是Where不能替代的。

14.2.4 对组合查询结果排序

用Union组合查询时,只能使用一条Order by子句,它必须位于最后一条Select语句。

select cust_name, cust_contact, cust_id from customers 
where cust_state in ('MI', 'OH') 
union 
select cust_name, cust_contact, cust_id from customers 
where cust_contact = 'E Fudd' 
order by cust_id;

注意:union组合查询中order by子句的列必须在select后面有。且order by必须在语句最后,对整个结果集进行排序。

第15课 插入数据

利用SQL的INSERT语句将数据插入表中。

15.1.1 插入完整的行

使用INSERT语句时,请在表名括号内明确给定列名。

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);
15.1.3 插入检索出的数据

将SELECT语句的查询结果插入到表中。顾名思义,它是由一条INSERT语句和一条SELECT语句组成的。

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;

说明:SELECT,插入到表Customers中.

注意: 1. INSERT和SELECT不一定要求列名匹配。它使用的是列的位置,因此SELECT中的第一列(不管其列名)将用来填充表列中指定的第一列,第二列将用来填充表列中指定的第二列。 2. INSERT通常只插入一行。INSERT SELECT是个例外,它可以用一条INSERT插入多行,不管SELECT语句返回对少条语句,都会被插入到表中。

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

SELECT INTO将数据复制到一个新表中。

INSERT SELECT与SELECT INTO区别: 1. INSERT SELECT是导出数据,先SELECT出结果,然后插入到表中。SELECT INTO是导入数据。

SELECT * INTO CustCopy FROM customers;
# 创建一个名为CustCopy的新表。然后将整个customers表内容复制到表CustCopy中。

Mysql中语法是:

create table CustCopy AS select * from Customers;

使用select into注意点: 1. 任何select选项和子句都可以使用,包括where和order by。 2. 可利用联结从多个表插入数据(Mysql中未测试通过). 3. 不管从多少个表中检索数据,数据都只能插入到一个表中。

第16课 更新和删除数据

16.1 更新数据

UPDATE语句更新表中的数据。

UPDATE Customers 
SET cust_contact = 'Sam Roberts',cust_email = 'sam@toyland.com' 
WHERE cust_id = '10000006';

注意:UPDATE语句可能更新多个值。

16.2 删除数据

delete语句删除表中的数据。

DELETE FROM Customers WHERE cust_id = '10000006';

删除时外键考虑: 使用DELETE语句删除数据时,可能会有外键的影响。 比如:我要删除Products表中某个商品,但是该商品cust_id在订单表中有记录,作为订单表的外键。那么删除会失败。

16.3 更新和删除的指导原则
  1. 除非确实删除所有行或更新所有行,否则绝不能不使用where语句。
  2. 保证每个表都要有主键,尽可能像where子句那样使用它。
  3. 在UPDATE和DELETE语句使用WHERE子句前,应该先用SELECT进行测试,保证过滤条件是正确无误的。
  4. 使用强制实施引用完整性的数据库,这样DBMS将不允许删除其数据与其他表相关联的行。

第17课 创建和操纵表

17.1.1 表创建基础

利用create table创建表,必须给出下列信息: 1. 新表的名字,在关键字create table后给出. 2. 表列的名字和定义,用逗号隔开。 3. 有的DBMS还要指定表的位置。

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),
prod_desc text(1000), NULL
);
17.1.2 使用Null

创建表的时候,指定表的列为Not null,则表示,以后执行insert语句,该列必须有值,不接受null。

17.1.3 指定默认值

创建表的时候,可以为表的列提供默认值。

CREATE TABLE OrderItems
(
order_num INTEGER Not null,
order_item INTEGER Not null,
prod_id CHAR(10) Not null,
quantity INTEGER Not null default 1,
item_price DECIMAL(8,2) not null
);
17.2 更新表

使用alter table时,请考虑下列的事: 1. 理想情况下,不要再表包含数据时对其进行更新。应该在表的设计过程中充分烤炉未来可能。 2. 所有的DBMS都允许给现有的表增加列,不过对所增加列的数据类型(以及NULL和Default的使用)有所限制. 3. 许多DBMS不允许删除或更改表中的列.

alter table Vendors add vend_phone CHAR(20);

复杂的表结构更改一般需要手动删除过程,它涉及以下步骤: 1. 用新的列布局创建一个新表; 2. 使用INSERT SELECT语句从旧表复制数据到新表。有必要的话,可以使用转换函数和计算字段; 3. 检验包含所需数据的新表; 4. 重命名旧表(如果确定,可以删除它); 5. 用旧表原来的名字重命名新表; 6. 根据需要,重新创建触发器、存储过程、索引和外键

17.3 删除表
drop table Customers;

注意:删除表不可撤销。请慎重

17.4 重命名表

每个DBMS对表重命名的支持有所不同,具体要参考具体的DBMS文档.

17.5 建表案例
CREATE TABLE model_update_info (
`SID` varchar(20) NOT NULL,
`VERSION` varchar(20) NOT NULL,
`MODEL_NM` varchar(20) NOT NULL,
`METRICS_NM` varchar(20) NOT NULL,
`METRICS_MEAN` double DEFAULT NULL,
`METRICS_STD` double DEFAULT NULL,
`CV_METHOD` varchar(40) NOT NULL,
PRIMARY KEY (`SID`,`VERSION`,`MODEL_NM`,`METRICS_NM`,`CV_METHOD`)
) 
CHARSET=utf8
;
CREATE TABLE `temp_yusimiao_airq_bydate` (
  `sid` varchar(20) NOT NULL,
  `date` varchar(50) NOT NULL,
  `aqi` double DEFAULT NULL,
  `co` double DEFAULT NULL,
  `no2` double DEFAULT NULL,
  `o3` double DEFAULT NULL,
  `pm10` double DEFAULT NULL,
  `pm25` double DEFAULT NULL,
  `so2` double DEFAULT NULL,
  `hour_hc` double DEFAULT NULL,
  `aqi_hc` double DEFAULT NULL,
  `co_hc` double DEFAULT NULL,
  `no2_hc` double DEFAULT NULL,
  `o3_hc` double DEFAULT NULL,
  `pm10_hc` double DEFAULT NULL,
  `pm25_hc` double DEFAULT NULL,
  `so2_hc` double DEFAULT NULL,
  UNIQUE KEY `uniquekey` (`sid`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='临时表,空气质量按站点/天汇总';

CREATE TABLE `task_message` (
  `Date` date NOT NULL,
  `Time` int(2) unsigned zerofill NOT NULL,
  `SID` varchar(45) NOT NULL,
  `Project` varchar(45) NOT NULL,
  `Version` varchar(45) NOT NULL,
  `Depend` varchar(45) DEFAULT NULL,
  `Data_status` varchar(45) DEFAULT NULL,
  `Calc_status` varchar(45) DEFAULT NULL,
  `Upload_status` varchar(45) DEFAULT NULL,
  `DS_start` datetime DEFAULT NULL,
  `DS_end` datetime DEFAULT NULL,
  `CS_start` datetime DEFAULT NULL,
  `CS_end` datetime DEFAULT NULL,
  `US_start` datetime DEFAULT NULL,
  `US_end` datetime DEFAULT NULL,
  PRIMARY KEY (`Date`,`Time`,`SID`,`Project`,`Version`),
  KEY `index1` (`Data_status`) USING BTREE,
  KEY `index2` (`Data_status`,`Calc_status`) USING BTREE,
  KEY `index3` (`Depend`,`Data_status`,`Calc_status`) USING BTREE,
  KEY `index4` (`SID`) USING BTREE,
  KEY `idx_task_message_Version` (`Version`),
  KEY `index7` (`Date`,`Time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


## SQL commands
## create database and tables

#create database for air quality prediction
create database AQI_PREDICTION_DB;

#create table - air quality station info
CREATE TABLE air_station_info (
  SID varchar(20) NOT NULL COMMENT '监测点编码'
  ,SID_C text COMMENT '监测点名称'
  ,CITY text COMMENT '城市'
  ,LON double DEFAULT NULL COMMENT '经度'
  ,LAT double DEFAULT NULL COMMENT '纬度'
  ,REGION text DEFAULT NULL COMMENT '所属地区'
  ,PRIMARY KEY (SID)
) 
CHARSET=utf8
COMMENT = '空气质量站点信息'
;

#create table - prediction by date / station / variable
CREATE TABLE result_prediction (
  SID varchar(20) NOT NULL COMMENT '监测点编码'
  ,date_cur varchar(50) NOT NULL COMMENT '进行预测时所在的日期'
  ,date_pred varchar(50) NOT NULL COMMENT '要预测的日期'
  ,varname_pred varchar(50) NOT NULL COMMENT '要预测的要素名称'
  ,value_pred double COMMENT '预测出的数值'
  ,model_tag varchar(100) NOT NULL COMMENT '模型标签'
  ,UNIQUE KEY uniquekey (SID, date_cur, date_pred, varname_pred, model_tag)
) 
CHARSET=utf8
COMMENT = '结果表,空气质量预测,按站点/日期/要素'
;

#SQL command - create table
CREATE TABLE temp_yusimiao_airq_bydate (
  sid varchar(20) NOT NULL COMMENT ''
  ,date varchar(50) NOT NULL COMMENT ''
  ,aqi double COMMENT ''
  ,co double COMMENT ''
  ,no2 double COMMENT ''
  ,o3 double COMMENT ''
  ,pm10 double COMMENT ''
  ,pm25 double COMMENT ''
  ,so2 double COMMENT ''
  ,hour_hc double COMMENT ''
  ,aqi_hc double COMMENT ''
  ,co_hc double COMMENT ''
  ,no2_hc double COMMENT ''
  ,o3_hc double COMMENT ''
  ,pm10_hc double COMMENT ''
  ,pm25_hc double COMMENT ''
  ,so2_hc double COMMENT ''
  ,UNIQUE KEY uniquekey (sid, date)
) 
CHARSET=utf8
COMMENT = '临时表,空气质量按站点/天汇总'
;

第18课 使用视图

18.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';
# 该句sql检索订购了某种商品的顾客。任何需要这个数据的人必须理解这些表与表之间的关系。

# 假如将整个查询包装成一个名为ProductsCustomers的虚拟表。那么sql可以简化为:
SELECT cust_name, cust_contact 
FROM ProductCustomers 
WHERE prod_id = 'RGANO1';

总结:视图不包含任何的列或数据,包含的是一个查询。

18.1.1 为什么使用视图

视图的常见使用场景: 1. 重用SQL语句。 2. 简化复制的SQL操作。在编写查询后,可以方便的使用它,而不必知道具体的细节。 3. 使用表的一部分,而不是整个表。 4. 保护数据。可以授予用户访问表的特定部分,而不是表的全部权限。 5. 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

创建视图后,可以用表相同的方式去使用它们。可以对视图执行select操作,过滤和排序数据,将视图联结到其他视图或表等。

再次强调:视图只是包含了动态检索数据的查询。

18.1.2 视图的规则与限制

不同的DBMS中视图的限制和规则可能不同,具体请参考文档。

视图的创建和使用的一些最常见的规则和限制: 1. 与表一样,视图必须唯一命名,名字不能有冲突。 2. 对于可以创建的视图数目没有限制。 3. 创建视图,必须具有足够的访问权限。 4. 视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造视图。所允许的嵌套层数在不同的DBMS中有所不同(嵌套视图可能会严 重降低查询的性能,因此在产品环境中使用之前,应该对其进行全面测试)。 5. 许多DBMS禁止在视图查询中使用ORDER BY子句。 6. 有些DBMS要求对返回的所有列进行命名,如果列是计算字段,则需要使用别名。 7. 视图不能索引,也不能有关联的触发器或默认值。 8. 有些DBMS把视图作为只读的查询,这表示可以从视图检索数据,但不能将数据写回底层表。

18.2 创建视图

Create view语句来创建视图。

18.2.1 利用视图简化复杂的联结

视图最常见的应用:隐藏复杂的SQL

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;

# 然后调用sql查询:
select cust_name, cust_contact from ProductCustomers where prod_id = 'RGANO1';
18.2.2 用视图重新下格式化检索的数据

视图另一个常见的应用: 重新格式化检索出的数据

create view VendorLocations AS
select RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
AS vend_title from Vendors;

在单个组合计算列中返回供应商名和位置

18.2.3 用视图过滤不想要的数据

可以定义一个视图:过滤没有电子邮件地址的顾客

create view CustomerEMailList AS
select cust_id, cust_name, cust_email
from Customers where cust_email is not Null;
18.2.4 使用视图与计算字段

在简化计算字段的使用上,视图也特别有用。

create view OrderItemsExpanded AS
select order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price
from OrderItems

第19课 使用存储过程

19.1 存储过程
  1. 通俗的讲,存储过程就是类似于C的一个方法。
  2. 简单的说,存储过程就是为以后使用而保存一条或多条SQL语句。可以将其视为批文件,但它的作用不仅限于批处理。
19.2 为什么使用存储过程
  1. 存储过程有3个优点:简单,安全和高性能。
  2. 多数DBMS的存储过程可能不同,而且存储过程也比较复杂,难于编写。
19.3 执行存储过程

使用关键字execute来执行存储过程

EXECUTE AddNewProduct( 'JTS01', 'Stuffed Eiffel Tower', 6.49,'Plush stuffed toy with the text");

AddNewProduct是一个存储过程,将一个新的商品添加到Product表中。但我们发现,最重要的字段prod_id列没有,应为我们想统一化规格化生成对应的prod_id. 所以,该存储过程需要做以下3件事: 1. 验证传递的数据,保证所有4个参数都有值; 2. 生成用作主键的唯一ID; 3. 将新产品插入Products表,在合适的列中存储生成的主键和传递的数据。

19.4 创建存储过程

创建存储过程,每个DBMS差别很大,具体需要参考DBMS文档。

第20课 管理事务处理

在使用事务时,需要理解几个关键字: 1. 事务(transaction)指一组SQL语句. 2. 回退(rollback)指撤销指定SQL语句的过程。 3. 提交(commit)指将未存储的SQL语句结果写入数据库表。 4. 保留点(savepoint)指事务处理中设置的临时占位符(placeholder),可以对它发布回退。

哪些sql语句可以回退? 答:Insert, Delete和Update语句可以回退。Select, Drop和Create语句不能回退。

20.2 控制事务处理

管理事务的关键在于将SQL语句组合成逻辑块,并规定数据何时回退,何时不应该回退。

不同的DBMS实现事务处理的语法不同: 1. SQL Server

Begin Transaction
...
Commit Transaction

在这里Begin Transaction和Commit Transaction之间的SQL必须完全执行,或完全不执行。 2. Mysql

Start Transaction
...
Commit
  1. Oracle

    Set Transaction
    ...
    Commit
20.2.1 使用Rollback

SQL 使用Rollback来回退(撤销)SQL命令

命令:

delete from Orders; Rollback;

使用Delete语句,然后使用Rollback语句撤销。该句能充分说明:在事务处理块中,delete操作(insert操作和update操作)并不是最终的结果。

20.2.2 使用Commit

在事务处理块中,提交必须时显示提交。

删除订单12345,所以需要同时更新两个数据库表Orders表和OrderItems表。 1. Mysql中

Begin Transaction
delete OrderItems where order_num = 12345;
delete Orders where order_num = 12345;
Commit Transaction
  1. Oracle中

    Set Transaction
    delete OrderItems where order_num = 12345;
    delete Orders where order_num = 12345;
    Commit;

上面的事务处理块中同时更新两个表中的记录,事务保证了操作的一致性,不可能出现部分删除。

20.2.3 使用保留点

保留点(savepoint)作用是:支持回退部分事务。 例如添加一个订单,需要插入顾客信息,插入订单信息,订单详情信息,但当插入订单详情时发生错误,只需要回退到插入订单信息,不需要回退到插入顾客信息。这是就需要回退部分事务。 保留点使用:在编写事务过程中,需要在事务处理块中的合适位置放置占位符。用于后面的回退。

不同的DBMS的保留点设置不同 1. Mysql

Savepoint delete1;
  1. SQL Server

    Save Transaction delete1;

不同的DBMS的回退保留点的方式不同 1. Mysql

Rollback to delete1;
  1. SQL Server

    Rollback transaction delete1;
20.3 SQL Server事务举例

不同的DBMS可能不同,但是总体概念和流程都是相同的。

Begin Transaction
insert into Customers(cust_id, cust_name) values('1000000010', 'Toys Emporium');
save transaction StartOrder;
insert into Orders(order_num, order_date, cust_id) values(20100,'2001/12/1','1000000010');
IF @@ERROR <> 0 Rollback Transaction StartOrder;
insert into OrderItems(order_num, order_item, prod_id, quantity, item_price)
values(20100, 1, 'BR01', 100, 5.49);
IF @@ERROR <> 0 Rollback Transaction StartOrder;
insert into OrderItems(order_num, order_item, prod_id, quantity, item_price)
values(20100, 2, 'BR03', 100, 10.99);
IF @@ERROR <> 0 Rollback Transaction StartOrder;
Commit Transaction;

第21课 使用游标

21.1 游标

SQL检索结果是返回一组称为结果集的行。但是无法从结果集中得到第一行,下一行或前10行的数据。但是大多数Web应用开发人员不使用游标,而是根据自己的需要开发相应的功能。如:利用limit1, 10来实现分页查询, 或使用foreach来实现遍历。

游标的用途:

有时,需要在检索出来的行中前进或后退一行或多行,就需要使用游标。 游标(cursor)是一个存储在DBMS服务器上的数据库查询,它不是一条SELECT语句。在存储了游标之后,应用程序可以根据需要滚动或浏览数据集中的数据。

游标在不同的DBMS中的一些共性: 1. 能够标记游标为只读,使数据能读取,但不能更新和删除。 2. 能控制可以执行的定向操作(向前、向后、第一、最后、绝对位置、相对位置等)。 3. 能标记某些列为可编辑的,某些列为不可编辑的。 4. 规定访问范围,使游标对创建它的特定请求(如存储过程)或对所有请求可访问。 5. 指示DBMS对检索出的数据(而不是指出表中活动数据)进行复制,使数据在游标打开和访问期间不变化。

21.2 使用游标

使用游标的几个明确的步骤: 1. 在使用游标前,必须定义它。这个过程实际上没有检索数据,它只是定义要使用的SELECT语句和游标。 2. 一旦定义,就必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。 3. 对于填有数据的游标,根据需要取检索各行。 4. 在结束游标使用时,必须关闭游标,可能的话,释放游标(有赖于具体的DBMS)。

21.2.1 创建游标

使用Declare语句创建游标。

创建一个游标来检索没有电子邮件地址的所有顾客. 1. Mysql + SQL Server

Declare CustCursor Cursor for
Select * from Customers where cust_email is null;
  1. Oracle

    Declare Cursor CustCursor is
    Select * From Customers where cust_email is null;
21.2.2 使用游标

游标的使用场景并不多,该节笔记比较粗糙。

使用OPEN CURSOR语句打开游标

Open Cursor CustCursor;

使用Fetch语句访问游标数据。 Fetch需要指出检索哪些行,从何处检索他们以及将他们存放于何处。

Declare Type CustCursor IS ref Cursor
Return Customers%ROWTYPE;
Declare CustRecord Customers%ROWTYPE
Begin
	Open CustCursor;
	Fetch CustCursor INTO CustRecord;
	Close CustCursor;
End;	
21.2.3 关闭游标
  1. Oracle

    Close CustCursor;
  2. SQL Server

    Close CustCursor
    Deallocate Cursor CustCursor;

第22课 高级SQL特性

22.1 约束
  1. 约束: 管理如何插入或处理数据库数据的规则。
  2. 引用完整性(referential integrity): 利用键来建立从一个表到另一个表的引用

关系型数据库,需要保证插入数据库的数据和合法性。 例如:如果Orders表存储订单信息,OrderItems表存储订单详细内容,应该保证OrderItems中引用的任何订单ID都存在于Orders中。类似地,在Orders表中引用的任意顾客必须存在于Customers表中。DBMS通过在数据库表上施加约束来实施引用完整性。

22.1.1 主键

主键是一种特殊的约束,用来某一行的数据是唯一的,而且主键永不改动。

表中的列只要满足以下条件,可用于主键: 1. 任意两行的主键值都不相同。 2. 每行都具有一个主键值(即列中不允许NULL值)。 3. 包含主键值的列从不修改或更新。 4. 主键值不能重用。如果从表中删除某一行,其主键值不分配给新行。

主键的创建: 1. Create Table

Create Table Vendors
(
vend_id		CHAR(10) NOT NULL Primary Key,
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声明表的主键列。

  1. Alter Table

    Alter Table Vendors 
    Add Constraint Primary Key(vend_id);
22.1.2 外键

外键是表中的一列,其值必须在另一表的主键列中。

外键是关系数据库描述表和表之间依赖关系 1. Create Table

Create Table Orders
(
	order_num		Integer	not null primary key,
	order_date	DateTime	not null,
	cust_id		char(10)	not null references Customers(cust_id)
);
  1. Alter Table

    Alter Table Orders
    Add Constraint Foreign key (cust_id) references Customers(cust_id);

外键的作用: 1. 能够帮组保证引用完整性。 2. 防止意外删除记录 由于顾客表Customers表中主见: cust_id,作为订单表Orders中的外键。所以如果想直接删除顾客表中的记录,必须保证其值已经不被订单表中依赖。 3. 级联删除 Mysql支持级联删除特性。解释:如果从顾客表Customers表中删除某个顾客,那么由于顾客表Customers表中的主见cust_id作为了Orders表中的外键依赖,所以订单表Orders中的该顾客的订单也一并删除。

22.1.3 唯一约束

唯一约束用来保证某一列中的数据是唯一的。它和主键有以下区别: 1. 表可包含多个唯一约束,但每个表只允许一个主键。 2. 唯一约束列可包含NULL值。 3. 唯一约束列可修改或更新。 4. 唯一约束列的值可重复使用。 5. 与主键不一样,唯一约束不能用来定义外键。

22.1.4 检查约束

检查约束用来保证一列中的数据满足一组指定的条件。检查约束的常见用途: 1. 检查最小或最大值。例如,防止0个物品的订单(即使0是合法的数)。 2. 指定范围。例如,保证发货日期大于等于今天的日期,但不超过今天起一年后的日期。 3. 只允许特定的值。例如,在性别字段中只允许M或F。

对OrderItems表施加了检查约束,保证所有物品的数量大于0:

CREATE TABLE OrderItems
(
	order_num		INTEGER	NOT NULL,
	order_item	INTEGER	not null, 
	prod_id		CHAR(10)	not null,
	quantity		INTEGER	not null check (quantity > 0),
	item_price	MONEY		not null
);

检查名为gender的列只包含M或F

Alter Table OrderItems
Add Constraint Check (gender LIKE '[MF]')
22.2 索引

索引是利用B+等索引机制,加快查询和排序的速度。

使用索引需要记住以下内容: 1. 索引提高检索操作的性能,但降低了数据插入、修改和删除的性能。因为数据变更需要更新索引。 2. 索引数据可能要占用大量的存储空间。 3. 并非所有数据都适合做索引。查询量非常大,列的数据非常多,使用使用索引。 4. 索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据,则该数据可能适合做索引。 5. 可以在索引中定义多个列(例如,州加上城市)。这样的索引仅在以州加城市的顺序排序时有用。

所以必须唯一命名:

Create Index prod_name_index On Products(prod_name);
22.2.1 索引建立的几大原则

组合索引的解释: 如果创建了(state, city, zip)列作为组合索引,索引中的数据行按照state/city/zip次序排列, 这意味着,这个索引可以被用于搜索如下所示的数据列组合: state, city, zip state, city state

但是MySQL不能利用这个索引来搜索没有包含在最左前缀的内容。例如,如果你按照city或zip来搜索,就不会使用到这个索引。如果你按照state,和zip来搜索,该索引也是不能用于这种组合值的,但是可以利用索引来查找匹配的state从而缩小搜索的范围。

  1. 选择唯一性索引。唯一性索引的值是唯一的,能快速的通过该索引来确定某条记录。
  2. 为经常需要排序、分组和联合操作的字段建立索引经常需要Order ByGroup ByDistinctUnion等操作的字段,排序操作会浪费很多时间。
  3. 为常作为查询条件的字段建立索引某个字段经常用来做查询条件,该字段的查询速度会影响整个表的查询速度。使用索引,可以极大加快查询速度。
  4. 最左前缀匹配原则,非常重要的原则。mysql会一直向右匹配直到遇到范围查询(><betweenlike)就停止匹配,比如state='yes' and city='wuwei' and price > 3 and name = 'ppp', 如果建立(state, city, price, name)顺序的索引,name的查询是用不到索引的,只能使用部分索引,然后在结果集上面进行排序。如果建立(state, city , ppp, price)的索引则都可以用到,state, city, name的顺序可以任意调整。
  5. =in可以乱序。比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式。但是如果建立索引(a,b,c),结果where子句是:b =2 and c=3,则索引不起作用。
  6. 尽量使用数据量少的索引。如果索引的值很长,那么查询的速度会受到影响。例如,对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。
  7. 尽量使用前缀来索引。索引字段的值很长,应该采用前缀来索引。
  8. 尽量选择区分度高的列作为索引。数据量区分度越高,索引的比较成本会小很多。
  9. 删除不再使用或者很少使用的索引。请定期删除不在使用的索引。
  10. 限制索引的数目。使用索引需要付出代价,索引会消耗磁盘空间,对数据库记录的更新和删除产生影响。
  11. 索引列不能参与计算。坚决不能将某个计算函数作为缩影。如:from_unixtime(create_time) = ’2014-05-29’
  12. 尽量的扩展索引,不要新建索引。 比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可
22.3 触发器

触发器是特殊的存储过程,可以在特定的数据库活动发生时自动执行。 触发器可以与特定表上的Insert, Update或Delete操作(或组合)想关联。 如:与Orders表上的Insert操作相关联的触发器只在Orders表中插入行时才会执行。

触发器的常见用途: 1. 保证数据一致。例如,在Insert或Update操作中将所有州名转换为大写。 2. 基于某个表的变动在其他表上执行活动。例如,每当更新或删除一行时将审计跟踪记录写入某个日志表。 3. 进行额外的验证并根据需要回退数据。例如,保证某个顾客的可用资金不超限定,如果已经超出,则阻塞插入。 4. 计算计算列的值或更新时间戳。

不同的DBMS的触发器差距很大,请参考具体文档. 如:创建一个触发器,对所有Insert和Update操作,将Customers表中的cust_state列转换为大写。 1. SQL Server

Create Trigger customer_state On Customers
For Insert, Update As
Update Customers Set cust_state = Upper(cust_state)
  1. Oracle

    Create Trigger customer_state
    After Insert Or Uupdate
    For Each Row
    Begin
    Update Customers
    Set cust_state = Upper(cust_state)
    Where Customers.cust_id = :OLD.cust_id
    end;
22.4 数据库安全

一般对数据保护操作有: 1. 对数据库管理功能(创建表、更改或删除已存在的表等)的访问; 2. 对特定数据库或表的访问; 3. 访问的类型(只读、对特定列的访问等); 4. 仅通过视图或存储过程对表进行访问; 5. 创建多层次的安全措施,从而允许多种基于登录的访问和控制; 6. 限制管理用户账号的能力。

第23课 SQL中的各种KEY

PRIMARY KEY, UNIQUE KEY, FOREIGN KEY。

23.1 PRIMARY KEY与UNIQUE KEY的作用

共同作用是为了约束字段/建立索引/提高查询效率

PRIMARY KEY的属性:

  1. 主键具有唯一性:是指一张表里只能有一个主键;
  2. 主键作用:主键PRIMARY KEY是为了唯一标识一个字段,使其唯一且不能为NULL,自动生成索引;
  3. 隐含定义:如果这些列没有被明确地定义为NOT NULL,MySQL会隐含地定义这些列。
  4. 主键其实也是索引,甚至在MySQL的术语里面“键”就等于“索引”,

UNIQUE KEY的属性:

  1. 唯一性:定义了UNIQUE约束的字段中不能包含重复值,
  2. 可为空:在UNIQUE约束的字段上可以包含空值.
  3. 扩展:UNIQUE就是唯一,当你需要限定你的某个表字段每个值都唯一,没有重复值时使用. 比如说,如果你有一个person_Info表,并且表中有个身份证的column,那么你就可以指定该字段unique.
23.2 关系

主键 = NOT NULL +UNIQUE 键的结合;

NOT NULL和UNIQUE约束最好的结合。如果这些列没有被明确地定义为NOT NULL,MySQL会隐含地定义这些列。

23.3 区别

第24课 SQL 约束(Constraints)

SQL 约束用于规定表中的数据规则。

约束可以在创建表时规定(通过 CREATE TABLE 语句),或者在表创建之后规定(通过 ALTER TABLE 语句)。

# SQL CREATE TABLE + CONSTRAINT 语法
CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

在 SQL 中,我们有如下约束:

SQL NOT NULL 约束

在默认的情况下,表的列接受 NULL 值。

NOT NULL 约束强制字段始终包含值。这意味着,如果不向字段添加值,就无法插入新记录或者更新记录。

# 下面的 SQL 强制 "P_Id" 列和 "LastName" 列不接受 NULL 值:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
SQL UNIQUE 约束

UNIQUE 约束唯一标识数据库表中的每条记录。

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束。

请注意,每个表可以有多个 UNIQUE 约束,但是每个表只能有一个 PRIMARY KEY 约束。

下面的 SQL 在 “Persons” 表创建时在 “P_Id” 列上创建 UNIQUE 约束:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)
SQL PRIMARY KEY 约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

主键必须包含唯一的值。

主键列不能包含 NULL 值。

每个表都应该有一个主键,并且每个表只能有一个主键。

下面的 SQL 在 “Persons” 表创建时在 “P_Id” 列上创建 PRIMARY KEY 约束:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)
SQL FOREIGN KEY 约束

一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)。

让我们通过一个实例来解释外键。请看下面两个表:

image-20190823181204590

请注意,”Orders” 表中的 “P_Id” 列指向 “Persons” 表中的 “P_Id” 列。

“Persons” 表中的 “P_Id” 列是 “Persons” 表中的 PRIMARY KEY。

“Orders” 表中的 “P_Id” 列是 “Orders” 表中的 FOREIGN KEY。

FOREIGN KEY 约束用于预防破坏表之间连接的行为。

FOREIGN KEY 约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。

下面的 SQL 在 “Orders” 表创建时在 “P_Id” 列上创建 FOREIGN KEY 约束:

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

REFERENCE

PDF与源码

W3School-SQL 教程