學(xué)完mysql后，自己弄得的思維導(dǎo)圖。原圖30.6MB，太大了放不上來(lái)，這里就放個(gè)鏈接吧。
歡迎大家去看，如果有需要改正的地方，請(qǐng)告訴我，謝謝。
鏈接：https://www.zhixi.com/view/718f3805
密碼：6522

下面是MarkDown形式的的內(nèi)容，但是缺少一些圖片。完整的在思維導(dǎo)圖上。

mysql數(shù)據(jù)庫(kù)

使用DOS命令行窗口連接MySQL數(shù)據(jù)庫(kù)

連接到mysql數(shù)據(jù)服務(wù)（mysql數(shù)據(jù)庫(kù)）的指令：

mysql -h 主機(jī)IP -P 端口名 -u 用戶名 -p密碼

【注意事項(xiàng)】：

1、-p密碼 之間不要有空格。
2、-p后面如果沒(méi)有寫(xiě)密碼，回車之后會(huì)要求輸入密碼。
3、如果沒(méi)有寫(xiě) -h 主機(jī)，則默認(rèn)就是本機(jī)。
4、如果沒(méi)有寫(xiě) -P 端口名，則默認(rèn)就是3306。
5、在實(shí)際工作中，為保證安全，一般會(huì)將3306改為其他端口號(hào)。

啟動(dòng)數(shù)據(jù)庫(kù) 的常用方式

net start mysql服務(wù)名

net stop mysql服務(wù)名

安裝圖形化MySQL管理軟件

Navicat

SQLyog

數(shù)據(jù)庫(kù)的三層結(jié)構(gòu)

1、所謂的安裝mysql數(shù)據(jù)庫(kù)，就是在主機(jī)安裝一個(gè)“數(shù)據(jù)庫(kù)管理系統(tǒng)（DBMS）”，這個(gè)管理程序可以管理多個(gè)數(shù)據(jù)庫(kù)。

DBMS（Database manage system）

2、一個(gè)數(shù)據(jù)庫(kù)可以創(chuàng)建多個(gè)表，以保存數(shù)據(jù)（信息）

3、數(shù)據(jù)庫(kù)管理系統(tǒng)，數(shù)據(jù)庫(kù) 和 表的關(guān)系。如上圖所示

4、mysql數(shù)據(jù)庫(kù) – 普通表的本質(zhì)仍然是文件。表的一行（row）稱之為一條記錄，在 Java 程序中，一行記錄往往使用“對(duì)象”來(lái)表示。

SQL語(yǔ)句的分類

DDL：數(shù)據(jù)定義語(yǔ)句【create 表，庫(kù)…】

DML：數(shù)據(jù)操作語(yǔ)句【增加 insert，修改 update，刪除 delete】

DQL：數(shù)據(jù)查詢語(yǔ)句【select】

DCL：數(shù)據(jù)控制語(yǔ)句【管理數(shù)據(jù)庫(kù)：例如用戶權(quán)限（grant，revoke）】

對(duì)數(shù)據(jù)庫(kù)的增、刪、查

創(chuàng)建數(shù)據(jù)庫(kù)

create datebase [if not exists] 數(shù)據(jù)庫(kù)名 [default] character set 字符集名 [default] collate 校對(duì)規(guī)則名;

顯示數(shù)據(jù)庫(kù)語(yǔ)句

show databases;

顯示數(shù)據(jù)庫(kù)創(chuàng)建（定義信息）語(yǔ)句

show create database 數(shù)據(jù)庫(kù)名;

刪除數(shù)據(jù)庫(kù)

drop database [if exists] 數(shù)據(jù)庫(kù)名;

備份恢復(fù)數(shù)據(jù)庫(kù)

備份數(shù)據(jù)庫(kù)(在DOS命令行)

mysqldump -u 用戶名 -p -B 數(shù)據(jù)庫(kù)1 數(shù)據(jù)庫(kù)2 數(shù)據(jù)庫(kù)n > 文件名.sql

恢復(fù)數(shù)據(jù)庫(kù)（進(jìn)入mysql命令行，然后執(zhí)行）

source 文件名.sql

例如：source d:\bak.sql ;

直接將 bak.sql 的內(nèi)容放到查詢編輯器中，執(zhí)行。

備份恢復(fù)數(shù)據(jù)庫(kù)的“表”

備份庫(kù)的表

mysqldump -u 用戶名 -p密碼 數(shù)據(jù)庫(kù)1 表1 表2 表n > d:\文件名.sql

創(chuàng)建表

field: 指定列名

datatype: 指定列類型（字段類型）
character set: 如果不指定，則為所在數(shù)據(jù)庫(kù)的字符集
collate: 如果不指定，則為所在數(shù)據(jù)庫(kù)的校對(duì)規(guī)則
engine: 存儲(chǔ)引擎

mysql表類型 和 存儲(chǔ)引擎

基本介紹

• 1、mysql的表類型由存儲(chǔ)引擎（storage engines）決定，主要包括：MyISAM、innodb、Memory 等。
  2、MySQL數(shù)據(jù)表主要支持 6種類型，分別是：CSV、Memory、ARCHIVE、MGR MYISAM、MYISAM、InnoDB。
  3、這六種又分為2類：一類是“事務(wù)安全型”（transaction-safe），比如：innodb ；
  其余都屬于第二類，稱為“非事務(wù)安全”型（non-transaction-safe）【myisam 和 memory】

主要的 存儲(chǔ)引擎/表 類型特點(diǎn)

細(xì)節(jié)說(shuō)明

• 1、MyISAM不支持事務(wù)，也不支持外鍵，但是其訪問(wèn)速度快，對(duì)事物的完整性沒(méi)有要求。
  2、InnoDB存儲(chǔ)引擎提供了具有 提交、回滾和崩潰恢復(fù)能力的事務(wù)安全。但是比起MyISAM存儲(chǔ)引擎，InnoDB寫(xiě)的處理效率差一些，并且會(huì)占用更多的磁盤(pán)空間，以保留數(shù)據(jù)和索引。
  3、Memory存儲(chǔ)引擎使用“存在內(nèi)存中的內(nèi)容”來(lái)創(chuàng)建表。每個(gè)Memory 表只實(shí)際對(duì)應(yīng)一個(gè)磁盤(pán)文件。Memory類型訪問(wèn)非常得快，因?yàn)樗臄?shù)據(jù)是存儲(chǔ)在內(nèi)存中的，并且還使用Hash索引。但是一旦 MySQL服務(wù)關(guān)閉，表中的數(shù)據(jù)就會(huì)丟失掉，但是“表的結(jié)構(gòu)”還在。

如何使用MyISAM、InnoDB、Memory三種存儲(chǔ)引擎 ？

• – 查看所有的存儲(chǔ)引擎
  SHOW ENGINES;

• – innodb存儲(chǔ)引擎是前面使用過(guò)的。
  /* 1、支持事務(wù)；2、支持外鍵；3、支持行級(jí)鎖 */

如何選擇表的存儲(chǔ)引擎

• 1、如果你的應(yīng)用不需要事務(wù)，處理的只是基本的CRUD操作，那么MylSAM
  是不二選擇,速度快
  2、如果需要支持事務(wù)，選擇lnnoDB。
  3.、Memory存儲(chǔ)引擎就是將數(shù)據(jù)存儲(chǔ)在內(nèi)存中，由于沒(méi)有磁盤(pán)I./O的等待,
  速度極快。但由于是內(nèi)存存儲(chǔ)引擎，所做的任何修改在服務(wù)器重啟后都將消失。(經(jīng)典用法：用戶的在線狀態(tài))

修改存儲(chǔ)引擎

• ALTER TABLE 表名 ENGINE = 存儲(chǔ)引擎 ;

約束

primary key（主鍵）

用于唯一的標(biāo)示表行的數(shù)據(jù)，當(dāng)定義主鍵約束后，該列不能重復(fù)

