mysql 多表练习3

最新推荐文章于 2024-02-20 17:22:38 发布
最新推荐文章于 2024-02-20 17:22:38 发布
阅读量316 收藏 1
点赞数
分类专栏： 笔记 练习 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45087693/article/details/111599209
版权

多表查询数据准备

-- 创建db4数据库
CREATE DATABASE db4;
-- 使用db4数据库
USE db4;

-- 创建user表
CREATE TABLE USER(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 用户id
	NAME VARCHAR(20),			-- 用户姓名
	age INT                                 -- 用户年龄
);
-- 添加数据
INSERT INTO USER VALUES (1,'张三',23);
INSERT INTO USER VALUES (2,'李四',24);
INSERT INTO USER VALUES (3,'王五',25);
INSERT INTO USER VALUES (4,'赵六',26);


-- 订单表
CREATE TABLE orderlist(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 订单id
	number VARCHAR(30),			-- 订单编号
	uid INT,    -- 外键字段
	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);
-- 添加数据
INSERT INTO orderlist VALUES (1,'hm001',1);
INSERT INTO orderlist VALUES (2,'hm002',1);
INSERT INTO orderlist VALUES (3,'hm003',2);
INSERT INTO orderlist VALUES (4,'hm004',2);
INSERT INTO orderlist VALUES (5,'hm005',3);
INSERT INTO orderlist VALUES (6,'hm006',3);
INSERT INTO orderlist VALUES (7,'hm007',NULL);


-- 商品分类表
CREATE TABLE category(
	id INT PRIMARY KEY AUTO_INCREMENT,  -- 商品分类id
	NAME VARCHAR(10)                    -- 商品分类名称
);
-- 添加数据
INSERT INTO category VALUES (1,'手机数码');
INSERT INTO category VALUES (2,'电脑办公');
INSERT INTO category VALUES (3,'烟酒茶糖');
INSERT INTO category VALUES (4,'鞋靴箱包');


-- 商品表
CREATE TABLE product(
	id INT PRIMARY KEY AUTO_INCREMENT,   -- 商品id
	NAME VARCHAR(30),                    -- 商品名称
	cid INT, -- 外键字段
	CONSTRAINT cp_fk1 FOREIGN KEY (cid) REFERENCES category(id)
);
-- 添加数据
INSERT INTO product VALUES (1,'华为手机',1);
INSERT INTO product VALUES (2,'小米手机',1);
INSERT INTO product VALUES (3,'联想电脑',2);
INSERT INTO product VALUES (4,'苹果电脑',2);
INSERT INTO product VALUES (5,'中华香烟',3);
INSERT INTO product VALUES (6,'玉溪香烟',3);
INSERT INTO product VALUES (7,'计生用品',NULL);


-- 中间表
CREATE TABLE us_pro(
	upid INT PRIMARY KEY AUTO_INCREMENT,  -- 中间表id
	uid INT, -- 外键字段。需要和用户表的主键产生关联
	pid INT, -- 外键字段。需要和商品表的主键产生关联
	CONSTRAINT up_fk1 FOREIGN KEY (uid) REFERENCES USER(id),
	CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES product(id)
);
-- 添加数据
INSERT INTO us_pro VALUES (NULL,1,1);
INSERT INTO us_pro VALUES (NULL,1,2);
INSERT INTO us_pro VALUES (NULL,1,3);
INSERT INTO us_pro VALUES (NULL,1,4);
INSERT INTO us_pro VALUES (NULL,1,5);
INSERT INTO us_pro VALUES (NULL,1,6);
INSERT INTO us_pro VALUES (NULL,1,7);
INSERT INTO us_pro VALUES (NULL,2,1);
INSERT INTO us_pro VALUES (NULL,2,2);
INSERT INTO us_pro VALUES (NULL,2,3);
INSERT INTO us_pro VALUES (NULL,2,4);
INSERT INTO us_pro VALUES (NULL,2,5);
INSERT INTO us_pro VALUES (NULL,2,6);
INSERT INTO us_pro VALUES (NULL,2,7);
INSERT INTO us_pro VALUES (NULL,3,1);
INSERT INTO us_pro VALUES (NULL,3,2);
INSERT INTO us_pro VALUES (NULL,3,3);
INSERT INTO us_pro VALUES (NULL,3,4);
INSERT INTO us_pro VALUES (NULL,3,5);
INSERT INTO us_pro VALUES (NULL,3,6);
INSERT INTO us_pro VALUES (NULL,3,7);
INSERT INTO us_pro VALUES (NULL,4,1);
INSERT INTO us_pro VALUES (NULL,4,2);
INSERT INTO us_pro VALUES (NULL,4,3);
INSERT INTO us_pro VALUES (NULL,4,4);
INSERT INTO us_pro VALUES (NULL,4,5);
INSERT INTO us_pro VALUES (NULL,4,6);
INSERT INTO us_pro VALUES (NULL,4,7);

