MySQL 入门笔记

雪中何以赠君别
8 阅读12分钟

一、数据库操作

1.1 创建数据库

-- 基本语法
CREATE DATABASE 数据库名;

-- 创建数据库示例
CREATE DATABASE school;

-- 创建时指定字符集（避免中文乱码）
CREATE DATABASE mydb 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 如果不存在则创建
CREATE DATABASE IF NOT EXISTS school;

1.2 选择数据库

-- 选择要操作的数据库
USE school;

-- 查看当前选择的数据库
SELECT DATABASE();

1.3 修改数据库

-- 修改数据库字符集
ALTER DATABASE school 
CHARACTER SET utf8 
COLLATE utf8_general_ci;

1.4 删除数据库

-- 删除数据库
DROP DATABASE school;

-- 安全删除（如果存在才删除）
DROP DATABASE IF EXISTS school;

-- 注意：删除后数据无法恢复！

二、表操作

2.1 MySQL数据类型

数值类型

类型大小范围用途
TINYINT1字节-128~127小整数
SMALLINT2字节-32768~32767中等整数
INT4字节-2147万~2147万标准整数
BIGINT8字节非常大大整数
FLOAT4字节单精度浮点数小数
DOUBLE8字节双精度浮点数精确小数
DECIMAL(m,d)变长精确小数金额

字符串类型

类型大小用途
CHAR(n)0-255字节固定长度字符串
VARCHAR(n)0-65535字节可变长度字符串
TEXT0-65535字节长文本数据
ENUM('值1','值2')1-2字节枚举值

日期时间类型

类型格式范围
DATEYYYY-MM-DD1000-01-01~9999-12-31
TIMEHH:MM:SS-838:59:59~838:59:59
DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 00:00:00~9999-12-31 23:59:59
TIMESTAMP时间戳1970-01-01~2038-01-19
YEARYYYY1901~2155

2.2 创建表

-- 基本语法
CREATE TABLE 表名 (
    列名1 数据类型 [约束],
    列名2 数据类型 [约束],
    ...
);

-- 创建学生表示例
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_no VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(50) NOT NULL,
    gender ENUM('男', '女') DEFAULT '男',
    age INT CHECK (age >= 0 AND age <= 100),
    email VARCHAR(100),
    phone VARCHAR(20),
    address VARCHAR(200),
    birthday DATE,
    enrollment_date DATE DEFAULT (CURRENT_DATE),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 创建课程表
CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_code VARCHAR(20) UNIQUE NOT NULL,
    course_name VARCHAR(100) NOT NULL,
    credit DECIMAL(3,1) DEFAULT 1.0,
    teacher VARCHAR(50),
    class_hours INT DEFAULT 36
);

2.3 查看表信息

-- 查看所有表
SHOW TABLES;

-- 查看表结构
DESC students;
-- 或
DESCRIBE students;

-- 查看建表语句
SHOW CREATE TABLE students;

2.4 修改表结构

-- 添加新列
ALTER TABLE students 
ADD COLUMN major VARCHAR(50) AFTER name;

-- 修改列定义
ALTER TABLE students 
MODIFY COLUMN name VARCHAR(100) NOT NULL;

-- 修改列名和定义
ALTER TABLE students 
CHANGE COLUMN phone mobile VARCHAR(15);

-- 删除列
ALTER TABLE students 
DROP COLUMN address;

-- 添加主键
ALTER TABLE students 
ADD PRIMARY KEY (id);

-- 添加唯一约束
ALTER TABLE students 
ADD UNIQUE (email);

-- 添加外键
ALTER TABLE scores 
ADD FOREIGN KEY (student_id) REFERENCES students(id);

-- 重命名表
ALTER TABLE students 
RENAME TO student_info;
-- 或
RENAME TABLE students TO student_info;

2.5 删除表

-- 删除表
DROP TABLE students;

-- 安全删除
DROP TABLE IF EXISTS students;

-- 清空表数据（保留表结构）
TRUNCATE TABLE students;

三、数据增删改查

3.1 插入数据 (INSERT)

-- 插入完整数据
INSERT INTO students (student_no, name, gender, age, email) 
VALUES ('2023001', '张三', '男', 20, 'zhangsan@qq.com');

