SQL语言精要-优快云博客

本文链接：https://blog.youkuaiyun.com/johnny_83/article/details/1835655

关系数据库标准SQL语言

SQL(Structured Query Language)的特点　SQL语言集数据查询（Data Query）、数据管理(Data Manipulation)、数据定义(Data Defination)和数据控制(Data Control)功能于一体．

一) 综合统一　　SQL语言则集数据定义语言(Data Definition Language)DDL、数据操作语言(Data Manipulation Language)DML、数据控制语言(Data Control Language)DCL的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动。另外，在关系模型中实体于实体间的联系均用关系表示，这中数据结构的单一性带来了数据操作符的统一，查询、插入、删除、修改等每一种操作都只需一种操作符。

二) 高度非过程化　　只要提出做什么，二无须指明怎么做，因此无需了解存取路径，存取路径的选择以及SQL语言的操作过程由系统自动完成。大大的减少用户的负担，而且有利于提高数据的独立性。

三) 面向集合的操作方式　　SQL语言采用集合操作方式，不仅操作对象、查询结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。

四) 以同一种语法结构提供两种使用方式　　SQL语言既是自含式语言，又是嵌入式语言。作为自含式语言，它能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作；作为嵌入式语言，SQL语句能够嵌入到高级语言程序中，供程序员设计程序时使用。

五) 语言简捷，易学易用　　核心功能只用了九个动词，如下表：

SQL功能	动词
数据查询	SELECT
数据定义	CREATE、DROP、ALTER
数据操作	INSERT、UPDATE、DELETE
数据控制	GRANT、REVOKE

SQL语言支持关系数据库的三级模式结构。其中：外模式对应于试图（View）和部分基本表（Base Table），模式对应于基本表、内模式对应于存储文件。

用户可以用SQL语言对基本表和视图进行查询或其它操作，基本表和视图都是关系。

基本表是本身独立存在的表，在SQL中一个关系就对应一个表。一个基本表对应一个存储文件，一个表可以带来若干索引，索引也存放在存储文件中。

存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理结构是任意的，对用户是透明的。

视图是从一个或几个基本表导出的表。它本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据，这些数据乃存放在导出视图的基本表中，因此视图是一个虚表。视图在概念上与基本表等同，用户可以在视图上在定义视图。

数　据　定　义

关系数据库系统支持三级模式结构，其模式、外模式和内模式中的基本表对象有表、视图和索引。因此SQL的数据定义功能包括定义基本表、定义视图和定义索引。

视图基于基本表的虚表，索引是依附于基本表的，因此SQL通常不提供修改视图的定义和修改索引定义的操作。用户如果修改视图定义或索引定义，只能先将它们删除掉，然后再重建。不过Oracle允许直接修改视图定义。

一）　定义、删除与修改基本表

1>　定义基本表

CREATE TABLE　[ database_name.[ owner ] .| owner.] table_name

　　 (

<column_name> <data_type> [column_constaint]

[,<column_name> <data_type> [column_constaint]]...

[,<table_constaint>]

);

建立表的同时通常可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操纵表中数据时由DBMS自动检查该操纵是否违背这些完整性约束条件。如果完整性也属条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。

数据类型：

类型	描述
SMALLINT	半字长二进制整数
INTEGER(或INT)	全字长二进制整数
DECIMAL(p[,q])(或DEC(p[,q]))	压缩十进制，共p位，其中小数后有q位。0<=q<=p<=15,q=0时可以省略不写。
FLOAT	双子长浮点型
CHARTER(n)(或CHAR(n))	长度为n的定长字符串
VARCHAR(n)	最大长度为n的变长字符串
GRAPHIC(n)	长度为n的定长图形字符串
VARGRIPHIC(n)	最大长度为n的变长图形字符串
DATE	日期型，格式为YYYY-MM-DD
TIME	时间型，格式为HH.MM.SS
TIMESTAMP	日期加时间

2>　删除基本表　

DROP TABLE [ database_name.[ owner ] .| owner.] table_name;

基本表一旦被删除，表中的数据、此表上建立的索引和视图都将自动被删除掉。因此执行删除基本表时要额外小心。

3>　修改基本表

ALTER TABLE [ database_name.[ owner ] .| owner.] table_name

[ADD <new_column_name> <data_type> [column_constaint]]

[DROP <column_constaint> <column_name>]

[MODIFY <column_name> <data_type>]

注：SQL没有提供删除属性列的语句，用户只能间接的实现这以功能，即把表中要保留的列及内容复制到一个新表中，然后删除原表，再将新表重命名要的原表名。

不论基本表中原来是否有数据，新增的列一律为空。

