Oracle知识梳理
create tablespace user1 datefile '' size 20M //创建一个名为User1的表空间
autoextend on//数据文件的可扩展性
autoextend off//关闭
autoextend on next 5M//指定数据文件的增长幅度
autoextend on next 5m maxsize 500M
//查看表空间是否创建成功
select file_name,tablespace_name from dba_data_files order by file_name
//查看表空间
select tablespace_name,status,allocation_type from dba_tablespaces
select user_id,username,default_tablespace from dba_users//修改数据库默认表空间的的SQL语句
alter tablespace default tablespace user1
alter tablespace user2 rename to user20
dorp tablespace user20 including contents and datafiles
//创建主键约束
create table student (student_id number primary key,student_name vachar2(20),student_birthday date,student_address vachar2(50),student_phone vachar2(20))
//建立外键
create table customers(customer_id number primary key,customers_name vachar2(50),customers_address vachar2(50),customers_phone vachar2(20),email vachar2(20))
create table orders(orders_id number primary key,customers_id number,goods_name vachar2(20),quantity number,unit vachar2(10))
alter table orders add constraint fk_orders_customer foreign key (customer_id) referemces customers (customer_id)
//创建视图
create table employees1(employee_id number primary key,first_name vachar2(4),last_name vachar2(4),province vachar2(10),city vachar2(10),salary number)
select text from user_views where view_name='VW_EMPLOYEES'
select *from VW_EMPLOYEES
//修改视图
create or replace view VW_EMPLOYEES as
select employee_id,last_name||first_name as employee_name,province||'-'||city as location,salary from employees1
//删除视图
drop view VW_EMPLOYEES
//创建索引
create index idx orders(orders_id,goods_name)
函数
//截取字符串
select substr('1234567890',5,4) from dual;//第五位开始的四个字符
//获得字符串出现的位置
select instr('big big tiger','big') from dual;
itrim()//删除字符串首部空格
rtrim()//删除字符串尾部空格
current_timestamp()//返回当前会话时区的当前时间戳
current_times()//返回当前会话时区的当前日期
//in判式用于判断表的列值是否存在于列表（集合）中。而exists判式则可用于判断查询结果集合是否为空
//排名
select student_name,rank() over(orders by student_age) position from student;
//if else
set serverout on;
delete employee_num number;
begin
select count(*) into employee_num from t_employees where status="ACT";
if employees_num>0 then
dbms_output.put_line('存在记录');
else
dbms_output.put_line('不存在记录');
end if;
end;
//case when
declare employee_num number;
begin
select count(*) into employee_num from t_employees where status="ACT";//将数目存储到变量employee_num中
case
when employee_num>1 then
dbms_output.put_line('存在多条有效记录');
when employee_num=1 then
dbms_output.put_line('仅存在一条有效记录');
else
dbms_output.put_line('不存在记录');
end case;
end;
创建储存过程
create or replace procedure update_students
begin
update student set student_age=10;
commit;
end update_students;
//执行储存过程
excute update_students;
IN OUT 参数
create or replace procedure swap(in_out_param1 in out number,in_out_param2 in out number) as
begin
declare param number;
begin
param:=in_out_param1;
in_out_param1:=in_out_param2;
in_out_param2:=param;
end;
end;
树形查询
查询
查找重复数据
select distinct month from t_salary//获得唯一性记录
order by //升序(asc)和降序(desc)排列
group by //用于对记录集合进行分组
having //对数据源进行条件过滤
