数据库(四) 多表查询

1.合并结果集

将多个select的结果合并在一起

select stuname from student union 
select sname from course;

⚠️：被合并的两个结果必须，列数目和列的数据类型相同
UNION将两个表查询到的不重复的stuname和sname整合到一起。
UNION ALL则是包含了重复项

SELECT STUNAME FROM STUDENT

2.连接查询

连接查询就是求出多个表的乘积，如t1连接t2,那么结果就是t1*t2 (这个乘积就是笛卡尔积)
t1的条数，t2的条数相乘。

SELECT * FROM T1,T2

但是该结果是不合理的，应该使用主外键的关系，去除不合理的数据。

SELECT * FROM emp1, dept1 WHERE emp1.deptno = dept1.deptno;

内连接查询：

SELECT COL_NAME
FROM TABLE1
INNER JOIN TABLE2
ON TABLE1.COL_NAME = TABLE2.COL_NAME2//外键列之间的关系
WHERE ....;

等价于

SELECT COL_NAME
FROM TABLE1 AS T1, TABLE2 AS T2
WHERE T1.COL_NAME = T2.COL_NAME2//外键列之间的关系
AND ....;

外连查询

外连接包括左外连接和右外连接，查询出的结果存在不满足条件的可能性

• 需求： 查询出所有部门和员工的对应关系，包括显示还没有员工的部门名称？
  左外连：

SELECT COL_NAME FROM MAIN_TABLE M LEFT JOIN SECONDARY_TABLE S
ON M.COL1 = S.COL2;

1. 主表的数据全部显示，次表的数据依靠匹配显示，能匹配到的显示数据，匹配失败的显示null值(员工表就是次表)
2. 主表和次表的位置不能随意替换
3. 使用场景 一般作为子语句使用

SELECT * FROM DEPT1 LEFT JOIN EMP1 ON DEPT1.deptno = EMP1.deptnum;

自然查询（开发中少使用的少）

等值连接，一般要求两个关系表中进行连接的必须有相同的属性列，并且无需添加连接条件，在结果中消除属性相同的列

select * from emp e natuaral join dept d;

子查询

常见于 WHERE 和 FROM 之后
常用关键字：any， all
比如高于部门id 为30的所有人工资的员工

SELECT EMPNAME, SAL FROM EMP WHERE SAL > ALL(
SELECT SAL FROM EMP WHERE DEPTID = 30); 

综合案例

1>查询用户的订单,没有订单的用户不显示

SELECT USERID,OID FROM USERS, ORDERS WHERE USER.USERID = ORDER.UID;

2>查询所有用户的订单详情

SELECT * FROM USERS LEFT JOIN ORDERS ON USER.USERID = ORDER.UID;

3>查询所有订单的用户详情

SELECT * FROM USERS RIGHT JOIN ORDERS ON USER.USERID = ORDER.UID;

4>查看用户为张三的订单详情

SELECT * FROM USERS, ORDERS WHERE USER.USERID = ORDERS.OID AND USERNAME = '张三';

5>查询出订单的价格大于300的所有用户信息。

SELECT * FROM USERS WHERE UID = (SELECT UID FROM USERS, ORDERS WHERE USERS.USERID = ORDERS.UID AND ORDERS.PRICE > 300);

6>查询订单价格大于300的订单信息及相关用户的信息。

SELECT * FROM USERS, ORDERS WHERE USERS.USERID = ORDERS.UID AND ORDERS.PRICE > 300;

7>查询所有订单信息，每页显示5条数据

SELECT * FROM ORDERS LIMIT 0,5;

扩展

多个表的更新/删除

多表更新

语法： UPDATE TABLE1 T1, TABLE2 T2 SET T1.COL1, T2.COL2 WHERE T1.COL_A = T2.COL_B AND …

修改职工’Rick’的工资为1000

UPDATE employee e, salary s SET s.salary = 1000 WHERE s.eid = e.empid AND e.name = 'Rick'; 

多表删除

去除人事部的信息

DELETE d,e,s FROM department d,employee e,salary s
WHERE d.depid=e.depid AND s.empid=e.empid AND depname='人事部';

数据库优化操作

1. 对查询进行优化:

1. 要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2. 尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如:

SELECT id FROM t WHERE num IS NULL;

3. 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。

4. 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如:

SELECT id FROM t WHERE num=10 OR Name = 'admin';

变更为下面这个：

SELECT id FROM t WHERE num = 10
UNION ALL
SELECT id FROM t WHERE Name = 'admin';

5. in 和 not in 也要慎用，否则会导致全表扫描，如:

SELECT id FROM t WHERE num IN(1,2,3);

对于连续的数值，能用 between 就不要用 in 了:

SELECT id FROM t WHERE num BETWEEN 1 AND 3;

很多时候用 exists 代替 in 是一个好的选择

2.数据库数值设定

最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.
备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。