数据库高级操作——查询

查询

1、子查询

子查询：允许把一个查询嵌套在另一个查询当中的查询

Table: student
+----+-----------+-----+-----+
| id | name      | age | sex |
+----+-----------+-----+-----+
|  1 | 小明      |  18 | 男  |
|  2 | 李四      |  20 | 女  |
|  3 | 王五      |  30 | 男  |
|  4 | 王明刚    |  40 | 男  |
+----+-----------+-----+-----+

Table: score
+------+------------+---------+-------+
| id   | student_id | subject | score |
+------+------------+---------+-------+
|    1 |          1 | 语文    |    80 |
|    2 |          2 | 语文    |    70 |
+------+------------+---------+-------+

举个例子：查询小明的语文成绩

SELECT score FROM score WHERE subject = '语文' AND student_id = (
SELECT id FROM student_info WHERE name = '小明'
);

2、联结查询

联结就是一种机制，用来在一条SELECT语句中关联表，因此称之为联结。 其意义在于：在用户查看数据的时候, 需要显示的数据来自多张表。

1. 内联结

又叫等值联结，基于两个表之间的相等测试，返回两个或者多个表之间相等关系的数据，用数学关系来看，相当于求交集。

内联结.JPG

语法：

SELECT * FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.column = t2.column;

举个例子：查询学生表中有成绩的学生

mysql> SELECT s1.name, s2.subject, s2.score FROM student_info AS s1 INNER JOIN score AS s2 ON s1.id = s2.student_id;
+--------+---------+-------+
| name   | subject | score |
+--------+---------+-------+
| 小明   | 语文    |    80 |
| 李四   | 语文    |    70 |
+--------+---------+-------+
2 rows in set (0.00 sec)

2. 外联结

外联结又可以分为左外联结和右外联结

左外联结：以左表为主表，返回左表中的所有数据

左外联结.JPG

语法：

SELECT * FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.column = t2.column;

举个例子：返回所有学生的成绩

mysql> SELECT s1.name, s2.subject, s2.score FROM student_info AS s1 LEFT JOIN score AS s2 ON s1.id = s2.student_id;
+-----------+---------+-------+
| name      | subject | score |
+-----------+---------+-------+
| 小明      | 语文    |    80 |
| 李四      | 语文    |    70 |
| 王五      | NULL    |  NULL |
| 王明刚    | NULL    |  NULL |
+-----------+---------+-------+
4 rows in set (0.00 sec)

右外联结：以右边为主表，返回右表中的所有数据

右外联结.JPG

语法：

SELECT * FROM table1 AS t1 RIGHT JOIN table2 AS t2 ON t1.column = t2.column;

举个例子：返回有成绩的学生

select t1.name,t2.subject,t2.score from student as t1 right join score as t2 on t1.id=t2.student_id;
+-----------+---------+-------+
| name      | subject | score |
+-----------+---------+-------+
| 小明      | 语文    |    80 |
| 李四      | 语文    |    70 |
+-----------+---------+-------+

3、组合查询

MySQL允许执行多个查询(多条SELECT语句)，并将结果作为单个查询结果集返回。这些组合查询通常称为并(union)或符合查询(compound query)。

语法：

SELECT * FROM table1 WHERE condition 
UNION
SELECT * FROM table2 WHERE condition;

举个例子：查询学生表中年龄超过20岁或者是男生的

mysql> SELECT * FROM student_info WHERE age > 20 
    -> UNION 
    -> SELECT * FROM student_info WHERE sex = '男';
+----+-----------+-----+-----+
| id | name      | age | sex |
+----+-----------+-----+-----+
|  3 | 王五      |  30 | 男  |
|  4 | 王明刚    |  40 | 男  |
|  1 | 小明      |  18 | 男  |
+----+-----------+-----+-----+
3 rows in set (0.05 sec)

三种查询方式的区别：

1、查询过程的区别：

子查询是由内到外的查询；联结查询是从多张表中取出信息进行匹配；组合查询是同时执行多条查询语句，并将查询结果一起返回

2、查询效率的区别：

子查询是从里到外的查询，效率比较低；

3、使用场景的区别：

• 查询的表过多时，子查询嵌套结构复杂，可读性低；
• 联结查询用于从多张表中获取数据；
• 在单个查询中，从不同表中返回类似结构的数据以及对单个表执行多个查询，按照单个查询返回数据时，使用组合查询