Python之MySQL

oracle（收费）
Mysql（开源）：稳定性可能不如Oracle，但是因为开源和免费所以是最常见的。
SqlServer（微软）
#以上三种为常见的
DB2
Postgresql
Sqlite（轻量级开源免费）

MySQL - MySQL服务器。你需要该选项，除非你只想连接运行在另一台机器上的MySQL服务器。
MySQL-client - MySQL 客户端程序，用于连接并操作Mysql服务器。
MySQL-devel - 库和包含文件，如果你想要编译其它MySQL客户端，例如Perl模块，则需要安装该RPM包。
MySQL-shared - 该软件包包含某些语言和应用程序需要动态装载的共享库(libmysqlclient.so*)，使用MySQL。
MySQL-bench - MySQL数据库服务器的基准和性能测试工具。

  yum -y remove mysql-community-client-5.6.38-2.el7.x86_64

  卸载不掉的用 rpm -ev 

  依次卸载 直到没有

1、修改MySQL的登录设置：
[root@localhost ~]#vim /etc/my.cnf
在[mysqld]的段中加上一句：skip-grant-tables
例如：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-grant-tables
保存并且退出vi。

2、重新启动mysqld 
[root@localhost ~]#service mysqld restart
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]

3、登录并修改MySQL的root密码 
[root@localhost ~]# mysql 

mysql> USE mysql ; 

mysql> UPDATE user SET Password = password ( 'new-password' ) WHERE User = 'root' ; 
##如果这里提示ERROR 1054 (42S22): Unknown column 'password' in 'field list'    输入：mysql> describe user;  来查看输出内容下方是否有authentication_string内容，如果有的话就使用下面的命令修改密码:
#mysql> update user set authentication_string=password('123456') where user='root';

mysql> flush privileges ; 

mysql> quit

4、将MySQL的登录设置修改回来
[root@localhost ~]# vim /etc/my.cnf
将刚才在[mysqld]的段中加上的skip-grant-tables删除
保存并且退出vim

5、重新启动mysqld 
[root@localhost ~]# service mysqld restart
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]

首先查询安装包：

rpm -qa|grep mysql

查询到的一个结果为：mysql-community-libs-5.7.13-1.el6.x86_64

yum 删除

yum -y remove mysql-community-libs-5.7.13-1.el6.x86_64

rpm 删除（仅用 "-e" 判断依赖）

rpm -e mysql-community-libs-5.7.13-1.el6.x86_64

Window上安装Mysql相对来说会较为简单，你只需要载 MySQL 下载中下载window版本的mysql安装包，并解压安装包。

双击 setup.exe 文件，接下来你只需要安装默认的配置点击"next"即可，默认情况下安装信息会在C:\mysql目录中。

接下来你可以通过"开始" =》在搜索框中输入 " cmd" 命令 =》 在命令提示符上切换到 C:\mysql\bin 目录，并输入一下命令：

mysqld.exe --console

如果登录时出现如下错误：
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
需要通过  alter  user 'root'@'localhost' identified by '111111';  重新设置密码。

但是又可能出现：ERROR 1819 (HY000): Your password does not satisfy the current policy requirements 
这说明密码复杂度不够，需要大写、小写、数字、特殊字符
mysql> alter  user 'root'@'localhost' identified by 'QWE!@#qwe123';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

[root@localhost ~]# mysql -uroot -p123qwe

#显示数据库列表
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
#当前有3个数据库

'''
【INFORMATION_SCHEMA 数据库】 是MySQL自带的，它提供了访问数据库 元数据 的方式。什么是 元数据 呢？元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。
有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。
在MySQL中，把【INFORMATION_SCHEMA】 看作是一个数据库，确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。
在 【INFORMATION_SCHEMA 】中，有数个 只读 表。它们实际上是 视图 ，而不是基本表，因此，你将无法看到与之相关的任何文件。
'''

'''
【mysql数据库】
mysql库很重要它里面有MYSQL的系统信息，我们改密码和新增用户，实际上就是用这个库进行操作。
'''

'''
【PERFORMANCE_SCHEMA数据库】
MySQL 5.5开始新增一个数据库：PERFORMANCE_SCHEMA，主要用于收集数据库服务器性能参数。并且库里表的存储引擎均为PERFORMANCE_SCHEMA，而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。
'''

