【連載電子書十】Pymysql 數(shù)據(jù)庫詳解

0. 前言

1. MySQL安裝

2. 建庫, 建表

3. 基礎SQL語句

1. 增加

2. 修改

3. 刪除

4. 查詢

4. 分組查詢

5. 排序

6. 分頁

7. 多表查詢

1. 子查詢

2. 關聯(lián)查詢

8. 其他雜七雜八(對于新手小白了解即可)

9. pymysql

作業(yè):

1. 基本SQL語句(慢慢做,  做一個月)

2. 使用pymysql完成業(yè)務線系統(tǒng)開發(fā).

0. 前言

數(shù)據(jù)庫其實并不高深, 它就是一個能存儲數(shù)據(jù)到硬盤上的一個軟件. 本質就是一個軟件.  數(shù)據(jù)庫承載了我們目前市面上能見到的絕大多數(shù)的軟件最底層的數(shù)據(jù)支持. 可以這么說. 如果沒有數(shù)據(jù),  就沒有目前互聯(lián)網(wǎng)的高速發(fā)展. 而數(shù)據(jù)庫作為承載數(shù)據(jù)的載體. 它的地位也是非常高的.

數(shù)據(jù)庫的分類:

早期, 數(shù)據(jù)庫分為層次型數(shù)據(jù)庫, 網(wǎng)絡型數(shù)據(jù)庫和關系型數(shù)據(jù)庫,  在今天的互聯(lián)網(wǎng)高速發(fā)展下, 數(shù)據(jù)庫被分為關系型數(shù)據(jù)庫和非關系型數(shù)據(jù)庫.

關系型數(shù)據(jù)庫

關系型數(shù)據(jù)庫把復雜的數(shù)據(jù)邏輯歸納為二元結構(二維表格結構), 并且, 表格與表格之間可以互相的關聯(lián). 讓數(shù)據(jù)的存儲和查詢都變得更加合理簡單.

常見的關系型數(shù)據(jù)庫:

1. MySql. 小巧, 免費. 功能強大. 目前市場上的大多數(shù)互聯(lián)網(wǎng)公司都在使用.

2. Microsoft SQL Server,  微軟提供的數(shù)據(jù)庫系統(tǒng). 功能強大. 但只能在windows平臺使用.

3. Oracle, Oracle公司出品, 功能強大. 數(shù)據(jù)處理速度快. 穩(wěn)定. 缺點: 貴. Oracle數(shù)據(jù)庫下載和使用都免費, 但是, 如果商用就很貴了.  而且Oracle的維護成本也要比其他數(shù)據(jù)庫要高很多.

4. DB2, IMB提供的數(shù)據(jù)庫系統(tǒng). 主要運行在unix上. 一般在我們生活中是很少見到的. 只有在一些對硬件穩(wěn)定性要求非常高的情況下會選擇使用. 當然. 你想用DB2必須先來一臺IMB的服務器. 那個價格爽死你.

非關系型數(shù)據(jù)庫

非關系型數(shù)據(jù)庫也被稱為NoSql.  首先, SQL是我們操作數(shù)據(jù)庫的編程語言. 專門為數(shù)據(jù)庫而設計的. NoSql表示Not Only SQL,   NoSQL的產(chǎn)生不是說徹底否定關系型數(shù)據(jù)庫的存在, 而是對常見的關系型數(shù)據(jù)庫進行補充. 目的是更好的為應用程序提供數(shù)據(jù)支撐. 傳統(tǒng)的關系型數(shù)據(jù)庫在遇到高并發(fā), 大量數(shù)據(jù)IO請求的時候會顯得力不從心. 此時NoSQL就派上用場了. 主旨是更快的為應用提供數(shù)據(jù). 承載更多的并發(fā).

常見的非關系型數(shù)據(jù):

1. Redis 可以對數(shù)據(jù)進行緩存(內存), 也可以持久化保存數(shù)據(jù)(硬盤). 非常容易搭建集群以及主從復制.

2. Memcache     主要是對數(shù)據(jù)進行緩存(內存)

