sqlplus scott/tiger@192.168.2.1[:port]/sid [as sysdba]
简单查询
select * from tab;
select * from dept;
创建表
create table t_user(id int primary key,username varchar2(20),password varchar2(20));
创建序列(用户主键增长策略)
create sequence seq_user increment by 1 start with 1 nomaxvalue nocache;
查看序列
select * from user_sequences;
建表空间 临时表空间 (默认表空间是user)
create tablespace test_ts datafile 'E:oracledatafcs.dbf' size 10M;
create temporary tablespace test_temp_ts tempfile 'E:oracledatafcs_temp.dbf' nsize 5M;
创建用户
create user fcs identified by 123 default tablespace test_ts temporary tablespace test_temp_ts;
给用户授权
grant create session,resource to fcs;
删除用户必须级联删除
drop user_name cascade;
每页显示50条记录
set line 100 pagesize 50;
对scott用户下的表恢复(查找脚本 复制语句 执行)
E:oracleproduct10.2.0db_1RDBMSADMIN
内连接、外连接，全连接
内 select * from student s ,class c where s.classid = c.id;
左 select * from student s left outer join class c on s.classid = c.id;
全 select * from student s full outer join class c on s.classid = c.id;
函数
字符函数 (to_char) ------- select tochar(sysdate,'yyyy-MM-dd') from dual;
数值函数 ----- 跟Java大同小异
获取当前登陆用户拥有的表
select table_name from user_tables;
select * from tab/tabs;
复制一张表
create table emp2 as select * from emp;
复制表结构而不要数据
create table emp3 as select empno,ename from emp where 1=0;
取一张表中的信息插入另一张表
insert into emp3 select empno,ename from emp where job='MANAGER';N
标识列(自增长的列)
MYSQL-----create table demo(id int primary key auto_increment);
SQLServer----create table demo(id int primary key identity(1,1));
Oracle----1.创建序列 2.创建触发器
1.create sequence seq_emp increment by 1 start with 1 nomxavalue(mxavalue 99999)[nocache];
2.create or replace trigger tri_emp
before insert on emp
for each row
begin
select seq_emp.nextval into :new.empno from dual;
end;
/
执行脚本---@E:fcsoracle ri_emp.sal;
插入时间字段和空字段
空字段 null或者不写
insert into emp(empno,job,hiredate) values(111,NULL,to_date('2011-1-2 09:10:11','yyy-MM-dd hh24:mi:ss'));
注意提交事物
更新
update emp3 set(empno,ename)=(select empno,ename from emp where empno =1121) where empno = 11;
删除(DML) or 还原 and 事物
delete from emp3;/truncate table emp3;(不进回收站 直接删除)
还原
rollback;
savepoint t;
xxxxxxxxxxx
rollback to t;(只能回退xxx的操作)
set autocommit on/off;
表的锁定
*隐式锁 update(一边未提交另一边无法再更新) delete(insert不会) 快速提交
锁定表 lock table manager2 in exclusive mode;
释放锁 commit/rollback;
表的操作
创建表 create table tablename..
查看表 decs tablename;
*修改表的结构 alter table test add age int [default 123]; alter table test drop column age;
修改字段大小 alter table test modify addr varcahr2(50);
表重命名 rename test to t_test;
完整性 数据完整性 参照完整性
约束：
(非空约束) not null
(默认约束) default
检查约束 check
唯一约束 unique
主键约束 primary key
外检约束 foreign key
补充:char和varchar的区别 可变/不可变长 时间(不需计算空间) 空间
alter table table_name modify stuaddress default '地址不详';
alter table table_name modify stuaddress not null;
alter table table_name add constraint uq_stuID unique(stuID);
alter table table_name add constraint ck_stuAge check(stuAge between 10 and 30);
alter table table_name add constraint pk_stuNo primary key(stuNo);
alter table stumark add contraints fk_stuno foregin key(stuNo) reference stuInfo(stuNo)[on delete cascade][on delete set null];
drop table stuInfo; 无法删除 因为有其他表参照它
-----》drop table stuInfo cascade constraints;
###################################### oracle特性----pl/sql #####################################
进行DML操作 用来编写过程 函数 包及数据库触发器 过程和函数也称为子程序一个块包括三个部分 每个部分由一个关键字标识
一个简单的字符串输出
set serveroutput onbegin
dbms_output.put_line('hello');
end;
/
变量
*普通变量 *列变量 全局变量 *记录变量1.普通变量
set serveroutput ondeclare
greeting varchar2(20):='fcs';
begin
greeting:='google';
dbms_output.put_line('greeting:'||greeting);
end;
/
2.列变量
set serveroutput ondeclare
greeting varchar2(20):='fcs';
myname class.name%type;
begin
greeting:='google';
dbms_output.put_line('greeting:'||greeting);
select name into myname from class where id=1;
dbms_output.put_line('name:'||myname);
end;
/
3.行变量 记录变量
set serveroutput ondeclare
greeting varchar2(20):='fcs';
myname class.name%type;
myrow class%rowtype;
begin
greeting:='google';
dbms_output.put_line('greeting:'||greeting);
select * into myrow from class where id=1;
dbms_output.put_line('name:'||myrow.name||' classid:'||myrow.id);
end;
/
流程控制
1).选择判断
declarescore number(3):=56;
begin
if score>=90 and score<=100 then
dbms_output.put_line('您的成绩为:优');
else
dbms_output.put_line('您的成绩为:差');
end if;
end;
/
declare
score number(3):=85;
begin
if score>=90 and score<=100 then
dbms_output.put_line('您的成绩为:优');
elsif score>=80 and score<90 then
dbms_output.put_line('您的成绩为:良');
else
dbms_output.put_line('您的成绩为:差');
end if;
end;
/
2).多路分支(case-when-then-else-end)
1-基本结构
declarescore number(3):=65;
degree number(1):=0;
begin
if
score>=90 and score<=100 then
degree:=1;
elsif score>=80 and score<90 then
degree:=2;
else
degree:=3;
end if;
case degree
when 1 then dbms_output.put_line('您的成绩为:优');
when 2 then dbms_output.put_line('您的成绩为:良');
else dbms_output.put_line('您的成绩为:差');
end case;
end;
/
2-表达式结构
declarescore number(3):=65;
degree number(1):=0;
level char(2);
begin
if
score>=90 and score<=100 then
degree:=1;
elsif score>=80 and score<90 then
degree:=2;
else
degree:=3;
end if;
level:=case degree
when 1 then '优'
when 2 then '良'
else '差'
end;
dbms_output.put_line('您的成绩为:'||level);
end;
/
3-搜索结构(case后面没有变量 when中可以写表达式)
declarescore number(3):=77;
begin
case when score>=90 and score<=100 then
dbms_output.put_line('您的成绩为:优');
when score>=80 and score<90 then
dbms_output.put_line('您的成绩为:良');
else
dbms_output.put_line('您的成绩为:差');
end case;
end;
/
3).循环
set serveroutput ondeclare
i number(5):=1;
v_sum number(5):=0;
begin
loop
v_sum:=v_sum+i;
i:=i+1;
exit when i>=10;
end loop;
dbms_output.put_line('sum:'||v_sum);
end;
---while---
declarei number(5):=1;
v_sum number(5):=0;
begin
while i<10
loop
i:=i+1;
v_sum:=v_sum+i;
end loop;
dbms_output.put_line('sum:'||v_sum);
end;
/
----for-----
declarev_sum number(5):=0;
begin
for i in 1..10
loop
v_sum:=v_sum+i;
end loop;
dbms_output.put_line('sum:'||v_sum);
end;
/
游标(用于临时存储):显式/隐式
select into...1.隐式游标
beginupdate class set name='wpj1403' where id=2;
if SQL%ISOPEN then
dbms_output.put_line('ok');
end if;
end;
/
2.显示游标
声明游标--->打开游标--->提取游标--->关闭游标---标准写法---
declarecursor cur_f is select * from class where id=1;
v_record class%rowtype;
begin
open cur_f;
fetch cur_f into v_record;
dbms_output.put_line('id='||v_record.id||',name:'||v_record.name);
close cur_f;
end;
/
---简单写法---
declarecursor cur_f is select * from class;
begin
for v_record in cur_f
loop
dbms_output.put_line('id='||v_record.id||',name:'||v_record.name);
end loop;
end;
/
begin
for v_record in (select * from class)
loop
dbms_output.put_line('id='||v_record.id||',name:'||v_record.name);
end loop;
end;
/
存储过程
存储过程和函数都是以命名的数据库对象形式存储在数据库当中。只有被授权的用户或者创建者本身才能执行存储过程或调用函数。
一个重复使用的功能 可以设计成为存储过程:一个经常调用的计算,可以设计为存储函数；
存储过程的返回值必须通过参数带回
create[or replace] procedure
创建一个显示雇员总人数的存储过程
create or replace procedure my_pro
is
v_num number;
begin
select count(*) into v_num from scott.emp;
dbms_output.put_line(v_num);
end;
/
调用
1).execute my_pro;
2).begin
scott.my_pro;
end;
/
删除
drop procedure my_pro;
编写显示雇员信息的存储过程emp_list 并引用emp_count存储过程
create or replace procedure emp_count
is
v_num number;
begin
select count(*) into v_num from scott.emp;
dbms_output.put_line(v_num);
end;
/
create or replace procedure emp_list
is
cursor emp_cur is select * from emp;
begin
for emp_rec in emp_cur
loop
dbms_output.put_line(emp_rec.ename||' '||emp_rec.sal);
end loop;
end;
/
参数传递
in 输入参数变量 用于传递参数给存储过程
out 出 从存储过程获取数据
inout 定义一个输入.输出参数变量
create or replace procedure change_salary(pno in number,psal in number)
as
begin
update emp set sal=sal+psal where empno=pno;
if SQL%FOUND then
dbms_output.put_line('salary update success!');
commit;
end if;
exception
when others then
dbms_output.put_line('salary update failure!');
rollback;
end;
/
execute change_salary(7788,500);
********************** Oracle 常考 ********************
如何输出前10条记录select * from emp where rownum<=10;
输出第10到20条的数据
select * from (select e.*,rownum r from emp e) where r>=10 and r<=20;
注:这条语句即是输出第10到第20条纪录，这里之所以用rownum rn，是把rownum转成实例，因为rownum本身只能用 <=的比较方式，只有转成实列，这样就可做 >=的比较了。
按工资和工作月份的乘积排序(按计算结果排序)
select ename, sal*months_between(sysdate,hiredate) "total" from emp order by "total";
统计各部门的最高工资,排除最高工资小于3000的部门。(having 后不可用别名)
select deptno,max(sal) from emp group by deptno having max(sal)>3000;
select dname ,max(sal) from emp e,dept d where e.deptno=d.deptno group by dname having max(sal)>3000;