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

樂小伍· 2019-08-29
本文来自 csdn ,作者 樂小伍
版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/lexiaowu/article/details/99988911

一、首先在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创建用户授权的步骤(在系统用户下完成的):

① 首先要创建 表空间

SQL> create tablespace 表空间名 datafile '数据文件绝对路径\数据文件名.DBF'  size  大小(单位 :m);

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

创建用户

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

③ 为用户 授权

SQL> grant connect,resource to 用户名;

④ 用普通用户登录
直接连接: conn 用户名/密码;

退出,重新登陆:exit

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

SQL> passw

更改 LXW 的口令
旧口令:
新口令:
重新键入新口令:
口令已更改

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

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

删除用户

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

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)区间查询

-- where 列名 between and 区间查询

(2)过滤掉重复的行

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

(3)模糊查询

select模糊查询使用like关键字

【语法】

like ’ % ’ ;

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

① 前面精确后面模糊

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

② 前面模糊后面精确

③ 前后模糊中间精确

(4)聚合函数

概念:数据汇总

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

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

(5)select查询汇总

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)索引

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

create index  索引名 on 表名(列名)
-- 表名(列名)  指定为表的哪一列创建索引

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

drop index 索引名

(2)视图

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

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用户下面为普通用户授予创建视图的权限

 SQL >  grant create view to 用户名;

(3)序列

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

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

【语法】

create sequenece 序列名
start with-- 初始值  int i=0 
increment by-- increment by 步长  i++
minvalue 值  -- 最小值  最小值不能大于start with
maxvalue 值  -- 最大值
nocache/cache  -- nocache不使用缓存   cache 使用缓存
nocycle/cycle;  -- nocycle不循环(超过最大值不循环) cycle超过最大值从新循环

【栗子】

-- 没有创建序列的情况下,需要显示的插入ID值
insert into 表名(id,其它列)values(你手输的id,'数据');

-- 序列名.NEXTVAL  就会自动插入id
-- 序列名 是我们创建的序列 , NEXTVAL序列的下一个值
insert into 表名(id,其它列)values(序列名.NEXTVAL,'数据');
commit;

【删除序列】

drop sequence 序列名;

四、PL/SQL (面向过程和SQL结合)

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

1. 匿名 PL/SQL


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


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

declare
  声明变量、常量
begin
  程序逻辑
end;  -- PL/SQL结束

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

-- 设置一个服务器端的输出缓存 大小是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使用&符号完成输入框的输入
【栗子】

-- 输入字符串必须有''
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】

declare
  声明变量、常量
begin

             if 条件  then
                 程序语句1
             else 
                 程序语句2;
             end if;
 
end;

4. 选择 — case when

【语法】

case
            when  条件 then  执行的语句1
            when  条件 then  执行的语句2
            when  条件 then  执行的语句3
            else 执行的语句4
end case;

5. 循环结构

【for循环】

for 迭代器变量  in 初始值..最大值 loop
	循环体;
end loop;

【栗子】-- 求1~100和

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循环】

while 条件 loop
	循环体;
	更新循环变量;
end loop;

【栗子】 – 求1~100和

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 过程

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

create or replace procedure 过程名称(参数名称 参数类型 数据类型)
as
-- 定义变量
begin
     语句
end;

【注意】

  • 参数名称使用v_ 开始

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

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

6.2 游标 (MySQL中没有)

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

create or replace procedure 过程名称(参数名称 参数类型  数据类型)
as
 cursor  游标名称  is  SQL语句;
begin
  -- 遍历游标,读取游标的数据
  for i in 游标名称  loop
  	i.empno  i.ename;
  end loop;
end;

6.3 触发器(重点)

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

create or replace trigger 触发器名称
触发时间  -- before 在触发事件之前执行 / after 在触发事件之后执行
触发事件  -- insert/ delete / update
on 表名称  -- 在那个表上触发
触发级别   -- for each row 行级触发
begin
--触发语句
end

五、Oracle提供函数

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

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


① sysdate:返回当前日期

-- 21-AUG-2019
select sysdate from dual; 

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

-- 提取系统日期中的天 
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是参数1四舍五入保留的小数位数
select round(参数1 ,参数2) from dual;

⑤ sqrt:平方根
【栗子】

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

⑥ power :幂
【栗子】

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

3. 字符串相关函数

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

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

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

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

③ trim :去掉空格
【语法】

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

④ length :返回字符串的长度

	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’)转换为字符串