oracle使用二(sql查询)

Oracle 中的伪列 

rowid,rownum,id,name,address,email from mytable 

更改 oracle 中表的信息:

以下代码演示了修改mytable表中的address列的长度：

    Alter table mytable modify (address varchar2(50))

以下代码演示了向mytable 表中新增加一列:

Alter table mytable add (phone varchar2(20))

以下代码演示了删除 mytable 表中的 phone 列

    Alter table mytable drop column phone

以下代码演示了truncate table 命令删除表中所有的行

Truncate table mytable;

以下代码演示了列出了mytable表的各列及其数据类型

   Desc mytable;

oracle 中利用现有的表创建新表：

create table newmytable as select * from mytable ;

以下代利用现有的表创建新表,没有任何记录.

create table newmytable  as select * from mytable where 1=2;

日期和时间的处理 ：

  以下代码演示了默认的时间格式：

insert into MYTABLE (ID, NAME, ADDRESS, EMAIL,REGDATE)

values (5, ''rose'', ''tianjin'', ''[e-mail]'',''12-5月-05'')

使用DATE 关键字向数据库提供一个日期文本字符串，此日期必须使用yyyy-mm-dd格式，以下代码演示了 date关键字;

insert into MYTABLE (ID, NAME, ADDRESS, EMAIL,REGDATE)

values (6, ''rose'', ''tianjin'', ''[e-mail]'', date ''2007-10-12'')

下列代码演示了通过 to_date() 函数返回的值添加到数据库中

insert into MYTABLE 

(ID, NAME, ADDRESS, EMAIL,REGDATE) values (6, ''rose'', ''tianjin'', ''[e-mail]'', to_date(''2007-10-23 12:36:58'',''yyyy-mm-dd hh24:mi:ss''))

To_char() 将时间转换为字符串

下列语句使用 to_char() 函数将 customers 中的 dob 列转换为字符串格式，格式为(yyyy-mm-dd hh:mm:ss)

selectid,name,address,email, to_char(regdate,''yyyy-mm-dd hh24:mi:ss'') from mytable

Last_day() 返回包含月的最后一天

以下代码返回了customers 表中的 dob 中的所在月的最后一天时间：

select last_day(regdate) as lastday from mytable

ADD_MONTHS 返回指定的日期加上指定的月数后的日期值得

select add_months(sysdate,3) from dual

ROUND 返回日期值，四舍五入为格式模型指定的单位。日期默认为最近的那一天。

  select round(to_date(''2007-6-30'',''yyyy-mm-dd''),''year'') from dual

 如果指定年份那么以 7月1号后作为下一年的最近日期。

  指定月:

  Select round(date’2007-10-14’,’month’),

 

           Round(date’2007-10-16’,’month’)

   From dual;

NEXT_DAY 此函数返回指定的下个星期几的日期

 Select next_day(sysdate,’星期二’) from  dual;

TRUNC  指定日期截断为由格式模型指定的单位的日期（只舍不入

）

Select trunc(sysdate,’year’) from dual

EXTRACT 获取日期时间类型中的特定部分

 Select extract(year from sysdate) from dual

 Oracle 插入来自其他表中的数据：

    insert into newEmployees3 select * from employees

Oracle中 savepoint , rollback , commit 使用

   insert into mytable values(7,''dick'',''tianjin'',''[e-mail]'',date''2007-10-10'');

savepoint p1;

    update mytable set name=''abck'' where id=1;

savepoint p2;

    insert into mytable values(8,''qing'',''tianjin'',''[e-mail]'',date''2008-10-10'');

savepoint p3;    

update mytable set name=''ddddd'' where id=3;

savepoint p4;

Oracle 中的 union all 操作符,查询返回所检索出的所有行，包括重复的行：下面的例子使用了 union all。注意products 和 more_products 中的所有的行都被检索出来，包括重复的行

   select product_id,product_type_id,name from products union all select prd_id,prd_type_id,name from more_products

Oracle 中的 union操作符号返回查询检索出的非重复的列：

select product_id,product_type_id,name from products union  select prd_id,prd_type_id,name from more_products

oracle 中的 intersect 操作符返回两个查询所检索出的共有的行：

select product_id,product_type_id,name from products intersect   select prd_id,prd_type_id,name from more_products

