SQL数据创建

最新推荐文章于 2022-06-21 13:07:56 发布

just__we

最新推荐文章于 2022-06-21 13:07:56 发布

阅读量183

点赞数

分类专栏：课堂笔记

本文链接：https://blog.csdn.net/weixin_43346864/article/details/102854910

版权

课堂笔记专栏收录该内容

5 篇文章 0 订阅

订阅专栏

创建数据库：

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);