《SQL基础培训》PPT课件.ppt

《《SQL基础培训》PPT课件.ppt》由会员分享，可在线阅读，更多相关《《SQL基础培训》PPT课件.ppt（36页珍藏版）》请在装配图网上搜索。

1、SQL 培训用友软件股份有限公司2011年 6月 01日 NC产品中的SQL常见SQL 问题SQL基础 NC产品中的SQL常见SQL 问题SQL基础 SQL介绍-1SQL(Structured Query Language，结构查询语言)是一个功能强大的数据库语言。SQL通常使用于数据库的通讯。ANSI（美国国家标准学会）声称，SQL是关系数据库管理系统的标准语言。SQL语句通常用于完成一些数据库的操作任务，比如在数据库中更新数据，或者从数据库中检索数据。使用SQL的常见关系数据库管理系统有：Oracle、 DB2、 Microsoft SQL Server等等。虽然绝大多数的数据库系统使用S

2、QL，但是它们同样有它们自立另外的专有扩展功能用于它们的系统。但是，标准的SQL命令，比如“Select”、 “Insert”、 “Update”、 “Delete” “Create”和 “Drop”常常被用于完成绝大多数数据库的操作。SQL功能强大，但是概括起来，它可以分成以下几组：DML（Data Manipulation Language，数据操作语言） 用于检索或者修改数据； DDL（Data Definition Language，数据定义语言） 用于定义数据的结构，比如 创建、修改或者删除数据库对象； DCL（Data Control Language，数据控制语言）用于定义数据库

3、用户的权限。 SQL介绍-2DML数据操作 可以细分为以下的几个语句 SELECT：用于检索数据； INSERT：用于增加数据到数据库； UPDATE：用于从数据库中修改现存的数据 DELETE：用于从数据库中删除数据。DDL数据定义 语句可以用于创建用户和重建数据库对象CREATE TABLE -创建一个数据库表DROP TABLE -从数据库中删除表ALTER TABLE -修改数据库表结构CREATE VIEW -创建一个视图DROP VIEW -从数据库中删除视图CREATE INDEX -为数据库表创建一个索引 DROP INDEX -从数据库中删除索引CREATE PROCEDUR

4、E -创建一个存储过程DROP PROCEDURE -从数据库中删除存储过程CREATE TRIGGER -创建一个触发器DROP TRIGGER -从数据库中删除触发器DCL数据控制 用于创建关系用户访问以及授权的对象 GRANT -授予用户访问权限DENY -拒绝用户访问REVOKE -解除用户访问权限 SQL介绍-3在正式学习SQL语言之前，首先让我们对数据库结构有一个基本认识:数据库的数据体系结构基本上是三级结构，但使用术语与传统关系模型术语不同。在SQL中，关系模式(模式)称为“基本表”(base table)；存储模式(内模式)称为“存储文件”(stored file)；子模式(外

5、模式)称为“视图”(view)；元组称为“行”(row)；属性称为“列”(column)。1. 表由行集构成，一行是列的序列(集合)，每列与行对应一个数据项。2. 视图是由若干基本表或其他视图构成的表的定义。3. 一个基本表可以跨一个或多个存储文件，一个存储文件也可存放一个或多个基本表。每个 存储文件与外部存储上一个物理文件对应4. 用户可以用SQL语句对视图和基本表进行查询等操作。在用户角度来看，视图和基本表是一样的，没有区别，都是关系(表格)。 执行SQL的工具Oracle: PL/SQL Developer/SQL Plus PL/SQL Developer： 智能提示，SQL 语句抽取

6、，数据库备份，SQL 格式化等。安装需要Oracle的OTN协议驱动。SQL Server: Sql Server Manager Studio Sql Server Manager Studio : 数据库管理、数据库备份、其他常见SQL工作。安装需要Sql Server的Native Client 驱动。DB2 : Quest Central Quest Central：数据库备份，其他常见SQL工作。需要安装DB2 Native 驱动。万能工具 Aqua Data Studio 基于JDBC。 检索数据 SQL的基本语法 无条件查询 有条件查询 比较运算符 算术运算符 逻辑运算符 子查询

