阅读《SQL必知必会》(第五版) 总结及笔记

事了拂喵去Arman

于 2024-10-07 14:08:34 发布

阅读量1k

点赞数 9

文章标签： sql 笔记数据库

本文链接：https://blog.csdn.net/qq_17338093/article/details/142739895

版权

书籍推荐：https://weread.qq.com/book-detail?type=1&senderVid=73931383&v=f7632a30720befadf7636bb&wtheme=white&wfrom=app&wvid=73931383&scene=bottomSheetShare

数据定义语言（DDL）

创建数据库
- CREATE DATABASE example_db;
删除数据库
- DROP DATABASE example_db;
创建表
- CREATE TABLE employees(user_id VARCHAR (64) NOT, user_name VARCHAR (64) PRIMARY KEY(target_id, category_id))
删除表
- DROP TABLE IF EXISTS employees
添加列
- ALTER TABLE MESSAGE ADD COLUMN msg_cuid INTEGER DEFAULT 0
删除列
- ALTER TABLE employees DROP COLUMN email
修改列
- ALTER TABLE employees MODIFY COLUMN name VARCHAR(255)
重命名列
- ALTER TABLE employees RENAME COLUMN name TO fullname;
创建索引
- CREATE INDEX IF NOT EXISTS i_con ON T_CONVERSATION_TAG (target_id, category_id, channel_id)
- CREATE INDEX IF NOT EXISTS i_uid ON T_MESSAGE (extra_column5)
验证添加的索引是否有作用
- EXPLAIN SELECT * FROM your_table WHERE indexed_column = ‘some_value’;
  - 输出中如果出现 Using index 或者类似的描述，说明索引正在被使用
删除索引
- DROP INDEX idx_employee_name ON employees
创建视图
删除视图
- DROP VIEW department_summary
创建主键
- ALTER TABLE employees ADD PRIMARY KEY(employee_id)
删除主键
- ALTER TABLE employees DROP PRIMARY KEY

数据操作语言（DML）

插入数据
- INSERT INTO CONVERSATION(target_id) VALUES(“a”)
更新数据
- UPDATE CONVERSATION SET last_time=? WHERE target_id=? AND category_id=?
删除数据
- DELETE FROM employees WHERE employee_id=1
查询所有数据
- SELECT * FROM employees
查询特定列
- SELECT name,age FROM employees
条件查询
- SELECT name FROM employees WHERE age>30
限制查询结果数量
- SELECT * FROM employees LIMIT 10
查询排序
- SELECT * FROM employees ORDER BY age DESC
  - SELECT send_time FROM MESSAGE WHERE target_id=? AND category_id=? ORDER BY send_time DESC LIMIT 1
分组统计
- SELECT department_id,COUNT(*) AS num_employees FROM employees GROUP BY department_id;
连接查询
- SELECT employees.name,department.name FROM employees JOIN departments ON employees.department_id=departments.department_id;
左连接查询
- SELECT employees.name,departments.name FROM employees LEFT JOIN departments ON employees.department_id= departments.department_id;
子查询
- SELECT name FROM employees WHERE department_id IN(SELECT department_id FROM departments WHERE name=‘IT’);
查询是否存在
- SELECT 1 FROM GROUP WHERE group_id=? LIMIT 1
计算总和
- SELECT SUM(unread_count) FROM CONVERSATION WHERE category_id=10
计算平均值
- SELECT AVG(salary) FROM employees
计算最大值
- SELECT MAX(salary) FROM employees
计算最小值
- SELECT MIN(salary) FROM employees
计数
- SELECT COUNT(*), page_offset FROM FRIEND_REQUEST WHERE user_id=?
  - 查询出满足条件的条数和字段
使用别名
- SELECT COUNT(*) AS total_employees FROM employees;
去重查询
- SELECT DISTINCT user_id, name FROM USER WHERE category_id IN(7,8)
  - 查询所有 category_id 为 7 或 8 的用户，并返回这些用户的 user_id 和 name，且确保每个用户只返回一次
  - 用户的唯一标识符 user_id 和用户名 name
使用条件函数
- SELECT name,CASE WHEN age>=18 THEN ‘Adult’ ELSE ‘Minor’ END AS status FROM employees;
使用 LIKE 进行模糊查询
- SELECT * FROM employees WHERE name LIKE ‘j%’
- SELECT user_id,remark,friend_type,add_time FROM FRIEND_PROFILE WHERE remark LIKE ‘%remark%’ ORDER BY add_time ASC"
  - LIKE 后面不需要添加括号

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

