啟動(dòng)/停止/重啟MySQL服務(wù)
-
啟動(dòng)MySQL服務(wù)：
mysql.server start -
停止MySQL服務(wù):
mysql.server stop -
重啟MySQL服務(wù):
mysql.server restart -
查看版本號(hào)：
mysql -V / mysql --version -
登錄退出：
mysql -u root -p //登錄 exit/quit //退出
MySQL 語句的規(guī)范
- 關(guān)鍵字和函數(shù)名稱全部大寫
- 數(shù)據(jù)庫(kù)名稱、表名稱、字段名稱全部小寫
- SQL 語句必須以分號(hào)結(jié)尾
操作數(shù)據(jù)庫(kù)
-
創(chuàng)建數(shù)據(jù)庫(kù)
CREATE DATABASE db_test; //創(chuàng)建名稱為tb_test的數(shù)據(jù)庫(kù) CREATE DATABASE IF NOT EXISTS db_test; //如果該數(shù)據(jù)庫(kù)已存在，則忽略警告信息 CREATE DATABASE IF NOT EXISTS db_test CHARACTER SET GBK; //創(chuàng)建時(shí)候指定字符編碼 -
查看警告信息
SHOW WARNINGS ; -
查看當(dāng)前服務(wù)器數(shù)據(jù)庫(kù)列表
SHOW DATABASES; -
查看數(shù)據(jù)庫(kù)創(chuàng)建方式
SHOW CREATE DATABASE db_test; -
修改數(shù)據(jù)庫(kù) -編碼字符集
ALTER DATABASE db_test CHARACTER SET=utf8; -
從刪庫(kù)到跑路
DROP DATABASE db_test;
-
使用數(shù)據(jù)庫(kù)
USE db_test; //db_test為數(shù)據(jù)庫(kù)名稱 -
查看當(dāng)前打開 的數(shù)據(jù)庫(kù)名稱
SELECT DATABASE();
數(shù)據(jù)類型
-
整型
TINYINT： 1字節(jié) SMALLINT： 2字節(jié) MEDIUMINT： 3字節(jié) INT： 4字節(jié) BIGINT： 8字節(jié)以上取值范圍從小到大。
-
浮點(diǎn)型
FLOAT 【M,D】 ： 單精度，M是數(shù)字總位數(shù)，D是小數(shù)點(diǎn)后面位數(shù)，精確到大約7位小數(shù)位 DOUBLE【M,D】 ： 雙精度 -
日期時(shí)間型
YEAR： 年份，默認(rèn)4位也可存兩位，可以允許70~69（1970~2069之間）1 TIME: -8385959~8385959之間的一個(gè)時(shí)間類型 3 DATE： 存儲(chǔ)日期范圍，支持范圍：1000年的1月1號(hào)到9999年的12月31號(hào)之間的日期 3 DATETIME： 日期時(shí)間類型，同DATE范圍,多了時(shí)分秒 8 TIMESTAMP：時(shí)間戳類型，1970年1月1號(hào)0點(diǎn)起到2037年的一個(gè)值 4 -
字符型
CHAR（M）：定長(zhǎng)字符，M個(gè)字節(jié)，0 <=M <=255
VARCHAR :變長(zhǎng)字符，
操作數(shù)據(jù)表
-
創(chuàng)建表
CREATE TABLE tb_test( user_name varchar(20), age TINYINT UNSIGNED, -- UNSIGNED表示無符號(hào)位 salary FLOAT(8,2) UNSIGNED -- （8，2）表示最多八位，小數(shù)點(diǎn)后2位 窮逼 T_T ); -
查看表列表
SHOW TABLES; //查看當(dāng)前數(shù)據(jù)庫(kù)表 SHOW TABLES FROM mysql; //查看指定數(shù)據(jù)庫(kù)表 -
查看表結(jié)構(gòu)
DESC girl; SHOW COLUMNS FROM girl; -
增加記錄
INSERT INTO girl VALUES(13,10,'E'); //省略掉列名的話 所有字段都要賦值 INSERT girl (id,age) VALUES(808,99); //一部分賦值 -
查找記錄
SELECT * FROM girl; //查詢girl表下所有字段 -
空值和非空
NULL：字段值可以為空，不指定話默認(rèn) NOT NULL：字段值禁止為空 CREATE TABLE tb2( user_name VARCHAR(20) NOT NULL, -- 不允許為空 age TINYINT UNSIGNED NULL ); 當(dāng) 使用 INSERT INTO tb2 VALUES(NULL,16); 插入時(shí)候會(huì)報(bào)錯(cuò)： Column 'user_name' cannot be null -
自動(dòng)編號(hào) AUTO_INCREMENT
- 自動(dòng)編號(hào)，必須和主鍵組合使用
- 默認(rèn)起始值為1，每次增量為1（保證記錄唯一性，不會(huì)重復(fù)）
-
主鍵 PRIMARY KEY（非空+唯一）
- 主鍵保證記錄的唯一性
- 主鍵自動(dòng)為 NOT NULL
- 不一定和 AUTO_INCREMENT 一起使用
- 每張數(shù)據(jù)表只能存在一個(gè)主鍵
-
唯一約束（UNIQUE KEY）
- 唯一約束
- 保證記錄唯一性
- 字段可以為空值（NULL）
- 每張數(shù)據(jù)表可以存在多個(gè)唯一約束
-
默認(rèn)值約束
默認(rèn)值，當(dāng)插入記錄時(shí)，如果沒有明確為字段賦值，則自動(dòng)賦予默認(rèn)值。create table tb_test ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, user_name VARCHAR(20) NOT null unique key, sex enum('1','2','3') DEFAULT '3' ); //插入時(shí)不指定sex的值 ，會(huì)默認(rèn)Wie‘3’ insert tb5(user_name) values("Tom");
約束
約束：
- 約束保證數(shù)據(jù)的完整性和一致性
- 約束分為表級(jí)約束和列級(jí)約束
- 約束類型包括：
NOT NULL （非空約束）
PRIMARY KEY （主鍵約束）
UNIQUE KEY （唯一約束）
DEFAULT （默認(rèn)值約束）
FOREIGN KEY （外鍵約束）
-
外鍵約束
作用：保證數(shù)據(jù)一致性，完整性；實(shí)現(xiàn)一對(duì)一或多對(duì)多關(guān)系。
要求：
1. 父表和子表必須使用相同的存儲(chǔ)引擎，而且禁止使用臨時(shí)表。
2. 數(shù)據(jù)表的存儲(chǔ)引擎只能為InnoDB。
3. 外鍵列和參照列必須具有相似的數(shù)據(jù)類型。其中數(shù)字的長(zhǎng)度或是否有符號(hào)位必須相同；而字符的長(zhǎng)度則可以不同。
4. 外鍵列和參照列必須創(chuàng)建索引。主鍵在創(chuàng)建的同時(shí)會(huì)自動(dòng)創(chuàng)建索引，所以如果參照列為主鍵的話，則會(huì)自動(dòng)創(chuàng)建索引（一般參照列就是作為主鍵存在）；而如果參照列不是主鍵而又不存在索引的話，MySQL不會(huì)自動(dòng)創(chuàng)建索引；
-
外鍵列不存在索引的話，MySQL會(huì)自動(dòng)創(chuàng)建索引。
-- 父表(子表所參照的表叫父表) create table provinces( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -- 參照列（主鍵在創(chuàng)建的同時(shí)會(huì)自動(dòng)創(chuàng)建索引，所以參照列其實(shí)已經(jīng)有了索引） p_name VARCHAR(20) NOT NULL ); -- 子表(有外鍵的表稱為子表) create Table users( id smallint UNSIGNED PRIMARY KEY AUTO_INCREMENT, username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED, -- 外鍵列（外鍵列上沒有創(chuàng)建索引，Mysql則會(huì)自動(dòng)創(chuàng)建索引） FOREIGN KEY (pid) REFERENCES provinces (id) ); -- 查看某張表的索引 SHOW INDEX FROM provinces;
-
外鍵約束的參照操作
- CASECADE:從父表刪除或更新且自動(dòng)刪除或更新子表中匹配的行
- SET NULL：從父表刪除或更新行，并設(shè)置子表中的外鍵列為 NULL；如果使用該選項(xiàng)，必須保證子表列沒有指定NOT NULL
- RESTRICT：拒絕對(duì)父表的刪除和更新操作
- NO ACTION：標(biāo)準(zhǔn) SQL 的關(guān)鍵字，在 MySQL 中與 RESTRICT 相同
在實(shí)際開放中，很少使用物理外鍵約束，很多都去使用邏輯的外鍵約束，因?yàn)槲锢淼耐怄I約束只有InnoDB這種引擎才得以支持；邏輯外鍵就是指：在定義兩張表的結(jié)構(gòu)的時(shí)候，我們是按照存在著某種結(jié)構(gòu)的方式去定義，但是不去使用 FOREIGN KEY 這個(gè)關(guān)鍵字
- 表級(jí)約束和列級(jí)約束 （按參照數(shù)目劃分）
- 表級(jí)約束：對(duì)一個(gè)數(shù)據(jù)列定義的約束
- 表級(jí)約束：對(duì)多個(gè)數(shù)據(jù)列定義的約束
- 列級(jí)約束即可以在列定義時(shí)聲明，也可以在列定以后聲明，表級(jí)約束只能在列定義后說明
修改數(shù)據(jù)表
-
添加刪除列：
-- 添加一列 默認(rèn)值添加在最后面 ALTER TABLE users ADD COLUMN age TINYINT UNSIGNED NOT NULL DEFAULT 10; -- 添加一列 添加到 username 字段后面 默認(rèn)值為'龜孫子' ALTER TABLE users ADD COLUMN sex VARCHAR(20) NOT NULL DEFAULT '龜孫子' AFTER username; -- 添加一列 到第一列 ALTER TABLE users ADD COLUMN true_name VARCHAR(20) NOT NULL DEFAULT '超人' FIRST; -- 添加多列 不能指定位置關(guān)系，只能在原來數(shù)據(jù)表列的下方 -- 刪除一列 ALTER TABLE users DROP COLUMN true_name; -- 刪除多列 ALTER TABLE users DROP COLUMN sex,DROP COLUMN age -
添加、刪除約束
-- 添加主鍵約束 ALTER TABLE user2 ADD COLUMN id SMALLINT UNSIGNED; -- 添加id字段 ALTER TABLE user2 ADD CONSTRAINT PK_user2_id PRIMARY KEY(id); -- 將id字段設(shè)置為主鍵 -- 添加唯一約束 ALTER TABLE user2 ADD UNIQUE(username) -- 添加外鍵約束 ALTER TABLE user2 ADD FOREIGN KEY (pid) REFERENCES provinces (id); -- 添加或刪除默認(rèn)約束 -- 設(shè)置age字段默認(rèn)值約束=15 ALTER TABLE user2 ALTER COLUMN age SET DEFAULT 15 -- 刪除默認(rèn)值 ALTER TABLE user2 ALTER COLUMN age DROP DEFAULT -- 刪除主鍵約束 ALTER TABLE user2 DROP PRIMARY KEY ; -- 刪除唯一約束 show INDEXES from user2; -- 先查看指定約束 ALTER TABLE user2 DROP INDEX username; -- 再根據(jù)約束名稱刪除指定約束 -- 刪除外鍵約束 ALTER TABLE user2 DROP FOREIGN KEY user2_ibfk_1; 修改列定義和更名數(shù)據(jù)表
-- 修改列定義 位置放到第一個(gè) （注意大類型改為小類型會(huì)精度丟失）
ALTER TABLE user2 MODIFY COLUMN id SMALLINT UNSIGNED NOT NULL FIRST;
-- 修改列名稱 id改為user_id，類型改為INT
ALTER TABLE user2 CHANGE COLUMN id user_id INT UNSIGNED NOT NULL ;
-- 修改數(shù)據(jù)表的名稱 為 user3
ALTER TABLE user2 RENAME user3;
-- 修改數(shù)據(jù)表的名稱 為 user2
RENAME TABLE user3 TO user2;
-
插入記錄 INSERT
INSERT person VALUES(NULL,'tom','123',16,1); -- id 為主鍵可用 null 或 default 替代 INSERT person VALUES(NULL,'tom',MD5('123'),17,0) -- MD5加密存儲(chǔ) INSERT person VALUES(NULL,'tom',MD5('123'),10*3-9,0); -- 表達(dá)式 INSERT person VALUES(NULL,'jerry','54321',17,1),(DEFAULT,'MARI','1993',18,0); -- 插入多個(gè) 第二種方式：可以使用子查詢 INSERT person SET user_name= 'Ben' , pass_word ='123456' ; -
單表更新記錄 UPDATE
-- 更新 person 表 age 字段的值 省略where條件將操作全部列記錄 UPDATE person SET age = age + 5; -- 更新多個(gè)字段 UPDATE person SET age = age + id,sex = 0; -- 設(shè)置id是偶數(shù)的 年齡+10 UPDATE person SET age = age + 10 WHERE id % 2 = 0; -
單表刪除記錄 DELETE
DELETE FROM person WHERE id = 6; -- 從person表刪除id=6的那行
查詢 SELECT
-- 查找記錄
SELECT select_expr[,select_expr ...]
[
FROM table_reference
[WHERE where_condition]
[GROUP BY {col_name | position} [ASC|DESC],...]
[HAVING where_condition]
[ORDER BY {col_name | expr | position} [ASC|DESC],...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
]
select_expr：查詢表達(dá)式
每一個(gè)表達(dá)式表示想要的一列，必須有至少一個(gè)
每個(gè)列之間以英文逗號(hào)分隔
星號(hào)（*）表示所有列，tbl_name.*可以表示命名表的所有列
查詢表達(dá)式可以使用[AS] alias_name為其賦予別名
別名可用于GROUP BY，ORDER BY 或 HAVING 子句
基本查詢：
-
查詢 person 表下所有字段
SELECT * FROM person; -
查詢 person 表下部分字段
SELECT user_name,pass_word FROM person; -
查詢時(shí)指定結(jié)果集字段別名
SELECT user_name AS '用戶名',pass_word AS '密碼' FROM person;
條件表達(dá)式 WHERE：
對(duì)記錄進(jìn)行過濾，如果沒有指定 WHERE 子句，則顯示所有記錄。在 WHERE 表達(dá)式中，可以使用 MySQL 支持的函數(shù)或運(yùn)算符。
查詢結(jié)果分組 GROUP BY：
-
查詢男女的人數(shù)
select sex,COUNT(*) as '人數(shù)' from person GROUP BY sex;
-
查詢總?cè)藬?shù)大于3的性別
SELECT sex,COUNT(*) as '人數(shù)' FROM person GROUP BY sex HAVING COUNT(*)>3 ;
排序 ORDER BY：
語法 : order by 字段 asc/desc
ASC : 順序，正序。數(shù)值：遞增，字母：自然順序（a-z）
DESC: 倒序，反序。數(shù)值：遞減，字母：自然反序 (z-a)
-
默認(rèn)情況下，按照插入記錄順序排序
select * from student; -
按照id排序
select * from student order by id ASC; //按照id順序排序 select * from student order by id DESC; //按照id倒序排序 -
多個(gè)排序條件：按照英語成績(jī)正序順序，如果英語成績(jī)相同，按照語文成績(jī)倒序
select * from student order by english ASC,chinese DESC;
限制查詢結(jié)果返回的數(shù)量 Limit（起始行,查詢幾行）(startRow,pageSize)
起始行從0開始
分頁：當(dāng)前頁 每頁顯示多少條
-
分頁查詢當(dāng)前頁的數(shù)據(jù)的sql:
SELECT * FROM student LIMIT (當(dāng)前頁-1)*每頁顯示多少條,每頁顯示多少條; -
例子
查詢第1,2條記錄（第1頁的數(shù)據(jù)）：SELECT * FROM student LIMIT 0,2; 查詢第3,4條記錄（第2頁的數(shù)據(jù)）：SELECT * FROM student LIMIT 2,2; 查詢第5,6條記錄（第3頁的數(shù)據(jù)）：SELECT * FROM student LIMIT 4,2; 查詢第7,8條記錄 (沒有記錄不顯示)：SELECT * FROM student LIMIT 6,2;
子查詢
子查詢（subQuery）是指出現(xiàn)在其他 SQL 語句內(nèi)的 SELECT 子句。
例如：
SELECT * FROM t1 WHERE col1= （SELECT col2 FROM t2）;
其中，SELECT * FROM t1 稱為 Outer Query/OuterStatement （外層查詢、外層聲明）
SELECT col2 FROM t2，稱為 subQuery（子查詢）
子查詢指嵌套在查詢內(nèi)部，且必須始終出現(xiàn)在圓括號(hào)內(nèi)。
子查詢可以包含多個(gè)關(guān)鍵字或條件，
如 DISTINCY 、GROUP BY、ORDER BY、LIMIT，函數(shù)等
子查詢的外層查詢可以是 SELECT 、INSERT、UPDATE、SET、或DO。
子查詢可以返回標(biāo)量、一行、一列或子查詢
-
使用比較運(yùn)算符的子查詢
=、>、<、>=、<=、<>、!=、<=> 這些運(yùn)算符可以引發(fā)子查詢
如果要和子查詢結(jié)果做運(yùn)算，而子查詢結(jié)果為多條時(shí)，使用ANY、SUM、ALL三個(gè)關(guān)鍵字：
ANY、SUM:符合其中一個(gè)
ALL：符合全部-- 查詢均價(jià) SELECT AVG(`goods_price`) FROM `tdb_goods` ; -- 查詢均價(jià) (四舍五入，保留兩位小數(shù)) SELECT ROUND(AVG(`goods_price`), 2) AS '均價(jià)' FROM `tdb_goods` ; -- 查詢大于均價(jià)的商品 SELECT `goods_id`,`goods_name`,`goods_price` FROM `tdb_goods` WHERE `goods_price` > (SELECT ROUND(AVG(`goods_price`), 2) FROM `tdb_goods`); -- 查詢超極本的價(jià)格 SELECT `goods_price` FROM `tdb_goods` WHERE `goods_cate`='超級(jí)本'; -- 查詢哪些商品的價(jià)格大于超極本 SELECT `goods_name`,`goods_price` FROM `tdb_goods` WHERE `goods_price` >ANY ( SELECT `goods_price` FROM `tdb_goods` WHERE `goods_cate`='超級(jí)本' ); -
使用 [NOT] IN 的子查詢
=ANY 運(yùn)算符與 IN 等效
!= ALL 或 <>ALL運(yùn)算符與 NOT IN 等效 -
使用 [NOT] EXISTS 的子查詢
如果子查詢返回任何行，EXISTS 將返回 TRUE；否則返回 FALSE。
將查詢結(jié)果寫入數(shù)據(jù)表：INSERT...SELECT
INSERT [INTO] tbl_name [(col_name ,...)]
SELECT ...
-- 1. 創(chuàng)建分類空表
CREATE TABLE IF NOT EXISTS tdb_goods_cates(
cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
cate_name VARCHAR(40) NOT NULL
);
-- 2. 將查詢到的分類 goods_cate 數(shù)據(jù) 插入到 tdb_goods_cates 表中的 cate_name 字段
INSERT INTO `tdb_goods_cates` (cate_name) SELECT `goods_cate` FROM `tdb_goods`GROUP BY `goods_cate`;
-- 查詢商品表
SELECT * FROM `tdb_goods_cates`
-
多表更新：參照另外一張表來更新當(dāng)前表的記錄
UPDATE table_references -- 表參照關(guān)系 SELECT col_name1={expr1|DEFAULT} [col_name2={expr2|DEFAULT} ]... [WHERE where_condition] -
表的參照關(guān)系 ：表通過 INNER JOIN 或 LEFT JOIN 去連接另外一張表
table_references -- 表1 {[INNER|CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN} -- 連接類型 table_references -- 表2 ON conditional_expr -- 連接條件連接類型：
- INNER JOIN：內(nèi)連接 【用的較多】
顯示左表和右表中交集(公共)的部分（僅顯示符合連接條件的記錄） - LEFT [OUTER] JOIN：左外連接
顯示左表的全部記錄及右表符合連接條件的記錄。 - RIGHT [OUTER] JOIN：右外連接
顯示右表的全部記錄及左表符合連接條件的記錄。 - 自身連接
電商分類，虛擬一張相同的表，使用內(nèi)連接
- INNER JOIN：內(nèi)連接 【用的較多】
3. 使用內(nèi)連接更新：
UPDATE tdb_goods INNER JOIN `tdb_goods_cates` ON `goods_cate`=`cate_name` SET `goods_cate`=`cate_id`; -- ON... 連接條件
連接條件：
使用 ON 關(guān)鍵字來設(shè)定連接條件，也可以使用 WHERE 來代替。
通常使用 ON 關(guān)鍵字來設(shè)定連接條件
使用 WHERE 關(guān)鍵字進(jìn)行結(jié)果集記錄的過濾
-
表連接：
外鍵的逆向操作，外鍵把數(shù)據(jù)分開來存儲(chǔ)，通過連接又將多張表聯(lián)系在一起。
外連接：子查詢和連接這里有點(diǎn)模糊 ，后面有時(shí)間再屢一下...
運(yùn)算符和函數(shù)
內(nèi)置函數(shù)庫(kù)
-
字符函數(shù)
-
CONCAT() : 字符連接
SELECT CONCAT('A', 'B'); -- 結(jié)果:AB SELECT CONCAT('1','-','2'); -- 結(jié)果:1-2 SELECT CONCAT(first_name,last_name) AS 'full name' FROM tb_name; --拼接姓名 -
CONCAT_WS(): 使用指定的分隔符進(jìn)行字符連接
SELECT CONCAT_WS('|', 'A','B','C') -- 結(jié)果 A|B|C SELECT CONCAT_WS('-',first_name,last_name) AS 'full name' FROM tb_name -- 結(jié)果：sun-wukong -
FORMAT(): 數(shù)字格式化
SELECT FORMAT(120.24,0) -- 120 SELECT FORMAT(120.25,1) -- 120.3 SELECT FORMAT(120.24,1) -- 120.2 SELECT FORMAT(120.24,3) -- 120.240 -
LOWER(): 轉(zhuǎn)換成小寫
SELECT LOWER('MySQL') -- mysql -
UPPER(): 轉(zhuǎn)換成大寫
SELECT UPPER('MySQL') -- MYSQL -
LEFT(): 獲取左側(cè)字符
SELECT LEFT('MySQL',2) -- My SELECT UPPER(LEFT('MySQL',2)) -- 函數(shù)嵌套 -
RIGHT(): 獲取右側(cè)字符
SELECT RIGHT('MySQL',3) -- SQL SELECT LOWER(RIGHT('MySQL',3) -- sql -
LENGTH(): 獲取字符長(zhǎng)度
SELECT LENGTH("MySQL") -- 5 SELECT LENGTH("My SQL") -- 6 LTRIM(): 刪除左邊空格
RTRIM(): 刪除右邊空格
TRIM (): 刪除左邊&&右邊的空格
-
REPLACE(): 字符替換
SELECT REPLACE('???My??SQL??','?','') -- MySQL SELECT REPLACE('???My??SQL??','?','-') -- ---My--SQL-- SELECT REPLACE('???My??SQL??','?','!!') -- !!!!!!My!!!!SQL!!!! SELECT REPLACE('???My??SQL??','?','!*') -- !*!*!*My!*!*SQL!*!* -
SUBSTRING(): 截取
SELECT SUBSTRING('MySQL',1,2) -- My 從1開始，不是從0開始 SELECT SUBSTRING('MySQL',3) -- SQL SELECT SUBSTRING('MySQL',-1) -- L SELECT SUBSTRING('MySQL',-3) -- SQL -
LIKE: 模式匹配
SELECT 'MYSQL' LIKE 'M%' -- 1 %指任意字符、_代表任意一個(gè)字符
-
-
數(shù)值運(yùn)算符和函數(shù)
-
CEIL(): 向上取整 / 進(jìn)一取整
SELECT CEIL(3.01) -- 4 SELECT CEIL(3.99) -- 4 -
FLOOR(): 向下取整 / 舍一取整
SELECT FLOOR(3.01) -- 3 SELECT FLOOR(3.99) -- 3 -
DIV: 整數(shù)除法（類似java中的除法）
SELECT 3/4 -- 0.7500 SELECT 3 DIV 4 -- 0 SELECT 4 DIV 3 -- 1 MOD（%）： 取余數(shù) （模）
-
SELECT 2 MOD 5; -- 2
SELECT 5 MOD 2; -- 1
SELECT 5.3 % 3; -- 2.3
* POWER（）： 冪運(yùn)算 m的n次方
SELECT POWER(3, 3) -- 3的3次方 3*3*3
* ROUND(): 四舍五入
SELECT ROUND(3.652,1) -- 3.7
SELECT ROUND(3.652,2) -- 3.65
SELECT ROUND(3.655,2) -- 3.66
SELECT ROUND(3.655,0) -- 4
-
比較運(yùn)算符和函數(shù)
-
[NOT] BETWEEN ... AND ... [不]在范圍內(nèi)
SELECT 35 BETWEEN 1 AND 22 -- 0 SELECT 35 BETWEEN 1 AND 36 -- 1 SELECT 35 NOT BETWEEN 1 AND 34 -- 1 -
[NOT] IN() [不]在列出值范圍之內(nèi)
SELECT 10 IN(5,10,15,20) -- 1 SELECT 30 IN(5,10,15,20) -- 0 -
IS [NOT] NULL [不]為空
SELECT NULL IS NULL -- 1 SELECT '' IS NULL -- 0 SELECT 0 IS NULL -- 0
-
-
時(shí)間日期函數(shù)
-
NOW(): 當(dāng)前日期和時(shí)間
SELECT NOW() -- 2018-03-22 17:08:26 -
CURDATE(): 當(dāng)前時(shí)間
SELECT CURDATE() -- 2018-03-22 -
CURTIME(): 當(dāng)前時(shí)間
SELECT CURTIME() -- 17:08:58 -
DATE_ADD(): 日期變化
SELECT DATE_ADD('2018-03-22',INTERVAL 365 DAY) -- 2019-03-22 SELECT DATE_ADD('2018-03-22',INTERVAL -365 DAY) -- 2017-03-22 SELECT DATE_ADD('2018-03-22',INTERVAL 1 YEAR) -- 2019-03-22 SELECT DATE_ADD('2018-03-22',INTERVAL 3 WEEK) -- 2018-04-12 -
DATEDIFF(): 日期差值
SELECT DATEDIFF('2018-03-22','2019-03-22') -- -365 SELECT DATEDIFF('2018-03-22','2017-03-22 -- 365 -
DATE_FORMAT(): 日期格式化
SELECT DATE_FORMAT('2018-03-22', '%m/%d/%Y') -- 03/22/2018
-
-
信息函數(shù)
-- 返回當(dāng)前連接的id(線程id) SELECT CONNECTION_ID() -- 當(dāng)前打開的數(shù)據(jù)庫(kù) SELECT DATABASE() -- 最后插入記錄的ID號(hào) SELECT LAST_INSERT_ID() -- 當(dāng)前用戶 root@localhost SELECT USER() -- 當(dāng)前數(shù)據(jù)庫(kù)版本信息 5.7.21 SELECT VERSION() -
聚合函數(shù)
特點(diǎn)：只有一個(gè)返回值* AVG()： 平均值 * COUNT(): 計(jì)數(shù) * MAX(): 最大值 * MIN： 最小值 * SUM(): 求和 -
加密函數(shù)
- MD5():信息摘要算法
- PASSWORD（）：密碼算法
自定義函數(shù)
用戶自定義函數(shù)（UDF）,是一種對(duì)MySQL擴(kuò)展的途徑，其用法與內(nèi)置函數(shù)相同
作用：將一些經(jīng)常使用的方法提前定義好，方便調(diào)用
-
創(chuàng)建自定義函數(shù)
CREATE FUNCTION function_name RETURNS -- 返回值類型 {STRING | INTEGER | REAL | DECIMAL} routine_body -- 函數(shù)體 -
關(guān)于函數(shù)體
1). 由合法的 SQL 語句構(gòu)成，
2). 也可以是簡(jiǎn)單的 SELECT 或 INSERT 語句
3). 如果為復(fù)合結(jié)構(gòu)則使用 BEGIN...END 語句
4). 復(fù)合結(jié)構(gòu)可以包含聲明，循環(huán)，控制結(jié)構(gòu) -
創(chuàng)建不帶參數(shù)的自定義函數(shù)
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H時(shí):%i分:%s秒') -- 2018年03月22日 19時(shí):19分:16秒 //自定義函數(shù) CREATE FUNCTION f1() RETURNS VARCHAR(30) RETURN DATE_FORMAT(NOW(), '%Y年%m月%d日 %H時(shí):%i分:%s秒'); //調(diào)用 SELECT f1(); -- 2018年03月22日 19時(shí):19分:16秒 //刪除方法 DROP FUNCTION f1(); -
創(chuàng)建帶有參數(shù)的自定義函數(shù)
//新建帶參函數(shù) CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED) RETURNS FLOAT(10,2) RETURN (num1 + num2)/2; -- 返回兩個(gè)參數(shù)和除以2 //調(diào)用 SELECT f2(1,5);
存儲(chǔ)過程
存儲(chǔ)過程是 SQL 語句和控制語句的預(yù)編譯集合，以一個(gè)名稱存儲(chǔ)并作為一個(gè)單元處理。存儲(chǔ)過程存儲(chǔ)在數(shù)據(jù)庫(kù)內(nèi)，省略了 MySQL引擎對(duì)SQL語句語法分析和編譯，客戶端調(diào)用則直接調(diào)用編譯的結(jié)果，所以執(zhí)行速度較快，同時(shí)增強(qiáng)了 SQL 語句的功能和靈活性，減少了網(wǎng)絡(luò)流量。
-
參數(shù)
- IN：表示改參數(shù)的值必須在調(diào)用存儲(chǔ)過程中指定
- OUT：表示該參數(shù)的值可以被存儲(chǔ)過程改變，并且可以返回
- INOUT：表示該參數(shù)的調(diào)用時(shí)指定，并且可以被改變了返回（都可以）
-
過程體
- 過程體由合法的SQL語句構(gòu)成
- 過程體可以是任意SQl語句
- 過程體如果我復(fù)合結(jié)構(gòu)，則使用 BEGIN...END語句
- 復(fù)合結(jié)構(gòu)可以包含聲明、循環(huán)、控制結(jié)構(gòu)。
-
創(chuàng)建不帶參數(shù)的存儲(chǔ)過程
CREATE PROCEDURE test_pro() SELECT VERSION(); //調(diào)用 CALL test_pro(); -
創(chuàng)建帶有IN類型參數(shù)的存儲(chǔ)過程
DELIMITER $ -- 定界符 CREATE PROCEDURE delPersonById(p_id INT UNSIGNED) BEGIN DELETE FROM person WHERE id = p_id; -- 過程體 END $ -- 調(diào)用存儲(chǔ)過程 CALL delPersonById(5); -
創(chuàng)建帶有OUT類型參數(shù)的存儲(chǔ)過程
-- 從 person 表中根據(jù)id刪除一條數(shù)據(jù)，并將 剩余總數(shù)設(shè)置到輸出參數(shù) num 字段 DELIMITER $ CREATE PROCEDURE delUserReturnNums(IN p_id INT,OUT userNums INT) BEGIN DELETE FROM person WHERE id = p_id; SELECT COUNT(id) FROM person INTO userNums; -- 將 SELECT 表達(dá)式的結(jié)果放入到 userNums 變量 END $ -- 調(diào)用存儲(chǔ)過程 CALL `delUserReturnNums`(4,@nums); -- 查看 nums 的值 SELECT @nums -- 聲明一個(gè)用戶變量：跟MySQL客戶端綁定，這種方式設(shè)定的變量只對(duì)當(dāng)前用戶所使用的客戶端生效 SET @i = 8; -
創(chuàng)建帶有多個(gè)OUT類型參數(shù)的存儲(chǔ)過程
-- 根據(jù)年齡刪除用戶：返回兩個(gè)參數(shù) 1.刪除的用戶數(shù)量 2. 剩余的用戶數(shù)量 DELIMITER $ CREATE PROCEDURE delUsersByAgeReturnInfos(IN p_age INT,OUT delUsers INT,OUT userCount INT) BEGIN DELETE FROM person WHERE age = p_age; -- 根據(jù)年齡刪除用戶 SELECT ROW_COUNT() INTO delUsers; -- 返回上一次操作影響的行數(shù) SELECT COUNT(id) FROM person INTO userCount; END $ -- 調(diào)用存儲(chǔ)過程 CALL delUsersByAgeReturnInfos(14,@A,@B); -- 查看返回結(jié)果變量 SELECT @A; SELECT @B; -
存儲(chǔ)過程與自定義函數(shù)的區(qū)別
- 存儲(chǔ)過程實(shí)現(xiàn)相對(duì)復(fù)雜；而函數(shù)針對(duì)性較強(qiáng)
- 存儲(chǔ)過程可以返回多個(gè)值；函數(shù)只能有一個(gè)返回值
- 存儲(chǔ)過程一般是獨(dú)立的來執(zhí)行；而函數(shù)可以作為其他SQL語句的組成部分來實(shí)現(xiàn)。
存儲(chǔ)引擎
MySQL 可以將數(shù)據(jù)以不同的技術(shù)存儲(chǔ)在文件（內(nèi)存）中，這種技術(shù)成為存儲(chǔ)引擎。
每一種存儲(chǔ)引擎使用了不同的存儲(chǔ)機(jī)制、索引技巧、鎖定水平、最終提供廣泛且不同的功能。
關(guān)系型數(shù)據(jù)庫(kù)中數(shù)據(jù)的存儲(chǔ)是以表的形式來實(shí)現(xiàn)的，所以說存儲(chǔ)引擎也可以稱為表類型。
一種技術(shù)：一種存儲(chǔ)數(shù)據(jù)
-
MySQL 支持的存儲(chǔ)引擎
- MyISAM：
存儲(chǔ)限制可達(dá)256TB,支持索引，表級(jí)鎖定，數(shù)據(jù)壓縮；適用于事務(wù)的處理不多的情況。 - InnoDB：
存儲(chǔ)限制可達(dá)64TB,支持事務(wù)和索引，鎖顆粒為行鎖；適用于事務(wù)處理比較多，需要有外鍵支持的情況 。
其他等等...
- MyISAM：
-
并發(fā)控制
- 當(dāng)多個(gè)連接對(duì)記錄進(jìn)行修改時(shí)保證數(shù)據(jù)的一致性和完整性
- 在處理并發(fā)讀或并發(fā)寫時(shí)，系統(tǒng)會(huì)使用一套鎖機(jī)制來解決這個(gè)問題
-
鎖：
共享鎖（讀鎖）：
在某一資源上 讀鎖是共享的（互不阻塞），在同一時(shí)間段內(nèi)，多個(gè)用戶可以同時(shí)讀取同一個(gè)資源，讀取過程中數(shù)據(jù)不會(huì)發(fā)生任何變化。排他鎖（寫鎖）：
在任何時(shí)候，只能有一個(gè)用戶來寫入資源，當(dāng)進(jìn)行寫鎖時(shí)會(huì)阻塞其他的讀鎖或者寫鎖操作。-
鎖顆粒
- 表鎖：是一種開銷最小的鎖策略
- 行鎖：是一種開銷最大的鎖策略（支持最大并發(fā)操作處理）
- 怎么理解呢，就是每條記錄都要加，所以開銷最大
-
事務(wù)
- 用于保證數(shù)據(jù)庫(kù)的完整性
- 特征：
- 原子性
- 一致性
- 隔離性
- 持久性
-
外鍵
- 是保證數(shù)據(jù)一致性的策略
-
索引
- 是對(duì)數(shù)據(jù)表中一列或者多列的值進(jìn)行排序的一種結(jié)構(gòu)。
使用索引可以快速的訪問數(shù)據(jù)表中的特定信息，索引是進(jìn)行記錄快速定位的一種方法，好比是書的目錄，如果我們要快速找到這本書的某一部分內(nèi)容，那么我們應(yīng)該首先在目錄當(dāng)中查找相應(yīng)的章節(jié)，然后看一下他在第幾頁，再快速定位到相應(yīng)的頁碼查找想看的內(nèi)容。
