欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql新手入门随笔4

程序员文章站 2022-05-24 20:14:12
40、子查询:出现在其他SQL语句里的SELECT语句 例如:SELECT sname,mark FROM student WHERE mark = (SELECT max(mark) FROM student); SELECT sname,mark FROM student WHERE mark ......

40、子查询:出现在其他SQL语句里的SELECT语句

例如:SELECT sname,mark FROM student WHERE mark = (SELECT max(mark) FROM student);

SELECT sname,mark FROM student WHERE mark in (SELECT max(mark) FROM student);

等效于:SELECT sname,mark FROM student WHERE mark =any (SELECT max(mark) FROM student);

in等效于=any

not in等效于!=all <>all

 

41、多表连接查询

内连接:INNER JOIN,JOIN,CROSS JOIN,NATURAL JOIN

意思:两张表的交集,仅显示符合连接条件的记录,不符合的不显示

用法:SELECT a.*,b.* FROM pa a INNER JOIN pb b ON a.id = b.id;

 

()连接:LEFT (OUTER) JOIN

意思:显示左表全部和左右表中符合连接条件的记录

用法:SELECT a.*,b.* FROM pa a LEFT (OUTER) JOIN pb b ON a.id = b.id;

 

()连接:RIGHT (OUTER) JOIN

意思:显示右表全部和左右表中符合连接条件的记录

用法:SELECT a.*,b.* FROM pa a RIGHT (OUTER) JOIN pb b ON a.id = b.id;

 

MySQL里的全连接

SELECT a.*,b.* FROM pa a LEFT (OUTER) JOIN pb b ON a.id = b.id

UNION

SELECT a.*,b.* FROM pa a RIGHT (OUTER) JOIN pb b ON a.id = b.id;

 

UNION:会对结果去重

UNION ALL:不会对结果去重