SQL查询语言练习

时间:2015-07-17 20:12:49   收藏:0   阅读:263
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(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
          )             

 

原文:http://www.cnblogs.com/caofangsheng/p/4655308.html

评论(0
© 2014 bubuko.com 版权所有 - 联系我们:wmxa8@hotmail.com
打开技术之扣,分享程序人生!