数据库和表的操作.ppt

《数据库和表的操作.ppt》由会员分享，可在线阅读，更多相关《数据库和表的操作.ppt（68页珍藏版）》请在三一办公上搜索。

1、1,结构化查询语言SQL,主要内容SQL概述 SQL的数据定义SQL的数据查询 数据更新 嵌入式SQL Transact SQL语言概述,2,本章概述,结构化查询语言SQL（Structured Query Language）是关系数据库的标准语言，对关系模型的发展和商用DBMS的研制起着重要的作用。SQL语言是介乎于关系代数和元组演算之间的一种语言。接下来介绍SQL的核心部分内容：数据定义、数据查询、数据更新和嵌入式SQL。,3,SQL概述,主要内容SQL的产生与发展SQL的组成及特点,4,SQL的产生与发展,1972年，IBM公司开始研制实验型关系数据库管理系统SYSTEM R，其配备的查

2、询语言称为SQUARE(Specifying Queries As Relational Expression)语言，语言中使用了较多的数学符号。1974年，Boyce和Chamberlin把SQUARE修改为SEQUEL(Structured English Query Language)语言。后来SEQUEL简称为SQL(Structured Query Language)，即“结构式查询语言”，SQL的发音仍“sequel”。现在SQL已经成为一个标准。SQL有两个标准：ANSI SQL和1992年通过的修改版本SQL-92（简称 SQL2）。还有一个新的标准SQL3，它扩充了SQL2，

3、引入了递归、触发 器和对象等概念和机制。,5,SQL的组成及特点,SQL语言从功能上可以分为四部分：数据查询（Data Query）、数据操纵（Data Manipulation）、数据定义（Data Definition）和数据控制（Data Control）。数据定义语言，即SQL DDL，用于定义SQL模式、基本表、视图、索引等结构。数据操纵语言，即SQL DML。数据操纵分成数据查询和数据更新两类。其中数据更新又分成插入、删除和修改三种操作。数据控制语言，即SQL DCL，这一部分包括对基本表和视图的授权、完整性规则的描述、事务控制等内容。SQL的核心部分相当于关系代数，同时又具有关系

4、代数所没有的许多特点，如聚集、数据库更新等。,6,特点：综合统一高度非过程化SQL是一种第四代语言（4GL），用户只需提出“干什么”，无需具体指明“怎么干”，像存取路径选择和具体处理操作等，均有系统自动完成。统一的语法结构SQL有两种使用方式：一是联机交互使用，另一种是嵌入到某种高级语言程序设计语言中去使用。语言简洁,SQL语言的基本组成,SQL语言支持关系数据库的三级模式结构，如下图所示。,8,SQL的核心动词,9,SQL的数据定义,主要内容 数据库的创建与撤销 定义、修改与删除基本表 建立与删除索引-稍后 创建与删除视图-稍后,10,SQL的数据定义功能包括数据库定义、表定义、视图和索引的

5、定义。,11,学生表：S(SNO,SNAME,SEX,AGE,SDEPT)课程表：C(CNO,CNAME,CPNO,CCREDIT)学生选课表：SC(SNO,CNO,GRADE),学生表实例,例1 定义一个教务管理数据库JWGL，它包含3个表：,本章数据库示例（教务管理数据库）,12,课程表实例,学生表选课表实例,13,数据库的创建与撤销,数据库的创建 在SQL2000中，一个数据库模式被定义为基本表的集合。对数据库的创建可用CREATE语句实现，其句法如下：CREATE DATABASE AUTHORIZATION 其中：AUTHORIZATION 是指该用户获得授予创建和使用数据库的权限。

6、例如，下面语句定义了教务管理数据库的模式：CREATE DATABASE JWGL AUTHORIZATION sa 创建的数据库名为JWGL，拥有者为sa。,14,数据库的撤消 当一个数据库及其所属的基本表、视图等元素都不需要时，可以用DROP语句撤消这个数据库。DROP语句的句法如下：DROP DATABASE CASCADE|RESTRICT撤消的方式有以下两种:CASCADE（连锁式）方式：执行DROP语句时，把数据库及其下属的基本表、视图、索引等所有元素全部撤消。RESTRICT（约束式）方式：执行DROP语句时，只有当数据库中没有任何下属元素时，才能撤消数据库，否则拒绝执行DROP

