Oracle-SQL-实用基础教程.ppt

1、王忠海 2018/12/13,Oracle SQL 实用基础教程,SQL概述,历史 SQL: Struceured Query Language 1974年，由Boyce和Chamber提出 1975-1979年，在System R上实现，由IBM的San Jose研究室研制，称为Sequel,SQL概述,标准化 有关组织 ANSI(American Natural Standard Institute) ISO(International Organization for Standardization) 有关标准 SQL-86 “数据库语言SQL” SQL-89 “具有完整性增强的数据库语

2、言SQL”，增加了对完整性约束的支持 SQL-92 “数据库语言SQL”，是SQL-89的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。 SQL-99 正在讨论中的新的标准，将增加对面向对象模型的支持,SQL概述,特点 一体化 集DDL，DML，DCL于一体 单一的结构-关系，带来了数据操作符的统一 面向集合的操作方式 一次一集合 高度非过程化 用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径 两种使用方式，统一的语法结构 SQL既是自含式语言（用户使用），又是嵌入式语言（程序员使用） 语言简洁，易学易用,SQL概述,1 SQL命令基础,准备

3、工作,用SQLPLUS来学习SQL 连接到SQLPLUS，创建学习用的用户和数据 SCOTT用户在ORACLE805中默认已经创建，在8i和9i中需要手动运行rdbmsadminscott.sql。是ORACLE安装的主目录，在SQLPLUS中可以用?来代替。 例如： SQL connect / as sysdba Connected. SQL ?rdbmsadminscott.sql SQLconnect scott/tiger Connected.,基本的SELECT命令,SELECT命令用于从数据库中获得想要的信息。 语法：SELECT * , column alias , FROM t

4、able; 一个最简单的查询语句至少要包括SELECT子句和FROM子句： SELECT后面指定要选择的列 FROM后面指定从哪些表或视图中获取数据,SQL语句基本规则,SQL命令是大小写不敏感SQL命令可写成一行或多行 一个关键字不能跨多行或缩写 子句通常位于独立行，以便编辑，并易读 空格和缩进使程序易读 关键字大写，其他小写,使用SELECT * 可显示所有的列,SQL SELECT * FROM dept;DEPTNO DNAME LOC - - -10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS

5、BOSTON,选择指定的列,我们可以在SELECT后面指定要选择的列。,SQL SELECT deptno , loc FROM dept; DEPTNO LOC - -10 NEW YORK20 DALLAS30 CHICAGO40 BOSTON,在SQL*PLUS中查看表具有哪些列,在SQLPLUS中，用DESC TABLENAME命令可以查看表具有的列以及类型等,SQL desc dept名称 是否为空? 类型- - -DEPTNO NUMBER(2)DNAME VARCHAR2(14)LOC VARCHAR2(13),从数据字典中获取表的列信息,SQL SELECT TABLE_NAM

6、E,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLE FROM USER_TAB_COLUMNS WHERE TABLE_NAME=DEPT;TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH NULLABLE - - - - - DEPT DEPTNO NUMBER 22 Y DEPT DNAME VARCHAR2 14 Y DEPT LOC VARCHAR2 13 Y,使用算术运算,可以对数字类型的字段进行算术运算。运算的先后顺序是先乘除，后加减，括号优先。 上面例子中，计算的是一年的报酬，月工资乘以12个月，再加上

7、100,SQL SELECT ename , sal , 12*sal+100 FROM emp; ENAME SAL 12*SAL+100 - - - KING 5000 60100 BLAKE 2850 34300 CLERK 2450 29500 JONES 2975 35800 14 rows selected。,关于空值(NULL),空值是指不可用，不知道，不适用的值 空值不等于零也不等于空格 对空值进行的任何运算仍然为空值,SQL SELECTename NAME, 12*salcommFROM emp WHERE ename KING; NAME 12*SAL+COMM - -

