MySQL基础知识（通俗版）

MySQL基础知识（通俗版）

一、MySQL基础概念

1.1 MySQL简介

想象MySQL就像一个超级大的Excel表格，但它比Excel更强大：

• 可以同时处理成千上万的数据
• 可以保证数据的安全性和一致性
• 可以支持多人同时操作
• 可以自动备份和恢复数据

1.2 基本概念

1. 数据库（Database）

   • 就像一个大文件夹，里面可以存放很多相关的数据表

2. 表（Table）

   • 就像Excel中的一个工作表，用来存储特定类型的数据

3. 字段（Field）

   • 就像Excel中的列，用来存储不同类型的数据
   • 常见类型：文本、数字、日期、图片等

4. 记录（Record）

   • 就像Excel中的一行，表示一条完整的数据

二、MySQL数据类型

2.1 数值类型

-- 整数类型
TINYINT      -- 1字节，范围：-128到127
SMALLINT     -- 2字节，范围：-32768到32767
INT          -- 4字节，范围：-2147483648到2147483647
BIGINT       -- 8字节，范围：-9223372036854775808到9223372036854775807-- 小数类型
FLOAT        -- 4字节，单精度浮点数
DOUBLE       -- 8字节，双精度浮点数
DECIMAL      -- 精确小数，用于金额等需要精确计算的场景

2.2 字符串类型

-- 固定长度字符串
CHAR(10)     -- 固定10个字符，不足补空格-- 可变长度字符串
VARCHAR(100) -- 最多100个字符，实际长度可变-- 文本类型
TEXT         -- 最大65535字节
MEDIUMTEXT   -- 最大16777215字节
LONGTEXT     -- 最大4294967295字节-- 二进制类型
BINARY       -- 固定长度二进制
VARBINARY    -- 可变长度二进制
BLOB         -- 二进制大对象

2.3 日期时间类型

DATE         -- 日期，格式：YYYY-MM-DD
TIME         -- 时间，格式：HH:MM:SS
DATETIME     -- 日期时间，格式：YYYY-MM-DD HH:MM:SS
TIMESTAMP    -- 时间戳，自动记录修改时间
YEAR         -- 年份，格式：YYYY

三、MySQL基本操作

3.1 数据库操作

-- 创建数据库
CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;-- 删除数据库
DROP DATABASE IF EXISTS shop;-- 使用数据库
USE shop;-- 查看所有数据库
SHOW DATABASES;-- 查看数据库创建语句
SHOW CREATE DATABASE shop;

3.2 表操作

-- 创建表（完整示例）
CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(50) NOT NULL UNIQUE,password VARCHAR(255) NOT NULL,email VARCHAR(100) UNIQUE,phone VARCHAR(20),age INT CHECK (age >= 0 AND age <= 150),gender ENUM('男', '女', '其他') DEFAULT '其他',status TINYINT DEFAULT 1 COMMENT '1:正常,0:禁用',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX idx_username (username),INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';-- 修改表结构
ALTER TABLE users 
ADD COLUMN address VARCHAR(200) AFTER phone,
MODIFY COLUMN email VARCHAR(150),
DROP COLUMN age,
RENAME COLUMN username TO name;-- 删除表
DROP TABLE IF EXISTS users;-- 清空表数据
TRUNCATE TABLE users;-- 重命名表
RENAME TABLE users TO customers;

3.3 数据操作

-- 插入数据（多种方式）
INSERT INTO users (username, password, email) 
VALUES ('张三', '123456', 'zhangsan@example.com');INSERT INTO users SET 
username = '李四',
password = '123456',
email = 'lisi@example.com';INSERT INTO users (username, password, email) VALUES 
('王五', '123456', 'wangwu@example.com'),
('赵六', '123456', 'zhaoliu@example.com');-- 更新数据
UPDATE users 
SET password = 'newpassword', email = 'newemail@example.com'
WHERE id = 1;-- 删除数据
DELETE FROM users WHERE id = 1;-- 批量删除
DELETE FROM users WHERE id IN (1, 2, 3);

四、MySQL查询操作

4.1 基础查询

-- 查询所有数据
SELECT * FROM users;-- 条件查询
SELECT * FROM users 
WHERE age > 18 
AND status = 1 
OR email LIKE '%@example.com';-- 排序
SELECT * FROM users 
ORDER BY created_at DESC, id ASC;-- 分组
SELECT gender, COUNT(*) as count 
FROM users 
GROUP BY gender 
HAVING count > 5;-- 分页
SELECT * FROM users 
LIMIT 10 OFFSET 20;-- 去重
SELECT DISTINCT gender FROM users;

