SQL进阶（三）

SQL Alias 别名：

SQL别名用于为表或表中的列提供临时名称，使列名具有可读性，一个别名只存在于查询期间；

列的SQL Alias语法：

SELECT column_name AS alias_name

FROM table_name；

表的SQL Alias语法：

SELECT column_name(s)

FROM table_name AS alias_name；

SQL约束：

约束类型：

约束	描述
NOT NULL约束	保证列中数据不能有NULL值
DEFAULT约束	提供该列数据未指定时所采用的默认值
UNIQUE约束	保证列中各数据各不相同
主键约束	唯一标识表中的行/记录
外键约束	唯一标识其他表中的行/记录
CHECK约束	此约束保证列中所有值满足某一条件
索引	用于在数据库中快速创建或检索数据

SQL创建约束：

CREAT TABLE table_name

(

　　column1 datatype constraint，

　　column2 datatype constraint，

　　column3 datatype constraint，

　　...

)

删除约束：

任何现有约束都可以通过ALTER TABLE命令中指定DROP CONSTRAINT 选项的方式删除掉；

如：删除Employees表中的主键约束，可用如下命令：

ALTER TABLE Employees DROP CONSTRAINT Employees_PK；

UNIQUE 约束：

每个表可以有多个UNIQUE约束，但只能有一个PRIMARY KEY约束；

在SQL Server，Oracle，MS Access中，UNIQUE约束可向NOT NULL约束一样写于列的datatype后面；

在MySQL中：为“person”表中的“P_Id”列创建UNIQUE约束：

CREAT TABLE person

(

　　P_Id int NOT NULL,
　　LastName varchar(255) NOT NULL,
　　FirstName varchar(255),
　　Address varchar(255),
　　City varchar(255),
　　UNIQUE (P_Id)

)

如需命名UNIQUE约束，并定义多个列的UNIQUE约束，需用如下语法（适用于MySQl、SQL Server,Oracle，MS Access）：

CONSTRAINT unique_name UNIQUE(column1,column2...)

当表已经创建时，如需在“p_Id”列创建UNIQUE约束，需用如下语法（适用于MySQl、SQL Server,Oracle，MS Access）：

ALTER TABLE Persons

ADD UNIQUE（p_Id）；

撤销UNIQUE约束：

MySQL：

ALTER TABLE table_name

DROP INDEX unique_name

撤销UNIQUE约束（SQL Server、Oracle、MS Access）：

ALTER TABLE table_name

DROP CONTRAINT unique_name；

 PRIMARY KEY约束：

PRIMARY KEY 唯一标识数据库中的每条记录，主键必须包含唯一值，主键列不能包含NULL值，每个表都应该有一个主键且只能有一个主键；

PRIMARY KEY在用法上同UNIQUE，请参照UNIQUE，在此不多做阐释。

FOREIGN KEY约束：

一个表中的FOREIGN KEY 指向另一个表中的PRIMARY KEY，FOREIGN KEY 约束能预防表之间连接破坏的行为。

MySQL：在“Orders”表中在“P_Id”列创建外键约束：

CREAT 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）

）；

SQL Server，Oracle，MS Access:

CREAT  TABLE Oreders

（

　　O_Id int NOT NULL PRIMARY KEY，

　　OrederNo int NOT NULL，

　　P_Id int FOREIGN KEY REFERENCES Persons（P_Id）

）；

如需命名FOREIGN KEY并定义多个列的FOREIGN KEY约束，使用如下语句（适用于SQL Server，Oracle，MS Access，MySQL）：

CREAT TABLE Orders

（

O_Id int  NOT NULL，

OrderNo int NOT NULL，

P_Id int，

PRIMARY KEY（O_Id），

CONSTRAINT fk_PerOrders FOREIGN KEY（P_Id）REFERENCES Persons（P_Id）

）

DEFAULT约束：

DEFAULT 约束用于向表中插入默认值，如果没有规定其他的值，会将默认值插入到表中；

CREAT TABLE Persons

（

