实验2 SQL的数据定义功能

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

create database supermarket
go
create table Student(
	SNO varchar(20) primary key,
	SName varchar(20),
	BirthYear int,
	Ssex varchar(2),
	College varchar(100),
	Major varchar(100),
	WeiXin varchar(100)
)

create table Category(
	CategoryNO varchar(20) primary key,
	CategoryName varchar(100),
	Description varchar(500)
)

create table Supplier(
	SupplierNO varchar(20) primary key,
	SupplierName varchar(100),
	Address varchar(200),
	Telephone varchar(20)
)

create table Goods(
	GoodsNO varchar(20) primary key,
	SupplierNO varchar(20),
	CategoryNO varchar(20),
	GoodsName varchar(100),
	InPrice decimal(18,2),
	SalePrice decimal(18,2),
	Number int,
	ProductTime smalldatetime,
	QGPeriod tinyint,
	foreign key(CategoryNO) references Category (CategoryNO),
	foreign key (SupplierNO) references Supplier (SupplierNO)
)



create table SaleBill(
	GoodsNO varchar(20),
	SNO varchar(20),
	HappenTime datetime,
	Number int,
	primary key (GoodsNO,SNO),
	foreign key (GoodsNO) references Goods (GoodsNO),
	foreign key (SNO) references Student (SNO)
)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

use supermarket
go
insert into supermarket.dbo.Student values('S01','李明',2001,'男','CS','IT','wx001');
insert into supermarket.dbo.Student values('S02','徐好',2000,'女','CS','IT','wx002');
insert into supermarket.dbo.Student values('S03','伍民',1998,'男','CS','MIS','wx003');
insert into supermarket.dbo.Student values('S04','闵红',1999,'女','ACC','IT','wx004');
insert into supermarket.dbo.Student values('S05','张小红',1999,'女','ACC','IT','wx005');
insert into supermarket.dbo.Student values('S06','张舒',2001,'男','CS','MIS','wx006');
insert into supermarket.dbo.Student values('S07','王民为',1999,'男','CS','MIS','wx007');
insert into supermarket.dbo.Student values('S08','李士任',2001,'男','ACC','IT','wx008');


insert into supermarket.dbo.Category values('CN001','咖啡','速溶咖啡、咖啡粉、罐装咖啡');
insert into supermarket.dbo.Category values('CN002','洗发水','袋装、瓶装洗发水');
insert into supermarket.dbo.Category values('CN003','方便面','袋装、碗装方便面');

insert into supermarket.dbo.Supplier values('Sup001','卡夫食品（中国）有限公司广州分公司','广州佛山','12348768900');
insert into supermarket.dbo.Supplier values('Sup002','东菀市南城久润食品贸易部','广州东菀','13248768901');
insert into supermarket.dbo.Supplier values('Sup003','重庆飞鹤食品贸易公司','重庆解放碑','12648768901');
insert into supermarket.dbo.Supplier values('Sup004','重庆南山日化品贸易公司','重庆南坪','11648768901');
insert into supermarket.dbo.Supplier values('Sup005','重庆缙云日化品贸易公司','重庆北碚','19648768903');


insert into supermarket.dbo.Goods values('GN0001','Sup001','CN001','麦氏威尔冰咖啡',5.79,7.80,20,'2016-02-08 00:00:00',18);
insert into supermarket.dbo.Goods values('GN0002','Sup002','CN001','捷荣三合一咖啡',12.30,17.30,15,'2017-10-08 00:00:00',18);
insert into supermarket.dbo.Goods values('GN0003','Sup002','CN001','力神咖啡',1.81,2.70,30,'2018-05-06 00:00:00',18);
insert into supermarket.dbo.Goods values('GN0004','Sup001','CN001','麦氏威尔小三合一咖啡',8.12,10.80,20,'2017-05-06 00:00:00',18);
insert into supermarket.dbo.Goods values('GN0005','Sup003','CN001','雀巢香滑咖啡饮料',1.99,2.70,3,'2018-01-01 00:00:00',18);
insert into supermarket.dbo.Goods values('GN0006','Sup003','CN001','雀巢听装咖啡',84.21,113.70,6,'2018-05-06 00:00:00',18);
insert into supermarket.dbo.Goods values('GN0007','Sup004','CN002','夏士莲丝质柔顺洗发水',25.85,35.70,30,'2018-03-08 00:00:00',36);
insert into supermarket.dbo.Goods values('GN0008','Sup005','CN002','飞逸清新爽节洗发水',20.47,30.00,50,'2018-03-09 00:00:00',36);
insert into supermarket.dbo.Goods values('GN0009','Sup005','CN002','力士柔亮洗发水（中/干）',22.65,32.30,20,'2017-12-08 00:00:00',36);
insert into supermarket.dbo.Goods values('GN0010','Sup005','CN002','风影去屑洗发水（清爽）',22.98,34.20,6,'2017-10-07 00:00:00',36);

