一镜沽、基本操作

1.啟動mysql

net start mysql

2.停止mysql

net stop mysql

3.登陸mysql

mysql -uroot -proot -P3306 -h127.0.0.1 --prompt \u@\h \d
root@localhost user> 

prompt 操作符參數(shù) \D 完整日期 \d 當(dāng)前數(shù)據(jù)庫 \h 服務(wù)器名稱 \u 當(dāng)前用戶

4.退出mysql

exit; 
quit; 
\q;

5.常用命令

1.顯示當(dāng)前服務(wù)器版本
SELECT VERSION();

2.顯示當(dāng)前日期時間
SELECT NOW();

3.顯示當(dāng)前用戶
SELECT USER();

4.客戶端展示的編碼格式
SET NAMES GBK;

5.返回影響的數(shù)據(jù)數(shù)量
SELECT ROW_COUNT();

6.設(shè)置結(jié)束符 默認(rèn)為; 防止與MySql語句沖突
DELIMITER 

6.數(shù)據(jù)庫操作

1.創(chuàng)建數(shù)據(jù)庫
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name

2.查看當(dāng)前服務(wù)器下的數(shù)據(jù)表列表
SHOW {DATABASE | SCHEMA} [LIKE 'pattern' | WHERE expr]

3.修改數(shù)據(jù)庫編碼
ALTER {DATABASE | SCHEMA} [db_name] [DEFULT] CHARACTER SET [=] charset_name

4.刪除數(shù)據(jù)庫
DROP {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

5.打開數(shù)據(jù)庫
USE {DATABASE}

7.標(biāo)準(zhǔn)規(guī)范

• 1.關(guān)鍵字與函數(shù)名稱全部大寫
• 2.數(shù)據(jù)庫名稱，表名稱，字段名稱全部小寫
• 3.sql語句必須以分號結(jié)尾

二、數(shù)據(jù)類型與操作數(shù)據(jù)表

1.數(shù)據(jù)類型

1.整型

整型.png

2.浮點(diǎn)型

浮點(diǎn)型.png

3.時間型

時間型.png

4.字符型

字符型.png

2.修飾屬性字段

1.空值與非空 NULL, NOT NULL

2.自動編號(且必須與主鍵組合使用) AUTO_INCREMENT

3.主鍵約束 PRIMARY KEY

4.唯一約束 UNIQUE KEY

5.默認(rèn)值 DEFULE 

6.外鍵 FOREIGN KEY

3.數(shù)據(jù)表操作

1.創(chuàng)建數(shù)據(jù)表
CREATE TABLE [IF NOT EXISTS] table_name(
 column_name data_type,
 ...
 )
 
2.查看數(shù)據(jù)表
SHOW TABLE [FROM db_name] [LIKE 'pattern' | WHERE expr]

3.查看數(shù)據(jù)表創(chuàng)建結(jié)構(gòu)
SHOW CREATE TABLE db_name

4.查看數(shù)據(jù)表結(jié)構(gòu)
SHOW COLUMNS FROM tbl_name

5.數(shù)據(jù)表更名
 (1). ALTER TABLE tbl_name RENAME [TO | AS] new_col_name
 (2). RENAME TABLE tbl_name TO new_tbl_name[,tbl_name2 TO new_tbl_name2]

創(chuàng)建一張數(shù)據(jù)表例子如下:

CREATE TABLE [IF NOT EXISTS] users(
 id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(20) NOT NULL UNIQUE KEY,
 sex ENUM(1,2,3) DEFAULT 3,
 pid SMALLINT,
 FOREIGN KEY (pid) REFERENCES provinces (id)
 )

三：約束以及修改數(shù)據(jù)表

1.約束

NOT NULL (非空約束)

PRIMARY KEY (主鍵約束)

UNIQUE KEY (默認(rèn)約束)

DEFULT (默認(rèn)約束)

FOREIGN KEY (外鍵約束)

2.外鍵約束的參照操作

• 1.CASCADE: 從父表刪除或更新且自動刪除或更新子表中匹配的行
• 2.SET NULL: 從父表刪除或更新行，并設(shè)置子表中的外鍵列為NULL恨旱。如果使用該選項(xiàng)，必須保證子表列沒有指定為NOT NULL
• 3.RESTRICT: 拒絕對父表的刪除或更新操作
• 4.NO ACTION: 標(biāo)準(zhǔn)SQL的關(guān)鍵字坝疼，在MySQL中與RESTRICT相同

3.表列屬性操作

1.添加單列
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]

