Mysql數(shù)據(jù)庫(kù) 深度知識(shí)點(diǎn)

頭號(hào)碼甲 2022-05-07 發(fā)布于北京

展開(kāi)全文

停止命令：net stop mysql

啟動(dòng)命令：net start mysql

mysql登錄命令

mysql -h ip -P 端口 -u 用戶(hù)名 -p

mysql --version 或者mysql -V用于在未登錄情況下，查看本機(jī)mysql版本

select version();：登錄情況下，查看鏈接的庫(kù)版本

顯示所有數(shù)據(jù)庫(kù)：show databases;

進(jìn)入指定的庫(kù)：use 庫(kù)名;

顯示當(dāng)前庫(kù)中所有的表：show tables;

查看其他庫(kù)中所有的表：show tables from 庫(kù)名;

查看表的創(chuàng)建語(yǔ)句：show create table 表名;

查看表結(jié)構(gòu)：desc 表名;

查看當(dāng)前所在庫(kù)：select database();

mysql語(yǔ)法規(guī)范

不區(qū)分大小寫(xiě)，但建議關(guān)鍵字大寫(xiě)，表名、列名小寫(xiě)
每條命令最好用英文分號(hào)結(jié)尾
每條命令根據(jù)需要，可以進(jìn)行縮進(jìn)或換行
注釋

單行注釋?zhuān)?注釋文字
單行注釋?zhuān)?- 注釋文字，注意，這里需要加空格
多行注釋?zhuān)? **注釋文字 **/

SQL的語(yǔ)言分類(lèi)

DQL（Data Query Language）：數(shù)據(jù)查詢(xún)語(yǔ)言

select 相關(guān)語(yǔ)句
DML（Data Manipulate Language）：數(shù)據(jù)操作語(yǔ)言

insert 、update、delete 語(yǔ)句
DDL（Data Define Languge）：數(shù)據(jù)定義語(yǔ)言

create、drop、alter 語(yǔ)句
TCL（Transaction Control Language）：事務(wù)控制語(yǔ)言

set autocommit=0、start transaction、savepoint、commit、rollback

主要包括以下五大類(lèi)

整數(shù)類(lèi)型：bit、bool、tinyint、smallint、mediumint、int、bigint

浮點(diǎn)數(shù)類(lèi)型：float、double、decimal

字符串類(lèi)型：char、varchar、tinyblob、blob、mediumblob、longblob、tinytext、text、mediumtext、longtext

日期類(lèi)型：Date、DateTime、TimeStamp、Time、Year

其他數(shù)據(jù)類(lèi)型：暫不介紹，用的比較少。

類(lèi)型(n)說(shuō)明

在開(kāi)發(fā)中，我們會(huì)碰到有些定義整型的寫(xiě)法是int(11)，這種寫(xiě)法個(gè)人感覺(jué)在開(kāi)發(fā)過(guò)程中沒(méi)有什么用途，不過(guò)還是來(lái)說(shuō)一下，int(N)我們只需要記住兩點(diǎn)：

無(wú)論N等于多少，int永遠(yuǎn)占4個(gè)字節(jié)
N表示的是顯示寬度，不足的用0補(bǔ)足，超過(guò)的無(wú)視長(zhǎng)度而直接顯示整個(gè)數(shù)字，但這要整型設(shè)置了unsigned zerofill才有效

decimal采用的是四舍五入

float和double采用的是四舍六入五成雙

什么是四舍六入五成雙？

就是5以下舍棄5以上進(jìn)位，如果需要處理數(shù)字為5的時(shí)候，需要看5后面是否還有不為0的任何數(shù)字，如果有，則直接進(jìn)位，如果沒(méi)有，需要看5前面的數(shù)字，若是奇數(shù)則進(jìn)位，若是偶數(shù)則將5舍掉

