SQL查询语言练习

USE master

GO

IF EXISTS (SELECT * 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() NOT NULL,      --姓名

     Sage INT NOT NULL ,               --年龄

     Ssex CHAR() 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() 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() 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  ( , -- S# - int

          N'张三', -- Sname - nvarchar()

          , -- Sage - int

          '男'  -- Ssex - char()

          )

INSERT INTO dbo.Student

        ( S#, Sname, Sage, Ssex )

VALUES  ( , -- S# - int

          N'李四', -- Sname - nvarchar()

          , -- Sage - int

          '男'  -- Ssex - char()

          )

 INSERT INTO dbo.Student

        ( S#, Sname, Sage, Ssex )

VALUES  ( , -- S# - int

          N'王五', -- Sname - nvarchar()

          , -- Sage - int

          '女'  -- Ssex - char()

          )

INSERT INTO dbo.Student

        ( S#, Sname, Sage, Ssex )

VALUES  ( , -- S# - int

          N'周六', -- Sname - nvarchar()

          , -- Sage - int

          '女'  -- Ssex - char()

          )

------------------------------------------------------------插入数据到教师表(Teacher)中----------------------------------------------------------

DELETE FROM dbo.Teacher;

INSERT INTO dbo.Teacher

        ( T#, Tname )

VALUES  ( , -- T# - int

          N'叶平'  -- Tname - nvarchar()

          )

------------------------------------------------------------------插入数据到课程表中-------------------------------------------------------------

DELETE FROM dbo.Course;

INSERT INTO dbo.Course

        ( C#, Cname, T# )

VALUES  ( , -- C# - int

          N'语文', -- Cname - nvarchar()

            -- T# - int

          )

INSERT INTO dbo.Course

        ( C#, Cname, T# )

VALUES  ( , -- C# - int

          N'数学', -- Cname - nvarchar()

            -- T# - int

          )

INSERT INTO dbo.Course

        ( C#, Cname, T# )

VALUES  ( , -- C# - int

          N'英语', -- Cname - nvarchar()

            -- T# - int

          )

--------------------------插入数据到成绩表（SC）中-----------------------------------------------------------------------------------------------

DELETE FROM dbo.SC;

INSERT INTO dbo.SC

        ( S#, C#, Score )

VALUES  ( , -- S# - int

          , -- C# - int

            -- Score - int

          )