泛微培训教材：SQL语句基础

1、泛微培训教材：SQL语句基础单击此处编辑母版标题样式单击此处编辑母版标题样式目录泛微软件股份-协同管理解决方案泛微软件股份-新员工入职培训课件n 第第一部分：一部分：SQL语句分类语句分类n 第二部分：SELECT专题n第三部分：数据操纵语言DMLn 第四部分：数据定义语言DDLSQL语句分类语句分类 查询语句SELECT数据操纵语言（Data Manipulation Language,DML）语句INSERTUPDATEDELETE数据定义语言（Data Definition Language,DDL）语句CREATEALTERDROPRENAMETRUNCATE事务控制（Transact

2、ion Control, TC）语句COMMITROLLBACKSAVEPOINT数据控制语言（Data Control Language, DCL）语句GRANTREVOKE目录泛微软件股份-协同管理解决方案泛微软件股份-新员工入职培训课件n第一部分：SQL语句分类n 第二部分第二部分：SELECT专题专题n第三部分：数据操纵语言DMLn第四部分：数据定义语言DDL查询记录查询记录l 基本语法 SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESCSQL语句书写规范语句书写规范lSQL 语句对大小写不敏感l S

3、QL 语句可以写成一行或多行l 关键字不能简写或分开折行l子句通常放在不同的行l缩进用于增强可读性查询记录查询记录示例1 查询所有员工的基本信息示例2 查询所有员工的基本信息，结果：姓名，联系 ，邮箱 select * from hrmresourceselect lastname,mobile,email from hrmresource查询记录查询记录l示例3 对查询结果记性计算：总金额 = 单价*数量-算术表达式-运算符优先级 select danjia*shuliang from formtable_main_2算数运算符算数运算符l算术表达式运算符的优先级-乘法和除法比加法和减法的优

4、先级高-相同优先级的运算符从左到右计算-圆括号用于强制优先计算，并且使语句更清晰* * / + - / + -查询记录查询记录l 示例4 对查询结果的多个字段进行拼接显示SQLserverOracle select deparmentname+(+departmentcode+) from hrmdepartment select deparmentname|(|departmentcode|) from hrmdepartment查询记录查询记录l 示例5 给查询结果指定列明 select lastname as 姓名姓名,email as 邮箱邮箱 from hrmresource过滤查询

5、结果过滤查询结果lWHERE子句中经常使用的操作符 -比较操作符-SQL操作符-逻辑操作符过滤查询结果过滤查询结果r比较操作符操作符操作符说明说明=等于等于或或!=不等于不等于大于大于=大于或等于大于或等于=2014-01-01 and createdate =2014-01-31或者或者Select * from workflow_requestbase where createdate between 2014-01-01 and 2014-01-31过滤查询结果过滤查询结果l示例3 查询系统有效用户l 示例4 查询分部id = 5的有效用户l 示例5查询分部id = 5且登录名以dl开头

6、的有效用户 select * from hrmresource where stauts in(0,1,2,3)select * from hrmresource where stauts in(0,1,2,3)And subcompanyid1 = 5select * from hrmresource where stauts in(0,1,2,3)And subcompanyid1 = 5 and loginid like dl%过滤查询结果过滤查询结果l示例6 查询员工姓名第二字是刚的员工l 示例7 查询哪些有效用户还未设置登录名 select * from hrmresource wh

7、ere lastname like _刚刚%select * from hrmresource where stauts in(0,1,2,3)And loginid is null改变记录顺序改变记录顺序l 改变查询结果显示顺序默认正序 ASC(可缺省)，倒序DESC 单列排序 多列排序 select * from workflow_requestbase order by createdate descselect * from workflow_requestbase order by createdate desc,requestlevel desc排除重复记录排除重复记录l 排除重复

8、记录 关键字：distinct查询某个员工参与过的流程 select distinct requestid from workflow_currentoperator where userid = 员工员工id order by requestid连接查询连接查询r普通连接 内连接（用于返回满足连接条件的记录） 相等连接 不等连接 自连接 外连接（内连接的扩展，不仅会返回满足连接条件的记录，而且会返回不满足连接条件的记录） 左连接（LEFT OUTER JOIN） 右连接（RIGHT OUTER JOIN） 全连接（FULL OUTER JOIN） (+)连接r其他类型连接 连接运算符（用于连

