MySQL數(shù)據(jù)庫應(yīng)用實(shí)戰(zhàn)教程-第3章-MySQL-常用操作

MySQL數(shù)據(jù)庫應(yīng)用實(shí)戰(zhàn)教程第3章MySQL常用操作本章要點(diǎn)數(shù)據(jù)庫用戶管理■數(shù)據(jù)庫操作■表操作■數(shù)據(jù)操作■字段操作■客戶端操作數(shù)據(jù)庫■小結(jié)■

本章采用命令行的方式來講解MySQL的常用操作。通過本章，我們來學(xué)習(xí)如何管理數(shù)據(jù)庫用戶、連接MySQL服務(wù)、新增用戶以及修改密碼；創(chuàng)建數(shù)據(jù)庫、修改數(shù)據(jù)庫、刪除數(shù)據(jù)庫以及查看數(shù)據(jù)庫操作；創(chuàng)建表、修改表結(jié)構(gòu)、復(fù)制表以及臨時(shí)表的使用；設(shè)置主鍵、設(shè)置復(fù)合主鍵、添加字段、改變字段類型、重命名、設(shè)置默認(rèn)值以及自增字段的使用；插入數(shù)據(jù)、修改數(shù)據(jù)、刪除數(shù)據(jù)、查詢數(shù)據(jù)。除了使用命令行的方式來進(jìn)行MySQL操作外，也可以使用客戶端來進(jìn)行操作，可以安裝NavicatforMySQL客戶端來進(jìn)行MySQL的操作。3.1數(shù)據(jù)庫用戶管理3.1.1連接MySQL連接MySQL包括兩方面內(nèi)容：一方面是連接本地MySQL；另一方面是連接遠(yuǎn)程MySQL。連接MySQL的命令格式如下。mysql-h主機(jī)地址-u用戶名－p用戶密碼（1）連接本地MySQL。如果本地安裝了MySQL數(shù)據(jù)庫服務(wù)，用戶名是root，密碼是123456，可按如下步驟連接MySQL。打開DOS命令行操作界面，進(jìn)入到mysql/bin目錄下面，輸入命令“mysql-uroot-p123456”，按回車鍵后就可以進(jìn)入到MySQL中。如果剛安裝好MySQL，超級(jí)用戶root是沒有密碼的，故直接按回車鍵即可進(jìn)入到MySQL中。（2）連接遠(yuǎn)程MySQL。如果遠(yuǎn)程有一臺(tái)MySQL服務(wù)器，IP是10.120.71.89，用戶名是root，密碼是123456，可按如下步驟連接MySQL。3.1.2新增用戶MySQL數(shù)據(jù)庫在安裝的時(shí)候會(huì)創(chuàng)建一個(gè)管理員root用戶，那么如果想再新增用戶怎么辦呢？新增用戶的命令格式如下。GRANTSELECTon數(shù)據(jù)庫.*to用戶名@登錄主機(jī)identifiedby“密碼”新增一個(gè)用戶，用戶名為shopdb，密碼為shopdb_123456。讓它可以在任何主機(jī)上登錄，并對(duì)所有數(shù)據(jù)庫有查詢、插入、修改、刪除的權(quán)限。3.1.3修改用戶密碼創(chuàng)建好的用戶如果要修改密碼，要更新MySQL的用戶表，在5.7版本的MySQL中，密碼存放在authentication_string中，需要對(duì)它進(jìn)行修改。在修改密碼的時(shí)候，用戶需要獲得reload權(quán)限，否則使用flushprivileges刷新MySQL的系統(tǒng)權(quán)限相關(guān)表會(huì)報(bào)錯(cuò)，可以使用如下命令進(jìn)行授權(quán)。GRANTreloadON*.*to'shopdb'@'%';如果不對(duì)用戶授予reload權(quán)限，也可以在更新密碼之后重啟MySQL服務(wù)，不使用flushprivileges刷新MySQL的系統(tǒng)權(quán)限相關(guān)表。3.2數(shù)據(jù)庫操作MySQL允許創(chuàng)建多個(gè)數(shù)據(jù)庫，每個(gè)數(shù)據(jù)庫承載不同的內(nèi)容，使用命令操作可以查看數(shù)據(jù)庫、創(chuàng)建數(shù)據(jù)庫、使用數(shù)據(jù)庫以及刪除數(shù)據(jù)庫。3.2.1查看數(shù)據(jù)庫使用SHOWdatabases命令可以查看有哪些數(shù)據(jù)庫。3.2.2創(chuàng)建數(shù)據(jù)庫使用CREATEDATABASEdatabaseName命令可以創(chuàng)建數(shù)據(jù)庫。3.2.3使用數(shù)據(jù)庫要操作某個(gè)數(shù)據(jù)庫或者數(shù)據(jù)庫里的表，首先需選擇要使用的數(shù)據(jù)庫，使用USEdatabaseName命令進(jìn)入到數(shù)據(jù)庫里。3.2.4刪除數(shù)據(jù)庫使用命令DROPDATABASEdatabaseName可以刪除數(shù)據(jù)庫。3.3表操作MySQL表操作是使用頻率最高的操作。用戶可以創(chuàng)建數(shù)據(jù)庫表，查看數(shù)據(jù)庫表，向數(shù)據(jù)庫表里插入數(shù)據(jù)、更新數(shù)據(jù)、刪除數(shù)據(jù)等，同時(shí)可以修改表結(jié)構(gòu)、復(fù)制表、使用臨時(shí)表。3.3.1創(chuàng)建表創(chuàng)建一個(gè)shop數(shù)據(jù)庫，在shop數(shù)據(jù)庫里新建一個(gè)user用戶表，包括用戶id、姓名、性別、年齡、密碼。創(chuàng)建數(shù)據(jù)庫表首先要進(jìn)入到shop數(shù)據(jù)庫里，然后使用createtableuser()命令來創(chuàng)建，括號(hào)里面是數(shù)據(jù)表的字段，包括用戶（id）、姓名（name）、性別（sex）、年齡（age）、密碼（password）。設(shè)置表的用戶id作為主鍵，自動(dòng)遞增并且不為空，同時(shí)設(shè)置字段的數(shù)據(jù)類型，存儲(chǔ)引擎采用Innodb數(shù)據(jù)庫引擎。3.3.2查看表結(jié)構(gòu)使用DESCtableName可以查看表結(jié)構(gòu)。3.3.3復(fù)制表MySQL可以快速復(fù)制表結(jié)構(gòu)及數(shù)據(jù)，它以要復(fù)制表的結(jié)構(gòu)和數(shù)據(jù)為基礎(chǔ)，可以快速創(chuàng)建相同表結(jié)構(gòu)和數(shù)據(jù)到新的表里，在開發(fā)過程中，可以復(fù)制一個(gè)新表作為測試表，而不用操作正式的表，以保證正在運(yùn)行的數(shù)據(jù)不被破壞。復(fù)制表提供了兩種方式：一種方式是可以復(fù)制表結(jié)構(gòu)、數(shù)據(jù)、主鍵、索引；另一種方式是只能復(fù)制表結(jié)構(gòu)、數(shù)據(jù)，不能復(fù)制主鍵和索引。1．第一種方式：復(fù)制表結(jié)構(gòu)、數(shù)據(jù)、主鍵、索引復(fù)制表結(jié)構(gòu)、主鍵、索引，可執(zhí)行如下命令。CREATETABLEnew_tablelikeold_table;插入數(shù)據(jù)，可執(zhí)行如下命令。INSERTTABLEnew_tableSELECT*FROMold_table;2．第二種方式：復(fù)制表結(jié)構(gòu)、數(shù)據(jù)，不能復(fù)制主鍵、索引

