关系数据库与sql语言(不同的数据库管理系统支持不同的数据模型)

SQL（Structured Query Language）语言包括数据查询（Query）、数据操纵（manipulation）、数据定义（definition）、数据控制功能，是一种通用强大的关系数据库标准语言

一、SQL数据库体系结构

　　　SQL主要标准有3个关系数据库与sql语言：ＡＮＳＩ（美国国家标准机构）ＳＱＬ；对ＡＮＳＩ　ＳＱＬ进行修改后在１９９２年采用标准ＳＱＬ－９２或ＳＱＬ２；最近ＳＱＬ９９标准，也叫ＳＱＬ３标准

1、SQL的特点

（1）综合统一关系数据库与sql语言：非关系模型的数据语言分为模式定义语言和数据操纵语言，其缺点是修改模式的时候，必须停止现有数据库的运行，转储数据，修改模式编译后再重新装数据库。SQL集数据定义、数据操纵和数据控制于一体，可以独立完成数据库生命周期的所有活动

（2）高度非过程化：非关系数据库模型的数据操作是面向过程的，若要完成某项请求，必须指定存储路径；而SQL语言是高度非过程化语言，当进行数据操作时，只要指出“做什么”，无须指出“怎么做”，存储路径对用户来说是透明的，提高了数据的独立性

（3）面向 *** 的操作方式：非关系型数据模式是采用面向记录的操作方式，操作对象是一条记录。而SQL语言采用的是面向 *** 的操作方式，其操作对象、查找结果可以是元组的 ***

4）两种访问方式：用户可以使用终端输入SQL命令，也可以使用高级语言程序访问

（5）语言简洁、简单易学、功能强大:完成核心概念只需要9个动词，如下所示

【1】数据查询：select

【2】数据定义：create、drop、alter

【3】数据操纵：insert、update、delete

【4】数据控制：grant、revork

2、SQL支持三级模式结构

SQL语言支持关系数据库三级模式结构：视图对应外模式、基本表对应模式、存储文件对应内模式

二、SQL的基本组成

SQL有以下部分组成

1、数据定义语言

　　SQL ＤＤＬ提供定义关系模式和视图、删除关系和视图、修改关系模式的定义

２、交互式数据操纵语言

　　　ＳＱＬ　ＤＭＬ提供增删改查命令

３、嵌入式ＳＱＬ和动态ＳＱＬ

　　　ＳＱＬ可以嵌入高级语言Ｊａｖａ、Ｃ等

４、完整性（integrity）

SQL DDL包括定义数据库中的数据必须满足完整性约束条件的命令，对于破坏完整性约束条件的更新将被禁止

5、事务控制

6、权限管理

三、SQL数据定义

基本表和视图都是表，所不同的是基本表是实际存储在数据库中的表，视图是虚表，是从基本表表或其它视图导出的表。数据库只存放视图的定义，不存放视图的数据。在用户看来基本表和视图都是表。一个表可以有若干个索引，索引页存储在文件中

SQL的数据定义包括对表、视图、索引的创建和删除

1、创建表

语法：

列级完整性约束条件有NULL（空）和UNIQUE（取值唯一），例如：NOT NULL UNIQUE表示取值唯一，不能取空值

示例：

建立个供应商、零件数据库。其中，“供应商”表S（sno,sname,status,city）分别表示供应商代码、供应商名、供应商状态和供应商所在城市。“零件”表（pno,pname,color,weight,city）表示零件号、零件名、颜色、重量及产地。数据库要满足以下要求：

（1）供应商代码不能为空，且值是唯一的，供应商名也是唯一的

（2）零件号不能为空，且值是唯一的；零件名不能为空

（3）一个供应商可以供应多个零件，而一个零件可以由多个供应商供应

分析：根据题意，供应商和零件分别要建立一个关系模式。供应商和零件之间是一个多对多的联系，在关系型数据库中，多对多联系必须生成一个关系模式，而关系模式的码是该联系两端实体的码加上联系的属性构成的，若联系名为SP，那么关系模式为SP（sno,pno,qty），其中qty表示零件的数量

有上所述，建立一个供应商、零件的数据库如下：

create table S(

sno char(5) NOT NULL UNIQUE,

sname char(30) UNIQUE,

status char(8),

city char(20),

primary key(sno)

)

