scott登錄查詢常用語句

一、簡單查詢

1.簡單查詢
select * from emp;--查詢表emp中的所有數據
select empno as id,ename as name from emp;--查詢表emp中的empno顯示為id，ename顯示為name

2.去除重復
select distinct job from emp;--將表emp中的job去重
select distinct job,deptno from emp;--將表emp中的job.deptno去重

3.字符串的連接
select '員工編號是' ||empno || '姓名是' ||ename ||'工作是'||job from emp;--用||可以將字符之間進行連接

4.乘法
select ename,sal *12 from emp;--查詢表emp中的ename，薪資*12
--加減乘除都類似

二、限定查詢
1.獎金大于1500的
select * from emp where sal>1500;--用where限定sal大于1500
2.有獎金的
select *from emp where comm is not null;--顯示comm不為空值的
3.沒有獎金的
select *from emp where comm is null;--顯示comm為空值的
4.有獎金且大于1500的
select *from emp where sal>1500 and comm is not null;--且用and連接
5.工資大于1500或者有獎金的
select *from emp where sal>1500 or comm is not null;--或用or連接
6.工資不大于1500且沒獎金的
select *from emp where sal<=1500 and comm is null;
select *from emp where not (sal >1500 or comm is not null);
7.工資大于1500但是小于3000的
select *from emp where sal>1500 and sal<3000;
select *from emp where sal between 1500 and 3000; --between是閉區間，是包含1500和3000的
8.時間區間
select *from emp where hiredate between to_date('1981-01-01','yyyy-MM-dd') and to_date('1981-12-31','yyyy-MM-dd');
9.查詢雇員名字
select *from emp where ename='SMITH';
10.查詢員工編號
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); --not是排除這3個，其他的都可以查
11.模糊查詢
select *from emp where ename like '_M%'; --第2個字母為M的
select *from emp where ename like '%M%';--包含字母M的
select *from emp where ename like '%%'; --全查詢
12.不等號的用法
select * from emp where empno !=7369;
select *from emp where empno<> 7369;

三、對結果集排序
1.查詢工資從低到高
select *from emp order by sal asc;--按升序排序時asc可省略
select *from emp order by sal desc,hiredate desc; --當sal列相同時就按hiredate來排序
2.字符函數
select *from dual;--偽表
select 2*3 from dual;
select sysdate from dual;
3.變成大寫
select upper('smith') from dual;
4.變成小寫
select lower('SMITH') from dual;
5.首字母大寫
select initcap('smith') from dual;
6.連接字符串
select concat('jr','smith') from dual; --只能在oracle中使用
select 'jr' ||'smith' from dual; --推薦使用
7.截取字符串
select substr('hello',1,3) from dual; --索引從1開始
8.獲取字符串長度
select length('hello') from dual;
9.字符串替換
select replace('hello','l','x') from dual; --把l替換為x

四、數值函數