9、接多个SELECT语句的结果） UNION UNION ALL INTERSECT MINUS连接查询连接查询r内连接内连接用于返回满足连接条件的所有记录。默认情况下，在执行连接查询时如果没有指定任何连接操作符，那么这些连接查询都属于内连接。另外，可以通过FROM子句中指定INNER JOIN选项，也可以指定内连接。连接查询连接查询r 内连接-相等连接使用相等比较符（=）指定连接条件的连接查询r 例：显示部门名称为客服部 的员工发起的流程，显示流程标题，创建日期 SELECT requestname,createdate FROM workflow_requestbase a,hrmdepar

10、tment b,hrmresrouce c WHERE a.create = c.id and b.id = c.departmentid AND b.DepartmentName LIKE %客服部客服部%; 连接查询连接查询r 内连接-不等连接在连接条件中使用除相等比较符外的其他比较符的连接查询，主要用于在不同表之间显示特定范围的信息。示例：显示部门名称不是客服部 的员工发起的流程，显示流程标题，创建日期 SELECT requestname,createdate from workflow_requestbase a, (select h.ID from HrmResource h,Hr

11、mDepartment d where h.departmentid = d.id and d.departmentname like %客服部客服部%) c where a.creater != c.id 连接查询连接查询r 内连接-自连接指在同一张表之间的连接查询，主要用在自参照表上显示上下级关系或者层次关系r 例：找到“某某“的上级领导是谁 SELECT b.lastname FROM hrmresource a,hrmresource b WHERE a.managerid=b.id AND a.lastname=某某某某; 连接查询连接查询r外连接外连接是内连接的扩展，它不仅会返回满

12、足连接条件的所有记录，而且还会返回不满足连接条件的记录。r类型左连接右连接全连接r实现语法LEFT|RIGHT|ALL OUTER JOIN(+)连接查询连接查询l示例： 查询所有员工发起流程的总数，从大到小排序 select a.lastname,case when num IS null then 0 when num is not null then num endfrom HrmResource a left outer join (select creater, COUNT(*) as num from workflow_requestbase group by creater) b

13、on a.id = b.createrorder by num desc介绍两个函数介绍两个函数l Case when 示例：显示员工姓名，性别 l NVL( string1, replace_with)。Oracle 特有select lastname ,case when sex=0 then 男男 when sex=1 then 女女 end from HrmResource 分组查询分组查询r 在实际应用中，经常需要把数据划分成几个组，最终统计每个组的数据结果。比如说统计不同部门的员工总数，按工作性质统计员工的平均工资等。r 在关系数据库中，数据分组是通过使用GROUP BY子句，HA

14、VING子句以及分组函数共同实现的。其中，GROUP BY用于指定分组列，HAVING用于限制分组结果，分组函数用于得到统计结果分组查询分组查询r GROUP BY和HAVING SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC分组查询分组查询r 练习：查询某个时间段员工的登录次数，从大到小排序 select relatedid,(select lastname from hrmresource where id= relatedid) as lastname,COUNT(*) as num from Sy

15、sMaintenanceLog where operateitem = 60 and operatedate=2007-07-01 and operatedate100;分组查询分组查询r SUM（） 计算值的总和并返回总数r COUNT（） 计算记录数r AVG（） 返回指定列中的平均值r MIN（） 返回自变量中指定列的最小值r MAX（） 返回自变量中指定列的最大值分组查询分组查询r注意事项：分组函数只能出现在选择列表、GROUP BY子句、HAVING子句和ORDER BY子句中。如果在SELECT语句中同时包含有GROUP BY、HAVING以及ORDER BY子句，那么必须将ORD

16、ER BY子句放在最后如果选择列表包含有列、表达式和分组函数，那么这些列和表达式必须出现在GROUP BY子句中，否则出错当限制分组显示结果时，必须使用HAVING子句，而不能在WHERE子句中使用分组函数，否则出错默认情况下，系统按照GROUP BY子句中指定的列升序排列子查询子查询r子查询就是嵌入在其他SQL语句中的SELECT语句，也称为嵌套查询。r子查询具有以下作用：通过在INSERT或CREATE TABLE语句中使用子查询，可以将源表数据追加到目标表 通过在CREATE VIEW或CREATE MATERIALIZED VIEW中使用子查询，可以定义视图或实体化视图所对应的SELE

