1. DQL - Data Query Language
命令行提示符修改
SQL> set time on
10:33:58 SQL> define
DEFINE _DATE = "11-DEC-22" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "" (CHAR)
DEFINE _O_RELEASE = "" (CHAR)
10:34:09 SQL> set sqlprompt "_USER'@'_CONNECT_IDENTIFIER>"
10:34:41 SYS@orcl>
一些基本参数的永久修改
[oracle@ora-server admin]$ pwd
/u01/app/oracle/product/12.2.0/db_1/sqlplus/admin
[oracle@ora-server admin]$ vim glogin.sql set linesize 200
set pagesize 100
set time onset sqlprompt "_USER'@'_CONNECT_IDENTIFIER>"
查看历史数据
08:56:52 SYS@orcl>show parameter undo_rNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 1800 --历史数据在数据库中存放1800秒09:08:25 SYS@orcl>select * from emp
09:08:55 2 as of timestamp
09:09:05 3 to_timestamp('2023-01-02 09:01:00','yyyy-mm-dd hh24:mi:ss');EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- -------------------- ---------- --------- ---------- ---------- ----------7369 SMITH CLERK 7902 17-DEC-80 800 207499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307521 WARD SALESMAN 7698 22-FEB-81 1250 500 307566 JONES MANAGER 7839 02-MAR-81 2975 207654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 307698 BLAKE MANAGER 7839 01-MAY-81 2850 307782 CLARK MANAGER 7839 09-JUN-81 2450 107788 SCOTT ANALIST 7566 19-MAR-87 3000 207839 KING PRESIDENT 17-NOV-81 5000 107844 TURNER SALESMAN 7698 08-SEP-81 1500 0 307876 ADAMS CLERK 7788 23-MAY-87 1100 207900 JAMES CLERK 7698 03-DEC-81 950 307902 FORD ANALYST 7566 03-DEC-81 3000 207934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.
1.1 基本语法
SELECT {* | [DISTINCT] colume | expression [alias],...} FROM table;
- SELECT 表示需要显示那些数据列
- FROM 表示选择的这些列是来自哪个表
SQL> set linesize 200;
SQL> select * from emp; # 从emp表中查看所有列EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- -------------------- ---------- --------- ---------- ---------- ----------7369 SMITH CLERK 7902 17-DEC-80 800 207499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307521 WARD SALESMAN 7698 22-FEB-81 1250 500 307566 JONES MANAGER 7839 02-MAR-81 2975 207654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 307698 BLAKE MANAGER 7839 01-MAY-81 2850 307782 CLARK MANAGER 7839 09-JUN-81 2450 107788 SCOTT ANALIST 7566 19-MAR-87 3000 207839 KING PRESIDENT 17-NOV-81 5000 107844 TURNER SALESMAN 7698 08-SEP-81 1500 0 307876 ADAMS CLERK 7788 23-MAY-87 1100 207900 JAMES CLERK 7698 03-DEC-81 950 307902 FORD ANALYST 7566 03-DEC-81 3000 207934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.SQL> select ename a,sal b from emp; # 从emp表中查看enam和sal，分别命名为a和bA B
-------------------- ----------
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 130014 rows selected.SQL> select sal,sal*12 as nianxin from emp; # 将sal乘以12，并以nianxin为名字展示出来，这一列数据只在内存中出现SAL NIANXIN
---------- ----------800 96001600 192001250 150002975 357001250 150002850 342002450 294003000 360005000 600001500 180001100 13200950 114003000 360001300 15600
SQL语句特性：
- 不区分大小写
- 可以在一行或多行内书写，以分号结尾
- 选择多个列时，列之间使用逗号分隔
- 关键字不能缩写，也不能跨行
- 不同的子句建议写在不同的行，一个关键字后面跟的语句叫子句
- 标题行都会大写，数字类型默认右对齐，字符类型默认左对齐
|| 连字符
SQL> select empno||'-'||ename||';' aa from emp; # 将empno连接一个-然后连接empno，在连接一个分号，最后命名为aa并输出AA
--------------------------------------------------------------
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.
distinct 去重关键字
SQL> select distinct job from emp; # 将job列展示出来，并去重JOB
--------------------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALIST
ANALYST6 rows selected.
count 计算行数关键字
SQL> select count(distinct job) from emp; # 统计有多少种工作COUNT(DISTINCTJOB)
------------------6null 空
SQL> select * from emp where comm=null; # 等号是比较运算符，因为空值没办法进行运算，所以这个语句不会有输出no rows selectedSQL> select * from emp where comm is null;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- -------------------- ---------- --------- ---------- ---------- ----------7369 SMITH CLERK 7902 17-DEC-80 800 207566 JONES MANAGER 7839 02-MAR-81 2975 207698 BLAKE MANAGER 7839 01-MAY-81 2850 307782 CLARK MANAGER 7839 09-JUN-81 2450 107788 SCOTT ANALIST 7566 19-MAR-87 3000 207839 KING PRESIDENT 17-NOV-81 5000 107876 ADAMS CLERK 7788 23-MAY-87 1100 207900 JAMES CLERK 7698 03-DEC-81 950 307902 FORD ANALYST 7566 03-DEC-81 3000 207934 MILLER CLERK 7782 23-JAN-82 1300 1010 rows selected.
数据类型
- number(p,s)：p代表有效长度，s代表精度（小数点后多少位）
- varchar(n)：可变长字符类型，n取值0-4000，如1保存在数据库中就是1
- char(n)：定长字符类型，如果n为4，那么4保存在数据库中就是0004
- date：日期类型
- timestamp：时间戳类型
1.2 过滤和排序
1.2.1 过滤语句-where
where属于过滤语句，因此需要接判断语句，常见的判断如下：
| 操作符 | 含义 |
|---|---|
| = | 等于 |
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| <> | 不等于 |
| between ... and ... | 在两个值之间（开区间） |
| in | 值包含在一个列表中 |
| like | 模糊匹配 |
| is null | 是一个空值 |
使用比较运算符来过滤数据
SQL> select * from emp where deptno=10; # 查询deptno是10的数据EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- -------------------- ---------- --------- ---------- ---------- ----------7782 CLARK MANAGER 7839 09-JUN-81 2450 107839 KING PRESIDENT 17-NOV-81 5000 107934 MILLER CLERK 7782 23-JAN-82 1300 10SQL> select * from emp where job='CLERK'; # 字符串类型需要使用引号，因为不属于语句类型，因此需要注意大小写EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- -------------------- ---------- --------- ---------- ---------- ----------7369 SMITH CLERK 7902 17-DEC-80 800 207876 ADAMS CLERK 7788 23-MAY-87 1100 207900 JAMES CLERK 7698 03-DEC-81 950 307934 MILLER CLERK 7782 23-JAN-82 1300 10# 在搜索时要想忽略大小写也可以，需要双向转换；
SQL> select * from emp where lower(job)=lower('sAlEsman'); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- -------------------- ---------- --------- ---------- ---------- ----------7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307521 WARD SALESMAN 7698 22-FEB-81 1250 500 307654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 307844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
逻辑运算符
# 查询deptno是10或20的数据
SQL> select * from emp where deptno=10 or deptno=20;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- -------------------- ---------- --------- ---------- ---------- ----------7369 SMITH CLERK 7902 17-DEC-80 800 207566 JONES MANAGER 7839 02-MAR-81 2975 207782 CLARK MANAGER 7839 09-JUN-81 2450 107788 SCOTT ANALIST 7566 19-MAR-87 3000 207839 KING PRESIDENT 17-NOV-81 5000 107876 ADAMS CLERK 7788 23-MAY-87 1100 207902 FORD ANALYST 7566 03-DEC-81 3000 207934 MILLER CLERK 7782 23-JAN-82 1300 108 rows selected.
between and
# 闭区间，包含边界值
SQL> select * from emp where sal between 1500 and 3000;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- -------------------- ---------- --------- ---------- ---------- ----------7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307566 JONES MANAGER 7839 02-MAR-81 2975 207698 BLAKE MANAGER 7839 01-MAY-81 2850 307782 CLARK MANAGER 7839 09-JUN-81 2450 107788 SCOTT ANALIST 7566 19-MAR-87 3000 207844 TURNER SALESMAN 7698 08-SEP-81 1500 0 307902 FORD ANALYST 7566 03-DEC