【MySQL】MySQL基礎

foreverfriends發表於2018-02-03
MySql

一、基本語法

【MySQL目錄結構】
●bin目錄，儲存可執行檔案
●data目錄，儲存資料檔案
●docs，文件
●include目錄，儲存包含的標頭檔案
●lib目錄，儲存庫檔案
●share，錯誤資訊和字符集檔案

 

【MySQL的配置選項】
●修改編碼方式
[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8

 

【啟動服務】
●啟動MySQL服務
net start mysql

 

【MySQL退出】
●mysql>exit;
●mysql>quit;
●mysql>\q;

 

【修改MySQL提示符】
●連線客戶端時通過引數指定
●shell>mysql -uroot -proot --promot 提示符


●連線上客戶端後，通過promot命令修改
●mysql>prompt 提示符

引數 描述
\n 完整的日期
\d 當前資料庫
\h 伺服器名稱
\u 當前使用者

 

【MySQL常用命令】

#顯示當前伺服器版本
SELECT VERSION();

#顯示當前日期時間
SELECT NOW();

#顯示當前使用者
SELECT USER();

 

 

【MySQL語句的規範】
●關鍵字與函式名稱全部大寫
●資料庫名稱、表名稱、欄位名稱全部小寫
●SQL語句必須以分號結尾

 

二、資料庫的建立、修改和刪除

【建立資料庫】

●CREARE {DATABASE | SCHEMA } [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] character_name

{}中為必選項
[]中為可選項

【檢視當前伺服器下的資料庫列表】
●SHOW {DATABASE | SCHEMAS} [LIKE 'pattern' | WHERE expr]

【修改資料庫】
●ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name

【刪除資料庫】
●DROP {DATABASE | SCHEMA } [IF EXISTS ] db_name

 

 三、資料型別

 

 

 

 

 

【MySQL目錄結構】
●bin目錄，儲存可執行檔案
●data目錄，儲存資料檔案
●docs，文件
●include目錄，儲存包含的標頭檔案
●lib目錄，儲存庫檔案
●share，錯誤資訊和字符集檔案

【MySQL的配置選項】
●修改編碼方式
[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8

【啟動服務】
●啟動MySQL服務
net start mysql

【MySQL退出】
●mysql>exit;
●mysql>quit;
●mysql>\q;

【修改MySQL提示符】
●連線客戶端時通過引數指定
●shell>mysql -uroot -proot --promot 提示符


●連線上客戶端後，通過promot命令修改
●mysql>prompt 提示符

引數 描述
\n 完整的日期
\d 當前資料庫
\h 伺服器名稱
\u 當前使用者

【MySQL常用命令】

#顯示當前伺服器版本
SELECT VERSION();

#顯示當前日期時間
SELECT NOW();

#顯示當前使用者
SELECT USER();

【MySQL語句的規範】
●關鍵字與函式名稱全部大寫
●資料庫名稱、表名稱、欄位名稱全部小寫
●SQL語句必須以分號結尾

【建立資料庫】

●CREARE {DATABASE | SCHEMA } [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] character_name

{}中為必選項
[]中為可選項

【檢視當前伺服器下的資料庫列表】
●SHOW {DATABASE | SCHEMAS} [LIKE 'pattern' | WHERE expr]

【修改資料庫】
●ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name

【刪除資料庫】
●DROP {DATABASE | SCHEMA } [IF EXISTS ] db_name
----------------------------------------------------------------------------------------------------------
【建立資料表】
●CREATE TABLE [IF NOT EXISTS] table_name (column_name data_type,...)

【檢視資料表列表】
●SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]

【檢視資料表結構】
●SHOW COLUMNS FROM tbl_name

【插入記錄】
●INSERT [INTO] tbl_name [(col_name,...)] VALUES(val,...)

【查詢記錄】
●SELECT expr,... FROM tbl_name

【空值與非空】
●NULL,欄位值可以為空
●NOT NULL，欄位值禁止為空

【AUTO_INCREMENT】
●自動編號，且必須與主鍵組合使用
● 預設情況下，起始值為1，每次的增量為1

【PRIMARY KEY】
●主鍵約束
●每張資料表只能存在一個主鍵
●主鍵保證記錄的唯一性
●主鍵自動為NOT NULL

【UNIQUE KEY】
●唯一約束
●唯一約束可以保證記錄的唯一性
●唯一約束的欄位可以為空值(NULL)
●每張資料表可以存在多個唯一約束

例如：
mysql>CREATE TABLE tb5
->(
->id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
->username VARCHAR(30) NOT NULL UNIQUE KEY,
->age TINYINT UNSIGNED)
->;

【DEFAULT】
●預設值
●當插入記錄時，如果沒有明確為欄位賦值，則自動賦予預設值
例如：
mysql>CREATE TABLE tb6
->(
->id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
->usename VARCHAR(30) NOT NULL UNIQUE KEY,
->sex ENUM('1','2','3') DEFAULT '3'
);

【約束】
●約束保證資料的完整性和唯一性
●約束分為表級約束和列級約束
●約束型別包括：
NOT NULL（非空約束）
PRIMARY KEY(主鍵約束)
UNIQUE KEY(唯一約束)
DEFAULT(預設約束)
FOREIGN KEY(外來鍵約束)

【FOREIGN KEY】
●外來鍵約束
●保證資料一致性，完整性
●實現一對一和一對多關係

【外來鍵約束的要求】
●父表和子表必須使用相同的儲存引擎，而且禁止使用臨時表
●資料表的儲存引擎只能為InnoDB
●外來鍵列和參照列必須具有相似的資料型別，切中數字的長度或是否有符號位必須相同；而只讀的長度可以不同
●外來鍵列和參照列必須建立索引，如果外來鍵列不存在索引的話，MySQL將自動建立索引

例如：
父表
mysql>CREATE TABLE provinces(
->id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
->pname VARCHAR(20) NOT NULL
->PRIMARY KEY ('id');

子表
mysql>CREATE TABLE users(
->id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
->username VARCHAR(10) NOT NULL,
->pid SMALLINT UNSIGNED,
->FOREIGN KEY (pid) REFERENCES province (id)
->;

【外來鍵約束的參照操作】
●CASCADE: 從父表刪除或更新且自動刪除或則更新子表中匹配的行
●SET NULL: 從父表刪除或更新行，並設定子表中的外來鍵列為NULL，如果使用該選項，必須保證子表列沒有指定NOT NULL
●RESTRICT:拒絕對父表的刪除或更新操作
●NOT ACTION:標準SQL關鍵字，在MySQL中與RESTRICT相同

【表級約束和列級約束】
●對一個資料列建立的約束，稱為列級約束
●對多個資料列建立的約束，稱為表級約束
●列級約束可以在列定義時宣告，也可以在列定義後宣告
●表級約束只能在列定義後宣告


【修改資料表】
新增單列
●ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST|ALTER col_name ]

新增多列
●ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition,...)

刪除列
●ALTER TABLE tbl_name DROP [COLUMN] col_name


例如：
mysql>ALTER TABLE users ADD truename VARCHAR(30) NOT NULL FIRST;
mysql>ALTER TABLE users DROP truename;
mysql>ALTER TABLE users DROP username,DROP age;

新增主鍵約束
●ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)

