第三章mysql數(shù)據(jù)庫(kù)的使用

第三章 數(shù)據(jù)庫(kù)的使一、MySQL簡(jiǎn)什么是MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，由瑞典MySQLAB公司開發(fā)，目前屬于Oracle公司。MySQL是一種關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，關(guān)系型數(shù)據(jù)庫(kù)將數(shù)據(jù)保存在不同的表MySQL特 是開源的，所以你不需要支付額外的費(fèi)用 支持大型系統(tǒng)的數(shù)據(jù)庫(kù)?？梢蕴幚?yè)碛猩锨f(wàn)條記錄的大型數(shù)據(jù)MySQLSQLMySQL可以運(yùn)行于多個(gè)系統(tǒng)上，并且支持多種語(yǔ)言。這些編程語(yǔ)言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。MySQL數(shù)據(jù)量較大，32位系統(tǒng)表文件最大可支持4GB，64位系統(tǒng)支持最大的表文件為8TB。MySQLGPLMySQL統(tǒng)二、MySQL的安裝與卸MySQL版本說(shuō)MySQLCommunity 社區(qū)版本，開源免費(fèi)，但不提供技術(shù)支持MySQLClusterCGE高級(jí)集群版，需。本套使用版本MySQL5.7社區(qū)MySQL服開始-->所有應(yīng)用-->Windows管理工具-->服務(wù)，將MySQL服務(wù)停止mysql控制面板-->所有控制面板項(xiàng)-->程序和功能，將mysqlserver卸載掉MySQL安將MySQL安 下的MySQL文件夾刪除（C:\ProgramFiles(x86)\MySQL或刪除表中信息運(yùn)行“regedit”文件，打開表，刪除如下文件夾L文件夾L文件夾。ySQL的文件夾。如果沒(méi)有相應(yīng)的文件夾，就不用刪除MySQL到地址欄回車。將整個(gè)MySQL文件夾刪除掉刪除服務(wù)中的如果已經(jīng)將MySQL卸載，但通過(guò)“開始-->所有應(yīng)用-->Windows管理工具-->服務(wù)”查看到MySQL服務(wù)仍然殘留在系統(tǒng)服務(wù)里，可以在CMD里輸入一條命令就可以將服務(wù)刪除：scdeletemysql57//這里mysql是你要?jiǎng)h除的服務(wù)名。重啟系統(tǒng)重啟系統(tǒng)安裝MySQL三、MySQL的客戶端工 自帶的客戶端端工具示第客戶端工具四、MySQLOracle實(shí)例區(qū)別MySQL是輕量型數(shù)據(jù)庫(kù)，開源免費(fèi)。Oracle是的而且價(jià)格非常高。MySQL一個(gè)實(shí)例可以操作多個(gè)庫(kù)Oracle一個(gè)實(shí)例只能對(duì)應(yīng)一個(gè)庫(kù)。MySQL安裝完后300MOracle3G左右。操作區(qū)別主鍵：MySQL一般使用自動(dòng)增長(zhǎng)類型，而Oracle則需要使用序列對(duì)象MySQL里可以用雙引號(hào)包起字符串，ORACLE里只可以用單引號(hào)包事務(wù)處理：MySQL默認(rèn)是自動(dòng)提交，而Oracle默認(rèn)不自動(dòng)提交，需要用戶CTL語(yǔ)言進(jìn)行事務(wù)提交五、創(chuàng)建與刪除數(shù)據(jù)庫(kù)創(chuàng)建數(shù)據(jù)庫(kù)使用命令創(chuàng)建數(shù)據(jù)庫(kù)示createdatabasetestdefaultcharactersetshowdatabases;selectschema_name,default_character_set_namefrominformation_schema.schematawhereschema_name='test';Navicat創(chuàng)建數(shù)據(jù)庫(kù)示創(chuàng)建一個(gè)test2的數(shù)刪除數(shù)據(jù)庫(kù)使用命令刪除數(shù)據(jù)庫(kù)Dropdatabase示dropdatabaseNavicat工具刪除數(shù)據(jù)庫(kù)示選擇數(shù)據(jù)庫(kù)需要在哪個(gè)庫(kù)中創(chuàng)建表需要先選擇該數(shù)據(jù)庫(kù)Use示例一createdatabasebjsxtdefaultcharactersetutf8;示例二選擇該數(shù)據(jù)useMySQL中的數(shù)據(jù)類型數(shù)值類型MySQL支持所有標(biāo)準(zhǔn)SQL數(shù)值數(shù)據(jù)類型SQL標(biāo)準(zhǔn)的擴(kuò)展，MySQL也支持整數(shù)類TINYINT、MEDIUMINTBIGINT例如：int(3)，如果實(shí)際值是2，如果列指定了zerofill，查詢結(jié)果就是002，左邊用0來(lái)填浮點(diǎn)型字符串型charchar(n)若存入字符數(shù)小于n，則以空格補(bǔ)于其后，查詢之時(shí)再將空格去掉。所以類型的字符串末尾不能有空格 不限于此charvarcharvarcharvarchar可指定n，text不能指定，內(nèi)部varchar是存入的實(shí)際字符數(shù)+1個(gè)字節(jié)（n<=255）或2個(gè)字節(jié)(n>255)，text是實(shí)際字符數(shù)+2個(gè)字節(jié)。varchar可直接創(chuàng)建索引，text創(chuàng)建索引要指定前多少個(gè)字符。varchar查詢速度快于text,在都創(chuàng)建索引的情況下，text的索引似乎不起作用。日期類型二進(jìn)制數(shù)據(jù)BLOB和TEXT方式不同，TEXT以文本方式，英文區(qū)分大小寫，而是以二進(jìn)制方式，不分大小寫B(tài)LOB的數(shù)據(jù)只能整體讀出TEXT可以指定字符集，BLOB創(chuàng)建表與刪除表創(chuàng)建表通過(guò)DDL語(yǔ)句創(chuàng)建示創(chuàng)建一個(gè)employees表包含雇員ID，雇員名字，雇員薪水createtableemployees(employee_idint,last_namevarchar(30),salaryfloat查看已創(chuàng)建的ShowNavicat工具創(chuàng)建表示創(chuàng)建一個(gè)employees2表包含雇員ID，雇員名字，雇員薪水刪除表通過(guò)DDL語(yǔ)句刪除示droptableNavicat工具刪除表示修改表使用DDL語(yǔ)句修改表ALTERTABLERENAME示例一創(chuàng)建一個(gè)employees表包含雇員ID，雇員名字，雇員薪水createtableemployees(employee_idint,last_namevarchar(30),salaryfloat示例二employeesemp。使用DDL修改列ALTERTABLECHANGECOLUMN示altertableempchangecolumnlast_namename使用DDL語(yǔ)句修改列類ALTERTABLEMODIFY示emp表中的name的長(zhǎng)度指定為40altertableempmodifynamevarchar(40);DDL語(yǔ)句添加ALTERTABLEADDCOLUMN示在emp表中添加一個(gè)新的列為altertableempaddcolumncommission_pctDDL語(yǔ)句刪除ALTERTABLEDROPCOLUMN示emp表中的altertableempdropcolumnMySQL中的約約束類型唯一性約束檢查約束(目前MySQL不支持、Oracle支持創(chuàng)建表時(shí)添加約束查詢表中的約SHOWKEYSFROM示例一departmentsdepartment_id該列為主鍵且自動(dòng)增長(zhǎng)，department_name列不允許重復(fù)，location_id列不允含有空值。createtabledepartments(department_idintprimarykeyauto_increment,department_namevarchar(30)unique,location_idintnotnull);示例二employeesemployees_id該列為主鍵且自動(dòng)增長(zhǎng)，last_name列不允許含有createtableemployees(employees_idintprimarykeyauto_increment,last_namevarchar(30)notnull,varchar(40)notnullunique,dept_idint,constraintemp_fkforeignkey(dept_id)referencesdepartments(department_id));修改表實(shí)現(xiàn)約束的添加與刪除主鍵約束添加主鍵約束示將emp表中的employee_id修改為主鍵且自動(dòng)增添加主鍵：altertableempaddprimary添加自動(dòng)增長(zhǎng)：altertableempmodifyemployee_idint刪除主鍵約束鍵示刪除employee_id的主鍵約束altertableempmodifyemployee_id刪除主鍵：altertableempdropprimary非空約束添加非空約束ALTERTABLEMODIFYNOT示向emp表中的salary添加非空約束altertableempmodifysalaryfloat(8,2)not刪除非空約束ALTERTABLEMODIFY示刪除salary的非空約束altertableempmodifysalaryfloat(8,2)唯一約束添加唯一約束ALTERTABLEADDCONSTRAINTUNIQUE(列名示altertableempaddconstraintemp_uk刪除唯一約束ALTERTABLEDROPKEY刪除name的唯一約束altertableempdropkey外鍵約束添加外鍵約束ALTERTABLE表名ADDCONSTRAINT約束 FOREIGNKEY(列名REFERENCES參照的表名(參照的列名示例一altertableempaddcolumndept_id示例二向emp表中的dept_id列添加外鍵約束altertableempaddconstrainte_fkforeignkey(dept_id)referencesdepar刪除外鍵

