MySQL-多表查询练习题1

作者: 七喜丶 | 来源:发表于2021-09-28 11:10 被阅读0次

MySQL-多表查询练习题1
MySQL-多表查询练习题3
MySQL-多表查询练习题2
MySql-多表查询
Mysql-多表查询as索引
SQLAlchemy（四）
python面试题01
4.MySQL多表&事务
MySQL多表&事务课堂笔记
MySql : 三、多表查询和事务

多表查询-数据准备

-- 创建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 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);

题目如下：

1.查询用户的编号、姓名、年龄。订单编号
2.查询所有的用户。用户的编号、姓名、年龄。订单编号
3.查询所有的订单。用户的编号、姓名、年龄。订单编号
4.查询用户年龄大于23岁的信息。显示用户的编号、姓名、年龄。订单编号
5.查询张三和李四用户的信息。显示用户的编号、姓名、年龄。订单编号
6.查询商品分类的编号、分类名称。分类下的商品名称
7.查询所有的商品分类。商品分类的编号、分类名称。分类下的商品名称
8.查询所有的商品信息。商品分类的编号、分类名称。分类下的商品名称
9.查询所有的用户和所有的商品。显示用户的编号、姓名、年龄。商品名称
10.查询张三和李四这两个用户可以看到的商品。显示用户的编号、姓名、年龄。商品名称

参考答案:

(1) 查询用户的编号、姓名、年龄。订单编号

/*
分析：
    用户的编号、姓名、年龄  user表     订单编号 orderlist表
    条件：user.id = orderlist.uid
*/
SELECT
    t1.`id`,    -- 用户编号
    t1.`name`,    -- 用户姓名
    t1.`age`,    -- 用户年龄
    t2.`number`    -- 订单编号
FROM
    USER t1,       -- 用户表
    orderlist t2   -- 订单表
WHERE
    t1.`id` = t2.`uid`;

(2)查询所有的用户。用户的编号、姓名、年龄。订单编号

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

(3)查询所有的订单。用户的编号、姓名、年龄。订单编号

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

(4)查询用户年龄大于23岁的信息。显示用户的编号、姓名、年龄。订单编号

/*
分析：
    用户的编号、姓名、年龄 user表     订单编号 orderlist表
    条件：user.age > 23 AND user.id = orderlist.uid
*/
/*
select
    t1.`id`,    -- 用户编号
    t1.`name`,    -- 用户姓名
    t1.`age`,    -- 用户年龄
    t2.`number`    -- 订单编号
from
    user t1,     -- 用户表
    orderlist t2 -- 订单表
where
    t1.`age` > 23
    and
    t1.`id` = t2.`uid`;
*/
SELECT
    t1.`id`,    -- 用户编号
    t1.`name`,    -- 用户姓名
    t1.`age`,    -- 用户年龄
    t2.`number`    -- 订单编号
FROM
    USER t1       -- 用户表
LEFT OUTER JOIN
    orderlist t2  -- 订单表
ON
    t1.`id` = t2.`uid`
WHERE
    t1.`age` > 23;

(5)查询张三和李四用户的信息。显示用户的编号、姓名、年龄。订单编号

/*
分析：
    用户的编号、姓名、年龄 user表     订单编号 orderlist表
    条件：user.id = orderlist.uid AND user.name IN ('张三','李四');
*/
SELECT
    t1.`id`,    -- 用户编号
    t1.`name`,    -- 用户姓名
    t1.`age`,    -- 用户年龄
    t2.`number`    -- 订单编号
FROM
    USER t1,        -- 用户表
    orderlist t2    -- 订单表
WHERE
    t1.`id` = t2.`uid`
    AND
    -- (t1.`name` = '张三' OR t1.`name` = '李四');
    t1.`name` IN ('张三','李四');

(6)查询商品分类的编号、分类名称。分类下的商品名称

/*
分析：
    商品分类的编号、分类名称 category表     分类下的商品名称 product表
    条件：category.id = product.cid
*/
SELECT
    t1.`id`,    -- 分类编号
    t1.`name`,    -- 分类名称
    t2.`name`    -- 商品名称
FROM
    category t1,    -- 商品分类表
    product t2        -- 商品表
WHERE
    t1.`id` = t2.`cid`;

(7)查询所有的商品分类。商品分类的编号、分类名称。分类下的商品名称

/*
分析：
    商品分类的编号、分类名称 category表     分类下的商品名称 product表
    条件：category.id = product.cid
    查询所有的商品分类，使用左外连接
*/
SELECT
    t1.`id`,    -- 分类编号
    t1.`name`,    -- 分类名称
    t2.`name`    -- 商品名称
FROM
    category t1    -- 商品分类表
LEFT OUTER JOIN
    product t2    -- 商品表
ON
    t1.`id` = t2.`cid`;

(8)查询所有的商品信息。商品分类的编号、分类名称。分类下的商品名称

/*
分析：
    商品分类的编号、分类名称 category表     分类下的商品名称 product表
    条件：category.id = product.cid
    查询所有的商品信息，使用右外连接
*/
SELECT
    t1.`id`,    -- 分类编号
    t1.`name`,    -- 分类名称
    t2.`name`    -- 商品名称
FROM
    category t1    -- 商品分类表
RIGHT OUTER JOIN
    product t2    -- 商品表
ON
    t1.`id` = t2.`cid`;

(9)查询所有的用户和所有的商品。显示用户的编号、姓名、年龄。商品名称

/*
分析：
    用户的编号、姓名、年龄 user表   商品名称 product表   中间表 us_pro
    条件：us_pro.uid = user.id AND us_pro.pid = product.id
*/
SELECT
    t1.`id`,    -- 用户编号
    t1.`name`,    -- 用户名称
    t1.`age`,    -- 用户年龄
    t2.`name`    -- 商品名称
FROM
    USER t1,    -- 用户表
    product t2,    -- 商品表
    us_pro t3    -- 中间表
WHERE
    t3.`uid` = t1.`id`
    AND
    t3.`pid` = t2.`id`;

(10)查询张三和李四这两个用户可以看到的商品。显示用户的编号、姓名、年龄。商品名称

/*
分析：
    用户的编号、姓名、年龄 user表   商品名称 product表   中间表 us_pro
    条件：us_pro.uid = user.id AND us_pro.pid = product.id AND user.name IN ('张三','李四')
*/
SELECT
    t1.`id`,    -- 用户编号
    t1.`name`,    -- 用户名称
    t1.`age`,    -- 用户年龄
    t2.`name`    -- 商品名称
FROM
    USER t1,    -- 用户表
    product t2,    -- 商品表
    us_pro t3    -- 中间表
WHERE
    (t3.`uid` = t1.`id` AND t3.`pid` = t2.`id`)
    AND
    -- (t1.`name` = '张三' or t1.`name` = '李四');
    t1.`name` IN ('张三','李四');