数据库增删改查

1.增
#在部分指定的列插入数据

INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees');

#插入整行数据

INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing');

#把子查询的数据插入表

insert into DQAM3 
 select * from DQAM2 where alarm_severity <= 3;
commit;

2.删
#删除指定行

DELETE FROM Person WHERE LastName = 'Wilson';

#删除所有行

DELETE FROM Person;

3.改
#更新某一行中的一个列

UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson';

#更新某一行中的若干列

UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'
WHERE LastName = 'Wilson';

4.查
#查询指定列

SELECT LastName,FirstName FROM Persons;

#查询所有列

SELECT * FROM Persons;

#三张表关联查询

select a.label_cn,a.cuid,c.group_name,a.pathinfo from traph a,traphgroup_related b,traphgroup c where 
a.cuid=b.object_cuid and b.related_group_cuid=c.cuid;

#计数count(1)(即按group_name来计数）

select count(1),b.group_name  from traphgroup_related a,traphgroup b where a.related_group_cuid=b.cuid group by b.group_name;

#时间格式转化:to_date(‘2013-01-25 00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’)

select ems_name,count(1) from history_alarm where nealarm_time >=to_date('2013-01-25 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND nealarm_time <=to_date('2013-01-25 10:00:00', 'yyyy-mm-dd hh24:mi:ss')
group by ems_name;

#模糊搜索like和not like

select * from alarm_trans_resource where fdn like '%EMS=SZH-N31-1-P:ManagedElement=172%';

SELECT * from history_alarm  WHERE alarm_remark not like '%SYNC%';

#匹配范围in

SELECT * from history_alarm  WHERE related_ems_cuid in (
'NMS_SYSTEM-8a49a29232b39b690132f6f362811887','NMS_SYSTEM-8a49a29232b39b690132f6f362811833'
)

#子查询作为匹配范围in

select count(*) from history_alarm where related_ems_cuid in (select cuid from nms_system where related_space_cuid='DISTRICT-00001-00009-00001' or related_space_cuid='DISTRICT-00001-00009');

#升序排列order by

select * from current_alarm  where 
nealarm_time =to_date('2012-09-27 09:17:31', 'yyyy-mm-dd hh24:mi:ss')and nealarm_time <=to_date('2012-09-28 00:00:00', 'yyyy-mm-dd hh24:mi:ss') order by nealarm_time;

#逻辑或or：查询出满足其中任何一个条件的结果

select cuid,label_cn,related_space_cuid from nms_system where related_space_cuid='DISTRICT-00001-00009-00009' or related_space_cuid='DISTRICT-00001-00009';

#字符串连接符||，把两个字段的字符连接成一个字符

SELECT * from std_fmdb.tfa_alarm_act where (nvl(alarm_text0,'')||nvl(alarm_text1,'')||nvl(alarm_text2,'') like '%东莞B接口%';

#空值判断nvl。nvl(alarm_text0,’’)：如果alarm_text0为空值，则返回空值，否则返回alarm_text0的值。’‘可以改为’字符串B’，不一定要是空值。

SELECT * from std_fmdb.tfa_alarm_act where (nvl(alarm_text0,'')||nvl(alarm_text1,'')||nvl(alarm_text2,'') like '%东莞B接口%';

#非空is not null

select ems_name,is_standard,standard_key from history_alarm
where nealarm_time >= to_date('2013-03-17 00:00:00','yyyy-mm-dd hh24:mi:ss')
and nealarm_time < to_date('2013-03-18 00:00:00','yyyy-mm-dd hh24:mi:ss')
and is_standard =2
and standard_key is not null;

#or内嵌于and

SELECT t.cuid,t.label_cn,tp.a_mac,tp.z_mac
FROM TRAPH t , TRAPH_ROUTE tr, TRAPH_ROUTE_TO_PATH tp
WHERE t.cuid = tr.RELATED_SERVICE_CUID 
AND tr.cuid = tp.TRAPH_ROUTE_CUID 
AND (tp.A_MAC ='PTP-8a49bf442cabdeb8012d1217483270ab' 
OR tp.Z_MAC ='PTP-8a49bf442cabdeb8012d1217483270ab' 
OR tp.A_MP ='PTP-8a49bf442cabdeb8012d1217483270ab' 
OR tp.Z_MP ='PTP-8a49bf442cabdeb8012d1217483270ab');

#合并查询结果union all

select ems_name,count(1) from DQAM3 where ems_name='Alcatel/xdsnm' and related_current_cuid in (select nms_alarm_id from zhjk) group by ems_name 
union all
select ems_name,count(1) from DQAM3 where ems_name='Alcatel/xdsshm' and related_current_cuid in (select nms_alarm_id from zhjk) group by ems_name;

#查询1的结果减去查询2的结果MINUS

select related_current_cuid,rtu_alarm_id,nealarm_time,revalarm_time,alm_devinfo from DQAM3 where ems_name='Alcatel/xdsnmm' and related_current_cuid in (select related_current_cuid from DQAM3
MINUS select nms_alarm_id from zhjk) ;