数据库

Oracle

数据库

  • 存储在一起的数据集合
  • 能为多个用户共享
  • 尽可能小的数据冗余
  • 与应用程序彼此独立

数据库管理系统(DBMS)

  • 是一种管理数据库的软件
  • 包括数据库的创建、使用、维护、配置
  • 数据库命令都要通过数据库管理系统处理

关系型数据库

  • 实体以及实体间的各种关系均用关系来表示
  • 用户的角度来看关系就是一张由行和列组成的二维数据表

常见关系型数据库

  • Oracle 甲骨文
  • MySQL 甲骨文
  • SQL Server 微软
  • DB2 IBM
  • Sybase sybase公司

Oracle数据库简介:

  • 目前是世界上最流行的关系型数据库之一
  • 支持各种操作系统(Windows、Linux、IOS)
  • 数据库领域一直处于领先地位
  • 特点:
    • 可移植性好
    • 使用方便，功能强
    • 适合各种大中小型服务器和微机环境

样例表

Dept(部门)

  • deptno:部门编号
  • dname:部门名称
  • loc:部门地址

Emp(员工表)

  • empno:员工编号
  • ename:员工姓名
  • job:工作岗位
  • mgr:经历编号
  • hiredate:入职日期
  • sal:薪水
  • comm:奖金
  • deptno:部门编号

SalGrade(薪水等级表)

  • GRADE:等级编号
  • LOSAL:最低工资
  • HISAL:最高工资

结构化的查询语言(SQL)

  • 是操作和检索关系型数据库的标准语言
  • 二十世纪七十年代由IBM公司开发，目前应用于各种关系型数据库
  • 分类:
    • 数据查询语言(DQL):SELECT，从表中查询数据
    • 数据操作语句(DML):INSERT,UPDATE,DELETE,用于添加修改删除数据
    • 事务处理语言(TPL):COMMIT和ROLLBACK，用于提交和回滚事务
    • 数据控制语言(DCL):GRANT和REVOKE,用于授权和收回授权
    • 数据定义语言(DDL):CREATE,DROP,ALTER,用于定义销毁和修改数据库对象

基本SELECT语句

  • SELECT语句

    • SELECT子句(SELECT *:查询所有列)
    • FROM子句(FROM DEPT:表示从哪张表查询数据)

  • SQL的相关概念

    • 关键字:SQL语言中的保留字符串
    • 语句:一条完整的SQL语句(独立执行)
    • 子句:部分SQL语句(不能独立执行)

  • 书写规则:

    • 不区分大小写
    • 可以在一行书写也可以多行书写，建议多行书写
    • 关键字不可以分开，缩写或跨行写

  • 选择列

    • 所有列:*
    SELECT *
FROM EMP;
    • 选择指定列:列名之间使用逗号分隔
    SELECT ENAME,HIREDATE 
FROM EMP;

  • 算术运算符

    • +,-,*,/,()

    • 例题:

      1.员工转正后，月薪上调20%，请查询出所有员工转正后的月薪。
      2.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的年薪所得（不考虑奖金部分.年薪的试用期6个月的月薪+转正后6个月的月薪)

    SELECT Ename,SAL,SAL*6+SAL*1.2*6 AS 试用转正年薪,SAL*1.2 AS 转正月薪
FROM EMP;

  • 空值

    • 空值是一种无效的,未赋值的,未知的,不确定的值
    • 空值不同于0或空格或空字符串
    • 包含空值的算术运算后的结果也为空值

  • 列别名

    • 用来在当前查询中重命名列名

    • 书写方式

      列名 列别名
列名 AS 列别名
      • 有三种情况需要在列名两侧加上引号
        • 列别名中包含空格
        • 列别名中区分大小写
        • 列别名中包含特殊字符或关键字

  • NVL(列名,值):空值处理函数,当列名对应的值是空的时候，返回第二个参数，不为空，返回实际值

  • 连接操作符

    • 用于连接列与列、列与字符
    • 形式上为||
    SELECT ENAME,JOB,ENAME||'的岗位是'||JOB
