学习数据库的好资料

/***************************************
 作者：颜清国
 学习数据库的资料 2007.4.19
*****************************************/

/*****************************************

COURSE表：

CNO    CNAME                CPNO   CCREDIT
------ -------------------- ------ -------
1      SS                   5      4
2      数学                 NULL   4
3      信息系统             1      4
4      操作系统             6      3
5      数据结构             7      4
6      数据处理             NULL   2
7      PASCAL语言           6      4

STUDENT表：

SNO       SNAME      SSEX   SAGE   SDEPT     
--------- ---------- ------ ------ ----------
200215121 李勇         M      20     CS
200215122 刘晨         F      19     CS
200215123 王敏         F      18     MA
200215125 张立         M      19     IS

SC表：

SNO       CNO    GRADE 
--------- ------ ------
200215121 1      92
200215121 2      85
200215121 3      88
200215122 2      90
200215122 3      80

*****************************************/
/****************************************
  创建一个数据库
*****************************************/
CREATE DATABASE ST;

USE ST;   /*使用ST数据库*/

/****************************************
 创建学生表
*****************************************/
CREATE TABLE STUDENT
 ( SNO CHAR(9) PRIMARY KEY,  /*学号*/
   SNAME VARCHAR(10),    /*姓名*/
   SSEX VARCHAR(6),      /*性别*/
   SAGE SMALLINT,        /*年龄*/
   SDEPT VARCHAR(10)     /*院系*/
  );

 

/******************************************
 创建课程表
******************************************/

CREATE TABLE COURSE
  ( CNO SMALLINT PRIMARY KEY,   /*课程号*/
    CNAME VARCHAR(20),          /*课程名称*/
    CPNO SMALLINT,              /*先行课程*/
    CCREDIT SMALLINT,            /*学分*/
    FOREIGN KEY (CPNO) REFERENCES COURSE(CNO)  /*定义外键*/
   );

/******************************************
 创建学生选课表
*******************************************/
CREATE TABLE SC
   ( SNO CHAR(9), 
     CNO SMALLINT,
     GRADE SMALLINT,
     PRIMARY KEY (SNO,CNO),
     FOREIGN KEY (SNO) REFERENCES STUDENT(SNO),
     FOREIGN KEY (CNO) REFERENCES COURSE(CNO)
   );

/**********************************************
 为三个表建立索引
***********************************************/
CREATE UNIQUE INDEX COURSEID ON COURSE(CNO);
CREATE UNIQUE INDEX STUDENTID ON STUDENT(SNO);
CREATE UNIQUE INDEX SCID ON SC(SNO,CNO);  /*默认为升序*/

/*******************************************************
 为学生表插入数据，这里在性别里面输入中文MYSQL报错
*********************************************************/
INSERT INTO STUDENT(SNO,SNAME,SSEX,SAGE,SDEPT) VALUES('200215121','李勇','M',20,'CS');
INSERT INTO STUDENT(SNO,SNAME,SSEX,SAGE,SDEPT) VALUES('200215122','刘晨','F',19,'CS');
INSERT INTO STUDENT(SNO,SNAME,SSEX,SAGE,SDEPT) VALUES('200215123','王敏','F',18,'MA');
INSERT INTO STUDENT(SNO,SNAME,SSEX,SAGE,SDEPT) VALUES('200215125','张立','M',19,'IS');

/*************************************************
 为课程表插入数据
**************************************************/
INSERT INTO COURSE(CNO,CNAME,CPNO,CCREDIT) VALUES(1,'SS',NULL,4);
INSERT INTO COURSE(CNO,CNAME,CPNO,CCREDIT) VALUES(2,'数学',NULL,4);
INSERT INTO COURSE(CNO,CNAME,CPNO,CCREDIT) VALUES(3,'信息系统',1,4);
INSERT INTO COURSE(CNO,CNAME,CPNO,CCREDIT) VALUES(4,'操作系统',NULL,3);
INSERT INTO COURSE(CNO,CNAME,CPNO,CCREDIT) VALUES(5,'数据结构',NULL,4);
INSERT INTO COURSE(CNO,CNAME,CPNO,CCREDIT) VALUES(6,'数据处理',NULL,2);
INSERT INTO COURSE(CNO,CNAME,CPNO,CCREDIT) VALUES(7,'PASCAL语言',NULL,4);
UPDATE COURSE SET CPNO=5 WHERE CNO=1;
UPDATE COURSE SET CPNO=6 WHERE CNO=4;
UPDATE COURSE SET CPNO=7 WHERE CNO=5;
UPDATE COURSE SET CPNO=6 WHERE CNO=7;

/***************************************************
 为SC插入数据
****************************************************/
INSERT INTO SC(SNO,CNO,GRADE) VALUES('200215121',1,92);
INSERT INTO SC(SNO,CNO,GRADE) VALUES('200215121',2,85);
INSERT INTO SC(SNO,CNO,GRADE) VALUES('200215121',3,88);
INSERT INTO SC(SNO,CNO,GRADE) VALUES('200215122',2,90);
INSERT INTO SC(SNO,CNO,GRADE) VALUES('200215122',3,80);

