Oracle與MySql知識匯總
sqlplus 連接數據庫的方式
1> cmd 中輸入：sqlplus 用戶名/密碼@數據庫實例
如：sqlplus system/lxr316@oracle,
sqlplus sys/admin as sysdba
2> 打開sqlplus 直接輸入用戶名和密碼
3> 使用命令：connect sys/admin as sysdba,
connect system/lxr316
4> 超級管理員登錄：sys as sysdba
斷開數據庫：disconn(ect);
MySql連接
cmd中輸入：mysql [–h服務器地址] –u用戶名 –p[密碼]（需要配置mysql數據庫的bin到環境變量中）
如：mysql -hlocalhost –uroot –proot
mysql –uroot -p
用戶操作
Oracle:
1> 創建用戶
create user 名稱 identified by 密碼；
2> 修改用戶密碼
alter user 用戶名 identified by 新密碼;
3> 賬戶上鎖、解鎖
alter user 用戶名 account lock|unlock;
4> 用戶首次登錄時直接修改密碼–密碼失效
alter user 用戶名 password expire;
MySql:
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’@’%’;
2> 設置與更改用戶密碼
SET PASSWORD FOR ‘username’@’host’=PASSWORD(‘newpassword’);
如果是當前登陸用戶用：SET PASSWORD = PASSWORD(“newpassword”);
例子: SET PASSWORD FOR ‘pig’@’%’ = PASSWORD(“123456”);
3> 刪除用戶
DROP USER ‘username’@’host’;
權限管理
Oracle:
1> 授權
grant 權限 to 用戶名;
grant all on 表名 to 用戶/角色
2> 收回權限
revoke 權限 from 用戶名;
對象權限可以級聯收回，但是系統權限不可以級聯收回。
(1) 系統權限: A賦予一個系統權限給B,B再授予C.然後A將該權限從B回收. 此後,C仍然有該權限.
(2) 對象權限: A賦予一個對象權限給B,B再授予C.然後A將該權限從B回收. 此後,C也失去了該權限
3> 常用系統權限
create session –用戶登錄
create table –創建表
unlimited tablespace –無限表空間
4> 對象權限
select on 表名
update on 表名
delete on 表名
insert on 表名
5> 權限的級聯授予
系統權限 with admin option
對象權限 with grant option
grant 權限 to 用戶名 with admin option|with grant option;
MySql:
1> 授權:
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;
2> 撤銷用戶權限
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 ON test.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;
2> 授權給角色
grant create any table,create procedure to role1;
3> 授予/回收用戶角色
grant role1 to user1;
revoke role1 from user1;
4> 創建帶有口令以角色(在生效帶有口令的角色時必須提供口令)
create role role1 identified by password1;
5> 修改角色：是否需要口令
alter role role1 not identified;
alter role role1 identified by password1;
6> 設置當前用戶要生效的角色
set role role1;//使role1生效
set role role1,role2;//使role1,role2生效
//使用帶有口令的role1生效
set role role1 identified by password1;
set role all;//使用該用戶的所有角色生效
set role none;//設置所有角色失效
//除role1外的該用戶的所有其它角色生效。
set role all except role1;
select * from SESSION_ROLES;//查看當前用戶的生效的角色。
7> 修改指定用戶，設置其默認角色
alter user user1 default role role1;
alter user user1 default role all except role1;
8> 刪除角色
drop role role1;
角色刪除後，原來擁用該角色的用戶就不再擁有該角色了，相應的權限也就沒有了。
9> 系統角色
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 databases;
查看表：show tables;
Oracle用戶鎖定與解鎖
ALTER USER username ACCOUNT LOCK;–鎖定用戶
ALTER USER username ACCOUNT UNLOCK;–解鎖用戶
Sqlplus清屏命令
clear screen 簡寫:cl scr (dos清屏命令: cls)
Oracle更改當前用戶日期格式命令
alter session set nls_date_format=’yyyy-mm-dd’;
Oracle中的基本數據類型
數值 number(5,2), int: 999.99
字符 char,varchar2(4000)
日期 date
圖片 BLOB (binaryLargeObject) 4G
文本 CLOB (Character LargeObject) 4G
Oracle表結構操作
1> 表重命名
alter table 表名 rename to 新表名;
2> 給表增加注釋
comment on table表名 is ’注釋內容’;
3> 添加約束方式
a) alter table 表名 add constraint 約束名 約束類型(約束的字段名);
constraint約束類型：
primary key 主鍵
unique 唯一
check 限制
not null 不能為null
b) alter table 表名 add constraint 約束名 foreign key(字段名) references 表名(字段名);
foreign key 外鍵
參照主鍵中存在的值，可以插入重復的記錄、可以插入重復的空值
4> 刪除約束方式
alter table 表名 drop constraint 約束名;
5> 刪除表結構
drop table 表名;(此操作屬DDL，會自動提交且不可回滾)
6> 表中增加字段
alter table 表名 add 字段名 類型;
7> 刪除字段
alter table 表名 drop(字段名);
(通常在系統不忙的時候刪除不使用的字段，可以先設置字段為unused:
alter table test3 set unused column address;
再執行刪除：
alter table test3 unused column;)
8> 字段重命名
alter table 表名 rename column 字段名 to 新字段名;
9> 修改字段
alter table 表名 modify 字段名 新類型;
–添加not null
alter table 表名 modify 字段名 not null
–刪除not null
alter table 表名 modify 字段名 null;
Oracle備份表
1> 在當前的數據庫之內進行備份
create table 表名(字段) as select 查詢語句
數據的移動
insert into 表名（字段列表） select 字段列表 from 表名
2> 數據庫服務器之間拷貝表
知識點：客戶端連接服務器
copy from system/hhl@hhl create hhl_table using select * from scott.emp;
a) 從A服務器拷貝到自己的數據庫中
copy from 用戶名/密碼@主機字符串 create 表名 using 查詢語句;
b) 從自己的數據庫中拷貝到A服務器
copy to 用戶用/密碼@主機字符串 create 表名 using 查詢語句;
c) 從A服務器拷貝表到B服務器
copy from 用戶用/密碼@主機字符串 to 用戶用/密碼@主機字符串 create 表名 using 查詢語句;
Oracle DBLINK 數據庫連接
在當前的數據庫內直接操作其他服務器中的表做增刪改查，格式如下：
create database link 名 connect to 用戶名 identified by 密碼 using ‘主機字符串’;
MySql查看數據庫字符集：
show variables like ‘character%’;
show variables like ‘%collation%’;
truncate與delete
1> 使用格式
truncate table 表名;–刪除表中全部記錄s
delete from 表名;
2> truncate 與delete 的區別
truncate 刪除速度比delete刪除速度快
truncate 不可以回滾，delete 可以回滾。
