关于Oracle与MySQL遇到的问题以及解决方案

平时使用的比较多的数据库管理系统就是Oracle和MySQL，我在这里记录下使用过程中的遇到的问题以及解决方案，以备不时之需

Oracle

关于表空间

Oracle创建数据的代价还是比较大的，所以使用表空间的概念，每个表空间相互独立。这里记录的操作包括从创建表空间、创建用户并分配表空间、授权用户这一系列操作

创建表空间

create tablespace db_test  
datafile 'D:\app\Administrator\oradata\orcl\db_test.dbf'  --表空间文件位置
size 50m  --初始大小
autoextend on  --自动增长

创建用户并分配表空间

CREATE USER utest  --用户名
IDENTIFIED BY utestpwd  --密码
DEFAULT TABLESPACE db_test --分配表空间

授予一些权限给用户

grant create session to utest  --或者是CONNECT
grant RESOURCE to utest
--grant dba to utes

导入与导出

Oracle可以使用dmp文件、sql文件、数据泵方式进行导入与导出

Oracle导入`.dmp`文件

命令语句为 imp 用户名/密码@实例名 file=d:\data\x.dmp log=d:\data\x.log full=y ignore=y;

其中：file 表示待导入的dmp文件位置；
log 指定导入过程log的记录文件位置，注意，即使指定了将记录保存到log文件里，屏幕打印也不会关闭；

full 表示将dmp全部内容导入；

ignore 表示忽略表创建过程中的错误，比如表名已存在时将直接覆盖原来表的内容；

Oracle导出`.dmp`文件

exp有三种主要的方式（完全、用户、表）

1. 将数据库TEST完全导出，用户名system 密码manager 导出到D:\daochu.dmp中 (完全) DBA权限
　　exp system/manager@TEST file=d:\test.dmp full=y
2. 将数据库中system用户与sys用户下的对象导出 (用户)
　　exp system/manager@TEST file=d:\test.dmp owner=（system，sys）
3. 将数据库中的表table1 、table2导出 (表)，只导出表
　　exp system/manager@TEST file=d:\test.dmp tables=（table1，table2）
4. 将数据库中的表table1中的字段filed1以”00”打头的数据导出 
　　exp system/manager@TEST file=d:\test.dmp tables=（table1） query=\” where filed1 like ‘00%’\”
5. 只导出表table1结构，不导出数据
   exp userid=uname/pwd  tables=table1  file=d:\test.dmp rows=n;
上面是常用的导出，对于压缩我不太在意，用winzip把dmp文件可以很好的压缩。
不过在上面命令后面加上 compress=y 就可以了

Oracle导入.sql文件

@文件名

oracle 导入数据泵

创建与数据泵文件(也是个.dmp文件)相同的表空间
创建数据泵文件相同的用户并分配表空间
授予一些权限给用户(有时候还是需要dba权限)
创建一个目录，并分配读写权限

CREATE OR REPLACE DIRECTORY  BM as 'D:\bm'  --D:\bm 手动创建

grant read,write on directory 目录名 to 用户名
--revoke read,write on directory BM from als7zx04  --收回目录的读写权限

将数据泵文件放在目录下,这里是D:\bm 下
使用impdp导入

impdp 用户名/密码@数据库实例 directory=目录名 dumpfile='数据泵文件名'full=y ignore=y;  --数据泵文件名不要写全路径

系统查询

这里的查询不是说如何查询表，而是查询一些与系统有关的信息

oracle怎么查看用户属于哪个表空间

select username,default_tablespace from dba_users  where username='用户名'; --用户名大写

查询当前数据库实例名

select instance_name from v$instance;

数据库实例名(instance_name)用于对外部连接。在操作系统中要取得与数据库的联系，必须使用数据库实例名。比如我们作开发，要连接数据库，就得连接数据库实例名：

jdbc:oracle:thin:@localhost:1521:orcl（orcl就为数据库实例名）

查询表个数

select table_name from user_tables; --当前用户拥有的表
--select count(1) from tabs;
--select count(1) from user_tables;

select table_name from all_tables; --所有用户的表
select table_name from dba_tables; --包括系统表
select table_name from dba_tables where owner='用户名'

--类似的，除了表，还有视图、存储过程、同义词等等对象，使用[DBA|USER|ALL]_TABLES可以查看到你想要的对象

查看数据库对象,可以发现同名数据库对象被占用的问题

--对象名为GETORGNAME
SELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS WHERE OBJECT_NAME='GETORGNAME';

创建表空间

Oracle查看哪些表被锁了

SELECT object_name, machine, s.sid, s.serial#, logon_time, locked_mode
FROM gv$locked_object l, dba_objects o, gv$session s 
WHERE l.object_id　= o.object_id 
AND l.session_id = s.sid;

解除锁定

--alter system kill session 'sid,serial#'; 
ALTER system kill session '23,1647';

查询语句

这里是一些特殊的SELECT

oracle中把1,2,3显示成01,02

--oracle中把1,2,3显示成01,02,03等的数值怎么写
SELECT to_char(2,'fm00') FROM dual;  --结果 02
SELECT to_char(2,'fm999.00') FROM dual; --结果 2.00

格式化时间

select
cc.IDEXPIRY,cc.CUSTOMERID,ci.CUSTOMERNAME
from 
CUSTOMER_CERT cc,CUSTOMER_INFO ci
where cc.CUSTOMERID=ci.CUSTOMERID and cc.IDEXPIRY<=to_char(sysdate,'yyyy/mm/dd')
-- Oracle 使用to_char 将当前时间sysdate 格式化

Oracle查询上一月

select to_char(add_months(sysdate,-1),'yyyymm') from dual;

Oracle从一个日期中提取特定部分

