韓jdbc筆記軟件圖解之mysql

mysqlmysqlAB跨平臺(tái)(windowslinuxunix/蘋(píng)果(13000個(gè))/安全/(對(duì)資源要求不高.)37.7mcpu/內(nèi)存要求不高安裝和配置過(guò)程見(jiàn)mysqlmysqlwindowdosmysql數(shù)據(jù)庫(kù).dosmysql 用戶名-p特別說(shuō)明:-p后面的不要有空格/使用該命令的時(shí)候，需要配置一下環(huán)境變量mysqlmysql數(shù)據(jù)庫(kù)mysql中創(chuàng)建數(shù)據(jù)庫(kù)createdatabasemydb1createdatabasemydb1;【sqlutf-8mydb2createdatabasemydb2charactersetutf-8mydb3createdatabasemydb3charactersetutf8collatemysqlshowcreatedatabse數(shù)據(jù)庫(kù)名；showshowcreatedatabase數(shù)據(jù)名④刪除數(shù)據(jù)庫(kù)dropdatabase⑤use數(shù)據(jù)庫(kù)名mysqldump–u用戶名–p數(shù)據(jù)名>存放路徑dos控制臺(tái)下直接執(zhí)行恢復(fù)數(shù)據(jù)庫(kù)mydb2但是這個(gè)數(shù)據(jù)庫(kù)目前是空usemysqlsourcecreatecreatetable表名charactersetcollatecreatetableusersidint,namevarchar(64),pwdvarchar(64),birthdaydate)mysql數(shù)據(jù)類(lèi)型(重點(diǎn)①數(shù)值型 m默認(rèn)為1createtabletest1idbit(1));這里顯示亂碼tinyintunsigned]如果是有符號(hào)則表示-1281270-createtabletest3(numtinyint)---128到127createtabletest4(numtinyintunsigned) 0255 samllint是兩個(gè)字節(jié)表示的帶符號(hào) 負(fù)的2的15次方到2的15次方-1,無(wú)符號(hào)2的16方- md:案例createtabletest5(numfloat);createtabletest6(numfloat(5,1));float類(lèi)似，只是表示的范圍更大,createtabletest7numnumeric);這樣其實(shí)就是可以存放整數(shù)createtabletest8numnumeric(5,2));//5,②字串類(lèi)型m0-255,定長(zhǎng)char(20)如果你存放‘a(chǎn)bc’字串，實(shí)際在表 案例createtabletest11(name小技巧mysql自帶的默認(rèn)支持utf8碼，所有我們?cè)谔砑又形牡臅r(shí)候，需要設(shè)置讓gbkshowvariableslike setcharacter_set_=gbk;//可以存中setcharacter_set_results=gbk;m,范圍0-65535varchar(20)‘a(chǎn)bc’字串，實(shí)際在表案例省略建議:..學(xué)號(hào)...255,25565535則使用該類(lèi)型，可以表示更大的字串③日期類(lèi)型(年-月-日createtabletest12(birthdaydate-月-createtabletest13(hiredate-月-:時(shí):分:datetimeupdatecreatetabletest14(namevarchar(64),salfloat,hiredate1timestamp,hiredate2建議:如果不知道該不該用timestamp，就不要用bitcreatetableemp(idint,namevarchar(64),birthdaydate,Entry_datedate,jobvarchar(32),salaryfloat,resumetext)altertableadddaltertablemodifyaltertabledroprenametabletoaltertablecharacterset字符集名altertableuserchangecolumnnameusername案例

