数据库系统概论课程实验指导书(V1.2) - 图文

更新时间：2024-04-16 22:01:01 阅读量：综合文库文档下载

说明：文章内容仅供预览，部分内容可能不全。下载后的文档，内容与下面显示的完全一致。下载之前请确认下面内容是否您想要的，是否完整无缺。

数据库系统概论课程设计推荐度：
相关推荐

数据库原理与应用课程实验指导书

《数据库系统概论》

课程实验指导书

江涛

数据库原理与应用课程实验指导书

淮南师范学院计算机信息工程系

2011.09

目录目

录 ………………………………………………………………………实

验

…一

…数

据

…库

的…

创

…建

和

… 管

理 ………………………………………………… 实验二

表的创建、管理及数据操

作…………………………………………… 实

验

三

数

据

查

询…………………………………………………………… 实

验

四

数

据

操

纵………………………………………………………… 实

验五

数据库保

数据库原理与应用课程实验指导书

护…………………………………………………… 实

验

六

数

据

库

应

用

开

发…………………………………………………… 附

加

实

验

事

务

并

发

控

制

操

作……………………………………………………

数据库原理与应用课程实验指导书

实验一数据库的创建和管理

一、实验目的

? 熟悉SQL Server 2000实验环境的设置、学会独立使用该系统； ? 熟悉SQL Server 2000中企业管理器和查询分析器两个常用管理工具；

? 熟练掌握SQL Server Enterprise Manager 创建和管理数据库； ? 熟练掌握在查询分析器中使用T-SQL语句创建和管理数据库二、实验内容

1. 熟悉Microsoft SQL Server / Enterprise Manager 2. 熟悉Microsoft SQL 查询分析器 3. 利用企业管理器创建和管理数据库 (1) 创建数据库 (2) 删除数据库

4. 在查询分析器中使用T-SQL语句创建和管理数据库 (1) 创建数据库 (2) 修改数据库 (3) 删除数据库

数据库原理与应用课程实验指导书

三、实验步骤 1、启动服务器管理器

首先通过【开始】→【程序】→【Microsoft SQL Servers】→【服务器管理器】，在系统托盘中会同时出现SQL服务的图标，单击【开始/继续】前的小三角，此时，在系统托盘中的图标上会有一个绿色箭头，表示服务已经启动。 2、SQL Server 2000企业管理器

首先通过【开始】→【程序】→【Microsoft SQL Servers】→【企业管理器】进入企业管理器界面。

在企业管理器的左边列出的是控制台管理目录，依次是数据库类型（SQL Servers）、数据库群组名（SQL Servers组）、数据库服务器主机名，默认连接为本机）和该数据库服务器提供的所有的管理工具（包括数据库、数据转换服务、管理、复制、安全性、支持服务等）。

在“数据库”选项中列出的是当前服务器中所有的数据库名（master、model、msdb、Northwind等），其中master、model、tempdb等为系统数据库，记录其他数据库的公有属性和某些隐藏信息，用户不要直接对系统数据库（也就是master库）中的表和数据项进行修改和删除，否则将会给数据库系统造成不可挽回的损失。

数据库原理与应用课程实验指导书

3、新建数据库

在服务器组的“数据库”上，右击，弹出快捷菜单，选择“新建数据库…”。在弹出的对话框中常规（General）页框中，要求用户输入数据库名称以及排序规则名称。

点击数据文件（Data Files）页框，该页框用来输入数据库文件的逻辑名称、存储位置、初始容量大小和所属文件组名称，如图

点击事务日志（Transaction Log）页框，该页框用来设置事务日志文件信息，如图：

数据库原理与应用课程实验指导书

单击“确定”按钮，则开始创建新的数据库。 4、表操作

① 新建表在企业管理器中，展开指定的服务器和数据库，打开想要创建新表的数据库，用右键单击表对象，从弹出的快捷菜单中选择“新建表…”选项，或者在工具栏中选择图标，就会出现新建表对话框，在该对话框中，可以定义列的以下属性：列名称、数据类型、长度、精度、小数位数、是否允许为空、缺省值、标识列、标识列的初始值、标识列的增量值和是否有行的标识。然后根据提示进行设置。

② 增加、删除和修改字段在企业管理器中，打开指定的服务器中要修改表的数据库，用右键单击要进行修改的表，从弹出的快捷菜单中选择“设计表”选项，则会出现设计表对话框，在该对话框中，可以利用图形化工具完成增加、删除和修改字段的操作。

③ 创建、删除和修改约束

数据库原理与应用课程实验指导书

在企业管理器中，用右键单击要操作的数据库表，从弹出的快捷菜单中选择“设计表”选项，然后根据提示对主键进行添加、删除和修改操作。

④ 查看表格

在企业管理器中，用右键单击要操作的数据库表，从弹出的快捷菜单中选择“设计表”选项，可以查看表格的定义

在企业管理器中，用右键单击要操作的数据库表，从弹出的快捷菜单中选择“打开表—返回所有行”选项，查看表格中的数据

⑤ 删除表格在企业管理器中，展开指定的数据库和表格项，用右键单击要删除的表，从快捷菜单中选择“除去表”选项，则会出现除去对象对话框。单击“全部删除”按钮，即可删除表。 5、查询分析器(Query Analyzer)

首先通过【开始】→【程序】→【Microsoft SQL Servers】→【查询分析器】进入查询分析器界面。窗口的顶部是菜单和工具栏，在工具栏中有一个数据库列表框，列出主机中所有的数据库，用户可直接在列表框中选择进行操作的数据库名（如：Northwind）。主窗口的左边是对象浏览器（包括对象和模板两页），右边是Transact-SQL程序编辑器。