【細(xì)節(jié)說(shuō)明】：

1、primary key 不能重復(fù)，而且不能為null。
2、一張表最多只能有一個(gè)主鍵，但可以是復(fù)合主鍵。
3、主鍵的指定方式有兩種
（1）在創(chuàng)建表時(shí)，直接在字段名后指定：字段名 字段類型 primary key
（2）在表定義的最后一行填寫(xiě)，此時(shí)還在（）內(nèi)部：primary key(列名)，
4、使用 desc 表名 可以看到 primary key 的情況。
5、在實(shí)際開(kāi)發(fā)中，每個(gè)表往往都會(huì)設(shè)計(jì)一個(gè)主鍵。

【舉例】

CREATE TABLE t17(
id INT PRIMARY KEY, – 表示 id 列是主鍵
name VARCHAR(32),
email VARCHAR(32)
);

復(fù)合主鍵

• 一張表最多 只能有一個(gè)主鍵，但是可以是復(fù)合主鍵（那也只能有1個(gè)）。
  CREATE TABLE t18(
  id INT PRIMARY KEY,
  name VARCHAR(32) PRIMARY KEY,-- 錯(cuò)誤
  email VARCHAR(32)
  );

• /* 演示復(fù)合主鍵的使用（id和name做成復(fù)合主鍵） */
  CREATE TABLE t18(
  id INT,
  name VARCHAR(32),
  email VARCHAR(32),
  PRIMARY KEY (id,name) – 這里就是復(fù)合主鍵，以后添加數(shù)據(jù)時(shí)，只有id和name同時(shí)發(fā)生重復(fù)時(shí)，才會(huì)報(bào)錯(cuò)。
  );

not null（非空）

如果在列上定義了not null，那么插入數(shù)據(jù)時(shí)，必須為列提供數(shù)據(jù)。

字段名 字段類型 not null

自增長(zhǎng)

基本介紹

• 在某張表中，存在一個(gè)id列（整數(shù)），我們希望在添加記錄的時(shí)候，該列從 1 開(kāi)始，自動(dòng)增長(zhǎng)。

使用細(xì)節(jié)

• 1、一般來(lái)說(shuō)，自增長(zhǎng)和 primary key 配合使用。
  2、自增長(zhǎng)也可以單獨(dú)使用（但是需要配合一個(gè) unique）
  3、自增長(zhǎng)修飾的字段為整數(shù)型的。（雖然小數(shù)也可以但是很少這樣使用）
  4、自增長(zhǎng)默認(rèn)從1開(kāi)始，也可以通過(guò)下列命令來(lái)修改
  alter table 表名 auto_increment = 新的開(kāi)始值 ；
  5、如果添加數(shù)據(jù)時(shí)，給自增長(zhǎng)字段（列）指定的有值，則以指定的值為準(zhǔn)。如果指定了自增長(zhǎng)，就按照自增長(zhǎng)的規(guī)則來(lái)添加數(shù)據(jù)。

添加 自增長(zhǎng)的的字段方式

• （1）insert into xxx(字段1，字段2，…) values(null，‘值’，…) ;

– null對(duì)應(yīng)字段1，如果字段1是自增長(zhǎng)的，那么添加之后就是1，之后2，3。

• （2）insert into xxx(字段2，…) values(‘值1’，‘值2’，…) ;

– 直接從字段2開(kāi)始寫(xiě)，值1對(duì)應(yīng)字段2，…不給字段1賦值，它會(huì)自己從1開(kāi)始增長(zhǎng)。

• （3）insert into xxx values(null, ‘值1’,…)

– 前面直接什么都不寫(xiě)，然后把所有數(shù)據(jù)的值都寫(xiě)清楚，它會(huì)自動(dòng)的添加。

舉例

• 創(chuàng)建表

• 測(cè)試自增長(zhǎng)的使用

  • INSERT INTO t24
    VALUES(NULL, ‘tom@qq.com’,‘tom’);

這里的null給的是id，因?yàn)閕d是自增長(zhǎng)的，所以雖然寫(xiě)的是null，但是實(shí)際上給的是1。再執(zhí)行一次，給tom分配的id為2。

* 執(zhí)行一次，給hsp分配的id為3

INSERT INTO t24
(email,name) VALUES(‘hsp@123.com’,‘hsp’);

• 修改默認(rèn)的自增長(zhǎng)開(kāi)始值

unique（唯一）

在定義了唯一約束后，該列的值是不能重復(fù)的。

字段名 字段類型 unique

【細(xì)節(jié)】：

1、如果沒(méi)有指定 not null，則unique字段可以有多個(gè) null。
2、一張表可以有多個(gè) unique字段。
3、unique not null 使用效果類似 primary key

foreign key（外鍵）

用于定義 主表 和 從表 之間的關(guān)系：

1、外鍵約束要定義在“從表”上，“主表”則必須要有“主鍵約束”或者“unique”約束。這樣形成的外鍵約束關(guān)系才是唯一的。
2、當(dāng)定義外鍵約束后，要求外鍵列數(shù)據(jù)必須在主表的主鍵列存在或是為null。

【舉例】學(xué)生表（從表,即：外鍵所在的表） 班級(jí)表（主表）

id, name, class_id id, class_name

1、學(xué)生表的class_id為300的，在班級(jí)表中并不存在，所以就會(huì)添加失敗，這叫外鍵約束.
2、如果學(xué)生表的jack和班級(jí)表已建立聯(lián)系再刪除班級(jí)表的id就會(huì)失敗，得先刪除學(xué)生表的jack才行，也叫外鍵約束

【基本語(yǔ)法】

foreign key(本表字段名) references 主表名(主鍵名或者unique字段名)

【細(xì)節(jié)說(shuō)明】

1、外鍵指向的主表的字段，必須是primary key 或者 unique；
2、表的類型（存儲(chǔ)引擎）是“innodb”，這樣的表才支持外鍵
3、外鍵字段的類型和關(guān)聯(lián)的主鍵字段的類型要一致，長(zhǎng)度可以不一樣。
4、外鍵字段的值，必須在主鍵字段中出現(xiàn)過(guò)，或者為“null”【前提是：外鍵字段允許為null】
5、一旦建立了 主鍵、外鍵的關(guān)系，數(shù)據(jù)就不能隨意刪除了。
6、刪除主表的記錄能否成功，要看是否有從表的外鍵指向它。如果有，則把從表中的相關(guān)記錄全部刪掉，然后再刪主表的記錄

check

check：用于強(qiáng)制行數(shù)據(jù)必須滿足的條件。

假定在sal列上，定義了check約束，并要求sal列的值在1000~2000之間。如果不在這個(gè)范圍內(nèi)就會(huì)報(bào)錯(cuò)。 【老韓提示】oracle和sql server均支持check，但是MySQL5.7 目前還不支持check，只做語(yǔ)法校驗(yàn)，但不會(huì)生效。即：語(yǔ)法上支持，但運(yùn)行不會(huì)生效, 如果不滿足check的條件，語(yǔ)句也會(huì)執(zhí)行。

【基本語(yǔ)法】：列名 類型 check (check的條件)

【舉例】

CREATE TABLE t23(
id INT PRIMARY KEY,
name VARCHAR(32),
sex VARCHAR(6) CHECK (sex IN (‘man’,‘woman’)),
sal DOUBLE CHECK (sal>1000 AND sal<2000)
);

mysql常用的的“數(shù)據(jù)類型”（列類型）

數(shù)值型（整數(shù)）的基本使用

使用規(guī)范：在滿足需求的情況下，盡量選擇占用空間小的。

#1. 如果沒(méi)有指定 unsinged , 則 Ttinyint 就是有符號(hào)

#2. 如果指定 unsinged , 則 tinyint 就是無(wú)符號(hào) 0-255

【舉例】

