SQL
SELECT deptno,AVG(sal+200) -----多行函数
FROM emp
group by deptno;
SELECT ABS(-10066) FROM dual;-----单行函数，需要一条数据作为参数
单行函数特征
单行函数对单行操作
每行返回一个结果
--有可能返回值与原参数数据类型不一致
select to_char(hiredate,'yyyy') from emp;
---单行函数可以写在SELECT、WHERE、ORDER BY子句中
select *
from emp
where to_char(hiredate,'yyyy')>'1983';
select *
from emp
order by to_char(hiredate,'MM');
----函数的嵌套
select deptno,trunc(avg(sal))
from emp
group by deptno;
-----------------------大小写转换函数------------------------
SELECT INITCAP('DAN MORGAN') FROM dual;
SELECT UPPER('Dan Morgan') FROM dual;
SELECT LOWER('Dan Morgan') FROM dual;
SELECT concat('A','42')------------拼接字符函数
FROM dual;
SELECT SUBSTR('Take the first four characters', 2, 7) FIRST_FOUR----2是起始位置，7是取子串的长度
FROM dual; ------------dual是伪表，如果查询不需要某张数据表的话，那么可以使用伪表代替
SELECT length('ABC33有')----中文字符对应一个字符位，返回字符串的长度
FROM dual;
有些函数没有参数，有些函数包括一个或多个参数
函数可以嵌套
SELECT INSTR('Taketahefirstfaour characters', 'a', 5, 1) FOUND_1-----查找a在字符串中，从位置5处开始找，第1次出现的位置，返回index索引
FROM dual;
SELECT LPAD('Dan', 10, '*') FROM dual;
SELECT RPAD('Dan', 10, '*') FROM dual;
SELECT RPAD(LPAD('Dan', 10, '*'), 15, '*') FROM dual;
SELECT REPLACE('So What', 'o', 'ay')
FROM dual;
------------------------trim 1.去首尾空格的函数；2.去掉函数中的某个字符-----------------------------
SELECT TRIM(' Dan Morgan ') FROM dual;---------中间空格不可以去掉
SELECT TRIM('D' FROM 'Dan Morgan') FROM dual;
SELECT ASCII(SUBSTR('Dan Morgan',1,1)) FROM dual;
----chr 函数可以将ascii码进行转换成对应的字符
SELECT TRIM(CHR(68) FROM 'Dan Morgan') FROM dual;
写一个查询,用首字母大写，其它字母小写显示雇员的 ename，显示名字的长度，
并给每列一个适当的标签，条件是满足所有雇员名字的开始字母是J、A 或 M 的雇员，
并对查询结果按雇员的ename升序排序。（提示：使用initcap、length、substr）
----------方法1
select initcap(ename) as "姓名",length(ename) "姓名长度"
from
emp
where
substr(ename,1,1) in('J','A','M')
order by ename asc;
----------方法2
select
initcap(ename) 姓名,length(ename) 姓名长度
from
emp
where
ename like 'J%' or ename like 'A%' or ename like 'M%'
order by ename asc;
---------------------------数值函数------------------------------
select round(123.456,2) from dual;----123.46
select round(183.456,-2) from dual;----200
select TRUNC(45.926, 2) from dual;----- 45.92
select TRUNC(453.926, -2) from dual;----- 400
select mod(100,30) from dual;----10
select mod(100,20) from dual;----0
100条数据，每页显示24条数据，需要多少页100/24+1 mod(100,24)>0 100/24+1页
--------------------------日期函数------------------------------
select hiredate,hiredate+15 from emp;---- 加天数
select hiredate,add_months(hiredate,1) from emp;----加月份
select hiredate,add_months(hiredate,12) from emp;----加年
SELECT MONTHS_BETWEEN(SYSDATE+365, SYSDATE-365) FROM dual;----求月份差
select sysdate-hiredate from emp;----求日期之间的天数差
select round((sysdate-hiredate)/365) from emp;----求年份差
select sysdate from dual;
---求两个日期相差天数
select to_date('10-08-2006','dd-MM-yyyy')-to_date('04-08-2006','dd-MM-yyyy') from dual;
-----给一个日期加240小时
select hiredate,hiredate+240/24 from emp;
SELECT NEXT_DAY(hiredate, '星期五') FROM emp;
SELECT NEXT_DAY(sysdate, '星期一') FROM dual;
SELECT LAST_DAY(sysdate) FROM dual;
select hiredate,round(hiredate,'month') from emp;----根据日进行四舍五入
select hiredate,round(hiredate,'year') from emp;----根据月进行四舍五入
select hiredate,trunc(hiredate,'year') from emp;----直接截断，月份全部变成1
select hiredate,trunc(hiredate,'month') from emp;----直接截断，日期全部变成1
----------------------显示每月最后一天入职的员工信息-------------
select *
from emp
where hiredate=last_day(hiredate);
1.查询服务器当前时间
select sysdate from dual;
2.查询部门10,20的员工截止到2000年1月1日，工作了多少个月，入职的月份。(提示：使用months_between,extract)
-----使用to_date函数，注意日期格式与模式字符串要匹配
select ename,months_between(to_date('1-1-2000','dd-mm-yyyy'),hiredate),extract(month from hiredate)
from emp
where deptno in(10,20);
select ename,months_between(to_date('2000-11-6','yyyy-mm-dd'),hiredate),extract(month from hiredate)
from emp
where deptno in(10,20);
select
ename,
trunc(months_between(to_date('2000-11-6','yyyy-mm-dd'),hiredate)) "工龄(月)",
extract(month from hiredate) 入职月份
from emp
where deptno in(10,20);
3.如果员工试用期6个月，查询职位不是MANAGER的员工姓名，入职日期，转正日期，入职日期后的第一个星期一,
入职当月的最后一天日期。（提示：使用add_months,next_day,last_day)
select
ename 员工姓名,
hiredate 入职日期,
add_months(hiredate,6) 转正日期,next_day(hiredate,'星期一') 入职日期后的第一个星期一,
last_day(hiredate) 入职当月的最后一天日期
from emp
where job<>'MANAGER'
------------------------sql 1999 表连接语法------------------------
----自然连接，匹配连接
-----方法一：sql标准写法，跨平台
select *
from emp natural join dept;
----方法二：oracle自有写法，效率高
select *
from emp,dept
where emp.deptno=dept.deptno;
----方法三：如果两张表中有多列字段名一样，数据类型也一样，那么可以使用using子句指定两个表的匹配条件,符合sql标准
select *
from emp join dept using(deptno);
--------外连接---------------sql标准语法该如何实现呢？----------
---查询员工姓名及部门信息，要求所有部门信息都要列出来
select * from emp,dept where emp.deptno(+)=dept.deptno;
----使用sql标准怎么做？
select *
from emp e join dept d on e.deptno=d.deptno;-----join---on这是固定搭配 ，但是只能返回匹配数据
select *
from emp e right join dept d on e.deptno=d.deptno;----right join ..on 表示join右边的表示主表
---查询所有员工姓名及部门信息，
select *
from emp e left join dept d on e.deptno=d.deptno;----left join ..on 表示join左边的表示主表
---查询所有员工姓名及部门信息，要求所有部门信息也要列出来
select *
from emp e full join dept d on e.deptno=d.deptno;----全外连接，两张表的数据都出来
--------如果我们需要查询多张表，比如三张表：emp,dept,salgrade,怎么办？
----总结：查询多张表的sql标准语法是： join..on...join..on..join..on
-------查询员工姓名及部门信息，工资等级
select e.empno,e.ename,d.deptno,d.loc,s.grade
from emp e join dept d on e.deptno=d.deptno join salgrade s on (e.sal between s.losal and s.hisal);
-------查询工作地点在new york的员工姓名及部门信息，工资等级
select e.empno,e.ename,d.deptno,d.loc,s.grade
from emp e join dept d on e.deptno=d.deptno join salgrade s on (e.sal between s.losal and s.hisal)
where d.loc='NEW YORK';----条件的执行自右向左，这样效率高，所以把严苛的条件放在最右边，可以避免查询全表
---要想sql语句执行效率高，最重要是避免查询全表
----交叉连接
select *
from emp cross join dept;-------15*4=60条数据被查询出来，笛卡尔乘积
--------------ppt后面的作业题
select *
from emp e join dept d using(deptno)
where d.loc='CHICAGO'
select *
from emp e join dept d on e.deptno=d.deptno
where d.loc='CHICAGO'
select *
from emp worker left join emp boss
on worker.mgr=boss.empno
select *
from emp boss right join emp worker
on worker.mgr=boss.empno
-------
selec *
from emp join dept ------报错，无效的sql语句，缺少on子句
-------------------------转换函数-------------------------
to_char(): 日期--字符串 数字---字符串
select to_char(sysdate) from dual; 日期--字符串
select to_char(123) from dual; 数字---字符串
-------------to_char() 转换日期为字符串时，可以指定格式得到想要的格式和想要的值
select to_char(sysdate,'mm-dd') from dual;
select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'mm') from dual;
select to_char(sysdate,'yyyy') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:Mi:ss') from dual;
select to_char(sysdate,'dd-mm-yyyy') from dual;
select to_char(sysdate,'year-month-day') from dual;
select to_char(sysdate,'dy') from dual;
------需求：需要一个日期字符串，2018年6月6日 09:44 星期三???????
select to_char(sysdate,'yyyy')||'年'||to_char(sysdate,'mm')||'月'||to_char(sysdate,'dd')||'日' from dual;
---字符串转化为日期类型
----to_date();
select to_date('06-6月-2018') from dual;----ok,2018/6/6
select to_date('2018/06/06','yyyy/mm/dd') from dual; ---客户传过来的日期格式可能各种各样的字符串，所以解析的时候需要指定模式： 2018/06/06
select to_date('2018$$06$$06','yyyy$$mm$$dd') from dual;
----to_number()
---- 数字的字符串形式转为数字类型
select to_number('123') from dual;
select to_number() from dual;-----not ok 使用时不可以不给参数
select to_number('abc') from dual;----not ok to_number函数，要求参数必须是纯数字
显式：需要程序员手动操作
隐式：程序自动去做
Double a=123; int类型，房子是32位的，double类型，房子是64位的
int b=123.456; ---not ok 会造成数据精度丢失，所以不会隐式转换
int b=(int)123.456; ---ok
------------------------格式化工资---------------------
select to_char(sal) from emp;
select to_char(sal,'99999') from emp;------对应每个数字，使用9进行格式化，其它字符不可以
select to_char(sal,'99999.99') from emp;-----9的个数必须大于等于待格式化的数字的位数 sal number(7,2) 对应取值范围：-99999.99-99999.99
select to_char(sal,'L99999') from emp;----L 对应一个单词Locale 本地
select to_char(sal,'$99999') from emp;-----美元
select * from emp;
-------------------------------通用函数------------------------------------------
1.作用:nvl目的是转换null值
select ename,nvl(comm,0) from emp;
create table person(
ename varchar2(20),
sex char(1)
);
----nvl2函数是nvl函数的升级版，既能转换null值，又能转换非null值
insert into person(ename,sex) values('zhangsan1','1');
insert into person(ename,sex) values('zhangsan2',null);
insert into person(ename,sex) values('zhangsan3','1');
insert into person(ename,sex) values('zhangsan4',null);
insert into person(ename,sex) values('zhangsan5','1');
insert into person(ename,sex) values('zhangsan6',null);
select ename 姓名,nvl2(sex,'男','女') 性别
from person;----ok
select ename 姓名,nvl2(sex,1,0) 性别
from person;
-----nullif函数------------
select nullif('男','男') from dual;----null
select nullif('男','女') from dual;----男
-----trim去掉字符和空格，只能去掉首尾的，不能去掉中间的
select trim('d' from 'dadaadaaad') from dual;
---------------返回第一个不为空的值
select COALESCE(null,3*5,44) hz from dual; 返回15
select COALESCE(0,3*5,44) hz from dual; 返回0
select COALESCE(null,' ','AAA') hz from dual; --返回''
select COALESCE('','AAA') hz from dual; 返回AAA
select * from emp;
-------case语法---------------------
deptno: 10 10号部门， 20 20号部门 30 30号部门
----case 语句的关键字： case----end 中间是：when v1 then v2 多组，中间不需要符号隔开,else子句可有可无
---------case 语句根据表达式确定值-------------
select ename,job,
case deptno
when 10 then '10号部门'
when 20 then '20号部门'
else '30号部门' end 部门名称
from emp;
select ename,job,
case deptno
when 10 then '10号部门'
when 20 then '20号部门'
end 部门名称
from emp;
---------case 语句根据条件确定值-------------
select ename,job,sal,
case
when sal>=5000 then '工资真高呀'
when sal>=2000 then '工资中等'
else '工资太少了' end 工资情况
from emp; ------ok
-------使用decode函数对case语句进行改造------------------
select ename,job,
decode(deptno,10,'销售部门',20,'技术部门',30,'管理部门','无')
from emp;
f5(f4(f3(f2(f1(sal)))))----嵌套函数的执行顺序 f1--f2--f3--f4--f5
1.计算2000年1月1日到现在有多少月，多少周（四舍五入）。
select round(months_between(sysdate,to_date('2000-1-1','yyyy-mm-dd'))) 月数
from dual;
select round((sysdate-to_date('2000-1-1','yyyy-mm-dd'))/7) 周数
from dual;
5.查询员工的姓名及其经理编号，要求对于没有经理的显示“No Manager”字符串。
----方法一：case
select ename,
(case
when mgr>0 then to_char(mgr)
else 'No Manager' end) ---case语句每个分支返回值必须一样
from emp;
select ename,
(case
when mgr>0 then ''||mgr
else 'No Manager' end) ---case语句每个分支返回值必须一样
from emp;
----方法二：decode
select ename,decode(mgr,null,'No manager',mgr) ---decode 函数每种情况，返回值可以不同
from emp;
-----------------------使用case语句查询姓名，工资，及所缴税---------------
select ename,sal,sal*(
case
when sal<1000 then 0.0
when sal<2000 and sal>=1000 then 0.1
when sal<3000 and sal>=2000 then 0.15
else 0.2 end
) 缴税金额
from emp;
--------------------------------分页 查询----------------------------
设定每页显示3条数据，显示第一页的数据:1-3
1.固定伪列号
select e.*,rownum from emp e
2.把这个结果作为表进行加工查询
select e2.*
from (select e.*,rownum rn from emp e) e2
where e2.rn<=3 and e2.rn>=1;
3.显示第二页的数据:4-6
select e2.*
from (select e.*,rownum rn from emp e) e2
where e2.rn<=6 and e2.rn>=4;----高效
select e2.*
from (select e.*,rownum rn from emp e) e2
where e2.rn<7 and e2.rn>3;---低效
3.显示第三页的数据:7-9
select e2.*
from (select e.*,rownum rn from emp e) e2
where e2.rn<=9 and e2.rn>=7;----高效
----每页显示a：3条数据，显示第n页数据，那么你能把起始页通过公式表达出来吗？
----提取分页公式：
select e2.*
from (select e.*,rownum rn from emp e) e2
where e2.rn<=n*a and e2.rn>=(n-1)*a+1; -----------需要两个参数
select e2.*
from (select e.*,rownum rn from emp e) e2
where e2.rn<=n*3 and e2.rn>=(n-1)*3+1; -----------需要一个参数
1.1-3 startIndex: (1-1)*3+1 endIndex: 1*3
2.4-6 (2-1)*3+1 2*3
3.7-9 (3-1)*3+1 3*3
4.10-12 (4-1)*3+1 4*4
-------------------------------------生产环境：会先把数据排序再分页，灵活获取对应页面的数据-------------------------
1.先排序
select *
from emp
order by sal asc----先排序
2.固定伪列号
select e.*,rownum
from (select *
from emp
order by sal asc) e
3.把这个结果作为表进行加工查询
select e2.*
from (select e.*,rownum rn
from (select *
from emp
order by sal asc) e) e2
---where e2.rn<=3 and e2.rn>=1;-----sql v1
where e2.rn between 1 and 3;
---------这真的是生产环境需要的分页吗？
select e2.*
from (select e.*,rownum rn
from (select *
from emp
order by sal asc) e where rownum<=3) e2 ----------------如果表中有100000条数据，小于等于3000
where e2.rn>=1; ------sql v2 效率高，越早限制效率越高
------提高sql执行效率，慎用不等号：<,>,<>,它们会造成全表扫描，效率低下
------提高sql执行效率，查询时越早限制效率越高
1.按照每页显示5条记录，分别查询工资（**最高**）的第1页，第2页，第3页信息，要求显示员工姓名、入职日期、部门名称、工资。
select e2.*
from (select e.*,d.dname,rownum rn
from (select ename,hiredate,sal,deptno
from emp
order by sal desc) e join dept d on e.deptno=d.deptno where rownum<=3) e2 ----------------如果表中有100000条数据，小于等于3000
where e2.rn>=1;
select t.*,rowid from EMP t where rowid='AAARbXAAEAAAAI0AAA'
select * from emp where empno=7369;
-----家里的书柜 购书中心的书柜
create table emp8 as select * from emp;
select * from emp8;
insert into emp8 select * from emp8;
select * from emp8 where ename='SCOTT';----1.405s 索引创建之前
---- 为ename创建索引
create index ids_emp_ename on emp8(ename);
select * from emp8 where ename='SCOTT';----1.357s
----为sal创建索引
select * from emp8 where sal=5000;---2.761s 索引创建之前
create index ids_emp_sal on emp8(sal);
select * from emp8 where sal=5000;---2.636s
索引的创建方式：
1.oracle自动创建索引，对于主键和唯一字段，oralce会自动创建索引
create table test22(tname varchar2(3) primary key,taddress varchar2(30) unique);
2.手动创建索引
create index ids_emp_sal on emp8(sal);
什么情况需要创建索引呢？
如
select level,lpad(ename,length(ename)+(level*4)-2,'-') as org_chart
from emp
start with empno=7839---ename ='KING'-----顶级节点，从哪开始
connect by prior empno = mgr----指定父亲节点与儿子节点之间的匹配关系，自上往下查，empno(父亲行的信息)=mgr（儿子行的信息）
order by ename;
select level,lpad(ename,length(ename)+(level*4)-2,'-') as org_chart
from emp
start with ename='SCOTT'---ename ='KING'-----顶级节点，从哪开始
connect by prior empno = mgr;----指定父亲节点与儿子节点之间的匹配关系，自上往下查，empno(父亲行的信息)=mgr（儿子行的信息）
select level,lpad(ename,length(ename)+(level*4)-2,'-') as org_chart
from emp
start with ename='SCOTT'---ename ='KING'-----顶级节点，从哪开始
connect by prior mgr=empno;----指定父亲节点与儿子节点之间的匹配关系，自下往上查，mgr(儿子行的信息)=empno（父亲行的信息）
create table emp as select * from emp2 where 1=0;
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450, null, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, null, 10);
--1.查询名字当中有“A%B”这样的员工
select * from emp
where ename like '%A@%B%' escape '@';
--2.查询公司编号最小的员工姓名，工资和入职时间
select ename,sal,hiredate
from emp
where empno in (select min(empno) from emp );
--3.查询和20号部门的员工入职 年份和工作相同的员工姓名，工资和工作
select ename,sal,job
from emp
where (hiredate,job) in (select hiredate,job from emp where deptno=20);
--4.查询emp表，每页显示3条数据，写出显示第3页数据的sql语句
select e1.*
from (select e.*,rownum rn from emp e where rownum<=9) e1
where rn>=7;
--5.查询员工姓名，部门名称，工资和工资等级
select emp.ename,dept.dname, emp.sal,salgrade.grade
from emp,dept,salgrade
where emp.deptno=dept.deptno and (emp.sal between salgrade.losal and hisal)
--6.查询工资比自己经理工资高的 员工姓名和工资，以及对应经理的姓名和工资
select worker.ename, worker.sal, manager.ename, manager.sal
from emp worker,emp manager
where worker.mgr=manager.empno and worker.sal>manager.sal;
--7.创建序列stuseq，要求初始值为20170001，增量1，最大值为20179999
create sequence stuseq
minvalue 20170001
maxvalue 20179999
start with 20170001
increment by 1;
--8.查询部门编号为10的员工姓名，部门编号，工资，还有sal小于2000的员工姓名，部门编号，工资（使用集合运算）
select e6.ename,e6.deptno,e6.sal
from emp e6
where deptno=10
union
select e6.ename,e6.deptno,e6.sal
from emp e6
where sal<2000;
--9.人员情况表（employee）中字段包括，
员工号（ID），姓名（name），年龄（age），文化程 度（wh）：包括四种情况（本科以上，大专，高中，初中以下）,
现在我要根据年龄字段查询统计出：表中文化程度为本科以上，大专，高中，初中以下，各有多少人，占总人数多 少。结果如下：
学历 年龄 人数 百分比
本科以上 20 34 14
大专 20 33 13
高中 20 33 13
初中以下 20 100 40
本科以上 21 50 20
请写出对应的sql语句。（需要写出相应的建表语句，插入模拟数据语句等）
create table employee(
ID number(10) unique not null,
name varchar2(10) not null,
age number(3),
wh varchar2(20) check (wh in('本科以上','大专','高中','初中以下'))
)
insert into employee (ID,name,age,wh) values (2018020001,'hqxu',27,'本科以上');
10.如果希望进行数据库性能优化，你有什么可行的办法？
浙公网安备 33010602011771号