oracle数据库基础语法

firstbook
89 阅读10分钟

一、基础语法

1.创建用户并授权用于登录

  • 创建用户
 create user xiaomai identified by 123456
  • 修改用户密码
alter user xiaomai identified by 123456
  • 授予用户权限

一般可以赋予connect、resource、dba三者权限

grant connect,resource,dba to xiaomai;

grant all to xiaomai;   //赋予所有权限
  • 移除用户上的权限
 语法： revoke connect, resource from 用户名;

2.表结构

创建表结构

CREATE TABLE "student2023"
(
	"stu_id" VARCHAR2(20) NOT NULL,
	"stu_name" VARCHAR2(20) NOT NULL,
	"stu_gender" NUMBER(1) NOT NULL,
	"stu_age" NUMBER(10) NOT NULL
)

修改表结构（modify）

-- 修改表结构
alter table "student2023" modify ("stu_gender" VARCHAR2(10));

添加表结构（add）

-- 添加一个字段
alter table "student2023" add ("stu_score" NUMBER(10) NOT NULL);

删除表结构（drop）

-- 删除一个字段
alter table "student2023" drop column "stu_score";

3.约束（constraint）

数据库中有以下几种约束

  • primary key:主键约束
  • unique:唯一约束
  • not null:非空约束
  • foreign key:外键约束

创建约束

-- 添加stu_id为表的主键约束
alter table "student2023" add constraint stu_id_pk primary key("stu_id");

--添加外键约束与添加主键约束类似，但是需要外键关联到的表主键上(dept:表示表名)
alter table "student2023" add constraint stu_no_fk foreign key(stu_no) references dept(dep_no);

-- 去掉stu_age的非空约束
alter table "student2023" modify "stu_age" NULL;

-- 添加唯一约束
alter table "student2023" add constraint stu_name_unq unique("stu_name");

删除约束

-- 删除唯一约束
-- alter table 表名 drop constraint 约束名
alter table "student2023" drop constraint stu_name_unq;

--删除主键
alter table "student2023" drop constraint stu_id_pk;
-- 删除主键同样可以使用
alter table "student2023" drop primary key;

4.索引（index）

索引为数据库中的一个对象，与表独立存在，使用索引能够加速数据库的查询效率在某些时候，一般只有在操作大型的表结构时，才会考虑使用索引查询。


-- 创建单列索引
create index "stu_id_index" on "student2023"("stu_id","stu_name");

-- 创建多列索引
create index "stu_id_index" on "student2023"("stu_id","stu_name");

-- 删除索引
drop index "stu_id_index";

5.视图（view）

视图能够在不改变基表的情况下，能够查询自己想要的数据

-- 创建查询student2023表数据的视图
create or replace view "stu_view" 
AS
select "stu_id","stu_name" from "student2023";

-- 查询视图，与查询数据库表一样
select * from "stu_view";

-- 删除视图
drop view "stu_view";

6.默认值

某些情况下，某个字段不能为空，但是添加的时候又不想添加，这时候可以考虑给这个字段添加默认值

-- 给stu_gender字段添加默认值
alter table "student2023" modify "stu_gender" default 1;

-- 去除默认值
alter table "student2023" modify "stu_gender" default null;

7.数据查询

数据查询主要有以下几种方式

  • 简单查询
  • 子查询
  • 多表联接查询

简单查询：即为从某张表中简单查询出自己所需要的数据，一般查询就为一张表即可。

-- 从表student2023中查询出所有数据
SELECT * FROM "student2023";

子查询：一般需要查询多张表，先查询出来的数据作为另外一次查询的结果集。

-- 从表student2023中查询出所有数据，作为结果集再继续查询下一次
SELECT "stu_id","stu_name" from (SELECT * FROM "student2023");

联接查询：一般进行多表查询使用，查询的表之间通过某种联系关联起来。联表查询有分为以下几种：

  • 内联接查询（inner join）
  • 左外联接（left outer join）
  • 右外链接（right outer join）

内联接查询:所查询的两张表尽量是对等的，只有两张表中的共同数据才会被查询出来。

-- 查询的时候a,b为别名，inner关键字可以省略
SELECT a."stu_id",a."stu_name",a."stu_gender",a."stu_age",b."stu_score"
FROM "student2023" a
INNER JOIN "score2023" b
ON a."stu_id" = b."stu_id";

