建立数据库及DDL语言实践

以下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语句。

1. 创建表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)

 );

 

1. 插入一个记录（“2017031”，25，“李明”，“男”，21）；
   再插入一个记录（“2017032”，10，“王丽”，“女”，20）；

具体代码：

--在表中插入记录

INSERT INTO S VALUES('2017031','25','李明','男','21');

INSERT INTO S VALUES('2017032','10','王丽','女','20');

 

1. 插入“2017031”班座号为30，姓名为“郑和”的学生记录；

具体代码：

INSERT INTO S (CLASS, SNO, NAME) VALUES('2017031','30','郑和');

 

1. 对表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))

 

 

1. 向S表添加“入学时间（comedate）”列，其数据类型为日期型（date）；

具体代码：

--在表中插入新的列

ALTER TABLE S ADD comedate DATE;

 

1. 删除S表的sage索引；

具体代码：

DROP INDEX sage ON S;

 

检验是否真的删除了该索引方法：

①：利用sp_help S;查询表的各项属性，找到index栏，看一下是否没有sage的索引

②：利用sage索引查询表，如果真正删除了，则应会报错，名为sage的索引不存在

 

1. 将年龄的数据类型改为smallint；

具体代码：

--将表中某列的数据类型更改

ALTER TABLE S ALTER COLUMN AGE smallint;

 

1. 删除学生姓名必须取唯一值的约束；

具体代码：

注：通过查询表的各项属性（sp_help S;），找到我们在创表时，给NAME的列级完整性约束条件中UNIQUE后，系统自动给NAME分配的一个约束名：

 

即为：UQ__S__D9C1FA00023D5A04，然后代入语句，即为：

ALTER TABLE S DROP CONSTRAINT UQ__S__D9C1FA00023D5A04;

 

 

1. 删除S表；

具体代码：

--删除表

DROP TABLE S;

 

1. 按照《数据库系统概论》(第五版)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');