MySQL-基本使用

2021 年 3 月 5 日
筆記
Python 高級

閱讀目錄

一.資料庫知識點

資料庫
RDBMS
SQL
MySQL

學習目標

熟練編寫數據的增刪改查相關的 SQL 語句

數據存儲

以前是這樣記錄的：結繩記事

也有這樣記錄的：甲骨

後來開始這樣記錄：圖書

傳統記錄數據的缺點:

不易保存
備份困難
查找不便

現代化手段—-文件

使用簡單，例如python中的open可以打開文件，用read/write對文件進行讀寫，close關閉文件
對於數據容量較大的數據，不能夠很好的滿足，而且性能較差
不易擴展

現代化手段—-資料庫

持久化存儲
讀寫速度極高
保證數據的有效性
對程式支援性非常好，容易擴展

真實的倉庫是這樣的:

我們看到的是這個樣子的

顧客看到的是這個樣子的:

資料庫

資料庫就是一種特殊的文件，其中存儲著需要的數據

關係型資料庫核心元素

數據行(記錄)
數據列(欄位)
數據表(數據行的集合)
資料庫(數據表的集合)

RDBMS

Relational Database Management System

通過表來表示關係型

當前主要使用兩種類型的資料庫：關係型資料庫、非關係型資料庫，本部分主要討論關係型資料庫，對於非關係型資料庫會在後面學習
所謂的關係型資料庫RDBMS，是建立在關係模型基礎上的資料庫，藉助於集合代數等數學概念和方法來處理資料庫中的數據
查看資料庫排名://db-engines.com/en/ranking
關係型資料庫的主要產品：
- oracle：在以前的大型項目中使用,銀行,電信等項目
- mysql：web時代使用最廣泛的關係型資料庫
- ms sql server：在微軟的項目中使用
- sqlite：輕量級資料庫，主要應用在移動平台

RDBMS和資料庫的關係

SQL

Structured Query Language

SQL是結構化查詢語言，是一種用來操作RDBMS的資料庫語言，當前關係型資料庫都支援使用SQL語言進行操作,也就是說可以通過 SQL 操作 oracle,sql server,mysql,sqlite 等等所有的關係型的資料庫

SQL語句主要分為：對於web程式設計師來講，重點是數據的crud（增刪改查），必須熟練編寫DQL、DML，能夠編寫DDL完成資料庫、表的操作，其它語言如TPL、DCL、CCL了解即可
- DQL：數據查詢語言，用於對數據進行查詢，如select
- DML：數據操作語言，對數據進行增加、修改、刪除，如insert、udpate、delete
- TPL：事務處理語言，對事務進行處理，包括begin transaction、commit、rollback
- DCL：數據控制語言，進行授權與許可權回收，如grant、revoke
- DDL：數據定義語言，進行資料庫、表的管理等，如create、drop
- CCL：指針控制語言，通過控制指針完成表的操作，如declare cursor
SQL 是一門特殊的語言,專門用來操作關係資料庫
不區分大小寫

學習要求

熟練掌握數據增刪改查相關的 SQL 語句編寫
在 Python程式碼中操作數據就是通過 SQL 語句來操作數據

# 創建Connection連接
conn = connect(host='localhost', port=3306, user='root', password='mysql', database='python1', charset='utf8')
# 得Cursor對象
cs = conn.cursor()
# 更新
# sql = 'update students set name="劉邦" where id=6'
# 刪除
# sql = 'delete from students where id=6'
# 執行select語句，並返回受影響的行數：查詢一條學生數據
sql = 'select id,name from students where id = 7'
# sql = 'SELECT id,name FROM students WHERE id = 7'
count=cs.execute(sql)
# 列印受影響的行數
print(count)

MySQL 簡介

點擊查看MySQL官方網站
MySQL是一個關係型資料庫管理系統，由瑞典MySQL AB公司開發，後來被Sun公司收購，Sun公司後來又被Oracle公司收購，目前屬於Oracle旗下產品

