MySQL 数据库( 多表)

外键：

现在我们有两张表”分类表“和”商品表“，为了表明商品属于哪个分类，通常情况下，我们将在商品表上加一列，用于存放 cid 的信息，此列称为：外键

 

category(分类)

cid

cname

 

products(商品)

pid

name

price

category_id（外键字段）

此时”分类表 category“称为主表，”cid“我们称之为主键，”商品表  products“称为从表，”category_id“称为外键，我们通过主表的主键和从表的外键来描述主外键关系，呈现出的是一对多的关系。

 

外键特点：

从表外键值是 对主表主键的引用

从表外键类型必须和主表主键类型一致

 

声明外键约束：

语法：alter table 从表 add [constraint] [外键名称] foreign key(从表外键字段名) refereces 主表 (主表的主键)

 

创建分类表：

CREATE TABLE category(

cid VARCHAR(32) PRIMARY KEY,

cname VARCHAR(100)

);

INSERT INTO category(cid,cname) VALUES('c001','家电');

INSERT INTO category(cid,cname) VALUES('c002','服饰');

INSERT INTO category(cid,cname) VALUES('c003','化妆品');

 

 

 

创建商品表：

CREATE TABLE category(

cid VARCHAR(32) PRIMARY KEY,

cname VARCHAR(100)

);

INSERT INTO product(pid,pname,price,category_id) VALUES('p001','联想','5000','c001');

INSERT INTO product(pid,pname,price,category_id) VALUES('p002','海尔','5000','c001');

INSERT INTO product(pid,pname,price,category_id) VALUES('p003','雷神','5000','c001');

 

INSERT INTO product(pid,pname,price,category_id) VALUES('p004','JACK JONES','800','c002');

INSERT INTO product(pid,pname,price,category_id) VALUES('p005','真维斯','200','c002');

INSERT INTO product(pid,pname,price,category_id) VALUES('p006','花花公子','440','c002');

INSERT INTO product(pid,pname,price,category_id) VALUES('p007','劲霸','2000','c002');

 

INSERT INTO product(pid,pname,price,category_id) VALUES('p008','香奈儿','800','c003');

INSERT INTO product(pid,pname,price,category_id) VALUES('p009','相宜本草','200','c003');

 

建立关联：

alter table product add foreign key (category_id) references category(cid)

 

 

 

 

使用外键的目的：

保证数据的完整性

 

一对多建表原则：

在多的一方创建一个字段，字段作为外键指向一的一方的主键

 

多对多关系建表原则：

需要创建第三张表，中间表至少l两个字段，这两个字段分别作为外键指向另一方主键。

 

创建订单表：

CREATE TABLE orders(

    oid VARCHAR(32) PRIMARY KEY,

    totalprice DOUBLE

);

 

创建订单项表：

CREATE TABLE orderitem(

    oid VARCHAR(50),

    pid VARCHAR(50)

);

 

 

订单表和订单项表的主外键关系：

alter table orderitem add constraint orderitem_orders_fk foreign key(oid) references orders(oid);

 

商品表和订单项表的主外键关系：

alter table orderitem add constraint orderitem_product_fk foreign key(pid) references product(pid);

多表查询（三种方式及其区别）

1、交叉连接查询：

SELECT * FROM category,product;

1 queries executed, 1 success, 0 errors, 0 warnings

 

查询：select * from category,product LIMIT 0, 1000

 

返回了 27 行

 

执行耗时   : 0.042 sec

传送时间   : 0.001 sec

总耗时      : 0.044 sec

 

 

2、内连接查询（使用关键字 inner join --inner可以省略）：

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

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

3、外连接查询：（使用关键字 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 条件

 

现在看看左外连和右外连接的区别：

INSERT  INTO category VALUES('c004',NULL);

INSERT INTO product VALUES ('p010','海飞丝',0.5,NULL);

 

 

 

 

子查询：

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

 查询“化妆品”分类上架商品信息