Create, modify and delete tables for MySQL learning 04/19 Update SLTechnology News&Howtos

Create, modify and delete tables for MySQL learning

2025-04-19 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

1. How to create a table structure?

The syntax format is as follows:

CREATE TABLE [IF NOT EXISTS] table name (field 1 data type [integrity constraint], field 2 data type [integrity constraint],.... Field n data type [integrity constraint])

Note: [IF NOT EXISTS] is an optional field. If you add this option, if the created data table already exists, no error will be reported, only a warning message will appear, but no error will be reported. If you do not add this option, an error message will appear. The name of the created table cannot be SQL keywords such as create, updata, order, etc. Each field is separated by a comma, and the last field does not need a comma.

Integrity constraints are also optional fields, which restrict fields. The integrity constraints commonly used in MySQL are shown in the following table:

Table 1 Integrity constraints in MySQL

Constraint description

PRIMARY KEY indicates that the field is the primary key of the table and can uniquely identify the corresponding tuple

FOREIGN KEY

Identifies that the property is the foreign key of the table, and the primary key NOT NULL of the parent table identifies that the field cannot be empty UNIQUE that the field is unique

The value of the attribute identified by AUTO_INCREMENT increases automatically, and the characteristic of MySQL, DEFAULT, can set the default value for the field.

The following sections describe the testing of these integrity constraints.

1.1 testing of primary keys

The purpose of setting the primary key is to help MySQL find a piece of information in the table as quickly as possible.

Features: the primary key must be unique

The fields of the record primary key in any two tables cannot be the same.

Primary key must be a non-null value

The primary key can be a single field or a combination of multiple fields.

Primary key test for a single field

Syntax rules: field name data type PRIMARY KEY

Example: create a student table and set stu_id as the primary key. The implementation code is as follows: CREATE TABLE IF NOT EXISTS student (stu_id INT PRIMARY KEY, stu_name VARCHAR (20), stu_age INT, stu_sex ENUM ('male', 'female', 'confidential'))

Testing of multiple primary keys

Syntax rules: PRIMARY KEY (field 1, field 2. Field n)

Example: create a teacher table and set the primary keys to tea_id and course_id. The implementation code is as follows: CREATE TABLE IF NOT EXISTS teacher (tea_id TINYINT, name VARCHAR (20), course_id INT, PRIMARY KEY (tea_id,course_id))

1.2 testing of foreign keys

The grammar rules are as follows:

CONSTRAINT foreign key alias FOREIGN KEY (field 1.1, field 1.2, field 1.3,...)

REFERENCES table name (field 2. 1, field 2. 2, field 2. 2.)

Description: the 'foreign key alias' is the code name of the foreign key; the parameter list in field 1 is the foreign key set in the child table, the 'table name' parameter is the name of the parent table, and the field 2 parameter list is the primary key of the parent table.

Example: create a new grade table, set stu_id as the foreign key, and associate it with the primary key stu_id in the student table.

The SQL code is as follows:

CREATE TABLE IF NOT EXISTS grade (id INT PRIMARY KEY, stu_id INT, stu_name VARCHAR (20), `Math `FLOAT, `English `FLOAT, `Chinese `FLOAT, CONSTRAINT grade_fk FOREIGN KEY (stu_id) REFERENCES student (stu_id))

Note: the foreign key of the created child table must be the primary key of the parent table. And the data types of the two must be the same, if they are not consistent, they cannot be created successfully.

1.3 testing of non-null constraints

The non-null constraint requires that the value of the field set in the table cannot be null. If the value of the field inserted by the user is null, the database will report an error message and cannot insert the data correctly. The 'NOT NULL' constraint is often used in conjunction with the "DEFAULT" constraint.

Basic grammatical rules:

Field data type NOT NULL

Example: recreate a student2 table and set the sex field to non-empty.

CREATE TABLE IF NOT EXISTS student2 (id TINYINT PRIMARY KEY, name VARCHAR (20), sex ENUM ('male', 'female', 'confidential') NOT NULL, age TINYINT)

1.4 Test uniqueness constraint

