MES开发与应用 课件 1.2.3 数据库常用介绍

——基础语法、基础操作4.2.3数据库常用介绍查询数据更新数据使用视图管理事务处理了解SQL使用存储过程知识回顾学习目标通过本节学习可以：重点学习如何查询数据重点学习如何更新数据掌握视图的使用掌握存储过程的使用了解如何管理事务处理01了解SQL了解SQLSQL（发音为字母S-Q-L或sequel）是结构化查询语言（StructuredQueryLanguage）的缩写。SQL是一种专门用来与数据库沟通的语言。SQL有如下的优点：SQL不是某个特定数据库供应商专有的语言。几乎所有重要的DBMS都支持SQL，所以学习此语言使你几乎能与所有数据库打交道。SQL简单易学。它的语句全都是由有很强描述性的英语单词组成，而且这些单词的数目不多。SQL虽然看上去很简单，但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。了解SQL使用PLSQLDeveloperPLSQLDeveloper工具是Oracle常用的数据库管理工具。在安装PLSQLDeveloper之前，要先安装好Oracle客户端。Oracle客户端的安装很简单，下载解压后放到某个目录下即可。安装Oracle客户端使用PLSQLDeveloperPLSQLDeveloper的安装很简单，按照提示安装即可。安装完后，打开PLSQLDeveloper，打开配置选项窗口，按下面说明进行配置，配置完后关闭并重新打开PLSQLDeveloper。安装和配置PLSQLDeveloper指定Oracle客户端的目录指定oci.dll的目录，在Oracle客户端目录下使用PLSQLDeveloper连接数据库需要知道数据库的IP、端口、服务名以及用户名和密码。连接数据库用户名密码ip:port/service_name02查询数据查询数据_检索数据SELECT

<列名>

FROM

<表名>

WHERE

