一、將查詢結(jié)果插入到其它表中

1. 思考

目前只有一個goods表，我們想要增加一個商品分類信息，比如：移動設(shè)備這個分類信息，只通過goods表無法完成商品分類的添加，那么如何實現(xiàn)添加商品分類信息的操作?

答案:

創(chuàng)建一個商品分類表，把goods表中的商品分類信息添加到該表中。
將goods表中的分類名稱更改成商品分類表中對應(yīng)的分類id

2. 創(chuàng)建商品分類表

-- 創(chuàng)建商品分類表
create table good_cates(
    id int not null primary key auto_increment, 
    name varchar(50) not null
);

3. 把goods表中的商品分類添加到商品分類表

-- 查詢goods表中商品的分類信息
select cate_name from goods group by cate_name;

-- 將查詢結(jié)果插入到good_cates表中
insert into good_cates(name) select cate_name from goods group by cate_name;

-- 添加移動設(shè)備分類信息
insert into good_cates(name) values('移動設(shè)備');

說明:

insert into .. select .. 表示: 把查詢結(jié)果插入到指定表中，也就是表復(fù)制。

二、使用連接更新表中某個字段數(shù)據(jù)

1. 更新goods表中的商品分類信息

上一節(jié)課我們已經(jīng)創(chuàng)建了一個商品分類表(good_cates)，并完成了商品分類信息的插入，現(xiàn)在需要更新goods表中的商品分類信息，把商品分類名稱改成商量分類id。

接下來我們實現(xiàn)第二步操作:

將goods表中的分類名稱更改成商品分類表中對應(yīng)的分類id

-- 查看goods表中的商品分類名稱對應(yīng)的商品分類id
select * from goods inner join good_cates on goods.cate_name = good_cates.name;

-- 把該語句中from 后的語句理解為一張?zhí)摫? 
update goods g inner join good_cates gc on g.cate_name=gc.name set g.cate_name=gc.id;

三、創(chuàng)建表并給某個字段添加數(shù)據(jù)

1. 思考

上一節(jié)課我們完成了商品分類表(good_cates)的創(chuàng)建和商品分類信息的添加以及把商品表(goods)中的商品分類名稱改成了對應(yīng)的商品分類id，假如我們想要添加一個品牌，比如：雙飛燕這個品牌信息，只通過goods表無法完成品牌信息的添加，那么如何實現(xiàn)添加品牌信息的操作?

答案:

創(chuàng)建一個品牌表，把goods表中的品牌信息添加到該表中。
將goods表中的品牌名稱更改成品牌表中對應(yīng)的品牌id

2. 創(chuàng)建品牌表

-- 查詢品牌信息 
select brand_name from goods group by brand_name;

-- 通過create table ...select來創(chuàng)建數(shù)據(jù)表并且同時插入數(shù)據(jù)
-- 創(chuàng)建商品分類表，注意: 需要對brand_name 用as起別名，否則name字段就沒有值
create table good_brands (     
id int unsigned primary key auto_increment,     
name varchar(40) not null) select brand_name as name from goods group by brand_name;

說明:

create table .. select 列名 .. 表示創(chuàng)建表并插入數(shù)據(jù)

3. 更新goods表中的品牌信息

-- 將goods表中的品牌名稱更改成品牌表中對應(yīng)的品牌id
update goods as g inner join good_brands gb on g.brand_name = gb.name set g.brand_name = gb.id;

四、修改goods表結(jié)構(gòu)

目前我們已經(jīng)把good表中的商品分類和品牌信息已經(jīng)更改成了商品分類id和品牌id，接下來需要把 cate_name 和 brand_name 字段分別改成 cate_id和 brand_id 字段，類型都改成int類型

-- 查看表結(jié)構(gòu)
desc goods;
-- 通過alter table語句修改表結(jié)構(gòu)
alter table goods change cate_name cate_id int not null, change brand_name brand_id int not null;

說明:

alert table 可以同時修改多個字段信息

五、PyMySQL的使用

1. 思考

如何實現(xiàn)將100000條數(shù)據(jù)插入到MySQL數(shù)據(jù)庫?

答案:

如果使用之前學(xué)習(xí)的MySQL客戶端來完成這個操作，那么這個工作量無疑是巨大的，我們可以通過使用程序代碼的方式去連接MySQL數(shù)據(jù)庫，然后對MySQL數(shù)據(jù)庫進行增刪改查的方式，實現(xiàn)10000條數(shù)據(jù)的插入，像這樣使用代碼的方式操作數(shù)據(jù)庫就稱為數(shù)據(jù)庫編程。

2. Python程序操作MySQL數(shù)據(jù)庫