例如：
msyql>ALTER TABLE users ADD id SMALLINT UNSIGNED;

新增唯一約束
●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) references_definiton

新增、刪除預設約束
●ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

mysql>ALTER TABLE users2 ADD age TINYINT UNSIGNED NOT NULL;
mysql>ALTER TABLE users2 ALTER age SET DEFAULT 15;

刪除主鍵約束
●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

mysql>ALTER TABLE user2 DROP FOREIGN KEY users_ibfk_1;

修改表定義
●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|ALTER col_name]

資料表更名
●方法1：ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
●方法2：RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2] ...

【INSERT】
插入記錄
●方法一：INSERT [INTO] tbl_name [(col_name,...)] {VALUES|VALUE} ({expr|DEFAULT},...),(...),...
●方法二：INSERT [INTO] tbl_name SET col_name={expr|DEFAULT},...
說明：與第一種的區別是，此方法可以使用子查詢。
●方法三：INSERT [INTO] tbl_name [(col_name,...)] SELECT ...
說明：此方法可以將查詢結果插入到指定資料表

【UPDATE】
●更新記錄（單表更新）
●UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr|DEFAULT} [,col_name2={expr2|DEFAULT}] .... [WHERE where_condition]

【DELETE】
●刪除記錄（單表刪除）
●DELETE FROM tbl_name [WHERE where_condition]

