Oracle 语法及使用
Oracle数据库的物理结构
数据文件的后缀：.DBF
控制文件的后缀：.CTL
日志文件的后缀：.LOG
一个数据库（数据库实例orcl）由一个或者多个数据文件和控制文件和日志文件组成，但是一个数据库至少有一个数据文件和控制文件和日志文件。
-- 使用命令查看数据库中的数据文件。
select name from V$DATAFILE;
-- 使用命令查看数据库中的控制文件。
select name from V$CONTROLFILE;
-- 使用命令查看数据库中的日志文件。
select MEMBER from V$LOGFILE;
Oracle行注释
-- 单行注释
/*
多行注释
*/
set pagesize; -- 每页显示多少行数据
set linesize; -- 每行占用多少个字符
set timing on; -- 显示每个sql命令执行时间
-- 如何将缓存中的数据持久化到磁盘的数据文件中？
commit;
Oracle数据库的逻辑结构
表空间（Tablespace）、段（Segment）、区间（Extend） 、块（Block）、用户、数据库对象。
表空间
-- 创建表空间
-- create tablespace 表空间名称 datafile '磁盘存在的路径\数据文件.dbf' size 初始化空间大小;
create tablespace tablespace_name datafile 'D:\app\oracle\oradata\orcl\tablespace_name.dbf' size 64m;
-- 注意：创建表空间必须指定数据文件，数据文件的路径必须真实存在 表空间大小一定要指定
-- 打开表空间自动扩展功能语法：
alter database datafile '磁盘存在的路径\数据文件.dbf' autoextend on;
-- 关闭表空间自动扩展功能语法：
alter database datafile '磁盘存在的路径\数据文件.dbf' autoextend off;
-- 查看数据文件另外的一个命令
select file_name,tablespace_name from dba_data_files;
-- 查看表空间命令，显示数据库(数据库实例)所有的表空间
select tablespace_name from dba_tablespaces;
-- 删除表空间语法
-- drop tablespace 表空间名称;
drop tablespace tablespace_name;
用户
-- 创建用户并指定密码
-- create user 用户名 identified by 密码;
create user username identified by t1234;
-- 创建用户并且指定表空间
-- create user 用户名 identified by 密码 default tablespace 表空间名称；
create user username identified by t1234 default tablespace table_space;
-- 切换用户登录
-- conn 用户名/密码;
conn username/t1234;
-- 删除用户
drop user 用户名;
用户授权和撤销授权
-- 用户授权
-- grant 权限列表 to 用户;
-- CONNECT 连接权限 RESOURCE 访问数据库对象(资源)权限 DBA最高权限
grant CONNECT,RESOURCE,DBA to username;
-- 撤销权限
-- revoke 权限列表 from 用户;
revoke CONNECT,RESOURCE,DBA from username;
用户上锁与解锁
-- 锁定用户
-- alter user 用户名 account lock;
alter user 用户名 account lock;
-- 用户解锁
-- alter user 用户名 account unlock;
alter user 用户名 account unlock;
数据库对象
数据库对象包括以下： 表空间(TableSpace)、表(Table)、视图(View)、索引(Index)、序列(Sequnce)、存储过程(stored procedure)、函数(Function)、触发器(Trigger)。
SQL语法
工作中尽量大写，提高sql语句执行效率。
SQL语言的基本概念
数据库定义语言（DDL）:是用于描述数据库中要存储的现实世界实体的语言。
——常用关键字：create（创建） alter（修改） drop（删除）
数据库操纵语言（DML）：查询数据库及操作已有数据库中的数据的计算机语言。
——常用关键字：insert（插入） update（更新） delete（删除）
数据库查询语言（DQL）：用于检索表中所有数据，或特定范围内的数据。
——常用关键字：select （查询）
数据库控制语言（DCL）：用来设置或更改数据库用户或角色权限的语句。
——常用关键字：grant（授权） revoke（回收权限）
数据库事物语言（TCL）：定义为把一串一起执行的操作作为单个逻辑工作单元处理
——常用关键字：commit 语句完成显式事务,并使所有的修改是持久有效的
rollback 语句终止当前事务，使数据库返回到以前的状态
Oracle数据类型
| 类型 | 含义 | 存储描述 | 备注 |
|---|---|---|---|
| CHAR | 固定长度字符串 | 最大长度2000bytes | |
| VARCHAR2 | 可变长度的字符串, | 最大长度4000bytes | 可做索引的最大长度749 |
| NCHAR | 根据字符集而定的固定长度字符串 | 最大长度2000bytes | |
| NVARCHAR2 | 根据字符集而定的可变长度字符串 | 最大长度4000bytes | |
| DATE | 日期（日-月-年） | DD-MM-YY(HH-MI-SS),经过严格测试，无千虫问题 | |
| TIMESTAMP | 日期（日-月-年） | DD-MM-YY(HH-MI-SS:FF3),经过严格测试，无千虫问题 | 与DATE相比较，TIMESTAMP有小数位秒信息 |
| LONG | 超长字符串 | 最大长度2G,足够存储大部头著作 | |
| RAW | 固定长度的二进制数据 | 最大长度2000bytes | 可存放多媒体图象声音等 |
| LONG RAW | 可变长度的二进制数据 | 最大长度2G | 可存放多媒体图象声音等 |
| BLOB | 二进制数据 | 最大长度4G | |
| CLOB | 字符数据 | 最大长度4G | |
| NCLOB | 根据字符集而定的字符数据 | 最大长度4G | |
| BFILE | 存放在数据库外的二进制数据 | 最大长度4G | |
| ROWID | 数据表中记录的唯一行号 | 10bytes | *..*格式，*为0或1 |
| NROWID | 二进制数据表中记录的唯一行号 | 最大长度4000bytes | |
| NUMBER(P,S) | 数字类型 | P为整数位，S为小数位 | |
| DECIMAL(P,S) | 数字类型 | P为整数位，S为小数位 | |
| INTEGER | 整数类型 | 小的整数 | |
| FLOAT | 浮点数类型 | NUMBER(38)，双精度 | |
| REAL | 实数类型 | NUMBER(63)，精度更高 |
运算符
| 查询条件 | 关键字 |
|---|---|
| 比较 | =、>、<、>=、<=、!=、NOT、等比较运算符 |
| 确定范围 | Between And、Not Between And |
| 确定集合 | In、Not In |
| 字符匹配 | Like、Not Like |
| 是否空值 | Is Null、Is Not Null |
| 多重条件 | And、Or |
数据定义语言(DDL)
创建表的语法：
create table 表名称( 列名称 数据类型, 列名称 数据类型, ... 列名称 数据类型 );-- 注意：任何数据库都没有{}，每列名称之间使用英文逗号分离-- 工作中一个表最好不要超过15列-- ; 表示创建表结束
例：
create table student( -- 学生表 stu_ID number(4), -- 学号 stu_NAME varchar2(10), -- 姓名 stu_birth date, -- 出生日期 stu_score number(7,2) -- 分数);
查看表是否创建成功
desc 表名称; -- 查看表结构-- 例：desc student;
在sys用户下可以查询数据库服务器创建的所有表名称
-- owner 表示表所在的用户(哪个用户创建的)-- table_name 表名称select owner,table_name from dba_tables;-- where owner='SCOTT' 查看SCOTT用户下面有哪些表select owner,table_name from dba_tables where owner='SCOTT';
删除表
语法
-- drop table 表名称 ;drop table student;
修改表结构
-- 1.添加新列-- 语法：-- alter table 表名称 add 列名称 数据类型;alter table tb_students add student_sex varchar(2); -- student_sex 学生性别-- 2.列的重命名-- 语法：-- alter table 表名称 rename column 旧列名称 to 新列名称;alter table tb_students rename column student_sex to stu_sex; -- student_sex 改为stu_sex-- 3.修改列的数据类型-- 语法：-- alter table 表名称 modify 列名称 数据类型;alter table tb_students modify stu_sex number(1); -- stu_sex列的数据类型修改为number 0男 1女-- 4.删除一列-- 语法：-- alter table 表名称 drop column 列名称;alter table tb_students drop column stu_sex; -- 删除stu_sex列-- 注意：rename 和 drop 后面一定要加上 column-- desc 表名称 作用是查看表结构
数据查询语言（DQL）
Data Query Language。检索(查询)：从表中检索(查询)数据，特征：不会修改(改变)表的数据和表的结构， 读。
select 关键字是DQL的核心。
查询语法
-- select 列名称1，列名称2,...,列名称n from 表名称;-- select 检索(查询)-- from 从那张表检索(查询)数据select tablespace_name,block_size from dba_tablespaces;/* dba_tablespaces表是sys用户下面的表，该表存储了所有表空间的信息，不是由我们自己创建的，而是在安装Oracle数据库服务器的过程中帮我们创建的。 */-- 从dba_tablespaces表中选择tablespace_name,block_size的数据进行检索(查询)-- 先执行from,后执行select-- 查询所有列(select * from 表名)select * from student;-- 查询指定列(select列1，列2…from 表名)select stu_id,stu_name from student;/* 打开黑窗口命令行执行select语句从数据文件中检索数据，首先会查询缓存中有没有该命令对应的数据，如果有直接冲缓存中检索(查询)数据，如果没有就会从数据文件中检索(查询)数据，将检索(查询)的数据放入缓存中。第二次执行select语句就会由于该数据在缓存中已经存在了，所以不会查询磁盘的数据文件，而是直接查询缓存。目的：少跟磁盘左I/O交互，因为磁盘读取数据慢，缓存处理数据快 */
列别名和表别名
列别名
列别名：增强SQL语句的可读性
select stu_id as 学号,stu_name as 姓名,stu_birth as 出生日期,stu_score as 分数 from student;-- as 可以省略，但在工作中不建议省略
表别名
表别名：多张表之间做联合查询能够简化书写
select s.stu_id as 学号,s.stu_name as 姓名,s.stu_birth as 出生日期,s.stu_score as 分数 from student s;-- from emp e ：e就是表别名 表别名不能有as关键-- 使用了表别名就可以在列上使用： s.stu_ID表示stu_ID是student表的列 此时的s就是student表的别名(小名称)
where关键字
根据条件进行行过滤，where关键字后面跟条件，where关键字跟在from 表名称之后。
select s.stu_id as 学号,s.stu_name as 姓名,s.stu_birth as 出生日期,s.stu_score as 分数 from student s where s.stu_id=101;
算术运算符
+ - * / ：依次为加，减，乘，除。Oracle语法中没有%，取模用mod()。
例：
-- 所有同学分数加5分select stu_score+5 分数 from student;-- 注意：不要再where后面写算术运算符
比较运算符
| 操作符 | 含义 |
|---|---|
| = | 等于（不是==） |
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| <> | 不等于 |
例：
-- 查询编号不为101的所有学生信息select stu_id as 学号,stu_name as 姓名,stu_birth as 出生日期,stu_score as 分数 from student where stu_id<>101;
逻辑运算符
| 操作符 | 含义 |
|---|---|
| AND | 与（交集） |
| OR | 或（并集） |
| NOT | 非（取反） |
例：
-- 查询出生日期在2019-10-09并且分数高于80的学生select stu_id as 学号,stu_name as 姓名,stu_birth as 出生日期,stu_score as 分数 from student where stu_birth='2019-10-09' and stu_score>80;
集合查询
关键字：in
例：
-- 查询学生id为101和102的学生信息select stu_id as 学号,stu_name as 姓名,stu_birth as 出生日期,stu_score as 分数 from student where stu_id in (101,102);
过滤重复的行
关键字：distinct
例：
-- 查询student表中所有学生编号，对编号进行去重select distinct stu_id from student;-- distinct 后面跟列名称-- distinct 去重的参照物是行，首先查询出结果集，然后根据每行进行比较，有没有重复的数据，如果有去掉，如果没有将其添加到新的结果集
模糊查询
关键字：like
通配符 “%” 和 “_”：
%：表示任意个字符，包括零个；
_：表示一个任意字符；
例：
-- 查询姓名以张开头的学生select * from student where stu_name like '张%';-- 查询姓名以四开头的学生select * from student where stu_name like '%四';-- 查询姓名第二个字为三的学生select * from student where stu_name like '_三%';
聚合函数
数据操纵语言（DML）
insert：向表中插入数据
-- 语法-- insert into 表名称 values(值1,值2,值3...值n);insert into student values(101,'李四',to_date('2019-10-09','yyyy-MM-dd'),82); -- 向表插入数据-- 按指定列插入数据-- insert into 表名称(列1,列2) values(值1,值2);insert into student(stu_id,stu_name,stu_sex,stu_birthday,stu_sal) values(109,'张三丰','男',to_date('2019-9-28','yyyy-MM-dd'),2500);-- 虽然完成了向表中插入数据，但是数据只是插入到Oracle的缓存中，并没有持久化到磁盘的数据文件，如何将缓存中的数据持久化到磁盘的数据文件中？commit; -- commit;表示将缓存中的数据持久化到磁盘的数据文件中-- 设置每页显示的行数,设置每行的大小-- set pagesize 大小;-- set linesize 大小;set pagesize 30 ; -- 表示每页显示30行数据set linesize 300; -- 表示每行占用300个字符
delete：删除表中的数据
-- delete from 表名称;delete from student; -- （删除整张表数据）/* 注意：delete只会删除表中的数据，不会删除表结构 在工作中删除表中的数据通常需要添加一个条件 where关键字 表示行过滤 */delete from student where id=105; -- （删除指定行数据）where后面的条件为true执行删除
update：更新(修改)表中的数据
语法：
update 表名称 set 列名称=值;
例：
update student set stu_score = stu_score + 1; -- 所有学生的分数+1-- 注意：Oracle不是面向对象语言不能写 +=update student set stu_score = stu_score + 1,stu_age = stu_age + 1; -- 所有学生分数+1，年龄+1-- 注意：如果由多个列要修改时，需使用英文逗号分隔-- 场景：将id为102的学生分数-3；update student set stu_score = stu_score - 3 where id=102;/* 注意：工作中update、delete执行更新和删除表中的数据最好加上条件 工作中insert插入数据最好指定列名称 */
小结：数据库表中的数据会发生改变（插入、删除、修改），所以执行完毕之后一定要 commit;
数据控制语言（DCL）
为用户进行授权的，通常DCL是由DBA做的
grant：给用户授予权限
语法
-- grant 权限列表 to 用户;
grant CONNECT,RESOURCE to username;
revoke：撤销用户的授权
语法
-- grant 权限列表 from 用户;
revoke CONNECT,RESOURCE from username;
-- CONNECT 表示连接的权限
-- RESOURCE 表示访问资源的权限
事务控制语言（TCL）
事务控制语言：TCL: Transaction Controll Language
-
事务是最小的工作单元，它作为一个整体进行操作, 此工作单元中的语句要么全部成功，要么全部失败不充许部分成功和部分失败
-
保证事务的整体成功或失败，称为事务控制
-
事务的四大特性ACID ：
原子性(Atomicity ）、一致性（Consistency ）、隔离性（Isolation）、持久性（Durability ） -
用于事务控制的语句有：
COMMIT - 提交并结束事务处理
ROLLBACK - 撤销事务中已完成的工作
SAVEPOINT – 标记事务中可以回滚的点
update student where num=10;
savepoint s1;
insert into student where num=11;
rollback s1; --回退到了s1，也就是之后的插入数据操作撤销了
commit;
Oracle连接数据库
package com.lyang.oracle.jdbc;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Oracle连接数据库查询emp表
*
*/
public class JdbcOracle {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
conn = DriverManager.getConnection(url, "scott", "t1234");
st = conn.createStatement();
String sql = "select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp";
rs = st.executeQuery(sql);
while (rs.next()) {
int empno = rs.getInt(1);
String ename = rs.getString(2);
String job = rs.getString(3);
int mgr = rs.getInt(4);
Date hiredate = rs.getDate(5);
int sal = rs.getInt(6);
int comm = rs.getInt(7);
int deptno = rs.getInt(8);
System.out.println(empno + "\t" + ename + "\t" + job + "\t" + mgr +
"\t" + hiredate + "\t" + sal + "\t" + comm + "\t" + deptno);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
conn.close();
st.close();
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