char類(lèi)型占用固定長(zhǎng)度，如果存放的數(shù)據(jù)為固定長(zhǎng)度的建議使用char類(lèi)型，如：手機(jī)號(hào)碼、身份證等固定長(zhǎng)度的信息

數(shù)據(jù)類(lèi)型選擇的一些建議

選小不選大：一般情況下選擇可以正確存儲(chǔ)數(shù)據(jù)的最小數(shù)據(jù)類(lèi)型，越小的數(shù)據(jù)類(lèi)型通常更快，占用磁盤(pán)，內(nèi)存和CPU緩存更小。
簡(jiǎn)單就好：簡(jiǎn)單的數(shù)據(jù)類(lèi)型的操作通常需要更少的CPU周期，例如：整型比字符操作代價(jià)要小得多，因?yàn)樽址托?duì)規(guī)則(排序規(guī)則)使字符比整型比較更加復(fù)雜。

盡量避免NULL：盡量制定列為NOT NULL，除非真的需要NULL類(lèi)型的值，有NULL的列值會(huì)使得索引、索引統(tǒng)計(jì)和值比較更加復(fù)雜。
浮點(diǎn)類(lèi)型的建議統(tǒng)一選擇decimal
記錄時(shí)間的建議使用int或者bigint類(lèi)型，將時(shí)間轉(zhuǎn)換為時(shí)間戳格式，如將時(shí)間轉(zhuǎn)換為秒、毫秒，進(jìn)行存儲(chǔ)，方便走索引

權(quán)限生效時(shí)間

用戶(hù)及權(quán)限信息放在庫(kù)名為mysql的庫(kù)中，mysql啟動(dòng)時(shí)，這些內(nèi)容被讀進(jìn)內(nèi)存并且從此時(shí)生效，所以如果通過(guò)直接操作這些表來(lái)修改用戶(hù)及權(quán)限信息的，需要重啟mysql或者執(zhí)行flush privileges;才可以生效。

用戶(hù)登錄之后，mysql會(huì)和當(dāng)前用戶(hù)之間創(chuàng)建一個(gè)連接，此時(shí)用戶(hù)相關(guān)的權(quán)限信息都保存在這個(gè)連接中，存放在內(nèi)存中，此時(shí)如果有其他地方修改了當(dāng)前用戶(hù)的權(quán)限，這些變更的權(quán)限會(huì)在下一次登錄時(shí)才會(huì)生效。

創(chuàng)建用戶(hù)

語(yǔ)法：

create user 用戶(hù)名[@主機(jī)名] [identified by '密碼'];

說(shuō)明：

主機(jī)名默認(rèn)值為%，表示這個(gè)用戶(hù)可以從任何主機(jī)連接mysql服務(wù)器

密碼可以省略，表示無(wú)密碼登錄

通過(guò)修改mysql.user表修改密碼

use mysql;

update user set authentication_string = password('321') where user = 'test1' and host = '%';

flush privileges;

給用戶(hù)授權(quán)：

grant privileges ON database.table TO 'username'[@'host'] [with grant option]

grant命令說(shuō)明：

priveleges (權(quán)限列表)，可以是all，表示所有權(quán)限，也可以是select、update等權(quán)限，多個(gè)權(quán)限之間用逗號(hào)分開(kāi)。
ON 用來(lái)指定權(quán)限針對(duì)哪些庫(kù)和表，格式為數(shù)據(jù)庫(kù).表名，點(diǎn)號(hào)前面用來(lái)指定數(shù)據(jù)庫(kù)名，點(diǎn)號(hào)后面用來(lái)指定表名，*.* 表示所有數(shù)據(jù)庫(kù)所有表。
TO 表示將權(quán)限賦予某個(gè)用戶(hù), 格式為username@host，@前面為用戶(hù)名，@后面接限制的主機(jī)，可以是IP、IP段、域名以及%，%表示任何地方。
WITH GRANT OPTION 這個(gè)選項(xiàng)表示該用戶(hù)可以將自己擁有的權(quán)限授權(quán)給別人。注意：經(jīng)常有人在創(chuàng)建操作用戶(hù)的時(shí)候不指定WITH GRANT OPTION選項(xiàng)導(dǎo)致后來(lái)該用戶(hù)不能使用GRANT命令創(chuàng)建用戶(hù)或者給其它用戶(hù)授權(quán)。

