ORACLE数据库学习笔记(01)
select 语句笔记
SQL> select *from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> commit;
提交完成。
SQL> select *from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 Development dada
60 kkkk ghan
70 backrowboy back
已选择7行。
SQL> rollback; (回滚)
回退已完成。
------------------------------
事务保存点设置
savepoint created.
SQL> select *from dept;
DEPTNO DNAME LOC
---------- -------------- -------
10 ppppp NEW YOR
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 Development dada
60 kkkk ghan
80 ghan han
90 backrowboy boy
已选择8行。
SQL> rollback to update01;
回退到update01这个保存点，
SQL> select ename,sal,sal+300
2 from emp;
ENAME SAL SAL+300
---------- ---------- ----------
SMITH 800 1100
ALLEN 1600 1900
WARD 1250 1550
JONES 2975 3275
MARTIN 1250 1550
BLAKE 2850 3150
CLARK 2450 2750
SCOTT 3000 3300
KING 5000 5300
TURNER 1500 1800
ADAMS 1100 1400
JAMES 950 1250
FORD 3000 3300
MILLER 1300 1600
ghan
tank 900 1200
han 17000 17300
已选择17行。
SQL>
别名:(可用AS后面加名，或在原表名后+空格+)
SQL> select ename AS name,sal salary
2 from emp;
NAME SALARY
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
ghan
tank 900
han 17000
已选择17行。
字符串连接符(||)
SQL> select ename||job AS Employees
2 from emp;
EMPLOYEES
-------------------
SMITHCLERK
ALLENSALESMAN
WARDSALESMAN
JONESMANAGER
MARTINSALESMAN
BLAKEMANAGER
CLARKMANAGER
SCOTTANALYST
KINGPRESIDENT
TURNERSALESMAN
ADAMSCLERK
JAMESCLERK
FORDANALYST
MILLERCLERK
ghan
tank
han
已选择17行。
去掉重复的关健字，，（distinct）
SQL> select distinct deptno
2 from emp;
DEPTNO
----------
10
20
30
SQL>
BETWEEN...AND...（语句）
SQL> select ename,sal
2 from emp
3 where sal BETWEEN 1000 AND 1500;
ENAME SAL
---------- ----------
WARD 1250
MARTIN 1250
TURNER 1500
ADAMS 1100
MILLER 1300
SQL>
IN语句，符合某个条件的集合
SQL> select empno,ename,sal,mgr
2 from emp
3 where mgr IN (7902,7566,7788);
EMPNO ENAME SAL MGR
---------- ---------- ---------- ----------
7369 SMITH 800 7902
7788 SCOTT 3000 7566
7876 ADAMS 1100 7788
7902 FORD 3000 7566
SQL>
NULL运算符
SQL> select ename,mgr
2 from emp
3 where mgr IS NULL;
ENAME MGR
---------- ----------
KING
ghan
tank
han
SQL>
AND运算符(二条件同为真时，返回值为真)
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
SQL>
OR运算符(二条件只要有一个为真，返回值为真)
SQL> SELECT empno,ename,job,sal
2 from emp
3 where sal>=1000
4 OR job='CLERK';
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7902 FORD ANALYST 3000
7934 MILLER CLERK 1300
9002 han 17000
已选择15行。
SQL>
使用NOT运算符 (取反信息)
SQL> select ename,job
2 from emp
3 where job NOT IN('CLERK','MANAGER','ANALYST');
ENAME JOB
---------- ---------
ALLEN SALESMAN
WARD SALESMAN
MARTIN SALESMAN
KING PRESIDENT
TURNER SALESMAN
SQL>
算术运算符要大于逻辑运算符
NOT高于AND高于OR
要改变运算，，可以用小括号
ORDER BY子句
使用ORDER BY子句对查询数据排序
--ASC:升序排序，默认
--DESC:降序排序
ORDER BY子句须放在SELECT语句的最后面
SQL> select ename,job,deptno,hiredate
2 from emp
3 ORDER BY hiredate;
ENAME JOB DEPTNO HIREDATE
---------- --------- ---------- ----------
SMITH CLERK 20 1980-12-17
ALLEN SALESMAN 30 1981-02-20
WARD SALESMAN 30 1981-02-22
JONES MANAGER 20 1981-04-02
BLAKE MANAGER 30 1981-05-01
CLARK MANAGER 10 1981-06-09
TURNER SALESMAN 30 1981-09-08
MARTIN SALESMAN 30 1981-09-28
KING PRESIDENT 10 1981-11-17
JAMES CLERK 30 1981-12-03
FORD ANALYST 20 1981-12-03
MILLER CLERK 10 1982-01-23
SCOTT ANALYST 20 1987-04-19
ADAMS CLERK 20 1987-05-23
ghan 2008-05-04
tank
han
已选择17行。
SQL>
降序排列 DESC
SQL> select ename,job,deptno,hiredate
2 from emp
3 ORDER BY hiredate DESC;
ENAME JOB DEPTNO HIREDATE
---------- --------- ---------- ----------
tank
han
ghan 2008-05-04
ADAMS CLERK 20 1987-05-23
SCOTT ANALYST 20 1987-04-19
MILLER CLERK 10 1982-01-23
JAMES CLERK 30 1981-12-03
FORD ANALYST 20 1981-12-03
KING PRESIDENT 10 1981-11-17
MARTIN SALESMAN 30 1981-09-28
TURNER SALESMAN 30 1981-09-08
CLARK MANAGER 10 1981-06-09
BLAKE MANAGER 30 1981-05-01
JONES MANAGER 20 1981-04-02
WARD SALESMAN 30 1981-02-22
ALLEN SALESMAN 30 1981-02-20
SMITH CLERK 20 1980-12-17
已选择17行。
SQL>
阅读(1845) | 评论(0) | 转发(0) |
