數(shù)據(jù)庫基本知識和基礎(chǔ)sql語句

1、數(shù)據(jù)庫的發(fā)展歷程沒有數(shù)據(jù)庫，使用磁盤文件存儲數(shù)據(jù)；層次結(jié)構(gòu)模型數(shù)據(jù)庫；網(wǎng)狀結(jié)構(gòu)模型數(shù)據(jù)庫；關(guān)系結(jié)構(gòu)模型數(shù)據(jù)庫：使用二維表格來存儲數(shù)據(jù)；關(guān)系-對象模型數(shù)據(jù)庫；理解數(shù)據(jù)庫RDBMS=管理員（manager）+倉庫（database）database=N個(gè)tabletable：表結(jié)構(gòu)：定義表的列名和列類型！表記錄：一行一行的記錄！Mysql安裝目錄:bin目錄中都是可執(zhí)行文件；my.ini文件是MySQL的配置文件；相關(guān)命令:啟動：netstartmysql；關(guān)閉：netstopmysql；mysql-uroot-p123-hlocalhost；-u：后面的root是用戶名，這里使用的是超級管理員r

2、oot；-p:后面的123是密碼，這是在安裝MySQL時(shí)就已經(jīng)指定的密碼；退出：quit或exit；sql語句語法要求SQL語句可以單行或多行書寫，以分號結(jié)尾；可以用空格和縮進(jìn)來來增強(qiáng)語句的可讀性；關(guān)鍵字不區(qū)別大小寫，建議使用大寫；分類DDL（DataDefinitionLanguage）：數(shù)據(jù)定義語言，用來定義數(shù)據(jù)庫對象：庫、表、列等；DML（DataManipulationLanguage）：數(shù)據(jù)操作語言，用來定義數(shù)據(jù)庫記錄（數(shù)據(jù)）；基本操作查看所有數(shù)據(jù)庫名稱：SHOWDATABASES；切換數(shù)據(jù)庫：USEmydbl,切換到mydbl數(shù)據(jù)庫；創(chuàng)建數(shù)據(jù)庫：CREATEDATABASEIFNO

3、TEXISTSmydb1；修改數(shù)據(jù)庫編碼：ALTERDATABASEmydb1CHARACTERSETutf8創(chuàng)建表：CREATETABLE表名（列名列類型,列名列類型,);查看當(dāng)前數(shù)據(jù)庫中所有表名稱：SHOWTABLES；查看指定表的創(chuàng)建語句：SHOWCREATETABLEemp，查看emp表的創(chuàng)建語句；查看表結(jié)構(gòu)：DESCemp，查看emp表結(jié)構(gòu)；刪除表：DROPTABLEemp，刪除emp表；修改表：修改之添加列：給stu表添加classname列：ALTERTABLEstuADD(classnamevarchar(100);修改之修改列類型：修改stu表的gender列類型為CHAR(

4、2)：ALTERTABLEstuMODIFYgenderCHAR(2);修改之修改列名：修改stu表的gender列名為sex：ALTERTABLEstuchangegendersexCHAR(2);修改之刪除列：刪除stu表的classname列：ALTERTABLEstuDROPclassname;修改之修改表名稱：修改stu表名稱為student：ALTERTABLEstuRENAMETOstudent;其他常用命令:mysql基本操作命令一、數(shù)據(jù)庫操作新增數(shù)據(jù)庫createdatabase數(shù)據(jù)庫名字?jǐn)?shù)據(jù)庫選項(xiàng);數(shù)據(jù)庫選項(xiàng)：規(guī)定數(shù)據(jù)庫內(nèi)部該用什么進(jìn)行規(guī)范字符集：charset具體字符集(

5、utf8)校對集：collate具體校對集(依賴字符集)查看數(shù)據(jù)庫2.1查看所有的數(shù)據(jù)庫showdatabases;匹配查詢：showdatabaseslikepattern;#pattern可以使用通配符_：下劃線匹配，表示匹配單個(gè)任意字符，如：_s，表示任意字符開始，但是以s結(jié)尾的數(shù)據(jù)庫%:百分號匹配，表示匹配任意個(gè)數(shù)的任意字符，如：student%,表示以student開始的所有數(shù)據(jù)庫2.2查看數(shù)據(jù)庫的創(chuàng)建語句showcreatedatabase數(shù)據(jù)庫名字；3修改數(shù)據(jù)庫數(shù)據(jù)庫名字在mysql高版本中不允許修改，所以只能修改數(shù)據(jù)庫的庫選項(xiàng)（字符集和校對集）alterdatabase數(shù)據(jù)庫名