備注：可以使用GRANT重復(fù)給用戶(hù)添加權(quán)限，權(quán)限疊加，比如你先給用戶(hù)添加一個(gè)select權(quán)限，然后又給用戶(hù)添加一個(gè)insert權(quán)限，那么該用戶(hù)就同時(shí)擁有了select和insert權(quán)限。

查看用戶(hù)有哪些權(quán)限

show grants for '用戶(hù)名'[@'主機(jī)']

show grants -- 查看當(dāng)前用戶(hù)的權(quán)限

撤銷(xiāo)用戶(hù)的權(quán)限：revoke privileges ON database.table FROM '用戶(hù)名'[@'主機(jī)'];

刪除用戶(hù)：

1.drop user '用戶(hù)名'[@'主機(jī)’]

2.delete from user where user='用戶(hù)名' and host='主機(jī)';

授權(quán)原則說(shuō)明

只授予能滿(mǎn)足需要的最小權(quán)限，防止用戶(hù)干壞事，比如用戶(hù)只是需要查詢(xún)，那就只給select權(quán)限就可以了，不要給用戶(hù)賦予update、insert或者delete權(quán)限
創(chuàng)建用戶(hù)的時(shí)候限制用戶(hù)的登錄主機(jī)，一般是限制成指定IP或者內(nèi)網(wǎng)IP段
初始化數(shù)據(jù)庫(kù)的時(shí)候刪除沒(méi)有密碼的用戶(hù)，安裝完數(shù)據(jù)庫(kù)的時(shí)候會(huì)自動(dòng)創(chuàng)建一些用戶(hù)，這些用戶(hù)默認(rèn)沒(méi)有密碼
為每個(gè)用戶(hù)設(shè)置滿(mǎn)足密碼復(fù)雜度的密碼
定期清理不需要的用戶(hù)，回收權(quán)限或者刪除用戶(hù)

總結(jié)

通過(guò)命令的方式操作用戶(hù)和權(quán)限不需要刷新，下次登錄自動(dòng)生效
通過(guò)操作mysql庫(kù)中表的方式修改、用戶(hù)信息，需要調(diào)用flush privileges;刷新一下，下次登錄自動(dòng)生效
mysql識(shí)別用戶(hù)身份的方式是：用戶(hù)名+主機(jī)
本文中講到的一些指令中帶主機(jī)的，主機(jī)都可以省略，默認(rèn)值為%，表示所有機(jī)器
mysql中用戶(hù)和權(quán)限的信息在庫(kù)名為mysql的庫(kù)中

修改列

alter table 表名 modify column 列名新類(lèi)型 [約束];

或者

alter table 表名 change column 列名新列名新類(lèi)型 [約束];

2種方式區(qū)別：modify不能修改列名，change可以修改列名

delete單表刪除

delete [別名] from 表名 [[as] 別名] [where條件];

注意：

如果無(wú)別名的時(shí)候，表名就是別名

如果有別名，delete后面必須寫(xiě)別名

如果沒(méi)有別名，delete后面的別名可以省略不寫(xiě)。

示例

-- 刪除test1表所有記錄delete from test1;

-- 刪除test1表所有記錄delete test1 from test1;

-- 有別名的方式，刪除test1表所有記錄delete t1 from test1 t1;

-- 有別名的方式刪除滿(mǎn)足條件的記錄delete t1 from test1 t1 where t1.a>100;

多表刪除

可以同時(shí)刪除多個(gè)表中的記錄，語(yǔ)法如下：

delete [別名1,別名2] from 表1 [[as] 別名1],表2 [[as] 別名2] [where條件];

說(shuō)明：

別名可以省略不寫(xiě)，但是需要在delete后面跟上表名，多個(gè)表名之間用逗號(hào)隔開(kāi)。

示例1

delete t1 from test1 t1,test2 t2 where t1.a=t2.c2;

刪除test1表中的記錄，條件是這些記錄的字段a在test.c2中存在的記錄

drop，truncate，delete區(qū)別

drop (刪除表)：刪除內(nèi)容和定義，釋放空間，簡(jiǎn)單來(lái)說(shuō)就是把整個(gè)表去掉，以后要新增數(shù)據(jù)是不可能的，除非新增一個(gè)表。

drop語(yǔ)句將刪除表的結(jié)構(gòu)被依賴(lài)的約束（constrain），觸發(fā)器（trigger）索引（index），依賴(lài)于該表的存儲(chǔ)過(guò)程/函數(shù)將被保留，但其狀態(tài)會(huì)變?yōu)椋篿nvalid。