1.取整
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位小數
2.取余
select mod(10,3) from dual;--10/3取余 =1
4.日期函數
--日期-數字=日期 日期+數字=日期 日期-日期=數字
5.查詢員工進入公司的周數
select ename,round((sysdate -hiredate)/7) weeks from emp;
6.查詢所有員工進入公司的月數
select ename,round(months_between(sysdate,hiredate)) months from emp;
7.求三個月后的日期
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;
8.轉換函數
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; --數字相加
9.查詢員工年薪
select ename,(sal*12+nvl(comm,0)) yearsal from emp; --空和任何數計算都是空
10.Decode函數，類似if else if (常用）
select decode(1,1,'one',2,'two','no name') from dual;

五、多表查詢
1.直接查詢
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;

2.查詢出雇員的編號，姓名，部門編號，和名稱，地址
select e.empno,e.ename,e.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;

3.查詢出每個員工的上級領導
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(+) ;
4.外連接

select *from emp order by deptno;
--查詢出每個部門的員工。部門表是全量表，員工表示非全量表，在做連接條件時，全量表在非全量表的哪端，那么連接時全量表的連接條件就在等號哪端
5.左連接
select * from dept d,emp e where d.deptno=e.deptno(+) order by e.deptno;
6.右連接
select * from emp e，dept d where e.deptno(+)=d.deptno order by e.deptno;

?

insert into table1(least1,least2) valuse (a,b)--插入a,b到表1的列1列2；
updata table1 set least1=a where least2=b--將表1中的b修改為a；
delete from table1 where least1=a--刪除表1中的數據a；
savepoint a--設置節點a；
rollback to a--回到節點a；

?

alter table dept rename column name to dname;--修改表dept中的列名name為dname；
select * from emp for update;--手動修改表格；
drop table dept;--刪除表dept；
rename emp to emp1;--將emp名字修改為emp1；
comment on table emp is 'enployee information';--為表emp添加注釋“enployee information”；
select * from user_tab_comments where table_name = 'STATUS2';--查看表STATUS2的注釋（這里的表明必須大寫）；

create table employees
(ID INTEGER not null,
constraint employees_id_pk primary key (id));
--創建表，列id，主鍵約束

drop table employees ;--刪除表

alter table order_status2
add constraint order_status2_id_fk
foreign key (id)
references employees(id);--增加外鍵約束

alter table order_status2
drop constraint ORDER_STATUS2_ID_FK ;--刪除約束

create table emp1
as select * from emp ;--創建表emp1使用emp的全部內容

alter table order_status2
add constraint order_status2_status_ck
check ( status in ('Male','Female'));--創建check約束

?

練習題

--1.得到平均工資大于2000的工作職種
select job,avg(sal)
from emp
group by job
having avg(sal) > 2000
--2.分部門得到工資大于2000的所有員工的平均工資，
--并且平均工資還要大于2500
select deptno,avg(sal)
from emp
where sal > 2000
group by deptno
having avg ( sal ) > 2500
--3.得到每個月工資總數最少的那個部門的部門編號，部門名稱，部門位置
select dept.deptno,dept.dname,dept.loc,dept1.sal1
from (select * from
(select deptno,sum(sal) sal1
from emp
group by deptno
order by sum(sal) )
where rownum=1) dept1, dept
where dept1.deptno=dept.deptno ;
--4.分部門得到平均工資等級為2級（等級表）的部門編號
select *
from ( select *
from (select deptno , avg(sal) sal1
from emp
group by deptno ) depno_avgsal , salgrade
where depno_avgsal.sal1
between salgrade.losal and salgrade.hisal )
where grade = 2 ;
--5.查找出部門10和部門20中，
--工資最高第3名到工資第5名的
--員工的員工名字，部門名字，部門位置
select e.ename,d.dname,d.loc,rno
from(select ename,deptno,rno
from(select ename,deptno,rownum rno
from(select *
from emp
where deptno in (10,20)
order by sal desc))
where rno>=3 and rno<=5) e,dept d
where e.deptno=d.deptno;
--6.查找出收入（工資加上獎金），
--下級比自己上級還高的員工編號，員工名字，員工收入
select e.empno,e.ename,e.sal1
from ( select empno,ename,mgr,sal+nvl(comm,0) sal1
from emp ) e ,
( select empno,ename,sal+nvl(comm,0) sal2
from emp ) b
where e.mgr = b.empno and e.sal1 > b.sal2 ;
--7.查找出職位和'MARTIN' 或者'SMITH'一樣的員工的平均工資
select avg(sal)
from emp
where job in (select job
from emp
where ename = 'MARTIN' or ename = 'SMITH') ;
--8.查找出不屬于任何部門的員工
select *
from emp
where deptno is null ;
--9.按部門統計員工數，
--查處員工數最多的部門的第二名到第五名
select *
from (select b.* , rownum rno
from (select *
from emp
where deptno in ( select deptno
from ( select deptno , count ( empno ) dnum
from emp
group by deptno
order by dnum desc ) a
where rownum =1 )) b
where rownum < = 5 ) c
where c.rno > = 3 ;
--10.查詢出king所在部門的部門號\部門名稱\部門人數
select emp.deptno,dname,count(emp.ename)
from emp inner join dept on emp.deptno=dept.deptno
group by emp.deptno,dname
having emp.deptno=(select deptno
from emp
where ename='KING');
select a.deptno , b.dname , a.donum
from (select deptno,count(deptno) donum
from emp
where deptno = ( select deptno
from emp
where ename = 'KING' )
group by deptno ) a ,dept b
where a.deptno = b.deptno ;

--11.查詢出king所在部門的工作年限最大的員工名字
create view king_dept as
select * from emp
where deptno in(select deptno from emp where ename='KING');
select ename from king_dept
where months_between(sysdate,hiredate)=(select max(months_between(sysdate,hiredate)) from king_dept);

select ename
from ( select *
from emp
where deptno = ( select deptno
from emp
where ename = 'KING' )
order by hiredate )
where rownum = 1 ;
--12.查詢出工資成本最高的部門的部門號和部門名稱
select deptno,dname
from dept
where deptno in ( select deptno
from emp
group by deptno
having sum(sal) in (select max(sum(sal))
from emp
group by deptno));
--13.顯示所有員工的姓名、工作和薪金,
--按工作的降序排序,若工作相同則按薪金排序
select ename , job , sal
from emp
order by job desc , sal ;
--14.顯示所有員工的姓名、加入公司的年份和月份,
--按受雇日期所在月排序,若月份相同則將最早年份的員工排在最前面
select ename 姓名 , to_char(hiredate,'yyyy') 年份 , to_char(hiredate,'mm') 月份
from emp
order by 月份 , 年份 ;
--15.顯示在一個月為30天的情況所有員工的日薪金,忽略余數
select ename , round((sal+nvl(comm,0))/30) 日薪
from emp ;
--16.找出在(任何年份的)2月受聘的所有員工
select ename
from emp
where to_char(hiredate,'mm') = 2 ;
--17.對于每個員工,顯示其加入公司的天數
select ename , round ( sysdate-hiredate)
from emp ;
--18.顯示姓名字段的任何位置包含"A"的所有員工的姓名
select ename
from emp
where ename like'%A%' ;
--19.以年月日的方式顯示所有員工的服務年限
select ename,trunc((sysdate-hiredate)/365) year ,
trunc(mod(sysdate-hiredate,365)/30) mon ,
trunc(mod(mod(sysdate-hiredate,365),30)) day
from emp ;
--20.顯示員工的姓名和受雇日期,根據其服務年限,將最老的員工排在最前面
select ename, hiredate
from emp
order by hiredate ;