《SQL必知必会》学习笔记（下）

《SQL必知必会》学习笔记（下）
这是我阅读《SQL必知必会》归纳整理的重点和易错点，分为上下两篇笔记，上篇在这里：《SQL必知必会》学习笔记（上）

第12课联结表

联结是一种机制，用来在一条SELECT语句中关联表，因此称为联结。

创建联结非常简单，指定要联结的所有表以及关联它们的方式即可：
```
SELECT vend_name, prod_name, prod_price FROM Venders, Products
WHERE Venders.vend_id = Products.vend_id;
```
在使用联表查询时，引用的列名通常使用完全限定列名（表名.列名）。

第13课创建高级联结

SQL除了可以对列名和计算字段使用别名，还允许给表名起别名。
```
SELECT vend_name, prod_name, prod_price FROM Venders AS V, Products AS P
WHERE V.vend_id = P.vend_id;
```
注意，Oracle不支持AS关键字。要在Oracle中使用别名，可以不用AS，简单地指定列名即可（因此，应该是Customers C，而不是Customers AS C）。

自联结

普通的联表查询称为内联结，两个表通过相同的属性联结起来。

还有一种联结，自联结，自己和自己联结：
```
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';
```
在自联结的使用，一定要使用 AS 关键字，使用表别名。

外联结

外联结分为左联结和右联结。

先看左联结：
```
SELECT Customers.cust_id, Orders.order_num 
FROM Customers LEFT JOIN Orders ON Customers.cust_id = Orders_cust_id;
```
使用LEFT JOIN,表示从FROM子句左边的表（Customers表）中选择所有行。

再看右联结：
```
SELECT Customers.cust_id, Orders.order_num 
FROM Customers RIGHT JOIN Orders ON Customers.cust_id = Orders_cust_id;
```
使用RIGHT JOIN,表示从FROM子句右边的表（Orders 表）中选择所有行。

第14课组合查询

SQL允许执行多个查询（多条SELECT语句），并将结果作为一个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。

主要有两种情况需要使用组合查询：
- 在一个查询中从不同的表返回结构数据；
- 对一个表执行多个查询，按一个查询返回数据。
UNION操作符

使用UNION很简单，所要做的只是给出每条SELECT语句，在各条语句之间放上关键字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';
```
可以使用多条WHERE子句代替组合查询，结果相同：
```
SELECT cust_name, cust_contact, cust_email FROM Customers 
WHERE cust_state IN('IL','IN','MI') AND cust_name = 'Fun4ALL';
```
第15课插入数据

把数据插入表中的最简单方法是使用基本的INSERT语法，它要求指定表名和插入到新行中的值。
```
INSERT INTO Customers VALUES(001, 'Toy','123');
```
上面的SQL语句高度依赖于表中列的定义次序,编写依赖于特定列次序的SQL语句是很不安全的。

编写INSERT语句的更安全（不过更烦琐）的方法如下：
```
INSERT INTO Customers(cust_id, cust_name, cust_zip) VALUES(001, 'Toy','123');
```
插入检索出的数据

可以利用 INSERT 将SELECT语句的结果插入表中，这就是所谓的INSERT SELECT。顾名思义，它是由一条INSERT语句和一条SELECT语句组成的。
```
INSERT INTO Customers(cust_id, cust_name, cust_zip)
SELECT cust_id, cust_name, cust_zip FROM CustNew;
```
这个例子从一个名为CustNew的表中读出数据并插入到Customers表。

NSERT通常只插入一行。要插入多行，必须执行多个INSERT语句。INSERT SELECT是个例外，它可以用一条INSERT插入多行，不管SELECT语句返回多少行，都将被INSERT插入。

INSERT SELECT中SELECT语句的列名并不重要，重要的是列的位置。

从一个表复制到另一个表

要将一个表的内容复制到一个全新的表（运行中创建的表），可以使用CREATE SELECT语句（或者在SQLServer里也可用SELECT INTO语句）。
```
CREATE TABLE CustCopy AS SELECT * FROM Customers;
```
若是使用SQL Server，可以这么写：
```
SELECT * INTO CustCopy FROM Customers;
```
这条SELECT语句创建一个名为CustCopy的新表，并把Customers表的整个内容复制到新表中。

第16课更新和删除数据

UPDATE语句
```
UPDATE Customers SET cust_email = '147@qq.com'
WHERE cust_id = 001;
```
UPDATE语句总是以要更新的表名开始,以WHERE子句结束。

DELETE语句

下面的语句从Customers表中删除一行：
```
DELETE FROM Customers WHERE cust_id = 001;
```
DELETE不需要列名或通配符。DELETE删除整行而不是删除列。

DELETE语句从表中删除行，甚至是删除表中所有行。但是，DELETE不删除表本身。

第17课创建和操纵表

一般有两种创建表的方法：
1. 多数DBMS都具有交互式创建和管理数据库表的工具；
2. 表也可以直接用SQL语句操纵。
示例：
```
CREATE TABLE `ums_role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL COMMENT '名称',
  `description` varchar(500) DEFAULT NULL COMMENT '描述',
  `admin_count` int(11) DEFAULT NULL COMMENT '后台用户数量',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `status` int(1) DEFAULT '1' COMMENT '启用状态：0->禁用；1->启用',
  `sort` int(11) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='后台用户角色表'
```
更新表

