数据库基础

数据库的语言

● DML（数据操作语言）

指的是对数据本身进行操作，主要包含增删改查，由开发人员操作。

● DDL（数据定义语言）

通常指的是对数据表进行的操作，由后端架构师负责。

● DCL（数据控制语言）

控制不同用户的操作权限，通常在公司由DBA（数据库管理员）负责。

数据库中表的创建

CREATE TABLE表名（
字段1 字段1类型[COMMENT  字段1注释l,
字段2 字段2类型[ COMMENT 字段2注释l,
字段3 字段3类型[ COMMENT 字段3注释l,
....
宁段n 字段n类型[COMMENT   字段n注释]
)[ COMMENT表注释];

例子：

--创建一个表 class22102
CREATE TABLE class22102(
    id        NUMBER,
    name      VARCHAR2(20)    DEFAULT    '无名氏',
    birthday  DATE
);

数据查询

简单查询

-- 1. 查看bonus表内容
SELECT * FROM bonus;
-- 2. 查看表结构
DESC bonus;

限定查询

限定查询是在简单查询的基础上，新增WHERE子句，用来限制结果的行数。

其语法结构如下：

WHERE子句支持以下运算：

● 关系运算

● 取值范围运算

● 基数范围运算

● 模糊查询

● 空判断

● 逻辑运算

4.1 关系运算

关系运算是最基础的运算，包括：

>、<、>=、<=、!=、<>、=

需要注意的是!=和<>效果完全相同，=不是赋值表示等式比较

【例子】查询出公司基础工资高于2000的雇员信息。

分析：如果一个查询，例如查询emp，结果是部分雇员信息，不是所有雇员的信息，那么这个查询通常要使用WHERE子句进行雇员的筛选。

SELECT * FROM emp WHERE sal>2000;

4.2 取值范围运算 BETWEEN...AND...

筛选两个数据的范围区间，需要注意的是，这个区间是闭区间，包含两端数据。

【例子】查询所有在1981年雇佣的雇员信息。

分析：只需要让雇佣日期落在1981年1月1日到1981年12月31日的区间上即可。

SELECT * FROM empWHERE hiredate BETWEEN '01-1月 -81' AND '31-12月-81';

4.3 基数范围运算 IN（包含），NOT IN（不包含）

表示是否落在其中的一个选项中。

【例子】查询出雇员编号为7369或7566或7839或8899（不存在）的雇员信息。

如果按照传统思维，可以通过下面的查询完成：

SELECT * FROM empWHERE empno=7369 OR empno=7566 OR empno=7839 OR empno=8899;

此时可以使用IN来简化操作：

SELECT * FROM emp WHERE empno IN (7369,7566,7839,8899);

4.4 模糊查询 LIKE

使用LIKE关键字在WHERE子句中进行模糊查询，模糊查询有两个通配符：

● _

匹配任意一个字符

● %

匹配任意多个字符（0,1,...n）

● [ ]:

表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。

● [^]：

表示不在括号所列之内的单个字符

【例子】查询所有姓名以字母A开头的雇员信息。

SELECT * FROM emp WHERE ename LIKE 'A%';

4.5 空判断 IS

NULL表示一种特殊的数值状态，这种状态不与任何数据相联系，例如不能用普通数据与NULL比较，也不能用普通数据与NULL计算等。

【例子】查询不领取佣金的人员信息。

分析：如果按照之前的关系判断，可能会写出下面的语句。

SELECT * FROM emp WHERE comm=NULL;

可以看到没有任何返回结果，NULL必须使用专用的判断方式：

● IS NULL

是否为空

SELECT * FROM emp WHERE comm IS NULL;

● IS NOT NULL、NOT IS NULL

是否不为空

SELECT * FROM emp WHERE comm IS NOT NULL;SELECT * FROM emp WHERE NOT comm IS NULL;

【例子】查询领取佣金的人（包括0）的信息

下面两种写法等效

SELECT * FROM emp WHERE comm IS NOT NULL;SELECT * FROM emp WHERE NOT comm IS NULL;

4.6 逻辑运算

包括三种运算：

● AND

所有的条件都满足，结果才满足。

● OR

所有的条件满足一个，结果就满足。

● NOT

逻辑结果反转

【例子】查询出所有销售人员并且工资高于1200的雇员信息。

分析：

条件一 职位是销售

条件二 工资高于1200

两个条件必须都满足才行，使用AND连接两个判断

SELECT * FROM emp WHERE job='SALESMAN' AND sal>1200;

5. 查询排序

通过ORDER BY子句，可以设置查询结果的排序规则。

语法结构如下：

两种排序规则：

● ASC

升序，即从小到大，从早到晚，从低到高

● DESC

降序，即从大到小，从晚到早，从高到低

【例子】查询所有雇员的信息，按照工资从高到低排序。

SELECT * FROM emp ORDER BY sal DESC;

二、函数

函数是一段可以重复利用的代码，其中有一些组成部门是特别需要关注的:

● 函数名称（包括函数的功能）

● 输入参数

● 返回值

1 字符串函数

这是一些专门用于处理字符串数据类型的函数。

1.1.1 大小写转换

函数名称与功能	输入参数	返回值
UPPER 转大写	一个字符串列（字段）	转换为大写后的字符串
LOWER 转小写	一个字符串列（字段）	转换为小写后的字符串
INITCA 首字母大写，其余字母小写	一个字符串列（字段）	转换后的字符串

【例子】查询所有雇员的姓名与职位，要求姓名全小写，职位首字母大写。

SELECT LOWER(ename),INITCAP(job) FROM emp;

【课堂练习】

1. 查询所有部门的位置，要求显示为首字母大写。

SELECT INITCAP(loc) FROM dept;

2 长度

函数名称和功能	输入参数	返回值
LENGTH 获取字符串长度	字符串类型的列	字符串长度数值

【例子】查询姓名长度为5的雇员名称。

SELECT ename FROM emp WHERE LENGTH(ename)=5;

3 数字函数

用于处理数字类型的数据列。

函数名称和功能	输入参数	返回值
ROUND 四舍五入	参数1：数字类型的数据列 [参数2：保留小数位]	处理后的数字
TRUNC 截取	数字类型的数据列 [参数2：保留小数位]	处理后的数字

【例子】查询所有雇员的姓名与基本工资，要求基本工资精确到百位（四舍五入）。

SELECT ename,ROUND(sal,-3) FROM emp;

【课堂练习】查询所有雇员的基础年薪，要求精确到万位，千位忽略。

SELECT ename,TRUNC(sal*12,-4) FROM emp;

4 时间与日期函数

Oracle数据库给每个表增加了两个隐藏列：SYSDATE、SYSTIMESTAMP，实际上这两列并不在表中，但是可以直接使用。内部包含当前的时间和日期。

SYSDATE	当前年月
SYSTIMESTAMP	当前年月日，时分秒

【例子】验证SYSDATE、SYSTIMESTAMP的存在。

SELECT SYSDATE,SYSTIMESTAMP FROM emp;

4.1 日期运算

日期可以进行加减运算，但是日期和日期之间只能进行减法运算。

【例子】计算90天后的日期。

SELECT SYSDATE+90 FROM emp;

【例子】计算每个雇员到今天为止雇佣的天数。

SELECT SYSDATE-hiredate FROM emp;

4.2 月数差

函数名称和功能	输入参数	返回值
MONTHS_BETWEEN 两个日期之间的月份差	参数1：日期列1 参数2：日期列2	两个参数的日期之间的月份

【例子】计算SCOTT雇员到今天为止的雇佣月数。

SELECT MONTHS_BETWEEN(SYSDATE,hiredate)FROM emp WHERE ename='SCOTT';

4.3 月计算

月也支持整数加减法，但是要通过一个专用的函数实现。

函数名称和功能	输入参数	返回值
ADD_MONTHS 给某个日期加上指定的月数	参数1：日期列 参数2：月数，整数	加完月数之后的日期

【例子】查询每个雇员的姓名与雇佣后半年的日期。

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

4.4 下个周几

函数名称和功能	输入参数	返回值
NEXT_DAY 求出下个周几的日期	参数1：日期列 参数2：周几	计算后的日期

【例子】下个周五是什么日期？

SELECT NEXT_DAY(SYSDATE,6) FROM emp;SELECT NEXT_DAY(SYSDATE,'星期五') FROM emp;

4.5 本月最后一天

函数名称和功能	输入参数	返回值
LAST_DAY 日期所在月的最后一天	日期列	计算后的日期

【例子】求出在每个月倒数第三天雇佣的雇员信息。

SELECT * FROM empWHERE LAST_DAY(hiredate)-2 = hiredate;

5 转换函数

5.1 字符串转换

函数名称和功能	输入参数	返回值
TO_CHAR 通常把日期转换为固定格式的字符串	参数1：日期列 参数2：转换格式，字符串	传入格式的字符串

常用的转换格式：

● yyyy

年

● mm

月

● dd

日

● hh 或 hh24

小时（12小时制 或 24小时制）

● mi

分钟

● ss

秒

【例子】显示当前日期和时间的符合中国人阅读的格式。

SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') FROM emp;

【例子】查询所有在6月份雇佣的雇员信息。

SELECT * FROM emp WHERE TO_CHAR(hiredate,'mm')=6;

5.2 空值转换

NULL不能参与任何计算，因此需要使用空值转换函数把NULL转换为特定的数值。

函数名称和功能	输入参数	返回值
NVL 把可能出现的NULL转换为特定的数值	参数1：可能为NULL的数据列 参数2：转换后的数值	如果参数1为NULL，则返回参数2； 如果参数1不为NULL，则返回参数1

【例子】查询所有雇员的姓名和年薪（包括薪金与佣金）

6. 统计函数

常用的统计函数有以下五个：

函数名称和功能	输入参数	返回值
COUNT 计数	任意列	计算后的结果
SUM 求和	数字列
AVG 平均值	数字或日期列
MAX 最大值	数字或日期列
MIN 最小值	数字或日期列

【例子】公司几个工资等级？

SELECT COUNT(*) FROM salgrade;

COUNT函数可以在面对未知的数据表时，先使用，获得数据量，最后再进行操作。

【例子】公司一个月要支付多少员工的基本工资？

分析：实际上求的是所有员工的基本工资和。

SELECT SUM(sal) FROM emp;