Java入门（七）Oracle 数据库

📢：哈喽~☀️欢迎加入程序🐒大家庭，快点开始✒️自己的黑客帝国吧 ~🌊🌊

内容简述：数据库简介、SQL基本操作、SQL分类、DML、DDL、DQL、筛选、排序、分组、聚合、关联、笛卡儿积、表之间的对应关系、权限管理、视图、索引、约束、事务。

写在前面：文章内容为个人整理，分享给大家学习使用，请勿商用哟~

• 文中查询、操作的数据库结构，在 六、附录 中可查看哟~

查看表结构的SQL：

-- 查看当前用户下的所有表及表注释
SELECT * FROM USER_TAB_COMMENTS;

-- 查看某张表的所有字段注释
SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME='';

-- 查看某张表的某个字段的注释
SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME='' AND COLUMNE_NAME='';

-- 查看某张表的所有字段的详细信息（字段类型，字段长度，字段精度，默认值等，除注释外）
SELECT * FROM USER_TAB_COLS WHERE TABLE_NAME='';

-- 查看某张表的所有字段类型
DESC XXX;

一、SQL语句、DDL语句、非空约束、修改表名、修改表结构、DML语句

Hello World

-- 打印
SELECT "Hello World" FROM dual;

SQL语句

• SQL：结构化查询语句，是用来操作数据库的语言；

• 所有的数据库都支持标准的SQL语句。

SQL语句包含：

• DDL,DML,TCL,DQL,DCL这几类语句

DDL语句：

• DDL语句是用于增删改数据库对象的

数据库对象：

• 表，视图，索引，序列

创建表:

CREATE TABLE employee(
	id NUMBER(4),
	name VARCHAR2(20),
	gender CHAR(1),
	birth DATE,
 	salary NUMBER(6,2),
 	job VARCHAR2(30),
 	deptno NUMBER(2)
)

查看表结构：

DESC employee;

删除表：

DROP TABLE employee;

大小写：

• SQL语句是不区分大小写的，但是字符串的值(直接量)是区分大小写的，字符串的直接量是使用单引号括起来的。

默认值：

• 数据库中所有数据类型的默认值都是NULL，在创建表时，可以使用DEFAULT为字段单独指定默认值。

  CREATE TABLE employee(
  	id NUMBER(4),
  	name VARCHAR2(20),
  	gender CHAR(1) DEFAULT 'M',
  	birth DATE,
   	salary NUMBER(6,2) DEFAULT 5000,
   	job VARCHAR2(30) DEFAULT 'CLERK',
   	deptno NUMBER(2)
  );
  
  DESC employee;
  

非空约束

• 当一个字段被NOT NULL修饰后，该字段在任何情况下值不能为NULL。

  CREATE TABLE employee(
  	id NUMBER(4),
  	name VARCHAR2(20) NOT NULL,
  	gender CHAR(1) DEFAULT 'M',
  	birth DATE,
  	salary NUMBER(6,2) DEFAULT 5000,
  	job VARCHAR2(30) DEFAULT 'CLERK',
  	deptno NUMBER(2)
  );
  
  -- 非空约束可以在查看表结构中体现出来
  DESC employee;
  

修改表 - 修改表名：

-- 方式一：
RENAME old_name TO new_name;
-- 方式二：
ALTER TABLE old_name TO new_name;

实践

-- 将employee表改名为myemp
RENAME employee TO myemp;

DESC myemp;

修改表结构:

1. 添加新字段

   • 向表myemp中添加字段hiredate。

   ALTER TABLE myemp ADD( hiredate DATE );
   

2. 删除表中现有字段

   • 将myemp表中的hiredate字段删除。

   -- 将myemp表中的hiredate字段删除
   ALTER TABLE myemp DROP(hiredate);
   

3. 修改表中现有字段

   • 修改字段可以修改字段的类型，长度，默认值

   ALTER TABLE myemp MODIFY( job VARCHAR2(40) DEFAULT 'CLERK');
   

注意：若表中已经存在数据，那么修改字段的时候尽量不修改类型，修改长度尽量不要缩小，否则可能导致修改失败。

DML语句

• DML语句用来对表中数据进行相关操作，包括：增，删，改。

1. 插入数据

   INSERT INTO myemp(id,name,salary,deptno) VALUES (1,'JACK',3000,10);
   

   • 插入数据时，忽略字段名则是全列插入；

   INSERT INTO myemp VALUES (2,'ROSE','F',SYSDATE,5000,'MANAGER',20);
   

   • 插入日期时，使用TO_DATE函数

   INSERT INTO myemp (id,name,birth) VALUES(3,'JACKSON',TO_DATE('1992-08-02','YYYY-MM-DD'));
   

2. 修改数据

   • UPDATE语句用于修改表中数据，需要使用WHERE添加条件以修改满足条件的记录；
   • 若不添加WHERE则是全表所有数据修改。

   -- 将ROSE的工资改为6000,部门号改为30
   UPDATE myemp SET salary=6000,deptno=30 WHERE name='ROSE'
   

3. 删除表中现有字段

   • DELETE语句用于删除表中记录，通常需要使用WHERE添加条件来删除满足条件的记录；
   • 若不添加WHERE是清空表操作。

   DELETE FROM myemp WHERE name='ROSE';
   

二、DQL语句、字符串函数、数字函数、日期类型、日期函数、空值操作、空值函数

