本文共 3605 字，大约阅读时间需要 12 分钟。
USE master GO IF EXISTS (SELECT * FROM sysdatabases WHERE name='MyStudentInfoManage')DROP DATABASE MyStudentInfoManage --删除数据库GO CREATE DATABASE MyStudentInfoManage --创建数据库GO USE MyStudentInfoManageGO --学生表IF EXISTS(SELECT * FROM sysobjects WHERE name='Student')DROP TABLE Student --删除表GO CREATE TABLE Student( -- Student(S#,Sname,Sage,Ssex) 学生表 S# INT NOT NULL PRIMARY KEY , --学号 Sname NVARCHAR(20) NOT NULL, --姓名 Sage INT NOT NULL , --年龄 Ssex CHAR(2) NOT NULL --性别 );--教师表IF EXISTS(SELECT * FROM sysobjects WHERE name='Teacher')DROP TABLE TeacherGO CREATE TABLE Teacher( --Teacher(T#,Tname) 教师表 T# INT NOT NULL PRIMARY KEY, Tname NVARCHAR(20) NOT NULL );--课程表IF EXISTS(SELECT * FROM sysobjects WHERE name='Course')DROP TABLE CourseGO CREATE TABLE Course( --Course(C#,Cname,T#) 课程表 C# INT NOT NULL PRIMARY KEY , Cname NVARCHAR(20) NOT NULL , T# INT NOT NULL REFERENCES dbo.Teacher(T#) );--成绩表IF EXISTS(SELECT * FROM sysobjects WHERE name='SC')DROP TABLE SCGO CREATE TABLE SC( --SC(S#,C#,score) 成绩表 S# INT NOT NULL REFERENCES dbo.Student(S#), C# INT NOT NULL REFERENCES dbo.Course(C#), Score INT NOT NULL, CONSTRAINT FK_Student_Course PRIMARY KEY(S#,C#) --学号和课程号同时座位 );----------------------------------------------------------------------插入测试数据到数据表(Student)中--------------------------------------------DELETE FROM dbo.Student;INSERT INTO dbo.Student ( S#, Sname, Sage, Ssex )VALUES ( 001, -- S# - int N'张三', -- Sname - nvarchar(20) 20, -- Sage - int '男' -- Ssex - char(2) ) INSERT INTO dbo.Student ( S#, Sname, Sage, Ssex )VALUES ( 002, -- S# - int N'李四', -- Sname - nvarchar(20) 21, -- Sage - int '男' -- Ssex - char(2) ) INSERT INTO dbo.Student ( S#, Sname, Sage, Ssex )VALUES ( 003, -- S# - int N'王五', -- Sname - nvarchar(20) 22, -- Sage - int '女' -- Ssex - char(2) ) INSERT INTO dbo.Student ( S#, Sname, Sage, Ssex )VALUES ( 004, -- S# - int N'周六', -- Sname - nvarchar(20) 18, -- Sage - int '女' -- Ssex - char(2) ) ------------------------------------------------------------插入数据到教师表(Teacher)中----------------------------------------------------------DELETE FROM dbo.Teacher; INSERT INTO dbo.Teacher ( T#, Tname )VALUES ( 01, -- T# - int N'叶平' -- Tname - nvarchar(20) ) ------------------------------------------------------------------插入数据到课程表中-------------------------------------------------------------DELETE FROM dbo.Course;INSERT INTO dbo.Course ( C#, Cname, T# )VALUES ( 001, -- C# - int N'语文', -- Cname - nvarchar(20) 01 -- T# - int ) INSERT INTO dbo.Course ( C#, Cname, T# )VALUES ( 002, -- C# - int N'数学', -- Cname - nvarchar(20) 01 -- T# - int ) INSERT INTO dbo.Course ( C#, Cname, T# )VALUES ( 0003, -- C# - int N'英语', -- Cname - nvarchar(20) 01 -- T# - int ) --------------------------插入数据到成绩表（SC）中----------------------------------------------------------------------------------------------- DELETE FROM dbo.SC; INSERT INTO dbo.SC ( S#, C#, Score )VALUES ( 001, -- S# - int 001, -- C# - int 90 -- Score - int )
转载地址：http://gxxkz.baihongyu.com/