数据库原理及应用(五)SQL

SQL的功能分类：

        数据定义功能DDL（定义模式、内模式、外模式）

        数据操纵功能DML（增删改查等功能）

        数据控制功能DCL（完整性校验）

SQL的特点

        1.SQL具有自含式和嵌入式两种（自含式可以独立交互）

        2.SQL具有语言简洁，易学易用的特点

        3.SQL支持三级模式

                (1)全体基本表构成一个数据库的模式

                (2)视图和部分数据表构成了数据库的外模式

                (3)存储文件和他们的索引文件构成了内模式

 SQL的数据定义功能

	创建	删除	修改
基本表	create table	drop table	alter table
索引	create index	drop index
视图	create view	drop view
数据库	create database	drop database	alter database

         1.语言格式约定符号：<>中为实际语义；[ ]中为任选项；()或 | 为必选项；[,...n]表示可以多项重复

         2.一般语法规定：分隔符为 , 字符串使用 ' 进行区分

         3.SQL的特殊语法规定：以 ; 为结束符

基本表的定义与维护功能

        定义基本表

CREATE TABLE[ (库名)] (表名) (
    <列名> <数据类型> [<列级数据完整性约束条件>]
    [,...n]
    [,<表级完整性约束条件>][,...n]);

        SQL支持的数据类型

                数值型数据

SMALLINT	半长整数，二进制，15bits数据
INTEGER / INT	全长整数（四字长），31bits
DECIMAL(p[,q])	十进制，共p位
FLOAT	双字长浮点数

                字符型数据

CHARTER(n)  / CHAR(n)	长度为n的字符串
VARCHAR(n)	最大长度为n的变长字符串

                特殊数据类型

GRAPHIC(n)	长度为n的定长图形字符串
VARGRAPHIC(n)	变长图形字符串

                时间数据类型

DATE	YYYY-MM-DD
TIME	HH.MM.SS
TIMESTAMP	日期+时间

        约束条件

                (1)NOT NULL  /  NULL 约束

                (2)UNIQUE        唯一性约束

                (3)DEFAULT        缺省约束                DEFAULT <约束名> <缺省值> FOR <列名>

                (4)CHECK        检查约束        CONSTRAINT <约束名> CHECK (<约束表达式>)

                (5)表级完整性约束                UNIQUE约束

                                                              PRIMARY KEY约束

                                                CONTRAINT <约束名> PRIMARY KEY[CLUSTERED(<列组>)]

                                                        其中CLUSTERED短语为建立<列组>聚簇

                                                                FOREIGN KEY约束        外码约束

                                                CONTRAINT <约束名>FOREIGN KEY(<外码>)

                                                        REFERENCES<被参照表名>(<与外码对应的主码名>)

        例

                 修改基本表

ALTER TABLE<表名>
    [ADD(<新列名> <数据类型> [完整性约束][...,n])]
    [DROP<完整性约束名>]
    [MODIFY(<列名> <数据类型> [...,n])]

                 删除基本表

DROP TABLE <表名>

         索引的定义和维护功能

                索引的作用              1.加快数据查询的速度

                                                2.可以保证数据的唯一性

                                                3.加快连接速度

                建立索引的原则      1.索引的建立和维护由DBA和DBMS完成

                                                2.大表适当建立索引，小表不必建立索引

                                                3.同一个表不要建立过多索引

                                                4.根据查询要求建立索引

                建立索引的格式

CREATE [UNIQUE][CLUSTER] INDEX <索引名> ON <表名>(<列名> [<次序>],...)

                         表名是索引的基本表名字；<列名>后的次序可以选择ASC(升序)、DESC(降序)，缺省为ASC

                 删除索引

DROP INDEX <索引名>

         视图的定义和维护

                视图的优点：          1.简化用户的操作

                                                2.使用户可以以不同的方式看待同一数据

                                                3.对数据库的重构提供一定程度的逻辑独立性

                                                4.对机密数据提供安全保护

                 视图定义的格式

CREATE VIEW <视图名> [(列组名)] AS<子查询>  [WITH CHECK OPTION]

                         WITH CHECK OPTION表示在视图中进行update、insert、delete时，要保证操作的数据满足视图定义中的位于条件（where子语句的条件）

                删除视图