create table t01(id tinyint); # 有符號(hào)的
create table t01(id tinyint unsigned); # 無(wú)符號(hào)的

數(shù)值型（bit）的基本使用

【說(shuō)明】

1、bit(m) m在1~64。8是一個(gè)字節(jié)，64是8個(gè)字節(jié)。
2、添加數(shù)據(jù) 范圍按照你給的位數(shù)來(lái)確定。8位一個(gè)字節(jié)來(lái)控制，比如m=8，表示一個(gè)字節(jié)0~255
3、顯示按照bit位的方式來(lái)顯示。【0是0，1是1，2是10，3是11，255是1111 1111】
4、查詢時(shí)仍然可以按照 數(shù) 來(lái)查詢。

【舉例】

CREATE TABLE t05 (num BIT(8));
INSERT INTO t05 VALUES(2);

數(shù)值型（小數(shù)）的基本使用

float/double [unsigned]

float–單精度，double–雙精度

decimal[M,D] [unsigned]

（1）可以支持更加精確地小數(shù)位。M是小數(shù)位數(shù)（精度）的總數(shù)，D是小數(shù)點(diǎn)（標(biāo)度）后面的位數(shù)。

如：decimal(5,3):一共是5位數(shù)，其中小數(shù)有3位。

（2）如果D是0，則值沒(méi)有小數(shù)點(diǎn)或分?jǐn)?shù)部分。M最大是65，D最大是30。
【如果D被省略，默認(rèn)是0；M被省略，默認(rèn)是10】

（3）【建議】如果希望小數(shù)的精度高，推薦使用decimal。

字符串 的基本使用

char(size) 固定長(zhǎng)度字符串，最大255字符

varchar(size) 0~65535字節(jié) ：可變長(zhǎng)度字符串，最大65532字節(jié)【utf8編碼最大21844字符，1-3個(gè)字節(jié)用于記錄大小】

【utf8編碼最大21844字符，每個(gè)漢字3個(gè)字節(jié)，所以65532/3 = 21844】

【gbk編碼，每個(gè)漢字2個(gè)字節(jié)，65532/2 = 32766個(gè) 字符】

【使用細(xì)節(jié)】

細(xì)節(jié)一

char(4) // 這個(gè)4表示字符數(shù)（最大255），不是字節(jié)數(shù)，不管是中文還是英文字母都是放4個(gè)，按字符計(jì)算。

varchar(4) //這個(gè)4表示字符數(shù)，不管是中文還是字母都是以定好的表的編碼來(lái)存放數(shù)據(jù)。不管是中文還是英文，都最多存放4個(gè)，是按照字符來(lái)存放的。

細(xì)節(jié)二

char(4) 是定長(zhǎng)（固定大小），就是說(shuō)：即使插入的是‘a(chǎn)a’，也會(huì)占用分配的4個(gè)字符的空間。

varchar(4) 是變長(zhǎng)（變化的大小），就是說(shuō)，即使插入了‘a(chǎn)a’，實(shí)際占用空間大小并不是4個(gè)字符，而是按照實(shí)際占用空間來(lái)分配。

• varchar本身還需要1-3個(gè)字符來(lái)記錄存放內(nèi)容的長(zhǎng)度。L (實(shí)際數(shù)據(jù)大小) +（1-3）字節(jié)

細(xì)節(jié)三

什么時(shí)候用char？什么時(shí)候用varchar？

• 如果數(shù)據(jù)是定長(zhǎng)，推薦使用char。比如：md5的密碼，郵編，手機(jī)號(hào)，身份證號(hào)等。

• 如果一個(gè)字段的長(zhǎng)度不確定，我們使用varchar，比如：留言，文章

• 查詢速度：char > varchar

細(xì)節(jié)四

存放文本時(shí)，可以使用text數(shù)據(jù)類型。可以將 TEXT列 視為 VARCHAR列。

注意text不能有默認(rèn)值，大小0~2^16字節(jié)。
如果希望存放更多的字符，可以選擇 mediumtext 0~2^24
或者 longtext 0~2^32

日期類型 的基本使用

需要在 timestamp數(shù)據(jù)類型后添加的代碼為：

NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

刪除表

drop table 表名 ;

修改表

基本介紹

使用 alter table 語(yǔ)句追加，修改或刪除列的語(yǔ)法

添加列

alter table 表名

add (column datatype [dafault expr]
[, column datatype]…
);

【舉例】

（1）員工表 emp 的上增加一個(gè) image 列，varchar 類型(要求在 resume 后面)
ALTER TABLE emp
ADD image VARCHAR(50) NOT NULL DEFAULT ‘’
AFTER resume ;

刪除列

alter table 表名 drop (column) ;

修改列

alter table 表名

modify (column datatype [dafault expr]
[, column datatype]…
);

查看表的結(jié)構(gòu)

desc 表名 ;

修改表名

rename table 表名 to 新表名

修改表的字符集

alter table 表名 character set 字符集 ;

修改列名

【舉例】修改列名name為user_name

ALTER TABLE employee
CHANGE name user_name VARCHAR(32) NOT NULL DEFAULT ‘’ ;

刪除列

alter table 表名 drop 列名 ;

數(shù)據(jù)庫(kù)的CRUD語(yǔ)句

C[create]

R[read]
U[update]
D[delete]

insert語(yǔ)句

基本語(yǔ)法

insert into 表名 [(列名 [, 列名, …])]

values (value [, value, ...]) ;

使用細(xì)節(jié)

1、插入的數(shù)據(jù)應(yīng)與字段的數(shù)據(jù)類型相同。

2、數(shù)據(jù)的長(zhǎng)度要在列的規(guī)定范圍內(nèi)。

3、在values中列出的數(shù)據(jù)位置，必須與被加入的列的排列位置對(duì)應(yīng)。

4、字符和日期型數(shù)據(jù)應(yīng)該包含在單引號(hào)中。

5、列可以插入空值（前提是該字段允許為空） insert into table value(null)

6、insert into 表名(列名…) value(), (), () 的形式添加多條記錄。

7、如果是給表中的所有字段添加數(shù)據(jù)，可以不寫(xiě)前面的字段名稱。

8、默認(rèn)值的使用：

（1）當(dāng)不給某個(gè)字段值時(shí)，如果有默認(rèn)值就添加默認(rèn)值，否則就報(bào)錯(cuò)。
（2）如果某個(gè)列沒(méi)有指定 not null，那么當(dāng)添加數(shù)據(jù)時(shí)，沒(méi)有給定值，則默認(rèn)會(huì)給null。
（3）如果我們希望指定某個(gè)列的默認(rèn)值，可以在創(chuàng)建表時(shí)指定。

舉例

INSERT INTO goods (id, goods_name, price)

VALUES(50, ‘三星手機(jī)’, 2300),(60, ‘海爾手機(jī)’, 1800);

INSERT INTO goods VALUES(70, ‘IBM 手機(jī)’, 5000);

CREATE TABLE dept( /部門表/

deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, dname VARCHAR(20) NOT NULL DEFAULT '', loc VARCHAR(13) NOT NULL DEFAULT ""

);

update語(yǔ)句

使用update語(yǔ)句修改表中的數(shù)據(jù)

基本語(yǔ)法

update 表名

set 列名2 = 新的值 [,列名2 = 新的值 , ...][where 條件語(yǔ)句] ；

使用細(xì)節(jié)

1、update語(yǔ)法可以用新值更新原有表行中的各列。

2、set子句指示要修改哪些，要給予哪些值。

3、where子句指定 應(yīng)該更新哪些行。如果沒(méi)有where子句，則更新所有的行（記錄），所以一定要小心。

4、如果需要修改多個(gè)字段，可以通過(guò) set 字段1 = 值1, 字段2 = 值2 …

5、在set子句中，不存在 += 或 -= 之類的情況。老老實(shí)實(shí)的用 num = num +1 這樣的語(yǔ)句形式。

delete語(yǔ)句