更新表定义，可以使用ALTER TABLE语句。

示例：
```
ALTER TABLE Venders ADD ven_phone CHAR(20);
```
这条语句给Vendors表增加一个名为vend_phone的列，其数据类型为CHAR。

删除表

删除表（删除整个表而不是其内容）非常简单，使用DROP TABLE语句即可：
```
DROP TABLE CustCopy;
```
注意，DELETE 是删除表数据，DROP 是删除表本身。

第18课使用视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

为什么使用视图？
1. 重用SQL语句。
2. 简化复杂的SQL操作。
3. 使用表的一部分而不是整个表。
4. 保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。
5. 更改数据格式和表示。
视图的规则和限制
1. 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。
2. 对于可以创建的视图数目没有限制。
3. 创建视图，必须具有足够的访问权限。
4. 视图可以嵌套。
5. 视图不能索引，也不能有关联的触发器或默认值。
创建视图

视图用CREATE VIEW语句来创建。
```
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的视图，它联结三个表，返回已订购了任意产品的所有顾客的列表.

检索订购了产品RGAN01的顾客，可如下进行：
```
SELECT cust_name, cust_contact FROM ProductCustomers WHERE prod_id = 'RGAN01';
```
利用视图，可一次性编写基础的SQL，然后根据需要多次使用。

删除视图

可以使用DROP语句，其语法为DROP VIEW viewname;。覆盖（或更新）视图，必须先删除它，然后再重新创建。

第19课使用存储过程

简单来说，存储过程就是为以后使用而保存的一条或多条SQL语句。可将其视为批文件，虽然它们的作用不仅限于批处理。

还有，SQLite不支持存储过程。

说到底，存储过程就是将部分计算逻辑，从后端转移到数据库。

执行存储过程

执行存储过程的SQL语句很简单，即EXECUTE。EXECUTE接受存储过程名和需要传递给它的任何参数。

示例：
```
EXECUTE AddNewProduct('JS01', 'Stuffed Eiffe', 7, 'xxx');
```
创建存储过程

参考文章：mysql存储过程详细教程

第20课管理事务处理

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

利用事务处理，可以保证一组操作不会中途停止，它们要么完全执行，要么完全不执行（除非明确指示）。

控制事务处理

在 MariaDB和MySQL中：
```
START TRANSACTION
...
CONNIT TRANSACTION
```
具体参考文章：MySQL 事务

第21课使用游标

有时需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

创建游标

使用DECLARE语句创建游标
```
DECLARE CustCursor CURSOR FOR
SELECT * FROM Customers WHERE cust_email IS NULL;
```
使用游标

使用OPEN CURSOR语句打开游标：
```
OPEN CURSOR CustCursor;
```
关闭游标
```
CLOSE CustCursor;
```
具体参考文章：MySQL 游标的使用

第22课高级SQL特性

主键

主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动。

示例：
```
CREATE TABLE Vendors(
  vend_id   CHAR(10) NOT NULL PRIMARY KEY,
  vend_name CHAR(50) NOT NULL,
  ... 
);
```
外键

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

示例：
```
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)
);
```
其中的表定义使用了REFERENCES关键字，它表示cust_id中的任何值都必须是Customers表的cust_id中的值。

索引

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

使用索引的注意事项：
1. 索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。
2. 索引数据可能要占用大量的存储空间。
3. 并非所有数据都适合做索引。
4. 索引用于数据过滤和数据排序。
5. 可以在索引中定义多个列
索引用CREATE INDEX语句创建：
```
CREATE INDEX prod_nam_ind
ON Products(prod_name);
```
索引必须唯一命名。这里的索引名prod_name_ind在关键字CREATE INDEX之后定义。ON用来指定被索引的表，而索引中包含的列（此例中仅有一列）在表名后的圆括号中给出。

触发器

触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。

与存储过程不一样（存储过程只是简单的存储SQL语句），触发器与单个的表相关联。

触发器内的代码具有以下数据的访问权：
1. INSERT操作中的所有新数据；
2. UPDATE操作中的所有新数据和旧数据；
3. DELETE操作中删除的数据。
一个触发器示例，它对所有INSERT和UPDATE操作，将Customers表中的cust_state列转换为大写，本例子基于SQL Server版本：
```
CREATE TRIGGER customer_state 
ON Customers 
FOR INSERT,UPDATE
AS
UPDATE Customers SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = inserted.cust_id;
```
数据库安全

安全性使用SQL的GRANT和REVOKE语句来管理。

附录 SQL数据类型

字符串数据类型

最常用的数据类型是字符串数据类型，有两种基本的字符串类型，分别为定长字符串和变长字符串。

定长字符串接受长度固定的字符串，其长度是在创建表时指定的。它们分配的存储空间与指定的一样多。缺少的字符用空格填充，或根据需要补为NULL。

变长字符串存储任意长度的文本(最大长度不超过指定值)，不管是哪种，只有指定的数据得以保存（额外的数据不保存）。

既然变长数据类型这样灵活，为什么还要使用定长数据类型？答案是性能。DBMS处理定长列远比处理变长列快得多。

此外，许多DBMS不允许对变长列（或一个列的可变部分）进行索引，这也会极大地影响性能。

数值数据类型

货币数据类型

多数DBMS支持一种用来存储货币值的特殊数值数据类型。一般记为MONEY或CURRENCY.

日期和时间数据类型

二进制数据类型

常用SQL语句速查

ALTER TABLE

ALTER TABLE用来更新现存表的模式。可以用CREATE TABLE来创建一个新表。详情可参见第17课。

COMMIT

COMMIT用来将事务写入数据库。详情可参见第20课。

CREATE INDEX

CREATE INDEX用来为一列或多列创建索引。详情可参见第22课。

CREATE TABLE

CREATE TABLE用来创建新的数据库表。可以用ALTER TABLE来更新一个现存表的模式。详情可参见第17课。

CREATE VIEW

CREATE VIEW用来创建一个或多个表的视图。详情可参见第18课。

DELETE

DELETE用来从表中删除一行或多行。详情可参见第16课。

DROP

DROP用来永久性地删除数据库对象（表、视图和索引等）。详情可参见第17课和第18课。

INSERT

INSERT用来对表添加一个新行。详情可参见第15课。

INSERT SELECT

INSERT SELECT用来将SELECT的结果插入到表中。详情可参见第15课。

ROLLBACK

ROLLBACK用来撤销事务块。详情可参见第20课。

SELECT

SELECT用来从一个或多个表（或视图）中检索数据。详情可参见第2课、第3课和第4课（第2课到第14课从不同方面涉及了SELECT）。

UPDATE

UPDATE用来对表中的一行或多行进行更新。详情可参见第16课。

每天学习一点点，每天进步一点点。
相关阅读:
php获取OS信息
 坑爹的IE quirk模式
 [转]mysql privileges
[转]VSFTPD的设置选项
 php性能分析工具xhprof
php扩展安装
 又是万恶的IE....6
ASP.NET操作文件(文件夹)简单生成html操作示例
 asp.net文件操作类
 c++/clr与c#的性能比较
原文地址：https://www.cnblogs.com/youcoding/p/15272186.html

《SQL必知必会》学习笔记（下）

第12课 联结表

第13课 创建高级联结

自联结

外联结

第14课 组合查询

UNION操作符

第15课 插入数据

插入检索出的数据

从一个表复制到另一个表

第16课 更新和删除数据

UPDATE语句

DELETE语句

第17课 创建和操纵表

更新表

删除表

第18课 使用视图

为什么使用视图？

视图的规则和限制

创建视图

删除视图

第19课 使用存储过程

执行存储过程

创建存储过程

第20课 管理事务处理

控制事务处理

第21课 使用游标

创建游标

使用游标

关闭游标

第22课 高级SQL特性

主键

外键

索引

触发器

数据库安全

附录 SQL数据类型

字符串数据类型

数值数据类型

货币数据类型

日期和时间数据类型

二进制数据类型

常用SQL语句速查

第12课联结表

第13课创建高级联结

第14课组合查询

第15课插入数据

第16课更新和删除数据

第17课创建和操纵表

第18课使用视图

第19课使用存储过程

第20课管理事务处理

第21课使用游标

第22课高级SQL特性