常用的SQL语句大全-多表操作

以案例的方式学习

一对多

购物商城的实体:
    用户 订单 商品 分类
常见关系:
    一对多. 用户和订单  分类和商品
    多对多. 订单和商品  学生和课程
    一对一. 丈夫和妻子  
ER图可以描述实体于实体之间的关系
    实体用矩形表示
    属性用椭圆表示
    关系用菱形表示
一对多:
    用户和订单   
    -- 创建用户表
    create  table user(
        id int primary key auto_increment,
        username varchar(20)
    );

    -- 创建订单表
    create  table orders(
        id int primary key auto_increment,
        totalprice double,
        user_id int
    );

    为了保证数据的有效性和完整性,添加约束(外键约束).
        在多表的一方添加外键约束
            格式:
                alter table 多表名称 add foreign key(外键名称) references 一表名称(主键);
            例如:
                alter table orders add foreign key(user_id) references user(id);
    添加了外键约束之后有如下特点:★
        1.主表中不能删除从表中已引用的数据
        2.从表中不能添加主表中不存在的数据
    开发中处理一对多:★
        在多表中添加一个外键,名称一般为主表的名称_id,字段类型一般和主表的主键的类型保持一致,
        为了保证数据的有效性和完整性,在多表的外键上添加外键约束即可.

多对多

例子:商品和订单
        -- 创建商品表
    create table product(
        id int primary key auto_increment,
        name varchar(20),
        price double
    );

    -- 创建中间表
    create table orderitem(
        oid int,
        pid int
    );

    -- 添加外键约束
    alter table orderitem add foreign key(oid) references orders(id);
    alter table orderitem add foreign key(pid) references product(id);

开发中处理多对多:★

• 引入一张中间表,存放两张表的主键,一般会将这两个字段设置为联合主键,这样就可以将多对多的关系拆分成两个一对多了
• 为了保证数据的有效性和完整性需要在中间表上添加两个外键约束即可.

笛卡尔积:了解

• 多张表无条件的联合查询.没有任何意义

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

内连接:★

格式1:显式的内连接

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

格式2:隐式的内连接

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

练习：     
查询用户的订单,没有订单的用户不显示
隐式内连接:
    select user.*,orders.* from user ,orders where user.id=orders.user_id;
显示内连接
    select user.*,orders.* from user join orders on user.id=orders.user_id;

外连接:★

左外连接:★

 select a.*,b.* from a left [outer] join b on 连接条件;
 ```
- 意思:先展示join左边的(a)表的所有数据,根据条件关联查询 join右边的表(b),符合条件则展示出来,不符合以null值展示.
######  右外连接:
```txt
select a.*,b.* from b right [outer] join a on 连接条件;




<div class="se-preview-section-delimiter"></div>

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

查询所有用户的订单详情
    左外连接: user在左
        select user.*,orders.* from user left join orders on user.id=orders.user_id;
查询所有订单的用户详情
    右外连接:orders 在右
        select orders.*,user.* from user right join orders on user.id=orders.user_id;




<div class="se-preview-section-delimiter"></div>

子查询:★

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

练习：
查询订单价格大于300的订单信息及相关用户的信息。
查看用户为张三的订单详情
    1.先查询张三的id
        select id from User where username = '张三';// 3
    2.select * from orders where user_id = ?;

    两个合二为一
        select * from orders where user_id = (select id from User where username = '张三');
查询出订单的价格大于300的所有用户信息。
    1.先查询出订单价格>300的用户的id
        select user_id from orders where price >300;//(3,3,5,null)
    2.select * from user where id in(3,3,5,null);

    两个合二为一:
        select * from user where id in(select user_id from orders where price >300);
    内连接:
        select orders.*,user.* from orders,user where user.id=orders.user_id  and orders.price>300 ;

    子查询:    是将一个查询的结果作为一张临时表        
        select user.*,tmp.* from user,(select * from orders where price>300) as tmp where user.id=tmp.user_id;

一对一:(了解)

• 1.两个实体合二为一(字段比较少)
• 2.将一张表的主键添加外键约束即可