Oracle数据库基本知识
每一个数据库语句写完之后,按住Fn键+F8键即为执行
什么是数据库触发器
触发器是特定事件出现的时候,自动指向的代码块。用户不能直接调用他们。数据库触发器是一个与表相关联的、存储的PL\SQL语句。每当一个特定的数据操作语句(insert update delete)在指定的表上发出时,Oracle自动执行触发器中定义的语句序列。
触发器的功能
1、允许\限制对标的修改 |
---|
2、自动生成派生列 |
3、强制数据一致性 |
4、提供审计和日志记录 |
5、防止无限的事务处理 |
6、启用复杂的业务逻辑 |
触发器语法结构:
creaet [or replace] tigger 触发器名 触发时间 触发事件
on 表名
[ for each row]
begin
pl\sql语句
end
statement level :语句级别,在创建触发器的过程中会有出发语句级别选择,是行级触发还是全局触发。
触发器类型:
1、语句触发器 |
---|
2、行触发器 |
3、INSTEAD OF 触发器 |
4、系统条件触发器 |
5、用户事件触发器 |
Order By用法详解
Order by 字句用来对一列或者多列的结果集按照圣墟或者降序来进行排序。
SELECT name1,name2,name3,...
from table1.name1,table2.name1,table2.name2,table2.name2,table.name3,...
order by
name1 ASC;//升序排列
name2 DESC;//降序排列
***Order by 字句总是在select语句中的最后一个字句。
Oracle Distinct用法
SELECT DISTINCT 用来过滤结果集中的重复行,以此来确保select子句中返回指定的一列或者多列的值是唯一的。
SELECT DISTINCT
column_1,column_2,...
from tab_name;
//tab_name表中的column_1、column_2中的值分别比较过滤掉重复项
Oracle创建用户、角色、授权、建表、删除用户
1、登录
此处的登录指的是控制台命令窗口登录,首先Win+R打开命令窗口,然后在里面输入cmd后按回车(Enter)键进入
然后输入语句:
sqlplus sys/sys as sys dba;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jYDANkyk-1627468492846)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210709104134785.png)]
2、创建用户
create user user_name identified by user_password;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KqkgYdyd-1627468492848)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210709104328939.png)]
3、赋予创建的新用户权限
不给用户分配权限,即使创建了用户,还是不能登录到PL里面去,因此需要赋予相应的权限。
登录权限:
grant create session to user_name;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fSep50DE-1627468492850)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210709105026979.png)]
创建表的权限:
grant create table to user_name;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ozU5WxwT-1627468492853)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210709105048918.png)]
对表的操作权限(增删改查的权限)
grant unlimited tablespace to user_name;//增加表空间
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZQ5L2WIG-1627468492854)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210709105108940.png)]
4、给某个用户授予超级权限(dba权限)
假设已知需要被赋予权限的用户为:kunlun
第一步:登录
Win+R 进入控制台,然后再输入 cmd 命令按Enter键进入Doc窗口
然后输入指令:sqlplus /nolog 按Enter进入下一步
sql>conn /as sysdba;
第二步:查询所有用户名,要授予权限的用户是否存在
sql>select username from dba_users;
第三步 赋予权限
sql>grant dba to kunlun;
5、删除用户
drop user user_name;
如果用户拥有对象,则不能直接删除,否则将返回一个错误值。指定关键字casecade,可删除用户所有的对象,然后再删除用户。
drop user user_name casecade;
6、修改用户密码
alter user user_name indentified by xxxxxx;
7、回收用户权限
revoke connect,resource from user_name;
--revoke 回收
--from 从哪里(哪个)用户回收
8、给用户加锁或者解锁
--加锁
alter user user_name account lock;
--加锁
alter user user_name account unlock;
9、使用imp指令导入外部.dmp文件到Oracle里面
imp username/pwd file=fileName.dmp full=y ignore=y
例如,将test.dmp这个dmp文件导入到Qracle中,用户为kunlun,登录口令为123456的用户表里面:
imp knlun/123456 file=text.ddmp full=y ignore=y
对数据库表的基本操作
1、数据库表单操作关键字
创建----create
修改----alter
删除----drop
查询----select
查询表结构----desc
特别地,查询表结构要在sql>__________________情况下进行
提交事务----commit
回滚事务----rollback
2、建表实例:创建一张表名为test,里面有name、id、sex、age、time字段的表
create table test(
id number(4) primary key,
name varchar2(10),
sex char(3),
age number(4),
time date
);
3、向表中插入数据
1、向全部列插入数据
insert into 表名 values(列值1,列值2,列值3,。。。,列值n);
----values中的值必须和表结构中的列名一一对应
insert into test values(1,'张三','男',24,2018-7-12);
2、向指定列插入数据
insert into 表名(列名1,列名2,。。。,列名n)values(列值1,列值2,。。。,列值n);
---列值必须和字段一一对应
insert into test (id,sex) values (5,'男');
4、修改(更新)表中内容
update 表名 set 列名1 = 该列新值,列名1 = 该列新值,。。。,列名n = 该列新值 where 条件语句;
特别的,如果没有where 条件,则默认更改全部的数据内容,所以,where条件很重要
5、删除表中数据
delete from 表名 where 条件;
如果没有where条件语句,则删除全部的数据
6、删除表
drop table 表名;
7、alter对表的操作
1、更新字段名
alter table TABLE_NAME rename column column_old to column_new
2、添加新字段
alter table TABLE_NAME add COLUMN_NAME varchar(15)
3、删除字段
alter table TABLE_NAME drop column COLUMN_NAME
4、添加字段并赋值
alter table TABLE_NAME add COLOMN_NAME NUMBER(1) DEFAULT 1
5、修改字段值
update TABLE_NAME set filedname=value where filename=value
6、修改字段数据类型
alter table table_name modify filename varchar(20)
----接要修改的数据类型即可
Oracle ora-01045错误解决方法
错误的根本原因:没有给用户授予权限
解决方法 :给用户授予相应的权限
操作方法:同样的进入控制台命令窗口,输入语句 sqlplus sys/sys as sys dba;
然后输入语句:grant create ssession to user_name;
Oracle where字句用法
where是Oracle中的条件查询字句,也就是通常所说的条件过滤语句
----where其实就是语句的查询条件
select user_name,user_id,user_sex,user_Number
from tset
where user_id>10 and user_id<25
Oracle fetch 字句用法
fetch 字句用来限制查询返回的行数,指定要返回的行数或者行数的百分比。
offset 字句指定在行限制开始之前跳过行数。offset子句是可选择的,如果跳过他,则偏移量为0,行限制从第一行开始计算。
偏移量必须是一个数字或者一个表达式,其值为一个数字。遵循的规则有:
如果偏移量是负值,则将其视为0;
如果偏移量为NUL或者大于查询返回的行数,则不返回任何行;
如果偏移量包含一个分数,则分数部分被截断。
Oracle like 字句
like运算符在Oracle语句中乐意用来测试列中字句是否与指定的模式匹配。
--代码示例:
select t_age,t_name,t_sex
from test
where t_sex like 'W%'
order by t_age
------- %匹配零个或者多个字符的任何字符串;_(下划线)匹配任何单个字符。
Oracle 事务
commit :提交事务,commit语句可以用来提交当前事务的所有更改,提交后,其他用户能够看到提交后的更改。
重点:必须拥有DBA权限才能访问系统视图;必须拥有DBA权限才能指定commit语句的某些功能。
数据库的约束语句和表之间的关系
约束即是一种限制,规定不能做某些事情。数据库的约束,是对数据的安全性、完整性的保证。
规范:约束类型_ 字段名
主键:PK_stuno
检查约束:CK_字段名
唯一约束:UQ_字段名
非空约束:NN_字段名
外键约束:FK_子表_父表
默认约束:一般不需要命名
数据库完整性以及分类
1、域完整性:又称为列完整性,指定一个数据集对某一个列是否有效和确定是否允许空值
2、实体完整性:又称为行完整性,要求表中每一行有一个唯一的标识符,即primary key。
3、参照完整性,又称为引用完整性
主键和唯一键的主要区别
一个数据表只能创建一个主键约束,但可以创建若干个唯一键。主键字段值不允许为空,但是唯一键可以为空;
两者均不允许表中的对应字段存在重复值,在创建主键和唯一键时会自动产生索引。
创建表时添加主键:
sql>create table table_name (field_name char(8) primary key);
创建表时添加外键
sql>create table table_name(field_name char(8) referance table_name2(field_name2));
增加 NOT NULL
sql>alter table table_name modify field_name not null;
增加unique
sql>alter table table_name add constraint constraint_name unigue(field_name);
或者
sql>alter table table_name add unique(field_name);
增加 primary key
sql>alter table table_name add constraint constraint_name primary key(field_name);
或者
sql>alter table table_name add primary key(key);
增加foreign key:
sql>alter table table_name add foreign key(field_name) references table_name2(field_name2);
删除约束
sql>alter table table_name drop constraint constraint_name;
或者
sql>alter table table_name drop unique(field_name);
sql>alter table table_name drop primary key;
在删除主键约束时,可能产生错误,这是因为两张表存在主从关系,所以在删除主键约束时,必须加上 cascade
sql>alter table table_name drop primary key cascade;
查看数据库端口号
进入Doc命令窗口,然后输入 lsnrctl status
然后找到以下文字:(DESCRIPTION = (ADDRESS =(PROTOCOL = tcp)(OST = 127.0.0.1) (PROT=1521)))
字符类型讲解
字符型
varchar():定长的字符型数据。也就是给某个字段分配了m个字符存储长度,只用了n个存储单位,则剩下的m-n个存储空间仍然空着,不能被其他字段占用,字段长度的取舍不当,会浪费大量的存储空间
varchar2():变长的字符型数据。也就是存储空间在给定范围内动态获取,没用的分配空间会被再次分使用,节省存储空间
数值型:
number():不带小数点的数值
number(8,3):数据的总长度为8位,小数点后占3位。就是小数点要占三位
Sql语句是不区分大小写的,包括登录的用户名和密码都是不区分大小写的
在查询过程中,对于数值型的数据,可以执行加减乘除的运算
取别名有不同的方式:可以不用 as 直接跟别名,可以使用as ,可以使用“”,也可以不用“”
使用双竖线可以把多列查询结果合并到一起。
在连接表达式中出现字符数据
select ziduan1 || 'zidiani是ziduan2 的' || ziduan2 '从属关系'
from table_name
两个日期行的数据相减,得到的是两个日期相差的天数,一个日期加减一个数值,得到一个新的日期
日期行的数据不能进行相乘除运算。
Oracle数据库——包
所谓包就是把一组PL/Sql的代码元素组织在一个命名空间下
包由两部分组成:规范部分(必须的,类似于java的接口),包体部分(可选择的,类似于接口的实现)
规范部分
1、可以申请差不多所有数据类型的元素,但是,一般来说,用该避免在包的规范部分声明变量,不过,声明声明常量总是安全的
2、可以声明否任何的数据结构
3、可以声明一个过程和函数,但是只能在包规范中包含程序的头部(关键字IS或者AS之前的内容,但是不能包括关键字
,头部必须用一个分号结尾
4、可以在包规范中使用显示游标
5、声明了一个过程或者函数,或者声明了一个没有查询语句的游标,就必须要提供一个实体来实现这些元素代码
6、AUTHID子句,确定在引用包中的数据对象是应该根据包的属主(AUTHID)来解析还是根据包的调用者(AUTHID CURRENT_USER)解析。但是这个不是必须的。
包体部分
什么时候需要包体:
1、包规范中包含了带有RETURN子句的游标声明,需要在包体中指定SELECT语句
2、包规范中包含了过程或者函数声明,需要在包体中实现这些模块的实现代码
3、想通过包的初始化单元执行代码
NVL函数:Oracle提供的函数,是用于处理null值使用的
select ename,sal*13+nvl(comm,0)*13 from emp
nvl(值1,值2)解释:nvl值1为null时则取值2,值1不为null时则取值1的原值。
连接字符串时使用 || 连接符
Oracle分组查询
1、max()求最大值,min()求最小值
2、avg()求平均值
3、count()求总数
Oracle常用函数
1、Ascii() :返回与指定的字符对应的十进制数
select ascii('A')A,ascii('a')a from dual;
2、char():给出整数,返回对应的字符
select char(555521)tao,char(7894)qian from dual;
3、concat():连接两个字符串,与 || 连接符的作用相同
select concat('hello','world')from dual;
--不加别名,则字段的别名默认为 CONCAT('HELLO','WORLD')
select concat('hello','world') upp from dual;
--upp 为字段的别名
--纯数字不能作为字段的别名,不然会报错
4、initcap():返回字符串,并将字符串的第一个字母变成大写
select initcap('hello') upp from dual;
5、instr(被搜索的字符串1,希望搜索的字符串2,搜索的开始位置,第n次出现的位置):在一个字符串中搜索指定的字符,返回发现指定的字符位置
select instr('hello world','lo',1,2) mmm from dual;
6、length(): 返回字符串长度
select length('sfsfgsgsgs')eee from dual;
7、lower():返回字符串,并能将所有的字符串小写
select lower('HELLO')ttt from dual;
8、upper():返回字符串,并将所有的字符串大写
select upper('hello')ttt from dual;