#本文主要介绍MySQLdb和MySQL常见用法
##MySQL常见用法
创建数据库 create database if not exists student;
删除数据库 drop database if exists student;
查看所有数据库 show databases;
选择数据库 use student;
创建数据表 create table tbl_score(
rl_sName varchar(20) not null,
rl_uScore int(3) not null,
primary key(rl_sName)
);
删除数据表 drop table tbl_score;
显示所有数据表 show tables;
显示表结构 desc tbl_score;
显示创建表过程(语法) show create table tbl_score;
增加一条数据 insert into tbl_score(rl_sName,rl_uScore) values("zuiaiqun",100);
显示表内容 select * from tbl_score;
修改一条数据 update tbl_score set rl_uScore=99 where rl_sName="zuiaiqun";
删除某一条数据 delete from tbl_score where rl_sName="zuiaiqun";
修改表结构 alter table tbl_score add rl_uSex int(3) not null after rl_uScore;
---
比较容易弄错的是“修改表结构”和“增加一条数据”这两个指令。。
##MySQLdb用法
保证你已经安装了MySQLdb模块,文件可以在此处下载http://sourceforge.net/projects/mysql-python/
使用例子:
#encoding:utf-8
import MySQLdb
def TestMySQLdb():
#连接MySQL
conn=MySQLdb.connect(host="localhost",user="root",passwd="zuiaiqun",db="student")
cursor=conn.cursor()
sSQL="drop table tbl_role"
try:
cursor.execute(sSQL)
except:
pass
#创建数据表
sSQL='''create table tbl_role(
rl_uID int(10) not null,
rl_sName varchar(20) not null,
primary key(rl_uID)
)
'''
try:
cursor.execute(sSQL)
except:
pass
#插入数据
sSQL="insert into tbl_role(rl_uID,rl_sName) values(%s,\"%s\")"
for uid in xrange(1,20):
sName="zuiaiqun%d"%uid
sExecute=sSQL%(uid,sName)
cursor.execute(sExecute)
sSQL="select * from tbl_role"
cursor.execute(sSQL)
tRes=cursor.fetchall()
for tVal in tRes: #tVal与select相关,由于是select *,所以tVal=(rl_uID,rl_sName)
print tVal
sSQL="select rl_uID from tbl_role"
cursor.execute(sSQL)
tRes=cursor.fetchall()
for tVal in tRes: #tVal与select相关,由于是select rl_uID,所以tVal=(rl_uID,)
print tVal
conn.commit() #测试发现如果有修改表的数据(insert into),得执行commit,否则无法成功修改数据库
cursor.close()
conn.close()
if __name__=="__main__":
TestMySQLdb()
##结果: