11 python 操作mysql数据库
sql语句
文件夹操作(数据库)：
创建:
create database db_name;
create database db_name default charset utf8;
删除:
drop database db_name;
进入文件夹：
use db_name
文件操作(数据表)：
创建:
单表：
create table user_info(
id int not null auto_increment primary key,
name char(20),
age int,
gender char(1)
)engine = innodb default charset=utf8;
insert into user_info(name,age,gender) values('alex',19,'男');
1. 列名
2. 数据类型
3. 是否可以问空(null,not null)
4. 默认值(default 1)
5. 自增(auto_increment) 一个表只能有一个自增列
6. 主键(primary key)
约束：数据不能为空、数据不能重复。
索引：加速查找(B树结构)
7. 外键:
对某列数据的内容进行约束。
约束:只能是某个表中某列已经存在的数据。
constraint 约束名称 foregin key(deparment_id) references dep_info(id)
含外键的关系成为1对多。
多表
多表:
一对多：
create table user_info(
id int not null auto_increment primary key,
name char(20),
age int,
gender char(1),
deparment_id int,
constraint 约束名称 foregin key(deparment_id) references dep_info(id)
)engine = innodb default charset=utf8;
create table dep_info(
id int not null auto_increment primary key,
title char(32)
)engine=innodb default charset=utf8;
多对多：(关系表)
create table boy(
id int not null auto_increment primary key,
name char(32)
)engine = innodb default charset=utf8;
create table girl(
id int not null auto_increment primary key,
name char(32)
)engine = innodb default charset=utf8;
create table b2g(
id int not null auto_increment primary key,
b_id int,
g_id int,
constraint 约束名称1 foregin key(b_id) references boy(id),
constraint 约束名称2 foregin key(g_id) references girl(id)
)engine = innodb default charset = utf8;
应用场景：
1、根据具体业务
2、HTML的场景
创建的时候只需要输入数据的情况下：单表
创建的时候必须要我们选择一个其他数据的场景下：一对多
创建的时候针对某些字段可以进行多选的情况下：多对多
删除:
drop table table_name;
文件内容操作
文件内容操作(数据行)：
插入：
insert into table_name(field) values(value),(value2) --> 两个(value)表示插如多行数据
insert into table_name(cname) select field from table_name --> 把select查到的结果,当作数据来赋值给value
删除:
清空表：
delete from table_name --> 自增列会继续之前的ID
truncate table table_name --> 物理删除，速度快，重新计算ID
删除某一条：
delete from table_name where filed = values and/or ... --> 只删除符合条件的数据
delete from table_name where filed in (1,2,3,4)
delete from table_name where id between 5 and 10
修改:
update table_name set field = 'value' --> 更新所有数据的field字段的值，加 where 只修改匹配到的行
update table_name set id = 8 , name = 'daxin' where age = 18;
查询：
select * from table_name where id > 2
select field as '别名' from table_name --> 加别名
select * from table_name where id in (1,2)
select * from table_name where cid in (select tid from teacher)
条件：
select * from table_name order by field asc/desc(正序/倒序)
select * from table_name order by field asc limit 1 取第一个值
select * from table_name limit 1,2(起始位置,找几个)
select * from table_name where field like '%key%' --> 查找field字段包含key的数据
% 表示任意个任意字符， _表示任意一个字符
分组:
select * from table_name group by field --> 分组显示,会去重,需要使用聚合函数来统计重复的次数
select field,count(id) from table_name group by field --> 对id字段进行聚合(其他的还有min(),max(),sum(),avg()等)
例子：
1、获取每个班级多少人
SELECT class.caption,count(sid) from class
LEFT join student on student.class_id = class.cid
group by class.caption
2、获取每个班级有多少人并且选出认识大于2的班级，
注意：如果针对 group by 的结果进行筛选,那么需要使用 having 不能在使用 where 了.
SELECT class.caption,count(sid) as number from class
LEFT join student on student.class_id = class.cid
group by class.caption
HAVING number >= 2
3、每个课程的不及格的人数。
select course.cname,count(sid) from score
left join course on score.corse_id = course.cid
where number < 60
group by course.cname
连表
连表：
select student.sid,student.sname,class.caption from student LEFT JOIN class on student.class_id = class.cid ;
把class表中的字段放在student表的左边,并且进行 student.class_id = class.cid 匹配后显示，数据量以from指定的表为基准
left join:
以 from 指定的表为基准,对数据进行显示
right join: -->不常用
以 join 后面的表为基准进行显示。
inner join:(join 使用的就是)
只保留连个表中都有数据的条目
例子：
1、id=1的老师任教的课程名称
2、老师姓名瞎猫任教的课程命令
3、已选课程id=1,所有学生的姓名
4、已选体育课，所有学生的姓名
5、已选波多任教任意课程,所有学生姓名
union:
把两个SQL的结果进行组合(上下合并)
select * from student
union / union all
select * from teacher;
注意上下两个表中的列数要统一
注意：
1、如果所有数据都一致，那么union会对结果进行去重
2、union all ,会保存所有的
基本数据类型
MySQL的数据类型大致分为：数值、时间 和 字符串。
数字：
整数
tinyint 小整数,数据类型用于保存一些范围的整数数值范围。
smallint
int
bigint
小数
float 浮点型(长度越长越不精准)
double 浮点型(双精度,精度比float稍高) 范围比float更大
decimal 精准(内部使用字符串进行存储的) -> 适合对精度有要求的
字符串
char(19)[字符长度] 定长字符串 --> 占用空间大，但是效率高
varchar(19)[字符长度] 不定长字符串 --> 占用空间是可变的，但是效率低
注意：最大可以存放255个字符
text() 65535个字符
mediumtext() 16777215个字符
longtext() 4294967254个字符
二进制：
TinyBlob
Blob
MediumBlob
LongBlob
存文件:虽然可以用二进制进行存储,但是一般是存储文件在服务器上的路径(URL)
时间：
date YYYY-MM-DD
time HH:MM:SS
year YYYY
DATETIME YYYY-MM-DD HH:MM:SS -->常用
TIMESTAMP 时间戳格式
Python操作MySQL
python 3.x 中使用 pymysql
python 2.x 中使用 mysqldb
方法和使用规则是一模一样的。
用户登录改造
sql拼接,引出SQL注入问题
提交 commit
cursor.lastrowid 获取上一个增加的数据的自增ID
指定查询的数据类型
cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)
#!/usr/bin/python # -*- coding:utf-8 -*- import pymysql # 获取数据 conn = pymysql.Connect(host='192.168.12.89',port=3306,user='root',password="123",database="s17day11db",charset='utf8') cursor = conn.cursor() # 受影响的行数 v = cursor.execute('select * from student') result = cursor.fetchall() # result = cursor.fetchone() # result = cursor.fetchmany(2) print(result) cursor.close() conn.close()
#!/usr/bin/python # -*- coding:utf-8 -*- import pymysql # 获取数据 # conn = pymysql.Connect(host='192.168.12.89',port=3306,user='root',password="123",database="s17day11db",charset='utf8') # cursor = conn.cursor() # 受影响的行数 # v = cursor.execute('insert into userinfo(username,password) values(%s,%s)',['eric','99999']) # conn.commit() # v = cursor.execute('delete from userinfo where username=%s',['eric']) # conn.commit() # v = cursor.execute('update userinfo set password=%s where username=%s',['999999','alex']) # conn.commit() # cursor.close() # conn.close()
# -*- coding:utf-8 -*- import pymysql # 获取数据 conn = pymysql.Connect(host='192.168.12.89',port=3306,user='root',password="123",database="s17day11db",charset='utf8') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 受影响的行数 v = cursor.execute('select * from student') result = cursor.fetchall() # result = cursor.fetchone() # result = cursor.fetchmany(2) print(result) # cursor.scroll(-1,mode='relative') # cursor.scroll(10,mode='absolute') cursor.close() conn.close() from concurrent.futures import ThreadPoolExecutor pool = ThreadPoolExecutor(10) def cmd(host_info,commond): pass commond = "ls" for i in [{'ip':'xx', 'port':'xx'},]: pool.submit(cmd,i,commond)
#!/usr/bin/python # -*- coding:utf-8 -*- import pymysql # 获取数据 conn = pymysql.Connect(host='192.168.12.89',port=3306,user='root',password="123",database="s17day11db",charset='utf8') cursor = conn.cursor() cursor.execute('insert into class(caption) values(%s)',['新班级']) conn.commit() new_class_id = cursor.lastrowid # 获取新增数据自增ID cursor.execute('insert into student(sname,gender,class_id) values(%s,%s,%s)',['李杰','男',new_class_id]) conn.commit() cursor.close() conn.close()
#!/usr/bin/python # -*- coding:utf-8 -*- import pymysql user = input('请输入用户名:') pwd = input('请输入密码:') # 获取数据 conn = pymysql.Connect(host='192.168.12.89',port=3306,user='root',password="123",database="s17day11db",charset='utf8') cursor = conn.cursor() v = cursor.execute('select * from userinfo where username=%s and password=%s',[user,pwd]) result = cursor.fetchone() cursor.close() conn.close() print(result)
#!/usr/bin/python # -*- coding:utf-8 -*- import pymysql user = input('请输入用户名:') pwd = input('请输入密码:') # 获取数据 conn = pymysql.Connect(host='192.168.12.89',port=3306,user='root',password="123",database="s17day11db",charset='utf8') cursor = conn.cursor() sql = 'select * from userinfo where username="%s" and password="%s" ' %(user,pwd,) # user = alex" -- # pwd= asdf 'select * from userinfo where username="alex" -- " and password="sdfsdf"' # user = asdfasdf" or 1=1 -- # pwd= asdf 'select * from userinfo where username="asdfasdf" or 1=1 -- " and password="asdfasdf"' v = cursor.execute(sql) result = cursor.fetchone() cursor.close() conn.close()
