MySQL新手教学

目录

一、MySQL 下载与安装

1. 版本选择

2. 下载地址

3. 安装步骤（Windows）

4. 环境变量配置

5. 配置文件

二、MySQL 基本操作

1. 登录与退出

2. 数据库操作

3. 表操作

三、数据操作（CURD）

1. 新增（Insert）

2. 查询（Select）

3. 修改（Update）

4. 删除（Delete）

四、数据类型与约束

1. 常用数据类型

2. 约束（保证数据合法性）

五、数据库设计：关系与范式

1. 表关联关系

2. 三大范式（设计原则）

六、高级查询

1. 条件查询（where）

2. 排序与分页

3. 分组与聚合

4. 关联查询

七、用户与权限管理

1. 用户操作

2. 权限管理

八、事务与索引

1. 事务（保证数据一致性）

2. 索引（提升查询速度）

九、Python 操作 MySQL（PyMySQL）

一、MySQL 下载与安装

1. 版本选择

MySQL 提供多个版本，核心区别如下：

Enterprise Edition（企业版）：稳定性强，适合生产环境，付费且提供官方支持。

Community Edition（社区版）：免费开源，包含最新测试功能，适合学习和中小型项目。推荐选择 MySQL Installer for Windows，支持 8.0、5.7 等版本（新手建议 8.0，兼容性更好）。

2. 下载地址

官网直接下载：https://www.mysql.com/downloads/，选择对应系统的社区版安装包即可。

3. 安装步骤（Windows）

1. 运行安装包，选择 “Custom” 自定义安装，勾选 “MySQL Server” 及必要组件。
2. 配置服务器类型：开发环境选 “Developer Machine”，端口默认 3306（可修改）。
3. 设置 root 用户密码（务必牢记），添加其他用户（可选）。
4. 完成安装后，勾选 “Start the MySQL Server at System Startup” 开机启动服务。

4. 环境变量配置

为了在任意目录使用mysql命令，需配置环境变量：

1. 找到 MySQL 安装路径的bin目录（默认：C:\Program Files\MySQL\MySQL Server 8.0\bin）。
2. 右键 “此电脑”→“属性”→“高级系统设置”→“环境变量”，在 “系统变量” 的Path中添加上述路径。

5. 配置文件

MySQL 核心配置文件为my.ini，默认路径：C:\ProgramData\MySQL\MySQL Server 8.0\my.ini（ProgramData为隐藏文件夹）。
关键配置项（[mysqld]节点下）：

port=3306：端口号

datadir=C:/ProgramData/MySQL/MySQL Server 8.0/Data：数据存储路径

default-storage-engine=INNODB：默认存储引擎

max_connections=151：最大连接数（可根据需求调整，注意及时关闭无用连接）

二、MySQL 基本操作

1. 登录与退出

打开命令提示符（CMD），输入登录命令：

-- 本地登录（默认端口3306）
mysql -u root -p  
-- 远程登录（指定IP和端口）
mysql -u root -p -h 192.168.1.100 -P 3306

输入密码后登录成功，退出用quit或exit。
注意：MySQL 命令不区分大小写，单行以分号;结尾。

2. 数据库操作

-- 查看所有数据库：
show databases;

-- 创建数据库（指定字符集为 utf8，支持中文）：
create database 数据库名 charset=utf8;

-- 删除数据库（谨慎操作！）：
drop database 数据库名;

-- 切换数据库（操作表前必须先切换）：
use 数据库名;


-- 查看当前使用的数据库：
select database();

3. 表操作

-- 创建表
create table if not exists 表名 (
  列名1 类型 约束,
  列名2 类型 约束,
  primary key(列名1) -- 声明主键
);

示例：创建学生表
create table if not exists student (
  id int not null auto_increment,
  name varchar(20) not null,
  age int,
  primary key(id)
);
-- 查看表
查看所有表：show tables;
查看表结构：desc 表名;
-- 修改表
修改表名：alter table 旧表名 rename to 新表名;
添加列：alter table 表名 add 列名 类型 约束;
修改列（含列名）：alter table 表名 change 原列名 新列名 类型 约束;
删除列：alter table 表名 drop 列名;
-- 删除表
drop table if exists 表名; -- if exists：避免表不存在时报错

三、数据操作（CURD）

1. 新增（Insert）

-- 全列插入（需按列顺序填写所有值）：
insert into 表名 values (值1, 值2, ...);
-- 插入多行
insert into 表名 values (值1, 值2), (值3, 值4);

-- 指定列插入（只填需要的列，推荐）：
insert into 表名 (列1, 列2) values (值1, 值2);

2. 查询（Select）

查询所有列：select * from 表名;（*表示所有列，生产环境尽量指定列名）

查询指定列：select 列1, 列2 from 表名;

别名查询：select 列1 as 别名1, 列2 as 别名2 from 表名;

3. 修改（Update）

update 表名 set 列1=值1, 列2=值2 where 条件;

注意：必须加where条件，否则会修改表中所有数据！
示例：修改 id=1 的学生年龄为 20

update student set age=20 where id=1;

4. 删除（Delete）

delete from 表名 where 条件;

注意：同update，不加where会删除所有数据！

四、数据类型与约束

1. 常用数据类型

2. 约束（保证数据合法性）

主键约束（primary key）：唯一标识一行数据，不可重复且非空，每个表必须有主键。