7、 多表连接查询 排序查询 计算查询 检索数据-基本语法Select基本语法:SELECT select_listFROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC | DESC SELECT子句: 指定由查询返回的列。FROM子句: Selec语句中使用的表、视图 WHERE 子句: 指定用于限制返回的行的搜索条件。GROUP BY 子句:指定用来放置输出行的组，并且如果 SELECT 子句 中包含聚

8、合函数，则计算每组的汇总值。指定 GROUP BY 时，选择列表中任一非聚合表达式内的所有列都应包含在 GROUP BY 列表中，或者 GROUP BY 表达式必须与选择列表表达式完全匹配。HAVING 子句: 指定组或聚合的搜索条件。HAVING 通常与 GROUP BY 子句一起使用。ORDER BY 子句:指定结果集的排序。 检索数据-无条件查询检索整张表:select * from table*号表示选择表中所有列检索表中某些列:select pk_corp,unitcode,unitname from bd_corp从公司基本档案中选择pk_corp,公司编码,公司名称 取消重复值:

9、select distinct ecotype from bd_corp 将bd_corp中重复ecotype值在结果中消除掉.DISTINCT 关键字可从 SELECT 语句的结果中除去重复的行。如果没有指定 DISTINCT，那么将返回所有行，包括重复的行。 检索数据-有条件查询有条件查询就是WHERE 子句,指定用于限制返回的行的搜索条件。算术运算符:算术运算符在两个表达式上执行数学运算，这两个表达式可以是数字数据类型分类的任何数据类型。 +（加）。-（减）。*（乘）。/（除）。%（模）模的含义是除法的整数余数 如:select chinese+english as allscore f

10、rom score where name=a将a的语文成绩和英语成绩相加得到总分.其中 as 是用于为结果集列指定别名 比较运算符: 比较运算符测试两个表达式是否相同 ,可以用于数值,字符串,日期等=（等于） （大于） =（大于或等于） =（小于或等于） （不等于） 如:select name from score where chinese60将语文成绩不及格的查找出来. 检索数据-有条件查询逻辑运算符:逻辑运算符对某个条件进行测试，以获得其真实情况。逻辑运算符和比较运算符一样，返回带有 TRUE 或 FALSE 值的布尔数据类型 运算符 含义AND 如果两个布尔表达式都为 TRUE，那么就

11、为 TRUE。IN 如果操作数等于表达式列表中的一个，那么就为 TRUE。LIKE 如果操作数与一种模式相匹配，那么就为 TRUE。 NOT 对任何其它布尔运算符的值取反。OR 如果两个布尔表达式中的一个为 TRUE，那么就为 TRUE。查询空值: select * from bd_corp where taxcode is nullselect * from bd_corp where taxcode is not null搜索串匹配:select * from bd_corp where unitname like 正航%select * from bd_corp where unitna

12、me like %正航%select * from bd_corp where unitname like %正航查找一组值:select pk_corp,unitcode,unitname from bd_corp where pk_corp in(1001,1003)查找pk_corp等于1001或者等于1003的公司名和编号 检索数据-子查询IN子查询通过 IN（或 NOT IN）引入的子查询结果是一列零值或更多值。子查询返回结果之后，外部查询将利用这些结果。 select * from hi_psndoc_edu where pk_psndoc in (select pk_psndoc

13、 from bd_psndoc where psnname=木普凤)查找员工木普凤的学历子集信息EXISTS子查询使用 EXISTS 关键字引入一个子查询时，就相当于进行一次存在测试。外部查询的 WHERE 子句测试子查询返回的行是否存在。子查询实际上不产生任何数 据；它只返回 TRUE 或 FALSE 值。 select * from hi_psndoc_edu where exists (select a.pk_psndoc from bd_psndoc a where a.pk_psndoc = hi_psndoc_edu.pk_psndoc and a.psnname =木普凤)此语句

14、等价与上面的IN子查询. 检索数据-多表连接查询内连接 inner joinselect 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 查询结果返回人员编码、人员姓名、学历、学校内连接指的是两个表中结果集的交集,如图: 工作信息 学历子集 学历子集 检索数据-多表连接查询左连接 left outer joinselect psn.psncode,psn.psnname,edu.

