DBS-Project4-数据库编程

###实验目的

• 掌握MySQL数据库函数和存储过程的定义、调用方法
• 巩固MySQL数据库触发器定义和使用方法
• 熟悉JDBC连接访问数据库的方法
• 了解MySQL备份和恢复方法

实验平台

• 数据库管理系统（MySQL）

• pymysql

实验内容和要求

1、建立数据库，并录入数据

该部分内容延用之前的代码，不赘述。

2、函数、存储过程的创建和调用：

• 使用函数实现统计学生选修课程数量的功能。

  • 代码

    delimiter $$
    CREATE FUNCTION my_count(NAME VARCHAR(20)) RETURNS INT
    DETERMINISTIC
    BEGIN
    	DECLARE str VARCHAR(5);
    	DECLARE a INT;
    	SELECT SID FROM S WHERE SNAME=NAME INTO str;
    	SELECT COUNT(CID) FROM SC WHERE SID = str INTO a;
    	RETURN a;
    END
    $$
    delimiter;
    SELECT my_count("Wang feng");

  • 结果

    

• 使用存储过程实现查询某个学生选修的课程的总学分。

  • 代码

    delimiter $$
    CREATE PROCEDURE my_count(IN NAME VARCHAR(20), OUT my_sum INT)
    DETERMINISTIC
    BEGIN
    	DECLARE str VARCHAR(5);
    	SELECT SID FROM S WHERE SNAME=NAME INTO str;
    	SELECT sum(credit) FROM C WHERE CID in (SELECT CID FROM SC WHERE SID = str) INTO my_sum;
    END
    $$
    delimiter;
    CALL my_count("Wang feng",@credits);
    SELECT (@credits);

  • 结果

####3、触发器的创建

• 删除一个学生信息，确保SC中也不存在该学生的信息；

  SET foreign_key_checks = 0;
  delimiter $$
  CREATE TRIGGER s_delete AFTER DELETE ON s FOR EACH ROW
  BEGIN
  	DELETE FROM SC WHERE SID not in (SELECT SID FROM S);
  END
  $$
  delimiter;

• 添加选课信息时，确保课程存在。

delimiter $$
CREATE TRIGGER course_insert_check before insert
ON sc FOR EACH ROW
begin
	 declare msg varchar(100);
     if NEW.CID not in (select CID from C)
     THEN
         SET msg = CONCAT('illegal messege');
		SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg;
     end if;
end
$$
delimiter ;

4、使用python实现数据库连接访问

• 代码

  import pymysql 
  
  # select all avaliable courses
  def show_courses(cursor):
      print('show all courses')
      cursor.execute('select distinct CNAME from C')
      output = cursor.fetchall()
      print(output)
  
  # check a course
  def check_course(cursor,sid):
      print('select physics course for' +sid)
      sql = "select * from C where CID not in(select CID from SC where SID = %s);"
      cursor.execute(sql,sid)
      output = cursor.fetchall()
      print(output)
      conn.commit()
  
  #select a course
  def select_course(cursor,sid,cid):
      print('select %s course for %s'%(cid,sid))
      cursor.execute('insert into SC (SID, CID, GRADE) values(%s, %s, %s)', ['S3', 'C2', 0])
      output = cursor.fetchall()
      print(output)
      conn.commit()
  
  # select all courses that have been selected
  def show_selected_courses(cursor):
      print('show all courses that have been selected')
      cursor.execute('select distinct CNAME from SC natural join C')
      output = cursor.fetchall()
      print(output)
  
  # select all courses that have been selected by someone
  def show_selected_courses_by_someone(cursor,sid):
      print('show all courses that have been selected by %s'%(sid))
      cursor.execute('select distinct CNAME from SC natural join C where SID = %s', sid)
      output = cursor.fetchall()
      print(output)
  
  # drop out of a course
  def quit_course(cursor,sid,cid):
      print('drop out of the %s course for %s'%(cid,sid))
      sql = "delete from SC where SID =%s and CID = %s;"
      cursor.execute(sql,(sid,cid))
      output = cursor.fetchall()
      print(output)
  
  
  # connect database
  conn = pymysql.connect(host = '127.0.0.1', port = 3306, user = 'root', passwd = '123', db = 'school')
  cursor = conn.cursor()
  
  show_courses(cursor)
  select_course(cursor,'S2','C2')
  show_selected_courses(cursor)
  quit_course(cursor,'S2','C2')
  show_selected_courses_by_someone(cursor,'S1')

• 结果