六、多表sql操作

一、多表外键约束

1.1一对多

用户和订单表关系

创建用户表

创建商品表

为了保证数据的有效性和完整性,添加约束(外键约束).

在多表的一方添加外键约束

alter table 多表名称 add foreign key(外键名称) references 一表名称

前提是从表必须先有user_id这个字段,否则会添加失败,如下所示:

也可以在创建表的时候就设置外键约束,例如:

teacher表对student表的一对多的关系，需要在多的一方(student)添加另一方(teacher)的主键作为外键约束。

CREATE TABLE teacher (

  id int(11) NOT NULL primary key auto_increment,

  name varchar(20) not null unique

 );

CREATE TABLE student (

  id int(11) NOT NULL primary key auto_increment,

  name varchar(20) NOT NULL unique,

  score double default '0',   //默认值

  teacher_id int(11) NOT NULL,

  FOREIGN KEY (teacher_id) REFERENCES teacher (id)

  ) ;

添加了外键约束之后有如下特点:

1.主表中不能删除从表中已引用的数据

2.从表中不能添加主表中不存在的数据

如果存在外键约束,直接删除主表或者从表都会失败,提示存在外键约束

如何删除外键约束?

通过查看从表的建表语句来查看外键值,然后使用如下命令来删除外键.

alter table 表名 drop foreign key 外键值

然后就可以删除该字段了

同时也可以删除主表了

1.2多对多

商品和订单表的关系

创建商品表

订单表

创建中间表

在中间表添加外键约束

二、多表查询

笛卡尔积: 多张表无条件的联合查询.没有任何意思

select a.*,b.* from a,b;

2.1内连查询

格式1:显式的内连接

select a.*,b.* from a [inner] join b on ab的连接条件

格式2:隐式的内连接

select a.*,b.* from a,b where ab的连接条件

2.2外连接查询

左外连接： select a.*,b.* from a left [outer] join b on 连接条件;

含义：先展示join左边的(a)表的所有数据,根据条件关联查询 join右边的表(b),符合条件则展示出来,不符合以null值展示.

右外连接: select a.*,b.* from b right [outer] join a on 连接条件;

含义：先展示jion右边的表(a)表的所有数据,根据条件关联查询join左边的表(b),符合条件则展示出来,不符合以null值展示.

2.3子查询

一个查询依赖另一个查询.

1.当子查询的结果只有一个时,可以用=连接

2.当子查询的结果有某几个时,可以用in连接

3.当子查询的结果是一个表时,可以给表起别名

练习

添加测试数据

-- 向user表中添加数据

insert into user values(3,'张三');

insert into user values(4,'李四');

insert into user values(5,'王五');

insert into user values(6,'赵六');

-- 向orders 表中插入数据

insert into orders values(1,1314,3);

insert into orders values(2,1314,3);

insert into orders values(3,15,4);

insert into orders values(4,315,5);

insert into orders values(5,1014,null);

1.查询用户的订单,没有订单的用户不显示

隐式内连接:

显示内连接

2.查询所有用户的订单详情

左外连接: user在左

右外连接:orders 在右

3.查看用户为张三的订单详情

4.查询出订单的价格大于300的所有用户信息。

5.查询订单价格大于300的订单信息及相关用户的信息。

或者，使用是将一个查询的结果作为一张临时表