表连接
表连接分为内连接和外连接,外连接又分为左连接和右连接
1.内连接查询仅显示出两张表中互相匹配的数据
select student.stuno,student.name,score.math from student,score where student.stuno=score.stuno;
2.左连接查询显示左边表数据及右表中没有的数据
select student.stuno,student.name,score.math from student left join score on student.stuno=score.stuno;
3.右连接查询显示右边表数据及左表中没有的数据
select student.stuno,student.name,score.math from student right join score on student.stuno=score.stuno;
子查询
子查询关键字包括 in、not in、= 、!=、exists、not exists等
select * from score where stuno in(select stuno from student);
如果记录数只有一条,可以用=代替in
select * from score where stuno=(select stuno from student limit 1);
记录联合
将两张表的查询结果并在一起显示,关键字union、union all
MariaDB [test1]> select * from student
-> union all
-> select * from score;
去掉重复结果
MariaDB [test1]> select stuno from student
-> union
-> select stuno from score;