Uniqueness constraint means that the value of this field in all records cannot be repeated. For example, the × × × number of each person is different, and the × × × field can be set to unique. When two identical × × × numbers appear in the inserted data, an alarm message will appear in the database. To put it simply, the uniqueness constraint requires that the value of this field cannot be repeated for all records.

The basic syntax is as follows:

Field data type UNIQUE

Example: create a student3 table and set the attribute of the id field to uniqueness. The SQL code is as follows:

CREATE TABLE IF NOT EXISTS student3 (id INT NOT NULL UNIQUE, name VARCHAR (20), age TINYINT)

1.5 Test self-growth

AUTO_INCREMENT is a special constraint in MySQL database, which automatically generates a unique ID for new records inserted in the table. And only one field in a table can use the AUTO_INCREMENT constraint, the field must be part of the primary key, and the constrained field can be of any integer type (INT TINYINT SMALLINT MEDIUMINT BIGINT). By default, the field grows from 1.

Basic syntax: field name data type AUTO_INCREMENT

Example: create a new table student4 and set the field id to self-growing attribute.

CREATE TABLE IF NOT EXISTS student4 (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR (30), age TINYINT)

1.6 Test default values

You can add a default value for the field in the table when you create the table, and if no data is inserted for the field when you insert the data, the field will automatically add a default value. Set the default value through the DEFAULT keyword.

Basic syntax: field data type DEFAULT default

Example: create a table student5, and set the default values for the fields age and sex in the table. The SQL code is as follows:

CREATE TABLE IF NOT EXISTS student5 (id INT PRIMARY KEY, name VARCHAR (20) NOT NULL, age TINYINT DEFAULT'20), sex ENUM ('male', 'female', 'confidential') DEFAULT 'male')

2. How to query the table structure?

After the database definition is completed, you can view the defined database through the query statement. The frequently used query statement is

DESCRIBE and SHOW CREATE TABLE can view the field name, data type, and integrity constraints of the table through these two statements.

2.1 Test DESCRIBE statement

Syntax structure: DESCRIBE table name

Example: query the student table structure that has just been established.

DESCRIBE can be abbreviated to DESC, and the code runs as follows:

From the query results, you can see the field name (Field), data type (Type), whether it is empty (null), whether the primary foreign key (key), the default value (Default), and additional information (Extra).

2.2 Test SHOW CREATE TABLE statements

You can view more detailed information through the SHOW CREATE TABLE statement, in addition to querying field name, data type, and integrity constraint information, you can also view the storage engine of the table and the character encoding used.

Basic syntax: SHOW CREATE TABLE table name

Example: to view the table structure of the newly built student1, the SQL statement is shown as follows:

Thu Dec 08 01:12:27 2016localhosttest_db > SHOW CREATE TABLE student2\ gaming * 1. Row * * Table: student2Create Table: CREATE TABLE `student2` (`id`tinyint (4) NOT NULL, `name`varchar (20) DEFAULT NULL, `sex`enum ('male', 'female', 'confidential') NOT NULL, `age`tinyint (4) DEFAULT NULL PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

Note: the end of\ G can make the displayed result more intuitive.

3. How to modify the table?

After the tables in the database are established, you can modify the established tables through the SQL statement. MySQL modifies the elements in the table through the ALTER TABLE statement. For example, you can modify the table name, modify the data type of the field, modify the field name, add fields, delete fields, modify the arrangement of fields, change the default storage engine, and delete table foreign key constraints, and so on.

3.1 modify the table name

Through the table name is only determined in one database, it is not possible to have two identical data table names. Sometimes for practical purposes, we need to change the name of the data table, and it takes time and effort to re-create the same table. In this case, we can change the name of the table through the SQL statement ALTER TABLE.

Basic syntax: ALTER TABLE old table name RENAME [TO | AS] new table name

Example: we will change the table student table name to stu, the student2 table name to stu1, and the student3 table name to stu2. The SQL code is as follows:

Note: [TO | AS] is an optional parameter, which can be added or not.

3.2 modify the data type of a field

You can modify the data type of a field with the ALTER TABLE statement. The basic syntax is as follows:

ALTER TABLE table name MODIFY attribute name data type

Example: modify the data type of the name field in the stu data table to VARCHAR (30). The SQL code is as follows:

3.3 modify field names in the table

Sometimes we need to change the field name of the table, and we can change the field name of the table through the ALTER TABLE statement.

The basic syntax is as follows:

ALTER TABLE table name CHANGE old field name new field name new data attribute

Example 1: modify the field 'stu_name'' in table stu to name without changing its data type. The SQL statement is implemented as follows:

Example 2: modify the field name as well as the data type of the field. Change the field 'stu_id' to' id', data type in the stu table to TINYINT, and the constraint is still the primary key. Change 'stu_age' to' age', data type to TINYINT and integrity constraint to 'non-empty'

The field 'stu_sex'' is changed to sex data type unchanged, and the integrity constraint is changed to 'DEFAULT' confidential'. The execution code is as follows:

Thu Dec 08 21:32:00 2016localhosttest_db > DESC stu +-+-- + | Field | Type | Null | Key | Default | Extra | +-+- -+ | stu_id | int (11) | NO | PRI | 0 | name | varchar (30) | YES | | NULL | | stu_age | int (11) | YES | | NULL | | stu_sex | enum ('male') Female, confidential) | YES | | NULL | | +-+-+ 4 rows in set (0.00 sec) Thu Dec 08 21:32:12 2016localhosttest_db > ALTER TABLE stu CHANGE stu_id id TINYINT primary key ERROR 1068 (42000): Multiple primary key defined-- should note that when you modify the primary key name, you do not need to add the PRIMARY KEY keyword later, otherwise the above error message will appear. Thu Dec 08 21:33:03 2016localhosttest_db > ALTER TABLE stu CHANGE stu_id id TINYINT;Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0Thu Dec 08 21:34:35 2016localhosttest_db > ALTER TABLE stu CHANGE stu_age age TINYINT;Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0Thu Dec 08 21:35:31 2016localhosttest_db > ALTER TABLE stu CHANGE stu_sex sex ENUM ('male', 'female', 'confidential') DEFAULT 'confidential' Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0Thu Dec 08 21:37:29 2016localhosttest_db > DESC stu +-+-+ | Field | Type | Null | Key | Default | Extra | + -+ | id | tinyint (4) | NO | PRI | 0 | | name | varchar (30) | YES | | NULL | | age | tinyint (4) | YES | | NULL | | sex | enum ('male') Female, Confidential) | YES | | Secret | | +-+-+ 4 rows in set (0.00 sec)-- Last By querying the table structure, you can see that the fields in the table have been modified to the field type we need.