表（table）某种特定类型数据的结构化清单
行（row）表中的一个记录。表中的数据是按行存储的，所保存的每个记录存储在自己的行内。如果将表想象为网格，网格中垂直的列为表列，水平行为表行。
主键（primary key）一列（或几列），其值能够唯一标识表中每一行。表中每一行都应该有一列（或几列）可以唯一标识自己
- 表中的任何列都可以作为主键，只要它满足以下条件：
  - 任意两行都不具有相同的主键值；
  - 每一行都必须具有一个主键值（主键列不允许空值NULL）；
  - 主键列中的值不允许修改或更新；
  - 主键值不能重用（如果某行从表中删除，它的主键不能赋给以后的新行）。
SQLite不允许使用ALTER TABLE定义键，要求在初始的CREATE TABLE语句中定义它们。
查询
- SELECT语句还可以检索所有的列而不必逐个列出它们。在实际列名的位置使用星号（＊）通配符可以做到这点。SELECT ＊ FROM Products;
  - 会返回表中所有列。列的顺序一般是表中出现的物理顺序，但并不总是如此。
- DISTINCT：只返回不同（具有唯一性）的vend_id行
  - SELECT DISTINCT vend_id FROM Products;
  - DISTINCT关键字作用于所有的列，不仅仅是跟在其后的那一列
- TOP
  - SELECT TOP 5 prod_name FROM Products; //只检索前5行数据
  - 用TOP关键字来限制最多返回多少行
  - SELECT prod_name FROM Products FETCH FIRST 5 ROWS ONLY;//FETCH FIRST 5 ROWS ONLY就会按字面的意思去做的（只取前5行）
  - SELECT prod_name FROM Products LIMIT 5;//LIMIT 5指示MySQL等DBMS返回不超过5行的数据
排序检索数据
- 检索出的数据并不是随机显示的。如果不排序，数据一般将以它在表中出现的顺序显示，这有可能是数据最初添加到表中的顺序。但是，如果数据随后进行过更新或删除，那么这个顺序将会受到DBMS重用回收存储空间的方式的影响
- ORDER BY
  - 取一个或多个列的名字，据此对输出进行排序
  - 在指定一条ORDER BY子句时，应该保证它是SELECT语句中最后一条子句。如果它不是最后的子句，将会出错。
按多个列排序
- SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price, prod_name;
- 仅在多个行具有相同的prod_price值时才对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的，则不会按prod_name排序。
指定排序方向
- SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC;
- DESC(DESCENDING)关键字只应用到直接位于其前面的列名。如果想在多个列上进行降序排序，必须对每一列指定DESC关键字
过滤数据
- SELECT prod_name, prod_price FROM Products WHERE prod_price = 3.49;
  - IS NULL;为NULL的值。BETWEEN 在指定的两个值之间。
    - NULL：无值（no value），它与字段包含0、空字符串或仅仅包含空格不同。
- 数据根据WHERE子句中指定的搜索条件进行过滤。WHERE子句在表名（FROM子句）之后给出
- 在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误
高级数据过滤
- 组合WHERE子句：AND或OR
- 可以使用圆括号对操作符进行明确分组
N操作符
- 用来指定条件范围，范围中的每个条件都可以进行匹配。IN取一组由逗号分隔、括在圆括号中的合法值
- IN操作符一般比一组OR操作符执行得更快
- IN的最大优点是可以包含其他SELECT语句，能够更动态地建立WHERE子句
NOT操作符
- 用来否定其后条件的关键字
- SELECT prod_name FROM Products WHERE NOT vend_id = ‘DLL01’ ORDER BY prod_name;//匹配vend_id不为DLL01的项
用通配符进行过滤
- 通配符搜索只能用于文本字段（字符串），非文本数据类型字段不能使用通配符搜索
LIKE
%：可以匹配零个或多个字符
- SELECT prod_id,prod_name FROM Products WHERE prod_name LIKE ‘%bean bag%’;
  - 在 MySQL 中，LIKE 默认是不区分大小写的，因此 ‘%bean bag%’ 既可以匹配 “Bean Bag” 也可以匹配 “bean bag”。
  - 在 PostgreSQL 中，LIKE 是区分大小写的，如果你需要不区分大小写匹配，你可以使用 ILIKE，例如：ILIKE ‘%bean bag%’。
  - 在 SQL Server 中，大小写匹配取决于列的排序规则（Collation）。默认情况下，SQL Server 的排序规则是不区分大小写的，但也可以通过自定义排序规则来区分大小写。