DROP VIEW  <视图名>

                 视图的查询和维护

                        不支持以下几种视图的更新

                                1.由两个以上基本表导出的视图

                                2.视图中的字段来自字段表达函数

                                3.有分组语句或使用DISTINCT语句

                                4.有嵌套查询

                                5.定义不允许更新

        查询功能

                SELECT语句

SELECT <目标列组>
FROM <数据源>
[WHERE <元组条件>]
[GROUP BY<分组列> [HAVING <组选择条件>]]
[ORDER BY<排序列1><排序要求1> [...,n]]

                 SQL常用逻辑操作符

[NOT] IN	[不]在其中	(字段) IN (<数据表子查询>)
ANY	任意一个	(字段) (比较符) ANY (数据表子查询)
ALL	全部	(字段) (比较符) ALL (数据表子查询)
EXISTS	[不]存在	EXISTS (<子查询>)
[NOT] BETWEEN ...AND...	[不]在范围	(字段) [NOT] BETWEEN 小值 AND大值
IS [NOT] NULL
[NOT] LIKE
AND
OR
NOT

                 组合查询

                        1.UNION：并查询，将两个查询的结果合并并去重

                        2.MINUS：差查询，查询1取得的结果（查询2中没有）

                        3.INTERSECT：交查询，取两个查询共有的元组

                其他操作符

                        * 或者 <表名> *        取全部字段

                        DISTINCT：去掉重复值

                        外部连接：左外部连接*=        右外部连接=*

 

                 SQL函数查询

                        AVG(表达式),SUM(表达式),MIN(表达式),MAX(表达式),COUNT(*|<字段>)

         数据更新功能

                插入

INSERT 
INTO <表名> [(<属性列1>[,<属性列2>]...)]
VALUES (<常量1>[,<常量2>...])

                 修改

UPDATE <表名>
SET <列名> = <表达式> [,<列名> = <表达式>][,...n]
[WHERE <条件>];

         数据删除功能

DELETE
FROM <表名>
[WHERE <条件>];

嵌入式SQL

        应该注意的问题

                1.SQL和主语言的配合问题

                2.合理选择主预言

        必须解决的问题

                1.如何区别SQL和主语言

                2.使数据库的工作单元与程序工作单元之间能够通讯

        不需要使用游标的SQL

                1.说明语句

EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL END DECLARE SECTION

                2.数据定义和数据控制语句

                3.查询结果为单记录的查询语句

                4.插入语句和某些删除、修改语句

        不用游标的查询语句

EXEC SQL SELECT [ALLDISTINCT] <目标列表达式> [,...n]
    INTO <主变量> [<指示变量>][,...n]
    FROM   <表名或视图名> [,...n]
    [WHERE <条件表达式>];

                语句开始时必须加EXEC SQL前缀；查询结果集只有一条记录，SELECT不必有排序和分组语句

        不用游标的维护语句

                1.删除语句

                2.修改语句

                3.插入语句

        使用游标的SQL

                1.定义游标命令(DECLARE)

EXEC SQL DECLARE <游标名> CURSOR
    FOR<子查询>
    [FOR UPDATE OF<字段名1>[,...n]];

                2.打开游标命令

EXEC SQL OPEN<游标名>

                3.关闭游标命令

EXEC SQL CLOSE<游标名>

 

 SQL的数据控制功能

        数据控制方法：1.授权定义        2.存权处理        3.查权处理

        操作权限：      1.隐含特权(系统内置权限，与用户身份有关)       

                                2.系统特权(语句特权，允许用户在数据库内部实施管理)

                                3.对象特权(对数据库中的表、视图、存储过程的操作权限)

         数据操作权限设置

                1.授权语句

GRANT <系统特权组> To<用户组> |PUBLIC--PUBLIC指所有用户
[WITH GRANT OPTION];    --指可以转授权

                         授权给特殊对象

GRANT ALL PRIVILIGES <对象特权组> ON <对象名> 
TO <用户组> | PUBLIC
[WITH GRANT OPTION];    

                 2.收权语句

REVOKE <权限组> | AALL PRIVILIGES [ON <对象名>]
FROM <用户组名> |PUBLIC

                 3.拒绝访问语句

DENY ALL [PRIVILIGES] <权限组> [ON <对象名> TO <用户组> | PUBLIC]