【400天挑战】热爱解题系列第157期（2018年3月10日）

&＃64;(跃迁之路)专栏

叨叨两句

1. ~

SQL习题052——MySQL多表查询基础巩固

--新建一个day09_exercise的数据库

create database day09_exercise;
use day09_exercise;


-- 用户表(user)
create table user (

&＃96;id&＃96; int auto_increment primary key, &＃96;username&＃96; varchar(50) -- 用户姓名 );


-- 订单表(orders)
create table orders (

&＃96;id&＃96; int auto_increment primary key, &＃96;price&＃96; double, &＃96;user_id&＃96; int );

-- 给订单表添加外键约束
alter table orders add constraint user_fk foreign key (user_id) references user(id);

-- 向user表中添加数据

insert into user values(3,&＃39;张三&＃39;);insert into user values(4,&＃39;李四&＃39;);insert into user values(5,&＃39;王五&＃39;);insert into user values(6,&＃39;赵六&＃39;);


-- 向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);

练习:
--查询用户的订单,没有订单的用户不显示

select *from user,orders where user.id&＃61;orders.user_id;
或者select * from user join orders on user.id&＃61;orders.user_id;

--查询所有用户的订单详情

select *from user left join orders on user.id&＃61;orders.user_id;

--查询所有订单的用户详情

select *from user right join orders on user.id&＃61;orders.user_id;

练习:
--查看用户为张三的订单详情

思路:查看用户为张三的订单详情select id from user where username &＃61; "张三";select * from orders where user_id &＃61; ?合二为一:
答案:select * from orders where user_id &＃61; (select id from user where username &＃61; "张三");


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

思路:select user_id from orders where price>300;-- 3 3 5 null
select * from user where id in ();合二为一:
答案:select * from user where id in (select user_id from orders where price>300);

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

SELECT *FROM USER,orders WHERE user.id&＃61;orders.user_id AND orders.price > 300;