SQL语言基础
一：前言
二：SQL语言概述
二 —— 1：SQL语言特点
二 —— 2：SQL语言分类
二 —— 3：SQL 语言编写规则
三：用户模式
三 —— 1：模式与模式对象
三 —— 2：实例模式 SCOTT
四：检索数据： select
四 —— 1：简单查询
登录 scott用户
View Code1 2 [oracle@localhost ~]$ sqlplus / as sysdba; 3 4 SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 24 15:26:39 2017 5 6 Copyright (c) 1982, 2011, Oracle. All rights reserved. 7 8 9 Connected to: 10 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production 11 With the Partitioning, OLAP, Data Mining and Real Application Testing options 12 13 ---访问用户 14 15:16:41 SYS@orcl> conn scott/oracle 15 ERROR: 16 ORA-28000: the account is locked 17 --用户被锁 18 ---解锁 19 SYS@orcl> alter user scott account unlock; 20 21 User altered. 22 ---切换用户 23 SYS@orcl> conn scott/oracle 24 ERROR: 25 ORA-01017: invalid username/password; logon denied ---登录密码错误 26 Warning: You are no longer connected to ORACLE. 27 ---修改用户密码： 28 SYS@orcl> alter user scott identified by scott; 29 30 User altered. 31 ----登录 32 SYS@orcl> conn scott/scott 33 Connected. 34 SCOTT@orcl>
View Code1 [oracle@localhost ~]$ sqlplus / as sysdba; 2 3 SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 24 15:28:04 2017 4 5 Copyright (c) 1982, 2011, Oracle. All rights reserved. 6 7 8 Connected to: 9 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production 10 With the Partitioning, OLAP, Data Mining and Real Application Testing options 11 12 SYS@orcl> conn scott/scott; 13 Connected. 14 SCOTT@orcl> select * from dept; 15 16 DEPTNO DNAME LOC 17 ---------- -------------- ------------- 18 10 ACCOUNTING NEW YORK 19 20 RESEARCH DALLAS 20 30 SALES CHICAGO 21 40 OPERATIONS BOSTON 22 23 SCOTT@orcl>
四 —— 2：检索指定的列
View Code1 SCOTT@orcl> select job,ename,empno from emp; 2 3 JOB ENAME EMPNO 4 --------- ---------- ---------- 5 CLERK SMITH 7369 6 SALESMAN ALLEN 7499 7 SALESMAN WARD 7521 8 MANAGER JONES 7566 9 SALESMAN MARTIN 7654 10 MANAGER BLAKE 7698 11 MANAGER CLARK 7782 12 ANALYST SCOTT 7788 13 PRESIDENT KING 7839 14 SALESMAN TURNER 7844 15 CLERK ADAMS 7876 16 17 JOB ENAME EMPNO 18 --------- ---------- ---------- 19 CLERK JAMES 7900 20 ANALYST FORD 7902 21 CLERK MILLER 7934 22 23 14 rows selected. 24
View Code1 SCOTT@orcl> select rowid ,job,ename from emp; 2 3 ROWID JOB ENAME 4 ------------------ --------- ---------- 5 AAASYzAAEAAAACXAAA CLERK SMITH 6 AAASYzAAEAAAACXAAB SALESMAN ALLEN 7 AAASYzAAEAAAACXAAC SALESMAN WARD 8 AAASYzAAEAAAACXAAD MANAGER JONES 9 AAASYzAAEAAAACXAAE SALESMAN MARTIN 10 AAASYzAAEAAAACXAAF MANAGER BLAKE 11 AAASYzAAEAAAACXAAG MANAGER CLARK 12 AAASYzAAEAAAACXAAH ANALYST SCOTT 13 AAASYzAAEAAAACXAAI PRESIDENT KING 14 AAASYzAAEAAAACXAAJ SALESMAN TURNER 15 AAASYzAAEAAAACXAAK CLERK ADAMS 16 17 ROWID JOB ENAME 18 ------------------ --------- ---------- 19 AAASYzAAEAAAACXAAL CLERK JAMES 20 AAASYzAAEAAAACXAAM ANALYST FORD 21 AAASYzAAEAAAACXAAN CLERK MILLER 22 23 14 rows selected. 24 25 SCOTT@orcl>
四 —— 3：带有表达式的select 字句
四 —— 4： 为列指定别名
四 —— 5： 显示不重复记录 distinct
五 ：刷选查询
五 —— 1：比较刷选
View Code1 2 SCOTT@orcl> select empno,ename,sal from emp where sal>150; 3 4 EMPNO ENAME SAL 5 ---------- ---------- ---------- 6 7369 SMITH 800 7 7499 ALLEN 1600 8 7521 WARD 1250 9 7566 JONES 2975 10 7654 MARTIN 1250 11 7698 BLAKE 2850 12 7782 CLARK 2450 13 7788 SCOTT 3000 14 7839 KING 5000 15 7844 TURNER 1500 16 7876 ADAMS 1100 17 18 EMPNO ENAME SAL 19 ---------- ---------- ---------- 20 7900 JAMES 950 21 7902 FORD 3000 22 7934 MILLER 1300 23 24 14 rows selected. 25 26 SCOTT@orcl> select empno,ename,sal from emp where sal <> all(3000,950,800); 27 28 EMPNO ENAME SAL 29 ---------- ---------- ---------- 30 7499 ALLEN 1600 31 7521 WARD 1250 32 7566 JONES 2975 33 7654 MARTIN 1250 34 7698 BLAKE 2850 35 7782 CLARK 2450 36 7839 KING 5000 37 7844 TURNER 1500 38 7876 ADAMS 1100 39 7934 MILLER 1300 40 41 10 rows selected. 42 43 SCOTT@orcl>
五 —— 2：使用特殊关键字筛选: like / in / between…and / not between..and / is null
View Code1 SCOTT@orcl> select empno,ename,job from emp where ename like 'S%'; 2 3 EMPNO ENAME JOB 4 ---------- ---------- --------- 5 7369 SMITH CLERK 6 7788 SCOTT ANALYST 7 8 SCOTT@orcl> 9 10 11
View Code1 17:21:01 SYS@orcl> conn hr/hr 2 Connected. 3 17:21:11 HR@orcl> select street_address from locations where state_province is null; 4 5 STREET_ADDRESS 6 ---------------------------------------- 7 1297 Via Cola di Rie 8 93091 Calle della Testa 9 9450 Kamiya-cho 10 40-5-12 Laogianggen 11 198 Clementi North 12 8204 Arthur St 13 14 6 rows selected. 15 16 17:21:36 HR@orcl>
五 —— 3：逻辑筛选 AND OR NOT
五 —— 4：分组查询
View Code1 2 17:29:57 SCOTT@orcl> select deptno,job from emp group by deptno,job order by deptno; 3 4 DEPTNO JOB 5 ---------- --------- 6 10 CLERK 7 10 MANAGER 8 10 PRESIDENT 9 20 ANALYST 10 20 CLERK 11 20 MANAGER 12 30 CLERK 13 30 MANAGER 14 30 SALESMAN 15 16 9 rows selected. 17 18 17:30:28 SCOTT@orcl>
View Code1 17:31:29 SCOTT@orcl> select deptno ,avg(sal) from emp group by deptno; 2 3 DEPTNO AVG(SAL) 4 ---------- ---------- 5 30 1566.66667 6 20 2175 7 10 2916.66667 8 9 17:31:53 SCOTT@orcl> 10
View Code1 17:34:13 SCOTT@orcl> select deptno , avg(sal) from emp group by deptno having avg(sal) >200; 2 3 DEPTNO AVG(SAL) 4 ---------- ---------- 5 30 1566.66667 6 20 2175 7 10 2916.66667 8 9 17:34:14 SCOTT@orcl>
五 —— 5： 排序查询
View Code1 17:36:05 SCOTT@orcl> select deptno,empno,ename from emp order by deptno,empno; 2 3 DEPTNO EMPNO ENAME 4 ---------- ---------- ---------- 5 10 7782 CLARK 6 10 7839 KING 7 10 7934 MILLER 8 20 7369 SMITH 9 20 7566 JONES 10 20 7788 SCOTT 11 20 7876 ADAMS 12 20 7902 FORD 13 30 7499 ALLEN 14 30 7521 WARD 15 30 7654 MARTIN 16 17 DEPTNO EMPNO ENAME 18 ---------- ---------- ---------- 19 30 7698 BLAKE 20 30 7844 TURNER 21 30 7900 JAMES 22 23 14 rows selected. 24 25 17:36:21 SCOTT@orcl>
五 —— 6： 多表关联查询
五 —— 6_____1：表别名
View Code1 17:41:35 SCOTT@orcl> select e.empno,e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and e.job='MANAGER'; 2 3 EMPNO ENAME DNAME 4 ---------- ---------- -------------- 5 7782 CLARK ACCOUNTING 6 7566 JONES RESEARCH 7 7698 BLAKE SALES 8 9 17:41:37 SCOTT@orcl>
五 —— 6_____2：内链接
五 —— 6_____3：外连接 （左连接、右连接、全连接）
View Code1 2 20:02:06 SCOTT@orcl> insert into emp(empno,ename,job) values(9527,'EAST','SAESMAN'); 3 4 1 row created. 5 6 20:02:08 SCOTT@orcl> select e.empno,e.ename,e.job,d.deptno,d.dname from emp e left join dept d on e.deptno=d.deptno; 7 8 EMPNO ENAME JOB DEPTNO DNAME 9 ---------- ---------- --------- ---------- -------------- 10 7934 MILLER CLERK 10 ACCOUNTING 11 7839 KING PRESIDENT 10 ACCOUNTING 12 7782 CLARK MANAGER 10 ACCOUNTING 13 7902 FORD ANALYST 20 RESEARCH 14 7876 ADAMS CLERK 20 RESEARCH 15 7788 SCOTT ANALYST 20 RESEARCH 16 7566 JONES MANAGER 20 RESEARCH 17 7369 SMITH CLERK 20 RESEARCH 18 7900 JAMES CLERK 30 SALES 19 7844 TURNER SALESMAN 30 SALES 20 7698 BLAKE MANAGER 30 SALES 21 22 EMPNO ENAME JOB DEPTNO DNAME 23 ---------- ---------- --------- ---------- -------------- 24 7654 MARTIN SALESMAN 30 SALES 25 7521 WARD SALESMAN 30 SALES 26 7499 ALLEN SALESMAN 30 SALES 27 9527 EAST SAESMAN 28 29 15 rows selected. 30 31 20:02:57 SCOTT@orcl>
View Code1 20:02:06 SCOTT@orcl> insert into emp(empno,ename,job) values(9527,'EAST','SAESMAN'); 2 3 1 row created. 4 5 20:02:08 SCOTT@orcl> select e.empno,e.ename,e.job,d.deptno,d.dname from emp e left join dept d on e.deptno=d.deptno; 6 7 EMPNO ENAME JOB DEPTNO DNAME 8 ---------- ---------- --------- ---------- -------------- 9 7934 MILLER CLERK 10 ACCOUNTING 10 7839 KING PRESIDENT 10 ACCOUNTING 11 7782 CLARK MANAGER 10 ACCOUNTING 12 7902 FORD ANALYST 20 RESEARCH 13 7876 ADAMS CLERK 20 RESEARCH 14 7788 SCOTT ANALYST 20 RESEARCH 15 7566 JONES MANAGER 20 RESEARCH 16 7369 SMITH CLERK 20 RESEARCH 17 7900 JAMES CLERK 30 SALES 18 7844 TURNER SALESMAN 30 SALES 19 7698 BLAKE MANAGER 30 SALES 20 21 EMPNO ENAME JOB DEPTNO DNAME 22 ---------- ---------- --------- ---------- -------------- 23 7654 MARTIN SALESMAN 30 SALES 24 7521 WARD SALESMAN 30 SALES 25 7499 ALLEN SALESMAN 30 SALES 26 9527 EAST SAESMAN 27 28 15 rows selected. 29 30 20:02:57 SCOTT@orcl> ho clear 31 32 33 20:16:54 SCOTT@orcl> select e.empno,e.ename,e.job,d.deptno,d.dname from emp e right join dept d on e.deptno=e.deptno; 34 35 EMPNO ENAME JOB DEPTNO DNAME 36 ---------- ---------- --------- ---------- -------------- 37 7369 SMITH CLERK 10 ACCOUNTING 38 7499 ALLEN SALESMAN 10 ACCOUNTING 39 7521 WARD SALESMAN 10 ACCOUNTING 40 7566 JONES MANAGER 10 ACCOUNTING 41 7654 MARTIN SALESMAN 10 ACCOUNTING 42 7698 BLAKE MANAGER 10 ACCOUNTING 43 7782 CLARK MANAGER 10 ACCOUNTING 44 7788 SCOTT ANALYST 10 ACCOUNTING 45 7839 KING PRESIDENT 10 ACCOUNTING 46 7844 TURNER SALESMAN 10 ACCOUNTING 47 7876 ADAMS CLERK 10 ACCOUNTING 48 49 EMPNO ENAME JOB DEPTNO DNAME 50 ---------- ---------- --------- ---------- -------------- 51 7900 JAMES CLERK 10 ACCOUNTING 52 7902 FORD ANALYST 10 ACCOUNTING 53 7934 MILLER CLERK 10 ACCOUNTING 54 7369 SMITH CLERK 20 RESEARCH 55 7499 ALLEN SALESMAN 20 RESEARCH 56 7521 WARD SALESMAN 20 RESEARCH 57 7566 JONES MANAGER 20 RESEARCH 58 7654 MARTIN SALESMAN 20 RESEARCH 59 7698 BLAKE MANAGER 20 RESEARCH 60 7782 CLARK MANAGER 20 RESEARCH 61 7788 SCOTT ANALYST 20 RESEARCH 62 63 EMPNO ENAME JOB DEPTNO DNAME 64 ---------- ---------- --------- ---------- -------------- 65 7839 KING PRESIDENT 20 RESEARCH 66 7844 TURNER SALESMAN 20 RESEARCH 67 7876 ADAMS CLERK 20 RESEARCH 68 7900 JAMES CLERK 20 RESEARCH 69 7902 FORD ANALYST 20 RESEARCH 70 7934 MILLER CLERK 20 RESEARCH 71 7369 SMITH CLERK 30 SALES 72 7499 ALLEN SALESMAN 30 SALES 73 7521 WARD SALESMAN 30 SALES 74 7566 JONES MANAGER 30 SALES 75 7654 MARTIN SALESMAN 30 SALES 76 77 EMPNO ENAME JOB DEPTNO DNAME 78 ---------- ---------- --------- ---------- -------------- 79 7698 BLAKE MANAGER 30 SALES 80 7782 CLARK MANAGER 30 SALES 81 7788 SCOTT ANALYST 30 SALES 82 7839 KING PRESIDENT 30 SALES 83 7844 TURNER SALESMAN 30 SALES 84 7876 ADAMS CLERK 30 SALES 85 7900 JAMES CLERK 30 SALES 86 7902 FORD ANALYST 30 SALES 87 7934 MILLER CLERK 30 SALES 88 7369 SMITH CLERK 40 OPERATIONS 89 7499 ALLEN SALESMAN 40 OPERATIONS 90 91 EMPNO ENAME JOB DEPTNO DNAME 92 ---------- ---------- --------- ---------- -------------- 93 7521 WARD SALESMAN 40 OPERATIONS 94 7566 JONES MANAGER 40 OPERATIONS 95 7654 MARTIN SALESMAN 40 OPERATIONS 96 7698 BLAKE MANAGER 40 OPERATIONS 97 7782 CLARK MANAGER 40 OPERATIONS 98 7788 SCOTT ANALYST 40 OPERATIONS 99 7839 KING PRESIDENT 40 OPERATIONS 100 7844 TURNER SALESMAN 40 OPERATIONS 101 7876 ADAMS CLERK 40 OPERATIONS 102 7900 JAMES CLERK 40 OPERATIONS 103 7902 FORD ANALYST 40 OPERATIONS 104 105 EMPNO ENAME JOB DEPTNO DNAME 106 ---------- ---------- --------- ---------- -------------- 107 7934 MILLER CLERK 40 OPERATIONS 108 109 56 rows selected. 110
全连接
五 —— 6_____4： 自然连接
View Code1 2 20:30:38 SCOTT@orcl> select e.empno,e.ename,e.job,dname from emp e natural join dept d where sal>2000; 3 4 EMPNO ENAME JOB DNAME 5 ---------- ---------- --------- -------------- 6 7782 CLARK MANAGER ACCOUNTING 7 7839 KING PRESIDENT ACCOUNTING 8 7902 FORD ANALYST RESEARCH 9 7788 SCOTT ANALYST RESEARCH 10 7566 JONES MANAGER RESEARCH 11 7698 BLAKE MANAGER SALES 12 13 6 rows selected. 14 15 20:31:19 SCOTT@orcl
五 —— 6_____5： 自连接
View Code1 20:45:39 SCOTT@orcl> select em2.ename as "上层管理者",em1.ename as "下属员工" from emp em1 left join emp em2 on em1.mgr=em2.empno order by em1.mgr; 2 3 ?????????? ???????? 4 ---------- ---------- 5 JONES FORD 6 JONES SCOTT 7 BLAKE JAMES 8 BLAKE TURNER 9 BLAKE MARTIN 10 BLAKE WARD 11 BLAKE ALLEN 12 CLARK MILLER 13 SCOTT ADAMS 14 KING CLARK 15 KING BLAKE
五 —— 6_____6：交叉连接
六：Oracle 常用系统函数
.
六 ——1 ：字符类函数
六 ——1_____1： ASCII (c) 函数 和 CHR（i）函数
View Code1 21:05:16 SCOTT@orcl> select ascii('Z') Z,ascii('H') H,ascii('D') D ,ascii('') space from dual; 2 3 Z H D SPACE 4 ---------- ---------- ---------- ---------- 5 90 72 68 6 7 21:05:18 SCOTT@orcl>
View Code1 21:05:16 SCOTT@orcl> select ascii('Z') Z,ascii('H') H,ascii('D') D ,ascii('') space from dual; 2 3 Z H D SPACE 4 ---------- ---------- ---------- ---------- 5 90 72 68 6 7 21:05:18 SCOTT@orcl> select chr(90),chr(72),chr(68),(32) s from dual; 8 9 C C C S 10 - - - ---------- 11 Z H D 32 12 13 21:06:39 SCOTT@orcl>
六 ——1_____2： concat(s1,s2) 连接 函数
View Code1 21:09:36 SCOTT@orcl> select concat('Hello','World') information from dual; 2 3 INFORMATIO 4 ---------- 5 HelloWorld 6 7 21:10:20 SCOTT@orcl>
六 ——1_____3： initcap(s) 字符串第一个字母大写函数
View Code1 2 21:12:21 SCOTT@orcl> select initcap('oh my god') information from dual; 3 4 INFORMATI 5 --------- 6 Oh My God 7 8 21:12:24 SCOTT@orcl>
六 ——1_____4：instr(s1,s2[,i][,i]) 返回字符在字符串第N次出现位置的 函数
View Code1 21:15:48 SCOTT@orcl> select instr('Oracle11g','1',3,2) abc from dual; 2 3 ABC 4 ---------- 5 8 6 7 21:16:20 SCOTT@orcl> 8
六 ——1_____5：length(s) 求字符串长度的函数
View Code1 21:20:31 SCOTT@orcl> select e.empno,e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno where length(e.ename) >5; 2 3 EMPNO ENAME DNAME 4 ---------- ---------- -------------- 5 7934 MILLER ACCOUNTING 6 7654 MARTIN SALES 7 7844 TURNER SALES 8 9 21:20:33 SCOTT@orcl> 10
六 ——1_____6：lower(s) 函数 upper(s) 大小 函数
View Code1 22:47:41 HR@orcl> conn hr/hr 2 Connected. 3 22:47:55 HR@orcl> select employee_id,lower(first_name),upper(last_name) from employees where lower(first_name) like 'a%'; 4 5 EMPLOYEE_ID LOWER(FIRST_NAME) UPPER(LAST_NAME) 6 ----------- -------------------- ------------------------- 7 103 alexander HUNOLD 8 115 alexander KHOO 9 121 adam FRIPP 10 147 alberto ERRAZURIZ 11 158 allan MCEWEN 12 167 amit BANDA 13 175 alyssa HUTTON 14 185 alexis BULL 15 187 anthony CABRIO 16 196 alana WALSH 17 18 10 rows selected. 19 20 22:47:58 HR@orcl>
六 ——1_____7： ltrim(S1,S2) 删除字符串s1左边字符串s2 、 rtrim(s1,s2) 删除字符串s1右边的字符串s2 、trim(s1,s2) 删除字符串s1 两边字符串s2
View Code1 23:01:56 HR@orcl> select ltrim( '####TTTT####','#') ltrim_,rtrim( '####TTTT####','#') rtrim_,trim('#' from '####TTTT####') trim_ from dual; 2 3 LTRIM_ RTRIM_ TRIM 4 -------- -------- ---- 5 TTTT#### ####TTTT TTTT 6 7 23:01:58 HR@orcl> 8
六 ——1_____8： replace(s1,s2[,s3]) 替换函数
View Code1 23:04:28 HR@orcl> select replace('Bad Luck Bad Gril','Bad','Good') test from dual; 2 3 TEST 4 ------------------- 5 Good Luck Good Gril 6 7 23:04:34 HR@orcl>
六 ——1_____9： substr(s,i[,j]) 截取函数
View Code1 23:06:33 HR@orcl> select substr('MessageBox',8,3) from dual; 2 3 SUB 4 --- 5 Box 6 7 23:06:54 HR@orcl>
六 ——2：数字类函数
六 ——2_____1： ceil（n） 取 接近2的最小值
六 ——2_____2： round(n1,n2) 四舍五入 函数
六 ——2_____3：power(n1,n2)
六 ——3： 日期和时间类函数
六 ——3_____1：sysdate() 当前系统时间 函数
六 ——3_____2：add_month(d,i) 增加月份 函数
View Code1 23:30:41 HR@orcl> select sysdate ,to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') nowtime,to_char(add_months(sysdate,6),'yyyy-MM-dd HH24:mi:ss') add_month from dual; 2 3 SYSDATE NOWTIME ADD_MONTH 4 --------- ------------------- ------------------- 5 24-DEC-17 2017-12-24 23:31:17 2018-06-24 23:31:17 6 7 23:31:17 HR@orcl> 8 23:31:17 HR@orcl>
六 ——4：转换类函数
六 ——5：集合类函数
七：子查询
七 ——1：子查询
View Code1 23:50:32 HR@orcl> conn scott/scott 2 Connected. 3 23:50:41 SCOTT@orcl> select empno,ename,job from emp where deptno=(select deptno from dept where dname='RESEARCH'); 4 5 EMPNO ENAME JOB 6 ---------- ---------- --------- 7 7369 SMITH CLERK 8 7566 JONES MANAGER 9 7788 SCOTT ANALYST 10 7876 ADAMS CLERK 11 7902 FORD ANALYST 12 13 23:50:43 SCOTT@orcl>
七 ——2：单行子查询
View Code1 23:53:17 SCOTT@orcl> conn scott/scott 2 Connected. 3 23:53:25 SCOTT@orcl> select empno,ename,sal from emp where sal >(select min(sal) from emp) and sal <(select max(sal) from emp); 4 5 EMPNO ENAME SAL 6 ---------- ---------- ---------- 7 7499 ALLEN 1600 8 7521 WARD 1250 9 7566 JONES 2975 10 7654 MARTIN 1250 11 7698 BLAKE 2850 12 7782 CLARK 2450 13 7788 SCOTT 3000 14 7844 TURNER 1500 15 7876 ADAMS 1100 16 7900 JAMES 950 17 7902 FORD 3000 18 19 EMPNO ENAME SAL 20 ---------- ---------- ---------- 21 7934 MILLER 1300 22 23 12 rows selected. 24 25 23:54:08 SCOTT@orcl> 26 27
七 ——3：多行子查询
七 ——3_____1： 使用 in 运算符
七 ——3_____2： 使用 any 运算符
查询工资大于部门编号为10号的任意一个员工工资即可
View Code1 23:59:17 SCOTT@orcl> select empno,ename ,job ,sal from emp where sal > any (select sal from emp where deptno=10) and deptno <>10; 2 3 EMPNO ENAME JOB SAL 4 ---------- ---------- --------- ---------- 5 7788 SCOTT ANALYST 3000 6 7902 FORD ANALYST 3000 7 7566 JONES MANAGER 2975 8 7698 BLAKE MANAGER 2850 9 7499 ALLEN SALESMAN 1600 10 7844 TURNER SALESMAN 1500 11 12 6 rows selected. 13 14 23:59:20 SCOTT@orcl>
七 ——3_____3： 使用 all 运算符
View Code1 00:01:49 SCOTT@orcl> select empno,ename ,job ,sal from emp where sal > all(select sal from emp where deptno=30); 2 3 EMPNO ENAME JOB SAL 4 ---------- ---------- --------- ---------- 5 7566 JONES MANAGER 2975 6 7902 FORD ANALYST 3000 7 7788 SCOTT ANALYST 3000 8 7839 KING PRESIDENT 5000 9 10 00:02:12 SCOTT@orcl>
七 ——4： 关联查询
八：操作数据库
八 ——1： 插入数据 insert
八 ——1_____1：单条插入数据
八 ——1_____2： 批量插入数据
View Code1 HR@orcl> create table jobs_temp( 2 2 job_id varchar2(10) primary key, 3 3 job_title varchar2(35) not null, 4 4 min_salay number(6), 5 5 max_salary number(6)); 6 7 Table created. 8 9 HR@orcl> insert into jobs_temp select * from jobs where jobs.max_salary>1000; 10 11 20 rows created. 12 13 HR@orcl> 14
八 ——2 ： 更新数据 UPDAET
View Code1 SCOTT@orcl> select empno,ename,job,sal from emp where rownum <=5; 2 3 EMPNO ENAME JOB SAL 4 ---------- ---------- --------- ---------- 5 9527 EAST SAESMAN 6 7369 SMITH CLERK 800 7 7499 ALLEN SALESMAN 1600 8 7521 WARD SALESMAN 1250 9 7566 JONES MANAGER 2975 10 11 SCOTT@orcl> update emp set sal=sal*2 where job='SALESMAN'; 12 13 4 rows updated. 14 15 SCOTT@orcl> select empno,ename,job,sal from emp where job='SALESMAN' and rownum <=5; 16 17 EMPNO ENAME JOB SAL 18 ---------- ---------- --------- ---------- 19 7499 ALLEN SALESMAN 3200 20 7521 WARD SALESMAN 2500 21 7654 MARTIN SALESMAN 2500 22 7844 TURNER SALESMAN 3000 23 24 SCOTT@orcl>
八 ——3：删除数据 Delete truncate
八 ——3_____1：delete 删除数据
八 ——3_____2：truncate
九：事务概述
九 ——1：操作事务
九 ——1_____1： 提交事务
九 ——1_____2：回滚事务 rollback
—————————————————————————————————————————————————————————————
—————————————————————————————————————————————————————————————
——————————————————————————————————————————————