高级审计培训SQL入门讲义.ppt

,SQL Server 2000,本机IP：02,基本概念,查询语句,主要内容,数据库类型 实体关系模型 数据库基本知识,SQL server 2000 安装与使用 创建数据库、表和字段 Select语句、连接、分组和函数 SQL server数据导入/导出工具 AO的SQL 语句操作,第一部分基本概念 一、数据库是什么,数据库是相互关联的数据的集合。 具有较小的数据冗余， 可供多个用户共享， 具有较高的数据独立性， 具有安全控制机制， 能够保证数据的安全、可靠， 允许并发地使用数据库，能有效、及时地处理数据， 能保证数据的一致性和完整性。,基本概念,实体关系模型,数据的三种范畴,现实,信息,电脑,抽取,存储,实例化,数据模型是用来抽象、表示和处理现实世界中的数据和信息的。即，数据模型就是对现实世界的模拟。（如：数据库、文件、文档）,基本概念,数据库关键概念,实体：客观存在并可以相互区分的客观事物或抽象事件称为实体。 属性：描述实体的特性。如职工的职工号，姓名，性别，出生日期，职称等。 关键字：如果某个属性或属性组合的值能唯一地标识出实体集中的每一个实体，可以选作关键字。 联系：实体集之间的对应关系称为联系，它反映现实世界事物之间的相互关联,基本概念,联系的三种类型,1）一对一联系（1:1） 2）一对多联系（1:n） 3）多对多联系（m:n）,基本概念,二、数据模型,为了准确地反映事物本身及事物之间的各种联系，数据库中的数据必须有一定的结构。模型结构包括: 1、层次模型、 2、网状模型、 3、关系模型、 4、面向对象模型 数据模型还包括：数据操作和完整性,基本概念,数据库发展趋势,后关系型数据库XMLSQL 审计数据整合,基本概念,三、常见关系型数据库,SQL server Oracle SyBase DB2 Access,基本概念,数据库系统的组成,DBMS DB DBA APP SYSTEM,基本概念,第二部分：SQL server 2000安装,企业管理器 查询分析器 数据库,SQL DB,数据库数据查询过程,提交查询语句,返回查询结果,第三部分 SQL基本知识 3.1 SQL的动词,3.2 SQL Server注释,行内注释：- 块注释：/* */,3.3 SQL数据类型,1. 数值型 :int,numeric,float 2. 字符串型:char,varchar 3. 日期、时间型:datetime 4. 货币类型:,3.3.1 数值型,定长（准确表达） 整数： INT（全字长32位），SMALLINT（半字长） 小数： NUMERIC（p,q）或DECIMAL（p,q）， 其中：p为数字位长度，q：小数位长度。 浮点（近似）数： FLOAT、REAL等,3.3.2.字符串型,CHAR（n）:定长存储，n8000 VARCHAR（n）：不定长存储（按实际长度存储），长度最大不超过n,3.3.3. 日期时间型,DATE TIME （8字节）：年月日时分秒毫秒 （例：2001/08/03 10:30:00 000 ） SmallDateTime（4字节）：年月日时分（例： 2001/08/03 10:30:00 ）,4. 货币类型,表示正的或负的货币值 Money：精确到货币单位的千分之十。存储大小为 8 个字节。 Smallmoney：精确到货币单位的千分之十。存储大小为 4 个字节。 限制到小数点后 4 位。 货币数据不需要用单引号 () 括起来。但是，货币数值之前必须带有适当的货币符号。例如，若要指定 100 英镑，请使用 100。,3.4 数据定义功能,数据库的定义 基本表的定义与删除,3.4.1数据库的定义,create database DB_NAME ON ( NAME=数据文件逻辑名, FILENAME=物理存储位置, SIZE=初始大小, MAXSIZE=最大大小, FILEGROWTH=增长方式) LOG ON 参数同上,数据库的定义例,CREATE DATABASE XShGL ON ( NAME= XShGL_data, FILENAME=c:mssqldataXShGL.mdf, SIZE=5MB, MAXSIZE=15MB, FILEGROWTH=20%) LOG ON (NAME=XShGL_log, FILENAME=c:mssqldataXShGL.ldf, SIZE=2MB, MAXSIZE=5MB, FILEGROWTH=1MB),CREATE TABLE （列定义 | 约束定义）,（1）表名：表达本表应用语义的字串。 （2）列定义（单列定义序列）：单列定义 ，单列定义， 单列定义由列名、列类型、尺寸、列取值约束，列缺省值子句组成（用空格分隔）,3.4.2表定义,约束定义, 列取值约束 Not null default(值) 表主码约束 Primary key （） 外码引用约束（外码主表数据引用） FOREIGN KEY （）REFERENCES （）,例：定义学生表 (学号，姓名，性别，年龄、所在系）,CREATE TABLE Student ( iSno int primary key, cName char(10) not null, iSex int default(0), dtBirthDay DateTime , cDept varchar(64) ),定义课程表 （课程号，课程名，学分，学期）,Create Table Course ( iNo int not null, cName varchar(32) not null, nCredit numeric(6,1) not null, -学分 iSemester int, -学期 primary key (iCno) ),学生修课成绩表（学号，课程号，成绩）,Create Table Score ( iSNo int not null, iCNo int not null, nGrade numeric(4,1) default(0), primary key ( iSno,iCno ), foreign key ( iSno ) references Student(iSno), foreign key ( iCno ) references Course(iCno) ),练习：,1、创建财务数据库：CWDB,2、创建科目代码表：KMB,3、创建凭证主表 ：PZ,4、创建凭证明细表:PZMX,参见练习一,3.5 删除数据库,从企业管理器的相关对象上右键，选择删除即可。 或者使用SQL语句,3.6 删除表,从企业管理器对象树选择需要删除的表，右键，选择【删除】 或者SQL Drop table Student;,3.7 追加、修改、删除字段,从企业管理器对象树选择需要编辑的表，右键，选择【设计表】，进行字段修改。 或者SQL alter table student add myCol int; alter table student alter column myCol varchar(12); alter table student drop column myCol;,4.1 基本表数据的插入,一单行数据的插入,之,第四部分 SQL 语句,一单行数据的插入,插入单行记录的INSERT语句的格式为： INSERT INTO VALUES （值表） 功能：新增一个符合表结构的数据行，将值表数据按表中列定义顺序或列名表顺序赋给对应列名。,说明：,1. 列名表与值表：列名必须在表中已定义，值可取常量或NULL。 2. 赋值规则： (1)值与列名按顺序对应，要求值类型与列数据类型一致。 (2)对语句中无值对应的列名赋NULL。 3. 如果INTO子句中没有指明列名，则新插入记录的值的顺序必须与表中列的顺序一致，且每一列均有值（可为空）。,例1将新生记录（95020，陈冬，男，信息系，18岁）插入到Student表中,INSERT INTO Student VALUES （1，陈冬，0，1976-03-20,IS ）,例2在SC表中插入一新记录，成绩暂缺,INSERT INTO SCore(iSno, iCno,iGrade) VALUES(1,1,100),注： 此时必须列出列名（因为有缺省） SC中的Grade必须允许为0 实际插入的值为：(95020,1，0),练习二,4.2 数据查询功能,1.简单SQL 语句,SELECT （需要哪些列） FROM （从哪些表） WHERE （根据什么条件）,查询过程,Select PZH,KMDH,KMMC,DF from ACCTTwo where DF0,2.查询所有数据,Select * from AcctOne,3.选择需要的列,select PZH,FSRQ,SHY from AcctOne,4.给列取别名（重命名）,select PZH as 凭证号, FSRQ as 发生日期, SHY as 审计员 from AcctOne,5.选择数据行,使用Where条件判断句,比较运算 1.数值类型的数据判断：=,=, 2.日期类型判断：=,=, 3.字符串判断：like,=,not like 4.集合判断:in,not in 5.空值判断:is null 6.确定范围:between and ,6.数值条件： 查询借方金额大于10000的分录,select * from accttwo where JF=10000,7.日期条件： 查询2005年上半年的凭证,Select * from acctOne where FSRQ2005-7-1,一般形式为： 列名 NOT LIKE 匹配串 匹配串类型：匹配串可以是字符串常量，也可以含有通配符。 通配符种类： %（百分号）：匹配0个或多个字符。 _（下划线）：匹配一个字符。 ：匹配括号中的字符 ：不匹配括号中的字符,8.字符匹配 （LIKE）,8.1字符串条件： 查询摘要为个人委托贷款的分录,select * from AcctOne where ZY=个人委托贷款,8.2查询摘要含有贷款的分录,select * from AcctOne where ZY like %贷款%,8.3 提高: 查询摘要含有个人贷款或者个人委托贷款的分录,select * from AcctOne where ZY like %个人,个人委托贷款%,8.4 摘要含有数字分录,select * from AcctOne where ZY like %0-9%,8.5摘要含有字母的分录,select * from AcctOne where ZY like %a-z,A-Z%,9.集合判断条件 查询凭证号为6，9，11，15的凭证的详细信息,Select * From AcctTwo Where PZH in (6,9,11,15),10.空值判断： 查询出所有的摘要为空的凭证,Select * From AcctOne Where ZY is null,查询出所有的摘要为非空的凭证,Select * From AcctOne Where ZY is not null,11.值区间判断： 查询凭证号在1015之间所有凭证,Select * from AcctOne where PZH between 10 and 15,注意：结果含10和15号凭证,12.选择数据行,逻辑运算 1.并且：and 2.或者：or 3.取反：！,12.1逻辑操作并且： 2005年下半年摘要内含有贷款的凭证,select * from AcctOne where ZY like %贷款% and FSRQ=2005-7-1,12.2 逻辑运算或者 所有发生额大于10000的分录,select * from AcctTwo where JF=10000 or DF=10000,12.3 删除结果集的重复行,Distinct,查看余额表余额方向的样本 select distinct JDFX from KMYE,13对查询结果排序,可对查询结果进行排序 排序子句为： ORDER BY , ASC | DESC 说明：按进行升序（ASC）或降序（DESC）排序。,排序举例： 按照借方金额进行排序,Select * From AcctTwo Order by JF -从小到大排序,Select * From AcctTwo Order by JF Desc -从大到小排序,14.获得查询的前n行,获取借方金额前10名的分录 Select top 10 * from AcctTwo Order by JF desc,15. SQL提供的计算函数,COUNT( * )：统计表中元组个数 COUNT()：统计本列非空列值个数 SUM()： 计算列值总和（必须是数值型列） AVG()： 计算列值平均值（必须是数值型列） MAX()：求列值最大值 MIN()：求列值最小值,15.1.查询记录数,Select count(*) as 记录数 from AcctOne,15.2 求最大值和最小值,Select max(JF) as 最大值,min(JF) as 最小值 from AcctTWO,Select max(JF) as 最大值,min(JF) as 最小值 from AcctTWO Where JF0,Select * into Temp1 From AcctTWO Where YF=1,16 查询结果保存到新表,Select * into Temp2 From AcctTWO Where YF=2,例：1季度凭证详细信息保存到3张临时表,Select * into Temp3 From AcctTWO Where YF=3,Select YF,PZH,KMDH, JF,DF from temp1 Union Select YF,PZH,KMDH, JF,DF from temp2 Union Select YF,PZH,KMDH, JF,DF from temp3,17 多个表合并,例：将1月、2月凭证表合并成一张表,五.对查询结果分组,作用：可以分别对每张凭证进行借贷平衡计算。 对每一组数据进行求最大值，最小值，平均值，求和，计数等。 分组语句的一般形式： GROUP BY HAVING ,例：请检算凭证表的借贷是否平衡。 分析： （1）检查全部数据是否平衡 （2）检查各月是否平衡 （3）检查各凭证是否借贷平衡 （4）显示借贷不平衡的凭证的详细信息,（1）检查全部数据是否平衡,Select sum(JF) as 借方合计，sum(DF) as 贷方合计 From AcctTwo,（2）检查各月是否平衡,select YF,sum(JF) as 借方, sum(DF) as 贷方 from AcctTwo group by YF Having sum(JF)!=sum(DF) Having abs(sum(JF)-sum(DF)0.001,直接显示有哪些借贷不平衡的月份,select YF,sum(JF) as 借方, sum(DF) as 贷方 from AcctTwo group by YF,浮点数不是精确数，不能直接使用等号进行比较,select YF, PZH ，sum(JF) as 借方, sum(DF) as 贷方 from AcctTwo group by YF，PZH Having abs(sum(JF)-sum(DF)0.001,（3）检算每张凭证借贷平衡情况,Select * From AcctTWO Where YF=? And PZH=?,（4）显示借贷不平衡的凭证的详细信息,4.8 条件筛选和分组,1月份各科目余额,Select KMDH,sum(JF),sum(DF) From AcctTwo Where YF=1 Group by KMDH,先筛选，后分组，再筛选，再选择,条件筛选和组筛选,验证分录数大于3条的， 6月份的凭证借贷发生额,Select PZH,sum(JF),sun(DF) From AcctTwo Where YF6 Group by PZH Having count(*)3,4.3 多表连接查询,若一个查询同时涉及两个或两个以上的表，则称之为连接查询。 连接查询是关系数据库中最主要的查询 连接查询包括内连接、外连接和交叉连接等。,理解表连接,Join,例：查看每张凭证的凭证日期，摘要和金额,Select a.YF,z.PZH.a.FSRQ,a.ZY,b.JF,b.DF From AcctOne a Join AcctTwo b On a.YF=b.YF and a.PZH=b.PZH,例：按以下形式显示1季度各月各科目余额（假设各月各科目均有发生额）,（1）一月各科目发生额,Select KMDH,sum(JF) -sum(DF) as 余额 Into tmpYE1 from ACCTTWO where YF=1 Group by KMDH,（2）二月各科目发生额,Select KMDH,sum(JF) -sum(DF) as 余额 Into tmpYE2 from ACCTTWO where YF=2 Group by KMDH,（3）一月各科目发生额,Select KMDH,sum(JF) -sum(DF) as 余额 Into tmpYE3 from ACCTTWO where YF=3 Group by KMDH,（4）合并各月余额表,select a.KMDH, a.余额 as 1月, b.余额 as 2月, c.余额 as 3月 from tmpYE1 a join tmpYE2 b on a.KMDH=b.KMDH join tmpYE3 c on a.KMDH=c.KMDH,例：从学生数据库，查询学生各科成绩，要求显示学生的姓名，科目和成绩,select s.cName ,c.cName,sc.nScore from student s join SCore sc on s.iSNo=sc.iSno join course c on Sc.iCno=c.iNo,4.4外连接,外连接会返回 FROM 子句中提到的至少一个表的所有行，只要这些行符合任何 WHERE 或 HAVING 搜索条件。 分类： LEFT OUTER JOIN 或 LEFT JOIN：左外连接 RIGHT OUTER JOIN 或 RIGHT JOIN：右外连接,1. 使用左外连接,左向外连接运算符 LEFT OUTER JOIN 指明，不管第二个表中是否有匹配的数据，结果将包含第一个表中的所有行。 运算过程： （1）首先将2张表完全连接； （2）在完全连接的表中，保留那些符合条件的数据行，删除不符合条件的数据行。 （3）在完全连接的表中，如果左边表某行与右边表中任何一行都不符合匹配条件，则保留左边表的那一行数据，对应右边表的数据列全部为null。,例：,在学生数据库中查询没有选课的学生,Select s.* From student s left join score sc on s.isno=sc.isno Where sc.isno is null,例,查询没有被学生选学的课程,Select c.* From Course c Left join score sc on c.iCno=sc.iCno Where sc.iCno is null,例：,从某财务数据库xData提取科目表，验证科目表的科目是否完整。 分析: 在该未知财务数据中，没有明确的科目表，需要进行提取。但在凭证明细表中和科目余额表中，均有科目代码和科目名称字段。 因此科目表可以从此两表中提取。两个表提取的科目表到底哪一个涵盖了哪一个？还是互相不涵盖？需要逐一验证。 过程： （1）分别提取科目表KMA，KMB （2）使用外连接，KMA是否存在比KMB多出的科目 （3）使用外连接，KMB是否存在比KMA多出的科目 （4）根据分析结果，选用科目表,（1）分别提取科目表KMA，KMB,Select distinct KMDH,KMMC into KMA from ACCTTWO,select distinct KMDH,KMMC into KMB from KMYE,（2）使用外连接，KMA是否存在比KMB多出的科目,select * from KMA a left join KMB b on a.KMDH=b.KMDH where b.KMDH is null,（3）使用外连接，KMB是否存在比KMA多出的科目,select * from KMB a left join KMA b on a.KMDH=b.KMDH where b.KMDH is null,（4）根据分析结