数据库实验报告一

学 号：********** * ***** 专业班级：计网121班 指导老师：*** 成 绩：

广西大学2014

数据库原理实验报告

实验一 SQL Server的安装及环境介绍

实验目的

1. 掌握SQL Server 服务器的安装方法（安装版本为SQL Server 2012）。 2. 了解SQL Server 服务器的环境

实验要求

1. 完成SQL Server的安装、启动。

实验内容

安装SQL Server。

实验步骤

（1）下载SQL Server2012集成包并解压，运行后出现“SQL Server安装中心”。在左侧的目录树中选择“安装”。

（2）在右侧的选择项中，选择第1项目“全新安装或向现有安装添加功能”，然后就进入了安装程序。

（3）输入产口密钥，许可条款，安装程序支持文件。

（4）接下来，才是正式安装SQL Server程序。这个步骤看起来跟刚才在准备过程中的一样，都是扫描本机，防止在安装过程中出现异常。现在并不是在重复刚才的步骤，从下图明显看出这次扫描的精度更细，扫描的内容也更多。

（5）功能选择。勾选全部的安装组件，方便以后使用。

（6）实例配置。我们这里安装一个默认实例。 （7）服务账户配置。 （8）数据库引擎配置。

（9）之后的配置无需改动，一路下一步即可，等待安装成功。

请你练习

1.

如果在安装时设置身份验证模式为混合模式，那SQL Server默认生成的系统管理员账户名是？

答：系统管理员账户名sa

2.

SQL Server 2008中有哪几种身份验证模式？

答：Windows身份、SQlServer和Windows身份验证模式

3.什么是实例、默认实例和命名实例？在一台计算机上是否可以安装多个实例？

答：“实例”，就是一个 SQL Server 数据库引擎。一台计算机上最多只有一个默认实例，也可以没有默认实例，默认实例名与计算机名相同。“命名实例”就是自己使用这自己命名的事例。客户端连接命名实例时，必须使用以下计算机名称与命名实例的实例名组合的格式。 SQL Server支持在同一台计算机上同时运行多个 SQL Server 数据库引擎实例。 分析与讨论:

1、程序运行出错，登录用户‘sa’无法连接数据库

解决方案：打开数据库企业管理器--展开sqlserver组--右键（local）数据库 属性 ---安全性---身份验证 选sqlserver和Windows身份验证模式 ---确定

2、数据库选择的是默认位置，要改变路径，右键——属性——数据库设置，填写路径保存即可。

实验二 SQL Server常用管理工具和使用

实验目的

1. 理解服务的概念，掌握SQL Server服务的启动、暂停和停止。

2. 熟悉SQL Server配置管理工具的使用，掌握使用管理工具查看数据库信息的方法。 3. 掌握帮助文档的使用

实验要求

1. 使用多种方法启动、暂停和停止SQL Server服务。 2. 使用管理工具查看当前数据库信息。

实验内容

1. 启动、暂停和停止SQL Server服务。 2. 查看帮助文档

实验步骤与结果

实验步骤

（1）依次”计算机”——“管理”——“服务和应用进程”——“服务”，打开服务项 （2）找到一项名为“SQL Server (MSSQLSERVER)”的服务，查看其描述和状态。

（3）停止该服务。 （4）启动该服务。 （5）暂停该服务。 （6）恢复该服务。

（7）在SQL Server配置管理器中尝试重复上述操作。

（8）在SQL Server配置管理器中还可以查看到SQL Server服务占用的端口和本机IP地址。点击左侧“MSSQLSERVER的协议”选择“TCP/IP”,然后选择IP地址选项卡。可以看到端口号。

（9）使用命令NET管理MSSQLSERVER服务。停止服务。命令：NET STOP MSSQLSERVER （10）启动服务。命令：NET START MSSQLSERVER （11）暂停服务。命令：NET PAUSE MSSQLSERVER

（12）重新开启服务。命令：NET CONTINUE MSSQLSERVER （9）~（12）截图如下：

请你练习

1. 上网查找在osql命令的作用和使用方法，并使用该命令登录SQL Server查看版本。

OSQL —？命令可查看osql命令的用法

*通过命令可知该数据库版本为SQL 2012 11.0 NT x64 上图列举了OSQL的一些常用命令。

分析与讨论:

SQL server的应用进程我服务会有多个，尤其是服务，停止或暂停了某些应用进程和服务，都可能是数据库无法正常的使用,所以不要停止相关进程和服务。

实验三 创建数据库

实验目的

1. 掌握在图形界面下创建数据库的方法。 2. 掌握使用SQL语句创建数据库的方法。

