8 MySQL数据库
8.1 初识数据库
8.1.1 数据库管理软件的由来
我们在编写任何程序之前，都需要事先写好基于网络操作一台主机上文件的程序（socket服务端与客户端程序），于是有人将此类程序写成一个
专门的处理软件，这就是mysql等数据库管理软件的由来，但mysql解决的不仅仅是数据共享的问题，还有查询效率，安全性等一系列问题，
总之，把程序员从数据管理中解脱出来，专注于自己的程序逻辑的编写。
8.1.2 数据库概述
1 什么是数据（Data）
描述事物的符号记录称为数据，描述事物的符号既可以是数字，也可以是文字、图片，图像、声音、语言等，数据由多种表现形式，它们都可以经过数字化后存入计算机
2 什么是数据库（DataBase，简称DB）
数据库即存放数据的仓库，只不过这个仓库是在计算机存储设备上，而且数据是按一定的格式存放的
数据库是长期存放在计算机内、有组织、可共享的数据即可。
数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种用户共享
3 什么是数据库管理系统（DataBase Management System 简称DBMS）
在了解了Data与DB的概念后，如何科学地组织和存储数据，如何高效获取和维护数据成了关键这就用到了一个系统软件---数据库管理系统
4 数据库服务器、数据管理系统、数据库、表与记录的关系（重点理解！！！）
8.1.3 MySQL安装与基本管理
一、MySQL介绍
mysql是什么
mysql就是一个基于socket编写的C/S架构的软件
客户端软件
mysql自带：如mysql命令，mysqldump命令等
python模块：如pymysql
数据库管理软件分类
分两大类：
关系型：如sqllite，db2，oracle，access，sql server，MySQL，注意：sql语句通用
非关系型：mongodb，redis，memcache
可以简单的理解为：
关系型数据库需要有表结构
非关系型数据库是key-value存储的，没有表结构
二、下载安装
三、MySQL启动与查看
四、登录设置密码
五、破解密码
六、统一字符编码
8.1.4 初识sql语句
SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型：
1、DDL语句数据库定义语言：数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
2、DML语句数据库操纵语言：插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
3、DCL语句数据库控制语言：例如控制用户的访问权限GRANT、REVOKE
#1.操作文件夹
增：create database db1 charset utf8;
查：show databases;
改：alter database db1 charset latin1;
删除: drop database db1;
#2.操作文件
先切换到文件夹下：use db1
增：create table t1(id int,name char);
查：show tables
改：alter table t1 modify name char(3);
alter table t1 change name name1 char(2);删：drop table t1;
#3.操作文件中的内容/记录
增：insert into t1 values(1,'egon1'),(2,'egon2'),(3,'egon3');
查：select * from t1;
改：update t1 set name='sb' where id=2;
删：delete from t1 where id=1;
8.2库操作
8.2.1 库的增删改查
一 系统数据库
information_schema： 虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等
performance_schema： MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象
mysql： 授权库，主要存储系统用户的权限信息
test： MySQL数据库系统自动创建的测试数据库
二 创建数据库
1 语法(help create database)
CREATE DATABASE数据库名charset utf8;
2 数据库命名规则：
可以由字母、数字、下划线、＠、＃、＄
区分大小写
唯一性
不能使用关键字如create select
不能单独使用数字
最长128位
三 数据库相关操作
查看数据库
show databases;show create database db1;select database();选择数据库
USE数据库名
删除数据库
DROP DATABASE数据库名;
修改数据库
alter database db1 charset utf8;8.3表的操作
8.3.1 存储引擎介绍
一 什么是存储引擎
数据库中的表也应该有不同的类型，表的类型不同，会对应mysql不同的存取机制，表类型又称为存储引擎。
存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）
二 mysql支持的存储引擎
1、 InnoDB 存储引擎
2、 MyISAM 存储引擎
3、 NDB 存储引擎
三 使用存储引擎
方法1：建表时指定
MariaDB [db1]> create table innodb_t1(id int,name char)engine=innodb;MariaDB [db1]> create table innodb_t2(id int)engine=innodb;MariaDB [db1]> show create table innodb_t1;MariaDB [db1]> show create table innodb_t2;方法2：在配置文件中指定默认的存储引擎
/etc/my.cnf[mysqld]default-storage-engine=INNODBinnodb_file_per_table=1查看
[root@egon db1]# cd /var/lib/mysql/db1/[root@egon db1]# lsdb.opt innodb_t1.frm innodb_t1.ibd innodb_t2.frm innodb_t2.ibd8.3.2 表的增删改查
一、表介绍
表相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段
二、创建表
create table表名(
字段名1类型[(宽度)约束条件],
字段名2类型[(宽度)约束条件],
字段名3类型[(宽度)约束条件]
); #注意：
1.在同一张表中，字段名是不能相同
2.宽度和约束条件可选
3.字段名和类型是必须的
三、查看表结构
MariaDB [db1]> describe t1; #查看表结构，可简写为desc表名
+-------+-----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-----------------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || name | varchar(50) | YES | | NULL | || sex | enum('male','female') | YES | | NULL | || age | int(3) | YES | | NULL | |+-------+-----------------------+------+-----+---------+-------+ MariaDB [db1]> show create table t1G; #查看表详细结构，可加G
四、修改表结构
语法：
1.修改表名
ALTER TABLE表名
RENAME新表名;
2.增加字段
ALTER TABLE表名
ADD字段名数据类型[完整性约束条件…],
ADD字段名数据类型[完整性约束条件…];
ALTER TABLE表名
ADD字段名数据类型[完整性约束条件…] FIRST;
ALTER TABLE表名
ADD字段名数据类型[完整性约束条件…] AFTER字段名;
3.删除字段
ALTER TABLE表名
DROP字段名;
4.修改字段
ALTER TABLE表名
MODIFY字段名数据类型[完整性约束条件…];
ALTER TABLE表名
CHANGE旧字段名新字段名旧数据类型[完整性约束条件…];
ALTER TABLE表名
CHANGE旧字段名新字段名新数据类型[完整性约束条件…];
五、复制表
复制表结构＋记录（key不会复制:主键、外键和索引）
mysql> create table new_service select * from service; 只复制表结构
mysql> select * from service where 1=2; //条件为假，查不到任何记录
Empty set (0.00 sec)mysql> create table new1_service select * from service where 1=2; Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0 mysql> create table t4 like employees;六、删除表
DROP TABLE表名;
8.3.3 数据类型
mysql常用数据类型概览
#1.数字：
整型：tinyinit int bigint
小数：
float：在位数比较短的情况下不精准
double：在位数比较长的情况下不精准
0.000001230123123123存成：0.000001230000
decimal：（如果用小数，则用推荐使用decimal）
精准
内部原理是以字符串形式去存
#2.字符串：
char（10）：简单粗暴，浪费空间，存取速度快
root存成root000000
varchar：精准，节省空间，存取速度慢
sql优化：创建表时，定长的类型往前放，变长的往后放
比如性别比如地址或描述信息
>255个字符，超了就把文件路径存放到数据库中。
比如图片，视频等找一个文件服务器，数据库中只存路径或url。
#3.时间类型：
最常用：datetime
#4.枚举类型与集合类型
数值类型
1、整数类型
2、浮点型
定点数类型 DEC等同于DECIMAL
浮点类型：FLOAT DOUBLE
日期类型
============注意啦，注意啦，注意啦===========
1.单独插入时间时，需要以字符串的形式，按照对应的格式插入
2.插入年份时，尽量使用4位值
3.插入两位年份时，<=69，以20开头，比如50,结果2050
>=70，以19开头，比如71，结果1971
datetime与timestamp的区别
在实际应用的很多场景中，MySQL的这两种日期类型都能够满足我们的需要，存储精度都为秒，但在某些情况下，会展现出他们各自的优劣。
下面就来总结一下两种日期类型的区别。
1.DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。
2.DATETIME存储时间与时区无关，TIMESTAMP存储时间与时区有关，显示的值也依赖于时区。在mysql服务器，
操作系统以及客户端连接都有时区的设置。
3.DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。
4.DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP），
如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。
字符类型
#常用字符串系列：char与varchar
注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡
#其他字符串系列（效率：char>varchar>text）
TEXT系列TINYTEXT TEXT MEDIUMTEXT LONGTEXT
BLOB系列TINYBLOB BLOB MEDIUMBLOB LONGBLOB
BINARY系列BINARY VARBINARY
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 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female
set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）
8.3.4 完整性约束
一、介绍
约束条件与数据类型的宽度一样，都是可选参数
作用：用于保证数据的完整性和一致性
主要分为：
PRIMARY KEY (PK)标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK)标识该字段为该表的外键
NOT NULL标识该字段不能为空
UNIQUE KEY (UK)标识该字段的值是唯一的
AUTO_INCREMENT标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT为该字段设置默认值
UNSIGNED无符号
ZEROFILL使用0填充
说明：
1.是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
2.字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male'age int unsigned NOT NULL default 20必须为正值（无符号）不允许为空默认是20
3.是否是key
主键primary key
外键foreign key
索引(index,unique...)
二、not null与default
三、 unique
四、 primary key
五、 auto_increment
六、 foreign key
如何找出两张表之间的关系
分析步骤：
#1、先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key右表一个字段（通常是id）
#2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key左表一个字段（通常是id）
#3、总结：
#多对一：
如果只有步骤1成立，则是左表多对一右表
如果只有步骤2成立，则是右表多对一左表
#多对多
如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系
#一对一:
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
8.4 数据操作
8.4.1 数据的增删改
一 介绍
MySQL数据操作： DML
========================================================
在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括
- 使用INSERT实现数据的插入
- UPDATE实现数据的更新
- 使用DELETE实现数据的删除
- 使用SELECT查询数据以及。
二 插入数据INSERT
1.插入完整数据（顺序插入）
语法一：
INSERT INTO表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);
语法二：
INSERT INTO表名VALUES (值1,值2,值3…值n);
2.指定字段插入数据
语法：
INSERT INTO表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);
3.插入多条记录
语法：
INSERT INTO表名VALUES
(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);
4.插入查询结果
语法：
INSERT INTO表名(字段1,字段2,字段3…字段n)
SELECT (字段1,字段2,字段3…字段n) FROM表2
WHERE …;三 更新数据UPDATE
语法：
UPDATE表名SET
字段1=值1,
字段2=值2,
WHERE CONDITION; 示例：
UPDATE mysql.user SET password=password(‘123’) where user=’root’ and host=’localhost’;四 删除数据DELETE
语法：
DELETE FROM表名
WHERE CONITION; 示例：
DELETE FROM mysql.user WHERE password=’’;五 权限管理
#授权表
user #该表放行的权限，针对：所有数据，所有库下所有表，以及表下的所有字段
db #该表放行的权限，针对：某一数据库，该数据库下的所有表，以及表下的所有字段
tables_priv #该表放行的权限。针对：某一张表，以及该表下的所有字段
columns_priv #该表放行的权限，针对：某一个字段
#创建用户
create user 'egon'@'1.1.1.1' identified by '123';
create user 'egon'@'192.168.1.%' identified by '123';
create user 'egon'@'%' identified by '123';
#授权：对文件夹，对文件，对文件某一字段的权限
查看帮助：help grant
常用权限有：select,update,alter,delete
all可以代表除了grant之外的所有权限
#针对所有库的授权:*.*
grant select on *.* to 'egon1'@'localhost' identified by '123'; #只在user表中可以查到egon1用户的select权限被设置为Y
#针对某一数据库：db1.*
grant select on db1.* to 'egon2'@'%' identified by '123'; #只在db表中可以查到egon2用户的select权限被设置为Y
#针对某一个表：db1.t1
grant select on db1.t1 to 'egon3'@'%' identified by '123'; #只在tables_priv表中可以查到egon3用户的select权限
#针对某一个字段：
mysql> select * from t3;
#可以在tables_priv和columns_priv中看到相应的权限
mysql> select * from tables_priv where user='egon4'G#删除权限
revoke select on db1.* to 'alex'@'%';8.4.2 单表查询
一 单表查询的语法
SELECT字段1,字段2... FROM表名
WHERE条件
GROUP BY fieldHAVING筛选
ORDER BY fieldLIMIT限制条数
二 关键字的执行优先级(重点)
重点中的重点：关键字的执行优先级
fromwheregroup byhavingselectdistinctorder bylimit1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.去重
7.将结果按条件排序：order by
8.限制结果的显示条数
三 简单查询
#简单查询
SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id FROM employee; SELECT * FROM employee; SELECT name,salary FROM employee;#避免重复DISTINCT
SELECT DISTINCT post FROM employee; #通过四则运算查询
SELECT name, salary*12 FROM employee; SELECT name, salary*12 AS Annual_salary FROM employee; SELECT name, salary*12 Annual_salary FROM employee;#定义显示格式
CONCAT()函数用于连接字符串
SELECT CONCAT('姓名: ',name,'年薪: ', salary*12) AS Annual_salary
FROM employee;CONCAT_WS()第一个参数为分隔符
SELECT CONCAT_WS(':',name,salary*12) AS Annual_salary FROM employee;四 WHERE约束
where字句中可以使用：
- 比较运算符：><>= <= <> !=
- between 80 and 100 值在10到20之间
- in(80,90,100) 值是10或20或30
- like 'egon%'
pattern可以是%或_，
%表示任意多字符
_表示一个字符 - 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
五 分组查询:GROUP BY
小窍门：‘每’这个字后面的字段，就是我们分组的依据
聚合函数
#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组
六 HAVING过滤
HAVING与WHERE不一样的地方在于!!!!!!
#！！！执行优先级从高到低：where > group by > having
#1. Where发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
七查询排序:ORDER BY
按单列排序
SELECT * FROM employee ORDER BY salary;
SELECT * FROM employee ORDER BY salary ASC;
SELECT * FROM employee ORDER BY salary DESC;
按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
SELECT * from employee
ORDER BY age,
salary DESC;
八 限制查询的记录数:LIMIT
示例：
SELECT * FROM employee ORDER BY salary DESC LIMIT 3; #默认初始位置为0
SELECT * FROM employee ORDER BY salary DESCLIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条
SELECT * FROM employee ORDER BY salary DESCLIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
九 使用正则表达式查询
SELECT * FROM employee WHERE name REGEXP '^ale'; SELECT * FROM employee WHERE name REGEXP 'on$'; SELECT * FROM employee WHERE name REGEXP 'm{2}'; 小结：对字符串匹配的方式
WHERE name = 'egon';WHERE name LIKE 'yua%';WHERE name REGEXP 'on$';8.4.3 多表查询
一 多表连接查询
#重点：外链接语法
SELECT字段列表
FROM表1 INNER|LEFT|RIGHT JOIN表2
ON表1.字段=表2.字段;
1 带IN关键字的子查询
2 带比较运算符的子查询
#比较运算符：=、!=、>、>=、<、<=、<>
3 带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
8.5 Navicat工具与pymysql模块
8.5.1 图形工具Navicat
8.5.2 pymysql模块
一 介绍
pip3 install pymysql二 链接、执行sql、关闭（游标）
import pymysqluser=input('用户名: ').strip()
pwd=input('密码: ').strip()
#链接
conn=pymysql.connect(host='localhost',user='root',password='123',database='egon',charset='utf8')#游标
cursor=conn.cursor() #执行完毕返回的结果集默认以元组显示
#cursor=conn.cursor(cursor=pymysql.cursors.DictCursor) #执行sql语句
sql='select * from userinfo where name="%s" and password="%s"' %(user,pwd) #注意%s需要加引号
print(sql)res=cursor.execute(sql) #执行sql语句，返回sql查询成功的记录数目
print(res) cursor.close()conn.close() if res:print('登录成功')
else:print('登录失败')
三 execute()之sql注入
解决方法：
#原来是我们对sql进行字符串拼接
# sql="select * from userinfo where name='%s' and password='%s'" %(user,pwd)# print(sql)# res=cursor.execute(sql) #改写为（execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号了）
sql="select * from userinfo where name=%s and password=%s" #！！！注意%s需要去掉引号，因为pymysql会自动为我们加上
res=cursor.execute(sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题，只要我们按照pymysql的规矩来。
四 查：fetchone，fetchmany，fetchall
五 获取插入的最后一条数据的自增ID
import pymysqlconn=pymysql.connect(host='localhost',user='root',password='123',database='egon')cursor=conn.cursor() sql='insert into userinfo(name,password) values("xxx","123");'rows=cursor.execute(sql)print(cursor.lastrowid) #在插入语句后查看
conn.commit()cursor.close()conn.close()8.6 mysql内置功能
8.6.1 试图
一 视图
视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，可以将该结果集当做表来使用。
二 使用视图
mysql> update course_view set cname='xxx'; #更新视图中的数据
mysql> select * from course; #发现原始表的记录也跟着修改了
我们不应该修改视图中的记录，而且在涉及多个表的情况下是根本无法修改视图中的记录的
三 修改视图
语法：ALTER VIEW视图名称AS SQL语句
mysql> alter view teacher_view as select * from course where cid>3;Query OK, 0 rows affected (0.04 sec) mysql> select * from teacher_view;+-----+-------+------------+| cid | cname | teacher_id |+-----+-------+------------+| 4 | xxx | 2 || 5 | yyy | 2 |+-----+-------+------------+
rows in set (0.00 sec)四 删除视图
语法：DROP VIEW视图名称
DROP VIEW teacher_view8.6.2 触发器
一 触发器
使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询
一创建触发器
#插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROWBEGIN ...END #插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROWBEGIN ...END #删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROWBEGIN ...END #删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROWBEGIN ...END #更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROWBEGIN ...END #更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROWBEGIN ...END二使用触发器
触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的。
三删除触发器
drop trigger tri_after_insert_cmd;8.6.3 事物
事务的特性：
事务有以下四个标准属性的缩写ACID，通常被称为：
原子性: 确保工作单元内的所有操作都成功完成，否则事务将被中止在故障点，和以前的操作将回滚到以前的状态。
一致性: 确保数据库正确地改变状态后，成功提交的事务。
隔离性: 使事务操作彼此独立的和透明的。
持久性: 确保提交的事务的结果或效果的系统出现故障的情况下仍然存在。
8.6.4 存储过程
一存储过程介绍
存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql
使用存储过程的优点：
#1.用于替代程序写的SQL语句，实现程序与sql解耦
#2.基于网络传输，传别名的数据量小，而直接传sql数据量大
使用存储过程的缺点：
#1.程序员扩展功能不方便
补充：程序与数据库结合使用的三种方式
#方式一：
MySQL：存储过程
程序：调用存储过程
#方式二：
MySQL：
程序：纯SQL语句
#方式三：
MySQL:程序：类和对象，即ORM（本质还是纯SQL语句）
二创建简单存储过程（无参）
delimiter //create procedure p1()BEGIN select * from blog; INSERT into blog(name,sub_time) values("xxx",now());END //delimiter ; #在mysql中调用
call p1() #在python中基于pymysql调用
cursor.callproc('p1') print(cursor.fetchall())三创建存储过程（有参）
对于存储过程，可以接收参数，其参数有三类：
#in仅用于传入参数用
#out仅用于返回值用
#inout既可以传入又可以当作返回值
四执行存储过程
--无参数
call proc_name() --有参数，全in
call proc_name(1,2) --有参数，有in，out，inout
set @t1=0;set @t2=3;call proc_name(1,2,@t1,@t2) 执行存储过程
在MySQL中执行存储过程--无参数
call proc_name() --有参数，全in
call proc_name(1,2) --有参数，有in，out，inout
set @t1=0;set @t2=3;call proc_name(1,2,@t1,@t2) 执行存储过程
在MySQL中执行存储过程
#!/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(cursor=pymysql.cursors.DictCursor)#执行存储过程
cursor.callproc('p1', args=(1, 22, 3, 4))#获取执行完存储的参数
cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")result = cursor.fetchall() conn.commit()cursor.close()conn.close() print(result)在python中基于pymysql执行存储过程
五删除存储过程
drop procedure proc_name;8.6.5 函数
MySQL中提供了许多内置函数，可以直接使用，介绍略
一自定义函数
#！！！注意！！！
#函数中不要写sql语句（否则会报错），函数仅仅只是一个功能，是一个在sql中被应用的功能
#若要想在begin...end...中写sql，请用存储过程
delimiter //create function f1( i1 int, i2 int)returns intBEGIN declare num int; set num = i1 + i2; return(num);END //delimiter ;删除函数
drop function func_name;三执行函数
#获取返回值
select UPPER('egon') into @res;SELECT @res; #在查询中使用
select f1(11,nid) ,name from tb2;8.6.6 流程控制
一 if条件语句
二循环语句
8.7 索引原理与慢查询
8.7.1 索引原理与慢查询（1）
一 介绍
为何要有索引?
一般的应用系统，读写比例在10:1左右，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。
什么是索引？
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。
你是否对索引存在误解？
索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响，要找到一个平衡点，这对应用程序的性能至关重要。
如果知道数据的使用，从一开始就应该在需要处添加索引。
二 索引的原理
一 索引原理
本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
二 磁盘IO与预读
当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内
三 索引的数据结构
###b+树性质 1.索引字段要尽量的小；2.索引的最左匹配特性
四 聚集索引与辅助索引
数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index），
聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。
聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息
1、聚集索引
聚集索引的好处之一：它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录
聚集索引的好处之二：范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可
2、辅助索引
表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引），与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。
叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据。
由于InnoDB存储引擎是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。
辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得只想主键索引的主键，然后再通过主键索引来找到一个完整的行记录。
五 MySQL索引管理
一 功能
#1. 索引的功能就是加速查找
#2. mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能
二 MySQL常用的索引
普通索引INDEX：加速查找
唯一索引：
-主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
-唯一索引UNIQUE:加速查找+约束（不能重复）
联合索引：
-PRIMARY KEY(id,name):联合主键索引
-UNIQUE(id,name):联合唯一索引
-INDEX(id,name):联合普通索引
三 索引的两大类型hash与btree
#我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
#不同的存储引擎支持的索引类型也不一样
InnoDB支持事务，支持行级别锁定，支持B-tree、Full-text等索引，不支持Hash索引；
MyISAM不支持事务，支持表级别锁定，支持B-tree、Full-text等索引，不支持Hash索引；
Memory不支持事务，支持表级别锁定，支持B-tree、Hash等索引，不支持Full-text索引；
NDB支持事务，支持行级别锁定，支持Hash索引，不支持B-tree、Full-text等索引；
Archive不支持事务，支持表级别锁定，不支持B-tree、Hash、Full-text等索引；
四 创建/删除索引的语法
#方法一：创建表时
CREATE TABLE表名(
字段名1数据类型[完整性约束条件…],
字段名2数据类型[完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY[索引名] (字段名[(长度)] [ASC |DESC])
); #方法二：CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX索引名
ON表名(字段名[(长度)] [ASC |DESC]) ;
#方法三：ALTER TABLE在已存在的表上创建索引
ALTER TABLE表名ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名(字段名[(长度)] [ASC |DESC]) ;
#删除索引：DROP INDEX索引名ON表名字;
五 总结
#1.一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加上索引
#2.在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快
比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。
建完以后，再查询就会很快了。
#3.需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI
MySAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在innodb中，表数据文件本身就是按照B+Tree（BTree即Balance True）组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此innodb表数据文件本身就是主索引。
因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型.
七 正确使用索引
一索引未命中
1 范围问题，或者说条件不明确，条件中出现这些符号或关键字：>、>=、<、<=、!= 、between...and...、like、
2 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1
3 =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
4 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)
5 and/or
6 最左前缀匹配原则（详见第八小节），非常重要的原则，对于组合索引mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配(指的是范围大了，有索引速度也慢)，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
7 其他情况
-使用函数
select * from tb1 where reverse(email) = 'egon'; -类型不一致
如果列是字符串类型，传入条件是必须用引号引起来，不然...
select * from tb1 where email = 999; #排序条件为索引，则select字段必须也是索引字段，否则无法命中
- order by select name from s1 order by email desc;当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢
select email from s1 order by email desc;特别的：如果对主键排序，则还是速度很快：
select * from tb1 order by nid desc; -组合索引最左前缀
如果组合索引为：(name,email)
name and email --命中索引
name --命中索引
email --未命中索引
- count(1)或count(列)代替count(*)在mysql中没有差别了
- create index xxxx on tb(title(19)) #text类型，必须制定长度
二 其他注意事项
-避免使用select *
- count(1)或count(列)代替count(*)
-创建表时尽量时char代替varchar
-表的字段顺序固定长度的字段优先
-组合索引代替多个单列索引（经常使用多个条件查询时）
-尽量使用短索引
-使用连接（JOIN）来代替子查询(Sub-Queries)
-连表时注意条件类型需一致
-索引散列值（重复少）不适合建索引，例：性别不适合
八 联合索引与覆盖索引
一 联合索引
联合索引时指对表上的多个列合起来做一个索引。联合索引的创建方法与单个索引的创建方法一样，不同之处在仅在于有多个索引列，如下
mysql> create table t( -> a int, -> b int, -> primary key(a), -> key idx_a_b(a,b) -> );Query OK, 0 rows affected (0.11 sec)但对于b列的查询select * from table where b=xxx,则不可以使用（a,b） 索引，其实你不难发现原因，叶子节点上b的值为1、2、1、4、1、2显然不是排序的，因此对于b列的查询使用不到(a,b) 索引
联合索引的第二个好处是在第一个键相同的情况下，已经对第二个键进行了排序处理，
二 覆盖索引
InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。
使用覆盖索引的一个好处是：辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作
对于（a,b）形式的联合索引，一般是不可以选择b中所谓的查询条件。但如果是统计操作，并且是覆盖索引，则优化器还是会选择使用该索引
九 查询优化神器-explain
关于explain命令相信大家并不陌生，具体用法和字段含义可以参考官网explain-output，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上都是在优化rows。
十 慢查询优化的基本步骤
0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE
1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
3.order by limit形式的sql语句让排序的表优先查
4.了解业务方使用场景
5.加索引时参照建索引的几大原则
6.观察结果，不符合预期继续从0分析