MySQL 教程–檢視閱讀
- 2020 年 3 月 29 日
- 筆記
MySQL 教程–檢視閱讀
準備：Windows 上安裝 MySQL
教程地址，PHP語言基礎
教程地址2
教程地址3，有講資料庫的備份和恢復
教程地址4,w3c.china,php基礎，擴展閱讀
Markdown支援的程式碼塊類型
- 這個教程大體在講什麼？
- 教程細部說了什麼？
- 是否同意教程的知識和觀點，同意程度？
- 該教程為了帶來了什麼？
RDBMS(Relational Database Management System：關係資料庫管理系統 。
安裝地址：C:Program FilesMySQLMySQL Server 5.7 登入mysql伺服器 1、打開cmd輸入mysql –u root –p 2、輸入root的密碼 什麼是資料庫？
資料庫（Database）是按照數據結構來組織、存儲和管理數據的倉庫。
每個資料庫都有一個或多個不同的 API 用於創建，訪問，管理，搜索和複製所保存的數據。
我們也可以將數據存儲在文件中，但是在文件中讀寫數據速度相對較慢。
所以，現在我們使用關係型資料庫管理系統（RDBMS）來存儲和管理大數據量。所謂的關係型資料庫，是建立在關係模型基礎上的資料庫，藉助於集合代數等數學概念和方法來處理資料庫中的數據。
RDBMS 即關係資料庫管理系統(Relational Database Management System)的特點：
- 1.數據以表格的形式出現
- 2.每行為各種記錄名稱
- 3.每列為記錄名稱所對應的數據域
- 4.許多的行和列組成一張表單
- 5.若干的表單組成database
RDBMS 術語
在我們開始學習MySQL 資料庫前，讓我們先了解下RDBMS的一些術語：
-
資料庫: 資料庫是一些關聯表的集合。
-
數據表: 表是數據的矩陣。在一個資料庫中的表看起來像一個簡單的電子表格。
-
列: 一列(數據元素) 包含了相同類型的數據, 例如郵政編碼的數據。
-
行：一行（=元組，或記錄）是一組相關的數據，例如一條用戶訂閱的數據。
-
冗餘：存儲兩倍數據，冗餘降低了性能，但提高了數據的安全性。
-
主鍵：主鍵是唯一的。一個數據表中只能包含一個主鍵。你可以使用主鍵來查詢數據。
-
外鍵：外鍵用於關聯兩個表。
-
複合鍵：複合鍵（組合鍵）將多個列作為一個索引鍵，一般用於複合索引。
-
索引：使用索引可快速訪問資料庫表中的特定資訊。索引是對資料庫表中一列或多列的值進行排序的一種結構。類似於書籍的目錄。
-
參照完整性: 參照的完整性要求關係中不允許引用不存在的實體。與實體完整性是關係模型必須滿足的完整性約束條件，目的是保證數據的一致性。
-
MySQL 支援大型資料庫，支援 5000 萬條記錄的數據倉庫，32 位系統表文件最大可支援 4GB，64 位系統支援最大的表文件為8TB。
-
MySQL 是可以訂製的，採用了 GPL 協議，你可以修改源碼來開發自己的 MySQL 系統。
啟動及關閉 MySQL 伺服器
1.Windows下 啟動服務 mysqld --console (用不了) 或 net start mysql （需用管理權許可權啟動cmd，否則會報發生系統錯誤 5。 拒絕訪問） 注意 如果mysql server 名稱不是默認的，則應該用命令 net start mysql5717 ，其中mysql5717是按照時mysql的名稱。 關閉服務 mysqladmin -uroot shudown 如果有設置密碼，則為 mysqladmin -uroot -p123456 shudown 123456為root密碼 或 net stop mysql || net start mysql5717 2.Linux下 檢查MySQL伺服器是否啟動：ps -ef | grep mysqld 命令行方式： 開啟 ./mysqld_safe & 關閉 mysqladmin -uroot shutdown rpm方式安裝的 開啟 service mysql start 關閉 service mysql stop 重啟服務 service restart stop ## mysql.user表中的欄位定義：collate utf_bin是 以二進位值比較，也就是區分大小寫，collate是核對的意思 `authentication_string` text COLLATE utf8_bin 針對資料庫的基本操作，非業務相關
use hello_spring; SELECT * from user; #mysql 新設置用戶或更改密碼後需用flush privileges刷新MySQL的系統許可權相關表，否則會出現拒絕訪問，還有一種方法，就是重新啟動mysql伺服器，來使新設置生效。 FLUSH PRIVILEGES; #在添加用戶時，請注意使用MySQL提供的 PASSWORD() 函數來對密碼進行加密。 #password() 加密函數已經在 8.0.11 中移除了，可以使用 MD5() 函數代替。 #在注意需要執行 FLUSH PRIVILEGES 語句。 這個命令執行後會重新載入授權表。 INSERT INTO user (host,user,authentication_string, select_priv, insert_priv, update_priv,ssl_cipher,x509_issuer,x509_subject)VALUES('localhost', 'guest', PASSWORD('123456'), 'Y', 'Y', 'Y','(BLOB)','(BLOB)','(BLOB)'); #列出 MySQL 資料庫管理系統的資料庫列表。 SHOW DATABASES; #顯示指定資料庫的所有表，使用該命令前需要使用 use 命令來選擇要操作的資料庫 SHOW TABLES; #顯示數據表的屬性，屬性類型，主鍵資訊 ，是否為 NULL，默認值等其他資訊。 SHOW COLUMNS FROM t_developer; #顯示數據表的詳細索引資訊，包括PRIMARY KEY（主鍵） SHOW INDEX FROM t_developer; #添加索引 ALTER TABLE t_developer ADD INDEX idx_name(name); #該命令將輸出Mysql資料庫管理系統的性能及統計資訊。 SHOW TABLE STATUS from hello_spring LIKE 't_d%'; #加上 G，查詢結果按列列印 ,這個得在命令行里才能使用 SHOW TABLE STATUS from hello_spring LIKE 't_d%'G; MySQL 連接
注意：mysql 不區分大小寫。
#連接mysql伺服器命令 [root@host]# mysql -u root -p Enter password:****** #如果直接寫成下面這種，並不能直接登錄，因為密碼是要另外寫的，-p後面接的是資料庫的名字 mysql -u root -p 123456 mysql -u root -p mysql(資料庫名) #退出 exit 資料庫命令
#創建資料庫 CREATE DATABASE hello_spring_backup; #刪除資料庫 DROP DATABASE hello_spring_backup; #選擇資料庫 USE hello_spring_backup; MySQL 數據類型
MySQL中定義數據欄位的類型對你資料庫的優化是非常重要的。
MySQL支援多種類型，大致可以分為三類：數值、日期/時間和字元串(字元)類型。
數值類型
MySQL支援所有標準SQL數值數據類型。
這些類型包括嚴格數值數據類型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似數值數據類型(FLOAT、REAL和DOUBLE PRECISION)。
類型 大小 範圍（有符號） 範圍（無符號） 用途
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,223,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的值 小數值
日期和時間類型
類型 大小 (位元組) 範圍 格式 用途
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 4 1970-01-01 00:00:00/2038結束時間是第 2147483647 秒，北京時間 2038-1-19 11:14:07，格林尼治時間 2038年1月19日 凌晨 03:14:07 YYYYMMDD HHMMSS 混合日期和時間值，時間戳
字元串類型
注意：CHAR 和 VARCHAR 類型類似，但它們保存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。檢索過程中是大小寫不敏感的。
類型 大小 用途
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位元組 極大文本數據
MySQL 創建數據表
- 如果你不想欄位為 NULL 可以設置欄位的屬性為 NOT NULL， 在操作資料庫時如果輸入該欄位的數據為NULL ，就會報錯。
- AUTO_INCREMENT定義列為自增的屬性，一般用於主鍵，數值會自動加1。
- PRIMARY KEY關鍵字用於定義列為主鍵。 您可以使用多列來定義主鍵，列間以逗號分隔。
- ENGINE 設置存儲引擎，CHARSET 設置編碼。
建表demo：
CREATE TABLE IF NOT EXISTS t_developer ( id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵', name VARCHAR(30) NOT NULL COMMENT '姓名', work_level INT(6) NOT NULL COMMENT '等級', position VARCHAR(30) DEFAULT NULL COMMENT '職位', salary DECIMAL(13,2) DEFAULT '0' COMMENT '薪水', status SMALLINT(2) DEFAULT NULL COMMENT '狀態', create_time datetime DEFAULT NOW() COMMENT '創建時間', create_emp_id BIGINT(10) NOT NULL COMMENT '創建人', PRIMARY KEY (id) )ENGINE = INNODB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8 COMMENT '開發員工表'; S CREATE TABLE IF NOT EXISTS t_bonus ( id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵', developer_id BIGINT(20) NOT NULL COMMENT '員工id', bonus DECIMAL(13,2) DEFAULT '0' COMMENT '獎金', performance SMALLINT(2) DEFAULT NULL COMMENT '績效', PRIMARY KEY (id) )ENGINE = INNODB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 COMMENT '開發員工獎金錶'; 數據表操作
注意： 在shell命令下使用mysql時，使用箭頭標記 -> 不是 SQL 語句的一部分，它僅僅表示一個新行，如果一條SQL語句太長，我們可以通過回車鍵來創建一個新行來編寫 SQL 語句，SQL 語句的命令結束符為分號 ;
數據查詢
- 查詢語句中你可以使用一個或者多個表，表之間使用逗號(,)分割，並使用WHERE語句來設定查詢條件。
- SELECT 命令可以讀取一條或者多條記錄。
- 你可以使用星號（*）來代替其他欄位，SELECT語句會返回表的所有欄位數據
- 你可以使用 WHERE 語句來包含任何條件。
- 你可以使用 LIMIT 屬性來設定返回的記錄數。
- 你可以通過OFFSET指定SELECT語句開始查詢的數據偏移量。默認情況下偏移量為0。
- MySQL 的 WHERE 子句的字元串比較是不區分大小寫的。 你可以使用 BINARY 關鍵字來設定 WHERE 子句的字元串比較是區分大小寫的。
引申MySQL中使用LIMIT進行分頁的方法：
參考
LIMIT 子句可以被用於強制 SELECT 語句返回指定的記錄數。LIMIT 接受一個或兩個數字參數。參數必須是一個整數常量。如果給定兩個參數，第一個參數指定第一個返回記錄行的偏移量，第二個參數指定返回記錄行的最大數目。初始記錄行的偏移量是 0(而不是 1) 。
#MySQL中使用LIMIT進行分頁的方法，利用分頁函數limit m,n #其中currentPage是頁碼(當前頁碼)，pageSize是每頁顯示的條數。 select * from table limit (currentPage-1)*pageSize,pageSize; -------------------------函數limit m,n----------------------------- SELECT * FROM table LIMIT 5,10; // 檢索記錄行 6-15 ##為了檢索從某一個偏移量到記錄集的結束所有的記錄行，可以指定第二個參數為 -1： SELECT * FROM table LIMIT 95,-1; // 檢索記錄行 96-last. --如果只給定一個參數，它表示返回最大的記錄行數目，換句話說，LIMIT n 等價於 LIMIT 0,n。 SELECT * FROM table LIMIT 5; //檢索前 5 個記錄行 #刪除表 DROP TABLE t_developer_backup; #MySQL插入數據，如果數據是字元型，必須使用單引號或者雙引號，如："value"。 #-------------------------插入數據----------------------------- INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN ); -------------------------格式------------------ #插入數據時可以指定字元串形式的時間 INSERT INTO t_developer(name,work_level,position,salary,status,create_time,create_emp_id)VALUES('小甜甜',7,"shanghai",63521.02, 1,'2020-03-30 10:10:10', 690050); #插入數據時也可以指定字元串形式的日期，時分秒mysql會自動補充0 INSERT INTO t_developer(name,work_level,position,salary,status,create_time,create_emp_id)VALUES('甜甜',7,"shanghai",63521.02, 1,'2020-03-30', 690060); #插入數據時也可以不指定字元串形式的時間，因為在建表時默認時間不指定則為now() INSERT INTO t_developer(name,work_level,position,salary,status,create_emp_id)VALUES('甜甜',7,"shanghai",63521.02, 1, 690060); SELECT * FROM t_developer; SELECT DATE_FORMAT(b.create_time, "%Y-%m-%d %H:%i:%s") FROM t_developer b WHERE b.id=1000; #DATE_FORMAT(date,format) 函數的fomat與java不同，下面這個語句是不能把時間轉換成想要的字元串格式的時間的 SELECT DATE_FORMAT('2020-03-30 10:10:10','YYYY-MM-DD HH:MM:SS') FROM DUAL; #要使用這種format格式才行 SELECT DATE_FORMAT('2020-03-30 10:10:10',"%Y-%m-%d %H:%i:%s") FROM DUAL; #-------------------------插入數據----------------------------- #-------------------------查詢數據----------------------------- SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N][ OFFSET M] -------------------------格式------------------ SELECT d.`name`,d.salary,d.work_level FROM t_developer d LIMIT 2; #limit和offset用法,下面兩種寫法都表示取2,3,4三條條數據；OFFSET要配合limit使用才行，而limit可以單獨使用 SELECT * FROM t_developer LIMIT 1,3; SELECT * FROM t_developer LIMIT 3 OFFSET 1; #LIKE子句 模糊查詢 #like 匹配/模糊匹配，會與 % 和 _ 結合使用。 _ 單字母或者單個中文匹配。 SELECT * FROM t_developer d where d.`name` like 'z%'; SELECT * FROM t_developer d where d.`name` like '小甜_'; #MySQL UNION 操作符用於連接兩個以上的 SELECT 語句的結果組合到一個結果集合中。多個 SELECT 語句會刪除重複的數據。 SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] UNION [ALL | DISTINCT] SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions]; # UNION 會去重，UNION ALL則返回所有查詢到的數據，因此UNION ALL 效率更高。 #UNION 聯合結果集合 去重 SELECT d.`name` FROM t_developer d UNION SELECT dd.`name` FROM t_developer_backup dd ; #全部集合不去重 SELECT d.`name` FROM t_developer d UNION ALL SELECT dd.`name` FROM t_developer_backup dd ; #數據排序 默認情況下，它是按升序排列。 SELECT field1, field2,...fieldN FROM table_name1, table_name2... ORDER BY field1 [ASC [DESC][默認 ASC]], [field2...] [ASC [DESC][默認 ASC]] SELECT * FROM t_developer ORDER BY create_time; SELECT * FROM t_developer ORDER BY create_time DESC; # GROUP BY 語句 #GROUP BY 語句根據一個或多個列對結果集進行分組。 #在分組的列上我們可以使用 COUNT, SUM, AVG,等函數。 SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name; ------------------------------------------ SELECT d.`name`,count(*) FROM t_developer d GROUP BY d.`name`; SELECT d.`name`,d.salary,count(*) FROM t_developer d GROUP BY d.`name`; #按名稱分組，統計每個人總的薪水 SELECT d.`name`,SUM(d.salary),count(*) FROM t_developer d GROUP BY d.`name`; --使用 WITH ROLLUP 可以實現在分組統計數據基礎上再進行相同的統計（SUM,AVG,COUNT…）。 --也就是說，它會把我們每個人的總薪水都加起來算出總的統計或者平均統計，這要看分組中的函數是總的還是平均函數 /** coalesce 來設置一個可以取代 NUll 的名稱，coalesce 語法： select coalesce(a,b,c); 參數說明：如果a==null,則選擇b；如果b==null,則選擇c；如果a!=null,則選擇a；如果a b c 都為null ，則返回為null（沒意義）。**/ #按名稱分組，統計每個人總的薪水和平均薪水 SELECT COALESCE(d.`name`,'總額'),SUM(d.salary),count(*),AVG(d.salary) FROM t_developer d GROUP BY d.`name` WITH ROLLUP; #-------------------------查詢數據----------------------------- #-------------------------UPDATE更新數據----------------------------- UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause] -------------------------格式------------------ UPDATE t_developer d SET d.`name`='MM',d.position='quanzhou' WHERE d.id=1002; #-------------------------UPDATE更新數據----------------------------- #-------------------------刪除數據----------------------------- DELETE FROM table_name [WHERE Clause] #多表的刪除： delete 別名1，別名2 from 表1 別名1，表2 別名2 where 連接條件 and 篩選條件; truncate table 表名 --執行的速度上，drop>truncate>delete--- -------------------------格式------------------ #刪除 DELETE FROM t_developer where id=1004; #表名不能用別名去操作刪除，會報錯 DELETE FROM t_developer d where d.id=1004; #多表刪除 #通過備份數據刪除 DELETE t_developer FROM t_developer ,t_developer_backup b WHERE b.id = t_developer.id; #多表刪除中兩張表都匹配到的數據就都會刪除 DELETE t_developer ,b FROM t_developer ,t_developer_backup b WHERE b.id = t_developer.id; # 注意多表刪除時要對兩張表都要加where限定條件，沒加條件限制的表將整表刪除。 DELETE d,b FROM t_developer d,t_developer_backup b WHERE d.id=1008 and b.id=1000; #整張表數據刪除 TRUNCATE t_developer_backup1; 操作圖：
MySQL JOIN 聯表查詢數據
你可以在 SELECT, UPDATE 和 DELETE 語句中使用 Mysql 的 JOIN 來聯合多表查詢。
JOIN 按照功能大致分為如下三類：
-
INNER JOIN（內連接,或等值連接）：獲取兩個表中欄位匹配關係的記錄。
-
LEFT JOIN（左連接）：獲取左表所有記錄，即使右表沒有對應匹配的記錄。
-
RIGHT JOIN（右連接）： 與 LEFT JOIN 相反，用於獲取右表所有記錄，即使左表沒有對應匹配的記錄。
–INNER JOIN (也可以省略 INNER 使用 JOIN，效果一樣)
SELECT d.
name,d.position,b.bonus,b.performance from t_developer d INNER JOIN t_bonus b ON d.id = b.developer_id;— 不建議只使用 JOIN來關聯查詢，要嘛全名INNER JOIN要嘛用,
SELECT d.name,d.position,b.bonus,b.performance from t_developer d JOIN t_bonus b WHERE d.id = b.developer_id;SELECT d.
name,d.position,b.bonus,b.performance from t_developer d ,t_bonus b WHERE d.id = b.developer_id;— LEFT JOIN 左連 會讀取左邊數據表的全部數據，即便右邊表無對應數據。
SELECT d.name,d.position,b.bonus,b.performance from t_developer d LEFT JOIN t_bonus b ON d.id = b.developer_id;–RIGHT JOIN 會讀取右邊數據表的全部數據，即便左邊邊表無對應數據。
SELECT d.name,d.position,b.bonus,b.performance from t_developer d RIGHT JOIN t_bonus b ON d.id = b.developer_id;–多表關聯 沒錯LEFT JOIN 都會有對應的 ON 關聯欄位把表關聯起來
SELECT d.name,d.position,b.bonus,b.performance
from t_developer d
LEFT JOIN t_bonus b ON d.id = b.developer_id
INNER JOIN t_developer_backup1 db ON d.id=db.id;
聯表查詢包含關係：
MySQL NULL 值處理
我們已經知道 MySQL 使用 SQL SELECT 命令及 WHERE 子句來讀取數據表中的數據,但是當提供的查詢條件欄位為 NULL 時，該命令可能就無法正常工作。
為了處理這種情況，MySQL提供了三大運算符:
- IS NULL: 當列的值是 NULL,此運算符返回 true。
- IS NOT NULL: 當列的值不為 NULL, 運算符返回 true。
- <=>: 比較操作符（不同於 = 運算符），當比較的的兩個值相等或者都為 NULL 時返回 true。
關於 NULL 的條件比較運算是比較特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。
在 MySQL 中，NULL 值與任何其它值的比較（即使是 NULL）永遠返回 NULL，即 NULL = NULL 返回 NULL 。
MySQL 中處理 NULL 使用 IS NULL 和 IS NOT NULL 運算符。
MySQL 正則表達式
MySQL中使用 REGEXP 操作符來進行正則表達式匹配。
模式 描述
^ 匹配輸入字元串的開始位置。如果設置了 RegExp 對象的 Multiline 屬性，^ 也匹配 ‘n’ 或 ‘r’ 之後的位置。
$ 匹配輸入字元串的結束位置。如果設置了RegExp 對象的 Multiline 屬性，$ 也匹配 ‘n’ 或 ‘r’ 之前的位置。
. 匹配除 "n" 之外的任何單個字元。要匹配包括 ‘n’ 在內的任何字元，請使用象 ‘[.n]’ 的模式。
[…] 字符集合。匹配所包含的任意一個字元。例如， ‘[abc]’ 可以匹配 "plain" 中的 ‘a’。
[^…] 負值字符集合。匹配未包含的任意字元。例如， ‘abc’ 可以匹配 "plain" 中的’p’。
p1|p2|p3 匹配 p1 或 p2 或 p3。例如，’z|food’ 能匹配 "z" 或 "food"。'(z|f)ood’ 則匹配 "zood" 或 "food"。
-
匹配前面的子表達式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等價於{0,}。
-
匹配前面的子表達式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等價於 {1,}。
{n} n 是一個非負整數。匹配確定的 n 次。例如，’o{2}’ 不能匹配 "Bob" 中的 ‘o’，但是能匹配 "food" 中的兩個 o。
{n,m} m 和 n 均為非負整數，其中n <= m。最少匹配 n 次且最多匹配 m 次。
例子：
--查找name欄位中以母音字元開頭或以'ok'字元串結尾的所有數據 SELECT name FROM t_developer WHERE name REGEXP '^[aeiou]|ne$'; MySQL 事務
- 在 MySQL 中只有使用了 Innodb 資料庫引擎的資料庫或表才支援事務。
- 事務處理可以用來維護資料庫的完整性，保證成批的 SQL 語句要麼全部執行，要麼全部不執行。
- 事務用來管理 insert,update,delete 語句
一般來說，事務是必須滿足4個條件（ACID）：：原子性（Atomicity，或稱不可分割性）、一致性（Consistency）、隔離性（Isolation，又稱獨立性）、持久性（Durability）。
- 原子性：一個事務（transaction）中的所有操作，要麼全部完成，要麼全部不完成，不會結束在中間某個環節。事務在執行過程中發生錯誤，會被回滾（Rollback）到事務開始前的狀態，就像這個事務從來沒有執行過一樣。
- 一致性：在事務開始之前和事務結束以後，資料庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預設規則，這包含資料的精確度、串聯性以及後續資料庫可以自發性地完成預定的工作。
- 隔離性：資料庫允許多個並發事務同時對其數據進行讀寫和修改的能力，隔離性可以防止多個事務並發執行時由於交叉執行而導致數據的不一致。事務隔離分為不同級別，包括讀未提交（Read uncommitted）、讀提交（read committed）、可重複讀（repeatable read）和串列化（Serializable）。
- 持久性：事務處理結束後，對數據的修改就是永久的，即便系統故障也不會丟失。
在 MySQL 命令行，客戶端的默認設置下，事務都是自動提交的，即執行 SQL 語句後就會馬上執行 COMMIT 操作。因此要顯式地開啟一個事務務須使用命令 BEGIN 或 START TRANSACTION，或者執行命令 SET AUTOCOMMIT=0，用來禁止使用當前會話的自動提交。
事務控制語句：
- BEGIN 或 START TRANSACTION 顯式地開啟一個事務；
- COMMIT 也可以使用 COMMIT WORK，不過二者是等價的。COMMIT 會提交事務，並使已對資料庫進行的所有修改成為永久性的；
- ROLLBACK 也可以使用 ROLLBACK WORK，不過二者是等價的。回滾會結束用戶的事務，並撤銷正在進行的所有未提交的修改；
- SAVEPOINT identifier，SAVEPOINT 允許在事務中創建一個保存點，一個事務中可以有多個 SAVEPOINT；
- RELEASE SAVEPOINT identifier 刪除一個事務的保存點，當沒有指定的保存點時，執行該語句會拋出一個異常；
- ROLLBACK TO identifier 把事務回滾到標記點；
- SET TRANSACTION 用來設置事務的隔離級別。InnoDB 存儲引擎提供事務的隔離級別有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
MYSQL 事務處理主要有兩種方法：
1、用 BEGIN, ROLLBACK, COMMIT來實現
- BEGIN 開始一個事務
- ROLLBACK 事務回滾
- COMMIT 事務確認
2、直接用 SET 來改變 MySQL 的自動提交模式:
-
SET AUTOCOMMIT=0 禁止自動提交
-
SET AUTOCOMMIT=1 開啟自動提交
即使在 AUTOCOMMIT=1 時，只要開啟了begin事務，就不會被影響到，該回滾就回滾。
BEGIN;
INSERT INTO t_developer(name,work_level,position,salary,status,create_emp_id)VALUES(‘守及1’,7,"shanghai",10,
1, 690050);
INSERT INTO t_developer(name,work_level,position,salary,status,create_emp_id)VALUES(‘守集1’,7,"shanghai",10,
1, 690050);COMMIT;
ROLLBACK;
禁止自動提交
SET AUTOCOMMIT=1;
開啟自動提交
SET AUTOCOMMIT=0;
表定義操作DDL
MySQL ALTER命令
當我們需要修改數據表名或者修改數據表欄位時，就需要使用到MySQL ALTER命令 。
----------------------------DDL-------------------------- #查看錶欄位的定義 SHOW COLUMNS FROM t_developer_backup; DESC t_developer_backup; #刪除表欄位 create_time ALTER TABLE t_developer_backup DROP create_time; #最少DDL必須包含欄位名和欄位類型即可。 ALTER TABLE t_developer_backup ADD create_time1 datetime; #推薦使用DDL類型，ADD添加欄位 ALTER TABLE t_developer_backup ADD create_time1 datetime DEFAULT NOW() COMMENT '創建時間'; #可以定義添加欄位的位置，使用 AFTER 欄位名; 或 FIRST ALTER TABLE t_developer_backup ADD modify_time datetime DEFAULT NOW() COMMENT '創建時間' AFTER status; #FIRST 和 AFTER 關鍵字可用於 ADD 與 MODIFY 子句，所以如果你想重置數據表欄位的位置就需要先使用 DROP 刪除欄位然後使用 ADD 來添加欄位並設置位置。 ALTER TABLE t_developer_backup ADD first_time datetime DEFAULT NOW() COMMENT '創建時間' FIRST; #沒有表示 NOT NULL 的都是表示默認可以為NULL的，而沒有加 DEFAULT 1 或者 now()等的則表示沒有默認值，或者說默認是NULL ALTER TABLE t_developer_backup ADD change_time datetime NOT NULL DEFAULT NOW() COMMENT '創建時間'; #MODIFY可以對該欄位修改，追加定義 ALTER TABLE t_developer_backup MODIFY create_time1 date DEFAULT NULL COMMENT '創建日期'; #AFTER 關鍵字也可用於 MODIFY 子句 ALTER TABLE t_developer_backup MODIFY first_time date DEFAULT NULL COMMENT '首次日期' AFTER modify_time; ALTER TABLE t_developer_backup MODIFY first_time date DEFAULT NULL COMMENT '首次日期' FIRST; #修改欄位時，你可以指定是否包含值 或者是否設置默認值。 ALTER TABLE t_developer_backup MODIFY first_time datetime NOT NULL DEFAULT NOW() COMMENT '首次日期' FIRST; #在 CHANGE 關鍵字之後，緊跟著的是你要修改的欄位名，然後指定新欄位名及類型。 ALTER TABLE t_developer_backup CHANGE create_time1 create_time2 datetime NULL ; #如果不修改欄位名則緊跟著的是原欄位名 ALTER TABLE t_developer_backup CHANGE create_time2 create_time2 datetime NULL ; --注意,修改表欄位時報錯：Data truncated for column 'create_time1' at row 1 --這是因為當原來是自動為空且有數據時如果要修改欄位為NOT NULL ,就會報這個錯。 #修改欄位默認值 ALTER TABLE t_developer_backup ALTER first_time SET DEFAULT NULL; #使用MODIFY或者change來修改欄位默認值 欄位名和欄位類型是不可以缺少的，因此如果只是修改默認值還是使用上面的 ALTER TABLE t_developer_backup MODIFY first_time datetime DEFAULT NOW() COMMENT '首次日期1'; #使用 ALTER 命令及 DROP子句來刪除欄位的默認值 ALTER TABLE t_developer_backup ALTER first_time DROP DEFAULT; #查看數據表類型可以使用 SHOW TABLE STATUS 語句,想修改說明TYPE都行。 SHOW TABLE STATUS; #修改數據表類型，可以使用 ALTER 命令及 TYPE 子句來完成 修改表的引擎 ALTER TABLE t_developer_backup ENGINE = MYISAM; #編碼格式 ALTER TABLE t_developer_backup CHARSET = utf16; ALTER TABLE t_developer_backup COMMENT = '員工開發表備份'; ALTER TABLE t_developer_backup AUTO_INCREMENT = 1; #修改數據表的名稱，可以在 ALTER TABLE 語句中使用 RENAME 子句來實現。 ALTER TABLE t_developer_backup RENAME TO t_developer_backup2; ------------------------------------------------ #刪除外鍵約束：keyName是外鍵別名 alter table tableName drop foreign key keyName; #刪除索引 ALTER TABLE t_developer_backup DROP KEY idxName; #添加索引 ALTER TABLE t_developer_backup ADD INDEX idxName(name); --修改欄位的相對位置：這裡name1為想要修改的欄位，type1為該欄位原來類型，first和after二選一，這應該顯而易見，first放在第一位，after放在name2欄位後面 alter table tableName modify name1 type1 first|after name2; MySQL 索引
索引可以大大提高MySQL的檢索速度，就像漢語字典的目錄頁 。
索引分單列索引和組合索引。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是組合索引。組合索引，即一個索引包含多個列。
缺點：雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件。建立索引會佔用磁碟空間的索引文件。
---創建索引格式--------- CREATE INDEX indexName ON mytable(username(length)); CREATE INDEX indexName ON t_developer_backup(first_time) ; --組合索引 ALTER TABLE t_developer ADD INDEX idx_create_info(create_time,create_emp_id); --添加索引，推薦用這種方式 ALTER table tableName ADD INDEX indexName(columnName) ALTER TABLE t_developer ADD INDEX idx_salary(salary); --創建表的時候直接指定 CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) ); DROP INDEX [indexName] ON mytable; #刪除索引 DROP INDEX idx_salary ON t_developer; #刪除索引，推薦，總的來說推薦使用ALTER開頭的命名操作表結構 ALTER TABLE t_developer DROP INDEX idx_salary; #添加唯一索引，不推薦 CREATE UNIQUE INDEX idx_salary ON t_developer(salary); #添加唯一索引，推薦 ALTER TABLE t_developer ADD UNIQUE idx_salary(salary); #創建表的時候直接指定 CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) ); # 該語句添加一個主鍵，這意味著索引值必須是唯一的，且不能為NULL。 ALTER TABLE tbl_name ADD PRIMARY KEY (column_list); 這條語句創建索引的值必須是唯一的（除了NULL外，NULL可能會出現多次）。 ALTER TABLE tbl_name ADD UNIQUE index_name (column_list); 添加普通索引，索引值可出現多次。 ALTER TABLE tbl_name ADD INDEX index_name (column_list); 該語句指定了索引為 FULLTEXT ，用於全文索引。 ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list); #刪除主鍵，注意如果主鍵是AUTO_INCREMENT則刪除不了 ALTER TABLE t_developer DROP PRIMARY KEY; #格式 SHOW INDEX FROM table_name; #顯示索引資訊 SHOW INDEX FROM t_developer; ---------索引效果------------ CREATE TABLE `t_developer` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵', `name` varchar(30) NOT NULL COMMENT '姓名', `work_level` int(6) NOT NULL COMMENT '等級', `position` varchar(30) DEFAULT NULL COMMENT '職位', `salary` decimal(13,2) DEFAULT '0.00' COMMENT '薪水', `status` smallint(2) DEFAULT NULL COMMENT '狀態', `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間', `create_emp_id` bigint(10) NOT NULL COMMENT '創建人', PRIMARY KEY (`id`), KEY `idxCreateInfo` (`create_time`,`create_emp_id`), KEY `idx_create_info` (`create_time`,`create_emp_id`), KEY `idx_salary` (`salary`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='開發員工表'; ALTER TABLE t_developer DROP PRIMARY KEY; 刪除主鍵報錯： Incorrect table definition; there can be only one auto column and it must be defined as a key 1.只能有一個自增列; 2.它必須被定義為鍵(當然，id一般都是作為主鍵使用的) 設為自增的欄位必須是主鍵，而我用的是客戶端，表是批量從SQLServer複製到MySQL的，導致最後MySQL表中主鍵丟失，設置id為主鍵並設為自增後，保存成功，問題解決。 MySQL 臨時表
MySQL 臨時表在我們需要保存一些臨時數據時是非常有用的。臨時表只在當前連接可見，當關閉連接時，Mysql會自動刪除表並釋放所有空間。
創建臨時表就是在創建普通表時的前面加上TEMPORARY 關鍵字。
當我們退出或者切換出當前MySQL會話，換個新的窗口時（在舊窗口還存在），該臨時表是不存在的，再使用 SELECT命令來讀取原先創建的臨時表數據，那你會發現資料庫中沒有該表的存在，因為在你退出時該臨時表已經被銷毀了。
#創建臨時表 CREATE TEMPORARY TABLE t_developer_temporary ( id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵', name VARCHAR(30) NOT NULL COMMENT '姓名', work_level INT(6) NOT NULL COMMENT '等級', position VARCHAR(30) DEFAULT NULL COMMENT '職位', salary DECIMAL(13,2) DEFAULT '0' COMMENT '薪水', status SMALLINT(2) DEFAULT NULL COMMENT '狀態', create_time datetime DEFAULT NOW() COMMENT '創建時間', create_emp_id BIGINT(10) NOT NULL COMMENT '創建人', PRIMARY KEY (id) )ENGINE = INNODB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8 COMMENT '開發員工表'; #插入數據 INSERT INTO t_developer_temporary(name,work_level,position,salary,status,create_emp_id)VALUES('cc',7,"qz",11, 12, 10001); #查詢 SELECT * from t_developer_temporary; #手動刪除臨時表，和普通版刪除一樣 DROP TABLE t_developer_temporary; #用查詢直接創建臨時表的方式，這種也可以用於普通表 CREATE TEMPORARY TABLE t_developer_temporary AS ( SELECT * FROM t_developer LIMIT 0,10000 ); ##備份表名 CREATE TABLE backup_tables.t_demo_20180322 LIKE t_demo; INSERT INTO backup_tables.t_demo_20180322 SELECT * FROM t_demo WHERE order_id ='123123'; #該語句可以查看錶的創建語句，這樣我們就可以直觀地看到表結構或者複製表創建語句 SHOW CREATE TABLE t_developer; #輸出 CREATE TABLE `t_developer` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵', `name` varchar(30) NOT NULL COMMENT '姓名', `work_level` int(6) NOT NULL COMMENT '等級', `position` varchar(30) DEFAULT NULL COMMENT '職位', `salary` decimal(13,2) DEFAULT '0.00' COMMENT '薪水', `status` smallint(2) DEFAULT NULL COMMENT '狀態', `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間', `create_emp_id` bigint(10) NOT NULL COMMENT '創建人', PRIMARY KEY (`id`), KEY `idxCreateInfo` (`create_time`,`create_emp_id`), KEY `idx_create_info` (`create_time`,`create_emp_id`), FULLTEXT KEY `inx_name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='開發員工表'; 獲取伺服器元數據
以下命令語句可以在 MySQL 的命令提示符使用，也可以在腳本中 使用，如PHP腳本。
命令 描述
SELECT VERSION( ) 伺服器版本資訊
SELECT DATABASE( ) 當前資料庫名 (或者返回空)
SELECT USER( ) 當前用戶名
SHOW STATUS 伺服器狀態
SHOW VARIABLES 伺服器配置變數
MySQL 序列使用
MySQL 中最簡單使用序列的方法就是使用 MySQL AUTO_INCREMENT 來定義列。
MySQL的客戶端中你可以使用 SQL中的LAST_INSERT_ID( ) 函數來獲取最後的插入表中的自增列的值。
MySQL 處理重複數據
防止表中出現重複數據
你可以在 MySQL 數據表中設置指定的欄位為
PRIMARY KEY（主鍵） 或者 UNIQUE（唯一） 索引來保證數據的唯一性。
INSERT IGNORE INTO 當插入數據時，在設置了記錄的唯一性後，如果插入重複數據，將不返回錯誤，只以警告形式返回。 而 REPLACE INTO 如果存在 primary 或 unique 相同的記錄，則先刪除掉。再插入新記錄。
統計重複數據
一般用GROUP BY 統計重複數據，用HAVING子句設置重複數大於1。
過濾重複數據
在 SELECT 語句中使用 DISTINCT 關鍵字來過濾重複數據。
刪除重複數據
#先用GROUP BY過濾語句插入一張新表，刪除舊錶，把新表重命名為舊錶名。 CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name, sex); DROP TABLE person_tbl; ALTER TABLE tmp RENAME TO person_tbl; SQL 注入
SQL注入，就是通過把SQL命令插入到Web表單遞交或輸入域名或頁面請求的查詢字元串，最終達到欺騙伺服器執行惡意的SQL命令。
我們永遠不要信任用戶的輸入，我們必須認定用戶輸入的數據都是不安全的，我們都需要對用戶輸入的數據進行過濾處理。
防止SQL注入，我們需要注意以下幾個要點：
- 1.永遠不要信任用戶的輸入。對用戶的輸入進行校驗，可以通過正則表達式，或限制長度；對單引號和 雙"-"進行轉換等。
- 2.永遠不要使用動態拼裝sql，可以使用參數化的sql或者直接使用存儲過程進行數據查詢存取。
- 3.永遠不要使用管理員許可權的資料庫連接，為每個應用使用單獨的許可權有限的資料庫連接。
- 4.不要把機密資訊直接存放，加密或者hash掉密碼和敏感的資訊。
- 5.應用的異常資訊應該給出儘可能少的提示，最好使用自定義的錯誤資訊對原始錯誤資訊進行包裝
- 6.sql注入的檢測方法一般採取輔助軟體或網站平台來檢測，軟體一般採用sql注入檢測工具jsky，網站平台就有億思網站安全平台檢測工具。MDCSOFT SCAN等。採用MDCSOFT-IPS可以有效的防禦SQL注入，XSS攻擊等。
MySQL 導出數據
MySQL中你可以使用SELECT…INTO OUTFILE語句來簡單的導出數據到文本文件上。
#導出普通數據txt格式的。自己指定文件名，前面的路徑要通過secure_file_priv設置 SELECT * from t_developer INTO OUTFILE 'D:/book/text.txt'; #查看secure-file-priv參數的值 show variables like '%secure%'; #導出 CSV 格式 SELECT * FROM t_developer INTO OUTFILE 'D:/book/text1.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'rn'; #也是CSV格式，值分割不一樣，這種格式可以被許多程式使用。 SELECT * INTO OUTFILE 'D:/book/text2.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'n' FROM t_developer; 報錯：The MySQL server is running with the --secure-file-priv option so it cannot execute this statement 答：出現這個錯誤是因為沒有給資料庫指定寫出文件的路徑或者寫出的路徑有問題。這是因為導出的secure_file_priv 位置不是我們系統設置的安全位置，因此我們要修改secure_file_priv對應的安全位置的值。我們進入到（window為例）MySQL數據路徑尋找到my.ini配置文件。 位置： C:ProgramDataMySQLMySQL Server 5.7 修改指定位置 #secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads" secure_file_priv = "D:/book" 重啟mysql服務端即可。 注意路徑保存和sql的路徑位置要用斜杠，反斜杠有點問題。 報錯參考
SELECT … INTO OUTFILE 語句有以下屬性:
- LOAD DATA INFILE是SELECT … INTO OUTFILE的逆操作，SELECT句法。為了將一個資料庫的數據寫入一個文件，使用SELECT … INTO OUTFILE，為了將文件讀回資料庫，使用LOAD DATA INFILE。
- SELECT…INTO OUTFILE ‘file_name’形式的SELECT可以把被選擇的行寫入一個文件中。該文件被創建到伺服器主機上，因此您必須擁有FILE許可權，才能使用此語法。
- 輸出不能是一個已存在的文件。防止文件數據被篡改。
- 你需要有一個登陸伺服器的帳號來檢索文件。否則 SELECT … INTO OUTFILE 不會起任何作用。
- 在UNIX中，該文件被創建後是可讀的，許可權由MySQL伺服器所擁有。這意味著，雖然你就可以讀取該文件，但可能無法將其刪除。
mysqldump 是 mysql 用於轉存儲資料庫的實用程式。它主要產生一個 SQL 腳本，其中包含從頭重新創建資料庫所必需的命令 CREATE TABLE INSERT 等。
使用 mysqldump 導出數據需要使用 –tab 選項來指定導出文件指定的目錄，該目標必須是可寫的。
以下實例將數據表 runoob_tbl 導出到 /tmp 目錄中
#如果不指定導出位置，則默認導出文件在該命令行窗口對應的文件夾位置 #在Windows上則是在cmd.exe對應的位置 C:WindowsSystem32 #導出 SQL 格式的數據到指定文件 mysqldump -u root -p hello_spring t_developer > dump.txt #導出 SQL 格式的數據到指定位置 mysqldump -u root -p hello_spring t_developer > C:/Users/Administrator/Desktop/t_developer_dump.txt #導出整個資料庫的數據 mysqldump -u root -p hello_spring > database_dump.txt #備份所有資料庫 mysqldump -u root -p --all-databases > alldatabase_dump.txt mysqldump -u root -p hello_spring t_developer > dump_backup.txt mysqldump -u root -p hello_spring > dump_backup_all.txt #將備份的資料庫導入到MySQL伺服器中，可以使用以下命令，使用以下命令你需要確認資料庫已經創建：這樣即使mysql數據刪除了也可以用該文件恢復，冷備 mysql -u root -p hello_spring < dump_backup_all.txt #可以直接指定要導出數據的位置 mysqldump -u root -p --all-databases > C:/Users/Administrator/Desktop/database_dump.txt #mysqldump -u root -p hello_spring t_developer > #C:/Users/Administrator/Desktop/t_developer_dump.txt #導出sql文件如下： -- MySQL dump 10.13 Distrib 5.7.17, for Win64 (x86_64) -- -- Host: localhost Database: hello_spring -- ------------------------------------------------------ -- Server version 5.7.17-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `t_developer` -- DROP TABLE IF EXISTS `t_developer`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `t_developer` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵', `name` varchar(30) NOT NULL COMMENT '姓名', `work_level` int(6) NOT NULL COMMENT '等級', `position` varchar(30) DEFAULT NULL COMMENT '職位', `salary` decimal(13,2) DEFAULT '0.00' COMMENT '薪水', `status` smallint(2) DEFAULT NULL COMMENT '狀態', `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間', `create_emp_id` bigint(10) NOT NULL COMMENT '創建人', PRIMARY KEY (`id`), KEY `idxCreateInfo` (`create_time`,`create_emp_id`), KEY `idx_create_info` (`create_time`,`create_emp_id`), FULLTEXT KEY `inx_name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='開發員工表'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `t_developer` -- LOCK TABLES `t_developer` WRITE; /*!40000 ALTER TABLE `t_developer` DISABLE KEYS */; INSERT INTO `t_developer` VALUES (1,'wayne',7,'shanghai',3000.00,1,'2020-03-22 20:43:25',690050),(2,'mike',7,'shanghai',4000.00,1,'2020-03-22 20:43:42',690050),(3,'mike',7,'shanghai',1000.00,1,'2020-03-22 20:43:48',690050),(4,'jhon',7,'shanghai',10000.00,1,'2020-03-22 20:44:09',690050),(5,'jhon',7,'shanghai',1005.00,1,'2020-03-22 20:44:22',690050),(6,'tingting',7,'shanghai',500.00,1,'2020-03-22 20:44:35',690050),(7,'tingting',7,'shanghai',6500.00,1,'2020-03-22 20:44:43',690050),(8,'tingting',7,'shanghai',101.00,1,'2020-03-22 20:45:13',690050),(9,'守及',7,'shanghai',102.00,1,'2020-03-22 23:01:16',690050),(10,'守集',7,'shanghai',104.00,1,'2020-03-22 23:01:17',690050); /*!40000 ALTER TABLE `t_developer` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2020-03-29 17:33:32 MySQL 導入數據
#mysql 命令導入語法格式為： mysql -u用戶名 -p密碼 < 要導入的資料庫數據(runoob.sql) #1、mysql命令導入，在命令行上執行，無需登錄資料庫終端 mysql -u root -p < C:/Users/Administrator/Desktop/database_dump.txt #2、source 命令導入 source命令導入資料庫需要先登錄到數庫終端。 #先執行登錄 mysql -u root -p password: # 創建要導入數據的資料庫 create database hello_spring_backup; # 使用已創建的資料庫 use hello_spring_backup; # 設置編碼 set names utf8; # 導入備份資料庫 source /home/abc/abc.sql #Windows下執行，txt文件也是可以的 source C:/Users/Administrator/Desktop/database_dump.txt #3、使用 LOAD DATA 導入數據，mysql客戶端就可執行的sql語句。 #MySQL 中提供了LOAD DATA INFILE語句來插入數據，如果指定LOCAL關鍵詞，則表明從客戶主機上按路徑讀取文件。如果沒有指定，則文件在伺服器上按路徑讀取文件。 #可以指定LOAD DATA語句中的列值的分隔符和行尾標記，但是默認標記是定位符和換行符。 #執行導入數據sql LOAD DATA LOCAL INFILE 'C:/Users/Administrator/Desktop/t_developer_dump.txt' INTO TABLE t_developer; #也可以指定要導入的數據的分割符類型，比如要導入csv文件數據入資料庫 LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl FIELDS TERMINATED BY ':' LINES TERMINATED BY 'rn'; #LOAD DATA 默認情況下是按照數據文件中列的順序插入數據的，如果數據文件中的列與插入表中的列不一致，則需要指定列的順序。 #如，在數據文件中的列順序是 a,b,c，但在插入表的列順序為b,c,a，則數據導入語法如下： LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl (b, c, a); #4、使用 mysqlimport 導入數據 mysqlimport -u root -p --local C:/Users/Administrator/Desktop/t_developer_dump.txt mysqlimport -u root -p --local --fields-terminated-by=":" --lines-terminated-by="rn" mytbl dump.txt mysqlimport的常用選項介紹
選項 功能
-d or –delete 新數據導入數據表中之前刪除數據數據表中的所有資訊
-f or –force 不管是否遇到錯誤，mysqlimport將強制繼續插入數據
-i or –ignore mysqlimport跳過或者忽略那些有相同唯一 關鍵字的行， 導入文件中的數據將被忽略。
-l or -lock-tables 數據被插入之前鎖住表，這樣就防止了， 你在更新資料庫時，用戶的查詢和更新受到影響。
-r or -replace 這個選項與－i選項的作用相反；此選項將替代 表中有相同唯一關鍵字的記錄。
–fields-enclosed- by= char 指定文本文件中數據的記錄時以什麼括起的， 很多情況下 數據以雙引號括起。 默認的情況下數據是沒有被字元括起的。
–fields-terminated- by=char 指定各個數據的值之間的分隔符，在句號分隔的文件中， 分隔符是句號。您可以用此選項指定數據之間的分隔符。 默認的分隔符是跳格符（Tab）
–lines-terminated- by=str 此選項指定文本文件中行與行之間數據的分隔字元串 或者字元。 默認的情況下mysqlimport以newline為行分隔符。 您可以選擇用一個字元串來替代一個單個的字元： 一個新行或者一個回車。
MySQL 函數
-----------------字元串----------------------- SELECT ASCII(name) AS first_name FROM t_developer; #################sql函數######################### #合併多個字元串 SELECT CONCAT("this",'is','my','destiny','!') as goal; SELECT CONCAT(d.`name`,d.create_time) from t_developer d; #合併多個字元串，並添加分隔符 SELECT CONCAT_WS(' ',"this",'is','my','destiny','!') as goal; #返回字元串 c 在列表值中的位置： SELECT FIELD("c", "a", "b", "c", "d", "e"); #函數可以將數字 x 進行格式化 "#,###.##", 將 x 保留到小數點後 n 位，最後一位四捨五入。 SELECT FORMAT(250500.5634, 2); -- 輸出 250,500.56 #字元串 RUNOOB 轉換為小寫： SELECT LOWER('ALAN') -- alan #將字元串 愛到發燒 重複三次： SELECT REPEAT('愛到發燒',3)； #將字元串 xyz 的順序反過來 SELECT REVERSE('xyz'); #返回 10 個空格： SELECT SPACE(10); #將字元串 runoob 轉換為大寫： SELECT UPPER('china'); -----------------數值----------------------- #返回 x 的絕對值 SELECT ABS(-10); -- 10 #返回 x 的 y 次方 SELECT POW(3,3) -- 27 SELECT POWER(3,3) -- 27 #四捨五入為整數 SELECT ROUND(3.1415); -- 3 SELECT ROUND(3.5415); -- 4 -----------------日期函數----------------------- #計算起始日期 d 加上 n 天的日期 SELECT ADDDATE("2017-06-15", 20); -- 2017-07-05 SELECT ADDDATE("2017-06-15", INTERVAL 20 DAY); -- 2017-07-05 SELECT ADDDATE("2017-02-28", INTERVAL 10 DAY); -- 2017-03-10 #如果日期時間是錯誤的，則會返回null SELECT ADDDATE("2017-02-30", INTERVAL 10 DAY); -- null #如果傳入的間隔時間是小數，則會四捨五入 SELECT ADDDATE("2017-02-28", 10.5); -- 2017-03-11 SELECT ADDDATE("2017-02-28", 10.4); -- 2017-03-10 # 計算起始日期 d 加上一個時間段後的日期 可以是正負值,可以操作從年到秒 #DATE_ADD(d，INTERVAL expr type) # (默認是天) SELECT ADDDATE('2011-11-11 11:11:11',1); #(TYPE的取值與上面那個列出來的函數類似) SELECT ADDDATE('2011-11-11 11:11:11', INTERVAL 5 MINUTE); -- 2011-11-11 11:16:11 SELECT ADDDATE('2011-11-11 11:11:11', INTERVAL 5 YEAR); -- 2016-11-11 11:11:11 SELECT ADDDATE('2011-11-11 11:11:11', INTERVAL 5 SECOND); -- 2011-11-11 11:11:16 #時間 t 加上 n 秒的時間 SELECT ADDTIME("2021-11-11 11:11:11", 5); -- 2021-11-11 11:11:16 #返回當前日期 SELECT CURDATE(); -- 2016-03-29 SELECT CURRENT_DATE(); #返回當前時間 注意是不包含日期的 SELECT CURRENT_TIME(); -- 21:52:59 #返回當前日期和時間 SELECT CURRENT_TIMESTAMP(); -- 2016-03-29 21:53:52 #從日期或日期時間表達式中提取日期值 SELECT DATE("2017-06-15"); #計算日期 d1->d2 之間相隔的天數,相當於是前面減去後面 SELECT DATEDIFF('2017-06-15','2017-06-29'); -- -14 #按表達式 f的要求顯示日期 d #DATE_FORMAT(d,f) SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r') -- 2011-11-11 11:11:11 AM #函數從日期減去指定的時間間隔 SELECT DATE_SUB('2011-11-11 11:11:11', INTERVAL 5 SECOND); -- 2011-11-11 11:11:06 SELECT DATE_SUB('2011-11-11 11:11:11', INTERVAL 5 DAY); -- 2011-11-06 11:11:11 #返回給給定日期的那一月份的最後一天 SELECT LAST_DAY("2017-06-20"); -- 2017-06-30 #返回當前日期和時間 SELECT NOW(); SELECT SYSDATE(); -----------------高級函數----------------------- #返回 x 的二進位編碼 SELECT BIN(20); -- 10100 SELECT * from t_developer; /** CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionN THEN resultN ELSE result END */ #CASE 表示函數開始，END 表示函數結束。 SELECT (CASE WHEN d.salary =10.00 THEN 'low' WHEN d.salary = 100.00 THEN 'mid' WHEN d.salary =1000.00 THEN 'good' when d.salary = 10000.00 THEN 'excellent' END) as title,`name` FROM t_developer d; #返回伺服器的連接數 SELECT CONNECTION_ID(); #返回當前用戶 SELECT CURRENT_USER(); -- root@localhost #返回當前資料庫名 SELECT DATABASE(); -- hello_spring #如果表達式 expr 成立，返回結果 v1；否則，返回結果 v2。 #if 表達式 IF(expr,v1,v2) SELECT IF(d.salary >= 1000,'good','bad') reputation,d.`name` FROM t_developer d; #如果 v1 的值不為 NULL，則返回 v1，否則返回 v2。 # IFNULL(v1,v2) 即給欄位設置默認值 SELECT IFNULL('ha','Hello Word'); #判斷表達式是否為 NULL SELECT ISNULL(NULL); -- 1 SELECT ISNULL('1'); -- 0 #返回最近生成的 AUTO_INCREMENT 值 ,我想知道這個是針對錶還是整個資料庫的AUTO_INCREMENT？ SELECT LAST_INSERT_ID(); #在兩值之間 >=min&&<=max select d.salary FROM t_developer d where d.salary between 100 and 1000; MySQL 字元串函數
函數 描述 實例
ASCII(s) 返回字元串 s 的第一個字元的 ASCII 碼。 返回 CustomerName 欄位第一個字母的 ASCII 碼：SELECT ASCII(CustomerName) AS NumCodeOfFirstChar FROM Customers;
CHAR_LENGTH(s) 返回字元串 s 的字元數 返回字元串 RUNOOB 的字元數SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString;
CHARACTER_LENGTH(s) 返回字元串 s 的字元數 返回字元串 RUNOOB 的字元數SELECT CHARACTER_LENGTH("RUNOOB") AS LengthOfString;
CONCAT(s1,s2…sn) 字元串 s1,s2 等多個字元串合併為一個字元串 合併多個字元串SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString;
CONCAT_WS(x, s1,s2…sn) 同 CONCAT(s1,s2,…) 函數，但是每個字元串之間要加上 x，x 可以是分隔符 合併多個字元串，並添加分隔符：SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString;
FIELD(s,s1,s2…) 返回第一個字元串 s 在字元串列表(s1,s2…)中的位置 返回字元串 c 在列表值中的位置：SELECT FIELD("c", "a", "b", "c", "d", "e");
FIND_IN_SET(s1,s2) 返回在字元串s2中與s1匹配的字元串的位置 返回字元串 c 在指定字元串中的位置：SELECT FIND_IN_SET("c", "a,b,c,d,e");
FORMAT(x,n) 函數可以將數字 x 進行格式化 "#,###.##", 將 x 保留到小數點後 n 位，最後一位四捨五入。 格式化數字 "#,###.##" 形式：SELECT FORMAT(250500.5634, 2); — 輸出 250,500.56
INSERT(s1,x,len,s2) 字元串 s2 替換 s1 的 x 位置開始長度為 len 的字元串 從字元串第一個位置開始的 6 個字元替換為 runoob：SELECT INSERT("google.com", 1, 6, "runnob"); — 輸出：runoob.com
LOCATE(s1,s) 從字元串 s 中獲取 s1 的開始位置 獲取 b 在字元串 abc 中的位置：SELECT LOCATE(‘st’,’myteststring’); — 5返回字元串 abc 中 b 的位置：SELECT LOCATE(‘b’, ‘abc’) — 2
LCASE(s) 將字元串 s 的所有字母變成小寫字母 字元串 RUNOOB 轉換為小寫：SELECT LCASE(‘RUNOOB’) — runoob
LEFT(s,n) 返回字元串 s 的前 n 個字元 返回字元串 runoob 中的前兩個字元：SELECT LEFT(‘runoob’,2) — ru
LOWER(s) 將字元串 s 的所有字母變成小寫字母 字元串 RUNOOB 轉換為小寫：SELECT LOWER(‘RUNOOB’) — runoob
LPAD(s1,len,s2) 在字元串 s1 的開始處填充字元串 s2，使字元串長度達到 len 將字元串 xx 填充到 abc 字元串的開始處：SELECT LPAD(‘abc’,5,’xx’) — xxabc
LTRIM(s) 去掉字元串 s 開始處的空格 去掉字元串 RUNOOB開始處的空格：SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;– RUNOOB
MID(s,n,len) 從字元串 s 的 n 位置截取長度為 len 的子字元串，同 SUBSTRING(s,n,len) 從字元串 RUNOOB 中的第 2 個位置截取 3個 字元：SELECT MID("RUNOOB", 2, 3) AS ExtractString; — UNO
POSITION(s1 IN s) 從字元串 s 中獲取 s1 的開始位置 返回字元串 abc 中 b 的位置：SELECT POSITION(‘b’ in ‘abc’) — 2
REPEAT(s,n) 將字元串 s 重複 n 次 將字元串 runoob 重複三次：SELECT REPEAT(‘runoob’,3) — runoobrunoobrunoob
REPLACE(s,s1,s2) 將字元串 s2 替代字元串 s 中的字元串 s1 將字元串 abc 中的字元 a 替換為字元 x：SELECT REPLACE(‘abc’,’a’,’x’) –xbc
REVERSE(s) 將字元串s的順序反過來 將字元串 abc 的順序反過來：SELECT REVERSE(‘abc’) — cba
RIGHT(s,n) 返回字元串 s 的後 n 個字元 返回字元串 runoob 的後兩個字元：SELECT RIGHT(‘runoob’,2) — ob
RPAD(s1,len,s2) 在字元串 s1 的結尾處添加字元串 s2，使字元串的長度達到 len 將字元串 xx 填充到 abc 字元串的結尾處：SELECT RPAD(‘abc’,5,’xx’) — abcxx
RTRIM(s) 去掉字元串 s 結尾處的空格 去掉字元串 RUNOOB 的末尾空格：SELECT RTRIM("RUNOOB ") AS RightTrimmedString; — RUNOOB
SPACE(n) 返回 n 個空格 返回 10 個空格：SELECT SPACE(10);
STRCMP(s1,s2) 比較字元串 s1 和 s2，如果 s1 與 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1 比較字元串：SELECT STRCMP("runoob", "runoob"); — 0
SUBSTR(s, start, length) 從字元串 s 的 start 位置截取長度為 length 的子字元串 從字元串 RUNOOB 中的第 2 個位置截取 3個 字元：SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; — UNO
SUBSTRING(s, start, length) 從字元串 s 的 start 位置截取長度為 length 的子字元串 從字元串 RUNOOB 中的第 2 個位置截取 3個 字元：SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString; — UNO
SUBSTRING_INDEX(s, delimiter, number) 返回從字元串 s 的第 number 個出現的分隔符 delimiter 之後的子串。 如果 number 是正數，返回第 number 個字元左邊的字元串。 如果 number 是負數，返回第(number 的絕對值(從右邊數))個字元右邊的字元串。 SELECT SUBSTRING_INDEX(‘ab’,’‘,1) — a SELECT SUBSTRING_INDEX(‘ab’,’‘,-1) — b SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(‘abcde’,’‘,3),’‘,-1) — c
TRIM(s) 去掉字元串 s 開始和結尾處的空格 去掉字元串 RUNOOB 的首尾空格：SELECT TRIM(‘ RUNOOB ‘) AS TrimmedString;
UCASE(s) 將字元串轉換為大寫 將字元串 runoob 轉換為大寫：SELECT UCASE("runoob"); — RUNOOB
UPPER(s) 將字元串轉換為大寫 將字元串 runoob 轉換為大寫：SELECT UPPER("runoob"); — RUNOOB
MySQL 數字函數
函數名 描述 實例
ABS(x) 返回 x 的絕對值 返回 -1 的絕對值：SELECT ABS(-1) — 返回1
ACOS(x) 求 x 的反餘弦值(參數是弧度) SELECT ACOS(0.25);
ASIN(x) 求反正弦值(參數是弧度) SELECT ASIN(0.25);
ATAN(x) 求反正切值(參數是弧度) SELECT ATAN(2.5);
ATAN2(n, m) 求反正切值(參數是弧度) SELECT ATAN2(-0.8, 2);
AVG(expression) 返回一個表達式的平均值，expression 是一個欄位 返回 Products 表中Price 欄位的平均值：SELECT AVG(Price) AS AveragePrice FROM Products;
CEIL(x) 返回大於或等於 x 的最小整數 SELECT CEIL(1.5) — 返回2
CEILING(x) 返回大於或等於 x 的最小整數 SELECT CEIL(1.5) — 返回2
COS(x) 求餘弦值(參數是弧度) SELECT COS(2);
COT(x) 求餘切值(參數是弧度) SELECT COT(6);
COUNT(expression) 返回查詢的記錄總數，expression 參數是一個欄位或者 * 號 返回 Products 表中 products 欄位總共有多少條記錄：SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
DEGREES(x) 將弧度轉換為角度 SELECT DEGREES(3.1415926535898) — 180
n DIV m 整除，n 為被除數，m 為除數 計算 10 除於 5：SELECT 10 DIV 5; — 2
EXP(x) 返回 e 的 x 次方 計算 e 的三次方：SELECT EXP(3) — 20.085536923188
FLOOR(x) 返回小於或等於 x 的最大整數 小於或等於 1.5 的整數：SELECT FLOOR(1.5) — 返回1
GREATEST(expr1, expr2, expr3, …) 返回列表中的最大值 返回以下數字列表中的最大值：SELECT GREATEST(3, 12, 34, 8, 25); — 34返回以下字元串列表中的最大值：SELECT GREATEST("Google", "Runoob", "Apple"); — Runoob
LEAST(expr1, expr2, expr3, …) 返回列表中的最小值 返回以下數字列表中的最小值：SELECT LEAST(3, 12, 34, 8, 25); — 3返回以下字元串列表中的最小值：SELECT LEAST("Google", "Runoob", "Apple"); — Apple
LN 返回數字的自然對數 返回 2 的自然對數：SELECT LN(2); — 0.6931471805599453
LOG(x) 返回自然對數(以 e 為底的對數) SELECT LOG(20.085536923188) — 3
LOG10(x) 返回以 10 為底的對數 SELECT LOG10(100) — 2
LOG2(x) 返回以 2 為底的對數 返回以 2 為底 6 的對數：SELECT LOG2(6); — 2.584962500721156
MAX(expression) 返回欄位 expression 中的最大值 返回數據表 Products 中欄位 Price 的最大值：SELECT MAX(Price) AS LargestPrice FROM Products;
MIN(expression) 返回欄位 expression 中的最小值 返回數據表 Products 中欄位 Price 的最小值：SELECT MIN(Price) AS LargestPrice FROM Products;
MOD(x,y) 返回 x 除以 y 以後的餘數 5 除於 2 的餘數：SELECT MOD(5,2) — 1
PI() 返回圓周率(3.141593） SELECT PI() –3.141593
POW(x,y) 返回 x 的 y 次方 2 的 3 次方：SELECT POW(2,3) — 8
POWER(x,y) 返回 x 的 y 次方 2 的 3 次方：SELECT POWER(2,3) — 8
RADIANS(x) 將角度轉換為弧度 180 度轉換為弧度：SELECT RADIANS(180) — 3.1415926535898
RAND() 返回 0 到 1 的隨機數 SELECT RAND() –0.93099315644334
ROUND(x) 返回離 x 最近的整數 SELECT ROUND(1.23456) –1
SIGN(x) 返回 x 的符號，x 是負數、0、正數分別返回 -1、0 和 1 SELECT SIGN(-10) — (-1)
SIN(x) 求正弦值(參數是弧度) SELECT SIN(RADIANS(30)) — 0.5
SQRT(x) 返回x的平方根 25 的平方根：SELECT SQRT(25) — 5
SUM(expression) 返回指定欄位的總和 計算 OrderDetails 表中欄位 Quantity 的總和：SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
TAN(x) 求正切值(參數是弧度) SELECT TAN(1.75); — -5.52037992250933
TRUNCATE(x,y) 返回數值 x 保留到小數點後 y 位的值（與 ROUND 最大的區別是不會進行四捨五入） SELECT TRUNCATE(1.23456,3) — 1.234
MySQL 日期函數
函數名 描述 實例
ADDDATE(d,n) 計算起始日期 d 加上 n 天的日期 SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY); ->2017-06-25
ADDTIME(t,n) 時間 t 加上 n 秒的時間 SELECT ADDTIME(‘2011-11-11 11:11:11’, 5) ->2011-11-11 11:11:16 (秒)
CURDATE() 返回當前日期 SELECT CURDATE(); -> 2018-09-19
CURRENT_DATE() 返回當前日期 SELECT CURRENT_DATE(); -> 2018-09-19
CURRENT_TIME 返回當前時間 SELECT CURRENT_TIME(); -> 19:59:02
CURRENT_TIMESTAMP() 返回當前日期和時間 SELECT CURRENT_TIMESTAMP() -> 2018-09-19 20:57:43
CURTIME() 返回當前時間 SELECT CURTIME(); -> 19:59:02
DATE() 從日期或日期時間表達式中提取日期值 SELECT DATE("2017-06-15"); -> 2017-06-15
DATEDIFF(d1,d2) 計算日期 d1->d2 之間相隔的天數 SELECT DATEDIFF(‘2001-01-01′,’2001-02-02’) -> -32
DATE_ADD(d，INTERVAL expr type) 計算起始日期 d 加上一個時間段後的日期 SELECT ADDDATE(‘2011-11-11 11:11:11’,1) -> 2011-11-12 11:11:11 (默認是天) SELECT ADDDATE(‘2011-11-11 11:11:11’, INTERVAL 5 MINUTE) -> 2011-11-11 11:16:11 (TYPE的取值與上面那個列出來的函數類似)
DATE_FORMAT(d,f) 按表達式 f的要求顯示日期 d SELECT DATE_FORMAT(‘2011-11-11 11:11:11′,’%Y-%m-%d %r’) -> 2011-11-11 11:11:11 AM
DATE_SUB(date,INTERVAL expr type) 函數從日期減去指定的時間間隔。 Orders 表中 OrderDate 欄位減去 2 天：SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders
DAY(d) 返回日期值 d 的日期部分 SELECT DAY("2017-06-15"); -> 15
DAYNAME(d) 返回日期 d 是星期幾，如 Monday,Tuesday SELECT DAYNAME(‘2011-11-11 11:11:11’) ->Friday
DAYOFMONTH(d) 計算日期 d 是本月的第幾天 SELECT DAYOFMONTH(‘2011-11-11 11:11:11’) ->11
DAYOFWEEK(d) 日期 d 今天是星期幾，1 星期日，2 星期一，以此類推 SELECT DAYOFWEEK(‘2011-11-11 11:11:11’) ->6
DAYOFYEAR(d) 計算日期 d 是本年的第幾天 SELECT DAYOFYEAR(‘2011-11-11 11:11:11’) ->315
EXTRACT(type FROM d) 從日期 d 中獲取指定的值，type 指定返回的值。 type可取值為： MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARSECOND_MICROSECONDMINUTE_MICROSECONDMINUTE_SECONDHOUR_MICROSECONDHOUR_SECONDHOUR_MINUTEDAY_MICROSECONDDAY_SECONDDAY_MINUTEDAY_HOURYEAR_MONTH SELECT EXTRACT(MINUTE FROM ‘2011-11-11 11:11:11’) -> 11
FROM_DAYS(n) 計算從 0000 年 1 月 1 日開始 n 天后的日期 SELECT FROM_DAYS(1111) -> 0003-01-16
HOUR(t) 返回 t 中的小時值 SELECT HOUR(‘1:2:3’) -> 1
LAST_DAY(d) 返回給給定日期的那一月份的最後一天 SELECT LAST_DAY("2017-06-20"); -> 2017-06-30
LOCALTIME() 返回當前日期和時間 SELECT LOCALTIME() -> 2018-09-19 20:57:43
LOCALTIMESTAMP() 返回當前日期和時間 SELECT LOCALTIMESTAMP() -> 2018-09-19 20:57:43
MAKEDATE(year, day-of-year) 基於給定參數年份 year 和所在年中的天數序號 day-of-year 返回一個日期 SELECT MAKEDATE(2017, 3); -> 2017-01-03
MAKETIME(hour, minute, second) 組合時間，參數分別為小時、分鐘、秒 SELECT MAKETIME(11, 35, 4); -> 11:35:04
MICROSECOND(date) 返回日期參數所對應的微秒數 SELECT MICROSECOND("2017-06-20 09:34:00.000023"); -> 23
MINUTE(t) 返回 t 中的分鐘值 SELECT MINUTE(‘1:2:3’) -> 2
MONTHNAME(d) 返回日期當中的月份名稱，如 November SELECT MONTHNAME(‘2011-11-11 11:11:11’) -> November
MONTH(d) 返回日期d中的月份值，1 到 12 SELECT MONTH(‘2011-11-11 11:11:11’) ->11
NOW() 返回當前日期和時間 SELECT NOW() -> 2018-09-19 20:57:43
PERIOD_ADD(period, number) 為 年-月 組合日期添加一個時段 SELECT PERIOD_ADD(201703, 5); -> 201708
PERIOD_DIFF(period1, period2) 返回兩個時段之間的月份差值 SELECT PERIOD_DIFF(201710, 201703); -> 7
QUARTER(d) 返回日期d是第幾季節，返回 1 到 4 SELECT QUARTER(‘2011-11-11 11:11:11’) -> 4
SECOND(t) 返回 t 中的秒鐘值 SELECT SECOND(‘1:2:3’) -> 3
SEC_TO_TIME(s) 將以秒為單位的時間 s 轉換為時分秒的格式 SELECT SEC_TO_TIME(4320) -> 01:12:00
STR_TO_DATE(string, format_mask) 將字元串轉變為日期 SELECT STR_TO_DATE("August 10 2017", "%M %d %Y"); -> 2017-08-10
SUBDATE(d,n) 日期 d 減去 n 天后的日期 SELECT SUBDATE(‘2011-11-11 11:11:11’, 1) ->2011-11-10 11:11:11 (默認是天)
SUBTIME(t,n) 時間 t 減去 n 秒的時間 SELECT SUBTIME(‘2011-11-11 11:11:11’, 5) ->2011-11-11 11:11:06 (秒)
SYSDATE() 返回當前日期和時間 SELECT SYSDATE() -> 2018-09-19 20:57:43
TIME(expression) 提取傳入表達式的時間部分 SELECT TIME("19:30:10"); -> 19:30:10
TIME_FORMAT(t,f) 按表達式 f 的要求顯示時間 t SELECT TIME_FORMAT(’11:11:11′,’%r’) 11:11:11 AM
TIME_TO_SEC(t) 將時間 t 轉換為秒 SELECT TIME_TO_SEC(‘1:12:00’) -> 4320
TIMEDIFF(time1, time2) 計算時間差值 SELECT TIMEDIFF("13:10:11", "13:10:10"); -> 00:00:01
TIMESTAMP(expression, interval) 單個參數時，函數返回日期或日期時間表達式；有2個參數時，將參數加和 SELECT TIMESTAMP("2017-07-23", "13:10:11"); -> 2017-07-23 13:10:11
TO_DAYS(d) 計算日期 d 距離 0000 年 1 月 1 日的天數 SELECT TO_DAYS(‘0001-01-01 01:01:01’) -> 366
WEEK(d) 計算日期 d 是本年的第幾個星期，範圍是 0 到 53 SELECT WEEK(‘2011-11-11 11:11:11’) -> 45
WEEKDAY(d) 日期 d 是星期幾，0 表示星期一，1 表示星期二 SELECT WEEKDAY("2017-06-15"); -> 3
WEEKOFYEAR(d) 計算日期 d 是本年的第幾個星期，範圍是 0 到 53 SELECT WEEKOFYEAR(‘2011-11-11 11:11:11’) -> 45
YEAR(d) 返回年份 SELECT YEAR("2017-06-15"); -> 2017
YEARWEEK(date, mode) 返回年份及第幾周（0到53），mode 中 0 表示周天，1表示周一，以此類推 SELECT YEARWEEK("2017-06-15"); -> 201724
MySQL 高級函數
函數名 描述 實例
BIN(x) 返回 x 的二進位編碼 15 的 2 進位編碼:SELECT BIN(15); — 1111
BINARY(s) 將字元串 s 轉換為二進位字元串 SELECT BINARY "RUNOOB"; -> RUNOOB
CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 … WHEN conditionN THEN resultN ELSE result END CASE 表示函數開始，END 表示函數結束。如果 condition1 成立，則返回 result1, 如果 condition2 成立，則返回 result2，當全部不成立則返回 result，而當有一個成立之後，後面的就不執行了。 SELECT CASE WHEN 1 > 0 THEN ‘1 > 0’ WHEN 2 > 0 THEN ‘2 > 0’ ELSE ‘3 > 0’ END ->1 > 0
CAST(x AS type) 轉換數據類型 字元串日期轉換為日期：SELECT CAST("2017-08-29" AS DATE); -> 2017-08-29
COALESCE(expr1, expr2, …., expr_n) 返回參數中的第一個非空表達式（從左向右） SELECT COALESCE(NULL, NULL, NULL, ‘runoob.com’, NULL, ‘google.com’); -> runoob.com
CONNECTION_ID() 返回伺服器的連接數 SELECT CONNECTION_ID(); -> 4292835
CONV(x,f1,f2) 返回 f1 進位數變成 f2 進位數 SELECT CONV(15, 10, 2); -> 1111
CONVERT(s USING cs) 函數將字元串 s 的字符集變成 cs SELECT CHARSET(‘ABC’) ->utf-8 SELECT CHARSET(CONVERT(‘ABC’ USING gbk)) ->gbk
CURRENT_USER() 返回當前用戶 SELECT CURRENT_USER(); -> guest@%
DATABASE() 返回當前資料庫名 SELECT DATABASE(); -> runoob
IF(expr,v1,v2) 如果表達式 expr 成立，返回結果 v1；否則，返回結果 v2。 SELECT IF(1 > 0,’正確’,’錯誤’) ->正確
IFNULL(v1,v2) 如果 v1 的值不為 NULL，則返回 v1，否則返回 v2。 SELECT IFNULL(null,’Hello Word’) ->Hello Word
ISNULL(expression) 判斷表達式是否為 NULL SELECT ISNULL(NULL); ->1
LAST_INSERT_ID() 返回最近生成的 AUTO_INCREMENT 值 SELECT LAST_INSERT_ID(); ->6
NULLIF(expr1, expr2) 比較兩個字元串，如果字元串 expr1 與 expr2 相等 返回 NULL，否則返回 expr1 SELECT NULLIF(25, 25); ->
SESSION_USER() 返回當前用戶 SELECT SESSION_USER(); -> guest@%
SYSTEM_USER() 返回當前用戶 SELECT SYSTEM_USER(); -> guest@%
USER() 返回當前用戶 SELECT USER(); -> guest@%
VERSION() 返回資料庫的版本號 SELECT VERSION() -> 5.6.34
MySQL 運算符
符號 描述 備註
= 等於
<>, != 不等於
大於
< 小於
<= 小於等於
= 大於等於
BETWEEN 在兩值之間 >=min&&<=max
NOT BETWEEN 不在兩值之間
IN 在集合中
NOT IN 不在集合中
<=> 嚴格比較兩個NULL值是否相等 兩個操作碼均為NULL時，其所得值為1；而當一個操作碼為NULL時，其所得值為0
LIKE 模糊匹配
REGEXP 或 RLIKE 正則式匹配
IS NULL 為空
IS NOT NULL 不為空
疑問：
Q:CHAR 和 VARCHAR 類型類似，但它們保存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。在存儲或檢索過程中不進行大小寫轉換。
A:經過驗證，檢索過程中是大小寫不敏感的，mysql會把無論是大寫或者小寫的值匹配的都檢索出來。
Q: SELECT LAST_INSERT_ID();返回最近生成的 AUTO_INCREMENT 值 ,我想知道這個是針對錶還是整個資料庫的AUTO_INCREMENT？
擴展閱讀
postgresql和mysql哪個好？
postgresql和mysql都是免費且功能強大的開源資料庫，很多用戶面對這兩個庫都會有一個問題，那就是哪一個才是最好的開源資料庫，MySQL還是PostgreSQL呢？該選擇哪一個開源資料庫呢？
一.PostgreSQL相對於MySQL的優勢
1、在SQL的標準實現上要比MySQL完善，而且功能實現比較嚴謹；
2、存儲過程的功能支援要比MySQL好，具備本地快取執行計劃的能力；
3、對錶連接支援較完整，優化器的功能較完整，支援的索引類型很多，複雜查詢能力較強；
4、postgresql主表採用堆表存放，MySQL採用索引組織表，能夠支援比MySQL更大的數據量。
5、postgresql的主備複製屬於物理複製，相對於MySQL基於binlog的邏輯複製，數據的一致性更加可靠，複製性能更高，對主機性能的影響也更小。
6、MySQL的存儲引擎插件化機制，存在鎖機制複雜影響並發的問題，而postgresql不存在。
二、MySQL相對於PostgreSQL的優勢：
1、innodb的基於回滾段實現的MVCC機制，相對PG新老數據一起存放的基於XID的MVCC機制，是佔優的。新老數據一起存放，需要定時觸發VACUUM，會帶來多餘的IO和資料庫對象加鎖開銷，引起資料庫整體的並發能力下降。而且VACUUM清理不及時，還可能會引發數據膨脹；
2、MySQL採用索引組織表，這種存儲方式非常適合基於主鍵匹配的查詢、刪改操作，但是對錶結構設計存在約束；
3、MySQL的優化器較簡單，系統表、運算符、數據類型的實現都很精簡，非常適合簡單的查詢操作；
4、MySQL分區表的實現要優於PG的基於繼承表的分區實現，主要體現在分區個數達到上千上萬後的處理性能差異較大。
5、MySQL的存儲引擎插件化機制，使得它的應用場景更加廣泛，比如除了innodb適合事務處理場景外，myisam適合靜態數據的查詢場景。
三、結論
總的來說，開源資料庫都還不是很完善，和這兩者相比，商業資料庫oracle無論在架構還是功能方面都要完善很多。
postgresql和mysql相比，postgresql更加適合嚴格的企業應用場景（比如金融、電信、ERP、CRM），而MySQL則是更加適合業務邏輯相對簡單、對數據可靠性要求比較低的互聯網場景（比如google、facebook、alibaba）。
