一创建表
1 创建新表
# 语法:
create table 表名 (字段名 1 类型 [(宽度) 约束条件],
字段名 2 类型 [(宽度) 约束条件],
字段名 3 类型 [(宽度) 约束条件]
);
# 注意:
1. 在同一张表中, 字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的
- mysql> create table auth(
- -> id int(10) primary key auto_increment,
- -> name varchar(10) not null,
- -> age int(3),
- -> birthday datetime
- -> );
- Query OK, 0 rows affected (0.36 sec)
2 复制表
- mysql> create table auth2 select * from auth;
- Query OK, 0 rows affected (0.29 sec)
- Records: 0 Duplicates: 0 Warnings: 0
二查看表
1 查看表结构
- mysql> desc auth;
- +----------+-------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +----------+-------------+------+-----+---------+----------------+
- | id | int(10) | NO | PRI | NULL | auto_increment |
- | name | varchar(10) | NO | | NULL | |
- | age | int(3) | YES | | NULL | |
- | birthday | datetime | YES | | NULL | |
- +----------+-------------+------+-----+---------+----------------+
- 4 rows in set (0.02 sec)
2 查看表的创建信息
- mysql> show create table auth;
- +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- | Table | Create Table |
- +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- | auth | CREATE TABLE `auth` (
- `id` int(10) NOT NULL AUTO_INCREMENT,
- `name` varchar(10) NOT NULL,
- `age` int(3) DEFAULT NULL,
- `birthday` datetime DEFAULT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
- +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- 1 row in set (0.00 sec)
三修改表
1 修改表名称
- mysql> alter table auth2 rename auth666;
- Query OK, 0 rows affected (0.10 sec)
2 增加表字段
- mysql> alter table auth add addr char(6) not null;
- Query OK, 0 rows affected (0.53 sec)
- Records: 0 Duplicates: 0 Warnings: 0
3 修改表字段
修改表字段信息
- mysql> alter table auth modify addr varchar(6) null;
- Query OK, 0 rows affected (0.60 sec)
- Records: 0 Duplicates: 0 Warnings: 0
修改表字段名以及字段信息
- mysql> alter table auth change addr address varchar(6);
- Query OK, 0 rows affected (0.09 sec)
- Records: 0 Duplicates: 0 Warnings: 0
4. 删除表字段
- mysql> alter table auth drop birthday;
- Query OK, 0 rows affected (0.49 sec)
- Records: 0 Duplicates: 0 Warnings: 0
四删除表
- mysql> drop table auth666;
- Query OK, 0 rows affected (0.10 sec)
五表的数据类型
http://www.cnblogs.com/fu-yong/p/8495001.html
六表的约束
http://www.cnblogs.com/fu-yong/p/8495003.html
来源: http://www.bubuko.com/infodetail-2513289.html