安裝pymysql第三方包:

sudo pip3 install pymysql

說明:

安裝命令使用 sudo pip3 install 第三方包名
卸載命令使用 sudo pip3 uninstall 第三方包
大家現(xiàn)在使用的虛擬機已經(jīng)安裝了這個第三方包，可以使用：?pip3 show pymysql?命令查看第三方包的信息
pip3 list?查看使用pip命令安裝的第三方包列表

pymysql的使用:

1、導(dǎo)入 pymysql 包

 import pymysql

2、創(chuàng)建連接對象

調(diào)用pymysql模塊中的connect()函數(shù)來創(chuàng)建連接對象,代碼如下:

 conn=connect(參數(shù)列表)

 * 參數(shù)host：連接的mysql主機，如果本機是'localhost'
 * 參數(shù)port：連接的mysql主機的端口，默認是3306
 * 參數(shù)user：連接的用戶名
 * 參數(shù)password：連接的密碼
 * 參數(shù)database：數(shù)據(jù)庫的名稱
 * 參數(shù)charset：通信采用的編碼方式，推薦使用utf8

連接對象操作說明:

關(guān)閉連接 conn.close()
提交數(shù)據(jù) conn.commit()
撤銷數(shù)據(jù) conn.rollback()

3、獲取游標對象

獲取游標對象的目標就是要執(zhí)行sql語句，完成對數(shù)據(jù)庫的增、刪、改、查操作。代碼如下:

# 調(diào)用連接對象的cursor()方法獲取游標對象   
 cur =conn.cursor()

游標操作說明:

使用游標執(zhí)行SQL語句: execute(operation [parameters ]) 執(zhí)行SQL語句，返回受影響的行數(shù)，主要用于執(zhí)行insert、update、delete、select等語句
獲取查詢結(jié)果集中的一條數(shù)據(jù):cur.fetchone()返回一個元組, 如 (1,’張三’)
獲取查詢結(jié)果集中的所有數(shù)據(jù): cur.fetchall()返回一個元組,如((1,’張三’),(2,’李四’))
關(guān)閉游標: cur.close(),表示和數(shù)據(jù)庫操作完成

4、pymysql完成數(shù)據(jù)的查詢操作

import pymysql

# 創(chuàng)建連接對象
conn = pymysql.connect(host='localhost', port=3306, user='root', password='mysql',database='python', charset='utf8')

# 獲取游標對象
cursor = conn.cursor()

# 查詢 SQL 語句
sql = "select * from students;"
# 執(zhí)行 SQL 語句 返回值就是 SQL 語句在執(zhí)行過程中影響的行數(shù)
row_count = cursor.execute(sql)
print("SQL 語句執(zhí)行影響的行數(shù)%d" % row_count)

# 取出結(jié)果集中一行數(shù)據(jù),　例如:(1, '張三')
# print(cursor.fetchone())

# 取出結(jié)果集中的所有數(shù)據(jù), 例如:((1, '張三'), (2, '李四'), (3, '王五'))
for line in cursor.fetchall():
    print(line)

# 關(guān)閉游標
cursor.close()

# 關(guān)閉連接
conn.close()

5、pymysql完成對數(shù)據(jù)的增刪改

import pymysql

# 創(chuàng)建連接對象
conn = pymysql.connect(host='localhost', port=3306, user='root', password='mysql',database='python', charset='utf8')

# 獲取游標對象
cursor = conn.cursor()

try:
    # 添加 SQL 語句
    # sql = "insert into students(name) values('劉璐'), ('王美麗');"
    # 刪除 SQ L語句
    # sql = "delete from students where id = 5;"
    # 修改 SQL 語句
    sql = "update students set name = '王鐵蛋' where id = 6;"
    # 執(zhí)行 SQL 語句
    row_count = cursor.execute(sql)
    print("SQL 語句執(zhí)行影響的行數(shù)%d" % row_count)
    # 提交數(shù)據(jù)到數(shù)據(jù)庫
    conn.commit()
except Exception as e:
    # 回滾數(shù)據(jù)， 即撤銷剛剛的SQL語句操作
    conn.rollback()

# 關(guān)閉游標
cursor.close()

# 關(guān)閉連接
conn.close()

說明:

conn.commit() 表示將修改操作提交到數(shù)據(jù)庫
conn.rollback() 表示回滾數(shù)據(jù)

6、防止SQL注入

什么是SQL注入?

用戶提交帶有惡意的數(shù)據(jù)與SQL語句進行字符串方式的拼接，從而影響了SQL語句的語義，最終產(chǎn)生數(shù)據(jù)泄露的現(xiàn)象。

