python 自动化 数据库比对 python和数据库比对

目录

• 一、mysql的基本知识
• 二、mysql的安装
• 三、启动和连接
• 四、mysql的基本SQL命令

• 1、库的操作
• 2、表的操作
• 3、字段的操作
• 4、记录的操作

• 五、mysql的数据结构
• 六、mysql的运算符

• 1、比较运算符
• 2、匹配式查找

• 七、mysql的高级SQL

• 1、order by(排序)
• 2、where（操作定位）
• 3、limit （限定行数）
• 4、聚合函数（对字段进行计算查询）
• 5、group by (分组聚合)
• 6、having (过滤)
• 5、distinct

• 七、mysql的约束与索引

• 1、约束
• 1、索引

• 八、mysql的导入导出、复制、备份

• 1、导入
• 2、导出
• 3、复制
• 4、备份

• 九、mysql的高级查询SQL

• 1、嵌套查询
• 2、链接查询
• 3、多表查询

• 十、mysql的事务
• 十一、mysql的储存引擎
• 十二、mysql的调优
• 十三、python操作mysql

一、mysql的基本知识

1、定义
	mysql是一个中小型且开源的数据库，在关系型数据库中仅次于oracle，
	由于mysql是开源数据库，所以是绝大部分公司的选择。
	
2、特点
	1) 关系型数据库
		以行和列的形式储存
		表中的行称为记录
		表中的列称为字段
		表与表之间的逻辑关联称为关系
	2) 跨平台
	3) 支持多种编程语言

3、注意
	1)  mysql的数据是以慰藉的形式存储在数据库目录/var/lib/mysql
	2)	关系型数据库的核心内容是 关系 及 二维表

4、修改默认编码类型
	1) 获取root权限 sudo -i
	2) 切换到配置文件路径 cd /etc/mysql/mysql.conf.d
	3) 备份 cp mysqld.cnd.cnf.bak
	4) 用vi编辑mysqld.cnf，修改一下内容,并保存退出
	[mysql]
	character_set_server = utf-8 
	5)重启服务 /etc/init.d/mysql restart

5、名词概念
	DB       数据库
	DBMS 数据库管理系统
	DBS    数据库系统

6、客户端把数据储存到数据库的服务器的过程
	1)连接数据库
	2)选择库
	3)创建/修改表
	4)断开链接

二、mysql的安装

1、ubuntu的安装（通过linux的apt源直接在终端安装即可）
安装服务器  sudo apt-get install mysql-server
安装客户端  sudo apt-get install mysql-client
2、window安装（下载压缩包，解压安装配置）
压缩包下载地址：[链接点击这里](https://pan.baidu.com/s/1bptkWIXu0ZD6aJNWw1ImKw)
提取码：zn5z 

三、启动和连接

1、服务端启动
sudo /etc/init.d/mysql start|stop|restart
start 启动服务
stop 停止服务
restart 重启服务

2、客户端连接
mysql -h主机地址 -u用户名 -p 密码
示例：mysql  -h127.0.0.1 -uroot -p123

四、mysql的基本SQL命令

1、库的操作

1.创建
create database 库名 [default charset=utf8]；
2.删
drop database 库名；
3.查
show databases;  #查看所有数据库
show create database name; #查看数据库创建语句
select database(); #查看当前所在库

2、表的操作

1. 创建表

create table table_name(
字段1 数据类型 [约束],
字段2 数据类型 [约束],
字段3 数据类型 [约束],
字段4 数据类型 [约束],
) [default charset=utf8];

2.查
show tables; #查看所有表
show create table table_name; #查看创建表语句
desc table_name; #查看表结构
3.删
drop table table_name;
4.改
alter table 表名 rename 新表名

3、字段的操作

1.增
alter table 表名 add 字段名 数据类型 first|after 字段名；
示例：alter table user sex emun after ago；#在ago后面添加一列sex；
2.删
alter table table_name drop filed_name;
3.改
alter table table_name modify filed_name 新数据类型； #修改数据类型
alter table table_name change old_name new_name 数据类型; #修改字段名

4、记录的操作

1.增（插入记录）
insert info 表名 values(值1)(值2)(值n);
insert info 表名(字段名列表) values(值1)(值2)(值n);
2.删
delete from 表名;
3.改
upadate 表名 set 字段名1=值1 字段名2=值2....;
示例：upadate user set sex=1 old=19；#将user表中的性别改成1，年龄改成19
4.查
select 字段1，字段2... from 表名

五、mysql的数据结构

1.整型
	1）int 大整型（4个字节）  取值范围：0~2**32-1（42亿多）
	2）tinyint 微小整型（1个组合） 取值范围：-128~127（0~255）
	3）smallint 小整形（2个字节） 取值范围：0~65535
	4）bigint  极大整型(8个字节)   取值范围：0~2**64-1
2.浮点型
	1) float (4个字节，最多显示7位数）取值范围：-999.99~999.99
		用法：字段名 float（m,n） m表示总位数 n表示小数位数
		注意：自动补全小数位数，小数位位数多的 时候四舍五入
	2) double（8个字节，最多显示15位有效位）用法：字段名 double（m,n）
	3) decimal(M+2个字节，最多显示28个有效位） 用法decimal（28,5）
3.字符型
	1）char 取值范围 ：1~255 ，不给宽度时默认为1. 用法：char（n） 
	2）varchar 取值范围：1~65535 ，没有固定宽度。 用法：varchar（n）
	比较：char 浪费储存空间，性能高。
		 varchar 介绍储存空间，性能低
4.枚举类型
	定义：字段值只能在列举的范围内旋转
	1）enum（...） 单选（最多有65536个不同值）
	2）set（...）  多选（最多哟64个不通知） 用法：字段名 set（值1，值2...）
5）日期时间类型
	1）year：年 YYYY
	2）data：日期 YYYYMMDD		
	3）time：时间HHMMSS		
	4）datatime：YYYYMMDDHHMMSS]
	5）timestamp：YYYYMMDDHHMMSS]   #缺失时用当前时间填充
	示例：user datatime 20200110221133

