oracle10g- oracle数据库查询 从已有的数据表中创建新表

主要是讲查询语句

第一个

select pub,books_name,sum(price*qty) from books where price>30 group by pub

有误

没有分组的column books_name;

改成

select pub,books_name,sum(price*qty) from books where price>30 group by pub;

第二个

select pub,sum(price*qty) from books where sum(price*qty) >200 group by pub;

提示在where后面不能用sum(price*qty) >20

改为

select pub,sum(price*qty) from books group by pub having sum(price*qty)>200;

或

select pub,sum(price*qty) s from books group by pub having s>200;

第三个

查找重复记录

select name ,count(*) from t1 group by name having count(*)>1;

第四个

模糊查询

select * from t1 where name like 'a_';

表示 以a开头两个字符长度的 同事 like 'a__'

select * from t1 where name like '%a%';

select * from t1 where name like 'a%';

连接查询

create synonym d for dept;

create synonym e for employee;

内连接

select eid 编号,ename 性别,d.name 所在部门 from d,e where d.id=e.id;

select eid 编号,ename 性别,d.name 所在部门 from e join d on e.id=d.id;

左连接

select eid 编号,ename 性别,d.name 所在部门 from e ,d where e.id=d.id(+);

右连接

select eid 编号,ename 性别,d.name 所在部门 from e ,d where e.id(+)=d.id;

子查询

无关子查询 主查询与子查询没有where 关联

select * from e where id in (select id from d);

相关子查询

select * from e where id in (select id from d where d.id=e.id and id='03');

select * from e where exists (select id from d);

select * from e where exists (select id from d where e.id =d.id);

select * from e where not exits (select id from d where e.id=d.id);

表连接 的效率会比子查询的效率好些

union

select id,name from t1

union //不会合并相同的id

select id,name from t2

 如果如下写

select id from t1

union 

select id from t2

这样如果有相同的id就会合并

intersect用于 返回两个语句里面都出现的行

select id from t1

intersect

select id from t2

插入

insert into t1 values(3,'nametst');insert into t1 (id) values(3);

insert into t1(id,name) select id ,name from t2;插入多条数据;

从已经有的数据表创建新表

create table t3 as select * from dept;

create table t3 as (select * from dept);

create table t3 as select deptno,dname from dept;