多表查询

最新推荐文章于 2022-10-20 22:26:00 发布
最新推荐文章于 2022-10-20 22:26:00 发布
阅读量396 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/HongRi_/article/details/88536474
版权

多表查询的分类

在这里插入图片描述

内连接：用左边表的记录去匹配右边表的记录，如果符合条件就展示

隐式内连接：看不到JOIN关键字，条件使用WHERE 指定 SELECT 字段名 FROM 左表 右表 WHERE 条件；

格式
SELECT 展示字段 FROM 表 WHERE 条件;

显式内连接：使用 INNER JOIN … ON 语句, 可以省略 INNER SELECT 字段名 FROM 左表 INNER JOIN 右表 ON 条件；

格式
SELECT 展示字段 FROM 左表 INNER JOIN 右表 ON 条件;

内连接使用步骤

  • 确定查询的表
  • 确定表的连接条件
  • 确定查询字段

左外连接：以左表为基准，右表没有显示为null

格式
select * from 左表 left outer join 右表 on 条件;

右外连接：以右表为基准，右表没有显示为null

格式
select * from 左表 right outer 右表 on 条件;

子查询

一条select 语句的结果作为另一条select 语法的一部分

  • 子查询的结果又三种
    • 子查询的结果是一个值；
      在这里插入图片描述
    • 子查询结果是单列多行；
      在这里插入图片描述
    • 子查询结果是多列多行；
      在这里插入图片描述

子查询只要是单列肯定是在where后面作为条件，子查询只要是多列多行，肯定在from后面作为表
查询结果要是单列多行的话将结果作为条件，使用 in 关键字作为选择

案例

建表语句

-- 创建库
CREATE DATABASE IF NOT EXISTS test4 CHARACTER SET  utf8
-- 创建部门表
CREATE TABLE IF NOT EXISTS dept(
	id INT PRIMARY KEY,	-- 部门 id
	dname VARCHAR (20),	-- 部门名字
	loc VARCHAR(20)		-- 部门位置
)
-- 添加数据
INSERT INTO dept VALUES(10,'教研部','北京'),
			(20,'学工部','上海'),
			(30,'销售部','广州'),
			(40,'财务部','深圳');
-- 创建职务表
CREATE TABLE job (
	id INT,
	jname VARCHAR (20),	-- 名称
	descripition VARCHAR(50)	-- 描述
)
-- 设置主键
ALTER TABLE job ADD CONSTRAINT j_fk PRIMARY KEY (id) 
-- 添加职务
INSERT INTO job VALUES(1, '董事长', '管理整个公司，接单'),
			(2, '经理', '管理部门员工'),
			(3, '销售员', '向客人推销产品'),
			(4, '文员', '使用办公软件')
-- 创建职工表
CREATE TABLE emp(
	id INT PRIMARY KEY,
	ename VARCHAR(50), -- 员工姓名
	job_id INT, -- 职务id
	mgr INT , -- 上级领导
	joindate DATE, -- 入职日期
	salary DECIMAL(7,2), -- 工资
	bonus DECIMAL(7,2), -- 奖金
	dept_id INT, -- 所在部门编号
	CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job(id),
	CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept(id)
)	
-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1001,'孙悟空',4,1004,'2000‐12‐17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001‐02‐20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001‐02‐22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001‐04‐02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001‐09‐28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001‐05‐01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001‐09‐01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007‐04‐19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001‐11‐17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001‐09‐08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007‐05‐23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001‐12‐03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001‐12‐03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002‐01‐23','13000.00',NULL,10);			
-- 创建工资等级表
CREATE TABLE  salarygrade(
	grade INT PRIMARY KEY,
	losalary INT,
	hisalary INT4
)	
-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);
  • 查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述
SELECT e.id 员工ID,e.ename 员工名称,e.salary 工资,j.jname 职务名称,j.descripition 职务描述 
	FROM emp e,job j 
		WHERE j.id = e.job_id			
-----------------------------------------------------------
SELECT e.id 员工ID,e.ename 员工名称,e.salary 工资,j.jname 职务名称,j.descripition 职务描述 
   FROM emp e 
   	INNER JOIN job j 
   		ON j.id = e.job_id
  • 查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
SELECT e.id 员工ID,e.ename 员工名称,e.salary 工资,j.jname 职务名称,j.descripition 职务描述,d.dname 部门名称,d.loc 部门位置FROM 
	emp e,job j,dept d WHERE
	 e.job_id = j.id AND e.dept_id = d.id;
------------------------------------------
SELECT e.id 员工ID,e.ename 员工名称,e.salary 工资,j.jname 职务名称,j.descripition 职务描述,d.dname 部门名称,d.loc 部门位置 FROM 
	emp e INNER JOIN dept d INNER JOIN job j ON
	 e.job_id = j.id AND e.dept_id = d.id;
  • 查询所有员工信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