8、KING,定义列的别名,当显示查询结果时，SQL*PLUS通常使列名作为列头。在很多情况下，列名并非是此列的清晰描述。因此我们就可使用列的别名作为列头，。缺省情况下，列的别名是大写的。如果区别大小写，可加双引号，如有特殊字符如$、#也必须使用双引号将其括起来。 上面的例子中的AS可以省略,SQL SELECT ename AS name , sal salary FROM emp; NAME SALARY - - ,SQL SELECT ename “Name“ , sal * 12 “Annual Salary“ FROM emp; NAME Annual Salary - - ,列连接操作

9、,使用双竖条”|”操作符，可将列和运算表达式常量连起来显示，形成一个输出显示,SQL SELECT ENAME|s salary is |sal “Employees Salary“ from emp; Employees Salary - SMITHs salary is 800 ALLENs salary is 1600 WARDs salary is 1250 JONESs salary is 2975 已选择14行。,去除重复记录,默认情况下，显示所有行，包括重复记录。如果想去掉重复记录，可以在DISTINCT关键字。如果在DISTINCT后面指定了多个列，则DISTINCT将对所有被

10、选择的列有效，其结果是不同的列的组合。,SQL SELECT deptno FROM emp;DEPTNO - 10 30 10 14 rows selected。,SQL SELECT DISTINCT deptno FROM emp;DEPTNO - 102030,SQL*PLUS程序介绍,SQL*PLUS是是Oracle自带的与Oracle交互的一个工具。你可在SQL*PLUS中做以下操作： 执行SQL命令来修改、查询、增加、删除数据库中的数据 格式化、计算、存储、数据于一定格式的报告中 产生用于存储SQL命令的脚本，以便以后执行 SQL*PLUS专用命令可被分为如下类别： 环境：影响通

11、常的SQL命令 格式化：格式化查询结果 文件处理：存储、调用、运行脚本文件 编辑：修改SQL缓存中的SQL命令 显示列的定义,登陆到SQL*PLUS,在命令行提示符下输入SQLPLUS /NOLOG 然后在SQL提示符下输入 CONNECT USERNAME/PASSWORDDATABASE,C:Documents and SettingsAdministratorSQLPLUS /NOLOG SQL*Plus: Release 9.2.0.4.0 - Production on 星期四 5月 24 09:51:42 2007 Copyright (c) 1982, 2002, Oracle

12、Corporation. All rights reserved. SQL connect system/managertlgaxz 已连接。,SQL*PLUS的编辑命令,SQL*PLUS的命令每次只能键入一行，且不能存于SQL缓存中 APPEND text 将text加到当前行的后面 CHANGE /old/ new 将当前行的old改为new CHANGE /text/ 从当前行中删除text CLEAR BUFFER 从缓存中删除所有的行 DEL 删除当前行 LIST： 列 出 SQL 缓 存 中 的 所 有 行 LIST n： 列 出 一 行(由 n 指 出 列 出 的 行) RUN：

13、 显 示 并 运 行 SQL 缓 存 中 的 SQL 语 句,SQL*PLUS的编辑命令(续),GET filename.ext：把文件的内容写到SQL缓存filename.ext：运行文件EDIT：调用编辑器编辑当前缓存中的内容EDIT filename.ext：调用编辑器编辑存的文件SPOOL filename.ext：将查询结果存于文件中SPOOL OFF 结束结果内容输出EXIT：退出SQL*PLUS,2 限定和排序数据,目的,限制某一查询所取记录 排序查询结果,使用选择限定记录,在上面的例子中，假定你想要显示部门10的所有员工，这种方式是基于WHERE子句的SQL命令。,使用wher

14、e子句限定返回的记录,WHERE子句在FROM子句后面 Condition:由列名表达式，常量和比较操作符组成。,SELECT DISTINCT * , column alias , FROM table WHERE condition(s);,使用WHERE语句,上面例子中返回job=CLERK的所有员工的name , job和deptno 注意：字符的大小写是敏感的。,SQL SELECT ename, job , deptnoFROM emp WHERE job =CLERK; ENAME JOB DEPTNO - - - KING CLERK 30 BLAKE CLERK 20 TUR

