数据库管理系统（DBMS，Database Management System）的软件，可以帮助我们实现对文件夹中的文件进行操作，而我们只要学习DBMS能识别的指令，就能控制它去帮助我们实现的文件和文件夹的处理。例如：

数据库管理系统（DBMS）专注于帮助开发者解决数据存储的问题，这样开发者就可以把主要精力放在实现业务功能上了。

业内有很多的的数据库管理系统产品，例如：

MySQL，原来是sun公司，后来被甲骨文收购。现在互联网企业几乎都在使用。【免费 + 收费】

Oracle，甲骨文。收费，一般国企、事业单位居多。【收费】

Microsoft SQL Server，微软。【收费】

DB2，IBM。【免费 + 收费】

SQLite，D. Richard Hipp个人开发。【免费】

Access，微软。【收费】

PostgreSQL，加州大学伯克利分校。【免费】

等众多..

由于各大公司都是使用MySQL，所以我们课程主要给大家讲解MySQL数据库。

在项目开发中想要基于MySQL来进行数据存储，大致应该怎么做呢？

数据库管理

内置客户端操作

安装配置完成后后，进入mysql。

常用基本代码：

show databases; 查看当前数据库

create database xx ; 创建一个名为xx的数据库(默认utf-8编码）

drop database xx; 删除名为xx的数据库

use db1; -> show tables; 查看该数据库下的所有数据表

desc 表名; 查看数据表信息（如有什么列，默认值多少，是否可以为空）

select * from 表; 查看表里的内容

exit; 退出

Python代码操作

想要使用Python操作MySQL需要安装第三方模块：

import pymysql
# 1. 连接MySQL（底层就是用socket链接的，utf8不是utf-8）
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8')
cursor = conn.cursor() #创建一个游标 初学要注意哪里用conn.commit() 哪里用cursor.fetchone()
# 2. 创建数据库（新增、删除、修改）
# 发送指令
cursor.execute('show databases')
# 获取返回的结果(接收要加cursor.fetchall()等才能收到）
result = cursor.fetchall()
print(result)
#(('bookmanager',), ('information_schema',), ('mysql',), ('new_store',), ('performance_schema',)...)
#result = cursor.fetchone()     返回：('bookmanager',)
# 2. 创建数据库（新增、删除、修改要加conn.commit()才能执行）
# 发送指令(后面默认的编码排序等可以不写）
cursor.execute("create database adb3 default charset utf8 collate utf8_general_ci")
conn.commit()
# 4. 删除数据库
cursor.execute('drop database adb3')
conn.commit()
# 5. 进入数据库，查看表
cursor.execute('use bookmanager')
cursor.execute('show tables')
result1 = cursor.fetchall()
print(result1)
#(('app01_author',), ('app01_author_books',), ('app01_book',), ('app01_publisher',)...)
cursor.close()
conn.close()

对于一些基础的命令错了、数据库创建重名、数据库不存在都会报错。

对于python操作，要先链接，之后设置游标，之后用游标发送命令。

链接：conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8')

设置游标：cursor = conn.cursor()

发送命令如：cursor.execute('show databases')

接收返回用cursor.fetchall()

执行增删改用conn.commit()

cursor.fecthall() 返回全部信息，元组里套元组，没有为一个空括号

cursor.fecthone() 返回第一条信息，一个元组里面是想要的信息，没有返回None

数据表管理

内置客户端操作

数据表常见操作的指令：

进入数据库 use 数据库;，查看当前所有表：show tables;
创建表结构

create table 表名(

列名类型,

列名类型

)default charset=utf8;

最后一个并不要加逗号

create table tb1(
    id int,
    name varchar(16)
)default charset=utf8;

写一行一样，这样习惯。

 create table tb4(
    id int primary key,             -- 主键（不允许为空、不能重复）
    name varchar(16) not null,   -- 不允许为空
    email varchar(32) null,      -- 允许为空（默认）
    age int default 3            -- 插入数据时，如果不给age列设置值，默认值：3
)default charset=utf8;

主键一般用于表示当前这条数据的ID编号（类似于人的身份证），需要我们自己来维护一个不重复的值，比较繁琐。所以，在数据库中一般会将主键和自增结合。

create table tb5(
    id int not null auto_increment primary key,    -- 不允许为空 & 主键 & 自增
    name varchar(16) not null,           -- 不允许为空
    email varchar(32) null,              -- 允许为空（默认）
    age int default 3                    -- 插入数据时，如果不给age列设置值，默认值：3
)default charset=utf8;

注意：一个表中只能有一个自增列【自增列，一般都是主键】。

注意：在mysql里 -- 代表注释

查看表

desc 表名;

删除表 drop table 表名;

清空表 delete from 表名; 或者 truncate from 表名;（速度快、无法回滚撤销等）

修改表

添加列

alter table 表名 add 列名类型;

alter table 表名 add 列名类型 DEFAULT 默认值;

alter table 表名 add 列名类型 not null default 默认值;

alter table 表名 add 列名类型 not null primary key auto_increment;

删除列

alter table 表名 drop column 列名;

修改列类型

alter table 表名 modify column 列名类型;

修改列类型+名称

alter table 表名 change 原列名新列名新类型;

alter table tb change id nid int not null;

alter table tb change id id int not null default 5;

alter table tb change id id int not null primary key auto_increment;

alter table tb change id id int; -- 允许为空，删除默认值，删除自增。

可用上面这一种方式修改已有列

修改列默认值

ALTER TABLE 表名 ALTER 列名 SET DEFAULT 1000;

删除列默认值

ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

添加主键

alter table 表名 add primary key(列名);

删除主键

alter table 表名 drop primary key;

Python代码操作

与上面一样不进行过多列举

import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8')
cursor = conn.cursor() #创建一个游标 初学要注意哪里用conn.commit() 哪里用cursor.fetchone()
cursor.execute("create database db3 default charset utf8 collate utf8_general_ci")
conn.commit()
cursor.execute('use db3')
message = '''
create table t4(
    id int not null primary key auto_increment,
    title varchar(128),
    content text,
    ctime datetime
)default charset=utf8;
'''
cursor.execute(message)
cursor.execute('show tables')
cursor.execute('desc t4')
result = cursor.fetchall()
print(result)
#(('id', 'int', 'NO', 'PRI', None, 'auto_increment'), ('title', 'varchar(128)', 'YES', '', None, ''), ('content', 'text', 'YES', '', None, ''), ('ctime', 'datetime', 'YES', '', None, ''))
cursor.close()
conn.close()

但对于一些数据类型显示的时候会告诉你这是什么数据

但遍历出来就没事了很多东西都是这样，如字典.items()会生成一个伪列表。

数据行管理

内置客户端操作

数据行操作的相关SQL语句（指令）如下：

新增数据

insert into 表名 (列名,列名,列名) values(对应列的值,对应列的值,对应列的值);

insert into tb1(name,password) values('卢本伟','123123');
insert into tb1(name,password) values('卢本伟','123123'),('alex','123');
insert into tb1 values('卢本伟','123123'),('alex','123'); -- 如果表中只有2列

放心写，不符合设定什么的都会报错。记得values 和字符串要加引号。

左边的可以不写全（如有自增的，默认值的）但右边一定要和左边数量相等且相匹配。

主键自增也是默认自增，12345，你可以直接插入一条id是7的数据，像这种主键默认是不允许重复的。

删除数据

delete from 表名;

delete from 表名 where 条件;

delete from tb1;
delete from tb1 where name="lbw";
delete from tb1 where name="lbw" and password="123";
delete from tb1 where id>9;

修改数据

update 表名 set 列名=值;

update 表名 set 列名=值 where 条件; （注：一个等号）

update tb1 set name="lbw";
update tb1 set name="lbw" where id=1;
update tb1 set age=age+1;  -- 整型
update tb1 set age=age+1 where id=2;
update L3 set name=concat(name,"db");
update L3 set name=concat(name,"123")  where id=2;  -- concat一个函数，可以拼接字符串

查询数据

select * from 表名;

select 列名,列名,列名 from 表名;

select 列名,列名 as 别名,列名 from 表名;

select * from 表名 where 条件;

select * from tb1;
select id,name,age from tb1;
select id,name as N,age, from tb1;
select id,name as N,age, 111 from tb1;
select * from tb1 where id = 1;
select * from tb1 where id > 1;
select * from tb1 where id != 1;
select * from tb1 where name="wupeiqi" and password="123";

Python代码操作

注意这里链接的时候写了参数 db='userdb'，即连接到userdb这个数据库。

相当于执行了 cursor.execute('use userdb')

import pymysql
# 连接MySQL，自动执行 use userdb; -- 进入数据库
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
cursor = conn.cursor()
# 1.新增（需commit）
"""
cursor.execute("insert into tb1(name,password) values('卢本伟','123123')")
conn.commit()
"""
# 2.删除（需commit）
"""
cursor.execute("delete from tb1 where id=1")
conn.commit()
"""
# 3.修改（需commit)
"""
cursor.execute("update tb1 set name='xx' where id=1")
conn.commit()
"""
# 4.查询
"""
cursor.execute("select * from tb where id>10")
data = cursor.fetchone() # cursor.fetchall()
print(data)
"""
# 关闭连接
cursor.close()
conn.close()

案例

例如：实现一个用户管理系统。

先使用MySQL自带的客户端创建相关数据库和表结构（相当于先创建好Excel结构）。

create database usersdb default charset utf8 collate utf8_general_ci;
create table users(
    id int not null primary key auto_increment,
    name varchar(32),
    password varchar(64)
)default charset=utf8;

再在程序中执行编写相应的功能实现注册、登录等功能。

import pymysql


def register():
    print("用户注册")

    user = input("请输入用户名：") # alex
    password = input("请输入密码：") # sb

    # 连接指定数据
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db="usersdb")
    cursor = conn.cursor()

    # 执行SQL语句（有SQL注入风险，稍后讲解）
    sql = 'insert into users(name,password) values("{}","{}")'.format(user, password)
    cursor.execute(sql)
    conn.commit()

    # 关闭数据库连接
    cursor.close()
    conn.close()

    print("注册成功，用户名：{},密码:{}".format(user, password))