六、mysql的运算符

1、比较运算符

1) 数值比较运算符：=、!=、>=、<、<=
2) 字符比较运算符：=、!=
3) 逻辑比较运算符：and（两个或两个以上同时满足）、or（多个条件满足其中一个）
4) 范围比较运算符：between 值1 and 值2  #在值1和值2之间
				 in （值1，值2，值3....） #在列表中的值
				 not in	（值1，值2，值3....）#不在列表中的值
	示例：select * from user where old between 10 and 20;
		  查看old值在10到20之间的信息
5) 匹配空值：is null，匹配非空 ：is not null
	示例：select * from user where old is not null;
		 #查找用户表中，年龄不为空的信息

2、匹配式查找

1) 模糊匹配（like）
	1、用法：where 字段 like 表达式
	2、表达式：
		_：匹配单个字符
		%：匹配0到多个字符
	示例：select name from user where name like "赵%"
		 #查看名字为赵开头的信息

2) 正则匹配查询 （regexp）
	1、用法：where 字段 regexp “表达式”
	2、表达式符号
		^:以...开头
		$:以...结尾
		.:匹配任意一个字符
		*:匹前面字符出现0个或者多个
		[]:包含...内容
			[0-9]:匹配带数字的
			[a-z]:匹配带小写字母的
			[A-Z]:匹配带大写字母的
	示例：select * name from user regexp "^赵.*"
		#查看名字为赵开头的信息

七、mysql的高级SQL

mysql往往需要多条件帅选查询，以及在查询是计算，通常可以使用一下模板，先展示结构，后面一个一个功能讲解。
一般语句顺序是where>group by>聚合函数>having>order by> limit.

#sql高阶查询
select 字段,聚合函数 from 表
where ...
group by ...
having ...
order by ...
limit ...

1、order by(排序)

1) 作用：给查询结果进行排序
2) 语法格式：order by 字段 排序方式;
3) 排序方式：
	ASC(默认):升序
	DESC:降序
4) 示例:seletc * from user order by old DESC;(查询结果按年龄降序)

2、where（操作定位）

1) 作用：通常配合运算符达到对查询定位的作用
2) 语法格式：where 定位条件
3) 示例：select * from user where old<10; #查找年龄小于十岁的信息

3、limit （限定行数）

1) 作用：限制显示查询记录的个数
2) 用法：
	limit n --> 显示n条记录
	limit m,n --> 从第m+1条开始，显示n条记录
	通常放在sql语句最右边执行

4、聚合函数（对字段进行计算查询）

1）分类：
	avg(字段名):求字段的平均值
	sum(字段名):求和
	max(字段名):求最大值
	min(字段名):求最小值
	count(字段名):统计该字段的记录个数（缺失值不算在内）
2) 语法：select avg(字段名),sum(字段名) ...

5、group by (分组聚合)

1) 作用：给查询的结果进行分组
2) 语法：group by 字段名；
3) 示例：select sex,count(sex) from user group by sex;#将查询的结果按性别分组，并计算每组的个数
4) 注意：按什么分组，前面就只能查询什么字段。其他字段会出现报错。只能用聚合函数统计。

