Oracle数据库基础入门、数据库语句

一、首先在dos黑窗口下输入：sqlplus -> 进入Oracle登录

二、sys用户、system用户、普通用户

Oracle安装会自动生成 sys用户 和 system用户 ，是系统用户

• sys用户：sys用户 是超级用户，具有最高权限，具有 sysdba 角色，有 create database 的权限

• system用户：system用户 是管理操作员，权限也很大，具有 sysoper 角色，没有 create database 的权限

• 普通用户：是通过在系统用户下创建的，可以给普通用户授予权限，可以授予connect、resource、create view、dba权限，dba权限是最高权限，一般不需要授给普通用户。

Oracle创建用户授权的步骤(在系统用户下完成的)：

① 首先要创建 表空间

1
2
3
4
SQL> create tablespace 表空间名 datafile '数据文件绝对路径数据文件名.DBF'
size
大小（单位 ：m）;

数据文件路径例如：D:applxw oradataorcl数据文件名.DBF (是在你安装的Oracle数据库的oradata文件下)
表空间（数据库/schema模式/.DBF）：一个用户只能创建一个表空间，一个表空间可以创建多个用户（重要，这也是和MySQL数据库不太一样的地方）

② 创建用户

1
2
SQL> create user 用户名 idetified by 密码 default tablespace 表空间名;

③ 为用户 授权

1
2
SQL> grant connect,resource to 用户名;

④ 用普通用户登录
直接连接： conn 用户名/密码;
或
退出，重新登陆：exit

⑤ 还可以修改Oracle用户密码：普通用户输入 passw

1
2
3
4
5
6
7
SQL> passw
更改 LXW 的口令
旧口令:
新口令:
重新键入新口令:
口令已更改

还可以用sys、system用户修改密码(alter是修改)

1
2
SQL> alter user 用户名 idetified by 新密码;

删除用户

1
2
SQL> drop user 用户名 【cascade】;

注意：在删除用户时，如果要删除的用户已经建了表，那么就需要写cascade，即把该用户和表一同删除

三、Oracle表的管理(对表操作后最好都 commit提交一哈，MySQL不用，会自动提交)

1. 表名和列名命名规则

① 和java不一样，不能使用驼峰命名，每个单词之间用下划线分隔（ _ ）
② 必须以字母开头
③ 长度不能超过30个字符
④ 不能使用Oracle关键字和保留字

注：列名后面直接空格跟列别名

2. 数据类型

- - 这里只列出最常用的几个
① 字符型
char(长度)： 长度固定
varchar2(长度)：长度可变，可以节省空间
【栗子】
char(10) 类型的 abc —> 会自动补全空格 —> ‘abc 七个空格’
varchar2(10) 类型 abc —> 就是 ‘abc’
注：Oracle中字符串都是用的单引号，和java中的不同，java中字符是单引号，字符串是双引号
还有，字符串连接是 两个竖杠（ || ），java中是加号（ + ）

② 数字型
number（总位数）
number（总位数，小数位数）

③ 日期类型 ----- date

1
2
3
4
5
6
to_date('日期' , 'YYYY-MM-DD');
-- 日期类型存入
select
to_char(列名 , 'YYYY-MM-DD');
-- 取出

注：Oracle中注释是两个减号，不是两个斜杠
④ 图片 — blob
但一般存图片的路径

3. 对表结构查询，增加，修改，删除（ DDL - 数据定义语言）

表结构的增删改查 : https://blog.csdn.net/lexiaowu/article/details/99697140

就是对表的 列名（即字段）操作

4. 创建表，对表的增删改查（DML - 数据操纵语言）

表的增删改查 ： https://blog.csdn.net/lexiaowu/article/details/99697670

• 其中select查询最重要：

（1）区间查询

1
2
-- where 列名 between and 区间查询

（2）过滤掉重复的行

1
2
3
4
--
distinct关键字 过滤掉重复的行
select DISTINCT 列名 from 表名 ;

（3）模糊查询