<条件>;该SQL包含了SELECT、FROM以及WHERE三个子句。SELECT：子句用于指定要查询的列名称。FROM：子句指定查询的表名称。WHERE：子句指定过滤数据的条件。一般来说，以分号作为单个SQL语句的结尾，但不是强制的。如果有多个SQL语句，可以使用分号区分不同的SQL，所以加上分号是一个好习惯。基本查询语法查询数据_检索数据SELECTdnameFROMdept;把要查询的列放在SELECT子句后面，非常简单。检索单个列查询数据_检索数据SELECTdeptno,dname,locFROMdept;指定检索多个列，列与列之间使用逗号分隔开。如果检索所有列，可以显示指定所有字段，也可以只指定*，*表示所有字段，如下所示：SELECT*FROMdept;检索多个列查询数据_检索数据SELECTjobFROMemp;如果希望去掉上面重复的数据，比如SALESMAN只显示一个，可以加上DISTINCT关键字去重：SELECTDISTINCTjobFROMemp;去除重复数据查询数据_检索数据SELECTdeptnoASno,dnameASname,locFROMdept;当字段命名较长时，可以使用别名简化。如果是表达式，可以使用别名命名，比如a+bASsalary。当从多张表查询数据时，如果有相同的字段，可以使用别名进行区分。指定别名查询数据_排序数据SELECT*FROMempORDERBYjob;通过使用ORDERBY子句对数据排序。如果不排序，数据一般将以它在底层表中出现的顺序显示，这有可能是数据最初添加到表中的顺序。排序数据查询数据_排序数据SELECT*FROMemptORDERBYsal,ename;可以指定按多个列排序，使用逗号隔开。首先会按前面的字段排序，如果值相同则按后面的字段排序。按多个列排序查询数据_排序数据SELECTempno,ename,job,mgr,hiredate,sal,commFROMempORDERBY2,3;SELECT子句先于ORDERBY执行，所以在排序时可以得到字段所在位置然后进行排序。按列的位置排序查询数据_排序数据SELECTempno,sal,ename,job,mgrFROMempORDERBYsalASC,enameDESC;ASC表示升序，默认排序方式，DESC表示降序。如果想在多个列上进行降序排序，必须对每一列指定DESC关键字。指定排序方向查询数据_过滤数据比较运算符查询数据_过滤数据SELECT*FROMempWHEREjob='MANAGER';要查询某个值的数据，可以使用=运算符判断。匹配过滤一般把字段放左边，具体值放右边字符串类型要放到引号内，即使这个字串符是数值查询数据_过滤数据SELECT*FROMempWHEREjob<>'SALESMAN';SELECT*FROMempWHEREjob!='SALESMAN';可以使用<>也可以使用!=，不过有的数据库不支持!=，建议使用<>。不匹配过滤查询数据_过滤数据SELECT*FROMempWHEREsal>3000;SELECT*FROMempWHEREsal<1800;SELECT*FROMempWHEREsal>2000ANDsal<3000;SELECT*FROMempWHEREsal>=1250ANDsal<=1600;SELECT*FROMempWHEREsalBETWEEN1250AND1600;范围过滤..between..and....>=..and..<=..查询数据_过滤数据SELECT*FROMempWHEREcomm=NULL;SELECT*FROMempWHEREcommISNULL;SELECT*FROMempWHEREcommISNOTNULL;SELECT*FROMempWHEREcomm<>300;当碰到NULL值时，会被判定为不满足条件，所以这里不会查出NULL值的数据。需要添加下面的条件才能把NULL的数据查出来：SELECT*FROMempWHEREcomm<>300ORcommISNULL;空值过滤这种方式无法查询为空值的数据针对空值，需要使用ISNULL或ISNOTNULL不会把查询出包含NULL的数据查询数据_过滤数据SELECT*FROMempWHERENOT(job='SALESMAN’);WHERE子句中的NOT操作符有且只有一个功能，那就是否定其后所跟的任何条件。这里当匹配条件时返回TRUE，然后NOT取反得到FALSE，这样匹配条件的数据被过滤了，查出的是未匹配的数据。NOT运算符查询数据_过滤数据SELECT*FROMempWHEREjob='SALESMAN'ANDsal>1300;SELECT*FROMempWHEREjob='SALESMAN'ORdeptno=30;AND运算符在其两侧的查询条件都成立时整个查询条件才成立，其意思相当于“并且”。OR运算符在其两侧的查询条件有一个成立时整个查询条件都成立，其意思相当于“或者”。AND、OR运算符需两侧条件同时成立仅需其中一个条件成立查询数据_过滤数据SELECT*FROMempWHEREjobIN('CLERK','PRESIDENT’);SELECT*FROMempWHEREjob='CLERK'ORjob='PRESIDENT';取出CLERK和PRESIDENT的所有数据，功能上与OR是一样的。当值特别多时，相比较OR而言，使用IN书写会更简洁。IN运算符得到的结果是一样的查询数据_过滤数据SELECT*FROMempWHEREsal+comm=1900;可以拿字段进行计算形成一个新的字段。既可以在WHERE子句也可以在SELECT子句对字段进行计算。SELECTsal+comm,t.*FROMemptWHEREsal+comm=1900;计算字段查询数据_过滤数据SELECT*FROMempWHEREename||','||job='ALLEN,SALESMAN';可以使用||把字符串拼接起来形成一个新的字段，有些数据库使用+拼接。既可以在WHERE子句也可以在SELECT子句对字段进行拼接。SELECTename||','||job,t.*FROMemptWHEREename||','||job='ALLEN,SALESMAN';

