善知教育筆記之資料庫_MySQL

1、課程名稱：MySQL

1.1、 MySQL資料庫學習準備

(1) 什麼是資料庫

資料庫，顧名思義，是存入數據的倉庫。只不過這個倉庫是在電腦存儲設備上的，而且數據是按一定格式存放的。指長期儲存在電腦內的、有組織的、可共用的數據集合。其組織方式可支持對數據的有效存取。

當人們收集了大量的數據後，應該把它們保存起來進入近一步的處理，進一步的抽取有用的信息。當年人們把數據存放在文件櫃中，可現在隨著社會的發展，數據量急劇增長，現在人們就藉助電腦和資料庫技術科學的保存大量的數據，以便能更好的利用這些數據資源。

(2) 資料庫的類型

資料庫包含關係資料庫、面向對象資料庫及新興的XML資料庫等多種，目前應用最廣泛的是關係資料庫，若在關係資料庫基礎上提供部分面向對象資料庫功能的對象關係資料庫。在資料庫技術的早期還曾經流行過層次資料庫與網狀資料庫，但這兩類資料庫目前已經極少使用。

(3) 資料庫管理

資料庫管理(Database Administration)是有關建立、存儲、修改和存取資料庫中信息的技術，是指為保證資料庫系統的正常運行和服務質量，有關人員須進行的技術管理工作。負責這些技術管理工作的個人或集體稱為資料庫管理員(DBA)。資料庫管理的主要內容有：資料庫的建立、資料庫的調整、資料庫的重組、資料庫的重構、資料庫的安全控制、數據的完整性控制和對用戶提供技術支持。

資料庫的建立：資料庫的設計只是提供了數據的類型、邏輯結構、聯繫、約束和存儲結構等有關數據的描述。這些描述稱為數據模式。要建立可運行的資料庫，還需進行下列工作：

(1) 選定資料庫的各種參數，例如最大的數據存儲空間、緩衝決的數量、併發度等。這些參數可以由用戶設置，也可以由系統按預設值設置。

(2) 定義資料庫，利用資料庫管理系統(DBMS)所提供的數據定義語言和命令，定義資料庫名、數據模式、索引等。

(3) 準備和裝入數據，定義資料庫僅僅建立了資料庫的框架，要建成資料庫還必須裝入大量的數據，這是一項浩繁的工作。在數據的準備和錄入過程中，必須在技術和制度上採取措施，保證裝入數據的正確性。電腦系統中原已積累的數據，要充分利用，儘可能轉換成資料庫的數據。

(4) 資料庫產品有

大型資料庫有：Oracle、Sybase、DB2
中型資料庫：SQL server
小型資料庫有：MySQL、Access等。

(5) 關係型資料庫基本概念

關係型資料庫是由多個表(table)和表之間的關聯關係組成的數據的集合，表是一個由若幹行、若幹列組成的二維的關係結構。
表的列稱為欄位(field)
表的行成為記錄(record)

1.2、MySQL資料庫簡介

MySQL是一個真正的多用戶、多線程SQL資料庫伺服器。SQL（結構化查詢語言）是世界上最流行的和標準化的資料庫語言。MySQL是以一個客戶機/伺服器結構的實現，它由一個伺服器守護程式mysqld和很多不同的客戶程式和庫組成。

SQL是一種標準化的語言，它使得存儲、更新和存取信息更容易。例如，你能用SQL語言為一個網站檢索產品信息及存儲顧客信息，同時MySQL也足夠快和靈活以允許你存儲記錄文件和圖像。

MySQL 主要目標是快速、健壯和易用。最初是因為我們需要這樣一個SQL伺服器，它能處理與任何可不昂貴硬體平臺上提供資料庫的廠家在一個數量級上的大型資料庫，但速度更快，MySQL就開發出來。自1996年以來，我們一直都在使用MySQL，其環境有超過 40 個資料庫，包含 10,000個表，其中500多個表超過7百萬行，這大約有100 個吉位元組(GB)的關鍵應用數據。

MySQL建立的基礎是業已用在高要求的生產環境多年的一套實用常式。儘管MySQL仍在開發中，但它已經提供一個豐富和極其有用的功能集。

MySQL官網：http://www.mysql.com

1.3、安裝MySQL

打開下載的mysql安裝文件mysql-5.0.27-win32.zip，雙擊解壓縮，運行“setup.exe”，出現如下界面

mysql安裝嚮導啟動，按“Next”繼續

選擇安裝類型，有“Typical（預設）”、“Complete（完全）”、“Custom（用戶自定義）”三個選項，我們選擇“Custom”，有更多的選項，也方便熟悉安裝過程

在“Developer Components（開發者部分）”上左鍵單擊，選擇“This feature, and all subfeatures, will be installed on local hard drive.”，即“此部分，及下屬子部分內容，全部安裝在本地硬碟上”。在上面的“MySQL Server（mysql伺服器）”、“Client Programs（mysql客戶端程式）”、“Documentation（文檔）”也如此操作，以保證安裝所有文件。點選“Change...”，手動指定安裝目錄。

