夯实根基之MySql从入门到精通（二）

续夯实根基之MySql从入门到精通（一）

夯实根基之MySql从入门到精通（二）

6. MySql连接和联合
- 6.1. MySql连接的概念和类型
- - 6.1.1. 内连接（INNER JOIN）
  - 6.1.2. 左外连接（LEFT JOIN 或 LEFT OUTER JOIN）
  - 6.1.3. 右外连接（RIGHT JOIN 或 RIGHT OUTER JOIN）
  - 6.1.4. 全外连接（FULL OUTER JOIN）
  - 6.1.5. 交叉连接（CROSS JOIN）
  - 6.1.6. 自连接
- 6.2. 联合
7. MySql子查询
8. MySql事务的基本概念
9. MySql事务的隔离级别和锁
- 9.1. 隔离级别
- - 9.1.1. READ UNCOMMITTED（读未提交）：
  - 9.1.2. READ COMMITTED（读已提交）：
  - 9.1.3. REPEATABLE READ（可重复读）：
  - 9.1.4. SERIALIZABLE（可串行化）：
- 9.2. 锁
10. MySql管理和权限管理

6. MySql连接和联合

6.1. MySql连接的概念和类型

将几个表连接起来输出查询结果，称为连接，连接可以分为内连接、外连接、交叉连接

6.1.1. 内连接（INNER JOIN）

select a.name,b.name from a_table a,b_table b where a.id=b.id;

三个表连接起来

select user.name as uname,group.names as gname from user,group,user_group where user.uid=user_group.uid and goup.uid=user_group.uid;

SELECT a.column_name, b.column_name
FROM table1 AS a
INNER JOIN table2 AS b
ON a.common_field = b.common_field;

6.1.2. 左外连接（LEFT JOIN 或 LEFT OUTER JOIN）

左外连接返回左表（table1）的所有记录，即使右表（table2）中没有匹配的记录。如果右表中没有匹配的记录，结果集中右表的部分将为NULL。

SELECT a.column_name, b.column_name
FROM table1 AS a
LEFT JOIN table2 AS b
ON a.common_field = b.common_field;

6.1.3. 右外连接（RIGHT JOIN 或 RIGHT OUTER JOIN）

右外连接返回右表（table2）的所有记录，即使左表（table1）中没有匹配的记录。如果左表中没有匹配的记录，结果集中左表的部分将为NULL。

SELECT a.column_name, b.column_name
FROM table1 AS a
RIGHT JOIN table2 AS b
ON a.common_field = b.common_field;

6.1.4. 全外连接（FULL OUTER JOIN）

全外连接返回两个表中任一表有匹配的所有记录。如果某一侧没有匹配，则该侧的结果为NULL。MySQL本身不支持全外连接，但可以通过合并左外连接和右外连接的结果来模拟。

6.1.5. 交叉连接（CROSS JOIN）

交叉连接（或称为笛卡尔积）返回两个表中所有可能的组合。每个表中的每一行都会与另一个表中的每一行组合。

SELECT a.column_name, b.column_name
FROM table1 AS a
CROSS JOIN table2 AS b;

6.1.6. 自连接

表信息中包含了上一级的信息
在MySQL中，自连接（Self Join）是一种特殊的连接查询，其中一个表与自身进行连接。这通常用于查询表中相互关联的数据，例如，查找经理和员工之间的关系、找出一个表中相互比较的数据等。

自连接通常使用表的别名来区分表的两个实例。以下是一个自连接的基本示例：

SELECT a.column_name, b.column_name
FROM table_name AS a, table_name AS b
WHERE condition;

这里的table_name是进行自连接的表，a和b是表的两个别名，用于区分表中的不同列。

6.2. 联合

在MySQL中，"联合"通常指的是使用UNION操作符来合并两个或多个SELECT语句的结果集。UNION操作符用于将多个查询的结果合并到一个结果集中，并且默认会去除重复的记录。如果需要包含所有重复的记录，可以使用UNION ALL。

基本语法

SELECT column1, column2, ...
FROM table1
WHERE condition

UNION

SELECT column1, column2, ...
FROM table2
WHERE condition;

使用UNION的注意事项

列数匹配：参与联合的每个SELECT语句必须具有相同数量的列。
列类型匹配：每个SELECT语句中的列不仅数量要相同，而且列的数据类型也需要匹配。
排序：UNION的结果默认不排序。如果需要排序，应该在最外层使用ORDER BY子句。
去重：UNION默认会去除重复的记录。如果需要包含所有记录，包括重复的，应该使用UNION ALL。

