mysql與pymysql

mysql基本命令指令碼

關係型資料庫

1.支援複雜的sql語句查詢

2.支援事務

非關係型資料庫

1.NQSQL不需要經過SQL層的處理

2.可擴充套件性，因為是鍵值對的形式所以水平擴充套件非常容易

事務

事務是邏輯上的一組操作，組成這組操作的各個單元，要不全部成功，要不全部失敗，這個特性就是事務

注意:MYSQL支援事務，但是要求必須是innoDB引擎

解決這個問題： mysql的事務解決這個問題，因為mysql的事務特性，要求這組操作，要不全都成功，要不全都失敗，這樣就避免了某個操作成功某個操作失敗。利於資料的安全

如何使用：

1.在執行sql語句之前，我們要開啟事務start transaction； 2.正常執行我們的sql語句； 3.當sql語句執行完畢，存在2種情況 第一種;全部成功，我們講sql語句對資料庫造成的影響提交到資料庫中，commit 第二種；某些sql語句失敗，我們執行rollback（回滾），將對資料庫的操作趕緊撤銷

一 進入到資料庫

net start mysql57

-h host 主機名

-u user 使用者名稱

-p password 密碼

啟動資料庫

mysql -hlocalhost -uroot -p

注：root超級管理員 可以建立和管理其他的使用者,root使用者不可以遠端登入

二 對於庫的操作

CREATE 建立

DROP 刪除

ALTER 修改

SHOW 展示

(1) 檢視所有的資料庫

show databases；

(2) 建立資料庫

create database 庫名；

create database if not exists 庫名; 防止建立同名的庫出現錯誤

(3) 檢視創庫語句

show create database 庫名；

（4）使用資料庫(進入到當前的庫中)

use 庫名

（5）檢視當前所在的資料庫

select database

（6）資料庫的刪除

drop database 庫名

（7）建立資料庫並設定字符集

create database 庫名 character set utf8；

（8）建立資料庫 並設定字符集

create database 小杰 character 字符集；

三 對於表的操作

0.檢視所有的表

show tables；

1.建立表

mysql> create table if not exists fs(
    -> id int unsigned primary key auto_increment,
    -> username varchar(20),
    -> sex tinyint,
    -> age tinyint unsigned,
    -> info varchar(100)
    -> );
複製程式碼

2.檢視錶結構

desc 表名；

mysql> create table if not exists fs(
    -> id int unsigned primary key auto_increment,
    -> username varchar(20),
    -> sex tinyint,
    -> age tinyint unsigned,
    -> info varchar(100)
    -> );
複製程式碼

3.刪除表中的某個欄位

alter table 表名 drop 欄位名；

4.刪除表

drop table if exists 表名；刪除表如果該表存在

5.增加表中的資料

insert into 表名 欄位名 values(值)；

6.檢視錶資料

select * from 表名；

7.檢視創表語句

show create table 表名（\G）；(豎著檢視)

8.重新命名錶名

rename table 原表名 to 新表名;

9.修改表結構

alter table 表名 add|change|drop 欄位名 (型別);

四 MYSQL表的建立

欄位型別

(1) 整形

型別 大小 範圍 無符號範圍 用途 tinyint 1位元組 -128,127 0，255 小整數值 smallint 2位元組 -32768,32767 0，65535 大整數值 int 4位元組 2-》10位置 4... 10 位的 大整數值 float(m,n) 4個位元組 單精度浮點型 double(m,n) 8個位元組 雙精度浮點型 decimal(m,n) 根據儲存的值 小資料值（更加精準） 浮點數中的m代表當前儲存的長度 n代表小數的位數 m-n代表整數的位數 超出則報錯 整形後面的數字的意義： 整形後面給定數字 並不是限定當前儲存值的長度 並沒有任何的意義 除非配合可選引數 zerofill 零填充 才有意義 字串型別後面給定的長度 則是限制當前儲存資料的長度 整形預設長度會比本身長度大1，因為是符號位

(2) 字串型別

char和varchar的相同和不同點 ：

char和varchar的儲存長度都為0-255

char的執行效率高於varchar

varchar要比 char更節省儲存空間

當char儲存的值達不到指定的長度時 則使用空來佔位

enum和set區別

enum只能選擇其中的一個值

set可以選擇多個值 多個值使用逗號來隔開

(3) 時間和日期

五 欄位約束

unsigned 無符號 正數

只能用於數值型別 不允許出現負數 儲存長度會擴大一倍

zerofill 零填充

只能用於數值型別 當指定的位數不足的時候 零填充

default 預設值

如果當前欄位沒有傳值 則值為預設值 （不設定預設值 預設為null）

null 和 not null

預設為null 當不插入值則插入的為null，當設定當前欄位為 not null的時候

則該欄位必須傳值

comment 設定當前欄位的說明

auto_increment 自增

注意

SQL 語句以分號作為結束

SQL命令 不區分大小寫

資料庫的切換使用use

\c 撤銷當前命令

\G 豎著檢視

當遇到在終端中 不管怎樣輸入命令都不執行 name檢視一下左側 是否為->

