对日常工作中使用 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：该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器。

例如：

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   --无限表空间

常用对象权限

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;

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

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 link 名 connect 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 可以回滚。