Home | Mirror | Search

2. Table

2.1. create table ... select

創建空表

			
create table admin_user_history select * from admin_user where 1 <> 1;
			
			

創建有數據的表

			
create table admin_user_history select * from admin_user;
			
			

2.2. modifiy table

modifiy table

ALTER TABLE ecs_users add user_picture varchar(255);
			

2.3. TEMPORARY Table

臨時表將在你連接期間存在。一旦斷開時將自動刪除表並釋放所用的空間。你在連接期間刪除該表也同樣釋放空間。

CREATE TEMPORARY TABLE tmp_table (
	key VARCHAR(10) NOT NULL,
	value INTEGER NOT NULL
)
			

聲明臨時表是一個HEAP表,允許你指定在內存中創建它

CREATE TEMPORARY TABLE tmp_mem_table (
	key VARCHAR(10) NOT NULL,
	value INTEGER NOT NULL
) TYPE = HEAP
			

2.4. Collate

ALTER TABLE `tmp_cats`  COLLATE='utf8_general_ci',  CONVERT TO CHARSET utf8;
			

2.5. CHARACTER

			
ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8;		
			
			

2.6. DEFAULT

更新時間

`mtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更改時間',

			
CREATE TABLE `bank_account` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增唯一ID',
	`bank_name` VARCHAR(255) NOT NULL DEFAULT '0' COMMENT '銀行名字(Bank Name)',
	`name` VARCHAR(50) NOT NULL DEFAULT '0' COMMENT '帳號名稱(Name on account)',
	`account_number` VARCHAR(50) NOT NULL DEFAULT '0' COMMENT '銀行帳號(Account Number)',
	`branch_location` VARCHAR(255) NOT NULL DEFAULT '0' COMMENT '支行位置(Branch Location)',
	`description` VARCHAR(255) NOT NULL DEFAULT '0' COMMENT '銀行描述',
	`status` ENUM('Y','N') NOT NULL DEFAULT 'N' COMMENT '銀行帳號狀態',
	`ctime` DATETIME NOT NULL COMMENT '創建時間',
	`mtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更改時間',
	PRIMARY KEY (`id`)
)
COMMENT='銀行帳號'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2;
			
			

2.7. KEY

2.7.1. PRIMARY KEY

一般主鍵定義

PRIMARY KEY (`id`),				
				

復合主鍵

PRIMARY KEY (`id`, `user_id`),				
				

2.8. COMMENT

ALTER TABLE `neo`.`stuff` COMMENT = '用戶表' ;
ALTER TABLE `neo`.`stuff` CHANGE COLUMN `name` `name` VARCHAR(50) NULL DEFAULT NULL COMMENT '姓名'  ;
ALTER TABLE `neo`.`stuff` CHANGE COLUMN `password` `password` VARCHAR(50) NULL DEFAULT NULL COMMENT '用戶密碼' ;


CREATE TABLE `stuff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL COMMENT ''姓名'',
  `password` varchar(50) DEFAULT NULL COMMENT ''用戶密碼'',
  `created` date NOT NULL DEFAULT ''0000-00-00'',
  PRIMARY KEY (`id`,`created`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COMMENT=''用戶表''
/*!50100 PARTITION BY HASH (year(created))
PARTITIONS 10 */
			
comments powered by Disqus