create table P(

pno char(6) ,

pname char(30) NOT NULL,

color char(8),

weight NUMERIC(6,2),

city char(20),

primary key(pno)

)

create table SP(

sno char(5) ,

pno char(6) ,

status char(8),

qty NUMERIC(9),

primary key(sno,pno),

foreign key(sno) references S(sno),

foreign key(pno) references P(pno)

)

从上述定义可以看出，sno char(5) NOT NULL UNIQUE语句定义了sno的列级完整性约束条件，取值唯一，不能取空值

说明：

（1）primary key(sno)已经定义了主键sno，所以sno char(5) not null unique语句中的not null unique可以省略

（2）foreign key(sno) references S(sno)定义了在SP关系中sno的外键，其取值必须来自S关系的sno域。同理，在sp关系中pno也定义为外键

2、修改和删除表

（1）修改表

—alter table <表名>

—[ add <新列名> <数据类型> [完整性约束条件]]

—[ drop <完整性约束名> ]

—[ modify <列名> <数据类型>]

—例如：向供应商表S增加 zap—“邮政编码”

—Alter table S add zap char(6)

—又如，将status字段改为整型

—Alter table S modify status int

（2）删除表

语法： drop table <表名>

例如： drop table student

3、索引建立与删除

索引使数据库程序无须对整个表进行扫描，就可以找到所需要的数据。数据库的索引是某个表中一列或若干列值的 *** 和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用如下：

（1）通过创建索引，可以保证数据记录的唯一性

（2）加快数据检索速度

（3）可以加速表和表之间的连接，这一点在实现引用完整性方面很有意义

（4）在使用order by和group by子句中进行检索时，可以显著减少查询中分组和排序长得时间

（5）使用索引可以在检索数据过程中使用优化隐藏器，提高系统性能

索引分为聚集索引和非聚集索引。聚集索引是指索引表中索引项与表中记录的物理顺序一致的索引

【1】创建索引

语法：

—CREATE [UNIQUE] [CLUSTER] INDEX <索引名>

—ON <表名>(

— <列名>[<次序>],

— <列名>[<次序>],

— ...

—)

—参数说明：

—次序：可选ASC(升序)、DSC(降序)，默认ASC

—UNIQUE：表明此索引的每一个索引值只对应唯一的数据记录

—CLUSTER：表明要建立的索引是聚集索引，即索引项的顺序与表中记录的物理顺序一致

示例：假设供应商销售数据库中有供应商S、零件P、工程项目J、供销情况SPJ关系，希望建立4个索引。其中，供应商Ｓ中sno按升序建立索引；零件P中的pno按升序建立索引；工程项目J中jno按是升序建立索引；供销商SPJ中sno按升序、pno按降序、jno按升序建立索引

解：

—CREATE UNIQUE INDEX S-SNO ON S(sno)

—CREATE UNIQUE INDEX P-PNO ON P(sno)

—CREATE UNIQUE INDEX J-JNO ON J(jno)

—CREATE UNIQUE INDEX SPJ-NO ON SPJ(sno ASC,pno DESC,jno ASC)

—

【2】删除索引

语法：

drop index <索引名>

4、视图创建与删除

视图是从一个或多个基表或视图中导出的表，其结构和数据是建立在对表的查询基础上的。和真实表一样，视图也包括几个被定义的数据列和多个数据行，但从本质上讲，这些数据列和数据行来源于其所引用的 表。因此，视图不是真实存在的表，而是一个虚拟表，视图所对应的数据并不实际的以视图结构存储在数据库中，而是存储在视图所引用的表中，使用视图的优点如下：

（1）可以使用视图集中数据、简化和定制不同用户对数据库的不同数据要求

（2）使用视图可以屏蔽数据的复杂性，用户不必了解数据库的结构就可以方便的使用和管理 数据，简化数据权限管理和重新组织数据以便输出到其它应用程序中

（3）视图可以使用户只关心他感兴趣的某些特定数据和他们所负责的特定任务，而那些不需要或者无用的数据则不在视图中显示

（4）视图大大简化了用户对数据的操作

（5）视图可以让不同用户以不同方式看到不同或者相同的数据集

（6）在某些情况下，由于表中数据量太大，因此在设计表时常常将表进行水平或者垂直分割，以免表的结构变化对应用程序产生不良影响

（7）视图提供一种简单而有效地安全机制

