Modification of mysql database basic data table structure

Table structure modification

background

In general, you should try to design well at the beginning, because once the database design is completed, it is better not to make major changes.

However, no one can guarantee that my watch will always be sufficient and may need to be changed at any time.

Therefore, we still need to understand and modify the table structure.

Note: table structure modification is generally not done by the development zone.

Prepare data table and data first:

CREATE TABLE students (
  number CHAR(9),   # Student ID
  name VARCHAR(20),  # Full name
  klass VARCHAR(10),    # class
  age INT,   # Age
  birth DATE   # Birthday
);

INSERT INTO students (number, name, klass, age, birth)
    VALUES ('201804001', 'Liu Yi', 19, 16, '2002-01-01'),
            ('201804002', 'Chen er', 18, 17, '2001-01-02'),
            ('201804003', 'Zhang San', 19, 18, '2000-01-03'),
            ('201804004', 'Li Si', 19, 19, '2001-01-04'),
            ('201804005', 'Wang Wu', 19, 16, '2002-01-05'),
            ('201804006', 'Zhao Liu', 18, 19, '1999-01-06'),
            ('201804007', 'Sun Qi', 19, 17, '2001-01-07'),
            ('201804008', 'Zhou Ba', 19, 18, '2000-01-08'),
            ('201804009', 'Wu 9', 18, 17, '2001-01-09'),
            ('201804010', 'Zheng Shi', 19, 18, '2000-01-10');

 mysql> select * from students;
+-----------+--------+-------+------+------------+
| number    | name   | klass | age  | birth      |
+-----------+--------+-------+------+------------+
| 201804001 | Liu Yi   | 19    |   16 | 2002-01-01 |
| 201804002 | Chen er   | 18    |   17 | 2001-01-02 |
| 201804003 | Zhang San   | 19    |   18 | 2000-01-03 |
| 201804004 | Li Si   | 19    |   19 | 2001-01-04 |
| 201804005 | Wang Wu   | 19    |   16 | 2002-01-05 |
| 201804006 | Zhao Liu   | 18    |   19 | 1999-01-06 |
| 201804007 | Sun Qi   | 19    |   17 | 2001-01-07 |
| 201804008 | Zhou Ba   | 19    |   18 | 2000-01-08 |
| 201804009 | Wu 9   | 18    |   17 | 2001-01-09 |
| 201804010 | Zheng Shi   | 19    |   18 | 2000-01-10 |
+-----------+--------+-------+------+------------+
10 rows in set (0.00 sec)

Supplementary order:

Table description: DESC tb_name;
Key in the table: Show keys from TB? Name;

The main command we want to use is: ALTER TABLE

Modify columns

Add column

ADD COLUMN

# Add the gender column to the last column
ALTER TABLE students
    ADD COLUMN gender BOOL;