数据库原理与应用课程实验指导书

查询分析器是SQL Server2000的另一个非常主要的客户端工具，查询分析器用Transact-SQL程序对数据库系统进行分析和管理，企业管理器用图形界面和向导对数据库进行操作。对于那些更高级的查询分析、批处理创建删除，使用查询分析器就比较灵活。

用户可直接在编辑器中输入查询分析语句“select * from categories”，其中categories是Northwind数据库中的一张表，用户可以在左边对象浏览器的数据库用户表中找到。然后点击工具栏上的绿色三角形按钮进行执行。查询结果将出现在编辑器的下面，如下图：

查询分析器的有一实用功能—程序模板，选择左边的模板类型，右边编辑框将自动生成相应的Transact-SQL程序。修改程序

数据库原理与应用课程实验指导书

中的参数就执行相应的功能。例如要创建一个数据库，就在左边选择Create Database 模板类型，再选择详细类型Create Database Basic Template，右边出现一段程序，其功能是判断主机中是否存在数据库N，如果存在的话，就将其删除，然后再创建数据库N。用户也可以通过程序模板来进一步学习Transact-SQL语言。

6、在查询分析器使用T-SQL语句管理数据库 ? 利用T-SQL语句创建数据库打开查询分析器，输入如下语句：

CREATE DATABASE MYDB ON