7、语句。例如，要撤消数据库JWGL及其下属所有的元素，可用下列语句实现：DROP DATABASE JWGL CASCADE,15,定义、修改与删除基本表,数据类型 一个属性选用哪种数据类型要根据实际情况来决定，一般要从两个方面来考虑，一是取值范围，二是要做哪些运算。,定义表-确定表的各个属性-确定每个属性域（取值范围）,16,定义基本表 定义基本表，就是创建基本表的结构。其一般格式为：CREATE TABLE（列级完整性约束条件，列级完整性约束条件，）；建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由RDBMS自动检查该操作是

8、否违背这些完整性约束条件。,17,例2 建立一个“学生”表S CREATE TABLE S(SNO CHAR(9)PRIMARY KEY,/*列级完整性约束条件，SNO 是主码*/SNAME CHAR(20)UNIQUE,/*SNAME取唯一值*/SEX CHAR(1),AGE SMALLINT,SDEPT CHAR(20);系统执行上面的CREATE TABLE语句后，就在数据库中建立一个新的空的“学生”表S，并将有关“学生”表的定义及有关约束条件存放在数据字典中。,18,例4 建立学生选课表SC CREATE TABLE SC(SNO CHAR(9),CNO CHAR(4),GRADE S

9、MALLINT,PRIMARY KEY(SNO,CNO),/*主码由两个属性构成，必须作 为表级完整性进行定义*/FOREIGN KEY(SNO)REFERENCES S(SNO),/*表级完整性约束条件，SNO是外码，被参照表是S*/FOREIGN KEY(CNO)REFERENCES C(CNO)/*表级完整性约束条件，CNO是外码，被参照表是C*/);,修改基本表 SQL语言用ALTER TABLE语句修改基本结构表，其一般格式为：ALTER TABLE ADD 完整性约束 DROP ALTER COLUMN；其中是要修改的基本表。ADD子句用于增加新列和新的完整性约束条件；DROP子句

10、用于删除指定的完整性约束条件；ALTER COLUMN 子句用于修改原有的列定义。包括修改列名和数据类型。例5 向S表增加“入学时间”列，其数据类型为日期型。ALTER TABLE S ADD S_ENTRANCE DATE;不论基本表中原来是否已有数据，新增加的列一律为空值。,20,例6 alter table s alter column age int；例7 alter table c add unique(cname)；,21,删除属性列(1)间接删除 把表中要保留的列及其内容复制到一个新表中 删除原表 再将新表重命名为原表名(2)直接删除 例：ALTER TABLE Student

11、Drop Scome；,完整性约束条件*,一、完整性的概念数据完整性是指存储在数据库中的数据正确无误并且相关数据具有一致性。引入数据完整性是为了在数据的添加、删除、修改等操作中不出现数据的破坏或多个表数据不一致。,23,在SQL Server 中，根据数据完整措施所作用的数据库对象和范围不同，可以将数据完整性分为以下几种：1、实体完整性（主键、唯一性约束）2、参照完整性（外键约束）3、域完整性（check、默认值约束）4、用户定义的完整性,24,二、完整性的类型,1）实体完整性实体完整性：在表中不能存在完全相同的记录，且每条记录都要具有一个非空且不重复的主键值。实现实体完整性的方法：设置主键、

12、惟一约束、惟一索引2）参照完整性参照完整性：又称为引用完整性。是指通过主键与外键相联系的两个表或两个以上的表，相关字段的值要保持一致。实现实体完整性的方法：外键约束,3）域完整性域完整性：向表中添加的数据必须与数据类型、格式及有效的数据长度相匹配。实现域完整性的方法：CHECK约束、外键约束、默认约束、非空定义、规则以及在建表时设置的数据类型4）用户定义的完整性用户定义的完整性：是根据具体的应用领域所要遵循的约束条件由用户自己定义的特定的规则。,26,三、约束的类型,约束：SQL Server提供的自动强制数据完整性的一种方法。它通过定义列的取值规则来维护数据的完整性。当完整性约束创建之后，它

13、作为基本表定义的一部分，存入DD中。列级完整性约束：是行定义的一部分，只能应用在一列上；表级完整性约束：它的定义独立于列的定义，可应用在一个基本表中的多列上。当需要在一个基本表中的多列上建立约束条件时，只能建立表级约束。常用约束：NOT NULL、CHECK、UNIQUE、PRIMARY KEY、FOREIGN KEY、DEFAULT,27,1、主键(PRIMARY KEY)约束,主键约束：在表中定义一个主键来惟一标识表中的每行记录每个表中只能有一个主键，主键可是一列，也可是多列；主键不能为空；主键值不能重复不能定义TEXT或IMAGE数据类型的字段列为主键。,28,PRIMARY KEY约束

14、可在列级或表级上进行定义，但不允许同时在两个级别上进行定义。创建方法：PRIMARY KEY约束直接写在列名及其类型之后；按照语法在相应列名及类型后单独列出：CONSTRAINT PRIMARY KEY 在CREATE TABLE语句列出基本表的所有列之后，PRIMARY KEY(，),29,SSMS创建主键的步骤,30,创建表时定义主键,例1：列级约束CREATE TABLE Tb_Class(ClassId char(8)PRIMARY KEY,ClassName varchar(30)NOT NULL,DeptId char(2)NOT NULL,ClassStuNumber int);

15、,31,例2：CREATE TABLE C(cno char(6)constraint PK_cno primary key,cname char(20),ccredit smallint,cpno char(6);,32,例3：CREATE TABLE Tb_Class(ClassId char(8)NOT NULL,ClassName varchar(30)NOT NULL,DeptId char(2)NOT NULL,ClassStuNumber int,CONSTRAINT PK_ClassId PRIMARY KEY(ClassId);,33,向已有表中添加主键,先创建一个无主键的表

16、CREATE TABLE Tb_Class(ClassId char(8)NOT NULL,ClassName varchar(30)NOT NULL,DeptId char(2)NOT NULL,ClassStuNumber int)-再给相应的列（ClassId）添加主键约束ALTER TABLE Tb_ClassADD CONSTRAINT PK_ClassId PRIMARY KEY(ClassId),34,删除主键约束ALTER TABLE Tb_ClassDROP CONSTRAINT PK_ClassId PRIMARY KEY(ClassId),35,2、外键（Foreign

17、Key）约束,外键：出于某种关联的需要，当一个表需要引用另一个表的主键作为自己的一个字段时，称这个引用来的字段为外键。外键约束：定义了表之间的关系，主要用来维护两个表之间的一致性。当主键更新或删除时，其它所有表中与这个主键关联的外键也将被相应的更新或删除。当向外键所在的表插入或更新数据（外键字段）时，如果与外键表相关联的主键表的主键中无与插入或更新的外键有相同的值时，系统会报错并拒绝插入或更新数据。,外键约束说明,SSMS设置外键约束,39,40,41,在创建表时定义外键约束,CREATE TABLE Tb_Dept(DeptId char(2)PRIMARY KEY,DeptName var

18、char(16)NOT NULL)-CREATE TABLE Tb_Student(StuId char(10)PRIMARY KEY,StuName char(8)NOT NULL,DeptId char(2)NOT NULL,CONSTRAINT FK_DeptId FOREIGN KEY(DeptId)REFERENCES Tb_Dept(DeptId),42,上述创建外键约束的语句可以简化为如下形式：-CREATE TABLE Tb_Student(StuId char(10)PRIMARY KEY,StuName char(8)NOT NULL,DeptId char(2)NOT N

19、ULL REFERENCES Tb_Dept)-从添加外键约束时只需添加REFERENCES关键字和被引用的表名。（需要注意的是，外键列和被引用表中的主键列应该名称相同，且具有相同的列数据类型）。,在创建表时定义外键约束,43,级联更新、删除,CREATE TABLE Tb_Student(StuId char(10)PRIMARY KEY,StuName char(8)NOT NULL,DeptId char(2)NOT NULL REFERENCES Tb_Dept ON UPDATE CASCADE ON DELETE CASCADE),44,CREATE TABLE Tb_Studen

20、t(StuId char(10)PRIMARY KEY,StuName char(8)NOT NULL,DeptId char(2)NOT NULL,CONSTRAINT FK_DeptId FOREIGN KEY(DeptId)REFERENCES Tb_Dept(DeptId)ON UPDATE CASCADE ON DELETE CASCADE),45,添加和删除外键约束,先创建一个无外键的表CREATE TABLE Tb_Student(StuId char(10)PRIMARY KEY,StuName char(8)NOT NULL,DeptId char(2)NOT NULL)再给

21、相应的列（DeptId）添加外键约束ALTER TABLE Tb_StudentADD CONSTRAINT FK_DeptId FOREIGN KEY(DeptId)REFERENCES Tb_Dept(DeptId),46,删除外键约束ALTER TABLE Tb_StudentDROP CONSTRAINT FK_DeptId,47,参照完整性（通过外键约束来实现）,参照完整性是指两个表的主关键字（PRIMARY KEY）和外关键字（FOREIGN KEY）的数据要对应一致。它确保了有主关键字的表中对应其它表的外关键字的行存在，即保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在

22、数据库中扩散。参照完整性是建立在外关键字和主关键字之上的。在参照表（从表）中，不允许参照不存在的主键值。如果主键值更改了，那么在整个数据库中，对该键值的所有引用要进行一致的更改。在被参照表（主表）中，当其主键值被其他表所参照时，该行不能被删除也不允许改变。,48,外键约束的作用,在子表中插入数据时检查（外键）修改父表主键时检查删除父表记录时检查,49,3、唯一性（UNIQUE）约束,可以使用 UNIQUE 约束确保在非主键列中不输入重复的值。尽管 UNIQUE 约束和 PRIMARY KEY 约束都强制唯一性，但想要强制一列或多列组合（不是主键）的唯一性时应使用 UNIQUE 约束而不是 PR

23、IMARY KEY 约束。可以对一个表定义多个 UNIQUE 约束，但只能定义一个 PRIMARY KEY 约束。UNIQUE 约束允许 NULL 值，这与 PRIMARY KEY 约束不同。不过，当与参与 UNIQUE 约束的任何值一起使用时，每列只允许一个空值。FOREIGN KEY 约束可以引用 UNIQUE 约束。,50,创建表时，可以创建 UNIQUE 约束作为表定义的一部分。如果表已经存在，可以添加 UNIQUE 约束（假设组成 UNIQUE 约束的列或列组合仅包含唯一的值）。默认情况下，向表中的现有列添加 UNIQUE 约束后，SQL Server 2005 Database E

24、ngine将检查列中的现有数据，以确保所有值都是唯一的。如果向含有重复值的列添加 UNIQUE 约束，数据库引擎将返回错误消息，并且不添加约束。若要修改 UNIQUE 约束，必须首先删除现有的 UNIQUE 约束，然后用新定义重新创建。除非显式指定了聚集索引，否则，默认情况下将创建唯一的非聚集索引以强制执行 UNIQUE 约束。,唯一性约束,51,创建表时创建UNIQUE约束,CREATE TABLE Tb_Student(StuId char(10)PRIMARY KEY,StuName char(8)NOT NULL UNIQUE NONCLUSTERED,DeptId char(2)NO

25、T NULL REFERENCES Tb_Dept)-CREATE TABLE Tb_Student(StuId char(10)PRIMARY KEY,StuName char(8)NOT NULL,DeptId char(2)NOT NULL REFERENCES Tb_Dept,CONSTRAINT UK_StuName UNIQUE(StuName),52,创建表结束后添加、删除UNIQUE约束,CREATE TABLE Tb_Student(StuId char(10)PRIMARY KEY,StuName char(8)NOT NULL,DeptId char(2)NOT NULL

26、 REFERENCES Tb_Dept)-添加UNIQUE约束ALTER TABLE Tb_StudentADD CONSTRAINT UK_DepName UNIQUE(DeptName),53,删除UNIQUE约束ALTER TABLE Tb_Student DROP CONSTRAINT UK_DepName,SSMS创建惟一约束,55,4、默认值约束,如果插入行时没有为列指定值，默认值则指定列中使用什么值。默认值可以是计算结果为常量的任何值，例如常量、内置函数或数学表达式。若要应用默认值，可以通过在 CREATE TABLE 中使用 DEFAULT 关键字来创建默认值定义。默认值必须与

27、要应用 DEFAULT 定义的列的数据类型相配。例如，int 列的默认值必须是整数，而不能是字符串。,56,CREATE TABLE Tb_Student(StuId char(10)PRIMARY KEY,StuName char(8)NOT NULL,DeptId char(2)NOT NULL REFERENCES Tb_Dept,Sex char(2)NOT NULL DEFAULT M,BIRTHDAY SMALLDATETIME NOT NULL DEFAULT getdate(),默认值约束,57,添加默认值约束ALTER TABLE Tb_StudentADD CONSTRAI

28、NT DEF_Sex DEFAULT M FOR Sex删除默认值约束ALTER TABLE Tb_Student DROP CONSTRAINT DEF_Sex,SSMS中设置默认值约束,59,5、CHECK约束,通过限制列可接受的值，CHECK 约束可以强制域的完整性。CHECK约束类似于 FOREIGN KEY 约束，因为可以控制放入列中的值。但两者在确定有效值的方式上有所不同：FOREIGN KEY 约束从其他表获得有效值列表CHECK 约束通过不基于其他列中的数据的逻辑表达式确定有效值,60,可通过任何基于逻辑运算符返回 TRUE 或 FALSE 的逻辑（布尔）表达式创建 CHECK

29、 约束。例如，逻辑表达式为：grade=0 AND grade=100。可以将多个 CHECK 约束应用于单个列。还可以通过在表级创建 CHECK 约束，将一个 CHECK 约束应用于多个列。自动编号字段不能应用检查约束。,61,创建表时，可以创建 CHECK 约束作为表定义的一部分。如果表已经存在，则可以添加 CHECK 约束。表和列可以包含多个 CHECK 约束。如果 CHECK 约束已经存在，则可以修改或删除该约束。例如，可能需要修改表中某列的 CHECK 约束使用的表达式。注意：必须首先删除现有的 CHECK 约束，然后使用新定义重新创建，才能修改 CHECK 约束。,CHECK约束,

30、62,向现有表中添加 CHECK 约束后，CHECK 约束可以仅应用于新数据，也可以应用于现有数据。默认情况下，CHECK 约束同时应用于现有数据和所有新数据。使用 ALTER TABLE 语句的 WITH NOCHECK 选项可以将新约束仅应用于新添加的数据。如果现有数据已符合新的 CHECK 约束时，或业务规则要求仅从此开始强制约束时，则可以使用此选项。,63,CREATE TABLE Tb_Student(StuId char(10)PRIMARY KEY,StuName char(8)NOT NULL,DeptId char(2)NOT NULL REFERENCES Tb_Dept,

31、Sex char(2)NOT NULL DEFAULT M CHECK(Sex IN(M,F),ZipCode char(6)NOT NULL CHECK(ZipCode like 0-90-90-90-90-90-9),CONSTRAINT CK_StuId CHECK(StuId like S0-90-90-90-90-90-90-90-90-9),创建表时创建CHECK约束,64,其它例子,CHECK(Score=0 and Score 1 AND Sex=F THEN 1 ELSE 0 END),65,添加和删除CHECK约束,添加CHECK约束 ALTER TABLE Tb_Stud

32、ent WITH NOCHECK ADD CONSTRAINT CK_Sex CHECK(Sex IN(M,F);删除CHECK约束ALTER TABLE Tb_Student DROP CONSTRAINT CK_Sex,使用SSMS创建删除CHECK约束,67,NOT NULL或NULL约束,NOT NULL约束不允许列值为空，而NULL约束允许列值为空。,68,删除基本表用DROP TABLE语句删除基本表。其一般格式为：DROP TABLE RESTRICT|CASCADE；缺省情况是RESTRICT。若选择RESTRICT：则该表的删除是有限制条件的。欲删除的基本表不能被其他表的约束所引用（如CHECK，FOREIGN KEY等约束），不能有视图，不能有触发器（trigger），不能有存储过程或函数等。如果存在这些依赖该表的对象，则此表不能被删除。若选择CASCADE：则该表的删除没有限制条件。在删除基本表的同时，相关的依赖对象，例如视图，都将被一起删除。例4.8 删除S表。DROP TABLE S CASCADE;,