Oracle数据库学习基础

1.Oracle简介
数据：data
数据库：database DB
数据库管理系统 database Management system DBMS
数据库系统 database system DBS

数据操作语言（DML）
数据库定义语言（DDL）
数据库控制语言（DCL）

Oracle7 oralce8
Oracle9i# 系列文章目录
提示：这里可以添加系列文章的所有文章的目录，目录需要自己手动添加
例如：第一章 Python 机器学习入门之pandas的使用

---

提示：写完文章后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

提示：这里可以添加本文要记录的大概内容：
例如：随着人工智能的不断发展，机器学习这门技术也越来越重要，很多人都开启了学习机器学习，本文就介绍了机器学习的基础内容。

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、pandas是什么？

示例：pandas 是基于NumPy 的一种工具，该工具是为了解决数据分析任务而创建的。

二、使用步骤

1.引入库

代码如下（示例）：

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import warnings
warnings.filterwarnings('ignore')
import  ssl
ssl._create_default_https_context = ssl._create_unverified_context

2.读入数据

代码如下（示例）：

data = pd.read_csv('https://labfile.oss.aliyuncs.com/courses/1283/adult.data.csv')
print(data.head())

该处使用的url网络请求的数据。

---

总结

提示：这里对文章进行总结：
例如：以上就是今天要讲的内容，本文仅仅简单介绍了pandas的使用，而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。

Oracle 10g Oracle 11g
Oracle12C

2.默认的用户
Sys 超级管理员 change_on_install
System 普通管理员 manager
Scott 测试用户 tiger
3.常用命令
Sqlplus 启动交互窗口
show user 查看当前登录的用户
切换用户：
Conn 用户名/密码 [as sysdba]
conn sys/change_on_install as sysdba
如果是sys用户，则必须以DBA的身份登录

查看当前用户下所有表：
select * from tab;
设置行的尺寸
set linesize 300;
设置每页显示条数
set pagesize 20;

查看表结构
desc 表名;
4.scott用户下表的简单介绍

员工表：

部门表：

工资等级表：

5.简单查询操作
1.查询表中所有数据
语法 select * from 表名
编写顺序：
Select 表示查询
From 从哪里查询
执行顺序：
①from 子句
② select 子句

• 表示通配符 ，通配所有的字段，开发中尽可能不要使用*

2. 查询部分字段
   语法： select 查询字段列表 from 表名

select empno,ename from emp;
2. 别名查询
语法： select 字段名 as 别名,字段名 as 别名 from 表名
select empno as 编号,ename as 姓名 from emp;
as 可以省了，直接使用空格隔开
select empno 编号,ename 姓名 from emp;
3.查询公司中所有雇员的职位信息
select distinct job from emp;
去重复查询，如果有多个字段，只有所有的字段都重复，才会去重复查询
4.数学运算符
+(加) -(减) *（乘） /(除)

select 2 + 3 from dual;
select (2+3)/2 from dual;
dual 对于除sys用户之外的用户是一个虚拟的表
5.查询出所有雇员的编号、雇员姓名、年基本工资和日基本工资（一月按30天计算）
select empno,ename, sal*12 ,round(sal / 30,2) from emp;

6.公司每个雇员在年底的时候可以领取5000元的年终奖，要求查询出员工的编号，姓名和增长后的年基本工资（不包括佣金）
select empno 编号,ename 姓名, sal*12 +5000 年薪 from emp;

7.公司每个月为雇员增加200元的补助金，此时要求查询出每个雇员的编号，姓名和基本年工资（包括奖金5000）
执行外部文件中的sql
@d:文件名.sql
select empno,ename,(sal+200)*12 +5000 from emp;

8.Oracle数据库中的常量
字符常量：字面意思 ‘ABC’ 使用单引号引起来
数字常量： 123 345
连接符 ||
select ‘编号：’||empno||’,姓名：’||ename||’,职位：’||job as 员工信息 from emp;

6.筛选查询（条件查询）
运算符 符号 说明
关系运算符 >、<、>=、<=、!=、<> 进行大小或相等的比较，其中不等于由两种：!=和<>
判断null is null、is not null 判断某一列的内容是否为null
逻辑运算符 and、or、not and表示多个条件必须同时满足，or表示只要一个条件满足即可，not表示条件取反，即真变假，假变真
范围查询 between最小值and 最大值 在一个制定范围中查找，查找结果为：“最小值<=内容<=最大值”
列表范围查询 in 通过in可以指定一个查询范围
模糊查询 like 可以对指定的字段进行模糊查询

语法结构：
Select 字段列表 from 表名 where 条件
执行顺序：
一：from 子句
二：where子句
三： select 子句
1.查询出所有基本工资小于等于2000的全部雇员信息
select * from emp where sal <=2000;

2.查询出SMITH的雇员信息
SELECT * FROM EMP WHERE ENAME = ‘SMITH’;
在Oracle数据库中，内容（表中的数据）严格区分大小写。
3.查询出所有业务员的（CLERK）的雇员信息
select * from emp where job = ‘CLERK’;

4.查询出所有不是业务（CLERK）的雇员信息
select * from emp where job != ‘CLERK’;
select * from emp where job <> ‘CLERK’;

5.查询出工资范围在1500~3000（包含1500和3000）元的全部雇员信息。
select * from emp where sal <=3000 and sal >=1500;

在数据库中不存在 1500 <= sal <= 3000
6.查询职位是销售人员(SALESMAN)，并且基本工资高于1200元的所有雇员的信息。
select * from emp where job = ‘SALESMAN’ and sal > 1200;

7.查询出10部门中的经理（MANAGER）或者20部门中的业务员(CLERK)的信息。
select * from emp where (deptno=10 and job=‘MANAGER’) or (deptno=20 and job=‘CLERK’);

8.查询不是业务且基本工资大于2000元的所有雇员信息
select * from emp where job <> ‘CLERK’ and sal >2000;

9.使用between…and操作符查询出工资在1500~3000（包含1500和3000）元的全部雇员信息。
语法：字段 between 小值 and 大值
包括小值和大值，值可以是任何的数据类型
select * from emp where sal between 1500 and 3000;

10.查询在1981年雇佣的全部雇员信息
elect * from emp where to_char(hiredate,‘yyyy’) =‘1981’;

11.查询所有领取佣金的雇员的完整信息
null :表示没有值，不能使用关系运算符
is null , is not null
注意和数字0 的区别：
0表示数学数字
select * from emp where comm is not null;
select * from emp where not comm is null;

12.查询所有不领取佣金的雇员的完整信息
select * from emp where comm is null;

13.列出所有不领取奖金，同时基本工资大于2000元的全部雇员信息。
select * from emp where comm is null and sal >2000;

14.查询不收取佣金或者收取佣金小于100元的雇员
select * from emp where comm is null or comm<100;

15.查询收取佣金的雇员的不同的工作。
select distinct job from emp where comm is not null;

16.查询出雇员编号是7369,7788,7566的雇员信息
select * from emp where empno=7369 or empno =7788 or empno=7566;
或
select * from emp where empno in(7369,7788,7566);

select * from emp where empno = any(7369,7788,7566);

=any ：等价于in