　　P_Id int NOT NULL，

　　LastName varchar(255) NOT NULL，

　　FirstName varchar(255），

　　Address varchar(255)，

　　City varchar(255) DEFAULT “sandnes”

）；

若表已经被创建时，需创建DEFAULT约束使用如下语句：

MySQL：

ALTER TABEL Persons

ALTER City SET DEFAULT “sandnes”；

SQL Server，MS Access：

ALTER TABLE Persons

ADD CONSTRAINT DF_Persons_City DEFAULT ("sandnes") FOR City;

Oracle：

ALTER TABLE Persons

MODIFY City DEFAULT “sandnes”；

CHECK约束：

CHECK约束用于对列中的值进行约束；如果对单个列进行约束，那么该列只允许特定的值；如果对一个表进行约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制；

CHECK用法同UNIQUE用法，约束写于括号内；

SQL JOIN连接：

SQL JOIN语句用于将数据库中两个或两个以上表中的记录组合起来，连接通过公有值将不同表中的字段组合在一起；

关于SQL INNER JOIN、RIGHT JOIN、LEFT JOIN、FULL JOIN在SQL高级语句（二）中已有详细阐述，在此不多做阐释。

现介绍一种新的连接方式：

笛卡尔连接：又称为交叉连接，返回两个或者更多连接表中记录的笛卡尔乘积，也就是说，他相当于连接谓词总是为真或者缺少连接谓词的内连接。基本语法如下：

SELECT table1.column1，table2.column2...

FROM tabel1,table2...

SQL UNION语句：

SQL UNION语句用于将两个或更多的SELECT语句运算结果组合起来。在SQL高级语句（二）中已有详细阐述，在此不多做阐释。

有两个子句（即运算法）有UNION语句非常像：

INTERSECT子句：用于组合两个SELECT语句，但是只返回两个SELECT语句的结果中都有的行；

EXCEPT子句：组合两个SELECT语句，并将第一个SELECT语句的结果中存在，但是第二个SELECT语句的结果中不存在的行返回。

SQL克隆数据表：

某些情况下，我们可能需要原样拷贝某张数据库表，但是，CREAT TABLE却不能满足我们的需求，因为复制表必须和原表拥有一样的索引，默认值等；

若使用MySQL关系型数据库管理系统，可用以下几个步骤解决该问题：

1、使用SHOW CREAT TABLE table_name命令来获取一条指定原表的结构，索引等信息的CREAT TABLE语句；

2、将语句中的表名修改为克隆表的名字，然后执行该语句，这样便可得到一张与原表完全相同的克隆表；

3、若还需要克隆表中的数据，需使用INSERT INTO...SELECT语句。

SQL索引：

建立索引是加快表查询速度的有效手段。当我们需要在一本书中查找某些信息时，往往是通过目录找到所需信息对应的页码，然后再从该页码中找到所要的信息，这种做法比直接翻阅书的内容速度要更快。如果把数据库比作一本书，那么表的索引就是这本书的目录，可以通过索引大大加快表的查询；

同UNIQUE约束一样，索引可以是唯一的，这种情况下，索引会阻止列中（或者列的组合，其中某些列有索引）出现重复的条目；

CREAT INDEX命令：

CREAT INDEX index_name ON table_name；

单列索引（即基于某一字段创建的索引）：

CREAT INDEX index_name

ON table_name （column_name)；

唯一索引：

唯一索引不止用于提升查询性能，还用于保证数据完整性，唯一索引不允许向表中插入重复值；

CREAT UNIQUE INDEX index_name

ON table_name（column_name）；

聚簇索引：

聚簇索引在表中两个或更多列的基础上建立；

CREAT INDEX index_name

ON table_name(column1,column2...)；

创建单列索引还是聚簇索引要看每次查询中，哪些列在作为过滤条件的WHERE子句中最常出现；

隐式索引：

隐式索引由数据库服务器在创建某些对象时自动生成，如对于主键约束和唯一约束，数据库服务器就会自动创建索引；

