可知dbserver_name是accunetsvr
dbserver_ip 是192.168.0.6
dbserver_port 是4100
dbname为nextip
url为 jdbc:sybase:Tds:192.168.0.6:4100/nextip
37、 创建identity列,如果是create table 时一定是numeric型。如果想要创建数据库自动为新建的所有表增加一个隐藏的identity字段,用[sp_dboption database_name, "auto identity", "true"]。在检索数据的时候必须隐式加上SYB_IDENTITY_COL作为隐藏的identity列,例如select SYB_IDENTITY_COL, sn_name from t-subnet
默认的隐藏精度大小为10如果用户想要增大其精度,可用[sp_configure "size of auto identity", 新的精度],例如: sp_configure "size of auto identity",15
38、 从select into 创建一个新的idenetity列,这在sql语句分页检索用。
Select idenetity_name=identity(精度) , * into new_table from old_table;
如:
select id0=identity(18),* into #subnets from t_subnet where sn_type=10;
39、 实现用sql语句进行分页查询方法:
A. 创建一个临时表带identity字段 select id=indentity(20), * into #table_anme from table_name where 条件
B. 然后根据id进行检索第n条到m条数据 (也可用between and)
C. 最后Drop掉该临时表
D. 注意要打开数据库的select into /bulk copy属性 sp_dboption database_name, "select into/bulk copy", "true"才能进行select into操作
E. mssql中格式为select identity(int) id, * from #table_name from table_name where 条件
40、 linux下访问sql-server用db-library与sybase相同要素。只是远程访问端口为sql-server指定的1433
49、 日期计算在ms-sql中
dateadd(日期代码,日期值, 日期)
select convert(varchar,getdate(),120) as year,convert(varchar,dateadd(ss, 1200,getdate()),120) as nYear
go
日期部分 简写 &nbbsp; 值
year yy 1753--9999
quarter qq 1--4
month mm 1--12
day of year dy 1--366
day dd 1--31
week wk 1--53
weekday dw 1--7(Sunday--Saturday)
hour hh 0--23
minute mi 0--59
second ss 0--59
milisecond ms 0-999
也可
select convert(varchar, getdate(), 1)''''bp''''+convert(varchar,getdate(),108) as oldtime,convert(varchar,dateadd(ss,1200,getdate())1)''''bp''''+convert(varchar,dateadd(ss,1200,getdate()),108) as date
也可
select convert(varchar,convert(datie''''2012''''),111)
go
50、 PostgreSQL中的lib编程时,select 和update /delete的成功失败条件判断是不同的。
Update/delete:: strcmp(PQcmdStatus(temp_res), "")==0
select :: !temp_res||PQresultStatus(temp_res) != PGRES_TUPLES_OK
51、 日期各格式的引用模式ms-sql和sybase中都通用
yyyy/mm/dd hh24:mi:ss
select convert(varchar, getdate(), 1)''''bp''''+convert(varchar,getdate(),108) as oldtime,convert(varchar,dateadd(ss,1200,getdate())1)''''bp''''+convert(varchar,dateadd(ss,1200,getdate()),108) as date
select name,text from all_source where ye''''FUTO'''' and ae''''F_CHECK_IBCP'''';
select convert(varchar,convert(datie''''2012''''),111)
go
Select count(ipgs_host_ne|''''ot''''||f_ipad_chang_c'''s''''''') From t_ipdev_gs
yyyy/mm/ddhh24miss
Select CO_DESC,to_char(sysdate - /8''''yyyymmddhhms'''') co_date from T_LICENSE
dateadd(ss, -1800, getdate())
select co_desc, convert(varchar, dateadd(ss, -1800, getdate()),111)+convert(varchar,dateadd(ss, -1800, getdate()),108) as co_date from t_license
select convert(varchar, getdate(), 1)''''bp''''+convert(varchar,getdate(),108) as oldtime,convert(varchar, dateadd(ss, -1800, getdate()),111)+convert(varchar,dateadd(ss, -1800, getdate()),108) as co_date
yymmddhh:mi:ss
select convert(varchar,getdate(),112)+convert(varchar,getdate(),8)
go
52、 显示sql执行时间用: isql -Unextip -Pnextip -Sleon1 -p
进入即可,加小写-p参数
53、 MS-SQL中创建数据库
create database NEXTIPDB
on primary
(
name = DEVDB,
filename =bp''''d:\mssql_data\dev.d'''',
size = 30MB,
maxsize = 100MB,
filegrowth = 10MB
),
(
name = DEVDB1,
filename =bp''''d:\mssql_data\devd.d'''',
size = 10MB,
filegrowth = 10MB
)
Log on
(
name = DEVDBLOG,
filename =bp''''e:\mssql_log\devdbl.d'''',
size = 10MB,
maxsize = 100MB,
filegrowth = 10MB
)
go
54、 细小区别
Sybase::
IF NOT EXISTS (SELECT * FROM master..syslogins, master..sysdatabases
WHERE master..syslogins.suid = master..sysdatabases.suid
AND master..syslogins.name =bp''''xi'''')
EXEC sp_changedbowner nextip, NEXTIPDB
GO
Ms-sqlserver::
IF NOT EXISTS (SELECT * FROM master..syslogins, master..sysdatabases
WHERE master..syslogins.sid = master..sysdatabases.sid
AND master..syslogins.name =bp''''xi'''')
EXEC sp_changedbowner nextip, NEXTIPDB
GO