Table of Contents
实验1 SQL Server概述
1.1. 安装SQL Server和SQL Server Management Studio
1.1.1. 实验目的与要求
(1) 掌握SQL Server数据库和开发工具的安装配置过程。
(2) 掌握SQL Server数据库的运行环境以及相应实用工具的使用方法。
1.1.2. 实验内容
(1) 安装SQL Server数据库。
(2) 安装集成开发工具Management Studio。
(3) 启动SQL Server数据库，在集成开发工具Management Studio中查看数据库、用户、系统表等信息。
1.1.3. SQL语句
select * from sys.databases
use [Test1_23_10_8]
select * from [学生信息表]1.1.4. 运行结果
1.2. SQL Server简单编程
1.2.1. 实验目的与要求
(1) 掌握SQL Server数据库提供的数据类型和函数。
(2) 熟练掌握变量和流控制语句的使用。
(3) 能够编写比较复杂的程序，为后继学习触发器和存储过程奠定扎实的基础。
1.2.2. 实验内容
编程实现：
[例1.12] 输入三个整型数据，按升序排序输出。
[例1.13] 计算1+2+...+100的值。
[例1.14] 显示100~200之间的素数。
1.2.3. SQL语句
[例1.12]
declare @a int,@b int,@c int
select @a=100,@b=110,@c=10
if @a > @b
if @a > @c
if @b > @c
select @c,@b,@a
else
select @b,@c,@a
else
select @b,@a,@c
else
if @b > @c
if @a > @c
select @c,@a,@b
else
select @a,@c,@b
else
select @a,@b,@c[例1.13]
declare @i int,@sum int
select @i = 1,@sum = 0
while @i<=100
begin
select @sum = @sum + @i,@i = @i + 1
end
select @sum[例1.14]
select @x = 100
while @x<=200
begin
set @i = 2
while @i<=sqrt(@x)
begin
if @x%@i = 0
break
set @i = @i +1
end
if @i>sqrt(@x)
select @x
set @x = @x +1
end1.2.4. 运行结果
[例1.12]
[例1.13]
[例1.14]
1.3. 初识数据库
1.3.1. 实验目的与要求
(1) 观察和分析数据库[1]和表的创建过程。
(2) 理解和掌握数据库的模式导航图。
(3) 理解数据库的完整性约束。
(4) 查看某些重要的系统表以及内容的变化。
1.3.2. 实验内容
创建商品订单管理数据库OrderDB，其数据库模式导航图如图1-33所示，表结构如图1-34～图1-39所示，相关数据如图1-40～图1-45所示（详见实验1 SQL Server概述-169.pptx）。导入脚本文件OrderDB.sql，完成如下的操作：
(1) 创建订单管理数据库OrderDB。
(2) 为订单数据库中的表建立主键和外键约束。
(3) 为表插入数据。
(4) 观察脚本运行的结果，如果出错请分析出错原因并修改脚本文件。
1.3.3. SQL语句
脚本代码
set nocount on
set dateformat ymd
use master
go
if not exists(select * from syslogins where name='user01')
exec sp_addlogin user01,888888
go
/*create database*/
if exists(select *from sysdatabases where name='OrderDB')
drop database OrderDB
go
create database OrderDB
on primary
(name='OrderDB_data',
filename='C:\Users\2357555891\Desktop\新建文件夹\数据库系统与原理设计\实验1\OrderDB_data.mdf',
size=5,
maxsize=10,
filegrowth=1)
log on
(name='Order_Log',
filename='C:\Users\2357555891\Desktop\新建文件夹\数据库系统与原理设计\实验1\Order_Log.ldf',
size=2,
maxsize=5,
filegrowth=1)
go
/*data mydatabase*/
use OrderDB
go
/* add my user*/
exec sp_adduser user01,user01
go
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
/*员工人事表*/
print'create table Employee'
go
CREATE TABLE Employee(
employeeNo char(8) not null /*员工编号*/
check(employeeNo like '[E][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
employeeName varchar(10) not null, /*员工姓名*/
sex char(1) not null, /*员工性别*/
birthday datetime null, /*员工生日*/
address varchar(50) null, /*家庭住址*/
telephone varchar(20) null, /*员工电话*/
hireDate datetime not null, /*雇佣日期*/
department varchar(30) not null, /*所属部门*/
headShip varchar(10) not null, /*职务*/
salary numeric(8,2) not null, /*薪水*/
constraint EmployeePK primary key (employeeNo)
)
go
go
/*人事表（employee）数据：*/
insert Employee values('E2015001','喻自强','M','19850415','南京市青海路18号', '13817605008', '20150206','财务科','科长',5800.80)
insert Employee values('E2015002','张小梅','F','19871101','上海市北京路8号', '13607405016', '20150328','业务科','职员',2400)
insert Employee values('E2015003','张小娟','F','19870306','上海市南京路66号', '13707305025', '20150328','业务科','职员',2600)
insert Employee values('E2015004','张露', 'F','19880105','南昌市八一大道130号', '15907205134', '20150328','业务科','科长',5100)
insert Employee values('E2015005','张小东','M','19870903','南昌市阳明路99号', '15607105243', '20150328','业务科','职员',1800)
insert Employee values('E2016001','陈辉', 'M','19881101','南昌市青山路100号', '13607705352', '20160328','办公室','主任',4500)
insert Employee values('E2016002','韩梅', 'F','19911211','上海市浦东大道6号', '13807805461', '20161128','业务科','职员',2600)
insert Employee values('E2016003','刘风', 'F','19920521','江西财经大学5栋1-101室', '15907805578', '20160228','业务科','职员',2500)
insert Employee values('E2017001','陈诗杰','M','19930106','江西财经大学12栋3-304室', null, '20170206','财务科','出纳',2200)
insert Employee values('E2017002','张良', 'M','19930216','上海市福州路135号', null, '20170206','业务科','职员',2700)
insert Employee values('E2017003','黄梅莹','F','19940515','上海市九江路88号', null, '20170220','业务科','职员',3100)
insert Employee values('E2017004','李虹冰','F','19931013','南昌市中山路1号', null, '20170220','业务科','职员',3400)
insert Employee values('E2017005','张小梅','F','19941106','深圳市阳关大道10号', null, '20170221','财务科','会计',5000)
go
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
/*客户表*/
print 'create Customer'
go
create table Customer(
customerNo char(9) not null primary key,/*客户号*/
check(customerNo like '[C][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
customerName varchar(40) not null, /*客户名称*/
telephone varchar(20) not null, /*客户电话*/
address char(40) not null, /*客户住址*/
zip char(6) null /*邮政编码*/
)
go
/*客户表（customer）数据：*/
insert Customer values('C20150001','统一股份有限公司', '022-3566021', '天津市', '220012')
insert Customer values('C20150002','兴隆股份有限公司', '022-3562452', '天津市', '220301')
insert Customer values('C20150003','上海生物研究室', '010-2121000', '北京市', '108001')
insert Customer values('C20150004','五一商厦', '021-4532187', '上海市', '210100')
insert Customer values('C20160001','大地商城', '010-1165152', '北京市', '100803')
insert Customer values('C20160002','联合股份有限公司', '021-4568451', '上海市', '210100')
insert Customer values('C20170001','南昌市电脑研制中心','1491-4412152', '南昌市', '330046')
insert Customer values('C20170002','世界技术开发公司', '021-4564512', '上海市', '210230')
insert Customer values('C20170003','万事达股份有限公司','022-4533141', '天津市', '220400')
insert Customer values('C20160003','红度股份有限公司', '010-5421585', '北京市', '100800')
go
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
/*产品类别表*/
print 'create ProductClass'
go
create table ProductClass(
classNo char(3) not null primary key, /*类别编号*/
className varchar(40) not null /*类别名称*/
)
go
go
/*产品类别表（ProductClass）数据：*/
insert ProductClass values('001','手机')
insert ProductClass values('002','电视机')
insert ProductClass values('003','CPU处理器')
insert ProductClass values('004', '耳机')
insert ProductClass values('005', '手环')
go
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
/*产品名称表*/
print 'create Product'
go
create table Product(
productNo char(9) not null primary key, /*商品编号*/
productName varchar(40) not null, /*商品名称*/
classNo char(3) not null, /*商品类别*/
productPrice numeric(7,2) not null, /*商品定价*/
productStock numeric(7,2) not null, /*商品实际库存*/
productMinstock numeric(7,2) not null, /*商品最低库存*/
constraint ProductFK1 foreign key(classNo) references ProductClass(classNo)
)
go
go
/*产品名称表（product）数据：*/
insert Product values('P20150001','vivo X9 ','001',2798.00,100,10)
insert Product values('P20150002','中兴AXON天机7(A2017)', '001',3099.00,100,10)
insert Product values('P20150003','三星 Galaxy A9', '001',2599.00,50,5)
insert Product values('P20150004','海信55英寸4K智能电视', '002',3999.00,10,6)
insert Product values('P20150005','TCL D55A630U', '002',3399.00,15,5)
insert Product values('P20160001','飞利浦65英寸64位九核','002', 5899.00,35,5)
insert Product values('P20160002','酷睿四核i5-6500', '003',1469.00,500,50)
insert Product values('P20160003','酷睿四核I7-7700k', '003',2799.00,500,50)
insert Product values('P20170001','酷睿四核i7-6700k', '003',819.00,28,5)
insert Product values('P20170002','Beats Solo2 MKLD2PA/A', '004',1499.00,300,60)
insert Product values('P20170003','魅族EP51', '004',269.00,400,50)
insert Product values('P20170004','Beats Solo3 MNEN2PA/A','004',2288.00,150,30)
insert Product values('P20160004','华为手环B3', '005',949.00,180,30)
insert Product values('P20160005','魅族H1智能手环','005',1499.00,210,30)
insert Product values('P20160006','AMAZFIT智能手环', '005',399.00,10,8)
go
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
/*订单主表*/
print 'create OrderMaster'
go
create table OrderMaster(
orderNo char(12) not null primary key,/*订单编号*/
customerNo char(9) not null, /*客户号*/
salerNo char(8) not null, /*业务员编号*/
orderDate datetime not null, /*订货日期*/
orderSum numeric(9,2) not null, /*订单金额*/
invoiceNo char(10) not null UNIQUE, /*发票号码*/
constraint OrderMasterFK1 foreign key(customerNo) references Customer(customerNo),
constraint OrderMasterFK2 foreign key(salerNo) references Employee(employeeNo)
)
go
go
/*订单主表（sales）数据：*/
insert OrderMaster values('201501090001','C20150001','E2015002','20150109',0.00,'I000000001')
insert OrderMaster values('201501090002','C20150004','E2015003','20150109',0.00,'I000000002')
insert OrderMaster values('201501090003','C20150003','E2015002','20150109',0.00,'I000000003')
insert OrderMaster values('201502190001','C20150001','E2015003','20150219',0.00,'I000000004')
insert OrderMaster values('201502190002','C20150002','E2015002','20150219',0.00,'I000000005')
insert OrderMaster values('201503010001','C20150002','E2015004','20150301',0.00,'I000000006')
insert OrderMaster values('201503020001','C20150004','E2015003','20150302',0.00,'I000000007')
insert OrderMaster values('201603090001','C20160003','E2016002','20160309',0.00,'I000000008')
insert OrderMaster values('201605090001','C20160002','E2016002','20160509',0.00,'I000000009')
insert OrderMaster values('201606120001','C20160001','E2016003','20160612',0.00,'I000000010')
insert OrderMaster values('201610010001','C20160001','E2016003','20161001',0.00,'I000000011')
insert OrderMaster values('201701140001','C20170001','E2017003','20170114',0.00,'I000000012')
insert OrderMaster values('201702070001','C20170003','E2017002','20170207',0.00,'I000000013')
go
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
/*订单明细表*/
print 'create OrderDetail'
go
create table OrderDetail(
orderNo char(12) not null, /*订单编号*/
productNo char(9) not null, /*产品编号*/
quantity int not null, /*销售数量*/
price numeric(7,2) not null, /*成交单价*/
constraint OrderDetailPK primary key clustered(orderNo,productNo),
constraint OrderDetailFK1 foreign key(orderNo) references OrderMaster(orderNo),
constraint OrderDetailFK2 foreign key(productNo) references Product(productNo)
)
go
go
/*订单明细表（OrderDetail）数据：*/
insert OrderDetail values('201501090001','P20150001',1 , 2798.00)
insert OrderDetail values('201501090001','P20150002',3 , 3099.00)
insert OrderDetail values('201501090001','P20150003',2 , 2599.00)
insert OrderDetail values('201501090002','P20150003',5 , 2599.00)
insert OrderDetail values('201501090002','P20150001',2 , 2798.00)
insert OrderDetail values('201501090002','P20150005',2 , 3399.00)
insert OrderDetail values('201501090003','P20150004',2 , 3999.00)
insert OrderDetail values('201501090003','P20150001',2 , 2798.00)
insert OrderDetail values('201501090003','P20150002',5 , 3099.00)
insert OrderDetail values('201502190001','P20150001',3 , 2798.60)
insert OrderDetail values('201502190001','P20150003',5 , 2599.00)
insert OrderDetail values('201502190002','P20150003',2 , 2599.00)
insert OrderDetail values('201502190002','P20150005',3 , 3399.00)
insert OrderDetail values('201503010001','P20150001',4 , 2798.00)
insert OrderDetail values('201503010001','P20150005',1 , 3399.00)
insert OrderDetail values('201503020001','P20150001',2 , 2798.00)
insert OrderDetail values('201503020001','P20150003',3 , 2599.00)
insert OrderDetail values('201503020001','P20150002',1 , 3099.00)
insert OrderDetail values('201603090001','P20150003',2 , 2599.00)
insert OrderDetail values('201603090001','P20160001',5 , 5899.00)
insert OrderDetail values('201603090001','P20160002',2 , 1499.00)
insert OrderDetail values('201603090001','P20160003',3 , 2799.00)
insert OrderDetail values('201605090001','P20150002',1 ,3099.00)
insert OrderDetail values('201605090001','P20160006',3 , 399.00)
insert OrderDetail values('201605090001','P20160004',2 , 949.00)
insert OrderDetail values('201605090001','P20160005',3 , 1499.60)
insert OrderDetail values('201606120001','P20160002',2 , 1499.00)
insert OrderDetail values('201606120001','P20160004',1 , 949.00)
insert OrderDetail values('201606120001','P20160005',2 , 1499.60)
insert OrderDetail values('201606120001','P20160006',2 , 399.00)
insert OrderDetail values('201610010001','P20150003',2 , 2599.00)
insert OrderDetail values('201610010001','P20160002',6 , 1499.00)
insert OrderDetail values('201610010001','P20160006',2 , 399.00)
insert OrderDetail values('201701140001','P20170002',5 , 1499.00)
insert OrderDetail values('201701140001','P20170003',4 , 269.00)
insert OrderDetail values('201702070001','P20170003',5 , 269.00)
insert OrderDetail values('201702070001','P20170004',6 , 2299.00)
insert OrderDetail values('201702070001','P20170001',2 , 819.00)
go
/*ORDERMASTER中ORDERSUM值的更新*/
update OrderMaster set orderSum=sum2
from OrderMaster a,(select orderNo,sum(quantity*price) sum2
from OrderDetail
group by orderNo) b
where a.orderNo=b.orderNo
查表代码
use [OrderDB]
select * from [Employee]1.3.4. 运行结果
脚本运行结果
查表测试
数据库关系图
1.4. 总结
本次实验让我成功安装了SQL server相关组件和编译环境，并了解SQL server的简单使用，为以后更好地学习SQL打下了坚实基础。
参考文献
[1] 数据库系统原理与设计（第三版） 万常选、廖国琼、吴京慧、刘喜平编著
Comments NOTHING