特點

使用C和C++編寫，並使用了多種編譯器進行測試，保證源程式碼的可移植性
支援多種作業系統，如Linux、Windows、AIX、FreeBSD、HP-UX、MacOS、NovellNetware、OpenBSD、OS/2 Wrap、Solaris等
為多種程式語言提供了API，如C、C++、Python、Java、Perl、PHP、Eiffel、Ruby等
支援多執行緒，充分利用CPU資源
優化的SQL查詢演算法，有效地提高查詢速度
提供多語言支援，常見的編碼如GB2312、BIG5、UTF8
提供TCP/IP、ODBC和JDBC等多種資料庫連接途徑
提供用於管理、檢查、優化資料庫操作的管理工具
大型的資料庫。可以處理擁有上千萬條記錄的大型資料庫
支援多種存儲引擎
MySQL 軟體採用了雙授權政策，它分為社區版和商業版，由於其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，一般中小型網站的開發都選擇MySQL作為網站資料庫
MySQL使用標準的SQL數據語言形式
Mysql是可以訂製的，採用了GPL協議，你可以修改源碼來開發自己的Mysql系統
在線DDL更改功能
複製全局事務標識
複製無崩潰從機
複製多執行緒從機

開源免費不要錢使用範圍廣,跨平台支援性好,提供了多種語言調用的 API

是學習資料庫開發的首選

二.MySQL安裝

伺服器端安裝

安裝伺服器端：在終端中輸入如下命令，回車後，然後按照提示輸入

sudo apt-get install mysql-server

當前使用的ubuntu鏡像中已經安裝好了mysql伺服器端，無需再安裝，並且設置成了開機自啟動
伺服器用於接收客戶端的請求、執行sql語句、管理資料庫
伺服器端一般以服務方式管理，名稱為mysql
啟動服務

sudo service mysql start

查看進程中是否存在mysql服務

ps ajx|grep mysql

停止服務

sudo service mysql stop

重啟服務

sudo service mysql restart

配置

配置文件目錄為/etc/mysql/mysql.cnf

進入conf.d目錄，打開mysql.cnf，發現並沒有配置
進入mysql.conf.d目錄，打開mysql.cnf，可以看到配置項

主要配置項如下

bind-address表示伺服器綁定的ip，默認為127.0.0.1

port表示埠，默認為3306

datadir表示資料庫目錄，默認為/var/lib/mysql

general_log_file表示普通日誌，默認為/var/log/mysql/mysql.log

log_error表示錯誤日誌，默認為/var/log/mysql/error.log

客戶端

客戶端為開發人員與dba使用，通過socket方式與服務端通訊，常用的有navicat、命令行mysql

圖形化介面客戶端navicat

可以到Navicat官網下載
將壓縮文件拷貝到ubuntu虛擬機中，放到桌面上，解壓

tar zxvf navicat112_mysql_cs_x64.tar.gz

./start_navicat

啟動如下圖，詳細功能見下節

點擊兩次「取消」按鈕後如下圖

點擊「試用」按鈕後如下圖

問題一：中文亂碼
解決：打開start_navicat文件

將export LANG="en_US.UTF-8"改為export LANG="zh_CN.UTF-8"

問題二：試用期
解決：刪除用戶目錄下的.navicat64目錄

cd ~
rm -r .navicat64

命令行客戶端

在終端運行如下命令，按提示填寫資訊

sudo apt-get install mysql-client

當前使用的ubuntu鏡像中已經安裝好了mysql客戶端，無需再安裝
詳細連接的命令可以查看幫助文檔

mysql --help

最基本的連接命令如下，輸入後回車

mysql -u root -pmysql

連接成功後提示如下圖

按ctrl+d或輸入如下命令退出

quit 或者 exit

三.數據完整性

一個資料庫就是一個完整的業務單元，可以包含多張表，數據被存儲在表中
在表中為了更加準確的存儲數據，保證數據的正確有效，可以在創建表的時候，為表添加一些強制性的驗證，包括數據欄位的類型、約束

