MySQL 复制建表、操作补充、pymysql操作—/—＜10＞

一、复制建表

1、复制其他表的结构

只复制结构，不复制值

CREATE TABLE aaa LIKE student

student表：

复制完的a表：

2、复制查询结果并建表:

create table a (select id,sum(score) as sum_sco from score group by id)

score表结构如图所示： 创建的a表：  

二、基础语言补充

1、查询建表语句

show CREATE table student

2、查询可视化的建表语句

DESC student

3、修改表字符集

alter table student character set 字符集名称;

4、增加一列

alter table 表名 add 列名 数据类型;

5、修改列名、类型

alter table 表名 change 列名 新列名 新数据类型

alter table 表名 modify 列名 新数据类型

6、删除列

alter table 表明 drop 列名;

7、删除表

drop table 表名;
drop table if exists 表名;

8、插入数据

insert into stu select * from student limit 500,500;

即从student表中取出从行500开始往后数500条数据，将这些数据插入stu表

9、去重

select distinct name from student

将学生表去除重复值

三、用Python连接SQL

1、导包

pip install pymysql

2、连接SQL

conn=pymysql.connect(host='192.168.197.100',user='root',passwd='123456',port=3306,db='ai_03')

其中host为主机ip，user为登入的用户，passwd为用户密码，port为端口，db为数据库

3、数据库游标

        数据库游标是指一个指向数据库中某个特定数据项的指针。通过使用游标，可以在数据库中遍历数据，查找、修改或删除指定的数据项。游标可以被认为是一个迭代器，它允许我们在数据库中按顺序访问数据。

conn=pymysql.connect(host='192.168.197.100',user='root',passwd='123456',port=3306,db='ai_03')
#建立连接后，建立游标用来获取信息
cursor=conn.cursor()

4、建立查询，查询数据

conn=pymysql.connect(host='192.168.197.100',user='root',passwd='123456',port=3306,db='ai_03')
#建立连接后，建立游标用来获取信息
cursor=conn.cursor()

#查询数据
#书写sql中的查询语句
#此时如果在上述建立连接时没有使用db来确认数据库，需要在获取信息的时候使用库名
#如果没有写，那么直接写表名即可
select_sql="select * from ai_03.student"

5、执行查询指令

conn=pymysql.connect(host='192.168.197.100',user='root',passwd='123456',port=3306,db='ai_03')
#建立连接后，建立游标用来获取信息
cursor=conn.cursor()

#查询数据
select_sql="select * from student"

#用游标执行查询语句
cursor.execute(select_sql)

6、获取数据

conn=pymysql.connect(host='192.168.197.100',user='root',passwd='123456',port=3306,db='ai_03')
#建立连接后，建立游标用来获取信息
cursor=conn.cursor()

#查询数据
select_sql="select * from student"

#用游标执行查询语句
cursor.execute(select_sql)

#获取数据
#获取一条数据
print(cursor.fetchone())
#获取9条数据，元祖类型返回
print(cursor.fetchmany(9))
#获取全部数据
print(cursor.fetchall())

        上述代码中cursor.fetchone()获取一条数据后，后面在使用fetchmany()获取多条数据，此时会接着从后面获取数据，而不是从头开始获取，元组形式打印

        此时可以通过for循环来遍历其中的值，例如

conn=pymysql.connect(host='192.168.197.100',user='root',passwd='123456',port=3306,db='ai_03')
#建立连接后，建立游标用来获取信息
cursor=conn.cursor()

#查询数据
select_sql="select * from student"

#用游标执行查询语句
cursor.execute(select_sql)

#遍历每条数据
for i in cursor.fetchall():
    print(i[1])

此时可以打印每条数据中的第二个：

7、关闭游标、关闭连接

获取完数据记得关闭游标和连接

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

8、插入数据，发送事务

#建立连接
conn=pymysql.connect(host='192.168.197.100',user='root',passwd='123456',port=3306,db='ai_03')
#建立游标
cursor=conn.cursor()

#创建一个表
create_sql = create table if not exists aaa(id int,name varchar(255))  
cursor.execute(create_sql)
#插入数据
insert_sql = "insert into aaa values(1,'hx')"
cursor.excute(insert_sql)
#执行insert语句时，需要提交事务
conn.commit()

cursor.close()
conn.close()

9、insert问题

1）外界传入一条数据

#建立连接
conn=pymysql.connect(host='192.168.197.100',user='root',passwd='123456',port=3306,db='ai_03')
#建立游标
cursor=conn.cursor()

#创建一个表，并定义主键及自增列
create_sql = create table aaa(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(255))  
cursor.execute(create_sql)

#外界传入一条数据
name_a='hhx'
insert_sql = f"INSERT INTO aaa VALUES(0,"{name_a}")" #此时{}外面需要给引号
#使用execute发送指令
cursor.execute(insert_sql)

conn.commit()


cursor.close()
conn.close()

2）外界传入多条数据

#外界传入多条数据
name_a=['hx1','hx2','hx3']
insert_sql = f"INSERT INTO aaa VALUES(0,%s)" #此时使用%s来表示传入参数
#此时使用executemanty来接收多条数据
cursor.executemany(insert_sql,name_a) #第一个参数用来接收指令，第二个参数用来接收可迭代对象
conn.commit()

cursor.close()
conn.close()

如果外界传入数据为多列的，只需要多增加几个%s即可,如下列代码所示

name_a=[('hx',14),('hhh',22),('sas',15)]
insert_sql="insert into aaa values(0,%s,%s,%s)" #0是占位用的，因为有id自增列

3）回滚事务

如果使用insert插入语句时出现错误，回滚事务，全部撤回

name_a=[('hx',14),('hhh',22),('sas',15)]
insert_sql="insert into aaa values(0,%s,%s,%s)" #0是占位用的，因为有id自增列

cursor.executement(insert_sql,name_a)
#判断，如果出现错误，回滚事务
try:
    conn.commit()
except Exception as e:
    conn.rollback()

cuesor.close()
conn.close()