掌握mysql，看完这篇文章就够了

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

提示：这里可以添加本文要记录的大概内容：

例如：MySQL是一款功能强大、性能出色、易于使用和可靠的数据库管理系统，被广泛应用于各种类型的应用程序和网站开发中，本文就介绍了MySQL学习的基础内容。

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、MySQL是什么？

MySQL是一种开源的关系型数据库管理系统（RDBMS），它是最常用的数据库之一。MySQL由瑞典公司MySQL AB开发，目前由Oracle公司维护和支持。

MySQL具有以下特点：

1. 可靠性：MySQL在处理大规模数据时表现出色，具有安全、稳定的功能。 高性能：MySQL的查询速度快，可以处理高并发的请求。
2. 可扩展性：MySQL可以方便地进行垂直和水平扩展，以满足不断增长的数据需求。
3. 容易使用：MySQL具有直观的用户界面和简单的命令，对于开发人员和管理员来说都容易上手。
4. 多平台支持：MySQL可在不同的操作系统上运行，如Windows、Linux、macOS等。
5. 多语言支持：MySQL支持多种编程语言如C、C++、Java、Python等。

MySQL可以用于各种应用程序和网站开发，从小型网站到大型企业级应用都可以使用。它提供了丰富的功能和工具，如事务处理、触发器、存储过程、视图等，以满足不同的业务需求。

二、简单介绍一下数据库

数据库：对大量数据进行存储和管理（增删改查）

数据库分类：关系型数据库、非关系型数据库

关系型数据库的典型代表

• 企业级：MySQL、SQLserver

• 大型：Oracle

• 轻量级文件数据库：SQLite

特点：通过表产生关联关系，每个表中都存储结构化数据，支持SQL结构化查询语言

组织：数据库管理系统DBMS ==> 数据库DB、表table、行Row、列column

非关系型数据库典型代表

• 文档数据库：MangoDB

• 内存数据库：Redis

特点：存储管理非结构化数据，高性能，适应分布式

三、MySQL

学习数据可，其实学习数据库管理系统（一个数据库管理系统）

1.客户端

• 黑窗口终端

• workbentch：mysql自带的客户端

• navicat：熊掌软件（我所使用的）

• sqlite：轻量级文件数据库，Python自带sqlite3

2.数据类型

1.数字

整数：

• int，4个字节
• bigint：8个字节

浮点数

• float：单精度，4个字节

• double：双精度，8个字节

• decimal：高精度，需要指明精确度小数位数

2.字符串

• char：长度

• varchar：可变长度

• text：长文本

3.布尔

• bool：0、1

4.时间日期

• date
• datetime
• time
• timestamp

5.枚举

• enum：罗列所有可能，eg：“男”， “女”

3.用户创建，授权，删除

• select user()：查看当前用户

创建用户

• create user ‘用户名’@‘%’ identified by ‘密码’；

设置权限

• grant all on . to ‘用户名’@‘%’; ==> 将所有数据库中的所有权限给到用户

• grant select，insert on mydb.* to ‘用户名’@‘%’; ==> 将mydb中对所有表的查询（select）、插入（insert）权限分配到用户

删除用户

• drop user ‘用户名’@‘%’

刷新权限

• flush privileges；
  注：每次更改用户后都需要刷新一次

四、MySQL的基础语言

1.数据库相关

• 展示所有数据库：show databases;

• 删除数据库：drop database 数据库名;

• 创建数据库：create database 数据库名;

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

• 使用数据库： use 数据库名;

• 不存在就创建库：create database if not exists 数据库名 charset=utf8;（charset=utf8 ==> 编码)

2.表相关

• 查看所有的表：show tables;

• 查看表信息：describe 表名； // desc 表名；（简略的）

• 创建表：create table 表名（列 类型 约束信息， …）;

• 删除：drop table表名;

• 表中的列相关：

• alter table 表名+
  ：添加：add 列名 类型 约束;
  ：删除：drop 列名;
  ：修改：change 原列名 新列名 类型 约束;
  ：重命名：rename table 原始表名 to 新表名;

3.约束信息

• primary key：主键

一个表中必须有一个主键，主键列默认不能重复，一般都是有一个独立的列id 可以在创建表时在列类型之后使用primary key，或在定义完所有以后单独使用primary key（列名）

• not null ：不能为空

• auto_increment：自增长

• unique：不能重复

• varchar：可变

• default ：默认

• current_timestamp：当前时间戳

• foreign key ：外键

一个表中的外键是另一个表中的主键，和其他表发生关联

4.数据相关

查看

• 查看所有：select * from 表名；