示例

假设有一个名为employees的表，包含员工的ID、姓名和薪水信息。我们想要查询薪水高于50000和低于5000的员工信息，可以使用UNION来分割查询条件：

SELECT id, name, salary
FROM employees
WHERE salary > 5000

UNION

SELECT id, name, salary
FROM employees
WHERE salary < 500
ORDER BY salary;

这个查询会返回所有薪水高于5000或低于500的员工信息，并且结果会按照薪水排序。

联合查询与连接查询的区别

联合查询（UNION）用于合并来自相同或不同表的相似数据的结果集。
连接查询（JOIN）用于结合来自两个或多个表的相关信息。

7. MySql子查询

在MySQL中，子查询（也称为嵌套查询或内部查询）是嵌套在另一个查询中的SQL查询。子查询可以出现在SELECT、INSERT、UPDATE、DELETE等语句中，通常用在WHERE或HAVING子句中。子查询可以返回单个值或多个值，具体取决于它们是如何使用的。

子查询的类型

标量子查询：返回单个值的子查询，通常用在SELECT列表、WHERE子句的=操作符右边或左边。

SELECT * FROM table_name WHERE column_name = (SELECT another_column FROM another_table WHERE condition);

行子查询：返回一行或多列的子查询，通常用在IN或EXISTSOME操作符中。

SELECT * FROM table_name WHERE column_name IN (SELECT another_column FROM another_table WHERE condition);

列子查询：返回多行单列的子查询，可以用在FROM子句中，作为派生表。

 SELECT * FROM (SELECT column_name FROM table_name WHERE condition) AS derived_table;

派生表：在FROM子句中使用的子查询，它返回多行多列的结果集，可以像常规表一样参与JOIN。

SELECT a.*, b.some_column
FROM (SELECT column1, column2 FROM table_name WHERE condition) AS b
JOIN another_table AS a ON b.column_name = a.column_name;

8. MySql事务的基本概念

在MySQL中，事务处理是一种确保数据完整性和一致性的重要机制。事务是一组不可分割的数据库操作，这些操作要么全部成功，要么全部失败。MySQL使用事务来确保即使在并发环境下，数据库的状态也能保持一致。

事务的特性

原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不做。
一致性（onsistency）：事务执行的结果必须使数据库从一个一致的状态转换到另一个一致的状态。
隔离性（solation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
持久性（Durability）：一旦事务提交，其结果就永久保存在数据库中。

事务的控制语句

开始事务：
```
START TRANSACTION;
```
或者使用BEGIN关键字：
```
BEGIN;
```

提交事务：

COMMIT;
set autocommit=0/1; 自动提交事务
select @@autocommit;

回滚事务：
```
ROLLBACK;
```
设置隔离级别：
```
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
```
隔离级别可以是：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE。

以下是一个使用事务处理的简单示例：

START TRANSACTION;

-- 第一个操作
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;

-- 第二个操作
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 提交事务
COMMIT;

在这个示例中，我们从用户1的账户中扣除100，并将这100加到用户2的账户中。这两个操作被包含在同一个事务中，确保它们要么同时完成，要么同时失败。

9. MySql事务的隔离级别和锁

在MySQL中，事务隔离级别是数据库管理系统用来控制事务之间如何隔离的规则。隔离级别定义了事务可以看到其他并发事务进行的更改到何种程度。MySQL支持四种隔离级别，每种级别都旨在解决特定的并发问题：

9.1. 隔离级别

在MySQL中，事务隔离级别定义了事务间的可见性和一致性规则。不同的隔离级别可以防止或允许不同类型的并发问题，如脏读、不可重复读和幻读。MySQL支持四种事务隔离级别：

9.1.1. READ UNCOMMITTED（读未提交）：

最低的隔离级别。
允许事务读取未提交的数据，可能会导致脏读。

9.1.2. READ COMMITTED（读已提交）：

允许事务读取其他事务已提交的数据。
防止脏读，但仍然可能出现不可重复读和幻读。

9.1.3. REPEATABLE READ（可重复读）：

MySQL的默认隔离级别（对于InnoDB引擎）。
保证在同一事务中多次读取同一数据的结果是一样的，防止不可重复读。
仍然可能出现幻读。
可以通过update software set verseion=version+1,查看新的事务修改后的数据；

9.1.4. SERIALIZABLE（可串行化）：

