数据库操作语句

常用的SQL语句，汇总，以便之后查阅。

针对具体的数据行级别 增删改查
Data Manipulation Language (DML)数据操作语言(DML) (for database) insert delete update select

定义是针对表、视图、索引、用户、触发器、存储过程等级别
Data Definition Language (DDL) 数据定义语言(DDL) (for database) create drop alter table/view/index

权限
Data Control Language (DCL) 数据控制语言(DCL) (for database) grant revoke

mysql 创建用户 和授权

CREATE USER ‘username‘@’%’ IDENTIFIED BY ‘password’;
GRANT ALL PRIVILEGES ON . TO ‘username‘@’%’ WITH GRANT OPTION;
查询用户

SELECT DISTINCT CONCAT(‘User: ‘’’,user,‘‘‘@’’’,host,’’’;’) AS query FROM mysql.user;

mysql 索引

BTREE HASH

MySQL的索引是什么？怎么优化？

explan或者describe, possible_key key key_len 执行的索引,已经扫描的行数row,如果是范围值的话，索引无效。

mysql索引的使用及优化方法内有my.ini的配置参数解释

Oracle的dblink

//00933问题一定要检查自己的SQL语句的语法

select * from user_sys_privs;//查找当前用户的权限是否具有dblink的create权限
grant create public database link,create database link to “account”; //分配权限

select name,value from v$parameter where name=’service_names’ //查询服务名
select from global_name;
select owner,object_name from dba_objects where object_type=’DATABASE LINK’;//查询已有的dblink
select from dba_db_links;

//两个创建 注意引号问题
create database link dblinktest connect to sstzjs_zjk identified by “12345678” using ‘19.131.106.252:1521/oracle’;
create public database link sstzjs_zjk.us.oracle.com connect to sstzjs_zjk IDENTIFIED BY “12345678” using ‘(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 19.131.106.252)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = ORCL)))’;

create public database link zrhs_link using ‘19.131.106.252:1521/oracle’;
在不指定用户名和口令的情况下，ORACLE使用当前的用户名和口令登录到远程数据库。
SELECT * FROM INS_BUSINESS_INFO@ZRHS_LINK

DROP PUBLIC DATABASE LINK SSTZJS_ZJK.US.ORACLE.COM; //删除dblink

Oracle

拼接：wm_concat (FDSXMC || ‘&&’)

替换：update Prj_Project set projectname = replace(projectname,’华为’,’2’);

导入数据库

imp username/password@192.168.5.214/ORCL file=D:\daoru.dmp FULL=y

导出数据库 注意版本要一致，10g 对10g 11g对11g 否则会出错。因为有exp版本和server版本之分

exp username/password@192.168.5.214/ORCL file=d:\daochu.dmp FULL=y

exp XNJD20171225/12345678@192.168.2.240/ORCL file=d:\daochu.dmp owner=(XNJD20171225,sys) 导出对应用户的数据库中的内容

exp username/pwd@sid file=d:/data/bak.dmp owner=(user)rows=n 不导出数据

创建表空间
create tablespace WEBSITE
datafile ‘D:\app\zhang\product\11.1.0\db_1\SPACE_TBSPACE.dbf’
size 1500M autoextend on next 5M maxsize 3000M;

create tablespace WEBSITE logging datafile D:\app\Myadmin.fs\product\11.2.0\test1.dbf’ size 1024m;

自动扩展：

select tablespace_name,file_name,autoextensible from dba_data_files;

alter database datafile ‘D:\APP\MYADMIN.FS\PRODUCT\11.2.0\SWBXN1.DBF’ autoextend on;

开启自动扩展功能语法：
alter database datafile ‘对应的数据文件路径信息’ autoextend on;
关闭自动扩展功能语法：
alter database datafile ‘对应的数据文件路径信息’ autoextend off;

创建用户，给权限
– Create the user
create user XNJD20180102
identified by “12345678”
default tablespace WEBSITE
temporary tablespace TEMP
profile DEFAULT;
– Grant/Revoke role privileges
grant connect to XNJD20180102 with admin option;
grant dba to XNJD20180102 with admin option;
– Grant/Revoke system privileges
grant unlimited tablespace to XNJD20180102;

