《SQL网络培训》PPT课件.ppt

SQL初级培训,周小强2006-2-18,软件概述,数据库：SQLSERVER,帐套数据库：UFDATA_XXX_XXXX,系统库：UFSYSTEM,模板库：UFMODEL,软件概述,Sqlserver的文件:主文件（*.mdf）-极为重要日志文件（*.Ldf）Access的文件：只有一个文件*.mdb,MicrosoftAccess和SqlServer的区别,Access不支持存储过程等sql高级功能Access在网络应用方面要进行文件共享（是打开文件数据），而sqlserver是进行连接控制（连接数据）,查询-Select语句,SELECTFORMWHERE例：SELECT*FROMYOURTABLENAME,函数：对数据的进一步处理,汇总函数SUMSUM就如同它的本意一样它返回某一列的所有数值的和。SELECTSUM(COLUMAN)FROMYOURTABLECOUNT该函数将返回满足WHERE条件子句中记录的个数。最简单的例子就是您想知道某一个表里的记录数：SELECTCOUNT(*)FROMYOURTABLE,函数：对数据的进一步处理,MAX返回表达式的最大值。语法MAX(ALL|DISTINCTexpression)参数ALL对所有的值进行聚合函数运算。ALL是默认设置。DISTINCT指定每个唯一值都被考虑。DISTINCT对于MAX无意义，使用它仅仅是为了符合SQL-92兼容性。expression常量、列名、函数以及算术运算符、按位运算符和字符串运算符的任意组合。MAX可用于数字列、字符列和datetime列，但不能用于bit列。不允许使用聚合函数和子查询。返回类型返回类型与expression相同。,字符函数,LEFT返回从字符串左边开始指定个数的字符。语法LEFT(character_expression,integer_expression)参数character_expression字符或二进制数据表达式。character_expression可以是常量、变量或列。character_expression必须是可以隐式地转换为varchar的数据类型。否则，请使用CAST函数显式转换character_eger_expression是正整数。如果integer_expression为负，则返回空字符串。返回类型varchar示例A.对列使用LEFT函数下面的示例返回每个书名最左边的5个字符。USEpubsGOSELECTLEFT(title,5)FROMtitlesORDERBYtitle_idRIGHT返回从字符串右边开始指定个数的字符。其参数语法与LEFT函数类似,SUBSTRING返回字符、binary、text或image表达式的一部分。语法SUBSTRING(expression,start,length)参数Expression：是字符串、二进制字符串、text、image、列或包含列的表达式。不要使用包含聚合函数的表达式。Start：是一个整数，指定子串的开始位置。Length：是一个整数，指定子串的长度（要返回的字符数或字节数）。说明由于在text数据上使用SUBSTRING时start和length指定字节数，因此DBCS数据（如日本汉字）可能导致在结果的开始或结束位置拆分字符。此行为与READTEXT处理DBCS的方式一致。然而，由于偶而会出现奇怪的结果，建议对DBCS字符使用ntext而非text。返回类型如果expression是支持的字符数据类型，则返回字符数据。如果expression是支持的binary数据类型，则返回二进制数据。注释在字符数中必须指定使用ntext、char或varchar数据类型的偏移量（start和length）。在字节数中必须指定使用text、image、binary或varbinary数据类型的偏移量。示例在字符串上使用SUBSTRING下例显示如何只返回字符串的一部分。该查询在一列中返回authors表中的姓氏，在另一列中返回authors表中的名字首字母。USEpubsSELECTau_lname,SUBSTRING(au_fname,1,1)FROMauthorsORDERBYau_lname,LEN返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾随空格。语法LEN(string_expression)参数string_expression要计算的字符串表达式。返回类型int示例下面的示例选定字符个数和位于芬兰的公司的CompanyName数据。USENorthwindGOSELECTLEN(CompanyName)ASLength,CompanyNameFROMCustomersWHERECountry=Finland,CAST和CONVERT将某种数据类型的表达式显式转换为另一种数据类型。CAST和CONVERT提供相似的功能。语法使用CAST：CAST(expressionASdata_type)使用CONVERT：CONVERT(data_type(length),expression,style)参数expression是任何有效的MicrosoftSQLServer表达式。data_type目标系统所提供的数据类型，包括bigint和sql_variant。不能使用用户定义的数据类型。lengthnchar、nvarchar、char、varchar、binary或varbinary数据类型的可选参数。style日期格式样式，借以将datetime或smalldatetime数据转换为字符数据（nchar、nvarchar、char、varchar、nchar或nvarchar数据类型）；或者字符串格式样式，借以将float、real、money或smallmoney数据转换为字符数据（nchar、nvarchar、char、varchar、nchar或nvarchar数据类型）。具体的值见帮助。此类函数使用也比较多。,SQL中的子句,SelectFromWhereOrderby,SQL中的子句,Groupby子句GROUPBYALLgroup_by_expression,.nWITHCUBE|ROLLUPUSEpubsSELECTtype,AVG(price)FROMtitlesWHEREadvance$5000GROUPBYtype,HAVING子句HAVING使用HAVING子句选择行HAVING子句对GROUPBY子句设置条件的方式与WHERE子句和SELECT语句交互的方式类似。WHERE子句搜索条件在进行分组操作之前应用；而HAVING搜索条件在进行分组操作之后应用。HAVING语法与WHERE语法类似，但HAVING可以包含聚合函数。HAVING子句可以引用选择列表中出现的任意项。下面的查询得到本年度截止到目前的销售额超过$40,000的出版商：USEpubsSELECTpub_id,total=SUM(ytd_sales)FROMtitlesGROUPBYpub_idHAVINGSUM(ytd_sales)40000,理解应用WHERE、GROUPBY和HAVING子句的正确序列对编写高效的查询代码会有所帮助：WHERE子句用来筛选FROM子句中指定的操作所产生的行。GROUPBY子句用来分组WHERE子句的输出。HAVING子句用来从分组的结果中筛选行。,表的联合（Join）,INNER指定返回每对匹配的行。废弃两个表中不匹配的行。如果未指定联接类型，则这是默认设置。FULLOUTER指定在结果集中包含左表或右表中不满足联接条件的行，并将对应于另一个表的输出列设为NULL。这是对通常由INNERJOIN返回的所有行的补充。LEFTOUTER指定在结果集中包含左表中所有不满足联接条件的行，且在由内联接返回所有的行之外，将另外一个表的输出列设为NULL。RIGHTOUTER指定在结果集中包含右表中所有不满足联接条件的行，且在由内联接返回的所有行之外，将与另外一个表对应的输出列设为NULL。,表的联合（Join）,所需的资料并不是放在同一个资料表中,在这个时候,你就要用到Join，如我们U8中的主子表当然Join如何将不同的数据库的资料结合,还要看你如何使用它。一共有四种不同的Join的方式：INNERJoinFULLOUTERJoinLEFTOUTERJoinRIGHTOUTERJoin,Union运算符,UNION运算符将两个或更多查询的结果组合为单个结果集，该结果集包含联合查询中的所有查询的全部行。这与使用联接组合两个表中的列不同。使用UNION组合两个查询的结果集的两个基本规则是：所有查询中的列数和列的顺序必须相同。数据类型必须兼容。,子查询：内嵌的SQL子句,ifexists(select*fromdbo.sysobjectswhereid=object_id(Ndbo.PO_Podetails)andOBJECTPROPERTY(id,NIsUserTable)=1)droptabledbo.PO_PodetailsGO,操作数据（INSERTUPDATEDELETE）,INSERTINTO子句INSERTINTOtitles(title_id,title,type,pub_id,price)VALUES(BU9876,CreatingWebPages,business,1389,29.99),操作数据（INSERTUPDATEDELETE）,UPDATE子查询子查询可以嵌套在UPDATE、DELETE和INSERT语句以及SELECT语句中。下面的查询使由NewMoonBooks出版的所有书籍的价格加倍。该查询更新titles表；其子查询引用publishers表。UPDATEtitlesSETprice=price*2WHEREpub_idIN(SELECTpub_idFROMpublishersWHEREpub_name=NewMoonBooks)下面是使用联接的等效UPDATE语句：UPDATEtitlesSETprice=price*2FROMtitlesINNERJOINpublishersONtitles.pub_id=publishers.pub_idANDpub_name=NewMoonBooks,操作数据（INSERTUPDATEDELETE）,DELETE子句通过下面嵌套的查询，可以删除商业书籍的所有销售记录：DELETEsalesWHEREtitle_idIN(SELECTtitle_idFROMtitlesWHEREtype=business),操作数据（INSERTUPDATEDELETE）,Update使用注意（where后的条件一定要验证）Select*fromaccinformationwherecid=99andcvalue=8.60Updateaccinformationsetcvalue=8.50wherecid=99andcvalue=8.60,操作数据（SelectInto）,Selectinto子句SELECTShippers.*,Link.Address,Link.City,Link.Region,Link.PostalCodeINTONewShippersFROMShippersJOINLinkServer.DB.dbo.ShippersASLinkON(Shippers.ShipperID=Link.ShipperID),CREATEDATABASE创建一个新数据库及存储该数据库的文件，或从先前创建的数据库的文件中附加数据库。语法CREATEDATABASEdatabase_nameON,.n,.nLOGON,.nCOLLATEcollation_nameFORLOAD|FORATTACH:=PRIMARY(NAME=logical_file_name,FILENAME=os_file_name,SIZE=size,MAXSIZE=max_size|UNLIMITED,FILEGROWTH=growth_increment),.n:=FILEGROUPfilegroup_name,.n以上就是具体的语法，其中的参数可以参照SQL的帮助。,CREATETABLE创建新表。和创建数据库一样，我们也可以利用图形界面工具（如企业管理器来创建），但理解其具体的操作并不是一件坏事。创建表的最基本语法（详细的语法见帮助）就是CREATETABLEtable_name(field1datatypeNOTNULL，field2datatypeNOTNULL，field3datatypeNOTNULL，。)我们来看一个简单的例子，如下：CREATETABLEBILLS(NAMECHAR(30)，AMOUNTINT，ACCOUNT_IDINT)分析该语句创建了一个名字叫BILLS的表，在BILLS表中有三个字段NAME，ACCOUNT和ACCOUNT_ID。其中NAME字段为字符类型可以存储长度30的字符串，而AMOUNT和AMOUNT_ID则只参存储整数。,ALTERTABLE语句,加入已经存在的表中的某一列,ALTERTABLEtablnameADD|column_nameAScomputed_column_expression,.n,以上例子是在MYTABLE里增加一个类型为VARCHAR(20)，允许为NULL的列NEWcolumn。,例子：ALTERTABLEMYTABLEADDNEWcolumnVARCHAR(20)NULL,创建和操作表,删除一列到已经存在的表中,ALTERTABLEtablnameDROPCONSTRAINTconstraint_name|COLUMNcolumn,.n,以上例子是在MYTABLE里删除列NEWcolumn。,例子：ALTERTABLEMYTABLEDROPCOLUMNNEWcolumn,创建和操作表,修改已经存在的表中的某一列,ALTERTABLEtablnameALTERCOLUMNcolumn_namenew_data_type(precision,scale)COLLATENULL|NOTNULL|ADD|DROPROWGUIDCOL,以上例子是在MYTABLE里将列NEWcolumn的类型改变成INT。,例子：ALTERTABLEMYTABLEALTERCOLUMNNEWcolumnINT,注：你可以增加或减少某一列的长度，但是你不能将它减少到使修改后的长度小于其中的已有数据长度。修改前一定要慎重！,创建和操作表,DROPTABLE语句SQL提供了一个可以从数据库去彻底地移去某个表的命令。DROPTABLE可以从数据库中删除一个指定的表。,一旦这个命令发出以后就没有办法撤消它（在事务提交前除外，关于事务我们将在后面讲解）。它最常用在你创建一个临时表并且已经进行完毕了你的全部计划工作的时候。,DROPTABLE语句的语法格式如下DROPTABLEtable_name,下边是如何删掉一个叫NEW_BILLS表的实例DROPTABLENEW_BILLS,注意：系统没有给出你提示该命令不会问你Areyousure?Y/N。但是删除操作已经执行，表已经永远地删除了。,删除一个数据库的命令也类似：DROPDATABASE，这里就不再解说。,创建视图和索引,创建视图的语法如下：CREATEVIEW.view_name(column,.n)WITH,.nASselect_statementWITHCHECKOPTION:=ENCRYPTION|SCHEMABINDING|VIEW_METADATA,创建视图和索引,使用简单的CREATEVIEW下例创建具有简单SELECT语句的视图。当需要频繁地查询列的某种组合时，简单视图非常有用。USEpubsGOIFEXISTS(SELECTTABLE_NAMEFROMINFORMATION_SCHEMA.VIEWSWHERETABLE_NAME=titles_view)DROPVIEWtitles_viewGOCREATEVIEWtitles_viewASSELECTtitle,type,price,pubdateFROMtitlesGO,创建视图和索引,删除视图语句就像每一个CREATE语句一样CREATEVIEW语句对应的也与DROPVIEW语句相对应。其语法形式如下：DROPVIEWview_name在使用它的时候需要记住DROPVIEW命令会使所有与DROP视图相关联的视图不能正常运行，此时如果你访问这个数据库，将返回如下错误：由于绑定错误，未能使用视图或函数对应名称。一些数据库系统甚至会将所有与要DROP的视图相关联的视图也删除掉,创建视图和索引,具体创建索引的语句可以参见帮助CREATEINDEX，删除索引DROPINDEX。,高级SQL-游标,建立（定义）一个游标：,DECLAREau_lnameVARCHAR(30)DECLAREauthors_cursorCURSORFORSELECTau_lnameFROMauthors,OPENauthors_cursor,打开游标：,FETCHNEXTFROMauthors_cursorINTOau_lname,取游标记录：,浏览游标记录：,WHILEFETCH_STATUS=0BEGINprintau_lnameFETCHNEXTFROMauthors_cursorINTOau_lnameEND,关闭游标：,CLOSEauthors_cursor,释放游标：,DEALLOCATEauthors_cursor,高级SQL-存储过程,CREATEPROCEDUREprocedure_name;numberparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASsql_statement.n,高级SQL-触发器,语法：createtriggertrigger_nameontable_nameforinsert,update,deleteasSQL_Statements,基本的事务控制（TRANSACTION）,BEGINTRANSACTIONBEGINTRANSACTIONtransaction_name|tran_name_variableWITHMARKdescriptionCOMMITTRANSACTIONCOMMITTRANSACTIONtransaction_name|tran_name_variableROLLBACKTRANSACTIONROLLBACKTRANSACTIONtransaction_name|tran_name_variable|savepoint_name|savepoint_variable,基本的事务控制（TRANSACTION）,Transaction应用useufdata_887_2004select*fromaccinformationwherecid=99beginTRANSACTIONtestupdateaccinformationsetcvalue=8.50wherecid=99rollbacktransactiontest,存储过程的执行,EXECUTE（exec）EXECUTE(string_variable|Ntsql_string+.n)EXECUTEproc_calculate_taxesp2=A,存储过程的执行,下例使用xp_cmdshell扩展存储过程列出文件扩展名为.exe的所有文件的目录。USEmasterEXECUTExp_cmdshelldir*.exe,系统存储过程sp_executesqlsp_exe