-- 插入多条数据
INSERT INTO students (student_no, name, gender, age) VALUES
('2023002', '李四', '女', 21),
('2023003', '王五', '男', 22),
('2023004', '赵六', '女', 19);

-- 插入部分字段
INSERT INTO students (student_no, name) 
VALUES ('2023005', '孙七');

-- 使用SET语法
INSERT INTO students 
SET student_no = '2023006', 
    name = '周八', 
    age = 23;

-- 从其他表复制数据
INSERT INTO new_students 
SELECT * FROM students WHERE age > 20;

3.2 查询数据 (SELECT)

-- 查询所有列
SELECT * FROM students;

-- 查询指定列
SELECT id, name, age FROM students;

-- 使用别名
SELECT 
    id AS 学号,
    name AS 姓名,
    age AS 年龄
FROM students;

-- 去重查询
SELECT DISTINCT gender FROM students;
SELECT DISTINCT age, gender FROM students;

3.3 条件查询 (WHERE)

-- 比较运算符
SELECT * FROM students WHERE age > 20;
SELECT * FROM students WHERE age >= 18;
SELECT * FROM students WHERE age < 22;
SELECT * FROM students WHERE age <= 21;
SELECT * FROM students WHERE age = 20;
SELECT * FROM students WHERE age != 20;
SELECT * FROM students WHERE age <> 20;

-- 逻辑运算符
SELECT * FROM students WHERE age > 18 AND gender = '男';
SELECT * FROM students WHERE age < 20 OR gender = '女';
SELECT * FROM students WHERE NOT (age = 20);

-- 范围查询
SELECT * FROM students WHERE age BETWEEN 18 AND 22;
SELECT * FROM students WHERE age IN (18, 20, 22);
SELECT * FROM students WHERE age NOT IN (18, 20);

-- 模糊查询
SELECT * FROM students WHERE name LIKE '张%';   -- 张开头
SELECT * FROM students WHERE name LIKE '%三';   -- 三结尾
SELECT * FROM students WHERE name LIKE '%小%';  -- 包含小
SELECT * FROM students WHERE name LIKE '张_';   -- 张开头，两个字
SELECT * FROM students WHERE name NOT LIKE '张%';

-- NULL值判断
SELECT * FROM students WHERE email IS NULL;
SELECT * FROM students WHERE email IS NOT NULL;

3.4 排序查询 (ORDER BY)

-- 单列排序
SELECT * FROM students ORDER BY age;        -- 升序（默认）
SELECT * FROM students ORDER BY age ASC;    -- 升序
SELECT * FROM students ORDER BY age DESC;   -- 降序

-- 多列排序
SELECT * FROM students 
ORDER BY gender DESC, age ASC;

-- 结合条件查询
SELECT * FROM students 
WHERE age > 18 
ORDER BY age DESC;

3.5 限制查询 (LIMIT)

-- 限制返回条数
SELECT * FROM students LIMIT 5;

-- 分页查询（跳过前10条，取5条）
SELECT * FROM students LIMIT 10, 5;

-- 结合排序
SELECT * FROM students 
ORDER BY age DESC 
LIMIT 3;

3.6 更新数据 (UPDATE)

-- 更新所有行（谨慎使用）
UPDATE students SET age = age + 1;

-- 条件更新
UPDATE students SET age = 21 WHERE name = '张三';

-- 更新多个字段
UPDATE students SET 
    age = 22,
    email = 'lisi@163.com'
WHERE name = '李四';

-- 使用表达式
UPDATE students SET 
    age = age + 1,
    updated_at = NOW()
WHERE birthday = '2000-01-01';

3.7 删除数据 (DELETE)

-- 删除特定行
DELETE FROM students WHERE id = 5;

-- 删除多个条件
DELETE FROM students 
WHERE age < 18 OR email IS NULL;

-- 删除所有数据（危险！）
DELETE FROM students;

-- 安全删除建议：先查询确认
SELECT * FROM students WHERE id = 5;  -- 先确认
DELETE FROM students WHERE id = 5;    -- 再删除

四、查询进阶

4.1 聚合函数

