SQL基础
欢迎来到SQL世界
一、SQL定义及历史
- 什么是SQL
结构化查询语言（SQL）是与关系型数据库进行通信的标准语言。 - 什么是ANSI SQL
SQL作为关系型数据库所使用的标准语言 被 作为国际标准。 - 新标准：SQL-2008
SQL-2008由9个相关的文档组成
第1部分--SQL/架构：指定实现一致性的一般性需求，定义SQL的基本概念。
第2部分--SQL/基础：定义SQL的语法和操作。
第3部分--SQL/调用级接口：定义程序编程与SQL的接口。
第4部分--SQL/持久存储模块：定义控制结构，进而定义SQL例程。还定义了包含SQL例程的模块。
第9部分--外部数据管理（SQL/MED）：定义SQL的扩张，用于通过使用数据包裹支持外部数据管理；还定义了数据链类型。
第10部分--对象语言绑定：定义SQL的扩张，支持把SQL语句内嵌到用Java编写的程序。
第11部分--信息和定义方案：定义信息方案和定义方案的规范，提供与SQL数据相关的结构和安全信息。
第13部分--使用Java编程语言的例程和类型：定义以SQL例程形式调用Java静态例程和类的功能。
第14部分--XML相关规范：定义SQL使用XML的方式。 - 什么是数据库
我们可以把数据库看成这样一种有组织的机制：它能够存储信息，用户能够以有效且高效的方式检索其中的信息。 - 关系型数据库
关系型数据库由称为表的逻辑单元组成，表之间通过共同的关键字彼此关联。 - 客户端/服务器技术
在客户端/服务器系统里，主机被称为服务器，访问服务器的被称为客户端。 - 基于Web的数据库系统
基于Web的数据库系统，是通过浏览器访问数据库。 - 主流数据库厂商
闭源版本：Oracle、Microsoft、Infomix、Sybase、IBM
开源版本：MySql、PostgresSQL、SAP
二、SQL会话
- SQL会话是用户利用SQL命令与关系型数据库进行交互时发生的事情。
- CONNECT
命令CONNECT用于建立与数据库的连接，它可以申请连接，也可以修改连接。
连接数据库通常需要用到以下命令：
CONNECT user@database 要求输入与当前用户对应的密码。 - DISCONNECT和EXIT
命令DISCONNECT用于断开用户与数据库的连接。
DICONNECT 当中断与数据库的连接以后，用户所使用的程序可能显得还在与数据库通讯，但实际上已经没有连接了。
命令EXIT用于离开数据库。
EXIT 当离开数据库时，SQL会话就结束了，而且用于访问数据库的软件通常会关闭。
三、SQL命令的类型
- SQL命令的类型包括：
数据定义语言（DDL）
数据操作语言（DML）
数据查询语言（DQL）
数据控制语言（DCL）
数据管理命令（DAC）
事务控制命令（TCC） - 数据定义语言
数据定义语言用于创建和重构数据库对象。
一些最基本的DDL命令包括：
CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE INDEX
ALTER INDEX
DROP INDEX
CREATE VIEW
DROP VIEW - 数据操作语言
数据操作语言用于操作关系型数据库对象内部的数据。
3个基本DML命令是：
INSERT
UPDATE
DELETE - 数据查询语言
数据查询语言（DQL）是现代关系型数据库用户最关注的部分。
只具有一个命令：
SELECT - 数据控制语言
数据控制语言用于控制对数据库里数据的访问。
这些控制命令包括：
ALTER PASSWORD
GRANT
REVOKE
CREATE SYNONYM - 数据管理命令
数据管理命令用于对数据库里的操作进行审计和分析，还有助于分析系统性能。
常用的两个数据管理命令如下：
START AUDIT
STOP AUDIT - 事务控制命令
事务控制命令用于管理数据库事务。
命令如下：
COMMIT：保存数据库事务。
ROLLBACK：撤销数据库事务。
SAVEPOINT：在一组事务里创建标记点以用于回退（ROLLBACK）。
SET TRANSACTION：设置事务的名称。
四、数据库
- 表命名标准
所谓的命名标准，只是为了在创建对象的时候有一定的准则可以用来遵循。 - 数据一瞥
- 表的构成
1、字段
字段是表里的一列，用于保持每条记录的特定信息。
2、记录或一行数据
记录，也被称为一行数据，是表里的各行。
3、列
列是表里垂直的一项，包含表里特定字段的全部信息。
4、主键
主键用于区分表里的每条数据行。
主键的特性确保了所有产品标识都是唯一的，并且还有其他用途。
5、NULL值
NULL是表示“没有值”的专用术语。
NULL并不等同于0或者空格。
定义数据类型
一、数据是什么
- 数据
数据是一个信息集合，以某种数据类型保存在数据库里。 - 数据类型
数据类型用于指定特定列所包含数据的规则。
二、基本数据类型
- 最基本的数据类型是：
1、字符串类型
2、数值类型
3、日期和时间类型 - 定长字符串
定长字符串通常具有相同的长度，是使用定长数据类型保存的。
定长字符串的标准：
CHARACTER(n)，n是一个数字，定义了字段里能够保存的最多字符数量。
有些SQL实现使用CHAR数据类型来保存定长数据。 - 变长字符串
SQL 支持变长字符串，也就是长度不固定的字符串。
变长字符串的标准：
CHARACTER VARYING(n) n是一个数字，表示字段里能够保存的最多字符数量。
VARCHAR 是 ANSI 标准，Microsoft SQL Server 和 MySQL 也使用它。
VARINARY 和 VARCHAR2 都是由 Oracle使用的。
VARINARY 包含的是长度不定的字节。 - 大对象类型
有些变长数据类型需要保存更长的数据，超过了一般情况下为VARCHAR字段所保留的长度。
TEXT 数据类型是一种长字符串类型。
BLOB 是二进制大对象，它的数据是很长的二进制字符串。 - 数值类型
数值被保存在定义为某种数值类型的字段里。
SQL数值的标准：
BIT(n)
BIT VARYING(n)
DECIMAL(p,s)
INTEGER
SMALLINT
BIGINT
FLOAT(p,s)
DOUBLE PRECISION(p,s)
REAL(s)
p表示字段的最大长度
s表示小数点后面的位数
SQL实现中一个通用的数值类型是NUMBERIC，它符合ANSI标准：NUMBERIC(5) - 小数类型
小数类型是指包含小数点的数值。
标准：DECIMAL(p,s) p表示有效数位，s表示标度。 - 整数
整数是不包含小数点的数值 - 浮点数
浮点数是有效数位和标度都可变并且没有限制的小数数值。
数据类型REAL代表单精度浮点数值。
数据类型DOUBLE PRECISION表示双精度浮点数值。 - 日期和时间类型
日期和时间数据类型很显然是用于保存日期和时间信息的。
标准SQL支持DATETIME数据类型，它包含以下类型：
DATE
TIME
DATETIME
TIMESTAMP
DATETTIME数据类型的元素包括：
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND - 直义字符串
直义字符串就是一系列字符。
直义字符串包含的数据与前面介绍的数据类型具有一样的属性，但字符串的值是已知的。 - NULL数据类型
NULL值表示没有值
引用NULL值的方法：
NULL（关键字NULL本身）
NULL并不等同于0或者空格或者'NULL'。
如果某个字段必须包含数据，就把它设置为NOT NULL。只要字段有可能不包含数据，最好就把它设置为NULL。 - 布尔值
布尔值的取值范围是TRUE、FALSE、NULL，用于进行数据比较。
MySQL拥有BOOLEAN类型，但实质上与其现有的TINYINT类型相同。
Oracle倾向于让用户使用一个CHAR(1)值来代替布尔值。
SQL Server则使用BIT来代替。 - 自定义类型
自定义类型是由用户定义的类型，它允许用户根据已有的数据类型来定制自己的数据类型，从而满足数据存储的需要。
在MySQL和Oracle中，可以像下面这样创建一个类型：
CREATE TYPE PERSON AS OBJECT(
NAME VARCHAR(30),
SSN VARCHAR(9));
然后可以像下面这样引用自定义类型：
CREATE TABLE EMP_PAY(
EMPLOYEE PERSON,
SALARY DECIMAL(10, 2),
HIRE_DATE DATE); - 域
域是能够被使用的有效数据类型的集合。
域的使用类似于自定义类型。像下面这样创建域：
CREATE DOMAIN MONEY_D AS NUMBER(8,2);
像下面这样为域添加约束：
ALTER DOMAIN MONEY_D
ADD CONSTRAINT MONEY_CON1
CHECK(VALUE > 5);
然后像下面这样引用域：
CREATE TALBE EMP_PAY(
EMP_ID NUMBER(9),
EMP_NAME VARCHAR2(30),
PAY_RATE MONEY_D);
管理数据库对象
一、什么是数据库对象
- 数据库对象
数据库对象是数据库里定义的、用于存储或引用数据的对象，比如表、视图、簇、序列、索引和异名。
二、什么是规划
- 规划
规划是与数据库某个用户名相关联的数据库对象集合。
三、表：数据的主要存储方式
- 列
字段在关系型数据库也被称为列，它是表的组成部分，被设置为特定的数据类型。 - 行
行是数据库表里的一条记录。 - CREATE TABLE 语句
创建表的基本语法如下所示：
CREATE TABLE table_name(
field1 data_type [ not null ],
field2 data_type [ not null ],
field3 data_type [ not null ],
field4 data_type [ not null ],
field5 data_type [ not null ]
); - 命名规范
让名称反应出所保存的数据 - ALTER TABLE 命令
ALTER TABLE 命令的标准如下所示：
alter table table_name [modify] [column column_name] [datatype | null not null]
[restrict | cascade]
[drop] [constraint constraint_name]
[add] [column] column definition
1、修改表的元素
列的属性是其所包含数据的规则和行为。属性的含义是：
列的数据类型；
列的长度、有效位数和标度；
列值能否为空。
2、添加列
添加 NOT NULL 列的方法：
1）添加一列，把它定义为NULL；
2）给这个新列在每条记录里都插入数据；
3）把列的定义修改为NOT NULL。
3、添加自动增加的列
MySQL：SERIAL
Microsoft SQL Server：IDENTITY
ORACLE：SEQUENCE
4、修改列
通用规则：
列的长度可以增加到特定数据类型所允许的最大长度；
如果想缩短某列的长度，则必须要求这一列在表里所有数据的长度都小于或等于新长度；
数值数据的位数可以增加；
如果要缩短数值数据的位数，则必须要求这一列在表里所有数值的位数小于或等于新指定的位数；
数值里的小数位数可以增加或减少；
列的数据类型一般是可以改变的。 - 从现有表新建另一个表
MySQL、Oracle：
create table new_table_name as
select [ * | column1, column2 ]
from table_name
[ where ]
SQL Server：
select [ * | column1, column2 ]
into new_table_name
from table_name
[ where ] - 删除表
drop table table_name [restrict | cascade ]
如果使用了RESTRICT选项，并且表被视图或约束所引用，DROP语句会返回一个错误。
当使用了CASCADE选项时，删除操作会成功执行，而且全部引用和视图和约束都被删除。
SQL Server不能使用CASCADE选项。因此，要在SQL Server中删除表，
必须同时删除与该表有引用关系的所有对象，以免系统中遗留无效对象。
四、完整性约束
- 完整性约束用于确定关系型数据库里数据的准确性和唯一性。
- 一、主键约束
主键是表里一个或多个用于实现记录唯一性的字段。
隐含约束：
EMP_ID CHAR(9) NOT NULL PRIMARY KEY
明确指定：
PRIMARY KEY (EMP_ID)
包含多个字段的主键可以用以下两种方式之一来定义，适用于Oracle数据库：
PRIMARY KEY (PROD_ID, VEND_ID)
ALTER TABLE PRODUCTS_TST
ADD CONSTRAINT PRODUCTS_PK PRIMARY KEY (PROD_ID, VEND_ID) - 二、唯一性约束
唯一性约束要求表里某个字段的值在每条记录里都是唯一的。
隐含约束：EMP_PHONE INTEGER(10) NULL UNIQUE - 三、外键约束
外键是子表里的一个字段，引用父表里的主键。
方式一：
CONSTRINT EMP_ID_FK FOREIGN KEY(EMP_ID) REFERENCES EMPLOYEE_TBL(EMP_ID)
方式二：
ALTER TABLE EMPLOYEE_PAY_TBL
ADD CONSTRAINT ID_FK FOREIGN KEY (EMP_ID)
REFERENCES EMPLOYEE_TBL(EMP_ID) - 四、NOT NULL 约束
- 五、检查约束
检查（CHK）约束用于检查输入到特定字段的数据的有效性。
CONSTRAINT CHK_EMP_ZIP CHECK(条件）。
在检查约束里可以使用几乎任何条件，就像在SQL查询里一样。 - 六、去除约束
利用ALTER TABLE命令的DROP CONSTRAINT选项可以去除已经定义的约束。
ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK
MySQL去除特定约束的快捷方式：
ALTER TABLE EMPLOYEES DROP PRIMARY KEY
规格化过程
一、规格化数据库
- 规格化
规格化是去除数据库里冗余数据的过程，在设计和重新设计数据库时使用。
它是一组减少数据冗余来优化数据库的指导方针，具体的方针被称为规格形式。 - 原始数据库
- 数据库逻辑设计
数据库逻辑数据，也被称为逻辑建模，是把数据安排到逻辑的、有组织的对象组，以便维护的过程。
1、什么是终端用户的需求
在设计时要考虑的与用户相关的因素包含：
1）数据库里应该保存什么数据
2）用户如何访问数据库
3）用户需要什么权限
4）数据库里的数据如何分组
5）哪些数据最经常被访问
6）全部数据与数据库如何关联
7）采取什么措施保证数据的正确性
8）采取什么措施减少数据冗余
9）采取什么措施让负责维护数据的用户更易于使用数据库
2、数据冗余
数据应该没有冗余；这意味着重复的数据应该保持到最少。 - 规格形式
规格形式是衡量数据库被规格化级别（或深度）的一种方式。
数据库的规格化级别是由规格形式决定的。
规格化过程中最常见的3种规格形式：
1）第一规格形式
2）第二规格形式
3）第三规格形式
1、第一规格形式
第一规格形式的目标是把原始数据分解到表中。在所有表都设计完成后，给大多数表或全部表设置一个主键。
2、第二规格形式
第二规格形式的目标是提取对主键仅有部分依赖的数据，把它们保存到另一个表里。
3、第三规格形式
第三规格形式的目标是删除表里不依赖于主键的数据。 - 命名规范
- 规格化的优点
规格化为数据库带来了很多好处，主要包括以下几点：
1）更好的数据库整体组织性；
2）减少冗余数据；
3）数据库内部的数据一致性；
4）更灵活的数据库设计；
5）更好地处理数据库安全；
6）加强引用整体性的概念。 - 规格化的缺点
降低数据库性能。
性能降低的程度取决于查询或事务被提交给数据库的时机。
二、去规格化数据库
- 去规格化
去规格化是修改规格化数据库的表的构成，在可控制的数据冗余范围内提高数据库性能。
优点：提高性能
缺点：增加了数据冗余
操作数据
一、数据操作概述
- SQL里3个基本的DML命令：
INSERT
UPDATDE
DELETE
二、用新数据填充表
- 用数据填充表就是把新数据输入到表的过程。
- 把数据插入到表
INSERT语句可以把数据插入到表，它具有一些选项，其基本语法如下所示：
INSERT INTO TABLE_NAME
VALUES ('value1', 'value2', [null]);
在使用这种语法时，必须在VALUES列表里包含表里的每个列。在这个列表里，每个值之间是以逗号分隔的。
字符、日期和时间数据类型的值必须以单引号包围，而数值或NULL值就不必了。
表里的每一列都应该有值，并且值的顺序与列在表里的次序一致。 - 给表里指定列插入数据
给表中特定列插入数据的语法：
INSERT INTO TABLE_NAME('COLUMN1', 'COLUMN2')
VALUES('VALUE1', 'VALUE2');
INSERT语句里的字段列表次序并不一定要与表定义中的字段次序相同，
但插入值的次序要与字段列表的次序相同。 - 从另一个表插入数据
从另一个表插入数据的语法：
INSERT INTO TABLE_NAME [('COLUMN1', 'COLUMN2')]
SELECT [* | ('COLUMN1', 'COLUMN2')]
FROM TABLE_NAME
[WHERE CONDITION(S)];
SELECT是SQL里执行查询的主要命令；
FROM是查询中的一个子句，用于指定要进行查询的表的名称；
WHERE子句也是查询的一部分，用于设置查询条件。
在采用这种语法时，必须确保查询返回的字段与表里的字段或INSERT语句里指定的字段列表具有相同的次序。
另外，还要确定SELECT语句返回的数据与要插入数据的表的字段具有兼容的数据类型。 - 插入NULL值
插入NULL值的语法如下所示：
INSERT INTO SCHEMA.TABLE_NAME
VALUES('COLUMN1', NULL, 'COLUMN2');
关键字NULL应该位于正确的次序上，相应的字段会没有值的。
三、更新现有的数据
- 更新一列的数据
更新一列的数据的语法：
UPDATE TABLE_NAME
SET COLUMN_NAME = 'VALUE'
[WHERE CONDITION];
检查目标数据集是否正确的一种简单方式是对同一个表使用SELECT语句，
其中包含要在UPDATE语句里使用的WHERE子句，判断返回的结果是否是我们要更新的记录。 - 更新一条或多记录里的多个字段
更新多列的语法如下：
UPDATE TABLE_NAME
SET COLUMN1 = 'VALUE1'
[,COLUMN2 = 'VALUE2']
[,COLUMN3 = 'VALUE3']
[WHERE CONDITION]
注意其中使用的SET,这里只有一个SET，但是有多个列，每个列之间以逗号分隔。
可以看出SQL里的一种趋势：通常使用逗号来分隔不同类型的参数。
四、从表里删除数据
- 从表里删除数据
从表里删除一行或多行的语法：
DELETE FROM TABLE_NAME
[WHERE CONDITION];
删除前首先使用SELECT语句对WHERE子句进行测试。
管理数据库事务
一、什么是事务
- 事务
事务是对数据库执行的一个操作单位。它是以逻辑顺序完成的工作单元或工作序列，
无论是用户手工操作，还是由程序进行的自动操作。
下面是事务的本质特征：
所有的事务都有开始和结束；
事务可以被保存或撤销；
如果事务在中途失败，事务中的任何部分都不会被记录到数据库。
二、控制事务
- 事务控制
事务控制是对关系型数据库管理系统（RDBMS）里可能发生的各种事务的管理能力。
控制事务的命令有3个：
COMMIT；
ROLLBACK；
SAVEPOINT。 - COMMIT命令
语法是：
COMMIT [ WORK ];
事务开启语法是：
START TRANSACTION; - ROLLBACK命令
ROLLBACK命令用于撤销还没有被保存到数据库的命令，它只能用于撤销上一个COMMIT或ROLLBACK命令之后的事务。
ROLLBACK的语法：
ROLLBACK [ WORK ]; - SAVEPOINT命令
保存点是事务过程中的一个逻辑点，我们可以把事务回退到这个点，而不必回退整个事务。
SAVEPOINT的语法：
SAVEPOINT SAVEPOINT_NAME;
Microsoft SQL Server的语法：
SAVE TRANSACTION SAVEPOINT_NAME - ROLLBACK TO SAVEPOINT命令
语法：
ROLLBACK TO SAVEPOINT_NAME;
保存点的名称必须唯一。 - RELEASE SAVEPOINT命令
语法：
RELEASE SAVEPOINT SAVEPOINT_NAME;
Microsoft SQL Server不支持RELEASE SAVEPOINT命令；在事务完成以后，所有的保存点会被自动删除。
这个过程不必使用COMMIT或者ROLLBACK命令。 - SET TRANSACTION命令
语法：
SET TRANSACTION READ WRITE;
SET TRANSACTION READ ONLY;
三、事务控制与数据库性能
数据库查询
一、什么是查询
- 查询是使用SELECT语句对数据库的探究。
二、SELECT语句
- SELECT语句里有4个关键字（或称为子句）是最有价值的：
SELECT
FROM
WHERE
ORDER BY - SELECT 语句
简单的SELECT语句的语法如下所示：
SELECT [ * | ALL | DISTINCT COLUMN1, COLUMN2]
FROM TABLE1 [ , TABLE2 ];
* 表示输出结果里包含表里的全部字段；
ALL 或者 ALL() 用于显示一列的全部值，是默认的；
DISTINCT 或者 DISTINCT() 禁止在结果里包含重复的行；
, 进行字段列表、表列表、数据列表、WHERE子句、ORDER BY子句的分隔。 - FROM 子句
FROM 子句的语法：
FROM TABLE1 [ , TABLE2] - WHERE 子句
WHERE 子句的语法：
SELECT [ * | ALL | DISTINCT COLUMN1, COLUMN2]
FROM TABLE1 [ , TABLE2 ]
WHERE [ CONDITION1 | EXPRESSION1 ]
[ AND | OR CONDITION2 | EXPRESSION2 ] - ORDER BY 子句
ORDER BY 子句的语法是：
SELECT [ * | ALL | DISTINCT COLUMN1, COLUMN2]
FROM TABLE1 [ , TABLE2 ]
WHERE [ CONDITION1 | EXPRESSION1 ]
[ AND | OR CONDITION2 | EXPRESSION2 ]
ORDER BY COLUNN1 | INTEGER [ ASC | DESC ]
INTEGER 表示字段在关键字 SELECT 之后列表里的位置
默认ASC - 大小写敏感性
SQL命令和关键字：
不区分
数据：
MySQL和Microsoft SQL Server不区分
Oracle区分
三、简单查询的范例
- 统计表里的记录数量
COUNT函数的语法：
SELECT COUNT(* | COLUMNNAME)
FROM TABLE_NAME;
COUNT(COLUMNNAME),NOT NULL才会统计进去。
如果要统计表中特定列所出现的值的种类数，需要在COUNT函数中使用DISTINCT关键字：
COUNT(DISTINCT(COLUMNNAME)); - 从另一个用户表里选择数据
必须在表的名称之前添加规划名或相应的用户名：
SELECT *
FROM SCHEMA.TABLE_NAME; - 使用字段别名
语法：
SELECT COLUMN_NAME ALIAS_NAME
FROM TABLE_NAME
使用操作符对数据进行分类
一、什么是SQL里的操作符
- 操作符
操作符是一个保留字或者字符，主要用于SQL语句的WHERE子句来执行操作。
操作符包括：
比较操作符
逻辑操作符
求反操作符
算术操作符
二、比较操作符
- 比较操作符用于在SQL语句里对单个值进行测试：
=、<>、<和> - 相等
= - 不相等
<>
MySQL、Microsoft SQL Server、Oracle还可使用 !=
Oracle还可使用 ^= - 小于或大于
<、> - 比较操作符的结合
<=、>=
三、逻辑操作符
- 逻辑操作符
逻辑操作符用于对SQL关键字而不是符号进行比较。
IS NULL;
BETWEEN;
IN;
LIKE;
EXISTS;
UNIQUE;
ALL和ANY. - IS NULL
这个操作符用于与NULL值进行比较。 - BETWEEN
操作符BETWEEN用于寻找位于一个给定最大值和最小值之间的值，这个最大值和最小值是包含在内的。 - IN
操作符IN用于把一个值与一个指定列表进行比较，当被比较的值至少与列表中的一个值相匹配时，它会返回TRUE。
使用操作符IN可以得到与操作符OR一样的结果，但它的速度更快。 - LIKE
操作符LIKE利用通配符把一个值与类似的值进行比较。通配符有两个：
%;
_.
百分号代表零个、一个或多个字符，下划线代表一个数字或字符。这些字符可以复合使用。 - EXISTS
这个操作符用于搜索指定表里是否存在满足特定条件的记录。 - ALL、SOME、ANY
操作符ALL用于把一个值与另一个集合里的全部值进行比较。
操作符ANY用于把一个值与另一个列表里任意值进行比较。SOME是ANY的别名，可以互换。
四、连接操作符
- 连接运算符
如果想在SQL语句里利用多个条件来缩小数据范围该怎么办呢？
AND
OR - AND
在使用AND时，无论SQL语句是事务还是查询，所有由AND连接的条件都必须为TRUE，SQL语句才会实际执行。 - OR
无论SQL语句是事务还是查询，只要OR连接的条件里有至少一个是TRUE，SQL语句就会执行。
五、求反操作符
- 求反操作符
<>、!=;
NOT BETWEEN;
NOT IN;
NOT LIKE;
IS NOT NULL;
NOT EXISTS;
NOT UNIQUE.
六、算术操作符
- 算术操作符
算术操作符用于在SQL语句里执行算术功能。
+;
-;
*;
/.
汇总查询得到的数据
一、什么时候汇总数据
- 函数是SQL里的关键字，用于对字段里的数据进行操作。
汇总函数有：
COUNT;
SUM;
MAX;
MIN;
AVG. - COUNT函数
COUNT函数用于统计不包含NULL值的记录或字段的值，在用于查询之中时，它返回一个数值。
COUNT函数的语法：
COUNT [ (*) | (DISTINCT | ALL) ] (COUNT NAME)
COUNT(*)会统计表里的全部记录数量，包括重复的，也包含NULL值。
DISTINCT命令不能与COUNT(*)一起使用，只能用于COUNT(column_name)。 - SUM函数
SUM函数返回一组记录中某一个字段值的总和。
SUM函数的语法：
SUM ([ DISTINCT ] COLUMN NAME)
SUM函数只能处理数值型字段。
数据可以隐含的转换为数值类型的也能处理。 - AVG函数
AVG函数可以计算一组指定记录的平均值。
AVG函数的语法：
AVG ([ DISTINCT ] COLUMN NAME)
AVG函数只能处理数值型的字段。 - MAX函数
MAX函数返回一组记录中某个字段的最大值。
MAX函数的语法：
MAX([ DISTINCT ] COLUMN_NAME) - MIN函数
MIN函数返回一组记录里某个字段的最小值。
MAX函数的语法：
MIN([ DISTINCT ] COLUMN_NAME) - 总结
汇总函数不计算NULL值。
汇总函数与DISTINCT命令通常不一起使用，因为没意义。
但有些情况也行，比如和COUNT、AVG一起使用有时候有意义。
数据排序与分组
一、为什么要对数据进行分组
- 数据分组是按照逻辑次序把具有重复值的字段进行合并。
二、GROUP BY 子句
- GROUY BY子句在查询中的位置：
SELECT
FROM
WHERE
GROUP BY
ORDER BY - 包含了GROUP BY子句的SELECT语句的语法：
SELECT COLUMN1, COLUMN2
FROM TABLE1, TABLE2
WHERE CONDITIONS
GROUP BY COLUMN1, COLUMN2
ORDER BY COLUMN1, COLUMN2 - 分组函数
典型的分组函数--也就是用于GROUP BY子句对数据进行划分的函数--包括AVG、MAX、MIN、SUM和COUNT。 - 对选中的数据进行分组
数据分组是个简单的过程。被选中的字段（查询中SELECT之后的字段列表）才能在GROUP BY子句里引用；
如果字段在SELECT语句里找不到，就不能用于GROUP BY子句。 - 创建分组和使用汇总函数
GROUP BY子句里的字段不必与SELECT子句里的字段具有相同的次序。 - 以整数代替字段名称
像ORDER BY子句一样，GROUP BY子句里也可以使用整数代表字段名称。
三、GROUP BY 与 ORDER BY
- GROUP BY 与 ORDER BY 的相同之处
GROUP BY 与 ORDER BY的相同之处在于它们都是对数据进行排序。
ORDER BY 子句专门用于对查询得到的数据进行排序，
GROUP BY子句也把查询得到的数据排序为适当分组的数据，
因此，GROUP BY子句也可以像ORDER BY子句那样用于数据排序。
用GROUP BY子句实现排序操作的区别与缺点是：
所有被选中的，非汇总函数的字段必须列在GROUP BY子句里；
除非需要使用汇总函数，否则使用GROUP BY子句进行排序通常是没有必要的。
四、CUBE和ROLLUP语句
- ROLLUP语句可以用来进行小计，即在全部分组数据的基础上，对其中一部分进行汇总。
其ANSI语法结构如下（Microsoft SQL Server和Oracle使用）：
GROUP BY ROLLUP(ordered column list of grouping sets)
MySQL的语法结构稍有不同：
GROUP BY ordered column list of grouping sets WITH ROLLUP
ROLLUP语句的工作方式是这样的，
在完成了基本的分组数据汇总以后，
按照从右向左的顺序，每次去掉字段列表中的最后一个字段，再对剩余的字段进行分组统计，
并将获得的小计结果插入返回表中，被去掉的字段位置使用NULL填充。
最后，再对全表进行一次统计，所有字段位置均使用NULL填充。 - CUBE语句
其ANSI语法结构如下（Microsoft SQL Server和Oracle使用）：
GROUP BY CUBE(ordered column list of grouping sets)
在MySQL5.6.17版本中，只定义了CUBE，但是不支持CUBE操作：
GROUP BY ordered column list of grouping sets WITH CUBE
CUBE语句的工作方式：
它对分组列表中的所有字段进行排列组合，并根据每一种组合结果，分别进行统计汇总。
最后，CUBE语句也会对全表进行统计。
五、HAVING子句
- HAVING子句
HAVING子句在SELECT语句里与GROUP BY子句联合使用时，用于告诉GROUP BY子句在输出里包含哪些分组。
HAVING对于GROUP BY的作用相当于WHERE对于SELECT的作用。 - HAVING子句在查询里的位置：
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY - 包含HAVING子句的语法：
SELECT COLUMN1, COLUMN2
FROM TABLE1, TABLE2
WHERE CONDITIONS
GROUP BY COLUMN1, COLUMN2
HAVING CONDITIONS
ORDER BY COLUMN1, COLUMN2
调整数据的外观
一、ANSI字符函数
- ANSI字符函数
字符函数用于在SQL里以不同于存储方式的格式来表示字符串。
串接就是把两个单独的字符串组合为一个。
子串的概念就是从字符串里提取一部分。
TRANSLATE函数用于逐字符地把一个字符串变换为另一个，
它通常有3个参数：要被转换的字符串、要转换的字符系列、代入字符的列表。
二、常用字符函数
- 串接函数
MySQL里的语法：CONCAT(column_name, [' ',] column_name [column_name])
ORACLE里的语法：column_name || [' ' ||] column_name [column_name]
SQL Server里的语法：column_name + [' ' +] column_name [column_name]
MySQL和Oracle中都有串接函数，区别在于，Oracle中的串接函数只能用于两个字符串，
而MySQL中的串接函数可以连接多个字符串。遗憾的是，Microsoft SQL Server不支持串接函数。 - TRANSLATE函数
TRANSLATE函数搜索字符串里的字符并查找特定的字符，标记找到的位置，然后用替代字符串里对应的字符替换它。
语法：TRANSLATE(CHARACTER set, value1, value2)
注：MySQL和Oracle都支持使用TRANSLATE函数，但是SQL Server不支持。 - REPLACE函数
REPLACE函数用于把某个字符或字符串替换为指定的一个字符(或多个字符)，其使用类似于TRANSLATE函数，
只是它是把一个字符或字符串替换到另一个字符串里。
语法：REPLACE('value','value',[null] 'value')
注：MySQL、Oracle、SQL Server全都支持该函数的ANSI语法结构。 - UPPER
语法：UPPER(CHARACTER string)
注：MySQL、Oracle、SQL Server全都支持该函数，MySQL还有一个UCASE函数可以实现同样的操作。 - LOWER
语法：LOWER(CHARACTER string)
注：MySQL、Oracle、SQL Server全都支持该函数，与UPPER类似，MySQL还有一个LCASE函数可以实现同样的操作。 - SUBSTR
MySQL(5.5.33)里的语法：
SUBSTR(column_name starting position, length)
SUBSTRING(column_name, starting position, length)
Oracle里的语法：SUBSTR(column_name starting position, length)
SQL Server里的语法：SUBSTRING(column_name, starting position, length) - INSTR
INSTR函数用于在字符串里寻找指定的字符集，返回其所在的位置。
语法：INSTR(column_name, 'set', [start position [,occurrence]]
注：INSTR在MySQL和Oracle中有效，在SQL Server中，则需要使用CHARINDEX函数。 - LTRIM
LTRIM用于从左剪除字符串里的字符。
语法：LTRIM(CHARACTER STRING [,'set')
注：MySQL、Oracle、SQL Sserver全都支持该函数。 - RTRIM
类似于LTRIM，RTRIM也用于剪除字符，但它是剪除字符串的右侧。
语法：RTRIM(CHARACTER STRING [,'set'])
注：MySQL、Oracle、SQL Sserver全都支持该函数。 - DECODE
主要用于Oracle、PostgreSQL，它可以在字符串里搜索一个值或字符串，如果找到了，就在结果里显示另一个字符串。
语法：DECODE(column_name, 'search1', 'return1', ['search2', 'return2', 'default value'])
三、其他字符函数
- LENGTH
LENGTH函数用于得到字符串、数字、日期或者表达式的长度，单位是字节。
语法：LENGTH(CHARACTER STRING)
注：MySQL、Oracle都支持该函数，而SQL Server则使用LEN函数来实现相同的功能。 - IFNULL
IFNULL函数用于在一个表达式是NULL时从另一个表达式获得值。
它可以用于大多数数据类型，但值与替代值必须是同一数据类型。
语法：IFNULL(‘value’, ‘subtstitution’)
注：只有MySQL支持该函数，要实现相同的功能，Oracle使用COALESCE函数，SQL Server则使用ISNULL函数。 - COALESCE
COALESCE函数也用于指定值替代NULL值，这一点与IFNULL是一样的。其不同点在于，它可以接受一个数据集，
依次检查其中每一个值，直到发现一个非 NULL值。如果没有找到非NULL值，它会返回一个NULL值。
注：MySQL、Oracle、SQL Server全都支持该函数。 - LPAD
LPAD(左填充)用于在字符串左侧添加字符或空格。
语法：LPAD(CHARACTER set)
注：MySQL、Oracle全都支持该函数，SQL Server中没有对应的函数。 - RPAD
RPAD(右填充)用于在字符串右侧添加字符或空格。
语法：RPAD(CHARACTER set)
注：MySQL、Oracle全都支持该函数，SQL Server中没有对应的函数。 - ASCII
ASCII函数返回字符串最左侧字符的”美国信息交换标准码(ASCII)”。
语法：ASCII(CHARACTER set)
注：MySQL、Oracle、SQL Server都支持该函数。
四、算术函数
- 最常见的算术函数包括：
绝对值(ABS)
舍入(ROUND)
平方根(SQRT)
符号(SIGN)
幂(POWER)
上限和下限(CEIL、FLOOR)
指数(EXP)
SIN、COS、TAN
大多数算术函数的语法：FUNCTION(expression)
五、转换函数
- 常见的数据转换：
字符到数字；
数字到字符；
字符到日期；
日期到字符。 - 字符串转换为数字
数值数据类型与字符串数据类型有两个主要的区别：
算术表达式和函数可以使用于数值；
在输出结果里，数值是右对齐的，而字符串是左对齐的。
Oracle：TO_NUMBER() - 数字转换为字符串
Oracle：TO_CHAR()
SQL Server：STR()
六、字符函数的使用
- 字符函数的组合使用
注：当SQL语句的函数内部嵌有函数时，最内层的函数首先被处理，然后从里向外依次执行各个函数。
日期和时间
一、日期是如何存储的
- 日期和时间的标准数据类型
日期和时间（DATETIME）存储的标准SQL数据类型有3种：
DATE：YYYY-MM-DD 0001-01-01到9999-12-31
TIME：HH:MI:SS.nn... 00:00:00...到23:59:61.999...
TIMESTAMP：YYYY-MM-DD HH:MI:SS.nn... 0001-01-01 00:00:00...到9999-12-31 23:59:61.999... - DATETIME元素
DATETIME元素及其取值范围：
YEAR 0001到9999
MONTH 01到12
DAY 01到31
HOUR 00到23
MINUTE 00到59
SECOND 00.000...到61.999... - 不同实现的数据类型
不同平台的日期和时间
MySQL
DATETIME 存储日期和时间信息
TIMESTAME 存储日期和时间信息
DATE 存储日期值
TIME 存储时间值
YEAR 单字节，表示年
Oracle
DATE 存储日期和时间信息
SQL Server
DATETIME 存储日期和时间信息
SMALLDATETIME 存储日期和时间信息，但取值范围小于DATETIME
DATE 存储日期值
TIME 存储时间值
二、日期函数
- 日期函数
日期函数用于调整日期和时间数据的外观 - 当前日期
当前日期保存在数据库所在的计算机上时，被称为系统时间。
MySQL：NOW()
Oracle：SYSDATE
SQL Server：GETDATE() - 时区
- 时间和日期相加
不同平台的时间和日期相加
MySQL
DATE_ADD+INTERVAL DATE_ADD(DATE_HIRE, INTERVAL 1 DAY)
ORACLE
ADD_MONTHS | + ADD_MONTHS(DATE_HIRE, 1)、DATE_HIRE + 1
SQL Server
DATEADD DATEADD(MONTH, 1, DATE_HIRE) - 其他日期函数
不同平台的日期函数
MySQL
DAYNAME(date) 显示星期几
DAYOFMONTH(date) 显示几日
DAYOFWEEK(date) 显示星期几
DAYOFYEAR(date) 显示一年中的第几天
Oracle
NEXT_DAT 返回指定日期之后的下一天（比如FRIDAY）
MONTHS_BETWEEN 返回两个日期之间相差的月数
SQL Server
DATEPART 返回日期的某个元素的整数值
DATENAME 返回日期的某个元素的文本值
GETDATE() 返回系统时间
DATEDIFF 返回两个日期之间由指定日期元素表示的间隔，比如天数、分钟数和秒数
三、日期转换
- 日期转换
进行日期转换的典型原因有：
比较不同数据类型的日期值；
把日期值格式化为字符串；
把字符串转化为日期格式。
ANSI的CAST操作符可以把一种数据类型转换为另一种，其基本语法如下：
CAST(EXPRESSION AS NEW_DATA_TYPE) - 日期描述
日期描述由格式元素组成，用于从数据库以期望的格式提取日期和时间信息。
常见的日期元素
MySQL
SECOND 秒
MINUTE 分钟
HOUR 小时
DAY 天
MONTH 月
YEAR 年
MINUTE_SECOND 分和秒
HOUR_MINUTE 小时和分
DAY_HOUR 天和小时
YEAR_MONTH 年和月
HOUR_SECOND 小时、分和秒
DAY_MINUTE 天和分钟
DAY_SECOND 天和秒
Oracle
AD 公元
AM 正午以前
BC 公元前
CC 世纪
D 星期中的第几天
DD 月份中的第几天
DDD 年中的第几天
DAY 拼写出来的周日（比如MONDAY）
Day 拼写出来的周日（比如Monday）
day 拼写出来的周日（比如monday）
DY 周日的三字母缩写（比如MON）
Dy 周日的三字母缩写（比如Mon）
dy 周日的三字母缩写（比如mon）
HH 小时
HH12 小时
HH24 小时
J 自公元前4713年12月31日起至今的日子
MI 分钟数
MM 月份
MON 月份的三字母缩写（比如JAN）
Mon 月份的三字母缩写（比如Jan）
mon 月份的三字母缩写（比如jan）
MONTH 月份的拼写（比如JANUARY）
Month 月份的拼写（比如January）
month 月份的拼写（比如january）
PM 正午之后
Q 季度数
RM 以罗马数字表示的月份
RR 两位数字表示的月份
SS 妙数
SSSSS 自午夜起累计的秒数
SYYYY 以符号数表示的年份，比如公元前500年就表示为-500
W 月里的第几星期
WW 年里的第几星期
Y 年份的最后一位数字
YY 年份的最后两位数字
YYY 年份的最后三位数字
YYYY 年份
YEAR 拼写出来的年份（TWO-THOUSAND-TEN）
Year 拼写出来的年份（Two-Thousand-Ten）
year 拼写出来的年份（tow-thousand-ten）
SQL Server
yy 年
qq 季度
mm 月
dy 积日（从历年的第一天累计的天数）
wk 星期
dw 周日
hh 小时
mi 分钟
ss 秒
ms 毫秒 - 日期转换为字符串
Oracle：
TO_CHAR：T0_CHAR(DATE_HIRE, 'Month dd, yyyy')
SQL Server：
DATENAME：DATE_HIRE = DATENAME(MONTH, DATE_HIRE) - 字符串转换为日期
MySQL:
STR_TO_DATE('01/01/2010 12:00:00 AM', '%m%d%Y %h:%i:%s %p')
SQL Server：
CONVERT：CONVERT(DATETIME, '02/25/2010 12:00:00 AM')
在查询里结合表
一、从多个表获取数据
- 从多个表获取数据
能够从多个表选择数据是SQL最强大的特性之一。如果没有这种能力，关系型数据库的整个概念就无法实现了。
二、结合的类型
- 结合的类型
结合是把两个或多个表结合在一起来获取数据。
最常用的结合方式有：
等值结合或内部结合；
非等值结合
外部结合
自结合 - 结合条件的位置
要结合的表列在FROM子句里，而结合是在WHERE子句里完成的。 - 等值结合
等值结合利用通用字段结合两个表，而这个字段通常是每个表里的主键。
等值结合的语法：
SELECT TABLE1.COLUMN1, TABLE2.COLUMN2...
FROM TABLE1, TABLE2 [, TABLE3 ]
WHERE TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME
[ AND TABLE1.COLUMN_NAME = TABLE3.COLUMN_NAME ]
注意：在SQL语句中推荐使用缩排，但不是必须的。
SELECT子句里每个字段名称都以表名作为前缀，从而准确标识各个字段。
在查询中这被称为限定字段，它只有在字段存在于多个表时才有必要。
SQL里可以利用INNER JOIN语法来提高可读性：
SELECT TABLE1.COLUMN1, TABLE2.COLUMN2...
FROM TABLE1 INNER JOIN TABLE2
ON TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME - 使用表的别名
使用表的别名意味着在SQL语句里对表进行重命名，
这是一种临时性的改变，表在数据库里的实际名称不会受到影响。
给表起别名同时也意味着选择字段必须用表的别名加以修饰。
WHERE子句里使用的字段也必须用表的别名加以修饰。 - 不等值结合
不等值结合根据同一个字段在两个表里值不相等来实现结合。其语法：
FROM TABLE1, TABLE2 [, TABLE3 ]
WHERE TABLE1.COLUMN_NAME != TABLE2.COLUMN_NAME
[ AND TABLE1.COLUMN_NAME != TALBE2.COLUMN_NAME ] - 外部结合
外部结合会返回一个表里的全部记录，即使对应的记录在第二个表里不存在。
外部结合的一般语法：
FROM TABLE1
{RIGHT | LEFT | FULL} [OUTER] JOIN TABLE2
ON CONDITION
Oracle的语法：
FROM TABLE1, TABLE2 [, TABLE3 ]
WHERE TABLE1.COLUMN_NAME[(+)] = TABLE2.COLUMN_NAME[(+)]
[ AND TABLE1.COLUMN_NAME[(+)] = TABLE3.COLUMN_NAME[(+)] ] - 自结合
自结合利用表别名在SQL语句对表进行重命名，像处理两个表一样把表结合到自身。语法：
SELECT A.COLUMN_NAME, B.COLUMN_NAME, [ C.COLUMN_NAME ]
FROM TABLE1 A, TABLE1 B [, TABLE3 C ]
WHERE A.COLUMN_NAME = B.COLUMN_NAME
[ AND A.COLUMN_NAME = C.COLUMN_NAME ]
利用INNER JOIN语法：
SELECT A.COLUMN_NAME, B.COLUMN_NAME
FROM TABLE1 A INNER JOIN TABLE1 B
ON A.COLUMN_NAME = B.COLUMN_NAME - 结合多个主键
语法：
SELECT A.COLUMN_NAME, B.COLUMN_NAME
FROM TABLE1 A, TABLE2 B
WHERE A.COLUMN_NAME1 = B.COLUMN_NAME1
AND A.COLUMN_NAME2 = B.COLUMN_NAME2
类似的，INNER JOIN的语法：
SELECT A.COLUMN_NAME, B.COLUMN_NAME
FROM TABLE1 A INNER JOIN TABLE2 B
ON A.COLUMN_NAME1 = B.COLUMN_NAME1
AND A.COLUMN_NAME2 = B.COLUMN_NAME2
三、需要考虑的事项
- 使用基表
如果需要从两个表里获取数据，但它们又没有公用字段，我们就必须结合另一个表，
这个表与前两个表都有公用字段，这个表就被称为基表。 - 笛卡尔积
笛卡尔积通常也被称为交叉结合
语法：
FROM TABLE1, TABLE2 [, TABLE3 ]
WHERE CONDITIONS
使用子查询定义未确定数据
一、什么是子查询
- 子查询
子查询也被称为嵌套查询，是位于另一个查询的WHERE子句里的查询，
它返回的数据通常在主查询里作为一个条件，从而进一步限制数据库返回的数据。
子查询必须遵循以下规则
子查询必须位于圆括号内。
除非主查询里有多个字段让子查询进行比较，否则子查询的SELECT子句里只能有一个字段。
子查询里不能使用ORDER BY子句。在子查询里，我们可以利用GROUP BY子句实现ORDER BY功能。
返回多条记录的子查询只能与多值操作符（比如IN）配合使用。
SELECT列表里不能引用任何BLOB、ARRAY、CLOB或NCLOB类型的值。
子查询不能直接被包围在函数里。
操作符BETWEEN不能用于子查询，但子查询内部可以使用它。
子查询的基本语法：
SELECT COLUMN_NAME
FROM TABLE
WHERE COLUMN_NAME =
(SELECT COLUMN_NAME
FROM TABLE
WHERE CONDITIONS); - 子查询与SELECT语句
基本语法：
SELECT COLUMN_NAME [, COLUMN_NAME ]
FROM TABLE1 [, TABLE2 ]
WHERE COLUMN_NAME OPERATOR
(SELECT COLUMN_NAME [, COLUMN_NAME ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]) - 子查询与INSERT语句
基本语法：
INSERT INTO TABLE_NAME [ (COLUMN1 [, COLUMN2 ]) ]
SELECT [ * | COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE VALUE OPERATOR ] - 子查询与UPDATE语句
基本语法：
UPDATE TABLE
SET COLUMN_NAME [, COLUMN_NAME) ] =
(SELECT COLUMN_NAME [, COLUMN_NAME) ]
FROM TABLE
[ WHERE ] - 子查询与DELETE语句
基本语法：
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME
[ WHERE ]) ]
二、嵌套的子查询
- 嵌套的子查询
基本语法：
SELECT COLUMN_ANME [, COLUMN_NAME ]
FROM TABLE1 [, TABLE2 ]
WHERE COLUMN_NAME OPERATOR
(SELECT COLUMN_NAME
FROM TABLE
WHERE COLUMN_NAME OPERATOR
(SELECT COLUMN_NAME
FROM TABLE
[ WHERE COLUMN_NAME OPERATOR VALUE ]))
三、关联子查询
- 关联子查询
关联子查询是依赖主查询里的信息的子查询。
这意味着子查询里的表可以与主查询里的表相关联。
例子1：
SELECT C.CUST_NAME
FROM CUSTOMER_TBL C
WHERE 10 <
(SELECT SUM(O.QTY)
FROM ORDERS_TBL O
WHERE O.CUST_ID = C.CUST_ID);
例子2（等效于例子1）：
SELECT C.CUST_NAME, SUM(O.QTY)
FROM CUSTOMER_TBL C,
ORDERS_TBL O
WHERE C.CUST_ID = O.CUST_ID
GROUP BY C.CUST_NAME;
四、子查询的效率
- 子查询的效率
由于子查询会在主查询之前进行，所以子查询所花费的时间，会直接影响整个查询所需要的时间。
组合多个查询
一、单查询与组合查询
- 单查询与组合查询
单查询是一个SELECT语句，而组合查询具有两个或多个SELECT语句。
二、组合查询操作符
- ANSI标准组合查询操作符
UNION、UNION ALL、EXCEPT、INTERSECT - UNION
UNION操作符可以组合两个或多个SELECT语句的结果，不包含重复的记录。
在使用UNION操作符时，每个SELECT语句里必须选择同样数量的字段、
同样数量的字段表达式、同样的数据类型、同样的次序--但长度不必一样。
基本语法：
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
UNION
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ] - UNION ALL
UNION ALL操作符可以组合两个SELECT语句的结果，并且包含重复的结果。
基本语法：
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
UNION ALL
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ] - INTERSECT
INTERSECT可以组合两个SELECT语句，但只返回第一个SELECT语句里与第二个SELECT语句里一样的记录。
目前MySQL尚不支持INTERSECT，但SQL SERVER和Oracle全都提供支持。
基本语法：
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
INTERSECT
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ] - EXCEPT
EXCEPT操作符组合两个SELECT语句，返回第一个SELECT语句里有但第二个SELECT语句里没有的记录。
目前MySQL尚不支持EXCEPT，但SQL SERVER和Oracle全都提供支持。
基本语法：
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
EXCEPT
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
三、组合查询里使用ORDER BY
- 组合查询里使用ORDER BY
ORDER BY子句可以用于组合查询，但它只能用于对全部查询结果的排序，因此组合查询里
虽然可能包含多个查询或SELECT语句，但只能有一个ORDER BY子句。
基本语法：
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
OPERATOR{UNION | EXCEPT | INTERSECT | UNION ALL}
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
[ ORDER BY ] - 组合查询里使用GROUP BY
与ORDER BY不同的是，GROUP BY子句可以用于组合查询中的每一个SELECT语句，也可以用于全部查询结果。
另外，HAVING子句也可以用于组合查询里的每个SELECT语句。
基本语法：
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
[ GROUP BY ]
[ HAVING ]
OPERATOR{UNION | EXCEPT | INTERSECT | UNION ALL}
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
[ GROUP BY ]
[ HAVING ]
[ ORDER BY ]
利用索引改善性能
一、什么是索引
- 什么是索引
简单来说，索引就是一个指针，指向表里的数据。
索引也会占据物理存储空间，而且可能会比表本身还大。
因此在考虑数据库的存储空间时，需要考虑索引要占用的空间。
二、索引是如何工作的
- 索引是如何工作的
索引在创建之后，用于记录与被索引字段相关联的位置值。当表里添加新数据时，
索引里也会添加新项。当数据库执行查询，而且WHERE条件里指定的字段已经设置了索引时，
数据库会首先在索引里搜索WHERE子句里指定的值。如果在索引里找到了这个值，
索引就可以返回被搜索数据在表里的实际位置。
索引通常以一种树形结构保存信息，因此速度比较快。
三、CREATE INDEX 命令
- CREATE INDEX 命令
大多数实现使用CREATE INDEX语句：
CREATE INDEX INDEX_NAME ON TABLE_NAME
四、索引的类型
- 索引的类型
数据库里的表可以创建多种类型的索引。通过提高数据检索速度来改善数据库性能。
单字段索引、组合索引、唯一索引 - 单字段索引
单字段索引是基于一个字段创建的。
基本语法：
CREATE INDEX INDEX_NAME
ON TABLE_NAME (COLUMN_NAME) - 唯一索引
唯一索引用于改善性能和保证数据完整性。唯一索引不允许表里具有重复的值。
基本语法：
CREATE UNIQUE INDEX INDEX_NAME
ON TABLE_NAME (COLUMN_NAME) - 组合索引
组合索引是基于一个表里两个或多个字段的索引。
一般来说，最具有限制的值应该排在前面，从而得到最好的性能。
但是总是会在查询里指定的字段应该放在首位。
基本语法：
CREATE INDEX INDEX_NAME
ON TABLE_NAME (COLUMN_NAME1, COLUMN_NAME2) - 隐含索引
隐含索引是数据库服务程序在创建对象时自动创建的。
比如数据库会为主键约束和唯一性约束自动创建索引。
五、何时考虑使用索引
- 何时考虑使用索引
一般来说，大多数用于表结合的字段都应该设置索引。
经常在ORDER BY和GROUP BY里引用的字段也应该考虑使用索引。
另外，具有大量唯一值的字段，或是在WHERE子句里会返回很小一部分记录的字段，都可以考虑设置索引。
六、何时应该避免使用索引
- 何时应该避免使用索引
索引不应该用于小规模的表
当字段用于WHERE子句作为过滤器会返回表里的大部分记录时，该字段就不适合作为索引。
经常会被批量更新的表可以具有索引。
可以在执行批量操作之前去除索引，在完成操作之后再重新创建索引。
不应该对包含大量NULL值的字段设置索引。
经常被操作的字段不应该设置索引。
七、修改索引
- 修改索引
创建索引后，也可以对其进行修改。能够修改的内容在不同的数据库实现中有所不同。
但基本语法修改都是字段、顺序等内容。
基本语法：
ALTER INDEX INDEX_NAME
八、删除索引
- 删除索引
大多数实现使用DROP命令。基本语法：
DROP INDEX INDEX_NAME
MySQL中的语法结构稍有不同，需要同时指定创建索引的表格：
DROP INDEX INDEX_NAME ON TABLE_NAME
改善数据库性能
一、什么是SQL语句调整
- SQL语句调整：
SQL语句调整是优化生成SQL语句的过程，从而以最有效和最高效的方式获得结果。
SQL语句调整主要涉及调整语句的FROM和WHERE子句，因为数据库服务程序主要根据这两个子句执行查询。
二、数据库调整与SQL语句调整
- 数据库调整
数据库调整是调整实际数据库的过程，包括分配内存、磁盘、CPU、I/O和底层数据库进程，
还涉及数据库结构本身的管理和操作。比如表和索引的设计与布局。 - SQL调整
SQL调整是调整访问数据库的SQL语句，这些语句包括数据库查询和事务操作，比如插入、更新和删除。
三、格式化SQL语句
- 格式化SQL语句
为提高可读性格式化SQL语句
FROM子句里表的顺序
最严格条件在WHERE子句里的位置
结合条件在WHERE子句里的位置 - 为提高可读性格式化SQL语句
基本规则
每个子句都以新行开始。
当子句里的参数超过一行长度需要换行时，利用制表符或空格来形成缩进。
以一致的方式使用制表符和空格。
当语句里使用多个表时，使用表的别名。
有节制的使用注释。
如果在SELECT语句里要使用多个字段，就让每个字段都从新行开始。
如果在FROM子句里要使用多个表，就让每个表名都从新行开始。
让WHERE子句里每个条件都以新行开始。 - FROM子句里的表
把较小的表列在前面，把较大的表列在后面，就会获得更好的性能。
范例：
FROM SMALLEST TABLE,
LARGEST TABLE - 结合条件的次序
在WHERE子句里，来自基表的字段一般放在结合操作的右侧，要被结合的表通常按照从小到大的次序排序。
结合条件应该位于WHERE子句里的最前面，其后才是过滤条件。如下：
FROM TABLE1, Smallest table
TABLE2, to
TABLE3 Largest table, also base table
WHERE TABLE1.COLUMN = TABLE3.COLUMN Join condition
AND TABLE2.COLUMN = TABLE3.COLUMN Join condition
[ AND CONDITION1 ] Filter condition
[ AND CONDITION2 ] Filter condition - 最严格条件
最严格条件通常是SQL查询达到最优性能的关键因素。什么是最严格条件？它是WHERE子句里返回最少记录的条件。
与之相反，最宽松条件就是语句里返回最多记录的条件。
从实践总结出来的经验表明，最好使用具有索引的字段作为查询里的最严格条件。索引通常会改善查询的性能。
四、全表扫描
- 全表扫描
应该被索引的数据：
作为主键的字段；
作为外键的字段；
在结合表里经常使用的字段；
经常在查询里作为条件的字段；
大部分值是唯一的字段。
五、其他性能考虑
- 其他性能考虑
使用LIKE操作符合通配符；
避免OR操作符；
避免HAVING子句；
避免大规模排序操作；
使用存储过程；
在批加载时关闭索引。 - 使用LIKE操作符和通配符
索引看左不看右 - 避免使用OR操作符
用IN代替OR后，检索数据的速度明显提高。 - 避免使用HAVING子句
HAVING子句的使用增加了额外的工作。
尽量不要在SQL语句中使用HAVING子句，如果需要使用，则最好尽可能地使用其中的限制条件简单化。 - 避免大规模排序操作
ORDER BY、GROUP BY和HAVING子句的主要问题是会影响SQL语句的响应时间。
最好把大规模排序在批处理过程里，在数据库使用的非繁忙期运行，从而避免影响大多数用户进程的性能。 - 使用存储过程
我们可以为经常运行的SQL语句（特别是大型事务或查询）创建存储过程。 - 在批加载时关闭索引
最好在加载过程中关闭相应表的索引。当相应的索引被删除之后，对表所做的修改会在更短的时间内完成，
整个操作也会更快的完成。当批加载结果之后，我们可以重建索引。
六、基于成本的优化
- 基于成本的优化
总计资源消耗 = 衡量方法 * 执行次数
七、性能工具
管理数据库用户
一、数据库的用户管理
- 用户的类型
数据输入员；
程序员；
系统工程师；
数据库管理员；
系统分析员；
开发人员；
测试人员；
管理者；
终端用户。 - 谁管理用户
数据库管理员通常负责创建数据库用户账户、角色、权限和特征，以及相应的删除操作。 - 用户在数据库里的位置
全部用户在数据库里都有位置，有些具有更多的责任和与众不同的职责。
数据库用户就像是我们身体的各个部分，以一个整体共同作用来达到某些目的。 - 不同规划里的用户
这个用户被称为规划所有人。
二、管理过程
- 创建用户
1、在Oracle里创建用户
创建用户的步骤：
1）使用默认设置创建数据库用户账户。
2）给用户账户授予适当的权限。
创建用户的语法：
CREATE USER USER_ID
INDENTIFIED BY [PASSWORD | EXTERNALLY ]
[ DEFAULT TABLESPACE TABLESPACE_NAME ]
[ TEMPORARY TABLESPACE TABLESPACE_NAME ]
[ QUOTA (INTEGER (K | M) | UNLIMITED) ON TABLESPACE_NAME ]
[ PROFILE PROFILE_TYPE ]
[PASSWORD EXPIRE |ACCOUNT [LOCK | UNLOCK]
给用户账户授予权限的语法：
GRANT PRIV1 [ , PRIV2, ... ] TO USERNAME | ROLE [, USERNAME ]
2、在Microsoft SQL Server里创建用户
创建用户账户的步骤：
1）为SQL Server创建登录账户，指定密码和默认的数据库。
2）把用户添加到适当的数据库，从而创建一个数据库账户。
3）给数据库账户分配适当的权限。
创建用户账户的语法：
SP_ADDLOGIN USER_ID, PASSWORD [, DEFAULT_DATABASE ]
把用户添加到数据库的语法：
SP_ADDUSER USER_ID [, NAME_IN_DB [, GRPNAME ] ]
给用户账户分配权限的语法：
GRANT PRIV1 [, PRIV2, ... ] TO USER_ID
3、在MySQL里创建用户
创建用户账户的步骤：
1）在数据库里创建用户账户。
2）给用户账户分配适当的权限。
创建用户账户的语法：
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
分配用户权限的语法：
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type]
{tbl_name | * | *.* | db_name.* | db_name.rotine_name}
TO user - 创建规划
语法：
CREATE SCHEMA [ SCHEMA_NAME ] [ USER_ID ]
[ DEFAULT CHARACTER SET CHARACTER_SET ]
[PATH SCHEMA NAME [, SCHEMA NAME] ]
[ SCHEMA_ELEMENT_LIST ] - 删除规划
使用DROP SCHEMA语句可以从数据库里删除规划，这时必须要考虑两个选项。
一个是RESTRICT,在使用这个选项时，如果规划里有对象，删除操作就会发生错误。
第二个选项是CASCADE，如果规划里有对象，与规划相关联的全部数据库对象都会被删除。
语法：
DROP SCHEMA SCHEMA_NAME { RESTRICT | CASCADE }
如果所使用的SQL实现里没有DROP SCHEMA命令，我们可以通过删除拥有规划对象的用户来删除规划。 - 调整用户
Oracle修改用户状态的范例：
ALTER USER USER_ID [ IDENTIFIED BY PASSWORD | EXTERNALLY | GLOBALLY AS 'CN=USER']
[ DEFAULT TABLESPACE TABLESPACE_NAME ]
[ TEMPORARY TABLESPACE TABLESPACE_NAME ]
[ QUOTA INTEGER K|M |UNLIMITED ON TABLESPACE_NAME ]
[ PROFILE PROFILE_NAME ]
[ PASSWORD EXPIRE ]
[ ACCOUNT [LOCK | UNLOCK]]
[ DEFAULT ROLE ROLE1 [, ROLE2 ] | ALL
[ EXCEPT ROLE1 [, ROLE2 | NONE ] ]
MySQL使用多种手段来调整用户：
UPDATE mysql.user SET Password=PASSWORD('new password') WHERE user='username';
RENAME USER old_username TO new_username; - 用户会话
一个数据库会话就是从登录数据库到退出这段时间。
使用下面这样的命令可以明确地连接和断开数据库，从而开始和结束SQL会话：
CONNECT TO DEFAULT | STRING1 [ AS STRING2 ] [ USER STRING3 ]
DISCONNECT DEFAULT | CURRENT | ALL | STRING
SET CONNECTION DEFAULT | STRING - 禁止用户访问
禁止用户访问的一些方法：
修改用户的密码；
从数据库删除用户账户；
撤销分配给用户的相应权限。
有些实现里可以使用DROP命令删除数据库里的用户：
DROP USER USER_ID [ CASCADE ]
在很多实现里，与GRANT命令执行相反操作的是REVOKE，用于取消已经分配给用户的权限。
这个命令在SQL Server、Oracle和MySQL里的语法：
REVOKE PRIV1 [, PRIV2, ... ] FROM USERNAME
三、数据库用户使用的工具
管理数据库安全
一、什么是数据库安全
- 数据库安全
数据库安全就是保护数据不受到未授权访问。 - 用户管理和数据库安全
一般来说，用户管理是创建用户账户、删除用户账户、跟踪用户在数据库里行为的过程。
而数据库安全更近一步，包括了为特定数据库访问授予权限、从用户撤销权限、
采取手段保护数据库的其他部分（比如底层数据库文件）。
二、什么是权限
- 什么是权限
权限是用于访问数据库本身、访问数据库里的对象、操作数据库里的数据、在数据库里执行各种管理功能的许可级别。
权限是通过GRANT命令分配的，用REVOKE命令撤销。
权限类型
权限有两种类型，一种是系统权限，一种是对象权限。 - 系统权限
系统权限允许用户在数据库里执行管理操作，比如创建数据库、删除数据库、创建用户账户、删除用户、
删除和修改数据库对象、修改对象的状态、修改数据库的状态以及其他会对数据库造成重要影响的操作。
SQL SERVER里一些常见系统权限：
CREATE DATABASE--允许建立新的数据库；
CREATE PROCEDURE--允许建立新的存储过程；
CREATE VIEW--允许建立新的视图；
BACKUP DATABASE--允许用户对数据库进行备份；
CREATE TABLE--允许用户建立新表；
CREATE TRIGGER--允许用户在表上建立触发器；
EXECUTE--允许用户在特定数据库中运行给定的存储过程。
Oracle里一些常见系统权限：
CREATE TABLE--允许用户在特定规划中建立新表；
CREATE ANY TABLE--允许用户在任意规划中建立新表；
ALTER ANY TABLE--允许用户在任意规划中修改表结构；
DROP TABLE--允许用户在特定规划中删除表对象；
CREATE USER--允许用户创建其他用户账户；
DROP USER--允许用户删除既有用户账户；
ALTER USER--允许用户修改既有用户账户；
ALTER DATABASE--允许用户修改数据库特性；
BACKUP ANY TABLE--允许用户备份任意规划中任意表的数据；
SELECT ANY TABLE--允许用户查询任意规划中任意表的数据。
MySQL里一些常见的全局（系统）权限：
CREATE--允许用户创建特定对象，如数据库、表或索引；
DROP--允许用户删除特定对象；
GRANT--允许用户对特定对象分配权限；
RELOAD--允许用户进行清除缓存操作，以便清除缓存中的日志文件等内容；
SHUTDOWN--允许用户关闭MySQL实例。 - 对象权限
对象权限是针对对象的许可级别，意味着必须具有适当的权限才能对数据库对象进行操作。
对象权限由对象的所有者授予数据库里的其他用户。
ANSI标准里包含的对象权限：
USAGE：批准使用指定的域。
SELECT：允许访问指定的表。
INSERT（column_name）：允许对数据插入到指定表的指定字段。
INSERT：允许对数据插入到指定表的全部字段。
UPDATE（column_name）：允许对指定表里的指定字段进行更新。
UPDATE：允许对指定表里的全部字段进行更新。
REFERENCES（column_name）：允许在完整性约束里引用指定表里的指定字段，任何完整性约束都需要这个权限。
REFERENCES：允许引用指定表里的全部字段。 - 谁负责授予和撤销权限
使用GRANT和REVOKE命令的人通常是DBA。
三、控制用户访问
- 控制用户访问
SQL里用两个命令控制数据库访问，包括权限的授予与撤销，分别是GRANT和REVOKE。 - GRANT命令
GRANT命令用于向现有数据库用户账户授予系统级和对象级权限。
语法：
GRANT PRIVILEGE1 [, PRIVILEGE2 ] [ ON OBJECT ]
TO USERNAME [ WITH GRANT OPTION | ADMIN OPTION ] - REVOKE命令
REVOKE命令撤销已经分配给用户的权限，它有两个选项：RESTRICT和CASCADE。
当使用RESTRICT选项时，只有当REVOKE命令里指定的权限撤销之后不会导致
其他用户产生报废权限时，REVOKE才能顺利完成。
而CASCADE会撤销权限，不会遗留其他用户的权限。
语法：
REVOKE PRIVILEGE1 [, PRIVILEGE2 ] [ GRANT OPTION FOR ] ON OBJECT
FROM USER { RESTRICT | CASCADE } - 控制对单独字段的访问
语法：
GRANT PRIVILEGE1(column_name) [, PRIVILEGE2(column_name) ] [ ON OBJECT ]
TO USERNAME [ WITH GRANT OPTION | ADMIN OPTION ] - 数据库账户PUBLIC
数据库账户PUBLIC是个代表数据库里全体用户的账户。所有用户都属于PUBLIC账户。
如果某个权限被授予PUBLIC账户，那么数据库全部用户都具有这个权限。 - 权限组
Oracle里的角色（权限组）：
CONNECT--允许用户连接数据库，并且对已经访问过的任何数据库对象进行操作。
RESOURCE--允许用户创建对象、删除其所拥有的对象、为其所拥有的对象赋予权限等。
DBA--允许用户在数据库中对任何对象进行任何操作。
SQL SERVER的权限组：
DB_DDLADMIN
DB_DATAREADER
DB_DATAWRITER
四、通过角色控制权限
- 角色
角色是数据库里的一个对象，具有类似权限组的特性。 - CREATE ROLE语句
1、CREATE ROLE语句语法：
CREATE ROLE role_name
2、向角色授予权限：
GRANT PRIVILEGE1 [, PRIVILEGE2 ] [ ON OBJECT ]
TO role_name [ WITH GRANT OPTION | ADMIN OPTION ] - DROP ROLE语句
DROP ROLE语句语法：
DROP ROLE role_name - SET ROLE语句
SET ROLE语句语法：
SET ROLE role_name
创建和使用视图及异名
一、什么是视图
- 视图
什么是视图
视图是一个虚拟表
视图与表的区别
视图与表之间的主要区别在于，表占据物理空间，
而视图不需要物理空间，它只是从表里引用数据。 - 使用视图来简化数据访问
可以创建一系列的视图，让终端用户能够更简单地进行查询。 - 使用视图作为一种安全形式
使用视图，可以避免访问表的其他敏感数据。 - 使用视图维护摘要数据
假如摘要数据报告所基于的表经常更新，或是报告经常被创建，使用视图来包含摘要数据就是个很好的选择。
二、创建视图
- 视图
视图是通过CREATE VIEW语句创建的。我们可以从一个表、多个表或另一个视图来创建视图。
基本的CREATE VIEW语法：
CREATE [RECURSIVE] VIEW VIEW_NAME
[COLUMN NAME [,COLUMN NAME]]
[OF UDT NAME [UNDER TABLE NAME]
[REF IS COLUMN NAME SYSTEM GENERATED | USER GENERATED | DERIVED]
[COLUMN NAME WITH OPTIONS SCOPE TABLE NAME]
AS
{SELECT STATEMENT}
[WITH [CASCADED | LOCAL] CHECK OPTION] - 从一个表创建视图
语法：
CREATE VIEW VIEW_NAME AS
SELECT * | COLUMN1 [, COLUMN2 ]
FROM TABLE_NAME
[ WHERE EXPRESSION1 [, EXPRESSION2]]
[ WITH CHECK OPTION ]
[ GROUP BY ] - 从多个表创建视图
语法：
CREATE VIEW VIEW_NAME AS
SELECT * | COLUMN1 [, COLUMN2 ]
FROM TABLE_NAME1, TABLE_NAME2 [, TABLE_NAME3 ]
WHERE TABLE_NAME1 = TABLE_NAME2
[ AND TABLE_NAME1 = TABLE_NAME3 ]
[ EXPRESSION1 ][, EXPRESSION2]]
[ WITH CHECK OPTION ]
[ GROUP BY ] - 从视图创建视图
语法：
CREATE VIEW VIEW_NAME2 AS
SELECT * FROM VIEW_NAME1
三、WITH CHECK OPTION
-
WITH CHECK OPTION
这是CREATE VIEW语句里的一个选项，其目的是确保全部的UPDATE和INSERT语句
满足视图定义的条件。如果它们不满足条件，UPDATE或INSERT语句就会返回错误。 -
CASCADED和LOCAL
在基于视图创建另一个视图时，WITH CHECK OPTION有两个选项：
CASCADED和LOCAL，其中CASCADED是默认选项。
在基于表进行更新时，CASCADED选项会检查所有底层视图、所有完整性约束，以及新视图的定义条件。
LOCAL选项只检查两个视图的完整性约束和新视图的定义条件，不检查底层的表。
四、从视图创建表
- 从视图创建表
我们可以从视图创建一个表，就像从一个表创建另一个表（或从一个视图创建另一个视图）一样。
语法：
CREATE TABLE TABLE_NAME AS
SELECT {* | COLUMN1 [, COLUMN2 ]
FROM VIEW_NAME
[ WHERE CONDITION1 [, CONDITION2 ]
[ ORDER BY ]
五、视图与ORDER BY子句
- 视图与ORDER BY子句
CREATE VIEW语句里不能包含ORDER BY子句，但是GROUP BY子句
用于CREATE VIEW语句时，可以起到类似ORDER BY子句的作用。
六、通过视图更新数据
- 在一定条件下，视图的底层数据可以进行更新：
视图不包含结合；
视图不包含GROUP BY子句；
视图不包含UNION语句；
视图不包含对伪字段ROWNUM的任何引用；
视图不包含任何组函数；
不能使用DISTINCT子句；
WHERE子句包含的嵌套的表达式不能与FROM子句引用同一个表；
视图可以执行INSERT、UPDATE和DELETE等语句。
七、删除视图
- DROP VIEW
DROP VIEW命令用于从数据库里删除视图，它有两个选项：RESTRICT和CASCADE。
如果使用了RESTRICT选项进行删除操作，而其他视图在约束里有所引用，删除操作就会出错。
如果使用了CASCADE选项，而且其他视图或约束被引用了，那么底层的视图或约束被删除。
八、嵌套视图对性能的影响
- 嵌套视图对性能的影响
嵌套的层数越多，搜索引擎为了获得一个执行计划而需要进行的分析工作就越多。
九、什么是异名
- 异名
异名就是表或视图的另一个名称。我们创建别名通常是为了在访问其他用户的表或视图时
不必使用完整限制名。异名可以创建为PUBLIC和PRIVITE，PUBLIC的异名可以被数据库里的
其他用户使用，而PRIVITE异名只能被所有者和拥有权限的用户使用。
一般来说，全部用户都可以创建PRIVITE异名，而只有数据库管理员（DBA）或
被授权的用户可以创建PUBLIC异名。 - 创建异名
语法：
CREATE [PUBLIC|PRIVATE] SYNONYM SYNONYM_NAME FOR TABLE|VIEW
异名的另一个常见应用是，表的所有者给表创建一个异名，这样其他有权限访问
这个表的用户不必在表的名称前面添加所有者名称也可以引用这个表了。 - 删除异名
语法：
DROP SYNONYM SYNONYM_NAME
使用系统目录
一、什么是系统目录
- 什么是系统目录
系统目录是一些表和视图的集合，它们包含了关于数据库的信息。
二、如何创建系统目录
- 如何创建系统目录
系统目录或者是在数据库创建时自动创建的，或是由DBA在数据库创建之后立即创建的。
在Oracle里，系统目录的所有者是一个名为SYS的用户，它对数据库具有完全的权限。
在Microsoft SQL Server里，SQL服务程序的系统目录位于master数据库里。
在MySQL里，系统目录位于mysql数据库里。
三、系统目录里包含什么内容
- 系统目录包含的内容：
用户账户和默认设置；
权限和其他安全信息；
性能统计；
对象大小估计；
对象变化；
表结构和存储；
索引结构和存储；
数据库其他对象的信息，比如视图、异名、触发器和存储过程；
表约束和引用完整性信息；
用户会话；
审计信息；
内部数据库设置；
数据库文件的位置。 - 用户数据
关于个人用户的全部信息都保存在系统目录里：用户具有的系统和对象权限、用户拥有的对象、
用户不拥有但能访问的对象。用户可以通过查询访问用户表或视图。 - 安全信息
系统目录也保存安全信息，比如用户标识、加密的密码、各种权限和权限组。 - 数据库设计信息
系统目录包含关于数据库的信息，包括数据库的创建日期、名称、对象大小估计、
数据文件的大小和位置、引用完整性信息、索引、每个表的字段信息和属性。 - 性能统计
性能统计一般也在系统目录里，包括关于SQL语句性能的信息，比如优化器执行SQL语句的时间和方法。
其他性能信息还有内存分配和使用、数据库里剩余空间、控制表格和索引碎片的信息。
四、不同实现里的系统目录表格
- 每个实现都有一些表格和视图来构成系统目录
Microsoft SQL Server：
SYSUSERS 数据库用户
SYS.DATABASES 全部数据库片段
SYS.DATABASE_PERMISSIONS 全部数据库权限
SYS.DATABASE_FILES 全部数据文件
SYSINDEXES 全部索引
SYSCONSTRAINTS 全部约束
SYS.TABLES 全部数据库表
SYS.VIEWS 全部数据库视图
Oracle：
ALL_TABLES 用户访问的表
USER_TABLES 用户拥有的表
DBA_TABLES 数据库里全部表
DBA_SEGMENTS 片段存储
DBA_INDEXES 全部索引
DBA_USERS 数据库里的全部用户
DBA_ROLE_PRIVS 分配的角色
DBA_ROLES 数据库里的角色
DBA_SYS_PRIVS 分配的系统权限
DBA_FREE_SPACE 数据库剩余空间
V$DATABASE 数据库的创建
V$SESSION 当前会话
MySQL：
COLUMNS_PRIV 字段权限
DB 数据库权限
FUNC 自定义函数的管理
HOST 与MySQL相关联的主机名称
TABLES_PRIV 表权限
USER 表关系
五、查询系统目录
- 查询系统目录
我们可以像对待数据库里的其他表格和视图一样使用SQL查询系统目录里的表格和视图。
六、更新系统目录
- 更新系统目录
系统目录只能执行查询操作--DBA也是如此。系统目录的更新是由数据库服务程序自动完成的。
高级SQL主题
一、光标
- 光标
光标被用于通过以记录为单位的操作，来获得数据库中数据的子集。
MySQL光标的语法：
DECLARE CURSOR_NAME CURSOR
FOR SELECT_STATEMENT
SQL Server光标的语法：
DECLARE CURSOR_NAME CURSOR
FOR SELECT_STATEMENT
[ FOR [READ ONLY | UPDATE {[ CULUMN_LIST ]}]]
Oracle光标的语法：
DECLARE CURSOR CURSOR_NAME
IS {SELECT_STATEMENT}
在光标被创建之后，可以使用如下操作对其进行访问。
OPEN：打开定义的光标。
FETCH：从光标获得记录，赋予程序标量。
CLOSE：在对光标的操作完成之后，关闭光标。 - 打开光标
在MySQL和Microsoft SQL Server里打开光标的语法：
OPEN CURSOR_NAME
在Oracle里打开光标的语法：
OPEN CURSOR_NAME [ PARAMETER1 [, PARAMETER2 ]] - 从光标获取数据
在SQL Server里，FETCH语句的语法：
FETCH NEXT FROM CURSOR_NAME [INTO FETCH_LIST ]
在Oracle里的语法：
FETCH CURSOR_NAME {INTO : HOST_VARIABLE
[[ INDICATOR ] : INDICATOR_VARIABLE ]
[, : HOST_VARIABLE
[[ INDICATOR ] : INDICATOR_VARIABLE ]]
| USING DESCRIPTOR DESCRIPTOR ] }
在MySQL里的语法：
FETCH CURSOR_NAME INTO VARIABLE_NAME, [VARIABLE_NAME]... - 关闭光标
SQL Server里关闭和释放标签的语法：
CLOSE CURSOR_NAME
DEALLOCATE CURSOR CURSOR_NAME
Oracle里的语法：
CLOSE CURSOR_NAME
MySQL里的语法：
CLOSE CURSOR_NAME
二、存储过程和函数
- 存储过程和存储函数
存储过程：
存储过程是保存在数据库里的一组SQL语句或函数，它们被编译，随时可以被数据库用户使用。
存储函数：
存储函数与存储过程是一样的，但函数可以返回一个值。 - 创建存储过程
MySQL创建存储过程的语法：
CREATE [ OR REPLACE ] PROCEDURE PROCEDURE_NAME
[ (ARGUMENT [ {IN | OUT | IN OUT} ] TYPE,
ARGUMENT [{IN | OUT | IN OUT} ] TYPE) ] { AS }
PROCEDURE_BODY
SQL Server创建存储过程的语法：
CREATE PROCEDURE PROCEDURE_NAME
[ [(] @PARAMETER_NAME
DATATYPE [(LENGTH) | (PRECISION] [, SCALE ])
[ = DEFAULT ][ OUTPUT ]]
[, @PARAMETER_NAME
DATATYPE [(LENGTH) | (PRECISION [, SCALE ])
[ = DEFAULT ][ OUTPUT ]] [)]]
[ WITH DECOMPILE ]
AS SQL_STATEMENTS
Oracle创建存储过程的语法：
CREATE [ OR REPLACE ] PROCEDURE PROCEDURE_NAME
[ ( ARGUMENT [{IN | OUT | IN OUT} ] TYPE,
ARGUMENT [(IN | OUT | IN OUT} TYPE) ] {IS | AS}
PROCEDURE_BODY - 执行存储过程的语法：
SQL Server执行存储过程的语法：
EXECUTE [@RETURN_STATUS = ]
PROCEDURE_NAME
[[@PARAMETER_NAME = ] VALUE |
[@PARAMETER_NAME = ] @VARIABLE [ OUTPUT ]]
[WITH RECOMPILE]
Oracle的语法：
EXECUTE [@RETURN STATUS =] PROCEDURE NAME
[[ @PARAMETER NAME = ] VALUE | [ @PARAMETER NAME = ] @VAIRABLE [ OUTPUT ]]]
[ WITH RECOMPILE ]
MySQL的语法：
CALL PROCEDURE_NAME([PARAMETER[,...]]) - 与单个SQL语句相比，存储过程具有一些明显的优点，包括：
存储过程的语句已经保存在数据库里了；
存储过程的语句已经被解析过，以可执行格式存在；
存储过程支持模块化编程；
存储过程可以调用其他存储过程和函数；
存储过程可以被其他类型的程序调用；
存储过程通常具有更好的响应时间；
存储过程提高了整体易用性。
三、触发器
- 触发器
触发器是数据库里编译了的SQL过程，基于数据库里发生的其他行为来执行操作。
它是存储过程的一种，会在特定DML行为作用于表格时被执行。 - CREATE TRIGGER语句
ANSI标准的语法是：
CREATE TRIGGER TRIGGER NAME
[[BEFORE | AFTER] TRIGGER EVENT ON TABLE NAME]
[REFERENCING VALUES ALIAS LIST]
[TRIGGERED ACTION
TRIGGER EVENT::=
INSERT | UPDATE | DELETE [OF TRIGGER COLUMN LIST]
TRIGGER COLUMN LIST ::= COLUMN NAME [,COLUMN NAME]
VALUES ALIAS LIST ::=
VALUES ALIAS LIST ::=
OLD [ROW] ` OLD VALUES CORRELATION NAME |
NEW [ROW] ` NEW VALUES CORRELATION NAME |
OLD TABLE ` OLD VALUES TABLE ALIAS |
NEW TALBE ` NEW VALUES TABLE ALIAS
OLD VALUES TABLE ALIAS ::= INDENTIFIER
NEW VALUES TABLE ALIAS ::= INDENTIFIER
TRIGGERED ACTION ::=
[FOR EACH [ROW | STATEMENT][WHEN SEARCH CONDITION]]
TRIGGERED SQL STATEMENT
TRIGGERED SQL STATEMENT ::=
SQL STATEMENT | BEGIN ATOMIC [SQL STATEMENT;]
END
MySQL里使用触发器的语法：
CREATE [DEFINER={USER | CURRENT_USER}]
TRIGGER TRIGGER_NAME
{BEFORE | AFTER}
{ INSERT | UPDATE | DELETE [,...]}
ON TABLE_NAME
AS
SQL_STATEMENTS
SQL Server里创建触发器的语法：
CREATE TRIGGER TRIGGER_NAME
ON TALBE_NAME
FOR { INSERT | UPDATE | DELETE [,...]}
AS
SQL_STATEMENTS
[ RETURN ]
Oracle的基本语法：
CREATE [ OR REPLACE ] TRIGGER TRIGGER_NAME
[ BEFORE | AFTER]
[ DELETE | INSERT | UPDATE]
ON [ USER.TABLE_NAME]
[ FOR EACH ROW ]
[ WHEN CONDITION ]
[PL/SQL BLOCK] - DROP TRIGGER语句
语法：
DROP TRIGGER TRIGGER_NAME - FOR EACH ROW语句
MySQL里的触发器还可以调整触发条件。FOR EACH ROW语法可以让过程在SQL语句影响每条记录时都触发，
或是一条语句只触发一次。
语法：
CREATE TRIGGER TRIGGER_NAME
ON TABLE_NAME FOR EACH ROW SQL_STATEMENTS
四、动态SQL
- 动态SQL
动态SQL允许程序员或终端用户在运行时创建SQL语句的具体代码，并且把语句传递给数据库。
数据库然后就把数据返回到绑定的程序变量里。
五、调用级接口
- 调用级接口
调用级接口用于把SQL代码嵌入到主机程序。
六、使用SQL生成SQL
- 使用SQL生成SQL
范例：
SELECT 'GRANT ENABLE TO '|| USERNAME || ';'
FROM SYS.DB_USERS;
七、直接SQL与嵌入SQL
- 直接SQL
直接SQL是指从某种形式的交互终端上执行的SQL语句，它的执行结果会直接返回到终端。 - 嵌入SQL
嵌入SQL是在其他程序里使用的SQL代码，通常以EXEC SQL开始，以分号结束。
嵌入SQL的范例：
{HOST PROGRAMMING COMMANDS}
EXEC SQL {SQL STATEMENT};
{MORE HOST PROGRAMMING COMMANDS}
八、窗口表格函数
- 窗口表格函数
窗口表格函数可以对表格的一个窗口进行操作，并且基于这个窗口返回一个值。
窗口表格函数的语法：
ARGUMENT OVER ([PARTITION CLAUSE]) (ORDER CLAUSE] [FRAME CLAUSE])
几乎所有汇总函数都可以作为窗口表格函数，另外还有5个窗口表格函数：
RANK() OVER；
DENSE_RANK() OVER；
PERCENT_RANK() OVER；
CUME_DIST() OVER；
ROW_NUMBER() OVER。
Microsoft SQL Server范例：
SELECT EMP_ID, SALARY, RANK() OVER (PARTITION BY YEAR(DATA_HIRE)
ORDER BY SALARY DESC) AS RANK_IN_DEPT
FROM EMPLOYEE_PAY_TBL
九、使用XML
- 使用XML
SQL Server就通过语句FOR XML提供这个功能，范例：
SELECT EMP_ID, HIRE_DATE, SALARY FROM
EMPLOYEE_TBL FOR XML AUTO
MySQL通过EXTRACTVALUE函数提供了这个功能，它有两个参数，第一个是XML片段，
第二个是定位器，用于返回与字符串匹配标记的第一个值，语法：
ExtractValue([XML Fragment],[locator string])
SQL扩展到企业、互联网和内部网
一、SQL与企业
- 后台程序
后台程序包括实际的数据库服务程序、数据源、把程序连接到Web或局域网上远程数据库的中间软件。 - 前台程序
前台程序是应用的组成部分，终端用户通过它进行交互。
二、访问远程数据库
- 远程数据库
远程数据库是非本地的，或是说位于非直接连接的服务器上，这时我们必须使用网络和网络协议与数据库进行交互。 - ODBC
开放式数据库连接（ODBC）可以通过一个库驱动程序连接到数据库。 - JDBC
JDBC是Java数据库连接，它类似ODBC，通过一个Java库驱动连接到数据库。 - OLE DB
OLE DB不仅可以连接各种数据库实现，也可以连接非数据库存储的数据。 - 产商连接产品
除了驱动和API之外，很多产商也提供了自己的产品，可以把用户连接到远程数据库。 - 通过Web接口访问远程数据库
通过Web接口访问远程数据库十分类似于通过局域网进行访问，主要区别在于
用户的全部请求都经过Web服务程序进行了路由。
三、SQL与互联网
- 让数据可以被全世界的顾客使用
随着互联网的出现，数据对全世界的顾客和产商都开放了。 - 向雇员和授权顾客提供数据
数据库可以通过互联网或公司的内部网向雇员或顾客提供访问。
四、SQL与内部网
- 内部网和互联网
内部网是针对单个公司的应用，而互联网是对公共大众开放的。
标准SQL的扩展
一、各种实现
- 多种实现
多家厂商发布了多种SQL实现。其中包括MySQL、Microsoft SQL Server和Oracle。
其他一些比较流行的厂商还有Sybase、IMB、Informix、Progress、PostgreSQL等。 - 不同实现之间的区别
比较几个主流厂商和ANSI标准的SELECT语句。
ANSI标准：
SELECT [DISTINCT] [* | COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE SEARCH_CONDITION ]
GROUP BY [ TABLE_ALIAS | COLUMN1 [, COLUMN2 ]
[ HAVING SEARCH_CONDITION ]]
[ ALL ]
[ CORRESPONDING [ BY (COLUMN1 [, COLUMN2 ]) ]
QUERY_SPEC | SELECT * FROM TABLE | TABLE_CONSTRUCTOR ]
[ORDER BY SORT_LIST]
Microsoft SQL Server的语法：
[WITH <COMMON_TABLE_EXPRESSION>]
SELECT [DISTINCT][*| COLUMN1 [, COLUMN2, ...]
[INTO NEW_TABLE]
FROM TABLE1 [, TABLE2 ]
[WHERE SEARCH_CONDITION]
GROUP BY [COLUMN1, COLUMN2, ...]
[HAVING SEARCH_CONDITION]
[ {UNION | INTERSECT | EXCEPT} ][ ALL ]
[ ORDER BY SORT_LIST ]
[ OPTION QUERY_HINT ]
Oracle的语法：
SELECT [ ALL | DISTINCT ] COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE SEARCH_CONDITION ]
[[ START WITH SEARCH_CONDITION ]
CONNECT BY SEARCH_CONDITION ]
[ GROUP BY COLUMN1 [, COLUMN2 ]
[ HAVING SEARCH_CONDITION ]]
[{UNION [ ALL ] | INTERSECT | MINUS} QUERY_SPEC ]
[ ORDER BY COLUMN1 [, COLUMN2 ]]
[ NOWAIT ] - 遵循ANSI SQL
对于被认为遵循ANSI的数据库来说，它只需要对应于ANSI标准的一个功能子集。
把代码限制到严格遵循ANSI标准的语句能够提高可移植性，但数据库性能可能不会达到最优。
总之，我们要在可移植性和性能之间权衡。权衡的结果通常是放弃可移植性，从而充分利用用户所用平台的性能。 - 实际上，全部主流厂商都对SQL有所扩展。
Oracle的PL/SQL、Sybase和Microsoft SQL Server使用Transact-SQL是标准SQL扩展的两个范例。
二、扩展范例
- Transact-SQL
Transact-SQL是Microsoft SQL Server使用的一种过程语言，表示我们告诉数据库如何、在何处获取和操作数据。
Transact-SQL语句的范例：
IF (SELECT AVG(COST) FROM PRODUCTS_TBL) > 50
BEGIN
PRINT 'LOWER ALL COSTS BY 10 PERCENT.'
END
ELSE
PRINT 'COSTS ARE REASONABLE.' - PL/SQL
PL/SQL是Oracle对SQL的扩展，也是一种过程语言，由代码的逻辑块构成。一个逻辑块包含三个部分，其中两个是可选的。
第一部分是DECLARE部分，是可选的。它包含变量、光标和常数。
第二部分是PROCEDURE，是必需的，包含条件命令和SQL语句，是逻辑块的执行部分。
第三部分是EXCEPTION，是可选的，定义了程序处理错误和自定义异常。
PL/SQL语句的范例：
DECLARE
CURSOR EMP_CURSOR IS SELECT EMP_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME
FROM EMPLOYEE_TBL;
EMP_REC EMP_CURSOR%ROWTYPE;
BEGIN
OPEN EMP_CURSOR;
LOOP
FETCH EMP_CURSOR INTO EMP_REC;
EXIT WHEN EMP_CURSOR%NOTFOUND;
IF (EMP_REC.MIDDLE_NAME IS NULL) THEN
UPDATE EMPLOYEE_TBL
SET MIDDLE_NAME = 'X'
WHERE EMP_ID = EMP_REC.EMP_ID;
COMMIT;
END IF;
END LOOP;
CLOSE EMP_CURSOR;
END; - MySQL
MySQL被认为是一种比较符合ANSI标准的数据库实现。
三、交互SQL语句
- 交互SQL语句
交互SQL语句会在完全执行之前询问用户变量、参数或某种形式的数据。
1145
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