4.2 多表查询

-- 内连接（只显示两表都匹配的数据）
SELECT o.order_id, p.name, o.quantity
FROM orders o
INNER JOIN products p ON o.product_id = p.id;-- 左连接（显示左表所有数据，右表没有的显示NULL）
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;-- 右连接（显示右表所有数据，左表没有的显示NULL）
SELECT o.order_id, p.name
FROM orders o
RIGHT JOIN products p ON o.product_id = p.id;

4.3 高级查询

-- 子查询
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE total_amount > 1000
);-- 相关子查询
SELECT * FROM products p
WHERE price > (SELECT AVG(price) FROM products WHERE category = p.category
);-- 使用EXISTS
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'completed'
);-- 使用CASE
SELECT id,username,CASE WHEN age < 18 THEN '未成年'WHEN age < 30 THEN '青年'WHEN age < 50 THEN '中年'ELSE '老年'END as age_group
FROM users;

五、MySQL函数

5.1 字符串函数

-- 字符串连接
SELECT CONCAT('Hello', ' ', 'World');-- 字符串长度
SELECT LENGTH('Hello');-- 字符串截取
SELECT SUBSTRING('Hello World', 1, 5);-- 大小写转换
SELECT UPPER('hello'), LOWER('WORLD');-- 去除空格
SELECT TRIM('  Hello  ');

5.2 数值函数

-- 四舍五入
SELECT ROUND(3.14159, 2);-- 向上取整
SELECT CEILING(3.1);-- 向下取整
SELECT FLOOR(3.9);-- 随机数
SELECT RAND();-- 绝对值
SELECT ABS(-100);

5.3 日期函数

-- 当前日期
SELECT CURDATE();-- 当前时间
SELECT CURTIME();-- 当前日期时间
SELECT NOW();-- 日期加减
SELECT DATE_ADD('2024-01-01', INTERVAL 1 MONTH);-- 日期差
SELECT DATEDIFF('2024-01-01', '2023-12-31');

5.4 聚合函数

-- 统计函数
SELECT COUNT(*) as total_users,AVG(age) as avg_age,MAX(age) as max_age,MIN(age) as min_age,SUM(total_amount) as total_sales
FROM users u
JOIN orders o ON u.id = o.user_id;

六、MySQL约束

6.1 主键约束

-- 单列主键
CREATE TABLE users (id INT PRIMARY KEY,name VARCHAR(50)
);-- 复合主键
CREATE TABLE order_items (order_id INT,product_id INT,quantity INT,PRIMARY KEY (order_id, product_id)
);

6.2 外键约束

-- 创建外键
CREATE TABLE orders (id INT PRIMARY KEY,customer_id INT,FOREIGN KEY (customer_id) REFERENCES customers(id)
);-- 设置级联操作
CREATE TABLE order_items (id INT PRIMARY KEY,order_id INT,FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

6.3 其他约束

-- 非空约束
CREATE TABLE products (id INT PRIMARY KEY,name VARCHAR(100) NOT NULL
);-- 唯一约束
CREATE TABLE users (id INT PRIMARY KEY,email VARCHAR(100) UNIQUE
);-- 默认值
CREATE TABLE orders (id INT PRIMARY KEY,status VARCHAR(20) DEFAULT 'pending'
);-- 检查约束
CREATE TABLE products (id INT PRIMARY KEY,price DECIMAL(10,2) CHECK (price >= 0)
);

七、MySQL高级特性

7.1 视图

-- 创建视图
CREATE VIEW user_orders AS
SELECT u.id,u.username,COUNT(o.id) as order_count,SUM(o.total_amount) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;-- 修改视图
ALTER VIEW user_orders AS
SELECT u.id,u.username,COUNT(o.id) as order_count,SUM(o.total_amount) as total_spent,MAX(o.created_at) as last_order_time
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;-- 删除视图
DROP VIEW IF EXISTS user_orders;

7.2 存储过程

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE get_user_orders(IN user_id INT)
BEGINSELECT o.id,o.total_amount,o.created_at,COUNT(oi.id) as item_countFROM orders oLEFT JOIN order_items oi ON o.id = oi.order_idWHERE o.user_id = user_idGROUP BY o.id;
END //
DELIMITER ;-- 调用存储过程
CALL get_user_orders(1);-- 删除存储过程
DROP PROCEDURE IF EXISTS get_user_orders;

7.3 触发器

-- 创建触发器
DELIMITER //
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGINUPDATE products SET stock = stock - NEW.quantityWHERE id = NEW.product_id;
END //
DELIMITER ;-- 删除触发器
DROP TRIGGER IF EXISTS after_order_insert;

八、MySQL事务

8.1 事务基础

-- 开始事务
START TRANSACTION;-- 执行多个操作
INSERT INTO orders (user_id, total_amount) VALUES (1, 1000);
UPDATE products SET stock = stock - 1 WHERE id = 1;
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1, 1);-- 提交事务
COMMIT;-- 如果出错，回滚事务
ROLLBACK;-- 设置事务隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

