oracle 查询 函数练习

/*
--以下代码是对emp表进行显示宽度设置
col empno for 9999;
col ename for a10;
col job for a10;
col mgr for 9999;
col hiredate for a12;
col sal for 9999;
col comm for 9999;
col deptno for 99;
set pagesize 20;

--创建新表new_emp,复制emp表中的结构和数据到new_emp表中
create table xxx
as
select * from emp;

--删除xxx表
drop table xxx purge;
*/

oracle数据库服务器由二个部分组成:
A)数据库(物理概念,底层是数据库专用文件的集合)
B)数据库实例(逻辑概念,只能能通过数据库实例操作数据库)

SQL
B) DML(数据操纵语言,例如:insert,update,delete,SELECT)
B)DDL(数据定义语言,例如:create table,drop table,alter table)
C) DCL(数据控制语言,例如:grant授予,revoke收权)
D) TCL(事务控制语言,例如:rollback,commit,事务开始)

-------------------------------------------------------------------------------------

从sqlplus环境,以scott用户和123456密码进入oracle数据库实例
c:\>sqlplus scott/123456

退出sqlplus环境
SQL:\>exit;

查询scott用户下的所有表
select * from tab;

查询当前用户是谁
show user;

设置显示的列宽(字符型、日期型),10个字符宽度,a表示字符型
col ename for a12;
a12:表示显示宽度为12个字符,a大小写不敏感

执行最近一次的SQL语句,并不是SQLPLUS命令
/表示最近执行过的SQL语句
col ename for a10,它不是SQL语句,而是oracle提供的专用SQLPLUS工具中的命令

清屏
host cls;
SQLPLUS命令,可以简写,分号可省略
SQL语句,不可简写,必须以分号结束

查询emp表的结构
desc emp;
数字型:number
字符型:varchar2
日期型:date

查询emp表的所有内容
select * from emp;

设置显示的列宽(数字型),9表示数字型,一个9表示一个数字位,四个9表示四个数字位
col empno for 999999;
col表示:column
empno表示:列名
for表示:format
999999:表示显示6个字符宽度,只用于数字

设置在一页中最多显示20条记录
set pagesize 20;

查询emp表的员工编号,姓名,工资,部门号
select empno,ename,job,deptno from emp;

查询emp表的不重复的工作
select distinct job from emp;

select distinct job,sal from emp;
最终由job和sal的笛卡尔积决定

查询员工的编号,姓名,月薪,年薪(月薪*12)
select empno,ename,sal,sal*12 from emp;

修改最近的一条SQL语句
edit;

查询员工的编号,姓名,月薪,年薪,年收入(年薪+奖金有空值)
select empno,ename,sal,sal*12,sal*12+comm from emp;
NULL运算数字=NULL

解决null的问题,使用NVL()函数,NVL(a,b):如果a是空,用b替代
select empno,ename,sal,sal*12,sal*12+NVL(comm,0) from emp;
null!=0
null!=空白字符串

使用别名,查询员工的编号,姓名,月薪,年薪,年收入(年薪+奖金)
select empno AS "编号",ename AS "姓名",sal AS "月薪",sal*12 AS "年薪",sal*12+NVL(comm,0) AS "年收入"
from emp;//正确

select empno "编号",ename "姓名",sal "月薪",sal*12 "年薪",sal*12+NVL(comm,0) "年收入"
from emp;//正确

select empno ‘编号‘,ename ‘姓名‘,sal ‘月薪‘,sal*12 ‘年薪‘,sal*12+NVL(comm,0) ‘年收入‘
from emp;//出错,

select empno 编号,ename 姓名,sal 月薪,sal*12 年薪,sal*12+NVL(comm,0) 年收入
from emp;//正确

select empno 编 号,ename 姓 名,sal 月 薪,sal*12 年 薪,sal*12+NVL(comm,0) 年 收 入
from emp;//出错

select empno "编 号",ename "姓 名",sal "月 薪",sal*12 "年 薪",sal*12+NVL(comm,0) "年 收 入"
from emp;//正确,要在别名中使用空格符,得用双引号

使用字符串连接符号,输出"hello world",使用dual表
dual表是oracle为了拼一些语言,而设计的一个哑表。
因为oracle语法,有select子句,必须有from子句

