sql练习

1.oracle数据库启动的服务(OracleOraDb10g_home1TNSListener,OracleServiceORCL)

2.用客户端sqldeveloper.exe连接指定数据库的方式

cmd中输入sqlplus进行本机数据库的方式

3.数据库中表和字段的概念

4.sql语句的分类

a)DDL，数据定义语言createtabledroptable等

b)*DML，数据操纵语言insertupdatedelete

c)*DQL，数据查询语言select

 d) DCL，数据控制语言 dba(数据库管理员)

5.ddl语句：建或者 删除表,索引等数据库对象

createtabletablename(column1type,column2type,column3type....)

droptabletablename;

6.DML，数据操纵语言增删改(insert,delete,update)

insertintotablename(column1,column2....)values()

deletefromtablenamewhere条件

updatetablenamesetcolumn1=value1,column2=value2where条件

7.DQL数据查询语句

select要查询的字段列表fromtablename

要查询的字段列表：*代表查询该表中所有字段或者是要查询的字段的列表格式为(column1,column2,column3....)

1).查询数据库当前的时间oracle的是sysdate

oracle中的虚表dual:查询与具体表没有关系的字段比如数据库当前时间或者是一些表达式时使用

selectsydatefromdual;

2).查询出所有员工的编号empno，姓名ename，职位job，月薪sal，年薪(不包含奖金)，入职日期hiredate并且给字段起别名

selectempnobianhao,enamexingming,jobzhiwei,salyuexin,hiredaterzsj,sal*12nianxinfromemp;

3).查询出所有员工的编号，姓名，职位，月薪，年薪(包含奖金)，入职日期并且给字段起别名

selectempnobianhao,enamexingming,jobzhiwei,salyuexin,hiredaterzsj,(sal+comm)*12nianxinfromemp;

含有任何null值的数学表达式最后的结果都为空值

数据中含有空值时往往会增加程序的处理难度,强烈建议在实际当中开发数据库应用系统的时候不要使用空值

4).查询出月薪大于1500的人的编号，姓名，职位，月薪，年薪(不包含奖金)，入职日期

selectempnobianhao,enamexingming,jobzhiwei,salyuexin,hiredaterzsj,sal*12nianxinfromempwheresal>1500;

5).查询出奖金为空的人的姓名，职位，月薪，奖金，年薪，入职日期

selectempnobianhao,enamexingming,jobzhiwei,salyuexin,hiredaterzsj,sal*12nianxin,commjiangjinfromempwherecommisnull;

6).查询出工资大于等于1500小于等于3000的人的姓名，职位，月薪，奖金，年薪(包含奖金)，入职日期(两种语句写出)

selectempnobianhao,enamexingming,jobzhiwei,salyuexin,hiredaterzsj,(sal+comm)*12nianxinfromempwheresal>=1500andsal<=3000;

selectempnobianhao,enamexingming,jobzhiwei,salyuexin,hiredaterzsj,(sal+comm)*12nianxinfromempwheresalbetween1500and3000;

7).查询出编号为7369，7844,7902三个人的信息(两种写法)

select*fromempwhereempno=7369orempno=7844orempno=7902;

select*fromempwhereempnolike7369orempnolike7844orempnolike7902;(like效率低)

select*fromempwhereempnoin(7369,7844,7902);

8).查询出编号不为7369，7844,7902人的信息(两种写法)

select*fromempwhereempno!=7369andempno!=7844andempno!=7902;

select*fromempwhereempnonotlike7369andempnonotlike7844andempnonotlike7902;(like效率低)

select*fromempwhereempnonotin(7369,7844,7902);

9).查询出名字中包含A的人的信息

selectempnobianhao,enamexingming,jobzhiwei,salyuexin,hiredaterzsj,sal*12nianxinfromempwhereenamelike'%A%';

10).查询出名字中倒数第三个字符为A的人的信息

selectempnobianhao,enamexingming,jobzhiwei,salyuexin,hiredaterzsj,sal*12nianxinfromempwhereenamelike'%A__';

11).查询出薪水大于等于2000小于等于3000并且职位不等于ANALYST的人的编号，名字，职位，月薪，奖金，年薪

selectempnobianhao,enamexingming,jobzhiwei,salyuexin,hiredaterzsj,sal*12nianxinfromempwheresal>=2000andsal<=3000andjob!='ANALYST';