復(fù)制表結(jié)構(gòu)、數(shù)據(jù)，可執(zhí)行如下命令。CREATETABLEnew_tableSELECT*FROMold_table;復(fù)制表結(jié)構(gòu)，不復(fù)制數(shù)據(jù)，可執(zhí)行如下命令。CREATETABLEnew_tableSELECT*FROMold_tableWHERE0;3.3.4臨時(shí)表和內(nèi)存表MySQL臨時(shí)表主要用于對(duì)大數(shù)據(jù)量表做一個(gè)臨時(shí)表，以提高查詢速度。臨時(shí)表建在內(nèi)存里，數(shù)據(jù)在內(nèi)存里，缺省存儲(chǔ)引擎為MySQL服務(wù)器默認(rèn)引擎，引擎類型只能是MEMORY（HEAP）、MyISAM、MERGE、InnoDB。

MySQL內(nèi)存表也可以對(duì)大數(shù)據(jù)量表做一個(gè)臨時(shí)表，以提高查詢速度，會(huì)把表結(jié)構(gòu)存放在磁盤上，把數(shù)據(jù)放在內(nèi)存中，缺省存儲(chǔ)引擎為MEMORY。創(chuàng)建臨時(shí)表，命令執(zhí)行如下所示。CREATEtemporaryTABLEtmp1(idintnotnull);創(chuàng)建內(nèi)存表，命令執(zhí)行如下所示。CREATETABLEtmp2(idintnotnull)ENGINE=MEMORY;

