ITPub博客

九 创建和管理表1.数据库对象表 视图 序列 索引 同义词 DDL 数据定义语言 如 创建，修改，删除DCL 数据控制语言2.创建表create table dept (deptno number(2) default sysdate, dname varchar2(14), loc varchar2(13));describe dept 确定表的创建3.用户表：是由用户创建和维护的表的集合 数据字典：是由Oracle Server创建和维护的表的集合查看用户拥有的表的名称：select table_name from user_tables查看用户拥有的独特的对象类型：select distinct object_type from user_objects查看用户拥有的表 视图 同义词和序列：select *

交叉联结 cross joinselect last_name,dep_name from employees,departments;等同于select last_name,dep_name from employees cross join departments;自然联结 naturl joinselect dep_id,dep_name,location_id,city from dep natural join locations;等同于select dep_id,dep_name,location_id,city from dep,locations where dep.location_id = locations.location_id;通过on子句联结select t.name,t.id,s.res_id,s.path,s.image from inch_cinema_res t join inch_cinema_partres s on (t.id =s.res_id)left outer join 左联 没匹配时

数字函数round(45.926,2) 45.93 四舍五入到小数点2位trunc(45.926,2) 45.92 截断到小数点2位、mod(1600,300) 100 返回除法运算的余数 常用于确定值为奇数还是偶数练习 select round(445.923,2) ,round(445.923,1) ,round(445.923,-1),round(455.923,-2) from dual处理日期sysdate select sysdate from dual 2009-3-31 22:18:31months_between 2个日期之前的月数 add_months 加月数 last_day 月的最后天 select id,months_between(sysdate,up_time) MOT,add_months(up_time,5) Addd, last_day(up_time) Lastt from inch_cinema_res转换函数

一 基本查询1.oracle9i关系数据库管理系统（RDBMS）加运算符SELECT last_name,salary,12*salary+100 FROM employees;使用列别名SELECT last_name AS name, comission_pct comm FROM employees; SELECT last_name "Name",salary*12 "Annual Salary"连接运算符select username || sysuser_id as "usre" from inch_cms_sysuser 文字字符串select username || ' -- id is -- ' ||sysuser_id as "usre" from inch_cms_sysuser 消除重复行select distinct fathertype_id from inch_cms_type 2.iSQL*P