SQL培训资料课件

SQL培训2013年3月第一页，共五十八页。内容简介♦SQL指令:SQL如何被用来储存、读取、以及处理数据库之中的资料。♦表格处理:SQL如何被用来处理数据库中的表格。♦进阶SQL:介绍SQL进阶概念，以及如何用SQL来执行一些较复杂的运算。

♦

SQL实例:介绍一些典型的实例，以及与大规划相关的语句。第二页，共五十八页。SELECTSELECT"栏位名"FROM"表格名";DISTINCT

找出表格内的不同资料值的情况SELECTDISTINCT"栏位名"FROM"表格名";例如：selectdistinctregion_namefromGEOGRAPHYt;WHERESELECT"栏位名"FROM"表格名"WHERE"条件";SQL指令第三页，共五十八页。AND/ORSELECT"栏位名"FROM"表格名"WHERE"简单条件"{[AND|OR]"简单条件"}IN/NOTINSELECT"栏位名"FROM"表格名"WHERE"栏位名"IN('值一','值二',...)例：SELECT*FROMStore_InformationWHEREstore_nameIN('LosAngeles','SanDiego')BETWEEN......AND.......SQL指令第四页，共五十八页。SELECT"栏位名"FROM"表格名"WHERE"栏位名"BETWEEN'值一'AND'值二'例：SELECT*FROMStore_InformationWHEREDatesBETWEEN'Jan-06-1999'AND'Jan-10-1999'LIKE字符筛选(注意：区分大小写)SELECT"栏位名"FROM"表格名"WHERE"栏位名"LIKE{模式}

例：SELECT*FROMStore_InformationWHEREstore_nameLIKE'%An%'SQL指令第五页，共五十八页。ORDERBY排序SELECT"栏位名"FROM"表格名"[WHERE"条件"]ORDERBY"栏位名"[ASC,DESC]ASC（默认）代表结果会以由小往大的顺序列出，而DESC代表结果会以由大往小的顺序列出例：SELECTstore_name,Sales,DatesFROMStore_InformationORDERBYSalesDESCGROUPBY分组SELECT"栏位1",SUM("栏位2")FROM"表格名"GROUPBY"栏位1"SQL指令第六页，共五十八页。SELECTstore_name,SUM(Sales)FROMStore_InformationGROUPBYstore_nameALIAS别名SELECT"表格别名"."栏位1""栏位别名"FROM"表格名""表格别名"selectt.store_name,sum(sales)合计fromSTORE_INFORMATIONtgroupbystore_name;函数AVG(平均)、COUNT(计数)、MAX(最大值)、MIN(最小值)、SUM(总合)SQL指令第七页，共五十八页。SELECT"函数名"("栏位名")FROM"表格名"selectcount(store_name)计数fromSTORE_INFORMATIONt;HAVING对函数产生的值来设定条件SELECT"栏位1",SUM("栏位2")FROM"表格名"GROUPBY"栏位1"HAVING(函数条件)SELECTstore_name,SUM(sales)FROMStore_InformationGROUPBYstore_nameHAVINGSUM(sales)>1500表格链接