6、字?jǐn)?shù)據(jù)庫選項(xiàng);eg:alterdatabasestucharsetutf8;4.刪除數(shù)據(jù)庫對于數(shù)據(jù)庫的刪除要謹(jǐn)慎考慮，是不可逆的。dropdatabase數(shù)據(jù)庫名字；4.選擇數(shù)據(jù)庫use數(shù)據(jù)庫名字；二、數(shù)據(jù)表操作（字段）新增數(shù)據(jù)表createtable表名（字段名1數(shù)據(jù)類型comment備注，字段名2數(shù)據(jù)類型comment備注，.#最后一行不需要逗號）表選項(xiàng);表選項(xiàng)：1）字符集：charset/characterset（可以不寫，默認(rèn)采用數(shù)據(jù)庫的）2）校對集：collate3）存儲引擎：engine=innodb（默認(rèn)的）：存儲文件的格式（數(shù)據(jù)如何存儲）創(chuàng)建數(shù)據(jù)表的時(shí)候，需要指定要在哪個(gè)數(shù)據(jù)庫

7、下創(chuàng)建。創(chuàng)建方式有隱式創(chuàng)建和顯式創(chuàng)建1）顯式創(chuàng)建：createtable數(shù)據(jù)庫名字.數(shù)據(jù)表名字2）隱式創(chuàng)建：use數(shù)據(jù)庫名字；查看數(shù)據(jù)表2.1查看所有的數(shù)據(jù)表showtables;2.2查看表使用匹配查詢Showtableslikepattern；#與數(shù)據(jù)庫的pattern樣：_和%兩個(gè)通配符2.3查看數(shù)據(jù)表的創(chuàng)建語句showcreatetable數(shù)據(jù)表名字；2.4查看數(shù)據(jù)表的結(jié)構(gòu)desc數(shù)據(jù)表名字；3修改數(shù)據(jù)表3.1修改表名字renametable舊表名to新表名；3.2修改表選項(xiàng)（存儲引擎，字符集和校對集）altertable表名表選項(xiàng);3.3修改字段（新增字段，修改字段名字，修改西段類型

8、，刪除字段）新增字段：altertable表名addcolumn字段名字?jǐn)?shù)據(jù)庫類型位置first/after；位置選項(xiàng)：first在第一個(gè)字段after在某個(gè)字段之后，默認(rèn)就是在最后一個(gè)字段后面修改字段名稱：altertable表名change舊字段名字新字段名字字段數(shù)據(jù)類型位置;eg:altertablestudentnamefullnamevarchar（30）afterid;修改字段的數(shù)據(jù)類型：altertable表名modify字段名字?jǐn)?shù)據(jù)類型位置;刪除字段：altertable表名drop字段名字；4.刪除數(shù)據(jù)表droptable表名;三、數(shù)據(jù)操作新增數(shù)據(jù)inserintotable

9、表名（字段列表）values（值列表）;查看數(shù)據(jù)select*/字段列表from表名where條件;3修改數(shù)據(jù)update表名set字段名二值where條件；注意：使用update操作最好配合limit1使用，避免操作大批量數(shù)據(jù)更新錯誤4.刪除數(shù)據(jù)deletefrom表名where條件；注意：沒有where條件就是默認(rèn)刪除全部數(shù)據(jù).四、列屬性（字段）.刪除主鍵：altertable表名dropprimarykey;2.增加主鍵：altertable表名addprimarykey字段列表）；#可以是復(fù)合主鍵3刪除自增長：只能通過修改字段屬性的方法操作.4刪除唯一鍵：altertable表名dro