• 指定列：select 列名 from 表名；

• 条件查询：select * from 表名 where 条件；

插入

• 全列插入：包含所有列
  insert into 表名 values(…插入的数据)，(), (), …；
  注：不管是否有默认值，都要插入对应的数据

• 缺省插入：需要指定列、指定值，有默认值可以省略
  insert into表名 (列名) values (数据);
  insert into 表名 set 列名 = 值，… ; （只能写入一行）

修改

• update 表名 set 列名=值，列2=值，…where 条件；
  注：没有添加条件会全部更改（整个表）

删除

• delete from 表名 where 条件；
  注：没有添加会清空整个表

分类

• 数据定义语言 DDL
  create、drop、alter、rename

• 数据操作语言DML
  insert、delete、update、select

提示：以下为注意点：
结尾要带分号（；）
不区分大小写

五、外键

创建

• 创建表时添加：

1. create table 表名 （列…,foreign key（外键列名） references 主表（主键）on update cascade ondelete cascade);
   例子：create table student(id int not null auto_increment,name varchar(30) not null,t_id int not null,primary key(id),foreign key(t_id) references teacher(id));

2. create table 表名（列…,constraint 外键名 foreign key(外键列表） references 主表(主键));
   例子：create table student(id int primary key not null auto_increment,name varchar(30) not null,t_id int not null,constraint fk_teacher_id foreign key(t_id) references teacher(id));

3. create table 表名（列…,constraint 外键名 foreign key(外键列名） references 主表(主键) on update cascade on delete cascade);
   例子：create table student(id int primary key not null auto_increment,name varchar(30),tid int not null, constraint fk_teacher_id foreign key(tid) references teacher(id) on update cascade on delete cascade);

• 创建表之后添加：
  alter table 表名 add constraint 外键名 foreign key（外键列名） references 主表（主键）on update cascade ondelete cascade）;

删除

alter table 表名 drop foreign key 外键名;

添加

alter table 表名 add constraint 外键名 foreign key （外键列名） references 主表（主键）

外键的修饰选项

• RESTRICT：拒绝
  加入有外键使用到主表中的主键，在修改删除主键时不能操作

• CASCADE：级联
  删除修改主表时，外键对应的内容直接删除

外键的值在主表中必须存在

六、查询语言

1.基础使用

• select * from 表名；

• select 列名… from 表名；

• select 列名 … from 表名 where 条件；

2.进阶查询基础

别名

针对查询到的结果（列名）起别名，常用于多表查询（有共同的列表）
select 列名 as 别名，… from表名 where 条件；

关键字 as

1. 表名 as 新名字（as可以省略）
2. 列的别名 可以用双引号（“”）引起来,不要用单引号（‘’）

查询指定列

select 列名 … from 表名 where 条件;

条件查询：where

• 比较运算符：=、<>、!=、<=、<、>=、>

• 逻辑运算符（多个条件）：and、or

• 成员运算符：in，在其中一个

• 范围比较：between and

• 判空
  is null：空
  is not null：不是空，空字符串不是空

模糊查询

select * from 表名 where 列名 like 条件

• like

1. %：任意n个字符
2. _ ：一个字符

• 例子：

1. where name like “黄%” ==》 查找姓黄的
2. where name like ‘%容%’ ==> 有容字的

3.关联查询

嵌套查询

一个表的查询结果作为另一个查询的条件
select * from student where tid in (select id from teacher where name = ‘t1’ or name = ‘t2’)；

笛卡尔连接

• 组合两个表中的所有数据

• 一个有m行，一个有n行，最终有m*n行

• select * from 表名，表名

连接查询

1.内连接

inner join…on 连接条件

例子：select student.name as 学生名，teacher.name as 教师名 from student inner join teacher on student.tid = teacher.id；
结果有14种

2.左外连接left join

内连接结果+左表内容（以左表为主，右表补空）

例子： select student.name as 学生名, teacher.name as 教师名 from student left join teacher on student.t_id * 3 = teacher.id;
结果有4种

3.右外连接right join

内连接结果+右表内容（以右表为主，左表补空）

例子：select student.name as 学生名, teacher.name as 教师名 from student right join teacher on student.t_id * 3 = teacher.id;
结果有4种

4.全连接

左连接的结果并上右连接

left join … union …right join…
上方例子结果有10种

提示：以下为补充：

• 表名.列名

• 不等于：！=、<>

七、系统函数与聚合函数

函数调用：select 函数名

系统函数

• user()：当前用户

• version()：当前数据库版本

• database()：当前数据库

• current_date：当前日期

• current_time：当前时间

• current_timestramp：当前日期时间

