实施部2011年培训(SQL基础部分).ppt

SQL基础知识SQL技能培训发现问题-解决问题,目录,SQL基础知识,SQL语句分:数据定义语言（DataDefinitionLanguage,简称“DDL”）、数据操纵语言（DataManipulationLanguage,简称“DML”）、数据控制语言（DataControlLanguage,简称“DCL”）三部分功能。主要讲解DML部分，查询是DML部分中最主要的部分，所以重点讲解。在sqlserver中DML包括：select,update,insert,delete;DDL包括：create,drop,alter;DCL包括grant,revoke,deny,execute,while等等。例表：假定对score（成绩）、student（学籍卡）、course（课程）、classcourse（科目属性设置表）这四张表操,SQL基础知识,SELECT语句1语法：select*fromtable_namewheregroupbyhavingorderby语法的执行顺序说明：先执行from字句，然后依次是where、groupby、having、orderby字句，最后才对结果select2普通单表查询select*fromSuppliersselecttop100*fromSuppliers3不重复查询SELECTDISTINCTCountryFROMSuppliers4带条件查询(where后面不可跟聚合函数，比较运算符的右边不可出现集合值)SELECT*FROMSuppliersWHERECountry=USA,SQL基础知识,SELECT语句（续）5关联查询（为了避免笛卡儿乘积的产生，对于n个表的连接，至少要有n-1个条件；对于重复的字段，则需要在字段前加上表的别名SELECTP.ProductID,P.ProductName,S.SupplierID,S.CompanyNameFROMSuppliersS,ProductsPWHERES.SupplierID=P.SupplierIDANDS.Country=USA这个语句根据ANSI-92标准也可以这样写：SELECTP.ProductID,P.ProductName,S.SupplierID,S.CompanyNameFROMSuppliersASSINNERJOINProductsASPON(S.SupplierID=P.SupplierID)WHERES.Country=USA,SQL基础知识,SELECT语句（续）6分组(groupby)、筛选(having)SELECTS.SupplierID,S.CompanyName,COUNT(*),COUNT(1)FROMSuppliersASSINNERJOINProductsASPON(S.SupplierID=P.SupplierID)WHERES.Country=USAGROUPBYS.SupplierID,S.CompanyNameHAVINGCOUNT(*)1说明：语句先执行where条件，然后在条件里分组（groupby）排列，最后在分组里进一步筛选（having）；select选择列表里除聚合函数以外的列都必须出现在groupby后面，groupby后面不能跟字段别名，不支持任何使用了聚合函数的集合列；having可以包含聚合函数，可以引用选择列表中出现的任意列,SQL基础知识,SELECT语句（续）7聚合函数(avg)、分组(groupby)、筛选(having)、排序(orderby)SELECTS.SupplierID,S.CompanyName,COUNT(*)品种数,AVG(P.UnitPrice)平均单价,MAX(UnitsInStock)库存最大FROMSuppliersASSINNERJOINProductsASPON(S.SupplierID=P.SupplierID)WHERES.Country=USAGROUPBYS.SupplierID,S.CompanyNameHAVINGCOUNT(*)1ORDERBYS.SupplierIDDESC说明：聚合函数（也称统计函数）共有5个，分别是：avg-求平均值，count-统计数目，max-求最大值，min-求最小值，sum-求和，更具体的用法查看sqlserver联机帮助；排序有两种：asc（升序），desc（降序）,SQL基础知识,SELECT语句（续）8并集运算SELECTSupplierID,CompanyNameFROMSuppliersWHERECountry=USAUNIONALLSELECTSupplierID,CompanyNameFROMSuppliersWHERECountry=Japan说明：unionall会取多表并集的重复行；union可以去掉重复行；多表之间的union必须列数相同一一对应，对应的列之间必须可以隐性的转换成相同的数据类型（即兼容）,SQL基础知识,SELECT语句（续）10嵌套查询（分为层次嵌套和相关嵌套两种，当嵌套多于2个又称多级嵌套）层次嵌套(in,notin,比较运算符)SELECT*FROMPRODUCTSWHERESupplierIDIN(SELECTSupplierIDFROMSuppliersWHERECountry=USA)任何的层次查询都可以化解成关联查询，如这题也可以写成：SELECTP.*FROMPRODUCTSPINNERJOINSuppliersSONP.SupplierID=S.SupplierIDWHERES.Country=USASELECTDISTINCTP.*FROMPRODUCTSP,SuppliersSWHEREP.SupplierID=S.SupplierIDANDS.Country=USA相关嵌套(EXISTS,NOTEXISTS)SELECT*FROMPRODUCTSASPWHEREEXISTS(SELECT1FROMSuppliersASSWHEREP.SupplierID=S.SupplierIDANDS.Country=USA),SQL基础知识,SELECT语句（续）10嵌套查询（分为层次嵌套和相关嵌套两种，当嵌套多于2个又称多级嵌套）说明：in、notin执行的机制是先执行紧跟其后的子查询，然后再执行父查询，判断父查询的关键字是否存在于子查询所得到的集合里；exists、notexists的执行机制是每取得子查询的一条记录马上就与父查询记录进行比较，一直遍历整个子查询到结束，相关查询之间的连接不是列之间的关系，而是表之间的关系，所以在select列表中，通常不需要明确的指定列名，使用*或数字（如1）代替就可以了；使用notin或notexists可以很方便的实现非成员关系型和非存在关系型的查询难题，降低查询复杂度；如果能确定子查询返回的是单值，那么可以使用比较运算符；另外嵌套查询只能放在小括号里；子查询还可以嵌套其他子查询，这就是多级查询.,SQL技能培训,SQLServer2000有6个系统数据库1、Master：存储所有系统信息。包括登录、系统设置、初始化信息和其它系统数据库及用户数据库的相关信息。2、Model：是所有用户数据库和Tempdb的模板数据库，它含有Master数据库所有系统表的子集。3、Msdb数据库是代理服务数据库，为其警报、任务调度和记录操作员的操作提供存储空间。4、Tempdb是一个临时数据库，它为所有的临时表、临时存储过程及其它临时操作提供存储空间。5、Pubs和Northwind数据库是两个实例数据库。,SQL技能培训,几个重要的系统表1.Sysobjects表：存储每个数据库对象，都含有一行记录。2.Syscolumns表：表或者视图的每个列和存储过程中的每个参数含有一行记录。3.Sysindexes表：每个索引和没有聚簇索引的每个表含有一行记录，它还对包括文本/图像数据的每个表含有一行记录。4.Sysusers表：每个WindowsNT用户、WindowsNT用户组、SQLServer用户或者SQLServer角色含有一行记录。5.Sysdatabases表：每个系统数据库和用户自定义的数据库都会有一行记录。6.Sysdepends表：记录表、视图和存储过程之间的每个依赖关系。7.Sysconstraints表：记录每个完整性约束，包括主键约束、默认值约束、外键约束等等。,SQL技能培训,怎么看帮助？,SQL技能培训,一些技巧1）Exists和IN的区别使用in和exist？in是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。例如：表A（小表），表B（大表）1：select*fromAwhereccin(selectccfromB)效率低，用到了A表上cc列的索引；select*fromAwhereexists(selectccfromBwherecc=A.cc)效率高，用到了B表上cc列的索引。2：select*fromBwhereccin(selectccfromA)效率高，用到了B表上cc列的索引；select*fromBwhereexists(selectccfromAwherecc=B.cc)效率低，用到了A表上cc列的索引。一直以来认为exists比in效率高的说法是不准确的。如果查询的两个表大小相当，那么用in和exists差别不大。如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：,SQL技能培训,一些技巧2）notin和notexists如果查询语句使用了notin那么内外表都进行全表扫描，没有用到索引；而notextsts的子查询依然能用到表上的索引。所以无论那个表大，用notexists都比notin要快。in与=的区别selectnamefromstudentwherenamein(zhang,wang,li,zhao);与selectnamefromstudentwherename=zhangorname=liorname=wangorname=zhao的结果是相同的。,SQL技能培训,一些技巧3）EXECUTE(EXEC)和SP_EXECUTESQL的区别MSSQL为我们提供了两种动态执行SQL语句的命令，分别是EXEC和sp_executesql;通常sp_executesql则更具有优势，它提供了输入输出接口，而EXEC没有。还有一个最大的好处就是利用sp_executesql，能够重用执行计划，执行性能大大提高EXEC在某些情况下会更灵活。EXEC命令有两种用法，一种是执行一个存储过程，另一种是执行一个动态的批处理。以下所讲的都是第二种用法。实例1：USENorthwindDECLARETableNameVARCHAR(50),SqlNVARCHAR(500),OrderIDINTSETTableName=Orders;SETOrderID=10251;SETsql=SELECT*FROM+QUOTENAME(TableName)+WHEREOrderID=+CAST(OrderIDASVARCHAR(10)+ORDERBYORDERIDDESCEXEC(sql);-注：这里的EXEC括号中只允许包含一个字符串变量，但是可以串联多个变量，如果我们这样写EXEC：EXEC(SELECTTOP(+CAST(TopCountASVARCHAR(10)+)*FROM+QUOTENAME(TableName)+ORDERBYORDERIDDESC);-SQL编译器就会报错，编译不通过，而如果我们这样：EXEC(sql+sql2+sql3);编译器就会通过,SQL技能培训,一些技巧3）EXECUTE和SP_EXECUTESQL的区别实例2：USENorthwindDECLARETableNameVARCHAR(50),SqlNVARCHAR(500),OrderIDINTSETTableName=OrdersSETOrderID=10251SETsql=SELECT*FROM+TableName+WHEREOrderID=OrderIDORDERBYORDERIDDESCEXEC(sql)关键就在SETsql这一句话中，如果我们运行这个批处理，编译器就会产生一下错误使用EXEC时，如果想访问变量，必须把变量内容串联到动态构建的代码字符串中。如：SETsql=SELECT*FROM+TableName+WHEREOrderID=+CAST(OrderIDASVARCHAR(10)+ORDERBYORDERIDDESC串联变量也存在性能方面的弊端。DECLARETableNameVARCHAR(50),sqlNVARCHAR(500)SETTableName=OrdersSETsql=SELECT*FROM+TableName+WHEREOrderID=OrderIDORDERBYORDERIDDESCEXECsp_executesqlsql,NOrderIDINT,OrderID=10251注意最后一行；所以，不考虑优化问题，我们都可以直接使用EXEC,SQL技能培训,一些技巧4）MIN(MAX,AVG)分组函数的使用要求：罗列出西药房，西药的所有近效期的药品极其库存情况YP_Stock表的主键是DeptCode,DrugCode,BatchNo这个查询怎么写？,SQL技能培训,一些技巧4）MIN(MAX,AVG)分组函数的使用实例：DECLARETTABLE(DrugCodeVARCHAR(18),BatchNOVARCHAR(20)SELECTSM.DrugCode,SM.BatchNo,SM.ExpireDate,CONVERT(CHAR(20),SM.ExpireDate,20)+CAST(SM.BatchNoASCHAR(16)FROMYP_StockASSMWHEREDeptCode=106ANDEXISTS(SELECT1FROMPD_DrugListASPWHEREP.DrugCode=SM.DrugCodeANDP.ClassCode=1)SELECTSM.DrugCode,CAST(RIGHT(MIN(CONVERT(CHAR(20),SM.ExpireDate,20)+CAST(SM.BatchNoASCHAR(16),16)ASVARCHAR(16)ASBatchNoFROMYP_StockASSMWHEREDeptCode=106ANDEXISTS(SELECT1FROMPD_DrugListASPWHEREP.DrugCode=SM.DrugCodeANDP.ClassCode=1)GROUPBYSM.DrugCodeINSERTINTOT(DrugCode,BatchNO)SELECTSM.DrugCode,CAST(RIGHT(MIN(CONVERT(CHAR(20),SM.ExpireDate,20)+CAST(SM.BatchNoASCHAR(16),16)ASVARCHAR(16)ASBatchNoFROMYP_StockASSMWHEREDeptCode=106ANDEXISTS(SELECT1FROMPD_DrugListASPWHEREP.DrugCode=SM.DrugCodeANDP.ClassCode=1)GROUPBYSM.DrugCodeSELECT*FROMYP_StockASSWHERES.DeptCode=106ANDEXISTS(SELECT1FROMTASTWHERET.DrugCode=S.DrugCodeANDT.BatchNO=S.BatchNO),SQL技能培训,一些技巧5）top的用法先看下面这个语句SELECT*FROMZY_PatientExpensea)TOPN(N数字)SELECT*FROMZY_PatientExpenseSELECTTOP10*FROMZY_PatientExpenseb)TOPNPERCENTSELECTTOP10PERCENT*FROMZY_PatientExpense,SQL技能培训,一些技巧6）WHERE1=2或者WHERE11、WHERE1=1先看两个SQL语句SELECT*FROMZY_PatientExpenseWHERE1=2SELECT*FROMZY_PatientExpenseWHERE11SELECT*FROMZY_PatientExpenseWHERE1=1接下来，DECLAREDeptINTSETDept=1IFDeptISNULLSETDept=-1SELECT*FROMXTD_UserWHEREDeptCode=DeptOR-1=Dept,SQL技能培训,一些技巧7）BEGINTRANROLLBACKCOMMIT,SQL技能培训,一些技巧8）其他a)得到如“YYYY-MM-DD”日期格式SELECTGETDATE(),CONVERT(VARCHAR(10),GETDATE(),120)b)清除数据库的日志文件dbccshrinkdatabase(ahcs_TR)-有用select*fromsysfiles-收缩日志文件,假设文件标识(ID)号为2，将日志文件收缩为1M,执行时保证没有其它用户连接。DBCCSHRINKFILE(2),SQL技能培训,临时表一种特殊的数据类型，用于存储结果集以供后续处理。相当于一个表，但是是作为变量存储在内存当中。IFOBJECT_ID(tempdb.#Temp)ISNOTNULLDROPTABLE#TempCREATETABLE#Temp(科室编码VARCHAR(10),科室VARCHAR(50)INSERTINTO#Temp(科室编码,科室)SELECTDeptCode,DeptNameFROMPD_DepartmentWHEREInputCodeLIKEA%ALTERTABLE#TempADD输入码VARCHAR(8)GOUPDATE#TempSET#Temp.输入码=P.InputCodeFROMPD_DepartmentASPWHEREP.DeptCode=#Temp.科室编码SELECT*FROM#TempDROPTABLE#Temp,SQL技能培训,表变量一种特殊的数据类型，用于存储结果集以供后续处理。相当于一个表，但是是作为变量存储在内存当中。DECLARETTABLE(科室代码VARCHAR(18),科室VARCHAR(40)INSERTINTOT(科室代码,科室)SELECTDeptCode,DeptNameFROMPD_DepartmentWHEREInputCodeLIKEA%SELECT*FROMT,SQL技能培训,那么表变量临时表的区别？比较临时表及表变量都可以通过SQL的选择、插入、更新及删除语句，它们的的不同主要体现在以下这些：1)表变量是存储在内存中的，临时表存储在TempDB中;2)在表变量中，是不允许有非聚集索引的;临时表可定义索引；3)表变量是不允许有DEFAULT默认值，也不允许有约束;临时表可以4)临时表可动态修改列信息，但会有些限制，而表变量则需在申明时候制定这些信息;5)临时表中是有锁的机制，而表变量中就没有锁的机制。,实例1CREATETA