mysql多表查询

-- 多表查询
# 分类表
CREATE TABLE category (
    cid VARCHAR (32) PRIMARY KEY,
    cname VARCHAR (50)
);

#商品表
CREATE TABLE products (
    pid VARCHAR (32) PRIMARY KEY,
    pname VARCHAR (50),
    price INT,
    flag VARCHAR (2),
    #是否上架标记为：1表示上架、0表示下架
    category_id VARCHAR (32),
    CONSTRAINT products_category_fk FOREIGN KEY (category_id) REFERENCES category (cid)
);

# 分类
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
#商品
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','联 想',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海 尔',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷 神',5000,'1','c001');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公⼦',440,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲 霸',2000,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','⾹奈⼉',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');

--  交叉查询
SELECT * from products,category；

select * from category；

-- 左外连接
SELECT * from products p LEFT JOIN category c on p.category_id=c.cid;

-- 右外连接
SELECT * from products p RIGHT JOIN category c ON p.category_id=c.cid;

-- 子查询
-- 查询家电和化妆品两个分类上的商品详情
SELECT
    p.*,c.cname
FROM
    products p
INNER JOIN category c ON c.cid = p.category_id
WHERE
    p.category_id IN (
        SELECT
            cid
        FROM
            category
        WHERE
            cname IN ('化妆品', '家电')
    )