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

数据查询:
-
查询用户的编号、姓名、年龄、订单编号
数据:用户的编号、姓名、年龄在 user 表,订单编号在 orderlist 表
条件:user.id = orderlist.uid
SELECT u.*, o.number FROM USER u, orderlist o WHERE u.id = o.uid; -
查询所有的用户,显示用户的编号、姓名、年龄、订单编号。
SELECT u.*, o.number FROM USER u LEFT OUTER JOIN orderlist o ON u.id = o.uid; -
查询用户年龄大于 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; -
查询张三和李四用户的信息,显示用户的编号、姓名、年龄、订单编号。
SELECT u.*, o.number FROM USER u, orderlist o WHERE u.id=o.uid AND u.name IN ('张三','李四'); -
查询所有的用户和该用户能查看的所有的商品,显示用户的编号、姓名、年龄、商品名称
数据:用户的编号、姓名、年龄在 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; -
查询张三和李四这两个用户可以看到的商品,显示用户的编号、姓名、年龄、商品名称。
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 ('张三','李四');