数据库应用示例代码

1.创建数据库

用CREATE DA TABASE语句创建一个名为Ex_db的数据库，其中包括五个数据文件，三个文件组，存储在e:\sqldb\data\目录中：

主文件组包括主数据文件Ex_db_data1，文件大小为50MB，按10%增长，最大大小不受限制，一个辅助数据文件Ex_db_data2，文件大小为10MB，按1MB增长，最大大小为50MB。

次要文件组名为Ex_dbgroup1，包括二个辅助数据文件Ex_db_data3和Ex_db_data4，文件大小均为50MB，按2MB增长，最大大小不受限制；

第三个文件组名为Ex_dbgroup2，包括一个辅助数据文件Ex_db_data5，文件大小为2MB，按15%增长，最大大小100MB；

有一个日志文件，文件大小为2MB，按1MB增长，最大大小35MB；

步骤一:在硬盘上建立目录e:\sqldb\data

步骤二:打开查询分析器,输入下列语句

USE master

GO

CREATE DATABASE Ex_db

ON

PRIMARY

/*主文件组包括主数据文件Ex_db_data1，文件大小为50MB，按10%增长，最大大小不受限制，一个辅助数据文件Ex_db_data2，文件大小为10MB，按1MB增长，最大大小为50MB。*/

( NAME =Ex_db_data1,

FILENAME ="e:\sqldb\data\Ex_db_data1.mdf",

SIZE = 50MB,

MAXSIZE =UNLIMITED,

FILEGROWTH =10%),

( NAME ="Ex_db_data2",

FILENAME ="e:\sqldb\data\Ex_db_data2.ndf",

SIZE = 10MB,

MAXSIZE = 50MB,

FILEGROWTH =1MB),

FILEGROUP Ex_dbgroup1

--包括二个辅助数据文件Ex_db_data3和Ex_db_data4，文件大小均为50MB，按2MB增长，最大大小不受限制；

( NAME = Ex_db_data3,

FILENAME = "e:\sqldb\data\Ex_db_data3.ndf",

SIZE = 50MB,

MAXSIZE = UNLIMITED,

FILEGROWTH =5MB),

( NAME = Ex_db_data4,

FILENAME ="e:\sqldb\data\Ex_db_data4.ndf",

SIZE = 50MB,

MAXSIZE = UNLIMITED,

FILEGROWTH =5MB),

FILEGROUP Ex_dbgroup2

/*第三个文件组名为Ex_dbgroup2，包括一个辅助数据文件Ex_db_data5，文件大小为2MB，按15%增长，最大大小100MB*/

( NAME =Ex_db_data5,

FILENAME ="e:\sqldb\data\Ex_db_data5.ndf",

SIZE = 2MB,

MAXSIZE =100MB,

FILEGROWTH =15%)

LOG ON

/*一个日志文件，文件大小为2MB，按1MB增长，最大大小35MB*/

( NAME =Ex_db_log,

FILENAME ="e:\sqldb\data\Ex_db_log.ldf",

SIZE = 2MB,

MAXSIZE = 35MB,

FILEGROWTH = 1MB);

GO

步骤三:刷新查询分析器中的对象浏览器,即可看到新建的数据库.

工具-->对象浏览器-->刷新

步骤四:查看分组信息和文件信息

use Ex_db

go

select * from dbo.sysfilegroups

select * from dbo.sysfiles

go

附: Master数据库中的服务器级系统表及其功能:

系统表功能

?Sysaltfiles 记录文件的状态和变化信息

?Sysdevices 记录磁盘、磁带备份文件的相关信息

?Sysoledbusers 记录连接服务器的用户名、密码等信息

?Syscacheobjects 记录高速缓存的使用情况

?Syslanguages 记录服务器所能识别的语言

?Sysperfinfo 记录有关统计服务器性能的计数器的信息

?Syscharsets 记录字符集和排列顺序的相关信息

?Syslockinfo 记录各种数据封锁的信息