二）建立与删除索引　

建立索引是为了加快查询速度的有效手段。用户可以根据应用环境的需要，在基本表上建立一个或多个索引，以提供多种存取路径，加快查找速度。系统在存取数据时会自动选择合适的索引作为存取路径，用户不必也不能选择索引。

1>　建立索引

CREATE [UNIQUE] [CLUSTER] INDEX <index_name>

ON <table_name> (<column_name>[ASC|DESC] [,<column_name>[ASC|DESC] ]...);

UNIQUE: 表明此索引的每一个索引值只对应唯一的数据纪录。

CLUSTER:表示要建立的索引是聚族索引。所谓聚族索引是指索引项的顺序与表中纪录的物理顺序一致的索引阻止。

用户可以在常查询的列建立聚族索引以提高查询效率。显然在一个基本表上最多只能建立一个聚族索引。建立聚族索引后，更新索引列数据时，往往导致表中纪录的数据顺序的变更，代价较大，因此对于经常更新的列不易建立聚族索引。　　

索引一经建立，就由系统使用和维护它，不需用户干预。

2>　删除索引

DROP INDEX <index_name>

三）建立与删除视图

视图是关系数据库系统供给用户以多种角度观察数据库中数据的重要机制。

视图是从一个或多个基本表（或视图）导出的表，它与基本表不同，是一个虚表。数据库只存放视图的定义，而不存放视图对于的数据，这些数据乃存放在原来的基本表中。所以基本表中的数据发生变化，从视图中的查询出的数据也就随之改变。

视图一经定义，就可以和基本表一样被查询、被删除，我们也可以在一个视图之上再定义新的视图，但对视图的更新（增加、删除、修改）操纵则有一定的限制。

1>　建立视图

CREATE VIEW <view_name> [(<column_name>[,<column_name>]...)]

AS <child_query>　

[WITH CHECK OPTION]

其中子查询可以是任意复杂的SELECT语句，但通常不允许含有ORDER BY子句和DISTINCT短语。

WITH CHECK OPTION表示对视图进行UPDATE、INSERT和DELETE操纵时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。

DBMS执行CREATE VIEW语句的结果只是把视图的定义存入数据字典，并不执行其中的SELECT语句。只是在对视图查询时，才按视图的定义从基本表中将数据查出。

组成视图的属性列或者全部省略或者全部指定。如果全部省略了视图的各个属性列名，则隐含该视图由子查询中SELECT子句目标列中的诸字段组成。但是在以下三种情况下必须明确指定视图的所以列名：

A) 某个目标列不是单纯的属性列，而是集函数或列表达式

B) 多表连接时选出了几个同名列作为视图的字段

C) 需要在视图中为某个列启用新的更合适的名字

2>　删除视图

DROP VIEW <view_name>;

视图删除后视图定义从数据字典中删除。但是由该视图导出的其它视图定义乃在数据字典中，不过该视图已失效。用户使用时会出错，要用DROP VIEW将它们一一删除。

数　据　查　询

数据库查询语法：

SELECT [ ALL | DISTINCT ] [ TOP n [ PERCENT ] [ WITH TIES ] ]

<dest_column> [AS column_name AS column_alias ]