使用delete語(yǔ)句刪除表中的數(shù)據(jù)（按行）

基本語(yǔ)法：

delete from 表名

[where 條件語(yǔ)句] ;

刪除表中的所有記錄

delete from 表名 ;

使用細(xì)節(jié)：

1、如果不使用 where子句，將會(huì)刪除表中的所有數(shù)據(jù)。

2、delete語(yǔ)句不能夠刪除某一列的值。（可以使用update 設(shè)為 null 或者 ‘’）

3、使用delete語(yǔ)句僅能夠刪除表中的記錄，不刪除表本身。如要?jiǎng)h除表，使用drop語(yǔ)句。 drop table 表名 ;

查詢表select

單表查詢

基本語(yǔ)法

select [distinct] * | {列名1, 列名2, …}

from 表名 ;

注意事項(xiàng)

1、select 指定查詢哪些列的數(shù)據(jù)。

2、select * 表示查詢所有的列

select emp.* 表示查詢emp表的所有列。

3、from 指定查詢哪張表。

4、distinct 可選，指顯示結(jié)果時(shí)，是否去掉重復(fù)數(shù)據(jù)。

使用表達(dá)式，對(duì)查詢的列進(jìn)行運(yùn)算

語(yǔ)法：

select * | {列名1 | 表達(dá)式1, 列名2 | 表達(dá)式2… }
from 表名 ；

【舉例】

SELECT name,(chinese + english + math + 10) FROM student;

在select語(yǔ)句中使用 as 語(yǔ)句給列名起一個(gè)別名

語(yǔ)法：

select 列名 as 別名 from 表名 ;

【舉例】

SELECT name AS ‘姓名’, (chinese + english + math + 10) AS total_score FROM student;

在where子句中經(jīng)常使用的運(yùn)算符

between … and … 是閉區(qū)間

使用where子句，進(jìn)行過(guò)濾查詢

使用 order by 子句排序查詢結(jié)果

基本語(yǔ)法

select 列名1, 列名2, 列名3…

from 表名order by 列名 asc | desc

使用細(xì)節(jié)

1、order by 指定排序的列，排序的列既可以是表中的列名，也可以是select語(yǔ)句后指定的列名。

2、asc – 升序

desc -- 降序

3、order by 子句應(yīng)該位于select 語(yǔ)句的結(jié)尾。

加強(qiáng)查詢

在 mysql 中,日期類型可以直接比較, 需要注意格式

SELECT * FROM emp

WHERE hiredate > ‘1992-01-01’ ;

like操作符

% 表示 0~多個(gè)任意字符

_ 表示單個(gè)任意字符

如何顯示第三個(gè)字符為大寫(xiě) O 的所有員工的姓名和工資 ？

SELECT ename, sal FROM emp
WHERE ename LIKE ‘__O%’ ;

order by 子句（多個(gè)排序條件）

按照部門號(hào)升序而雇員的工資降序排列 , 顯示雇員信息

SELECT * FROM emp
ORDER BY deptno ASC , sal DESC ;

分頁(yè)查詢

基本語(yǔ)法：

select … limit start, rows
表示 從 start+1 行開(kāi)始取，取出 rows行，start從 0 開(kāi)始計(jì)算

推導(dǎo)公式

select * from 表名
order by 列名
limit 每頁(yè)顯示記錄數(shù) * (第幾頁(yè)-1), 每頁(yè)顯示記錄數(shù)

使用分組函數(shù)和分組語(yǔ)句 group by

SELECT COUNT(*),COUNT(IF(comm IS NULL,1,NULL)) ;

如果comm列為空，如果是空的就返回1，如果不為空就返回null。

FROM emp;

顯示管理者的總?cè)藬?shù)

SELECT COUNT(*) FROM emp WHERE job = ‘MANAGER’;
或者
SELECT COUNT(DISTINCT mgr ) FROM emp; #distinct —— 去重

數(shù)據(jù)分組的總結(jié)

如果select語(yǔ)句中同時(shí)含有g(shù)roup by，having，limit，order by。那么它們的順序是：group by，having，order by，limit。

/*

select 列名1,列名2,列名3,… from 表名

group by 列名

having 條件

order by 列名

limit start,rows ;

如果順序?qū)戝e(cuò)，語(yǔ)法不會(huì)通過(guò)。

*/

【應(yīng)用案例】

請(qǐng)統(tǒng)計(jì)各部門的平均工資，并且是大于1000的，而且按照平均工資從高到低排序，取出前兩行記錄。
SELECT deptno,AVG(sal) AS ‘平均工資’
FROM emp
GROUP BY deptno
HAVING 平均工資>1000
ORDER BY 平均工資 DESC # 【分組 --> 過(guò)濾 --> 排序】
LIMIT 0,2;

多表查詢

多表查詢指的是：基于2張或者2張以上的表進(jìn)行的查詢

當(dāng)我們需要指定顯示某個(gè)表的列是，需要 表.列表

舉例

SELECT ename,sal,dname,emp.deptno FROM emp,dept #ok

WHERE emp.deptno = dept.deptno;

– ？如何顯示部門號(hào)為10的部門名、員工名和工資

SELECT dname,ename,sal,emp.deptno
FROM emp,dept
WHERE emp.deptno = dept.deptno AND emp.deptno = 10 ;

注意事項(xiàng)

在默認(rèn)情況下：當(dāng)兩個(gè)表進(jìn)行查詢時(shí)，規(guī)則：

1、從第一張表中，取出1行和第二張表的每一行進(jìn)行組合，返回多個(gè)結(jié)果。【含有兩張表的所有列，全部都包含在里面】

2、一共返回的記錄數(shù)：第一張表的行數(shù)第二張表的行數(shù)。【134=52】

3、這樣的多表查詢默認(rèn)處理的返回結(jié)果，稱為“笛卡爾集”。

4、解決多表的關(guān)鍵：在where中 寫(xiě)出正確的過(guò)濾條件。這需要程序員進(jìn)行正確的分析。
– 老韓小技巧：多表查詢的條件不能少于表的個(gè)數(shù)-1，否則會(huì)出現(xiàn)笛卡爾集

自連接

定義

在同一張表的連接查詢【將同一張表看作兩張表】

特點(diǎn)

1、把同一張表當(dāng)做兩張表使用

2、需要給表取別名 表名 表別名

3、列名不明確，可以指定列的別名 列名 as 列的別名

舉例

SELECT worker.ename AS ‘職員名’ , boss.ename AS ‘上級(jí)名’

FROM emp worker, emp boss
WHERE worker.mgr

注意事項(xiàng)

給表取別名，那么這個(gè)別名可以在查詢語(yǔ)句的 select 部分中使用，但是也只能在這個(gè)查詢語(yǔ)句中使用！

子查詢

定義

嵌入在其他sql語(yǔ)句中的select語(yǔ)句，也叫“嵌套查詢”

單行子查詢

只返回一行數(shù)據(jù)的子查詢語(yǔ)句

【舉例】

【Question】如何顯示與smith同一部門的員工？

1、先查詢smith在哪個(gè)部門，部門號(hào)是什么？
SELECT deptno
FROM emp
WHERE ename=‘SMITH’;

2、把上面的select語(yǔ)句當(dāng)做一個(gè)子查詢使用。
SELECT * FROM EMP
WHERE DEPTNO=(
SELECT deptno
FROM emp
WHERE ename=‘SMITH’
);

多行子查詢

返回多行的子查詢語(yǔ)句，使用關(guān)鍵字in

【舉例】

如何查詢和部門 10 的工作相同的雇員的 名字、崗位、工資、部門號(hào), 但是不含 10 號(hào)部門自己的雇員。

1、查詢到10號(hào)部門有哪些工作

SELECT DISTINCT job
FROM emp
WHERE deptno = 10

2、把上面查詢的結(jié)果當(dāng)做子查詢使用

