1.数据库管理
如果将数据库管理系统与之前的文件管理做类比的话:
数据库 | 文件夹 |
---|---|
数据库管理系统 | 文件管理 |
数据表 | 文件夹下的excel文件 |
1.1内置客户端操作
查看当前所有的数据库: show databases;
#create database 数据库名 default charset 编码 collate 排序规则;
创建数据库 :create database xxx DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
删除数据库:drop database 数据库名;
进入数据(进入文件):use 数据库;
show tables;#进入数据库查看数据表
exit #退出mysql
1.2用python操作数据库
import pymysql
# 连接MySQL(socket)
conn = pymysql.connect(host='xxx', port=3306, user='xxx', passwd='xxx', charset="utf8")
cursor = conn.cursor()
# 1. 查看数据库
# 发送指令
cursor.execute("show databases")
# 获取指令的结果
result = cursor.fetchall()
print(result) # (('information_schema',), ('mysql',), ('performance_schema',), ('sys',))
# 2. 创建数据库(新增、删除、修改)
# 发送指令
cursor.execute("create database db3 default charset utf8 collate utf8_general_ci")
conn.commit()
# 3. 查看数据库
# 发送指令
cursor.execute("show databases")
# 获取指令的结果
result = cursor.fetchall()
print(result) # (('information_schema',), ('db3',), ('mysql',), ('performance_schema',), ('sys',))
# 4. 删除数据库
# 发送指令
cursor.execute("drop database db3")
conn.commit()
# 5.查看数据库
# 发送指令
cursor.execute("show databases")
# 获取指令的结果
result = cursor.fetchall()
print(result) # (('information_schema',), ('mysql',), ('performance_schema',), ('sys',))
# 6.进入数据库,查看表
# 发送指令
cursor.execute("use mysql")
cursor.execute("show tables")
result = cursor.fetchall()
print(result) # (('columns_priv',), ('db',), ('engine_cost',), ('event',), ('func',), ('general_log',),....
# 关闭连接
cursor.close()
conn.close()
2.数据表管理
2.1内置客户端操作
create table stu (
id int,
name varchar(16),
age int --最后一个字段不需要加逗号
)default charset=utf8;
create table tb2(
id int,
name varchar(16) not null, -- 不允许为空
pwd varchar(32) null, -- 允许为空(默认)
)default charset=utf8;
create table tb3(
id int,
name varchar(16) not null, -- 不允许为空
email varchar(32) null, -- 允许为空(默认)
age int default 3 -- 插入数据时,如果不给age列设置值,默认值:3
)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;
create table work(
id int not null auto_increment primary key,
name varchar(32) not null,
password varchar(64) not null,
gender char(1) not null,
email varchar(64),
amout decimal(10,2) default 0,
ctime datetime
)default charset=utf8;
注意:一个表中只能有一个自增列【自增列,一般都是主键】。
#删除表
drop table 表名
--清空表
delete from 表名
truncate from 表名
- 修改表
---添加列
alter table 表名 add 列名 类型;
alter table 表名 add 列名 default 默认值;
alter table 表名 add 列名 not null default 默认值;
alter table 表名 add 列名 default not null primary key auto_increment default 默认值;
--删除列
alter table 表名 drop column 列名;
--修改列类型
alter table 表名 modify column 列名 类型
---修改 列名
alter table 表名 原列名 rename 新列名;
--修改类型 + 名称
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(列名);
2.2常见列类型
int[(m)][unsigned][zerofill]
int 表示有符号,取值范围:-2147483648 ~ 2147483647
int unsigned 表示无符号,取值范围:0 ~ 4294967295
int(5)zerofill 仅用于显示,当不满足5位时,按照左边补0,例如:00002;满足时,正常显示。
- tinyint[(m)] [unsigned] [zerofill]
有符号,取值范围:-128 ~ 127.
无符号,取值范围:0 ~ 255
decimal[(m[,d])] [unsigned] [zerofill]
准确的小数值,m是数字总个数(负号不算),d是小数点后个数。 m最大值为65,d最大值为30。
例如:
create table L2(
id int not null primary key auto_increment,
salary decimal(8,2)
)default charset=utf8;
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
单精度浮点数,非准确小数值,m是数字总个数,d是小数点后个数。
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
双精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数。
-
char(m)
定长字符串,m代表字符串的长度,最多可容纳255个字符。
定长的体现:即使内容长度小于m,也会占用m长度。例如:char(5),数据是:yes,底层也会占用5个字符;如果超出m长度限制(默认MySQL是严格模式,所以会报错)。
如果在配置文件中加入如下配置,
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
保存并重启,此时MySQL则是非严格模式,此时超过长度则自动截断(不报错)。。
注意:默认底层存储是固定的长度(不够则用空格补齐),但是查询数据时,会自动将空白去除。 如果想要保留空白,在sql-mode中加入 PAD_CHAR_TO_FULL_LENGTH 即可。
查看模式sql-mode,执行命令:show variables like 'sql_mode';
一般适用于:固定长度的内容。
create table L3(
id int not null primary key auto_increment,
name varchar(5),
depart char(3)
)default charset=utf8;
insert into L3(name,depart) values("alexsb","sbalex");
- varchar(m)
变长字符串,m代表字符串的长度,最多可容纳65535个字节。
变长的体现:内容小于m时,会按照真实数据长度存储;如果超出m长度限制((默认MySQL是严格模式,所以会报错)。
如果在配置文件中加入如下配置,
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
保存并重启,此时MySQL则是非严格模式,此时超过长度则自动截断(不报错)。
例如:
create table L3(
id int not null primary key auto_increment,
name varchar(5),
depart char(3)
)default charset=utf8;
text
text数据类型用于保存变长的大字符串,可以组多到65535 (2**16 − 1)个字符。
一般情况下,长文本会用text类型。例如:文章、新闻等。
create table L4(
id int not null primary key auto_increment,
title varchar(128),
content text
)default charset=utf8;
- TIMESTAMP
YYYY-MM-DD HH:MM:SS(1970-01-01 00:00:00/2037年)
对于TIMESTAMP,它把客户端插入的时间从当前时区转化为UTC(世界标准时间)进行存储,查询时,将其又转化为客户端当前时区进行返回。
对于DATETIME,不做任何改变,原样输入和输出。
mysql> create table L5(
-> id int not null primary key auto_increment,
-> dt datetime,
-> tt timestamp
-> )default charset=utf8;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into L5(dt,tt) values("2025-11-11 11:11:44", "2025-11-11 11:11:44");
mysql> select * from L5;
+----+---------------------+---------------------+
| id | dt | tt |
+----+---------------------+---------------------+
| 1 | 2025-11-11 11:11:44 | 2025-11-11 11:11:44 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)
-- “CST”指的是MySQL所在主机的系统时间,是中国标准时间的缩写,China Standard Time UT+8:00
mysql> set time_zone='+0:00';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | +00:00 |
+------------------+--------+
2 rows in set (0.01 sec)
mysql> select * from L5;
+----+---------------------+---------------------+
| id | dt | tt |
+----+---------------------+---------------------+
| 1 | 2025-11-11 11:11:44 | 2025-11-11 03:11:44 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
2.3代码操作
import pymysql
# 连接MySQL
conn = pymysql.connect(host='127.0.0.1', port=3306, user='', passwd='', charset="utf8")
cursor = conn.cursor()
# 1. 创建数据库
"""
cursor.execute("create database db4 default charset utf8 collate utf8_general_ci")
conn.commit()
"""
# 2. 进入数据库、查看数据表
"""
cursor.execute("use db4")
cursor.execute("show tables")
result = cursor.fetchall()
print(result)
"""
# 3. 进入数据库创建表
cursor.execute("use db4")
sql = """
create table L4(
id int not null primary key auto_increment,
title varchar(128),
content text,
ctime datetime
)default charset=utf8;
"""
cursor.execute(sql)
conn.commit()
# 4. 查看数据库中的表
"""
cursor.execute("show tables")
result = cursor.fetchall()
print(result)
"""
# 关闭连接
cursor.close()
conn.close()
3.数据行管理
3.1内置客户端操作
- 新增数据
--新增数据
insert into 表名(列名,xx,xx,) values(对应列的值,xxx,xx);
insert into tb1(name,password) values('zs','123123');
insert into tb1(name,password) values('zs','123123'),('ls','123');
insert into tb1 values('zs','123123'),('ls','123'); -- 如果表中只有2列
- 删除数据
delete from 表名;
delete from 表名 where 条件;
delete from tb1;
delete from tb1 where name="zs";
delete from tb1 where name="zs" and password="123";
delete from tb1 where id>9;
- 修改数据
updata 表名 set 列名 =值;
updata 表名 set 列名=值 where 条件;
update tb1 set name="zs";
update tb1 set name="zs" 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 distint xx from xxx; --去掉重复的值
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";
3.2python代码操作
import pymysql
conn=pymysql.connect(host="127.0.0.1",user="root",port=3306,password="",charset="utf8", db="lianxi")
cursor=conn.cursor()
# 查看数据表
cursor.execute(" show tables")
res1=cursor.fetchall()
# 1. 新增 需(commit)
cursor.execute("insert into tb1(name,password,gender) values ('zj','123456','男'),('周武','123456','女')")
conn.commit()
# res=cursor.execute("insert into tb1(name,password,gender) values ('zj','123456','男'),('周武','123456','女')")
# conn.commit()
# print(res)#2
# 2.删除 需(commit)
cursor.execute("delete from tb1 where name='zj' ")
conn.commit()
#3.修改 需(commit)
cursor.execute("update tb1 set name='张杰'where name='zs'")
conn.commit()
#4. 查询
cursor.execute(" select * from tb1")
res2=cursor.fetchall()
print(res2,type(res2))
# 关闭连接
cursor.close()
conn.close()
3.1案例
其实在真正做项目开发时,流程如下:
-
第一步:根据项目的功能来设计相应的 数据库 & 表结构(不会经常变动,在项目设计之初就确定好了)。
-
第二步:操作表结构中的数据,已达到实现业务逻辑的目的。
实现一个 用户管理系统。
create database xxx default charset utf8 collate utf8_genral_ci;
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("alex","sb")'
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 = 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获取结果
# 关闭数据库连接
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()
4.关于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='zs' 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中
--
表示注释。
切记,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()