DROP INDEX命令：

DROP INDEX index_name;

尽管创建索引的目的是为了提升数据库的性能，但是还是有些情况应当避免使用索引，下面的几条指导原则给出了何时需要考虑是否使用索引：

1、小的数据库不应当使用索引；

2、需要频繁进行大批量的更新或插入操作的表不宜创建索引；

3、如果列中包含大数或者NULL值，不宜创建索引；

4、频繁操作的列不宜创建索引；

 SQL子查询：

又称为内查询或嵌套查询，是嵌套在SQL查询的WHERE子句中的查询；

子查询用于为主查询返回所需数据，或者对检索数据进行进一步的限制。

子查询可以在SELECT，INSERT，UPDATE和DELETE语句中，同=，<,>,>=,<=,IN，BETWEEN运算符一起使用；

使用子查询必需遵循下面几个规则：

1、子查询必须在圆括号中；

2、子查询的SELECT子句中只能有一个列，除非主查询中有多个列，用于与子查询选中的列相比较；

3、子查询不能使用OREDER BY，不过主查询可以。在子查询中，GROUP BY可以起到同ORDER BY一样的作用；

４、返回多行数据的子查询只能同多指操作符一起使用，比如IN操作符；

5、子查询不能直接用于集合函数中；

6、BETWEEN操作符不能同子查询一起使用，但BETWEEN操作符可以用在子查询中；

7、SELECT列表中不能包含对BLOB、ARRAY、CLOB或NCLOB类型值的引用；

SELECT语句中的子查询：

SELECT column_name[,column_name]

FROM table1[，table2]

WHERE column_name OPERATOR（如IN、=，<等）

　　（

　　SELECT column_name[,column_name]

　　FROM table1[,table2]

　　[WHERE]

　　）

INSERT 语句中的子查询：

INSERT INTO  table_name[(column1 [,column2])]