左连接(leftjoin)，又称内部连接(innerjoin)，在这个情况下，要两个表格内都有同样的值，那一笔资料才会被选出。SQL指令第八页，共五十八页。selectg.*,s.*fromgeographyg,store_informationswhereg.store_name=s.store_name笛卡儿连接selectg.*,s.*fromgeographyg,store_informations外部连接(outerjion)，列出一个表格中每一笔的资料，无论它的值在另一个表格中有没有出现。在要选出所有资料的那个表格之后加上一个"(+)"selectg.store_name,sum(s.sales)salesfromgeographyg,store_informationswhereg.store_name=s.store_name(+)groupbyg.store_name;请注意：当第二个表格没有相对的资料时，SQL会传回NULL值。SQL指令第九页，共五十八页。CONCATENATE连接字符串有的时候，我们有需要将由不同栏位获得的资料串连在一起。每一种数据库都有提供方法来达到这个目的MySQL:CONCAT()Oracle:CONCAT(),||SQLServer:+CONCAT()的语法如下：CONCAT(字符串1,字符串2,字符串3,...)注意:Oracle的CONCAT()只允许两个参数；换言之，一次只能将两个字符串串连起来。不过，在Oracle中，我们可以用'||'来一次串连多个字符串。例：selectg.region_name||''||g.store_namefromgeographyg;SQL指令第十页，共五十八页。SUBSTRING抓出一个栏位资料中的其中一部分MySQL:SUBSTR(),SUBSTRING()Oracle:SUBSTR()SQLServer:SUBSTRING()最常用到的方式如下(在这里我们用SUBSTR()为例)：SUBSTR(str,pos):由<str>中，选出所有从第<pos>位置开始的字符。请注意，这个语法不适用于SQLServer上。SUBSTR(str,pos,len):由<str>中的第<pos>位置开始，选出接下去的<len>个字符。selectsubstr(store_name,3,6)fromgeographywherestore_name='LosAngeles';SQL指令第十一页，共五十八页。TRIM移除SQL中的TRIM函数是用来移除掉一个字符串中的字头或字尾。最常见的用途是移除字首或字尾的空白。这个函数在不同的数据库中有不同的名称：MySQL:TRIM(),RTRIM(),LTRIM()Oracle:RTRIM(),LTRIM()SQLServer:RTRIM(),LTRIM()各种trim函数的语法如下：TRIM([[位置][要移除的字符串]FROM]字符串)[位置]的可能值为LEADING(起头),TRAILING(结尾),or (起头及结尾)。这个函数将把[要移除的字符串]从字符串的起头、结尾，或是起头及结尾移除。如果我们没有列出[要移除的字符串]是什么的话，那空白就会被移除。SQL指令第十二页，共五十八页。LTRIM(字符串):将所有字符串起头的空白移除。RTRIM(字符串):将所有字符串结尾的空白移除。selectrtrim(store_name)fromgeographyg;--删除头和尾空白selecttrim(store_name)fromgeographyg;--删除头和尾字母“o”selecttrim(BOTH'o'fromstore_name)fromgeographyg;在起头处添加字段：lpad(tab_month,2,‘0’)月份格式改为“MM”2位

selectlpad('7',2,'0')fromdualSQL指令第十三页，共五十八页。内容简介♦SQL指令:SQL如何被用来储存、读取、以及处理数据库之中的资料。♦表格处理:SQL如何被用来处理数据库中的表格。♦进阶SQL:介绍SQL进阶概念，以及如何用SQL来执行一些较复杂的运算。

♦