mysql> ALTER TABLE students ADD COLUMN gender BOOL;
Query OK, 0 rows affected (0.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from students;
+-----------+--------+-------+------+------------+--------+
| number    | name   | klass | age  | birth      | gender |
+-----------+--------+-------+------+------------+--------+
| 201804001 | Liu Yi   | 19    |   16 | 2002-01-01 |   NULL |
| 201804002 | Chen er   | 18    |   17 | 2001-01-02 |   NULL |
| 201804003 | Zhang San   | 19    |   18 | 2000-01-03 |   NULL |
| 201804004 | Li Si   | 19    |   19 | 2001-01-04 |   NULL |
| 201804005 | Wang Wu   | 19    |   16 | 2002-01-05 |   NULL |
| 201804006 | Zhao Liu   | 18    |   19 | 1999-01-06 |   NULL |
| 201804007 | Sun Qi   | 19    |   17 | 2001-01-07 |   NULL |
| 201804008 | Zhou Ba   | 19    |   18 | 2000-01-08 |   NULL |
| 201804009 | Wu 9   | 18    |   17 | 2001-01-09 |   NULL |
| 201804010 | Zheng Shi   | 19    |   18 | 2000-01-10 |   NULL |
+-----------+--------+-------+------+------------+--------+
10 rows in set (0.01 sec)

SHOW CREATE TABLE students\G
*************************** 1. row ***************************
       Table: students
Create Table: CREATE TABLE `students` (
  `number` char(9) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `klass` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

Sequence position

FIRST

# Add the gender column to the first column and give it a default value

ALTER TABLE students
  ADD COLUMN gender BOOL NOT NULL DEFAULT TRUE FIRST;

AFTER

# Add gender column after klass column

ALTER TABLE  students
  ADD COLUMN gender BOOL NOT NULL DEFAULT TRUE AFTER klass;

Delete column

DROP COLUMN

# Remove gender column
ALTER TABLE students
    DROP COLUMN gender;

mysql> ALTER TABLE students DROP COLUMN gender;
Query OK, 0 rows affected (0.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE students\G
*************************** 1. row ***************************
       Table: students
Create Table: CREATE TABLE `students` (
  `number` char(9) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `klass` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `birth` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Modify columns

MODIFY COLUMN only: MODIFY COLUMN

# Move the gender column after the age column

ALTER TABLE students
  MODIFY COLUMN gender BOOL NOT NULL DEFAULT TRUE AFTER age;

mysql> ALTER TABLE students MODIFY COLUMN gender BOOL NOT NULL DEFAULT TRUE AFTER age;
ERROR 1054 (42S22): Unknown column 'gender' in 'students'
mysql> ALTER TABLE  students
  ->     ADD COLUMN gender BOOL NOT NULL DEFAULT TRUE AFTER klass;
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE students MODIFY COLUMN gender BOOL NOT NULL DEFAULT TRUE AFTER age;
Query OK, 0 rows affected (0.40 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE students\G
*************************** 1. row ***************************
     Table: students
Create Table: CREATE TABLE `students` (
`number` char(9) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`klass` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`gender` tinyint(1) NOT NULL DEFAULT '1',
`birth` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

With rename: CHANGE COLUMN

# Change the birth column to a DATETIME type column named birthday

ALTER TABLE students
  CHANGE COLUMN birth birthday DATETIME

mysql> ALTER TABLE students CHANGE COLUMN birth birthday DATETIME;
Query OK, 10 rows affected (0.46 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE students\G
*************************** 1. row ***************************
     Table: students
Create Table: CREATE TABLE `students` (
`number` char(9) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`klass` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`gender` tinyint(1) NOT NULL DEFAULT '1',
`birthday` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Sequence position

FIRST
AFTER

Modification key

Add constraint key

ADD PRIMARY KEY

# Add primary key constraint for number column (unique key similar)

ALTER TABLE students
  ADD PRIMARY KEY (number);

mysql> ALTER TABLE students 
  -> ADD PRIMARY KEY (number);
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE students\G
*************************** 1. row ***************************
     Table: students
Create Table: CREATE TABLE `students` (
`number` char(9) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`klass` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`gender` tinyint(1) NOT NULL DEFAULT '1',
`birthday` datetime DEFAULT NULL,
PRIMARY KEY (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ADD UNIQUE KEY

# Remove the primary key constraint for the number column (but leave it non empty)

ALTER TABLE students
  ADD UNIQUE KEY (number);

mysql> ALTER TABLE students  ADD UNIQUE KEY (number);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE students\G
*************************** 1. row ***************************
     Table: students
Create Table: CREATE TABLE `students` (
`number` char(9) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`klass` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`gender` tinyint(1) NOT NULL DEFAULT '1',
`birthday` datetime DEFAULT NULL,
PRIMARY KEY (`number`),
UNIQUE KEY `number` (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Delete constraint key

Delete primary key: DROP PRIMARY KEY

ALTER TABLE students
  DROP PRIMARY KEY;

mysql> ALTER TABLE students  DROP PRIMARY KEY ;
Query OK, 10 rows affected (2.07 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE students\G
*************************** 1. row ***************************
     Table: students
Create Table: CREATE TABLE `students` (
`number` char(9) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`klass` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`gender` tinyint(1) NOT NULL DEFAULT '1',
`birthday` datetime DEFAULT NULL,
UNIQUE KEY `number` (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Delete unique key: DROP KEY

ALTER TABLE students
  DROP KEY number;

mysql> ALTER TABLE students  DROP KEY number ;
Query OK, 10 rows affected (0.39 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE students\G
*************************** 1. row ***************************
     Table: students
Create Table: CREATE TABLE `students` (
`number` char(9) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`klass` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`gender` tinyint(1) NOT NULL DEFAULT '1',
`birthday` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Note: number here refers to the key name, not the column name.

No function key (index)

ADD KEY

ALTER TABLE students
  ADD KEY (name);

mysql> ALTER TABLE students  ADD  KEY (name) ;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE students\G
*************************** 1. row ***************************
     Table: students
Create Table: CREATE TABLE `students` (
`number` char(9) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`klass` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`gender` tinyint(1) NOT NULL DEFAULT '1',
`birthday` datetime DEFAULT NULL,
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

DROP KEY

ALTER TABLE students
  DROP KEY name;
mysql> ALTER TABLE students  DROP KEY name ;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE students\G
*************************** 1. row ***************************
     Table: students
Create Table: CREATE TABLE `students` (
`number` char(9) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`klass` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`gender` tinyint(1) NOT NULL DEFAULT '1',
`birthday` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Note: number here refers to the key name, not the column name.

Posted by meanrat on Wed, 01 Apr 2020 18:15:05 -0700

Programmer Group

Modification of mysql database basic data table structure

Table structure modification

background

Supplementary order:

Modify columns

Add column

Sequence position

Delete column

Modify columns

Sequence position

Modification key

Add constraint key

Delete constraint key

No function key (index)

Hot Keywords