SQL练习

--创建表数据
CREATE TABLE PEOPLE
(
  PEOPLEID NUMBER(38, 0)
, PEOPLENAME VARCHAR2(20)
, PEOPLEAGE NUMBER(38, 0)
);

create table people(
peopleId number(38,0),
peopleName varchar2(64),
peopleAge number(38,0));

--删除数据
truncate table people1;

delete from table people1;

drop table people1;

--truncate会删除表回滚段的数据，释放空间，但不会删除表的结构，drop会删除表的数据，甚至表结构（所依赖的约束，触发器，索引），依赖于该表的存储过程和函数将会保留，字段变成invalid状态。delete删除表的数据，可以回滚。

--插入数据
insert into people values (1001,'zhuol',20);
insert into people values (1002,'abin',22);
insert into people values (1003,'zzl',26);
insert into people values (1003,'gege',32);

select * from people;

--查询所有的年龄的最大，最小，平均，总和的值。
select max(peopleage),min(peopleage),avg(peopleage),sum(peopleage) from people;

--更新数据：
update people set peopleage=22 where peopleid=1003;

--查询重复记录
select distinct * from people where peopleage in (select peopleage from people group by peopleage having count(peopleage)>1);

--对年龄进行分组
select peopleage from people group by peopleage;

--查询重复的年龄
select peopleage from people group by peopleage having count(peopleage)>1;

--查询重复的人的信息，单个字段重复的情况：
select distinct * from people where peopleId in (select peopleId from people group by peopleId having COUNT(peopleId)>1);

--查询重复的peopleid记录

select * from people where peopleId in (select peopleId from people group by peopleId having COUNT(peopleId)>1);

--打印重复的记录条数:查询某个重复的人的总数,先查询重复信息，再计算重复的条数
select count(peopleid),peopleid from people group by peopleid  having count(peopleid)>1;

--查询peopleid是1003的记录个数，group by 分组，having 限制分组显示结果:

having 中的条件一般对集合函数进行比较，除此之外一般的条件应该写到where子句

中。如：

效率底：（having 只对检索出所有纪录后对结果集进行过滤）

select count(*),peopleid from people group by peopleid having peopleid=1001;

效率高：

select count(*),peopleid from people where peopleid=1001 group by peopleid;

--分组查询最大的年龄人的id

select peopleid,max(peopleage) from people group by peopleid;

--分组函数：max,min,avg,sum,count---如果列中有一个分组函数，其他的都必须是分组函数,分组字段需要在查询列表里。
select max(peopleage),min(peopleage),avg(peopleage),peopleid from people group by  peopleid order by max(peopleage);

--查询年纪最大的人的号码和名称
select peopleid,peoplename,peopleage from people where peopleage in (select max(peopleage) from people);

--修改表alter table 增加，删除，修改表列的方法：
alter table people add (address varchar2(64));
alter table people modify (address varchar2(60));
alter table people drop column address;
--重命名表
rename pe to people;
select * from people where address is null;
--查看表的结构
desc people;
--创建保存点,一旦commit之后就不能回退了。
savepoint a;
update people set address='湖南' where peopleid=1001;
rollback to a;  --则会恢复到原保存点

---分页查找
select t2.* from
(select t1.*,rownum rn from
 (select * from people) t1
 where rownum<=3) t2 where rn>=2;
--分页查找
select t1.* from (select people.* ,rownum rn from people where rownum <=3) t1 where rn >=2;
--分页查找
select t1.* from (select people.*,rownum rn from people) t1 where rn>=2 and rn<=3;

select * from people;

--drop table people;

--保存事务
savepoint a;
update people set peoplename='gege' where peoplename='zzl';

--查询重复多余的记录,多个字段重复的情况：
select a.* from people a where (a.peopleid,a.peoplename) in (select peopleid,peoplename from people group by peopleid,peoplename having count(*)>1)
and rowid not in (select min(rowid) from people group by peopleid,peoplename having count(*)>1);

--查询重复多余的一个纪录，高效：用表的自连接：

select * from people a where a.rowid > (select min(b.rowid) from people b where b.peopleage=a.peopleage);

--删除重复多个字段多余的记录：
delete from people a where (a.peopleid,a.peoplename) in (select peopleid,peoplename from people group by peopleid,peoplename having count(*)>1)
and rowid not in (select min(rowid) from people group by peopleid,peoplename having count(*)>1);

--删除重复的一个字段重复多余纪录：(高效：表的自连接)

delete from people a where a.rowid > (select min(b.rowid) from people b where b.peopleage=a.peopleage);

--rowid 标记记录的物理位置，是不变的，rownum记录行号，是变化的。
select rowid,people.* from people;

--回滚事务
rollback to a;

--自我复制：
insert into people (peopleid,peoplename,peopleage,address) (select peopleid,peoplename,peopleage,address from people);

--降序输出，升序是asc
select * from people order by peopleid desc;

--like的用法：
select * from people where peoplename like 'g%';

--存储过程
create procedure haha
is
begin
    insert into people values(1002,'zzm',42,'深圳');
end;
--存储过程的调用
execute haha

drop procedure haha;

多表查询的效率比较：

--in低效些
select instanceid,resourceid,instancename,hostname from tics_pso_instance where instanceid in (select instanceid from tinstance_nic where ipaddress='10.71.195.205');
--exists更高效
select t.instanceid,t.resourceid,t.instancename,t.hostname from tics_pso_instance t where exists (select * from tinstance_nic n where t.instanceid=n.instanceid and ipaddress='10.71.195.205');
--表连接 更更高效
select t.instanceid,t.resourceid,t.instancename,t.hostname from tics_pso_instance t,tinstance_nic n where t.instanceid=n.instanceid and ipaddress='10.71.195.205';

--表的备份
su - oracle -c "expuserid=$oracle_user/$oracle_pwd@$oracle_sidtables=TDATASTORE_HOST,TDATASTORE,TINSTANCE file=/home/oracle/$(basename $0).dmp indexes=y rows=y compress=y grants=y direct=y buffer=409600 log=/home/oracle/$(basename $0).log"

--表的恢复

su - oracle -c "impuserid=$oracle_user/$oracle_pwd@$oracle_sidfile=/home/oracle/$(basename $0).dmp fromuser=$oracle_user touser=$oracle_user ignore=y buffer=409600 commit=y log=/home/oracle/$(basename $0).log"