Oracle数据库笔记以及plsql的使用

1.登录sys

1.sql*plus登录

请输入用户名: connect / as sysdba

输入口令:oracle

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

2.plsql登录

输入用户名: system

输入口令:oracle

数据库：orcl

2.用户创建与授权（system权限）

1.新建个用户

create user xxxxx(用户名) identified by "密码"

例如： create user sqluser01 identified by oracle;

2.修改用户信息

alter user 用户名 identified by “新密码” --修改用户密码

例如：alter user sqluser01 identified by 123456

3.删除用户

语法：drop user 用户名;

例如：drop user sqluser01;

若用户拥有对象，则不能直接删除，否则将返回一个错误值。指定关键字cascade,可删除用户所有的对象，然后再删除用户。

语法： drop user 用户名 cascade;

例子： drop user sqluser01 cascade;

4.查看权限

1.查看当前用户所有权限

select * from user_sys_privs;

2.查看所用用户对表的权限

select * from user_tab_privs;

3.授权

新建的用户和默认的用户是锁住的，没有权限。所以新建用户后要给用户赋予权限.ORACLE系统提供三种权限：Object 对象级、System 系统级、Role 角色级。

oracle为兼容以前版本，提供三种标准角色（role）:connect/resource和dba.

1 connect role(连接角色)

connect 只对其他用户的表有访问权限，包括select/insert/update和delete等。

connect role 的用户还能够创建表、视图、序列（sequence）、簇（cluster）、同义词(synonym)、回话（session）和其他 数据的链

2 resource role(资源角色)

--更可靠和正式的数据库用户可以授予resource role。resource提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)。

3 dba role(数据库管理员角色)

system由dba用户拥有。dba role拥有所有的系统权限.包括无限制的空间限额和给其他用户授予各种权限的能力。

授权命令

语法： grant connect, resource to 用户名;

grant connect,resource to sqluser01;

4.收回权限

revoke connect,resource from sqluser01;

5.创建/授权/删除角色

除了前面讲到的三种系统角色----connect、resource和dba，用户还可以在oracle创建自己的role。用户创建的role可以由表或系统权限或两者的组合构成。为了创建role，用户必须具有create role系统权限。Oracle 的角色存放在表 dba_roles 中，某角色包含的系统权限存放在 dba_sys_privs 中，包含的对象权限存放在 dba_tab_privs 中。实体权限分类

select, update, insert, alter, index, delete, all  //all包括所有权限
​
execute  //执行存储过程权限

1》创建角色

语法： create role 角色名;

例子： create role sqluser01role01;

2》授权角色

1.授予用户sqluser01role01登录权限（只能登陆数据库，以及查询一些public视图等）

grant create session to sqluser01role01;

2.授予sqluser01role01建表权限

grant create table to sqluser01role01;

3.授予sqluser01role01建视图权限

grant create view to sqluser01role01;

4.授予sqluser01role01操作表空间的权限

grant unlimited tablespace to sqluser01role01;

5.授予sqluser01role01建类型权限

grant create type to sqluser01role01;

6.授予sqluser01role01创建序列的权限

grant create sequence to sqluser01role01;

7.授予sqluser01role01创建触发器的权限

grant create trigger to sqluser01role01;

8.授予sqluser01role01创建存储过程的权限

grant create any procedure to sqluser01role01;

9.授予sqluser01role01执行存储过程的权限

grant execute any procedure to sqluser01role01;

10.授予sqluser01role01对class表的select查询权限

语法： grant select on class to 角色名;

列子： grant select on class to sqluser01role01;

3》收回权限：

语法： revoke connect, resource from 角色名;

列子： revoke connect, resource from sqluser01role01;

3》删除角色

语法： drop role 角色名;

例子： drop role sqluser01role01;

4》查看角色权限

1查询当前用户拥有的全部角色的全部信息

     select * from user_role_privs; 

2.查询当前用户拥有的权限全部信息

    select * from role_sys_privs;

3.创建表

1.使用sequence实现自增

1.语法：

create sequence SQE_TEST--名字
minvalue 10000--最小值
maxvalue 99999999999--最大值
start with 10000--起始值
increment by 1--自增数量
cache 20;--定义存放序列的内存块的大小，默认为20。对序列进行内存缓冲，可以改善序列的性能。
--nocache;--表示不对序列进行内存缓冲。

2.查询自增序列：

创建完成sequence,必须先使用nextval

1.查看当前的序列：

select SQE_TEST.currval from dual;

2.查看下一个自增序列

select SQE_TEST.nextval from dual;

3.创表语法(alter语法)