15、education,edu.schoolfrom bd_psndoc as psn left outer join hi_psndoc_edu as edu on psn.pk_psndoc=edu.pk_psndoc 左外连接首先要确保左边的表数据是完全的 工作信息 工作信息 学历子集检索数据-多表连接查询右连接 rigth outer joinselect psn.psncode,psn.psnname,edu.education,edu.schoolfrom bd_psndoc as psn right outer join hi_psndoc_edu as edu on psn.pk_

16、psndoc=edu.pk_psndoc 右外连接和左外连接正好相反,首先要确保右边表的数据完整,左边表的数据 检索数据-连接查询Pk_psndoc psncode psnnameA 张颖 郭凯 付继红工作信息 Pk_psndoc 学历 学校A 本科 北京大学 专科 北京医专 研究生 清华大学学历子集思考：针对以下示例数据四种连接方式返回结果是怎样的？ 检索数据-排序查询语法 ORDER BY order_by_expression ASC | DESC ,.n 参数order_by_expression指定要排序的列。可以将排序列指定为列名或列的别名（可由表名或视图名限定）和表达式，或者指定

17、为代表选择列表内的名称、别名或表达式的位置的负整数。 可指定多个排序列。ORDER BY 子句中的排序列序列定义排序结果集的结构。ORDER BY 子句可包括未出现在此选择列表中的项目。然而，如果指定 SELECT DISTINCT，或者如果 SELECT 语句包含 UNION 运算符，则排序列必定出现在选择列表中。 此外，当 SELECT 语句包含 UNION 运算符时，列名或列的别名必须是在第一选择列表内指定的列名或列的别名。 说明 在 ORDER BY 子句中不能使用 ntext、text 和 image 列。ASC指定按递增顺序，从最低值到最高值对指定列中的值进行排序。DESC指定按递

18、减顺序，从最高值到最低值对指定列中的值进行排序。空值被视为最低的可能值。select pk_corp,unitcode,unitname from bd_corp order by pk_corp将结果集按照pk_corp从小到大排序,反之加上 desc 检索数据-计算查询语法 GROUP BY group_by_expression ,.n 参数group_by_expression是对其执行分组的表达式。group_by_expression 也称为分组列。group_by expression 可以是列或引用列的非聚合表达式。在选择列表内定义的列的别名不能用于指定分组列。 说明 text

