關於Mysql使用的一些總結

Simon_Zhou發表於2019-05-10
MySql

1.資料庫的備份與恢復

-- 資料庫備份 在庫外執行，將資料庫備份，在當前目錄生成一個xxx.sql檔案
mysqldump -uroot -p 資料庫名 > xxx.sql;
  -- 資料庫恢復 在庫外執行，將資料庫恢復到其他資料庫中去
mysql -uroot -p 新資料庫名 < xxx.sql; 複製程式碼

2.事務的四大特性

原子性:

一個事務必須被視為一個不可分割的最小工作單元，整個事務中的所有操作要麼全部提交成功，要麼全部失敗回滾，對於一個事務來說，不可能只執行其中的一部分操作，這就是事務的原子性

一致性:

資料庫總是從一個一致性的狀態轉換到另一個一致性的狀態。（例如在轉賬過程中系統崩潰，支票賬戶中也不會損失，因為事務最終沒有提交，所以事務中所做的修改也不會儲存到資料庫中。）

隔離性:

通常來說，一個事務所做的修改操作在提交事務之前，對於其他事務來說是不可見的。

永續性:

一旦事務提交，則其所做的修改會永久儲存到資料庫。

事務的使用

在使用事務之前，先要確保表的儲存引擎是 InnoDB 型別, 只有這個型別才可以使用事務，MySQL資料庫中表的儲存引擎預設是 InnoDB 型別。

表的儲存引擎說明:

表的儲存引擎就是提供儲存資料一種機制，不同表的儲存引擎提供不同的儲存機制。

檢視MySQL資料庫支援的表的儲存引擎:

-- 檢視MySQL資料庫支援的表的儲存引擎show engines;複製程式碼

說明:

  • 常用的表的儲存引擎是 InnoDB 和 MyISAM

  • InnoDB 是支援事務的

  • MyISAM 不支援事務，優勢是訪問速度快，對事務沒有要求或者以select、insert為主的都可以使用該儲存引擎來建立表

開啟事務:

begin;--或者start transaction;複製程式碼

  • 開啟事務後執行修改命令，變更資料會儲存到MySQL服務端的快取檔案中，而不維護到物理表中

  • MySQL資料庫預設採用自動提交(autocommit)模式，如果沒有顯示的開啟一個事務,那麼每條sql語句都會被當作一個事務執行提交的操作

set autocommit = 0; 
 --設定autocommit = 0 變為了手動提交事務，預設為1為自動提交
insert into 表名(name) values('張無忌');
-- 需要執行手動提交，資料才會真正新增到表中, 驗證的話需要重新開啟一個連線視窗檢視錶的資料資訊，因為是臨時關閉自動提交模式
commit  --提交事務​
-- 另外重新開啟一個終端視窗，連線MySQL資料庫服務端
mysql -uroot -p​
-- 然後查詢資料,如果上個視窗執行了commit，這個視窗才能看到資料select * from 表名;複製程式碼

回滾事務:

放棄本地快取檔案中的快取資料, 表示回到開始事務前的狀態

rollback; --回滾事務複製程式碼

3.索引的使用

索引在MySQL中也叫做“鍵”，它是一個特殊的檔案，它儲存著資料表裡所有記錄的位置資訊，更通俗的來說，資料庫索引好比是一本書前面的目錄，能加快資料庫的查詢速度。

應用場景:

當資料庫中資料量很大時，查詢資料會變得很慢，我們就可以通過索引來提高資料庫的查詢效率。

檢視錶中已有索引:

show index from 表名;複製程式碼

說明:

  • 主鍵列會自動建立索引

索引的建立:

-- 建立索引的語法格式
-- alter table 表名 add index 索引名[可選](列名, ..)
-- 給name欄位新增索引
alter table 表名 add index my_name (name);複製程式碼

說明:

  • 索引名不指定，預設使用欄位名

索引的刪除:

-- 刪除索引的語法格式
-- alter table 表名 drop index 索引名
-- 如果不知道索引名，可以檢視創表sql語句
show create table 表名;
alter table 表名 drop index my_name;複製程式碼

4.SQL語句

聚合函式的介紹

聚合函式又叫組函式，通常是對錶中的資料進行統計和計算，一般結合分組(group by)來使用，用於統計和計算分組資料。下面是一些常見的聚合函式：

  1. count(col): 表示求指定列的總行數

  2. max(col): 表示求指定列的最大值

  3. min(col): 表示求指定列的最小值

  4. sum(col): 表示求指定列的和

  5. avg(col): 表示求指定列的平均值

分組查詢

分組查詢就是將查詢結果按照指定欄位進行分組，欄位中資料相等的分為一組。

語法格式：

GROUP BY 列名 [HAVING 條件表示式] [WITH ROLLUP]複製程式碼

HAVING對分組後的資料進行條件過濾，WITH ROLLUP在所有記錄的最後加上一條記錄，顯示select查詢時聚合函式的統計和計算結果。

-- 根據gender欄位進行分組，彙總所有人的年齡
select gender,group_concat(age) from 表名 group by gender with rollup;複製程式碼

下面對資料庫比較常見的SQL語句做一些簡單總結：

