c 操作数据库oracle数据库,oracle数据库基本操作 - csophia的个人空间 - 51Testing软件测试网 51Testing软件测试网-软件测试人的精神家园...

1.登录数据库>sqlplus / as sysdba

>sqlplus scott/tiger

SQL>conn scott/tiger

2.用户解锁

alter user scott account unlock;

select 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; //创建函数