2.添加多列
ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition, ...) 

3.刪除列
ALTER TABLE tbl_name DROP [COLUMN] col_name

4.添加主鍵約束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name, ...)

5.添加唯一約束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (index_col_name, ...)

6.添加外鍵約束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) reference_definiton

7.添加／刪除默認(rèn)約束
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFULT}

8.刪除主鍵約束
ALTER TABLE tbl_name DROP PRIMARY KEY

9.刪除唯一約束
ALTER TABLE tbl_name DROP {INDEX | KEY} index_name

10.刪除外鍵約束
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

11.修改列定義
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

12.修改列名稱
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]

四：操作數(shù)據(jù)表中的記錄

1.INSERT 插入記錄

1. 標(biāo)準(zhǔn)的插入語句
INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},..),(...),...

2. 此方法可以使用子查詢(SubQuery)
INSERT [INTO] tbl_name SET col_name={expr | DEFAULT},...

3.此方法可以將查詢的結(jié)果插入到指定的數(shù)據(jù)表
INSERT [INTO] tbl_name [(col_name,...)] SELECT ...

2.UPDATE 更新記錄

UPDATE [LOW_PRIRITY] [IGNORE] table_reference SET col_name1={expr1 | DEFAULT} [, col_name2={expr2 | DEFAULT}] ... [WHERE where_condition]

3.DELETE 刪除記錄

DELECE FROM tbl_name [WHERE where_condition]

4.SELECT 查詢記錄

SELECT select_expr [, select_expr ...]
[
   FROM table_references
   [WHERE where_condition]
   [GROUP BY {col_name | position} [ASE | DESE], ...]
   [HAVING where_condition]
   [ORDER BY {col_name | expr | position} [ASE | DESE], ...]
   [LIMIT {[offset,] row_cont | row_cont OFFSET offset}]
]

5.WHERE 過濾條件

6.GROUP BY 對查詢結(jié)果進(jìn)行分組

[GROUP BY {col_name | position} [ASC | DESC], ...]

7.HAVING 分組條件

[HAVING where_condition]

8.ORDER BY 對查詢結(jié)果進(jìn)行排序

[ORDER BY {col_name | expr | position} [ASC | DESC], ...]

9.LIMIT 限制查詢結(jié)果返回的數(shù)量

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

10.UNION 合并兩個或多個 SELECT 語句的結(jié)果集

默認(rèn)地，UNION 操作符選取不同的值谆沃。如果允許重復(fù)的值钝凶，請使用 UNION ALL。

11.DISTINCT 方法用于返回唯一不同的值

SELECT DISTINCT select_expr [, select_expr ...] FROM table_references;

五：子查詢與連接

1.子查詢

(1).子查詢定義

子查詢（Subquery）是指出現(xiàn)在其他SQL語句內(nèi)的SELECT子句。

SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);
 其中 SELECT * FROM t1 稱為Outer Query/Outer Statement. SELECT col2 FROM t2 稱為Subquery;

• 子查詢指嵌套在查詢內(nèi)部耕陷，且必須始終出現(xiàn)在圓括號內(nèi)掂名。
• 子查詢可以包含多個關(guān)鍵字或條件，如DISTINCT,GROUP BY,ORDER BY,LIMIT,函數(shù)等哟沫。
• 子查詢的外層查詢可以是：SELECT,INSERT,UPDATE,SET或DO.

