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 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语句特性：

1. 不区分大小写
2. 可以在一行或多行内书写，以分号结尾
3. 选择多个列时，列之间使用逗号分隔
4. 关键字不能缩写，也不能跨行
5. 不同的子句建议写在不同的行，一个关键字后面跟的语句叫子句
6. 标题行都会大写，数字类型默认右对齐，字符类型默认左对齐

|| 连字符

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)
------------------6

null 空

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