Python自动化开发课堂笔记【Day11】 - Python进阶（MySQL）

MySQL语句

1. 文件夹操作（数据库）：

create database db1; #创建数据库

create database db1 default charset utf8; #创建数据库，字符编码为UTF-8

drop database db1; #删除数据库

use db1; #选择使用数据库

#数据库重命名：命令不存在，可以新建数据库，再将原文件导入新数据库

2.文件操作（数据表）：

#创建单表：--->应用场景：

create table userinfo(
    id int not null auto_increment primary key,
    name char(20),
    age int default 18,
    gender char(10)
)engine=innodb default charset=utf8; #engine=innodb指定数据库引擎支持回滚操作，charset=utf8指定默认字符集

insert into userinfo(name,age,gender) values ('Albert',30,'男'); #添加一条记录

drop table userinfo; # 删除数据表

# 列名(id,name,age,gender)
# 数据类型(int,char,varchar)
# 是否可以为空 (null or not null)
# 默认值(default)
# 自增：一个数据表只能有一个自增列（auto_increment）
# 主键：
　　　 约束：不能为空，不能修复
　　　 索引：加速查找

#创建多表（一对多）--->应用场景：

create table userinfo_1(
    id int not null auto_increment primary key,
    name char(20),
    age int default 18,
    gender char(10),
    depart_id int,
    constraint ys foreign key (depart_id) references department(id) #userinfo_1表中的depart_id受到department表中id列的约束
)engine=innodb default charset=utf8;

create table department(
    id int not null auto_increment primary key,
    title char(32)
)engine=innodb default charset=utf8;

insert into department(title) value ('IT');
insert into department(title) value ('HR');

insert into userinfo_1(name,age,gender,depart_id) values ('Albert',30,'男',10000); #添加的depart_id不在department表id范围之内，报错
insert into userinfo_1(name,age,gender,depart_id) values ('Baker',29,'男',2); #添加成功

# 外键
　　　约束：只能是某个表中某列已经存在的数据（一对多的关系，department表中的id对应了userinfo_1表中的多个user的id）

#创建多表（多对多）--->应用场景：

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 b2g(
    id int not null auto_increment primary key,
    bid int,
    gid int,
    constraint ys1 foreign key (bid) references boy(id), # 约束1
    constraint ys2 foreign key (gid) references girl(id) # 约束2
)engine=innodb default charset=utf8;

 3. 数据库中使用的数据类型

bit[(M)]
　　二进制位（101001），m表示二进制位的长度（1-64），默认m＝1
tinyint[(m)] [unsigned] [zerofill]
　　小整数，数据类型用于保存一些范围的整数数值范围：
　　有符号：
　　　　-128 ～ 127.
　　无符号：
　　　　0 ～ 255
　　特别的： MySQL中无布尔值，使用tinyint(1)构造。
int[(m)][unsigned][zerofill]
　　整数，数据类型用于保存一些范围的整数数值范围：
　　有符号：
　　　　-2147483648 ～ 2147483647
　　无符号：
　　　　0 ～ 4294967295
　　特别的：整数类型中的m仅用于显示，对存储范围无限制。例如： int(5),当插入数据2时，select 时数据显示为： 00002
bigint[(m)][unsigned][zerofill]
　　大整数，数据类型用于保存一些范围的整数数值范围：
　　有符号：
　　　　-9223372036854775808 ～ 9223372036854775807
　　无符号：
　　　　0  ～  18446744073709551615
decimal[(m[,d])] [unsigned] [zerofill]
　　准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。
　　特别的：对于精确数值计算时需要用此类型，decaimal能够存储精确值的原因在于其内部按照字符串存储。
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
　　单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
　　　　无符号：
　　　　　　-3.402823466E+38 to -1.175494351E-38,
　　　　　　0
　　　　　　1.175494351E-38 to 3.402823466E+38
　　　　有符号：
　　　　　　0
　　　　　　1.175494351E-38 to 3.402823466E+38
　　　　**** 数值越大，越不准确 ****
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
　　双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
　　　　无符号：
　　　　　　-1.7976931348623157E+308 to -2.2250738585072014E-308
　　　　　　0
　　　　　　2.2250738585072014E-308 to 1.7976931348623157E+308
　　　　有符号：
　　　　　　0
　　　　　　2.2250738585072014E-308 to 1.7976931348623157E+308
　　　　**** 数值越大，越不准确 ****
char(m)
　　　　char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度。
　　　　PS: 即使数据小于m长度，也会占用m长度
varchar(m)
　　　　varchars数据类型用于变长的字符串，可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。
　　　　注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡
text
　　　　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) characters.
enum
　　　　枚举类型，
　　　　An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
　　　　示例：
　　　　CREATE TABLE shirts (
　　　　　　name VARCHAR(40),
　　　　　　size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
　　　　　　);
　　　　INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
set
　　　　集合类型
　　　　A SET column can have a maximum of 64 distinct members.
　　　　示例：
　　　　　　CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
　　　　　　INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
DATE
　　　　YYYY-MM-DD（1000-01-01/9999-12-31）
TIME
　　　　HH:MM:SS（'-838:59:59'/'838:59:59'）
YEAR
　　　　YYYY（1901/2155）
DATETIME ***经常使用***
　　　　YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）
TIMESTAMP
　　　　YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）

 4. MySQL语句练习