any : 比最小的要大就行
<any : 比最大的要小就行

all : 比最大的还要大就行
<all ：比最小的还要小

17.查询出雇员编号不是7369,7788,7566的雇员信息
select * from emp where empno not in(7369,7788,7566);
或
select * from emp where empno!=7369 and empno !=7788 and empno!=7566;

注意：in运算时如果值中出现了null值，null不会参与。
select * from emp where comm in(300,500,null);

not in中如果出现了null值，则永远不会查询到值（not In 不能出现null值）。
select * from emp where comm not in(300,500,null);

18.查询雇员姓名以S开头的全部信息
语法：
字段名 like ‘字符串’
两个通配符：
% ： 通配任意的 0 个 1个 或多个字符
_(下划线)： 通配任意的1 个字符

select * from emp where ename like ‘S%’;
注意： like后面跟的是字符串
19.查询出姓名的第二个字母是M的全部雇员信息
select * from emp where ename like ‘_M%’;

20.查询出姓名中含有字母F的雇员信息
select * from emp where ename like ‘%F%’;

21.查询基本工资中包含1或在81年雇佣的全部雇员信息

22.找出部门10中所有的经理（MANAGER），部门20中所有的业务员（CLERK）,既不是经理也不是业务员的但是薪金大于2000元的所有的雇员的详细信息，并且要求这些雇员的姓名的名字中含有字母S或字母K。
分析：
条件：
① deptno = 10 and job = ‘MANAGER’
② Deptno =20 and job =’CLERK’
③ Job not in(‘MANAGER’,’ CLERK’) and sal >2000
④ Ename like ‘%S%’ or ename like ‘%K%’

（① Or ② or③） and ④

select * from emp where ((deptno=10 and job=‘MANAGER’)or(deptno=20 and job=‘CLERK’)or(job not in(‘MANAGER’,‘CLERK’))) and (ename like ‘%S%’ or ename like ‘%K%’);

7.排序查询
语法：
select 字段列表
from 表名
where 条件
order by 字段名 desc/asc;
desc【cend】 倒序
asc 正序
执行顺序：
(一)from 子句
(二)where子句
(三)select 子句
(四)order by 子句

1.查询雇员的完整信息，并按照基本工资由高到低进行 排序
select * from emp order by sal desc;

2.查询雇员的完整信息，并按照基本工资由低到高进行 排序
select * from emp order by sal asc;
Asc可以省略

3.查询所有业务员（CLERK）的详细资料，并按照基本工资由低到高排序
select * from emp where job =‘CLERK’ order by sal asc;

4.查询所有雇员的信息，按照基本工资由高到低排序，如果工资相等则按照雇佣日期有早到晚进行排序
select *
from emp
order by sal desc,hiredate asc;

如果有多个字段参与排序，每个排序的字段使用逗号隔开，并且按照字段编写顺序依次排序
8.函数
数据库中的函数：自定义函数和预定义函数（自带的函数）
1.字符函数：接收数据返回具体的字符信息
2.数值函数：对数字进行处理
3.日期函数：直接对日期进行相关的操作
4.转换函数:日期、字符、数字之间可以完成相互转换功能
5.通用函数：Oracle自己提供的有特色的函数

8.1.字符函数
函数名称 说明
upper(列|字符串) 将字符串的内容全部大写
lower(列|字符串) 将字符串的内容全部小写
initcap(列|字符串) 将字符串的开头首字母大写
replace(列|字符串,旧的字符串,新的字符串) 使用新的字符串替换旧的字符串
length(列|字符串) 求出字符串的长度
substr(列|字符串，,开始点[,长度]) 字符串截取
ascII(字符) 返回与指定字符对应的十进制数字
chr(数字) 给出一个整数，并返回与之对应的字符
rpad(列|字符串，长度,填充字符)
lpad(列|字符串，长度,填充字符)
在右或左填充指定长度字符串
ltrim(字符串),rtrim(字符串) 去掉左或右空格
trim（列|字符串） 去掉左右空格
instr(列|字符串，要查找的字符串，开始位置，第几次的出现位置) 查找一个子字符串是否存在指定的位置上出现
怎么学习？
1.记住函数名
2.参数个数，类型…
3.函数的作用
A.Upper(列|字符串)：将所有英文字符大写
select upper(‘admin’) from dual;
select ename,upper(ename) from emp;

A.Lower(列|字符串)：将所有英文字符小写
select lower(‘AdMin’) from dual;
select ename,lower(ename) from emp;

B.initcap(列|字符串)：对单词首字母大写，其他的全部小写
select initcap(‘admin’) from dual;
select ename,initcap(ename) from emp;

C.replace(列|字符串,旧的字符串,新的字符串)：替换
select replace(‘admin’,‘m’,’’) from dual;
select ename,replace(ename,‘A’,’’) from emp;

D.length(列|字符串)：获取字符串的长度
select length(‘admin’),length(‘中国’) from dual;
select ename,length(ename) from emp;

E.substr(列|字符串，,开始点[,长度])：截取字符串
–有两个参数，第二个参数表示开始截取的位置,表示截取到最后
select substr(‘ABCDEFGHIJK’,3) from dual;
–可以是负数，表示最后多少位
select substr(‘ABCDEFGHIJK’,-3) from dual;
–有三个参数，第二个参数表示开始截取的位置，第三个位置表示截取的长度
select substr(‘ABCDEFGHIJK’,3,5) from dual;
–如果有三个参数，第二个参数是负数
select substr(‘ABCDEFGHIJK’,-3,5) from dual;

F.ascII(字符)
select ascii(‘a’) from dual;

G.chr(数字)
获取数字对应的符号
select chr(97) from dual;

H.rpad(列|字符串，长度,填充字符)lpad(列|字符串，长度,填充字符)
select lpad(‘admin’,10,’’),rpad(‘admin’,10,’’) from dual;
select ename,lpad(ename,10,’-’),rpad(ename,10,‘A’) from emp;

