Oracle学习笔记

蒲苇zjl

已于 2023-12-15 10:30:03 修改

阅读量383

点赞数 7

文章标签： oracle

于 2023-12-02 19:03:57 首次发布

本文链接：https://blog.csdn.net/weixin_62890914/article/details/134755865

版权

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();
        }
}

蒲苇zjl

关注

7
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
Oracle学习笔记

insert into 表名(列名1，列名2……) values(值)；system把该方案的备份导入到SCOTT用户。Scott创建一个跟EMP表的一样的表EMP1。Scott删除了EMP1表中的所有元组。system导出SCOTT的方案。做了一个该表的备份存放在某路径下。SYSTEM把该方案导入给自己。修改字段（修改数据类型）SCOTT导出自己的方案。number:数值类型。导入备份，只导入数据。
复制链接

扫一扫