Oracle学习时关于SQL语言的总结

Oracle学习时关于SQL语言的总结

 

1、查询dept表的结构     www.2cto.com  

 

在命令窗口输入：desc dept;  

 

2、检索dept表中的所有列信息   

 

 select * from dept  

 

3、检索emp表中的员工姓名、月收入及部门编号    

 

select ename "员工姓名",sal "月收入",empno "部门编号" from emp   

 

4、检索emp表中员工姓名、及雇佣时间     日期数据的默认显示格式为“DD-MM-YY",如果希望使用其他显示格式(YYYY-MM-DD),那么必须使用TO_CHAR函数进行转换。    

 

select ename "员工姓名", hiredate "雇用时间1",to_char(hiredate,&＃39;YYYY-MM-DD&＃39;) "雇用时间2" from emp    

 

注意：第一个时间是日期类型的，在Oracle的查询界面它的旁边带有一个日历。第二个时间是字符型的。不要将YYYY-MM-DD使用双引号  

 

5、使用distinct去掉重复行。    

 

检索emp表中的部门编号及工种，并去掉重复行。                                                                                                                   

 

select  distinct deptno "部门编号",job "工种"  from emp order by deptno     

 

6、使用表达式来显示列     检索emp表中的员工姓名及全年的月收入    

 

select ename "员工姓名", (sal+nvl(comm,0))*12 "全年收入" from emp     注意：防止提成comm为空的操作，使用nvl函数  

 

7、使用列别名     用姓名显示员工姓名，用年收入显示全年月收入。    

 

select ename "员工姓名",sal*12 "全年收入" from emp  

 

8、连接字符串  　

 

在oracle中连接字符串用“||”操作符来完成的，当连接字符串时，如果在字符串要加入数字值     那么在“||”后可以直接指定数字，如果在字符串加入字符和日期值，则必须要用单引号。    

 

检索emp表，用is a 这个字符串来连接员工姓名和工种两个字段     

 

select ename||&＃39; is a &＃39;||job "他们各自的职位"from emp     注意：用的是单引号！！！  

 

9、使用WHERE子句    

 

检索月收入大于2000的员工姓名及月收入。    

 

select ename "姓名" ,sal "月薪"from emp where sal>2000    

 

检索月收入在1000元到2000元的员工姓名、月收入及雇佣时间。    

 

select ename "姓名" ,sal "月薪",hiredate "雇佣时间" from emp where sal between 1000 and 2000  

 

10、like的用法：     

 

检索以S开头的员工姓名及月收入。     

 

select ename "员工姓名",sal "月收入" from emp where ename like &＃39;S%&＃39;     

 

检索员工姓名中的第三个字符是A的员工姓名及月收入。     

 

select ename "员工姓名",sal "月收入" from emp where ename like &＃39;__A%&＃39;

 

11、在WHERE条件中使用IN操作符     

 

检索emp表中月收入是800的或是1250的员工姓名及部门编号     

 

select ename "姓名",deptno "部门编号",sal "工资" from emp where sal in(800,1250)      注意:IN的意思是或者。是800或者1250而不是表示范围  

 

12、在WHERE条件中使用逻辑操作符(AND、OR、NOT)     

 

显示在部门20中岗位CLERK的所有雇员信息     

 

select * from emp where deptno=&＃39;20&＃39; and job=&＃39;CLERK&＃39;     

 

显示工资高于2500或岗位为MANAGER的所有雇员信息     

 

select * from emp where sal>&＃39;2500&＃39; or job=&＃39;MANAGER&＃39;      注意：在where里面的条件都是使用的单引号  

 

13、查询表中是空值的数据     

 

检索emp表中有提成的员工姓名、月收入及提成。     

 

select ename "姓名",comm "提成",sal "工资" from emp where comm is not null  

 

14、使用ORDER BY子句，进行排序。     

 

检索emp表中部门编号是30的员工姓名、月收入及提成，并要求其结果按月收入升序、然后按提成降序显示。     

 

