oracle——笔记——简单查询语句

            create
            table emp2 ( empno number(4),
                    ename varchar2(10),
                    job varchar2(9),
                    mgr number(4),
                    hiredate date,
                    sal number(7,2),
                    comm number(7,2),
                    deptno number(2) 
                 );
-----------------------------------------------------------------------------------------------------------insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, ‘SMITH‘, ‘CLERK‘, 7902, to_date(‘17-12-1980‘, ‘dd-mm-yyyy‘), 800.00, null, 20);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, ‘ALLEN‘, ‘SALESMAN‘, 7698, to_date(‘20-02-1981‘, ‘dd-mm-yyyy‘), 1600.00, 300.00, 30);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, ‘WARD‘, ‘SALESMAN‘, 7698, to_date(‘22-02-1981‘, ‘dd-mm-yyyy‘), 1250.00, 500.00, 30);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, ‘JONES‘, ‘MANAGER‘, 7839, to_date(‘02-04-1981‘, ‘dd-mm-yyyy‘), 2975.00, null, 20);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, ‘MARTIN‘, ‘SALESMAN‘, 7698, to_date(‘28-09-1981‘, ‘dd-mm-yyyy‘), 1250.00, 1400.00, 30);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, ‘BLAKE‘, ‘MANAGER‘, 7839, to_date(‘01-05-1981‘, ‘dd-mm-yyyy‘), 2850.00, null, 30);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, ‘CLARK‘, ‘MANAGER‘, 7839, to_date(‘09-06-1981‘, ‘dd-mm-yyyy‘), 2450.00, null, 10);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, ‘SCOTT‘, ‘ANALYST‘, 7566, to_date(‘19-04-1987‘, ‘dd-mm-yyyy‘), 3000.00, null, 20);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, ‘KING‘, ‘PRESIDENT‘, null, to_date(‘17-11-1981‘, ‘dd-mm-yyyy‘), 5000.00, null, 10);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, ‘TURNER‘, ‘SALESMAN‘, 7698, to_date(‘08-09-1981‘, ‘dd-mm-yyyy‘), 1500.00, 0.00, 30);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, ‘ADAMS‘, ‘CLERK‘, 7788, to_date(‘23-05-1987‘, ‘dd-mm-yyyy‘), 1100.00, null, 20);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, ‘JAMES‘, ‘CLERK‘, 7698, to_date(‘03-12-1981‘, ‘dd-mm-yyyy‘), 950.00, null, 30);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, ‘FORD‘, ‘ANALYST‘, 7566, to_date(‘03-12-1981‘, ‘dd-mm-yyyy‘), 3000.00, null, 20);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, ‘MILLER‘, ‘CLERK‘, 7782, to_date(‘23-01-1982‘, ‘dd-mm-yyyy‘), 1300.00, null, 10);

=============================================================================================================================createtable dept2 ( deptno number(2),
                     dname varchar2(14),
                     loc varchar2(13)
                   );

----------------------------------------------------insertinto dept2 (DEPTNO, DNAME, LOC)
values (10, ‘ACCOUNTING‘, ‘NEW YORK‘);

insertinto dept2 (DEPTNO, DNAME, LOC)
values (20, ‘RESEARCH‘, ‘DALLAS‘);

insertinto dept2 (DEPTNO, DNAME, LOC)
values (30, ‘SALES‘, ‘CHICAGO‘);

insertinto dept2 (DEPTNO, DNAME, LOC)
values (40, ‘OPERATIONS‘, ‘BOSTON‘);

============================================================createtable salgrade2 ( grade number,
                         losal number,
                         hisal number
                       );

--------------------------------------------------insertinto salgrade2 (GRADE, LOSAL, HISAL)
values (1, 700, 1200);

insertinto salgrade2 (GRADE, LOSAL, HISAL)
values (2, 1201, 1400);

insertinto salgrade2 (GRADE, LOSAL, HISAL)
values (3, 1401, 2000);

insertinto salgrade2 (GRADE, LOSAL, HISAL)
values (4, 2001, 3000);

insertinto salgrade2 (GRADE, LOSAL, HISAL)
values (5, 3001, 9999);


========================================================select*from emp2;

select*from dept2;

select*from salgrade2;

=====================================select*from emp2;        --employee2 员工表select*from dept2;       --department2 部门表select*from salgrade2;   -- salary grade2 工资等级表-----------------------------------------------------emp2

empno    员工编号
ename    员工姓名
job      工作/工种
mgr      manager上级编号
hiredate 入职日期
sal      salary 工资
comm     奖金/津贴
deptno   部门编号
---------------------
dept2

deptno  部门号
dname   部门名称
loc     所在地
---------------------------salgrade2