　　SELECT [ *|column1 [,column2]

　　RROM table1 [,table2]

　　[WHERE VALUE OPERATOR]

UPDATE中的子查询：

UPDATE tabel

SET column_name = new_value

[WHERE OPERATOR [VALUE]

　　(SELECT column_name

　　FROM table_name

　　[WHERE])

DELETE语句中的子查询：

DELETE FROM table_name

[WHERE OPERATOR [value]

　　(SELECT column_name

　　FROM table_name

　　[WHERE])

ALTER TABLE 语句：

ALTER命令用于添加删除或更改现有表中的列；

添加新列：

ALTER TABLE table_name ADD column_name datatype；

删除列:

ALTER TABLE table_name DROP COLUMN column_name；

更改列的数据类型：

ALTER TABLE　table_name MODIFY COLUMN column_name datatype；

添加NOT NULL约束：

ALTER TABLE table_name MODIFY column_name datatype NOT NULL；

 添加唯一约束：

ALTER TABLE table_name

ADD CONSTRAINT MyUniqueConstraint UNIQUE（column1，column2..)

添加CHECK约束：

ALTER TABLE

ADD CONSTRAINT MyCheckConstraint CHECK（condition）；

添加主键约束：

ALTER TABLE table_name

ADD CONSTRAINT MyPrimaryKey PRIMARY KEY（column1，column2...)

删除约束：

ALTER TABLE tabel_name

DROP CONSTRAINT constraintName；

MySQL删除约束：

ALTER TABLE table_name

DROP INDEX constraint_name；

MySQL删除主键列约束：

ALTER TABLE table_name

DROP PEIMARY KEY

SQL处理重复数据：

SQL中DISTINCT关键字与SELECT语句一起使用，可以达到消除重复记录，只返回唯一记录的目的；

SELECT DISTINCT column1，column2，....columnN

FROM table_name

WHERE [condition]

SQL视图：

视图是存储在数据库中的具有名字的SQL语句，或者说是以预定义的SQL查询的形式存在的数据表的成分，是一种虚拟的表；允许用户有以下的操作：

1、以用户或者某些类型的用户感觉自然或者直观的方式来组织数据；

2、限制对数据的访问，从而使得用户仅能够看到或者修改他们需要的数据；

3、从多个表中汇总数据，以产生报表；

创建视图：

CREAT VIEW view_name AS

SELECT column1，column2...

FROM table_name

WHERE [condition]；

CREAT CHECK OPTION：

CREAT CHECK OPTION是CREATE VIEW语句的一个可选项。WITH CHECK OPTION用于保证所有的UPDATE和INSERT语句都满足视图中的条件，如果不能满足就会报错，如：

CREAT VIEW customers_view AS

SELECT name，age

FROM customers

WHERE age IS NOT NULL

WITH CHEKC OPTION；

这的WITH CHECK OPTION使得视图拒绝任何avg字段为NULL的条目；

更新视图：

视图可以在特定的情况下更新：

1、SELECT子句不能包含DISTINCT关键字；

2、SELECT子句不能包含任何汇总函数；

3、SELECT子句不能包含任何集合函数；

4、SELECT子句不能包含任何集合运算符；

5、SELECT子句不能包含ORDER BY 子句；

6、FROM子句中不能有多个数据表；

7、WHERE子句不能包含子查询；

８、查询语句中不能包含HAVING语句或GROUP BY；

9、计算得出的列不能更新；

10、视图必须包含原始数据表中所有的NOT NULL列，从而使INSERT查询生效；

向视图中插入新行，删除视图中的行的规则同UPDATE命令；

删除视图：

DROP VIEW view_name；

SQL HAVING子句：

HAVING子句用于过滤指定条件，从而控制查询结果中哪些组可以出现在最终结果里面；WHERE子句对被选择的列施加条件，而HAVING子句则对GROUP BY子句所产生的组施加条件；

SELECT column1，column2

FROM table1，table2

WHERE [condition]

GROUP BY column1,column2

HAVING [condition]

ORDER BY column1,column2;

SQL事务：

事务是在数据库上按照一定逻辑顺序执行的任务序列，既可以由用户手动执行，也可以由某种数据库程序自动执行；事务实际上就是对数据库的一个或多个更改，当我们在数据库上创建更新或者删除记录师，我们就已经使用事务了；控制事务以保证数据的完整性，并对数据库错误做出处理；

事务的属性：（ＡＣＩＤ）

原子性：保证任务中的所有操作都执行完毕，否则事务会在出现错误时终止，并回滚之前所有操作到原始状态；

一致性：如果事务执行成功，则数据库的状态进行了正确的转变；

隔离性：保证不同的事务相互独立，透明的执行；

持久性：即使出现系统故障，之前成功执行的事务的结果也会持久存在；

事务控制的四个命令：

COMMIT：提交更改；

ROLLBACK：回滚更改；

SAVEPOINT：在事务内部创建一系列可以ROLLBACK的还原点；

SET TRANSACTION：命名事务；

COMMIT：

COMMIT命令用于保存事务对数据库的更改，会将上次COMMIT命令或者ROLLBACK命令执行以来所有的事务都保存到数据库中，语法如下：

COMMIT；

ROLLBACK：

用于撤销尚未保存到数据库中的事务；只能撤销上次自COMMIT命令或者ROLLBACK命令执行以来的事务，语法如下：

ROLLBACK；

SAVEPOINT命令：

SAVEPOINT是事务中的一个状态点，使得我们可以将事务回滚到特定的点，而不是将整个事务都撤销，语法如下：

SAVEPOINT savepoint_name；

而ROLLBACK是撤销一系列的事务，若要回滚至某一保存点需使用：

ROLLBACK TO savepoint_name；

RELEASE SAVEPOINT命令：

RELEASE SAVEPOINT用于删除之前创建的保存点，语法如下：

RELEASE SAVEPOINT savepoint_name；

SET TRANSATION命令：

SET TRANSATION命令可以用来初始化数据库事务，指定随后的事务的各种特征。如将某个事务指定为只读或读写：

SET TRANSATION [READ WRITE|READ ONLY];