<python模块>pymysql
目录：
操作数据库的有网页版的phpadmin和系统上使用的navicat都是不错的选择。
对于python就可以使用pymysql，当然也有MySQLdb等。
数据库操作
对于操作数据库
显示数据库
SHOW DATABASES;
默认的数据库
- mysql - 用户权限相关数据
- test - 用于用户测试数据
- information_schema - MySQL本身架构相关数据
创建数据库
创建一个utf-8编码的数据库
CREATE DATABASE 数据库名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
创建一个gbk编码的数据库
CREATE DATABASE 数据库名称 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
不过不推荐使用gbk的编码的数据库
使用数据库
USE 数据库名称;
用户管理
创建用户
create user '用户名'@'IP地址' identified by '密码';
删除用户
drop user '用户名'@'IP地址';
修改用户
rename user '用户名'@'IP地址' to '新用户名'@'IP地址';
修改密码
set password for '用户名'@'IP地址' = Password('新密码');
用户权限其实保存在mysql数据库的user表中，也可以直接对其仅从操作，不过，不建议这么做。
授权管理
查看权限
show grants for '用户'@'IP地址';
授权管理
grant 权限 on 数据库.表 to '用户'@'IP地址';
取消权限
revoke 权限 on 数据库.表 from '用户'@'IP地址';
权限种类
all privileges 除grant外的所有权限
select 仅查权限
select,insert 查和插入权限
等等一系列操作...
usage 无访问权限
alter 使用alter table
alter routine 使用alter procedure和drop procedure
create 使用create table
create routine 使用create procedure
create temporary tables 使用create temporary tables
create user 使用create user、drop user、rename user和revoke all privileges
create view 使用create view
delete 使用delete
drop 使用drop table
execute 使用call和存储过程
file 使用select into outfile 和 load data infile
grant option 使用grant 和 revoke
index 使用index
insert 使用insert
lock tables 使用lock table
process 使用show full processlist
select 使用select
show databases 使用show databases
show view 使用show view
update 使用update
reload 使用flush
shutdown 使用mysqladmin shutdown(关闭MySQL)
super 使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆
replication client 服务器位置的访问
replication slave 由复制从属使用
对于权限
对于匹配数据库
数据库名.* 数据库中的所有
数据库名.表 指定数据库中的某张表
数据库名.存储过程 指定数据库中的存储过程
*.* 所有数据库
对于用户名和IP
用户名@IP地址 用户只能在改IP下才能访问
用户名@192.168.1.% 用户只能在改IP段下才能访问(通配符%表示任意)
用户名@% 用户可以再任意IP下访问(默认IP地址为%)
立即生效
原理是将数据库中数据刷新到内存中，对于新添加的用户和授权就不需要进行该操作。
flush privileges
忘记mysql密码
启动免授权服务端
mysqld --skip-grant-tables
客户端
mysql -u root -p
修改密码
update mysql.user set authentication_string=password('123456') where user='root';
flush privileges;
对于表的操作
创建表
create table 表名(
列名 类型 是否可以为空，
列名 类型 是否可以为空
)ENGINE=InnoDB DEFAULT CHARSET=utf8
是否可为空，not null和null两种
创建表时设置默认值
如果未插入数据的时候自动添加默认值
create table tb1(
nid int not null defalut 2,
num int not null
)
创建表时设置自增
设置自增后，插入数据的时候不需要对此列进行插入，默认进行自增。
create table tb1(
nid int not null auto_increment primary key,
num int null
)
或
create table tb1(
nid int not null auto_increment,
num int null,
index(nid)
)
表中只能有一列为自增，并且自增列必须是索引。
查看自增起始值和步长
查看的时候有三种
show variables like '%auto_inc%';
show session variables like '%auto_inc%'; -- //session会话变量
show global variables like '%auto_inc%'; -- //全局变量
设置自增起始值和步长
set session auto_increment_increment=2;
set session auto_increment_offset=10;
主键
主键是一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则值必须唯一，如果多列，则其组合必须唯一
create table tb1(
nid int not null auto_increment primary key,
num int null
)
或
create table tb1(
nid int not null,
num int not null,
primary key(nid,num)
)
外键
外键是一个特殊的索引，字段只能是指定内容
creat table color(
nid int not null primary key,
name char(16) not null
)
create table fruit(
nid int not null primary key,
smt char(32) null ,
color_id int not null,
constraint fk_cc foreign key (color_id) references color(nid)
)
这样就创建了一个外键fk_cc，用于fruit表的color_id字段，约束该字段的为color表的nid字段，这样在fruit表中插入数据的时候color_id必须要在color表的id字段中有对应的值。
alter table fruit add constraint fk_cc foreign key (color_id) references color(nid);
外键的主要作用于分表然后建立一对多或多对多的情况使用。
例如上述color字段可能只有几种，而fruit会有很多，每一个都对应一个color，这就是一对多。
对于多对多，例如错乱的男女关系，这时候就需要三个表，men，women和men_and_women
men表有name和mid，而women表有name和wid，而men_and_women表有mid和wid，即表示男和女的关系，mid外键为men表的mid，wid外键为women表的wid。
查询的时候就可以使用
select
men.name as man,
women.name as woman,
from men_and_women
left join men on men_to_women.mid = men.mid
left join women on men_to_women.wid = women.wid
where men.name = 'why'
把需要的表中的字段列出来，起一个别名展示
删除表
drop table 表名
清空表
delete from 表名
truncate table 表名
修改表
添加列：alter table 表名 add 列名 类型
删除列：alter table 表名 drop column 列名
修改列：
alter table 表名 modify column 列名 类型; -- 类型
alter table 表名 change 原列名 新列名 类型; -- 列名，类型
添加主键：
alter table 表名 add primary key(列名);
删除主键：
alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop primary key;
添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表）foreign key 从表(外键字段) references 主表(主键字段);
删除外键：alter table 表名 drop foreign key 外键名称
修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
数据类型
数字
bit
- bit(m)
二进制位，m表示二进制长度(1~64)，默认为1
tiyint
- tiyint
小整数，有符号范围为-128~127，无符号范围为0~255
对于mysql没有布尔值，一般都通过tiyint(1)来进行构造，示例0Flase，1Ture
int
- int(m)
整数，有符号范围为-2147483648 ～ 2147483647，无符号范围为0 ～ 4294967295
整数类型中m仅用于显示，对存储范围无影响，例如int(4)中，插入1时，select到的数据为0001
bigint
- bigint(m)
整数，有符号范围为-9223372036854775808 ～ 9223372036854775807，无符号范围为0 ～ 18446744073709551615
decimal
- decimal(m[,d])
准确的小数，m是总字数，d是小数点后位数，m最大为65，d最大为30
对于非常精确的计算可以使用此类型
float
- float(m,d)
单精度浮点型(非准确小数值)，m是数字总个数，d是小数点后位数
double
- double(m,d)
双精度浮点数（非准确小数值），m是数字总个数，d是小数点后位数。
float和double的区别
- 在内存中占有的字节数不同，单精度浮点数在机内占4个字节，双精度浮点数在机内占8个字节
- 有效数字位数不同，单精度浮点数有效数字8位，双精度浮点数有效数字16位
- 所能表示数的范围不同，单精度浮点的表示范围：-3.40E+38 ~ +3.40E+38，双精度浮点的表示范围：-1.79E+308 ~ +1.79E+308
- 在程序中处理速度不同一般来说，CPU处理单精度浮点数的速度比处理双精度浮点数快
字符串
char
- char(m)
char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度。即使数据小于m长度，也会占用m长度
varchar
- varchar(m)
varchars数据类型用于变长的字符串，可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中
虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡
text
text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。
mediumtext
mediumtext数据类型用于保存变长的大字符串，可以组多16,777,215 (2**24 − 1)个字符。
longtext
longtext数据类型用于保存变长的大字符串，可以组多4,294,967,295 or 4GB (2**32 − 1)个字符。
时间
DATE
YYYY-MM-DD（1000-01-01/9999-12-31）
TIME
HH:MM:SS（'-838:59:59'/'838:59:59'）
YEAR
YYYY（1901/2155）
DATETIME
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
TIMESTAMP
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
存储二进制
TinyBlob、Blob、MediumBlob、LongBlob
例如图片就可以进行存储，不过我们更习惯将图片存储在磁盘上，然后通过存储路径进行调取
枚举
- enum
从枚举的值中选一个，例如买衣服，只能有以下型号中的一种
示例
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
集合
- set
集合只是在枚举的基础上可以选多个
示例
CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
更多
更多参考 http://www.runoob.com/mysql/mysql-data-types.html 和 http://dev.mysql.com/doc/refman/5.7/en/data-type-overview.html
表操作
增
insert into 表 (列名,列名...) values (值,值,值...)
insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
insert into 表 (列名,列名...) select (列名,列名...) from 表
删
delete from 表
delete from 表 where id＝1 and name＝'alex'
改
update 表 set name ＝ 'alex' where id>1
一定一定要加上条件
查
select * from 表
select * from 表 where id > 1
select nid,name,gender as 别名 from 表
select nid,name,gender as 别名 from 表 where id > 1
select nid,name,gender as 别名 from 表 where id in (1, 2, 3)
select nid,name,gender as 别名 from 表 where id1 in (select nid from 表2)
这里把所有列写上查比用*快，如果列多说明表有问题
其他
条件
select * from 表 where id > 1 and name != 'alex' and num = 12;
select * from 表 where id between 5 and 16;
select * from 表 where id in (11,22,33)
select * from 表 where id not in (11,22,33)
select * from 表 where id in (select nid from 表)
通配符
select * from 表 where name like 'ale%' - ale开头的所有（多个字符串）
select * from 表 where name like 'ale_' - ale开头的所有（一个字符）
限制
select * from 表 limit 5; - 前5行
select * from 表 limit 4,5; - 从第4行开始的5行
select * from 表 limit 5 offset 4 - 从第4行开始的5行
排序
select * from 表 order by 列 asc - 根据 “列” 从小到大排列
select * from 表 order by 列 desc - 根据 “列” 从大到小排列
select * from 表 order by 列1 desc,列2 asc - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序
分组
select num from 表 group by num
select num,nid from 表 group by num,nid
select num,nid from 表 where nid > 10 group by num,nid order nid desc
select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
select num from 表 group by num having max(id) > 10
特别的：group by必须在where之后，order by之前
- group by的结果，对于没group by到的字段，查询出来是字段所有的值们可以进行函数操作，但是显示的时候默认只现实第一个
- 对于查询结果不能放在where中使用，必须使用having
连表
无对应关系则不显示
select A.num, A.name, B.name
from A,B
Where A.nid = B.nid
无对应关系则不显示
select A.num, A.name, B.name
from A inner join B
on A.nid = B.nid
A表所有数据都进行显示，如果B中无对应关系，则值为null
select A.num, A.name, B.name
from A left join B
on A.nid = B.nid
B表所有数据都进行显示，如果B中无对应关系，则值为null
select A.num, A.name, B.name
from A right join B
on A.nid = B.nid
组合
组合，自动处理重合
select nickname
from A
union
select name
from B
组合，不处理重合
select nickname
from A
union all
select name
from B
两次查询的列数必须一致，列的类型可以不一致，列名一致的以第一条SQL为准。
视图
视图是一个虚拟表，本身并不存在，本质上是sql语句获取的动态数据集，在使用的时候只需要使用视图名称即可获取结果集，并可以当做表使用
创建视图
格式
CREATE VIEW 视图名称 AS SQL语句
示例
CREATE VIEW v1 AS
SELET nid,
name
FROM
A
WHERE
nid > 4
删除视图
DROP VIEW 视图名称
修改视图
ALTER VIEW 视图名称 AS SQL语句
使用视图
因为视图是虚拟的表，无法执行创建更新和删除等操作，仅做查询使用
select * from 视图名称
触发器
触发器是在对表进行增删改操作的时候触发的特定行为，可以在操作前执行，也可以在操作后执行。
创建触发器
基本创建语法
插入前触发器
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
FOR EACH ROW是一个固定的格式，对每一行都进行操作。
这样的话，如果执行INSERT INTO tb1(name, password) values ('why','123456'), ('mabiao', '234567')是会触发两次的。
插入后触发器
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
删除前触发器
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
删除后触发器
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
更新前触发器
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
更新后触发器
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
创建触发器示例
delimiter //
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
IF NEW.NAME == 'why' THEN
INSERT INTO tb2 (NAME)
VALUES
('why')
END
END//
delimiter ;
如果插入的新数据的NAME为why，则在tb2表中添加NAME为why
- 这里NEW代表的是新添加的数据行，而OLD表示即将删除的数据行
- delimiter代表将结束符换为
'\\'而不是';'
delimiter //
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
IF NEW.num = 666 THEN
INSERT INTO tb2 (NAME)
VALUES
('666'),
('666') ;
ELSEIF NEW.num = 555 THEN
INSERT INTO tb2 (NAME)
VALUES
('555'),
('555') ;
END IF;
END//
delimiter ;
删除触发器
DROP TRIGGER tri_after_insert_tb1;
使用触发器
触发器是无法通过用户直接调用，都是通过对表的增删改操作进行触发
存储过程
存储过程是一个SQL语句的合集，当主动调用储存过程的时候，其中的SQL语句就会按照逻辑执行。
创建存储过程
创建无参数的存储过程
delimiter //
create procedure p1()
BEGIN
select * from t1;
END//
delimiter ;
创建有参数的存储过程
可以进行使用的参数有三类
- in，仅用于传入参数使用
- out，仅用于返回值用
- inout，即可以传入又可以当作返回值
delimiter \\
create procedure p1(
in i1 int,
in i2 int,
inout i3 int,
out r1 int
)
BEGIN
DECLARE temp1 int;
DECLARE temp2 int default 0;
set temp1 = 1;
set r1 = i1 + i2 + temp1 + temp2;
set i3 = i3 + 100;
end\\
delimiter ;
解释一下，这里
- in代表传入参数
- i1代表传入参数名称，数据类型为int
- declare代表声明局部变量
- temp1代表局部变量名称，数据类型为int
- default代表默认值为0
- set为赋值
删除存储过程
drop procedure proc_name;
drop procedure if exists proc_name;
执行存储过程
执行无参数存储过程
call proc_name()
执行有参数存储过程
如果全是in的可以参照以下
call proc_name(1,2)
如果参数有in，out和inout的可以参照以下
set @t1=0;
call proc_name(1,2,@t1,@t2)
会话变量@t1和@t2，定义在外边，作为引用传递给存储过程，存储过程对其进行赋值，对于out的变量不需要进行进行set赋值，例如t2
- @用户变量
- @@全局变量
然后就可以select @t1来获取返回值
函数
部分内置函数
CHAR_LENGTH(str)
返回值为字符串str的长度，长度的单位为字符。一个多字节字符算作一个单字符。
对于一个包含五个二字节字符集, LENGTH()返回值为10, 而CHAR_LENGTH()的返回值为5。
CONCAT(str1,str2,...)
字符串拼接
如有任何一个参数为NULL，则返回值为NULL。
CONCAT_WS(separator,str1,str2,...)
字符串拼接（自定义连接符）
CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。
CONV(N,from_base,to_base)
进制转换
例如：
SELECT CONV('a',16,2); 表示将a由16进制转换为2进制字符串表示
FORMAT(X,D)
将数字X的格式写为'#,###,###.##',以四舍五入的方式保留小数点后D位， 并将结果以字符串的形式返回。若D为0, 则返回结果不带有小数点，或不含小数部分。
例如：
SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
INSERT(str,pos,len,newstr)
在str的指定位置插入字符串
pos：要替换位置其实位置
len：替换的长度
newstr：新字符串
特别的：
如果pos超过原字符串长度，则返回原字符串
如果len超过原字符串长度，则由新字符串完全替换
INSTR(str,substr)
返回字符串str中子字符串的第一个出现位置。
LEFT(str,len)
返回字符串str从开始的len位置的子序列字符。
LOWER(str)
变小写
UPPER(str)
变大写
LTRIM(str)
返回字符串str，其引导空格字符被删除。
RTRIM(str)
返回字符串 str ，结尾空格字符被删去。
SUBSTRING(str,pos,len)
获取字符串子序列
LOCATE(substr,str,pos)
获取子序列索引位置
REPEAT(str,count)
返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。
若 count <= 0,则返回一个空字符串。
若str 或 count 为 NULL，则返回 NULL 。
REPLACE(str,from_str,to_str)
返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
REVERSE(str)
返回字符串 str ，顺序和字符顺序相反。
RIGHT(str,len)
从字符串str 开始，返回从后边开始len个字符组成的子序列
SPACE(N)
返回一个由N空格组成的字符串。
SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。
mysql> SELECT SUBSTRING('Quadratically',5);
-> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
-> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
-> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
-> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
-> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
-> 'ki'
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
返回字符串 str ， 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。
mysql> SELECT TRIM(' bar ');
-> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
-> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
-> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
-> 'barx'
更多的可以参考MySQL官方文档
创建自定义函数
注意自定义函数不能返回结果集
delimiter \\
create function f1(
i1 int,
i2 int)
returns int
BEGIN
declare num int;
set num = i1 + i2;
return(num);
END \\
delimiter ;
对传入的函数的两个值进行相加。
- declare用于声明变量
删除函数
drop function func_name;
其实对于不用的函数就不使用就好。
调用函数
select f1(1,nid) ,name from tb2;
也可以获取返回值
declare @i VARCHAR(32);
select UPPER('why') into @i;
SELECT @i;
事务
事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。
最简单的实例就是转钱，我少钱，他多钱，原子性如果遇到问题，就进行回滚，如果我少钱了，他还没多钱，这样就会出现问题。
事务需要MySQL的引擎支持，我们常用的就是INNODB引擎。
delimiter \\
create PROCEDURE p1(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
set p_return_code = 1;
rollback;
END;
DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
set p_return_code = 2;
rollback;
END;
START TRANSACTION;
UPDATE userinfo SET asset = asset - 5 WHERE NAME = 'mabiao';
UPDATE userinfo SET asset = asset + 5 WHERE NAME = 'why';
COMMIT;
-- SUCCESS
set p_return_code = 0;
END\\
delimiter ;
获取事务返回值。
set @i =0;
call p1(@i);
select @i;
索引
索引是数据库用于专门帮助快速查询数据的一种数据结构，类似字典的目录，查找字典的目录就可以根据目录查找到数据的存放的位置，然后直接获取。
索引原理
MySQL的索引是B-tree索引。
原理可以查看以下
30
10 40
5 15 35 66
1 6 11 19 21 39 55 100
如果查找11的时候，直接和30比较，小于30，就在30的左边，和10比较以下，大于10就在10的右边，直到找到11
常见索引
- 普通索引
- 唯一索引
- 主键索引
- 组合索引
普通索引
普通索引的目的为加速查询，MySQL默认的情况是进行全表扫描的。
对于文本的搜索最好还是要solr，lucence或者sphix来做。
创建表加索引
create table in1(
nid int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
extra text,
index ix_name (name)
)
创建索引
create index index_name on table_name(column_name)
注意，如果是BLOB类型和TEXT类型，必须指定length
create index ix_extra on in1(extra(32));
删除索引
drop index_name on table_name;
查看索引
show index from table_name;
唯一索引
唯一索引在普通索引的加速查询的基础上，添加了约束了字段唯一(可以为NULL)
创建表和唯一索引
create table in1(
nid int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
extra text,
unique ix_name (name)
)
创建唯一索引
create unique index 索引名 on 表名(列名
删除唯一索引
drop unique index 索引名 on 表名
主键索引
主键索引的也能加速索引和约束字段唯一，但是字段不能为NULL
创建表和主键索引
create table in1(
nid int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
extra text,
index ix_name (name)
)
或者
create table in1(
nid int not null auto_increment,
name varchar(32) not null,
email varchar(64) not null,
extra text,
primary key(ni1),
index ix_name (name)
)
创建主键索引
alter table 表名 add primary key(列名);
删除主键索引
alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop primary key;
组合索引
组合索引是将多个列组合成一个索引
应用场景为频繁的使用多列为条件进行查询，例如where name = 'why' and phone = '13523332333'
创建表和组合索引
create table in3(
nid int not null auto_increment primary key,
name varchar(32) not null,
phone varchar(64) not null,
extra text
)
创建组合索引
create index ix_name_email on in3(name,phone);
组合索引生效问题
对于组合索引，例如name,phone
- name,phone 使用索引
- name 使用索引
- phone 使用索引
对于同时搜索n个条件时，组合索引的性能好于多个单一索引合并。
其实理解的B-tree索引的原理这些就都应该好理解了。
可以通过explain接需要执行的SQL语句进行测试一下是否走索引了，如果type为ALL则代表使用了全表扫描，没有使用索引。
当然也有hash索引，将其转化成hash值进行排序存储。
有一个点，进行全表扫描的时候查找唯一的数据，如果使用limit 1，虽然进行全表扫描，但是找到后就停止了，不会继续。就是因为limit 1的原因
对于where条件，like的进行匹配，对于'wanghong%'是可以走索引的，而'%hongyu'是不走索引的，同理B-tree索引原理。
在where条件中使用函数的也不走索引。
覆盖索引
示例tb1表的索引为name
而执行的SQL语句为select name from tb1 where name = why;
这种情况其实就没有查数据库，直接查完索引就返回了
其他
条件语句
delimiter \\
CREATE PROCEDURE proc_if ()
BEGIN
declare i int default 0;
if i = 1 THEN
SELECT 1;
ELSEIF i = 2 THEN
SELECT 2;
ELSE
SELECT 7;
END IF;
END\\
delimiter ;
循环语句
while
delimiter \\
CREATE PROCEDURE proc_while ()
BEGIN
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
SELECT
num ;
SET num = num + 1 ;
END WHILE ;
END\\
delimiter ;
repeat
delimiter \\
CREATE PROCEDURE proc_repeat ()
BEGIN
DECLARE i INT ;
SET i = 0 ;
repeat
select i;
set i = i + 1;
until i >= 5
end repeat;
END\\
delimiter ;
loop
BEGIN
declare i int default 0;
loop_label: loop
set i=i+1;
if i<8 then
iterate loop_label;
end if;
if i>=10 then
leave loop_label;
end if;
select i;
end loop loop_label;
END
动态执行SQL
动态执行SQL依赖的存储过程，但是存储过程中有占位符。
delimiter \\
DROP PROCEDURE IF EXISTS proc_sql \\
CREATE PROCEDURE proc_sql ()
BEGIN
declare p1 int;
set p1 = 11;
set @p1 = p1;
PREPARE prod FROM 'select * from tb2 where nid > ?';
EXECUTE prod USING @p1;
DEALLOCATE prepare prod;
END\\
delimiter ;
这个存储过程是获取大于11的行。
- ?是一个占位符。
- PREPARE做预编译
- EXECUTE执行
- DEALLOCATE删除
delimiter \\
DROP PROCEDURE IF EXISTS proc_sql \\
CREATE PROCEDURE proc_sql (
in str_sql VARCHAR(128),
in nid int,
)
BEGIN
set @str_sql = str_sql;
set @nid = nid;
PREPARE prod FROM @str_sql;
EXECUTE prod USING @nid;
DEALLOCATE prepare prod;
END\\
delimiter ;
这样就可以传入SQL和需要的参数nid动态执行了。
set @str_sql = 'select * from tb2 where nid > ?';
set @nid = 11;
call proc_sql(@str_sql, @nid);
这样也能起到防止SQL注入的功能。
注意点
数据库是用作数据存储的地方，不要用作做一些逻辑运算的操作，数据库虽然支持了很多的功能，但是会加重数据库的负担，切记能在程序逻辑中解决的就在逻辑中解决，推荐多用索引，原子性操作使用事务。
当然还有别的
- 避免使用select *
- 创建表的时候进行使用char
- 使用count(1)代替count(*)
- 对于表字段，固定长度的字段在前
- 尽量使用组合索引
- 尽量使用短索引
- 使用JOIN替代子查询
- 重复值多的不适合做索引，例如性别
pymysql使用
示例代码，创建数据库表
pymysql执行SQL
# -*- coding:utf-8 -*-
import pymysql
# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=9999, user='why', password='123456', db='testpymysql')
# 创建游标（游标用于对数据库进行操作）
curson = conn.cursor()
# 执行数据库操作，返回值为受影响的行数，但是此时还没有执行操作
effect_now = curson.execute("CREATE TABLE tb1 ("
"id int not null auto_increment primary key, "
"name varchar(16) null, "
"password varchar(16) null "
") ENGINE=InnoDB DEFAULT CHARSET=utf8"
)
# 提交到数据库
conn.commit()
# 关闭游标
curson.close()
# 关闭连接
conn.close()
可以在mysql那边检验一下。
mysql> use testpymysql;
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> show tables;
+-----------------------+
| Tables_in_testpymysql |
+-----------------------+
| tb1 |
+-----------------------+
1 row in set (0.00 sec)
插入单条数据
也是同理
effect_now = curson.execute("INSERT INTO tb1 (name, password) VALUES ('why', '123456')")
数据库中查询到的
mysql> select * from tb1;
+----+------+----------+
| id | name | password |
+----+------+----------+
| 1 | why | 123456 |
+----+------+----------+
1 row in set (0.00 sec)
通过占位符的形式
effect_now = curson.execute("INSERT INTO tb1 (name, password) VALUES ('%s', '%s')" % ('mabiao', '234567'))
mysql> select * from tb1;
+----+--------+----------+
| id | name | password |
+----+--------+----------+
| 1 | why | 123456 |
| 2 | mabiao | 234567 |
+----+--------+----------+
2 rows in set (0.00 sec)
使用pymysql提供的arg
在pymysql中内部提供了第二个参数，用于占位符的方式
effect_now = curson.execute("INSERT INTO tb1 (name, password) VALUES (%s, %s)" ,('pqt', '345678'))
以上两种操作究竟有什么有区别呢，其实是可以防止SQL注入的功能
mysql> select * from tb1;
+----+--------+----------+
| id | name | password |
+----+--------+----------+
| 1 | why | 123456 |
| 2 | mabiao | 234567 |
| 3 | pqt | 345678 |
+----+--------+----------+
3 rows in set (0.00 sec)
如果对多行数据插入，pymysql提供了另一个方法executemany
effect_now = curson.executemany("INSERT INTO tb1 (name, password) VALUES (%s, %s)" ,[('yanwei', '456789'),('chenyu', '567890')])
mysql> select * from tb1;
+----+--------+----------+
| id | name | password |
+----+--------+----------+
| 1 | why | 123456 |
| 2 | mabiao | 234567 |
| 3 | pqt | 345678 |
| 4 | yanwei | 456789 |
| 5 | chenyu | 567890 |
+----+--------+----------+
5 rows in set (0.00 sec)
获取自增id
# -*- coding:utf-8 -*-
import pymysql
# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=9999, user='why', password='123456', db='testpymysql')
# 创建游标（游标用于对数据库进行操作）
curson = conn.cursor()
# 执行数据库操作，返回值为受影响的行数，但是此时还没有执行操作
effect_now = curson.executemany("INSERT INTO tb1 (name, password) VALUES (%s, %s)" ,[('clw', '456789'),('xumingfei', '567890')])
# 提交到数据库
conn.commit()
# 关闭游标
curson.close()
# 关闭连接
conn.close()
# 获取自增id
new_id = curson.lastrowid
print(new_id)
输出的new_id值为8
mysql> select * from tb1;
+----+-----------+----------+
| id | name | password |
+----+-----------+----------+
| 1 | why | 123456 |
| 2 | mabiao | 234567 |
| 3 | pqt | 345678 |
| 4 | yanwei | 456789 |
| 5 | chenyu | 567890 |
| 8 | clw | 456789 |
| 9 | xumingfei | 567890 |
+----+-----------+----------+
7 rows in set (0.00 sec)
可以看到是插入的第一个id
可以测试插入一行数据
effect_now = curson.executemany("INSERT INTO tb1 (name, password) VALUES (%s, %s)" ,[('dongpch', '456789')])
输出的new_id值为10
查询数据
fetch
fetch获取到的值都是按照顺序执行的。
# -*- coding:utf-8 -*-
import pymysql
# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=9999, user='why', password='123456', db='testpymysql')
# 创建游标（游标用于对数据库进行操作）
curson = conn.cursor()
# 执行数据库操作，返回值为受影响的行数，但是此时还没有执行操作
effect_now = curson.execute("select * from tb1")
# 获取第一行数据
row_1 = curson.fetchone()
# 获取前几行数据
row_2 = curson.fetchmany(3)
# 获取所有行数据
row_3 = curson.fetchall()
# 提交到数据库
conn.commit()
# 关闭游标
curson.close()
# 关闭连接
conn.close()
# 输出查询结果
print row_1
print row_2
print row_3
输出的值
(1, 'why', '123456')
((2, 'mabiao', '234567'), (3, 'pqt', '345678'), (4, 'yanwei', '456789'))
((5, 'chenyu', '567890'), (8, 'clw', '456789'), (9, 'xumingfei', '567890'), (10, 'dongpch', '456789'), (11, 'aaa', '456789'), (12, 'bbb', '456789'), (13, 'ccc', '456789'))
可以看到每行的数据被放在一个元组，而多行数据依然是在元组中，不过curson中获取的数据就没有了。
另外row不是一定要写在commit前，写到conn.close()后都行
设置游标类型
如果想将获取值为字典的形式，可以在创建游标的时候指定其类型
curson = conn.cursor(cursor=pymysql.cursors.DictCursor)
获取到的结果
{u'password': '123456', u'id': 1, u'name': 'why'}
[{u'password': '234567', u'id': 2, u'name': 'mabiao'}, {u'password': '345678', u'id': 3, u'name': 'pqt'}, {u'password': '456789', u'id': 4, u'name': 'yanwei'}]
[{u'password': '567890', u'id': 5, u'name': 'chenyu'}, {u'password': '456789', u'id': 8, u'name': 'clw'}, {u'password': '567890', u'id': 9, u'name': 'xumingfei'}, {u'password': '456789', u'id': 10, u'name': 'dongpch'}, {u'password': '456789', u'id': 11, u'name': 'aaa'}, {u'password': '456789', u'id': 12, u'name': 'bbb'}, {u'password': '456789', u'id': 13, u'name': 'ccc'}]
scroll
# -*- coding:utf-8 -*-
import pymysql
# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=9999, user='why', password='123456', db='testpymysql')
# 创建游标（游标用于对数据库进行操作）
curson = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行数据库操作，返回值为受影响的行数，但是此时还没有执行操作
effect_now = curson.execute("select * from tb1")
# 提交到数据库
conn.commit()
# 关闭游标
curson.close()
# 关闭连接
conn.close()
# 获取第一行数据
row_1 = curson.fetchone()
# 获取前几行数据
row_2 = curson.fetchmany(3)
# 获取当前位置的上一行
curson.scroll(-1, mode='relative')
row_3 = curson.fetchone()
# 获取当前位置的上一行
curson.scroll(-1, mode='relative')
row_4 = curson.fetchone()
# 获取绝对位置的第一行
curson.scroll(0, mode='absolute')
row_5 = curson.fetchone()
print row_1
print row_2
print row_3
print row_4
print row_5
执行结果
{u'password': '123456', u'id': 1, u'name': 'why'}
[{u'password': '234567', u'id': 2, u'name': 'mabiao'}, {u'password': '345678', u'id': 3, u'name': 'pqt'}, {u'password': '456789', u'id': 4, u'name': 'yanwei'}]
{u'password': '456789', u'id': 4, u'name': 'yanwei'}
{u'password': '456789', u'id': 4, u'name': 'yanwei'}
{u'password': '123456', u'id': 1, u'name': 'why'}
- cursor.scroll(1,mode='relative') 相对当前位置移动，正数代表向下移动几行，负数代表向上移动几行
- cursor.scroll(2,mode='absolute') 相对绝对位置移动
关于防止SQL注入
tornado代码
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import tornado.ioloop
import tornado.web
import pymysql
class IndexHandler(tornado.web.RequestHandler):
def get(self):
self.render("index.html")
def post(self,*args,**kwargs):
name = self.get_argument('name',None)
password = self.get_argument('password',None)
conn = pymysql.connect(host='127.0.0.1', port=9999, user='why', password='123456', db='testpymysql')
curson = conn.cursor()
sql = "select name from tb1 where name = '%(name)s' and password = '%(password)s'" % locals()
print sql
curson.execute(sql)
result = curson.fetchone()
conn.commit()
curson.close()
conn.close()
if result:
self.write('登录成功~')
else:
self.write('登录失败')
application = tornado.web.Application([ #创建tornado.web.Application对象
(r"/", IndexHandler), #当发送url请求的时候，对检查是否匹配，然后路由到对应的类的get，post等方法(路由映射)
],) #加载settings
if __name__ == "__main__":
application.listen(8888) #创建一个socket
tornado.ioloop.IOLoop.instance().start() #使用epoll，IO多路复用循环
index.html代码
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<form method="post">
<input type="text" name="name">
<input type="password" name="password">
<input type="submit" value="提交">
</form>
</body>
</html>
测试登录成功
打印出来的SQL为
select name from tb1 where name = 'why' and password = '123456'
登录登录失败
进行sql注入
name输入
why' -- f
打印出来的SQL为
select name from tb1 where name = 'why' -- f' and password = '123'
原理是 -- 是注释
why' or 1 = 1 -- d
select name from tb1 where name = 'why' or 1 = 1 -- d' and password = '123'
dsafsa' and 1 or 1 -- d
select name from tb1 where name = 'dsafsa' and 1 or 1 -- d' and password = ''
人为的进行拼接，对输入没有进行判断
pymysql进行了优化
这里SQL注入还只是个登录，没有对数据库表进行任何操作。
使用pymysql原生的自带的字符串拼接
curson = conn.cursor()
sql = "select name from tb1 where name = '%s' and password = '%s'"
curson.execute(sql,(name, password))
result = curson.fetchone()
conn.commit()
报错信息
ProgrammingError: (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dsafsa\\' and 1 or 1 -- d'' and password = ''''' at line 1")
这里会把引号进行特殊处理
原理可以参考源码中的execute方法
def execute(self, query, args=None):
"""Execute a query
:param str query: Query to execute.
:param args: parameters used with query. (optional)
:type args: tuple, list or dict
:return: Number of affected rows
:rtype: int
If args is a list or tuple, %s can be used as a placeholder in the query.
If args is a dict, %(name)s can be used as a placeholder in the query.
"""
while self.nextset():
pass
query = self.mogrify(query, args)
result = self._query(query)
self._executed = query
return result
使用了query = self.mogrify(query, args)进行了处理
我们也可以调用这个query方法来处理一下字符串拼接的SQL
curson = conn.cursor()
sql = "select name from tb1 where name = '%s' and password = '%s'"
query = curson.mogrify(sql,(name, password))
print query
curson.execute(sql,(name, password))
result = curson.fetchone()
conn.commit()
输出的SQL
select name from tb1 where name = ''dsafsa\' and 1 or 1 -- d'' and password = ''''
存储过程
cursor.callproc()
传递多的参数会自动忽略。
