3.SQL多表操作

1.修改表-添加外键约束
外键约束：foreign key
添加的两张表：

给product中的这个cno添加一个外键约束：
alter table product add foreign key(cno) references category(cid);

在创建表时也可以直接添加外键约束：
create table A(
	foreign key(cno) references category(cid)，
);

删除注意事项：这里，category表中的cid就对product表中的cno产生了约束，product表中的cno栏的数值必须属于cid的一个值，此时不能删除category表中的出现在product表中cno一栏的值的项，除非先删除product表中相关的依赖项。另外，可以对category中有依赖的列的其他非依赖的属性值。

2.建表原则
建库原则：通常情况下，一个项目对应建一个数据库。

建表原则：
　　一对一：比如公民和身份证的关系，将一对一的情况当作是一对多的情况处理，在任意一张表添加一个外键，并且这个外键要唯一，指向另外一张表；可以将两张表合成一张表；将两张表的主键建立起连接，让两张表里的主键相等。实际应用：比如相亲网站，将个人信息进行拆分，例如拆分成表：（姓名，性别，年龄，身高，体重）和表：（职业，学历，年收入，特长）。
　　一对多：比如商品分类，在“多”的一方添加一个外键，指向“一”的一方的主键。
　　多对多：比如学生与课程，在多对多的建表过程中，多建一张中间表，将多对多的关系拆成一对多的关系，中间表至少要有两个外键，这两个外键分别指向原来的那张表。对于关系如下：　
学生表-中间表：一对多的关系，在学生表中，学号是唯一的，但是在中间表中，学号是可以有重复的，所以是一对多的关系。
课程表-中间表：一对多的关系，在课程表中，课程编号是惟一的，但是在中间表中，课程编号是可以重复的，所以是一对多是关系。

3.商城案例
(1)商城数据库所需的表
基本表：

用户地址表：

(2)数据表关系分析
用户表和订单表：由于一个用户可以对应于多个订单，

订单表和商品表：由于一个订单可以包含多种商品，且一种商品（比如：中华烟）可以属于多个订单，

商品表和商品分类表：一个商品分类（比如：香烟酒水）包含多种商品，

(3)建立商城项目数据库
创建用户表：(包含：用户的ID、用户名、密码、手机号)

创建用户表：
create table user(
    uid int primary key auto_increment,
    username varchar(31),
    password varchar(31),
    phone varchar(11)
    );

插入用户数据：
insert into user values(1,'zhangsan','123','13866666688');

查看用户表：

创建订单表：(包含：订单编号、总价、创建时间、地址、外键用户ID)

创建订单表：
create table orders(
    oid int primary key auto_increment,
    sum int not null,
    otime timestamp,
    address varchar(100),
    uno int,
    foreign key(uno) references user(uid)
    );

插入订单数据：
insert into orders values(1,200,null,'北京市西城区',1);
insert into orders values(2,250,null,'北京市海淀区',1);

查看订单表：

创建商品表：(包含：商品ID、商品名称、商品价格、外键cno)

创建商品表：
create table product(
    pid int primary key auto_increment,
    pname varchar(10),
    price double,
    cno int,
    foreign key(cno) references category(cid)
    );

插入商品信息：
insert into product values(null,'小米9',1999,1),
    (null,'锤子',2888,1),
    (null,'阿迪达斯',99,2),
    (null,'老村长',88,3),
    (null,'小熊饼干',2,4),
    (null,'无为板鸭',130,5);

查看商品表：

创建商品分类表：(包含：分类ID、分类名称、分类描述)

创建商品分类表：
create table category(
    cid int primary key auto_increment,
    cname varchar(15),
    cdesc varchar(100)
    );

插入商品分类信息：
insert into category values(null,'数码产品','玩游戏'),
    (null,'鞋靴箱包','鞋子靴子包'),
    (null,'酒水香烟','大中华等'),
    (null,'酸奶饼干','蒙牛'),
    (null,'小零食','辣条、星球杯');

查看商品分类表：

创建订单项（即中间表）：(包含：订单ID、商品ID、商品数量、订单项总价)

创建中间表：
create table orderitem(
	ono int,
	pno int,
	foreign key(ono) references orders(oid),
	foreign key(pno) references product(pid),
	ocount int,
	subsum double
	);

给1号订单添加商品：
insert into orderitem values(1,7,100,100);
insert into orderitem values(1,8,100,100);	

查看中间表：

分析：对于创建存在依赖关系的表，首先需要创建该表需要依赖的那张表，再创建该表；删除过程则相反，得先删除该依赖着别的表的表，才能删除其所依赖的表。

4.多表查询(使用SQLyog)
SQLyog：是数据库客户端软件的一种；

多表查询内连接和外连接：
笛卡尔积：查出来的是两张表的乘积，没有太大用处，

单纯的笛卡尔积：
select *from product,category;

笛卡尔积加上约束(as 可以省略)：
select *from product as p,category as c where p.cno=c.cid;

内连接查询：

隐式内连接：
select *from product as p,category as c where p.cno=c.cid;

显示内连接：
select *from product as p INNER JOIN category  c ON p.cno = c.cid;

区别：
　　隐式内连接：在查询出结果的基础上去做的where条件过滤；
　　显示内连接：带着条件去查询结果，执行效率更高。

外连接查询：
左外连接:

插入数据：
insert into product values(NULL,'大保健',100,NULL);

左外连接，会将左表中的所有数据都查询出来，如果右表中没有对应的数据，用NULL代替：
select *from product p LEFT OUTER JOIN category c ON p.cno = c.cid; 

注：这里的pid插入时为NULL是选择默认在使用过的pid的基础上顺延的方式，之前13-16被删除了。

右外连接:

插入数据：
insert into category values(100,'办公用品','笔墨纸砚');

右外连接，会将右表所有数据都查询出来，如果左表没有对应数据的话，用NULL代替：
select *from product p RIGHT OUTER JOIN category c ON p.cno = c.cid; 

分页查询：
格式：select *from product limit a,b;
这里的数字a代表开始索引（这里的开始索引以每页三条数据为例，可以计算为：(index - 1)*b），数字b代表一页的个数。

子查询：
示例1：

---查询分类名称为手机数码的所有商品
1.查询分类名称为手机数码的ID
select cid from category where cname='数码产品';
2.得出product表中cid为1的产品：
select *from product where cno=1;

3.利用子查询，合并1和2：
select *from product where cno=(select cid from category where cname='数码产品');

示例2：

---查询出商品名称，商品分类的信息
使用左连接：
select p.pname,c.cname from product p LEFT OUTER JOIN category c ON p.cno = c.cid;

使用子查询：
select p.pname,(select cname from category c where p.cno=c.cid) as '商品分类' from product p;