image-20230824171534677转存失败，建议直接上传图片文件

左外联接查询：会将左表中的所有数据查询出来，右表中的数据如果与左表中的数据的字段不对等，会被显示为null。

-- 同理OUTER可以省略
SELECT a."stu_id",a."stu_name",a."stu_gender",a."stu_age",b."stu_score"
FROM "student2023" a
LEFT OUTER JOIN "score2023" b
ON a."stu_id" = b."stu_id";

image-20230824171511489转存失败，建议直接上传图片文件

右外联接查询：会将右表的所有数据查询出来，左表中的数据如果与右表中数据的字段不对等，会被显示为null。

SELECT a."stu_id",a."stu_name",a."stu_gender",a."stu_age",b."stu_score"
FROM "student2023" a
RIGHT OUTER JOIN "score2023" b
ON a."stu_id" = b."stu_id";

image-20230824171443530转存失败，建议直接上传图片文件

8.分组排序查询（group by、order by）

使用分组查询时，要与一些复合函数搭配使用才行，例如count()、sum()、max()、min()等，并且没有在having之后出现的表达式必须出现在group by表达式之后。

-- 查询score2023表中的数据，将score分数大于60的分为一个组，最后以id进行降序处理
-- 在使用分组查询时，条件限制只能使用having限制，而不能使用where条件限制
-- GROUP BY 后面接的字段必须是前面select后查询的字段
SELECT "stu_id",sum("stu_score") as "stu_score" from "score2023" 
group by "stu_id" having sum("stu_score") > 60 
order by "stu_id" desc;

二、基本函数

1.转换函数

  • cast()函数

cast()函数主要用于将一个数据类型转换成另外一个数据类型。接收两个参数，第一个参数为转换的表达式或者列，第二个参数是目标数据类型。常用的转换函数还要to_char(),to_date()，to_number()等

-- 将字符串类型转换为日期类型并显示
SELECT CAST('2023-08-25' as DATE) as time from dual;

2.聚合函数

  • ceil()
  • floor()
  • round()

ceil()函数用于向上取整，floor()函数用于向下取整，round()函数用于四舍五入。

-- 输出10
SELECT ceil(9.8) FROM dual;

-- 输出9
SELECT floor(9.8) from dual;

-- 输出10
SELECT ROUND(9.8005) FROM dual;

3.截取函数

  • substr

substr()函数用于截取字符。

-- 从第二个数截取到最后
SELECT substr('hello',2) as hello from dual;

4.单行函数

  • nvl(x,value)

    如果x为空，返回value，否则返回x。

    案例：查询student2023表，如果stu_gender字段为空，则自动变为0。

    SELECT "stu_id","stu_name",NVL("stu_gender",0) "stu_gender","stu_age" from "student2023";

  • nvl2(x,value1,value2)

    如果x非空，返回value1,否则返回value2

    案例：查询score2023表，如果stu_score有数据，则变为100，否则为0。

    SELECT "stu_id",NVL2("stu_score", 100,0) "stu_score" from "score2023";

许多函数用的时候自查即可

三、常用对象

1.序列（sequence）

序列用于设置数据库某个字段按照特定的值进行生成，一般使用序列可以搭配触发器进行主键id的自动增长。

创建序列

CREATE sequence "student2023_stu_id_seq"
minvalue 1 --最小值
nomaxvalue --表示没有最大值
increment by 1 --步长为1
start with 1 --从1开始增加
nocache; --没有缓存

查看序列

-- 查看所有存在的序列
select * from user_sequences;

-- 查看特定的序列
select * from user_sequences where sequence_name = 'student2023_stu_id_seq';

-- 查看包含的序列
select * from user_sequences where sequence_name like '%student2023';

修改序列

-- 修改序列，步长为10
ALTER SEQUENCE "student2023_stu_id_seq" increment by 10;

删除序列

-- 删除序列
DROP SEQUENCE "student2023_stu_id_seq";

2.触发器（trigger）

触发器的作用是用于在特定的时机做特定的事情，例如在每次插入数据之前，都获取一个id值，搭配序列，实现id自动增长。

语法格式：

