CIMS系统导论_chapter3_3

1、结构化查询语言SQL(Structured Query Language)是一种关系数据库的标准语言关系数据库的标准语言用于存取数据以及查询、更新和管理关系数据库系统 高度非过程化。只要指出“做什么”，无须指明“怎么做”应用广泛。被绝大多数商品化关系数据库系统采用，如Oracle，SQL Server，DB2，Sybase，Informix等 n基本术语数据库技术的术语关系模型术语SQL术语记录类型文件记录属性、字段、数据项关系模式关系、实例（表）元组属性基本表基本表、表格行列学号姓名性别出生日期信息技术010329张晖男81.10.1589010139李小凡男80.12.3078010117

2、周秋红男80.05.1890010207张志平女80.11.3088学生（学号，姓名，性别，出生日期，信息技术）学生（学号，姓名，性别，出生日期，信息技术） 应用应用A A 应用应用B B 应用应用C C 应用应用D D 应用应用E E 外模式外模式1 1 外模式外模式2 2 外模式外模式3 3 模式模式 内模式内模式数据库数据库外模式（也称子模式）外模式（也称子模式）：数：数据库用户使用的据库用户使用的局部局部数据的数据的逻辑结构和特征的描述，反逻辑结构和特征的描述，反映了不同的用户的应用需求。映了不同的用户的应用需求。模式（模式（Schema）：数据库中数据库中全体全体数据的逻辑结构和特征

3、数据的逻辑结构和特征的描述，综合了所有用户的的描述，综合了所有用户的需求。需求。内模式（也称存储模式）内模式（也称存储模式）：是数据物理结构和存储方式是数据物理结构和存储方式的描述。的描述。2. SQL数据库的体系结构数据库的体系结构特征：nSQL用户可以是终端用户，也可以是应用程序。SQL语句可嵌入在宿主语言的程序中使用。n用户可以用SQL语句对视图和基本表进行查询等操作。在用户看来，视图和基本表是一样的，都是关系(即表格)。 n一个表可以是一个基本表，也可以是一个视图，基本表是实际存储在数据库中的表。视图是从基本表或其他视图中导出的表，不独立存储在数据库中，是一个虚表虚表。 n一个基本表可

4、以跨一个或多个存储文件，一个存储文件也可存放一个或多个基本表。每个存储文件与外部存储器上一个物理文件对应。用户1用户2视图1视图2基本表2基本表1基本表3基本表4存储文件1存储文件2外模式外模式模模 式式内模式内模式实现四类功能：数据定义 (DDL, Data Definition Language)用于定义SQL模式、基本表、视图和索引。数据操纵 (DML, Data Manipulation Language)数据查询数据更新：插入、删除和修改三种操作。数据控制 (DCL, Data Control Language)包括对基本表和视图的授权，完整性规则的描述，事务控制语句等。嵌入式SQL

5、使用涉及到SQL语句嵌入在宿主语言程序中的使用规则。SQL功能功能动动 词词数据定义创建CREATE撤消DROP修改ALTER数据操纵查询SELECT插入INSERT修改UPDATE删除DELETE数据控制授权GRANT收权REVOKE4. SQL的数据定义的数据定义(DDL)nSQL的数据定义语句操作对象操作对象操作方式操作方式创创 建建撤撤 消消修修 改改模式(SCHEMA) CREATE SCHEMADROP SCHEMA表(TABLE)CREATE TABLEDROP TABLEALTER TABLE视图(VIEW)CREATE VIEWDROP VIEW索引(INDEX)CREATE

6、 INDEXDROP INDEX句法：CREATE TABLE （ ， ， ）；：所要定义的基本表的名字：组成该表的各个属性（列）：涉及相应属性列的完整性约束条件：涉及一个或多个属性列的完整性约束条件 数据库的完整性是指数据库中数据的正确性与相容性正确性与相容性。SQL语言定义完整性约束条件主码约束：主键子句PRIMARY KEY参照完整性约束：外键子句FOREIGN KEY检查子句：CHECK唯一性约束：UNIQUE非空值约束：NOT NULL表级表级列级列级创建四个表“供应商关系”表S“零件关系”表P“工程项目关系”表J“供应情况关系”表SPJ创建一个“供应商关系”表S ，它有四个属性，分

