经典_Oracle的sql语句百例训练

Oracle系列《一》：简单SQL与单行函数

使用s

cott/tiger用户下的emp表和dept表完成下列练习，表的结构说明如下

emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)

dept部门表(deptno部门编号/dname部门名称/loc地点)

工资 ＝ 薪金 ＋ 佣金 登录Oracle数据库 1、sqlplus scott/tiger 2、sqlplus /nolog SQL>conn scott/tiger

若是使用SYS的账号进行登录的话，则使用以下语句 SQL>conn / as sysdba

【1】EMP表内容查询

SQL> SELECT * FROM emp;

出错，原因是没有找到该表，因为该表时SCOTT用户的表，所以查询时应该加上scott.emp就可以了

【2】显示当前用户

SQL> show user

【3】查看当前用户的所有表 SQL> SELECT * FROM tab;

【4】若想重复执行上一条SQL语句，则在sqlplus命令行下输入\即可

【5】查询一张表的结构，例如dept表 SQL> desc dept

【6】在雇员表中查询雇员的编号、姓名、工作 SQL> SELECT empno,ename,job FROM emp;

【7】可以为列名取别名，在Linux下Oracle如果英文别名不加上双引号则会变成大写

SQL> SELECT empno 编号,ename 姓名,job 工作 FROM emp;

【8】查询所有的工作

SQL> SELECT DISTINCT job FROM emp; 工作可能会重复，加上DISTINCT关键字

【9】若要求按照以下的格式进行结果输出，如

NO:7469,Name:SMITH,Job:CLERK

SQL> SELECT 'NO:'||empno||',Name:'||ename||',Job:'||job FROM emp;

【10】要求列出每个雇员的姓名及年薪

SQL> SELECT ename,sal*12 income FROM emp; 这里年薪最好用别名进行标识，可以一眼就能明白

【11】查看每月可以得到奖金的雇员信息

SQL> SELECT * FROM emp WHERE comm is NOT NULL;

【12】要求基本工资大于1500，同时可以领取奖金的雇员信息

SQL> SELECT * FROM emp WHERE sal>1500 AND comm is NOT NULL; 如果是或的是关系，则使用 OR

【13】查询基本工资不大于1500，同时不可以领取奖金的雇员信息

SQL> SELECT * FROM emp WHERE NOT(sal>1500 AND comm is NOT NULL);

【14】查询在1981年雇佣的全部雇员信息，BETWEEN .. AND 包含等于的情况

SQL> SELECT * FROM emp

WHERE hiredate BETWEEN '01-JAN-81' AND '31-DEC-81';

【15】Oracle对大小敏感，所以查询时名字要区分大小写

【16】要求查询出雇员编号不是 7369、7499的雇员信息 SQL> SELECT * FROM emp

WHERE empno NOT IN(7369,7499);

【17】SQL中LIKE语句要注意通配符 % 和 _ SQL> SELECT * FROM emp

WHERE hiredate LIKE '?%';

【18】查看雇员编号不是7369的雇员信息，使用<>或!= SQL> SELECT * FROM emp WHERE empno<>7369;

【19】要求对雇员的工资由低到高进行排序，升序为默认(ASC)，降序(DESC) SQL> SELECT * FROM emp GROUP BY sal;

【20】查看出部门号为10的雇员信息，查询的信息按照工资从高到低，若工资相等则按雇用日期从早到晚排列 SQL> SELECT * FROM emp WHERE deptno=10

GROUP BY sal DESC,hiredate ASC;

数据库系统中，每个数据库之间区别最大的就是在函数的支持上，单行函数是最简单的函数，单行函数分为

1、字符函数：接受字符输入并且返回字符或数值 2、数值函数：接受数值输入并返回数值 3、日期函数：对日期型数据进行操作

4、转换函数：将一种数据类型转换为另一种数据类型 5、通用函数：NVL、DECODE 函数

字符函数：

【1】大小写转换 UPPER 和 LOWER

SQL> SELECT UPPER('smith') FROM dual; 【2】将雇员姓名变为开头字母大写，INITCAP SQL> SELECT INITCAP(ename) FROM emp;

字符函数中有连接函数CONCAT，但不如 || 好用，还有字符串处理的一些函数 字符串截取：substr() 字符串长度：length() 内容替换：replace()

