mysql基礎(chǔ)知識(shí)資料

Mysql基礎(chǔ)知識(shí)分享

1用戶及權(quán)限管理

1.1創(chuàng)建用戶

createuserusername@hostidentifiedby'password';

1.2授權(quán)

全部權(quán)限：

grantallprivilegesondatabase.tableto'username^'host';

增刪改查權(quán)限：

grantselect,update,insert,deleteondatabase.tableto'usemame^'host'

不同host的授權(quán)

本地授權(quán):grantallprivilegeson*.*to'username^'localhost';

網(wǎng)絡(luò)授權(quán):grantselectprivilegesondbl.userinfoto'username'@'60.191.%.%';

權(quán)限列表：

ALTER修改表和索引。

CREATE:創(chuàng)建數(shù)據(jù)庫和表。

DELETE:刪除表中已有的記錄。

DROP:拋棄（刪除卜數(shù)據(jù)庫和表。

INDEX:創(chuàng)建或拋棄索引。

INSERT:向表中插入新行。

SELECT:檢索表中的記錄。

UPDATE:修改現(xiàn)存表記錄。

FILE:讀或?qū)懛?wù)器上的文件。

PROCESS:杳看服務(wù)器中執(zhí)行的線程信息或殺死線程。

RELOAD:重載授權(quán)表或清空日志、主機(jī)緩存或表緩存。

SHUTDOWN:關(guān)閉服務(wù)器。

ALL:所有權(quán)限,ALLPRIVILEGES同義詞。

USAGE:特殊的"無權(quán)限"權(quán)限。

1.3取消授權(quán)

revokeallprivilegesondbl.userjnfofrom'zhangsan'@'60.191.%.%,;

1.4刪除用戶

dropuser,zhangsan'@'60.191.%.%,;

1.5查看用戶權(quán)限

showgrantsfor'zhangsan'@'%';

1.6刷新權(quán)限

flushprivileges;

1.7查看用戶有設(shè)置過權(quán)限的host

selectuser,hostfrommysql.userwhereuser='usemame,;

1.8修改密碼

root用戶修改其他用戶密碼

setpasswordfor,zhangsan'@'60.191.%.%,=,new_pwd,;

修改自己的密碼

setpassword='new_pwd'

shell命令行修改密碼

mysqladmin-uroot-pl23456password123

修改user表

updateusersetpassword=passwordC123'）whereuser='zhangsan'andhost='localhost,;

破解密碼

mysqld—skip-grant-tables模式啟動(dòng)mysql

1.9mysql權(quán)限分配規(guī)則

?一個(gè)用戶在不同的host上可能有不同的權(quán)限

?慎用,username'?'%',最好根據(jù)需要開放對(duì)應(yīng)IP的數(shù)據(jù)庫訪問權(quán)限，而且數(shù)據(jù)庫的

端口最好也只針對(duì)指定ip開放

?用戶權(quán)限是存在mysql.user表中的，一個(gè)用戶如果對(duì)應(yīng)有不同的host,在表中也是對(duì)

應(yīng)的多條記錄，所以說用戶權(quán)限是根據(jù)'usemame'@'hosts'兩個(gè)條件區(qū)分的（可以看作

兩個(gè)用戶，因?yàn)橐部梢栽O(shè)置不用的密碼），同用戶不同的host之間是沒有權(quán)限關(guān)聯(lián)

的。

?用戶賬戶包括“username"和"host"兩部分，后者表示該用戶被允許從何地接入。

tom@'%'表示任何地址，默認(rèn)可以省略。還可以是"tom@192.1681%”、

"tom@%."等。數(shù)據(jù)庫格式為db.table,可以是"test.*"或前者表示

test數(shù)據(jù)庫的所有表，后者表示所有數(shù)據(jù)庫的所有表。

2常用操作命令

2.1查看當(dāng)前登錄用戶

selectcurrent_user();

2.2查看當(dāng)前選擇數(shù)據(jù)庫

selectdatabaseO;

2.3表操作

2.3.1創(chuàng)建表

createtableuser_info3(idxintprimarykeyauto_increment,namevarchar(3O)notnull,age

int,sexboolean);

2.3.2刪除表

droptableuser_info2;

2.3.3修改表