數據類型

可以通過查看幫助文檔查閱所有支援的數據類型
使用數據類型的原則是：夠用就行，盡量使用取值範圍小的，而不用大的，這樣可以更多的節省存儲空間
常用數據類型如下：
- 整數：int，bit
- 小數：decimal
- 字元串：varchar,char
- 日期時間: date, time, datetime
- 枚舉類型(enum)
特別說明的類型如下：更全的數據類型可以參考//blog.csdn.net/anxpp/article/details/51284106
- decimal表示浮點數，如decimal(5,2)表示共存5位數，小數佔2位
- char表示固定長度的字元串，如char(3)，如果填充’ab’時會補一個空格為'ab '
- varchar表示可變長度的字元串，如varchar(3)，填充’ab’時就會存儲’ab’
- 字元串text表示存儲大文本，當字元大於4000時推薦使用
- 對於圖片、音頻、影片等文件，不存儲在資料庫中，而是上傳到某個伺服器上，然後在表中存儲這個文件的保存路徑

約束

主鍵primary key：物理上存儲的順序
非空not null：此欄位不允許填寫空值
惟一unique：此欄位的值不允許重複
默認default：當不填寫此值時會使用默認值，如果填寫時以填寫為準
外鍵foreign key：對關係欄位進行約束，當為關係欄位填寫值時，會到關聯的表中查詢此值是否存在，如果存在則填寫成功，如果不存在則填寫失敗並拋出異常
說明：雖然外鍵約束可以保證數據的有效性，但是在進行數據的crud（增加、修改、刪除、查詢）時，都會降低資料庫的性能，所以不推薦使用，那麼數據的有效性怎麼保證呢？答：可以在邏輯層進行控制

數值類型(常用)

類型	位元組大小	有符號範圍(Signed)	無符號範圍(Unsigned)
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32768 ~ 32767	0 ~ 65535
MEDIUMINT	3	-8388608 ~ 8388607	0 ~ 16777215
INT/INTEGER	4	-2147483648 ~2147483647	0 ~ 4294967295
BIGINT	8	-9223372036854775808 ~ 9223372036854775807	0 ~ 18446744073709551615

字元串

類型	位元組大小	示例
CHAR	0-255	類型:char(3) 輸入 ‘ab’, 實際存儲為’ab ‘, 輸入’abcd’ 實際存儲為 ‘abc’
VARCHAR	0-255	類型:varchar(3) 輸 ‘ab’,實際存儲為’ab’, 輸入’abcd’,實際存儲為’abc’
TEXT	0-65535	大文本

日期時間類型

類型	位元組大小	示例
DATE	4	‘2020-01-01’
TIME	3	’12:29:59′
DATETIME	8	‘2020-01-01 12:29:59’
YEAR	1	‘2017’
TIMESTAMP	4	‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-01 00:00:01’ UTC

四.Navicat連接

打開navicat，點擊工具欄的「連接」，選擇「mysql」，彈出窗口如下圖

在彈出的窗口中填寫名稱、主機ip、埠、用戶名、密碼，如下圖
密碼為mysql

點擊確定，在左側欄會看到剛才填寫的名稱，雙擊打開連接，如下圖

創建資料庫

在左側欄空白處右擊，選擇「新建資料庫」，點擊

填寫完成後點擊「確定」創建資料庫，在左側會出現剛才創建的資料庫，雙擊打開

編輯資料庫

在左側欄資料庫上右擊，彈出菜單，如下圖

選擇「編輯資料庫」，可以修改字符集、排序規則，如下圖

選擇「轉儲sql文件」，可以完成結構和數據的備份，如下圖：

選擇「刪除資料庫」，可以將資料庫刪除

創建數據表

點擊工具欄「表」，在第二行顯示關於表的命令，點擊「創建表」

