oracle常用语句，带详细的说明

/*作用：创建表空间 用来保存用户的操作   说明：oracleDemo文件夹必需存在  默认的大小10M 超过10M时会以1M为增长大小  最大不能超过100M*/

create tablespace oracleDemoTS

datafile 'd:\oracleDemo\demoTableSpace.ora'

size 10M autoextend on next 1M  maxsize 100M;

/*作用：删除表空间以及所有内容和文件*/
drop tablespace  oracleDemoTS

including contents  and datafiles;

/*作用：创建临时表空间 主要用来做查询和存放一些缓冲区数据 如事务*/

create temporary tablespace oracleDemoTTS

tempfile 'd:\oracleDemo\oracleTemporaryTableSpace.ora'

size 5M   autoextend  on next 1M  maxsize 50M;

/*作用:删除临时表空间以及所有内容和文件*/
drop tablespace oracleDemoTTS

including  contents and datafiles;

/*作用：创建用户并指定默认表空间  说明：paul 为密码  */

create user christines identified by paul

default tablespace oracleDemoTS

temporary tablespace oracleDemoTTS ;

/*作用：删除用户 cascade 并删除christines创建的所有用户*/

drop user christines cascade;

/*作用：授与用户  connect  resource权限*/

grant connect,resource to christines;

/*作用：授与用户session对话权限*/

grant create session to christines;

/*作用：查看登录用户建立的表*/

select * from tabs;

/*作用：dual是一个虚拟表，用来构成select的语法规则，只有一行一列*/
select * from dual;

select sysdate from dual;

/*作用：创建部门表  deptId 部门编号，deptName 部门名称，createTime 部门创建时间   字段类型说明请看《oracle常用的基本类型》  约束说明请看《oracle约束的基本语法以及详细的讲解》*/
create table Dept
(
       deptId integer not null,
       deptName nvarchar2(20) not null,
       createTime date default(sysdate)  not null,
       constraint pk_Dept primary key (deptId),
       constraint unique_Dept_deptName unique (deptName) 

);

/*作用：创建自动增长序列  说明： increment by 1  增长的步长是1 ，    start with 1  从1开始增长*/

create sequence seqDeptId increment by 1 start with 1;

/*作用：删除表*/

drop table Dept;

/*作用：删除自动增长序列*/

drop sequence seqDeptId;

/*作用：插入数据  说明：seqDeptId.NextVal  取自动增长序列seqDeptId的下一个值  createTime为默认时间*/                                          

insert into dept(deptId,deptName) values (seqDeptId.NextVal,'development'); 

insert into dept(deptId,deptName,createTime) values (seqDeptId.NextVal,'development',to_date(sysdate,'yyyy-mm-dd')); 

select  seqDeptId.NextVal  from dual;

/*作用：seqDeptid.Currval 查看seqDeptid的自动增长值已为多少*/

select seqDeptid.Currval from dual;

/*作用：转化字符为日期数据  说明：to_date(字符串类型时间，日期格式)*/

select to_date('1991-10-03 10:10:03','yyyy-mm-dd hh24:mi:ss') from dual;

/*作用：插入数据 说明：手动的添加createTime值*/

insert into dept(deptId,deptName,createTime) values (seqDeptId.NextVal,'Technology',to_date(sysdate,'yyyy-mm-dd'));

/*作用：删除dept中的所有记录*/

delete from dept;

/*作用：查询dept中的所有记录*/

select * from dept;

/*作用:创建员工表  说明：empId 员工编号，empName 员工姓名，sex 员工性别 ，birthday 员工生日，salary 员工薪水，deptI 员工所在部门Id*/
create table Employee
(
    empId integer primary key,
    empName nvarchar2(20) not null,
    sex nchar(6) check(sex in ('Male','female')),
    birthday date,
    salary number(18,2),
    deptId integer,
    constraint fk_Employee_dept_deptId foreign key (deptId) references Dept

);

create sequence seqEmployee increment by 1 start with 1;

drop sequence seqEmployee;

drop table Employee;

insert into Employee(empId,empName,sex,birthday,salary,deptId) 

values(seqEmployee.NextVal,'christines','female',to_date('1991-10-03','yyyy--mm-dd'),3.5,1);

insert into Employee(empId,empName,sex,birthday,salary,deptId) 

values(seqEmployee.NextVal,'kevin','Male',to_date('1985-10-03','yyyy--mm-dd'),9.5,1);

insert into Employee(empId,empName,sex,birthday,salary,deptId) 

values(seqEmployee.NextVal,'levin','Male',to_date('1924-10-03','yyyy--mm-dd'),8.5,1);

insert into Employee(empId,empName,sex,birthday,salary,deptId) 

values(seqEmployee.NextVal,'wonsto','Male',to_date('1981-10-03','yyyy--mm-dd'),7.5,1);