15、NER CLERK 10 14 rows selected。,字符串和日期,在WHERE子句中的字符串和日期必须用单引号括起来，所有的字符是大小写敏感的。 Oracle存储日期是以内定的格式存放，它们代表世纪、年、月、日、小时、分钟和秒，缺省显示的日期格式是DD-MON-YY，也可能是其他格式。 为了避免日期字段查询条件不同格式下可能造成的错误，通常用TO_DATE函数来进行转换。,日期类型查询条件举例,上面第二个例子使用了TO_DATE函数，就不再受日期格式的影响了。否则同样的查询，同样的表数据，不同的系统日期格式会导致结果不正确。,SQL SELECT ENAME,HIREDATE FRO

16、M EMP WHERE HIREDATE=03-DEC-81; ENAME HIREDATE - - JAMES 03-DEC-81 FORD 03-DEC-81 SQL SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE=TO_DATE(19811203,YYYYMMDD); ENAME HIREDATE - - JAMES 03-DEC-81 FORD 03-DEC-81,常用比较运算符,使用BETWEEN,SQL SELECT ename , sal FROM empWHERE sal BETWEEN 1000 AND 1500; ENAME SA

17、L - - KING 1250 BLAKE 1500 CLERK 1250 JONES 1300,注意：BETWEEN后面要先写低值，后写高值,使用IN运算符,SQL SELECT empno, ename , sal, mgrFROM empWHERE mgr IN(7902, 7566, 7788); EMPNO ENAME SAL MGR - - 7902 KING 1250 75667369 BLAKE 1500 79027788 CLARK 1250 75667876 JONES 1300 7788,使用LIKE运算符,使用LIKE运算符执行通配查询 查询条件可包含文字字符或数字 %

18、可表示零或多个字符 _可表示一个字符,SQL SELECT ename FROM empWHERE ename LIKE S%; ENAME - SMITH SCOTT,用LIKE和ESCAPE来查找包含特殊字符的数据,例如如果想查找表EMP中ENAME包含下划线_的数据，就需要用到ESCAPE选项，否则查询结果不准确。 ESCAPE后面单引号内只能有一个字符，表示前面的LIKE条件中这个字符后面的第一个字符当作普通字符处理,SQL SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE %_% ESCAPE ;EMPNO ENAME - -9999 FOR_

19、TEST,使用IS NULL,查询包含空值的记录,SQL SELECT ename , mgr FROM empWHERE mgr IS NULL; ENAME MGR KING,优先级次序： 1 所有的比较运算 2 NOT 3 AND 4 OR 括号将跨越所有优先级,使用AND运算符,AND需要条件都满足,SQL SELECT empno,ename,job,sal FROM emp WHEREsal= 1100 AND job=CLERK； EMPNO ENAME JOB SAL - - 7369 BLAKE CLERK 13007788 CLARK CLERK 1250,使用OR运算符,

20、OR需要满足条件之一即可,SQL SELECT empno,ename,job,sal FROM emp WHEREsal= 1100 OR job=CLERK； EMPNO ENAME JOB SAL - - 7369 BLAKE CLERK 13007788 CLARK CLERK 1250 7839 KING PERSIDENT 50007645 MARTIN MANAGER 1050,使用NOT运算符,SQL SELECT ename ,job FROM emp WHERE job NOT IN(CLERK,MANAGER,ANALYST); ENAME JOB - - KING PE

21、RSIDENT MARTIN SALESMAN WARD SALESMAN,ORDER BY 语句,在缺省情况下，查询返回的结果是没被排序的。使用ORDER BY子可将记录排序。ORDER BY 子句放在最后。ASC表示升序排序，DESC表示降序排序，缺省为ASC,SELECT expr FROM tableWHERE conditionORDER BY column , expr ASC | DESC,降序排列,SQL select ename,hiredate from emp order by hiredate desc;ENAME HIREDATE - - FOR_TEST ADAMS