如何防止SQL注入?

SQL語句參數(shù)化

SQL語言中的參數(shù)使用%s來占位，此處不是python中的字符串格式化操作
將SQL語句中%s占位所需要的參數(shù)存在一個列表中，把參數(shù)列表傳遞給execute方法中第二個參數(shù)

防止SQL注入的示例代碼:

from pymysql import connect

def main():

    find_name = input("請輸入物品名稱：")

    # 創(chuàng)建Connection連接
    conn = connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')
    # 獲得Cursor對象
    cs1 = conn.cursor()

    # 非安全的方式
    # 輸入 ' or 1 = 1 or '   (單引號也要輸入)
    # sql = "select * from goods where name='%s'" % find_name
    # print("""sql===>%s<====""" % sql)
    # # 執(zhí)行select語句，并返回受影響的行數(shù)：查詢所有數(shù)據(jù)
    # count = cs1.execute(sql)

    # 安全的方式
    # 構(gòu)造參數(shù)列表
    params = [find_name]
    # 執(zhí)行select語句，并返回受影響的行數(shù)：查詢所有數(shù)據(jù)
    count = cs1.execute("select * from goods where name=%s", params)
    # 注意：
    # 如果要是有多個參數(shù)，需要進行參數(shù)化
    # 那么params = [數(shù)值1, 數(shù)值2....]，此時sql語句中有多個%s即可
    # %s 不需要帶引號

    # 打印受影響的行數(shù)
    print(count)
    # 獲取查詢的結(jié)果
    # result = cs1.fetchone()
    result = cs1.fetchall()
    # 打印查詢的結(jié)果
    print(result)
    # 關(guān)閉Cursor對象
    cs1.close()
    # 關(guān)閉Connection對象
    conn.close()

if __name__ == '__main__':
    main()

說明:

execute方法中的 %s 占位不需要帶引號

六、事務(wù)

1. 事務(wù)的介紹

事務(wù)就是用戶定義的一系列執(zhí)行SQL語句的操作, 這些操作要么完全地執(zhí)行，要么完全地都不執(zhí)行，它是一個不可分割的工作執(zhí)行單元。

事務(wù)的使用場景:

在日常生活中，有時我們需要進行銀行轉(zhuǎn)賬，這個銀行轉(zhuǎn)賬操作背后就是需要執(zhí)行多個SQL語句，假如這些SQL執(zhí)行到一半突然停電了，那么就會導(dǎo)致這個功能只完成了一半，這種情況是不允許出現(xiàn)，要想解決這個問題就需要通過事務(wù)來完成。

2. 事務(wù)的四大特性

原子性(Atomicity)
一致性(Consistency)
隔離性(Isolation)
持久性(Durability)

原子性:

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

一致性:

數(shù)據(jù)庫總是從一個一致性的狀態(tài)轉(zhuǎn)換到另一個一致性的狀態(tài)。（在前面的例子中，一致性確保了，即使在轉(zhuǎn)賬過程中系統(tǒng)崩潰，支票賬戶中也不會損失200美元，因為事務(wù)最終沒有提交，所以事務(wù)中所做的修改也不會保存到數(shù)據(jù)庫中。）

隔離性:

通常來說，一個事務(wù)所做的修改操作在提交事務(wù)之前，對于其他事務(wù)來說是不可見的。（在前面的例子中，當執(zhí)行完第三條語句、第四條語句還未開始時，此時有另外的一個賬戶匯總程序開始運行，則其看到支票帳戶的余額并沒有被減去200美元。）

持久性:

一旦事務(wù)提交，則其所做的修改會永久保存到數(shù)據(jù)庫。

說明:

事務(wù)能夠保證數(shù)據(jù)的完整性和一致性，讓用戶的操作更加安全。

3. 事務(wù)的使用

在使用事務(wù)之前，先要確保表的存儲引擎是 InnoDB 類型, 只有這個類型才可以使用事務(wù)，MySQL數(shù)據(jù)庫中表的存儲引擎默認是 InnoDB 類型。

表的存儲引擎說明:

表的存儲引擎就是提供存儲數(shù)據(jù)一種機制，不同表的存儲引擎提供不同的存儲機制。

汽車引擎效果圖:

說明:

不同的汽車引擎，提供的汽車動力也是不同的。

查看MySQL數(shù)據(jù)庫支持的表的存儲引擎:

-- 查看MySQL數(shù)據(jù)庫支持的表的存儲引擎
show engines;

說明:

常用的表的存儲引擎是 InnoDB 和 MyISAM
InnoDB 是支持事務(wù)的
MyISAM 不支持事務(wù)，優(yōu)勢是訪問速度快，對事務(wù)沒有要求或者以select、insert為主的都可以使用該存儲引擎來創(chuàng)建表

