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,删除答案,自已练习一遍