1、数据操作

1.1、插入

1.2、删除

1.2.1、删除整个表数据

// (清空表中的数据)：删除内容、释放空间但不删除定义(保留表的数据结构)。与drop不同的是,只是清空表数据而已
truncate  table [talbeName]
//  truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）
delete from dbo.Sys_Test

1.3、更新

1.4、查询

1.4.1、获取第一条记录

select top 1 * from ems.d5000.semaphore where "date" between '2021-10-01' and '2021-10-31' order by "date" asc
select * from ems.d5000.semaphore where "date" between '2021-10-01' and '2021-10-31' order by "date" desc limit 1

1.4.2 分组合并统计个数

select 
sum(case when d.pri_flags='1' then d.num end) as acc
,sum(case when d.pri_flags='2' then d.num end)
,sum(case when d.pri_flags='3' then d.num end)
,sum(case when d.restrain_flag='0'  and d.pri_flags='1' then d.num end) 
,sum(case when d.restrain_flag='0'  and d.pri_flags='2' then d.num end)  
,sum(case when d.restrain_flag='0'  and d.pri_flags='3' then d.num end) 
from (select b.pri_flags,a.restrain_flag,count(*) as num from HISDB.ALARM.YX_BW_2021_10 a,EMS.EMS.RELAYSIG b ,EMS.EMS.SUBSTATION c  
where a.yx_id&0xFFFF0000FFFFFFFF=b.id 
and c.id=a.st_id  
and c.bv_id='112871465677750274'
and a.occur_time between '2021-10-03' and '2021-10-04' group by  a.restrain_flag,b.pri_flags) as d

1.4.3、获取所有表名

-- 获取数据库中的所有表名
SELECT TABLE_NAME
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA="lms_job"

1.4.4、查询某个表的所有列

-- 获取某个库的某个表的列属性
SELECT *
FROM information_schema.`COLUMNS`
WHERE TABLE_SCHEMA="lms_job" and TABLE_NAME="lms_job_task"

2、索引

2.1、创建索引

// 达梦数据库创建索引
CREATE [UNIQUE] INDEX "date_index" ON "EMS"."D5000"."SEMAPHORE"("date");

2.2、删除索引

// 达梦数据库
DROP INDEX "EMS"."D5000"."seq_no_index";

3、函数

3.1、执行函数

select round(1.1923,2);
select ceil(1.1923);

3.2、时间函数

select * from px_basesystem_fileservice.nfile_info_oss 
where STR_TO_DATE(upload_date,'%Y-%m-%d')=STR_TO_DATE(SYSDATE(),'%Y-%m-%d')

1、时间转字符串
STR_TO_DATE(SYSDATE(),'_%Y-%m-%d %H:%i:%s_')

2、重新格式化
DATE_FORMAT(date_add(sysdate(),interval -1 day),'%Y-%m-%d')