?Sysprocesses 记录正在进行中的进程信息

?Sysconfigures 记录服务器的配置信息

?Syslogins 记录所有的本地账户信息

?Sysremotelogins 记录所有的远程用户信息

?Syscurconfigs 记录服务器当前的配置信息

?Sysmessages 记录所有的系统错误和警告信息

?Sysdatabases 记录所有SQL Server数据库的相关信息

?Sysservers 记录所有可访问的SQL Server服务器信息

每个数据库中的系统表及其功能

系统表功能

?syscolumns数据字典

?syscomments视图、规则、默认值、触发器、约束、和存储过程的项?sysconstraints数据库约束信息

?sysdepends对象（表、视图和过程）之间的相关性信息

?sysfilegroups数据库的文件组信息

?sysfiles数据库中的文件信息

?sysfulltextcatalogs全文目录集

?sysforeignkeys外键约束的信息

?sysindexes数据库中的索引表信息

?sysindexkeys索引中的键或列的信息

?sysmembers数据库角色成员信息

?sysobjects数据库的对象（约束、默认值、日志、规则、存储过程等）信息?syspermissions有关对数据库内的用户、组和角色授予和拒绝的权限的信息?sysprotects安全帐户的权限的信息

?sysreferences参照关系信息

?systypes数据类型和用户定义数据类型信息

?sysusers数据库中用户、组、角色信息

2.为数据库增加文件组

用ALTER DA TABASE语句为Ex_db数据库增加一个名为Ex_dbgroup3的文件组，并为其添加二个数据文件Ex_db_data6和Ex_db_data7，文件大小均为10MB，按2MB增长，最大大小不受限制；

ALTER DATABASE EX_DB

ADD FILEGROUP Ex_dbgroup3

GO

ALTER DATABASE EX_DB

ADD FILE

( NAME =Ex_db_data6,

FILENAME ="e:\sqldb\data\Ex_db_data6.ndf",

SIZE = 10MB,

MAXSIZE =unlimited,

FILEGROWTH =2MB),

( NAME =Ex_db_data7,

FILENAME ="e:\sqldb\data\Ex_db_data7.ndf",

SIZE = 10MB,

MAXSIZE =unlimited,

FILEGROWTH =2MB)

TO FILEGROUP Ex_dbgroup3

GO

3.创建数据库表,定义完整性约束.

在数据库Ex_db中建立订货数据库表,其关系模型如下:

仓库(仓库号,城市,面积)

职工(仓库号,职工号,工资)

供应商(供应商号,供应商名,地址,电话)

订购单(职工号,供应商号,订单号,订购日期,)

约束要求如下:

仓库面积不小于500M2 ,每座城市只能设一个仓库;

职工最低工资为800元,最高5000元,缺省值为1200元.

订购单中的职工和供应商信息不能为空.

该关系模型的关系示意图如下：

USE Ex_db

/*（1）建立仓库表*/

CREATE TABLE 仓库(

仓库号CHAR(5) PRIMARY KEY,

城市CHAR(10) constraint un_city unique,

面积INT CHECK (面积> 100) )

/*（2）建立职工表*/

CREATE TABLE 职工(

仓库号CHAR(5) FOREIGN KEY REFERENCES 仓库,

职工号CHAR(5) PRIMARY KEY,

工资INT CHECK (工资>= 800 AND 工资<= 5000)

DEFAULT 1200 )

/*（3）建立供应商表*/

CREATE TABLE 供应商(

供应商号CHAR(5) PRIMARY KEY,

供应商名CHAR(20),

地址CHAR(20),

电话INT)

/*（4）建立订购单表*/

CREATE TABLE 订购单(

职工号CHAR(5) NOT NULL FOREIGN KEY REFERENCES 职工,

供应商号CHAR(5) NOT NULL FOREIGN KEY REFERENCES 供应商,

订购单号CHAR(5) PRIMARY KEY,

订购日期DATETIME DEFAULT getdate())

GO

