Oracle复习

一．创建用户

在YGGL数据库中创建一个用户MANAGER，授予DBA角色和SYSDBA系统权限，它可以替代system系统用户。

CONNECT SYSTEM/123456 AS SYSDAB

CREATE USER MANAGER

PROFILE "DEFAULT"

IDENTIFIED BY manager

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP"

ACCOUNT UNLOCK;

GRANT SYSDBA TO "MANAGER";

GRANT "COUNTNECT" TO "MANAGER";

GRANT "DBA" TO "MANAGER";

ALTER user MANGER GRANT CONNECT THROUGH SYSTEM;

二：创建角色

在YGGL数据库中创建一个角色ADMIN，授予DBA角色和SYSDBA系统权限。CREATE ROLE ADMIN

IDENTIFILED BY "manager";

GRANT SYSDAB TO ADMIN ;

GRANT SYSOPER TO ADMIN;

GRANT "CONNECT" TO ADMIN;

GRANT "DBA" TO "ADMIN";

一．创建概要文件

在YGGL数据库中创建概要文件YGGL_PROFILE并分配给用户MANAGER。CREATE PROFILE "XSCJ _PROFILE"LIMIT

COMPOSITE_LIMIT DEFAULT FAILED_LOGIN_ATTEMPTS 3

PASSWORD_LOCK_TIME 20 PASSWORD_GRACE_TIME 60

PASSWORD_LIFE_TIME 30 PASSWORD_REUSE_MAX UNLIMITED PASSWORD_REUSE_TIME 120 PASSWORD_VERIFY_FUNCTION DEFAULT （1）使用命令方式分别向YGGL数据库个表中插入一行记录

INSERT INTO ZPEMPLOYEES V ALUES('011112','罗林

',TO_DATE('26-06-1973','DD-MM-YYYY'),1,'解放路100 ',210002,4055663,5); INSERT INTO ZPDEPARTMENTS V ALUES('6','人力资源',NULL);

INSERT INTO ZPSALARY

V ALUES ('011112',1200.09,50)

（2）修改表salry的记录值

UPDATE ZPSALARY SET InCome=2890 WHERE EmployeeID='011112';

（3）删除Salary中的一行记录

DELETE FROM ZPSALARY WHERE EmployeeID='01112';

3.索引和完整性

(1).对YGGL数据库中的Employees表的DepartmentID列建立索引

CREATE INDEX PK_XS_BAK ON ZPEMPLOYEES(DepartmentID)

TABLESPACE USERS PCTFREE 48 INITRANS 10 MAXTRANS 100

STORAGE (INITIAL 64K NEXT 64K MINEXTENTS 5 MAXEXTENTS 20

PCTINCREASE 10 FREELISTS 1 FREELIST GROUPS 1)

PARALLEL(DEGREE DEFAULT);

(2).实体完整性

使用SQL语句创建表Departments，DepartmentId列为主键

CREATE TABLE ZPDEPARTMENTS

(

DepartmentID CHAR(3) NOT NULL,

DepartmentName CHAR(20) NOT NULL,

Note VARCHAR2(100) NULL

CONTRAINT PK_ZPDEPARTMENTS PRIMARY KEY(DepartmentID)

);

(3).实现参照完整性

为Employees表中的DepartmentID列建立外键

ALTER TABLE ZPEMPLOYEES

ADD(CONSTRAINT FK_ZPEMPLOYEES FOREIGN KEY (DepartmentID)

REFERENCES ZPDEPARTMENTS (DepartmentID));

1.SELECT语句的基本使用

（1）SELECT * FROM ZPEMPLOYEES;

（2）查询每个雇员的地址和电话

SELECT Address,PhoneNumber FROM ZPEMPLOYEES；

（3）查询EmployeeId为000001的雇员的地址和电话

SELECT Address,PhoneNumber FROM ZPEMPLOYEES; WHERE EmployeeID='000001' （4）查询Employees表中所有女雇员的地址和电话。

SELECT Address AS 地址 ,PhoneNumber AS 电话FROM ZPEMPLOYEES WHERE sex=0;

（5)计算每个雇员的实际收入

SELECT EMPLOYEEID,INCOME-OUTCOME AS 实际收入FROM ZPSALARY;

（6）找出所有姓王的的雇员的部门号

SELECT DEPARTMENTID FROM ZPEMPLOYEES WHERE NAME LIKE'王%';

（7）找出所有收入在2000元-3000元之间的雇员号码

SELECT EMPLOYEEID FROM ZPSALARY WHERE INCOME BETWEEN 2000 AND 3000;

2.子查询的使用

（1）查找在财务部工作的雇员的情况

SELECT * FROM ZPEMPLOYEES WHERE DEPARTMENTID =

(

SELECT DEPARTMENTID

FROM ZPDEPARTMENTS

WHERE DEPARTMENTNAME = '财务部'

);

（2）查找财务部年龄不低于所有研发部雇员年龄的雇员的姓名

SELECT NAME FROM ZPEMPLOYEES WHERE DEPARTMENTID IN

(

SELECT DEPARTMENTID FROM ZPDEPARTMENTS

WHERE DEPARTMENTNAME = '财务部'