MysqlSQL语句2☞联表查询、自连接、分页和排序、子查询、常用函数、聚合函数、MD5加密

联表查询

where等值查询

on 连接查询

1.内连接

SELECT `grade`,s.id,`name` FROM student  [AS] s INNER JOIN result [AS] r WHERE s.id=r.id

student和result都有的显示

Oracle有full join ，但MySQL中没有，使用union达到目的

2.全连接

SELECT * FROM student LEFT JOIN result ON student.id=result.id 
UNION 
SELECT * FROM student RIGHT JOIN result ON student.id=result.id

student有的和result有的

3.并集去交集

SELECT * FROM student LEFT JOIN result ON student.id=result.id
WHERE result.id IS NULL 
UNION 
SELECT * FROM student RIGHT JOIN result ON student.id=result.id 
WHERE student.id IS NULL 

student和result的并集去除都有的交集（student和result独有的）

4.左连接

SELECT `grade`,s.id,`name` FROM student  AS s LEFT JOIN result AS r ON s.id=r.id

student表有的都显示，(result有的)student没有的不显示

5.左独有