Oracle基础知识及例子.docVIP

数据库类型： 小型：Access foxbase 中型：MySql SQLServer 大型：DB2 Oracle Sybase Oca Oracle初级认证 Ocp Oracle 专家级认证 Ocm Oracle 大师级认证 Oracle创始人：劳伦斯 埃里森 1970做数据库起家 Oracle9i 2002年 Oracle10g 2003年 Oracle11g 2007年 Oracle管理工具：sql plus 账号：user1-user19 口令同上 recruit sql语句： DDL：Data Definition Language create drop alter desc / describe DML:Data Manipulation Language Insert delete update 更新 select 命令行：isqlplusctl start 启动isqlplus服务 select 语句 select * from externalcandidate; select vfirstname,vlastname,ccity from externalcandidate; select vfirstname|| ||vlastname as name , ccity as city from externalcandidate; 查看表中唯一的值 SELECT DISTINCT column_name FROM tablename Where字句 select * from college where cstate=California; 选择年龄比23-11月-67 desc externalcandidate; select * from externalcandidate where dbirthdate23-11月-67; select sysdate from dual; select * from dual; select 99*5 from dual; 模糊查询：like select cnewspapername paper,vcontactperson person,cphone phone from newspaper where vcontactperson like %Jackson%; %表示任意字符数 _表示一个字符数 IS NULL关键字: 用来检查表中是否包含NULL值的空白列或行. 语法 SELECT select_list FROM tablename WHERE column_name IS NULL; desc position; select * from position; select (NBUDGETEDSTRENGTH -NCURRENTSTRENGTH )need from position where cpositioncode=0001; select * from position order by cpositioncode desc;（降序） 单行函数： select initcap(jack) from dual; select upper(vfirstname) from externalcandidate; select lower(vfirstname) from externalcandidate; select ltrim(helloworld,hello) from dual; select rtrim(helloworld,hello) from dual; ltrim(char,set) :从char左侧开始比较。如果在set中出现，则删除，否则就停止。 select concat(hello,worldssss) from dual; select substr(helloworld,2,4) from dual; select instr(helloworld,e) from dual; select lpad(1000,7,$) from dual; select rpad(1000,7,$) from dual; select length(helloworld) from dual; select vfirstname,vlastname,round(ntestscore,1) from externalcandidate where nTestscore70; select round(123.456,-2)from dual; select abs(-9) from dual; select ceil(12.3) from dual; select floor(12.3) from dual; s