insert into supermarket.dbo.SaleBill values('GN0001','S01','2018-06-09 00:00:00',3);
insert into supermarket.dbo.SaleBill values('GN0001','S02','2018-05-03 00:00:00',1);
insert into supermarket.dbo.SaleBill values('GN0001','S03','2018-04-07 00:00:00',1);
insert into supermarket.dbo.SaleBill values('GN0001','S06','2018-06-12 00:00:00',2);
insert into supermarket.dbo.SaleBill values('GN0002','S02','2018-05-08 00:00:00',2);
insert into supermarket.dbo.SaleBill values('GN0002','S05','2018-06-26 00:00:00',3);
insert into supermarket.dbo.SaleBill values('GN0002','S06','2018-06-16 00:00:00',2);
insert into supermarket.dbo.SaleBill values('GN0003','S01','2018-07-10 00:00:00',2);
insert into supermarket.dbo.SaleBill values('GN0003','S02','2018-07-08 00:00:00',2);
insert into supermarket.dbo.SaleBill values('GN0003','S05','2018-06-01 00:00:00',2);
insert into supermarket.dbo.SaleBill values('GN0003','S06','2018-07-01 00:00:00',2);
insert into supermarket.dbo.SaleBill values('GN0005','S05','2018-06-11 00:00:00',1);
insert into supermarket.dbo.SaleBill values('GN0006','S03','2018-05-07 00:00:00',1);
insert into supermarket.dbo.SaleBill values('GN0007','S01','2018-06-09 00:00:00',1);
insert into supermarket.dbo.SaleBill values('GN0007','S04','2018-06-08 00:00:00',1);
insert into supermarket.dbo.SaleBill values('GN0007','S05','2018-06-09 00:00:00',1);
insert into supermarket.dbo.SaleBill values('GN0008','S02','2018-06-04 00:00:00',1);
insert into supermarket.dbo.SaleBill values('GN0008','S06','2018-06-28 00:00:00',1);

1
2
3
4
5

alter table Category add Cat_CategoryNO varchar(20)

alter table Goods drop column Barcode   

alter table Supplier alter column SupplierName nvarchar(200) not null;

1

drop table sjktable

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

use students
go
create table Student(
	Sno char(7) primary key,
	Sname nchar(5) not null,
	Sex nchar(1),
	Sage tinyint,
	Sdept nvarchar(20)
)

create table Course(
	Cno char(6) primary key,
	Cname nvarchar(20) not null,
	Ccredit tinyint,
	Semester tinyint
)
create table Sc(
	Sno char(7),
	Cno char(6),
	Grade tinyint,
	primary key(Sno,Cno)
)

1

alter table Student add Address nvarchar(50)

1

alter table Student alter column Address nvarchar(30)

1

alter table Student drop column Address

1
2
3
4
5

create unique nonclustered index index_xinz on salary(Saname asc)

create unique index index_yfsf on salary(Sapayabl asc,Psalary desc)

drop index salary.index_xinz,salary.index_yfsf

1
2
3

use supermarket
go
create unique nonclustered index index_supplier on supplier(SupplierName)

1
2

聚集索引是通过设置主码来完成的，每个表的主码都是聚集索引，一个表只能有一个聚集索引，
非聚集索引可以有多个。因此无法重复创建聚焦索引

1

drop index supplier.index_supplier