SQL> SELECT

substr('hello',1,3),length('hello'),replace('hello','l','x') FROM dual;

这里注意的是Oracle中字符串截取从0和从1开始都是一样的，谨防面试提问

【3】要求显示所有雇员的姓名及姓名的后3个字符

SQL> SELECT ename,SUBSTR(ename,LENGTH(ename)-2) FROM emp; 以上操作显得较为麻烦，substr()函数是可以倒着截取 SQL> SELECT ename,SUBSTR(ename,-3,3) FROM emp;

数值函数：

1、四舍五入：ROUND() 2、截断小数位：TRUNC() 3、取余(取模)：MOD

SQL> SELECT ROUND(789.536) FROM dual;

【1】保留2位小数,(如果是-2则对整数进行四舍五入，变为800了) SQL> SELECT ROUND(783.56,2) FROM dual; 【2】使用MOD()函数进行取余操作 SQL> SELECT MOD(10,3) FROM dual;

日期函数：

1、日期 - 数字 = 日期 2、日期 + 数字 = 日期 3、日期 - 日期 = 数字(天数) 【1】求出当前日期

SQL> SELECT SYSDATE FROM dual;

Oracle提供了以下的日期函数支持：

MONTHS_BETWEEN():求出给定日期范围的月数

ADD_MONTHS():在指定日期上加上指定的月数，求出之后的日期 NEXT_DAY():下一个的今天的日期

LAST_DAY():求出给定日期的最后一天日期

【2】求出从雇用日期到今天所有雇员的雇员编号、姓名和月数

SQL> SELECT

empno,ename,ROUND(MONTHS_BETWEEN(SYSDATE,hiredate)) FROM emp;

【2】按部门分组，并显示部门的名称，及每个部门的员工数 SQL> SELECT d.dname,COUNT(e.empno) FROM emp e,dept d

WHERE e.deptno=d.deptno GROUP BY d.dname;

【3】要求显示平均工资大于2000的部门编号和平均工资 SQL> SELECT deptno,AVG(sal) FROM emp

WHERE AVG(sal)>2000 GROUP BY deptno;

出错，WHERE子句中不能出现分组函数的条件，要使用HAVING子句 上述语句应该改为如下

SQL> SELECT deptno,AVG(sal) FROM emp

GROUP BY deptno

HAVING AVG(sal)>2000

【4】显示非销售人员工作名称以及从事同一工作雇员的月工资总和，并且要求从事同一工作的雇员月工资合计大于$5000, 输出结果按月工资的合计升序排序

<1>按工作分组，求出非销售人员的月工资总和 SQL> SELECT job,SUM(sal) FROM emp

WHERE job<>'SALESMAN' GROUP BY job;

<2>对分组条件进行限制，然后进行排序,HAVING子句不能使用别名 SQL> SELECT job,SUM(sal) totalSal FROM emp

WHERE job<>'SALESMAN' GROUP BY job

HAVING SUM(sal) > 5000 ORDER BY totalSal;

【3】分组函数可以嵌套使用，但是在SELECT列中就不能再出现该分组条件的列名了

SQL> SELECT deptno,MAX(AVG(sal)) FROM emp

GROUP BY deptno; 出错！修改如下

SQL> SELECT MAX(AVG(sal)) FROM emp

GROUP BY deptno;

【4】查询出比7654工资要高的全部雇员的信息 <1>首先要查询雇员编号7654的工资

SQL> SELECT sal FROM emp WHERE empno=7654; <2>以上述条件的结果最后后续查询的依据

SQL> SELECT * FROM emp

WHERE sal>(SELECT sal FROM emp WHERE empno=7654);

子查询在操作中分为以下三类：

1、单列子查询：返回的结果是一列的内容 2、单行子查询：返回多个列，也可能是一条记录 3、多行子查询：返回多个记录

【1】要求查询工资比7654高，同时与7788从事相同工作的全部雇员 SQL> SELECT * FROM emp

WHERE sal>(SELECT sal FROM emp WHERE empno=7654) AND job=(SELECT job FROM emp WHERE empno=7788);

【2】要求查询 部门名称、部门员工数、部门平均工资，部门的最低收入雇员的姓名

<1>查询部门员工数、部门平均工资

SQL> SELECT deptno,COUNT(empno),AVG(sal) FROM emp

