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

文章目录

- 一、首先在dos黑窗口下输入：sqlplus -> 进入Oracle登录
- 二、sys用户、system用户、普通用户
- 三、Oracle表的管理(对表操作后最好都 commit提交一哈，MySQL不用，会自动提交)
- - 1. 表名和列名命名规则
  - 2. 数据类型
  - 3. 对表结构查询，增加，修改，删除（ DDL - 数据定义语言）
  - 4. 创建表，对表的增删改查（DML - 数据操纵语言）
  - - （1）区间查询
    - （2）过滤掉重复的行
    - （3）模糊查询
    - （4）聚合函数
    - （5）select查询汇总
  - 5. 其它DDL
  - - （1）索引
    - （2）视图
    - （3）序列
- 四、PL/SQL （面向过程和SQL结合）
- - 1. 匿名 PL/SQL
  - - 1.1 定义变量
    - 1.2 定义常量
    - 1.3 Oracle注意的地方
  - 2. 输入（相当于java中的Scanner）
  - 3. 分支结构
  - 4. 选择 --- case when
  - 5. 循环结构
  - 6. 署名
  - - 6.1 过程
    - 6.2 游标（MySQL中没有）
    - 6.3 触发器（重点）
- 五、Oracle提供函数
- - 1. 日期，系统时间相关函数
  - 2. 数学相关函数
  - 3. 字符串相关函数
  - 4. 转换函数

一、首先在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/lexiaowu/article/details/99697140

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

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

表的增删改查： https://blog.csdn/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’)转换为字符串

更多推荐

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

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

文章目录

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

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

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

1. 表名和列名命名规则

2. 数据类型

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

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

（1）区间查询

（2）过滤掉重复的行

（3）模糊查询

（4）聚合函数

（5）select查询汇总

5. 其它DDL

（1）索引

（2）视图

（3）序列

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

1. 匿名 PL/SQL

1.1 定义变量

1.2 定义常量

1.3 Oracle注意的地方

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

3. 分支结构

4. 选择 — case when

5. 循环结构

6. 署名

6.1 过程

6.2 游标（MySQL中没有）

6.3 触发器（重点）

五、Oracle提供函数

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

2. 数学相关函数

3. 字符串相关函数

4. 转换函数

发布评论取消回复

最近发表

热门文章

标签列表

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

文章目录

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

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

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

1. 表名和列名命名规则

2. 数据类型

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

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

（1）区间查询

（2）过滤掉重复的行

（3）模糊查询

（4）聚合函数

（5）select查询汇总

5. 其它DDL

（1）索引

（2）视图

（3）序列

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

1. 匿名 PL/SQL

1.1 定义变量

1.2 定义常量

1.3 Oracle注意的地方

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

3. 分支结构

4. 选择 — case when

5. 循环结构

6. 署名

6.1 过程

6.2 游标 （MySQL中没有）

6.3 触发器（重点）

五、Oracle提供函数

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

2. 数学相关函数

3. 字符串相关函数

4. 转换函数

相关文章

发布评论取消回复

最近发表

热门文章

标签列表

6.2 游标（MySQL中没有）