[color=brown]--插入数据方面
如果您需要数据库自增...
那么在插入的时候不需要指定自增列.
如果想自己指定ID列的值, 那么要设置
set identity_insert 数据库名.模式名.表名;
----然后插入数据;
example:
create table zhabei.test(
id bigint identity(1,1) primary key,
name varchar(100)
)
--这样插入 ID会自增.
insert into zhabei.test(name) values ('dddd');
--指定ID插入 会报错
insert into zhabei.test(id,name) values (21,'ddd');
--必须先设置identity 当然插入的ID有可能重复 违反主键约束
set identity_insert zhabei.test on
insert into zhabei.test(id,name) values (21,'ddd');
--从第二个开始查询10条记录
select * from zb.tb_sites_definition limit 10 offset 1;
limit 的使用有问题 , 建议不使用. 建议使用top
模糊查询
分页:
查询。DM数据库,作为一个大型关系型数据库,当然也支持分页查询了。下面将列出DM数据库的分页查询SQL语句:方法一:
DM数据库支持select top m,n * from tableName这样的语法,这里的m指的是起始记录位置,n指的是查询多少条记录。
例:
select top 0,5 * from tableName;
select top 5 * from tableName;
上面的两条SQL语句执行得到的数据是一样的,由上面的语句可以看出,在数据表中的第一条记录对应的位置应该是0,这点一定要注意。
实现查询指定页数据SQL语句如下:
select top (目标页值-1)*页大小,页大小 * from tableName;
方法二:
由于DM数据库是完全符合SQL -92标准的,因此也可以用别的方法来实现分页。
例:
select top 页大小 * from tableName where id not in (select top (目标页值-1)*页大小 * from tableName);
这条SQL语句当id是关键字时,是没有问题的,但是如果这里的id不是唯一的关键字,也就是说当id有重复情况出现时,这天SQL语句执行得到的结果将是错误的。
方法三:
select top 页大小 * from tableName where (id 〉 (select max(id) from (select top 页大小*目标页值id from tableName order by id) as T)) order by id
这条SQL语句和上面的两条都不能等效,当然也会存在方法二中的关键字问题,但是效率是很高的。
方法四:
将方法一中的SQL语句应用到存储过程来实现分页,存储过程如下:
create or replace procedure pageProcedure(sqlStr varchar(4000),pageIndex int, pageSize int) as
countSql varchar(4100); --得到总记录条数SQL语句
dataSql varchar(4100); --得到指定数据的SQL语句
tempTableSql varchar(1000) := 'create table #tempTable (pageCount int)'; --创建一个临时表,用于后面得到总页数结果集
countNum int; --总记录条数
pageCount int; --总页数
startRow int; --所取数据的起始行
begin
execute immediate tempTableSql; --创建一个临时表
countSql := 'select count(1) as countNum from (' + sqlStr + ')';
execute immediate countSql into countNum; --得到总记录条数,并将值存入到countNum中
--下面判断计算出总页数
if(mod(countNum, pageSize) = 0) then
pageCount := countNum / pageSize;
else
pageCount := countNum / pageSize + 1;
end if;
execute immediate countSql; --得到总记录数结果集
execute immediate 'insert into #tempTable values (?)' using pageCount; --将总页数写入临时表
execute immediate 'select * from #tempTable'; --取临时表数据结果集
startRow := (pageIndex - 1) * pageSize;
dataSql := 'select top ' || startRow || ',' || pageSize || ' * from (' + sqlStr + ')';
execute immediate dataSql; --取指定数据 end;
达梦数据库的存储过程调用和JAVA中调用函数类似,调用语句如下:
call pageProcedure(' select * from tableName',2,10);
该存储过程执行可以得到3个结果集,分别是总记录条数、总页数、数据集。
在方法一中的分页SQL语句和MySql的很类似,MySql的分页SQL语句如下:
select * from tableName limit (目标页值-1)*页大小,页大小;
在方法二和方法三中的SQL语句,基本上在所有的关系数据库中都能使用,可能要做少量调整。
方法四和在SQL Server中使用存储过程取指定页数据类似,下面给出SQL Server中的存储过程供参考:create procedure pageProcedure
@sqlStr nvarchar(4000), --查询字符串@pageIndex int, --第N页
@pageSize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 Output,@sqlStr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pageSize) as 总页数--,@rowcount as 总行数,@pageIndex as 当前页 set @pageIndex=(@pageIndex-1)*@pageSize+1 exec sp_cursorfetch @P1,16,@pageIndex,@pageSize exec sp_cursorclose @P1 set nocount off
在实际的项目应用中,我们一般采用第四个方法,因为调用这个存储过程就能得到我们想要的数据,而不需要写程序来判断了。调用方法四中的存储过程,得到翻页结果集的JAVA方法如下:
public Map execSpPaging(String sqlStr, int pageIndex, int pageSize)
throws SQLException { Map m = new HashMap();
String query_string = sqlStr.replaceAll("'", "''");
String sql = "call pageProcedure('" + query_string + "'," + pageIndex + "," + pageSize + ")"; statement.execute(sql);
ResultSet rsCountNum = statement.getResultSet();
if (rsCountNum != null) {
if (rsCountNum.next()) {
int countNum = rsCountNum.getInt("countNum");
m.put("countNum", countNum);
} else {
m.put("countNum", 0); }
}
// 参照下列的相关存储过程
if (statement.getMoreResults()) { // 第二个记录记录的是总页数
ResultSet rsPageCount = statement.getResultSet(); if (rsPageCount.next()) {
int pageCount = rsPageCount.getInt("pageCount");
m.put("pageCount", pageCount);
} else
m.put
("pageCount", "1");
} else
m.put("PageCount", "1");
// 由于存储过程的关系,返回的结果集在第三个记录集
if (statement.getMoreResults()) {
m.put("dataResultSet", statement.getResultSet());
} else {
m.put("dataResultSet", null); }
return m;
}
模糊查询:
不支持xml的参数设置
直接写成 like '%?%' ??? 对吗? 有转化问题. 写成 '%$?$%' 这样可以嘛?
<select id="searchBySearchBean"
resultMap="abatorgenerated_TbSitesDefinitionResult"
parameterClass="com.shietion.base.domain.pager.SearchBean">
select top #start#,#rowsPerPage# sites_id , NAME, SITE_DESCTION, UPDATE_BY, UPDATE_TIME
,status ,domain from TB_SITES_DEFINITION
<dynamic prepend="where">
<isNotEmpty prepend="where" property="name">
NAME like '%$name$%'
</isNotEmpty>
<isNotEmpty prepend="and" property="domain">
domain like '%$domain$%'
</isNotEmpty>
<isNotEqual prepend="and" property="status"
compareValue="-1">
status =#status#
</isNotEqual>
</dynamic>
order by name asc
</select>
这样的却可以 , 但是 这样有注入漏洞问题, 如何避免???
select * from tbl_school where school_name like '%'+#name#+'%'
而 DM 和sqlserver 的写法一样 这样可以避免注入漏洞
如果Mysql 移植到DM sql语言需要遵循sql规范, 而mysql 有些是不遵循的.
--------------------------------------------------
group by 的用法 mysql 没有限制
而DM必须遵从sql规范 group by 中的列必须是查询中的列
可以这么写:
select id,FORM_GROUP_ID,FORM_NAME,FORM_TYPE,FORM_ADDRESS,TIME,DESIGNER,HANDLER,STATUS,(select max
(VERSION) from zhabei.tb_zdform) as VERSION,FLOW_NAME,FORM_CONTENT
from zhabei.tb_zdform
where form_group_id = 8 and
id in (select min(id) from zhabei.tb_zdform group by FORM_GROUP_ID)[/color]
如果您需要数据库自增...
那么在插入的时候不需要指定自增列.
如果想自己指定ID列的值, 那么要设置
set identity_insert 数据库名.模式名.表名;
----然后插入数据;
example:
create table zhabei.test(
id bigint identity(1,1) primary key,
name varchar(100)
)
--这样插入 ID会自增.
insert into zhabei.test(name) values ('dddd');
--指定ID插入 会报错
insert into zhabei.test(id,name) values (21,'ddd');
--必须先设置identity 当然插入的ID有可能重复 违反主键约束
set identity_insert zhabei.test on
insert into zhabei.test(id,name) values (21,'ddd');
--从第二个开始查询10条记录
select * from zb.tb_sites_definition limit 10 offset 1;
limit 的使用有问题 , 建议不使用. 建议使用top
模糊查询
分页:
查询。DM数据库,作为一个大型关系型数据库,当然也支持分页查询了。下面将列出DM数据库的分页查询SQL语句:方法一:
DM数据库支持select top m,n * from tableName这样的语法,这里的m指的是起始记录位置,n指的是查询多少条记录。
例:
select top 0,5 * from tableName;
select top 5 * from tableName;
上面的两条SQL语句执行得到的数据是一样的,由上面的语句可以看出,在数据表中的第一条记录对应的位置应该是0,这点一定要注意。
实现查询指定页数据SQL语句如下:
select top (目标页值-1)*页大小,页大小 * from tableName;
方法二:
由于DM数据库是完全符合SQL -92标准的,因此也可以用别的方法来实现分页。
例:
select top 页大小 * from tableName where id not in (select top (目标页值-1)*页大小 * from tableName);
这条SQL语句当id是关键字时,是没有问题的,但是如果这里的id不是唯一的关键字,也就是说当id有重复情况出现时,这天SQL语句执行得到的结果将是错误的。
方法三:
select top 页大小 * from tableName where (id 〉 (select max(id) from (select top 页大小*目标页值id from tableName order by id) as T)) order by id
这条SQL语句和上面的两条都不能等效,当然也会存在方法二中的关键字问题,但是效率是很高的。
方法四:
将方法一中的SQL语句应用到存储过程来实现分页,存储过程如下:
create or replace procedure pageProcedure(sqlStr varchar(4000),pageIndex int, pageSize int) as
countSql varchar(4100); --得到总记录条数SQL语句
dataSql varchar(4100); --得到指定数据的SQL语句
tempTableSql varchar(1000) := 'create table #tempTable (pageCount int)'; --创建一个临时表,用于后面得到总页数结果集
countNum int; --总记录条数
pageCount int; --总页数
startRow int; --所取数据的起始行
begin
execute immediate tempTableSql; --创建一个临时表
countSql := 'select count(1) as countNum from (' + sqlStr + ')';
execute immediate countSql into countNum; --得到总记录条数,并将值存入到countNum中
--下面判断计算出总页数
if(mod(countNum, pageSize) = 0) then
pageCount := countNum / pageSize;
else
pageCount := countNum / pageSize + 1;
end if;
execute immediate countSql; --得到总记录数结果集
execute immediate 'insert into #tempTable values (?)' using pageCount; --将总页数写入临时表
execute immediate 'select * from #tempTable'; --取临时表数据结果集
startRow := (pageIndex - 1) * pageSize;
dataSql := 'select top ' || startRow || ',' || pageSize || ' * from (' + sqlStr + ')';
execute immediate dataSql; --取指定数据 end;
达梦数据库的存储过程调用和JAVA中调用函数类似,调用语句如下:
call pageProcedure(' select * from tableName',2,10);
该存储过程执行可以得到3个结果集,分别是总记录条数、总页数、数据集。
在方法一中的分页SQL语句和MySql的很类似,MySql的分页SQL语句如下:
select * from tableName limit (目标页值-1)*页大小,页大小;
在方法二和方法三中的SQL语句,基本上在所有的关系数据库中都能使用,可能要做少量调整。
方法四和在SQL Server中使用存储过程取指定页数据类似,下面给出SQL Server中的存储过程供参考:create procedure pageProcedure
@sqlStr nvarchar(4000), --查询字符串@pageIndex int, --第N页
@pageSize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 Output,@sqlStr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pageSize) as 总页数--,@rowcount as 总行数,@pageIndex as 当前页 set @pageIndex=(@pageIndex-1)*@pageSize+1 exec sp_cursorfetch @P1,16,@pageIndex,@pageSize exec sp_cursorclose @P1 set nocount off
在实际的项目应用中,我们一般采用第四个方法,因为调用这个存储过程就能得到我们想要的数据,而不需要写程序来判断了。调用方法四中的存储过程,得到翻页结果集的JAVA方法如下:
public Map execSpPaging(String sqlStr, int pageIndex, int pageSize)
throws SQLException { Map m = new HashMap();
String query_string = sqlStr.replaceAll("'", "''");
String sql = "call pageProcedure('" + query_string + "'," + pageIndex + "," + pageSize + ")"; statement.execute(sql);
ResultSet rsCountNum = statement.getResultSet();
if (rsCountNum != null) {
if (rsCountNum.next()) {
int countNum = rsCountNum.getInt("countNum");
m.put("countNum", countNum);
} else {
m.put("countNum", 0); }
}
// 参照下列的相关存储过程
if (statement.getMoreResults()) { // 第二个记录记录的是总页数
ResultSet rsPageCount = statement.getResultSet(); if (rsPageCount.next()) {
int pageCount = rsPageCount.getInt("pageCount");
m.put("pageCount", pageCount);
} else
m.put
("pageCount", "1");
} else
m.put("PageCount", "1");
// 由于存储过程的关系,返回的结果集在第三个记录集
if (statement.getMoreResults()) {
m.put("dataResultSet", statement.getResultSet());
} else {
m.put("dataResultSet", null); }
return m;
}
模糊查询:
不支持xml的参数设置
直接写成 like '%?%' ??? 对吗? 有转化问题. 写成 '%$?$%' 这样可以嘛?
<select id="searchBySearchBean"
resultMap="abatorgenerated_TbSitesDefinitionResult"
parameterClass="com.shietion.base.domain.pager.SearchBean">
select top #start#,#rowsPerPage# sites_id , NAME, SITE_DESCTION, UPDATE_BY, UPDATE_TIME
,status ,domain from TB_SITES_DEFINITION
<dynamic prepend="where">
<isNotEmpty prepend="where" property="name">
NAME like '%$name$%'
</isNotEmpty>
<isNotEmpty prepend="and" property="domain">
domain like '%$domain$%'
</isNotEmpty>
<isNotEqual prepend="and" property="status"
compareValue="-1">
status =#status#
</isNotEqual>
</dynamic>
order by name asc
</select>
这样的却可以 , 但是 这样有注入漏洞问题, 如何避免???
select * from tbl_school where school_name like '%'+#name#+'%'
而 DM 和sqlserver 的写法一样 这样可以避免注入漏洞
如果Mysql 移植到DM sql语言需要遵循sql规范, 而mysql 有些是不遵循的.
--------------------------------------------------
group by 的用法 mysql 没有限制
而DM必须遵从sql规范 group by 中的列必须是查询中的列
可以这么写:
select id,FORM_GROUP_ID,FORM_NAME,FORM_TYPE,FORM_ADDRESS,TIME,DESIGNER,HANDLER,STATUS,(select max
(VERSION) from zhabei.tb_zdform) as VERSION,FLOW_NAME,FORM_CONTENT
from zhabei.tb_zdform
where form_group_id = 8 and
id in (select min(id) from zhabei.tb_zdform group by FORM_GROUP_ID)[/color]