Python--MySql(主键的创建方式、存储引擎、存储过程、索引、pymsql)

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;

/etc/my.cnf

[mysqld]

default-storage-engine=INNODB

innodb_file_per_table=1

[root@egon db1]# cd /var/lib/mysql/db1/

[root@egon db1]# ls

db.opt  innodb_t1.frm  innodb_t1.ibd  innodb_t2.frm  innodb_t2.ibd

创建四个表，分别使用innodb，myisam，memory，blackhole存储引擎，进行插入数据测试

MariaDB [db1]> create table t1(id int)engine=innodb;

MariaDB [db1]> create table t2(id int)engine=myisam;

MariaDB [db1]> create table t3(id int)engine=memory;

MariaDB [db1]> create table t4(id int)engine=blackhole;

MariaDB [db1]> quit

[root@egon db1]# ls /var/lib/mysql/db1/ #发现后两种存储引擎只有表结构，无数据

db.opt  t1.frm  t1.ibd  t2.MYD  t2.MYI  t2.frm  t3.frm  t4.frm

#memory，在重启mysql或者重启机器后，表内数据清空

#blackhole，往表内插入任何数据，都相当于丢入黑洞，表内永远不存记录 

-- 创建存储过程

delimiter //

create procedure p1()

BEGIN

    select * from t1;

END//

delimiter ;

-- 执行存储过程

call p1()

-- 创建存储过程

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 ;

-- 执行存储过程

set @t1 =4;

set @t2 = 0;

CALL p1 (1, 2 ,@t1, @t2);

SELECT @t1,@t2;

delimiter //

                    create procedure p1()

                    begin

                        select * from v1;

                    end //

                    delimiter ;

delimiter //

                    create procedure p2(

                        in n1 int,

                        inout n3 int,

                        out n2 int,

                    )

                    begin

                        declare temp1 int ;

                        declare temp2 int default 0;

                        select * from v1;

                        set n2 = n1 + 100;

                        set n3 = n3 + n1 + 100;

                    end //

                    delimiter ;

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;

                            DELETE from tb1;

                            insert into tb2(name)values('seven');

                          COMMIT; 

                          -- SUCCESS

                          set p_return_code = 0; 

                          END\\

                    delimiter ;

delimiter //

                    create procedure p3()

                    begin

                        declare ssid int; -- 自定义变量1

                        declare ssname varchar(50); -- 自定义变量2

                        DECLARE done INT DEFAULT FALSE;

                        DECLARE my_cursor CURSOR FOR select sid,sname from student;

                        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

                        open my_cursor;

                            xxoo: LOOP

                                fetch my_cursor into ssid,ssname;

                                if done then

                                    leave xxoo;

                                END IF;

                                insert into teacher(tname) values(ssname);

                            end loop xxoo;

                        close my_cursor;

                    end  //

                    delimter ;

delimiter \\

                    CREATE PROCEDURE p4 (

                        in nid int

                    )

                    BEGIN

                        PREPARE prod FROM 'select * from student where sid > ?';

                        EXECUTE prod USING @nid;

                        DEALLOCATE prepare prod;

                    END\\

                    delimiter ;

drop procedure proc_name;

-- 无参数

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)

--创建表时

--语法：

    CREATE TABLE 表名 (

                字段名1  数据类型 [完整性约束条件…],

                字段名2  数据类型 [完整性约束条件…],

                [UNIQUE]   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]  INDEX  索引名

              ON 表名 (字段名[(长度)]  [ASC |DESC]) ;

    ---ALTER TABLE在已存在的表上创建索引

      ALTER TABLE 表名 ADD  [UNIQUE] 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;

pip3 install pymysql

import pymysql

# 创建连接

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

# 创建游标

cursor = conn.cursor()

# 执行SQL，并返回收影响行数

effect_row = cursor.execute("update hosts set host = '1.1.1.2'")

# 执行SQL，并返回受影响行数

#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))

# 执行SQL，并返回受影响行数

#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])

# 提交，不然无法保存新建或者修改的数据

conn.commit()

# 关闭游标

cursor.close()

# 关闭连接

conn.close()

import pymysql

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

cursor = conn.cursor()

cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])

conn.commit()

cursor.close()

conn.close()

# 获取最新自增ID

new_id = cursor.lastrowid

import pymysql

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

cursor = conn.cursor()

cursor.execute("select * from hosts")

# 获取第一行数据

row_1 = cursor.fetchone()

# 获取前n行数据

# row_2 = cursor.fetchmany(3)

# 获取所有数据

# row_3 = cursor.fetchall()

conn.commit()

cursor.close()

conn.close()

import pymysql

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

# 游标设置为字典类型

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

r = cursor.execute("call p1()")

result = cursor.fetchone()

conn.commit()

cursor.close()

conn.close()