- 浏览: 476259 次
- 性别:
- 来自: 广州
文章分类
- 全部博客 (502)
- Java (70)
- Linux (10)
- 数据库 (38)
- 网络 (10)
- WEB (13)
- JSP (4)
- 互联网 (71)
- JavaScript (30)
- Spring MVC (19)
- HTML (13)
- CSS (3)
- AngularJS (18)
- Redis (5)
- Bootstrap CSS (1)
- ZooKeeper (4)
- kafka (6)
- 服务器缓存 (4)
- Storm (1)
- MongoDB (9)
- Spring boot (16)
- log4j (2)
- maven (3)
- nginx (5)
- Tomcat (2)
- Eclipse (4)
- Swagger (2)
- Netty (5)
- Dubbo (1)
- Docker (7)
- Hadoop (12)
- OAuth (1)
- webSocket (4)
- 服务器性能 (7)
- Session共享 (1)
- tieye修改 (1)
- 工作 (1)
- 有用的语录 (0)
- https (2)
- common (5)
- 产品开发管理 (1)
- CDN 工作原理 (1)
- APNS、GCM (1)
- 架构图 (3)
- 功能实现分析 (1)
- JMX (1)
- 服务器相关操作命令 (1)
- img02 (0)
- 服务器环境搭建 (9)
- goodMenuBook (1)
- CEInstantPot (0)
- 有用数据 (1)
- 百度地图WEB API (2)
- 正则表达式 (1)
- 样式例子 (2)
- staticRecipePressureCooker.zip (1)
- jCanvas (1)
- 网站攻击方法原理 (1)
- 架构设计 (3)
- 物联网相关 (3)
- 研发管理 (7)
- 技术需求点 (1)
- 计划 (1)
- spring cloud (11)
- 服务器开发的一些实用工具和方法 (1)
- 每天学到的技术点 (4)
- Guava (1)
- ERP 技术注意要点 (2)
- 微信小程序 (1)
- FineRepor (1)
- 收藏夹 (1)
- temp (5)
- 服务架构 (4)
- 任职资格方案 (0)
- osno_test (1)
- jquery相关 (3)
- mybatis (4)
- ueditor (1)
- VueJS (7)
- python (10)
- Spring EL (1)
- shiro (1)
- 前端开发原理与使用 (7)
- YARN (1)
- Spark (1)
- Hbase (2)
- Pig (2)
- 机器学习 (30)
- matplotlib (1)
- OpenCV (17)
- Hystrix (1)
- 公司 (1)
- miniui (4)
- 前端功能实现 (3)
- 前端插件 (1)
- 钉钉开发 (2)
- Jenkins (1)
- elasticSearch使用 (2)
- 技术规范 (4)
- 技术实现原理 (0)
最新评论
SQL常用语句
- 博客分类:
- 数据库
delete from cacherefresh where 1=1;
COMMIT;
//=====================================================
获取数据库所有表明的SQL
MYSQL
select table_name from information_schema.tables where table_schema='当前数据库';
select table_name from information_schema.tables where table_schema = (select database()) order by table_name;
select t.table_name from user_tables t;
ORACLE
SELECT * FROM user_tables
-- 加 ORDER BY TABLE_NAME 是让结果按照表名顺序展示
SELECT * FROM user_tables ORDER BY TABLE_NAME
https://www.cnblogs.com/cnsdhzzl/p/9848493.html
Oracle
获取用户所有表
select table_name from user_tables where TABLESPACE_NAME is not null and user='UserName'
获取表字段
SELECT table_name, column_name, data_type FROM all_tab_cols WHERE table_name = 'tablename '
//=====================================================
oracle 删除表
declare
p_table VARCHAR2(4000) := 'supplierdocumentinfo';
v_count number(10) := 0;
begin
select count(*)
into v_count
from user_tables
where table_name = upper(p_table);
if v_count > 0 then
execute immediate 'drop table ' || p_table ||' purge';
end if;
end;
mysql
判断表是否存在
select * from information_schema.tables where table_name ='acl';
//=====================================================
//=====================================================
//删除oracle相关表的SQL
create global temporary table delete_table_temp_tb (col1 varchar(200)) on commit delete rows;
create global temporary table delete_table_message_tb (col1 varchar(200)) on commit delete rows;
insert into delete_table_temp_tb values('test11');
insert into delete_table_temp_tb values('test22');
insert into delete_table_temp_tb values('test33');
declare
p_table VARCHAR2(200) := '';
v_count number(10) := 0;
begin
FOR r IN (select * from delete_table_temp_tb) loop
v_count := 0;
p_table := trim(r.col1);
begin
select count(*)
into v_count
from user_tables
where table_name = upper(p_table);
if v_count > 0 then
execute immediate 'drop table ' || p_table ||' purge';
end if;
end;
end loop;
end;
select * from delete_table_temp_tb;
select * from delete_table_message_tb;
drop table delete_table_temp_tb;
drop table delete_table_message_tb;
MySQL
CREATE TEMPORARY TABLE delete_table_temp_tb (
table_name VARCHAR(100) NOT NULL,
primary key (table_name)
);
insert into delete_table_temp_tb values('esbtransaction ');
insert into delete_table_temp_tb values('esbtransactionrelease ');
select * from delete_table_temp_tb;
create procedure delete_table_procedure()
begin
declare p_table varchar(100);
declare v_count int;
declare delTabl varchar(1000);
-- 遍历数据结束标志 注意位置顺序
DECLARE done INT DEFAULT FALSE;
-- 注意用别名 因为id在上面已经有定义所以需要使用表的别名区别
declare cur_test CURSOR for select trim(t.table_name) from delete_table_temp_tb t;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open cur_test;
repeat
fetch cur_test into p_table;
set v_count =0;
select count(*) into v_count from information_schema.tables where table_name = upper(p_table);
if v_count > 0 then
SET delTabl = CONCAT(' DROP TABLE ', p_table);
PREPARE temp FROM delTabl;
EXECUTE temp;
end if;
until done
end repeat;
close cur_test;
end;
call delete_table_procedure();
# 删除临时表
drop TEMPORARY TABLE delete_table_temp_tb;
# 删除存储过程
drop procedure delete_table_procedure;
//=====================================================
ORAcle数据库删除字段的SQL语句 :
alter table tableName drop (name)
MySQL
MySQL中添加、删除字段,使用SQL语句操作
1.新增字段
alter table table1
add col1 int(2) not NULL DEFAULT 0 COMMENT '注释1',
add col2 VARCHAR(300) DEFAULT NULL COMMENT '注释2'
语法:alter table 表名 add 列名 int(2) 字段数据类型、长度 NOT NULL (是否可为空)DEFAULT 0 默认值
alter table MTClsPanelAttribute add defaultValue varchar2(100);
2.删除字段
alter table id_name drop column age,drop column address;
语法:alter table 表名 drop column 列名
修改字段信息
alter table acl_ext_test modify column column_2 decimal(7,3) default 12.350;
//=====================================================
oracle中判断某个字段是否存在
select count(column_name)
from cols
where table_name = upper('acl')
and column_name = upper('oid2')
MySQL
SELECT count(*) FROM information_schema.columns WHERE table_name = 'Acl' AND COLUMN_NAME = 'Oid2';
//=====================================================
//=====================================================
//删除oracle相关表的相关字段
create global temporary table delete_table_temp_tb (tb_name varchar(200),column_name varchar(200)) on commit delete rows;
create global temporary table delete_table_message_tb (tb_name varchar(200),column_name varchar(200)) on commit delete rows;
insert into delete_table_temp_tb (tb_name,column_name) values('test11','test111');
insert into delete_table_temp_tb (tb_name,column_name) values('test22','test222');
declare
p_table VARCHAR2(200) := '';
p_column_name VARCHAR2(200) := '';
v_count number(10) := 0;
begin
FOR r IN (select * from delete_table_temp_tb) loop
v_count := 0;
p_table := trim(r.tb_name);
p_column_name := trim(r.column_name);
begin
select count(column_name)
into v_count
from cols
where table_name = upper(p_table)
and column_name = upper(p_column_name);
if v_count > 0 then
execute immediate 'alter table ' || p_table ||' drop (' || p_column_name || ')';
end if;
end;
end loop;
end;
select * from delete_table_temp_tb;
select * from delete_table_message_tb;
drop table delete_table_temp_tb;
drop table delete_table_message_tb;
MySQL
CREATE TEMPORARY TABLE delete_table_temp_tb
(
tb_name VARCHAR(100) NOT NULL,
column_name VARCHAR(100) NOT NULL,
primary key (tb_name)
);
insert into delete_table_temp_tb (tb_name,column_name) values('test11','test111');
insert into delete_table_temp_tb (tb_name,column_name) values('test22','test222');
insert into delete_table_temp_tb (tb_name,column_name) values('objecthelpconfig ','name');
select * from delete_table_temp_tb;
create procedure delete_table_column_procedure()
begin
declare p_table varchar(100);
declare p_column varchar(100);
declare v_count int;
declare delTabl varchar(1000);
-- 遍历数据结束标志 注意位置顺序
DECLARE done INT DEFAULT FALSE;
-- 注意用别名 因为id在上面已经有定义所以需要使用表的别名区别
declare cur_test CURSOR for select trim(t.tb_name), trim(t.column_name)from delete_table_temp_tb t;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open cur_test;
repeat
fetch cur_test into p_table,p_column;
set v_count = 0;
SELECT count(*) into v_count FROM information_schema.columns WHERE table_name = upper(p_table) AND COLUMN_NAME = upper(p_column);
if v_count > 0 then
SET delTabl = CONCAT(' alter table ', p_table,' drop column ',p_column);
PREPARE temp FROM delTabl;
EXECUTE temp;
end if;
until done
end repeat;
close cur_test;
end;
call delete_table_column_procedure();
# 删除临时表
drop TEMPORARY TABLE delete_table_temp_tb;
# 删除存储过程
drop procedure delete_table_column_procedure;
//=====================================================数据表备份
create table ts_dictionary_20160715 as select * from ts_dictionary;
//=====================================================
//=====================================================
//=====================================================
oracle
查询死锁
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id
SHOW TABLE STATUS FROM `xinttest_ext` //可列出指定数据库中所有表的信息,包括表类型;
SHOW CREATE TABLE `xinttest_ext` //可列出指定数据表的信息,包括表类型。
//获取所有表中的最大OID或OBJECTIDENTIFIER
create global temporary table delete_table_count_max_tb (count_max number(20)) on commit delete rows;
declare
p_table_mame VARCHAR2(200) := '';
v_count number(20) := 0;
v_count_max number(20) := 0;
begin
FOR r IN (SELECT TABLE_NAME FROM user_tables ORDER BY TABLE_NAME ) loop
p_table_mame := trim(r.TABLE_NAME);
v_count := 0;
begin
select count(column_name)
into v_count
from cols
where table_name = upper(p_table_mame)
and column_name = upper('OBJECTIDENTIFIER');
if v_count > 0 then
execute immediate 'insert into delete_table_count_max_tb (count_max) select MAX(OBJECTIDENTIFIER) from ' || p_table_mame;
end if;
end;
v_count := 0;
begin
select count(column_name)
into v_count
from cols
where table_name = upper(p_table_mame)
and column_name = upper('OID');
if v_count > 0 then
execute immediate 'insert into delete_table_count_max_tb (count_max) select MAX(OID) from ' || p_table_mame;
end if;
end;
end loop;
end;
select MAX(count_max) from delete_table_count_max_tb;
drop table delete_table_count_max_tb;
发表评论
-
ES数据同步方案
2022-06-24 10:57 2178//============================= ... -
SQL存储过程例子和有用的SQL
2022-02-19 09:20 167delete from cacherefresh where ... -
SQL优化对比与总结
2021-01-09 14:44 34519000000 b表 SELECT * from b w ... -
执行存储过程测试
2020-12-30 16:47 349--执行存储过程创建 if (exists (select * ... -
mysql提高insert into 插入速度的方法
2018-12-14 17:26 6059mysql提高insert into 插入 ... -
Mysql并发时经典常见的死锁原因及解决方法
2018-12-08 09:30 3017Mysql并发时经典常见的死锁原因及解决方法 MySQL有 ... -
数据库沉余实现方式
2018-12-04 17:30 976数据库沉余实现方式 canal 原理相对比较简单: (1)c ... -
最终一致性的常用做法
2018-12-01 22:28 594最终一致性的常用做法 ... -
库存扣减和锁
2018-11-29 16:19 2库存扣减和锁 在对数据库的值进行修改时,如果在多线程情况下 ... -
Spring Boot中整合Sharding-JDBC
2018-11-26 18:03 3407Spring Boot中整合Sharding-JDBC ... -
MYSQL 主从、读写分离、分库分表、高可用、数据安全
2018-11-19 18:03 1698MYSQL 主从、读写分离、分库分表、高可用、数据安全 ... -
mybatis-generator 使用
2018-05-19 11:29 511http://www.cnblogs.com/Jason-Xi ... -
eclipse JPA Tools 使用
2018-05-14 17:11 730https://blog.csdn.net/guoxin91/ ... -
mybatis 通用查询实现
2018-03-26 10:04 1362package com.oceano.modity.entit ... -
存储过程 函数
2017-10-27 17:59 441存储过程 函数 存储过 ... -
分页查询例子
2017-10-19 10:22 743分页查询例子 Mybatis分页插件PageHelper的 ... -
数据库同步工具
2017-10-14 14:27 1293数据库同步工具 goden gate Oracle Go ... -
ETL工具
2017-09-01 15:14 668ETL工具 ETL,是英文 Extract-Transfor ... -
PowerDesigner 对比pdm文件内容变化工具
2017-08-06 14:24 670PowerDesigner 对比pdm文件内容变化工具 -
hibernate 分库分表
2017-06-21 14:36 1069hibernate 分库分表 事务是对同一个数据库进行的,不 ...
相关推荐
sql常用语句sql常用语句sql常用语句sql常用语句sql常用语句sql常用语句sql常用语句sql常用语句sql常用语句
SQL常用语句集锦,很好用的哦 SQL常用语句集锦,很好用的哦
自己整理的sql常用语句,比较适合0接触学习sql的人
面试常用中的SQL常用语句.面试常用中的SQL常用语句. 欢迎下载
Sql常用语句汇总记录.pdfSql常用语句汇总记录.pdf
sql常用语句,sql语句,sqlserver ,查询 常用的对数据库执行的增删改查等sql语句!
我收藏的 一本SQL常用语句,个人觉得还可以
常用语句大全,SQL常用语句大全,SQL
office word文件,SQL常用语句速查手册,SQL常用语句速查手册,SQL常用语句速查手册
常用的几个不好记的语句 和 数据库连接 希望给有用的
SQL常用语句参考 一些常用的语句
sql常用语句以及函数
SQL常用语句一览.doc SQL常用语句一览.doc SQL常用语句一览.doc
sql常用语句经典.txt 对于数据库不熟悉的人很有帮助
Sql常用语句,非常不错,适合初学者学习!
mysql sql常用语句大全
Sql常用语句汇总
sql常用语句速查手册大全.doc
sql常用语句命令ffdfdfdfdfdfdfd