以 scott/tiger 登录 oracle:
1、查看当前用户:show user;
SQL> show user;
USER 为 "SCOTT"
2、查询 scott 用户下所有的对象:select * from tab;(其中 column tname format a10; 语句是用来设置显示的列宽,下面再讲)
SQL> column tname format a10;
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
---------- -------------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
3、查看 EMP 表的结构:desc EMP;
SQL> desc EMP;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
4、查看 EMP 表中的数据:select * from EMP;(结构太乱了)
SQL> select * from EMP;
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
SAL COMM DEPTNO
---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80
800 20
7499 ALLEN SALESMAN 7698 20-2月 -81
1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81
1250 500 30
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
SAL COMM DEPTNO
---------- ---------- ----------
7566 JONES MANAGER 7839 02-4月 -81
2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81
1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81
2850 30
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
SAL COMM DEPTNO
---------- ---------- ----------
7782 CLARK MANAGER 7839 09-6月 -81
2450 10
7788 SCOTT ANALYST 7566 19-4月 -87
3000 20
7839 KING PRESIDENT 17-11月-81
5000 10
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
SAL COMM DEPTNO
---------- ---------- ----------
7844 TURNER SALESMAN 7698 08-9月 -81
1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87
1100 20
7900 JAMES CLERK 7698 03-12月-81
950 30
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
SAL COMM DEPTNO
---------- ---------- ----------
7902 FORD ANALYST 7566 03-12月-81
3000 20
7934 MILLER CLERK 7782 23-1月 -82
1300 10
已选择14行。
5、设置显示的列宽:数值类型(number);9表示数字型,一个9表示一个数字位,四个9表示四个数字位(只能用 9 表示);
column empno format 9999; 表示设置 empno 列的宽度为 四个数字位;
SQL> column empno format 9999;
6、设置显示的列宽:字符串类型(varchar2)和 日期类型(date);用 a 表示字符串位,后面的数字表示宽度;
column ename format a12; 表示设置 ename 列的宽度为 12 个字符宽度;
SQL> column ename format a8;
7、优化查询 EMP 显示的列宽:
SQL> column empno format 9999;
SQL> column ename format a8;
SQL> column job format a10;
SQL> column mgr format 9999;
SQL> column hiredate format a12;
SQL> column sal format 9999;
SQL> column comm format 9999;
SQL> column deptno format 9999;
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- -------- ---------- ----- ------------ ----- ----- ------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- -------- ---------- ----- ------------ ----- ----- ------
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择14行。
8、可以看到上面查询的数据分成了两页,可以通过设置一页显示多少个记录,来取消分页;
比如 EMP 表中总共有 14 条数据,只要设置每页显示的记录数大于 14,就不会出现分页效果;
例如设置每页显示 80 条数据:set pagesize 80;
SQL> set pagesize 80;
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- -------- ---------- ----- ------------ ----- ----- ------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择14行。
注意:上面对列宽的设置 和 对分页的设置 只在当前会话中有效。
登录 oracle 就是开启一个会话,退出登录 就是结束会话。(即退出数据库,设置失效)
9、执行最近一个执行的 sql 语句:使用 /
SQL> /
10、清屏,属于 sqlplus 工具中的命令:host cls;
SQL> host cls;
11、查询指定的列,并指定列的别名:列名和别名之间以空格分隔,多个列名之间以逗号分隔
SQL> select empno "编号",ename "姓名",sal "工资",deptno "部门号" from emp;
编号 姓名 工资 部门号
---------- -------------------- ---------- ----------
7369 SMITH 800 20
7499 ALLEN 1600 30
7521 WARD 1250 30
7566 JONES 2975 20
7654 MARTIN 1250 30
7698 BLAKE 2850 30
7782 CLARK 2450 10
7788 SCOTT 3000 20
7839 KING 5000 10
7844 TURNER 1500 30
7876 ADAMS 1100 20
7900 JAMES 950 30
7902 FORD 3000 20
7934 MILLER 1300 10
已选择14行。
12、查询 emp 表中的 job 字段,去除重复的数据:使用关键字 distinct 去除重复的数据
SQL> select distinct job from emp;
JOB
----------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
13、查询员工的 编号,姓名,月薪,年薪:年薪为(月薪*12)
SQL> select empno,ename,sal,sal*12 "年薪" from emp;
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
7900 JAMES 950 11400
7902 FORD 3000 36000
7934 MILLER 1300 15600
已选择14行。
14、查询员工的 编号,姓名,入职时间,月薪,年薪,年收入(年薪+奖金):
SQL> select empno "编号",ename "姓名",hiredate "入职时间",sal "月薪",sal*12 "年薪",sal*12+comm "年收入" from emp;
编号 姓名 入职时间 月薪 年薪 年收入
---------- -------------------- -------------- ---------- ---------- ----------
7369 SMITH 17-12月-80 800 9600
7499 ALLEN 20-2月 -81 1600 19200 19500
7521 WARD 22-2月 -81 1250 15000 15500
7566 JONES 02-4月 -81 2975 35700
7654 MARTIN 28-9月 -81 1250 15000 16400
7698 BLAKE 01-5月 -81 2850 34200
7782 CLARK 09-6月 -81 2450 29400
7788 SCOTT 19-4月 -87 3000 36000
7839 KING 17-11月-81 5000 60000
7844 TURNER 08-9月 -81 1500 18000 18000
7876 ADAMS 23-5月 -87 1100 13200
7900 JAMES 03-12月-81 950 11400
7902 FORD 03-12月-81 3000 36000
7934 MILLER 23-1月 -82 1300 15600
已选择14行。
注意:在 sqlplus 工具中,不显示 null 这个值;任何数值与 null 进行加减乘除运算,结果都是 null。
15、 解决 null 的问题,可以使用 NVL(a, b) 函数:如果 a 为 null,就取 b 的值,如果 a 不为 null,就取 a 的值;
SQL> select NVL(null,10) from dual;
NVL(NULL,10)
------------
10
SQL> select NVL(5,10) from dual;
NVL(5,10)
----------
5
注意:dual 是 oracle 中一个特殊的表,叫做哑表,或者伪表,只有一行。
16、 查询员工的 编号,姓名,入职时间,月薪,年薪,年收入(年薪+奖金):使用 NVL() 函数处理 null;
SQL> select empno "编号",ename "姓名",hiredate "入职时间",sal "月薪",sal*12 "年薪",sal*12+NVL(comm,0) "年收入" from emp;
编号 姓名 入职时间 月薪 年薪 年收入
---------- -------------------- -------------- ---------- ---------- ----------
7369 SMITH 17-12月-80 800 9600 9600
7499 ALLEN 20-2月 -81 1600 19200 19500
7521 WARD 22-2月 -81 1250 15000 15500
7566 JONES 02-4月 -81 2975 35700 35700
7654 MARTIN 28-9月 -81 1250 15000 16400
7698 BLAKE 01-5月 -81 2850 34200 34200
7782 CLARK 09-6月 -81 2450 29400 29400
7788 SCOTT 19-4月 -87 3000 36000 36000
7839 KING 17-11月-81 5000 60000 60000
7844 TURNER 08-9月 -81 1500 18000 18000
7876 ADAMS 23-5月 -87 1100 13200 13200
7900 JAMES 03-12月-81 950 11400 11400
7902 FORD 03-12月-81 3000 36000 36000
7934 MILLER 23-1月 -82 1300 15600 15600
已选择14行。
其中 sal*12+NVL(comm,0):NVL(comm, 0) 表示如果 comm 的值为 null,就用 0 代替。
17、使用关键字 AS 为列设置别名:AS 用小写(as)也可以,不写也可以;
SQL> select empno AS "编号",ename as "姓名",sal "月薪" from emp;
编号 姓名 月薪
---------- -------------------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
已选择14行。
18、为列设置的别名可以用引号,也可以不用引号:
SQL> select empno AS "编号",ename as 姓名,sal 月薪 from emp;
编号 姓名 月薪
---------- -------------------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
已选择14行。
注意:如果别名没有用引号,则别名不能有空格;如果别名用了引号,则可以有空格;
别名只能用 双引号,不能用 单引号;因为在 oracle 中,单引号表示字符串类型,或者日期类型。
19、 使用 dual 哑表或者伪表,使用字符串连接符号(||),输出 "hello world",在 oracle 中,from 是必须写的;
SQL> select 'hello' || ' world' "结果" from dual;
结果
----------------------
hello world
20、使用字符串连接符号 ||,显示如下格式信息:**** 的薪水是 **** 美元
SQL> select ename || ' 的薪水是 ' || sal || ' 美元' from emp;
ENAME||'的薪水是'||SAL||'美元'
--------------------------------------------------------------------------------
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 美元
已选择14行。