一、數(shù)據(jù)庫(kù)基本操作
1. 想允許在數(shù)據(jù)庫(kù)寫(xiě)中文，可在創(chuàng)建數(shù)據(jù)庫(kù)時(shí)用下面命令
create database zcl charset utf8;
2. 查看students表結(jié)構(gòu)
desc students;
3. 查看創(chuàng)建students表結(jié)構(gòu)的語(yǔ)句
show create table students;
4. 刪除數(shù)據(jù)庫(kù)
drop database zcl;
5. 創(chuàng)建一個(gè)新的字段
alter table students add column nal char(64);
PS: 本人是很討厭上面這種“簡(jiǎn)單解釋+代碼”的博客。其實(shí)我當(dāng)時(shí)在mysql終端寫(xiě)了很多的實(shí)例，不過(guò)因?yàn)楫?dāng)時(shí)電腦運(yùn)行一個(gè)看視頻的軟件，導(dǎo)致我無(wú)法Ctrl+C/V。現(xiàn)在懶了哈哈~~
二、python連接數(shù)據(jù)庫(kù)
python3不再支持mysqldb。其替代模塊是PyMySQL。本文的例子是在python3.4環(huán)境。
1. 安裝pymysql模塊
pip3 install pymysql
2. 連接數(shù)據(jù)庫(kù)，插入數(shù)據(jù)實(shí)例
|
1
2
3
4
5
6
7
8
9
10
11
12
|
import pymysql#生成實(shí)例，連接數(shù)據(jù)庫(kù)zclconn = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='zcl')#生成游標(biāo)，當(dāng)前實(shí)例所處狀態(tài)cur = conn.cursor()#插入數(shù)據(jù)reCount = cur.execute('insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)',('Jack','man',25,1351234,"CN"))reCount = cur.execute('insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)',('Mary','female',18,1341234,"USA"))conn.commit() #實(shí)例提交命令 cur.close()conn.close()print(reCount) |
查看結(jié)果:
|
1
2
3
4
5
6
7
8
|
mysql> select* from students;+----+------+-----+-----+-------------+------+| id | name | sex | age | tel | nal |+----+------+-----+-----+-------------+------+| 1 | zcl | man | 22 | 15622341234 | NULL || 2 | alex | man | 30 | 15622341235 | NULL |+----+------+-----+-----+-------------+------+rows in set |
3. 獲取數(shù)據(jù)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
import pymysqlconn = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='zcl')cur = conn.cursor()reCount = cur.execute('select* from students')res = cur.fetchone() #獲取一條數(shù)據(jù)res2 = cur.fetchmany(3) #獲取3條數(shù)據(jù)res3 = cur.fetchall() #獲取所有(元組格式)print(res)print(res2)print(res3)conn.commit()cur.close()conn.close() |
輸出:
|
1
2
3
|
(1, 'zcl', 'man', 22, '15622341234', None)((2, 'alex', 'man', 30, '15622341235', None), (5, 'Jack', 'man', 25, '1351234', 'CN'), (6, 'Mary', 'female', 18, '1341234', 'USA'))() |
三、事務(wù)回滾
事務(wù)回滾是在數(shù)據(jù)寫(xiě)到數(shù)據(jù)庫(kù)前執(zhí)行的，因此事務(wù)回滾conn.rollback()要在實(shí)例提交命令conn.commit()之前。只要數(shù)據(jù)未提交就可以回滾，但回滾后ID卻是自增的。請(qǐng)看下面的例子:
插入3條數(shù)據(jù)(注意事務(wù)回滾):
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
import pymysql#連接數(shù)據(jù)庫(kù)zclconn=pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='zcl')#生成游標(biāo)，當(dāng)前實(shí)例所處狀態(tài)cur=conn.cursor()#插入數(shù)據(jù)reCount=cur.execute('insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)', ('Jack', 'man', 25, 1351234, "CN"))reCount=cur.execute('insert into students(name, sex, age, tel, nal) values(%s,%s,%s,%s,%s)', ('Jack2', 'man', 25, 1351234, "CN"))reCount=cur.execute('insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)', ('Mary', 'female', 18, 1341234, "USA"))conn.rollback() #事務(wù)回滾conn.commit() #實(shí)例提交命令 cur.close()conn.close()print(reCount) |
未執(zhí)行命令前與執(zhí)行命令后(包含回滾操作)(注意ID號(hào)): 未執(zhí)行上面代碼與執(zhí)行上面代碼的結(jié)果是一樣的!!因?yàn)槭聞?wù)已經(jīng)回滾，故students表不會(huì)增加數(shù)據(jù)!
|
1
2
3
4
5
6
7
8
9
10
|
mysql> select* from students;+----+------+--------+-----+-------------+------+| id | name | sex | age | tel | nal |+----+------+--------+-----+-------------+------+| 1 | zcl | man | 22 | 15622341234 | NULL || 2 | alex | man | 30 | 15622341235 | NULL || 5 | Jack | man | 25 | 1351234 | CN || 6 | Mary | female | 18 | 1341234 | USA |+----+------+--------+-----+-------------+------+rows in set |
執(zhí)行命令后(不包含回滾操作):只需將上面第11行代碼注釋。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql> select* from students;+----+-------+--------+-----+-------------+------+| id | name | sex | age | tel | nal |+----+-------+--------+-----+-------------+------+| 1 | zcl | man | 22 | 15622341234 | NULL || 2 | alex | man | 30 | 15622341235 | NULL || 5 | Jack | man | 25 | 1351234 | CN || 6 | Mary | female | 18 | 1341234 | USA || 10 | Jack | man | 25 | 1351234 | CN || 11 | Jack2 | man | 25 | 1351234 | CN || 12 | Mary | female | 18 | 1341234 | USA |+----+-------+--------+-----+-------------+------+rows in set |
總結(jié)：雖然事務(wù)回滾了，但I(xiàn)D還是自增了，不會(huì)因回滾而取消，但這不影響數(shù)據(jù)的一致性（底層的原理我不清楚~）
四、批量插入數(shù)據(jù)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
import pymysql#連接數(shù)據(jù)庫(kù)zclconn = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='zcl')#生成游標(biāo)，當(dāng)前實(shí)例所處狀態(tài)cur = conn.cursor()li = [ ("cjy","man",18,1562234,"USA"), ("cjy2","man",18,1562235,"USA"), ("cjy3","man",18,1562235,"USA"), ("cjy4","man",18,1562235,"USA"), ("cjy5","man",18,1562235,"USA"),]#插入數(shù)據(jù)reCount = cur.executemany('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)', li)#conn.rollback() #事務(wù)回滾conn.commit() #實(shí)例提交命令cur.close()conn.close()print(reCount) |
pycharm下輸出: 5
mysql終端顯示:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
mysql> select* from students; #插入數(shù)據(jù)前+----+-------+--------+-----+-------------+------+| id | name | sex | age | tel | nal |+----+-------+--------+-----+-------------+------+| 1 | zcl | man | 22 | 15622341234 | NULL || 2 | alex | man | 30 | 15622341235 | NULL || 5 | Jack | man | 25 | 1351234 | CN || 6 | Mary | female | 18 | 1341234 | USA || 10 | Jack | man | 25 | 1351234 | CN || 11 | Jack2 | man | 25 | 1351234 | CN || 12 | Mary | female | 18 | 1341234 | USA |+----+-------+--------+-----+-------------+------+rows in setmysql> mysql> select* from students; #插入數(shù)據(jù)后+----+-------+--------+-----+-------------+------+| id | name | sex | age | tel | nal |+----+-------+--------+-----+-------------+------+| 1 | zcl | man | 22 | 15622341234 | NULL || 2 | alex | man | 30 | 15622341235 | NULL || 5 | Jack | man | 25 | 1351234 | CN || 6 | Mary | female | 18 | 1341234 | USA || 10 | Jack | man | 25 | 1351234 | CN || 11 | Jack2 | man | 25 | 1351234 | CN || 12 | Mary | female | 18 | 1341234 | USA || 13 | cjy | man | 18 | 1562234 | USA || 14 | cjy2 | man | 18 | 1562235 | USA || 15 | cjy3 | man | 18 | 1562235 | USA || 16 | cjy4 | man | 18 | 1562235 | USA || 17 | cjy5 | man | 18 | 1562235 | USA |+----+-------+--------+-----+-------------+------+rows in set |
以上就是本文的全部?jī)?nèi)容，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來(lái)一定的幫助，同時(shí)也希望多多支持服務(wù)器之家！
原文鏈接：http://www.cnblogs.com/0zcl/p/6477042.html
