【原文链接】
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>