22、 12-JAN-83 SCOTT 09-DEC-82 MILLER 23-JAN-82 JAMES 03-DEC-81 SMITH 17-DEC-80 15 rows selected.,使用列别名排序,SQL select empno,ename,sal*12 annual from emp order by annual;EMPNO ENAME ANNUAL - - -7369 SMITH 96007900 JAMES 114007876 ADAMS 132007521 WARD 150007654 MARTIN 150007934 MILLER 156007844 TURNER 1800

23、0 15 rows selected.,按照多个列排序,SQL SELECT ename,sal,deptno FROM EMP ORDER BY deptno,sal DESC;ENAME SAL DEPTNO - - - KING 5000 10 CLARK 2450 10 MILLER 1300 10 SCOTT 3000 20 FORD 3000 20 JONES 2975 20 ADAMS 1100 2015 rows selected.,3 单行函数,SQL函数,SQL函数 有两种不同的SQL函数 单行函数 多行函数 单行函数这些函数仅作用于单行记录，并对每行记录返回一个值，有许多

24、不同类型的单行函数，常用的类型有：字符函数数字函数日期函数转换函数 多行函数这些函数作用于记录组，每组记录返回一个结果。,单行函数,单行函数 单行函数操作数据项，它们接收一个或多个参数，并对查询出的每一条记录返回一个值。参数可以是： 用户提供的常量 一个列名 一个表达式 单行函数的特性 它们作用于查询的每一条记录 每条记录返回一个结果 它们可返回一个不同于它所参照的数据类型 它们可嵌入到SELECT ，WHERE 和ORDER BY子句。,字符串函数,字符函数被分为：大小写转换函数字符处理函数 LOWER(column | expression): 将字符转换为小写 UPPER(column

25、| expression): 将字符转换不大写 INITCAP(column | expression): 将每一个单词的第一个字母大写其它小写 CONCAT(column expression):返回第一个串接上第二个串,它的作用和| 运算是相同的 SUBSTR(column expression,):返回从字母m开始,有n个字符 长的字符串。 LENGTH(column expression):返回字符串长度 INSTR(column 1 expression.mn):返回字符串中字符的位置 LPAD(column 1 cxpression,n,string):在字符串前填补字符,使其长度

26、达到n。 RPAD(column 1 cxpression,n,string):在字符串后填补字符,使其长度达到n。,字符串函数举例,上面的例子将ename列的首字母大写，其余字母小写,SQL SELECT INITCAP(ename) FROM emp;INITCAP(EN - Smith Allen Ward ,字符串处理函数举例,SQLSELECT ENAME,SUBSTR(ENAME,1,3) “SUBSTR“,RPAD(ENAME,10,*) “RPAD“,LENGTH(ENAME) “LENGTH“ FROM EMP;ENAME SUBSTR RPAD LENGTH - - - -

27、 SMITH SMI SMITH* 5 ALLEN ALL ALLEN* 5 WARD WAR WARD* 4 JONES JON JONES* 5 MARTIN MAR MARTIN* 6 ,数字函数,数字函数接收数字输入返回数字值ROUND(column | expression, n):返回舍入到小数点右边n位的值TRUNC(column | expression, n):返回截断到n位的值MOD(m,n):返回m和n相除后的余数,使用ROUND函数,上面的例子分别显示45.923到小数点后两位，个位，十位,SQL SELECT ROUND(45.923,2),ROUND(45.923,

28、0),ROUND(45.923,-1) FROM DUAL; ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1) - - -45.92 46 50,使用TRUNC函数,显示45.923到小数点后两位，个位，十位,SQL SELECT TRUNC(45.923,2),TRUNC(45.923,0),TRUNC(45.923,-1) FROM DUAL; TRUNC(45.923,2) TRUNC(45.923,0) TRUNC(45.923,-1) - - -45.92 45 40,使用MOD函数,这个例子计算工资除以奖金后的余数,SQL SELEC

