MySQL常用命令之——数据表操作

以OS X EI Captain 10.11.4系统下MySQL 5.6.29数据库为例

1、查看数据表列表

#1、查看当前数据库中的数据表
SHOW TABLES;

#2、查看指定数据库中的数据表，FROM后边为要查看的数据库名称
SHOW TABLES FROM mysql;

2、创建表

#1、创建简单表
CREATE TABLE tb1 (
    id INT,
    username VARCHAR (30)
);

#2、创建表，并对字段设置非空约束
CREATE TABLE tb2 (
    id INT NOT NULL,
    username VARCHAR (30) NOT NULL
);

#3、创建表，并对字段设置默认值
CREATE TABLE tb3 (
    id INT,
    username VARCHAR (30),
    gender TINYINT DEFAULT 0
);

#4、创建表，并对字段设置自增属性(该列必须定义为键)
CREATE TABLE tb4 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR (30)
);

#5、创建表，并设置主键
CREATE TABLE tb5 (
    id INT PRIMARY KEY,
    username VARCHAR (30)
);
#或
CREATE TABLE tb5 (
    id INT,
    username VARCHAR (30),
    PRIMARY KEY (id)
);

#6、创建表，并设置唯一约束
CREATE TABLE tb6 (
    id INT UNIQUE,
    username VARCHAR (30)
);
或
CREATE TABLE tb6 (
    id INT,
    username VARCHAR (30),
    UNIQUE (id)
);

#7、创建表，并添加索引(这里只讲简单索引，其他索引在专门的索引章节来讲)
CREATE TABLE tb7 (
    id INT,
    username VARCHAR (30),
    INDEX(id)
);

#8、创建表，并对该表添加注释
CREATE TABLE tb8 (
    id INT,
    username VARCHAR (30)
) COMMENT '该表用于测试';

#9、创建表，同时设置数据库引擎(MySQL默认引擎为InnoDB)
CREATE TABLE tb9 (
    id INT,
    username VARCHAR (30)
)ENGINE=MyISAM;

3、修改表

#以tb3表为例：
CREATE TABLE tb3 (
    id INT,
    username VARCHAR (30),
    gender TINYINT DEFAULT 0
);

#1、修改表的名字；
ALTER TABLE tb3 RENAME table3;
#然后再以另一种方法修改回来
RENAME TABLE table3 TO tb3;

#2、修改表的引擎；
ALTER TABLE tb3 ENGINE = MyISAM;

#3、新增列；
    #a.在最后一列之后插入(默认)
    ALTER TABLE tb3 ADD age TINYINT UNSIGNED;

    #b.在第一列之前插入
    ALTER TABLE tb3 ADD age TINYINT UNSIGNED FIRST;

    #c.在指定列之后插入
    ALTER TABLE tb3 ADD age TINYINT UNSIGNED AFTER id;

#4、删除列；
    #a.删除指定列
    ALTER TABLE tb3 DROP age;

    #b.删除指定的多列(注意，要写多个DROP)
    ALTER TABLE tb3 DROP username,DROP gender;

#5、移动列；
    #a.将某列移到第一列(数据类型也要照写)
    ALTER TABLE tb3 MODIFY gender TINYINT DEFAULT 0 FIRST;

    #b.将某列移动到指定列后边
    ALTER TABLE tb3 MODIFY gender TINYINT DEFAULT 0 AFTER id;

#6、字段修改
    #a.修改字段名称(数据类型照写)
    ALTER TABLE tb3 CHANGE gender sex TINYINT DEFAULT 0;

    #b.修改字段类型(注意从大类型到小类型修改时可能造成数据丢失)
    ALTER TABLE tb3 MODIFY gender TINYINT UNSIGNED DEFAULT 0;

    #c.同时修改数据名称和数据类型
    ALTER TABLE tb3 CHANGE sex gender INT DEFAULT 0;

#7、索引修改
    #a.添加索引(两种方法)
    CREATE INDEX index_username ON tb3(username);
    ALTER TABLE tb3 ADD INDEX index_username(username );

    #b.删除索引
    DROP INDEX index_username ON tb3

#8、主键修改
    #a.添加主键
    ALTER TABLE tb3 ADD CONSTRAINT PK_tb3_id PRIMARY KEY(id);

    #删除主键
    ALTER TABLE tb3 DROP PRIMARY KEY;

#9、唯一约束修改
    #a.添加唯一约束
    ALTER TABLE tb3 ADD UNIQUE (username);

    #b.删除唯一约束
    ALTER TABLE tb3 DROP INDEX username;

#10、非空约束修改
    #a.添加非空约束(可以理解为修改数据类型，只是在原有类型后边加了NOT NULL)
    ALTER TABLE tb3 MODIFY username VARCHAR (30) NOT NULL;

    #b.删除非空约束(同理)
    ALTER TABLE tb3 MODIFY username VARCHAR (30);

#11、默认约束修改
    #a.添加或修改默认约束(两种方法)
    ALTER TABLE tb3 ALTER gender SET DEFAULT 1;
    ALTER TABLE tb3 MODIFY username VARCHAR (30) DEFAULT 'abc';

    #b.删除默认约束(两种方法)
    ALTER TABLE tb3 ALTER gender DROP DEFAULT;
    ALTER TABLE tb3 MODIFY username VARCHAR (30);

4、删除表

#1、简单删除
DROP TABLE tb1;

#2、删除多张表
DROP TABLE tb2,tb3,tb4;

#3、删除时判断该表是否存在，若存在则删除，否则不执行操作
DROP TABLE IF EXISTS tb10;