我有视频,
先给你视频笔记
九、多表查询
1、链接查询:(面试几率灰常大)
a、交叉链接:
SELECT * FROM customers,orders;返回的是两张表记录的笛卡尔积。(隐式查询:不使用关键字)
或者
SELECT * FROM customers CROSS JOIN orders;
b、内连接:
SELECT * FROM customers c,orders o WHERE c.id=o.customer_id;
或者
SELECT * FROM customers c INNER JOIN orders o ON c.id=o.customer_id;
c、外连接:
查询所有的客户信息,同时把对应的订单查询出来。
左外:返回符合链接条件的记录,同时返回左表中不满足链接条件的剩余记录
SELECT * FROM customers c LEFT OUTER JOIN orders o ON c.id=o.customer_id;
右外:
SELECT * FROM customers c RIGHT JOIN orders o ON c.id=o.customer_id;
2、几个简单的子查询
嵌套查询,子查询的语句放到小括号之内。
ID=1的这个老师教过哪些学员(语句的查询形式:多条SQL语句)
SELECT s_id FROM teacher_student WHERE t_id=1;
SELECT * FROM students WHERE id IN (1,2);
子查询:
SELECT * FROM students WHERE id IN (SELECT s_id FROM teacher_student WHERE t_id=1); |