I.ltrim(字符串),rtrim(字符串)，trim(字符串)：去除左边空格，去除右边空格，去除左右两边的空格
select ’ admin ‘,trim(’ admin ‘),ltrim(’ admin ‘),rtrim(’ admin ') from dual;

J.instr(列|字符串，要查找的字符串，开始位置，第几次的出现位置)
–查询AA第一次出现的位置:第二个参数表示要查询的字符串
select ‘abcdAAasdfasAAdfdadsfasfAAdfsf’,instr(‘abcdAAasdfasAAdfdadsfasfAAdfsf’,‘AA’) from dual;
–查询AA第一次出现的位置:第二个参数表示要查询的字符串，第三个参数表示的是开始查询的位置
select ‘abcdAAasdfasAAdfdadsfasfAAdfsf’,instr(‘abcdAAasdfasAAdfdadsfasfAAdfsf’,‘AA’,7) from dual;
–查询AA第一次出现的位置:第二个参数表示要查询的字符串，第三个参数表示的是开始查询的位置,第四个参数表示的是第几次出现的位置
select ‘abcdAAasdfasAAdfdadsfasfAAdfsf’,instr(‘abcdAAasdfasAAdfdadsfasfAAdfsf’,‘AA’,7,2) from dual;

8.2.数学函数
1.Round四舍五入
–round四舍五入(小数第一位)
select round(123456.789) from dual;
—第二个参数如果是正数表示保留的小数位
select round(12345.678,2) from dual;
—第二个参数如果是负数表示从小数点向前推
select round(12345.678,-2) from dual;

2.Trunc
–round截取数字（直接省略小数）
select trunc(123456.789) from dual;
—第二个参数如果是正数表示截取的小数位
select trunc(12345.678,2) from dual;
—第二个参数如果是负数表示从小数点向前推
select trunc(12345.678,-2) from dual;

3.Mod
–mod : 取余数（取模）

select mod(3,2) from dual;
select mod(101,3) from dual;

8.3.日期函数
获取当前系统时间：

–获取当前系统时间，精确到秒
select sysdate from dual;
–获取时间戳，精确到毫秒 1秒 = 1000 毫秒
select systimestamp from dual;

日期公式：
日期 + 天数 = 日期 ：获取某个日期几天之后的日期
日期 - 天数 = 日期 ：获取某个日期前几天的日期
日期 - 日期 = 天数 ： 获取两个日期之间的天数
实例：

–查询距离今天位置3天之后及3天之前的日期
select sysdate - 3 as 大前天 , sysdate + 3 大后天 from dual;

–查询出每个雇员到今天位置的雇佣天数，以及十天之前每个雇员的雇佣天数

select sysdate - hiredate , sysdate -10 -hiredate from emp;

select round(sysdate - hiredate,2) as 天数 , trunc(sysdate -10 -hiredate) as 十天之前 from emp;

日期函数：
函数名称 说明
add_months(日期,数字) 在指定的日期上加入指定的月数，求出新的日期
months_between(日期1,日期2) 求出两个日期间的雇佣月数
next_day(日期，星期数) 求出下一个星期X的具体日期
last_day(日期) 求出指定日期的最后一天日期
extract(格式from数据) 日期时间分割，或计算给定两个日期的间隔

A．求出所有雇员被雇佣的三个月之后的日期
Select add_months(hiredate,3) from emp;
B．查询出每个雇员的编号、姓名、雇佣日期、雇佣的月数及年份。
select empno,ename,hiredate,trunc(months_between(sysdate,hiredate),2) 月数, trunc(months_between(sysdate,hiredate)/12,2) 年数
from emp

C．Next_day(日期,星期数)：获取指定日期之后指定的星期X在那一个日期
select next_day(sysdate,‘星期四’) from dual;

D．last_day(日期):获取指定日期所在月的最后一天的日期
select last_day(sysdate) from dual;

select extract(year from sysdate)|| ‘年’|| extract(month from sysdate) || '月’from dual;
select extract(month from sysdate) from dual;

查询所有员工是在雇佣日期所在月的倒数第三天被公司雇佣的完整雇员信息。
select * from emp where hiredate = last_day(hiredate)-2;

8.4.转换函数
函数名称 说明
to_char(日期|数字|列,转换格式) 将指定的数据按照指定的格式变为字符串型
to_date(字符串|列,转换格式) 将指定的字符串按照指定的格式变为date型
to_number(字符串|列) 将指定的数据类型变为数字型

to_char(日期,转换格式):
NO 转化格式 说明
1 YYYY 完整的年份数字表示年有四位
2 Y,YYY 带逗号的年
3 YYY 年的后三位
4 YY 年的后两位
5 Y 年的最后一位
6 YEAR 年份的文字表示，直接表示四位的年
7 MONTH 月份的文字表示，直接表示两位的月
8 MM 用两位数字来表示月份，月有两位
9 DAY 天数的文字表示
10 DDD 表示一年里的天数（001~366）
11 DD 表示一月里的天数（01~31）
12 D 表示一周里的天数（1~7）
13 DY 用文字表示星期几
14 WW 表示一年里的周数
15 W 表示一月里的周数
16 HH 表示12小时制，
17 HH24 表示24小时制
18 MI 表示分钟
19 SS 表示秒
20 SSSSS 午夜之后的秒数字表示（0~86399）
21 AM|PM(A.M|P.M) 表示上午或下午
22 FM 去掉查询后的前导0,该标记用于时间模板的后缀

实例：
–获取年数后四位
select sysdate,to_char(sysdate,‘yyyy’) from dual;
–获取年数后三位
select sysdate,to_char(sysdate,‘yyy’) from dual;
–获取年数后两位
select sysdate,to_char(sysdate,‘yy’) from dual;
–获取年数后一位
select sysdate,to_char(sysdate,‘y’) from dual;

select sysdate,to_char(sysdate,‘y,yyy’) from dual;
–英文的年份
select sysdate,to_char(sysdate,‘year’) from dual;
–英文的月份
select sysdate,to_char(sysdate,‘month’) from dual;
–数字的月份
select sysdate,to_char(sysdate,‘mm’) from dual;
–获取周几
select sysdate,to_char(sysdate,‘day’) from dual;
–获取一年中的第几天
select to_char(sysdate,‘ddd’) from dual;
–获取一月中的第几天
select to_char(sysdate,‘dd’) from dual;
–获取一周中的第几天
select to_char(sysdate,‘d’) from dual;
–获取周几
select to_char(sysdate,‘dy’) from dual;
–一年中的第几周
select to_char(sysdate,‘ww’) from dual;
–一月中的第几周
select to_char(sysdate,‘w’) from dual;
–小时
select to_char(sysdate,‘hh’) from dual;
–24小时制
select to_char(sysdate,‘hh24’) from dual;
select to_char(sysdate,‘MI’) from dual;
select to_char(sysdate,‘ss’) from dual;

–注意格式符不区分大小写
select to_char(sysdate,‘YYYY-mm-dd hh:mi:ss’) from dual;

To_char(日期,格式符)：格式化日期的
To_char(数字,格式符)：格式化数字的
转换格式 说明
9 表示一位数字
0 显示前导0
$ 将货币的符号显示为美元符号
L 格局语言环境不同，自动选择货币符号
. 显示小数点
, 显示千位符

select to_char(123456.789,’$999,999.999’) from dual;
select to_char(123456.789,‘L999,999,999.999’) from dual;

select to_char(123456.789,‘L000,000,000.000’) from dual;

8.5.通用函数
函数名称 说明
NVL(数字|列,默认值) 如果显示的数字是null的话，则使用默认的值表示
NVL2(数字|列,返回结果1(不为空显示),返回结果2（为空显示）) 判断指定的列是否为null,如果不为null则返回结果1如果为空则返回结果2
nullif(表达式1,表达式2) 比较表达式1和表达式2的结果是否相等，如果相等返回null，如果不等返回表达式1
decode（列|值，判断值1，显示结果1，判断值2，显示结果2，…,默认值） 多值的判断，如果一个列（或某一个值）与判断值相同，则使用指定的结果显示结果输出，如果没有满足条件，则显示默认的值
case 列|数值 when 表达式1 then 显示结果1…else 表达式n…end 用于实现多条件的判断，在when之后编写条件，而在then之后编写条件满足的显示操作，如果都不满足则使用else中的表达式处理
coalesce(表达式1，表达式2，表达式n,…) 表达式逐个判断如果表达式1的内容时null，则显示表达式2，如果表达式2的内容时null，则显示表达式3，依次类推，如果表示式n的结果还是null，则返回null

一、NVL(数字|列,默认值)： 如果列的值是null，则使用默认值，不为null则是用该列的原值
–nvl(数字\列，默认值)\；如果列的返回值是null，则使用默认值
select nvl(null,2),nvl(3,2) from dual;
–统计每个员工一年的收入（包括薪水和奖金）
–因为comm存在null值，没法做计算
select ename,(sal + comm) *12 from emp;
select ename ,(sal +nvl(comm,0))*12 from emp;

二、Nvl2(列，结果1，结果2)：如果列不为null，则返回结果1，如果列为null值，则返回结果2
select nvl2(2,‘结果1’,‘结果2’) from dual;
select ename, (sal + nvl2(comm,comm,0))*12 from emp;

三、nullif(表达式1,表达式2)：如果表达式1和表达式2结果一样返回null，如果不一样返回表达式1、
select nullif(2,2),nullif(3,4) from dual;

四、decode（列|值，判断值1，显示结果1，判断值2，显示结果2，…,默认值）
–拿第一个值和偶数位置的值比对，比对成功，返回对应比对值后面的值，都没有匹配成功返回默认值
select decode(7,1,‘A’,2,‘B’,3,‘C’,4,‘D’,‘默认值’) from dual;

五、case 列|数值 when 表达式1 then 显示结果1…else 表达式n…end
select
case 1
when 1 then ‘A’
when 2 then ‘B’
when 3 then ‘C’
when 4 then ‘D’
else
‘没有匹配成功’
end
from dual;

六、coalesce(表达式1，表达式2，表达式n,…)
select ename,(sal + coalesce(comm,0))*12 from emp;
select coalesce(null,null,3,4,5,6,7) from dual;
select coalesce(‘a’,‘b’,‘c’,‘d’) from dual;

9.统计函数（聚合函数，分组函数）
Max(字段):最大值
Min(字段)：最小值
Avg(字段)：平均值
Sum(字段)：求和
–获取公司中工资的最大值，最小值，平均值，工资总和
select max(sal),min(sal),avg(sal),sum(sal) from emp;

Count(字段)：个数
– 获取员工人数
select count(empno),count(*) from emp;

注意：max,min,avg,sum如果没有统计到数据返回null
Count 如果统计不到数据返回0
select count(*) from emp where deptno=40;

select max(sal) from emp where deptno=40;

10.多表查询
笛卡尔积：查询时，多张表中数据的乘积
等值链接：所谓的等值连接操作就是表关系的笛卡尔积中选择所匹配字段值相等（=符号）的数据记录。
select * from emp ,dept where emp.deptno = dept.deptno;

不等值链接：所谓的不等值连接操作就是从表关系的笛卡尔积中选择出没有匹配字段值（!=符号）的数据记录
select * from emp ,dept where emp.deptno != dept.deptno;

自然链接：只会将有关联关系的数据查询出来，如图查询使用 * 则 查询出的关联关系的字段会合并
select * from emp natural join dept;

外链接：（on后面只能跟两张表的关联关系，筛选条件where都写在最后面）
左外链接：将left左边的表作为主要查询对象，无论是否有关系都要查询出来。
内连接的特点:只有满足条件的数据才会被显示。

在左外连接和右外连接时都会以一张表为基表，该表的内容会全部显示，然后加上两张表匹配的内容。 如果基表的数据在另一张表没有记录。 那么在相关联的结果集行中列显示为空值（NULL）

对于外连接， 也可以使用“(+) ”来表示。 关于使用（+）的一些注意事项：
1.（+）操作符只能出现在where子句中，并且不能与outer join语法同时使用。
2. 当使用（+）操作符执行外连接时，如果在where子句中包含有多个条 件，则必须在所有条件中都包含（+）操作符
3.（+）操作符只适用于列，而不能用在表达式上。
4.（+）操作符不能与or和in操作符一起使用。
5.（+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外 连接。
用（+）来实现， 这个+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。所以加号写在右表，左表就是全部显示，故是左连接。
语法：
Select 字段列表 from 左表 left outer join 右表 on 关联的关系
select * from emp e left outer join dept d on e.deptno = d.deptno;

右外连接：
语法：
Select 字段列表 from 左表 right outer join 右表 on 关联的关系
全外连接：
select * from emp e full outer join dept d on d.deptno=e.deptno;

外链接中的outer关键字可以省略

内连接：
select * from emp e inner join dept d on e.deptno = d.deptno;
注意 ： inner可以省略

交叉链接：
select * from emp e cross join dept d where e.deptno = d.deptno;

实例：
A.查询每个雇员的编号、姓名、职位、基本工资、部门名称、部门位置信息
/**
分析：
1.确定数据源
员工表 emp e : e.empno,e.ename,e.job,e.sal
部门表 dept d: d.dname,d.loc
2.确定关联关系（消除笛卡尔积）
e.deptno = d.deptno
3.条件
4.分组
5.排序

**/
–等值链接
select e.empno,e.ename,e.job,e.sal,d.dname,d.loc
from emp e,dept d
where e.deptno = d.deptno
–内连接
select e.empno,e.ename,e.job,e.sal,d.dname,d.loc
from emp e inner join dept d
on e.deptno = d.deptno
–左外链接
select e.empno,e.ename,e.job,e.sal,d.dname,d.loc
from emp e left outer join dept d
on e.deptno = d.deptno

B.通过别名查询每个雇员的编号、姓名、职位、基本工资、部门名称、部门位置信息
/***
分析：
1.确定数据源
员工表 emp e : e.empno 编号,e.ename 姓名,e.job 职位,e.sal 薪水
部门表 dept d : d.dname 部门名称,d.loc 部门位置
2.确定关系关系
e.deptno = d.deptno

***/

–内连接
select e.empno 编号,e.ename 姓名,e.job 职位,e.sal 薪水, d.dname 部门名称,d.loc 部门位置
from emp e inner join dept d
on e.deptno = d.deptno

–等值链接

select e.empno 编号,e.ename 姓名,e.job 职位,e.sal 薪水,d.dname 部门名称,d.loc 部门位置
from emp e ,dept d
where e.deptno = d.deptno

–左外链接
select e.empno 编号,e.ename 姓名,e.job 职位,e.sal 薪水,d.dname 部门名称,d.loc 部门位置
from emp e left join dept d
on e.deptno = d.deptno
–可以使用（+）的方式简化左外链接和右外连接（仅限于Oracle数据库）
select e.empno 编号,e.ename 姓名,e.job 职位,e.sal 薪水,d.dname 部门名称,d.loc 部门位置
from emp e , dept d
where e.deptno = d.deptno(+)
–右外连接
select e.empno 编号,e.ename 姓名,e.job 职位,e.sal 薪水,d.dname 部门名称,d.loc 部门位置
from emp e right join dept d
on e.deptno = d.deptno

–全外连接
select e.empno 编号,e.ename 姓名,e.job 职位,e.sal 薪水,d.dname 部门名称,d.loc 部门位置
from emp e full join dept d
on e.deptno = d.deptno

C.
–通过别名查询每个雇员的编号、姓名、职位、基本工资、部门名称、部门位置信息。
–将上例中的工资等级分别显示为E、D、C、B、A四个等级：

/***
分析：
1.确定数据源
员工表 emp e : e.empno 编号 ,e.ename 姓名, e.job 职位 ,e.sal 基本工资
部门表 dept d : d.dname 部门名称 , d.loc 部门位置
等级表 salgrade s : s.grade ,
case s.grade
when 1 then ‘E级’
when 2 then ‘D级’
when 3 then ‘C级’
when 4 then ‘B级’
when 5 then ‘A级’
end ,
decode(s.grade,1,‘E级’,2,‘D级’,3,‘C级’,4,‘B级’,5,‘A级’)
2.确定关联关系
员工表与部门表 ： e.deptno = d.deptno
员工表与等级表： e.sal between s.losal and s.hisal

***/
–等值链接
select e.empno 编号 ,e.ename 姓名, e.job 职位 ,e.sal 基本工资,
d.dname 部门名称 , d.loc 部门位置,
s.grade ,
case s.grade
when 1 then ‘E级’
when 2 then ‘D级’
when 3 then ‘C级’
when 4 then ‘B级’
when 5 then ‘A级’
end 等级1,
decode(s.grade,1,‘E级’,2,‘D级’,3,‘C级’,4,‘B级’,5,‘A级’) 等级2
from emp e,dept d,salgrade s
where
e.deptno = d.deptno
and
e.sal between s.losal and s.hisal ;
--------左外链接----------
select
e.empno 编号 ,e.ename 姓名, e.job 职位 ,e.sal 基本工资,
d.dname 部门名称 , d.loc 部门位置,
s.grade ,
case s.grade
when 1 then ‘E级’
when 2 then ‘D级’
when 3 then ‘C级’
when 4 then ‘B级’
when 5 then ‘A级’
end 等级1,
decode(s.grade,1,‘E级’,2,‘D级’,3,‘C级’,4,‘B级’,5,‘A级’) 等级2
from emp e left join dept d on e.deptno = d.deptno left outer join salgrade s
on e.sal between s.losal and s.hisal;

D.查询出每个雇员的编号、姓名、上级领导的编号、姓名。
自连接查询：自己和自己进行关系
/**
分析：
1. 确定数据源
员工表 emp e ： e.empno, e.ename
领导表 emp m : m.empno, m.ename
2.确定关联关系
e.mgr = m.empno
.
**/
–等值链接
select e.empno, e.ename,m.empno, m.ename
from emp e,emp m
where e.mgr = m.empno
–外链接

select e.empno, e.ename,m.empno, m.ename
from emp e left outer join emp m
on e.mgr = m.empno

E.查询在1981年雇用的全部雇员的编号、姓名、雇用日期（按年-月-日显示）、工作、领导姓名、雇员月工资、雇员年工资（基本工资+奖金）、雇员的工资等级、部门编号、部门名称、并要求这些雇员的工资在1500~3500之间，将最后的结果按照年工资降序排列、如果年工资相等，则按照工作时间进行排序
/***
1.确定数据源
员工表 emp e : e.empno,e.ename,to_char(e.hiredate,‘yyyy-mm-dd’) , e.job,e.sal, (e.sal + nvl(e.comm,0))*12 yearsal
领导表 emp m : m.ename
等级表 salgrade s : s.grade
部门表 dept d : d.deptno,d.dname
2.确定关联关系
员工表与领导表 e.mgr = m.empno
员工表与等级表 e.sal between s.losal and s.hisal
员工表与部门表 e.deptno = d.deptno
领导表与等级表 m.sal between s.losal and s.hisal(此处不需要)
领导表 与部门表 m.deptno = d.deptno (此处不需要)
3.确定条件
to_char(e.hiredate,‘yyyy’) =‘1981’
e.sal between 1500 and 3500
4.排序
order by yearsal desc,e.hiredate asc

***/
select e.empno,e.ename,to_char(e.hiredate,‘yyyy-mm-dd’) , e.job,e.sal, (e.sal + nvl(e.comm,0))*12 yearsal,
m.ename,
s.grade,
d.deptno,d.dname
from emp e,emp m,salgrade s,dept d
where
e.mgr = m.empno
and
e.sal between s.losal and s.hisal
and
e.deptno = d.deptno
and
to_char(e.hiredate,‘yyyy’) =‘1981’
and
e.sal between 1500 and 3500
order by yearsal desc,e.hiredate asc;

select
e.empno,e.ename,to_char(e.hiredate,‘yyyy-mm-dd’) , e.job,e.sal, (e.sal + nvl(e.comm,0))*12 yearsal,
m.ename,
s.grade,
d.deptno,d.dname
from emp e inner join emp m on e.mgr = m.empno inner join salgrade s on e.sal between s.losal and s.hisal
inner join dept d on e.deptno = d.deptno
where
to_char(e.hiredate,‘yyyy’) =‘1981’
and
e.sal between 1500 and 3500
order by yearsal desc,e.hiredate asc;

11.集合查询
A.并集
Union 将查询结果合并，会将重复的数据去除
Union all将查询结果合并，不会将重复的数据去除
–union (并集)
select * from emp where deptno=20 or deptno = 30
union
select * from emp where deptno =10 or deptno =20

–unoin all (并集)
select * from emp where deptno=20 or deptno = 30
union all
select * from emp where deptno =10 or deptno =20

B.差集

select * from emp where deptno =10 or deptno =20
minus
select * from emp where deptno=20 or deptno = 30

注意：获取10部门，不会获取30部门，跟顺序有关
C.交集
select * from emp where deptno =10 or deptno =20
intersect
select * from emp where deptno=20 or deptno = 30

12.分组查询
语法结构：
Select 字段列表 from 表名 where 条件
Group by 字段名 having 分组之后的条件
Order by 字段 asc/desc
执行顺序：
1.from 子句
2.where 子句
3.group by 子句
4.select 子句
5.order by 子句
实例：
统计出每种职位的最高工资和最低工资
select job, max(sal),min(sal) from emp group by job;


–ORA-00979: 不是 GROUP BY 表达式
select d.dname,count(*),avg(sal),avg(months_between(sysdate,hiredate)/12)
from dept d,emp e
where d.deptno = e.deptno
group by d.deptno

–group by 子句中没有出现的字段不能直接出现在select 子句中
select d.dname,count(*),avg(sal),avg(months_between(sysdate,hiredate)/12)
from dept d,emp e
where d.deptno = e.deptno
group by d.deptno,d.dname

–ORA-00934: 此处不允许使用分组函数:分组之后的条件需要在having条件编写
—查询每个部门的名称、部门人数、部门的平均工资、平均服务年限（部门的） 且部门人数大于4
select d.dname,count(),avg(sal),avg(months_between(sysdate,hiredate)/12)
from dept d,emp e
where d.deptno = e.deptno
group by d.deptno,d.dname
having count()>4

–查询公司各个工资等级雇员的数量、平均工资
/**
1.
emp e ： count(*),avg(sal)
salgrade s : s.grade
2.
e.sal between s.losal and s.hisal
3.
group by s.grade

**/

select count(*),round(avg(sal),2),s.grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal
group by s.grade

–列出至少有一个员工的所有部门编号、名称、并统计出这些部门的平均工资、最低工资、最高工资
/**
分析：
1.确定数据源
部门表 dept d : d.deptno,d.dname
员工表 emp e : avg(e.sal),min(e.sal),max(e.sal)
2.确定关联关系
d.deptno = e.deptno
3.条件
4.分组
group by d.deptno

 count(*) > 1

5.排序
**/
select d.deptno,d.dname,avg(e.sal),min(e.sal),max(e.sal)
from dept d ,emp e
where d.deptno = e.deptno
group by d.deptno,d.dname
having count(*) > 4

–列出至少有一个员工的所有部门编号、名称、并统计出这些部门的平均工资、最低工资、最高工资,以及该部门中员工姓名
select d.deptno,d.dname,wm_concat(e.ename),avg(e.sal),min(e.sal),max(e.sal)
from dept d ,emp e
where d.deptno = e.deptno
group by d.deptno,d.dname
having count(*) > 1

13.子查询
子查询定义：
在一个查询语句的每一个子句中嵌入一个完整查询语句
查询公司中工资最低的雇员的完整信息
–1.查询工资最低是多少 800
select min(sal) from emp
–2.查工资是800员工信息
select * from emp where sal =800

select * from emp where sal =(select min(sal) from emp )

–查询出与ＡＬＬＥＮ从事同一工作，并且基本工资高于雇员编号为7521的全部雇员信息
–1.allen的工作 :SALESMAN
select job from emp where ename=‘ALLEN’
–2.查询编号为7521的工资 1250
select sal from emp where empno = 7521

–3.查询工作为SALESMAN 薪水是 1250

select * from emp where job=‘SALESMAN’ and sal>1250

select * from emp where job=(select job from emp where ename=‘ALLEN’) and sal>(select sal from emp where empno = 7521)

–子查询返回的是一行多列的情况
–查询与SCOTT从事同一工作且工资相同的雇员信息

–查询工作
select job from emp where ename=‘SCOTT’
–查询工资
select sal from emp where ename=‘SCOTT’

select * from emp where job=(select job from emp where ename=‘SCOTT’) and sal=(select sal from emp where ename=‘SCOTT’)

select * from emp where (job,sal)=(select job,sal from emp where ename=‘SCOTT’)

–一列多行的情况
–查询出与每个部门中最低工资相同的全部雇员信息
–1查看每个部门最低工资是多少
select min(sal) from emp group by deptno
–2.查询工资是 950 800 1300的工资
select * from emp where sal=950 or sal = 800 or sal =1300

select * from emp where sal in(950, 800,1300)

select * from emp where sal in (select min(sal) from emp group by deptno)

select * from emp where sal =any (select min(sal) from emp group by deptno)

–不是950 800 1300
select * from emp where sal not in (select min(sal) from emp group by deptno)

select * from emp where sal >any (select min(sal) from emp group by deptno)

select * from emp where sal <any (select min(sal) from emp group by deptno)

select * from emp where sal >all (select min(sal) from emp group by deptno)

select * from emp where sal <all (select min(sal) from emp group by deptno)

14.数据类型
为什么划分数据类型？

Oracle数据库中常用的数据类型
类型 长度 说明
char(n) n=1~2000 保存定长的字符串
varchar2(n) n=1~4000 可以放数字、字母及ASCII码字符集，Oracle12C开始，其最大支持32767字节长度
number(m,n) m=1~38
n=-84~127 表示数字，其中小数部分长度为n,整数部分长度为m-n位。
date - 用于存放日期时间型数据（不包括毫秒）
timestamp - 用于存放日期时间型数据（包括毫秒）
clob 4G 用于存放海量的文字
blob 4G 用于保存二进制文件，例如图片、电影、音乐等

char(N) 表示不可变长度的字符串
varchar2(n):表示可变长度的字符串
number(m):表示整数，可以使用int（Integer）代替
number(m,n):表示的小数，全部长度是m,小数位是n,整数m-n
可以使用float代替
date ：时间类型，精确到秒
timestamp:时间类型，精确到毫秒
clob: 可以存放海量的字符
blob: 存放二进制的文件

15.表的相关操作(DDL操作)
表的创建：
语法：

create table 表名(
字段名 数据类型,
字段名 数据类型，
…
);
实例：
create table student(
sno number(4),
sname varchar2(10),
age number(3),
birthday date,
sex varchar(4),
note blob
);
注意：表字段之间使用英文逗号隔开，字符串类型必须指定长度

表的删除和闪回技术：
Drop table 表名 [purge]
–删除表操作:放入回收站
drop table student;
–闪回技术（回收站）
flashback table student to before drop

–彻底删除
drop table users purge;

–清空回收站
purge recyclebin;
修改表名称：
–修改表名
alter table student rename to stu;
其他操作
–添加新字段
alter table student add higth number(3,2);
–删除字段
alter table student drop column higth;
–修改列名称
alter table student rename column sex to gender ;

表名的定义规则：
1.可以是字母、数字、下划线(_),$,#等
2.数字不能开头
3.不能是Oracle数据库中的关键字
4.长度不能超过30个字符
5.不区分大小写
6.起名要有意义
16.表的复制操作
–语法 create table 表名 as 子查询
create table myemp as select * from emp;
–只复制表结构
create table myemp2 as select * from emp where 1=2;

17．表中数据的操作（CURD）
17.1 添加操作
语法：
Insert into 表名(field1,field2,filedN)values(value1,value2,valueN)
insert into myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values
(8888,‘张三’,‘CLERK’,7654,sysdate,6000,4000,10);

—简化(简化时，值的列表顺序必须和建表时字段的顺序一致，且给所有的字段填值)
insert into myemp
values
(2222,‘张二’,‘CLERK’,7654,sysdate,6000,4000,10);
–少值
insert into myemp
values
(2222,‘张二’,‘CLERK’,7654,sysdate,6000,4000);
–添加部分字段
insert into myemp(empno,ename,job)values(1111,‘admin’,‘CLERK’);
commit;
17.2.修改操作
语法：
Update 表名 set 字段1 = 值2,字段2=值2…. Where 条件
update myemp set job=‘SALESMAN’, sal=8000 where empno = 7369

注意修改时一定注意条件

17.3. 删除操作
语法：
Delete from 表名 where 条件
delete from myemp where ename =‘张二’;

注意删除时一定注意条件
–删除
delete from myemp where ename =‘张二’;
–清空表
delete from myemp;

17.4. 事务
事务的四个特征：
1.原子性：原子性是事务的最小的单元，是不可再分割的单元，相当于一个个小的数据库操作，这些操作必须同时完成，如果有一个失败了，则一切的操作将全部失败。
2.一致性：指的是在数据操作的前后是完全一致的，保证数据的有效性，如果事务正常操作则系统会维持有效性，如果事务出现了错误，则回到最原始状态，也要维持其有效性，这样保证事务开始时和结束时系统处于一致状态。
3.隔离型：多个事务可以同时进行且彼此之间无法访问，只有当事务完成最终操作的时候，才可以看到结果。
4.持久性:当一个系统崩溃时，一个事务依然可以坚持提交，当一个事务完成后，操作的结果保存在磁盘中，永远不会被回滚
18.表空间
表空间的创建需要管理员权限，可以使用sys用户登录,来创建
永久性表空间语法：
create tablespace 表空间名称
datafile 表空间管理的文件 size 默认表空间大小
autoextend on next 每次自增的大小
logging --是否记录日志
实例：
create tablespace myspace
datafile ‘C:\app\qyc\oradata\orcl\mydata.dbf’ size 10M
autoextend on next 2M
logging

如何用表空间：
create table student(
sno number(4),
name varchar2(20)
)tablespace myspace;–指定表空间

整个语法；
create [temporary] tablespace 表空间名称
[datafile|tempfile 表空间文件保存路径…][size 数字[K|M]]
[autoextend on|off][next 数字[K|M]]
[logging|nologging];
19.约束
1.非空约束
2.唯一约束
3.主键约束
4.检查约束
5.外键约束

19.1.非空约束
create table users(
id number(4),
username varchar2(20) not null,–非空约束
password varchar2(20),
sex varchar2(4)
);
–校验:ORA-01400: 无法将 NULL 插入 (“SCOTT”.“USERS”.“USERNAME”)
insert into users(id,password,sex)values(1,‘123456’,‘男’);

19.2.唯一约束
创建方式1：建表时指定

drop table users purge;

create table users(
id number(4),
username varchar2(20) unique,–设置唯一约束
password varchar2(20),
sex varchar2(4)
);
–重复执行：ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C0011094)
insert into users(id,username,password,sex)values(1,‘lisi@QQ.com’,‘123456’,‘男’);
commit;
第二种方式：建表时指定唯一约束，并制定约束的名称
drop table users purge;
create table users(
id number(4),
username varchar2(20),
password varchar2(20),
sex varchar2(4),
constraint UK_USERS_USERNAME unique (username)
);

