mysql数据库SQL语句大全详解（下）

六、事务管理

1、开始事务

START TRANSACTION;

2、提交事务

COMMIT;

3、回滚事务

ROLLBACK;

4、保存点

设置保存点：

SAVEPOINT savepoint_name;

回滚到保存点：

ROLLBACK TO SAVEPOINT savepoint_name;

七、视图

1、创建视图

CREATE VIEW view_name AS SELECT columns FROM table WHERE condition;

示例：

CREATE VIEW user_emails AS SELECT username, email FROM users;

2、查询视图

SELECT * FROM view_name;

3、删除视图

DROP VIEW view_name;

八、存储过程和函数

1、创建存储过程

CREATE PROCEDURE procedure_name (parameters)
BEGIN
    SQL statements;
END;

示例：

CREATE PROCEDURE GetUserEmails()
BEGIN
    SELECT username, email FROM users;
END;

2、调用存储过程

CALL procedure_name(parameters);

3、创建函数

CREATE FUNCTION function_name (parameters) RETURNS datatype
BEGIN
    SQL statements;
    RETURN value;
END;

示例：

CREATE FUNCTION GetUserCount() RETURNS INT
BEGIN
    DECLARE count INT;
    SELECT COUNT(*) INTO count FROM users;
    RETURN count;
END;

4、调用函数

SELECT function_name(parameters);

九、触发器

1、创建触发器

CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name
FOR EACH ROW
BEGIN
    SQL statements;
END;

示例：

CREATE TRIGGER before_insert_user BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
END;

2、删除触发器

DROP TRIGGER trigger_name;

十、用户和权限管理

1、创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

2、删除用户

DROP USER 'username'@'host';

3、授予权限

GRANT privileges ON database.table TO 'username'@'host';

示例：

GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'username'@'localhost';

4、撤销权限

REVOKE privileges ON database.table FROM 'username'@'host';

示例：

REVOKE INSERT, UPDATE ON mydb.* FROM 'username'@'localhost';

5、刷新权限

FLUSH PRIVILEGES;

在对用户权限进行修改后，刷新MySQL的权限缓存以确保更改生效。

十一、备份与恢复

1、备份数据库 

使用 mysqldump 命令可以备份整个数据库或单个表。

mysqldump -u username -p database_name > backup_file.sql

示例：

mysqldump -u root -p mydb > mydb_backup.sql

2、恢复数据库

使用 mysql 命令可以从备份文件恢复数据库。

mysql -u username -p database_name < backup_file.sql

示例：

mysql -u root -p mydb < mydb_backup.sql

十二、优化和性能

1、查询性能分析 

使用 EXPLAIN 语句分析查询性能。

EXPLAIN SELECT * FROM table_name WHERE condition;

2、优化表

OPTIMIZE TABLE table_name;

3、更新表统计信息

ANALYZE TABLE table_name;

4、缓存查询结果

​​​​​​​ 配置 query_cache 参数来缓存查询结果，提高查询性能。

SET GLOBAL query_cache_size = value;
SET GLOBAL query_cache_type = ON;

十三、安全和备份策略

1、数据加密

加密连接：配置MySQL使用SSL/TLS进行客户端与服务器之间的加密通信。

加密数据：使用MySQL的原生加密函数，如 AES_ENCRYPT() 和 AES_DECRYPT() 进行数据加密。

2、备份策略

定期备份：设置定期备份策略，确保数据的定期备份。

异地备份：将备份文件存储在异地，以防止物理灾害。

3、访问控制

最小权限原则：只授予用户所需的最小权限。

审计日志：开启审计日志，记录用户的所有操作。

十四、常用函数和操作符

1、字符串函数：
CONCAT(str1, str2, ...)：连接字符串。
UPPER(str)：转换为大写。
LOWER(str)：转换为小写。
SUBSTRING(str, pos, len)：提取子字符串。

2、日期和时间函数：
NOW()：当前日期和时间。
CURDATE()：当前日期。
DATE_ADD(date, INTERVAL value unit)：日期加上时间间隔。
DATEDIFF(date1, date2)：计算两个日期之间的天数。

3、数学函数
ABS(x)：绝对值。
CEIL(x)：向上取整。
FLOOR(x)：向下取整。
ROUND(x, d)：四舍五入到d位小数。

4、聚合函数：
COUNT(column)：计算列的数量。
SUM(column)：计算列的总和。
AVG(column)：计算列的平均值。
MAX(column)：计算列的最大值。
MIN(column)：计算列的最小值。


5、逻辑和比较操作符：
=：等于。
<> 或 !=：不等于。
>：大于。
<：小于。
>=：大于或等于。
<=：小于或等于。
AND：逻辑与。
OR：逻辑或。
NOT：逻辑非。

十五、示例综合应用

以下是一个综合示例，展示了如何创建数据库、表、插入数据、查询、更新和删除数据，以及创建视图、存储过程和触发器。

-- 创建数据库
CREATE DATABASE company;

-- 选择数据库
USE company;

-- 创建员工表
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE
);

-- 插入数据
INSERT INTO employees (name, position, salary, hire_date)
VALUES ('Alice', 'Manager', 75000, '2020-01-15'),
       ('Bob', 'Developer', 60000, '2019-03-22'),
       ('Charlie', 'Analyst', 55000, '2021-07-11');

-- 查询数据
SELECT * FROM employees;

-- 更新数据
UPDATE employees SET salary = 65000 WHERE name = 'Bob';

-- 删除数据
DELETE FROM employees WHERE name = 'Charlie';

-- 创建视图
CREATE VIEW manager_view AS
SELECT name, salary FROM employees WHERE position = 'Manager';

-- 查询视图
SELECT * FROM manager_view;

-- 创建存储过程
CREATE PROCEDURE RaiseSalary(IN employee_id INT, IN increase DECIMAL(10, 2))
BEGIN
    UPDATE employees SET salary = salary + increase WHERE id = employee_id;
END;

-- 调用存储过程
CALL RaiseSalary(2, 5000);

-- 创建触发器
CREATE TRIGGER before_insert_employee
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    SET NEW.hire_date = IFNULL(NEW.hire_date, CURDATE());
END;