Oracle数据库基本操作

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