–重复执行：ORA-00001: 违反唯一约束条件 (SCOTT.UK_USERS_USERNAME)
insert into users(id,username,password,sex)values(1,‘lisi@QQ.com’,‘123456’,‘男’);
commit;

第三种方式，建完表之后指定唯一约束
create table users(
id number(4),
username varchar2(20),
password varchar2(20),
sex varchar2(4)
);
–建表完毕之后指定唯一约束
alter table users add constraint uk_users_username unique (username);
–重复执行：ORA-00001: 违反唯一约束条件 (SCOTT.UK_USERS_USERNAME)
insert into users(id,username,password,sex)values(1,‘lisi@QQ.com’,‘123456’,‘男’);
commit;

19.3主键约束
主键：能够唯一表示一条数据，非空 + 唯一
自然主键：把跟业务相关的字段作为主键来使用，称之为自然主键
代理主键：把跟业务不相关的字段作为主键来使用，称之为代理主键

联合主键：将两个或两个以上的字段作为主键来使用，成为联合主键

创建主键方式1：

drop table users purge;

create table users(
id number(4) primary key,
username varchar2(20),
password varchar2(20)
);

–校验：ORA-01400: 无法将 NULL 插入 (“SCOTT”.“USERS”.“ID”)
insert into users(username,password)values(‘zhangsan’,‘123456’);
commit;
–重复执行：ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C0011099)
insert into users(id,username,password)values(1111,‘zhangsan’,‘123456’);
commit;
第二种方式：建表指定，且指定主键约束名
create table users(

id number(4),
username varchar2(20),
password varchar2(20),
constraint PK_USES_ID primary key (id)
);
–校验：ORA-01400: 无法将 NULL 插入 (“SCOTT”.“USERS”.“ID”)
insert into users(username,password)values(‘zhangsan’,‘123456’);
commit;
–重复执行：ORA-00001: 违反唯一约束条件 (SCOTT.PK_USES_ID)
insert into users(id,username,password)values(1111,‘zhangsan’,‘123456’);
commit;
第三种创建主键约束：
drop table users purge;
create table users(
id number(4),
username varchar2(20),
password varchar2(20)
);
–添加主键约束
alter table users add constraint pk_users_id primary key (id)