内连接查询

/*
	显示内连接
	标准语法：
		SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 关联条件;
*/
-- 查询用户信息和对应的订单信息
SELECT
	* 
FROM
	`USER`
	JOIN orderlist ON `USER`.id = orderlist.uid

-- 查询用户信息和对应的订单信息，起别名
SELECT
	* 
FROM
	`USER` u
	INNER JOIN orderlist o ON u.id = o.uid
-- 查询用户姓名，年龄。和订单编号
SELECT
	u.NAME,
	u.age,
	o.number 
FROM
	`USER` u
JOIN orderlist o ON u.id = o.uid

/*
	隐式内连接
	标准语法：
		SELECT 列名 FROM 表名1,表名2 WHERE 关联条件;
*/
-- 查询用户姓名，年龄。和订单编号
SELECT
	u.NAME,
	u.age,
	o.number 
FROM
	`USER` u,
	orderlist o 
WHERE
	u.id = o.uid;

外连接查询

/*
	左外连接
	标准语法：
		SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;
*/
-- 查询所有用户信息，以及用户对应的订单信息
SELECT
	u.*,
	o.number
FROM
	`USER` u
LEFT JOIN
	orderlist o
ON 
	u.id=o.uid
/*
	右外连接
	标准语法：
		SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;
*/
-- 查询所有订单信息，以及订单所属的用户信息
SELECT 
	o.*,
	u.name
FROM
	`USER` u
RIGHT JOIN
orderlist o
ON
o.uid=u.id

子查询

/*
	结果是单行单列的
	标准语法：
		SELECT 列名 FROM 表名 WHERE 列名=(SELECT 列名 FROM 表名 [WHERE 条件]);
*/
-- 查询年龄最高的用户姓名
select name ,age from `USER` where age=(SELECT MAX(age) FROM `USER`)


/*
	结果是多行单列的
	标准语法：
		SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN (SELECT 列名 FROM 表名 [WHERE 条件]); 
*/
-- 查询张三和李四的订单信息
-- 第一步 查询查询张三李四对应的id
select id FROM `USER` where name IN('张三','李四');
-- 第二步 将查询到的id作为一个条件
select * from orderlist where uid in(select id FROM `USER` where name IN('张三','李四'))

/*
	结果是多行多列的
	标准语法：
		SELECT 列名 FROM 表名 [别名],(SELECT 列名 FROM 表名 [WHERE 条件]) [别名] [WHERE 条件];
*/
-- 查询订单表中id大于4的订单信息和所属用户信息
-- 第一步 先查询订单表中id>4的信息
SELECT * FROM orderlist where id>4;
-- 第二步 将查询出来的结果作为一张表 结合user表一起查询
select u.`NAME`,o.number FROM `USER` u,(SELECT * FROM orderlist where id>4) o where o.uid=u.id

自关联查询

-- 创建员工表
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 员工编号
	NAME VARCHAR(20),			-- 员工姓名
	mgr INT,				-- 上级编号
	salary DOUBLE				-- 员工工资
);
-- 添加数据
INSERT INTO employee VALUES (1001,'孙悟空',1005,9000.00),
(1002,'猪八戒',1005,8000.00),
(1003,'沙和尚',1005,8500.00),
(1004,'小白龙',1005,7900.00),
(1005,'唐僧',NULL,15000.00),
(1006,'武松',1009,7600.00),
(1007,'李逵',1009,7400.00),
(1008,'林冲',1009,8100.00),
(1009,'宋江',NULL,16000.00);


