← 返回学习路线

SYSTEM

查询练习

Java基础查询练习system
src/content/notes/system/查询练习.md

查询练习

数据准备:

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

-- 创建user表
CREATE TABLE USER(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 用户id
	NAME VARCHAR(20),					-- 用户姓名
	age INT                             -- 用户年龄
);

-- 订单表
CREATE TABLE orderlist(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 订单id
	number VARCHAR(30),					-- 订单编号
	uid INT,   							-- 外键字段
	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);

-- 商品分类表
CREATE TABLE category(
	id INT PRIMARY KEY AUTO_INCREMENT,  -- 商品分类id
	NAME VARCHAR(10)                    -- 商品分类名称
);

-- 商品表
CREATE TABLE product(
	id INT PRIMARY KEY AUTO_INCREMENT,   -- 商品id
	NAME VARCHAR(30),                    -- 商品名称
	cid INT, -- 外键字段
	CONSTRAINT cp_fk1 FOREIGN KEY (cid) REFERENCES category(id)
);

-- 中间表
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)
);

多表练习架构设计

数据查询:

  1. 查询用户的编号、姓名、年龄、订单编号

    数据:用户的编号、姓名、年龄在 user 表,订单编号在 orderlist 表

    条件:user.id = orderlist.uid

    SELECT
    	u.*,
    	o.number
    FROM
    	USER u,
    	orderlist o
    WHERE
    	u.id = o.uid;
  2. 查询所有的用户,显示用户的编号、姓名、年龄、订单编号。

    SELECT
    	u.*,
    	o.number
    FROM
    	USER u
    LEFT OUTER JOIN
    	orderlist o
    ON
    	u.id = o.uid;
  3. 查询用户年龄大于 23 岁的信息,显示用户的编号、姓名、年龄、订单编号

    SELECT
    	u.*,
    	o.number
    FROM
    	USER u,
    	orderlist o
    WHERE
    	u.id = o.uid
    	AND
    	u.age > 23;
    SELECT
    	u.*,
    	o.number
    FROM
    	(SELECT * FROM USER WHERE age > 23) u,-- 嵌套查询
    	orderlist o
    WHERE
    	u.id = o.uid;
  4. 查询张三和李四用户的信息,显示用户的编号、姓名、年龄、订单编号。

    SELECT
    	u.*,
    	o.number
    FROM
    	USER u,
    	orderlist o
    WHERE
    	u.id=o.uid
    	AND
    	u.name IN ('张三','李四');
  5. 查询所有的用户和该用户能查看的所有的商品,显示用户的编号、姓名、年龄、商品名称

    数据:用户的编号、姓名、年龄在 user 表,商品名称在 product 表,中间表 us_pro

    条件:us_pro.uid = user.id AND us_pro.pid = product.id

    SELECT
    	u.id,
    	u.name,
    	u.age,
    	p.name
    FROM
    	USER u,
    	product p,
    	us_pro up
    WHERE
    	up.uid = u.id
    	AND
    	up.pid=p.id;
  6. 查询张三和李四这两个用户可以看到的商品,显示用户的编号、姓名、年龄、商品名称。

    SELECT
    	u.id,
    	u.name,
    	u.age,
    	p.name
    FROM
    	USER u,
    	product p,
    	us_pro up
    WHERE
    	up.uid=u.id
    	AND
    	up.pid=p.id
    	AND
    	u.name IN ('张三','李四');