12).查询出入职日期28-9月-81之后入职并且奖金不为空的人的编号，名字，职位，月薪，奖金，年薪

selectempnobianhao,enamexingming,jobzhiwei,salyuexin,hiredaterzsj,sal*12nianxinfromempwherehiredatebetween'28-9月-81'andsysdateandcommisnotnull;

selectempnobianhao,enamexingming,jobzhiwei,salyuexin,hiredaterzsj,sal*12nianxinfromempwherehiredate>'28-9月-81'andcommisnotnull;

13).按照员工月薪由高到低的顺序进行排列后的员工的信息

selectempnobianhao,enamexingming,jobzhiwei,salyuexin,hiredaterzsj,sal*12nianxinfromemporderbysaldesc;

14).查询出薪水大于1500的人的编号，姓名，薪水，年薪并且按照薪水的降序进行显示

selectempnobianhao,enamexingming,jobzhiwei,salyuexin,hiredaterzsj,sal*12nianxinfromempwheresal>1500orderbysaldesc;

15).查询出薪水在1000到4000之间的人的信息并且按照入职时间的先后进行显示

selectempnobianhao,enamexingming,jobzhiwei,salyuexin,hiredaterzsj,sal*12nianxinfromempwheresalbetween1000and4000orderbyhiredate;

16).查询出薪水在1000到4000并且名字中包含A并且奖金为空的人的信息并且按照薪水的降序进行排列

17).求出名字中包含a(不区分大小写)的人的信息

注意：基本函数(lower,upper,substr,to_char,to_date等)可以用在select和from之间的查询字段的列表处也可以用在where条件语句中

selectempnobianhao,enamexingming,jobzhiwei,salyuexin,hiredaterzsj,sal*12nianxinfromempwherelower(ename)like'%a%';

18).查询出薪水在1000到4000并且名字中包含A并且奖金为空的人的信息并且按照薪水的降序进行排列

selectempnobianhao,enamexingming,jobzhiwei,comm,salyuexin,hiredaterzsj,sal*12nianxinfromempwheresalbetween1000and4000andenamelike'%A%'andcommisnullorderbysaldesc;

19).查询出名字前两个字符为al的人的信息(两种写法实现)

selectempnobianhao,enamexingming,jobzhiwei,comm,salyuexin,hiredaterzsj,sal*12nianxinfromempwherelower(ename)like'al%';

selectempnobianhao,enamexingming,jobzhiwei,comm,salyuexin,hiredaterzsj,sal*12nianxinfromempwherelower(substr(ename,1,2))='al';

19-5).查询出名字中后两位字符为er人的信息(两种写法实现)

selectempnobianhao,enamexingming,jobzhiwei,comm,salyuexin,hiredaterzsj,sal*12nianxinfromempwherelower(ename)like'%er';

selectempnobianhao,enamexingming,jobzhiwei,comm,salyuexin,hiredaterzsj,sal*12nianxinfromempwherelower(substr(ename,length(ename)-1,2))='er';

20).查询出薪水大于1200并且入职日期在1981-04-02之后的人的编号，名字，月薪，年薪(不包含奖金),入职日期,格式化中国人能接受的日期形式

selectempnobianhao,enamexingming,salyuexin,sal*12nianxin,to_char(hiredate,'YYYY-MM-DD')rzsjfromempwheresal>1200andhiredate>to_date('1981-04-02','YYYY-MM-DD');

21).求出名字中包含a并且入职日期在1982-06-01之后入职的人编号，名字，月薪，年薪(不包含奖金),入职日期,格式化中国人能接受的日期形式

并且按照月薪的由高到低的顺序进行显示(两种形式to_char和to_date)

selectempnobianhao,enamexingming,salyuexin,sal*12nianxin,to_char(hiredate,'YYYY-MM-DD')rzsjfromempwherelower(ename)like'%a%'andhiredate>to_date('1982-06-01','YYYY-MM-DD')orderbysaldesc;

selectempnobianhao,enamexingming,salyuexin,sal*12nianxin,to_char(hiredate,'YYYY-MM-DD')rzsjfromempwherelower(ename)like'%a%'andto_char(hiredate,'YYYY-MM-DD')>'1982-06-01'orderbysaldesc;