-- 统计总数
SELECT COUNT(*) FROM students;
SELECT COUNT(email) FROM students;  -- 不统计NULL
SELECT COUNT(DISTINCT gender) FROM students;

-- 计算平均值
SELECT AVG(age) FROM students;
SELECT AVG(age) AS 平均年龄 FROM students;

-- 求和
SELECT SUM(age) FROM students;

-- 最大值最小值
SELECT MAX(age) FROM students;
SELECT MIN(age) FROM students;

-- 综合使用
SELECT 
    COUNT(*) AS 总人数,
    AVG(age) AS 平均年龄,
    MAX(age) AS 最大年龄,
    MIN(age) AS 最小年龄
FROM students;

4.2 分组查询 (GROUP BY)

-- 按性别分组
SELECT gender, COUNT(*) AS 人数
FROM students
GROUP BY gender;

-- 分组后条件筛选 (HAVING)
SELECT gender, AVG(age) AS 平均年龄
FROM students
GROUP BY gender
HAVING AVG(age) > 20;

-- 多列分组
SELECT gender, major, COUNT(*) AS 人数
FROM students
GROUP BY gender, major;

-- 分组排序
SELECT gender, COUNT(*) AS 人数
FROM students
GROUP BY gender
ORDER BY 人数 DESC;

4.3 连接查询 (JOIN)

创建关联表

-- 创建成绩表
CREATE TABLE scores (
    score_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course_id INT,
    score DECIMAL(5,2),
    exam_date DATE,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

内连接 (INNER JOIN)

-- 查询学生及成绩
SELECT 
    s.name AS 学生姓名,
    c.course_name AS 课程名称,
    sc.score AS 成绩
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id
INNER JOIN courses c ON sc.course_id = c.course_id;

-- 简化写法
SELECT 
    s.name, c.course_name, sc.score
FROM students s, scores sc, courses c
WHERE s.id = sc.student_id 
  AND sc.course_id = c.course_id;

左连接 (LEFT JOIN)

-- 查询所有学生（包括没有成绩的）
SELECT 
    s.name AS 学生姓名,
    sc.score AS 成绩
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id;

右连接 (RIGHT JOIN)

-- 查询所有课程（包括没有学生的）
SELECT 
    c.course_name AS 课程名称,
    sc.score AS 成绩
FROM scores sc
RIGHT JOIN courses c ON sc.course_id = c.course_id;

4.4 子查询

-- 单行子查询
SELECT * FROM students 
WHERE age > (SELECT AVG(age) FROM students);

-- 多行子查询
SELECT * FROM students 
WHERE id IN (SELECT student_id FROM scores WHERE score > 90);

-- 作为计算字段
SELECT 
    name,
    age,
    (SELECT AVG(age) FROM students) AS 平均年龄
FROM students;

-- EXISTS子查询
SELECT * FROM students s
WHERE EXISTS (
    SELECT 1 FROM scores sc 
    WHERE sc.student_id = s.id AND sc.score > 95
);

4.5 联合查询 (UNION)

-- 合并查询结果
SELECT name, '学生' AS 类型 FROM students
UNION
SELECT teacher, '教师' AS 类型 FROM courses;

-- UNION ALL（包含重复）
SELECT name FROM students WHERE gender = '男'
UNION ALL
SELECT name FROM students WHERE age > 20;

五、数据完整性

5.1 约束类型

-- 创建表时添加约束
CREATE TABLE employees (
    -- 主键约束
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- 非空约束
    name VARCHAR(50) NOT NULL,
    
    -- 唯一约束
    email VARCHAR(100) UNIQUE,
    
    -- 检查约束（MySQL 8.0+）
    age INT CHECK (age >= 18 AND age <= 65),
    
    -- 默认值
    status VARCHAR(20) DEFAULT 'active',
    
    -- 外键约束
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(id)
);

-- 创建外键级联操作
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) 
    REFERENCES customers(customer_id)
    ON DELETE CASCADE    -- 级联删除
    ON UPDATE CASCADE    -- 级联更新
);

5.2 索引优化

-- 创建索引
CREATE INDEX idx_name ON students(name);
CREATE INDEX idx_age_gender ON students(age, gender);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON students(email);