填上安裝目錄，我的是“F:\Server\MySQL\MySQL Server 5.0”，也建議不要放在與操作系統同一分區，這樣可以防止系統備份還原的時候，數據被清空。按“OK”繼續。

返回剛纔的界面，按“Next”繼續

確認一下先前的設置，如果有誤，按“Back”返回重做。按“Install”開始安裝。

正在安裝中，請稍候，直到出現下麵的界面

這裡是詢問你是否要註冊一個mysql.com的賬號，或是使用已有的賬號登陸mysql.com，一般不需要了，點選“Skip Sign-Up”，按“Next”略過此步驟。

現在軟體安裝完成了，出現上面的界面，這裡有一個很好的功能，mysql配置嚮導，不用向以前一樣，自己手動亂七八糟的配置my.ini了，將 “Configure the Mysql Server now”前面的勾打上，點“Finish”結束軟體的安裝並啟動mysql配置嚮導。

mysql配置嚮導啟動界面，按“Next”繼續

選擇配置方式，“Detailed Configuration（手動精確配置）”、“Standard Configuration（標準配置）”，我們選擇“Detailed Configuration”，方便熟悉配置過程。

選擇伺服器類型，“Developer Machine（開發測試類，mysql占用很少資源）”、“Server Machine（伺服器類型，mysql占用較多資源）”、“Dedicated MySQL Server Machine（專門的資料庫伺服器，mysql占用所有可用資源）”，大家根據自己的類型選擇了，一般選“Server Machine”，不會太少，也不會占滿。

選擇mysql資料庫的大致用途，“Multifunctional Database（通用多功能型，好）”、“Transactional Database Only（伺服器類型，專註於事務處理，一般）”、“Non-Transactional Database Only（非事務處理型，較簡單，主要做一些監控、記數用，對MyISAM數據類型的支持僅限於non-transactional），隨自己的用途而選擇了，我這裡選擇“Transactional Database Only”，按“Next”繼續。

對InnoDB Tablespace進行配置，就是為InnoDB 資料庫文件選擇一個存儲空間，如果修改了，要記住位置，重裝的時候要選擇一樣的地方，否則可能會造成資料庫損壞，當然，對資料庫做個備份就沒問題了，這裡不詳述。我這裡沒有修改，使用用預設位置，直接按“Next”繼續

選擇您的網站的一般mysql訪問量，同時連接的數目，“Decision Support(DSS)/OLAP（20個左右）”、“Online Transaction Processing(OLTP)（500個左右）”、“Manual Setting（手動設置，自己輸一個數）”，我這裡選“Online Transaction Processing(OLTP)”，自己的伺服器，應該夠用了，按“Next”繼續

是否啟用TCP/IP連接，設定埠，如果不啟用，就只能在自己的機器上訪問mysql資料庫了，我這裡啟用，把前面的勾打上，Port Number：3306，在這個頁面上，您還可以選擇“啟用標準模式”（Enable Strict Mode），這樣MySQL就不會允許細小的語法錯誤。如果您還是個新手，我建議您取消標準模式以減少麻煩。但熟悉MySQL以後，儘量使用標準模式，因為它可以降低有害數據進入資料庫的可能性。按“Next”繼續

這個比較重要，就是對mysql預設資料庫語言編碼進行設置，第一個是西文編碼，第二個是多位元組的通用utf8編碼，都不是我們通用的編碼，這裡選擇第三個，然後在Character Set那裡選擇或填入“gbk”，當然也可以用“gb2312”，區別就是gbk的字型檔容量大，包括了gb2312的所有漢字，並且加上了繁體字、和其它亂七八糟的字——使用mysql的時候，在執行數據操作命令之前運行一次“SET NAMES GBK;”（運行一次就行了，GBK可以替換為其它值，視這裡的設置而定），就可以正常的使用漢字（或其它文字）了，否則不能正常顯示漢字。按 “Next”繼續。

選擇是否將mysql安裝為windows服務，還可以指定Service Name（服務標識名稱），是否將mysql的bin目錄加入到Windows PATH（加入後，就可以直接使用bin下的文件，而不用指出目錄名，比如連接，“mysql.exe -uusername -ppassword;”就可以了，不用指出mysql.exe的完整地址，很方便），我這裡全部打上了勾，Service Name不變。按“Next”繼續。

這一步詢問是否要修改預設root用戶（超級管理）的密碼（預設為空），“New root password”如果要修改，就在此填入新密碼（如果是重裝，並且之前已經設置了密碼，在這裡更改密碼可能會出錯，請留空，並將“Modify Security Settings”前面的勾去掉，安裝配置完成後另行修改密碼），“Confirm（再輸一遍）”內再填一次，防止輸錯。

“Enable root access from remote machines（是否允許root用戶在其它的機器上登陸，如果要安全，就不要勾上，如果要方便，就勾上它）”。

最後“Create An Anonymous Account（新建一個匿名用戶，匿名用戶可以連接資料庫，不能操作數據，包括查詢）”，一般就不用勾了，設置完畢，按“Next”繼續。

確認設置無誤，如果有誤，按“Back”返回檢查。按“Execute”使設置生效。

