数据库和表的操作.ppt

1,结构化查询语言SQL,主要内容SQL概述SQL的数据定义SQL的数据查询数据更新嵌入式SQLTransactSQL语言概述,2,本章概述,结构化查询语言SQL（StructuredQueryLanguage）是关系数据库的标准语言，对关系模型的发展和商用DBMS的研制起着重要的作用。SQL语言是介乎于关系代数和元组演算之间的一种语言。接下来介绍SQL的核心部分内容：数据定义、数据查询、数据更新和嵌入式SQL。,3,SQL概述,主要内容SQL的产生与发展SQL的组成及特点,4,SQL的产生与发展,1972年，IBM公司开始研制实验型关系数据库管理系统SYSTEMR，其配备的查询语言称为SQUARE(SpecifyingQueriesAsRelationalExpression)语言，语言中使用了较多的数学符号。1974年，Boyce和Chamberlin把SQUARE修改为SEQUEL(StructuredEnglishQueryLanguage)语言。后来SEQUEL简称为SQL(StructuredQueryLanguage)，即“结构式查询语言”，SQL的发音仍“sequel”。现在SQL已经成为一个标准。SQL有两个标准：ANSISQL和1992年通过的修改版本SQL-92（简称SQL2）。还有一个新的标准SQL3，它扩充了SQL2，引入了递归、触发器和对象等概念和机制。,5,SQL的组成及特点,SQL语言从功能上可以分为四部分：数据查询（DataQuery）、数据操纵（DataManipulation）、数据定义（DataDefinition）和数据控制（DataControl）。数据定义语言，即SQLDDL，用于定义SQL模式、基本表、视图、索引等结构。数据操纵语言，即SQLDML。数据操纵分成数据查询和数据更新两类。其中数据更新又分成插入、删除和修改三种操作。数据控制语言，即SQLDCL，这一部分包括对基本表和视图的授权、完整性规则的描述、事务控制等内容。SQL的核心部分相当于关系代数，同时又具有关系代数所没有的许多特点，如聚集、数据库更新等。,6,特点：综合统一高度非过程化SQL是一种第四代语言（4GL），用户只需提出“干什么”，无需具体指明“怎么干”，像存取路径选择和具体处理操作等，均有系统自动完成。统一的语法结构SQL有两种使用方式：一是联机交互使用，另一种是嵌入到某种高级语言程序设计语言中去使用。语言简洁,SQL语言的基本组成,SQL语言支持关系数据库的三级模式结构，如下图所示。,8,SQL的核心动词,9,SQL的数据定义,主要内容数据库的创建与撤销定义、修改与删除基本表建立与删除索引-稍后创建与删除视图-稍后,10,SQL的数据定义功能包括数据库定义、表定义、视图和索引的定义。,11,学生表：S(SNO,SNAME,SEX,AGE,SDEPT)课程表：C(CNO,CNAME,CPNO,CCREDIT)学生选课表：SC(SNO,CNO,GRADE),学生表实例,例1定义一个教务管理数据库JWGL，它包含3个表：,本章数据库示例（教务管理数据库）,12,课程表实例,学生表选课表实例,13,数据库的创建与撤销,数据库的创建在SQL2000中，一个数据库模式被定义为基本表的集合。对数据库的创建可用CREATE语句实现，其句法如下：CREATEDATABASEAUTHORIZATION其中：AUTHORIZATION是指该用户获得授予创建和使用数据库的权限。例如，下面语句定义了教务管理数据库的模式：CREATEDATABASEJWGLAUTHORIZATIONsa创建的数据库名为JWGL，拥有者为sa。,14,数据库的撤消当一个数据库及其所属的基本表、视图等元素都不需要时，可以用DROP语句撤消这个数据库。DROP语句的句法如下：DROPDATABASECASCADE|RESTRICT撤消的方式有以下两种:CASCADE（连锁式）方式：执行DROP语句时，把数据库及其下属的基本表、视图、索引等所有元素全部撤消。RESTRICT（约束式）方式：执行DROP语句时，只有当数据库中没有任何下属元素时，才能撤消数据库，否则拒绝执行DROP语句。例如，要撤消数据库JWGL及其下属所有的元素，可用下列语句实现：DROPDATABASEJWGLCASCADE,15,定义、修改与删除基本表,数据类型一个属性选用哪种数据类型要根据实际情况来决定，一般要从两个方面来考虑，一是取值范围，二是要做哪些运算。,定义表-确定表的各个属性-确定每个属性域（取值范围）,16,定义基本表定义基本表，就是创建基本表的结构。其一般格式为：CREATETABLE（列级完整性约束条件，列级完整性约束条件，）；建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由RDBMS自动检查该操作是否违背这些完整性约束条件。,17,例2建立一个“学生”表SCREATETABLES(SNOCHAR(9)PRIMARYKEY,/*列级完整性约束条件，SNO是主码*/SNAMECHAR(20)UNIQUE,/*SNAME取唯一值*/SEXCHAR(1),AGESMALLINT,SDEPTCHAR(20);系统执行上面的CREATETABLE语句后，就在数据库中建立一个新的空的“学生”表S，并将有关“学生”表的定义及有关约束条件存放在数据字典中。,18,例4建立学生选课表SCCREATETABLESC(SNOCHAR(9),CNOCHAR(4),GRADESMALLINT,PRIMARYKEY(SNO,CNO),/*主码由两个属性构成，必须作为表级完整性进行定义*/FOREIGNKEY(SNO)REFERENCESS(SNO),/*表级完整性约束条件，SNO是外码，被参照表是S*/FOREIGNKEY(CNO)REFERENCESC(CNO)/*表级完整性约束条件，CNO是外码，被参照表是C*/);,修改基本表SQL语言用ALTERTABLE语句修改基本结构表，其一般格式为：ALTERTABLEADD完整性约束DROPALTERCOLUMN；其中是要修改的基本表。ADD子句用于增加新列和新的完整性约束条件；DROP子句用于删除指定的完整性约束条件；ALTERCOLUMN子句用于修改原有的列定义。包括修改列名和数据类型。例5向S表增加“入学时间”列，其数据类型为日期型。ALTERTABLESADDS_ENTRANCEDATE;不论基本表中原来是否已有数据，新增加的列一律为空值。,20,例6altertablesaltercolumnageint；例7altertablecaddunique(cname)；,21,删除属性列(1)间接删除把表中要保留的列及其内容复制到一个新表中删除原表再将新表重命名为原表名(2)直接删除例：ALTERTABLEStudentDropScome；,完整性约束条件*,一、完整性的概念数据完整性是指存储在数据库中的数据正确无误并且相关数据具有一致性。引入数据完整性是为了在数据的添加、删除、修改等操作中不出现数据的破坏或多个表数据不一致。,23,在SQLServer中，根据数据完整措施所作用的数据库对象和范围不同，可以将数据完整性分为以下几种：1、实体完整性（主键、唯一性约束）2、参照完整性（外键约束）3、域完整性（check、默认值约束）4、用户定义的完整性,24,二、完整性的类型,1）实体完整性实体完整性：在表中不能存在完全相同的记录，且每条记录都要具有一个非空且不重复的主键值。实现实体完整性的方法：设置主键、惟一约束、惟一索引2）参照完整性参照完整性：又称为引用完整性。是指通过主键与外键相联系的两个表或两个以上的表，相关字段的值要保持一致。实现实体完整性的方法：外键约束,3）域完整性域完整性：向表中添加的数据必须与数据类型、格式及有效的数据长度相匹配。实现域完整性的方法：CHECK约束、外键约束、默认约束、非空定义、规则以及在建表时设置的数据类型4）用户定义的完整性用户定义的完整性：是根据具体的应用领域所要遵循的约束条件由用户自己定义的特定的规则。,26,三、约束的类型,约束：SQLServer提供的自动强制数据完整性的一种方法。它通过定义列的取值规则来维护数据的完整性。当完整性约束创建之后，它作为基本表定义的一部分，存入DD中。列级完整性约束：是行定义的一部分，只能应用在一列上；表级完整性约束：它的定义独立于列的定义，可应用在一个基本表中的多列上。当需要在一个基本表中的多列上建立约束条件时，只能建立表级约束。常用约束：NOTNULL、CHECK、UNIQUE、PRIMARYKEY、FOREIGNKEY、DEFAULT,27,1、主键(PRIMARYKEY)约束,主键约束：在表中定义一个主键来惟一标识表中的每行记录每个表中只能有一个主键，主键可是一列，也可是多列；主键不能为空；主键值不能重复不能定义TEXT或IMAGE数据类型的字段列为主键。,28,PRIMARYKEY约束可在列级或表级上进行定义，但不允许同时在两个级别上进行定义。创建方法：PRIMARYKEY约束直接写在列名及其类型之后；按照语法在相应列名及类型后单独列出：CONSTRAINTPRIMARYKEY在CREATETABLE语句列出基本表的所有列之后，PRIMARYKEY(，),29,SSMS创建主键的步骤,30,创建表时定义主键,例1：列级约束CREATETABLETb_Class(ClassIdchar(8)PRIMARYKEY,ClassNamevarchar(30)NOTNULL,DeptIdchar(2)NOTNULL,ClassStuNumberint);,31,例2：CREATETABLEC(cnochar(6)constraintPK_cnoprimarykey,cnamechar(20),ccreditsmallint,cpnochar(6);,32,例3：CREATETABLETb_Class(ClassIdchar(8)NOTNULL,ClassNamevarchar(30)NOTNULL,DeptIdchar(2)NOTNULL,ClassStuNumberint,CONSTRAINTPK_ClassIdPRIMARYKEY(ClassId);,33,向已有表中添加主键,先创建一个无主键的表CREATETABLETb_Class(ClassIdchar(8)NOTNULL,ClassNamevarchar(30)NOTNULL,DeptIdchar(2)NOTNULL,ClassStuNumberint)-再给相应的列（ClassId）添加主键约束ALTERTABLETb_ClassADDCONSTRAINTPK_ClassIdPRIMARYKEY(ClassId),34,删除主键约束ALTERTABLETb_ClassDROPCONSTRAINTPK_ClassIdPRIMARYKEY(ClassId),35,2、外键（ForeignKey）约束,外键：出于某种关联的需要，当一个表需要引用另一个表的主键作为自己的一个字段时，称这个引用来的字段为外键。外键约束：定义了表之间的关系，主要用来维护两个表之间的一致性。当主键更新或删除时，其它所有表中与这个主键关联的外键也将被相应的更新或删除。当向外键所在的表插入或更新数据（外键字段）时，如果与外键表相关联的主键表的主键中无与插入或更新的外键有相同的值时，系统会报错并拒绝插入或更新数据。,外键约束说明,SSMS设置外键约束,39,40,41,在创建表时定义外键约束,CREATETABLETb_Dept(DeptIdchar(2)PRIMARYKEY,DeptNamevarchar(16)NOTNULL)-CREATETABLETb_Student(StuIdchar(10)PRIMARYKEY,StuNamechar(8)NOTNULL,DeptIdchar(2)NOTNULL,CONSTRAINTFK_DeptIdFOREIGNKEY(DeptId)REFERENCESTb_Dept(DeptId),42,上述创建外键约束的语句可以简化为如下形式：-CREATETABLETb_Student(StuIdchar(10)PRIMARYKEY,StuNamechar(8)NOTNULL,DeptIdchar(2)NOTNULLREFERENCESTb_Dept)-从添加外键约束时只需添加REFERENCES关键字和被引用的表名。（需要注意的是，外键列和被引用表中的主键列应该名称相同，且具有相同的列数据类型）。,在创建表时定义外键约束,43,级联更新、删除,CREATETABLETb_Student(StuIdchar(10)PRIMARYKEY,StuNamechar(8)NOTNULL,DeptIdchar(2)NOTNULLREFERENCESTb_DeptONUPDATECASCADEONDELETECASCADE),44,CREATETABLETb_Student(StuIdchar(10)PRIMARYKEY,StuNamechar(8)NOTNULL,DeptIdchar(2)NOTNULL,CONSTRAINTFK_DeptIdFOREIGNKEY(DeptId)REFERENCESTb_Dept(DeptId)ONUPDATECASCADEONDELETECASCADE),45,添加和删除外键约束,先创建一个无外键的表CREATETABLETb_Student(StuIdchar(10)PRIMARYKEY,StuNamechar(8)NOTNULL,DeptIdchar(2)NOTNULL)再给相应的列（DeptId）添加外键约束ALTERTABLETb_StudentADDCONSTRAINTFK_DeptIdFOREIGNKEY(DeptId)REFERENCESTb_Dept(DeptId),46,删除外键约束ALTERTABLETb_StudentDROPCONSTRAINTFK_DeptId,47,参照完整性（通过外键约束来实现）,参照完整性是指两个表的主关键字（PRIMARYKEY）和外关键字（FOREIGNKEY）的数据要对应一致。它确保了有主关键字的表中对应其它表的外关键字的行存在，即保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。参照完整性是建立在外关键字和主关键字之上的。在参照表（从表）中，不允许参照不存在的主键值。如果主键值更改了，那么在整个数据库中，对该键值的所有引用要进行一致的更改。在被参照表（主表）中，当其主键值被其他表所参照时，该行不能被删除也不允许改变。,48,外键约束的作用,在子表中插入数据时检查（外键）修改父表主键时检查删除父表记录时检查,49,3、唯一性（UNIQUE）约束,可以使用UNIQUE约束确保在非主键列中不输入重复的值。尽管UNIQUE约束和PRIMARYKEY约束都强制唯一性，但想要强制一列或多列组合（不是主键）的唯一性时应使用UNIQUE约束而不是PRIMARYKEY约束。可以对一个表定义多个UNIQUE约束，但只能定义一个PRIMARYKEY约束。UNIQUE约束允许NULL值，这与PRIMARYKEY约束不同。不过，当与参与UNIQUE约束的任何值一起使用时，每列只允许一个空值。FOREIGNKEY约束可以引用UNIQUE约束。,50,创建表时，可以创建UNIQUE约束作为表定义的一部分。如果表已经存在，可以添加UNIQUE约束（假设组成UNIQUE约束的列或列组合仅包含唯一的值）。默认情况下，向表中的现有列添加UNIQUE约束后，SQLServer2005DatabaseEngine将检查列中的现有数据，以确保所有值都是唯一的。如果向含有重复值的列添加UNIQUE约束，数据库引擎将返回错误消息，并且不添加约束。若要修改UNIQUE约束，必须首先删除现有的UNIQUE约束，然后用新定义重新创建。除非显式指定了聚集索引，否则，默认情况下将创建唯一的非聚集索引以强制执行UNIQUE约束。,唯一性约束,51,创建表时创建UNIQUE约束,CREATETABLETb_Student(StuIdchar(10)PRIMARYKEY,StuNamechar(8)NOTNULLUNIQUENONCLUSTERED,DeptIdchar(2)NOTNULLREFERENCESTb_Dept)-CREATETABLETb_Student(StuIdchar(10)PRIMARYKEY,StuNamechar(8)NOTNULL,DeptIdchar(2)NOTNULLREFERENCESTb_Dept,CONSTRAINTUK_StuNameUNIQUE(StuName),52,创建表结束后添加、删除UNIQUE约束,CREATETABLETb_Student(StuIdchar(10)PRIMARYKEY,StuNamechar(8)NOTNULL,DeptIdchar(2)NOTNULLREFERENCESTb_Dept)-添加UNIQUE约束ALTERTABLETb_StudentADDCONSTRAINTUK_DepNameUNIQUE(DeptName),53,删除UNIQUE约束ALTERTABLETb_StudentDROPCONSTRAINTUK_DepName,SSMS创建惟一约束,55,4、默认值约束,如果插入行时没有为列指定值，默认值则指定列中使用什么值。默认值可以是计算结果为常量的任何值，例如常量、内置函数或数学表达式。若要应用默认值，可以通过在CREATETABLE中使用DEFAULT关键字来创建默认值定义。默认值必须与要应用DEFAULT定义的列的数据类型相配。例如，int列的默认值必须是整数，而不能是字符串。,56,CREATETABLETb_Student(StuIdchar(10)PRIMARYKEY,StuNamechar(8)NOTNULL,DeptIdchar(2)NOTNULLREFERENCESTb_Dept,Sexchar(2)NOTNULLDEFAULTM,BIRTHDAYSMALLDATETIMENOTNULLDEFAULTgetdate(),默认值约束,57,添加默认值约束ALTERTAB