测试学习第十七天——oracle学习第一次总结

1、首先用dos窗口登陆：sqlplus sys as sysdba

2、更改账户：alter user scott account unlock;  -->用户已更改

3、输入用户名：scott,口令：xxxx

4、desc   emp（table名） :

select讲解

5、select * from  salgrade(表名)

6、select emono ,emame ,deptno from emp

7、select 2*3 from emp ???

8、desc dua(空)

9、select sysdate from dual -->sysdate:系统当前时间 dual表名 

10、select ename ,sal*12 anual_sal form. emp    -->起别名　列名：ename，ANUAL_SAL

11、selectename ,sal*12 ＂anual　sal＂ form. emp    -->起别名，其中有特殊字符，加上双引号，输出会保持原本格式(小写) ,列名：ename ，anual al

12、select ename , comm　from emp;输出comm列时，有空值，有0，没有为空值，0是有值，只是为0而已

13、select enamel ,salary*12+comm from emp:comm列，有空值，有0。当为空值时，算出的值也为空，为0时算出的值正确

13、select ename || sal from emp   -->连接符,输出为ename 的字符串sal字符连接，等价于java字符串的“+”

14、select ename || 'afahfafk' from emp   -->数据库中的表示字符串都是用‘’但引号引起来表示字符，例如‘affanfnn’

15、select ename || 'fjk’'fshoih' from emp   -->当字符串中已经有单引号，就用两个单引号来表示其中的单引号

distinct 讲解：去掉重复的

select distinct deptno ,job from emp -->去掉相同的行，只显示一次

where详解：有选择，按条件(包含了null  ,in  ，日期,匹配符_ %，转义字符escape)

1、select * from emp where deptno = 2 -->选择2部门的人员

2、select * emp where job = MANAGER   -->选择工作是经理的人员信息

3、select ename ,sal from emp where sal >1500   -->选择薪水>1500的名字和薪水

4、select ename,sal depto from emp where deptno <> 10   -->选择部门不是10 的人员的名字，薪水和部门

5、select ename ，sal fom emp where ename >'CHA'  -->选择姓名大于CHA字符串的人的名字和薪水

，字符串的比较是按java的字符串的比较方式，比较从第一个字母开始字母的大小。

6、select * from emp where  sal between 800 and 1000   -->查找薪水在800到1000的人员的信息

7 、select  *  from emp where sal >800 and sal <1000

8、select ename , sal ,comm  from emp where comm = null ，写法错误

9、select ename , sal, comm from emp where comm is（not） null  -->查找comm 列值是空值的人员的名字，薪水和奖金

10、select ename , sal ,comm  from emp where sal in(1000,2222,1111)   -->查找薪水在1000，2,22,111的人员信息

11、select ename , sal ,comm  from emp where ename in ('sam','yezi','mingming')

12、select ename , sal ，hiredate from emp where  hiredate > ‘20-2月-81’   -->查找出生日期是81年2月20号的人信息。日期格式必须这样写，如果忘记了，可以“select sysdate from emp”来查看系统格式

13、select ename , sal ,comm  from emp where deptno = 10 and(or) sal >1000   选择部门为10 和（或）sal >1000的人员信息

14、select ename , sal ,comm  from emp where sal not in(1222,1234,5456)

15、select ename , sal ,comm  from emp where ename like '%a%a%' 匹配符，查找xaxax的名字匹配的名字，%表示匹配0个或多个字符，_表示匹配一个字符

16、select ename , sal ,comm  from emp where ename like '%$%ddf%' escape '$'  -->当名字中含有%时，使用转义字符，转移字符可以任意指定，用escape定义转义字符 

order by详解 排序

1、select *　from emp order by deptno desc    -->将表按照部门降序排列 desc 降序，asc 升序，使用

2、select ename , sal ,comm  from emp where deptno <>10 order by deptno asc  将表按照部门降序排列 asc 升序，排序，并且部门不等于10

3、select ename , sal ,comm  from emp where deptno <>10 order by deptno asc，ename desc.是将按照deptno排序，但是如果deptno相同时，内部按ename来进行排序

4、select ename ,sal*12 anunal_sal from emp where ename not like '_A%' and sal >1000 order by sal desc,将前面学习的内容一句总结了