-- 查询所有员工的姓名及其直接上级的姓名，没有上级的员工也需要查询
/*
分析
	员工信息 employee表
	条件：employee.mgr = employee.id
	查询左表的全部数据，和左右两张表有交集部分数据，左外连接
*/
SELECT 
e1.id,
e1.name,
e1.mgr,
e2.id,
e2.name
FROM
employee e1
LEFT JOIN 
employee e2
ON
e1.mgr=e2.id;

多表查询综合练习

-- 1.查询用户的编号、姓名、年龄。订单编号
/*
分析
	用户的编号、姓名、年龄  user表      订单编号 orderlist表
	条件：user.id=orderlist.uid
*/
SELECT 
		u.id,   -- 用户编号
		u.`NAME`,  -- 用户姓名
		u.age, 	-- 用户年龄
		o.number -- 订单编号
		
FROM
`USER` u, -- 用户表
orderlist o -- 订单表
WHERE
	u.id=o.uid

-- 2.查询所有的用户。用户的编号、姓名、年龄。订单编号
/*
分析
	用户的编号、姓名、年龄  user表    订单编号 orderlist表
	条件：user.id=orderlist.uid
	查询所有的用户，左外连接
*/
SELECT
	u.id,  		-- 用户编号
	u.`NAME`, -- 用户姓名
	u.age,    -- 用户年龄
	o.number  -- 订单编号
FROM
	`USER` u -- 用户表
	LEFT JOIN orderlist o -- 订单表
	ON u.id = o.uid

-- 3.查询所有的订单。用户的编号、姓名、年龄。订单编号
/*
分析
	用户的编号、姓名、年龄 user表    订单编号 orderlist表
	条件：user.id=orderlist.uid
	查询所有的订单，右外连接
*/
SELECT 
	u.id,  		-- 用户编号
	u.`NAME`, -- 用户姓名
	u.age,    -- 用户年龄
	o.number  -- 订单编号
FROM
`USER` u		-- 用户表
RIGHT JOIN
orderlist o  -- 订单表
ON 
u.id=o.uid
	

-- 4.查询用户年龄大于23岁的信息。显示用户的编号、姓名、年龄。订单编号
/*
分析
	用户的编号、姓名、年龄 user表    订单编号 orderlist表
	条件：user.id=orderlist.uid AND user.age > 23
*/
SELECT
	u.id,-- 用户编号
	u.`NAME`,-- 用户姓名
	u.age,-- 用户年龄
	o.number -- 订单编号
	
FROM
	`USER` u,-- 用户表
	orderlist o -- 订单表
	
WHERE
	u.id = o.uid 
	AND u.age > 23


-- 5.查询张三和李四用户的信息。显示用户的编号、姓名、年龄。订单编号
/*
分析
	用户的编号、姓名、年龄 user表   订单编号 orderlist表
	条件：user.id=orderlist.uid AND user.name IN ('张三','李四')
*/
SELECT
	u.id,-- 用户编号
	u.`NAME`,-- 用户姓名
	u.age,-- 用户年龄
	o.number -- 订单编号
FROM
`USER` u, -- 用户表
orderlist o -- 订单表
WHERE
u.id=o.uid
AND 
u.`NAME` IN ('张三','李四')



-- 6.查询商品分类的编号、分类名称。分类下的商品名称
/*
分析
	商品分类的编号、分类名称 category表    商品名称 product表
	条件：category.id=product.cid
*/
SELECT 
	c.id,  -- 商品分类的编号
	c.`NAME`, -- 分类名称
	p.`NAME`
FROM
category c,  -- 商品分类表
product p -- 商品表
WHERE
	c.id=p.cid

-- 7.查询所有的商品分类。商品分类的编号、分类名称。分类下的商品名称
/*
分析
	商品分类的编号、分类名称 category表    商品名称 product表
	条件：category.id=product.cid
	查询所有的商品分类，左外连接
*/
SELECT
c.id,  -- 分类的编号
c.`NAME`, -- 分类的名称
p.`NAME`-- 商品名称
FROM
category c -- 商品分类表
LEFT JOIN
product p -- 商品表
ON 
c.id=p.cid