grade                   等级
losal lowest salary     最低工资
hisal high salary       最高工资

=============================================
数据查询语句:

selectfromwhere



查询emp2表中，所有员工的姓名、工资、部门号:

select ename,sal,deptno from emp2;
 



查询emp2表中，工资大于1500 的员工信息:

select* from emp2 where sal >1500;



 
查询emp2表中，上级是7698 的员工姓名和津贴:

select ename,comm from emp2 where mgr =7698;




列的别名:
select ename,sal,deptno from emp2;

select ename 姓名,sal 工资,deptno 部门号 from emp2;

select ename as 姓名,sal as 工资,deptno as 部门号 from emp2;

select ename as e,sal as s,deptno as d from emp2;

select ename as "Esc",sal as "alS",deptno as "dDD" from emp2;

select ename as "e%",sal as "s#",deptno as "d()" from emp2;




==============================================================================================================


算术运算 +-*/  （）

支持数值型和日期型(只能加减)数据:




查询每个员工的年薪:

select ename,sal,sal*12 from emp2;



 
给20部门员工，涨薪300之后，工资是多少？:
 
select ename,sal,sal+300 from emp2 where deptno =20;



 
查询每名员工的 工资奖金和 :
 
select sal,comm,sal+comm from emp2;




运算中如果有空值，那么最后结果为空

--空值替换 nvl()select sal,comm,nvl(comm,0),sal+nvl(comm,0) from emp2;

select*from emp2;
  
insertinto emp2(empno) values(1122);

deletefrom emp2 where empno =1122;

select ename,nvl(ename,‘未知‘) from emp2;





连接符 :

select ename,sal from emp2;

select ename||‘的工资是‘||sal||‘，部门是‘||deptno from emp2;
  


 
去重


查询emp中，有多少个部门:

selectdistinct deptno from emp2; 




查询emp中，有多少种工作

selectdistinct job from emp2;
  





where
比较运算符

一般的比较运算符 >>=<=<=!=<>

特殊的比较运算符

   between……and……
   
   in(……)
   
   like ……  像  模糊查询
   
   isnull    空值 
   


select*from emp2 where comm isnull;



select*from emp2 where comm isnotnull;
   
   




     
查询工资在1500 到 3000 的员工信息:

select* from emp2  where sal between1500and3000;  --闭区间                                      下限     上限 

select* from emp2 where sal >=1500and sal <=3000;
   





查询从事CLERK或SALESMAN 的员工信息:

1）字符串
2）关于大小写
    
select*from emp2 where job in(‘CLERK‘,‘SALESMAN‘);
   
select*from emp2 where job =‘CLERK‘or job =‘SALESMAN‘ ;
   
select*from emp2 where sal in(1500,3000);

select*from emp2 where sal =1500or sal =3000;
  




likeselect*from emp2 where ename like‘S%‘;




%
sql里的% 等同于linux的* ，代表零个或多个任意字符
 _ 代表一个任意字符

select*from emp2 where ename like‘S_ITH‘;
  
select t.*,t.rowid from emp2 t;
  
select emp2.*,rowid from emp2;
  


查询以S%开头的员工信息:

select*from emp2 where ename like‘S%%‘escape‘‘;       escape‘‘:定义这个符号后面符号位普通字符串，不是系统里默认的符号。即转义

select*from emp2 where ename like‘S|%%‘escape‘|‘;
  
select*from emp2  where ename like‘S%%‘escape‘‘;

escape：

用来转译的，比如数据库中有个表 test字段name 中存了字符‘%‘，那么我查询的时候如果想查询第二位是 ‘%‘的记录就要用到模糊查询，


但是‘%‘是模糊查询的通配符，我不能如下查询：

select*from test where name like‘_%%‘;



这样系统不会把第二个【%】当做字符，只能转译，转译默认的符号是 【】

select*from test where name like‘_%%‘;



但是可以自定义转义符，这时候就用 escape，比如定义【*】为转义符

select*from test where name like‘_*%%‘escape‘*‘;


-----------------------------------------------------------------------------------------------------



逻辑运算符 andornot ()

运算优先级：not>and>or ,()优先级最高



查询，20 部门中，从事CLERK 工作的员工:
  
select*from emp2 where deptno =20and job =‘CLERK‘;



查询，30 部门中，奖金为空的员工信息:
  
select*from emp2  where deptno =30and comm isnull;


  
查询，除10 部门之外，工资大于1500 的员工信息:
  
select*from emp2 where deptno !=10and sal >1500;


  
查询，工作是SALESMAN ，或工资不小于3000 的员工:
  
select*from emp2 where job =‘SALESMAN‘or sal >=3000;

  
查询，工作不是SALESMAN ，也不是CLERK 的员工:
  
