第4章 关系数据库的结构化查询语言(1)

NINGBO University of Technology
4.2.2 SQL Server中的数据库表和索引
1.定义基本表 使用T-SQL中的CREATE TABLE语句可以创建基本 表，其语句格式为： CREATE TABLE [[<数据库名>.]<主人名>.]<表名> (<列名1> <类型1> {NOT NULL∣NULL} [,<列名2> <类型2>{NOT NULL∣NULL}„„]) SQL Server中支持二种数据类型：系统数据类型 和用户定义数 据类型。 （1）系统数据类型 字符数据： char(n),varchar(n),text 二进制数据：binary(n), varbinary(n),image 整数数据：int,smallint,tinyint,bit 浮点数据： float,real 货币数据： money,smallmoney 日期时间数据： datetime,smalldatetime
NINGBO University of Technology
3. 撤销基本表 语句格式为： DROP TABLE <表名>； 功能：删除指定表及其数据，释放相应的存储空间，同 时系统也自动地删除在此表上建立的各种索引，也删除 了在该表上授予的操作权限。虽然删除表时并未删除定 义在该表上的视图，但这些视图已无效，不能再使用 了。 例2 删除已存在的表table1。 use teaching DROP TABLE table1 只有当没有其它表引用它时，这个表才能被删除， 否则删除操作就会失败。 需要注意的是DROP TABLE语句不能用来删除系 统表。
NINGBO University of Technology
2）创建course表
CREATE TABLE course (cno char (5) NOT NULL PRIMARY KEY , cname varchar (20) NOT NULL , chour tinyint NULL ) 上面语句创建了一个名为course的课程表，表中字 段含义分别为：课程号、课程名和学时数。其中cno字 段非空且设置为主键；cname字段不能为空；chour字段 可以取空值。
NINGBO University of Technology
3) 创建student_course表 CREATE TABLE student_course( id int IDENTITY (1, 1) NOT NULL PRIMARY KEY , sno char (7) NOT NULL FOREIGN KEY(sno) REFERENCES student(sno) ON DELETE CASCADE , cno char (5) NOT NULL FOREIGN KEY(cno) REFERENCES course(cno) ON DELETE NO ACTION , grade decimal(5,0) NULL CHECK (grade>=0 and grade<=100)) 在student_course表，id字段非空且设置为主键； sno字段不能为空，设置成相对于student表来说为外 键，并且当在student表中删除某个学生记录时，会根据 学号自动在student_course表中删除此学生的选课记 录；cno字段不能为空，设置成相对于course表来说 为外键，只有当在student_course表中无某门课的选课 记录时才能在course表中删除这门课程的记录；grade字 段可以为空，但取值必须在0到100之间。
NINGBO University of Technology
4.3 T-SQL数据查询功能
最常用的SELECT语句的语法： SELECT <投影的字段列表> FROM <参与查询的表列表> [WHERE <查询选择的条件> ] [GROUP BY <分组表达式>][HAVING<分组查询条件>] [ORDER BY <排序表达式> [ASC∣DESC ]] 整个语句的含义为：根据 WHERE子句中的条件表达， 从基本表(或视图)中找出满足条件的元组．按SELECT子 句中的目标列，选出元组中的分量形成结果表。如果有 ORDER子句，则结果表要根据指定的表达式按升序(ASC) 或降序(DESC)排序。如果有GROUP子句，则将结果按列名 分组，根据 HAVING指出的条件，选取满足该条件的组予 以输出。
NINGBO University of Technology
4.2 T-SQL的数据定义 4.2.1 SQL Server中的数据库
1.系统数据库 （1）Master数据库：它用来存储有关其它数据库的各种 信息，这些信息全都记录在一组事先定义的系统表中， 用来管理和控制整个DBMS的运行。 （2）Model数据库：它为创建其他用户数据库提供一个 原型。每当建立一个新的用户数据库时，都先建立一份 Model数据库的拷贝，然后再对其尺寸进行适当的扩充。 （3）Tempdb数据库：用于储存临时表和其它临时建立的 对象。它为该服务器上的所有数据库用户所共享。 （4）Msdb数据库：它主要被SQL Server Agent用来进行 复制、作业调度以及管理报警等活动，也可通过调度任 务来排除故障。 2.用户数据库
NINGBO University of Technology
（2）用户定义数据类型 格式：sp_addtype <类型名>,〈系统类型> [,<null说明>] 例如： use pubs exec sp_addtype phone_type, ”varchar(24)”, ”not null” exec sp_addtype fax_type, ”varchar(24)”, ”null” 使用sp_droptype <类型名>可删除用户自定义数据 类型。
NINGBO University of Technology
4.1 SQL概述
SQL是一种介于关系代数和关系演算之间的一种结 构化查询语言，其主要功能包括数据定义语言(DDL）、 数据操纵语言（DML）和数据控制语言（DCL），它是一 种通用的、功能很强的关系数据库语言。
4.1.1标准SQL的特点
NINGBO University of Technology
2. 扩充修改基本表 用户使用数据库时，随着应用要求的改变，往往需 要对原有的表格结构进行修改，此时使用 ALTER TABLE 命令。语句格式如下： （1）扩充基本表 ALTER TABLE 表名 ADD 列名 类型 [NULL][，列名 类型 [NULL]„)； （2）修改基本表 ALTER TABLE 表名 ALTER COLUMN 列名 类型[NULL|NOT NULL][，列名 类型 [NULL | NOT NULL]]„)； 功能：对存在的表进行修改，但不影响原有表的数据， ADD后面指出新增列的名字及它们的数据类型等信息； ALTER COLUMN后指出要修改列定义的第一个名字及它们 的数据类型等信息。注意一个列要改变成非空值(NOT NULL)，要求该列当前不含有空值，如果不选择该项,其 默认的值保持原有的值；若一个列要改变数据类型，则 该列数据必须全为空值,否则不能改变。
图4.l SQL对关系数据库模式结构的支持
1.基本表是本身独立存在的表，一个（或多个）基 本表对应一个存储文件，一个表可以带若干索引，索引 也存放在存储文件中。存储文件的逻辑结构组成了关系 数据库的内模式。 2.视图是从一个或几个基本表导出的表，它本身不 独立存储在数据库中，即数据库中只存储视图的定义而 不存储对应的数据，因此视图是一个虚表。
第4章 关系数据库的结构化查询语言
Chapter 4 Structured Query Language of Relational Database 本章重点 本章主要介绍了T-SQL语言的数据定 义、数据操纵和数据控制功能，介绍了视 图、存储过程和触发器，介绍批、脚本、 局部变量、全局变量的概念，重点介绍了 流程控制语句和游标的使用方法。本章重 点要求掌握T-SQL语言的查询和更新功能， 灵活掌握视图的作用和用法，掌握流程控 制语句和游标的使用方法。
NINGBO University of Technology
4. 索引的建立和删除 SQL Server中提供了二种形式的索引：簇集索引 和非簇集索引。簇集索引根据键的值对行进行排序，所 以每个表只能有一个簇集索引。非簇集索引不根据键值 排序，索引数据结构与数据行是分开的。索引建立和删 除的语句格式为： （1）CREATE [UNIQUE] {CLUSTERED|NONCLUSTERED} INDEX <索引名> ON <表名>(<列名1>[,<列名 2>···]) （2）DROP INDEX <表名>.<索引名1> [,<表名>.<索引名2>···] 例3 在课程表上建立和删除索引。 Create unique clustered index kh_ind on course(cno); Drop index course.kh_ind;
NINGBO University of Technology
例1下面将使用CREATE TABLE语句来创建teaching数 据库的三个基本表：student、student_course和 course。 1）创建student表 CREATE TABLE student (sno char (7) NOT NULL PRIMARY KEY , sname varchar (20) NOT NULL , dept varchar (20) NOT NULL , birthday datetime NULL , tel varchar (20) NULL , email varchar (20) NULL UNIQUE) 上面语句创建了一个名为student的学生表，表 中字段含义分别为：学号、姓名、系名、生日、电话 号码和电子邮件地址。其中sno字段非空且设置为主 键；email字段可以为空，但必须不能取重复值；其 它字段含义自明，在此略去。