-- 8.查询所有的商品信息。商品分类的编号、分类名称。分类下的商品名称
/*
分析
	商品分类的编号、分类名称  category表   商品名称 product表
	条件：category.id=product.cid
	查询所有的商品信息，右外连接
*/
SELECT
c.id,  -- 分类的编号
c.`NAME`, -- 分类的名称
p.`NAME`-- 商品名称
FROM
category c
RIGHT JOIN
product p
ON
c.id=p.cid



-- 9.查询所有的用户和该用户能查看的所有的商品。显示用户的编号、姓名、年龄。商品名称
/*
分析
	用户的编号、姓名、年龄 user表   商品名称 product表    中间表 us_pro
	条件：us_pro.uid=user.id AND us_pro.pid=product.id
*/
SELECT
u.*,    -- 用户的所有信息
p.`NAME` -- 商品的名称
FROM
`USER` u, -- 用户表
product p, -- 商品表
us_pro  up -- 商品和用户的中间表
WHERE
up.uid=u.id 
AND 
up.pid=p.id


-- 10.查询张三和李四这两个用户可以看到的商品。显示用户的编号、姓名、年龄。商品名称
/*
分析
	用户的编号、姓名、年龄 user表   商品名称 product表   中间表 us_pro
	条件：us_pro.uid=user.id AND us_pro.pid=product.id AND user.name IN ('张三','李四') 
*/
SELECT
	u.*,-- 用户的所有信息
	p.`NAME` -- 商品的名称
	
FROM
	`USER` u,-- 用户表
	product p,-- 商品表
	us_pro up -- 中间表
	
WHERE
	up.uid = u.id 
	AND up.pid = p.id 
	AND u.`NAME` IN ('张三','李四')