?(修改表名altertableuser_info3renamenew_userinfo;

?修字段數(shù)據(jù)類型altertablenew_userinfomodifynamechar(20);

?修改字段名altertablenew_userinfochangesexgenderboolean;

?在最后追加新的字段altertablenew_userinfoaddaddressvarchar(200)notnull;

?在指定字段后插入新字段altertablenew_userinfoaddphonevarchar(20)notnull

afterage;

?在表的第一個(gè)位置插入新的字段altertablenew_userinfoadduuidtimestampdefault

now()first;

?刪除一個(gè)字段altertablenew_userinfodropuuid;

?更改表的存儲(chǔ)引擎altertablenew_userinfoengine=MyISAM;

?刪除外鍵altertableforiegn_tabdropforeignkeyc_fk

2.4查看當(dāng)前庫下所有的表

showtables;

2.5查看表的字段信息

desctablename;

2.6查看表的創(chuàng)建信息

showcreatetabletablebname;

2.7數(shù)據(jù)操作

2.7.1增

insertintotest(id.email,ip,state)values(2/qq@770');

2.7.2刪

?deletefrom

?deleteFROMusersWHEREname='n汰e'ORDERBYidDESCLIMIT6;

?truncatetable

?truncatetableusers;

清空數(shù)據(jù)庫

?deletefromtablename;

?truncatetabletablename;

delete和truncate的區(qū)別

truncate刪除表中的所有行，表的列、約束、索引保持不變，但標(biāo)識(shí)計(jì)數(shù)器將被垂置。

而delete會(huì)保留標(biāo)識(shí)計(jì)數(shù)器。有foreignkey約束的表和參與視圖索引的表，都不能使用

truncate。所以truncate操作是無法在事務(wù)中回滾的，但delete在事務(wù)上卜文中在

commit前是可以回滾常見的DDL命令還有CREATE/ALTER/DROP,這些操作在事務(wù)上

下文中都是無法回滾的，你可認(rèn)為它們自帶提交TRUNCATE是DDL(數(shù)據(jù)定義語言)，

DELETE是DML(數(shù)據(jù)操作語言)

2.7.3改

UPDATErunoob_tblSETrunoob_title=，學(xué)習(xí)C++*WHERErunoob_id=3;

2.7.4查

SELECT*FROM表名WHERE條件

3數(shù)據(jù)類型

mysql數(shù)據(jù)類型大致分為整形，定點(diǎn)數(shù)，浮點(diǎn)型，日期時(shí)間型，字符串類型，二進(jìn)制類型，

枚舉，集合等幾種大類型。由于過于龐雜，所以在此不多做介紹，有興趣的同學(xué)請(qǐng)?jiān)诰W(wǎng)上查

閱。

4字段約束

關(guān)系型數(shù)據(jù)庫有三大完整性

?實(shí)體完整性：實(shí)體屬性中的標(biāo)識(shí)屬性不能為空、不能重復(fù)，該約束通過制定的主鍵實(shí)

現(xiàn)，其約束有系統(tǒng)強(qiáng)制實(shí)施

?參照完整性：實(shí)體中的外鍵可以為空，但不能是錯(cuò)的。

?用戶定義完整性：某一具體的實(shí)際數(shù)據(jù)庫的約束條件，由應(yīng)用環(huán)境所決定，反映某一

具體應(yīng)用所涉及的數(shù)據(jù)必須滿足的要求。(例如定義某一行不能為空)

約束主要完成對(duì)數(shù)據(jù)的檢驗(yàn)和限制，從而保證數(shù)據(jù)庫的完整性.

4.1primarykey主鍵

定義：主鍵約束列不允許重復(fù)，即任意兩行的主鍵值都不相同：每行都具有一個(gè)主鍵值，

也不允許出現(xiàn)空值；

單列主鍵

創(chuàng)建方式L

CREATETABLEstudent(idINTPRIMARYKEY,

nameVARCHAR(20)

)

創(chuàng)建方式2：

CREATETABLEstudent(idINTNOTNULL,

nameVARCHAR(20),

CONSTRAINTPRIMARYKEY(id)

//PK.STUD.ID為約束的名字

)

聯(lián)合主鍵

有時(shí)候一個(gè)列的字段可能有重復(fù)，可以聯(lián)合多個(gè)列設(shè)為主鍵。

CREATETABLEstudent(nameVARCHAR(20),

classVARCHAR(20)z

CONSTRAINTPRIMARYKEY(name,class)

)

其他操作

〃刪除主鍵約束ALTERTABLE表名DROPPRIMARYKEY;

〃添加主鍵ALTERTABLE表名ADDPRIMARYKEY(歹U名)；

〃修改列為主犍ALTERTABLE表名MODIFY列名數(shù)據(jù)類型PRIMARYKEY;

4.2foreignkey外鍵約束

定義：外鍵約束是保證一個(gè)或兩個(gè)表之間的參照完整性，保持?jǐn)?shù)據(jù)一致性。表的外鍵可以是

另一表的主鍵，外鍵可以有重豆的，可以是空值。實(shí)現(xiàn)一對(duì)一或一對(duì)多關(guān)系。

創(chuàng)建外鍵約束

創(chuàng)建主表

CREATETABLEclasses(idINTAUTOJNCREMENTPRIMARYKEY,

nameVARCHAR(20)

)；

創(chuàng)建外鍵

〃添加關(guān)鍵字

CREATETABLEst