【SELECT】
●查詢記錄
●SELECT select_expr [,select_expr...]
[
FROM table_references
[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】
查詢表示式
●每一個表示式表示想要的一列，必須有至少一個
●多個列之間以英文逗號分隔
●星號表示所有列，tbl_name.*可以表示命名錶的所有列
●查詢表示式可以使用 [AS] alias_name為其賦予別名
●別名可用於GROUP BY,ORDER BY或HAVING子句

【WHERE】
●條件表示式
●對記錄進行過濾，如果沒有指定WHERE子句，則顯示所有記錄
●在WHERE表示式中，可以使用MySQL支援的函式或運算子

【GROUP BY 】
●查詢結果分組
●[GROUP BY {col_name|position} [ASC| DESC],...]

【HAVING】
●分組條件
● [HAVING where_condition]

【ORDER BY】
●對查詢結果進行排序
●[ORDER BY {col_name| expr|position} [ASC|DESC],...]

【LIMIT】
●限制查詢結果返回的數量
●[LIMIT {[offset,] row_count | row_count OFFSET offset}]

【子查詢】
子查詢指巢狀在查詢內部，且必須始終出現在圓括號內；
子查詢可以包含多個關鍵字或條件，如DISTINCT、GROUP BY、ORDER BY、LIMIT、函式等。
子查詢的外層查詢可以是SELECT,INSERT,UPDATE,SET或DO.

【子查詢返回值】
子查詢可以返回標量、一行、一列或子查詢。

【使用比較運算子的子查詢】
使用比較運算子的子查詢：=,>,<,>=,<=,<>,!=,<=>
語法結構： operand comparison_operator subquery

【用ANY、SOME或ALL修飾的比較運算子】
operand comparison_operator ANY (subquery)
operand comparison_operator SOME (subquery)
operand comparison_operator ALL (subquery)

【使用[NOT] IN 的子查詢】
語法結構：
operand comparison_operator [NOT] IN (subquery)
=ANY 運算子與IN等效。
!=ALL或<>ALL運算子與NOT IN 等效。

【使用[NOT] EXISTS的子查詢】
如果子查詢返回任何行，EXISTS將返回TRUE；否則返回FALSE.

【INSERT...SELECT】
將查詢結果寫入資料表
INSERT [INTO] tbl_name [(col_name,...)] SELECT ....

【多表更新】
UPDATE table_references
SET col_name1={expr|DEFAULT}
[,col_name2={expr2|DEFAULT}]...
[WHERE where_condition]

【語法結構】
table_reference
{[INNER| CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN}
table_reference
ON conditonal_expr

【連線型別】
INNER JOIN : 內連線
在MySQL中，JOIN,CROSS JOIN和INNER JOIN是等價的。
LEFT [OUTER] JOIN： 左連線
RIGHT [OUTER] JOIN: 右連線

mysql>UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate
->SET goods_cate = cate_id;

【CREATE ...SELECT】
建立資料表同時將查詢結果寫入到資料表
CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
select_statement

【連線】
MySQL在SELECT語句、多表更新、多表刪除語句中支援JOIN操作。

【資料表參照】
table_reference
tbl_name [[AS] alias] | table_subquery [AS] alias

資料表可以使用tbl_name AS alias_name或tbl_name alias_name賦予別名。
table_subquery可以作為子查詢使用在FROM子句中，這樣的子查詢必須為其賦予別名。

【連線條件】
使用ON關鍵字來設定連線條件，也可以使用WHERE來代替；
使用ON關鍵字來設定連線條件，使用WHERE關鍵字進行結果集記錄的過濾；

【內連線】
顯示左表及右表符合連線條件的記錄

【左外連線】
顯示左表的全部記錄及右表符合連線條件的記錄

【右外連線】
顯示右表的全部記錄及左表符合連線條件的記錄

【外連線】
A LEFT JOIN B join_condition
資料表B的結果集依賴資料表A
資料表A的結果集根據左連線條件依賴所有資料表（B表除外）
左外連線條件決定如何檢索資料表B（在沒有指定WHERE條件的情況下）
如果資料表A的某條記錄符合WHERE條件，但是在資料表B不存在符合連線條件的記錄，將生成一個所有列為空的額外的B行。

如果使用內連線查詢的記錄在連線資料表中不存在，並且在WHERE子句中嘗試如下操作：
col_name IS NULL時，如果col_name被定義為NOT NULL,MySQL將在找到符合連線的條件的記錄後停止搜尋更多的行。

【自身連線】
同一個資料表對其自身進行連線。

【多表刪除】
DELETE tbl_name[.*] [,tbl_name[.*]]...
FROM table_references
[WHERE where_condition]

【字元函式】
函式名稱 描述
CONCAT() 字元連線
CONCAT_WS() 使用指定的分隔符進行字元連線
FORMAT() 數字格式化
LOWER() 轉換成小寫字母
UPPER() 轉換成大寫字母
LEFT() 獲取左側字元
RIGHT() 獲取右側字元
LENGTH() 獲取字串長度
LTRIM() 刪除前導空格
RTRIM() 刪除後續空格
TRIM() 刪除前導和後續空格
SUBSTRING() 字串擷取
[NOT] LIKE 模式匹配
REPLACE() 字串替換

例如：
mysql>SELECT CONCAT('imooc','MySQL')
mysql>SELECT CONCAT_WS('imooc','-','MySQL')
mysql>SELECT CONCAT_WS('|','A','B','C');
mysql>SELECT FORMAT(12560.75,2);
mysql>SELECT FORMAT(12560.75,1);
mysql>SELECT LOWER('MySQL');
mysql>SELECT UPPER('MySQL');
mysql>SELECT LEFT('MySQL',2);
mysql>SELECT LENGTH(TRIM( 'MySQL' ));
mysql>SELECT TRIM(LEADING '?' FROM '??MySQL???');
mysql>SELECT TRIM(BOTH '?' FROM '??MySQL???');
mysql>SELECT TRIM(BOTH '?' FROM '??My??SQL???');
mysql>SELECT REPLACE('??My??SQL???','?','');
mysql>SELECT SUBSTRING('MySQL',1,2);
mysql>SELECT SUBSTRING('MySQL',3);
mysql>SELECT SUBSTRING('MySQL',-1);
mysql>SELECT * FROM test WHERE first_name LIKE '%1%%' ESCAPE '1';

【數值運算子與函式】
名稱 描述
CEIL() 進一取整
DIV 整數除法
FLOOR() 舍一取整
MOD 取餘數
POWER() 冪運算
ROUND() 四捨五入
TRUNCATE() 數字擷取

【比較運算子與函式】
名稱 描述
[NOT] BETWEEN...AND.... [不]在範圍之內
[NOT] IN() [不]在列出值範圍內
IS [NOT] NULL [不]為空

【日期時間函式】
名稱 名稱
NOW() 當前日期和時間
CURDATE() 當前日期
CURTIME() 當前時間
DATE_ADD() 日期變化
DATEDIFF() 日期差值
DATE_FORMAT() 日期格式化

mysql>SELECT DATE_ADD('2014-3-12',INTERVAL 365 DAY);
mysql>SELECT DATEDIFF('2013-3-12','2014-3-12');
mysql>SELECT DATE_FORMAT('2014-3-2','%m/%d/%Y');

【資訊函式】
名稱 描述
CONNECTION_ID() 連線ID
DATABASE() 當前資料庫
LAST_INSERT_ID() 最後插入記錄的ID號
USER() 當前使用者
VERSION() 版本資訊

【聚合函式】
名稱 描述
AVG() 平均值
COUNT() 計數
MAX() 最大值
MIN() 最小值
SUM() 求和

【加密函式】
名稱 描述
MD5() 資訊摘要演算法
PASSWORD() 密碼演算法

【自定義函式】
自定義函式的兩個必要條件：引數和返回值
函式可以返回任意型別的值，同樣可以接收這些型別的引數
●建立自定義函式
CREATE FUNCTION function_name
RETURNS
{STRING|INTEGER|REAL|DECIMAL}
routine_body

【關於函式體】
●函式體由合法的SQL語句構成；
●函式體可以是簡單的SELECT或INSERT語句；
●函式體如果為複合結構則使用BEGIN...END語句；
●複合結構可以包含宣告，迴圈，控制結構；

mysql>CREATE FUNCTION f1() RETURNS VARCHAR(30)
mysql>RETURN DATE_FORMAT(NOW(),'%Y%m%d %H:%i:%s');

mysql>CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
mysql>RETURNS FLOAT(10,2) UNSIGNED
mysql>RETURN (num1+num2)/2;

mysql>DELIMITER //
mysql>CREATE FUNCTION adduser(username VARCHAR(20))
mysql>RETURNS INT UNSIGNED
mysql>BEGIN
mysql>INSERT test(username) VALUES(username);
mysql>RETURN LAST_INSERT_ID();
mysql>END
mysql>//


【刪除函式】
DROP FUNCTION [IF EXISTS] function_name

【建立儲存過程】
CREATE
[DEFINER = {user| CURRENT_USER}]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic...] routine_body

proc_parameter:
[IN|OUT|INOUT] param_name type

【引數】
●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} : 指明誰有許可權來執行

【過程體】
●過程體由合法的SQL語句構成
●過程體可以是任意SQL語句
●過程體如果為複合結構則使用BEGIN..END語句
●複合結構可以包含宣告，迴圈，控制結構

【呼叫儲存過程】
●CALL sp_name([parameter[,...]])
●CALL sp_name[()]

mysql>DELIMITER //
mysql>CREATE PROCEDURE removeUserById(IN id INT UNSIGNED)
mysql>BEGIN
mysql>DELETE FROM users WHERE id = id;
mysql>END
mysql>//

【修改儲存過程】
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

mysql>DELIMITER //
mysql>CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)
mysql>BEGIN
mysql>DELETE FROM users WHERE id = p_id
mysql>SELECT count(id) FROM users INTO userNums;
mysql>END
mysql>//

