javaweb簡單學(xué)習(xí)指導(dǎo)數(shù)據(jù)庫database筆記

1、1. show 1. show dropdatabasealterdatabasemydb2charactersetgb2312; show create database mydb2;use tt;createtablea nameselect *from 為恢復(fù)庫，要先創(chuàng)建庫 createdatabaseuse為恢復(fù)庫，要先創(chuàng)建庫 createdatabaseuse為恢復(fù)庫，要先創(chuàng)建庫 createdatabaseuse,sex varchar(4), birthday date, entry_datedate, job varchar(40), resumeshow tables;sho

2、w create table employee;desc altertableemployeeaddimagealtertableemployeemodifyjobaltertableemployeedroprenametableemployeetoaltertableusercharacterset altertableuserchangecolumnnameusernamedroptable renametableusertoaltertableusercharacterset altertableuserchangecolumnnameusernamedroptable renameta

3、bleuserto select*from 客戶采用gb2312 setcharacter_set_c select * from employee; C ntgb2312serverdatabase將ini. 將 將為bbb4000元,job改為ccc 將 將為bbb4000元,job改為ccc 。 。 。查詢selectfrom語句（select*from selectdistinctenglishfromselect select +english+math)+10as fromfromselectnameselect select +english+math)+10as fromfro

4、mselectnameselect select*fromstudentwhereselectnamefromstudentwhereselectnamefromstudentwhereenglish80andenglish=80andselect*fromstudentwheremathselect*fromstudentwherenamelike李%; select*fromstudentwherenamelike李select*fromstudentwheremath80andselectname,mathfromstudentorderby+english+math) 總分fromst