SQL实例:介绍一些典型的实例。第十四页，共五十八页。CREATETABLE建表语法是：CREATETABLE"表格名"("栏位1""栏位1资料种类"NOTNULL,"栏位2""栏位2资料种类",...)--CreatetablecreatetableGEOGRAPHY(region_nameVARCHAR2(20),store_nameVARCHAR2(20)NOTNULL)表格处理第十五页，共五十八页。CREATETABLE建表语法是：CREATETABLE"表格名"("栏位1""栏位1资料种类"NOTNULL,"栏位2""栏位2资料种类",...)注意：字段类型char和varchar2区别CHAR的长度是固定的，而VARCHAR2的长度是可以变化的。比如，存储字符串“abc”，对于CHAR(20)，表示你存储的字符将占20个字节(包括17个空字符)，而同样的VARCHAR2(20)则只占用3个字节的长度，20只是最大值，当你存储的字符小于20时，按实际长度存储。表格处理第十六页，共五十八页。表格处理添加注释--AddcommentstothetablecommentontableGEOGRAPHYis'商店所属地理位置';--AddcommentstothecolumnscommentoncolumnGEOGRAPHY.region_nameis'地区名称';commentoncolumnGEOGRAPHY.store_nameis'商店名称';CONSTRAINT约束限制NOTNULLUNIQUECHECK主键(PrimaryKey)外来键(ForeignKey)第十七页，共五十八页。表格处理UNIQUE唯一性UNIQUE限制是保证一个栏位中的所有资料都是有不一样的值。举例来说，在以下的语句中，CREATETABLECustomer(SIDintegerUnique,Last_Namevarchar(30),First_Namevarchar(30));"SID"栏位不能有重复值存在，而"Last_Name"及"First_Name"这两个栏位则是允许有重复值存在。请注意，一个被指定为主键的栏位也一定会含有UNIQUE的特性。相对来说，一个UNIQUE的栏位并不一定会是一个主键。第十八页，共五十八页。表格处理CHECK核对CHECK限制是保证一个栏位中的所有资料都是符合某些条件。举例来说，在以下的语句中，CREATETABLECustomer(SIDintegerCHECK(SID>0),Last_Namevarchar(30),First_Namevarchar(30));"SID"拦只能包含大于0的整数。注意，CHECK限制目前尚未被执行于MySQL数据库上。第十九页，共五十八页。表格处理主键主键(PrimaryKey)中的每一笔资料都是表格中的唯一值。换言之，它是用来独一无二地确认一个表格中的每一行资料。主键可以是原本资料内的一个栏位，或是一个人造栏位(与原本资料没有关系的栏位)。主键可以包含一或多个栏位。当主键包含多个栏位时，称为组合键(CompositeKey)。注意，在用ALTERTABLE语句来添加主键之前，我们需要确认被用来当做主键的栏位是设定为『NOTNULL』；也就是说，那个栏位一定不能没有资料。Oracle:CREATETABLECustomer(SIDintegerPRIMARYKEY,Last_Namevarchar(30),First_Namevarchar(30));Oracle:ALTERTABLECustomerADDPRIMARYKEY(SID);第二十页，共五十八页。表格处理外来键外来键是一个(或数个)指向另外一个表格主键的栏位。外来键的目的是确定资料的参考完整性(referentialintegrity)。换言之，只有被准许的资料值才会被存入数据库内。举例来说，假设我们有两个表格：一个CUSTOMER表格，里面记录了所有顾客的资料；另一个ORDERS表格，里面记录了所有顾客订购的资料。在这里的一个限制，就是所有的订购资料中的顾客，都一定是要跟在CUSTOMER表格中存在。在这里，我们就会在ORDERS表格中设定一个外来键，而这个外来键是指向CUSTOMER表格中的主键。这样一来，我们就可以确定所有在ORDERS表格中的顾客都存在CUSTOMER表格中。换句话说，ORDERS表格之中，不能有任何顾客是不存在于CUSTOMER表格中的资料。第二十一页，共五十八页。表格处理

第二十二页，共五十八页。表格处理在以上的例子中，ORDERS表格中的customer_SID栏位是一个指向CUSTOMERS表格中SID栏位的外来键。第二十三页，共五十八页。表格处理CREATEVIEW视图视观表(Views)可以被当作是虚拟表格。它跟表格的不同是，表格中有实际储存资料，而视观表是建立在表格之上的一个架构，它本身并不实际储存资料。建立一个视观表的语法如下：CREATEVIEW"VIEW_NAME"AS"SQL语句"createviewv_geographyasselectregion_name,trim(store_name)v_store_namefromgeographyg;-第二十四页，共五十八页。表格处理CREATEVIEW视图视观表(Views)可以被当作是虚拟表格。它跟表格的不同是，表格中有实际储存资料，而视观表是建立在表格之上的一个架构，它本身并不实际储存资料。建立一个视观表的语法如下：CREATEVIEW"VIEW_NAME"AS"SQL语句"createviewv_geographyasselectregion_name,trim(store_name)v_store_namefromgeographyg;--删除视图dropviewv_geography;第二十五页，共五十八页。表格处理CREATEINDEX 索引如果一个表格没有索引的话，数据库系统就需要将整个表格的资料读出(这个过程叫做'tablescan')。若有适当的索引存在，数据库系统就可以先由这个索引去找出需要的资料是在表格的什么地方，然后直接去那些地方抓资料。这样子速度就快多了。语法：CREATEINDEX"INDEX_NAME"ON"TABLE_NAME"(COLUMN_NAME)createindexidx_store_info_store_nameonstore_information(store_name,)索引的命名并没有一个固定的方式。通常会用的方式是在名称前加一个字首，例如“IDX_”，来避免与数据库中的其他物件混淆。另外，在索引名之内包括表格名及栏位名也是一个好的方式。(删除，修改索引)第二十六页，共五十八页。表格处理ALTERTABLE修改表结构ALTERTABLE语句的语法形式：ALTERTABLEtable{[

MODIFYcolumn_name{new_data_type[(precision[,scale])][NULL|NOTNULL]}]|ADD{[<add_column_nameadd_data_type>]}[,...n]|DROPCOLUMN{drop_colum_name}[,...n]}在以上语法形式中：column_name：要修改的列名。new_data_type：要修改列的新数据类型。precision：是指定数据类型的精度。第二十七页，共五十八页。表格处理scale：是指定数据类型的小数位数。add_column_name：要添加到表中的列名。add_data_type：要添加到表中的列的数据类型。drop_colum_name：要从表中删除的列名[,...n]：可以有多个列。加一个栏位:ADD"栏位1""栏位1资料种类"altertablecustomeraddaddresschar(30);删去一个栏位:DROPcolumn"栏位1"altertablecustomerdropcolumnaddress;改变栏位的资料种类:MODIFY"栏位1""新资料种类"altertablecustomermodifyaddrschar(50);修改栏位名RENAME……TO……

