mysql資料型別：
MySQL支援多種型別，大致可以分為三類：數值、日期/時間和字串(字元)型別。
- 數值型別
MySQL支援所有標準SQL數值資料型別。
這些型別包括嚴格數值資料型別(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似數值資料型別(FLOAT、REAL和DOUBLE PRECISION)。
關鍵字INT是INTEGER的同義詞，關鍵字DEC是DECIMAL的同義詞。
BIT資料型別儲存位欄位值，並且支援MyISAM、MEMORY、InnoDB和BDB表。
作為SQL標準的擴充套件，MySQL也支援整數型別TINYINT、MEDIUMINT和BIGINT。下面的表顯示了需要的每個整數型別的儲存和範圍。
| 型別 | 大小 | 範圍（有符號-預設） | 範圍（無符號） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 位元組 | (-128，127) | (0，255) | 小整數值 |
| SMALLINT | 2 位元組 | (-32 768，32 767) | (0，65 535) | 大整數值 |
| MEDIUMINT | 3 位元組 | (-8 388 608，8 388 607) | (0，16 777 215) | 大整數值 |
| INT或INTEGER | 4 位元組 | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整數值 |
| BIGINT | 8 位元組 | (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 極大整數值 |
| FLOAT | 4 位元組 | (-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 單精度 浮點數值 |
| DOUBLE | 8 位元組 | (1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 雙精度 浮點數值 |
| DECIMAL | 對DECIMAL(M,D) ，如果M>D，為M+2否則為D+2 | 依賴於M和D的值 | 依賴於M和D的值 |
小數值
|
注：M：資料的總長度（不包括小數點） D：小數位
例如：decimal（5,2） 123.45
存入資料的時候按四捨五入計算
- 日期和時間型別
表示時間值的日期和時間型別為DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每個時間型別有一個有效值範圍和一個"零"值，當指定不合法的MySQL不能表示的值時使用"零"值。
TIMESTAMP型別有專有的自動更新特性，將在後面描述。
| 型別 | 大小 (位元組) | 範圍 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 時間值或持續時間 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和時間值 |
| TIMESTAMP | 8 | 1970-01-01 00:00:00/2037 年某時 | YYYYMMDD HHMMSS | 混合日期和時間值，時間戳 |
- 字串型別
字串型別指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。該節描述了這些型別如何工作以及如何在查詢中使用這些型別。
| 型別 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255位元組 | 定長字串 |
| VARCHAR | 0-65535 位元組 | 變長字串 |
| TINYBLOB | 0-255位元組 | 不超過 255 個字元的二進位制字串 |
| TINYTEXT | 0-255位元組 | 短文字字串 |
| BLOB | 0-65 535位元組 | 二進位制形式的長文字資料 |
| TEXT | 0-65 535位元組 | 長文字資料 |
| MEDIUMBLOB | 0-16 777 215位元組 | 二進位制形式的中等長度文字資料 |
| MEDIUMTEXT | 0-16 777 215位元組 | 中等長度文字資料 |
| LONGBLOB | 0-4 294 967 295位元組 | 二進位制形式的極大文字資料 |
| LONGTEXT | 0-4 294 967 295位元組 | 極大文字資料 |
CHAR和VARCHAR型別類似，但它們儲存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。在儲存或檢索過程中不進行大小寫轉換。
BINARY和VARBINARY類類似於CHAR和VARCHAR，不同的是它們包含二進位制字串而不要非二進位制字串。也就是說，它們包含位元組字串而不是字元字串。這說明它們沒有字符集，並且排序和比較基於列值位元組的數值值。
BLOB是一個二進位制大物件，可以容納可變數量的資料。有4種BLOB型別：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它們只是可容納值的最大長度不同。
有4種TEXT型別：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。這些對應4種BLOB型別，有相同的最大長度和儲存需求。
mysql命令：
資料庫操作：
1、顯示所有資料庫：Show databases；
2、建立資料庫：Create database 資料庫名；
3、刪除資料庫：Drop database 資料庫名；
表操作：
CREATE TABLE 表名( 屬性名 資料型別 [完整性約束條件],
屬性名 資料型別 [完整性約束條件],
.
.
屬性名 資料表格 [完整性約束條件]
);
|
約束條件
|
說明
|
|
PRIMARY KEY
|
標識該屬性為該表的主鍵，可以唯一的標識對應的記錄
|
|
FOREIGN KEY
|
標識該屬性為該表的外來鍵，與某表的主鍵關聯
|
|
NOT NULL
|
標識該屬性不能為空
|
|
UNIQUE
|
標識該屬性的值是唯一的
|
|
AUTO_INCREMENT
|
標識該屬性的值自動增加
|
|
DEFAULT
|
為該屬性設定預設值
|
注： UNIQUE 和 PRIMARY KEY 約束均為列或列集合提供了唯一性的保證。簡單來說：unique=primary key+not null
區別：1、primary key只能有一個，unique可以有多個同時存在；
2、primary key不能為空，而unique可以為空；
primary key一般在設計邏輯中用作記錄標識，而unique只是用來保證唯一性
UNIQUE (bookId)//新建表時將bookId設為唯一
//新增唯一約束
ALTER TABLE Persons
ADD UNIQUE (Id_P)
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)//設定Id_P和LastName都為唯一併命名為uc_PersonID//刪除唯一約束
ALTER TABLE Persons
DROP INDEX uc_PersonID
建表時建立外來鍵
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)//設定本表Id_p欄位和Persons表的Id_p主鍵關聯檢視錶結構：
1，檢視基本表結構： DESCRIBE(或DESC） 表名；//返回表欄位及其詳細資訊表格
2，檢視錶詳細結構： SHOW CREATE TABLE 表名；//返回結果是建立表的sql
刪除/清空 表：
1、 drop table 表名； //徹底刪除表格，刪除欄位資料，釋放記憶體空間
2、 truncate 表名； //刪除表格資料，不刪除欄位，釋放記憶體空間
3、 delete from 表名；或delete * from 表名;// 刪除內容不刪除定義，不釋放空間,系統一行一行地刪,效率較truncate低
truncate和delete對比:
1、truncate 將重新設定高水平線和所有的索引。在對整個表和索引進行完全瀏覽時，經過 truncate 操作後的表比Delete操作後的表要快得多。 當表被清空後表和表的索引將重新設定成初始大小，而delete則不能。
2、truncate 是隱式提交，不能觸發任何Delete觸發器，不能通過rollback命令撤回，而delete可以，因為delete 語句每次刪除一行，都在事務日誌中為所刪除的每行記錄一項 ；
3、truncate table 在功能上與不帶 WHERE 子句的 delete語句相同：二者均刪除表中的全部行。但 truncate 比 delete速度快，且使用的系統和事務日誌資源少，因為delete是一行一行的刪除。
注意：truncate不能清空父表
修改表：
1，修改表名 ALTER TABLE 舊錶名 RENMAE 新表名 ；
2，修改欄位 ALTER TABLE 表名 CHANGE 舊屬性名 新屬性名 新資料型別
3，增加欄位 ALTER TABLE 表名 ADD 屬性名 資料型別 [完整性約束條件] [FIRST | AFTER 屬性名 2]
4，刪除欄位 ALTER TABLE 表名 DROP 屬性名
插入資料
- 給表的所有欄位插入資料
格式：INSERT INTO 表名 VALUES(值 1，值 2，值 3，...，值 n)；
- 給表的指定欄位插入資料
格式：INSERT INTO 表名(屬性 1，屬性 2，...，屬性 n) VALUES(值 1，值 2，值 3，...，值 n)；
- 同時插入多條記錄
INSERT INTO 表名 [(屬性列表)] VALUES(取值列表 1)，(取值列表 2) ...， (取值列表 n)；
更新資料
UPDATE 表名 SET 屬性名 1=取值 1，屬性名 2=取值 2， ...， 屬性名 n=取值 n WHERE 條件表示式；
刪除資料
DELETE FROM 表名 [WHERE 條件表示式]
查詢表
單表查詢：
條件查詢 WHERE：
SELECT 欄位 1，欄位 2，欄位 3...FROM 表名 WHERE 欄位 [NOT] IN (元素 1，元素 2，元素 3)；//查詢屬性（不為）為元素1、元素2和元素3的結果
SELECT 欄位 1，欄位 2...FROM 表名 WHERE 條件表示式 1 AND 條件表示式 2 [...AND 條件表示式 n] //and多條件查詢
SELECT 欄位 1，欄位 2...FROM 表名 WHERE 條件表示式 1 OR 條件表示式 2 [...OR 條件表示式 n] //or多條件查詢
範圍查詢 BETWEEN AND：
SELECT 欄位 1，欄位 2，欄位 3...FROM 表名 WHERE 欄位 [NOT] BETWEEN 取值 1 > 指定值；//大於小於等符號
SELECT 欄位 1，欄位 2，欄位 3...FROM 表名 WHERE 欄位 [NOT] BETWEEN 取值 1 AND 取值 2；//兩端都包含
模糊查詢 LIKE：
SELECT 欄位 1，欄位 2，欄位 3...FROM 表名 WHERE 欄位 [NOT] LIKE ‘字串’；
“%”代表任意字元；
“_” 代表單個字元；
空值查詢 NOT NULL：
SELECT 欄位 1，欄位 2，欄位 3...FROM 表名 WHERE 欄位 IS [NOT] NULL；
去重複查詢 DISTINCT ：
SELECT DISTINCT 欄位名 FROM 表名；
排序 ORDER BY
SELECT 欄位 1，欄位 2...FROM 表名 ORDER BY 屬性名 [ASC|DESC] //ASC升序（預設） DESC降序
分組查詢 GROUP BY
GROUP BY 屬性名 [HAVING 條件表示式][WITH ROLLUP]
1，單獨使用(毫無意義)；
2，與 GROUP_CONCAT()函式一起使用；
3，與聚合函式一起使用；
4，與 HAVING 一起使用(限制輸出的結果)；
5，與 WITH ROLLUP 一起使用(最後加入一個總和行)；
GROUP_CONCAT()函式示例：
語句 SELECT locus,GROUP_CONCAT(id) FROM info WHERE locus IN('AB086827','AF040764') GROUP BY locus; 的返回結果為
+----------+------------------+
| locus | GROUP_CONCAT(id) |
+----------+------------------+
| locus | GROUP_CONCAT(id) |
+----------+------------------+
| AB086827 | 1,2 |
| AF040764 | 23,24 |
+----------+------------------+
即將查詢到的括號內欄位的結果合併到一起並用逗號隔開
可以將逗號更換成其他符號，如：
語句 SELECT locus,GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') FROM info WHERE locus IN('AB086827','AF040764') GROUP BY locus;的返回結果為
+----------+----------------------------------------------------------+
| locus | GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') |
+----------+----------------------------------------------------------+
| AB086827 | 2_1 |
| AF040764 | 24_23 |
| locus | GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') |
+----------+----------------------------------------------------------+
| AB086827 | 2_1 |
| AF040764 | 24_23 |
+----------+----------------------------------------------------------+
同時有CONCAT函式將查詢結果連成一個字串，但是查詢的欄位中有null時則整個結果為null，如：
SELECT CONCAT(id, ‘，’, name,age) AS con FROM info LIMIT 1;返回結果為
+----------+
| con |
+----------+
| 1,BioCyc23 |
| con |
+----------+
| 1,BioCyc23 |
+----------+
WITH ROLLUP會在返回結果的最後加上一行將查詢出的如果是數字結果則將所有結果數字相加，如果是字元結果則將所有字元合並
SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;
分頁查詢 limit：
SELECT 欄位 1，欄位 2...FROM 表名 LIMIT 初始位置，記錄數；
聚合函式查詢：
一，COUNT()函式
COUNT()函式用來統計記錄的條數； 與 GOUPE BY 關鍵字一起使用；
二，SUN()函式
SUM()函式是求和函式； 與 GOUPE BY 關鍵字一起使用；如：SELECT stuName,SUM(score) FROM t_grade GROUP BY stuName;//根據學生姓名分組算出每個學生的分數總和
注意：當用group by 分組查詢時只能查出分組的欄位和聚合函式的結果，其他欄位無法查出
三，AVG()函式
AVG()函式是求平均值的函式； 與 GOUPE BY 關鍵字一起使用；//SELECT stuName,AVG(score) FROM t_grade WHERE stuName="張三";//算出張三的平均分
四，MAX()函式
MAX()函式是求最大值的函式； 與 GOUPE BY 關鍵字一起使用；
MIN()函式
MIN()函式是求最小值的函式； 與 GOUPE BY 關鍵字一起使用；
多表查詢
連線查詢：
連線查詢是將兩個或兩個以上的表按照某個條件連線起來，從中選取需要的資料；
t_book表： t_bookType表：
如：SELECT * from t_book,t_booktype; 返回結果將兩個表的所有組合結果返回
1、內連線查詢：
內連線查詢是一種最常用的連線查詢。內連線查詢可以查詢兩個或者兩個以上的表；
SELECT * from t_book b,t_booktype t where t.id=b.id;
或SELECT * from t_book,t_booktype where t_book.id=t_booktype.id;
查詢結果：
也可這麼寫來查詢需要的欄位：SELECT bookName,author,bookTypeName from t_book,t_booktype where t_book.id=t_booktype.id;
注意：如果查詢的欄位（如id）中兩個表都有時這種寫法會出錯，因為無法得知id是哪個表的欄位，所以平時多表查詢時要寫別名
修改為：SELECT tb.bookName,tb.author,tby.id from t_book tb ,t_booktype tby where tb.id=tby.id;
2、外連線查詢
外連線可以查出某一張表的所有資訊；
SELECT 屬性名列表 FROM 表名 1 LEFT|RIGHT JOIN 表名 2 ON 表名 1.屬性名 1=表名 2.屬性名 2；
- 左連線查詢：
可以查詢出“表名 1”的所有記錄，而“表名 2”中，只能查詢出匹配的記錄；如：
select * from t_book tb left JOIN t_booktype tby on tb.bookTypeId=tby.id; 查詢結果：
- 右連線查詢：
可以查詢出“表名 2”的所有記錄，而“表名 1”中，只能查詢出匹配的記錄；如：
select * from t_book tb right JOIN t_booktype tby on tb.bookTypeId=tby.id; 查詢結果：
- 多條件連線查詢
即where後面有多個條件and連線查詢
子查詢
1 、帶 In 關鍵字的子查詢
一個查詢語句的條件可能落在另一個 SELECT 語句的查詢結果中。
2 、帶比較運算子的子查詢
子查詢可以使用比較運算子。
3 、帶 （not）Exists 關鍵字的子查詢
假如子查詢查詢到記錄，則進行外層查詢，否則，不執行外層查詢；not exists表示內層沒有內容時才查詢外層
如：SELECT * from t_book where EXISTS(select * from t_booktype);//表示t_booktype中返回的有內容時才執行外層t_book的查詢
4 、帶 Any 關鍵字的子查詢
ANY 關鍵字表示滿足其中任一條件；如：SELECT * FROM t_book WHERE price>= ANY (SELECT price FROM t_pricelevel);//表示篩選價格大於任意t_pricelevel表中選出的價格的結果，即比最小的大的價格就滿足條件
5 、帶 All 關鍵字的子查詢
ALL 關鍵字表示滿足所有條件；如：SELECT * FROM t_book WHERE price>= ALL (SELECT price FROM t_pricelevel);//表示篩選價格大於所有t_pricelevel表中選出的價格的結果，即比最大的價格大的才滿足條件
合併查詢
- UNION
使用 UNION 關鍵字是，資料庫系統會將所有的查詢結果合併到一起，然後去除掉相同的記錄；
- UNION ALL
使用 UNION ALL，不會去除掉系統的記錄；
索引
索引定義
索引是由資料庫表中一列或者多列組合而成，其作用是提高對錶中資料的查詢速度，建立索引查詢速度有數量級提升 ；
類似於圖書的目錄，方便快速定位，尋找指定的內容；
索引的優缺點
優點：提高查詢資料的速度；
缺點：建立和維護索引的時間增加了；
索引分類
1，普通索引
這類索引可以建立在任何資料型別中；
2，唯一性索引
使用 UNIQUE 引數可以設定，在建立唯一性索引時，限制該索引的值必須是唯一的；
3，全文索引
使用 FULLTEXT 引數可以設定，全文索引只能建立在 CHAR，VARCHAR，TEXT 型別的欄位上。主要作用
就是提高查詢較大字串型別的速度；只有 MyISAM 引擎支援該索引，Mysql 預設引擎不支援；
4，單列索引
在表中可以給單個欄位建立索引，單列索引可以是普通索引，也可以是唯一性索引，還可以是全文索引；
5，多列索引
多列索引是在表的多個欄位上建立一個索引；
6，空間索引
使用 SPATIAL 引數可以設定空間索引。空間索引只能建立在空間資料型別上，這樣可以提高系統獲取空間數
據的效率；只有 MyISAM 引擎支援該索引，Mysql 預設引擎不支援；
建立索引
- 建立表的時候建立索引
CREATE TABLE 表名 (屬性名 資料型別 [完整性約束條件],
屬性名 資料型別 [完整性約束條件],
....
屬性名 資料型別
[UNIQUE | FULLTEXT | SPATIAL ] INDEX| KEY
[別名] (屬性名 1 [(長度)] [ASC | DESC])
);
1，建立普通索引
CREATE TABLE mytable(
id INT PRIMARY KEY AUTO_INCREMENT,
bookId INT,
bookName VARCHAR(20) NOT NULL,
price DECIMAL(6,2),
INDEX(bookId) //建立普通索引
//UNIQUE INDEX (bookId) //建立唯一性索引
//UNIQUE INDEX bokId(bookId) //建立唯一性索引並起別名為bokId
//UNIQUE INDEX bokId(bookId，id) //建立多列索引並起別名為bokId
);
2，建立唯一性索引
3，建立全文索引（mysql預設搜尋引擎不支援）
4，建立單列索引
5，建立多列索引
6，建立空間索引（不支援）
- 在已經存在的表上建立索引
CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (屬性名 [(長度)] [ ASC | DESC])；
如：CREATE INDEX bookId ON mytable(bookId); //建立普通索引
CREATE UNIQUE INDEX bookId ON mytable(bookId); //建立唯一索引
CREATE UNIQUE INDEX bookId_price ON mytable(bookId,price); //建立多列索引
- 用 ALTER TABLE 語句來建立索引
ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (屬性名 [(長度)] [ ASC | DESC])；
如：ALTER TABLE mytable ADD INDEX index_bookId(bookId); //建立普通索引
ALTER TABLE mytable ADD UNIQUE INDEX index_bookId(bookId); //建立唯一索引
ALTER TABLE mytable ADD UNIQUE INDEX index_bookId_price(bookId,price); //建立多列索引
刪除索引
DROP INDEX 索引名 ON 表名 ； 如：drop index index_bookId_price on mytable;
檢視
第一節：檢視的引入
1，檢視是一種虛擬的表，是從資料庫中一個或者多個表中匯出來的表。
2，資料庫中只存放了檢視的定義，而並沒有存放檢視中的資料，這些資料存放在原來的表中。
3，使用檢視查詢資料時，資料庫系統會從原來的表中取出對應的資料。
第二節：檢視的作用
1，使操作簡便化；
2，增加資料的安全性；
3，提高表的邏輯獨立性；
第三節：建立檢視
CREATE [ ALGORITHM ={ UNDEFIEND | MERGE | TEMPTABLE }]
VIEW 檢視名 [ ( 屬性清單) ]
AS SELECT 語句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]；
ALGORITHM 是可選引數，表示檢視選擇的演算法；
“檢視名”參數列示要建立的檢視的名稱；
“屬性清單”是可選引數，其指定了檢視中各種屬性的名詞，預設情況下與 SELECT 語句中查詢的屬性相同；
SELECT 語句引數是一個完整的查詢語句，標識從某個表查出某些滿足條件的記錄，將這些記錄匯入檢視中；
WITH CHECK OPTION 是可選引數，表似乎更新檢視時要保證在該檢視的許可權範圍之內；
ALGORITHM 包括 3 個選項 UNDEFINED、MERGE 和 TEMPTABLE。其中，
UNDEFINED 選項表示 MySQL 將 自動選擇所要使用的演算法；
MERGE 選項表示將使用檢視的語句與檢視定義合併起來，使得檢視定義的某一部分 取代語句的對應部分；
TEMPTABLE 選項表示將檢視的結果存入臨時表，然後使用臨時表執行語句；
CASCADED 是可選引數，表示更新檢視時要滿足所有相關檢視和表的條件，該引數為預設值；
LOCAL 表示更新檢視時，要 滿足該檢視本身的定義條件即可；
如：建立檢視：create view v1 as select * from mytable where bookId >30;
查詢使用：select * from v1;
建立檢視並給返回的結果欄目定義欄目名：CREATE VIEW v1(b,p) AS select bookId,bookName from mytable;
返回結果如：
注意查詢檢視欄位時有別名的話按照別名查詢，沒有別名可以按照原欄位名稱查詢；
第四節：檢視檢視
4.1 DESCRIBE 檢視名稱； //檢視檢視欄位屬性資訊
4.2 SHOW TABLE STATUS LIKE ‘檢視名稱’；// 檢視檢視基本資訊，因為檢視是虛表，故查詢結果欄位都為空；此sql也可以查詢普通表的基本資訊；後面沒有like指定檢視時表示查詢庫中所有表和檢視的基本資訊
4.3 SHOW CREATE VIEW 檢視名稱；//檢視檢視詳細資訊
4.3 在 views 表中檢視檢視詳細資訊
第五節：修改檢視
5.1 CREATE OR REPLACE VIEW 語句修改檢視
CREATE OR REPLACE [ ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }]
VIEW 檢視名 [( 屬性清單 )]
AS SELECT 語句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]；
如：create or replace view v1 as select * from mytable where bookId < 30;
5.2 ALTER 語句修改檢視
ALTER [ ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }]
VIEW 檢視名 [( 屬性清單 )]
AS SELECT 語句
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]；
如：alter view v1 as select * from mytable;
第六節：更新檢視
更新檢視是指通過檢視來插入（INSERT）、更新（UPDATE）和刪除（DELETE）表中的資料。因為檢視是一個虛
擬的表，其中沒有資料。通過檢視更新時，都是轉換基本表來更新。更新檢視時，只能更新許可權範圍內的資料。
超出了範圍，就不能更新。
6.1 插入(INSERT)
CREATE VIEW v1(b,p) AS select bookId,bookName from mytable;
INSERT into v1 values(44,'think in java');//插入檢視，再次檢視檢視v1時會看到插入的資料，檢視操作實際是對錶的操作，可以看到mytable表中增加了一條資料，但是其他沒有插入的欄位則是顯示預設值
6.2 更新(UPDATE)
update v1 set b=8,p='設計模式' where b=44
6.3 刪除(DELETE)
delete from v1 where b=44;
第七節：刪除檢視
刪除檢視是指刪除資料庫中已存在的檢視。刪除檢視時，只能刪除檢視的定義，不會刪除資料；
DROP VIEW [ IF EXISTS ] 檢視名列表 [ RESTRICT | CASCADE ]
如： DROP VIEW IF EXISTS v1;
觸發器
第一節：觸發器的引入
觸發器（TRIGGER）是由事件來觸發某個操作。這些事件包括 INSERT 語句、UPDATE 語句和 DELETE 語句。
當資料庫系統執行這些事件時，就會啟用觸發器執行相應的操作。
第二節：建立與使用觸發器
2.1 建立只有一個執行語句的觸發器
CREATE TRIGGER 觸發器名 BEFORE | AFTER 觸發事件
ON 表名 FOR EACH ROW 事件發生時要執行的語句
如：CREATE TRIGGER trig_book AFTER INSERT
ON t_book FOR EACH ROW
UPDATE t_bookType SET bookNum=bookNum+1 WHERE new.bookTypeId=t_booktype.id;
表示t_book表中國執行插入操作時觸發t_bookType的更新操作，將bookNum加1
注：new是中間量，表示插入（更新）時的那條資料，如果是刪除時用old，表示刪除的那條資料
2.2 建立有多個執行語句的觸發器
CREATE TRIGGER 觸發器名 BEFORE | AFTER 觸發事件
ON 表名 FOR EACH ROW
BEGIN
執行語句列表
END
如：
DELIMITER |
CREATE TRIGGER trig_book2 AFTER DELETE
ON t_book FOR EACH ROW
BEGIN
UPDATE t_bookType SET bookNum=bookNum-1 WHERE old.bookTypeId=t_booktype.id;
INSERT INTO t_log VALUES(NULL,NOW(),'在book表裡刪除了一條資料');
DELETE FROM t_test WHERE old.bookTypeId=t_test.id;
END
|
DELIMITER ;
注：DELIMITER | 包裹起來避免觸發多條sql時遇到分號中斷執行的情況
第三節：檢視觸發器
3.1 SHOW TRIGGERS; 語句檢視觸發器資訊
3.2 在 triggers 表中檢視觸發器資訊
第四節：刪除觸發器
DROP TRIGGER 觸發器名；
mysql常用函式（部分舉例）
第一節：日期和時間函式
1，CURDATE() 返回當前系統日期； 如：select CURDATE() ,CURTIME(),MONTH(created_at) from theme13；
2，CURTIME() 返回當前系統時間；
3，MONTH(d) 返回日期 d 中的月份值，範圍是 1~12
第二節：字串函式
1，CHAR_LENGTH(s) 計算字串 s 的字元數；
2，UPPER(s) 把所有字母變成大寫字母；
3，LOWER(s) 把所有字母變成小寫字母；
第三節：數學函式
1，A BS(x) 求絕對值
2，SQRT(x) 求平方根
3，MOD(x,y) 求餘
第四節：加密函式
1，PASSWORD(str) 一般對使用者的密碼加密 不可逆
2，MD5(str) 普通加密 不可逆
3，ENCODE(str，pswd_str) 加密函式，結果是一個二進位制數，必須使用 BLOB 型別的欄位來儲存它；
4，DECODE(crypt_str，pswd_str) 解密函式；
儲存過程和函式的引入
第一節：儲存過程和函式的引入
儲存過程和函式是在資料庫中定義一些 SQL 語句的集合，然後直接呼叫這些儲存過程和函式來執行已經定義好
的 SQL 語句。儲存過程和函式可以避免開發人員重複的編寫相同的 SQL 語句。而且，儲存過程和函式是在 MySQL
伺服器中儲存和執行的，可以減少客戶端和伺服器端的資料傳輸；
第二節：建立儲存過程和函式
1 建立儲存過程
CREATE PROCEDURE sp_name([proc_parameter[,...]])
[characteristic...] routine_body
sp_name 引數是儲存過程的名稱；
proc_parameter 表示儲存過程的引數列表；
characteristic 引數指定儲存過程的特性；
routine_body 引數是 SQL 程式碼的內容，可以用 BEGIN...END 來標誌 SQL 程式碼的開始和結束。
proc_parameter 中的每個引數由 3 部分組成。這 3 部分分別是輸入輸出型別、引數名稱和引數型別。
[ IN | OUT | INOUT ] param_name type
其中，IN 表示輸入引數；OUT 表示輸出引數；INOUT 表示既可以是輸入，也可以是輸出；param_name 引數是
儲存過程的引數名稱；type 引數指定儲存過程的引數型別，該型別可以是 MySQL 資料庫的任意資料型別；
Characteristic 引數有多個取值。其取值說明如下：
LANGUAGE SQL：說明 routine_body 部分是由 SQL 語言的語句組成，這也是資料庫系統預設的語言。
[ NOT ] DETERMINISTIC ：指明儲存過程的執行結果是否是確定的。DETERMINISTIC 表示結果是確定的。每
次執行儲存過程時，相同的輸入會得到相同的輸出。NOT DETERMINISTIC 表示結果是非確定的，相同的輸入
可能得到不同的輸出。預設情況下，結果是非確定的。
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程式使用 SQL 語句的限制；
CONTAINS SQL 表示子程式包含 SQL 語句，但不包含讀或寫資料的語句；NO SQL 表示子程式中不包含 SQL
語句；READS SQL DATA 表示子程式中包含讀資料的語句；MODIFIES SQL DATA 表示子程式中包含寫資料的
語句。預設情況下，系統會指定為 CONTAINS SQL；
SQL SECURITY { DEFINER | INVOKER }；指明誰有許可權來執行。DEFINER 表示只有定義者自己才能夠執行；
INVOKER 表示呼叫者可以執行。預設情況下，系統指定的許可權是 DEFINER。
COMMENT ‘string’ ：註釋資訊；
如：
DELIMITER &&
CREATE PROCEDURE pro_book ( IN bT INT,OUT count_num INT)
READS SQL DATA
BEGIN
SELECT COUNT(*) FROM t_book WHERE bookTypeId=bT;
END
&&
DELIMITER ;
呼叫：CALL pro_book(1,@total);
2 建立儲存函式
CREATE FUNCTION sp_name ( [func_parameter[,...]] )
RETURNS type
[ characteristic... ] routine_body
sp_name 引數是儲存函式的名稱；func_parameter 表示儲存函式的引數列表；RETURNS type 指定返回值的
型別；characteristic 引數指定儲存過程的特性，該引數的取值與儲存過程中的取值是一樣的；routine_body 引數
是 SQL 程式碼的內容，可以用 BEGIN...END 來標誌 SQL 程式碼的開始和結束；
func_parameter 可以由多個引數組成，其中每個引數由引數名稱和引數型別組成，其形式如下：
param_name type
其中，param_name 引數是儲存函式的引數名稱；type 引數指定儲存函式的引數型別，
該型別可以是 MySQL 資料庫的任意資料型別；
如：
DELIMITER &&
CREATE FUNCTION func_book (bookId INT)
RETURNS VARCHAR(20)
BEGIN
RETURN ( SELECT bookName FROM t_book WHERE id=bookId );
END
&&
DELIMITER ;
SELECT func_book(2);
3 變數的使用
- 定義變數
DECLARE var_name [,...] type [ DEFAULT value ]
如：
DELIMITER &&
CREATE PROCEDURE pro_user()
BEGIN
DECLARE a,b VARCHAR(20) ;
INSERT INTO t_user VALUES(NULL,a,b);
END
&&
DELIMITER ;
呼叫該儲存過程後會向資料庫新增一條空值記錄
//將t_user2表資料讀取出來直接插入到t_user中
DELIMITER &&
CREATE PROCEDURE pro_user3()
BEGIN
DECLARE a,b VARCHAR(20) ;
SELECT userName2,password2 INTO a,b FROM t_user2 WHERE id2=1;
INSERT INTO t_user VALUES(NULL,a,b);
END
&&
DELIMITER ;
- 為變數賦值
SET var_name = expr [,var_name=expr] ...
SELECT col_name[,...] INTO var_name[,...]
FROM table_name WHERE condition
如：
DELIMITER &&
CREATE PROCEDURE pro_user2()
BEGIN
DECLARE a,b VARCHAR(20) ;
SET a='java1234',b='123456';
INSERT INTO t_user VALUES(NULL,a,b);
END
&&
DELIMITER ;
4 遊標的使用
查詢語句可能查詢出多條記錄，在儲存過程和函式中使用遊標來逐條讀取查詢結果集中的記錄。遊標的使
用包括宣告遊標、開啟遊標、使用遊標和關閉遊標。遊標必須宣告在處理程式之前，並且宣告在變數和條
件之後。
1，宣告遊標
DECLARE cursor_name CURSOR FOR select_statement ;
2，開啟遊標
OPEN cursor_name;
3，使用遊標
FETCH cursor_name INTO var_name [,var_name ... ]；
4，關閉遊標
CLOSE cursor_name；
如：
DELIMITER &&
CREATE PROCEDURE pro_user4()
BEGIN
DECLARE a,b VARCHAR(20) ;
DECLARE cur_t_user2 CURSOR FOR SELECT userName2,password2 FROM t_user2; //宣告遊標
OPEN cur_t_user2; //開啟遊標
FETCH cur_t_user2 INTO a,b; //將遊標資料賦給a和b
INSERT INTO t_user VALUES(NULL,a,b);
CLOSE cur_t_user2; //關閉遊標
END
&&
DELIMITER ;
5 流程控制的使用
儲存過程和函式中可以使用流程控制來控制語句的執行。MySQL 中可以使用 IF 語句、CASE 語句、LOOP語句、LEAVE 語句、ITERATE 語句、REPEAT 語句和 WHILE 語句來進行流程控制。
- IF 語句
IF search_condition THEN statement_list
[ ELSEIF search_condition THEN statement_list ]...
[ ELSE statement_list ]
END IF
如：
DELIMITER &&
CREATE PROCEDURE pro_user5(IN bookId INT)
BEGIN
SELECT COUNT(*) INTO @num FROM t_user WHERE id=bookId;
IF @num>0 THEN UPDATE t_user SET userName='java12345' WHERE id=bookId;
ELSE
INSERT INTO t_user VALUES(NULL,'2312312','2321312');
END IF ;
END
&&
DELIMITER ;
注：@num表示定義的會話變數或者全域性變數
- CASE 語句
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]...
[ELSE statement_list ]
END CASE
如：
DELIMITER &&
CREATE PROCEDURE pro_user6(IN bookId INT)
BEGIN
SELECT COUNT(*) INTO @num FROM t_user WHERE id=bookId;
CASE @num
WHEN 1 THEN UPDATE t_user SET userName='java12345' WHERE id=bookId;
WHEN 2 THEN INSERT INTO t_user VALUES(NULL,'2312312','2321312');
ELSE INSERT INTO t_user VALUES(NULL,'231231221321312','2321312321312');
END CASE ;
END
&&
DELIMITER ;
- LOOP，LEAVE 語句
LOOP 語句可以使某些特定的語句重複執行，實現一個簡單的迴圈。但是 LOOP 語句本身沒有停止迴圈的語句，必須是遇到 LEAVE 語句等才能停止迴圈。LOOP 語句的語法的基本形式如下：
[begin_label：]LOOP
Statement_list
END LOOP [ end_label ]
LEAVE 語句主要用於跳出迴圈控制。語法形式如下：
LEAVE label
如：
DELIMITER &&
CREATE PROCEDURE pro_user7(IN totalNum INT)
BEGIN
aaa:LOOP
SET totalNum=totalNum-1;
IF totalNum=0 THEN LEAVE aaa ;
ELSE INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
END IF ;
END LOOP aaa ;
END
&&
DELIMITER ;
- ITERATE 語句
ITERATE 語句也是用來跳出迴圈的語句。但是，ITERATE 語句是跳出本次迴圈，然後直接進入下一次迴圈。基本語法：
ITERATE label ;
如：
DELIMITER &&
CREATE PROCEDURE pro_user8(IN totalNum INT)
BEGIN
aaa:LOOP
SET totalNum=totalNum-1;
IF totalNum=0 THEN LEAVE aaa ;
ELSEIF totalNum=3 THEN ITERATE aaa ;
END IF ;
INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
END LOOP aaa ;
END
&&
DELIMITER ;
- REPEAT 語句
REPEAT 語句是有條件控制的迴圈語句。當滿足特定條件時，就會跳出迴圈語句。REPEAT 語句的基本語法形式如下：
[ begin_label : ] REPEAT
Statement_list
UNTIL search_condition
END REPEAT [ end_label ]
如：
DELIMITER &&
CREATE PROCEDURE pro_user9(IN totalNum INT)
BEGIN
REPEAT
SET totalNum=totalNum-1;
INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
UNTIL totalNum=1
END REPEAT;
END
&&
DELIMITER ;
//表示重複直到totalNum=1結束迴圈
- WHILE 語句
[ begin_label : ] WHILE search_condition DO
Statement_list
END WHILE [ end_label ]
如：
DELIMITER &&
CREATE PROCEDURE pro_user10(IN totalNum INT)
BEGIN
WHILE totalNum>0 DO
INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
SET totalNum=totalNum-1;
END WHILE ;
END
&&
DELIMITER ;
第三節：呼叫儲存過程和函式
1 呼叫儲存過程
CALL sp_name( [parameter[,...]] )
2 呼叫儲存函式
fun_name( [parameter[,...]] )
第四節：檢視儲存過程和函式
4.1 SHOW STATUS 語句檢視儲存過程和函式的狀態
SHOW { PROCEDURE | FUNCTION } STATUS [ LIKE ‘pattern’ ] ;//pattern代表儲存過程名
如：SHOW PROCEDURE STATUS LIKE 'pro_book';
4.2 SHOW CREATE 語句檢視儲存過程的函式的定義
SHOW CREATE { PROCEDURE | FUNCTION } sp_name ;
如：SHOW CREATE PROCEDURE pro_book;
4.3 從 information_schema.Routines 表中檢視儲存過程和函式的資訊
第五節：修改儲存過程和函式
ALTER { PROCEDURE | FUNCTION } sp_name [ characteristic ... ]
characteristic :
{ CONTAINS SQL } NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’
其中，sp_name 參數列示儲存過程或函式的名稱；characteristic 引數指定函式的特性。CONTAINS SQL 表示子程
序包含 SQL 語句，但不包含讀或寫資料的語句；NO SQL 表示子程式中不包含 SQL 語句；READS SQL DATA
表 示 子 程 序 中 包 含 數 據 的 語 句 ； MODIFIES
SQL DATA 表 示 子 程 序 中 包 含 寫 數 據 的 語 句 。 SQL
SECURITY{ DEFINER | INVODER } 指明誰有許可權來執行。 DEFINER 表示只有定義者自己才能夠執行；
INVODER 表示呼叫者可以執行。COMMENT ‘string’ 是註釋資訊。
如：
ALTER PROCEDURE pro_book COMMENT '我來測試一個COMMENT'; //修改pro_book 的註釋為'我來測試一個COMMENT'
第六節：刪除儲存過程和函式
DROP {PROCEDURE | FUNCTION } sp_name ;
如：
DROP PROCEDURE pro_user3;
資料備份與還原
第一節：資料備份
備份資料可以保證資料庫中資料的安全，資料庫管理員需要定期的進行資料庫備份；
1.1 使用 mysqldump 命令備份
mysqldump -u username -p dbname table1 table2 ... > BackupName.sql
dbname 參數列示資料庫的名稱；table1 和 table2 參數列示表的名稱，沒有該引數時將備份整個資料庫；
BackupName.sql 參數列示備份檔案的名稱，檔名前面可以加上一個絕對路徑。通常以 sql 作為字尾。
如：
mysqldump -u root -p db_book > c:\db_book.sql //將整個db_book資料庫備份到c盤db_book.sql檔案中，當然圖形介面都是可以的
1.2 使用 sqlyog 圖形工具備份
第二節：資料還原
2.2 使用 mysql 命令還原
Mysql -u root -p [dbname] < backup.sql
dbname 參數列示資料庫名稱。該引數是可選引數，可以指定資料庫名，也可以不指定。指定資料庫名時，表
示還原該資料庫下的表。不指定資料庫名時，表示還原特定的一個資料庫。而備份檔案中有建立資料庫的語句。
2.3 使用 sqlyog 圖形工具還原