/*
* tri_student_id_auto: 触发器名称，一般见名知意
**/
create or replace trigger tri_student_id_auto
--触发触发器的条件
/*
*	before: 在指定操作之前，after在指定操作之后
*	insert: 表示添加操作，update修改操作，delete删除操作，查询一般不用
*	student: 表示基于哪一张表进行操作
*/
before insert on student
/*
*	for each row: 是指行级触发器，表示每一行操作都会触发该触发器
*	如果不写则指语句块触发器，表示在整张表上只会触发一次该触发器
*/
for each row
declare
	//定义变量
begin
	//编写逻辑
end;

实现id自动增长：

CREATE OR REPLACE TRIGGER "student2023_trigger"
BEFORE INSERT ON "student2023"

FOR EACH ROW

DECLARE
-- 定义变量
BEGIN
	select "student2023_stu_id_seq".nextval into :new."stu_id" from dual;
END;

插入数据测试下：

INSERT INTO "student2023"("stu_name","stu_gender","stu_age") values('A',1,23);
INSERT INTO "student2023"("stu_name","stu_gender","stu_age") values('B',0,20);
INSERT INTO "student2023"("stu_name","stu_gender","stu_age") values('C',1,18);

SELECT * FROM "student2023";

image-20230825105310002转存失败，建议直接上传图片文件

实现：当对学生表中的数据进行删除时，同理将成绩表中对应的学生成绩删除，实现一个触发器。

CREATE OR REPLACE TRIGGER "student2023_delete_score"
AFTER DELETE ON "student2023"
FOR EACH ROW
DECLARE
	v_count NUMBER;
BEGIN
-- 	如果是删除操作才会执行
IF DELETING THEN
	SELECT COUNT(1) INTO v_count FROM "score2023" where "stu_id"=:old."stu_id";
-- 	如果在成绩表中存在才会执行相应操作
	IF (v_count > 0) THEN
		DELETE FROM "score2023" where "stu_id"=:old."stu_id";
	END IF;
END IF;
END;

删除触发器

DROP TRIGGER 触发器名称
DROP TRIGGER "student2023_delete_score";

3.函数（function）

在数据库中，自定义函数与编程语言一样，函数能够实现很多功能。下面是自定义函数语法。函数与存储过程类似，唯一不同的是函数有返回值，存储过程没有。

语法格式：

create or replace function 函数名(参数1 模式 参数类型)
return 返回值类型
as
变量1 变量类型;
变量2 变量类型;
begin
    函数体;
end 函数名;

参数的模式有3种:(如果没有注明, 参数默认的类型为 in.)

  • in: 为只读模式, 在函数中, 参数的值只能被引用, 不能被改变;
  • out: 为只写模式, 只能被赋值, 不能被引用;
  • in out: 可读可写.

创建一个函数，接收两个参数，返回两个数中的最大值。

-- 创建函数
CREATE OR REPLACE FUNCTION getMaxValue(num1 in NUMBER,num2 in NUMBER)
RETURN NUMBER
AS
-- 定义变量
BEGIN
	IF (num1 >= num2) THEN
		return num1;
	ELSE
		return num2;
	END IF;
END;

-- 使用函数,输出3000
SELECT getMaxValue(300,3000) "max" from dual;

删除函数

DROP FUNCTION 函数名
DROP FUNCTION getMaxValue;

4.存储过程

存储过程语法与自定义函数类似，只是没有返回值。

编写一个存储过程，判断学生表中是否有该学生，如果有，则打印出学生信息，如果没有则添加信息。

CREATE OR REPLACE PROCEDURE "insert_student2023_info"(stu_id in VARCHAR2)
AS
 v_count NUMBER;
BEGIN
-- 	查找数据
SELECT COUNT(1) INTO v_count FROM "student2023" WHERE "stu_id"=stu_id;
IF (v_count > 0) THEN
	dbms_output.Put_line('学生信息已经存在!');
ELSE
	INSERT INTO "student2023"("stu_name","stu_gender","stu_age") VALUES('vv',0,18);
END IF;

EXCEPTION
-- 异常处理
	WHEN others THEN
		ROLLBACK;
END;

调用存储过程

-- 调用存储过程
call "insert_student2023_info"('5');

如果存在，会打印以下信息：

image-20230825120002031转存失败，建议直接上传图片文件

不存在，则添加数据。

删除存储过程

-- 删除存储过程
DROP PROCEDURE "insert_student2023_info";
avatar
文章
阅读
粉丝