数据类型描述一个日期和时间值(06-08-1981 04:25:08),在MySQL中Date数据类型仅仅描述从'1000-01-01'到'9999-12-31'之间的日期。
gb.db的能力之一是“类型独立”,这意味着如果用gb.db创建带有一些表的数据库,组件将能在被gb.db支持的任意数据库管理器(例如ODBC、sqlite、postgresql等等)中来创建它。这是很棒的,但是如果必须在MySQL中创建带有特定数据类型的特殊数据库,那应该使用gb.mysql组件。
Public Procedure CreateDatabase()
Dim $hConnection As New Connection
With $hConnection
.Type = "mysql"
.Port = "3306"
.Host = "localhost"
.User = "root"
.Password = "mypass"
.Name = "Gambas"
.Open()
End With
$hConnection.MySQL.Charset = "utf8" '设置字符集,默认: utf8
$hConnection.MySQL.Collation = "utf8_general_ci" '设置定序,默认: utf8_general_ci
$hConnection.MySQL.Engine = "InnoDB" '设置引擎,默认: InnoDB
$hConnection.MySQL.DataBase.Delete(Gambas, True) '删除数据库
$hConnection.MySQL.DataBase.Add(Gambas, $hConnection.MySQL.Charset, $hConnection.MySQL.Collation) 'Adds a database
$hConnection.MySQL.DataBase.Modify(Gambas, $hConnection.MySQL.Charset, $hConnection.MySQL.Collation)
'显示数据库信息
Message(("Information for: ") & $hConnection.Name & GB.NewLine & $hConnection.MySQL.DataBase.Info(txtDatabase.Text))
'允许创建一些表
'表actor
$hConnection.MySQL.Field.Add("actor_id", $hConnection.MySQL.DataTypes.UnsignedSmallInt, False,, True)
$hConnection.MySQL.Field.Add("first_name", $hConnection.MySQL.DataTypes.VarChar(45), False)
$hConnection.MySQL.Field.Add("last_name", $hConnection.MySQL.DataTypes.VarChar(45), False)
$hConnection.MySQL.Field.Add("last_update", $hConnection.MySQL.DataTypes.TimeStamp, False, "CURRENT_TIMESTAMP",, "CURRENT_TIMESTAMP")
$hConnection.MySQL.Field.PrimaryKey(["actor_id"])
$hConnection.MySQL.Field.Index("idx_actor_last_name", "last_name")
$hConnection.MySQL.Table.Add("actor", "InnoDB", $hConnection.MySQL.Charset)
'执行的语句
' Create TABLE `actor` (
' `actor_id` smallint(5) unsigned NOT NULL auto_increment,
' `first_name` varchar(45) NOT NULL,
' `last_name` varchar(45) NOT NULL,
' `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
' PRIMARY KEY(`actor_id`),
' KEY `idx_actor_last_name` (`last_name`)
' )ENGINE = InnoDB Default CHARSET = utf8
'表country
$hConnection.MySQL.Field.Add("country_id", $hConnection.MySQL.DataTypes.UnsignedSmallInt, False,, True)
$hConnection.MySQL.Field.Add("country", $hConnection.MySQL.DataTypes.VarChar(50), False)
$hConnection.MySQL.Field.Add("last_update", $hConnection.MySQL.DataTypes.TimeStamp, False, "CURRENT_TIMESTAMP",, "CURRENT_TIMESTAMP")
$hConnection.MySQL.Field.PrimaryKey(["country_id"])
$hConnection.MySQL.Table.Add("country", "InnoDB", $hConnection.MySQL.Charset)
'执行的语句
' Create TABLE `country` (
' `country_id` smallint(5) unsigned NOT NULL auto_increment,
' `country` varchar(50) NOT NULL,
' `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
' PRIMARY KEY(`country_id`)
' )ENGINE = InnoDB Default CHARSET = utf8
'表city
$hConnection.MySQL.Field.Add("city_id", $hConnection.MySQL.DataTypes.UnsignedSmallInt, False,, True)
$hConnection.MySQL.Field.Add("city", $hConnection.MySQL.DataTypes.VarChar(50), False)
$hConnection.MySQL.Field.Add("country_id", $hConnection.MySQL.DataTypes.UnsignedSmallInt, False)
$hConnection.MySQL.Field.Add("last_update", $hConnection.MySQL.DataTypes.TimeStamp, False, "CURRENT_TIMESTAMP",, "CURRENT_TIMESTAMP")
$hConnection.MySQL.Field.PrimaryKey(["city_id"])
$hConnection.MySQL.Field.Index("idx_fk_country_id", "country_id")
$hConnection.MySQL.Field.ForeignKey("country_id", "country", "country_id", "RESTRICT", "CASCADE")
$hConnection.MySQL.Table.Add("city", "InnoDB", $hConnection.MySQL.Charset)
'执行的语句
' Create TABLE `city` (
' `city_id` smallint(5) unsigned NOT NULL auto_increment,
' `city` varchar(50) NOT NULL,
' `country_id` smallint(5) unsigned NOT NULL,
' `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
' PRIMARY KEY(`city_id`),
' KEY `idx_fk_country_id` (`country_id`),
' CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
' )ENGINE = InnoDB Default CHARSET = utf8
'表address
$hConnection.MySQL.Field.Add("address_id", $hConnection.MySQL.DataTypes.UnsignedSmallInt, False,, True)
$hConnection.MySQL.Field.Add("address", $hConnection.MySQL.DataTypes.VarChar(50), False)
$hConnection.MySQL.Field.Add("address2", $hConnection.MySQL.DataTypes.VarChar(50), True, $hConnection.MySQL.DataTypes.Null)
$hConnection.MySQL.Field.Add("district", $hConnection.MySQL.DataTypes.VarChar(20), False)
$hConnection.MySQL.Field.Add("city_id", $hConnection.MySQL.DataTypes.UnsignedSmallInt, False)
$hConnection.MySQL.Field.Add("postal_code", $hConnection.MySQL.DataTypes.VarChar(10), True, $hConnection.MySQL.DataTypes.Null)
$hConnection.MySQL.Field.Add("phone", $hConnection.MySQL.DataTypes.VarChar(20), False)
$hConnection.MySQL.Field.Add("last_update", $hConnection.MySQL.DataTypes.TimeStamp, False, "CURRENT_TIMESTAMP",, "CURRENT_TIMESTAMP")
$hConnection.MySQL.Field.PrimaryKey(["address_id"])
$hConnection.MySQL.Field.Index("idx_fk_city_id", "city_id")
$hConnection.MySQL.Field.ForeignKey("city_id", "city", "city_id", "RESTRICT", "CASCADE")
$hConnection.MySQL.Table.Add("address", "InnoDB", $hConnection.MySQL.Charset)
'执行的语句
' Create TABLE `address` (
' `address_id` smallint(5) unsigned NOT NULL auto_increment,
' `address` varchar(50) NOT NULL,
' `address2` varchar(50) default 'NULL',
' `district` varchar(20) NOT NULL,
' `city_id` smallint(5) unsigned NOT NULL,
' `postal_code` varchar(10) default 'NULL',
' `phone` varchar(20) NOT NULL,
' `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
' PRIMARY KEY(`address_id`),
' KEY `idx_fk_city_id` (`city_id`),
' CONSTRAINT `address_ibfk_1` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON UPDATE CASCADE
' )ENGINE = InnoDB Default CHARSET = utf8
End