(2).使用比較運(yùn)算符的子查詢

=, >, <, >=, <=, <>, !=, <=>

1.語法結(jié)構(gòu) operand comparison_operstor subquery

2.使用ANY, SOME, ALL修飾的比較運(yùn)算符

operand comparison_operstor ANY (subquery)
operand comparison_operstor SOME (subquery)
operand comparison_operstor ALL (subquery)

(3).使用[NOT] IN 的子查詢

語法結(jié)構(gòu) operand comparison_operstor [NOT] IN (subquery)

=ANY運(yùn)算符與IN等效饺蔑。
!=ALL或<>ALL運(yùn)算符與NOT IN等效.

(4).使用[NOT] EXISTS的子查詢

如果子查詢返回任何行，EXISTS將返回TRUE;否則為FALSE.

(5).子查詢的應(yīng)用

1.將查詢的結(jié)果寫入數(shù)據(jù)表

INSERT [INTO] tbl_name [(col_name, ...)] SELECT ...

2.多表更新

UPDATE table_references
SET col_name1 = {expr1 | DEFAULT}
[, col_name2 = {expr1 | DEFAULT)] ...
[WHERE where_conditon]

3.多表更新之一步到位

CREATE...SELECT
創(chuàng)建數(shù)據(jù)表同時將查詢的結(jié)果寫入到數(shù)據(jù)表
CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition, ...)]
select_statement

2.連接

(1).基本定義

table_reference {([INNER | CROSS] JOIN)|({LEFT | RIGHT} [OUTER] JOIN)}
table_reference ON conditional_expr

(2).數(shù)據(jù)表參照 AS

table_reference tbl_name [[AS] alias] | table_subquery [AS] alias

數(shù)據(jù)表可以使用 tbl_name AS alias_name 或 tbl_name alias_name賦予別名嗜诀。
table_subquery可以作為子查詢使用在FROM子句中猾警，這樣的子查詢必須為其賦予別名.

(3).連接類型

• INNER JOIN, 內(nèi)連接(JOIN, CROSS JOIN, INNER JOIN等價)

• LEFT [OUTER] JOIN, 左外連接

• RIGHT [OUTER] JOIN, 右外連接

• ON關(guān)鍵字設(shè)定連接條件 WHERE關(guān)鍵字進(jìn)行結(jié)果記錄的過濾

• 內(nèi)連接僅顯示符合連接條件的記錄

• 左外連接顯示左表的全部記錄及右表符合連接條件的記錄

• 右外連接顯示右表的全部記錄及左表符合連接條件的記錄

(4).多表連接

SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tbd_goods AS g
INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id
INNER JOIN tdb_goods_brands AS a ON g.brand_id = b.brand_id\G

注：將 tbd_goods表中cate_id與tdb_goods_cates表中cate_id相等條件數(shù)據(jù)進(jìn)行內(nèi)連接 并輸出tdb_goods_cates中的cate_name字段，
   將 tbd_goods表中brand_id與tdb_goods_brands表中brand_id相等條件數(shù)據(jù)進(jìn)行內(nèi)連接 并輸出tdb_goods_brands中的brand_name字段隆敢，其中兩個內(nèi)連接是等價的,不存在依存關(guān)系

(5).無限制分類表設(shè)計(jì)（自身連接）

CREATE TABLE tab_goods_types(
  type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  type_name VARCHAR(20) NOT NULL,
  parent_id SMALLINT UNSIGNED NOT NULL DEFAULE 0
);

SELECT s.type_id,s.type_name,p.type_name FROM tbd_goods_types AS s LEFT JOIN tbd_goods_types AS p ON s.parent_id = p.type_id\G 

(6).多表刪除

DELETE tbl_name[.*] [,tbl_name[.*]] ...
FROM table_references
[WHERE where_condition]