select模糊查询使用like关键字

【语法】

like ’ % ’ ;

①②③ 只是百分号的位置不同

① 前面精确后面模糊

1
2
3
4
5
-- %匹配多个字符
-- 前面精确， 后模模糊(可以是任意字符)
-- 注意：SQL关键字和列名称不区分大小写，但是查询的内容一定要区分大小写，否则无法查询正确的结果
-- 小结： like关键字只能用在from关键字后面

② 前面模糊后面精确

③ 前后模糊中间精确

（4）聚合函数

概念：数据汇总

特征：每个聚合函数查询结果当行单列,返回一个值

数据库提供了5个聚合函数：max(), min(), sum(), avg(), count() from 表名

（5）select查询汇总

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select 列名 / 或 聚合函数 （列名）
from 表名
-- 指定表
where 条件（可以加子查询select）
-- 根据条件过滤 行
group by 列名
-- 分组
having
过滤分组的条件
-- 过滤分组条件，对聚合过滤，where不支持聚合。所以用having
order
by 列名 desc（递减↓）;
-- 最后对查询结果进行排序 ，默认是asc（递增↑）

-- 执行顺序： from ---> where
---> group by
---> having
---> select ---> order by 

select 列名 / 或 聚合函数 （列名）-------- 查询
from 表名 ---------------------------------------- 指定表
where 条件（可以加子查询select） ------ 根据条件过滤 行
group by 列名 ----------------------------------- 分组
having 过滤分组的条件 ----------------------- 过滤分组条件，对聚合过滤，where不支持聚合。所以用having
order by 列名 desc（递减↓）; --------------- 最后对查询结果进行排序 ，默认是asc（递增↑）

上述为一条完整的语句

执行顺序： from —> where —> group by —> having —> select —> order by

5. 其它DDL

（1）索引

【概念】作用在表上，相当于一本书（表）的目录（索引）
【创建索引的好处】可以快速查询数据，就像看书一样，通过目录，看到页码，然后根据页码很快就能翻到。
【创建索引语法】

1
2
3
4
5
create index
索引名 on 表名(列名)
-- 表名(列名)
指定为表的哪一列创建索引

【注意】
当你为某个表创建一个主键约束，就会自动隐式的创建一个索引
当你为某个表创建一个唯一约束，也会隐式的创建一个唯一索引
【删除索引语法】

1
2
drop index 索引名

（2）视图

【概念】视图是一张虚表，一般只供查看，不支持修改，所以要设置为只读，
而且视图为了隐藏核心数据，只暴露其它数据，供查看的
【创建视图语法】

1
2
3
4
5
6
7
create view 视图名
as
SQL 语句
-- SQL语句控制显示哪些数据
with read only;
-- 视图设置为只读

创建视图最好设置为只读，要不然你修改了视图的内容，原表的数据也会同步修改，因为你创建视图就是为了查看的，不要修改

【问题】

错误报告 -
ORA-01031: 权限不足
01031. 00000 - “insufficient privileges”
*Cause: An attempt was made to perform a database operation without
the necessary privileges.
*Action: Ask your database administrator or designated security
administrator to grant you the necessary privileges
【原因】scott用户没有创建视图的权限
【如何解决】在system用户下面为普通用户授予创建视图的权限

1
2
3
 SQL >
grant create view to 用户名;

（3）序列

【概念】自动为你创建一个整数类型的编号值,能够为你自动创建id

【为什么需要序列】之前向表插入id值是手动插入的，风险：有可能插入的id已经在表中存在，就会报错

【语法】

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create sequenece 序列名
start with 值 -- 初始值
int i=0 
increment by 值
-- increment by 步长
i++
minvalue 值
-- 最小值
最小值不能大于start with
maxvalue 值
-- 最大值
nocache/cache
-- nocache不使用缓存
cache 使用缓存
nocycle/cycle;
-- nocycle不循环（超过最大值不循环） cycle超过最大值从新循环

【栗子】