-- 查看索引
SHOW INDEX FROM students;

-- 删除索引
DROP INDEX idx_name ON students;

-- 使用索引的查询示例
-- 这些查询会使用索引：
SELECT * FROM students WHERE name = '张三';
SELECT * FROM students WHERE age > 20 AND gender = '男';
SELECT * FROM students ORDER BY name;

-- 这些可能不会使用索引：
SELECT * FROM students WHERE age + 1 > 20;  -- 使用函数
SELECT * FROM students WHERE name LIKE '%三%';  -- 前导通配符

六、实战练习

6.1 学生管理系统完整示例

-- 创建数据库
CREATE DATABASE school_system;
USE school_system;

-- 创建院系表
CREATE TABLE departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50) NOT NULL UNIQUE,
    dean VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建专业表
CREATE TABLE majors (
    major_id INT PRIMARY KEY AUTO_INCREMENT,
    major_name VARCHAR(50) NOT NULL,
    dept_id INT,
    duration INT DEFAULT 4,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- 创建班级表
CREATE TABLE classes (
    class_id INT PRIMARY KEY AUTO_INCREMENT,
    class_name VARCHAR(50) NOT NULL,
    major_id INT,
    counselor VARCHAR(50),
    student_count INT DEFAULT 0,
    FOREIGN KEY (major_id) REFERENCES majors(major_id)
);

-- 修改学生表，增加关联
ALTER TABLE students
ADD COLUMN class_id INT,
ADD COLUMN enrollment_year YEAR,
ADD FOREIGN KEY (class_id) REFERENCES classes(class_id);

-- 插入测试数据
INSERT INTO departments (dept_name, dean) VALUES
('计算机学院', '张教授'),
('经济学院', '李教授');

INSERT INTO majors (major_name, dept_id) VALUES
('计算机科学与技术', 1),
('软件工程', 1),
('经济学', 2);

-- 复杂查询示例
-- 1. 查询每个院系的学生人数
SELECT 
    d.dept_name AS 院系,
    COUNT(s.id) AS 学生人数
FROM departments d
LEFT JOIN majors m ON d.dept_id = m.dept_id
LEFT JOIN classes c ON m.major_id = c.major_id
LEFT JOIN students s ON c.class_id = s.class_id
GROUP BY d.dept_id;

-- 2. 查询各科平均成绩
SELECT 
    c.course_name AS 课程,
    AVG(sc.score) AS 平均分,
    COUNT(sc.score) AS 考试人数
FROM courses c
LEFT JOIN scores sc ON c.course_id = sc.course_id
GROUP BY c.course_id
HAVING COUNT(sc.score) > 0
ORDER BY 平均分 DESC;

6.2 常用实用查询模板

-- 分页查询模板
SELECT * FROM table_name
WHERE conditions
ORDER BY column_name
LIMIT (page_num - 1) * page_size, page_size;

-- 统计报表模板
SELECT 
    DATE_FORMAT(date_column, '%Y-%m') AS 月份,
    COUNT(*) AS 总数,
    SUM(amount) AS 总额,
    AVG(amount) AS 平均值
FROM table_name
WHERE date_column BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY DATE_FORMAT(date_column, '%Y-%m')
ORDER BY 月份;

-- 存在性检查
SELECT EXISTS(
    SELECT 1 FROM students 
    WHERE student_no = '2023001'
) AS 是否存在;

-- 随机获取记录
SELECT * FROM students
ORDER BY RAND()
LIMIT 1;

七、故障处理

7.1 常见错误及解决

连接错误

ERROR 1045 (28000): Access denied for user
解决：检查用户名密码，或重置密码

数据库不存在

ERROR 1049 (42000): Unknown database 'database_name'
解决：CREATE DATABASE 先创建数据库

表不存在

ERROR 1146 (42S02): Table doesn't exist
解决：检查表名拼写，或创建表

语法错误

ERROR 1064 (42000): You have an error in your SQL syntax
解决：检查SQL语句，特别注意引号、逗号

外键约束错误

ERROR 1452 (23000): Cannot add or update a child row
解决：先插入主表数据，再插入从表数据

7.2 备份与恢复

# 备份单个数据库
mysqldump -u root -p school > backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_backup.sql

# 备份指定表
mysqldump -u root -p school students courses > tables_backup.sql

# 恢复数据库
mysql -u root -p school < backup.sql

# 压缩备份
mysqldump -u root -p school | gzip > backup.sql.gz

# 解压恢复
gunzip < backup.sql.gz | mysql -u root -p school

7.3 性能优化建议

  1. 设计时优化
    • 选择合适的数据类型
    • 规范化表结构（但不要过度）
    • 为经常查询的列创建索引
  1. 查询时优化
    • 避免 SELECT *
    • 使用 LIMIT 限制结果
    • 避免在 WHERE 子句中使用函数
    • 使用 EXPLAIN 分析查询
  1. 维护建议
    • 定期优化表：OPTIMIZE TABLE table_name;
    • 定期分析表：ANALYZE TABLE table_name;
    • 监控慢查询日志
-- 查看当前连接
SHOW PROCESSLIST;

-- 终止查询
KILL process_id;

-- 查看系统变量
SHOW VARIABLES LIKE '%timeout%';

-- 查看状态
SHOW STATUS LIKE 'Connections';

八、快速参考

8.1 常用命令速查

操作命令
登录mysql -u root -p
查看数据库SHOW DATABASES;
使用数据库USE database_name;
查看表SHOW TABLES;
查看表结构DESC table_name;
退出EXIT; 或 \q

8.2 数据类型选择指南

存储内容推荐类型
用户IDINT UNSIGNED AUTO_INCREMENT
用户名VARCHAR(50)
密码哈希CHAR(60) (bcrypt)
邮箱VARCHAR(100)
手机号VARCHAR(20)
金额DECIMAL(10,2)
文章内容TEXT 或 LONGTEXT
是否启用TINYINT(1) 或 BOOLEAN
创建时间TIMESTAMP DEFAULT CURRENT_TIMESTAMP
更新时间TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

8.3 最佳实践清单

命名规范

  • ✅ 数据库名：小写，下划线分隔（如：school_db）
  • ✅ 表名：复数形式，小写（如：students）
  • ✅ 列名：小写，下划线分隔（如：created_at）
  • ❌ 避免使用MySQL保留字
  • ❌ 避免使用特殊字符

设计原则

  • ✅ 每列不可再分（第一范式）
  • ✅ 每个表必须有主键
  • ✅ 适当使用外键保持数据完整性
  • ✅ 考虑查询频率创建索引
  • ❌ 避免过度规范化
  • ❌ 避免存储大对象在数据库

安全建议

  • ✅ 定期备份数据（至少每天一次）
  • ✅ 生产环境使用专用账号，不用root
  • ✅ 应用程序使用只读/只写账号
  • ✅ 密码加密存储（不用明文）
  • ✅ 限制远程访问IP
  • ❌ 不要在代码中写死密码

性能优化

  • ✅ 为频繁查询的列创建索引
  • ✅ 查询时使用具体的列名，避免SELECT *
  • ✅ 合理使用LIMIT限制返回结果
  • ✅ 定期清理无用数据
  • ❌ 避免在WHERE子句中使用函数
  • ❌ 避免使用前导通配符的LIKE查询

8.4 实用代码片段

快速创建测试表

-- 快速创建测试用户表
CREATE TABLE test_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    age INT DEFAULT 18,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

常用日期查询

-- 今天的数据
SELECT * FROM orders WHERE DATE(order_date) = CURDATE();

-- 昨天的数据
SELECT * FROM orders WHERE DATE(order_date) = DATE_SUB(CURDATE(), INTERVAL 1 DAY);

-- 最近7天的数据
SELECT * FROM orders WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

-- 本月的订单
SELECT * FROM orders 
WHERE MONTH(order_date) = MONTH(CURDATE()) 
AND YEAR(order_date) = YEAR(CURDATE());

数据导出导入

-- 导出查询结果到文件
SELECT * INTO OUTFILE '/tmp/students.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM students;

-- 从文件导入数据
LOAD DATA INFILE '/tmp/students.csv'
INTO TABLE students
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
avatar
文章
阅读
粉丝