资源描述
第3章 关系数据库标准语言SQL第3章 关系数据库标准语言SQL13.1.1 语言的发展及标准化语言的发展及标准化 在在70年代初，年代初，E.F.Codd首先提出了关系模型。首先提出了关系模型。70年代年代中期，中期，IBM公司在研制公司在研制 SYSTEM R关系数据库管理系统关系数据库管理系统中研制了中研制了SQL语言，最早的语言，最早的SQL语言（叫语言（叫SEQUEL2）是在是在1976 年年 11 月的月的IBM Journal of R&D上公布的。上公布的。1979年年ORACLE公司首先提供商用的公司首先提供商用的SQL，IBM公司在公司在DB2 和和SQL/DS数据库系统中也实现了数据库系统中也实现了SQL。1986年年10月，美国月，美国ANSI采用采用SQL作为关系数据库管理作为关系数据库管理系统的标准语言系统的标准语言(ANSI X3.135-1986），后为国际标准），后为国际标准化组织（化组织（ISO）采纳为国际标准。）采纳为国际标准。3.1 SQL语言的基本概念与特点 3.1.1 语言的发展及标准化 在70年代初，E.F.Cod21989年，美国年，美国ANSI采纳在采纳在ANSI X3.135-1989报告中定义报告中定义的关系数据库管理系统的的关系数据库管理系统的SQL标准语言，称为标准语言，称为ANSI SQL 89。1992年，年，ISO又推出了又推出了SQL92标准，也称为标准，也称为SQL2。目前目前SQL99(也称为也称为SQL3)在起草中，增加了面向对象的在起草中，增加了面向对象的功能。功能。结构化查询语言结构化查询语言SQL(Structured Query Language)是一是一种介于关系代数与关系演算之间的语言，其功能包括查询、种介于关系代数与关系演算之间的语言，其功能包括查询、操纵、定义和控制四个方面，是一个通用的、功能极强的操纵、定义和控制四个方面，是一个通用的、功能极强的关系数据库语言。目前已成为关系数据库的标准语言，广关系数据库语言。目前已成为关系数据库的标准语言，广泛应用于各种数据库。泛应用于各种数据库。1989年，美国ANSI采纳在ANSI X3.135-1983一、关系数据库三级模式结构一、关系数据库三级模式结构3.1.2 SQL语言的基本概念语言的基本概念 SQL视图1基本表2视图2基本表3基本表4基本表1存储文件1存储文件2外模式模式内模式一、关系数据库三级模式结构3.1.2 SQL语言的基本4基本表是本身独立存在的表，在基本表是本身独立存在的表，在SQL中一个关系就对应一个中一个关系就对应一个表。一些基本表对应一个存储文件，一个表可以有若干索引，表。一些基本表对应一个存储文件，一个表可以有若干索引，索引也存放在存储文件中。索引也存放在存储文件中。视图是从基本表或其他视图中导出的表，它本身不独立存储视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放在数据库中，也就是说数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。因此视图是一个虚表。存储文件的物理结构及存储方式等组成了关系数据库的内模存储文件的物理结构及存储方式等组成了关系数据库的内模式。存储文件的物理结构及存储方式等不同数据库管理系统式。存储文件的物理结构及存储方式等不同数据库管理系统往往是不同的，一般也是不公开的。往往是不同的，一般也是不公开的。外模式对应于视图（外模式对应于视图（View）和部分基本表（）和部分基本表（Base Table）;模式对应于基本表模式对应于基本表;内模式对应于存储文件。内模式对应于存储文件。视图和基本表是视图和基本表是SQL语言的主要操作对象，用户可以用语言的主要操作对象，用户可以用SQL语言对语言对视图和基本表视图和基本表进行各种操作。在用户眼中，视图和基本进行各种操作。在用户眼中，视图和基本表都是关系表，而存储文件对用户是透明的。表都是关系表，而存储文件对用户是透明的。基本表是本身独立存在的表，在SQL中一个关系就对应一个表。一53.1.3 SQL语言的主要特点语言的主要特点1、综合统一、综合统一SQL集数据定义语言集数据定义语言DDL、数据操纵语言、数据操纵语言DML、数据控制语言、数据控制语言DCL的功的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动，能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动，包括：包括：定义关系模式，插入数据，建立数据库定义关系模式，插入数据，建立数据库;对数据库中的数据进行查询和更新对数据库中的数据进行查询和更新;数据库重构和维护数据库重构和维护;数据库安全性、完整性控制。数据库安全性、完整性控制。SQL集数据查询、数据操纵、数据定义集数据查询、数据操纵、数据定义 和数据控制功能于一体，主和数据控制功能于一体，主要特点包括：要特点包括：3.1.3 SQL语言的主要特点1、综合统一SQL集数据定62、高度非过程化、高度非过程化非关系数据模型的数据操纵语言是非关系数据模型的数据操纵语言是“面向过程面向过程”的，用的，用“过程化过程化”语言语言完成某项请求，必须指定存储路径。完成某项请求，必须指定存储路径。SQL进行数据操作，只要提出进行数据操作，只要提出“做什么做什么”，而无须指明，而无须指明“怎么做怎么做”，因，因此无需了解存储路径。存储路径的选择以及此无需了解存储路径。存储路径的选择以及SQL的操作过程由系统自动完的操作过程由系统自动完成。这样可以减轻用户的负担，也提高了数据独立性。成。这样可以减轻用户的负担，也提高了数据独立性。3、面向集合的操作方式、面向集合的操作方式非关系数据模型得采用提面向记录的操作方式，操作对象是一条记录。非关系数据模型得采用提面向记录的操作方式，操作对象是一条记录。SQL采用集合操作方式，不仅操作对象、查找结果可以是元组的集合，采用集合操作方式，不仅操作对象、查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。而且一次插入、删除、更新操作的对象也可以是元组的集合。4、以同一种语法结构提供多种使用方式、以同一种语法结构提供多种使用方式2、高度非过程化非关系数据模型的数据操纵语言是“面向过程”7SQL既是独立的语言，又是嵌入式语言。既是独立的语言，又是嵌入式语言。作为独立的语言，它能够独立地用于联机交互的使用方式，用户可以在作为独立的语言，它能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入终端键盘上直接键入SQL命令对数据库进行操作命令对数据库进行操作;作为嵌入式语言，作为嵌入式语言，SQL语句能够嵌入到高级语言（如语句能够嵌入到高级语言（如C、C+、Java）程序中，供程序员设计程序时使用。程序中，供程序员设计程序时使用。而在两种不同的使用方式下，而在两种不同的使用方式下，SQL的语法结构基本上是一致的。的语法结构基本上是一致的。4、以同一种语法结构提供多种使用方式、以同一种语法结构提供多种使用方式5、语言简洁，易学易用、语言简洁，易学易用SQL功能极强，完成核心功能只用了功能极强，完成核心功能只用了9个动词，接近英语口语，所以容易个动词，接近英语口语，所以容易学习，易于使用。学习，易于使用。数据查询数据查询数据定义数据定义数据操纵数据操纵数据控制数据控制SELECTCREATE DROP ALTERINSERT UPDATE DELETEGRANT REVOKESQL既是独立的语言，又是嵌入式语言。4、以同一种语法结构提83.2.1 字段数据类型字段数据类型整数数据类型整数数据类型:bigint,int,smallint,tinyint 精确数值类型精确数值类型:numeric,decimal近似浮点数值数据类型近似浮点数值数据类型:float,real日期时间数据类型日期时间数据类型:datetime,smalldatetime字符串数据类型字符串数据类型:char,varchar,text Unicode字符串数据类型字符串数据类型:nchar,nvarchar,ntext二进制数据类型二进制数据类型:binary、varbinary、image 货币数据类型货币数据类型:money,smallmoney 标记数据类型标记数据类型:timestamp,uniqueidentifier 具体见书具体见书P74表表3.13.2 SQL数据定义3.2.1 字段数据类型整数数据类型:bigint,i91、定义基本表、定义基本表 CREATE TABLE (列级完整性约束条件列级完整性约束条件 ,列级完整性约束条件列级完整性约束条件 ,)3.2.2 创建、修改和删除数据表创建、修改和删除数据表建表的同时通常还可以定义与该表有关的完整性约束条件，这建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由数据时由DBMS自动检查该操作是否违背这些完整性约束条件。自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件涉及到该表的多个属性列，则必须定义在如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。表级上，否则既可以定义在列级也可以定义在表级。1、定义基本表 CREATE TABLE 10（1）实体完整性）实体完整性 主码主码(Primary Key)要求：要求：一个基本表中只能定义一个一个基本表中只能定义一个Primary Key约束约束;作为作为Primary Key的任何列不能出现空值的任何列不能出现空值;定义为表级约束的语法格式：定义为表级约束的语法格式：constraint 约束名约束名 Primary Key (列列1 ASC|DESC，n)空值空值(Null/Not Null)要求：该约束只能用于列级约束。要求：该约束只能用于列级约束。唯一值唯一值(Unique)要求：要求：表示某一列或多个列的组合上的取值必须唯一，系统会自动表示某一列或多个列的组合上的取值必须唯一，系统会自动 为其建立唯一索引为其建立唯一索引;定义为表级约束的语法格式：定义为表级约束的语法格式：constraint 约束名约束名 Unique Clustered|NonClustered (列列1 ASC|DESC，n)（1）实体完整性 主码(Primary Key)空值(11例例1、建立、建立Student表表(Sno char(9)主码、主码、Sname char(20)不为空、不为空、Ssex char(2)、Sage smallint、Sdept char(20)CREATE TABLE Student (Sno char(9)PRIMARY KEY,/*在列级定义主码在列级定义主码*/Sname char(20)NOT NULL,Ssex char(2),Sage smallint,Sdept char(20)CREATE TABLE Student (Sno char(9),Sname char(20)NOT NULL,Ssex char(2),Sage smallint,Sdept char(20),/*在表级定义主码在表级定义主码*/PRIMARY KEY(Sno)例1、建立Student表(Sno char(9)主码、S12例例2、建立、建立SC表表(Sno char(9)不为空、不为空、Cno char(4)不为空、不为空、Grade Smallint)，将，将Sno,Cno属性组定义为主码。属性组定义为主码。CREATE TABLE SC (Sno char(9)NOT NULL,Cno char(4)NOT NULL,Grade smallint,PRIMARY KEY(Sno,Cno)/*只能在表级定义主码只能在表级定义主码*/)例2、建立SC表(Sno char(9)不为空、Cno 13（2）参照完整性）参照完整性通过通过FOREIGN KEY短语定义哪些列为外码，用短语定义哪些列为外码，用REFERENCES短语短语指明这些外码参照哪些表的主码。指明这些外码参照哪些表的主码。表级约束的语法格式为：表级约束的语法格式为：constraint 约束名约束名 Foreign Key (子表列子表列1,n)References 主表名主表名(主表列主表列1,n)ON DELETE CASCADE|NO ACTION ON UPDATE CASCADE|NO ACTION 其中：其中：CASCADE 是级联操作是级联操作;NO ACTION 是拒绝操作。是拒绝操作。（2）参照完整性通过FOREIGN KEY短语定义哪些14例例1、建立、建立SC表表(Sno char(9)不为空、不为空、Cno char(4)不为空、不为空、Grade Smallint)，(Sno,Cno)是主码，是主码，Sno和和Cno分分 别参照引用别参照引用Student表的主码和表的主码和Course表的主码。表的主码。CREATE TABLE SC (Sno char(9)NOT NULL,Cno char(4)NOT NULL,Grade smallint,PRIMARY KEY(Sno,Cno)/*在表级上定义实体完整性在表级上定义实体完整性*/FOREIGN KEY (Sno)REFERENCES Student(Sno),/*在表级上定义参照完整性在表级上定义参照完整性*/FOREIGN KEY (Cno)REFERENCES Course(Cno)例1、建立SC表(Sno char(9)不为空、Cno 15例例2、显示说明参照完整性的违约处理示例。、显示说明参照完整性的违约处理示例。CREATE TABLE SC(Sno char(9)NOT NULL,Cno char(4)NOT NULL,Grade smallint,PRIMARY KEY(Sno,Cno),Foreign Key(Sno)References Student(Sno)ON DELETE CASCADE ON UPDATE CASCADE,Foreign Key(Cno)References Course(Cno)ON DELETE NO ACTION /*当删除当删除Course表中的元组造成了与表中的元组造成了与SC表不一致时拒绝删除表不一致时拒绝删除*/ON UPDATE CASCADE /*当更新当更新Course表中的表中的Cno时，级连更新时，级连更新SC表中相应的元组表中相应的元组*/)例2、显示说明参照完整性的违约处理示例。CREATE T16（3）用户定义完整性）用户定义完整性Check可用于定义用户自定义的完整性约束规则。语法格式为：可用于定义用户自定义的完整性约束规则。语法格式为：Constraint 约束名约束名 CHECK (条件条件)例例1、建立、建立Student表，要求表，要求Ssex列只允许取列只允许取“男男”或或“女女”。CREATE TABLE Student (Sno char(9)PRIMARY KEY,/*在列级定义主码在列级定义主码*/Sname char(20)NOT NULL,Ssex char(2)CHECK (Ssex IN (男男,女女),Sage smallint,Sdept char(20)（3）用户定义完整性Check可用于定义用户自定义的完整性约17例例2、建立、建立SC表，要求表，要求Grade的值应该在的值应该在0和和100之间。之间。CREATE TABLE SC (Sno char(9)NOT NULL,Cno char(4)NOT NULL,Grade smallint CHECK (Grade=0 and Grade=100),PRIMARY KEY(Sno,Cno)/*只能在表级定义主码只能在表级定义主码*/)例例3、建立、建立Student表，要求当学生的性别是男时，其名字不能以表，要求当学生的性别是男时，其名字不能以Ms.打头。打头。CREATE TABLE Student (Sno char(9),Sname char(20)NOT NULL,Ssex char(2),Sage smallint,Sdept char(20)，PRIMARY KEY(Sno),CHECK (Ssex=女女 OR Sname NOT LIKE Ms.%)例2、建立SC表，要求Grade的值应该在0和100之间。C18（4）完整性约束的命名）完整性约束的命名SQL在在CREATE TABLE语句中提供了完全性约束命名子句语句中提供了完全性约束命名子句Constraint，用来对完整性约束条件命名。从而可以灵活地增加、，用来对完整性约束条件命名。从而可以灵活地增加、删除一个完整性约束条件。删除一个完整性约束条件。例、建立学生登记表例、建立学生登记表Student，要求学号在，要求学号在9000099999之间，姓名之间，姓名不能取空值，年龄小于不能取空值，年龄小于30,性别只能是性别只能是“男男”或或“女女”，主码为，主码为Sno。CREATE TABLE Student (Sno numeric(6)CONSTRAINT C1 Check (Sno BETWEEN 90000 AND 99999),Sname char(20)NOT NULL,Ssex char(2)CONSTRAINT C2 Check(Ssex IN (男男,女女)Sage smallint CONSTRAINT C3 Check(Sage0),DEPT VARCHAR(20),CONSTRAINT SN_U UNIQUE(SN)例1、建立一个“学生”表S，它由学号SNO、姓名SN、性别S23例例2 建立建立“课程课程”表表C，它由课程号（，它由课程号（CNO）、课程名（）、课程名（CN）、）、学分（学分（CT）三个属性组成。）三个属性组成。CNO为该表主键，学分大于等于为该表主键，学分大于等于1。CREATE TABLE C (CNO CHAR(5)NOT NULL PRIMARY KEY,CN VARCHAR(20),CT INT CHECK (CT=1)CREATE TABLE SC (SNO CHAR(5)NOT NULL CONSTRAINT S_F FOREIGN KEY REFERENCES S(SNO),CNO CHAR(5)NOT NULL,SCORE NUMERIC(3),CONSTRAINT S_C_P PRIMARY KEY (SNO,CNO),CONSTRAINT C_F FOREIGN KEY(CNO)REFERENCES C(CNO)例例3 建立建立“选修选修”关系表关系表SC，定义，定义SNO,CNO为为SC的外部键，的外部键，(SNO,CNO)为该表的主键。为该表的主键。例2 建立“课程”表C，它由课程号（CNO）、课程名（24练习：建立如下表练习：建立如下表 Student(Sno,Sname,Ssex,Sage,Sdept)要求：要求：Sno为主码，为主码，Sage在在1545间间(包括包括15和和45)，Ssex只能取男或女，默认值为男。只能取男或女，默认值为男。Course(Cno,Cname,Cpno,Ccredit)要求：要求：Cno为主码。为主码。Sc(Sno,Cno,Grade)要求：要求：Sno和和Cno为主码，利用为主码，利用Sno建立与表建立与表Student 的关联、的关联、Cno建立与表建立与表Course的关联。的关联。Grade只能接收空值或只能接收空值或0100(包括包括0和和100)的值。的值。练习：建立如下表25CREATE TABLE Student (Sno CHAR(5)NOT NULL PRIMARY KEY,Sname VARCHAR(8),Ssex CHAR(2)DEFAULT 男男 CHECK(Ssex IN(男男,女女),Sage SMALLINT CHECK(Sage=15 AND Sage=45),Sdept CHAR(2)CREATE TABLE Course (Cno CHAR(2)NOT NULL PRIMARY KEY,Cname VARCHAR(20),Cpno CHAR(2),Ccredit SMALLINT)CREATE TABLE Sc (Sno CHAR(5)NOT NULL FOREIGN KEY REFERENCES Student(Sno),Cno CHAR(2)NOT NULL,Grade SMALLINT CHECK(Grade IS NULL)OR(Grade BETWEEN 0 AND 100),PRIMARY KEY(Sno,Cno),FOREIGN KEY(Cno)REFERENCES Course(Cno)CREATE TABLE Student (S26ALTER TABLE 表名表名 ALTER COLUMN 列名 新类型 (长度 ,小数位 )NULL|NOT NULL ADD 新列名 AS 表达式或别名,.n WITH CHECK|WITH NOCHECK ADD 完整性约束 DROP CONSTRAINT 约束名|COLUMN 列名 ,.n CHECK|NOCHECK CONSTRAINT 约束名 ,.n 2、修改基本表、修改基本表 其中其中:指定需要修改的基本表，指定需要修改的基本表，ADD子句用于增加新列和新的完整性约束条件，子句用于增加新列和新的完整性约束条件，DROP子句用于删除指定的完整性约束条件或原有列，子句用于删除指定的完整性约束条件或原有列，ALTER子句用于修改原有的列定义。子句用于修改原有的列定义。CHECK|NOCHECKCONSTRAINT 指定启用或禁用指定启用或禁用 constraint_name。如果禁用，将来插入或更新该列时将不用该约束条件进行验证。此如果禁用，将来插入或更新该列时将不用该约束条件进行验证。此选项只能与选项只能与 FOREIGN KEY 和和 CHECK 约束一起使用。约束一起使用。ALTER TABLE 表名 2、修改基本表 其中27例例4 向S表增加“入学时间”列，其数据类型为日期型。ALTER TABLE S ADD SCOME DATETIME例例5 将年龄的数据类型改为半字长整数。ALTER TABLE S ALTER COLUMN AGE SMALLINT 例例6 删除例4增加的“入学时间”列。ALTER TABLE S DROP COLUMN SCOME例例7 禁止SC中的参照完整性C_F。ALTER TABLE S NOCHECK CONSTRAINT C_F例4 向S表增加“入学时间”列，其数据类型为日期型。28对学生表进行如下操作：对学生表进行如下操作：将姓名列的长度先改为将姓名列的长度先改为6 6，不允许有空值。，不允许有空值。增加两列，性别增加两列，性别(char(2)(char(2)电话电话(varchar(11)(varchar(11)；再将新建的两列删除。再将新建的两列删除。练习：练习：alter tablealter table 学生学生 alter column alter column 姓名姓名 char(6)not null char(6)not nullgogoalter tablealter table 学生学生 add add 性别性别 char(2),char(2),电话电话 varchar(11)varchar(11)gogoalter tablealter table 学生学生 drop column drop column 性别性别,电话电话gogo对学生表进行如下操作：练习：alter table 学生29例例:1)1)为为xsxs表添加主键约束表添加主键约束,该主键约束由学号单列组成该主键约束由学号单列组成,约束约束 名为名为pk_xh.pk_xh.2)2)修改修改xsxs表的主键约束表的主键约束,该主键约束由学号和姓名两列该主键约束由学号和姓名两列 组成组成,约束名为约束名为pk_xh_xm.pk_xh_xm.1)1)ALTER TABLEALTER TABLE xs xs ADD CONSTRAINTADD CONSTRAINT pk_xh pk_xh PRIMARY KEYPRIMARY KEY(学号学号)2)2)ALTER TABLEALTER TABLE xs xs DROP CONSTRAINTDROP CONSTRAINT pk_xh pk_xh GOGO ALTER TABLEALTER TABLE xs xs ADD CONSTRAINTADD CONSTRAINT pk_xh_xm pk_xh_xm PRIMARY KEYPRIMARY KEY(学号学号,姓名姓名)例:1)为xs表添加主键约束,该主键约束由学号单列组成,30例例:为选课表添加外键约束为选课表添加外键约束,约束名为约束名为FK_kcbh,FK_kcbh,使之与课程表建使之与课程表建 立级联删除和级联更新操作立级联删除和级联更新操作.ALTER TABLEALTER TABLE 选课表选课表ADD CONSTRAINTADD CONSTRAINT FK_kcbh FK_kcbh FOREIGN KEYFOREIGN KEY (课程编号课程编号)REFERENCESREFERENCES 课程表课程表(课程编号课程编号)ON DELETE CASCADEON DELETE CASCADEON UPDATE CASCADEON UPDATE CASCADEUPDATEUPDATE 课程表课程表SETSET 课程编号课程编号=1111=1111WHEREWHERE 课程编号课程编号=0001=0001DELETE DELETE 课程表课程表WHERE WHERE 课程编号课程编号=1111=1111ALTER TABLEALTER TABLE 选课表选课表DROP CONSTRAINTDROP CONSTRAINT FK_kcbh FK_kcbh例:为选课表添加外键约束,约束名为FK_kcbh,使之与课程31例例:为选课表添加外键约束为选课表添加外键约束,约束名为约束名为FK_xh,FK_xh,使之与学生表建使之与学生表建 立级联删除和级联更新操作立级联删除和级联更新操作.ALTER TABLEALTER TABLE 选课表选课表ADD CONSTRAINTADD CONSTRAINT FK_xh FK_xh FOREIGN KEYFOREIGN KEY (学号学号)REFERENCESREFERENCES 学生表学生表(学号学号)ON DELETE CASCADEON DELETE CASCADEON UPDATE CASCADEON UPDATE CASCADEALTER TABLEALTER TABLE 选课表选课表DROP CONSTRAINTDROP CONSTRAINT FK_xh FK_xh例:为选课表添加外键约束,约束名为FK_xh,使之与学生表建32例：例：1 1）建立）建立XSXS表，包括学号表，包括学号 char(2)char(2)姓名姓名 char(6)char(6)，同时以学，同时以学 号建立唯一性约束，约束名为号建立唯一性约束，约束名为UK_xh.UK_xh.2 2）为）为XSXS表以姓名建立唯一性约束，约束名为表以姓名建立唯一性约束，约束名为UK_xmUK_xm。3 3）删除）删除XSXS表中的表中的UK_xhUK_xh的唯一性约束。的唯一性约束。CREATE TABLECREATE TABLE XS XS(学号学号 charchar(2)(2)CONSTRAINTCONSTRAINT UK_xh UK_xh UNIQUEUNIQUE,姓名姓名 charchar(6)(6)ALTER TABLEALTER TABLE XS XS ADD ADD CONSTRAINTCONSTRAINT UK_xm UK_xm UNIQUEUNIQUE(姓名姓名)ALTER TABLEALTER TABLE XS XS DROP CONSTRAINTDROP CONSTRAINT UK_xh UK_xh例：1）建立XS表，包括学号 char(2)姓名 cha33CREATE TABLECREATE TABLE score score(学号学号 charchar(4),(4),课号课号 charchar(1),(1),分数分数 decimaldecimal(5,2)(5,2)CONSTRAINTCONSTRAINT CK_cj CK_cj CHECK CHECK(分数分数=0 and =0 and 分数分数=100)=0 and=0 and 分数分数=150)=150)例：例：建立建立scorescore表，包括学号表，包括学号char(4)char(4)课号课号char(1)char(1)分数分数 decimal(5,2)decimal(5,2)，为分数建立检查约束，约束名为，为分数建立检查约束，约束名为CK_cjCK_cj，要求分，要求分数只能在数只能在01000100间间CREATE TABLE score例：建立score表348.4.5 8.4.5 默认约束默认约束例例 员工表员工表employeeemployee的的sexsex列添加默认约束，默认值是列添加默认约束，默认值是“男男”。ALTER TABLEALTER TABLE employee employee ADD CONSTRAINT ADD CONSTRAINT sex_default sex_default DEFAULT DEFAULT 男男 FORFOR sex sex 更改表更改表employeeemployee为为hire_datehire_date列定义默认约束。列定义默认约束。ALTER TABLEALTER TABLE employee employee ADD CONSTRAINT hire_date_df ADD CONSTRAINT hire_date_df DEFAULT getdate()FOR DEFAULT getdate()FOR hire_datehire_date8.4.5 默认约束例 员工表employee的sex列添加35DROP TABLE 例例8 删除S表。DROP TABLE S 3、删除基本表、删除基本表 DROP TABLE 3、删除基本表 36建立索引是加快查询速度的有效手段。可在基本表上建立一个或建立索引是加快查询速度的有效手段。可在基本表上建立一个或多个索引，以提供多种存取路径，加快查询速度。多个索引，以提供多种存取路径，加快查询速度。系统在存取数据时会自动选择合适的索引作为存取路径，用户不系统在存取数据时会自动选择合适的索引作为存取路径，用户不必也不能显式地选择索引。必也不能显式地选择索引。按照索引记录的存放位置不同，可分为聚集索引按照索引记录的存放位置不同，可分为聚集索引(Clustered Index)与非聚集索引与非聚集索引(Non-Clustered Index)两类。两类。聚集索引是指索引项的顺序与表中记录的物理顺序一致的索引组聚集索引是指索引项的顺序与表中记录的物理顺序一致的索引组织；织；非聚集索引按照索引的字段排列记录，但是排列的结果并不会存非聚集索引按照索引的字段排列记录，但是排列的结果并不会存储在表中，而是另外存储。在检索记录时，聚集索引会比非聚集储在表中，而是另外存储。在检索记录时，聚集索引会比非聚集索引速度快，一个表中只能有一个聚集索引，而非聚集索引可以索引速度快，一个表中只能有一个聚集索引，而非聚集索引可以有多个。有多个。3.2.2 设计、创建和维护索引设计、创建和维护索引1、索引的概念、索引的概念 建立索引是加快查询速度的有效手段。可在基本表上建立一个或多个37 CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX ON|(ASC|DESC ,.n )2、创建索引、创建索引 例例9 为学生为学生-课程数据库中的课程数据库中的S、C、SC三个表建立索引。三个表建立索引。其中其中S表按学号升序建惟一索引，表按学号升序建惟一索引，C表按课程号降序建立聚簇索引，表按课程号降序建立聚簇索引，SC表按学号升序和课程号降序建非聚簇索引。表按学号升序和课程号降序建非聚簇索引。CREATE UNIQUE INDEX S_SNO ON S(SNO)CREATE CLUSTERED INDEX C_CNO ON C(CNO DESC)CREATE NONCLUSTERED INDEX SC_SNO_CNO ON SC(SNO ASC,CNO DESC)CREATE UNIQUE CLUSTER381 1、按、按employeeemployee表的表的employee_nameemployee_name列建立非聚集索引。列建立非聚集索引。CREATE NONCLUSTERED INDEX CREATE NONCLUSTERED INDEX name_idxname_idx ON ON employee(employee_name)employee(employee_name)2、在学生表上创建一个名为学号、在学生表上创建一个名为学号_index的唯一性聚簇索的唯一性聚簇索 引，索引关键字为学号，降序。引，索引关键字为学号，降序。CREATE UNIQUE CLUSTEREDCREATE UNIQUE CLUSTEREDINDEXINDEX 学号学号_index_indexON ON 学生表学生表(学号学号 DESC DESC)3 3、在选课表上创建一个名为学号、在选课表上创建一个名为学号_ _课号课号_index_index的非聚簇复合索引，的非聚簇复合索引，索引关键字为学号索引关键字为学号,课号，升序课号，升序CREATE NONCLUSTERED CREATE NONCLUSTERED INDEXINDEX 学号学号_ _课号课号_index_indexONON 学生表学生表(学号学号 ASCASC,课号课号 ASCASC)1、按employee表的employee_name列建立非39删除索引的命令语法删除索引的命令语法:DROP INDEX 例例1010 删除S表的S_SNO索引。DROP INDEX S_SNODROP INDEX S_SNO3、删除索引、删除索引 删除索引的命令语法:例10 删除S表的S_SNO索引。40SELECT ALL|DISTINCT 目标列表达式目标列表达式,目标列表达式目标列表达式.INTO 新表名新表名FROM 表名或视图名表名或视图名,表名或视图名表名或视图名.WHERE 条件表达式条件表达式 GROUP BY 列名列名1 HAVING 条件表达式条件表达式 ORDER BY 列名列名2 ASC|DESC.3.3 SQL数据查询3.3.1 SELECT命令的格式及其含义命令的格式及其含义SELECT ALL|DISTINCT 目标列411、查询指定列、查询指定列 例例11 查询全体学生的学号与姓名。SELECT SNO,SN FROM S 例例12 查询全体学生的姓名、学号、所在系。SELECT SN,SNO,DEPT FROM S 3.3.2 单表查询单表查询在在Select子句中给出包含所选字段的一个列表，各个字段之间用逗子句中给出包含所选字段的一个列表，各个字段之间用逗号分隔，字段的次序可以任意指定。号分隔，字段的次序可以任意指定。1、查询指定列 例11 查询全体学生的学号与姓名。3.42如如在在FromFrom子子句句中中指指定定了了两两个个表表，而而这这两两个个表表中中又又有有同同名名的的字段，使用这些字段时就应在其字段名前冠以表名。字段，使用这些字段时就应在其字段名前冠以表名。USEUSE JWGL JWGLSelectSelect studentstudent.student_idstudent_id,student_name,monitor,student_name,monitor FromFrom student,class student,class WhereWhere class.class_id=student.class_id class.class_id=student.class_id 如在From子句中指定了两个表，而这两个表中又有同名的字段，43例例13 查询全体学生的详细记录。SELECT *FROM S 等价于：SELECT SNO,SN,SEX,AGE,DEPT FROM S2、查询全部列、查询全部列 例13 查询全体学生的详细记录。2、查询全部列 44SELECT子句的不仅可以是表中的属性列，也可以是有关表达式。例例14 查全体学生的姓名及其出生年份。SELECT SN,2005-AGE FROM S输出的结果为：SN -李涛 1986 王林 1987 陈高 1984 张杰 1988 吴小丽 1986 徐敏敏 19853、查询经过计算的列、查询经过计算的列 SELECT子句的不仅可以是表中的属性列，也45例例15 查全体学生的姓名、出生年份和所在系，要求用小写字母表示所有系名。SELECT SN,出生年份出生年份:,2005-AGE,lower(DEPT)FROM S输出的结果为：SN -李涛 出生年份:1986 信息 王林 出生年份:1987 计算机 陈高 出生年份:1984 自动化 张杰 出生年份:1988 自动化 吴小丽 出生年份:1986 信息 徐敏敏 出生年份:1985 计算机例15 查全体学生的姓名、出生年份和所在系，要求用小写46设置字段别名：设置字段别名：显显示示选选择择查查询询的的结结果果时时，第第一一行行（即即表表头头）中中显显示示的的是是各各个个输输出出字字段段的的名名称称。为为了了便便于于阅阅读读，也也可可指指定定更更容容易易理解的字段名来取代原来的字段名。设置别名的方法：理解的字段名来取代原来的字段名。设置别名的方法：原字段名原字段名 字段别名字段别名原字段名原字段名 AS AS 字段别名字段别名 字段别名字段别名=原字段名原字段名 设置字段别名：显示选择查询的结果时，第一行（即表头）中显示的47用户可以通过别名来改变查询结构的列标题，这对于含自述表达式、函数用户可以通过别名来改变查询结构的列标题，这对于含自述表达式、函数名的目标列表达式尤为重要。名的目标列表达式尤为重要。上例可以定义如下：上例可以定义如下：SELECT SN,出生年份出生年份:BIRTH,BIRTHDAY=2005-AGE,lower(DEPT)AS DEPARTMENTFROM S输出的结果为：SN BIRTH BIRTHDAY DEPARTMENT -李涛 出生年份:1986 信息 王林 出生年份:1987 计算机 陈高 出生年份:1984 自动化 张杰 出生年份:1988 自动化 吴小丽 出生年份:1986 信息 徐敏敏 出生年份:1985 计算机用户可以通过别名来改变查询结构的列标题，这对于含自述表达式、483.3.3 WHERE子句的基本使用子句的基本使用 1.消除取值重复的行 2.指定WHERE查询条件 返回本节首页3.3.3 WHERE子句的基本使用 1.消除取值重复的行49消除取值重复的行消除取值重复的行 例例16 查所有选修过课的学生的学号。SELECT SNO FROM SC结果为：SNO -S1 S1 S2 S2 S2 S3 S3 S3 S3 S4 S4 S4 S5该查询结果里包含了许多重复的行。如果想去掉结果表中的重复行，必须指定DISTINCT短语：SELECT DISTINCT SNOFROM SC执行结果为：SNO -S1 S2 S3 S4 S5消除取值重复的行 例16 查所有选修过课的学生的学号。50例例 显示显示employeeemployee表中全部员工的姓名和年龄，表中全部员工的姓名和年龄，去掉重名。去掉重名。(注：表中只有出生日期字段注：表中只有出生日期字段birth_date)birth_date)SELECT DISTINCT employee_name AS 姓名，姓名，YEAR(GETDATE()-YEAR(birth_date)AS 年龄年龄FROM employee例 显示employee表中全部员工的姓名和年龄，51常用的查询条件常用的查询条件 查询条件查询条件 谓词谓词 比较运算符比较运算符=,=,=,!=,!,!,=52(1)比较运算符比较运算符 例例17 查计算机系全体学生的名单。SELECT SN FROM S WHERE DEPT=计算机例例18 查所有年龄在20岁以下的学生姓名及其年龄。SELECT SN,AGE FROM S WHERE AGE=20(1)比较运算符 例17 查计算机系全体学生的名单。53例例 对对employeeemployee表，求出男员工的平均工资。表，求出男员工的平均工资。SELECT AVG(wages)AS 平均工资平均工资 FROM employee WHERE sex=男男例、在例、在studentstudent中检索男生且年龄大于中检索男生且年龄大于2121岁的学生记录。岁的学生记录。(只有只有birthbirth字段字段)例、在例、在“student”“student”中检索姓张或姓李的学生。中检索姓张或姓李的学生。Select *FROM student Where sex=男 AND Datediff(yy,birth,Getdate()Select *FROM student Where Substring(姓名姓名,1,1)=张张 OR Substring(姓名姓名,1,1)=李李例 对employee表，求出男员工的平均工资。SELECT54(2)确定范围确定范围 例例20 查询年龄在20至23岁之间的学生的姓名、系别和年龄。SELECT SN,DEPT,AGE FROM S WHERE AGE BETWEEN 20 AND 23与“BETWEENAND”相对的谓词是“NOT BETWEENAND”。例例21 查询年龄不在20至23岁之间的学生姓名、系别和年龄。SELECT SN,DEPT,AGE FROM S WHERE AGE NOT BETWEEN 20 AND 23返回本节首页(2)确定范围 例20 查询年龄在20至23岁之间的学55例 对employee表，列出月工资在2000到3000之间的员 工名单。SELECTSELECT *FROMFROM employee employee WHEREWHERE wages BETWEEN 2000 AND 3000 wages BETWEEN 2000 AND 3000 语句中的WHERE子句还有等价的形式：WHEREWHERE wages=2000 AND wages=2000 AND wages=3000wages=3000例 对employee表，列出月工资在2000到3000之56(3)确定集合确定集合 例例22 查询信息系、自动化系和计算机系的学生的姓名和性别。SELECT SN,SEXFROM SWHERE DEPT IN(信息,自动化,计算机)与IN相对的谓词是NOT IN，用于查找属性值不属于指定集合的元组。例例23 查既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECT SN,SEXFROM SWHERE DEPT NOT IN(信息,自动化,计算机)返回本节首页(3)确定集合 例22 查询信息系、自动化系和计算机系57例：在例：在“student”“student”中检索姓李、姓张、姓林、姓许中检索姓李、姓张、姓林、姓许的学生记录。的学生记录。Select *FROM student Where Substring(姓名,1,1)IN (李,张,王,刘)等价于：Select *FROM student Where Substring(姓名,1,1)李 OR Substring(姓名,1,1)张 OR Substring(姓名,1,1)王 OR Substring(姓名,1,1)刘例：在“student”中检索姓李、姓张、姓林、姓许的学生记58(4)字符匹配字符匹配 通配符描述%（百分号）代表零个或更多字符的任意字符串。_（下划线）代表任何单个字符（长度可以为0）。（中扩号）指定范围(a-f)或集合(abcdef)中的任何单个字符。不属于指定范围(a-f)或集合(abcdef)的任何单个字符。(4)字符匹配 通配符描述%（百分号）代表零个或更多字符的任59字符匹配示例字符匹配示例1例例24 查所有姓刘的学生的姓名、学号和性别。SELECT SN,SNO,SEX FROM S WHERE SN LIKE 刘%例例25 查姓“欧阳”且全名为三个汉字的学生的姓名。SELECT SN FROM S WHERE SN LIKE 欧阳_返回本节首页字符匹配示例1例24 查所有姓刘的学生的姓名、学号和性60字符匹配示例字符匹配示例2例例26 查名字中第二字为“阳”字的学生的姓名和学号。SELECT SN,SNO FROM S WHERE SN LIKE _阳%例例27 查所有不姓刘的学生姓名。SELECT SN,SNO,SEX FROM S WHERE SN NOT LIKE 刘%返回本节首页字符匹配示例2例26 查名字中第二字为“阳”字的学生的61字符匹配示例字符匹配示例3例例28 查DB_Design课程的课程号和学分。SELECT CNO,CT FROM C WHERE CN LIKE DB_Design ESCAPE ESCAPE 短语表示为换码字符，这样匹配串中紧跟在后面的字符_不再具有通配符的含义，而是取其本身含义，被转义为普通的_字符。返回本节首页字符匹配示例3例28 查DB_Design课程的课程号62例例 对对employeeemployee表，列出所有的姓表，列出所有的姓“张张”的员工名单。的员工名单。SELECTSELECT *FROMFROM employee employee WHEREWHERE employee_name LIKE employee_name LIKE 张张%语句中的语句中的WHEREWHERE子句还有等价的形式：子句还有等价的形式：WHERE WHERE LEFTLEFT(employee_name,1)=(employee_name,1)=张张例、例、从从“STUDENT”“STUDENT”中查找姓王、姓李和姓林的学生。中查找姓王、姓李和姓林的学生。SelectSelect *FROMFROM STUDENT STUDENT WhereWhere 姓名姓名 LIKE LIKE 王李林王李林%例、例、从从“STUDENT”中检索学号末位数字不在中检索学号末位数字不在26范围内的范围内的 学生。学生。Select *FROM STUDENT Where student_id LIKE%2-6 例 对employee表，列出所有的姓“张”的员工名单。例63(5)涉及空值的查询涉及空值的查询 例例29 某些学生选修某门课程后没有参加考试，所以某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，下面我们来查一下有选课记录，但没有考试成绩，下面我们来查一下缺少成绩的学生的学号和相应的课程号。缺少成绩的学生的学号和相应的课程号。SELECTSELECT SNO,CNO SNO,CNO FROMFROM SC SC W
展开阅读全文