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

數(shù)據(jù)庫基本知識和基礎(chǔ)sql語句數(shù)據(jù)庫基本知識和基礎(chǔ)sql語句數(shù)據(jù)庫基本知識和基礎(chǔ)sql語句xxx公司數(shù)據(jù)庫基本知識和基礎(chǔ)sql語句文件編號：文件日期：修訂次數(shù)：第1.0次更改批準(zhǔn)審核制定方案設(shè)計(jì)，管理制度數(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個tabletable：表結(jié)構(gòu)：定義表的列名和列類型！表記錄：一行一行的記錄！Mysql安裝目錄:bin目錄中都是可執(zhí)行文件；文件是MySQL的配置文件；相關(guān)命令:啟動：netstartmysql；關(guān)閉：netstopmysql；mysql-uroot-p123-hlocalhost；-u：后面的root是用戶名，這里使用的是超級管理員root；-p：后面的123是密碼，這是在安裝MySQL時就已經(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ù)庫：USEmydb1，切換到mydb1數(shù)據(jù)庫；創(chuàng)建數(shù)據(jù)庫：CREATEDATABASE[IFNOTEXISTS]mydb1；修改數(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(2)：ALTERTABLEstuMODIFYgenderCHAR(2);修改之修改列名：修改stu表的gender列名為sex：ALTERTABLEstuchangegendersexCHAR(2);修改之刪除列：刪除stu表的classname列：ALTERTABLEstuDROPclassname;修改之修改表名稱：修改stu表名稱為student：ALTERTABLEstuRENAMETOstudent;其他常用命令:mysql基本操作命令一、數(shù)據(jù)庫操作1.新增數(shù)據(jù)庫create

database數(shù)據(jù)庫名字[數(shù)據(jù)庫選項(xiàng)];數(shù)據(jù)庫選項(xiàng)：規(guī)定數(shù)據(jù)庫內(nèi)部該用什么進(jìn)行規(guī)范

字符集：charset具體字符集(utf8)

校對集：collate具體校對集（依賴字符集）

2.查看數(shù)據(jù)庫查看所有的數(shù)據(jù)庫showdatabases;

匹配查詢：showdatabaseslike'pattern';

#pattern可以使用通配符

_：下劃線匹配，表示匹配單個任意字符，如：_s，表示任意字符開始，但是以s結(jié)尾的數(shù)據(jù)庫

%：百分號匹配，表示匹配任意個數(shù)的任意字符，如：student%，表示以student開始的所有數(shù)據(jù)庫

查看數(shù)據(jù)庫的創(chuàng)建語句showcreatedatabase數(shù)據(jù)庫名字;

3.修改數(shù)據(jù)庫數(shù)據(jù)庫名字在mysql高版本中不允許修改，所以只能修改數(shù)據(jù)庫的庫選項(xiàng)（字符集和校對集）

alterdatabase數(shù)據(jù)庫名字[數(shù)據(jù)庫選項(xiàng)];eg:alterdatabasestucharsetutf8;

4.刪除數(shù)據(jù)庫對于數(shù)據(jù)庫的刪除要謹(jǐn)慎考慮，是不可逆的。

dropdatabase數(shù)據(jù)庫名字;

4.選擇數(shù)據(jù)庫use數(shù)據(jù)庫名字;二、數(shù)據(jù)表操作（字段）

1.新增數(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ù)據(jù)庫下創(chuàng)建。創(chuàng)建方式有隱式創(chuàng)建和顯式創(chuàng)建1)顯式創(chuàng)建：createtable數(shù)據(jù)庫名字.數(shù)據(jù)表名字2)隱式創(chuàng)建：use數(shù)據(jù)庫名字;

2.查看數(shù)據(jù)表

查看所有的數(shù)據(jù)表showtables;

查看表使用匹配查詢

Showtableslike‘pattern’;

#與數(shù)據(jù)庫的pattern一樣：_和%兩個通配符

查看數(shù)據(jù)表的創(chuàng)建語句

showcreatetable數(shù)據(jù)表名字;查看數(shù)據(jù)表的結(jié)構(gòu)

desc數(shù)據(jù)表名字;

3.修改數(shù)據(jù)表修改表名字

renametable舊表名to新表名;

修改表選項(xiàng)（存儲引擎，字符集和校對集）altertable表名[表選項(xiàng)];修改字段（新增字段，修改字段名字,修改西段類型,刪除字段）新增字段：altertable