显示系统当前时间
select now()这是mysql的语法
select sysdate from dual;

使用字符串连接符号,显示如下格式信息:xxxx的薪水是yyyy
方式一:select ‘hello world‘ from dual;
方式二:select ‘hello‘ || ‘ world‘ from dual;
select ename || ‘的薪水‘ || sal "薪水情况" from emp;

保存SQL语句到文件,并创建sql文件
spool d:\1234.sql;

保存SQL语句及其执行的结果
spool off;

执行文件中的sql语句,该文件必须是*.sql文件
@ d:\1234.sql;

单行注释
--select * from emp;

多行注释
/**/

SQL vs SQLPLUS

SQL
1)语言
2)ANSI标准
3)关健字不可缩写
4)必须是分号结束
5)更新表中的数据

SQLPLUS
1)环境,命令
2)是Oracle的特征之一
3)关健字可缩写
4)分号可有可无
5)不能更新表中的数据

HTML:<!-- -->
JSP: <%-- --%>
XML: <!-- -->
Pros:#
Java:
A)//
B)/* */
C)/** */
MySQL:#
Oracle:
A)--
B)/* */
JS:
A)//
B)/* */
CSS:
/* */

-------------------------------------------------------------------------------------

查询20号部门的员工
select *
from emp
where deptno = 20;

查询姓名是SMITH的员工,字符串值,大小写敏感
select *
from emp
where ename=‘SMITH‘;
不敏感:表名、字段名,‘A‘
敏感:字符串常量值

查询1980年12月17日入职的员工,"17-12月-80"满足oracle默认日期格式(DD-MON-RR表示2位的年份)
select *
from emp
where hiredate = ‘17-12月-80‘;

查询工资大于1500的员工
select * from emp where sal > 1500;

查询薪水在1300到1600之间的员工,包括1300和1600
select * from emp where sal>=1300 and sal<=1600;//正确
select * from emp where sal<=1600 and sal>=1300;//正确
select * from emp where sal between 1300 and 1600;//如果是数值型:小值在前,大值在后,返之无值

查询入职时间在"20-2月-81"到"23-1月-82"之间的员工
select * from emp where hiredate between ‘23-1月-82‘ and ‘20-2月-81‘;//无值
select * from emp where hiredate between ‘20-2月-81‘ and ‘23-1月-82‘;//如果是日期型:小值在后,大值在前,返之无值

查询20号或30号部门的员工
select * from emp where deptno=20 or deptno=30;
select * from emp where deptno in (20,30);

查询姓名以"S"开头的员工
select * from emp where ename like ‘S%‘;
%表示:0个,1个,多个字符

_表示:1个字符

查询姓名以"N"结束的员工,%表示0个,或多个字符
select * from emp where ename like ‘%N‘;

查询姓名是4个字符的员工,_下划线只能表示1个字符
select * from emp where ename like ‘W___‘;

查询员工姓名中含有‘_‘的员工,让\后的字符回归本来意思【like ‘%\_%‘ escape ‘\‘】
select * from emp where ename like ‘%\_%‘ escape ‘\‘;//正确
select * from emp where ename like ‘%E_%‘ escape ‘E‘;//正确
select * from emp where ename like ‘%$_%‘ escape ‘$‘;//正确
select * from emp where ename like ‘%#_%‘ escape ‘#‘;//正确
select * from emp where ename like ‘%@_%‘ escape ‘@‘;//正确
select * from emp where ename like ‘%%_%‘ escape ‘%‘;//错误

查询佣金为null的员工
select * from emp where comm = null;//错误
select * from emp where comm is null;//正确
select * from emp where comm is not null;//正确

查询无佣金且工资大于1500的员工
select * from emp where (comm is null) and (sal>1500);

查询工资是1500或,3000或5000的员工
select * from emp where sal in (1500,3000,5000);

查询职位是"MANAGER"或职位不是"ANALYST"的员工(方式一)
select * from emp where (job = ‘MANAGER‘) or (job != ‘ANALYST‘);

查询职位是"MANAGER"或职位不是"ANALYST"的员工(方式二)
select * from emp where (job = ‘MANAGER‘) or not (job = ‘ANALYST‘);

查询员工信息(编号,姓名,月薪,年薪),按月薪升序排序,默认升序
select empno "编号",ename "姓名",sal "月薪",sal*12 "年薪" from emp order by sal asc;

