数据库进阶

pymsql是Python中操作MySQL的模块，其使用方法和py2的MySQLdb几乎相同。

import pymysql

#添加数据

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='yyy')

cursor = conn.cursor()


# sql = """CREATE TABLE EMPLOYEE (
#          FIRST_NAME  CHAR(20) NOT NULL,
#          LAST_NAME  CHAR(20),
#          AGE INT,
#          SEX CHAR(1),
#          INCOME FLOAT )"""
# 
# cursor.execute(sql)

#row_affected = cursor.execute("create table t1(id INT ,name VARCHAR(20))")

#row_affected=cursor.execute("INSERT INTO t1(id,name) values (1,'alvin'),(2,'xialv')")

#cursor.execute("update t1 set name = 'silv2' where id=2")




#查询数据
row_affected=cursor.execute("select * from t1")
one=cursor.fetchone()

# many=cursor.fetchmany(2)
# all=cursor.fetchall()



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

#cursor.scroll(2,mode='absolute') # 相对绝对位置移动


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


conn.commit()
cursor.close()
conn.close()

二 索引语法

--创建表时
--语法：
    CREATE TABLE 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                [索引名]  (字段名[(长度)]  [ASC |DESC])
                );

--------------------------------

--创建普通索引示例：

    CREATE TABLE emp1 (
        id INT,
        name VARCHAR(30) ,
        resume VARCHAR(50),
        INDEX index_emp_name (name)
    --KEY index_dept_name (dept_name)
        );



--创建唯一索引示例：

    CREATE TABLE emp2 (
        id INT,
        name VARCHAR(30) ,
        bank_num CHAR(18) UNIQUE ,
        resume VARCHAR(50),
        UNIQUE INDEX index_emp_name (name)
        );

--创建全文索引示例：

    CREATE TABLE emp3 (
        id INT,
        name VARCHAR(30) ,
        resume VARCHAR(50),
        FULLTEXT INDEX index_resume (resume)
        );

--创建多列索引示例：

    CREATE TABLE emp4 (
        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 | SPATIAL ] INDEX
                    索引名 (字段名[(长度)]  [ASC |DESC]) ;
    
    
    
     CREATE INDEX index_emp_name on emp1(name);
     ALTER TABLE emp2 ADD UNIQUE INDEX index_bank_num(band_num);


-- 删除索引
    
    语法：DROP INDEX 索引名 on 表名
    
    DROP INDEX index_emp_name on emp1;
    DROP INDEX bank_num on emp2;

 三 索引测试实验

--创建表
create table Indexdb.t1(id int,name varchar(20));


--存储过程

delimiter $$
create procedure autoinsert() 
BEGIN
declare i int default 1;
while(i<500000)do
insert into Indexdb.t1 values(i,'yuan');
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