select ename "姓名",comm "提成",sal "工资" from emp where deptno=&＃39;30&＃39; order by sal asc,comm desc  

 

15、查询工资大于1200的员工姓名和工资    

 

select ename "姓名",sal "工资" from emp where sal>1200  

 

16、查询员工号为7934的员工的姓名和部门号    

 

select ename "姓名",deptno "部门编号" from emp where empno=&＃39;7934&＃39;  

 

17、选择工资不在5000到12000的员工的姓名和工资    

 

select ename "姓名",sal "工资" from emp where sal not between 5000 and 12000     注意:不是 is not

 

18、选择雇用时间在1981-02-01到1981-05-01之间的员工姓名，职位(job)和雇用时间,按从早到晚排序.    

 

select ename "姓名",job "职位",hiredate "雇佣时间"    from emp where hiredate between to_date(&＃39;1981-02-01&＃39;,&＃39;YYYY-MM-DD&＃39;) and to_date(&＃39;1981-05-01&＃39;,&＃39;YYYY-MM-DD&＃39;)     order by hiredate asc    

 

19、选择在20或10号部门工作的员工姓名和部门号     

 

select ename "姓名",deptno "部门号" from emp where deptno in (20,10)   

 

20、选择在1987年雇用的员工的姓名和雇用时间    

 

select ename "姓名",hiredate "雇佣时间" from emp where to_char(hiredate,&＃39;YYYY&＃39;)=&＃39;1987&＃39;    

 

取出雇用时间的年份且转换为字符形式；然后与&＃39;1987&＃39;比较    

 

select ename "姓名",hiredate "雇佣时间" from emp where to_char(hiredate,&＃39;MM&＃39;)=&＃39;04&＃39;    

 

select ename "姓名",hiredate "雇佣时间" from emp where to_char(hiredate,&＃39;MM&＃39;)=&＃39;4&＃39;     前者是可以的，后者不可以 

 

21、选择公司中没有管理者的员工姓名及job    

 

select ename "姓名",job "工作" from emp where mgr is null  

 

22、选择公司中有奖金 (COMM不为空,且不为0) 的员工姓名，工资和奖金比例,按工资逆排序,奖金比例逆排序.        

 

select ename "姓名",sal "工资",comm "奖金比例"from emp where comm is not null and comm !=0 order by sal desc,comm desc  

 

23、选择员工姓名的第三个字母是a的员工姓名    

 

select ename "姓名"from emp where ename=&＃39;__A&＃39;    

 

select ename "姓名"from emp where ename like &＃39;__A%&＃39;    

 

总结：1，前者是错的，这样定死了：一共只有三个字母，且最后一个是A     2，后者正确。表示A后的不限定    

 

24、显示系统时间(取别名为“DATE”).    

 

select sysdate "Date" from dual     注意：dual是Oracle系统的一个虚拟表    

 

25、查询员工号，姓名，工资(若为NULL则作为0处理)，补助(若为NULL则作为0处理)     以及工资提高百分之20%后四舍五入到整数的结果（取别名为new salary）    

 

select ename "姓名",empno "员工号",nvl(sal,0) "原来的sal",round(sal*(1+0.2),2) "new salary",nvl(comm,0) "补助"from emp    

 

总结：这里用了两个很重要的函数：nvl和round()用于四舍五入——比如将原工资除以7，只保留三位小数    

 

select round(sal/7,3) "new sal" from emp  

 

26、将员工的姓名（取别名为"Name"）按字母表先后顺序排序，并写出姓名的长度（取别名为"length"）    

 

select ename "Name",length(ename) "length" from emp order by ename asc    

 

select ename "名字",length(ename) "名字的长度" from emp order by ename asc  

 

27、查询各员工的姓名，并显示出各员工在公司工作了多少个月份（起别名为"worked_month"）四舍五入到整数.    

 

select ename "名字",round(months_between(sysdate,hiredate),0) "worked_month" from emp    

 