查询员工信息,按入职日期降序排序
select * from emp order by hiredate desc;

order by后面可以跟列名、别名、表达式、列号,从1开始
列名排序:select * from emp order by hiredate desc;
别名排序:select ename "姓名",sal "月薪",hiredate "入职时间" from emp order by 入职时间 desc;
表达式排序:select ename "姓名",sal*12 "年薪",hiredate "入职时间" from emp order by sal*12 desc;
列号排序:select ename "姓名",sal*12 "年薪",hiredate "入职时间" from emp order by 2 desc;

查询员工信息,按佣金升序或降序排列,null值看成最大值
select * from emp order by comm desc;

查询员工信息,按工资降序排列,相同工资的员工再按入职时间升序排列
select * from emp order by sal desc,hiredate asc;

查询20号部门,且工资大于1500,按职入时间降序排列
select *
from emp
where (deptno=20) and (sal > 1500)
order by hiredate desc;

-------------------------------------------------------------------------------------

测试lower/upper/initcap函数
select lower(‘www.itCAST.cN‘) from dual;
select upper(‘www.itCAST.cN‘) from dual;
select initcap(‘www.itCAST.cN‘) from dual;

测试concat/substr函数,从1开始
select concat(‘hello‘,‘ world‘) from dual;
select ‘hello‘ || ‘ world‘ from dual;
select substr(‘www.itcast.cn‘,12,2) from dual;
12表示:从第几个字符开始,第一个字符为1
2表示:共取几个字符

测试length/lengthb函数,前提是,装oracle时,编码方式为UTF-8,一个中文占3个Byte长度
select length(‘CHINA中国‘) from dual;//7
select lengthb(‘CHINA中国‘) from dual;

测试instr/lpad/rpad函数
select instr(‘你好 world‘,‘w‘) from dual;//4
select lpad(‘hello‘,10,‘@‘) from dual;
select rpad(‘hello‘,10,‘#‘) from dual;

测试trim/replace函数
select trim(‘w‘ from ‘wwwwwbookwwwwwwwwwwwwww‘) from dual;
select replace(‘www.itcast.cn‘,‘cn‘,‘com‘) from dual;

测试round/trunc/mod函数
select round(3.1415,3) from dual;
select trunc(3.1415,3) from dual;
select mod(10,3) from dual;

测试round作用于当前时间(month)
12-3月-13
select round(sysdate,‘month‘) from dual;//01-3月-13
select round(sysdate,‘year‘) from dual;//01-1月-13


测试trunc作用于当前时间(month)
12-3月-13
select trunc(sysdate,‘month‘) from dual;//01-3月-13

测试trunc作用于当前时间(year)
select trunc(sysdate,‘year‘) from dual;//01-1月-13

round和trunc除了用在数值型,也能用在日期型。

测试sysdate,默认情况下,只显示日期部份,格式为:日-月-2位的年
select sysdate from dual;

显示昨天,今天,明天的日期,日期类型只能+-数值
select sysdate-1 "昨天",sysdate "今天",sysdate+1 "明天" from dual;

以年和月形式显示员工近似工龄,只能日期-日期=数值
select ename,hiredate,(sysdate-hiredate)/30 "近似月工龄",(sysdate-hiredate)/365 "近似年工龄" from emp;

使用months_between函数,计算到年底还有多少个月
select months_between(‘31-12月-13‘,sysdate) from dual;

使用months_between函数,以精确月形式显示员工工龄
select ename,(sysdate-hiredate)/30 "近似月工龄",months_between(sysdate,hiredate) "精确月工龄" from emp;

测试add_months函数
select add_months(sysdate,1) from dual;

测试next_day函数,从今天开始算,下一个星期三是多少号?【中文平台】
select next_day(sysdate,‘星期日‘) from dual;

测试last_day函数,本月最后一天是多少号?
select last_day(sysdate) from dual;

测试last_day函数,下一个月最后一天是多少号?
select last_day(add_months(sysdate,1)) from dual;

-------------------------------------------------------------------------------------

双引:
1)为列设置别名

单引:
1)字符串常量单引

 


动手练习:

将今天讲过的OracleSQL,删除答案,自已练习一遍

 

oracle 查询 函数练习,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。