Mysql操作：
grant select,insert,update,delete on *.* to root@"%" Identified by "123456"; #授权远程访问
create database s12day9 charset utf8; #创建支持中文的数据库
创建表：
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; #显示表结构
mysql> desc students;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(32) | NO | | NULL | |
| sex | char(20) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| tel | char(13) | YES | | - | |
+-------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
show create table students; #查看一个表创建时候的命令
mysql> show create table students\G;
*************************** 1. row ***************************
Table: students
Create Table: CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(32) NOT NULL,
`sex` char(20) NOT NULL,
`age` tinyint(3) unsigned NOT NULL,
`tel` char(13) DEFAULT '-',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
insert into students(name,sex,age,tel) values('tom','man',28,'18600033313');
insert into students(name,sex,age,tel) values('jack','man',18,'18600033312'); #插入数据
mysql> insert into students(name,sex,age,tel) values('tom','man',20,'');
Query OK, 1 row affected (0.00 sec)
select * from students; #查询数据
+----+------+-----+-----+-------------+
| id | name | sex | age | tel |
+----+------+-----+-----+-------------+
| 1 | alex | man | 18 | 151515151 |
| 2 | jack | man | 18 | 18600033312 |
| 3 | tom | man | 28 | 18600033313 |
| 4 | tom | man | 20 | 18600033313 |
+----+------+-----+-----+-------------+
4 rows in set (0.00 sec)
select * from students where age > 18 and name = "tom"; #组合条件查询
mysql> select * from students where age > 18 and name = "tom";
+----+------+-----+-----+-------------+
| id | name | sex | age | tel |
+----+------+-----+-----+-------------+
| 3 | tom | man | 28 | 18600033313 |
| 4 | tom | man | 20 | 18600033313 |
+----+------+-----+-----+-------------+
2 rows in set (0.00 sec)
select * from students where age like "2%"; #模糊查询
mysql> select * from students where age like "2%";
+----+------+-----+-----+-------------+
| id | name | sex | age | tel |
+----+------+-----+-----+-------------+
| 3 | tom | man | 28 | 18600033313 |
| 4 | tom | man | 20 | 18600033313 |
+----+------+-----+-----+-------------+
2 rows in set (0.00 sec)
update students set name = "aaa" where name = "alex"; #更新数据
mysql> update students set name = "aaa" where name = "alex";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
update students set age = 25; #批量改数据
mysql> update students set age = 25;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
delete from students where name = "aaa"; #删除数据
mysql> delete from students where name = "aaa";
Query OK, 1 row affected (0.00 sec)
alter table students add column nal char(32); #插入一个字段:
mysql> select * from students;
+----+------+-----+-----+-------------+------+
| id | name | sex | age | tel | nal |
+----+------+-----+-----+-------------+------+
| 2 | jack | man | 25 | 18600033312 | NULL |
| 3 | tom | man | 25 | 18600033313 | NULL |
| 4 | tom | man | 25 | 18600033313 | NULL |
+----+------+-----+-----+-------------+------+
3 rows in set (0.00 sec)
Mysql操作数据库：
pyton3操作数据库：使用pymysql模块：
import pymysql conn = pymysql.connect(host="10.16.59.102",user="root",passwd="",db="s12day9") #定义主机地址、端口、用户和密码
cur = conn.cursor() #连接数据库之后的状态，游标，现在在是在s10day12
reCount = cur.execute('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)',('Rachel','F',26,3663,"US")) #添加数据
reCount = cur.execute('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)',('Jack','F',23,135343,"CN")) #添加数据
conn.commit() #提交更改，在python 3不提交也是写入到数据库的
conn.close() #关闭conn连接
print(reCount)
python2操作数据库：使用MySQLdb模块：
#/usr/bin/env python
# -*- coding:utf-8 -*- import MySQLdb
conn = MySQLdb.connect(host="localhost",user="root",passwd="zhang@123",db="s11day12") #实例化对象并定义主机地址、端口、用户和密码
cur = conn.cursor() #连接数据库之后的状态，游标，现在在是在当前数据库之上 #reCount = cur.execute('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)',('Jack1','F',3,135343,"CN")) #reCount = cur.execute('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)',('Rachel1','F',6,3663,"US"))
reCount = cur.execute("select * from students")
data = cur.fetchall() #取到的所有数据
print(data) data1 = data[0] #取获取到的第一个数据
print(list(data1)) #将获取到的数据转换为列表，以方便做下一步操作 cur.close() #关闭游标
conn.close() #关闭会话 执行结果：
C:\Python27\python.exe C:/Users/zhang/PycharmProjects/untitled1/python_mysql.py
((33L, 'tom', 'F', 26, '', 'US'), (73L, 'Rachel', 'F', 6, '', 'US'), (72L, 'Jack', 'F', 3, '', 'CN'), (74L, 'Jack', 'F', 3, '', 'CN'), (75L, 'Rachel', 'F', 6, '', 'US'), (76L, 'Jack1', 'F', 3, '', 'CN'), (77L, 'Rachel1', 'F', 6, '', 'US'))
[33L, 'tom', 'F', 26, '', 'US']
批量插入数据：
#/usr/bin/env python
# -*- coding:utf-8 -*- import MySQLdb
conn = MySQLdb.connect(host="localhost",user="root",passwd="zhang@123",db="s11day12") #实例化对象并定义主机地址、端口、用户和密码
cur = conn.cursor() #连接数据库之后的状态，游标，现在在是在当前数据库之上
data =[
('a1','M',11,111,'CN'),
('a2','F',19,111,'JP'),
('a3','F',11,111,'USA'),
('a4','F',11,555,'UK'),
] #将列表插入到数据库，数据格式与表中的格式需一致 reCount = cur.executemany('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)',li) #使用sql通过Python模块将数据插入到MySQL，值为变量，会将后面的列表data传递进去
cur.close() #关闭游标
conn.close() #关闭会话
print(reCount)
事物回滚：
回滚需要表的引擎必须为InnoDB才行，因为MySAM是无事物引擎
#/usr/bin/env python
# -*- coding:utf-8 -*- import MySQLdb
conn = MySQLdb.connect(host="localhost",user="root",passwd="zhang@123",db="s11day12") #实例化对象并定义主机地址、端口、用户和密码
cur = conn.cursor() #连接数据库之后的状态，游标，现在在是在当前数据库之上 reCount = cur.execute('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)',('Jack1','F',3,135343,"CN"))
reCount = cur.execute('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)',('Rachel1','F',6,3663,"US")) conn.rollback()
conn.commit() cur.close() #关闭游标
conn.close() #关闭会话
print(reCount)