1
2
3
4
5
6
7
8
-- 没有创建序列的情况下，需要显示的插入ID值
insert into 表名(id,其它列)values(你手输的id,'数据');
-- 序列名.NEXTVAL
就会自动插入id
-- 序列名 是我们创建的序列 ， NEXTVAL序列的下一个值
insert into 表名(id,其它列)values(序列名.NEXTVAL,'数据');
commit;

【删除序列】

1
2
drop sequence 序列名;

四、PL/SQL （面向过程和SQL结合）

Procedure Language / Struct Query Language
面向过程语言和结构化查询语言

1. 匿名 PL/SQL

重要：使用 PL/SQL 之前，必须加下面一句，否则执行不成功
- - 设置一个服务器端的输出缓存 大小是999999
set serveroutput on size 999999

【概念】
没有名字的的PL/SQL语句，只能执行一次，不能重复调用
【语法】

1
2
3
4
5
6
7
declare
声明变量、常量
begin
程序逻辑
end;
-- PL/SQL结束

【栗子】— 第一个HelloWorld（程序猿学语言的第一个程序HelloWorld）

1
2
3
4
5
6
7
-- 设置一个服务器端的输出缓存 大小是999999
set serveroutput on size 999999
begin
dbms_output.put_line('Helloworld');
-- dbms_output.put_line 输出语句
end;

1.1 定义变量

【语法】 变量名 数据类型 := 值;

1.2 定义常量

【语法】 常量名称 constant 数据类型 :=值;

1.3 Oracle注意的地方

① PL/SQL使用’’ 表示字符串， 使用||做字符串拼接
② 不能使用自增自减运算符，只能
变量 := 变量 +/- 数

2. 输入（相当于java中的Scanner）

PL/SQL使用&符号完成输入框的输入
【栗子】

1
2
3
4
5
6
7
8
9
-- 输入字符串必须有''
declare
v_sex varchar2(1) := '&n';
v_score number(5,2) := &no;
begin
dbms_output.put_line(v_sex);
dbms_output.put_line(v_score);
end;

3. 分支结构

【简单分支 if else】

1
2
3
4
5
6
7
8
9
10
11
declare
声明变量、常量
begin
if 条件
then
程序语句1
else
程序语句2;
end if;
end;

4. 选择 — case when

【语法】

1
2
3
4
5
6
7
8
9
10
11
12
13
case
when
条件 then
执行的语句1
when
条件 then
执行的语句2
when
条件 then
执行的语句3
else 执行的语句4
end case;

5. 循环结构

【for循环】

1
2
3
4
5
for 迭代器变量
in 初始值..最大值 loop
循环体；
end loop;

【栗子】-- 求1~100和

1
2
3
4
5
6
7
8
9
declare
v_sum number(5) :=0;
begin
for i in 1..100 loop
v_sum := v_sum+i;
end loop;
dbms_output.put_line('value='||v_sum);
end;

【while循环】

1
2
3
4
5
while 条件 loop
循环体;
更新循环变量;
end loop;

【栗子】 – 求1~100和

1
2
3
4
5
6
7
8
9
10
11
12
13
declare
v_sum number(5) :=0;
v_index number(3) :=0;
begin
while v_index<=100 loop
-- 循环体
v_sum := v_sum+v_index;
-- 更新循环变量
v_index :=v_index+1;
end loop;
dbms_output.put_line('=sum='||v_sum);
end;

6. 署名

【匿名PL/SQL缺点】不能重复调用

【署名】显示定义有名称的PL/SQL

6.1 过程

【概念】定义一次可以重复调用
【语法】

1
2
3
4
5
6
7
create or replace procedure 过程名称(参数名称 参数类型 数据类型)
as
-- 定义变量
begin
语句
end;

【注意】

• 参数名称使用v_ 开始

• 参数类型 in输入参数 out输出参数 inout即是输入也是输出参数

• 变量的定义在as和begin之间

6.2 游标 （MySQL中没有）