insert into 表名(name) values ('黃蓉'),('郭靖'),('黃藥師'); --一次插入多列;
​select * from 表名 where not age between 10 and 25; --年齡不在10到25之間；
​select * from 表名 where  age in(20,23); --年齡是20，23的；
​select * from 表名 where height is null; --身高不為空；
​select * from 表名 order by age desc,height desc; --年齡按降序，年齡相同身高按降序排序；
--求平均價格,並且保留兩位小數
select round(avg(price),2) from 表名;
--查詢所有價格大於平均價格的商品，並且按價格降序排序
select id,name,price from 表名 where price > (select round(avg(price),2) from 表名) order by price desc;
 --分頁查詢
select * from 表名 limit start,count 
 -- limit是分頁查詢關鍵字
-- start表示開始行索引，預設是0-
- count表示查詢條數複製程式碼

5.連線查詢

內連線查詢

查詢兩個表中符合條件的共有記錄。

select 欄位 from 表1 inner join 表2 on 表1.欄位1 = 表2.欄位2複製程式碼

  • inner join 就是內連線查詢關鍵字

  • on 就是連線查詢條件

左連線查詢

以左表為主根據條件查詢右表資料，如果根據條件查詢右表資料不存在使用null值填充。

select 欄位 from 左表 left join 右表 on 左表.欄位1 = 右表.欄位2複製程式碼

  • left join 就是左連線查詢關鍵字

  • on 就是連線查詢條件

右連線查詢

以右表為主根據條件查詢左表資料，如果根據條件查詢左表資料不存在使用null值填充。

select 欄位 from 左表 right join 右表 on 左表.欄位1 = 右表.欄位2複製程式碼

  • right join 就是右連線查詢關鍵字

  • on 就是連線查詢條件

6.外來鍵約束

外來鍵約束:對外來鍵欄位的值進行更新和插入時會和引用表中欄位的資料進行驗證，資料如果不合法則更新和插入會失敗，保證資料的有效性。

對已存在的欄位新增外來鍵約束

-- 為cls_id欄位新增外來鍵約束
alter table students add foreign key(cls_id) references classes(id);複製程式碼

在建立資料表時設定外來鍵約束

-- 建立學校表
create table school( id int not null primary key auto_increment,  
                        name varchar(10));
​-- 建立老師表
create table teacher( id int not null primary key auto_increment, 
                      name varchar(10),
                     s_id int not null,  
                     foreign key(s_id) references school(id));複製程式碼

刪除外來鍵約束

-- 需要先獲取外來鍵約束名稱,該名稱系統會自動生成,可以通過檢視錶建立語句來獲取名稱
show create table teacher;​
-- 獲取名稱之後就可以根據名稱來刪除外來鍵約束
alter table teacher drop foreign key 外來鍵名;複製程式碼

7.pymysql的使用

pymysql是python連線mysql的一個比較常用的庫。可通過pip安裝。

pip show pymysql 
 --檢視是否裝pymysql及檢視裝後的資訊；
pip install pymysql --安裝複製程式碼
import pymysql
'''
host=None,  # Mysql所在的主機IP地址，如果在本地使用localhost
user=root,  # 使用者名稱
password="mysql",# 密碼
database='py', # 使用哪個資料庫
port=3306,    # mysql的埠，預設3306
charset='utf8' # 資料庫編碼格式
'''
conn = pymysql.connect(host = 'None',user = 'root',                       password='mysql',database='py',
                       port = 3306,charset = 'utf8') 

sql = 'select * from students' #待執行的sql語句
cursor = conn.cursor() # 獲取遊標
raw_count = cursor.execute(sql) #cursor執行sql語句，返回影響的行數
print('raw_count:',raw_count)
# one = cursor.fetchone() # 返回一個資料，格式是元祖
# print('one:',one)
res = cursor.fetchall() # 返回所有查詢資料，是一個複合元祖
for one in res:
    print(one)
cursor.close() # 關遊標
conn.close()   # 關閉資料庫連線複製程式碼

上面的程式碼演示了查詢語句的基本使用。

try:
    sql = "insert into students(name,age)values(%s,%s),(%s,%s)" # %s引數佔位,插入兩行
    cursor = conn.cursor() # 獲取遊標
    raw_count = cursor.execute(sql,['西門吹雪',25,'獨孤求敗',30]) #返回影響行數
    print('raw_count:',raw_count) 
    conn.commit() #提交事務
except Exception as e:
    print(e)
    conn.rollback() # 出現錯誤，對資料進行回滾
finally:    
    cursor.close()
    conn.close()複製程式碼

防止sql注入，用%s佔位，可傳入任意型別資料, %s佔位不需要帶引號。上面的程式碼演示了執行sql插入的操作。下面演示刪除與更新sql的程式碼。注意一下cursor的execute()方法，如果傳入第二個引數，可以是列表或元祖。

conn = pymysql.connect(host = 'None',user = 'root',
                       password='mysql',database='py',
                       port = 3306,charset = 'utf8')

try:
    # sql = "delete from students where id = %s" # 刪除
    sql = "update students set age = %s where id = %s" # 修改
    cursor = conn.cursor() # 獲取遊標
    # raw_count = cursor.execute(sql,(9,))  #刪除id=9的資料
    raw_count = cursor.execute(sql,(18,19)) # 修改
    print('raw_count:',raw_count)
    conn.commit() #提交事務
    res = cursor.fetchall() #遍歷所有記錄
    for one in res:
        print(one)
except Exception as e:
    print(e)
    conn.rollback() # 出現錯誤，對資料進行回滾
finally:    
    cursor.close()
    conn.close()複製程式碼


相關文章