sql语句复杂版(lnh25)

骑猪去兜风z1

已于 2023-04-07 19:03:52 修改

阅读量87

点赞数

文章标签： sql 数据库

于 2023-04-07 17:02:32 首次发布

本文链接：https://blog.csdn.net/qq_52385631/article/details/130016505

版权

数据准备

create table dep(
    id int primary key auto_increment,
    name varchar(20) 
);

create table emp(
    id int primary key auto_increment,
    name varchar(20),
    sex enum('male','female') not null default 'male',
    age int,
    dep_id int
);

#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'保洁')
;

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

多表查询

# 我们之前查询的表都是单表查询

# 查询kevin所在的部门名称
 select * from dep where id = ( select dep_id from emp where name='kevin');
多表查询的思路：
	1. 子查询
    		# 一条SQL的执行结果当成另外一条SQL语句的执行条件.
        	# 大白话：分布操作
    2. 连表查询
    	# 把多张表拼接成一张虚拟表(不是真实存在的表，临时在内存中保存)，然后按照单表查询思路查询
        # 如何拼接表？
        '''一下四个关键字都是用来连表的'''
        '''这里还可以给表明起别名... as  '''
        inner join  # 内连接----->要两张表的共有数据
        	select * from emp inner join dep on emp.dep_id=dep.id;
        	select * from emp as e inner join dep as d on e.dep_id=d.id;
            
        left  join  # 左连接------>以左表为基准，查询左表中所有的数据，右表没有的数据用NULL填充
        	select * from emp left join dep on emp.dep_id=dep.id;
        right join # 右连接------>以右表为基准，查询右表中所有的数据，左表没有的数据用NULL填充
        	select * from emp right join dep on emp.dep_id=dep.id;
        union      # 全连接------>连接两个SQL语句的结果.
        select * from emp left join dep on emp.dep_id=dep.id
        union
        select * from emp right join dep on emp.dep_id=dep.id;
 """
 	当一张表中存现字段冲突的时候，我们使用表名点字段名的方式解决.
 """

SQL文件

/*
 数据导入：
 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` (
  `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,
  `gender` char(1) NOT NULL,
  `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;

-- ----------------------------
--  Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

多表查询练习题

1、查询所有的课程的名称以及对应的任课老师姓名
2、查询平均成绩大于八十分的同学的姓名和平均成绩
3、查询没有报李平老师课的学生姓名
4、查询挂科超过两门(包括两门)的学生姓名和班级
'''可能有点难，自己做，能做几个做几个.'''

答案

"""
# 1、查询所有的课程的名称以及对应的任课老师姓名
# select * from course INNER JOIN teacher on course.teacher_id = teacher.tid

# 2、查询平均成绩大于八十分的同学的姓名和平均成绩 WHERE AVG( num ) > 80 AVG( num )
# SELECT sname,AVG(num) FROM score INNER JOIN student ON score.student_id = student.sid GROUP BY sname HAVING AVG(num) >80;

# 3、查询没有报李平老师课的学生姓名
# 3.1 得到李平老师教的课程id
SELECT cid from course INNER JOIN teacher on course.teacher_id = teacher.tid  WHERE tname = '李平老师';
# 3.2 获取没有报名的学生姓名
select student.sname from student where sname not in(select sname from student  inner join score on student.sid =score.student_id WHERE course_id NOT in (select cid from course inner join teacher on teacher_id = tid where tname = '李平老师'))

4.查询挂科超过两门(包括两门)的学生姓名和班级
4.1 第一步:先得到连续挂科两门的学生id
select student_id from score where num<60 HAVING (count(num<60)>=2);
4.2 第二步:得到名字和班级id
select sname,class_id from student where student.sid in (select student_id from score where num<60 HAVING (count(num<60)>=2));
4.3 第三步:得到班级名称:
select sname,caption from (select sname,class_id from student where student.sid in (select student_id from score where num<60 HAVING (count(num<60)>=2))) as b left join class on class.cid = b.class_id;

"""