表名add[column]字段名字?jǐn)?shù)據(jù)庫類型[位置first/after];

位置選項(xiàng)：first在第一個字段

after在某個字段之后，默認(rèn)就是在最后一個字段后面

修改字段名稱：altertable表名change舊字段名字新字段名字字段數(shù)據(jù)類型[位置];

eg:altertablestudentnamefullnamevarchar(30)

afterid;

修改字段的數(shù)據(jù)類型：altertable表名modify字段名字?jǐn)?shù)據(jù)類型[位置];刪除字段：altertable表名drop字段名字;

4.刪除數(shù)據(jù)表droptable表名;

三、數(shù)據(jù)操作1.新增數(shù)據(jù)inserintotable表名[(字段列表)]values（值列表);2.查看數(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ù).

四、列屬性（字段）1.刪除主鍵：altertable表名dropprimarykey;2.增加主鍵：altertable表名addprimarykey(字段列表);

#可以是復(fù)合主鍵3.刪除自增長：只能通過修改字段屬性的方法操作.4.刪除唯一鍵：altertable表名dropindex索引名字;

#默認(rèn)的唯一鍵名字就是字段的本身5.增加唯一鍵：altertable表名adduniquekey(字段列表);

#可以是復(fù)合唯一索引

五、外鍵約束1.創(chuàng)建表的時候增加外鍵constraint外鍵名字foreignkey(外鍵字段)references父表（主鍵字段）;eg:--創(chuàng)建父表（班級表）createtableclass(

idintprimarykeyauto_increment,

namevarchar(10)notnullcomment'班級名字',

roomvarchar(10)notnullcomment'教室號'

)charsetutf8;--創(chuàng)建子表（外鍵表）

createtablestudent(

idintprimarykeyauto_increment,

numberchar(10)notnulluniquecomment'學(xué)號：itcast+四位數(shù)',

namevarchar(10)notnullcomment'姓名',

c_idintcomment'班級ID',

--增加外鍵

foreignkey(c_id)referencesclass(id)

)charsetutf8;2.創(chuàng)建表之后增加外鍵altertable表名addconstraint外鍵名字foreignkey(外鍵字段)references父表(主鍵字段);eg:--增加外鍵

altertablestudentaddconstraintstudent_class_fk

foreignkey(c_id)referencesclass(id);3.刪除外鍵altertable表名dropforeignkey外鍵名字;