4.修改基本表

ALTER TABLE <表名>

[ADD <新列名> <数据类型>[<完整性约束>][,…n]]

[DROP[[[CONSTRAINT]<约束名>]|[COLUMN<列名>]][,…n]]

[MODIFY <列名> <数据类型>[,…n]]；

修改订购单表,增加订购商品的品名、规格、数量、单价，金额等项，修改后的订购单关系如下

订购单(职工号,供应商号,订单号,订购日期, 品名,规格,数量,单价,金额)

约束要求:数量、单价>0，金额为计算列，由数量*单价得到。

1)问题：

a)如此修改后是否破坏了该关系模型的3NF属性？为什么？（提示：结合E-R图思考）

b)如要保持关系模型的3NF属性，应如何修改关系表？

2)参考解决方案：

a)增加新实体：商品表

b)在订购单中增加订购商品联系信息

c)建立订购信息视图(职工号,供应商号,订单号,订购日期, 品名,规格,数量,单价,金额)

USE Ex_db

go

CREATE TABLE 商品(

商品号CHAR(5) PRIMARY KEY,

商品名CHAR(20),

规格CHAR(20))

alter table 订购单

add 商品号CHAR(5) FOREIGN KEY REFERENCES 商品,

数量int CHECK(数量> 0),

单价decimal CHECK(单价> 0)

go

修改后的关系模型如下:

仓库(仓库号,城市,面积)

职工(仓库号,职工号,工资)

供应商(供应商号,供应商名,地址,电话)

订购单(职工号,供应商号,订单号,订购日期,商品号,数量,单价)

商品(商品号,商品名,规格)

USE Ex_db

go

alter TABLE 商品

add 商品名CHAR(20),规格CHAR(20)

alter TABLE 商品

drop COLUMN 供商品名

go

5.建立关系图

建立仓库、职工、供应商、订购单等关系的关系图。

打开企业管理器，选择操作->新建数据库关系图，按向导引导操作。调整图形布局，完成后存盘。

6.建立索引

/*对订购单表按职工号和供应商号降序、订购单号升序创建惟一性索引；*/

create unique index idx_xh_kch

on 订购单(职工号desc,供应商号desc,订购单号asc);

CREATE UNIQUE CLUSTERED INDEX number_ind

ON订购单(职工号,供应商号,订单号,订购日期,商品号,数量,单价)

with

pad_index,

fillfactor=20,

ignore_dup_key,

drop_existing,

statistics_norecompute

7.建立视图

建立订购信息视图(职工号,供应商号,订单号,订购日期, 品名,规格,数量,单价,金额)

USE Ex_db

go

create view view_订购信息(职工号,供应商号,订单号,订购日期,商品名,规格,数量,单价,金额) as select 订购单.职工号,订购单.供应商号,订购单.订购单号,订购单.订购日期, 商品.商品名,商品.规格,商品.数量,商品.单价,商品.数量*商品.单价

from 订购单,商品

where 订购单.商品号=商品.商品号

8.增加数据

向下表中输入数据:

1)仓库(仓库号,城市,面积)

2)职工(仓库号,职工号,工资)

3)供应商(供应商号,供应商名,地址,电话)

4)订购单(职工号,供应商号,订单号,订购日期,商品号,数量,单价)

5)商品(商品号,商品名,规格)

/*向仓库表中插入右表数据*/

insert into 仓库 values('WH1','北京',370)

insert into 仓库 values('WH2','上海',500)

insert into 仓库 values('WH3','广州',200)

insert into 仓库 values('WH4','武汉',400)

select * from 仓库

/*向职工表中插入右表数据*/

insert into 职工 values('WH2','E1',1220)

insert into 职工 values('WH1','E3',1210)

insert into 职工 values('WH2','E4',1250)

insert into 职工 values('WH3','E6',1230)

insert into 职工 values('WH1','E7',1250)

select * from 职工

/*向供应商表中插入右表数据*/

