第3章关系数据库标准语言SQL(上)VIP

.,第3章关系数据库标准语言SQL,SQL是StructuredQueryLanguage（结构化查询语言）的缩写。可以说查询是SQL语言的重要组成部分，但不是全部，SQL还包含数据定义、数据操纵和数据控制功能等部分。SQL已经成为关系数据库的标准数据语言，所以现在所有的关系数据库管理系统都支持SQL。,.,学习目标,了解SQL的发展过程和主要功能；熟练掌握SQL的CREATETABLE语句和数据完整性约束的定义方法；熟练掌握SQL的数据操作语句，深刻理解数据完整性约束的作用；熟练掌握SQLSELECT语句，能够运用该语句完成关系数据库上的各种查询；理解关系数据库的视图概念，掌握视图的定义和使用方法。,.,重点难点,SQL语言本身就是学习数据库的重点，所以要求学生要熟练掌握，具体就是要熟练掌握SQLCREATETABLE语句、SQLINSERT语句、SQLDELETE语句、SQLUPDATE语句和SQLSELECT语句。本章学习的难点是CREATETABLE语句中有关数据完整性约束的定义，以及SQLSELECT中复杂的连接和嵌套查询。,.,3.1SQL语言概述,SQL标准源于1986年10月的ANSI标准1987年6月正式成为ISO标准SQL89提出了完整性特征SQL92标准逐步完善(基本、标准和完全级)SQL99增加了面向对象等最新的标准是2003年发布的SQL2003SQL标准只是一个建议标准，主流产品一般也只达到了基本级的要求,.,SQL语言的主要特点,SQL是一种一体化的语言SQL语言是一种高度非过程化的语言SQL语言非常简洁SQL语言可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用,.,样本数据库,图3-1样本数据库,.,数据定义功能与数据完整性约束的实现,SQL的定义功能包括模式、表、视图、存储过程、触发器、规则、默认值等的定义。,.,SQL中的模式(Schema),组织数据库对象的一种逻辑结构把一个数据库中的所有对象按用途划分为若干集合,.,模式组织举例,产品模式订购模式销售模式,.,定义模式,模式通常由DBA创建也可以由得到CREATESCHEMA授权的用户创建使用短语AUTHORIZATIONowner_name可以指定模式的管理者（默认是创建者）,CREATESCHEMAschema_nameAUTHORIZATIONowner_name,.,删除模式,只有在模式为空、即模式中不包含任何对象时才可以删除模式。,DROPSCHEMAschema_name,.,表的定义,一个表由若干列构成，自SQLServer2005不仅可以定义实列(简称列，column_definition),而且还可以定义虚列(即计算列，computed_column_definition)理解：,CREATETABLEschema_name.table_name(|,.n),.,定义列(),CREATETABLEschema_name.table_name(|,.n),column_nameNULL|NOTNULLCONSTRAINTconstraint_namePRIMARYKEY|UNIQUE|FOREIGNKEYREFERENCESschema_name.referenced_table_name(ref_column)ONDELETENOACTION|CASCADE|SETNULL|SETDEFAULTONUPDATENOACTION|CASCADE|SETNULL|SETDEFAULT|CHECK(logical_expression)DEFAULTconstant_expression,.,定义列(),列名（column_name）和数据类型（）必须指定，在SQLServer2005中可以使用的数据类型如表3-1所示其他项目是各种约束,column_nameNULL|NOTNULLCONSTRAINTconstraint_namePRIMARYKEY|UNIQUE|FOREIGNKEYREFERENCESschema_name.referenced_table_name(ref_column)ONDELETENOACTION|CASCADE|SETNULL|SETDEFAULTONUPDATENOACTION|CASCADE|SETNULL|SETDEFAULT|CHECK(logical_expression)DEFAULTconstant_expression,.,是否允许空值约束,空值的概念？,column_nameNULL|NOTNULLCONSTRAINTconstraint_namePRIMARYKEY|UNIQUE|FOREIGNKEYREFERENCESschema_name.referenced_table_name(ref_column)ONDELETENOACTION|CASCADE|SETNULL|SETDEFAULTONUPDATENOACTION|CASCADE|SETNULL|SETDEFAULT|CHECK(logical_expression)DEFAULTconstant_expression,.,关于约束名称,列级约束名，名称可以省略用于命名主关键字、唯一性或参照完整性约束省略名称不便于管理相应的完整性约束,column_nameNULL|NOTNULLCONSTRAINTconstraint_namePRIMARYKEY|UNIQUE|FOREIGNKEYREFERENCESschema_name.referenced_table_name(ref_column)ONDELETENOACTION|CASCADE|SETNULL|SETDEFAULTONUPDATENOACTION|CASCADE|SETNULL|SETDEFAULT|CHECK(logical_expression)DEFAULTconstant_expression,.,主关键字和唯一性约束,PRIMARYKEY：主关键字约束，说明该列是主关键字UNIQUE：惟一性约束，说明该列取值必须惟一PRIMARYKEY和UNIQUE约束的区别？,column_nameNULL|NOTNULLCONSTRAINTconstraint_namePRIMARYKEY|UNIQUE|FOREIGNKEYREFERENCESschema_name.referenced_table_name(ref_column)ONDELETENOACTION|CASCADE|SETNULL|SETDEFAULTONUPDATENOACTION|CASCADE|SETNULL|SETDEFAULT|CHECK(logical_expression)DEFAULTconstant_expression,.,参照完整性约束,说明外部关键字，FOREIGNKEY可以省略什么是外部关键字？外部关键字的作用？schema_name.referenced_table_name(ref_column)指出参照的表和列，默认模式时schema_name可以省略，列名相同时(ref_column)可以省略,column_nameNULL|NOTNULLCONSTRAINTconstraint_namePRIMARYKEY|UNIQUE|FOREIGNKEYREFERENCESschema_name.referenced_table_name(ref_column)ONDELETENOACTION|CASCADE|SETNULL|SETDEFAULTONUPDATENOACTION|CASCADE|SETNULL|SETDEFAULT|CHECK(logical_expression)DEFAULTconstant_expression,.,参照完整性规则,ONDELETE和ONUPDATE说明在执行删除和更新操作时如何处理参照完整性删除操作检查的完整性？更新操作检查的完整性？,column_nameNULL|NOTNULLCONSTRAINTconstraint_namePRIMARYKEY|UNIQUE|FOREIGNKEYREFERENCESschema_name.referenced_table_name(ref_column)ONDELETENOACTION|CASCADE|SETNULL|SETDEFAULTONUPDATENOACTION|CASCADE|SETNULL|SETDEFAULT|CHECK(logical_expression)DEFAULTconstant_expression,.,CHECK约束,用来说明列的取值范围本约束用逻辑表达式(logical_expression)进行说明如果列值使表达式为真则是合法值，否则就是非法值,column_nameNULL|NOTNULLCONSTRAINTconstraint_namePRIMARYKEY|UNIQUE|FOREIGNKEYREFERENCESschema_name.referenced_table_name(ref_column)ONDELETENOACTION|CASCADE|SETNULL|SETDEFAULTONUPDATENOACTION|CASCADE|SETNULL|SETDEFAULT|CHECK(logical_expression)DEFAULTconstant_expression,.,字段默认值,用常量表达式（constant_expression）来说明列的默认取值,column_nameNULL|NOTNULLCONSTRAINTconstraint_namePRIMARYKEY|UNIQUE|FOREIGNKEYREFERENCESschema_name.referenced_table_name(ref_column)ONDELETENOACTION|CASCADE|SETNULL|SETDEFAULTONUPDATENOACTION|CASCADE|SETNULL|SETDEFAULT|CHECK(logical_expression)DEFAULTconstant_expression,.,定义计算列,column_nameAScomputed_column_expressionPERSISTEDNOTNULLCONSTRAINTconstraint_namePRIMARYKEY|UNIQUE|FOREIGNKEYREFERENCESreferenced_table_name(ref_column)ONDELETENOACTION|CASCADEONUPDATENOACTION|CHECK(logical_expression),CREATETABLEschema_name.table_name(|,.n),.,定义计算列,其中column_name给出计算列的列名，computed_column_expression是对应的计算表达式，PERSISTED说明物理存储计算列的值。其他选项和定义实列时类似。,column_nameAScomputed_column_expressionPERSISTEDNOTNULLCONSTRAINTconstraint_namePRIMARYKEY|UNIQUE|FOREIGNKEYREFERENCESreferenced_table_name(ref_column)ONDELETENOACTION|CASCADEONUPDATENOACTION|CHECK(logical_expression),.,定义表级完整性约束,如果某个完整性约束与多个列相关，则这样的完整性约束不能定义在单个列上，这时候就需要表级完整性约束。单个列上的完整性约束也可以用表级完整性约束的形式来定义。,.,定义表级完整性约束,CONSTRAINTconstraint_namePRIMARYKEY|UNIQUE|FOREIGNKEY(column,.n)REFERENCESreferenced_table_name(ref_column,.n)ONDELETENOACTION|CASCADE|SETNULL|SETDEFAULTONUPDATENOACTION|CASCADE|SETNULL|SETDEFAULT|CHECK(logical_expression),CREATETABLEschema_name.table_name(|,.n),.,表定义举例,例1：定义院系表，其中编号为主关键字，名称应该惟一、并且不允许为空值。createtablestudent.院系(编号tinyintprimarykey,名称char(30)uniquenotnull,负责人char(10),办公地点char(40),.,.,表定义举例,例2：定义学生表，其中规定：学号列是主关键字；院系列为计算列（取学号列的第3和第4个字符），并且院系值参照院系表的编号值（院系表是被参照表，主关键字是编号；参照表是学生表，外部关键字是院系），此约束说明一名学生一定属于某个院系；姓名列不允许为空值；性别必须取值“男”或“女”；学生的学籍状态为正常、留级、休学或退学。,.,createtablestudent.学生(学号char(8)primarykey,院系asconvert(tinyint,substring(学号,3,2)persistednotnullforeignkeyreferencesstudent.院系(编号),姓名char(10)notnull,性别char(2)check(性别=男or性别=女),生源char(6),状态char(4)check(状态in(正常,留级,休学,退学),.,.,表定义举例,例3：定义教师表，其中规定：教师编号是主关键字；院系列为计算列（取教师编号的第1和第2个字符），并且院系值参照院系表的编号值，此约束说明一名教师一定属于某个院系；姓名列不允许为空值；性别必须取值“男”或“女”；职称的取值为教授、副教授、讲师或助教。,.,createtablestudent.教师(教师编号char(6)primarykey,院系asconvert(tinyint,substring(教师编号,1,2)persistednotnullforeignkeyreferencesstudent.院系(编号),姓名char(10)notnull,性别char(2)check(性别=男or性别=女),职称char(6)check(职称in(教授,副教授,讲师,助教),专业char(16),.,.,表定义举例,例4：定义课程表，其中规定：课程编号是主关键字；课程名称不允许为空值；责任教师参照教师表的教师编号值，此约束说明责任教师一定是已经在编的教师；学时不允许为空值；课程性质分为公共基础、专业基础、专业选修和任意选修。,.,createtablestudent.课程(课程编号char(6)primarykey,课程名称char(24)notnull,责任教师char(6)constraintduty_teacherforeignkeyreferencesstudent.教师(教师编号),学时tinyintnotnull,课程性质char(8)check(课程性质in(公共基础,专业基础,专业选修,任意选修),.,.,表定义举例,例5：定义选课表，其中规定：学号和课程编号两列构成主关键字（需要表级完整性约束）；学号参照学生表的学号（约束选课的学生必须存在）；课程编号参照课程表的课程编号（约束被选的课程必须存在）；考试成绩取值在0100之间，并且默认为空值。,.,createtablestudent.选课(学号char(8)foreignkeyreferencesstudent.学生,课程编号char(6)foreignkeyreferencesstudent.课程,考试成绩tinyintcheck(考试成绩between0and100)defaultnullprimarykey(学号,课程编号),.,.,.,修改表结构,ALTERTABLEschema_name.table_nameALTERCOLUMNcolumn_nameNULL|NOTNULL|ADD|DROPCONSTRAINTconstraint_name|COLUMNcolumn_name,ALTERCOLUMN修改已有列的定义，但是只能修改为兼容数据类型或重新定义是否允许空值；,.,修改表结构,ADD添加新列、或新的计算列或表级完整性约束；,ALTERTABLEschema_name.table_nameALTERCOLUMNcolumn_nameNULL|NOTNULL|ADD|DROPCONSTRAINTconstraint_name|COLUMNcolumn_name,.,修改表结构,DROP删除指定的完整性约束或指定的列。,ALTERTABLEschema_name.table_nameALTERCOLUMNcolumn_nameNULL|NOTNULL|ADD|DROPCONSTRAINTconstraint_name|COLUMNcolumn_name,.,修改表结构举例,例6：规定学生表的性别字段不允许为空值。ALTERTABLE学生ALTERCOLUMN性别char(2)NOTNULL是否会影响原先定义在该列上的其他约束？,.,.,修改表结构举例,例7：删除课程表中的责任教师列。altertablestudent.课程dropcolumn责任教师如果捆绑了其他对象（如列约束），则需要先删除捆绑的对象。altertablestudent.课程dropduty_teacher,.,.,.,修改表结构举例,例8：为课程表增加责任教师列，列描述如下：字符类型，长度为6；允许空值；外部关键字，参照教师表的教师编号列，当删除被参照记录时该列置为空值，当修改被参照记录的主关键字时级联修改参照记录的外部关键字值。,.,altertablestudent.课程add责任教师char(6)nullconstraintref_teacherforeignkeyreferencesstudent.教师(教师编号)ondeletesetnullonupdatecascade,.,.,删除表,DROPTABLEschema_name.table_name,.n,一次可以删除多个表。不能使用DROPTABLE删除被FOREIGNKEY约束参照的表。如果要在同一个DROPTABLE语句中删除参照表以及被参照表，则必须先列出参照表。,.,思考题,在SQL中CREATESCHEMA是一条什么命令？创建的SCHEMA是一种什么对象？它有什么作用？在表中定义约束时，PRIMARYKEY和UNIQUE有什么区别？在CREATETABLE命令中哪些内容与定义参照完整性有关？讨论它们的具体作用。约束一般定义在列上，为什么还需要表级约束？定义计算列时选用关键词PERSISTED有什么作用？计算列是否可以作为参照列（外部关键字）？如果在定义约束时没有使用CONSTRAINT短语给出约束名，而事后又要删除该约束应该怎么办？对已有的表可以进行哪些方面的表结构修改？,.,数据操作功能,及数据完整性的作用,.,SQL的数据操作,插入操作及其完整性约束删除操作及其完整性约束更新操作及其完整性约束,.,插入操作及其完整性约束,SQL的插入语句是INSERT，常用格式：INSERTINTOschema_name.table_name(column_list)VALUES(expression|DEFAULT|NULL,.n)其中schema_name指出模式名table_name指出表名column_list给出插入操作所涉及列的列表（默认是表的全部列）expression|DEFAULT|NULL,.n给出对应于column_list的各个列的值,.,例9插入院系表的第1个元组(记录),INSERTINTOstudent.院系(编号,名称,负责人,办公地点)VALUES(1,信息管理学院,李嘉,教一楼3层),CREATETABLEstudent.院系(编号tinyintPRIMARYKEY,名称char(30)UNIQUENOTNULL,负责人char(10),办公地点char(40),.,.,例10省略列名、插入院系表的第2个元组,INSERTINTOstudent.院系VALUES(2,经济管理学院,吴鸿忠,教三楼4层),CREATETABLEstudent.院系(编号tinyintPRIMARYKEY,名称char(30)UNIQUENOTNULL,负责人char(10),办公地点char(40),.,.,例11,INSERTINTOstudent.院系VALUES(2,计算机学院,张秋霞,教二楼3层),CREATETABLEstudent.院系(编号tinyintPRIMARYKEY,名称char(30)UNIQUENOTNULL,负责人char(10),办公地点char(40),违背PRIMARYKEY约束,.,.,例12,INSERTINTOstudent.院系VALUES(3,经济管理学院,张秋霞,教二楼3层),CREATETABLEstudent.院系(编号tinyintPRIMARYKEY,名称char(30)UNIQUENOTNULL,负责人char(10),办公地点char(40),违背UNIQUE约束,.,.,例13,INSERTINTOstudent.院系(编号,负责人,办公地点)VALUES(3,张秋霞,教二楼3层),CREATETABLEstudent.院系(编号tinyintPRIMARYKEY,名称char(30)UNIQUENOTNULL,负责人char(10),办公地点char(40),违背NOTNULL约束,.,.,.,例14,CREATETABLEstudent.学生(学号char(8)PRIMARYKEY,院系ASCONVERT(Tinyint,SUBSTRING(学号,3,2)PERSISTEDNOTNULLFOREIGNKEYREFERENCESstudent.院系(编号),姓名char(10)NOTNULL,性别char(2)CHECK(性别=男OR性别=女),生源char(6),状态char(4)CHECK(状态IN(正常,留级,休学,退学),插入学生表的第1个元组。,INSERTINTOstudent.学生(学号,姓名,性别,生源,状态)VALUES(04014101,曹波,男,湖北,正常),.,.,例15插入学生表的第2条记录（指定正确的院系值）,注意：学生表的院系列是计算列，是不能被插入语句操作的。,CREATETABLEstudent.学生(学号char(8)PRIMARYKEY,院系ASCONVERT(Tinyint,SUBSTRING(学号,3,2)PERSISTEDNOTNULLFOREIGNKEYREFERENCESstudent.院系(编号),姓名char(10)NOTNULL,性别char(2)CHECK(性别=男OR性别=女),生源char(6),状态char(4)CHECK(状态IN(正常,留级,休学,退学),INSERTINTOstudent.学生(学号,院系,姓名,性别,生源,状态)VALUES(04014111,1,程鸣,女,湖南,正常),INSERTINTOstudent.学生VALUES(04014111,程鸣,女,湖南,正常),正确语句：,.,.,.,例17,INSERTINTOstudent.学生VALUES(04074122,吴红霞,女,浙江,正常),CREATETABLEstudent.学生(学号char(8)PRIMARYKEY,院系ASCONVERT(Tinyint,SUBSTRING(学号,3,2)PERSISTEDNOTNULLFOREIGNKEYREFERENCESstudent.院系(编号),姓名char(10)NOTNULL,性别char(2)CHECK(性别=男OR性别=女),生源char(6),状态char(4)CHECK(状态IN(正常,留级,休学,退学),违背参照完整性约束,.,.,例18,INSERTINTOstudent.学生VALUES(04026205,张鸿飞,南,北京,正常),CREATETABLEstudent.学生(学号char(8)PRIMARYKEY,院系ASCONVERT(Tinyint,SUBSTRING(学号,3,2)PERSISTEDNOTNULLFOREIGNKEYREFERENCESstudent.院系(编号),姓名char(10)NOTNULL,性别char(2)CHECK(性别=男OR性别=女),生源char(6),状态char(4)CHECK(状态IN(正常,留级,休学,退学),违背域完整性约束,.,.,CREATETABLEstudent.教师(教师编号char(6)PRIMARYKEY,院系ASCONVERT(Tinyint,SUBSTRING(教师编号,1,2)PERSISTEDNOTNULLFOREIGNKEYREFERENCESstudent.院系(编号),姓名char(10)NOTNULL,性别char(2)CHECK(性别=男OR性别=女),职称char(6)CHECK(职称IN(教授,副教授,讲师,助教),专业char(16),插入教师表记录，注意教师编号的PRIMARYKEY约束，院系是计算列及其REFRENCES约束等。,.,CREATETABLEstudent.课程(课程编号char(6)PRIMARYKEY,课程名称char(24)NOTNULL,责任教师char(6)CONSTRAINTduty_teacherFOREIGNKEYREFERENCESstudent.教师(教师编号),学时tinyintNOTNULL,课程性质char(8)CHECK(课程性质IN(公共基础,专业基础,专业选修,任意选修),插入课程表记录，注意相关约束。,.,例20,CREATETABLEstudent.选课(学号char(8)FOREIGNKEYREFERENCESstudent.学生,课程编号char(6)FOREIGNKEYREFERENCESstudent.课程,考试成绩tinyintCHECK(考试成绩BETWEEN0AND100)DEFAULTNULLPRIMARYKEY(学号,课程编号),INSERTINTOstudent.选课(学号,课程编号,考试成绩)VALUES(04014122,B00016,88),.,.,例21,CREATETABLEstudent.选课(学号char(8)FOREIGNKEYREFERENCESstudent.学生,课程编号char(6)FOREIGNKEYREFERENCESstudent.课程,考试成绩tinyintCHECK(考试成绩BETWEEN0AND100)DEFAULTNULLPRIMARYKEY(学号,课程编号),INSERTINTOstudent.选课VALUES(04014111,X00201,NULL),.,.,例22,CREATETABLEstudent.选课(学号char(8)FOREIGNKEYREFERENCESstudent.学生,课程编号char(6)FOREIGNKEYREFERENCESstudent.课程,考试成绩tinyintCHECK(考试成绩BETWEEN0AND100)DEFAULTNULLPRIMARYKEY(学号,课程编号),INSERTINTOstudent.选课VALUES(04014111,B00016,DEFAULT),.,.,例23,CREATETABLEstudent.选课(学号char(8)FOREIGNKEYREFERENCESstudent.学生,课程编号char(6)FOREIGNKEYREFERENCESstudent.课程,考试成绩tinyintCHECK(考试成绩BETWEEN0AND100)DEFAULTNULLPRIMARYKEY(学号,课程编号),INSERTINTOstudent.选课(学号,课程编号)VALUES(04014101,B00018),.,.,例24违反参照完整性的输入,CREATETABLEstudent.选课(学号char(8)FOREIGNKEYREFERENCESstudent.学生,课程编号char(6)FOREIGNKEYREFERENCESstudent.课程,考试成绩tinyintCHECK(考试成绩BETWEEN0AND100)DEFAULTNULLPRIMARYKEY(学号,课程编号),INSERTINTOstudent.选课VALUES(04016116,B00017，52),.,.,.,插入操作时检查完整性？,检查实体完整性规则检查参照完整性规则检查用户定义完整性规则,.,检查实体完整性规则,如果插入元组的主关键字的属性不为空值、并且相应的属性值在关系中不存在（即保持唯一性），则可以执行插入操作，否则不可以执行插入操作。,.,检查参照完整性规则,如果是向被参照关系插入元组，则无须检查参照完整性；如果是向参照关系插入元组，则要检查外部关键字属性上的值是否在被参照关系中存在对应的主关键字的值，如果存在则可以执行插入操作，否则不允许执行插入操作。另外，如果插入元组的外部关键字允许为空值，则当外部关键字是空值时也允许执行插入操作。,.,检查用户定义完整性规则,如果插入的元组在相应的属性值上遵守了用户定义完整性规则(包括CHECK约束、UNIQUE约束、NULL或NOTNULL约束)，则可以执行插入操作，否则不可以执行插入操作。,.,删除操作及其完整性约束,SQL的删除语句是DELETE，常用格式：DELETEFROMschema_name.table_nameWHERE其中：schema_name指出模式名；table_name指出从哪个表删除记录；用来指出删除记录的条件，默认是删除全部记录。,.,例25,DELETEstudent.学生WHERE姓名=吴红霞,删除操作失败，违背参照完整性,CREATETABLEstudent.选课(学号char(8)FOREIGNKEYREFERENCESstudent.学生,课程编号char(6)FOREIGNKEYREFERENCESstudent.课程,考试成绩tinyintCHECK(考试