如果要?jiǎng)h除表定義及其數(shù)據(jù)，請(qǐng)使用 drop table 語(yǔ)句。
truncate (清空表中的數(shù)據(jù))：刪除內(nèi)容、釋放空間但不刪除定義(保留表的數(shù)據(jù)結(jié)構(gòu))，與drop不同的是，只是清空表數(shù)據(jù)而已。

注意：truncate不能刪除具體行數(shù)據(jù)，要?jiǎng)h就要把整個(gè)表清空了。
delete (刪除表中的數(shù)據(jù))：delete 語(yǔ)句用于刪除表中的行。delete語(yǔ)句執(zhí)行刪除的過(guò)程是每次從表中刪除一行，并且同時(shí)將該行的刪除操作作為事務(wù)記錄在日志中保存，以便進(jìn)行進(jìn)行回滾操作。

truncate與不帶where的delete ：只刪除數(shù)據(jù)，而不刪除表的結(jié)構(gòu)（定義）

truncate table 刪除表中的所有行，但表結(jié)構(gòu)及其列、約束、索引等保持不變。

對(duì)于由foreign key約束引用的表，不能使用truncate table ，而應(yīng)使用不帶where子句的delete語(yǔ)句。由于truncate table 記錄在日志中，所以它不能激活觸發(fā)器。

delete語(yǔ)句是數(shù)據(jù)庫(kù)操作語(yǔ)言(dml)，這個(gè)操作會(huì)放到 rollback segement 中，事務(wù)提交之后才生效；如果有相應(yīng)的 trigger，執(zhí)行的時(shí)候?qū)⒈挥|發(fā)。

truncate、drop 是數(shù)據(jù)庫(kù)定義語(yǔ)言(ddl)，操作立即生效，原數(shù)據(jù)不放到 rollback segment 中，不能回滾，操作不觸發(fā) trigger。

如果有自增列，truncate方式刪除之后，自增列的值會(huì)被初始化，delete方式要分情況（如果數(shù)據(jù)庫(kù)被重啟了，自增列值也會(huì)被初始化，數(shù)據(jù)庫(kù)未被重啟，則不變）
如果要?jiǎng)h除表定義及其數(shù)據(jù)，請(qǐng)使用 drop table 語(yǔ)句
安全性：小心使用 drop 和 truncate，尤其沒(méi)有備份的時(shí)候，否則哭都來(lái)不及
刪除速度，一般來(lái)說(shuō): drop> truncate > delete

drop	truncate	delete
條件刪除	不支持	不支持	支持
刪除表結(jié)構(gòu)	支持	不支持	不支持
事務(wù)的方式刪除	不支持	不支持	支持
觸發(fā)觸發(fā)器	否	否	是

別名中有特殊符號(hào)的，比如空格，此時(shí)別名必須用引號(hào)引起來(lái)。