22).查询出月薪在公司的平均工资之上人的信息

 组函数(avg,count,max,min,sum)只可以用在查询的列表 不可以用在where的条件语句中

 如果在查询列表出现组函数 那么通常情况下不能单独出现其他字段 除非其他字段也放到相应的组函数中才可

selectempnobianhao,enamexingming,jobzhiwei,salyuexin,hiredaterzsj,commjiangjin,(sal+nvl(comm,0))*12nianxinfromempwheresal>(selectavg(sal)fromemp);

23).求出部门编号为10的所有员工的平均工资

selectdeptno,avg(sal)fromempwheredeptno=10groupbydeptno;

24).求出公司每年总的支出，以及平均薪水，以及总人数

selectsum((sal+nvl(comm,0))*12)zongzhichu,avg(sal)pjgz,count(ename)zrsfromemp;

25).求出公司中每个部门的平均薪水

在使用groupby时，有一个规则需要遵守，即出现在select列表中的字段，如果没有在组函数中，那么必须出现在groupby子句中。

selectdeptno,avg(sal)fromempgroupbydeptno;

26).求出每个部门的部门编号，每年总的支出，以及该部门的平均薪水，以及该部门的人数

selectdeptno,sum((sal+nvl(comm,0))*12),avg(sal),count(ename)fromempgroupbydeptno;

27).查询出部门平均薪水大于1600的部门的平均薪水，人数以及该部门一年总的支出

selectavg(sal),count(ename),sum((sal+nvl(comm,0))*12)fromempgroupbydeptnohavingavg(sal)>1600;

28).求出每个部门的部门编号，每年总的支出，以及该部门的平均薪水，以及该部门的人数并且按照平均薪水由高到低的顺序进行排列

selectdeptno,sum((sal+nvl(comm,0))*12),avg(sal),count(ename)fromempgroupbydeptnoorderbyavg(sal)desc;

小结：sql语句select的语法

selectcolumn1,column2,.....fromtablenamewhere过滤条件(andor)groupbycolumnhaving分组的限制条件

 order by column(最后进行排序)

 注意：where过滤条件中只允许使用普通函数 不可以使用组函数   但是having分组限制条件中可以使用组函数

29)*.查询出工资大于1200，并且入职日期在1981-09-09以后的部门里面的人的平均薪水大于2000的部门的平均工资及部门编号 并且将其结果按照平均工资进行降序的排列

30).查询出公司所有人里面工资在部门编号为30最高工资之上的人信息

selectempnobianhao,enamexingming,jobzhiwei,salyuexin,hiredaterzsj,commjiangjin,(sal+nvl(comm,0))*12nianxinfromempwheresal>(selectmax(sal)fromempwheredeptno=30);

SELECTename,salFROMempWHEREsal>ALL(selectsalfromempwheredeptno=30);

31).查询出工资大于公司的平均工资并且入职日期大于1981-02-16，并且名字中包含a的人的编号，名称，月薪，年薪并且按照年薪进行降序排列

selectempnobianhao,enamexingming,jobzhiwei,salyuexin,hiredaterzsj,commjiangjin,(sal+nvl(comm,0))*12nianxinfromempwherehiredate>to_date('1981-02-16','YYYY-MM-DD')andlower(ename)like'%a%'andsal>(selectavg(sal)fromemp);

32).公司里面薪水最高的人的编号，名称，工作，入职日期，年薪

selectempnobianhao,enamexingming,jobzhiwei,salyuexin,hiredaterzsj,commjiangjin,(sal+nvl(comm,0))*12nianxinfromempwheresal=(selectmax(sal)fromemp);

33)*.求平均薪水大于1600的部门里面工资大于1200的人所在的部门平均薪水，并且按照平均薪水的降序进行排序

34-1).查比部门号为10中最低的工资高的人的信息(不用组函数)。

SELECTename,salFROMempWHEREsal>ANY(SELECTsalFROMempWHEREdeptno=10);用some也可以

34-2).找到和30号部门员工的任何一个人工资相同的那些员工

SELECTename,salFROMempWHEREsal=SOME(SELECTsalFROMempWHEREdeptno=30)ANDdeptno<>30;

34-3).找到比部门号为20的员工的所有员工的工资都要高的员工，也就是比部门编号为20的那个工资最高的员工的还要高的员工(不用组函数)

SELECTename,salFROMempWHEREsal>ALL(selectsalfromempwheredeptno=20);

