mysql基础语法 表

#if语句，防止报错

mysql> drop database if exists `HA-test`;

Query OK, 0 rows affected, 1 warning (0.00 sec)

#创建表

mysql> use book

Database changed

mysql> create table student(id int(20),name char(40),age int);

Query OK, 0 rows affected (0.06 sec)

#查看表

mysql> show tables;

+----------------+

| Tables_in_book |

+----------------+

| student |

+----------------+

1 row in set (0.00 sec)

#查看表结构

mysql> desc student;

+-------+----------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| id | int(20) | YES | | NULL | |

| name | char(40) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

+-------+----------+------+-----+---------+-------+

#查看创建的表执行的语句

mysql> show create table student\G

*************************** 1. row ***************************

Table: student

Create Table: CREATE TABLE `student` (

`id` int(20) DEFAULT NULL,

`name` char(40) DEFAULT NULL,

`age` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

#改表名

mysql> alter table student rename id;

Query OK, 0 rows affected (0.01 sec)

mysql> show tables;

+----------------+

| Tables_in_book |

+----------------+

| id |

+----------------+

1 row in set (0.00 sec)

#修改表中的字段类型modify

mysql> desc id;

+-------+----------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| id | int(20) | YES | | NULL | |

| name | char(40) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

+-------+----------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> alter table id modify id int(40);

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc id;

+-------+----------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| id | int(40) | YES | | NULL | |

| name | char(40) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

+-------+----------+------+-----+---------+-------+

3 rows in set (0.00 sec)

#修改表中的字段名和类型

mysql> alter table id change name names char(20);

Query OK, 0 rows affected (0.11 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc id;

+-------+----------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| id | int(40) | YES | | NULL | |

| names | char(20) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

+-------+----------+------+-----+---------+-------+

3 rows in set (0.00 sec)

注：CHANGE 和MODIFY的区别：

CHANGE 对列进行重命名和更改列的类型，需给定旧的列名称和新的列名称、当前的类型。 MODIFY 可以改变列的类型，此时不需要重命名（不需给定新的列名称）

#添加类型

语法：alter table 表名 add 字段名 字段类型；

mysql> alter table student add sex enum('M','W');

Query OK, 0 rows affected (0.08 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;

+-------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------------+------+-----+---------+-------+

| id | int(40) | YES | | NULL | |

| names | char(20) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

| sex | enum('M','W') | YES | | NULL | |

+-------+---------------+------+-----+---------+-------+

#字段添加到第一位

mysql> alter table student add uid int(10) first;

Query OK, 0 rows affected (0.17 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;

+-------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------------+------+-----+---------+-------+

| uid | int(10) | YES | | NULL | |

| id | int(40) | YES | | NULL | |

| names | char(20) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

| sex | enum('M','W') | YES | | NULL | |

+-------+---------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

#在一个字段后面添加

mysql> alter table student add address char(40) after age;

Query OK, 0 rows affected (0.56 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;

+---------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+---------------+------+-----+---------+-------+

| uid | int(10) | YES | | NULL | |

| id | int(40) | YES | | NULL | |

| names | char(20) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

| address | char(40) | YES | | NULL | |

| sex | enum('M','W') | YES | | NULL | |

+---------+---------------+------+-----+---------+-------+

6 rows in set (0.00 sec)

#删除字段

mysql> alter table student drop id;

Query OK, 0 rows affected (0.14 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;

+---------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+---------------+------+-----+---------+-------+

| uid | int(10) | YES | | NULL | |

| names | char(20) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

| address | char(40) | YES | | NULL | |

| sex | enum('M','W') | YES | | NULL | |

+---------+---------------+------+-----+---------+-------+

5 rows in set (0.00 sec)