数据库实践二：高级sql语句

华东师范大学软件学院上机实践报告

课程名称：数据库应用	年级：15级	上机实践成绩：
指导教师：金澈清	姓名：陈伟文
上机实践名称：高级sql语句实践	学号：10152510217	上机实践日期：2017/3/29
上机实践编号：No.2	组号：1-217	上机实践时间：4学时

一、目的

1. 深入理解SQL语言，熟练使用一些高级技术。
2. 掌握存储过程的使用方法
3. 掌握触发器的使用方法

二、内容与设计思想

所涉及到的库表结构

总共三张表，即学生表，课程表，选课表。

内容一：创建如下的存储过程

1. 输入课程序号，输出课程名称
2. 输入学生学号，输出该学生的选课的数量
3. 增加一门课程信息，输入是课程代码、课程名称
4. 输出恰巧选择三门课程的学生名单
5. 输出选修人数最多的课程的代码和名称
6. 以游标技术，列举出所有学生的名单，包括学生姓名、选择的课程的数量。

内容二：创建如下的触发器

1. 加入一门新课程时，自动为所有学生选上该门课程
2. 更改一门课程的课程号时，自动修改相应的选课记录的课程号。
3. 删除一个学生时，自动删除该学生的选课记录
4. 删除一条选课记录的时候，如果对应的课程号没有人选了，则删除该课程号。
5. 增加一个学生记录时，察看“数据库”和“CPP”课程是否存在，如果存在的话则为该学生选择这两门课程。

三、使用环境

Windows XP Professional -> mysql

SQL Server 2005 ->DataGrip

四、实验过程

写出各条sql语句

内容一：创建如下的存储过程

#1.输入课程序号，输出课程名称
select cou_name
from courses
where cou_id='SOFT0031131105';

#2.输入学生学号，输出该学生的选课的数量
SELECT count(cou_id)
FROM stu_cou
WHERE stu_id = '10152510217';

#3.增加一门课程信息，输入是课程代码、课程名称
INSERT INTO courses
VALUES('PESE0031131411', '书法（隶书）')

#4.输出恰巧选择三门课程的学生名单
SELECT students.stu_name
FROM students, stu_cou
WHERE students.stu_id = stu_cou.stu_id
GROUP BY stu_cou.stu_id
HAVING count(stu_cou.cou_id) = 3;

#5.输出选修人数最多的课程的代码和名称
SELECT cou_id,cou_name
FROM courses NATURAL JOIN stu_cou
GROUP BY cou_id
HAVING count(*)>=ALL(
  SELECT count(*)
  FROM courses NATURAL JOIN stu_cou
  GROUP BY cou_id
);

#6.以游标技术，列举出所有学生的名单，包括学生姓名、选择的课程的数量。
CREATE PROCEDURE get_stus()
  BEGIN
    DECLARE no_more_stu int DEFAULT 0;
    DECLARE stud_id VARCHAR(20);
    DECLARE my_cursor CURSOR FOR SELECT stu_id FROM students;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_stu = 1;
    CREATE TEMPORARY TABLE info(
      stu_name VARCHAR(20),
      take_cnt INT
    );

    OPEN my_cursor;
    FETCH my_cursor INTO stud_id;
    REPEAT
      INSERT INTO info
      SELECT stu_name,count(cou_id)
        FROM stu_cou NATURAL JOIN students
        WHERE stu_id = stud_id;
      FETCH my_cursor INTO stud_id;
    UNTIL  no_more_stu = 1
    END REPEAT ;
    CLOSE my_cursor;
    SELECT *FROM info;
  END;

CALL get_stus();
DROP TABLE info;

内容二：创建如下的触发器

#触发器
#7.加入一门新课程时，自动为所有学生选上该门课程
DROP TRIGGER IF EXISTS auto_sel_cou;

CREATE TRIGGER auto_sel_cou
AFTER INSERT ON courses
FOR EACH ROW
BEGIN
    INSERT INTO stu_cou
    SELECT stu_id, NEW.cou_id, null
    FROM students;
END;

INSERT INTO courses VALUES ('SOFT0031121000','线性代数');

#8.更改一门课程的课程号时，自动修改相应的选课记录的课程号。
DROP TRIGGER auto_qudate_cou_id;
CREATE TRIGGER auto_qudate_cou_id AFTER UPDATE ON courses
  FOR EACH ROW
  BEGIN
    UPDATE stu_cou
      SET cou_id = NEW.cou_id
    WHERE cou_id = OLD.cou_id;
  END;

INSERT INTO stu_cou VALUES ('10152510217','     SOFT0031131130',NULL );
UPDATE courses
  SET cou_id = 'SOFT0031131130'
WHERE cou_id = '    SOFT0031131130';

#9.删除一个学生时，自动删除该学生的选课记录
CREATE TRIGGER auto_del_taken AFTER DELETE ON students
  FOR EACH ROW
  BEGIN
    DELETE FROM stu_cou
      WHERE stu_cou.stu_id = OLD.stu_id;
  END;

INSERT INTO students VALUES ('111','111',NULL );
INSERT INTO stu_cou VALUES ('111','SOFT0031131130',NULL );
DELETE FROM students WHERE stu_id = '111';

#10.删除一条选课记录的时候，如果对应的课程号没有人选了，则删除该课程号。
DROP TRIGGER auto_del_cou;
CREATE TRIGGER auto_del_cou AFTER DELETE ON stu_cou
  FOR EACH ROW
  BEGIN
    DELETE FROM courses
    WHERE courses.cou_id = OLD.cou_id
      AND 1 > (SELECT count(*)
           FROM stu_cou
           WHERE stu_cou.cou_id = OLD.cou_id);
  END;

INSERT INTO courses VALUES ('11','11');
INSERT INTO stu_cou VALUES ('11','11',NULL );
DELETE FROM stu_cou WHERE cou_id = '11';
SELECT count(*) FROM stu_cou WHERE cou_id = '11';

#11.增加一个学生记录时，察看“数据库”和“CPP”课程是否存在，如果存在的话则为该学生选择这两门课程。
DROP TRIGGER auto_add_db_cpp  ;
CREATE TRIGGER auto_add_db_cpp AFTER INSERT ON students
  FOR EACH ROW
  BEGIN
    INSERT INTO stu_cou
      SELECT NEW.stu_id, cou_id, NULL
        FROM courses
          WHERE cou_name = '数据库' or cou_name = 'CPP';
  END;

UPDATE courses SET cou_name = '数据库' WHERE cou_name = 'DataBase';
INSERT INTO courses VALUES ('SOFT0031131010','CPP');
INSERT INTO students VALUES ('11','11',NULL );
DELETE FROM students WHERE stu_id = '11';
DELETE FROM stu_cou WHERE cou_id = '11';

五、总结

对上机实践结果进行分析，问题回答，上机的心得体会及改进意见。

游标不会写，抄的同学的，感觉本来可以一个select解决的东西，而且就算以后真的会用到循环，肯定是用其他语言for循环（以前也是这么干的），很稳，mysql感觉稳啊

中途自己在另一台windows服务器装了sql server的数据库，经过测试发现比mysql的query速度慢了不少，再加上mysql简单，就。。。嘻嘻

六、附录

目前数据库样子