《{管理信息化CIMS集成制造系统}CIMS系统导论chapter33》由会员分享，可在线阅读，更多相关《{管理信息化CIMS集成制造系统}CIMS系统导论chapter33（36页珍藏版）》请在金锄头文库上搜索。
1、3.4 结构化查询语言SQL,1. 什么是SQL,结构化查询语言SQL(Structured Query Language)是一种关系数据库的标准语言 用于存取数据以及查询、更新和管理关系数据库系统 高度非过程化。只要指出“做什么”，无须指明“怎么做” 应用广泛。被绝大多数商品化关系数据库系统采用，如Oracle，SQL Server，DB2，Sybase，Informix等,数据库中的基本概念,基本术语,学生（学号，姓名，性别，出生日期，信息技术）,数据库系统的三级模式结构,外模式（也称子模式）：数据库用户使用的局部数据的逻辑结构和特征的描述，反映了不同的用户的应用需求。 模式（Schema
2、）：数据库中全体数据的逻辑结构和特征的描述，综合了所有用户的需求。 内模式（也称存储模式）：是数据物理结构和存储方式的描述。,2. SQL数据库的体系结构,特征： SQL用户可以是终端用户，也可以是应用程序。SQL语句可嵌入在宿主语言的程序中使用。 用户可以用SQL语句对视图和基本表进行查询等操作。在用户看来，视图和基本表是一样的，都是关系(即表格)。 一个表可以是一个基本表，也可以是一个视图，基本表是实际存储在数据库中的表。视图是从基本表或其他视图中导出的表，不独立存储在数据库中，是一个虚表。 一个基本表可以跨一个或多个存储文件，一个存储文件也可存放一个或多个基本表。每个存储文件与外部存储器
3、上一个物理文件对应。,用户1,用户2,视图1,视图2,基本表2,基本表1,基本表3,基本表4,存储文件1,存储文件2,外模式,模 式,内模式,华侨大学信息科学与工程学院,3. SQL语言的组成,实现四类功能： 数据定义 (DDL, Data Definition Language) 用于定义SQL模式、基本表、视图和索引。 数据操纵 (DML, Data Manipulation Language) 数据查询 数据更新：插入、删除和修改三种操作。 数据控制 (DCL, Data Control Language) 包括对基本表和视图的授权，完整性规则的描述，事务控制语句等。 嵌入式SQL使用
4、涉及到SQL语句嵌入在宿主语言程序中的使用规则。,SQL语言的动词,4. SQL的数据定义(DDL),SQL的数据定义语句,华侨大学信息科学与工程学院,（1）基本表的创建,句法： CREATE TABLE （ ， ， ）； ：所要定义的基本表的名字 ：组成该表的各个属性（列） ：涉及相应属性列的完整性约束条件 ：涉及一个或多个属性列的完整性约束条件,SQL的完整性约束,数据库的完整性是指数据库中数据的正确性与相容性。 SQL语言定义完整性约束条件 主码约束：主键子句PRIMARY KEY 参照完整性约束：外键子句FOREIGN KEY 检查子句：CHECK 唯一性约束：UNIQUE 非空值约束
5、：NOT NULL,表级,列级,例题3.1,创建四个表 “供应商关系”表S “零件关系”表P “工程项目关系”表J “供应情况关系”表SPJ,例题3.1 “供应商关系”表,创建一个“供应商关系”表S ，它有四个属性，分别是供应商号(SNO)、供应商名(SNAME)、状态(STATUS)和地址(ADDR)，属性的类型都是字符型，长度分别是4、20、10和20个字符。规定供应商号和供应商名不能取空值。主键是供应商编号SNO。 (SNO CHAR(4) ， SNAME CHAR(20) ， STATUS CHAR(10)， ADDR CHAR(20)， PRIMARY KEY (SNO)；,CREA
6、TE TABLE,S,NOT NULL,NOT NULL,例题3.1 “供应情况关系”表,创建一个“供应情况关系”表SPJ 五个属性： 供应商号(SNO)，字符型(4)，非空 零件号(PNO)，字符型(4)，非空 工程项目号(JNO)，字符型(4)，非空 单价(PRICE)，定点数(7位数字，小数点后2位数字) 供应数量(QTY)，短整数 主键是(SNO，PNO，JNO) 定义三个外键： 外键SNO和基本表S中SNO属性相对应 外键PNO和基本表P中PNO属性相对应 外键JNO和基本表J中JNO属性相对应 使用一个检查子句，指出供应数量QTY在010000之间。,CREATE TABLE SP
7、J (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) REFERENCES S(SNO), FOREIGN KEY (PNO) REFERENCES P(PNO), FOREIGN KEY (JNO) REFERENCES J(JNO), CHECK(QTY BETWEEN 0 AND 10000)；,（2）基本表的修改,句法： ALTER TABLE A
8、DD 完整性约束 DROP CASCADE|RESTRICT MODIFY ； ：要修改的基本表 ADD子句：增加新列和新的完整性约束条件 DROP子句：删除指定的列 MODIFY子句：用于修改列名和数据类型 注意： 新增加的属性不能定义为“NOT NULL”。基本表在增加一个属性后，原有元组在新增加的属性列上的值都被定义为空值(NULL) DROP子句中的CASCADE方式表示：在基本表中删除某属性时，所有引用到该属性的视图和约束也要一起自动地被删除。而RESTRICT方式表示在没有视图或约束引用该属性时，才能在基本表中删除该属性，否则拒绝删除操作。,例题,例3.2 在基本表S中增加一个电话
9、号码(TELE)属性，可用下列语句： ALTER TABLE S ADD TELE CHAR(12)； 例3.3 在基本表S中删除状态(STATUS)属性，并且将引用该属性的所有视图和约束也一起删除，可用下列语句： ALTER TABLE S DROP STATUS CASCADE；,（3）视图的创建,视图的特点： 虚表，是从一个或几个基本表（或视图）导出的表。 数据库中只存放视图的定义而不存放视图的数据，这些数据仍存放在导出视图的基本表中。 基表中的数据发生变化，从视图中查询出的数据也随之改变。,（3）视图的创建,句法： CREATE VIEW ( ，) AS 注意： 执行CREATE VI
10、EW语句时只是把视图的定义存入数据字典，并不执行其中的SELECT语句。在对视图查询时，按视图的定义从基本表中将数据查出。,例题3.5,对工程项目零件供应数据库中基本表S,P,J,SPJ，用户经常要用到有关项目使用零件情况信息：工程号(JNO)、工程项目名称(JNAME)、供应商号(SNO)、供应商名(SNAME)、零件号(PNO)、零件名(PNAME)、供应数量(QTY)等列的数据，那么可用下列语句建立视图： CREATE VIEW JSP_NAME(JNO,JNAME,SNO,SNAME,PNO,PNAME,QTY) AS SELECT (J.JNO,JNAME,S.SNO,SNAME,P
11、.PNO,PNAME,QTY) FROM S,P,J,SPJ WHERE S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO AND J.JNO=SPJ.JNO；,5. SQL的数据操纵,数据查询 SELECT 数据更新 插入INSERT 修改UPDATE 删除DELETE,6. SQL的数据查询,（1）语句格式 SELECT ALL|DISTINCT , FROM , WHERE GROUP BY HAVING ORDER BY ASC|DESC ；,SELECT子句：指定要显示的属性列 DISTINCT表示要在结果中去掉取值重复的行。 要选择表中所有列，可用星号“”代替 FRO
12、M子句：指定查询对象(基本表或视图) WHERE子句：指定查询条件 GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。 HAVING短语：筛选出只有满足指定条件的组 ORDER BY子句：对查询结果表按指定列值的升序(ASC)或降序(DESC)排序,（2）单表查询,例题3.9 从供应情况关系表SPJ中，检索供应工程J1零件的供应商编号SNO。 SELECT DISTINCT SNO FROM SPJ WHERE JNO=J1; 查询全体工程项目的详细信息 SELECT * FROM J; 从工程项目关系表J中，查询没有正余额的工程编号、名
13、称及城市，结果按工程编号升序排列。 SELECT JNO,JNAME,CITY FROM J WHERE BALANCE IS NULL OR BALANCE=0 ORDER BY JNO；,从供应情况关系表SPJ中，求使用零件数量在100与1000之间的工程项目编号，零件号和数量。 SELECT JNO,PNO,QTY FROM SPJ WHERE QTY BETWEEN 100 AND 1000； 从供应商关系表S中，查询泉州的供应商名称，假设供应商关系的ADDR列的值都以城市名开头。 SELECT SNAME FROM S WHERE ADDR LIKE 泉州%,（3）多表查询,例题3.
14、10 求使用了P3零件的工程项目名称。 SELECT DISTINCT JNAME FROM J,SPJ WHERE SPJ.JNO=J.JNO AND PNO=P3; 这个SELECT语句执行时，要对关系SPJ和J做联接操作。执行联接操作的表示方法是FROM子句后面写上执行联接操作的表名SPJ和J，再在WHERE子句中写上联接的条件SPJ. JNO=J.JNO。 求为工程项目J1供应红色零件的供应商号SNO。 SELECT DISTINCT SNO FROM SPJ,P WHERE SPJ.PNO=P.PNO AND JNO=J1 AND COLOR=红;,例题3.10,求至少使用了零件编号
15、为P3和P5的工程编号JNO。 SELECT DISTINCT X.JNO FROM SPJ AS X,SPJ AS Y WHERE X.JNO=Y.JNO AND X.PNO=P3 AND Y.PNO=P5; 同一个关系SPJ在一层中出现两次，为区别，引入两个元组变量(即别名)X和Y。在语句中应用元组变量对列名进行限定。,（4）使用合计函数,合计函数,例题3.11,求供应P3零件的供应商个数。 SELECT COUNT(DISTINCT(SNO) AS COUNT_P3 FROM SPJ WHERE PNO=P3; 这个查询结果只有一行和一列，就是供应“P3”零件的供应商个数。谓词DISTI
16、NCT用在列名前表示消除该列中重复的值。COUNT_P3为输出的列名。 求项目余额的最大值，最小值，总值和平均值。 SELECT MAX(BALANCE) AS MAX_NUMBER,MIN(BLANCE) AS MIN_NUMBER，SUM(BALANCE) AS SUM_NUMBER, AVG(BALANCE) AS AVG_NUMBER FROM J,（5）数据分组,例题3.12：统计泉州地区的项目使用零件的种数(超过3种)和零件总数量。要求查询结果按零件的种数升序排列，种数相同时按总数量降序排列。 SELECT SPJ.JNO,COUNT(DISTINCT(PNO) AS COUNT_PNO, SUM(QTY) AS SUM_QTY FROM J,SPJ WHERE J.CITY=泉州 AND J.JNO= SPJ.JNO GROUP BY SPJ.JNO HAVING COUNT(DISTINCT(PNO)3 ORDER BY 2,3 DESC; 首先，根据WHERE子句的条件，对关系J和SPJ执行联接操作找出满足
