欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

数据库简单多表查询

程序员文章站 2022-05-29 22:04:40
...

数据库简单多表查询

1.第一步准备数据

1)创建数据库
2)创建分类表
3)创建商品表
4) 分类表和商品表数据初始化
#创建数据库
CREATE DATABASE webdb_3;
USE webdb_3;

##创建分类表
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_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 A inner join B on 条件;   (显式内连接)

查询哪些分类的商品已经上架:
SELECT cname FROM category,products WHERE cid = category_id AND flag = '1';

建议多表查询时,使用别名
SELECT DISTINCT c.cname FROM category c INNER JOIN products p ON c.cid = p.category_id WHERE p.flag = '1';

子查询

一条select语句的结果作为另一条select语法一部分(查询条件,查询结果,表等)

SELECT * FROM products p WHERE p.category_id = (SELECT cid FROM category WHERE cname = '化妆品') AND p.flag = '1';

举例:
查询“化妆品”或“家电”两个分类上架商品详情
SELECT p.* FROM products p , (SELECT * FROM category WHERE cname='化妆品' OR cname='家电') c WHERE c.cid = p.category_id AND p.flag = '1';
相关标签: 多表查询 入门