--乘法
select ename,sal *12 from emp;
--加减乘除都类似
---------------------------------------------------------------------
--限定查询
--奖金大于1500的
select *from emp where sal>1500;
--有奖金的
select *from emp where comm is not null;
--没有奖金的
select *from emp where comm is null;
--有奖金且大于1500的
select *from emp where sal>1500 and comm is not null;
--工资大于1500或者有奖金的
select *from emp where sal>1500 or comm is not null;
--工资不大于1500且没奖金的
select *from emp where sal<=1500 and comm is null;
select *from emp where not (sal >1500 or comm is not null);
--工资大于1500但是小于3000的
select *from emp where sal>1500 and sal<3000;
select *from emp where sal between 1500 and 3000; --between是闭区间，是包含1500和3000的
--时间区间
select *from emp where hiredate between to_date('1981-01-01','yyyy-MM-dd') and to_date('1981-12-31','yyyy-MM-dd');
--查询雇员名字
select *from emp where ename='SMITH';
--查询员工编号
select *from emp where empno=7369 or empno=7499 or empno=7521;
select *from emp where empno in(7369,7499,7521);
select *from emp where empno not in(7369,7499,7521); --排除这3个，其他的都可以查
--模糊查询
select *from emp where ename like '_M%'; --第2个字母为M的
select *from emp where ename like '%M%';
select *from emp where ename like '%%'; --全查询
--不等号的用法
select * from emp where empno !=7369;
select *from emp where empno<> 7369;
--对结果集排序
--查询工资从低到高
select *from emp order by sal asc;
select *from emp order by sal desc,hiredate desc; --asc 当导游列相同时就按第二个来排序
--字符函数
select *from dual;--伪表
select 2*3 from dual;
select sysdate from dual;
--变成大写
select upper('smith') from dual;
--变成小写
select lower('SMITH') from dual;
--首字母大写
select initcap('smith') from dual;
--连接字符串
select concat('jr','smith') from dual; --只能在oracle中使用
select 'jr' ||'smith' from dual; --推荐使用
--截取字符串
select substr('hello',1,3) from dual; --索引从1开始
--获取字符串长度
select length('hello') from dual;
--字符串替换
select replace('hello','l','x') from dual; --把l替换为x
--------------------------------------------------------------------------------------------------
--通用函数
--数值函数
--四舍五入
select round(12.234) from dual;--取整的四舍五入 12
select round (12.657,2) from dual; --保留2位小数
select trunc(12.48) from dual;--取整
select trunc(12.48675，2) from dual; --保留2位小数
--取余
select mod(10,3) from dual;--10/3取余 =1
--日期函数
--日期-数字=日期 日期+数字=日期 日期-日期=数字
--查询员工进入公司的周数
select ename,round((sysdate -hiredate)/7) weeks from emp;
--查询所有员工进入公司的月数
select ename,round(months_between(sysdate,hiredate)) months from emp;
--求三个月后的日期
select add_months(sysdate,6) from dual;
select next_day(sysdate,'星期一') from dual; --下星期
select last_day(sysdate) from dual; --本月最后一天
select last_day(to_date('1997-1-23','yyyy-MM-dd')) from dual;
--转换函数
select ename ,
to_char(hiredate,'yyyy') 年，
to_char(hiredate,'mm')月，
to_char(hiredate,'dd') 日
from emp;
select to_char(10000000,'$999,999,999') from emp;
select to_number('20')+to_number('80') from dual; --数字相加
--查询员工年薪
select ename,(sal*12+nvl(comm,0)) yearsal from emp; --空和任何数计算都是空
--Decode函数，类似if else if (常用)
select decode(1,1,'one',2,'two','no name') from dual;
--查询所有职位的中文名
select ename, decode(job,
'CLERK',
'业务员',
'SALESMAN',
'销售',
'MANAGER',
'经理',
'ANALYST',
'分析员',
'PRESIDENT',
'总裁',
'无业')
from emp;
select ename,
case
when job = 'CLERK' then
'业务员'
when job = 'SALESMAN' then
'销售'
when job = 'MANAGER' then
'经理'
when job = 'ANALYST' then
'分析员'
when job = 'PRESIDENT' then
'总裁'
else
'无业'
end
from emp;
-------------------------------------------------------------------------------------------
--多表查询
select *from dept;
select *from emp,dept order by emp.deptno;
select *from emp e,dept d where e.deptno=d.deptno;
select e.*,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;
--查询出雇员的编号，姓名，部门编号，和名称，地址
select e.empno,e.ename,e.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;
--查询出每个员工的上级领导
select e.empno,e.ename,e1.empno,e1.ename from emp e,emp e1 where e.mgr=e1.empno;
select e.empno,e.ename,d.dname
from emp e,dept d ,salgrade s, emp e1
where e.deptno=d.deptno
and e.sal between s.losal
and s.hisal
and e.mgr=e1.empno;
select e.empno,e.ename,e1.empno,e1.ename from emp e,emp e1 where e.mgr=e1.empno(+) ;
--外连接
select *from emp order by deptno;
--查询出每个部门的员工
/*
分析：部门表是全量表，员工表示非全量表，
在做连接条件时，全量表在非全量表的哪端，那么连接时全量表的连接条件就在等号哪断
*/
--左连接
select * from dept d,emp e where d.deptno=e.deptno(+) order by e.deptno;
--右连接
select * from emp e，dept d where e.deptno(+)=d.deptno order by e.deptno;
-----------------------------作业
--查询与smith相同部门的员工姓名和雇佣日期
select *from emp t
where t.deptno= (select e.deptno from emp e where e.ename='SMITH')
and t.ename<> 'SMITH';
--查询工资比公司平均工资高的员工的员工号，姓名和工资
select t.empno,t.ename,t.sal
from emp t
where t.sal>(select avg(sal) from emp);
--查询各部门中工资比本部门平均工资高的员工号，姓名和工资
select t.empno,t.ename,t.sal
from emp t, (select avg(e.sal) avgsal,e.deptno from emp e group by e.deptno) a
where t.sal>a.avgsal and t.deptno=a.deptno;
--查询姓名中包含字母u的员工在相同部门的员工的员工号和姓名
select t.empno,t.ename from emp t
where t.deptno in( select e.deptno from emp e where e.ename like '%U%')
and t.empno not in ( select e.empno from emp e where e.ename like '%U%') ;
--查询管理者是king的员工姓名和工资
select t.ename,t.sal from emp t
where t.mgr in
(select e.empno from emp e where e.ename='KING');
-------------------------------------------------------------------------------------
---sql1999语法
select *from emp join dept using(deptno) where deptno=20;
select *from emp natural join dept;
select *from emp e join dept d on e.deptno=d.deptno;
select *from dept;
select *from dept d left join emp e on d.deptno=e.deptno;
select *from dept d,emp e where d.deptno=e.deptno(+);
---分组
select count(empno) from emp group by deptno;
select deptno,job,count(*) from emp group by deptno,job order by deptno;
select *from EMP for UPDATE;
--group by 后面有的字段，select后才可以有，group by后面没有的字段，select后面绝对不能有
select d.dname, d.loc, count(e.empno) from emp e, dept d where e.deptno = d.deptno group by d.dname, d.loc ;
----------------------------------------------------------------------------------------------------
--子查询
select *from emp t where t.sal>(select *from emp e where e.empno=7654);
select rownum ,t.* from emp t where rownum <6 ;
--pagesize 5
select *from(select rownum rw,a.* from (select *from emp ) a where rownum <16) b where b.rw>10;
select *from (select *from emp) where rownum>0;
--索引
create index person_index on person(p_name);
--视图
create view view2 as select *from emp t where t.deptno=20;
select *from view2;
--------------------------------------------------------------------------------------------------------
--pl/sql
--plsql是对sql语言的过程化扩展
-----
declare
begin
dbms_output.put_line('hello world');
end;
-------
declare
age number(3);
marry boolean := true; --boolean不能直接输出
pname varchar2(10) := 're jeknc';
begin
age := 20;
dbms_output.put_line(age);
if marry then
dbms_output.put_line('true');
else
dbms_output.put_line('false');
end if ;
dbms_output.put_line(pname);
end;
--常量和变量
--引用变量,引用表中的字段的类型
Myname emp.ename%type; --使用into来赋值
declare
pname emp.ename%type;
begin
select t.ename into pname from emp t where t.empno=7369;
dbms_output.put_line(pname);
end;
--记录型变量
Emprec emp%rowtype; --使用into来赋值
declare
Emprec emp%rowtype;
begin
select t.* into Emprec from emp t where t.empno=7369;
dbms_output.put_line(Emprec.empno || ' '||Emprec.ename||' '||Emprec.job);
end;
--if分支
语法1：
IF 条件 THEN 语句1;
语句2;
END IF;
语法2：
IF 条件 THEN 语句序列1;
ELSE 语句序列 2;
END IF;
语法3：
IF 条件 THEN 语句;
ELSIF 条件 THEN 语句;
ELSE 语句;
END IF;
--1
declare
pname number:=#
begin
if pname = 1 then
dbms_output.put_line('我是1');
else
dbms_output.put_line('我不是1');
end if;
end;
--2
declare
pname number := #
begin
if pname = 1 then
dbms_output.put_line('我是1');
elsif pname = 2 then
dbms_output.put_line('我是2');
else
dbms_output.put_line('我不是12');
end if;
end;
--loop循环语句
语法2：
Loop
EXIT [when 条件];