计算指定日期到雇佣日期有多少个月？    

 

select ename "名字",round(months_between(to_date(&＃39;1990-01-01&＃39;,&＃39;YYYY-MM-DD&＃39;),hiredate),0) "worked_month" from emp    

 

计算两个日期之间有多少个月？    

 

select ename "名字",round(months_between(to_date(&＃39;1990-01-01&＃39;,&＃39;YYYY-MM-DD&＃39;),to_date(&＃39;1980-01-01&＃39;,&＃39;YYYY-MM-DD&＃39;)),0) "worked_month" from emp    

 

注意:months_between()这个函数里的两个参数是日期类型，所以需要转型！  

 

28、查询员工的姓名和工资，按下面的形式显示结果(工资字段必须为15位,空位用$填充)    

 

姓名     工资    

 

KING   $$$$$$$$$$24000    

 

MIKE   $$$$$$$$$$$4800     

 

select ename "名字",lpad(sal,15,&＃39;$&＃39;) "工资" from emp     注意lpad的最后一个参数，因为它是字符型的，所以用单引号  

 

29、查询员工的姓名，以及在公司工作满了多少个月（worked_month）,并按月份数降序排列    

select ename "姓名",months_between(sysdate,hiredate) "worked_month" from emp order by  having worked_month desc(错误的)    

 

select ename "姓名",months_between(sysdate,hiredate) "worked_month" from emp order by "worked_month" desc(不太正确的) 注意order by后面的双引号    

 

select ename "姓名",trunc(months_between(sysdate,hiredate)) "worked_month" from emp order by "worked_month" desc    

 

trunc()函数表示只取整数！题目要求的是"满了多少个月"，所以应该取整    

 

30、查询公司员工工资的最大值，最小值，平均值，总和   

 

select max(sal) "最高工资",min(sal) "最低工资",round(avg(sal),2) "平均工资",sum(sal) "工资总和" from emp  

 

31、查询各job的员工工资的最大值，最小值，平均值，总和   

 

select job "工种",max(sal) "最高工资",min(sal) "最低工资",round(avg(sal),2) "平均工资",sum(sal) "工资总和" from emp group by job  

 

32、选择具有各个job 的员工人数   

 

select job "工种",count(*) "此工种的员工人数" from emp group by job  

 

33、查询员工最高工资和最低工资的差距（DIFFERENCE）   

 

select max(sal)-min(sal) "DIFFERENCE" from emp  

 

34、查询公司的人数，以及在80,81,82,87年，每年雇用的人数，结果类似下面的格式   

 

total 1980 1981 1982 1987   

 

14  1  10  1  2   

 

select count(*) "total",    sum(decode(to_char(hiredate,&＃39;YYYY&＃39;),1980,1,0)) "1980",    sum(decode(to_char(hiredate,&＃39;YYYY&＃39;),1981,1,0)) "1981",    sum(decode(to_char(hiredate,&＃39;YYYY&＃39;),1982,1,0)) "1982",    sum(decode(to_char(hiredate,&＃39;YYYY&＃39;),1987,1,0)) "1987"    from emp    

 

这个例子相当相当重要！ 其中select decode(to_char(hiredate,&＃39;YYYY&＃39;),1980,1,0) from emp这个语句是核心   如果年份是1980的将其置为1，否则为0，这样操作了所有的年份(相当于形成了一张临时表)   然后再利用sum计算其和(即计算这个临时表的总和就知道有多少个1980了)！！！     同理可以判断每个月份有多少人入职   select   sum(decode(to_char(hiredate,&＃39;MM&＃39;),02,1,0)) "2月入职",   sum(decode(to_char(hiredate,&＃39;MM&＃39;),03,1,0)) "3月入职",   sum(decode(to_char(hiredate,&＃39;MM&＃39;),04,1,0)) "4月入职",   sum(decode(to_char(hiredate,&＃39;MM&＃39;),05,1,0)) "5月入职"   from emp