python运维之路——mysql、pymysql

MySQL

1、数据库介绍

什么是数据库
数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，每个数据库都有一个或多个不同的API用于创建，访问，管理，搜索和复制所保存的数据。我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理的大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。RDBMS即关系数据库管理系统(Relational Database Management System)的特点：
- 1.数据以表格的形式出现
- 2.每行为各种记录名称
- 3.每列为记录名称所对应的数据域
- 4.许多的行和列组成一张表单
- 5.若干的表单组成database

下图就是一个数据库

数据库

RDBMS术语
在我们开始学习MySQL 数据库前，让我们先了解下RDBMS的一些术语：
- 数据库: 数据库是一些关联表的集合。
- 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
- 列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
- 行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
- 冗余：存储两倍数据，冗余可以使系统速度更快。(表的规范化程度越高，表与表之间的关系就越多；查询时可能经常需要在多个表之间进行连接查询；而进行连接操作会降低查询速度。例如，学生的信息存储在student表中，院系信息存储在department表中。通过student表中的dept_id字段与department表建立关联关系。如果要查询一个学生所在系的名称，必须从student表中查找学生所在院系的编号（dept_id），然后根据这个编号去department查找系的名称。如果经常需要进行这个操作时，连接查询会浪费很多的时间。因此可以在student表中增加一个冗余字段dept_name，该字段用来存储学生所在院系的名称。这样就不用每次都进行连接操作了。)
- 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
- 外键：外键用于关联两个表。
- 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
- 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
- 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

Mysql数据库
Mysql是最流行的关系型数据库管理系统，在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
- Mysql是开源的，所以你不需要支付额外的费用。
- Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
- MySQL使用标准的SQL数据语言形式。
- Mysql可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
- Mysql对PHP有很好的支持，PHP是目前最流行的Web开发语言。
- MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。
- Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统。

2、MySQL的安装和使用

安装

sudo apt-get install mysql-server

安装后需要做的
Mysql安装成功后，默认的root用户密码为空，你可以使用以下命令来创建root用户的密码：

mysqladmin -u root password "new_password"

连接的命令

mysql -u root -p

启动服务

/etc/init.d/mysqld start

3、mysql管理

启动及关闭MySQL服务
检查MySQL服务器是否启动：

ps -ef | grep mysqld

如果MySql已经启动，以上命令将输出mysql进程列表， 如果mysql未启动，你可以使用以下命令来启动mysql服务器:

cd /usr/bin./mysqladmin -u root -p shutdown

MySQL用户设置
如果你需要添加 MySQL 用户，你只需要在 mysql 数据库中的 user 表添加新用户即可。

以下为添加用户的的实例，用户名为zara，密码为zara123：

grant select,insert,update,delete,create,drop on TUTORIALS.* to 'zara'@'localhost' identified by 'zara123';

/ectc/my.cnf文件配置
一般情况下，不需要配置该文件，文件的默认配置如下

