实验一
实验名称:数据定义(4课时)
一、实验目的
1、理解数据库模式的概念,通过使用SQL SERVER企业管理器或者My Sql建立数据库和基本表。模式为人事表、客户表、销售表、销售明细表、产品表。熟悉SQL SERVER企业管理器的使用,并将得到的表生成脚本,然后保存。
2、理解上述基本表之间的关系,建立关系表。
3、掌握修改表结构的基本方法
4、掌握索引和视图的创建方法
二、实验环境
MS SQL SERVER或者My Sql。
三、实验内容与步骤
1、建立一个数据库和五张表的表结构。
--员工人事表
create table employee(
emp_no char(5) primary key,
emp_name char(10) not null,
sex char(1) not null,
dept char(4),
title char(6),
date_hired datetime,
birthday datetime,
salary int,
addr char(50),
Mod_date datetime default(getdate())
)
--客户表
create table customer(
cust_id char(5) primary key,
cust_name char(20) not null,
sex char(1) not null,
addr char(40) not null,
tel_no char(10) not null,
zip char(6)
)
--销售主表
create table sales(
order_no char(5) primary key,
cust_id char(5) not null,
sale_id char(5) not null,
tot_amt numeric(9,2) default 0,
order_date datetime,
ship_date datetime,
invoice_no char(10)
)
--销售明细表
create table sales_item(
order_no char(5),
prod_id char(5),
qty int default 0,
unit_price numeric(9,2) default 0,
order_date datetime
primary key(order_no,prod_id)
)
--产品名称表
create table product(
prod_id char(5) primary key,
prod_name char(20) not null
)
2、建立5张表的关系图
--2.建立5张表的关系图
alter table sales add foreign key(cust_id) references customer
alter table sales add foreign key(sale_id) references employee(emp_no)
alter table sales_item add foreign key(order_no) references sales
alter table sales_item add foreign key(prod_id) references product
3、修改表结构,通过SQL语句修改表约束。
① 在表employee加入CHECK约束:输入的员工编号必须以E开头的5位数编号,性别只能为M/F。
alter table employee add check(emp_no like('E____'))
alter table employee add check(sex in('M','F'))
②为销售主表sales中的发票编号字段建立UNIQUE约束。
alter table sales add unique(invoice_no)
③所有性别属性,限制取值为f或m
说明:用drop constraint 删除①中关于sex的约束,重新建立约束,并把表中已存有的数据改为符合要求的数据格式。
alter table employee drop constraint CK__employee__sex__30F848ED
alter table employee add check(sex in('m','f'))
update employee set sex='m' where sex='M'
update employee set sex='f' where sex='F'
4、员工表建立唯一索引:emp_no属性、升序;员工表建立聚集索引:emp_name属性、升序;
create unique index emp_no_unique on employee(emp_no asc)
create clustered index emp_name_clustered on employee(emp_name asc)
drop index PK__employee__129850FAAF8804E9
go
说明:无法为员工表建立聚集索引,因为聚集索引只能有一个,表中已有PK索引表示聚集索引。
5、创建视图:
① 视图只含上海客户信息,即客户号、客户姓名、住址。
create view 上海客户信息(客户号,客户姓名,住址) as
select cust_id,cust_name,addr from customer
go
②有两个基本表employee和sales,创建一个视图,该视图包含相同业务员的编号、姓名、订单号、销售总金额。
create view 业务员销售(编号,姓名,订单号,销售总金额) as
select customer.cust_id,customer.cust_name,sales.order_no,qty*unit_price
from customer,sales,sales_item
where sales.cust_id=customer.cust_id and sales.order_no=sales_item.order_no
go
四、实验报告
实验二
实验名称: 数据操纵(4课时)
一. 实验目的
1、要求学生熟练掌握添加、修改、删除数据的操作。
2、要求学生熟练掌握数据查询操作。
二、实验环境
MS SQL SERVER 2000或者My SQL
三、实验内容与步骤
1.数据更新
①在每个表中插入若干条记录;
--1.数据更新
--(1)在每个表中插入若干条记录
--员工表employee 添加记录
insert into employee values('E0001','张三','m','业务','经理','1999-10-13','1970-08-01',8000,'上海市',getDate())
insert into employee values('E0002','李四','m','业务','职员','1999-10-01','1981-08-01',4000,'上海市',getDate())
insert into employee values('E0003','王五','m','会计','科长','1999-09-15','1982-06-09',4800,