Oracle与MySql知识汇总

1. 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);
2. MySql连接
   cmd中输入：mysql [–h服务器地址] –u用户名 –p[密码]（需要配置mysql数据库的bin到环境变量中）
   如：mysql -hlocalhost –uroot –proot
   mysql –uroot -p
3. 用户操作
   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’;
4. 权限管理
   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’@’%’; 查看。
5. 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角色权限。
6. 查看表结构信息命令
   Oracle:
   desc(ribe) 表名 如：desc lxr
   MySql:
   desc(ribe) 表名;
7. 显示当前登录用户：
   Oracle:show user
   MySql:select user();
8. MySql查看用户下的数据库：show databases;
   查看数据库：show databases;
   查看表：show tables;
9. Oracle用户锁定与解锁
   ALTER USER username ACCOUNT LOCK;–锁定用户
   ALTER USER username ACCOUNT UNLOCK;–解锁用户
10. Sqlplus清屏命令
    clear screen 简写:cl scr (dos清屏命令: cls)
11. Oracle更改当前用户日期格式命令
    alter session set nls_date_format=’yyyy-mm-dd’;
12. Oracle中的基本数据类型
    数值 number(5,2), int: 999.99
    字符 char,varchar2(4000)
    日期 date
    图片 BLOB (binaryLargeObject) 4G
    文本 CLOB (Character LargeObject) 4G
13. 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;
14. 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 查询语句;
15. Oracle DBLINK 数据库连接
    在当前的数据库内直接操作其他服务器中的表做增删改查，格式如下：
    create database link 名 connect to 用户名 identified by 密码 using ‘主机字符串’;
16. MySql查看数据库字符集：
    show variables like ‘character%’;
    show variables like ‘%collation%’;
17. truncate与delete
    1> 使用格式
    truncate table 表名;–删除表中全部记录s
    delete from 表名;
    2> truncate 与delete 的区别
    truncate 删除速度比delete删除速度快
    truncate 不可以回滚，delete 可以回滚。