_ :总是刚好匹配一个字符，不能多也不能少
*
[]:用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。
- SELECT cust_contact FROM Customers WHERE cust_contact LIKE ‘[JM]%’;//找出所有名字以J或M起头的联系人
使用通配符时要记住的技巧:
- 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符
- 在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的
- 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。
计算字段:需要直接从数据库中检索出转换、计算或格式化过的数据，而不是检索出数据，然后再在客户端应用程序中重新格式化。
- 拼接（concatenate）将值联结到一起（将一个值附加到另一个值）构成单个值。使用 + 或者 ||
  - SELECT vend_name || ‘(’ || vend_country || ‘)’ FROM Vendors ORDER BY vend_name;//查询输出：vend_name(vend_country)
- 别名（alias）是一个字段或值的替换名。别名用AS关键字赋予
算术操作符：在语句中执行加减乘除
- SELECT prod_id,quantity＊item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;
大多数SQL实现支持以下类型的数据处理函数：
- 用于处理文本字符串（如删除或填充值，转换值为大写或小写）的文本函数。
  - UPPER(X): 将字符串 X 转换为大写字母。
  - LOWER(X): 将字符串 X 转换为小写字母。
  - TRIM(X): 移除字符串 X 两端的空白字符。
  - LTRIM(X): 移除字符串 X 左侧的空白字符。
  - RTRIM(X): 移除字符串 X 右侧的空白字符。
  - REPLACE(X, Y, Z): 将字符串 X 中的子串 Y 替换为 Z。
  - SUBSTR(X, Y, Z): 从字符串 X 的第 Y 个字符开始，提取长度为 Z 的子串。
  - LENGTH(X): 返回字符串 X 的长度。
  - INSTR(X, Y): 返回子串 Y 在字符串 X 中首次出现的位置。
  - LIKE: 用于模式匹配（通配符 _ 表示单个字符， % 表示任意多个字符）。
- 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。
- 用于处理日期和时间值并从这些值中提取特定成分（如返回两个日期之差，检查日期有效性）的日期和时间函数。
  - DATE(‘now’, …): 返回当前日期或对日期进行操作后的结果，格式为 YYYY-MM-DD。
  - TIME(‘now’, …): 返回当前时间或对时间进行操作后的结果，格式为 HH:MM:SS。
  - DATETIME(‘now’, …): 返回当前日期和时间，格式为 YYYY-MM-DD HH:MM:SS。
  - JULIANDAY(‘now’): 返回当前日期的儒略日数。
  - STRFTIME(‘%format’, ‘now’): 返回格式化后的日期或时间值（类似于 strftime 的格式化功能）。
  - strftime(‘%Y’, ‘now’): 提取特定部分，比如年份、月份等。
- 用于生成美观好懂的输出内容的格式化函数（如用语言形式表达出日期，用货币符号和千分位表示金额）。
- 返回DBMS正使用的特殊信息（如返回用户登录信息）的系统函数。
聚集函数（aggregate function）对某些行运行的函数，计算并返回一个值。需要汇总数据而不用把它们实际检索出来，为此SQL提供了专门的函数
- AVG()通过对表中行数计数并计算其列值之和，求得该列的平均值
  - SELECT AVG(prod_price) AS avg_price FROM Products;//返回值avg_price，它包含Products表中所有产品的平均价格
- COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目。
  - COUNT()函数有两种使用方式：
    - 使用COUNT(＊)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
    - 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
- SELECT COUNT(cust_email) AS num_cust FROM Customers;
  - 如果指定列名，则COUNT()函数会忽略指定列的值为NULL的行，但如果COUNT()函数中用的是星号（＊），则不忽略。
- MAX()返回指定列中的最大值
  - SELECT MAX(prod_price) AS max_price FROM Products;
- MIN()的功能正好与MAX()功能相反，它返回指定列的最小值
  *
- SUM()用来返回指定列值的和
  - SELECT SUM(quantity) AS items_ordered FROM OrderItems
  - 也可以用来合计计算值：SELECT SUM(item_price＊quantity) AS total_price FROM OrderItems
