mysql多表查询0529

/*
多表查询综合练习，测试显性查询和隐性查询，熟悉多表对应关系，写清中间表

显示内连接
标准语法：
SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 关联条件;

隐式内连接
标准语法：
SELECT 列名 FROM 表名1,表名2 WHERE 关联条件;

左外连接
标准语法：
SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;

右外连接
标准语法：
SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;
/
/
总结：
1.要查询的字段在哪些表中
2.多表的对应关系，例如：u.id=o.uid
3.搞清楚要查询的字段
*/

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

-- 笛卡尔积查询
SELECT * FROM USER,orderlist;

/*
显示内连接
标准语法：
SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 关联条件;
*/
--标准显性查询
SELECT u.name,u.age,o.number FROM USER u JOIN orderlist o ON u.id=o.uid;

-- 查询用户姓名，年龄。和订单编号
SELECT
u.name, -- 用户姓名
u.age, -- 用户年龄
o.number -- 订单编号
FROM
USER u -- 用户表
INNER 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
    u.name,      -- 用户姓名
    u.age,       -- 用户年龄
    o.number     -- 订单编号
FROM
    USER u,      -- 用户表
    orderlist o  -- 订单表
WHERE
    u.id=o.uid;

/*
总结：
1.要查询的字段在哪些表中
2.多表的对应关系，例如：u.id=o.uid
3.搞清楚要查询的字段
*/

外连接查询

--左外连接标准语法
SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件；
SELECT u.name,u.age,o.number FROM USER u LEFT OUTER JOIN orderlist o ON u.id=o.uid;

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

--右外连接标准语法
SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;
SELECT u.name,u.age,o.number FROM USER u RIGHT OUTER JOIN orderlist o ON u.id=o.uid;

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

SELECT * FROM USER WHERE age =(SELECT MAX(age) FROM USER);

SELECT * FROM orderlist WHERE uid NOT IN(SELECT id FROM USER WHERE NAME="张三" OR NAME="李四");

SELECT * FROM USER u,(SELECT * FROM orderlist WHERE id>4) o WHERE u.id=o.uid;