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

#テーブル作成


```sql

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


```sql

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


```sql
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);
```


```sql
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');
```


```txt
+-----------+--------------+------+-----+---------+----------------+
| 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       |                |
+-----------+--------------+------+-----+---------+----------------
```


```sql
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');
```


```txt
+-----------+--------------+------+-----+---------+----------------+
| 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       |                |
+-----------+--------------+------+-----+---------+----------------+
```

#テーブル削除


```sql
DROP TABLE friend;
```

#テーブル改造
###カラムの追加


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

###カラムの削除


```sql
ALTER TABLE  `friend` DROP `delflag`;
```
###カラムの定義を変更


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

#テーブル情報確認


```sql
DESC friend;
```


```txt
+------------+--------------+------+-----+---------+----------------+
| 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       |                |
+------------+--------------+------+-----+---------+----------------+
```