多表查询（MySQL）

最新推荐文章于 2024-03-04 15:03:37 发布

哒哒哒哒~

最新推荐文章于 2024-03-04 15:03:37 发布

阅读量206

点赞数

分类专栏： Java第二阶段学习总结

本文链接：https://blog.csdn.net/SjwFdb_1__1/article/details/100052799

版权

Java第二阶段学习总结专栏收录该内容

13 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL中多表的关系，包括一对一、一对多和多对多关系的建立。接着，讲解了多表查询的各类操作，如UNION、UNION ALL、交叉连接、内连接、外连接以及子查询的使用。最后，提到了如何在MySQL中创建用户和授权。

摘要由CSDN通过智能技术生成

1.多表的关系

一对多关系：在多的一方创建一个字段，字段作为外键指向一方的主键.
多对多关系：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键.
一对一关系：
两种建表原则：

唯一外键对应：在多的一方创建一个外键指向一的一方的主键，将外键设置为unique和非空.

主键对应：让一对一的双方的主键进行建立关系.

如下图：

2.多表查询

2.1 合并结果集 UNION、UNION ALL

作用：合并结果集就是把两个select语句的查询结果合并到一起！

UNION：去除重复记录，例如：SELECT* FROM t1 UNION SELECT * FROM t2；

UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。

如下图：

注意：用UNION或者UNION ALL合并两个结果：列数必须相同，列类型可以不同

2.1交叉连接（笛卡尔积，两个表的乘积）（两个表格的位置可以交换）

连接查询是将两个表格的每一行与另一个表格另一行进行组合。emp表有14行，dept表中有4行，所以共有56行数据。

SELECT * FROM emp,dept;会产生很多重复无用的数据：如下

可以根据外键，来过滤掉没用的信息。

SELECT * FROM emp,dept WHERE emp.`deptno`=dept.`deptno`;

2.2 内连接（两个的位置不能交换，有主次之分）

SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

特点：on后边条件成立是才能合并，与去重后的交叉连接结果相同

左外连接（左表为主表，右表为从表）

用外链接可以查到左表的所有行，无论该行的外键有没有数据都能查到。而内连接无法查到。

右外连接（与左外连接相反）同上

2.3 子查询

一个select语句中包含另一个完整的select语句。

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。里面的查询叫做子查询，外层的查询叫父查询，一般情况都是先执行子查询，再执行父查询。

l 子查询出现的位置：

a. where后，作为被查询的条件的一部分；

b. from后，作临时表；

l 当子查询出现在where后作为条件时，还可以使用如下关键字：

a. any

b. all

l 子查询结果集的常见形式：

a. 单行单列（用于条件）

b. 多行单列（用于条件）

c. 多行多列（用于表）

当子查询结果集形式为多行单列时可以使用ALL或ANY关键字

实例1：1. 工资高于JONES的员工。

#可以分两步完成
SELECT sal FROM emp WHERE ename = 'JONES';
SELECT * FROM emp WHERE sal > 2975;
#也可以直接一步完成
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'jones');

实例2：查询与SCOTT同一个部门的员工。

#两步完成该操作
SELECT deptno FROM emp WHERE ename='scott';
SELECT * FROM emp WHERE deptno = 20;
#一步完成该操作
SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename='scott');

案例3： 工资高于30号部门所有人的员工信息对关键字ALL的使用

SELECT * FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 30);

案例4：工资高于30号部门任意一个人的员工信息对关键字ANY的使用

SELECT * FROM emp WHERE sal > ANY(SELECT sal FROM emp WHERE deptno=30);

2.4 from后面子查询

SELECT ename,job,hiredate FROM (SELECT ename,job,hiredate FROM emp WHERE hiredate>'1987-1-1') AS temp;

把查询出来的数据存储成一个虚表，并且命名为temp。

创建用户和授权

创建用户

CREATE USER zhangsan IDENTIFIED BY '123'；等同于第4个，所有主机都可以登录
CREATE USER zhangsan @localhost IDENTIFIED BY '123';//指定只能在本机登录该用户
CREATE USER zhangsan @10.9.21.245 IDENTIFIED BY '123';//指定只能IP为10.9.21.245的主机可以登录该用户
CREATE USER zhangsan @`%` IDENTIFIED BY '123';//指定所有主机都能登录该用户

给用户授权：

GRANT ALL ON school.* TO `zhangsan`;

撤销权限：

REVOKE ALL ON school.* FROM `zhangsan`;

删除用户：

删除该用户是必须与创建时保持一致

例：删除第二个：drop user zhangsan @localhost