唔艾爪哇
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL多表查询练习题原数据
08-18
练习题答案解决思路请查看本人原文章MySQL数据库多表查询练习题
MySQL学习笔记一
qq_55906442的博客
03-29 684
一.MySQL的功能特点 1.开源 MySQL在GPL的限制下通过因特网开发，免费提供其源代码，任何人可以修改其代码。 2.可扩展 MySQL可伸缩，可用于管理小型或大型数据库，RDBMS适应数据的增长。 3.可移植 MySQL支持多个操作系统。 4.使用客服机/服务器和嵌入式架构 5.高性能 6.低TCO 7.可靠 8.便于利用 9.安全 10.高可用 二.数据库查询语言 1.数据定义语言（DDL） create:创建数据库对象（表）。 alter:修改数据库对象..
mysql fk1_Mysql基础知识
weixin_42421824的博客
02-17 414
数据库操作create database yanliang; 创建数据库drop database yanliang; 删除数据库show databases; 显示所有数据库use yanliang; 选中yanliang数据库进行操作表操作(前提:use选中数据库)建表规则create table 表名(属性名 数据类型 [完整性约束条件],……属性名 数据类型 [完整性约束条件] 注...
HQL07 查找年龄大于24岁的用户信息
m0_47792921的博客
03-29 293
题目说明 练习题目来自牛客网sq在线编程 非技术快速入门查找年龄大于24岁的用户信息_牛客题霸_牛客网 (nowcoder.com) 测试用例 create database nk_test; //创建数据库 use nk_test; //进入数据库 -- 创建测试表 CREATE TABLE user_profile( id int, device_id int, gender string, age int, university string, province string); --
查询 年龄 在25 - 30 之间的用户 显示id 名字 年龄
qq_60509834的博客
07-29 1258
package cn.tedu; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Test02 { public static void main(String[] args) throws Exception { //查询 年龄 在25 - 30 之间的用户 显示id 名字 .
实验5 数据查询--连接查询
beyond谚语的博客
12-06 2488
实验5 数据查询--连接查询 一、实验目的 1.熟悉等值联接查询的方法。 2.熟悉非等值联接查询的方法。 3.熟悉自身联接查询的方法。 4.熟悉外联接查询的方法。 5.熟悉复合条件联接的方法。 二、实验要求 应用SELECT语句对数据库eshop中数据进行指定条件的连接查询。 三、实验步骤 1.查询购买了商品号为“0910810004”的会员号和姓名，并以汉字标题显示，SQL代码...
MySQL单表查询练习
12-07
MySQL单表查询练习题
mysql 多表联接查询练习
10-31
mysql,多表联接查询练习,mysql,多表联接内联,外联练习。 mysql,多表联接查询练习,mysql,多表联接内联,外联练习。
MySQL：多表查询练习
最新发布
05-07
MySQL：多表查询练习
mysql学生表练习.pdf
10-18
在MySQL数据库中，创建学生表是一项基础且重要的任务，它涉及到数据库设计和管理的基本概念。以下将详细解释如何创建、修改、删除以及查询学生表数据。 首先，创建一个学生表通常涉及以下几个步骤： 1. **定义表...
3.sql2008查询
weixin_30408675的博客
11-27 248
根据需要和条件，查看并显示结果集，如果需要，可将结果集生成数据表select:查什么,列筛选,可以用*代表全部列from:在哪个表中查,where:符合什么样的条件,行筛选select: 表示x不等于''的方法: 1.x!='' 2.x<>'' 3.x Not IN('') 4.Not X='' ...
MySQL子查询之表子查询
weixin_45966674的博客
10-30 1055
这就是表子查询比较常见的一种形式 用sql查询出一个表类型 多行多列的数据 作为暂时的数据存储表 去和别的表去连表查询。这里 我们将子查询 查到的多行多列的数据 作为一张暂时的存储数据的表 然后用他里面的数据跟部门表去连表查询。表子查询 是只 子查询得到的数据 是多行多列的 就像一张单独的数据表。然后我们一步到位 实现上面的需求 编写sql如下。我们要找出年龄大于30的用户 并找出他们的部门。我们这里有一张staff 员工表。我们先来实现查询年龄大于三十的用户。
XPO学习(3)----数据查询
weixin_33920401的博客
10-18 344
通过前两节，我们知道我们从数据库中检索数据是通过 XPCollection或XPView，但是我们一直查询的是数据库中所有的数据，其实我们也可以根据自己的意愿来获取想得到的数据。 这一节，我们将学会如何自定义一个简单的规则。这一节的例子是在上一节基础上操作的。 任务： 这从数据库中查询年龄大于30的客户。显示年龄大于30小于40的客户。 解决方案： XPO为我们提供了多种途径来实现同样的...
实验04 单表查询
weixin_47500703的博客
06-29 3034
文章目录实验目的实验要求实验内容查询所有客户的信息。查询所有客户的姓名、电话、年龄、性别。查询所有客户的姓名、出生年份、电话，并且将输出结果中的列名显示为“客户姓名”、“出生年份”、“手机号码”。查询所有男客户的信息。查询年龄小于30岁的客户姓名、电话、地址查询成交记录表中下订单两天后才成交的订单编号、客户编号、商品编号。查询“广东省”的客户姓名、年龄、电话。查询年龄大于50岁的男客户姓名、电话、地址，并按年龄升序排列输出。查询年龄在30到50之间的客户姓名、电话、地址，并按年龄降序排列输出。查询姓李的客户
关于数据库中FK的简单理解以及应用
热门推荐
Alex.hu的专栏
09-13 1万+
本文用于博主自身记录遇到的问题以及解决办法。
MySQL-----多表操作
2301_77599154的博客
02-20 2489
实际开发中，一个项目通常需要很多张表才能完成。例如:一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系，接下来我们将在单表的基础上，一起学习多表方面的知识。
Mysql——多表操作
浪漫的诗人博客
04-28 877
在含有子查询的语句中，子查询必须书写在圆括号内。sql语句首先会执行子查询中的语句，然后再将返回的结果作为外层sql的过滤条件，执行顺序是从最里层的子查询开始执行。
mysql多表查询练习
11-05
MySQL多表查询是指在查询中同时涉及到多个表的操作。多表查询可以更加方便地获取到需要的数据，提供了更加灵活的查询方式。 在进行多表查询之前，我们首先需要对多个表之间的关系进行了解和分析。常见的多表查询...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值