MySQL笔记： 查询练习2

-- 1.1 给定一个学生 2012001，查询该生选修了几门课。
--   返回 学号  选修了几门课
SELECT student_no AS '学号',COUNT(*) AS '选修课程数量' FROM choose WHERE student_no = 2012001;

-- 1.2 显示所有学生名字，选修了几门课。(姓名，几门)
-- student_name          course_num
--  张三        2
--  李四        2
--  ...

-- 内连接写法1
SELECT student.student_name,COUNT(*) FROM choose,student WHERE choose.student_no = student.student_no GROUP BY student.student_no;
-- 内连接写法2
SELECT student.student_name,COUNT(*) FROM choose INNER JOIN student ON choose.student_no = student.student_no GROUP BY choose.student_no;

-- 1.3. 统计每个学生选修多少课程,最高分、最低分、总分、平均成绩
-- student_name          course_num    max(score)    min(score) sum(score) avg(score)
--  张三        2    
--  李四        2
-- ...
SELECT student.student_name,COUNT(*) AS course_num,MAX(score),MIN(score),SUM(score),AVG(score) 
FROM choose,student 
WHERE choose.student_no = student.student_no 
GROUP BY student.student_no;

-- 语法
-- select 字段
-- from 数据源
-- where 条件表达式
-- group by 分组字段 having 条件表达式

-- 1.4 检索平均成绩高于70分的学生信息及平均成绩  分组的或者聚合函数的条件用having 关键字 不是where
-- student_name  avg(score)
-- 王五        80.0
-- 马六        90.0
SELECT student.student_name,AVG(score) 
FROM student,choose 
WHERE choose.student_no = student.student_no 
GROUP BY student.student_no 
HAVING AVG(score) < 70;

-- 1.5 检索显示学生的所有选修课程的成绩，条件是成绩要比张三的平均成绩高的才显示. (使用子查询)
-- 结果字段:
-- class_name student_no student_name courese_name score
SELECT class_name,student.student_no,student.student_name,course_name,score 
FROM course,choose,student,classes 
WHERE choose.student_no = student.student_no 
AND choose.course_no = course.course_no 
AND student.class_no = classes.class_no 
AND score > 
(SELECT AVG(score) FROM choose,student WHERE choose.student_no = student.student_no AND student_name = '张三');

-- 2. 给定一个学生如2012001显示该生选修了哪几门课，老师是谁，电话多少
-- 结果示例： 
-- 课程编号 课程名字 老师名字 老师电话 课程描述
-- 2    MySQL数据库   李老师    12000000  暂无
-- 1    Java语言程序设计 张老师 10000000  暂无
SELECT choose.course_no AS '课程编号',course.course_name AS '课程名字',teacher_name AS '老师名字',
teacher_contact AS '老师电话',description AS '课程描述' 
FROM teacher,choose,course 
WHERE teacher.teacher_no = course.teacher_no 
AND course.course_no = choose.course_no 
AND choose.student_no = 2012001;

-- 3. 给定一门课程如course_no = 1,统计哪些学生选修了这门课，结果排序先按院系再按班级再按学号
-- 结果字段:
-- 院系            班级        学号        姓名        电话
-- deaprtment_name    class_name    student_no    student_name    student_contact
SELECT classes.department_name AS '院系',class_name AS '班级',student.student_no '学号',
student_name AS '姓名',student_contact AS '电话' 
FROM choose,classes,student 
WHERE choose.course_no = 1 
AND student.student_no = choose.student_no 
AND student.class_no = classes.class_no 
ORDER BY classes.department_name;

SELECT classes.department_name AS '院系',class_name AS '班级',student.student_no '学号',
student_name AS '姓名',student_contact AS '电话' 
FROM choose,classes,student 
WHERE choose.course_no = 1 
AND student.student_no = choose.student_no 
AND student.class_no = classes.class_no 
ORDER BY classes.class_name;

SELECT classes.department_name AS '院系',class_name AS '班级',student.student_no '学号',
student_name AS '姓名',student_contact AS '电话' 
FROM choose,classes,student 
WHERE choose.course_no = 1 
AND student.student_no = choose.student_no 
AND student.class_no = classes.class_no 
ORDER BY student.student_no;

-- 4. 1检索所有学生、老师信息 使用union 合并结果集
-- 结果:
-- 编号        姓名    练习方式
-- 2012001     张三    150000000
-- 2012002     李四    150000000
-- ...
-- 001         张老师    110000000
-- ...
SELECT student_no AS '编号',student_name AS '姓名',student_contact AS '联系方式' FROM student
UNION
SELECT teacher_no AS '编号',teacher_name AS '姓名',teacher_contact AS '联系方式' FROM teacher;

-- 4.2统计哪些课程已报满。
SELECT * FROM course WHERE get_choose_number_fn(course_no)>= up_limit;
-- 不使用函数
SELECT * FROM course WHERE course_no IN 
(
SELECT choose.course_no FROM choose,course 
WHERE choose.course_no = course.course_no 
GROUP BY course.course_no 
HAVING COUNT(choose.course_no) >= course.up_limit
);