3. MongoDB      是一個介于nosql數(shù)據(jù)庫和mysql數(shù)據(jù)庫之間的一個數(shù)據(jù)存儲系統(tǒng)

4. Elasticsearch  一個全文檢索的搜索引擎.

我們前期的重點在mysql上. 原因有

1. Mysql免費, 而且小巧, 無論是安裝還是使用都非常的簡單容易

2. 功能強大. 麻雀雖小五臟俱全, 各個大型數(shù)據(jù)庫擁有的功能它幾乎都支持

3. 市場認可度高.

1. MySQL安裝

mysql是一個小型的DBMS, 但是麻雀雖小, 五臟俱全.  各種大型數(shù)據(jù)庫的操作MySQL都有,  首先, 我們來安裝一下MySQL.  

首先, 去官網(wǎng)下載MySQL

不同平臺有不同的安裝方案:

windows:  https://dev./downloads/installer/

下面是安裝windows需要的插件

裝完了. 配置一下下環(huán)境變量

把上面那個路徑扔到path里面

確定, 一路保存確認就可以了.  很easy好吧.

測試: 打開cmd. 輸入命令:

回車之后, 輸入密碼.  最終見到以下內容, 視為沒毛病

navicat安裝:

激活過程自己想辦法吧....

2. 建庫, 建表

數(shù)據(jù)庫系統(tǒng)包括:

數(shù)據(jù)庫(DB): 存放數(shù)據(jù)的地方, 主要是存儲數(shù)據(jù)的硬件以及存儲的數(shù)據(jù)結構算法.

數(shù)據(jù)庫管理系統(tǒng)(DBMS): 用來操縱數(shù)據(jù)庫的地方. DBMS是數(shù)據(jù)庫系統(tǒng)的基礎與核心. 我們程序員最關心的就是這里. DBMS也是我們操縱數(shù)據(jù)庫唯一的途徑和手段.

大部分的DBMS提供了

DCL(Data Control Language), 數(shù)據(jù)庫控制語言, 主要是對用戶或者角色進行授權.

DDL(data definition language),  數(shù)據(jù)定義語言. 主要用來創(chuàng)建和刪除數(shù)據(jù)表的.

DML(data manipulation language),數(shù)據(jù)操縱語言, 這個是我們關注的核心. 專門用來操縱數(shù)據(jù)的. 比如, 添加數(shù)據(jù), 刪除數(shù)據(jù), 修改數(shù)據(jù), 查詢數(shù)據(jù)等等.

DDL: 數(shù)據(jù)定義語句

1. 創(chuàng)建數(shù)據(jù)庫, 在mysql中, 保存數(shù)據(jù)最大的單位就是數(shù)據(jù)庫. 一般中小型項目都是一個項目一個數(shù)據(jù)庫. 如果遇見大型項目, 單一的數(shù)據(jù)庫可能就撐不住前端的數(shù)據(jù)量了. 此時可能會涉及到多數(shù)據(jù)庫設計. 我們剛入門, 先學習但數(shù)據(jù)庫操作.

 -- 創(chuàng)建數(shù)據(jù)庫

 CREATE DATABASE IF NOT EXISTS 數(shù)據(jù)庫名 DEFAULT CHARSET UTF8mb4;

 -- 刪除數(shù)據(jù)庫

