--**创建表T，插入数据后进行查询
Create Table t(Id Int,Name Varchar2(20),riqi date);
Insert Into t Values(1,'a',Sysdate);
Insert Into t Values(2,'a',Sysdate);
Insert Into t Values(1,'b',Sysdate);
Insert Into t Values(2,'b',Sysdate);
Select Distinct Id,Name From t ;  --过滤重复
Select Unique Id From t ;    --distinct = unique
Select Count(*) From t;      --返回列
Select Count(Distinct(Id)) From t;   --返回不同id的个数
Select Id,Name,upper(Name) From t;   --转成大写


/*
nosql
Not Only Sql


Cube
雪花状
星形状
*/


--不等于
Select * From t Where Id != 1;
Select * From t Where Id ^= 1;
Select * From t Where Id <> 1;


--in
Select * From t Where Id In (1,2);
Select * From t Where Id In(Select Distinct Id From t);


Select * From t Where Id = 1 Or Id = 2;


--使用执行统计信息语句，查看in和or的执行过程
Set autotrace On
Set linesize 200
Select * From t Where Id = 1 Or Id = 2;     --效率低于in，oracle执行过程中可能转换成in
Select * From t Where Id In (1,2);


--like模糊查询 _代替单字符，%代替多字符
Insert Into t Values(3,'asdicneeee',Sysdate);
Select * From t Where Name Like '%eee';


--强制走索引
Select /*+ index(tt idx_tt_name) */ * From tt Where Name Like 'a%';  --hint 提示


--between
Select * From t Where Id >= 1 And Id <=2;  --一样
Select * From t Where Id Between 1 And 2;  --一样


--order by排序
Select * From t Order By Id;     --null是无穷大的
Select * From t Order By Id asc;     --升序
Select * From t Order By Id Desc;    --降序
Select * From t Order By 2;          --按列排序


/*研究排序的资源耗费
create table t1 tablespace users from select * from dba_objects;
insert into t1 from select * from t1; -- 将自身数据插入N次 数据量达到8W左右
select * from t1 order by object_name;   --查询排序
select * from v$sql_workarea_active;     --查看work的 sql_id ,work_area_size 12M,tempseg_size 8M  *当sga足够大时，可能不使用表空间tempseg_size
select * from v$sql where sql_id = 上面的sql_id;      --
sga:system global area    --系统全局区
pga:private global area   --私有全局区