SELECT e.ename 员工姓名,e.salary 工资,j.jname 职务名称,j.descripition 职务描述,d.dname 部门名称,d.loc 部门位置,	s.grade 工资等级 FROM 
	emp e,dept d,job j,salarygrade s WHERE
	 e.job_id = j.id AND e.dept_id = d.id AND e.salary < hisalary AND e.salary > losalary
	  ORDER BY s.grade DESC
------------------------------------------
SELECT e.ename 员工姓名,e.salary 工资,j.jname 职务名称,j.descripition 职务描述,d.dname 部门名称,d.loc 部门位置,	s.grade 工资等级 FROM 
	emp e INNER JOIN dept d INNER JOIN salarygrade s INNER JOIN job j
	 ON e.job_id = j.id AND e.dept_id = d.id AND e.salary BETWEEN s.losalary AND s.hisalary
	  ORDER BY s.grade DESC
  • 查询经理的信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
SELECT e.ename 姓名,e.salary 工资,j.jname 职务名称,j.descripition 职务描述,d.dname 部门名称,d.loc 部门位置,s.grade 工资等级 FROM
	emp e INNER JOIN dept d INNER JOIN job j INNER JOIN salarygrade s ON
	 e.job_id = j.id AND e.dept_id = d.id AND e.salary BETWEEN s.losalary AND s.hisalary
	 ORDER BY d.dname;	
---------------------------------------
SELECT e.ename 姓名,e.salary 工资,j.jname 职务名称,j.descripition 职务描述,d.dname 部门名称,d.loc 部门位置,s.grade 工资等级 FROM			
	emp e,dept d,job j,salarygrade s WHERE 
	 e.job_id = j.id AND e.dept_id = d.id AND e.salary BETWEEN s.losalary AND s.hisalary
	  ORDER BY d.dname;
  • 查询出部门编号、部门名称、部门位置、部门人数
SELECT d.id 部门编号, d.dname 部门名称,d.loc 部门位置,e. 部门人数
 FROM dept d ,(SELECT COUNT(*) 部门人数,dept_id  FROM emp GROUP BY dept_id) e
  WHERE d.id  =  e.dept_id		
----------------------------------------
SELECT d.id 部门编号,d.dname 部门名称,d.loc 部门位置,e. 部门人数 
 FROM dept d INNER JOIN (SELECT COUNT(*) 部门人数,dept_id  FROM emp GROUP BY dept_id) e
  ON d.id  =  e.dept_id
多表查询练习
#########################################################
#省份和市
#########################################################
#创建省份表
CREATE TABLE province(
  pid INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10),
  descripition VARCHAR(20)
);
#创建市表
CREATE TABLE city(
  cid INT,
  NAME VARCHAR(10),
  descripition VARCHAR(20),
  pid INT
);
#创建外键约束
ALTER TABLE city ADD CONSTRAINT FOREIGN KEY (pid) REFERENCES province (pid);
#########################################################
#用户和角色
#########################################################
#创建用户表
CREATE TABLE USER (
  uid INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(10),
  PASSWORD VARCHAR(20)
);
#创建角色表
CREATE TABLE role(
  rid INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10)
);
#创建关联表
CREATE TABLE user_role(
  urid INT,
  uid INT,
  rid INT
);
#创建外键约束
ALTER TABLE user_role ADD CONSTRAINT FOREIGN KEY (uid) REFERENCES USER (uid);
ALTER TABLE user_role ADD CONSTRAINT FOREIGN KEY (rid) REFERENCES role(rid);
#########################################################
#商品和分类
#########################################################
#分类表
CREATE TABLE category(
  cid INT PRIMARY KEY AUTO_INCREMENT,
  cname VARCHAR(30)
);
#商品表
CREATE TABLE products(
  pid VARCHAR(10) PRIMARY KEY,
  pname VARCHAR(30),
  price INT, 
  flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架
  category_id VARCHAR(32),
);
#添加外键约束
ALTER TABLE products ADD CONSTRAINT FOREIGN KEY (category_id) REFERENCES category (cid);
#分类数据 
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
INSERT INTO category(cid,cname) VALUES('c004','奢侈品');
#商品数据
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001'); 
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001'); 
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002'); 
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002'); 
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p006','花花公 子',440,'1','c002'); 
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002'); 
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003'); 
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p009','相宜本 草',200,'1','c003');