【1】视图的创建

语法：

create view 视图名 (列表名)

as select 查询子句

[with check option]

（1）子查询可以是任意select语句，但通常不包含order by子句和distinct短语

（2）with check option表示对update、insert、delete操作时保证更新、插入、删除时的行满足视图定义中的谓词条件（即查询中的条件表达式）

（3）组成视图的属性列名或者全部省略或者全部指定。如果省略属性列名，则隐含该视图由select子查询目标列的主属性组成

示例：若学生关系模式student(sno,sname,sage,sex,email,tel)，建立“计算机系”（CS表示计算机系）学生的视图，并要求进行修改、插入操作时保证该视图只有计算机系学生

—create view cs_student

—as select sno,sname,sage,sex from student

—where sd='cs'

—with check option

—由于cs_student视图使用了with check option子句，因此，对该视图进行修改、插入操作时DBMS会自动加上sd = ‘cs’的条件，保证该视图只有计算机系的学生

【2】删除视图

语法：

drop view <视图名>

4、SQL数据查询

SQL的数据操纵功能包括增（insert）、删（delete）、改（update）、查（select）

（1）select基本结构

语法格式：

—SELECT [ ALL|DISTINCT] <目标列表达式1>,<目标列表达式2>,<目标列表达式1> ...

—FROM <表1或视图1>,<表2或视图2>,<表3或视图3> ...

—[WHERE <条件表达式>]

—[GROUP BY <列名1> [HAVING <条件表达式>]]

—[ORDER BY <列名2> [ASC|DESC]]

—

—SQL查询中的子句顺序为select、from、where、group by、having和order by。其中，

Select、from是必须的，having条件子句只能与group by搭配使用

【1】select子句对应的是关系代数中的投影运算，用来列出查询结果中的属性。其输出可以是列名、表达式、集函数（AVG、COUNT、MAX、MIN、SUM），DISTINCT选项可以保证查询的结果集中不存在重复元组

【2】from子句对应的是关系代数中的笛卡尔积，它列出的是表达式求值过程中需要扫描的关系，即在from子句中出现多个基本表和视图时，系统首先执行笛卡尔积操作

【3】where子句对应的是关系代数中的选择谓词。Where子句的条件表达式中可以使用运算符如图所示：

【2】简单查询

SQL最简单的查询是找出关系中满足特定条件的元组。这些查询与关系代数中的选择操作类似。简单查询只需要使用3个保留字，即select、from、where

示例-1：查询学生—课程数据库中计算机系CS学生的学号、姓名、年龄

Select sno,sname,age from S where SD = ‘CS’

（3）、连接查询

若查询涉及到两个以上的表，则称为连接查询

示例-1：

检索选修了课程号“C1”的学生的学号和姓名，可以用连接查询

Select sno,sname from S,SC where s.sno=sc.sno and sc.cno = ‘C1’

示例-2：

检索选修了课程名“MS”的学生的学号和姓名

Select sno,sname from S,SC,C where

S.Sno = SC.sno and sc.cno = C.cno and C.cname = ‘MS’

示例-3：

检索至少选修了课程号“C1”和“C3”的学生的学号

Select sno from SC t1 ,sc t2 where t1.sno = t2.sno and t1.cno =‘C1 ’

And t2.cno = ‘C3’

（4）子查询与聚集函数

【1】子查询

子查询也叫嵌套查询

示例：检索选修了课程名“MS”的学生的学号和姓名

Select sno,sname from S where

Sno in (select sno from SC where cno in (select cno from C

where cname = ‘MS’))

【2】聚集函数

聚集函数是一个值的 *** 为输入，返回单个值的函数

使用ANY和ALL谓词必须同时使用比较运算符，用聚集函数实现子查询通常比直接用ALL或ANY效率高

示例-1：查询课程C1的最高分和最低分以及最高分和最低分之间的差距

Select max(G) ,min(G),max(G) – min(G) from SC where cno = ‘C1’

示例-2：查询其它系比计算机系CS所有学生年龄都要小的学生的姓名及年龄

*** 1：

Select sname ,age from S where age <

ALL(select age from S where SD = ‘CS’)

*** 2：

Select sname , age from S where

Age < (select min(age) from S where SD = ‘CS’ ) and SD <> ‘CS’

示例-3：查询其它系比计算机系某一学生年龄小的学生的姓名和年龄