設置完畢，按“Finish”結束mysql的安裝與配置——這裡有一個比較常見的錯誤，就是不能“Start service”，一般出現在以前有安裝mysql的伺服器上，解決的辦法，先保證以前安裝的mysql伺服器徹底卸載掉了；
不行的話，檢查是否按上面一步所說，之前的密碼是否有修改，照上面的操作；如果依然不行，將mysql安裝目錄下的data文件夾備份，然後刪除，在安裝完成後，將安裝生成的 data文件夾刪除，備份的data文件夾移回來，再重啟mysql服務就可以了，這種情況下，可能需要將資料庫檢查一下，然後修複一次，防止數據出錯。

1.4、數據類型

MySQL支持多種列類型：數值類型、日期/時間類型和字元串(字元)類型。
長度以位元組為單位

數據類型更詳細的講解請參考MYSQL幫助文檔

1.5、SQL

SQL全稱是：結構化查詢語言(Structured Query Language)。

SQL語言包含4個部分

(1) 數據定義語言（Data Definition Language--DDL）：如CREATE, DROP,ALTER等語句

(2) 數據操縱語言（Data Manipulation Language- -DML）：INSERT, UPDATE, DELETE語句

(3) 數據查詢語言（Data Retrieval Language --DRL）：SELECT語句

(4) 事務控制語言（Transaction Control Language--TCL）：如COMMIT, ROLLBACK等語句

1.5.1、數據定義語言（DDL）

 創建資料庫（CREATE DATABASE語句）
CREATE DATABASE mydatabase;

show databases; //查看當前伺服器上存在的資料庫(當前登錄用戶可見的)
use mydatabase; //訪問資料庫

 創建表（CREATE TABLE語句）
CREATE TABLE student(
sid int(11) primary key auto_increment,name varchar(20),gender char(1),age int(2),birth date
);

desc student; //查看表結構

//創建表