刪除外鍵約束ALTERTABLEDROPFOREIGNKEY刪除外鍵索引(索引名與約束名相同ALTERTABLEDROPINDEX示刪除dept_id的外鍵約束刪除外鍵：altertableempdropforeignkeyaltertableempdropindexMySQL中的DML操添加數(shù)據(jù)插入數(shù)據(jù)選擇插入INSERTINTO表名(列名1，列名2，列名3.....)VALUES(值1，值2，值 insertintodepartments(department_name,location_id)values("market",1);完全插入 如果主鍵是自動(dòng)增長(zhǎng)，需要使用default或者null或者0占位示例一departmentsdevelopmentID2。使用default占位。insertintodepartments示例二向departments表中添加一條數(shù)據(jù)，部門名稱為human，工作地點(diǎn)ID為3。使用null位insertintodepartments示例三向departments表中添加一條數(shù)據(jù)，部門名稱為teaching，工作地點(diǎn)ID為4。使用0位insertintodepartments自動(dòng)增長(zhǎng)MySQL中的自動(dòng)增長(zhǎng)類型要求一個(gè)表中只能有一個(gè)列為自動(dòng)增長(zhǎng)自動(dòng)增長(zhǎng)的列的類型必須是整數(shù)類型自動(dòng)增長(zhǎng)只能添加到具備主鍵約束與唯一性約束的列示createtableemp2(idintprimarykey,namevarchar(30),seq_numintunique默認(rèn)值處理MySQLDEFAULT定該列的值，那么MySQL會(huì)將默認(rèn)值添加到該列創(chuàng)建表時(shí)指定列的默認(rèn)值示emp3emp_idnameaddress該列默認(rèn)createtableemp3(emp_idintprimarykeyauto_increment,namevarchar(30),addressvarchar(50)default'Unknown');修改表添加列的默認(rèn)值示emp3job_id0。altertableemp3addcolumnjob_idintdefault插入數(shù)據(jù)時(shí)的默認(rèn)值處理完全項(xiàng)插入需要使用default來(lái)占位。示向emp3表中添加數(shù)據(jù)，要求address列與job_id列使用默認(rèn)值作為該列的值insertintoemp3(name)insertintoemp3更新數(shù)據(jù)UPDATE表名SET列名=值，列名=值 條mysqlupdate的特更新的表不能在set和where中用于子查詢update示例一updateemp3esete.address="BeiJing"whereemp_id=1;示例二Oracle:updateemp3esete.address=(selectaddressfromemp3whereemp_id=1)wheree.emp_id=2;MySQL:updateemp3e,(selectaddressfromemp3whereemp_id=1)tset=t.addresswheree.emp_idupdateemp3esete.address=(selectt1.addressfrom(selectemp_id,addressfromemp3)t1wheret1.emp_id=1)wheree.emp_id=2;刪除數(shù)據(jù)使用DELETE子DELETEFROMWHERE示deletefromemp3whereemp_id=TRUNCATE清空表示刪除emp3表中的所有數(shù)truncatetableDELETETRUNCATE區(qū)truncate是整體刪除（速度較快），delete是逐條刪除（速度較慢truncate不寫服務(wù)器log，delete寫服務(wù)器log，也就是truncate效率比delete高的原因不是接著原來(lái)的值。而delete刪除以后，自增值仍然會(huì)繼續(xù)累加。MySQL中的事務(wù)處理關(guān)閉MySQL的事務(wù)自動(dòng)提交示向emp3表中添加一條數(shù)據(jù)，要求手動(dòng)提交事mysql>startQueryOK,0rowsaffected(0.00mysql>insertintoemp3values(default,"oldlu",default,default);QueryOK,1rowaffected(0.00sec)mysql>QueryOK,0rowsaffected(0.01六、MySQL查詢MySQL的基本查MySQL的列選SELECT*|FROM示查詢departments表中的所有數(shù)select*fromMySQL的行選SELECT*|FROMWHERE示查詢departments表中部門ID為4的部門名稱與工作地點(diǎn)IDSELECT語(yǔ)句中的算術(shù)表達(dá)式+-*/%示例一修改employees表添加salaryaltertableemployeesaddcolumnsalary示例二查詢雇員的年薪select 示例三計(jì)算employees表中的員工全年薪水加100以后的薪水是多少select ,12*salary+100fromMySQL中定義包含空值的算術(shù)表達(dá)式計(jì)算結(jié)果為空示altertableemployeesaddcolumncommission_pctfloat(5,2);select12*salary*commission_pctfromemployees;MySQL中的列SELECTASFROMWHERE示selectlast_nameasnamefromemployees;MySQL中的連MySQL中并不支持||作為連字符，需要使用concat函數(shù)。在參數(shù)數(shù)量上與oracle的函數(shù)有區(qū)別示查詢雇員表中的所有數(shù)據(jù)，將所有數(shù)據(jù)連接到一起，每列值中通過(guò)#分割 MySQL中去除SELECTDISTINCT示selectdistinctdept_idfromemployees;約束和排序數(shù)據(jù)MySQL中的比較條比較運(yùn)算符等于大于大于等于小于小于等于不等于!=或示例一查詢employees表，員工薪水大于等于3000的員工的與薪水select*fromemployeeswheresalary示例二查詢employees表，員工薪水不等于5000的員工的與薪水select*fromemployeeswheresalary模糊查詢%表示任意多個(gè)任意字_示查詢employees中雇員名二個(gè)字母是e的雇員信息select*fromemployeeswherelast_namelike邏輯運(yùn)算符示例一查詢employees表中雇員薪水是5000的并且名字中含有d的雇員信select*fromemployeeswheresalary=5000andlast_namelike示例二查詢employees表中雇員名字中不包含u的雇員信select*fromemployeeswherelast_namenotlike范圍查詢between...in表示在一個(gè)非連續(xù)的范圍示例一查詢employees表，薪水在3000-8000之間的雇員信select*fromemployeeswheresalarybetween3000and示例二employyees表，找出薪5000,6000,8000的雇員信select*fromemployeeswheresalary空值判斷示例一找出emloyees表中那些沒(méi)有傭金的select*fromemployeeswherecommission_pctis示例二找出employees表中那些有傭金的雇select*fromemployeeswherecommission_pctisnotORDERBY排ORDERBYASC:DESC:示例一查詢employees表中的所有雇員，薪水按升序排序select*fromemployeesorderby示例二查詢employees表中的所有雇員，雇員名字按降序select*fromemployeesorderbylast_nameMySQL中常見(jiàn)的單行函數(shù)大小寫控制函數(shù) 轉(zhuǎn)換大小寫混合的字符串為小寫字符 轉(zhuǎn)換大小寫混合的字符串為大寫字符字符處理 將str1、str2等字符串連接起 strpos位（范圍：1~str.length）開始，截取長(zhǎng)度為的字符 獲取str的長(zhǎng)度 獲取substr在str中的位置 從str中刪除開頭和結(jié)尾的空格（不會(huì)處理字符串中間含有的空格 從str中刪除左側(cè)開頭的空 從str中刪除右側(cè)結(jié)尾的空 from_str的字符串）數(shù)字函數(shù)日期函數(shù)SYSDATE()或者NOW() CURDATE()返回系統(tǒng)當(dāng)前日期，不返回時(shí)間 返回當(dāng)前系統(tǒng)中的時(shí)間，不返回日 d d今天是星期幾，1星期日，2 返回date日期是星期幾 轉(zhuǎn)換函數(shù) 將日期轉(zhuǎn)換成字符串（類似oracle中的to_char()） 將字符串轉(zhuǎn)換成日期（類似oracle中的to_date()）示例一altertableemployeesaddcolumnhire_date示例二向employees表中添加一條數(shù)據(jù)，名字 ， ，部門薪水：9000，入職時(shí)間：2018年5月1日，傭金：insertintoemployees 51日','%Y年%m月%d日示例三查詢employees表中雇員名字為King的雇員的入職日期，要求顯示格式為yyyy年ddselectDATE_FORMAT(hire_date,'%Y年%m月%d日f(shuō)romemployeeswherelast_name通用函數(shù) expr1是否nullnull，則expr2（類似oracle的NVL()函數(shù) expr1expr2null，如果不相等則返回expr1 expr1是否為真（是否不為null），如果為真，則使替代expr1；如果為假，則使用expr3替代expr1（類似oracle的NVL2()函數(shù)） 判斷value的值是否為null，如果不為null，則返回value；后一個(gè)為null的valueCASEWHENTHENELSE 條件函多表連接查詢等值連接示查詢雇員King所在的部門名 d.department_idande.last_name='King'非等值連接示例一sal_level表，包lowest_sal，highest_sal，level插入數(shù)ABCDinsertintosal_level4.2.3示例三查詢所有雇員的薪水級(jí)別selecte.last_name,s.levelfromemployeese,sal_levelswheree.salarybetweens.lowest_salandhighest_sal;自連接示例一示例二示例三查詢每個(gè)雇員的經(jīng)理的名字以及雇員的名字selectemp.last_name,man.last_namefromemployeesemp,employeesmanwhereemp.manager_id=man.employees_id外連接(OUTER左外連接(LEFTOUTER示例一向employees表中添加一條數(shù)據(jù)，名字：Lee，： insertintoemployees(last_name,,hire_date) 查詢所有雇員的名字以及他們的部門名稱，包含那些沒(méi)有部門的雇員selecte.last_name,d.department_namefromemployeeseLEFTOUTERJOINdepartmentsdone.dept_id=d.department_id右外連接(RIGHTOUTER示例一insertintodepartmentsvalues(default,'Testing',5)示例二查詢所有雇員的名字以及他們的部門名稱，包含那些沒(méi)有雇員的部門selecte.last_name,d.department_namefromemployeeserightOUTERjoindepartmentsdone.dept_id=d.department_id;全外可以使用union實(shí)現(xiàn)全完連接。可以將兩個(gè)查詢結(jié)果集合并返回的行都是唯一的，如同對(duì)整個(gè)結(jié)果集合使用了DISTINCTUNION只是簡(jiǎn)單的將兩個(gè)結(jié)果合并后就返回。這樣，如果返回的兩個(gè)結(jié)果集中有重復(fù)的數(shù)據(jù)，那么返回的結(jié)果集就會(huì)包含重復(fù)的數(shù)據(jù)了。語(yǔ)法結(jié)構(gòu)SELECT投影列FROM表名LEFTOUTERJOIN表名ON連接條件UNIONSELECT投影列FROM表名RIGHTOUTERJOIN表名ON連接條件示查詢所有雇員的名字以及他們的部門名稱，包含那些沒(méi)有雇員的部門以及沒(méi)有部門的員(selecte.last_name,d.department_namefromemployeeseLEFTOUTERJOINdone.dept_id=d.department_id)UNION(selecte1.last_name,d1.department_namefromemployeese1RIGHTOUTERJOINdepartmentsd1ond1.department_id=e1.dept_id)SQL99標(biāo)準(zhǔn)中的查SQL99中的交叉連接(CROSS示使用交叉連接查employees表與departmentsselect*fromemployeescrossjoinSQL99中的自然連接(NATURAL示例一altertableemployeeschangecolumndept_iddepartment_idint示例二使用自然連接查詢所有有部門的雇員的名字以及部門名稱SQL99中的內(nèi)連接(INNER示查詢雇員名字為OldLu的雇員ID，薪水與部門名稱selecte.employees_id,e.salary,d.department_namefromemployeeseinnerJOINdepartmentsdone.department_id=d.department_idwheree.last_name='Oldlu';聚合函數(shù)AVG(arg)函對(duì)分組數(shù)據(jù)做平均值運(yùn)算arg:參數(shù)類型只能是數(shù)字類型SUM(arg)函數(shù)對(duì)分組數(shù)據(jù)求和arg:參數(shù)類型只能是數(shù)字類型MIN(arg)函數(shù)求分組中最小arg:參數(shù)類型可以是字符、數(shù)字、日期MAX(arg)函求分組中最大arg:參數(shù)類型可以是字符、數(shù)字、日期COUNT函返回一個(gè)表中的行數(shù)COUNTCOUNT(DISTINCT數(shù)據(jù)組(GROUP創(chuàng)建數(shù)據(jù)組示計(jì)算每個(gè)部門的平均selectavg(e.salary)fromemployeesegroupby約束分組結(jié)果selecte.department_id,max(e.salary)fromemployeesegroupbye.department_idHAVINGMAX(e.salary)>5000子查詢WHEREHAVINGFROM使用子查詢的原則子查詢放在圓括號(hào)中將子查詢放在比較條件的右邊在單行子查詢中用單行運(yùn)算符，在多行子查詢中用多行運(yùn)算符示誰(shuí)的薪水比Oldluselectem.last_name,em.salaryfromemployeesemwhereem.salary>(selecte.salaryfromemployeesewheree.last_name='Oldlu')單行子查詢示查詢Oldlu的同事，但是不包含他自己selectempl.last_namefromemployeesemplwhereempl.department_id=(selecte.department_idfromemployeesewheree.last_name='Oldlu')andempl.last_name<>'Oldlu'多行子查詢示查找收入為部門最低的那些雇員。顯示他們的名字，薪水以及部 IDselectem.last_name,em.salary,em.department_idfromemployeesemwhereem.salaryin(selectmin(e.salary)fromemployeesegroupbye.department_id)MySQL中的正則表達(dá)式MySQL中允許使用正則表達(dá)式定義字符串的搜索條件，性能要高于like 使用REGEXP關(guān)鍵字表示正則匹配默認(rèn)忽略大小寫，如果要區(qū)分大小寫，使用BINARY正則表達(dá)式的模式及其含義“^”符號(hào)^在正則表達(dá)式中表示開語(yǔ)查詢以x開頭的數(shù)據(jù)(忽略大小寫SELECTFROMWHEREREGEXP示查詢雇員表中名字是以k開頭的雇員名字與薪水“$”符語(yǔ)查詢以x結(jié)尾的數(shù)據(jù)(忽略大小寫SELECTFROMWHEREREGEXP示查詢雇員表中名字是以n結(jié)尾的雇員名字與薪水語(yǔ)英文的點(diǎn)，它匹配任何一個(gè)字符，包括回車、換行等SELECTFROMWHEREREGEXP示查詢雇員表中名字含有o的雇員的與薪水“*”符語(yǔ)“*”：星號(hào)匹配0個(gè)或多個(gè)字符，在它之前必須有內(nèi)“+”符號(hào)語(yǔ)"+"：加號(hào)匹配1個(gè)或多個(gè)字符，在它之前也必須有內(nèi)容SELECTFROMWHEREREGEXPx+';-1“?”符語(yǔ)SELECTFROMWHEREREGEXPx?';-01“|”符語(yǔ)“|”：表示或者含SELECTFROMWHEREREGEXPabc|bcd';-abc示查詢雇員表中名字含有ke或者lu的雇員的名字與薪“[a-語(yǔ) ]”：匹配不包含在[]SELECTFROMWHEREREGEXPa-z]';-a-z據(jù)示例一查詢雇員表中名字包含x、y、z字符的雇員的名字和薪水示例二查詢雇員名字是t、f開頭的雇員名字與薪水示例三查詢雇員的名字與薪水，不包含oldlu語(yǔ)“{n}”：固定次數(shù)示例一查詢雇員名字含有連續(xù)兩個(gè)e的雇員的與薪示例二查詢名字中含有兩個(gè)o的雇員的名字與薪水語(yǔ)“{n,m}”：范圍次數(shù)select*fromstudentwherenameREGEXP^s{2,5}';--s25次的所示查詢雇員名字中包含1個(gè)或者兩個(gè)o的雇員與薪水七、MySQL中的其他對(duì)索MySQL索引的建立對(duì)于MySQL的高效運(yùn)行是很重要的，索引可以大大提高的檢索速度MySQL中的索引類普通索引是最基本的索引，它沒(méi)有任何限制類型的字段才能指定索引長(zhǎng)度，如果是BLOBTEXT類型，必須指定length。創(chuàng)建索引時(shí)需要注意查詢索引：SHOWINDEXFROMtable_name直接創(chuàng)建索引示createindexemp3_name_indexONemp3(name)修改表添加索引示altertableemp3addindexemp3_address_index(address)創(chuàng)建表時(shí)指定索引列CREATETABLE`table`(COLUMNTYPE)示emp4emp_id,name,addressname列創(chuàng)建索引。索引名為createtableemp4(emp_idintprimarykeyauto_increment,namevarchar(30),addressvarchar(50),indexemp4_name_index(name))刪除索引DROPINDEXindex_nameON示mep3表中索引名emp3_address_index的索dropindexemp3_address_indexon唯一索引創(chuàng)建唯一索引示createuniqueindexemp_name_indexonemp(name)修改表添加唯一索引示修改emp表，為salary列添加唯一索引，索引名為altertableempaddunique創(chuàng)建表時(shí)指定唯一索引CREATETABLE`table`(COLUMNTYPE)示emp5表，包emp_id,name,address列，同時(shí)name列創(chuàng)建唯一索引。索引名createtableemp5(emp_idintprimarykey,namevarchar(30),addressvarchar(30),unique主鍵索引修改表添加主鍵索引示修改emp表為employee_id添加主鍵索altertableempaddprimary創(chuàng)建表時(shí)指定主鍵索引CREATETABLE`table`(COLUMNTYPEPRIMARY)示emp6表，包emp_id,name,address列，同時(shí)emp_id列創(chuàng)建主createtableemp6(employee_idintprimarykeyauto_increment,namevarchar(20),address組合索引最左前綴原則就是最左優(yōu)先name，address，salary創(chuàng)建組合索引，那么想要組合索引生效，如果使用addrees/salary或者是salary則索引不會(huì)生效修改添加組合索引示修改emp6表，為name，address列創(chuàng)建組合索altertableemp6addindex創(chuàng)建表時(shí)創(chuàng)建組合索引CREATETABLE`table`(COLUMNTYPE)示emp7表，包emp_id,name,address列，同時(shí)name,address列創(chuàng)建組合索引createtableemp7(emp_idintprimarykeyauto_increment,namevarchar(20),addressvarchar(30),indexemp7_index_n_a(name,address))全文索引全文索引(FULLTEXTINDEX)主要用來(lái)查找文本中的關(guān)鍵字，而不是直接與索引中的值相的參數(shù)匹配。FULLTEXT索引配合matchagainst操作使用，而不是一般的where語(yǔ)句加全文索引可以從CHAR、VARCHARTEXT列中作CREATETABLE語(yǔ)句的一部分ALTERTABLE添加。不過(guò)切記對(duì)于大容量的數(shù)據(jù)表，生成全文索修改添加全文索引示例一altertableemp7addCOLUMNcontent示例二修改emp7表，為content列創(chuàng)建全文索altertableemp7addfulltext創(chuàng)建表時(shí)創(chuàng)建全文索引CREATETABLE`table`(COLUMNTYPE)示emp8表包含emp_idcontent列該列類型為text，并為該列添加名為emp8_content_fulltext的全文索 刪除全文索引DROPINDEXindex_nameON示emp8表中名emp8_content_full的索引使用全文索引全文索引的使用與其他索引不同。在查詢語(yǔ)句中需要使用match(column)against(‘content’)來(lái)檢索數(shù)據(jù)。全文解析器全文索引中基本單位是”詞”。分詞，全文索引是以詞為基礎(chǔ)的，MySQL默認(rèn)的分詞是所有非字母和數(shù)字的特殊符號(hào)都是分詞符。在檢索數(shù)據(jù)時(shí)我們給定的檢索條件也是詞。 中默認(rèn)的全文解析器不支持中文分詞。如果數(shù)據(jù)含有中文需要更換全文解析器NGRAM使用全文索引SELECTFROMWHEREMATCH(全文索引列名AGAINST(‘搜索內(nèi)容示例一emp8表，為content列創(chuàng)建名emp8_content_full的全altertableemp8addfulltext示例二向emp8表中插入一條數(shù)據(jù)content的值為” insertintoemp8values(default,' objsxt')示例三查詢emp8表中內(nèi)容包含bjsxt的數(shù)據(jù)select*fromemp8wherematch(content)更換全文解析器在創(chuàng)建全文索引時(shí)可以指定ngram解析ALTERTABLEtable_nameADDFULLTEXTindex_content(content)WITHPARSER示例一emp8emp8_content_full全文索dropindexemp8_content_fullon示例二emp8表content列添加名稱為emp8_content_full的全文索引全文解析器altertableemp8addfulltextemp8_content_full(content)withparser示例三insertintoemp8values(default,'你好，尚學(xué)堂')示例四查詢emp8表中內(nèi)容包含”尚學(xué)堂”的數(shù)MySQL中的用戶管理MySQL是一個(gè)多用戶的數(shù)據(jù)庫(kù)系統(tǒng)，按權(quán)限，用戶可以分為兩種：root用戶，超級(jí)管理員，和由root用戶創(chuàng)建的普通用戶。MySQL創(chuàng)建用查看用戶SELECTUSER,NOSTFROMUSER(該表位mysql庫(kù)中示創(chuàng)建一個(gè)u_sxt的用戶，并查看創(chuàng)建是否成功createuseru_sxtIDENTIFIEDby分配權(quán)限新用戶創(chuàng)建完后是無(wú)法登陸的，需要分配權(quán)限GRANT權(quán)限ON數(shù)據(jù)庫(kù).表TO用戶名@登錄主機(jī)IDENTIFIEDBY"登陸主機(jī) 匹配所有主 localhost不會(huì)被解析IP地址，直接通UNIXsocket連 會(huì)通過(guò)TCP/IP協(xié)議連接，并且只能在本機(jī) 權(quán)限列表ALTER:修改表和索引。CREATE:創(chuàng)建數(shù)據(jù)庫(kù)和表。DELETE刪除表中已有的記錄。DROP:刪除數(shù)據(jù)庫(kù)和表。INDEX:創(chuàng)建或刪除索引。INSERT:向表中插入新行。SELECT:檢索表中的記錄。UPDATE:修改現(xiàn)存表記錄。FILE:PROCESS:查看服務(wù)器中執(zhí)行的線程信息或殺死線程。RELOAD:重載表或清空日志、主機(jī)緩存或表緩存。SHUTDOWN:關(guān)閉服務(wù)器。:USAGE:"無(wú)權(quán)限"示為u_sxt用戶分配只能查詢bjsxt庫(kù)中的employees表，并且只能在本機(jī)登陸的權(quán)限grantselectONbjsxt.employeesto'u_sxt'@'localhost'IDENTIFIEDby刷新權(quán)限每當(dāng)調(diào)整權(quán)限后，通常需要執(zhí)行以下語(yǔ)句刷新權(quán)FLUSH刪除用戶DROPUSER示dropuserNavicat工具管理用戶創(chuàng)建用戶分配權(quán)限刪除用戶八、MySQL分頁(yè)MySQL分頁(yè)查詢?cè)贛ySQL數(shù)據(jù)庫(kù)中使用LIMIT子句進(jìn)行分頁(yè)查詢MySQL分頁(yè)中開始位置為0分頁(yè)子句在查詢語(yǔ)句的最后側(cè)LIMIT子語(yǔ)法格式SELECTFROMWHEREORDERBYLIMIT示查詢雇員表中所有數(shù)據(jù)按id排序，實(shí)現(xiàn)分頁(yè)查詢，每次返回兩條結(jié)果select*fromemployeesorderbyemployees_idlimitLIMITOFFSET子語(yǔ)法格式SELECT投影列FROM表名WHERE條件ORDERBYLIMIT查詢數(shù)量開始位置示idLIMITOFFSET實(shí)現(xiàn)分頁(yè)查詢，每次返回兩條select*fromemployeesorderbyemployees_idlimit2offset九、MySQL中的執(zhí)行計(jì)MySQL執(zhí)行計(jì)在MySQL中可以通過(guò)exin關(guān)鍵字模擬優(yōu)化器執(zhí)行SQL語(yǔ)句，從而知道MySQL是如何處理SQL語(yǔ)句的。MySQL整個(gè)查詢執(zhí)行過(guò)程客戶端向MySQL服務(wù)器發(fā)送一條查詢請(qǐng)服務(wù)器進(jìn)行SQLMySQL根據(jù)執(zhí)行計(jì)劃，調(diào)用引擎的API來(lái)執(zhí)行查將結(jié)果返回給客戶端，同時(shí)緩存查詢啟動(dòng)執(zhí)行計(jì)劃 INSELECTFROMWHERE IN列的解查詢執(zhí)行順序id值相同時(shí)表示從上向下執(zhí)id值相同被視為一如果是子查詢，id值會(huì)遞增，id值越高，優(yōu)先級(jí)越simple:表示查詢中不包含子查詢或者primary:當(dāng)查詢中包含任何復(fù)雜的子部分，最外層的查詢被標(biāo)記成primaryderived:在from的列表中包含的子查詢被標(biāo)記成derivedsubquery:在select或where列表中包含了子查詢，則子查詢被標(biāo)記成union:selectPRIMARYUNION。union出現(xiàn)RMAYnio第二個(gè)子查詢標(biāo)記為顯示這一行的數(shù)據(jù)是關(guān)于哪張表的這是重要的列，顯示連接使用了何種類型。從最好到的連接類型為system、const、eq_reg、ref、range、index和ALL。system：表中只有一行數(shù)據(jù)。屬于const的特例。如果物理表中就一行數(shù)據(jù)為constconst查詢速度非?？?，因?yàn)橹蛔x一次。一般情況下把主鍵或唯一索引作為唯一條件的查詢都是consteq_ref：（主鍵表中數(shù)據(jù)可以有沒(méi)有用到的）ref：相比eq_refref。也可能是索引查詢。查詢條件字段涉及到的索引，可能沒(méi)有使用實(shí)際使用的索引。如果為NULL，則沒(méi)有使用索引表示索引中使用的字節(jié)數(shù)，查詢中使用的索引的長(zhǎng)度（最大可能長(zhǎng)度，并非實(shí)際使用長(zhǎng)度，理論上長(zhǎng)度越短越好。key_en是根據(jù)表定義計(jì)算而得的，不是通過(guò)表內(nèi)檢索出的。顯示索引的哪一列被使用了，如果可能的話，是一個(gè)常 const根據(jù)表統(tǒng)計(jì)信息及索引選用情況，大致估算出找到所需的記錄所需要的行數(shù)顯示了通過(guò)條件過(guò)濾出的行數(shù)的百分比估計(jì)值MYSQL如何解析查詢的額外信息Distinct:MySQL發(fā)現(xiàn)第1個(gè)匹配行后,停止為當(dāng)前的行組合搜索的行Notexists:MySQLLEFTJOIN優(yōu)化,1LEFTJOIN標(biāo)準(zhǔn)的行rangecheckedforeachrecord(indexmap:#):MySQL沒(méi)有發(fā)現(xiàn)好的可以使用的索引,但發(fā)Usingfilesort:MySQL需要額外的一次傳遞,以找出如何按排序順序檢索行Usingindex:從只使用索引樹中的信息而不需要進(jìn)一步搜索實(shí)際的行來(lái)檢索表中的Usingtemporary:為了解決查詢,MySQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來(lái)容納結(jié)果Using 子句用于限制哪一個(gè)行匹配下一個(gè)表或發(fā)送到客戶Usingsort_union(...),Usingunion(...),Usingintersect(..