–校验：ORA-01400: 无法将 NULL 插入 (“SCOTT”.“USERS”.“ID”)
insert into users(username,password)values(‘zhangsan’,‘123456’);
commit;
–重复执行：ORA-00001: 违反唯一约束条件 (SCOTT.PK_USES_ID)
insert into users(id,username,password)values(1111,‘zhangsan’,‘123456’);
commit;

联合主键：
主键在表中有且仅有一个。
drop table users purge;
create table users(
id number(4),
username varchar2(20) ,
password varchar2(20),
constraint pk_users_id_username primary key(id,username)
);
–重复执行：ORA-00001: 违反唯一约束条件 (SCOTT.PK_USERS_ID_USERNAME)
insert into users(id,username,password)values(1111,‘zhangsan’,‘123456’);

insert into users(id,username,password)values(2222,‘李四’,‘123456’);

–删除约束（了解）
alter table users drop constraint PK_USERS_ID_USERNAME;
以后没有特别说明的情况下，一个表中必须有一个主键约束。

19.4.检查约束（mysql数据库不支持）

–第一种检查约束
drop table users purge;
create table users(
id number(4) primary key,
username varchar2(20) ,
password varchar2(20),
gender varchar2(4) check (gender in(‘男’,‘女’)),—检查约束
age number(3) check (age between 0 and 150) —检查约束
);
–校验：ORA-02290: 违反检查约束条件 (SCOTT.SYS_C0011104)
insert into users(id,username,password,gender,age)values(2222,‘李四’,‘123456’,‘男’,160);

