一. 友好配置
友好配置是为了在之后使用数据库时更加的方便而安装相关软件或者一些参数的配置。
1.1 安装rlwrap
安装包下载地址：rlwrap
- 将安装包上传至服务器
- 执行编译会报错：需要python3
[root@oracel_1 ~]# rpm -ivh rlwrap-0.45.2-1.el7.x86_64.rpm
warning: rlwrap-0.45.2-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 352c64e5: NOKEY
error: Failed dependencies:
/usr/bin/python3 is needed by rlwrap-0.45.2-1.el7.x86_64
perl(File::Slurp) is needed by rlwrap-0.45.2-1.el7.x86_64
安装python3
yum install python3
- 再次编译
[root@oracel_1 ~]# rpm -ivh rlwrap-0.45.2-1.el7.x86_64.rpm --force --nodeps
warning: rlwrap-0.45.2-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 352c64e5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:rlwrap-0.45.2-1.el7 ################################# [100%]
–nodeps就是安装时不检查依赖关系
–force就是强制安装
- 配置oracle环境变量
[root@oracel_1 ~]# su - oracle
Last login: Wed Sep 7 09:37:45 CST 2022 on pts/5
[oracle@oracle1 ~]$ vi .bash_profile
#在.bash_profile文件最下方添加以下两行
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
执行source .bash_profile生效
1.2 设置 sqlplus 提示符配置glogin
[oracle@oracle1 ~]$ vi $ORACLE_HOME/sqlplus/admin/glogin.sql
#添加以下几行
set linesize 120
set pagesize 999
define _editor=vi
set sqlprompt "_user'@'_connect_identifier> "
验证一下，有‘
二. 基本select语句
2.1 scott用户
从 Oracle 第一个商业化版本至今，oracle 的默认数据库里都少不了这个名字为scott，密码为tiger 的用户。我们可以使用他作为sql语句学习的工具。
- 重建scott用户方案
SYS@oradb> @?/rdbms/admin/utlsampl.sql
- 解锁scott用户并设置密码
SYS@oradb> alter user scott identified by tiger account unlock;
User altered.
SYS@oradb> conn scott/tiger
Connected.
SCOTT@oradb>
- 查看scott用户下的表
user_tables存储的时当前用户所有表信息–数据字典表（视图）
SCOTT@oradb> select table_name from user_tables;
TABLE_NAME
------------------------------------------------------------------------------------
DEPT
EMP
BONUS
SALGRADE
接下来可以使用以上scott用户下的表进行sql语句的学习。
2.2 查询所有列 [SELECT *]
SCOTT@oradb> SELECT * FROM DEPT;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
- '*'代表所有列
- 查询所有列会增加oracle处理的代价，性能会降低
- select * 的时候，oracle需要查询数据字典，转换为具体的列名称
查询具体列 [SELECT COLUMN_NAME]
当我们需要查询某个表特定的列时，可以使用DESC TABLE_NAME命令来查看该表表结构（列名），从而选择自己想要查询的列名。desc->describe
SCOTT@oradb> desc emp
Name Null? Type
----------------------------------------------------------------- -------- ------------------------
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)
我们选择empno，ename两列进行查询
SCOTT@oradb> SELECT EMPNO,ENAME FROM EMP;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
14 rows selected.
2.3 伪列 [SELECT ROWNUM]
伪列实际上并不存储在实际的表中，常见的伪列有：rownum,rowid,currval,nextval(对象号,文件号,块号,行号)。
SCOTT@oradb> select rownum,deptno,dname,loc from dept;
ROWNUM DEPTNO DNAME LOC
---------- ---------- -------------- -------------
1 10 ACCOUNTING NEW YORK
2 20 RESEARCH DALLAS
3 30 SALES CHICAGO
4 40 OPERATIONS BOSTON
- 伪列不能进行插入和删除的操作
2.4 数学表达式在select中的使用
可以在 SELECT 语句中用数学表达式对列值进行计算
| 操作符 | 描述 |
|---|---|
| + | 加 |
| - | 减 |
| * | 乘 |
| / | 除 |
- 示例：查询每个员工工资增加300以后的值
SCOTT@oradb> select ename,sal,sal+300 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
14 rows selected.
- 表达式的优先级
先乘除后加减，相同优先级自左向右依次计算，（）用来重新组合表达式的优先级
SCOTT@oradb> select ename,sal,12*sal+100,12*(sal+100) from emp;
ENAME SAL 12*SAL+100 12*(SAL+100)
---------- ---------- ---------- ------------
SMITH 800 9700 10800
ALLEN 1600 19300 20400
WARD 1250 15100 16200
JONES 2975 35800 36900
MARTIN 1250 15100 16200
BLAKE 2850 34300 35400
CLARK 2450 29500 30600
SCOTT 3000 36100 37200
KING 5000 60100 61200
TURNER 1500 18100 19200
ADAMS 1100 13300 14400
JAMES 950 11500 12600
FORD 3000 36100 37200
MILLER 1300 15700 16800
14 rows selected.
2.5 使用列别名查询
- 使用别名的目的：为了以友好的方式显示，可以定义列的别名
使用方法：
[select column_name as new_name from]
[select column_name as new_name from]
[select column_name “new_Name”] 当别名中有特殊字符（如空格、/ 等）、别名需要区分大小写时，要用双引号。
SCOTT@oradb> select empno as ID,ename Name,sal "Salary" from emp;
ID NAME Salary
---------- ---------- ----------
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 rows selected.
- 列的别名不能再数学表达式中运算
SCOTT@oradb> select sal salary ,salary + 300 from emp;
select sal salary ,salary + 300 from emp
*
ERROR at line 1:
ORA-00904: "SALARY": invalid identifier
2.6 消除重复行 [SELECT DISTINCT]
- 对单个列去重
SCOTT@oradb> SELECT DEPTNO FROM EMP;
DEPTNO
----------
20
30
30
20
30
30
10
20
10
30
20
30
20
10
14 rows selected.
SCOTT@oradb> select distinct deptno from emp;
DEPTNO
----------
30
20
10
- 对多个列去重
如果distinct后指定了多个列，那么只要满足：这多者组合起来的值是不同的，这些行都会被选取显示。
SCOTT@oradb> select distinct deptno,job from emp;
DEPTNO JOB
---------- ---------
20 CLERK
30 SALESMAN
20 MANAGER
30 CLERK
10 PRESIDENT
30 MANAGER
10 CLERK
10 MANAGER
20 ANALYST
9 rows selected.
- DISTINCT 关键字前不能查询其他列
SCOTT@oradb> select empno,distinct deptno from emp;
select empno,distinct deptno from emp
*
ERROR at line 1:
ORA-00936: missing expression
- 一个select语句中不能出现多个DISTINCT
SCOTT@oradb> select distinct empno,distinct deptno from emp;
select distinct empno,distinct deptno from emp
*
ERROR at line 1:
ORA-00936: missing expression
三. 其他select的设置与引用
3.1 列标题的默认格式
-默认标题对齐：左对齐
-默认标题显示：大写
-字符和日期类型的列左对齐
-数值类型的列右对齐
-默认字段显示方式：大写
示例：
3.2 格式化列的宽度 [COLUMN … FORMAT]
SCOTT@oradb> column hiredate format a20
SCOTT@oradb> col mgr for 9999
SCOTT@oradb> SELECT * FROM EMP
2 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ----- -------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
- [COL … FOR…]=[COLUMN … FORMAT…]
- FORMAT: a10可以理解为设置列宽为10个a字母这样的宽度；9999通常用于设置数值类型列的宽度，可以理解为显示宽度为4个9。
- 如果列宽设置不够，会出现###乱码
SCOTT@oradb> col mgr for 99
SCOTT@oradb> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- --- ---------- ---------- ---------- ----------
7369 SMITH CLERK ### 17-DEC-80 800 20
7499 ALLEN SALESMAN ### 20-FEB-81 1600 300 30
7521 WARD SALESMAN ### 22-FEB-81 1250 500 30
7566 JONES MANAGER ### 02-APR-81 2975 20
7654 MARTIN SALESMAN ### 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER ### 01-MAY-81 2850 30
7782 CLARK MANAGER ### 09-JUN-81 2450 10
7788 SCOTT ANALYST ### 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN ### 08-SEP-81 1500 0 30
7876 ADAMS CLERK ### 23-MAY-87 1100 20
7900 JAMES CLERK ### 03-DEC-81 950 30
7902 FORD ANALYST ### 03-DEC-81 3000 20
7934 MILLER CLERK ### 23-JAN-82 1300 10
14 rows selected.
3.3 空值（NULL）的定义与处理
3.3.1 空值的定义
- NULL是无效的，未指定的，未知的或不可预知的值
- NULL不等于0，也不是空格
- 包含空值的数学表达式的值都是空值
3.3.2 nvl函数 (NULL转换成0)
- 格式：nvl(column_name,0)
- 含义：如果column_name这列中某个值为空，就转换为0
SCOTT@oradb> select comm,nvl(comm,0) from emp;
COMM NVL(COMM,0)
---------- -----------
0
300 300
500 500
0
1400 1400
0
0
0
0
0 0
0
0
0
0
14 rows selected.
3.4 连接符||、引用运算符q
3.4.1 连接符
- 把列和列，列和字符连接在一起
- 用’||'表示
- 可以用来‘合成’列
SCOTT@oradb> select ename,job,ename||' is a '||job from emp;
ENAME JOB ENAME||'ISA'||JOB
---------- --------- -------------------------
SMITH CLERK SMITH is a CLERK
ALLEN SALESMAN ALLEN is a SALESMAN
WARD SALESMAN WARD is a SALESMAN
JONES MANAGER JONES is a MANAGER
MARTIN SALESMAN MARTIN is a SALESMAN
BLAKE MANAGER BLAKE is a MANAGER
CLARK MANAGER CLARK is a MANAGER
SCOTT ANALYST SCOTT is a ANALYST
KING PRESIDENT KING is a PRESIDENT
TURNER SALESMAN TURNER is a SALESMAN
ADAMS CLERK ADAMS is a CLERK
JAMES CLERK JAMES is a CLERK
FORD ANALYST FORD is a ANALYST
MILLER CLERK MILLER is a CLERK
- 特殊情况：需要显示单引号的时候，通常要在字符串中应该出现的单引号前面再加一个单引号
SCOTT@oradb> select ename ||'''s job is '|| job from emp;
ENAME||'''SJOBIS'||JOB
-----------------------------
SMITH's job is CLERK
ALLEN's job is SALESMAN
WARD's job is SALESMAN
JONES's job is MANAGER
MARTIN's job is SALESMAN
BLAKE's job is MANAGER
CLARK's job is MANAGER
SCOTT's job is ANALYST
KING's job is PRESIDENT
TURNER's job is SALESMAN
ADAMS's job is CLERK
JAMES's job is CLERK
FORD's job is ANALYST
MILLER's job is CLERK
注意看’’'s job is '，第一个’是转义字符，第二个’与最后一个’是表示引号内为需要显示的字符串，第三个’就是需要被显示出来的单引号。
3.4.2 引用操作符（q）
- 指定自己的引号分隔符
- 选择任何分隔符
- 增加可读性和可用性
使用方式：q’<…>’,<>也可以由其他符号进行替换，…就是我们想要显示输出的内容
SCOTT@oradb> select ename||q'\'s job is \'||job from emp;
ENAME||Q'\'SJOBIS\'||JOB
-----------------------------
SMITH's job is CLERK
ALLEN's job is SALESMAN
WARD's job is SALESMAN
JONES's job is MANAGER
MARTIN's job is SALESMAN
BLAKE's job is MANAGER
CLARK's job is MANAGER
SCOTT's job is ANALYST
KING's job is PRESIDENT
TURNER's job is SALESMAN
ADAMS's job is CLERK
JAMES's job is CLERK
FORD's job is ANALYST
MILLER's job is CLERK
14 rows selected.
SCOTT@oradb> select ename||q'.'s job is .'||job from emp;
ENAME||Q'.'SJOBIS.'||JOB
-----------------------------
SMITH's job is CLERK
ALLEN's job is SALESMAN
WARD's job is SALESMAN
JONES's job is MANAGER
MARTIN's job is SALESMAN
BLAKE's job is MANAGER
CLARK's job is MANAGER
SCOTT's job is ANALYST
KING's job is PRESIDENT
TURNER's job is SALESMAN
ADAMS's job is CLERK
JAMES's job is CLERK
FORD's job is ANALYST
MILLER's job is CLERK
3.4.3 扩展
连接运算符在生成批量脚本时非常有用,提高工作效率：
如构造描述 scott 用户所有表结构的 sql 语句
SCOTT@oradb> select 'desc '|| table_name from user_tables;
'DESC'||TABLE_NAME
-----------------------------------------------
desc DEPT
desc EMP
desc BONUS
desc SALGRADE
3.5 save保存查询语句
- 执行查询语句
SCOTT@oradb> select distinct ename,deptno from emp;
- 查看历史命令[list/l]
SCOTT@oradb> l
1* select distinct ename,deptno from emp
- 保存此sql语句到操作系统中/home/oracle/p1_01.sql
SCOTT@oradb> save /home/oracle/p1_01.sql
Created file /home/oracle/p1_01.sql
- 在sqlplus中查看保存在操作系统中的文件
使用get和!cat命令都可以查看文件内容
SCOTT@oradb> get /home/oracle/p1_01.sql
1* select distinct ename,deptno from emp
SCOTT@oradb> !cat /home/oracle/p1_01.sql
select distinct ename,deptno from emp
/
- 使用@在sqlplus中执行该文件
SCOTT@oradb> @/home/oracle/p1_01.sql
ENAME DEPTNO
---------- ----------
SMITH 20
JONES 20
FORD 20
CLARK 10
SCOTT 20
TURNER 30
JAMES 30
KING 10
ADAMS 20
BLAKE 30
MILLER 10
ALLEN 30
WARD 30
MARTIN 30
14 rows selected.
四. 练习
4.1 描述 scott.emp 表的结构，查询 emp 表中所有数据。
SCOTT@oradb> DESC EMP
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
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)
SCOTT@oradb> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- -------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
4.2 找出下面语句中的错误并修正。
select empno ename,job sal x 12 New Salary from emp;
SCOTT@oradb> SELECT EMPNO,ENAME,JOB,SAL*12 "NEW SALARY" FROM EMP;
EMPNO ENAME JOB NEW SALARY
---------- ---------- --------- ----------
7369 SMITH CLERK 9600
7499 ALLEN SALESMAN 19200
7521 WARD SALESMAN 15000
7566 JONES MANAGER 35700
7654 MARTIN SALESMAN 15000
7698 BLAKE MANAGER 34200
7782 CLARK MANAGER 29400
7788 SCOTT ANALYST 36000
7839 KING PRESIDENT 60000
7844 TURNER SALESMAN 18000
7876 ADAMS CLERK 13200
7900 JAMES CLERK 11400
7902 FORD ANALYST 36000
7934 MILLER CLERK 15600
14 rows selected.
4.3 查询 scott.emp 表中，所有员工的姓名、工资、工资与奖金之和，列标题显示为如下格式：NAME SALARY Total_Salary。
SCOTT@oradb> SELECT ENAME NAME,SAL SALARY,SAL+NVL(COMM,0) "Total_Salary" from emp;
NAME SALARY Total_Salary
---------- ---------- ------------
SMITH 800 800
ALLEN 1600 1900
WARD 1250 1750
JONES 2975 2975
MARTIN 1250 2650
BLAKE 2850 2850
CLARK 2450 2450
SCOTT 3000 3000
KING 5000 5000
TURNER 1500 1500
ADAMS 1100 1100
JAMES 950 950
FORD 3000 3000
MILLER 1300 1300
14 rows selected.
4.4 查看最后一次执行的查询语句，将此语句保存在操作系统中：/home/oracle/l1.sql,在sqlplus中查看并执行这个脚本。
SCOTT@oradb> L
1* SELECT ENAME NAME,SAL SALARY,SAL+NVL(COMM,0) "Total_Salary" from emp
SCOTT@oradb> SAVE /home/oracle/l1.sql
Created file /home/oracle/l1.sql
SCOTT@oradb> get /home/oracle/l1.sql
1* SELECT ENAME NAME,SAL SALARY,SAL+NVL(COMM,0) "Total_Salary" from emp
SCOTT@oradb> @/home/oracle/l1.sql
NAME SALARY Total_Salary
---------- ---------- ------------
SMITH 800 800
ALLEN 1600 1900
WARD 1250 1750
JONES 2975 2975
MARTIN 1250 2650
BLAKE 2850 2850
CLARK 2450 2450
SCOTT 3000 3000
KING 5000 5000
TURNER 1500 1500
ADAMS 1100 1100
JAMES 950 950
FORD 3000 3000
MILLER 1300 1300
14 rows selected.
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