查看goods表的創(chuàng)表語句:

-- 選擇數(shù)據(jù)庫
use jing_dong;
-- 查看goods表
show create table goods;

mysql root@(none):jing_dong> show create table goods;
+-------+--------------------------------------------------------+
| Table | Create Table                                           |
+-------+--------------------------------------------------------+
| goods | CREATE TABLE `goods` (                                 |
|       |   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,       |
|       |   `name` varchar(150) NOT NULL,                        |
|       |   `cate_id` int(10) unsigned NOT NULL,                 |
|       |   `brand_id` int(10) unsigned NOT NULL,                |
|       |   `price` decimal(10,3) NOT NULL DEFAULT '0.000',      |
|       |   `is_show` bit(1) NOT NULL DEFAULT b'1',              |
|       |   `is_saleoff` bit(1) NOT NULL DEFAULT b'0',           |
|       |   PRIMARY KEY (`id`)                                   |
|       | ) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------+

說明:

通過創(chuàng)表語句可以得知，goods表的存儲引擎是InnoDB。
修改表的存儲引擎使用: alter table 表名 engine = 引擎類型;比如: alter table students engine = ‘MyISAM’;

開啟事務(wù):

begin;
或者
start transaction;

說明:

開啟事務(wù)后執(zhí)行修改命令，變更數(shù)據(jù)會保存到MySQL服務(wù)端的緩存文件中，而不維護到物理表中
MySQL數(shù)據(jù)庫默認采用自動提交(autocommit)模式，如果沒有顯示的開啟一個事務(wù),那么每條sql語句都會被當作一個事務(wù)執(zhí)行提交的操作
當設(shè)置autocommit=0就是取消了自動提交事務(wù)模式，直到顯示的執(zhí)行commit和rollback表示該事務(wù)結(jié)束。
- set autocommit = 0 表示取消自動提交事務(wù)模式，需要手動執(zhí)行commit完成事務(wù)的提交
set?autocommit =?0;?insert?into?students(name)?values(‘劉三峰’);?— 需要執(zhí)行手動提交，數(shù)據(jù)才會真正添加到表中, 驗證的話需要重新打開一個連接窗口查看表的數(shù)據(jù)信息，因為是臨時關(guān)閉自動提交模式?commit?— 重新打開一個終端窗口，連接MySQL數(shù)據(jù)庫服務(wù)端?mysql -uroot -p?— 然后查詢數(shù)據(jù),如果上個窗口執(zhí)行了commit，這個窗口才能看到數(shù)據(jù)?select?*?from?students;
提交事務(wù):
將本地緩存文件中的數(shù)據(jù)提交到物理表中，完成數(shù)據(jù)的更新。
commit;
回滾事務(wù):
放棄本地緩存文件中的緩存數(shù)據(jù), 表示回到開始事務(wù)前的狀態(tài)
rollback;
事務(wù)演練的SQL語句:
begin;?insert?into?students(name)?values(‘李白’);?— 查詢數(shù)據(jù)，此時有新增的數(shù)據(jù), 注意: 如果這里后續(xù)沒有執(zhí)行提交事務(wù)操作，那么數(shù)據(jù)是沒有真正的更新到物理表中?select?*?from?students;?— 只有這里提交事務(wù)，才把數(shù)據(jù)真正插入到物理表中?commit;?— 新打開一個終端，重新連接MySQL數(shù)據(jù)庫，查詢students表,這時沒有顯示新增的數(shù)據(jù)，說明之前的事務(wù)沒有提交，這就是事務(wù)的隔離性?— 一個事務(wù)所做的修改操作在提交事務(wù)之前，對于其他事務(wù)來說是不可見的?select?*?from?students;

七、索引

1. 索引的介紹

索引在MySQL中也叫做“鍵”，它是一個特殊的文件，它保存著數(shù)據(jù)表里所有記錄的位置信息，更通俗的來說，數(shù)據(jù)庫索引好比是一本書前面的目錄，能加快數(shù)據(jù)庫的查詢速度。

應(yīng)用場景:

當數(shù)據(jù)庫中數(shù)據(jù)量很大時，查找數(shù)據(jù)會變得很慢，我們就可以通過索引來提高數(shù)據(jù)庫的查詢效率。

2. 索引的使用

查看表中已有索引:

show index from 表名;

說明:

主鍵列會自動創(chuàng)建索引

索引的創(chuàng)建:

-- 創(chuàng)建索引的語法格式
-- alter table 表名 add index 索引名[可選](列名, ..)
-- 給name字段添加索引
alter table classes add index my_name (name);