彈出新窗口，按照上節的設計，創建班級表，填寫各欄位，選擇相應的類型

對於id欄位，需要設置為int類型，無符號，自動增長，主鍵，非空
對於字元串類型，必須指定包含字元個數，還需要指定字符集、排序規則，默認與資料庫的一致
datetime的默認值可以設置成now(),也可以是一個具體值，如’2000-1-1′
按照同樣的方式創建學生表students

編輯數據表

選擇一張表後，工具欄的第二行「打開表」、「設計表」、「刪除表」都變的可用

打開表會查看錶的當前數據，可以在這個窗口中增加、修改、刪除數據
設計表和創建表的窗口一樣，可以增加、修改、刪除欄位，或編輯欄位的類型、約束
刪除表會將表物理刪除

查看數據

雙擊表，或者選擇表後，點擊工具欄第二行的「打開表」，可以查看錶的數據

增加數據

默認沒有數據，可以在對應的列中填寫數據，點擊底部的對勾完成添加
注意：自動增長的主鍵列不需要填寫值

如果需要繼續添加數據，點擊詢問的加號，會出現一個新的空白行，填寫數據即可

修改數據

點擊某個單元格，即可編輯值，修改完後，點擊底部的勾生效

刪除數據

點擊某個單元格，再點擊詢問的減號，可以刪除

說明：對於重要數據，推薦將isdelete屬性改為1，而不是進行物理刪除

五.命令行腳本（重點）

命令行連接

在工作中主要使用命令操作方式，要求熟練編寫
打開終端，運行命令

mysql -uroot -p
回車後輸入密碼，當前設置的密碼為mysql

連接成功後如下圖

退出登錄

quit 和 exit
或
ctrl+d

登錄成功後，輸入如下命令查看效果

查看版本：select version();
顯示當前時間：select now();

修改輸入提示符

prompt python>

\D 完整日期
\U 使用用戶

資料庫

查看所有資料庫

show databases;

使用資料庫

use 資料庫名;

查看當前使用的資料庫

select database();

創建資料庫

create database 資料庫名 charset=utf8;
例：
create database python charset=utf8;

刪除資料庫

drop database 資料庫名;
例：
drop database python;

數據表

查看當前資料庫中所有表

show tables;

查看錶結構

desc 表名;

創建表
auto_increment表示自動增長

CREATE TABLE table_name(
    column1 datatype contrai,
    column2 datatype,
    column3 datatype,
    .....
    columnN datatype,
    PRIMARY KEY(one or more columns)
);

例：創建班級表

create table classes(
    id int unsigned auto_increment primary key not null,
    name varchar(10)
);

例：創建學生表

create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男','女','人妖','保密'),
    cls_id int unsigned default 0
)

修改表-添加欄位

alter table 表名 add 列名 類型;
例：
alter table students add birthday datetime;

修改表-修改欄位：重命名版

alter table 表名 change 原名 新名 類型及約束;
例：
alter table students change birthday birth datetime not null;

修改表-修改欄位：不重命名版

alter table 表名 modify 列名 類型及約束;
例：
alter table students modify birth date not null;

修改表-刪除欄位

alter table 表名 drop 列名;
例：
alter table students drop birthday;

刪除表

drop table 表名;
例：
drop table students;

查看錶的創建語句

show create table 表名;
例：
show create table classes;

增刪改查(curd)

curd的解釋: 代表創建（Create）、更新（Update）、讀取（Retrieve）和刪除（Delete）

查詢基本使用

查詢所有列

select * from 表名;
例：
select * from classes;

查詢指定列
可以使用as為列或表指定別名

select 列1,列2,... from 表名;
例：
select id,name from classes;

增加

格式:INSERT [INTO] tb_name [(col_name,…)] {VALUES | VALUE} ({expr | DEFAULT},…),(…),…

說明：主鍵列是自動增長，但是在全列插入時需要佔位，通常使用0或者 default 或者 null 來佔位，插入成功後以實際數據為準
全列插入：值的順序與表中欄位的順序對應

