以下SQL语句均在SQL2008环境下执行
在SQL Server2008中建立一个StuDB数据库:
有一个数据文件:逻辑名为StuData,文件名为“d:\db\StuData.mdf”,文件初始大小为3MB,文件的最大大小不受限制,文件的增长率为2MB;
有一个日志文件,逻辑名为StuLog,文件名为“d:\db\ StuLog.ldf”,文件初始大小为1MB,文件的最大大小为10MB,文件的增长率为10%
具体代码:
CREATE DATABASE StuDB
ON PRIMARY
(
NAME='StuData',
FILENAME='D:\db\StuData.mdf',
SIZE=3MB,
MAXSIZE=UNLIMITED,
FILEGROWTH=2MB
)
LOG ON
(
NAME='Stulog',
FILENAME='D:\db\StuLog.ldf',
SIZE=1MB,
MAXSIZE=10MB,
FILEGROWTH=10%
)
2、设有如下关系表S:
S(CLASS, SNO, NAME, SEX, AGE)。
其中:CLASS为班号,char(7) , SNO为座号,char(2);NAME为姓名,char(10),姓名不能为空且取值要求唯一;SEX为性别,char(2);AGE为年龄,int;表中主码为班号+座号。写出实现下列功能的SQL语句。
- 创建表S;
具体代码:
--创建表
CREATE TABLE S
(CLASS CHAR(7),
SNO CHAR(2),
NAME CHAR(10)UNIQUE NOT NULL,
SEX CHAR(2),
AGE INT,
PRIMARY KEY(CLASS, SNO)
);
- 插入一个记录(“2017031”,25,“李明”,“男”,21);
再插入一个记录(“2017032”,10,“王丽”,“女”,20);
具体代码:
--在表中插入记录
INSERT INTO S VALUES('2017031','25','李明','男','21');
INSERT INTO S VALUES('2017032','10','王丽','女','20');
- 插入“2017031”班座号为30,姓名为“郑和”的学生记录;
具体代码:
INSERT INTO S (CLASS, SNO, NAME) VALUES('2017031','30','郑和');
- 对表S,按姓名升序建唯一索引(索引名为sname);
对表S,按年龄降序建索引(索引名为sage);
具体代码:
CREATE UNIQUE INDEX sname ON S(NAME ASC);
CREATE INDEX sage ON S(AGE DESC);
运行结果(分别按照姓名、年龄进行查询表,是否为对应的升序和降序):
利用姓名索引查询表的代码:
SELECT *
FROM S
WITH(INDEX(sname))
利用年龄索引查询表的代码:
SELECT *
FROM S
WITH(INDEX(sage))
- 向S表添加“入学时间(comedate)”列,其数据类型为日期型(date);
具体代码:
--在表中插入新的列
ALTER TABLE S ADD comedate DATE;
- 删除S表的sage索引;
具体代码:
DROP INDEX sage ON S;
检验是否真的删除了该索引方法:
①:利用sp_help S;查询表的各项属性,找到index栏,看一下是否没有sage的索引
②:利用sage索引查询表,如果真正删除了,则应会报错,名为sage的索引不存在
- 将年龄的数据类型改为smallint;
具体代码:
--将表中某列的数据类型更改
ALTER TABLE S ALTER COLUMN AGE smallint;
- 删除学生姓名必须取唯一值的约束;
具体代码:
注:通过查询表的各项属性(sp_help S;),找到我们在创表时,给NAME的列级完整性约束条件中UNIQUE后,系统自动给NAME分配的一个约束名:
即为:UQ__S__D9C1FA00023D5A04,然后代入语句,即为:
ALTER TABLE S DROP CONSTRAINT UQ__S__D9C1FA00023D5A04;
- 删除S表;
具体代码:
--删除表
DROP TABLE S;
- 按照《数据库系统概论》(第五版)P79页的学生-课程数据库创建STUDENT、COURSE和SC三张表,每一张表都必须有主键约束,合理使用列级完整性约束和表级完整性约束。并输入相关数据。
具体代码:
--建STUDENT表
CREATE TABLE STUDENT
(Sno CHAR(10) PRIMARY KEY,
Sname CHAR(10),
Ssex CHAR(2),
Sage INT,
Sdept CHAR(4)
);
--给STUDENT表插入记录
INSERT INTO STUDENT VALUES('201215121','李勇','男','20','CS');
INSERT INTO STUDENT VALUES('201215122','刘晨','女','19','CS');
INSERT INTO STUDENT VALUES('201215123','王敏','女','18','MA');
INSERT INTO STUDENT VALUES('201215125','张立','男','19','IS');
--建COURSE表
CREATE TABLE COURSE
(Cno INT PRIMARY KEY,
Cname CHAR(10),
CpnO INT,
Ccredit INT
);
--给COURSE表插入记录
INSERT INTO COURSE VALUES('1','数据库','5','4');
INSERT INTO COURSE (Cno,Cname,Ccredit) VALUES('2','数学','2');
INSERT INTO COURSE VALUES('3','信息系统','1','4');
INSERT INTO COURSE VALUES('4','操作系统','6','3');
INSERT INTO COURSE VALUES('5','数据结构','7','4');
INSERT INTO COURSE (Cno,Cname,Ccredit) VALUES('6','数据处理','2');
INSERT INTO COURSE VALUES('7','PASCAL语言','6','2');
--Cpno的输入要受到Cno的约束
ALTER TABLE COURSE
ADD CONSTRAINT FK_Cpno FOREIGN KEY(Cpno) REFERENCES COURSE(Cno);
--建SC表
CREATE TABLE SC
(Sno CHAR(10),
Cno INT,
Grade INT CHECK (Grade>=0 And Grade<=100),
PRIMARY KEY(Sno,Cno),
FOREIGN KEY(Sno) REFERENCES Student(Sno),
FOREIGN KEY(Cno) REFERENCES COURSE(Cno)
);
--给SC表插入记录
INSERT INTO SC VALUES('201215121','1','92');
INSERT INTO SC VALUES('201215121','2','85');
INSERT INTO SC VALUES('201215121','3','88');
INSERT INTO SC VALUES('201215122','2','90');
INSERT INTO SC VALUES('201215122','3','80');
3.按照《数据库系统概论》(第五版)P70-71页习题6的SPJ数据库。创建SPJ数据库,并在其中创建S、P、J和SPJ四张表。每一张表都必须有主键约束,合理使用列级完整性约束和表级完整性约束。
具体代码:
--创建S表
CREATE TABLE S
(SNO CHAR(2) PRIMARY KEY,
SNAME CHAR(10),
STATUS INT,
CITY CHAR(10)
);
--为S表插入记录
INSERT INTO S VALUES('S1','精益','20','天津');
INSERT INTO S VALUES('S2','盛锡','10','北京');
INSERT INTO S VALUES('S3','东方红','30','北京');
INSERT INTO S VALUES('S4','丰泰盛','20','天津');
INSERT INTO S VALUES('S5','为民','30','上海');
--创建P表
CREATE TABLE P
(PNO CHAR(2) PRIMARY KEY,
PNAME CHAR(10),
COLOR CHAR(4),
WEIGHT INT
);
--为P表插入记录
INSERT INTO P VALUES('P1','螺母','红','12');
INSERT INTO P VALUES('P2','螺栓','绿','17');
INSERT INTO P VALUES('P3','螺丝刀','蓝','14');
INSERT INTO P VALUES('P4','螺丝刀','红','14');
INSERT INTO P VALUES('P5','凸轮','蓝','40');
INSERT INTO P VALUES('P5','齿轮','红','30');
--创建J表
CREATE TABLE J
(JNO CHAR(2) PRIMARY KEY,
JNAME CHAR(10),
CITY CHAR(10)
);
--为J表插入记录
INSERT INTO J VALUES('J1','三建','北京');
INSERT INTO J VALUES('J2','一汽','长春');
INSERT INTO J VALUES('J3','弹簧厂','天津');
INSERT INTO J VALUES('J4','造船厂','天津');
INSERT INTO J VALUES('J5','机车厂','唐山');
INSERT INTO J VALUES('J6','无线电厂','常州');
INSERT INTO J VALUES('J7','半导体厂','南京');
--创建SPJ表
CREATE TABLE SPJ
(SNO CHAR(2),
PNO CHAR(2),
JNO CHAR(2),
QTY INT,
PRIMARY KEY(SNO,PNO,JNO),
FOREIGN KEY(SNO) REFERENCES S(SNO),
FOREIGN KEY(PNO) REFERENCES P(PNO),
FOREIGN KEY(JNO) REFERENCES J(JNO)
);
--为SPJ表插入记录
INSERT INTO SPJ VALUES('S1','P1','J1','200');
INSERT INTO SPJ VALUES('S1','P1','J3','100');
INSERT INTO SPJ VALUES('S1','P1','J4','700');
INSERT INTO SPJ VALUES('S1','P2','J2','100');
INSERT INTO SPJ VALUES('S2','P3','J1','400');
INSERT INTO SPJ VALUES('S2','P3','J2','200');
INSERT INTO SPJ VALUES('S2','P3','J4','500');
INSERT INTO SPJ VALUES('S2','P3','J5','400');
INSERT INTO SPJ VALUES('S2','P5','J1','400');
INSERT INTO SPJ VALUES('S2','P5','J2','100');
INSERT INTO SPJ VALUES('S3','P1','J1','200');
INSERT INTO SPJ VALUES('S3','P3','J1','200');
INSERT INTO SPJ VALUES('S4','P5','J1','100');
INSERT INTO SPJ VALUES('S4','P6','J3','300');
INSERT INTO SPJ VALUES('S5','P6','J4','200');
INSERT INTO SPJ VALUES('S5','P2','J4','100');
INSERT INTO SPJ VALUES('S5','P3','J1','200');
INSERT INTO SPJ VALUES('S5','P6','J2','200');
INSERT INTO SPJ VALUES('S5','P6','J4','500');