*** -1：

Select sname,age from S where age< any(select age from S where SD=‘CS’)

And SD <> ‘CS’

*** -2：

Select sname,age from S where age<(select max(age) from S where SD=‘CS’)

And sd<>’CS’

（5）分组查询

【1】group by子句

在where子句后面加上group by子句可以对元组进行分组，保留字group by 后面跟着一个分组属性列表。最简单的情况是from子句后面只有一个关系，根据分组属性对它的元组进行分组。Select子句中使用的聚集操作符仅用在每个分组上

示例：学生数据库中的SC关系，查询每个学生的平均成绩

Select sno,AVG(grade) from SC group by sno

该语句是将SC关系的元组重新组织并进行分组，使得不同学号的元组分别被组织在一起，求出各个学生的平均值并输出

【2】having子句

加入元组在分组前按照某种方式加上限制，使得不需要的分组为空，在group by子句后面跟一个having子句即可

注意：当元组含有空值时

【1】空值在任何聚集操作中被忽视。它对求和、求平均值和计数都没有影响。它也不能是某列的最大值和最小值。例如，count(*)是某个关系中所有元组数目之和，但count(A)却是A属性非空的元组个数之和

【2】NULL值又可以在分组属性中看作是一个一般的值。例如在select A ,AVG(B) from 中，当A的属性值为空时，就会统计 A=NULL的所有元组中B的均值

示例-1：

供应商数据库中的S、P、J、SJP关系，查询某工程至少用了三家供应商（包含三家）供应的零件的平均数量，并按工程号的降序排列

Select jno,AVG(qty) from SPJ group by jno

Having count(DISTINCT(sno)) > 2

Order by jno desc

根据题意“某工程至少用了三家供应商（包含三家）供应商的零件”，应该按照工程号分组，而且应该加上条件---供应商的数目。但需要注意的是，一个工程项目可能用了同一个供应商的多种零件，因此，在统计供应商数的时候需要加上DISTINCT，以避免重复统计导致错误的结果。例如，按工程号jno=‘J1’分组，其结果如下。如果不加DISTINCT，统计结果为7；而加了DISTINCT，统计结果为5

（6）更名运算

SQL提供了可为关系和属性重新命名的机制，这是通过使用具有如下形式的AS子句来实现的：

Oid-name as new-name

示例：查询计算机系选修C1课程的学生的姓名sname和成绩grade

Select sname ,grade from students as s, SC as y

Where x.sno = y.sno and y.cno = ‘C1’

（7）字符串操作

对于字符串进行的最常用的操作是使用操作符LIKE的模式匹配。使用两个特殊的字符来描述模式：“%”匹配任意字符串；“_”匹配任意一个字符。模式是大小写敏感的。例如：Marry%匹配任何以Marry开头的字符串；%jerry%匹配任何包含jerry的字符串，例如：hellojerryme等

“_”匹配只含两个字符的字符串；“_%”匹配至少包含两个字符的字符串

示例-1：

学生关系模式为（sno,sname,sex,sd,age,add），其中sno为学号、sname为姓名、sex为性别、sd为所在系、age为年龄、add为住址，请查询：

【1】住址包含“科技路”的学生姓名

【2】名字为“晓军”的学生的姓名、年龄、所在系

解：

【1】select sname from S where add like ‘%科技路%’

【2】select sname,age,sd from S where sname like ‘_晓军’

为了使模式中包含特殊模式字符（即%和_），在SQL中允许使用ESCAPE关键词来定义转移符。转衣符仅靠特殊字符，并放在它的前面，表示特殊字符被当成普通字符。例如，在like比较中使用ESCAPE关键词来定义转衣符时，使用反斜杠“\”作为转衣符

like ‘ab\%cd%’ESCAPE ‘\’ ,匹配所有以ab%cd开头的字符串

like ‘ab\\cd%’ESCAPE ‘\’ ,匹配所有以ab\cd开头的字符串

（8）视图的查询

示例-1：建立“计算机系”（CS表示计算机系）学生的视图，并要求进行修改、插入操作时保证该视图只有计算机系的学生

Create view cs_student as select sno,sname,sage,sex from student

Where sd = ‘CS’ with check option;

此时，查询年龄小于20岁的学生的学号及年龄的SQL语句如下：

Select sno,age from cs_student where sd=‘CS’ and age < 20