DQL语句

• DQL语句用于查询数据库中的数据

DQL必须包含两个子句:

• SELECT：SELECT子句用来指定要查询的字段，可以是表中的字段，函数和表达式；

• FROM：FROM子句用来指定数据来源的表；

  -- 查看emp表中的数据
  SELECT * FROM emp;
  
  -- 查看ename,job,sal,deptno
  SELECT ename,job,sal,deptno FROM emp;
  

• DQL中也可以使用WHERE子句来添加过滤条件，这样只会将满足条件的记录查询出来；

  SELECT ename,job,sal,deptno FROM emp WHERE deptno=20
  

• SELECT子句中也可使用函数或表达式。

  -- 查看公司每个员工的年薪是多少?
  SELECT ename,sal,sal*12 FROM emp;
  

字符串函数

1. CONCAT(char1,char2)

   • 将两个参数字符串连接在一起返回；

   -- 示例1
   SELECT CONCAT(ename,sal) FROM emp;
   -- 示例2
   SELECT CONCAT(CONCAT(ename,','),sal) FROM emp;
   

   • "||"可以连接字符串。

   SELECT ename||','||sal FROM emp;
   

2. LENGTH(char)

   • 返回指定字符串的长度

   SELECT ename, LENGTH(ename) FROM emp;
   

补充：dual（伪表）

• 伪表不是一张真是存在的表，当查询的内容与任何表数据无关时，可以使用伪表。

  SELECT SYSDATE FROM dual;
  

3. UPPER、LOWER、INITCAP

   • 将字符串转换为大写，小写，首字母大写其余小写；

   SELECT UPPER('helloworld'), LOWER('HELLOWORLD'), INITCAP('HELLO WORLD') FROM dual;
   
   -- 打印信息
   'HELLOWORLD','helloworld','Hello World'
   

   • 可以作为查询条件。

   SELECT ename,sal,job,deptno FROM emp WHERE ename=UPPER('scott');
   

4. TRIM、LTRIM、RTRIM

   • 去除字符串两端的指定字符

   SELECT TRIM('e' FROM 'eeeliteee'), LTRIM('eddsdsesliteee','esd'), RTRIM('eeeliteddsdses','esd') FROM dual;
   
   -- 打印信息
   'lit','liteee','eeelit'
   

5. LPAD、RPAD 补位函数

   • 将指定字符串显示指定长度，当不足时补充若干个指定字符以达到该长度。

   SELECT ename,RPAD(sal,5,'$') FROM emp;
   

6. SUBSTR(char, m[, n])

   • 截取指定字符串，从m处开始连续截取n个字符；
   • n若不指定或超过实际可截取的长度，则都是截取到字符串末尾；
   • m若为负数，则是从倒数位置开始截取，数据库中下标都从1开始。

   SELECT SUBSTR('thinking in java',-7,2) FROM dual;
   

7. INSTR(char1, char2[, m[, n]])

   • 查看char2在char1中的位置；
   • m为从哪里开始查找，不写默认为1；
   • n为第几次出现，不写默认为1。

   SELECT INSTR('thinking in java','in',4,2) FROM dual;
   

数字函数

1. ROUND(n,m)

   • 四舍五入保留n小数点后m位；
   • 若m不写或0则表示保留到个位；
   • 若是负数则是保留到十位以上的数字。

   -- 保留两位小数
   SELECT ROUND(45.678, 2) FROM DUAL; -- 45.68
   -- 保留整数
   SELECT ROUND(45.678, 0) FROM DUAL; -- 46
   -- 保留到百位
   SELECT ROUND(55.678, -2) FROM DUAL; -- 100
   

2. **TRUNC()**函数

   • 与ROUND参数意义一致，作用是截取数字。

   -- 截取两位小数
   SELECT TRUNC(45.678, 2) FROM DUAL; -- 45.67
   -- 截取整数
   SELECT TRUNC(45.678, 0) FROM DUAL; -- 45
   -- 截取到十分位
   SELECT TRUNC(55.678, -1) FROM DUAL; -- 50
   

3. **MOD(m,n)**函数

   • 求余数，n为0则直接返回m。

   SELECT MOD(4321.123,1000) FROM DUAL;
   

4. CEIL、FLOOR

   • 向上取整、向下取整。

   SELECT CEIL(45.678) FROM DUAL; -- 46
   SELECT FLOOR(45.678) FROM DUAL; -- 45
   

日期类型

• 两个常用关键字：

  • SYSDATE:对应数据库一个内置函数，返回一个DATE类型数据，表示当前系统时间；
  • SYSTIMESTAMP:返回一个时间戳类型的当前系统时间。

  -- 获取系统日期 2020/12/1 9:22:59
  SELECT SYSDATE FROM DUAL;
  -- 插入系统日期
  INSERT INTO emp(empno,ename,hiredate) VALUES(1,'jack',SYSDATE);
  -- 获取系统时间戳 01-DEC-20 09.23.15.157000 AM +08:00
  SELECT SYSTIMESTAMP FROM DUAL;
  

日期转换函数

