tsc.txt

创建一个数据库 用于测试！以及训练 学生——课程——教师

USE master
go
CREATE DATABASE TSC
ON
(
   NAME = TSC_data,
   FILENAME = 'D:\TSC\TSC_data.mdf',
   SIZE = 5MB,
   MAXSIZE = 100MB,
   FILEGROWTH = 5MB
)
LOG ON 
(
   Name = TSC_log,
   FILENAME = 'D:\TSC\TSC_log.ldf',
   SIZE = 5MB,
   MAXSIZE = 20MB,
   FILEGROWTH = 3MB
)
go


USE TSC
go

CREATE TABLE Teacher
(
    /*教师编号：5位字符数据，比如计算机系的教师为CS001*/
    TeaID char(5)  NOT NULL 
          PRIMARY KEY NONCLUSTERED
          CHECK (TeaID LIKE '[A-Z][A-Z][0-9][0-9][0-9]'),
    TeaName varchar(50) NOT NULL,
    /*性别为男——M 女——W 默认值为男M*/
    TeaSex char(1) CHECK(TeaSex LIKE 'M' OR TeaSex LIKE 'W') 
           DEFAULT 'M',
    /*教师生日 默认值为1985年11月9日*/
    TeaBirthday datetime DEFAULT '1985-11-09',
    TeaProf varchar(10) CHECK (TeaProf LIKE '教授'
                            OR TeaProf LIKE '副教授'
                            OR TeaProf LIKE '讲师' 
                            OR TeaProf LIKE '助教')
            DEFAULT '教授',
    TeaMail varchar(40) /*电子邮件地址，[e-mail]*/
       CHECK( TeaMail like '%@%.%')
)


