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
(
    /*教師編號(hào)：5位字符數(shù)據(jù)，比如計(jì)算機(jī)系的教師為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 默認(rèn)值為男M*/
    TeaSex char(1) CHECK(TeaSex LIKE 'M' OR TeaSex LIKE 'W') 
           DEFAULT 'M',
    /*教師生日 默認(rèn)值為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) /*電子郵件地址，goodness@hqu.edu.cn*/
       CHECK( TeaMail like '%@%.%')
)


CREATE TABLE Student
(
    /*學(xué)號(hào)：8位字符數(shù)據(jù)，比如計(jì)算應(yīng)用的學(xué)生**學(xué)號(hào)為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) /*電子郵件地址，goodness@hqu.edu.cn*/
       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 '優(yōu)' 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 '優(yōu)' 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
 


實(shí)驗(yàn)數(shù)據(jù)
*****************************************************************************
INSERT INTO Teacher
VALUES ('CS000','張飛','M','1946-11-12','教授','zhangfei@126.com')
INSERT INTO Teacher
VALUES ('CS001','關(guān)羽','W','1950-01-12','副教授','guanyu@sina.com')
INSERT INTO Teacher
VALUES ('CS002','馬超','M','1970-11-10','教授','machao@hqu.edu.cn')
INSERT INTO Teacher
VALUES ('CS003','趙云','M','1980-10-2','助教','zhaoyun@126.com')
***********************************************************************************
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','大學(xué)語文', 2)
INSERT INTO Course
VALUES ('MT001','Math', 18)
INSERT INTO Course
VALUES ('MT002','ALG', 3)
INSERT INTO Course
VALUES ('MT003','系統(tǒng)分析', 2)
INSERT INTO Course
VALUES ('MT004','數(shù)值計(jì)算', 6)
*******************************************************************************
INSERT INTO Student
VALUES ('03150001','張三', 'M','1985-11-09','goodness@hqu.edu.cn')
INSERT INTO Student
VALUES ('03150002','lisi', 'W','1989-12-09','siss@hqu.edu.cn')
INSERT INTO Student
VALUES ('03150003','wangwu', 'M','1987-6-09','wuwuw@hqu.edu.cn')
INSERT INTO Student
VALUES ('03150004','haoguo', 'W','1988-11-09','zgu@126.com')

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

INSERT INTO Student
VALUES ('03152001','alpha', 'M','1984-11-29','alp@hqu.edu.cn')
INSERT INTO Student
VALUES ('03152002','bet', 'W','1985-1-19','bb@hqu.edu.cn')
INSERT INTO Student
VALUES ('03152003','delta', 'M','1983-10-09','del@hqu.edu.cn')
INSERT INTO Student
VALUES ('03152004','huhu', 'M','1982-11-09','frfr@hqu.edu.cn')

INSERT INTO Student
VALUES ('03154001','gug', 'M','1986-6-19','ggggg@hqu.edu.cn')
INSERT INTO Student
VALUES ('03154002','dede', 'M','1985-11-1','eeeeeeeee@hqu.edu.cn')
INSERT INTO Student
VALUES ('03154003','trrtr', 'W','1989-12-09','rrrrrrrrs@hqu.edu.cn')
 
********************************************************************************
INSERT INTO SelCourse
VALUES('03150001','CS001','優(yōu)')
INSERT INTO SelCourse
VALUES('03150001','CS002','良')
INSERT INTO SelCourse
VALUES('03150001','CS003','優(yōu)')
INSERT INTO SelCourse
VALUES('03150001','CS245','優(yōu)')
INSERT INTO SelCourse
VALUES('03150001','MT001','優(yōu)')
INSERT INTO SelCourse
VALUES('03150001','MT002','優(yōu)')

INSERT INTO SelCourse
VALUES('03150002','CS001','及')
INSERT INTO SelCourse
VALUES('03150002','CS002','中')
INSERT INTO SelCourse
VALUES('03150002','CS003','及')
INSERT INTO SelCourse
VALUES('03150002','CS004','優(yōu)')
INSERT INTO SelCourse
VALUES('03150002','COM001','優(yōu)')

INSERT INTO SelCourse
VALUES('03150003','CS245','優(yōu)')
INSERT INTO SelCourse
VALUES('03150003','COM001','優(yōu)')

INSERT INTO SelCourse
VALUES('03151001','COM001','良')
INSERT INTO SelCourse
VALUES('03151001','COM002','優(yōu)')
INSERT INTO SelCourse
VALUES('03151002','MT001','優(yōu)')
INSERT INTO SelCourse
VALUES('03151002','COM003','不')


INSERT INTO SelCourse
VALUES('03152001','CS001','優(yōu)')
INSERT INTO SelCourse
VALUES('03152001','MT001','中')
INSERT INTO SelCourse
VALUES('03152002','CS003','不')
INSERT INTO SelCourse
VALUES('03152001','COM001','優(yōu)')
INSERT INTO SelCourse
VALUES('03152001','COM002','優(yōu)')
INSERT INTO SelCourse
VALUES('03152003','MT003','優(yōu)')


INSERT INTO SelCourse
VALUES('03152001','CS002','不')
INSERT INTO SelCourse
VALUES('03154001','CS001','優(yōu)')
INSERT INTO SelCourse
VALUES('03154001','MT001','良')
INSERT INTO SelCourse
VALUES('03154001','COM001','優(yōu)')
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選修的課程的同學(xué)
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
--創(chuàng)建存儲(chǔ)過程查詢某一位學(xué)生的基本信息
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
--插入一條學(xué)生記錄
INSERT INTO Student
VALUES (@StuNo,@StuName,@StuSex,@StuBirthday,@StuMail)

--修改存儲(chǔ)過程
USE TSC
go
--創(chuàng)建存儲(chǔ)過程查詢某一位學(xué)生的姓名
ALTER PROCEDURE SelStudent_Pro @StuNo char(8)
AS
SELECT StuName FROM Student 
WHERE StuID = @StuNo

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

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

--創(chuàng)建函數(shù)在數(shù)據(jù)庫中取得學(xué)生的姓名
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

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