不等于符號(hào)的使用：

<> 這個(gè)是最早的用法。

!=是后來(lái)才加上的。

兩者意義相同，在可移植性上前者優(yōu)于后者

故而sql語(yǔ)句中盡量使用<>來(lái)做不等判斷

like（模糊查詢(xún)）

select 列名 from 表名 where 列 like pattern;

pattern中可以包含通配符，有以下通配符：

%：表示匹配任意一個(gè)或多個(gè)字符

_：表示匹配任意一個(gè)字符。

查詢(xún)運(yùn)算符、like、between and、in、not in對(duì)NULL值查詢(xún)不起效

<=>（安全等于）

<=>：既可以判斷NULL值，又可以判斷普通的數(shù)值，可讀性較低，用得較少

總結(jié)

like中的%可以匹配一個(gè)到多個(gè)任意的字符，_可以匹配任意一個(gè)字符
空值查詢(xún)需要使用IS NULL或者IS NOT NULL，其他查詢(xún)運(yùn)算符對(duì)NULL值無(wú)效
建議創(chuàng)建表的時(shí)候，盡量設(shè)置表的字段不能為空，給字段設(shè)置一個(gè)默認(rèn)值
<=>（安全等于）玩玩可以，建議少使用

語(yǔ)法：

select 列 from 表 limit [offset,] count;

說(shuō)明：

offset：表示偏移量，通俗點(diǎn)講就是跳過(guò)多少行，offset可以省略，默認(rèn)為0，表示跳過(guò)0行；范圍：[0,+∞)。

count：跳過(guò)offset行之后開(kāi)始取數(shù)據(jù)，取count行記錄；范圍：[0,+∞)。

limit中offset和count的值不能用表達(dá)式。

limit中不能使用表達(dá)式，limit后面的2個(gè)數(shù)字不能為負(fù)數(shù)

字段存在相同的值，當(dāng)排序過(guò)程中存在相同的值時(shí)，沒(méi)有其他排序規(guī)則時(shí)，mysql懵逼了，不知道怎么排序了；

建議：分頁(yè)排序時(shí)，排序不要有二義性，二義性情況下可能會(huì)導(dǎo)致分頁(yè)結(jié)果亂序，可以在后面追加一個(gè)主鍵排序

where 多字段同時(shí)限制：

SELECT

user_id 用戶(hù)id,

price 最大金額,

the_year 年份

FROM

t_order t1

WHERE

(t1.user_id , t1.price)

(SELECT

t.user_id, MAX(t.price)

FROM

t_order t

GROUP BY t.user_id);

現(xiàn)在我們來(lái)討論java輸出的順序?yàn)楹魏蛃ql不一致？

上面java代碼中兩個(gè)表的連接查詢(xún)使用了嵌套循環(huán)，外循環(huán)每執(zhí)行一次，內(nèi)循環(huán)的表都會(huì)全部遍歷一次，如果放到mysql中，就相當(dāng)于內(nèi)標(biāo)全部掃描了一次（一次全表io讀取操作），主表（外循環(huán)）如果有n條數(shù)據(jù)，那么從表就需要全表掃描n次，表的數(shù)據(jù)是存儲(chǔ)在磁盤(pán)中，每次全表掃描都需要做io操作，io操作是最耗時(shí)間的，如果mysql按照上面的java方式實(shí)現(xiàn)，那效率肯定很低。

那mysql是如何優(yōu)化的呢？

msql內(nèi)部使用了一個(gè)內(nèi)存緩存空間，就叫他join_buffer吧，先把外循環(huán)的數(shù)據(jù)放到j(luò)oin_buffer中，然后對(duì)從表進(jìn)行遍歷，從表中取一條數(shù)據(jù)和join_buffer的數(shù)據(jù)進(jìn)行比較，然后從表中再取第2條和join_buffer數(shù)據(jù)進(jìn)行比較，直到從表遍歷完成，使用這方方式來(lái)減少?gòu)谋淼膇o掃描次數(shù)，當(dāng)join_buffer足夠大的時(shí)候，大到可以存放主表所有數(shù)據(jù)，那么從表只需要全表掃描一次（即只需要一次全表io讀取操作）。

