【第二阶段 | 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),category_id varchar (32), — 添加外键约束 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 category(cid,cname) values(‘c004’,’汽车’); #商品数据 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’,’美 的冰箱’,4500,’1’,’c001’); INSERT INTO products (pid, pname,price,flag,category_id) VALUES(‘p004’,’篮 球鞋’,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’,’T 恤’,300,’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’);

— 1) 笛卡尔积

— 语法 SELECT 字段名 FROM 表1, 表2; select * from category,products;
— 得到的结果是无法使用的

— 2)进阶 笛卡尔积基础+where条件 过滤出有效数据

select * from category c ,products p; where c.cid=p.category_id;

— 3)内连接

• 内连接的特点:

  • 通过指定的条件去匹配两张表中的数据, 匹配上就显示,匹配不上就不显示
  • 比如通过: 从表的外键 = 主表的主键 方式去匹配

    1)

    — 笛卡尔积+where 查询家电分类所有商品 select from category c ,products p; where c.cid=p.category_id and cname=’家电’; — 查询格力空调属于哪个分类 select from category c ,products p where c.cid=p.category_id and pname=’格力空调’;

    — 显式内连接

    select * from category c inner join products p on c.cid=p.category_id;

    — 左外关联

    select * from category c left join products p on c.cid=p.category_id;

    — 查询每个分类下的的商品个数

    select c.cname,count(p.pname) from category c left join products p on c.cid=p.category_id group by c.cname;

    — 右外关联

• 按照左关联理解

子查询

数据准备

create table customers( id int primary key auto_increment, name varchar(20), sex varchar(20), address varchar(20), salary int ); create table orders( oid int primary key auto_increment, date varchar(20), customers_id varchar(20), amount int ); insert into customers (name, sex, address, salary) values (‘孙悟空’, ‘男’, ‘花果山’,2000); insert into customers (name, sex, address, salary) values (‘猪八戒’, ‘男’, ‘高老庄’,1500); insert into customers (name, sex, address, salary) values (‘唐僧’, ‘男’, ‘东土大唐’,3000); insert into customers (name, sex, address, salary) values (‘沙僧’, ‘男’, ‘流沙河’,2500); insert into customers (name, sex, address, salary) values (‘女儿国王’, ‘女’, ‘女儿国’,10000); insert into customers (name, sex, address, salary) values (‘黄蓉’, ‘女’, ‘桃花岛’,7500); insert into customers (name, sex, address, salary) values (‘郭静’, ‘男’, ‘牛家村’,6000); insert into orders (oid, date, customers_id, amount) values (102, ‘2019- 12-08 00:00:00’, 3, 2000); insert into orders (oid, date, customers_id, amount) values (100, ‘2019- 10-06 00:00:00’, 3, 1500); insert into orders (oid, date, customers_id, amount) values (101, ‘2019- 09-20 00:00:00’, 6, 3000); insert into orders (oid, date, customers_id, amount) values (103, ‘2020- 05-20 00:00:00’, 5, 5000); — select id,name,amount,date from customers c left join orders o on c.id=o.customers_id; union SELECT Id,NAME,Amount,Date from customers RIGHT JOIN orders on customers.Id = orders.Customers_Id;

— 通过子查询的方式, 查询价格最高的商品信息

select max(price) from products; select from products where price =5000; select from products where price =(select max(price) from products);

— 查询化妆品分类下的 商品名称 商品价格

— 先查出化妆品分类的分类id select cid from category where cname=’化妆品’ select from products where category_id=’c003’ select from products where category_id=( select cid from category where cname=’化妆品’); — 查询小于平均价格的商品信息 — 查询平均价格 select avg(price) from products ;

— 查询小于平均价格的商品

select * from products where price <(select avg(price) from products)
— 子查询单列多行

— 查询价格小于两千的商品,来自于哪些分类(名称)

— 查询出价格小于2000的商品信息

select distinct category_id from products where price <2000; select * from category where cid in (select distinct category_id from products where price <2000);

— 查询家电类 与 鞋服类下面的全部商品信息

select cid from category where cname in (‘家电’,’鞋服’); select * from products where category_id in(select cid from category where cname in (‘家电’,’鞋服’))