说明

有关表的CHECK约束问题：

在MySQL 8.0.16之前，CREATE TABLE只允许以下有限版本的表约束语法，这些语法被解析和忽略：CHECK
要实现CHECK，离散型数据使用enum，连续性数据使用触发器
表的操作涉及数据类型以及表的约束条件和列的约束

使用数据库

  1. mysql> use stu;
  2. Database changed

创建表简单版

  1. mysql> create table student(
  2.     -> id int(30),
  3.     -> name varchar(30),
  4.     -> age int
  5.     -> );
  6. Query OK, 0 rows affected (0.03 sec)

创建表复杂版

  1. mysql> create table student_1(
  2.     -> id int(20) auto_increment primary key,
  3.     -> name varchar(10) not null,
  4.     -> phone varchar(20) comment 'phone',
  5.     -> address varchar(20) default 'NULL'
  6.     -> )engine=innodb;
  7. Query OK, 0 rows affected (0.02 sec)

查看创建的表

  1. mysql> show create table student_1;
  2. +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | Table     | Create Table                                                                                                                                                                                                                                              |
  4. +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | student_1 | CREATE TABLE `student_1` (
  6.   `id` int(20) NOT NULL AUTO_INCREMENT,
  7.   `name` varchar(10) NOT NULL,
  8.   `phone` varchar(20) DEFAULT NULL COMMENT 'phone',
  9.   `address` varchar(20) DEFAULT 'NULL',
  10.   PRIMARY KEY (`id`)
  11. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
  12. +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  13. 1 row in set (0.00 sec)

查看表结构

  1. mysql> desc students;
  2. +-----------+-------------+------+-----+---------+----------------+
  3. | Field     | Type        | Null | Key | Default | Extra          |
  4. +-----------+-------------+------+-----+---------+----------------+
  5. | id        | int(20)     | NO   | PRI | NULL    | auto_increment |
  6. | name      | varchar(10) | NO   |     | NULL    |                |
  7. | tel_phone | varchar(13) | YES  |     | NULL    |                |
  8. | address   | varchar(20) | YES  |     | NULL    |                |
  9. +-----------+-------------+------+-----+---------+----------------+
  10. 4 rows in set (0.00 sec)

修改表名

  1. mysql> alter table student_1 rename to students;
  2. Query OK, 0 rows affected (0.01 sec)

修改表中字段

  1. mysql> alter table students change phone tel_phone int(11);
  2. Query OK, 0 rows affected (0.03 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  5. mysql> alter table students modify tel_phone varchar(13);
  6. Query OK, 0 rows affected (0.04 sec)
  7. Records: 0  Duplicates: 0  Warnings: 0

删除表的字段

  1. mysql> alter table students drop gender;
  2. Query OK, 0 rows affected (0.04 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0