with as
WITH
TMP_1 AS (SELECT CITY FROM BBBBBBBBBBBBBB B),
TMP_2 AS (SELECT BSFWR FROM TMP_1 A INNER JOIN AREAS B ON A.CITY = B.AREA_NAME)
SELECT BSFWR
FROM TMP_2
CASE WHEN
case when相当于if
select id,
CASE WHEN AGE>0 OR AGE<10 THEN '大于0小于10' else '不大于0不小于10' end as age from demp
这个case when相当于java的if,多个条件就case when --- case when --- end就表示结束了
if(age>0||age<10){
就输出大于0小于10
}else{
输出不大于0不小于10
}
一次执行多条sql
mybaits也可以直接执行
begin
delete from employee where name='zhangsan';
delete from dept where name='caiwu';
end;
查指定表的字段和注释
Employee
为表名
select
ut.COLUMN_NAME,--字段名称
uc.comments,--字段注释
ut.DATA_TYPE,--字典类型
ut.DATA_LENGTH,--字典长度
ut.NULLABLE--是否为空
from user_tab_columns ut
inner JOIN user_col_comments uc
on ut.TABLE_NAME = uc.table_name and ut.COLUMN_NAME = uc.column_name
where ut.Table_Name='Employee'
order by ut.column_name
查所有表的字段和注释
SELECT * FROM ALL_COL_COMMENTS;
批量修改表字段注释
SELECT 'COMMENT ON COLUMN '||TABLE_NAME||'.'||COLUMN_NAME||' IS ''无注释'';' from ALL_COL_COMMENTS where owner='DDFRAME' AND table_name like 'T_COST%' AND COMMENTS IS NULL;
通过条件筛选,拼接成sql语句
以下就是结果,拿去执行就可以修改了
grouping sets
相当于使用union all
多条语句
grouping sets(a,(a,b))
相当于第一条语句的group by
是a
,第二条语句的group by
是a,b
,前提是他们查询参数的数量要一样,可以使用Null代替
select a,null b from temp1 where nf=2020 group by a
union all
select a,b from temp1 where nf=2020 group by a,b
grouping sets
改造后
select a,b from temp1 where nf=2020 group by grouping sets (a,(a,b)) order by nf
来源https://www.cnblogs.com/shiliye/p/10518151.html
SELECT country, null department, round(avg(salary), 2) FROM employee GROUP BY country
UNION ALL
SELECT null country, department, round(avg(salary), 2) FROM employee GROUP BY department;
等价于
SELECT country, department, round(avg(salary), 2) FROM employee GROUP BY GROUPING SETS (country, department);
GROUP BY GROUPING SETS (A,B,C) 等价与 GROUP BY A
UNION ALL
GROUP BY B
UNION ALL
GROUP BY C
GROUP BY GROUPING SETS ((A,B,C)) 等价与 GROUP BY A,B,C
GROUP BY GROUPING SETS (A,(B,C)) 等价与 GROUP BY A
UNION ALL
GROUP BY B,C
GROUP BY GROUPING SETS (A) 等价于 GROUP BY A,B,C
,GROUPING SETS (B)
,GROUPING SETS (C)
GROUP BY GROUPING SETS (A) 等价于 GROUP BY A,B,C
,GROUPING SETS ((B,C))
GROUP BY GROUPING SETS (A) 等价于 GROUP BY A,B
,GROUPING SETS (B,C) UNION ALL
GROUP BY A,C
GROUP BY A 等价于 GROUP BY A
,B ,B
,GROUPING SETS ((B,C)) ,C
GROUP BY A 等价于 GROUP BY A,B,C
,B UNION ALL
,GROUPING SETS (B,C) GROUP BY A,B
GROUP BY A 等价于 GROUP BY A,B,C
,B UNION ALL
,C GROUP BY A,B,C
,GROUPING SETS (B,C)
行转列
查询出的数据
SELECT F_CONTENT_TYPE type, F_CONTENT_PATH path, F_CL_CODE code FROM TEST_CONTENT
把type
转到横排作为表头,要使用子查询,先查出需要的数据,然后通过pivot
转换,type是要转的字段,作为表头,sum(path)并没有计算,是要展示的数据,code这一列实在最左边,这里type是写死的,如果要动态转换,需要用到存储过程
SELECT
*
FROM
( SELECT F_CONTENT_TYPE type, F_CONTENT_PATH path, F_CL_CODE code FROM TEST_CONTENT ) pivot ( sum( path ) FOR type IN ( 2, 3, 4 ) )
PL/SQL语句块
方括号部分是可选的
[DECLARE]
/*声明部分:在这个PL/SQL用到的变量、类型及游标等*/
BEGIN
/*执行部分:过程及SQL语句,SQL主体*/
[EXCEPTION]
/*异常处理:错误处理*/
END;
DECLARE -- 声明变量
name VARCHAR2 ( 30 );
BEGIN -- 语句块开始
SELECT '张三' INTO newName FROM dual; --into赋值
DBMS_OUTPUT.PUT_LINE ( name ); --输出name
EXCEPTION -- 异常
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE ( '错误' );
END; --语句块结束
begin中可嵌套begin
DECLARE
/*
声明变量,
字段类型可以自己指定name VARCHAR2 ( 30 ),也可以使用 表名.字段%type 和 表名%rowtypede 的方式
表名.字段%type ,意思是字段类型和这个表中的某个字段类型一致,
例如 name employee.name%type,name的类型和employee表中的name字段类型一致
表名%rowtype,返回的就是一整行数据,例如: emp employee%rowtype,使用的时候就是emp.name或emp.age这种
*/
name VARCHAR2 ( 30 );
newName VARCHAR2 ( 30 );
BEGIN -- 语句块开始
BEGIN -- 子块
SELECT '李四' INTO name FROM dual; --into赋值
END;
SELECT '张三' INTO newName FROM dual; --into赋值
DBMS_OUTPUT.PUT_LINE ( name );
DBMS_OUTPUT.PUT_LINE ( newName );
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE ( '错误' );
END; -- 语句块结束
赋值
两种方式,一种:=
,一种into
DECLARE
name VARCHAR2 ( 30 );
newName VARCHAR2 ( 30 );
BEGIN
name:='你好'; -- := 赋值
DBMS_OUTPUT.PUT_LINE ( name );
SELECT '张三','李四' INTO name,newName FROM dual; -- into赋值,可以按照顺序赋值多个
DBMS_OUTPUT.PUT_LINE ( name );
DBMS_OUTPUT.PUT_LINE ( newName );
END;