1. TO_DATE()

   • 可以将给定字符串按照指定的日期格式转换为DATE类型值。

   -- 1992/8/3 15:22:33
   SELECT TO_DATE('1992-08-03 15:22:33', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
   

   • 在日期格式字符串中凡不是英文，符号的其他字符都需要使用双引号括起来。

   -- 1992/8/3 15:22:33
   SELECT
     TO_DATE('1992年08月03日 15时22分33秒', 'YYYY"年"MM"月"DD"日" HH24"时"MI"分"SS"秒"')
   FROM DUAL;
   

2. TO_CHAR() 函数

   • 可以将DATE按照给定的日期格式转换为字符串。

   -- 2020-12-01 09:33:51
   SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
   
   -- 1958-08-03
   SELECT TO_CHAR(TO_DATE('58-08-03','RR-MM-DD'), 'YYYY-MM-DD') FROM DUAL;
   

日期类型是可以计算的

• 对一个日期加减一个数字等同于加减天数；

• 两个日期做减法，差为相差的天数。

查看明天的日期?

SELECT SYSDATE+1 FROM DUAL;

查看每个员工入职至今多少天了?

SELECT ename,SYSDATE-hiredate FROM emp;

日期函数:

1. LAST_DAY(date)

   • 返回给定日期所在月的月底日期。

   • 案例：查看当月月底的日期：

     SELECT LAST_DAY(SYSDATE) FROM DUAL;
     

2. ADD_MONTHS(date,n)

   • 对指定日期加上n个月；

   • 若n为负数，则是减去指定的月数。

   • 案例：查看每个员工的转正日期：

     SELECT ename,ADD_MONTHS(hiredate,3) FROM emp;
     

3. MONTHS_BETWEEN(date1,date2)

   • 计算两个指定日期之间相差的月；

   • 案例：查看每个员工入职至今多少个月：

     SELECT ename, MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp;
     

4. NEXT_DAY(date,i)

   • 返回给定日期第二天开始一周内指定周几的日期；

   • i可以是1-7，分别表示周日，周一…周六。

     SELECT NEXT_DAY(SYSDATE,5) FROM DUAL;
     

5. LEAST、GREATEST

   • 求最小值与最大值；

   • 对于日期而言，最大值为最晚的日期，最小值为最早的日期。

     -- 1998/8/6
     SELECT LEAST(SYSDATE, TO_DATE('1998-08-06', 'YYYY-MM-DD')) FROM DUAL;
     
     -- 2020/12/1 10:06:51
     SELECT GREATEST(SYSDATE, TO_DATE('1998-08-06', 'YYYY-MM-DD')) FROM DUAL;
     

6. EXTRACT函数

   • 获取一个日期中指定时间分量的值。

   • 案例：查看今年是哪年：

     -- 2020
     SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
     

   • 案例：查看1980年入职的员工：

     SELECT ename, hiredate
     FROM emp
     WHERE EXTRACT(YEAR FROM hiredate)=1980
     

空值操作

CREATE TABLE student(id NUMBER(4), name CHAR(20), gender CHAR(1) NOT NULL);

INSERT INTO student VALUES(1000, '李莫愁', 'F');
INSERT INTO student VALUES(1001, '林平之', NULL);
INSERT INTO student(id, name) VALUES(1002, '张无忌');

更新NULL

UPDATE student SET gender = NULL WHERE id=1000;

判断是否为NULL

• 判断要用 IS NULL 或 IS NOT NULL

  DELETE FROM student WHERE gender IS NULL;
  

NULL的运算

• NULL与字符串连接等于什么都没做；

  注意：区分于MySQL，MySQL中，NULL与字符串连接结果为NULL；

• NULL与数字运算结果还是NULL。

  SELECT '123' || NULL FROM DUAL;
  

空值函数

1. NVL(arg1, arg2)

   • 当arg1为NULL时，函数返回arg2的值，否则返回arg1自身。

     相当于MySQL 中的IFNULL()

   • 该函数意义:将NULL值替换为非NULL值

   • 查看每个员工的收入(工资+绩效)：

     SELECT ename, sal, comm, NVL(sal, 0) + NVL(comm, 0) FROM emp;
     

2. NVL2(arg1, arg2, arg3)

   • 当arg1不为NULL时，函数返回arg2，若为NULL，则函数返回arg3。

   • 案例：查看每个员工是否有绩效，即：有绩效的显示"有绩效"，为NULL的则显示为"没有绩效"。

     SELECT ename,comm,NVL2(comm,'有绩效','没有绩效') FROM emp;
     

练习：

1:查询emp表中数据，列出一列，内容为名字与职位， 显示格式：ename:job

2:查看每个员工职位的字符个数

3:将所有员工的名字以小写形式，与首字母大写形式查询出来，第一列为小写形式，第二列为首字母大写

4:将字符串’aaaaaabaaaaa’中左右两边的a去除

5:显示每个员工的名字，要求显示10位，第一列左对齐效果，第二列 右对齐效果

6:截取字符串’DOCTOR WHO’中的’WHO’

7:查看’DOCTOR WHO’中’WHO’的位置

8:分别查看55.789四舍五入保留小数点后2位，整数位，十位后的数字，显示成三列。

9:分别查看55.789截取后保留小数点后2位，整数位，十位后的数字，显示成三列。

10：查看每名员工工资百位以下的数字?

11:查看每名员工从入职到今天一共多少天，若有小数则向上取整。

12:查看从2008-08-08号到今天为止一共经历了多少天?

13:将每名员工入职时间以例如:"1981年12月3日"的形式显示

14：查看每个员工入职所在月的月底是哪天?

15:查看每名员工转正日期(入职后3个月)

16:查看每名员工入职至今共多少个月?

17:查看从明天开始一周内的周日是哪天?

18:查看82年以后入职的员工的入职日期，82年以前的按照"1982年01月01号"显示。格式都是DD-MON-RR(默认格式)

19:查看每名员工的入职年份?

20:显示每个员工的总收入(工资加奖金)，奖金为NULL的只看工资

21:使用NVL2实现20题的需求

22:查看到今天为止自己活了多少天?

三、列别名、LIKE关键字、聚合函数、分组、关联查询、内连接、外链接、自连接

列别名

• 当一个SELECT子句中包含函数或者表达式时，查询的结果集对应的该字段，就是使用这个函数或者表达式作为字段名，可读性差。为此可以为这样的字段添加别名。

• 若别名使用双引号，那么别名就可以区分大小写并且包含空格。

  SELECT ename,sal*12 sal FROM emp;
  

AND的优先级高于OR

SELECT ename,job,sal FROM emp WHERE sal>1000 AND (job='SALESMAN' OR job='CLERK');

LIKE关键字

• LIKE用于模糊匹配字符串，它支持两个通配符比较:

  • _:表示单一的一个字符；
  • %:表示任意个字符(0-多个)。

• 案例：查看名字第二个字母是A的第四个字母是T的员工。

  SELECT ename FROM emp WHERE ename LIKE '_A_T%';
  

IN(list)与NOT IN(list)

• 判断在列表中或不在列表中，IN和NOT IN常用在子查询的判断中。

• 案例：查看职位是CLERK或SALESMAN的员工。

  SELECT ename,job,deptno FROM emp WHERE job IN ('CLERK','SALESMAN');
  

BETWEEN…AND…

• 案例：判断在一个范围内，查看工资在1500到3000之间的员工?

  SELECT ename,job,sal FROM emp WHERE sal BETWEEN 1500 AND 3000;
  

ANY(list)和ALL(list)

• ANY与ALL是配合>,>=,<,<=使用的
  • >ANY(list)：大于列表之一；
  • >ALL(list)：大于列表所有；
  • <ANY(list)：小于列表之一；
  • <ALL(list)：小于列表所有。
• 常用在子查询中。

DISTINCT 关键字

• 去除重复行；

• 注意distinct效率不高，尽量使用exists关键字代替或者不使用。

• 案例：查看公司有哪些职位?

  SELECT DISTINCT job FROM emp;
  

• 多字段去重，这几个字段值的组合没有重复行。

  SELECT DISTINCT job,deptno FROM emp;
  

排序结果集

• ORDER BY子句用来对结果集按照指定的字段排序。

• 排序有两种方式:

  • 升序(ASC):不写默认就是升序；
  • 降序(DESC):从大到小，需要单独指定。

• ORDER BY子句必须写在DQL的最后一个子句上。

• 案例：查看公司中工资的排名？

  SELECT ename,sal,deptno
  FROM emp
  ORDER BY sal DESC;
  

• 多字段排序，有优先级，首先按照第一个字段排序，当第一个字段有重复值时才按照第二个字段排序，依次类推。

  SELECT ename,deptno,sal
  FROM emp
  ORDER BY deptno DESC,sal DESC;
  

• 排序时NULL被认作为最大值。

  SELECT ename,comm
  FROM emp
  ORDER BY comm DESC;
  

聚合函数

• 聚合函数又称为：多行函数，分组函数。

• 作用是对结果集的指定字段进行统计然后得出一个结果。

• 案例：查看公司的最高工资与最低工资?

  SELECT MAX(sal),MIN(sal) FROM emp;
  

• 案例：查看公司的平均工资与工资总和?

  SELECT AVG(sal),SUM(sal) FROM emp;
  

COUNT函数

• COUNT函数是对记录数的统计。

• 案例：查看公司共多少人?

  SELECT COUNT(ename) FROM emp;
  

聚合函数忽略NULL值

SELECT SUM(comm),AVG(comm) FROM emp;
SELECT AVG(NVL(comm,0)) FROM emp;

注意：COUNT()在统计某列的时候会忽略NULL值。

分组

GROUP BY 子句

• GROUP BY可以将结果集按照给定字段值一样的记录进行分组；

• 配合聚合函数，可以对不同的分组分别统计结果。

  SELECT AVG(sal),deptno
  FROM emp
  GROUP BY deptno;
  
  SELECT MAX(sal),job
  FROM emp
  GROUP BY job;
  

多字段分组

• 这些字段值都一样的记录看做一组。

• 案例1：同部门，同职位的员工的平均工资?

  SELECT AVG(sal),deptno,job
  FROM emp
  GROUP BY deptno,job;
  

• 案例2：查看每个部门的最低工资是多少?（前提是该部门的最低工资要高于1000）

  -- SQL语句会报错:此处不允许使用分组函数
  SELECT MIN(sal),deptno
  FROM emp
  WHERE MIN(sal)>1000
  GROUP BY deptno;
  

HAVING 子句

• HAVING子句必须跟在GROUP BY 子句之后；

• 作用是添加过滤条件来过滤GROUP BY的分组，它可以将不满足条件的分组去除;

• HAVING子句可以使用聚合函数作为过滤条件。

• 案例1：查看平均工资高于2000的部门的最低工资?

  SELECT MIN(sal),deptno
  FROM emp
  GROUP BY deptno
  HAVING AVG(sal)>2000;
  

• 案例2：查看最低工资高于1000的那些职位的平均工资?

  SELECT AVG(sal),job
  FROM emp
  GROUP BY job
  HAVING MIN(sal)>1000;
  

关联查询

• 查询数据是从多张表中关联查询一个结果集，关联查询的重点是添加连接条件。

• 关联条件的作用是告知数据库表与表之间的数据是怎样对应的。

• 关联查询通常都要添加连接条件，否则会产生笛卡尔积，通常是一个无意义的结果集。

• 案例：查看每个员工的名字以及其所在部门的名字?

  SELECT e.ename,e.deptno,d.dname
  FROM emp e,dept d
  WHERE e.deptno=d.deptno;
  

• 当关联查询的表中有同名字段，需要通过表名或表别名来指定该字段所属表。

• 在关联查询中过滤条件必须与连接条件同时成立。

• 案例：查看RESEARCH部门的员工信息?

  SELECT e.ename,e.deptno,d.dname
  FROM emp e,dept d
  WHERE e.deptno=d.deptno
  AND d.dname='RESEARCH';
  

• 不加关联条件会产生笛卡尔积。

  SELECT e.ename,d.dname FROM emp e,dept d;
  

内连接

• 内连接也是关联查询的一种。

  SELECT e.ename,d.dname
  FROM emp e,dept d
  WHERE e.deptno=d.deptno;
  

• 关联查询忽略不满足连接条件的记录。

  -- 筛选 dept 的同时会筛选掉有对应关系的 emp 数据。
  SELECT e.ename,d.dname
  FROM emp e JOIN dept d ON e.deptno=d.deptno
  WHERE d.dname='RESEARCH';
  

外链接

• 外链接在关联查询时还可以将不满足连接条件的记录也查询出来。

外链接分为:

• 左外连接，右外连接，全外连接。

  SELECT e.ename,d.dname
  FROM emp e LEFT|RIGHT|FULL OUTER JOIN dept d
  ON e.deptno=d.deptno;
  

左外连接

• 以JOIN左侧表作为驱动表。

• 驱动表中所有数据都要列出来，那么当该表某条记录不满足连接条件时，那么来自右侧表的字段值全部为NULL。

自连接

• 当表中的一条记录可以对应当前表的其他记录时，这种设计称为自连接。

• 案例：查看每个员工以及其上司的名字?

  -- 写法一：
  SELECT e.ename,m.ename
  FROM emp e,emp m
  WHERE e.mgr=m.empno;
  
  -- 写法二：
  SELECT e.ename,m.ename
  FROM emp e JOIN emp m
  ON e.mgr=m.empno;
  

作业：

1:查看工资高于2000的员工

2:查看不是"CLERK"职位的员工

3:查看工资在1000-2500之间的员工

4:查看名字是以K结尾的员工

5:查看20,30号部门的员工

6:查看奖金为NULL的员工

7:查看年薪高于20000的员工

8:查看公司共有多少种职位

9:按部门号从小到大排列查看员工

10:查看每个部门的最高，最低，平均工资，和工资总和

11:查看平均工资高于2000的部门的最低薪水

12:查看在NEWYORK工作的员工

13:查看所有员工及所在部门信息，若该员工没有部门，则

部门信息以NULL显示

14:查看ALLEN的上司是谁

15:查看SMITH上司在哪个城市工作?

16:查看平均工资高于2000的那些部门名字以及所在城市?

17:在NEW YORK工作的员工有多少人?

18:在DALLAS工作的员工的平均工资是多少?

19:查看SMITH的上司是谁?他在哪个城市工作?

四、子查询、EXISTS关键字、分页查询、DECODE函数、排序函数、高级分组函数

子查询

• 子查询是一条查询语句，它是嵌套在其他SQL语句当中的，目的是为了外层查询提供数据的。

• 案例1：查看谁的工资高于CLARK?

  SELECT ename,sal
  FROM emp
  WHERE sal>(SELECT sal FROM emp WHERE ename='CLARK');
  

• 案例2：查看谁的工资高于公司平均工资?

  SELECT ename,sal
  FROM emp
  WHERE sal>(SELECT AVG(sal) FROM emp);
  

在DDL中使用子查询

• 根据一个查询结果集快速构建一张表：

  CREATE TABLE employees
  AS
  SELECT e.empno,e.ename,e.sal,e.job,d.deptno,d.dname,d.loc
  FROM emp e,dept d
  WHERE e.deptno=d.deptno(+); -- 左连接
  

DML中使用子查询

• 将SMITH所在部门的员工工资上浮10%

  UPDATE emp
  SET sal=sal*1.1
  WHERE deptno=(SELECT deptno FROM emp WHERE ename='SMITH');
  

• 当子查询为多行单列时，那么在用作判断条件中时要搭配IN、ANY、ALL使用。

• 案例1：查看与职位是SALESMAN同部门的其他职位员工?

  SELECT ename,sal,deptno,job
  FROM emp
  WHERE deptno IN(SELECT deptno FROM emp WHERE job='SALESMAN') AND job <> 'SALESMAN';
  

• 案例2：查看比职位是SALESMAN和CLERK工资都高的员工信息?

  SELECT ename,sal
  FROM emp
  WHERE sal>ALL(SELECT sal FROM emp WHERE job IN('SALESMAN','CLERK'));
  

EXISTS关键字

• EXISTS关键字后面跟一个子查询，当该子查询可以查询出至少一条记录时，EXISTS条件成立。

• 案例1：查看有员工的部门有哪些?

  SELECT d.deptno,d.dname,d.loc
  FROM dept d
  WHERE EXISTS(SELECT * FROM emp e WHERE e.deptno=d.deptno);
  

• 案例2：查看哪些人是别人的领导?

  SELECT empno,ename,job,deptno
  FROM emp m
  WHERE EXISTS(SELECT * FROM emp e WHERE e.mgr=m.empno);
  

• 案例3：查看部门的最低薪水?（前提是该部门的最低薪水要高于30号部门的最低薪水）

  SELECT MIN(sal),deptno
  FROM emp
  GROUP BY deptno
  HAVING MIN(sal)>(SELECT MIN(sal) FROM emp WHERE deptno=30);
  

补充：

• 在FROM子句中使用子查询，通常是将子查询的结果当做一张表看待，基于该查询结果进行二次查询使用。

• 案例1：查看谁的工资高于其所在部门的平均工资?

  SELECT e.ename,e.sal,e.deptno
  FROM emp e,
    (SELECT AVG(sal) avg_sal, deptno
     FROM emp
     GROUP BY deptno) t
  WHERE e.deptno=t.deptno AND e.sal>t.avg_sal
  

分页查询

• 分页查询需要两个参数：

  • pageSize：每页显示的条目数；
  • page：页数。

• 开始行号 和 结束行号：

  • start：(page - 1) * pageSize + 1；
  • end：pageSize * page。

• 分页查询就是将数据分段查询出来，一次只查询数据的一部分。

• 这样做可以减少系统资源开销，减少数据量可以提高网络传输速度。

• 分页在不同的数据库中的SQL语句是不同的。

ORACLE中提供了一个伪列：ROWNUM

• ROWNUM字段不存在于任何一张表中，但是每张表都可以查询该字段；

• 该字段的值是结果集中每条记录的行号；

• ROWNUM字段的值是动态生成的，伴随查询过程；

• 只要可以查询出一条记录，ROWNUM就会为该条记录生成行号，从1开始每次递增1。

• 由于ROWNUM是在查询表的过程中进行编号的，所以在使用ROWNUM对结果集编行号的查询过程中不要使用ROWNUM做大于1以上数字的判断，否则结果集没有任何数据。

  SELECT *
  FROM (SELECT ROWNUM rn,empno,ename,sal,deptno FROM emp)
  WHERE rn BETWEEN 6 AND 10;
  -- 注意：oracle中的子查询结果集可以没有别名，Mysql中必须有别名。
  

  输出结果

  

• 案例：查看公司工资排名的第6-10名

  -- 写法一：
  SELECT *
  FROM(SELECT ROWNUM rn,t.*
     FROM(SELECT empno,ename,sal,deptno FROM emp ORDER BY sal DESC) t)
  WHERE rn BETWEEN 6 AND 10;
  
  -- 写法二：
  SELECT *
  FROM(SELECT ROWNUM rn,t.*
     FROM(SELECT empno,ename,sal,deptno FROM emp ORDER BY sal DESC) t
     WHERE ROWNUM <=10)
  WHERE rn >=6
  

  输出结果

  

DECODE函数

• 可以实现分支效果。

  SELECT ename, job, sal,
   DECODE(job,  
  	'MANAGER', sal * 1.2,
  	'ANALYST', sal * 1.1,
  	'SALESMAN', sal * 1.05,
  	sal) bonus
  FROM emp;
  

  输出结果

  

  补充：相当于MySql中的 CASE-WHEN 语句。

• 案例：将MANAGER与ANALYST看做一组，其他职位看做另一组，分别统计两组人数?

  SELECT COUNT(*),
   DECODE(job,
  	'MANAGER','VIP',
  	'ANALYST','VIP',
  	'OTHER') TYPE
  FROM emp
  GROUP BY DECODE(job,
  	'MANAGER','VIP',
  	'ANALYST','VIP',
  	'OTHER');
  

  输出结果

排序函数

• 排序函数允许将结果集，按照指定字段分组，在组内按照指定字段排序，然后该函数为每组生成一个行号。

• ROW_NUMBER():生成组内连续且唯一的数字。

• 案例：查看每个部门的工资排名?

  SELECT ename,deptno,sal,
   ROW_NUMBER() OVER(
    PARTITION BY deptno
    ORDER BY sal DESC
   ) rank
  FROM emp;
  

  输出结果

  

RANK函数

• 生成组内不连续也不唯一的数字。

  SELECT ename,deptno,sal,
   RANK() OVER(
    PARTITION BY deptno
    ORDER BY sal DESC
   ) rank
  FROM emp;
  

  输出结果

  

DENSE_RANK函数

• 生成组内连续但不唯一的数字

  SELECT 
   ename,deptno,sal,
   DENSE_RANK() OVER(
    PARTITION BY deptno
    ORDER BY sal DESC
  ) rank
  FROM emp;
  

  输出结果

  

高级分组函数

1. ROLLUP(a[,b,c…])

• GROUP BY ROLLUP(a,b,c)

  等同于

  GROUP BY a,b,c UNION ALL
  GROUP BY a,b UNION ALL
  GROUP BY a
  

2. CUBE()

• CUBU的分组策略为每个参数的组合进行一次分组；

• GROUP BY CUBE(a,b,c)

  等同于

  GROUP BY a,b,c	UNION ALL
  GROUP BY a,b 	UNION ALL
  GROUP BY b,c 	UNION ALL
  GROUP BY a,c 	UNION ALL
  GROUP BY a 		UNION ALL
  GROUP BY b 		UNION ALL
  GROUP BY c
  

3. GROUPING SETS()

• 该函数允许自行指定分组策略，然后将这些分组统计的结果并在一起。函数的每个参数为一种分组方式。

• GROUP BY GROUPING SETS (A, B, C)

  等同于

  GROUP BY A	UNION ALL
  GROUP BY B	UNION ALL
  GROUP BY C
  

• 案例1：查看每天与每月的营业额?

  SELECT year_id,month_id,day_id,SUM(sales_value)
  FROM sales_tab
  GROUP BY
   GROUPING SETS(
  	(year_id,month_id,day_id),
  	(year_id,month_id))
  ORDER BY year_id,month_id,day_id
  

• 案例2：统计一下每个国家的平均工资和每个部门的平均工资

  CREATE TABLE employee
  (
    name          NVARCHAR2(10),
    gender        NCHAR(1),
    country       NVARCHAR2(10),
    department    NVARCHAR2(10),
    salary        NUMBER(10)
  );
  INSERT INTO employee VALUES ('张三','男','中国','市场部',4000);
  INSERT INTO employee VALUES ('李四','男','中国','市场部',5000);
  INSERT INTO employee VALUES ('王五','女','美国','市场部',3000);  
  INSERT INTO employee VALUES ('赵红','男','中国','技术部',2000);
  INSERT INTO employee VALUES ('李白','女','中国','技术部',5000);  
  INSERT INTO employee VALUES ('王蓝','男','美国','技术部',4000);
  
  SELECT NVL(country, department) TYPE, round(avg(salary), 2)
  FROM employee
  GROUP BY GROUPING SETS (country, department);
  

  输出结果

  

作业：

1:查看与CLARK相同职位的员工

2:查看低于公司平均工资的员工

3:查看与ALLEN同部门的员工

4:查看平均工资低于20号部门平均工资的部门平均工资

5:查看低于自己所在部门平均工资的员工

6:查看公司工资排名的第1-5名

7:查看CLERK职位的人数和其他职位的总人数各多少?

8:查看每个职位的工资排名

9:查看每个职位的工资排名，若工资一致，排名一致

10:查看每个职位的工资排名，若工资一致，排名一致，不跳名次。

11:分别查看:同部门同职位，同职位，以及所有员工的工资总和

12:分别查看:同部门同职位，同职位，同部门以及所有员工的工资总和

13:分别查看同部门同职位和同职位的员工的工资总和

14:查看公司最高工资的员工的名字以及所在部门名称

15:查看每个部门的最高工资的员工名字

16:查看有下属的员工信息

五、视图、序列、约束

视图

• 视图也是数据库对象之一；
• 在SQL语句中体现的角色与表一致，但视图只是对应一个查询语句的结果集。

创建视图:

CREATE VIEW v_emp_10
AS
SELECT empno, ename, sal, deptno 
FROM emp 
WHERE deptno = 10;

视图也可以查看结构

DESC v_emp_10;

视图分类：

• 视图根据对应的SQL语句不通，分为：简单视图，复杂视图，连接视图；
• 简单视图：当对应的子查询不含有函数，表达式，分组，去重，关联查询的视图称为简单视图；
• 复杂视图：简单视图相反就是复杂视图；
• 连接视图：连接视图算作复杂视图的一种，连接视图指子查询使用了关联查询。

视图别名

• 视图对应的子查询的查询字段可以使用别名，那么该字段的名字就是这个别名。

• 若字段含有函数或表达式，那么该字段必须指定别名。

• 举个栗子：

  CREATE OR REPLACE VIEW v_emp_10
  AS
  SELECT empno id,ename name,sal*12 sal,deptno
  FROM emp
  WHERE deptno=10
  

对视图进行DML操作

• 对视图进行DML就是对视图数据来源的基础表进行的操作；
• 只能对简单视图进行DML操作，复杂视图不可以；
• 对视图中不包含的数据进行DML操作会污染基础表数据。

视图检查

• 可以为视图添加检查选项，来保证对视图进行DML操作时不会对基表数据污染。

• WITH CHECK OPTION

• 当视图添加了检查选项后，视图要求对视图中数据进行DML操作后，视图必须对该记录可见，否则不允许操作。

• 举个栗子：

  CREATE OR REPLACE VIEW v_emp_10
  AS
  SELECT empno,ename,sal,deptno
  FROM emp
  WHERE deptno=10
  WITH CHECK OPTION;
  

视图只读

• 为视图添加只读选项，当一个视图添加了只读选项后，该视图不能进行DML操作。

• WITH READ ONLY

• 举个栗子：

  CREATE OR REPLACE VIEW v_emp_10
  AS
  SELECT empno,ename,sal,deptno
  FROM emp
  WHERE deptno=10
  WITH READ ONLY;
  

补充：查看数据字典，有助于了解曾经创建过的数据库对象。

SELECT * FROM user_objects WHERE object_name LIKE '%_FANCQ';
SELECT * FROM user_views;
SELECT * FROM user_tables;

复杂视图

• 复杂视图不能进行DML操作。

• 案例1：创建一个部门工资信息的视图：

  CREATE VIEW v_dept_sal
  AS
  SELECT MIN(e.sal) min_sal,
  	MAX(e.sal) max_sal,
  	AVG(e.sal) avg_sal,
  	SUM(e.sal) sum_Sal,
  	d.deptno,d.dname
  FROM emp e,dept d
  WHERE e.deptno=d.deptno
  GROUP BY d.deptno,d.dname;
  

• 案例2：查看谁的工资高于自己所在部门平均工资?

  SELECT e.ename,e.sal,e.deptno
  FROM emp e,v_dept_sal v
  WHERE e.deptno=v.deptno
  AND e.sal>v.avg_sal;
  

删除视图

• 删除视图不会影响基表数据。

DROP VIEW v_emp_10;

序列

• 序列也是数据库对象之一；
• 作用是根据指定的规则生成一系列数字；
• 一般用于为表的每一条记录的主键字段提供值。

创建序列

CREATE SEQUENCE seq_emp_id
START WITH 1
INCREMENT BY 1;

序列支持两个伪列：

• NEXTVAL：获取序列下一个数字，序列会根据序列最后生成的数字加上步进来得到。

  NEXTVAL会导致序列发生步进，序列是不能回退的。

• CURRVAL：获取序列最后一次生成的数字。需要注意的是，新创建的序列必须在使用NEXTVAL生成一个数字后才可以使用CURRVAL。

  SELECT seq_emp_id.NEXTVAL FROM DUAL;
  SELECT seq_emp_id.CURRVAL FROM DUAL;
  

删除序列

DROP SEQUENCE seq_emp_id;

索引

• 索引是数据库对象之一；
• 索引是加快查询效率的机制；
• 索引的建立以及应用是数据库自行完成的。

约束

1. 唯一性约束

• 唯一性约束要求该字段每条记录的值不能重复，NULL除外。

• 举个栗子：

  CREATE TABLE employees1 (
   eid NUMBER(6) UNIQUE,
   name VARCHAR2(30),
   email VARCHAR2(50),
   salary NUMBER(7, 2),
   hiredate DATE,
   CONSTRAINT employees1_email_uk UNIQUE(email)
  );
  

2. 主键约束

• 主键约束要求该字段的值为空且唯一；

• 主键约束只能在一张表的一个字段上建立。

• 主键：使用该字段的值可以唯一定位表中的一条记录。

  CREATE TABLE employees2 (
   eid NUMBER(6) PRIMARY KEY,
   name VARCHAR2(30),
   email VARCHAR2(50),
   salary NUMBER(7, 2),
   hiredate DATE
  );
  

作业：

1:创建一个视图，包含20号部门的员工信息，

字段:empno,ename,sal,job,deptno

2:创建一个序列seq_emp_no,从10开始，步进为10

3:编写SQL语句查看seq_emp_no序列的下一个数字

4:编写SQL语句查看seq_emp_no序列的当前数字

5:为emp表的ename字段添加索引:idx_emp_ename

6:为emp表的LOWER(ename)字段添加索引:

idx_emp_lower_ename

7:为emp表的sal,comm添加多列索引

8:创建myemployee表，字段:

id NUMBER(4) , id作为主键

nameVARCHAR2(20), name要求不能为空

birthday DATE,

telephone VARCHAR2(11) telephone需要唯一

scoreNUMBER(9,2) score值必须>=0

六、附录：

本文中的sql基础库如下：

create table EMP(
EMPNO NUMBER(4) primary key,
ENAME varchar(10) not null,
JOB varchar(9),
MGR NUMBER(4),
HIREdate date,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(4)
);

create table Dept(
DEPTNO NUMBER(4) primary key,
DNAME varchar(14) not null unique,
LOC varchar(13)
);

create table SALGRADE( 
 GRADE NUMBER,
 LOSAL NUMBER,
 HISAL NUMBER
);

insert into SALGRADE VALUES (1,700,1200);
insert into SALGRADE VALUES (2,1201,1400);
insert into SALGRADE VALUES (3,1401,2000);
insert into SALGRADE VALUES (4,2001,3000);
insert into SALGRADE VALUES (5,3001,9999);

insert into Dept VALUES (10,'ACCOUNTING','NEW YORK');
insert into Dept VALUES (20,'RESEARCH','DALLAS');
insert into Dept VALUES (30,'SALES','CHICAGO');
insert into Dept VALUES (40,'OPERATIONS','BOSTON');

SELECT * FROM DEPT;


insert into EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-YYYY'),800,null,20);
insert into EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-YYYY'),1600,300,30);
insert into EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-YYYY'),1250,500,30);
insert into EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-YYYY'),2975,NULL,20);
insert into EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-YYYY'),1250,1400,30);
insert into EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-YYYY'),2850,NULL,30);
insert into EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-YYYY'),2450,NULL,10);
insert into EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-YYYY'),5000,NULL,10);
insert into EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-YYYY'),1500,0,30);
insert into EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-YYYY'),950,NULL,30);
insert into EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-YYYY'),3000,NULL,20);
insert into EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-YYYY'),1300,NULL,10);

commit;