聚集不同值
- ALL参数不需要指定，因为它是默认行为。如果不指定DISTINCT，则假定为ALL。
- SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products WHERE vend_id = ‘DLL01’;//返回特定供应商提供的产品的平均价格。使用了DISTINCT参数，因此平均值只考虑各个不同的价格
GROUP BY分组:可以将数据分为多个逻辑组，对每个组进行聚集计算。
- 在使用GROUP BY子句前，需要知道一些重要的规定：
  - GROUP BY子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。
  - 如果在GROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
  - GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
  - 大多数SQL实现不允许GROUP BY列带有长度可变的数据类型（如文本或备注型字段）。
  - 除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出。
  - 如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
  - GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
HAVING：过滤分组
- 所有类型的WHERE子句都可以用HAVING来替代(支持所有WHERE操作符)。唯一的差别是，WHERE过滤行，而HAVING过滤分组。
- SELECT vend_id, COUNT(＊) AS num_prods FROM Products WHERE prod_price>=4 GROUP BY vend_id HAVING COUNT(＊)>=2;
子查询：SQL还允许创建子查询（subquery），即嵌套在其他查询中的查询。
- 在SELECT语句中，子查询总是从内向外处理
- 作为子查询的SELECT语句只能查询单个列。企图检索多个列将返回错误。
- SELECT cust_name, cust_state, (SELECT COUNT(＊) FROM Orders WHERE Orders.cust_id = Customers.cust_id) AS orders FROM Customers ORDER BY cust_name;
  - 先根据用户id查询订单表这个用户的数量，并输出别名为orders。再查询Customers表中用户信息，排序并输出。两个表的cust_id需要区分并且一致
联结表
- 相同的数据出现多次决不是一件好事，这是关系数据库设计的基础。关系表的设计就是要把信息分解成多个表，一类数据一个表。各表通过某些共同的值互相关联（所以才叫关系数据库）。
- 联结是一种机制，用来在一条SELECT语句中关联表，因此称为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。
- 语法(内联)：SELECT vend_name, prod_name, prod_price FROM Vendors, Products WHERE Vendors.vend_id = Products.vend_id;
  - 查询的字段分别存在两个表中，FROM后面使用这两个表，通过WHERE语句进行联结
- 笛卡儿积（cartesian product）由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
  - 要保证所有联结都有WHERE子句，否则DBMS将返回比想要的数据多得多的数据。同理，要保证WHERE子句的正确性。不正确的过滤条件会导致DBMS返回不正确的数据。
内联结 INNER JOIN
- SELECT vend_name, prod_name, prod_price FROM Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id;
  - 和语法(内联)效果一样。但FROM子句不同。这里，两个表之间的关系是以INNER JOIN指定的部分FROM子句。在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的相同。
- SQL规范首选INNER JOIN语法
联结多个表
- 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;
高级联结
- 表别名
  - 允许给表名起别名。这样做有两个主要理由：
    - 缩短SQL语句；
    - 允许在一条SELECT语句中多次使用相同的表
  - 使用 AS 给表起别名
    - SELECT cust_name FROM Customers AS C, Orders AS O WHERE C.cust_id = O.cust_id
自联结（self-join）
- 自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多DBMS处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。
- SELECT c1.cust_id, c1.cust_name, c1.cust_contact FROM Customers AS c1, Customers AS c2 WHERE c1.cust_name = c2.cust_name AND c2.cust_contact = ‘Jim Jones’;
  - 与Jim Jones同一公司的所有顾客。子查询相对耗时，Customers第一次出现用了别名c1，第二次出现用了别名c2。现在可以将这些别名用作表名。例如，SELECT语句使用c1前缀明确给出所需列的全名。如果不这样，DBMS将返回错误，因为名为cust_id、cust_name、cust_contact的列各有两个。DBMS不知道想要的是哪一列（即使它们其实是同一列）。WHERE首先联结两个表，然后按第二个表中的cust_contact过滤数据，返回所需的数据。
自然联结（natural join）
- 无论何时对表进行联结，应该至少有一列不止出现在一个表中（被联结的列）。标准的联结（前一课中介绍的内联结）返回所有数据，相同的列甚至多次出现。自然联结排除多次出现，使每一列只返回一次
外联结（outer join）
- 语法(内联) 比直接在FROM后面加表效率更高
- LEFT OUTER JOIN
  - select * from T1 left outer join T2 on T1.userid=T2.userid
  - 显示左表T1中的所有行，并把右表T2中符合条件加到左表T1中;右表T2中不符合条件，就不用加入结果表中，并且NULL表示。