CREATE TABLE Student
(
    /*学号：8位字符数据，比如计算应用的学生**学号为03150079*/
    StuID char(8)  NOT NULL 
          PRIMARY KEY NONCLUSTERED
          CHECK (StuID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
    StuName varchar(50) NOT NULL,
    StuSex char(1) CHECK(StuSex LIKE 'M' OR StuSex LIKE 'W') 
           DEFAULT 'M',
    StuBirthday datetime DEFAULT '1985-11-09',
    StuMail varchar(40) /*电子邮件地址，[e-mail]*/
       CHECK( StuMail like '%@%.%')
)

CREATE TABLE Course
(
    CouID char(10)  NOT NULL 
          PRIMARY KEY NONCLUSTERED,
    CouName varchar(40) NOT NULL,
    CouCredit smallint DEFAULT 2
)

CREATE TABLE SelCourse
(
    StuID char(8)  NOT NULL    
          CHECK (StuID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
    CouID char(10)  NOT NULL,           
    Score char(2) CHECK (Score LIKE '优' OR Score LIKE '良' 
                      OR Score LIKE '中' OR Score LIKE '及' OR Score LIKE '不'),
    /*引用完整性约束*/
    PRIMARY KEY (StuID,CouID),
    FOREIGN KEY (StuID) REFERENCES Student,
    FOREIGN KEY (CouID) REFERENCES Course,
)
    

CREATE TABLE TeaCourse
(
    TeaID char(5)  NOT NULL 
          CHECK (TeaID LIKE '[A-Z][A-Z][0-9][0-9][0-9]'),
    CouID char(10)  NOT NULL,           
    CouTime smallint DEFAULT 2,
    /*引用完整性约束*/
    PRIMARY KEY (TeaID,CouID),
    FOREIGN KEY (TeaID) REFERENCES Teacher,
    FOREIGN KEY (CouID) REFERENCES Course,
)


CREATE TABLE SelCourse
(
    StuID char(8)  NOT NULL    
          CHECK (StuID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
    CouID char(10)  NOT NULL,           
    Score char(2) CHECK (Score LIKE '优' OR Score LIKE '良' 
                      OR Score LIKE '中' OR Score LIKE '及' OR Score LIKE '不'),
    /*引用完整性约束*/
    PRIMARY KEY (StuID,CouID),
    FOREIGN KEY (StuID) REFERENCES Student,
    FOREIGN KEY (CouID) REFERENCES Course,
)


DROP TABLE TeaCourse

CREATE TABLE TeaCourse
(
    TeaID char(5)  NOT NULL 
          CHECK (TeaID LIKE '[A-Z][A-Z][0-9][0-9][0-9]'),
    CouID char(10)  NOT NULL,           
    /*引用完整性约束*/
    PRIMARY KEY (TeaID,CouID),
    FOREIGN KEY (TeaID) REFERENCES Teacher,
    FOREIGN KEY (CouID) REFERENCES Course,
)
ALTER TABLE TeaCourse ADD CouTime smallint DEFAULT 2 
GO
 


实验数据
*****************************************************************************
INSERT INTO Teacher
VALUES ('CS000','张飞','M','1946-11-12','教授','[e-mail]')
INSERT INTO Teacher
VALUES ('CS001','关羽','W','1950-01-12','副教授','[e-mail]')
INSERT INTO Teacher
VALUES ('CS002','马超','M','1970-11-10','教授','[e-mail]')
INSERT INTO Teacher
VALUES ('CS003','赵云','M','1980-10-2','助教','[e-mail]')
***********************************************************************************
INSERT INTO Course
VALUES ('CS001','An Introduction to Computer', 3)
INSERT INTO Course
VALUES ('CS002','The Programing Language', 2)
INSERT INTO Course
VALUES ('CS003','Complier', 4)
INSERT INTO Course
VALUES ('CS004','DataBase', 3)
INSERT INTO Course
VALUES ('CS245','The Database Management', 2)
INSERT INTO Course
VALUES ('COM001','Collage English', 5)
INSERT INTO Course
VALUES ('COM002','Collahe Mathmatics',11)
INSERT INTO Course
VALUES ('COM003','大学语文', 2)
INSERT INTO Course
VALUES ('MT001','Math', 18)
INSERT INTO Course
VALUES ('MT002','ALG', 3)
INSERT INTO Course
VALUES ('MT003','系统分析', 2)
INSERT INTO Course
VALUES ('MT004','数值计算', 6)
*******************************************************************************
INSERT INTO Student
VALUES ('03150001','张三', 'M','1985-11-09','[e-mail]')
INSERT INTO Student
VALUES ('03150002','lisi', 'W','1989-12-09','[e-mail]')
INSERT INTO Student
VALUES ('03150003','wangwu', 'M','1987-6-09','[e-mail]')
INSERT INTO Student
VALUES ('03150004','haoguo', 'W','1988-11-09','[e-mail]')

INSERT INTO Student
VALUES ('03151001','de', 'M','1985-11-09','****@hqu.edu.cn')
INSERT INTO Student
VALUES ('03151002','af', 'M','1988-1-09','[e-mail]')

INSERT INTO Student
VALUES ('03152001','alpha', 'M','1984-11-29','[e-mail]')
INSERT INTO Student
VALUES ('03152002','bet', 'W','1985-1-19','[e-mail]')
INSERT INTO Student
VALUES ('03152003','delta', 'M','1983-10-09','[e-mail]')
INSERT INTO Student
VALUES ('03152004','huhu', 'M','1982-11-09','[e-mail]')

INSERT INTO Student
VALUES ('03154001','gug', 'M','1986-6-19','[e-mail]')
INSERT INTO Student
VALUES ('03154002','dede', 'M','1985-11-1','[e-mail]')
INSERT INTO Student
VALUES ('03154003','trrtr', 'W','1989-12-09','[e-mail]')
 
********************************************************************************
INSERT INTO SelCourse
VALUES('03150001','CS001','优')
INSERT INTO SelCourse
VALUES('03150001','CS002','良')
INSERT INTO SelCourse
VALUES('03150001','CS003','优')
INSERT INTO SelCourse
VALUES('03150001','CS245','优')
INSERT INTO SelCourse
VALUES('03150001','MT001','优')
INSERT INTO SelCourse
VALUES('03150001','MT002','优')

INSERT INTO SelCourse
VALUES('03150002','CS001','及')
INSERT INTO SelCourse
VALUES('03150002','CS002','中')
INSERT INTO SelCourse
VALUES('03150002','CS003','及')
INSERT INTO SelCourse
VALUES('03150002','CS004','优')
INSERT INTO SelCourse
VALUES('03150002','COM001','优')

INSERT INTO SelCourse
VALUES('03150003','CS245','优')
INSERT INTO SelCourse
VALUES('03150003','COM001','优')

INSERT INTO SelCourse
VALUES('03151001','COM001','良')
INSERT INTO SelCourse
VALUES('03151001','COM002','优')
INSERT INTO SelCourse
VALUES('03151002','MT001','优')
INSERT INTO SelCourse
VALUES('03151002','COM003','不')


INSERT INTO SelCourse
VALUES('03152001','CS001','优')
INSERT INTO SelCourse
VALUES('03152001','MT001','中')
INSERT INTO SelCourse
VALUES('03152002','CS003','不')
INSERT INTO SelCourse
VALUES('03152001','COM001','优')
INSERT INTO SelCourse
VALUES('03152001','COM002','优')
INSERT INTO SelCourse
VALUES('03152003','MT003','优')


INSERT INTO SelCourse
VALUES('03152001','CS002','不')
INSERT INTO SelCourse
VALUES('03154001','CS001','优')
INSERT INTO SelCourse
VALUES('03154001','MT001','良')
INSERT INTO SelCourse
VALUES('03154001','COM001','优')
INSERT INTO SelCourse
VALUES('03154001','CS004','中')
INSERT INTO SelCourse
VALUES('03154002','CS001','不')
INSERT INTO SelCourse
VALUES('03154002','CS002','中')
INSERT INTO SelCourse
VALUES('03154002','CS003','及')
INSERT INTO SelCourse
VALUES('03154002','CS004','良')
****************************************************8************************

**********************************************************************
更新
UPDATE Student
SET StuBirthday = '1987-09-09'
WHERE StuID = '03150001'

DELETE FROM Student 
WHERE  StuID = '03150001'
************************************************
索引

USE TSC
CREATE CLUSTERED INDEX IND_TID
ON Teacher(TeaID DESC)
go

USE TSC
DROP INDEX Teacher.IND_TID
go
*****************************************************

USE TSC
SELECT StuName FROM Student 
WHERE StuID IN 
     (SELECT StuID FROM SelCourse
      WHERE CouID IN 
            (SELECT CouID FROM Course
             WHERE CouName LIKE  'An INtroduction to Computer'
            )
      )

SELECT StuName FROM Student
WHERE (StuID = ANY (SELECT StuID FROM SelCourse
                    WHERE CouID = (SELECT CouID FROM Course
                               WHERE CouName LIKE  'An INtroduction to Computer' )
                    )
       )

USE TSC
SELECT * FROM Course
--至少选修了03150001选修的课程的同学
USE TSC
SELECT DISTINCT StuID
FROM SelCourse SCX
WHERE NOT EXISTS (SELECT * FROM SelCourse SCY
                  WHERE SCY.StuID = '03150004' 
                        AND NOT EXISTS (SELECT * FROM SelCourse SCZ 
                                        WHERE SCZ.StuID = SCX.StuID 
                                              AND SCZ.CouID = SCY.CouID))
select * from Course

SELECT StuName FROM Student WHERE StuID 
IN 
(
    SELECT StuID From SelCourse 
            WHERE CouID = (SELECT CouID FROM Course
                           WHERE CouName LIKE  'An INtroduction to Computer' )
    UNION 
    SELECT StuID From SelCourse 
            WHERE CouID = (SELECT CouID FROM Course
                           WHERE CouName LIKE  'DataBase' )
)

************************************************************************
--procedure
USE TSC
IF EXISTS (SELECT name FROM sysobjects 
    WHERE name = 'SelStudent_Pro' and type = 'p')
  DROP PROCEDURE SelStudent_Pro
go
--创建存储过程查询某一位学生的基本信息
CREATE PROCEDURE SelStudent_Pro @StuNo char(8)
AS
SELECT * FROM Student 
WHERE StuID = @StuNo

DECLARE @StuNO char(8)
SET @StuNO = '03150001'
EXEC SelStudent_Pro @StuNO



USE TSC
IF EXISTS (SELECT name FROM sysobjects 
    WHERE name = 'StuInsert_Pro' and type = 'p')
  DROP PROCEDURE SelStudent_Pro
go
CREATE PROCEDURE StuInsert_Pro
 @StuNo char(8),
 @StuName varchar(50),
 @StuSex char(1),
 @StuBirthday datetime,
 @StuMail varchar(40)
AS
--插入一条学生记录
INSERT INTO Student
VALUES (@StuNo,@StuName,@StuSex,@StuBirthday,@StuMail)

--修改存储过程
USE TSC
go
--创建存储过程查询某一位学生的姓名
ALTER PROCEDURE SelStudent_Pro @StuNo char(8)
AS
SELECT StuName FROM Student 
WHERE StuID = @StuNo

***************************************pp************************

******************************************************
function

--创建函数在数据库中取得学生的姓名
IF EXISTS (SELECT * FROM sysobjects
           WHERE name = 'GetStuName' AND type = 'fn')
DROP FUNCTION GetStuName
go

CREATE FUNCTION GetStuName( @StuNo char(8))
RETURNS varchar(50)
AS
BEGIN
   DECLARE @name varchar(40)
   SELECT @name = (SELECT StuName FROM Student 
                   WHERE StuID = @StuNo)
   RETURN @name
END

DECLARE @name varchar(40)
SELECT @name = dbo.GetStuName('03150001')
SELECT @name

****************************************************************