【概念】运行在过程里面，能够逐行的读取数据， 游标名称就是表名称

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create or replace procedure 过程名称(参数名称 参数类型
数据类型)
as
cursor
游标名称
is
SQL语句；
begin
-- 遍历游标，读取游标的数据
for i in 游标名称
loop
i.empno
i.ename;
end loop;
end;

6.3 触发器（重点）

【概念】
发生某件事情，会触发一个事件，执行对应的行为。触发器不用显示调用就能够执行
【触发器机制】
当发生insertdelete事件时，会产生一个触发事件，在触发事件中会创建一张虚表(dual),虚表的结构和真实表一样，虚表永远只保存真实表最近插入or删除的一行数据(虚表永远只有一行数据)
【语法】

1
2
3
4
5
6
7
8
9
10
11
12
13
create or replace trigger 触发器名称
触发时间
-- before 在触发事件之前执行 / after 在触发事件之后执行
触发事件
-- insert/ delete / update
on 表名称
-- 在那个表上触发
触发级别
-- for each row 行级触发
begin
--触发语句
end；

五、Oracle提供函数

1. 日期，系统时间相关函数

相当于Java中Date类和Calendar类的成员方法

① sysdate:返回当前日期

1
2
3
-- 21-AUG-2019
select sysdate from dual;

AUG是英文八月的缩写，很不友好，不好看
② extract :根据指定格式提取时间相关的数据

1
2
3
4
5
6
7
-- 提取系统日期中的天 
select extract(day from sysdate) from dual;
-- 提取系统日期中的年份 
select extract(year from sysdate) from dual;
-- 提取系统日期中的月份 
select extract(month from sysdate) from dual;

所以，引出一个问题，那么工龄等怎么计算的，
因为工作年份是日期类型存储的，就可以用当前日期减工作年份，就可得到工龄惹

2. 数学相关函数

相当于Java中的Math类的静态方法
① abs（args）：参数的绝对值
② ceil(args) 天花板:向上取整 3.14----》4

floor(args) 地板：向下取整 3.14----》3
③ mod (args): 类似于java的%运算符 取余数
④ round ：四舍五入

1
2
3
-- 对参数1进行四舍五入， 参数2是参数1四舍五入保留的小数位数
select round(参数1 ,参数2) from dual;

⑤ sqrt:平方根
【栗子】

1
2
3
4
--取9的平方根：结果3
select sqrt(9) from dual;
select sal,sqrt(sal)工资的平方根 from emp;

⑥ power :幂
【栗子】

1
2
3
4
5
-- power 取参数1的幂，幂的次数由参数2决定
-- 此时3的3次幂
select power(3,3) from dual;
select sal, power(sal,2) from emp;

3. 字符串相关函数

① concat:拼接字符串
【语法】

1
2
	select 列名1 ,ename , concat(empno,ename) from emp;

② replace:字符串的替换
参数1是原始字符串，参数2是需要替换其中的字符串， 参数3是目的字符串
【语法】

1
2
	select replace(原始字符串,'要替换的字符串','目的字符串') from 表名;

③ trim ：去掉空格
【语法】

1
2
3
4
5
	-- 去掉字符串左边和右边的空格
select trim('
vac
') from dual;

④ length ：返回字符串的长度

1
2
	select length(列名) 列别名 from 表名 ;

【注意】Oracle数据库关键字不区分大小写，但是字符串内容会区分大小写

4. 转换函数

① to_date(args1,args2) :将其他类型转换为日期
to_date(‘1980-01-01’,‘YYYY-MM-DD’) 将字符串’1980-01-01’转换为指定的格式’YYYY-MM-DD’
② to_char(args1,args2):将其他类型转换为字符串
to_char(日期类型 ,‘YYYY-MM-DD’) 将日期类型，按照指定格式(‘YYYY-MM-DD’)转换为字符串

最后

以上就是甜蜜学姐最近收集整理的关于Oracle数据库基础入门、数据库语句的全部内容，更多相关Oracle数据库基础入门、数据库语句内容请搜索靠谱客的其他文章。