一、SQL
1、概述
结构化查询语言(Structured Query Language)，是数据库编程的核心语言。用于访问和处理数据库的标准的计算机语言。
- SQL 指结构化查询语言
- SQL 使我们有能力访问数据库
- SQL 是一种 ANSI 的标准计算机语言
SQL 语句用关键字、表名和列名等组合而成的一条语句。
SQL非常接近英语，关键字其实都是英文单词，记住单词的意思，使用起来非常简单。
2、SQL分类
DQL数据查询语言
Data Query Language
select用于数据查询的关键字
from
where
group by
order by
DDL数据定义语言
Data Definition Language
create创建数据库和表等对象
drop删除数据库和表等对象
alter修改数据库和表等对象
truncate删除表数据保留表结构
DML数据操作语言
Data Manipulation Language
insert向表中插入数据
update修改表数据
delete删除表中数据
DCL数据控制语言
Data Control Language
用于授权或回收访问数据库的某种特权
grant赋予用户的操作权限
revoke取消用户权限
TCL事务控制语言
Transaction Control Language
控制数据库操纵事务发生的时间及效果
commit提交、确认对数据库中的数据进行的变更
rollback回滚、取消对数据库中的数据进行的变更
savepoint保存点、是当前的事务可以退到指定的保存点
3、DQL数据查询语言
查询语句顺序
SELECT - 查询数据
SELECT 语句用于从表中选取数据，结果被存储在一个结果表中（称为结果集）。
SELECT * FROM 表名称；
我们也可以指定所要查询数据的列
SELECT 列名称 FROM 表名称;
实例
SELECT * FROM Persons; //查询表Persons的全部数据
星号（*）是选取所有列的快捷方式。
如需获取名为 “LastName” 和 “FirstName” 的列的内容（从名为 “Persons” 的数据库表），请使用类似这样的 SELECT 语句
SELECT LastName,FirstName FROM Persons;
PS: SQL 语句对大小写不敏感，SELECT 等效于 select。
DISTINCT - 去除重复值
如果一张表中有多行重复数据，如何去重显示呢？可以了解下 DISTINCT 。
SELECT DISTINCT 列名称 FROM 表名称;
实例
SELECT LASTNAME FROM Persons; ---先查询重复的内容
SELECT DISTINCT LASTNAME FROM Persons;
WHERE - 条件过滤
如果需要从表中选取指定的数据，可将 WHERE 子句添加到 SELECT 语句。
语法
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值;
下面的运算符可在 WHERE 子句中使用
| 操作符 | 描述 |
|---|---|
| = | 等于 |
| <> 或 != | 不等于 |
| > | 大于 |
| < | 小于 |
| >= | 大于等于 |
| <= | 小于等于 |
| BETWEEN | 在某个范围内 |
| LIKE | 搜索某种模式 |
实例
如果只希望选取居住在城市 “Beijing” 中的人，我们需要向 SELECT 语句添加 WHERE 子句
SELECT * FROM Persons WHERE City='Beijing';
AND & OR - 逻辑操作符
AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。
- 如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。
- 如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。
语法
AND 运算符实例
SELECT * FROM 表名称 WHERE 列 运算符 值 AND 列 运算符 值;
实例
使用 AND 来显示所有姓为 “Carter” 并且名为 “Thomas” 的人
SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter';
OR 运算符实例
SELECT * FROM 表名称 WHERE 列 运算符 值 OR 列 运算符 值;
实例
使用 OR 来显示所有姓为 “Carter” 或者名为 “Thomas” 的人
SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter';
我们也可以把 AND 和 OR 结合起来（使用圆括号来组成复杂的表达式）:
SELECT * FROM Persons WHERE (FirstName='Thomas' OR FirstName='William') AND LastName='Carter';
GROUP BY – 分组
GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。
语法
SELECT 列名A, 统计函数(列名B)
FROM 表名
WHERE 查询条件
GROUP BY 列名A;
HAVING – 分组过滤
在 SQL 中WHERE 关键字无法与合计函数一起使用，而HAVING可以和聚合函数连用。
语法
SELECT 列名A, 统计函数(列名B) FROM table_name
WHERE 查询条件
GROUP BY 列名A
HAVING 统计函数(列名B) 查询条件;
ORDER BY - 排序
ORDER BY 语句用于根据指定的列对结果集进行排序，默认按照升序对记录进行排序，如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。
order:排列查询的关键字
by；介词，后面跟列名
asc:升序（默认）
desc:降序
语法
SELECT * FROM 表名称 ORDER BY 列1,列2 DESC; ---降序排序
order by 可多条件排序，先左再右。当左边的条件有相同时，右边才排序
#先以数字倒顺序显示sal，如果工资相同，再以数字正顺序显示empno
SELECT * FROM emp ORDER BY sal desc,empno;
order by 可用别名
select ename,sal gongzi from emp order by gongzi;
LIKE – 模糊匹配
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
语法
SELECT 列名/(*) FROM 表名称 WHERE 列名称 LIKE 值;
sql通配符
| 符号 | 说明 |
|---|---|
| % | 替代0个或多个字符 |
| _ | 替代1个字符 |
| escape “\” | 表示为换码字符"\" |
SQL 通配符必须与 LIKE 运算符一起使用，表示模糊匹配，而不是跟在数学运算符比如=后面
1、现在，我们希望从上面的 “Persons” 表中选取居住在以 “N” 开头的城市里的人
SELECT * FROM Persons WHERE City LIKE 'N%';
2、接下来，我们希望从 “Persons” 表中选取居住在以 “g” 结尾的城市里的人
SELECT * FROM Persons WHERE City LIKE '%g';
3、接下来，我们希望从 “Persons” 表中选取居住在包含 “lon” 的城市里的人
SELECT * FROM Persons WHERE City LIKE '%on%';
4、通过使用 NOT 关键字，我们可以从 “Persons” 表中选取居住在不包含 “lon” 的城市里的人
SELECT * FROM Persons WHERE City NOT LIKE '%on%';
REGEXP - 正则表达式
SQL中可以使用 like、%进行模糊匹配。
在一些稍复杂的查询场景中，sql也支持正则表达式的匹配，Mysql中使用regexp操作符进行正则表达式的匹配。
匹配方式
| 分类 | 模式字符 | 说明 |
|---|---|---|
| 定位元字符 | ^ | 匹配开头 |
| $ | 匹配结尾 | |
| 表达式的替换匹配和分组 | . | 匹配除 “\n” 之外的任何单个字符 |
| [.\n] | 匹配任意单个字符 | |
| [abc] | 匹配所包含的字符集合中的任意单个字符 | |
| [a-c] | 匹配字符a到c中任意字符，即a或b或c | |
| [^abc] | 匹配所包含的字符集合之外的任意单个字符 | |
| x|y | 匹配x或y | |
| ( ) | 标记一个子表达式的开始和结束位置。 | |
| 量词或重复操作符 | * | 匹配前面的子表达式零次、一次或多次 |
| ? | 匹配 前面0 次或 1 次 | |
| + | 匹配前面的子表达式一次或多次 | |
| {n} | n 是一个非负整数。匹配确定的 n 次。例如，‘o{2}’ 不能匹配 “Bob” 中的 ‘o’，但是能匹配 “food” 中的两个 | |
| {n,m} | m 和 n 均为非负整数，其中n <= m。最少匹配 n 次最多匹配 m 次 | |
| 预定义的 POSIX 字符类 | [:alpha:] | 任何字母。 |
| [:digit:] | 任何数字。 | |
| [:alnum:] | 任何字母和数字。 | |
| [:space:] | 任何白字符。 | |
| [:upper:] | 任何大写字母。 | |
| [:lower:] | 任何小写字母。 | |
| [:punct:] | 任何标点符号。 | |
| [:xdigit:] | 任何16进制的数字，相当于[0-9a-fA-F]。 |
操作函数
| 函数 | 说明 |
|---|---|
| regexp_like(str,str1,match_option) | 查看str是否与str1相匹配 |
| regexp_instr(str,str1,start,num,num1, match_option) | 用于在str中查找str1，返回str1在str中出现的位置,匹配位置从start开始 |
| regexp_replace(str,str1,str3,start,num, match_option) | 用于在str中查找str1，并将其替换为str3 |
| regexp_substr(str,str1,start,num, match_option) | 用于在str中查找str1并返回 |
regexp_like(匹配)
regexp_like(str,str1,match_option),查看str是否与str1相匹配，该函数还可以提供一个可选的参数match_option字符串说明默认的匹配选项。match_option的取值如下
‘c’ 说明在进行匹配时区分大小写（缺省值）；
‘i’ 说明在进行匹配时不区分大小写；
‘n’(.)点号能表示所有单个字符,包括换行(俺还不知道什么地方有用到换行.只知道sql里面可以用chr(10)表示换行；
‘m’ 字符串存在换行的时候当作多行处理.这样
就可匹配每行的结尾
.
不然的话
就可匹配每行的结尾.不然的话
就可匹配每行的结尾.不然的话只匹配字符串最后的位置；
示例
#查找ename中以a开头以n结尾的行,不区分大小写
select * from emp where regexp_like(ename,'^a[a-z]*n$','i);
regexp_instr(包含)
regexp_instr(str,str1,start,num,num1, match_option)用于在str中查找str1，返回str1在str中出现的位置,匹配位置从start开始。可以参考字符串函数 instr()，参数相关
'start’开始查找的位置；
'num’说明应该返回第几次出现pattern的位置；
'num1’说明应该返回什么整数。若该参数为0，则说明要返回的整数是x中的一个字符的位置；若该参数为非0的整数，则说明要返回的整数为x中出现在str1之后 的字符的位置；
'match_option’修改默认的匹配设置.与regexp_like里面的相同。
示例
#查找第一个o的位置
SELECT REGEXP_INSTR('hello world','o',1,1,0) FROM DUAL; #结果为5
regexp_replace(替换)
regexp_replace(str,str1,str3,start,num, match_option)用于在str中查找str1，并将其替换为str3。可以参考字符串函数 replace()，参数同regexp_instr函数。
示例
#将第一个o替换成x
SELECT REGEXP_REPLACE('hello world','o','x',1,1) FROM DUAL; #结果为hellx world.
regexp_substr(提取)
regexp_substr(str,str1,start,num, match_option)用于在str中查找str1并返回。可以参考字符串函数 substr()，参数同regexp_instr()函数.
示例
#提取'll'
SELECT regexp_substr('hello world','l{2}') FROM DUAL;
#'7499,ALEN,SALESMAN,7698,19810220,1600,303,30'把名字和工作显示出来
SELECT regexp_substr('7499,ALEN,SALESMAN,7698,19810220,1600,303,30', '[^,]+', 1, 2) 名字,
regexp_substr('7499,ALEN,SALESMAN,7698,19810220,1600,303,30', '[^,]+', 1, 3) 工作
FROM dual;
IN – 锁定多个值
IN 操作符允许我们在 WHERE 子句中规定多个值。
语法
SELECT 列名 FROM 表名称 WHERE 列名称 IN (值1,值2,值3);
实例
现在，我们希望从 Persons 表中选取姓氏为 Adams 和 Carter 的人
SELECT * FROM Persons WHERE LastName IN ('Adams','Carter');
BETWEEN AND – 选取区间数据
操作符 BETWEEN A AND B 会选取介于A,B两个值之间的数据范围。这些值可以是数值、文本或者日期。
语法
SELECT 列名/(*) FROM 表名称 WHERE 列名称 BETWEEN 值1 AND 值2;
实例
1、查询以字母顺序显示介于 “Adams”**（包括）和 “Carter”（不包括）**之间的人
SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter';
2、查询上述结果相反的结果，可以使用 NOT
SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Adams' AND 'Carter';
MINUS - 差集
A minus B 就意味着将结果集A去除结果集B中所包含的所有记录后的结果，即在A中存在，而在B中不存在的记录。
表A{1，2，3，4，5,}
表B{4，5，6，7，8,}
select * from A minus select * from B；--结果1,2,3。
select * from B minus select * from A；--结果为6,7,8。
select * from A minus (select * from A minus select * from B); --结果为4,5。
AS – 别名
通过使用 SQL，可以为列名称和表名称指定别名（Alias），别名使查询程序更易阅读和书写。
语法
表别名
SELECT 列名称/(*) FROM 表名称 AS 别名;
列别名
SELECT 列名称 as 别名 FROM 表名称;
在实际使用时AS可以省略
实例
使用表名称别名
SELECT p.LastName, p.FirstName
FROM Persons p
WHERE p.LastName='Adams' AND p.FirstName='John';
使用列名别名
SELECT LastName Family, FirstName Name FROM Persons;
NVL - 空值转换
语法
#列中的空值null转换为0
select nvl(column_name,0) from table_nale;
ROWNUM / ROWID- 伪列
rownum
不是在物理上真实存在的列，它是对查询结果给了一个从一开始的排列数据，获取到结果集之后再加上去的一个列。rownum是一个伪列，只是使用他生成行号。 rownum在每一行显示的时候，都会自动增加一个行号，但需要记住rownum生成的行号不是固定的,而是动态计算得来的。
select ename,sal,deptno,rownum from emp;
select ename from emp where rownum>=3; #伪列不能大于等于一个比一大的正整数
select ename,sal,rownum from emp order by sal;
rowid
rowid指的是每行数据提供的地址。rowid大部分情况下是针对一些分析上使用的，而且在实际的开发过程中你也不会感受到rowid的存在。
select *,rowid from emp e;
NOT NULL – 非空
约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
语法
CREATE TABLE 表 (列 int NOT NULL);
注意 如果这个强制非空列插入 NULL 值，则会报错 ORA-01400 提示无法插入！
拓展小知识
NOT NULL 也可以用于查询条件
select * from persons where FirstName is not null;
同理，NULL 也可
select * from persons where FirstName is null;
4、SQL 常用函数学习
聚合函数
| 函数 | 说明 | 示例 |
|---|---|---|
| sum() | 返回数值列求和的值。NULL 值不包括在计算中。 | SELECT SUM(列名) FROM 表名; |
| avg() | 返回数值列的平均值。NULL 值不包括在计算中。 | |
| max() | 返回一列中的最大值。NULL 值不包括在计算中。 | |
| min() | 返回一列中的最小值。NULL 值不包括在计算中。 | |
| count() | COUNT(*) 返回表中的记录数。 COUNT(列名)返回指定列的值的数目（NULL 不计入） COUNT(DISTINCT 列名)返回指定列的不同值的数目。 | SELECT COUNT(*) FROM 表名; SELECT COUNT(列名) FROM 表名; SELECT COUNT(DISTINCT 列名) FROM 表名; |
where条件里为什么不能有聚合函数
聚集函数也叫列函数，它们都是基于整列数据进行计算的，而where子句则是对数据行进行过滤的(这里过滤是在一个记录里边过滤的,基于"行")，在筛选过程中依赖“基于已经筛选完毕的数据得出的计算结果”是一种悖论，这是行不通的。更简单地说，因为聚集函数要对全列数据时行计算，因而使用它的前提是结果集已经确定！
而where子句还处于“确定”结果集的过程中，因而不能使用聚集函数。
与where子句不能出现聚集函数正相反的是，我们几乎看不到不使用聚集函数的having子句。为什么？因为在水平方向上根据外部指定条件的筛选（也就是对行的筛选），where子句可以独立完成，剩下的往往都是需要根据结果集自身的统计数据进一步筛选了，这时，几乎都需要通过having子句配合聚集函数来完成。
字符函数
| 函数名 | 函数说明 | 示例 |
|---|---|---|
| ascii（） | 字符 转 ASCII | select ascii(‘A’) from 表名; |
| chr() | ASCII 转 字符 | select chr(65) from 表名; |
| concat(str1,str2) | 合并字符串str与str2,与 || 用法相同 | select concat(str1,str2) from 表名; |
| initcap(str) | 将单词首字母大写（以空格区分单词） | select initcap(str) from 表名; |
| upper() | 将字符串大写 | select upper(列名) from 表名; |
| lower() | 将字符串小写 | select lower(列名) from 表名; |
| replace(str,str1,str2) | replace(列名,‘需替换字符’,‘替换字符’) | select replace(ename,‘M’,‘MM’) from emp; |
| translate(str,str1,str2) | translate(列名,‘需替换字符列表’,‘替换字符列表’) | select translate(‘abc’,‘ac’,‘AC’) from dual; |
| substr() 截取字符 | substr(列名,截取开始位,截取长度) | select substr(str,start,len) from 表名; |
| instr() 字符位置 | instr(列名,‘需要查找的字符’,从第几位开始,目标字符出现次数) | select instr(ename,‘A’,1,1) from emp; |
| lpad() 左填充 | lpad(列名,填充后长度,使用什么填充) | select lpad(ename,10,’ ') from emp; |
| rpad() 右填充 | rpad(列名,填充后长度,使用什么填充) | select rpad(ename,10,’ ') from emp; |
| trim() 剔除 | trim(both | leading | trailing ‘剔除字符’ from 列名) | select trim(both ’ ’ from ename) from emp; |
| ltrim() 左剔除 | ltrim(列名,’剔除字符‘) | select ltrim(ename,’ ') from emp; |
| rtrim() 右剔除 | rtrim(列名,’剔除字符‘) | select rtrim(ename,’ ') from emp; |
| length() 字符长度 | length(列名) | select length(ename) from emp; |
数学函数
| 函数名 | 函数说明 | 示例 |
|---|---|---|
| abs() 取绝对值 | abs(num) | abs(-10)=10 |
| sign() 取符号/标志位 | 可用于判断两个数值的大小 | sign(4)=1 sign(-99)=-1 sign(0)=0 |
| ceil() 取最大整数 | ceil(num) | ceil(5.1)=6 |
| foor() 取最小整数 | floor(num) | floor(5.8)=5 |
| mod() 取余数 | mod(被除数，除数) | mod(8,3)=2 |
| power() 幂运算 | power(底数,指数) | power(3,2)=9 |
| round() 取最大有效数 | round(数值型列名,精度) 四舍五入 | round(5.555,2)=5.56 round(5.555,-1)=10 |
| trunc() 取最小有效数 | trunc(数值型列名,精度) | trunc(5.555,2)=5.55 trunc(5.555,-1)=0 |
转换函数
| 函数 | 功能 | 示例 |
|---|---|---|
| to_char | 转换成字符 | to_char(hiredate,‘yyyymmdd’) |
| to_date | 转换成时间 | to_date(20210922,‘yyyymmdd’) |
| to_number | 转换成数字 | to_number(C999999) |
转换格式
- 日期类:
| 格式符 | 格式说明 | 示例 |
|---|---|---|
| CC SCC | 世纪 | to_char(sysdate,‘cc’) to_char(sysdate,‘scc’) |
| YYYY RRRR | 年 | to_char(sysdate,‘yyyy’) to_char(sysdate,‘RRRR’) |
| Q | 季度 | to_char(sysdate,‘Q’) |
| MM MON MONTH | 月 | to_char(sysdate,‘MM’) to_char(sysdate,‘MON’) to_char(sysdate,‘MONTH’) |
| WW W | 周 | to_char(sysdate,‘ww’) to_char(sysdate,‘w’) |
| DDDD DDD DD D | 日 | to_char(sysdate,‘DDD’) to_char(sysdate,‘DD’) to_char(sysdate,‘D’) |
| DAY | 星期 | to_char(sysdate,‘DAY’) |
| HH24 HH | 时 | to_char(sysdate,‘HH24’) |
| MI | 分 | to_char(sysdate,‘MI’) |
| SS FF | 秒 | to_char(sysdate,‘SS’) to_char(sysdate,‘FF9’) |
数字类:
| 格式符 | 格式说明 | 示例 |
|---|---|---|
| B | 添加空格 | to_char(12345,‘B999999’) |
| C | 添加国际货 币符号 | to_char(12345,‘C999999’) =CNY12345 |
| D | 添加小数位 数同‘.’ | to_char(12345,‘99999D9’) =12345.0 |
| G | 添加数组分 割 | to_char(123456,‘99G99G99’) =12 34 56 |
| L | 添加本地货 币符 | to_char(12345,‘L999999’) =¥12345 |
| 9 | 1、在小数点前存在非0数字则显示数字（不包括数字0，如果存在数字0，作为不存在数字处理，还是显示空格），数字0或者不存在数字显示空格； 2、在小数点后存在非0数字则显示数字（不包括数字0，如果存在数字0，作为不存在数字处理，还是显示0，但是这个0是9转换过去的0，所以在使用FM是会消除这个9转换过去的0），数字0或者不存在数字显示0； | to_char(1,‘999.9’)= 1.0 |
| 0 | 存在数字显示数字，不存在显示0 | to_char(1,‘09.9’) = 01.0 |
| FM | FM消除了因为使用9而转换过来的空格和0 | to_char(1,‘FM9999.009’) = 1.00 |
| $ | 数字开头返回一个美元符号 | to_char(12345,‘$999999’) =$12345 |
| . | 指定位置返回小数点 | to_char(1,‘9.9’) = 1.0 |
| , | 指定位置返回逗号 | to_char(1000000,‘9,999,999’) = 1,000,000 |
日期函数
| 函数符号 | 说明 | 示例 |
|---|---|---|
| sysdate | 获取系统时间 | |
| to_date() | 转换成时间格式 | to_date(20210922,‘yyyy-mm-dd’) |
| add_months(x,y) | x时间基础上加上y月，y为负数则减去 | add_months(sysdate,3) |
| months_between(x,y) | 给出x和y之间的月份差，x数值大则为正，反之则负 | months_between(日期1,日期2) |
| last_day(x) | 给出x的月最后一天 | last_day(sysdate) |
| next_day(x,day) | 给出x开始到下一个DAY的时间，day可以是星期几。 Monday星期一 / Tuesday星期二 / Wednesday星期三 / Thursday星期四 / Friday星期五 / Saturday星期六 / Sunday星期日 | next_day(sysdate,3) --显示下一个星期二 |
| round() | 四舍五入获取日期 | round(sysdate,yyyy) |
| trunc() | 截取获取日期 | trunc(sysdate,mm) |
| interval() | 时间跨度表达 | interval ‘1 2:30’ day to minute --一天零两小时 三十分 |
| extract() | 时间截取函数 能够从 时间和时间戳类型的数据中截取年月日时分秒 |
条件判断函数
| 函数 | 说明 |
|---|---|
| 简单 case表达式 | 等值比较（=），只能处理简单的逻辑 |
| 搜索 case 表达式 | 可以进行复杂的逻辑处理 |
| decode语句 | 似于简单 CASE 表达式 |
- case when 语句
1.简单 case表达式
简单 CASE 表达式使用的是等值比较（=），只能处理简单的逻辑。
如果想要进行复杂的逻辑处理，例如根据考试成绩评出优秀、良好、及格等，或者判断表达式的值是否为空，就需要使用更加强大的搜索 CASE 表达式。
注意简单case表达式when后面不能放表达式，只能放数值、字符串、日期等值。
语法
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
ELSE default_result
END
说明
首先计算 expression 的值；然后依次与 WHEN 列表中的值（value1，value2，…）进行比较，找到第一个相等的值并返回对应的结果（result1，result2，…）；
如果没有找到相等的值，返回 ELSE 中的默认结果；如果此时没有指定 ELSE，返回 NULL 值。
示例
--对部门进行评级
select deptno,
case deptno
when 10 then '第一部门'
when 20 then '第二部门'
when 30 then '第三部门'
else '第四部门'
end 部门
from emp;
CASE 表达式除了可以用于 SELECT 列表，也可以出现在其他子句中，例如 WHERE、GROUP BY、ORDER BY 等。以下语句使用 CASE 表达式实现了自定义的排序规则
SELECT ename,
case deptno
when 10 then '第一部门'
when 20 then '第二部门'
when 30 then '第三部门'
else '第四部门'
end 部门
FROM emp
ORDER BY
case deptno
when 10 then '第一部门'
when 20 then '第二部门'
when 30 then '第三部门'
else '第四部门'
END;
2.搜索 case 表达式
语法
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
END
按照顺序依次计算每个分支中的条件（condition1，condition2，…），找到第一个结果为真的分支并返回相应的结果（result1，result2，…）；
如果没有任何条件为真，返回 ELSE 中的默认结果；如果此时没有指定 ELSE，返回 NULL 值。
示例
--对全体人员的工资进行评级
select ename,sal,
case when sal > 4000 and sal <= 5000 then 'A级'
when sal > 3000 and sal <= 4000 then 'B级'
when sal > 2000 and sal <= 3000 then 'C级'
when sal > 1000 and sal <= 2000 then 'D级'
when sal <= 1000 then 'F级'
else'不知道'
end sal_grade
from emp
该查询的结果与前文中的简单 case表达式示例相同。decode是 Oracle 专有函数，推荐大家使用标准的 case表达式。
- decode语句
decode函数可以实现类似于简单 CASE 表达式的功能
语法:
select DECODE(expression,
value1, result1,
value2, result2,
...,
default_result )
from 表名;
该函数依次比较表达式 expression 与 valueN 的值，如果找到相等的值就返回对应的 resultN；
如果没有匹配到任何相等的值，返回默认结果 default_result；
如果此时没有提供 default_result，返回 NULL 值。
示例
--判断所有人员是否有奖金
select ename,
decode(comm,'','无奖金',
'有奖金') howcomm
from emp;
行转列函数
pivot列转行
语法
SELECT *
FROM (数据查询集) PIVOT (SUM(Score/行转列后 列的值/) FOR coursename/需要行转列的列/ IN (转换后列的值)
示例
select * from sc
pivot(max(score) for cno in ('c001','c002','c003','c004','c005','c006','c007','c008','c009','c010'))
unpivot 列转行:
示例
select * from (select * from sc pivot(max(score) for cno in
('c001'as c001,'c002' as c002,'c003' as c003 ,'c004'as c004,'c005'as c005,'c006'as c006,'c007'as c007,'c008'as c008,'c009'as c009,'c010'as c010)))
unpivot (score for cno in (c001,c002,c003,c004,c005,c006,c007,c008,c009,c010));
拓展
listagg() witnin group()函数
#统计每种job下面有哪些员工，要求在一行显示员工姓名
#用法1
select job,listagg(ename,',') within group(order by ename)
from emp
group by job;
#用法2分析函数 用over()k
select job,listagg(ename,',') within group(order by ename)
over(partition by job)
from emp ;
wm_concat函数
#统计每种job下面有哪些员工，要求在一行显示员工姓名
#用法1
select job,wm_concat(ename) from emp group by job;
#用法2
select job,wm_concat(ename)over(partition by job) from emp;
# wm_concat()括号里可以任意使用||合并字符串
select job,wm_concat(' '||ename) from emp group by job;
开窗函数
开窗函数格式 函数名(列) over(partition by 分组列 order by 排序列)
OVER 关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数，使用 OVER 关键字来区分这两种用法。
开窗行数用partition by 关键字来进行分区
select deptno,sal,row_number() over(partition by deptno order by sal desc)
from emp;
排序开窗函数
| 函数 | 说明 | 示例 |
|---|---|---|
| row_number() | 将select查询到的数据进行排序，每一条数据加一个序号。排序的序号和rownum伪列相同，连续序号，不考虑值重复的情况（值相同序号不相同)。 | select sal,row_number() over(order by sal desc) from emp; |
| rank() | rank()是跳跃的排名函数，序号不连续，考虑重复数据，如果值相等序号相同。 | select sal,rank() over(order by sal desc) from emp; |
| dense_rank() | dense_rank()密集的排名，紧密排序，可以用来做排序，它序号连续，考虑重复数据，如果值相等序号就相同。 | select sal,dense_rank() over(order by sal desc) from emp; |
聚合函数开窗
| 函数 | 说明 | 示例 |
|---|---|---|
| avg() | 求平均 | avg(num) over(partition by 分组列 order by 排序列) |
| sum() | 求和 | sum(num) over(partition by 分组列 order by 排序列) |
| max() | 求最大值 | max(num) over(partition by 分组列 order by 排序列) |
| min() | 求最小值 | min(num) over(partition by 分组列 order by 排序列) |
| count() | 求行数 | count(num) over(partition by 分组列 order by 排序列) |
示例
#显示部门平均工资的最大值
select deptno, a, r
from (select deptno,
avg(sal) a,
row_number() over(order by avg(sal) desc) r
from emp
group by deptno)
where r = 1;
#显示部门平均工资,以及部门内最高工资
select distinct deptno,
avg(sal) over(partition by deptno),
max(sal) over(partition by deptno)
from emp;
5、子查询 ★
定义子查询是嵌套在查询中的查询。
使用子查询原因很多时候，我们对数据的查询处理不是一个查询语句就可以完成的；
不能完成的原因，可能是查询条件比较复杂，也可能是受困于SQL自身语法的限制；
这时候，子查询作为查询条件的重要组成部分，用在WHERE子句以及HAVING子句中，可以帮助我们快速灵活的完成查询操。
从语句形式上来看，子查询有两个特点
1)，子查询需要包含在括号内。
2)，子查询通常放在比较条件的右侧。
从查询结果上来看，子查询可以分为两个类别
1)，单行子查询或称单值子查询，顾名思义，子查询的结果集是一个值。单值子查询，由于只返回一个值，我们可以使用<、>、=、>=、<=、<>等运算符对其进行判断运算。
2)，多行子查询再次顾名思义，子查询的结果集是多行。
多行子查询有自己的运算符，常用的由IN（存在）、NOT IN（不存在）、EXISTS（存在）、NOT EXISTS（不存在）、ALL（所有的）、ANY（任意一个）等。
注意in和exists，not in和not exists的之间的转换
示例
select *
from emp
where (job,sal) in (select job,sal from emp where deptno=30);
>all表示大于最大值 <all表示小于最小值
>any表示大于最小值 <any表示小于最大值 =any和in类似
示例
#查询工资比20号部门工资都大的员工信息
Select * from emp where sal>all(select sal from emp where deptno=20);
select * from emp where sal>(select max(sal) from emp where deptno=20);
#查询工资比20号部门工资任意一个大的员工信息
select * from emp where sal>any(select sal from emp where deptno=20);
select * from emp where sal>(select min(sal) from emp where deptno=20);
in和exists 的使用
in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。通常情况下采用exists要比in效率高，因为IN不走索引，但要看实际情况具体使用in适合于外表大而内 表小的情况；exists适合于外表小而内表大的情况。
其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，**如果是exists，那么以外层表为驱动表先被访问；如果是in，那么先执行子查询。**所以我们会以驱动表的快速返回为目标， 那么就会考虑到索引及结果集的关系了 ，另外in不对NULL进行处理。
exists语法
exists用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值true或false。
exists指定一个子查询，用于检测行的存在。当子查询的行存在时，则执行主查询表达式，否则不执行。
示例
#查询部门编号小于30的所有员工信息
select *
from emp
where deptno in (select deptno from dept where deptno <30);
select *
from emp e
where exists (select * from dept d where deptno <30 and d.deptno = e.deptno);
/*
1.首先执行外查询 select * from emp e，然后取出第一行数据，将数据中的部门编号传给内查询
2.内查询执行select * from dept d where deptno <30 and d.deptno = e.deptno ；看是否查询到结果，查询到，则返回true，否则返回false；比如传来的是30，则不满足deptno <30 and d.deptno = 30，返回false
3.内查询返回true，则该行数据保留，作为结果显示；反之，返回false，则不作结果显示
4.逐行查询，看内查询是否查到数据，是否保留作结果显示
*/
#查询emp表中各部门工资最少的人员信息
select * from emp e
where Exists
(select deptno,min(sal) from emp p where e.deptno=p.deptno group by deptno
having min(p.sal)=e.sal)
在一个查询的某些位置，嵌套其余的查询语句
某些位置A/B/C/E后面可以跟子查询
嵌套嵌套的查询一定是完整的查询
其余可以嵌套多个查询语句
select A from B where C group by D having E order by F;
where后面用子查询
# 1、单行单列
select ename from emp where sal=(select max(sal) from emp );
# 2、单行多列
select ename from emp where (ename,empno,sal) in (select ename,empno,sal from emp where empno=7369);
#查询员工工资和工作都和20号部门同时一样的员工信息
select * from emp where (sal,job) in (select sal,job from emp where deptno=20 and empno=7369) and deptno<>20;
# 3、多行单列
select ename from emp where sal in (select sal from emp);
# 4、多行多列
select empno,ename,sal from emp where (empno,sal) in (select empno,sal from emp);
having后面用子查询
#查询部门平均工资大于整个公司平均工资的部门
select deptno,avg(sal) from emp group by deptno having avg(sal)>(select avg(sal) from emp);
#查询工资比公司平均高的员工
Select ename,avg(sal)
from emp
GROUP BY ename
HAVING avg(sal) > (select avg(sal) from emp);
select ename,sal
from emp e
where sal >(select avg(sal) from emp );
from后面用子查询
select * from (select dname from dept);
示例
#求工资前五名的员工姓名
SELECT ename
FROM (SELECT ename,row_number() over(ORDER BY sal desc) a FROM emp)
WHERE a <= 5;
#求每个部门工资最高的员工姓名
SELECT deptno,ename,sal
from(SELECT deptno,ename,sal,dense_rank()over(PARTITION BY deptno ORDER BY sal desc) a FROM emp)
WHERE a = 1;
select 后面用子查询
注意子查询放到select 后面只能用单行单列
select ename,(select ename from emp where empno=7369) from emp;
6、联合查询 ★
联合查询用于数据源不止一个的情况下，通过将多个表进行拼接后生成一个临时的数据源就被称为表连接。SQL联合查询主要是涉及两个表或者多个表（自身表多次）的查询。
JOIN - 连接查询
| 函数 | 名称 | 说明 |
|---|---|---|
| inner join或 join | 内连接 | 内部连接，返回两表中匹配的行 |
| left outer join或 left join | 左外连接 | 即使右表中没有匹配，也从左表返回所有的行 |
| right outer join或 right join | 左外连接 | 即使左表中没有匹配，也从右表返回所有的行 |
| full outer join或 full join | 全外连接 | 只要其中一个表中存在匹配，就返回行 |
| cross join | 交叉连接 | 笛卡儿积 |
| natural join | 自然连接 | 默认使用相同的列作为连接条件 |
| using(列名) | 指定连接 | 当有多个列相同时，可以指定用哪一列来做链接 |
join用于根据两个或多个表中的列之间的关系，从这些表中查询数据。有时为了得到完整的结果，我们需要从两个或更多的表中获取结果。我们就需要执行join。
数据库中的表可通过键将彼此联系起来。主键（Primary Key）是一个列，在这个列中的每一行的值都是唯一的。在表中，每个主键的值都是唯一的。这样做的目的是在不重复每个表中的所有数据的情况下，把表间的数据交叉捆绑在一起。
语法
select 列名
from 表A
INNER|LEFT|RIGHT|FULL JOIN 表B
ON 表A主键列 = 表B外键列;
实例
如果我们希望列出所有人的定购，可以使用下面的 SELECT 语句
SELECT e1.ename 员工,
e1.sal 员工工资,
s1.grade 员工工资级别,
e2.ename 领导,
e2.sal 领导工资,
s2.grade 领导工资级别
FROM emp e1
LEFT JOIN emp e2 ON e1.mgr=e2.empno
JOIN salgrade s1 ON e1.sal BETWEEN s1.LOSAL AND s1.HISAL
JOIN salgrade s2 ON e2.sal BETWEEN s2.LOSAL AND s2.HISAL;
UNION – 集合查询
| 操作符 | 名称 | 说明 |
|---|---|---|
| union | 并集 | 所有行，包含重复行 |
| union all | 并集 | 合并所有行，不包含重复 |
| intersect | 交集 | 返回共有行，即重复行 |
| minus | 差集 | 将表二中的行剔除后的剩余记录 |
操作符用于合并两个或多个 SELECT 语句的结果集。集合操作符可以把两个或者多个查询返回的行组合起来。
注意 UNION 内部的 SELECT 语句列数必须相同，列的数据类型也必须相同。同时，每条 SELECT 语句中的列的顺序必须相同。
UNION 语法
SELECT 列名 FROM 表A
UNION
SELECT 列名 FROM 表B;
示例
#求emp表ename中含’A‘或含有‘M’
select * from emp where ename like '%A%'
union
select * from emp where ename like '%M%';
#求emp表ename中即含’A‘又含有‘M’
select * from emp where ename like '%A%'
intersect
select * from emp where ename like '%M%';
7、DDL数据定义语言
CREATE - 创建表
一个数据库通常包含一个或多个表。每个表由一个名字标识（例如“客户”或者“订单”）。
表包含带有数据的记录(行)和字段(列)。
语法
create table 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
);
示例
create table emp2
(
empno number ,--6位数 ，3为小数
ename varchar2(50),
job varchar2(50),
mgr number,
hiredate date,
sal number,
comm number,
deptno number,
constraint pk_emp_empno primary key(empno)
--constraint uk_emp_job unique(job)
--constraint ck_emp_sal check(sal between 800 and 5000)
--constraint fk_emp_deptno foreign key (deptno) references dept(deptno)
);
复制表
语法
create table new_table as select * from old_table [where]
示例
--只复制表结构
create table emp1 as select * from scott.emp where 1<>1;
--复制表结构和数据
CREATE TABLE emp1 AS SELECT * FROM emp;
CREATE TABLE emp1 AS SELECT * FROM emp where deptno = 10;
ALTER - 修改表
功能
-
添加一个新字段
alter table 表名 add 列名 类型 [约束&默认值]; alter table student add City varchar(255) not null; -
修改现有的列定义
alter table 表名 modify 列名 类型 [约束 默认值];--修改表中的列 alter table 表名 modify 列名 类型 number(30); --去掉列的非空约束 alter table 表名 modify 列名 类型 not null; --给列添加非空约束 -
删除表中的列
alter table 表名 drop column 列名; alter table student drop column sname; -
新的列定义默认值
alter table 表名 modify 列名 default 默认值; alter table student modify city default '北京'; -
重命名列
alter table 表名 rename column 旧列名 to 新列名; --改列名 rename 表名 to 新列名; --改表名 -
将表更改为只读状态
ALTER TABLE 表名 READ ONLY; ALTER TABLE 表名 READ WRITE; --改回可编辑模式 -
添加约束
ALTER table 表名 add constraint 约束名 约束语法 (列名); --给表添加表级约束 ALTER table emp add constraint FK_DEPTNO foreign key(DEPTNO) references DEPT (DEPTNO);--添加外键约束 -
删除约束
alter table 表名 drop constraint 约束名; --删除一个约束 -
修改用户
alter user scott account unlock/lock; 解锁 alter user scott identified by password; 修改密码
TRUNCATE - 删除表内数据
如果我们仅仅需要除去表内的数据，但并不删除表本身，那么我们该如何做呢？
可以使用 TRUNCATE TABLE 命令（仅仅删除表格中的数据）
语法
TRUNCATE TABLE 表名称;
实例
本例演示如何删除名为 “Persons” 的表。
TRUNCATE TABLE persons;
DROP – 删除表
DROP TABLE 语句用于删除表（表的结构、属性以及索引也会被删除）。
语法
DROP TABLE 表名称;
8、DML数据操作语言
INSERT - 插入数据
insert into 语句用于向表格中插入新的记录（行）。
语法
insert into 表名称 values(值1, 值2,....);
insert into 表名称 (列1, 列2,...) values(值1, 值2,....);
Insert into 表名1 (列名1，列名2，列名3) select * from 表名2 where
实例
INSERT INTO emp2 values(001,'lmno','manager',000,to_date('2018-02-24','yyyy-mm-dd'),6000,1000,10);
INSERT INTO emp2 values(002,'lpy','analyst',001,to_date('2019-02-24','yyyy-mm-dd'),5000,1000,10);
INSERT INTO emp2 values(003,'abcd','salesman',002,to_date('2020-02-24','yyyy-mm-dd'),4000,1000,10);
INSERT INTO emp2 values(004,'defg','PRESIDENT',003,to_date('2021-02-24','yyyy-mm-dd'),3000,1000,10);
INSERT INTO emp2 values(005,'hijk','clerk',004,to_date('2022-02-24','yyyy-mm-dd'),2000,1000,10);
SELECT * FROM Persons;
insert into emp2 (empno,ename) values('006', 'pqrs');
insert into emp2 (empno,ename) select from emp where deptno = 10;
UPDATE – 修改数据
用于修改表中的数据。
语法
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值;
实例
更新某一行中的一个字段（列）
目前 Persons 表有很多字段为 null 的数据，可以通过 UPDATE 为 LASTNAME 是 “Wilson” 的人添加FIRSTNAME
UPDATE Persons SET FirstName = 'Fred' WHERE LastName = 'Wilson';
更新某一行中的若干列
UPDATE Persons SET ID_P = 6,city= 'London' WHERE LastName = 'Wilson';
DELETE – 删除数据
DELETE 语句用于删除表中的记录（行）。
语法
DELETE FROM 表名称 WHERE 列名称 = 值;
实例
删除某行
删除 Persons 表中 LastName 为 “Fred Wilson” 的行
DELETE FROM Persons WHERE LastName = 'Wilson';
删除所有行
可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的
DELETE FROM table_name;
删除重复数据
#查询表中Name 重复的数据
select sname from Student group by sname having count(sname) > 1;
方法
# 1.有主键，通过唯一列最大或最小方式删除重复记录
delete from Student
where sname in (select sname from Student group by sname having count(sname) > 1) and
sno not in(select max(sno) from Student group by sname having count(sname) > 1)
# 2.无主键使用ROW_NUMBER()函数删除重复记录
DELETE FROM sc1
WHERE sno IN (SELECT DISTINCT sno FROM ((SELECT sno,ROW_NUMBER()over(PARTITION BY sno ORDER BY score) ranking FROM sc1))
WHERE ranking > 1);
# 3.用 rowid删除
Delete from sc1
where rowid not in (Select min(rowid) from sc1 group by sno);
MERGE - 关联操作
根据与源表联接的结果，对目标表执行插入、更新或删除操作。例如，根据在另一个表中找到的差异在一个表中插入、更新或删除行，可以对两个表进行同步。
语法
MERGE INTO 目标表 A
USING 源表 / sql语句结果集 B
ON (两表关联条件 and 两表关联条件2)
WHEN MATCHED THEN
[UPDATE set 列=值]
[delete where 判断条件]
WHEN NOT MATCHED THEN
[INSERT values(值) ]
delele 只能跟update 一起使用，同时where只能出现一次，如果update 使用了where，delete后面的 where就无效了。同时注意使用Merge关键字只能更新一个表，源表中不能有重复的记录。
示例
merge into sc2
using sc
on (sc2.sno=sc.sno and sc2.cno=sc.cno)
when matched then update set sc2.score=sc.score
delete where (sc2.sno!=sc.sno and sc2.cno!=sc.cno)
when not matched then insert values(sc.sno,sc.cno,sc.score);
--无限制插入
merge into sc2 sc2
using sc sc
on (1=0)
when not matched then
insert (sc2.sno,sc2.cno,sc2.score) values(sc.sno,sc.cno,sc.score);
9、TCL事务控制语言
事务的正确执行可以使数据库从一种状态变换成为另一种状态，这不同于文件系统，它是数据库所特用的。
TCL语句包括commit、rollback、savepoint，用于提交和回滚，设置回滚点等操作。当执行了DML语句后使用TCL语句提交或撤回。
事务开始结束
事务由连接到数据库后，执行DML、DCL、DDL语句开始，以下情况发送后结束事务
- 执行DDL(例如ALTER TABLE)，DCL(例如GRANT),系统自动执行COMMIT语句
- 执行COMMIT / ROLLBACK
- 退出/断开数据库的连接自动执行COMMIT语句
- 进程意外终止，事务自动rollback
- 事务COMMIT时会生成一个唯一的系统变化号（SCN）保存到事务表
事务的特性 - ACID
- Atomicity（原子性）: 事务中sql语句不可分割，要么都做，要么都不做
- Consistency(一致性) 指事务操作前后，数据库中数据是一致的，数据满足业务规则约束 （例如账户金额的转出和转入），与原子性对应。
- Isolation（隔离性）多个并发事务可以独立运行，而不能相互干扰，一个事务修改数 据未提交前，其他事务看不到它所做的更改。
- Durability（持久性）事务提交后，数据的修改是永久的。
事务并发问题
oracle支持的隔离级别不支持脏读。
幻读事务T1读取一条指定where条件的语句，返回结果集。此时事务T2插入一行新记录并commit， 恰好满足T1的where条件。然后T1使用相同的条件再次查询，结果集中可以看到T2插入的记录，这条新 纪录就是幻想。
不可重复读事务T1读取一行记录，紧接着事务T2修改了T1刚刚读取的记录并commit，然后T1再次查 询，发现与第一次读取的记录不同，这称为不可重复读。
脏读事务T1更新了一行记录，还未提交所做的修改，这个T2读取了更新后的数据，然后T1执行回滚操 作，取消刚才的修改，所以T2所读取的行就无效，也就是脏数据。
事务锁
锁是一种机制，多个事务同时访问一个数据库对象时，该机制可以实现对并发的控制。
锁的类别
- DDL锁 oracle自动的施加和释放
- DML锁事务开始时施加，使用Commit后者Rollback被释放
- 内部锁 由oracle自己管理以保护内部数据库结构
锁的粒度
- 行级锁（TX）阻止该行上的DML操作，直到Commit或者Rollback
- 表级锁（TM）
- 数据库级锁 eg 将数据库锁定为只读模式 alter database open read only;
eg: 将数据库设置为限制模式（导入导出数据库时使用）alter system enable restricted session;
锁级别
| 锁模式 | 锁描述 | 说明 | SQL |
|---|---|---|---|
| 0 | none | ||
| 1 | null | 无 | select |
| 2 | RS（行级共享） | 其他事务只能查询 | for update |
| 3 | RX（行级排他） | 该事务提交前其他事务无法dml | insert | update | delete | lock row share |
| 4 | S（共享） | 共享锁 | create index | lock share |
| 5 | SRX（共享行级排他锁） | lock share row exclusive | |
| 6 | X（排他锁） | 排他锁 | alter table | drop table | drop index | truncate | lock exclusive |
示例
lock table emp in row share mode;
lock table emp in row exclusive mode; --用于行的修改
lock table emp in share mode; --阻止其他DML操作
lock table emp in share row exclusive mode; --阻止其他事务操作
lock table emp in exclusive mode; --独立访问使用
避免锁死
- 避免应用不运行长事务。
- 经常提交以避免长时间锁定行。
- 避免使用LOCK命令锁定表。
- 在非高峰期间执行DDL操作，在非高峰期间执行长时间运行的查询或事务。
解锁步骤
-- 1.查看被锁的表
Select b.owner,b.object_name,a.session_id,a.locked_mode
From v$locked_object a,dba_objects b
Where b.object_id = a.object_id;
-- 2.杀掉进程 system用户
SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock" FROM v$session WHERE sid IN (SELECT sid FROM v$lock WHERE block = 1);
alter system kill session '30,3';
10、对象
表
数据类型
| 数据类型 | 描述 |
|---|---|
| number() | |
| integer(size),int(size),smallint(size),tinyint(size) | 仅容纳整数、在括号内规定数字的最大位数 |
| decimal(size,d),numeric(size,d) | 容纳带有小数的数字、“size” 规定数字的最大位数、“d” 规定小数点右侧的最大位数 |
| char(size) | 容纳固定长度的字符串（可容纳字母、数字以及特殊字符）、在括号中规定字符串的长度 |
| varchar(size) | 容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）、在括号中规定字符串的最大字节 |
| date(yyyymmdd) | 日期类型 |
事例:
本例演示如何创建名为 “Persons” 的表。
该表包含 5 个列，列名分别是“Id_P”、“LastName”、“FirstName”、“Address” 、 “City”
create table Persons
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
Id_P 列的数据类型是 int，包含整数。其余 4 列的数据类型是 varchar，最大长度为 255 个字符。
约束
约束是强加在表上的规则或条件，确保数据库满足业务规则，保证数据的完整性。
当对表进行DML或 DDL操作时，如果此操作会造成表中的数据违反约束条件或规则的话，系统就会拒绝执行这个操作。约 束可以是列一级别的 也可以是表级别的。定义约束时没有给出约束的名字，ORACE系统将为该约束自动 生成一个名字，其格式为SYS_Cn，其中n为自然数(强烈建议各位在创建表或增加约束时，给约束定义名 称。
约束的功能实现一些业务规则，防止无效的垃圾数据进入数据库，维护数据库的完整性(完整性指正确 性与一致性)。从而使数据库的开发和维护都更加容易。
约束类型
1.主键约束（pk_）
如果表中一个字段或多个字段组合起来的值是唯一的，就可以作为表的主键，在创建或修改表时用 primay key关键字来指定主键。主键是定位表中单个行的方式，可唯一确定表中的某一行，关系型数据 库要求所有表都应该有主键，不过Oracle没有遵循此范例要求，Oracle中的表可以没有主键（这种情况不多见）。
关于主键有几个需要注意的点
1.键列必须必须具有唯一性，且不能为空，其实主键约束相当于 unique + not null
2.一个表只允许有一个主键
3.主键所在列必须具有索引（主键的唯一约束通过索引来实现），如果不存在，将会在索引添加的时 候自动创建
主键的作用
- 体现数据结构设计的合理性。
- 提升数据操作的速度。
- 保证数据的完整性，在表中添加或修改记录时，数据库会检查该记录主键的值，不允许与其它记录 主键的值重复。
语法
constraint 约束名 primary key(列名)
constraint pk_sc1_sno_cno primary key (sno,cno)
2.唯一约束（uk_）
唯一性约束可作用在单列或多列上，对于这些列或列组合，唯一性约束保证每一行的唯一性。
UNIQUE需要注意
-
对于unique约束来讲，索引是必须的。如果不存在，就自动创建一个（unique的唯一性本质上 是通过索引来保证的）
-
unique允许null值，unique约束的列可存在多个null,因为unique唯一性通过btree索引 来实现，而b-tree索引中不包含null。当然，这也造成了在where语句中用null值进行过滤会造成全 表扫描。
-
唯一性约束和主键的区别
主键所有组成主键的列都不能包含空值。
唯一性约束如果唯一性约束由多列组成，其中的部分列可以包含空值。
Oracle中不容许在相同列上既创建主键又创建唯一性约束。
语法
constraint 约束名 unique(列名);
constraint uk_emp_job unique(job);
3.非空约束（un_）
非空约束作用的列也叫强制列。顾名思义，强制键列中必须有值，当然建表时候若使用default关键字指定了默认值，则可不输入。
语法
列名 类型 not null
create table emp6 (sal number(6,0) not null);
4.外键约束(fk_)
外键约束定义在具有父子关系的子表中，外键约束使得子表中的列对应父表的主键列，用以维护数据库的完整性。不过出于性能和后期的业务系统的扩展的考虑，很多时候外键约束仅出现在数据库的设计中，实际会放在业务程序中进行处理。
外键约束注意以下几点
- 外键约束的子表中的列和对应父表中的列数据类型必须相同，列名可以不同
- 对应的父表列必须存在主键约束（PRIMARY KEY）或唯一约束（UNIQUE）
- 外键约束列允许NULL值，对应的行就成了孤行了
- 父表的主键才能做子表的外键
外键的作用
- 当对从表进行操作时，数据库会
向从表插入新记录时，如果外键值在主表中不存在，阻止插入。
修改从表的记录时，如果外键的值在主表中不存在，阻止修改。 - 当对主表进行修改操作时，数据库会
主表修改主键值时，旧值在从表里存在便阻止修改。 - 当对主表进行删除操作时，数据库会（三选一）
父表删除行时，其主键值在子表里存在便阻止删除。
父表删除行时，连带子表的相关行一起删除。
父表删除行时，把子表相关行的外键字段置为null。
语法
constraint 约束名 foreign key(外键列) references 主表(主键列)
constraint fk_emp_deptno foreign key(DEPTNO) references DEPT(DEPTNO)
5.检查约束(ck_)
检查约束可用来实施一些简单的规则，比如列值必须在某个范围内。检查的规则必须是一个结果为true 或false 的表达式。
语法
constraint 约束名 check(条件表达式)
constraint ck_emp_sal check(sal between 800 and 5000)
SEQUENCE- 序列
sequence 是oracle提供的用于产生一系列唯一数字的数据库对象。由于oracle中没有设置自增列的方 法，所以我们在oracle数据库中主要用序列来实现主键自增的功能。
语法
Create sequence 序列名
[ Start with 开始数字
Increment by 增量数
Maxvalue 最大数|nomaxvalue
MINVALUE 最小数|nominvalue
Cycle|nocycle --到达最大或最小时是否循环
Cache 缓存个数|nocache
Order|noorder --确保按照请求次序生成整数 ]
使用nextval 和currval值获取序列中的值，一条sql语句获取一次序列值，和nextval调用次数无关。
用于给表插数据时的自动生成序列号。
Insert into 表名 (主键，列名，列名) values(序列名.nextval,'','')
Insert into 表名 (主键，列名，列名) values(序列名.currval,'','')
示例
-- 先创建序列
CREATE SEQUENCE goods
START WITH 1 --从1开始
INCREMENT BY 1
MAXVALUE 100
MINVALUE 1;
-- 插入数据
INSERT INTO goodstype values(goods.nextval,'01','泰山',7,10,'03','');
INSERT INTO goodstype values(goods.nextval,'02','青岛啤酒',3,4,'04','');
通过上述我们发现使用序列有几个基本的约束条件,总结有以下几条
- 序列第一次必须先调用nextval获取一个序列值才能使用currval查看当前值
- 序列的起始值不能小于最小值
- 创建一个循环序列,则必须要设定最大值
- 如果创建带缓存的序列,缓存的值必须满足约束公式: 最大值-最小值>=(缓存值-1)*每次循环的值
INDEX- 索引 ★
在对包含很多行的表进行检索其中几行数据时，都应该创建索引，以加快检索速度。
索引概述
- 索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。
- 索引是建立在表上的可选对象，索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率。
- 索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表。
- 索引一旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时），oracle会自动管理索引，索引删除，不会对表产生影响。
- 索引对用户是透明的，无论表上是否有索引，sql语句的用法不变。
- oracle创建主键时会自动在该列上创建索引。
优势
1、可以快速检索速度，减少I/O次数，加快检索速度；
2、根据索引分组和排序，可以加快分组和排序；
劣势
1、索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍；
2、索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；
3、当对表中的数据进行DDL操作时（例如增加、删除和修改），索引也要动态的维护，这样就降低了数据的维护速度，因为在修改数据表的同时还需要修改索引表。
创建索引
语法
create [unique|BITMAP] index 索引名称 on 表名（列名[，列名]） tablespace 表空间名;
create index i_sno on student('sno');
unique 用于指定是否强制要求索引列为唯一性数据，表空间可选择是否指定，不指定则用默认表空间。 出于性能考虑索引表空间和表的表空间要分开。
修改索引 语法
alter index 索引名 rename to 新索引名;
删除索引 语法
drop index 索引名； -- 删除索引
查询索引 语法
-- 索引的全部信息
select * from user_indexes;
-- 查询索引涉及到的列
select * from user_ind_columns u where u.index_name='IND1’;
索引类型
索引分为B树索引（也叫平衡树索引，即就是什么都不写，最常用）和位图索引（多用于数据仓库）。这两种索引在逻辑结构（存储）上完全不同。
B树索引
B树索引是oracle中最常用的索引。B树索引结构主要由三部分组成根节点、分支节点、叶子节点。 B树索引就是 一颗二叉树；叶子节点（双向链表）包含索引列和指向表中每个匹配行的rowid值。所有叶子节点具有相同的深度，因而不管查询条件怎样，查询速度基本相同。B树索引结构能够适应精确查询（=）、模糊查询（like）和比较查询（>）。
B树索引又可以分为唯一索引、组合索引、反向键索引、基于函数的索引。
唯一索引
唯一索引确保在定义索引的列中没有重复值，Oracle 自动在表的主键列上创建唯一索引，使用CREATE UNIQUE INDEX语句创建唯一索引。
语法
create unique index 索引名 on 表名(列名);
具体列值 索引相关列上的值必须唯一，但可以不限制NULL值。
组合索引
组合索引是在表的多个列上创建的索引，索引中列的顺序是任意的，如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列，则可以提高检索速度。
语法
create index 索引名 on 表名(sno，cno);
具体列值该表中的元组由两列共同确定一行，例如班级号 学号 唯一确定一个学生。
反向键索引
反向键索引反转索引列键值的每个字节，为了实现索引的均匀分配，避免b树不平衡。通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上。创建索引时使用reverse关键字。
语法
create index 索引名 on 表名(列名) reverse;
具体列值 适用于某列值前面相同，后几位不同的情况，例如
sno 1001 1002 1003 1004 1005 1006 1007
索引转化1001 2001 3001 4001 5001 6001 7001
基于函数索引
基于一个或多个列上的函数或表达式创建的索引，表达式中不能出现聚合函数，不能在LOB类型的列上创建，创建时必须具有 QUERY REWRITE 权限
语法
-- 1.打开限制
alter system set query_rewrite_enabled = true;
-- 2.创建索引
create index 索引名 on 表名(函数(列名));
select * from student where upper(sname) = 'TOM';
位图索引
创建位图索引时，oracle会扫描整张表，并为索引列的每个取值建立一个位图（位图中，对表中每一行使用一位（bit、0或者1）来标识该行是否包含该位图的索引列的取值。如果为1，表示对应的rowid所在的记录包含该位图索引列值），最后通过位图索引中的映射函数完成位到行的rowid的转换。
位图索引适合创建在低基数列上；位图索引不直接存储ROWID，而是存储字节位到ROWID的映射；节省空间占用；如果索引列被经常更新的话，不适合建立位图索引；总体来说，位图索引适合于数据仓库中，不适合OLTP中。
语法
create bitmap index 索引名 on 表名(列名);
具体列值 不适用于经常更新的列，适用于条目多但取值类别少的列，例如性别列。
VIEW – 视图
在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。
视图包含记录(行)和字段(列)，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。视图是一种虚表。视图建立在已有表的基础上，视图赖以建立的这些表成为基表。向视图提供数据内容的语句为SELECT语句，可以将视图理解为存储起来的SELECT语句 。
视图的作用
- 可以保护表中的其他数据。
- 视图能够简化用户的操作。
- 当频繁的操作一个查询语句时，将这个查询语句创建成视图，便于操作。
语法
CREATE VIEW 视图名 AS
SELECT 列名
FROM 表名
WHERE 查询条件;
视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用 SQL 语句来重建数据。
如果需要更新视图中的列或者其他信息，无需删除，使用 CREATE OR REPLACE VIEW 选项
CREATE OR REPLACE VIEW 视图名 AS
SELECT 列名
FROM 表名
WHERE 查询条件;
删除视图就比较简单，跟表差不多，使用 DROP 即可
drop view persons_beijing;
同义词
同义词它是给数据库中的对象起一个别名，可以和使用原对象一样去使用同义词 同义词一般是给原对象取一个简单易记的名字
语法
create synonym 同义词名 for 对象名;
示例
create synonym e for emp; --给emp表创建同义词e
11、拓展知识
MySQL和Oracle的区别
mysql和oracle的区别有**1、**Oracle数据库是一个对象关系数据库管理系统，要收费；MySQL是一个开源的关系数据库管理系统，是免费的；**2、**数据库安全性的区别；**3、**对象名称的区别；**4、**临时表处理方式上的区别等等。
1、本质的区别
Oracle数据库是一个对象关系数据库管理系统（ORDBMS）。它通常被称为Oracle RDBMS或简称为Oracle，是一个收费的数据库。
MySQL是一个开源的关系数据库管理系统（RDBMS）。它是世界上使用最多的RDBMS，作为服务器运行，提供对多个数据库的多用户访问。它是一个开源、免费的数据库。
2、数据库安全性
MySQL使用三个参数来验证用户，即用户名，密码和位置；
Oracle使用了许多安全功能，如用户名，密码，配置文件，本地身份验证，外部身份验证，高级安全增强功能等。
3、SQL语法的区别
Oracle的SQL语法与MySQL有很大不同。Oracle为称为PL / SQL的编程语言提供了更大的灵活性。Oracle的SQL * Plus工具提供了比MySQL更多的命令，用于生成报表输出和变量定义。
4、存储上的区别
与Oracle相比，MySQL没有表空间，角色管理，快照，同义词和包以及自动存储管理。
5、对象名称的区别
虽然某些模式对象名称在Oracle和MySQL中都不区分大小写，例如列，存储过程，索引等。
但在某些情况下，两个数据库之间的区分大小写是不同的。
Oracle对所有对象名称都不区分大小写；而某些MySQL对象名称（如数据库和表）区分大小写（取决于底层操作系统）。
6、运行程序和外部程序支持
Oracle数据库支持从数据库内部编写，编译和执行的几种编程语言。此外，为了传输数据，Oracle数据库使用XML。
MySQL不支持在系统内执行其他语言，也不支持XML。
7、MySQL和Oracle的字符类型比较
MySQL具有char和varchar，最大长度允许为65,535字节（char最多可以为255字节，varchar最多为65,535字节）。
而Oracle支持四种字符类型，即char，nchar，varchar2和nvarchar2; 所有四种字符类型都需要至少1个字节长。
char和nchar最大可以是2000个字节，varchar2和nvarchar2的最大限制是4000个字节。可能会在最新版本中进行扩展。
8、MySQL和Oracle的额外功能比较
MySQL数据库不支持其服务器上的任何功能，如Audit Vault。
Oracle支持其数据库服务器上的几个扩展和程序，例如Active Data Guard，Audit Vault，Partitioning和Data Mining等。
9、处理临时表方式的区别
在MySQL中，临时表是仅对当前用户会话可见的数据库对象，并且一旦会话结束，这些表将自动删除。
Oracle中临时表的定义与MySQL略有不同，因为临时表一旦创建就会存在，直到它们被显式删除，并且对具有适当权限的所有会话都可见。但是，临时表中的数据仅对将数据插入表中的用户会话可见，并且数据可能在事务或用户会话期间持续存在。
10、MySQL和Oracle中的备份类型
Oracle提供不同类型的备份工具，如冷备份，热备份，导出，导入，数据泵。Oracle提供了最流行的称为Recovery Manager（RMAN）的备份实用程序。使用RMAN，我们可以使用极少的命令或存储脚本自动化我们的备份调度和恢复数据库。
MySQL有mysqldump和mysqlhotcopy备份工具。在MySQL中没有像RMAN这样的实用程序。
11、Oracle和MySQL的数据库管理
在数据库管理部分，Oracle DBA比MySQL DBA更有收益。与MySQL相比，Oracle DBA有很多可用的范围。
12、数据库的认证
MySQL认证比Oracle认证更容易。
与Oracle（设置为使用数据库身份验证时）和大多数仅使用用户名和密码对用户进行身份验证的其他数据库不同，MySQL在对用户进行身份验证location时会使用其他参数。此location参数通常是主机名，IP地址或通配符。
使用此附加参数，MySQL可以进一步将用户对数据库的访问限制为域中的特定主机或主机。此外，这还允许根据进行连接的主机为用户强制实施不同的密码和权限集。因此，从abc登录的用户scott可能与从xyz登录的用户scott相同或不同。
delete，drop，truncate 的区别
都有删除表的作用，区别在于:
1、delete和truncate仅仅删除表数据，drop连表数据和表结构一起删除，打个比方。delete 是单杀，truncate 是团灭，drop是把电脑摔了。
2、delete是DML语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和drop是DDL语句，操作完马上生效，不能回滚。打个比方，delete 是发微信说分手,后悔还可以撤回,truncate和drop是直接扇耳光说滚，不能反悔。
3、执行的速度上，drop > truncate > delete。 打个比方，drop是神舟火箭，truncate 是和谐号动车，delete 是自行车。
二、面试题
- SQL优化 执行计划 exists与in
- 索引跟主键的区别，索引的优缺点
- 分区表种类
- 什么是切片
- 导入导出怎么编写的
- 表的五大约束
- 表连接有哪些连接，怎连接的
- 表连接 ，左连接会显示多少条数据
- sql 中去重怎么写，其他数据库没有row_id怎么写
- delete与truncated的区别
- 表关联，聚合函数，where和having的区别
- union 和union all 的区别
- 锁表，锁表（查询、杀锁）
- with as，临时表字符串函数，日期截取数仓分为哪几层？你做的内容在哪一层？
- where 和 having 的区别是什么
- 避免使用distinct，为什么，说下原理
- date型和时间戳型的区别是什么
- 查看上一周的数据
- 有哪几种常见的索引
- 什么是笛卡尔积
- 什么是递归
- 什么是拉链表
- 怎么做行列转换
- 主键、外键的看法
- 怎么看这个字段是否用了索引
- 分区表的概念，和电脑的磁盘区分概念一样么
- 临时表
- DML和DDL的区别
- wm_concat的功能，有替代方案吗
- 如何实现分页查询
更多推荐
Oracle SQL基础（附带例子,学不会你打我）
发布评论