#进入数据库并显示库中的数据表
mysql> use mysql；
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
28 rows in set (0.00 sec)

查看表结构如下：
Field相当于excel中第一行的标题
Type是数据的类型（数字、字符串等等。。。）
char表示字符串，括号中的数字表示定义这个字符串的长度
enum表示枚举
Null表示是否可以为空；NO的话就是必须要赋值，不能为空
Key中的PRI表示 primary key（主键），之前说过主键代表唯一的值（相当于×××）。
default 表示可以填写默认值
extra 可以填写额外的内容，没有要求

查看表内容：
内容较多，显示的比较乱

查看表内容，通过\G来整理内容的显示格式，以竖着的方式来显示。

/etc/init.d/mysqld start

需要将mysqld二进制文件添加到/etc/init.d/目录中

#查看所有进程
ps -ef

#查看mysql进程
ps -ef | grep mysql

mysql> select User from user;

当前的用户只有root
下面是mysql的常用增删改查命令：
SELECT查找
INSERT插入
UPDATE修改
DELETE删除
CREATE创建
DROP删除

#授予查找与插入权限
mysql> grant select,insert

#创建账号授予全部权限
grant all on test.* to 'aubrey'@'%' identified by '123qwe';
'''授予aubrey全部权限访问test这个数据库；
@表示指定访问源，%表示所有，也就是指定所有源都可以访问。
访问的密码为：123qwe。'''

Query OK：表示查询，因为这里并没有进行查询，所有没有异常，就是OK。
0 rows affected：因为是新建，没有插入，所以显示有0行收到影响。

查看user字段的内容，可以看到有关user:aubrey的账户信息；
不过这里权限都为N

查看账号信息

我们通过新账号进入mysql，但是提示被localhost拒绝，%虽然是可以指定访问源，但不包括localhost。

指定访问源为localhost

指定localhost后可以访问mysql了。

在root下是可以看到3个的，这里当前只能看到information_schema 是默认就可以看到的，但是这里没有看到test数据库，是因为还没有建立test数据库

#创建test数据库 
create database test;
'''
我们在xshell中回到root登录的mysql来创建test数据库。
'''

回到aubrey用户登录的mysql，再次查看，就可以看到test数据库了。

使用test数据库，并查看表项，表项为空。

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

USE 数据库名 :选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。

SHOW DATABASES: 列出 MySQL 数据库管理系统的数据库列表。

SHOW TABLES: #显示指定数据库的所有表，使用该命令前需要使用 use命令来选择要操作的数据库。

SHOW COLUMNS FROM 数据表: #显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。

create database testdb charset "utf8"; #创建一个叫testdb的数据库，且让其支持中文 

drop database testdb; #删除数据库

SHOW INDEX FROM 数据表:显示数据表的详细索引信息，包括PRIMARY KEY（主键）。

char：存储定长数据很方便，CHAR字段上的索引效率级高，必须在括号里定义长度，可以有默认值，比如定义char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间（自动用空格填充），且在检索的时候后面的空格会隐藏掉，所以检索出来的数据需要记得用什么trim之类的函数去过滤空格。
varchar：存储变长数据，但存储效率没有CHAR高，必须在括号里定义长度，可以有默认值。保存数据的时候，不进行空格自动填充，而且如果数据存在空格时，当值保存和检索时尾部的空格仍会保留。另外，varchar类型的实际长度是它的值的实际长度+1，这一个字节用于保存实际使用了多大的长度。
text：存储可变长度的非Unicode数据，最大长度为2^31-1个字符。text列不能有默认值，存储或检索过程中，不存在大小写转换，后面如果指定长度，不会报错误，但是这个长度是不起作用的，意思就是你插入数据的时候，超过你指定的长度还是可以正常插入。

经常变化的字段用varchar
知道固定长度的用char
尽量用varchar
超过255字符的只能用varchar或者text
能用varchar的地方不用text   

语法
CREATE TABLE table_name (column_name column_type);

创建一个student表

#在aubrey账号下创建一个student表；类型INT ，NOT NULL不能为空 AUTO_INCREMENT对主键自动增加编号；name 32个字节，不能为空；age 不能为空； 注册日期不能为空；PRIMARY KEY(stu_id)将stu_id设置为主键，设置主键后就是唯一。
create table student(
   stu_id INT NOT NULL AUTO_INCREMENT,
   name CHAR(32) NOT NULL,
   age  INT NOT NULL,
   register_date DATE,
   PRIMARY KEY ( stu_id )
);