在Windows下 不區分庫,表名的大小寫 但是在Ubuntu下區分

退出資料庫的幾種方式

\q exit quit

六 資料操作

1 增

a 全列插入

格式:insert into (表名) values(...);

說明:主鍵列是自動增長,但是在全列插入時需要佔位,通常使用0,插入成功以後以實際資料為準

b.預設插入

格式:insert into 表名 (欄位1,欄位2,...) values (...)

c.同時插入多條資料

格式:insert into 表名 values (...),(...),...

2.刪

格式:delete from 表名 where 條件;

示例:delete from student where id=4;

注意: !!沒有條件是全部刪除,慎用;

3改

格式:update 表名 set 欄位1=值1,欄位2=值2,.... where 條件

示例:update student set age=16 where id = 7;

注意:如果沒有條件,是全部欄位都修改;

4查

1.基本語法

格式:select * from 表名

說明:from 關鍵字後面是表名,表示資料來源於這張表

       select 後面寫表中的欄位名," * " 表示所有欄位名,  
       在select 後面欄位名,可以使用as為欄位名起別名
       這個別名顯示在結果集中
       如果要查詢多個欄位,直接使用逗號分隔
複製程式碼

示例:select name,age from student; select name as a,age from student;

2.消除重複行

在select後面欄位名的前面使用distinct可以消除重複的行

示例:select distinct genger from student

3.條件查詢

a.語法: select * from 表名 where 條件;

b.比較運算子

 等於
 大於
 小於
 大於等於
 小於等於
 不等於
複製程式碼

需求:查詢id大於8的所有資料

示例:select * from student where id > 8;

邏輯運算子

and or not

需求;查詢id>7的女同學

示例;select * from student where id>7 and gender=0;

d.模糊查詢

like %:表示任意多個任意字元 :表示任意一個字元 需求:查詢姓習的同學 示例:select * from student where name like "習%"; 示例:select * from student where name like "習";

e.範圍查詢

in:表示在一個非連續的範圍內

between..and...:表示在一個連續的範圍內

需求:查詢編號為8,10,12的學生

示例:select * from student where id in(8,10,12);

需求;查詢編號為6-8的學生

示例; select * from student where id between 6 and 8;

f:空判斷

注意:null與" "是不同的

判斷空: isnull

判斷非空: is not null

需求:查詢沒有地址的同學 示例;select * from student where address is null

g:優先順序

小括號,not 比較運算子,邏輯運算子 and比or優先順序高

4.聚合

為了快速得到統計的資料,提供了5個聚合函式

a.   count(*)   表示計算總行數,括號中可以寫*和欄位名;
b.   max(列)   表示求此列的最大值
c.   min(列)   表示求此列的最小值
d.   sum(列)   表示求此列的和
e.   avg(列)   表示求此列的平均值
複製程式碼

需求:查詢學生總數:

示例:select count(*) from student;

需求:查詢女生編號的最大值;

示例:select max(id) from student where gender=0;

需求:查詢女生編號的最小值;

示例:select min(id) from student where gender=0;

需求:查詢所有學生的年齡和

示例:select sum(age) from student;

需求:查詢所有學生的年齡平均值

示例:select avg(age) from student;

5 分組

按照欄位分組,表示此欄位相同的資料會被放到一個集合中,分組後,只能查詢出相同的資料列。對於有差異的資料列,無法顯示在結果集中,可以對分組後的資料進行統計,做聚合運算

語法:select 列1,列2,聚合...... from 表名 group by 列1,列2,列3,......

需求:查詢男女生總數

示例:select gender,count(*) from student group by gender;

python操作mysql

python操作mysql步驟

import pymysql

(1) 連結mysql資料庫

db = pymysql.connect(主機名,使用者名稱,密碼,資料庫名)

(2) 設定字元編碼

db.set_charset('utf8')

(3) 建立遊標物件

cursor = db.cursor()

(4) 準備sql語句

sql = '...'

(5) 執行sql語句

cursor.execute(sql)

(6) 獲取所有結果集

cuesor.fetchall()

(7) 獲取一條結果集

cursor.fetchone()

(8) 獲取受影響的行數

cursor.rowcount

(9) 關閉資料庫連結

db.close()

pymysql的事務處理

預設開啟事物處理

需要提交或者回滾

import pymysql
db = pymysql.connect('127.0.0.1','root','123456','hz03')
db.set_charset('utf8')
cursor = db.cursor()
try:
    sql = 'insert into goods values(null,1,"商品名稱",12.1)'
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
print(cursor.rowcount)
db.close()
複製程式碼

示例: import pymysql

1.連線mysql資料庫

db = pymysql.connect('localhost','root','abcdef','test')

2.設定字元編碼

db.set_charset('utf8')

3. 建立遊標物件

cursor = db.cursor()

4. 準備sql語句

sql = 'select * from hz03'

5. 執行sql語句

cursor.execute(sql)

6. 獲取所有結果集

date = cursor.fetchall() print('結果集為',date)

7. 獲取受影響的行數

print('受影響的行數為',cursor.rowcount)

8. 關閉資料庫連線

db.close()