7、别是供应商号(SNO)、供应商名(SNAME)、状态(STATUS)和地址(ADDR)，属性的类型都是字符型，长度分别是4、20、10和20个字符。规定供应商号和供应商名不能取空值。主键是供应商编号SNO。(SNO CHAR(4) ，SNAME CHAR(20) ，STATUS CHAR(10)，ADDR CHAR(20)，PRIMARY KEY (SNO)； CREATE TABLE SNOT NULLNOT NULL创建一个“供应情况关系”表SPJ五个属性：供应商号(SNO)，字符型(4)，非空零件号(PNO)，字符型(4)，非空工程项目号(JNO)，字符型(4)，非空单价(PRICE)，

8、定点数(7位数字，小数点后2位数字)供应数量(QTY)，短整数主键是(SNO，PNO，JNO)定义三个外键：外键SNO和基本表S中SNO属性相对应外键PNO和基本表P中PNO属性相对应外键JNO和基本表J中JNO属性相对应使用一个检查子句，指出供应数量QTY在010000之间。 CREATE TABLE SPJ (SNO CHAR(4) NOT NULL,PNO CHAR(4) NOT NULL,JNO CHAR(4) NOT NULL,PRICE NUMERIC(7,2),QTY SMALLINT, PRIMARY KEY (SNO,PNO,JNO),FOREIGN KEY (SNO) RE

9、FERENCES S(SNO),FOREIGN KEY (PNO) REFERENCES P(PNO),FOREIGN KEY (JNO) REFERENCES J(JNO),CHECK(QTY BETWEEN 0 AND 10000)； 句法：ALTER TABLE ADD 完整性约束 DROP CASCADE|RESTRICT MODIFY ；：要修改的基本表ADD子句：增加新列和新的完整性约束条件DROP子句：删除指定的列MODIFY子句：用于修改列名和数据类型注意：新增加的属性不能定义为“NOT NULL”。基本表在增加一个属性后，原有元组在新增加的属性列上的值都被定义为空值(NULL

10、) DROP子句中的CASCADE方式表示：在基本表中删除某属性时，所有引用到该属性的视图和约束也要一起自动地被删除。而RESTRICT方式表示在没有视图或约束引用该属性时，才能在基本表中删除该属性，否则拒绝删除操作。 例3.2 在基本表S中增加一个电话号码(TELE)属性，可用下列语句：ALTER TABLE S ADD TELE CHAR(12)； 例3.3 在基本表S中删除状态(STATUS)属性，并且将引用该属性的所有视图和约束也一起删除，可用下列语句：ALTER TABLE S DROP STATUS CASCADE；视图的特点：虚表，是从一个或几个基本表（或视图）导出的表。数据库中

11、只存放视图的定义而不存放视图的数据，这些数据仍存放在导出视图的基本表中。基表中的数据发生变化，从视图中查询出的数据也随之改变。句法：CREATE VIEW ( ，) AS 注意：执行CREATE VIEW语句时只是把视图的定义存入数据字典，并不执行其中的SELECT语句。在对视图查询时，按视图的定义从基本表中将数据查出。对工程项目零件供应数据库中基本表S,P,J,SPJ，用户经常要用到有关项目使用零件情况信息：工程号(JNO)、工程项目名称(JNAME)、供应商号(SNO)、供应商名(SNAME)、零件号(PNO)、零件名(PNAME)、供应数量(QTY)等列的数据，那么可用下列语句建立视图：

12、CREATE VIEW JSP_NAME(JNO,JNAME,SNO,SNAME,PNO,PNAME,QTY) AS SELECT (J.JNO,JNAME,S.SNO,SNAME,P.PNO,PNAME,QTY) FROM S,P,J,SPJ WHERE S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO AND J.JNO=SPJ.JNO；数据查询SELECT数据更新插入INSERT修改UPDATE删除DELETE（1）语句格式SELECT ALL|DISTINCT , FROM , WHERE GROUP BY HAVING ORDER BY ASC|DESC ；SELECT

13、子句子句：指定要显示的属性列DISTINCT表示要在结果中去掉取值重复的行。要选择表中所有列，可用星号“”代替 FROM子句子句：指定查询对象(基本表或视图)WHERE子句子句：指定查询条件GROUP BY子句子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。HAVING短语短语：筛选出只有满足指定条件的组ORDER BY子句子句：对查询结果表按指定列值的升序(ASC)或降序(DESC)排序 例题3.9从供应情况关系表SPJ中，检索供应工程J1零件的供应商编号SNO。SELECT DISTINCT SNO FROM SPJ WHERE JNO=J1;查询

14、全体工程项目的详细信息SELECT * FROM J;从工程项目关系表J中，查询没有正余额的工程编号、名称及城市，结果按工程编号升序排列。SELECT JNO,JNAME,CITY FROM J WHERE BALANCE IS NULL OR BALANCE3 ORDER BY 2,3 DESC;首先，根据WHERE子句的条件，对关系J和SPJ执行联接操作找出满足条件为“泉州”的工程的元组；再按工程号JNO的值对泉州的工程进行分组，将JNO列的值相同的元组分为一组；对每一个分组进行合计操作；并按HAVING子句的条件对产生的元组进行选择，消除只使用三种以下零件的元组；最后，再对结果进行排序。

15、查询各工程项目购买零件的总金额。结果显示工程项目名和购买零件总金额，并按金额降序排列。SELECT JNAME,SUM(PRICE*QTY) AS SUM_PRICE FROM J,SPJ WHERE J.JNO= SPJ.JNO GROUP BY SPJ.JNO ORDER BY 2 DESC;采用基于角色的多级授权安全机制，所有对数据库的操作都需要更高一级的授权。任何级别的用户在使用数据库系统时，除了必须拥有的授权外，还必须提供正确的用户名和用户口令。在数据库系统中，数据库系统管理员DBA负责完成整个系统的管理工作，获得DBA授权的用户可以创建数据库、表等而成为这些数据库对象的拥有者。SQ

16、L的数据控制功能语句有两条：授权语句(GRANT)：使某个用户具有某些权限收权语句(REVOKE)：收回已授给用户的权限。语句格式： GRANT ,. ON TO ,. WITH GRANT OPTION;GRANT功能：将对指定操作对象的指定操作权限授予指定的用户。 对象对象类型操 作 权 限 属性列TABLESELECT,INSERT,UPDATEDELETE,ALL PRIVILEGES 视图TABLESELECT,INSERT,UPDATEDELETE,ALL PRIVILEGES 基本表TABLESELECT,INSERT,UPDATEDELETE ALTER, INDEX,ALL PRIVILEGES 数据库DATABASE CREATETAB指定了WITH GRANT OPTION子句: 获得某种权限的用户还可以把这种权限再授予别的用户。没有指定WITH GRANT OPTION子句: 获得某种权限的用户只能使用该权限，不能传播该权限 例1 把查询Student表权限授给用户U1 GRANT SELECT ON TABLE Student TO U1;例2 把查询Student表和修改学生学号的权限授给用户U4 GRANT UPDATE(SNO),SELECT ON TABLE Student TO U4;例3 把对表