Mysql学习---DML语句表连接、子查询、记录联合

表连接

表连接分为内连接和外连接，外连接又分为左连接和右连接

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;