Oracle 复习

• 1、建表：create table

create table dandan(xm char(10) primary key,xh char(10),age number(10));

&&

create table song(name varchar(8) not null,
id number(4,0) not null,
primary key (id));

• 2、修改表结构:alter table, alter column

增加列
alter table dandan add sex char(10);

修改表中列的长度，宽度
alter table dandan modify xm char(20);

修改表名称
alter table dandan rename to dandandan;

删除表
drop table dandandan;

修改列名称
alter table dandandan rename column xm to xmm;

删除列名称
alter table dandandan drop  column xmm;
删除多个列名称
alter table dandandan drop（xmm，xh）;

• 3、插入记录:insert into

insert into dandan(xm,xh) values ('zhangsan','lisi');单行插入
insert into soongl select* from scott.emp;子查询插入

• 4、查询：多表查询、分组查询、嵌套查询、更新查询:update

多表查询：
两张表：select * from employee e, department d where e.deptid=d.deptid;
三张表：select * from employee e left join department d on e.deptid=d.deptid left join evaluation v on d.deptid=v.deptid;

例题：
1.查询雇员工资大于公司平均工资的雇员名

select ename from employee
where salary >  (select avg(salary) from employee);

2.查询最低工资的雇员名称

select ename from employee
where salary in (SELECT  min(salary) from employee);

3.查询工资在5000~8000之间的雇员信息。

select ename from employee 
where salary between 5000 and 8000;



4.显示雇佣日期为2011-5月的雇员名称。

select ename from employee where hiredate between '11-5月-01' and '11-5月-31';

5.查询各部门的罚款信息并排列，包括部门号，罚金总额

select  deptid,sum(fines) as "罚款金额" from evaluation
group by deptid
order by sum(fines);

6.查询各部门的奖励信息并排列，包括部门号，奖励总额

select  deptid,sum(reward) as "奖励金额" from evaluation
group by deptid
order by sum(reward);

• 5、创建视图

create or replace view v_dandan
as
(SQL语句，，)

• 6、创建序列:create sequence

create sequence s_ling
increment by 1
start with 1
maxvalue 100
nominvalue
nocycle
nocache;

例题：
1.创建初始值1000，增量10，达到1100后重新1000开始递增
create sequence employees_seq
minvalue 1000
maxvalue 1100
increment by 10;

2.修改employees_seq 增量20 并设置最大值10000
alter sequence employee_seq
maxvalue 10000
increment by 20;

3.通过访问nextvalue和currval 实现nextvalue 伪列返回序列的下一个值currval 位列返回序列的当前值 
select userinfo_seq.nextval from dual;
select userinfo_seq.currval from dual;
insert into userinfo(id,username)
values (userinfo_seq.nextval,'admin');

4.删除
drop sequence userinfo_seq;

• 7、创建存储过程:编写一个过程，要求根据用户输入的员工号（employee_id）查询EMPLOYEES表，返回员工的工作职位、工作年限、电话和Email。并调用此过程。

create or replace procedure dandan(employee_id employee.empid%type) 
    as
       vempid employee.empid%type;
       vjob employee.jobid%type;
       vyear employee.hiredate%type;
       vtel employee.mobile%type;
          begin
       select empid,jobid,hiredate,mobile  into vempid,vjob,vyear,vtel from employee 
                  where empid=employee_id;
       dbms_output.put_line('THE ID is '||employee_id||'job '||' id is: '||
                  vjob||'; year is: '||vyear||' and vtel is:'||vtel);
                   exception
       when no_data_found then
           dbms_output.put_line('no data');
       when too_many_rows then
           dbms_output.put_line('too many data');
       when others then
           dbms_output.put_line('error');
end;

exec dandan(2)

特殊情况(不用锚定)：
  create or replace procedure queryempinf(deptcode number) 
    as
       v_count number;
    
    begin

• 8、 用户创建及授权

create user dandan identified by 123;

授权：

grant connect to ling;授权连接

grant select on scott.emp to ling;授予查询权利

revoke select on scott.emp from ling;回收权利

drop  user ling cascade;删除用户