CREATE TABLE `employee` (
  `eid` int(11) NOT NULL default '0',
  `name` varchar(11) NOT NULL,
  `dept` varchar(11) NOT NULL,
  `job` varchar(11) NOT NULL,
  `gender` varchar(5) NOT NULL,
  PRIMARY KEY  (`eid`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

重要概念
主鍵：用來唯一代表一條記錄的欄位（主鍵值必須是唯一）

 刪除表（DROP TABLE語句）

DROP TABLE student;

//drop table 語句會刪除該的所有記錄及表結構

 修改表結構（ALTER TABLE語句）

alter table test add  column name varchar(10); --添加表列

alter table test rename test1; --修改表名

alter table test drop  column name; --刪除表列

alter table test modify address char(10) --修改表列類型
alter table test change address address  char(40) --修改表列類型

alter table test change  column address address1 varchar(30)--修改表列名

1.5.2、數據操縱語言（DML）

 添加數據（INSERT INTO…語句）

INSERT INTO student(name,gender,birth) values(‘Tom’,’男’,’1985-2-5’);

 修改數據（UPDATE … SET語句）

UPDATE student SET name=’LILY’,gender=’女’,birth=’1988-1-1’ where id=1;

 刪除數據（DELETE FROM…語句）

DELETE FROM student; --刪除所有記錄
DELETE FROM student where id=1; --刪除ID為1的記錄

1.5.3、數據查詢語言（DRL）

查詢數據（SELECT … FROM …語句）

SELECT * FROM student; --查詢所有學生信息
select 1+1 from dual;-- 在沒有表被引用的情況下，允許您指定DUAL作為一個假的表名
SELECT * FROM student where id=1; --查詢ID為1的學生信息
SELECT * from employee where name is null;--查詢姓名為空的學生信息
SELECT name,gender FROM student where id=1; 
SELECT s.name,s.gender FROM student s where id=1; --使用別名
--查詢ID為1的學生的姓名和性別
SELECT name FROM student where gender=’女’ and birth=’1988-1-1’ ;
–查詢性別為“女”，並且生日為“1988-1-1”的學生信息
SELECT * FROM student where id>5; --查詢ID大於5的學生信息
SELECT * FROM student where gender=’男’ or id<5;
--查詢性別為“男”或者ID小於5的學生信息
SELECT * FROM student where name LIKE ‘%T’;
--查詢姓名的最後一個字元為“T”的學生信息
SELECT * FROM student where name LIKE ‘T%’;
--查詢姓名以“T”開頭的學生信息
SELECT * FROM student where name LIKE ‘%T%’;
--查詢姓名中包含“T”的學生信息
SELECT * FROM student ORDER BY birth desc;
--查詢所有學生信息，並按生日降序排序（預設為升序：ASC）
select * from students order by age desc,birthdate;
--多個排序條件：當第一個條件相同時，以第二個條件排序
SELECT gender,COUNT(gender) 人數  FROM student GROUP BY gender;
--按性別分組查詢男女學生的人數
HAVING條件語句一個HAVING子句(條件查詢)必須位於GROUP BY子句之後，並位於ORDER BY子句之前。
SELECT gender,COUNT(gender) AS人數 FROM employee GROUP BY gender having gender='女'
--按性別分組，查詢出女生人數的總數
select dept,count(dept) from employee group by dept having dept='IT';
select count(*) as 總數 from students; --查詢表的總記錄數
select * from student limit 0,3;  --查詢學生記錄的前三條（從0位置開始找出3條）

1.5.4、數據控制語言（TCL）

1.5.4.1、什麼是事務

事務(Transaction)是訪問並可能更新資料庫中各種數據項的一個程式執行單元(unit)。事務通常由高級資料庫操縱語言或編程語言（如SQL，C++或Java）書寫的用戶程式的執行所引起，並用形如begin transaction和end transaction語句（或函數調用）來界定。事務由事務開始(begin transaction)和事務結束(end transaction)之間執行的全體操作組成。

例如：在關係資料庫中，一個事務可以是一條SQL語句，一組SQL語句或整個程式。

事務是恢復和併發控制的基本單位。
事務應該具有4個屬性：原子性、一致性、隔離性、持續性。這四個屬性通常稱為ACID特性。

 原子性（atomicity）
一個事務是一個不可分割的工作單位，事務中包括的諸操作要麼都做，要麼都不做。

 一致性（consistency）
事務必須是使資料庫從一個一致性狀態變到另一個一致性狀態。一致性與原子性是密切相關的。

 隔離性（isolation）
一個事務的執行不能被其他事務干擾。即一個事務內部的操作及使用的數據對併發的其他事務是隔離的，併發執行的各個事務之間不能互相干擾。

 持久性（durability）
持續性也稱永久性（permanence），指一個事務一旦提交，它對資料庫中數據的改變就應該是永久性的。接下來的其他操作或故障不應該對其有任何影響。

小結：
事務(Transaction)，也就是要麼成功，要麼失敗，並恢複原狀。

1.5.4.2、事務操作

 設置預設事務提交方式
````
set autocommit = false –設置事務提交方式為“手動提交”
set autocommit = true –設置事務提交方式為“自動提交”

事務就是對資料庫的多步操作，要麼一起成功，要麼一起失敗
set autocommit = false;
update student set name=’vince’ where id=1;--更新數據
insert into student(name,gender,birth) values(‘yoyo’,’女’,’1981-03-20’);--插入數據
commit;--手動提交事務

delete from student where id=1;--刪除數據
insert into student(name,gender,birth) values(‘tony,’男’,’1985-02-26’);--插入數據
rollback;--回滾事務

delete from student where id=1;--刪除數據
savepoint point1; --保存還原點
delete from student where id=1;--刪除數據
savepoint point2; --保存還原點
rollback to point2; --回滾到point2還原點
commit; --提交事務

####1.6、函數
######1.6.1、GROUP BY（聚合）函數
   AVG ([DISTINCT] expr)
返回expr 的平均值。 DISTINCT 選項可用於返回 expr的不同值的平均值。

SELECT gender, AVG(age) FROM student
GROUP BY gender;

   COUNT(expr)
返回SELECT語句檢索到的行中非NULL值的數目。

SELECT COUNT(*) FROM student; --返回檢索行的數目，不論其是否包含 NULL值

SELECT COUNT(name) FROM student; --返回SELECT語句檢索到的行中非NULL值的數目

   MIN ([DISTINCT] expr), MAX ([DISTINCT] expr)
返回expr 的最小值和最大值

SELECT MIN(age),MAX(age) FROM student;

   SUM ([DISTINCT] expr)
返回expr 的總數

SELECT SUM(age) FROM student;

#####1.6.2、控制流程函數
   CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END
如果沒有匹配的結果值，則返回結果為ELSE後的結果，如果沒有ELSE 部分，則返回值為 NULL。

SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;

   IF(expr1,expr2,expr3)
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，則 IF()的返回值為expr2; 否則返回值則為 expr3。

SELECT IF(1<2,'yes ','no');

   IFNULL(expr1,expr2)
假如expr1 不為 NULL，則 IFNULL() 的返回值為 expr1; 否則其返回值為 expr2

SELECT IFNULL(1,0);

   NULLIF(expr1,expr2)
如果expr1 = expr2  成立，那麼返回值為NULL，否則返回值為 expr1

SELECT NULLIF(1,1);

######1.6.3、字元串函數
   ASCII (str)
返回值為字元串str 的最左字元的數值。假如str為空字元串，則返回值為 0 。假如str 為NULL，則返回值為 NULL。 ASCII()用於帶有從 0到255的數值的字元。

SELECT ASCII('dx');

   BIN (N)
返回值為N的二進位值的字元串表示

SELECT BIN(15);

   BIT_LENGTH (str)
返回值為二進位的字元串str 長度

SELECT BIT_LENGTH('text');

   CHAR_LENGTH(str)
返回值為字元串str 的長度，長度的單位為字元

SELECT CHAR_LENGTH( 'vince');

   FORMAT(X,D)
將數字X 的格式寫為'#,###,###.##',以四捨五入的方式保留小數點後 D 位，並將結果以字元串的形式返回。若D為0, 則返回結果不帶有小數點，或不含小數部分。 

SELECT FORMAT(12332.123456, 4);

   INSERT (str,pos,len,newstr)
返回字元串 str, 其子字元串起始於 pos 位置和長期被字元串 newstr取代的len 字元。  如果pos 超過字元串長度，則返回值為原始字元串。 假如len的長度大於其它字元串的長度，則從位置pos開始替換。若任何一個參數為null，則返回值為NULL。

SELECT INSERT('Quadratic', 3, 4, 'What');
SELECT INSERT('Quadratic', 3, 100, 'What');

   INSTR(str,substr)
返回字元串 str 中子字元串的第一個出現位置

SELECT INSTR('foobarbar', 'bar');

   LEFT(str,len) 
返回從字元串str 開始的len 最左字元。 

SELECT LEFT('foobarbar', 5);

   LENGTH(str) 
返回值為字元串str 的長度，單位為位元組。一個多位元組字元算作多位元組。這意味著對於一個包含5個2位元組字元的字元串， LENGTH() 的返回值為 10, 而 CHAR_LENGTH()的返回值則為5。

SELECT LENGTH('text');

   LTRIM(str) 
返回字元串 str ，其引導空格字元被刪除。

SELECT LTRIM(' barbar');

   TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str) 
返回字元串 str ， 其中所有remstr 首碼和/或尾碼都已被刪除。若分類符BOTH、LEADIN或TRAILING中沒有一個是給定的,則假設為BOTH 。 remstr 為可選項，在未指定情況下，可刪除空格。

SELECT TRIM(' bar '); --去空格
SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); --去左邊的x
SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); --去左右兩邊的x
SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); --去右邊的xyz

   STRCMP(expr1,expr2) 