函数详解

1、select lower(ename) from emp  -->将ename 取出来之后全部都变成小写输出来

2、select ename from emp where lower(enmae) like '_a%'  -->查出名字第二位是a(不管大小写)的信息

3、select ename from emp where ename like '_a%' or ename like '_A%'

4、upper（）函数类似

5、select substr(ename,2,3) from emp   截取字符串输出，第一个是被截取的字符串，第二个是第几个开始截取，第三个是截取的长度

6、select char(65) from dual -->把65转为相应的ASCII码值，返回给递归整数的ASCII码值，输出为A

7、select ascii（'A'）from dual  -->把字符转换为编码：输出为65

8、select round(58.345) from dual  -->四舍五入  输出58

9、select round(58.345，2) from dua   -->四舍五入,小数点后两位  输出58.35

10、select round(58.345，-1) from dua   --》四舍五入到10位  输出60

11、select to_char(sal,$99.999.9999) from temp;   -->将第一个参数转换为你在第二个参数设置的格式，美元格式

12、select to_char(sal,L99.999.9999) from temp;    -->输出格式为人民币的格式

13、select to_date(hiredate,'YYYY-MM-DD HH/HH24:MI:SS'）  from emp    -->指定日期的格式.HH是12进制，HH24是24小时的格式

14、select ename,hiredate from emp where hiredate>to_date('1981-1-11 12:32:23','YYYY-MM-DD HH24:MI:SS')    -->当将表中日期与比较日期的格式不一致时，用to_date（）函数转换再比较

15、select sal from emp where sal >$1.234.00 错误格式，格式不匹配

16 select sal from emp where sal > to_number('$1.234.00','$9.999.99');

17 select ename sal*12 + nvl(comm,0) from emp   -->每个人每年挣多少钱 ,nvl（）函数，如果参数为null，将其转换为0，如果参数不为空，保留参数的值

组函数，group function:给多个函数，只返回一个值

1、select max(sal) min(sal) avg(sal)  to_char(avg(sal),'9999999.99') from emp

2、select round(avg(sal),2) from emp

3、select sum(sal) from emp    -->求和

4、select count(*) from emp  -->求出表中有多少条记录

5、select count( *) from emp where deptno = 10;

6.select count(ename) from emp  -->count（*）求出所有的条数，count(comm) :当计算某个列时，就会忽略null 的列，返回非空的条数

group by :按分组来查询：没有出现　group by 后面的列名不能出现在select 后面

1、select deptno avg(sal) from emp group by deptno  -->查询每个部门的平均薪水

2、select max(*) from emp group by deptno ,job  -->按照两个字段分组，然后找出分组后的最大值

3、select  ename from emp where sal = (select max(sal) from emp);

4、select ename max(sal) from emp   -->错误，因为组合函数只输出一条信息，但是很有可能最高工资的人员不只一个人，所以ename输出不止一个，就会出错

5、select  ename  max(sal) from emp group by deptno ename;如果不按ename分组，就会出错

having 

1、select avg(sal) deptno from  emp group by deptno  having avg(sal) > 2000   -->  where是对单条的语句过滤,having 是对分组语句进行限制

2、select * from emp where sal > 1000 group by depto  having  agv(sal) >2000  order by ename.这是一条完整的sql语句，是执行不了的，但是是从左到又的顺序进行执行的

3、select avg(sal) from emp where sal >1200 group by deptno having avg(sal) >1500 having avg(sal) desc

子查询详解

1、select ename .sal from emp where sal  = (select max(sal) from emp);-->嵌套查询就是子查询

2、select ename  from emp where sal >(select avg(sal) from emp)   -->查询所有位于平均工资之上的人员名字

3、select ename deptno from emp where sal in(select max(sal)) group by deptno   -->where 

sal in(sekect max(sal))，找的不是每个部门的最高薪水。找的是每个部门只要包含 返回的值 的所有人员。将in 换成 = 也错误，因为 字句返回的是每个部门最高值，有多个值，sal = 多个值错误

4、select ename sal from emp join(select max(sal) max_sal  ,deptno  from emp group by deptno) t on( t.max_sal = emp.sal and emp.deptno = t.deptno); -->寻找每个部门的最高薪水的人理解子查询，把它当成一张表，然后再将两张表连接查询

self-table—connection:自连接：为同一张表起别名，当成两张表来用

1、select e1.ename ,e2.ename from emp e1 emp e2 where e1,mgr = e2.empno   -->选出员工姓名和他的经理人的姓名，条件是员工的经理编号等于经理的员工编号

sql1999—table-connection

等值连接

1、select ename,dname from emp cross join dept;  -->交叉连接（cross join）,是笛卡尔积

2、select ename ,dname from emp,dept where  emp.deptno = dept.deptno   -->老式写法，条件在where里

3、select ename,dname from emp join dept on (emp.deptno = dept.deptno)  -->新写法，条件在no

4、select ename from emp join dept using(deptno)   -->如果是等值连接，可以用using,就是表示两个表的deptno相等，不推荐使用

非等值连接

1、select ename ,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal)