SELECT ename, job, sal, deptno
FROM emp
WHERE job IN ( # 多行子查詢要使用in
SELECT DISTINCT job
FROM emp WHERE deptno = 10
) AND deptno <> 10;

子查詢當(dāng)做臨時(shí)表使用

把子查詢的語(yǔ)句用括號(hào)括起來(lái)，放在from 子句中，并在括號(hào)后面 對(duì)臨時(shí)表取別名tmp

• 【舉例】
  查詢每個(gè)部門的信息(包括：部門名,編號(hào),地址)和人員數(shù)量
  SELECT tmp.* , dname, loc
  FROM dept, (
  SELECT COUNT(*) AS per_num, deptno
  FROM emp
  GROUP BY deptno
  ) tmp
  WHERE tmp.deptno = dept.deptno ;

在多行子查詢中使用 all / any

all

• – 請(qǐng)思考：顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號(hào)。
  SELECT ename,sal,deptno
  FROM emp
  WHERE sal>ALL(
  SELECT sal
  FROM emp
  WHERE deptno = 30
  );

any

• – 請(qǐng)思考：顯示工資比部門30的其中一個(gè)（任意一個(gè)）員工的工資高的員工的姓名、工資和部門號(hào)。
  在多行查詢中使用any操作符
  SELECT ename,sal,deptno
  FROM emp
  WHERE sal>ANY(
  SELECT sal
  FROM emp
  WHERE deptno = 30
  );

多列子查詢

指的是：返回多個(gè)列的子查詢語(yǔ)句

基本語(yǔ)法

(字段1,字段2…) = (select 字段1,字段2 from…) ;

字段1與字段1匹配，字段2與字段2匹配。

舉例

【問(wèn)題】查詢與allen的部門、崗位完全相同的所有雇員（并且不含smith本人）。

– 分析：1、得到allen的部門、崗位
SELECT deptno, job
FROM emp WHERE ename = ‘ALLEN’;

– 2、把上面的查詢當(dāng)做子查詢使用，并且使用多列子查詢的語(yǔ)法來(lái)進(jìn)行匹配
SELECT * FROM emp
WHERE (deptno,job) = (
SELECT deptno, job
FROM emp WHERE ename = ‘ALLEN’
)AND ename <> ‘ALLEN’;

表的自我復(fù)制（蠕蟲(chóng)復(fù)制）

目的：為了對(duì)某個(gè) sql 語(yǔ)句進(jìn)行效率測(cè)試，我們需要海量數(shù)據(jù)時(shí)，可以使用此法為表創(chuàng)建海量數(shù)據(jù)。

復(fù)制步驟

1、先把emp表的記錄復(fù)制(遷移)到 my_tab01

【先查找數(shù)據(jù)，然后把查找的數(shù)據(jù) 列對(duì)列 的放入新的表中】

INSERT INTO my_tab01

(id, name, sal, job, deptno)
SELECT empno, ename, sal, job, deptno FROM emp;

2、自我復(fù)制

INSERT INTO my_tab01

SELECT * FROM my_tab01;

【提問(wèn)】如何刪除掉一張表中的重復(fù)記錄？

1、先創(chuàng)建一張臨時(shí)表 my_temp, 該表的結(jié)構(gòu)和my_tab02一樣。

CREATE TABLE my_temp LIKE my_tab02;

這個(gè)語(yǔ)句把my_tab02表的結(jié)構(gòu)(列)等信息，不包含數(shù)據(jù)，復(fù)制到my_temp

2、把my_temp 的記錄，通過(guò)distinct 關(guān)鍵字處理后，把記錄復(fù)制到my_temp。

INSERT INTO my_temp SELECT DISTINCT * FROM my_tab02;

DISTINCT去重

3、清除掉 my_tab02 的所有記錄。（不是刪掉 表my_tab02 本身）

DELETE FROM my_tab02;

4、把my_temp表的記錄復(fù)制到my_tab02。【可以把my_tab02刪掉drop后，再把my_temp表改名為my_tab02】

– 改表名：rename old_name to new_name;

INSERT INTO my_tab02 SELECT * FROM my_temp;

5、drop掉 臨時(shí)表my_temp。

DROP TABLE my_temp;

合并查詢

目的

有時(shí)在實(shí)際應(yīng)用中，為了合并多個(gè)select語(yǔ)句的結(jié)果，可以使用 集合操作符號(hào) union，union all。

union all

– union all 就是將查詢結(jié)果合并 (取并集)，不會(huì)去重

SELECT ename,sal,job FROM emp WHERE sal>2500

UNION ALL
SELECT ename,sal,job FROM emp WHERE job=‘MANAGER’; – 8條記錄

union

– union 該操作與union all相似，但是會(huì)“自動(dòng)去掉”結(jié)果集合中的“重復(fù)行”。

SELECT ename,sal,job FROM emp WHERE sal>2500

UNION
SELECT ename,sal,job FROM emp WHERE job=‘MANAGER’; – 6條記錄

表外鏈接

左外連接

如果左側(cè)的表完全顯示，我們就說(shuō)是“左外連接”。即使左邊的表和右邊的表如果沒(méi)有匹配的，那他也會(huì)把左邊的表全部顯示出來(lái)

基本語(yǔ)法

select … from 表1(左表) left join 表2(右表) on 條件;

右外連接

如果右側(cè)的表完全顯示，我們就說(shuō)是“右外連接”

基本語(yǔ)法

select … from 表1(左表) right join 表2(右表) on 條件;

函數(shù)

合并/統(tǒng)計(jì)函數(shù)

count

返回行的總數(shù)

基本語(yǔ)法

select count(*) | count(列名)

from 表名
[where 條件語(yǔ)句] ;

count(*) 和 count(列) 的區(qū)別

count(*) – 返回滿足條件的記錄的行數(shù)。

count(列) – 統(tǒng)計(jì)滿足條件的某列有多少個(gè)，但是會(huì)排除 為null的情況。

sum

基本語(yǔ)法

select sum(列名1) [, sum(列名2) ]

from 表名
[where 條件語(yǔ)句] ;

注意事項(xiàng)

1、sum()僅對(duì) 數(shù)值起作用。

2、對(duì)多列求和，" , "號(hào)不能少。

舉例

統(tǒng)計(jì)一個(gè)班級(jí)語(yǔ)文、英語(yǔ)、數(shù)學(xué)的成績(jī)總和

SELECT SUM(math + english + chinese) FROM student;

avg

返回滿足where條件的一列的平均值

基本語(yǔ)法

select avg(列名1) [, avg(列名2) ]

from 表名
[where 條件語(yǔ)句] ;

max/min

max()：返回滿足where條件的一列的 最大值

min()：返回滿足where條件的一列的 最小值

【舉例】

求班級(jí)最高分和最低分（數(shù)值范圍在統(tǒng)計(jì)中特別有用）
SELECT MAX(math + english + chinese), MIN(math + english + chinese)
FROM student;

group by

對(duì)列進(jìn)行分組

基本語(yǔ)法

select 列名1, 列名2, 列名3, …

from 表名
group by 列名 ;

having

對(duì)分組后的結(jié)果進(jìn)行過(guò)濾

基本語(yǔ)法

select 列名1, 列名2, 列名3, …

from 表名
group by 列名 having … ;

having一般與group by 連用

其中 group by用于對(duì)查詢結(jié)果進(jìn)行統(tǒng)計(jì)，
having 子句用于限制 分組顯示結(jié)果。

字符串相關(guān)函數(shù)

charset (str)

返回字符串str的字符集

concat (string2 [, …])

連接字符串，將多個(gè)列拼接成一列

instr (string, substring)

返回 substring在string中出現(xiàn)的位置，沒(méi)有的話，就返回0

ucase (string2)

轉(zhuǎn)換成大寫(xiě), 本身是大寫(xiě)的，還是大寫(xiě)。

lcase (string2)