系统执行该语句时，通常先将其转化为等价的对基本表的查询，然后执行查询语句。即当查询视图表时，系统先从数据字典中取出该视图的定义，然后将定义的查询语句和对该视图的查询语句结合起来，形成一个修正的查询语句。对上例子修正之后的查询语句为：

Select sno,age from student where sd = ‘cs ’ and age <20;

5、SQL数据更新

（1）新增

语法：

INSERT INTO 基本表（字段1,字段2,字段3...） values(值1，值2，值3...)

INSERT INTO 基本表名（列表名） SELECT 查询语句

示例-1：将学号为3002、课程号为C4、成绩为98的元组插入SC关系中

Insert into SC values(‘3002’,’C4’,98)

示例-2：创建一个新的视图v_employees，该视图基于表employees创建

Create view_employees(number,name,age,sex,salary)

as

select number,name,age,sex,salary

from employees

where name = ‘张三’

通过执行以下语句使用 v_employees视图向表empolyees中添加一条新的记录

Insert into v_employees values(100,’lily’,21,’F’,20000)

（2）删除

语法：DELETE FROM 基本表名 [ WHERE 条件表达式 ]

示例：删除employees中姓名我“王五”的记录

Delete from employees where name = ‘王五’

（3）修改

语法：

—UPDATE 基本表名

—SET 列1 = 值1，列2 = 值2，列3 = 值3，...

—[ WHERE 条件表达式 ]

—示例-1：将教师工资增加5%

—Update teachers set salary = salary*1.05

—示例-2：将教师工资少于1000的增加5%工资

—Update teachers set salary = salary*1.05 where salary<1000

—示例-3：创建个基于表employees的视图v_employees，然后通过该视图修改表employees中的记录

—

—使用视图可以更新数据记录，但应该注意的是更新的是数据库中的基表

—Create view v_employees as select * from employees

—

—Update v_employees set name = ‘lily’ where name = ‘lisi’

6、SQL的访问控制

数据控制是控制用户对数据存储的权利，是由DBA决定的。但是，某个用户对某类数据具有何种权利，是个政策问题而不是技术问题。DBMS的功能就是保证这些决定的执行。因此，DBMS数据控制应具有以下功能：

（1）通过GRANT和REVOKE将授权通知系统，并存入数据字典

（2）当用户提出请求时，根据授权情况检查是否执行操作请求

SQL标准包括delete、insert、select、update权限。Select权限对应于read权限，SQL还包括REFERENCES权限，用来限制用户在创建关系时定义外码的能力

授权语句的格式：

—GRANT <权限1>, <权限2>, <权限3> ...

—[ ON <对象类型> <对象名> ]

—TO <用户1> ,<用户2> ,<用户3> ...

—[ WITH GRANT OPTION ]

—

—不同类型的操作对象有不同的操作权限，常见的操作权限如下表所示：

说明如下：

【1】public：接受权限的用户可以是单个或者多个具体的用户，public 参数可以将权限赋给所有用户

【2】with grant option：若指定了此子句，那么获得了权限的用户还可以将权限赋给其它用户

示例：如果用户要求给数据库SPJ中的供应商S、零件P、项目J表赋予各种权限

【1】将对供应商S、零件P、项目J的所有操作权限赋给用户user1、user2

Grant all privileges on table s,p,j to user1,user2

【2】将对供应商S的插入权限赋给用户user1，并允许将此权限赋给其它用户

Grant insert on table s to user1 with grant option

【3】DBA把数据库SPJ中建立表的权限赋给用户user1，其授权语句如下：

Grant createtab on database spj to user1

收回权限语句格式

—

—REVOKE <权限1>, <权限2>, <权限3> ...

—[ ON <对象类型> <对象名> ]

—FROM <用户1>,<用户2>,<用户3>,...

示例：要求回收用户对数据库SPJ中供应商S、零件P、项目J表的操作权限

【1】将用户user1、user2对供应商S、零件P、项目J的所有操作权限收回

Revoke all privileges on table s,p,j from user1,user2

【2】将所有用户对供应商S的所有查询权限收回

Revoke select on table s from public

【3】将user1用户对供应商S的供应商编号sno的修改权限收回

Revoke update(sno) on table S from user1

（7）嵌入SQL

嵌入SQL包括嵌入到高级语言、存储过程、游标等