/********************************************
 查询全体学生的学号和姓名
*********************************************/
SELECT SNO,SNAME FROM STUDENT;

/********************************************
 查询全体学生的学号,姓名和所在系
*********************************************/
SELECT SNO,SNAME,SDEPT FROM STUDENT

/********************************************
 查询全体学生的详细记录
*********************************************/
SELECT * FROM STUDENT;

/********************************************
 查询全体学生的姓名和出生年月,BIRTH为列名
*********************************************/
SELECT SNAME,2007-SAGE AS BIRTH FROM STUDENT;

 

/********************************************
 查询全体学生的姓名和出生年月和所在院系，要求
 用小写字母表示所有系名
*********************************************/
SELECT SNAME,'YEAR OF BIRTH:' ,2007-SAGE AS BIRTH,LOWER(SDEPT) AS SDEPT FROM STUDENT;

/********************************************
 查询全选修了课程的学生号
*********************************************/
SELECT SNO FROM SC;
/*或
SELECT DISTINCT SNO FROM SC;*/

/********************************************
 查询计算机科学系全体学生的名单
*********************************************/
SELECT  SNAME FROM STUDENT WHERE SDEPT='CS';

/********************************************
 查询所有年龄在20岁以下的学生名单及其年龄
*********************************************/
SELECT  SNAME,SAGE FROM STUDENT WHERE SAGE < 20;

/********************************************
 考试成绩有不几个的学生的学号
*********************************************/
SELECT  DISTINCT SNO FROM SC WHERE GRADE < 60;

/********************************************
 查询年龄在20-30之间的学生姓名、年龄、系
*********************************************/
SELECT  SNAME,SAGE,SDEPT FROM STUDENT WHERE SAGE BETWEEN 20 AND 30;

/********************************************
 查询计算机科学系、数学系和信息系的姓名和性别
*********************************************/
SELECT  SNAME,SSEX FROM STUDENT WHERE SDEPT IN ('CS','MA','IS');

/********************************************
 查询不是计算机科学系、数学系和信息系的姓名和性别
*********************************************/
SELECT  SNAME,SSEX FROM STUDENT WHERE SDEPT NOT IN ('CS','MA','IS');

/********************************************
 查询学号为200215121的学生的详细情况
*********************************************/
SELECT  * FROM STUDENT WHERE SNO LIKE '200215121';

/***********************************************
 查询所有姓刘的学生的姓名、学号和性别
************************************************/
SELECT SNAME,SNO,SSEX FROM STUDENT WHERE SNAME LIKE '刘%';

/***********************************************
 查询所有姓刘的且全名为3个字符的学生的姓名
************************************************/
SELECT SNAME,SNO,SSEX FROM STUDENT WHERE SNAME LIKE '刘__';

/***********************************************
 查询所有姓不姓刘的学生信息
************************************************/
SELECT * FROM STUDENT WHERE SNAME NOT LIKE '刘%';

/***********************************************
 查询没有先行课的课程
************************************************/
SELECT * FROM COURSE WHERE CPNO IS NULL;

/***********************************************
 查询计算机系年龄在20岁以下的学生姓名
************************************************/
SELECT SNAME FROM STUDENT WHERE SDEPT='CS' AND SAGE < 20;

/***********************************************
 查询选修了3号课程的学生的学号及其成绩，查询结果按
 分数的降序排列
************************************************/
SELECT SNO,GRADE FROM SC WHERE CNO=3 ORDER BY GRADE DESC;

/***********************************************
  查询学生总人数
************************************************/
SELECT COUNT(*) AS TOTAL FROM STUDENT;

/***********************************************
  查询选修了课程的学生人数
************************************************/
SELECT COUNT(DISTINCT SNO) AS TOTALSNO FROM SC;

/***********************************************
  计算1号课程的学生平均成绩
************************************************/
SELECT AVG(GRADE)AS GRADE FROM SC WHERE CNO = 1;

/***********************************************
  查询选修3号课程的学生的最高分数
************************************************/
SELECT MAX(GRADE)AS GRADE FROM SC WHERE CNO = 3;

/***********************************************
  查询学生200215121选修课的总学分数
************************************************/
SELECT SUM(CCREDIT)AS CCREDIT FROM SC,COURSE WHERE SNO = '200215121' AND SC.CNO =

COURSE.CNO;

/***************************************
 例31 求各个课程号及相应的选课人数
*****************************************/
SELECT COUNT(SNO) AS NUMBER,CNO FROM SC
  GROUP BY CNO

/*******************************************
 查询选修了2门以上的课程的学生学号
********************************************/
SELECT COUNT(CNO),SNO FROM SC
   GROUP BY SNO
   HAVING COUNT(CNO) > 2

/********************************************
 例33查询每个学生及其选修课程的情况
*********************************************/
SELECT STUDENT.*,SC.* FROM SC,STUDENT WHERE SC.SNO = STUDENT.SNO;

 