insert into 表名 values(...)
例：
insert into students values(0,』郭靖『,1,'蒙古','2016-1-2');

部分列插入：值的順序與給出的列順序對應

insert into 表名(列1,...) values(值1,...)
例：
insert into students(name,hometown,birthday) values('黃蓉','桃花島','2016-3-2');

上面的語句一次可以向表中插入一行數據，還可以一次性插入多行數據，這樣可以減少與資料庫的通訊
全列多行插入：值的順序與給出的列順序對應

insert into 表名 values(...),(...)...;
例：
insert into classes values(0,'python1'),(0,'python2');
insert into 表名(列1,...) values(值1,...),(值1,...)...;
例：
insert into students(name) values('楊康'),('楊過'),('小龍女');

修改

格式: UPDATE tbname SET col1={expr1|DEFAULT} [,col2={expr2|default}]…[where 條件判斷]

update 表名 set 列1=值1,列2=值2... where 條件
例：
update students set gender=0,hometown='北京' where id=5;

刪除

DELETE FROM tbname [where 條件判斷]

delete from 表名 where 條件
例：
delete from students where id=5;

邏輯刪除，本質就是修改操作

update students set isdelete=1 where id=1;

備份

運行mysqldump命令

mysqldump –uroot –p 資料庫名 > python.sql;

# 按提示輸入mysql的密碼

恢復

連接mysql，創建新的資料庫
退出連接，執行如下命令

mysql -uroot –p 新資料庫名 < python.sql

# 根據提示輸入mysql密碼

六.資料庫設計

關係型資料庫建議在E-R模型的基礎上，我們需要根據產品經理的設計策劃，抽取出來模型與關係，制定出表結構，這是項目開始的第一步
在開發中有很多設計資料庫的軟體，常用的如power designer，db desinger等，這些軟體可以直觀的看到實體及實體間的關係
設計資料庫，可能是由專門的資料庫設計人員完成，也可能是由開發組成員完成，一般是項目經理帶領組員來完成
現階段不需要獨立完成資料庫設計，但是要注意積累一些這方面的經驗

三範式

經過研究和對使用中問題的總結，對於設計資料庫提出了一些規範，這些規範被稱為範式(Normal Form)
目前有跡可尋的共有8種範式，一般需要遵守3範式即可
◆ 第一範式（1NF）：強調的是列的原子性，即列不能夠再分成其他幾列。

考慮這樣一個表：【聯繫人】（姓名，性別，電話）如果在實際場景中，一個聯繫人有家庭電話和公司電話，那麼這種表結構設計就沒有達到 1NF。要符合 1NF 我們只需把列（電話）拆分，即：【聯繫人】（姓名，性別，家庭電話，公司電話）。1NF 很好辨別，但是 2NF 和 3NF 就容易搞混淆。
◆ 第二範式（2NF）：首先是 1NF，另外包含兩部分內容，一是表必須有一個主鍵；二是沒有包含在主鍵中的列必須完全依賴於主鍵，而不能只依賴於主鍵的一部分。

考慮一個訂單明細表：【OrderDetail】（OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName）。因為我們知道在一個訂單中可以訂購多種產品，所以單單一個 OrderID 是不足以成為主鍵的，主鍵應該是（OrderID，ProductID）。顯而易見 Discount（折扣），Quantity（數量）完全依賴（取決）於主鍵（OderID，ProductID），而 UnitPrice，ProductName 只依賴於 ProductID。所以 OrderDetail 表不符合 2NF。不符合 2NF 的設計容易產生冗餘數據。

可以把【OrderDetail】表拆分為【OrderDetail】（OrderID，ProductID，Discount，Quantity）和【Product】（ProductID，UnitPrice，ProductName）來消除原訂單表中UnitPrice，ProductName多次重複的情況。
◆ 第三範式（3NF）：首先是 2NF，另外非主鍵列必須直接依賴於主鍵，不能存在傳遞依賴。即不能存在：非主鍵列 A 依賴於非主鍵列 B，非主鍵列 B 依賴於主鍵的情況。