8.2 事务特性

• 原子性（Atomicity）
• 一致性（Consistency）
• 隔离性（Isolation）
• 持久性（Durability）

九、MySQL索引

9.1 索引类型

-- 创建索引
CREATE INDEX idx_email ON users(email);-- 创建唯一索引
CREATE UNIQUE INDEX idx_username ON users(username);-- 创建联合索引
CREATE INDEX idx_name_age ON users(name, age);-- 创建全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);-- 删除索引
DROP INDEX idx_email ON users;

9.2 索引优化

• 选择合适的索引列
• 避免过多索引
• 使用覆盖索引
• 定期维护索引

十、MySQL存储引擎

10.1 InnoDB

• 支持事务和外键
• 行级锁定
• 崩溃恢复
• 适合大多数应用场景

10.2 MyISAM

• 不支持事务
• 表级锁定
• 查询速度快
• 适合读多写少的场景

10.3 其他存储引擎

• MEMORY：数据存储在内存中
• ARCHIVE：适合存储归档数据
• CSV：以CSV格式存储数据

十一、MySQL性能优化

11.1 查询优化

-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM products WHERE price > 1000;-- 使用LIMIT优化分页
SELECT * FROM products LIMIT 10 OFFSET 20;-- 避免SELECT *
SELECT id, name, price FROM products;-- 使用覆盖索引
SELECT id, name FROM products WHERE price > 1000;

11.2 配置优化

# my.cnf 配置文件
[mysqld]
# 缓冲池大小
innodb_buffer_pool_size = 4G# 最大连接数
max_connections = 1000# 查询缓存
query_cache_size = 64M# 临时表大小
tmp_table_size = 64M# 排序缓冲区大小
sort_buffer_size = 4M

十二、MySQL备份恢复

12.1 物理备份

# 完整备份
mysqldump -u root -p --all-databases > backup.sql# 增量备份
mysqlbinlog mysql-bin.000001 > incremental.sql# 压缩备份
mysqldump -u root -p shop | gzip > backup.sql.gz

12.2 逻辑备份

-- 导出表结构
SHOW CREATE TABLE products;-- 导出数据
SELECT * FROM products INTO OUTFILE 'products.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

12.3 恢复操作

# 恢复完整备份
mysql -u root -p shop < backup.sql# 恢复增量备份
mysql -u root -p shop < incremental.sql# 恢复压缩备份
gunzip < backup.sql.gz | mysql -u root -p shop

十三、MySQL监控

13.1 性能监控

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';-- 查看慢查询
SHOW VARIABLES LIKE '%slow%';-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS;-- 查看表状态
SHOW TABLE STATUS LIKE 'products';

13.2 日志管理

-- 开启慢查询日志
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;-- 开启错误日志
SET GLOBAL log_error = '/var/log/mysql/error.log';-- 开启二进制日志
SET GLOBAL log_bin = 1;
SET GLOBAL binlog_format = 'ROW';

13.3 性能诊断

-- 查看当前锁等待
SHOW PROCESSLIST;-- 查看表锁等待
SHOW OPEN TABLES WHERE In_use > 0;-- 查看死锁
SHOW ENGINE INNODB STATUS;

十四、MySQL最佳实践

14.1 开发规范

1. 命名规范

   • 使用有意义的名称
   • 保持命名风格统一

2. 代码规范

   • 写好注释
   • 保持代码整洁

3. 测试规范

   • 做好数据备份
   • 测试各种情况

14.2 运维规范

1. 定期维护

   • 检查数据库状态
   • 清理无用数据
   • 优化表结构

2. 安全维护

   • 定期更改密码
   • 及时更新补丁
   • 做好访问控制

3. 性能维护

   • 监控系统资源
   • 优化查询性能
   • 调整配置参数