把学习的Oracle的基本操作记录一下,同时也加深一下记忆
数据库,Oracle 11g,用户Scott
- 清屏
SQL> host cls
SQL> --清屏
- 当前用户
SQL> --当前用户
SQL> show user
USER 为 "SCOTT"
- 查询当前用户下的表
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
查看emp表的表结构
SQL> desc emp
名称 是否为空? 类型
----------------------------------------- -------- ----------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
EMPJOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
设置行宽和列宽
SQL>set linesize 120
SQL>col ename for a8
SQL>col sal for 9999
在编写sql语句时尽量少使用 *,用列名替代,提高查询效率
c命令 用来修改上一次输入的错误命令,/ 表示执行上一条sql语句。如:
SQL> select empno , ename , sal , sal*12 年薪
2 form emp;
form emp
*
第 2 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字
SQL> c /form/from
2* from emp
SQL> /
EMPNO ENAME SAL 年薪
---------- -------- ----- ----------
7369 SMITH 800 9600
7499 ALLEN 1600 19200
7521 WARD 1250 15000
7566 JONES 2975 35700
7654 MARTIN 1250 15000
7698 BLAKE 2850 34200
7782 CLARK 2450 29400
7788 SCOTT 3000 36000
7839 KING 5000 60000
7844 TURNER 1500 18000
7876 ADAMS 1100 13200
EMPNO ENAME SAL 年薪
---------- -------- ----- ----------
7900 JAMES 950 11400
7902 FORD 3000 36000
7934 MILLER 1300 15600
已选择14行。
- 所有包含null的sql表达式都为null,null永远都不等于null,需要用is null代替,如:当查询员工的年收入(由月薪*12+奖金组成)时,可以看到没有奖金的员工的年收入查出来为空
SQL> select sal*12 , comm ,sal*12+comm from emp;
SAL*12 COMM SAL*12+COMM
---------- ---------- -----------
9600
19200 300 19500
15000 500 15500
35700
15000 1400 16400
34200
29400
36000
60000
18000 0 18000
13200
解决办法,nvl函数,nvl函数会判断奖金(comm)值是否为空,如果为空comm的值则为0,否则为comm的值。如:
SQL> select sal*12 , comm ,sal*12+nvl(comm,0) from emp;
SAL*12 COMM SAL*12+NVL(COMM,0)
---------- ---------- ------------------
9600 9600
19200 300 19500
15000 500 15500
35700 35700
15000 1400 16400
34200 34200
29400 29400
36000 36000
60000 60000
18000 0 18000
13200 13200
- concat字符串拼接函数
SQL> select concat('Hello ',' World') from dual;
CONCAT('HELL
------------
Hello World
SQL>--dual伪表
- ‘||’用法,字符串拼接
SQL> select ename||'的薪水是'||sal 字符串 from emp;
字符串
----------------------------------------------------------
SMITH的薪水是800
ALLEN的薪水是1600
WARD的薪水是1250
JONES的薪水是2975
- 修改系统默认日期格式。select * from v$nls_parameters,查询会话参数,可以看到日期对应的格式是 DD-MON-RR,修改其值即可,如
SQL> alter session set NLS_DATE_FORMAT='yyyy-mm-dd';
会话已更改。
SQL> select sysdate from dual;
SYSDATE
----------
2017-03-06
- between … and …
SQL> select * from emp where sal between 1000 and 2000;
- in 在集合中查询
SQL> select * from emp where deptno in (10,20);
- 如果在集合中含有null,不能使用not in,可以使用in
SQL> select * from emp where deptno not in (10,20,null);
未选定行
SQL> select * from emp where deptno in (10,20,null);
EMPNO ENAME EMPJOB MGR HIREDATE SAL COMM DEPTNO
---------- -------- --------- ---------- ---------- ----- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 800 20
7566 JONES MANAGER 7839 1981-04-02 2975 20
7782 CLARK MANAGER 7839 1981-06-09 2450 10
like模糊查询
SQL> --查询名字以S开头的员工
SQL> select ename,empjob from emp where ename like 'S%';
ENAME EMPJOB
-------- ---------
SMITH CLERK
SCOTT ANALYST
SQL> --查询名字是四个字的员工
SQL> select ename , empjob from emp where ename like '____';
ENAME EMPJOB
-------- ---------
WARD SALESMAN
KING PRESIDENT
FORD ANALYST
- order by,作用域后面所有的列,先按照第一个列排序,如果相同再按照第二个排序,以此类推。此外,desc作用于靠它最近的列
SQL> select ename , empjob ,deptno , sal from emp order by deptno , sal desc;
ENAME EMPJOB DEPTNO SAL
-------- --------- ---------- -----
KING PRESIDENT 10 5000
CLARK MANAGER 10 2450
MILLER CLERK 10 1300
SCOTT ANALYST 20 3000
FORD ANALYST 20 3000
JONES MANAGER 20 2975
ADAMS CLERK 20 1100
SMITH CLERK 20 800
BLAKE MANAGER 30 2850
ALLEN SALESMAN 30 1600
TURNER SALESMAN 30 1500
a命令用于追加命令
SQL> select ename , empjob,deptno from emp order by deptno ;
ENAME EMPJOB DEPTNO
-------- --------- ----------
CLARK MANAGER 10
KING PRESIDENT 10
MILLER CLERK 10
JONES MANAGER 20
FORD ANALYST 20
ADAMS CLERK 20
SMITH CLERK 20
SCOTT ANALYST 20
WARD SALESMAN 30
TURNER SALESMAN 30
ALLEN SALESMAN 30
已选择14行。
SQL> a desc;
1* select ename , empjob,deptno from emp order by deptno desc
SQL> /
ENAME EMPJOB DEPTNO
-------- --------- ----------
BLAKE MANAGER 30
TURNER SALESMAN 30
ALLEN SALESMAN 30
MARTIN SALESMAN 30
WARD SALESMAN 30
JAMES CLERK 30
SCOTT ANALYST 20
JONES MANAGER 20
SMITH CLERK 20
ADAMS CLERK 20
FORD ANALYST 20
函数:函数分为单行函数与分组函数,单行函数只对一行进行变换,每行只返回一个结果。分组函数作用于一组数据,并对一组数据返回一个值。
单行函数操作:
字符函数,lower()转小写,upper()转大写,initcap()首字母大写
SQL> select lower('Hello') 转小写 , upper('Hello') 转大写 , initcap('hello') 首字母大写 from dual;
转小写 转大写 首字母大写
----- ----- -----
hello HELLO Hello
- 取子串函数,substr(a,b)从a中,第b位开始取。substr(a, b,c)从a中第b位开始取,取c位
SQL> select substr('Hello World',3) 子串 from dual;
子串
---------
llo World
SQL> select substr('Hello World',3,3) 子串 from dual;
子
---
llo
- length()返回字符长度,lengthb()返回字节长度
SQL> select length('世界你好') 字符 ,
2 lengthb('世界你好') 字节 from dual;
字符 字节
---------- ----------
4 8
- instr(a,b)查找函数,在a中查找b,返回位置
SQL> select instr('Hello World','ll') 位置 from dual;
位置
----------
3
- lpad左填充,rpad右填充
SQL> select lpad('abcd',10,'*') 左 , rpad('abcd',10,'*') 右
2 from dual;
左 右
---------- ----------
******abcd abcd******
- trim()去掉前后指定的字符
SQL> select trim('H' from 'Hello WorldH') from dual;
TRIM('H'FR
----------
ello World
- 四舍五入
SQL> select round(45.926,2),round(45.926,1),round(45.926,0),round(45.926,-1),round(45.926,-2) from dual;
ROUND(45.926,2) ROUND(45.926,1) ROUND(45.926,0) ROUND(45.926,-1) ROUND(45.926,-2)
--------------- --------------- --------------- ---------------- ----------------
45.93 45.9 46 50 0
SQL> select trunc(45.926,2),trunc(45.926,1),trunc(45.926,0),trunc(45.926,-1),trunc(45.926,-2) from dual;
TRUNC(45.926,2) TRUNC(45.926,1) TRUNC(45.926,0) TRUNC(45.926,-1) TRUNC(45.926,-2)
--------------- --------------- --------------- ---------------- ----------------
45.92 45.9 45 40 0
- 格式化显示时间
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2017-03-06 15:05:12
多行函数
- sum()函数计算总和
SQL> select sum(sal) from emp;
SUM(SAL)
----------
29025
- count()计算个数
SQL> select count(*) from emp;
COUNT(*)
----------
14
组函数自动滤空,可以嵌套滤空函数来屏蔽滤空功能
多表查询
等值连接
SQL> select e.ename , e.empno ,e.sal , d.dname
2 from emp e ,dept d
3 where e.deptno=d.deptno;
ENAME EMPNO SAL DNAME
-------- ---------- ----- --------------
CLARK 7782 2450 ACCOUNTING
KING 7839 5000 ACCOUNTING
MILLER 7934 1300 ACCOUNTING
JONES 7566 2975 RESEARCH
FORD 7902 3000 RESEARCH
ADAMS 7876 1100 RESEARCH
SMITH 7369 800 RESEARCH
SCOTT 7788 3000 RESEARCH
WARD 7521 1250 SALES
TURNER 7844 1500 SALES
- 右外连接
SQL> select d.deptno , d.dname , count(e.empno) from
2 emp e , dept d
3 where e.deptno(+)=d.deptno
4 group by d.deptno , d.dname;
DEPTNO DNAME COUNT(E.EMPNO)
---------- -------------- --------------
10 ACCOUNTING 3
40 OPERATIONS 0
20 RESEARCH 5
30 SALES 6
- 左外连接语法,e.deptno=d.deptno(+),符号左边的表将比包含。
暂时先记这么多吧。。。。。。。