05.5 查询练习3

-- 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_nummax(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显示该生选修了哪几门课，老师是谁，电话多少
-- 结果示例： 
-- 课程编号 课程名字 老师名字 老师电话 课程描述
-- 2MySQL数据库   李老师    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_nameclass_namestudent_nostudent_namestudent_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());

 

 

 

 

转载于:https://www.cnblogs.com/bchen/p/7426292.html