数据库进阶

　　　　　　　　　　python关于，mysql的API---pymysql模块

pymysql是python中操作MYSQL的模块，其使用方法和py2的MYSQLdb几乎相同。

模块安装

pip install pymysql

执行sql语句

import pymysql
# 创建连接
conn = pymysql.connect(host="127.0.0.1",port=3306,user="root",passwd="123456",db="test1",charset="utf-8")

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = conn.cursor()

# 使用 execute() 方法执行 SQL 查询
cursor.execute("show databases")

# 使用 fetchone() 方法获取单条数据
data = cursor.fetchone()

print("数据:%s"%data)

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

host ip地址
port 端口
user 用户名
passwd 密码
db 数据库
charset 编码

cursor.fetchone() 获取单条数据
cursor.fetchall() 获取全部数据
cursor.fetchmany(2) 获取2条数据

scroll
cursor.scroll(-1,mode="relative") # 相对当前位置移动
cursor.scroll(2,mode="absolute") # 相对绝对位置移动

更改获取数据结果的数据类型，默认是元祖，可以改为字典等:conn.cursor(cursor=pymysql.cursors.DictCursor)

　　　　　　事务

事务命令

事务指逻辑上的一组操作，组成这组操作的各个单元，要不全部成功，要不全部不成功。

数据库开启事务命令

start transaction 开启事务
Rollback 回滚事务，即撤销指定的sql语句（只能回退insert delete update语句），回滚到上次commit的位置
Commit 提交事务，提交未存储的事务
savepoint 保留点，事务处理中设置的临时占位符，你可以对它发布回退（与整个事务回退不同）

　　转账实例：

"UPDATE account set balance=balance-100 WHERE name=‘a‘"
"UPDATE account set balance=balance-100 WHERE name=‘b‘"

create table test2(id int PRIMARY KEY auto_increment,name VARCHAR(20)) engine=innodb;
INSERT INTO test2(name) VALUE ("alvin"),
                              ("aaa"),
                              ("xialv");



start transaction;
insert into test2 (name)values(‘silv‘);
select * from test2;
commit;


-- 保留点

start transaction;
insert into test2 (name)values(‘wu‘);
savepoint insert_wu;
select * from test2;



delete from test2 where id=4;
savepoint delete1;
select * from test2;


delete from test2 where id=1;
savepoint delete2;
select * from test2;

rollback to delete1;


select * from test2;

import pymysql
# 创建连接
conn = pymysql.connect("127.0.0.1","root","lwj5203344587")
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = conn.cursor()

sql0 = "CREATE DATABASE test1"
sql1 = "use test1"
sql2 = "CREATE table account(name varchar(20),balance int)"
sql3 = "INSERT INTO account(name,balance) values (‘a‘,5000),(‘b‘,5000),(‘c‘,5000)"
sql4 = "savepoint into1"
sql5 = "SELECT * FROM account"
sql6 = "delete from account where name=‘a‘"
sql7 = "savepoint del1"
sql8 = "INSERT INTO account(name,balance) values (‘a‘,5000),(‘b‘,5000)"
sql10 = "rollback to into1"
# 使用 execute() 方法执行 SQL 查询
cursor.execute(sql0)
cursor.execute(sql1)
cursor.execute(sql2)
cursor.execute(sql3)
cursor.execute(sql4)
cursor.execute(sql5)
print(cursor.fetchall())
cursor.execute(sql6)
cursor.execute(sql7)
cursor.execute(sql8)
cursor.execute(sql5)
print(cursor.fetchall())
cursor.execute(sql10)
conn.commit()
cursor.execute(sql5)
print(cursor.fetchall())
cursor.close()
conn.close()

python中调用数据库启动事务的方式

import pymysql
# 创建连接
conn = pymysql.connect("127.0.0.1","root","lwj5203344587")

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = conn.cursor()
try:
    sql0 = "CREATE DATABASE test1"
    sql1 = "use test1"
    sql2 = "CREATE table account(name varchar(20),balance int)"
    sql3 = "INSERT INTO account(name,balance) values (‘a‘,5000),(‘b‘,5000)"
    sql4 = "UPDATE account set balance=balance-100 WHERE name=‘a‘"
    sql5 = "UPDATE account set balance=balance-100 WHERE name=‘b‘"

    # 使用 execute() 方法执行 SQL 查询
    cursor.execute(sql0)
    cursor.execute(sql1)
    cursor.execute(sql2)
    cursor.execute(sql3)
    # 提交事务
    conn.commit()

    cursor.execute(sql4)
    # 创造异常
    raise Exception
    cursor.execute(sql5)
    cursor.close()
    conn.commit()
except Exception as e:
    # 回滚事务
    conn.rollback()
    # 提交事务
    conn.commit()
# 关闭连接
cursor.close()
conn.close()

索引

一、索引简介

索引在MYSQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构·。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。

索引优化应该是对查询性能优化最有效的手段了。

索引能够轻易将查询性能提高好几个数量级。

索引相当于字典的音序表，如果要某个字，如果不使用音序表，则需要从几百页中逐页去查。

索引特点：创建与维护索引会消耗很多时间与磁盘空间，但查询速度大大提高！

二、索引语法

-- 创建比表时
-- 语法：
    CREATE TABLE 表名(
                            字段1 数据类型 [完整约束条件...],
                            字段2 数据类型 [完整约束条件...],
                            [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
                            [索引名] (字段名[(长度)] [ASC | DESC])
                            );
 
 
-- 创建普通索引示例：
 
    CREATE TABLE emp(
            id INT,
            name VARCHAR(30),
            resume VARCHAR(50),
            INDEX index_emp_name(name)
         --KEY index_emp_name(dept_name)
            );
 
-- 创建唯一索引示例：
     
     CREATE TABLE emp(
            id INT,
            name VARCHAR(30),
            resume VARCHAR(50),
            FULLTEXT INDEX index_resume(resume)
            );
 
-- 创建多列索引示例：
 
    CREATE TABLE emp(
            id INT,
            name VARCHAR(30),
            resume VARCHAR(50),
            INDEX index_name_resume(name,resume)
            );
 
 
 
 
-- 添加索引
    -- CREATE在已存在的表上创建索引
        CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名
                    ON 表名 (字段名[(长度)] [ASC | DESC]);
 
    -- ALTER TABLE在已存在的表上创建索引
        ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATTAL] INDEX
                            索引名 (字段名[(长度)] [ASC | DESC]);
 
 
        CREATE INDEX index_emp_name on emp(name);
        ALTER TABLE emp ADD UNIQUE INDEX index_bank_num(band_num);
 
-- 删除索引
    语法：DROP INDEX 索引名 on 表名
     
    DROP INDEX index_emp_name on emp;
    DROP INDEX bank_num on emp;

　　索引测试实验

-- 创建表
create table indexdb.t1(id  int,name varchar(20));
 
-- 存储过程
delimiter $$
create procedure autoinsert()
BEGIN
declare i int default 1;
while (i<50000)do
insert into indexdb.t1 values(i,"lwj");
set i=i+1;
end while;
END$$
 
delimiter ;
 
-- 调用函数
call autoinsert();
 
-- 花费时间比较：
-- 创建索引前
   select * from Indexdb.t1 where id=300000;--0.32s
-- 添加索引
   create index index_id on Indexdb.t1(id);
-- 创建索引后
   select * from Indexdb.t1 where id=300000;--0.00s

数据库进阶

原文：https://www.cnblogs.com/linglinglingling/p/12012825.html