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）、用户、数据库对象。

 详见：ORACLE数据库存储结构简介

表空间

-- 创建表空间
-- create tablespace 表空间名称 datafile '磁盘存在的路径数据文件.dbf' size 初始化空间大小;
create tablespace tablespace_name datafile 'D:apporacleoradataorcl	ablespace_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

1. 事务是最小的工作单元，它作为一个整体进行操作, 此工作单元中的语句要么全部成功，要么全部失败不充许部分成功和部分失败

2. 保证事务的整体成功或失败，称为事务控制

3. 事务的四大特性ACID ：
   原子性(Atomicity ）、一致性（Consistency ）、隔离性（Isolation）、持久性（Durability ）

4. 用于事务控制的语句有：

   ​ 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 + "	" + ename + "	" + job + "	" + mgr + 
						"	" + hiredate + "	" + sal + "	" + comm + "	" + deptno);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				conn.close();
				st.close();
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}