10、pindex索引名字；#默認(rèn)的唯一鍵名字就是字段的本身5.增加唯一鍵：altertable表名adduniquekey（字段列表）；#可以是復(fù)合唯一索引五、外鍵約束創(chuàng)建表的時(shí)候增加外鍵constraint外鍵名字foreignkey(外鍵字段)references父表(主鍵字段)；eg:創(chuàng)建父表(班級表)createtableclass(idintprimarykeyauto_increment,namevarchar(lO)notnullcomment班級名字，roomvarchar(lO)notnullcomment教室號)charsetutf8;創(chuàng)建子表(外鍵表)createtables

11、tudent(idintprimarykeyauto_increment,numberchar(10)notnulluniquecomment學(xué)號：itcast+四位數(shù)，namevarchar(10)notnullcomment姓名，c_idintcomment班級ID,增加外鍵foreignkey(c_id)referencesclass(id)charsetutf8;創(chuàng)建表之后增加外鍵altertable表名addconstraint外鍵名字foreignkey(外鍵字段)references父表(主鍵字段)；eg：增加外鍵altertablestudentaddconstraintstu

12、dent_class_fkforeignkey(c_id)referencesclass(id);刪除外鍵altertable表名dropforeignkey外鍵名字；#查看外鍵名字需要通過表創(chuàng)建語句來查詢.eg:刪除外鍵altertablestudentdropforeignkeystudent_ibfk_l;數(shù)據(jù)查詢語法（DQL）DQL就是數(shù)據(jù)查詢語言，數(shù)據(jù)庫執(zhí)行DQL語句不會對數(shù)據(jù)進(jìn)行改變，而是讓數(shù)據(jù)庫發(fā)送結(jié)果集給客戶端。SELECTselection_list/*要查詢的列名稱*/FROMtable_list/*要查詢的表名稱*/WHEREcondition/*行條件*/GROUPBY

13、grouping_columns/*對結(jié)果分組*/HAVINGcondition/*分組后的行條件*/ORDERBYsorting_columns/*對結(jié)果分組*/LIMIToffset_start,row_count/*結(jié)果限定*/基礎(chǔ)查詢查詢所有列SELECT*FROMstu;查詢指定列SELECTsid,sname,ageFROMstu;條件查詢條件查詢介紹條件查詢就是在查詢時(shí)給出WHERE子句，在WHERE子句中可以使用如下運(yùn)算符及關(guān)鍵字:二、！=、二、=；BETWEENAND；IN(set)；ISNULL；AND；OR；NOT；查詢性別為女，并且年齡50的記錄SELECT*FROMs

14、tuWHEREgender=femaleANDge50;2.3查詢學(xué)號為S_1001，或者姓名為liSi的記錄SELECT*FROMstuWHEREsid=S_1001ORsname=liSi;查詢學(xué)號為S_1001,S_1002,S_1003的記錄SELECT*FROMstuWHEREsidIN(S_1001,S_1002,S_1003);查詢學(xué)號不是S_1001,S_1002,S_1003的記錄SELECT*FROMtab_studentWHEREs_numberNOTIN(S_1001,S_1002,S_1003);查詢年齡為null的記錄SELECT*FROMstuWHEREageIS

15、NULL;查詢年齡在20到40之間的學(xué)生記錄SELECT*FROMstuWHEREage=20ANDage=40;或者SELECT*FROMstuWHEREageBETWEEN20AND40;查詢性別非男的學(xué)生記錄SELECT*FROMstuWHEREgender!=male;或者SELECT*FROMstuWHEREgendermale;或者SELECT*FROMstuWHERENOTgender=male;查詢姓名不為null的學(xué)生記錄SELECT*FROMstuWHERENOTsnameISNULL;或者SELECT*FROMstuWHEREsnameISNOTNULL;模糊查詢當(dāng)想查詢

16、姓名中包含a字母的學(xué)生時(shí)就需要使用模糊查詢了。模糊查詢需要使用關(guān)鍵字LIKE。查詢姓名由5個(gè)字母構(gòu)成的學(xué)生記錄SELECT*FROMstuWHEREsnameLIKE;模糊查詢必須使用LIKE關(guān)鍵字。其中“_”匹配任意一個(gè)字母，5個(gè)“_”表示5個(gè)任意字母。3.2查詢姓名由5個(gè)字母構(gòu)成，并且第5個(gè)字母為“i”的學(xué)生記錄SELECT*FROMstuWHEREsnameLIKEi;3.3查詢姓名以“z”開頭的學(xué)生記錄SELECT*FROMstuWHEREsnameLIKEz%;其中“”匹配0n個(gè)任何字母。3.4查詢姓名中第2個(gè)字母為“i”的學(xué)生記錄SELECT*FROMstuWHEREsnameLI

17、KE_i%;3.5查詢姓名中包含“a”字母的學(xué)生記錄SELECT*FROMstuWHEREsnameLIKE%a%;字段控制查詢?nèi)コ貜?fù)記錄去除重復(fù)記錄(兩行或兩行以上記錄中系列的上的數(shù)據(jù)都相同)，例如emp表中sal字段就存在相同的記錄。當(dāng)只查詢emp表的sal字段時(shí)，那么會出現(xiàn)重復(fù)記錄，那么想去除重復(fù)記錄，需要使用DISTINCT：SELECTDISTINCTsalFROMemp;查看雇員的月薪與傭金之和因?yàn)閟al和comm兩列的類型都是數(shù)值類型，所以可以做加運(yùn)算。如果sal或comm中有一個(gè)字段不是數(shù)值類型，那么會出錯。SELECT*,sal+commFROMemp;comm列有很多記錄