考慮一個訂單表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主鍵是（OrderID）。其中 OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity 等非主鍵列都完全依賴於主鍵（OrderID），所以符合 2NF。不過問題是 CustomerName，CustomerAddr，CustomerCity 直接依賴的是 CustomerID（非主鍵列），而不是直接依賴於主鍵，它是通過傳遞才依賴於主鍵，所以不符合 3NF。通過拆分【Order】為【Order】（OrderID，OrderDate，CustomerID）和【Customer】（CustomerID，CustomerName，CustomerAddr，CustomerCity）從而達到 3NF。 *第二範式（2NF）和第三範式（3NF）的概念很容易混淆，區分它們的關鍵點在於，2NF：非主鍵列是否完全依賴於主鍵，還是依賴於主鍵的一部分；3NF：非主鍵列是直接依賴於主鍵，還是直接依賴於非主鍵列。

不遵循1NF

不遵循2NF

不遵循3NF

最終表

E-R模型

E表示entry，實體，設計實體就像定義一個類一樣，指定從哪些方面描述對象，一個實體轉換為資料庫中的一個表
R表示relationship，關係，關係描述兩個實體之間的對應規則，關係的類型包括包括一對一、一對多、多對多
關係也是一種數據，需要通過一個欄位存儲在表中
實體A對實體B為1對1，則在表A或表B中創建一個欄位，存儲另一個表的主鍵值

實體A對實體B為1對多：在表B中創建一個欄位，存儲表A的主鍵值

實體A對實體B為多對多：新建一張表C，這個表只有兩個欄位，一個用於存儲A的主鍵值，一個用於存儲B的主鍵值

想一想：舉些例子，滿足一對一、一對多、多對多的對應關係

邏輯刪除

對於重要數據，並不希望物理刪除，一旦刪除，數據無法找回
刪除方案：設置isDelete的列，類型為bit，表示邏輯刪除，默認值為0
對於非重要數據，可以進行物理刪除
數據的重要性，要根據實際開發決定

示例

設計兩張表：班級表、學生表
班級表classes
- id
- name
- isdelete
學生表students
- id
- name
- birthday
- gender
- clsid
- isdelete

擴展閱讀

看看別人家設計的規範
58到家資料庫30條軍規解讀

MySQL-基本使用

閱讀目錄

一.資料庫知識點

學習目標

數據存儲

傳統記錄數據的缺點:

現代化手段—-文件

現代化手段—-資料庫

真實的倉庫是這樣的:

我們看到的是這個樣子的

顧客看到的是這個樣子的:

資料庫

資料庫就是一種特殊的文件，其中存儲著需要的數據

關係型資料庫核心元素

RDBMS

RDBMS和資料庫的關係

SQL

學習要求

MySQL 簡介

點擊查看MySQL官方網站

特點

二.MySQL安裝

伺服器端安裝

配置

客戶端

圖形化介面客戶端navicat

命令行客戶端

三.數據完整性

數據類型

約束

四.Navicat連接

創建資料庫

編輯資料庫

創建數據表

編輯數據表

查看數據

增加數據

修改數據

刪除數據

五.命令行腳本（重點）

命令行連接

修改輸入提示符

資料庫

數據表

增刪改查(curd)

查詢基本使用

增加

修改

刪除

備份

恢復

六.資料庫設計

三範式

不遵循1NF

不遵循2NF

不遵循3NF

最終表

E-R模型

邏輯刪除

示例

擴展閱讀

分享此文：

Related Posts

如何獲取QQ/微信好友的IP地址

《程式設計師漫畫》| 萌新面試Google

盤點Excel中的那些有趣的「bug」

記一次Drone無法觸發構建的問題

VirMach 便宜 VPS

QNews

熱門搜尋