数据库复习（复习用）
/*
数据库对象
1 function 函数
2 trigger 触发器
3 table 表格
4 index 索
5 view 视图
6 synonym 同义词
7 tablespace 表空间
8 user 用户
9 sequence 系列
*/
/*
Oracle 数据类型
字符串 : varchar2 超过4000个字节使用long
整数/小数： number(p,s) p代表精度 s代表小数位数
日期: data
*/
–授权
GRANT CONNECT,RESOURCE TO CS;
–取消授权
REVOKE CONNECT FROM CS;
nvl(a,‘b’) 如果a为null ，就用b替代
SELECT * FROM SCOTT.EMP
–复制表
CREATE TABLE NEMP
AS
SELECT * FROM EMP;
–复制表格式
CREATE TABLE NNEMP
AS
SELECT * FROM EMP WHERE 1=2;
–去重复 DISTINCT
INSERT INTO SCOTT.EMP ()
SELECT , FROM DUAL
UNION
REPLACE 替换
EXTRACT(MONTH FROM HIREDATE) 得到月份
–转varchar2
TO_CHAR()
SELCT TO_CHAR() FROM DUAL;
–转数字
TO_NUMBER
SELCT TO_NUMBER() FROM DUAL;
–转日期
TO_DATE
SELECT TO_DATE() FROM DUAL;
–得到本机当前日期
SELECT SYSDATE FROM DUAL;
SELECT SYSTIMESTAMP FROM DUAL;
–月份增加
SELECT ADD_MONTHS(SYSDATE,2) FROM DUAL
–日期之间的月份
SELECT MONTHS_BETWEEN(SYSDATE,SYSDATE) FROM DUAL;
–得到年月日
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
SELECT EXTRACT(MONTHS FROM SYSDATE) FROM DUAL;
SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL;
–本月最后一天
SELECT LAST_DAY(SYSDATE) FROM DUAL;
–下一个星期一
SELECT NEXT_DAY (SYSDATE,‘星期一’) FROM DUAL;
–超过12点为下一天
SELECT ROUND (SYSDATE) FROM DUAL;
–当前日期
SELECT TRUNC (SYSDATE) FROM DUAL;
–字符长度
SELECT LENGTH(’’) FROM DUAL;
–全大写
SELECT UPPER(‘ADADSa’) FROM DUAL;
–全小写
SELECT LOWER(’’) FROM DUAL;
–替换
SELECT REPLACE(’’,A,B) FROM DUAL;
–从第2位开始截取后面的全部
SELECT SUBSTR(‘A123123’,2) FROM DUAL;
–从第2位开始截取3位
SELECT SUBSTR(’’,2,3) FROM DUAL;
–拼接
SELECT CONCAT(’’,’’) FROM DUAL;
–首字母大写
SELECT INITCAP(’’) FROM DUAL;
–去两边 L为右边，r为左边
SELECT LTRIM(‘1a123’,‘1’) FROM DUAL;
SELECT RTRIM(’’,’’) FROM DUAL;
–得到字符的出现位置
SELECT INSERT(’’,’’) FROM DUAL;
SELECT INSERT (’’,’’,19) FROM DUAL;
–得到ascii码
SELECT ASCII(‘A’) FROM DUAL;
–ascii码转
SELECT CHAR(89) FROM DUAL;
–填充
SELECT LPAD(‘123’,7,‘X’) FROM DUAL;
SELECT RPAD(‘123’,7,‘X’) FROM DUAL;
–自定义 和if 条件差不多
SELECT DECODE(‘G’,‘G’,‘2’,‘A’,5) FROM DUAL;
–绝对值
SELECT ABS(-23) FROM DUAL;
SELECT POWER(4,3) FROM DUAL;–幂值
SELECT SQRT(225) FROM DUAL;–平方根
SELECT MOD(10,3) FROM DUAL;–得余数
SELECT CEIL(23.42) FROM DUAL;–向上得整
SELECT FLOOR(22.22) FROM DUAL;–向下得整
SELECT SIGN(0) FROM DUAL;–大于0为1，等于0位0，小于0为-1
SELECT ROUND (23.234,1) FROM DUAL;–小数后一位，四舍五入
SELECT TRUNC(22.361,1) FROM DUAL;–取有效字
DECLARE
SNO NUMBER(8);
SNAME VARCHAR2(22);
BEGIN
SELECT ENAME FROM SCOTT.EMP WHERE EMPTNO=SNO;
END;
–赋值方式(两种)
SNO:=12;
SELECT ENAME INTO EN FROM SCOTT.EMP WHERE EMPTNO=SNO;
–引用数据类型
SDATE SCOTT.EMP.HIREDATE%TYPE;
–引用整行的数据类型
SALL SCOTT.EMP%ROWTYPE;
IF条件结构
BEGIN
IF() THEN
ELSIF() THEN
ELSE
END;
–键盘输入值
SNO NUMBER(7):=’&SNO’
CASE 结构
BEGIN
CASE ‘’
WHEN … THEN …;
WHEN … THEN …;
AND CASE;
END;
–LOOP 循环
DECLARE
I NUMBER(5):=1;
BEGIN
<>
LOOP
EXIT AA WHERE I=100;
END LOOP;
END;
–while循环
DECLARE
BEGIN
WHILE(I<30) LOOP
END LOOP;
END;
–for循环
for i in 1…100 loop
end loop;
–自定义异常
DECLARE
SNO NUMBER (7):=1122;
SSAL EMP.SAL%TYPE;
–定义异常
MYE EXCEPTION;
BEGIN
SELECT SAL FROM EMP WHERE EMPTNO=SNO;
if
–抛出异常
RAISE MYE;
else
–处理异常
EXCEPTION WHEN MYE THEN
–输出
END;
CREATE OR REPLACE FUNCTION MY(SNO NUMBER)（不能带长度）
RETURN VARCHAR2（不能带长度）
IS
–局部变量
BEGIN
END;
–调用行数的两种方法
SELECT MYF(123) FROM DUAL;
BEGIN
DBMS_OUTPUT.PUT_LINE(MYF(123))
END;
ORACLE题集
1.基于scott方案中的emp表的empno、ename和job字段创建一新的雇员表，包括编号、姓名和工作三个字段。雇员表中的编号、
姓名和工作字段分别来自于scott方案中emp表的empno、ename和job字段。
CREATE TABLE NEMP
AS
SELECT EMPNO,ENAME,JOB FROM SCOTT.EMP
SELECT * FROM NEMP
2.创建一个出版社表Perss，其表结构如下：
publishID char(8) 出版社编号
publishName varchar2(50) 出版社名称
publishAddress varchar2(50) 出版社地址
publishPhoneNo varchar2(15) 出版社电话
publishEmail varchar2(30) 出版社电子邮箱
CREATE TABLE NNEMP(
publishID char(8),
publishName varchar2(50),
publishAddress varchar2(50) ,
publishPhoneNo varchar2(15),
publishEmail varchar2(30)
)
SELECT * FROM NNEMP
3.编写SQL代码向出版社表Perss中添加一列publishCredit，类型varchar2(20)，默认值good。
ALTER TABLE NNEMP ADD (publishCredit VARCHAR2(20) DEFAULT ‘GOOD’)
4.编写SQL代码删除出版社表Perss中的publishCredit列。
ALTER TABLE NNEMP DROP (publishCredit)
5.编写SQL代码删除一张表。
DROP TABLE NNEMP
6.向已创建的出版社表Perss中插入记录：
20101001 清华出版社 北京 010-12345733 12345@qq.com
20101002 电子工业大学出版社 河南 0371-6666666 soft@cnzz.com
INSERT INTO NNEMP
SELECT ‘20101001’,‘清华出版社’,‘北京’,‘010-12345733’,‘12345@qq.com’ FROM DUAL UNION
SELECT ‘20101002’, ‘电子工业大学出版社’, ‘河南’, ‘0371-6666666’, ‘soft@cnzz.com’ FROM DUAL
7.将出版社Perss表中“电子工业大学出版社”的电话修改为“0371-9999999”。
UPDATE NNEMP SET publishPhoneNo=‘0371-9999999’ WHERE publishName=‘电子工业大学出版社’;
SELECT * FROM NNEMP
8.删除出版社Perss表中“清华出版社”的记录。
DELETE NNEMP WHERE publishName=‘清华出版社’
简单查询
9.查询scott.emp表中所有列。
SELECT * FROM SCOTT.EMP
10.查询scott.emp表中的ename列。
SELECT ENAME FROM SCOTT.EMP
11.查询scott.emp表中ename列，将其列名改为“雇员姓名”。
SELECT ENAME AS 雇员姓名 FROM SCOTT.EMP
12.查询scott.emp表中sal、comm两项工资的总和。
SELECT SAL+NVL(COMM,0) FROM SCOTT.EMP
13.消除emp表中job列的重复值。
–去重复
SELECT DISTINCT JOB FROM SCOTT.EMP
14.查看scott.emp表中在10号部门的所有员工具体情况。
SELECT * FROM SCOTT.EMP WHERE DEPTNO=10
15.查询scott.emp表中工资在1500和3000之间的雇员编号。
SELECT * FROM SCOTT.EMP WHERE SAL BETWEEN 1500 AND 3000
16.查询scott.emp表中在10、20或30号部门工作的雇员编号。
SELECT * FROM SCOTT.EMP WHERE DEPTNO=10 OR DEPTNO =20 OR DEPTNO=30
17.查询scott.emp表中名字中含有K字母的雇员姓名。
SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE ‘%K%’
18.查询comm列为NULL的雇员信息。
SELECT * FROM SCOTT.EMP WHERE COMM IS NULL
19.查询10号部门工资在1000元以上的雇员信息。
SELECT * FROM SCOTT.EMP WHERE DEPTNO =10 AND SAL>1000
20.将emp表按照部门号升序、员工编号降序排列。
SELECT * FROM SCOTT.EMP ORDER BY DEPTNO ,EMPNO DESC
21.统计scott.emp表中各部门的人数。
SELECT DEPTNO,COUNT(DEPTNO) FROM SCOTT.EMP GROUP BY DEPTNO
22.统计各部门人数，将多于5人的部门编号输出。
SELECT DEPTNO,COUNT(DEPTNO) FROM SCOTT.EMP GROUP BY DEPTNO HAVING COUNT(*)>=5
23.将scott.emp表中的内容复制一份到newemp表。
CREATE TABLE NEWEMP
AS
SELECT *FROM SCOTT.EMP
SELECT *FROM NEWEMP
DROP TABLE NEWEMP
内置函数
24.查看A的ASCII码。
SELECT ASCII(‘A’) FROM DUAL
25.查看ASCII码为65的字符。
SELECT CHR(65) FROM DUAL
26.使用LENGTH函数求字符串‘This is Oracle 11g’。
SELECT LENGTH(‘This is Oracle 11g’) FROM DUAL
27.使用CONCAT函数将EMP表中各元组的ename添加在job之后。
SELECT CONCAT(JOB,ENAME) FROM EMP;
28.使用SUBSTRING函数、SUBSTR函数
SELECT SUBSTR(‘12345’,3) FROM DUAL
29.使用MOD函数求8除以3的余数。
SELECT MOD(8,3) FROM DUAL
30.使用ROUND函数取整。
SELECT ROUND(10.01) FROM DUAL
31.聚合函数：
求scott.emp表中sal+500后的平均值。
SELECT AVG(SAL+500) FROM SCOTT.EMP
求scott.emp表中不同sal值的平均值。
SELECT AVG(SAL) FROM SCOTT.EMP
求scott.emp表中sal的最大值和最小值。
SELECT MAX(SAL),MIN(SAL) FROM SCOTT.EMP
求scott.emp表所有员工的人数。
SELECT COUNT(*) FROM SCOTT.EMP
求scott.emp表sal列之和
SELECT SUM(SAL) FROM SCOTT.EMP
32.日期函数：
求当前系统时间。
SELECT SYSDATE,SYSTIMESTAMP FROM DUAL
求当前月份的最后一天。
SELECT LAST_DAY(SYSDATE) FROM DUAL
求当前日期与2016年12月20日之间相隔的月数。
SELECT MONTHS_BETWEEN(SYSDATE,TO_DATE(‘2016-12-20’,‘YYYY-MM-DD’)) FROM DUAL
求下一个星期一的日期。
SELECT NEXT_DAY(SYSDATE,‘星期一’) FROM DUAL
求你现在的年龄。
SELECT 2022- EXTRACT(YEAR FROM TO_DATE(‘2003-06-07’,‘YYYY-MM-DD’)) FROM DUAL
33.转换函数：
将123转换成字符串。
SELECT TO_CHAR(123) FROM DUAL
将’123’转换成数值型。
SELECT TO_NUMBER(‘123’) FROM DUAL;
将当前日期转换成日期型。
SELECT TO_DATE(‘2022-12-2’,‘YYYY-MM-DD’) FROM DUAL
将‘970.13’转换成数值型后再加上100。
SELECT TO_NUMBER(‘970.12’)+100 FROM DUAL
多表连查
34.查询scott.emp表各雇员信息与其所在部门信息。
SELECT A.
,B.
FROM SCOTT.EMP A
JOIN
SCOTT.DEPT B
on A.DEPTNO=B.DEPTNO
WHERE 1=1
35.查询scott.emp表各雇员名字和所在部门名称。
SELECT A.ENAME,B.DNAME FROM SCOTT.EMP A
JOIN
SCOTT.DEPT B
ON A.DEPTNO=B.DEPTNO
WHERE 1=1
36.查询比SMITH工资高的雇员姓名。KING
SELECT * FROM SCOTT.EMP WHERE SAL > (SELECT SAL FROM SCOTT.EMP WHERE ENAME =‘WARD’)
37.查询各雇员名字和所在部门名称。
SELECT A.ENAME,B.DNAME FROM SCOTT.EMP A
JOIN
SCOTT.DEPT B
ON A.DEPTNO=B.DEPTNO
WHERE 1=1
嵌套查询（子查询）
38.查询SMITH所在部门的详细信息。
SELECT * FROM SCOTT.DEPT WHERE DEPTNO IN(
SELECT DEPTNO FROM SCOTT.EMP WHERE ENAME=‘KING’)
39.查询与SMITH在同一部门的员工信息。
SELECT * FROM SCOTT.EMP WHERE DEPTNO IN(
SELECT DEPTNO FROM SCOTT.EMP WHERE ENAME=‘KING’)
40.找出其他部门比10号部门某员工工资高的雇员信息。
SELECT * FROM SCOTT.EMP WHERE DEPTNO NOT IN(
SELECT DEPTNO FROM SCOTT.EMP WHERE ENAME =‘CLARK’)
AND SAL>
(SELECT SAL FROM SCOTT.EMP WHERE ENAME=‘CLARK’)
41.找出SMITH所在部门的详细信息。
SELECT * FROM SCOTT.DEPT WHERE DEPTNO IN(
SELECT DEPTNO FROM SCOTT.EMP WHERE ENAME=‘FORD’)
42.查看职务为CLERK或SALESMAN的雇员信息。
SELECT * FROM SCOTT.EMP WHERE JOB=‘CLERK’ OR JOB=‘SALESMAN’
高级查询
43.查询所有员工的人数和他们的平均工资
SELECT COUNT(*),AVG(SAL) FROM SCOTT.EMP
44.查询部门编号为20的所有员工信息
SELECT * FROM SCOTT.EMP WHERE DEPTNO=20
45.查询部门编号为20，部门中工资最高的员工信息
SELECT * FROM SCOTT.EMP WHERE SAL IN(
SELECT MAX(SAL) FROM SCOTT.EMP WHERE DEPTNO=20) AND DEPTNO =20
46.查询将金COMM高于工资SAL 20%的员工信息
SELECT * FROM SCOTT.EMP WHERE COMM>(SAL*0.2)
47.查询所有有奖金的员工信息
SELECT * FROM SCOTT.EMP WHERE COMM IS NOT NULL;
48.查询工龄大于或等于10年的员工信息
SELECT * FROM SCOTT.EMP WHERE (EXTRACT(YEAR FROM HIREDATE)+10)<1980
49.查询员工名正好为8个字母的员工信息
SELECT * FROM SCOTT.EMP WHERE LENGTH(ENAME)=4
50.查询员工的姓名和工资，并按工资降序排序
SELECT ENAME,SAL FROM SCOTT.EMP ORDER BY SAL DESC
51.查询所有员工的姓名、部门号、工资，结果先按部门号升序排序，再按工资降序排序
SELECT ENAME,DEPTNO,SAL FROM SCOTT.EMP ORDER BY DEPTNO,SAL DESC
52.查询各个部门中的员工的人数和平均资
SELECT DEPTNO,COUNT(*),AVG(SAL) FROM SCOTT.EMP GROUP BY DEPTNO
53.统计各个工种的员工的人数和平均工资
SELECT JOB,COUNT(*),AVG(SAL) FROM SCOTT.EMP GROUP BY JOB
PL/SQL编程：
54.根据scott.emp表中DEPTNO字段的值，为姓名为SMITH的雇员修改工资；若部门号为10，则工资加100；若部门号为20， WARD
则工资加300；否则工资加400。
SELECT * FROM SCOTT.EMP WHERE ENAME=‘WARD’
DECLARE
DEP SCOTT.EMP.DEPTNO%TYPE;
BEGIN
SELECT DEPTNO INTO DEP FROM SCOTT.EMP WHERE ENAME=‘WARD’;
IF(DEP=10) THEN
UPDATE SCOTT.EMP SET SAL=SAL+100 WHERE ENAME=‘WARD’;
DBMS_OUTPUT.PUT_LINE(‘100’);
ELSIF(DEP=20) THEN
UPDATE SCOTT.EMP SET SAL=SAL+300 WHERE ENAME=‘WARD’;
DBMS_OUTPUT.PUT_LINE(‘300’);
ELSE
UPDATE SCOTT.EMP SET SAL=SAL+400 WHERE ENAME=‘WARD’;
DBMS_OUTPUT.PUT_LINE(‘400’);
END IF;
END;
55.将ename、sal、comm、hiredate分别改用“雇员姓名”、“薪水”、“红利”和“聘用日期”。
SELECT ENAME 雇员姓名,SAL 薪水,COMM 红利,HIREDATE 聘用日期 FROM SCOTT.EMP ;
函数
-
定义一个函数，求1+2+3+……+100的和并返回。
CREATE OR REPLACE FUNCTION F
RETURN NUMBER
IS
S NUMBER(8):=0;
I NUMBER (8):=1;
BEGIN
WHILE(I<=100) LOOP
S:=S+I;
I:=I+1;
END LOOP;
RETURN S;
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(F);
END;
-
定义一个函数，根据给定的员工编号返回scott.emp表中员工的姓名。
CREATE OR REPLACE FUNCTION F(X NUMBER)
RETURN VARCHAR2
IS
SNAME EMP.ENAME%TYPE;
BEGIN
SELECT ENAME INTO SNAME FROM EMP WHERE EMPNO=X;
RETURN SNAME;
END;
SELECT F(7521) FROM DUAL
-
定义一个函数，根据给定的部门编号返回scott.emp表中该部门员工的总数和该部门员工的最低月薪和最高月薪。
CREATE OR REPLACE FUNCTION F(X NUMBER)
RETURN VARCHAR2
IS
RS NUMBER;
MA NUMBER;
MI NUMBER;
BEGIN
SELECT COUNT(*),MAX(SAL),MIN(SAL) INTO RS,MA,MI FROM EMP WHERE DEPTNO=X;
RETURN ‘人数:’||RS||‘最高工资:’||MA||‘最低工资:’||MI;
END;
SELECT F(10) FROM DUAL
59. 在scott.emp表上创建一个函数deptavgsal，输入参数为部门编号，返回该部门的平均工资。
CREATE OR REPLACE FUNCTION DEPTAVGSAL(X NUMBER)
RETURN NUMBER
IS
ASL EMP.SAL%TYPE;
BEGIN
SELECT AVG(SAL) INTO ASL FROM EMP WHERE DEPTNO=X;
RETURN ASL;
END;
SELECT DEPTAVGSAL(10) FROM DUAL;
-
在scott.emp表上创建一个函数deptsal，输入参数为员工编号，返回该员工所在部门的平均工资。
CREATE OR REPLACE FUNCTION DEPTSAL(X NUMBER)
RETURN NUMBER
IS
GZ NUMBER;
BEGIN
SELECT AVG(SAL) INTO GZ FROM EMP WHERE DEPTNO IN(
SELECT DEPTNO FROM EMP WHERE EMPNO=X);
RETURN GZ;
END;
SELECT DEPTSAL(7782) FROM DUAL
61. 在scott.emp表上创建一个函数deptnum，输入部门编号，返回该部门的员工人数，并调用该函数。
CREATE OR REPLACE FUNCTION DEPTNUM(X NUMBER)
RETURN NUMBER
IS
RS NUMBER;
BEGIN
SELECT COUNT(*) INTO RS FROM EMP WHERE DEPTNO=X;
RETURN RS;
END;
SELECT DEPTNUM(10) FROM DUAL
62. 删除上题创建的函数
DROP FUNCTION DEPTNUM
触发器
-
创建一个触发器FirstTrigger，当用户删除scott.emp表中的数据时提示：有用户删除了emp表中数据。
CREATE OR REPLACE TRIGGER FIRSTTIRGGER
AFTER DELETE
ON EMP
FOR EACH ROW
BEGIN
IF(:OLD.ENAME=‘WARD’) THEN
RAISE_APPLICATION_ERROR(-20001,‘有用户删除了emp表中的数据’);–错误代号只能以2开头
END IF;
END;
DELETE FROM EMP WHERE ENAME=‘WARD’
64. 创建一个触发器MyTrigger，它的作用是当表scott.dept中的deptno列的值发生变化时，自动更新表scott.emp中的deptno列的值，从而保证数据的完整性。
CREATE OR REPLACE TRIGGER TIG
AFTER UPDATE
ON DEPT
FOR EACH ROW
DECLARE
XX NUMBER;
OL NUMBER;
BEGIN
IF(:NEW.DEPTNO!=10 OR :NEW.DEPTNO!=20 OR:NEW.DEPTNO!=30 OR :NEW.DEPTNO!=40) THEN
XX:= :NEW.DEPTNO;
OL:= :OLD.DEPTNO;
UPDATE EMP SET DEPTNO=XX WHERE DEPTNO=OL;
END IF;
END;
UPDATE DEPT SET DEPTNO=50 WHERE DEPTNO=10;
SELECT * FROM DEPT
65. 在scott.dept表上创建一个触发器，当删除该表中的记录时，可以同时删除EMP表中记录，保证参照完整性。–在触发触发器时会把值传进来
CREATE OR REPLACE TRIGGER TIGG
AFTER DELETE
ON DEPT
FOR EACH ROW
DECLARE
DEP DEPT.DEPTNO%TYPE;
BEGIN
DEP:= :OLD.DEPTNO;
DELETE FROM EMP WHERE DEPTNO=DEP;
END;
DELETE DEPT WHERE DEPTNO=30;
SELECT * FROM EMP
- 删除上题中创建的触发器。
DROP TRIGGER FIRSTTIRGGER
–查询删除的表
select * from recyclebin
–恢复删除的表 （emp为要恢复的表）
flashback table emp to before drop;
select * from emp