首页 > 数据库 >MySQL多表查询语法

MySQL多表查询语法

时间：2022-11-28 18:46:07浏览次数：42  
标签：dep 多表 -- 语法 course student MySQL male id

MySQL多表查询语法

多表查询准备

数据准备

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),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

笛卡尔积

select * from emp,dep;
  • 运行得到下面的表格
    id name sex age dep_id id name
    1 jason male 18 200 200 技术
    1 jason male 18 200 201 人力资源
    1 jason male 18 200 202 销售
    1 jason male 18 200 203 运营
    1 jason male 18 200 205 财务
    2 dragon female 48 201 200 技术
    2 dragon female 48 201 201 人力资源
    2 dragon female 48 201 202 销售
    2 dragon female 48 201 203 运营
    2 dragon female 48 201 205 财务
    3 kevin male 18 201 200 技术
    3 kevin male 18 201 201 人力资源
    3 kevin male 18 201 202 销售
    3 kevin male 18 201 203 运营
    3 kevin male 18 201 205 财务
    4 nick male 28 202 200 技术
    4 nick male 28 202 201 人力资源
    4 nick male 28 202 202 销售
    4 nick male 28 202 203 运营
    4 nick male 28 202 205 财务
    5 owen male 18 203 200 技术
    5 owen male 18 203 201 人力资源
    5 owen male 18 203 202 销售
    5 owen male 18 203 203 运营
    5 owen male 18 203 205 财务
    6 jerry female 18 204 200 技术
    6 jerry female 18 204 201 人力资源
    6 jerry female 18 204 202 销售
    6 jerry female 18 204 203 运营
    6 jerry female 18 204 205 财务

可以看到这个表格十分的庞大，相当于将两表中所有的记录都两两组合起来

当然我们可以通过筛选条件where来让emp.dep_id与dep.id相匹配，但是这个筛选是在这个庞大的表的基础上得到的，如果表数据很多，那么这个笛卡尔集就是记录数量级平方的执行次数，十分容易导致崩溃。

所以我们还有其他两种拼接表的操作。

连表查询

  1. inner join 内连接

    只连接两张表中公有的数据部分

    select * from emp inner join dep on emp.dep_id = dep.id;

    image

  2. left join 左连接

    以左边的表为基准，左边的所有数据都会显示，如果右表中没有对应的连接字段，则显示null

    select * from emp left join dep on emp.dep_id = dep.id;

    image

  3. right join 右连接

    以右边的表为基准，右边的所有数据都会显示，如果左表中没有对应的连接字段，则显示null

    select * from emp right join dep on emp.dep_id = dep.id;

    image

  4. union 全连接

    将左连接表和右连接表的结果并起来。

    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;

    image

ps：union是通过将两个select的结果并集得到了一张表，而select语法就是会返回它的表结果，这个表结果还可以和别的表连接。

子查询

将一条SQL语句用括号括起来当成另外一条SQL语句的查询条件，这个条件常用于

  • 作为一个集合被拿来成员运算
# 将员工表中等于名字jason的dep_id作为查询结果与dep表中的id值做比对，最终拿dep.name字段
# 于是拿到jason所在部门的名字
select name from dep where id=(select dep_id from emp where name='jason');

image

多表查询实战