29、T ename,sal ,comm,MOD(sal,comm) FROM emp WHERE job = SALESMAN;ENAME SAL COMM MOD(SAL,COMM) - - - - ALLEN 1600 300 100 WARD 1250 500 250 MARTIN 1250 1400 1250 TURNER 1500 0 1500,使用日期函数,Oracle的日期函数Oracle使用内部的数字化格式存储日期，它们代表世纪、年、月、日、小时、分钟和秒.缺省显示的日期格式为DD-MON-YY，有效的日期在公元前4712年1月1日到公元后9999年12月31日SYSDATESYS

30、DATE是一个返回当前日期和时间的日期函数DUALDUAL是一个SYS用户所拥有的表，所有的用户都可以访问。它包括一个列DUMMY和一条记录值为X。例子：显示当前的日期SQLSELECT SYSDATE FROM SYS.DUAL; SYSDATE - 24-MAY-07,日期运算,日期+数字=日期 加天数 日期-数字=日期 减天数 日期-日期=数字 两日期间的天数 日期+number/24=日期 加小时 注意：两个日期类型字段不能相加,日期运算举例,SQL SELECT ENAME,SYSDATE-HIREDATE FROM EMP; ENAME SYSDATE-HIREDATE - - S

31、MITH 9654.54954 ALLEN 9589.54954 WARD 9587.54954 JONES 9548.54954 MARTIN 9369.54954 ,常见的日期函数,日期函数使用举例,SQL SELECT SYSDATE,ADD_MONTHS(SYSDATE,12),LAST_DAY(SYSDATE),NEXT_DAY(SYSDATE,FRIDAY) FROM DUAL; SYSDATE ADD_MONTHS LAST_DAY(S NEXT_DAY(S - - - - 2007/05/24 2008/05/24 2007/05/31 2007/05/25,转换函数,转换函数

32、用于数据类型之间的转换。SQL尽可能地自动进行转换，它会隐含地调用转换函数。但是你无法对隐含调用中使用的格式指定符进行控制，并且这会使得你的代码很难理解。因此使用显式转换函数而不依赖于隐式转换是一个很好的程序设计风格。,转换函数,Oracle提供了3个转换函数TO_CHAR(number | date, fmt) :将数字或日期按格式转换成字符TO_NUMBER(char) :将字符串转换成数字，此字符串必须是数字TO_DATE(CHAR, fmt) ：将字符串按指定的格式转换成日期,使用TO_CHAR操作日期函数举例,上面的例子中：YYYY代表4位的年份，MM代表月份，HH24代表24小时，

33、MI代表分钟，SS代表秒。,SQL SELECT SYSDATE,TO_CHAR(SYSDATE,YYYY/MM/DD HH24:MI:SS) DETAILDATE FROM DUAL;SYSDATE DETAILDATE - - 24-MAY-07 2007/05/24 13:47:32,使用TO_DATE函数举例,上面的例子，如果不用TO_DATE进行转换，会怎么样呢？,SQL SELECT ENAME,HIREDATE FROM EMP WHEREHIREDATE=TO_DATE(19820101,YYYYMMDD);ENAME HIREDATE - - SCOTT 09-DEC-82

34、ADAMS 12-JAN-83 MILLER 23-JAN-82,SQL SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE=19820101; SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE=19820101* ERROR at line 1: ORA-01861: literal does not match format string,因为19820101不是采用默认日期格式写的，Oracle无法进行隐含转换，导致报错。因此可以看出，对于可能发生转换的语句，应该显式指定转换,NVL函数,将空值转换为实际的

35、值 数据格式可以是日期，字符，数字 数据类型必须匹配,NVL(comm,0)：如果comm为空，则转换为0 NVL(hiredate,01-JAN-97)：如果hiredate为空，则转换为01-JAN-97 NVL(job,No Job Yet) ：如果job为空，则转换为No Job Yet,使用NVL,SQL SELECT ename , comm, sal , sal+300,(sal*12)+NVL(comm,0) FROM emp;ENAME COMM SAL SAL+300 (SAL*12)+NVL(COMM,0) - - - - - SMITH 800 1100 9600 AL

36、LEN 300 1600 1900 19500 WARD 500 1250 1550 15500 JONES 2975 3275 35700 MARTIN 1400 1250 1550 16400 BLAKE 2850 3150 34200 CLARK 2450 2750 29400 SCOTT 3000 3300 36000 ,4 从多个表中选择数据,从多个表中获取数据,有时候你需要从多个表中获得数据。在上面的例子中，报告显示的数据取自两个表。EMPNO存在于EMP表中，DEPTNO在EMP和DEPT表中都有，LOC存在于DEPT表中为了生成上面的报告，你需要将表EMP和DEPT连起来，从两

37、个中获取数据。,定义连接,当从两个以上的表中获取数据时，就要使用连接条件。一个表中的记录可以根据两个表的相同列和另一个表的记录相连接。两表中的相同列一般是主键和外键列。为了能显示两个或多个表中的数据，在WHERE子句中需要设简单的连接条件。语法如下：table.column 指定取数据的表和它的列table.column1 = table2.column2 将表连接起来的条件 当写一个有连接的SELECT命令时，为了避免同样的不同表具有同样的列名，应该在列前加表的名字或者表的别名。 当在表中有相同的列名时，并且这个列作为显示内容或者查询条件，必须在列名前加表名或表的别名作为前缀。 如果要将n个

38、表连起来，你必须指定n-1个连接条件。因此连接4个表需要有3个连接条件。如果你的表有组合主键，此规则可能不适用，此时多一条记录需要多个列唯一标识。,笛卡尔结果,笛卡尔结果 笛卡尔结果形成于： -连接条件被省略 -连接条件无效 -第一个表的所有记录连接到第二个表的所有记录 一个笛卡尔结果趋于产生一个巨大的记录数 ，通常没有意义。为了避免笛卡尔结果我们要在WHERE子句中使用有效连接,笛卡尔结果举例,SELECT * FROM EMP,DEPT;,连接类型,有两种主要的连接条件等值连接非等值连接 其它的连接方式包括多连接自连接定置运算符,什么是等值连接,就是两个表连接的WHERE条件是一个表的列等

39、于另外一个表的列。通常情况下，这种连接是主键和外键的连接。,使用等值连接获取记录举例,因为DEPTNO列在EMP和DEPT中都存在，因此需要在这个列前面加上表名，否则Oracle认为有歧义，语句无法执行,SQL SELECT ENAME,EMP.DEPTNO,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;ENAME DEPTNO DNAME - - - MILLER 10 ACCOUNTING KING 10 ACCOUNTING CLARK 10 ACCOUNTING FORD 20 RESEARCH ADAMS 20 RESEARCH S

40、COTT 20 RESEARCH ,额外的条件,除了连接条件，可能还有额外的查询条件。例如，显示员工King的员工号、各字、部门号和部门位置，这时在WHERE子句中需要设定一个额外的条件。,SQLSELECT empno, ename, emp.deptno, LocFROM emp, dept WHERE emp.deptno=dept.deptnoAND INITCAP(ename)=King;EMPNO ENAME DEPTNO LOC - - - -7839 KING 10 NEW YORK,使用表的别名,表的别名使用表名限定列名可能会很浪费时间，尤其是当表名特别长，这时你可以使用表的别名。使用表的别名会减少程序代码，因此占用较少的内存。注意，表的别名是在FROM子句中指定的。 表别名规则：表的别名最长为30个字符，但通常以短字符为佳表的别名最好有一定的含义 表的别名只在当前的SELECT语句有效如果在FROM子句定义了表的别名，在SELECT子句中必须用它来替代表名。,