sql实战 多表

实战

 有三张表

trips
id	INTEGER
date	TEXT
pickup	TEXT
dropoff	TEXT
rider_id	INTEGER
car_id	INTEGER
type	TEXT
cost	INTEGER

riders
id	INTEGER
first	TEXT
last	TEXT
username	TEXT
rating	INTEGER
total_trips	INTEGER
referred	INTEGER

cars
id	INTEGER
model	TEXT
OS	TEXT
status	TEXT
trips_completed	INTEGER

 1.将司机名字与汽车型号做一个简单的cross Join

SELECT riders.first,riders.last,cars.model
FROM riders,cars;

first	last	model
Sonny	Li	Ada
Sonny	Li	Ada
Sonny	Li	Turing XL
Sonny	Li	Akira
Laura	Breiman	Ada
Laura	Breiman	Ada
Laura	Breiman	Turing XL
Laura	Breiman	Akira

2.将罚单与人对应 使用left join

SELECT * 
FROM trips
LEFT JOIN riders
ON trips.rider_id=
riders.id;


！这样写也是没问题的
SELECT trips.date, 
   trips.pickup, 
   trips.dropoff, 
   trips.type, 
   trips.cost,
   riders.first, 
   riders.last,
   riders.username
FROM trips
LEFT JOIN riders 
  ON trips.rider_id = riders.id;

 

id	date	pickup	dropoff	rider_id	car_id	type	cost	id	first	last	username	rating	total_trips	referred
1001	2017-12-05	06:45	07:10	102	1	X	28.66	102	Laura	Breiman	@lauracle	4.99	687	101
1002	2017-12-05	08:00	08:15	101	3	POOL	9.11	101	Sonny	Li	@sonnynomnom	4.66	352
1003	2017-12-05	09:30	09:50	104	4	X	24.98	104	Yakov	Kagan	@yakovkagan	4.52	1910	103
1004	2017-12-05	13:40	14:05	105	1	X	31.27
1005	2017-12-05	15:15	16:00	103	2	POOL	18.95	103	Kassa	Korley	@kassablanca	4.63	42
1006	2017-12-05	18:20	18:55	101	3	XL	78.52	101	Sonny	Li	@sonnynomnom	4.66	352

3. 3.将罚单与车对应 使用inner join

select *
from trips
JOIN cars
ON trips.car_id=cars.id;