数据准备 
/*
 数据导入：
 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;

image

查询所有的课程的名称以及对应的任课老师姓名

分析：涉及两张有直接联系的表，直接使用连表查询即可。

SELECT
	course.cname,
	teacher.tname 
FROM
	course
	INNER JOIN teacher ON teacher_id = teacher.tid;
查询平均成绩大于七十分的同学的姓名和平均成绩

分析：学生表和成绩表也是直接联系的，直接用连表查询，后续可以按照姓名划分求平均分。

# 连表操作
SELECT
	sname,
	avg( num ) AS avg_num 
FROM
	student
	INNER JOIN score ON student.sid = score.student_id 
GROUP BY
	sname 
HAVING
	avg( num ) > 70;
查询没有报李平老师课的学生名

分析：学生表和老师表不是直接联系的，适合用子查询逐步的接近一侧。

SELECT sname FROM student 
WHERE sid NOT IN
(SELECT student_id FROM score WHERE course_id IN
	(SELECT cid FROM course WHERE teacher_id IN 
		(SELECT tid FROM teacher WHERE tname = '李平老师'
		)
	)
);
查询没有同时选择物理和生物的学生姓名
SELECT
	student.sname 
FROM
	student
	INNER JOIN score ON student.sid = score.student_id
	INNER JOIN course ON score.course_id = course.cid 
WHERE
	course.cname IN ( '物理', '生物' ) 
GROUP BY
	student.sname
HAVING
	COUNT( score.course_id )= 1;
	
	
# 答案2
SELECT
	sname 
FROM
	student
WHERE 
	sid in(
	SELECT student_id FROM score
	INNER JOIN course ON score.course_id = course.cid 
WHERE
	course.cname IN ( '物理', '生物' ) 
GROUP BY
	student_id
HAVING
	COUNT( score.course_id )= 1);
	
	
# 答案3
SELECT
	sname 
FROM
	student
WHERE 
	sid in(
	SELECT student_id FROM score
	where course_id IN (
	SELECT cid FROM course WHERE cname IN ( '物理', '生物' )
	) 
GROUP BY
	student_id
HAVING
	COUNT( course_id )= 1);
查询挂科超过两门的学生及所在班级
SELECT student.sname, class.caption   # 最终需要展示的内容，尝试用拼表解决
FROM student INNER JOIN class ON student.class_id = class.cid
WHERE student.sid IN
(SELECT student_id FROM score  # 不需要展示，但需要借助筛选的，用子查询拿到结果
	WHERE num < 60
	GROUP BY student_id
	HAVING COUNT(course_id)>=2);

标签：dep,多表,--,语法,course,student,MySQL,male,id
From： https://www.cnblogs.com/Leethon-lizhilog/p/16933012.html

相关文章

  • mysql数据库之事务
     一、mysql事务1、事务的概念2、事务的ACID特点2.1原子性2.2一致性2.3隔离性2.4持久性3、两个事务之间的影响3.1脏读（读取未提交数据）3.2不可重复度（前后多......
  • MySQL深度掌握之路
    目录​​目录​​​​前言​​​​知识点​​前言知识点MySQL要求​​AUTO_INCREMENT​​列必须有某种索引，如果没有索引，那么表的定义就是不合法的。任何一个​​PRIMARYKE......
  • MySQL自定义函数
     ⚠不推荐将业务逻辑存储在数据库中.MySQL不仅提供了很多很方便的内置函数，用户还可以自定义函数。不同于MongoDB对Js函数的良好支持，MySQL的自定义函数用起来感觉处处掣......
  • 多表查询 可视化软件Navicat
    今日内容总结多表查询的两种方法#方式一:连表操作 1.innerjoin 内连接select*from表1innerjoin表2on连接部分只连接两张表的共有部分 2.l......
  • 多mysql实例库联合查询
    情况一2个库在同一台物理主机情况二2个库不在同一台物理主机(即2个库分别在不同的物理主机)注意:myemployees库和shoppingCart库在同一台物理主机，如果不在同一台物理......
  • Linux：CentOS release 8.5 安装Mysql5.7
    添加Mysqlyum存储库下载安装软件包#下载软件包wgethttps://dev.mysql.com/get/mysql80-community-release-el7-6.noarch.rpm#安装软件包rpm-Uvhmysql80-commun......
  • springboot+mysql+mybatis逆向工程
     最近一直再做hibernate相关的工作，想捡起遗忘了一年的mybatis，就在自己的demo项目中引入了一下,记录下过程，这里只生成mapper和model和mapper.xml一、引入依赖  ......
  • MYSQL-8.0.31 windows免安装 版安装 方法
    1从官方下载绿色安装包 https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.31-winx64.zip2解压到C:\mysql-8.0.31-winx64\建立data目录新建m......
  • MySQL数据库：8、多表查询的方法与思路
    目录一、多表查询思路数据准备1、笛卡尔积1、1.代码实践1、2.笛卡尔积进阶操作2、连表操作2、1.内连接2、2.左连接2、3.右连接2、4.全连接3、子查询3、1.子查询代码实践一......
  • MySQL多表查询及pymysql简单使用
    目录多表查询的两种方法小知识点补充说明可视化软件Navicat多表查询练习题python操作MySQLpymysql补充说明多表查询的两种方法方式一：连表操作innerjoin内连接 selec......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99