create table **sqluser01t1**(--黑体为表名
​
id number not null,
​
name varchar2(8),
​
sex varchar2(2) check(sex='男' or sex='女'),
​
primary key(id)
​
);
create table sqluser01test2(
id number not null,
fid number,
name varchar2(8),
sex varchar2(2) check(sex='男' or sex='女'),
primary key(id,fid)
);

1.添加 name约束：name不为空；

alter table sqluser01test1 modify name varchar2(8) not null;

测试语句：

insert into sqluser01test1 values(1,'test','男');
insert into sqluser01test1 values(1,'男');

2.表t2添加外码ti的id:

表外：

alter table sqluser01test2 add foreign key(fid) references sqluser01test1(id);  

或者：

drop table sqluser01test2;
create table sqluser01test2(
id number not null,
fid number,
name varchar2(8),
sex varchar2(2) check(sex='男' or sex='女'),
primary key(id,fid),
foreign key(fid) references  sqluser01test1(id)--添加
);

3.alter对表字段操作：

1.先对表2添加字段 testfild：

alter table sqluser01test2 add testfild varchar2(8);--单字段
alter table sqluser01test2 add (testfild varchar2(8),testfild2 number(8));--多字段

2.对表2修改字段 testfild：

alter table sqluser01test2 modify testfild varchar2(10) notnull;--单字段
alter table sqluser01test2 modify (testfild varchar2(10),testfild2 number(10));--多字段

3.对表2删除字段 testfild：

alter table sqluser01test2 drop (testfild);--注意小括号
alter table sqluser01test2 drop (testfild,testfild2);--多字段

4.对主键约束

对表2取消外键作为主码：

1.判断主码约束是否有名字：

没有名字：

对于表2：

create table sqluser01test2(
id number not null,
fid number,
name varchar2(8),
sex varchar2(2) check(sex='男' or sex='女'),
primary key(id,fid),
foreign key(fid) references  sqluser01test1(id)--添加
);

1.查询主键名：

SELECT * from user_cons_columns where table_name='SQLUSER01TEST2';--表名

结果为：

OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME SQLUSER01 SYS_C0012207 SQLUSER01TEST2 ID SQLUSER01 SYS_C0012208 SQLUSER01TEST2 SEX SQLUSER01 SYS_C0012209 SQLUSER01TEST2 ID SQLUSER01 SYS_C0012209 SQLUSER01TEST2 FID SQLUSER01 SYS_C0012210 SQLUSER01TEST2 FID

主键名字：SYS_C0012209

2.依据主键名字，删除主键：

alter table sqluser01test2 drop constraint SYS_C0012209;

3.添加主键(自定义主键名)

alter table sqluser01test2 add constraint pk_student primary key(id);

有名字的：

create table sqluser01test2(
id number not null,
fid number,
name varchar2(8),
sex varchar2(2) check(sex='男' or sex='女'),
constraints pk_sqluser01test2  primary key(id),
foreign key(fid) references  sqluser01test1(id)--添加
);

1.依据主键名字，删除主键：

alter table sqluser01test2 drop constraint pk_sqluser01test2;

2.添加主键(自定义主键名)

alter table sqluser01test2 add constraint pk_sqluser01test2 primary key(id,fid);

4.常用函数：

1.时间函数：

查询当前时间：

select sysdate from dual;

2.日期转换字符串：

to_char(date,formate)

例如：

把当前时间转换成字符串：

to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')；

3.字符串转换成日期的函数：

to_date(str,formate)

例如：

1.字符串2021-7-21 00:11:23转成时间

to_date('2021-7-21 00:11:23'), 'yyyy-mm-dd hh24:mi:ss');

2.把当前时间转化成字符串，把字符串转化成时间：

to_date(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');

例如：对表SQE_DATETESTT1插入时间，

create sequence SQE_DATETESTT1
minvalue 10000
maxvalue 99999999999
start with 10000
increment by 1
cache 20 ;
​
create table datetestt1(
id number,
birdate date,
name varchar2(20),
constraint pk_datetestt1 primary key(id)
);
​
insert into datetestt1 values(SQE_DATETESTT1.nextval,sysdate,'test');

4.最大值：

Max(coloms)

select 学号,Max(成绩) as 最高分 from grade group by 学号 order by 学号;
select 课程号,Max(成绩) as 最高分 from grade group by 课程号 order by 课程号;

5.最小值：

Min()

select 学号,Min(成绩) as 最低分 from grade group by 学号 order by 学号;
select 课程号,Min(成绩) as 最低分 from grade group by 课程号 order by 课程号;

6.平均值：

Avg()

select 学号,Avg(成绩) as 学生平均成绩 from grade group by 学号 order by 学号;
select 课程号,Avg(成绩) as 课程平均成绩 from grade group by 课程号 order by 课程号;