网络运行分析-数据库基础sql语言操作

SQL指中，我们马上可以看到两个关键字：从(FROM)数据库中的表格内选出(SELECT)。(表使用SQL来设定表格。)我们由这里可以看到最基本的SQL架构：SELECT栏位名FROM表格名LosSanLos(store_Name)，我们就打入：SELECTstore_nameFROMStore_Information结果LosSanLosSQL查询只包括选择列表、FROM子句和WHERE子句。它们分别说明所查询列、testtable表中所有列的数据：代码:SELECT*FROMtesttable代码:SELECTnickname,FROM代码:SELECT昵称=nickname,电子邮件=FROMSELECTALL或DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行，默认为ALLDISTINCTSELECT返回TOPn是表示一百分数，指定返回的行数等于总行数的百分之几。例如：代码:SELECTTOP2*FROM`testtable`代码:SELECTTOP20PERCENT*FROMSELECT指令让我们能够表格中一个或数个栏位的所有资料。这将把所有的资料都抓SQLSELECT后加上一个DISTINCT就可以了。DISTINCT的语法如下：SELECTDISTINCT栏位名FROM表格名Store_InformationLosSanLosSELECTDISTINCTstore_nameFROM结果LosAngelesSanDiego$1,000的资料。要做到这一点，我们就需要用到WHERE这个指令。这个指令的语法如下：SELECT栏位名FROM表格名"WHERE"条件"961月的定单SELECTorderID,CustomerID,orderDateFROMordersMcirosoftJETSQL中，日期用‘#’Datevalue()函数来代替。在比较字符WHEREorderDate〉#96-1-WHEREorderDate〉$1,000Store_InformationLosSanLosSELECTstore_nameFROMStore_InformationWHERESales>1000结果LosAngelesAND在上一页中，我们看到WHERE指令可以被用来由表格中有条件地选取资料。这个条件可能是简单的(像上一页的例子)，也可能是复杂的。复杂条件是由二或多个简单条件透过AND或是OR的连接而成。一个SQL语句中可以有无限多个简单条件的存在。SELECT栏位名FROM"表格名"WHERE简单条件"{[AND|OR简单条件{}+代表{}ANDOR加简单条件的情况可以发生一或多次。另外，我们可以用()来代表条件的先后次序。举例来说，我们若要在Store_Information表格中选出所有Sales高于$1,000或是$500$275Store_InformationLosSanSanSELECTstore_nameFROMStore_InformationWHERESales>1000OR(Sales<500ANDSales>结果LosAngelesSanFrancisco在SQL中，在两个情况下会用到IN这个指令；这一页将介绍其中之一：与WHERE有道的值都放入IN这个子句。IN指令的语法为下：SELECT栏位名FROM表格名WHERE栏位名IN值一值二WHERE栏位名值一Store_InformationLosAngelesSanStore_InformationLosSanSanSELECTFROMWHEREstore_nameIN('LosAngeles','San结果LosSanIN这个指令可以让我们依照一或数个不连续(discrete)的值的限制之内抓出资料库中的BETWEEN(range)BETWEENSELECT栏位名FROM表格名WHERE栏位名BETWEEN值一AND值二Store_InformationJanuary6,1999January10,1999中的资料，Store_InformationLosSanSanSELECTFROMWHEREDateBETWEEN'Jan-06-1999'AND'Jan-10-结果SanSanLIKEWHERELIKE能让我们依据一个模式(pattern)IN的时候，我们完全地知道我们需要的条件；在运用BETWEEN的时候，我们则是列出一个范围。LIKE的语法如下：SELECT栏位名FROM表格名WHERE栏位名LIKE模式LIKE运算符里使用的通配符通配符含义*0~9[字符列表][！字符列表]-{模式}(wildcard).'A_Z':'A''Z''ABZ''A2Z'都符合这一个模式，而'AKKZ'并不符合(因为在A和Z之间有两个字原，而不是一个'ABC%':'ABC''ABCD''ABCABC''%XYZ':'XYZ'结尾的字符串。举例来说，'WXYZ''ZZXYZ''%AN%':所有含有'AN'这个模式的字符串。举例来说，'LOSANGELES'和'SANFRANCISCO'都符合这个模式。Store_Information表格上Store_InformationLOSSANSANSELECTFROMWHEREstore_nameLIKE结果LOSSANSANODERSELECTWHERE这两个指令将资料由表格中抓出。(ASCENDing)或是由大往小(DESCENDing)ODERBYODERBY的语法如下SELECT栏位名FROM"表格名"[WHERE条件ODERBY栏位名ASC[]WHEREWHEREODERBY子句之前。ASC代表结果会以由小往大的顺序列出，而DESC代表结果会以由大往小的顺序列出。如果两者皆没有被写出的话，那我们就会用ASC。ODERBY子句的语法如下(假设ODERBY栏位一ASCDESC栏位二ASC"栏位一"由小"栏位一""栏位二"SalesStore_InformationStore_InformationLosSanSanSELECTstore_name,Sales,DateFROMStore_InformationODERBYSales结果LosSanSan(SQL句中的顺序)SELECT12，以此类推。在上面这个例子中，我们用以下这句SQL可以达到完全一样的效果：SELECTstore_name,Sales,DateFROMStore_InformationODERBY2做一些运算，例如将它们总合起来，或是找出它们的平均值。SQL有提供一些这一类的函AVG(平均)COUNT计数MAX最大值)MIN(最小值)SUM(总合)VAR(方差)STDEV(标准误差)FIRST(第一个值)LAST(最后一个值SELECT函数名"("栏位名")FROM"表格名"SalesStore_InformationLosSanLosSELECTSUM(Sales)FROM结果$2750Sales栏位的总合$1500$250$300SQL(concatenation)，文字修整(trim)，以及子字符串(substring)。不同的数据库对这些函数有不同的语法，所以最好是参考您所用数基本上，COUNT让我们能够数出在表格中有多少笔资料被选出来。它的语法是：FROM表格名store_nameStore_InformationLosSanLosSELECTCOUNT(store_name)FROMStore_InformationWHEREstore_nameisnotNULL结果"isnotNULL""这个栏位不是空白"COUNT和DISTINCT经常被合起来使用，目的是找出表格中有多少笔不同的资料(至于的store_name，我们就键入，SELECTCOUNT(DISTINCTFROM结果Count(DISTINCTstore_name)GROUP我们现在回到函数上。记得我们用SUM这个指令来算出所有的Sales(营业额)吧！如果我们的需求变成是要算出每一间店(store_name)的营业额(sales)，那怎么办呢？在这个情况我们需要确认所有的sales都要依照各个store_name来分开算。这个语法为：SELECT1SUM("FROM"表格名"GROUPBY1"Store_InformationLosSanLosSELECTstore_name,SUM(Sales)FROMStore_InformationGROUPBYstore_name结果LosSan当我们选不只一个栏位，且其中至少一个栏位有包含函数的运用时，我们就需要用到GROUPBYGROUPBY所有其他的栏位。换句话说，除了有包括函数的栏位外，我们都需要将其放在GROUPBY的子句中。$1,500WHERESQL有提供一个HAVING的指令，而我们就可以用这个指令来达到这个目标。HAVING子句通常是在一个SQL句子的最后。一个含有HAVING子句的SQL并不一定要包含GROUPBY子句。HAVING的语法如下：SELECT1SUM("FROM"表格名"(SELECTGROUPBY子句。在我们Store_Information表格这个例子中,Store_InformationLosSanLosSales$1,500store_nameSELECTstore_name,SUM(sales)FROMStore_InformationGROUPBYstore_nameHAVINGSUM(sales)>1500结果 LosAngeles alias别名)SQL上的用处。最常用到的别名有两种：栏位别名及表SQL产生的结果易读。在之前的例子中，每当我们有营业额总合时，栏位名都是SUM(sales)。虽然在这个情况下没有什么问题，可是如果这FROM子句中的表格名后空一SQL由数个不同的表格中获取资料时是很方便的。这一点我们在之后谈到连接(join)时会看到。SELECT表格别名"."1"栏位别名"FROM"表格名""表格别名"SELECT表格名AS表格别名续使用Store_Information这个表格来做例子：Store_InformationLosSanLosSQLGROUPBY那一页一样的例子。这里的不同处是我们加上了栏位别名以及SELECTA1.store_nameStore,SUM(A1.Sales)"TotalSales"FROMStore_InformationA1结果LosSan"Sum(Sales)""TotalSales"。很明"TotalSales""Sum(Sales)"更精确地阐述这个栏位的含意。用表格别名的好处在这里并没有显现出来，不过这在下一页就会很清楚了。Store_InformationLosSanLosGeographyNewLosSan(region_name)(sales)Geography这个表格告诉我们每一区有哪些店，而Store_Information告诉我们每一个店的营业额。若我们要知道每一区的营会发现它们可经由一个相同的栏位，store_nameSQL句列出，之后SELECTA1.region_nameREGION,SUM(A2.Sales)SALESFROMGeographyA1,Store_InformationA2WHEREA1.store_name=A2.store_nameGROUPBYA1.region_name结果 在第一行中，我们告诉SQL去选出两个栏位：第一个栏位是Geography表格中的region_name(REGION)Store_Information表sales(SALES)。请注意在这里我们有用到表格别名：Geography表格的别名是A1，Store_Information表格的别名是A2。若我们没有用表格别名的话，第一行SQL句容易被了解，尤其是这个SQL句含盖好几个不同的表格时。接下来我们看第三行，就是WHERE子句。这是我们阐述连接条件的地方。在这里，我们要确认Geography表格中store_name栏位的值与Store_Information表格中store_name栏位的值是相等的。这个WHERE子句是接的人物，因为它的角色是确定两个表格之间的连接是正确的。如果WHERE子句是错误的，我们就极可能得到一个笛卡儿连(Cartesianjoin)。笛卡儿连接会造成我们得到所有两个表格每两行之间所有可能的组合。在这个例子中，笛卡儿连接会让我们得到4x4=16行的结果。之前我们看到的左连接(leftjoin)，又称内部连接(innerjoin)。在这个情况下，要两个表格SQLOUTERJOIN(外部连接)的指令。外部连接的语法是依数据库的不同而有所不同的。举例来说，在Oracle上，我们会在WHERE子句中要选出所有资料的那个表格之后加上一个"(+)"来代表说这个表格中的所Store_InformationLosSanLosGeographyNewLosSan，我们需要知道每一间店的营业额。如果我们用一个普通的连接会漏失掉'NewYork'这个店，因为它并不存在于Store_Information这个表格。所以，在这个情况下，我们需要，SELECTA1.store_name,SUM(A2.Sales)SALESFROMGeorgraphyA1,Store_InformationA2WHEREA1.store_name=A2.store_name(+)Oracle结果NewLosSan请注意：当第二个表格没有相对的资料时，SQLNULL'NewYork'并不存在于Store_Information表格，所以它的"SALES"栏位是NULL.MySQL:Oracle:CONCAT(),||SQLServer:+()CONCAT(1,2,3,123，等字符串连连起来。不过，在Oracle中，我们可以用'||'来一次串连多个字符串。GeographyNewLosSanSELECTCONCAT(region_name,store_name)FROMGeographyWHEREstore_name='Boston';2:SELECTregion_name||''||store_nameFROMGeographyWHEREstore_name='Boston';'EastBoston'3:SQLSELECTregion_name+''+store_nameFROMGeographyWHEREstore_name='Boston';'EastSQLsubstringMySQL:SUBSTR(),Oracle:SQLServer:(在这里我们用SUBSTR()为例SUBSTR(str,pos):由<str>中，选出所有从第<pos>位置开始的字符。请注意，这个语法不适SQLServer上。SUBSTR(str,pos,len):由<str>中的第<pos>位置开始，选出接下去的<len>个字符。GeographyNewLosSanSELECTSUBSTR(store_name,3)FROMGeographyWHEREstore_name='Los结果'sSELECTFROMWHEREstore_name='San结果'an进阶在这一部分，介绍以下的SQL概念及关键字SQLUNIONSQLUNIONALLSQLINTERSECTSQLMINUSSQLSubquerySQLEXISTSSQLCASESQL(Rank)(Running(Percentto(CumulativePercenttoUNION指令的目的是将两个SQL语句的结果合并起来。从这个角度来看，UNION跟JOINUNION的一个限制是两个SQL语句所产生的栏位需要是同样的资料种类。另外，当我们用UNION这个指令时，我们只会看到不同的资料值(类似SELECTDISTINCT)。UNION[SQL1][SQLStore_InformationLosSanLosInternet_Sales)SELECTDateFROMStore_InformationSELECTDateFROM结果有一点值得注意的是，如果我们在任何一个SQL语句(或是两句都一起)用"SELECTDISTINCTDate"的话，那我们会得到完全一样的结果。UNIONUNIONALL这个指令的目的也是要将两个SQL语句的结果合并在一起。UNIONALL和UNIONUNIONALLUNIONALL[SQL1]UNIONALL[SQL2]UNIONALLUNION的不同。同样假设我们有以Store_InformationLosSanLosInternet_SalesSELECTDateFROMStore_InformationUNIONALLSELECTDateFROM和UNION指令类似，INTERSECTSQL语句所产生的结果做处理的。不同UNIONOR如果这个值存在于第一句或是第二句，它就会被选出)，而INTERSECT则比较像AND(这个值要存在于第一句和第二句才会被选出)。UNION是联集，而INTERSECT是交集。INTERSECT[SQL1][SQL2]Store_InformationLosSanLosInternet_SalesSQLSELECTDateFROMStore_InformationSELECTDateFROM结果INTERSECTMINUS指令是运用在两个SQL语句上。它先找出第一个SQL语句所产生的结果，然后SQL语句的结果中。如果有的话，那这一笔资料就被去除，而不会在最后的结果中出现。如果第二个SQL语句所产生的结果并没有存在于第一个SQLMINUS[SQL1][SQLStore_InformationLosSanLosInternet_Sales的SQL语句：SELECTDateFROMStore_InformationSELECTDateFROM结果 Store_Information"所产生的结果。在这里面，"Jan-07-1999"是存在于"SELECTDateFROMInternet_Sales"所产生的结果中。因此"Jan-07-1999"并不在最后的结果中。MINUSSQLSQLWHEREHAVINGSQL(subquery)SELECT1"FROM"表格"WHERE2比较运算素](SELECT"1"FROM"表格"WHERE条件])[比较运算素]这也可以是一个对文字的运算素，例如"LIKE"。绿色的部分代表外查询，红色的部分代表内查询。SQLStore_InformationLosSanLosGeographyNewLosSansubquerySQL来达到我SELECTSUM(Sales)FROMStore_InformationWHEREStore_nameIN(SELECTstore_nameFROMGeographyWHEREregion_name='West')结果(SimpleSubquery)。如果内部查询是要利用到外部查询提到的表格中的栏位，那这个字查询就被称为『相关子查询』(CorrelatedSubquery)。以下是一个相关子查询的例子：SELECTSUM(a1.Sales)FROMStore_Informationa1WHEREa1.Store_nameIN(SELECTstore_nameFROMGeographya2WHEREa2.store_name=a1.store_name)IN><=，都可以用来连接内查询和外查询。EXISTS也是其中式这一页讨论EXISTS基本上，EXISTS是用来测试内查询有没有产生任何结果。如果有的话，系统就会执行外查询中的SQL。若是没有的话，那整个SQL语句就不会产生任何结果。EXISTSSELECTFROM"1"WHEREEXISTS(SELECT*FROM"2"WHERE[条件])*2中的任何栏Store_InformationLosSanLosGeographyNewLosSanSQL是SELECTSUM(Sales)FROMStore_InformationWHEREEXISTS(SELECT*FROMGeographyWHEREregion_name='West')[region_nameWest']的条件，了超过一笔的资料，所以EXISTS的条件成立，所以外查询被执行。而外查询本身并没有包含[region_name='West']这个条件。CASESQLIF-THEN-ELSECASESELECTCASE栏位名WHEN1"THENWHEN2"THEN[ELSEN"]FROM表格名"条件"ELSE子句则并不是必须的。在我们的Store_Information中Store_InformationLosSanSanJan-08-'LosAngeles'Sales2'SanDiego'Sales1.5，我们就键入以下的SQL：SELECTstore_name,CASEstore_nameWHEN'LosAngeles'THENSales*2WHEN'SanDiego'THENSales*1.5ELSESales"NewSales",FROMNewLosNewLosSanSan算列出每一行的是一个常见的需求，可惜SQL并没有一个很直接的方式达到这个需求。要以SQL列出，基本的概念是要做一个表格自我连结(selfjoin)，将结果依序列出，然后算出每一行之前(包含那一行本身)有多少行数。这样讲读者听得可能有点困惑，所以Total_SalesS要找出每一行的，我们就打入以下的SQL语句SELECTa1.Name,a1.Sales,COUNT(a2.sales)Sales_RankFROMTotal_Salesa1,Total_Salesa2WHEREa1.Sales<=a2.SalesOR(a1.Sales=a2.SalesANDa1.Name=a2.Name)GROUPBYa1.Name,a1.SalesODERBYa1.SalesDESC,a1.Name结果12S3356WHERE(a1.Salesa2.Sales)，我们算出有多少笔资料Sales栏位的值是比自己本身的值小或是相等。如果在Sales栏位中没有同样大小的资料，那这部分的WHERE子句本身就可以产生出正确的。子句的第二部分，(a1.Sales=a2.SalesANDa1.Namea2.Name)Sales栏位中有同样大小的资料时(像Sla及Jeff这两笔资料)，仍然能够产生正确的。找出『中间』的为何。举例来说，如果总共有9笔资料，那中间就是5(有4545笔资料小)。Total_SalesSSELECTSalesMedian(SELECTa1.Name,a1.Sales,COUNT(a1.Sales)RankFROMTotal_Salesa1,Total_Salesa2WHEREa1.Sales<a2.SalesOR(a1.Sales=a2.SalesANDa1.Name<=a2.Name)GROUPBYa1.Name,a1.SalesODERBYa1.SalesDESC)WHERERank=(SELECT(COUNT(*)+1)DIV2FROM结果读者将会发现，第2行到第6行是跟产生的语句完全一样。第7行则是算出中间的DIV是在MySQL中算出商的方式在不同的数据库中会有不同的方式求商第1SQL并没有一个很直接的方式达到这个需求。要以SQL算出累积总计，基本上的概念与列出类似：第一是先做个表格自我连结(selfjoin)，然后将结果依序列出。在做列出时，我们算出每一行之前(包含那一行本身)有多少行数；而在做累积总计时，我们则是算出每一行之前(包含那一行本身)的总合。Total_SalesSSELECTa1.Name,a1.Sales,SUM(a2.Sales)Running_TotalFROMTotal_Salesa1,Total_Salesa2WHEREa1.Sales<=a2.salesOR(a1.Sales=a2.SalesANDa1.Name=a2.Name)GROUPBYa1.Name,a1.SalesODERBYa1.SalesDESC,a1.Name结果SSQLWHERE子句和ODERBY子句让我们能够在有重复值时能够算要用SQL算出总合百分比，我们需要用到算和累积总计的概念，以及运用子查询的做法。在这里，我们把子查询放在外部查询的SELECT子句中。让我们来看以下的例子：Total_SalesSSELECTa1.Name,a1.Sales,a1.Sales/(SELECTSUM(Sales)FROMTotal_Sales)Pct_To_TotalFROMTotal_Salesa1,Total_Salesa2WHEREa1.Sales<=a2.salesOR(a1.Sales=a2.SalesANDa1.Name=a2.Name)GROUPBYa1.Name,a1.SalesODERBYa1.SalesDESC,a1.Name结果S"SELECTSUM(Sales)FROMTotal_Sales"SQL累积总合百分比算出，我们运用类似总合百分比的概念。两者的不同处在于在这Total_SalesSSELECTa1.Name,a1.Sales,SUM(a2.Sales)/(SELECTSUM(Sales)FROMTotal_Sales)WHEREa1.Sales<=a2.salesOR(a1.Sales=a2.SalesANDa1.Name=a2.Name)GROUPBYa1.Name,a1.SalesODERBYa1.SalesDESC,a1.Name结果S"SELECTSUM(Sales)FROMTotal_Sales"这一段子查询是用来算出总合。我们接下来用累积总计"SUM(a2.Sales)"除以总合来求出每一行的累积总合百分比。数据类SQLSERVERSQLServer25·Binary·CharBinary、VarbinaryBinary数据类型既可以是固定长度的(Binary),Binary[(n)]n位固定的二进制数据。其中，n18000。其窨的大小是n+4个字节。Varbinary[(n)]n位变长度的二进制数据。其中，n18000。其窨的大小是n+4n个字节。在Image数据类型中的数据是以位字符串的，不是由SQLServer解释的，BMP、TIEF、GIFJPEG格式把数据在Image数据类型中。Char，VarcharVarchar8KB。Char是定长字符数据，其长度最多为8KB。超过8KB的ASCII数据可以使用Text数据类型。例如，因为Html文档全部ASCII8KBText数据类型存SQLServer中。UnicodeUnicodeNchar,Nvarchar在SQLServer中，传统的非Unicode数据类型允许使用由特定字符集定义SQLServerUnicode数据类型，列中可以任何由Unicode标准定义的字符。在Unicode标准中，包括了以各种字符集定义UnicodeUnicode数据类型所占用的窨在SQLServer中，Unicode数据以Nchar、Nvarchar和Ntext数据类型。使用这字符类型，这时最多可以4000个字符。当列的长度固定不变时，应该使用Nchar字符类型，同样，这时最多可以4000个字符。当使用Ntext数据类型时，该列可以多于4000个字符。DatetimeSmalldatetime8/17/98时间在后一个数据类型是霎时间，日期在后。在SQLServer中，日期和时间数据类型包括DatetimeSmalldatetime两种类型时，所的日期范围是从1753年1月1日开始，到9999年12月31日结束(每一个值要求8个字节)。使用Smalldatetime数据类型时，所的日期范围是1900年1月1日开始，到2079年月31日结束(每一个值要求4个字节)SetDateFormat{format|@format其中，format|@format_varMDYDMYYMDYDM、MYD和DYMMDY。SetDateFormatYMDSetDateFormatDMY之后，日期的格式为日月有年形式，整数由正整数和负整数组成，例如39、25、0-2和33967。在MicrsoftSQLServer中， Int，Smallint和Tinyint。Int数据类型数据的范围大于Smallint数据类型数据的范围而Smallint据类型数据的范围大于Tinyint数据类型数据的范围。使用Int数据狗昔数据的范围是从-2147483648到2147483647（每一个值要求4个字节空间。使用Smallint数据类型时数据的范围从-32，768到32767（每一个值要求2个字节空间。使用Tinyint数据类型时，数据的范围是从0到255（每一个值要求1个字节空间。精确数据在SQLServer中的数据类型是Decimal和Numeric。这种数据所占的SQLServerFloatReal。例如，三分之一这个分数记作。，当使用近似数据类型时能准确表示。因此，从系统中检索到的