--语法
extract (    

        { year | month | day | hour | minute | second }    

        | { timezone_hour | timezone_minute }    

        | { timezone_region | timezone_abbr }    

from { date_value | interval_value } )


--例子(从一个日期里提取年，月，日)
select  extract (year from sysdate) year, extract (month from sysdate) month, extract (day from sysdate) day from  dual;
--EXTRACT(MONTH  FROM TO_DATE(MIN(A1.EARLIESTDUEBILLPUTOUTMON),'YYYY-MM-DD'))

数据字典

查询表的表名,字段名,字段类型，注释

--如果还需要查询系统中的表 使用数据字典 dba_ 打头的数据字典
select 
user_tab_columns.table_name,user_tab_comments.comments as tablename,
user_tab_columns.COLUMN_NAME,user_col_comments.comments,
user_tab_columns.data_type||'('||user_tab_columns.data_length||')',
user_tab_columns.COLUMN_ID
from user_tab_columns,user_col_comments,user_tab_comments
where user_tab_columns.TABLE_NAME=user_tab_comments.table_name
and user_tab_columns.TABLE_NAME=user_col_comments.table_name
and user_tab_columns.COLUMN_NAME=user_col_comments.column_name 
and user_tab_columns.table_name like '表名%' order by user_tab_columns.COLUMN_ID  
--表名大写,或者使用upper('brc_21bank_custrisk')，以创建表的字段顺序(column_id)排序

压缩表

在create table的DDL语句后加入compress，这样创建出来的表可以对数据进行自动压缩节省空间，比较推荐

create table STUDENT.stuinfo
(
  stuid      varchar2(11) not null,
  stuname    varchar2(50) not null,
  sex        char(1) not null,
  age        number(2) not null
) compress;

创建索引时加上online

在创建索引时，特别是表已经存在被使用且存在数据的情况下，建立索引一定要加上online。因为建立索引会锁表，如果正在使用该表就会阻塞DML，而加上online在建立索引过程中就不会锁表，只是保证表结构不能修改

Create index idx_stu on stuinfo (stuname) online;

MySQL

导入与导出

MySQL中我使用最多的是sql文件的方式导入与导出

登录MySQL

mysql ‐uroot ‐proot

选中需要操作的数据库

user db1;

使用source命令导出

source C:\bak.sql

导入也需要先登录系统，并选中数据库(这个数据库需要提前建好，一个空数据库)

使用SOURCE命令

mysql ‐uroot ‐proot

create database db1;

user db1;

SOURCE 导入文件的路径;

查询语句

如果是null设置默认值

ifnull(表达式1,表达式2)方法 : 若表达式1的值为null,则返回表达式2的结果;若表达式1的值不为null,则返回表达式1的值 ifnull(age, 0)

SELECT COUNT(IFNULL(english,0)) FROM student3;

设置小数位数

可以使用cast(数值 as decimal(总位数,小数位))设置小数的位数

select cast(avg(price) as decimal(5,2)) from products;

数据字典

SELECT
	table_schema,
	table_name,
	column_name,
	column_type,
	column_comment
FROM
	information_schema.COLUMNS
WHERE
	table_schema = '数据库名';

增强版：

SELECT
	a.TABLE_SCHEMA AS '数据库名',
	a.TABLE_NAME AS '表名',
	b.TABLE_COMMENT AS '表备注',
	a.ORDINAL_POSITION AS '序号',
	a.COLUMN_NAME AS '字段名',
	a.COLUMN_TYPE AS '字段类型',
	a.COLUMN_DEFAULT AS '默认值',
	a.IS_NULLABLE AS '是否为空',
	a.COLUMN_COMMENT AS '字段备注',
	a.COLUMN_KEY AS '键',
	a.EXTRA AS '额外'
FROM
	information_schema.COLUMNS a
LEFT JOIN (
	SELECT
		TABLE_COMMENT,
		TABLE_NAME
	FROM
		information_schema.TABLES
	WHERE
		TABLE_SCHEMA = '数据库名'
) b ON a.TABLE_NAME = b.TABLE_NAME
WHERE
	a.TABLE_SCHEMA = '数据库名'
ORDER BY
	b.TABLE_NAME

`MYSQL`忘记密码

使用管理员运行cmd ---> net stop mysql 停止mysql服务
使用无验证方式启动mysql服务：mysqld --skip-grant-tables
打开新的cmd窗口，直接输入mysql，回车登录成功
user mysql;
set password for '用户名'@'主机名' = password('新密码');或者 update user set password = password('新密码') where user = '用户名';
关闭两个窗口
打开任务管理器，手动结束mysqld.exe进程
启动mysql服务 ---> net start mysql
使用新密码登录即可

posted @ 2019-04-18 19:37 OverZeal 阅读(1038) 评论(0) 编辑收藏举报

刷新页面返回顶部

OverZeal

关于Oracle与MySQL遇到的问题以及解决方案

Oracle

关于表空间

创建表空间

创建用户并分配表空间

授予一些权限给用户

导入与导出

Oracle导入.dmp文件

Oracle导出.dmp文件

Oracle导入.sql文件

oracle 导入数据泵

系统查询

oracle怎么查看用户属于哪个表空间

查询当前数据库实例名

查询表个数

查看数据库对象,可以发现同名数据库对象被占用的问题

创建表空间

Oracle查看哪些表被锁了

解除锁定

查询语句

oracle中把1,2,3显示成01,02

格式化时间

Oracle查询上一月

Oracle从一个日期中提取特定部分

数据字典

压缩表

创建索引时加上online

MySQL

导入与导出

查询语句

如果是null设置默认值

设置小数位数

数据字典

MYSQL忘记密码

公告

Oracle导入`.dmp`文件

Oracle导出`.dmp`文件

`MYSQL`忘记密码