6、having (过滤)

1) 作用: 通常与group by连用，过滤group by返回的内容。弥补where只能对实际字段操作的缺陷。
2) 语法: having 字段条件

5、distinct

1) 作用: 不显示字段的重复值
2) 注意：
	distinct 处理的是distinct和from之间的所有字段，所有字段值必须完全相同才可以去重
	distinct不能对任何字段做聚合处理
3) 示例：select count(distinct name) from sanguo where country="蜀国";  #查看蜀国有多少个英雄。

七、mysql的约束与索引

1、约束

1) 作用：为了保证数据的完整性、一致性、有限性，可以限制无效的数据插入大数据表中
2) 分类
	1.默认约束（default）
		a.作用: 在插入记录时，如果不给改字段赋值，则使用默认值
		b.格式: 字段名 数据类型 default 默认值
	2.非空约束
		a.作用: 不允许该字段的值 有空值null记录
		b.格式: 字段名 数据类型 not null

1、索引

1) 定义: 对数据库中表的一列或者多列的值进行排序的一种结构(mysql中索引用Btree方式)
2) 优缺点: 加快了检索速度，但是降低了数据的维护速度。占用物理储存空间
3）分类
	1.普通索引
		a.规则
			一个表中可以有多个index字段
			字段的值可以有重复，也可以为空
			经常把做查询条件的字段设置为index字段
			index字段的key标志为：MUL
		b.创建索引
			创建表时创建，在字段后面加上index（字段）
			已有表添加：create index 索引名 on 表名(字段名)
		c.查看索引
			desc 表名；-->可以标志为MUL的字段
			show index from 表名;
		d.删除索引
			drop index 索引名 on 表名;
			
	2.唯一索引
		a.规则
			一个表中可以有多个unique字段
			字段的值不允许有重复，但是可以为空
			index字段的key标志为：UNI
		b.创建索引
			创建表时创建，在字段后面加上unique（字段）
			已有表添加：create unique index 索引名 on 表名(字段名)
		c.查看索引
			desc 表名；-->可以标志为MUL的字段
			show index from 表名;
		d.删除索引
			drop index 索引名 on 表名;
		
	3.主键索引
		a.规则
			一个表中只能有一个主键索引
			字段的值不允许有重复，也不能为空
			index字段的key标志为：PRI
			把表中能够唯一标识一条记录的字段设置为主键，通常把表中记录编号的字段设置为主键
		b.创建索引
			创建表时创建，在字段后面加上 primary key auto_increment
			已有表添加：alter table 表名 add primary key(字段名);
		c.查看索引
			desc 表名；-->可以标志为MUL的字段
			show index from 表名;
		d.删除索引
			alter table name modify id int primary key; #删除自增长属性
			alter table 表名 drop primary key;删除主键

八、mysql的导入导出、复制、备份

1、导入

1）作用：将文件系统的内容导入到数据库中（例如csv，txt）
2）语法
	load data infile "文件名"
	into table 表名
	fields terminated by "分隔符"
	lines terminated by  "\n";
3)示例
	a.查看数据库的默认路径
		show variables like "secure_file_priv";#结果/var/lib/mysql-files
	b.将需要导入的文件复制到默认搜索目录中
		sudo cp 文件 /var/lib/mysql-files/
	c..导入文件
		load data infile " /var/lib/mysql-files/文件名"
		into table userinfo
		fields terminated by ":"
		lines terminated by  "\n";

2、导出

1）作用：将数据库的内容导出到文件中（例如csv，txt）
2）语法
	select ... from  表名
	into outfile  文件名
	fields terminated by "分隔符"
	lines terminated by  "\n";

3、复制

1) 语法：create table 表名 select 查询命令；
2) 示例：create table userinfo2 select * from userinfo;#复制userinfo表的所有记录到userinfo2
3) 复制表结构：create table 表名 select 查询命令 where false; #复制不会把key类型复制过来

4、备份

1) 备份：mysqldump -uroot -p 源库名 > 路径/文件名.sql
	源库名：
		--all-databases    备份所以的库
		库名                备份单个库
		-B 库1 库2         备份多个库
		库名 表1 表2      备份指定库的指定表
2) 恢复：mysql -uroot -p 库名 < 理解/文件名.sql

九、mysql的高级查询SQL

1、嵌套查询

1) 定义：把查询结果作为外层的查询条件
2) select username,uid from userinfo  where uid < (select avd(uid) from userinfo );
	#查询uid小于uid平均值的用户名和uid

2、链接查询

