計算機誕生后，數(shù)據(jù)開始在計算機中存儲并計算，并設(shè)計出了數(shù)據(jù)庫系統(tǒng)，數(shù)據(jù)庫系統(tǒng)解決的問題：持久化存儲，優(yōu)化讀寫，保證數(shù)據(jù)的有效性。
當前使用的數(shù)據(jù)庫，主要分為兩類
1、文檔型，如sqlite，就是一個文件，通過對文件的復制完成數(shù)據(jù)庫的復制；
2、服務(wù)型，如mysql、oracle、postgre，數(shù)據(jù)存儲在一個物理文件中，但是需要使用終端以tcp/ip協(xié)議連接，進行數(shù)據(jù)庫的讀寫操作。
E-R模型
當前物理的數(shù)據(jù)庫都是按照E-R模型進行設(shè)計的，E表示entity，實體，R表示relationship，關(guān)系
ER模型是構(gòu)建現(xiàn)實世界和程序數(shù)據(jù)世界的橋梁，通過ER圖可以理清楚程序的業(yè)務(wù)關(guān)系，在工程中只有先把業(yè)務(wù)關(guān)系搞清楚了，才好設(shè)計數(shù)據(jù)庫，才好進行相應的編碼工作。其實在一個大的工程中，設(shè)計數(shù)據(jù)庫和理清業(yè)務(wù)關(guān)系甚至要比實際的編碼時間都要長。
關(guān)系描述兩個實體之間的對應規(guī)則，包括
1.一對一
合法的情況下：一個男人娶一個女人，一個女人嫁一個男人
2.一對多
目前教室里，一個老師教多個學生，一個學生被一個老師教
一個教室里有很多學生，一個學生只能一個教室
3.多對多
在大學中，一個學生選很多課程，一個課程被學生選
在大學里，一個學生可以有很多老師，一個老師可以教很多學生
關(guān)系轉(zhuǎn)換為數(shù)據(jù)庫表中的一個列在關(guān)系型數(shù)據(jù)庫中一行就是一個對象
三范式
經(jīng)過研究和對使用中問題的總結(jié)，對于設(shè)計數(shù)據(jù)庫提出了一些規(guī)范，這些規(guī)范被稱為范式
1.第一范式（1NF)：列不可拆分
2.第二范式（2NF)：唯一標識
3.第三范式（3NF)：引用主鍵
簡而言之：
說明：后一個范式，都是在前一個范式的基礎(chǔ)上建立的。
數(shù)據(jù)類型
MySQL支持所有標準的SQL數(shù)據(jù)類型，主要分3類：數(shù)值類型、字符串類型、時間日期類型。
1、數(shù)值類型
1.1、整數(shù)類型
下面一張表就能解釋清楚：
1.2、定點數(shù)
DECIMAL和NUMERIC類型在MySQL中視為相同的類型。它們用于保存必須為確切精度的值。
我們看到上面這個例子中有兩個參數(shù)，即DECIMAL(M,D)，其中M表示十進制數(shù)字總的個數(shù)，D表示小數(shù)點后面數(shù)字的位數(shù)，上例中的取值范圍為-999.99~999.99。
如果存儲時，整數(shù)部分超出了范圍（如上面的例子中，添加數(shù)值為1000.01），MySql就會報錯，不允許存這樣的值。
如果存儲時，小數(shù)點部分若超出范圍，就分以下情況：
若四舍五入后，整數(shù)部分沒有超出范圍，則只警告，但能成功操作并四舍五入刪除多余的小數(shù)位后保存。如999.994實際被保存為999.99。
若四舍五入后，整數(shù)部分超出范圍，則MySql報錯，并拒絕處理。如999.995和-999.995都會報錯。
M的默認取值為10，D默認取值為0。如果創(chuàng)建表時，某字段定義為decimal類型不帶任何參數(shù)，等同于decimal(10,0)。帶一個參數(shù)時，D取默認值。
M的取值范圍為1~65，取0時會被設(shè)為默認值，超出范圍會報錯。
D的取值范圍為0~30，而且必須<=M，超出范圍會報錯。
所以，很顯然，當M=65，D=0時，可以取得最大和最小值。
1.3、浮點數(shù)
浮點數(shù)是用來表示實數(shù)的一種方法，相對于定點數(shù)來說，在長度一定的情況下，具有表示數(shù)據(jù)范圍大的特點。但同時也存在誤差問題，如果希望保證值比較準確，推薦使用定點數(shù)數(shù)據(jù)類型。
MySql中的浮點類型有float，double和real。他們定義方式為：FLOAT(M,D) 、 REAL(M,D) 、 DOUBLE PRECISION(M,D)。
REAL就是DOUBLE?，如果SQL服務(wù)器模式包括REAL_AS_FLOAT選項，REAL是FLOAT的同義詞而不是DOUBLE的同義詞。
“(M,D)”表示該值一共顯示M位整數(shù)，其中D位位于小數(shù)點后面。例如，定義為FLOAT(7,4)的一個列可以顯示為-999.9999。MySQL保存值時進行四舍五入，因此如果在FLOAT(7,4)列內(nèi)插入999.00009，近似結(jié)果是999.0001。
FLOAT和DOUBLE中的M和D的取值默認都為0，即除了最大最小值，不限制位數(shù)。
M取值范圍為0~255。FLOAT只保證6位有效數(shù)字的準確性，所以FLOAT(M,D)中，M<=6時，數(shù)字通常是準確的。如果M和D都有明確定義，其超出范圍后的處理同decimal。
D取值范圍為0~30，同時必須<=M。double只保證16位有效數(shù)字的準確性，所以DOUBLE(M,D)中，M<=16時，數(shù)字通常是準確的。如果M和D都有明確定義，其超出范圍后的處理同decimal。
FLOAT和DOUBLE中，若M的定義分別超出7和17，則多出的有效數(shù)字部分，取值是不定的，通常數(shù)值上會發(fā)生錯誤。因為浮點數(shù)是不準確的，所以我們要避免使用“=”來判斷兩個數(shù)是否相等。
內(nèi)存中，F(xiàn)LOAT占4-byte（1位符號位 8位表示指數(shù) 23位表示尾數(shù)），DOUBLE占8-byte（1位符號位 11位表示指數(shù) 52位表示尾數(shù)）。
1.4、bit(1)
BIT數(shù)據(jù)類型可用來保存位字段值。BIT(M)類型允許存儲M位值。M范圍為1~64(即64位二進制數(shù))，默認為1，BIT其實就是存入二進制的值，類似010110。
如果存入一個BIT類型的值，位數(shù)少于M值，則左補0.
如果存入一個BIT類型的值，位數(shù)多于M值，MySQL的操作取決于此時有效的SQL模式：
如果模式未設(shè)置，MySQL將值裁剪到范圍的相應端點，并保存裁減好的值。
如果模式設(shè)置為traditional(“嚴格模式”)，超出范圍的值將被拒絕并提示錯誤，并且根據(jù)SQL標準插入會失敗。
看下面官方給出的例子：
2、字符串類型
2.1、CHAR和VARCHAR類型
CHAR和VARCHAR類型聲明的長度表示你想要保存的最大字符數(shù)。例如，CHAR(30)可以占用30個字符。默認長度都為255。
CHAR列的長度固定為創(chuàng)建表時聲明的長度。長度可以為從0到255的任何值。當保存CHAR值時，在它們的右邊填充空格以達到指定的長度。當檢索到CHAR值時，尾部的空格被刪除掉，所以，我們在存儲時字符串右邊不能有空格，即使有，查詢出來后也會被刪除。在存儲或檢索過程中不進行大小寫轉(zhuǎn)換。
所以當char類型的字段為唯一值時，添加的值是否已經(jīng)存在以不包含末尾空格（可能有多個空格）的值確定，比較時會在末尾補滿空格后與現(xiàn)已存在的值比較。
VARCHAR列中的值為可變長字符串。長度可以指定為0到65,535之間的值（實際可指定的最大長度與編碼和其他字段有關(guān)，比如，本人MySql使用utf-8編碼格式，大小為標準格式大小的2倍，僅有一個varchar字段時實測最大值僅21844，如果添加一個char(3)，則最大取值減少3。整體最大長度是65,532字節(jié)）。
同CHAR對比，VARCHAR值保存時只保存需要的字符數(shù)，另加一個字節(jié)來記錄長度(如果列聲明的長度超過255，則使用兩個字節(jié))。
VARCHAR值保存時不進行填充。當值保存和檢索時尾部的空格仍保留，符合標準SQL。
如果分配給CHAR或VARCHAR列的值超過列的最大長度，則對值進行裁剪以使其適合。如果被裁掉的字符是空格，則會產(chǎn)生一條警告。如果裁剪非空格字符，則會造成錯誤(而不是警告)并通過使用嚴格SQL模式禁用值的插入。
下面顯示了將各種字符串值保存到CHAR(4)和VARCHAR(4)列后的結(jié)果：
表中最后一行的值只適用在不使用嚴格模式時；如果MySQL運行使用嚴格模式，超過列長度的值不保存，并且會出現(xiàn)錯誤。因為空格的原因，相同的值存入到長度都足夠的varvhar和char中，取出可能會不同，比如"a"和"a ?"。
2.2、BINARY和VARBINARY類型（2）
BINARY和VARBINARY類型類似于CHAR和VARCHAR類型，但是不同的是，它們存儲的不是字符字符串，而是二進制串。所以它們沒有字符集，并且排序和比較基于列值字節(jié)的數(shù)值值。
當保存BINARY值時，在它們右邊填充0x00(零字節(jié))值以達到指定長度。取值時不刪除尾部的字節(jié)。比較時所有字節(jié)很重要（因為空格和0x00是不同的，0x00<空格），包括ORDER BY和DISTINCT操作。比如插入'a '會變成'a \0'。
對于VARBINARY，插入時不填充字符，選擇時不裁剪字節(jié)。比較時所有字節(jié)很重要。
當類型為BINARY的字段為主鍵時，應考慮上面介紹的存儲方式。
2.3、BLOB和TEXT類型
BLOB是一個二進制大對象，可以容納可變數(shù)量的數(shù)據(jù)。有4種BLOB類型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它們只是可容納值的最大長度不同。
有4種TEXT類型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。這些對應4種BLOB類型，有相同的最大長度和存儲需求。
BLOB列被視為二進制字符串。TEXT列被視為字符字符串，類似CHAR和BINARY。
在TEXT或BLOB列的存儲或檢索過程中，不存在大小寫轉(zhuǎn)換。
在大多數(shù)方面，可以將BLOB列視為能夠足夠大的VARBINARY列。同樣，可以將TEXT列視為VARCHAR列。
BLOB和TEXT在以下幾個方面不同于VARBINARY和VARCHAR：
1、當保存或檢索BLOB和TEXT列的值時不刪除尾部空格。(這與VARBINARY和VARCHAR列相同）。
2、比較時將用空格對TEXT進行擴充以適合比較的對象，正如CHAR和VARCHAR。
3、對于BLOB和TEXT列的索引，必須指定索引前綴的長度。對于CHAR和VARCHAR，前綴長度是可選的。
4、BLOB和TEXT列不能有默認值。
MySQL Connector/ODBC將BLOB值定義為LONGVARBINARY，將TEXT值定義為LONGVARCHAR。
BLOB或TEXT對象的最大大小由其類型確定，但在客戶端和服務(wù)器之間實際可以傳遞的最大值由可用內(nèi)存數(shù)量和通信緩存區(qū)大小確定。你可以通過更改max_allowed_packet變量的值更改消息緩存區(qū)的大小，但必須同時修改服務(wù)器和客戶端程序。
3、時間日期類型
3.1、DATE, DATETIME, 和TIMESTAMP類型
這三者其實是關(guān)聯(lián)的，都用來表示日期或時間。
當你需要同時包含日期和時間信息的值時則使用DATETIME類型。MySQL以'YYYY-MM-DD HH:MM:SS'格式檢索和顯示DATETIME值。支持的范圍為'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。
當你只需要日期值而不需要時間部分時應使用DATE類型。MySQL用'YYYY-MM-DD'格式檢索和顯示DATE值。支持的范圍是'1000-01-01'到 '9999-12-31'。
TIMESTAMP類型同樣包含日期和時間，范圍從'1970-01-01 00:00:01' UTC 到'2038-01-19 03:14:07' UTC。
3.2、TIME類型
MySQL以'HH:MM:SS'格式檢索和顯示TIME值(或?qū)τ诖蟮男r值采用'HHH:MM:SS'格式)。
TIME值的范圍可以從'-838:59:59'到'838:59:59'。小時部分會因此大的原因是TIME類型不僅可以用于表示一天的時間(必須小于24小時)，還可能為某個事件過去的時間或兩個事件之間的時間間隔(可以大于24小時，或者甚至為負)。
對于指定為包括時間部分間割符的字符串的TIME值，如果時、分或者秒值小于10，則不需要指定兩位數(shù)。'8:3:2'與'08:03:02'相同。
為TIME列分配簡寫值時應注意。沒有冒號，MySQL解釋值時假定最右邊的兩位表示秒。(MySQL解釋TIME值為過去的時間而不是當天的時間）。例如，你可能認為'1112'和1112表示'11:12:00'(11點過12分)，但MySQL將它們解釋為'00:11:12'(11分，12 秒)。同樣，'12'和12 被解釋為 '00:00:12'。相反，TIME值中使用冒號則肯定被看作當天的時間。也就是說，'11:12'表示'11:12:00'，而不是'00:11:12'。
3.3、YEAR類型
YEAR類型是一個單字節(jié)類型用于表示年。MySQL以YYYY格式檢索和顯示YEAR值。范圍是1901到2155。
可以指定各種格式的YEAR值：
1、四位字符串，范圍為'1901'到'2155'。
2、四位數(shù)字，范圍為1901到2155。
3、兩位字符串，范圍為'00'到'99'。'00'到'69'和'70'到'99'范圍的值被轉(zhuǎn)換為2000到2069和1970到1999范圍的YEAR值。
4、兩位整數(shù)，范圍為1到99。1到69和70到99范圍的值被轉(zhuǎn)換為2001到2069和1970到1999范圍的YEAR值。請注意兩位整數(shù)范圍與兩位字符串范圍稍有不同，因為你不能直接將零指定為數(shù)字并將它解釋為2000。你必須將它指定為一個字符串'0'或'00'或它被解釋為0000。
非法YEAR值被轉(zhuǎn)換為0000。
約束
1.主鍵primary key
2.非空not null
3.惟一unique
4.默認default
5.外鍵foreign key
基本操作
·創(chuàng)建數(shù)據(jù)庫：create database 數(shù)據(jù)庫名 charset=utf8;
·刪除數(shù)據(jù)庫：drop database數(shù)據(jù)庫名;
·切換數(shù)據(jù)庫：use數(shù)據(jù)庫名;
·查看當前選擇的數(shù)據(jù)庫：select database();
·查看當前數(shù)據(jù)庫中所有表：show tables;
·創(chuàng)建表：create table表名(列及類型);? ? ??
主鍵本身是用來唯一標識這一行，沒有業(yè)務(wù)邏輯意義，所以是什么值不重要，只要唯一就行，所以如果是主鍵不需要修改，auto_increment表示自動增長,只能是數(shù)字類型。
如：
create table students(
id int auto_increment primary key,
sname varchar(10) not null
);
·修改表：alter table表名add|modify|drop列名類型;
如：alter table students add birthday datetime;
·刪除表：drop table表名;
·查看表結(jié)構(gòu)：desc表名;
·更改表名稱：rename table原表名to新表名;
·查看表的創(chuàng)建語句：show create table '表名';
·查詢：select * from表名
·增加
全行插入：insert into表名values(...)
缺省插入：insert into表名(列1,...) values(值1,...)
同時插入多條數(shù)據(jù)：insert into表名values(...),(...)...;
或insert into表名(列1,...) values(值1,...),(值1,...)...;
·主鍵列是自動增長，但是在全列插入時需要占位，通常使用0，插入成功后以實際數(shù)據(jù)為準
·修改：update 表名 set 列1=值1,... where條件
·刪除：delete?from 表名 where 條件
·邏輯刪除，本質(zhì)就是修改操作update
alter table students add isdelete bit default 0;
如果需要刪除則
update students isdelete=1 where ...;
備份與恢復
數(shù)據(jù)備份
·進入超級管理員:sudo -s
·運行mysqldump命令:mysqldump–uroot –p數(shù)據(jù)庫名> ~/備份文件.sql;
按提示輸入mysql的密碼
數(shù)據(jù)恢復
·連接mysql，創(chuàng)建數(shù)據(jù)庫
·退出連接，執(zhí)行命令:mysql -uroot -p數(shù)據(jù)庫名< ~/備份文件.sql
根據(jù)提示輸入mysql密碼
查詢：
聚合：
分組：分組前面查詢的內(nèi)容只能是聚合函數(shù)和分組
排序：
分頁：
模糊查詢：like(%表示任意多個任意字符,??_表示一個任意字符)
查詢范圍：·in表示在一個非連續(xù)的范圍內(nèi)，between ... and ...表示在一個連續(xù)的范圍內(nèi)。
非空判斷：is null和is not null
去重復：distinct
多表直接查詢其實做的是一個笛卡爾積，可以看到結(jié)果并不是我們想要的：
多表查詢：
1、內(nèi)連接：join或inner join
以上兩種寫法等效，可以看到在DEPT表中，有deptno為40的記錄，但在EMP表中，并沒有員工屬于40這個deptno,在進行內(nèi)斂查詢時，因為neptno為40無法匹配，所以就自動將其刪除了，所以內(nèi)連接不會保留無法匹配的記錄。
內(nèi)連接同樣可以進行一些篩選，有兩種方式的篩選，其效果相同：
2、外聯(lián)接：left/right outer join或 left/right join
right表示join右邊的是主表，左邊的是次表，進行外聯(lián)接時，主表無法匹配的記錄不會忽略，而會保留，次表中記錄的相應內(nèi)容會用NULL填充。
自關(guān)聯(lián)：
舉一個簡單的例子來說明自關(guān)聯(lián)，假如現(xiàn)在有兩張表結(jié)構(gòu)如下：
觀察兩張表發(fā)現(xiàn)，citys表比provinces表多一個列proid，其它列的類型都是一樣的，存儲的都是地區(qū)信息，而且每種信息的數(shù)據(jù)量有限，沒必要增加一個新表，或者將來還要存儲區(qū)、鄉(xiāng)鎮(zhèn)信息，都增加新表的開銷太大。因此我們可以把這兩張表合并為一張表。
因為省沒有所屬的省份，所以可以填寫為null
城市所屬的省份pid，填寫省所對應的編號id
這就是自關(guān)聯(lián)，表中的某一列，關(guān)聯(lián)了這個表中的另外一列，但是它們的業(yè)務(wù)邏輯含義是不一樣的，城市信息的pid引用的是省信息的id，在這個表中，結(jié)構(gòu)不變，可以添加區(qū)縣、鄉(xiāng)鎮(zhèn)街道、村社區(qū)等信息。
子查詢案例：
內(nèi)置函數(shù)：
1、字符串函數(shù)：
2、數(shù)學函數(shù)
3、時間日期函數(shù)
視圖
為了引入視圖先看下面一個簡單的查詢操作的例子：
這是一個簡單的查詢命令，相對而言還是稍微有點長的，在工作中，我們遇到的數(shù)據(jù)庫操作可能比這樣的SQL語句冗長的多，每次重新再寫相同的命令會顯得十分繁瑣，那么能不能像python中的函數(shù)那樣把SQL語句封裝起來，每次只需要調(diào)用就可以進行多次重復的操作而實現(xiàn)代碼復用呢？視圖就是實現(xiàn)一種類似的功能。視圖本質(zhì)就是對查詢的一個封裝，虛擬的表，一旦封裝的內(nèi)容改變了，視圖的內(nèi)容也隨著用，視圖就是用來進行進行查詢操作的。通過創(chuàng)建視圖，我們可以看到下面的代碼實現(xiàn)了和上述代碼相同的功能，且提高了代碼的復用性。
事務(wù)
當一個業(yè)務(wù)邏輯需要多個sql完成時，如果其中某條sql語句出錯，則希望整個操作都退回，即回到操作前的狀態(tài)，保證數(shù)據(jù)的正確性。使用事務(wù)可以完成退回的功能，保證業(yè)務(wù)邏輯的正確性。
事物有兩個功能：有一個出問題，回滾。都沒有問題，提交。(同生共死)
表的引擎類型必須是innodb或bdb類型，才可以對此表使用事務(wù)
事務(wù)四大特性(簡稱ACID)
1、原子性(Atomicity)：事務(wù)中的全部操作在數(shù)據(jù)庫中是不可分割的，要么全部完成，要么均不執(zhí)行；
2、一致性(Consistency)：幾個并行執(zhí)行的事務(wù)，其執(zhí)行結(jié)果必須與按某一順序串行執(zhí)行的結(jié)果相一致；
3、隔離性(Isolation)：事務(wù)的執(zhí)行不受其他事務(wù)的干擾，事務(wù)執(zhí)行的中間結(jié)果對其他事務(wù)必須是透明的；
4、持久性(Durability)：對于任意已提交事務(wù)，系統(tǒng)必須保證該事務(wù)對數(shù)據(jù)庫的改變不被丟失，即使數(shù)據(jù)庫出現(xiàn)故障。
事務(wù)語句：
開啟begin;
提交commit;
回滾rollback;
例1：
例2：
與python交互
需安裝pip3和pyMySql模塊（針對python3.x版本）
sql語句用法舉例：
1、update main set num=floor(num/10)*10 where num between 20 and 39;
數(shù)據(jù)庫備份與恢復：
創(chuàng)建數(shù)據(jù)庫并設(shè)置字符集和排序規(guī)則：create database 數(shù)據(jù)庫名 CHARACTER SET utf8 COLLATE utf8_general_ci;
備份整個數(shù)據(jù)庫：mysqldump 數(shù)據(jù)庫名> 備份文件名.sql
備份數(shù)據(jù)庫單張表：mysqldump 數(shù)據(jù)庫名 表名 > 備份文件名.sql
從整庫備份文件中提取單張表的創(chuàng)建表語句：sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `表名`/!d;q' 整庫備份文件名.sql > 提取出來的sql文件名.sql
從整庫備份文件中提取單張表的插入數(shù)據(jù)sql語句：grep -i 'INSERT INTO `表名`'? 整庫備份文件名.sql >> 提取出來的sql文件名.sql
還原單張表：進入mysql數(shù)據(jù)庫，執(zhí)行source /home/yxx/提取出來的sql文件名.sql
將表中某一字段值中包含“XX”的部分替換成“AA”:
update proinfo set approved_param=REPLACE(approved_param,'公共事業(yè)','公共科技事業(yè)部')；
將某一字段中指定部分的內(nèi)容（如‘（1111）’）替換為指定內(nèi)容：
update option_code set name=replace(name,substring(name,locate('(',name),(locate(')',name)-locate('(',name)+1)),'');
添加字段：
alter table table1 add transactor varchar(10) DEFAULT NULL;
新建數(shù)據(jù)庫：
create database 數(shù)據(jù)庫名 CHARACTER SET utf8 COLLATE utf8_general_ci;
修改數(shù)據(jù)庫表名稱：
alter table briefing_water_level rename tongque_briefing_water;