#插入操作

insert into class (caption) values ('三年二班')
insert into class (caption) values ('三年三班'),('三年四班')

insert into student(sname,gender,class_id) values ('Albert','男',1),('Baker','男',1),('Charlie','男',2),('Dog','男',2),('Edge','男',3);

insert into teacher(tname) values('瞎驴'),('瞎狗'),('瞎猫');

insert into course (cname,teacher_id) values('数学',1),('语文' ,1),('英语',2),('政治',2),('经济',3);

insert into socre (student_id,course_id,number) values (1,1,60),(2,1,80),(1,2,90);

insert into class (caption) select tname from teacher;

#删除操作

create table department(
    id int not null auto_increment primary key,
    title char(32)
)engine=innodb default charset=utf8;

insert into department(title) values ('IT'),('HR'),('SALES');

delete from department; #清空列表，但是自增id不归零

insert into department(title) values ('IT'),('HR'),('SALES');

truncate table department; #清空列表，同时自增id归零

insert into department(title) values ('IT'),('HR'),('SALES');

delete from department where id = 2;

delete from department where id = 2 and title = 'HR';

delete from department where id > 2;

delete from department where id in (1,2,3,4);

delete from department where id between 5 and 10

#修改操作

update department set title='SRE';

update department set titel='SRE' where id = 2;

update department set titel='SRE',id=9 where id = 2;

#普通查询操作

select * from class;

select cid,caption as 'alias' from class;

select * from class where cid in (1,2);

select * from class where cid in (select tid from teacher);


select * from socre order by number asc;#按照分数列升序排列

select * from socre order by number desc;#按照分数列降序排列

select * from socre order by number desc limit 1;#限制查询结果输出条目

select * from socre order by number asc limit 0,1;#利用limit模拟分页查找
select * from socre order by number asc limit 1,1;
select * from socre order by number asc limit 2,1;

select * from teacher where tname like '瞎%'#模糊匹配，%代指任意字符的组合
select * from teacher where tname like '瞎_'#精确匹配，_代指任意一个字符

#链表操作（left join）

select * from student left join class on student.class_id = class.cid;

select student.sid,student.sname,class.caption from student left join class on student.class_id = class.cid;

select student.sid,student.sname,class.caption from student inner join class on student.class_id = class.cid;

select socre.sid,student.sname,course.cname,socre.number,class.caption,teacher.tname from socre
left join student on socre.student_id = student.sid
left join course on socre.course_id = course.cid
left join class on student.class_id = class.cid
left join teacher on course.teacher_id = teacher.tid;

select teacher.tid,teacher.tname,course.cname from teacher
left join course on teacher.tid = course.teacher_id where tid = 1 or tid = 2;

select teacher.tid,teacher.tname,course.cname from teacher
left join course on teacher.tid = course.teacher_id where tname = '瞎猫';

select student.sname from socre
left join student on socre.student_id = student.sid where course_id = 1;

select student.sname from socre
left join student on socre.student_id = student.sid
left join course on course.cid = socre.course_id where course.cname='数学'

select student.sname from socre
left join student on student.sid = socre.student_id
left join course on course.cid = socre.course_id
left join teacher on teacher.tid = course.teacher_id where tname = '瞎驴'；