mysql中這種方式叫做Block Nested Loop。

字段值為NULL的時(shí)候，not in查詢(xún)有大坑，這個(gè)要注意

建議創(chuàng)建表的時(shí)候，列不允許為空

delimiter關(guān)鍵字 sql語(yǔ)句結(jié)束符

delimiter用來(lái)設(shè)置結(jié)束符，當(dāng)mysql執(zhí)行腳本的時(shí)候，遇到結(jié)束符的時(shí)候，會(huì)把結(jié)束符前面的所有語(yǔ)句作為一個(gè)整體運(yùn)行，存儲(chǔ)過(guò)程中的腳本有多個(gè)sql，但是需要作為一個(gè)整體運(yùn)行，所以此處用到了delimiter

/*在執(zhí)行過(guò)程中出任何異常設(shè)置hasSqlError為T(mén)RUE*/

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasSqlError=TRUE;

根據(jù)hasSqlError判斷是否有異常，做回滾和提交操作

mysql使用binlog和relaylog記錄數(shù)據(jù)庫(kù)的變化

binlog有三種格式：ROW STATEMENT MIXED ，ROW記錄national數(shù)據(jù)被修改了，修改成了什么樣子；STATEMENT 記錄修改的sql到binlog；MIXED就是以上兩種模式的混合。

binlog是一個(gè)二進(jìn)制文件，解析binlog有兩種方式：

1.在命令行使用,show binlog events in 'binlog-filename'；方式查看

2.下載分析binlog，然后通過(guò)mysqlbinlog工具進(jìn)行解析

扇區(qū)：磁盤(pán)存儲(chǔ)的最小單位，扇區(qū)一般大小為512Byte

磁盤(pán)塊：文件系統(tǒng)與磁盤(pán)交互的最小單位（計(jì)算機(jī)系統(tǒng)讀寫(xiě)磁盤(pán)的最小單位），一個(gè)磁盤(pán)塊由連續(xù)幾個(gè)扇區(qū)組成，塊一般大小為4KB

二分法查找數(shù)據(jù)的優(yōu)點(diǎn)：定位數(shù)據(jù)非?？?，前提是：目標(biāo)數(shù)組是有序的。

總結(jié)一下使用索引的一些建議

在區(qū)分度高的字段上面建立索引可以有效的使用索引，區(qū)分度太低，無(wú)法有效的利用索引，可能需要掃描所有數(shù)據(jù)頁(yè)，此時(shí)和不使用索引差不多
聯(lián)合索引注意最左匹配原則：必須按照從左到右的順序匹配，mysql會(huì)一直向右匹配直到遇到范圍查詢(xún)(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整
查詢(xún)記錄的時(shí)候，少使用*，盡量去利用索引覆蓋，可以減少回表操作，提升效率
有些查詢(xún)可以采用聯(lián)合索引，進(jìn)而使用到索引下推（IPC），也可以減少回表操作，提升效率
禁止對(duì)索引字段使用函數(shù)、運(yùn)算符操作，會(huì)使索引失效
字符串字段和數(shù)字比較的時(shí)候會(huì)使索引無(wú)效
模糊查詢(xún)'%值%'會(huì)使索引無(wú)效，變?yōu)槿頀呙?，但?值%'這種可以有效利用索引
排序中盡量使用到索引字段，這樣可以減少排序，提升查詢(xún)效率

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶(hù)發(fā)布，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購(gòu)買(mǎi)等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來(lái)自：頭號(hào)碼甲 > 《待分類(lèi)》

舉報(bào)/認(rèn)領(lǐng)