insert into Employee(empId,empName,sex,birthday,salary,deptId) 

values(seqEmployee.NextVal,'gavin','Male',to_date('1971-10-03','yyyy--mm-dd'),6.5,1);
/*作用：crazy 没有指定部门  说明：deptId默认为NULL*/

insert into Employee(empId,empName,sex,birthday,salary) 

values(seqEmployee.NextVal,'crazy','female',to_date('1991-10-03','yyyy--mm-dd'),6.5);

/*作用：别名称  说明：as  别名关键字 可要可不要 */

select empId as "employeeId", empName as "employeeName", sex "employeeSex",

birthday employeeBirthday,salary employeeBirthday ,deptId as employeeDeptID

from Employee;

/*作用：查询  说明：e 为employee表的别名 不需要加as */

select e.*  from Employee   e;

/*作用：以empName:(empId,sex,salary)这种格式查询employee中的所有记录    说明：|| oracle中的连接符  用于拼接*/

select empName||':'||'('||empId||','||sex||','||salary||')' as "Employee Info" from Employee;

/*作用：查询那些部门有员工  说明：distinct去掉重复记录*/

select distinct e.deptId  from  Employee e where e.deptId is not null;

/*作用：行号查询  说明：rownum  会给每一笔记录添加一个编号 */
select rownum "romun" ,e.* from Employee e;

select rownum "romun" ,e.* from Employee e where rownum<=4;

/*作用：连接查询(内联查询)  说明:inner join  联合某一个表  on 说明两个表的关系  */

select * from dept d inner join employee e on d.deptId=e.deptId;

/*作用：连接查询(内联查询) 的另外一种方式*/

select * from dept d,employee e where d.deptId=e.deptId;

/*作用：左连查询(左连接)  说明： 左边表的记录全会显示出来   employee carzy不会显示出来 */

select * from dept d left outer join employee e on d.deptId=e.deptId; 

/*作用：右连查询(右连接)   说明： 右边的全会显示出来   dept Technology 不会显示 出来*/

select * from dept d right outer join employee e on d.deptId=e.deptId;

/*作用：交叉连接*/

select e.* ,d.* from employee e cross join dept d; 

/*作用：查询没有employee的dept  子查询*/

select * from dept where deptId not in (select distinct deptId from employee where deptId is not null);

/*作用：查询employee表中所有员工在‘development’部门的记录*/

select * from employee where deptId=(select deptId from dept where deptName='development');

/*作用：union演示  说明：union 联合  将两个不相干的表联合为一个表*/

select deptName ,createTime  from dept

union

select empName ,birthday  from employee;

/*作用：查询employee行数为3<=rownum<=6的记录  说明：minus减法集合  */

select * from employee where rownum<=6

minus

select * from employee where rownum <3;

/*作用：intersect 演示  说明 intersect 交集*/

select d.deptId from dept d

intersect

select e.deptId from employee e;

/*作用：日期函数演示  说明：sysdate 系统当前时间   extract:抽取时间*/

select sysdate from dual;

select extract (year  from sysdate) from dual;

select extract (day from sysdate) from dual;

select extract (month from sysdate) from dual;

select extract (month from birthday) from employee;

select extract (year from sysdate) - extract (year from birthday) "age" from employee;

/*作用:将时间转换为字符型  说明：to_char(日期类型的时间,要转换为字符串的格式) 与to_date相对应*/

select to_char (sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') as "time" from dual;

select empName,to_char(birthday,'yyyy/mm/dd') from employee;

select to_char('1991-10-02','dd/mm/yyyy') from dual;

/*作用：字符串函数演示 说明： lower 将符转换为小写字母  ；upper 将字符转换为大写字母    length 字符的长度  substr 截取字符  从1开始 3表示截取位置(包括它)，2表示截取两个字符    trim 对字符左右除空*/

select lower('ADcb') from dual; 

select upper('ADcb') from dual;

select length ('好Cd') from dual;

select substr('ABcbedfd',3) from dual;

select substr('ADCBCDGER',3,2) from dual;

select substr(empName,1,1) from employee;

select '('|| trim(  '     how are you!   ' )||')' from dual;

/*作用：数学函数演示  说明：avg 平均数，  max 求最大值  ，min 最小值 ，  ceil 最大值，  floor最小值  ，round四舍5入 2表示留几位数 ， mod取模（计算最余数），power 2的3次方   abs  绝对值 , dbms_random.random 随机数*/

select avg(salary) from employee;

select max(salary) from employee;

select min(salary) from employee;

select ceil(5.000001) from dual;

select floor(5.99999) from dual;

select round (5.49784,2) from dual;

select mod (50,3) from dual;

select power (2,3) from dual;

select sign (84),sign (-87),sign(0) from dual;

select sysdate,to_char(3.4)  from dual;

select abs(89),abs(0),abs(-89) from dual;

select dbms_random.random from dual;