select student.sname from socre
left join student on socre.student_id = student.sid where course_id in (
    select cid from course left join teacher on course.teacher_id = teacher.tid where teacher.tname = '瞎驴'
);

#分组操作（group by...having...）

select class_id,count(sid),max(sid),min(sid) from student GROUP BY class_id;

select class_id as '班级',count(sid) as '人数' from student GROUP BY class_id having count(sid) >= 2;

select course_id,count(sid) from socre where number < 90 GROUP BY course_id having count(sid) > 

#union操作

select tname from teacher

UNION

select sname from student;

Python操作MySQL

# 连接mysql数据库取出数据表中内容

import pymysql

conn = pymysql.Connect(host='192.168.32.130',port=3306,user='jiaxl',password='intel,123',database='db1',charset='utf8')

cursor = conn.cursor()
res = cursor.execute('select * from student')
# print(res) # 显示受影响的行数
# res_info = cursor.fetchall() #取出全部的行数
# print(res_info) # 打印数据库中的数据
# res_single = cursor.fetchone() #取出一个数据，带指针，会依次取出数据
# print(res_single)
res_many = cursor.fetchmany(2) #括号中自定义想取出的几行数据
print(res_many)

conn.close()

P.S.如果出现此种错误：pymysql.err.OperationalError: (1045, "Access denied for user 'jjj'@'192.168.32.1' (using password: YES)")

解决办法：
　　　　mysql> GRANT ALL PRIVILEGES ON *.* TO 'jjj'@'192.168.32.1' IDENTIFIED BY 'pwd' WITH GRANT OPTION; 
　　　　mysql> FLUSH PRIVILEGES;

模拟数据库登录和防sql注入

import pymysql

username = input('username:')
password = input('password:')

conn = pymysql.Connect(host='192.168.32.130',port=3306,user='jiaxl',password='intel,123',database='db1',charset='utf8')
cursor = conn.cursor()
sql = 'select * from userinfo where username="%s" and password="%s"' % (username,password)
print(sql)

res = cursor.execute(sql)
res_info = cursor.fetchone()
print(res_info)

cursor.close()
conn.close()

# 以上代码有sql注入的风险

# username:aaa" --
# password:asdf
# sql = 'select * from userinfo where username="aaa" -- " and password="%s"'

# username:asdf" or 1=1 --
# password:asdf
# 'select * from userinfo where username="asdf" or 1=1 -- " and password="%s"'

# 解决方案：

import pymysql

username = input('username:')
password = input('password:')

conn = pymysql.Connect(host='192.168.32.130',port=3306,user='jiaxl',password='intel,123',database='db1',charset='utf8')
cursor = conn.cursor()
# sql = 'select * from userinfo where username="%s" and password="%s"' % (username,password)
# print(sql)

res = cursor.execute('select * from userinfo where username="%s" and password="%s"' , [username,password])
res_info = cursor.fetchone()
print(res_info)

cursor.close()
conn.close()

# 操作数据表数据并提交

import pymysql

conn = pymysql.Connect(host='192.168.32.130',port=3306,user='jiaxl',password='intel,123',database='db1',charset='utf8')
cursor = conn.cursor()

# res = cursor.execute('insert into userinfo(username,password) values (%s,%s)' , ['ddd','444'])
# res = cursor.execute('delete from userinfo where username = %s' , ['ddd'])
# res = cursor.execute('update userinfo set passwrod=%s where userinfo=%s' , ['666','aaa'])
conn.commit() #提交，此步操作必须执行，否则数据表不会改变。

cursor.close()
conn.close()

# 获取新增自增ID

import pymysql

conn = pymysql.Connect(host='192.168.32.130',port=3306,user='jiaxl',password='intel,123',database='db1',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)',['Lucky','男',new_class_id])
conn.commit()

cursor.close()
conn.close()

# 获取数据类型

import pymysql

conn = pymysql.Connect(host='192.168.32.130',port=3306,user='jiaxl',password='intel,123',database='db1',charset='utf8')
cursor = conn.cursor(pymysql.cursors.DictCursor) #获取的信息为字典格式

res = cursor.execute('select * from student')
res_info = cursor.fetchall()
print(res_info)

cursor.close()
conn.close()