–第三种检查约束
create table users(
id number(4) primary key,
username varchar2(20) ,
password varchar2(20),
gender varchar2(4) ,
age number(3),
constraint CK_USERS_GENDER check (gender =‘男’ or gender =‘女’),
constraint CK_USERS_AGE check (age >=0 and age <=150)
);

–校验：ORA-02290: 违反检查约束条件 (SCOTT.CK_USERS_GENDER)
insert into users(id,username,password,gender,age)values(2222,‘李四’,‘123456’,‘aa’,100);

第三种自己完成
19.5.外键约束
create table users(
id number(4) primary key,
username varchar2(20),
password varchar2(20),
sex varchar2(4)

);

create table idcode(
id number(4) primary key,
mz varchar2(4),
idno varchar2(18),
userid number(4),
constraint FK_IDCODE_USERID foreign key (userid) references users(id)–创建外键约束
);

insert into users(id,username,password,sex)values(1,‘张三’,‘123456’,‘男’);
insert into users(id,username,password,sex)values(2,‘李四’,‘123456’,‘女’);
insert into users(id,username,password,sex)values(3,‘王五’,‘123456’,‘女’);
insert into users(id,username,password,sex)values(4,‘赵六’,‘123456’,‘男’);
commit;

select * from users;
–ORA-02291: 违反完整约束条件 (SCOTT.FK_IDCODE_USERID) - 未找到父项关键字
insert into idcode(id,mz,idno,userid)values(1,‘汉族’,‘11111111X’,5);