FROM EMP;

  • 原义字符串

    • 包含在select列表中的一个字符串，一个数字或一个日期
    • 日期和字符串字面量必须使用单引号括起来''(字面量:直接写到原程序中的固定值)
    • 每个原义字符串会在每一行都出现
    SELECT ENAME,JOB,ENAME||'的岗位是'||JOB,'浑南' 公司地址,2000 年终奖金，'20-5月-2023' 日期
FROM EMP;

  • 消除重复行:DISTINCT

    SELECT DISTINCT JOB
FROM EMP;

  • 练习四

    1.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的所有收入（需考虑奖金部分)，
要求显示格式为:XXX的第一年总收入为XXX。
SELECT ENAME||'第一年总收入为'||((SAL+NVL(COMM,0))*6+(SAL*1.2+NVL(COMM,0))*6)
FROM EMP;

2.查询员工表中一共有哪几种岗位类型。
SELECT DISTINCT JOB 岗位类型
FROM EMP;

WHERE子句的使用

作用
  • 使用WHERE子句限制返回的记录
  • 写在FROM子句后面
语法
SELECT 列名列表
FROM 表名
WHERE 条件
比较运算符

  • =、>、>=、<、<=、<>（不等于）

  • 比较规则

    • 字符串或日期类型要加上单引号

      SELECT * 
FROM EMP
WHERE ENAME = 'SMITH';

SELECT * 
FROM EMP
WHERE HIREDATE > '20-2月-1981';

    • 字符串内容区分大小写

    • 练习一:

    1.查询职位为SALESMAN的员工编号、职位、入职日期。
SELECT EMPNO,JOB,HIREDATE
FROM EMP
WHERE JOB = 'SALESMAN';
2.查询1985年12月31日之前入职的员工姓名及入职日期。
SELECT ENAME,HIREDATE
FROM EMP
WHERE HIREDATE < '31-12月-1985';
3.查询部门编号不在10部门的员工姓名、部门编号。
SELECT ENAME,DEPTNO
FROM EMP
WHERE DEPTNO <> 10;

  • 特殊比较运算符

    • between … and … 在XXX到XXX之间

      SELECT *
FROM EMP
WHERE EMPNO BETWEEN 7800 AND 9999;

    • IN(列表,列表) 或者

      SELECT *
FROM EMP
WHERE DEPTNO IN(20,30);

    • LIKE 模糊查询

      /*
	使用通配符代替未知的信息
	通配符:%表示任意多个字符
		  _表示任意一个字符
	使用ESCAPE进行转义
	SELECT *
	FROM EMP
	WHERE ENAME LIKE '%@_%' ESCAPE '@';
*/
SELECT *
FROM EMP
WHERE ENAME LIKE '__O%';
      
练习2:
	1.查询入职日期在82年至85年的员工姓名，入职日期。
	SELECT ENAME,HIREDATE
	FROM EMP
	WHERE HIREDATE BETWEEN '1-1月-1982' AND '31-12月-1985';
	2.查询月薪在3000到5000的员工姓名，月薪。
	SELECT ENAME,SAL
	FROM EMP
	WHERE SAL BETWEEN 3000 AND 5000;
	3.查询部门编号为10或者20的员工姓名，部门编号。
	SELECT ENAME,DEPTNO
	FROM EMP
	WHERE DEPTNO IN(10,20);
	4.查询经理编号为7902，7566，7788的员工姓名，经理编号。
	SELECT ENAME,MGR
	FROM EMP
	WHERE MGR IN(7902,7566,7788);

    • IS NULL 判断空值

    练习三:
1.查询员工姓名以W开头的员工姓名。
SELECT ENAME
FROM EMP
WHERE ENAME LIKE 'W%';
2.查询员工姓名倒数第2个字符为T的员工姓名。
SELECT ENAME
FROM EMP
WHERE ENAME LIKE '%T_';
3.查询奖金为空的员工姓名，奖金。
SELECT ENAME,COMM
FROM EMP
WHERE COMM IS NULL;

  • 逻辑运算符

    • AND(与):两个条件同时满足就为TRUE;
    • OR(或):两个条件有一个满足就为TRUE;
    • NOT(非):对指定条件取相反
    练习四:
1.查询工资超过2000并且职位是MANAGER,或者职位是SALESMAN的员工姓名、职位、工资
SELECT ENAME,JOB,SAL
FROM EMP
WHERE  SAL > 2000 
AND JOB = 'MANAGER' 
OR JOB = 'SALESMAN'  ;
2.查询工资超过2000并且职位是 MANAGER或SALESMAN的员工姓名、职位、工资。
SELECT ENAME,JOB,SAL
FROM EMP
WHERE (JOB = 'MANAGER' OR JOB = 'SALESMAN') 
AND SAL > 2000 ;
3.查询部门在10或者20，并且工资在3000到5000之间的员工姓名、部门、工资。
SELECT ENAME,DEPTNO,SAL
FROM EMP
WHERE DEPTNO IN(10,20) 
AND SAL BETWEEN 3000 AND 5000;
4.查询入职日期在81年，并且职位不是SALES开头的员工姓名、入职日期、职位。
SELECT ENAME,HIREDATE,JOB
FROM EMP
WHERE HIREDATE BETWEEN '1-1月-1981' AND '31-12月-1981' 
AND JOB NOT LIKE 'SALES%';
5.查询职位为SALESMAN或MANAGER，部门编号为10或者20，姓名包含A的员工姓名、职位、部门编号。
SELECT ENAME,JOB,DEPTNO
FROM EMP
WHERE (JOB = 'MANAGER' OR JOB = 'SALESMAN') 
AND DEPTNO IN(10,20) 
AND ENAME LIKE '%A%';

排序

  • 作用:用来排序
  • 语法:
    • ORDER BY{列名|表达式|别名|列号}
    • ASC:默认值，升序
    • DESC:降序
    • 注意:空值最大(MySQL则相反)
    • 可多列排序，先按照前面的列排序，如果前面的列值相同，使用后面的列进行排序
    • 特殊情况: ORDER BY子句可以出现SELECT子句中没有列出的列
    • 子句的执行顺序:FROM WHERE SELECT ORDER

表连接

  • 为什么需要表连接
    • 需要查询的数据分布在多张表中
  • 什么是表连接
    • 连接是在多张表之间通过一定的条件，使得表之间发生关联，进而能从多张表中获取数据
  • 连接的类型
    • 按连接条件分:等值连接，不等值连接
    • 按其他的连接方法分:外连接和内连接
  • 多表连接的写法：
    • Oracle风格或旧式连接
    • ANSI 99(标准语法)
  • 笛卡尔积:第一张表的所有行和第二张表的所有行都发生连接
  • image-20230527134955510
SELECT *
FROM EMP,DEPT;

  • 等值连接

    • 用等号连接关联的列
    SELECT * 
FROM EMP,DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

SELECT EMP.*,DEPT.DNAME,DEPT.LOC 
FROM EMP,DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

  • 表连接注意

    • 未明确定义的列:两张表中都存在的名称相同的列，解决方法:在列名前加上表名作为前缀
    • 通常在表连接的时候，会给表起别名，目的是书写方便，一旦起了别名，在当前查询中，不能再使用原来的表名，一律使用表别名替代

  • 非等值连接:不使用等号对表进行连接

SELECT *
FROM SALGRADE S,EMP E
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
  • 内连接:只有匹配的记录才会显示到最终的结果集中
SELECT * 
FROM EMP,DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

SELECT 列名
FROM 表1
INNER JOIN 表2
ON 表1.列名 = 表2.列名

-- 注意:INNER可以省略
  • 外连接:两个表连接没有匹配的记录也会显示出来，+号写在where条件中需要添加空白行的一段
    • 主表:该表中的所有数据至少出现一次
    • 从表:只有匹配上的记录才显示(+号写在从表一端)
SELECT ENAME,EMPNO,D.DEPTNO,D.DNAME,D.LOC 
FROM EMP E,DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO;

-- 左外连接
SELECT 列名
FROM 表1
LEFT OUTER JOIN 表2
ON 表1.列名 = 表2.列名;
-- OUTER可以省略
-- 或者
SELECT 列名
FROM 表1,表2
WHERE 表1.列名 = 表2.列名(+);
  • Oracle风格，表连接特点
    • 表连接的条件和行筛选条件都写到where子句中
  • 多于两张表的连接