例子：刪除一張表中g(shù)oods_id大的重復(fù)的數(shù)據(jù)
1.找出一張表中重復(fù)的記錄
SELECT goods_id,goods_name FROM tbd_goods GROUP BY goods_name Having count(goods_name) >= 2;

DELETE t1 FROM tbd_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tbd_goods GROUP BY goods_name Having count(goods_name) >= 2) AS t2 ON t1.goods_name = t2.goods_name WHERE t1.goods_id > t2.goods_id

六：運(yùn)算符和函數(shù)

(1).字符函數(shù)

CONCAT()                字符連接
COUCAT_WS()             使用指定的分隔符進(jìn)行字符連接
FORMAT()                數(shù)字格式化
LOWER()                 轉(zhuǎn)換成小寫字母
UPPER()                 轉(zhuǎn)換成大寫字母
LEFT()                  獲取左側(cè)字符
RIGHT()                 獲取右側(cè)字符
LENGTH()                獲取字符串長度
LTRIM()                 刪除前導(dǎo)空格
RTRIM()                 刪除后續(xù)空格
TRIM()                  刪除前導(dǎo)和后續(xù)空格
SUBSTRING()             字符串截取
[NOT] LIKE              模式匹配
REPLACE()               字符串替換

(2).數(shù)值運(yùn)算符與函數(shù)

CEIL()                  進(jìn)一取整DIV                     整數(shù)除法
FLOOR()                 舍一取整
MOD                     取余數(shù)（取模）
POWER()                 冪運(yùn)算
ROUND()                 四舍五入
TRUNCATE()              數(shù)字截取

(3).比較運(yùn)算符與函數(shù)

[NOT] BETWEEN...AND... [不]在范圍之內(nèi)
[NOT] IN()             [不]在列出的值范圍內(nèi)
IS [NOT] NULL          [不]為空

(4).日期時間函數(shù)

NOW()                  當(dāng)前日期和時間
CURDATE()              當(dāng)前日期
CURTIME()              當(dāng)前時間
DATE_ADD()             日期變化 
DATEDIFF()             日期差值
DATE_FORMAT()          日期格式化

(5).信息函數(shù)

CONNECTION_ID()        連接ID
DATEBASE()             當(dāng)前數(shù)據(jù)庫 
LAST_INSERT_ID()       最后插入記錄的ID號
USER()                 當(dāng)前用戶
VERSION()              版本信息

(6).聚合函數(shù)

AVG()                  平均值
COUNT()                計(jì)數(shù)
MAX()                  最大值
MIN()                  最小值
SUM()                  求和

(7).加密函數(shù)

MD5()                  信息摘要算法
PASSWORD()             密碼算法

七：自定義函數(shù)UDF

(1).創(chuàng)建自定義函數(shù)

基本定義:
CREATE FUNCTION function_name 
RETURNS
{STRING | INTEGER | REAL | DECIMAL}
routine_body

不帶參數(shù)的函數(shù)：
CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(), '%Y年%m月%d日 %H點(diǎn):%i分:%s秒');

創(chuàng)建帶有參數(shù)的自定義函數(shù):
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED,) RETURNS FLOAT(10,2) UNSIGNED
RETURN (num1+num2)/2;

創(chuàng)建具有復(fù)合機(jī)構(gòu)函數(shù)體的函數(shù) 
DELIMITER //  設(shè)置結(jié)束符 默認(rèn)為; 防止與MySql語句沖突
CREATE FUNCTION adduser(username VARCHAR(20)) 
RETURNS INT UNSIGNED
BEGIN
INSERT test(username) VALUES(username);
RETURN LAST_INSERT_ID();
END
//

(2).刪除函數(shù)

DROP FUNCTION [IF EXISTS] function_name

八：MySQL存儲過程

(1).創(chuàng)建存儲過程

CREATE
[DEFINER = {user | CURRENT_USER}]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