3. 熟悉SQL Server Management Studio的环境。

实验要求

1. 熟练使用两种方法创建数据库。

实验内容

创建数据库。

实验步骤和结果：

（路径选择本机系统默认的路径）

（1）明确需求。

（2）使用向导创建上诉描述的数据库。

（3）单击“连接”按钮，便可以进入【SQL Server Management Studio】窗口。

（4）在“数据库”节点上右击，弹出如下图所示的快捷菜单，选择“新建数据库”命令。 （5）在“常规”数据页的“数据库名称”文本框中，输入要创建的数据库名称和其他属性

信息。

（6）单击“确定”。数据库建立成功。

（7）使用T-SQL语言创建数据库。需求：使用查询分析器新建一个数据库，名称为“LIB”，其主要数据文件大小为4M，最大文件大小为10M，每次增长2M；次要数据文件大小为1M；日志文件大小为1M；三个文件的文件名自定，上述没有说明的选项都采用默认值。 （8）点击“新建查询”，键入SQL语句。

CREATE DATABASE LIB ON

( NAME = bdata1,

FILENAME =’C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\DATA

\\bdata3.mdf', SIZE = 2,

MAXSIZE = 10, FILEGROWTH = 2 ), ( NAME = bdata2,

FILENAME = ‘C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\DATA

\\bdata4.ndf', SIZE = 1 ) LOG ON

( NAME = blog,

FILENAME =’ C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\DATA

\\blog.ldf', SIZE = 1 ) （9）点击“执行”

执行以上代码后发现以下问题：

CREATE DATABASE 语句失败。主文件必须至少是 5 MB 才能容纳 model 数据库的副本。

将主文件大小改为5M后，再执行，提示命令功能执行成功

请你练习

1. 熟悉SQL语言建库的语法。

2. 创建一个test数据库，其主数据文件逻辑名test_data，物理文件名test_data.mdf，初始

大小10MB，最大尺寸为无限大，增长速度1MB；数据库日志文件逻辑名称为test_log，物理文件名为test_log.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为10%。

3. 自行查找删除数据库T-SQL语句。

删除语句为：drop database text，其中text为数据库的名称

分析与讨论:

1、题目中要求数据库主文件的初始大小位4，运行后出现问题：CREATE DATABASE 语句失败。

主文件必须至少是 5 MB 才能容纳 model 数据库的副本。所以要把主文件的厨师大小改为5MB，其他条件可以不变，即可执行成功。

2、因为数据库的路径选择的是安装的默认路径，所以对练习中文件的路径也要做相应的改变，即要修改为本数据库默认的路径。否则无法创建数据库成功。

实验四 管理数据库

实验目的

1. 2. 3. 4.

进一步熟练企业管理器和查询分析器的使用。 掌握附加和分离数据库的方法。 熟悉数据库的更名。 掌握数据库的删除。

实验要求

1. 使用T-SQL语句创建和修改数据库。 2. 掌握其他常用的数据库管理操作。

实验内容

1. 使用T-SQL语句修改数据库。 其他数据库管理操作。

实验目的

5. 6. 7. 8.

进一步熟练企业管理器和查询分析器的使用。 掌握附加和分离数据库的方法。 熟悉数据库的更名。 掌握数据库的删除。

实验要求

3. 使用T-SQL语句创建和修改数据库。 4. 掌握其他常用的数据库管理操作。

实验内容

1 使用T-SQL语句修改数据库。 2. 其他数据库管理操作。

实验步骤

（1） 建库需求。创建一个名为“Test”数据库。主要数据文件：逻辑文

件名为TestData1，实际文件名为TestData1.mdf；初始容量为5MB，最大容量为10MB，递增量为1MB。次要数据文件：逻辑文件名为TestData2，实际文件名为TestData2.ndf；初始容量为1MB，最大容量为10MB，递增量为1MB。事务日志文件：逻辑文件名为TestLog，实际文件名为TestLog.ldf；初始容量为1MB，最大容量为5MB，递增量为1MB。

（2） 修改需求。按照下面的要求修改数据库Test。主要数据文件的容量

为2MB，最大容量为20MB，递增量为2MB。次要数据文件的容量为2MB，最大容量为20MB，递增量为2MB。事务日志文件的容量为2MB，最大容量为10MB，递增量为2MB。

（3）启动“企业管理器”，在控制面板的“树形目录”中选择“数据库”节点，单击鼠标右键，在弹出菜单中选择“新建数据库”命令，出现 “数据库属性”对话框。

（4）设置常规选项卡：在“名称”文本框中输入数据库名称“Test”； （5）设置数据文件选项卡：在文件名字段中输入数据文件逻辑文件名“TestData1”；设置该文件初始大小为1MB；标记“文件属性”栏下的“文件自动增长” 复选框，并选中“按兆字节”设置文件容量递增值为1。在“最大文件大小”对话框中选择“将文件增长限制为(MB)”为10。同样，在第二行输入次要数据文件信息，并进行相应设置。设置事务日志选项卡：在文件名字段中输入数据文件逻辑文件名“TestLog”；设置该文件初始大小为1MB，标记“文件属性”栏下的“文件自动增长”复选框，并选中“按兆字节”设置文件容量递增值为1；在“最大文件大小”对话框中，选择“将文件增长限制为(MB)”为5。

（6）单击“确定”按钮，完成数据库的创建。

（7）在“树状目录窗格”中，选择新建的数据库“Test”，单击鼠标右键，在弹出菜单中选择“属性”命令，打开数据库属性窗口；

（8）选择数据文件选项卡，在其中将主要数据文件和次要数据文件的容量改为2MB，最大容量改为20MB，递增量改为2MB。

（9）选择事务日志选项卡，在其中将事务日志文件的容量改为2MB，最大容量改为10，递增量改为2。

（10）使用Transact-SQL语句创建和修改数据库。 （11）修改数据库 （12）点击“执行”，执行修改。

（13）在“树状目录窗格”中，选择新建的数据库“Test”，单击鼠标右键，在弹出菜单中选择“属性”命令，打开数据库属性窗口，查看更新是否已执行。 （14）重命名数据库。选择数据库“Test”，单击鼠标右键，在弹出菜单中选择“重命名”命令

（15）分离数据库。

（16）分离数据库准备就绪后，请单击“确定”。 （17）右键单击“数据库”，然后单击“附加” （18）单击“确定”，附加数据库。 （19）删除数据库。选择数据库“Test”，单击鼠标右键，在弹出菜单中选择“删除”命令。然后在弹出的窗口中勾选“关闭现有连接”

请你练习

1. 认真完成本节数据库管理的相关练习，自己制定需求，然后按“建立-修改-重命名-分离

-附加-删除”顺序操作。 建立：

修改：

ALTER DATABASE Test MODIFY FILE

(NAME=TestData1, SIZE=7, MAXSIZE=20, FILEGROWTH=2) GO

ALTER DATABASE Test MODIFY FILE

(NAME=TestData2, SIZE=7, MAXSIZE=20, FILEGROWTH=2) GO

ALTER DATABASE Test MODIFY FILE

(NAME = TestLog, SIZE=3, MAXSIZE=10, FILEGROWTH=2) GO

重命名：

分离 ：

附加：

删除：

2.查询“分离”和“附加”操作的T-SQL命令分别是什么？ 分离：EXEC sp_detach_db DatabaseName 附加：CREATE DATABASE databasename

ON (FILENAME = 'C:\\Database\\dbname.mdf') ----路径和名称 FOR ATTACH | FOR ATTACH_REBUILD_LOG

分析与讨论：

1、在 64 位和 32 位环境中，SQL Server 磁盘存储格式均相同。 因此，可以将 32 位环境中的数据库附加到 64 位环境中，反之亦然。 从运行在某个环境中的服务器实例上分离的数据库可以附加到运行在另一个环境中的服务器实例。

2、分离数据库是指将数据库从 SQL Server 实例中删除，但使数据库在其数据文件和事务日志文件中保持不变。 之后，就可以使用这些文件将数据库附加到任何 SQL Server 实例，包括分离该数据库的服务器。如果存在下列任何情况，则不能分离数据库：

已复制并发布数据库。 如果进行复制，则数据库必须是未发布的。 必须通过运行 sp_replicationdboption 禁用发布后，才能分离数据库

3、附加数据库时，所有数据文件（MDF 文件和 NDF 文件）都必须可用。 如果任何数据文件的路径不同于首次创建数据库或上次附加数据库时的路径，则必须指定文件的当前路径。

实验五 表的创建

实验目的

1. 熟练掌握用Transact-SQL语句创建基本表的方法。 2. 熟练掌握基本表的特性。

3. 了解SQL Server的基本数据类型。 4. 使用SSMS管理平台创建基本表。

实验要求

1. 熟练使用企业管理器创建和删除基本表。

2. 是用查询分析器，完成用SQL语句创建和删除基本表。

实验内容

创建、删除基本表。

实验步骤

（1）打开企业管理器。

（2）选择一个创建好的数据库test，单击数据库中的表对象，然后右击窗口右侧选择新建表

（3）在这个窗体中，列名列表示表的字段名，可以在这个窗体中为字段选择数据类型和长度以及是否可以为空值。

（4）在“id”数据项上设置主键。右键单击该数据项选择“设置主键”

（5）设置自动增长后，插入输入时可以不填主键，主键可以自动生成

（6）填完正在创建表的其他信息，单击工具栏上的保存图标,输入表的名称 （7）输入表的名称后，单击确定系统会创建表

（8）在查询分析器中利用SQL语句创建表。点击“新建查询”。 （9）在查询窗体中输入创建表的T-SQL语句。

（10）单击工具栏上的图标按钮，执行分析查询，以检查输入的SQL语句是否存在语法错误。

（11）创建一个更为复杂的学生表，引入SQL Server创建表的更多特性 （12）在查询窗体中输入T-SQL语句

请你练习

1.建表练习，按下列要求通过SSMS创建表。 属性列 st_id st_nm st_sex st_birth 数据类型 长度 nVarChar 9 nVarChar 8 nVarChar 2 datetime 空值 Not Null Not Null Null Null 列约束 PK 说明 学生学号 学生姓名 学生性别 出生日期 st_score st_date st_from st_dpid st_mnt int datetime nChar nVarChar tinyint 20 2 Null Null Null Null Null 入学成绩 入学日期 学生来源 所在系编号 学生职务

2. 建表练习，按下列要求通过T-SQL语句创建表。 属性列 数据类型 长度 cs_id nVarChar 4 cs_nm nVarChar 20 cs_tm int cs_sc int 创建上表的T—SQL语句为：

空值 Not Null Not Null Null Null 列约束 PK 说明 课程编号 课程名称 课程学时 课程学分 IF OBJECT_ID('class') IS NOT NULL DROP TABLE class GO

CREATE TABLE class (

cs_id nVarChar(4) PRIMARY KEY, cs_nm nVarChar(20) Not Null, cs_tm int, cs_sc int, ) ON [PRIMARY] GO

exec sp_addextendedproperty N'MS_Description', N'课程编号', N'user', N'dbo', N'table', N'class', N'column', N'cs_id'

exec sp_addextendedproperty N'MS_Description', N'课程名称', N'user', N'dbo', N'table', N'class', N'column', N'cs_nm'

exec sp_addextendedproperty N'MS_Description', N'课程学时', N'user', N'dbo', N'table', N'class', N'column', N'cs_tm'

exec sp_addextendedproperty N'MS_Description', N'课程学分', N'user', N'dbo', N'table', N'class', N'column', N'cs_sc'

GO

3. 建表练习，按下列要求通过T-SQL语句创建表。

属性列 数据类型 长度 cs_id nVarChar 4 st_id nVarChar 9 score int sltdate datetime 创建上表的T—SQL语句为：

空值 Not Null Not Null Null Null 列约束 FK FK 说明 课程编号 学生编号 课程成绩 选课日期 IF OBJECT_ID('class_student1') IS NOT NULL DROP TABLE class_student1 GO

CREATE TABLE class_student1 (

cs_id nVarChar(4) Not Null FOREIGN KEY references class(cs_id), st_id nVarChar(9) Not Null FOREIGN KEY references student(st_id), score int,

sltdate datetime, ) GO

exec sp_addextendedproperty N'MS_Description', N'课程编号', N'user', N'dbo', N'table', N'class_student1', N'column', N'cs_id'

exec sp_addextendedproperty N'MS_Description', N'学生编号', N'user', N'dbo', N'table', N'class_student1', N'column', N'st_id'

exec sp_addextendedproperty N'MS_Description', N'课程成绩', N'user', N'dbo', N'table', N'class_student1', N'column', N'score'

exec sp_addextendedproperty N'MS_Description', N'选课日期', N'user', N'dbo', N'table', N'class_student1', N'column', N'sltdate'

GO

执行以上代码后，课得到下表：

分析与讨论:使用外键链接时，列的数据类型和数据长度必须要一样

实验六 表的维护

实验目的

1. 熟练掌握使用T-SQL语句对表的结构进行修改。 2. 熟练掌握使用SSMS管理工具删除表、重命名表。 3. 熟练掌握使用T-SQL语句删除表、重命名表。

实验要求

1. 了解常用的修改表定义语句。

2. 完成已经创建成功的表上的修改表定义的操作。

实验内容

修改表定义练习。

实验步骤

（1） 右键点击所要修改的学生表，选择“设计表”。在空白处点右键，选择“属性”，

弹出另一窗体，在此可以更改约束，也可以增加约束

（2） 利用Transact-SQL语句修改表。使用上一节最后完成的Student表。先做好准

备，通过SSMS删除Sage上的check约束，Ssex上的check约束，sno上的主键约束。

（3） 修改Sage列，允许空值，类型为smallint。添加一列列名为Sdept，类型为

varchar(20)。 use Test

alter table Student

add Sage smallint not null go

alter table Student add Sdept varchar(20)

（4） 更改表以删除列

（5）修改表的列以增加约束。

（6）查看Student表上添加的约束。 exec sp_helpconstraint student0

由于执行了很多次，所以结果有多个。

（7）修改表的列以删除约束。

（8）修改列的属性。将Student表中的Sname长度改为20。 use Test go

alter table student0

alter column sname char(20)

（9）将Student表重新命名成Stu。 use TEST go

Sp_rename 'student0','stu'

（10）将stu表的列Sname重命名为T-Sname。 use TEST go

Sp_rename 'stu，Sname','T-Sname','column'

请你练习

1. 为“stu”表添加“dp_count”列（数据类型为nvarchar，长度为3，允许为空）。

use TEST alter table stu

add dp_count nvarchar(3)

2. 修改“stu”表的“dp_count”列数据类型为int。

use TEST alter table stu

alter column dp_count int

3. 删除“stu”表的“dp_count”列。

use TEST alter table stu drop column dp_count

4.删除表“stu”。

分析与讨论：在对表进行修改操作时，应先将各列的约束清除，否则无法进行修改。

实验七 数据查询初步

实验目的

1. 掌握常规的select语句的使用方法。 2. 掌握其他数据操纵语句的使用。 3. 熟练通过SQL语句导入数据

实验要求

1. 掌握简单select语句使用方法。

2. 熟悉关系型数据库查询的基本思想。

实验内容

1. Select语句简单查询。 2. T-SQL数据操纵语句。

实验步骤

1、使用insert语句向数据库中插入数据

2、打开企业管理器，右键点击stu表，选择“选择前1000行

3、update语句可以用来更新行。

use TEST UPDATE stu SET

Sbir = '1991-1-2' ,Sage = 25 WHERE Sname = 'gxustu'

GO

（4）delete语句。delete语句用于删除数据行。 （5）创建表。新建查询，使用SQL语句导入数据。 （6）查询EMP表中的全部信息。 select * from [Test].[dbo].[EMP];

（7）查询EMP表中的EMPNO、ENAME、SAL三列，并将名称显示为职工号、姓名、工资。 select EMPNO 职工号,ENAME 姓名,SAL 工资 from [Test].[dbo].[EMP];

（8）查询EMP表中的ENAME、DEPTNO、JOB、SAL四列，并将名称显示为姓名、部门号、工作、工资，并使用SAL为依据排序。

select ENAME 姓名,DEPTNO 部门号,Job 工作,SAL 工资 from [Test].[dbo].[EMP] ORDER BY SAL;

请你练习

1. 查找insert、delete、update语句的格式，练习使用。

插入：INSERT INTO [数据库名].[dbo].[表名] ([列1] ,[列2] ,[列3]) VALUES

(值1 ,值2 ,值3） GO

删除：DELETE FROM [数据库名].[dbo].[表名] WHERE 列名 = '列值 GO

更新：use 数据库名 UPDATE表名 SET

Sbir = '1991-1-2' ,Sage = 25

WHERE Sname = 'gxustu' GO

2、按下图显示的表定义，通过SSMS建表。写出插入这些数据的SQL语句。

建表语句为：

use TEST

create table JOB(

job_id smallint primary key,

job_desc varchar(50), min_v tinyint, max_v tinyint）

T_SQL语句为：

insert into JOB values(1,'New Hire.Job not specified',10,10); insert into JOB values(2,'Chief Executive',200,250);

insert into JOB values(3,'Business Operations Manager',175,225); insert into JOB values(4,'Chief Financial Offical',175,250); insert into JOB values(5,'Publisher',150,250); insert into JOB values(6,'Manager Editer',140,225); insert into JOB values(7,'Maketing Manager',120,200);

insert into JOB values(8,'Public Relations Manager',100,175); insert into JOB values(9,'Acquisitions Manager',75,175); insert into JOB values(10,'Productions Manager',75,165); insert into JOB values(11,'Operations Manager',75,150); insert into JOB values(12,'Editor',25,100);

insert into JOB values(13,'Sales Representative',25,100); insert into JOB values(14,'Designer',25,100);

分析与讨论:

有两种情况UPDATE不会对影响表中的数据。

1. 当WHERE中的条件在表中没有记录和它匹配时。

2. 当我们将同样的值赋给某个字段时，如将字段abc赋为'123'，而abc的原值就是'123'。

实验八 数据查询巩固

实验目的

1. 巩固上节数据查询操作。 2. 分组、排序查询。

3. 了解示例数据库Northwind结构

实验要求

1. 熟练掌握各种数据查询方法。

2. 掌握关系型数据库查询的基本思想。

实验内容

1. 数据查询练习。

附加Northwind数据库，了解其结构。

实验步骤

(1) 从EMP数据库中查出有多少种不同的工作，列名为“工作”。 select distinct JOB 工作 from [Test].[dbo].[EMP]

（2）从EMP数据库中查出“DEPTNO”等于30的所有人员信息。

select * from [Test].[dbo].[EMP] where DEPTNO=30

（3）查出所有办事员的姓名、职工号、所属部门。

select ENAME 姓名,EMPNO 职工号,DNAME 部门 from [Test].[dbo].[EMP],[Test].[dbo].[DEPT] where JOB='办事员'

（4）查出部门号为30，薪水大于100的员工信息。

select * from [Test].[dbo].[EMP] where DEPTNO=30 and SAL>=100

（5）查出所在部门号为10，职位不是办事员又不是部门经理的所有人。 select * from [Test].[dbo].[EMP]

where DEPTNO=10 and JOB!='办事员' and JOB!='部门经理' （6）在EMP中选择所有姓王的雇员信息。

select * from [Test].[dbo].[EMP] where ENAME like'王%' （7）按部门升序、工资降序输出EMP表。 select * from [Test].[dbo].[EMP] where DEPTNO=10 order by SAL desc

（8）按部门升序，工资降序输出EMP表。 select * from [Test].[dbo].[EMP]

where DEPTNO=10 order by SAL desc

（9）下面进行Northwind数据库的查询练习。首先要附加数据库。右键单击“数据库”选择“附加”。在弹出的对话框中选择添加，选择Northwnd数据库。 （10）在NorthWind中查询出每个供应商所提供产品的平均价格。

select SupplierID,avg(UnitPrice) from [Northwind].[dbo].[Products] group by SupplierID

（11）在NorthWind中查询得到不同的地区有多少家供应商的信息。

select Country,count(*)

from [Northwind].[dbo].[Suppliers] group by Country

（12）在NorthWind中查询显示哪些供应商有传真。

select CompanyName,Fax

from [Northwind].[dbo].[Suppliers] where fax is not null

（13）在NorthWind中查询不同产品所订购的总价。

select ProductID,sum(Quantity*UnitPrice) from [Northwind].[dbo].[Order Details] group by ProductID order by ProductID

（14）在NorthWind中查询出生日期在1940年至1960年之间的雇员信息。

select *

from [Northwind].[dbo].[Employees]

where BirthDate >= '1940-1-1' and BirthDate < '1960-1-1'

（15）查询“Northwnd”示例数据库中供应商的ID、公司名称、地区、城市和电话字段的值。条件是“地区等于华北”并且“联系人头衔等于销售代表”。

Select SupplierID,CompanyName,Address,City from [Northwind].[dbo].[suppliers] where Region='华北' and ContactTitle='销售代表'

（16）查询“Northwind”示例数据库中供应商的ID、公司名称、地区、城市和电话字段的值。其中的一些供应商位于华东或华南地区，另外一些供应商所在的城市是天津。

Select SupplierID,CompanyName,Region,City,Phone from [Northwind].[dbo].[Suppliers] where Region in ('东北','华南') or City='天津'

<找不到符合条件的值>

（17）查询“Northwind”示例数据库中位于“华东”或“华南”地区的供应商的ID、公司

名称、地区、城市和电话字段的值。

Select SupplierID,CompanyName,Region,City,Phone from [Northwind].[dbo].[Suppliers] where Region in ('东北','华南') <找不到符合条件的值>

请你练习

1. 在NorthWind数据库中查询所有产品的信息。

SQl语句为：select * from [Northwind].[dbo].[Products]

部分截图：

2. 查询NorthWind中雇员姓名、所在城市及其家庭电话，姓名以格式“姓.名”显示。

查询语句为：

Select LastName,FirstName,City,HomePhone from [Northwind].[dbo].[Employees]

3. 在NorthWind中查询出单价最贵的前10种产品的信息。

查询语句为：

select Top 10 * from [Northwind].[dbo].[Products] order by UnitPrice desc

4. 在NorthWind中查询出所有单价超过50美元的货物名称、货物代号及其单位重量。

查询语句为：

select ProductName,ProductID,QuantityPerUnit

from [Northwind].[dbo].[Products] where UnitPrice > 50

5.在NorthWind中查询出职务为销售代表，称呼为小姐的所有职员姓名和生日。 Select LastName,FirstName, BirthDate from [Northwind].[dbo].[Employees]

where Title='Sales Representative' and TitleOfCourtesy='Ms.'

分析与讨论：

1、SQL 使用单引号来环绕文本值（大部分数据库系统也接受双引号）。如果是数值，请不要使用引号。

实验九 稍复杂的查询（多表查询、子查询）

实验目的

1. 掌握SSMS查询编辑器的使用方法，加深对SQL语言的嵌套查询语句的理解。

2. 熟悉基本的连接查询，掌握内连接与外连接查询。 3. 掌握相关子查询的使用方法。

4. 学会嵌套子查询的使用方法、集合查询。

实验要求

1. 掌握在实际的数据关系中设计有意义的查询。 2. 掌握较复杂的数据查询方法。

实验内容

数据查询联系。 实验步骤

（1）查询唐强的工作所在部门名称和部门所在地。 select DNAME,LOCATION from [T].[dbo].[DEPT] where DEPTNO = ( select DEPTNO from [Test].[dbo].[EMP]

where ENAME='唐强')

（2）在沈阳工作的雇员信息。 select * from [Test].[dbo].[EMP] where DEPTNO = ( select DEPTNO from [Test].[dbo].[DEPT]

where LOCATION='沈阳')

3）找出在沈阳工作的雇员信息及其所在部门信息。

select * from [Test].[dbo].[EMP],[Test].[dbo].[DEPT]

where DEPT.LOCATION='沈阳' and DEPT.DEPTNO=EMP.DEPTNO

（4）找出工资超过经理的职工，列出姓名、工资及其经理的姓名、工资。 select first.ENAME 职工,second.ENAME 经理

from [Test].[dbo].[EMP] first,[Test].[dbo].[EMP] second where first.JOB not like'%经理'

and second.JOB in ('总经理','部门经理') and first.SAL>second.SAL

（5）下面使用Northwind数据库进行练习。查询Boise和Albuquerque的客户在1996年订购的所有订单的订单ID、所订购的产品名称和数量

use Northwind

select orders.orderid,productname,quantity,city from [order details] join products

on [order details].productid=products.productid join orders

on [order details].orderid=orders.orderid join customers

on orders.customerid=customers.customerid where city in('Boise' ,'Albuquerque') and

OrderDate between '1996-1-1' and '1996-12-31'

（6）查询客户的每份订单的订单ID、产品名称和销售金额 use Northwind

select orders.orderid,productname,[order details].unitprice*quantity as 销售金额 from [order details] join products

on [order details].productid=products.productid join orders

on [order details].orderid=orders.orderid join customers

on orders.customerid=customers.customerid

（7）按运货商公司名称，统计1997年由各个运货商承运的订单的总数量 use Northwind

select companyname,count(*) from shippers join orders

on shippers.shipperid=orders.shipvia where year(orderdate)=1997 group by companyname

（8）找出1997年订单id为10400的订单详情。 select * from [order details] join orders on [order details].orderid=orders.orderid

where orders.orderid=10400 and year(orderdate)=1997

（9）统计各类产品的平均价格 use Northwind

select categories.categoryname,avg(unitprice) from products join categories

on products.categoryid=categories.categoryid group by categories.categoryname

请你练习

以下查询均基于Northwind数据库。

1. 查询所有运货商的公司名称和电话。 查询语句：

use Northwind

select companyname,phone from shippers

2. 查询所有客户的公司名称、电话、传真、地址、联系人姓名和联系人头衔。

查询语句：

use Northwind

select companyname,fax,phone,address,contactname,contacttitle from customers

……..

3. 查询单价介于10至30元的所有产品的产品ID、产品名称和库存量。

查询语句：

select productid,productname,unitsinstock from products

where unitprice between 10 and 30

……..

4. 查询单价大于20元的所有产品的产品名称、单价以及供应商的公司名称、电话。

查询语句：

select productname,unitprice,suppliers.companyname,suppliers.phone from suppliers join products

on suppliers.supplierid=products.supplierid where unitprice>20

5. 统计1997年上半年的每份订单上所订购的产品的总数量。

查询语句：

select orders.orderid,sum(quantity) from [order details] join orders

on [order details].orderid=orders.orderid

where year(orderdate)=1997 and month(orderdate)>=1 and month(orderdate)<=6 group by orders.orderid

………

6. 统计各地区客户的总数量。

查询语句：

select count(*) from customers

where region is not null group by region

分析与讨论:

1、Count(*)返回组中的项数。COUNT 与 COUNT_BIG 函数类似。两个函数唯一的差别是它们的返回值。COUNT 始终返回 int 数据类型值。COUNT_BIG 始终返回 bigint 数据类型值。

COUNT(ALL expression) 对组中的每一行都计算 expression 并返回非空值的数量。 COUNT(DISTINCT expression) 对组中的每一行都计算 expression 并返回唯一非空值的数量。

2、Jion相当于将多个表合并得到一个虚拟的表，要注意找到多个表的联系。而对于嵌套的子查询可以将内层查询作为一个结果，从而产生一个约束条件。

实验十 视图的管理

实验目的

1. 熟练掌握使用T-SQL语句创建视图。

2. 熟练使用SSMS管理工具创建视图、修改视图。 3. 熟练掌握使用T-SQL语句删除视图、重命名视图。

实验要求

1. 掌握在SQL Server中视图的创建和删除操作。

实验内容

视图的创建与删除。 实验步骤

（1）首先来看如何使用SSMS创建视图。启动SSMS。

（2）在SSMS管理平台中，展开指定的服务器，打开要创建的视图的数据库，选择视图文件夹，右击该视图文件夹，从弹出的快捷菜单中选择“新建视图”选项。接着就出现添加表、视图、函数对话框。

（3）选择好创建视图所需的表、视图、函数后，通过单击字段左边的复选框选择需要的字段。单击工具栏中的“保存”按钮，或者单击鼠标右键，从快捷菜单中选择保存选项保存视图，输入视图名，即可完成视图的创建。

（4）现在使用T-SQL语句创建视图。查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、客户ID和雇员ID等字段的值。 （5）执行代码。然后再视图节点下，找到创建好的视图。

---Create View Orderquery（该视图已经存在，无需创建，直接查询即可） --as

Select OrderDate,OrderID,CustomerID,EmployeeID from [Northwind].[dbo].[Orders]

where OrderDate Between '1996-07-01' and '1996-07-15'

（6）查询“10248”和“10254”号订单的订单ID、运货商的公司名称、订单上所订购的产品的名称，首先建立视图。

Create view orderquery3 as

Select Orders.OrderID,Shippers.CompanyName,ProductName From Orders join Shippers

on Shippers.ShipperID=Orders.ShipVia join [Order Details]

on [Order Details].OrderID=Orders.OrderID join Products

on Products.ProductID=[Order Details].ProductID （7）执行查询，查看结果。 Select * from orderquery3

where OrderID =10248 or OrderID=10254

（8）查询“10248”和“10254”号订单的订单ID、订单上所订购的产品的名称、数量、单价和折扣

Create view orderquery4 as

Select Orders.OrderID,ProductName,quantity,Products.unitprice From [Order Details] join Orders

on [Order Details].orderid=Orders.orderid join Products

on Products.ProductID=[Order Details].ProductID

（9）执行查询，查看结果。 Select * from orderquery4

where OrderID =10248 or OrderID=10254

（10）修改视图。在SSMS平台中，展开指定的数据库和视图，右击要修改的视图“orderquery4”，从弹出的快捷菜单中选择“设计”选项。

（11）在弹出的对话框中，选择好修改视图所需要的表、视图后，通过单击字段左边的复选框选择需要修改的字段。然后完成保存后退出即完成修改。

请你练习

1.使用视图完成下列查询。查询“10248”和“10254”号订单的订单ID、订单上所订购的产品的名称及其销售金额。（必须使用join）

Create view orderquery6 as

Select Orders.OrderID,Products.ProductName,Products.UnitPrice From Orders join [Order Details]

on Orders.OrderID=[Order Details].OrderID join Products

on [Order Details].ProductID=[Order Details].ProductID

Select * from orderquery6

where OrderID =10248 or OrderID=10254

2. 查询重命名视图的T-SQL语句格式，重命名视图。 格式：sp_rename [ @objname = ] 'object_name' ,

[ @newname = ] 'new_name' [ , [ @objtype = ] 'object_type' ] 重命名：

Exec sp_rename 'orderquery',' orderqueryA'

3. 查询删除视图的T-SQL语句格式，删除视图。

格式：DROP VIEW [ schema_name . ] view_name [ ...,n ] [ ; ] 删除：drop view orderquery6

分析与讨论：

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。视图在数据库中并不是以数据值存储集形式存在，除非是索引视图。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其他数据库的一个或多个表，或者其他视图。分布式查询也可用于定义使用多个异类源数据的视图。。