语法
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[OFFSET M ][LIMIT N]

查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
你可以使用 WHERE 语句来包含任何条件。
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0(偏移量就是偏移几个数据开始查询)。
你可以使用 LIMIT 属性来设定返回的记录数（显示多少条）。

语法
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。
select *from student where name like binary "%Li" order by stu_id desc;

select * from student order by stu_id;

select * from student order by stu_id desc;

update student set register_date="2030-01-01" where stu_id = 1;
update student set register_date="2031-02-02" where stu_id = 2;
update student set register_date="2032-02-08" where stu_id = 3;
#修改一下时间

select * from student order by register_date;

select * from student order by register_date desc;

insert into student (name,age,register_date) values("LiSi",22,"2014-03-21");

select name,count(*) from student group by name;

select name,count(*) as JiShu from student group by name;

select name,sum(age) from student group by name;

select name,sum(age) from student group by name with rollup;

select coalesce(name,"Total Age"), sum(age) from student group by name with rollup;

alter table student add money int(11);
#alter table表示要修改哪个表，这里修改student表。
#add表示增加字段，这里增加一个叫money的字段，类型为int，括号里的11表示11个字节。

insert into student (name,age,register_date,money) values("WangEr",67,"2018-9-9",999);

alter table student drop age;
#删掉age字段及所有内容

alter table student drop money;
alter table student add money int(11) not null;
#我们删掉money字段，然然后在重新增加money字段，然后设定为not null

alter table student modify money int(123) null;
修改null为YES（修改字段的类型）

alter table student modify money int(123) not null;
在修改回为 not null

alter table student change money new_money char(32) not null default "X";
#将money改成新名字new_money；
#类型改为char（32），not null
#默认值为X

alter table student change stu_id id int(11) not null ;
修改主键的名称，从stu_id修改为id

CREATE TABLE `study_record` (
  `id` int(11) NOT NULL,
  `day` int NOT NULL,
  `status` char(32) NOT NULL,
  `stu_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_student_key` (`stu_id`),
  CONSTRAINT `fk_student_key` FOREIGN KEY (`stu_id`) REFERENCES `student` (`id`)
)
;
#  `stu_id` int(11) NOT NULL 这里定义的是外键
#KEY `fk_student_key` (`stu_id`)：是定义一个外键的名称为fk_student_key，关联外键stu_id
#CONSTRAINT `fk_student_key`：使用名为fk_student_key的外键，外键stu_id关联student表中的id值（主键）

alter table study_record modify id int auto_increment;
#创建study_record表时，没有设置为自动自增 id数。这里修改为自动自增。

mysql> create table A(
    -> a int not null );

mysql>
mysql> create table B( b int not null );

#创建一个A、一个B表用于测试查询

select * from A inner join B on A.a = B.b ;
#A inner B：A内连接B；
#on A.a = B.b：用A表中的a字段来比较B表中的b字段。

select A.*, B.* from A,B where A.a = B.b;
#等同于select * from A inner join B on A.a = B.b ;

select * from A left join B on A.a = B.b ;
#获取左表所有记录，即使右表没有对应匹配的记录

select * from B left join A on A.a = B.b ;
#把A和B调换位置

select * from A right join B on A.a = B.b ;

select * from A left join B on A.a = B.b UNION select * from A right join B on A.a = B.b;
#通过UNION将两个语句联合起来使用

1、事务的原子性：一组事务，要么成功；要么撤回; 
   所做的操作，要么成功，要么撤回。
2、稳定性 ： 有非法数据（外键约束之类），事务撤回。
   比如说：两个关联的表，当你删除时，是要将两个表关联的数据都删除，可是当你操作后只删除其中一个表的数据，数据库就卡死了，导致没有完全删除，就会撤回。
3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里
   当执行命令到一半时就宕机了，因为宕机也无法撤回，但是当重启后会查看日志，根据日志进行撤回。

mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into A (a) values (6);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into A (a) values (7);
Query OK, 1 row affected (0.00 sec)