2. 表操作.  關系型數(shù)據(jù)庫使用二維表的形式來存儲數(shù)據(jù). 就像Excel差不多. 有行和列. 創(chuàng)建表的時候我們要指定每一列的含義, 也就是列名, 在后面使用的時候. 數(shù)據(jù)是以行為單位進行查詢的. 然后從行內拿到你想要獲取的列. 就拿到你想要的數(shù)據(jù)了

 CREATE TABLE IF NOT EXISTS 表名(

    列名 類型(長度) 約束

類型:

數(shù)字一般用int, 還有bit, long等, 但用的不多

字符串: char, varchar. 其中char表示定長的列. varchar表示不定長的列

char(32)  如果你只存儲一個字母. 占用的空間也是32個

varchar(32) 如果值存儲一個字母, 你可以認為就占用1個位置.

時間:

date    年月日

time    小時分鐘秒

datetime  年月日, 時分秒

小數(shù):

float     單精度小數(shù)

double 雙精度小數(shù)

decimal  小數(shù)-> 精度可以自己進行設定. 精度高, 誤差小

約束:

主鍵約束: 可以唯一的表示一條數(shù)據(jù), 我們可以使用主鍵來確定具體的某個人. 就像我們生活中的身份證號, 通過身份證號可以唯一的確定一個人. 通常我們都是使用主鍵自增來維護主鍵.

語法: primary key

主鍵自增: 在添加數(shù)據(jù)的時候. 主鍵不需要給出具體的值. 由mysql自動來維護這個字段, 自動的在原有的數(shù)據(jù)基礎上+1.

語法: AUTO_INCREMENT

非空約束: 設置該列是否可以為空

語法: null 或者 not null

默認值: 在沒有數(shù)據(jù)的情況下, 可以給出默認值

default 值

唯一性約束:

unique

 -- 建表語句

  id int(6) primary key AUTO_INCREMENT,

  age int(5) default 0 COMMENT '年齡',

  salary decimal(10, 2) default 0.0,

  phone char(11) not null unique

 -- 刪除表

 -- 修改表字段信息

 ALTER TABLE stu add address VARCHAR(200);

 -- 刪除字段

 ALTER TABLE stu MODIFY sno varchar(32) unique not null;

3. 基礎SQL語句

1. 增加

insert into 表(字段1, 字段2, 字段3....) values(值1, 值2, 值3....)

-- 添加數(shù)據(jù)

-- 一次性添加多條數(shù)據(jù)

('小鉆風1',22, '18811112221'),

('小鉆風3',22, '18811112223'),

2. 修改

語法: 

update person set name = '唐僧', age = 29, phone='14722223333' where id = '5';

3. 刪除

delete from table where 條件

delete from person; --  全表刪除. 慎用

delete from person where name = '小鉆風1'; -- 刪除名字叫小鉆風1的數(shù)據(jù)

4. 查詢

select  *|[distinct]字段1, 字段2, 字段3... from 表 where 條件 [group by] [having] [order by]

select  * from person;

-- 查詢部分字段

select id as '主鍵ID', name as '名字', age as '年齡' from person;

上條件:

select * from person where age > 20;

-- 查詢電話號是14722223333的人

select * from person where age is null;  

select * from person where age BETWEEN 20 and 22;

-- 查詢手機號是13778787878,14722223333,18811111111的人

-- 查詢年齡大于19 手機號以14開頭的人

select * from person where age > 20 or name = '唐僧';

4. 分組查詢

分組查詢: 按組進行查詢, 分組需要使用group by語句

-- 根據(jù)名字和年齡進行分組. 

分組查詢一般配合聚合函數(shù)一起使用.

sum() 求和

avg() 平均值

max() 最大值

min() 最小值

count() 計數(shù)

-- 聚合函數(shù)

select avg(age) from person

-- 查詢每個班級的平均年齡

select cls, count(id) from person group by cls;

select cls, max(age) from person group by cls;

select cls, min(age) from person group by cls;

select cls, sum(age) from person group by cls;

使用having子句可以對分組查詢之后的結果進行篩選.

select cls, avg(age) from person group by cls having avg(age) > 20;

having和where都可以做數(shù)據(jù)的篩選. 區(qū)別是:

where是在原始數(shù)據(jù)上進行數(shù)據(jù)篩選

having是在分組計算之后的結果進行篩選.

它們起作用的時機是不一樣的

5. 排序

使用order by子句可以完成數(shù)據(jù)的排序工作

select * from person order by age;

select * from person order by age desc;

6. 分頁

select * from student limit 0, 10  -- 從第0條開始查詢, 查詢10條數(shù)據(jù)

select * from student limit 10, 10 -- 從第10條開始查詢, 查詢10條數(shù)據(jù)

-- 查詢第n頁數(shù)據(jù)

7. 多表查詢

首先, 在一個項目里肯定不能都把數(shù)據(jù)放在一張表里.  比如, 我們把員工信息都存放在一張表里:

大家觀察一下.  員工信息沒問題. 但是員工相應的部門信息就有大量的數(shù)據(jù)冗余.  如果一直用這樣的數(shù)據(jù)來保存數(shù)據(jù).  那么冗余的數(shù)據(jù)會占用大量的硬盤存儲空間. 數(shù)據(jù)庫的利用率就變的很低. 所以此時我們要考慮. 把部門信息單獨的拿到另一張表里. 然后把部門表的主鍵引入到員工表里. 這樣從結構和邏輯上講, 效果是一樣的. 但是存儲空間就會節(jié)省很多.

分表:

員工表

部門表

這樣, 我們通過查詢員工表信息就能知道員工的deptId, 再通過deptId,到部門表就能找到該員工對應的部門信息. 最終查詢出的數(shù)據(jù)是一致的.

OK, 分表結束了. 但是, 這里我們必須要保證員工表中的deptId的數(shù)據(jù)來源必須是部門表. 如果deptId是胡亂填寫的. 那該員工的部門信息就是錯誤的. 為了保證數(shù)據(jù)的一致性和安全性. 我們此時需要對deptId這一列數(shù)據(jù)進行約束. 該約束被稱為外鍵約束.

外鍵約束: 某字段的數(shù)據(jù)值來源必須來自于xxx表的xxx字段. 通常我們都是把另一張表的主鍵拿到當前表來做外鍵.  在本案例中, 我們就可以把部門表的id拿到員工表中做外鍵. 外鍵的名字叫deptId

create table dept(

deptName VARCHAR(200),

deptDesc VARCHAR(200)

id int(10) primary key auto_increment,

age int(3),

constraint fk_emp_dept_id FOREIGN KEY (deptId) REFERENCES dept(id)  # 添加外鍵約束

多表聯(lián)合查詢:

1. 子查詢

-- 子查詢

select * from employee 

select id from dept where dept.deptName = '明教'

-- 查詢員工和部門的全部信息

-- 查詢'峨眉派'的平均工資

where deptId in (

) and employee.deptId = dept.id group by deptName;

2. 關聯(lián)查詢

在sql語句中. 還有一種語法可以讓兩張甚至多張表進行關聯(lián)查詢.

1. inner join  內連接

2. left join  左連

3. right join 右連

表示:  A表和B表鏈接. 通過A表的字段1和b表的字段2進行連接. 通常on后面的都是主外鍵關系

-- 內連接

-- 左聯(lián)

-- 右聯(lián)

8. 其他雜七雜八(對于新手小白了解即可)

索引. 當我們的數(shù)據(jù)量很大的時候. 查詢的速度會明顯的下降.  尤其涉及到全表檢索的時候, 此時查詢效率會低到令人發(fā)指.  在數(shù)據(jù)庫系統(tǒng)中, 可以通過添加索引來加快查詢的速度.

索引: 按照一定的數(shù)據(jù)結構, 將該列的數(shù)據(jù)進行特殊處理. 用來提高查詢速度.

默認情況下, 我們設計的每一個主鍵, 都會自動把主鍵設置成索引. 所以我們查詢的時候. 如果是按照主鍵來搜索數(shù)據(jù), 查詢的效率和速度都是最快的.

索引的優(yōu)點: 提高查詢速度

索引的缺點: 由于每次添加新數(shù)據(jù)都會讓索引重新進行排列. 如果頻繁的涉及添加和刪除數(shù)據(jù). 反而會讓數(shù)據(jù)庫的執(zhí)行效率變低.

 -- 創(chuàng)建索引

 -- 刪除索引

當我們遇見復雜的sql語句的時候. 我們可以把一部分查詢的結果(sql) 保存在視圖. 方便后面查詢的時候可以簡化sql語句.

-- 創(chuàng)建視圖

select sname, score from student s inner join sc on s.sid = sc.sid

-- 使用視圖來查詢數(shù)據(jù)

drop view my_view;

注意: 視圖只是簡化查詢的sql語句. 并不能提高性能. 也就是說以上代碼和不適用視圖的查詢效率是一致的.

觸發(fā)器: 在執(zhí)行某些操作的時候, 去執(zhí)行一些其他的操作

例如:  假設, 我們現(xiàn)在要刪除一些數(shù)據(jù)庫中的數(shù)據(jù). 很容易, 寫個delete語句就OK了. 但是, 如果過了1個小時之后, 我們突然發(fā)現(xiàn)刪除的數(shù)據(jù)是有用的. 想要恢復回來. 此時如果沒有任何的備份和日志存在的話, 幾乎是不可能的. 那怎么辦?  我們可以在刪除數(shù)據(jù)之前, 把要刪除的數(shù)據(jù)備份出來. 保存在另一張表中. 萬一要恢復的話. 直接從另一張表里就可以把數(shù)據(jù)讀取出來. 防止數(shù)據(jù)丟失. 但, 此時如果我們每次執(zhí)行一條刪除語句之前都要多寫一個插入語句的話. 就會顯得很繁瑣. 此時我們可以把這個工作交給觸發(fā)器來完成. 當觸發(fā)器設置好之后. 一旦有人刪除數(shù)據(jù). 就可以自動的把你的數(shù)據(jù)保存起來.

    BEFORE|AFTER

    ON 表 FOR EACH ROW

    sql

-- 創(chuàng)建觸發(fā)器

BEFORE DELETE

BEGIN

END;

delete from student where sid = 3

-- 查看

select * from student_backup;

-- 刪除觸發(fā)器

old在觸發(fā)器里表示要刪除的舊數(shù)據(jù). 也可以認為是現(xiàn)在目前數(shù)據(jù)庫中的數(shù)據(jù), 在刪除的時候拿到的就是old

new表示新數(shù)據(jù). 即. 即將進入數(shù)據(jù)庫的數(shù)據(jù). 在新增的時候拿到的就是new

在修改數(shù)據(jù)的時候, 原來的數(shù)據(jù)用old表示, 新數(shù)據(jù)用new來表示

9. pymysql

1. 安裝pymysql

pip install -i https://pypi.tuna.tsinghua.edu.cn/simple pymysql

2. 使用pymysql 完成數(shù)據(jù)庫連接

from pymysql.cursors import DictCursor

# 連接數(shù)據(jù)庫

# 獲取游標

# 執(zhí)行sql

print(cursor.fetchone())  # 拿一個結果

print(cursor.fetchmany(10))  # 拿出n個數(shù)據(jù)

3. 使用pymysql完成各種數(shù)據(jù)庫操作(增刪改)

from pymysql.cursors import DictCursor

# 連接數(shù)據(jù)庫

cursor = db.cursor(DictCursor)

    sql = 'INSERT INTO student(sname, sage, ssex) VALUE ('馬虎疼', 18, 1)'

    db.commit()  # 提交事務. 保證數(shù)據(jù)沒問題

    db.rollback()  # 事務回滾. 回到原來狀態(tài)

    cursor.close()  # 關閉游標

4. 查詢操作

import pymysql

# print(lst)

for row in cursor:  # 直接循環(huán)出結果

5. sql注入以及未來寫sql的注意事項

import pymysql

password = input('請輸入密碼:')

sql = f'select * from user where username = '{username}' and password = '{password}''

cursor.execute(sql)

one = cursor.fetchone()

    print('登錄成功')

else:

輸入密碼的時候搞點兒事情

請輸入用戶名:alex

登錄成功

神奇的一幕出現(xiàn)了,  為什么會這樣呢? 我們把sql拼接出來就知道咋回事兒了

注意看最后的條件  1 = 1 百分之百是成立的. 所以. 任何賬戶都能登錄成功. 所以. 這樣的操作是有問題的.  那如何避免呢? 使用預處理方式就可以有效的屏蔽掉sql注入

import pymysql

password = input('請輸入密碼:')

sql = 'select * from user where username = %(username)s and password = %(password)s'

cursor.execute(sql, {'username': username, 'password': password})

one = cursor.fetchone()

    print('登錄成功')

else:

為防止sql注入, 以后寫sql.  盡量這么寫.

作業(yè):

1. 基本SQL語句(慢慢做,  做一個月)

每個學生可以學習多門課程, 并且, 每一門課程都有得分.  每一門課程都有老師來教. 一個老師可以教多個學生.

看題:

1. 查詢姓“朱”的學生名單

2. 查詢同名同性學生名單，并統(tǒng)計同名人數(shù)

3. 查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程號降序排列

4. 查詢平均成績大于85的所有學生的學號、姓名和平均成績

5. 查詢課程名稱為“睡覺”，且分數(shù)低于60的學生姓名和分數(shù)

6. 查詢所有學生的選課情況

7. 查詢任何一門課程成績在70分以上的姓名、課程名稱和分數(shù)

8. 查詢每門課程被選修的學生數(shù)

9. 查詢不同老師所教不同課程平均分從高到低顯示

10. 按各科平均成績從低到高和及格率的百分數(shù)從高到低順序

11. 查詢和“2”號的同學學習的課程完全相同的其他同學學號和姓名

12. 查詢學過“黃觀”老師所教的所有課的同學的學號、姓名.

13. 把“SC”表中'黃觀'老師教的課的成績都更改為此課程的平均成績

14. 查詢課程編號“2”的成績比課程編號“1”課程低的所有同學的學號、姓名

15. 查詢沒學過“黃觀”老師課的同學的學號、姓名.

16. 查詢平均成績大于60分的同學的學號和平均成績

初始化數(shù)據(jù):

CREATE DATABASE `homework`;

-- ----------------------------

-- ----------------------------

CREATE TABLE `course` (

  `cname` varchar(10) DEFAULT NULL,

  KEY `fk` (`tid`),

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- ----------------------------

CREATE TABLE `sc` (

  `cid` int(3) DEFAULT NULL,

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- ----------------------------

CREATE TABLE `student` (

  `sname` varchar(30) DEFAULT NULL,

  `ssex` int(3) DEFAULT NULL

-- Table structure for teacher

DROP TABLE IF EXISTS `teacher`;

  `tid` int(3) NOT NULL PRIMARY KEY  AUTO_INCREMENT,

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- ----------------------------

INSERT INTO `course` VALUES ('2', '睡覺', '1');

INSERT INTO `course` VALUES ('4', '讀書', '2');

INSERT INTO `course` VALUES ('6', '聽歌', '2');

INSERT INTO `sc` VALUES ('1', '2', '50');

INSERT INTO `sc` VALUES ('1', '4', '80');

INSERT INTO `sc` VALUES ('2', '1', '70');

INSERT INTO `sc` VALUES ('2', '3', '35');

INSERT INTO `sc` VALUES ('2', '5', '78');

INSERT INTO `sc` VALUES ('3', '2', '85');

INSERT INTO `sc` VALUES ('3', '4', '47');

INSERT INTO `sc` VALUES ('4', '1', '55');

INSERT INTO `sc` VALUES ('4', '3', '85');

INSERT INTO `sc` VALUES ('4', '5', '87');

INSERT INTO `sc` VALUES ('5', '2', '5');

INSERT INTO `sc` VALUES ('5', '4', '34');

INSERT INTO `sc` VALUES ('6', '1', '100');

INSERT INTO `sc` VALUES ('6', '3', '100');

INSERT INTO `sc` VALUES ('6', '5', '71');

INSERT INTO `sc` VALUES ('7', '4', '55');

INSERT INTO `sc` VALUES ('8', '6', '50');

INSERT INTO `student` VALUES ('1', '朱元璋', '10', '1');

INSERT INTO `student` VALUES ('3', '朱高熾', '20', '1');

INSERT INTO `student` VALUES ('5', '萬貴妃', '55', '2');

INSERT INTO `student` VALUES ('7', '小郭子', '40', '1');

INSERT INTO `student` VALUES ('9', '猴哥', '5', '1');

INSERT INTO `teacher` VALUES ('2', '商洛');

2. 使用pymysql完成業(yè)務線系統(tǒng)開發(fā).  

表關系：

        ID  用戶名   密碼    郵箱    

        ID  名稱

        ID  主機名  密碼  業(yè)務線ID

需求：

    業(yè)務線管理：

        刪除業(yè)務線

        查看業(yè)務線

        添加主機

        修改主機