MySQL数据库基础下

一、修改表——添加外键约束二、多表之间的建表原则1.建数据库原则：通常情况下，一个项目/应用建一个数据库2.多表之间的表原则：（1）一对多：分类和商品 （一个分类对应多个商品）（2）多对多：老师和学生、学生和课程（3）一对一：公民和身份证、班级和班长三、商城案例——建表和数据准备四、使用商城表实现对商品信息的多表查询五、子查询

一、修改表——添加外键约束

mysql> select * from product;+-----+--------------+-------+---------------------+------+| pid | pname | price | pdate| cno |+-----+--------------+-------+---------------------+------+| 1 | 锤子 | 2888 | -08-07 13:58:51 | 1 || 2 | 小米11 | 998 | -08-06 17:13:10 | 1 || 3 | 威龙辣条|8 | -08-06 17:16:45 | 3 || 4 | 拖鞋 |8 | -08-06 17:17:05 | 4 || 5 | 毛巾 |8 | -08-06 17:17:22 | 4 || 6 | 老村长 | 88 | -08-07 13:54:28 | 2 || 7 | 敬酒 | 99 | -08-07 13:55:20 | 2 || 8 | 小熊饼干|4 | -08-07 13:55:57 | 3 || 9 | 旺旺大饼|5 | -08-07 13:56:22 | 3 |+-----+--------------+-------+---------------------+------+

mysql> select * from category;+-----+-----------+--------------------+| cid | cname| cdesc |+-----+-----------+--------------------+| 1 | 酒水| 茅台、二锅头 || 2 | 日用品 | 电子产品 || 3 | 衣服| 外套|| 4 | 零食| 辣条|+-----+-----------+--------------------+

1.技术分析：

多表之间的关系如何来维护？

外键约束：foreign key，给product中的这个cno添加一个外键约束

mysql> alter table product add foreign key(cno) references category(cid);Query OK, 9 rows affected (0.06 sec)

2.从分类表中删除分类信息为5的信息。

delete from category where cid = 5;//删除失败

首先得去product表删除所有分类ID5的商品。

==删除的时候，先删除外键关联的所有数据，然后才能删除分类的数据。

主键约束：默认不能为空，唯一

a.外键都是指向另外一张表的主键

b.一张表只能有一个主键

唯一约束：列里面的内容必须唯一，不能出现重复的情况、为空

a.唯一约束不能作为其他表的外键

b.一张表可以有多个唯一约束。

二、多表之间的建表原则

1.建数据库原则：通常情况下，一个项目/应用建一个数据库

2.多表之间的表原则：

（1）一对多：分类和商品 （一个分类对应多个商品）

建表原则：在多的一方添加一个外键，指向一的一方的主键

mysql> alter table product add foreign key(cno) references category(cid);

（2）多对多：老师和学生、学生和课程

建表原则：多建一张中间表，将多对多的关系拆成一对多的关系，中间表至少要有两个外键。这两个外键分别指向原来的那张表。

案例：学生和选课

学生表：student

课程表：course

安逸选了：Java、Mysql

神秘的天：Java 、UI 、Android

天山派盟主：Java 、UI 、Android、Mysql

中间表：(学生表(n)——>(1)中间表；课程表(n)——>(1)中间表)

（3）一对一：公民和身份证、班级和班长

建表原则：

a.将一对一的情况，当作是一对多的情况处理，在任意一张表添加一个外键，并且这个外键要唯一，指向另外一张表。

b.直接将两张表合并成一张表。

c.将两张表的主键建立起连接，让两张表里面的之间相同。

应用案例：实际用途不是很多，通常用于拆表操作。

相亲网站：

个人信息：姓名、性别、年龄、身高、体重、兴趣爱好（年收入、特长、学历、职业、择偶要求）

拆表操作：将个人的常用信息和不常用信息存放在不同的表中，减少表的臃肿。

三、商城案例——建表和数据准备

1.所有的表：

2.建表：

（1）学生表

mysql> create table user(-> uid int primary key auto_increment,-> username varchar(31),-> password varchar(31),-> phone varchar(11)-> );

添加数据：

insert into user(null,‘安逸’,‘123’,‘17611149396’);

mysql> insert into user values(1,'anyi','123','17611149396');

(2)订单表（订单编号，总价，订单时间，地址，外键-用户的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));

mysql> insert into orders values(1,250,null,'CSDN大学6-212',1);

（3）商品分类表：（分类id，分类名称，分类描述）

mysql> create table category(-> cid int primary key auto_increment,-> cname varchar(15),-> cdesc varchar(100)-> );

添加数据：

