初级DBA需要掌握的常用数据库操作sql

数据库操作

记录一下数据库的安装和一些操作（自用）

Oracle

直接导出导入

导出来之前，先清理一下空表的缓存，不然会出现空表导不出来的情况；

select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0
# 执行了上面的sql语句后会显示结果，复制所有结果到新建查询中执行一次
# 执行了两次sql语句后再用导出

select 'alter table '||table_name||' allocate extent(size 64k);'
     from tabs t
     where not exists (select segment_name from user_segments s where s.segment_name=t.table_name);

Copy

导出

exp digital/digital@127.0.0.1/orcl file="D:\orclbak\digital.DMP" log="D:\orclbak\digital.txt"

Copy

导入

imp XJST_ESTATE/XJST_ESTATE@127.0.0.1/orcl file="D:\orclbak\estate.dmp"  log="D:\orclbak\digital3.txt ignore=y STATISTICS=NONE FULL=Y

Copy

数据泵导出导入

创建存放目录

create or replace directory cwc1 as 'D:\5数据库备份';
create or replace directory cwc1 as '/tmp/dumpdata/';

Copy

在system用户中给导入的用户授权；

Grant read,write on directory cwc1 to xx2;

Copy

导出

expdp DXESTATE_TEST/DXESTATE_TEST@127.0.0.1/orcl directory=cwc1 dumpfile=DXESTATE_TEST.DMP logfile=DXESTATE_TEST.log schemas=DXESTATE_TEST

Copy

创建表空间、用户

Create tablespace REGISTERv1 datafile
'D:\app\Administrator\oradata\orcl\REGISTERv1.DBF'size 1024m autoextend
on next 100M;

create or replace directory bao1 as 'D:\database_bak';
Grant read,write on directory bao1 to REGISTERv1_user;

Copy

导入

impdp REGISTERv1/REGISTERv1@orcl dumpfile=DXESTATE_TEST.DMP directory=bao remap_schema=DXESTATE_TEST:REGISTERv1 remap_tablespace=DXESTATE_TEST:REGISTERv1

Copy

删除表空间

–删除空的表空间，但是不包含物理文件

drop tablespace tablespace_name;

Copy

–删除非空表空间，但是不包含物理文件

drop tablespace tablespace_name including contents;

Copy

–删除空表空间，包含物理文件

drop tablespace tablespace_name including datafiles;

Copy

–删除非空表空间，包含物理文件

drop tablespace tablespace_name including contents and datafiles;

Copy

–如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS

drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;
drop user u_name cascade;

Copy

删除实例

确认当前数据库是否要删除

select name from v$database;

Copy

关闭数据库，将数据库启动成mount状态

shutdown immediate;
tartup restrict mount;

Copy

删除数据库

drop database;
-- （10g及以后版本适用）
-- 它只删除了数据库文件（控制文件、数据文件、日志文件）
-- 但并不删除$ORACLE_BASE/admin/$ORACLE_SID目录下的文件
-- 也不会删除初始化参数文件及密码文件。

Copy

删除数据库的管理/跟踪信息

rm $ORACLE_BASE/admin/$ORACLE_SID

Copy

清理oratab文件中的数据库信息，注释掉和数据库实例有关的行

vim /etc/oratab

Copy

删除初始化参数文件和密码文件

find $ORACLE_HOME| grep spfileorcl.ora
find $ORACLE_HOME | grep orapworcl

Copy

常用SQL语句

启动停止

shutdown immediate;
shutdown abort  -- 停止所有实例，要慎用
startup nomount
startup;

Copy

查看字符集

select * from nls_instance_parameters where parameter='NLS_CHARACTERSET';
select * from nls_instance_parameters;

Copy

查看用户下有多少张表

select count (*) from user_tables;

Copy

查看用户的表数量和数据量

select count(*) from all_tables a where a.OWNER = upper('cx_estate')
select sum(bytes)/1024/1024/1024  from dba_segments s where s.owner='GSST_FMS'

Copy

查看用户的表

select t.*, t.rowid from UUMS_USER t
select REAL_NAME, t.rowid from UUMS_USER t

Copy

复制新表

create table file_info_bak_ as select * from file_info;

Copy

批量替换

