1. 數據庫操作
#創(chuàng)建數據庫
CREAT DATABASE IF NOT EXISTS db_name DEFAULT CHARACTER SET charset_name
#查看數據庫
SHOW DATABASE LIKE 'pattern' | WHERE expr
#修改數據庫
ALTER DATABASE db_name DEFAULT CHARACTER SET charset_name
#刪除數據庫
DROP DATABASE IF EXISTS db_name
#使用數據庫
USE db_name
2. MySQL 管理
端口號:3306
超級用戶名:root
連接:mysql -u root -p
關閉:cd /usr/bin 然後 ./mysqladmin -u root -p shutdown
檢查MySQL服務器是否啟動:ps -ef | grep mysqld
啟動mysql服務器cd /usr/bin 然後 ./mysqld_safe &
用戶管理
#新增用戶
INSERT INTO user (host, user, password, select_priv, insert_priv, update_priv) VALUES ('localhost', 'guest', PASSWORD('guest123'), 'Y', 'Y', 'Y');
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON TUTORIALS.* TO 'zara'@'localhost' IDENTIFIED BY 'zara123';
#刷新
FLUSH PRIVILEGES;
#查看用戶
SELECT host, user, password FROM user WHERE user = 'guest';
#權限列表
Select_priv、Insert_priv帆竹、Update_priv、Delete_priv青扔、Create_priv、Drop_priv、Reload_priv微猖、Shutdown_priv谈息、
Process_priv、File_priv凛剥、Grant_priv侠仇、References_priv、Index_priv犁珠、Alter_priv
數據表操作
創(chuàng)建數據表
#創(chuàng)建數據表
CREAT TABLE IF NOT EXISTS table_name(column_name data_type,...);
#同時插入數據
CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] select_statement
查看數據表
#查看數據表
SHOW TABLES [FROM db_name]
#查看數據表結構
SHOW COLUMNS FROM tbl_name
#查看數據表索引
SHOW INDEX FROM tbl_name
#查看Mysql的性能及統(tǒng)計信息
SHOW TABLE STATUS LIKE [FROM db_name] [LIKE 'pattern'] \G:
插入記錄
#插入記錄
INSERT INTO tbl_name [(col_name),...] VALUES(val,...) #普通插入
INSERT [INTO] tbl_name SET col_name={expr | DEFAULT},... #可使用子查詢
INSERT [INTO] tbl_name [(col_name)] SELECT ... #將查詢結果插入到指定表
更新記錄
#更新記錄
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT}[,clo_name2={expr2|DEFAULT}]...[WHERE where_condition] #單表更新
UPDATE table_references SET col_name1={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}]...[WHERE where_condition] #多表更新
刪除記錄
#刪除記錄
DELETE FROM tbl_name [WHERE where_condition] #單表刪除
DELETE tbl_name[.*][,tbl_name[.*]]... FROM table_references [WHERE where_condition] #多表刪除
查找記錄
#查詢表達式
SELECT id [AS] alias_name
#條件表達式
SELECT id [AS] alias_name [WHERE where_condition]
#LIKE子句
SELECT id [AS] alias_name WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
#結果分組, 分組條件
SELECT id [AS] alias_name [GROUP BY {col_name|position} [ASC|DESC],...] [HAVING where_condition]
#UNION 操作符
SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions];
#結果排序
SELECT id [AS] alias_name [ORDER BY {col_name | expr | position} [ASC | DESC],...]
#限制結果返回數量
SELECT id [AS] alias_name [LIMIT {[offset,] row_count | row_count OFFSET offset}]
子查詢
#語法
SELECT * FROM tb_name WHERE col_name =(SELECT col_name2 FROM tb_name2)
#內層-可包含多個關鍵字或條件
DISTINC逻炊、GROUP BY、ORDER BY盲憎、LIMIT嗅骄、函數等
#內層-可使用比較運算符
=胳挎、>饼疙、<、>=慕爬、<=窑眯、<>、!=医窿、<=>
#外層
SELECT磅甩、INSERT、UPDATE姥卢、SET卷要、DO
#ANY、SOME独榴、ALL僧叉、[NOT] IN、[NOT] EXISTS
operand comparison_operator ANY(subquery)
operand comparison_operator SOME(subquery)
operand comparison_operator ALL(subquery)
operand comparison_operator [NOT] IN(subquery)
operand comparison_operator [NOT] EXISTS(subquery)
連接
#語法
table_reference {[INNER | CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN} table_reference ON conditional_expr
tbl_name [[AS] alias] | table_subquery [AS] alias
#內連接
INNER JOIN棺榔、JOIN瓶堕、CROSS JOIN #顯示左表及右表符合連接條件的記錄
#左外連接
LEFT [OUTER] JOIN #顯示左表全部記錄及右表符合連接條件的記錄
#右外連接
RIGHT [OUTER] JOIN #顯示右表全部記錄及左表符合連接條件的記錄
#自身連接
同一個數據表對其自身進行連接
約束使用
#自動編號
AUTO_INCREMENT
#空值與非空
NULL,NOT NULL
#主鍵約束
PRIMARY KEY
#唯一約束
UNIQUE KEY
#默認值
DEFAULT
#外鍵約束
FOREIGN KEY : CASCADE,SET NULL,RESTRICT,NO ACTION
#表級約束
#列級約束
存儲引擎
#配置文件
default-storage-engine=INNODB
#創(chuàng)建時指定引擎
CREATE TABLE tbl_name(...) ENGINE=engine;
#修改存儲引擎
ALTER TABLE tal
#MyISAM、InnoDB症歇、Memory郎笆、CSV、Archive類型
MyISAM #256TB忘晤、支持索引宛蚓、表級鎖定、數據壓縮
InnoDB #64TB设塔、支持事務和索引苍息、鎖顆粒為行鎖
共享鎖（讀鎖）
同一時間段時,多個用戶可以讀取同一資源,讀取過程中數據不會發(fā)生任何變化
排他鎖（寫鎖）
在任何時間只能有一個用戶寫入資源,當進行寫鎖時會阻塞其他的讀鎖或寫鎖操作
修改數據表
#添加單列
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]
#添加多列
ALTER TABLE tbl_name ADD[COLUMN] (col_name column_definition,...)
#刪除列
ALTER TABLE tbl_name DROP [COLUMN] col_name
#修改列定義
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name]
#修改列名稱
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
#添加主鍵約束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type](index_col_name,...)
#添加唯一約束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY][index_name] [index_type] (index_col_name,...)
#添加外鍵約束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition
#添加/刪除默認約束
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
#刪除主鍵約束
ALTER TABLE tbl_name DROP PRIMARY KEY
#刪除唯一約束
ALTER TABLE tbl_name DROP {INDEX|KEY} index_name
#刪除外鍵約束
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
#數據表更名
ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2]...
臨時表
函數
內置函數-字符函數
CONCAT() #字符連接
CONCAT_WS() #使用指定的分隔符連接
FORMAT() #數字格式化
LOWER() #轉換成小寫字母
UPPER() #轉換成大寫字母
LEFT() #獲取左側字符
RIGHT() #獲取名側字符
LENGHT() #獲取字符串長度
LTRIM() #刪除前導空格
RTRIM() #刪除后續(xù)空格
TRIM() #刪除前導
SUBSTRIANG() #字符串截取
[NOT] LIKE #模糊匹配
REPLACE() #字符串替換
內置函數-數值運算符與函數
CEIL() #進一取整
FLOOR() #舍一取整
DIV() #整數除法
MOD() #取余數(取模)
POWER() #冪運算
ROUND() #四舍五入
TRUNCATE() #數字截取
內置函數-比較運算符與函數
[NOT] BETWEEN ... AND ... #[不]在范圍內
[NOT] IN() #[不]在列出值范圍內
IS [NOT] NULL #[不]為空
內置函數-日期時間函數
NOW() #當前日期和時間
CURDATE() #當前日期
CURTIME() #當前時間
DATE_ADD() #日期變化
DATEDIFF() #日期差值
DATE_FORMAT() #日期格式化
內置函數-信息函數
CONNECTION_ID() #連接ID
DATEBASE() #當前數據庫
LAST_INSERT_ID() #最后播放
USER() #當前用戶
VERSION() #版本信息
內置函數-聚合函數
AVG() #平均值
COUNT() #計數
MAX() #最大值
MIN() #最小值
SUM() #求和
內置函數-加密函數
MD5() #信息摘要算法
PASSWORD() #密碼算法
自定義函數
創(chuàng)建自定義函數
# 參數 : 0個或多個
# 返回值 : 只能有1個返回值
CREATE FUNCTION function_name(xxname VACHAR(32)) RETURNS {STRING | INTEGER | REAL | DECIMAL} routine_body
#復合結構
CREATE FUNCTION function_name(xxname VARCHAR(20)) RETURNS {STRING | INTEGER | REAL | DECIMAL} BEGIN routine_body END
# 刪除函數
DROP FUNCTION [IF EXISTS] function_name
存儲過程
流程
SQL命令->MySQL引擎->分析語法->可識別命令->執(zhí)行結果0>返回客服端
優(yōu)點
增加SQL功能和靈活性,實現較快的執(zhí)行速度,減少網絡流量
#創(chuàng)建存儲過程
CREATE [DEFINER={user | CURRENT_USER}] PROCEDURE sp_name ([proc_parameter[,...]])[characteristic ...] routine_body
proc_parameter:[IN | OUT | INOUT ]param_name type #需要通過delimiter修改定界符
#修改存儲過程
ALTER PROCEDURE sp_name [characteristic ...] COMMENT 'string' | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER }
#刪除存儲過程
DROP PROCEDURE [IF EXISTS] sp_name
#調用存儲過程
CALL sp_name([parameter[,...]])
CALL sp_name[()]
參數
IN #該參數必須在存儲過程時指定
OUT #該參數可被存儲過程改變,且可返回
INOUT #該參數調用時指定,且可被改變和返回
特性
COMMENT 'string' | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER }
COMMENT #注釋
CONTAINS SQL #包含SQL語句,但不含讀或寫數據的語句
NO SQL #不包含SQL語句
READS SQL DATA #包含讀數據的語句
MODIFIES SQL DATA #包含寫數據的語句
SQL SECURITY { DEFINER | INVOKER } #指明誰有權限來執(zhí)行
存儲過程與自定義函數區(qū)別
- 存儲過程實現的功能要復雜一些;函數針對性更強
- 存儲過程可以返回多個值;函數只能有一個返回值
- 存儲過程一般獨立來執(zhí)行;而函數可以作為其他SQL語句的組成部分出現
事務
控制語句
BEGIN或START TRANSACTION； #顯式地開啟一個事務
COMMIT 或 COMMIT WORK #提交事務，并使已對數據庫進行的所有修改成為永久性的竞思；
ROLLBACK 或 ROLLBACK WORK #回滾會結束用戶的事務表谊，并撤銷正在進行的所有未提交的修改
SAVEPOINT identifier #允許在事務中創(chuàng)建一個保存點，一個事務中可以有多個SAVEPOINT
RELEASE SAVEPOINT identifier #刪除一個事務的保存點盖喷，當沒有指定的保存點時爆办，執(zhí)行該語句會拋出一個異常
ROLLBACK TO identifier #把事務回滾到標記點
SET TRANSACTION #設置事務的隔離級別 : READ UNCOMMITTED,READ COMMITTED,REPEATABLE ,SERIALIZABLEREAD
事務處理
BEGIN #開始一個事務
ROLLBACK #事務回滾
COMMIT #事務確認
SET AUTOCOMMIT=0 # 禁止自動提交
SET AUTOCOMMIT=1 # 開啟自動提交