- RIGHT OUTER JOIN
  - select * from T1 right outer join T2 on T1.userid=T2.userid
  - 显示右表T2中的所有行，并把左表T1中符合条件加到右表T2中;左表T1中不符合条件，就不用加入结果表中，并且NULL表示。
- 全联(full outer join)
复合查询 UNION ：用UNION操作符来组合数条SQL查询。利用UNION，可给出多条SELECT语句，将它们的结果组合成一个结果集。
- 多数SQL查询只包含从一个或多个表中返回数据的单条SELECT语句。但是，SQL也允许执行多个查询（多条SELECT语句），并将结果作为一个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。
- 主要有两种情况需要使用组合查询：
  - 在一个查询中从不同的表返回结构数据；
  - 对一个表执行多个查询，按一个查询返回数据
- UNION的限制：
  - 使用UNION组合SELECT语句的数目，SQL没有标准限制。但是，最好是参考一下具体的DBMS文档，了解它是否对UNION能组合的最大语句数目有限制。
- 性能问题：
  - 理论上讲，这意味着从性能上看使用多条WHERE子句条件还是UNION应该没有实际的差别。不过我说的是理论上，实践中多数查询优化程序并不能达到理想状态，所以最好测试一下这两种方法，看哪种工作得更好。
- UNION规则:
  - UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合四条SELECT语句，将要使用三个UNION关键字）。
  - UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的次序列出）。
  - 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含转换的类型（例如，不同的数值类型或不同的日期类型）。
  - UNION从查询结果集中自动去除了重复的行；换句话说，它的行为与一条SELECT语句中使用多个WHERE子句条件一样。因为Indiana州有一个Fun4All单位，所以两条SELECT语句都返回该行。使用UNION时，重复的行会被自动取消。这是UNION的默认行为，如果愿意也可以改变它。事实上，如果想返回所有的匹配行，可使用UNION ALL而不是UNION。
    - 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’;
数据插入
- INSERT INTO MESSAGE_DIRECTED_USERS(msg_id, user_id) VALUES(?,?)
- 插入检索出的数据
  - INSERT INTO Customers(cust_id, cust_contact) SELECT cust_id, cust_contact FROM CustNew;
    - 为简单起见，这个例子在INSERT和SELECT语句中使用了相同的列名。但是，不一定要求列名匹配。事实上，DBMS一点儿也不关心SELECT返回的列名。它使用的是列的位置，因此SELECT中的第一列（不管其列名）将用来填充表列中指定的第一列，第二列将用来填充表列中指定的第二列，如此等等。
    - INSERT SELECT中SELECT语句可以包含WHERE子句，以过滤插入的数据。
- INSERT通常只插入一行。要插入多行，必须执行多个INSERT语句
从一个表复制到另一个表,使用CREATE SELECT语句
- CREATE TABLE CustCopy AS SELECT ＊ FROM Customers;
  - 创建一个名为CustCopy的新表，并把Customers表的整个内容复制到新表中。因为这里使用的是SELECT＊，所以将在CustCopy表中创建（并填充）与Customers表的每一列相同的列。要想只复制部分的列，可以明确给出列名，而不是使用＊通配符
更新和删除数据
- UPDATE Customers SET cust_email = NULL WHERE cust_id = 105;
删除特定的行
- DELETE FROM Customers WHERE cust_id = 106;
如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，而速度更快（因为不记录数据的变动）。
在 SQLite 中，并没有 TRUNCATE TABLE 命令，但可以使用 SQLite 的 DELETE 命令从已有的表中删除全部的数据。
更新和删除的指导原则：
- 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
- 保证每个表都有主键（如果忘记这个内容，请参阅第12课），尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。
- 在UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
- 使用强制实施引用完整性的数据库（关于这个内容，请参阅第12课），这样DBMS将不允许删除其数据与其他表相关联的行。
- 有的DBMS允许数据库管理员施加约束，防止执行不带WHERE子句的UPDATE或DELETE语句。如果所采用的DBMS支持这个特性，应该使用它。
创建和操纵表
- NULL值就是没有值或缺值。允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受没有列值的行，换句话说，在插入或更新行时，该列必须有值。
  - ALTER TABLE Vendors ADD vend_phone CHAR(20);
  - ALTER TABLE Vendors DROP COLUMN vend_phone;