insert into idcode(id,mz,idno,userid)values(1,‘汉族’,‘11111111X’,1);

指定外键约束语法:
constraint 约束名称 foreign key (子表中的外键列) references users(父表中字段)

create table users(
id number(4) primary key,
username varchar2(20),
password varchar2(20),
sex varchar2(4)

);

create table idcode(
id number(4) primary key,
mz varchar2(4),
idno varchar2(18),
userid number(4)
);
–建完表之后指定外键约束
alter table idcode add constraint FK_IDCODE_USERID foreign key(userid) references users(id)

insert into users(id,username,password,sex)values(1,‘张三’,‘123456’,‘男’);
insert into users(id,username,password,sex)values(2,‘李四’,‘123456’,‘女’);
insert into users(id,username,password,sex)values(3,‘王五’,‘123456’,‘女’);
insert into users(id,username,password,sex)values(4,‘赵六’,‘123456’,‘男’);
commit;
–ORA-02291: 违反完整约束条件 (SCOTT.FK_IDCODE_USERID) - 未找到父项关键字
insert into idcode(id,mz,idno,userid)values(1,‘汉族’,‘11111111X’,5);

insert into idcode(id,mz,idno,userid)values(1,‘汉族’,‘11111111X’,1);

注意：如果子表有对父表中数据的引用，则被引用的父表中的数据不能直接删除
–ORA-02292: 违反完整约束条件 (SCOTT.FK_IDCODE_USERID) - 已找到子记录
delete from users where id = 1;

