搜索
华侨大学信息科学与工程学院3.4结构化查询语言SQL华侨大学信息科学与工程学院1.什么是SQL结构化查询语言SQL(StructuredQueryLanguage)是一种关系数据库的标准语言用于存取数据以及查询、更新和管理关系数据库系统高度非过程化。只要指出“做什么”，无须指明“怎么做”应用广泛。被绝大多数商品化关系数据库系统采用，如Oracle，SQLServer，DB2，Sybase，Informix等华侨大学信息科学与工程学院数据库中的基本概念基本术语数据库技术的术语关系模型术语SQL术语记录类型文件记录属性、字段、数据项关系模式关系、实例（表）元组属性基本表基本表、表格行列学号姓名性别出生日期信息技术010329张晖男81.10.1589010139李小凡男80.12.3078010117周秋红男80.05.1890010207张志平女80.11.3088学生（学号，姓名，性别，出生日期，信息技术）华侨大学信息科学与工程学院数据库系统的三级模式结构应用A应用B应用C应用D应用E外模式1外模式2外模式3模式内模式数据库外模式（也称子模式）：数据库用户使用的局部数据的逻辑结构和特征的描述，反映了不同的用户的应用需求。模式（Schema）：数据库中全体数据的逻辑结构和特征的描述，综合了所有用户的需求。内模式（也称存储模式）：是数据物理结构和存储方式的描述。华侨大学信息科学与工程学院2.SQL数据库的体系结构特征：SQL用户可以是终端用户，也可以是应用程序。SQL语句可嵌入在宿主语言的程序中使用。用户可以用SQL语句对视图和基本表进行查询等操作。在用户看来，视图和基本表是一样的，都是关系(即表格)。一个表可以是一个基本表，也可以是一个视图，基本表是实际存储在数据库中的表。视图是从基本表或其他视图中导出的表，不独立存储在数据库中，是一个虚表。一个基本表可以跨一个或多个存储文件，一个存储文件也可存放一个或多个基本表。每个存储文件与外部存储器上一个物理文件对应。用户1用户2视图1视图2基本表2基本表1基本表3基本表4存储文件1存储文件2外模式模式内模式华侨大学信息科学与工程学院3.SQL语言的组成实现四类功能：数据定义(DDL,DataDefinitionLanguage)用于定义SQL模式、基本表、视图和索引。数据操纵(DML,DataManipulationLanguage)数据查询数据更新：插入、删除和修改三种操作。数据控制(DCL,DataControlLanguage)包括对基本表和视图的授权，完整性规则的描述，事务控制语句等。嵌入式SQL使用涉及到SQL语句嵌入在宿主语言程序中的使用规则。华侨大学信息科学与工程学院SQL语言的动词SQL功能动词数据定义创建CREATE撤消DROP修改ALTER数据操纵查询SELECT插入INSERT修改UPDATE删除DELETE数据控制授权GRANT收权REVOKE华侨大学信息科学与工程学院4.SQL的数据定义(DDL)SQL的数据定义语句操作对象操作方式创建撤消修改模式(SCHEMA)CREATESCHEMADROPSCHEMA表(TABLE)CREATETABLEDROPTABLEALTERTABLE视图(VIEW)CREATEVIEWDROPVIEW索引(INDEX)CREATEINDEXDROPINDEX华侨大学信息科学与工程学院（1）基本表的创建句法：CREATETABLE表名（列名数据类型[列级完整性约束条件][，列名数据类型[列级完整性约束条件]]…[，表级完整性约束条件]）；表名：所要定义的基本表的名字列名：组成该表的各个属性（列）列级完整性约束条件：涉及相应属性列的完整性约束条件表级完整性约束条件：涉及一个或多个属性列的完整性约束条件华侨大学信息科学与工程学院SQL的完整性约束数据库的完整性是指数据库中数据的正确性与相容性。SQL语言定义完整性约束条件主码约束：主键子句PRIMARYKEY参照完整性约束：外键子句FOREIGNKEY检查子句：CHECK唯一性约束：UNIQUE非空值约束：NOTNULL表级列级华侨大学信息科学与工程学院例题3.1创建四个表“供应商关系”表S“零件关系”表P“工程项目关系”表J“供应情况关系”表SPJ华侨大学信息科学与工程学院例题3.1“供应商关系”表创建一个“供应商关系”表S，它有四个属性，分别是供应商号(SNO)、供应商名(SNAME)、状态(STATUS)和地址(ADDR)，属性的类型都是字符型，长度分别是4、20、10和20个字符。规定供应商号和供应商名不能取空值。主键是供应商编号SNO。(SNOCHAR(4)，SNAMECHAR(20)，STATUSCHAR(10)，ADDRCHAR(20)，PRIMARYKEY(SNO))；CREATETABLESNOTNULLNOTNULL华侨大学信息科学与工程学院例题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在0～10000之间。CREATETABLESPJ(SNOCHAR(4)NOTNULL,PNOCHAR(4)NOTNULL,JNOCHAR(4)NOTNULL,PRICENUMERIC(7,2),QTYSMALLINT,PRIMARYKEY(SNO,PNO,JNO),FOREIGNKEY(SNO)REFERENCESS(SNO),FOREIGNKEY(PNO)REFERENCESP(PNO),FOREIGNKEY(JNO)REFERENCESJ(JNO),CHECK(QTYBETWEEN0AND10000))；华侨大学信息科学与工程学院（2）基本表的修改句法：ALTERTABLE表名[ADD新列名数据类型[完整性约束]][DROP列名[CASCADE|RESTRICT]][MODIFY列名数据类型]；表名：要修改的基本表ADD子句：增加新列和新的完整性约束条件DROP子句：删除指定的列MODIFY子句：用于修改列名和数据类型注意：新增加的属性不能定义为“NOTNULL”。基本表在增加一个属性后，原有元组在新增加的属性列上的值都被定义为空值(NULL)DROP子句中的CASCADE方式表示：在基本表中删除某属性时，所有引用到该属性的视图和约束也要一起自动地被删除。而RESTRICT方式表示在没有视图或约束引用该属性时，才能在基本表中删除该属性，否则拒绝删除操作。华侨大学信息科学与工程学院例题例3.2在基本表S中增加一个电话号码(TELE)属性，可用下列语句：ALTERTABLESADDTELECHAR(12)；例3.3在基本表S中删除状态(STATUS)属性，并且将引用该属性的所有视图和约束也一起删除，可用下列语句：ALTERTABLESDROPSTATUSCASCADE；华侨大学信息科学与工程学院（3）视图的创建视图的特点：虚表，是从一个或几个基本表（或视图）导出的表。数据库中只存放视图的定义而不存放视图的数据，这些数据仍存放在导出视图的基本表中。基表中的数据发生变化，从视图中查询出的数据也随之改变。华侨大学信息科学与工程学院（3）视图的创建句法：CREATEVIEW视图名[(列名[，列名]…)]ASSELECT查询语句注意：执行CREATEVIEW语句时只是把视图的定义存入数据字典，并不执行其中的SELECT语句。在对视图查询时，按视图的定义从基本表中将数据查出。华侨大学信息科学与工程学院例题3.5对工程项目零件供应数据库中基本表S,P,J,SPJ，用户经常要用到有关项目使用零件情况信息：工程号(JNO)、工程项目名称(JNAME)、供应商号(SNO)、供应商名(SNAME)、零件号(PNO)、零件名(PNAME)、供应数量(QTY)等列的数据，那么可用下列语句建立视图：CREATEVIEWJSP_NAME(JNO,JNAME,SNO,SNAME,PNO,PNAME,QTY)ASSELECT(J.JNO,JNAME,S.SNO,SNAME,P.PNO,PNAME,QTY)FROMS,P,J,SPJWHERES.SNO=SPJ.SNOANDP.PNO=SPJ.PNOANDJ.JNO=SPJ.JNO；华侨大学信息科学与工程学院5.SQL的数据操纵数据查询SELECT数据更新插入INSERT修改UPDATE删除DELETE华侨大学信息科学与工程学院6.SQL的数据查询（1）语句格式SELECT[ALL|DISTINCT]目标列表达式[,目标列表达式]…FROM表名或视图名[,表名或视图名]…[WHERE条件表达式][GROUPBY列名1[HAVING条件表达式]][ORDERBY列名2[ASC|DESC]]；华侨大学信息科学与工程学院SELECT子句：指定要显示的属性列DISTINCT表示要在结果中去掉取值重复的行。要选择表中所有列，可用星号“”代替FROM子句：指定查询对象(基本表或视图)WHERE子句：指定查询条件GROUPBY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。HAVING短语：筛选出只有满足指定条件的组ORDERBY子句：对查询结果表按指定列值的升序(ASC)或降序(DESC)排序华侨大学信息科学与工程学院（2）单表查询例题3.9①从供应情况关系表SPJ中，检索供应工程J1零件的供应商编号SNO。SELECTDISTINCTSNOFROMSPJWHEREJNO=’J1’;②查询全体工程项目的详细信息SELECT*FROMJ;③从工程项目关系表J中，查询没有正余额的工程编号、名称及城市，结果按工程编号升序排列。SELECTJNO,JNAME,CITYFROMJWHEREBALANCEISNULLORBALANCE=0ORDERBYJNO；华侨大学信息科学与工程学院④从供应情况关系表SPJ中，求使用零件数量在100与1000之间的工程项目编号，零件号和数量。SELECTJNO,PNO,QTYFROMSPJWHEREQTYBETWEEN100AND1000；⑤从供应商关系表S中，查询泉州的供应商名称，假设供应商关系的ADDR列的值都以城市名开头。SELECTSNAMEFROMSWHEREADDRLIKE’泉州%’华侨大学信息科学与工程学院（3）多表查询例题3.10①求使用了P3零件的工程项目名称。SELECTDISTINCTJNAMEFROMJ,SPJWHERESPJ.JNO=J.JNOANDPNO=’P3’;这个SELECT语句执行时，要对关系SPJ和J做联接操作。执行联接操作的表示方法是FROM子句后面写上执行联接操作的表名SPJ和J，再在WHERE子句中写上联接的条件SPJ.JNO=J.JNO。②求为工程项目J1供应红色零件的供应商号SNO。SELECTDISTINCTSNOFROMSPJ,PWHERESPJ.PNO=P.PNOANDJNO=’J1’ANDCOLOR=’红’;华侨大学信息科学与工程学院例题3.10③求至少使用了零件编号为P3和P5的工程编号JNO。SELECTDISTINCTX.JNOFROMSPJASX,SPJASYWHEREX.JNO=Y.JNOANDX.PNO=’P3’ANDY.PNO=’P5’;同一个关