数据库管理系统(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要加的