mysql> select * from A;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+---+
7 rows in set (0.00 sec)
#查看当前A表的内容，数据已经成功的插入。

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select * from A;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---+
5 rows in set (0.00 sec)
#使用回滚后，就恢复了插入数据之前的数据内容。

mysql> insert into A (a) values (6);
Query OK, 1 row affected (0.02 sec)

mysql>
mysql> insert into A (a) values (7);
Query OK, 1 row affected (0.02 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from A;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+---+
7 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from A;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+---+
7 rows in set (0.01 sec)
#commit提交以后，就无法再回滚了

show index from student;
#查看student表的索引

create index index_name on student (name(32));
#创建索引，索引名温蒂index_name；
#on student (name(32))：是针对student这个表来创建索引，引用student的name字段来创建索引，索引hash长度不能超过32（这个长度一般建议使用与字段相同的长度）

CREATE TABLE mytable( 

ID INT NOT NULL,  

username VARCHAR(16) NOT NULL, 

INDEX [indexName] (username(length)) 

); 

drop index index_name on student;

create unique index index_name on student (id);
#通过id建立唯一索引； 不要使用name等会出现重复值的字段来建立唯一索引。

pip3 install pymysql

#授权
grant all on *.* to 'root'@'%' identified by '123qwe';
'''我们通过root进入mysql，然后给root授权，所有源都可以登录（除本地）'''

#更新配置
flush privileges;

#在centos7中关闭防火墙
systemctl stop firewalld.service
systemctl disable firewalld.service
systemctl mask firewalld.service

#!/usr/bin/env python
# -*- coding:utf-8 -*-
#示例

import pymysql

# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')

# 创建游标
cursor = conn.cursor()
'游标就是MySQL中的："mysql>" 位置'

# 执行SQL，并返回收影响行数
effect_row = cursor.execute("update hosts set host = '1.1.1.2'")

# 执行SQL，并返回受影响行数
#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))

# 执行SQL，并返回受影响行数
#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])

# 提交，不然无法保存新建或者修改的数据
conn.commit()

# 关闭游标
cursor.close()
# 关闭连接
conn.close()

#在pycharm中执行代码来连接linux
import pymysql

# 创建连接
conn = pymysql.connect(host='192.168.2.202', port=3306, user='aubrey', passwd='123qwe', db='test')

# 创建游标
cursor = conn.cursor()
'''游标就是MySQL中的："mysql>" 光标位置'''
'''相当于创建一个实例'''

# 执行SQL，并返回收影响行数
effect_row = cursor.execute("select * from student")

#读取数据
print (cursor.fetchone())
'''cursor.fetchone读取一条数据，'''
print (cursor.fetchone())
'''cursor.fetchone接着上一条数据，再往下读取一条数据，'''

import pymysql

# 创建连接
conn = pymysql.connect(host='192.168.2.202', port=3306, user='aubrey', passwd='123qwe', db='test')

# 创建游标
cursor = conn.cursor()
'''游标就是MySQL中的："mysql>" 光标位置'''
'''相当于创建一个实例'''

# 执行SQL，并返回收影响行数
effect_row = cursor.execute("select * from student")

#读取数据
# print (cursor.fetchone())
# '''cursor.fetchone读取一条数据，'''
# print (cursor.fetchone())
# '''cursor.fetchone接着上一条数据，再往下读取一条数据，'''
print ('----------------------------')
print (cursor.fetchall())
'''读取所有数据'''

print (cursor.fetchmany())
'''获取多条数据'''

drop table study_record;
'''因为之前student表被关联了study_record表，导致不能修改student表，所以这里删除study_record表'''

alter table student modify id int auto_increment;
'''让student表的id可以自增'''

import pymysql

# 创建连接
conn = pymysql.connect(host='192.168.2.202', port=3306, user='aubrey', passwd='123qwe', db='test')

# 创建游标
cursor = conn.cursor()
'''游标就是MySQL中的："mysql>" 光标位置'''
'''相当于创建一个实例'''

data = [
    ("N1","2015-05-22",'M'),
    ("N2","2015-05-21",'M'),
    ("N3","2015-05-23",'F')
]

#执行SQL，并返回受影响行数，执行多次
cursor.executemany("insert into student (name,register_date,new_money) values(%s,%s,%s)",data)
'''将data数据插入到student表中'''

conn.commit()
'''提交才能生效'''