《SQL基础培训.ppt》由会员分享,可在线阅读,更多相关《SQL基础培训.ppt(36页珍藏版)》请在得力文库 - 分享文档赚钱的网站上搜索。
1、SQL培训用友软件股份有限公司2011年6月01日目录NC产品中的产品中的SQL常见常见SQL 问题问题SQL基础基础目录NC产品中的产品中的SQL常见常见SQL 问题问题SQL基础基础SQL介绍-1SQL(StructuredQueryLanguage,结构查询语言)是一个功能强大的数据库语言。SQL通常使用于数据库的通讯。ANSI(美国国家标准学会)声称,SQL是关系数据库管理系统的标准语言。SQL语句通常用于完成一些数据库的操作任务,比如在数据库中更新数据,或者从数据库中检索数据。使用SQL的常见关系数据库管理系统有:Oracle、DB2、MicrosoftSQLServer等等。虽然绝
2、大多数的数据库系统使用SQL,但是它们同样有它们自立另外的专有扩展功能用于它们的系统。但是,标准的SQL命令,比如“Select”、“Insert”、“Update”、“Delete”“Create”和“Drop”常常被用于完成绝大多数数据库的操作。SQL功能强大,但是概括起来,它可以分成以下几组:DML(DataManipulationLanguage,数据操作语言)用于检索或者修改数据;DDL(DataDefinitionLanguage,数据定义语言)用于定义数据的结构,比如创建、修改或者删除数据库对象;DCL(DataControlLanguage,数据控制语言)用于定义数据库用户的权
3、限。SQL介绍-2DML数据操作 可以细分为以下的几个语句SELECT:用于检索数据;INSERT:用于增加数据到数据库;UPDATE:用于从数据库中修改现存的数据DELETE:用于从数据库中删除数据。DDL数据定义 语句可以用于创建用户和重建数据库对象CREATETABLE-创建一个数据库表DROPTABLE-从数据库中删除表ALTERTABLE-修改数据库表结构CREATEVIEW-创建一个视图DROPVIEW-从数据库中删除视图CREATEINDEX-为数据库表创建一个索引DROPINDEX-从数据库中删除索引CREATEPROCEDURE-创建一个存储过程DROPPROCEDURE-从
4、数据库中删除存储过程CREATETRIGGER-创建一个触发器DROPTRIGGER-从数据库中删除触发器DCL数据控制用于创建关系用户访问以及授权的对象GRANT-授予用户访问权限DENY-拒绝用户访问REVOKE-解除用户访问权限SQL介绍-3在正式学习SQL语言之前,首先让我们对数据库结构有一个基本认识:数据库的数据体系结构基本上是三级结构,但使用术语与传统关系模型术语不同。在SQL中,关系模式(模式)称为“基本表”(basetable);存储模式(内模式)称为“存储文件”(storedfile);子模式(外模式)称为“视图”(view);元组称为“行”(row);属性称为“列”(col
5、umn)。1.表由行集构成,一行是列的序列(集合),每列与行对应一个数据项。2.视图是由若干基本表或其他视图构成的表的定义。3.一个基本表可以跨一个或多个存储文件,一个存储文件也可存放一个或多个基本表。每个存储文件与外部存储上一个物理文件对应4.用户可以用SQL语句对视图和基本表进行查询等操作。在用户角度来看,视图和基本表是一样的,没有区别,都是关系(表格)。执行SQL的工具Oracle:PL/SQLDeveloper/SQLPlusPL/SQLDeveloper:智能提示,SQL语句抽取,数据库备份,SQL格式化等。安装需要Oracle的OTN协议驱动。SQLServer:SqlServer
6、ManagerStudioSqlServerManagerStudio:数据库管理、数据库备份、其他常见SQL工作。安装需要SqlServer的NativeClient驱动。DB2:QuestCentralQuestCentral:数据库备份,其他常见SQL工作。需要安装DB2Native驱动。万能工具AquaDataStudio基于JDBC。检索数据SQL的基本语法无条件查询有条件查询比较运算符算术运算符逻辑运算符子查询多表连接查询排序查询计算查询检索数据-基本语法Select基本语法:SELECTselect_listFROMtable_source WHEREsearch_conditi
7、on GROUPBYgroup_by_expression HAVINGsearch_condition ORDERBYorder_expressionASC|DESCSELECT子句:指定由查询返回的列。FROM子句:Selec语句中使用的表、视图WHERE子句:指定用于限制返回的行的搜索条件。GROUPBY子句:指定用来放置输出行的组,并且如果SELECT子句中包含聚合函数,则计算每组的汇总值。指定GROUPBY时,选择列表中任一非聚合表达式内的所有列都应包含在GROUPBY列表中,或者GROUPBY表达式必须与选择列表表达式完全匹配。HAVING子句:指定组或聚合的搜索条件。HAVING
8、通常与GROUPBY子句一起使用。ORDERBY子句:指定结果集的排序。检索数据-无条件查询检索整张表:select*fromtable*号表示选择表中所有列检索表中某些列:selectpk_corp,unitcode,unitnamefrombd_corp从公司基本档案中选择pk_corp,公司编码,公司名称取消重复值:selectdistinctecotypefrombd_corp将bd_corp中重复ecotype值在结果中消除掉.DISTINCT关键字可从SELECT语句的结果中除去重复的行。如果没有指定DISTINCT,那么将返回所有行,包括重复的行。检索数据-有条件查询有条件查询就
9、是WHERE子句,指定用于限制返回的行的搜索条件。算术运算符:算术运算符在两个表达式上执行数学运算,这两个表达式可以是数字数据类型分类的任何数据类型。+(加)。-(减)。*(乘)。/(除)。%(模)模的含义是除法的整数余数如:selectchinese+englishasallscorefromscorewherename=a将a的语文成绩和英语成绩相加得到总分.其中as是用于为结果集列指定别名比较运算符:比较运算符测试两个表达式是否相同,可以用于数值,字符串,日期等=(等于)(大于)=(大于或等于)=(小于或等于)(不等于)如:selectnamefromscorewherechinese6
10、0将语文成绩不及格的查找出来.检索数据-有条件查询逻辑运算符:逻辑运算符对某个条件进行测试,以获得其真实情况。逻辑运算符和比较运算符一样,返回带有TRUE或FALSE值的布尔数据类型运算符含义AND如果两个布尔表达式都为TRUE,那么就为TRUE。IN如果操作数等于表达式列表中的一个,那么就为TRUE。LIKE如果操作数与一种模式相匹配,那么就为TRUE。NOT对任何其它布尔运算符的值取反。OR如果两个布尔表达式中的一个为TRUE,那么就为TRUE。查询空值:select*frombd_corpwheretaxcodeisnullselect*frombd_corpwheretaxcodeis
11、notnull搜索串匹配:select*frombd_corpwhereunitnamelike正航%select*frombd_corpwhereunitnamelike%正航%select*frombd_corpwhereunitnamelike%正航查找一组值:selectpk_corp,unitcode,unitnamefrombd_corpwherepk_corpin(1001,1003)查找pk_corp等于1001或者等于1003的公司名和编号检索数据-子查询IN子查询通过IN(或NOTIN)引入的子查询结果是一列零值或更多值。子查询返回结果之后,外部查询将利用这些结果。sele
12、ct*fromhi_psndoc_eduwherepk_psndocin(selectpk_psndocfrombd_psndocwherepsnname=木普凤)查找员工木普凤的学历子集信息EXISTS子查询使用EXISTS关键字引入一个子查询时,就相当于进行一次存在测试。外部查询的WHERE子句测试子查询返回的行是否存在。子查询实际上不产生任何数据;它只返回TRUE或FALSE值。select*fromhi_psndoc_eduwhereexists(selecta.pk_psndocfrombd_psndocawherea.pk_psndoc=hi_psndoc_edu.pk_psndo
13、canda.psnname=木普凤)此语句等价与上面的IN子查询.检索数据-多表连接查询内连接innerjoinselect psn.psncode,psn.psnname,edu.education,edu.schoolfrom bd_psndoc as psn inner join hi_psndoc_edu as edu on psn.pk_psndoc=edu.pk_psndoc 查询结果返回人员编码、人员姓名、学历、学校内连接指的是两个表中结果集的交集,如图:工作信息学历子集学历子集检索数据-多表连接查询左连接leftouterjoinselect psn.psncode,psn.p
14、snname,edu.education,edu.schoolfrom bd_psndoc as psn left outer join hi_psndoc_edu as edu on psn.pk_psndoc=edu.pk_psndoc 左外连接首先要确保左边的表数据是完全的工作信息工作信息学历子集检索数据-多表连接查询右连接rigthouterjoinselect psn.psncode,psn.psnname,edu.education,edu.schoolfrom bd_psndoc as psn right outer join hi_psndoc_edu as edu on ps
15、n.pk_psndoc=edu.pk_psndoc 右外连接和左外连接正好相反,首先要确保右边表的数据完整,左边表的数据检索数据-连接查询Pk_psndocpsncodepsnnameA张颖郭凯付继红工作信息Pk_psndoc学历学校A本科北京大学专科北京医专研究生清华大学学历子集思考:针对以下示例数据四种连接方式返回结果是怎样的?检索数据-排序查询语法ORDERBYorder_by_expressionASC|DESC,.n参数order_by_expression指定要排序的列。可以将排序列指定为列名或列的别名(可由表名或视图名限定)和表达式,或者指定为代表选择列表内的名称、别名或表达式的
16、位置的负整数。可指定多个排序列。ORDERBY子句中的排序列序列定义排序结果集的结构。ORDERBY子句可包括未出现在此选择列表中的项目。然而,如果指定SELECTDISTINCT,或者如果SELECT语句包含UNION运算符,则排序列必定出现在选择列表中。此外,当SELECT语句包含UNION运算符时,列名或列的别名必须是在第一选择列表内指定的列名或列的别名。说明在ORDERBY子句中不能使用ntext、text和image列。ASC指定按递增顺序,从最低值到最高值对指定列中的值进行排序。DESC指定按递减顺序,从最高值到最低值对指定列中的值进行排序。空值被视为最低的可能值。selectpk
17、_corp,unitcode,unitnamefrombd_corporderbypk_corp将结果集按照pk_corp从小到大排序,反之加上desc检索数据-计算查询语法GROUPBYgroup_by_expression,.n参数group_by_expression是对其执行分组的表达式。group_by_expression 也称为分组列。group_by expression 可以是列或引用列的非聚合表达式。在选择列表内定义的列的别名不能用于指定分组列。说明text、ntext和image类型的列不能用于group_by_expression。SELECTsex,count(pk_
18、psnbasdoc)FROMbd_psnbasdocGROUPBYsex常用的列函数:MAX:取某列最大值MIN:取某列最小值AVG:取某列平均值COUNT:返回集合中项目的数量插入数据基本语法:Insert into 表名(字段列表)values(值列表)|select_statement如:Insert into bd_defdoc(doccode,docname,docsystype,dr,pk_corp,pk_defdoc,pk_defdoc1,pk_defdoclist,sealflag,ts)Values(CM001,固定期限、,0,0,0001,0001AA10000000001
19、20W,null,0001CM00000000000001,null,2003-10-28 15:36:32)Insert Into bd_deptdoc_x select*from be_deptdoc更新数据基本语法:Update table_name|view_nameSet column_list=expression,.Where clauseUpdate语句用来修改表中已存在的数据。Update语句既可以一次修改一行数据,也可以一次修改许多行,甚至可以一次修改表中的全部数据。Update语句使用Where子句指定要修改的行,使用Set子句给出新的 数据。新数据可以是常量,也可以是指
20、定的表达式,还可以是使用From子句来自其他表的数据。例子:Update bd_psnbasdoc Set sex=男 Where psnname=黄红删除数据基本语法:Delete From table_nameWhere search_conditions删除数据使用Delete语句。Delete语句可以一次从一个表中删除一条或者多条数据行。例子:Delete bd_corp Where unitcode like 1010%在删除表中的全部数据时,还可以使用TruncateTable语句。TruncateTable语句和Delete语句都可以将表中的全部数据删除,但是,两条语句又有不同的
21、特点。当用户使用Delete语句删除数据时,被删除的数据要记录在日志中。并不将对数据的变化记录在日志中。因此,使用TruncateTablebd_corp语句删除记录的速度快于使用Deletefrombd_corp语句删除表中记录的速度。Truncate是DDL语句,DDL语句执行后,隐含地提交一个事务,而Delete是DML语句。练习题1.查询公司编码为001的公司PK值.公司基本档案表2.按照公司编码对bd_corp表进行倒序排序.公司基本档案表3.取公司编码列值包含001的记录 公司基本档案表4.取出不重复的公司简称.公司基本档案5.如何找出公司简称相同的记录.公司基本档案6.找出人员编
22、码psncode=0001的工作信息7.将公司编码=001的公司简称更改为原简称+”new”目录NC产品中的产品中的SQL常见常见SQL 问题问题SQL基础基础NC中间件SQL翻译引擎NC的中间件SQL翻译引擎在NC的EJB层,存在着一个SQL翻译引擎。此SQL翻译引擎的主要工作是把前前端应用发起的MSSQLServer的SQL语句翻译成可以在底层数据库运行的语句。此翻译工作主要体现在SQL函数的翻译上。注意1:并非所有的SQL语句都能正确翻译成可以在底层数据库上可以运行的SQL,导致一些非常有用的函数和语法并不能使用。例如UpdateFrom,MergerInto.语法,ISDate函数等。
23、NC中间件SQL执行工具NC中间件SQL执行工具:位置:NC_HOME/bin/dbtRunSQL.bat。使用方式:在文件dbtRunSQL.ini中书写需要执行的SQL,按照一行一条完整的SQL解析执行。配置完成之后,在命令行提示符下,执行:dbtRunSQL.bat dsName./dbtRunSQL.sh dsName目录NC产品中的产品中的SQL常见常见SQL 问题问题SQL基础基础常见问题问题1:含有层级关系的树形参照无法显示。原因分析极有可能是层级关系字段出现循环引用导致。判定方法OracleConnectby语法。例如:常见问题问题2:倒入的数据日期存在格式的问题。原因分析出现
24、2009-02-31或者长度不够10位的非法日期字符串。判定方法SQLServer中的IsDate函数。在Oracle中请使用自定义IsDate函数。常见问题问题3:使用其他表中的数据快速更新目标表中的数据。方法:SqlServer中使用UpdateFrom.语句.DB2和Oracle中使用:MergeintoUsing语句.举例:MERGE INTOarchivearUSING(SELECTactivity,descriptionFROMactivities)acON(ar.activity=ac.activity)WHEN MATCHED THENUPDATE SET descriptio
25、n=ac.descriptionWHEN NOT MATCHED THENINSERT(activity,description)VALUES(ac.activity,ac.description)常见问题问题4:NULL值的比较。问题根源:NULL本身代表不确定的内容。即,NULL既可能代表1也可能代表3,正是由于NULL的不确定性,故NULL和其他任何值进行比较永远返回False.即使两个NULL值进行比较。对待NULL值各数据库的奇特之处:SQL SERVER:NULL值和空字符串区分对待。ORACLE:把空字符串看作是NULL对待。常见问题问题5:实现动态SQLLIKE功能。selec
26、tpk_corp,innercodefrombd_corpawhereexists(selectinnercodefrombd_corpbwhereb.pk_corp=3586andlocate(b.innercode,a.innercode)=1);此功能实现的功能类似于:likeabc%,当然根据是like%abc或者是like%abc%,只需要更改一索引值以及关系运算符号即可.上面的是针对DB2的,对于SQLServer,oracle只需要替换相应的locate函数即可!常见问题问题6:行号,获取前N行数据SQL SERVER 2005selecttop3row_number()over
27、(orderbybd_psndoc.psncodeasc),bd_psndoc.psncodefrombd_psndocOracleselectbd_psndoc.*,rownumfrombd_psndocDB2selectbd_org.orgname,bd_org.orgcode,row_number()over(orderbyorgcodeasc)asrownumfrombd_orgfetchfirst10rowsonly常见问题问题6:跟踪NC中执行的SQL。最简单的方法:启动SQLServer的SQLProfiler,连接到指定的数据库,新建一个跟踪,在NC中进行数据查询,更新,删除等操作后,SQLProfiler会把目标数据库中执行的SQL抓取出来。方便BUG跟踪,理解NC后台业务。常见问题问题7:IN和Exists子句可以使用INNERJOIN替代不要迷信In的效率低Exists的效率高。数据库中没有绝对的秘籍或者参数,能够彻底地解决某个问题。问题要分场合。
限制150内