MySQL常用知识点

MySQL常用知识点

1. MySQL基础操作

MySQL是一个强大的关系型数据库管理系统。要开始使用MySQL,首先需要掌握一些基本操作。

a. 连接到MySQL服务器

使用命令行连接MySQL:

1
mysql -u username -p

输入密码后,你就可以连接到MySQL服务器了。

b. 创建和管理数据库

创建新数据库:

1
CREATE DATABASE mydatabase;

切换到该数据库:

1
USE mydatabase;

查看所有数据库:

1
SHOW DATABASES;

删除数据库:

1
DROP DATABASE mydatabase;

c. 创建和管理表

创建新表:

1
2
3
4
5
6
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE
);

查看表结构:

1
DESCRIBE employees;

修改表结构:

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

删除表:

1
DROP TABLE employees;

2. MySQL数据类型

MySQL提供了多种数据类型,以适应不同的数据存储需求。

a. 数值类型

  • INT: 整数
  • DECIMAL(M,D): 精确小数

b. 字符串类型

  • VARCHAR(N): 可变长度字符串
  • TEXT: 长文本

c. 日期和时间类型

  • DATE: 日期
  • DATETIME: 日期和时间

d. 选择合适数据类型的原则

  • 选择能够准确表示数据的最小数据类型
  • 考虑未来可能的数据增长
  • 对于频繁查询的列,选择适合索引的类型

3. MySQL的增删改查(CRUD)操作

a. 插入数据(INSERT)

1
2
INSERT INTO employees (name, email, hire_date)
VALUES ('John Doe', 'john@example.com', '2023-01-15');

b. 查询数据(SELECT)

1
SELECT * FROM employees WHERE hire_date > '2023-01-01';

c. 更新数据(UPDATE)

1
UPDATE employees SET salary = 50000 WHERE name = 'John Doe';

d. 删除数据(DELETE)

1
DELETE FROM employees WHERE id = 1;

4. MySQL高级查询技巧

a. 子查询

1
SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

b. 组函数和分组(GROUP BY)

1
2
3
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department;

c. HAVING子句

1
2
3
4
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 50000;

d. JOIN连接查询

1
2
3
SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

5. MySQL索引

a. 创建索引

1
CREATE INDEX idx_name ON employees(name);

b. 删除索引

1
DROP INDEX idx_name ON employees;

好的,我们继续讨论剩下的主题:

6. MySQL事务处理

事务是保证数据库操作的一致性和完整性的重要机制。

a. 事务的ACID属性

  • 原子性(Atomicity): 事务中的所有操作要么全部完成,要么全部不完成。
  • 一致性(Consistency): 事务开始前和结束后,数据库的完整性约束没有被破坏。
  • 隔离性(Isolation): 多个事务并发执行时,一个事务的执行不应影响其他事务的执行。
  • 持久性(Durability): 事务完成后,其结果是永久性的。

b. 开始、提交和回滚事务

1
2
3
4
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

如果出现错误,可以回滚事务:

1
ROLLBACK;

c. 事务隔离级别

MySQL支持四种事务隔离级别:

  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ (默认)
  • SERIALIZABLE

设置隔离级别:

1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

7. MySQL视图和存储过程

a. 创建和使用视图

视图是一个虚拟表,基于SQL语句的结果集:

1
2
3
4
CREATE VIEW high_salary_employees AS
SELECT * FROM employees WHERE salary > 50000;

SELECT * FROM high_salary_employees;

b. 创建和调用存储过程

存储过程是一组预编译的SQL语句:

1
2
3
4
5
6
7
8
DELIMITER //
CREATE PROCEDURE get_employee_by_id(IN emp_id INT)
BEGIN
    SELECT * FROM employees WHERE id = emp_id;
END //
DELIMITER ;

CALL get_employee_by_id(1);

8. MySQL用户管理和权限控制

a. 创建和删除用户

1
2
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
DROP USER 'newuser'@'localhost';

b. 授予和撤销权限

1
2
GRANT SELECT, INSERT ON mydatabase.* TO 'newuser'@'localhost';
REVOKE INSERT ON mydatabase.* FROM 'newuser'@'localhost';
  1. MySQL性能优化基础

a. 使用EXPLAIN分析查询

EXPLAIN命令可以显示MySQL如何执行查询:

1
EXPLAIN SELECT * FROM employees WHERE salary > 50000;

b. 查询优化技巧

  • 只选择需要的列,避免使用SELECT *
  • 使用适当的索引
  • 避免在WHERE子句中使用函数,这可能会阻止使用索引
  • 使用LIMIT限制结果集大小

示例优化查询:

1
2
3
4
5
-- 优化前
SELECT * FROM employees WHERE YEAR(hire_date) = 2023;

-- 优化后
SELECT id, name, email FROM employees WHERE hire_date >= '2023-01-01' AND hire_date < '2024-01-01';

这个优化后的查询可以使用hire_date列上的索引,并且只返回需要的列。