-- 函数：输入课程号，输出选课人数
DELIMITER $$
CREATE FUNCTION get_choose_number_fn(course_no1 VARCHAR(10)) RETURNS INT
READS SQL DATA
BEGIN
    DECLARE choose_number INT;
    SELECT COUNT(student_no) INTO choose_number FROM choose WHERE course_no = course_no1;
    RETURN choose_number;
END;
$$
DELIMITER ;
-- 查看该函数
SHOW CREATE FUNCTION get_choose_number_fn;
-- 执行该函数
SELECT get_choose_number_fn('1');

-- 4.3编写一个函数，在函数中使用循环插入60个学生
-- 提示: 字符串+数字 使用 contact('机器人',1)
-- 2013001 机器人1   2012自动化3班
-- ~
-- 2013001 机器人60  2012自动化3班
-- 让他们都选报 java语言程序设计这门课程
-- 验证测试4语句，是否显示 java语言程序设计这门课程 报满
DELIMITER $$
CREATE FUNCTION set_student() RETURNS BOOL
NO SQL
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 60 DO
        INSERT INTO student VALUES(2013000+i,CONCAT('机器人',i),NULL,3);
        INSERT INTO choose VALUES(NULL,2013000+i,1,NULL,NULL);
        SET i= i + 1;
    END WHILE;
    RETURN TRUE;
END;
$$
DELIMITER ;
-- 删除该函数
DROP FUNCTION set_student;
-- 执行该函数
SELECT set_student();
-- 注意：注释不能放在函数中！！！！！！！！

-- 5. 统计选修人数少于30人的所有课程信息(未考虑choose表中没有记录的课程)
SELECT * FROM course WHERE get_choose_number_fn(course_no) < 30;
SELECT * FROM course WHERE course_no IN (SELECT course_no FROM choose GROUP BY course_no HAVING COUNT(course_no) < 30);
SELECT course.course_no FROM course WHERE NOT EXISTS (SELECT DISTINCT choose.course_no FROM choose WHERE choose.course_no = course.course_no);
SELECT course.course_no FROM course WHERE course.course_no NOT IN (SELECT choose.course_no FROM choose);

-- 6. 统计一门课程已经有多少学生选修，还能提供多少学生选修
-- 字段
-- course_no course_name teacher_name up_limit student_num available
SELECT DISTINCT course.course_no,course_name,teacher_name,up_limit,get_choose_number_fn(course.course_no) AS 'student_num',
up_limit - get_choose_number_fn(course.course_no) AS 'available' 
FROM choose,course,teacher 
WHERE choose.course_no = course.course_no AND course.teacher_no = teacher.teacher_no;

-- 7. 将6定义为视图 available_course_view
CREATE VIEW available_course_view
AS
SELECT DISTINCT course.course_no,course_name,teacher_name,up_limit,get_choose_number_fn(course.course_no) AS 'student_num',
up_limit - get_choose_number_fn(course.course_no) AS 'available' 
FROM choose,course,teacher 
WHERE choose.course_no = course.course_no AND course.teacher_no = teacher.teacher_no;

SELECT * FROM available_course_view;

-- 8.作业:编写存储过程 
-- 8.1. 查询学生的成绩
-- 传入学生学号、课程编号 返回成绩
DELIMITER $$
CREATE PROCEDURE get_score_proc(IN tmp_student_no VARCHAR(10),IN tmp_course_no VARCHAR(10),OUT tmp_score INT)
READS SQL DATA
BEGIN
    SELECT score INTO tmp_score FROM choose WHERE student_no = tmp_student_no AND course_no = tmp_course_no;
END
$$
DELIMITER ;

DROP PROCEDURE get_score_proc;
-- 测试
SET @tmp_student_no = '2012002';
SET @tem_course_no = '3';
SET @tem_score = 0;
CALL get_score_proc(@tmp_student_no,@tem_course_no,@tem_score);
SELECT @tem_score; 

-- 8.2. 查询教师编号，返回所教授课程
DELIMITER $$
CREATE PROCEDURE get_course_name_proc(IN tmp_teacher_no VARCHAR(10),OUT tmp_course_name VARCHAR(30))
READS SQL DATA
BEGIN
    SELECT course_name INTO tmp_course_name FROM course WHERE tmp_teacher_no = course.teacher_no;
END
$$
DELIMITER ;
-- 测试
SET @tmp_teacher_no = '003';
SET @tmp_course_name = '0';
CALL get_course_name_proc(@tmp_teacher_no,@tmp_course_name);
SELECT @tmp_course_name; 

-- 8.3. 选课存储过程
-- 传入 学号、课程号 传出 状态
-- 用以下语句测试

-- 状态
-- 0 插入成功
-- -1 选课失败,已选择
-- -2 选课失败,课程已达到人数上限
-- -3 选课失败,课程未审核

