Python学习笔记——进阶篇【第九周】———MYSQL操作

Mysql 增删改查操作

查看数据库
show databases;

创建数据库并允许中文插入
create database s12day9 charset utf8;

使用数据库
use s12day9;

查看表
show tables;

创建表
create table students
    (
        id int  not null auto_increment primary key,
        name char(32) not null,
        sex char(20) not null,
        age tinyint unsigned not null,
        tel char(13) null default "-"
    );

查看表结构
desc students; 

查看创建sql的语句（查看别人创建的sql语句,InnoDB 支持事物操作）
show create table students;  

插入数据
insert into students(name,sex,age,tel) values('alex','man',18,'151515151')

删除数据
delete from students where id =2;
 
修改数据
update students set name = 'sb' where id =1;
 
查询数据
select * from students 

查询年龄大于20学生的所有数据
select * from students where age> 20;

查询年龄大于20男生的所有数据
select * from students where age> 20 and sex='man';

查询年龄中含1学生的所有数据(模糊查询，%代表所有)
selsct * from students where age like "1%";

查询年龄中含1学生的姓名和性别（模糊查询，%代表所有）
select name,sex from students where age like "1%";

修改数据
update students set age =26 where name ='alex';

查询数据
select * from students 

批量修改
update students set age=26;

删除数据
delete from students where name='rain';

插入字段
alter table students add colum nal char(64)；

查看表结构
desc students;

python MySQL API

import MySQLdb
  
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
  
cur = conn.cursor()
  
reCount = cur.execute('insert into UserInfo(Name,Address) values(%s,%s)',('alex','usa'))
# reCount = cur.execute('insert into UserInfo(Name,Address) values(%(id)s, %(name)s)',{'id':12345,'name':'wupeiqi'})
  
conn.commit()
  
cur.close()
conn.close()
  
print reCount

import MySQLdb

conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')

cur = conn.cursor()

li =[
     ('alex','usa'),
     ('sb','usa'),
]
reCount = cur.executemany('insert into UserInfo(Name,Address) values(%s,%s)',li)

conn.commit()
cur.close()
conn.close()

print reCount

import MySQLdb
 
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
 
cur = conn.cursor()
 
reCount = cur.execute('delete from UserInfo')
 
conn.commit()
 
cur.close()
conn.close()
 
print reCount

import MySQLdb
 
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
 
cur = conn.cursor()
 
reCount = cur.execute('update UserInfo set Name = %s',('alin',))
 
conn.commit()
cur.close()
conn.close()
 
print reCount

# ##################### fetchone 取一条数据/fetchmany(num)  指定取几条数据#####################

import MySQLdb
 
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
cur = conn.cursor()
 
reCount = cur.execute('select * from UserInfo')
 
print cur.fetchone()
print cur.fetchone()
cur.scroll(-1,mode='relative')
print cur.fetchone()
print cur.fetchone()
cur.scroll(0,mode='absolute')
print cur.fetchone()
print cur.fetchone()
 
cur.close()
conn.close()
 
print reCount
 
 
 
# ###################### fetchall  取所有数据##############################
 
import MySQLdb
 
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
#cur = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
cur = conn.cursor()
 
reCount = cur.execute('select Name,Address from UserInfo')
 
nRet = cur.fetchall()
 
cur.close()
conn.close()
 
print reCount
print nRet
for i in nRet:
    print i[0],i[1]

事物的回滚（插入2条数据——回滚——提交）

import MySQLdb

conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
cur = conn.cursor()
reCount = cur.execute('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)',('Jack','F',22,12342345,'name',"CN"))
reCount = cur.execute('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)',('Rachel','F',26,35642345,'name',"CN"))
conn.rollback()
conn.commit()

cur.close()
conn.close()

print reCount

SQL的详细讲解：http://www.cnblogs.com/wupeiqi/articles/5095821.html

SQL的基本使用：http://www.cnblogs.com/Eva-J/p/5133716.html