*假设已经连接上SQL服务器,连接句柄为Sql_Connect SQL_DataBase="yafox" &&要建立的数据库名称 SQL_CommandStr="SELECT count(name) as cntdatabase FROM sysdatabases WHERE name=?SQL_DataBase" =SQLEXEC(Sql_Connect,SQL_CommandStr,"SqlCurs") IF SqlCurs.cntdatabase=0 WAIT "正在建立数据库"+SQL_DataBase+",请稍候……" WINDOW NOWAIT SQL_CommandStr="create database "+SQL_DataBase+" on (name='"+SQL_DataBase+"',filename='c:\"+SQL_DataBase+".mdf',size=50,maxsize=1024,filegrowth=50) " SQL_CommandStr=SQL_CommandStr+"log on (name='"+SQL_DataBase+"_log',filename='c:\"+SQL_DataBase+".ldf',size=50,maxsize=1024,filegrowth=50)" =SQLCOMMIT(Sql_Connect) IF SQLEXEC(Sql_Connect,SQL_CommandStr)<0 WAIT CLEAR MESSAGEBOX("创建数据库"+SQL_DataBase+"失败",16,"失败") ELSE WAIT CLEAR MESSAGEBOX("创建数据库"+SQL_DataBase+"成功",16,"成功") ENDIF MESSAGEBOX("创建数据库"+SQL_DataBase+"失败",16,"失败") ELSE MESSAGEBOX("数据库"+SQL_DataBase+"已经存在",16,"重复") ENDIF ****************************************
SQL_CommandStr="create database "+SQL_DataBase+" on (name='"+SQL_DataBase+"',filename='c:\"+SQL_DataBase+".mdf',size=50,maxsize=1024,filegrowth=50) " SQL_CommandStr=SQL_CommandStr+"log on (name='"+SQL_DataBase+"_log',filename='c:\"+SQL_DataBase+".ldf',size=50,maxsize=1024,filegrowth=50)" 语句解释: name 数据库的逻辑名称 filename 数据库的物理名称及位置 size 初始大小 (M) maxsize 最大限制 (M,可以忽略) filegrowth 数据库增量
*建立表 CodeUser 的命令如下: SQL_CommandStr="create table CodeUser (userid nchar(4),username nchar(10),userpass nchar(24),uniqueid int identity,updatetime datetime)" =SQLEXEC(Sql_Connect,SQL_CommandStr)
当然,在建立一个表之前,我们应该先查询一下它是否已经存在于数据库中,查询命令如下: SQL_CommandStr="SELECT count(id) as cntid FROM sysobjects WHERE name='CodeUser' and xtype='u'" =SQLEXEC(Sql_Connect,SQL_CommandStr,"SqlCurs") IF SqlCurs.cntid>0 MESSAGEBOX("表CodeUser已经存在",16,"重复") ENDIF
*建立表 CodeUser 的INSERT, UPDATE的触发器命令如下: SQL_CommandStr="CREATE TRIGGER CodeUser_IU ON CodeUser FOR INSERT, UPDATE AS "+; "update CodeUser set Updatetime=getdate() where uniqueid in (select uniqueid from inserted)" =SQLEXEC(Sql_Connect,SQL_CommandStr)
此触发器的功能是在增加或者编辑过CodeUser表后,自己更新Updatetime字段为系统当前时间用以记录最后的更改时间 当然在建立触发器之前,我们也得先查询一下它是否已经存在,查询命令如下: SQL_CommandStr="SELECT count(id) as cntid FROM sysobjects WHERE name='CodeUser_IU' and xtype='tr'" =SQLEXEC(Sql_Connect,SQL_CommandStr,"SqlCurs") IF SqlCurs.cntid>0 MESSAGEBOX("触发器CodeUser_IU已经存在",16,"重复") ENDIF