(NAME='MY_DB_DATA', FILENAME='D:\\PROGRAM

FILES\\Microsoft

SQL

Server\\MSSQL\\data\\MY_DB_DATA.MDF', SIZE=5, MAXSIZE=15, FILEGROWTH=2) LOG ON

数据库原理与应用课程实验指导书

(NAME='MY_DB_LOG', FILENAME='D:\\PROGRAM

FILES\\Microsoft

Server\\MSSQL\\data\\MY_DB_LOG.LDF', SIZE=3, MAXSIZE=10, FILEGROWTH=1 ) GO

? 利用T-SQL语句修改数据库

用T-SQL语句完成修改数据库的操作，语句如下。 ALTER DATABASE MYDB ADD FILE

(NAME='MY_DB_DATA1', FILENAME='C:\\Program

Files\\Microsoft

Server\\MSSQL\\data\\MY_DB_DATA1.NDF', SIZE=3, MAXSIZE=15, FILEGROWTH=2 ) GO

ALTER DATABASE MYDB MODIFY FILE

(NAME='MY_DB_LOG', MAXSIZE=20, FILEGROWTH=2 ) GO

SQL

数据库原理与应用课程实验指导书

? 利用T-SQL语句删除数据库

DROP DATABASE MYDB

数据库原理与应用课程实验指导书

实验二表的创建、管理及数据操作

一、实验目的

? 熟练掌握使用企业管理器和T-SQL语句创建、修改和删除表。 ? 熟练掌握使用企业管理器和T-SQL语句插入、修改和删除表数据。二、实验内容

1、使用企业管理器创建学生选修课程数据库Student_Courses（参照课本）；

2、Student_Courses数据库包含如下三个表：学生表、选修表、课程表；主要设置表的完整性；

3、在企业管理器中插入、修改和删除表数据。 4、使用T-SQL重复上述操作

5、分别使用企业管理器和T-SQL修改和删除表三、实验步骤

1、建立studentdb数据库和spjdb数据库；

2、在studentdb数据库中利用企业管理器和查询分析器创建以下3个表，同时完成数据完整性的定义（实体完整性、参照完整性和用户定义的域完整性）：

student（学生信息表）：

数据库原理与应用课程实验指导书

主列名码 Pk

sno sname ssex sage sdept 数据类宽度型 char char char smallint char 9 10 2 15 小数空否位 N N Y Y 取值范备注围不小于年龄 12 Y 系名学号姓名性别 course（课程表）：

主列名码 Pk cno cname 型 Char Char 度 4 20 位 N Y 称先行课

sc（学生选课表）：

主列名码

数据类宽小数空否备注课程号课程名cpno ccredit Char smallint 4 Y 号 Y 学分数据类型宽小数度空否 14

外码参照关系取值范围备注数据库原理与应用课程实验指导书

sno Pk cno Char Char 9 4 N N Fk student Fk course 学号课程号

grade Decimal 5 1 Y 0≤x≤成绩 100 3、在spjdb数据库中利用企业管理器和查询分析器创建以下4个表，同时完成数据完整性的定义（实体完整性、参照完整性和用户定义的域完整性）：

S（供应商信息表）：

主列名码 Pk sno sname 型 char char 2 10 数据类宽度位 N N 称大于0 供应商状

P（零件信息表）：

主列名码

小数空否取值范备注围供应商号供应商名status smallint city char 10 Y 态 Y 所在城市数据类宽度型小数空否位 15

取值范备注围

数据库原理与应用课程实验指导书

pno pname color char char char 2 10 2 N N Y Y 大于0 零件号零件名称颜色重量 weight smallint J（工程项目表）：

主列名码 Pk jno 型 char 2 数据类宽度位 N 号

SPJ（供应情况表）：

主码列名 sno Pk pno jno 数据类型 Char Char Char 宽度 2 2 2 小数空否 N 号 N N 目号

小数空否取值备注范围工程项目工程项目名称 jname city char char 10 10 N Y 所在城市外码 Fk 参照关取值范备注系 S 围供应商Fk Fk P J 零件号工程项数据库原理与应用课程实验指导书

qty smallint Y x>0 数量 4、修改表结构，具体要求如下：

(1) 将表course的cname列的数据类型改为varchar(40). (2) 为表student增加一个新列: birthday(出生日期), 类型为datetime, 默认为空值.

(3) 将表sc中的grade列的取值范围改为小于等于150的正数. (4) 为Student表的“Sex”字段创建一个缺省约束，缺省值为?男? (5)为“Sdept”字段创建一个检查约束，使得所在系必须是?CS?、?MA?或?IS?之一。

(6)为Student表的“Sname”字段增加一个唯一性约束 (7)为SC表建立外键,依赖于Student表的fk_S_c约束。 (8)禁止启用Student表的“Sdept”的CHECK约束ck_student。 5、分别建立以下索引(如果不能成功建立,请分析原因) (1) 在student表的sname列上建立普通降序索引. (2) 在course表的cname列上建立唯一索引. (3) 在sc表的sno列上建立聚集索引.

(4) 在spj表的sno(升序), pno(升序)和jno(降序)三列上建立一个普通索引.

Transact-SQL提示:

1. 建立表, 修改表, 建立索引需具有create table的权限.

数据库原理与应用课程实验指导书

2. 创建基本表的SQL语句是CREATE TABLE . 简单语法格式:

CREATE TABLE 表名

( 列名数据类型 [default 缺省值] [not null] [，列名数据类型 [default 缺省值] [not null]]

……

[，primary key（列名 [，列名] …）]

[，foreign key （列名 [，列名] …） references 表名（列

名 [，列名] …）]

一般语法格式:

CREATE TABLE [ database_name.[owner].] table_name ({| table_constraint> }[,…n] )

[ON { filegroup | DEFAULT }] [TEXTIMAGE_ON {filegroup | DEFAULT }]

::={column_name constant_expression]

| [IDENTITY [(seed, increment )]] [ROWGUIDCOL] [] […n]

[，check（条件）] );

column_name | <

data_type}[DEFAULT

数据库原理与应用课程实验指导书

参数说明：

①[database_name.[owner].] table_name：定义表的名字，表名的长度不得超过128个字节，如果是临时表，则表名不能超过116个字符。

②：列的定义。

③column_name：列的名字。列的命名必须遵守有关数据库对象的命名规则。

④data_type：列的数据类型。

⑤DEFAULT constant_expression：定义该列的默认值。 ⑥IDENTITY：定义该列是一个标识列。当一个新的数据行插入表中的时候，SQL Server2000为标识列提供一个唯一的、递增的数值。在一张表格中，只能定义一个标识列。在定义标识列时，必须同时定义起始值和增量。

⑦Seed：定义标识列的起始值。所谓起始值就是插入表的第一行的数据的标识列的值。Increment：定义标识列的增量。所谓增量就是插入表的最近一行相对与前一行标识列的数据值的增量。

⑧ROWGUIDCOL：定义该列是一个行全局唯一的标识列。在一张表中只有一个唯一标识符列可以被定义为ROWGUIDCOL。column_constraint：定义与列相关联的约束。

⑨table_constraint：定义对表的约束。

⑩ON { filegroup | DEFAULT }：定义将表存储在某一个指定的文件组中，DEFAULT表示将表存储在默认文件组中。

数据库原理与应用课程实验指导书

[TEXTIMAGE_ON {filegroup | DEFAULT }]：如果表中有ntext、text或image类型的数据，则将这些数据存储在某一个指定的文件组里。

3. 修改表结构的SQL语句是ALTER TABLE, 修改的内容包括：修改、增加、删除列或约束、使约束和触发器无效等。简单语法格式:

alter table <表名>

[add <列名> <数据类型> [<列级完整性约束>]]

// 增加新列

[drop <完整性约束名>]

// 删除约束

[drop column <列名>] // 删除列

[alter column <列名> <数据类型> [<列级完整性约束>] ];

//修改列定义

其中列级完整性约束包括: ·空值约束 NOT NULL和NULL ·主关键字约束 PRIMARY KEY ·唯一性约束 UNIQUE

·参照完整性约束 FOREIGN KEY

数据库原理与应用课程实验指导书

一般语法格式:

ALTER TABLE table

{[ALTER COLUMN column_name { new_data_type [NULL| NOT NULL]|{ADD |DROP} ROWGUIDCOL }]

|ADD

{[]|

column_name

computed_column_expression }[,…n]

|ADD {}[,…n] |DROP column }[,…n]

|{CHECK

|NOCHECK}

CONSTRAINT

{ALL

{[CONSTRAINT]

constraint_name

|COLUMN

constraint_name [,…n]

|{ENABLE |DISABLE} TRIGGER {ALL |trigger_name [,…n]}

参数说明：

①ALTER COLUMN：修改已经存在的列的属性。

②{ADD |DROP} ROWGUIDCOL：将指定列定义成ROWGUIDCOL，或者删除该列的ROWGUIDCOL属性。 ③ADD

{[]|

column_name

computed_column_expression}[,…n]：增加新的列。

④ADD {}[,…n]:定义新的表约束。 ⑤DROP { [CONSTRAINT] constraint_name |COLUMN

数据库原理与应用课程实验指导书

column }[,…n]：删除约束或删除列。

⑥{CHECK

|NOCHECK}

CONSTRAINT

{ALL

constraint_name [,…n]}：使所有约束或者指定的约束有效或失效。

例：使cnst_example表中的约束salary_cap失效。

ALTER TABLE cnst_example NOCHECK CONSTRAINT

salary_cap

⑦{ENABLE |DISABLE} TRIGGER {ALL |trigger_name [,…n]}：使所有或部分触发器有效或无效。

例：使用T_SQL语句对学生表进行各种修改。

①向表中添加新的字段：在学生表中添加一个“class”字段，数据类型为字符型。

ALTER TABLE student add class char(10) ②删除表中的旧列：将学生表中的“class”字段删除。 ALTER TABLE student DROP column class ③更改表中的约束：

删除某列的约束：将SC表中的外码约束FK_sc_sno删除。

ALTER TABLE sc DROP FK_sc_sno

将Student表中的主码约束PK_student_sno删除。

ALTER

PK_student_sno

为某列添加约束：为Student表的“Ssex”字段创建一个缺

省约束，缺省值为?男?。

TABLE student DROP

数据库原理与应用课程实验指导书

ALTER TABLE student ADD constraint def_ssex

DEFAULT '男' FOR ssex

更改Student表中的“sno”字段的宽度为10及非空约束。

ALTER TABLE student ALTER COLUMN sno

char(10) NOT NULL

为Student表添加一个主码约束PK_student_sno。

ALTER TABLE student ADD CONSTRAINT

PK_student_sno PRIMARY KEY(sno)

为SC表添加一个外码约束FK_sc_sno。

ALTER TABLE sc ADD CONSTRAINT FK_sc_sno

FOREIGN KEY (sno) REFERENCES student(sno)

④给学生表增加“grade”字段并加上CHECK约束，让其不可以大于100。

ALTER TABLE student ADD grade int Constraint ch_grade check(grade<100)

Exec sp_help student

⑤给学生表中添加“birthday”字段，并且这个日期不能在录入当天的日期之后。

Alter table student Add birthday DATETIME NULL CONSTRAINT ch_birthday CHECK (birthday

⑥添加具有默认值的可为空的列：

在学生表中加入“matriculationday”字段，并且这一字段的默认

数据库原理与应用课程实验指导书

值为录入当天的日期。

Alter table student Add matriculationday smalldatetime NULL Constraint adddateflt Default getdate()

4.建立索引的SQL语句是CREATE INDEX, 语法格式: CREATE [UNIQUE] [CLUSTERED] INDEX <索引名>

ON <表名>(<列名>[ ASC | DESC ] [,<列名>[ ASC |

DESC ] … ] )

其中 UNIQUE指出是唯一索引, CLUSTERED指出是聚集索引。

数据库原理与应用课程实验指导书

实验三 SQL数据查询

一、实验目的

? 熟练掌握使用T-SQL语句进行单表查询，尤其要熟练掌握GROUP BY子句、HAVING 子句和集函数；

? 熟练掌握使用SQL标准语句和T-SQL扩展语句进行连接查询。

? 熟练掌握使用IN、比较符、ANY或ALL和EXISTS操作符进行嵌套查询操作。二、实验内容完成如下单表查询： 1、 2、 3、 4、 5、 6、

单表查询使用分组和集函数内连接外连接 IN子查询 EXISTS子查询

三、实验步骤

1、在studentdb数据库中Student、Course以及SC表中，完成以

数据库原理与应用课程实验指导书

下查询（必做）：

(1)查询course表中所有学分大于2并且序号小于5的课程信息。

(2)查询学分界于2和8之间的课程的信息。

(3)从course表中查询出课程号为1或4或7的课程的课程号、

课程名称、以及学分。

(4)从course表中查询学分大于3的课程信息，并按升序排列。 (5)查询每个学生及其选修课情况。

(6)查询选修2号课程且成绩在80分以上的学生信息。 (7)查询每个学生的学号、姓名、选修的课程及成绩。 (8)在student数据库中，查询所有计算机系学生的学号、选修课程号以及分数。(使用IN谓词)。

(9)查询选修了课程名为“操作系统”的学生学号和姓名。 (10)在student数据库中，查询所有计算机系学生的学号、选修课程号以及分数（使用EXISTS谓词）。

2、在spjdb数据库中S、P、J以及SPJ表中，完成以下查询（选做）：

① 完成P74习题5中的查询； ② 完成P127习题5中(1)~(7)的查询。

Transact-SQL提示:

1. SELECT语句的语法格式如下：

数据库原理与应用课程实验指导书

基本格式：

SELECT [ALL|DISTINCT] <表达式1>[,<表达式2>… ] FROM <表名1>[,<表名2>… ] [WHERE <逻辑表达式>]

[GROUP BY <列名>[,<列名>… ][HAVING <谓词|条件>]] [ORDER

名>[ASC|DESC]… ]]

一般格式：

SELECT [ALL|DISTINCT] [TOP n [PERCENT] [WITH TIES]]

[ INTO ]

[ FROM { < table_source > } [ , ... n ] ]

[ WHERE < search_condition > | < old_outer_join > ]

[ GROUP BY [ ALL ] group_by_expression [ , ... n ] [ WITH { CUBE | ROLLUP } ] ]

[HAVING ]

::={*|{table_name|view_name|table_alias }.*|{column_name}

[[AS] column_alias]}[,…n]

< table_source > ::= table_name [ [ AS ] table_alias ] [ WITH ( <

BY <列名>[ASC|DESC][,<列

数据库原理与应用课程实验指导书

table_hint > [ ,...n ] ) ]

| view_name [ [ AS ] table_alias ] [ WITH ( < view_hint > [ ,...n ] ) ]

| rowset_function [ [ AS ] table_alias ] | user_defined_function [ [ AS ] table_alias ]

| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] | < joined_table >

< joined_table > ::= < table_source > < join_type > < table_source > ON < search_condition >

| < table_source > CROSS JOIN < table_source > | [ ( ) < joined_table > [ ] ]

< join_type > ::= [ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ]

[ < join_hint > ] JOIN

< old_outer_join > ::= column_name { * = | = * } column_name 参数说明：

(1)ALL：默认设置，指定在结果集中可以显示重复行。 (2)DISTINCT：指定在结果集中只能显示唯一行，空值被认为相等。

(3)TOP n：指定只从查询结果集中输出前n行。N是介于0～4294967295之间的整数。

数据库原理与应用课程实验指导书

(4)PERCENT：从结果集中输出前百分之几行，当指定时带PERCENT，n的值必须介于0～100之间。

(5)WITH TIES：指定从结果集中返回附加的行，这些行包含与出现在TOP n（PERCENT）行最后的ORDER BY列中的值相等的值。如果指定了ORDER BY子句，则只能指定TOP…WITH TIES。

(6)：为结果选择的列。选择列表是以逗号分隔的一系列表达式。

(7)INTO ：创建新表并将结果行从查询插入新表中。

用户若要执行带 INTO 子句的 SELECT 语句，必须在目的数据库内具有 CREATE TABLE 权限。SELECT...INTO 不能与 COMPUTE 子句一起使用。new_table 的格式通过对选择列表中的表达式进行取值来确定。new_table 中的列按选择列表指定的顺序创建。new_table 中的每列有与选择列表中的相应表达式相同的名称、数据类型和值。

(8) FROM < table_source >: 指定要在Transact-SQL语句中使用的表或视图（带或不带别名均可）。可在语句中使用多达256个表。可将table变量指定为表源。 < table_source >中的参数说明见(19)。

(9)< old_outer_join >: 使用不标准的产品专用语法和 WHERE 子句指定外联接。*= 运算符用于指定左向外联接，=* 运

数据库原理与应用课程实验指导书

算符用于指定右向外联接。

注：这种语法具有潜在的二义性解释并且不标准，因此不提倡使用这种语法进行外联接。而应在 FROM 子句中使用联接运算符指定联接。不能在同一语句中同时使用这两种方法。

(10)：通过使用谓词限制结果集内返回的行。对搜索条件中可以包含的谓词数量没有限制。

(11) *：指定在FROM子句内返回所有表和视图内的所有列，列按FROM子句所指定的由表或视图返回，并按他们在表或视图中的顺序返回。

(12)column_name：要返回的列名，必要时限定column_name以避免二义性引用，当FROM子句中的两个表内包含重复名的列时会出现该情况。

(13)column_alias：是查询结果集内替换列名的可选名。 (14)GROUP BY ALL: 包含所有组和结果集，甚至包含那些任何行都不满足 WHERE 子句指定的搜索条件的组和结果集。如果指定了 ALL，将对组中不满足搜索条件的汇总列返回空值。不能用 CUBE 或 ROLLUP 运算符指定 ALL。

如果访问远程表的查询中有 WHERE 子句，则不支持 GROUP BY ALL 操作。

(15)group_by_expression : 是对其执行分组的表达式。group_by_expression 也称为分组列。group_by expression 可以是列或引用列的非聚合表达式。在选择列表内定义的列的别名不能

数据库原理与应用课程实验指导书

用于指定分组列。

说明 text、ntext 和 image 类型的列不能用于 group_by_expression。

对于不包含 CUBE 或 ROLLUP 的 GROUP BY 子句，group_by_expression 的项数受查询所涉及的 GROUP BY 列的大小、聚合列和聚合值的限制。该限制从 8,060 字节的限制开始，对保存中间查询结果所需的中间级工作表有 8,060 字节的限制。如果指定了 CUBE 或 ROLLUP，则最多只能有 10 个分组表达式。

(16)CUBE: 指定在结果集内不仅包含由 GROUP BY 提供的正常行，还包含汇总行。在结果集内返回每个可能的组和子组组合的 GROUP BY 汇总行。GROUP BY 汇总行在结果中显示为 NULL，但可用来表示所有值。使用 GROUPING 函数确定结果集内的空值是否是 GROUP BY 汇总值。

结果集内的汇总行数取决于 GROUP BY 子句内包含的列数。GROUP BY 子句中的每个操作数（列）绑定在分组 NULL 下，并且分组适用于所有其它操作数（列）。由于 CUBE 返回每个可能的组和子组组合，因此不论指定分组列时所使用的是什么顺序，行数都相同。

(17)ROLLUP: 指定在结果集内不仅包含由 GROUP BY 提供的正常行，还包含汇总行。按层次结构顺序，从组内的最低级别到最高级别汇总组。组的层次结构取决于指定分组列时所使用的

数据库原理与应用课程实验指导书

顺序。更改分组列的顺序会影响在结果集内生成的行数。

重要提示：使用 CUBE 或 ROLLUP 时，不支持区分聚合，如

AVG(DISTINCT

column_name)、COUNT(DISTINCT

column_name) 和 SUM(DISTINCT column_name)。如果使用这类聚合，SQL Server 将返回错误信息并取消查询。

(18) HAVING : 指定组或聚合应满足的搜索条件。HAVING 通常与 GROUP BY 子句一起使用。当 HAVING 与 GROUP BY ALL 一起使用时，HAVING 子句替代 ALL。如果不使用 GROUP BY 子句，HAVING 的行为与 WHERE 子句一样。

注：在HAVING子句中不能使用text、image和ntext数据类型。在SELECT语句中使用HAVING 子句不影响CUBE运算符分组结果集和返回汇总聚合行的方式。

(19) < table_source >中的参数说明:

table_name: 表名。FROM 关键字之后的表和视图的顺序并不影响返回的结果集。当 FROM 子句中出现重复名称时将报告错误。

[AS] table_alias: table_name、view_name 或 rowset_function 的别名，为方便起见而使用，或用于区分自联接或子查询中的表或视图。别名通常是一个缩短了的表名，用于在联接中引用表中的特定列。如果联接中的多个表中有相同名称的列存在，SQL Server 要求必须使用表名或别名来限定列名。（如果定义了别名则

数据库原理与应用课程实验指导书

不能使用表名）。

WITH ( < table_hint > ) : 指定表扫描、查询优化器要使用的一个或多个索引或查询优化器要对此表、此语句使用的锁定方法。有关更多信息，请参见\表提示\。

view_name: 是视图名称。视图是一个\虚拟表\，通常创建为一个或多个表中列的子集。

WITH ( < view_hint > ) :指定索引视图扫描。默认情况下，视图在查询优化器处理查询之前展开。视图提示只能用在 SELECT 语句中，而不能用于 UPDATE、DELETE 或 INSERT 语句。

rowset_function: 指定一个行集函数，该函数返回可替代表引用的对象。

user_defined_function : 指定用户定义的函数，该函数返回一个表。如果用户定义的函数是一个内置的用户定义函数，则前面必须加两个冒号，如：FROM ::fn_listextendedproperty

derived_table: 是从数据库中检索行的子查询。derived_table 用作对外部查询的输入。

column_alias: 替换结果集内列名的可选别名。在选择列表中放入每个列的一个别名，并将整个列别名列表用圆括号括起来。

: 由两个或更多表的积组成的结果集，例如： SELECT *

FROM tab1 LEFT OUTER JOIN tab2 ON tab1.c3 = tab2.c3 RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4

数据库原理与应用课程实验指导书

ON tab3.c1 = tab4.c1 ON tab2.c3 = tab4.c3

对于多个 CROSS 联接，请使用圆括号来更改联接的自然顺序。

: 指定联接操作的类型。

INNER: 指定返回每对匹配的行。废弃两个表中不匹配的行。如果未指定联接类型，则这是默认设置。

FULL [OUTER]: 指定在结果集中包含左表或右表中不满足联接条件的行，并将对应于另一个表的输出列设为 NULL。这是对通常由 INNER JOIN 返回的所有行的补充。

注：按此处指定的方法指定外联接或在 WHERE 子句中使用旧式非标准的 *= 和 =* 运算符都是可行的。不能在同一语句中同时使用这两种方法。

LEFT [OUTER]: 指定在结果集中包含左表中所有不满足联接条件的行，且在由内联接返回所有的行之外，将另外一个表的输出列设为 NULL。

RIGHT [OUTER]: 指定在结果集中包含右表中所有不满足联接条件的行，且在由内联接返回的所有行之外，将与另外一个表对应的输出列设为 NULL。

: 指定 SQL Server 查询优化器为在查询的 FROM 子句中指定的每个联接使用一个联接提示或执行算法。有关更多信息，请参见本主题下面的\联接提示\。

数据库原理与应用课程实验指导书

JOIN: 指明所指定的联接操作应在给定的表或视图之间执行。

ON : 指定联接所基于的条件。尽管经常使用列和比较运算符，但此条件可指定任何谓词，例如：

SELECT ProductID, Suppliers.SupplierID

FROM Suppliers JOIN Products ON (Suppliers.SupplierID = Products.SupplierID)

当条件指定列时，列不一定必须具有相同的名称或数据类型；但是，如果数据类型不一致，则这些列要么必须相互兼容，要么是 Microsoft? SQL Server? 能够隐性转换的类型。如果数据类型不能隐式转换，则条件必须使用 CAST 函数显式转换数据类型。

在 ON 子句中可能有仅涉及一个联接表的谓词。这样的谓词也可能出现在查询中的 WHERE 子句中。尽管这种谓词的放置在 INNER 联接中不会产生差别，但是在涉及 OUTER 联接时可能会导致不同的结果。这是因为 ON 子句中的谓词在应用于联接之前先应用于表，而 WHERE 子句在语意上应用于联接结果。

CROSS JOIN: 指定两个表的矢量积。这将返回相同的行，就好像在旧式的非 SQL-92 式联接中并没有指定 WHERE 子句。

2. 简单的SELECT查询：

SELECT语句在任何SQL语言中都是使用频率最高的语句，是SQL的灵魂。

?最简单的SELECT查询语法如下：

数据库原理与应用课程实验指导书

SELECT column_name [,… n] [ FROM tablename] ?使用TOP关键字

例：分别从course中检索出前2条及前面67%的教师的信息。

SELECT top 2 * FROM course

SELECT top 67 PERCENT * FROM course ?使用DISTINCT关键字 ?使用计算列

例：将teacher表中各教师的姓名、教工号及工资按95%发放的信息，第2条语句将工资按95%发放后列名改为?预发工资?。

select 教工号,姓名,工资*0.95 from teacher

select 教工号,姓名,工资*0.95 AS 预发工资 from teacher ?操作查询的列名

SELECT cno ?课程序号?，cname ?课程名称?, credit+1 ?修正学分? FROM course

?使用WHERE子句

例：查询course表，返回修正后的学分后仍然大于4的课程的代号、名称以及原先的学分。

SELECT cno AS 序号,cname AS课程名称，credit AS 学分 FROM course WHERE course+1>4

?使用LIKE子句进行模糊查询

例：从teacher表中分别检索出姓伍的教师的资料，或者姓名

数据库原理与应用课程实验指导书

的第2个字是寿或立的教师的资料。

SELECT * FROM teacher WHERE 姓名 LIKE ?伍%? SELECT * FROM teacher WHERE 姓名 LIKE ?_[寿，立]%? ?使用ORDER BY给数据排序

例：从course表中查询学分大于3的课程信息，并按升序排列。

SELECT cno AS ?序号?,cname AS ?课程名? , credit AS ?学分? FROM course

WHERE credit>3 ORDER BY credit ASC 3. 多表查询

数据库各表中存放着不同的数据，用户经常需要用多个表中的数据来组合提炼出所需要的信息，如果一个查询需要对多个表进行操作，就称为关联查询，关联查询的结果集或结果表称为表之间的连接。关联查询实际上是通过各各表之间共同列的关联来查询数据的，它是关系数据库查询最基本的特征。

?连接查询：如果需要对多张表中的数据进行查询，称之为连接查询。

①等值和非等值连接查询

例：查询每个学生及其选修课情况。

SELECT student.*,sc.* FROM student,sc WHERE student.sno=sc.sno

②外连接

数据库原理与应用课程实验指导书

例：以student表为主体列出每个学生的基本情况及其选课情况，若没有选课，只输出其基本情况。

SELECT student.sno,sname,sex,sbirth,sdept,sc.cno,grade FROM student,sc WHERE student.sno*=sc.sno ③复合条件连接

例：查询选修2号课程且成绩在80分以上的学生信息。 SELECT student.sno,sname,grade FROM student,sc WHERE student.sno=sc.sno AND sc.grade>80

例：查询每个学生的学号、姓名、选修的课程及成绩。 SELECT student,sc,course

WHERE student.sno=sc.sno AND sc.cno=course.cno ?嵌套查询

①带有IN谓词的子查询

例：在student数据库中，查询所有计算机系学生的学号、选修课程号以及分数。

SELECT sno,cno,grade FROM sc WHERE sno IN (SELECT sno FROM student WHERE sdept=?计算机系?) ②在嵌套查询中使用EXISTS关键字

例：在student数据库中，查询所有计算机系学生的学号、选修课程号以及分数

SELECT sno,cno,grade FROM sc WHERE EXISTS

student.sno,sname,cname,grade FROM

数据库原理与应用课程实验指导书

(SELECT sno FROM student WHERE sdept=?计算机系?) ?使用UNION子句

如果希望将多个不同的查询结果数据集组成一组数据，可以使用UNION子句，但有2条基本准则：

①每一个结果集的数据类型必须相同。

②每一个结果集中的列数必须相等，排列顺序必须相互对应。例：将pubs数据库authors表格中的列state进行排序。 SELECT state FROM authors UNION

SELECT state FROM publishers ORDER BY state

注意：UNION子句会自动删除查询结果中重复的行，如果刻意要求获得所有的行，可以使用ALL关键字，如上例的UNION改写为UNION ALL。

数据库原理与应用课程实验指导书

实验四 SQL数据操纵

一、实验目的

熟练掌握使用SQL Server企业管理器和T-SQL向数据库输入数据、修改数据和删除数据的操作。

二、实验内容

1、熟练掌握使用SQL Server企业管理器数据库输入数据、修改数据和删除数据的操作；

2、熟练掌握使用T-SQL向数据库输入数据、修改数据和删除数据的操作。三、实验步骤

1. 将教材P82表中的数据添加到数据库STUDENTDB中. 2. 将教材P75表中的数据添加到数据库SPJDB中. 体会执行插入操作时检查实体完整性规则、参照完整性规则和用户定义完整性规则的效果.

3. 设计几个删除操作，体会执行删除操作时检查参照完整性规则的效果.

4. 设计一组更新操作, 体会执行更新操作时检查实体完整性规则、参照完整性规则和用户定义完整性规则的效果.

5. 设计一组更新操作, 它需要另外一个表中的数据作为更新条

数据库原理与应用课程实验指导书

件(如将选修了“信息系统”课程的成绩均提高15% )。

6. 设计一个删除操作，它需要另外一个表中的数据作为删除条件(如将“刘晨”的选课记录删除 )。

Transact-SQL提示:

1.插入语句INSERT的一般格式: 格式1: 一次插入一个元组.

INSERT [INTO] <表名>[(<列名> [,<列名>…] ) ] VALUES(<表达式> [,<表达式>…] ) 格式2: 一次插入多个元组(子查询结果).

INSERT [INTO] <表名>[(<列名>[,<列名>…] ) ] <子查询>

从一个关系中选择一些元组插入到另一个关系中（当然相应属性要出自同一个域）。

2.数据更新语句UPDATE的一般格式: UPDATE <表名>

SET <列名> = <表达式> [, <列名> = <表达式>…] [ [FROM <表名> ] WHERE <逻辑表达式> ]

使用说明: 使用WHERE子句指定条件, 以更新满足条件的一些元组的属性值, 并且一次可以更新多个属性; 更新条件可以与其他的表相关（使用FROM指定）; 如果没有WHERE子句，则

数据库原理与应用课程实验指导书

更新全部元组。

例: 将所有选修“数据库”课程的成绩提高10% update SC set grade=grade*1.1

from COURSE where SC.cno=COURSE.cno and cname=?数据库?

3.删除操作语句DELETE的一般格式： DELETE FROM <表名>

[ [ FROM <表名> ] WHERE <逻辑表达式> ]

使用说明: 如果没有指定删除条件则删除全部元组; 删除条件可以与其他的表相关(使用FROM指定); DELETE语句只删除表中的元组(数据), 保留表结构.

例: 删除计算机系学生的选课记录. delete from SC

from STUDENT where SC.sno=STUDENT.sno and sdept=?CS?

4. 用TRUNCATE TABLE清空表格

TRUNCATE TABLE语句可以删除表格中所有的数据，只留下一个表格的定义。该操作要比DELETE语句快，因为TRUNCATE TABLE是不记录日志的操作。TRUNCATE TABLE将释放表的数据和索引所占据的所有空间。语法如下：TRUNCATE table-name。

例：删除表格course中的数据。 TRUNCATE course

数据库原理与应用课程实验指导书

注意：由于TRUNCATE TABLE操作不进行日志的记录，所以删除数据后无法恢复，同DROP TABLE一样，只有数据库的拥有者可以执行TRUNCATE TABLE命令，而且权力无法转让。 5.在执行INSERT、UPDATE和DELETE操作时可能会受到关系完整性的约束，这种约束可以保证数据库中的数据是正确的。

数据库原理与应用课程实验指导书

实验五数据库保护

一、实验目的

? ? ?

通过实验使学生加深对数据完整性的理解，

掌握使用SQL语句进行数据库权限授予和回收的方法掌握SQL Server中数据库备份和恢复的方法。

二、实验内容

1、实体完整性、参照完整性和用户定义的完整性实现； 2、使用SQL语句对数据库进行授权和权力回收操作； 3、掌握数据库备份和恢复的方法三、实验步骤简单的完整性

1、主键的添加、删除和修改操作方法有两种：

?企业管理器操作法，在企业管理器中，用右键单击要操作的数据库表，从弹出的快捷菜单中选择设计表选项，然后根据提示操作。

? SQL语句操作法。其语法形式如下： CREATE TABLE Student (Sno CHAR(9),

Sname CHAR(20) NOT NULL,

数据库原理与应用课程实验指导书

Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20), PRIMARY KEY(Sno) )

在SQL Server 2000中查看违反以上定义的实体完整性规则后的结果以及DBMS采取的处理方法。

2、定义SC中的参照完整性 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), )

在SQL Server 2000中查看违反以上定义的参照完整性规则后的结果以及DBMS采取的处理方法。

3、Student表中的Ssex只允许取“男”或“女”。 CREATE TABLE Student (Sno CHAR(9),

Sname CHAR(20) NOT NULL,

Ssex CHAR(2) CHECK(Ssex IN (?男?，‘女’)),

数据库原理与应用课程实验指导书

Sage SMALLINT, Sdept CHAR(20), PRIMARY KEY(Sno) )

在SQL Server 2000中查看违反以上定义的用户自定义完整性规则后的结果以及DBMS采取的处理方法。

数据库权限

1．管理数据库用户：安装SQL Server之初只有两个用户，一个是sa（系统管理员），另一个是guest（系统安装时创建的一个可以对样板数据库作最基本查询的用户）。

?添加数据库的用户

利用T_SQL：执行系统存储过程sp_adduser，格式是： sp_adduser login_id[,username[,grpname]]

?删除数据库用户：相当于删除一个登录账号在这个数据库中的映射。

利用T_SQL：使用系统存储过程sp_dropuser。如：sp_dropuser student

2．管理数据库角色 ?创建自定义数据库角色

使用T_SQL ：使用系统存储过程sp_addrole可以创建数据库新角色，同时使用系统存储过程sp_addrolemember和 sp_droprolemember分别用于向角色中增加或从角色中删除成员。

数据库原理与应用课程实验指导书

如在Student数据库中创建NewRoles新角色，并且将用户Shanjk添加到该角色中：

Exec Sp_addrole ?NewRoles?

Exec sp_addrolemember ?NewRoles?,?Shanjk?

?删除用户自定义角色：不能删除一个有成员的角色，在删除这样的角色之前，应先删除其成员。只能删除自定义的角色，系统的固定角色不能被删除。

利用T_SQL：使用系统存储过程sp_droprole，如：sp_droprole ?NewRoles?

3．权限管理：

分为语句权限和对象权限管理两类。前者是对用户执行语句或命令的权限的管理；后者是系统管理员、数据库拥有者、数据库对象拥有者对数据库及其对象的操作权限的控制。

利用T_SQL管理权限

①语句授权：GRANT {ALL|statement_list} TO {PUBLIC|name_list}

其中：ALL即全部语句，只有系统管理员可以使用此选项，因为只有系统管理员可以授予或收回CREATE DATABASE的权限；statement_list给出授权的语句列表，这些语句可以是CREATE DATABASE（如果执行这个语句的用户是系统管理员）、CREATE DEFAULT、CREATE PROCEDURE、CREATE RULE、CREATE TABLE、CREATE VIEW、DUMP DATABASE和DUMP

数据库原理与应用课程实验指导书

TRANSACTION等；

PUBLIC说明这些语句的执行权限将授予所有的用户；name_list是数据库用户名或组名，说明这些语句的执行权限授予哪些用户或组。

②对象授权：

GRANT{ALL|Permission_list}ON{table_name[(colume_list)]|view_name[(column_list)]|

stored_procedure_name|extended_stored_procedure_name} TO {PUBLIC|name_list}

例：将对student表的age和grade列的修改权限授予用户名为wang的用户。

GRANT UPDATE ON student(age,grade) TO wang 例：将执行存储过程sp_upd_student的权限授予用户名为wang的用户。

GRANT EXECUTE ON sp_upd_student TO wang ③收回语句授权：

REVOKE{ALL|statement_list}FROM{PUBLIC|name_list} ④收回对象授权：

REVOKE{ALL|permission_list}

ON{table_name[(column_list)]|view_name[(column_list)]|