SQL语言基础
SQL語言基礎
一：前言
二：SQL語言概述
二???? ——? 1：SQL語言特點
二???? ——? 2：SQL語言分類
二???? ——? 3：SQL 語言編寫規則
三：用戶模式
三???? ——? 1：模式與模式對象
三???? ——? 2：實例模式 SCOTT
四：檢索數據： select?
四???? ——? 1：簡單查詢
登錄 scott用戶
1 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 Code 1 [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> View Code四???? ——? 2：檢索指定的列
1 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 Code 1 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> View Code四???? ——? 3：帶有表達式的select 字句
四???? ——? 4： 為列指定別名
四???? ——? 5：? 顯示不重復記錄? distinct
五? ：刷選查詢
五???? —— 1：比較刷選
1 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> View Code五???? —— 2：使用特殊關鍵字篩選:? like? /? in??? /? between…and / not between..and / is null
1 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 Code五???? —— 3：邏輯篩選?? AND? OR? NOT
五???? —— 4：分組查詢
1 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 Code 1 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 Code 1 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> View Code五???? —— 5： 排序查詢
1 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> View Code五???? —— 6： 多表關聯查詢
五???? —— 6_____1：表別名
1 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> View Code五???? —— 6_____2：內鏈接
五???? —— 6_____3：外連接 （左連接、右連接、全連接）
1 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 Code 1 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 View Code全連接
五???? —— 6_____4： 自然連接
1 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 View Code五???? —— 6_____5： 自連接
1 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 View Code五???? —— 6_____6：交叉連接
六：Oracle 常用系統函數
.
六???? ——1 ：字符類函數
六???? ——1_____1： ASCII (c) 函數 和 CHR（i）函數
1 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 Code 1 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> View Code六???? ——1_____2： concat(s1,s2)? 連接 函數
1 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> View Code六???? ——1_____3： initcap(s)? 字符串第一個字母大寫函數
1 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> View Code六???? ——1_____4：instr(s1,s2[,i][,i])?? 返回字符在字符串第N次出現位置的?? 函數
1 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 View Code六???? ——1_____5：length(s)? 求字符串長度的函數
1 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 View Code六???? ——1_____6：lower(s) 函數? upper(s)?? 大小 函數
1 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> View Code六???? ——1_____7： ltrim(S1,S2)? 刪除字符串s1左邊字符串s2 、 rtrim(s1,s2) 刪除字符串s1右邊的字符串s2?? 、trim(s1,s2) 刪除字符串s1 兩邊字符串s2
1 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 View Code六???? ——1_____8： replace(s1,s2[,s3])? 替換函數
1 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> View Code六???? ——1_____9： substr(s,i[,j]) 截取函數
1 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> View Code六???? ——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)?? 增加月份 函數
1 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> View Code六???? ——4：轉換類函數
六???? ——5：集合類函數
七：子查詢
七???? ——1：子查詢
1 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> View Code七???? ——2：單行子查詢
1 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 View Code七???? ——3：多行子查詢
七???? ——3_____1： 使用? in? 運算符
七???? ——3_____2： 使用 any 運算符
查詢工資大于部門編號為10號的任意一個員工工資即可
1 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> View Code七???? ——3_____3： 使用 all 運算符
1 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> View Code七???? ——4：? 關聯查詢
八：操作數據庫
八???? ——1： 插入數據 insert
八????? ——1_____1：單條插入數據
八????? ——1_____2： 批量插入數據
八????? ——2 ： 更新數據 UPDAET
1 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> View Code八????? ——3：刪除數據? Delete truncate
八????? ——3_____1：delete 刪除數據
八????? ——3_____2：truncate
九：事務概述
九????? ——1：操作事務
九????? ——1_____1： 提交事務
九????? ——1_____2：回滾事務 rollback
—————————————————————————————————————————————————————————————
—————————————————————————————————————————————————————————————
——————————————————————————————————————————————
總結
- 上一篇： 莫旗政务服务中心通过“互联网＋智慧城市”
- 下一篇： 适用于zTree 、EasyUI tre