-- 插入时要判断的条件:
-- 未选择过该课程，否则返回 -1
-- 课程是审核过的,否则返回 -3
-- 课程人数未满, 否则返回 -2
DROP PROCEDURE choose_proc;
DELIMITER $$
CREATE PROCEDURE choose_proc(IN tmp_student_no VARCHAR(10),IN tmp_course_no VARCHAR(10),OUT tmp_status INT)
READS SQL DATA
BEGIN
    DECLARE t1 INT;
    DECLARE t2 VARCHAR(6);
    DECLARE t3 INT;
    
    DECLARE CONTINUE HANDLER FOR 1452
    BEGIN
        SET @error_info = '外键约束';
    END;

    SELECT COUNT(*) INTO t1 FROM choose WHERE student_no = tmp_student_no AND course_no = tmp_course_no;
    IF (t1 >= 1) THEN
        SET tmp_status = -1;
    ELSE 
        SELECT course.status INTO t2 FROM course WHERE course_no = tmp_course_no;
        IF (t2 = '未审核') THEN
             SET tmp_status = -3;
        ELSE
            SELECT available INTO t3 FROM available_course_view WHERE course_no = tmp_course_no;
            IF (t3 <= 0) THEN
                SET tmp_status = -2;
            ELSE
                INSERT INTO choose VALUES(NULL,tmp_student_no,tmp_course_no,NULL,NOW()); 
                SET tmp_status = 0;
            END IF;
        END IF;
    END IF;
END
$$
DELIMITER ;

SET @state = 1;
CALL choose_proc('2012003',1,@state);
SELECT @state; 
SET @state = 0;
CALL choose_proc('2012003',2,@state);
SELECT @state; 
SET @state = 0;
CALL choose_proc('2012003',3,@state);
SELECT @state;
SET @state = 0;
CALL choose_proc('2012003',4,@state);
SELECT @state;

-- 测试错误
SET @state = 0;
SET @error_info = '';
CALL choose_proc('2016003',2,@state);
SELECT @state,@error_info;


-- 数据导入：
CREATE DATABASE choose;
USE choose;

DROP TABLE choose;
DROP TABLE student;
DROP TABLE course;
DROP TABLE classes;
DROP TABLE teacher;


CREATE TABLE teacher(
    teacher_no VARCHAR(10) PRIMARY KEY,
    teacher_name VARCHAR(30) NOT NULL,
    teacher_contact VARCHAR(30)
    ) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE classes(
    class_no VARCHAR(10) PRIMARY KEY,
    class_name VARCHAR(30) NOT NULL UNIQUE,
    department_name VARCHAR(30) NOT NULL
    ) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE course(
    course_no VARCHAR(10) PRIMARY KEY,
    course_name VARCHAR(30) NOT NULL,
    up_limit INT DEFAULT 60,
    description TEXT,
    STATUS VARCHAR(6) DEFAULT '未审核',
    teacher_no VARCHAR(10) NOT NULL UNIQUE,
    CONSTRAINT course_teacher_fk FOREIGN KEY(teacher_no) REFERENCES teacher(teacher_no)
    )ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE student(
    student_no VARCHAR(10) PRIMARY KEY,
    student_name VARCHAR(30) NOT NULL,
    student_contact VARCHAR(30),
    class_no VARCHAR(10),
    CONSTRAINT student_class_fk FOREIGN KEY(class_no) REFERENCES classes(class_no)
    )ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE choose(
    choose_no INT AUTO_INCREMENT PRIMARY KEY,
    student_no VARCHAR(10) NOT NULL,
    course_no VARCHAR(10) NOT NULL,
    score TINYINT UNSIGNED,
    choose_tim DATETIME,
    CONSTRAINT choose_student_fk FOREIGN KEY(student_no) REFERENCES student(student_no),
    CONSTRAINT choose_course_fk FOREIGN KEY(course_no) REFERENCES course(course_no)
    )ENGINE = INNODB DEFAULT CHARSET=utf8;
    
INSERT INTO teacher VALUES('001','张老师','11000000000');
INSERT INTO teacher VALUES('002','李老师','12000000000');
INSERT INTO teacher VALUES('003','王老师','13000000000');

INSERT INTO classes(class_no,class_name,department_name) VALUES('1','2012自动化1班', '机电工程');
INSERT INTO classes(class_no,class_name,department_name) VALUES('2','2012自动化2班', '机电工程');
INSERT INTO classes(class_no,class_name,department_name) VALUES('3','2012自动化3班', '机电工程');

INSERT INTO course VALUES('1','java语言程序设计',DEFAULT,'暂无','已审核','001');
INSERT INTO course VALUES('2','MySQL数据库',150,'暂无','已审核','002');
INSERT INTO course VALUES('3','c语言程序设计',230,'暂无','已审核','003');

INSERT INTO student VALUES
('2012001','张三','15000000000',1),
('2012002','李四','16000000000',1),
('2012003','王五','17000000000',3),
('2012004','马六','18000000000',2),
('2012005','田七','19000000000',2);

INSERT INTO choose VALUES
(NULL,'2012001',2,40,NOW()),
(NULL,'2012001',1,50,NOW()),
(NULL,'2012002',3,60,NOW()),
(NULL,'2012002',2,70,NOW()),
(NULL,'2012003',1,80,NOW()),
(NULL,'2012004',2,90,NOW()),
(NULL,'2012005',3,NULL,NOW()),
(NULL,'2012005',1,NULL,NOW());