In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-04-01 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
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.
Thu Dec 08 00:58:40 2016localhosttest_db > DESCRIBE student +-+-- + | Field | Type | Null | Key | Default | Extra | +-+- -+ | stu_id | int (11) | NO | PRI | NULL | stu_name | varchar (20) | YES | | NULL | | stu_age | int (11) | YES | | NULL | | | stu_sex | enum ("male") 'female', 'confidential') | YES | | NULL | | +-+-+ 4 rows in set (0.02 sec)
DESCRIBE can be abbreviated to DESC, and the code runs as follows:
Thu Dec 08 00:59:02 2016localhosttest_db > DESC student +-+-- + | Field | Type | Null | Key | Default | Extra | +-+- -+ | stu_id | int (11) | NO | PRI | NULL | stu_name | varchar (20) | YES | | NULL | | stu_age | int (11) | YES | | NULL | | | stu_sex | enum ("male") 'female', 'confidential') | YES | | NULL | | +-+-+ 4 rows in set (0.01 sec)
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:
First check which tables are in the library Thu Dec 08 01:11:32 2016localhosttest_db > SHOW TABLES +-+ | Tables_in_test_db | +-+ | grade | | student | | student2 | | student3 | | student4 | student5 | | teacher | +-+ 7 rows in set (0) .00 sec)-execute SQL code ALTER TABLE student RENAME TO stu ALTER TABLE student2 RENAME AS stu1;ALTER TABLE student3 RENAME stu2;-- uses the SHOW TABLES statement to check whether the change is successful Thu Dec 08 01:33:59 2016localhosttest_db > SHOW TABLES +-+ | Tables_in_test_db | +-+ | grade | | stu | | stu1 | | stu2 | | student4 | | student5 | | teacher | +-+ 7 rows in set (0.00 sec)
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:
-- first take a look at stu_name 's existing data types Thu Dec 08 01:34:09 2016localhosttest_db > DESC stu +-+-- + | Field | Type | Null | Key | Default | Extra | +-+- -+ | stu_id | int (11) | NO | PRI | NULL | stu_name | varchar (20) | YES | | NULL | | stu_age | int (11) | YES | | NULL | | | stu_sex | enum ("male") Female, confidential) | YES | | NULL | | +-+-+ 4 rows in set (0.02 sec)-- execute SQL code Change the data type of stu_name to VARCHAR (30) Thu Dec 08 01:44:53 2016localhosttest_db > ALTER TABLE stu MODIFY stu_name VARCHAR (30) Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 Murray-query the data type of the modified stu_name Thu Dec 08 01:45:55 2016localhosttest_db > DESC stu +-+-- + | Field | Type | Null | Key | Default | Extra | +-+- -+ | stu_id | int (11) | NO | PRI | NULL | stu_name | varchar (30) | YES | | NULL | | stu_age | int (11) | YES | | NULL | | | stu_sex | enum ("male") 'female', 'confidential') | YES | | NULL | | +-+-+ 4 rows in set (0.02 sec)
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:
First take a look at the table structure of stu Thu Dec 08 20:54:26 2016localhosttest_db > DESC stu +-+-- + | Field | Type | Null | Key | Default | Extra | +-+- -+ | stu_id | int (11) | NO | PRI | NULL | stu_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)-- the name of the field modified by executing the ALTER TABLE statement Thu Dec 08 20:54:40 2016 localhost test _ Db > ALTER TABLE stu CHANGE stu_name name VARCHAR (30) Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0Thu Dec 08 20:56:56 2016localhosttest_db > DESC stu +-+-- + | Field | Type | Null | Key | Default | Extra | +-+- -+ | stu_id | int (11) | NO | PRI | NULL | name | varchar (30) | YES | | NULL | | stu_age | int (11) | YES | | NULL | | stu_sex | enum ('male') 'female', 'confidential') | YES | | NULL | | +-+-+ 4 rows in set (0.02 sec)
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.
Views: 0
*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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.