altertablecustomerrenamecolumnaddrtoaddrs;第二十八页，共五十八页。表格处理DROPTABLE

清除表格DROPTABLE"表格名"droptablecustomer;TRUNCATETABLE清除表格中所有资料TRUNCATETABLE"表格名"truncatetablecustomer;DELETEFROM删除表数据DELETEFROM“表格名”

WHERE{条件}deletefromstore_informationtwherestore_name='LosAngeles';第二十九页，共五十八页。表格处理INSERTINTO插入INSERTINTO"表格名"("栏位1","栏位2",...)VALUES("值1","值2",...)insertintostore_information(store_name,sales,dates)values('losangeles',900,'jan-10-1999')INSERTINTO"表格1"("栏位1","栏位2",...)SELECT"栏位3","栏位4",…FROM"表格2"insertintostore_information(store_name,sales,dates)selectstore_name,sales,datesfromv_store_informationwheredateslike'%1998'第三十页，共五十八页。表格处理UPDATE修改表格资料

UPDATE"表格名“

SET"栏位1"=[新值]

WHERE{条件}updatestore_informationsetsales=800wherestore_name='losangeles';第三十一页，共五十八页。内容简介♦SQL指令:SQL如何被用来储存、读取、以及处理数据库之中的资料。♦表格处理:SQL如何被用来处理数据库中的表格。♦进阶SQL:介绍SQL进阶概念，以及如何用SQL来执行一些较复杂的运算。

♦

SQL实例:介绍一些典型的实例。第三十二页，共五十八页。UNION

求合集将两个

SQL语句的结果合并在一起。[SQL语句1]UNION[SQL语句2]selectdatesfromstore_informationunionselectdatesfrominternet_salesUNIONALLUNIONALL和

UNION不同之处在于

UNIONALL会将每一笔符合条件的资料都列出来，无论资料值有无重复。

SQL进阶第三十三页，共五十八页。INTERSECT交集

和

UNION指令类似，

INTERSECT也是对两个

SQL语句所产生的结果做处理的。不同的地方是，UNION基本上是一个OR(如果这个值存在于第一句或是第二句，它就会被选出)，而INTERSECT则比较像AND(这个值要存在于第一句和第二句才会被选出)。UNION是合集，而INTERSECT是交集。

INTERSECT的语法如下：

[SQL语句1]INTERSECT[SQL语句2]SQL进阶第三十四页，共五十八页。MINUS

MINUS指令是运用在两个SQL语句上。它先找出第一个SQL语句所产生的结果，然后看这些结果有没有在第二个SQL语句的结果中。如果有的话，那这一笔资料就被去除，而不会在最后的结果中出现。如果第二个SQL语句所产生的结果并没有存在于第一个SQL语句所产生的结果内，那这笔资料就被抛弃。

