[Oracle基础]-Oracle数据库
...大约 56 分钟
[Oracle基础]-Oracle数据库
[TOC]
1.数据库的基本概念
【1】Oracle基础
Oracle简介
Oracle公司成立于1977年，总部位于美国加州，是世界领先的信息管理软件开发商，因其复杂的关系数据库产品而闻名。Oracle数据库产品为财富排行榜上的前1000家公司所采用，许多大型网站也选用了Oracle系统。
Oracle数据库是Oracle（中文名称叫甲骨文）公司的核心产品，Oracle数据库是一个适合于大中型企业的数据库管理系统。在所有的数据库管理系统中（比如：微软的SQL Server，IBM的DB2等），Oracle的主要用户涉及面非常广，包括：银行、电信、移动通信、航空、保险、金融、电子商务和跨国公司等。
Oracle公司成立以来，从最初的数据库版本到Oracle7、Oracle8i、Oracle9i，Oracle10g到Oracle11g，Oracle12c虽然每一个版本之间的操作都存在一定的差别，但是Oracle对数据的操作基本上都遵循SQL标准。因此对Oracle开发来说各版本之间的差别不大。
2008年1月16日，全球最大的数据库软件公司甲骨文（Oracle）宣布已经同BEA达成协议，以85亿美元收购BEA。
2008年1月16日，Sun宣布已经与MySQL AB达成协议，以大约10亿美元收购MySQL AB。
2009年04月20日，甲骨文宣布，该公司将以每股9.5美元的价格收购Sun。该交易价值约为74亿美元。
Oracle的官方网站：http://www.oracle.com
Oracle相关的参考文档
文档：
http://www.oracle.com/technetwork/database/database10g/documentation/index.html
在线：
http://www.oracle.com/pls/db102/homepage
Oracle中的一些概念
（1）Oracle数据库
位于硬盘上实际存放数据的文件，这些文件组织在一起，成为一个逻辑整体，这个就是数据库。数据库是指硬盘上文件的逻辑集合，必须和内存中的实例才能对外提供管理服务
（2）Oracle实例
位于物理内存中存的是数据结构 它是由一个共享的内存池和多个后台进程共同组成的，共享的内存池是可以被所有的进程访问。用户要想存取数据库，必须通过实例才可以实现访问 而不能直接访问硬盘上的文件
（3）Oracle服务器
一个Oracle服务器 是一个数据管理系统（RDBMS）它提供了开放的全面近乎完整的信息管理。由一个Oracle实例和一个Oracle数据库组成
（4）数据库的逻辑和物理结构
【2】搭建Oracle环境（安装与配置）
系统需求
内存需求：1 GB
磁盘空间需求：Oracle软件需要 1.5 GB 到 3.5 GB
操作系统：根据手册文档而定
安装Oracle服务器端
全局数据库名是数据库在服务器网络中的唯一标识
数据库创建完毕后，需要设置数据库的默认用户。Oracle中为管理员预置了两个用户分别是SYS和SYSTEM。同时Oracle为程序测试提供了一个普通用户scott，口令管理中，可以对数据库用户设置密码，设置是否锁定。 Oracle客户端使用用户名和密码登录Oracle系统后才能对数据库操作。
默认的用户中，SYS和SYSTEM用户是没有锁定的，安装成功后可以直接使用，SCOTT用户默认为锁定状态，因此不能直接使用，需要把SCOTT用户设定为非锁定状态才能正常使用。
Oracle 数据库是一个庞大的软件，启动它会占有大量的内存和CPU资源。如果不想让Oracle数据库自动启动，可做如下设置：
虽然一个Oracle数据库服务器中可以安装多个数据库，但是一个数据库需要占用非常大的内存空间，因此一般一个服务器只安装一个数据库。每一个数据库可以有很多用户，不同的用户拥有自己的数据库对象（比如：数据库表），一个用户如果访问其他用户的数据库对象，必须由对方用户授予一定的权限。不同的用户创建的表，只能被当前用户访问。因此在Oracle开发中，不同的应用程序只需使用不同的用户访问即可
（1）SQL语句说明
SQL语言大小写不敏感 Java严格规范大小写
SQL语句可以写在一行或者多行
关键字不能分成多行
（2）一些SQL*Plus命令
说明：命令不区分大小写
（3）登录、注销
说明：用户名不区分大小写，密码区分
登录普通用户：
Sqlplus hr/hr
Sqlplus --->用户名—>密码
登录管理员：
sqlplus / as sysdba;
退出：
exit;
（4）用户锁定、解锁、修改密码
解锁用户：
alter user 用户名 account unlock;
SQL> alter user hr account unlock;
锁定用户：
alter user 用户名 account lock;
SQL> alter user hr account lock;
修改密码：
Alter user 用户名 identified by 新密码
SQL> alter user hr identified by hr;
修改管理员密码：
sqlplus/as sysdba;
忽略输入密码提示，直接回车，随后执行下述指令
alter user sys identified by newpassword;
查看当前登录的用户
select user from dual
查看所有用户
select * from all_users
查看当前登录用户的数据表
select * from user_tables;
（5）终止正在执行的命令
在命令行的SqlPlus中，中止一个正在执行的命令是 Ctrl+/, Ctrl + C，如果直接按Ctrl+C会退出SqlPlus程序。
在sqlplus.exe（单独运行的程序）中，中止一个正在执行的命令是 Ctrl + C
（6）Oracle启动和关闭
services.msc
登录sys用户
启动 starup open
关闭shutdown immediate
SQL语句与SqlPlus命令
<3>Oracle相关的服务
OracleService+服务名，该服务是数据库启动的基础，只有该服务启动了，Oracle数据库才能正常启动。这是必须启动的服务
OracleOraDb11g_home1TNSListener，该服务是服务器端为客户端提供的监听服务，只有该服务在服务器上正常启动，客户端才能连接到服务器。该监听服务接收客户端发出的请求，然后将请求传递给数据库服务器。一旦建立了连接，客户端和数据库服务器就能直接通信了
<4>Oracle方案
scott方案
Hr方案
2.数据库的基本操作-数据查询
此处以普通用户hr/hr中的相关数据表进行分析
【1】查询数据（基本查询）
查询（select .. form ..）
（1）基本语法
SELECT 标识 选择哪些列
FROM 标识从哪个表中选择
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table;
（2）简单查询（全部列与指定列）
选择全部列
--查询所有的列：select * from 表名;
select *
from employees
选择特定的列
--查询指定的列,不同的列之间用,号隔开:select [列名1，列名2...] from 表名;
select employee_id,first_name
from employees
使用数学运算符
乘除的优先级高于加减
优先级相同时，按照从左至右运算
可以使用括号改变优先级
--使用数学运算符进行计算:(用列名代替运算，表示对应列的数据参与运算)
select employee_id,first_name,salary,salary+500
from employees
空值（null）与对空值的处理
空值是无效的、是未指定的、是未知的、是不确定的
空值不是空格”” 也不是0
包含空值的数学表达式都是空值
--由于commission_pct列部分数据为空值，则其参与表达式计算得出的值也是空值
select employee_id,first_name,last_name,salary*(1+commission_pct)*12
from employees
列的别名
重命名一个列（给一个列起别名）
便于计算
紧跟在列名之后，加入
关键字as其中as关键字可以省略
--工资增加500后进行打印
select employee_id,first_name,last_name,salary,salary+500 as newSalary
from employees
使用连接符（连接字符串或列）
连接符：
把“列与列之间”或者“列与字符之间”联系在一起
用
||表示可以’合成 ’列（用指定列合成指定的字符串形式）
--将名与姓之间以空格相连
select employee_id,first_name || ' ' || last_name as name,salary
from employees
对字符串的处理
字符串可以是select列表中的一个字符、数字、或者日期
日期和字符只能出现在单引号中出现
每当返回一行时,字符串被输出一次
--对字符串的处理(合并相关列，打印一行数据作为新的列)
select first_name || ' is_a ' || last_name as sname,email,salary
from employees
删除重复的行
--删除重复的行：distinct关键字
select distinct department_id
from employees
过滤
（1）基本语法
使用WHERE 子句：将不满足条件的行过滤掉。
WHERE 子句紧随 FROM 子句。
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)];
--查询department_id为30
select employee_id,first_name,last_name,department_id
from employees
where department_id=30
（2）处理字符串与日期
字符和日期要包含在单引号之内
字符大小写敏感 日期格式也是敏感的
默认日期格式是DD-MON-RR
--查询first_name = 'lex'的员工信息
--字符串严格区分大小写，如果是‘lex’则无相关员工信息
select employee_id,first_name,last_name
from employees
where first_name = 'Lex'
比较运算
（1）简单运算符
| 操作符 | 含义 |
|---|---|
| = | 等于（不是==） |
| > | 大于 |
| >= | 大于、等于 |
| < | 小于 |
| <= | 小于、等于 |
| <> | 不等于（可为!=） |
--比较运算符的应用
--查询员工的工资、小于等于3000的所有员工的具体信息
select employee_id,first_name,salary
from employees
where salary<=3000
（2）其他运算符
| 操作符 | 含义 |
|---|---|
| BETWEEN...AND... | 在两个值之间[包含边界] |
| IN(set) | 等于值列表中的一个 |
| LIKE | 模糊查询 |
| IS NULL | 空值 |
BETWEEN
--between...and... 表示数据在什么范围中之间
--查询工资在4000-7000之间的所有员工信息
select employee_id,first_name,salary
from employees
where salary between 4000 and 7000
IN
--in关键字表示数据在指定的范围内（指定的内容中）
--查询员工部门号在90-100-110之内的所有员工的信息
select employee_id,first_name,department_id
from employees
where department_id in (90,100,110)
LIKE
使用like运算是选择类似的值
选择条件可以包含数字和字符
%代表零个或者多个字符(任意个字符)
_代表一个字符
-- like 关键字表示选择类似的值
-- 下划线_表示单个字符、百分号%表示任意个字符
--查询first_name 包含an的所有的员工信息
select employee_id,first_name,last_name
from employees
where first_name like '%an%'
--查询第first_name以Da开头的所有员工信息
select employee_id,first_name,last_name
from employees
where first_name like 'Da%'
--查询第1个字符任意、第2个字符是oh、第3个字符任意的所有员工信息
select employee_id,first_name,last_name
from employees
where first_name like '_oh%'
对空值（null）的处理
--对空值null的处理（判断是否为空值用is null进行判断）
--注意的是“=null”、“=‘ ’”均是不合理的判断为空的操作
--查询没有部门的员工
select employee_id,first_name||' '||last_name as name,department_id
from employees
where department_id is null
--查询公司boss（manager_id为空值的对应人员）
select employee_id,first_name||' '||last_name as name,manager_id
from employees
where manager_id is null
逻辑运算
| 操作符 | 含义 |
|---|---|
| AND | 逻辑并 |
| OR | 逻辑或 |
| NOT | 逻辑否 |
（1）AND
-- and 要求两个条件同时满足（为真）
--查询工资大于10000且job_id包含MAN的所有员工
select employee_id,last_name || ' ' || first_name as name,salary,job_id
from employees
where salary>10000 and job_id like '%MAN%'
（2）OR
-- or 要求其中任意一个条件满足即可（为真）
--查询工资大于10000或者job_id包含MAN的所有员工
select employee_id,last_name || ' ' || first_name as name,salary,job_id
from employees
where salary>10000 or job_id like '%MAN%'
（3）NOT
-- not 取非
--查询员工的job_id不在AD_VP,FI_MGR,ST_MAN中的所有员工
select employee_id,first_name,last_name,job_id
from employees
where job_id not in ('AD_VP','FI_MGR','ST_MAN')
优先级
可以使用括号改变优先级顺序，不需要死记硬背相关的比较级顺序，掌握最基本的即可
| 优先级 | |
|---|---|
| 1 | 算数运算符 |
| 2 | 连接符 |
| 3 | 比较符 |
| 4 | IS [NOT] NULL,LIKE,[NOT] IN |
| 5 | [NOT] BETWEEN |
| 6 | NOT |
| 7 | AND |
| 8 | OR |
【2】排序
语法
- 使用
oreder by 子句排序
ASC 升序排序 （ASC是默认的排序方式，可以省略）
DESC 降序排序
Order by子句是在select语句的结尾处
排序规则:
可以按照select语句的别名排序
可以按照select语句中的列名的顺序排序
如果有多个列进行排序，则规则是先按照第一排序，如果第一列相同，则按照第二列排序，依次类推即可
--排序：使用rder by子句
--默认是升序排序asc、降序排序为desc
--按照雇佣日期进行升序排序
select employee_id,first_name,last_name,hire_date
from employees
order by hire_date
--按照工资高低进行降序排序
select employee_id,first_name,last_name,salary
from employees
order by salary desc
按别名排序
--按照年薪方式进行降序排序(此处直接为月工资*12，不考虑奖金)
select employee_id,first_name,last_name,(salary*12) as yearSal
from employees
order by yearSal desc
多个列排序
按照order by列表的顺序排序
可以使用不在select列表中的列排序
--按照部门编号进行升序排序，如果部门编号相同则按照工资进行降序排序
select employee_id,first_name,last_name,department_id,salary
from employees
order by department_id ,salary desc
【3】单行函数
单行函数说明
字符函数
1>大小写控制函数
改变字符的大小写
| 函数 | 结果 |
|---|---|
| LOWER('SQL Course') | sql course |
| UPPER('SQL Course') | SQL COURSE |
| INITCAP('SQL Course') | Sql Course |
-- 此处的 dual 是一张万能表、虚表，能够提供任何数据
--大小写控制函数：LOWER小写、UPPER大写、INITCAP首字母大写
select LOWER('NOOb') from dual
select UPPER('nOOB') from dual
select INITCAP('noOb') from dual
select first_name
from employees
where LOWER(first_name) = 'lex'
2>字符控制函数
控制字符
| 函数 | 结果 |
|---|---|
| CONCAT('Hello','World') | HelloWorld |
| SUBSTR('HelloWorld',1,5) | Hello |
| LENGTH('HelloWorld') | 10 |
| INSTR('HelloWorld','W') | 6 |
| LPAD(salary,10,'-') | -----24000 |
| RPAD(salary,10,'*') | 24000----- |
| TRIM('H' FROM 'HelloWord') | elloWorld |
| replace('abcd','b','m') | amcd |
/*
CONCAT(STR1,STR2):将STR2字符串数据拼接在STR1字符串之后
SUBSTR(STR,a,b):截取字符串STR从第a个字符开始往后截取b个字符（计数从1开始）
LENGTH(STR):取STR字符串的长度
INSTR(STR,CH):得到指定的CH（字符或是字符串）在STR中的位置，不存在则返回0
LPAD(STR,i,CH)：在字符串STR左侧用字符CH填充字符串（总共位数为i）
RPAD(STR,i,CH)：在字符串STR右侧用字符CH填充字符串（总共位数为i）
TRIM('H' FROM 'HelloWorld')：去除字符串首尾的空格或首尾的单个字符
replace(str,ch1,ch2):以ch2替换str中的ch1（ch1、ch2可为字符或者是字符串）
*/
/*
使用字符控制函数完成下述操作
1.将first_name与last_name进行拼接后作为sname输出
2.将job_id的长度作为len输出
3.获取字符a在last_name出现的位置后作为结果输出
4.在first_name左侧填充字符‘*’，总的位数为10位后输出
5.选择的条件是从job_id的第4个位置开始往后截取3个字符的结果为大写的‘rep’
*/
select employee_id,concat(first_name,last_name) as sname,
length(job_id) as len,instr(last_name,'a'),
lpad(first_name,10,'*'),job_id
from employees
where substr(job_id,4,3)=upper('rep');
--trim:去除字符串首尾的空格、也可去除指定的首部或者尾部的字符
select ' Hello World '
from dual
select trim(' Hello World ')
from dual
--截取掉的部分只能是一个字符
select trim('H' from 'Hello')
from dual
select trim('o' from 'Hello')
from dual
--replace:用给定字符（字符串）替换指定字符串的指定字符(字符串)
--如果字符串中没有指定字符则视为无效替换，返回原有的字符串
select replace('abcd','b','x')
from dual
select replace('abcd','s','x')
from dual
select replace('abcd','bc','xx')
from dual
数字函数
ROUND: 四舍五入
ROUND(45.926,2) 45.93
TRUNC：截断
TRUNC(45.926,2) 45.92
MOD：求余
MOD(1600, 300) 100
/*
round(num,i)：将数字num进行四舍五入，保留i位小数
trunc(num):将小数点之后的内容截断舍弃
trunc(num,i)：小数点之前（i为负整数）或之后（i为正整数）第|i|位之后的内容均以0记
mod(num1,num2)：将num1对num2求余
*/
select round(36.973,2),round(36.966,2)
from dual --36.97 36.97
select trunc(36.973,2),trunc(36.973,-2),trunc(362.973,-2)
from dual --36.97 00.000-->0 300.000-->300
select mod(1000,300)
from dual --100
日期相关
Oracle 中的日期型数据实际含有两个值：日期和时间
默认的日期格式是 DD-MON-RR
根据操作系统的不同，需要使用不同的内容，如果是英文的系统使用DD-MON-RR
如果是中文的，可以使用汉字的年月日
1>日期的数学运算
在日期上加上或减去一个数字结果仍为日期
两个日期相减返回日期之间相差的天数
可以用数字除24来向日期中加上或减去小时
--获取系统当前的时间（日期）
select sysdate
from dual
--计算80号部门所有员工从入职到现在工作了多少周，按照工作时间进行降序排序
select employee_id,concat(last_name,first_name) as sname,department_id,
trunc((sysdate-hire_date)/8,2) as weeks,hire_date
from employees
where department_id = 80
order by weeks desc
2>日期函数
| 函数 | 描述 |
|---|---|
| MONTH_BETWEEN | 两个日期相差的月数 |
| ADD_MONTHS | 向指定日期中加上若干约束 |
| NEXT_DAY | 指定日期的下一个日期 |
| LAST_DAY | 本月的最后一天 |
| ROUND | 日期四舍五入 |
| TRUNC | 日期截断 |
配置环境变量：nls_lang=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
--中文系统日期格式：01-4月-2018
--英文系统日期格式：01-sep-2018（月份为英文月份的前三个字母缩写）
--MONTHS_BETWEEN:得到两个日期相差的月数（前者-后者）
select months_between('01-4月-2018','01-9月-2018')
from dual -- -5
--ADD_MONTHS:在指定日期加上若干月数得到新的日期
select add_months('10-5月-2018',6)
from dual -- 2018/11/10
--NEXT_DAY:得到指定日期的下一个日期
select next_day('07-3月-2018','星期日')
from dual -- 2018/3/11
--LAST_DAY:得到本月的最后一天
select last_day('07-3月-2018')
from dual -- 2018/3/31
转换函数
隐式数据类型转换
Oracle自动完成下列转换：
| 源数据类型 | 目标数据类型 |
|---|---|
| VARCHAR2 or CHAR | NUMBER |
| VARCHAR2 or CHAR | DATE |
| NUMBER | VARCHAR2 |
| DATE | VARCHAR2 |
显示数据类型转换
1>TO_CHAR函数对日期的转换
语法：TO_CHAR(date,format_model')
格式：
必须包含在单引号中而且大小写敏感
可以包含任意的有效的日期格式
日期之间用逗号隔开
日期格式的元素
| 格式 | 说明 | 举例 |
|---|---|---|
| YYYY | Full year in numbers | 2011 |
| YEAR | Year spelled out(年的英文全称) | twenty eleven |
| MM | Two-digit value of month 月份（两位数字） | 04 |
| MONTH | Full name of the month（月的全称） | 4月 |
| DY | Three-letter abbreviation of the day of the week(星期几) | 星期一 |
| DAY | Full name of the day of the week | 星期一 |
| DD | Numeric day of the month | 02 |
--日期格式转换
select e.employee_id,concat(e.last_name,e.first_name),to_char(e.hire_date,'DD-Month-YYYY')
from employees e
where e.department_id = 30
2> TO_CHAR 函数对数字的转换
语法：TO_CHAR(number,format_model')
下面是在TO_CHAR 函数中经常使用的几种格式:
| 格式 | 说明 |
|---|---|
| 9 | 数字 |
| 0 | 零 |
| $ | 美元符 |
| L | 本地货币符号 |
| . | 小数点 |
| , | 千位符 |
select e.employee_id,concat(e.last_name,e.first_name),
to_char(e.salary,'L99,999,999.999'), e.department_id
from employees e
where e.department_id = 30
通用函数
这些函数适用于任何数据类型，同时也适用于空值：
NVL (expr1, expr2)NVL2 (expr1, expr2, expr3)NULLIF (expr1, expr2)COALESCE (expr1, expr2, ..., expr n)
NVL函数将空值转换成一个已知的值：
可以使用的数据类型有日期、字符、数字
函数的一般形式 :
NVL(commission_pct,0)NVL(hire_date,'01-JAN-97')NVL(job_id,'No Job Yet')
--查询所有员工的年薪，此处包含奖金，如果奖金为空值则设置为0参与运算
--NVL(e.commission_pct,0),如此一来则则能够通过计算准确得到所有员工的年薪
select e.employee_id,concat(e.last_name,e.first_name),
(e.salary*(1+NVL(e.commission_pct,0)))*12 as yearSal
from employees e
NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3
--查询部门号为30、50、80、90的员工，如果commission_pct不为null则返回字符串true，否则返回false
--NVL2(exp1,exp2,exp3):如果exp1不为null返回exp2，否则返回exp3
select concat(e.last_name,e.first_name),e.commission_pct,
nvl2(e.commission_pct,'true','false'),e.department_id
from employees e
where e.department_id in (30,50,80,90)
NULLIF (expr1, expr2) : 相等返回NULL，不等返回expr1
--判断员工的first_name与last_name的值是否相等
--NULLIF(exp1,exp2):判断exp1与exp2是否相等，相等则返回null,不想等则返回exp1
--NULLIF函数不能够使用别名
select concat(e.last_name,e.first_name),length(e.first_name) as flen,
length(e.last_name) as llen,nullif(length(first_name),length(last_name))
from employees e
使用 COALESCE 函数
COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值
如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE
即：找第一个不为空的值
--COALESCE函数
select concat(e.last_name,e.first_name),e.commission_pct,e.department_id,
coalesce(e.commission_pct,e.department_id,1000) as res
from employees e
条件表达式
在 SQL 语句中使用IF-THEN-ELSE 逻辑
使用两种方法:
CASE 表达式：SQL99的语法，类似Basic，比较繁琐
DECODE 函数：Oracle自己的语法，类似Java，比较简介
1>case表达式
case expr
when comparison_expr1 then return_expr1
[when comparison_expr2 then return_expr2
when comparison_exprn then return_exprn
else else_expr]
end
--如果员工的job_id是IT_PROG工资涨两倍 AD_VP工资涨1.1倍
--PU_CLERK工资涨1.14倍 ST_CLERK工资涨1.2倍
select e.employee_id,concat(e.last_name,e.first_name),e.job_id,
e.salary,e.job_id,
case e.job_id
when 'IT_PROG' then 2*e.salary
when 'AD_VP' then 1.1*e.salary
when 'PU_CLERK' then 1.14*e.salary
when 'ST_CLERK' then 1.2*e.salary
else e.salary
end "revsal" --结束选择,定义别名为revsal
from employees e
2>decode函数
decode(col|experssion,search1,result1
[,search2,reslult2,......,]
[,default]
)
--如果员工的job_id是IT_PROG工资涨两倍 AD_VP工资涨1.1倍
--PU_CLERK工资涨1.14倍 ST_CLERK工资涨1.2倍
select e.employee_id,concat(e.last_name,e.first_name),e.job_id,
e.salary,e.job_id,
decode( e.job_id,
'IT_PROG' , 2*e.salary,
'AD_VP' , 1.1*e.salary,
'PU_CLERK' , 1.14*e.salary,
'ST_CLERK' , 1.2*e.salary,
e.salary) "revsal" --结束选择,定义别名为revsal
from employees e
【4】分组函数(多行函数)
分组函数的基本概念
分组函数作用于一组数据，并对一组数据返回一个值：
组函数使用
类型：
| 函数 | 说明 |
|---|---|
| AVG | 平均值 |
| COUNT | 数量 |
| MAX | 最大值 |
| MIN | 最小值 |
| SUM | 总和 |
可以对任意数据类型的数据使用 MIN 和 MAX 函数
COUNT(*) 返回表中记录总数
--求所有员工的最大工资、最小工资、平均工资、每个月发放工资总和、员工总数
select max(salary),min(salary),avg(salary),sum(salary),count(*)
from employees
--其中count(*)是返回表中的记录总数，也可以用count(1)均可代替
--如果是count(列名)则是返回对应列的记录总数
组函数忽略空值
组函数一般会忽略空值
在组函数中使用NVL函数，NVL函数使分组函数无法忽略空值
--计算拥有奖金的员工的平均值
--组函数会自动忽略空值
select avg(e.commission_pct),sum(e.commission_pct)
from employees e
--如果使用了NVL函数，则考虑了空值的计算
select avg(nvl(e.commission_pct,0)),sum(nvl(e.commission_pct,0))
from employees e
分组数据
employee表示例
（1）group by子句语法
可以使用GROUP BY子句将表中的数据分成若干组
在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中
包含在GROUP BY子句中的列不必包含在SELECT列表中
--在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中
--包含在 GROUP BY 子句中的列不必包含在SELECT 列表中
--求各个部门的平均工资（按照部门进行分组，随后求出每个部门的平均工资）
select e.department_id,avg(salary)
from employees e
group by e.department_id
（2）分组示例
使用多个列分组
# 在group by子句中包含多个列
--可以使用多个列进行分组，group by子句中不允许使用组函数
select e.department_id,e.job_id,avg(salary)
from employees e
group by e.department_id,e.job_id
order by avg(salary)
# 非法使用组函数
--1.所有包含在select中除组函数之外的列均应该被包含在group by子句中
--错误案例分析
select e.department_id,avg(salary),e.manager_id
from employees e
group by e.department_id--会提示e.manager_id不是group by表达式的错误信息
--2.反之，出现在group by子句中的列可以不包含在select子句中
--正确案例分析
select e.department_id,avg(salary)
from employees e
group by e.department_id,e.manager_id --正确显示相应的结果
过滤分组（Having）
--求出部门平均工资高于10000的部门
--常见错误：
select e.department_id,avg(salary)
from employees e
group by e.department_id
where avg(salary)>10000
order by avg(salary)
--如果是在分组条件中要再次进行筛选，则应该使用having子句进行筛选
--必须注意的是where子句中不能够使用组函数，因此不能够用where实现
--正确示例：
select e.department_id,avg(salary)
from employees e
group by e.department_id
having avg(salary)>10000
order by avg(salary) --结果正确显示
组函数嵌套
--组函数之间允许相互嵌套，但组函数最多允许嵌套两层
--如果组函数嵌套过多则出现错误提示（结合组函数的概念进行理解，也没有多大必要嵌套两层以上）
--正确示例：
select max(avg(salary))
from employees
group by department_id;
--错误示例：
select sum(max(avg(salary)))
from employees
group by department_id; -- ORA-00935:组函数的嵌套太深
--如果不是使用单组函数，则不能在select子句中使用普通列
select max(avg(salary))，department_id
from employees
group by department_id; -- ORA-00937：不是单组分组函数
【5】集合运算
集合运算的类型与集合运算符
（1）UNION/UNION ALL并集
UNION表示重复的值只出现一次
UNION ALL表示重复的值出现两次（将两个集合中的所有相应数据查询出来）
（2）INTERSECT交集
- INTERSECT表示查询两个集合中的公共部分
3>MINUS差集
- MINUS表示查询存在于集合A但不存在于集合B的数据
案例分析
1>并集（Uinon、Union All）
并集（Union）
UNION运算符返回两个集合去掉重复元素后的所有记录
--Union:用以返回两个集合去掉重复元素之后的所有记录
--显示所有员工当前和之前的工作情况，要求每条记录只出现1次
select e.employee_id,e.job_id
from employees e
union
select j.employee_id,j.job_id
from job_history j;
--结果显示115条数据
并集（Union All）
UNION ALL 返回两个集合的所有记录，包括重复的
--Union All:用以返回两个集合的所有记录（不考虑纪录重复的情况）
select e.employee_id,e.job_id
from employees e
union all
select j.employee_id,j.job_id
from job_history j;
--结果显示117条数据，说明其中有两条数据是重复的
2>交集（intersect）
INTERSECT 运算符返回同时属于两个集合的记录
--intersect:用以返回两个集合的公共的数据
--显示所有员工当前的工作与之前的工作重复的情况
select e.employee_id,e.job_id
from employees e
intersect
select j.employee_id,j.job_id
from job_history j;
--结果显示2条数据
3>差集（minus）
MINUS返回属于第一个集合，但不属于第二个集合的记录
--minus：用以返回属于第1个集合但不属于第2个集合的纪录
--下述employees表有107条数据，job_history表有10条数据
select e.employee_id,e.job_id
from employees e
minus
select j.employee_id,j.job_id
from job_history j;
--结果显示105条数据（107除去两条重复的纪录）
---------------------------------------------------------------------
select j.employee_id,j.job_id
from job_history j
minus
select e.employee_id,e.job_id
from employees e;
--结果显示8条数据（10除去两条重复的纪录）
4>集合运算的注意事项
select语句中的参数类型和个数一致
可以使用括号改变集合的执行顺序
如果有order by子句，必须放在每一个语句最后
<6>多表查询
基础（相关说明与笛卡尔积）
（1）从多个表中获取数据
（2）笛卡尔集
笛卡尔积 会在下面的条件(情况)下产生
省略连接条件
连接条件无效
所有表中的所有行相互连接
为了避免笛卡尔积的产生，可以加入where条件
--笛卡尔积
select * from employees; --107条数据
select * from departments; --27条数据
--在没有任何连接条件下产生的无效数据：笛卡尔积
select e.employee_id,d.department_id
from employees e,departments d;
--上述语句产生的结果是产生2889条数据：107*27=2889
---------------------------------------------------------------------
--为了避免冗余数据的产生，应该通过where子句提供相应的连接条件
select e.employee_id,d.department_id
from employees e,departments d
where e.department_id = d.department_id ;
--加入连接条件之后筛选出106条数据(其中有一个员工没有相应的部门)
（3）连接的类型
Oracle连接
等值连接、不等值连接、外连接、自然连接
SQL：1999
cross join、natural join、using clasue、full or two sized outer join
Oracle的连接查询
等值连接
在where中加入连接条件
在表中有相同的列 在列名之前可以加上前缀
--等值连接
--筛选出每个员工对应的员工id、姓名、部门id
select e.employee_id,e.first_name||' '||e.last_name as name,d.department_id
from employees e,departments d
where e.department_id = d.department_id ;
连接多个表
连接n张表至少需要n-1个连接条件
--通过相应的连接条件实现多张表的连接
--连接n张表至少需要n-1个连接条件
--筛选出每个员工对应的员工id、姓名、部门id、部门名称、以及部门所在地址id、街道
select e.employee_id,e.first_name||' '||e.last_name as name,
d.department_id,d.department_id,
l.location_id,l.street_address
from employees e,departments d,locations l
where e.department_id = d.department_id
and d.location_id = l.location_id;
外连接
使用外连接可以查询不满足条件的数据
外连接的符号(+)，语法分析如下：
select table1.column,table2.column
from table1,table2
where table.column(+)=table2.column;
select table1.column,table2.column
from table1,table2
where table.column=table2.column(+);
--外连接
/*
左外连接：返回左表相关联的所有结果集，如果左侧的某一行在右表中没有
匹配的行，则返回相应的右侧匹配值为null
*/
--筛选出所有员工的部门信息，将没有部门的员工也一并筛选出来
select e.employee_id,d.department_id
from employees e,departments d
where e.department_id = d.department_id(+);
--结果显示共有107条数据
---------------------------------------------------------------------
/*
右外连接：返回右表相关联的所有结果集，如果右侧的某一行在右表中没有
匹配的行，则返回相应的左侧匹配值为null
*/
select e.department_id,count(*)
from employees e
group by e.department_id;
--根据上述查询可以看到所有员工所在部门共为11个部门(有一个员工部门id为null)
--筛选出所有部门相应的员工，将没有员工的部门也要筛选出来
select e.employee_id,d.department_id
from employees e,departments d
where e.department_id(+) = d.department_id;
--结果显示共有122条数据：106+（27-11）= 122
自连接
--自连接
--自身表与自身表的连接
--筛选出所有员工的id、姓名以及员工对应的领导的id、姓名
select e.employee_id,e.first_name||' '||e.last_name as ename,
m.employee_id,m.first_name||' '||m.last_name as mname
from employees e,employees m
where e.manager_id = m.employee_id;
SQL99标准的连接查询
（1）使用连接从多个表中查询数据
使用连接从多个表中查询数据：
select table1.column,table2.column
from table1
[cross join table2] |
[natural join table2] |
[join table2 using(column_name)] |
[join table2 on (table1.column_name = table2.column_name)] |
[left|right|full outer join table2 on (table1.column_name = table2.column_name)];
（2）叉集
使用cross join 子句连接产生叉集
叉集和笛卡尔积是完全相同的
--叉集(cross join)：等价于笛卡尔积
select e.employee_id,d.department_id
from employees e,departments d;
--------------------------------------
select e.employee_id,d.department_id
from employees e cross join departments d
--上述结果显示共筛选出2889条数据：107*27=2889
（3）自然连接
natural join 子句会以两个表中具有相同名字的列作为等值连接条件，在表中查询满足条件的数据
如果只是列名相同 数据类型不同 也会产生错误
--自然连接
--查询每个员工相应的部门id
select e.employee_id,d.department_id
from employees e,departments d
where e.department_id = d.department_id;
--natural join子句
--natural join子句会自动将两个表中相等的列作为连接条件进行查询
/*
此处需要注意的有两个问题
natural连接不能够使用限定词进行查询
如果两个表中作为连接的列只是列名相同，数据类型不同的话也无法操作
自然连接是特殊的等值连接，其会根据连接条件去除重复的纪录
*/
--错误案例：
select e.employee_id,d.department_id
from employees e natural join departments d;
--正确示例：显示所有员工对应的部门编号
select e.employee_id,department_id
from employees e natural join departments d;
--两个表中相同的列不能够使用限定词，但是不同的列还是可以使用限定词进行操作
select employee_id,department_id
from employees natural join departments d;
--上述语句执行显示32条数据
--其等价于下述语句
select e.employee_id,d.department_id
from employees e,departments d
where e.department_id = d.department_id and e.manager_id = d.manager_id;
/*
分析：
自然连接会自动匹配所有列名、数据类型相应相同的列作为连接条件
根据表格内容分析employees表和departments表中均具有相同的列
其分别为manager_id、department_id，因此是将这两个列作为连接条件
进行查询，从而返回的结果为32条数据记录
但如果是根据where表达式限定查找的连接条件，可以看到如果仅仅是限定了
department_id作为连接条件，则返回的结果为106条数据记录
*/
----------------------------------------------------------
select d.department_name,d.manager_id,location_id,city
from departments d natural join locations;
select d.department_name,d.manager_id,l.location_id,city
from departments d , locations l
where d.location_id = l.location_id;
--上述语句执行均显示27条数据：departments表与locations表只有一个公共列location_id
（4）使用 USING 子句创建连接
在创建等值连接的时候，可以使用using子句指定等值连接条件
使用using子句可以在有多个列满足条件的情况下进行筛选
不要给选中的列加上表名或者前缀或者别名
join 一般都是和using结合使用
-- join经常与using子句结合使用
--使用using子句创建连接条件
--显示所有员工对应的部门编号
select e.employee_id,department_id
from employees e join departments
using (department_id);
（5）使用ON 子句创建连接
自然连接是以具有相同名字的列作为连接条件
可以使用ON子句指定额外的连接条件
这个连接条件是与其他条件分开的
ON子句有更高的可读性
--on子句
--查询员工的id、姓名、员工的部门id、部门名称、员工部门所在的城市的名称
--方式1：普通查询使用where子句
select e.employee_id,e.first_name||' '||e.last_name as name,
d.department_id,d.department_name,l.city
from employees e,departments d,locations l
where e.department_id = d.department_id
and d.location_id = l.location_id;
--方式2：使用join结合on子句实现
select e.employee_id,e.first_name||' '||e.last_name as name,
d.department_id,d.department_name,l.city
from employees e join departments d on e.department_id=d.department_id
join locations l on d.location_id=l.location_id;
--上述两种方式均返回106条数据
（6）内连接和外连接
在SQL: 1999中，内连接只返回满足连接条件的数据
两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外联接
两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行 ，这种连接称为满外联接
--外连接
/*
左外连接：
根据连接条件检索左表中的所有数据，如果在右表中没有匹配的值则相应置为null
*/
--检索所有员工的相应部门，将没有部门的员工也一并检索
select e.employee_id,d.department_id
from employees e,departments d
where e.department_id = d.department_id(+);
---------等价于下述语句---------------
select e.employee_id,d.department_id
from employees e left outer join departments d
on e.department_id = d.department_id;
--外连接需要指定相应的连接条件，上述语句测试均检索出107条语句
/*
右外连接：
根据连接条件检索右表中的所有数据，如果在左表中没有匹配的值则相应置为null
*/
--检索所有部门的员工，将没有员工的部门也一并检索
select e.employee_id,d.department_id
from employees e,departments d
where e.department_id(+) = d.department_id;
---------等价于下述语句---------------
select e.employee_id,d.department_id
from employees e right outer join departments d
on e.department_id = d.department_id;
--上述语句测试均检索出122条数据
/*
满外连接：
两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行
*/
select e.employee_id,d.department_id
from employees e full outer join departments d
on e.department_id = d.department_id;
--上述语句测试检索出123条数据，将员工当中没有部门id的员工和没有员工的部门也一并显示出来
<7>子查询
子查询基本语法
select select_list
from table
where expr operator
(select select_list
from table);
注意事项
子查询是包含在括号内的
将子查询放在比较条件的右侧
单行操作符对应子查询 多行操作符可以对应多行子查询
--查询谁的工资比Donald高
/*
分析：
查询谁的工资比Donald高？可以分为两步进行分析
1.获取Donald的工资
select e.first_name,e.salary
from employees e
where e.first_name = 'Donald';
检索出的结果为2600
2.检索比Donald工资高的员工信息
select e.employee_id,e.salary
from employees e
where e.salary > 2600;
检索出92条数据
*/
--子查询语句实现
select e.employee_id,e.salary
from employees e
where e.salary > (select e.salary
from employees e
where e.first_name = 'Donald');
--需要注意的是此处查询相应返回的结果只需要工资即可
--如果查询返回的列太多则提示“to many values”错误
TOP-N分析
/*
简单理解隐藏的rowid和rownum概念
rowid是能够唯一标示该行数据的数据记录
rownum是按照现有表的数据进行排序的数据记录
*/
--eg:查询员工工资最高的三个人
/*
步骤分析：
a.先将排序后的数据表进行固化
select rownum,e.*
from employees e
order by e.salary desc;
(得到的是按照工资大小进行降序排序后的数据信息
但此时的rownum还是按照原有的表的数据排序)
记录此时得到的新的表别名为a
b.再次通过隐藏的rownum进行查询
select rownum,a.first_name,a.salary
from a
where rownum<=3;
(此时得到的结果是按照工资降序排序后的前三个数据记录)
*/
select rownum,a.first_name,a.salary
from (select rownum,e.*
from employees e
order by e.salary desc) a
where rownum<=3;
分页查询
--eg:查询工资第31-40名的员工信息
/*
需要注意的是，rownum是伪列，必须从第1行数据开始查询
如果不是从第1行数据开始取，则查无数据
以下两种查询方式显示结果均为查无数据
select rownum,a.first_name,a.salary
from (select rownum,e.*
from employees e
order by e.salary desc) a
where rownum>=31 and rownum<=40;
------------------------------------
select rownum,a.first_name,a.salary
from (select rownum,e.*
from employees e
order by e.salary desc) a
where rownum between 31 and 40;
*/
/*
步骤分析：只能把伪列,固化为实际的列再进行应用
a.把员工按照工资的高低进行排序,a表是排序之后的表
b.按照排序之后的表,把rownum进行固化成实例的列,固化之后的表为b表
c.按照固化之后的列进行查询数据即可
*/
select rn,b.first_name,b.salary
from (select rownum rn,a.first_name,a.salary
from (select rownum,e.*
from employees e
order by e.salary desc) a) b
where rn between 31 and 40;
-------------------------------------------
--1.将排序后的表进行固化 2.再次固化伪列 3.查询
select b.rn ,b.salary
from (select rownum rn ,a.*
from (select rownum,e.*
from employees e
order by e.salary desc)a)b
where b.rn between 5 and 10;
单行子查询
只返回一行数据，使用的是单行比较符
（1）在子查询中使用组函数
--在子查询中使用组函数
--eg：查询工资为最低工资的员工信息
select e.employee_id,e.salary
from employees e
where e.salary = (
select min(salary)
from employees);
（2）子查询中的 HAVING 子句
首先执行子查询
向主查询中的HAVING 子句返回结果
--查询最低工资比20号部门最低工资高的部门有哪些
/*
可以先查询20号部门的最低工资，然后再进行比较
步骤分析：
a.先将员工按照部门进行分组
b.获取20号的部门的最低工资
c.检索最低工资比20号部门工资高的部门信息
*/
select e.department_id,min(e.salary)
from employees e
group by e.department_id
having min(e.salary)>(select min(e.salary)
from employees e
where e.department_id = 20)
（3）非法使用子查询
多行子查询使用单行比较符
--非法使用子查询
--多行子查询使用单行比较符:
--single-row subquery returns more than one row
--eg：查询工资大于名字中包含’an‘字符串的员工的员工信息
select e.first_name,e.salary
from employees e
where e.salary > (
select e.salary
from employees e
where e.first_name like '%an%');
--分析：由于first_name包含an字符串的员工有多个，不能够使用单行操作符进行操作7.5
多行子查询
返回多行，使用多行比较操作符
| 操作符 | 含义 |
|---|---|
| IN | 等于列表中的任何一个 |
| ANY | 和子查询返回的任意一个值比较 |
| ALL | 和子查询返回的所有值比较 |
在多行子查询中使用 in操作符
在多行子查询中使用 Any 操作符
在多行子查询中使用 All 操作符
--in操作符（等于子查询返回的列表中的任意一个）
select e.first_name,e.salary
from employees e
where e.salary in (
select e.salary
from employees e
where e.first_name like '%an%');
---------------------------------------------------------------------
--any操作符（与子查询返回的任意一个值进行比较）
select e.first_name,e.salary
from employees e
where e.salary > any(
select e.salary
from employees e
where e.first_name like '%an%');
--此处结果显示只需要满足工资大于子查询返回结果的最低工资2800即可
---------------------------------------------------------------------
--all操作符（与子查询返回的所有值进行比较）
select e.first_name,e.salary
from employees e
where e.salary > all(
select e.salary
from employees e
where e.first_name like '%an%');
--此处结果显示只需要满足工资大于子查询返回结果的最高工资12008即可
分级查询
可以明确的看到上下级关系
分级查询可以从上往下查询 也可以从下往上查询
参考示例：https://blog.csdn.net/psp0001060/article/details/53364179
3.数据库的基本操作-管理表结构(DDL)
【1】管理表结构（DDL）基础
创建自己的用户
/*
创建自己的普通用户
a.拥有管理员权限管理员，以管理员身份登录
b.创建自己的用户（设置用户名、密码）
c.赋予普通用户权限
管理员登录选择’sysdba‘
普通用户登录选择’normal‘
*/
--以管理员身份登录，创建新用户,并赋予用户权限
create user hzgg identified by "hzgg"
grant connect,resource to hzgg
--回收权限
revoke connect,resource from hzgg
--删除用户
drop user hzgg cascade
表结构和数据类型
选中指定的表，右键选择查看，能够查阅到有关表格的详细信息
| 数据类型 | 描述 |
|---|---|
| VARCHAR2(size) | 可变长字符数据 |
| CHAR(size) | 定长字符数据 |
| NUMBER(p,s) | 可变长数值数据 |
| DATE | 日期型数据 |
| LONG | 可变长字符数据，最大可达到2G |
| CLOB | 字符数据，最大可达到4G |
| RAW and LONG RAW | 原始的二进制数据 |
| BLOB | 二进制数据，最大可达到4G |
| BFILE | 存储外部文件的二进制数据，最大可达到4G |
新建表
（1）建表规则
表名和列名
必须以字母开头
必须是1-30个字符
只能包含A-Z a-z _$ 和#
不能和用户定义的其他对象重名
不能是oracle 的关键字
Oracle默认存储都是大写的
常见的数据库对象
| 对象 | 描述 |
|---|---|
| 表 | 基本的数据存储集合，由行和列组成 |
| 视图 | 从表中抽出的逻辑上相关的数据集合 |
| 序列 | 提供有规律的数值 |
| 索引 | 提高查询的效率 |
| 同义词 | 给对象起别名 |
（2）create table语法
必须指定:
表名
列名, 数据类型, 数据类型的大小
要求必须具备以个资源才可以建表:
CREATE TABLE权限
存储空间
create table [schema.]table
(column datatype [default expr][,...]);
create table student(
sid number(10), --学生id
sname varchar2(20), --学生姓名
address varchar2(30), --学生地址
note1 varchar2(200), --冗余字段1
note2 varchar2(200) --冗余字段2
);
select * from student;
（3）使用子查询创建表
使用 AS subquery选项，将创建表和插入数据结合起来
指定的列和子查询中的列要一一对应
通过列名和默认值定义列
使用子查询创建表举例
create table tablename
[(colunm,column,...)]
as subquery
--利用现有的表创建新的表（用于屏蔽原有表的部分信息，亦可用视图实现）
create table mytable
as
select s.sid,s.sname,s.address
from student s;
select * from mytable;
修改表结构（alter table）
（1）语法说明
新增列
alter table table_name
add (
column datatype [default expr]
[,column datatype [default expr]...]
);
修改列
alter table table_name
modify (
column datatype [default expr]
[,column datatype [default expr]...]
);
删除列
alter table table_name
drop column (column);
重命名
alter table table_name
rename column old_column_name to new_column_name;
（2）示例
追加一个列
--1.增加新的列
alter table student add(password varchar2(20));
修改一个列
--2.修改一个已有的列（此处的修改仅仅只是修改列的数据类型）
alter table student add(descr varchar2(20));
alter table student modify(descr varchar2(100));
--修改列的名称（涉及重命名）
alter table student rename column note1 to email;
删除一个列
--3.删除一个列
alter table student drop column note2;
清空表（Truncate）
Truncate关键字删除表中所有的数据，释放表空间，但表结构依然存在的
--4.清空表的数据（只是清空数据，而不删除表的结构）
--insert into mytable values(1,'小李','杭州');
--形式1：truncate table 表名
truncate table mytable;
select * from mytable;
--形式2：delete 表名
delete mytable;
删除表（Drop Table）
drop关键字实现的是将数据和表结构都删除掉，且所有的涉及到的索引全部被删除
truncate清空表仅仅是清空数据 表结构依然存在。清空表的时候，所有运行的相关事务都会被自动提交
--5.删除表（删除表的数据、表的结构、表的索引）
drop table mytable;
改变对象的名称
执行RENAME语句改变表, 视图, 序列, 或同义词的名称
必须是对象的拥有者
--重命名表、序列、视图、或是同义词
rename student to stu;
约束
（1）约束说明
约束是表一级的限制
如果存在依赖关系 约束可防止错误数据的删除
约束的类型
| 约束 | 说明 |
|---|---|
| Primary key | 主键约束 |
| Unique key | 唯一约束 |
| Not null | 非空约束 |
| Foreign key | 外键约束 |
| Check | 检查约束 |
约束的规则：
用户可以自定义约束，使用 oracle server的sys_cn格式命名
约束创建的时机：
创建表的时候，同时创建约束；
表结构创建完成后， 再去添加约束
约束可以定义在列一级，或者是表一级
通过数据字典查询约束
约束按照定义位置划分：
表级约束： 可以将定义完成表之后再去定义约束，同时可以在多个字段上定义
列级约束： 在定义表的时候就必须定义，这个约束只能定义在一个字段上
约束定义名称格式：
| 约束 | 命名规范 |
|---|---|
| 非空约束 | NN_表名_列名 |
| 唯一约束 | UK_表名_列名 |
| 主键约束 | PK_表名 |
| 外键约束 | FK_表名_列名 |
| 条件约束 | CK_表名_列名 |
| 默认约束 | DF_表名_列名 |
not null非空约束只能定义为列级约束，其余4种约束（主键约束、唯一约束、检查约束、外键约束）均可以通过列级约束或者事表级约束实现创建
（2）约束案例分析
主键约束Primary key
主键约束在表中定义一个主键来唯一确定表中每一行数据的标识符
（主键约束要求对应列满足非空、唯一两个条件）
主键约束可以是列级约束，也可以是表级约束
唯一约束Unique key
唯一性约束唯一约束保证在一个字段或者一组字段里的数据与表中其它行的数据相比是唯一的
唯一性约束可以是表级约束，也可以是列级约束 ，可以在定义表的时候定义约束，也可以定义完成后再定义约束，并且可以同时在多个字段上定义
非空约束Not null
- 非空约束只能是列级约束，只能在定义表的时候去定义 并且不能同时定义在多个字段上
外键约束Foreign key
如果表A的主关键字是表B中的字段，则该字段称为表B的外键，表A称为主表，表B称为从表。外键是用来实现参照完整性的
外键约束可以是列级约束，也可以是表级约束
检查约束Check
- 检查约束是列级约束，也是表级约束 定义每一行必须满足check 的条件
案例分析1
创建表格
/*
创建teacher表：
tid 教师id -- 主键
tname 教师姓名 --not null
t_birthday 教师生日
要求定义t_birthday不为空，约束名称为t_birthday_nn
*/
create table teacher(
tid number(10) primary key, --主键
tname varchar2(20) not null, --非空
t_birthday date constraint t_birthday_nn not null
--为列t_birthday创建非空约束，约束名称为t_birthday_nn
);
select * from teacher;
查看当前创建的约束，可以通过指令查看，也可直接查看创建的表的相关信息
方式1：通过查询数据字典进行查阅-->user_constranits
--查阅数据字典，查看相应的索引
--1.查询当前用户定义的所有约束：user_constrains
select *
from user_constraints
where table_name = 'TEACHER';
--p:主键约束
--R:外键约束
--C:检查约束和非空约束
--U:唯一约束
--2.查看约束是建立在哪个字段上：用户字典user_cons_columns
select *
from user_cons_columns
where table_name = 'TEACHER';
方式2：通过右键点击表名，选择“查看”选项，随后查看相应的“索引选项卡”的内容
数据测试
--数据测试
select * from teacher;
--1.正常插入数据
insert into teacher values(1001,'张老师','13-4月-1998');
insert into teacher values(1002,'白老师','20-6月-1989');
--2.非法插入数据
--a.违反主键约束：主键tid要求唯一、非空
insert into teacher(tname,t_birthday) values('李老师','3-8月-1999');
insert into teacher values(1001,'李老师','3-8月-1999');
--b.违反非空约束：tname、t_birthday要求非空
insert into teacher values(1003,'','3-1月-2000');
insert into teacher values(1004,'小黑','');
案例分析2
创建表格
--创建表格
/*
创建学生student表
sid 学生id --主键,表级约束
sname 学生姓名 --唯一,表级约束
sage 学生年龄 --非空,列级约束
address 学生住址 --非空,列级约束
*/
create table student(
sid number(10) ,
sname varchar2(20),
sage number(4) not null,
address varchar2(30) not null
);
select * from student;
--在表定义之后创建表级约束
alter table student add constraint sid_pk primary key(sid);
alter table student add constraint sname_un unique(sname);
--查看索引信息
select *
from user_cons_columns
where table_name = 'STUDENT';
非法操作 :not null不能够定义为表级约束，只能定义为列级约束
alter table student add constraint sage_nn not null(sage);
数据测试
---测试数据
select * from student;
--1.正常插入数据
insert into student values(2015001,'张三',18,'浙江杭州');
insert into student values(2015002,'李四',19,'浙江杭州');
--2.非法插入数据
--a.违反唯一约束：sname 唯一
insert into student values(2015003,'张三',18,'浙江杭州');
--主键约束、非空约束例子参考案例1，此处不作累述
案例分析3
创建表格
--表级约束和列级约束
/*
列级约束：在定义列的时候同时创建约束
列名1 数据类型 约束类型，
列名2 数据类型 约束类型，
.......
表级约束：在列或表定义之后在创建约束
可对单个字段或者是多个字段添加约束
a.对字段添加完成后再次添加约束
constraints 约束名称 约束类型(列名)
constraints 约束名称 约束类型(列名1，列名2...)
b.定义完表之后再添加约束
alter table 表名 add constraints 约束名称 约束类型(列名)
alter table 表名 add constraints 约束名称 约束类型(列名1，列名2...)
5种约束中只有not null非空约束为列级约束，其它4种即可为列级约束，又可为表级约束
*/
--创建表格
/*
创建员工表：employee
eid 员工id --主键
ename 员工姓名 --唯一
address 员工地址 --非空
*/
create table employee(
eid number(10),
ename varchar2(20),
address varchar2(30) not null,
--对字段添加完成后再次添加约束,仅限于表级约束
constraints pk_employee_eid primary key(eid),
constraints uk_employee_ename unique(ename)
);
select * from employee;
--查看EMPLOYEE的约束信息
select *
from user_cons_columns u
where u.table_name = 'EMPLOYEE';
/*
创建顾客表：customer
cid 顾客id --主键
cname 顾客姓名
address 顾客地址
由ename与address作为联合约束条件，约束类型为unique
*/
create table customer(
cid number(10) primary key,
cname varchar2(20),
address varchar2(30) not null
);
select * from customer;
--在表定义完之后为customer中的指定列创建约束条件，仅限于表级约束
alter table customer
add constraint uk_customer_cname_address unique(cname,address);
select * from customer;
--查看CUSTOMER的约束信息
select *
from user_cons_columns u
where u.table_name = 'CUSTOMER';
数据测试
--数据测试
--1.正常插入数据
--employee表中插入数据
insert into employee values(1001,'张三','浙江杭州');
insert into employee values(1002,'李四','广东惠州');
select * from employee;
--customer表中插入数据
insert into customer values(1001,'张三','浙江杭州');
insert into customer values(1002,'李四','广东惠州');
select * from customer;
--2.非法插入数据
--employee表中ename为‘唯一’，因此插入失败
insert into employee values(1003,'张三','浙江温州');
--customer表中将cname与address联合作为唯一约束，要满足两个字段同时不同
案例4
创建表格
--创建表格
/*
创建部门表deptments
dept_id 部门id --主键
dept_name 部门名称 --not null
address 部门地址 --not null
*/
create table departments(
dept_id number(10) primary key,
dept_name varchar2(20) not null,
address varchar2(30) not null
);
select * from departments;
/*
创建员工表employees
employee_id 员工id --主键
employee_name 员工姓名 --not null
salary 员工工资 --not null
dept_id 员工部门id --创建外键
*/
create table employees(
employee_id number(10) primary key,
employee_name varchar2(20) not null,
salary number(10) not null,
dept_id number(10),
constraint fk_employees_departments
foreign key(dept_id) references departments(dept_id)
);
--查看创建的约束信息
select *
from user_cons_columns
where table_name = 'EMPLOYEES';
/*
亦可定义完表之后创建外键约束
创建格式：
alter table 表名
add constraint 约束名称
foreign key（列名）references 引用表名（引用列名）
举例分析：
alter table employees
add constraint fk_employees_departments
foreign key(dept_id) references departments(dept_id);
*/
数据测试
--数据测试
--a.增加数据
select * from departments;
select * from employees;
--1.正常插入数据
--在departments部门表中添加数据
insert into departments values(10,'人事部','浙江杭州');
insert into departments values(20,'财务部','浙江温州');
insert into departments values(30,'开发部','浙江台州');
--在employees员工表中添加数据
insert into employees values(1001,'小李',10000,10);
insert into employees values(1002,'小白',8000,20);
insert into employees values(1003,'小黑',12000,30);
--2.非法插入数据
--插入部门id为不存在的部门的员工信息
insert into employees values(1004,'小张',3000,50);
--b.删除数据
--删除问题：
--a.删除没有员工的部门：此处以40号部门为例，成功删除！
delete
from departments
where dept_id = 40;
--b.删除存在员工的部门：此处以10号部门为例，删除出错！
delete
from departments
where dept_id = 10;
--b.删除数据
--删除问题：
--1.删除没有员工的部门：此处以40号部门为例，成功删除！
delete
from departments
where dept_id = 40;
--2.删除存在员工的部门：此处以10号部门为例，删除出错！
delete
from departments
where dept_id = 10;
--c.引入“级联删除”概念
/*
根据不同业务需求,在创建外键的时候设置相应的删除方式
a.如果部门删除,但是公司正常运行,只需要把员工的部门设置为空即可
不会删除相应的员工信息
on delete set null 在删除父项同时设置子项内容为空
b.如果部门删除 但是公司一并解散
在删除部门的同时一并删除员工信息
on delete cascade 级联删除
*/
--重新创建emlpoyees表，设置相应的外键约束，插入数据，测试不同的删除方式
drop table employees;
create table employees(
employee_id number(10) primary key,
employee_name varchar2(20) not null,
salary number(10) not null,
dept_id number(10),
constraint fk_employees_departments
foreign key(dept_id) references departments(dept_id)
-- on delete set null
on delete cascade
);
select * from employees;
--插入数据
insert into departments values(10,'人事部','浙江杭州');
insert into employees values(1001,'小李',10000,10);
--删除数据
delete
from departments
where dept_id = 10;
--查询相应的部门信息
select * from departments;
/*
结果分析：
在删除含有员工的部门时，分别对应以下两种情况
a.on delete set null：删除部门，不删除员工信息，部门置空
b.on delete cascade：删除部门及其对应的所有员工信息
*/
案例5
创建表格
--创建表格
/*
创建人员表person
pid 人员id --主键
pname 人员名称 --not null
age 年龄 --0<=age<=150
gender 性别 --男、女
dept_id 部门编号 --10,20,30
*/
create table person(
pid number(10) primary key,
pname varchar2(20) not null,
age number(3) check(age>=0 and age<=150),
gender varchar2(4),
dept_id number(10),
--定义完列名之后定义表级约束
constraint ck_person_gender check(gender='男' or gender='女')
);
select * from person;
--在定义表之后在创建约束
alter table person
add constraint ck_person_dept_id check(dept_id in (10,20,30,40));
--查看创建的约束
select *
from user_cons_columns
where table_name = 'PERSON';
数据测试
--数据测试
--1.正常插入数据
insert into person values(1001,'张三',18,'男',10);
insert into person values(1002,'小吴',50,'男',20);
insert into person values(1003,'小燕',29,'女',30);
--2.非法插入数据
--a.年龄超出范围
insert into person values(1004,'小李',151,'男',40);
--b.性别超出范围
insert into person values(1004,'小李',27,'中',40);
--c.部门编号超出范围
insert into person values(1004,'小李',27,'男',50);
（3）数据字典
Oracle中数据库表可以分为用户自定义的表和数据字典两类
用户自定义的表由用户自己创建并维护，其中保存了用户的信息
数据字典表是由oracle自动创建并维护的一组表
数据字典表主要分为三类
DBA—所有的方案的对象
ALL—当前用户可以访问的所有对象
User—当前用户可以访问的对象信息
--数据字典
--a.查看当前用户的缺省表空间
select username,default_tablespace from user_users;
--b.查看当前用户的角色 (系统赋予了什么权限)
select * from user_role_privs;
--c.查看当前用户的系统权限和表级权限
select * from user_sys_privs;
select * from user_tab_privs;
--查看信息
--a.查看当前用户创建的所有表
select table_name from user_tables;
--查看信息
--a.查看当前用户创建的所有表
select table_name from user_tables;
--b.查看当前用户可以访问的表
select table_name from all_tables;
（4）约束管理
约束的查询
--约束查询
--查询数据字典：查询当前用户创建的所有约束user_constraints
select *
from user_constraints;
--查询用户字典:需要查看约束是建立在哪个字段上user_cons_columns
select *
from user_cons_columns;
--可以使用where条件限制选择要查询的条件
创建表之后添加约束
在创建表之后再添加约束针对的是表级约束，not null不能够用这种方式进行定义，其只能用列级约束
定义格式：
alter table 表名
add constraint 约束名称 约束类型(列名);
针对外键约束定义的格式：
alter table 表名
add constraint 约束名称
foreign(列名) references 引用表(引用列名);
删除约束
删除约束格式：
alter 表名
drop constraint 约束名;
--删除约束
alter table person
drop constraint ck_person_dept_id
select *
from user_constraints
where table_name = 'PERSON';
4.数据库的基本操作-数据处理
【1】数据处理基础
处理数据（DML）
数据控制语言（DML）
DML(Data mainipultation language)数据操纵语言
事务的完成是由若干个DML语句组成的
把sql的操作分为四大类
DML(Data mainipultation language) 数据操纵语言：insert update delete select
DDL(Data Defintied Language) 数据定义语言：create alter drop
TCL(Transaction Controll Language) 事务控制语言：commit savepoint rollback
DCL(Data Controll Language) 数据控制语言：grant revoke
插入数据
（1）insert语句语法
使用 INSERT 语句向表中插入数据
使用这种语法一次只能向表中插入一条数据
insert into table [(column [,column ...])]
values (value [,value...]);
（2）插入数据
插入数据的三种方式：
a.省略列名
insert into 表名 values(value1,value2);
b.插入指定的列（列名与value要一一对应）
insert into 表名(列名1，列名2，...... ) values(value1,value2，...... );
c.通过创建脚本进行创建
insert into 表名 values（&提示1，&提示2，...... ）;
insert into 表名（列名1，列名2，....）values（&提示1，&提示2，...... ）;
通过&实现，需要注意的是如果插入的是字符或字符串则需要加上‘单引号’进行标识
（3）案例分析
创建表格
--插入数据案例分析
--创建表格
/*
创建student表
sid 学生id --primary key
sname 学生姓名
sage 学生年龄
grade 学生成绩
*/
create table student(
sid number(10) primary key,
sname varchar2(20),
sage number(3),
grade number(3)
);
select * from student;
--插入数据测试
/*
插入数据有三种方式
a.省略列名插入数据
insert into 表名 values(value1，value2，...);
b.插入指定的列
insert into 表名（列名1，列名2，...）values(value1，value2，...);
c.通过创建脚本方式插入数据
insert into 表名 values(&提示1，&提示2，...);
insert into 表名（列名1，列名2，...）values(&提示1，&提示2，....);
*/
数据测试
--a.省略列名：必须使得插入数据的顺序与定义的顺序一一对应
insert into student values(1001,'张三',18,90);
--在满足约束的条件下，可以插入空值（用空值填充数据）
--显示插入空值：在values子句中插入指定的空值
insert into student values(1002,'李四',null,null);
--隐式插入空值:在插入的时候省略相应列的值
insert into student(sid,sname) values(1003,'王五');
--b.插入指定的列
insert into student(sid,sname,grade) values(1004,'王五',95);
--c.通过创建脚本进行创建
insert into student values(&学生编号,&学生姓名,&学生年龄,&学生成绩);
insert into student(sid,sname) values(&学生编号,&学生姓名);
--亦可插入指定的数据
insert into student(sid,sname) values(1007,sysdate);
--查询插入的所有数据
select * from student;
更新数据
（1）基本语法
update table
set column = value [,column=value,...]
[where condition];
（2）案例分析
--更新数据
--普通更新数据
--1.将student表中sid为1007的学生姓名修改为小七，并设置相应的成绩
update student
set sname='小七',grade=95
where sid=1007;
select * from student;
--使用子查询更新语句
--2.将student表中sid为空的学生成绩设置为与sid为1005的学生成绩相同
update student
set grade=(select grade
from student
where sid=1005)
where grade is null;
select * from student;
删除数据
（1）基本语法
delete [from] table
[where condition];
（2）分析案例
--删除数据
--a.普通删除指定数据
--删除一条指定数据
--1.删除student表中sid为1007的数据
delete
from student
where sid = 1007;
select * from student;
--删除多组数据
--2.删除student表中sid小于1005的所有数据
delete
from student
where sid < 1005;
select * from student;
--b.通过子查询删除指定数据
--3.删除student表中成绩与sid为1005的学生相同的学生信息
delete
from student
where grade = (select grade
from student
where sid = 1005);
select * from student;
（3）Delete和Truncate
delete和truncate都是删除表中的数据
delete操纵可以rollback（回滚），但delete 操纵可能会产生碎片，并且不会释放空间
truncate 清空表
--delete与truncate
insert into student values(1001,'张三',18,92);
select * from student;
--delete删除表中数据，可以回滚
delete from student;
点击数据回滚，恢复删除之前的内容
select * from student;
Truncate语句执行之后自动提交，此时可以看到相应的回滚按钮并没有亮起来，数据也被清空
Powered by Waline v3.1.3