数据库-查询基础操作

1.初始化数据

# 分类表
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','JACKJONES',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');

2.多表查询

交叉连接查询(基本不会使用-得到的是两个表的乘积) [了解]

• 语法： select * from A,B;

内连接查询(使用的关键字 inner join -- inner可以省略)

• 隐式内连接： select * from A,B where 条件;
• 显示内连接： select * from A inner join B on 条件;

#1.查询哪些分类的商品已经上架
#隐式内连接
SELECT DISTINCT c.cname FROM category c , products p WHERE c.cid = p.category_id AND p.flag = '1';
#内连接
SELECT DISTINCT c.cname FROM category c INNER JOIN products p ON c.cid = p.category_id WHERE p.flag = '1';

外连接查询(使用的关键字 outer join -- outer可以省略)

• 左外连接：left outer join
  • select * from A left outer join B on 条件;
• 右外连接：right outer join
  • select * from A right outer join B on 条件;

#2.查询所有分类商品的个数
#左外连接
INSERT INTO category(cid,cname) VALUES('c004','奢侈品');
SELECT cname,COUNT(category_id) FROM category c LEFT OUTER JOIN products p ON c.cid = p.category_id GROUP BY cname;

3.子查询

一条select语句结果作为另一条select语法一部分（查询条件，查询结果，表等）。

#3 子查询, 查询“化妆品”分类上架商品详情
#隐式内连接
SELECT p.* FROM products p , category c WHERE p.category_id=c.cid AND c.cname = '化妆品';
#子查询
##作为查询条件
SELECT * FROM products p WHERE p.category_id =(SELECT c.cid FROM category c WHERE c.cname='化妆品' );
 
#4 查询“化妆品”和“家电”两个分类上架商品详情
SELECT * FROM products p WHERE p.category_id in (SELECT c.cid FROM category c WHERE c.cname='化妆品' or c.name='家电');