您现在的位置：首页 > 实用文 > 其他范文

作业员动态关爱表范文

时间：2023-01-09 07:48:29 其他范文收藏本文下载本文

下面是小编给大家带来关于作业员动态关爱表范文，本文共11篇，一起来看看吧，希望对您有所帮助。

作业员动态关爱表范文

篇1：动态打开一个表

程序名： useTable.prg

* 程序说明：打开一个想要打开的表

* 变量说明： cTableName 要想打开的表的名称

* 使用说明： do usetable with “表1”

para cTableName

if !used('&cTableName')

use &cTableName in 0 share

endif

sele &cTableName

篇2：mysql数据库动态创建表

mysql数据库动态创建表

大家一般可能很少有这种需求吧，我以前也没有遇到过，但这次做项目需要这么做，

就是表的字段名和字段数都不是固定的，要根据需要来创建。

这是我的创建形成过程，大家照着演示一下就知道了我的动态表的来龙去脉了。

第一步.创建相关表

/*---建立所有指标信息的临时表---*/

drop table if exists INTERBANKBONDQUOTE_SClass;

create table INTERBANKBONDQUOTE_SClass (

Name varchar(50) not null,

id int Primary key,

Parent int,

Value varchar(50)

);

insert into INTERBANKBONDQUOTE_SClass(Name,id,Value)values('最新成交',0,'');

insert into INTERBANKBONDQUOTE_SClass(Name,id,Value)values('买入信息',1,'');

insert into INTERBANKBONDQUOTE_SClass(Name,id,Value)values('买卖价差',2,'');

insert into INTERBANKBONDQUOTE_SClass(Name,id,Value)values('卖出信息',3,'');

insert into INTERBANKBONDQUOTE_SClass(Name,id,Parent,Value)values('中债最新估值',4,-1,'');

insert into INTERBANKBONDQUOTE_SClass(Name,id,Value)values('含权债行权指标',5,'');

insert into INTERBANKBONDQUOTE_SClass(Name,id,Value)values('基础指标',6,'');

insert into INTERBANKBONDQUOTE_SClass(Name,id,Value)values('双边报价笔数',7,'');

insert into INTERBANKBONDQUOTE_SClass(Name,id,Parent,Value)values('报价方',8,1,'col4');

insert into INTERBANKBONDQUOTE_SClass(Name,id,Parent,Value)values('报价方会员号',9,1,'col5');

insert into INTERBANKBONDQUOTE_SClass(Name,id,Parent,Value)values('报价时间',10,1,'col6');

insert into INTERBANKBONDQUOTE_SClass(Name,id,Parent,Value)values('匿名',11,1,'col7');

第二步：/*---建立显示数据表格标题的表---*/

drop table if exists INTERBANKBONDQUOTE_T;

create table INTERBANKBONDQUOTE_T as select concat('\'',a.Name,',',b.Name,'\' __TITLE__',b.Value);

第三步：这里有两种实现方法。因为在mysql中，这时的变量长度受到了限制，本来应该longtext足够长的，可实际只返回了限制长度的。如果字段太多了，就要用第二种方法。

存储过程A: 这里变量返回值长度受限，字段不多时可以。

DELIMITER $$

DROP PROCEDURE IF EXISTS `dzhappdb_bond`.`INTERBANKBONDQUOTE_TSP`$$

CREATE PROCEDURE INTERBANKBONDQUOTE_TSP

BEGIN

DECLARE objs1 TEXT;

DECLARE objs TEXT;

SELECT GROUP_CONCAT(col1) INTO objs1 FROM T;

SET objs =CONCAT('CREATE TABLE Title AS SELECT ',objs1);

SET @sql_txt = objs;

PREPARE stmt FROM @sql_txt;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END$$

call INTERBANKBONDQUOTE_TSP;

存储过程B：这样不管字段多少，只要数据库支持就可以创建成功，

但不如第一个方法简洁。

DROP PROCEDURE IF EXISTS INTERBANKBONDQUOTE_TSP_Title;

drop table if EXISTS Title;

CREATE PROCEDURE INTERBANKBONDQUOTE_TSP_Title()

proc:begin

DECLARE add_sql LONGTEXT;

DECLARE insert_sql LONGTEXT;

DECLARE nhh_sql varchar(200);