18、的值為NULL，因?yàn)槿魏螙|西與NULL相加結(jié)果還是NULL，所以結(jié)算結(jié)果可能會出現(xiàn)NULL。下面使用了把NULL轉(zhuǎn)換成數(shù)值0的函數(shù)IFNULL：SELECT*,sal+IFNULL(comm,0)FROMemp;給列名添加別名在上面查詢中出現(xiàn)列名為sal+IFNULL(comm,0),這很不美觀，現(xiàn)在我們給這一列給出一個(gè)別名，為total：SELECT*,sal+IFNULL(comm,0)AStotalFROMemp;給列起別名時(shí)，是可以省略AS關(guān)鍵字的：SELECT*,sal+IFNULL(comm,0)totalFROMemp;排序查詢所有學(xué)生記錄，按年齡升序排序SELECT*FROMs

19、tuORDERBYsageASC;或者SELECT*FROMstuORDERBYsage;查詢所有學(xué)生記錄，按年齡降序排序SELECT*FROMstuORDERBYageDESC;查詢所有雇員，按月薪降序排序，如果月薪相同時(shí)，按編號升序排序SELECT*FROMempORDERBYsalDESC,empnoASC;聚合函數(shù)聚合函數(shù)是用來做縱向運(yùn)算的函數(shù)：COUNT():統(tǒng)計(jì)指定列不為NULL的記錄行數(shù)；MAX():計(jì)算指定列的最大值，如果指定列是字符串類型，那么使用字符串排序運(yùn)算；MIN()：計(jì)算指定列的最小值，如果指定列是字符串類型，那么使用字符串排序運(yùn)SUM():計(jì)算指定列的數(shù)值和，如果指

20、定列類型不是數(shù)值類型，那么計(jì)算結(jié)果為0;AVG():計(jì)算指定列的平均值，如果指定列類型不是數(shù)值類型，那么計(jì)算結(jié)果為0；COUNT當(dāng)需要縱向統(tǒng)計(jì)時(shí)可以使用C0UNT()。查詢emp表中記錄數(shù):SELECTCOUNT(*)AScntFROMemp;查詢emp表中有傭金的人數(shù)：SELECTCOUNT(comm)cntFROMemp;注意，因?yàn)閏ount()函數(shù)中給出的是comm列，那么只統(tǒng)計(jì)comm列非NULL的行數(shù)。查詢emp表中月薪大于2500的人數(shù)：SELECTCOUNT(*)FROMempWHEREsal2500;統(tǒng)計(jì)月薪與傭金之和大于2500元的人數(shù)：SELECTCOUNT(*)AScnt

