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)
