Oracle 与 MySQL 知识总结

对日常工作中使用 Oracle 和 MySQL 数据库知识对比总结。

SQLPlus 连接数据库的方式

cmd 中输入:sqlplus 用户名/密码@数据库实例

1
2
sqlplus system/lxr316@oracle
sqlplus sys/admin as sysdba

打开 SQLPlus 直接输入用户名和密码

使用命令

1
2
connect sys/admin as sysdba
connect system/lxr316

超级管理员登录

1
sys as sysdba

断开数据库

1
disconnect; # 可简写 disconn

MySQL 连接

命令: mysql [–h 服务器地址] –u 用户名 –p [密码]需要配置 mysql 数据库的 bin 到环境变量中

1
2
mysql -h localhost –u root –p root
mysql –u root -p

用户操作

Oracle

创建用户

1
create user [username] identified by [password];

修改用户密码

1
alter user [username] identified by [password];

账户上锁、解锁

1
alter user [username] account lock|unlock;

用户首次登录时直接修改密码–密码失效

1
alter user [username] password expire;

MySQL

创建用户:

1
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

备注:

  • username:你将创建的用户名。
  • host:指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%。
  • password:该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器。

例如:

1
2
3
4
5
CREATE USER 'dog'@'localhost'IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101_'IDENDIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';

设置与更改用户密码

1
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

如果是当前登陆用户

1
SET PASSWORD = PASSWORD("newpassword");

删除用户

1
DROP USER 'username'@'host';

权限管理

Oracle

授权

1
2
grant 权限 to 用户名;
grant all on 表名 to用户/角色

收回权限

1
revoke 权限from 用户名;

对象权限可以级联收回,但是系统权限不可以级联收回。

  • 系统权限: A 赋予一个系统权限给 B,B 再授予 C。然后A将该权限从 B 回收,此后 C 仍然有该权限。
  • 对象权限: A 赋予一个对象权限给 B,B 再授予 C。然后A将该权限从 B 回收,此后 C 也失去了该权限。

常用系统权限

1
2
3
create session --用户登录
create table --创建表
unlimited tablespace --无限表空间

常用对象权限

1
2
3
4
select on 表名; -- 查询权限
update on 表名; -- 更新权限
delete on 表名; -- 删除权限
insert on 表名; -- 插入权限

权限的级联授予

1
grant 权限 to 用户名 with admin option|with grant option;
  • with admin option:系统权限
  • with grant option:对象权限

MySQL

授权:

1
GRANT privileges ON databasename.tablename TO 'username'@'host'

备注:

  • privileges:用户的操作权限,如 SELECT , INSERT , UPDATE 等;如果要授予所的权限则使用 ALL。
  • databasename:数据库名。
  • tablename:表名

如果要授予该用户对所有数据库和表的相应操作权限则可用 * 表示, 如 *.*
例如:

1
2
3
GRANT SELECT, INSERT ON test.user TO 'pig'@'%';

GRANT ALL ON *.* TO 'pig'@'%';

注意: 用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:

1
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

撤销用户权限

1
REVOKE privilege ON databasename.tablename FROM 'username'@'host';

说明: privilege, databasename, tablename - 同授权部分。
例如:

1
REVOKE SELECT ON *.* FROM 'pig'@'%';

注意: 假如你在给用户 'pig'@'%' 授权的时候是这样的(或类似的):GRANT SELECT ON test.user TO 'pig'@'%'; 则在使用:REVOKE SELECT ON *.* FROM'pig'@'%'; 命令并不能撤销该用户对 test 数据库中 user 表的 SELECT 操作;相反,如果授权使用的是:GRANT SELECT ON *.* TO'pig'@'%'; 则:REVOKE SELECT ONtest.user FROM 'pig'@'%'; 命令也不能撤销该用户对 test 数据库中 user 表的 SELECT 权限。

具体信息可以用命令:SHOW GRANTS FOR 'pig'@'%'; 查看。

Oracle角色 role

角色:权限的集合

角色数据字典表

  • 查看当前用户中的角色:user_role_privs
  • 查看角色中的系统权限:role_sys_privs
  • 查看角色中的对象权限:role_tab_privs

建一个角色

1
create role role1;

授权给角色

1
grant create any table, create procedure to role1;

授予/回收用户角色

1
2
grant role1 to user1;
revoke role1 from user1;

创建带有口令以角色

在生效带有口令的角色时必须提供口令

1
create role role1 identified by password1;

修改角色:是否需要口令

1
2
alter role role1 not identified;
alter role role1 identified by password1;

设置当前用户要生效的角色

