/*
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
