MYSQL教程之静态创立SQL Server数据库、表、存储过...

DBaaS系统本身并不提供面对面访问或个人客户关系或持续不断的支持MySQL学习教程。这些就是需要解决方案提供商的原因。他们帮助客户选择正确的解决方案、规划集成和迁移战略，然后协助实施。server|创立|存储历程|静态|数据|数据库上面是使用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
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
<System.Diagnostics.DebuggerStepThrough()>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
Me.CreateViewBtn.Text="创立视图"

btnAlterTable

Me.btnAlterTable.Location=NewSystem.Drawing.Point(441,9)
Me.btnAlterTable.Name="btnAlterTable"
Me.btnAlterTable.TabIndex=4
Me.btnAlterTable.Text="修正表"

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="创立划定规矩和索引"

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)
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)
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="+_
"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()
增加记录
sql="INSERTINTOmyTable(myId,myName,myAddress,myValues)"+_
"VALUES(1001,_【孟宪会之出色天下】之一,http://xml.sz.luohuedu.net/,100)"
cmd=NewSqlCommand(sql,conn)
cmd.ExecuteNonQuery()
sql="INSERTINTOmyTable(myId,myName,myAddress,myValues)"+_
"VALUES(1002,【孟宪会之出色天下】之二,http://www.erp800.com/net_lover/,99)"
cmd=NewSqlCommand(sql,conn)
cmd.ExecuteNonQuery()
sql="INSERTINTOmyTable(myId,myName,myAddress,myValues)"+_
"VALUES(1003,【孟宪会之出色天下】之三,http://xml.sz.luohuedu.net/,99)"
cmd=NewSqlCommand(sql,conn)
cmd.ExecuteNonQuery()
sql="INSERTINTOmyTable(myId,myName,myAddress,myValues)"+_
"VALUES(1004,【孟宪会之出色天下】之四,http://www.erp800.com/net_lover/,100)"
cmd=NewSqlCommand(sql,conn)
cmd.ExecuteNonQuery()
CatchaeAsSqlException
MessageBox.Show(ae.Message.ToString())
EndTry

EndSub
创立存储历程
PrivateSubCreateSPBtn_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)_
HandlesCreateSPBtn.Click
sql="CREATEPROCEDUREmyProcAS"+"SELECTmyName,myAddressFROMmyTableGO"
ExecuteSQLStmt(sql)
EndSub
创立视图
PrivateSubCreateViewBtn_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)_
HandlesCreateViewBtn.Click
sql="CREATEVIEWmyViewASSELECTmyNameFROMmyTable"
ExecuteSQLStmt(sql)

EndSub
修正表
PrivateSubbtnAlterTable_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)_
HandlesbtnAlterTable.Click
sql="ALTERTABLEMyTableADDnewColdatetimeNOTNULLDEFAULT(getdate())"
ExecuteSQLStmt(sql)
EndSub
创立划定规矩和索引
PrivateSubbtnCreateOthers_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)_
HandlesbtnCreateOthers.Click
sql="CREATEUNIQUEINDEX"+"myIdxONmyTable(myName)"
ExecuteSQLStmt(sql)

sql="CREATERULEmyRule"+"AS@myValues>=90AND@myValues<9999"
ExecuteSQLStmt(sql)
EndSub

删除表
PrivateSubbtnDropTable_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)_
HandlesbtnDropTable.Click
DimsqlAsString="DROPTABLEMyTable"
ExecuteSQLStmt(sql)
EndSub
扫瞄表数据
PrivateSubbtnViewData_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)_
HandlesbtnViewData.Click
conn=NewSqlConnection(ConnectionString)
Ifconn.State=ConnectionState.OpenThen
conn.Close()
EndIf
ConnectionString="DataSource=.;InitialCatalog=MyDataBase;UserId=sa;Password=;"
conn.ConnectionString=ConnectionString
conn.Open()
DimdaAsNewSqlDataAdapter("SELECT*FROMmyTable",conn)
DimdsAsNewDataSet("myTable")
da.Fill(ds,"myTable")
DataGrid1.DataSource=ds.Tables("myTable").DefaultView
EndSub
扫瞄存储历程
PrivateSubbtnViewSP_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)_
HandlesbtnViewSP.Click
conn=NewSqlConnection(ConnectionString)
Ifconn.State=ConnectionState.OpenThen
conn.Close()
EndIf
ConnectionString="DataSource=.;InitialCatalog=MyDataBase;UserId=sa;Password=;"
conn.ConnectionString=ConnectionString
conn.Open()
DimdaAsNewSqlDataAdapter("myProc",conn)
DimdsAsNewDataSet("SP")
da.Fill(ds,"SP")
DataGrid1.DataSource=ds.DefaultViewManager
EndSub
扫瞄视图
PrivateSubbtnViewView_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)_
HandlesbtnViewView.Click
conn=NewSqlConnection(ConnectionString)
Ifconn.State=ConnectionState.OpenThen
conn.Close()
EndIf
ConnectionString="DataSource=.;InitialCatalog=MyDataBase;UserId=sa;Password=;"
conn.ConnectionString=ConnectionString
conn.Open()
DimdaAsNewSqlDataAdapter("SELECT*FROMmyView",conn)
DimdsAsNewDataSet()
da.Fill(ds)
DataGrid1.DataSource=ds.DefaultViewManager
EndSub

PrivateSubExecuteSQLStmt(ByValsqlAsString)
conn=NewSqlConnection(ConnectionString)
翻开毗连
Ifconn.State=ConnectionState.OpenThen
conn.Close()
EndIf
ConnectionString="DataSource=.;InitialCatalog=MyDataBase;UserId=sa;Password=;"
conn.ConnectionString=ConnectionString
conn.Open()
cmd=NewSqlCommand(sql,conn)
Try
cmd.ExecuteNonQuery()
CatchaeAsSqlException
MessageBox.Show(ae.Message.ToString())
EndTry
EndSub
EndClass

”由于MySQL已经是一个运行了众多知名Web2.0网站的数据，包括Craigslist、Digg、Wikipedia和Google等，或许我们可以说每一个Web2.0公司实质上是一个使用MySQL数据库的公司。

这就引发了对varchar和char效率讨论的老问题。到底如何分配varchar的数据，是否会出现大规模的碎片？

这是一个不错的新特性。虽然索引的附加字段没有索引键值效率高，但是相对映射到数据表中效率还是提高了很多。我做过试验，在我的实验环境中会比映射到表中提高30%左右的效率。

如果我们从集合论（关系代数）的角度来看，一张数据库的表就是一组数据元的关系，而每个SQL语句会改变一种或数种关系，从而产生出新的数据元的关系（即产生新的表）。

微软对CLR作了大篇幅的宣传，这是因为数据库产品终于融入.net体系中。最开始我们也是狂喜，感觉对象数据库的一些概念可以实现了。

对一张百万级别的表建游标，同时又没有什么过滤条件，取得游标效率是如果直接SQL查询百万条数据；如果再对每条记录做处理，耗时将更长。

多加的系统视图和实时系统信息这些东西对DBA挑优非常有帮助，但是感觉粒度还是不太细。

财务软件要用SQL也只是后台的数据库而已,软件都是成品的,当然多学东西肯定是有好处的..

可以动态传入参数，省却了动态SQL的拼写。