一条sql语句更新两个表的内容的sql语句
UPDATE urlName,siteName SET url=@url,name=@name WHERE siteName.zid=urlName.zid AND ID=@IN ;
select * from user_tab_comments;//查询表的注释
select * from user_col_comments;//查询列的注释
select * from all_tables;//查询此用户下面所有的表
select * from dba_col_comments where column_name like '%LINECATE%'and (owner like 'JTVRAIL' OR owner like 'MAXRAIL');//查询dba下面的哪个column的名字带有LINECATE
1.create table users(
id number(20),
name varchar2(10)
);
2.select username from dba_users;//查看当前的所有用户
3.
select * from dba_users; 查看数据库里面所有用户，前提是你是有dba权限的帐号，如sys,system
select * from all_users; 查看你能管理的所有用户！
select * from user_users; 查看当前用户信息 ！
4.select * from dba_sys_privs;
select * from user_sys_privs;
查看用户对象权限
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
select s.grantee ,count(*) from dba_sys_privs s group by s.grantee;--查询用户下面有多少个权限
5查看用户系统权限
select * from dba_sys_privs;
select * from all_sys_privs;
select * from user_sys_privs;
select * from dba_roles;//查看所有角色
查看用户所拥有的角色
select * from dba_role_privs;
select * from user_role_privs;
select username,default_tablespace from user_users;//查看当前用户的缺省表空间
6.
select extract(year from date'2011-05-17') year from dual;
SELECT * from mf_report_define WHERE report_name = 'workCount';
SELECT * from mf_report_define_item WHERE report_id='2c90db9e47f66223014815228c5d0030';
SELECT * FROM workorderappr_tg;
CREATE TABLE A(
ID NUMBER(2),
NAME VARCHAR2(3)
);
CREATE TABLE B(
ID NUMBER(2),
AID NUMBER(2),
NAME VARCHAR2(3)
);
INSERT into A values(3,'A3');
INSERT into B values(3,3,'B3');
SELECT * from A;
SELECT * from B WHERE B.AID = 1;
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID=B.AID);
SELECT ID,NAME FROM A WHERE EXISTS(SELECT * FROM B WHERE B.AID = 1);
SELECT * from B FOR UPDATE;
F596EA0E65C747E3A53BE984F24E89A4
4.
comment on TABLE plan_pro IS '工电问题录入管理' ;更改数据库表的注释
select * from user_col_comments WHERE comments LIKE '%车间%';
select * from user_col_comments WHERE column_name LIKE '%TUR%';
//获取table_name和collumn_name包含GEN的字段
select * from User_Tab_Columns WHERE column_name LIKE '%GEN%';//表的注释
SELECT * from USER_COL_COMMENTS;//字段的注释
SELECT ut.TABLE_NAME,ut.COLUMN_NAME,uc.comments
from User_Tab_Columns ut,USER_COL_COMMENTS uc
WHERE ut.column_name LIKE '%GEN%' AND ut.TABLE_NAME = uc.table_name
AND ut.COLUMN_NAME = uc.column_name;
------------------------------------------------------
select * from user_col_comments WHERE column_name LIKE '%DEPT%';
select * from user_constraints c where c.constraint_type = 'R' and c.table_name='STAFFPOSITION';
------------------------------------------------------------------
1.create table users(
id number(20),
name varchar2(10)
);
2.select username from dba_users;//查看当前的所有用户
3.
select * from dba_users; 查看数据库里面所有用户，前提是你是有dba权限的帐号，如sys,system
select * from all_users; 查看你能管理的所有用户！
select * from user_users; 查看当前用户信息 ！
4.select * from dba_sys_privs;
select * from user_sys_privs;
查看用户对象权限
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
5查看用户系统权限
select * from dba_sys_privs;
select * from all_sys_privs;
select * from user_sys_privs;
select * from dba_roles;//查看所有角色
查看用户所拥有的角色
select * from dba_role_privs;
select * from user_role_privs;
select username,default_tablespace from user_users;//查看当前用户的缺省表空间
6.
select extract(year from date'2011-05-17') year from dual;
SELECT * from mf_report_define WHERE report_name = 'workCount';
SELECT * from mf_report_define_item WHERE report_id='2c90db9e47f66223014815228c5d0030';
SELECT * FROM workorderappr_tg;
CREATE TABLE A(
ID NUMBER(2),
NAME VARCHAR2(3)
);
CREATE TABLE B(
ID NUMBER(2),
AID NUMBER(2),
NAME VARCHAR2(3)
);
INSERT into A values(3,'A3');
INSERT into B values(3,3,'B3');
SELECT * from A;
SELECT * from B WHERE B.AID = 1;
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID=B.AID);
SELECT ID,NAME FROM A WHERE EXISTS(SELECT * FROM B WHERE B.AID = 1);
SELECT * from B FOR UPDATE;
F596EA0E65C747E3A53BE984F24E89A4
-----------------------------------------------------------------------------
--第一种方法: 查询dba_tab_columns
select COLUMN_NAME,DATA_TYPE,DATA_LENGTH
from dba_tab_columns
where table_name =upper('表名')
order by COLUMN_NAME
--这种方法需要有DBA权限
--第二种方法: 查询user_tab_cols
select COLUMN_NAME,DATA_TYPE,DATA_LENGTH
from user_tab_cols
where table_name=upper('表名')
order by COLUMN_NAME
--这种方法只能查找当前用户下的表
--第三种方法: 查询ALL_TAB_COLUMNS
select distinct COLUMN_NAME,DATA_TYPE,DATA_LENGTH
from ALL_TAB_COLUMNS
WHERE TABLE_NAME= upper('表名')
--这种方法可以查询所有用户下的表
---------------------------补充-------------------------------------------------------------
--增加字段
alter table cw_srcbpb
add (SRCBPB_RJBPBL varchar2(100) );
alter table cw_srcbpb
modify (SRCBPB_RJBPBL number(30,3) );
--Oracle查看所有表和字段
--获取表：
select table_name from user_tables; --当前用户的表
select table_name from all_tables; --所有用户的表
select table_name from dba_tables; --包括系统表
select table_name from dba_tables where owner='LBSP'; --获取用户***所拥有的表这里的用户名要记得是用大写的。
-- 获取表字段：其实这里是根据用户的权限来获取字段的属性（表名要大写）
select * from user_tab_columns where Table_Name='用户表';--获取用户表的所有字段还有字段的属性。
select * from all_tab_columns where Table_Name='用户表';--获取用户表的所有字段还有字段的属性。所属用户是***
select * from dba_tab_columns where Table_Name='用户表';--获取用户表的所有字段还有字段的属性。所属用户是***
--获取表注释：
select * from user_tab_comments
--user_tab_comments：table_name,table_type,comments
--相应的还有dba_tab_comments，all_tab_comments，这两个比user_tab_comments多了ower列。
--获取字段注释：
select * from user_col_comments--注意的是user_col_comments里面的column_name的字段的值在进行like的搜索的时候这个时候一定要大写
--user_col_comments：table_name,column_name,comments
--相应的还有dba_col_comments，all_col_comments，这两个比user_col_comments多了ower列。
--查询出用户所有表的索引
select * from user_indexes
--查询用户表的索引(非聚集索引):
select * from user_indexes where uniqueness='NONUNIQUE'
--查询用户表的主键(聚集索引):
select * from user_indexes where uniqueness='UNIQUE'
--查询表的索引
select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and
t.table_name='NODE'
--查询表的主键
select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and
au.constraint_type = 'P' AND cu.table_name = 'NODE'
--查找表的唯一性约束（包括名称，构成列）：
select column_name from user_cons_columns cu, user_constraints au where cu.constraint_name=au.constraint_name and
cu.table_name='NODE'
--查找表的外键
select * from user_constraints c where c.constraint_type = 'R' and c.table_name='STAFFPOSITION'
--查询外键约束的列名：
select * from user_cons_columns cl where cl.constraint_name = 外键名称
--查询引用表的键的列名：
select * from user_cons_columns cl where cl.constraint_name = 外键引用表的键名
--------------------------------------------------------------------------
查询主键的值
select * from user_indexes where uniqueness= 'UNIQUE' AND index_name LIKE '%pk%' OR index_name LIKE '%PK%';
-------------------------------------------
SELECT * from user_tab_cols;--这条sql语句能获取到表的所有的字段的值还有其类型等等
---------------------------------------------------------------------------------
上面编辑页面
上面红线除外的是列表页面,红线是查询页面
SELECT * FROM dba_tables WHERE table_name LIKE '%ASSETDEPT%';
SELECT COLUMN_NAME FROM User_Tab_Columns@gdlink WHERE table_name='ASSET' AND DATA_TYPE='VARCHAR2';
-----------------------------------------------------------------------------
dblink
tnsnames.ora
1.select * from TBSYS_PERMISSIONS where instr(smid, '2') > 0;//查询出TBSYS_PERMISSIONS的smid字段包含2的行数
select * from TBSYS_PERMISSIONS where smid like '%2%';//上面的instr属性类似于这句sql
首先想到的就是contains，contains用法如下：
1 |
select |
但是，使用contains谓词有个条件，那就是列要建立索引，也就是说如果上面语句中students表的address列没有建立索引，那么就会报错。
DB2当前的时间减去一天的时间
SELECT SYSDATE - 1 DAY FROM DUAL
WITH WORKERS AS(
SELECT 'DOM1' DEPT, 'ZHANGSAN' NAMES , 23 AGE, 4000 SALARIES FROM DUAL UNION ALL
SELECT 'DOM1' DEPT, 'LISI' NAMES , 35 AGE, 9000 SALARIES FROM DUAL UNION ALL
SELECT 'DOM1' DEPT, 'ZHANGCHEN' NAMES, 35 AGE, 9000 SALARIES FROM DUAL UNION ALL
SELECT 'DOM1' DEPT, 'QIANSI' NAMES , 35 AGE, 4000 SALARIES FROM DUAL UNION ALL
SELECT 'DOM2' DEPT, 'WANGWU' NAMES , 26 AGE, 6500 SALARIES FROM DUAL UNION ALL
SELECT 'DOM2' DEPT, 'MALIU' NAMES , 28 AGE, 6000 SALARIES FROM DUAL UNION ALL
SELECT 'DOM2' DEPT, 'ZHAOQI' NAMES , 26 AGE, 5000 SALARIES FROM DUAL UNION ALL
SELECT 'DOM1' DEPT, 'LIBA' NAMES , 23 AGE, 3000 SALARIES FROM DUAL
)
SELECT S.NAMES,S.SALARIES,S.NAMES,DEPT, ROW_NUMBER() OVER(PARTITION BY DEPT ORDER BY SALARIES DESC)RANK FROM WORKERS S;
上面的PARTITION BY DEPT是以DEPT分组然后在这一组内进行排序,正如上面的DOM1分为1,2,3,4,5,下面的DOM2分为一组1,2,3
ROW_NUMBER()逐个增加一
WITH WORKERS AS(
SELECT 'DOM1' DEPT, 'ZHANGSAN' NAMES , 23 AGE, 4000 SALARIES FROM DUAL UNION ALL
SELECT 'DOM1' DEPT, 'LISI' NAMES , 35 AGE, 9000 SALARIES FROM DUAL UNION ALL
SELECT 'DOM1' DEPT, 'ZHANGCHEN' NAMES, 35 AGE, 9000 SALARIES FROM DUAL UNION ALL
SELECT 'DOM1' DEPT, 'QIANSI' NAMES , 35 AGE, 4000 SALARIES FROM DUAL UNION ALL
SELECT 'DOM2' DEPT, 'WANGWU' NAMES , 26 AGE, 6500 SALARIES FROM DUAL UNION ALL
SELECT 'DOM2' DEPT, 'MALIU' NAMES , 28 AGE, 6000 SALARIES FROM DUAL UNION ALL
SELECT 'DOM2' DEPT, 'ZHAOQI' NAMES , 26 AGE, 5000 SALARIES FROM DUAL UNION ALL
SELECT 'DOM1' DEPT, 'LIBA' NAMES , 23 AGE, 3000 SALARIES FROM DUAL
)
SELECT S.NAMES, DEPT, S.SALARIES, DENSE_RANK() OVER(PARTITION BY DEPT ORDER BY SALARIES DESC)RANK FROM WORKERS S;
DENSE_RANK()重复连续 DENSE_RANK()重复连续 DENSE_RANK()重复连续
WITH WORKERS AS(
SELECT 'DOM1' DEPT, 'ZHANGSAN' NAMES , 23 AGE, 4000 SALARIES FROM DUAL UNION ALL
SELECT 'DOM1' DEPT, 'LISI' NAMES , 35 AGE, 9000 SALARIES FROM DUAL UNION ALL
SELECT 'DOM1' DEPT, 'ZHANGCHEN' NAMES, 35 AGE, 9000 SALARIES FROM DUAL UNION ALL
SELECT 'DOM1' DEPT, 'QIANSI' NAMES , 35 AGE, 4000 SALARIES FROM DUAL UNION ALL
SELECT 'DOM2' DEPT, 'WANGWU' NAMES , 26 AGE, 6500 SALARIES FROM DUAL UNION ALL
SELECT 'DOM2' DEPT, 'MALIU' NAMES , 28 AGE, 6000 SALARIES FROM DUAL UNION ALL
SELECT 'DOM2' DEPT, 'ZHAOQI' NAMES , 26 AGE, 5000 SALARIES FROM DUAL UNION ALL
SELECT 'DOM1' DEPT, 'LIBA' NAMES , 23 AGE, 3000 SALARIES FROM DUAL
)
SELECT S.NAMES, DEPT, S.SALARIES, RANK() OVER(PARTITION BY DEPT ORDER BY SALARIES DESC)RANK FROM WORKERS S;
RANK()重复不连续, RANK()重复不连续, RANK()重复不连续
★:RANK()重复不连续 DENSE_RANK()重复连续
★//查询连续两个小时的AQI>=500的数据,而且查询出爆表时长和爆表次数
SELECT AREA,F.CITYCODE,SUM_HOUR AS AQIBBSC,CNT_HOUR AS AQICXBBCS FROM (
SELECT AREA,E.CITYCODE,SUM_HOUR,CNT_HOUR,C.LONGITUDE,C.LATITUDE,
ROW_NUMBER() OVER (PARTITION BY E.AREA ORDER BY E.AREA DESC) AS RN
FROM (
SELECT AREA,D.CITYCODE,SUM(HOUR_CNT) SUM_HOUR,COUNT(*) CNT_HOUR
FROM (
SELECT AREA,C.CITYCODE,HOURSDIF,COUNT(*) HOUR_CNT
FROM (
SELECT AREA,B.CITYCODE,
HOUR(ATIMEPOINT)-HOUR(BTIMEPOINT) + (DAYS(ATIMEPOINT) - DAYS(BTIMEPOINT)) * 24 -ROWNUM AS HOURSDIF
FROM (
SELECT TIMEPOINT ATIMEPOINT,AREA,A.CITYCODE,(
SELECT TIMEPOINT
FROM DSJYDD.AIR_CITYHOURAQI_PUBLISH B WHERE AQI >= 500
AND TO_CHAR(TIMEPOINT,'YYYY-MM') >= '2017-10' AND TO_CHAR(TIMEPOINT,'YYYY-MM') <= '2018-03'
AND B.CITYCODE = A.CITYCODE
AND ROWNUM = 1
) BTIMEPOINT
FROM DSJYDD.AIR_CITYHOURAQI_PUBLISH A
WHERE AQI >= 500
AND TO_CHAR(TIMEPOINT,'YYYY-MM') >= '2017-10' AND TO_CHAR(TIMEPOINT,'YYYY-MM') <= '2018-03'
AND A.CITYCODE IN ('110000','120000','130100','130200','131000','130600','130900','131100','130500','130400','140100'
,'140300','140400','140500','370100','370300','370800','371400','371500','371600','371700','410100','410200'
,'410500','410600','410700','410800','410900')
) B
ORDER BY AREA
) C
GROUP BY AREA,C.CITYCODE,HOURSDIF
HAVING COUNT(HOURSDIF) > 1
) D
GROUP BY AREA,CITYCODE
ORDER BY AREA
) E
INNER JOIN DSJYDD.POINT_INFO C
ON E.CITYCODE = C.CITYCODE
GROUP BY AREA,SUM_HOUR,CNT_HOUR,C.LONGITUDE,C.LATITUDE,E.CITYCODE
) F
WHERE RN = 1
DB2备份表的操作
1）创建一个备份表
CREATE TABLE T_PUBLIC_NOTICE_BACKUP LIKE T_PUBLIC_NOTICE
2）COPY数据到备份表
INSERT INTO T_PUBLIC_NOTICE_BACKUP SELECT * FROM T_PUBLIC_NOTICE
msyql没有错误:
select a,b,a-b from (
select count(*) a,(select count(*) from stan_organization where rgdt is not null) b from stan_organization) t;
oracle要这样写,mysql也支持这样写:
select a.num,b.num,a.num - b.num from
(select count(*) num from stan_organization) a,
(select count(*) num from stan_organization where rgdt is not null) b;
select 5+6,5+null from dual
select 5-6 from dual
select 5*6 from dual
select 5/6 from dual
select sysdate-sysdate from dual;
mysql数据库支持
INSERT INTO STUDENT SET SID = 4, SNAME = '赵七',SAGE = '29', SADDRESS = '华信路4号',SDESCRIPT = '12',NUM = 7
这种语句插入数据
//mysql的between and包含左边也包含右边
select count(*) from calendar where day between '2021-01-01' and '2021-01-04';
SELECT `day`, holiday, dow FROM test.calendar;
update calendar set holiday = 1 where day between '20211001' and '20211007';
select * from calendar where day between '20210501' and '20210505';
update calendar set holiday = 2 where day = '20211009';
select `day`, holiday, dow from test.calendar where holiday in (0,2) or dow not in (6,7);
select `day`, holiday, dow from test.calendar where holiday = 2
union all
select `day`, holiday, dow from test.calendar where holiday = 0 and dow not in (6,7);
select `day`, holiday, dow from test.calendar where holiday in (0,2) and dow not in (6,7);
select `day`, holiday, dow from test.calendar where holiday = 0;
select `day`, holiday, dow from test.calendar where holiday = 1;
select `day`, holiday, dow from test.calendar where holiday = 2;
create table stan_workday (
id int primary key auto_increment comment '主键',
`workday` date default null comment '工作日'
) engine=myisam default charset=utf8mb4
insert into stan_workday(workday)
select day from test.calendar where holiday in (0,2) and dow not in (6,7);
select * from stan_workday;
delete from stan_workday;
drop table stan_workday;
create table stan_workday (
`workday` date default null comment '工作日'
) engine=myisam default charset=utf8mb4
select * from stan_workday;
insert into stan_workday(workday)
select day from test.calendar where holiday in (0,2) and dow not in (6,7);
alter table stan_workday drop primary key;
//每次重置主键从哪个开始,记住是当前最大值的下一个,不能设置比当前有的最大值小
alter table stan_workday auto_increment=251;
alter table stan_workday modify id int;
//获取类似与oracle的rownum那样的从1开始的列
select @rownum:=@rownum+1 as rownum, stan_workday.*
from (select @rownum:=0) r, stan_workday;
//设置值减去250的值又是从1开始了
update stan_workday set id=
select rownum from (
select @rownum:=@rownum+1 rownum
from (select @rownum:=0) r, stan_workday s) t;
update stan_workday set id=id - 250;
//加入对应的值
insert into stan_workday(workday)
select day from test.calendar where holiday in (0,2) and dow not in (6,7);
drop table stan_workday;
alter table stan_workday auto_increment = 1;
select id,workday from stan_workday;
delete from stan_workday;
update stan_workday set id = null;
select id, workday from test.stan_workday;
alter table stan_workday add old_id int(11) not null;
update stan_workday set old_id = id;
alter table stan_workday drop id;
alter table stan_workday add id int primary key auto_increment comment '主键';
update stan_workday set id = old_id;
select * from test.stan_workday;
//当mysql的自增的主键由于删除数据断开不连续的时候,如果原来的主键没有作用,这个时候要删除原来的列,
然后重新创造一个相同字段的列就是了,然后把这列赋值成主键自增不为空就是了!
要是原来的主键有用就是建立一个字段把原来的字段更新成新建的这个字段,然后把对应的别的表的外键更新成对应的主键就是了,这个时候再删除原来的那个旧的列就是了!
update demo as a,其它相关联的表名 as b set b.aid=a.id where b.aid=a.old_id;
alter table stan_workday drop id;
alter table stan_workday add id int(10) not null;
alter table stan_workday modify column id int(10) not null auto_increment, add primary key (id);
select id, workday from test.stan_workday;
alter table stan_workday add old_id int(11) not null;
update stan_workday set old_id=id;
alter table stan_workday drop id;
alter table stan_workday add id int primary key auto_increment comment '主键';
update stan_workday set id=old_id;
select * from test.stan_workday;
alter table stan_workday drop id;
alter table stan_workday add id int(10) not null;
alter table stan_workday modify column id int(10) not null auto_increment, add primary key (id);
//记住数据库一对多这种关系非常常用,要完全明白并且了解这种关系,就是要看条件,一定要看出是谁的条件,过滤的哪个表,然后判断是不是一条数据,对面是不是多条数据,这样就清楚一对多的关联关系了!
select a.inp_code,a.col_code,b.col_alias,b.col_name,b.vis_width,b.enb_ord,b.edt_typ
from hnd_inp_view a,hnd_inp_itm b
where a.inp_code=b.inp_code and a.col_code=b.col_code;
分析关系性数据库最重要的指标就是要抓住一端是定的,一端是变的就能成功抓住关系型数据库的核心问题了,就是这样的,这就是一对多的关系型数据库的最重要的核心指标了,就是这样的,做事情也是这样的,你总要有个指标,有个目的,你的目的再怎么变化也是定的!
oracle 这样的写法,查询出每一列多少的数据
select 1,(select count(*) from DIC_BUSI_COMMON where type = 'WTVC' and code = 'A'),
(select count(*) from DIC_BUSI_COMMON where type = 'CTVC_ORI') from dual;
mysql 这样的写法,查询出每一列多少的数据,也可以不写后面的from dual同样查出那样多的数据
select 1,(select count(1) from test.bas_region where id = '1'),
(select count(1) from test.bas_region) from dual;
//having count(distinct a.csnm) >= 5这里记住了统计的什么就是having count(distinct a.csnm) >= 5,当时记住的是一定要distinct因为统计的不是重复的客户数,更是不要写成count(*)这样统计的是行数了,就完全不一样了!
select a.csnm,b.name,b.num from stan_person a,stan_contract b,stan_apply_contract c
where a.csnm = c.csnm and b.code = c.bstp and a.cmbl is not null
and a.ctst = 'N' and (a.rgdt <= '20211231' or a.rgdt is null)
group by a.cmbl having count(distinct a.csnm) >= 5
union
select a.csnm,b.name,b.num from stan_person a,stan_contract b,stan_trust_contract c
where a.csnm = c.csnm and b.code = c.bstp and a.cmbl is not null
and a.ctst = 'N' and (a.rgdt <= '20211231' or a.rgdt is null)
group by a.cmbl having count(distinct a.csnm) >= 5;
单上面就是一个表的时候having count(*) >= 5没有任何问题,但是多表关联的时候,这个时候就是要数统计的哪个值去重就行了having count(distinct a.csnm) >= 5//这个数的就是不重复的客户数
select * from taccount t where regexp_like(t.vc_code, ‘[a-zA-Z]’);//查询vc_code是不是含有英文大小写字母
select civt from stan_person where
(civt like '%*%' or civt like '%?%' or civt like '%!%' or civt like '%&%' or civt like '%￥%'
or civt like '%$%' or civt like '%/%%' or civt like '%^%' or civt like '%#%' or civt like '%/%'
or civt like '%@%' or civt like '%"%' or civt like '%;%' or civt like '%:%' or civt like '%>%'
or civt like '%<%' or civt like '%]%' or civt like '%[%' or civt like '%}%' or civt like '%{%'
or civt like '%-%' or civt like '%=%' or civt like '%+%' or civt like '%/_%' or civt like '%\%'
or civt like '%|%' or civt like '%。%' or civt like '%、%' or civt like '%？%' or civt like '%’%'
or civt like '%‘%' or civt like '%“%' or civt like '%”%' or civt like '%~%' or civt like '%`%')
and (length(ctid)<>lengthb(ctid) or regexp_like(ctid,'[a-zA-Z]'))
select translate('1212中国2323','0123456789'||'1212中国2323','0123456789') from dual;
select regexp_replace('23456中国3-00=.,45','[^0-9]') from dual;
--监控短信内容包含单引号
select e.* from t_cc_l2_sms_send e
where e.act_start_dttm > sysdate - 60/1440
and e.sms_type_cd ='100' and e.sms_content like '%''%'
and rownum = 1需要双写单引号即可！
2.如何查询的某一个字段中包含下划线【_】
--用户表 120402
select * from t_cc_l2_employee e where e.account_no like '%\_%' escape '\'
order by e.crt_dttm desc
3.如何查询某一个字段中包含百分号【%】
select * from t_cc_l3_property_log p
where p.log_type ='发短信' and p.case_no like '%\%%' escape '\'
//下面这样写会包含那些rtid为null的客户,因为同是null的客户大于5的也算里面
select p.csnm, count(r.rtid) from stan_organization p,stan_relation r
where p.csnm = r.csnm and p.rgdt <= '20210630' and p.ctst = '1'
and r.rtid is not null and p.cstp = '2' and p.chnl = '3' and r.rltp = 'C01'
group by r.rtid having count(r.rtid) >= 5;
要是排除null的应该加上 and r.rtid is not null
select p.csnm, count(distinct p.csnm) from stan_organization p,stan_relation r
where p.csnm = r.csnm and p.ctst = 'N' and r.rtid is not null
and p.cstp = '2' and r.rltp = 'C01' group by r.rtid
having count(distinct p.csnm) >= 5;
equals()适用于list,set,map,甚至是里面顺序相反了,但是要类型一致,count返回的是bigDecimal类型,数据库里面取的是String类型,这个时候要全部toString()之后再equals()比较,这样才能相等!
select dbms_lob.substr(content,50,1)||dbms_lob.substr(content,50,51)||
dbms_lob.substr(content,50,101)||dbms_lob.substr(content,50,151)
||dbms_lob.substr(content,50,201)||dbms_lob.substr(content,50,251)
||dbms_lob.substr(content,50,301)||dbms_lob.substr(content,50,351)
||dbms_lob.substr(content,50,401)||dbms_lob.substr(content,50,451)
as a from stan_diligence where id = 829;
select xmlagg(xmlparse(content a.INVENTORY_ID||',') order by a.P0670_ID).getclobval()
from D_INVENTORY_BATCHSUB a WHERE a.P0670_ID=19100800000001;
select xmlagg(xmlparse(content dbms_lob.substr(content,1000,1) || '')).getclobval()
|| xmlagg(xmlparse(content dbms_lob.substr(content,1000,1001) || '')).getclobval()
as a from stan_diligence where id = 835;
select xmlagg(xmlparse(content dbms_lob.substr(content,1000,1) || '') order by id).getclobval()
|| xmlagg(xmlparse(content dbms_lob.substr(content,1000,1001) || '') order by id).getclobval()
as a from stan_diligence;
dbms_lob.substr(content,32)
select (xmlparse(content dbms_lob.substr(content,500,1) || '').getclobval()
|| xmlparse(content dbms_lob.substr(content,500,501) || '').getclobval()
|| xmlparse(content dbms_lob.substr(content,500,1001) || '').getclobval()
|| xmlparse(content dbms_lob.substr(content,500,1501) || '').getclobval())
as content from stan_diligence;
select dbms_lob.substr(content) as a from stan_diligence where id = 829;
select * from stan_diligence order by id desc;
select * from (select ROWNUM sid, t.* from
(select id, csnm, cstp, type, attach_path, attach_name, status, remark, create_time, creator, update_time, updater, mbrc,
dbms_lob.substr(xmlparse(content dbms_lob.substr(content,1000,1) || '').getclobval()) as subcontent1,
dbms_lob.substr(xmlparse(content dbms_lob.substr(content,1000,1001) || '').getclobval()) as subcontent2
from stan_diligence where csnm= #{CSNM}
select a.id,listagg(a.email, ',') within group (order by a.id) email,
listagg(b.role_id, ',') within group (order by a.id) roleId
from sys_user a,sys_user_role b
where a.id = b.user_id and a.email is not null and b.role_id in (2,3,4)
group by a.id
select a.userid,a.roleid,b.real_name as realname,b.email from (
select user_id as userid,listagg(role_id, ',') within group (order by user_id) as roleid
from sys_user_role a where a.user_id in (select user_id from sys_worktips)
group by user_id) a,sys_user b where a.userid = b.id and a.roleid != '1' and b.email is not null
left join的时候要是直接的连接右边的表然后加上where过滤条件,这个时候会过滤掉不是where的数据,这个时候应该在右边的表上加上过滤条件当成一个表然后关联,这样才能把关联的数据和对应字段是null的数据都是关联出来了,这样做就是对的!
<select id="getDeptInfo" resultType="Map">
select a.deptcode,b.name,a.userids from
(select dept_code as deptcode,listagg(id, ',') within group (order by dept_code) as userids from sys_user
where dept_code is not null and state = '1' group by dept_code) a,sys_unit b
where a.deptcode = b.code
</select>
上面的查询条件计算出一个部门下面有多少个用户,用户用,隔开
注意的是强转能避免空指针,比toString()方法好多了,尽量的强转,避免不必要的麻烦,记住了就是了!