轉(zhuǎn)換成小寫(xiě)

left (string2, length)

從String2中的左邊起，獲取length個(gè)字符

right (string2, length)

從String2中的右邊起，獲取length個(gè)字符

length (string)

string的長(zhǎng)度【按照字節(jié)】

SELECT LENGTH(‘韓順平’) FROM DUAL ; # 9，按照字節(jié)返回，utf8編碼一個(gè)漢字占3個(gè)字節(jié)。如果是字符的話，會(huì)返回3.

replace (str, search_str, repalce_str)

在str中用replace_str替換search_str

strcmp (string1, string2)

逐個(gè)字符比較2個(gè)字符串的大小（根據(jù)ascll碼），看這兩個(gè)string是否相等。

SELECT STRCMP(‘hanshunping’,‘hanxianchu’)FROM DUAL;返回-1，跟表本身是否區(qū)分大小寫(xiě)有關(guān)。

SELECT STRCMP(‘hanshunping’,‘hanshunping’)FROM DUAL;返回0。

substring (str, position [, length])

從str的position開(kāi)始【從1開(kāi)始計(jì)算】，取length個(gè)字符

ltrim (string2)

去除前端空格

SELECT LTRIM(’ 韓順平教育’) FROM DUAL;

rtrim (string2)

去除后端空格

SELECT RTRIM('韓順平教育 ') FROM DUAL;

trim (string2)

把左右兩端的空格都去掉

SELECT TRIM(’ 韓順平教育! ') FROM DUAL;

數(shù)學(xué)相關(guān)函數(shù)

abs (num)

絕對(duì)值

bin (decimal_number)

十進(jìn)制轉(zhuǎn)換成二進(jìn)制

ceiling (number)

向上取整，得到比number2大的最小整數(shù)

conv (number2, from_base, to_base)

進(jìn)制轉(zhuǎn)換 convert:轉(zhuǎn)換

SELECT CONV(8,10,2) FROM DUAL;

把數(shù)字8當(dāng)做十進(jìn)制來(lái)對(duì)待，讓它轉(zhuǎn)換成二進(jìn)制。

floor (number2)

向下取整，得到小于number2的最大整數(shù)

format (number, decimal_places)

保留小數(shù)位數(shù)(四舍五入)

SELECT FORMAT(78.325386,2) FROM DUAL;

保留2位小數(shù)

hex (DecimalNumber)

轉(zhuǎn)十六進(jìn)制

least (number, number2 [, …])

求最小值

mod (numerator, denominator)

求余數(shù)

rand ([seed])

返回一個(gè)隨機(jī)數(shù)，其范圍是0 <= v <= 1.0

加入數(shù)字seed后，返回的隨機(jī)數(shù)不再改變。每次更換seed的值，查詢結(jié)果都會(huì)改變。
如果已經(jīng)產(chǎn)生一個(gè)整數(shù)N，則它被用作種子值，用來(lái)產(chǎn)生重復(fù)序列。

時(shí)間日期相關(guān)函數(shù)

current_date ()

當(dāng)前日期

current_time ()

當(dāng)前時(shí)間

current_timestamp ()

當(dāng)前時(shí)間戳

SELECT CURRENT_TIMESTAMP() FROM DUAL;

• 返回結(jié)果：2022-06-19 20:49:24

date (datetime)

返回datetime的日期部分

date_add (date2, interval d_value d_type)

在date2上面加上日期/時(shí)間

SELECT *

FROM mes WHERE DATE_ADD(sendtime,INTERVAL 10 MINUTE) >= NOW();

INTERVAL 時(shí)間間隔

date_sub (date2, interval d_value d_type)

在date2上減去一個(gè)時(shí)間

datediff (date1, date2)

兩個(gè)日期差（單位是天,而且是date1-date2）

請(qǐng)?jiān)趍ysql的sql語(yǔ)句中求出2011-11-11與1990-1-1相差多少天？

SELECT DATEDIFF(‘2011-11-11’,‘1990-01-01’)/365 FROM DUAL;

timediff (date1, date2)

兩個(gè)時(shí)間差（時(shí)分秒,而且是date1-date2）

SELECT TIMEDIFF(‘13:13:13’,‘10:10:10’) FROM DUAL;

now()

當(dāng)前時(shí)間

SELECT NOW() FROM DUAL;

year (datetime)

返回datetime里的的年份

SELECT YEAR(NOW()) FROM DUAL;

month (datetime)

返回datetime里的的月份

SELECT MONTH(NOW()) FROM DUAL;

day (datetime)

返回datetime里的的天數(shù)

SELECT DAY(NOW()) FROM DUAL;

date(datetime)

返回datetime里的日期部分（年月日）

SELECT DATE(NOW()) FROM DUAL;

unix_timestamp()

返回的是從1970-1-1到現(xiàn)在的秒數(shù)，不是毫秒數(shù)。

SELECT UNIX_TIMESTAMP() FROM DUAL;

from_unixtime()

可以把一個(gè)unix_timestamp的秒數(shù)（時(shí)間戳），轉(zhuǎn)成指定格式的日期。

%Y-$m-%d 表示：年月日，這是規(guī)定好的格式。

%Y-%m-%d %H:%i:%s 表示: 年月日時(shí)分秒（默認(rèn)格式）

SELECT FROM_UNIXTIME(1655028638)FROM DUAL
SELECT FROM_UNIXTIME(1655028638,‘%Y-%m-%d’)FROM DUAL
SELECT FROM_UNIXTIME(1655028638,‘%Y-%m-%d %H:%i:%s’)FROM DUAL

# 意義：在開(kāi)發(fā)中，我們可以把一個(gè)數(shù)值當(dāng)做一個(gè)時(shí)間來(lái)記錄，然后通過(guò)from_unixtime()方法來(lái)進(jìn)行轉(zhuǎn)換。

加密和系統(tǒng)函數(shù)

user ()

查詢用戶

database()

數(shù)據(jù)庫(kù)名稱

MD5(str)

為字符串str算出一個(gè)MD5 32的字符串，常用來(lái)（用戶密碼）加密

root的密碼是：123456 -> md5加密 -> 在數(shù)據(jù)庫(kù)中存放的是加密后的密碼。

SELECT MD5(‘123456’) FROM DUAL;
SELECT LENGTH(MD5(‘123456’)) FROM DUAL; # 32位。

password (str)

加密函數(shù)。從原文密碼str計(jì)算并返回密碼字符串，通常用于連接對(duì)mysql數(shù)據(jù)庫(kù)的用戶密碼加密。

SELECT PASSWORD(‘sdyu’) FROM DUAL;

在MySQL數(shù)據(jù)庫(kù)的密碼就是用這個(gè)函數(shù)加密的。

select * from mysql.user \G

mysql.user表示: 數(shù)據(jù)庫(kù).表 的含義。

SELECT * FROM mysql.user ;

這樣不用切換數(shù)據(jù)庫(kù)，就可以查詢到其他數(shù)據(jù)庫(kù)的表。

流程控制函數(shù)

if (expr1, expe2, expr3 )

如果expr1為true，則返回expr2的值，否則返回expr3的值。

ifnull(expr1, expr2)

如果expr1不為null，則返回expr1，否則返回expr2

select case

when expr1 then expr2
when expr3 then expr4
else expr5
end ;

– select case

– when expr1 then expr2 如果expr1為True，就返回expr2，然后結(jié)束。如果expr1為false，就執(zhí)行下個(gè)when代碼
– when expr3 then expr4 如果expr3為true，就返回expr4，然后結(jié)束。
– else expr5 如果expr3為false，就返回expr5，然后結(jié)束。
– end; 【感覺(jué) 類似于多重分支語(yǔ)句】

SELECT CASE

WHEN TRUE THEN 'jack' -- jack WHEN FALSE THEN 'tom' -- 執(zhí)行else語(yǔ)句，返回mary。 ELSE 'mary' END;

