《数据库原理》实验四 SQL的数据定义语句

本系列传送门：

1. 实验二 SQL Server SSMS工具创建和管理数据库及数据表
2. 实验三 SQL Server SSMS工具添加数据
3. 实验四 SQL的数据定义语句
4. 实验五 数据库完整性约束的实现与验证
5. 实验六 SQL数据查询实验
6. 实验六（2） SQL数据查询—连接查询
7. 实验七 SQL的数据更新和视图
8. 实验八 T_SQL编程
9. 实验九 存储过程和触发器
10. 实验十 索引和数据库的安全性

1. 实验要求

1． 掌握 SQL数据定义语句：create、 drop 、alter各自的功能
2． 掌握数据库、模式、基本表的创建、修改、删除的SQL语句语法格式
3． 通过SQL语句完成“电子商务系统”数据库及其基本表、模式的创建、修改和删除实践练习

2. 实验内容

2.1 通过SQL语句创建名为ecommerce1的数据库：

CREATE DATABASE ecommerce1

2.2 在数据库ecommerce1中练习模式的创建和删除语句

（如给用户li创建一个学生管理模式“S-T”）（需要先添加一个用户li）

use ecommerce1
create schema S_T authorization li;

2.3 在数据库ecommerce1中执行SQL语句操作，创建如下表：

2.3.1 商品类别表 category

category(catno,catname,describe)，主码为catno

CREATE TABLE category (
	catno	int	not null primary key,
	catname varchar(30) not null,
	describe text
)

2.3.2 商品表 product

product(prono,proname,brand,stock,supno,price,cost,picture,catno,ontime,status)，要求proname不能取空值

CREATE TABLE product (
	prono	int not null primary key,
	proname	varchar(30) not null,
	brand	varchar(30),
	stock	int,
	supno	int,
	price	smallmoney,
	cost	smallmoney,
	picture	varchar(30),
	catno	int,
	ontime	datetime,
	satus	smallint
)

2.3.3 供应商表 supplier

supplier(supno,supname,contactname,address,telephone)，主码为(supno),属性supname不能取空值

CREATE TABLE supplier(
	supno	int not null primary key,
	supname	varchar(30) not null,
	contactname	varchar(20),
	address	varchar(30),
	telephone	varchar(15),
)

2.3.4 订单表 orders

orders(orderno,memno,prono,qty,discount,totalmoney,orderdate,paydate)

• 属性prono参照商品表product的属性prono
• memno参照会员表member的属性memno

create table orders(
	orderno int primary key,
	memno	int not null,
	prono	int not null,
	qty int,
	discount money,
	totalmoney money,
	orderdate datetime,
	paydate datetime
);

2.3.5 会员表 member

member(memno,memname,address,telephone,username,userpwd)

CREATE TABLE member(
	memno	int not null primary key,
	memname	varchar(30) not null,
	address	varchar(50),
	telephone	varchar(15),
	username	varchar(30) not null unique,
	userpwd	varchar(30) not null
)

2.3.6 员工表 employee

employee(empno,empname,depno,sex,telephone,username,userpwd)

CREATE TABLE employee(
	empno	int not null,
	empname	varchar(30) not null,
	depno	int,
	sex	varchar(4),
	telephone	varchar(15),
	username	varchar(30) not null,
	userpwd	varchar(30) not null,
)

2.3.7 部门表 department

department(depno,depname,manager,deptotal)

CREATE TABLE department(
	depno	int not null primary key,
	deppname	varchar(30) not null,
	manager	int,
	deptotal	int,
)

2.4 对上表增加外键约束

由于存在两个表互相有约束，所以不方便在建表的时候就加外键。
完成上述表的创建后，可以执行下列语句进行外键的添加。

alter table product 
add constraint fk_supno
foreign key(supno) references supplier(supno);

alter table orders
add constraint fk_memno
foreign key(memno) references member(memno);

alter table orders
add constraint fk_prono
foreign key(prono) references product(prono);

alter table employee
add constraint fk_depno
foreign key(depno) references department(depno);

alter table department
add constraint fk_empno
foreign key(manager) references employee(empno);

（说明：表的属性设置具体参照实验二即可）

2.5 使用sql语句修改表

（1）为订单表增加一属性deliverdate(发货日期)，数据类型为datetime，允许取空值。

alter table orders
add deliverdate datetime

（2）修改商品表中属性列ontime的数据类型为date

alter table product
alter column ontime date

（3）为商品类别表中属性列catname增加不能取空值（not null）的约束

alter table category
alter column catname varchar(30) not null;

（4）为商品表增加一个主码约束，约束名为pk1，主码为prono

alter table product
add constraint pk1 primary key(prono);

（5）为员工表employee的属性userpwd增加一个默认值约束，默认值为123456，约束名为DF1

alter table employee
add constraint DF1 default 123456 for userpwd

（6）删除约束DF1

alter table employee
drop constraint DF1

（7）为会员表member属性列username添加取唯一值的约束，约束名为UK1

alter table member
add constraint UK1 unique(username)

（8）删除订单表中新增的属性deliverdate

alter table orders
drop column deliverdate

（9）为商品表中属性列catno增加一个外码约束，约束名为FK1，要求其参照商品类别表中主码catno的值

alter table product
add constraint FK1
foreign key(catno) references category(catno)

（10）删除会员表、员工表和部门表

先删除外键约束

alter table department
drop constraint fk_empno;

alter table employee
drop constraint fk_depno;

alter table orders
drop constraint fk_memno;

再删除表

drop table member;
drop table employee;
drop table department;