mysql多表查询实训_MySql-多表查询练习数据准备excel中向下取整函数用法
/*
数据导⼊：
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 50624
Source Host : localhost
Source Database : sqlexam
Target Server Type : MySQL
Target Server Version : 50624
File Encoding : utf-8
Date: 10/21/2016 06:46:46 AM
*/
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
mysql语句多表查询`cid` int(11) NOT NULL AUTO_INCREMENT,
`caption` varchar(32) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年⼆班'), ('2', '三年三班'), ('3', '⼀年⼆班'), ('4', '⼆年九班');
COMMIT;
-- ----------------------------
-- Table structure for `course`
-
- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(32) NOT NULL,
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`cid`),
KEY `fk_course_teacher` (`teacher_id`),
CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '⽣物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2'); COMMIT;
-- ----------------------------
-- 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,
`num` int(11) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_score_student` (`student_id`),
KEY `fk_score_course` (`course_id`),
CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`), CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2',
'3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2',
'100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8',
'1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9',
'4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;
-- ----------------------------
-- Table structure for `student`
-- ----------------------------
小程序开发制作公司兴田德润DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
vlookup两个表精确匹配`gender` char(1) NOT NULL,make pairs什么意思
`class_id` int(11) NOT NULL,
`sname` varchar(32) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_class` (`class_id`),
CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
-- ----------------------------
-
- Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '⼥', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张⼀'), ('5', '⼥', '1', '张⼆'), ('6', '男', '1', '张四'), ('7', '⼥', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李⼀'), ('10', '⼥', '2', '李⼆'), ('11', '男', '2', '李四'), ('12', '⼥', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘⼀'), ('15', '⼥', '3', '刘⼆'), ('16', '男', '3', '刘四');
COMMIT;
-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(32) NOT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
strlwr函数-- Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊⽼师'), ('2', '李平⽼师'), ('3', '刘海燕⽼师'), ('4', '朱云海⽼师'), ('5', '李杰⽼师');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
表结构如下:
题⽬:
1、查询所有的课程的名称以及对应的任课⽼师姓名
select cname,tname from course left join teacher on teacher_id = tid;
2、查询学⽣表中男⼥⽣各有多少⼈
select gender,count(*) num from student group by gender;
3、查询物理成绩等于100的学⽣的姓名
select sname from score join course join student on course_id = cid and student_id = student.sid where cname = '物理' and num = 100;
4、查询平均成绩⼤于⼋⼗分的同学的姓名和平均成绩
select sname,avg_num from student join
(select student_id, avg(num) avg_num from score group by student_id having avg(num) > 80) as t2 on sid = t2.student_id;
5、查询所有学⽣的学号，姓名，选课数，总成绩
select student.sid,sname,course_num,total_mark from student join
(select student_id,count(*) course_num,sum(num) total_mark from score group by student_id) as t on sid = student_id;
6、 查询姓李⽼师的个数
select count(*) num from teacher where tname like '李%';
7、 查询没有报李平⽼师课的学⽣姓名
select sname from student where sid not in
(select student_id from score where course_id in
(select cid from course join teacher on tid = teacher_id where tname = '李平⽼师'));
8、 查询物理课程⽐⽣物课程⾼的学⽣的学号
select w.student_id from
(select student_id,num from score where course_id =
(select cid from course where cname = '物理'))as w
join
(select student_id,num from score where course_id =
(select cid from course where cname = '⽣物')) as s
on w.student_id = s.student_id where w.num > s.num;
9、 查询没有同时选修物理课程和体育课程的学⽣姓名
select sname from student where sid not in
(select w.student_id from
(select student_id from score where course_id =
(select cid from course where cname = '物理')) as w
join
(select student_id from score where course_id =
(select cid from course where cname = '体育')) as s
on w.student_id = s.student_id);
10、查询挂科超过两门(包括两门)的学⽣姓名和班级
select sname,caption from student join
(select student_id from score where num < 60 group by student_id having count(*) >=2) as s
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论