Oracle
一、创建表空间
- create tablespace waterboss(表空间名) datafile 'c:\waterboss.dbf'(数据文件) size 100m(文件大小) autoextend on next 10m(每次扩容的大小);
二、创建用户与用户授权
1.创建用户
- create user wateruser(用户名) identified by 801322(密码) default tablespace waterboss(表空间名);
2.给用户赋予dba权限
- grant dba to wateruser(用户名)
三、数据类型
1.字符型
- char:固定长度(不够长度用空格补齐)的字符类型,最多存储2000个字节
- varchar2:可变长度的字符类型,最多存储4000个字节
- long:大文本数据类型,最大可以存储2G
2.数值型
-
number:数值类型
number(5) 最大可以存的数是99999 ps:5为存的最大位数 number(5,2) 最大可以存的数为999.99 ps:(5,2)5为存的最大位数,2为小数的位数
3.日期类型
- date:日期时间型,精确到秒
- timestamp:精确到秒的小数点后9位
4.二进制型(大数据类型)
- clob:存储字符,最大可以存4G,弥补了long不足
- blob:存储图像、声音、视频等二进制数据,最多可以存4G
四、表的创建、修改与删除
1.创建表
create table 表名(
字段名 类型(长度) primary key,
字段名 类型(长度),
……
);
eg://创建业主表
create table T_OWNERS(
ID number primary key,
NAME varchar2(30),
ADDRESSID number(18),
HOUSENNUMBER varchar2(30),
WATERMETER varchar2(30),
ADDATE date,
OWNERTYPEID number
);
2.修改表
-
增加字段
alter table 表名 add(列名1 类型, 列名2 类型,……); eg: alter table T_OWNERS add( REMARK varchar2(20), OUTDATE date );
-
修改字段(修改数据类型)
alter table 表名 modify(列名1 类型, 列名2 类型,……); eg: alter table T_OWNERS modify( REMARK char(20), OUTDATE timestamp );
-
修改字段名
alter table 表名 rename column 原列名 to 新列名; eg: alter table T_ONWERS rename column OUTDATE to EXITDATE;
-
删除字段名
alter table 表名 drop column 列名1,列名2……; eg: alter table T_OWNWES drop column RAMAARK;
3.删除表
-
删除表
drop table 表名; eg: drop table T_OWNERS;
五、数据的增删改
1.插入数据
-
insert into 表名(列名1,列名2……) values(值);
insert into T_OWNERTYPE(ID,NAME) values(1,'居民');
2.修改语句
- update T_OWNERS set 列名 where 条件
3.删除语句
- delete from 列名 where 条件
六、整库的导入与导出
1.导出
- exp system/801322 file=文件名 full=y;
2.导入
- 对象如果存在,会自动跳过,会显示失败
- imp system/801322 file=文件名 full=y;
实验
- 打开输出语句 set serveroutput on;
- 需要注意的是:我为了保持数据不变,后续过程中需要修改的部分,我都使用查询语句进行输出代替了
一. 实验一
略
二. 实验二
1. 创建表
create table goods(
goodsId varchar2(20) primary key,
goodsName varchar2(20) not null ,
unitprice number check(unitprice > 0),
category varchar2(20),
provider varchar2(20)
);
/*
goodsId 商品号
goodsName 商品名
unitprice 单价
category 商品种类
provider 供应商
*/
create table customer(
customerId varchar2(20) primary key,
name varchar2(10),
address varchar2(30) ,
email varchar2(20) unique ,
sex char(2) default '男' check (sex = '男' or sex = '女'),
cardId varchar2(18)
);
/*
customerId 客户号
name 姓名
address 住址
email 电邮
sex 性别
cardId 身份证
*/
create table purchase(
customerId varchar2(20),
goodsId varchar2(20),
nums number check ( nums between 1 and 30)
);
/*
customerId 客户号
goodsId 商品号
nums 购买数量
*/
2. 增加约束
alter table goods
modify goodsName not null;
alter table customer
modify cardId unique;
alter table customer
modify address check(address in('海淀','朝阳','东城','西城','通州','崇文'));
三. 实验三
1. 创建索引
create index TEST on STUDENT(SAGE desc );
2. 查询每个员工的间接上司的员工号----自身连接
select e1.EMPNO,e1.ENAME,e2.MGR 间接上司的员工号
from EMP e1
join EMP e2 on e1.MGR = e2.EMPNO;
3. 查询每个部门入职最早的员工的姓名、工资、部门号和入职时间
select ENAME,SAL,DEPTNO,HIREDATE from EMP where (DEPTNO,HIREDATE) in (
select DEPTNO,min(HIREDATE )from EMP group by DEPTNO
);
4. 查询以工资降序排列的4至9号员工的姓名、雇员号和工资
select ENAME,DEPTNO,SAL from (
select ENAME,DEPTNO,SAL,row_number() over (order by SAL desc ) rno from EMP
) e
where e.rno between 4 and 9;
四. 实验四
1. 编写一个程序块,从EMP表中显示名为‘SMITH’的雇员的薪水和职位
set serveroutput on; -- 打开输出
declare
v_job EMP.JOB%type;
v_sal EMP.SAL%type;
begin
select SAL,JOB into v_sal,v_job from EMP where ENAME = 'SMITH';
DBMS_OUTPUT.PUT_LINE('SMITH的工资:' || v_sal || ',职位是:' || v_job );
end;
2. 编写一个程序块,接受用户输入一个部门号,从DEPT表中显示该部门的名称与所在位置
declare
v_dname DEPT.DNAME%type;
v_loc DEPT.LOC%type;
begin
select DNAME,LOC into v_dname,v_loc from DEPT where DEPTNO = &no;
DBMS_OUTPUT.PUT_LINE('部门名称:'|| v_dname || ',所在位置:' || v_loc);
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('输入的部门号不存在!');
end;
3. 编写一个程序块,将EMP表中雇员名全部显示出来(需要使用游标)
declare
-- 定义游标
type emp_cursor is ref cursor;
-- 定义游标变量
ename_cursor emp_cursor;
-- 定义变量
v_eanme EMP.ENAME%type;
begin
-- 把游标与select结合
open ename_cursor for select ENAME from EMP;
-- 循环取出
loop
fetch ename_cursor into v_eanme;
-- 退出循环如果没有更多数据
exit when ename_cursor%notfound;
DBMS_OUTPUT.PUT_LINE('员工姓名:'||v_eanme);
end loop;
end;
4. 编写一个程序块,将EMP表中前5人的名字显示出来。(游标变量)
-
使用ROWNUM <= 5输出前五行
declare -- 定义游标 type emp_cursor is ref cursor; -- 定义游标变量 ename_cursor emp_cursor; -- 定义变量 v_eanme EMP.ENAME%type; begin -- 把游标与select结合 open ename_cursor for select ENAME from EMP where ROWNUM <= 5 ; -- 循环取出 loop fetch ename_cursor into v_eanme; -- 退出循环如果没有更多数据 exit when ename_cursor%notfound; DBMS_OUTPUT.PUT_LINE('员工姓名:'||v_eanme); end loop; end;
5. 编写一个程序块,从EMP表中对名字以“A”或“S”开头的所有雇员按他们基本薪水的10%给他们加薪
declare
type emp_cursor is ref cursor;
sal_cursor emp_cursor;
v_sal EMP.SAL%type;
v_name EMP.ENAME%type;
begin
open sal_cursor for select ENAME,SAL into v_name,v_sal from EMP where ENAME like 'A%' or ENAME like 'S%';
loop
fetch sal_cursor into v_name,v_sal;
exit when sal_cursor%notfound;
v_sal := (1+0.1) * v_sal;
DBMS_OUTPUT.PUT_LINE('员工姓名:'||v_name||',加薪后工资:'||v_sal);
end loop;
end;
五. 实验五
1. 创建表空间
create tablespace data01 datafile 'D:\Oracle11g\tablespacedata\data01.dbf' size 20m uniform size 128k;
2. SCOTT用户在该表空间里创建一个表
CREATE TABLE SCOTT.employees(
employee_id varchar2(10) PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
hire_date DATE,
salary NUMBER
)
TABLESPACE data01;
3. 扩展表空间
-
增加数据文件
alter tablespace data01 add datafile 'D:\Oracle11g\tablespacedata\data02.dbf' size 20m;
-
增加数据文件的大小
alter database datafile 'D:\Oracle11g\tablespacedata\data01.dbf' resize 10m;
-
设置文件的自动增长
alter database datafile 'D:\Oracle11g\tablespacedata\data01.dbf' autoextend on next 10m maxsize 50m;
4. 设置表空间中脱机与只读
-
脱机
alter tablespace data01 offline;
-
联机
alter tablespace data01 online;
-
只读
alter tablespace data01 read only;
5. 移动表空间
-- 先确保表空间处于脱机状态 保持数据的一致性
alter tablespace data01 offline;
ALTER DATABASE RENAME FILE 'D:\Oracle11g\tablespacedata\data02.dbf' TO 'D:\Oracle11g\data\data02.dbf';
6. 删除表空间
drop tablespace data01 including contents and datafiles;
六. 实验六
略
七. 实验七
1. 创建一个存储过程,以员工号为参数,输出该员工的工资
create or replace procedure get_salary(id number)
as
v_salary EMP.SAL%type;
begin
select SAL into v_salary from EMP where EMPNO = id;
DBMS_OUTPUT.PUT_LINE(id ||'号员工的工资为:'||v_salary);
end;
call get_salary(7788);
2. 创建一个存储过程,以员工号为参数,修改该员工的工资
create or replace procedure up_sal(id number)
as
v_sal EMP.SAL%type;
v_dptno EMP.DEPTNO%type;
begin
select SAL,DEPTNO into v_sal,v_dptno from EMP where EMPNO = id;
if v_dptno = 10 then
v_sal := v_sal + 150;
elsif v_dptno = 20 then
v_sal := v_sal + 200;
elsif v_dptno = 30 then
v_sal := v_sal + 250;
else
v_sal := v_sal + 300;
end if;
DBMS_OUTPUT.PUT_LINE(id || '工资:' || v_sal);
update EMP set SAL = v_sal where EMPNO = id;
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('输入的员工号不存在!');
end;
call up_sal(7788);
3. 创建一个存储过程,以员工号为参数,返回该员工的工作年限(以参数形式返回)
create or replace procedure get_date(id number,yeartime out varchar2)
as
begin
select to_char(sysdate,'YYYY')-to_char(EMP.hiredate,'YYYY') into yeartime from EMP where EMPNO = id;
end;
DECLARE
yeartime VARCHAR2(100);
BEGIN
get_date(7499, yeartime);
DBMS_OUTPUT.PUT_LINE('工作年限为: ' || yeartime);
END;
4. 创建一个存储过程,以部门号为参数,输出入职日期最早的10个员工信息
create or replace procedure get_emp(v_deptno emp.deptno%type) as
cursor c_emp is select * from emp where deptno = v_deptno order by hiredate;
v_times number := 0;
begin
for v_emp in c_emp loop
v_times := v_times + 1;
dbms_output.put_line(v_emp.empno || ',姓名:' || v_emp.ename || ',入职时间:' || to_char(v_emp.hiredate,'yyyy-mm-dd'));
if v_times = 10 then
exit;
end if;
end loop;
end;
call get_emp(20);
5. 创建一个函数,以员工号为参数,返回该员工的工资
create or replace function get_sal1(name EMP.ENAME%type)
return EMP.SAL%type as
v_sal EMP.SAL%type;
begin
select SAL into v_sal from emp where ENAME = name;
return v_sal;
end;
declare
sal EMP.SAL%type;
begin
sal := get_sal1('SCOTT'); // 调用函数
DBMS_OUTPUT.PUT_LINE('工资:'||sal);
end;
// 也可以这样调用
select get_sal1('SCOTT') from DUAL;
6. 创建一个函数,以部门号为参数,返回该部门的平均工资
create or replace function get_avg_sal1(dptno EMP.DEPTNO%type)
return EMP.SAL%type as
v_avg_sal EMP.SAL%type;
begin
select avg(SAL) into v_avg_sal from EMP where DEPTNO = dptno;
return v_avg_sal;
end;
declare
sal EMP.SAL%type;
begin
sal := get_avg_sal1(10);
DBMS_OUTPUT.PUT_LINE('平均工资:'||sal);
end;
7. 创建一个函数和过程,以员工号为参数,返回该员工所在部门的平均工资
// 过程
create or replace procedure get_avg_sal(epno number)
as
v_avgsal number;
begin
select avg(sal) into v_avgsal from emp where deptno =
(select deptno from emp where empno = epno);
dbms_output.put_line('员工号为:'||epno||'所在部门平均工资:'||v_avgsal);
end;
call get_avg_sal(7788);
// 函数
create or replace function get_avg_sal2(epno EMP.DEPTNO%type)
return EMP.SAL%type as
v_avg_sal EMP.SAL%type;
begin
select avg(SAL) into v_avg_sal from EMP where DEPTNO =
(select DEPTNO from EMP where EMPNO = epno);
return v_avg_sal;
end;
declare
sal EMP.SAL%type;
begin
sal := get_avg_sal2(7788);
DBMS_OUTPUT.PUT_LINE('该员工所在部门平均工资:'||sal);
end;
8. 创建一个存储过程,以员工号和部门号作为参数,修改员工所在的部门为输入的部门号
create or replace procedure up_deptno(epno number,dptno number)
as
v_dptno emp.deptno%type;
begin
select DEPTNO into v_dptno from emp where EMPNO = epno;
update emp set deptno = dptno where empno= epno;
dbms_output.put_line('员工由'|| v_dptno||'号部门调入到' || dptno || '号部门');
exception
when no_data_found then
dbms_output.put_line('该部门不存在,请输入正确的部门号');
end;
call up_deptno(7788,40);
9. 创建一个存储过程,以一个整数为参数,输出工资最高的前几个(参数值)员工的信息
create or replace procedure get_emp(num number)
as
cursor emp_cursor is select * from emp order by SAL desc;
v_index number := 0;
begin
dbms_output.put_line('工资最高的前' || num || '个的员工信息如下:');
for v_emp in emp_cursor loop
v_index := v_index + 1;
dbms_output.put_line(v_emp.empno || ',姓名:' || v_emp.ename || ',工资:' || v_emp.SAL);
if v_index = num then
exit;
end if;
end loop;
end;
call get_emp(3);
10. 创建一个存储过程,以两个整数为参数,输出工资排序在两个参数之间的员工信息
create or replace procedure get_emp(num1 number,num2 number)
as
cursor emp_cursor is select * from emp order by SAL desc;
v_index number := num1;
begin
dbms_output.put_line('工资排序在' || num1 || '与' || num2 || '之间的员工信息为:');
for v_emp in emp_cursor loop
dbms_output.put_line(v_emp.empno || ',姓名:' || v_emp.ename || ',工资:' || v_emp.SAL);
v_index := v_index + 1;
if v_index > num2 then
exit;
end if;
end loop;
end;
call get_emp(1,3);
八. 实验八
1. 创建用户A,B,C,D
create user A identified by 123;
create user B identified by 123;
create user C identified by 123;
create user D identified by 123;
2. 创建角色ROLE1
create role role1 not identified;
3. 授权利用角色概念采用级联授权方式使四个用户(A-B-C-D)都能在数据库中创建表create,能对Scott的EMP表进行查询select,能EMP表的SAL属性修改alter;授给A,A再给B,以此类推
grant select on SCOTT.emp to role1; grant alter on SCOTT.EMP(sal) to role1; grant role1 to A; GRANT role1 TO B WITH ADMIN OPTION; GRANT role1 TO C WITH ADMIN OPTION; GRANT role1 TO D WITH ADMIN OPTION;
4. 删除C用户
drop user C cascade;
5. 回收A用户和B用户创建表的权限
revoke create table from A,B;
6. 回收所有用户对EMP表的访问权限
REVOKE ALL PRIVILEGES ON SCOTT.EMP FROM PUBLIC;
7. 给用户A增加一个给scott用户的EMP表创建索引权限
grant index on SCOTT.EMP to A;
8. 回收角色ROLE1的对EMP表查询的权限
REVOKE SELECT ON SCOTT.EMP FROM ROLE1;
九. 实验九
1. SCOTT用户导出EMP表到某路径下;SCOTT删除EMP表;导入该表备份
- 细节:需要进入bin目录;eg: D:\Oracle11g\product\11.2.0\dbhome_1\BIN>
-
导出表
exp userid=scott/801322@orcl tables=(emp) file=D:\Oracle11g\test\emp.dmp
-
删除emp表
drop table emp;
-
导入emp表
imp userid=scott/801322@orcl tables=(emp) file=D:\Oracle11g\test\emp.dmp
2. 管理员用户SYSTEM备份SCOTT方案的表EMP;把该备份导入到SYSTEM方案中
-
备份表
exp userid=system/801322@orcl tables=(scott.emp) file=D:\Oracle11g\test\emp1.dmp
-
导入表
① imp userid=system/801322@orcl file=D:\Oracle11g\test\emp1.dmp full=y ② imp userid=system/801322@orcl file=D:\Oracle11g\test\emp1.dmp fromuser=system touser=scott
3. SCOTT导出自己的方案;system把该方案的备份导入到SCOTT用户
-
SCOTT导出自己的方案
exp userid=scott/801322@orcl file=D:\Oracle11g\test\scott.dmp
-
system把该方案的备份导入到SCOTT用户
imp userid=system/801322@orcl file=D:\Oracle11g\test\scott.dmp fromuser=scott touser=scott
4. system导出SCOTT的方案;SYSTEM把该方案导入给自己
-
system导出SCOTT的方案
exp userid=system/801322@orcl owner=(scott) file=D:\Oracle11g\test\system.dmp
-
SYSTEM把该方案导入给自己
imp userid=system/801322@orcl file=D:\Oracle11g\test\system.dmp full=y
5. 备份
-
Scott创建一个跟EMP表的一样的表EMP1
create table emp1 as select * from EMP;
-
做了一个该表的备份存放在某路径下
exp userid=scott/801322@orcl tables=(scott.emp1) file=D:\Oracle11g\test\emp2.dmp
-
Scott删除了EMP1表中的所有元组
truncate table emp1;
-
导入备份,只导入数据
imp userid=scott/801322@orcl tables=(emp1) file=D:\Oracle11g\test\emp2.dmp ignore=y
十. 实验十
1. 利用JDBC远程连接方式在JAVA中查询EMP表中雇员名
public static void main(String[] args) {
try {
// 1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.得到链接
Connection cn = DriverManager.getConnection
("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","801322");
// 3.执行语句
Statement statement = cn.createStatement();
ResultSet rs = statement.executeQuery("select * from emp");
// 4.得到数据
while (rs.next()) {
System.out.println("姓名:" + rs.getString(2)); // rs.getString()下标从1开始
}
// 5.关闭资源
cn.close();
statement.close();
rs.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
2. 利用JDBC远程连接方式在JAVA中将EMP中10号部门员工的工资加100
try {
// 1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.得到链接
Connection cn = DriverManager.getConnection
("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","801322");
// 3.执行语句
String sql = "UPDATE EMP SET SAL = SAL - 100 WHERE DEPTNO = ?";
PreparedStatement statement = cn.prepareStatement(sql);
statement.setInt(1, 10);
// 4.得到结果
int rowsUpdated = statement.executeUpdate();
if (rowsUpdated > 0) {
System.out.println("员工工资已成功更新。");
} else {
System.out.println("未找到符合条件的员工。");
}
// 5.关闭资源
cn.close();
statement.close();
}catch (Exception e){
e.printStackTrace();
}
3. 创建一个存储过程,功能为将指定雇员号的雇员工资改为新工资
create or replace procedure UP_sal(epno number,newsal number)
as
begin
update EMP set SAL = newsal where EMPNO = epno;
end;
4. 利用JDBC远程连接的方式在Java中掉用此过程
try {
// 1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2. 得到数据库连接
Connection connection = DriverManager.getConnection
("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","801322");
//3.创建 CallableStatement
CallableStatement cs = connection.prepareCall("{call UP_sal(?,?)}");
//4.给?赋值
cs.setDouble(1,7788);
cs.setDouble(2, 3000);
//5.执行
cs.execute();
System.out.println("存储过程已执行");
// 6.关闭连接和释放资源
cs.close();
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}