35).求出每个人的编号，名称，工资，入职日期，部门编号，部门名称及部门所在地

selectempno,ename,sal,to_char(hiredate,'YYYY-MM-DD'),emp.deptno,dname,locfromemp,deptwhereemp.deptno=dept.deptno;

selectempno,ename,sal,to_char(hiredate,'YYYY-MM-DD'),emp.deptno,dname,locfromempleftjoindeptonemp.deptno=dept.deptno;

selectempno,ename,sal,to_char(hiredate,'YYYY-MM-DD'),emp.deptno,dname,locfromempinnerjoindeptonemp.deptno=dept.deptno;

selectempno,ename,sal,to_char(hiredate,'YYYY-MM-DD'),dept.deptno,dname,locfromemprightjoindeptonemp.deptno=dept.deptno;

36).查询工资大于公司平均工资，且名字中不包含a的，并且入职日期大于1982-07-07的员工的编号，名称，月薪，年薪，部门编号，部门名称，部门所在地并且按照薪水降序进行排列

selectempno,ename,sal,(sal+nvl(comm,0))*12nianxin,to_char(hiredate,'YYYY-MM-DD'),emp.deptno,dname,locfromemp,deptwhereemp.deptno=dept.deptnoandsal>(selectavg(sal)fromemp)andlower(ename)notlike'%a%'andhiredate>to_date('1982-07-07','YYYY-MM-DD')orderbysaldesc;

selectempno,ename,sal,(sal+nvl(comm,0))*12nianxin,to_char(hiredate,'YYYY-MM-DD'),emp.deptno,dname,locfromempinnerjoindeptonemp.deptno=dept.deptnoandsal>(selectavg(sal)fromemp)andlower(ename)notlike'%a%'andhiredate>to_date('1982-07-07','YYYY-MM-DD')orderbysaldesc;

37).求每个人的工资属于哪个级别

selectsal,grade,losal,hisalfromemp,salgradewheresalbetweenlosalandhisal;

selectename,sal,grade,losal,hisalfromempleftjoinsalgradeonsalbetweenlosalandhisal;

38).求出工资在公司的平均工资之上，并且入职日期在1981-06-04之后，并且名字中包含a的人的编号，姓名，工资，等级

selectempno,ename,sal,gradefromemp,salgradewheresalbetweenlosalandhisalandsal>(selectavg(sal)fromemp)andhiredate>to_date('1981-06-04','YYYY-MM-DD')andlower(ename)like'%a%';

39).查出名字里面第二个字母不是A的人的信息以及所在的部门情况以及工资的等级情况

selectempno,ename,sal,(sal+nvl(comm,0))*12nianxin,to_char(hiredate,'YYYY-MM-DD'),emp.deptno,loc,grade,dnamefromemp,dept,salgradewheresalbetweenlosalandhisalandemp.deptno=dept.deptnoandupper(ename)notlike'_A%';

selectempno,ename,sal,(sal+nvl(comm,0))*12nianxin,to_char(hiredate,'YYYY-MM-DD'),emp.deptno,loc,grade,dnamefromempinnerjoindeptonemp.deptno=dept.deptnoinnerjoinsalgradeonsalbetweenlosalandhisalwhereupper(ename)notlike'_A%';

40).每个人和对应的经理人(领导)的信息

selecte1.ename,e1.empno,e1.mgr,e1.sal,e2.ename,e2.empno,e2.salfromempe1,empe2wheree1.mgr=e2.empno;

selecte1.ename,e1.empno,e1.mgr,e1.sal,e2.ename,e2.empno,e2.salfromempe1leftjoinempe2one1.mgr=e2.empno;

41).求出每个部门以及对应的部门的员工信息

selectename,empno,sal,to_char(hiredate,'YYYY-MM-DD'),e.deptno,dname,locfromempe,deptdwheree.deptno=d.deptno;

selectename,empno,sal,to_char(hiredate,'YYYY-MM-DD'),d.deptno,dname,locfromemperightjoindeptdone.deptno=d.deptno;

42).求出每个部门平均薪水等级

selectpj,gradefrom(selectdeptno,avg(sal)pjfromempgroupbydeptno)innerjoinsalgradeonpjbetweenlosalandhisal;

43).求平均薪水最高的部门编号

---最高的平均薪水---求出每个部门的平均薪水