mysql> insert into category values(null,'手机数码','小米生产，电子产品');mysql> insert into category values(null,'鞋靴箱包','江南皮鞋厂造');mysql> insert into category values(null,'香烟酒水','黄鹤楼，茅台，二锅头');mysql> insert into category values(null,'酸奶饼干','哇哈哈，蒙牛酸奶');mysql> insert into category values(null,'馋嘴零食','瓜子花生，辣条');

（4）订单项：中间表（订单id，商品id，商品数量，订单项总价）

mysql> create table prderitem(-> ono int,-> pno int,-> foreign key(ono)references orders(oid),-> foreign key(pno)references product(pid),-> ocountn int,-> subsum double-> );

——给1号订单添加商品 200块钱的商品

mysql> insert into orderitem values(1,7,100,100);mysql> insert into prderitem values(1,8,101,100);

——给2号订单添加商品 250块钱的商品

mysql> insert into orderitem values(2,5,1,35);mysql> insert into prderitem values(2,3,3,99);

（5）商品表：（商品ID，商品名称，商品价格，外键cno）

mysql> create table product(-> pid int primary key auto_increment,-> pname varchar(10),-> price double,-> cno int,-> foreign key(cno) references category(cid)-> );

添加数据：

mysql> insert into product values(null,'小米11',998,1);mysql> insert into product values(null,'锤子',2888,1);mysql> insert into product values(null,'阿迪王',99,2);mysql> insert into product values(null,'老村长',88,3);mysql> insert into product values(null,'敬酒',35,3);mysql> insert into product values(null,'小熊饼干',1,4);mysql> insert into product values(null,'威龙辣条',1,5);mysql> insert into product values(null,'五味花生',1,5);

四、使用商城表实现对商品信息的多表查询

1.交叉连接查询：笛卡尔积,查出来是两张表的乘积，查出来的结果没有意义。

select * from product,category;

过滤出有意义的数据

select *from product,category where cno=cid;

select *from product p,category c where o=c.cid;

2.内连接查询：

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

显示内连接：select *from product p inner join category c on o=c.cid;

隐式内连接和显示内连接的区别：两者查询结果是一样的

隐式内连接：在查询出结果的基础上去做where条件过滤。

显示内连接：带着条件去查询结果，执行效率要高。

3.左外连接：会将左表中的所有数据都查询出来，如果右表中没有对应的数据，用null代替。

select * from product p left outer join category c on o=c.cid;

4.右外连接：会将右表中的所有数据都查询出来，如果左表中没有对应的数据，用null代替。

select * from product p right outer join category c on o=c.cid;

5.分页查询：

每页数据3

起始索引从0开始（startindex = (index-1)*3）

第一页：0 select *from product limit 0,3;

第二页：3 select *from product limit 3,3;

select *from product limit 0,5;

第一个参数是索引，第二个参数显示的是一个页面数据个数。

五、子查询

1.查询分类名为手机数码的所有商品

（1）查询分类名为手机数码的id

select cid from category where cname=‘手机数码’;

（2）得出id为1的结果

select * from product where cno = (select cid from category where cname=‘手机数码’);

mysql> select * from product where cno = (select cid from category where cname='手机数码');+-----+--------+-------+-----+| pid | pname | price | cno |+-----+--------+-------+-----+| 1 | 小米11 | 998 | 1 || 2 | 锤子 | 2888 | 1 |+-----+--------+-------+-----+

2.查询出（商品名称、商品分类名称）信息

方法一——左连接

select p.pname,ame from product p left outer join category c on o=c.cid;

mysql> select p.pname,ame from product p left outer join category c on o=c.cid;+----------+----------+| pname | cname |+----------+----------+| 小米11 | 手机数码 || 锤子| 手机数码 || 阿迪王 | 鞋靴箱包 || 老村长 | 香烟酒水 || 敬酒| 香烟酒水 || 小熊饼干 | 酸奶饼干 || 威龙辣条 | 馋嘴零食 || 五味花生 | 馋嘴零食 |+----------+----------+

方法二——子查询：

select pname,(select cname from category c where o=c.cid)as 商品名称 from product p;

mysql> select pname,(select cname from category c where o=c.cid)as 商品名称 from product p;+----------+----------+| pname | 商品名称 |+----------+----------+| 小米11 | 手机数码 || 锤子| 手机数码 || 阿迪王 | 鞋靴箱包 || 老村长 | 香烟酒水 || 敬酒| 香烟酒水 || 小熊饼干 | 酸奶饼干 || 威龙辣条 | 馋嘴零食 || 五味花生 | 馋嘴零食 |