insert into 供应商 values('S3','振华电子厂', '西安',13041)

insert into 供应商 values('S4','华通电子公司', '北京',13652)

insert into 供应商 values('S6','607厂', '郑州',13653)

insert into 供应商 values('S7','爱华电子厂', '北京',13654)

select * from 供应商

/*向商品表中插入右表数据*/

insert into 商品 values('TV','电视机', '48')

insert into 商品 values('PHONE','电话机', 'T8')

select * from 商品

/*向订购单表中插入右表数据*/

insert into 订购单 values('E3','S7', 'OR67', '2003/06/23', 'TV',10,5000) insert into 订购单 values('E1','S4', 'OR73', '2003/06/23','TV',5,5000)

insert into 订购单 values('E7','S4', 'OR76','2003/06/23', 'PHONE',5,5000) insert into 订购单 values('E6','S7', 'OR77', '2003/06/23','TV',5,5000)

insert into 订购单 values('E3','S4', 'OR79', '2003/06/23','PHONE',5,5000) insert into 订购单 values('E1', 'S7', 'OR80', '2003/06/23','PHONE',5,5000) insert into 订购单 values('E3', 'S7', 'OR90', '2003/06/23','TV',5,5000) insert into 订购单 values('E3','S3', 'OR91', '2003/06/23','TV',5,5000)

select * from 订购单

订购单(职工号,供应商号,订单号,订购日期,商品号,数量,单价)

6)例4-4：在Insert语句中使用exec子句

CREATE TABLE 用户与进程信息(

编号int PRIMARY KEY IDENTITY,

进程ID smallint,

定线程上下文ID smallint,

进程状态nchar(30),

登录名nchar(128),

主机名nchar(128),

阻塞进程的系统进程ID nchar(5),

数据库名nchar(128),

运行命令nchar(16),

请求ID int,

查询时间smalldatetime DEFAULT getdate()

)

GO

INSERT用户与进程信息(进程ID,定线程上下文ID,进程状态,登录名,主机名, 阻塞进程的系统进程ID,数据库名,运行命令,请求ID)

EXEC sp_who

SELECT * FROM 用户与进程信息

9.修改数据

10.查询练习

1)检索出工资在1220元到1240元范围内的职工信息

SELECT *

FROM 职工

WHERE 工资BETWEEN 1220 AND 1240

2)从供应商关系中检索出全部公司制的供应商信息

SELECT *

FROM 供应商

WHERE 供应商名LIKE '%公司'

3)找出尚未确定供应商的订购单

SELECT *

FROM 订购单

WHERE 供应商号IS NULL

4)先按仓库号排序，再按工资排序输出全部职工信息

SELECT *

FROM 职工

ORDER BY仓库号,工资

5)给出在仓库WH1或WH2工作，并且工资少于1250元的职工号SELECT 职工号

FROM 职工

WHERE工资< 1250 AND (仓库号='WH1' OR 仓库号='WH2')

6)找出不在北京的全部供应商信息

SELECT *

FROM 供应商

WHERE地址!= '北京'

或

SELECT *

FROM 供应商

WHERE NOT (地址= '北京')

7)找出工资多于1230元的职工号和他们所在的城市

SELECT 职工号,城市

FROM 职工,仓库

WHERE (工资> 1230)

AND (职工.仓库号= 仓库.仓库号)

或

SELECT 职工号,城市

FROM 职工JOIN仓库

ON职工.仓库号= 仓库.仓库号

WHERE 工资> 1230

8)查询：根据雇员关系，列出其各级职工上下级关系的清单。

SELECT S.雇员姓名, …领导?,E.雇员姓名

FROM 雇员AS S,雇员E

WHERE S.雇员号= E.经理

9)仓库关系和职工关系的广义笛卡尔积连接运算：

SELECT * FROM 仓库CROSS JOIN职工

10)对仓库关系和职工关系进行传统的等值连接：

SELECT * FROM 仓库CROSS JOIN职工

WHERE 仓库.仓库号=职工.仓库号

