关于MySQl数据库
声明:如果是首次使用Mysql,则获取的数据库和以下的不同,新创建的数据库没有表,是一个空数据库
所有的语句执行完,最后不要忘记关闭数据库
db.close();
一、连接MySQL
import pymysql
# 连接数据库
db = pymysql.connect("localhost",'root','111')
二、查看数据库,并输出所有的数据库
# 查看所有的数据库
sql = "show databases;"
cursor.execute(sql)
# 输出所有的信息
all_db = cursor.fetchall()
for i in all_db:
print(i)
运行结果如下,显示所有的已创建的数据库:
MySQl中显示的所有已创建的数据库
三、选择数据库并显示所有的表
# 选择其中的一个数据库
sql1 = "use library;"
cursor.execute(sql1)
# 输出此数据库中的所有表
sql2 = "show tables;"
cursor.execute(sql2)
all_lib_t = cursor.fetchall()
for j in all_lib_t:
print(j)
输出结果如下:
四、删除指定的数据库
声明:为了防止数据丢失,删除了一个没有表的数据库,db_01是创建的一个数据库
接下来删除db_01
# 删除数据库
sql3 = "drop database db_01;"
cursor.execute(sql3)
# 查看是否删除成功
sql = "show databases;"
cursor.execute(sql)
# 输出所有的信息
all_db = cursor.fetchall()
for i in all_db:
print(i)
没有了db_01,删除成功
由于已经删除了db_01,顾在这个环境下删除一个不存在的数据库会报错。
五、创建新的数据库
# 创建一个数据库
sql4 = "create database if not exists db_1;"
cursor.execute(sql4)
# 显示所有的数据库,看是否创建成功
cursor.execute("show databases;")
all_db = cursor.fetchall()
for i in all_db:
print(i)# 遍历输出所有的数据库
六、创建数据表
## 选择db_1数据库
sql5 = "use db_1;"
cursor.execute(sql5)
# 在数据库db_1中创建一个表 t_1
# python中‘\(反斜杠)’实现多行语句
sql6 = "create table t_1(id int(4) primary key, \
bookname varchar(20) not null, \
author varchar(10) not null \
);"
cursor.execute(sql6)
七、查看表结构
sql7 = "desc t_1;"
cursor.execute(sql7)
r = cursor.fetchall()
print(r)
如果采用如下方式,则会报错,错误的返回内容是自己设置的
为什么会报错呢,因为还没有往表里添加记录。
try:
sql7 = "desc t_1;"
cursor.execute(sql7)
result = cursor.fetchall()
for row in result:
id1 = row[0]
bookname = row[1]
author = row[2]
print("id=%d,bookname=%s,author=%s"%(id1,bookname,author))
except:
print ("Error: unable to fetch data")
查看表结构,MySql有两种查询方式:
- describe table_name; # descirbe可以缩写为desc
- show columns from table_name from db_name;
八、修改表结构
MySQl语句使用alter table来对表结构进行修改
修改表结构
# 将money字段添加进去
sql9 = "alter table t_1 add money int(5) not null;"
sql10 = "alter table t_1 add money1 int(5) not null;"
cursor.execute(sql9)
cursor.execute(sql10)
# 该语句不会有返回值
# 将author字段的varchar(10)修改成varchar(15)
sql11 = "alter table t_1 modify author varchar(15);"
cursor.execute(sql11)
九、重命名表
# 重命名数据表
#sql12 = "rename table t_1 to new_t1;"
#cursor.execute(sql12)
# 输出此数据库中的所有表,看是否更改成功
sql2 = "show tables;"
cursor.execute(sql2)
all_lib_t = cursor.fetchall()
for j in all_lib_t:
print(j)
十、删除表
# 删除表
sql13 = "drop table if exists new_t1;"
cursor.execute(sql13)
# 输出此数据库中的所有表
sql2 = "show tables;"
cursor.execute(sql2)
all_lib_t = cursor.fetchall()
for j in all_lib_t:
print(j)
十一、插入记录
MySQL使用insert into values来实现插入记录的功能
插入记录首先要有一张表,借用前面的代码
## 在数据库db_1中创建一个表 t_1
# python中‘\(反斜杠)’实现多行语句
sql6 = "create table t_1(id int(4) primary key, \
bookname varchar(20) not null, \
author varchar(10) not null \
);"
cursor.execute(sql6)
sql14 = "insert into t_1(id,bookname,author) values(3,'Spirited away','Hayao');"
# 在python中,双引号不能包含双引号,汇报无效语法错误
# sql14 = "insert into t_1(id,bookname,author) values(3,"Spirited away books","Miyazaki Hayao");"
cursor.execute(sql14)
db.commit() # 这句话很重要的, 提交当前事务
try:
sql8 = "select * from t_1"
cursor.execute(sql8)
q = cursor.fetchall()
for i in q:
id1 = i[0]
bookname = i[1]
author = i[2]
print("id=%d,bookname=%s,author=%s"%(id1,bookname,author))
except:
print ("Error: unable to fetch data")
十二、查询数据库记录
1.查询所有的列
sql = " select * from t_1;"
try:
sql8 = "select * from t_1;"
cursor.execute(sql8)
q = cursor.fetchall()
for i in q:
id1 = i[0]
bookname = i[1]
author = i[2]
print("id=%d,bookname=%s,author=%s"%(id1,bookname,author))
except:
print ("Error: unable to fetch data")
声明:如果不加db.commit() ,则用python添加的记录在mysql中是查询不到的
2. 查询指定的列
try:
sql8 = "select id,bookname from t_1;"
cursor.execute(sql8)
q = cursor.fetchall()
for i in q:
id1 = i[0]
bookname = i[1]
print("id=%d,bookname=%s,"%(id1,bookname))
except:
print ("Error: unable to fetch data")
3. 从表中查询
查询内容来自两张至多张表
需要再创建一个新的表
sql6 = "create table t_info(id int(4) primary key, \
bookname varchar(20) not null, \
price int(5) not null \
);"
cursor.execute(sql6)
sql16 = "insert into t_info(id,bookname,price) values(1,'Spirited',15);"
cursor.execute(sql16)
db.commit()
sql17 = "insert into t_info(id,bookname,price) values(2,'away',30);"
cursor.execute(sql17)
db.commit()
sql18 = "insert into t_info(id,bookname,price) values(3,'Spirited away',25);"
cursor.execute(sql18)
db.commit()
sql19 = "select t_1.author,t_info.price,t_1.bookname from t_1,t_info where t_1.id=t_info.id;"
cursor.execute(sql19)
s = cursor.fetchall()
for i in s:
print(i)
4.where条件语句
sql20 = "select t_1.bookname from t_1,t_info where t_1.id=t_info.id and t_info.price<=15;"
cursor.execute(sql20)
s = cursor.fetchall()
for i in s:
print(i)
5. group by划分分组
1>group by 与聚合函数一起使用时,则分组后计算
2>group by 与having一起使用时,则分组后过滤
#group
sql21 = "select bookname,author from t_1 group by author;"
cursor.execute(sql21)
s = cursor.fetchall()
for i in s:
print(i)
6. distinct 去除结果中重复的行
distinct
sql22 = "select distinct author from t_1;"
cursor.execute(sql22)
s = cursor.fetchall()
for i in s:
print(i)
7. order by 对结果进行升、降序排序
sql23 = "select bookname,price from t_info order by price;"
cursor.execute(sql23)
s = cursor.fetchall()
for i in s:
print(i)
8. like 实现模糊查询
like : % 表示一个或多个字符,‘_’表示一个字符,中英文没有区别
# like
sql24 = "select * from t_1 where author like 'H%';"
cursor.execute(sql24)
s = cursor.fetchall()
for i in s:
print(i)
9. concat 联合多个字段,构成一个总字符串
sql25 = "select concat( bookname,':',author ) as info from t_1;"
cursor.execute(sql25)
s = cursor.fetchall()
for i in s:
print(i)
10.limit 限定查询结果的记录条数,控制输出行数
limit后面跟的两个参数,第一个是开始的位置(类似索引,从0开始),第二个是输出的元组的个数
# limit
sql26 = "select * from t_1 where id limit 0,2;"
cursor.execute(sql26)
s = cursor.fetchall()
for i in s:
print(i)
11.使用函数和表达式
可以使用聚合函数,如:AVG(),sum(),max(),min(),count()……
# 使用函数和表达式
sql27 = “select *,AVG(price) as average from t_info;”
cursor.execute(sql27)
s = cursor.fetchall()
for i in s:
print(i)
sql28 = “select ,(price0.8) as discount from t_info;”
cursor.execute(sql28)
s = cursor.fetchall()
for i in s:
print(i)
十三、修改记录
# 修改记录
sql29 = "update t_1 set bookname='Python' where author='xiaomi';"
cursor.execute(sql29)
db.commit() # 切记,一定不要忘记这句话。
十四、删除记录
# 删除记录
sql29 = "delete from t_1 where bookname='Python';"
cursor.execute(sql29)
db.commit();
# 查看是否删除成功
try:
sql8 = "select * from t_1;"
cursor.execute(sql8)
q = cursor.fetchall()
for i in q:
id1 = i[0]
bookname = i[1]
author = i[2]
print("id=%d,bookname=%s,author=%s"%(id1,bookname,author))
except:
print ("Error: unable to fetch data")
sql30 = "delete from t_1;"
cursor.execute(sql30)
db.commit();
try:
sql8 = "select * from t_1;"
cursor.execute(sql8)
q = cursor.fetchall()
for i in q:
id1 = i[0]
bookname = i[1]
author = i[2]
print("id=%d,bookname=%s,author=%s"%(id1,bookname,author))
except:
print ("Error: unable to fetch data")
十五、练习:操作teacher表
创建teacher表,并将表中的name字段的数据类型改为varchar(30),将birthday字段的位置改到sex字段的前面
# 创建teacher表
# 在数据库db_1中创建一个表 teacher
# python中‘\(反斜杠)’实现多行语句
sql6 = "create table teacher(num int(10) not null, \
name varchar(20) not null, \
sex varchar(4) not null, \
birthday datetime, \
address varchar(50) \
);"
cursor.execute(sql6)
sql = "show tables;"
cursor.execute(sql)
s = cursor.fetchall()
for i in s:
print(i)
sql = "alter table teacher modify name varchar(30);"
cursor.execute(sql)
sql1 = "alter table teacher modify birthday datetime after name;"
cursor.execute(sql1)
sql1 = "alter table teacher modify birthday datetime after num;"
cursor.execute(sql1)
如果哪里有错,请提出,会更正