#交叉查询数据:产生笛卡尔积
SELECT * FROM products category;
#内连接查询
#隐试内连接查询:使用where判断,避免笛卡尔积产生
SELECT * FROM products p, category c WHERE p.category_id = c.cid AND p.flag='1';
#显式内连接 SELECT * FROM 左表 INNER[可以省略] JOIN 右表 ON 条件;
SELECT * FROM products p INNER JOIN category c ON p.category_id = c.cid AND p.flag='1';
#外链接查询:
#左外链接查询:以左表为基准,右表没有的数据显式null 格式:SELECT * FROM 左表 LEFT OUTER[可以省略] JOIN 右表 ON 条件
SELECT cname, COUNT(category_id) FROM products p LEFT OUTER JOIN category c ON p.category_id = c.cid GROUP BY cname; 
#右外链接查询:以右表为基准,左表没有的数据显式null
SELECT cname,COUNT(category_id) FROM products p RIGHT OUTER JOIN category c ON p.category_id = c.cid GROUP BY cname;
#查询分类名称和分类下商品数量
SELECT c.cname, COUNT(category_id) FROM category c,products p WHERE c.cid = p.category_id GROUP BY c.cname; #这种情况查的是交集,
SELECT c.cname, COUNT(category_id) FROM category c LEFT OUTER JOIN products p ON c.cid = p.category_id GROUP BY cname; #这种情况查询的是并集,并且是以左侧为基准
SELECT c.cname, COUNT(category_id) FROM category c RIGHT OUTER JOIN products p ON c.cid = p.category_id GROUP BY cname; #这种情况查询的也是并集,知识以右表为基准,但是右表中没有c004的分类
#子查询:一条SELECT语句的结果作为另一条SELECT语句语法的一部分
#查询化妆品分类商家商品的详情
#隐试内连接查询方式
SELECT p.* FROM products p, category c WHERE p.category_id = c.cid AND p.category_id = 'c003';
#显式内连接查询方式
SELECT p.* FROM products p INNER JOIN category c ON p.category_id = c.cid AND p.category_id = 'c003';
#子查询
SELECT p.* FROM products p,category c WHERE p.category_id = (SELECT cid FROM category WHERE cname = '化妆品') AND p.category_id = c.cid;
红日_
关注
专栏目录
多表的查询
embelfe_segge的博客
07-31 1803
因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接。如果给表起了别名，一旦在SELECT或WHERE中使用表的别名的话，则必须使用表的别名，而不能再使用表的原名。如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。...
数据库基础篇：5.多表查询
最新发布
weixin_41822929的博客
08-26 724
多表查询就是指从多张表中查询数据。在SQL语句中，如何来去除无效的笛卡尔积呢？我们可以给多表查询加上连接查询的条件即可。1). 概念SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。2). 分类根据子查询结果不同，分为：A. 标量子查询（子查询结果为单个值）B. 列子查询(子查询结果为一列)C. 行子查询(子查询结果为一行)D. 表子查询(子查询结果为多行多列)
【MyBatis系列】Mybatis多表查询与动态SQL
未见花闻的博客
10-20 2797
本篇文章将介绍使用MyBatis进行多表查询以及MyBatis的动态SQL特性。
MySQL多表查询
m0_51779342的博客
03-14 2127
MySQL多表查询 多表查询就是从多张表查询数据 若不加限制条件，直接使用select语句进行多表查询，将会产生笛卡尔积 笛卡尔积就是把所有集合元素的所偶有组合都列举出来，即A的个数乘上B的个数 -- 删除表 DROP TABLE IF EXISTS emp; DROP TABLE IF EXISTS dept; -- 部门表 CREATE TABLE dept ( id INT PRIMARY KEY auto_increment, -- 部门编号,主键自增 dep_n
java数据库多表查询
SSSS__AS的博客
09-26 1917
但在左连接中，因为emp表是左表，所以左表中的记录都会查询出来，即“张三”这条记录也会查出，但相应的右表部分显示NULL。两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。这个条件大多数情况下都是使用主外键关系去除。连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。
多表查询（整理）
weixin_43294936的博客
03-06 3868
一、多表关系 概述：项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间互相关联，所以各个表结构之间也存在着各种联系。 基本上有三种：一对多（多对一），多对多，一对一。 一对多（多对一） 多对多 一对一 辅助建表内容： -- 多表查询 -- create table student( id int auto_increment primary key comment '主键id', name varchar(10...
数据库的多表查询
KooCoolX的博客
06-29 2840
1.多表查询-数据准备 SQL语句 -- 创建db6数据库 CREATE DATABASE db6; -- 使用db6数据库 USE db6; -- 创建user表 CREATE TABLE USER( id INT PRIMARY KEY AUTO_INCREMENT, -- 用户id NAME VARCHAR(20), -- 用户姓名 age INT -- 用户年龄 ); -- 添加数据 INSERT INTO US
Day05_多表查询
weixin_45014721的博客
03-30 627
1.多表查询 * 查询语法： select 列名列表 from 表名列表 where.... 先准备sql # 创建部门表 CREATE TABLE dept( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ); INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部'); # 创建员工表 CREATE TABLE emp ( id INT PRIMARY KEY AUTO_IN
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值