or

create user XNJD20180102 identified by 12345678 default tablespace WEBSITE temporary tablespace temp;

grant dba,connect,resource to XNJD20180102;

性能问题

表空间

SELECT a.tablespace_name “表空间名”,
a.bytes / 1024 / 1024 “表空间大小(M)”,
(a.bytes - b.bytes) / 1024 / 1024 “已使用空间(M)”,
b.bytes / 1024 / 1024 “空闲空间(M)”,
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) “使用比”
FROM (SELECT tablespace_name, sum(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, sum(bytes) bytes, max(bytes) largest
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
ORDER BY ((a.bytes - b.bytes) / a.bytes) DESC;

select dbf.tablespace_name,
dbf.totalspace “总量(M)”,
dbf.totalblocks as 总块数,
dfs.freespace “剩余总量(M)”,
dfs.freeblocks “剩余块数”,
(dfs.freespace / dbf.totalspace) * 100 “空闲比例”
from (select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name);

SELECT T.TABLESPACE_NAME,
D.FILE_NAME,
D.AUTOEXTENSIBLE,
D.BYTES,
D.MAXBYTES,
D.STATUS
FROM DBA_TABLESPACES T, DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME, FILE_NAME;

SELECT * FROM dba_data_files WHERE tablespace_name = ‘WEBSITE’;

select blocks, maxblocks,file_name,tablespace_name from dba_data_files;

alter tablespace WEBSITE add datafile ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\WEBSITE1.dbf’ size 5120M

排查问题

查询正在运行的SQL:

select a.program, b.spid, c.sql_text,c.SQL_ID from v$session a, v$process b, v$sqlarea c
where a.paddr = b.addr and a.sql_hash_value = c.hash_value and a.username is not null;

上面的SQL_ID 完整的SQL: select a.* from v$sql a where a.SQL_ID=’43c5ykm1mcp2a’

约束表:
ORACLE有关ORA-02292：违反完整约束条件（XXX.FKXXX）- 找到子记录的删除方法

select a.constraint_name, a.table_name, b.constraint_name from user_constraints a, user_constraints b
where a.constraint_type = ‘R’ and b.constraint_type = ‘P’ and a.r_constraint_name = b.constraint_name
and a.constraint_name = ‘FKXXX’

数据被锁，查看session, for update 导致的锁表问题等。

执行计划F5

看懂Oracle执行计划

触发器

测试环境Oracle 10g

CREATE OR REPLACE TRIGGER  T_INS_BUSINESSINFO_UPDATE    --T_INS_BUSINESSINFO_UPDATE 表示触发器的名字
AFTER update OF  CASEEXCHANGE       --CASEEXCHANGE  更新哪个字段
ON T_INS_BUSINESSINFO  -- T_INS_BUSINESSINFO触发的表
FOR EACH ROW -- 代表行级触发器
BEGIN
 UPDATE INS_BUSINESS_INFO@SSTZJS_ZJK.US.ORACLE.COM h SET h.EXCHANGESTATUS='5' 
 WHERE h.bsnumcopy = :new.bsnum and :new.CASEEXCHANGE ='3' AND h.EXCHANGESTATUS = '2';--收到材料回退
 UPDATE INS_BUSINESS_INFO@SSTZJS_ZJK.US.ORACLE.COM h SET h.EXCHANGESTATUS='8' 
 WHERE h.bsnumcopy = :new.bsnum and :new.CASEEXCHANGE ='4' AND h.EXCHANGESTATUS = '7';--已经领证调整

 --DBMS_OUTPUT.PUT_LINE('旧的flongnumber值是'||:old.FLONGNUMBER ||'、新的flongnumber值是'||:new.FLONGNUMBER); --  输出值

END;

中间用到了dblink @ 多用用就可以啦还是挺简单的，就是不知道效率怎么样

--DROP TRIGGER TR_ORG_UPDATE;
select * from all_triggers  where table_name='T_INS_BUSINESSINFO';   
--UPDATE INS_BUSINESS_INFO@SSTZJS_ZJK.US.ORACLE.COM h SET h.EXCHANGESTATUS='2' WHERE h.bsnumcopy ='1708111553000610N'  -- :new.bsnum and :new.CASEEXCHANGE ='3';

定义赋值

create or replace trigger yearPaoId_trigger
before insert
on YEARPATENT
for each row
declare
v_yearPaoId number(9);//定义变量
v_yearPaoIdOld number(9);
begin
    select :new.yearPaoId into v_yearPaoId from YEARPATENT;//选择什么into到变量中
    select :new.yearPaoIdOld into v_yearPaoIdOld from YEARPATENT;
    update mapping@dbLinkName set newId = v_yearPaoId where oldId = v_yearPaoIdOld;
end;

下面这个是一个标准写法：

CREATE OR REPLACE TRIGGER TG_NAME
　　BEFORE | AFTER
　　INSERT OR DELETE OR UPDATE [OF COL1,COL2,...]
　　ON 表名
　　[FOR EACH ROW]
　　[WHEN 条件]
　　DECLARE
　　...
　　BEGIN
　　....
　　EXCEPTION
　　...
　　END;
注: A) FOR EACH ROW 代表行级触发器.
　　B) WHEN: 只有符合该条件的时候才进行触发.
　　C) 可以通过INSERTING,DELETING,UPDATING来识别INSERT,DELETE,UPDATE操作.
　　D) 用:new 和:old(称为伪记录)来访问数据变更前后的值。
　　:NEW :INSERT,UPDATE之后的数据.
　　:OLD :DELETE,UDPATE之前的数据.
　　:old.字段名或:new.字段名
　　E) 触发器中的操作与触发事件(INSERT,UPDATE,DELETE)一起进行COMMIT和ROLLBACK;
　　REM 创建包含插入、删除、修改多种触发事件的触发器DML_LOG，对EMP表的操作进行记录。