MINUS的语法如下：[SQL语句1]MINUS[SQL语句2]注意：在MINUS指令下，不同的值只会被列出一次。SQL进阶第三十五页，共五十八页。子查询我们可以在一个SQL语句中放入另一个SQL语句。当我们在WHERE子句或HAVING子句中插入另一个SQL语句时，我们就有一个子查询(subquery)的架构。子查询的作用是什么呢？它可以被用来连接表格。有的时候子查询是唯一能够连接两个表格的方式。

子查询的语法如下：

SELECT“栏位1”

FROM"表格"

WHERE"栏位2"[比较运算素](SELECT“栏位1”

FROM“表格”

WHERE[条件])selectsum(sales)fromstore_information

twherestore_namein(selectstore_namefromgeographywhereregion_name='East');SQL进阶第三十六页，共五十八页。在这个例子中，我们并没有直接将两个表格连接起来，然后由此直接算出每一间东区店面的营业额。我们做的是先找出哪些店是在东区的，然后再算出这些店的营业额总共是多少。

在以上的例子，内部查询本身与外部查询没有关系。这一类的子查询称为『简单子查询』(SimpleSubquery)。如果内部查询是要利用到外部查询提到的表格中的栏位，那这个字查询就被称为『相关子查询』(CorrelatedSubquery)。以下是一个相关子查询的例子：

selectsum(i.sales)fromstore_informationiwherei.store_namein(selectstore_namefromgeographygwhereg.store_name=i.store_name)SQL进阶第三十七页，共五十八页。EXISTS

在上一页中，我们用IN来连接内查询和外查询。另外有数个方式，例如>，<，及=，都可以用来连接内查询和外查询。EXISTS也是其中一种方式。这一页我们将讨论EXISTS的用法。

基本上，EXISTS是用来测试内查询有没有产生任何结果。如果有的话，系统就会执行外查询中的SQL。若是没有的话，那整个SQL语句就不会产生任何结果。

EXISTS的语法是：

SELECT“栏位1”

FROM“表格1”WHEREEXISTS(SELECT*FROM“表格2”WHERE[条件])SQL进阶第三十八页，共五十八页。CASE

SQL用来做为if-then-else之类逻辑的关键字。CASE的语法如下：

SELECTCASE("栏位名")WHEN"条件1"THEN"结果1"WHEN"条件2"THEN"结果2"...[ELSE"结果N"]ENDFROM"表格名"

“条件”可以是一个数值或是公式。ELSE子句则并不是必须的。SQL进阶第三十九页，共五十八页。selectstore_name,casestore_namewhen'losangeles'thensales*2when'SanDiego'thensales*1.5elsesalesend"newsales",datesfromstore_information“NewSales”是用到CASE那个栏位的栏位别名。算排名要以SQL列出排名，基本的概念是要做一个表格自我连结(selfjoin)，SQL进阶第四十页，共五十八页。也是一个笛卡尔连接，将结果依序列出，然后算出每一行之前(包含那一行本身)有多少行数,a1.sales,count(a2.sales)sales_rankfromtotal_salesa1,total_salesa2wherea1.sales<a2.sales

or(a1.sales==),a1.salesorderbya1.salesdesc,desc;对比：selectrow_number()over(orderbyt.salesdesc)m,t.*

fromtotal_salest;SQL进阶第四十一页，共五十八页。算中位数selectsalesmedianfrom(,a1.sales,count(a1.sales)rankfromtotal_salesa1,total_salesa2wherea1.sales<a2.sales

or(a1.sales=<=),a1.salesorderbya1.salesdesc)a3whererank=(selectceil((count(*)+1)/2)fromtotal_sales);SQL进阶第四十二页，共五十八页。算累积总计要以SQL算出累积总计，基本上的概念与列出排名类似：第一是先做个表格自我连结(selfjoin)，然后将结果依序列出。在做列出排名时，我们算出每一行之前(包含那一行本身)有多少行数；而在做累积总计