DECLARE column_name varchar(100);

DECLARE column_value varchar(100);

DECLARE mycount int;

DECLARE len int;

DECLARE strlen int;

DECLARE cursor_Title CURSOR for select col1 from INTERBANKBONDQUOTE_T;

create table Title(mid int);

insert into Title values (100);

select count(col1) into @mycount from INTERBANKBONDQUOTE_T;

OPEN cursor_Title;

REPEAT

FETCH cursor_Title INTO nhh_sql;

begin

set @mycount=@mycount-1;

set @strlen=CHARACTER_LENGTH(nhh_sql);

set @len=INSTR(nhh_sql,' ');

set @column_name=RIGHT(nhh_sql,@strlen-@len);

set @column_value=LEFT(nhh_sql,@len);

set @add_sql=CONCAT('ALTER table Title add COLUMN ',@column_name,' varchar(100)');

set @insert_sql=CONCAT('update Title set ',@column_name,'=',@column_value,' where mid=100');

PREPARE stmt1 FROM @add_sql;

EXECUTE stmt1;

PREPARE stmt2 FROM @insert_sql;

EXECUTE stmt2;

DEALLOCATE PREPARE stmt1;

DEALLOCATE PREPARE stmt2;

end;

until @mycount<=0

END REPEAT;

CLOSE cursor_Title;

ALTER table Title drop column mid;

end proc;

call INTERBANKBONDQUOTE_TSP_Title;

篇3：动态关联表数据库教程

动态

原帖地址:

community.csdn.net/Expert/topic/3452/3452577.xml?temp=.1377375

--示例数据

create table [table](A sysname,B varchar(10))

insert [table] select 'table_1','a'

union all select 'table_2','b'

create table table_1(A int)

insert table_1 select 1

union all select 2

create table table_2(A int)

insert table_2 select 3

union all select 4

/*--问题说明:

table中,A字段存储着其他表的名称

A字段中,记录和各个表的A字段的最大值

即得到如下结果:

table.B C

-------- -----------

a 1

b 2

--*/

--处理方法

declare @s varchar(8000)

set @s=''