3.4 add fields to the table that has been built

For the already built data table, if we want to modify the table and add a new field to the table, we can use the ALTER TABLE statement to add a new field to the table. Its basic syntax is as follows:

ALTER TABLE table name ADD New field name data type [Integrity constraint] [FIRST | existing field name of AFTER]

Example 1: adding a new field Tel to the stu table does not require any integrity constraints. The SQL code is as follows:

First check the table structure Thu Dec 08 21:37:29 2016localhosttest_db > DESC stu +-+-+ | Field | Type | Null | Key | Default | Extra | + -+ | id | tinyint (4) | NO | PRI | 0 | | name | varchar (30) | YES | | NULL | | age | tinyint (4) | YES | | NULL | | sex | enum ('male') Female, confidential) | YES | | Secret | | +-+-+ 4 rows in set (0.00 sec)-- add a new field to the table: telThu Dec 08 21:37:39 2016localhosttest_db > ALTER TABLE stu ADD tel CHAR (11) Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 Murray-query the table structure, you can see that a new field telThu Dec 08 21:55:42 2016localhosttest_db > DESC stu has been added to the table stu +-+-+ | Field | Type | Null | Key | Default | Extra | + -+ | id | tinyint (4) | NO | PRI | 0 | | name | varchar (30) | YES | | NULL | | age | tinyint (4) | YES | | NULL | | sex | enum ('male') 'female', 'confidential') | YES | | Secret | tel | char (11) | YES | | NULL | | +-+-+ 5 rows in set (0.00 sec)

Example 2: add a field address to the stu table and set the integrity constraint to 'non-empty'. The SQL code is as follows:

Thu Dec 08 21:55:49 2016localhosttest_db > ALTER TABLE stu ADD address VARCHAR (20) NOT NULL;Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0Thu Dec 08 22:00:47 2016localhosttest_db > DESC stu +-+-- + | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | tinyint (4) | NO | PRI | 0 | name | varchar (30) | YES | | NULL | | age | tinyint (4) | YES | | NULL | | sex | enum ('male') 'female' ) | YES | | Secret | tel | char (11) | YES | | NULL | | address | varchar (20) | NO | | NULL | | +-+-- + -+ 6 rows in set (0.02 sec)

Test 3: add a field in the first position of the table and a num field in the stu table. The SQL code is as follows:

Thu Dec 08 22:00:53 2016localhosttest_db > ALTER TABLE stu ADD num TINYINT FIRST;Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0Thu Dec 08 22:28:24 2016localhosttest_db > DESC stu +-+-- + | Field | Type | Null | Key | Default | Extra | +-+- -+ | num | tinyint (4) | YES | | NULL | | id | tinyint (4) | NO | PRI | 0 | | name | varchar (30) | YES | | NULL | | age | | tinyint (4) | YES | | NULL | sex | enum ("male") | 'female' ) | YES | | Secret | tel | char (11) | YES | | NULL | | address | varchar (20) | NO | | NULL | | +-+-- + -+ 7 rows in set (0.00 sec)

Test 4: add a field after the specified location.

Add a new field after the tel field in the stu table: 'birthday'Thu Dec 08 22:28:38 2016localhosttest_db > ALTER TABLE stu ADD birthday DATE AFTER tel;Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0Thu Dec 08 22:34:52 2016localhosttest_db > DESC stu +-+-- + | Field | Type | Null | Key | Default | Extra | +-+- -+ | num | tinyint (4) | YES | | NULL | | id | tinyint (4) | NO | PRI | 0 | | name | varchar (30) | YES | | NULL | | | age | tinyint (4) | YES | | NULL | | sex | enum ("male") 'female' ) | YES | | Secret | tel | char (11) | YES | | NULL | | birthday | date | YES | | NULL | | address | varchar (20) | NO | | NULL | | +-+-| -+ 8 rows in set (0.01 sec)

3.5 Delete field

Deleting fields means that we can delete fields that have been defined in the table. Using ALTER TABLE, you can delete a defined field in a table. Its basic syntax is as follows:

ALTER TABLE table name DROP field name

For example, we no longer need the field num in the table stu. Now we need to delete it. The SQL code is as follows:

Thu Dec 08 22:42:31 2016localhosttest_db > ALTER TABLE stu DROP num;Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0Thu Dec 08 22:42:53 2016localhosttest_db > DESC stu +-+-- + | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | tinyint (4) | NO | PRI | 0 | | name | varchar (30) | YES | | NULL | | age | tinyint (4) | YES | | NULL | | sex | enum ('male') 'female' ) | YES | | Secret | tel | char (11) | YES | | NULL | | birthday | date | YES | | NULL | | address | varchar (20) | NO | | NULL | | +-+-| -+ 7 rows in set (0.01 sec)

3.6 modify the arrangement position of fields

You can also use the ALTER TABLE statement to modify the arrangement of fields in a table. The basic syntax is as follows:

ALTER TABLE table name MODIFY field name 1 data type FIRST | AFTER field name 2

Description: field name 1 is the field name that needs to be changed; "data type" is the data type of field 1; FIRST parameter is to change field 1 to the first position; and "AFTER field name 2" parameter is to add field 1 after field 2.