聚合函数

• max（列名）

• min（列名）

• sum（列名）

• avg（列名）

• count（任意列名）:统计结果行数

八、其他

1.排序

• order by 列名 排序方式，列名 排序方式
  asc：默认升序
  desc：降序

2.分页limit

• limit n：显示前n个
• limit m，n：从索引m开始显示n个，第一个索引是0

公式：limit（page-1）*size，size ==> 显示page页，每页显示size个

3.分组group by

select 分组的列名, count(*) from 表名 where 条件 group by 分组依据的列

例子：sele sex, count(*) from teacher where id > 3 group by sex;

• 在分组中使用having，类似where，针对分组结果进行处理

select …by… having 条件

sele sex, count(*) from teacher where id > 3 group by sex having sex = ‘女’;

4.去重

• distinct:去除某一列
  例子select distinct id from 表; 去掉相同的id号

九、扩展

提示：以下为扩展知识，可以仅做了解，理论>实践，可以不了解具体的SQL语句：

1.视图

视图：一张虚拟表，操作表等同于操作真实表，方便查询

创建视图

不要求使用SQL语句，可以使用工具

如上图所示
在工具中会自动生成优化sql语句，也可以自己定义

使用视图：等同于使用表
结果：

2.函数与存储过程

都是存在在服务器上的，可以提升数据的安全

函数

select 函数名(实参); ==> 在终端调用
经过计算返回一个结果（需要指定形参，指定返回值）

存储过程

call 函数名(实参); ==> 在终端调用
一套SQL操作，没有返回值

3.索引

索引：一种查询优化技术，可以提升查询效率（优点）
缺点：实现的本质是要预先存储一些额外数据，牺牲存储空间，提升查询效率

索引类型

1. 主键索引：主键自带主键索引
2. 唯一索引：唯一约束unique字段
3. 普通索引：任何字段

在大量数据中进行查询，有主键和唯一约束的查询速度较快，而普通数据（未添加索引）比前两种慢，eg：在user表中插入了10000条数据，进行id和name查询时只用了0.0秒，而地址、电话为0.3秒

何时定义索引

如果表的修改 频率非常高，不适合创建索引；
如果表的查询 频率非常高，几乎不修改，适合创建索引

注：索引不是创建的越多越好

索引方法

• BTREE（树）：大数据量，适合范围比较

• HASH（哈希）：适合小数据量，适合精准的等值比较 eg：id==101

4.事务

mysql存储引擎innoDB支持事务

mysql数据库中对应的一系列操作，要么全部执行成功然后提交，要么全部执行失败然后回滚.。
成功：commit 失败：rollback

• mysql终端默认提交
  set autocommit = 0； 取消自动提交
  如果想要提交需要手动提交，输入一个commit

• set autocommint = 1； 设置自动提交

事务相关

• 开启事务：start transcactuin； 默认不会自动提交
• 结束事务：commint 成果提交

ACID原则：

1. 原子性：不可再分，要么全部成功，要么全部失败
2. 一致性：执行前后数据要保持一致
3. 隔离性：多个事件互不影响
4. 永久性：一旦提交，永久不变

5.存储引擎

数据存储的实现方式，不同的存储引擎适合不同的场景

• 关键字engine
  create table() engine=引擎名字

常用

• InnoDB：默认
  特点：支持事务、外键，支持行级别锁定、阻塞，综合能力强，适合大多数场景

• MyISAM
  查询，排序速度非常快，不支持外键

• Memory
  读写内存速度最快，但不能持久化，不能在本地保存

• CSV
  使用逗号隔开，适合导入导出操作

6.数据库的备份与恢复

备份
将数据库信息转储为SQL文件，形成一个sql文件

在Navicat工具中选择转储sql文件的结构和数据

恢复
手动创建数据库，执行sql文件

然后运行备份好的sql文件

十、Mysql与Python基础交互

Python想要使用数据库，需要使用pymysql模块。

如果没有pymysql模块，可以在pycharm虚拟环境中下载安装包
命令：pip install pymysql

基础语句

• 导入模块：import pymysql

• 构建连接
  pymysql.connect(host=“localhost”, port=3306, user=“用户”, password=“密码”, database=“数据库名称”)

• 使用数据库：连接.select_db(“数据库名称”)，con.select_db(“wll”)

• 断开连接：close()

• 创建游标实例：游标 = 连接.cursor()，例：cur = con.cursor()

• 通过游标实例执行sql语句：游标.execute(“sql语句”)
  例：sql = “select * from user;”
  line = cur.execute(sql) # 6（影响行数）
  注：execute返回的是一个数