#查看外鍵名字需要通過表創(chuàng)建語句來查詢.eg:--刪除外鍵altertablestudentdropforeignkeystudent_ibfk_1;數(shù)據(jù)查詢語法（DQL）DQL就是數(shù)據(jù)查詢語言，數(shù)據(jù)庫執(zhí)行DQL語句不會對數(shù)據(jù)進(jìn)行改變，而是讓數(shù)據(jù)庫發(fā)送結(jié)果集給客戶端。SELECTselection_list/*要查詢的列名稱*/FROMtable_list/*要查詢的表名稱*/WHEREcondition/*行條件*/GROUPBYgrouping_columns/*對結(jié)果分組*/HAVINGcondition/*分組后的行條件*/ORDERBYsorting_columns/*對結(jié)果分組*/LIMIToffset_start,row_count/*結(jié)果限定*/基礎(chǔ)查詢查詢所有列SELECT*FROMstu;查詢指定列SELECTsid,sname,ageFROMstu;2條件查詢條件查詢介紹條件查詢就是在查詢時給出WHERE子句，在WHERE子句中可以使用如下運(yùn)算符及關(guān)鍵字：=、!=、<>、<、<=、>、>=；BETWEEN…AND；IN(set)；ISNULL；AND；OR；NOT；查詢性別為女，并且年齡50的記錄SELECT*FROMstuWHEREgender='female'ANDge<50;查詢學(xué)號為S_1001，或者姓名為liSi的記錄SELECT*FROMstuWHEREsid='S_1001'ORsname='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*FROMstuWHEREageISNULL;查詢年齡在20到40之間的學(xué)生記錄SELECT*FROMstuWHEREage>=20ANDage<=40;或者SELECT*FROMstuWHEREageBETWEEN20AND40;查詢性別非男的學(xué)生記錄SELECT*FROMstuWHEREgender!='male';或者SELECT*FROMstuWHEREgender<>'male';或者SELECT*FROMstuWHERENOTgender='male';查詢姓名不為null的學(xué)生記錄SELECT*FROMstuWHERENOTsnameISNULL;或者SELECT*FROMstuWHEREsnameISNOTNULL;3模糊查詢當(dāng)想查詢姓名中包含a字母的學(xué)生時就需要使用模糊查詢了。模糊查詢需要使用關(guān)鍵字LIKE。查詢姓名由5個字母構(gòu)成的學(xué)生記錄SELECT*FROMstuWHEREsnameLIKE'_____';模糊查詢必須使用LIKE關(guān)鍵字。其中“_”匹配任意一個字母，5個“_”表示5個任意字母。查詢姓名由5個字母構(gòu)成，并且第5個字母為“i”的學(xué)生記錄SELECT*FROMstuWHEREsnameLIKE'____i';查詢姓名以“z”開頭的學(xué)生記錄SELECT*FROMstuWHEREsnameLIKE'z%';其中“%”匹配0~n個任何字母。查詢姓名中第2個字母為“i”的學(xué)生記錄SELECT*FROMstuWHEREsnameLIKE'_i%';查詢姓名中包含“a”字母的學(xué)生記錄SELECT*FROMstuWHEREsnameLIKE'%a%';4字段控制查詢?nèi)コ貜?fù)記錄去除重復(fù)記錄（兩行或兩行以上記錄中系列的上的數(shù)據(jù)都相同），例如emp表中sal字段就存在相同的記錄。當(dāng)只查詢emp表的sal字段時，那么會出現(xiàn)重復(fù)記錄，那么想去除重復(fù)記錄，需要使用DISTINCT：SELECTDISTINCTsalFROMemp;查看雇員的月薪與傭金之和因?yàn)閟al和comm兩列的類型都是數(shù)值類型，所以可以做加運(yùn)算。如果sal或comm中有一個字段不是數(shù)值類型，那么會出錯。SELECT*,sal+commFROMemp;comm列有很多記錄的值為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)在我們給這一列給出一個別名，為total：SELECT*,sal+IFNULL(comm,0)AStotalFROMemp;給列起別名時，是可以省略AS關(guān)鍵字的：SELECT*,sal+IFNULL(comm,0)totalFROMemp;5排序查詢所有學(xué)生記錄，按年齡升序排序SELECT*FROMstuORDERBYsageASC;或者SELECT*FROMstuORDERBYsage;查詢所有學(xué)生記錄，按年齡降序排序SELECT*FROMstuORDERBYageDESC;查詢所有雇員，按月薪降序排序，如果月薪相同時，按編號升序排序SELECT*FROMempORDERBYsalDESC,empnoASC;6聚合函數(shù)聚合函數(shù)是用來做縱向運(yùn)算的函數(shù)：COUNT()：統(tǒng)計(jì)指定列不為NULL的記錄行數(shù)；MAX()：計(jì)算指定列的最大值，如果指定列是字符串類型，那么使用字符串排序運(yùn)算；MIN()：計(jì)算指定列的最小值，如果指定列是字符串類型，那么使用字符串排序運(yùn)算；SUM()：計(jì)算指定列的數(shù)值和，如果指定列類型不是數(shù)值類型，那么計(jì)算結(jié)果為0；AVG()：計(jì)算指定列的平均值，如果指定列類型不是數(shù)值類型，那么計(jì)算結(jié)果為0；COUNT當(dāng)需要縱向統(tǒng)計(jì)時可以使用COUNT()。查詢emp表中記錄數(shù)：SELECTCOUNT(*)AScntFROMemp;查詢emp表中有傭金的人數(shù)：SELECTCOUNT(comm)cntFROMemp;注意，因?yàn)閏ount()函數(shù)中給出的是comm列，那么只統(tǒng)計(jì)comm列非NULL的行數(shù)。查詢emp表中月薪大于2500的人數(shù)：SELECTCOUNT(*)FROMempWHEREsal>2500;統(tǒng)計(jì)月薪與傭金之和大于2500元的人數(shù)：SELECTCOUNT(*)AScntFROMempWHEREsal+IFNULL(comm,0)>2500;查詢有傭金的人數(shù)，以及有領(lǐng)導(dǎo)的人數(shù)：SELECTCOUNT(comm),COUNT(mgr)FROMemp;SUM和AVG當(dāng)需要縱向求和時使用sum()函數(shù)。查詢所有雇員月薪和：SELECTSUM(sal)FROMemp;查詢所有雇員月薪和，以及所有雇員傭金和：SELECTSUM(sal),SUM(comm)FROMemp;查詢所有雇員月薪+傭金和：SELECTSUM(sal+IFNULL(comm,0))FROMemp;統(tǒng)計(jì)所有員工平均工資：SELECTSUM(sal),COUNT(sal)FROMemp;或者SELECTAVG(sal)FROMemp;MAX和MIN查詢最高工資和最低工資：SELECTMAX(sal),MIN(sal)FROMemp;分組查詢當(dāng)需要分組查詢時需要使用GROUPBY子句，例如查詢每個部門的工資和，這說明要使用部分來分組。分組查詢查詢每個部門的部門編號和每個部門的工資和：SELECTdeptno,SUM(sal)FROMempGROUPBYdeptno;查詢每個部門的部門編號以及每個部門的人數(shù)：SELECTdeptno,COUNT(*)FROMempGROUPBYdeptno;查詢每個部門的部門編號以及每個部門工資大于1500的人數(shù)：SELECTdeptno,COUNT(*)FROMempWHEREsal>1500GROUPBYdeptno;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*FROMempLIMIT3,10;分頁查詢?nèi)绻豁撚涗洖?0條，希望查看第3頁記錄應(yīng)該怎么查呢第一頁記錄起始行為0，一共查詢10行；第二頁記錄起始行為10，一共查詢10行；第三頁記錄起始行為20，一共查詢10行；多表連接查詢連接查詢內(nèi)連接外連接左外連接右外連接全外連接（MySQL不支持）自然連接子查詢連接查詢連接查詢就是求出多個表的乘積，例如t1連接t2，那么查詢出的結(jié)果就是t1*t2。連接查詢會產(chǎn)生笛卡爾積，假設(shè)集合A={a,b}，集合B={0,1,2}，則兩個集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}?？梢詳U(kuò)展到多個集合的情況。那么多表查詢產(chǎn)生這樣的結(jié)果并不是我們想要的，那么怎么去除重復(fù)的，不想要的記錄呢，當(dāng)然是通過條件過濾。通常要查詢的多個表之間都存在關(guān)聯(lián)關(guān)系，那么就通過關(guān)聯(lián)關(guān)系去除笛卡爾積。內(nèi)連接上面的連接語句就是內(nèi)連接，但它不是SQL標(biāo)準(zhǔn)中的查詢方式，可以理解為方言！SQL標(biāo)準(zhǔn)的內(nèi)連接為：SELECT*FROMempeINNERJOINdeptdON=;內(nèi)連接的特點(diǎn)：查詢結(jié)果必須滿足條件。例如我們向emp表中插入一條記錄：其中deptno為50，而在dept表中只有10、20、30、40部門，那么上面的查詢結(jié)果中就不會出現(xiàn)“張三”這條記錄，因?yàn)樗荒軡M足=這個條件。外連接（左連接、右連接）外連接的特點(diǎn)：查詢出的結(jié)果存在不滿足條件的可能。左連接：SELECT*FROMempeLEFTOUTERJOINdeptdON=;左連接是先查詢出左表（即以左表為主），然后查詢右表，右表中滿足條件的顯示出來，不滿足條件的顯示NULL。子查詢子查詢就是嵌套查詢，即SELECT中包含SELECT，如果一條語句中存在兩個，或兩個以上SELECT，那么就是子查詢語句了。子查詢出現(xiàn)的位置：where后，作為條件的一部分；from后，作為被查詢的一條表；當(dāng)子查詢出現(xiàn)在where后作為條件時，還可以使用如下關(guān)鍵字：anyall子查詢結(jié)果集的形式：單行單列（用于條件）單行多列（用于條件）多行單列（用于條件）多行多列（用于表）練習(xí)：工資高于smith的員工。分析：查詢條件：工資>smith工資，其中smith工資需要一條子查詢。第一步：查詢smith的工資SELECTsalFROMempWHEREename='smith'第二步：查詢高于smith工資的員工SELECT*FROMempWHEREsal>(${第一步})結(jié)果：SELECT*FROMempWHEREsal>(SELECTsalFROMempWHEREename='smith')子查詢作為條件子查詢形式為單行單列工資高于30部門所有人的員工信息分析：查詢條件：工資高于30部門所有人工資，其中30部門所有人工資是子查詢。高于所有需要使用all關(guān)鍵字。第一步：查詢30部門所有人工資SE