select*from emp2 where job !=‘SALESMAN‘and job !=‘CLERK‘;
  
select*from emp2 where job in(‘SALESMAN‘,‘CLERK‘);
  
select*from emp2 where job notin(‘SALESMAN‘,‘CLERK‘);


  
查询，工作是SALESMAN，或，工作是PRESIDENT并且工资大于1500的员工信息:
  
select*from emp2 where job =‘SALESMAN‘or job =‘PRESIDENT‘and sal >1500;



查询，工作是SALESMAN或PRESIDNET，并且工资大于1500 的员工信息:

select*from emp2 where (job =‘SALESMAN‘or job =‘PRESIDENT‘) and sal >1500;

select*from emp2 where job in(‘SALESMAN‘,‘PRESIDENT‘) and sal >1500;
  





orderby 排序 

selectfromwhereorderby 列名|别名|算术表达式|函数
    
orderby 列1,别名,函数

orderby 列1,列2 desc;

order by的位置：在整个查询语句的最后多次排序 


select*from emp2 orderby sal;  --升序select*from emp2 orderby sal asc;  --升序select*from emp2 orderby sal desc; --降序select ename 姓名,job 工作 from emp2 where deptno =20orderby 姓名 desc;
 


查询员工信息，结果按照工资奖金和 升序排序:

select emp2.*,sal+nvl(comm,0) from emp2 orderby sal+nvl(comm,0);
 


查询员工信息，结果按照部门号排序;如果部门号相同，按照工资降序排序

select*from emp2 orderby deptno asc,sal desc;






函数
 
单组函数          一个值对应一个结果 

分组函数/聚合函数 多个值对应一个结果  avg() sum()

单组函数
  --字符函数UPPER()   LOWER()   INITCAP()  ---字母大小写  大写       小写    首字母大写
  
  replace()      substr()    concat()  nvl() nvl2()
    替换           截取       连接
  

select ename,lower(ename),initcap(ename) from emp2;

select t.*,t.rowid from emp2 t;

select*from emp2 wherelower(job) =‘clerk‘;
  
select ename,replace(ename,‘S‘,‘s‘) from emp2;

select ename,substr(ename,2,3) from emp2;
                       起始位,长度
  


查询工作名称以SALES 开头的员工信息:

select*from emp2 where job like‘SALES%‘;

select*from emp2 where substr(job,1,5) =‘SALES‘;
  
select ename||‘的工资是‘||sal||deptno from emp2;


select concat(ename,sal) from emp2;

select concat(concat(ename,‘的工资是‘),sal) from emp2;






函数是可以嵌套的

select substr(concat(ename,‘的工资是‘),5,5) from emp2;
  
select comm,nvl(comm,0),nvl2(comm,1000,0) from emp2;
                                 非空替换,空值替换  
                                 
       



                          
数值函数

   round(x[,y])  取整或保留指定小数位，规则：四舍五入
   trunc(x[,y])  取整或保留指定小数位，规则：截断
   mod(x,y) 取模/取余
   
   round(5.72) =6round(5.718,2) =5.72round(04.718,-1) =0
   
   trunc(5.72) =5
   trunc(5.718,2) =5.71
   trunc(05.718,-1) =0selectround(5.72), round(5.718,2),round(5.718,-1) from dual;
   select trunc(5.718,2),mod(8,4),mod(10,3) from dual;
 
  
dual表
   
作用：语句补全
   
   select*from dual;
   
   select sysdate from dual;
   
   select12*15,round(1000/23,2) from dual;
   
   select Sys_Context(‘userenv‘,‘db_name‘) from dual;--查看当前数据库 名select Dbms_Random.random from dual;--获得一个随机数select Dbms_Random.value(10,20) from dual;--获得一个随机数






日期函数

使用insert，新增一条记录：
员工编号：1122
员工工作：SALESMAN
入职日期：2018年7月1号

   insertinto 表名(列名) values(列对应的值);
   
   insertinto emp(empno,job,hiredate) 
   values(1122,‘SALESMAN‘,‘01-7月-18‘);
   
   insertinto emp(empno,job,hiredate) 
   values(1122,‘SALESMAN‘,‘2018-7-1‘);   
   
   select*from emp;
   
   --1)默认日期格式select*from nls_session_parameters;
    alter session set NLS_DATE_FORMAT=‘YYYY-MM-DD‘;
    alter session set NLS_DATE_FORMAT=‘DD-MON-RR‘;
   
   --2) 日期函数 to_date(日期，格式)insertinto emp(empno,job,hiredate) 
   values(1123,‘SALESMAN‘,to_date(‘10-1-2018‘,‘MM-DD-YYYY‘)); 
   
    --查询入职日期早于1981年9 月30 号的员工信息selectfromwhere hiredate < to_date()