• 获取游标中的内容
  fetchall() 返回所有，元组；只有fetchall需要遍历
  fetchmany(size=个数) 获取多个
  fetchone 获取一行

• 游标的偏移
  scroll(size, mode=)
  relative：相对的；absolute：绝对的

注：最后一定要释放游标和连接

• 自动提交
  get_autocommit() # 是否自动提交
  autocommit(True) # 设置自动提交，con.autocommit(True)

• commit() # 手动提交，eg：con.commit()

占位参数：%s

• %s：对应的是在execute中args的参数，对应的是列表或者元组
  Eg：insert into user values (%s, %s, %s, %s)
  cur.execute(sql, args=(name, address))

• %(name)s：字典
  Eg：insert into user values (%(id)s, %(name)s, %(address)s, %(phone)s) # 过于麻烦不建议使用字典

executemany()

不支持字典
大列表（元组）里套列表（元组），里边的一个列表就是一条数据
例子：
args = [[20 + i, f"守望{20 + i}“, f"东三街{20+i}号”, f"15103120{200+i}"] for i in range(10)]
sql = “insert into user values (%s, %s, %s, %s)”
line = cur.executemany(sql, args)

使用流程

1.导入模块
2.构建连接
3.构建游标
4.通过游标执行sql语句
5.处理sql结果
6.释放游标与连接

上述知识的代码实例

# 1.导入pymysql模块
import pymysql
# 2.构建一个连接
con = pymysql.connect(host="localhost", port=3306, user="root", password="123456", database="wll")
# 2.1使用数据库
# con.select_db("wll")
# 3.通过连接创建一个游标实例
cur = con.cursor()
# 4.通过游标实例执行sql语句
sql = "select * from user;"
line = cur.execute(sql)  # 行数
# 4.1获取游标中的内容
# fetchone获取一行
data = cur.fetchone()
print(data)
# (1, '守望0', '东三街0号', '15103120000')

print("-----------------------------------------------------------")
# fetchmany获取多个
datas = cur.fetchmany(size=3)
for data in datas:
    print(data)

# (2, '守望1', '东三街1号', '15103120001')
# (3, '守望2', '东三街2号', '15103120002')
# (4, '守望3', '东三街3号', '15103120003')
print("-----------------------------------------------------------")

# fetchall()返回所有
datas = cur.fetchall()  # 元组
for data in datas:
    print(data)

# (5, '守望4', '东三街4号', '15103120004')
# (6, '守望5', '东三街5号', '15103120005')
# (7, '守望6', '东三街6号', '15103120006')
# (8, '守望7', '东三街7号', '15103120007')
print("-----------------------------------------------------------")

cur.scroll(-4, mode="relative")
data = cur.fetchone()
print(data)
# (5, '守望4', '东三街4号', '15103120004')
print("-----------------------------------------------------------")

cur.scroll(1, mode="relative")
data = cur.fetchone()
print(data)
# (7, '守望6', '东三街6号', '15103120006')
print("-----------------------------------------------------------")


# 5.释放游标与连接
cur.close()
con.close()

补充：

import pymysql

con = pymysql.connect(user="root", password="123456", database="wll")
print("是否自动提交", con.get_autocommit())  # 是否自动提交 False

# 设置自动提交
con.autocommit(True)
print("是否自动提交", con.get_autocommit())  # 是否自动提交 True

cur = con.cursor()
sql = "insert into teacher set name='t12';"
line = cur.execute(sql)
print("影响行数", line)

# 手动提交
# con.commit()

cur.close()
con.close()

"""
%s：列表或者元组
insert into user values (%s, %s, %s, %s)

%(name)s：字典
Eg：
insert into user values (%(id)s, %(name)s, %(address)s)
# 过于麻烦不建议使用字典

executemany()
不支持字典
"""
import pymysql

con = pymysql.connect(user="root", password="123456", database="wll")

cur = con.cursor()

sql = "select * from user"
# 分页
sql = "select * from user limit 10, 9;"
# 查询
sql = "select * from user where name='守望68' and address='东三街68号'"  # 普通

name = input("输入用户名：")
address = input("请输入地址：")

sql = "select * from user where name= '" + name + "' and address= '" + address + "' ;"
line = cur.execute(sql)

# 占位参数
sql = "select * from user where name= %s and address= %s ;"
line = cur.execute(sql, args=(name, address))

uid = int(input("输入用户id"))
sql = "select * from user where id > %s;"
line = cur.execute(sql, args=(uid, ))


con.autocommit(True)

# 插入语句
sql = "insert into user values (0, '守望1001', '东三街4号', '15103120904')"
line = cur.execute(query=sql)