拼接字段查询数据_模糊匹配在条件中可以使用通配符%或_进行模糊匹配，%表示匹配多个任意字符，_只能匹配一个任意字符。SELECT*FROMempWHEREenameLIKE'J%’;查出J开头的所有数据，不管J后面有多少个字符。SELECT*FROMempWHEREenameLIKE'%N';查出以N结尾的所有数据，不管N前面有多少个字符都能匹配出来。SELECT*FROMempWHEREenameLIKE'%AR%';查出只要包含了AR的所有数据，不管AR前后有多少个字符。模糊匹配查询数据_聚合数据SELECTCOUNT(*),SUM(sal),MIN(mgr),MAX(mgr),AVG(sal)FROMemp;聚合函数用于对所有数据或分组数据进行计算。COUNT统计行数，SUM汇总，MIN统计最小值，MAX统计最大值，AGV计算平均值；聚合函数会忽略NULL值，在使用COUNT的时候要特别注意，如果指定的是*不会忽略NULL值，如果指定的是某个字段，该字段有NULL会被忽略，意味着所在行不会被统计。SELECTCOUNT(*),COUNT(mgr)FROMempt;聚合函数其中一行的MRG是NULL不会被统计查询数据_聚合数据可以使用GROUPBY子句对数据进行组，GROUPBY后面指定分组字段，会把字段值相同的视为一组，然后可以基于分组进行统计。如果分组列中包含具有NULL值的行，NULL值将单独划分为一组。SELECTjob,COUNT(*),SUM(sal),AVG(sal)FROMempGROUPBYjob;分组数据查询数据_聚合数据在分组后，可以使用HAVING子句对分组统计进一步过滤。HAVING是基于分组计算过滤的，必须结合GROUPBY子句一起使用。SELECTjob,COUNT(*),SUM(sal),AVG(sal)FROMempGROUPBYjobHAVINGCOUNT(*)>2;该示例统计出了每个分组超过两行的数据：过滤分组03更新数据更新数据_插入数据INSERTINTO<表名>(字段1,字段2,…)VALUES(值1,值2,…);INSERTINTOemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7935,'XIAOLI','CLERK',7566,to_date('1985-1-23','yyyy-mm-dd'),1200,NULL,20);在表名旁边指定字段名称，在VALUES子句中指定字段对应值。如果对所有字段赋值，可以不用指定这些字段名称，只要在VALUES子句指定列值即可。如果仅对部分字段赋值，那么就必须指定这些字段名称。对于设置为非空的字段，必须要赋值。INSERTINTOemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)SELECT7935,'XIAOLI','CLERK',7566,to_date('1985-1-23','yyyy-mm-dd'),1200,NULL,20FROMdual;可以把查询语句的数据插入到表中，意味着可以通过这种方式把多行的数据插入到表中。插入数据更新数据_修改、删除数据UPDATE<表名>SET字段1=值1,字段2=值2,字段3=值3,…WHERE;UPDATEempSETename='XIAOLI2'WHEREempno=7935;修改满足条件的数据。DELETEFROM<表名>WHERE...;DELETEFROMemp;删除所有数据。DELETEFROMempWHEREempno=7935;删除满足条件的数据。注意：为了防止误修改和删除，在执行前要仔细检查，有条件的话要先做备份。修改、删除数据04使用视图更新数据_使用视图视图并不存放数据，相当于一张虚拟表，里面包含了一段SQL语句，然后基于这个视图查询数据。使用视图查询数据的好处如下：重用SQL语句。简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。使用表的一部分而不是整个表。保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。了解视图更新数据_使用视图SELECT*FROMempa,deptbWHEREa.deptno=b.deptno;针对这两张两表联结的查询，可以将其创建为视图：CREATEVIEWemp_allASSELECTa.empno,a.ename,a.job,a.mgr,a.hiredate,a.sal,m,a.deptno,b.dnameFROMempa,deptbWHEREa.deptno=b.deptno;通过视图可以简化查询语句：SELECT*FROMemp_all;创建视图05使用存储过程更新数据_使用存储过程存储过程（StoredProcedure）是一组为了完成特定功能的SQL语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。使用存储过程的原因：通过把处理封装在一个易用的单元中，可以简化复杂的操作。重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量。减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量。简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，那么只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。了解存储过程更新数据_使用存储过程下面是Oracle数据库创建存储过程的方法：调用存储过程：创建存储过程CREATEORREPLACEPROCEDUREp_testAS

v_sal_totalNUMBER;BEGIN

FORvIN(SELECT*FROMemp)LOOPv_sal_total:=nvl(v_sal_total,0)+v.sal;ENDLOOP;dbms_output.put_line(v_sal_total);END;BEGIN

p_test;END;定义存储过程名称定义变量BEGIN..END内编写业务逻辑06