-- the test inserts the name field from the stu table into the first position, modifying the field 'sex' to the back of the field' tel' ALTER TABLE stu MODIFY name VARCHAR (30) FIRST;ALTER TABLE stu MODIFY sex ENUM ('male', 'female', 'confidential') AFTER tel;Thu Dec 08 22:43:08 2016localhosttest_db > ALTER TABLE stu MODIFY name VARCHAR (30) FIRST;Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0Thu Dec 08 22:56:00 2016localhosttest_db > DESC stu +-+-- + | Field | Type | Null | Key | Default | Extra | +-+- -+ | name | varchar (30) | YES | | NULL | | id | tinyint (4) | NO | PRI | 0 | | age | tinyint (4) | YES | | NULL | | sex | enum ('male') 'female' ) | YES | | Secret | tel | char (11) | YES | | NULL | | birthday | date | YES | | NULL | | address | varchar (20) | NO | | NULL | | +-+-| -+ 7 rows in set (0.00 sec) Thu Dec 08 22:56:12 2016localhosttest_db > ALTER TABLE stu MODIFY sex ENUM ('male' 'female', 'confidential') AFTER tel Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0Thu Dec 08 22:56:26 2016localhosttest_db > DESC stu +-+-- + | Field | Type | Null | Key | Default | Extra | +-+- -+ | name | varchar (30) | YES | | NULL | | id | tinyint (4) | NO | PRI | 0 | | age | tinyint (4) | YES | | NULL | | | tel | char (11) | YES | | NULL | | sex | enum ("male") 'female' ) | YES | | NULL | birthday | date | YES | | NULL | address | varchar (20) | NO | | NULL | | +-+ -+-+ 7 rows in set (0.02 sec)

3.7 change the storage engine of the table

There are three storage engines commonly used in MySQL: InnoDB, MyISAM, MEMORY and so on. The storage engine type of the table can be changed through the ALTER TABLE statement. Its grammatical structure is as follows:

ALTER TABLE table name ENGINE= storage engine name

For example, change the storage engine for the stu table to MyISAM,SQL code as follows:

Thu Dec 08 22:56:34 2016localhosttest_db > ALTER TABLE stu ENGINE=MyISAM Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0Thu Dec 08 23:03:35 2016localhosttest_db > SHOW CREATE TABLE stu\ Graph * 1. Row * * Table: stuCreate Table: CREATE TABLE `stu` (`name` varchar (30) DEFAULT NULL, `id`tinyint (4) NOT NULL DEFAULT'0' `age`tinyint (4) DEFAULT NULL, `tel`char (11) DEFAULT NULL, `sex`enum ('male', 'female', 'confidential') DEFAULT NULL, `day`date DEFAULT NULL, `address`varchar (20) NOT NULL, PRIMARY KEY (`id`) ENGINE=MyISAM DEFAULT CHARSET=utf81 row in set (0.05sec)

Note: if there is already a lot of data in the table, it is not recommended to change the storage engine of the table, which may have some unexpected effects if you change the storage engine.

3.8 Delete constraints on foreign keys of a table

A foreign key is a special field that associates a table with the parent table. The foreign key constraint is set when the table is created, and if you want to remove the association with the parent table, you can use the ALTER TABLE statement to delete the foreign key constraint. The basic syntax is as follows:

ALTER TABLE table name DROP FOREIGN KEY foreign key alias

-- create table grade set stu_num as table student primary key Thu Dec 08 23:36:16 2016localhosttest_db > CREATE TABLE IF NOT EXISTS grade (- > id INT PRIMARY KEY,-> stu_num INT,-> english FLOAT,-> math FLOAT,-> CONSTRAINT g_fk FOREIGN KEY (stu_num)-> REFERENCES student (id)->) Query OK, 0 rows affected (0.00 sec)-- look at the table structure of grade Thu Dec 08 23:40:53 2016localhosttest_db > SHOW CREATE TABLE grade +- - -+ | Table | Create Table | | +-+- - - -+ | grade | CREATE TABLE `grade` (`id` int (11) NOT NULL `stu_ Num` int (11) DEFAULT NULL, `english` float DEFAULT NULL, `math` float DEFAULT NULL, PRIMARY KEY (`id`), KEY `g_ fk` (`stu_ Num`) CONSTRAINT `gfk` FOREIGN KEY (`stu_ Num`) REFERENCES `student` (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-- - -+ 1 row in set (0.00 sec)-- remove the foreign key constraint Thu Dec 08 23:41:18 2016localhosttest_db > ALTER TABLE grade DROP FOREIGN KEY g_fk Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0Thu Dec 08 23:42:45 2016localhosttest_db > SHOW CREATE TABLE grade +- -+ | Table | Create Table | + - -+ | grade | CREATE TABLE `grade` (`id` int (11) NOT NULL `stu_ Num` int (11) DEFAULT NULL, `english` float DEFAULT NULL, `math` float DEFAULT NULL, PRIMARY KEY (`id`) KEY `gfk` (`stu_ Num`) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-+- - -+ 1 row in set (0.00 sec)

4. Delete tabl

When you delete a table, you delete an existing data table. Deleting a table also deletes all data in the table, so you should make the necessary preparations before deleting it. Use the DROP TABLE statement in MySQL to delete the table. For some tables, there are foreign key constraints when the table is created, and some tables become the parent of the table associated with it. To delete these parent tables, the situation is more complicated.

4.1 Delete ordinary tables that are not associated

Delete ordinary tables that are not associated with the DROP TABLE statement directly. The syntax is as follows:

DROP TABLE table name

For example, to delete the student5 table, the SQL statement is as follows:

First take a look at the table structure of student5 Thu Dec 08 23:22:02 2016localhosttest_db > DESC student5 +-+-+ | Field | Type | Null | Key | Default | Extra | + -+ | id | int (11) | NO | PRI | NULL | name | varchar (20) | NO | | NULL | | age | tinyint (4) | YES | | 20 | | sex | enum ('male') 'female', 'confidential') | YES | | male | | +-+-+ 4 rows in set (0.01sec)-- execute deletion code Thu Dec 08 23:23:46 2016localhosttest_db > DROP TABLE student5 Query OK, 0 rows affected (0.00 sec) Thu Dec 08 23:24:05 2016localhosttest_db > DESC student5;ERROR 1146 (42S02): Table 'test_db.student5' doesn't exist

4.2 Delete tables associated with foreign keys

To delete a table with a foreign key association, you first need to delete the foreign key of the table before you can delete the table structure, as shown in the SQL statement:

First create a grade table with the field stu_num as the foreign key Thu Dec 08 23:48:16 of the table student primary key 2016localhosttest_db > CREATE TABLE IF NOT EXISTS grade (- > id INT PRIMARY KEY,-> stu_num INT,-- > english FLOAT,-> math FLOAT,-> CONSTRAINT g_fk FOREIGN KEY (stu_num)-> REFERENCES student (id)->) Query OK, 0 rows affected (0.02 sec)-take a look at the table structure of grade Thu Dec 08 23:48:51 2016localhosttest_db > SHOW CREATE TABLE grade\ Graph * 1. Row * * Table: gradeCreate Table: CREATE TABLE `grade` (`id` int (11) NOT NULL, `stu_ num` int (11) DEFAULT NULL `english` float DEFAULT NULL, `math` float DEFAULT NULL, PRIMARY KEY (`id`), KEY `gfk` (`stu_ num`), CONSTRAINT `gfk` FOREIGN KEY (`stu_ num`) REFERENCES `student` (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)-- execute the delete command Delete the table student and find that the delete command Thu Dec 08 23:49:19 2016localhosttest_db > DROP TABLE student cannot be executed ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails-- executes the command to delete the foreign key constraint of table grade Thu Dec 08 23:49:39 2016localhosttest_db > ALTER TABLE grade DROP FOREIGN KEY g_fk Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0Thu Dec 08 23:50:46 2016localhosttest_db > SHOW CREATE TABLE grade\ Graph * 1. Row * * Table: gradeCreate Table: CREATE TABLE `grade` (`id`int (11) NOT NULL, `stu_ num` int (11) DEFAULT NULL, `english` float DEFAULT NULL `math` float DEFAULT NULL, PRIMARY KEY (`id`), KEY `gfk` (`math`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)-execute the delete command again Found that student table Thu Dec 08 23:50:54 2016localhosttest_db > DROP TABLE student was deleted successfully Query OK, 0 rows affected (0.02 sec) Thu Dec 08 23:51:13 2016localhosttest_db > SHOW TABLES;+-+ | Tables_in_test_db | +-+ | animalinfo | | grade | stu | | teacher | +-+ 4 rows in set (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.