- 使用ALTER TABLE要极为小心，应该在进行改动前做完整的备份（表结构和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，也许无法删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据
删除表
- DROP TABLE CustCopy;
视图
- 视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
- 视图为虚拟的表。它们包含的不是数据而是根据需要检索数据的查询。视图提供了一种封装SELECT语句的层次，可用来简化数据处理，重新格式化或保护基础数据。
- SQLite仅支持只读视图，所以视图可以创建，可以读，但其内容不能更改。
为什么使用视图：
- 重用SQL语句
- 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。
- 使用表的一部分而不是整个表。❑ 保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。
- 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。创建视图之后，可以用与表基本相同的方式使用它们。可以对视图执行SELECT操作，过滤和排序数据，将视图联结到其他视图或表，甚至添加和更新数据（添加和更新数据存在某些限制，关于这个内容稍后做介绍）。
注意：
- 视图仅仅是用来查看存储在别处数据的一种设施。视图本身不包含数据，因此返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。
- 因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时需要的所有检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，性能可能会下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。
视图的规则和限制：
- 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。
- 对于可以创建的视图数目没有限制。
- 创建视图，必须具有足够的访问权限。这些权限通常由数据库管理人员授予。
- 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造视图。所允许的嵌套层数在不同的DBMS中有所不同（嵌套视图可能会严重降低查询的性能，因此在产品环境中使用之前，应该对其进行全面测试）。
- 许多DBMS禁止在视图查询中使用ORDER BY子句。
- 有些DBMS要求对返回的所有列进行命名，如果列是计算字段，则需要使用别名（关于列别名的更多信息，请参阅第7课）。
- 视图不能索引，也不能有关联的触发器或默认值。
- 有些DBMS把视图作为只读的查询，这表示可以从视图检索数据，但不能将数据写回底层表。详情请参阅具体的DBMS文档。
- 有些DBMS允许创建这样的视图，它不能进行导致行不再属于视图的插入或更新。例如有一个视图，只检索带有电子邮件地址的顾客。如果更新某个顾客，删除他的电子邮件地址，将使该顾客不再属于视图。这是默认行为，而且是允许的，但有的DBMS可能会防止这种情况发生。
创建视图
- 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;
  - 创建一个名为ProductCustomers的视图，它联结三个表
存储过程
- 存储过程就是为以后使用而保存的一条或多条SQL语句。可将其视为批文件，虽然它们的作用不仅限于批处理
- SQLite不支持存储过程。
为什么要使用存储过程：
- 通过把处理封装在一个易用的单元中，可以简化复杂的操作（如前面例子所述）。
- 由于不要求反复建立一系列处理步骤，因而保证了数据的一致性。如果所有开发人员和应用程序都使用同一存储过程，则所使用的代码都是相同的。
- 上一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
- 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，那么只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
- 上一点的延伸就是安全性。通过存储过程限制对基础数据的访问，减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。
- 因为存储过程通常以编译过的形式存储，所以DBMS处理命令所需的工作量少，提高了性能。
- 存在一些只能用在单个请求中的SQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。
执行存储过程
创建存储过程
事务 transaction processing
- 事务处理是一种机制，用来管理必须成批执行的SQL操作，保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们要么完全执行，要么完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表；如果发生错误，则进行回退（撤销），将数据库恢复到某个已知且安全的状态。
保留点（savepoint）指事务处理中设置的临时占位符（placeholder），可以对它发布回退（与回退整个事务处理不同）。
可以回退哪些语句？
- 事务处理用来管理INSERT、UPDATE和DELETE语句。不能回退SELECT语句（回退SELECT语句也没有必要），也不能回退CREATE或DROP操作。事务处理中可以使用这些语句，但进行回退时，这些操作也不撤销。
开始事务
- begin transaction
- commit transaction
SQL的ROLLBACK命令用来回退（撤销）SQL语句
- DELETE FROM Orders; ROLLBACK;
使用保留点
- 使用简单的ROLLBACK和COMMIT语句，就可以写入或撤销整个事务。但是，只对简单的事务才能这样做，复杂的事务可能需要部分提交或回退。
- 要支持回退部分事务，必须在事务处理块中的合适位置放置占位符。这样，如果需要回退，可以回退到某个占位符(保留点)。
  - 在事务中创建保留点：SAVE TRANSACTION delete1;
  - 执行回退到保留点：ROLLBACK TRANSACTION delete1;