组合主键（多列共同作为主键） primary key(列1, 列2)

非空约束（not null）：列值不可为空。

唯一约束（unique）：列值不可重复（允许 null）。

默认约束（default）：未指定值时使用默认值。age int default 18,   默认为18

外键约束（foreign key）：关联两个表，保证数据一致性（如学生表的teacher_id关联教师表的id）。foreign key(teacher_id) references teacher(id)

五、数据库设计：关系与范式

1. 表关联关系

一对一：如 “用户表” 和 “用户详情表”，可通过外键关联（外键加唯一约束）。

一对多：如 “班级表” 和 “学生表”，外键设在 “多” 的一方（学生表存班级 id）。

多对多：如 “学生表” 和 “课程表”，需通过中间表（如 “学生选课表”）存储两者主键。

2. 三大范式（设计原则）

第一范式：列不可再分（如 “地址” 拆分为 “省”“市”）。

第二范式：非主键列完全依赖主键（避免部分依赖）。

第三范式：非主键列不依赖其他非主键列（避免传递依赖）。

六、高级查询

1. 条件查询（where）

-- 比较运算符：>、<、=、<>（不等于）
select * from student where age > 18;

-- 逻辑运算符：and、or、not
select * from student where age > 18 and sex = '男';

-- 范围查询：between...and
select * from student where age between 18 and 22;

-- 成员查询：in
select * from student where class_id in (1, 3, 5);

-- 模糊查询：like（%匹配任意个，_匹配1个）
select * from student where name like '张%'; -- 姓张的人

2. 排序与分页

排序（order by）：

-- 按年龄降序（asc为升序，默认）
select * from student order by age desc; 

分页（limit）：

-- 第1页（前10条）
select * from student limit 10;
-- 第2页（从第10条开始，取10条）
select * from student limit 10, 10;
-- 通用公式：第m页，每页n条 → limit (m-1)*n, n

3. 分组与聚合

聚合函数：count()（计数）、sum()（求和）、avg()（平均值）、max()（最大值）、min()（最小值）。

select avg(age) as 平均年龄 from student;
select sum(age) as 平均年龄和 from student;
select max(age) as 最大年龄 from student;
select min(age) as 最小年龄 from student;

分组（group by）：结合聚合函数统计分组数据，用having过滤分组结果（类似where，但having可接聚合函数）。

-- 按班级分组，统计每个班级的人数（人数>30的班级）
select class_id, count(*) as 人数 from student 
group by class_id 
having 人数 > 30;

4. 关联查询

内连接（inner join）：只返回两表匹配的行。

select s.name, c.class_name 
from student s 
inner join class c on s.class_id = c.id;

左外连接（left join）：返回左表所有行，右表无匹配则为 null。

右外连接（left join）：返回右表所有行，左表无匹配则为 null。

子查询：嵌套查询，内层结果作为外层条件。

-- 查询年龄大于平均年龄的学生
select * from student where age > (select avg(age) from student);

七、用户与权限管理

1. 用户操作

-- 创建用户：
create user "用户名"@"主机地址" identified by "密码";

-- 示例：允许本地登录的temp用户
create user "temp"@"localhost" identified by "123456";

-- 修改密码：
alter user "用户名"@"主机地址" identified by "新密码";

-- 删除用户：
drop user "用户名"@"主机地址";

2. 权限管理

• 授权（all表示所有权限，*.*表示所有数据库的所有表）：

-- 授予temp用户所有权限
grant all on *.* to "temp"@"localhost";
-- 只授予查询和删除student表的权限
grant select, delete on 数据库名.student to "temp"@"localhost";

-- 刷新权限（授权后需执行）
flush privileges;

八、事务与索引

1. 事务（保证数据一致性）

事务是一组操作的集合，要么全成功，要么全失败（如转账：扣钱和加钱必须同时成功）。ACID 特性：原子性（不可分割）、一致性（前后数据一致）、隔离性（互不干扰）、持久性（提交后永久生效）。

操作命令：

start transaction; -- 开启事务，或者begin
-- 执行SQL操作（如update、insert）
commit; -- 提交事务（成功时）
rollback; -- 回滚事务（失败时）

2. 索引（提升查询速度）

索引是对列的预处理，类似书籍目录，可加速查询，但会降低插入 / 更新速度（需维护索引）。

常用索引类型：主键索引（primary key）：自动创建。

唯一索引（unique）：基于唯一约束自动创建。

普通索引：create index 索引名 on 表名(列名);

适合建索引的场景：查询频繁、值唯一或重复少的列（如身份证号）。

不适合：更新频繁、值重复多的列（如性别）。

九、Python 操作 MySQL（PyMySQL）

通过PyMySQL库可在 Python 中操作 MySQL，步骤如下：

1. 安装库：pip install pymysql
2. 基本操作示例：

import pymysql

# 连接数据库
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='密码',
    database='数据库名',
    port=3306,
    charset='utf8'
)

# 创建游标（执行SQL）
cursor = conn.cursor()

# 执行查询
cursor.execute("select * from student;")
result = cursor.fetchall()  # 获取所有结果
print(result)

# 执行增删改（需提交事务）
try:
    cursor.execute("insert into student(name) values('张三');")
    conn.commit()  # 提交
except:
    conn.rollback()  # 失败回滚

# 关闭连接
cursor.close()
conn.close()