mysql基本操作
- 接mysql
- 创建数据库
- 切换数据库
- 创建表
- 操作表:增删改查
连接mysql
命令:mysql -u用户名 -p密码 -P端口 -hIP 数据库名称
举例:mysql -uroot -proot -P3306 -h127.0.0.1 my
创建数据库
命令:create database 数据库名称
举例:create database mydb
1
切换数据库
show databases;
use mydb;
查看和创建表
show tables;
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
`username` varchar(20) NOT NULL,
`password` varchar(20) NOT NULL,
`id` int(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
8
操作表:增删改查
insert into admin (username,password) values("hello","hello1");
delete from admin where id=1;
update admin set password="hello2" where username="hello";
select * from admin;
MySQLdb
- MySQLdb简介
- 安装
- 基本操作
MySQLdb简介
用于Python链接MySQL数据库的接口,它实现了Python的MySQL数据库接口规范
安装:
Windows下安装MySQLdb:
下载地址:http://www.codegood.com/archives/4
1、如果下载的exe文件,跟Windows普通软件安装方法一样,一直下一步下一步就行;(注意:看本地安装的Python是32位还是64位,下载对应的exe)
2、如果下载的压缩文件,解压,cmd下进入setup.py所在目录,执行如下命令:
Python setup.py install
Linux下安装MySQLdb:
下载地址:https://pypi.python.org/pypi/MySQL-python
选择适合机器的源码.tar.gz安装包,然后按如下操作(确保账号有安装权限):
1)gunzip MySQL-python-1.2.2.tar.gz
2)tar -xvf MySQL-python-1.2.2.tar
3)cd MySQL-python-1.2.2
4)python setup.py build
5)python setup.py install
检测安装成功否
进入Python交互模式,执行import MySQLdb,如果没报错,说明安装成功。
基本操作
流程:
导入包-》建立连接(connection)-》游标-》操作数据(增/删/改查)-》获取结果-》
-》关闭游标-》关闭连接
代码示例
import MySQLdb
def insert():
conn=MySQLdb.connect(host='127.0.0.1',port=3306,user='root',passwd='root',db='mydb',charset='utf8')
cur=conn.cursor()
sql='insert into userInfo(userName,userAge,userSex,userAddress) values(%s,%s,%s,%s)'
params=("name",25,"女","北京")
reCount=cur.execute(sql,params)
# insertSql1='insert into userInfo VALUEs(%s,%s,%s,%s,%s)'
# insertResult1=cur.executemany(insertSql1,[(12,"name",25,"女","北京"),(13,"name",25,"女","北京"),(14,"name",25,"女","北京")])
# print insertResult1
conn.commit()
cur.close()
conn.close()
print reCount
def delete():
conn=MySQLdb.connect(host='127.0.0.1',port=3306,user='root',passwd='root',db='mydb',charset='utf8')
cur=conn.cursor()
sql='delete from userInfo where id= %s'
params=(5,)
reCount=cur.execute(sql,params)
conn.commit()
cur.close()
conn.close()
print reCount
def update():
conn=MySQLdb.connect(host='127.0.0.1',port=3306,user='root',passwd='root',db='mydb',charset='utf8')
cur=conn.cursor()
sql='update userInfo set userName=%s where id=6'
params=('Na1',)
reCount=cur.execute(sql,params)
conn.commit()
cur.close()
conn.close()
print reCount
def select():
conn=MySQLdb.connect(host='127.0.0.1',port=3306,user='root',passwd='root',db='mydb',charset='utf8')
cur=conn.cursor()
cur=conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)#通过字典方式获取数据,这样可以把列名也展示出来
sql='select * from userinfo'
reCount=cur.execute(sql)
reData=cur.fetchall()
conn.commit()
cur.close()
conn.close()
# print reCount
print reData
def manyInsert():
conn=MySQLdb.connect(host='127.0.0.1',port=3306,user='root',passwd='root',db='mydb',charset='utf8')
cur=conn.cursor()
sql='insert into userinfo (userName,userAge,userSex,userAddress) values (%s,%s,%s,%s)'
li=[
('n2',20,'nv','here1'),
('n3',23,'nv','here2'),
('n4',24,'nv','here3')
]
reCount=cur.executemany(sql,li)
conn.commit()
cur.close()
conn.close()
print reCount
if __name__ == '__main__':
select()
# update()
# delete()
# insert()
# manyInsert()