游标
- 有时，需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。游标（cursor）是一个存储在DBMS服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
SQLite支持的游标称为步骤（step）
游标选项和特性:
- 能够标记游标为只读，使数据能读取，但不能更新和删除。
- 能控制可以执行的定向操作（向前、向后、第一、最后、绝对位置和相对位置等）。
- 能标记某些列为可编辑的，某些列为不可编辑的。
- 规定范围，使游标对创建它的特定请求（如存储过程）或对所有请求可访问。
- 指示DBMS对检索出的数据（而不是指出表中活动数据）进行复制，使数据在游标打开和访问期间不变化。
创建游标
高级SQL特性
约束（constraint）:管理如何插入或处理数据库数据的规则。
通过在数据库表上施加约束来实施引用完整性。大多数约束是在表定义中定义的
- 订单表当中一定要有数据，订单详情表才会有数据，虽然可以在插入新行时进行检查（在另一个表上执行SELECT，以保证所有值合法并存在），但最好不要这样做，原因如下：
  - 如果在客户端层面上实施数据库完整性规则，则每个客户端都要被迫实施这些规则，一定会有一些客户端不实施这些规则。
  - 在执行UPDATE和DELETE操作时，也必须实施这些规则。
  - 执行客户端检查是非常耗时的，而DBMS执行这些检查会相对高效。
在 SQL 中，我们有如下约束：
- NOT NULL - 指示某列不能存储 NULL 值。
- UNIQUE - 保证某列的每行必须有唯一的值。
- PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
- FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
- CHECK - 保证列中的值符合指定的条件。
- DEFAULT - 规定没有给列赋值时的默认值。
- INDEX - 用于快速访问数据库表中的数据。
外键
- 外键是表中的一列，其值必须列在另一表的主键中。外键是保证引用完整性的极其重要部分。
- CREATE TABLE IF NOT EXISTS MESSAGE_DIRECTED_USERS(...) FOREIGN KEY (msg_id) REFERENCES MESSAGE(id) ON DELETE CASCADE
  - 这部分定义了一个外键约束（FOREIGN KEY）。外键用于在两个表之间建立关系，确保数据的一致性和完整性。
  - REFERENCES 关键字后面跟的是被引用的表名（MESSAGE）和引用的列名（id）。这意味着msg_id字段在MESSAGE_DIRECTED_USERS表中的值必须与MESSAGE表中某个记录的id字段匹配。
  - ON DELETE CASCADE：这是外键约束的一个特性，当主表（指MESSAGE）中的相关记录被删除时，从表（指MESSAGE_DIRECTED_USERS）中所有关联的记录也将被自动删除。这种行为称为级联删除（Cascade Delete）。
唯一约束:用来保证一列（或一组列）中的数据是唯一的。它们类似于主键，但存在以下重要区别。
检查约束:用来保证一列（或一组列）中的数据满足一组指定的条件
索引
- 用来排序数据以加快搜索和排序操作的速度
- 索引注意事项：
  - 索引数据可能要占用大量的存储空间。
  - 并非所有数据都适合做索引。取值不多的数据（如州）不如具有更多可能值的数据（如姓或名），能通过索引得到那么多的好处。
  - 索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。
  - 可以在索引中定义多个列（例如，州加上城市）。这样的索引仅在以州加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。
CREATE INDEX prod_name_ind ON Products (prod_name);
索引的效率随表数据的增加或改变而变化。许多数据库管理员发现，过去创建的某个理想的索引经过几个月的数据处理后可能变得不再理想了。最好定期检查索引，并根据需要对索引进行调整。
触发器 TRIGGER
- 触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。触发器可以与特定表上的INSERT、UPDATE和DELETE操作（或组合）相关联。
- 触发器内的代码具有以下数据的访问权：
  - INSERT操作中的所有新数据；
  - UPDATE操作中的所有新数据和旧数据；
  - DELETE操作中删除的数据。
- 触发器的一些常见用途:
  - 保证数据一致。例如，在INSERT或UPDATE操作中将所有州名转换为大写。
  - 基于某个表的变动在其他表上执行活动。例如，每当更新或删除一行时将审计跟踪记录写入某个日志表。
  - 进行额外的验证并根据需要回退数据。例如，保证某个顾客的可用资金不超限定，如果已经超出，则阻塞插入。
  - 计算计算列的值或更新时间戳。
约束比触发器更快
- 一般来说，约束的处理比触发器快，因此在可能的时候，应该尽量使用约束。
CREATE TRIGGER