GROUP BY deptno;

<2>查询部门的名称，及最低收入雇员姓名，要进行表关联(子查询)

SQL> SELECT d.dname,ed.c,ed.a,e.ename FROM dept d,(

SELECT deptno,COUNT(empno) c,AVG(sal) a,MIN(sal) min FROM emp

GROUP BY deptno) ed, emp e

WHERE d.deptno=ed.deptno AND e.sal = ed.min;

若上述存在两个最低工资的情况，则会出错，在子查询中存在以下3种查询的操作符号

IN：指定一个查询范围，例如查询每个部门的最低工资(返回值有多个) SQL> SELECT * FROM emp

WHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY deptno);

ANY：=ANY(与IN操作一样)、>ANY(比最小大)、 SELECT * FROM emp

WHERE sal

ALL: >ALL(比最大要大)、

SQL多列子查询示例如下

SQL> SELECT * FROM emp

WHERE (sal,NVL(comm,-1)) IN

(SELECT sal,NVL(comm,-1) FROM emp WHERE deptno=20);

数据库更新操作INSERT、UPDATE、DELETE

【1】复制一张表，例如复制EMP表为MYEMP

SQL> CREATE TABLE MYTEMP AS SELECT * FROM emp;

【2】将编号为7899的雇员的领导取消

SQL> UPDATE myemp SET mgr=null WHERE empno=7899;

【3】更新时，一定要注意不能批量更新(加上WHERE子句)，多列更新例子如下

SQL> UPDATE myemp SET mgr=null,comm=null WHERE empno IN(7369,8899);

【4】删除掉全部领取奖金的雇员

SQL> DELECT FROM emp WHERE comm is NOT NULL;

事务处理 ACID

A：Atomicity 原子性：事务中的操作或者都完成，或者都取消

C：Consistency 一致性：事务中的操作保证数据库中的数据不会出现逻辑上不一致的情况

I：Isolation 隔离性：当前的事务与其他未完成的事务是隔离的 D：Durability 持久性：在COMMIT之后，数据永久保存在数据库中，在此之前，事务的操作都可以回滚

验证事务过程：

<1>创建一张临时表，只包含部门10

SQL> CREATE TABLE emp10 AS SELECT * FROM emp WHERE empno=10; <2>删除emp10中的7782雇员

SQL> DELETE FROM emp10 WHERE empno=7782;

再打开另一个窗口，发现数据还存在，此时如果可以使用以下的两种命令进行事务处理

COMMIT 和 ROLLBACK 提交事务和回滚事务

SQL查询练习

【1】列出至少一个员工的所有部门

SQL> SELECT d.*,ed.cou FROM dept d,(

SELECT deptno,COUNT(empno) cou FROM emp GROUP BY deptno

HAVING COUNT(empno) > 1) ed WHERE d.deptno=ed.deptno;

【2】列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

SQL> SELECT d.deptno,d.dname,e.empno,e.ename FROM dept d,emp e

WHERE d.deptno = e.deptno(+);

【3】列出所有\办事员)的姓名及其部门名称，部门的人数 <1>关联dept表

SQL> SELECT e.ename,d.dname FROM emp e,dept d

WHERE e.deptno=d.deptno and e.job='CLERK'; <2>使用GROUP BY 完成部门分组人数

SQL> SELECT e.ename,d.dname,ed.cou FROM emp e,dept d,( SELECT deptno,COUNT(empno) cou FROM emp GROUP BY deptno) ed

WHERE job='CLERK' AND e.deptno=d.deptno AND ed.deptno=e.deptno;

Oracle系列《三》：表、（约束）索引、序列、视图的使用 一、创建、删除、修改表

建立表：Oracle中主要数据类型 VARCHAR2、NUMBER、DATE、CLOB(大量文本)、BLOB(图片、音乐、电影)

如果只能复制一张表的结构，但不复制内容，则加上一个不可能成立的条件即可，例如

SQL> CREATE TABLE tmp AS (SELECT * FROM emp WHERE 1==2)

例如创建表Person如下：

CREATE TABLE person( pid VARCHAR2(18), name VARCHAR2(30), age NUMBER(3), birthday DATE,

sex VARCHAR(2) DEFAULT 'M' );

本文来源：https://www.bwwdw.com/article/ibx.html