数据库操作、锁特性

1. DML、DDL和DQL是数据库操作语言的三种主要类型

1.1 DML（Data Manipulation Language）数据操纵语言

• DML是用于检索、插入、更新和删除数据库中数据的SQL语句。

• 主要的DML语句包括：

  • SELECT：用于查询数据库中的数据。

  • INSERT：用于向数据库表中插入新数据。

  • UPDATE：用于修改数据库表中的现有数据。

  • DELETE：用于从数据库表中删除数据。

• DML语句通常不涉及数据库结构的修改。

1.1.1 SELECT

• 用于查询数据库中的数据，可以结合WHERE子句、GROUP BY子句、HAVING子句和ORDER BY子句等进行复杂的查询。

SELECT column1, column2 FROM table_name WHERE condition;

1.1.2 INSERT

• 用于向数据库表中插入新的数据行。

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

• 如果不指定列名，将插入所有列的默认值：

INSERT INTO table_name VALUES (value1, value2);

1.1.3 UPDATE

• 用于修改数据库表中的现有数据。

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

1.1.4 DELETE

• 用于从数据库表中删除数据。

DELETE FROM table_name WHERE condition;

• 注意：如果没有WHERE子句，将删除表中的所有行。

1.2 DDL（Data Definition Language）数据定义语言

• DDL用于定义和管理数据库的结构，如创建、修改和删除数据库对象（如表、索引、视图、触发器等）。

• 主要的DDL语句包括：

  • CREATE：用于创建新的数据库对象，如表、索引或视图。

  • ALTER：用于修改现有数据库对象的结构。

  • DROP：用于删除数据库对象。

  • TRUNCATE：用于快速删除表中的所有行。

• DDL语句通常会影响数据库的元数据，并且需要数据库管理员权限。

1.2.1 CREATE：创建新的数据库对象。

• 创建数据库：

CREATE DATABASE test;

• 创建表：

CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, position VARCHAR(100), hire_date DATE );

• 创建索引：

CREATE INDEX idx_lastname ON employees (name);

• 创建视图：

CREATE VIEW employee_positions AS SELECT name, position FROM employees；

• 创建触发器：在指定的数据库表上特定事件（如INSERT、UPDATE或DELETE）发生时自动执行。

CREATE TRIGGER before_insert_employee BEFORE INSERT ON employees FOR EACH ROW BEGIN SET NEW.name = CONCAT('Mr/Ms ', NEW.name); END;

注：创建一个名为before_insert_employee的触发器，在employees表中执行插入操作之前，在名字之前加上'Mr/Ms'。

1.2.2 ALTER：修改现有数据库对象的结构。

• 修改表结构，添加新列：

ALTER TABLE employees ADD COLUMN salary DECIMAL(10, 2);

• 修改列的数据类型：

ALTER TABLE employees MODIFY COLUMN name VARCHAR(150);

• 重命名表：

ALTER TABLE employees RENAME TO staff;

1.2.3 DROP：删除数据库对象。

• 删除表：

DROP TABLE IF EXISTS staff;

• 删除数据库：

DROP DATABASE IF EXISTS mydatabase;

• 删除视图：

DROP VIEW IF EXISTS employee_positions;

• 删除触发器：

DROP TRIGGER IF EXISTS before_insert_employee;

1.2.4 TRUNCATE：快速删除表中的所有行。

• 快速删除表中的所有行，不记录行删除操作：

TRUNCATE TABLE employees;

1.3 DQL（Data Query Language）数据查询语言：

• DQL是用于查询或检索数据库中数据的SQL语句。

• DQL实际上主要是SELECT语句，它允许用户指定他们想要检索的数据。

• DQL语句可以包含多个子句，如WHERE、GROUP BY、HAVING和ORDER BY，以提供更复杂的查询能力。

1.3.1 基本查询

SELECT column1, column2 FROM table_name;

1.3.2 条件查询

SELECT column1, column2 FROM table_name WHERE condition;

1.3.3 分组查询

SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

1.3.4 分组后筛选

SELECT column1, SUM(column2) FROM table_name GROUP BY column1 HAVING SUM(column2) > 100;

1.3.5 排序查询

SELECT column1, column2 FROM table_name ORDER BY column1 ASC, column2 DESC;

1.3.6 聚合查询

SELECT column1, AVG(column2), MAX(column3), MIN(column3), COUNT(*) FROM table_name GROUP BY column1;

1.3.7 联结查询

SELECT a.column1, b.column2 FROM table_name AS a JOIN another_table AS b ON a.common_column = b.common_column;

1.3.8 子查询

SELECT * FROM table_name WHERE column1 IN (SELECT column1 FROM another_table WHERE condition);

2. 简单的示例

2.1 DQL语句关键字执行顺序

1. FROM

   • 首先确定查询的数据来源，即指定的表或子查询。

2. JOIN

   1. 接下来执行连接操作，根据JOIN关键字（INNER JOIN, LEFT JOIN, RIGHT JOIN等）将多个表连接起来。

3. WHERE

   1. 应用WHERE子句的条件过滤数据，只有满足条件的记录才会被保留。

4. GROUP BY

   1. 将结果集按照GROUP BY子句中的列进行分组。

5. HAVING

   1. 对分组后的结果应用HAVING子句的条件过滤，类似于WHERE，但是用于过滤分组后的结果。

