MySQL----MySQL中数据表的常用操作命令

【原文链接】

1、数据表的常见操作命令

• 查看当前数据库中所有数据表

show tables

如：

mysql> use blog_redrose2100;
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_blog_redrose2100 |
+----------------------------+
| articles                   |
| comment                    |
| credit                     |
| favorite                   |
| users                      |
+----------------------------+
5 rows in set (0.00 sec)

mysql>

• 创建数据表

create table 数据表名字 (字段 类型 约束，字段2 类型2 约束2, ...)

如下为创建一个简单的表

mysql> create table demo(id int,name varchar(30));
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+----------------------------+
| Tables_in_blog_redrose2100 |
+----------------------------+
| articles                   |
| comment                    |
| credit                     |
| demo                       |
| favorite                   |
| users                      |
+----------------------------+
6 rows in set (0.00 sec)

mysql>

如下为创建一个带约束的表

mysql> create table demo2(id int primary key auto_increment not null, name varchar(30) not null);
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+----------------------------+
| Tables_in_blog_redrose2100 |
+----------------------------+
| articles                   |
| comment                    |
| credit                     |
| demo                       |
| demo2                      |
| favorite                   |
| users                      |
+----------------------------+
7 rows in set (0.00 sec)

mysql>

• 查看数据表的结构

desc 数据表名;

如：

mysql> desc demo;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

• 查看创建数据表的语句

show create table 数据表名;

如：

mysql> show create table demo2;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                            |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| demo2 | CREATE TABLE `demo2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

• 为表增加一个字段

alter table  表名  add 列名 类型;

如，给demo表增加一个密码的列

mysql> alter table demo add password varchar(16);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc demo;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| name     | varchar(30) | YES  |     | NULL    |       |
| password | varchar(16) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

• 修改已有的列

alter table 表名 modify 列名 类型及约束;

如给demo表中的password增加非空约束，并设置默认值admin123

mysql> alter table demo modify password varchar(16) not null default 'admin123';
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc demo;
+----------+-------------+------+-----+----------+-------+
| Field    | Type        | Null | Key | Default  | Extra |
+----------+-------------+------+-----+----------+-------+
| id       | int(11)     | YES  |     | NULL     |       |
| name     | varchar(30) | YES  |     | NULL     |       |
| password | varchar(16) | NO   |     | admin123 |       |
+----------+-------------+------+-----+----------+-------+
3 rows in set (0.00 sec)

mysql>

• 删除已有的列

alter table 数据表名 drop 列名

如删除demo数据表中的password列

mysql> alter table demo drop password;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc demo;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

• 删除数据表

drop table  数据表名

如删除demo2数据表

mysql> drop table demo2;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+----------------------------+
| Tables_in_blog_redrose2100 |
+----------------------------+
| articles                   |
| comment                    |
| credit                     |
| demo                       |
| favorite                   |
| users                      |
+----------------------------+
6 rows in set (0.00 sec)

mysql>