–可以删除2
delete from users where id = 2;

可以先解除关联关系，然后再删除
alter table idcode drop constraint FK_IDCODE_USERID;

注意：如果有关联的两个表，删除操作时，会有外键约束，想删除则可以先删除子表然后再删除父表。或者解除关联关系，再删除父表。
—删除表
drop table users purge;
drop table idcode purge;

19.6.默认值设置（不属于约束）
create table users(
id number(4) primary key,
username varchar2(20),
password varchar2(20) default ‘12345’,–使用默认值
sex varchar2(4) default ‘男’–使用默认值

);

insert into users(id,username)values (1,‘admin’);
insert into users(id,username,sex,password)values (2,‘admin’,‘女的’,‘111111’);
20．视图
视图是从一个或多个实体表（或视图）导出的表。它与实体表不同，视图本身是一个不含任何真实数据的虚拟的表。数据库中只存放视图的定义，不存放视图对应的数据。这些数据仍存放在原来的实体表中。所以实体表中的数据发生变化，从视图中查询出的数据也能随之变化。

创建视图的语法：
create or replace view 视图名 as 子查询
–使用sys用户登录后，执行 grant create view to scott
create view myview20 as select * from myemp where deptno =20;
视图的优点有：
１.提供了另外一种级别的表安全性
２.隐藏的数据的复杂性
３.简化的用户的SQL命令
４.隔离基表结构的改变
５.通过重命名列，从另一个角度提供数据

删除视图：
drop view myview20;
21.序列

–创建一个序列
create sequence myseq;

–nextval属性：获取序列的下一个值
select myseq.nextval from dual;
–currval属性：获取序列的当前值
select myseq.currval from dual;

– Create sequence
create sequence my_seq
minvalue 500 – 最小值
maxvalue 1000 --最大值
start with 550 --开始值
increment by 3 --每次自增大小
cache 20 --缓存大小
cycle; --是否循环

–删除序列
drop sequence my_seq

注意：在获取currval之前需要执行nextval一次。
序列的使用：
–使用序列
insert into users(id,username,password) values(myseq.nextval,‘admin’,‘123’);
22.伪列
Rowid：和数据库中表数据的每一条数据绑定
Rownum:动态绑定，主要用来分页查询
—rowid

select e.*, rowid from emp e where rowid=‘AAAR3sAAEAAAACXAAA’;

select e., rowid from emp e where deptno=20;
–rownum :动态帮行号
select e.,rownum from emp e where deptno = 30

–查询第一条数据
select e.* ,rownum from emp e where rownum =1;
–查询第二条数据（查询不到数据）
select e.* ,rownum from emp e where rownum = 2;
–查询前10条数据
select e.* ,rownum from emp e where rownum <=10;
–查询6到10，显示第二页的数据
select * from (select e.* ,rownum rn from emp e where rownum <=10) temp where temp.rn between 6 and 10
–程序中写法：（假设每页显示5条）
select * from (select e.* ,rownum rn from emp e where rownum <=(每页显示条数*当前页数)) temp where temp.rn between ((当前页数-1)每页显示条数+1) and (每页显示条数当前页数)

select * from (select e.* ,rownum rn from emp e where rownum <=10) temp where temp.rn > 5
–程序中写法：（假设每页显示5条）
select * from (select e.* ,rownum rn from emp e where rownum <=(每页显示条数*当前页数)) temp where temp.rn > ((当前页数-1)*每页显示条数)

select * from (select e.* ,rownum rn from emp e where rownum <=(15)) temp where temp.rn > (10)

23.索引
B树索引：
唯一索引：Oracle数据库中，当设置某个字段为主键或唯一约束时，会自动创建唯一索引。可以手动创建：
Create unique index 索引名称 on 表名(表中的字段)

位图索引：位图索引适合创建在低基数列上
语法：
Create bitmap index索引名称 on 表名(表中的字段)
create table users(
id number(4) primary key,
username varchar2(20) unique,
password varchar2(20)
);
–手动创建唯一索引
create unique index UK_INDEX_USERS on users(password);

insert into users(id,username,password)values(2,‘bb’,‘22’);

–位图索引：用在列中数据重复率高的字段上

create table users(
id number(4) primary key,
username varchar2(20) unique,
password varchar2(20),
sex varchar2(4)
);
–手动创建位图索引
create bitmap index B_index_users on users(sex)

24.用户-权限-角色
创建用户：（必须是管理员身份）
create user QWY --创建用户
identified by 123456 —给用户指定密码
default tablespace USERS --指定表空间，默认为users
temporary tablespace TEMP --指定临时表空间，默认为TEMP
profile DEFAULT – 指定摘要文件
password expire; – 密码失效

—给用户赋予会话的权限
grant create session to qwy
一次性赋予用户多个权限：
–一次性赋予多个权限
grant create session,create table,create view,create sequence to qwy;

收回权限：
–收回权限
revoke create sequence,create table from qwy;

创建角色：（角色是权限的集合）
—创建角色（role）
create role myrole;

给角色赋予权限：
–给角色添加权限
grant create session,create table,create sequence to myrole

给用户赋予角色：
create user zhangsan identified by 111111;
–给用户赋予角色
grant myrole,myrole2 to zhangsan;

收回角色：
–收回角色
revoke myrole from zhangsan

赋予开发人员的福利：（两个角色）
–必须记住
grant resource,connect to zhangsan

–锁定用户
alter user qwy account lock;
–解锁用户
alter user qwy account unlock;

--1.grant给表赋权限：grant  select/update on  table to user；grant  权限 on 表  to 用户。2.grant 给存储过程赋权限：grant execute on package/function/procedure to user；grant execute on 过程、包、方法 to user