首页 > 代码库 > 数据库编程1 Oracle 过滤 函数 分组 外连接 自连接
数据库编程1 Oracle 过滤 函数 分组 外连接 自连接
【本文谢绝转载原文来自http://990487026.blog.51cto.com】
<大纲> 数据库编程1 Oracle 过滤 函数 分组 外连接 自连接 本文实验基于的数据表: winsows安装好Oracle11g之后,开始实验 SQLplus 登陆 ORacle sqlplus 退出的方式 查看用户之下有什么表 查看表的所有记录，不区分大小写 设置SQLplus行宽,页宽,列宽： 清屏命令 select as 语法 1,as别名的使用 2,没有引号带有空格的别名,无法识别: 3,带有空格的别名解决办法: 年收入抛出问题,年收入显示为空 解决办法,nvl(a,b)函数 1,NULL空值 任何数与NULL运算都是NULL 2,空值不是空 null != null ed修改上一条命令:保存退出,/开始执行 where 过滤,查询奖金为空的员工: ||连接符,与虚表 dual虚表 DISTINCT 删除重复行 DISTINCT修饰多个字段时,当两个数据完全一样才认为是重复的 SQLplus工具的 help edit的帮助: Oracle系统常用的参数 默认的时间显示 临时修改系统时间的显示方式: where过滤字句 > 大于 where过滤字句between and where过滤字句between A and B,A 必须小于等于 B where过滤字句 in where过滤字句 not in where过滤字句 not in 与null在一起,受影响 where like模糊查询,查询员工姓名S开头的信息: where like模糊查询,查询员工姓名是4个字母 where like模糊查询,转义字符 where order by 工资排序,默认升序 where order by 别名 order by 遇到null 1,任何表达式与null运算都为空 2,null!=null nulls last 把null放在最后 order修饰多个列,desc的作用范围只是最近的哪一个! count函数 字符函数 大小写转换函数 LOWER UPPER INITCAP 字符串连接: concat substr(a,b) 从a中第b位开始取字符串 length 字符数 lengthb 字节数 instr求子串的位置 lpad,rpad左填充右填充: trim去除前后指定字符: replace 字符串替换 数字函数 round 四舍五入 tuec截断函数 mod求余函数 to_char格式化输出 两个日期相减,返回日期之间相差的天数 精确显示员工入职月数,时间运算months_between add_months()向指定的日期中加若干的月数 3,1隐式转换,字符串转日期 3,1显示转换,字符串转日期 字符串与字符串比较 to_char()对数字转字符 to_number()字符转数字 通用函数 nvl2(a,b,c)当a为null时返回c,否则返回b nullif(a,b)当a,b相等时返回NULL,否则返回a 案例:总裁涨1000,经理涨800,其他涨500 方法1,SQL规范方法: 方法2:Oracle专有方法 组函数: avg sum max min count 组函数与null在一起 组函数会自动过滤空值: 修正: 分组数据: 求出各个部门的平均工资 分组最难的地方: having 分组过滤: 求10号部门的平均工资的两种方法 1,先分组再过滤 2,先过滤再分组 SQL优化问题: 多表查询: [等值连接],显示员工信息,员工号,姓名,月薪 [不等值连接]显示员工信息,员工号,姓名,月薪,薪水级别 多表查询4,求每个部门的人数:(需要分组) [少了一个部门,这个方法有漏洞,因为40号部门没有员工] [外连接]技术引出: 右外连接,+在等号的左边 左外连接,+在等号的右边 [难点]:[自连接] 显示老板的信息: [优化显示] [再优化] [难点]:查询每个部门的人数
[说明]本文实验基于以下数据表
dept表; emp表; salgrade表; ______________________________________________________________________________ SQL> select * from dept; DEPTNO DNAME LOC ---------- ---------------------------- -------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON ______________________________________________________________________________ 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 1 tom_abc 8000 10 已选择15行。 ______________________________________________________________________________ SQL> select * from salgrade; GRADE LOSAL HISAL ---------- ---------- ---------- 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999 ______________________________________________________________________________
winsows安装好Oracle11g之后,
实验基于[OracleServiceORCL]后台服务进程的开启:
以超级用户方式登录：
C:\Users\VMware>sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on 星期三 8月 24 22:03:26 2016 Copyright (c) 1982, 2010, Oracle. All rights reserved. SQL> connect /as sysdba 已连接。 SQL>
1,SQLplus 登陆 ORacle:
winsows安装好Oracle11g之后，修改scott的密码
C:\Users\VMware>sqlplus 请输入用户名: scott 输入口令:tiger ERROR: ORA-28001: the password has expired 更改 scott 的口令 新口令: 重新键入新口令: 口令已更改 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
输入账户和密码，直接登录
C:\Users\VMware>sqlplus scott/11 SQL*Plus: Release 11.2.0.1.0 Production on 星期三 8月 24 22:00:50 2016 Copyright (c) 1982, 2010, Oracle. All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
sqlplus 退出的方式：
C:\Users\VMware>sqlplus scott/11 SQL> quit SQL> exit
查看用户之下有什么表：
tab是关键字 C:\Users\VMware>sqlplus scott/11 SQL> select * from tab; TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- BONUS TABLE DEPT TABLE EMP TABLE TNAME TABTYPE ------------------------------------------------------------ -------------- CLUSTERID ---------- SALGRADE TABLE SQL>
查看表的所有记录，不区分大小写
SQL> select * from dept; DEPTNO DNAME LOC ---------- ---------------------------- -------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL>
查看表结构：
SQL> desc dept; 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) SQL>
设置SQLplus行宽,页宽,列宽：
设置行宽前： 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 设置行宽后： SQL> set linesize 999; 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行。 SQL> 设置页宽： SQL> set pagesize 999; 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> set linesize 199; 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行。 SQL> col empno for 9999999999999999999 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行。 设置字符的列宽:60个字符的宽度 SQL> col ename for a60; 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行。 恢复,便于后面做实验: SQL> col ename for a20; SQL> col empno for 999999 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行。
清屏命令:
SQL> host cls
select 语法:
select 参与运算语法:
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 SQL> select ename, empno, job, sal, sal*12 from emp; ENAME EMPNO JOB SAL SAL*12 -------------------- ------- ------------------ ---------- ---------- SMITH 7369 CLERK 800 9600 ALLEN 7499 SALESMAN 1600 19200 WARD 7521 SALESMAN 1250 15000 JONES 7566 MANAGER 2975 35700 MARTIN 7654 SALESMAN 1250 15000 BLAKE 7698 MANAGER 2850 34200 CLARK 7782 MANAGER 2450 29400 SCOTT 7788 ANALYST 3000 36000 KING 7839 PRESIDENT 5000 60000 TURNER 7844 SALESMAN 1500 18000 ADAMS 7876 CLERK 1100 13200 JAMES 7900 CLERK 950 11400 FORD 7902 ANALYST 3000 36000 MILLER 7934 CLERK 1300 15600
select as 语法
as 可以省略 冒号可以省略 别名之间不能有空格 1,as别名的使用 SQL> select ename as "员工编号", empno "编号", job 工作 , sal 月薪 , sal*12 年薪 from emp; 员工编号 编号 工作 月薪 年薪 -------------------- ---------- ------------------ ---------- ---------- SMITH 7369 CLERK 800 9600 ALLEN 7499 SALESMAN 1600 19200 WARD 7521 SALESMAN 1250 15000 JONES 7566 MANAGER 2975 35700 MARTIN 7654 SALESMAN 1250 15000 BLAKE 7698 MANAGER 2850 34200 CLARK 7782 MANAGER 2450 29400 SCOTT 7788 ANALYST 3000 36000 KING 7839 PRESIDENT 5000 60000 TURNER 7844 SALESMAN 1500 18000 ADAMS 7876 CLERK 1100 13200 JAMES 7900 CLERK 950 11400 FORD 7902 ANALYST 3000 36000 MILLER 7934 CLERK 1300 15600 已选择14行。
2,没有引号带有空格的别名,无法识别:
SQL> select ename as "员工编号", empno "编号", job 工作 , sal 月 薪 , sal*12 年薪 from emp; select ename as "员工编号", empno "编号", job 工作 , sal 月 薪 , sal*12 年薪 from emp * 第 1 行出现错误: ORA-00923: 未找到要求的 FROM 关键字
3,带有空格的别名解决办法:
SQL> select ename as "员工编号", empno "编号", job 工作 , sal "月 薪" , sal*12 年薪 from emp; 员工编号 编号 工作 月 薪 年薪 -------------------- ---------- ------------------ ---------- ---------- SMITH 7369 CLERK 800 9600 ALLEN 7499 SALESMAN 1600 19200 WARD 7521 SALESMAN 1250 15000 JONES 7566 MANAGER 2975 35700 MARTIN 7654 SALESMAN 1250 15000 BLAKE 7698 MANAGER 2850 34200 CLARK 7782 MANAGER 2450 29400 SCOTT 7788 ANALYST 3000 36000 KING 7839 PRESIDENT 5000 60000 TURNER 7844 SALESMAN 1500 18000 ADAMS 7876 CLERK 1100 13200 JAMES 7900 CLERK 950 11400 FORD 7902 ANALYST 3000 36000 MILLER 7934 CLERK 1300 15600 已选择14行。
年收入抛出问题,年收入显示为空
SQL> select ename as "员工编号", empno "编号", job 工作 , sal "月 薪" , sal*12+comm 年收入 from emp; 员工编号 编号 工作 月 薪 年收入 -------------------- ---------- ------------------ ---------- ---------- SMITH 7369 CLERK 800 ALLEN 7499 SALESMAN 1600 19500 WARD 7521 SALESMAN 1250 15500 JONES 7566 MANAGER 2975 MARTIN 7654 SALESMAN 1250 16400 BLAKE 7698 MANAGER 2850 CLARK 7782 MANAGER 2450 SCOTT 7788 ANALYST 3000 KING 7839 PRESIDENT 5000 TURNER 7844 SALESMAN 1500 18000 ADAMS 7876 CLERK 1100 JAMES 7900 CLERK 950 FORD 7902 ANALYST 3000 MILLER 7934 CLERK 1300
1,NULL空值 任何数与NULL运算都是NULL
2,空值不是空 null != null
解决办法,nvl(a,b)函数,当a是null是返回b,b可以是数字/字符串
SQL> select ename,empno, job, sal, nvl(sal*12+comm,0) from emp; ENAME EMPNO JOB SAL NVL(SAL*12+COMM,0) -------------------- ---------- ------------------ ---------- ------------------ SMITH 7369 CLERK 800 0 ALLEN 7499 SALESMAN 1600 19500 WARD 7521 SALESMAN 1250 15500 JONES 7566 MANAGER 2975 0 MARTIN 7654 SALESMAN 1250 16400 BLAKE 7698 MANAGER 2850 0 CLARK 7782 MANAGER 2450 0 SCOTT 7788 ANALYST 3000 0 KING 7839 PRESIDENT 5000 0 TURNER 7844 SALESMAN 1500 18000 ADAMS 7876 CLERK 1100 0 JAMES 7900 CLERK 950 0 FORD 7902 ANALYST 3000 0 MILLER 7934 CLERK 1300 0 已选择14行。 SQL> select ename,empno, job, sal, nvl(sal*12+comm,0) "年收入" from emp; ENAME EMPNO JOB SAL 年收入 -------------------- ---------- ------------------ ---------- ---------- SMITH 7369 CLERK 800 0 ALLEN 7499 SALESMAN 1600 19500 WARD 7521 SALESMAN 1250 15500 JONES 7566 MANAGER 2975 0 MARTIN 7654 SALESMAN 1250 16400 BLAKE 7698 MANAGER 2850 0 CLARK 7782 MANAGER 2450 0 SCOTT 7788 ANALYST 3000 0 KING 7839 PRESIDENT 5000 0 TURNER 7844 SALESMAN 1500 18000 ADAMS 7876 CLERK 1100 0 JAMES 7900 CLERK 950 0 FORD 7902 ANALYST 3000 0 MILLER 7934 CLERK 1300 0 已选择14行。 SQL> select ename,empno, job, sal, nvl(sal*12+comm,0)as "年收入" from emp; ENAME EMPNO JOB SAL 年收入 -------------------- ---------- ------------------ ---------- ---------- SMITH 7369 CLERK 800 0 ALLEN 7499 SALESMAN 1600 19500 WARD 7521 SALESMAN 1250 15500 JONES 7566 MANAGER 2975 0 MARTIN 7654 SALESMAN 1250 16400 BLAKE 7698 MANAGER 2850 0 CLARK 7782 MANAGER 2450 0 SCOTT 7788 ANALYST 3000 0 KING 7839 PRESIDENT 5000 0 TURNER 7844 SALESMAN 1500 18000 ADAMS 7876 CLERK 1100 0 JAMES 7900 CLERK 950 0 FORD 7902 ANALYST 3000 0 MILLER 7934 CLERK 1300 0 已选择14行。
ed修改上一条命令:保存退出,/开始执行
SQL> ed 已写入 file afiedt.buf 1* select ename,empno, job, sal, nvl(sal*12+comm,0)as "年 入" from emp SQL> / ENAME EMPNO JOB SAL 年 入 -------------------- ---------- ------------------ ---------- ---------- SMITH 7369 CLERK 800 0 ALLEN 7499 SALESMAN 1600 19500 WARD 7521 SALESMAN 1250 15500 JONES 7566 MANAGER 2975 0 MARTIN 7654 SALESMAN 1250 16400 BLAKE 7698 MANAGER 2850 0 CLARK 7782 MANAGER 2450 0 SCOTT 7788 ANALYST 3000 0 KING 7839 PRESIDENT 5000 0 TURNER 7844 SALESMAN 1500 18000 ADAMS 7876 CLERK 1100 0 JAMES 7900 CLERK 950 0 FORD 7902 ANALYST 3000 0 MILLER 7934 CLERK 1300 0 已选择14行。
where 过滤,查询奖金为空的员工:
SQL> select * from emp where comm is null; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7566 JONES MANAGER 7839 02-4月 -81 2975 20 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 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 已选择10行。
||连接符,与虚表
SQL> select ‘Hello ‘ || ‘World‘ from emp; ‘HELLO‘||‘WORLD‘ ---------------------- Hello World Hello World Hello World Hello World Hello World Hello World Hello World Hello World Hello World Hello World Hello World Hello World Hello World Hello World 已选择14行。
dual虚表:
SQL> select ‘Hello ‘ || ‘World‘ from dual; ‘HELLO‘||‘WORLD‘ ---------------------- Hello World
利用伪表,显示系统时间
SQL> select sysdate from dual; SYSDATE -------------- 25-8月 -16
DISTINCT 删除重复行,显示部门个数:
SQL> select DISTINCT deptno from emp; DEPTNO ---------- 30 20 10
DISTINCT修饰多个字段时,当两个数据完全一样才认为是重复的
SQL> select deptno,job from emp; DEPTNO JOB ---------- ------------------ 20 CLERK 30 SALESMAN 30 SALESMAN 20 MANAGER 30 SALESMAN 30 MANAGER 10 MANAGER 20 ANALYST 10 PRESIDENT 30 SALESMAN 20 CLERK 30 CLERK 20 ANALYST 10 CLERK 已选择14行。 SQL> 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行。
SQLplus工具的 help
SQL> help topic Help is available on the following topics: / @ @@ ACCEPT APPEND ARCHIVE LOG ATTRIBUTE BREAK BTITLE CHANGE CLEAR COLUMN COMPUTE CONNECT COPY DEFINE DEL DESCRIBE DISCONNECT EDIT EXECUTE EXIT GET HELP HOST INDEX INPUT LIST MENU PASSWORD PAUSE PRINT PROMPT QUIT RECOVER REMARK REPFOOTER REPHEADER RESERVED WORDS (PL/SQL) RESERVED WORDS (SQL) RUN SAVE SET SHOW SHUTDOWN SPOOL SQLPLUS START STARTUP STORE TIMING TOPICS TTITLE UNDEFINE VARIABLE WHENEVER OSERROR WHENEVER SQLERROR XQUERY SQL>
edit的帮助:
SQL> ? edit EDIT ---- Invokes an operating system text editor on the contents of the specified file or on the contents of the SQL buffer. The buffer has no command history list and does not record SQL*Plus commands. ED[IT] [file_name[.ext]] SQL>
Oracle系统常用的参数
SQL> set pagesize 299 SQL> set linesize 299 SQL> select * from v$nls_parameters; PARAMETER VALUE ----------------------------- ----------- NLS_LANGUAGE SIMPLIFIED CHINESE NLS_TERRITORY CHINA NLS_CURRENCY ￥ NLS_ISO_CURRENCY CHINA NLS_NUMERIC_CHARACTERS ., NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE SIMPLIFIED CHINESE NLS_CHARACTERSET AL32UTF8 NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY ￥ NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE 已选择19行。
默认的时间显示
SQL> select sysdate from dual; SYSDATE -------------- 25-8月 -16
临时修改系统时间的显示方式:
SQL> alter session set NLS_DATE_FORMAT=‘yyyy-mm-dd‘; 会话已更改。 SQL> select sysdate from dual; SYSDATE ---------- 2016-08-25 SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7782 CLARK MANAGER 7839 1981-06-09 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7876 ADAMS CLERK 7788 1987-05-23 1100 20 7900 JAMES CLERK 7698 1981-12-03 950 30 7902 FORD ANALYST 7566 1981-12-03 3000 20 7934 MILLER CLERK 7782 1982-01-23 1300 10 已选择14行。
where过滤字句 > 大于:
查询入职比80年1月1号晚的员工:日期格式是敏感的
SQL> select * from emp where hiredate > ‘1980-01-01‘; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7782 CLARK MANAGER 7839 1981-06-09 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7876 ADAMS CLERK 7788 1987-05-23 1100 20 7900 JAMES CLERK 7698 1981-12-03 950 30 7902 FORD ANALYST 7566 1981-12-03 3000 20 7934 MILLER CLERK 7782 1982-01-23 1300 10 已选择14行。
where过滤字句between and,查询工资在1000 ~ 2000之间的
SQL> select * from emp where sal between 1000 and 2000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7876 ADAMS CLERK 7788 1987-05-23 1100 20 7934 MILLER CLERK 7782 1982-01-23 1300 10 已选择6行。
where过滤字句between A and B,A 必须小于等于 B
SQL> select * from emp where sal between 9000 and 200; 未选定行 SQL>
where过滤字句 and
SQL> select * from emp where sal <=9000 and sal > 2000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 7566 JONES MANAGER 7839 1981-04-02 2975 20 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7782 CLARK MANAGER 7839 1981-06-09 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7902 FORD ANALYST 7566 1981-12-03 3000 20 已选择6行。
where过滤字句 in
SQL> select * from emp where deptno in (10,20); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 800 20 7566 JONES MANAGER 7839 1981-04-02 2975 20 7782 CLARK MANAGER 7839 1981-06-09 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7876 ADAMS CLERK 7788 1987-05-23 1100 20 7902 FORD ANALYST 7566 1981-12-03 3000 20 7934 MILLER CLERK 7782 1982-01-23 1300 10 已选择8行。
where过滤字句 not in
SQL> select * from emp where deptno not in (10,20); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7900 JAMES CLERK 7698 1981-12-03 950 30 已选择6行。
where过滤字句 not in 与null在一起,受影响
SQL> select * from emp where deptno in (10,20); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 800 20 7566 JONES MANAGER 7839 1981-04-02 2975 20 7782 CLARK MANAGER 7839 1981-06-09 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7876 ADAMS CLERK 7788 1987-05-23 1100 20 7902 FORD ANALYST 7566 1981-12-03 3000 20 7934 MILLER CLERK 7782 1982-01-23 1300 10 已选择8行。 SQL> select * from emp where deptno in (10,20,null); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 800 20 7566 JONES MANAGER 7839 1981-04-02 2975 20 7782 CLARK MANAGER 7839 1981-06-09 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7876 ADAMS CLERK 7788 1987-05-23 1100 20 7902 FORD ANALYST 7566 1981-12-03 3000 20 7934 MILLER CLERK 7782 1982-01-23 1300 10 已选择8行。 SQL> select * from emp where deptno not in (10,20,null); 未选定行
where like模糊查询,查询员工姓名S开头的信息:
SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7782 CLARK MANAGER 7839 1981-06-09 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7876 ADAMS CLERK 7788 1987-05-23 1100 20 7900 JAMES CLERK 7698 1981-12-03 950 30 7902 FORD ANALYST 7566 1981-12-03 3000 20 7934 MILLER CLERK 7782 1982-01-23 1300 10 已选择14行。 SQL> select * from emp where ename like ‘S%‘; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 800 20 7788 SCOTT ANALYST 7566 1987-04-19 3000 20
本文出自 “魂斗罗” 博客，谢绝转载！
数据库编程1 Oracle 过滤 函数 分组 外连接 自连接
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。