臨時(shí)表和內(nèi)存表的區(qū)別如下。（1）臨時(shí)表的表結(jié)構(gòu)和數(shù)據(jù)都保存在內(nèi)存里；內(nèi)存表的表結(jié)構(gòu)保存在磁盤里，數(shù)據(jù)和索引保存在內(nèi)存里。（2）臨時(shí)表使用的缺省存儲(chǔ)引擎為MySQL服務(wù)器默認(rèn)引擎；內(nèi)存表使用的存儲(chǔ)引擎為MEMORY服務(wù)器引擎。（3）臨時(shí)表可以通過參數(shù)tmp_table_size來設(shè)定臨時(shí)表的大小；內(nèi)存表可以通過參數(shù)max_heap_table_size來設(shè)定內(nèi)存表的大小。（4）臨時(shí)表到達(dá)tmp_table_size設(shè)定的內(nèi)存上限后將在磁盤上創(chuàng)建臨時(shí)文件；內(nèi)存表到達(dá)max_heap_table_size設(shè)定的內(nèi)存上限后將報(bào)錯(cuò)。（5）臨時(shí)表可以包含TEXT、BLOB等字段；內(nèi)存表不能包含TEXT、BLOB等字段。（6）臨時(shí)表一般比較少用，通常是在應(yīng)用程序中動(dòng)態(tài)創(chuàng)建或者由MySQL內(nèi)部根據(jù)SQL執(zhí)行計(jì)劃自己創(chuàng)建；內(nèi)存表則大多作為Cache來使用，特別在沒有第三方Cache使用時(shí)，隨著memcache、NoSQL的流行，越來越少選擇使用內(nèi)存表。（7）臨時(shí)表只在當(dāng)前連接可見，當(dāng)關(guān)閉連接時(shí)，MySQL會(huì)自動(dòng)刪除表并釋放所有空間；內(nèi)存表在MySQL重啟后，主鍵、自增、索引仍然存在，只是數(shù)據(jù)丟失。3.4數(shù)據(jù)操作插入、查詢、修改和刪除是MySQL數(shù)據(jù)庫的4種最基本的操作，在項(xiàng)目開發(fā)的過程中也使用得最頻繁。在MySQL里，插入使用INSERT關(guān)鍵字、查詢使用SELECT關(guān)鍵字、修改使用UPDATE關(guān)鍵字、刪除使用DELETE關(guān)鍵字。3.4.1插入（INSERT）、查詢（SELECT）將一條數(shù)據(jù)插入到數(shù)據(jù)庫里，可以使用如下命令。INSERTINTO表名(字段名，字段名)

VALUES(值，值)；或者可以不指明表里的字段，但是值按字段的順序插入，可以使用如下命令。INSERTINTO表名

VALUES(值，值)；查詢數(shù)據(jù)，可以使用如下命令。SELECT*FROM表名；