若所有的字元串均相同，則返回0，若根據當前分類次序，第一個參數小於第二個，則返回 -1，其它情況返回1。

SELECT STRCMP('text', 'text2'); --返回-1
SELECT STRCMP('text2', 'text'); --返回1
SELECT STRCMP('text', 'text'); --返回0

   CONCAT (str1,str2,...) 
返回結果為連接參數產生的字元串。如有任何一個參數為NULL ，則返回值為 NULL。或許有一個或多個參數。 如果所有參數均為非二進位字元串，則結果為非二進位字元串。 如果自變數中含有任一二進位字元串，則結果為一個二進位字元串。一個數字參數被轉化為與之相等的二進位字元串格式；若要避免這種情況，可使用顯式類型 cast, 例如： 

SELECT CONCAT(CAST(int_col AS CHAR), char_col)

mysql> SELECT CONCAT('My', 'S', 'QL');

1.6.4、日期和時間函數
   DAYOFWEEK(date)
返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。

mysql> select DAYOFWEEK('1998-02-03');

   WEEKDAY(date)
返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。

mysql> select WEEKDAY('1997-10-04 22:23:00');
mysql> select WEEKDAY('1997-11-05');

   DAYOFMONTH (date)
返回date的月份中日期，在1到31範圍內。 

mysql> select DAYOFMONTH ('1998-02-03');

   DAYOFYEAR(date)
返回date在一年中的日數, 在1到366範圍內。 

mysql> select DAYOFYEAR('1998-02-03');

   MONTH(date)
返回date的月份，範圍1到12。 

mysql> select MONTH('1998-02-03');

   DAYNAME(date) 
返回date的星期名字。

mysql> select DAYNAME("1998-02-05");

   MONTHNAME(date) 
返回date的月份名字。

mysql> select MONTHNAME("1998-02-05");

   QUARTER(date) 
返回date一年中的季度，範圍1到4。  

mysql> select QUARTER('98-04-01');

   WEEK(date) 
對於星期天是一周的第一天的地方，有一個單個參數，返回date的周數，範圍在0到52。2個參數形式WEEK()允許你指定星期是否開始於星期天或星期一。如果第二個參數是0，星期從星期天開始，如果第二個參數是1，從星期一開始。

mysql> select WEEK('1998-02-20');
mysql> select WEEK('1998-02-20',0);
mysql> select WEEK('1998-02-20',1);

   YEAR(date) 
返回date的年份，範圍在1000到9999。 

mysql> select YEAR('98-02-03');

   HOUR(time) 
返回time的小時，範圍是0到23。 

mysql> select HOUR('10:05:03');

   MINUTE(time) 
返回time的分鐘，範圍是0到59。

mysql> select MINUTE('98-02-03 10:05:03');

   SECOND(time) 
返回time的秒數，範圍是0到59。

mysql> select SECOND('10:05:03');

   PERIOD_ADD(P,N) 
增加N個月到階段P（以格式YYMM或YYYYMM)。以格式YYYYMM返回值。註意階段參數P不是日期值。

mysql> select PERIOD_ADD(9801,2);

   PERIOD_DIFF(P1,P2) 
返回在時期P1和P2之間月數，P1和P2應該以格式YYMM或YYYYMM。註意，時期參數P1和P2不是日期值。

mysql> select PERIOD_DIFF(9802,199703);

   ADDDATE(expr,days)
expr是指定加到開始日期的間隔值一個表達式，expr是一個字元串；它可以以一個“-”開始表示負間隔。type是一個關鍵詞，指明表達式應該如何被解釋。（type關鍵詞用法請參考幫助文檔）
若 days 參數只是整數值，則將其作為天數值添加至 expr。