update FILE_INFO set FILE_PATH_URL = replace(FILE_PATH_URL,'\','/');

Copy

将一个表的数据插入到另一个表

insert into file_info select * from FILE_INFO_JQ_0629 a where not exists (select 1 from file_info b where a.id = b.id);

Copy

对比两个表去重

select distinct FIRST_PAGES from t_wf_biz_file_type_jq_0629 t

Copy

查询被删除的表（回收站）

select * from dba_recyclebin r where r.type='TABLE'  order by r.droptime desc;

Copy

建表和建账号

--创建表空间
create tablespace hl_estate
logging
datafile 'D:\app\Administrator\oradata\orcl\hl_estate.dbf'
size 50m
autoextend on
next 50m maxsize 32000m
extent management local;
--创建用户
create user hl_estate identified by hl_estate;
--授于用户至表空间
alter user hl_estate default tablespace hl_estate;
--给用户赋权限
GRANT CONNECT TO hl_estate WITH ADMIN OPTION;  --connect是赋予连接数据库的权限
GRANT dba TO hl_estate WITH ADMIN OPTION;     --dba是赋予数据库管理员权限
grant resource to hl_estate;--resource是赋予用户只可以创建实体但是没有创建数据结构的权限
grant create session to hl_estate;--这个是给用户赋予登录的权限。
grant create table to  hl_estate;--给用户赋予表操作的权限
grant select any table to hl_estate;--给该用户赋予访问任务表的权限,同理可以赋予update 和delete 的
GRANT UNLIMITED TABLESPACE TO hl_estate WITH ADMIN OPTION; --给用户赋予表空间操作的权限
grant create  view to hl_estate;    --创建视图权限，只授权第一个是无法创建 视图的，需要三个权限都授予
grant select any table to hl_estate;  --授予查询权限
grant select any dictionary to hl_estate;  --授予权限

Copy

Oracle归档日志

创建归档日志文件夹，设置文件权限，

mkdir -p /data/oracle/arch && chown -R oracle:oinstall /data/oracle/arch
cd $ORACLE_HOME/bin && chmod 6751 oracle

Copy

设置归档日志

-- 查看实例状态，查看归档日志状态
select instance_name,status from v$instance;
archive log list;

-- 停止实例，开启归档日志，设置归档日志目录
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
alter system set log_archive_dest_1='location=/data/oracle/arch' scope =both;

Copy

删除归档日志

rman target /
crosscheck archivelog all;
delete force noprompt archivelog all completed before 'sysdate-15';
exit;

Copy

Oracle杀死死锁

用plsql查询死锁，将结果全部复制再执行一次就好了

select 'alter system kill session '''||sid||','||serial#||''';' from(
select b.username, b.sid, b.serial#, logon_time
  from v$locked_object a, v$session b
 where a.session_id = b.sid
 order by b.logon_time  )

 where username='oracle用户名'

Copy

SQLserver

导库

这个比oracle导出导入简单多了，一个SQLserver服务器上一般只用一个用户

2.1.进入数据库，这里新建一个数据库，导出的数据需要导入到这个新数据库中；

2.2.选择数据库右键导出，导出的时候可以选择sqlserver账号和windows账号，再选需要导出的数据库，下一步，再选导入的数据库和账号密码，继续点下一步，选所有要导入的表就行了

Mongo

导入导出

# 导出数据库
mongodump -h 47.92.237.158:22000 -d iebdc_hebei_dev -o /hua/
mongodump -h 127.0.0.1:22000 -d iebdc_weinan -u iebdc_weinan -p hBUE49vGMpn -o D:\mongobak\

# 导出后要进入mongo创建数据库，账号和密码
> use iebdc
>
> 创建账户密码
>
> db.createUser({ user:"ww_internet", pwd:"ww_internet", roles: [ { role: "readWrite", db: "ww_internet" } ] } )

# 创建完成后，执行以下命令进行验证，返回1，表示创建成功
> db.auth('log','log')
> 1

# 导入数据库
mongorestore -h 119.3.229.205:22000 -d iebdc_hainan_internet /hua/
mongorestore -h 127.0.0.1:22000 -d shangluo_internet -u shangluo_internet -p hBUE49vGMpn  D:\mongobak\iebdc_weinan\

Copy

PGSQL

安装PGSQL

yum install -y make zlib zlib-devel gcc-c++ libtool openssl openssl-devel unzip net-tools wget vim telnent tree pstree cmake telnet tree pstree cmake xz
yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install postgresql12-server
yum -y install postgresql12-server
/usr/pgsql-12/bin/postgresql-12-setup initdb
systemctl start postgresql-12