21、FROMempWHEREsal+IFNULL(comm,0)2500;查詢有傭金的人數(shù)，以及有領(lǐng)導(dǎo)的人數(shù)：SELECTCOUNT(comm),COUNT(mgr)FROMemp;SUM和AVG當(dāng)需要縱向求和時(shí)使用sum()函數(shù)。查詢所有雇員月薪和：SELECTSUM(sal)FROMemp;查詢所有雇員月薪和，以及所有雇員傭金和：SELECTSUM(sal),SUM(comm)FROMemp;查詢所有雇員月薪+傭金和：SELECTSUM(sal+IFNULL(comm,0)FROMemp;統(tǒng)計(jì)所有員工平均工資：SELECTSUM(sal),COUNT(sal)FROMemp;或者SELECTA

22、VG(sal)FROMemp;MAX和MIN查詢最高工資和最低工資：SELECTMAX(sal),MIN(sal)FROMemp;分組查詢當(dāng)需要分組查詢時(shí)需要使用GROUPBY子句，例如查詢每個(gè)部門的工資和，這說明要使用部分來分組。分組查詢查詢每個(gè)部門的部門編號和每個(gè)部門的工資和：SELECTdeptno,SUM(sal)FROMempGROUPBYdeptno;查詢每個(gè)部門的部門編號以及每個(gè)部門的人數(shù)：SELECTdeptno,COUNT(*)FROMempGROUPBYdeptno;查詢每個(gè)部門的部門編號以及每個(gè)部門工資大于1500的人數(shù)：SELECTdeptno,COUNT(*)FROM

23、empWHEREsal1500GROUPBYdeptno;HAVING子句查詢工資總和大于9000的部門編號以及工資和：SELECTdeptno,SUM(sal)FROMempGROUPBYdeptnoHAVINGSUM(sal)9000;注意，WHERE是對分組前記錄的條件，如果某行記錄沒有滿足WHERE子句的條件，那么這行記錄不會參加分組；而HAVING是對分組后數(shù)據(jù)的約束。8LIMITLIMIT用來限定查詢結(jié)果的起始行，以及總行數(shù)。查詢5行記錄，起始行從0開始SELECT*FROMempLIMIT0,5;注意，起始行從0開始，即第一行開始！查詢10行記錄，起始行從3開始SELECT*FR

24、OMempLIMIT3,10;分頁查詢?nèi)绻豁撚涗洖?0條，希望查看第3頁記錄應(yīng)該怎么查呢？第一頁記錄起始行為0,共查詢10行；第二頁記錄起始行為10,一共查詢10行；第三頁記錄起始行為20,一共查詢10行；多表連接查詢連接查詢內(nèi)連接外連接左外連接右外連接全外連接（MySQL不支持）自然連接子查詢連接查詢連接查詢就是求出多個(gè)表的乘積，例如t1連接t2,那么查詢出的結(jié)果就是t1*t2。tl4SELECT本FROMtl,吃c4d5egt2ab匚1a4d1a5e1ag2b4d2b5e2b53u43Cr5e3uG單行單列(用于條件)單行多列(用于條件)多行單列(用于條件)多行多列(用于表)練習(xí)：工資高

25、于smith的員工。分析：查詢條件：工資smith工資，其中smith工資需要一條子查詢。第一步：查詢smith的工資SELECTsalFROMempWHEREename=smith第二步：查詢高于smith工資的員工SELECT*FROMempWHEREsal（$第一步）結(jié)果：SELECT*FROMempWHEREsal(SELECTsalFROMempWHEREename=smith)子查詢作為條件子查詢形式為單行單列工資高于30部門所有人的員工信息分析：查詢條件：工資高于30部門所有人工資，其中30部門所有人工資是子查詢。高于所有需要使用all關(guān)鍵字。第一步：查詢30部門所有人工資SELECTsalFROMempWHEREdeptno=30;第二步：查詢高于30部門所有人工資的員工信息SELECT*FROMempWHEREsalALL（$第一步）結(jié)果：SELECT*FROMempWHEREsalALL(SELECTsalFROMempWHEREdeptno=30)子查詢作為條件子查詢形式為多行單列（當(dāng)子查詢結(jié)果集形式為多行單列時(shí)可以使用ALL或ANY關(guān)鍵字）查詢工作和工