一、sql语句:
1、desc:描述……
2、||:追加字母
3、排序,默认是升序,降序是des
distinct:去掉不同的,如:select distinct name from form;
alter user:更改用户
select name,address,salary*12 annual_sa from form where name not like '_a%' and salary > 5000 order by salary desc;
4、sql函数:
a:lower(name)将大写转换成小写,如: select lower(name) from form;
b:uper()转换成大写
c:substr()单行函数,截取字符串,如 select substr(name,2,3) from form;
d:chr(),ascii(),编码转换,如:select chr(65) from form; select ascii('A') from form
e:round(),四舍五入,如:select round(2323.32,1) from form;
f:tochar(),字符格式,如:select tochar(salary,'$999,999.9999') from form;用L表示人民币,select tochar(salary,'L000,000.0000') from form;
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from form;
g: todate(),日期格式转换
h:tonumber(),将字符串转换成相关数字,如:select * from form where salary > tonumber('$1,233.00','$999,999.99');
i:nvl()……
以下是组函数:(重要)
a:sum():求和
b:count():求非空的数量
c:avg():求平均值
d:max(),min()最大,最小
5、group:如:select deptno,avg(sal) from emp group by deptno;
6、出现在select列表里的字段(列表例有组函数),没有出现在组函数里必须出现在group by里面(小难点),如:select ename, max(sal) from emp group by deptno;(可能产生不唯一的值不行)
select deptno, max(sal) from emp group by deptno;(可以)
7、where语句是对单条语句进行过滤的,不能过滤分组之后的记录(优先于group by执行)
8、having,对分组进行限制,如: select avg(sal),deptno from emp group by deptno having avg(sal) > 2000;
9、select * from emp(顺序)
where sal > 1000
group by deptno
having
order by
10、select 语句必须非常熟练!!!select avg(sal) from emp where sal > 1200 group by deptno having avg(sal) >1500 order by avg(sal) desc;
11、子查询:select ename from emp where sal =(select max(sal) from emp);
12、如果出现“不是单分组函数”或“此处不允许使用单分组函数”的错误时,尝试子查询!!
13、ed:弹出文本编辑器
14、select ename,sal,deptno from emp where sal in (select max(sal) from emp group by deptno);(错)只要是三个数据之一就会选出来
15、99年语法:select ename,dname from emp cross join dept;
老语法:select ename,dname from emp,dept where empno = deptno 新语法:select ename,dname from emp join dept on (empno = deptno);
或 select ename,dname from emp join dept using(deptno);(不推荐)
select ename,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);
select ename,dname,grade from emp e join dept d on (e.deptno = d.deptno)join salgrade s on (e.sal between s.losal and s.hisal)where ename not like '_A%'(新语法where只用于数据过滤)
16、左(右)外连接:把左(右)边表的多余数据也显示出来,如: select e1.ename,e2.ename from emp e1 left(right)【outer】 join emp e2 on (e1.mgr = e2.empno);
还有全外连接,如:select ename,dname from emp e full join dept d on (e.deptno = d.deptno);
17、“/”是执行上条语句
18、创建视图:create view v$_dept_avg_sal info as + 语句,如:create view v as select avg(sal) avg_sal,deptno from emp group by deptno;
删除视图:drop view viewname
19、创建新表:create table pu (name varchar(50),address varchar(50),age number(20));
删除表:drop table pu;
19、命令:conn pu/pu [as sysdba] 切换用户
20、命令:grant create table,create view to scott 附权限
21、插入行:insert into form values (‘dingyuanpu’,’shandong’,21); 简单!
删除行:delete from pu where age = 25;
更新:update pu set name = 'yuan' where name = 'ding';
22、创建EM
emca -config dbcontrol db -repos create
或者是建过,但后台服务丢了:
emca -config dbcontrol db -repos recreate
23、查询前几条记录:select * from emp where rownum < num;(不同数据库产品不同)
24、以any为例说,该怎么理解‘任何值’呢?比如
select employee_id,last_name from employees where salary > any (select high_salary from salary_grades);
select employee_id,last_name from employees where salary > all (select low_salary from salary_grades);
s有区别!
25、基本的Sql编写注意事项
* 尽量少用IN操作符,基本上所有的IN操作符都可以用EXISTS代替。
* 不用NOT IN操作符,可以用NOT EXISTS或者外连接+替代。
* Oracle在执行IN子查询时,首先执行子查询,将查询结果放入临时表再执行主查询。而EXIST则是首先检查主查询,然后运行子查询直到找到第一个匹配项。NOT EXISTS比NOT IN效率稍高。但具体在选择IN或EXIST操作时,要根据主子表数据量大小来具体考虑。
* 不用“<>”或者“!=”操作符。对不等于操作符的处理会造成全表扫描,可以用“<” or “>”代替。
* Where子句中出现IS NULL或者IS NOT NULL时,Oracle会停止使用索引而执行全表扫描。可以考虑在设计表时,对索引列设置为NOT NULL。这样就可以用其他操作来取代判断NULL的操作。
* 当通配符“%”或者“_”作为查询字符串的第一个字符时,索引不会被使用。
* 对于有连接的列“||”,最后一个连接列索引会无效。尽量避免连接,可以分开连接或者使用不作用在列上的函数替代。
* 如果索引不是基于函数的,那么当在Where子句中对索引列使用函数时,索引不再起作用。
* Where子句中避免在索引列上使用计算,否则将导致索引失效而进行全表扫描。
* 对数据类型不同的列进行比较时,会使索引失效。
* 用“>=”替代“>”。
* UNION操作符会对结果进行筛选,消除重复,数据量大的情况下可能会引起磁盘排序。如果不需要删除重复记录,应该使用UNION ALL。
* Oracle从下到上处理Where子句中多个查询条件,所以表连接语句应写在其他Where条件前,可以过滤掉最大数量记录的条件必须写在Where子句的末尾。
* Oracle从右到左处理From子句中的表名,所以在From子句中包含多个表的情况下,将记录最少的表放在最后。(只在采用RBO优化时有效,下文详述)
* Order By语句中的非索引列会降低性能,可以通过添加索引的方式处理。严格控制在Order By语句中使用表达式。
* 不同区域出现的相同的Sql语句,要保证查询字符完全相同,以利用SGA共享池,防止相同的Sql语句被多次分析。
* 多利用内部函数提高Sql效率。
* 当在Sql语句中连接多个表时,使用表的别名,并将之作为每列的前缀。这样可以减少解析时间。
26、EXISTS 和NOT EXISTS与IN和NOT IN 的比较
EXISTS:只检查行的存在性
IN :则要检查值的实际存在性。
EXISTS 性能高于IN,应优先使用。
当用于比较的列值为NULL时:
NOT EXISTS 返回True
NOT IN 返回false
如:
select ename from emp where exists (select comm from emp);
select ename from emp where comm in (select comm from emp);
27、包含子查询的UPDATE
update employees set salary = (select avg(high_salary) from salary_grades) where employee_id = 4;
28、包含子查询的DELETE
delete from employees where salary > (select avg(high_salary) from salary_grades);
例题:
查询每个部门的哪些人薪水最高
select ename,sal from emp join (select max(sal) max_sal,deptno from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno = t.deptno);
select ename,sal,e.deptno from emp e join (select max(sal) max_sal,deptno from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno = t.deptno);(当两个表中都有同一个列名时要明确定义列)
雇员中哪些人是经理人(自连接)
select e1.ename, e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;(为一张表起不同的别名,当成两张表来用)
或select e1.ename, e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno);
或select ename from emp where empno in (select distinct mgr from emp);
求部门平均薪水的等级
select deptno,avg_sal,grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal)
求部门平均的薪水等级
select deptno,avg(grade) from (select deptno,ename,grade from emp join salgrade s on (emp.sal between s.losal and s.hisal)) t group by deptno;
不准用组函数,求薪水的最高值(面试题)
select sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));
求薪水最高的部门的部门编号
select deptno,avg_sal from (select avg(sal) avg_sal,deptno from emp group by deptno) where avg_sal = (select max(avg_sal) max_sal from (select avg(sal) avg_sal,deptno from emp group by deptno));
(组函数可以嵌套,最多两层)如上面的可以是:
select deptno,avg_sal from (select avg(sal) avg_sal,deptno from emp group by deptno) where avg_sal = (select max(avg(sal))from emp group by deptno);
嵌套分组函数要有group by
求薪水最高的部门的部门名称
select dname from dept join (select deptno,avg_sal from (select avg(sal) avg_sal,deptno from emp group by deptno) where avg_sal = (select max(avg_sal) max_sal from (select avg(sal) avg_sal,deptno from emp group by deptno))) t on (dept.deptno = t.deptno); 或
select dname from dept where dept.deptno = (select deptno from (select avg(sal) avg_sal,deptno from emp group by deptno) where avg_sal = (select max(avg_sal) max_sal from (select avg(sal) avg_sal,deptno from emp group by deptno)));
求平均薪水的等级最低的部门名称(难?)
select dname from dept where deptno = (select deptno from (select avg(sal) avg_sal,deptno from emp group by deptno) t where t.avg_sal = (select min(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno)));
或:
create view v as select avg(sal) avg_sal,deptno from emp group by deptno;(先创建视图)
select dname from dept where deptno = (select deptno from v t where t.avg_sal = (select min(avg_sal) from v));
求部门经理中平均薪水最低的部门名称
create view vi as select ename,sal,deptno from emp where empno in (select mgr from emp);
create view vie as select avg(sal) avg_sal,deptno from vi group by deptno;
select dname,deptno from dept where deptno = (select deptno from vie where avg_sal = (select min(avg_sal) from vie)) ;
求比普通雇员中最高薪水还要高的经理名称
select ename from (select * from vi) t where t.sal > (select max(sal) max_sal from emp where ename not in (select ename from vi));
求薪水最高的前五名雇员
create view v$ as select ename,sal from emp order by sal desc;
select ename,sal from v$ where rownum < 6;
求薪水最高的第六到第十名雇员(具体见rownum专题)
select * from (select rownum r,v$.* from v$ where rownum <=10) where r >= 6;(高效)
select * from v$ where rownum < 11 minus select * from v$ where rownum < 6;(咋无序?)
select rownum r,v$.* from v$ where rownum < 11 minus select rownum r,v$.* from v$ where rownum < 6;(而这个有序)
select * from (select rownum r,v$.* from v$) where r > 5 and r < 11;(效率不如第一个)
比较效率(面试题)
select * from emp where deptno = 10 and ename like ‘%A%’;
select * from emp where ename like ‘%A%’ and deptno = 10;