day16-20180508笔记

笔记：python的MySQL数据库模块、SQL语句

一、MySQL数据库模块

MySQLdb 是用于Python链接Mysql数据库的接口，它实现了 Python 数据库 API 规范 V2.0，基于 MySQL C API 上建立的。
pip好像是不支持安装MySQLdb的，我们可以通过网站下载安装，
下载地址：https://pypi.python.org/pypi/MySQL-python/1.2.5
分别对应有windows和源码安装的方法

安装依赖包：

yum install –y python-devel
yum install –y mysql-devel
yum install –y gcc

python3以后好像是不支持MySQLdb了，可以是用pymysql包，

可以直接通过pymysql进行使用。
pip install pymysql

MySQLdb 只适用于python2.x，发现pip装不上。它在py3的替代品是： 
import pymysql

mysql 事物
一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）
1、事务的原子性：一组事务，要么成功；要么撤回。
2、稳定性 ： 有非法数据（外键约束之类），事务撤回。
3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里

mysql默认开启autocommit自动提交功能，如果关闭autocommit功能后，插入或更新的数据会保存到虚拟内存，需要手动commit提交插入到相应的表时；

mysql> show variables like 'auto%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
| autocommit               | ON    |
| automatic_sp_privileges  | ON    |
+--------------------------+-------+

python连接Mysql

1.创建数据库 create database python;
2. 授权用户
grant all privileges on *.* to xiang@’%’ identified by ‘123456’;
flush privilege;

import pymysql

# 创建连接
conn = pymysql.connect(host="192.168.48.128",user="xiang",passwd="123456",db="python",charset="utf8")

比较常用的参数包括:
host:数据库主机名.默认是用本地主机
user:数据库登陆名.默认是当前用户
passwd:数据库登陆的秘密.默认为空
db:要使用的数据库名.没有默认值
port:MySQL服务使用的TCP端口.默认是3306，数字类型
charset:数据库编码

创建库

create database test;

创建表

create table tb1(id smallint unsigned auto_increment primary key,username varchar(20) not null);

查看表的结构

----+----------------------+------+-----+---------+----------------+

+----------+----------------------+------+-----+---------+----------------+

| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |

+----------+----------------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)

插入数据

insert tb1(id,username) values (1, 'user1');

insert tb1(id,username) values (2, 'user2');

insert tb1(id,username) values (3, 'user1');

insert tb1(id,username) values (5, 'user4');

import pymysql

# 创建连接
conn = pymysql.connect(host="192.168.3.11",user="user1",passwd="123456",db="test",charset="utf8")
# 创建游标
cus = conn.cursor()
# 定义sql
sql = "select * from tb1;"
print(sql)

try:
    cus.execute(sql)
    result = cus.fetchall()
    print(result)
except Exception as e:
    raise e
finally:
    cus.close()
    conn.close()

以上实例输出的结果

select * from tb1;
((1, 'user1'), (2, 'user2'), (3, 'user1'), (5, 'user4'))

推荐大家使用函数的方式：

import pymysql

def connect_mysql():
    db_config = {
        'host': '192.168.48.128',
        'port': 3306,
        'user': 'xiang',
        'passwd': '123456',
        'db': 'python',
        'charset': 'utf8'
    }
    cnx = MySQLdb.connect(**db_config)
return cnx

游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理

import…………
if __name__ == '__main__':
    cnx = connect_mysql()
    cus = cnx.cursor()
    sql  = ''' create table test(id int not null);insert into test(id) values (100);'''
    try:
        cus.execute(sql)
        cus.close()
        cnx.commit()
    except Exception as e:
        cnx.rollback()
        print('Error')
        # raise e
    finally:
        cnx.close()

游标常用的方法：
常用方法:
cursor():创建游标对象
Cus = connect_mysql().cursour()
close():关闭此游标对象
excute(sql[, args]):执行一个数据库查询或命令
fetchone():得到结果集的下一行
fetchmany([size = cursor.arraysize]):得到结果集的下几行
fetchall():得到结果集中剩下的所有行
executemany (sql, args):执行多个数据库查询或命令

个人推荐，尽量不使用executemany，通过程序循环不断调用excute函数

python编程中可以使用MySQLdb进行数据库的连接及诸如查询/插入/更新等操作，但是每次连接mysql数据库请求时，都是独立的去请求访问，相当浪费资源，而且访问数量达到一定数量时，对mysql的性能会产生较大的影响。因此，实际使用中，通常会使用数据库的连接池技术，来访问数据库达到资源复用的目的。

import pymysql
from DBUtils.PooledDB import PooledDB

from DBUtils.PooledDB import PooledDB
db_config = {
        "host": "192.168.48.131",
        "port": 3306,
        "user": "xiang",
        "passwd": "xiang",
        "db": "python",
        # "charset": "utf8"
    }

spool = PooledDB(pymysql, 5, **db_config)  # 5为连接池里的最少连接数
# def connect_myssql():
conn = spool.connection()  # 以后每次需要数据库连接就是用connection（）函数获取连接
cur = conn.cursor()
SQL = "select * from tmp;"
r = cur.execute(SQL)
r = cur.fetchall()
print(r)
cur.close()
conn.close()

