/*作用:创建表空间 用来保存用户的操作 说明: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;