sql数据库 管理
1.设置和修改root密码
1.1 修改密码
1.2 忘记密码修改
-
修改配置文件,在[mysql]节点下添加
skip-grant-tables=1
-
关闭重启mysql,再次登录,无需密码
-
windows启动
net stop mysql57 net start mysql57
-
-
进入数据库执行修改密码命令
user mysql; update user set authentication_string=password('新密码'),password_last_changed=now() where user='root';
-
退出并再次修改配置文件,删除[mysql]节点下的
skip-grant-tables=1
-
再次关闭启动,使用新密码登录
2.数据库管理
数据库管理系统与文件管理类比
数据库管理系统 | 文件管理 |
---|---|
数据库 | 文件夹 |
数据表 | 文件夹下的excel文件 |
2.1内置客户端操作
查看当前所有的数据库
show databases;
创建数据库
create database db110 default charset utf8 collate utf8_general_ci; -- db110是创建数据库的名字
删除数据库
drop database db110;
进入数据库
use db110;
2.2 python代码数据库操作
安装第三方库
pip install pymysql
import pymysql
#连接mysql(底层使用socket)
conn=pymysql.connect(host='127.0.0.1',port=3306,user='root',password='wxy575254',charset='utf8')
cursor=conn.cursor()
#1.查看数据库
cursor.execute('show databases')
#获取所有结果
result=cursor.fetchall()
print(result)
#(('data',), ('db110',), ('db_example',), ('estoredb',), ('info',), ('information_schema',), ('meiduo',), ('meiduo01',), ('mydb',), ('mydb1',), ('mysql',), ('mysql_db',), ('performance_schema',), ('qiubai',), ('shop',), ('shop2',), ('spider',), ('sys',), ('testdb',))
#2.创建数据库
cursor.execute('create database db111 default charset=utf8 collate utf8_general_ci')
conn.commit()
#3.查看数据库
cursor.execute('show databases')
#获取所有结果
result=cursor.fetchall()
print(result)
#(('data',), ('db110',), ('db111',), ('db_example',), ('estoredb',), ('info',), ('information_schema',), ('meiduo',), ('meiduo01',), ('mydb',), ('mydb1',), ('mysql',), ('mysql_db',), ('performance_schema',), ('qiubai',), ('shop',), ('shop2',), ('spider',), ('sys',), ('testdb',))
#4.删除数据库
cursor.execute('drop database db111')
conn.commit()
#5.查看数据库
cursor.execute('show databases')
#获取所有结果
result=cursor.fetchall()
print(result)
#(('data',), ('db110',), ('db_example',), ('estoredb',), ('info',), ('information_schema',), ('meiduo',), ('meiduo01',), ('mydb',), ('mydb1',), ('mysql',), ('mysql_db',), ('performance_schema',), ('qiubai',), ('shop',), ('shop2',), ('spider',), ('sys',), ('testdb',))
#6.进入数据库
cursor.execute('use db110')
cursor.execute('show tables')
result=cursor.fetchall()
print(result)
#(('tb1',), ('tb2',), ('tb3',), ('tb4',), ('tb5',))
#关闭连接
cursor.close()
conn.close()
3.数据表管理
3.1创建表结构
设置字段类型
create table 表名(
列表 类型,
...
列表 类型
)default charset=utf8;
#主键,自增
create table tb4(
id int not null auto_increment primary key,-- 不允许为空,&主键&自增
name varchar(16) not null, --允许为空
email varchar(32) null, -- 可以为空(默认)
age int default 3 -- 插入数据,不给值,默认值为3
)default charset=utf8;
注意:一个表只能有一个自增列【自增列,一般都是主键】
3.2 删除表
drop table 表名;
3.3 清空表
delete table 表名;
3.4 修改表
-
添加列
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
3.5 常见列类型
-
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
-
bigint[(m)][unsigned][zerofill]
有符号,取值范围:-9223372036854775808 ~ 9223372036854775807 无符号,取值范围:0 ~ 18446744073709551615
-
decimal[(m[,d])][unsigned][zerofill]
准确的小数值,m是数字个数(负号不算),d是小数点后个数(小数点后面默认四舍五入),m最大值为65,d最大值为30 (8,2) -- 整数6位,小数2位,总共8位
-
float[(M,D)][unsigned][zerofill]
单精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数
-
double[(M,D)] [unsigned][zerofill]
双精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数
-
char(m)
定长字符串,m代表字符串的长度,最多可容纳255个字符 定长体现: 即使内容长度小于m,也会占用长度 例如:char(5),数据是yes,底层也会占用5个字符,如果超出m长度限制(默认mysql是严格模式,会报错) 配置文件设置:sql-mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' 保存并重启,模式变为非严格模式,此时超过长度则自动截断(不报错) 注意:默认底层存储是固定的长度(不够用空格补齐),但是查询数据时,会自动将空白去除,如果想要保留空白 在:sql-mode中加入PAD_CHAR_TO_FULL_LENGTH即可 查看模式sql-mode:执行命令:show variables like 'sql_mode'; 一般适用于:固定长度的内容
-
varchar(m)
变长字符串,m代表字符串的长度,最多容纳65535(2**16-1)个字节 变长体现: 内容小于m时,会按照实际的数据长度存储,如果超出m长度限制(报错) 配置文件设置:sql-mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' 保存并重启,模式变为非严格模式,此时超过长度则自动截断(不报错)
-
text
用于保存变长字符串,可以最多到65535(2*16-1)个字符 应用场景:例如:文章,新闻等
-
mediumtext
A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.
-
longtext
A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1)
-
datetime
(不做任何改变,原样输入和输出)YYYY-MM-DD HH:MM:SS (1000-01-01 00:00:00/9999-12-31 23:59:59)
-
timestamp
YYYY-MM-DD HH:MM:SS (1970-01-01 00:00:00/2037年)
timestamp,它把客户端插入的时间从当前时区转化为UTC(世界标准时间),进行存储,查询时,将其又转化为客户端当前时区进行返回
-
date
YYYY-MM-DD (1000-01-01/9999-12-31)
-
time
HH:MM:SS('-838:59:59'/'838:59:59')
3.6 python代码操作
import pymysql
#连接mysql
conn=pymysql.connect(host='127.0.0.1',port=3306,user='root',password='wxy575254',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,创建表
sql="""
create table tb1(
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')
result1=cursor.fetchall()
print(result1)
#(('tb1',),)
#关闭连接
cursor.close()
conn.close()
4.数据行
4.1.数据行的操作
新增数据
insert into 表名 (列名,列名) values(对应列的值,对应列的值)
-- 单行插入
insert into tb6(name,password) values('wxy','123');
-- 多行插入
insert into tb6(name,password) values('wxy','123'),('wxy1','456');
-- 确认列(可以不用写列名,默认按照顺序插入),插入
insert into tb6 values('wxy','123');
删除数据
-- 删除表中的所有数据
delete from 表名;
-- 删除数据+条件
delete from 表名 where 条件;
delete from tb7;
delete from tb7 where name='wxy';
delete from tb7 where name='wxy' and password='123';
delete from tb7 where id>7;
修改数据
-- 修改值
update 表名 set 列名=值;
-- 修改值+条件
update 表名 set 列名=值 where 条件;
update tb8 set name='wxy1';
update tb8 set name='wxy1' where id=1;
-- age是整型
update tb8 set age=age+1;
update tb8 set age=age+1 where id=2;
-- concat函数,可以拼接字符串
update tb8 set name=concat(name,'db');
update tb8 set name=concat(name,'db') where id=2;
查询数据
select * from 表名;
select 列名,列名 from 表名;
select 列名,列名 as 别名 from 表名;
select * from 表名 where 条件;
select * from tb9;
select id,name from tb9;
select id,name as n from tb9;
select id,name,111 from tb9;
select * from tb9 where id=1;
select * from tb9 where id>1;
select * from tb9 where id!=1;
select * from tb9 where name='wxy' and password='123';
4.2 python代码操作
import pymysql
conn=pymysql.connect(host='127.0.0.1',port=3306,user='root',password='wxy575254',charset='utf8',db='usersdb')
cursor=conn.cursor()
#1.新增数据
# sql="""
# insert into tb2(name,password)
# values('user1','1231'),('user2','1232'),('user3','1233'),('user4','1234'),('user5','1235')"""
# cursor.execute(sql)
# conn.commit()
#2.删除数据
# sql='delete from tb2 where id=1'
# cursor.execute(sql)
# conn.commit()
#3.修改数据,查不到,不修改
cursor.execute('update tb2 set name="user" where id=1')
conn.commit()
#4.查询数据
sql='select * from tb2 where id>3'
cursor.execute(sql)
result=cursor.fetchall()
print(result)
#((4, 'user4', '1234'), (5, 'user5', '1235'))
#关闭
cursor.close()
conn.close()
5.关于注入
开发用户认证系统,用户登录成功后才能正确的返回相应的用户结果
import pymysql
user=input('请输入用户名:')# 'or 1=1 --
pwd=input('请输入密码:')#123
conn=pymysql.connect(host='127.0.0.1',port=3306,user='root',password='wxy575254',charset='utf8' db='userdb')
cursor=conn.cursor()
#-- 是mysql中的注释,怎么样都成立
#基于字符串格式化,拼接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)
#查不到返回none
result=cursor.fetchone()
cursor.close()
conn.close()
如果用户在输入user时,输入了: ' or 1=1 --
,这样即使用户输入的密码不存在,也会可以通过验证。
为什么呢?
因为在SQL拼接时,拼接后的结果是:
-- name="",1=1恒成立,后面密码被注释
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',password='wxy575254',charset='utf8' db='userdb')
cursor=conn.cursor()
#方式一
cursor.execute("select * from users where name=%(n1)s and password=%(n2)s",{'n1':user,'n2':pwd})
#方式二
cursor.execute("select * from users where name=%s and password=%s",[user,pwd])
result=cursor.fetchone()
cursor.close()
conn.close()