SQL查询语言练习

* FROM sysdatabases WHERE name=‘MyStudentInfoManage‘) DROP DATABASE MyStudentInfoManage --删除数据库 GO CREATE DATABASE MyStudentInfoManage --创建数据库 GO USE MyStudentInfoManage GO --学生表 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 Teacher GO 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 Course GO 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 SC GO 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 )

 

SQL查询语言练习

标签：