[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock[mysql.server]user=mysqlbasedir=/var/lib[safe_mysqld]err-log=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

管理MySQL的命令
一下列出了使用MySQL数据库过程中常用的命令：
- use 数据库名：选择要操作的MySQL数据库，使用该命令后所有MySQL命令都只针对该数据库。
- show databases：列出MySQL所有的数据库
- show tables：列出当前数据库所有的表
- show columns from 表名：显示表的属性，属性类型，主键信息，是否为NULL。默认值等其他信息。
- create databases testdb charset ‘utf8’; ：创建一个名叫testdb的数据库，编码为utf8
- drop database testdb; :删除testdb数据库
- show index from 表名：显示表的详细索引信息，包括primary key(主键)。

4、数据类型

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型
MySQL支持所有标准SQL数值数据类型。
这些类型包括严格数值数据类型（integer, smallint, decimal和numeric），以及近似数值数据类型(float, real和double precision)。
关键字int和integer是同义词， 关键字dec和decimal是同义词。
bit数据类型保存字段值，并且支持tinyint、mediumint和bigint。下面显示了需要的每个整数类型的存储和范围

类型：tinyint
大小：1 字节
范围（有符号）：(-128，127)
范围（无符号）：(0，255)
用途： 小整数值

类型：smallint
大小： 2 字节
范围（有符号）：(-32 768，32 767)
范围（无符号）：(0，65 535)
用途： 大整数值

类型：mediumint
大小： 3 字节
范围（有符号）：(-8 388 608，8 388 607)
范围（无符号）：(0，16 777 215)
用途：大整数值

类型：int、integer
大小： 4 字节
范围（有符号）：(-2 147 483 648，2 147 483 647)
范围（无符号）： (0，4 294 967 295)
用途： 大整数值

类型：bigint
大小：8 字节
范围（有符号）： (-9 233 372 036 854 775 808，9 223 372 036 854 775 807)
范围（无符号）：(0，18 446 744 073 709 551 615)
用途： 极大整数值

类型：float
大小： 4 字节
范围（有符号）：(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)
范围（无符号）：0，(1.175 494 351 E-38，3.402 823 466 E+38)
用途： 单精度浮点数值

类型：double
大小：8 字节
范围（有符号）：(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
范围（无符号）：0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
用途：双精度浮点数值

类型：decimal
大小：对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2
范围（有符号）：依赖于M和D的值
范围（无符号）：依赖于M和D的值
用途：小数值

日期和时间类型
表示是时间值的日期和时间的类型为datetime、date、timestamp、time和year。
每个时间类型都有一个有效值范围和一个“零”值，当指定不合法的MySQL不能表示的值时，使用“零”值。
timestamp类型有专有的自动更新特性，将在后面描述。

类型：date
大小(字节)：3
范围：1000-01-01/9999-12-31
格式:YYYY-MM-DD
用途: 日期值

类型：time
大小(字节)：3
范围： ‘-838:59:59’/’838:59:59’
格式:HH:MM:SS
用途: 时间值或持续时间

类型：year
大小(字节)：1
范围： 1901/2155
格式:YYYY
用途:年份值

类型：datetime
大小(字节)：8
范围：1000-01-01 00:00:00/9999-12-31 23:59:59
格式: YYYY-MM-DD HH:MM:SS
用途:混合日期和时间值

类型：timestamp
大小(字节)：4
范围：1970-01-01 00:00:00/2037 年某时
格式: YYYYMMDD HHMMSS
用途:混合日期和时间值，时间戳

字符串类型
字符串类型指char、varchar、binary、varbinary、blob、text、enum和set。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型:char
大小:0-255字节
用途:定长字符串

类型:varchar
大小: 0-65535 字节
用途:变长字符串

类型:tintblob
大小:0-255字节
用途:不超过 255 个字符的二进制字符串

类型:tinytext
大小: 0-255字节
用途:短文本字符串

类型:blob
大小:0-65 535字节
用途:二进制形式的长文本数据

类型:text
大小: 0-65 535字节
用途: 长文本数据

类型:mediumblob
大小: 0-16 777 215字节
用途:二进制形式的中等长度文本数据

类型:mediumtext
大小: 0-16 777 215字节
用途:中等长度文本数据

类型:longblob
大小:0-4 294 967 295字节
用途:二进制形式的极大文本数据

类型:longtext
大小: 0-4 294 967 295字节
用途:极大文本数据

char和varchar类型类似，但他们保存和检索的方式不同。他们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写切换。

binary和varbinary类似于char和varchar，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

blob是一个二进制大对象，可以容纳可变数量的数据。有4种blob类型：tinyblob、blob、mediumblob和longblob。它们只是可容纳值的最大长度不同。

有4中text类型：tintext、text、mediumtext和longtext。这些对应4种blob类型，有相同的最大长度和存储需求。

5、MySQL常用命令

创建数据库

语法

create database 数据库名

例子：

mysql> create database dingyi;

MySQL创建表

语法

create tables 表名 (字段名 字段属性, 字段名 字段属性, ...)

创建一个student表

mysql> create table student(    -> stu_id int not null auto_increment,    -> name char(32) not null,    -> age int not null,    -> redister_date date,    -> primary key(stu_id)    -> );

实例分析：
- 如果不想字段为null，可以设置字段属性为not null，在操作数据库是如果输入该字段的数据为null，就会报错
- auto_increment定义为自增的属性，一般用于主键，数值会自动加1。
- primary key关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔。

MySQL插入数据

语法：

insert into table_name (字段1, 字段2, ... 字段n) values (值1, 值2, ... ,值n)；

插入数据

mysql> insert into student (name, age, redister_date) valuues ('dingyi', 18, '1970-01-05');

MySQL查询数据

语法：

select 字段1, 字段2 from 表名 [where 条件] [offset m] [limit n]

• 查询语句中可以使用一个或多个表，表之间使用逗号分隔，病使用where语句来设定查询条件。
• selecr命令可以读取一条或者多条记录。
• 可以使用星号(*)来代替其他字段，select语句会返回表的所有的字段数据
• 可以使用where语句来包含任何条件
• 可以通过offset指定select语句还是查询的数据偏移量。默认情况下偏移量为0。
• 可以使用limit属性来设定返回的记录数
  例子
  “`
  mysql> select * from student limit 3 offset 2;
  +——–+——–+—–+—————+
  | stu_id | name | age | redister_date |
  +——–+——–+—–+—————+
  | 3 | dingyi | 18 | 1970-01-07 |
  | 4 | dingyi | 18 | 1970-01-08 |
  | 5 | dingyi | 18 | 1970-01-09 |
  +——–+——–+—–+—————+
  3 rows in set (0.00 sec)
  这条sql，limit 3表示只显示3条数据， offset 2表示从第三条开始显示

```mysql> select * from student limit 3,1;+--------+--------+-----+---------------+| stu_id | name   | age | redister_date |+--------+--------+-----+---------------+|      4 | dingyi |  18 | 1970-01-08    |+--------+--------+-----+---------------+1 row in set (0.00 sec)这条SQL表示 从第三条开始读取，只读取1条信息

mysql> select * from student limit 3;+--------+--------+-----+---------------+| stu_id | name   | age | redister_date |+--------+--------+-----+---------------+|      1 | dingyi |  18 | 1970-01-05    ||      2 | dingyi |  18 | 1970-01-06    ||      3 | dingyi |  18 | 1970-01-07    |+--------+--------+-----+---------------+3 rows in set (0.00 sec)这条SQL表示读取前三条信息

MySQL where字句

语法

select 字段1, 字段2, ... , 字段n from 表1, 表2, ... [where 条件1 [and [or] 条件2]]

下列为操作度表，可以用于where子句中。
下列实例中，设定A = 10, b = 20

操作符:=
描述： 等号，检测两个值是否相等，如果相等返回true
实例： (A = B) 返回false

操作符:<>, !=
描述：不等于，检测两个值是否相等，如果不相等返回true
实例： (A != B) 返回 true。

操作符:>
描述：大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true
实例：(A > B) 返回false。

操作符:<
描述：小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true
实例： (A < B) 返回 true。

操作符:>=
描述：大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true
实例： (A >= B) 返回false。

操作符:<=
描述：小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true
实例：A <= B) 返回 true。

使用主键来作为where子句的条件查询是非常快的。

select * from student where redister_date > '2017-08-15';

MySQL update查询

update 表名 set 字段名1=新值1, 字段名2=新值2 [where 条件]

update student set age=22, name='dingyi' where stu_id > 3;

MySQL delete语句

delete from 表名 [where 条件]

delete from  student where stu_id=5;

MySQL like

语法

select 字段1, 字段2 from 表名1, 表名2 where 字段1 like 条件1 [and [or]] 条件2;

select * from student where name like "%ding%";

MySQL 排序

语法

select 字段1, 字段2 from 表名1, 表名2 where 条件1 and/or 条件2 order by 字段 [desc];desc 表示倒序默认是升序

select * from student where name like "ding%" order by stu_id desc;

MySQL group by

select 字段1, 字段2 from 表名1, 表名2 where 条件1 and/or 条件2 group by 字段 [desc];

mysql> select * from student;+--------+--------+-----+---------------+| stu_id | name   | age | redister_date |+--------+--------+-----+---------------+|      1 | ddyy   |  22 | 1970-01-05    ||      2 | ddyy   |  22 | 1970-01-06    ||      3 | dingyi |  18 | 1970-01-07    ||      4 | dingyi |  18 | 1970-01-08    |+--------+--------+-----+---------------+4 rows in set (0.00 sec)下面我们用group by语句，将该表用名字进行分组，并统计每个人有多少条记录：mysql> select name, count(*) from student group by name;+--------+----------+| name   | count(*) |+--------+----------+| ddyy   |        2 || dingyi |        2 |+--------+----------+2 rows in set (0.05 sec)with rollup：统计汇总mysql> select name,sum(age) as aage from student group by name with rollup;+--------+------+| name   | aage |+--------+------+| ddyy   |   44 || dingyi |   36 || NULL   |   80 |+--------+------+3 rows in set (0.00 sec)其中NULL表示上面两个值得和可以使用coalesce来这是NULL的显示mysql> select coalesce(name,'sum'),sum(age) as aage from student group by name with rollup;+----------------------+------+| coalesce(name,'sum') | aage |+----------------------+------+| ddyy                 |   44 || dingyi               |   36 || sum                  |   80 |+----------------------+------+3 rows in set (0.00 sec)

MySQL alter命令

用于修改表名或者表结构

删除，添加或修改表字段

alter table student drop register_date;从student表中删除register_date字段alter table add phone int(11) not null;添加phone字段

修改字段类型及名称
如果需要修改字段类型及名称, 你可以在alter命令中使用 modify 或 change 子句 。
例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:

mysql> alter table student modify name char(100);Query OK, 4 rows affected (0.11 sec)Records: 4  Duplicates: 0  Warnings: 0

使用 change 子句, 语法有很大的不同。 在 change 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：

mysql> alter table student change name name_2 char(80);mysql> alter table student change age age_new bigint;

ALTER TABLE 对 Null 值和默认值的影响
当你修改字段时，你可以指定是否包含值或者是否设置默认值。
以下实例，指定字段 age_new 为 NOT NULL 且默认值为100 。

mysql> alter table student modify age_new int not null default 100;Query OK, 4 rows affected (0.03 sec)Records: 4  Duplicates: 0  Warnings: 0

修改表名

mysql> alter table student rename to student_new;Query OK, 0 rows affected (0.01 sec)

关于主键

外键，一个特殊的索引，用于关键2个表，只能是指定内容

mysql> create table class(    -> id int not null primary key,    -> name char(16)    -> );Query OK, 0 rows affected (0.01 sec)mysql> create table student2 (    -> id int(11) not null,    -> name char(16) not null,    -> class_id int(11) not null,    -> primary key(id),    -> key fk_class_key(class_id),    #这只外检约束    -> constraint fk_class_key foreign key (class_id) references class (id)    -> );此时如果class 表中不存在id 1,student2表也插入不了，这就叫外键约束mysql> insert into student2 (id,name,class_id) values (1,'ddy',1);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dingyi`.`student2`, CONSTRAINT `fk_class_key` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))mysql> insert into class(id, name) values(1,'python')    -> ;Query OK, 1 row affected (0.00 sec)mysql> insert into student2(id, name, class_id) values(1, 'ddy', 1);Query OK, 1 row affected (0.00 sec)＃如果有student表中跟这个class表有关联的数据，你是不能删除class表中与其关联的纪录的mysql> delete from class where id=1;ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`dingyi`.`student2`, CONSTRAINT `fk_class_key` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))

MySQL NULL 值处理

我们已经知道MySQL使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。
为了处理这种情况，MySQL提供了三大运算符:
- IS NULL: 当列的值是NULL,此运算符返回true。
- IS NOT NULL: 当列的值不为NULL, 运算符返回true。
- <=>: 比较操作符（不同于=运算符），当比较的的两个值为NULL时返回true。
关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。
在MySQL中，NULL值与任何其它值的比较（即使是NULL）永远返回false，即 NULL = NULL 返回false 。
MySQL中处理NULL使用IS NULL和IS NOT NULL运算符。

Mysql 连接（left join, right join, inner join ,full join）

如果在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。
MySQL的join在两个或多个表中查询数据。
在mysql中可以在select,update和delete语句中使用Mysql的join来联合多表查询。
join按照功能大致分为如下三类：
- inner join（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
- left join（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
- right join（右连接）：与left join相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

例子
有如下两张表

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

mysql> select * from b;+------+| b    |+------+|    3 ||    4 ||    5 ||    6 |+------+4 rows in set (0.00 sec)

内关联 inner join
显示两个表的交集

mysql> select * from a inner join b on a.a=b.b;+------+------+| a    | b    |+------+------+|    3 |    3 ||    4 |    4 |+------+------+2 rows in set (0.00 sec)mysql> select a.* ,b.* from a, b where a.a = b.b;+------+------+| a    | b    |+------+------+|    3 |    3 ||    4 |    4 |+------+------+2 rows in set (0.00 sec)

左关联 left join
获取左表所有记录，即使右表没有对应匹配的记录

mysql> select * from a left join b on a.a = b.b;+------+------+| a    | b    |+------+------+|    3 |    3 ||    4 |    4 ||    1 | NULL ||    2 | NULL |+------+------+4 rows in set (0.00 sec)

右关联 right join
用于获取右表所有记录，即使左表没有对应匹配的记录

mysql> select * from a right join b on a.a = b.b;+------+------+| a    | b    |+------+------+|    3 |    3 ||    4 |    4 || NULL |    5 || NULL |    6 |+------+------+4 rows in set (0.00 sec)

两边都关联 full join
同时实现左关联与右关联,但是Mysql不支持full join

select * from a FULL JOIN b on a.a = b.b; a   |  b-----+-----   1 | null   2 | null   3 |    3   4 |    4null |    6null |    5下面的语句可以达到同样的效果mysql> select * from a left join b on a.a=b.b union select * from a right join b on a.a=b.b;+------+------+| a    | b    |+------+------+|    3 |    3 ||    4 |    4 ||    1 | NULL ||    2 | NULL || NULL |    5 || NULL |    6 |+------+------+6 rows in set (0.00 sec)

6、事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
- 在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
- 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行
- 事务用来管理insert,update,delete语句

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

在Mysql控制台使用事务来操作

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> insert into a(a) values(555);Query OK, 1 row affected (0.04 sec)mysql> select * from a;+------+| a    |+------+|    1 ||    2 ||    3 ||    4 ||  555 |+------+5 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 |+------+4 rows in set (0.00 sec)当然如果上面的数据没问题，就输入commit提交命令就行；

7、索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。
创建索引时，需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

普通索引

创建索引

create index 索引名 on 表名(字段名(长度))

mysql> create table people(    -> name char(10) not null,    -> age int(10) not null,    -> number char(10) not null    -> );mysql> create index p_index on people(number(10));Query OK, 0 rows affected (0.07 sec)Records: 0  Duplicates: 0  Warnings: 0

如果是CHAR，VARCHAR类型，长度可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定长度。

创建表的时候直接指定

mysql> create table people_3(    -> id int not null,    -> name varchar(10) not null,    -> index 3_index (id)    -> );Query OK, 0 rows affected (0.01 sec)

删除索引

mysql> create table people_3(    -> id int not null,    -> name varchar(10) not null,    -> index 3_index (id)    -> );Query OK, 0 rows affected (0.01 sec)

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
创建索引

创建索引create unique index 索引名 on 表名(字段名(长度))mysql> create unique index 4_index on people_4(name(10));Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0创建表的时候直接指定：mysql> create table people_5(    -> id int not null,    -> name varchar(16) not null,    -> unique 5_index(name(16))    -> );Query OK, 0 rows affected (0.01 sec)

使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：ALTER TABLE 表名 ADD PRIMARY KEY (字段名);该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。ALTER TABLE 表名 ADD UNIQUE index_name (字段名);这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。ALTER TABLE 表名 ADD INDEX index_name (字段名)添加普通索引，索引值可出现多次。ALTER TABLE 表名 ADD FULLTEXT index_name (字段名)该语句指定了索引为 FULLTEXT ，用于全文索引。

mysql> alter table people_4 add unique 4_2_index(number);Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> alter table people add index n_index(name(10));Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0

使用 ALTER 命令添加和删除主键

主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);你也可以使用 ALTER 命令删除主键：mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;删除指定时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

显示索引信息

mysql> SHOW INDEX FROM table_name\G

pymysql

安装

pip3 install pymysql

使用操作

1、执行sql

#!/usr/bin/env python3import pymysql#创建连接conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='dingyi')#创建游标cursor = conn.cursor()#执行sql，并返回影响的行数effect_row = cursor.execute("update student_new set name_2 = 'lala' where redister_date = '1970-01-05'")effect_row = cursor.execute("update student_new set name_2 = %s where redister_date = '1970-01-06'" ,('haha',))effect_row = cursor.executemany("insert into student_new(stu_id, name_2, age_new, redister_date) values(%s, %s, %s, %s)" , [(5, "d5", 23, "1970-01-08" ), (6, "d6", 44, "1970-01-08"), ])#提交，否则操作无效conn.commit()#关闭游标cursor.close()#关闭连接conn.close()

2、获取新创建数据自增ID

#!/usr/bin/env pyython3import pymysql#创建连接conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='dingyi')#创建游标cursor = conn.cursor()#执行sql返回影响的行数cursor.executemany("insert into student_new(name_2, age_new, redister_date) values(%s, %s, %s)", [('ding2', 22, "2017-09-12"), ('ding3', 244, "1998-09-11")])#提交conn.commit()#关闭游标cursor.close()#关闭连接conn.close()#获取最新自增的学号now_stu_id = cursor.lastrowidprint(now_stu_id)

3、获取查询数据

#!/usr/bin/env python3import pymysql#创建连接conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='dingyi')#创建游标cursor = conn.cursor()#执行SQL返回影响的行数cursor.execute('select * from student_new')#获取第一行row_1 = cursor.fetchone()#for i in row_1:print('row_1:', row_1)print('')#获取前n行row_2 = cursor.fetchmany(3)for i in row_2:    print('row_2:', i)print('')#获取所有的行row_3 = cursor.fetchall()for i in row_3:    print('row_3:', i)print('')#提交conn.commit()#关闭游标cursor.close()#断开连接conn.close()#这的读取是根据指针的位置的，先去了第一行，指针变成了第二行，多以再取前三行的时候，就显示了2,3,4，在获取所有的时候，就从5开始了。dy@dy-uu:~/pymysql_test$ python3 pymysql_test03.py row_1: (1, 'lala', 22, datetime.date(1970, 1, 5))row_2: (2, 'haha', 22, datetime.date(1970, 1, 6))row_2: (3, 'dingyi', 18, datetime.date(1970, 1, 7))row_2: (4, 'dingyi', 18, datetime.date(1970, 1, 8))row_3: (5, 'd5', 23, datetime.date(1970, 1, 8))row_3: (6, 'd6', 44, datetime.date(1970, 1, 8))row_3: (7, 'ding2', 22, datetime.date(2017, 9, 12))row_3: (8, 'ding3', 244, datetime.date(1998, 9, 11))

注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：
- cursor.scroll(1,mode=’relative’) # 相对当前位置移动
- cursor.scroll(2,mode=’absolute’) # 相对绝对位置移动

4、fetch数据类型

#!/usr/bin/env pyton3import pymysql#创建连接conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='dingyi')#游标设置为字典类型cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)cursor.execute('select * from student_new')r = cursor.fetchone()print(r)conn.commit()cursor.close()conn.close()dy@dy-uu:~/pymysql_test$ python3  pymysql_test04.py{'age_new': 22, 'name_2': 'lala', 'redister_date': datetime.date(1970, 1, 5), 'stu_id': 1}