mysql> SELECT ADDDATE('1998-01-02', 31);
SELECT DATE_ADD('1997-12-31 23:59:59',INTERVAL 1 SECOND);

   SUBDATE(date,INTERVAL expr type) 
date是一個指定開始日期的DATETIME或DATE值，expr是指定從開始日期減去的間隔值一個表達式，expr是一個字元串；它可以以一個“-”開始表示負間隔。type是一個關鍵詞，指明表達式應該如何被解釋。（type關鍵詞用法請參考幫助文檔）

mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);

   ADDTIME(expr,expr2)
將 expr2添加至expr 然後返回結果。 expr 是一個時間或時間日期表達式，而expr2 是一個時間表達式。

mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');

   DATE(expr)
提取日期或時間日期表達式expr中的日期部分。

mysql> SELECT DATE('2003-12-31 01:02:03');

   TO_DAYS(date)
給出一個日期date，返回一個天數(從0年的天數)。 

mysql> select TO_DAYS(950501);
mysql> select TO_DAYS('1997-10-07');

   FROM_DAYS(N) 
給出一個天數N，返回一個DATE值。

mysql> select FROM_DAYS(729669);

   CURDATE()
以'YYYY-MM-DD'或YYYYMMDD格式返回今天日期值，取決於函數是在一個字元串還是數字上下文被使用。

mysql> select CURDATE(); -- YYYY-MM-DD 格式
mysql> select CURDATE() + 0; --YYYYMMDD 格式

   CURTIME() 
以'HH:MM:SS'或HHMMSS格式返回當前時間值，取決於函數是在一個字元串還是在數字的上下文被使用。 

mysql> select CURTIME(); --HH:MM:SS 格式
mysql> select CURTIME() + 0; --HHMMSS 格式

   NOW()
以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回當前的日期和時間，取決於函數是在一個字元串還是在數字的上下文被使用。

mysql> select NOW(); -- YYYY-MM-DD HH:MM:SS 格式
mysql> select NOW() + 0; --YYYYMMDDHHMMSS 格式

   SEC_TO_TIME(seconds) 
返回seconds參數，變換成小時、分鐘和秒，值以'HH:MM:SS'或HHMMSS格式化，取決於函數是在一個字元串還是在數字上下文中被使用。 

mysql> select SEC_TO_TIME(2378); -- HH:MM:SS格式
mysql> select SEC_TO_TIME(2378) + 0; --HHMMSS 格式

   TIME_TO_SEC(time) 
返回time參數，轉換成秒。