SELECT ENAME,LOC,GRADE
FROM EMP E,DEPT D,SALGRADE S
WHERE E.DEPTNO = D.DEPTNO AND E.SAL BETWEEN S.LOSAL AND S.HISAL;

先将两张表连接起来，再基于已经生成的连接结果(临时表)，再与第三张表连接

  • 自连接

    • 是一个表通过某种条件和自身进行连接的一种方式，就如同两个表连接一样
    SELECT ENAME,LOC,GRADE
FROM EMP E,DEPT D,SALGRADE S
WHERE E.DEPTNO = D.DEPTNO AND E.SAL BETWEEN S.LOSAL AND S.HISAL;

分组函数

  • MAX函数:取最大值

  • MIN函数:取最小值

  • SUM函数:求和

  • AVG函数:求平均数

  • COUNT函数:查询总的记录数(空值不算):COUNT(*)指行数

  • DISTINCT函数:去重

  • GROUP BY子句

    SELECT 列名
FROM 表名
WHERE 条件
GROUP BY 分组列
ORDER BY 列名;
-- 多值的列和分组函数不能混用
-- GROUP BY A,B 没有先后之分，按AB相同分组
-- 分组的条件不能写在WHERE后面
-- 执行顺序:FROM WHERE GROUP SELECT ORDER
-- 分组函数AVG是在GROUP期间完成的一个语句，WHERE执行分组函数时还未计算出结果
-- 解决方法如下的HAVING语句
-- 一旦分组，SELECT后只能写分组条件和分组函数(但MySQL与之不同)

  • HAVING子句:负责分组条件的筛选

    -- 标准书写顺序
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
    -- 标准执行顺序
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
SELECT ... 
ORDER BY ...
-- 注意:MYSQL中的执行顺序
FROM ...
WHERE ...
GROUP BY ...
SELECT ... 
HAVING ...
ORDER BY ...

子查询

  • 括号中的查询称为子查询，外面的查询为父查询，子查询可以独立执行，先执行子查询得到确定的结果，父查询基于这些结果再执行

  • 使用注意:

    • 子查询用括号括起来
    • 放在比较运算符的右边
    • 大多数情况下，子查询不需要排序

  • 子查询分类:

    • 单行子查询:子查询中返回单行单列，使用的比较运算符:>=、<=、>、<、<>、=
    -- 查询比该部门平均薪水低的员工信息
SELECT *
FROM EMP E JOIN
(SELECT DEPTNO,AVG(SAL) AVGSAL
    FROM EMP
    GROUP BY DEPTNO
) A ON E.DEPTNO = A.DEPTNO
  WHERE E.SAL < A.AVGSAL;
    • 多行子查询:子查询中返回多行单列，使用的比较运算符:IN、ANY、ALL
    • IN:和以前介绍的功能一致，判断是否与子查询的任意一个返回值相同。
    -- 查询是经理的员工姓名，工资。
SELECT ENAME,SAL
FROM EMP
WHERE EMPNO IN(
SELECT MGR
FROM EMP
);
    • ANY:表示和子查询的任意一行结果进行比较，有一个满足条件即可。
    -- 查询是经理的员工姓名，工资。(ANY)
SELECT ENAME,SAL
FROM EMP
WHERE EMPNO = ANY(
SELECT MGR
FROM EMP
);
-- 查询部门编号不为10，且工资比10部门任意一名工资高的员工编号，姓名，职位，工资。
SELECT EMPNO,ENAME,JOB,SAL
FROM EMP
WHERE DEPTNO <> 10 
AND SAL > ANY(
    SELECT SAL
    FROM EMP
    WHERE DEPTNO = 10
);
    • ALL:表示和子查询的所有行结果进行比较，每一行必须都满足条件。
    -- 查询部门编号不为10，且工资比10部门所有员工工资高的员工编号，姓名，职位，工资。
SELECT EMPNO,ENAME,JOB,SAL
FROM EMP
WHERE DEPTNO <> 10 
AND SAL > ALL(
    SELECT SAL
    FROM EMP
    WHERE DEPTNO = 10
);
    • 多列子查询:子查询中返回多行多列
    -- 查询各个部门收入最低的人的姓名