等值连接和不等值连接合用：

2、select ename ,dname ,grade from emp e join dept d on(e.deptno = t.deptno) join salgradde s on (e.sal between s.losal and s.hisal) where ename not like '_A%';

3、select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mrg = e2.empno) -->用新式写法查找员工和他的经理的名字，

4、select e1.ename ,e2.ename from emp e1 left （outer） join emp e2 on(e1.mrg = e2.empno)   -->左连接会把左边的那张表的人员的全部信息显示出来

5、select　ename ,dname from emp e right （outer） join dept d on (e.deptno = d.deptno)  -->右外连接，会把右边所有的信息全部显示出来

6、select　ename ,dname from emp e full join dept d on (e.deptno = d.deptno) --》左右都显示出来，全连接

7、select avg_sal ,deptno from (slect deptno ,avg(sal) avg_sal  from emp group by deptno) t join salgrade s on(t.avg_sal between s.losal and s.hisal )   -->部门的平均薪水的等级

8、select ename ,sal from (select max(sal) max_sal,deptno from emp group by deptno) t join emp  e on (emp.deptno =  t.deptno)   -->求部门那些薪水最高

9、select ename , deptno,grade from emp join salgrade s on(emp.sal between s.lowsal and s.hisal)-->每个人的薪水等级

10、select avg(grade) from t group by deptno   -->t是九小题中的所有命名为t，求每个组的薪水等级的平均值

11、select ename ,empno from emp where empno in(select mgr from emp )   -->哪些是经理人

12、select sal from emp where sal not in(select sal from emp  e1 join emp e2 on(e1.sal < e2.sal))   -->不用组函数算出薪水值最大的，思想，找出e1表中薪水<e2表中的薪水，包含了所有小于最大值的。然后再用not in找出最大值

13、select avg_sal,deptno from (select avg(sal) avg_sal , deptno from emp group by deptno) 

where avg_sal = (select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by 

deptno))  -->平均薪水最高的部门编号（首先找出每个部门的平均薪水和部门编号，将其作为一张表，在从这个表中查出部门号和平均薪水列，这一列的平均薪水等于最大的平均薪水）

13、select dname from emp where dempno =

(select deptno from (select avg(sal) avg_sal , deptno from emp group by deptno) 

where avg_sal = (select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by 

deptno)) )     -->查询平均工资最高部门的名称（12求得是最高平均水平的部门编号，然后查询部门名称部门编号等于最高平均水平的部门编号）

14、select t1.deptno dname ,grade ,avg_sal from 

(select deptno ,grade,avg_sal from(select avg(sal) avg_sal,deptno from emp group by deptno 

)t join salgrade s 0n(t.avg)sal btween s.losal and s.hisal)) t1

join dept on(t1.deptno = dept.deptno)

where t1.grade =

(select min(grade) from 

(select deptno ,grade,avg_sal from(select avg(sal) avg_sal,deptno from emp group by deptno 

)t join salgrade s 0n(t.avg)sal btween s.losal and s.hisal)) )    -->平均薪水等级最低的部门名称

15、select ename where sal >

(select max(sal) from emp where empno not in (select distinct mrg from emp where 

mgr is not null) )  and empno in ( select distinct mrg from emp where mgr is not null) -->比普通员工薪水还要高的经理人名称