如果emp表的job是clerk，則顯示 職員；如果是 manager，則顯示 經(jīng)理；如果是salesman，則顯示 銷售人員，其他的正常顯示。

SELECT ename, (SELECT CASE
WHEN job = ‘CLERK’ THEN ‘職員’ – jack
WHEN job = ‘MANAGER’ THEN ‘經(jīng)理’ – 執(zhí)行else語(yǔ)句，返回mary。
WHEN job = ‘SALESMAN’ THEN ‘銷售人員’
ELSE job
END
)AS ‘job’ FROM emp ;

索引

提高數(shù)據(jù)庫(kù)的性能，加快查詢速度（不用加內(nèi)存，不用改程序，不用調(diào)sql）

原理

如果沒(méi)有索引

會(huì)進(jìn)行全表掃描。因?yàn)檎业揭粋€(gè)結(jié)果后，不確定在表的其他位置是否還有符合條件的結(jié)果。所以要全表掃描，這樣一來(lái)會(huì)浪費(fèi)很多時(shí)間。

如果有索引

會(huì)形成一個(gè)索引的數(shù)據(jù)結(jié)構(gòu)，例如二叉樹(shù)索引，B+樹(shù)索引等。查詢效率會(huì)增加。

類型

主鍵索引：主鍵自動(dòng)的為主索引，類型 primary key

唯一索引：類型unique

普通索引：index

全文索引：fulltext，適用于MyISAM

一般開(kāi)發(fā)不適用mysql自帶的全文索引，而是使用：全文搜索 Solr 和 ElasticSearch（ES）

使用

1、查詢表是否有索引

show indexses from 表名 ;

2、添加索引

基本語(yǔ)法

【法一】

• create [unique] index 索引名稱 on 表名(列名[(length)]) [asc|desc],…) ;

【法二】

• alter table 表名 add index [索引名] (表的列名) ;

添加唯一索引

create unique index 索引名稱 on 表名 (列名) ;

添加普通索引

create index 索引名 on 表名(列名) ;

alter table 表名 add index 索引名(表的列名) ;

如何選擇 ？

如果某一列的值是不會(huì)重復(fù)的，則優(yōu)先考慮使用unique索引，否則使用普通索引。

添加主鍵索引

在使用 create創(chuàng)建完表后，使用

alter table 表名 add primary key(表的列名) ;

3、刪除索引

刪除索引

drop index 索引名 on 表名 ;

刪除主鍵索引（比較特別）

alter table 表名 drop primary key ;

4、修改索引

先刪除 原索引，然后添加 新的索引。

5、查詢索引

【方式1】 show index from 表名 ;

【方式2】show indexes from 表名 ;

【方式3】show keys from 表名 ;

【方式4】desc 表名 ;

小結(jié)

【哪些列上適合使用索引 ？】

1、較頻繁的作為查詢條件的字段，應(yīng)該創(chuàng)建索引。

2、唯一性太差的字段不適合單獨(dú)創(chuàng)建索引，即使頻繁作為查詢條件。（例如：男女性別）

3、更新非常頻繁的字段，不適合創(chuàng)建索引。（例如：用戶登陸次數(shù) 會(huì)頻繁改變）

4、不會(huì)出現(xiàn)在“where子句”中的字段不該創(chuàng)建索引。

事務(wù)

什么是“事務(wù)”？

事務(wù)用于保證數(shù)據(jù)的一致性，由一組相關(guān)的dml語(yǔ)句組成，該組的dml語(yǔ)句要么全部成功，要么全部失敗。（例如：轉(zhuǎn)賬要用事務(wù)來(lái)處理，用以保證數(shù)據(jù)的一致性）

事務(wù)和鎖

在執(zhí)行事務(wù)操作時(shí)（dml語(yǔ)句），mysql語(yǔ)句會(huì)在表上加鎖，防止其他用戶更改表的數(shù)據(jù)。這對(duì)于用戶來(lái)講十分重要。

mysql控制臺(tái)事務(wù)的幾個(gè)重要操作

start transaction 開(kāi)始一個(gè)事務(wù)

savepoint 保存點(diǎn)名，設(shè)置保存點(diǎn)

rollback to 保存點(diǎn)名，回滾事務(wù)至某一個(gè)保存點(diǎn)

rollback 回滾全部事務(wù)至事務(wù)開(kāi)始時(shí)的樣子

cmmit 提交事務(wù)，同時(shí)也會(huì)刪除從 start transaction 開(kāi)始的所有保存點(diǎn)，所有的操作生效，不能回退。

回退事務(wù) rollback

執(zhí)行回退事務(wù)時(shí)，，通過(guò)指定保存點(diǎn)可以回退到指定的點(diǎn)。

提交事務(wù) commit

使用commit語(yǔ)句可以提交事務(wù)。當(dāng)執(zhí)行了commit語(yǔ)句后，會(huì)確認(rèn)事務(wù)的變化、結(jié)束事務(wù)、刪除該事務(wù)定義的所有保存點(diǎn)、釋放鎖、數(shù)據(jù)生效。當(dāng)使用 commit語(yǔ)句結(jié)束事務(wù)后，其他會(huì)話（其他連接）可以查看到 事務(wù)變化后的新數(shù)據(jù) 。【所有數(shù)據(jù)就正式生效】

事務(wù)細(xì)節(jié)討論

1、如果不開(kāi)始事務(wù)，默認(rèn)情況下，dml操作是自動(dòng)提交的，不能回滾。

2、如果開(kāi)始一個(gè)事務(wù)，而我們又沒(méi)有創(chuàng)建保存點(diǎn)，可以執(zhí)行 rollback ，默認(rèn)退回到該事務(wù)開(kāi)始時(shí)的狀態(tài)。
3、當(dāng)事務(wù)還沒(méi)有提交時(shí)，可以在事務(wù)中創(chuàng)建多個(gè)保存點(diǎn)。
（比如：savepoint aaa ; 執(zhí)行dml，savepoint bbb ;）
4、可以在事務(wù)沒(méi)有提交前，選擇退回到哪個(gè)保存點(diǎn)。
5、mysql 的事務(wù)機(jī)制需要 innodb 的存儲(chǔ)引擎 才可以使用，MyISAM 不好使。
6、開(kāi)啟一個(gè)事務(wù)有2種方式
（1）start transaction
（2）set autocommit=off

事務(wù)的隔離級(jí)別

多個(gè)連接開(kāi)啟各自事務(wù)操作數(shù)據(jù)庫(kù)中的數(shù)據(jù)時(shí)，數(shù)據(jù)庫(kù)系統(tǒng)要負(fù)責(zé)隔離操作，以保證各個(gè)連接在獲取數(shù)據(jù)時(shí)的準(zhǔn)確性。

如果不考慮隔離性，可能會(huì)引發(fā)：臟讀、不可重復(fù)讀、幻讀 3種問(wèn)題。

臟讀（dirty read）

當(dāng)一個(gè)事物讀取到另外一個(gè)事務(wù)“尚未提交的改變（update，insert，delete）”時(shí)，產(chǎn)生臟讀。

不可重復(fù)讀（nonrepeatable read）

同一查詢?cè)谕皇挛镏卸啻芜M(jìn)行，由于“其他提交事物所做的 修改 和 刪除”，每次都會(huì)返回不同的結(jié)果集，此時(shí)發(fā)生不可重復(fù)讀。

幻讀（phantom read）

同一查詢?cè)谕皇挛镏卸啻芜M(jìn)行，由于“其他提交事務(wù)所做的 插入 ”操作，每次返回不同的結(jié)果集，此時(shí)發(fā)生幻讀。

事務(wù)的隔離級(jí)別

MySQL的隔離級(jí)別定義了“事務(wù)與事務(wù)之間的隔離程度”。

設(shè)置事務(wù)的隔離級(jí)別