mysql> select TIME_TO_SEC('22:23:00');
mysql> select TIME_TO_SEC('00:39:38');
```
 DATE_FORMAT(date,format)
根據format 字元串安排date 值的格式。

以下說明符可用在 format 字元串中：
說明符 說明
%a 工作日的縮寫名稱 (Sun..Sat)
%b 月份的縮寫名稱 (Jan..Dec)
%c 月份，數字形式(0..12)
%D 帶有英語尾碼的該月日期 (0th, 1st, 2nd, 3rd, ...)
%d 該月日期, 數字形式 (00..31)
%e 該月日期, 數字形式(0..31)
%f 微秒 (000000..999999)
%H 小時(00..23)
%h 小時(01..12)
%I 小時 (01..12)
%i 分鐘,數字形式 (00..59)
%j 一年中的天數 (001..366)
%k 小時 (0..23)
%l 小時 (1..12)
%M 月份名稱 (January..December)
%m 月份, 數字形式 (00..12)
%p 上午（AM）或下午（ PM）
%r 時間 , 12小時制 (小時hh:分鐘mm:秒數ss 後加 AM或PM)
%S 秒 (00..59)
%s 秒 (00..59)
%T 時間 , 24小時制 (小時hh:分鐘mm:秒數ss)
%U 周 (00..53), 其中周日為每周的第一天
%u 周 (00..53), 其中周一為每周的第一天
%V 周 (01..53), 其中周日為每周的第一天 ; 和 %X同時使用
%v 周 (01..53), 其中周一為每周的第一天 ; 和 %x同時使用
%W 工作日名稱 (周日..周六)
%w 一周中的每日 (0=周日..6=周六)
%X 該周的年份，其中周日為每周的第一天, 數字形式,4位數;和%V同時使用
%x 該周的年份，其中周一為每周的第一天, 數字形式,4位數;和%v同時使用
%Y 年份, 數字形式,4位數
%y 年份, 數字形式 (2位數)
%% ‘%’文字字元

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00','%D %y %a %d %m %b %j');

1.7、關聯查詢

1.7.1、多表連接查詢

使用單個SELECT語句從多個表中取出相關的數據，通過多表之間的關係，構建相關數據的查詢。多表連接通常是建立在相互關係的父子(主從)表上的。

SQL1999標準中多表連接的語法：
SELECT... FROM join_table
　　JOIN_TYPE join_table
　　ON join_condition
WHERE where_condition

join_table：參與連接的表
JOIN_TYPE：連接類型：內連接、外連接、交叉連接、自連接
join_condition：連接條件
where_condition：where過濾條件

示例表
employee（員工）表

dept（部門）表

select e.name,e.salary,e.comm,d.dname from emp e join dept d on
e.deptid=d.did where e.comm is null;
--查詢員工姓名，工資，獎金，部門姓稱，並且員工獎金為null

select e.name,e.salary,e.comm,d.dname from emp e join dept d on
e.deptid=d.did where e.comm is not null;
--查詢員工姓名，工資，獎金，部門姓稱，並且員工獎金不為null

select e.name,e.salary,ifnull(e.comm,0),d.dname from emp e join dept d on
e.deptid=d.did where e.comm>0;
--查詢員工姓名，工資，獎金，部門姓稱，並且員工獎金大於0

select e.name,d.dname from emp e,dept d where d.dname='技術部'
and e.deptid=d.did;
--查詢部門名稱為“技術部”的員工姓名和部門名稱

1.7.2、外鍵

在MySQL伺服器3.23.44和更高版本中，InnoDB存儲引擎支持對外鍵約束的檢查功能，這些約束包括CASCADE、ON DELETE和ON UPDATE。

外鍵增強為資料庫開發人員提供了多項益處：

 假定關聯設計恰當，外鍵約束使得程式員更難將不一致性引入資料庫。
 資料庫伺服器具有集中式約束檢查功能，因而沒有必要在應用程式一側執行這類檢查。這樣，就消除了不同應用程式使用不同方式檢查約束的可能性。
 使用級聯更新和刪除，簡化了應用程式代碼。
 設計恰當的外鍵有助於以文檔方式記錄表間的關係。

當兩個表有關聯關係時，例如部門表中的每一個部門對應員工表的多個員工，那麼可以使用外鍵約束來約束兩個表的關係（則部門表為主表，員工表為從表），通過在員工表中定義一個部門表的主鍵欄位(dept_id)，此時，員工表中的dept_id就叫做部門表的外鍵。

添加外鍵語法：
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}][ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

外鍵定義服從下列情況：
 所有tables必須是InnoDB型，它們不能是臨時表。
 在引用表中，必須有一個索引，外鍵列以同樣的順序被列在其中作為第一列。這樣一個索引如果不存在，它必須在引用表裡被自動創建。
 在引用表中，必須有一個索引，被引用的列以同樣的順序被列在其中作為第一列。
 不支持對外鍵列的索引首碼。這樣的後果之一是BLOB和TEXT列不被包括在一個外鍵中，這是因為對這些列的索引必須總是包含一個首碼長度。

示例：
ALTER TABLE emp ADD FOREIGN KEY(did) REFERENCES dept(did);
--在員工表的did欄位上添加外鍵約束

通過查看表的定義找出外鍵的名稱：
show create table emp;

刪除外鍵
alter table emp drop foreign key 外鍵名;

1.8、資料庫設計

當資料庫比較複雜時我們需要設計資料庫，良好的資料庫設計，節省數據的存儲空間
能夠保證數據的完整性，方便進行資料庫應用系統的開發。

1.8.1、軟體項目開發周期

(1) 需求分析階段：分析客戶的業務和數據處理需求;

(2) 概要設計階段：設計資料庫的E-R模型圖(概念模型)，確認需求信息的正確和完整;

(3) 詳細設計階段：將E-R圖轉換為多張表(物理模型)，進行邏輯設計，並應用資料庫設計的三大範式進行審核;

(4) 代碼編寫階段：選擇具體資料庫進行物理實現，並編寫代碼實現前端應用;

(5) 軟體測試階段：……

(6) 安裝部署：……

既然我們建庫前需要預先設計資料庫，那到底如何設計呢？
我們一起從資料庫設計的角度，看看項目開發周期的不同階段，資料庫設計的具體工作有哪些。

(1) 重點講解與資料庫設計相關的各個階段。

(2) 強調需求分析階段：分析客戶的業務需求。

(3) 強調概要設計階段：重點是分析資料庫E-R圖（類似建築方面的施工圖），用於項目團隊之間以及團隊和客戶之間的溝通，客戶根據圖紙提出修改意見，項目組修改後再與客戶反覆溝通，直到客戶確認。 E-R的好處主要是簡潔直觀。

(4) 強調詳細設計階段：重點是實現，需要把E-R圖轉化為具體的多張表。但是10個人有10種設計方案，所以我們需要評估、審核並優化，審核時就需要一些設計規則進行審核，這些規則就是三大範式。

(5) 在代碼編寫階段：我們再根據項目性能要求、項目經費、技術實現難度等選擇是MySQL還是Oracle等進行物理實現：建庫、建表、加約束等。

概念數據模型描述的是獨立於資料庫管理系統(DBMS)的實體定義和實體關係定義。
物理數據模型是在概念數據模型的基礎上針對目標資料庫管理系統的具體化。

概念數據模型(CDM)
CDM表現資料庫的全部邏輯的結構,與任何的軟體或數據儲藏結構無關。一個概念模型經常包括在物理資料庫中仍然不實現的數據對象。它給運行計劃或業務活動的數據一個正式表現方式。不考慮物理實現細節，只考慮實體之間的關係。

物理數據模型 (PDM)
PDM敘述資料庫的物理實現。主要目的是把CDM中建立的現實世界模型生成特定的DBMS腳本，產生資料庫中保存信息的儲存結構，保證數據在資料庫中的完整性和一致性。

1.8.2、資料庫設計步驟

(1) step1：收集信息
與該系統有關人員進行交流、坐談，充分理解資料庫需要完成的任務

(2) Step2：標識對象（實體－Entity）
標識資料庫要管理的關鍵對象或實體
實體的概念：相當於Java中講解的對象，現實中實實在在存在的事物都是實體，如汽車、房子、人等。

(3) Step3：標識每個實體的屬性（Attribute）
類似Java中類的屬性

(4) Step4：標識對象之間的關係（Relationship）
世界萬物都是聯繫的，一個系統中的實體間也是如此，所以我們還需要標出實體間的關係。

1.8.3、繪製E-R圖

E-R（Entity－Relationship）實體關係圖

繪製E-R圖的工具：
微軟的Viso
Sybase公司的PowerDesigner
不同的工具表示方法略有不同

演示用PowerDesigner繪製 PDM圖（物理模型圖）
把PDM轉成資料庫表

1.8.4、資料庫的規範化之三大範式

僅有好的DBMS並不足以避免數據冗餘，必須在資料庫的設計中創建好的表結構。
Dr E.F.codd 最初定義了資料庫規範化的三個級別，範式是具有最小冗餘的表結構。
這些範式是：
第一範式(1st NF －First Normal Fromate)
第二範式(2nd NF－Second Normal Fromate)
第三範式(3rd NF－ Third Normal Fromate)

(1) 第一範式：
如果每列都是不可再分的最小數據單元（也稱為最小的原子單元），則滿足第一範式（1NF）。
第一範式的目標是確保每列的原子性。簡而言之，第一範式就是無重覆的列。

在任何一個關係資料庫中，第一範式（1NF）是對關係模式的基本要求，不滿足第一範式（1NF）的資料庫就不是關係資料庫。

第一範式（1NF）的目標：確保每列的原子性。

(2) 第二範式：
如果一個關係滿足1NF，並且除了主鍵以外的其他列，都依賴於該主鍵，則滿足第二範式（2NF）第二範式要求每個表只描述一件事情。
簡而言之，第二範式就是非主屬性完全依賴於主關鍵字。

第二範式（2NF）的目標：確保表中的每列，都和主鍵相關。

(3) 第三範式：
如果一個關係滿足2NF，並且非主鍵列都不傳遞依賴於主鍵列，則滿足第三範式（3NF）
第三範式要求一個表中不能包含在其它表中已定義的非主鍵列
簡而言之，第三範式就是屬性不依賴於其它非主屬性。

第三範式（3NF）的目標：確保每列都和主鍵列直接相關，而不是間接相關。

1.8.5、範式應用示例

來逐步分析設計一個論壇的資料庫。
有如下信息：
用戶：用戶名，email，主頁，電話，聯繫地址
帖子：發帖標題，發帖內容，回覆標題，回覆內容

第一次：將資料庫設計為僅有一張表：

這個資料庫表符合第一範式，但是沒有任何一組候選關鍵字能決定資料庫表的整行，唯一的關鍵欄位用戶名也不能完全決定整個元組。
我們需要增加"主帖ID"、"回覆ID"欄位，即將表修改為：

這樣的設計不符合第二範式，因為存在如下決定關係：
(用戶名) → (email,主頁,電話,聯繫地址)
(主帖ID) → (發帖標題,發帖內容)
(回覆ID) → (回覆標題,回覆內容)
即非主鍵列依賴於候選關鍵列

我們將資料庫表分解為（帶下劃線的為主鍵列）：
用戶信息：用戶名，email，主頁，電話，聯繫地址
帖子信息：主帖ID，標題，內容
回覆信息：回覆ID，標題，內容
發貼：用戶名，主帖ID
回覆：主帖ID，回覆ID

但是這樣的設計是不是最好的呢？

第4項"發帖"中的"用戶名"和"主帖ID"之間是1：N的關係，因此可以把"發帖"合併到第2項的"帖子信息"中；
第5項"回覆"中的"主帖ID"和"回覆ID"之間也是1：N的關係，因此可以把"回覆"合併到第3項的"回覆信息"中。這樣可以一定量地減少數據冗餘，新的設計為：
用戶信息：用戶名，email，主頁，電話，聯繫地址
帖子信息：用戶名，主帖ID，標題，內容
回覆信息：主帖ID，回覆ID，標題，內容

1.8.6、資料庫設計總結

滿足範式要求的資料庫設計是結構清晰的，同時可避免數據冗餘和操作異常。
沒有冗餘的資料庫未必是最好的資料庫，有時為了提高運行效率，就必須降低範式標準，適當保留冗餘數據。
具體做法是：
在概念數據模型設計時遵守第三範式。
降低範式標準的工作放到物理數據模型設計時考慮。降低範式就是增加欄位，允許冗餘。