說明:

索引名不指定，默認使用字段名

索引的刪除:

-- 刪除索引的語法格式
-- alter table 表名 drop index 索引名
-- 如果不知道索引名，可以查看創(chuàng)表sql語句
show create table classes;
alter table classes drop index my_name;

3. 案例-驗證索引查詢性能

創(chuàng)建測試表testindex:

create table test_index(title varchar(10));

向表中插入十萬條數(shù)據(jù):

from pymysql import connect

def main():
    # 創(chuàng)建Connection連接
    conn = connect(host='localhost',port=3306,database='python',user='root',password='mysql',charset='utf8')
    # 獲得Cursor對象
    cursor = conn.cursor()
    # 插入10萬次數(shù)據(jù)
    for i in range(100000):
        cursor.execute("insert into test_index values('ha-%d')" % i)
    # 提交數(shù)據(jù)
    conn.commit()

if __name__ == "__main__":
    main()

驗證索引性能操作：

-- 開啟運行時間監(jiān)測：
set profiling=1;
-- 查找第1萬條數(shù)據(jù)ha-99999
select * from test_index where title='ha-99999';
-- 查看執(zhí)行的時間：
show profiles;
-- 給title字段創(chuàng)建索引：
alter table test_index add index (title);
-- 再次執(zhí)行查詢語句
select * from test_index where title='ha-99999';
-- 再次查看執(zhí)行的時間
show profiles;

4. 聯(lián)合索引

聯(lián)合索引又叫復(fù)合索引，即一個索引覆蓋表中兩個或者多個字段，一般用在多個字段一起查詢的時候。

-- 創(chuàng)建teacher表
create table teacher
(
    id int not null primary key auto_increment,
    name varchar(10),
    age int
);

-- 創(chuàng)建聯(lián)合索引
alter table teacher add index (name,age);

聯(lián)合索引的好處:

減少磁盤空間開銷，因為每創(chuàng)建一個索引，其實就是創(chuàng)建了一個索引文件，那么會增加磁盤空間的開銷。

5. 聯(lián)合索引的最左原則

在使用聯(lián)合索引的時候，我們要遵守一個最左原則,即index(name,age)支持 name 、name 和 age 組合查詢,而不支持單獨 age 查詢，因為沒有用到創(chuàng)建的聯(lián)合索引。

最左原則示例:

-- 下面的查詢使用到了聯(lián)合索引
select * from stu where name='張三' -- 這里使用了聯(lián)合索引的name部分
select * from stu where name='李四' and age=10 -- 這里完整的使用聯(lián)合索引，包括 name 和 age 部分 
-- 下面的查詢沒有使用到聯(lián)合索引
select * from stu where age=10 -- 因為聯(lián)合索引里面沒有這個組合，只有 name | name age 這兩種組合

說明:

在使用聯(lián)合索引的查詢數(shù)據(jù)時候一定要保證聯(lián)合索引的最左側(cè)字段出現(xiàn)在查詢條件里面，否則聯(lián)合索引失效

6. MySQL中索引的優(yōu)點和缺點和使用原則

優(yōu)點：
加快數(shù)據(jù)的查詢速度
缺點：
創(chuàng)建索引會耗費時間和占用磁盤空間，并且隨著數(shù)據(jù)量的增加所耗費的時間也會增加
使用原則：
通過優(yōu)缺點對比，不是索引越多越好，而是需要自己合理的使用。
對經(jīng)常更新的表就避免對其進行過多索引的創(chuàng)建，對經(jīng)常用于查詢的字段應(yīng)該創(chuàng)建索引，
數(shù)據(jù)量小的表最好不要使用索引，因為由于數(shù)據(jù)較少，可能查詢?nèi)繑?shù)據(jù)花費的時間比遍歷索引的時間還要短，索引就可能不會產(chǎn)生優(yōu)化效果。
在一字段上相同值比較多不要建立索引，比如在學(xué)生表的”性別”字段上只有男，女兩個不同值。相反的，在一個字段上不同值較多可是建立索引

版權(quán)聲明：本文內(nèi)容由互聯(lián)網(wǎng)用戶自發(fā)貢獻，該文觀點僅代表作者本人。本站僅提供信息存儲空間服務(wù)，不擁有所有權(quán)，不承擔相關(guān)法律責(zé)任。如發(fā)現(xiàn)本站有涉嫌抄襲侵權(quán)/違法違規(guī)的內(nèi)容，請發(fā)送郵件至2705686032@qq.com 舉報，一經(jīng)查實，本站將立刻刪除。原文轉(zhuǎn)載：原文出處：