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;

--插入数据
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);

--查询某个重复的人的总数,先查询重复信息，再计算重复的条数

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 限制分组显示结果:

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

--分组查询最大的年龄人的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);
--删除重复多余的记录：
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);
--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%';