javaEE--------Oracle数据库基础
一. Oracle数据库概述
什么是Oracle？Oracle是甲骨文公司出品的大型关系型数据库系统。
它在数据库i领域一直处于顶尖的定位
Oracle的发展？
1.1970年，Oracle问世的理论基础，来自IBM研究员的关系型数据库理论论文。
2.1977年，Oracle创始人ELLISON(曾在美国三所大学辍学)正式成立ORACLE的前身软件研发工作室，
3.找了员工号为1号员工，名字叫做SCOTT，后来为了奖励他的贡献，ORACLE的版本都有内置一个SCOTT演示用户账户。
4.1979年，迎来了第一个商业用户，美国中央情报局
5.1983年，正式更名为ORACLE(希腊语：神谕、预言含义)，未来高速发展称为数据库世界第一
开发者如何通过SQL命令操作Oracle？
Oracle SQL完全符合SQL标准，通过SQL可以通信Oracle。
Oracle SQL分类和关键字
数据定义语言：create 创建 alter 更改 drop 删除 对组件的控制
数据操作语言：insert 插入update 更改 delete 删除 select 查询 对组件中数据的控制
事务处理语言：commit 提交 savepoint 保存点rollback 回滚
数据库控制语言：grant 授权 revoke 取消授权
除此之外，还有Oracle扩展的数据库编程语言PL/SQL
Oracel的运作环境及工具？
ORACLE分为服务器端和客户端
服务器端：负责存储数据和管理数据
客户端：通常发送用户编写的SQL命令给服务器，执行相应的数据库操作
客户端工具，通常会有2个工具，标配。
1.SQL Plus 命令行工具
SQL Plus是一种交互式，批处理的查询工具，是最基本的数据库工具，通过一个基本的命令行接口，发送命令至Oracle服务器
如何远程连接Oracle
a. sqlplus /nolog 匿名登录 此时没有权限
b. connect jsd1705/[email protected]:1521/ora10g
connect 用户名/密码@服务器所在机器IP地址：监听端口/数据实例名
or
jsd1705
jsd1705
2.SQL Developer 图形工具
Oracle SQL初体验
DML 数据库操作语言
select 用来操作查询的主关键字
insert 用来操作插入数据的主关键字
update 用来更新数据的主关键字
delete 用来删除数据的主关键字
DDL 数据库定义语言
create 用来创建表的主关键字
1.如何创建一个简单的表
name String --> Oracle String varchar2
create空格table空格自定义数据库名(字段名称1 字符类型(length)，字段名称2 字符类型);
——建议字母打头，不要有特殊符号
Table t = new Table();
t.id=1;
t.name=jian;
t.age=17;
t.salary=50000;
例子：
1.创建表
create关键字 创建组件的对象 名字
create table jian_001(
id varchar2(20),
name varchar2(20),
age varchar2(20),
salary varchar2(20)
);
2.查询表
select * from 表名 简单一个查询该表的命令
3.如何插入一条数据
语法：
insert into 表名(字段1,字段2,字段3..)
value(值1,值2,值3...);
例子：
insert into jian_001(id,name,age,salary)
values('1','黄鳝','57','50000');
commit; //数据提交后才能生效，否则只在当前会话有效
4.如何删除一条数据
delete from jian_001 where id='5';
delete from jian_001 where age='18';
二. Oracle基础
ORACLE表操作定义
关系型数据库是以表作为存储的关键组件，表是由多行多列组成，如excel的表格行列
一行表示一条记录，一列表示一列字段
如何创建表 SQL DDL
SQL DDL 数据定义语言：create创建 alter 修改 drop 删除
a 创建一张表
create table 表名(字段1 字段类型(字段长度) ,字段2 字段类型(字段长度) ...);
查询新建表的情况
1.select * from 表名
2.desc 表名字
了解字符串java string --> oraclevarchar2(str.length)
demo1 -
b 如何删除一张表
语法：drop table 表名
c.修改表
修改表的列名
语法：alter table 表名 rename column 原列名 to 新列名
命名规则：数字不打头，不用中文，不用特殊符号
修改表名：
语法：alter table 表名rename to 新表名
注意事项：
1.正常执行并完成的DDL语句，create drop alter，Oracle会自动提交事务，自动执行commit；
2.事务主要针对于数据库表中的数据
{假设是当前某一个数据库}
3.SQL标准：表名只能用一次，不能重复
Oracle标准：同一个用户下表名不能重复
4.建议初学者遵守SQL标准，在任何情况下都不要创建同名的表；
(1)可以更好的和别的数据库对接和学习，比如MySQL
(2)等熟练ORACLE，更清晰理解ORACLE独有表空间，用户权限，表，同名表之间的逻辑关系，再尝试
如何管理新建表中的数据。ORACLE SQL DML
数据管理语言 select insert update delete
1.查询
select * from 表名; 全部数据
select 列名 from 表名; 某一列数据
select 列名,列名... from 表名; 某几列数据
select 列名 as 别名,列名 as 别名... from 表名; 列名称变成别名
2.插入数据
insert into 表名values(值1，值2...);全部列名的插入数据
= insert into 表名(列名1,列名2...) values('值1','值2',..);
事务提交命令
commit;
执行后，当前操作的数据会永久的保存在数据库文件当中
数据持久化
场景：
20日上午 10:00 登录数据库
10：30 操作了n张表，分别往n张表里面各插入了n条记录
10：31 执行commit；
结果：10:00-10:30 数据操作永久保存更新在数据库中。
若 10:31 未提交，断电电脑关闭
结果： 10:00 -10:30 期间DML操作的数据均不保存。
丢失更新
3.如何更新数据？
语法：update 表名 set 列名 = 新值
where条件(某列名=特指的值);
黄晓明夫妇 --> 杨幂夫妇
黄鳝的皇后 --> 老太君
4.如何删除数据？
语法：delete 表名
where 条件(某列名=需要选择删除的值);
老太君删除;
(1) 删除是删除符合条件的表记录(表行),条件语句类似于JQ的选择器，只要符合条件均属于删除范围。
(2) 是否提交? 实验数据写完，一律提交。讲数据永久保存在我们的数据库中。
(3) 如果出现同名，只想删除其中一个
方法1：改变删除的条件。 name='' --> id='3';
方法2：增加删除的条件。 (SQL逻辑运算符) name='h' and id='3'
复合条件 and 且运算符同时满足AND两边条件
ORACLE事务什么是事务
TPL Transaction Process Language
事务是指作为单个逻辑工作单元执行的一组相关操作
简单一点，一个商业交易业务完成
事务工作 就是确保一个商业交易中更新的数据要么全部提交要么全部不提交
数据库使用事务的原因
保证数据的安全性，数据商业应用数据正确性
事务的四个特性ACID
1. 原子性 Atomic 0 or100 有or没有
事务中所有的数据修改，要么全部执行，要不就全不执行
2.一致性 Consistence
事务完成时，要使所有的数据都保持同步一致的状态
简单说来：数据商业应用中某一个业务进行时，所有的数据修改，必须在所有的相关表中得到反映。
3.隔离性 Isolation
事务应该在另一个事务对数据的修改前或修改后进行访问。事务与事务是同步的。
4.持久性 Durability 保证事务对数据库的修改是持久有效的。即使发生系统故障，也不应该丢失数据。
ORACLE事务的分类(重点)
A 显式事务
1. 用commit命令明确数据保存至数据库文件中
2. 工具操作提交事件
B 隐式事务
1.正常执行DDL语句 create alter drop
2.正常执行DCL语句 grant revoke
关键字
1. commit 提交数据，数据持久化
2. rollback 回滚数据，对于未提交的数据进行撤销
注意：一旦调用commit,rollback无法回滚
3. savepoint 保存记录点
savepoint 后面接的参数不能是纯数字
如何实现事务管理
1.全部回滚
lab8:
insert into a record1;
insert into a record2;
insert into a record3;
insert into a record4;
insert into a record5;
rollback;
2.局部回滚
lab6:
insert into a record1;
insert into a record2;
savepoint point1;
insert into a record3;
rollback to savepoint point1;
lab7:
insert into a record1;
insert into a record2;
savepoint point1;
insert into a record3;
insert into a record4;
savepoint point2;
insert into a record5;
rollback to savepoint point1;
3.事务提交
insert into a record1;
insert into a record2;
insert into a record3;
commit;
4.多表事务控制模拟
素材：员工工资表，个人资产表
a.甲取得工资
b.将工资存入个人的资产表中
c.借钱给乙
d.乙成功借的钱，存入个人资产表中
ORACLE基本数据类型
字符串类型
定义
1. char：是固定长度的字符串
所谓固定长是指虽然输入字段值小于该字段限制得到长度
在实际保存至数据表中，会先自动向右补齐空格，然后存入表中
2. varchar2：是可变的字符串
所谓可变长度字符串是指输入的字段值小于该字段的限制长度，直接将字段保存，不补足空格，比较灵活
Oracle推荐使用varchar2
语法
varchar2(1-4000)
char(1-2000) 默认1
例子：
char(10)
vachar2(20)
同样输入abc，前者abc+7个空格，后者abc 3个字符
参数10，是限制该字段最大的字节数
计算字节，含英文和中文计算字字节长度和数据库编码集有关
GBK GB2312 支持中文数字，英文字母各占1个，中文占2个
UTF-8 数字，英文字母各占1个，中文占3个
select userenv("language")fromdual;
//获取当前数据库的安装文字编码集
建议：设置varchar2字段的长度时，通常三倍计划值
length() 计算字符的个数
select length('中国复活节2')from dual;
select length(name),length(salary) fromzengjian_test_01;
数字类
number数据类型可以存储整数和浮点数
number == java int+double
语法：number(p,s)
参数p：表示数字的有效位数
参数s：表示小数的有效位数
*p 有效位数 - s小数的位数 = 一个浮点数的整数的有效位数
number(5) 50000 没有s
p-s = 5-0=5 整数位
number(11,2)
p - s= 11-2=9
number默认最大值(38,0)
lab12
定义规则(重要)：
1. number(p)表示整数，且整数位最多是p位
2. number(p,s)整数位超出报错，小数位超出四舍五入截取
s>0 对于小数点右侧最多保留S位小数位
s=3 88.8888 --> 88.889
s=0 小数点是四舍五入进整数位，小数部分截取
88.8 --> 89
以下两种通常不会遇到，但ORACLE有对应的处理机制
s<0 对小数点左侧S位四舍五入清0
c number(p,s) p<s是一个小于1的小数，小数位四舍五入保留s位，将小数点右侧s-p位清0
日期时间类型
1. date类型用于存储表中的日期和时间的数据
Oracel使用的是自己定义的日期时间格式
sysdate 返回当前的日期和时间
select sysdate from dual;
2. timestamp时间戳 用来存储年月日小时分钟的秒数值
其中秒数值可以精确到小数点6位数，该数据类型包含时区
select systimestamp from dual;
时间字段 有效值
Year -4712-9999 整数 1971-2200
Month 01~12
Day 01~31
Hour 0~23
minute 0~59
second 0~59
三. 表空间，用户管理，权限管理及授权操作DCL
Oracle数据库存放结构表空间，数据库实例，数据库表，数据库服务器
1、Oracle数据库
它是物理存储，这就包括系统数据库全局系统文件
含数据文件ORA或者DBF(文件后缀)，控制文件，日志文件，参数文件等
它本身可以看作就是一个大型数据库服务器
数据库实例工厂，数据库实例的操作系统平台
可以看做类似于window mac操作系统
2、数据库实例
当数据库服务器安装完成之后，会默认启动一个自带数据库实例，提示符SID，通常默认值是orcl
SID,代表着ORACLE数据库的一个实例，所谓实例就是我们通常提到的数据库的概念
比如如京东商城，这个web应用项目，通常它只会对应一个数据库实例，在ORACLE数据服务器中运行和管理
临时结论：Oracle服务器可以包含N个数据库实例，这些数据库被部署在Oracle服务器中运行与维护管理
表空间：表空间是一个用来管理数据存储的逻辑概念，表空间只是和数据文件发生关系，数据文件是物理的，存放在服务器所在电脑硬盘中，一个表空间可以包含多个数据文件而一个数据文件只能隶属于一个表空间
JSD1705班级，它本身就是一个物理概念的体现。
因为陈老师的上课需要，把1705班分成5个实验小组
这个就是逻辑概念的体现
数据表，是被保存在一个表空间。
逻辑上该表属于保存的那个表空间。
物理上数据表数据其实是被记录在1个或多个文件当中。
物理上是真实保存在物理文件。
Oracle数据服务器 -->大学
Oracle数据库实例 -->大学里院系,计算机系,金融系等
表空间-->某一个系,计算机系,大一、大二的划分
表-->学生
用户-->老师
计算机系 大一 A班 B班
大二 A班 B班
Oracle的数据库不同于其它数据库
Oracle是要有用户和表空间来管理数据表
因为数据表不是表空间去查询的，而是由用户 来查询的，这里就有了用户的的概率
临时结论：
表实际上是存放在物理的数据文件当中，当数据库实例在ORACLE
服务器中运行时，表在逻辑上属于表空间，ORACLE用户在表空间去寻找该数据表和数据。ORACLE用户是实际操作表的执行者。
例子：
Oracle服务器
实例1 实例2
30表空间 70表空间
300张表 700张表
用户 x 实例1 第8个表空间 10
Oracle用户管理
1、Oracle用户介绍
定义：当创建一个新的数据库时，Oracle将自动创建三个用户。
SYS：公司董事长
SYSTEM：公司CEO
SCOTT：公司员工，明星员工
SYS：是ORACLE的唯一个超级用户超级BOSS，是ORACLE权限最高的唯一一个用户
数据库服务器中所有的数据字典，系统文件等都在SYS权限管理中，这些数据和文件是运行ORACLE服务器的重要文件。
SYSTEM:是ORACLE数据库的系统员。它拥有DBA权限
SCOTT：Oracle自带的演示用户，定位一般用户
用QQ群来做个比喻，SYS是群主
SYSTEM是群管理员，SCOTT就是成员
用户相关的命令
显示当前用户:
select user from dual;
显示当前用户管理的表
select * from user_tables;
显示当前用户访问的表空间
select username,default_tablespace fromuser_users;
2、如何管理用户
DDL
(1) 如何创建用户
create user 用户名 identified by 密码;
(2) 如何修改用户
修改密码：
alter user 用户名identified by 新密码;
解锁：
alter user 用户名account unlock;
(3) 如何删除用户
drop user 用户名
lab 2 建设一个用户abc_pingying_(luckynumber)
1.管理员连接数据库
(1) sqlplus as sysdba 登录数据库服务器
(2) show user;确定是董事长身份
(3) alter user system identified by 新密码
霸道式修改CEO级别用户SYSTEM的密码
(4) 远程链接服务器 by sqldeveloper tool
(5) 完成连接设置
连接名：自定义名字
用户和密码：SYSTEM新密码
主机：IP地址 ORACLE服务器放在的电脑IP地址
端口号：1521
SID:要连接访问的数据库实例名字sid2
sid2 sid2
(6) 测试通过后即可连接,
若测试失败,环境问题
SYSTEM被禁用,被锁定
解锁方法：
alter user SYSTEM account unlock;
(7) 连接数据库实例成功，检测连接数据
select * from dual;
2.管理员给学生账户授权
授权DBA给学生
grant dba to 用户名;
收回DBA权限
revoke dba from 用户名;
查看授予用户的角色全县信息
select * from dba_role_privs
where t.grantee='abc10';
Oracle三种标准权限角色
1.connect角色，新员工注册报道
授予该用户最基本权利
能够链接ORACLE服务器
2.Resource角色，可以展开工作的员工
授予开发人员，能够在自己的表空间中创建表、序列、视图等数据库组件。
3.DBA角色，已经晋升为公司高管的员工
授予系统管理员级别的用户，拥有该权限的用户
可以看做是自定义的SYSTEM用户
角色查看
select * from user_role_privs;
表空间操作，用户权限分配，数据表权限分配
1 表空间操作
操作表空间，需要使用DDL语句
create drop alter
如何创建表空间
语法：
a 创建临时表空间
create temporary tablespace 临时表空间名
tempfile 'D:\JSD1705\JIAN007.dbf'
--数据文件都不是自己创建的，只需要
--在创建表空间的时候指定就可以了
--系统自动创建
size 50m --数据文件默认其实大小是50M
autoextend on -- 自动扩张，当数据文件无法存放新的数据的时候
next 50m maxsize 2048m
--需要扩张的时候,每次数据文件自增加50M
extent management local;
--设置表空间为服务器本地管理
删除表空间：
drop temporary tablespace jian666;
b 创建数据表空间
create tablespace javaspace01
datafile 'D:\JSD1705\space\JIAN007.dbf'
size 50m
autoextend on
next 50m maxsize 2048m
extent management local;
删除表空间
drop tablespace jian66601;
c 创建用户并指定表空间
create user xxx identified by xxx
default tablespace JIAN007
temporary tablespace JIAN00701;
--更换用户的表空间
--包含表空间和临时表空间
查看特定用户的表空间信息 新建用户
select username,default_tablespace,
temporay_tablespace from dba_users
where username='创建的用户名'
查看当前用户的表空间信息 JSD1705
select username,default_tablespace
from user_users;
select * from user_users;
表空间：用来进行数据库存储，所以是实际物理存储区域
临时表空间：主要在数据库实例运行中，访问表或者视图等临时操作是提供运算空间和临时存放空间。
用户退出后，临时表空间清空
数据表的权限管理
语法
DCL
grant dba to 用户
revoke dba form 用户
角色权限分配
grant select on 表名 to用户
revoke select on 表名from 用户
表管理权限分配
常用的6个表权限
all on 表名 --> 一张数据表全部权限
select on 表名 --> 一张数据表查询权限
update on 表名 --> 更新权限
delete on 表名 --> 删除权限
insert on 表名 --> 新增权限
alter on 表名 --> 修改表结构权限
grant select on scott.emp to JSD1705;
--------------------------------------------------------------------------------------------------------------------------------
总结：
1.安装一个ORACLE在本机上，实际上是安装一个ORACLE数据库服务器，它可以包含N个数据库实例。
2.SID数据库实例可以包含N个表空间
3.表空间可以包含N个数据表
4.级别结构的关系，不可逾越
ORACLE>SID>tablespace>table
5.用户通常访问一个表空间，管理员可以访问多个。
6.一张数据表只能隶属于一个表空间
7.用户是操作表空间里表的执行者，所以用户拥有数据表的拥有权。通常用户自己创建的表都是拥有的表，可以把表权限给其它用户分享。
四. SQL查询与ORACLE函数
ORACLE运算符1.算术运算符
第一组 +-*/()
案例 select 5+3 as 计算器 from dual;
select * from scott.emp where sal*>10000;
select * from scott.emp where(sal+8000)/2>=5000;
第二组 between and
定义: 查询在某一位范围内的记录
语法：where 列名 between Aand B
通常用于数值型的区间判断
案例：
select * from scott.emp where sal between2000 and 3000;
sal在2000和3000的区间，含2000和3000本身
第三组
语法 where 列名 is null is not null
select * from scott.emp where COMM is null;
select * from scott.emp where COMM is notnull;
第四组 like
定义：模糊查询，通常用于字符串
%：通配符，通常与like连用
表示0到任意多个字符
语法：where 列名 like '%'
案例：
select * from scott.emp where name like'%m'; //以m结尾
select * from scott.emp where name like 'm%';//以m开头
select * from scott.emp where name like '%m%'； //中间有m的，包含了开头和结尾的
强调 %--> 代表为0，或者空
第五组
not in 和 in
定义：列名值不在给定范围之内的 not in
列名值在给定范围的有效 in
语法：where 字段名(列名) not in/in (范围值1,范围值2,...)
字符串需要用单引号
2.比较运算符
=
!= --> <>
<
<=
>=
>
3.逻辑运算符
and or
and 并且，可以关联多个条件
但是条件必须满足访客作为查询的结果
or 或，可以关联多个文件
但是只要满足其中一个便可以作为查询结果
1.查询EMP 职位是 salesman & manager
2.查询EMP 职位是 manager 同时工资超过2500
4.字符串函数
第一组 长度计算length() 查看字符的个数
lengthhb() 查看字符的字节数
select length('黄忠')as 个数 from dual;
select lengthb('黄忠')as 字节数 from dual;
第二组 大小写upper(s) 将字符串变大写
lower(s) 将字符串变小写
initcap(s) 将字符串中的单词首字母大写
select upper('javascript') from dual;
select initcap('java query martin') fromdual; //J Q M 均大写
select lower('WriterABC') from dual;
第三组 字符串连接concat(str1,str2) 连接两个字符串，合并成一个字符串返回
|| 可以连接多个列名
语法
select concat(列名1，列名2)
select 列名1||列名2||列名3... as 新名字
EMP表的姓名和岗位连接起来，作为新的列名SHOW
第四组 过滤空格trim(s) 过滤字符串左右两边的空格
lrim(s) 过滤字符串左边的空格
rtrim(s) 过滤字符串右边的空格
select trim(' helloworld') from dual;
左补足 lpad右补足 rpad
语法：
lpad(s,n,char)对s字符串部位，如不够n为，则左侧补充char字符
rpad(s,n,char)一样，右侧补充char字符
char--> 任意字符均可，建议不要用关键字
select rpad('1386987',11,'0') from dual;
329897797987987
**********7987
select lpad('7987',12,'*') from dual;
第五组 截取和寻找语法 substr(s,begin,size*) size非比填项
定义：对S字符串截取，从begin的位置上开始截取（开始位置从1开始，包含begin位置）
size是指截取后保留的长度。
select substr('黄鳝的新衣服',1)from dual;
select substr('1111111',2,5) from dual;
select substr('2222222',2)from dual;
中文和英文字母一样，数人头
查找 instr
语法 instr(s,ss,begin,n) begin & n非必填项
在字符串S中，寻找SS字符串，从begin的位置开始寻找第N次出现的位置
返回位置数
案例
默认从1开始找，找第一次出现S的位置
select instr('fsdhsdfsydsgws','s')from dual;
从第四个字符开始寻找，S第一次出现的位置
select instr('fsdhsdfydsws','s',4)
从第四个字符开始寻找，S第四次出现的位置
select instr('fsdhsdfsydsges','s',4,4)fromdual;
EMPNO 出现后2位 JOB 中 N出现的位置
select substr(empno,3,2),instr(job,'N') fromscott.emp;
数学与数字函数
第一组 进位与截取进位
语法：round(n,s*) *非必填
采用四舍五入方法进位并截取-->去掉多余小数位
round(n) 四舍五入取整数
round(n,s) 四舍五入保留S位小数
select round(8.97) from dual;
select round(8.87,1) from dual;
select round(8.87,5) from dual;
select round(8.8777666,5) from dual;
trunc 直接截取
trunc(n,s)
不四舍五入，直接按小数位截取
select trunc(8.8777766565,5) from dual;
第二组 取余数和整数mod(n1,n2):计算n1/n2取余数
floor(n):返回小于等于N的整数
ceil(n):返回大于等于N的整数
select mod(5,3) from dual;
select ceil(-11.85) from dual;
select floor(99.9999990) from dual;
select sal/33 as 截取前,round(sal/33,5)as 截取后,ceil(sal/33) as ceil后 from emp;
第三组 数学运算power(n1,n2) 返回n1的n2乘方,n1是底数
sqrt(n)返回N的平方根
select power(2,3) from dual; 8
select sqrt(9) from dual; 3
日期函数
系统常量
sysdate,systimestamp
返回ORACLE服务器系统时间
使用建议
oracle date 存储年月日小时分钟秒
oracle timestamp 存储的年月日小时分钟秒毫秒时区
如果只需要年月日，使用date类型
如果需要年月日小时分钟秒毫秒时区，使用timestamp类型
常用的四个日期时间类函数
last_day()
last_day(d) 返回当前时间月份的最后一天日期
select last_day(sysdate) from dual;
next_day()
next_day(d,f) 从从前日期开始计算，下一个星期几的日期
select next_day(sysdate,2) from dual;
oracle：周日1~周六7
add_months()
add_months(date,n)对date日期的月份加上N
select add_months(sysdate,2) from dual;
select sysdate as date1,sysdate+5 as date2from dual; --加天数
extract() 获取时间的特定信息
select extract(year from sysdate) from dual;
select extract(month from systimestamp) fromdual;
select extract(day from sysdate) from dual;
select extract(hour from systimestamp) fromdual;
select extract(minute from systimestamp) fromdual;
select extract(second from systimestamp) fromdual;
转换函数
定义：除了字符，数值和日期函数外，oracle还提供转换函数，将值从一种类型转换成另一种数据类型
函数
第一组 日期、时间和字符串相互转换to_char()
定义：把日期类型数据，按照指定的格式转换字符串，返回输出
语法 to_char(date,format)
将date数据，按照format格式转换成varchar2.
如果不指定format，采用Oracle服务器默认格式
to_date(varchar2,format)
定义：把varchar2数据类型
按照指定的格式转换成日期时间类型，返回输出。
语法 to_date(varchar,format)
将字符串按照格式输出，同样有默认的格式
to_timestamp --> to_date 用法一致
区别：信息更多，有毫秒，时区信息等
format：日期和时间类型的格式
yyyy 四位数字的年
year 全拼的年
month 全拼的月
mm 两位数字的月
mon 简写的月
dd 两位数字的日
day 全拼的星期
dy 简写的星期 (英文)
am pm 上午,下午
hh 小时
hh24 24小时计算模式
mi 分钟
ss 秒
ww 全年的第几周
select to_char(sysdate)from dual;
selectto_char(sysdate,'yyyy/mm/dd--hh24:mi:ss~day')from dual;
to_date('2017-5-11','yyyy-mm-dd')
select to_date('2017-5-1110:30:15','yyyy-mm-dd hh24:mi:ss') from dual;
create table martin99(
staff_id varchar2(20),
name varchar2(20),
password vachar2(20)
birthday date;
lastlogintime timestamp
)
第二组 数字和字符串相互转换to_char(n,format)
按照格式将数字转换成varchar2
select to_char(2000.56) from dual;
select to_char(2000.56,'9999.9')from dual;
select to_char(2000.56,'$9999');
格式规则：
1 默认值即是直接输出
2 格式
9 代表数组
0 表示强迫0显示
$ 显示美金符号
L ORACLE环境指向国家的当地货币符号
. 强调显示小数位
, 显示千位数(西方)
L9999.99
$999.99000
99999999.9900000000
$999,999,999.990000
to_number('varchar2',format)
按照格式将字符串转换成number
sal varchar2(30)
*字符串 可以接受日期，数字*
sal number(8,2)
第三组
五. SQL高级查询
聚合函数定义
聚合函数对一组记录的某个列执行计算统计并返回一个值，聚合函数忽略空值
聚合函数通常和group by字句一起使用
所以也称聚合函数为组函数
函数
count():返回找到的记录数
min():返回一列的最小值
max():返回一列的最大值
avg():返回一列的平均值
sum():返回一列的总和值
select count(sal)或count(*)from scott.emp;
select min(sal) from scott.emp;
select max(sal) from scott.emp;
select avg(sal) from scott.emp;
select sum(sal) from scott.emp;
SQL查询分组与排序
1.排序 order by
定义：order by 列名(字段名) 按照某一列排序
按照某一列排序 Asc(升序，从小到大) 或 Desc 默认ASC(降序,从大到小的顺序)
2.语法 where -->order by 字段名[asc or desc]
案例：EMP表所有的信息，按照收入进行排名，收入最高排榜首
select * from scott.emp where order by saldesc;
select * from scott.emp where oreder by sal; 默认，从小到大
lab1 根据名字进行排序
lab2 根据收入加名字2个字段进行排序
3.分组group by & 聚合函数通常是绑定出现
定义：根据字段把数据表进行分组
功能：它的作用是通过一定的规则将一个数据表的数据分成若干部分，然后结合聚合函数针对每一个部分进行数据处理统计
使用规则：
当使用group by语法关键字时，select某列或某几列的聚合函数(组函数)的返回值，而通常不能是列
where语句后面
语法：
select聚合函数(列) + group by 字段名
案例：
查询每个部门收入最高的员工姓名和收入emp
select deptno,max(sal) from scott.emp groupby deptno;
//错误示范
select ename,max(sal) from scott.emp group bydeptno;
(子查询)
使用group by语法以后，数据已经分成若干组
这个时候查询出来的每一样记录，是在描述某一组记录的信息
空值约束
定义
约束：负责限制表中字段值，是数据表的数据更加合理健壮
5个常用约束
1.非空约束* 要求字段值不能为null
2.主键约束* 非空且唯一
3.外键约束* 要求字段值必须在另外一表中存在，但可以为NULL
4.检查约束* 要求字段值必须符合指定条件
5.唯一约束* 要求字段值不能重复，但可以为NULL
非空约束管理1) 在oracle中，任何数据类型都可以取NULL值
create table tt(
staff_id varchar2(30),
staff_name varchar2(30)
);
默认是字段可以输入NULL空值2) 当数据表中某一列为必填项，需要使用空值约束
如用户注册时，电子邮箱和手机号码通常都为必填项或者说软件公司需要手机用户重要信息
3) 如何使用空值约束 not null关键字
create table tt2(
staff_id varchar2(30),
staff_name varchar2(30),
staff_phone varchar2(30) not null
);
insert into tt2|tt1values(1,'Moriniho',1388888989);
insert into tt2|tt1 values(1,'Moriniho',null);
lab4
null与数字,字符串,日期的运算
select '123456'|| null from dual;
select 33 + null from dual;
select systimestamp||null from dual;
备注：
1.如何数据类型都可以取值NULL,且默认值是可以为NULL
2.空值和任何数值做运算，结果都是NULL
*****************************3.空值和字符串连接，结果都是NULL
4. 如何修改字段的空值约束 DDL
前提：设置空值约束，该字段不允许有NULL值的出现
通常设置空值约束，会在创建表的时候同步创建
或者在没有数据的表中，进行表的字段修改
1 create table ... staff_name varchar2(30)not null;
2 在空表中进行修改
语法
修改添加空值约束
alter table 表名 modify列名 not null;
删除空值约束
alter table 表名 modify列名 null;
主键约束
主键 primary key
是表中的一个重要字段，它的值用于唯一标识表中的某一条记录
在多表查询中，主关键字段用来在一个表中应用来自于另一张表的特定记录
备注：
1.一个表中只能有一个主键
2.主键唯一，非空，不可重复切不能为null
意义：
1.加快数据查询的速度和效率
2.唯一的表示一条记录
类似于身份证ID实名制，而不是姓名表示国家公民
如何创建，修改，删除主键DDL
1.创建表的同时创建主键
create table tt1( -->匿名主键创建
staff_id varchar2(30) primary key,
staff_name varchar2(30),
staff_phone varchar2(30)
);
create table tt2( -->命名主键创建staff_id varchar2(30) primary key,
staff_name varchar2(30),
staff_phone varchar2(30),
constraint tt2_pkey primary key(staff_id)
);
语法：创建表结构最后一行加上关键字constraint 主键名字
primary key(设立主键的列名)
//查询当前用户拥有表的所有约束
select * from user_cons_columns;
删除主键
语法
alter table u1 drop constraint 约束名字(匿名的系统会定义)
//查询当前用户拥有表的所有约束
select * from user_cons_columns;
//先找到匿名的约束名字，命令方式或者工具均可
...drop constraint 自定义约束名称
修改表结构来重新添加主键
语法
alter table 表名 addconstraint 自定义名字 primary key(staff_id)
SQL DDL高级补充
设置字段的默认值
关键字default+字段默认值
create table u1(
staff_id varchar2(30) primary key;
staff_name varchar2(30),
staff_phone varchar2(30),
salary number(7,1) default 2000.0,
job_position varchar2(30) default '实习生'
);
lab1 --day18 pminsert into 自定义
insert into 默认值字段不赋值，不填
insert into u1 values(5,'peter')
insert into u3values(2,'peter',65565655,default,default);
insert intou3(staff_id,staff_name,staff_phone) values(3,'mary',89080909);
增改删字段(列)
add
alter table u3 add abc varchar2(20) default'abc';
modify
alter table u3 modify staff_phonenumber(11,0) not null;
delete
alter table u3 drop column abc
需改表名和字段名
alter table 表名 renameto 新名字
alter table 表名 renameto column name to 新名字
重要提示：
表结构设计均可以通过工具来设置管理
数据表备份
在实际开发项目中，如银行系统，有些关键的数据表备份副表是十分必要的
比如误删了一个重要的VIP客户信息，又想回复该表的数据的时候，提前备份的副表就能确保数据的保护。
否则，让VIP客户重新填写信息，和申报原来的存款?
丢失数据的代价与确保数据不丢失的代价之比
1 确保数据不丢失备份的代价几乎可以忽略不计
2 丢失重要数据的代价却是企业通常无法承受的
如何备份表结构和数据?
语法
create table 新表(不存在) as select * from 要备份的数据表名
SQL DML高级补充
数据同步更新
数据副表创建后，如何和主表保持信息同步
语法： insert into 副表名 select * from 主表名
1. scott.emp --> martin_emp1
2. martin_emp1(主表)--> martin_emp2(副表)
3. emp1插入一条新的记录
4. 删除副表的数据
5. 重新同步更新主副表数据
create table martin_emp1 as select * fromscott.emp;
create table martin_emp2 as select * frommartin_emp1;
insert into martin_emp1 values(99,'变形金刚'5,'汽车人',7777,sysdate,9999.99,999.99,50)
delete from martin_emp2;
insert into martin_emp2 select * frommartin_emp1;
表别名和列别名
1.列别名 略 as 或空格
2.表别名
t-->自定义别名，随便取
select t.ename,t.job from martin_emp1 t
martin_emp1 t -->给当前表取了别名t
并且可以在字段中使用，select语句和where语句均可
select m.salary,m.ename from martin_emp1 mwhere m.salary >=3000;
select x.salary,ename from martin_emp1 xwhere x.salary >= 3000 and x.ename like '%n';
SQL 高级查询
select where,group by,order by ,having
SQL高级查询六大关键字
综合练习+聚合数据
(1) 书写顺序查询中用的关键词主要包含6个，它们的书写顺序依次为：
select-->from -->where-->group by--> having -->order by
(2) 使用规则
select 和 from是必须的，其它关键字为可选，需要注意的是六大关键字的书写顺序和执行顺序不一样
执行顺序
from--> where --> group by--> having-->select --> orderby
(3) 解释
from: 需要从哪个表获取数据
where: 过滤表中数据的条件
group by: 如何分组
having: 对上面分组的数据，再次进行条件过滤
order by: 根据最终查询结果，来排序显示
例子：
查询各部门的平均薪水及部门编号
要求只列出平均工资 >2000 emp
select avg(m.sal) as '部门平均工资' from martin_emp1 m group by m.deptno;
SQL 复合查询
子查询
定义：将一个查询包含到另一个查询当中
为什么：
1. 有些查询如果不使用子查询无法用SQL查询出来
2. 表达高级查询是最自然方式
lab查询与scott在同一个部门的雇员
select * from martin_emp1 where deptno notin(
select deptno from martin_emp1
where ename='SCOTT' or ename='KING'
);
查询当前收入最低的员工姓名
select ename,empno from scott.emp wheresal=(select min(sal) from scott.emp);
联合查询
集合操作是针对2个select查询出来的结果集进行操作
(1) 合并操作 union unionall
union 合并连个select结果
去除重复记录，过滤select后面所有字段值都相同的记录
union all合并两个select结果，不去除重复记录
select empno,ename,sal from scott.emp whersal<=1000
union| union all
select empno,ename,sal from scott.emp wheresal>=5000
order by sal asc;
交集操作 intersect
select empno,ename,sal from scott.emp whersal<=1000
intersect
select empno,ename,sal from scott.emp wheresal>=5000
order by sal asc;
差操作 minus 获取前一个select有而后一个没有的记录结合操作总结select empno,ename,sal from scott.emp whersal<=1000
minus
select empno,ename,sal from scott.emp wheresal>=5000
order by sal asc;
连接查询
内连接
inner join 内连接 ~ join
外连接
left join 右连接
right join 左连接
full join 完全外连接 `
SQL分页查询
如何获取前N条记录
10,11-20
在oracle中，使用rownum来获取前N条记录
它为伪例，在创建表是自动添加一列，对应着每一条记录
select ename,sal from(
select empno,ename,sal from emp order by salasc
) where rownum <=3;
六. PL/SQL编程
PLSQL简介procedual language SQL ,过程语言，是结合了Oracle过程语言和SQL的一种基于SQL的扩展语言
PLSQL可以使用条件语句和循环控制语句结构
过程的控制结构和SQL的数据处理能力无缝结合
形成了强大的编程语言
极大的弥补了SQL单程序执行的确定
使用SLSQL的原因和优势
支持SQL
SQL是访问数据库的标准语言，通过SQL用户可以操作数据库。PLSQL支持所有的SQL语言。
更好的性能
SQL非过程语言，只能一条一条执行。PLSQL可以组装任意多的SQL执行语言，更加高效，更符合实际需要。
PLSQL语法结构
PLSQL是一种块结构的语言，一个PLSQL包含了一个或多个SQL执行程序
PLSQL程序块可以声明变量，写程序柱体，和异常捕获机制
PLSQL语法结构
DECLARE 声明部分
...... declare 程序，通常是变量的声明部分，可选
begin 程序执行主体部分，begin开始，end结束 =={}
....程序块
exception 异常处理部分，可选。和java异常一致
end;
简言之，PLSQL程序块，包含三个部分。开头声明部分中间执行主体。主体包含一个特殊部分异常处理部分。
除中间执行主题为必填项，其余均为可选项。
PLSQL之Helloworld！
lab1 hello world + class_name JSD1705
declare
class_name varchar2(20) // String class_name
begin
class_name:='JSD1705'; //c
lass_name=JSD1705";
dbms_output_put_line('HelloWorld'||class_name); //System.out.println("str");打印内容
end;
PLSQL编程
PLSQL特殊符号
1. 赋值 :=
2. 范围 .. 常用在循环控制做计数器使用
如 1...5表示从1到5循环5次
3. 算术符号 +_*/()
4. 关系!= = < > >= <= > <
5. 逻辑 and or
如何声明变量与常量
1. 声明变量
语法：
变量名 变量类型;(声明)
变量名 变量类型:=xxx;(声明并赋值)
案例：
abc number(7,2):=5000.55;
abc2 varchar2(20);
abc:='hello USA';
2.变量默认值*
语法：
变量名 变量类型 default 初始值;
abc varchar2(30) default 'hello USA';
3.常量
语法：
常量名 constant 数据类型:=常量不变的值;
7.5.3 PLSQL数据类型
a 常用类型
data timestamp number varchar2 char boolean
b 属性类型 %ROWTYPE
%ROWTYPE:当声明一个变量的值是数据表的一行记录时，可以直接使用属性类型来声明
7.5.4 动态赋值
语法 select 列名 into 变量名 from 表名
案例 select ename into str from emp;
例子：输出员工编号为7654的员工姓名和工资待遇
PLSQL逻辑控制
1. if else if elseif else
语法：
a
if 表达式 then
elseif 表达式 then
else
end if;
b case --->switch
语法 case
when 表达式 then 程序；
when 表达式 then 程序； //可选项
...
end case;
c for ---> java for
语法
for i in 1...10 loop int i1-->10
end loop;
七. ORACLE和MYSQL的简单区别
1）Oracle没有offet,limit，在mysql中我们用它们来控制显示的行数，最多的是分页了。oracle要分页的话，要换成rownum。2）oracle建表时，没有auto_increment，所有要想让表的一个字段自增，要自己添加序列，插入时，把序列的值，插入进去。
3）oracle有一个dual表，当select后没有表时，加上的。不加会报错的。select 1 这个在mysql不会报错的，oracle下会。select 1 from dual这样的话，oracle就不会报错了。
4）对空值的判断，name != ""这样在mysql下不会报错的，但是oracle下会报错。在oracle下的要换成name is not null
5）oracle下对单引号，双引号要求的很死，一般不准用双引号，用了会报
ERROR at line 1:
ORA-00904: "t": invalid identifier
而MySQL要求就没有那么严格了，单引号，双引号都可以。
6）oracle有to_number,to_date这样的转换函数，oracle表字段是number型的，如果你$_POST得到的参数是123456，入库的时候，你还要to_number来强制转换一下，不然后会被当成字符串来处理。而mysql却不会。
7）group_concat这个函数，oracle是没有的，如果要想用自已写方法。
8）mysql的用户权限管理，是放到mysql自动带的一个数据库mysql里面的，而oracle是用户权限是根着表空间走的。
9）group by,在下oracle下用group by的话，group by后面的字段必须在select后面出现，不然会报错的，而mysql却不会。
10）mysql存储引擎有好多，常用的mysiam,innodb等，而创建oracle表的时候，不要这样的，好像只有一个存储引擎。
11）oracle字段无法选择位置，alter table add column before|after，这样会报错的，即使你用sql*plus这样的工具，也没法改字段的位置。
12）oracle的表字段类型也没有mysql多，并且有很多不同，例如：mysql的int,float合成了oracle的number型等。
13）oracle查询时from 表名后面 不能加上as 不然会报错的，select t.username from test as t而在mysql下是可以的。
14）oracle中是没有substring这个函数的，mysql有的。
--------------------------------- 我是低调的分隔线 ---------------------------------