数据库基础总结

把之前学过的SQL的知识复习一遍，记一下难点。

数据类型

数值类型 INT、FLOAT
字符串类型 CHAR、TEXT
日期和时间类型 DATE、TIMESTAMP
复合类型 ENUM、SET

其中一个ENUM类型只允许从一个集合中取得一个值；而SET类型允许从一个集合（元素不能重复的）中取得任意多个值

主键

任意两行都不具有相同的主键值
每一行都必须具有一个主键值（主键列不允许NULL值），虽然不一定需要
主键列中的值不允许修改或更新
主键值不能重用(如果某行从表中删除，它的主键不能赋给以后的新行)。

Select

命令排序

SELECT 要返回的列或表达式
FROM 从中检索数据的表
WHERE 行级过滤
GROUP BY 分组说明
HAVING 组级过滤
ORDER BY 输出排序顺序

不同的值

DISTINCT

返回所有学生的年龄

1 2	SELECT DISTINCT age FROM student

LIMIT

检索记录行 6、7、8

1
2
3

SELECT DISTINCT age
FROM student
LIMIT 5 offset 3

检索前 5 个记录行

1
2
3

SELECT DISTINCT age
FROM student
LIMIT 5

顺序

ORDER BY

按照排列对象二进制的编码从小到大排序

Comparing the ASCII one by one until difference found, or, one reach the end of the string, it’s the same logic in other program languages.

DESC倒序

从贵到便宜排列

1
2
3

SELECT DISTINCT age
FROM student
ORDER BY price DESC

与或

SQL先处理AND在处理OR

HAVING 和 WHERE

WHERE过滤行、HAVING过滤分组
WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤
WHERE排除的行不包括在分组中，这可能会改变HAVING子句中基于这些值过滤掉的分组

子查询

可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
                    FROM OrderItems
                    WHERE prod_id = 'RGAN01')

在SELECT语句中，子查询总是从内向外处理

作为子查询的SELECT语句只能查询单个列，企图检索多个列将返回错误

作为计算字段使用子查询

查询一个顾客的订单数量

1
2
3

SELECT COUNT(*) AS orders
FROM Orders
WHERE cust_id = '1000000001'

查询所有顾客的订单数量

SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;

联结

如果数据存储在多个表中，怎样用一条SELECT语句就检索出数据呢？答案是使用联结：

内联结
外联结
交叉联结

假设有以下两个表，其中a.id同b.parent_id存在关系：

A表

id	name
1	张三
2	李四
3	王五

B表

id	job	paremt
1	23	1
2	34	2
3	34	4

内联结根据每个表共有的列的值匹配两个表中的行

1	SELECT a.,b. FROM a INNER JOIN b on a.id=b.parent_id

返回结果：

1 张三 1 23 1
2 李四 2 34 2

左向外连接的结果集包括指定的左表的所有行，如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空

1	SELECT a.,b. FROM a LEFT JOIN b on a.id=b.parent_id

返回结果：

1 张三 1 23 1
2 李四 2 34 2
3 王五 null

右向外连接的结果集包括指定的右表的所有行，如果左表的某行在右表中没有匹配行，则将为左表返回空值

1	SELECT a.,b. FROM a RIGHT JOIN b on a.id=b.parent_id

返回结果：

1 张三 1 23 1
2 李四 2 34 2
null 3 34 4

完整外部连接返回左表和右表中的所有行

1	SELECT a.,b. FROM a FULL JOIN b on a.id=b.parent_id

返回结果：

1 张三 1 23 1
2 李四 2 34 2
null 　　3 34 4
3 王五 null

交叉联结，两个表的数据进行组合

1	SELECT a.,b. FROM a CROSS JOIN b

返回3*3=9条数据

自联结，一个表出现多次

UNION

可用UNION操作符来组合数条SQL查询。利用UNION，可给出多条SELECT语句，将它们的结果组合成一个结果集（不能重复）

如果需要重复，使用UNION ALL

INSERT

正常添加行

1 2	INSERT INTO Customers VALUES('10001', 'TONY')

移植多行

INSERT INTO Customers(cust_id,
                      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
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
FROM CustNew;

复制表

1
2
3

SELECT *
INTO CustCopy
FROM Customers;

UPDATE & DELETE

正常更新行

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

正常删除表

1 2	DELETE FROM Customers WHERE cust_id = '1000000006'

使用外键的好处是防止删除某个关系需要用到的行：
例如，要从Products表中删除一个产品，而这个产品用在OrderItems的已有订单中，那么DELETE语句将抛出错误并中止

存储过程

管理事务

检查数据库中是否存在相应的顾客，如果不存在，添加他;
提交顾客信息;
检索顾客的ID;
在Orders表中添加一行;
如果向Orders表添加行时出现故障，回退;
检索Orders表中赋予的新订单ID;
对于订购的每项物品，添加新行到OrderItems表;
如果向OrderItems添加行时出现故障，回退所有添加的OrderItems行和Orders行

约束

主键PRIMARY KEY
外键REFERENCES XX(XX)
唯一约束UNIQUE
检查约束CHECK(xx > xx)

唯一约束和主键的区别

表可包含多个唯一约束，但每个表只允许一个主键。
唯一约束列可包含NULL值。
唯一约束列可修改或更新。
唯一约束列的值可重复使用。
与主键不一样，唯一约束不能用来定义外键。

索引

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

安全

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