顺序执行，上一个事务未提交，下一个事务无法开启，最高的隔离级别。
通过在读取的每一行数据上加锁来防止脏读、不可重复读和幻读。
可能导致大量的超时和锁争用。

设置隔离级别

隔离级别可以通过以下SQL语句设置：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

或者在事务开始前设置：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;

9.2. 锁

在事务处理中，锁是用来保证数据一致性和隔离性的重要机制。MySQL使用多种类型的锁：

共享锁（S锁）：
- 允许一个事务读取一行数据，其他事务也可以读取这一行，但不能修改。
```
 lock table table_name read;
 unlock  tables; 解锁
```
排他锁（X锁）：
- 允许一个事务读取和写入一行数据，阻止其他事务读取或写入这一行。
```
 lock table table_name write;
 unlock  tables; 解锁
```
意向锁（Intention Locks）：
- 表明事务打算在行或表上设置共享锁或排他锁。
间隙锁（Gap Locks）：
- 防止幻读，锁定一个范围的索引记录，而不是单个记录。
Next-Key锁：
- 结合了共享锁和间隙锁，锁定一个索引记录和它前面的范围。

死锁

当两个或多个事务相互等待对方释放资源时，就会发生死锁。为了避免死锁，可以采取以下措施：

确保事务按相同的顺序访问资源。
使用较低的隔离级别。
检测到死锁时，自动回滚其中一个事务。
应用超时设置，当事务等待资源超过一定时间后自动放弃。

10. MySql管理和权限管理

在MySQL中，授权表是存储用户权限信息的系统表，它们记录了用户可以访问和操作数据库对象（如表、列等）的权限。MySQL的授权表主要包括以下几个：

mysql.user：存储用户账号信息和全局权限。
mysql.db：存储数据库级别的权限。
mysql.tables_priv：存储表级别的权限。
mysql.columns_priv：存储列级别的权限。
mysqlprocs_priv：存储过程（存储过程和函数）的权限。
mysql.host：存储主机名和用户账号的映射。

这些表共同构成了MySQL的权限系统，通过这些表，MySQL可以控制用户对数据库对象的访问和操作权限。管理员可以通过修改这些表中的数据来授予或撤销用户的权限。

管理授权表

管理员可以使用SQL语句来管理授权表，常见的操作包括：

创建用户：使用CREATE USER语句创建新用户。
授予权限：使用GRANT语句授予用户权限。
查看权限：使用SHOW GRANTS语句查看用户的权限。
撤销权限：使用REVOKE语句撤销用户的权限。
删除用户：使用DROP USER语句删除用户。

示例

以下是一些管理授权表的示例操作：

-- 创建新用户
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

-- 授予权限
GRANT SELECT, INSERT ON mydatabase.* TO 'newuser'@'localhost';

-- 查看权限
SHOW GRANTS FOR 'newuser'@localhost';

-- 撤销权限
REVOKE INSERT ON mydatabase.* FROM 'newuser'@localhost';

-- 删除用户
DROP USER 'newuser'@localhost';

注意事项

在授予或撤销权限后，需要执行FLUSH PRIVILEGES语句使更改生效。
修改授权表后，应谨慎操作，确保不会无意中授予过多权限或泄露敏感数据。
管理授权表时，应遵循最小权限原则，只授予用户完成任务所需的最小权限集。

授权表是MySQL中实现用户权限管理的关键部分，通过合理管理授权表，可以确保数据库的安全性和合规性。

秒客网

夯实根基之MySql从入门到精通（二）

夯实根基之MySql从入门到精通（二）

6. MySql连接和联合

6.1. MySql连接的概念和类型

6.1.1. 内连接（INNER JOIN）

6.1.2. 左外连接（LEFT JOIN 或 LEFT OUTER JOIN）

6.1.3. 右外连接（RIGHT JOIN 或 RIGHT OUTER JOIN）

6.1.4. 全外连接（FULL OUTER JOIN）

6.1.5. 交叉连接（CROSS JOIN）

6.1.6. 自连接

6.2. 联合

7. MySql子查询

8. MySql事务的基本概念

9. MySql事务的隔离级别和锁

9.1. 隔离级别

9.1.1. READ UNCOMMITTED（读未提交）：

9.1.2. READ COMMITTED（读已提交）：

9.1.3. REPEATABLE READ（可重复读）：

9.1.4. SERIALIZABLE（可串行化）：

9.2. 锁

10. MySql管理和权限管理

相关文章