数据库标准语言sql

1SQL语言的特点SQL语言的组成SQL的数据定义语句的使用SQL的数据查询语句的使用SQL的数据操纵语句的使用SQL的数据控制语句的使用本章要点：2SQL的工作原理SQL请求数据数据库DB数据库管理系统DBMS当用户去要检索数据库的数据时通过SQL发出申请，DBMS对请求进行处理，将结果返回用户。3SQL的特点通用性强:集数据定义/数据操纵/数据控制功能于一体两种使用方式(统一的语法结构)方式一、独立的用于联机交互使用方式；方式二、嵌入到高级语言使用。面向集合(表)：改变以往操作方式(记录操作)，建立表间(集合)的数据操作。易学易用:语法非常接近自然语言（英语)便于学习理解4SQL支持三级模式视图：是一个虚表。数据库中仅存视图定义，不存对应的数据。基本表：独立存在的表。在SQL中一个关系对应一个表，每个基表对应一个存储文件，一个表可带若干索引。存储文件：逻辑结构组成数据库的内模式。存储文件+索引=内模式5外模式模式内模式Storefile1Storefile2Storefile3Storefile4Basetable1Basetable2Basetable3Basetable4View1View2SQL用户SQL支持关系数据库三级模式视图基本表存储文件6数据查询语言（DQL）：querySELECT数据定义语言（DDL）:definitionCREATE；DROP；ALTER数据操作语言（DML）:manipulationINSERT;UPDATE;DELETE数据控制语言（DCL）:controlGRANT;REVOKESQL的基本功能7SQL语言的语句表语言命令实现的功能DDLCREATE创建表DROP删除表ALTER修改表结构DQLSELECT查询数据库中满足指定条件的记录DMLINSERT向表中插入记录UPDATE修改表中记录的数据DELETE删除表中符合条件的记录DCLGRANT给用户授权REVOKE收回用户的权限8数据定义功能CREATE；DROP；ALTER操作对象操作方式创建删除修改表CREATETABLEDROPTABLEALTERTABLE视图CREATEVIEWDROPVIEW索引CREATEINDEXDROPINDEX用户创建或重新构建数据库的语言9基本表定义：CREATETABLE表名(列名数据类型[列级完整性约束],列名数据类型[列级完整性约束],……,[表级完整性约束])[其它参数]Ÿ表名给出要创建的基本表的名称；Ÿ列名给出列名或字段名；Ÿ数据类型Ÿ列级完整性约束Ÿ表级完整性约束Ÿ其它参数10数据类型11列级完整性约束用于定义列或字段一级的完整性约束，一般包括：NOTNULL和NULL约束PRIMARYKEY约束UNIQUE约束FOREIGNKEY约束DEFAULT定义CHECK约束12表级完整性约束用于定义表一级的完整性约束，一般包括：PRIMARYKEY约束（复合属性构成的主关键字说明）FOREIGNKEY约束（外部关键字及参照关系说明）CHECK约束（同时涉及到多个属性的域完整性约束）13其它参数不是SQL的标准选项，一般用于与物理存储有关的说明，不同的数据库管理系统定义的方式肯定不同，另外该项参数一般也不是必需的。14建立表的例子物资订购管理数据库的扩展Bachman图1:n1:n1:n15仓库仓库号城市面积订购单职工号供应商号订购单号订购日期完成日期供应商供应商号供应商名地址FK__订购单__供应商号__0BC6C43E职工仓库号职工号工资FK__职工__仓库号__014935CBFK__订购单__职工号__0AD2A005物资订购管理数据库在SQL中的实现1617例：建立仓库表CREATETABLE仓库(仓库号CHAR(5)PRIMARYKEY,城市CHAR(10),面积INTCHECK(面积0))18例：建立职工表CREATETABLE职工(仓库号CHAR(5)FOREIGNKEYREFERENCES仓库,职工号CHAR(5)PRIMARYKEY,工资INTCHECK(工资=1000AND工资=5000)DEFAULT1200)19例：建立供应商表CREATETABLE供应商(供应商号CHAR(5)PRIMARYKEY,供应商名CHAR(20),地址CHAR(20))20例：建立订购单表CREATETABLE订购单(职工号CHAR(5)NOTNULLFOREIGNKEYREFERENCES职工,供应商号CHAR(5)NULLFOREIGNKEYREFERENCES供应商,订购单号CHAR(5)PRIMARYKEY,订购日期DATETIMEDEFAULTgetdate())21表级约束的例子假设职工关系的主关键字是（仓库号,职工号），则相应的命令如下：CREATETABLE职工(仓库号CHAR(5)FOREIGNKEYREFERENCES仓库,职工号CHAR(5),工资INTCHECK(工资=1000AND工资=5000)DEFAULT1200,PRIMARYKEY(仓库号,职工号))创建表23修改表结构命令ALTERTABLE表名ADD列名数据类型[列级完整性约束]DROP完整性约束名|DROPCOLUMN列名|ALTERCOLUMN列名数据类型[列级完整性约束]24增加新的属性（字段）•往订购单关系中增加一个新属性“完成日期”ALTERTABLE订购单ADD完成日期DATETIMENULL25修改属性的定义•将订购单关系的完成日期属性的数据类型修改为SMALLDATETIMEALTERTABLE订购单ALTERCOLUMN完成日期SMALLDATETIMENULL26删除完整性约束•建立的列级或表级完整性约束可以删除，但是前提是在定义完整性约束时必须给出约束名称，否则不能删除。设有：CREATETABLE职工(仓库号CHAR(5)FOREIGNKEYREFERENCES仓库,职工号CHAR(5)PRIMARYKEY,工资INTCONSTRAINTsalaryCHECK(工资=1000AND工资=5000)DEFAULT1200)则可以：ALTERTABLE职工DROPCONSTRAINTsalary27删除属性删除订购单关系中的“完成日期”属性：ALTERTABLE订购单DROPCOLUMN完成日期注意：有些系统的ALTERTABLE命令不允许删除属性，如果必须要删除属性，一般步骤是：先将旧表中的数据备份，然后删除旧表、并建立新表，最后将原来的数据恢复到新表中。28删除表命令格式：DROPTABLE表名例：DROPTABLE订购单29索引定义的命令格式CREATE[UNIQUE][CLUSTERED]INDEX索引名ON表名(列名[ASC|DESC][,列名[ASC|DESC]…])•普通索引•唯一（UNIQUE）索引•聚集（CLUSTERED）索引30普通索引•如果没有指定UNIQUE或CLUSTERED等将建立普通索引。在单个字段上建立普通索引：CREATEINDEXsup_idxON订购单(供应商号)在多个字段上建立普通索引：CREATEINDEXsup_emp_idxON订购单(供应商号,职工号DESC)31唯一索引通过指定UNIQUE则为表创建唯一索引（不允许存在索引值相同的两个元组）。在CREATETABLE命令中的UNIQUE约束将隐式创建唯一索引。在仓库关系的城市属性上建立一个唯一索引：CREATEUNIQUEINDEXcity_idxON仓库(城市)32聚集索引通过指定CLUSTERED建立聚集索引。在CREATETABLE命令中的PRIMARYKEY约束将隐式创建聚集索引。一个表只允许建立一个聚集索引。如果在创建表时已经指定了主关键字，则不可以再创建聚集索引。33使用索引的原则：•不应该在一个表上建立太多的索引（一般不超过两到三个），索引能改善查询效果，但也耗费了磁盘空间，降低了更新操作的性能，因为系统必须花时间来维护这些索引。•除了为数据的完整性而建立的唯一索引外，建议在表较大时再建立普通索引，表中的数据越多，索引的优越性才越明显。34索引的删除命令格式：DROPINDEX索引名例：DROPINDEXcity_idxON仓库35数据操作功能插入功能：INSERT更新功能：UPDATE删除功能:DELETE36插入操作INSERT插入一个元组INSERTINTO表名[(列名[,列名…])]VALUES(表达式[,表达式……])插入一个查询结果INSERTINTO表名[(列名[,列名…])]SELECT查询37插入操作举例插入一个完整的元组INSERTINTO仓库VALUES('WH5','沈阳',500)插入一个不完整的元组INSERTINTO仓库(仓库号,面积)VALUES('WH6',500)插入一个查询结果INSERTINTO订购单备份SELECT*FROM订购单先创建新表举例仓库表：INSERTINTO仓库VALUES('WH1','北京','370');INSERTINTO仓库VALUES('WH2','上海','500');INSERTINTO仓库VALUES('WH3','广州','200');INSERTINTO仓库VALUES('WH4','武汉','400');职工表：INSERTINTO职工VALUES('WH2','E1','1220');INSERTINTO职工VALUES('WH1','E3','1210');INSERTINTO职工VALUES('WH2','E4','1250');INSERTINTO职工VALUES('WH3','E6','1230');INSERTINTO职工VALUES('WH1','E7','1250');39更新操作UPDATE命令格式:UPDATE表名SET列名=表达式[,列名=表达式…][[FROM表名]WHERE逻辑表达式]UPDATE更新满足“逻辑表达式”条件的记录；一次可以更新多个属性的值；更新的条件可以与其他的表相关（使用FROM指定）；如果没有指定更新条件则更新表中的全部记录。40更新操作举例给所有职工增加一倍的工资UPDATE职工SET工资=工资*2给WH1仓库的职工提高10%的工资UPDATE职工SET工资=工资*2WHERE仓库号='WH1'给“上海”仓库的职工的工资提高到三倍UPDATE职工SET工资=工资*3FROM仓库WHERE仓库.仓库号=职工.仓库号AND城市=‘上海'41删除操作DELETEDELETEFROM表名[[FROM表名]WHERE逻辑表达式]DELETE命令从指定的表中删除满足“逻辑表达式”条件的元组；如果没有指定删除条件则删除表中的全部元组，所以在使用该命令时要格外小心；删除的条件可以与其他的表相关（使用可选的FROM指定）；DELETE命令只删除元组，它不删除表或表结构。42删除操作举例删除仓库关系中仓库号值是WH5的元组DELETEFROM仓库WHERE仓库号='WH5'删除所在城市是上海的仓库的所有职工元组DELETEFROM职工FROM仓库WHERE仓库.仓库号=职工.仓库号AND城市='上海'43SQL的数据查询功能基本格式：SELECT……FROM……WHERE……44SQL查询SELECT[ALL|DISTINCT]{*|目标列表达式,…,目标列表达式}FROM表名或视图名[,表名视图名…][WHERE条件表达式][GROUPBY列名[,列名…][HAVING表达式]][ORDERBY列名[ASC|DESC][,列名[ASC|DESC]..][COMPUTE…]45