def login():
    print("用户登录")

    user = input("请输入用户名：")
    password = input("请输入密码：")

    # 连接指定数据
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db="usersdb")
    cursor = conn.cursor()

    # 执行SQL语句（有SQL注入风险，稍后讲解）
    # sql = select * from users where name='wupeiqi' and password='123'
    sql = "select * from users where name='{}' and password='{}'".format(user, password)
    cursor.execute(sql)
    result = cursor.fetchone() # 去向mysql获取结果
    # 没有返回 None
    # 关闭数据库连接
    cursor.close()
    conn.close()

    if result:
        print("登录成功", result)
    else:
        print("登录失败")
    #这里可以细分 只去找账号，返回账号是否不存在，然后根据元组坎密码是否对应正确。


def run():
    choice = input("1.注册；2.登录")
    if choice == '1':
        register()
    elif choice == '2':
        login()
    else:
        print("输入错误")


if __name__ == '__main__':
    run()

注意，看到引号没 -> 这个要加不然有时会出错

不过不应该这样写，不用字符串格式化，下面MySQL注入会讲

应该

So，你会发现，在项目开发时，数据库 & 数据表的操作其实就做那么一次，最最常写的还是对数据行的操作

关于SQL注入

假如，你开发了一个用户认证的系统，应该用户登录成功后才能正确的返回相应的用户结果。

