Oracle数据库基本操作
1.登录数据库
>sqlplus / as sysdba
>sqlplus scott/tiger
SQL>conn scott/tiger
2.用户解锁
alter user scott account unlock;
aelect account_status from dba_users where username='SCOTT';
3.表数据的基本操作
select * from user_tables; //查看用户拥有哪些表
insert into dept(deptno,dname,loc) values(50,'sale','beijing');
update dept d set d.dname='developer' where d.deptno=50;
delete from dept d where d.deptno = 50;
SQL>alter session set nls_date_format='YYYY-MM-DD'; //设置时间格式
select e.ename as "姓名",round((sysdate-e.hiredate)/365) as "工作年数" from emp e order by 2 //按第二列排序
select e.ename||'员工本月工资为：'||(e.sal+1500) as "本月工资" from emp e
select distinct e.deptno from emp e //去重
nvl(comm,0) //comm有值则返回comm值，否则显示0
group by //分组函数
select avg(e.sal),e.job from emp e group by e.job having avg(e.sal)>2000 //having，使用group by分组有条件限制时
4.表的基本操作
oracle数据库5个约束：主键、外键、非空、唯一、条件
create table Business(
busiNo number(2) constraint PK_Business primary key, //主键
busiName varchar2(32) not null, //非空
busiModel varchar2(32) unique, //唯一
price number(7,2) constraint check_price check(price>0 and price<100), //条件
ItemNO number(2),constraint FK_Business foreign key(ItemNO) references Item (ItemNO), //外键
StartTime date);
create table business_copy as select * from business; //复制表
drop table business_copy //删除表
insert into business(busino,businame,itemno,starttime) select * from business_copy //导入数据
alter table item add(manager varchar2(20)) //表结构增加字段
alter table item modify(manager varchar2(20)) //修改表字段
alter table item drop column manager //删除表字段
5.用户的基本操作
create user lisi identified by lisi; //创建用户
grant connect，resource to lisi //授权
grant select,insert,update,delete on scott.emp to lisi //授权某张表的相关权限
revoke select on scott.emp from lisi //回收权限
alter user lisi identified by lisi123 //修改密码
drop user lisi cascade //删除用户(加上cascade将删除与用户相关联的表）
select * from session_privs //查看数据库用户的权限
select * from user_role_privs //查看用户拥有的角色
dba_sys_privs
6.数据库对象的基本操作
数据库对象包括：表、视图、序列、索引、函数、触发器等等
视图：
create view avgsal as
select round(avg(sal),0) as "工资", count(*) as "人数", d.deptno
from dept d, emp e
where d.deptno = e.deptno
group by d.deptno;
select * from avgsal;
select * from user_views; //查看用户拥有的视图
序列：
create sequence BOOKID
minvalue 1
maxvalue 10000
start with 5
increment by 1
nocache
nocycle; //数值不可循环
insert into book(bookid,bookname) values(bookid.nextval,'aabbcc');
select bookid.currval from dual; //查看序列当前值
索引：
create index id_test_index on test_index(id);
同义词：
同义词分私有同义词和公共同义词
grant create any synonym to scott;
grant create public synonym to scott;
create synonym sg for salgrade; //创建同义词
create public synonym emp for scott.emp
存储过程：
create or replace procedure insert_data
is
temp varchar2(20):='insert data';
begin
for i in 1 .. 1000000 loop
insert into test_index(id,name) values(i,temp);
end loop;
commit;
end;
SQL>execute insert_data; //执行存储过程
函数：
avg()、upper()、lower()、round()、to_date()等
create or replace function get_empsal(emp_no in number)
return number
is emp_sal number(7,2);
begin
select sal into emp_sal
from emp
where empno=emp_no;
return(emp_sal);
end; //创建函数
引用 删除 liumangniu / 2013-10-15 17:09:04
引用 删除 xsjccit319x / 2013-05-29 16:33:46
引用 删除 RachelLead / 2013-05-23 10:43:19