Copy

修改配置文件允许所有人连接数据库

vim /var/lib/pgsql/12/data/postgresql.conf
# 修改成listen_addresses = '*'

Copy

修改第二个配置文件

vim /var/lib/pgsql/12/data/pg_hba.conf
# 大概在86行左右把IPv4的host后面的地址改成0.0.0.0/0
# 84行后面也要改成md5，不然其他的数据库用户连不上

Copy

改完了要重启服务

systemctl restart postgresql-12

Copy

数据库配置

切换到postgres用户下改密码

su postgres
psql -U postgres

Copy

-- 进入到postgres用户下以后，输入\password修改密码
\password

-- 查看数据库版本
select version();

Copy

创建用户，创建数据库

-- 创建新用户
CREATE USER user1 WITH PASSWORD '123456';
-- 创建数据库
CREATE DATABASE ecologyrepair;
-- 将数据库权限给这个新用户
GRANT ALL PRIVILEGES ON DATABASE ecologyrepair TO user1;
-- 将表的权限给这个新用户
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user1;
GRANT ALL ON tablename TO user1;

Copy

导入数据

如果导出的是sql语句，就可以用psql来导入

psql -U user1 -W -d ecologyrepair -f /data/pgsql_dump/ecologyrepair.sql

Copy

用–help查看帮助

psql是PostgreSQL 的交互式客户端工具。
使用方法:
  psql [选项]... [数据库名称 [用户名称]]
通用选项:
  -c, --command=命令       执行单一命令(SQL或内部指令)然后结束
  -d, --dbname=DBNAME      指定要连接的数据库 (默认："root")
  -f, --file=文件名        从文件中执行命令然后退出
  -l, --list               列出所有可用的数据库,然后退出
  -v, --set=, --variable=NAME=VALUE
                           设置psql变量NAME为VALUE
                           (例如，-v ON_ERROR_STOP=1)
  -V, --version            输出版本信息, 然后退出
  -X, --no-psqlrc          不读取启动文档(~/.psqlrc)
  -1 ("one"), --single-transaction
                           作为一个单一事务来执行命令文件(如果是非交互型的)
  -?, --help[=options]     显示此帮助，然后退出
      --help=commands      列出反斜线命令，然后退出
      --help=variables     列出特殊变量，然后退出

输入和输出选项：
  -a, --echo-all           显示所有来自于脚本的输入
  -b, --echo-errors        回显失败的命令
  -e, --echo-queries       显示发送给服务器的命令
  -E, --echo-hidden        显示内部命令产生的查询
  -L, --log-file=文件名    将会话日志写入文件
  -n, --no-readline        禁用增强命令行编辑功能(readline)
  -o, --output=FILENAME    将查询结果写入文件(或 |管道)
  -q, --quiet              以沉默模式运行(不显示消息，只有查询结果)
  -s, --single-step        单步模式 (确认每个查询)
  -S, --single-line        单行模式 (一行就是一条 SQL 命令)

输出格式选项 :
  -A, --no-align           使用非对齐表格输出模式
      --csv                CSV（逗号分隔值）表输出模式
  -F, --field-separator=STRING
                           为字段设置分隔符,用于不整齐的输出(默认："|")
  -H, --html               HTML 表格输出模式
  -P, --pset=变量[=参数]   设置将变量打印到参数的选项(查阅 \pset 命令)
  -R, --record-separator=STRING
                           为不整齐的输出设置字录的分隔符(默认：换行符号)
  -t, --tuples-only        只打印记录i
  -T, --table-attr=文本    设定 HTML 表格标记属性（例如,宽度,边界)
  -x, --expanded           打开扩展表格输出
  -z, --field-separator-zero
                           为不整齐的输出设置字段分隔符为字节0
  -0, --record-separator-zero
                           为不整齐的输出设置记录分隔符为字节0

联接选项:
  -h, --host=主机名        数据库服务器主机或socket目录(默认："本地接口")
  -p, --port=端口          数据库服务器的端口(默认："5432")
  -U, --username=用户名    指定数据库用户名(默认："root")
  -w, --no-password        永远不提示输入口令
  -W, --password           强制口令提示 (自动)

更多信息，请在psql中输入"\?"(用于内部指令)或者 "\help"(用于SQL命令)，
或者参考PostgreSQL文档中的psql章节.