import pymysql

# 输入用户名和密码
user = input("请输入用户名：") # ' or 1=1 -- 
pwd = input("请输入密码：") # 123


conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8",db='usersdb')
cursor = conn.cursor()

# 基于字符串格式化来 拼接SQL语句
# sql = "select * from users where name='alex' and password='123'"
# sql = "select * from users where name='' or 1=1 -- ' and password='123'"
sql = "select * from users where name='{}' and password='{}'".format(user, pwd)
cursor.execute(sql)

result = cursor.fetchone()
print(result) # None，不是None

cursor.close()
conn.close()

如果用户在输入user时，输入了： ' or 1=1 -- ，这样即使用户输入的密码不存在，也会可以通过验证。

为什么呢？

因为在SQL拼接时，拼接后的结果是：

select * from users where name='' or 1=1 -- ' and password='123'

注意：在MySQL中 -- 表示注释。

那么，在Python开发中如何来避免SQL注入呢？

切记，SQL语句不要在使用python的字符串格式化，而是使用pymysql的execute方法。

import pymysql

# 输入用户名和密码
user = input("请输入用户名：")
pwd = input("请输入密码：")

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')

cursor = conn.cursor()

cursor.execute("select * from users where name=%s and password=%s", [user, pwd])
# 或
# cursor.execute("select * from users where name=%(n1)s and password=%(n2)s", {"n1": user, 'n2': pwd})

result = cursor.fetchone()
print(result)

cursor.close()
conn.close()

其他常用

条件

根据条件搜索结果。

select * from info where age > 30;
select * from info where id > 1;
select * from info where id = 1;
select * from info where id >= 1;
select * from info where id != 1;
select * from info where info.id > 10;
select * from info where id between 2 and 4;   -- id大于等于2、且小于等于4
select * from info where name = 'xx' and age = 19;
select * from info where name = 'xx' or age = 49;
select * from info where (name = 'xx' or email="pyyu@live.com")  and age=49;
select * from info where id in (1,4,6);
select * from info where id not in (1,4,6);
select * from info where id in (select id from depart);
# select * from info where id in (1,2,3);
# exists select * from depart where id=5，去查数据是否存在，如果存在，如果不存在。
select * from info where exists (select * from depart where id=5);
select * from info where not exists (select * from depart where id=5);
select * from (select * from info where id>2) as T where age > 10;1.

如：查找年龄大于20小于60的数据：

select * from info where age between 20 and 60;
select * from info where age>=20 and age<=60;
select * from (select * from info where age>=20) as T where age<=60;
-- 最后一种的as要加的

MySQL数据库（1）

数据库管理

内置客户端操作

Python代码操作

数据表管理

内置客户端操作

Python代码操作

数据行管理

内置客户端操作

Python代码操作

案例

关于SQL注入

其他常用

条件

热门文章

最新文章

相关课程

相关电子书

相关实验场景