OCP学习笔记-007 SQL语言之一:DQL
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 on
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER>"
查看历史数据
08:56:52 SYS@orcl>show parameter undo_r
NAME 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 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-MAR-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 ANALIST 7566 19-MAR-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.
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 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-MAR-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 ANALIST 7566 19-MAR-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.
SQL> select ename a,sal b from emp; # 从emp表中查看enam和sal,分别命名为a和b
A 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 1300
14 rows selected.
SQL> select sal,sal*12 as nianxin from emp; # 将sal乘以12,并以nianxin为名字展示出来,这一列数据只在内存中出现
SAL NIANXIN
---------- ----------
800 9600
1600 19200
1250 15000
2975 35700
1250 15000
2850 34200
2450 29400
3000 36000
5000 60000
1500 18000
1100 13200
950 11400
3000 36000
1300 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
ANALYST
6 rows selected.
count 计算行数关键字
SQL> select count(distinct job) from emp; # 统计有多少种工作
COUNT(DISTINCTJOB)
------------------
6
null 空
SQL> select * from emp where comm=null; # 等号是比较运算符,因为空值没办法进行运算,所以这个语句不会有输出
no rows selected
SQL> select * from emp where comm is null;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- -------------------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-MAR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALIST 7566 19-MAR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
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
10 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 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> select * from emp where job='CLERK'; # 字符串类型需要使用引号,因为不属于语句类型,因此需要注意大小写
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- -------------------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 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 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 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 20
7566 JONES MANAGER 7839 02-MAR-81 2975 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALIST 7566 19-MAR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
8 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 30
7566 JONES MANAGER 7839 02-MAR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALIST 7566 19-MAR-87 3000 20
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7902 FORD ANALYST 7566 03-DEC