Oracle: 数据库,
1,认识数据库
数据库：数据的仓库，保存大量数据的地方，有利于对数据的维护。增删改查很方便。
数据库分类：
层次型数据库：现实世界中很多事物是按层次组织起来的。层次数据模型的提出，首先是为了模拟这种按层次组织起来的事物。
网状型数据库：处理以记录类型为结点的网状数据模型的数据库。处理方法是将网状结构分解成若干棵二级树结构，称为系。
关系型数据库：mysql SQLserver oracle
分布式数据库:是指数据分存在计算机网络中的各台计算机上的数据库
oracle 数据库
oracle ：甲骨文公司 全球第一大数据库厂商
2 关系型数据库概念
a,由 E.F.Codd博士在1970年提出关系型数据库系统模型
b,它是关系型数据库管理系统的基础
c,关系型数据库模型包含以下3部分内容
i 包含一系列【关系】和【对象】
ii 包含一系列对于关系的操作–>增删改查
iii 包含数据完整性约束
【规范保存到数据库中的数据】
- 关系型数据库中的关系是指:
把实际问题的数据分别归成若干个简单的二元关系，
一个二元关系都可以建一个满足一定条件的二维表格
关系 : 二维表格
3 关系型数据库是由一系列二维表组成
二维表：每张表由行和列组成
可以通过Structured Query Language 操作数据库表中的数据
二维表：一行 一条数据
一列(字段) 一类数据
Primary Key：主键
主键：表中的一个列，随便是什么列
要求：非空且唯一
主要用于 唯一的表示一条数据
Foreign Key：外键
为了表结构的优化，所产生的操作，
使用外键进行表和表的关联
外键:值可以为空，可以重复，
如果有值必须是关联表主键中的值.
用来关联两张表
4 完整性约束条件
【不满足 完整性，数据不能保存到数据库中】
关系完整性是为保证数据库中数据的正确性和相容性，对关系模型提出的某种约束条件或规则
在关系模型中，关系完整性主要是指以下三方面：
a、实体完整性(主键约束)：所谓的实体完整性就是指关系(所谓的关系就是表)的主键不能取空值；
比如学生表通常是取学号为主键
b、参照完整性(外键约束)：是指参照关系中每个元素的外码要么为空(NULL)，要么等于被参照关系中某个元素的主码；
比如今天是9月2日是开学日，大学新生刚来报道,在学生表里，有的学生可能还没来得及分配具体的班，所以这些还未来得及分班的学生教务处可以在学生表里的班级属性取空值NULL(空值代表“不确定”)，而哪些已分了班的学生就必须取班级表里的某些属性，比如班级类别，即学生属于哪个班。比如取“软件工程”，”计算机技术应用“等等。参照关系也称为外键表，被参照关系也称为主键表。
c、用户定义的完整性(自定义约束)：指对关系中每个属性的取值作一个限制(或称为约束)的具体定义。比如 性别属性只能取”男“或”女“ ，再就是年龄的取值范围，可以取值0-130 ，但不能取负数，因为年龄不可能是负数
横着看：就是看行，是一条数据。
竖着看：就是看列，是某一类数据。
看数据库：
圆饼累加的圆柱都代表数据库，
在关系型数据中都是一张一张表存放的。
每一张表放大都是一个二维表
oracle数据库是不区分大小写的
字段/属性
Table Name:S_customer Table Name:s_emp
id name phone emp_id id name
201 tom 151000 12 10 市场部
202 jak 152000 14 11 教管部
203 tom 159000 11 12 mis部
204 sport 153000 14 14 教学部
解释：
id(Primary key)---主键列(非空且唯一)：以后每一个表都有一个id列，因为每一条数据需要一个特殊标示,这个id就是唯一的标示了一条数据。
name:标示这一列都是代表姓名信息
phone:标示这一列都是代表电话信息
rep_id:外键(Foreign key),这样是另一个表的主键(id),表与表之间就是通过这样的外键关联起来。
学习oracle需要了解：
Oracle中的对象
table：表 。重点学习的对象。
view：视图。在表的基础上，以后可能是一个结果作为一个视图
sequence：序列。将来这个序列就是用来产生id主键，主键要求唯一不重复
index：索引，提高我们的检索速度
Program unit：程序单元 在PL/SQL中声明的 存储过程，函数，包Program unit
oracle的组成：
oracle 10g:
9I:internet
10G:网格grid
11G
12C:云计算
内 ------------------> 外
Data-------------->operation System --> oracle10g --------->sql,pl/sql----->tools
电脑内存中的数据 打开操作系统 安装oracle软件 执行sql命令 使用各种工具
sql和sqlplus以及plsql区别：
sql：structured query language
有结构的 查询 语言 ---->结构化查询语言
第三代: 高级编程语言
第四代: 有结构的查询语言
只关心做什么 不关心怎么做
sql 命令 需要 ; 结尾，标示一条sql语句结尾
需求 : 得到名字叫张三的学生 所有信息
查询 张三
sqlplus:我们在登录oracle的时候提供一种登入oracle的方式
sqlplus 工具 用于执行 sql语句
sqlplus工具 可以完成的事情:
1,可以登入oracle数据库
2,执行输入的sql语句并且显示结果。
3,提供部分命令
sqlplus：有一些内置的命令，当使用这些命令的时候不需要 ; 结尾, 但是有 ; 也不会错
pl/sql:在存储过程中，在sql语言的基础上增加了逻辑控制功能。
BEGIN
IF c_id <= 0 THEN
RAISE ex_invalid_id;
ELSE
SELECT name, address INTO c_name, c_addr
FROM customers
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
END
oralce主要操作:
1.select语句
2.数据操纵语言(DML语句)data manipulation language
对数据库里的数据进行操作 查询 更新 插入 删除
insert, update, delete
插入 更新 删除
有事务概念–>commit;
3.数据定义语言(DDL语句)data definition language
建表时候使用
create(创建), alter(修改), drop(删除), rename(重命名), truncate(删除表中的数据)
会提交事务
4.事务控制语句(TCL语句)transction control language
commit(提交), rollback(回滚), savepoint(记 录点,用来回滚)
5.数据控制语言(DCL语句)data control language
设置或者更改数据库用户和权限的语句
grant【授权】, revoke【回收】
安装数据库:
1521端口号 oracle的主服务端口号:使用oracle必须开启oracle主服务
8080端口号 oracle监听端口号:使用网页oracle或者使用工具连接oracle需要开启监听服务
localhost:8080/apex—>网页版oracle
oracle基础前导操作:
1.以管理员方式登录oracle
sqlplus system/密码
show user:显示当前登录的用户名字
2.创建新用户briup,密码briup
create user briup identified by briup;
3.给新用户briup授权
grant connect,resource to briup;
conn briup/briup
@.sql路径
使用网页版oracle 创建用户:
不推荐
推荐:sqlplus:sql命令创建oracle数据库
3.激活用户:
alter user briup account unlock;
4.切换至briup用户
conn briup/briup
5.设置当前会话(session)的日期语言格式
alter session set nls_date_language=english;
6.执行sql文件
start e:/table.txt
@路径
7.查询当前用户下有哪些表？
select table_name from user_tables;
查看表有哪些列？
desc 表名
学习阶段主要涉及表:
S_EMP ：员工表
id id
last_name 后名字 姓
first_name 前名字 名
userid 员工内部编号，没用
start_date 入职日期
comments 注释：记录员工的特点
manager_id 经理的id
title 职称
dept_id 所在部门id外键
salary 工资
commission_pct 提成
S_DEPT：部门表
id id
name 部门名称
region_id 所在地区id的外键
S_REGION ：区域表
id id
name 区域名
三张表 主键 外键 关系需要很清楚
oracle二维表中列的数据类型:
1,NUMBER[(p,s)] 数值类型
p代表有效位数 精度(总长度)，
s代表小数点后位数 刻度
eg:
NUMBER(6,2):
123456.00
1234.123
1234.12
123.123
NUMBER(3):
123
12.3
0.123
123.00
2,字符类型:
char的长度是不可变的，而varchar的长度是可变的，
2.1:CHAR(8) 定长字符类型:占用空间固定
00000tom
0000lisi
hello world 不能保存
2.2:VARCHAR(8) mysql 变长字符类型:占用空间 跟着输入有关
2.3:VARCHAR2(8) oracle 变长字符类型:占用空间 跟着输入有关
tom
lisi
hello world 不能保存
oracle中字符串用 ''表示
oracle关键字不区分大小写，但是字符串区分大小写
3,日期类型:DATE
默认:04-9月-17
3.1:日期类型，世纪年月日时分秒都会保存
3.2:中文语言环境:
系统默认:‘DD-MM-YY’
eg:‘16-7月-17’ —> 2017/7/16
3.3:修改当前语言环境:
alter session set nls_date_language=english;
3.4:英文语言环境
系统默认:‘DD-MM-YY’
eg:‘16-JULY-17’ —> 2017/7/16
3.5:修改日期显示格式
alter session set nls_date_format=‘yyyy-mm-dd’;
eg:select sysdate from dual;
【写一个长得和日期一样的字符串】
4,CLOB:超级长字符,整本小说的所有字符串 可以保存
5,BLOB:字节数组:可以用于保存文件的二进制字节数组.
字节数组: byte[]
select 语句 ：作用:查询数据
1:distinct ：消除重复行,有多条重复数据就只会显示一条
语法：
select [distinct] {*|(列名1,列名2...)}
from table;
* : 所有的列
样例：
(1)查询s_dept表中的所有信息
eg:
select * from s_dept;
(2)查询s_emp表中的last_name,salary
eg:
SeleCT LAst_NaME,Salary
from S_emP;
(3)查看s_emp表id,last_name,salary字段信息(列)
eg:
select id,last_name,salary
from s_emp;
(5) 查询所有部门名
eg:
select name
from s_dept;
(5.1) 去除重复的行
select distinct name
from s_dept;
2,起别名：给某一个列 起别名
语法:
select 列1 as 新列名字,列2 as 新列名字…
from 表
a.使用as
b.空格后直接跟别名
select 列1 别名1,列2 别名2...
from 表
c.使用双引号区分大小写
3, 算术运算符 + - * /
查询出来的列是可以运算的
当月工资 加 100;
eg：查询 s_emp 中last_name 列和年薪
4,nvl(参数一，参数二):空值函数
参数一 ： 可能为空的列，
参数二 ： 如果参数一的列为空 就使用参数二的值代替
单纯查工资和提成：
egs:
select salary,commission_pct
from s_emp;
eg: 查询当月工资:
使用nvl空值函数 进行操作
select salary*(1+ nvl(commission_pct,0) )
from s_emp
5,连接符||：可以将多个字符串或者多个字符串列的值进行拼接
作用：可以将多个列的值或者字符串进行拼接
注意：oracle中字符串使用的是单引号
oracle中单引号表示字符串.
语法:
select 列||‘你好’ from s_emp;
eg:1字符串拼接:
hello world
select 'hello'||'world' from dual;
dual表:哑表-->同义词
任何用户都可以使用,
一般使用做测试.
eg:2字符串拼接列:
姓:last_name
eg:3列拼接列:
名:三 姓 :张
sqlplus 命令特点:
运送sql语句)
把sql语句的结果集展示
1 : 缓存机制：
1.1:sqlplus会保存最近执行过的一条sql语句(可能是多行)
1.2:每次执行的sql语句都会保存在buffer中，但是会覆盖掉之前的sql语句
2 :命令 l : 显示刚才执行过的sql语句，并且写到缓存中。
语法: l
3 :命令 / : 执行缓存中的sql语句
语法: /
4 :命令 n : 直接输入一个数字 就是选中某一行--->下面用到的[定位]。
语法: n
5:命令 a[ppend] : 在【定位】的那一行后面追加新的内容
语法: 先定位
a 被追加的字符串
6 :命令 c[hange] : 在【定位】的那一行修改指定的字符串内容
语法: 先定位
c /old/new
7 :命令 cl[ear] buff[er] : 清除缓存区中的sql语句
语法: cl buff 或 clear buffer
8 :命令 del : 删除【定位】的那一行
语法: 先定位
del
9 :命令 i[nput] : 在【定位】的那一行的后一行插入新的一行
语法: 先定位
i 把字符串插入下面一行
10 :命令 l[ist] n : 显示第n行内容
语法: l 4 显示第4行内容
11 :命令 n sql语句 : 重写那一行
语法: 2 from s_dept; //重写第二行
总结:
1，需要已经执行过一条sql语句。就是sqlplus缓存中有一条sql语句
2，需要先选中有问题的那一行， 输入n 定位某一行
3，使用下面相应的关键字 操作
2 :命令 SAVE filename : 将缓存中sql语句保存到本地文件
语法: SAVE 文件名
默认位置:C:\Users\Administrator
3 :命令 GET filename : 将文件中sql语句加载到缓存中，但是不执行sql语句
语法: get 文件名
4 :命令 START filename : 将文件中sql语句加载到缓存中，并且执行sql语句
语法: start 文件全路径+文件名
5 :命令 @ filename : 将文件中sql语句加载到缓存中，并且执行sql语句
语法: @ 文件全路径+文件名
6 :命令 edit filename : 以记事本形式打开文件,编辑sql语句, Ctrl+s保存,关闭。
语法: edit 文件全路径+文件名
7 :命令 ed : 将缓存中sql写到文件afiedt.buf并且打开文件
语法: ed
8 :命令 spool filename : 开始保存 sqlplus输入的任何结果,(录控制台)
语法: spool startPlus.text
9 :命令 spool off : 结束
语法: spool off
注意:spool 和spool off必须要一起使用
作用：记录sqlplus中sql操作
20: :命令 col [{column] [option] : 修改列在sqlplus中的显示格式(样式)
option : 取值: cle for hea jus
语法: col name for a15;//修改name列在sqlplus显示的长度为15
20.1:for操作字符串和日期：col 列名 for a12; 把这一列的长度定成12字符
20.2:for操作数字类型： col 列名 for 占位符;
补充: 占位符,用来占数字的位置
1) 9 ：不强制补全 ,就是数字占位符一个9占一个数字位，如果数据数字长度大于9占位符长度就还会显示###，
col salary for 99999 就是这一列变成5个长度，因为用了5个9来占位
2) 0 ：强制补全
col salary for 00099 就是这一列变成5个长度，因为用了三个0个两个9来占位，但是0是自动补全的，所有会在前面补0直到满足5个长度
3) $ :西方的货币符号 货币符号在一列数据中只能出现一个
col salary for $9999 就是用四个9占位，但是在显示的时候回在前面加一个$号
col salary for $0000
4) L :本地货币符号
同上，但是加的是本地的货币符号
5) . :小数点
就是小数点的意思
col salary for 9999.9999 就是一个四位数精确到小数点后四位。
6) , ：分隔符
col salary for 9,999.99 在那个地方输出一个，号
20.3: col cle : 清除某一个列的格式
语法: col name cle
20.3: clear columns : 清除所有列的格式
使用:
L : 查看缓存sql
/ : 执行缓存sql
n sql : 重写缓存第n行的sql语句
c /old/new : 替换当前选中行的值
save 文件名 : 把缓存中是sql保存到某一个文件
get 文件名
@ 文件名
start 文件名
-----------------------------day1------------------------------
排序 Order by
1,语法:order by 列名 排序,列名 排序;
升序：asc 如果不写 默认为asc
降序：desc
eg:
select id,last_name,salary,commission_pct
from s_emp
order by commission_pct;
null 最大
2,特点：
2.1 : order by放在select语句最后一行，最后，最后，最后
2.2 : 默认升序(asc),降序(desc)
2.3 : order by后面可以跟列名、别名、select后面字段名的序号
不写排序规则就是默认升序排序
eg:
select id s_id ,salary a
from s_emp
order by s_id;
显示升序排序
eg:
显示降序排序
eg:
select id,salary,commission_pct
from s_emp
order by commission_pct desc;
字符串排序是按照ASCII值排序
eg:
按照多个列排序
eg:
select id,last_name,salary
from s_emp
order by salary desc,id;
注意：如果按照多个列进行排序，多个列之间逗号隔开，先按照第一个列的值进行排序，如果第一个列的值相同，那么会按照第二个列的值进行排序。.......以此类推
按照commission_pct进行排序
select last_name,commission_pct
from s_emp
order by commission_pct asc;
2.4 : null 表示无限大
select salary
where子句
作用:过滤(筛选)要查询的数据
书写位置：放置在from子句的后面 紧跟着from
书写组成：可以由字段名(列名)、常量、比较符、表达式等组成。
1, 基本操作符
逻辑比较操作符
= > < >= <=
select id,last_name,salary
from s_emp
where id = 10;
select id,last_name,salary//选列进行显示
from s_emp
where id < 5;//对行约束
不等于:三个都表示不等于的意思(经常用的是!=)
!= <> ^=
eg：查询 id last_name salary
from s_emp 需要查询的
条件是 工资大于1000
select id,last_name,salay
from s_emp
where salary > 1000
NGao
select salary
from s_emp
where last_name = ‘Ngao’
eg:查询不是41部门的员工信息
select id,last_name,dept_id,salary
from s_emp
where dept_id != 41
2, (not) between 一 and
在某一个区间 [一,二]
select id,last_name
from s_emp
where id between 20 and 50;
eg:工资不在1000-2000之间的员工信息
select id,last_name,salary
from s_emp
where salary not between 1000 and 2000;
where salary between 100 and 1000;
where salary not between 100 and 1000;
eg:
select id,last_name,salary
from s_emp
where id between 4 and 10;
[4,10]
eg:
select id,last_name,salary
from s_emp
where salary not between 1000 and 2000;
3, 列名 (not) in
(41,42,43)某一列的值只能是括号里的数据
eg：where 列名 in(41,42,10)
select id,last_name,salary
from s_emp
where id in (1,2,3,4,5);
工资1250|1000|1200
select id,last_name,salary
from s_emp
where salary in(1250,1000,1200);
查询名字为Ngao,Biri的信息
from s_emp
where last_name in(‘Ngao’,‘Biri’);
4, and 且
需要连接多个条件
语法:where 条件一 and 条件二 当条件一和条件二都满足的数据才会被查询出来
eg:工资不在1000-2000之间的员工信息
from s_emp
where salary < 1000 or salary > 2000 and id = 10 or last_name = ‘Ngao’
eg:工资不在1000-2000之间的员工信息并且部门号只能是41 42 44
from s_emp
where salary not between 1000 and 2000
and
dept_id=41
or
dept_id in(41,42,44)
5, or 或
需要连接多个条件
eg: where 条件一 or 条件二 or 条件三 当条件一或者条件二满足一个就可以出来
select id,last_name,salary
from s_emp
where id < 10 or salary>1000;
6, (not) like：模糊匹配
_ ：占位符 占一个符号位
% : 占位符 占0-多个位子
语法: where last_name like ‘tom’;
where last_name like ‘_om’;
where last_name like ‘%o%’;
escape : 定义转义符号。将特殊含义的字符串 转义为普通字符串
语法: where last_name like ‘/_briup’ escape ‘/’;
eg:last_name N开头员工信息
select id,last_name,salary
from s_emp
where last_name like ‘N%’
eg:last_name 第二个字符 是 g
select id,last_name
from s_emp
where last_name like ‘_g%’;
eg:包含g
select id,last_name,salary
from s_emp
where last_name like ‘%g%’
插入一条sql语句
insert into s_emp(id,last_name) values(99,’_briup’);
commit;
eg:查询下划线开头的用户信息
select id,last_name
from s_emp
where last_name like ‘#_%’ escape ‘#’;
7, is (not) null : 判断那一列是null；
语法: where commission_pct is null;
select id,salary * commission_pct
from s_emp
where commission_pct is not null;
注意：oracle中字符串使用单引号，单引号中的字符区分大小写
eg:41号部门，salary 大于1500 salary降序
select dept_id,salary
from s_emp
where dept_id = 41 and salary < 1500
order by salary desc;
优先级：
(1)算术运算符
(2)连接符 ||
(3)比较符
(4)列名 [not]in 列名 [not]like
(5)列名 [not]between 1 and 2
(6)not
(7)and
(8)or
(函数)...
*使用括号改变优先级
select id,dept_Id,salary
from s_emp
where
id > 10
or
dept_Id > 40
and
salary > 1100
lambda:实现函数式接口
Function<T,R>{
R appect(T)
}
String msg = “hello”;
Function<String,Integer> f = msg->10;
f = msg::companTo;
函数 关注 参数 和 返回值
函数 ：单值函数 + 组函数
1，基础
位置：可以出现在select、where、order by字句中
单值函数分类：
字符函数:
lower('');
upper('');
substr('',0,0);
length('')
nvl('','');
数字函数:
round(3.435,2);
trunc(43.256,2);
mod(30,4)
日期函数
months_between('12-3月-17','12-5月-17');
转换函数
to_char(32,'$99,99');
to_number('123321');
to_date('2017-06-11','yyyy-MM-dd');
dual：哑表:系统表,只是每个新建的用户有权限操作,里面只有一条数据.
2,字符函数:
2.1 : lower('参数') : 大写转小写，字符串全都转出小写
语法:
lower('字符串')
lower(列名)
eg:转换为小写HELLO worLd
select lower('HELLO world')
from dual;
eg:查询Chang用户有关信息
select id,last_name
from s_emp
where lower('Chang') = 'chang';
eg:把last_name列传为小写进行操作
select lower(last_name)
from s_emp;
2.2 : upper('')：小写转大写，字符串全都转出大写
-
2.3 : substr(参数1,参数2,参数3)
substr(参数1,参数2,参数3)
参数1：要截取的字符串 必须
参数2：必须是数字 开始截取的位置(从几号位开始截取(会包含当前位置)) 必须
整数：角标是从1开始的没有零，如果你写从0开始，系统会让你从1开始。
负数：从后面开始截取
参数3：必须是数字 截取的长度，如果没有参数3，会截取到最后 可选
负数：没有意义hello world
select substr(‘hello world’,3,4) from dual;
select substr(‘hello world’,0,4) from dual;
select substr(‘hello world’,-2,4) from dual;
select substr(‘hello world’,-5) from dual;
select substr(‘hello world’,5) from dual;select substr(last_name,2)
from s_emp;eg: 取子串：‘Hello WOrlD’ 从3开始 取5个长度
2.4 : length(参数1)：
求字符串或者字符串列的长度
eg:求hello world的长度
select length('hello world')
from dual;
eg:查询名字长度为4的用户信息
select last_name
from s_emp
where length(last_name) = 4
2.5 : nvl : 空值函数 nvl(object,num)如果Object不为空返回objec如果为空返回num;
nvl(参数一,参数二)
参数一：可能为空的列
参数二：如果那列的值为空 就用参数二来替代
eg:把last_name = _briup 的first_name
如果的null 就替换成Briup#123456
3,数字函数:
3.1 : round(参数一,参数二) ：四舍五入
参数一:数字
参数二:精确到小数点后几位
select round(21.56,2) from dual; 20.56
select round(21.5656,1) from dual; 20.6
select round(25.5656,-1) from dual;20
select round(25.5656,-2) from dual;0
3.2 : trunc(参数一,参数二)：只舍不入
参数一:数字
参数二:精确到小数点后几位
select trunc(43.256,2) from dual;
select trunc(43.256,0) from dual;
select trunc(43.256,-1) from dual;
3.3 : mod(参数一,参数二):取余数
参数一:数字
参数二:对几取余
select mod(7,3)
from dual;
eg:工资是600的整数倍
select id,last_Name,salary
from s_emp
where mod(salary,600) = 0;
3.5: 日期中的round和trunc 保留的年or月
月:5舍6入
日:15舍16入
select round(to_date('27-7月-16'),'month') from dual;
select round(to_date('27-7月-16'),'year') from dual;
month:15舍16入
year:6舍7入
select round(sysdate,'day')
from dual;
select round(sysdate,'month') from dual;
select round(sysdate,'year') from dual;
select trunc(sysdate,'month') from dual;
select trunc(sysdate,'year') from dual;
4,日期函数：
4.1 : sysdate:当前系统时间
select sysdate from dual;
select id,salary,sysdate
from s_emp;
select sysdate,start_date
from s_emp;
select start_date
from s_emp
where sysdate > start_date;
4.2 : current_date:当前系统时间秒数。有区别...有兴趣的同学自己看看：http://www.cnblogs.com/haitao-fan/archive/2013/01/27/2878841.html
select id,salary,current_date
from s_emp
4.3 : months_between(参数一,参数二)：两个日期相差多少个月
参数一：日期或者当前系统格式的日期字符串
参数二：日期或者当前系统格式的日期字符串
得到的是负数：参数一 < 参数二 :
得到的是小数：不满一个月 --> 1/31
select months_between(sysdate,sysdate+1) from dual;--->1 相差一个月 sysdate+31 算术运行符
select add_months(to_date('20-7月-20'),2) from dual;
5,转换函数：
5.1 : to_char(参数一,参数二):将数字或者日期类型的数据转换为字符串类型
返回值为字符串
参数一:数字或日期
数字：
参数二：‘数字输出格式 使用上面学到的数字占位符’
select to_char(1234,’$999,999’)
from dual;
日期：
参数二：'日期输出的格式 如下介绍'
格式：
把日期转换成字符串，指定为参数2的格式
yyyy 年
rrrr 四位数的年份
yy 年
rr 两位数的年份
mm 月
d 这周的第几天
dd 这个月的第几天
ddd 这年的第几天
year 英文下的年
month 月
ddsp 当前日期
ddspth 同上
day 星期几
dy 星期几
hh24 小时
mi 分钟
ss 秒
Q 季度
WW 当年第几周
W 当月第几周
eg:
select to_char(123,'$999.99') tochar from dual;
select to_char(sysdate,'yyyy--mm?dd+d ddd year month ddsp ddspth day dy hh24 mi ss ') 日期 from dual;
5.2 : to_number(参数一):将字符串数据转换为数字类型
返回值是一个数字
参数一:全数字的字符串
只能将能够转化成数字类型的字符串转换成数字类型
select to_number('12345') from dual;
select to_number('abc') from dual;//error
5.3 : to_date(参数一,[参数二]):将字符串转化为日期类型
返回值是日期类型Date
参数一：满足当前系统时间格式的字符串
参数二: 某一个日期规格
to_date('12-7月-16')-->2016/7/12
to_date('2016-3-4','yyyy-MM-dd');//2016/3/4
5.4:
临时改动日期的格式
alter session set nls_date_format='yyyy-mm-dd HH24:MI:SS';
alter session set nls_language='simplified chinese';
第三章：
哑表：dual,满足select子句的结构
处理函数的返回值
select…
from dual;
单值函数的使用
1）单值函数在字符串上的使用
lower:将字符串转化为小写
select lower(‘HELLO World’)
from dual;
upper:将字符串转化为大写
select upper(‘HELLO World’)
from dual;
initcap:每个单词的首字母大写，其他字母小写
select initcap(‘hello world’)
from dual;
concat:字符串的连接
select concat(‘Hello’,’ gril’)
from dual;
substr:求子串
select substr(‘hello world’,3,6)
from dual;
第一个参数：字符串，母串
第二个参数：子串开始的索引(索引从1开始的)
第三个参数：子串的长度
length:求字符串的长度
select length(‘hello world’)
from dual;
NVL：处理空值
练习：查询所有员工的全名(使用first_name,last_name分割)，
全名以大写的方式显示，并且要求first_name
的长度为6，按照全名进行降序排序？
select upper(last_name||’,’||first_name) name
from s_emp
where length(first_name)=6
order by 1 DESC;
///
select upper(concat(last_name,
concat(’,’,first_name)))
where first_name like ‘______’
order by 1 DESC;
2)单值函数在数字类型上的使用
round:四舍五入
select round(45.67) from dual; //46
select round(45.67,1) from dual; //45.7
select round(45.67,2) from dual; //45.67
select round(45.67,0) from dual; //46
select round(45.67,-1) from dual; //50
select round(45.67,-2) from dual; //0
select round(55.67,-2) from dual; //100
trunc:只舍不取
select trunc(45.67) from dual; //45
select trunc(45.67,1) from dual; //45.6
select trunc(45.67,2) from dual; //45.67
select trunc(45.67,0) from dual; //45
select trunc(45.67,-1) from dual; //40
select trunc(45.67,-2) from dual; //0
select trunc(55.67,-2) from dual; //0
mod:取余
select mod(1500,400) from dual;
3)单值函数在日期类型上的使用
select sysdate from dual;
设置日期显示格式：
alter session set nls_date_language=english;
select sysdate from dual;
MONTHS_BETWEEN：两个日期相差的月数
select months_between(sysdate,‘18-MAY-13’)
from dual;
select months_between(‘18-MAY-13’,sysdate)
from dual;
该函数精确到毫秒级别的。
ADD_MONTHS:一个日期加上一个月数之后的日期
select add_months(sysdate,4)
from dual;
NEXT_DAY:即将来临的星期几的日期
select next_day(sysdate,‘friday’)
from dual;
select next_day(sysdate,6)
from dual;
LAST_DAY:一个月的最后一天
select last_day(sysdate)
from dual;
select last_day(‘10-MAY-13’)
from dual;
ROUND:四舍五入
select round(to_date(‘19-MAY-13’),‘MONTH’)
from dual;
练习：测试对于MONTH的临界时
15舍16入
测试对于YEAR的临界时
6舍7入
TRUNC:只舍不取
多表连接查询:
1,oracle检索规则
1，将表s_emp中的每一条数据放到结果集中。
2，如果该条数据满足where条件那么就留在结果集中，并检索下一条数据。
3，如果该条数据不满足where条件 那么就从结果集中剔除该数据，并检索下一条数据。
查询员工的姓名 部门名
1,笛卡尔积:有些记录的连接是没有意义的
使用where条件解决这种问题
n个表，n-1个条件
2,等值连接 : 一般通过外键和主键的值相同进行表关联 外键=主键
2.1: 连接条件使用 =
2.2: 两列的值是一样的
例一：查询所有员工的ID，名字和所在部门的名称？
**第一步：确定表**
s_emp
s_dept
**第二步：确定连接条件**
s_emp.dept_id = s_dept.id
第三步：拼写sql语句
select e.id,last_name,name
from s_emp e,s_dept d
where e.dept_id = d.id;
例二：查询id小于30员工的姓名和部门名称所在地区的名称？
1,
s_emp
s_dept
s_region
2,确定连接条件
s_emp.dept_Id = s_dept.id
s_dept.region_id = s_region.id
3,拼接
select last_name,d.name,r.name
from s_emp e , s_dept d,s_region r
where dept_id = d.id
and region_id = r.id
and e.id < 30;
练习一：查询部门名称包含sa的员工姓名和薪水
s_emp
s_dept
dept_Id = s_dept.id
select last_name,salary,name
from s_emp e,s_dept d
where dept_id = d.id
and lower(d.name) like '%sa%';
练习二：查询部门名称是5位，
该部门员工的薪水不等于1500，
并按员工的薪水降序排序
1确定表
s_emp
s_dept
2,连接条件
dept_id = s_dept.id
3,拼接
select last_name,name,salary,dept_Id
from s_emp e,s_dept d
where dept_Id = d.id
and salary != 1500
and length(d.name)=5
order by salary desc;
3,不等值连接 : 连接条件使用的不是等号的连接, >= <= between…and
解释 : 不等值连接就是表一的某一列 是表二中某几列的一个中间值
前提 :
创建工资等级表
create table s_rank(
id number(5) primary key,
minSal number(7),
maxSal number(7),
name varchar2(10));
insert into s_rank values(1,0,1000,'蓝领');
insert into s_rank values(2,1000,1500,'白领');
insert into s_rank values(3,1500,2500,'金领');
commit;
练习:
例1：查询所有员工的工资等级？
s_emp 员工表
s_rank 等级表
2，连接条件
s_emp.salary between s_rank.minsal and s_rank.maxsal
salary >= minsal and salary <= maxsal
3,拼接
select last_name,salary,name
from s_emp e,s_rank r
where salary between minsal and maxsal;
4,外连接 :外键(某一列)为空 并且需要被查询出来
如果有部分数据不满足连接条件也要被查询出来
插入数据:
insert into s_emp(id,last_name) values(999,’_briup’);
insert into s_dept(id,name) values(60,‘教学部’);
commit;
4.1:左外连接:
语法: 左边的表当做基准。左表的数据全部显示出来，右表显示符合的数据
from 左表 left outer join 右表 on a表和b表连接的连接条件
from s_emp left join s_dept on dept_id=s_dept.id
左表: 某一条数据外键如果为空 也要被显示出来
用（+）来实现， 这个+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。如果加号写在右表，左表就是全部显示，所以是左连接。
【位于左表中的一条数据不满足连接条件也要被查询出来】
eg:查询员工的id,姓名,所在部门id,
部门名,所有员工需要被查询出来
s_emp
s_dept
dept_Id = s_dept.id
但是员工_briup没有部门，不满足连接条件
但是需求需要被查询出所有员工
select e.id,last_name,d.id,name
from s_emp e left join s_dept d
on dept_Id = d.id;
eg:查询员工的id,姓名,所在部门id,
部门名,所有部门需要被查询出来
s_emp
s_dept
dept_Id = s_dept.id
但是 教学部 没有任何员工,不满足连接条件
教学部需要被查询出来
左外连接:
select e.id,last_name,d.id,name
from s_dept d left join s_emp e
on dept_Id = d.id
注: outer可以不写
4.2:右外连接:
语法:
from 左表 right outer join 右表 on a表和b表连接的连接条件
from s_emp right join s_dept on dept_id=s_dept.id
会把右表中外键(右表的连接条件)为空的列也查询出来
右表: 某一条数据外键(单纯列,两个表连接的条件列)如果为空 也要被显示出来
eg:查询员工的id,姓名,所在部门id,部门名,所有部门需要被查询出来
【位于右表中的一条数据不满足连接条件也要被查询出来】
4.3:全连接:
解释:综合了左外和右外连接
左表外键为空 右表外键为空的数据都会被显示出来
【把两个表中的数据都全部显示出来,虽然连接条件不成立】
语法:
from 左表 full outer join 右表 on 两个表的连接条件
from s_emp full join s_dept on dept_id=s_dept.id
eg:查询所有员工以及对应的部门的名字,
没有任何员工的部门也要显示出来,
没有部门的员工也要显示出来
select e.id,last_name,d.id,name
from s_emp e full join s_dept d
on dept_id = d.id;
5,自连接
连接两张相同的表：通过表的别名，将同一张表视为多张表
不适合操作大表
解释 ：当前表和当前表连接
eg:查询每个员工的名字以及员工对应的经理的名字
员工表 经理表
s_emp s_emp
id manager_id id manager_id
注:下面两表 e表示员工
m表示经理
select e.id,e.last_name,m.id,m.last_name
from s_emp e, s_emp m
where e.manager_id = m.id
6,集合操作符:单词背
前提：是对结果集进行操作,俩个被操作的结果集中查询的列要完全一致
语法 ：
结果集一
集合操作符
结果集二;
select 完整语句
munus
select 完整语句;
minus : 差集 : 第一个结果集减去第二个结果集和它相同的部分
eg:使用集合操作符，查询id为2-7之间的员工信息。
select id,last_name
from s_emp
where id<8
minus
select id,last_name
from s_emp
where salary = 2500;
union : 并集’’
两个查询的列要相同
eg: 使用集合操作符，查询id小于5和id大于10的员工信息
select id,last_name
from s_emp
where id<5
union
select id,last_name
from s_emp
where id>3;
union all : 并集
eg: 使用集合操作符，查询id小于5和id大于10的员工信息
观察和上一个区别
select id,last_name
from s_emp
where id<5
union all
select id,last_name
from s_emp
where id>3;
intersect : 交集
eg:使用集合操作符，查询id小于10，并且工资大于1000的员工信息。
select id,last_name
from s_emp
where id < 10
intersect
select id,last_name
from s_emp
where salary > 1000;
1,操作两个结果集
2,必须列相同
3,第一个结果集不需要写 ;
7rownum : 伪列(oracle数据库独有的)
检索规则: 检索表中每条数据，会从上到下检索，每次检索一条数据，
每次检索的数据会先放到结果集中，放到结果集中的数据如果满足where条件 就会留在结果集中，如果不满足就会从结果集中删除。
定义:在结果集中的每条数据oracle会自动分配一个列–>伪列–>伪列的值是1-n
作用：给结果集中的数据编号
测试一:查询id,last_name,rownum
但是只要伪列等于2的数据。
select id,last_name,rownum
from s_emp
where rownum = 2;
无数据
测试二:查询id,last_name,rownum
但是只要伪列小于3的数据。
select id,last_name,rownum
from s_emp
where rownum < 3
为什么有数据？分析？
测试三:查询id,last_name,rownum
但是只要伪列大于3并且伪列小于5的数据。
select id,last_naem,rownum
from s_emp
where rownum < 5 and rownum > 3
为什么没有数据？分析？
思考:利用伪列进行分页操作(介绍方式)
分组函数/组函数:
1,基础:分组概念
任意一个列都可以进行分组
工资相同的人分到一个小组
1.1:效果:分组以后，表变成什么样子了
1.2:关键字: group by
1.3:书写位置:紧跟着where 后面
1.4:语法: group by a列,b列...
1.5:意思: 对当前结果集中的数据进行分组,对a列分组,如果a列中有相同的数据再按照b列进行分组
如果按照多个列分组，会先按照第一列分组，然后按照第二列分组…
或者说：
【只有在列1和列2和列3的值都相同的才会被分到一个组】
1.6: 对组的限定: having 限定条件
不写group by 并且使用组函数:默认是把整个表分成一个小组
2,组函数:
2.1:基础:只有在分组的结果集中才能使用组函数
或者说有group by的sql语句才能使用组函数.
2.2:解释:使用组函数操作小组,每个小组会产生一个值
具体:
avg(列):求平均值 :在某个小组中列的平均值
count(列):计算记录总数 :在某个小组中列的记录总数
Max(列):求最大值 :在某个小组中列的最大值
Min(列)：求最小值 :在某个小组中列的最小值
sum(列):求和 :在某个小组中列的和
STDDEV(列)：标准差
VARIANCE(列)：方差
2.3:理解组函数：
插入数据:
insert into s_emp(id,last_name,dept_id) values(123,'Patel',42);
commit;
eg: 查询 41号部门的平均工资。
select dept_Id,avg(salary)
from s_emp
where dept_Id = 41
group by dept_Id;
eg: 查询平均工资大于1000的部门
s_emp
avg(salary)
`select` dept_id,avg(salary),count(*),max(salary),min(salary)
from s_emp
group by dept_id
having avg(salary) > 1000
2.4:得到结论：
如果使用了组函数,那么在select中的列，
必须是group by 列 不然查询不到。
2.5:对表的多个列进行分组:
eg:对last_name,和工资进行分组
查询每个小组的人数
select last_name,salary,count(id)
from S_emp
group by last_name,salary;
eg:查询员工工资，和员工部门名
每个部门的平均工资
select salary,name,avg(salary)
from s_emp e,s_dept d
where dept_id = d.id
group by dept_id,salary,name;
解释:对last_name和salary进行分组，如果有某几条数据last_name和salary值相同那么这几条数据就会被分到同一个组。
代码执行顺序:
3.1:顺序:from 表 a--->where-->
group by分组-->执行组函数-->
having筛选->select-->order by
3.2:组函数出现的位置 :
select字句 having字句 order by字句
Having子句：使用having（先分组后过滤）
1.行已经被分组 2.使用了组函数 3.满足having子句中条件的分组将被显示
group by
group by 是分组函数，是把by后面的数据进行分组，如果后面是一个字段，就把一个字段当成一组，如果是两个字段就把两个字段当成一组，以此类推。
having子句和group by一起使用，紧跟在group by的后边，having子句可以使用结果集中的列，也可以使用聚合函数(max、min、count、sum、svg)。having的作用是对分组后的结果进行过滤。
这里在提一下where，where是对每一行进行过滤的，查询出符合where条件的每一行。having是对查询出结果集分组后的结果进行过滤。（先过滤再分组）
————————————————
where 和having的区别：where后面不能使用组函数，having可以
组函数练习:
4.1:查询所有员工的平均工资?
【特例:把表中所有数据分到一个组不用写group by】
select avg(salary)
from s_emp
4.2:查询每个部门的平均工资?
select dept_Id,avg(salary)
from s_emp
group by dept_Id;
4.3:查询平均工资大于1500的部门?
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary) > 1500;
4.4:不能在GROUP BY子句中使用select列别名。
select
from
where
group by
having
order by
4.5:查询每个部门的平均工资并且显示出部门的名字和部门id？
select avg(salary),name,dept_id
from s_emp e,s_dept d
where dept_id = d.id
group by name,dept_id;
4.6:查询平均工资大于1400的部门id,并且显示出部门的名字？
select name,dept_Id,avg(salary)
from s_emp e,s_dept d
where dept_Id = d.id
group by name,dept_Id
having avg(salary) > 1400
4.7:查询title中不包含vp字符串的每个职位的平均薪水，
并对平均薪水进行降序排列，
并且每个职位的总薪水大于5000？
select title,avg(salary)
from s_emp
where lower(title) not like '%vp%'
group by title
having sum(salary) > 5000
order by avg(salary) desc;
4.8:查询s_emp表中最大工资数,并且显示出这个人的名字？
select max(salary)
from s_emp
2500
select id,last_name,title
from s_emp
where salary = 2500;
select id,last_name,title
from s_emp
where salary = (select max(salary) from s_emp);
子查询
1,定义 : 一条sql语句嵌套一个或者多个sql语句
强调from后面的子查询
1,把查询一的结果集(多行多列)当做表 进行查询
查询一:
select id,last_name,salary,rownum
from s_emp
where id<15
select id,last_Name
from ()
where id > 4
2,把查询一的结果集(多行一列)当做当前表的限定条件
查询一:
select salary
from s_emp
where salry > 1000
select id,last_name,salary
from s_emp
where salary in ()
3,把查询一的结果集(一行一列)当做当前表的限定条件
查询1:
select max(salary)
from s_emp
select id,last_name
from s_emp
where salary = ();
2,格式 :
select *
from (查询一) 查询一别名
select *
from 表
where id = (查询一)
3,什么情况下使用：比较符中参数不确定的情况
4,子查询出现的地方：
1）from 子句
2）where 子句
3) having
5,子查询练习:
步骤一 ： 将复杂查询分解为多个简单的查询
步骤二 ： 使用子查询将多个简单的查询融入到一个sql中
5.1:查询工资比[41号部门平均工资]高的员工信息
select avg(salary)
from s_emp
where dept_Id = 41
select id,last_name,salary
from s_emp
where salary > (1247.5)
结果：
select id,last_name,salary
from s_emp
where salary > (select avg(salary)
from s_emp
where dept_Id = 41)
5.1.1: 41号部门平均工资
5.1.2:查询工资比(1247.5)高的员工信息
5.2:查询工资比[NewMan所在部门]平均工资高的员工信息
1,NewMan所在部门-->31
select dept_id
from s_emp
where lower(last_name) = 'newman'
查询工资比[31]平均工资高的员工信息
2,31 平均工资 --> 1200
select avg(salary)
from s_emp
where dept_Id = ()
结果：查询工资比1200高的员工信息
select id,last_name
from s_emp
where salary > (select avg(salary)
from s_emp
where dept_Id = (select dept_id
from s_emp
where lower(last_name) = 'newman'));
5.3:查看部门平均工资大于32号部门平均工资的部门id
1,32号部门平均工资 --> ()
select avg(salary)
from s_emp
where dept_Id = 32
查看部门平均工资大于()的部门id
select dept_Id
from s_emp
group by dept_id
having avg(salary) > ();
结果：
select dept_Id
from s_emp
group by dept_id
having avg(salary) > (select avg(salary)
from s_emp
where dept_Id = 32);
5.4:查询平均工资大于41号部门平均工资的所有部门中的员工信息
1，41号部门平均工资 --> (1200);
select avg(salary)
from s_emp
where dept_Id = 41
查询平均工资大于(1200)的所有部门中的员工信息
2,平均工资大于(1200)的所有部门--> (30,31,32)
select dept_id
from s_emp
group by dept_Id
having avg(salary) > ()
查询(30,31,32)部门中的员工信息
select id,last_name,salary,dept_Id
from s_emp
where dept_id in ()
结果:
select id,last_name,salary,dept_Id
from s_emp
where dept_id in (select dept_id
from s_emp
group by dept_Id
having avg(salary) > (select avg(salary)
from s_emp
where dept_Id = 41))
5.5:查询出工资比Ngao的经理的薪水高的员工
select m.salary
from s_emp e,s_emp m
where e.manager_id = m.id
and e.last_name = 'Ngao';
select id,last_name,salary
from s_emp
where salary > ()
结果
select id,last_name,salary
from s_emp
where salary > (select m.salary
from s_emp e,s_emp m
where e.manager_id = m.id
and e.last_name = 'Ngao');
5.6:利用子查询做分页:
eg:每页显示3条数据 显示 第2页的数据
select id,last_name,salary,rownum r
from s_emp
where rownum <= 3*2
select id,last_name,salary
from () z
where z.r > 3*(2-1)
结果:
select id,last_name,salary
from (select id,last_name,salary,rownum r
from s_emp
where rownum <= 3*3) z
where z.r > 3*(3-1)
运行时变量
1,定义:在sql语句执行时动态添加进去的数据,
2,语法: 使用关键字 &变量名
3,书写位置：
select &变量名
from &变量名
where &变量名
select &col1,&col2
from s_emp;
select id,last_name,salary
from (select id,last_name,salary,rownum r
from s_emp
where rownum <= 3*&page) z
where z.r > 3*(&page-1)
建表语句:
drop table t10;
create table t10(
id number not null unique,
name varchar2(20) constraint name_unique unique,
gender varchar2(20),
age number(2)
);
建表
1,语法:
create table 表名(
列名 列的数据类型 [default] ‘默认值’ [column_constraint], //列的约束
…
[table_constraint]
);
2,五种约束：
1,not null :不为空 :只能存在列级约束
2,unique :唯一 :定义在表级约束或者列级约束都可以
3,primary key :主键 :定义在表级约束或者列级约束都可以
4,foreign key :外键 :定义在表级约束或者列级约束都可以
5,check :检查约束条件 :定义在表级约束或者列级约束都可以
3,两种约束级别：
3.1:列级约束：在每一列后面加约束
语法规则：[constraint 约束名字] 约束类型(五种) 约束类型(五种)
语法规则: 约束类型(五种) 约束类型(五种)
eg:
create table s_rank(
id number constraint s_rank_id_pk primary key,
minsal number not null,
maxsal number,
name varchar2(7)
);
3.1:表级约束：表最后
语法规则：用constraint
constraint 约束名字 约束类型(unique(列,列...)/primary key(列)/foreign key/check)
eg:
create table s_rank(
id number ,
minsal number ,
maxsal number,
name varchar2(7),
constraint s_rank_b_id primary key(id)
);
4,五种约束详细解析：
4.1:NOT NULL//修饰列
4.1.1:解释:限制取值不为空
4.1.2:确保该列不允许空值
4.1.3:只能在列约束级别上定义
drop table student;
create table student(
id number(7) not null,
name varchar2(50) constraint student_name_nn not null,–为了起名有意义：表名_列名_约束条件
gender varchar2(10) default ‘male’
);
insert into student(id,name) values(1,NULL);
4.2:unique
4.2.1:指定一列或列组中任何两行都不能有相同值
4.2.2:允许空值(多列空值不同)
4.2.3:定义在表级别或者列级别都可以
4.2.4:列级约束:
drop table student;
create table student(
id number(7) constraint student_id_un unique,//添加了一个列级约束
name varchar2(50) unique
)
insert into student(id,name) values(1,NULL);//插入成功
insert into student(id,name) values(2,'tom');//插入成功
insert into student(id,name) values(2,'tom2');//插入失败
insert into student(id,name) values(3,'tom2');//插入失败
4.2.5:表级约束:
drop table a;
create table a(
id number(7),
name varchar2(50),
constraint abcdsss unique(id),
constraint nsdfsdf unique(name)
)
insert into student(id,name) values(1,NULL);//插入成功
insert into student(id,name) values(11,NULL);//插入成功
insert into student(id,name) values(2,'tom');//插入成功
insert into student(id,name) values(2,'tom2');//插入失败
insert into student(id,name) values(3,'tom2');//插入失败
4.3:primary key
4.3.1:一个表中只允许一个主键
4.3.1:非空且唯一
4.3.1:可以定义表级别可以定义列级别
4.3.1:列级约束:
drop table p;
create table p(
id number primary key,
name varchar(10)
);
create table p(
id number,
name varchar(10) constraint myprimary primary key
);
4.3.1:表级约束:
drop table p;
create table p(
id number,
name varchar(10),
constraint myprimarykey primary key(id)
);
4.3.1:联合主键:
4.3.1.1:当前表使用两个列或者更多列当做主键
4.3.1.2:联合主键创建到表级约束上
drop table wp;
create table wp(
id number(7),
name varchar2(50),
constraint wp_idAndName_pkw primary key(id,name)
);
insert into wp(id,name) values(1,'tom');//成功
insert into wp(id,name) values(1,'tom1');//成功
insert into wp(id,name) values(2,'tom');//成功
insert into wp(id,name) values(1,'tom');//失败
4.4:foreign key
4.4.1:声明外键,和另一个表主键进行关联的列
4.4.1:要么为空，要么为另一张表的主键
4.4.1:列级约束
先建立主表（被外键所引用的列所在表）
drop table a;
create table a(
id number primary key,
name varchar2(20) unique not null
);
后建立外键所在的表
drop table b;
create table b(
id number primary key,
name varchar2(10),
a_id number constraint a_fk references a(id)
);
create table b(
id number primary key,
name varchar2(10),
a_id number references a(id)
);
4.4.1:表级约束:
drop table b;
create table b(
id number primary key,
name varchar2(10),
a_id number,
constraint fk_a foreign key(a_id) references a(id)
);
注意：联合外键引用的另一张表的必须是联合主键,并且本表中的两列需要与另表中的数据类型相同
4.4.2: on delete cascade: 删除父表中的数据，把关联的子表数据一同删除
在声明外键列的时候加入on delete cascade语句
create table abc(
id number,
a_id number,
constraint id_pk primary key(id),
constraint aid_fk foreign key(a_id) references a(id) on delete cascade
);
4.4.3: on delete set null: 删除父表中的数据，把关联的子表数据设置为null
在声明外键列的时候加入on delete set null语句
create table abc(
id number,
a_id number,
constraint id_pk primary key(id),
constraint aid_fk foreign key(a_id) references a(id) on delete set null
);
4.5:check
4.5.1:意义:对某一个列输入的值做限制
4.5.1:语法:constraint 约束名 check(逻辑表达式)
drop table abc;
create table abc(
id number check(id > 4)
);
eg:ch表gender列的值只能是男或女
eg:ch表的age列需要在20-90之间
eg:ch表name列长度只能在3-6之间
eg:ch表name列前两位只能输入 ab
DML语句 insert delete update
特点：执行DML语句的时候会产生事务(commit后修改的数据才会生效)
insert:插入数据
语法：
insert into 表 values(表中每列的值);----> 这条语句没有真正的对数据库产生影响(改变)
insert into 表(列1,列2,列3) values(列1对应数据1,列2对应数据2,列3对应数据3); 给表中的某几列添加值
insert进阶:插入的数据是从一张表中查询得到
语法:insert into 表(列1,列2,列3…) select 列1的值,列2的值,列3的值 from 表2 where 条件 group by 分组 having 分组约束 order by 列排序;
测试:
基础表:
drop table t1;
create table t1(
id number,
name varchar2(20)
);
insert into t1 values(1,‘tom1’);
insert into t1 values(2,‘tom22’);
insert into t1 values(3,‘tom333’);
insert into t1 values(4,‘tom4444’);
insert into t1 values(5,‘tom55555’);
测试表：
drop table t2;
create table t2(
id number,
name varchar2(20),
age number,
email varchar2(20)
);
测试开始:
向t2表中保存数据,数据t1表中的列
update语句 更新数据
语法：
update 表
set 列1=新值1,列2=新值2…
where 限定条件
需求:向s_rank表中添加一条数据
1,更新s_rank表中id为14的一条数据把minsal修改为2000
2,普通人 -》 一般人
update进阶:更新的数据是从一张表中查询得到
语法:update 表1
set (列1,列2,列3) = (select 列一的值,列二的值,列三的值 from t1 where 表1.id = 表二.id)
where id < 3;
eg:
update test
set (id,name) = (select id,last_name from s_emp where test.id = s_emp.id)
where id < 10;
delete删除表中的某几行数据:删除某一张表中的某一行或者某几行数据
delete 表 where 限制条件
delete s_rank;
eg:
删除s_rank表中id为14的信息
delete s_rank where
on delete cascade
建表语句和测试数据上例1相同,只是在声明外键列的时候加入on delete cascade语句
create table abc(
id number,
a_id number,
constraint id_pk primary key(id),
constraint aid_fk foreign key(a_id) references a(id) on delete cascade
);
on delete set null
建表语句和测试数据上例1相同,只是在声明外键列的时候加入on delete set null语句
create table abc(
id number,
a_id number,
constraint id_pk primary key(id),
constraint aid_fk foreign key(a_id) references a(id) on delete set null
);
DDL语句:create(创建), alter(修改), drop(删除), rename(重命名), truncate(删除表中的数据)【提交事务】
create:
####1,创建表：
create table 表名(
列名 列的数据类型 [default] ‘默认值’ [column_constraint], //列的约束
…
[table_constraint]
);
create table s_rank(
id number,
minsal number,
maxsal number,
name varchar2(7)
);
2,创建序列:一般使用序列充当主键
create sequence 序列名
[increment by n] 每次增加几 默认：1
[start with n] 从哪开始 默认：0
[{maxvalue n | nomaxvalue}] 最大值 默认：n
[{minvalue n | nominvalue}] 最小值 默认：0
[{cycle | nocycle}] 循环：在取值达到最大值的时候就循环 默认：不循环
[{cache n | nocache}] 缓存，把序列的值放到缓存中，提高效率，缓存这个个数不能大于最大值 默认：没有
create sequence 序列名;//简单创建序列 所有都默认 也可以
eg:
create sequence myseq123
increment by 3
start with 0
maxvalue 12
minvalue 0
cycle
cache 4;
序列名:myseq123
步长：3
从0开始
最大值12
最小值0
循环
缓存4个数据
0 3 6 9 12 0 3 6 9 12-----
序列的属性:
每个序列对象有俩个属性:
nextval 返回序列中的下一个值
currval 序列中的当前的值 ---> 刚创建出来的序列是没有当前值 会报错----->session（控制台），先执行nextval以后才能使用currval
序列属性的调用:
序列名.属性
eg: myseq.nextval
myseq.currval
eg:动态参数+序列:插入多条数据;
insert into test(id,name) values(test_seq.nextVal,'&name');
3,创建视图(view)：
语法: create view 视图名
as
子查询
create view s_view
as
select id,last_name,salary
from s_emp
####3视图view
**定义:**视图是一条查询的sql语句，用于显示一个或多个表或其他视图中的相关数据。
视图将一个查询的结果作为一个表来使用，因此视图可以被看作是一个虚拟表.
视图基于的表称为基表。
好处:
用户可以通过视图以不同形式来显示基表中的数据。
通过视图可以设定允许用户访问的列和数据行，从而为表提供了额外的安全控制。
视图就对用户隐藏了数据的来源。
创建视图:
前提：当前用户需要有创建序列的权限
授权:给用户授予创建序列的权限(需要管理员用户才可以)
授权语法:grant create view to 用户名;
方式一:
create view s_emp_view
as
select id,last_name,salary from s_emp(子查询)
方式二:如果视图名已经存在，那就会先删掉再创建
create or replace view s_emp_view
as
select id,last_name,first_name,salary from s_emp
with read only;
查看视图:
select 列,… from 视图名
删除视图:
drop view 视图名;
更新视图：
update 视图名 set 列名=’’
where 条件;
视图只读:with read only; 加在创建视图的最后一行。
create or replace view s_view
as
select last_name,salary,dept_id
from s_emp
with read only;
视图限制dml操作:with check option,加在创建视图的最后一行
特点:DML的结果满足该创建的视图中的where子句条件，DML就可以正常进行
//测试用的表及其数据
drop table t_user;
create table t_user(
id number constraint user_id_pk primary key,
name varchar2(100),
salary number
);
insert into t_user values(1,‘tom’,1000);
insert into t_user values(2,‘tom2’,2000);
创建视图:
create or replace view v_test
as
select id,name,salary
from t_user
where id <6
with check option;
//插入报错 插入的数据不符合 创建视图时的where条件
insert into v_test values(10,‘tom3’,3000);
//更新失败 更新以后的结果不符合 创建视图时的where条件
update v_test
set name=‘lily’
where id=1;
简单视图: 视图所代表的sql中如果没有group by语句,没有组函数,查询的只有一张表,那么这样的视图就是简单视图.
1, 对简单视图进行增删改查会影响基表中的数据.
4,创建索引:
1,概念:
1. 类似书的目录结构
2. Oracle 的"索引"是一种对象，是与表关联的可选对象，能提高SQL查询语句的速度
3. 索引直接指向包含所查询值的行的位置，减少磁盘I/O
4. 索引和表是相互独立的物理结构
5. Oracle 自动使用并维护索引，插入、删除、更新表后，自动更新索引
2,自动创建
当在表中指定了primary Key或者unique约束时会自动创建唯一值索引。
3,用户创建。
用户可以创建非唯一值索引以提高在访问数据时的效率。
格式:
create index 索引名
on 表名 (列名);
eg:
create index emp_index
on s_emp(last_name);
4,查看当前用户的索引
select index_name from user_indexes;
5,给某列创建索引的原则：
1.列经常作为where子句的限定条件或者作为连接条件
2.列包含的数据量很大，并且很多非空的值。
3.两个或者更多列频繁的组合在一起作为where的限定条件或者连接条件
4.列总是作为搜索条件
5.索引查出的数据量占2%~4%
6.索引不是越多越好，不是索引越多越能加速查找。
7.要索引的表不经常进行修改操作
5,创建用户：
create user 用户名 identified by 密码;
没有权限
alter:
只能操作列
1,添加列：
alter table 表名
add(列名 列的数据类型 列的约束条件)
alter table test
add(gender varchar2(20) not null)
2,删除列：
alter table 表名
drop column 列名;
alter table test
drop column last_name;
3,改变数据类型：
alter table 表名
modify(老列名 新的数据类型(12) 列的约束条件);
alter table test
modify(age number(20,2) not null)
【4,添加列的唯一性约束：**
alter table 表名
add constraint 约束名 约束类型(列名);**】
alter table test
add constraint testa_id_pk primary key(id)
5,删除表中的约束
alter table 表名 drop constraint 约束名;
alter table test drop constraint testa_id_pk;
eg:修改那么的约束为 非空unique 约束
alter table 表名 modify(列名 约束类型);
alter table test
modify(name unique);
drop:
1,删除表：
drop table 表名;
2,删除序列:
drop sequence 序列名;
3,删除视图：
drop view 视图名;
4,删除索引:
drop index 索引名;
5,删除用户:
drop user 用户名 cascde;
rename:
1,修改表的名字
rename 旧表名 to 新表名;
rename test to newtest;
2,修改表中列的名字
alter table 表名
rename column 旧列 to 新列
alter table newtest
rename column dob to newdob;
truncate:
1,删除表中所有数据:truncate table 表;
2,truncate和delete作用一样,也是删除表中数据（只有要删除表中所有数据的时候truncate才和delete等价）
3,delete是DML语句,删除数据后需要自己手动的提交事务或者回滚事务
4,truncate是DDL语句,删除数据后会自动的帮我们提交事务
TCL语句:commit(提交), rollback(回滚), savepoint(记录点,用来回滚)
###事务 : 由一组DML语句组成: dml --> insert delete update （一个装sql语句的容器）
1,定义 : 指的是一组操作对数据库的数据真正的影响,这一组操作中有一个操作失败了 这一组事务就全失败了;
2,开启事务:执行第一条dml语句,接下来所有的dml语句都会放到事务中。
3,事务的提交:commit和DDL语句都会提交事务 exit；退出关闭控制台
背:
4,事务的特点:ACID（A原子性、C一致性、I隔离性和D持久性）背
Automicity（原子性）：原子是不可分割的，同一事务中的一组操作也是不可分割的，事务同时成功或者同时失败
Consistency（一致性）：事务开始之前数据库处于一致的状态，事务结束以后数据库也处于一致的状态
Isolation（隔离性）：一个事务的执行不会影响到另一个事务；oracle默认隔离级别是read commited（提交读，提交后才能读）
**Durability（持久性）：**一旦事务被提交，它对数据库的影响是持久的
5,commit;提交事物,对数据库产生永久的影响。没有提交前的数据在当前的session缓存中可以被查询。
commit;ddl ; exit;(退出);
6,exit; 退出sqlplus控制台,会提交事物,点击关闭 也会提交事务(有的版本会)
7,rollback； 回滚全部事务,当前的一组操作失败了，把这一组操作都返回到原始状态.
8,savepoint 保存点; 标记保存点,可以回滚到标记点。
9,rollback to 保存点名; 回滚到某标记点
10,测试事务:
create table c(name varchar2(20));
提交:
insert into c values(‘tom1’) 产生一个事务A
insert into c values(‘tom2’) 把这个操作放进已经存在的事务A里面
updete c set name = ‘jary’; 把这个操作放进已经存在的事务A里面
delete c where name =‘tom1’ 把这个操作放进已经存在的事务A里面
commit; 提交事务:让前面的DML操作都生效
回滚:
insert into c values(‘tom1’) 产生一个事务A
insert into c values(‘tom2’) 把这个操作放进已经存在的事务A里面
updete c set name = ‘jary’; 把这个操作放进已经存在的事务A里面
delete c where name =‘tom1’ 把这个操作放进已经存在的事务A里面
rollback; 回滚事务:让前面的DML操作都撤销
保存点:
insert into c values(‘tom1’); 产生一个事务A
insert into c values(‘tom2’); 把这个操作放进已经存在的事务A里面
insert into c values(‘tom3’); 把这个操作放进已经存在的事务A里面
insert into c values(‘tom4’); 把这个操作放进已经存在的事务A里面
savepoint a_point;
update c set name=‘jary’
where name=‘tom3’; 把这个操作放进已经存在的事务A里面
delete c where name =‘tom1’; 把这个操作放进已经存在的事务A里面
savepoint b_point;
insert into c values(‘tom5’); 把这个操作放进已经存在的事务A里面
insert into c values(‘tom6’); 把这个操作放进已经存在的事务A里面
回滚到:
rollback to 保存点;
eg: rollback to a_point;
11:事务隔离级别 isolation ,了解
事务并发访问中产生的问题:
1.脏读
主要针对update操作。 一个事务A读到另一个事务B中修改过但是还没有提交的数据
2.不可重复读
主要针对update操作。 一个事务A在第一次读数据和第二次读数据之间,有另一个事务B把这个数据更改并提交了,所以就出现了事务A里面读一个数据俩次,但是读到的结果是不同的。
3.幻读
主要针对的是insert/delete操作。事务A第一次用where条件筛选出了10条数据,事务A第二次用通样的where条件筛选出的却是11条数据,因为事务B在事务A的第一次和第二次查询直接进行了插入操作,并且插入的这个数据满足事务A的where筛选条件.
第十六章: 用户权限控制
system /oracle
1.创建用户
create user user_name identified by password
例如:
create user zhangsan identified by zhangsan;
2.删除用户
drop user zhangsan cascade;
3.赋予权限
grant 权限 to user;
例如:把建表 建序列 建视图的权限赋给zhangsan
grant create table, create sequence,create view to zhangsan;
//把connect角色和resource角色赋给zhangsan
//角色是一组权限的集合
grant connect,resource to zhangsan;
注意: 只是登陆oracle数据库的话 需要的权限是create session
4.修改密码
alter user user_name identified by password;
例如:
alter user zhangsan identified by zhangsan123;
5.赋予某一个用户某种对象操作的权限
grant 操作[select/delete/update/insert] on 对象名,对象名[表名...] to 用户;
6.回收权限
revoke 操作[select/delete/update/insert] on 对象名,对象名[表名…] from 用户;
例如:
revoke insert,select
on s_view
from tom;
7,数据库的导入导出
系统终端执行
导出：exp 根据提示按回车下一步即可
导入：imp 根据提示按回车下一步即可
导出：数据表和表中数据
数据字典:dictionary
*,查询用户下所有的表
select table_name from user_tables;
*,查询用户下所有的视图
select view_name from user_views;
*,查询用户下所有的序列
select sequence_name from user_sequences;
https://www.cnblogs.com/helong/articles/2086399.html
7.创建同义词synonym
作用:可以隐藏表原来的名字
分为:私有同义词 公共同义词
//给表t_user创建一个私有同义词
create synonym my_test for t_user;
create synonym abcds for s_emp;
grant create synonym to briup;
//给用户briup授权可以查询my_test
grant select
on my_test
to briup;
//收回用户briup查询my_test的权限
revoke select
on my_test
from briup;
//利用数据字典查看同义词synonyms
//用户创建的同义词有哪些
select synonym_name
from user_synonyms;
//用户有权利查询的同义词有哪些
select synonym_name
from all_synonyms;
//用户有权利查询的同义词有哪些是以字母D开头的
//注意:表中的数据都是大写存在
select synonym_name
from all_synonyms
where synonym_name like ‘D%’;
结果可以看到我们常用的dual
8.删除同义词synonym
drop synonym name;
例如:
删除私有同义词
drop synonym my_test;
删除公共同义词
drop public synonym my_test;
9.公共的同义词
因为普通用户没有创建public synonym的权限,所有我们需要用dba的身份登录到数据库中去创建。
sqlplus “/as sysdba”
或者system用户登录
create public synonym my_test2
for test.t_user;
//在管理员权限下创建公有同义词
//同义词名:abc
//同义词来自的表 是briup下的test_abc表
//所有用户都可以访问同义词
create public synonym abc
for briup.test_abc;
//让所有人都有查询这个同义词的权限
grant select on abc to public;
然后其他用户登录之后就可以通过这个公共的同义词来查询test用户下面的t_user表了`
1613
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