oracle 中的 minuse 操作符返回将第二个查询检索出的行从第一个查询检索出的行中减去之后剩余的行

select product_id,product_type_id,name from products minus  select prd_id,prd_type_id,name from more_products

Oracle 中的连接操作符号

select (''商品名称为:''||name) info from products

oracle 中的字符串转换函数：

select name,translate(name,''spring'',''SPRING'') from products

LPAD 函数由三个参数 ，第一个参数是字符串，该字符串左边需要填充，第二个是数字，它表示返回值的总长度，第三个是字符串，需要时用它进行左填充。

     Select lpad(‘function’,10,’=’) from dual;

Oracle 中的 decode()函数返回了两个值是否相等，如果相等的话返回指定的值，否则返回默认的值：

以下代码演示了 decode 使用: Select decode(1,1,2,3) from dual;

 

下面代码对 more_products 中的 available 列进行比较。如果等于Y返回字符串’产品可以用’, 如果等于N 返回字符串’ 产品不可以用’

select prd_id,available,decode(available,''Y'',''产品可以用'',''N'',''产品不可以用'') from more_products

下列代码演示了向 decode() 传递多个搜索和结果参数：

   Select product_id,product_type_id,decode(product_type_id,1,

’书本’,2,’ 视频’,3,’DVD’,4,’CD’,’ 杂志’) from products;

Oracle 中转换空值的函数

NVL 使用

select id,name ,NVL(to_char(regdate,''yyyy-mm-dd''),sysdate) from mytable

NVL2  使用 (如果第一个参数不为空返回第二个参数,否则返回第三个参数)

SELECT id,name,email,NVL2(id,name,email) 

 from mytable 

NULLIF 使用(判断两个参数是否相等,如果相等返回 null ,否则返回第一个参数)

select id,nullif(name,email) from mytable

使用 rollup 子句为每个分组返回一条小计，并为全部分组返回总计：

Select  division_id,sum(salary) from employees2 Group by division_id

向 rollup 传一列 

   Select  division_id,sum(salary) from employees2 Group by rollup(division_id)

向 rollup 传多列

Select  division_id,job_id,sum(salary) from employees2 Group by rollup(division_id,job_id)

使用 cube 子句对 group by 进行扩展，返回 cube中所有列组合的小计，

同时在最后显示总计信息

Select division_id,job_id,sum(salary) from employees2

 Group by cube(division_id,job_id)

使用分析函数：

以下代码演示了如何对所有员工的工资进行排名，即使工资相同其排名也不相同，

select employee_id,division_id,job_id,salary,row_number()  over( order by salary desc)  from employees2 

以下代码演示了如何对所有员工的工资按职务进行排名，即使工资相同其排名也不相同

select employee_id,division_id,job_id,salary,row_number()  over(partition by job_id order by salary desc)  from employees2 

以下代码演示如何根据员工的公司ID进行排位。

select employee_id,division_id,job_id,salary,rank()  over(partition by division_id order by salary desc)  from employees2

以下代码演示了选择所在公司ID 和职务ID进行排位，相等的工资排位相同。

select d.dname,e.ename,e.sal,dense_rank() over(partition by e.deptno order by e.sal desc) as denserank from emp e,dept d where e.deptno = d.deptno

Oracle 中的层次化查询:

1. 使用 connect by 和 start with 子句

select employee_id, manager_id, empname, title, salary from more_employees start with employee_id=1  connect by prior employee_id=manager_id

 

说明：

start with employee_id=1   表示查询从员工 1 开始

connect by prior 当编写层次化查询的时必须定义 connect by prior 

employee_id=manager_id  表示父节点的employee_id和子节点manager_id 之间存在关系，也就是说子节点的manger_id 指向父节点的employee_id

2. 在 oracle 中使用 level 伪列

下面的查询说明了如何使用伪列 level 显示节点在树中的层次

Select level, employee_id, manager_id, empname, title, salary from more_employees start with employee_id=1  connect by prior employee_id=manager_id  order by level

说明：level 是一个伪列，代表位于树的第几层，对根节点来说，level 返回1，根节点的子节点返回2，依次类推。

3. 格式化层次化查询的结果

Select level,lpad(''  '',2*level-1) || empname as employeename from more_employees start with employee_id =1 connect by prior employee_id = manager_id