Mysql DBA Advanced Operations Learning notes-Delete table fields change table names remove tables combat

9.12 Adding or deleting table fields

9.12.1 command syntax and default add-in Demo

1. Command syntax: ALTER TABLE name add field type other

2. Test table Data

mysql> show create table student\G*************************** 1. row ***************************   Table: studentCreate Table: CREATE TABLE `student` (  `id` int(4) NOT NULL,  `name` char(20) NOT NULL,  `age` tinyint(2) NOT NULL DEFAULT ‘0‘,  `dept` varchar(16) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)mysql> desc student;+-------+-------------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id| int(4)  | NO   | | NULL|   || name  | char(20)| NO   | | NULL|   || age   | tinyint(2)  | NO   | | 0   |   || dept  | varchar(16) | YES  | | NULL|   |+-------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)

3. Practice

For example, add fields in table student sex,qq type char (4), varchar (15)

4. Execution of the command demo

A. Adding a gender column, default statement

 mysql> desc student;+-------+-------------+------+-----+---------+-------+| Field | type| Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id| Int (4) | NO | | null| || name | char (20) | NO | | null| || Age | tinyint (2) | NO | | 0 | || Dept | varchar (16) | YES | | null| |+-------+-------------+------+-----+---------+-------+4 rows in Set (0.00 sec) mysql> ALTER TABLE student add sex char (4); Query OK, 0 rows affected (0.03 sec) records:0 duplicates:0 warnings:0mysql> desc student;+-------+-------------+-- ----+-----+---------+-------+| Field | type| Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id| Int (4) | NO | | null| || name | char (20) | NO | | null| || Age | tinyint (2) | NO | | 0 | || Dept | varchar (16) | YES | | null| || sex | CHAR (4) | YES | | null| |+-------+-------------+------+-----+---------+-------+5 rows in Set (0.00 sec) 

Tip: Add columns By default to the end of all fields

9.12.2 specify where to add columns in the table

B. Specify the following to add QQ to the Name column

mysql> alter table student add qq varchar(15) after name;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc student;+-------+-------------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id| int(4)  | NO   | | NULL|   || name  | char(20)| NO   | | NULL|   || qq| varchar(15) | YES  | | NULL|   || age   | tinyint(2)  | NO   | | 0   |   || dept  | varchar(16) | YES  | | NULL|   || sex   | char(4) | YES  | | NULL|   |+-------+-------------+------+-----+---------+-------+6 rows in set (0.00 sec)

C. Adding an address column in the first column

mysql> alter table student add address char(20) first;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc student;+---------+-------------+------+-----+---------+-------+| Field   | Type| Null | Key | Default | Extra |+---------+-------------+------+-----+---------+-------+| address | char(20)| YES  | | NULL|   || id  | int(4)  | NO   | | NULL|   || name| char(20)| NO   | | NULL|   || qq  | varchar(15) | YES  | | NULL|   || age | tinyint(2)  | NO   | | 0   |   || dept| varchar(16) | YES  | | NULL|   || sex | char(4) | YES  | | NULL|   |+---------+-------------+------+-----+---------+-------+7 rows in set (0.00 sec)

9.13 Changing table names

9.13.1 Rename Method

1. Command syntax: Rename table name to new table name;

Columns such as: Table student name changed to user

mysql> show tables;+---------------+| Tables_in_zbf |+---------------+| student   |+---------------+1 row in set (0.00 sec)mysql> rename table student to user;Query OK, 0 rows affected (0.00 sec)mysql> show tables;+---------------+| Tables_in_zbf |+---------------+| user  |+---------------+1 row in set (0.01 sec)

9.13.2 Alter method

mysql> alter table user rename to student;Query OK, 0 rows affected (0.01 sec)mysql> show tables;+---------------+| Tables_in_zbf |+---------------+| student   |+---------------+1 row in set (0.00 sec)

9.14 Deleting a table

Command syntax: Drop table< table name >

Example: Deleting a table with a table named student

mysql> drop table student;Query OK, 0 rows affected (0.01 sec)mysql> show tables;Empty set (0.01 sec)

Mysql DBA Advanced Operations Learning notes-Delete table fields change table names remove tables combat