args = (0, '守望1001', '东三街4号', '15103120904')
sql = "insert into user values (%s, %s, %s, %s)"
line = cur.execute(query=sql, args=args)

# 效率较低，不建议使用
for i in range(10):
    args = (0, f'守望{1001+i}', f'东三街{4+i}号', f'1510312091{i}')
    sql = "insert into user values (%s, %s, %s, %s)"
    line = cur.execute(query=sql, args=args)

    print("影响行数", line)


args = {
    "id": 0,
    "name": "守望41",
    "address": "东三街41号",
    "phone": "15103120941"

}
sql = "insert into user values (%(id)s, %(name)s, %(address)s, %(phone)s)"
line = cur.execute(query=sql, args=args)
print("影响行数", line)


# executemany()
args = [[20 + i, f"守望{20 + i}", f"东三街{20+i}号", f"15103120{200+i}"] for i in range(10)]
sql = "insert into user values (%s, %s, %s, %s)"
line = cur.executemany(sql, args)
print("影响行数", line)

datas = cur.fetchall()
for data in datas:
    print(data)
cur.close()
con.close()

Python中使用事务

"""
食物：一套sql操作，要么同时执行，要么同时回滚撤销
创建表需要使用inno DB存储引擎

ACID 四大特性
1.原子性：不可再分，要么全部成功，要么全部失败
2.一致性：执行前后数据要保持一致
3.隔离性：多个事件互不影响
4.永久性：一旦提交，永久改变

start transaction 开启事务
commit 提交事务
rollback 回滚
"""

import pymysql
from pymysql import OperationalError, IntegrityError

try:
    con = pymysql.connect(host="localhost", port=3306, user="root", password="123456")
    con.select_db("wll")
    print("是否自动提交", con.get_autocommit())  # 是否自动提交 False
    cur = con.cursor()

    try:
        # 开启一个事务
        con.begin()

        sql0 = "delete from user where id > 100"
        line = cur.execute(sql0)
        print("影响行数：", line)  # 影响行数： 613

        sql = "insert into user values (%s, %s, %s, %s)"
        # 使用参数
        cur.execute(sql, (0, '守望101', '东三街8号', '15103120008'))
    except IntegrityError as e:
        print("执行sql语句错误", e)
        # 回滚
        con.rollback()

    else:
        print("sql语句执行成功，事务结束")
        # 提交事务
        con.commit()

except OperationalError as e:
    print("连接异常", e)

else:
    print("断开数据库的连接")
    cur.close()
    con.close()

十一、Mysql辅助类封装

父类

import pymysql
from pymysql import IntegrityError


class MySqlHelper:
    def __init__(self):
        self.con = pymysql.connect(user="root", password="123456", database="wll")

        self.cur = self.con.cursor()

    def query_one(self, sql, args=None):
        self.cur.execute(sql, args)
        return self.cur.fetchone()

    def query_many(self, sql, args=None, size=2):
        self.cur.execute(sql, args)
        return self.cur.fetchmany(size)

    def query_all(self, sql, args):
        self.cur.execute(sql, args)
        return self.cur.fetchall()

    def update(self, sql, args=None):
        try:
            # 开启事务
            self.con.begin()
            line = self.cur.execute(sql, args)
            print("影响行数：", line)

        except IntegrityError as e:
            print("执行sql语句错误", e)
            # 回滚
            self.con.rollback()

        else:
            print("sql语句执行成功，事务结束")
            # 提交事务
            self.con.commit()

    def __del__(self):
        self.cur.close()
        self.con.close()

子类

from mysqlhelper import MySqlHelper


class UserHelper:
    @staticmethod
    def login(username):
        # 登录函数
        sqlhelper = MySqlHelper()
        result = sqlhelper.query_one("select * from user where name = %s", (username,))
        return True if result else False

    @staticmethod
    def registry(username, address, telephone):
        # 注册函数
        sqlhelper = MySqlHelper()
        result = sqlhelper.update("insert into user values (%s, %s, %s, %s)", (0, username, address, telephone))
        return True if result else False

使用

from userhelper import UserHelper


def main():
    while True:
        username = input("输入用户名")
        result = UserHelper.login(username)
        if result:
            break
        else:
            print("请重新登录")
    print("登录成功")


if __name__ == '__main__':
    main()

---

总结

以上就是今天要讲的内容，本文介绍了MySQL的使用，望诸君共赏此篇文章。愿同道之人，相互启迪，共同进步。助人自助，团结一心，方能共创美好未来。祝愿学无止境，日臻完善，共享学习之乐！