proc_parameter:
[IN | OUT | INOUT] param_name type
注:IN 表示該參數(shù)的值必須在調(diào)用過程時指定
OUT 表示該參數(shù)的值可以被存儲過程改變发皿，并且可以返回
INOUT 表示該參數(shù)的調(diào)用時指定，并且可以被改變和返回

1.創(chuàng)建不帶參數(shù)的存儲過程
CREATE PROCeDURE sql() SELECTION VERSION();
調(diào)用: CALL sql;

1.創(chuàng)建帶有IN類型參數(shù)的存儲過程
DELIMITER //  設(shè)置結(jié)束符 默認(rèn)為; 防止與MySql語句沖突
CREATE PROCeDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN
DELECTE FROM users WHERE id = p_id;
END
//
調(diào)用: CALL removeUserById(3);

2.創(chuàng)建帶有IN和OUT類型參數(shù)的存儲過程
CREATE PROCeDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED, OUT userNums INT UNSIGNED)
BEGIN
DELECTE FROM users WHERE id = p_id;
SELECT count(id) FROM users INTO userNums;
END
//
調(diào)用: CALL removeUserAndReturnUserNums(27,@nums);
使用返回值: SELECT @nums;
SET @i = 7; @nums, @i為用戶變量穴墅，全局變量

3.創(chuàng)建帶有多個OUT類型參數(shù)的存儲過程
CREATE PROCeDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED, OUT delectUsers SMALLINT UNSIGNED, OUT userCounts SMALLINT UNSIGNED)
BEGIN
DELECTE FROM users WHERE age = p_age;
SELECT ROW_COUNT() INTO delectUsers;
SELECT COUNT(id) FROM users INTO userCounts;
END
//
調(diào)用: CALL removeUserByAgeAndReturnInfos(27,@a,@b);

(2).修改存儲過程

ALTER PROCEDURE sp_name [characteristic ...]
COMMENT 'string'
| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} 
| SQL SECURITY {DEFINER | INVOKER}

CONTAINS SQL:包含SQL語句玄货，但不包含讀或?qū)憯?shù)據(jù)的語句
NO SQL:不包含SQL語句
READS SQL DATA:包含寫數(shù)據(jù)的語句
SQL SECURITY {DEFINER | INVOKER}:指明誰有權(quán)限來執(zhí)行

(3).刪除存儲過程

DROP PROCEDURE [IF EXISTS] sp_name

(4).調(diào)用存儲過程

CALL sp_name([parameter[,...]])
CALL sp_name[()]

九：MySQL存儲引擎

(1).主要存儲引擎類型

特點(diǎn)     MyISAM InnoDB Memory Archive
存儲限制  256TB  64TB   有     無
事務(wù)安全  -      支持    -      -
支持索引  支持    支持   支持    支持
鎖顆粒    表鎖   行鎖    表鎖    行鎖
數(shù)據(jù)壓縮  支持    -      -      支持
支持外鍵  -      支持    -      -

(2).性能詳解

1.并發(fā)處理

并發(fā)控制：當(dāng)多個連接對記錄進(jìn)行修改時保證數(shù)據(jù)的一致性和完整性
鎖：     共享鎖（讀鎖）   排他鎖（寫鎖）
鎖顆粒： 表鎖，開銷最小的鎖策略券躁； 行鎖也拜，開銷最大的鎖策略慢哈；

2.事務(wù)處理

事務(wù)處理：保證數(shù)據(jù)庫的完整性
事務(wù)的特性：
原子性（Atomicity）
一致性（Consistency）
隔離性（Isolation）
持久性（Durability）

3.外鍵和索引

外鍵：是保證數(shù)據(jù)一致性的策略
索引：是對數(shù)據(jù)表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu)

4.修改存儲引擎

通過創(chuàng)建數(shù)據(jù)表命令實(shí)現(xiàn)
CREATE TABLE table_name(
...
...
) ENGING = engine_name;

通過修改數(shù)據(jù)表命令實(shí)現(xiàn)
ALTER TABLE table_name ENGINE [=] engine_name;