时间：2021-07-01 10:21:17 帮助过：14人阅读
/* Navicat MySQL Data Transfer Source Server : localhost Source Server Version : 50621 Source Host : localhost:3306 Source Database : cmz Target Server Type : MYSQL Target Server Version : 50621 File Encoding : 65001 Date: 2018-03-17 21:20:45 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for `class` -- ---------------------------- DROP TABLE IF EXISTS `class`; CREATE TABLE `class` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `caption` char(10) NOT NULL, `grade_id` int(11) NOT NULL, PRIMARY KEY (`cid`), KEY `grade_id` (`grade_id`), CONSTRAINT `class_ibfk_1` FOREIGN KEY (`grade_id`) REFERENCES `class_grade` (`gid`) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of class -- ---------------------------- INSERT INTO `class` VALUES (‘1‘, ‘一年一班‘, ‘1‘); INSERT INTO `class` VALUES (‘2‘, ‘一年二班‘, ‘1‘); INSERT INTO `class` VALUES (‘3‘, ‘一年三班‘, ‘1‘); INSERT INTO `class` VALUES (‘4‘, ‘一年四班‘, ‘1‘); INSERT INTO `class` VALUES (‘5‘, ‘一年五班‘, ‘1‘); INSERT INTO `class` VALUES (‘6‘, ‘二年一班‘, ‘2‘); INSERT INTO `class` VALUES (‘7‘, ‘二年二班‘, ‘2‘); INSERT INTO `class` VALUES (‘8‘, ‘二年三班‘, ‘2‘); INSERT INTO `class` VALUES (‘9‘, ‘二年四班‘, ‘2‘); INSERT INTO `class` VALUES (‘10‘, ‘三年一班‘, ‘3‘); INSERT INTO `class` VALUES (‘11‘, ‘三年二班‘, ‘3‘); -- ---------------------------- -- Table structure for `class_grade` -- ---------------------------- DROP TABLE IF EXISTS `class_grade`; CREATE TABLE `class_grade` ( `gid` int(11) NOT NULL AUTO_INCREMENT, `gname` char(10) NOT NULL, PRIMARY KEY (`gid`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of class_grade -- ---------------------------- INSERT INTO `class_grade` VALUES (‘1‘, ‘一年级‘); INSERT INTO `class_grade` VALUES (‘2‘, ‘二年级‘); INSERT INTO `class_grade` VALUES (‘3‘, ‘三年级‘); -- ---------------------------- -- Table structure for `course` -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `cname` char(10) NOT NULL, `teacher_id` int(11) NOT NULL, PRIMARY KEY (`cid`), KEY `teacher_id` (`teacher_id`), CONSTRAINT `course_ibfk_1` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of course -- ---------------------------- INSERT INTO `course` VALUES (‘1‘, ‘生物‘, ‘1‘); INSERT INTO `course` VALUES (‘2‘, ‘体育‘, ‘1‘); INSERT INTO `course` VALUES (‘3‘, ‘物理‘, ‘2‘); INSERT INTO `course` VALUES (‘4‘, ‘数学‘, ‘1‘); INSERT INTO `course` VALUES (‘5‘, ‘美术‘, ‘3‘); -- ---------------------------- -- Table structure for `score` -- ---------------------------- DROP TABLE IF EXISTS `score`; CREATE TABLE `score` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `student_id` int(11) NOT NULL, `course_id` int(11) NOT NULL, `score` int(10) unsigned NOT NULL, PRIMARY KEY (`sid`), UNIQUE KEY `sid` (`sid`), KEY `student_id` (`student_id`), KEY `course_id` (`course_id`), CONSTRAINT `score_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`), CONSTRAINT `score_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of score -- ---------------------------- INSERT INTO `score` VALUES (‘5‘, ‘1‘, ‘3‘, ‘80‘); INSERT INTO `score` VALUES (‘8‘, ‘4‘, ‘3‘, ‘70‘); INSERT INTO `score` VALUES (‘9‘, ‘5‘, ‘3‘, ‘50‘); -- ---------------------------- -- Table structure for `student` -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `sname` char(10) NOT NULL, `gender` enum(‘男‘,‘女‘) DEFAULT NULL, `class_id` int(11) NOT NULL, PRIMARY KEY (`sid`), KEY `class_id` (`class_id`), CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES (‘1‘, ‘乔丹‘, ‘女‘, ‘1‘); INSERT INTO `student` VALUES (‘2‘, ‘艾森‘, ‘女‘, ‘2‘); INSERT INTO `student` VALUES (‘3‘, ‘科比‘, ‘男‘, ‘3‘); INSERT INTO `student` VALUES (‘4‘, ‘黄家‘, ‘女‘, ‘1‘); INSERT INTO `student` VALUES (‘5‘, ‘胡启‘, ‘男‘, ‘1‘); INSERT INTO `student` VALUES (‘6‘, ‘吴宇‘, ‘男‘, ‘2‘); INSERT INTO `student` VALUES (‘7‘, ‘唐明‘, ‘女‘, ‘3‘); INSERT INTO `student` VALUES (‘8‘, ‘叶葱‘, ‘男‘, ‘2‘); INSERT INTO `student` VALUES (‘9‘, ‘张淳‘, ‘女‘, ‘2‘); INSERT INTO `student` VALUES (‘10‘, ‘映月‘, ‘男‘, ‘1‘); INSERT INTO `student` VALUES (‘11‘, ‘胡启‘, ‘男‘, ‘1‘); INSERT INTO `student` VALUES (‘12‘, ‘天宇‘, ‘女‘, ‘2‘); INSERT INTO `student` VALUES (‘13‘, ‘孙兰‘, ‘男‘, ‘3‘); INSERT INTO `student` VALUES (‘14‘, ‘张笑‘, ‘男‘, ‘3‘); INSERT INTO `student` VALUES (‘15‘, ‘张兰‘, ‘女‘, ‘1‘); INSERT INTO `student` VALUES (‘16‘, ‘张兰‘, ‘女‘, ‘2‘); INSERT INTO `student` VALUES (‘17‘, ‘张兰‘, ‘女‘, ‘3‘); -- ---------------------------- -- Table structure for `teacher` -- ---------------------------- DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` ( `tid` int(11) NOT NULL AUTO_INCREMENT, `tname` char(10) NOT NULL, PRIMARY KEY (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of teacher -- ---------------------------- INSERT INTO `teacher` VALUES (‘1‘, ‘张三‘); INSERT INTO `teacher` VALUES (‘2‘, ‘李四‘); INSERT INTO `teacher` VALUES (‘3‘, ‘王五‘); INSERT INTO `teacher` VALUES (‘4‘, ‘李坤‘); -- ---------------------------- -- Table structure for `teacher2cls` -- ---------------------------- DROP TABLE IF EXISTS `teacher2cls`; CREATE TABLE `teacher2cls` ( `tcid` int(11) NOT NULL AUTO_INCREMENT, `tid` int(11) NOT NULL, `cid` int(11) NOT NULL, PRIMARY KEY (`tcid`), UNIQUE KEY `tcid` (`tcid`), KEY `tid` (`tid`), KEY `cid` (`cid`), CONSTRAINT `teacher2cls_ibfk_1` FOREIGN KEY (`tid`) REFERENCES `teacher` (`tid`), CONSTRAINT `teacher2cls_ibfk_2` FOREIGN KEY (`cid`) REFERENCES `course` (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of teacher2cls -- ---------------------------- INSERT INTO `teacher2cls` VALUES (‘1‘, ‘1‘, ‘1‘); INSERT INTO `teacher2cls` VALUES (‘2‘, ‘1‘, ‘2‘); INSERT INTO `teacher2cls` VALUES (‘3‘, ‘2‘, ‘1‘); INSERT INTO `teacher2cls` VALUES (‘4‘, ‘3‘, ‘2‘); -- ---------------------------- -- Procedure structure for `p1` -- ---------------------------- DROP PROCEDURE IF EXISTS `p1`; DELIMITER ;; CREATE DEFINER=`cmz`@`localhost` PROCEDURE `p1`() BEGIN select * from score; END ;; DELIMITER ; -- ---------------------------- -- Procedure structure for `p2` -- ---------------------------- DROP PROCEDURE IF EXISTS `p2`; DELIMITER ;; CREATE DEFINER=`cmz`@`localhost` PROCEDURE `p2`(in n1 int,in n2 int,out res int) BEGIN select * from score where course_id=n1 and score >n2 ; set res = 1; END ;; DELIMITER ;完整sql
#1.------------------------- create database ------------------------- #1. create database and change this database create database cmz charset utf8; use cmz; #2.------------------------- create table ------------------------- #2. create table #1. teacher table create table teacher( tid int not null primary key auto_increment, tname char(10) not null )charset utf8 engine=innodb; #2. class_grade table create table class_grade( gid int not null primary key auto_increment, gname char(10) not null )charset utf8 engine=innodb; #3. course table create table course( cid int not null primary key auto_increment, cname char(10) not null, teacher_id int not null, foreign key(teacher_id) references teacher(tid) )charset utf8 engine=innodb; #4. class table create table class( cid int not null primary key auto_increment, caption char(10) not null, grade_id int not null, foreign key(grade_id) references class_grade(gid) )charset utf8 engine=innodb; #5. student table create table student( sid int not null primary key auto_increment, sname char(10) not null, gender enum(‘男‘,‘女‘), class_id int not null, foreign key(class_id) references class(cid) )charset utf8 engine=innodb; #6. score table create table score( sid int not null unique auto_increment, student_id int not null, course_id int not null, score int unsigned not null, foreign key(student_id) references student(sid), foreign key(course_id) references course(cid), primary key(sid) )charset utf8 engine=innodb; #7. teacher2cls table create table teacher2cls( tcid int not null unique auto_increment, tid int not null, cid int not null, foreign key(tid) references teacher(tid), foreign key(cid) references course(cid), primary key(tcid) )charset utf8 engine=innodb; #3. ------------------------- insert into table ------------------------- # insert every table data #1. teacher table insert into teacher(tname) values ("张三"), ("李四"), ("王五"); #2. class_grade table data insert into class_grade(gname) values ("一年级"), ("二年级"), ("三年级"); #3. course table data insert into course(cname,teacher_id) values ("生物",1), ("体育",1), ("数学",1), ("物理",2); #4. class table data insert into class(caption,grade_id) values ("一年一班",1), ("一年二班",1), ("一年三班",1), ("一年四班",1), ("一年五班",1), ("二年一班",2), ("二年二班",2), ("二年三班",2), ("二年四班",2), ("三年一班",3), ("三年二班",3); #5. student table data insert into student(sname,gender,class_id) values ("乔丹","女",1), ("艾森","女",2), ("科比","男",3), ("黄家","女",1), ("胡启","男",1), ("吴宇","男",2), ("唐明","女",3), ("叶葱","男",2), ("张淳","女",2), ("映月","男",1), ("胡启","男",1), ("天宇","女",2), ("孙兰","男",3); #6. score table data insert into score(student_id,course_id,score) values (1,1,60), (1,2,59), (2,2,59); #7. teacher2cls table data insert into teacher2cls(tid,cid) values (1,1), (1,2), (2,1), (3,2); # sql for operation mysql #1. 创建测试数据 #2. 查询学生总人数 select COUNT(sid) from student; 运行过程 mysql> select COUNT(sid) from student; +------------+ | COUNT(sid) | +------------+ | 17 | +------------+ 1 row in set (0.00 sec) #3. 查询“生物”课程和“物理”课程都及格的学生id和姓名 select s1.sid,s1.sname from student s1 where s1.sid in ( select distinct(s.student_id) from score s left join course c on c.cid=s.course_id where s.score >= 60 and (c.cname=‘生物‘ or c.cname=‘物理‘)); 运行过程 mysql> select s1.sid,s1.sname from student s1 -> where s1.sid in ( -> select distinct(s.student_id) from score s left join course c on c.cid=s.course_id -> where s.score >= 60 and (c.cname=‘生物‘ or c.cname=‘物理‘)); +-----+-------+ | sid | sname | +-----+-------+ | 1 | 乔丹 | | 4 | 黄家 | +-----+-------+ 2 rows in set (0.00 sec) #4. 查询每个年级的班级数，取出班级数最多的前三个年级 select grade_id from class_grade LEFT JOIN class on class_grade.gid = class.grade_id GROUP BY grade_id ORDER BY count(grade_id) asc LIMIT 3; 运行过程 mysql> select grade_id from class_grade LEFT JOIN class on class_grade.gid = class.grade_id -> GROUP BY grade_id ORDER BY count(grade_id) asc LIMIT 3; +----------+ | grade_id | +----------+ | 3 | | 2 | | 1 | +----------+ 3 rows in set (0.00 sec) #5. 查询 平均成绩最高和最低的学生的id和姓名以及平均成绩 SELECT student.sid,student.sname,avg(score) from student RIGHT JOIN score on student.sid = score.student_id GROUP BY score ORDER BY avg(score) desc LIMIT 1; SELECT student.sid,student.sname,avg(score) from student RIGHT JOIN score on student.sid = score.student_id GROUP BY score ORDER BY avg(score) asc LIMIT 1; 运行过程 mysql> SELECT student.sid,student.sname,avg(score) from student RIGHT JOIN score on student.sid = sc ore.student_id -> GROUP BY score ORDER BY avg(score) desc LIMIT 1; +------+-------+------------+ | sid | sname | avg(score) | +------+-------+------------+ | 1 | 乔丹 | 80.0000 | +------+-------+------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT student.sid,student.sname,avg(score) from student RIGHT JOIN score on student.sid = sc ore.student_id -> GROUP BY score ORDER BY avg(score) asc LIMIT 1; +------+-------+------------+ | sid | sname | avg(score) | +------+-------+------------+ | 5 | 胡启 | 50.0000 | +------+-------+------------+ 1 row in set (0.00 sec) #6. 查询每个年级的学生人数 select count(*) from class group by grade_id; 运行过程 mysql> select count(*) from class group by grade_id; +----------+ | count(*) | +----------+ | 5 | | 4 | | 2 | +----------+ 3 rows in set (0.00 sec) #7. 查询每个学生的学号，姓名，选课数，平均成绩 SELECT s.sid,s.sname,count(course_id),avg(score) from student s RIGHT JOIN score sc on s.sid = sc.sid GROUP BY student_id; 运行过程 mysql> SELECT s.sid,s.sname,count(course_id),avg(score) from student s RIGHT JOIN score sc on s.sid= sc.sid -> GROUP BY student_id; +------+-------+------------------+------------+ | sid | sname | count(course_id) | avg(score) | +------+-------+------------------+------------+ | 5 | 胡启 | 1 | 80.0000 | | 8 | 叶葱 | 1 | 70.0000 | | 9 | 张淳 | 1 | 50.0000 | +------+-------+------------------+------------+ 3 rows in set (0.00 sec) #8. 查询学生编号为2的学生的姓名，该学生的成绩最高的课程名，成绩最低的课程名以及分数 SELECT course.cname,score.score from score LEFT JOIN course on score.course_id = course.cid where score.student_id = 2 ORDER BY score.score desc LIMIT 1; SELECT course.cname,score.score from score LEFT JOIN course on score.course_id = course.cid where score.student_id = 2 ORDER BY score.score asc LIMIT 1; 运行过程 mysql> SELECT course.cname,score.score from score LEFT JOIN course on score.course_id = course.cid -> where score.student_id = 2 ORDER BY score.score desc LIMIT 1; Empty set (0.00 sec) mysql> SELECT course.cname,score.score from score LEFT JOIN course on score.course_id = course.cid -> where score.student_id = 2 ORDER BY score.score asc LIMIT 1; Empty set (0.00 sec) #9. 查询姓“李”的老师的个数和所带班级数 select (select tea.tid from teacher tea where tea.tname like ‘张%‘) as 姓李的人数 , count(tea2.tid) as 课程 from teacher2cls tea2 where tea2.tid in (select tea.tid from teacher tea where tea.tname like ‘张%‘ ) group by tea2.tid; 运行过程 mysql> select (select tea.tid from teacher tea where tea.tname like ‘张%‘) as 姓李的人数 , -> count(tea2.tid) as 课程 from teacher2cls tea2 -> where tea2.tid in (select tea.tid from teacher tea where tea.tname like ‘张%‘ ) group by tea2.tid; +------------+------+ | 姓李的人数 | 课程 | +------------+------+ | 1 | 2 | +------------+------+ 1 row in set (0.00 sec) #10.查询班级数小于5的年级id和年级名 select gid,gname from class_grade where gid in (select grade_id from class GROUP BY grade_id HAVING count(class.grade_id)<5); 运行过程 mysql> select gid,gname from class_grade -> where gid in -> (select grade_id from class GROUP BY grade_id HAVING count(class.grade_id)<5); +-----+--------+ | gid | gname | +-----+--------+ | 2 | 二年级 | | 3 | 三年级 | +-----+--------+ 2 rows
