sql基础语法

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