有一些是网上转载的,如果有原地址,留言一下,我添加上去
删除表 drop table studnet. 表数据和结构都没有了
清空表 truncate table student 不能回滚数据了
添加表的列
alter table Student add phone varchar2(11) null;
删除表的列
alter table Student drop column phone;
修改表的列
alter table Student modify phone varchar2(12) ;
//todo 有时候自己去试一下,因为不同的博客写的有点不太一样,最终能不能执行,还有点不太清楚
如何在oracle中,让id自增
1.首先创建一个序列: ?? 什么是序列 序列就是序列呗
tablename_columnname_seq;//序列这样子命名比较好看
2.为Insert操作创建触发器,无需在SQL语句里写NEXTVAL,名称为:表名_INS_TRG
create or replace trigger table2_ins_trg before insert on table2 for each row when(new.order_id is null)
begin
select table2_order_id_seq.nextval into :new.order_id from dual; //这里使用Select into 居然不是insert into 神奇
end;
这样子就可以了,但是前提是这个用户必须有创建序列和触发器的权限......
左连接是左边不管有没有,都保持不变. //其实很多时候确实应该用左连接,而不是内连接.
select table1.*,table2.*
from table1
left join table2
on table1.customer_id=table2.customer_id; 
右连接是右边不管有没有,都保持不变: //为什么table1,还是放在前面,这个主要受到select后面的顺序的影响.
select table1.*,table2.*
from table2
right join table1
on table1.customer_id=table2.customer_id; 
以上两种写法,获得的结果是一模一样的.而且执行的效果也是一模一样的,
因为这两种写法其实就是一种写法...
一个简单的例子:
表:
获得来自杭州，并且订单数少于2的客户
法一:
select customer_id ,count(table2.customer_id) 人数
from table2 where customer_id in (select customer_id from table1 where city='hangzhou') //错在这一句
group by customer_id having count(customer_id)<2;结果:
错了,因为还有一名用户是'baidu',却没有显示出来.
错了,因为还有一名用户是'baidu',却没有显示出来.因为在table2中是没有这个baidu这个用户的,所以在group by中就自动的忽略掉了'baidu'这个用户
如果这道题目后面的那个条件再加上一个条件:必须有买过东西的客户,这样子就是正确的
还有一种跟上面类似的写法:
select a.customer_id
from table1 a ,table2 b
where a.customer_id=b.customer_id and a.city='hangzhou'
group by a.customer_id having count(*)<2;
不过这种写法,跟上面出现一样的错误,就是少了'baidu'这个客户
正确答案:
select a.customer_id, count(b.order_id) 人数
from table1 a
left join table2 b
on a.customer_id=b.customer_id
where a.city='hangzhou'
group by a.customer_id
having count(b.order_id)<2;用group by进行筛选的时候,会保留第一条记录.但是group by和having 一起使用的时候,
其实数据还是那几条,并不会变成单单的一条记录.
而且having 是为了group by才再出现的.所以有having的地方一定有 group by
但是有group by的地方,不一定要有 having
count() 会把null值一起算进去吗? (MySql中会)
having count(b.order_id)<2;最后一句 在使用count的时候,不管count(columnName)这个列名是不是NULL,都会计算进去的,(在MySql是这样的,不知道在Oracle是不是也是这样的)
对于没有应用order by的limit字句,得到的结果同样是无序的,所以很多时候,我们都将limit和order by 字句一起使用(Mysql的关键字 limit).这个是Mysql 但是Sql的执行顺序应该Oracle跟这个也是一样的
(7) SELECT
(8) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list> //如果select语句后面的列没有结合聚合函数,那么只能是group by 后面的这些列.因为如果不是这样的话,oracle没有办法决定是哪一行的数据 因为是先执行group by语句,所以只要你能保证select后面的列让Oracle知道怎么取就行了,记住Oracle是没办法从一组里面随机选择一个,必须是确定下来的
(6) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>
排序肯定是要放到最后都弄完之后再排序.
在执行SQL语句的过程中,都会产生一个虚拟表,用来保存SQL语句的执行结果.
having 必须结合group by一起使用,但是group by可是脱离having 这个很重要.这个其实很简单吧
经典sql题目:
首先是表结构:
Student(S#,Sname,Sage,Ssex) 学生表 |
S#：学号；Sname：学生姓名；Sage：学生年龄；Ssex：学生性别
|
Course(C#,Cname,T#) 课程表
|
C#：课程编号；Cname：课程名字；T#：教师编号
|
SC(S#,C#,score) 成绩表
|
S#：学号；C#,课程编号；score：成绩
|
Teacher(T#,Tname) 教师表
| T#：教师编号； Tname：教师名字
|
注意:下面这些都是选修课.不是必修课
1、查询同时选了"001课程"和"002课程“中001”课程比“002”课程成绩高的所有学生的学号；
select a.S# from (select S#,Score from SC where C#='001') a,(select S#,score from SC where C#='002') b
where a.score>b.score and a.S#=b.S#;答案里面这样子是同时选了这两门课的人
2、查询平均成绩大于60分的同学的学号和平均成绩；(每个学生都有在SC这张表里面,不要考虑有些学生的成绩没有录进去的情况)
select S# ,avg(score) '平均分数' from SC group by S# having avg(score)>60;Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
3. 查询所有同学的学号、姓名、选课数、总成绩；(如果要考虑Student中有一些学生没有课程的话,就是这种写法了)
select Student.S#,Sname,count(C#),sum(score) from Student left (outer) join SC on Student.S#=SC.S#
group by Student.S#,Sname.//不知道这里要不要填,多个group by是什么鬼
如果没有 那些聚合函数 group by 后面出现什么,就必须在select后面一起出现
可能有一些同学没有选课如果不考虑在Student中的一些学生不在SC中,就可以像下面这样子写,但是下面那样子也是错的(没有蓝色那部分),group by
和select之间的关系还是没有搞清楚.这里好就好在S#是唯一的,所以再加一个Sname 还是跟之前那个一样
select SC.S# ,Sname ,count(S#),sum(score) from SC,Student where
Student.S#=SC.S# group by SC.S# Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
4、查询姓“李”的老师的个数
其实这里根本就不需要考虑重名的情况,因为既然有这个老师,那么它的T#就是唯一的,所以.....
Select count(*) from teacher where Tname like '李%';
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
5 查询没学过“叶平”老师课的同学的学号、姓名 注:学号肯定是唯一的.
参考答案:
select Student.S#,Student.Sname
from Student
where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平'); //这里得到的是'叶平'老师有上过的课,然后得到上过
叶平老师的课的学生的学号() 还是这种写法看上去就是比较清爽
Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
6查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select a.S#,a.Sname from (select S# from SC where C#='001' )a ,
(select S# from SC where C#='002') b
where
a.S#=b.S# 下面这个方法看看就好 了解一下
select Student.S#,Student.Sname from Student,SC
where Student.S#=SC.S# and SC.C#='001'and
exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');
这个答案好奇怪,感觉怪怪的.Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
（1）表名：购物信息
| 购物人 商品名称 数量 A 甲 2 B 乙 4 C 丙 1 A 丁 2 B 丙 5 |
购买 "商品名称" 超过1个的购物人
select 购物人 from 购物信息 group by 购物人 having count(商品名称)>=2
select 购物人 from 购物信息 group by 购物人 having count(*)>=2
这两种有区别吗?应该没有区别.其实个人认识最好的做法是用cuont(*),因为count()函数感觉只是一个用来计算行数的.而不是具体某一个列.（2）表名：成绩表(score)(注:不要考虑姓名会重复,因为姓名重复这道题就没意义了)
| 姓名 课程 分数 张三 语文 81 张三 数学 75 李四 语文 56 李四 数学 90 王五 语文 81 王五 数学 100 王五 英语 49 |
select * from score where name in (select name from score group by name having min(分数)>59);
//这种写法是错误的,因为有可能有学生的的姓名是一样的.如果一个张三及格,一个张三不及格.那么会把不及格的一起查询出来
正确写法:
答：select * from 成绩表 where 姓名 not in (select distinct 姓名 from 成绩表 where 分数 < 60)
或者：
select * from 成绩表 where 姓名 in (select 姓名 from 成绩表 group by 姓名 having min(分数) >=60) 正确答案也是错的.
正确答案也没有考虑到学生姓名重复的情况.又或者说如果这张表内有多个张三,那么这张表已经没有意义了.因为你不知道语文数学,分别是哪个张三的,所以可以假设,不会有重复
（3）表名：商品表
名称 产地 进价 苹果 烟台 2.5 苹果 云南 1.9 苹果 四川 3 西瓜 江西 1.5 西瓜 北京 2.4 |
select 名称 from 商品表 group by 名称 having avg(进行)<2;插播一条:
drop will delete all data and the table structure as well
drop 是把数据和表结构都清除掉了
delete will delete the data but the table structure will remain the same and we can still rollback the data. Also with
DELETE you can use the where condition i.e. to delete only certain records.delete 只是删除数据,而且可以rollback 所以执行效率会比较慢,因为在删除的时候要进行相应的备份
Oracle 中的truncate delete drop commands
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows.
If no WHERE condition is specified, all rows will be removed.
After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.
通过使用 commit 或者 rollback 来使这个改变变成永久的,或者撤销这个改变
Note that this operation will cause all DELETE triggers on the table to fire.
to fire 这里面的to fire 就是执行的意思吧 ,擦火的意思TRUNCATE
TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.(不需要像delete一样需要一个undo撤销的空间)
这个操作不能回滚,而且不会触发触发器,这个速度非常快,而不需要像delete这样额外的空间来执行undo操作.会全部删除数据,但是表的结构是会保存下来的.
DROP
The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. (todo 没有DML触发器回去触发还是解雇)The operation cannot be rolled back.
这个是直接把整个表都删了,索引,权限什么都没有了.而且DML(select insert delete update)这些触发器不会被触发.
所以只有delete执行的时候会触发触发器,其他两个是不会触发触发器的.
PS: DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. As such, DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
Drop command will delete the entire row also the structure.But truncate will delete the contenets only not the strucure,(todo 意思是说除了表结构,其他类似索引这些东西也会被删掉的吗) so no need to give specifications for another table creation.
DML(Data definition language):select insert delete update 还有一些其他的
DDL(Data definition language):create alter drop
(主要用在定义或者改变表(alter)的结构,数据结构,表之间的连接和约束等初始化工作上)
DCL(Data Control language):数据库控制功能,用来设置或者更改数据库用户或角色权限的语句
包括(grant,deny(否认;拒绝),revoke(撤回,取消,废除)等)
（4）表名：高考信息表
| 准考证号 科目 成绩 2006001 语文 119 2006001 数学 108 2006002 物理 142 2006001 化学 136 2006001 物理 127 2006002 数学 149 2006002 英语 110 2006002 语文 105 2006001 英语 98 2006002 化学 129 |
给出高考总分在600以上的学生准考证号
select 准考证号 from 高考信息表 group by 准考证号 having sum(成绩)>600;| 准考证号 数学 语文 英语 物理 化学 2006001 108 119 98 127 136 2006002 149 105 110 142 129 |
select 准考证号 from 高考信息表 where (数学+语文+英语+物理+化学) > 600
where字句还可以这样子完,以前很少见到,只要这种方法吗?(四部分)
（一）表名：club
| id gender age 67 M 19 68 F 30 69 F 27 70 F 16 71 M 32 |
gender number
select a.*,b.* from
(select count(*) 男性 from table4 where gender='M') a,
(select count(*) 女性 from table4 where gender='F' )b;
所以可以这样写
select gender ,count(*) 人数 from TABLE4 group by gender;这种其实是最好的写法
两种方法第一种的方法格式比较好看!!! 但是比较麻烦一点
插播一条重要信息:
我自己在数据库中设计了一种表
desc club;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
GENDER CHAR(1)
数据为:GENDER
------
F
F
M这个时候我自己写了一条数据:
SQL> select gender, count(*) 人数 from club where gender='F';
select gender, count(*) 人数 from club where gender='F'
通过这个案例 可以知道在Oracle中: 如果在select中 Oralce必须具体知道它要取的是哪一条记录,绝对不能模棱两可
这里面首先通过 where gender='F'或者where gender='M' 得到数据之后,这个时候可能是一条也可能是多条
接下来因为你用到了count(*) 那么它就全部计算这些数据.可是这个时候又出现 gender 虽然我们知道 它肯定等于
gender='F' 但是Oracle 不知道啊,感觉Oracle好难用.其实只要Oracle肯动点脑子的话,还是很容易就输出其他一些数据的吧
继续 sql:
（二）表名：team(id是主键)
ID(number型) Name(varchar2型)1 a
2 b
3 b
4 a
5 c
6 c
要求:执行一个删除语句，当Name列上有相同时,只保留ID这列上值小的
例如：删除后的结果应如下:
ID(number型) Name(varchar2型)
1 a
2 b
5 c
下面这句话错的很离谱啊,having min(id)根本就没有这种写法啊啊啊啊
select * from team gruop by name having min(id);而且 select * 这样子也是错的吧 可以是这样子 select name
delete from team where id not in (select min(id) from team group by name) ;（三）表名：student(假设不存在有两个张青这样的人.就只有一个张青)
name course score张青 语文 72
王华 数学 72
张华 英语 81
张青 物理 67
李立 化学 98
张燕 物理 70
张青 化学 76
查询出“张”姓学生中平均成绩大于75分的学生信息
select * from student where name like '张%' group by name having avg(score)>75;
//参考答案
select * from student where name in (select name from student
where name like '张%' group by name having avg(score) > 75)
其实第一种写法不会输出一条记录,因为它的格式错误,错就错在 select * 这里面.所以参考答案才是那样写的.之前没有发现这一点 2017-07-211.一道SQL语句面试题，关于group by表内容：
info 表
date result
2005-05-09 win
2005-05-09 lose
2005-05-09 lose
2005-05-09 lose
2005-05-10 win
2005-05-10 lose
2005-05-10 lose
如果要生成下列结果, 该如何写sql语句?
date win lose
2005-05-09 1 3
2005-05-10 1 2
可以分表来分别完成
select date ,count(*) win from info where result ='win' group by date; ----a
select date ,count(*) lose from info where result ='lose' group by date;-----b
有这两句话分别得出这两张表
date win
2005-05-09 1
2005-05-10 1
date lose
2005-05-09 3
2005-05-10 2
然后就是进行连接
select a.date,a.win win,b.lose lose from a left join b on a.date=b.date .
//今天我又发现其实这种写法是存在一个漏洞的 比如说如果 2015-05-09这一天它没有赢过,或者输过呢
//那么是不是用全连接会更好一点呢 其实不然,因为只要具体某一天有比赛,那么肯定就有记录,如果
//某一天真的没有比赛的话,那么其实就没显示这条记录了
//上面那句话说错了吧,具体某一天真的有比赛 可能它都没赢过 那它肯定输过,这个时候赢的下面就是NULL而已 不不不 如果2005-05-09这一天它没有输过,而且它是左连接的话,那么就会少了这一天的.
另一种不常见的方法
select date, sum(case when result = "win" then 1 else 0 end) as "win",
sum(case when result = "lose" then 1 else 0 end) as "lose" from info
group by date; //执行顺序是先分组,然后在分组中,进行统计. 但是这种写法貌似在Oracle中不能执行所以这道题最好的方法是全外连接
select b.date1,a.lose lose ,b.win win from
(select date1 ,count(*) lose from info where result='lose' group by date1) a
full join
(select date1 ,count(*) win from info where result='win' group by date1) b
on a.date1=b.date1 //on这个条件感觉没什么用,只是因为语法上面的问题而已
order by a.date1;
那么这道题算是解决了表是用来存放数据的
oracle时间比较特殊 日-月-年
DD-MON-YY
Oracle 可以插入空值 null
查询没有生日的那些人
select * from student where birthday is null;//is not null
字符串是用单引号
修改多个字段 set name=‘’，age=12 用逗号来分割
null的话 记得用is 而不是用==
回滚操作，首先你要
savapoint aa;
设置一个回滚点。然后进行一下其他的操作
当你想回到之前那一步就直接 rollback to aa 就可以了。其实这些操作挺危险的。
一个有经验的oracle数据库管理员。首先一上班就建立一个保存点，然后进行其他操作，如果有错误，就回滚到之前的。
保存点如果不做处理的话，只能有一个保存点。旧的保存点会被覆盖掉。因为这需要大量的工作
上面这句话是假的吧,我做一下实验验证一下,不会被覆盖掉
truncate 比delete快，原因在于它不写日志，无法找回删除的记录。但是表的结构还在，就是数据没有了，而且不能恢复。
查询是最花时间的
学数据库有一半时间在学习查询
查询数据如果动不动就是select * 那么会很影响你的查询效率
set timing on 打开操作执行的时间。就是你每次操作，花了多次时间，都会显示出来。
复制表的数据，比如一张学生 id name
可以这样
insert into student (id ,name) select * from student; 两倍两倍的复制
Oracle对内容的大小写有区分的。是内容不是关键字
如何使用算数表达式
如果comm为空，那么都为空
Oracle有个毛病，如果运算过程中有个值为null，那么整个值都为null
如何处理null值，用nvl()函数来处理
select sal*12+nvl(comm,0) 年工资,ename from emp;这样子错误的
select ename,hiredate from emp where hiredate>'1-1月-1982'; //注意这里时间的格式
ENAME HIREDATE
---------- -----------
SCOTT 1987/4/19
ADAMS 1987/5/23
MILLER 1982/1/23工资范围
where sal sal>=2000 and sal<=4000
like
%表示任意个字符
_代表任意单个字符
in()集合
scott.emp表中如何显示没有上级的人
20:02:50 SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
order by 默认升序 desc（降序） asc（升序）
使用列的别名进行排序 order by 可以使用别名来进行排序
其实有些时候要记住什么时候可以用别名,什么时候不可以,其实本质是
sql语句的执行顺序.因为order by是最后执行的,所以select中列的别名已经
生成了
比如按照年薪进行排序
select ename,sal*12 年薪 from emp order by '年薪';
ENAME 年薪
---------- ----------
SMITH 9600
ALLEN 19200
WARD 15000
JONES 35700
MARTIN 15000
BLAKE 34200
CLARK 29400
SCOTT 36000
KING 60000
TURNER 18000
ADAMS 13200
JAMES 11400
FORD 36000
MILLER 15600查询最高工资和最低工资的并且是谁一起查出来？这个有点难度
where中是不能写入分组函数的
这里面必须用到子查询，我想我已经知道了,没办法只用有个sql语句
20:51:11 SQL> select ename,sal from emp where sal in (select max(sal),min(sal) from emp );
select ename,sal from emp where sal in (select max(sal),min(sal) from emp )
ORA-00913: 值过多
这种写法居然是错的，我简直震惊 有什么好震惊的,这很明显是错的! 子查询返回的是两列啊,可是你只用一列去接收,傻逼啊前方高能:
居然是分成两个句子来实现,我他妈的无语了
查询工资最高的员工的名字及其 工资
select ename,max(sal) from emp;
select ename,max(sal) from emp
ORA-00937: 不是单组分组函数
错误的原因是为什么呢
必须这个样子
select ename,sal from emp where sal=(select max(sal) from emp);
ENAME SAL
---------- ---------
KING 5000.00
感觉挺奇怪的。第一种用max（sal）查出来之后明明是已经可以确定一行了。或者多行也可以，但是为什么那种方法不行，这里面一定有什么交易。
但是
21:01:25 SQL> select min(sal),max(sal) from emp;
MIN(SAL) MAX(SAL)
---------- ----------
800 5000
这个就可以，日了狗了。
凭什么啊 只能理解成语法规定吧显示工资高于平均工资的员工信息
自己第一次写的 where字句中是不能有聚合函数的 大哥
21:06:43 SQL> select * from emp where sal>avg(sal);
select * from emp where sal>avg(sal)
ORA-00934: 此处不允许使用分组函数
哈哈，在where 字句中是不能这样写的，大哥，不要再犯了
正确答案如下:
卧槽,为知笔记真的很垃圾,我就不应该抛弃印象笔记,添加代码这么重要的功能,还这么经常抽风,
再加一个条件，并且一起显示平均工资。留做以后的思考问题吧
就是这个,我一直想知道怎么做.不能直接加一列 avg(sal)
其实也很简单:
SQL> select ename,sal, avgsal
2 from emp ,(select avg(sal) avgsal from emp) b
3 where emp.sal>avgsal;
ENAME SAL AVGSAL
---------- --------- ----------
JONES 2975.00 1866.07142
BLAKE 2850.00 1866.07142
CLARK 2450.00 1866.07142
KING 5000.00 1866.07142
FORD 3000.00 1866.07142group by 用于对查询的结果分组统计
having 字句用于限制分组显示的结果(having 就是对group by再进行一次处理而已)
如何显示每个部门的平均工资和最高工资。
21:15:13 SQL> select deptno,avg(sal),max(sal) from emp group by deptno;
DEPTNO AVG(SAL) MAX(SAL)
------ ---------- ----------
30 1566.66666 2850
20 2175 3000
10 2916.66666 5000函数结合起来一起用的时候，那么它必须出现在group by 后面
显示每个部门最高工资和最低工资的员工
select max(sal),min(sal) ,deptno from emp group by deptno order by deptno;显示平均工资低于2000的部门号和它的平均工资
分组函数只能出现在select ,having, order by字句中.
select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000 order by avg(sal);
AVG(SAL) MAX(SAL) DEPTNO
---------- ---------- ------
2175 3000 20
2916.66666 5000 10如果在select语句中同时包含有group by ,having order by ,那么顺序是group by > having > order by
这个就是sql的执行顺序而已,没有什么好说的.
显示SALES部门位置和其员工的姓名.
select ename,loc from emp,dept where emp.deptno=dept.deptno and dept.dname='SALES';
ENAME LOC
---------- -------------
WARD CHICAGO
TURNER CHICAGO
ALLEN CHICAGO
JAMES CHICAGO
BLAKE CHICAGO
MARTIN CHICAGO笛卡尔积
如果有三张表，至少要有两个条件来进行三张表的查询（前提是你条件还不能写错）
显示部门号为10的部门名称，员工名和工资
where emp.deptno=dept.deptno and dept.deptno=10; 最好是把 dept.deptno=10 放在后面,因为oracle先执行后面的语句
SQL> select dept.dname ,ename ,sal from emp,dept where emp.deptno=dept.deptno and dept.deptno=10;
DNAME ENAME SAL
-------------- ---------- ---------
ACCOUNTING CLARK 2450.00
ACCOUNTING KING 5000.00
ACCOUNTING MILLER 1300.00
一般是给表取个表名SQL> select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999SQL> select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;
ENAME SAL GRADE
---------- --------- ----------
SMITH 800.00 1
JAMES 950.00 1
ADAMS 1100.00 1
WARD 1250.00 2
MARTIN 1250.00 2
MILLER 1300.00 2
TURNER 1500.00 3
ALLEN 1600.00 3
CLARK 2450.00 4
BLAKE 2850.00 4
JONES 2975.00 4
SCOTT 3000.00 4
FORD 3000.00 4
KING 5000.00 5上面的答案不严谨,因为有时候emp里面有个人的工资巨低,低到连等级1 都达不到,或者说太高,超过了等级五,所以我觉得最好的方法还是使用左连接
保证每个用户都可以出现.即使他的工资等级有点尴尬(卧槽,我服,我都忘记了还有这个了)
select empno,ename,sal,grade from emp left join salgrade on sal>=losal and sal<=hisal;
或者:
select grade ,ename ,sal from emp left join salgrade on sal between losal and hisal;
EMPNO ENAME SAL GRADE
----- ---------- --------- ----------
7788 SCOTT 100.00 //这个人的工资低于700.已经低于等级1了.当然其实在实际上,应该保证员工的工资高于这个最低值700
7369 SMITH 800.00 1
7900 JAMES 950.00 1
7876 ADAMS 1100.00 1
7521 WARD 1250.00 2
7654 MARTIN 1250.00 2
7934 MILLER 1300.00 2
7844 TURNER 1500.00 3
7499 ALLEN 1600.00 3
7782 CLARK 2450.00 4
7698 BLAKE 2850.00 4
7566 JONES 2975.00 4
7902 FORD 3000.00 4
7839 KING 5000.00 5
显示雇员名 雇员工资 以及所在的的部门的名字，并按照部门排序(应该是按照部门号来进行排序的)
SQL> select ename ,sal, dname from emp,dept where emp.deptno=dept.deptno order by dept.deptno;
ENAME SAL DNAME
---------- --------- --------------
CLARK 2450.00 ACCOUNTING
KING 5000.00 ACCOUNTING
MILLER 1300.00 ACCOUNTING
JONES 2975.00 RESEARCH
FORD 3000.00 RESEARCH
ADAMS 1100.00 RESEARCH
SMITH 800.00 RESEARCH
SCOTT 3000.00 RESEARCH
WARD 1250.00 SALES
TURNER 1500.00 SALES
ALLEN 1600.00 SALES
JAMES 950.00 SALES
BLAKE 2850.00 SALES
MARTIN 1250.00 SALES自连接
显示某个员工的姓名和ta的上级领导的姓名。
SQL> select distinct a1.ename,a2.ename from emp a1,emp a2 where a1.mgr=a2.empno;
ENAME ENAME
---------- ----------
BLAKE KING
TURNER BLAKE
WARD BLAKE
FORD JONES
SMITH FORD
MARTIN BLAKE
ADAMS SCOTT
SCOTT JONES
JONES KING
ALLEN BLAKE
JAMES BLAKE
MILLER CLARK但是这里有个问题 King没有显示出来，虽然他自己本身没有上级、？？
所以这里换一个方法,为知笔记为什么更新之后插入代码会出错!!! 然后都不能删除掉这个代码插入的这个黑黑的模块.还不如使用印象笔记
SQL> select a1.ename ,a2.ename boss from emp a1 left join emp a2 on a1.mgr=a2.empno; //有时候感觉left join 会比我们自己写的那个 from tableA ,tableB 这样子更严谨 除非是是tableA 和 tableB 之间本身就有约束关系吧
ENAME BOSS
---------- ----------
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KING
JONES KING
SMITH FORD
KING
子查询：
单行子查询
如何显示与SMITH同一部门的所有员工
SQL> select * from emp where deptno =(select deptno from emp where ename='SMITH');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7902 FORD ANALYST 7566 1981/12/3 3000.00 20执行在执行sql的时候，从右到左
多行子查询 子查询返回多行数据
查询和部门号10工作的相同的雇员的姓名，岗位，工资和部门号(就是查询工作跟部门10号工作相同的一些工作)
SQL> select ename ,job ,sal ,deptno from emp where job in(select job from emp where deptno=10);
ENAME JOB SAL DEPTNO
---------- --------- --------- ------
CLARK MANAGER 2450.00 10
BLAKE MANAGER 2850.00 30
JONES MANAGER 2975.00 20
KING PRESIDENT 5000.00 10
MILLER CLERK 1300.00 10
JAMES CLERK 950.00 30
ADAMS CLERK 1100.00 20
SMITH CLERK 800.00 20
更好的写法，加上distinct
SQL> select ename ,job ,sal ,deptno from emp where job in(select distinct job from emp where deptno=10);
all --> max any-->min
多列子查询
如何查询与SMITH的部门和岗位完全相同的左右雇员
SQL> select * from emp where (deptno,job)= (select deptno ,job from emp where ename='SMITH'); //列和列之间要对应起来，顺序不能乱
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20难题;(其实也挺简单的)
如何显示高于自己部门平均工资的员工的信息
SQL> select a1.ename,a1.deptno from emp a1 ,(select deptno,avg(sal) sal from emp group by deptno) a2 where a1.deptno=a2.deptno and a1.sal>a2.sal order by deptno;
ENAME DEPTNO
---------- ------
KING 10
JONES 20
SCOTT 20
FORD 20
ALLEN 30
BLAKE 30
6 rows selected其实简单,而且一般来说应该先将a1.deptno=a2.deptno 放在最后面因为最后面的先开始执行. 其它的应该没有什么问题
网络处理 数据库 代码的优化 ----程序猿
给表取别名的时候不需要as 直接 空格 别名 Oracle
Oracle分页有三种：
1 rownum rownum是一个伪列 ,没有什么实际的用处
SQL> select a1.*,rownum rn from (select * from emp) a1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
7369 SMITH CLERK 7902 1980/12/17 800.00 20 1
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 2
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 3
7566 JONES MANAGER 7839 1981/4/2 2975.00 20 4
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 5
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 6
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 7
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 8
7839 KING PRESIDENT 1981/11/17 5000.00 10 9
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 10
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 11
7900 JAMES CLERK 7698 1981/12/3 950.00 30 12
7902 FORD ANALYST 7566 1981/12/3 3000.00 20 13
7934 MILLER CLERK 7782 1982/1/23 1300.00 10 14
14 rows selected2 比如 6--10条
先前10条
SQL> select a1.*,rownum rn from (select * from emp) a1 where rownum<=10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
7369 SMITH CLERK 7902 1980/12/17 800.00 20 1
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 2
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 3
7566 JONES MANAGER 7839 1981/4/2 2975.00 20 4
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 5
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 6
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 7
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 8
7839 KING PRESIDENT 1981/11/17 5000.00 10 9
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 10
10 rows selected再筛选一次：
SQL> select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where rn>=6 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 6
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 7
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 8
7839 KING PRESIDENT 1981/11/17 5000.00 10 9
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 10上面查询是查全部的数据，但是有时候我们可能只需要其中的一小部列，那么只需要改最里面的那个就可以了
select * from (select a1.*,rownum rn from (select 具体的列 from emp) a1 where rownum<=10) where rn>=6 ;排序也只需要对最里层进行修改
select * from (select a1.*,rownum rn from (select * from emp order by sal) a1 where rownum<=10) where rn>=6 ;
用查询结果创建新表
SQL> create table mytable324 (id,name ,sal) as select empno ,ename,sal from emp;
Table created
SQL> select * from mytable324;
ID NAME SAL
----- ---------- ---------
7369 SMITH 800.00
7499 ALLEN 1600.00
7521 WARD 1250.00
7566 JONES 2975.00
7654 MARTIN 1250.00
7698 BLAKE 2850.00
7782 CLARK 2450.00
7788 SCOTT 3000.00
7839 KING 5000.00
7844 TURNER 1500.00
7876 ADAMS 1100.00
7900 JAMES 950.00
7902 FORD 3000.00
7934 MILLER 1300.00
14 rows selectedOracle中的合并查询 union unionall 等等，这些效率很高
SQL> insert into copy_emp as select * from copy_emp;
insert into copy_emp as select * from copy_emp
ORA-00926: 缺失 VALUES 关键字如果用已有的数据批量插入的话,不加as ,加了关键字as就会报错
但是创建表的时候,并且写入数据的时候必须加关键字as
select distinct <select_list>
from <left_table>
<join_type> join <right_type>
on <join_condition>
group by <group_by_list>
having <having_condition>
order by <order_by_condition>
limit <limit_number>
希望员工SCOTT
的岗位,工资 ,补助和SMITH
员工一样
一次性更新多个字段
update emp set (job ,sal ,comm)=(select job ,sal ,comm from emp where ename ='SMITH') where ename ='SCOTT';