或者查詢指定字段，可以使用如下命令。SELECTidFROM表名；使用WHERE條件語句按條件查詢，把某一列或者幾列作為查詢條件，可以使用如下命令。SELECT*FROM表名WHEREid=10；SELECT*FROM表名WHEREid=10andname='小明'；3.4.2修改記錄（UPDATE）在MySQL里修改使用關(guān)鍵字UPDATE，命令如下所示。UPDATE表名SET字段=值，字段=值WHERE條件UPDATEuserSETname='小明',sex='男'WHEREid=4;3.4.3刪除記錄（DELETE）在MySQL中刪除數(shù)據(jù)可以使用DELETE關(guān)鍵字，命令如下所示。DELETEFROM表名WHERE條件DELETEFROMuserWHEREid=4;3.4.4對(duì)查詢結(jié)果排序（ORDEYBY）在MySQL數(shù)據(jù)庫中，使用ORDEYBY進(jìn)行排序，使用關(guān)鍵字ASC進(jìn)行升序排序，使用關(guān)鍵字DESC進(jìn)行降序排序，同時(shí)可以按一個(gè)字段或者多個(gè)字段進(jìn)行排序。如果按多個(gè)字段進(jìn)行排序，先進(jìn)行第一個(gè)字段的排序，然后在結(jié)果集里面再進(jìn)行第二個(gè)字段的排序，以此類推。（1）ORDEYBYcolumnASC：按某一字段進(jìn)行升序排序，ASC可以省略不寫。SELECT*FROMuserORDEYBYidASC;或者SELECT*FROMuserORDEYBYid;（2）ORDEYBYcolumnDESC：按某一字段進(jìn)行降序排序，DESC不可以省略不寫。SELECT*FROMuserORDEYBYidDESC;（3）ORDEYBYcolumn1,column2DESC：按多個(gè)字段進(jìn)行降序排序。SELECT*FROMuserORDEYBYsex,ageDESC;3.4.5對(duì)查詢結(jié)果分組（GROUPBY）GROUPBY對(duì)查詢結(jié)果分組是將查詢結(jié)果按照1個(gè)或多個(gè)字段進(jìn)行分組，字段值相同的為一組，GROUPBY可以用于單個(gè)字段和多個(gè)字段。SELECT*FROMuserGROUPBYsex;group_concat（字段名）可以作為一個(gè)輸出字段來使用，表示分組之后，根據(jù)分組結(jié)果，使用group_concat()來放置每一組的某字段的值的集合。SELECTsex,group_concat(name)FROMuserGROUPBYsex;3.4.6設(shè)置分組條件（HAVING）HAVING是用來設(shè)置分組條件的條件表達(dá)式，用來在分組查詢后指定一些條件來輸出查詢結(jié)果，WHERE語句在聚合前先篩選記錄，也就是說作用在GROUPBY和HAVING子句前，而HAVING子句在聚合后對(duì)組記錄進(jìn)行篩選，HAVING只能用于GROUPBY。SELECTsex,count(sex)FROMuserWHEREage>15