二、MySQL常用操作

授权超级用户：
grant all privileges on *.* to 'tangnanbing'@'%' identified by '1qaz@WSX' with grant option;
查看库：
show databases;
查看都有哪些库  show databases;
查看某个库的表 use db; show tables G; 
查看表的字段 desc tb;
查看建表语句 show create table tb;
当前是哪个用户  select user();
查看用户列表　　select user,host from mysql.user;
当前库 select database();
创建库 create database db1; 
创建表 create table t1 (id int, name char(40) adress varchar(30));  
char(10)              'aaa       '
varchar(10)          'aaa'
查看数据库版本 select version(); 
查看mysql状态 show status;
修改mysql参数 show variables like 'max_connect%'; set global max_connect_errors = 1000; 
查看mysql队列 show processlist; 
select * from information_schema.processlist where info is not null;
sleep的可以忽略，qurey查询的才有
创建普通用户并授权 grant all on *.* to databases1.user1 identified by '123456'; 
grant all on db1.* to 'user2'@'10.0.2.100' identified by '111222'; 
grant all on db1.* to 'user3'@'%' identified by '231222';insert into tb1 (id,name) values(1,'aming');
更改密码 UPDATE mysql.user SET password=PASSWORD("newpwd") WHERE user='username' ;   
查询 select count(*) from mysql.user; select * from mysql.db; select * from mysql.db where host like '10.0.%'; 
插入 update db1.t1 set name='aaa' where id=1;  
清空表 truncate table db1.t1; 
删除表 drop table db1.t1; 
删除数据库 drop database db1; 
修复表 repair table tb1 [use frm];
查看权限show grants for root@'localhost';

echo "select user,host,password from mysql.user" |mysql -uroot -plingxiangxiang
mysql -uroot -p1234556 -e "select user,host,password into outfile '/home/mysql/1.txt' from mysql.user;"

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

索引：
 sql4 = '''alter table Course add primary key(CouID);'''
 sql5 = '''alter table Score add index idx_StdID_CouID(StdID, CouID);'''

删除索引：
# sql6 = '''alter table Score drop  index idx_StdID_CouID;'''   删除索引

 sql7 = '''explain select * from Score where StdID = 16213;'''

工作当中使用最多的SQL操作:

mysql mysqldump 只导出表结构 不导出数据
复制代码代码如下:

mysqldump --opt -d 数据库名 -u root -p > xxx.sql 

备份数据库 
复制代码代码如下:

#mysqldump　数据库名　>数据库备份名 
#mysqldump　-A　-u用户名　-p密码　数据库名>数据库备份名 
#mysqldump　-d　-A　--add-drop-table　-uroot　-p　>xxx.sql 
# mysqldump -S 数据库套接字文件 -u用户名 -p用户密码 -h mysql主机 --skip-lock-tables 数据库名称 表名称 --where "查询条件" > 导出文件名.sql

1.导出结构不导出数据 
复制代码代码如下:

mysqldump　--opt　-d　数据库名　-u　root　-p　>　xxx.sql　　 

2.导出数据不导出结构 
复制代码代码如下:

mysqldump　-t　数据库名　-uroot　-p　>　xxx.sql　 

3.导出数据和表结构 
复制代码代码如下:

mysqldump　数据库名　-uroot　-p　>　xxx.sql　 

4.导出特定表的结构 
复制代码代码如下:

mysqldump　-uroot　-p　-B　数据库名　--table　表名　>　xxx.sql　　 

导入数据： 
　　由于mysqldump导出的是完整的SQL语句，所以用mysql客户程序很容易就能把数据导入了： 
复制代码代码如下:

#mysql　数据库名　<　文件名 
#source　/tmp/xxx.sql　　 

规则：
mysqldump   -u root  -p   数据库名  表名 > 你要保存的sql文件（加位置）

单表备份
mysqldump -h 127.0.0.1 -u root -p erp cms_info>cms_info.sql


命令行下具体用法如下： 
mysqldump -u用戶名 -p密码 -d 数据库名 表名 > 脚本名;
  
导出整个数据库结构和数据
mysqldump -h localhost -uroot -p123456 database > dump.sql
 
导出单个数据表结构和数据
mysqldump -h localhost -uroot -p123456  database table > dump.sql
 
 
导出整个数据库结构（不包含数据）
mysqldump -h localhost -uroot -p123456  -d database > dump.sql
 
导出单个数据表结构（不包含数据）
mysqldump -h localhost -uroot -p123456  -d database table > dump.sql


删除一个月前当天的数据
DELETE FROM yeah100.student_answer_block WHERE examination_id IN (SELECT ID FROM yeah100.examinations WHERE STATISTIC_TRIGGERED = 'Y' AND STATISTIC_DATE < DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND STATISTIC_DATE > DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 1 DAY),INTERVAL 1 MONTH) AND ORG_NO IS NOT NULL)
执行完删除后，再执行以下指令，回收资源（目前只了解到阿里云RDS云数据库仅此操作步骤）
optimize table yeah100.student_answer_block

总结：

1、熟悉SQL语句，基本要会增删改查；

2、mysql优化，最基本的优化就是查询数据，千万不要使用 *