MySQL簡單查詢和單表查詢

歡迎閱讀本文檔，希望本文檔能對您有所幫助!歡迎閱讀本文檔，希望本文檔能對您有所幫助!歡迎閱讀本文檔，希望本文檔能對您有所幫助!歡迎閱讀本文檔，希望本文檔能對您有所幫助!歡迎閱讀本文檔，希望本文檔能對您有所幫助!歡迎閱讀本文檔，希望本文檔能對您有所幫助!MySQL簡單查詢和單表查詢MySQL記錄操作概覽MySQL數(shù)據(jù)操作：DML在MySQL管理軟件中，可以通過SQL語句中的DML語言來實(shí)現(xiàn)數(shù)據(jù)的操作，包括使用INSERT實(shí)現(xiàn)數(shù)據(jù)的插入U(xiǎn)PDATE實(shí)現(xiàn)數(shù)據(jù)的更新使用DELETE實(shí)現(xiàn)數(shù)據(jù)的刪除使用SELECT查詢數(shù)據(jù)以及。插入數(shù)據(jù)insert1.插入完整數(shù)據(jù)（順序插入）語法一：INSERTINTO表名(字段1,字段2,字段3…字段n)VALUES(值1,值2,值3…值n);語法二：INSERTINTO表名VALUES(值1,值2,值3…值n);2.指定字段插入數(shù)據(jù)語法：INSERTINTO表名(字段1,字段2,字段3…)VALUES(值1,值2,值3…);3.插入多條記錄語法：INSERTINTO表名VALUES(值1,值2,值3…值n),(值1,值2,值3…值n),(值1,值2,值3…值n);4.插入查詢結(jié)果語法：INSERTINTO表名(字段1,字段2,字段3…字段n)SELECT(字段1,字段2,字段3…字段n)FROM表2WHERE…;更新數(shù)據(jù)update語法：UPDATE表名SET字段1=值1,字段2=值2,WHERECONDITION;示例：UPDATEmysql.userSETpassword=password(‘123’)whereuser=’root’andhost=’localhost’;刪除數(shù)據(jù)delete語法：DELETEFROM表名WHERECONITION;示例：DELETEFROMmysql.userWHEREpassword=’’;練習(xí)：更新MySQLroot用戶密碼為mysql123刪除除從本地登錄的root用戶以外的所有用戶查詢數(shù)據(jù)search1.單表查詢關(guān)鍵字執(zhí)行的優(yōu)先級:fromwheregroupbyselectdistincthavingorderbylimit1.找到表:from2.拿著where指定的約束條件，去文件/表中取出一條條記錄3.將取出的一條條記錄進(jìn)行分組groupby，如果沒有g(shù)roupby，則整體作為一組4.執(zhí)行select（去重）5.將分組的結(jié)果進(jìn)行having過濾6.將結(jié)果按條件排序：orderby7.限制結(jié)果的顯示條數(shù)簡單查詢#創(chuàng)建表createtableemployee(idintnotnulluniqueauto_increment,emp_namevarchar(20)notnull,sexenum(#39;male#39;,#39;female#39;)notnulldefault#39;male#39;,#大部分是男的ageint(3)unsignednotnulldefault28,hire_datedatenotnull,postvarchar(50),post_commentvarchar(100),salarydouble(15,2),officeint,#一個(gè)部門一個(gè)屋子depart_idint);#查看表結(jié)構(gòu)mysqldescemployee;+--------------+-----------------------+------+-----+---------+----------------+|Field|Type|Null|Key|Default|Extra|+--------------+-----------------------+------+-----+---------+----------------+|id|int(11)|NO|PRI|NULL|auto_increment||emp_name|varchar(20)|NO||NULL|||sex|enum(#39;male#39;,#39;female#39;)|NO||male|||age|int(3)unsigned|NO||28|||hire_date|date|NO||NULL|||post|varchar(50)|YES||NULL|||post_comment|varchar(100)|YES||NULL|||salary|double(15,2)|YES||NULL|||office|int(11)|YES||NULL|||depart_id|int(11)|YES||NULL||+--------------+-----------------------+------+-----+---------+----------------+#插入記錄#三個(gè)部門：教學(xué)，銷售，運(yùn)營insertintoemployee(emp_name,sex,age,hire_date,post,salary,office,depart_id)values(#39;egon#39;,#39;male#39;,18,#39;20170301#39;,#39;辦事處#39;,7300.33,401,1),#以下是教學(xué)部(#39;anwen#39;,#39;male#39;,78,#39;20150302#39;,#39;teacher#39;,1000000.31,401,1),(#39;wudi#39;,#39;male#39;,81,#39;20130305#39;,#39;teacher#39;,8300,401,1),(#39;yuanhao#39;,#39;male#39;,73,#39;20140701#39;,#39;teacher#39;,3500,401,1),(#39;liwen#39;,#39;male#39;,28,#39;20121101#39;,#39;teacher#39;,2100,401,1),(#39;jingliyang#39;,#39;female#39;,18,#39;20110211#39;,#39;teacher#39;,9000,401,1),(#39;jinxin#39;,#39;male#39;,18,#39;19000301#39;,#39;teacher#39;,30000,401,1),(#39;成龍#39;,#39;male#39;,48,#39;20101111#39;,#39;teacher#39;,10000,401,1),(#39;歪歪#39;,#39;female#39;,48,#39;20150311#39;,#39;sale#39;,3000.13,402,2),#以下是銷售部門(#39;丫丫#39;,#39;female#39;,38,#39;20101101#39;,#39;sale#39;,2000.35,402,2),(#39;丁丁#39;,#39;female#39;,18,#39;20110312#39;,#39;sale#39;,1000.37,402,2),(#39;星星#39;,#39;female#39;,18,#39;20160513#39;,#39;sale#39;,3000.29,402,2),(#39;格格#39;,#39;female#39;,28,#39;20170127#39;,#39;sale#39;,4000.33,402,2),(#39;張xx#39;,#39;male#39;,28,#39;20160311#39;,#39;operation#39;,10000.13,403,3),#以下是運(yùn)營部門(#39;程咬金#39;,#39;male#39;,18,#39;19970312#39;,#39;operation#39;,20000,403,3),(#39;程咬銀#39;,#39;female#39;,18,#39;20130311#39;,#39;operation#39;,19000,403,3),(#39;程咬銅#39;,#39;male#39;,18,#39;20150411#39;,#39;operation#39;,18000,403,3),(#39;程咬鐵#39;,#39;female#39;,18,#39;20140512#39;,#39;operation#39;,17000,403,3);#ps：如果在windows系統(tǒng)中，插入中文字符，select的結(jié)果為空白，可以將所有字符編碼統(tǒng)一設(shè)置成gbk準(zhǔn)備表和記錄#簡單查詢SELECTid,emp_name,sex,age,hire_date,post,post_comment,salary,office,depart_idFROMemployee;SELECT*FROMemployee;SELECTemp_name,salaryFROMemployee;#避免重復(fù)DISTINCTSELECTDISTINCTpostFROMemployee;#通過四則運(yùn)算查詢SELECTemp_name,salary*12FROMemployee;SELECTemp_name,salary*12ASAnnual_salaryFROMemployee;SELECTemp_name,salary*12Annual_salaryFROMemployee;#定義顯示格式CONCAT()函數(shù)用于連接字符串SELECTCONCAT(#39;姓名:#39;,emp_name,#39;年薪:#39;,salary*12)ASAnnual_salaryFROMemployee;CONCAT_WS()第一個(gè)參數(shù)為分隔符SELECTCONCAT_WS(#39;:#39;,emp_name,salary*12)ASAnnual_salaryFROMemployee;結(jié)合CASE語句：SELECT(CASEWHENemp_name=#39;jingliyang#39;THENemp_nameWHENemp_name=#39;alex#39;THENCONCAT(emp_name,#39;_BIGSB#39;)ELSEconcat(emp_name,#39;SB#39;)END)asnew_nameFROMemployee;小練習(xí)：1查出所有員工的名字，薪資,格式為lt;名字:egonlt;薪資:30002查出所有的崗位（去掉重復(fù)）3查出所有員工名字，以及他們的年薪,年薪的字段名為annual_yearselectconcat(#39;lt;名字:#39;,emp_name,#39;#39;,#39;lt;薪資:#39;,salary,#39;#39;)fromemployee;selectdistinctdepart_idfromemployee;selectemp_name,salary*12annual_salaryfromemployee;where約束where字句中可以使用：比較運(yùn)算符：lt;=lt;=lt;!=between80and100值在80到100之間in(80,90,100)值是80或90或100like'e%'??通配符可以是%或_，??%表示任意多字符??_表示一個(gè)字符?邏輯運(yùn)算符：在多個(gè)條件直接可以使用邏輯運(yùn)算符andornot#1:單條件查詢SELECTemp_nameFROMemployeeWHEREpost=#39;sale#39;;#2:多條件查詢SELECTemp_name,salaryFROMemployeeWHEREpost=#39;teacher#39;ANDsalary10000;#3:關(guān)鍵字BETWEENANDSELECTemp_name,salaryFROMemployeeWHEREsalaryBETWEEN10000AND20000;SELECTemp_name,salaryFROMemployeeWHEREsalaryNOTBETWEEN10000AND20000;#4:關(guān)鍵字ISNULL(判斷某個(gè)字段是否為NULL不能用等號，需要用IS)SELECTemp_name,post_commentFROMemployeeWHEREpost_commentISNULL;SELECTemp_name,post_commentFROMemployeeWHEREpost_commentISNOTNULL;SELECTemp_name,post_commentFROMemployeeWHEREpost_comment=#39;#39;;注意#39;#39;是空字符串，不是nullps：執(zhí)行updateemployeesetpost_comment=#39;#39;whereid=2;再用上條查看，就會有結(jié)果了#5:關(guān)鍵字IN集合查詢SELECTemp_name,salaryFROMemployeeWHEREsalary=3000ORsalary=3500ORsalary=4000ORsalary=9000;SELECTemp_name,salaryFROMemployeeWHEREsalaryIN(3000,3500,4000,9000);SELECTemp_name,salaryFROMemployeeWHEREsalaryNOTIN(3000,3500,4000,9000);#6:關(guān)鍵字LIKE模糊查詢通配符’%’SELECT*FROMemployeeWHEREemp_nameLIKE#39;eg%#39;;通配符’_’SELECT*FROMemployeeWHEREemp_nameLIKE#39;al__#39;;小練習(xí)：1.查看崗位是teacher的員工姓名、年齡2.查看崗位是teacher且年齡大于30歲的員工姓名、年齡3.查看崗位是teacher且薪資在9000-1000范圍內(nèi)的員工姓名、年齡、薪資4.查看崗位描述不為NULL的員工信息5.查看崗位是teacher且薪資是10000或9000或30000的員工姓名、年齡、薪資6.查看崗位是teacher且薪資不是10000或9000或30000的員工姓名、年齡、薪資7.查看崗位是teacher且名字是jin開頭的員工姓名、年薪selectemp_name,agefromemployeewherepost=#39;teacher#39;;selectemp_name,agefromemployeewherepost=#39;teacher#39;andage30;selectemp_name,age,salaryfromemployeewherepost=#39;teacher#39;andsalarybetween9000and10000;select*fromemployeewherepost_commentisnotnull;selectemp_name,age,salaryfromemployeewherepost=#39;teacher#39;andsalaryin(10000,9000,30000);selectemp_name,age,salaryfromemployeewherepost=#39;teacher#39;andsalarynotin(10000,9000,30000);selectemp_name,salary*12fromemployeewherepost=#39;teacher#39;andemp_namelike#39;jin%#39;;groupby單獨(dú)使用GROUPBY關(guān)鍵字分組SELECTpostFROMemployeeGROUPBYpost;注意：我們按照post字段分組，那么select查詢的字段只能是post，想要獲取組內(nèi)的其他相關(guān)信息，需要借助函數(shù)GROUPBY關(guān)鍵字和GROUP_CONCAT()函數(shù)一起使用SELECTpost,GROUP_CONCAT(emp_name)FROMemployeeGROUPBYpost;#按照崗位分組，并查看組內(nèi)成員名SELECTpost,GROUP_CONCAT(emp_name)asemp_membersFROMemployeeGROUPBYpost;GROUPBY與聚合函數(shù)一起使用selectpost,count(id)ascountfromemployeegroupbypost;#按照崗位分組，并查看每個(gè)組有多少人強(qiáng)調(diào)：如果我們用unique的字段作為分組的依據(jù)，則每一條記錄自成一組，這種分組沒有意義多條記錄之間的某個(gè)字段值相同，該字段通常用來作為分組的依據(jù)聚合函數(shù)#強(qiáng)調(diào)：聚合函數(shù)聚合的是組的內(nèi)容，若是沒有分組，則默認(rèn)一組示例：SELECTCOUNT(*)FROMemployee;SELECTCOUNT(*)FROMemployeeWHEREdepart_id=1;SELECTMAX(salary)FROMemployee;SELECTMIN(salary)FROMemployee;SELECTAVG(salary)FROMemployee;SELECTSUM(salary)FROMemployee;SELECTSUM(salary)FROMemployeeWHEREdepart_id=3;小練習(xí)：1.查詢崗位名以及崗位包含的所有員工名字2.查詢崗位名以及各崗位內(nèi)包含的員工個(gè)數(shù)3.查詢公司內(nèi)男員工和女員工的個(gè)數(shù)4.查詢崗位名以及各崗位的平均薪資5.查詢崗位名以及各崗位的最高薪資6.查詢崗位名以及各崗位的最低薪資7.查詢男員工與男員工的平均薪資，女員工與女員工的平均薪資#題1：分組mysqlselectpost,group_concat(emp_name)fromemployeegroupbypost;#題目2：mysqlselectpost,count(id)fromemployeegroupbypost;#題目3：mysqlselectsex,count(id)fromemployeegroupbysex;#題目4：mysqlselectpost,avg(salary)fromemployeegroupbypost;#題目5mysqlselectpost,max(salary)fromemployeegroupbypost;#題目6mysqlselectpost,min(salary)fromemployeegroupbypost;#題目七mysqlselectsex,avg(salary)fromemployeegroupbysex;HAVING過濾HAVING與WHERE不一樣的地方在于!!!!!!#?。。?zhí)行優(yōu)先級從高到低：wheregroupbyhaving#1.Where發(fā)生在分組groupby之前，因而Where中可以有任意字段，但是絕對不能使用聚合函數(shù)。#2.Having發(fā)生在分組groupby之后，因而Having中可以使用分組的字段，無法直接取到其他字段,可以使用聚合函數(shù)select*fromempwheresalary100000;mysqlselectpost,group_concat(emp_name)fromempgroupbyposthavingsalary10000;#錯(cuò)誤，分組后無法直接取到salary字段ERROR1054(42S22):Unknowncolumn#39;salary#39;in#39;havingclause#39;mysqlselectpost,group_concat(emp_name)fromempgroupbyposthavingavg(salary)10000;小練習(xí)：1.查詢各崗位內(nèi)包含的員工個(gè)數(shù)小于2的崗位名、崗位內(nèi)包含員工名字、個(gè)數(shù)3.查詢各崗位平均薪資大于10000的崗位名、平均工資4.查詢各崗位平均薪資大于10000且小于20000的崗位名、平均工資selectpost,group_concat(emp_name),count(id)fromemployeegroupbyposthavingcount(id)lt;2;selectpost,avg(salary)fromemployeegroupbyposthavingavg(salary)10000;selectpost,avg(salary)fromemployeegroupbyposthavingavg(salary)10000andavg(salary)lt;20000;ORDERBY查詢排序按單列排序SELECT*FROMemployeeORDERBYsalary;SELECT*FROMemployeeORDERBYsalaryASC;SELECT*FROMemployeeORDERBYsalaryDESC;按多列排序:先按照age排序，如果年紀(jì)相同，則按照薪資排序SELECT