查重和去重、其他

select t.*,t.rowid from table t
有个很好用的rownum 和 count(1)

查重：select bsnum ,count(1) from table group by bsnum having count(1)>1;

去重：select distinct(bsnum) from table;

复制表结构： create table newtable as select * from oldtable where 1=2

复制表结构和数据： create table newtable as select * from oldtable

删除数据：

批量插入：从一个表中查出来的数据
insert into table2 (SELECT B.ID,B.NAME,B.ID,’5’,’5’,’5’,’5’,’5’,’5’ FROM table1 B WHERE B.STATUS != 0)

数据库编码

select from nls_database_parameters; –服务器
select from nls_instance_parameters; –客户端
select * from nls_session_parameters; –session 连接

select userenv(‘language’) from dual;

SELECT sys_context(‘userenv’, ‘language’) FROM dual;–查询服务端语言
Oracle字符集的查看查询和Oracle字符集的设置修改

group by 组合 listagg方法

select pr.rolekey, pr.rolename as roleName,
listagg(fu.id,’,’)within group(order by rolename) as userIds,
listagg(to_char(fu.username),’,’)within group(order by rolename) as userNames
from t_roleuser t
left join t_user fu on fu.id = t.fk_userid
left join t_role pr on pr.id =t.fk_roleid
where t.fk_projectid =? group by pr.rolename,pr.rolekey ;

oracle tree

ORACLE 数据库递归查询当前数据的所有孩子数据（不包含自己）

SELECT id   AS id,
  parent_id AS parentId,
  parent_id AS parent,
  name      AS text
FROM mdm_center
  START WITH parent_id = #{parentId}
  CONNECT BY prior id  = parent_id

ORACLE数据库查询当前数据的所有父数据（包含自己）

SELECT id   AS id,
  parent_id AS parentId,
  parent_id AS parent,
  name      AS text
FROM mdm_center
  START WITH id              = #{centerId}
  CONNECT BY prior parent_id = id

oracle tree 结构级联删除数据

SELECT t.*,t.rowid FROM t_sys_nodetree_count t
CONNECT BY PRIOR id = pid start WITH ID = ‘20160406195638000013’

delete from t_sys_nodetree_count where ID in
(SELECT ID FROM t_sys_nodetree_count
CONNECT BY PRIOR FENXID = FUJID start WITH ID = ‘20160406195638000013’)