sql*plus命令:
column的使用 – 格式 化列
column SAL format $9999999.00 (设置数字显示格式)
column ENAME format a15;(设置字符串显示15个字符)
column SAL justify left/right/center (设置对齐格式 )
column SAL heading emp_SAL (设置显示的头部名)
column 列名 clear(清除格式 )
column SAL justify left format $99,999.00 (定义工资的显示格式)
清屏 sql>clear scr
基本查询
查询所有列 select * from EMP;
查询具体列 select EMPNO,ENAME from EMP;
列运算 select SAL*12 from EMP;
可以使用+ - * / % 对列进行运算
给列起别名
select SAL*12 年薪 from EMP;
select SAL*12 income from EMP;
用||拼凑列或其它值
select ENAME||’(’ || EMPNO || ’)’ from EMP;
select '10*2='||(10*2) from dual;
查询员工的信息显示如下格式
no=7396/name=SMITH/sal=1500
select 'no='||empno||'/name='||ename||'/sal='||sal from emp;
NUL函数使用-如果原来的列值为null的话,由指定的数值替代
select ENAME,SAL*COMM from EMP;
Select ENAME,SAL*(NUL(COMM,0)) FROM EMP
去重 - distinct
select distinct ENAME,JOB from EMP;
第2条语句是对两个列组合起来去重,即只有两个列组合起来也是相同的才会去重
查询各个员工的年总收到(包括提成)
selcet
where 子句 - 过滤
如果要查找符合某些条件记录或结果,则可以使用where子句进行过滤
使用 >,<,>=,<=,!=,<>
查看 部门号为30的所有员工 select * from EMP where DEPTNO=30
查看工资高于1000的所有员工 select * from EMP where SAL>1000
where 子句 - 过滤
找出名字为SMITH的员工的工资 select SAL from EMP where ENAME=‘SMITH’
找出名字不是SMITH的员工的工资 select SAL from EMP where ENAME!=‘SMITH’
select SAL from EMP where ENAME<>‘SMITH’
使用and和or
条件1 and 条件2:且
条件1 or 条件2 :或
查询月薪在大于等于1000小于等于1500之间的员工
select * from EMP Where SAL >=1000 And SAL<=1500;
查询在部门10或者部门20的员工
select * from EMP where DEPTNO=10 or DEPTNO=20
查询部门为20且工资大于1200的员工
select * from emp where deptno=20 sal>1200;
查询工资大于1500或者入职日期早于 23-JAN-82 的员工
select * from emp where sal>1500 or hiredate<'23-JAN-82';
使用is null 和 is not null
查询没有提成的员工 select * from EMP Where COMM is null;
查询有提成的员工 select * from EMP Where COMM is not null;
使用like和not like
找出名字为SMITH的员工的工资
select SAL from EMP where ENAME like ‘SMITH’
找出名字不为SMITH的员工的工资
select SAL from EMP where ENAME not like ‘SMITH’
使用like和not like
找出名字包含M的员工
select * from EMP where ENAME like ‘%M%’
找出名字第2个字母不是A的员工
select * from EMP where ENAME not like ‘_A%’
like 后面可以跟通配符进行查找 _ :代表一个字母 %:代表任意多个字母
where 子句 - 过滤
查询以S_打头的表
select table_name from user_tables where table_name=‘S_EMP’
查某个具体表名时,表名字符串必须大写
select * from user_tables Where table_name like ‘s\_%’ escape ‘\’
escape 用来指定转义符
练习
找出名字中含有a和e的的员工
select * from enp where ename like '%A%' and ename like '%E%;
找出名字最后一个字母是R的员工
between…and…
查询月薪在1000-1500之间的员工
select * from EMP Where SAL between 1000 and 1500;
可以在between….and …前加not,表示取反,表示 不在某个范围
查询月薪不在1000-1500之间的员工
select * from EMP Where SAL not between 1000 and 1500;
练习:查询年总收入在5000到15000的员工
select * from EMP Where SAL*12 between 5000 and 15000;
select * from emp where nvl(sal,0)*12+nvl(comm,0) between 5000 and 15000;
查询入职日期不在22-FEB-81到09-JUN-81之间的员工
select * from emp where
in和not in
in (value1,value2,value3…)
表示在指定值列表中去匹配
查询员工号为7900,7902,7988,7566
select * from EMP where EMPNO in (7900,7902,7988,7566)
练习
找出20、30部门中工资在1000到1500之间的员工。
select * from emp where (sal between 1000 and 1500) and deptno in (20,30);
order by - 排序
升序-asc(默认) order by 列名 asc
select * from EMP order by SAL asc
降序-desc order by 列名 desc
select * from EMP order by SAL desc
按部门号升序,工资降序
select * from EMP order by DEPTNO,SAL desc
按第一字段排序
select * from EMP order by 1
练习
找出所有有提成的员工,列出名字、工资、提成,显示结果按工资从小到大,提成从大到
小。
select * from emp order comm deptno,sal desc
selct ename,nv1(sal,0),comm from wehere () and comm!=0 order
SQL进阶
lower将字符串转成小写
select lower(‘SQLPLUS’)from DUAL;
将SQLPLUSL转成小写
select lower(ENNAME)from EMP;
upper将字符串转成大写
select upper('HELLOWORD')from DUAL;
常在过滤条件中使用
select * from EMP where upper(ENAME)='SMITH';
initcap将首字母变成大写,其余变小写
select initcap('HELLOWORD')from DUAL;
concat用来连接字符串
select concat('HELLO','WORD')from DUAL;
length用来求字符长度
Select length('hello') from DUAL;
Select ENAME,lenth(ENAME) from EMP;
replace用来将字符串中的某些内容替换指定内容
select ename, replace ('Minwell',ename)from emp where ename='maxwell';
substr用来截取子串(取前6个字符)
select substr('toceansoft',1,6) from dual;
(取后2个字符,负数表示从后面取)
select substr('toceansoft',-2) from dual;
练习
找出谁是最高领导,将名字按小写形式显示。
select lower(ename) from emp where mgr is null;
数值函数
函数名 说明 例子
round (四舍五入) select round(45.932,2) from dual
trunc 截取 select trunc(45.932,2) from dual
单行函数-数值函数
ROUND (45.923, 2)45.92
ROUND (45.923, 0)46
ROUND (45.923, -1)50
TRUNC (45.923, 2)45.92
TRUNC (45.923)45
TRUNC (45.923, -1)40
单行函数:日期函数
sysdate - 系统时间
函数名 说明
select sysdate from dual 从伪表里查系统时间,以默认的格式输出
Sysdate+(5/24/60/60) 在系统时间基础上延迟5秒
Sysdate+5/24/60 在系统时间基础上延迟5分
Sysdate+5/24 在系统时间基础上延迟5小时
sysdate+5 在系统时间基础上延迟5天
单行函数
函数名 说明
months_between 2个日期之间有多少个月
add_months 在第一个日期上增加多少月, 可以是负数
last_day 一个月最后一天
Next_day 求下一个日期
round 四舍五入
trunc 截取日期,多余的都裁掉
单行函数-字符函数
求今天过5个月后的日期 select add_months(sysdate,5) from DUAL
求距今天5个月之前的日期 select add_months(sysdate,-5) from DUAL
求距今天5年后的日期 select add_months(sysdate,5*12) from DUAL
单行函数-日期函数
查询今天和160天后之间的月分 select months_between(sysdate,sysdate+160) from
DUAL
查询本月的最后一天 select last_day(sysdate) from DUAL
查询下一个星期四的日期 select next_day(sysdate,’Friday’) from DUAL
round对日期四舍五入 select round(sysdate,’day’) from DUAL day为要四舍五入的
范围
trunc对日期进行截取 select trunc(sysdate,‘month’) from DUAL month为要截取的
范围
练习
上月末的日期 select last_day(add_months(sysdate,-1))from dual;
本月的最后一秒 select trunc(add_months(sysdate,1),'day')-1/24/60/60 from dual;
本周星期一的日期 select next_day(sysdate,'Monday')-7 from DUAL;
年初至今天的天数 select (sysdate-trunc(sysdate,'year')) from dual;
格式转换函数
函数名 说明 例子
to_char to_char(num,’格式’) --从数字转换为char select to_char(SAL,’fm
$99,999.00’) from dual
to_char(date,’fmt ’)– 从日期转换为char slect to_char(sysdate,’
yyyy’)from dual
to_date 字符串转日期 select to_date(‘2000 11 20
’,’yyyy mm dd’) from dual
to_num 字符转数字 select to_number(‘10’)
from dual
group by 子句与组函数:按....分组
对分组后的子句进行过滤还可以用having
Where 是对记录进行过滤
组函数
函数名 说明
avg 求平均值
count 统计总数
max 求最大值
min 求最小值
sun 求和
注:所有组函数都会忽略空值,avg、sum 只能作用于数值类型
组函数的特点是多条记录进去,出来的是一个结果
求有提成员工的提成平均值 select avg(nul(COMM,0))from EMP; select avg(comm)
from emp;
求有多少人有提成 Select count(COMM) from EMP;
统计总人数 Select count(*) from EMP;
员工分布在多少个部门 select count(DEPTNO) from EMP;X
select count(distinct DEPTNO) from from EMP;
各个部门的人数: select count(*) from emp group by DEPTNO;
如果想求各个部门的平均工资,这个时候需要将员工按部门进行分组,然后按组来 求平
均工资。需要使用group by 子句
select DEPTNO,avg(SAL) aa from EMP group by DEPTNO
注意:group by 子句也会触发排序
如果要查询部门总人数大于4的部门,这个时候不能用where,只能用having子句了
having子句用来过滤分组后的条件
Select count(EMPNO)from EMP group by DEPTNO having count(EMPNO) >4
语法限制 having后面不能跟别名
练习
哪些部门工资高于1000的人数超过2人,列出
部门编号。
平均工资大于1500的部门的人数。
查询最低的工资 select min(SAL) from EMP
查询最高的工资 select max(SAL) from EMP
查询各部门的工资总和select DEPTNO,sum(SAL) from EMP group by DEPTNO;
查询工资总和 select sum(SAL) from EMP
求各个部门的不同工种的平均工资
select DEPTNO,JOB,avg(SAL) aa from EMP group by DEPTNO,JOB
哪些部门的平均工资比2000高
select DEPTNO,avg(SAL) aa from EMP group by DEPTNO having avg(SAL) >2000
除了30部门以外的平均工资,并按平均工资升序排序
select DEPTNO,avg(SAL) aa from EMP group by DEPTNO having DEPTNO!=30
下面的sql效率更高 select DEPTNO,avg(SAL) aa from EMP where DEPTNO!=30 group by DEPTNO