[,<dest_column> [AS column_name AS column_alias ] ...

FROM table_name | view_name [table_alias]

[WHERE <condition_expression>]

[GROUP BY <column_name> [HAVING <condition_exp>]]

[ORDER BY <column_name> [ASC | DESC]];

说明：

ALL|DISTINCT: 指明查询结果集中表中重复的元组是否输出，ALL全部输出，DISTINCT去除重复的元组，保留其中一个元组。此默认为ALL。

GROUP BY 子句: 则指明按照列的值进行分组，该属性列值相等的元组为一个组。通常在每个组左右集函数。如果GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。

ORDER BY 子句: 则结果表还要按指定列的值升序或降序。　

常用的查询条件：

查询条件	谓词
比较	=, >, <, >=, <=, !=, <>, !>, !<, NOT + 上述比较运算符
确定范围	BETWEEN...AND..., NOT BETWEEN...AND...
确定集合	IN, NOT IN
字符匹配	LIKE, NOT LIKE
空值	IS NULL, IS NOT NULL
多重条件	AND, OR

注：谓词LIKE可以用来进行字符串的匹配，其一般格式：[NOT] LIKE '<匹配串>' [ESCAPE '<换码字符>'].其含义是查找指定的属性列值与<匹配串>相匹配的元组。通配符：％(任意长度字符，长度>=0)或_(任意单个字符)；ESCAPE：说明紧跟在换码字符后面的字符不在有通配符的属性，转义为普通的字符。

带有ANY或ALL谓词的子查询：

谓词	描述
> ANY	大于子查询结果中的某个值
> ALL	大于子查询结果中的所有值
< ANY	小于子查询结果中的某个值
< ALL	小于子查询结果中的所有值
>= ANY	大于等于子查询结果中的某个值
>= ALL	大于等于子查询结果中的所有值
<= ANY	小于等于子查询结果中的某个值
<= ALL	小于等于子查询结果中的所有值
= ANY	等于子查询结果中的某个值
= ALL	等于子查询结果中的任何一个值
!= (或<>) ANY	不等于子查询结果中的某个值
!= (或<>) ALL	不等于子查询结果中的任何一个值

ANY,ALL谓词与集函数及IN谓词的等价转换关系：

	=	<>或！＝	<	<=	>	>=
ANY	IN	--	<MAX	<=MAX	>MIN	>=MIN
ALL	--	NOT IN	<MIN	<=MIN	>MAX	>=MAX

带有EXISTS谓词的子查询：

　带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值"TRUE"或逻辑假值"FALSE".

集合查询：

SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可以进行集合操纵。集合操纵主要包括并操纵UNION、交操纵INTERSECT和差操纵MINUS。使用UNION将多个查询结果合并起来时，系统会自动去掉重复元组。注意，参加UNION操纵的各结果表的列数必须相同；对应项的数据类型也必须相同。

集合函数：

函数名称	描述
COUNT( [DISTINCT \| ALL] *)	统计元组个数
COUNT( [DISTINCT \| ALL] <column_name>)	统计一列中值的个数
SUM( [DISTINCT \| ALL] <column_name>))	计算一列值的总和（此列必须是数值型）
AVG( [DISTINCT \| ALL] <column_name>))	计算一列值的平均值
MAX( [DISTINCT \| ALL] <column_name>))	求一列值中的最大值
MIN( [DISTINCT \| ALL] <column_name>))	求一列值中的最小值

数　据　更　新

一） 插入数据

1>插入单个元组

INSERT

INTO <table_name> [(<column_name[,<column_name>]…>)]

Values (<value>[,<value>]…);

2> 插入子查询

INSERT

INTO <table_name> [<column_name >[,<column_name>]…]

Sub_Query

注：在表定义时说明了NOT NULL属性列不能取空值。否则会出错。

二） 修改数据

UPDATE <table_name>

SET <column_name>=expression [,<column_name>=expression]…

[WHERE <condition>];

注：如果没有指定条件的话，则修改的是表中的所有元组。

三） 删除数据

DELETE

FROM <table_name>

[WHERE <condition>];

DELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元组。如果省略WHERE子句，表示删除表中全部元组，但表的定义乃在数据字典中。也就是说，DELETE语句删除的是表中的数据，而不是关于表的定义。

数　据　控　制

由DBMS提供统一的数据控制功能是数据库系统的特点之一。SQL中数据控制功能包括事务管理功能和数据保护功能，即数据库的恢复、并发控制；数据库的安全性和完整性控制。

SQL语言定义完整性约束条件的功能主要体现在CREATE TABLE语句和ALTER TABLE中，可以在这些语句中定义码、取值唯一的列、不允许空值的列、外码（参照完整性）及其它一些约束条件。

某个用户对某类数据具有何种操纵权力是个政策问题而不是技术问题。数据库管理系统的功能是保证这些决定的执行。为此DBMS必须具有以下功能：

1> 把授权的决定告知系统，这是由SQL的GRANT和REVOKE语句来完成的。

2> 把授权的结果存入数据字典。

3> 当用户提出操纵请求时，根据授权情况进行检查，以决定是否执行操纵请求。

一）授权

GRANT <option> [,<option>]…

[ON <object_type> <object_name>]

TO <user> [,<user>]…

[WITH GRANT OPTION];

将对指定操作对象的指定操作权限授予指定的用户。

如果指定了WITH GRANT OPTION子句，则获得某种权限的用户还可以把这种权限再授予其它的用户。如果没有指定WITH GRANT OPTION子句，则获得某种权限的用户只能使用该权限，但不能传播该权限。

不同对象类型允许的操作权限

对象	对象类型	操作权限
属性	TABLE	SELECT,INSERT,UPDATE,DELETE,ALL PRIVILESGES
视图	TABLE	SELECT,INSERT,UPDATE,DELETE,ALL PRIVILESGES
基本表	TABLE	SELECT,INSERT,UPDATE,DELETE,ALTER,INDEX,ALL PRIVILESGES
数据库	DATABASE	CREATETAB