4.JavaWeb&MySQL多表查询与事务的操作

目录

导语：

一、回顾

1.DQL查询

2.约束

3.表与表之间的关系

4.数据库的三大范式

二、表连接查询

1.多表查询的作用

2.笛卡尔积现象

3.内连接

（1）隐式内连接

（2）显式内连接

4.左外连接

5.右外连接

三、子查询

1.子查询的概念

2.子查询结果的三种情况

3.单行单列子查询

4.多行单列子查询

四、事务

1.事务的应用场景

2.手动提交事务

3.自动提交事务

4.事务原理

5.回滚点

6.事务的隔离级别

五、DCL

1.创建用户

2.授权

3.撤销授权

4.查看权限

5.删除用户

6.修改管理员密码

7.修改普通用户密码

结语：

---

导语：

    MySQL作为一种广泛使用的数据库，其查询和事务操作是Java开发中非常重要的技能。本文将为您详细介绍MySQL中的多表查询与事务操作。

一、回顾

1.DQL查询

   （1）排序：order by

   （2）聚合函数：count、max、min、sum、avg

   （3） 分页查询：limit

   （4）分组查询：group by

2.约束

（1）约束关键字

（2）自增长：auto_increment

（3）级联操作：on update cascade、on delete cascade

3.表与表之间的关系

（1）一对多

（2）多对多

（3）一对一

4.数据库的三大范式

详细内容可看：

3.JavaWeb&MySQL约束与设计

MySql约束与设计

负笈修业，公众号：微澜网络3.JavaWeb&MySQL约束与设计

二、表连接查询

1.多表查询的作用

    多表查询可以用于查询多个表中的数据。例如，查询员工信息及其所在部门信息：

SELECT e.name, d.name 
FROM emp e 
JOIN dept d ON e.dept_id = d.id;

2.笛卡尔积现象

    当没有条件限制时，多表查询会返回所有可能的组合结果，这称为笛卡尔积。例如：

SELECT * FROM emp, dept;

3.内连接

    内连接返回符合连接条件的记录。

（1）隐式内连接

SELECT e.name, d.name 
FROM emp e, dept d 
WHERE e.dept_id = d.id;

（2）显式内连接

SELECT e.name, d.name 
FROM emp e 
JOIN dept d ON e.dept_id = d.id;

4.左外连接

    左外连接返回左表的所有记录，即使右表中没有匹配的记录，右表字段会返回NULL。

SELECT e.name, d.name 
FROM emp e 
LEFT JOIN dept d ON e.dept_id = d.id;

5.右外连接

    右外连接返回右表的所有记录，即使左表中没有匹配的记录，左表字段会返回NULL。

SELECT e.name, d.name 
FROM emp e 
RIGHT JOIN dept d ON e.dept_id = d.id;

    以上是表连接查询的拓展内容，包括案例展示了如何使用不同类型的连接查询多表数据。

三、子查询

1.子查询的概念

    子查询是嵌套在其他查询中的查询语句，它可以作为另一个查询的条件或返回结果集的一部分。

2.子查询结果的三种情况

• 单行单列：返回单个值，如最大值、最小值、平均值等。

• 多行单列：返回多个值，形成数组或集合。

• 多行多列：返回多个字段的多行数据。

3.单行单列子查询

-- 查询工资最高的员工信息
SELECT * 
FROM emp 
WHERE salary = (SELECT MAX(salary) FROM emp);

4.多行单列子查询

-- 查询工资大于5000的员工所在的部门名称
SELECT name 
FROM dept 
WHERE id IN (SELECT dept_id FROM emp WHERE salary > 5000);

5.多行多列子查询

-- 查询2011年入职的员工信息及其所在部门信息
SELECT * 
FROM (SELECT * FROM emp WHERE join_date >= '2011-1-1') e
JOIN dept d ON e.dept_id = d.id;

    以上内容涵盖了子查询的基本概念、分类以及各种子查询的使用案例，有助于深入理解子查询在SQL中的应用。

四、事务

1.事务的应用场景

    事务用于保证一系列SQL操作要么全部成功，要么全部失败。典型的应用场景包括银行转账、在线购物支付等。

2.手动提交事务

BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;

3.自动提交事务

    MySQL默认每条SQL语句都是一个单独的事务，执行完毕后会自动提交。

4.事务原理

    事务开启后，所有操作先写入事务日志，提交时将日志应用到数据库，回滚时清除日志。

5.回滚点

BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 1;
SAVEPOINT sp1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
ROLLBACK TO sp1;
COMMIT;

6.事务的隔离级别

-- 查看隔离级别
SELECT @@tx_isolation;

-- 设置隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

    以上内容涵盖了事务的基本概念、提交方式、原理、回滚点以及隔离级别等内容，并提供了相关代码案例，有助于深入理解事务在MySQL中的工作原理和使用方法。

五、DCL

1.创建用户

CREATE USER 'user1'@'localhost' IDENTIFIED BY '123';
CREATE USER 'user2'@'%' IDENTIFIED BY '123';

2.授权

GRANT CREATE,ALTER,INSERT,UPDATE,SELECT ON test.* TO 'user1'@'localhost';
GRANT ALL ON *.* TO 'user2'@'%';

3.撤销授权

REVOKE ALL ON test.* FROM 'user1'@'localhost';

4.查看权限

SHOW GRANTS FOR 'user1'@'localhost';

5.删除用户

DROP USER 'user2'@'%';

6.修改管理员密码

SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('newpassword');

7.修改普通用户密码

SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('newpassword');

    以上内容涵盖了DCL的基本操作，包括创建用户、授权、撤销授权、查看权限、删除用户以及修改密码等内容，并提供了相关代码案例，有助于深入理解DCL在MySQL中的使用方法。

结语：

    通过上述介绍，相信您对MySQL的多表查询和事务操作有了更深入的理解。掌握这些技术，可以大大提高Java开发中的数据库操作能力。

    在Java技术日新月异的发展中，数据库技术的学习和应用显得尤为重要。掌握多表查询和事务操作，可以大大提高我们的Java开发能力。希望本文能对您的学习有所帮助。

欢迎大家私信讨论。

（一份比较早的面试宝典，有兴趣的读者姥爷可以私信我领取！！！免费滴）