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 课程号;