mysql>CALL removeUserAndRerurnUserNums;
mysql>SELECT @nums;

mysql>DELIMITER //
mysql>CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED,OUT deleteUsers SMALLINT UNSIGNED,OUT userCounts SMALLINT UNSIGNED)
mysql>BEGIN
mysql>DELETE FROM users WHERE age=p_age;
mysql>SELECT ROW_COUNT() INTO deleteUsers;
mysql>SELECT COUNT(id) FROM users INTO userCounts;
mysql>END
mysql>//

【儲存過程與自定義函式的區別】
●儲存過程實現的功能要複雜一些，而函式的針對性較強；
●儲存過程可以返回多個值；函式只能有一個返回值；
●儲存過程一般獨立的來執行；而函式可以作為其他SQL語句的組成部分來出現；

【儲存引擎】
MySQL可以將資料以不同的技術儲存在檔案中，這種技術成為儲存引擎。

【MySQL支援的儲存引擎】
MyISAM
InnoDB
Memory
CSV
Archive

【關於鎖】
鎖
●共享鎖（讀鎖）
在同一時間段內，多個使用者可以讀取同一個資源，讀取過程中資料不會發生任何變化；
●排他鎖（寫鎖）
在任何時候只能有一個使用者寫入資源，當進行寫鎖時會阻塞其他的讀鎖或者寫鎖操作

鎖顆粒
●表鎖：是一種開銷最小的鎖策略
●行鎖：是一種開銷最大的鎖策略

 

【編輯資料表的預設儲存引擎】
●方式一：修改MySQL配置檔案
default storage-engine=INNODB
●方式二：使用命令修改
ALTER TABLE table_name ENGINE [=] engine_name;

【修改儲存引擎的方法】
●通過建立資料表命令實現
CREATE TABLE table_name(
...
) ENGINE = engine;

 

相關文章