19、、ntext 和 image 类型的列不能用于 group_by_expression。 SELECT sex, count(pk_psnbasdoc) FROM bd_psnbasdoc GROUP BY sex常用的列函数:MAX:取某列最大值MIN:取某列最小值AVG:取某列平均值COUNT:返回集合中项目的数量 插入数据基本语法: Insert into 表名 （字段列表） values（值列表）|select_statement 如:Insert into bd_defdoc(doccode,docname,docsystype,dr,pk_corp,pk_defdoc,pk_def

20、doc1,pk_defdoclist,sealflag,ts) Values(CM001,固定期限、,0,0,0001,0001AA1000000000120W,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 语句既可以一次

21、修改一行数据，也可以一次修改许多行，甚至可以一次修改表中的全部数据。 Update 语句使用 Where 子句指定要修改的行，使用 Set 子句给出新的 数据。新数据可以是常量， 也可以是指定的表达式，还可以是使用 From 子句来自其他表的数据。 例子: Update bd_psnbasdocSet sex=男 Where psnname=黄红 删除数据基本语法:Delete From table_nameWhere search_conditions删除数据使用 Delete 语句。Delete 语句可以一次从一个表中删除一条或者多条数据行。 例子: Delete bd_corp Wher

22、e unitcode like 1010% 在删除表中的全部数据时，还可以使用 Truncate Table 语句。Truncate Table 语句和 Delete 语句都可以将表中的全部数据删除，但是，两条语 句又有不同的特点。当用户使用 Delete 语句删除数据时，被删除的数据 要记录在日志中。并不将对数据的变化记录在日志中。因此，使用 Truncate Table bd_corp语句删除记录的速度快于使用 Delete from bd_corp语句删除表中记录的速度。Truncate 是DDL语句，DDL语句执行后，隐含地提交一个事务，而Delete是DML语句。 练习题1. 查询公

23、司编码为001的公司PK值.公司基本档案表2. 按照公司编码对bd_corp表进行倒序排序.公司基本档案表3. 取公司编码列值包含001的记录 公司基本档案表4. 取出不重复的公司简称. 公司基本档案5. 如何找出公司简称相同的记录. 公司基本档案6. 找出人员编码psncode=0001的工作信息7. 将公司编码=001的公司简称更改为原简称+”new” NC产品中的SQL常见SQL 问题SQL基础 NC中间件SQL 翻译引擎NC的中间件SQL 翻译引擎在NC的EJB层，存在着一个SQL翻译引擎。此SQL翻译引擎的主要工作是把前前端应用发起的MS SQL Server的SQL语句翻译成可以在

24、底层数据库运行的语句。此翻译工作主要体现在SQL函数的翻译上。注意1 ： 并非所有的SQL语句都能正确翻译成可以在底层数据库上可以运行的SQL，导致一些非常有用的函数和语法并不能使用。例如 Update From ， Merger Into .语法， ISDate函数等。 NC中间件SQL 执行工具NC中间件SQL 执行工具：位置：NC_HOME/bin/dbtRunSQL.bat。使用方式: 在文件dbtRunSQL.ini中书写需要执行的SQL ，按照一行一条完整的SQL解析执行。配置完成之后，在命令行提示符下，执行： dbtRunSQL.bat dsName./dbtRunSQL.sh

25、dsName NC产品中的SQL常见SQL 问题SQL基础 常见问题问题1：含有层级关系的树形参照无法显示。原因分析 极有可能是层级关系字段出现循环引用导致。判定方法 Oracle Connect by 语法。例如： 常见问题问题2：倒入的数据日期存在格式的问题。原因分析 出现2009-02-31或者长度不够10位的非法日期字符串。判定方法 SQL Server中的IsDate函数。在Oracle中请使用自定义IsDate函数。 常见问题问题3：使用其他表中的数据快速更新目标表中的数据。方法：Sql Server中使用 Update From.语句. DB2和Oracle中使用: Merge

26、into Using 语句. 举例： MERGE INTO archive arUSING (SELECT activity, description FROM activities) acON (ar.activity = ac.activity)WHEN MATCHED THEN UPDATE SET description = ac.descriptionWHEN NOT MATCHED THENINSERT (activity, description) VALUES (ac.activity, ac.description) 常见问题问题4：NULL值的比较。问题根源：NULL本身代

27、表不确定的内容。即，NULL既可能代表1 也可能代表3， 正是由于NULL的不确定性，故NULL和其他任何值进行比较永远返回False. 即使两个NULL值进行比较。对待NULL值各数据库的奇特之处:SQL SERVER: NULL值和空字符串区分对待。ORACLE: 把空字符串看作是NULL对待。 常见问题问题5：实现 动态 SQL LIKE 功能。select pk_corp, innercode from bd_corp a where exists (select innercodefrom bd_corp b where b.pk_corp=3586 and locate(b.inn

28、ercode, a.innercode) = 1);此功能实现的功能类似于:like abc%,当然根据是like %abc 或者是like %abc%,只需要更改一索引值以及关系运算符号即可.上面的是针对DB2的，对于SQL Server，oracle只需要替换相应的locate函数即可！ 常见问题问题6：行号，获取前N行数据SQL SERVER 2 0 0 5select top 3 row_number() over(order by bd_psndoc.psncode asc), bd_psndoc.psncodefrom bd_psndocOracleselect bd_psndoc

29、.*, rownum from bd_psndocDB2 select bd_org.orgname, bd_org.orgcode, row_number() over (order by orgcode asc) as rownum from bd_org fetch first 1 0 rows only 常见问题问题6：跟踪NC中执行的SQL。最简单的方法：启动SQL Server的SQL Profiler ,连接到指定的数据库，新建一个跟踪，在NC中进行数据查询，更新，删除等操作后， SQL Profiler 会把目标数据库中执行的SQL 抓取出来。方便BUG跟踪，理解NC后台业务。 常见问题问题7：IN 和 Exists 子句可以使用INNER JOIN 替代不要迷信In的效率低Exists的效率高。数据库中没有绝对的秘籍或者参数，能够彻底地解决某个问题。问题要分场合。