关系型数据库基础操作（四）
复制一张表数据到另一张表
复制指定字段
Insert into Table2(a, c, d) select a,c,5 from Table1;
复制表中所有数据
Insert into Table2 select * from Table1;
备份表
SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。
SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。
select * into persons_backup from persons;
查询所有用户（必须以 sysdba 模式登录）
SQL> select distinct owner from all_objects;
OWNER
MDSYS
PUBLIC
OUTLN
CTXSYS
HR
FLOWS_FILES
SYSTEM
APEX_040000
XDB
SYS
查询所有用户和密码
SQL> SELECT name,password FROM user$;
NAME PASSWORD
SYS 303F2F4CFE7F35D6
PUBLIC
CONNECT
RESOURCE
DBA
SYSTEM 1F674DF7AEE76E9B
SELECT_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
OUTLN 4A3BA55E08595C81
EXP_FULL_DATABASE
解除用户锁定（必须以 sysdba 模式登录）
SQL> conn /as sysdba
Connected.
SQL> alter user system account unlock;
修改用户密码（必须以 sysdba 模式登录）
SQL> alter user system identified by 123456;
User altered.
SQL> SELECT name,password FROM user$;
SP2-0640: Not connected
SQL> connect as sysdba
Enter user-name: system
Enter password:
Connected.
查询已存在的表空间的名称以及大小
SQL> select t.tablespace_name, round(sum(bytes/ (1024*1024)),0) ts_size from dba_tablespaces t,dba_data_files d where t.tablespace_name=d.tablespace_name group by t.tablespace_name;
TABLESPACE_NAME TS_SIZE
SYSAUX 640
UNDOTBS1 25
USERS 100
SYSTEM 360
User altered.
查看表空间物理文件的名称及大小
SQL> select tablespace_name,file_id,file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;
TABLESPACE_NAME FILE_ID FILE_NAME TOTAL_SPACE
SYSAUX 2 /u01/app/oracle/oradata/XE/sysaux.dbf 640
SYSTEM 1 /u01/app/oracle/oradata/XE/system.dbf 360
锁表解决办法
-- 登录
sqlplus /nolog
connect system/oracle
-- 查询对应的会话
SELECT LPAD(' ', DECODE(L.XIDUSN, 0, 3, 0)) || L.ORACLE_USERNAME USER_NAME,
O.OWNER,O.OBJECT_NAME,O.OBJECT_TYPE,S.SID,S.SERIAL#
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S
WHERE L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = S.SID
ORDER BY O.OBJECT_ID, XIDUSN DESC;
-- 杀掉对应的会话
alter system kill session 'sid,serial#';
更新数据库 TIMESTAMP 类型数据
update S41T1_CTR_ITM_REL t set t.tms=SYSTIMESTAMP where t.ctr_id=1;
新增字段, 并添加备注信息
ALTER TABLE S41T1_CTR_ITM_REL ADD( CTR_SN NUMBER(10) ) ;
COMMENT ON COLUMN S41T1_CTR_ITM_REL.CTR_SN is '合同SN';
months_between 函数的使用
select appr.ENTR_TM as entrTm
from S41T1_STFF_APPR_INF appr
where 1=1
and months_between(sysdate,to_date(appr.ENTR_TM, 'yyyyMMdd'))>=appr.PBPRD;
查询结果作为临时表使用
select a.num1,b.num2 from
(select cuont(*) num1 from tab1) a, (select cuont(*) num2 from tab2) b;
将表中一个字段的值赋值给另外一个字段
update SAL t set t.name_rpl=t.name;
插入 date 和 TIMESTAMP 类型数据
insert into S41T1_OUTSRC_STFF_INF t (SN,STFF_ST,TIME,TIMESTA) values (3,1,TO_DATE('1900-01-02','yyyy-MM-dd'),TO_TIMESTAMP('1999-12-0111:00:00','YYYY-MM-DDHH:MI:SS'));
insert into S41T1_OUTSRC_STFF_INF t (SN,STFF_ST,TIME,TIMESTA) values (3,1,TO_DATE('1900-01-02','yyyy-MM-dd'),'01-12月-99 11.00.00.000 上午');
查询锁表信息
select * from v$lock;
使用 with 语句将结果集查询出来，再进行筛选
with dm as (
select cm.customerno,cm,customername,cm.creditopen,cm.nationalorgcode,cm,submittime
from clm_cust_customer_info c,
clm_cust_group_member cg,
clm_cust_group_info g,
clm_crdmg_mgtb_info cm
where c.customerno=cg.customerno
and c.partygroupno=g.partygroupno
and (c.customer=cm.customerno or c.customername=cm.customername or c.nationalorgcode=cm.nationalorgcode)
and c.partygroupno='8405918760'
and nvl(c.deleteflag,'0')!='1'
and nvl(cg.deleteflag,'0')!='1'
and nvl(g.deleteflag,'0')!='1'
and nvl(cm.deleteflag,'0')!='1'
);
select * from dm
where 1=1
and submittime=(select max(submittime) from dm);
创建分区表
create table sale (prodct_id varchar2(5),sale_count number(10,2))
partition by range (sale_count)(
partition p1 values less than (1000),
partition p1 values less than (2000),
partition p1 values less than (3000))
查询用户
SELECT * FROM DBA_USERS;
创建公共同义词
CREATE PUBLIC SYNONYM DEPT_DICT FOR COMM.DEPT_DICT;
CREATE PUBLIC SYNONYM ADT_LOG FOR INPADM.ADT_LOG;
CREATE PUBLIC SYNONYM WAIT_BED_PATS FOR INPADM.WAIT_BED_PATS;
删除公共同义词
drop public synonym DEPT_DICT;
drop public synonym ADT_LOG;
drop public synonym WAIT_BED_PATS;
查询数据最大允许最大连接数
show variables like '%max_connections%';
mysql 查询数据库信息
-- 查看指定数据库的表
select * from INFORMATION_SCHEMA.TABLES where table_schema = 'test' and table_type = 'BASE TABLE';
-- 查看数据库表的数量
select COUNT(*) from INFORMATION_SCHEMA.TABLES where table_schema = 'test' and table_type = 'BASE TABLE';
-- 查看数据库的空间占用大小
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES where table_schema = 'test' and table_type = 'BASE TABLE';
-- 查看数据库数据条数
select sum(table_rows) from INFORMATION_SCHEMA.TABLES where table_schema = 'test' and table_type = 'BASE TABLE';
-- 查看linux指定文件行数
wc -l test.txt
-- 查看linux指定文件大小
ls -lh file
-- 查看指定文件夹大小
du -h –max-depth=0 logs
-- 查看指定文件夹下所有文件的行数总和
wc -l ./*