SQL语句（多表关系）

第1章           多表关系实战

1.1     实战1：省和市

l 方案1：多张表，一对多

l 方案2：一张表，自关联一对多

 

1.2     实战2：用户和角色

l 多对多关系

1.3     实战3：角色和权限

l 多对多关系

1.4     实战4：客户和联系人(可选)

l 一对多：一个客户服务于多个联系人

 

第2章           多表查询

CREATE TABLE category (

  cidVARCHAR(32) PRIMARY KEY ,

  cnameVARCHAR(50)

);

CREATE TABLE products(

  pidVARCHAR(32) PRIMARY KEY ,

  pnameVARCHAR(50),

  price INT,

  flagVARCHAR(2),             #是否上架标记为：1表示上架、0表示下架

 category_id VARCHAR(32),

  CONSTRAINTproducts_fk FOREIGN KEY (category_id) REFERENCES category (cid)

);

 

2.1     初始化数据

#分类

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');

 

 

2.2     多表查询

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

n  语法：select * from A,B;

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

n  隐式内连接：select * from A,B where 条件;

n  显示内连接：select * from A inner join B on 条件;

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

n  左外连接：left outer join

u  select * from A left outer join B on 条件;

n  右外连接：right outer join

u  select * from A right outer join B on 条件;

 

#1.查询哪些分类的商品已经上架

#隐式内连接

SELECT DISTINCT c.cname FROM category c , productsp

    WHEREc.cid = p.category_id AND p.flag = '1';

 

#内连接

SELECT DISTINCT c.cname FROM category c

    INNERJOIN products p ON c.cid = p.category_id

    WHEREp.flag = '1';

 

        

 

#2.查询所有分类商品的个数

#左外连接

INSERT INTO category(cid,cname) VALUES('c004','奢侈品');

SELECT cname,COUNT(category_id) FROM category c

     LEFT OUTER JOIN products p

        ONc.cid = p.category_id

    GROUP BYcname;

 

          

 

下面通过一张图说明连接的区别:

        

2.3     子查询

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

select ....查询字段 ... from ... 表.. where ... 查询条件

 

#3 子查询,查询“化妆品”分类上架商品详情

#隐式内连接

SELECT p.* FROM products p , category c

    WHEREp.category_id=c.cid AND c.cname = '化妆品';

 

#子查询

##作为查询条件

SELECT * FROM products p

    WHEREp.category_id =

    (

        SELECTc.cid FROM category c

            WHEREc.cname='化妆品'

    );

    ##作为另一张表

    SELECT * FROM products p ,

            (SELECT * FROM category WHERE cname='化妆品') c

        WHERE p.category_id = c.cid;

 

        

 

#查询“化妆品”和“家电”两个分类上架商品详情

SELECT * FROM products p

    WHEREp.category_id in

    (

        SELECTc.cid FROM category c

            WHEREc.cname='化妆品' or c.name='家电'

    );