SQL数据创建

创建数据库：

CREATE DATABASE MyDB ON PRIMARY ( NAME='MyDB_Primary', FILENAME= 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_Prm.mdf', SIZE=4MB, MAXSIZE=10MB, FILEGROWTH=1MB), FILEGROUP MyDB_FG1 ( NAME = 'MyDB_FG1_Dat1', FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_1.ndf', SIZE = 1MB, MAXSIZE=10MB, FILEGROWTH=1MB), ( NAME = 'MyDB_FG1_Dat2', FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_2.ndf', SIZE = 1MB, MAXSIZE=10MB,FILEGROWTH=1MB) LOG ON ( NAME='MyDB_log', FILENAME ='c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB.ldf', SIZE=1MB, MAXSIZE=10MB, FILEGROWTH=1MB);

创建学生表、课程表以及学生选课表

CREATE TABLE STUDENT(Sno CHAR(5) PRIMARY KEY CHECK(Sno LIKE '[0-9][0-9][0-9]'), Sname VARCHAR(8), Ssex CHAR(2) CHECK(Ssex IN ('男','女')), Sage INT CHECK (Sage>15 and Sage<40), Sdept VARCHAR(15)); CREATE TABLE Course (Cno CHAR(4) PRIMARY KEY, Cname CHAR(30), Cpno CHAR(4) REFERENCES Course(Cno), Ccredit INT CHECK (Ccredit>0 and Ccredit<=5)) CREATE TABLE SC(Sno CHAR(5), Cno CHAR(4), Grade INT CHECK(Grade>=0 and Grade<=100), CONSTRAINT SC_PK PRIMARY KEY(Sno,Cno), CONSTRAINT SC_FK_1 FOREIGN KEY(Sno) REFERENCES Student(Sno), CONSTRAINT SC_FK_2 FOREIGN KEY(Cno) REFERENCES Student(Cno))

插入数据

INSERT INTO Course VALUES('2','数学',NULL,'2'); INSERT INTO Course VALUES('6','数据处理',NULL,'2'); INSERT INTO Course VALUES('7','PASCAL语言','6',4); INSERT INTO Course VALUES('5','数据结构','7',4); INSERT INTO Course VALUES('4','操作系统','6',3); INSERT INTO Course VALUES('1','数据库','5',4); INSERT INTO Course VALUES('3','信息系统','1',4); INSERT INTO Course VALUES('8','DB_Design',NULL,3); INSERT INTO Course VALUES('9','DB%Design',NULL,4); INSERT INTO SC VALUES('95001','1',92); INSERT INTO SC VALUES('95001','2',85); INSERT INTO SC VALUES('95001','3',88); INSERT INTO SC VALUES('95002','2',90); INSERT INTO SC VALUES('95002','3',80); INSERT INTO SC VALUES('95004','3',80); INSERT INTO SC VALUES('95004','1',NULL); INSERT INTO SC VALUES('95004','2',NULL);