1) 内连接
	a.定义：从表中删除与其他被连接的表中没有匹配到的行
	b.语法：select 字段名列表 from 表1 inner join 表2 on 条件 inner join 表3 in 条件；
	c.注释：获取的结果是所有表中不满足条件的行全部去除，以表1显示为主
2) 左连接
	a.定义：从表中删除与其他被连接的表中没有匹配到的行
	b.语法：select 字段名列表 from 表1 left join 表2 on 条件;
	c.注释：获取的结果是表1的全部和表2满足的部分，以表1显示为主
3) 右连接
	a.定义：从表中删除与其他被连接的表中没有匹配到的行
	b.语法：select 字段名列表 from 表1right join 表2 on 条件;
	c.注释：获取的结果是表2的全部和表1满足的部分，以表1显示为主

3、多表查询

1) select 字段名列表 from 表名列表；#卡迪尔积
2）select 字段名列表 from 表名列表 where 条件; #等同于内连接

十、mysql的事务

1. 定义：MySQL每次的sql语句都会默认提交。为了保证sql运行的一致性，事务提供了多条sql一起提交的方式，只要其中一条错误就会回滚。
2. 查看语句:show variables like "autocommit";
3. 开启事务
	start transaction;
	sql命令.....
	##此时 autocommit 被禁用，sql命令不会对数据库中的数据做修改
4. 终止事务
	提交sql：commit;
	取消sql：rollback;
5.实例
	背景：银行间的转账 小A从建行转5000给小B的工商

start transaction;
update userinfo_jh set 
price=(select price-5000 from userinfo_jh where name=小A)
where name=小A;  #将小A的银行卡减去5000
update userinfo_gs set 
price=(select price+5000 from userinfo_gs where name=小B)
where name=小B;  #将xiaoB的银行卡加上5000
commit; #都没有问题的时候，再提交

十一、mysql的储存引擎

1. 定义：是用来处理表的处理器
2. 基本操作：
	1) 查看已有表的储储存引擎:show create table 表名;
	2) 创建表时制定存储引擎 ； create table 表名(...) engine=myisaml
	3) 常用的储存引擎特点
		a.innodb特点（适用于经常修改表的引擎，高并发）
			共享表攻击（报名.frm粗存表结构，表名.idb储存表记录和索引信息）
			支持行级锁
		b.myiam特点（适用于经常查询的表，用innodb会浪费资源）
			独享表空间(表名.frm,表名.myd,表名.myi)
			表级锁
	4) 锁
		1、加锁的目的
			解决客户端并发访问的冲突问题
		2、锁类型
			读锁（select）共享锁
				加读锁之后不能更改表中内容，但可以进行查询
			写锁（update，insert，delect）互斥锁、排他锁
		3、锁力度
			表级锁（针对整个表的作用）
			行级锁（针对行的作用）
			##操作完成后会自动释放锁

十二、mysql的调优

1、选择合适的储存引擎
	1）经常用来读的表使用myisam储存引擎
	2）其余的表都用innodb储存引擎
2、SQL语句调优（表面全表扫描）
	1）在select where order by 常涉及到的字段简历索引
	2）where字句中不适用！=,否则将放弃索引进行全表扫描
	3）尽力避免用NULL值判断，否则会全表扫描（可以用0或者其他值代表NULL值）
	4）尽量避免 or 来链接条件，导致全表扫描。
	5）模糊查询尽量避免使用前置%，导致全表扫描
	6）劲量避免 in 和not in,导致全表扫描。（可以转换成between and）
	7）尽量避免使用 select * ...,要用具体的字段列表代替 *，不要返回用不到的任何字段

十三、python操作mysql

1、python操作mysql的库
	python3:pymysql 
	python2:MysSQLdb
	安装：pip install 相应的库名
2、python模块使用流程
	1）建立数据库连接
		db=pymysql.connect(IP地址,用户,密码,库名,编码类型)
	2）建立游标对象
		corsor=db.cursor()
	3）使用游标对象的方法操作数据库
		cursor.execute(sql命令)
	4）提交commit
		db.commit
	5）管理游标对象
		cursor.close()
	6）关闭数据库
		db.close()
3、对象的具体函数及功能
	1) 连接语法：对象名=pymysql.connect(...)
	2) connect支持的方法
		a. cursor() 创建一个游标对象db.cursor()
		b. commit() 提交到数据库执行
		c. rollvack() 回滚
		d. close() 关闭数据库
	3) 游标对象支持的方法
		1、execyte(sql) 执行sql命令
		2、fetchone() 取得结果集的第一条记录
		3、fetchmany() 取得结果集的n条记录
		4、fetchall() 取得结果集的所有记录
		5、close() 关闭游标