Oracle 与 MySQL 知识总结
对日常工作中使用 Oracle 和 MySQL 数据库知识对比总结。
SQLPlus 连接数据库的方式
cmd 中输入：sqlplus 用户名/密码@数据库实例
sqlplus system/lxr316@oracle
sqlplus sys/admin as sysdba
打开 SQLPlus 直接输入用户名和密码
使用命令
connect sys/admin as sysdba
connect system/lxr316
超级管理员登录
sys as sysdba
断开数据库
disconnect; # 可简写 disconn
MySQL 连接
命令： mysql [–h 服务器地址] –u 用户名 –p [密码]（需要配置 mysql 数据库的 bin 到环境变量中）
mysql -h localhost –u root –p root
mysql –u root -p
用户操作
Oracle
创建用户
create user [username] identified by [password];
修改用户密码
alter user [username] identified by [password];
账户上锁、解锁
alter user [username] account lock|unlock;
用户首次登录时直接修改密码–密码失效
alter user [username] password expire;
MySQL
创建用户:
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'@'%';
设置与更改用户密码
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
如果是当前登陆用户
SET PASSWORD = PASSWORD("newpassword");
删除用户
DROP USER 'username'@'host';
权限管理
Oracle
授权
grant 权限 to 用户名;
grant all on 表名 to用户/角色
收回权限
revoke 权限from 用户名;
对象权限可以级联收回，但是系统权限不可以级联收回。
- 系统权限： A 赋予一个系统权限给 B，B 再授予 C。然后A将该权限从 B 回收，此后 C 仍然有该权限。
- 对象权限： A 赋予一个对象权限给 B，B 再授予 C。然后A将该权限从 B 回收，此后 C 也失去了该权限。
常用系统权限
create session --用户登录
create table --创建表
unlimited tablespace --无限表空间
常用对象权限
select on 表名; -- 查询权限
update on 表名; -- 更新权限
delete on 表名; -- 删除权限
insert on 表名; -- 插入权限
权限的级联授予
grant 权限 to 用户名 with admin option|with grant option;
- with admin option：系统权限
- with grant option：对象权限
MySQL
授权:
GRANT privileges ON databasename.tablename TO 'username'@'host'
备注：
- privileges：用户的操作权限,如 SELECT , INSERT , UPDATE 等；如果要授予所的权限则使用 ALL。
- databasename：数据库名。
- tablename：表名
如果要授予该用户对所有数据库和表的相应操作权限则可用 * 表示, 如 *.* 。
例如：
GRANT SELECT, INSERT ON test.user TO 'pig'@'%';
GRANT ALL ON *.* TO 'pig'@'%';
注意： 用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
撤销用户权限
REVOKE privilege ON databasename.tablename FROM 'username'@'host';
说明： privilege, databasename, tablename - 同授权部分。 例如：
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
建一个角色
create role role1;
授权给角色
grant create any table, create procedure to role1;
授予/回收用户角色
grant role1 to user1;
revoke role1 from user1;
创建带有口令以角色
在生效带有口令的角色时必须提供口令
create role role1 identified by password1;
修改角色：是否需要口令
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; -- 查看当前用户的生效的角色。
修改指定用户，设置其默认角色
alter user user1 default role role1;
alter user user1 default role all except role1;
删除角色
drop role role1;
注意： 角色删除后，原来拥用该角色的用户将失去该角色，相应的权限也将会失去。
系统角色
- DBA: 拥有全部特权，是系统最高权限角色
- RESOURCE：拥有 RESOURCE 角色权限的用户只可以创建实体
- CONNECT：拥有 CONNECT 角色权限的用户只可以登录 Oracle
对于普通用户：授予 CONNECT，RESOURCE 角色权限；对于DBA管理用户：授予 CONNECT，RESOURCE， DBA 角色权限。
查看表结构信息命令
Oracle
desc(ribe) 表名; -- 如：desc lxr
MySQL
desc(ribe) 表名;
显示当前登录用户
Oracle
show user;
MySQL
select user();
MySQL 查看用户下的数据库
show databases;
查看表
show tables;
Oracle 用户锁定与解锁
ALTER USER username ACCOUNT LOCK; -- 锁定用户
ALTER USER username ACCOUNT UNLOCK; -- 解锁用户
清屏命令
SQLPlus
clear screen -- 简写：cl scr
cls -- dos 清屏命令
MySQL
system clear; -- Linux 环境下
system cls; -- dos 环境下
Oracle 更改当前用户日期格式命令
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 表结构操作
表重命名
alter table 表名 rename to 新表名;
给表增加注释
comment on table 表名 is '注释内容';
添加约束方式
方式一
alter table 表名 add constraint 约束名 约束类型(约束的字段名);
Constraint 约束类型：
| 值 | 名称 |
|---|---|
| primary key | 主键 |
| unique | 唯一 |
| check | 限制 |
| not null | 不能为null |
方式二
alter table 表名 add constraint 约束名 foreign key(字段名) references 表名(字段名);
foreign key 外键： 参照主键中存在的值，可以插入重复的记录、可以插入重复的空值
删除约束方式
alter table 表名 drop constraint 约束名;
删除表结构
drop table 表名; -- 此操作属DDL，会自动提交且不可回滚
表中增加字段
alter table 表名 add 字段名 类型;
删除字段
alter table 表名 drop 字段名;
通常在系统不忙的时候删除不使用的字段，可以先设置字段为 unused
alter table test3 set unused column address;
再执行删除
alter table test3 unused column;
字段重命名
alter table 表名 rename column 字段名 to 新字段名;
修改字段
alter table 表名 modify 字段名 新类型;
添加 not null
alter table 表名 modify 字段名 not null
删除 not null
alter table 表名 modify 字段名 null;
Oracle 备份表
在当前的数据库之内进行备份
create table 表名(字段) as select 查询语句;
数据的移动
insert into 表名（字段列表）select 字段列表 from 表名;
数据库服务器之间拷贝表
客户端连接服务器
copy from system/hhl@hhl create hhl_table using select * from scott.emp;
从A服务器拷贝到自己的数据库中
copy from 用户名/密码@主机字符串 create 表名 using 查询语句;
从自己的数据库中拷贝到A服务器
copy to 用户用/密码@主机字符串 create 表名 using 查询语句;
从A服务器拷贝表到B服务器
copy from 用户用/密码@主机字符串 to 用户用/密码@主机字符串 create 表名 using 查询语句;
OracleDBLINK 数据库连接
在当前的数据库内直接操作其他服务器中的表做增删改查，格式如下：
create database link 名 connect to 用户名 identified by 密码 using '主机字符串';
MySQL 查看数据库字符集
show variables like 'character%';
show variables like '%collation%';
truncate 与 delete
使用格式
truncate table 表名; -- 删除表中全部记录
delete from 表名;
truncate 与 delete 的区别
- truncate 删除速度比 delete 删除速度快；
- truncate 不可以回滚，delete 可以回滚。