imagealtertableempaddimagejob60。altertableempmodifyjobvarchar(60);刪 列altertableemp user。renametableemptouser;utf-8altertableusercharactersetutf8;namealtertableuserchangecolumnnameusername如何顯示創(chuàng)建表的指令showcreatetable表名insert語(yǔ)句insertinsertinto表名[列名]values( createtabletest15(namevarchar(64));insertintotest15(name)values(‘a(chǎn)aa’);insertintotest15(name)values(34);createtabletest16(ageinsertintotest16(age)insertintotest16age)values(‘a(chǎn)aa’);(錯(cuò)insertintotest16age)values(‘111’);(ok,但是不是好的寫(xiě)法40的列中。valuescreatetabletest17(idint,namevarchar(64));insertintotest17(id,name)values(3,’aaa’);insertintotest17(name,id)values(’aaa’,3);insertintotableupdate基本語(yǔ)法updateupdateset列名=where說(shuō)明:如果where后面沒(méi)有條件，則相當(dāng)于對(duì)整個(gè)表進(jìn)行操SETWHEREWHERE5000updateemployeeset將為’zs’的員工薪水修改為3000元;updateemployeesetsal=3000wherename=’zs’;wu1000updateemployeeset wheredelelefromdelelefromwhere條件注意where子句，將刪除表中所有數(shù)據(jù)。Delete語(yǔ)句不能刪除某一列的值（deletedroptableinsertupdate一樣，從一個(gè)表中刪除記錄將引起其它表的參照完整性問(wèn)題，在修改TRUNCATETABLEdeletetruncatetable表名，可以刪除表的記錄，速度快，但不能回滾mysql中事務(wù)的特殊說(shuō)明mysql控制臺(tái)是默認(rèn)自動(dòng)提交事務(wù) savepoint//操作rollbackto保存點(diǎn)selectselectselect列名,列（可以運(yùn)行）fromwhere條件注意事項(xiàng)Selectcolumnselect*from表名;From指定查詢哪DISTINCTselectdistinct*fromselect*from查詢表中所有學(xué)生的和對(duì)應(yīng)的英語(yǔ)成績(jī)selectname,englishfromselectdistinct*from10分特長(zhǎng)分(10分)selectenglish+math++10,namefromselectenglishas‘英語(yǔ)’,mathas數(shù)學(xué),fromwhere查詢?yōu)閣u的學(xué)生成select ,namefromstudentwherename=90select*fromstudentwhere200select*fromstudentwhere(math+english+where案例80－90select*fromstudentwhereenglish>=80and89,90,91select*fromstudentwheremathinselect*fromstudentwherenamelik‘李查詢數(shù)學(xué)分>80，語(yǔ)文分>80select*fromstudentwherematn>80andorderbyselectname,mathfromstudentorderbyselectmath+english+asallfen,namefromstudentorderbyselect(math+english+)asallfen,namefromstudentwherenamelike‘李%’orderby

selectcount(*)from90selectcount(*)fromstudentwhere250selectcount(*)fromstudentwhere sum的用法selectsum(math)fromselect )fromselect )fromselect )/count(*)fromavgselectavg(math)fromselect )fromgroupby練習(xí)：對(duì)訂單表品歸類(lèi)后，顯示每一類(lèi)商品的總selectproduct,sum(price)fromordersgroupbyhaving練習(xí)：查詢了幾類(lèi)商品，并且每類(lèi)總價(jià)大于100的商selectproduct,sum(price)fromordersgroupbyproducthavingCURRENT_DATE(CURRENT_TIME( DATE(datetimeDATE_ADD(date2,INTERVALd_typeDATE_SUB(date2,INTERVALd_typeDATEDIFF(date1,date2兩個(gè)時(shí)間差(多少小時(shí)多少分鐘NOW(案例selectcurrent_date()fromdualselectcurrent_time()fromdual; 說(shuō) 有一個(gè)留言createtablemessage(idint,titlevarchar(64),publishdate請(qǐng)查詢出，兩個(gè)小時(shí)內(nèi)，發(fā)布的消息select*frommessage date_add(publishdate,interval2hour)>=date_add(日期/date/datetime/timestamp,interval type可以使用如下值:，常用函數(shù)一覽圖CONCAT(string2[,...INSTR(string,substringsubstringstring中出現(xiàn)的位置UCASE(string2LCASE(string2LEFT(string2,lengthstring2lengthLENGTH(stringstring (str,search_str,rece_str在str中用rece_str替換STRCMP(string1,string2 position[,lengthstrposition開(kāi)始,lengthLTRIM(string2)RTRIM(string2把ename列的smiTh第一個(gè)字母大寫(xiě)，其它全部小寫(xiě)，怎么辦? UCASE(SUBSTRING(LCASE(‘smiTh’),1,1))fromdual;//‘smiTh’//‘smiTh’去掉首字母后，余下的部分取出,//concat(ucase(substring(‘smiTh’,1,1)),lcase(substring(‘smiTh’,2,length(‘smiTh’)-1)))from結(jié)果selectconcat(lcase(substring('smiTh',1,1)),ucase(substring('smiTh',2,length('smiTh')-1)))frommysql①primarykey(主鍵特點(diǎn):主鍵是用于唯一標(biāo)識(shí)一條記錄的約束一最多只能有一個(gè)主鍵主鍵不能為createtableuser1(idintprimarykey,name②createtableuser2(idintprimarykeyauto_increment,name③unique（唯一特點(diǎn):表的某列的值不能重復(fù),可以為null(可以有多個(gè)null),一中可以有多個(gè)unique.createtableuser4(idintunique,namevarchar(32));④notnull(非空mysqlnull,notnullcreatetableuser5(idintprimarykey,namevarchar(32)not⑤外鍵foreigncreatetabledept(idintprimarykey,namevarchar(64));insertintodeptvalues(1,’財(cái)務(wù)部createtableemp(idintprimarykey,namevarchar(32),deptidintreferencescreatetableemp(idintprimarynamevarchar(32),deptidint,constraintemp_fkforeignke