selectdeptnofrom(selectdeptno,avg(sal)pjfromempgroupbydeptno)wherepj=(selectmax(pj)from(selectdeptno,avg(sal)pjfromempgroupbydeptno));

44).求平均薪水最低的部门名称

----3求出平均薪水最低的部门的编号----2求出平均薪水最低的值-----1求出平均薪水

selectdnamefromdeptinnerjoin(selectdeptno,avg(sal)pjfromempgroupbydeptno)aondept.deptno=a.deptnowherepj=(selectmin(pj)from(selectdeptno,avg(sal)pjfromempgroupbydeptno));

45).求平均薪水等级最低的部门部门名称

selectdnamefromsalgradeinnerjoin(selectdeptno,avg(sal)pjfromempgroupbydeptno)aona.pjbetweensalgrade.losalandsalgrade.hisalinnerjoindeptondept.deptno=a.deptnowherepj=(selectmin(pj)from(selectdeptno,avg(sal)pjfromempgroupbydeptno));

----先求出平均薪水等级最低的部门的部门编号----求出平均薪水等级最低的值-----求出平均薪水等级情况-----平均薪水的值

46).薪水最高的前5个人

select*from(selectrownumr,a.*from(select*fromemporderbysaldesc)a)whererbetween1and5;

47).按薪水从高到低排列的第6个到第10个人的信息

select*from(selectrownumr,a.*from(select*fromemporderbysaldesc)a)whererbetween6and10;

48).查询出名字中包含a的，并且薪水大于1200，并且入职日期大于1979-01-03的人里面薪水由高到低顺序排名的3到7人的信息

select*from(selectrownumr,a.*from(select*fromempwherelower(ename)like'%a%'andsal>1200andhiredate>to_date('1979-01-03','YYYY-MM-DD')orderbysaldesc)a)whererbetween3and7;

49).查询出名字中包含a的，并且薪水大于1200，并且入职日期大于1979-01-03的人里面薪水由高到低顺序排名的3到7人的编号，姓名，职位，月薪，年薪，部门名称，所在地以及工资的等级情况

    select empno,ename,job,sal,(sal+nvl(comm,0))*12 nianxin,d.deptno,dname,loc,grade from (select rownum r,a.* from (select * from emp where lower(ename) like '%a%' and sal>1200 and hiredate>to_date('1979-01-03','YYYY-MM-DD') order by sal desc) a) b inner join dept d on b.deptno=d.deptno inner join salgrade s on b.sal between s.losal and s.hisal where r between 3 and 7;

数据库的分页：

 select * from (select rownum r,a.* from (?) a) where r between ? and ?

第一个问号：指的是要查询的sql语句(包含排序)

第二个问号：指的是分页查询的起始行号

 第三个问号：指的是分页查询的结束行号

oracle分页利用的rownum伪字段

mysql的分页利用的limit关键字

sqlserver的分页利用的top

视图：

以数据库管理员身份(dba)登录数据库在cmd中输入sqlplus sys/密码 as sysdba

创建视图 需要赋权给scott用户grant create table,create view to scott; 

createviewv$_dept_avg_sal_infoasselectdeptno,grade,avg_salfrom(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)tinnerjoinsalgradeson

(t.avg_sal between s.losal and s.hisal);

使用视图的目的：

1。降低操作复杂度；视图是预编译的查询操作，一次定义，之后可快速调用；

2。提高系统安全。视图作为数据库对象，可以将其权限独立出来赋给用户，可避免用户对基表的盲目危险操作，同时也可屏蔽一部分私密的属性列。

1.查询出每门课都大于80分的学生姓名        

2.查询出username和passwd重复的数据

3.删除掉student表中重复的记录

思路：(1)先查询出表中重复的记录

(2)将查询的重复记录的条件作为删除数据的条件之一(删除的就是这些重复的记录但是要保留一个)

(3)如何去区分出这些重复的记录中保留的唯一的一条记录(利用rowid[取出rowid的最小值或者最大值])

(4)删除的另外一个条件是取出的rowid最小值的那条重复的记录不能删除

deletefromstudent

where(username,passwd)in(

selectusername,passwdfromstudent

groupbyusername,passwd

havingcount(*)>1)

androwidnotin(

selectmin(rowid)fromstudent

groupbyusername,passwd

havingcount(*)>1)