MySQL/mariadb知识点——操作篇(3)表管理语句

创建表

方法一：直接创建

CREATE TABLE [IF NOT EXISTS] tbl_name (create_definition,...)

示例：

MariaDB [testdb]> CREATE TABLE IF NOT EXISTS students (id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(30) NOT NULL,phone CHAR(11),gender ENUM('M','F')); 

方法二：通过查询现存表创建，新表会被直接插入查询来的数据

CREATE TABLE [IF NOT EXISTS] tbl_name select_statement

示例：

MariaDB [testdb]> CREATE TABLE user SELECT user,host,password FROM mysql.user;

如果只想模仿查询旧表创建一个无记录的表，加入如下条件即可；

where 0=1；

示例：

MariaDB [testdb]> CREATE TABLE user2 SELECT user,host,password FROM mysql.user WHERE 0=1;

方法三：通过复制现存表的表结构创建，但不复制数据

CREATE TABLE [IF NOT EXISTS] tbl_name LIKE old_tbl_name

MariaDB [testdb]> CREATE TABLE user3 LIKE mysql.user;

查看表

SHOW TABLES; 列出库中所有的表

DESC [db_name.]tb_name; 查看表结构

SHOW CREATE TABLE tbl_name; 查看创建表的命令

SHOW TABLE STATUS LIKE 'tbl_name'; 查看表状态

SHOW TABLE STATUS FROM db_name; 查看指定库中所有表状态

SHOW ENGINES; 查看所有存储引擎

示例：

显示当前数据库上的所有表；

MariaDB [testdb]> SHOW tables;
+------------------+
| Tables_in_testdb |
+------------------+
| score            |
| students         |
| user             |
| user2            |
+------------------+
4 rows in set (0.00 sec)

查看当前库所有表的具体属性信息；

MariaDB [testdb]> SHOW table status\G;
*************************** 1. row ***************************
           Name: score
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 6
 Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 7
    Create_time: 2018-06-10 20:11:53
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
......

如只想查看当前库的某张状态；可用like匹配名称；

MariaDB [testdb]> SHOW table status like 'students' \G;

或者使用通配符，进行搜索匹配；

MariaDB [testdb]> SHOW table status where name like '%tude%'\G;

查看表结构；

MariaDB [testdb]> DESC students;
+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| id      | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| name    | varchar(30)         | NO   |     | NULL    |                |
| ages    | tinyint(2)          | NO   |     | NULL    |                |
| gender  | enum('M','F')       | YES  |     | NULL    |                |
| address | text                | YES  |     | NULL    |                |
| sid     | tinyint(2)          | YES  |     | NULL    |                |
| tid     | tinyint(2)          | YES  |     | NULL    |                |
+---------+---------------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)

查看某张表被创建时对应的sql语句；

MariaDB [testdb]> SHOW CREATE table students\G;
*************************** 1. row ***************************
       Table: students
Create Table: CREATE TABLE `students` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `ages` tinyint(2) NOT NULL,
  `gender` enum('M','F') DEFAULT NULL,
  `address` text DEFAULT NULL,
  `sid` tinyint(2) DEFAULT NULL,
  `tid` tinyint(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

修改表

如果在表中还没有任何数据时，我们可以相对倾诉的修改表结构；但当表中已经存在了一定数据，我们应充分考虑到修改表结构之后所带来的相应问题。所以，在表设计伊始，就应该具有前瞻性，尽量避免日后对表进行二次修改。

ALTER TABLE tbl_name [alter_specification [, alter_specification] ...]

修改表名or重命名表；

MariaDB [testdb]> ALTER test1 RENAME AS test2;

增加属性；ADD

只添加字段，不指定字段任何属性

MariaDB [testdb]> ALTER TABLE test ADD column age INT;

 修改表字段时，column可省；不指定被操作对象时，默认为列操作

为表添加字段，同时添加相应约束

MariaDB [testdb]> ALTER TABLE test ADD age INT not null default 0;

在name字段后添加age字段

MariaDB [testdb]> ALTER TABLE students ADD age TINYINT AFTER name;　　

删除属性；DROP

warning！！删除包含数据的字段前请三思；真爱数据，请勿手抖

MariaDB [testdb]> ALTER TABLE students DROP phone;

修改属性；CHANGE,MODIFY

修改age字段名称为ages并从新定义数据类型

MariaDB [testdb]> ALTER TABLE students CHANGE age ages TINYINT(2) NOT NULL;

修改gender字段的数据类型

MariaDB [testdb]> ALTER TABLE students MODIFY gender ENUM('M','F');

删除表

warning！！删除表前请三思；真爱数据，请勿手抖；此操作不可逆。

MariaDB [testdb]> DROP TABLE user3;