SELECT ENAME
FROM EMP
WHERE (SAL,DEPTNO) IN (SELECT MIN(SAL),DEPTNO FROM EMP GROUP BY DEPTNO);
-- 1.查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名，职位，不包括10部门员工
SELECT ENAME,JOB
FROM EMP
WHERE(JOB,MGR) IN (SELECT JOB,MGR FROM EMP WHERE DEPTNO = 10)
AND DEPTNO <> 10;
-- 2.查询职位及经理和10部门任意一个员工职位或经理相同的员工姓名，职位，不包括10部门员工
SELECT ENAME,JOB
FROM EMP
WHERE (JOB IN (SELECT JOB FROM EMP WHERE DEPTNO = 10)
OR MGR IN (SELECT MGR FROM EMP WHERE DEPTNO = 10))
AND DEPTNO <> 10;

  • 注意:如果子查询中有空值，就不能使用NOT IN运算符

ROWNUM

  • ROWNUM是一个伪列，伪列是使用上类似于表中的列而实际没有存储在表中的特殊对象
  • ROWNUM的功能是在每次查询时返回结果集的顺序数，这个顺序数是在记录输出时才一步一步产生的，第一行显示1，第二行显示2，以此类推
  • ROWNUM只能进行小于和小于等于的运算，不能执行大于或大于等于运算

TOP-N

  • 是实现表中按照某个列排序输出最大或最小的N条记录
SELECT ROWNUM,A.ENAME
FROM
(SELECT ENAME
FROM EMP ORDER BY SAL DESC) A
WHERE ROWNUM < 3;

分页

  • pagesize 每页的记录数(每页最多有多少行)
  • pagenum 目标页数(第几页)
-- 1.按照每页显示5条记录，分别查询工资最高的第1页，第2页，第3页信息，要求显示员工姓名、入职日期、部门名称、工资。
 SELECT B.ENAME 姓名, B.HIREDATE 入职日期, DNAME 部门名称, B.SAL 工资
  FROM (SELECT A.*, ROWNUM NM
          FROM (SELECT * FROM EMP ORDER BY SAL DESC) A
         WHERE ROWNUM <= 5) B
  JOIN DEPT D
    ON B.DEPTNO = D.DEPTNO
 WHERE NM >= 1;
 SELECT B.ENAME 姓名, B.HIREDATE 入职日期, DNAME 部门名称, B.SAL 工资
  FROM (SELECT A.*, ROWNUM NM
          FROM (SELECT * FROM EMP ORDER BY SAL DESC) A
         WHERE ROWNUM <= 10) B
  JOIN DEPT D
    ON B.DEPTNO = D.DEPTNO
 WHERE NM >= 6;
SELECT B.ENAME 姓名, B.HIREDATE 入职日期, DNAME 部门名称, B.SAL 工资
  FROM (SELECT A.*, ROWNUM NM
          FROM (SELECT * FROM EMP ORDER BY SAL DESC) A
         WHERE ROWNUM <= 15) B
  JOIN DEPT D
    ON B.DEPTNO = D.DEPTNO
 WHERE NM >= 11;

相关子查询

  • 当子查询中引用的父查询表中的一个列时,Oracle服务器执行相关子查询
  • 执行过程:
    • 取得父查询的候选行
    • 用候选行被子查询引用列的值执行子查询
    • 用来自子查询的值确认或取消候选行
    • 重复上面三步直到父查询中没有剩余的候选行为止
  • 相关子查询不可以独立执行
-- 查询工资高于部门平均薪水的员工信息
SELECT *
FROM EMP E
WHERE SAL > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = E.DEPTNO);

  • EXISTS:判断是否存在,操作过程:

    • 子查询如果有记录找到，子查询语句不会继续执行，返回值为true
    • 子查询中如果到表的末尾也没找到，返回false
    -- 查询是经理的员工姓名
SELECT ENAME
FROM EMP E
WHERE EXISTS(SELECT ('1') FROM EMP WHERE E.EMPNO = MGR );

  • NOT EXISTS:与上面相反，判断子查询是否没有返回值，如果没有返回值，表达式为true，如果找到一条返回值，则为false

    -- 查询不是经理的员工姓名
SELECT ENAME
FROM EMP E
WHERE NOT EXISTS(SELECT ('1') FROM EMP WHERE E.EMPNO = MGR );