Mysql各コードあれこれ。(テーブル作成・編集・削除・情報確認）

テーブル作成

CREATE TABLE `prefecture`(
`id` INT NOT NULL PRIMARY KEY  AUTO_INCREMENT,
`prefectureName` VARCHAR(128) NOT NULL);

CREATE TABLE `area`(
`id` INT NOT NULL PRIMARY KEY  AUTO_INCREMENT,
`areaName` VARCHAR(128) NOT NULL);

CREATE TABLE `friend`(
`id` INT NOT NULL PRIMARY KEY  AUTO_INCREMENT,
`areaId` INT NOT NULL,
`friendName` VARCHAR(128) NOT NULL,
`age` INT(128) NOT NULL,
`sex` VARCHAR(16) NOT NULL);

CREATE TABLE `comment`( `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `userId` INT NOT NULL, `comment` VARCHAR(128) NOT NULL, `creatDate` DATETIME NOT NULL, `renewDate` DATETIME  NOT NULL, `delFlag` TINYINT( 2 ) NOT NULL DEFAULT '0');

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| userId    | int(11)      | NO   |     | NULL    |                |
| comment   | varchar(128) | NO   |     | NULL    |                |
| creatDate | datetime     | NO   |     | NULL    |                |
| renewDate | datetime     | NO   |     | NULL    |                |
| delFlag   | tinyint(2)   | NO   |     | 0       |                |
+-----------+--------------+------+-----+---------+----------------

CREATE TABLE `userinfo`( `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(128) NOT NULL, `pass` VARCHAR(128) NOT NULL, `creatDate` DATETIME NOT NULL, `renewDate` DATETIME  NOT NULL, `delFlag` TINYINT( 2 ) NOT NULL DEFAULT '0');

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| name      | varchar(128) | NO   |     | NULL    |                |
| pass      | varchar(128) | NO   |     | NULL    |                |
| creatDate | datetime     | NO   |     | NULL    |                |
| renewDate | datetime     | NO   |     | NULL    |                |
| delFlag   | tinyint(2)   | NO   |     | 0       |                |
+-----------+--------------+------+-----+---------+----------------+

テーブル削除

DROP TABLE friend;

テーブル改造

カラムの追加

ALTER TABLE  `friend` ADD  `delFlag` TINYINT( 2 ) NOT NULL DEFAULT  '0';

カラムの削除

ALTER TABLE  `friend` DROP `delflag`;

カラムの定義を変更

ALTER TABLE `friend` MODIFY `delflag` int(6);

テーブル情報確認

DESC friend;

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| areaId     | int(11)      | NO   |     | NULL    |                |
| friendName | varchar(128) | NO   |     | NULL    |                |
| age        | int(128)     | NO   |     | NULL    |                |
| sex        | varchar(16)  | NO   |     | NULL    |                |
| delFlag    | tinyint(2)   | NO   |     | 0       |                |
+------------+--------------+------+-----+---------+----------------+