1、查看當(dāng)前會(huì)話的隔離級(jí)別（指某一個(gè)用戶的）

SELECT @@tx_isolation ;

2、查看系統(tǒng)當(dāng)前的隔離級(jí)別（即：所有用戶登陸時(shí)的隔離級(jí)別）

SELECT @@global.tx_isolation ;

3、設(shè)置當(dāng)前會(huì)話的隔離級(jí)別

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

READ UNCOMMITTED 是4個(gè)隔離級(jí)別之一

4、設(shè)置系統(tǒng)的當(dāng)前隔離級(jí)別

SET GLOBAL TRANSACTION ISOLATION LEVEL [你設(shè)置的級(jí)別] ;

– mysql默認(rèn)的事務(wù)隔離界別是 repeatable read, 一般情況下，沒(méi)有特殊的要求，沒(méi)有必要修改

– （因?yàn)樵摷?jí)別可以滿足絕大部分項(xiàng)目的需求）

事務(wù)的ACID

1、原子性（Atomicity）

事務(wù)是一個(gè)不可分割的工作單位，事務(wù)中的操作要么都發(fā)生，要么都不發(fā)生。

2、一致性（Consistency）
事務(wù)必須使數(shù)據(jù)庫(kù)從一個(gè)一致性狀態(tài)轉(zhuǎn)變?yōu)榱硪粋€(gè)一致性狀態(tài)。
（事務(wù)一旦提交了，就會(huì)進(jìn)行一個(gè)整體性的改變。這樣事務(wù)就結(jié)束，然后就再開(kāi)啟一個(gè)新的事務(wù)）

3、隔離性（Isolation）
多個(gè)用戶并發(fā)訪問(wèn)數(shù)據(jù)庫(kù)時(shí)，數(shù)據(jù)庫(kù)為每一個(gè)用戶開(kāi)啟的事務(wù)，不能被其他事務(wù)的 操作數(shù)據(jù)所干擾，多個(gè)并發(fā)事務(wù)之間一定要相互隔離。

4、持久性（Durability）
一個(gè)事務(wù)一旦被提交，它對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的改變就是永久性的，接下來(lái)即使數(shù)據(jù)庫(kù)發(fā)生故障，也不會(huì)對(duì)其有任何影響。

視圖

什么是視圖 ？什么是基表 ？

視圖是一個(gè)虛擬表，其內(nèi)容由查詢定義。同真實(shí)的表一樣，視圖包含列，其數(shù)據(jù)來(lái)自對(duì)應(yīng)的真實(shí)表（基表）。

視圖與基表的關(guān)系

【對(duì)視圖的總結(jié)】

1、視圖是根據(jù)基表（可以是多個(gè)基表）來(lái)創(chuàng)建的，視圖是虛擬的表。
2、視圖也有列，數(shù)據(jù)來(lái)自基表
3、通過(guò)視圖可以修改基表的數(shù)據(jù)
4、基表的改變，也會(huì)影響到視圖的數(shù)據(jù)。

“視圖”與“基表” 之間的是 “映射關(guān)系”，一個(gè)視圖 可以對(duì)應(yīng) 多個(gè)基表。

視圖的基本使用

基本語(yǔ)法

1、create view 視圖名 as select 語(yǔ)句

2、alter view 視圖名 as select 語(yǔ)句
3、show view 視圖名 as select 語(yǔ)句
4、drop view 視圖名1, 視圖名2

select語(yǔ)句是 根據(jù) 基表 來(lái)進(jìn)行選擇。

舉例

使用細(xì)節(jié)

1、創(chuàng)建視圖后，到數(shù)據(jù)庫(kù)去看，對(duì)應(yīng)視圖只有一個(gè)視圖結(jié)構(gòu)文件。（文件形式：視圖名.frm）

2、視圖的數(shù)據(jù)變化會(huì)影響到基表，基表的數(shù)據(jù)變化也會(huì)影響到視圖（insert update delete）

3、視圖中可以再使用視圖，數(shù)據(jù)仍來(lái)自于基表。

舉例

視圖的最佳實(shí)踐

mysql管理

mysql用戶

使用原因：

當(dāng)我們做項(xiàng)目開(kāi)發(fā)是時(shí)，可以根據(jù)不同的開(kāi)發(fā)人員，賦給他相應(yīng)的mysql操作權(quán)限
所以MySQL數(shù)據(jù)庫(kù)管理人員根據(jù)需要?jiǎng)?chuàng)建不同的用戶，賦給相應(yīng)的權(quán)限，供人員使用。

mysql中的用戶，都存儲(chǔ)在 系統(tǒng)數(shù)據(jù)庫(kù)mysql里的user表中。

創(chuàng)建用戶，同時(shí)指定密碼

create user ‘用戶名’ @‘允許登陸位置’ identified by ‘密碼’ ;

刪除用戶

drop user ‘用戶名’ @‘允許登陸位置’ ;

用戶修改密碼

修改自己的密碼

set password = password(‘新密碼’) ;

修改他人的密碼（需要有修改他人密碼的權(quán)限）

set password for ‘用戶名’@‘登錄位置’ = password(‘密碼’) ；

登錄

不同的數(shù)據(jù)庫(kù)用戶，登錄到DBMS后，根據(jù)相應(yīng)的權(quán)限，可以操作的數(shù)據(jù)庫(kù)和數(shù)據(jù)對(duì)象（表、視圖、觸發(fā)器）不一樣

mysql中的權(quán)限

給用戶授權(quán)

基本語(yǔ)法

grant 權(quán)限列表 on 庫(kù).對(duì)象名 to ‘用戶名’@‘登錄位置’ [identified by ‘密碼’] ;

說(shuō)明

1、權(quán)限列表，多個(gè)權(quán)限用逗號(hào)分隔開(kāi)

grant select on …
grant select, delete, create on …
grant all [privileges] on … //表示賦予該用戶在該對(duì)象上的所有權(quán)限。

2、特別說(shuō)明

. ：代表本系統(tǒng)中的所有數(shù)據(jù)庫(kù)的所有對(duì)象（表、視圖、存儲(chǔ)過(guò)程等）
庫(kù).* ：表示某個(gè)數(shù)據(jù)庫(kù)中的所有數(shù)據(jù)對(duì)象（表、視圖、存儲(chǔ)過(guò)程等）

3、identified by 可以省略，也可以寫(xiě)出

（1）如果用戶存在，就是修改用戶的密碼
（2）如果該用戶不存在，就是創(chuàng)建該用戶！

回收用戶授權(quán)

基本語(yǔ)法

revoke 權(quán)限列表 on 庫(kù).對(duì)象名 from ‘用戶名’@‘登錄位置’ ;

舉例

權(quán)限生效指令

如果權(quán)限沒(méi)有生效，就執(zhí)行下面的指令：

【基礎(chǔ)語(yǔ)法】
flush privileges ;

細(xì)節(jié)說(shuō)明

1、在創(chuàng)建用戶時(shí)，如果不指定主機(jī)Host，則為 % 。

% 表示所有的IP都有連接權(quán)限。
create user xxx ;

2、也可以這樣指定

create user ‘xxx’@‘192.168.1.%’ ; —— 表示 xxx用戶在192.168.1.*的ip可以登錄mysql

3、在刪除數(shù)據(jù)庫(kù)的時(shí)候，如果host 不是 %，就需要明確指定 ‘用戶’@‘host值’

CREATE TABLE customer(

customer_id CHAR(8) PRIMARY KEY, -- 程序員自己決定`name` VARCHAR(64) NOT NULL DEFAULT '', address VARCHAR(64) NOT NULL DEFAULT '', email VARCHAR(64) UNIQUE NOT NULL, sex ENUM('男','女') NOT NULL , -- 這里使用的枚舉類型, 是生效card_Id CHAR(18)

);

總結(jié)

以上是生活随笔為你收集整理的mysql数据库——思维导图的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。