时，我们则是算出每一行之前(包含那一行本身)的总合。,a1.sales,sum(a2.sales)running_totalfromtotal_salesa1,total_salesa2wherea1.sales<=a2.sales

/*or(a1.sales==)*/,a1.salesorderbya1.salesdesc,desc;SQL进阶第四十三页，共五十八页。算总合百分比每一笔资料是所有总合的百分之几？要用SQL算出总合百分比，我们需要用到算排名和累积总计的概念，以及运用子查询的做法。在这里，我们把子查询放在外部查询的SELECT子句中。,a1.sales,

a1.sales/(selectsum(sales)fromtotal_sales)pct_to_totalfromtotal_salesa1,total_salesa2wherea1.sales<=a2.sales

/*or(a1.sales==)*/,a1.salesorderbya1.salesdesc,desc;SQL进阶第四十四页，共五十八页。算累积总合百分比我们要算出到目前为止的累积总合是所有总合的百分之几，而不是光看每一笔资料是所有总合的百分之几。,a1.sales,

sum(a2.sales)/(selectsum(sales)fromtotal_sales)pct_to_totalfromtotal_salesa1,total_salesa2wherea1.sales<=a2.sales

/*or(a1.sales==)*/,a1.salesorderbya1.salesdesc,desc;SQL进阶第四十五页，共五十八页。数据处理的相关函数ceil(5/2)=3--向上取整floor(5/2)=2--向下取整round(7/3,2)=2.33--四舍五入法则mod(5,2)=1--取余TRUNCfordates(日期)TRUNC（date,[fmt]）trunc(sysdate,'yyyy')--返回当年第一天.trunc(sysdate,'mm')--返回当月第一天.trunc(sysdate,'d')--返回当前星期的第一天.TRUNCfornumber(数字)TRUNC（number[,decimals]）trunc(89.985,2)=89.98trunc(89.985)=89(即取整)trunc(89.985,-1)=80小数点左边指定位数后面的部分截去，以0记。SQL进阶第四十六页，共五十八页。查询指定行数据--注意：伪例rownum不能使用“>”--查询表的前十行数据select*fromtablenamewhererownum<=10;--查询11-20行记录--方法一select*from(selectrownumm,t.*fromreport_infotwhererownum<=20)wherem>=11;--方法二select*fromtablenamewhererownum<=20minusselect*fromtablenamewhererownum<11;--方法三select*from(selectrow_number()over(orderbyt.salesdesc)m,t.*fromtotal_salest)wherembetween2and5;SQL进阶第四十七页，共五十八页。对比学习selectrownum,t.*fromtotal_salest;--顺序排序selectrow_number()over(orderbyt.salesdesc)m,t.*fromtotal_salest;--跳跃排序selectrank()over(orderbyt.salesdesc)m,t.*fromtotal_salest;--连续排序selectdense_rank()over(orderbyt.salesdesc)m,t.*fromtotal_salest;SQL进阶第四十八页，共五十八页。内容简介♦SQL指令:SQL如何被用来储存、读取、以及处理数据库之中的资料。♦表格处理:SQL如何被用来处理数据库中的表格。♦进阶SQL:介绍SQL进阶概念，以及如何用SQL来执行一些较复杂的运算。

♦

SQL实例:介绍一些典型的实例，以及与大规划相关的语句。第四十九页，共五十八页。--设置月报报表参考期别selectdistincttab_year年份,tab_month月份from(selecttab_year,tab_monthfromDXP_408703unionselectto_number(to_char(add_months(sysdate,-1),'yyyy'))tab_year,

to_number(to_char(add_months(sysdate,-1),'mm'))tab_monthfromdual

)orderbytab_yeardesc,tab_monthdesc

SQL实例第五十页，共五十八页。--设置季报报表参考期别selectdistincttab_year年份,tab_month季度from(selectdistincttab_year,tab_month

fromJNJP_PRICE_EXPONENT_SGCCunionselectto_number(to_char(sysdate,'yyyy'))tab_year,

trunc(to_number(to_char(sysdate,'mm'))/3)tab_month

fromdual)

orderbytab_yeardesc,tab_monthdesc;SQL实例第五十一页，共五十八页。t