GROUPBYsexHAVINGcount(sex)>2;3.4.7限制查詢數(shù)量（LIMIT）LIMIT用于限制查詢的數(shù)量，常用于分頁語句。LIMIT子句可以被用于強(qiáng)制SELECT語句返回指定的記錄數(shù)。LIMIT接受一個(gè)或兩個(gè)數(shù)字參數(shù)，參數(shù)必須是一個(gè)整數(shù)常量。（1）如果只給定一個(gè)參數(shù)，則它表示返回最大的記錄行數(shù)目。#檢索前6行記錄SELECT*FROMuserLIMIT6;（2）如果給定兩個(gè)參數(shù)，則第一個(gè)參數(shù)指定第一個(gè)返回記錄行的偏移量，第二個(gè)參數(shù)指定返回記錄行的最大數(shù)目，初始記錄行的偏移量是0（而不是1）。#從第2條數(shù)據(jù)開始，檢索出5條數(shù)據(jù)SELECT*FROMuserLIMIT2,5;3.5字段操作針對(duì)數(shù)據(jù)庫表的字段，可以對(duì)字段進(jìn)行設(shè)置為主鍵、設(shè)置為復(fù)合主鍵、添加字段、改變字段類型、字段重命名、字段設(shè)置默認(rèn)值以及設(shè)置自增字段的操作。3.5.1設(shè)置為主鍵主鍵是一個(gè)表的唯一約束，是不可以為空、不可以重復(fù)的字段，如在外賣訂單中，填寫的手機(jī)號(hào)就可以作為主鍵。根據(jù)這個(gè)手機(jī)號(hào)，就可以找到相應(yīng)的人，它起到唯一標(biāo)識(shí)的作用。創(chuàng)建表的時(shí)候，使用PRIMARYKEY添加主鍵。CREATETABLEtbl_name([字段描述省略...],PRIMARYKEY(index_col_name));3.5.2設(shè)置為復(fù)合主鍵復(fù)合主鍵就是由多個(gè)字段組成的主鍵，它就像開啟寶藏的鑰匙，往往會(huì)分成兩把或者更多把，當(dāng)同時(shí)插入兩把或更多把鑰匙時(shí)，才能開啟寶藏的大門，復(fù)合主鍵也是這樣，以多個(gè)字段作為復(fù)合主鍵來確定唯一標(biāo)識(shí)。3.5.3添加字段添加一個(gè)手機(jī)號(hào)碼（phone）新字段到user表里，數(shù)據(jù)類型為字符串類型。ALTERTABLEuseraddphonevarchar(25)notNull;3.5.4改變字段類型可以修改表字段的數(shù)據(jù)類型，將手機(jī)號(hào)碼（phone）字符串類型修改為整型（int）。ALTERTABLEusermodifyphoneint(25)notNull;3.5.5字段重命名對(duì)于已經(jīng)存在的表結(jié)構(gòu)，如果想對(duì)表里的字段重命名，則需要使用altertable來修改表里的字段，格式如下所示。ALTERTABLE<表名>change<字段名><字段新名稱><字段的類型>3.5.6字段設(shè)置默認(rèn)值MySQL數(shù)據(jù)庫字段在創(chuàng)建的時(shí)候可以設(shè)置默認(rèn)值，也可以修改它的默認(rèn)值，如果有默認(rèn)值，也可以將默認(rèn)值刪除。設(shè)置默認(rèn)值的命令如下所示。ALTERTABLE表名ALTER字段名SETdefault默認(rèn)值;刪除默認(rèn)值的命令如下所示。ALTERTABLE表名ALTER字段名DROPdefault;3.5.7設(shè)置自增字段MySQL數(shù)據(jù)庫表經(jīng)常會(huì)將主鍵設(shè)置為自增字段。從設(shè)計(jì)的角度來說，表的主鍵應(yīng)盡量設(shè)計(jì)成一個(gè)與業(yè)務(wù)無關(guān)的字段，如果將它設(shè)計(jì)成自增，則應(yīng)用系統(tǒng)在開發(fā)的時(shí)候不用關(guān)心這個(gè)主鍵的設(shè)定，由數(shù)據(jù)庫自己來維護(hù)，但是有在高并發(fā)下成為瓶頸的風(fēng)險(xiǎn)，當(dāng)然如果并發(fā)并不是非常高的話，一般不會(huì)成為瓶頸。如果自己控制這個(gè)主鍵值，則更需要付出一點(diǎn)代價(jià)來生成這個(gè)值，并發(fā)問題可以通過擴(kuò)展應(yīng)用集群來解決。

在MySQL中定義字段列為自增的屬性：AUTO_INCREMENT。（1）如果把一個(gè)NULL插入到一個(gè)AUTO_INCREMENT數(shù)據(jù)列里去，MySQL將自動(dòng)生成下一個(gè)序列編號(hào)，編號(hào)從1開始，并以1為基數(shù)遞增。（2）當(dāng)插入記錄時(shí)，沒有為AUTO_INCREMENT明確指定值，則等同于插入NULL值。（3）當(dāng)插入記錄時(shí)，如果為AUTO_INCRE