Oracle专题4之Oracle基本查询
发布时间：2020-12-12 14:12:22 所属栏目：百科 来源：网络整理
导读：1、Oracle的基本查询语句 查询概述：查询是使用最频繁的一个操作，可以从一个表中查询数据，也可以从多个表中查询数据，本节主要围绕单表查询的操作。 基本查询的语法格式：SELECT * | {[DISTINCT] column | expression [alias]，...} FROM table [ WHERE co
1、Oracle的基本查询语句
a、排除重复行distinct
SQL> conn scott/02000059 已连接。 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行。 SQL> ``` ## b、查询日期列 - 日期列：是指数据类型为日期类型的列，如DATE类型。日期列的默认显示格式为DD-MON-RR。 - RR：代表年，而且只取年份的后两位数字。 - 日期格式中RR和YY的区别： | 指定日期 | 当前年份 | RR格式说明 | RR结果年份 | YY格式说明 | YY结果年份 | | -------- | -------- | -------- | -------- | -------- | -------- | | 18-1月-12 | 2017 | 指定年份在0-49之间，当前年份在0-49之间 | 2012 | 当前年份的前两位 + 指定日期的后两位 | 2012 | | 18-1月-81 | 2017 | 指定年份在50-99之间，当前年份在0-49之间 | 1981（上一世纪） | 当前年份的前两位 + 指定日期的后两位 | 2081 | | 18-1月-12 | 2060 | 指定年份在0-49之间，当前年份在50-99之间 | 2112（下一世纪） | 当前年份的前两位 + 指定日期的后两位 | 2012 | | 18-1月-81 | 2060 |指定年份在50-99之间，当前年份在50-99之间 | 2081 | 当前年份的前两位 + 指定日期的后两位 | 2081 | ## c、以简体中文来显示日期结果 - 如果以简体中文显示日期结果，那么可以将会话的NLS_LANGUAGE参数设置为SIMPLEFIED CHINESE。 - ALTER SESSION SET nls_language='SIMPLIFIED CHINESE'; SQL> ALTER SESSION SET nls_language='SIMPLIFIED 会话已更改。 pno,hiredate from emp; EMPNO HIREDATE 7369 17-12月-80 7499 20-2月 -81 7521 22-2月 -81 7566 02-4月 -81 7654 28-9月 -81 7698 01-5月 -81 7782 09-6月 -81 7788 19-4月 -87 7839 17-11月-81 7844 08-9月 -81 7876 23-5月 -87 7900 03-12月-81 7902 03-12月-81 7934 23-1月 -82 已选择14行。 ``` ## d、以美国英语显示日期结果
SQL> ALTER SESSION SET nls_language = 'AMERICAN'; Session altered. SQL> SELECT empno,hiredate from emp; EMPNO HIREDATE 7369 17-DEC-80 7499 20-FEB-81 7521 22-FEB-81 7566 02-APR-81 7654 28-SEP-81 7698 01-MAY-81 7782 09-JUN-81 7788 19-APR-87 7839 17-NOV-81 7844 08-SEP-81 7876 23-MAY-87 7900 03-DEC-81 7902 03-DEC-81 7934 23-JAN-82 14 rows selected. e、以特定形式显示日期结果
SQL> ALTER SESSION SET nls_date_format = 'YYYY/MM/DD'; Session altered. SQL> SELECT empno,hiredate from emp; EMPNO HIREDATE 7369 1980/12/17 7499 1981/02/20 7521 1981/02/22 7566 1981/04/02 7654 1981/09/28 7698 1981/05/01 7782 1981/06/09 7788 1987/04/19 7839 1981/11/17 7844 1981/09/08 7876 1987/05/23 7900 1981/12/03 7902 1981/12/03 7934 1982/01/23 14 rows selected. ``` # 2、Oracle查询语句中算术运算符的使用 ## a、使用算术运算符 - 算术运算符可以应用到日期类和数字列。 - 需求：应用scott.emp表查询雇员编号、雇员名、雇员月工资、雇员的年收入。如：SELECT empno,ename,sal,sal *12 year_sal form emp; SQL> SELECT empno,sal *12 year_sal from emp; EMPNO ENAME SAL YEAR_SAL 7369 SMITH 800 9600 7499 ALLEN 1600 19200 7521 WARD 1250 15000 7566 JONES 2975 35700 7654 MARTIN 1250 15000 7698 BLAKE 2850 34200 7782 CLARK 2450 29400 7788 SCOTT 3000 36000 7839 KING 5000 60000 7844 TURNER 1500 18000 7876 ADAMS 1100 13200 7900 JAMES 950 11400 7902 FORD 3000 36000 7934 MILLER 1300 15600 14 rows selected. ## b、算术运算符的优先级 - 我们了解到，算术运算符有加减乘除，乘除具有相同的优先级、加减具有相同的优先级，相同的优先级的运算符从左向右执行，乘除的优先级高于加减，而括号中的运算先执行。 - 优先级举例：应用scott.emp表查看雇员编号、雇员名，年收入（年薪加上半月工资），即：SELECT empno,sal * 12 + sal/2 year_sal from emp; SQL> SELECT empno,sal * 12 + sal/2 year_sal from emp; EMPNO ENAME YEAR_SAL 7369 SMITH 10000 7499 ALLEN 20000 7521 WARD 15625 7566 JONES 37187.5 7654 MARTIN 15625 7698 BLAKE 35625 7782 CLARK 30625 7788 SCOTT 37500 7839 KING 62500 7844 TURNER 18750 7876 ADAMS 13750 EMPNO ENAME YEAR_SAL 7900 JAMES 11875 7902 FORD 37500 7934 MILLER 16250 14 rows selected. ``` ## c、在算术运算中的空值NULL
SQL> SELECT empno,sal+ comm FROM emp; EMPNO ENAME SAL COMM SAL+COMM 7369 SMITH 800 7499 ALLEN 1600 300 1900 7521 WARD 1250 500 1750 7566 JONES 2975 7654 MARTIN 1250 1400 2650 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 0 1500 7876 ADAMS 1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 14 rows selected. ``` ----- **- 附件说明：空值和默认值** - 空值：当插入数据时，如果没有为特定列提供数据，并且该列没有默认值，那么其结果为NULL。 - 默认值：在创建表时可以使用default关键字为列设置默认值，在向表中插入数据时，如果没有为该列提供数据，那么该列将使用默认值。 - 默认值的示例： SQL> create table student(sid number(8,0) primary key,name varchar2(10),sex char(2) default '男',age number(2,0) default 20,address varchar2(50)); Table created. SQL> Insert into student(sid,name) values(20012001,'张三'); 1 row created. SQL> Insert into student values(20011002,'王五',default,'广东广州'); 1 row created. # 3、查询语句中的别名，连接符、字符串 ## a、列的别名 - 用于改变列的显示标题，列的别名可以跟在列名的后面，也可以在二者之间加AS关键字。如果别名中包含空格或者特殊的字符或者需要区分大小写，那么需要给别名加上双引号。 - 列的别名举例： - 不使用列的名称别名：SELECT empno,sal * 12 from emp; SQL> conn scott/02000059 已连接。 SQL> select empno,sal * 12 FROM emp; EMPNO ENAME SAL*12 7369 SMITH 9600 7499 ALLEN 19200 7521 WARD 15000 7566 JONES 35700 7654 MARTIN 15000 7698 BLAKE 34200 7782 CLARK 29400 7788 SCOTT 36000 7839 KING 60000 7844 TURNER 18000 7876 ADAMS 13200 EMPNO ENAME SAL*12 7900 JAMES 11400 7902 FORD 36000 7934 MILLER 15600 已选择14行。 - 使用列的别名：SELECT empno "雇员编号",ename "雇员名",sal * 12 "全年工资" FROM emp;、SELECT empno "雇员编号",sal * 12 AS "全年工资" FROM emp;、SELECT empno 雇员编号,ename 雇员名,sal * 12 全年工资 FROM emp;。 SQL> SELECT empno "雇员编号",sal * 12 "全年工资" FROM emp; 雇员编号 雇员名 全年工资 7369 SMITH 9600 7499 ALLEN 19200 7521 WARD 15000 7566 JONES 35700 7654 MARTIN 15000 7698 BLAKE 34200 7782 CLARK 29400 7788 SCOTT 36000 7839 KING 60000 7844 TURNER 18000 7876 ADAMS 13200 雇员编号 雇员名 全年工资 7900 JAMES 11400 7902 FORD 36000 7934 MILLER 15600 已选择14行。 SQL> SELECT empno "雇员编号",sal * 12 AS "全年工资" FROM emp; 雇员编号 雇员名 全年工资 7369 SMITH 9600 7499 ALLEN 19200 7521 WARD 15000 7566 JONES 35700 7654 MARTIN 15000 7698 BLAKE 34200 7782 CLARK 29400 7788 SCOTT 36000 7839 KING 60000 7844 TURNER 18000 7876 ADAMS 13200 雇员编号 雇员名 全年工资 7900 JAMES 11400 7902 FORD 36000 7934 MILLER 15600 已选择14行。 SQL> SELECT empno 雇员编号,sal * 12 全年工资 FROM emp; 雇员编号 雇员名 全年工资 7369 SMITH 9600 7499 ALLEN 19200 7521 WARD 15000 7566 JONES 35700 7654 MARTIN 15000 7698 BLAKE 34200 7782 CLARK 29400 7788 SCOTT 36000 7839 KING 60000 7844 TURNER 18000 7876 ADAMS 13200 雇员编号 雇员名 全年工资 7900 JAMES 11400 7902 FORD 36000 7934 MILLER 15600 已选择14行。 ## b、连接符 || - 当执行查询操作时，为了显示更有意义的结果值，有时候需要将多个字符串连接起来，连接字符串可以使用 “||”操作符或者concat函数。（可以用来“合并”列） - 举例：select ename || '的岗位是：' || job as 员工的职位信息描述 from emp; SQL> select ename || '的岗位是：' || job as 员工的职位信息描述 from emp; 员工的职位信息描述 SMITH的岗位是：CLERK ALLEN的岗位是：SALESMAN WARD的岗位是：SALESMAN JONES的岗位是：MANAGER MARTIN的岗位是：SALESMAN BLAKE的岗位是：MANAGER CLARK的岗位是：MANAGER SCOTT的岗位是：ANALYST KING的岗位是：PRESIDENT TURNER的岗位是：SALESMAN ADAMS的岗位是：CLERK 员工的职位信息描述 JAMES的岗位是：CLERK FORD的岗位是：ANALYST MILLER的岗位是：CLERK 已选择14行。 ## c、字符串 - 字符串可以是SELECT列表中的字符，数字，日期。日期和字符只能在单引号中出现。每当返回一行时，字符串被输出一次。 - 示例：SELECT ename || '的月工资是： ' || SAL from emp; SQL> SELECT ename || '的月工资是： ' || SAL from emp; ENAME||'的月工资是：'||SAL- SMITH的月工资是： 800 ALLEN的月工资是： 1600 WARD的月工资是： 1250 JONES的月工资是： 2975 MARTIN的月工资是： 1250 BLAKE的月工资是： 2850 CLARK的月工资是： 2450 SCOTT的月工资是： 3000 KING的月工资是： 5000 TURNER的月工资是： 1500 ADAMS的月工资是： 1100 ENAME||'的月工资是：'||SAL JAMES的月工资是： 950 FORD的月工资是： 3000 MILLER的月工资是： 1300 已选择14行。 ``` 4、Oracle过滤数据（比较运算）a、过滤数据
b、where 子句中使用数字值
SQL> SELECT * from emp where deptno = 20; 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 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7902 FORD ANALYST 7566 03-12月-81 3000 20 c、Where子句中使用字符值
SQL> SELECT * FROM emp WHERE job = 'MANAGER'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 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 d、Where子句中使用日期值
SQL> select * from emp where hiredate = '02-4月-81';
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
7566 JONES MANAGER 7839 02-4月 -81 2975
20
e、比较运算符运算符 |
说明 |
|