11)等值或自然连接操作如下：

SELECT 仓库.仓库号,城市,面积,职工号,工资

FROM 仓库JOIN职工

ON 仓库.仓库号=职工.仓库号

12)左连接操作如下：

SELECT 仓库.仓库号,城市,面积,职工号,工资

FROM 仓库LEFT JOIN职工

ON 仓库.仓库号=职工.仓库号

13)哪些城市至少有一个仓库的职工工资为1250元？

SELECT 城市

FROM 仓库

WHERE 仓库号IN (SELECT仓库号

FROM 职工

WHERE 工资= 1250 )

14)找出和职工E4挣同样工资的所有职工。

SELECT 职工号

FROM 职工

WHERE 工资= (SELECT工资

FROM 职工

WHERE 职工号= “E4” )

15)找出哪些城市的仓库向北京的供应商发出了订购单

SELECT 城市FROM 仓库WHERE 仓库号IN

(SELECT仓库号FROM 职工WHERE 职工号IN

(SELECT职工号FROM 订购单WHERE 供应商号IN

(SELECT供应商号FROM 供应商WHERE 地址='北京')))

16)检索有职工的工资大于或等于WH1仓库中任何一名职工的工资的仓库号SELECT DISTINCT 仓库号FROM 职工

WHERE 工资>= ANY (SELECT 工资FROM 职工

WHERE 仓库号= 'WH1')

等价于

SELECT DISTINCT 仓库号FROM 职工

WHERE 工资>= (SELECT MIN(工资) FROM 职工

WHERE 仓库号= 'WH1')

17)检索有职工的工资大于或等于WH1仓库中所有职工的工资的仓库号SELECT DISTINCT 仓库号FROM 职工

WHERE 工资>= ALL (SELECT 工资FROM 职工

WHERE 仓库号= 'WH1')

等价于

SELECT DISTINCT 仓库号FROM 职工

WHERE 工资>= (SELECT MAX(工资) FROM 职工

WHERE 仓库号= 'WH1')

18)检索那些仓库中还没有职工的仓库的信息。

SELECT * FROM 仓库WHERE NOT EXISTS

(SELECT * FROM 职工

WHERE 仓库号= 仓库.仓库号)

或

SELECT * FROM 仓库WHERE 仓库号NOT IN

(SELECT 仓库号FROM 职工)

19)检索那些仓库中至少已经有一个职工的仓库的信息

SELECT * FROM 仓库WHERE EXISTS

(SELECT * FROM 职工

WHERE 仓库号= 仓库.仓库号)

或

SELECT * FROM 仓库WHERE 仓库号IN

(SELECT 仓库号FROM 职工)

20)企业的供应商来自于几个不同的地方？

SELECT COUNT (DISTINCT地址)

FROM 供应商

21)求支付的工资总数

SELECT SUM(工资) FROM 职工

22)求在“上海”仓库工作的职工的最高工资值

SELECT MAX(工资) FROM 职工

WHERE 仓库号IN

(SELECT 仓库号FROM 仓库

WHERE 城市=…上海?)

23)求每个仓库的职工的平均工资

SELECT 仓库号,AVG(工资)

FROM 职工

GROUP BY仓库号

24)分别求每个至少有两个职工的仓库的平均工资

SELECT 仓库号,COUNT(*),A VG(工资)

FROM 职工

GROUP BY仓库号

HA VING COUNT(*) >= 2

25)列出职工全部记录并计算各仓库的平均工资和工资小计，最后给出全体职工的平均工资

和工资总和

SELECT 仓库号,职工号,工资FROM 职工

ORDER BY仓库号

COMPUTE A VG(工资),SUM(工资) BY仓库号

COMPUTE A VG(工资),SUM(工资)

26)列出职工全部记录并计算全体职工的平均工资和工资总和

SELECT 仓库号,职工号,工资

FROM 职工

COMPUTE AVG(工资),SUM(工资)

27)

28)

11.D