6. SELECT

   1. 选择特定的列或计算后的列。

7. DISTINCT

   1. 如果使用DISTINCT关键字，去除结果中的重复行。

8. ORDER BY

   1. 对结果集进行排序。

9. LIMIT（在某些数据库系统中，如MySQL）

   1. 限制返回的结果数量。

10. OFFSET（与LIMIT一起使用）

    1. 指定从哪一条记录开始返回结果。

2.2 左连接示例

mysql> select * from employees e left join departments d on e.department = d.department_name;
+----+------------+-----------+---------------------------+------------+------------+-----------+---------------+-----------------+
| id | first_name | last_name | email                     | department | hire_date  | salary    | department_id | department_name |
+----+------------+-----------+---------------------------+------------+------------+-----------+---------------+-----------------+
|  1 | John       | Doe       | [e-mail]      | Finance    | 2023-01-10 |  70000.00 |             1 | Finance         |
|  2 | Jane       | Smith     | [e-mail]    | IT         | 2023-02-15 |  75000.00 |             3 | IT              |
|  3 | Alice      | Johnson   | [e-mail] | HR         | 2023-03-20 |  65000.00 |             2 | HR              |
|  4 | Mike       | Brown     | [e-mail]    | Marketing  | 2023-04-25 |  80000.00 |             4 | Marketing       |
|  5 | yang       | rongkai   | [e-mail]         | IT         | NULL       | 100000.00 |             3 | IT              |
+----+------------+-----------+---------------------------+------------+------------+-----------+---------------+-----------------+
5 rows in set (0.00 sec)mysql> select * from employees e left join departments d on e.department = d.department_name where id = 1;
+----+------------+-----------+----------------------+------------+------------+----------+---------------+-----------------+
| id | first_name | last_name | email                | department | hire_date  | salary   | department_id | department_name |
+----+------------+-----------+----------------------+------------+------------+----------+---------------+-----------------+
|  1 | John       | Doe       | [e-mail] | Finance    | 2023-01-10 | 70000.00 |             1 | Finance         |
+----+------------+-----------+----------------------+------------+------------+----------+---------------+-----------------+
1 row in set (0.00 sec)mysql> select * from employees e left join departments d on e.department = d.department_name order by id desc limit 3;
+----+------------+-----------+---------------------------+------------+------------+-----------+---------------+-----------------+
| id | first_name | last_name | email                     | department | hire_date  | salary    | department_id | department_name |
+----+------------+-----------+---------------------------+------------+------------+-----------+---------------+-----------------+
|  5 | yang       | rongkai   | [e-mail]         | IT         | NULL       | 100000.00 |             3 | IT              |
|  4 | Mike       | Brown     | [e-mail]    | Marketing  | 2023-04-25 |  80000.00 |             4 | Marketing       |
|  3 | Alice      | Johnson   | [e-mail] | HR         | 2023-03-20 |  65000.00 |             2 | HR              |
+----+------------+-----------+---------------------------+------------+------------+-----------+---------------+-----------------+
3 rows in set (0.00 sec)

注：inner join...on、left join...on、right join...on是一样的写法，主要在于以哪一个表为核心连接。

3. 锁

MySQL中的锁机制是确保数据一致性和隔离性的关键技术，主要分为以下几种类型：

1. 全局锁

   1. 锁定整个数据库，使其处于只读状态。适用于全库备份等需要确保数据一致性的场景。使用FLUSH TABLES WITH READ LOCK;命令可以获取全局锁。

2. 表级锁

   1. 对某个表加锁，包括表锁和元数据锁（MDL）。表锁分为读锁（共享锁）和写锁（排他锁）。表级锁是粗粒度的锁，适用于不需要并发操作的场景。

   2. 读锁（共享锁）允许多个事务同时读取数据，而写锁（排他锁）在操作未完成前会阻塞其他读和写操作。

3. 意向锁（Intention Lock）

   1. 表级锁的一种，标记当前事务对表中行的锁定意图，用于加速表锁和行锁之间的协调。分为意向共享锁（IS）和意向排他锁（IX）。

4. 行级锁

   1. 每次操作锁住对应的行数据。主要分为记录锁（Record Lock）、间隙锁（Gap Lock）和临键锁（Next-Key Lock）。InnoDB通过行级锁实现了更细粒度的控制，支持更高的并发更新和查询。

   2. 记录锁（Record Lock）针对索引记录的锁定，防止其他事务对特定行进行update和delete。

   3. 间隙锁（Gap Lock）锁定索引记录间隙，确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。

   4. 临键锁（Next-Key Lock）结合了记录锁和间隙锁的功能，在RR隔离级别下支持。

5. 共享锁（S锁）与排他锁（X锁）

   1. 共享锁允许多个事务同时获取，而排他锁则确保同一时间内只有一个事务可以获取。

6. 乐观锁与悲观锁

   1. 乐观锁认为自己的操作会成功，先尝试执行，失败时再获取锁；悲观锁则认为自己的操作可能不成功，会先获取锁再执行。

7. 页面锁

   1. 针对数据库表中的页进行加锁的机制，适用于对某一页中的多行进行操作时，减少锁的粒度，提高并发性能。

不积跬步，无以至千里 --- xiaokai