• 1. SQL语言基础服务支持中心 余蕾
  • 2. SQL历史简介SQL是由位于加利福尼亚San Jose的IBM实验室与20实际70年代后期开发出来的,其含义为结构化查询语言(Structured Query Language)。 SQL是一种非过程化的语言,它使得建立关系数据库成为可能。
  • 3. 数据库历史简介数据库的用途 关系型数据库的产生 Codd博士提出关系数据库模型的12条准则 用集合来组织数据库系统 SQL是关系数据库操作和检索的标准语言
  • 4. 目录基本SQL语句 对数据进行限定和排序 处理单行函数 从多个表中获取数据 使用分组函数对数据进行聚集 子查询 操作数据——DML语句 创建和管理表
  • 5. 第一部分 基本SQL语句
  • 6. 目标完成本节课学习后,应当达到如下目标: 能够指出SQL语句中SELECT语句的作用 执行一个基本SELECT查询语句
  • 7. SELECT t语句的功能10/20/2018选择投影Table 1Table 2Table 1Table 1连接
  • 8. 基本SELECT语句10/20/2018SELECT {*, column [alias],...} FROM table;SELECT 关键字标明取哪些列的数据. FROM 关键字标明从哪些表中取数据. 重要特征:表达式以分号结尾.
  • 9. SQL语句的书写SQL 语句不是大小写敏感的 建议关键字用大写,其他用小写; SQL 语句可以分布在一行或者多行中; 关键字不能进行缩写,也不能分布在两行; 一般地,不同的子句写在不同的行为宜; 经常用跳格键或者缩进来增强可读性。
  • 10. 选出所有的列10/20/2018 DEPTNO DNAME LOC --------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 40 OPERATIONS BOSTON SQL> SELECT * 2 FROM dept;
  • 11. 选出特定的列10/20/2018 DEPTNO LOC --------- ------------- 10 NEW YORK 20 DALLAS 30 CHICAGO 40 BOSTONSQL> SELECT deptno, loc 2 FROM dept;
  • 12. 列头部的缺省格式缺省的对齐方式 左部对齐:日期、字符数据 右部对齐:数字格式数据 缺省的显示方式: 大写10/20/2018
  • 13. 剔除重复数据 DEPTNO DNAME LOC --------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTONSQL> SELECT DISTINCT * 2 FROM dept;
  • 14. 数学表达式10/20/2018符号 + - * / 描述 加 减 乘 除可以用下述的数学符号,将日期或者数字数据组合起来形成数学表达式
  • 15. 在SQL语句中运用数学表达式10/20/2018SQL> SELECT ename, sal, sal+300 2 FROM emp;ENAME SAL SAL+300 ---------- --------- --------- KING 5000 5300 BLAKE 2850 3150 CLARK 2450 2750 JONES 2975 3275 MARTIN 1250 1550 ALLEN 1600 1900 ... 14 rows selected.
  • 16. 表达式优先级10/20/2018*/+_ 乘和除操作的优先级高于加和减; 相同优先级的操作符自左向右进行计算; 圆括号“(”“)”,用来重新组合表达式的优先级。
  • 17. 操作符顺序10/20/2018SQL> SELECT ename, sal, 12*sal+100 2 FROM emp;ENAME SAL 12*SAL+100 ---------- --------- ---------- KING 5000 60100 BLAKE 2850 34300 CLARK 2450 29500 JONES 2975 35800 MARTIN 1250 15100 ALLEN 1600 19300 ... 14 rows selected.
  • 18. 使用圆括号10/20/2018SQL> SELECT ename, sal, 12*(sal+100) 2 FROM emp;ENAME SAL 12*(SAL+100) ---------- --------- ----------- KING 5000 61200 BLAKE 2850 35400 CLARK 2450 30600 JONES 2975 36900 MARTIN 1250 16200 ... 14 rows selected.
  • 19. 10/20/2018ENAME JOB SAL COMM ---------- --------- --------- --------- KING PRESIDENT 5000 BLAKE MANAGER 2850 ... TURNER SALESMAN 1500 0 ... 14 rows selected.SQL> SELECT ename, job, sal, comm 2 FROM emp; 空值是一个表达不可用、未分配、未知、或者不适用等意义的值。 空值与零和空格的概念不同,应当严格区分。定义空值(Null)
  • 20. 10/20/2018SQL> select ename, 12*sal+comm 2 from emp 3 WHERE ename='KING';ENAME 12*SAL+COMM ---------- ----------- KING 如果数学表达式中包含了一个空值,那么该数学表达式计算的结果为空值 数学表达式中空值的处理
  • 21. 定义一个列别名10/20/2018重新命名一个列的名字有时很有用; 可以用AS关键字为列名取一个别名; 如果别名中包含空格,或者其他特殊自符,则需要用引号(“”)将别名包含起来。
  • 22. SQL> SELECT ename AS name, sal salary 2 FROM emp;NAME SALARY ------------- --------- ...SQL> SELECT ename "Name", 2 sal*12 "Annual Salary" 3 FROM emp;Name Annual Salary ------------- ------------- ...使用列的别名
  • 23. 串接操作符10/20/2018可以将一些列连接起来,也可以将一些列和一些字符串连接起来; 串接操作符用两个竖杠 (||)表示; 可以用这种方法创建一个由字符串组成的结果列。
  • 24. 使用串接操作符10/20/2018SQL> SELECT ename||job AS "Employees" 2 FROM emp;Employees ------------------- KINGPRESIDENT BLAKEMANAGER CLARKMANAGER JONESMANAGER MARTINSALESMAN ALLENSALESMAN ... 14 rows selected.
  • 25. 文字(literal)字符串10/20/2018文字字符串是包含在一个Select语句中的字符串、数字、或者日期; 文字字符串必须用单引号(‘’)括起来; 对于返回的每一行,文字字符串都显示一次; 在生成报表时非常有用。
  • 26. 10/20/2018Employee Details ------------------------- KING is a PRESIDENT BLAKE is a MANAGER CLARK is a MANAGER JONES is a MANAGER MARTIN is a SALESMAN ... 14 rows selected.SQL> SELECT ename ||' is a '||job 2 AS "Employee Details" 3 FROM emp;使用文字字符串
  • 27. 重复行10/20/2018SQL> SELECT deptno 2 FROM emp; DEPTNO --------- 10 30 10 20 ... 14 rows selected. 查询语句执行的结果,缺省的显示方法是显示所有的行,包括重复的行。
  • 28. 消除掉重复行10/20/2018在SELECT语句中用 DISTINCT关键字来消除所有重复的行。SQL> SELECT DISTINCT deptno 2 FROM emp; DEPTNO --------- 10 20 30
  • 29. 显示表结构10/20/2018DESC[RIBE] tablename用 SQL*Plus的 DESCRIBE 命令来展示表的结构.
  • 30. 显示表结构10/20/2018SQL> DESCRIBE deptName Null? Type ----------------- -------- ------------ DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13)
  • 31. 总结10/20/2018SELECT [DISTINCT] {*,column [alias],...} FROM table;
  • 32. 第二部分 数据进行限定和排序
  • 33. 目标10/20/2018结束本节课后,应当达到如下目标: 在一个查询中限定查询出的行 对查询出的行进行排序
  • 34. 用“选择”限定行10/20/2018“…查询出department为10的所有员工"EMP EMPNO ENAME JOB ... DEPTNO 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20 ...EMP EMPNO ENAME JOB ... DEPTNO 7839 KING PRESIDENT 10 7782 CLARK MANAGER 10 7934 MILLER CLERK 10
  • 35. 选择查询出的行10/20/2018SELECT [DISTINCT] {*| column [alias], ...} FROM table [WHERE condition(s)];用where子句限定查询出的行. Where子句紧跟在From子句之后.
  • 36. 运用WHERE子句10/20/2018SQL> SELECT ename, job, deptno 2 FROM emp 3 WHERE job='CLERK';ENAME JOB DEPTNO ---------- --------- --------- JAMES CLERK 30 SMITH CLERK 20 ADAMS CLERK 20 MILLER CLERK 10
  • 37. 字符串和日期10/20/2018SQL> SELECT ename, job, deptno 2 FROM emp 3 WHERE ename = ;'JAMES'字符串和日期值应当用单引号括起来. 字符串值大小写敏感,日期值对于日期的格式敏感。 缺省的日期值格式: DD-MON-YY. 例如:23-09-02.为02年9月23日
  • 38. 比较操作符10/20/2018操作符 = > >= < <= <>含义 等于 大于 大于或等于 小于 小于或等于 不等于
  • 39. 使用比较操作符10/20/2018SQL> SELECT ename, sal, comm 2 FROM emp 3 WHERE sal<=comm;ENAME SAL COMM ---------- --------- --------- MARTIN 1250 1400
  • 40. 其它比较操作符10/20/2018操作符 BETWEEN ...AND... IN(list) LIKE IS NULL含义 在两个值之间 (包含着两个值) 在列出的值中 与给出的字符串匹配 是一个空值
  • 41. 用 BETWEEN 操作符10/20/2018ENAME SAL ---------- --------- MARTIN 1250 TURNER 1500 WARD 1250 ADAMS 1100 MILLER 1300 SQL> SELECT ename, sal 2 FROM emp 3 WHERE sal BETWEEN 1000 AND 1500;Lower limitHigher limit用BETWEEN操作符来查询出在某一范围内的行.
  • 42. 使用IN操作符10/20/2018SQL> SELECT empno, ename, sal, mgr 2 FROM emp 3 WHERE mgr IN (7902, 7566, 7788); EMPNO ENAME SAL MGR --------- ---------- --------- --------- 7902 FORD 3000 7566 7369 SMITH 800 7902 7788 SCOTT 3000 7566 7876 ADAMS 1100 7788用IN操作符来检验一个值是否在一个列表中.
  • 43. 使用LIKE操作符10/20/2018SQL> SELECT ename 2 FROM emp 3 WHERE ename LIKE 'S%';用LIKE进行某个字符串值的通配符匹配,来选出某些行. 查询条件中既可以包含字符,也可以包含数字. % 代表0个或者多个字符. _ 代表一个字符.
  • 44. 使用 LIKE操作符10/20/2018SQL> SELECT ename 2 FROM emp 3 WHERE ename LIKE '_A%';ENAME ---------- MARTIN JAMES WARD可以混合使用模式字符串. 如果要匹配%这个字符,可以用\%来表示
  • 45. 使用 IS NULL 操作符10/20/2018SQL> SELECT ename, mgr 2 FROM emp 3 WHERE mgr IS NULL;ENAME MGR ---------- --------- KING用 IS NULL操作符来检查有无空值
  • 46. 逻辑操作符10/20/2018操作符 AND OR NOT含义 两个条件都为TRUE ,则返回TRUE 两个条件中任何一个为TRUE,则返回TRUE 如果条件为FALSE,返回TRUE
  • 47. 使用 AND操作符10/20/2018AND 需要所有的条件都为TRUE.SQL> SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal>=1100 4 AND job='CLERK'; EMPNO ENAME JOB SAL --------- ---------- --------- --------- 7876 ADAMS CLERK 1100 7934 MILLER CLERK 1300
  • 48. 使用OR 操作符10/20/2018OR操作符只需任意条件为TRUE即可SQL> SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal>=1100 4 OR job='CLERK'; EMPNO ENAME JOB SAL --------- ---------- --------- --------- 7839 KING PRESIDENT 5000 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7566 JONES MANAGER 2975 7654 MARTIN SALESMAN 1250 ... 7900 JAMES CLERK 950 ... 14 rows selected.
  • 49. 使用 NOT操作符10/20/2018SQL> SELECT ename, job 2 FROM emp 3 WHERE job NOT IN ('CLERK','MANAGER','ANALYST');ENAME JOB ---------- --------- KING PRESIDENT MARTIN SALESMAN ALLEN SALESMAN TURNER SALESMAN WARD SALESMAN
  • 50. 优先级规则10/20/2018 顺序 操作符 1 所有比较操作符 2 NOT 3 AND 4 OR 用圆括号可以改变操作符的优先级次序。
  • 51. 10/20/2018ENAME JOB SAL ---------- --------- --------- KING PRESIDENT 5000 MARTIN SALESMAN 1250 ALLEN SALESMAN 1600 TURNER SALESMAN 1500 WARD SALESMAN 1250SQL> SELECT ename, job, sal 2 FROM emp 3 WHERE job='SALESMAN' 4 OR job='PRESIDENT' 5 AND sal>1500;优先级规则
  • 52. 优先级规则10/20/2018ENAME JOB SAL ---------- --------- --------- KING PRESIDENT 5000 ALLEN SALESMAN 1600用圆括号强制改变操作符优先级.SQL> SELECT ename, job, sal 2 FROM emp 3 WHERE (job='SALESMAN' 4 OR job='PRESIDENT') 5 AND sal>1500;
  • 53. 10/20/2018 SQL> SELECT ename, job, deptno, hiredate 2 FROM emp 3 ORDER BY hiredate; ENAME JOB DEPTNO HIREDATE ---------- --------- --------- --------- SMITH CLERK 20 17-DEC-80 ALLEN SALESMAN 30 20-FEB-81 ... 14 rows selected.用 ORDER BY子句进行行的排序 ASC: 升序, 缺省 DESC: 降序 ORDER BY子句跟在SELECT 语句之后 ORDER BY 子句
  • 54. 采用降序排序10/20/2018SQL> SELECT ename, job, deptno, hiredate 2 FROM emp 3 ORDER BY hiredate DESC;ENAME JOB DEPTNO HIREDATE ---------- --------- --------- --------- ADAMS CLERK 20 12-JAN-83 SCOTT ANALYST 20 09-DEC-82 MILLER CLERK 10 23-JAN-82 JAMES CLERK 30 03-DEC-81 FORD ANALYST 20 03-DEC-81 KING PRESIDENT 10 17-NOV-81 MARTIN SALESMAN 30 28-SEP-81 ... 14 rows selected.
  • 55. 按照列的别名来进行排序10/20/2018SQL> SELECT empno, ename, sal*12 annsal 2 FROM emp 3 ORDER BY annsal; EMPNO ENAME ANNSAL --------- ---------- --------- 7369 SMITH 9600 7900 JAMES 11400 7876 ADAMS 13200 7654 MARTIN 15000 7521 WARD 15000 7934 MILLER 15600 7844 TURNER 18000 ... 14 rows selected.
  • 56. 按照多个列进行排序10/20/2018可以按照 SELECT 列中没有的列来进行排序.SQL> SELECT ename, deptno, sal 2 FROM emp 3 ORDER BY deptno, sal DESC;ENAME DEPTNO SAL ---------- --------- --------- KING 10 5000 CLARK 10 2450 MILLER 10 1300 FORD 20 3000 ... 14 rows selected.ORDER BY 后的列的顺序既排序的顺序.
  • 57. 总结10/20/2018SELECT [DISTINCT] {*| column [alias], ...} FROM table [WHERE condition(s)] [ORDER BY {column, expr, alias} [ASC|DESC]];
  • 58. 第三部分 处理单行的函数
  • 59. 目标10/20/2018完成本节课后,应当达到如下的目标: 描述在 SQL语句中可能用到的各种函数。 在SELECT 语句中使用字符串、数字、日期的函数。 描述转换函数的用法
  • 60. SQL函数函数输入参数 1参数2参数n函数处理动作输出结果值
  • 61. 两种类型的 SQL函数10/20/2018函数单行函数多行函数
  • 62. 单行函数10/20/2018function_name (column|expression, [arg1, arg2,...])操作数据项 接收参数并返回一个值 在返回的每一行上进行处理 每一行都有一个处理结果 可能要修改数据类型 可以进行嵌套 接受多个参数,参数可以是一个列或者一个表达式
  • 63. 单行函数10/20/2018格式转换字符数字日期常规处理单行函数
  • 64. 字符函数10/20/2018字符函数LOWER UPPER INITCAPCONCAT SUBSTR LENGTH INSTR LPAD TRIM大小写转换函数字符操作函数
  • 65. 大小写转换10/20/2018函数结果LOWER('SQL Course') UPPER('SQL Course') INITCAP('SQL Course')sql course SQL COURSE Sql Course 对字符串的大小写进行转换
  • 66. 10/20/2018SQL> SELECT empno, ename, deptno 2 FROM emp 3 WHERE ename = 'blake'; no rows selected EMPNO ENAME DEPTNO --------- ---------- --------- 7698 BLAKE 30SQL> SELECT empno, ename, deptno 2 FROM emp 3 WHERE LOWER(ename) = 'blake';使用大小写转换函数查出员工Blake的号码、名字、部门号
  • 67. 10/20/2018CONCAT('Good', 'String') SUBSTR('String',1,3) LENGTH('String') INSTR('String', 'r') LPAD(sal,10,'*') TRIM('S' FROM 'SSMITH')GoodString Str 6 3 ******5000 MITH函数结果操作字符串 字符操作函数
  • 68. 使用字符操作函数10/20/2018SQL> SELECT ename, CONCAT (ename, job), LENGTH(ename), 2 INSTR(ename, 'A') 3 FROM emp 4 WHERESUBSTR(job,1,5) = 'SALES';ENAME CONCAT(ENAME,JOB) LENGTH(ENAME) INSTR(ENAME,'A') ---------- ------------------- ------------- ---------------- MARTIN MARTINSALESMAN 6 2 ALLEN ALLENSALESMAN 5 1 TURNER TURNERSALESMAN 6 0 WARD WARDSALESMAN 4 2
  • 69. 数字函数10/20/2018ROUND: 对指定的值进行四舍五入 ROUND(45.926, 2) 45.93 TRUNC: 对指定的值进行取整 TRUNC(45.926, 2) 45.92 MOD: 返回除法计算后的余数 MOD(1600, 300) 小数小数100
  • 70. 使用 ROUND函数10/20/2018SQL> SELECT ROUND(45.923,2), ROUND(45.923,0), 2 ROUND(45.923,-1) 3 FROM DUAL;ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1) --------------- -------------- ----------------- 45.92 46 50
  • 71. 使用 TRUNC函数10/20/2018SQL> SELECT TRUNC(45.923,2), TRUNC(45.923), 2 TRUNC(45.923,-1) 3 FROM DUAL;TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1) --------------- ------------- --------------- 45.92 45 40
  • 72. 使用 MOD函数10/20/2018SQL> SELECT ename, sal, comm, MOD(sal, comm) 2 FROM emp 3 WHERE job = 'SALESMAN'; ENAME SAL COMM MOD(SAL,COMM) ---------- --------- --------- ------------- MARTIN 1250 1400 1250 ALLEN 1600 300 100 TURNER 1500 0 1500 WARD 1250 500 250计算工作为 salesman的员工的工资(Sal),除以COMM后的余数。
  • 73. 使用日期10/20/2018Oracle 以一种内部的格式来保存日期: 世纪,年,月,日,小时,分钟,秒. 缺省的格式:DD-MON-YY.例如:23-JAN-02 SYSDATE 是一个返回日期和时间的函数. DUAL 是一个显示 SYSDATE的虚拟表.
  • 74. 日期的计算10/20/2018为一个日期值增加或者减少一个数字. 计算出两个日期之间相隔的天数. 要为日期值增加小时数,可以将小时数除以24后加上。
  • 75. 用数学运算符对日期进行计算10/20/2018SQL> SELECT ename, (SYSDATE-hiredate)/7 WEEKS 2 FROM emp 3 WHERE deptno = 10;ENAME WEEKS ---------- --------- KING 830.93709 CLARK 853.93709 MILLER 821.36566
  • 76. 日期函数10/20/2018两个日期之间的月数MONTHS_BETWEENADD_MONTHSNEXT_DAY LAST_DAYROUND TRUNC 为一个日期增加月份一个日期的下一个指定日子 (例如:星期1)的日期某个月份的最后一天对日期进行四舍五入计算 对日期进行取整计算函数描述
  • 77. 使用Date函数10/20/2018MONTHS_BETWEEN ('01-SEP-05','11-JAN-04')ADD_MONTHS ('11-JAN-05',6)NEXT_DAY ('01-SEP-05','FRIDAY') LAST_DAY('01-SEP-05')19.6774194'11-JUL-05''02-SEP-05''30-SEP-05'
  • 78. 使用日期函数10/20/2018ROUND('25-JUL-05','MONTH') 01-AUG-05ROUND('25-JUL-05','YEAR') 01-JAN-06TRUNC('25-JUL-05','MONTH') 01-JUL-05 TRUNC('25-JUL-05','YEAR') 01-JAN-05
  • 79. NVL 函数10/20/2018NVL(expr1, expr2) 如果expr1为null,则返回expr2,否则返回expr1 可以使用的数据类型是数字、日期、字符型. 数据类型必须能够匹配 NVL(comm,0) NVL(hiredate,'01-JAN-97') NVL(job,'No Job Yet')
  • 80. 使用 NVL 函数10/20/2018SQL> SELECT ename, sal, comm, (sal*12)+NVL(comm,0) 2 FROM emp;ENAME SAL COMM (SAL*12)+NVL(COMM,0) ---------- --------- --------- -------------------- KING 5000 60000 BLAKE 2850 34200 CLARK 2450 29400 JONES 2975 35700 MARTIN 1250 1400 16400 ALLEN 1600 300 19500 ... 14 rows selected.
  • 81. 函数的嵌套10/20/2018F3(F2(F1(col,arg1),arg2),arg3)Step 1 = Result 1Step 2 = Result 2Step 3 = Result 3单行的函数可以嵌套到任何一个层次. 嵌套函数的计算顺序——先计算深层嵌套,再计算浅层嵌套.
  • 82. 嵌套函数10/20/2018SQL> SELECT ename, 2 NVL(TO_CHAR(mgr),'No Manager') 3 FROM emp 4 WHERE mgr IS NULL;ENAME NVL(TO_CHAR(MGR),'NOMANAGER') ---------- ----------------------------- KING No Manager
  • 83. 总结10/20/2018Character data: LOWER, UPPER, INITCAP, CONCAT, SUBSTR, INSTR, LENGTH Number data: ROUND, TRUNC, MOD Date data: MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, TRUNC
  • 84. 第四部分 从多个表中获取数据
  • 85. 目标10/20/2018完成本节课的学习后,应当达到如下目标: 写出能从多个等连接或者非等连接的表中取出数据的SQL语句。 使用外连接来查找数据。 写出一个表与其自身连接的SQL语句。
  • 86. 从多个表中获取数据10/20/2018EMPNO DEPTNO LOC ----- ------- -------- 7839 10 NEW YORK 7698 30 CHICAGO 7782 10 NEW YORK 7566 20 DALLAS 7654 30 CHICAGO 7499 30 CHICAGO ... 14 rows selected.EMP DEPT EMPNO ENAME ... DEPTNO ------ ----- ... ------ 7839 KING ... 10 7698 BLAKE ... 30 ... 7934 MILLER ... 10DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
  • 87. 什么是连接?10/20/2018SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2;用一个连接来从多个表中获取数据. 在Where子句中书写连接的条件. 如果某个列的名字在多个表中出现了,那么需要在列的名字前面加上表名作为前缀.
  • 88. 笛卡尔积10/20/2018以笛卡尔积连接的表具有下列特征: 连接条件被忽略。 第一个表中的所有的行与第二个表中的所有行相连接。 如果在WHERE子句中加入条件,那么可以避免笛卡尔积.
  • 89. 产生一个笛卡尔积10/20/2018ENAME DNAME ------ ---------- KING ACCOUNTING BLAKE ACCOUNTING ... KING RESEARCH BLAKE RESEARCH ... 56 rows selected.EMP (14 行) DEPT (4 行) EMPNO ENAME ... DEPTNO ------ ----- ... ------ 7839 KING ... 10 7698 BLAKE ... 30 ... 7934 MILLER ... 10DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON“笛卡尔积 运算结果: 14*4=56行”
  • 90. 连接的类型10/20/2018等连接非等连接外连接自连接
  • 91. 什么是等连接?10/20/2018EMP DEPT EMPNO ENAME DEPTNO ------ ------- ------- 7839 KING 10 7698 BLAKE 30 7782 CLARK 10 7566 JONES 20 7654 MARTIN 30 7499 ALLEN 30 7844 TURNER 30 7900 JAMES 30 7521 WARD 30 7902 FORD 20 7369 SMITH 20 ... 14 rows selected. DEPTNO DNAME LOC ------- ---------- -------- 10 ACCOUNTING NEW YORK 30 SALES CHICAGO 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 20 RESEARCH DALLAS 20 RESEARCH DALLAS ... 14 rows selected.外键主键
  • 92. 用等连接获取记录10/20/2018SQL> SELECT emp.empno, emp.ename, emp.deptno, 2 dept.deptno, dept.loc 3 FROM emp, dept 4 WHERE emp.deptno=dept.deptno;EMPNO ENAME DEPTNO DEPTNO LOC ----- ------ ------ ------ --------- 7839 KING 10 10 NEW YORK 7698 BLAKE 30 30 CHICAGO 7782 CLARK 10 10 NEW YORK 7566 JONES 20 20 DALLAS ... 14 rows selected.
  • 93. 限定不明确的列名10/20/2018用表名作为前缀来限定在多个表中都出现的列名字. 利用表的前缀可以提高性能. 使用不同的别名来标志在不同表中的相同的名字。
  • 94. 用 AND操作符增加查询条件10/20/2018EMP DEPT EMPNO ENAME DEPTNO ------ ------- ------- 7839 KING 10 7698 BLAKE 30 7782 CLARK 10 7566 JONES 20 7654 MARTIN 30 7499 ALLEN 30 7844 TURNER 30 7900 JAMES 30 7521 WARD 30 7902 FORD 20 7369 SMITH 20 ... 14 rows selected.DEPTNO DNAME LOC ------ --------- -------- 10 ACCOUNTING NEW YORK 30 SALES CHICAGO 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 20 RESEARCH DALLAS 20 RESEARCH DALLAS ... 14 rows selected.
  • 95. 使用表的别名10/20/2018SQL> SELECT emp.empno, emp.ename, emp.deptno, 2 dept.deptno, dept.loc 3 FROM emp, dept 4 WHERE emp.deptno=dept.deptno;SQL> SELECT e.empno, e.ename, e.deptno, 2 d.deptno, d.loc 3 FROM emp e, dept d 4 WHERE e.deptno= d.deptno;使用的表的别名来简化查询
  • 96. 连接多个表10/20/2018NAME CUSTID ----------- ------ JOCKSPORTS 100 TKB SPORT SHOP 101 VOLLYRITE 102 JUST TENNIS 103 K+T SPORTS 105 SHAPE UP 106 WOMENS SPORTS 107 ... ... 9 rows selected.CUSTOMER CUSTID ORDID ------- ------- 101 610 102 611 104 612 106 601 102 602 106 604 106 605 ... 21 rows selected.ORD ORDID ITEMID ------ ------- 610 3 611 1 612 1 601 1 602 1 ... 64 rows selected. ITEM
  • 97. 非等连接10/20/2018EMPSALGRADE“在EMP表中,如何查出在 SALGRADE表所示的每个 工资段中的员工号码?” EMPNO ENAME SAL ------ ------- ------ 7839 KING 5000 7698 BLAKE 2850 7782 CLARK 2450 7566 JONES 2975 7654 MARTIN 1250 7499 ALLEN 1600 7844 TURNER 1500 7900 JAMES 950 ... 14 rows selected.GRADE LOSAL HISAL ----- ----- ------ 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999
  • 98. 用非等连接查询记录10/20/2018ENAME SAL GRADE ---------- --------- --------- JAMES 950 1 SMITH 800 1 ADAMS 1100 1 ... 14 rows selected.SQL> SELECT e.ename, e.sal, s.grade 2 FROM emp e, salgrade s 3 WHERE e.sal 4 BETWEEN s.losal AND s.hisal;
  • 99. 外连接10/20/2018EMP DEPT 在OPERATIONS 部门中没有任何员工ENAME DEPTNO ----- ------ KING 10 BLAKE 30 CLARK 10 JONES 20 ... DEPTNO DNAME ------ ---------- 10 ACCOUNTING 30 SALES 10 ACCOUNTING 20 RESEARCH ... 40 OPERATIONS
  • 100. 外连接(基于Oracle)10/20/2018SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column(+) = table2.column;SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column = table2.column(+);可以用外连接来查出在一个表中不匹配连接条件的行. 外连接的符号是: (+).
  • 101. 外连接(基于Informix )外连接 outerSELECT table1.column, table2.column FROM table1, outer table2 WHERE table1.column = table2.column;
  • 102. 使用外连接10/20/2018SQL> SELECT e.ename, d.deptno, d.dname 2 FROM emp e, dept d 3 WHERE e.deptno(+) = d.deptno 4 ORDER BY e.deptno;ENAME DEPTNO DNAME ---------- --------- ------------- KING 10 ACCOUNTING CLARK 10 ACCOUNTING ... 40 OPERATIONS 15 rows selected.
  • 103. 自连接10/20/2018EMP (WORKER)EMP (MANAGER)“在 WORKER表中的MGR列等于 MANAGER 表中的EMPNO列”EMPNO ENAME MGR ----- ------ ---- 7839 KING 7698 BLAKE 7839 7782 CLARK 7839 7566 JONES 7839 7654 MARTIN 7698 7499 ALLEN 7698EMPNO ENAME ----- -------- 7839 KING 7839 KING 7839 KING 7698 BLAKE 7698 BLAKE
  • 104. 10/20/2018WORKER.ENAME||'WORKSFOR'||MANAG ------------------------------- BLAKE works for KING CLARK works for KING JONES works for KING MARTIN works for BLAKE ... 13 rows selected.SQL> SELECT worker.ename||' works for '||manager.ename 2 FROM emp worker, emp manager 3 WHERE worker.mgr = manager.empno;表与自身连接
  • 105. 总结10/20/2018SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2;等连接非等连接外连接自连接
  • 106. 第五部分 用分组函数来对数据进行聚集
  • 107. 目标10/20/2018完成本节课的学习后,应当完成下面的目标: 识记常用的分组函数。 描述分组函数的用处。 使用GROUP BY 子句对元组进行分组 使用HAVING 子句来筛选分组
  • 108. 10/20/2018EMP“EMP 表中最 大的SAL值” DEPTNO SAL --------- --------- 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250 MAX(SAL) --------- 5000分组函数对一组数据行进行操作,并对每个组得出一个结果. 什么是分组函数?
  • 109. 分组函数的类型10/20/2018AVG COUNT MAX MIN STDDEV SUM VARIANCE
  • 110. 使用分组函数10/20/2018SELECT [column,] group_function(column) FROM table [WHERE condition] [GROUP BY column] [ORDER BY column];
  • 111. 使用AVG和SUM 函数10/20/2018AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL) -------- --------- --------- --------- 1400 1600 1250 5600SQL> SELECT AVG(sal), MAX(sal), 2 MIN(sal), SUM(sal) 3 FROM emp 4 WHERE job LIKE 'SALES%';对于数字类型的数据,可以使用 AVG和SUM 函数.
  • 112. 使用 MIN和MAX 函数10/20/2018SQL> SELECT MIN(hiredate), MAX(hiredate) 2 FROM emp;MIN(HIRED MAX(HIRED --------- --------- 17-DEC-80 12-JAN-83 可以对许多类型的数据使用MIN和MAX函数.
  • 113. 使用 COUNT函数10/20/2018 COUNT(*) --------- 6SQL> SELECT COUNT(*) 2 FROM emp 3 WHERE deptno = 30;COUNT(*) 返回查询出的总行数.
  • 114. 使用 COUNT函数10/20/2018SQL> SELECT COUNT(comm) 2 FROM emp 3 WHERE deptno = 30;COUNT(COMM) ----------- 4COUNT(expr) 返回expr值非空的行的数.
  • 115. 10/20/2018SQL> SELECT AVG(comm) 2 FROM emp; AVG(COMM) --------- 550分组函数和空值分组函数忽略了行中所有的空行.
  • 116. 10/20/2018SQL> SELECT AVG(NVL(comm,0)) 2 FROM emp;AVG(NVL(COMM,0)) ---------------- 157.14286分组函数和NVL函数的结合NVL函数使得分组函数可以处理空值.
  • 117. 10/20/2018EMP“EMP表中 每个部门 的平均 工资” 2916.6667 2175 1566.6667 DEPTNO SAL --------- --------- 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250 DEPTNO AVG(SAL) ------- --------- 10 2916.6667 20 2175 30 1566.6667创建分组数据
  • 118. 10/20/2018SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];创建分组数据: GROUP BY 子句将表中的数据行用GROUP BY语句分为几个组.
  • 119. 使用 GROUP BY子句10/20/2018SQL> SELECT deptno, AVG(sal) 2 FROM emp 3 GROUP BY deptno; DEPTNO AVG(SAL) --------- --------- 10 2916.6667 20 2175 30 1566.6667在SELECT语句中,没有使用分组函数的列必须在GROUP BY子句中.
  • 120. 使用 GROUP BY子句10/20/2018SQL> SELECT AVG(sal) 2 FROM emp 3 GROUP BY deptno; AVG(SAL) --------- 2916.6667 2175 1566.6667GROUP BY后面的列可以不出现在 SELECT链中.
  • 121. 根据多个列进行分组10/20/2018EMP“每个部门中, 每种工作的 工资总和” DEPTNO JOB SAL ------ ------- -------- 10 MANAGER 2450 10 PRESIDENT 5000 10 CLERK 1300 20 CLERK 800 20 CLERK 1100 20 ANALYST 3000 20 ANALYST 3000 20 MANAGER 2975 30 SALESMAN 1600 30 MANAGER 2850 30 SALESMAN 1250 30 CLERK 950 30 SALESMAN 1500 30 SALESMAN 1250JOB SUM(SAL) -------- ------- CLERK 1300 MANAGER 2450 PRESIDENT 5000 ANALYST 6000 CLERK 1900 MANAGER 2975 CLERK 950 MANAGER 2850 SALESMAN 5600DEPTNO ------ 10 10 10 20 20 20 30 30 30
  • 122. 在多个列上使用 GROUP BY 子句10/20/2018SQL> SELECT deptno, job, sum(sal) 2 FROM emp 3 GROUP BY deptno, job; DEPTNO JOB SUM(SAL) --------- --------- --------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 6000 20 CLERK 1900 ... 9 rows selected.
  • 123. 分组函数的误用10/20/2018SQL> SELECT deptno, COUNT(ename) 2 FROM emp;SELECT deptno, COUNT(ename) * ERROR at line 1: ORA-00937: not a single-group group functionDeptno列没有在 GROUP BY 子句中出现在SELECT 语句中,任何不在分组函数中出现的列,必须在 GROUP BY 子句中.
  • 124. 分组函数的误用10/20/2018SQL> SELECT deptno, AVG(sal) 2 FROM emp 3 WHERE AVG(sal) > 2000 4 GROUP BY deptno;WHERE AVG(sal) > 2000 * ERROR at line 3: ORA-00934: group function is not allowed here不能用 WHERE 子句对 分组做出限定不能在 WHERE子句中对列做出限定. 使用 HAVING 子句来限定分组.
  • 125. 排除分组结果10/20/2018每组中最多薪水大 于$2900的部门号”EMP500030002850 DEPTNO SAL ------ -------- 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250DEPTNO MAX(SAL) ------- -------- 10 5000 20 3000
  • 126. 用 HAVING 子句排除分组结果10/20/2018SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];用 HAVING 子句来限定分组 行被分组了. 使用了分组函数. 符合 HAVING子句条件的组将被显示出来.
  • 127. 使用 HAVING 子句10/20/2018SQL> SELECT deptno, max(sal) 2 FROM emp 3 GROUP BY deptno 4 HAVING max(sal)>2900; DEPTNO MAX(SAL) --------- --------- 10 5000 20 3000
  • 128. 使用 HAVING子句10/20/2018SQL> SELECT job, SUM(sal) PAYROLL 2 FROM emp 3 WHERE job NOT LIKE 'SALES%' 4 GROUP BY job 6 ORDER BY SUM(sal);JOB PAYROLL --------- --------- ANALYST 6000 MANAGER 82755 HAVING SUM(sal)>5000
  • 129. 分组函数的嵌套使用10/20/2018SQL> SELECT max(avg(sal)) 2 FROM emp 3 GROUP BY deptno;MAX(AVG(SAL)) ------------- 2916.6667显示最大的平均工资.
  • 130. 总结10/20/2018SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column]; 子句的顺序: WHERE 子句 GROUP BY 子句 HAVING 子句
  • 131. 第六部分 子 查 询
  • 132. 目标10/20/2018完成本节课的学习后,应当完成如下的目标: 描述子查询可以解决的问题类型。 定义子查询。 列出子查询的类型。 写出单行或者多行的子查询。
  • 133. 使用子查询解决问题谁的工资比Jones多?10/20/2018“哪一位员工的工资比 Jones的薪水高?”主查询?“Jones的薪水是多少?”?子查询
  • 134. 子查询10/20/2018SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table); 在主查询执行之前,子查询首先要执行一次。 子查询的结果要在主查询中使用。
  • 135. 使用子查询10/20/20182975SQL> SELECT ename 2 FROM emp 3 WHERE sal > 4 (SELECT sal 5 FROM emp 6 WHERE empno=7566);ENAME ---------- KING FORD SCOTT
  • 136. 子查询要点10/20/2018子查询要包括在括号里面. 子查询要放在比较操作符的右边. 子查询中不要包含ORDER BY 子句。 对于单行的子查询使用单行的比较操作符 对于多行的子查询要使用多行的比较操作符.
  • 137. 10/20/2018主查询子查询返回CLERK多行子查询CLERK MANAGER主查询子查询返回多列子查询CLERK 7900 MANAGER 7698主查询子查询返回子查询的类型单行子查询
  • 138. 单行子查询10/20/2018操作符 = > >= < <= <>含义 等于 大于 大于或者等于 小于 小于或者等于 不等于仅返回一行 使用单行的比较操作符
  • 139. 10/20/2018CLERK1100ENAME JOB ---------- --------- MILLER CLERKSQL> SELECT ename, job 2 FROM emp 3 WHERE job = 4 (SELECT job 5 FROM emp 6 WHERE empno = 7369) 7 AND sal > 8 (SELECT sal 9 FROM emp 10 WHERE empno = 7876); 执行单行子查询
  • 140. 在子查询中使用分组函数10/20/2018800ENAME JOB SAL ---------- --------- --------- SMITH CLERK 800SQL> SELECT ename, job, sal 2 FROM emp 3 WHERE sal = 4 (SELECT MIN(sal) 5 FROM emp);
  • 141. 在子查询中使用HAVING子句10/20/2018800SQL> SELECT deptno, MIN(sal) 2 FROM emp 3 GROUP BY deptno 4 HAVING MIN(sal) > 5 (SELECT MIN(sal) 6 FROM emp 7 WHERE deptno = 20);Oracle服务器首先执行子查询. Oracle服务器将结果返回给主查询的HAVING子句.
  • 142. 这个语句错在哪里?10/20/2018ERROR: ORA-01427: single-row subquery returns more than one row no rows selected SQL> SELECT empno, ename 2 FROM emp 3 WHERE sal = 4 (SELECT MIN(sal) 5 FROM emp 6 GROUP BY deptno);单行操作符用在多行子查询上
  • 143. 这个查询能正常工作么?10/20/2018no rows selected子查询不返回任何值SQL> SELECT ename, job 2 FROM emp 3 WHERE job = 4 (SELECT job 5 FROM emp 6 WHERE ename='SMYTHE');
  • 144. 多行子查询10/20/2018操作符 IN ANY ALL含义 等于列表中的某一个值 与列表中的任意值比较 与列表中的所有值相比较 返回值多于一行 使用多行比较操作符
  • 145. 在多行子查询中使用Any操作符10/20/201895080011001300 EMPNO ENAME JOB --------- ---------- --------- 7654 MARTIN SALESMAN 7521 WARD SALESMAN SQL> SELECT empno, ename, job 2 FROM emp 3 WHERE sal < ANY 4 (SELECT sal 5 FROM emp 6 WHERE job = 'CLERK') 7 AND job <> 'CLERK';
  • 146. 在子查询中使用ALL操作符10/20/20182916.666721751566.6667 EMPNO ENAME JOB --------- ---------- --------- 7839 KING PRESIDENT 7566 JONES MANAGER 7902 FORD ANALYST 7788 SCOTT ANALYSTSQL> SELECT empno, ename, job 2 FROM emp 3 WHERE sal > ALL 4 (SELECT avg(sal) 5 FROM emp 6 GROUP BY deptno);
  • 147. 总结10/20/2018SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);当一个查询基于未知值时,子查询是非常有用的.
  • 148. 第七部分 操作数据-DML语句
  • 149. 目标10/20/2018结束本节课后,应当达到如下目标: 描述每个DML语句 向一个表中插入数据 更新一个表中数据行 从一个表中删除行
  • 150. 数据操作语言10/20/2018DML语句在下列情况下发生: 往一个表中增加新行 更改一个表中现有的行 从一个表中删除掉现有的行 一个事务由一组构成一个逻辑操作的DML语句组成.
  • 151. 向一个表中增加新行10/20/2018DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON新行 50 DEVELOPMENT DETROITDEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON“…向 DEPT 表中 增加新行…” 50 DEVELOPMENT DETROIT
  • 152. INSERT语句10/20/2018INSERT INTO table [(column [, column...])] VALUES (value [, value...]);使用 INSERT 语句向表中增加新行. 使用这种方法只能一次插入一行数据.
  • 153. 插入新行10/20/2018SQL> INSERT INTO dept (deptno, dname, loc) 2 VALUES (50, 'DEVELOPMENT', 'DETROIT'); 1 row created.插入包含每一个列值的新行. 按缺省顺序列出表中所有的列值. 列出 INSERT 子句中所有的列. 日期值和字符值要用单引号括起来.
  • 154. 插入带有空值的行10/20/2018SQL> INSERT INTO dept (deptno, dname ) 2 VALUES (60, 'MIS'); 1 row created.明确的方法: 指定 NULL 关键字.SQL> INSERT INTO dept 2 VALUES (70, 'FINANCE', NULL); 1 row created.省略的方法: 从列的链表忽略有空值的列.
  • 155. 插入特殊的值10/20/2018SQL> INSERT INTO emp (empno, ename, job, 2 mgr, hiredate, sal, comm, 3 deptno) 4 VALUES (7196, 'GREEN', 'SALESMAN', 5 7782, SYSDATE, 2000, NULL, 6 10); 1 row created.SYSDATE函数取出当前的日期的时间.
  • 156. 插入特殊的日期值10/20/2018SQL> INSERT INTO emp 2 VALUES (2296,'AROMANO','SALESMAN',7782, 3 TO_DATE('FEB 3, 1997', 'MON DD, YYYY'), 4 1300, NULL, 10); 1 row created.检验结果.EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------- -------- ---- --------- ---- ---- ------ 2296 AROMANO SALESMAN 7782 03-FEB-97 1300 10增加一个新的员工.
  • 157. 使用替换变量检验插入值10/20/2018SQL> INSERT INTO dept (deptno, dname, loc) 2 VALUES (&department_id, 3 '&department_name', '&location');Enter value for department_id: 80 Enter value for department_name: EDUCATION Enter value for location: ATLANTA 1 row created.在SQL 语句中用&替换变量提示用户输入值 &是一个用于变量值的占位符
  • 158. 从另一个表中拷贝行10/20/2018SQL> INSERT INTO managers(id, name, salary, hiredate) 2 SELECT empno, ename, sal, hiredate 3 FROM emp 4 WHERE job = 'MANAGER'; 3 rows created.创建带有子查询的 INSERT语句。 不要使用 VALUES 子句. 子查询中的列要与INSERT子句中的列相匹配.
  • 159. 改变一个表中的数据10/20/2018EMP“…更改EMP 表中的一行数据…”EMP EMPNO ENAME JOB ... DEPTNO 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20 ...20 EMPNO ENAME JOB ... DEPTNO 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20 ...
  • 160. UPDATE 语句10/20/2018UPDATE table SET column = value [, column = value, ...] [WHERE condition];使用 UPDATE 语句更改现有的行. 如果需要,可以一次更改多行.
  • 161. 10/20/2018SQL> UPDATE emp 2 SET deptno = 20 3 WHERE empno = 7782; 1 row updated.SQL> UPDATE employee 2 SET deptno = 20; 14 rows updated.更改表中的行使用 WHERE 子句来指定要修改的行. 如果忽略WHERE子句,那么句子中所有的值都将被更改.
  • 162. 用多列子查询进行修改10/20/2018SQL> UPDATE emp 2 SET (job, deptno) = 3 (SELECT job, deptno 4 FROM emp 5 WHERE empno = 7499) 6 WHERE empno = 7698; 1 row updated.更改第 7698号雇员的工作和部门,以与第 7499号雇员进行匹配.
  • 163. 对基于另一个表的行进行更改10/20/2018SQL> UPDATE employee 2 SET deptno = (SELECT deptno 3 FROM emp 4 WHERE empno = 7788) 5 WHERE job = (SELECT job 6 FROM emp 7 WHERE empno = 7788); 2 rows updated.在 UPDATE语句中,使用子查询来进行更改,更改的数据与其它表中的数据有关.
  • 164. 参照完整性错误10/20/2018UPDATE emp * ERROR at line 1: ORA-02291: integrity constraint (USR.EMP_DEPTNO_FK) violated - parent key not foundSQL> UPDATE emp 2 SET deptno = 55 3 WHERE deptno = 10;不存在第55号部门
  • 165. 从一个表中移去一行 10/20/2018 “…从一个表中删去一行…”DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 DEVELOPMENT DETROIT 60 MIS ...DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 60 MIS ...
  • 166. DELETE语句10/20/2018DELETE [FROM] table [WHERE condition];可以使用 DELETE 语句从表中删去现存的行.
  • 167. 从一个表中删去行10/20/2018SQL> DELETE FROM department 2 WHERE dname = 'DEVELOPMENT'; 1 row deleted.SQL> DELETE FROM department; 4 rows deleted.使用 WHERE 子句以指定哪些行应当被删去. 如果忽略WHERE 子句,那么表中所有的数据.
  • 168. 参照另一个表来删除行10/20/2018SQL> DELETE FROM employee 2 WHERE deptno = 3 (SELECT deptno 4 FROM dept 5 WHERE dname ='SALES'); 6 rows deleted.使用子查询,使得 DELETE 语句能从另一个表中删除某些行.
  • 169. 10/20/2018SQL> DELETE FROM dept 2 WHERE deptno = 10;DELETE FROM dept * ERROR at line 1: ORA-02292: integrity constraint (USR.EMP_DEPTNO_FK) violated - child record found删除行时的完整性约束错误如果其它表将本表中的某个键值作为其外键,那么不能删除本表中的这一行
  • 170. 总结语句 INSERT UPDATE DELETE 描述 向表中增加一个新的行 更改表中现存的行 从表中删除现存的行10/20/2018
  • 171. 第八部分 创建和管理表
  • 172. 目标10/20/2018结束本节课后,应当能够完成如下目标: 描述主要的数据库对象 创建表 描述在进行列定义时用到的数据类型 改变表定义 丢弃、改名或截断表
  • 173. 10/20/2018对象 描述 表 存储的基本单元,由行和列组成 视图 一个逻辑的数据集,数据来自一个或者多个表 序列 产生主键值 索引 目的提高查询的性能 同义词 给出对象的别名数据库对象
  • 174. 命名习惯10/20/2018必须以一个字母开始 可以是1-10个字符的长度 仅能包括字符: A–Z, a–z, 0–9, _, $, 和# 不能与本用户拥有的其他对象重名 不能是一个 Oracle服务器的保留字
  • 175. CREATE TABLE语句10/20/2018CREATE [GLOBAL TEMPORARY] TABLE [schema.]table (column datatype [DEFAULT expr][, ...]);必须有 : CREATE TABLE 的权限 拥有存储空间 你可以指定: 表名 列名、列类型、列长度
  • 176. 参考其它用户的表10/20/2018其它用户的表并不在本用户的模式(模式)之中 要使用,则必须在其它表名前面加上前缀.
  • 177. 缺省选项10/20/2018… hiredate DATE DEFAULT SYSDATE, … 合法的值可以是一个直接的量、表达式、或者一个SQL函数. 缺省值的数据类型必须与列的数据类型匹配.为某个列指定一个缺省值.
  • 178. 10/20/2018SQL> CREATE TABLE dept 2 (deptno NUMBER(2), 3 dname VARCHAR2(14), 4 loc VARCHAR2(13)); Table created.确认创建的结果.SQL> DESCRIBE dept Name Null? Type ------------------------ -------- --------- DEPTNO NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13)创建表创建表
  • 179. Oracle数据库中的表10/20/2018用户表 由用户创建和维护的一系列表的集合 包含用户的信息 数据字典 由Oracle 服务器创建和维护的一系列表的集合 包含数据库的信息
  • 180. 查询数据字典10/20/2018查看用户所拥有的数据类型.查看用户拥有的表、视图、同义词、序列.SQL> SELECT * 2 FROM user_tables;SQL> SELECT DISTINCT object_type 2 FROM user_objects;SQL> SELECT * 2 FROM user_catalog;描述用户拥有的表.
  • 181. 10/20/2018数据类型 描述 VARCHAR2(size) 变长字符数据,size为最大长度 CHAR(size) 定长字符数据, size为长度 NUMBER(p,s) 数字类型,p为 精度,s为刻度 DATE 日期和时间值 LONG 可以保存最大长度为 2GB的字符数据 CLOB 可以保存最大长度为 4GB的单字节字符 数据 RAW and LONG RAW 存储二进制数据 BLOB 最大为 4GB的二进制数据类型 BFILE 在外部文件中存储的二进制数据;最大为 4GB数据类型(基于Oracle)
  • 182. 数据类型(1/2)Informix 数据类型Oracle 数据类型Char(n) 长度范围:1~32767Char(n) 长度范围:1~32767 建议改为VARCHAR2类型Date 时间只能到日,即年月日Date 时间到秒,即年月日时分秒Datetime 时间可以到豪秒,即年月日时分秒豪秒Date 时间到秒,即年月日时分秒Decimal(p,s) 长度范围:-1.0E+130~1.0E+123 最大位数:32位Decimal 最大位数:38位 Number子类型
  • 183. 数据类型(2/2)Informix 数据类型Oracle 数据类型Float(n) 最大位数:16位 Double precision与Float类型相同Float 最大位数:38位 Number子类型Interval没有相应的类型,可用Int代替Serial(n) 数值非自动增加,必须用于唯一索引列没有相应的类型,可用Number(p)代替Smallint 数值范围:-32767~32767Smallint 最大位数:38位 Number子类型Varchar(m,r) 取值范围:1~255个字符Varchar2(n)
  • 184. 10/20/2018CREATE TABLE table [(column, column...)] AS subquery;使用 CREATE TABLE语句和AS子查询选项来创建一个表并插入数据. 创建的表的列要和子查询的列相匹配. 使用子查询来创建一个表
  • 185. 10/20/2018SQL> CREATE TABLE dept30 2 AS 3 SELECT empno, ename, sal*12 ANNSAL, hiredate 4 FROM emp 5 WHERE deptno = 30; Table created. Name Null? Type ---------------------------- -------- ----- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) ANNSAL NUMBER HIREDATE DATESQL> DESCRIBE dept30使用子查询来创建一个表
  • 186. ALTER TABLE命令10/20/2018ALTER TABLE table ADD (column datatype [DEFAULT expr] [, column datatype]...);ALTER TABLE table MODIFY (column datatype [DEFAULT expr] [, column datatype]...);使用 ALTER TABLE 命令来进行: 增加一个新列 更改一个现存的列 为新列定义缺省的值
  • 187. 增加一列10/20/2018DEPT30 EMPNO ENAME ANNSAL HIREDATE ------ ---------- -------- 7698 BLAKE 34200 01-MAY-81 7654 MARTIN 15000 28-SEP-81 7499 ALLEN 19200 20-FEB-81 7844 TURNER 18000 08-SEP-81 ...“…为表DEPT30 增加一个新列…”DEPT30 EMPNO ENAME ANNSAL HIREDATE ------ ---------- -------- 7698 BLAKE 34200 01-MAY-81 7654 MARTIN 15000 28-SEP-81 7499 ALLEN 19200 20-FEB-81 7844 TURNER 18000 08-SEP-81 ...JOBJOB新列
  • 188. 增加一列使用ADD子句来增加一个列.10/20/2018 EMPNO ENAME ANNSAL HIREDATE JOB --------- ---------- --------- --------- ---- 7698 BLAKE 34200 01-MAY-81 7654 MARTIN 15000 28-SEP-81 7499 ALLEN 19200 20-FEB-81 7844 TURNER 18000 08-SEP-81 ... 6 rows selected.SQL> ALTER TABLE dept30 2 ADD (job VARCHAR2(9)); Table altered.新列已经增加,成为最后一个列.
  • 189. 更改一个现存的列10/20/2018SQL> ALTER TABLE dept30 2 MODIFY (ename VARCHAR2(15)); Table altered.可以更改一个现存列的数据类型、长度、以及缺省值. 对于缺省值的修改只影响到以后会修改的列.
  • 190. 丢弃一个列10/20/2018SQL> ALTER TABLE dept30 2 DROP COLUMN job ; Table altered.使用 DROP COLUMN来丢弃一个表中将来不使用的列.
  • 191. 丢弃一个表10/20/2018SQL> DROP TABLE dept30; Table dropped.表中所有的数据和结构都被删除了. 所有未决的事务都被提交. 此表上所有的索引全部被删除. 这个操作是不能回滚的.
  • 192. 改变一个对象的名称10/20/2018SQL> RENAME dept TO department; Table renamed.更改表、视图、同义词、序列等对象的名称,执行RENAME 命令就可以了. 操作用户必须是对象的拥有者.
  • 193. 截断一个表(Oracle特有)10/20/2018SQL> TRUNCATE TABLE department; Table truncated.TRUNCATE TABLE 语句功能: 移除表中所有的数据行 释放表中的存储空间 在执行 TRUNCATE语句后,不能对该操作回滚。 可以用DELETE语句来做相同的操作,不过DELETE并不立即释放空间,因而可以回滚.
  • 194. 总结语句 CREATE TABLE ALTER TABLE DROP TABLE RENAME TRUNCATE 描述 创建一个表 更改表结构 废弃一个表,包括它的结构和数据行 更改对象的名称 从表中移除所有的行,并将这些空间释放掉10/20/2018
  • 195. 总结引子 基本SQL 对数据进行限定和排序 处理单行函数 从多个表中获取数据 使用分组函数对数据进行聚集 子查询 操作数据——DML语句 创建和管理表
  • 196. 谢谢聆听服务支持中心 余 蕾 2009.6