17、CT语句通过在UPDATE语句中使用子查询可以修改一列或多列的数据通过在WHERE、HAVING、START WITH子句中使用子查询，可以提供条件值r在DDL语句中使用子查询时，可以带有ORDER BY子句；而在WHERE、SET子句中使用子查询，则不能带有ORDER BY子句。子查询能解决的问题子查询能解决的问题r 哪些商品的售价比”疯狂的石头”高？“疯狂的石头疯狂的石头”的售价是多少？的售价是多少？子查询主查询那些影音商品的售价比那些影音商品的售价比“疯狂的石头疯狂的石头”的售价高？的售价高？子子查询记录查询记录r 练习：查询客服部员工的请假记录 select (select lastn

18、ame from hrmresource where id = resourceid),fromdate,fromtime,todate,totime,case when leavetype=1 then 事假事假 when leavetype=2 then 病假病假 else 假别假别end from bill_bohaiLeave where requestid in (select requestid from workflow_requestbase where creater in (select id from hrmresource where departmentid = (s

19、elect id from hrmdepartment where departmentname =客服部客服部) )子查询的应用子查询的应用r 除了使用在嵌套式SELECT语句之中外，子查询还可以被放在 CREATE VIEW 语句中、CREATE TABLE 语句、UPDATE 语句、INSERT 语句的 INTO 子句和 UPDATE 语句的 SET 子句中。 子查询在子查询在Update中的应用中的应用r 将与任盈盈同岗位的员工的工资改为与任盈盈完全相同。UPDATE Employee SET(ESal)=(SELECT ESal FROM Employee WHERE EName=任

20、盈盈任盈盈)WHERE EJob=(SELECT EJob FROM Employee WHERE EName=任盈盈任盈盈);表间数据转换表间数据转换r 可能存在很多原因需要将某一表中的内容复制到另外一个表： 引入老式系统（legacy system）中的数据 将汇总加载到数据仓库 将关系数据复制到平面表以快速访问表的维护表的维护r 在当前表的基础上创建新表CREATE TABLE new_table_name AS(SELECT statement);表间数据转换表间数据转换r 运用INSERT进行数据转换INSERT INTO table_name (SELECT statement);

21、单击此处编辑母版标题样式单击此处编辑母版标题样式目录泛微软件股份-协同管理解决方案泛微软件股份-新员工入职培训课件n第一部分：SQL语句分类n 第二部分：SELECT专题n 第三部分：第三部分：数据操纵语言数据操纵语言DMLn第四部分：数据定义语言DDL插入记录插入记录-INSERTr 基本语法 INSERT INTO table (column , column.) VALUES (value , value.);插入记录插入记录r 示例INSERT INTO HRMRESOURCE VALUES( 7369, 20, 丘处机丘处机, 副经理副经理, 7902, TO_DATE(17/12/

22、1995,DD/MM/YYYY), 800);插入记录插入记录r 如何插入包含空值的记录?方法一：INSERT INTO HRMRESOURCE VALUES( 7369, 20, 丘处机, NULL, 副经理, 7902, TO_DATE(17/12/1995,DD/MM/YYYY), 800);插入记录插入记录r 如何插入包含空值的记录? 方法二：INSERT INTO EMPLOYEE(EID,DID,EName,EJob,EManager,EHireDate,ESal)VALUES( 7369, 20, 丘处机丘处机, 副经理副经理, 7902, TO_DATE(17/12/1995,

23、DD/MM/YYYY), 800);更新记录更新记录-UPDATEr 基本语法 UPDATE table SET column= value, column = value, . WHERE condition; 更新记录更新记录r 示例更新姓名为张翠山的员工的手机号码 UPDATE HRMRESOURCE SET EMOBILE=98322334 WHERE LASTNAME=张翠山张翠山;更新记录更新记录r 示例2更新姓名为张翠山的员工的 号码为空值 UPDATE HRMRESORUCE SET MOBILE=NULL WHERE LASTNAME=张翠山张翠山; UPDATE HRMRESORUCE SET MOBILE= WHERE LASTNAME=张翠山张翠山;删除记录删除记录-DELETEr 基本语法 DELETE FROM Table WHERE condition;删除记录删除记录r 删除满足指定条件的行删除安全级别小于20的人员DELETE FROM HRMRESOURCE WHERE SECLEVEL 20删除记录删除记录r 删除所有行 在没有指定标准的情况下删除记录 清空表DELETE FROM HRMRESOURCE;单击此处编辑母版标题样式单击此处编辑母版标题样式目录泛微软件股份-协同管理解决方案泛微软件股份-新员工入职培训课件n 第一部