5、udentorderbyselect select+english+math) fromstudentorderby select*fromstudentwherenamelike李orderby selectcount(*)from count（count只統(tǒng)有值的行 selectcount(*)from count（count只統(tǒng)有值的行selectsum(math)fromselect select select selectselectselect （歸組查selectproductfromordersgroupbyproduc ving 3. createtable(namecrea

6、tetablestudent primary key namedrop table student; 3. createtable(namecreatetablestudent primary key namedrop table student; (namevarchar(40)drop table student; (namevarchar(40)uniquenotcreatetablehusband namecreatetable(name,4. 1. 表的設(shè)計方案: name(primaryname4. 1. 表的設(shè)計方案: name(primaryname, 2. createtab

7、le(primary name salary(name(, primary key(teacher_id,student_id), (, primary key(teacher_id,student_id), createtableteacher_student Id key,teacher(id), student(id)3. create(name(city 4. create(primarycreate(name(city 4. create(primaryname ,點3 SELECT 列名1,列名2FROM 表名WHERE 列名1 運SELECT 列名1,列名2FROM 表名WHER

8、E 列名1 運算符 比較操作符都比較簡單，不再贅述。關(guān)于N和LIKE，專門拿出來重點說2、N SELECTID,Name,AgeFROMStudentsWHEREAgeN18AND=等大18ANDNameLIKE張5、 SELECTID,NameFROMStudentsWHERENameLIKE張%ORNameLIKE李6、IN WHERE子句中規(guī)定多個值。表示：在哪些值當中SELECTID,Name FROMStudentsWHEREAgeIN7、NOT SELECTID,NameFROMStudentsWHERENameNOTLIKE張1）查詢學(xué)生信息表的學(xué)號SELECT1）查詢學(xué)生信息表

9、的學(xué)號SELECTID,Name,AgeFROMStudentsORDERBY或指明SELECTID,Name,AgeFROMStudentsORDERBYAge SELECTID,Name,AgeFROMStudentsORDERBYAgeSELECTID,Name,ScoreFROMStudentsORDERBYScoreDESC,ID這個查詢含義：首先按Score 倒序排列，如果有多Score相同，再按ID9、2廣3老1趙升默倒1) SELECTIDASStudentID,NameASStudentNameFROMSELECTCOUNT(ID)ASStudentCountFROMSELE

10、CTDISTINCT 列名稱FROM 表名SELECTDISTINCTCityFROMSELECTDISTINCT(Score),Count(ID)FROMStudentGROUPBY學(xué)生成績可能重復(fù)，以此得到分數(shù)、得到這一成績的學(xué)生數(shù)。后續(xù)會詳細介紹GROUPBY用法MAX 函數(shù)返回一列中的最大值。NULL 值不包括在計MIN 函數(shù)返回一列中的最小值。NULL 值不包括在計算中MIN MAX 也可用于文本列，以獲得按字母順序排列的最高或最低值SELECTMAX(Score)FROMSELECTS.ID,S.Name,M.NameASMajorName FROM Students AS SLE

11、FT ajorsASONS.MajorID=SELECTMIN(Age)FROMSELECTSELECTMIN(Age)FROMSELECTSUM(Score)ASTotalScoreFROMSCWHEREAVG 函數(shù)返回數(shù)值列的平均SELECTAVG(Age)ASAgeAverageFROM2）求課程IDC001SELECTAVG(Score)FROMSCWHERECOUNT函數(shù)返回匹配指定條件的SELECTCOUNT(ID)FROMSELECTCOUNT(DISTINCTAge)FROMSELECTCOUNT(ID)FROMStudentsWHERESex=男SELECTSex,COUNT

12、(ID)FROM 。 。SELECTSex,COUNT(ID)FROM 2) SELECTCity,COUNT(ID) FROMStudentsGROUPBYSELECTStudentID,AVG(Score)FROMSCGROUPBYDELETEFROMStudentsWHEREIDNOTIN(SELECTMAX(ID) FROMStudentsGROUPSQL 中增加HAVING 子句原因是，WHERE 關(guān)鍵字無法與合計函數(shù)一起SELECTStudentID,AVG(Score)FROMSCGROUPBYStudentIDHAVING2）還是用HAVING 的SQL語句中，可以有普通的WH

13、ERE條查詢平均成績大于等于60，且學(xué)生ID等于1的學(xué)生的ID及平均成績SELECTStudentID,AVG(Score)FROMSC WHERE StudentID=1GROUP BY HAVINGFROM table_nameWHEREcolumn_nameoperatorvalue GROUP BY column_name SELECTStudentIDFROMSCGROUPBYStudentIDHAVINGSELECTStudentIDFROMSCGROUPBYStudentIDHAVING SELECTTOP3ID,NameFROM StudentsORDERBYAgeSELECT

14、ID,Name,AgeFROMStudentsWHEREAgeIN(SELECTTOP3AgeFROM18、CaseCASE表達式有兩種格式 CASE搜索表達式，它通過計算一組布爾表達式來確定SELECTIDNameCASEWHENAge=18THEN成年ELSE未成年ENDAS 是否成FROM SELECTID, NameCASESexWHEN0THEN男WHEN1THEN女ELSE其他ENDASFROM THENresult_expres.n ELSE else_result_expresCASEWHENwhen_expresTHENresult_expres.n ELSE else_re

15、sult_expresSQLSQL語句SELECT CASE WHEN Sex=0 THEN 男 ELSE 女 END AS SUM(CASE WHEN Age=18 THEN 1 ELSE 0 END) AS 成年FROM 男3女2SELECT SUM(CASEWHENAge=18THEN 1ELSE0ENDAS 成年,SUM(CASEWHENAge18 THEN 1 ELSE 0 END) AS 未成年FROM 6 1.INNERJOIN（內(nèi)連接 1.INNERJOIN（內(nèi)連接 SELECTStudents.ID,Students.Name,Majors.NameASMajorName F

16、ROM Students INNER JOONStudents.MajorID=SELECTFROM table1 INNER joON 外連接分為三種：左外連接，右外連接，全外連接。對應(yīng)SQL：LEFT/RIGHT/FULLOUTERJOIN。通省略outer 這個關(guān)鍵字。寫成：LEFT/RIGHT/FULL JOIN。重點：至少有一方保留全集，沒有匹配行用NULL代替外連接分為三種：左外連接，右外連接，全外連接。對應(yīng)SQL：LEFT/RIGHT/FULLOUTERJOIN。通省略outer 這個關(guān)鍵字。寫成：LEFT/RIGHT/FULL JOIN。重點：至少有一方保留全集，沒有匹配行用N

17、ULL代替 匹配項，用 NULL 代替 就用 NULL 代替。 ( SELECTStudents.ID,Students.Name,Majors.NameASMajorName FROM Students LEFT JOONStudents.MajorID=SELECTStudents.ID,Students.Name,Majors.NameASMajorName FROM Students FULL JOONStudents.MajorID=SELECTStudents.ID,Students.Name,Majors.NameASMajorName FROM Students RIGHT J

18、OONStudents.MajorID= 3.CROSSJOIN（交叉連接 3.CROSSJOIN（交叉連接 FROMStudentsCROSS4. sql 2. notnullprimary)create4. sql 2. notnullprimary)create)alter table 需加外鍵的 表 add constra 外鍵 名 foreign key( 需加外鍵表的字段 es 關(guān)聯(lián)表名(關(guān)聯(lián)字段名); 1. Apache 2. API3. QueryRunne 3.1 構(gòu)造方 3.2 主要方法update(ConnectionconnStringsqlObjectparamsthrows3. QueryRunne 3.1 構(gòu)造方 3.2 主要方法update(ConnectionconnStringsqlObjectparamsthrowsSQLException: , pu