/********************************************
对例33用自然连接完成
*********************************************/
SELECT STUDENT.SNAME,STUDENT.SSEX,STUDENT.SAGE,STUDENT.SDEPT,SC.* FROM SC,STUDENT
       WHERE SC.SNO = STUDENT.SNO;

/*****************************************
 查询每一门课程的间接先修课
*****************************************/
SELECT SY.CNO,SX.CPNO FROM COURSE SX,COURSE SY
WHERE SX.CNO = SY.CPNO

/********************************************
对例33用左外连接连接完成,注意这里加OUT出错
*********************************************/
SELECT STUDENT.SNAME,STUDENT.SSEX,STUDENT.SAGE,STUDENT.SDEPT,SC.* FROM 
   STUDENT LEFT JOIN  SC ON(SC.SNO = STUDENT.SNO);

/********************************************
查询每个学生的学号、姓名、选修的课程名及成绩
*********************************************/
SELECT STUDENT.SNO,STUDENT.SNAME,COURSE.CNAME,COURSE.CCREDIT FROM SC,STUDENT,COURSE
   WHERE SC.CNO =COURSE.CNO AND SC.SNO = STUDENT.SNO;

/********************************************
  查询与刘晨在同一个系学习的学生
*********************************************/

SELECT SX.* FROM STUDENT SX
   WHERE SX.SDEPT IN
             ( SELECT SY.SDEPT FROM STUDENT SY
               WHERE SY.SNAME='刘晨'
             );
/************
   或者如下:
 
   SELECT SX.* FROM STUDENT SX,STUDENT SY
      WHERE SX.SDEPT = SY.SDEPT AND SY.SNAME = '刘晨';
  或者
   SELECT * FROM STUDENT SX WHERE EXISTS
        ( SELECT * FROM STUDENT SY WHERE SY.SDEPT = SX.SDEPT AND SY.SNAME='刘晨'); 
**************/

/********************************************
  查询选修了课程名为"信息系统"的学生学号和姓名
*********************************************/

SELECT SNO,SNAME FROM STUDENT
       WHERE SNO IN
          (
            SELECT SNO FROM SC
                WHERE  CNO IN
                   ( SELECT CNO FROM COURSE
                      WHERE CNAME = '信息系统'
                   )
           );
 
 
/********************************************
  找出每个学生超过他选修课程平均成绩的课程号
*********************************************/
SELECT SX.SNO,SX.CNO FROM SC SX WHERE
         SX.GRADE  >
         ( SELECT AVG(GRADE) FROM SC SY
              WHERE SX.SNO = SY.SNO
              GROUP BY SY.SNO
          );

/*****************************************************
  查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄
*******************************************************/
SELECT SNAME,SAGE FROM STUDENT
   WHERE SAGE < ANY
       (SELECT SAGE FROM STUDENT
           WHERE SDEPT='CS'
       )
      AND SDEPT<>'CS';

/*****************************************************
  查询其他系中比计算机科学系所有学生年龄小的学生姓名和年龄
*******************************************************/
SELECT SNAME,SAGE FROM STUDENT
   WHERE SAGE < ALL
       (SELECT SAGE FROM STUDENT
           WHERE SDEPT='CS'
       )
      AND SDEPT<>'CS';

/*****************************************************
  查询所有选修了1号课程的学生姓名
*******************************************************/
SELECT SNAME FROM STUDENT
   WHERE EXISTS 
         ( SELECT * FROM SC
           WHERE SC.SNO = STUDENT.SNO AND SC.CNO='1'
          );

/******************************************
 查询选修了全部课程的学生姓名
*******************************************/
SELECT SNAME FROM STUDENT WHERE NOT EXISTS
    ( SELECT * FROM COURSE WHERE NOT EXISTS
       ( SELECT * FROM SC WHERE STUDENT.SNO = SC.SNO AND COURSE.CNO = SC.CNO)
     );

/*****************************************************
 查询至少选修了学生200215122选修的全部课程的学生号码
*****************************************************/
SELECT DISTINCT SNO FROM SC X
    WHERE NOT EXISTS
       ( SELECT * FROM SC Y WHERE SNO='200215122' AND
             NOT EXISTS
             ( SELECT * FROM SC Z WHERE Z.SNO = X.SNO AND Y.CNO = Z.CNO
             )
        ); 
 

/**********************************************************
 从自身表中选择一条记录，修改某个字段再回插到自身表中
 这里的INSERT INTO 可以用来插入子查询
*********************************************************/
INSERT INTO COURSE(CNO,CNAME,CPNO,CCREDIT) SELECT '8',CNAME,CPNO,CCREDIT FROM  COURSE WHERE

CNO='2';

/********************************************
 建立信息系学生的视图,在SQL SERVER 2000中后面
不能跟逗号，并且CREATE VIEW必须是第一条语句
**********************************************/
CREATE VIEW VSTUDENT
AS
SELECT SNO,SNAME,SAGE,SSEX
FROM STUDENT WHERE SDEPT='CS'
WITH CHECK OPTION