创建表
#语法： create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名3 类型[(宽度) 约束条件] ); #注意： 1. 在同一张表中，字段名是不能相同 2. 宽度和约束条件可选 3. 字段名和类型是必须的
mysql> create database staff; Query OK, 1 row affected (0.00 sec) mysql> use staff; Database changed mysql> create table staff_info (id int,name varchar(50),age int(3),sex enum('male','female'),phone bigint(11),job varchar(11)); Query OK, 0 rows affected (0.02 sec) mysql> show tables; +-----------------+ | Tables_in_staff | +-----------------+ | staff_info | +-----------------+ row in set (0.00 sec) mysql> desc staff_info; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ rows in set (0.00 sec) mysql> select id,name,sex from staff_info; Empty set (0.00 sec) mysql> select * from staff_info; Empty set (0.00 sec) 建表
mysql> insert into staff_info (id,name,age,sex,phone,job) values (1,'Alex',83,'female',13651054608,'IT'); Query OK, 1 row affected (0.00 sec) mysql> insert into staff_info values (2,'Egon',26,'male',13304320533,'Teacher'); Query OK, 1 row affected (0.00 sec) mysql> insert into staff_info values (3,'nezha',25,'male',13332353222,'IT'),(4,'boss_jin',40,'male',13332353333,'IT'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from staff_info; +------+----------+------+--------+-------------+---------+ | id | name | age | sex | phone | job | +------+----------+------+--------+-------------+---------+ | 1 | Alex | 83 | female | 13651054608 | IT | | 2 | Egon | 26 | male | 13304320533 | Teacher | | 3 | nezha | 25 | male | 13332353222 | IT | | 4 | boss_jin | 40 | male | 13332353333 | IT | +------+----------+------+--------+-------------+---------+ rows in set (0.00 sec) 插入数据
查看表结构
查看表结构有两种方式：
describe [tablename];这种方法和desc [tablename];效果相同；可以查看当前的表结构
虽然desc命令可以查看表的定义，但是其输出的信息还不够全面，为了得到更全面的表定义信息，有时候就需要查看创建表的SQL语句，使用show create table语法。除了可以看到表定义之外，还可以看到engine（存储引擎）和charset（字符集）等信息。（G选项的含义是是的记录能够竖向排列，以便更好的显示内容较长的记录。）
mysql> describe staff_info; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ rows in set (0.00 sec) mysql> desc staff_info; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ rows in set (0.00 sec) mysql> show create table staff_infoG; *************************** 1. row *************************** Table: staff_info Create Table: CREATE TABLE `staff_info` ( `id` int(11) DEFAULT NULL, `name` varchar(50) DEFAULT NULL, `age` int(3) DEFAULT NULL, `sex` enum('male','female') DEFAULT NULL, `phone` bigint(11) DEFAULT NULL, `job` varchar(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 row in set (0.01 sec) ERROR: No query specified desc和show create table
修改表结构
语法： 1. 修改表名 ALTER TABLE 表名 RENAME 新表名; 2. 增加字段 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…], ADD 字段名 数据类型 [完整性约束条件…]; 3. 删除字段 ALTER TABLE 表名 DROP 字段名; 4. 修改字段 ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…]; 5.修改字段排列顺序/在增加的时候指定字段位置 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST; ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; ALTER TABLE 表名 CHANGE 字段名 旧字段名 新字段名 新数据类型 [完整性约束条件…] FIRST; ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
mysql> desc staff_info; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ rows in set (0.00 sec) # 表重命名 mysql> alter table staff_info rename staff; Query OK, 0 rows affected (0.00 sec) mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ rows in set (0.00 sec) # 删除sex列 mysql> alter table staff drop sex; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ rows in set (0.01 sec) # 添加列 mysql> alter table staff add sex enum('male','female'); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 # 修改id的宽度 mysql> alter table staff modify id int(4); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(4) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ rows in set (0.01 sec) # 修改name列的字段名 mysql> alter table staff change name sname varchar(20); Query OK, 4 rows affected (0.03 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(4) | YES | | NULL | | | sname | varchar(20) | YES | | NULL | | | age | int(3) | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ rows in set (0.00 sec) # 修改sex列的位置 mysql> alter table staff modify sex enum('male','female') after sname; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(4) | YES | | NULL | | | sname | varchar(20) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | age | int(3) | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ rows in set (0.00 sec) # 创建自增id主键 mysql> alter table staff modify id int(4) primary key auto_increment; Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | sname | varchar(20) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | age | int(3) | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+----------------+ rows in set (0.00 sec) # 删除主键，可以看到删除一个自增主键会报错 mysql> alter table staff drop primary key; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key # 需要先去掉主键的自增约束，然后再删除主键约束 mysql> alter table staff modify id int(11); Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | sname | varchar(20) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | age | int(3) | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ rows in set (0.01 sec) mysql> alter table staff drop primary key; Query OK, 4 rows affected (0.06 sec) Records: 4 Duplicates: 0 Warnings: 0 # 添加联合主键 mysql> alter table staff add primary key (sname,age); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 # 删除主键 mysql> alter table staff drop primary key; Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 # 创建主键id mysql> alter table staff add primary key (id); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | sname | varchar(20) | NO | | | | | sex | enum('male','female') | YES | | NULL | | | age | int(3) | NO | | 0 | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ rows in set (0.00 sec) # 为主键添加自增属性 mysql> alter table staff modify id int(4) auto_increment; Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | sname | varchar(20) | NO | | | | | sex | enum('male','female') | YES | | NULL | | | age | int(3) | NO | | 0 | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+----------------+ rows in set (0.00 sec) 示例
删除表
DROP TABLE 表名;