mysql基礎知識資料

Mysql基礎知識分享

1用戶及權限管理

1.1創(chuàng)建用戶

createuserusername@hostidentifiedby'password';

1.2授權

全部權限：

grantallprivilegesondatabase.tableto'username^'host';

增刪改查權限：

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

不同host的授權

本地授權:grantallprivilegeson*.*to'username^'localhost';

網絡授權:grantselectprivilegesondbl.userinfoto'username'@'60.191.%.%';

權限列表：

ALTER修改表和索引。

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

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

DROP:拋棄（刪除卜數據庫和表。

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

INSERT:向表中插入新行。

SELECT:檢索表中的記錄。

UPDATE:修改現存表記錄。

FILE:讀或寫服務器上的文件。

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

RELOAD:重載授權表或清空日志、主機緩存或表緩存。

SHUTDOWN:關閉服務器。

ALL:所有權限,ALLPRIVILEGES同義詞。

USAGE:特殊的"無權限"權限。

1.3取消授權

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

1.4刪除用戶

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

1.5查看用戶權限

showgrantsfor'zhangsan'@'%';

1.6刷新權限

flushprivileges;

1.7查看用戶有設置過權限的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模式啟動mysql

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

?一個用戶在不同的host上可能有不同的權限

?慎用,username'?'%',最好根據需要開放對應IP的數據庫訪問權限，而且數據庫的

端口最好也只針對指定ip開放

?用戶權限是存在mysql.user表中的，一個用戶如果對應有不同的host,在表中也是對

應的多條記錄，所以說用戶權限是根據'usemame'@'hosts'兩個條件區(qū)分的（可以看作

兩個用戶，因為也可以設置不用的密碼），同用戶不同的host之間是沒有權限關聯

的。

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

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

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

test數據庫的所有表，后者表示所有數據庫的所有表。

2常用操作命令

2.1查看當前登錄用戶

selectcurrent_user();

2.2查看當前選擇數據庫

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;

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

?修改字段名altertablenew_userinfochangesexgenderboolean;

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

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

afterage;

?在表的第一個位置插入新的字段altertablenew_userinfoadduuidtimestampdefault

now()first;

?刪除一個字段altertablenew_userinfodropuuid;

?更改表的存儲引擎altertablenew_userinfoengine=MyISAM;

?刪除外鍵altertableforiegn_tabdropforeignkeyc_fk

2.4查看當前庫下所有的表

showtables;

2.5查看表的字段信息

desctablename;

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

showcreatetabletablebname;

2.7數據操作

2.7.1增

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

2.7.2刪

?deletefrom

?deleteFROMusersWHEREname='n汰e'ORDERBYidDESCLIMIT6;

?truncatetable

?truncatetableusers;

清空數據庫

?deletefromtablename;

?truncatetabletablename;

delete和truncate的區(qū)別

truncate刪除表中的所有行，表的列、約束、索引保持不變，但標識計數器將被垂置。

而delete會保留標識計數器。有foreignkey約束的表和參與視圖索引的表，都不能使用

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

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

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

DELETE是DML(數據操作語言)

2.7.3改

UPDATErunoob_tblSETrunoob_title=，學習C++*WHERErunoob_id=3;

2.7.4查

SELECT*FROM表名WHERE條件

3數據類型

mysql數據類型大致分為整形，定點數，浮點型，日期時間型，字符串類型，二進制類型，

枚舉，集合等幾種大類型。由于過于龐雜，所以在此不多做介紹，有興趣的同學請在網上查

閱。

4字段約束

關系型數據庫有三大完整性

?實體完整性：實體屬性中的標識屬性不能為空、不能重復，該約束通過制定的主鍵實

現，其約束有系統(tǒng)強制實施

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

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

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

約束主要完成對數據的檢驗和限制，從而保證數據庫的完整性.

4.1primarykey主鍵

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

也不允許出現空值；

單列主鍵

創(chuàng)建方式L

CREATETABLEstudent(idINTPRIMARYKEY,

nameVARCHAR(20)

)

創(chuàng)建方式2：

CREATETABLEstudent(idINTNOTNULL,

nameVARCHAR(20),

CONSTRAINTPRIMARYKEY(id)

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

)

聯合主鍵

有時候一個列的字段可能有重復，可以聯合多個列設為主鍵。

CREATETABLEstudent(nameVARCHAR(20),

classVARCHAR(20)z

CONSTRAINTPRIMARYKEY(name,class)

)

其他操作

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

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

〃修改列為主犍ALTERTABLE表名MODIFY列名數據類型PRIMARYKEY;

4.2foreignkey外鍵約束

定義：外鍵約束是保證一個或兩個表之間的參照完整性，保持數據一致性。表的外鍵可以是

另一表的主鍵，外鍵可以有重豆的，可以是空值。實現一對一或一對多關系。

創(chuàng)建外鍵約束

創(chuàng)建主表

CREATETABLEclasses(idINTAUTOJNCREMENTPRIMARYKEY,

nameVARCHAR(20)

)；

創(chuàng)建外鍵

〃添加關鍵字

CREATETABLEst