0%

mysql8基础操作(二)

mysql8基础操作

1.union(去重,需要更多的性能)和union all(不会去重,速度快)查询结果对比

1
2
SELECT * FROM A UNION SELECT * FROM B
SELECT * FROM A UNION ALL SELECT * FROM B

2.笛卡尔集现象

1
SELECT * FROM A,B

3.去除笛卡尔集,添加条件筛选

4.内连接查询,inner join 就等于 join

1
SELECT * FROM A name_a INNER JOIN B name_b on name_a.`name` = name_b.`name` WHERE name_b.score > 30;

5.左外连接查询,取左边的表的全部,右边的表按条件,符合的显示,不符合则显示null,left outer join 与 left join 等价, 一般写成left join

1
SELECT * FROM A name_a LEFT JOIN B name_b on name_a.`name` = name_b.`name`;

6.右外连接查询,取右边的表的全部,左边的表按条件,符合的显示,不符合则显示null,right outer join 与 right join等价,一般写成right join

1
SELECT * FROM A name_a RIGHT JOIN B name_b on name_a.`name` = name_b.`name`;

7.多表联合查询

1
SELECT * FROM A name_a JOIN B name_b RIGHT JOIN C name_c on name_a.`name` = name_b.`name` AND  name_b.`name` = name_c.`name`;

image-20201222111033326