select @s=@s+' when '''+A+''' then(select max(A) from ['+A+'])'

from [table] group by A

exec('select B,C=case A'+@s+' end from [table]')

--删除测试

drop table [table],table_1,table_2

/*--测试结果

B C

---------- -----------

a 2

b 4

--*/

篇4：创建动态MSSQL数据库表

复制代码代码如下:

'btnCreateOthers

Me.btnCreateOthers.Location=NewSystem.Drawing.Point(17,43)

Me.btnCreateOthers.Name=“btnCreateOthers”

Me.btnCreateOthers.Size=NewSystem.Drawing.Size(104,23)

Me.btnCreateOthers.TabIndex=5

Me.btnCreateOthers.Text=“创建规则和索引”

feedom.net

'btnDropTable

Me.btnDropTable.Location=NewSystem.Drawing.Point(138,43)

Me.btnDropTable.Name=“btnDropTable”

Me.btnDropTable.TabIndex=6

Me.btnDropTable.Text=“删除表”

'btnViewData

Me.btnViewData.Location=NewSystem.Drawing.Point(351,43)

Me.btnViewData.Name=“btnViewData”

Me.btnViewData.TabIndex=7

Me.btnViewData.Text=“查看数据”

'btnViewSP

Me.btnViewSP.Location=NewSystem.Drawing.Point(230,43)

feedom.net

Me.btnViewSP.Name=“btnViewSP”

Me.btnViewSP.Size=NewSystem.Drawing.Size(104,23)

Me.btnViewSP.TabIndex=8

Me.btnViewSP.Text=“查看存储过程”

'btnViewView

Me.btnViewView.Location=NewSystem.Drawing.Point(443,43)

Me.btnViewView.Name=“btnViewView”

Me.btnViewView.TabIndex=9

Me.btnViewView.Text=“查看视图”

'DataGrid1

Me.DataGrid1.DataMember=“”

Me.DataGrid1.HeaderForeColor=System.Drawing.SystemColors.ControlText

Me.DataGrid1.Location=NewSystem.Drawing.Point(20,76)54com.cn

Me.DataGrid1.Name=“DataGrid1”

Me.DataGrid1.Size=NewSystem.Drawing.Size(500,183)

Me.DataGrid1.TabIndex=10

'Form1

Me.AutoScaleBaseSize=NewSystem.Drawing.Size(5,13)

Me.ClientSize=NewSystem.Drawing.Size(538,281)

Me.Controls.AddRange(NewSystem.Windows.Forms.Control{Me.DataGrid1,Me.btnViewView,_

Me.btnViewSP,Me.btnViewData,Me.btnDropTable,Me.btnCreateOthers,Me.btnAlterTable,_

复制代码代码如下:

Me.CreateViewBtn,Me.CreateSPBtn,Me.CreateTableBtn,Me.CreateDBBtn})

Me.Name=“Form1”

Me.Text=“动态创建SQLServer数据库、表、存储过程等架构信息”

CType(Me.DataGrid1,System.ComponentModel.ISupportInitialize).EndInit()

Me.ResumeLayout(False)

EndSub

#EndRegion

'创建数据库

PrivateSubCreateDBBtn_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)_

HandlesCreateDBBtn.Click

conn=NewSqlConnection(ConnectionString)

'打开连接

Ifconn.StateConnectionState.OpenThen

conn.Open()

EndIf

'MyDataBase为数据库名称

DimsqlAsString=“CREATEDATABASEMyDataBaseONPRIMARY(Name=MyDataBase_data,filename=”+_

54com.cn

“'D:\MyDataBase.mdf',size=3,”+“maxsize=5,filegrowth=10%)logon”+“(name=MyDataBase_log,”+_

“filename='D:\MyDataBase.ldf',size=3,”+“maxsize=20,filegrowth=1)”

cmd=NewSqlCommand(sql,conn)

Try

cmd.ExecuteNonQuery()

CatchaeAsSqlException

MessageBox.Show(ae.Message.ToString())

EndTry

EndSub

'创建表

PrivateSubCreateTableBtn_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)_

HandlesCreateTableBtn.Click

conn=NewSqlConnection(ConnectionString)

'打开连接

Ifconn.State=ConnectionState.OpenThen

conn.Close()

EndIf

ConnectionString=“DataSource=.;InitialCatalog=MyDataBase;UserId=sa;Password=;”

conn.ConnectionString=ConnectionString

conn.Open()

sql=“CREATETABLEmyTable”+“(myIdINTEGERCONSTRAINTPKeyMyIdPRIMARYKEY,”+_

“myNameCHAR(50)NOTNull,myAddressCHAR(255),myValuesFLOAT)”

cmd=NewSqlCommand(sql,conn)

Try

cmd.ExecuteNonQuery()

'添加纪录

54com.cn

sql=“INSERTINTOmyTable(myId,myName,myAddress,myValues)”+_

“VALUES(1001,_'【孟宪会之精彩世界】之一','xml.sz.luohuedu.net/',100)”

cmd=NewSqlCommand(sql,conn)

cmd.ExecuteNonQuery()

sql=“INSERTINTOmyTable(myId,myName,myAddress,myValues)”+_

“VALUES(1002,'【孟宪会之精彩世界】之二','www.erp800.com/net_lover/',99)”

篇5：创建动态MSSQL数据库表

以下是引用片段：

复制代码代码如下:

ImportsSystem.Data

ImportsSystem.Data.SqlClient

PublicClassForm1

InheritsSystem.Windows.Forms.Form

PrivateConnectionStringAsString=“DataSource=.;InitialCatalog=;UserId=sa;Password=;”

PrivatereaderAsSqlDataReader=Nothing

PrivateconnAsSqlConnection=Nothing

PrivatecmdAsSqlCommand=Nothing

PrivateAlterTableBtnAsSystem.Windows.Forms.Button

PrivatesqlAsString=Nothing

PrivateCreateOthersBtnAsSystem.Windows.Forms.Button

#Region“Windows窗体设计器生成的代码”

'窗体重写处置以清理组件列表，

ProtectedOverloadsOverridesSubDispose(ByValdisposingAsBoolean)

IfdisposingThen

IfNot(componentsIsNothing)Then

components.Dispose()

EndIf

MyBase.Dispose(disposing)

EndSub

PublicSubNew()

MyBase.New()

InitializeComponent()

EndSub

PrivatecomponentsAsSystem.ComponentModel.IContainer

FriendWithEventsDataGrid1AsSystem.Windows.Forms.DataGrid

FriendWithEventsCreateDBBtnAsSystem.Windows.Forms.Button

FriendWithEventsCreateTableBtnAsSystem.Windows.Forms.Button

FriendWithEventsCreateSPBtnAsSystem.Windows.Forms.Button

FriendWithEventsCreateViewBtnAsSystem.Windows.Forms.Button

FriendWithEventsbtnAlterTableAsSystem.Windows.Forms.Button

FriendWithEventsbtnCreateOthersAsSystem.Windows.Forms.Button

FriendWithEventsbtnDropTableAsSystem.Windows.Forms.Button

FriendWithEventsbtnViewDataAsSystem.Windows.Forms.Button

FriendWithEventsbtnViewSPAsSystem.Windows.Forms.Button

FriendWithEventsbtnViewViewAsSystem.Windows.Forms.Button

PrivateSubInitializeComponent()

复制代码代码如下:

Me.CreateDBBtn=NewSystem.Windows.Forms.Button()

Me.CreateTableBtn=NewSystem.Windows.Forms.Button()

Me.CreateSPBtn=NewSystem.Windows.Forms.Button()

Me.CreateViewBtn=NewSystem.Windows.Forms.Button()

Me.btnAlterTable=NewSystem.Windows.Forms.Button()

Me.btnCreateOthers=NewSystem.Windows.Forms.Button()

Me.btnDropTable=NewSystem.Windows.Forms.Button()

Me.btnViewData=NewSystem.Windows.Forms.Button()

Me.btnViewSP=NewSystem.Windows.Forms.Button()

Me.btnViewView=NewSystem.Windows.Forms.Button()

Me.DataGrid1=NewSystem.Windows.Forms.DataGrid()

CType(Me.DataGrid1,System.ComponentModel.ISupportInitialize).BeginInit()

Me.SuspendLayout()

'CreateDBBtn

Me.CreateDBBtn.Location=NewSystem.Drawing.Point(19,9)

Me.CreateDBBtn.Name=“CreateDBBtn”

Me.CreateDBBtn.Size=NewSystem.Drawing.Size(104,23)

Me.CreateDBBtn.TabIndex=0

Me.CreateDBBtn.Text=“创建数据库”

'CreateTableBtn

Me.CreateTableBtn.Location=NewSystem.Drawing.Point(139,9)

Me.CreateTableBtn.Name=“CreateTableBtn”

Me.CreateTableBtn.TabIndex=1

Me.CreateTableBtn.Text=“创建表”

'CreateSPBtn

Me.CreateSPBtn.Location=NewSystem.Drawing.Point(230,9)

Me.CreateSPBtn.Name=“CreateSPBtn”

Me.CreateSPBtn.Size=NewSystem.Drawing.Size(104,23)

Me.CreateSPBtn.TabIndex=2

Me.CreateSPBtn.Text=“创建存储过程”

'CreateViewBtn

Me.CreateViewBtn.Location=NewSystem.Drawing.Point(350,9)

Me.CreateViewBtn.Name=“CreateViewBtn”

Me.CreateViewBtn.TabIndex=3中国网管联盟www.bitscn.com

Me.CreateViewBtn.Text=“创建视图”

'btnAlterTable

Me.btnAlterTable.Location=NewSystem.Drawing.Point(441,9)

Me.btnAlterTable.Name=“btnAlterTable”

Me.btnAlterTable.TabIndex=4

Me.btnAlterTable.Text=“修改表”

篇6：动态创建MSSQL数据库表存储过程存储过程

下面是利用SQL语句创建数据库、表、存储过程、视图、索引、规则、修改表、查看数据等的方法，所要增加的控件如下：

以下是引用片段：

ImportsSystem.Data

ImportsSystem.Data.SqlClient

PublicClassForm1

InheritsSystem.Windows.Forms.Form

PrivateConnectionStringAsString=“DataSource=.;InitialCatalog=;UserId=sa;Password=;”

PrivatereaderAsSqlDataReader=Nothing

PrivateconnAsSqlConnection=Nothing

PrivatecmdAsSqlCommand=Nothing

PrivateAlterTableBtnAsSystem.Windows.Forms.Button

PrivatesqlAsString=Nothing

PrivateCreateOthersBtnAsSystem.Windows.Forms.Button

#Region“Windows窗体设计器生成的代码”

'窗体重写处置以清理组件列表。

ProtectedOverloadsOverridesSubDispose(ByValdisposingAsBoolean)

IfdisposingThen

IfNot(componentsIsNothing)Then

components.Dispose

EndIf

MyBase.Dispose(disposing)

EndSub

PublicSubNew()

MyBase.New()

InitializeComponent()

EndSub

PrivatecomponentsAsSystem.ComponentModel.IContainer

FriendWithEventsDataGrid1AsSystem.Windows.Forms.DataGrid

FriendWithEventsCreateDBBtnAsSystem.Windows.Forms.Button

FriendWithEventsCreateTableBtnAsSystem.Windows.Forms.Button

FriendWithEventsCreateSPBtnAsSystem.Windows.Forms.Button

FriendWithEventsCreateViewBtnAsSystem.Windows.Forms.Button

FriendWithEventsbtnAlterTableAsSystem.Windows.Forms.Button

FriendWithEventsbtnCreateOthersAsSystem.Windows.Forms.Button

FriendWithEventsbtnDropTableAsSystem.Windows.Forms.Button

FriendWithEventsbtnViewDataAsSystem.Windows.Forms.Button

FriendWithEventsbtnViewSPAsSystem.Windows.Forms.Button

FriendWithEventsbtnViewViewAsSystem.Windows.Forms.Button

PrivateSubInitializeComponent()

Me.CreateDBBtn=NewSystem.Windows.Forms.Button()

Me.CreateTableBtn=NewSystem.Windows.Forms.Button()

Me.CreateSPBtn=NewSystem.Windows.Forms.Button()

Me.Cr

关键字：MYSQL

篇7：动态创建MSSQL数据库表存储过程

下面是利用SQL语句创建数据库、表、存储过程、视图、索引、规则、修改表、查看数据等的方法，所要增加的控件如下：

以下是引用片段：

Imports System.Data

Imports System.Data.SqlClient

Public Class Form1

Inherits System.Windows.Forms.Form.

Private ConnectionString As String = “Data Source=.;Initial Catalog=;User Id=sa;Password=;”

Private reader As SqlDataReader = Nothing

Private conn As SqlConnection = Nothing

Private cmd As SqlCommand = Nothing

Private AlterTableBtn As System.Windows.Forms.Button

Private sql As String = Nothing

Private CreateOthersBtn As System.Windows.Forms.Button

#Region “ Windows 窗体设计器生成的代码 ”

'窗体重写处置以清理组件列表。

Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)

If disposing Then

If Not (components Is Nothing) Then

components.Dispose()

End If

MyBase.Dispose(disposing)

End Sub

Public Sub New()

MyBase.New()

InitializeComponent()

End Sub

Private components As System.ComponentModel.IContainer

Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid

Friend WithEvents CreateDBBtn As System.Windows.Forms.Button

Friend WithEvents CreateTableBtn As System.Windows.Forms.Button

Friend WithEvents CreateSPBtn As System.Windows.Forms.Button

Friend WithEvents CreateViewBtn As System.Windows.Forms.Button

Friend WithEvents btnAlterTable As System.Windows.Forms.Button

Friend WithEvents btnCreateOthers As System.Windows.Forms.Button

Friend WithEvents btnDropTable As System.Windows.Forms.Button

Friend WithEvents btnViewData As System.Windows.Forms.Button

Friend WithEvents btnViewSP As System.Windows.Forms.Button

Friend WithEvents btnViewView As System.Windows.Forms.Button

Private Sub InitializeComponent()

Me.CreateDBBtn = New System.Windows.Forms.Button()

Me.CreateTableBtn = New System.Windows.Forms.Button()

Me.CreateSPBtn = New System.Windows.Forms.Button()

Me.CreateViewBtn = New System.Windows.Forms.Button()

Me.btnAlterTable = New System.Windows.Forms.Button()

Me.btnCreateOthers = New System.Windows.Forms.Button()

Me.btnDropTable = New System.Windows.Forms.Button()

Me.btnViewData = New System.Windows.Forms.Button()

Me.btnViewSP = New System.Windows.Forms.Button()

Me.btnViewView = New System.Windows.Forms.Button()

Me.DataGrid1 = New System.Windows.Forms.DataGrid()

CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()

Me.SuspendLayout()

'CreateDBBtn

Me.CreateDBBtn.Location = New System.Drawing.Point(19, 9)

Me.CreateDBBtn.Name = “CreateDBBtn”

Me.CreateDBBtn.Size = New System.Drawing.Size(104, 23)

Me.CreateDBBtn.TabIndex = 0

Me.CreateDBBtn.Text = “创建数据库”

'CreateTableBtn

Me.CreateTableBtn.Location = New System.Drawing.Point(139, 9)

Me.CreateTableBtn.Name = “CreateTableBtn”

Me.CreateTableBtn.TabIndex = 1

Me.CreateTableBtn.Text = “创建表”

'CreateSPBtn

Me.CreateSPBtn.Location = New System.Drawing.Point(230, 9)

Me.CreateSPBtn.Name = “CreateSPBtn”

Me.CreateSPBtn.Size = New System.Drawing.Size(104, 23)

Me.CreateSPBtn.TabIndex = 2

Me.CreateSPBtn.Text = “创建存储过程”

'CreateViewBtn

Me.CreateViewBtn.Location = New System.Drawing.Point(350, 9)

Me.CreateViewBtn.Name = “CreateViewBtn”

Me.CreateViewBtn.TabIndex = 3

Me.CreateViewBtn.Text = “创建视图”

'btnAlterTable

Me.btnAlterTable.Location = New System.Drawing.Point(441, 9)

Me.btnAlterTable.Name = “btnAlterTable”

Me.btnAlterTable.TabIndex = 4

Me.btnAlterTable.Text = “修改表”

'btnCreateOthers

Me.btnCreateOthers.Location = New System.Drawing.Point(17, 43)

Me.btnCreateOthers.Name = “btnCreateOthers”

Me.btnCreateOthers.Size = New System.Drawing.Size(104, 23)

Me.btnCreateOthers.TabIndex = 5

Me.btnCreateOthers.Text = “创建规则和索引”

'btnDropTable

Me.btnDropTable.Location = New System.Drawing.Point(138, 43)

Me.btnDropTable.Name = “btnDropTable”

Me.btnDropTable.TabIndex = 6

Me.btnDropTable.Text = “删除表”

'btnViewData

Me.btnViewData.Location = New System.Drawing.Point(351, 43)

Me.btnViewData.Name = “btnViewData”

Me.btnViewData.TabIndex = 7

Me.btnViewData.Text = “查看数据”

'btnViewSP

Me.btnViewSP.Location = New System.Drawing.Point(230, 43)

Me.btnViewSP.Name = “btnViewSP”

Me.btnViewSP.Size = New System.Drawing.Size(104, 23)

Me.btnViewSP.TabIndex = 8

Me.btnViewSP.Text = “查看存储过程”

'btnViewView

Me.btnViewView.Location = New System.Drawing.Point(443, 43)

Me.btnViewView.Name = “btnViewView”

Me.btnViewView.TabIndex = 9

Me.btnViewView.Text = “查看视图”

'DataGrid1

Me.DataGrid1.DataMember = “”

Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText

Me.DataGrid1.Location = New System.Drawing.Point(20, 76)

Me.DataGrid1.Name = “DataGrid1”

Me.DataGrid1.Size = New System.Drawing.Size(500, 183)

Me.DataGrid1.TabIndex = 10

'Form1

Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)

Me.ClientSize = New System.Drawing.Size(538, 281)

Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.DataGrid1, Me.btnViewView, _

Me.btnViewSP, Me.btnViewData, Me.btnDropTable, Me.btnCreateOthers, Me.btnAlterTable, _

Me.CreateViewBtn, Me.CreateSPBtn, Me.CreateTableBtn, Me.CreateDBBtn})

Me.Name = “Form1”

Me.Text = “动态创建SQL Server数据库、表、存储过程等架构信息”

CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()

Me.ResumeLayout(False)

End Sub

#End Region

' 创建数据库

Private Sub CreateDBBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles CreateDBBtn.Click

conn = New SqlConnection(ConnectionString)

' 打开连接

If conn.State ConnectionState.Open Then

conn.Open()

End If

'MyDataBase为数据库名称

Dim sql As String = “CREATE DATABASE MyDataBase ON PRIMARY (Name=MyDataBase_data, filename = ” + _

“'D:\MyDataBase.mdf', size=3,” + “maxsize=5, filegrowth=10%) log on” + “(name=MyDataBase_log, ” + _

“filename='D:\MyDataBase.ldf',size=3,” + “maxsize=20,filegrowth=1)”

cmd = New SqlCommand(sql, conn)

Try

cmd.ExecuteNonQuery()

Catch ae As SqlException

MessageBox.Show(ae.Message.ToString())

End Try

End Sub

'创建表

Private Sub CreateTableBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles CreateTableBtn.Click

conn = New SqlConnection(ConnectionString)

' 打开连接

If conn.State = ConnectionState.Open Then

conn.Close()

End If

ConnectionString = “Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;”

conn.ConnectionString = ConnectionString

conn.Open()

sql = “CREATE TABLE myTable” + “(myId INTEGER CONSTRAINT PKeyMyId PRIMARY KEY,” + _

“myName CHAR(50) NOT Null, myAddress CHAR(255), myValues FLOAT)”

cmd = New SqlCommand(sql, conn)

Try

cmd.ExecuteNonQuery()

' 添加纪录

sql = “INSERT INTO myTable(myId, myName, myAddress, myValues) ” + _

“VALUES (1001, _'【孟宪会之精彩世界】之一', 'xml.sz.luohuedu.net/', 100 ) ”

cmd = New SqlCommand(sql, conn)

cmd.ExecuteNonQuery()

sql = “INSERT INTO myTable(myId, myName, myAddress, myValues) ” + _

“VALUES (1002, '【孟宪会之精彩世界】之二', 'www.erp800.com/net_lover/', 99) ”

cmd = New SqlCommand(sql, conn)

cmd.ExecuteNonQuery()

sql = “INSERT INTO myTable(myId, myName, myAddress, myValues) ” + _

“VALUES (1003, '【孟宪会之精彩世界】之三', 'xml.sz.luohuedu.net/', 99) ”

cmd = New SqlCommand(sql, conn)

cmd.ExecuteNonQuery()

sql = “INSERT INTO myTable(myId, myName, myAddress, myValues) ” + _

“VALUES (1004, '【孟宪会之精彩世界】之四', 'www.erp800.com/net_lover/', 100) ”

cmd = New SqlCommand(sql, conn)

cmd.ExecuteNonQuery()

Catch ae As SqlException

MessageBox.Show(ae.Message.ToString())

End Try

End Sub

'创建存储过程

Private Sub CreateSPBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles CreateSPBtn.Click

sql = “CREATE PROCEDURE myProc AS” + “ SELECT myName, myAddress FROM myTable GO”

ExecuteSQLStmt(sql)

End Sub

'创建视图

Private Sub CreateViewBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles CreateViewBtn.Click

sql = “CREATE VIEW myView AS SELECT myName FROM myTable”

ExecuteSQLStmt(sql)

End Sub

'修改表

Private Sub btnAlterTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles btnAlterTable.Click

sql = “ALTER TABLE MyTable ADD newCol datetime NOT NULL DEFAULT (getdate())”

ExecuteSQLStmt(sql)

End Sub

'创建规则和索引

Private Sub btnCreateOthers_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles btnCreateOthers.Click

sql = “CREATE UNIQUE INDEX ” + “myIdx ON myTable(myName)”

ExecuteSQLStmt(sql)

sql = “CREATE RULE myRule ” + “AS @myValues >= 90 AND @myValues < 9999”

ExecuteSQLStmt(sql)

End Sub

'删除表

Private Sub btnDropTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles btnDropTable.Click

Dim sql As String = “DROP TABLE MyTable”

ExecuteSQLStmt(sql)

End Sub

'浏览表数据

Private Sub btnViewData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles btnViewData.Click

conn = New SqlConnection(ConnectionString)

If conn.State = ConnectionState.Open Then

conn.Close()

End If

ConnectionString = “Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;”

conn.ConnectionString = ConnectionString

conn.Open()

Dim da As New SqlDataAdapter(“SELECT * FROM myTable”, conn)

Dim ds As New DataSet(“myTable”)

da.Fill(ds, “myTable”)

DataGrid1.DataSource = ds.Tables(“myTable”).DefaultView

End Sub

'浏览存储过程

Private Sub btnViewSP_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles btnViewSP.Click

conn = New SqlConnection(ConnectionString)

If conn.State = ConnectionState.Open Then

conn.Close()

End If

ConnectionString = “Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;”

conn.ConnectionString = ConnectionString

conn.Open()

Dim da As New SqlDataAdapter(“myProc”, conn)

Dim ds As New DataSet(“SP”)

da.Fill(ds, “SP”)

DataGrid1.DataSource = ds.DefaultViewManager

End Sub

'浏览视图

Private Sub btnViewView_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles btnViewView.Click

conn = New SqlConnection(ConnectionString)

If conn.State = ConnectionState.Open Then

conn.Close()

End If

ConnectionString = “Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;”

conn.ConnectionString = ConnectionString

conn.Open()

Dim da As New SqlDataAdapter(“SELECT * FROM myView”, conn)

Dim ds As New DataSet()

da.Fill(ds)

DataGrid1.DataSource = ds.DefaultViewManager

End Sub

Private Sub ExecuteSQLStmt(ByVal sql As String)

conn = New SqlConnection(ConnectionString)

' 打开连接

If conn.State = ConnectionState.Open Then

conn.Close()

End If

ConnectionString = “Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;”

conn.ConnectionString = ConnectionString

conn.Open()

cmd = New SqlCommand(sql, conn)

Try

cmd.ExecuteNonQuery()

Catch ae As SqlException

MessageBox.Show(ae.Message.ToString())

End Try

End Sub

End Class

篇8：怎样掌握MySQL数据库中动态表特征

如果一个MyISAM表包含任何可变长度列（VARCHAR, BLOB或TEXTDynamic），或者如果一个表被用ROW_FORMAT=DYNAMIC选项来创建，动态存储格式被使用，

这个格式更为复杂一点，因为每行有一个表明行有多长的头。当一个记录因为更新的结果被变得更长，该记录也可以在超过一个位置处结束。

你可以使用OPTIMIZE TABLE或myisamchk来对一个表整理碎片。如果在一个表中有你频繁访问或改变的固定长度列，表中也有一些可变长度列，仅为避免碎片而把这些可变长度列移到其它表可能是一个好主意。

动态格式表的一般特征：

· 除了长度少于4的列外，所有的字符串列是动态的。

· 在每个记录前面是一个位图，该位图表明哪一列包含空字符串（对于字符串列）或者0（对于数字列）。注意，这并不包括包含NULL值的列。如果一个字符列在拖曳空间移除后长度为零，或者一个数字列为零值，这都在位图中标注了且列不被保存到磁盘。非空字符串被存为一个长度字节加字符串的内容。

· 通常比固定长度表需要更少的磁盘空间。

· 每个记录仅使用必需大小的空间，

尽管如此，如果一个记录变大，它就按需要被分开成多片，造成记录碎片的后果。比如，你用扩展行长度的信息更新一行，该行就变得有碎片。在这种情况下，你可以时不时运行OPTIMIZE TABLE或myisamchk -r来改善性能。可使用myisamchk -ei来获取表的统计数据。

· 动态格式表在崩溃后要比静态格式表更难重建，因为一个记录可能被分为多个碎片且链接（碎片）可能被丢失。

· 动态尺寸记录期望的行长度用下列表达式来计算：

·3

·+ (number of columns + 7) / 8

·+ (number of char columns)

·+ (packed size of numeric columns)

·+ (length of strings)

·+ (number of NULL columns + 7) / 8

对每个链接需要额外的6字节。在一个更新导致一个记录的扩大之时，一个动态记录被链接了。每个新链接至少是20字节，所以下一个扩大可能在同样的链接里进行。如果不是，则另一个链接将被建立。你可以使用myisamchk -ed来找出链接的数目。所有的链接可以用myisamchk -r来移除。

篇9：动态创建SQL Server数据库、表、存储过程数据库教程

server|创建|存储过程|动态|数据|数据库

下面是利用SQL语句创建数据库、表、存储过程、视图、索引、规则、修改表、查看数据等的方法，所要增加的控件如下：

Imports System.Data

Imports System.Data.SqlClient

Public Class Form1

Inherits System.Windows.Forms.Form