1
2
3
4
5
6
7
set role role1; -- 使 role1 生效
set role role1, role2; -- 使 role1, role2 生效
set role role1 identified by password1; -- 使用带有口令的 role1 生效
set role all; -- 使用该用户的所有角色生效
set role none; -- 设置所有角色失效
set role all except role1; -- 除 role1 外的该用户的所有其它角色生效
select * from SESSION_ROLES; -- 查看当前用户的生效的角色。

修改指定用户,设置其默认角色

1
2
alter user user1 default role role1;
alter user user1 default role all except role1;

删除角色

1
drop role role1;

注意: 角色删除后,原来拥用该角色的用户将失去该角色,相应的权限也将会失去。

系统角色

  • DBA: 拥有全部特权,是系统最高权限角色
  • RESOURCE:拥有 RESOURCE 角色权限的用户只可以创建实体
  • CONNECT:拥有 CONNECT 角色权限的用户只可以登录 Oracle

对于普通用户:授予 CONNECT,RESOURCE 角色权限;对于DBA管理用户:授予 CONNECT,RESOURCE, DBA 角色权限。

查看表结构信息命令

Oracle

1
desc(ribe) 表名; -- 如:desc lxr

MySQL

1
desc(ribe) 表名;

显示当前登录用户

Oracle

1
show user;

MySQL

1
select user();

MySQL 查看用户下的数据库

1
show databases;

查看表

1
show tables;

Oracle 用户锁定与解锁

1
2
ALTER USER username ACCOUNT LOCK; -- 锁定用户
ALTER USER username ACCOUNT UNLOCK; -- 解锁用户

清屏命令

SQLPlus

1
2
3
clear screen -- 简写:cl scr

cls -- dos 清屏命令

MySQL

1
2
system clear; -- Linux 环境下
system cls; -- dos 环境下

Oracle 更改当前用户日期格式命令

1
alter session set nls_date_format = 'yyyy-mm-dd';

Oracle 中的基本数据类型

  • 数值:number(5,2),int
  • 字符:char,varchar2(4000)
  • 日期:date
  • 图片:BLOB (binaryLargeObject) 4G
  • 文本:CLOB (Character LargeObject) 4G

Oracle 表结构操作

表重命名

1
alter table 表名 rename to 新表名;

给表增加注释

1
comment on table 表名 is '注释内容';

添加约束方式

方式一

1
alter table 表名 add constraint 约束名 约束类型(约束的字段名);

Constraint 约束类型:

名称
primary key主键
unique唯一
check限制
not null不能为null

方式二

1
alter table 表名 add constraint 约束名 foreign key(字段名) references 表名(字段名);

foreign key 外键: 参照主键中存在的值,可以插入重复的记录、可以插入重复的空值

删除约束方式

1
alter table 表名 drop constraint 约束名;

删除表结构

1
drop table 表名; -- 此操作属DDL,会自动提交且不可回滚

表中增加字段

1
alter table 表名 add 字段名 类型;

删除字段

1
alter table 表名 drop 字段名;

通常在系统不忙的时候删除不使用的字段,可以先设置字段为 unused

1
alter table test3 set unused column address;

再执行删除

1
alter table test3 unused column;

字段重命名

1
alter table 表名 rename column 字段名 to 新字段名;

修改字段

1
alter table 表名 modify 字段名 新类型;

添加 not null

1
alter table 表名 modify 字段名 not null

删除 not null

1
alter table 表名 modify 字段名 null;

Oracle 备份表

在当前的数据库之内进行备份

1
create table 表名(字段) as select 查询语句;

数据的移动

1
insert into 表名(字段列表)select 字段列表 from 表名;

数据库服务器之间拷贝表

客户端连接服务器

1
copy from system/hhl@hhl create hhl_table using select * from scott.emp;

从A服务器拷贝到自己的数据库中

1
copy from 用户名/密码@主机字符串 create 表名 using 查询语句;

从自己的数据库中拷贝到A服务器

1
copy to 用户用/密码@主机字符串 create 表名 using 查询语句;

从A服务器拷贝表到B服务器

1
copy from 用户用/密码@主机字符串 to 用户用/密码@主机字符串 create 表名 using 查询语句;

OracleDBLINK 数据库连接

在当前的数据库内直接操作其他服务器中的表做增删改查,格式如下:

1
create database linkconnect to 用户名 identified by 密码 using '主机字符串';

MySQL 查看数据库字符集

1
2
show variables like 'character%';
show variables like '%collation%';

truncate 与 delete

使用格式

1
2
truncate table 表名; -- 删除表中全部记录
delete from 表名;

truncate 与 delete 的区别

  • truncate 删除速度比 delete 删除速度快;
  • truncate 不可以回滚,delete 可以回滚。

欣赏此文?求鼓励,求支持!
显示 Disqus 评论
0%
Title - Artist
0:00