Third, the basic operation of the data table 02/24 Update SLTechnology News&Howtos

Third, the basic operation of the data table

2025-02-24 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

3.1. Create a data table

Creating a datasheet refers to creating a new table in a database that has already been created. The process of creating a data table is not only the process of specifying the attributes of the data column, but also the process of implementing data integrity (entity integrity, referential integrity, domain integrity) constraints.

Create a grammatical format for the table

The data table belongs to the database, and before you create the data table, you should create the database and specify which database to do it in.

Mysql > USE test;Database changedmysql > CREATE TABLE tb_name (field name 1, data type [column level constraint] [default], field name 2, data type [column level constraint] [default],... [table level constraints])

For example, create the employee table tb_emp1, which has the following structure:

Field name data type remarks idINT (11) employee number nameVARCHAR (25) employee name deptIdINT (11) Department number salaryFLOAT salary mysql > USE test;Database changedmysql > CREATE TABLE tb_emp1-> (- > idINT (11),-> nameVARCHAR (25),-> depId INT (11),-> salaryFLOAT->); Query OK, 0 rows affected (0.08 sec) mysql > SHOW TABLES +-+ | Tables_in_test | +-+ | tb_emp1 | +-+ 1 row in set (0.00 sec)

Use primary key constraint

A primary key, also known as a primary code, is a combination of one or more columns in a table. The primary key constraint requires that the data of the primary key column is unique and is not allowed to be empty. The primary key can uniquely identify a record in the table, can be combined with foreign keys to define the relationship between different data tables, and can speed up the speed of data query. The primary key is divided into single-field primary key and multi-field joint primary key.

A single field primary key consists of a field with two grammatical formats:

Field name data type PRIMARY KEY [default] mysql > CREATE TABLE tb_emp2-> (- > id INT (11) PRIMARY KEY,-> name VARCHAR (25),-> depId INT (11),-> salary FLOAT->) [CONSTRAINT] PRIMARY KEY [field name] mysql > CREATE TABLE tb_emp3-> (- > id INT (11),-> name VARCHAR (25),-> depId INT (11),-> salary FLOAT,-> PRIMARY KEY (id)->)

Multi-field federated primary key, that is, the primary key is composed of multiple fields, and its syntax format is:

PRIMARY KEY [Field 1, Field 2, Field 3 mysql > CREATE TABLE tb_emp4-> (- > name VARCHAR (25),-> depId INT (11),-> salary FLOAT-> PRIMARY KEY (name,depId)->)

Use foreign key constraints

A foreign key is used to establish a link between the data of two tables, which can be one or more columns. A table can have one or more foreign keys. A foreign key corresponds to referential integrity. The foreign key of a table can be empty, and when not empty, each foreign key value must be equal to a value of the primary key in another table.

Foreign key: it is a field in a table, which may not be the primary key of this table, but corresponds to the primary key of another table. The main function of a foreign key is to ensure the integrity of the data reference. after defining a foreign key, it is not allowed to delete rows that have an associated relationship in another table. For example, the primary key of the department table tb_dept is id, and there is a key depId associated with this id in the employee table tb_emp5.

Primary table: for two tables that have an associated relationship, the table with the primary key in the associated field is the primary table.

Slave table: for two tables that have an associated relationship, the table where the foreign key in the associated field is located is the slave table.

Create syntax rules for foreign keys:

[CONSTRAINT] FOREIGN KEY field name 1 [, field name 2.] REFERENCES primary key column 1 [, primary key column 2.]

The 'foreign key name' is the name of the defined foreign key constraint, and there cannot be the same foreign key in a table; the 'field name' represents the field column on which the foreign key constraint needs to be added to the word table; the 'primary table name' is the name of the table on which the quilt table foreign key depends; and the 'primary key column' represents the primary key column defined in the primary table.

For example: define the datasheet tb_emp5 and create a foreign key constraint on tb_emp5.

Create a department table:

Mysql > CREATE TABLE tb_dept1 (- > id INT (11) PRIMARY KEY,-> name VARCHAR (22) NOT NULL,-> location VARCHAR (50)->); Query OK, 0 rows affected (0.02 sec)

Define the data table tb_emp5 and associate its key depId as a foreign key to the primary key id of tb_dept1:

Mysql > CREATE TABLE tb_emp5 (- > id INT (11) PRIMARY KEY,-> name VARCHAR (25),-> deptId INT (11),-> salary FLOAT,-> CONSTRAINT fk_emp_dept1 FOREIGN KEY (deptId) REFERENCES tb_dept1 (id)->); Query OK, 0 rows affected (0.05 sec)

Use a non-null constraint

A non-null constraint means that the value of a field cannot be empty. For fields that use non-null constraints, the database reports an error if no value is specified when adding data.

Syntax rules for non-null constraints:

Field name data type NOT NULLLmysql > CREATE TABLE tb_emp6 (- > id INT (11) PRIMARY KEY,-> name VARCHAR (25) NOT NULL,-> depId INT (11),-> salary FLOAT->); Query OK, 0 rows affected (0.03 sec)

Use uniqueness constraint

The uniqueness constraint requires that the column is unique and is allowed to be empty, but only one null value can occur. Uniqueness constraints ensure that one or more columns do not have duplicate values.

The grammatical rules for uniqueness constraints are:

1. Directly specify the unique constraint of the defined column

Field name data type UNIQUEmysql > CREATE TABLE tb_dept2 (- > id INT (11) PRIMARY KEY,-> name VARCHAR (22) UNIQUE,-> location VARCHAR (50)->); Query OK, 0 rows affected (0.20 sec)

2. Specify unique constraints after all columns have been defined

[CONSTRAINT] UNIQUE () mysql > CREATE TABLE tb_dept3 (- > id INT (11) PRIMARY KEY,-> name VARCHAR (22),-> location VARCHAR (50),-> CONSTRAINT STH UNIQUE (name)->); Query OK, 0 rows affected (0.03 sec)

The difference between UNIQUE and PRIMARY KEY: a table can have multiple fields declared as UNIQUE, but only one PRIMARY KEY declaration. Columns declared as PRIMARY KEY do not allow null values, but fields declared as UNIQUE allow null values (null values).

Use default constraint

The default constraint refers to the default value of a column. Its grammatical rules are:

Field name data type DEFAULT defaults mysql > CREATE TABLE tb_emp7 (- > id INT (11) PRIMARY KEY,-> name VARCHAR (25) NOT NULL,-> depId INT (11) DEFAULT 1111,-> salary FLOAT->); Query OK, 0 rows affected (0.03 sec)

Set the property value of the table to increase automatically

In database applications, it is often expected that the system will automatically generate the primary key value of the field each time a new record is inserted. This can be done by adding the AUTO_INCREMENT keyword to the table primary key. Its grammatical format is:

Field name data type AUTO_INCREMENTmysql > CREATE TABLE tb_emp8 (- > id INT (11) PRIMARY KEY AUTO_INCREMENT,-> name VARCHAR (25) NOT NULL,-> depId INT (11),-> salary FLOAT->); Query OK, 0 rows affected (0.03 sec) mysql > INSERT INTO tb_emp8 (name,salary)-> VALUES ('Lucy',1000), (' Lura',2000), ('Kevin',3000) Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0mysql > SELECT * FROM tb_emp8 +-+ | id | name | deptId | salary | +-+ | 1 | Lucy | NULL | 1000 | 2 | Lura | NULL | 2000 | | 3 | Kevin | NULL | 3000 | +-+-- -+-+ 3 rows in set (0.00 sec)

View data table structure

After you have created the data table using the SQL statement, you can look at the definition of the table structure to determine whether the definition of the table is correct. In MySQL, you can use DESCRIBE and SHOW CREATE TABLE statements to view the table structure.

3.2. Modify the data table

Modifying a data table refers to modifying the structure of a data table that already exists in the database. MySQL uses the ALTER TABLE statement to modify the table. The commonly used operations to modify the table are: modify the table name, modify the field data type or field name, add and delete fields, modify the order of fields, change the storage engine of the table, delete the foreign key constraints of the table, and so on.

Modify table name

The syntax rule for modifying the table name is:

Modify the data type of a field

To modify the data type of a field is to convert the data type of the field to another data type. The syntax rules are as follows:

Modify field name

The syntax rules for modifying table field names in MySQL are:

Add a field

The syntax format for adding a field is:

ALTER TABLE ADD [constraint] [FIRST | Field name already exists in AFTER]

Add fields without complete constraints

Mysql > ALTER TABLE tb_dept1 ADD managerId INT (10); Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > DESC tb_dept1 +-+ | Field | Type | Null | Key | Default | Extra | +- -+-+ | id | int (11) | NO | PRI | NULL | name | varchar (33) | YES | | NULL | | loc | varchar (50) | YES | | NULL | | managerId | int (10) | YES | NULL | | +-+-| -+ 4 rows in set (0.00 sec)

Add fields with complete constraints

Add a field to the first column of the table

Mysql > ALTER TABLE tb_dept1 ADD column2 INT (11) FIRST; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > DESC tb_dept1 +-+ | Field | Type | Null | Key | Default | Extra | +- -+-+ | column2 | int (11) | YES | | NULL | | id | int (11) | NO | PRI | NULL | | name | varchar (33) | YES | | NULL | | loc | varchar (50) | YES | NULL | | managerId | int (10) | YES | NULL | | | | column1 | varchar (12) | NO | | NULL | | +-+-+ 6 rows in set (0.00 sec) |

Add a field after the specified column of the table

Mysql > ALTER TABLE tb_dept1 ADD column3 INT (11) AFTER name;Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > DESC tb_dept1 +-+ | Field | Type | Null | Key | Default | Extra | +- -+-+ | column2 | int (11) | YES | | NULL | | id | int (11) | NO | PRI | NULL | | name | varchar (33) | YES | | NULL | | column3 | int (11) | YES | NULL | | loc | varchar (50) | YES | NULL | | | | managerId | int (10) | YES | | NULL | column1 | varchar (12) | NO | | NULL | | +-+-+ 7 rows in set (0.00 sec) |

Delete a field

To delete a field is to remove a field from the data table in the syntax format:

ALTER TABLE DROP mysql > DESC tb_dept1 +-+ | Field | Type | Null | Key | Default | Extra | +- -+-+ | column2 | int (11) | YES | | NULL | | id | int (11) | NO | PRI | NULL | | name | varchar (33) | YES | | NULL | | column3 | int (11) | YES | NULL | | loc | varchar (50) | YES | NULL | | | | managerId | int (10) | YES | | NULL | column1 | varchar (12) | NO | | NULL | | +-+-+ 7 rows in set (0.00 sec) mysql > ALTER TABLE tb_dept1 DROP column2 | Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > DESC tb_dept1 +-+ | Field | Type | Null | Key | Default | Extra | +-- -+ | id | int (11) | NO | PRI | NULL | name | varchar (33) | YES | | NULL | | column3 | int (11) | YES | | NULL | | loc | varchar (50) | YES | | NULL | | managerId | int (10) | YES | | NULL | column1 | varchar (12) | NO | | NULL | | +-+-+ 6 rows in set (0.00 sec)

Modify the arrangement position of the field

For a data table, the order of the fields in the table has been determined at the time of creation. However, the structure of the table is not completely unchangeable. You can change the relative position of the fields in the table through ALTER TABLE. The syntax format is as follows:

ALTER TABLE MODIFY FIRST | AFTER

Modify the field to the first field of the table

Mysql > ALTER TABLE tb_dept1 MODIFY column1 VARCHAR (12) FIRST;Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > DESC tb_dept1 +-+ | Field | Type | Null | Key | Default | Extra | +- -+-+ | column1 | varchar (12) | YES | | NULL | | id | int (11) | NO | PRI | NULL | | name | varchar (33) | YES | | NULL | | column3 | int (11) | YES | NULL | | loc | varchar (50) | YES | NULL | | | | managerId | int (10) | YES | | NULL | | +-+-+ 6 rows in set (0.00 sec) |

After modifying the field to the specified column of the table

Mysql > ALTER TABLE tb_dept1 MODIFY column1 VARCHAR (12) AFTER loc;Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > DESC tb_dept1 +-+ | Field | Type | Null | Key | Default | Extra | +- -+-+ | id | int (11) | NO | PRI | NULL | name | varchar (33) | YES | | NULL | | column3 | int (11) | YES | | NULL | | loc | varchar (50) | YES | NULL | | column1 | varchar (12) | YES | NULL | | | | managerId | int (10) | YES | | NULL | | +-+-+ 6 rows in set (0.00 sec) |

Change the storage engine of the table

The data engine syntax format for changing the table is:

ALTER TABLE ENGINE=mysql > SHOW CREATE TABLE tb_deptment3\ gateway * 1. Row * * Table: tb_deptment3Create Table: CREATE TABLE `tb_ deptment3` (`id` int (11) NOT NULL, `name` varchar (22) DEFAULT NULL, `location` varchar (50) DEFAULT NULL, PRIMARY KEY (`id`) UNIQUE KEY `STH` (`name`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) mysql > ALTER TABLE tb_deptment3 ENGINE=MyISAM Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > SHOW CREATE TABLE tb_deptment3\ G * * 1. Row * * Table: tb_deptment3Create Table: CREATE TABLE `tb_ deptment3` (`id` int (11) NOT NULL, `name` varchar (22) DEFAULT NULL, `location` varchar (50) DEFAULT NULL PRIMARY KEY (`id`), UNIQUE KEY `STH` (`name`) ENGINE=MyISAM DEFAULT CHARSET=utf81 row in set (0.00 sec) mysql >

Delete the foreign key constraint of the table

Foreign keys defined in the database can be deleted if they are no longer needed. Once the foreign key is deleted, the association between the master table and the slave table will be disassociated. The syntax format for deleting a foreign key in MySQL is:

ALTER TABLE FROP FOREIGN KEY mysql > CREATE TABLE tb_emp9 # create a table with foreign key constraints-> (- > id INT (11) PRIMARY KEY,-> name VARCHAR (25),-> deptId INT (11),-> salary FLOAT,-> CONSTRAINT fk_emp_dept FOREIGN KEY (deptId) REFERENCES tb_dept1 (id)->) Query OK, 0 rows affected (0.03 sec) mysql > SHOW CREATE TABLE tb_emp9\ G * * 1. Row * * Table: tb_emp9Create Table: CREATE TABLE `tb_ emp9` (`id`int (11) NOT NULL, `name` varchar (25) DEFAULT NULL, `deptId` int (11) DEFAULT NULL, `salary` float DEFAULT NULL PRIMARY KEY (`id`), KEY `fk_emp_ dept` (`deptId`), CONSTRAINT `fk_emp_ dept` FOREIGN KEY (`deptId`) REFERENCES `tb_ dept1` (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) mysql > ALTER TABLE tb_emp9 DROP FOREIGN KEY fk_emp_dept Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > SHOW CREATE TABLE tb_emp9\ G * * 1.row * * Table: tb_emp9Create Table: CREATE TABLE `tb_ emp9` (`id` int (11) NOT NULL, `name` varchar (25) DEFAULT NULL `deptId` int (11) DEFAULT NULL, `salary` float DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_emp_ dept` (`deptId`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

3.3. Delete the data table

Delete tables that are not associated

In MySQL, you can use DROP TABLE to delete one or more data tables that are not associated with other tables at a time, in the syntax format:

Delete master tables associated with other tables

If there is a foreign key association between the data tables, if the parent table is deleted directly, the result will show a failure. The reason is that direct deletion will destroy the referential integrity of the table. If you must delete, you can delete the foreign key constraint first, and then delete the parent table.

Mysql > CREATE TABLE tb_dept2 (id INT (11) PRIMARY KEY, name VARCHAR (25), location VARCHAR (50)); # create parent table Query OK, 0 rows affected (0.03 sec) mysql > CREATE TABLE tb_emp (id INT (11) PRIMARY KEY, name VARCHAR (25), deptId INT (11), salary FLOAT, CONSTRAINT fk_emp_dept FOREIGN KEY (deptId) REFERENCES tb_dept2 (id)) # create child table, foreign key constraint Query OK, 0 rows affected (0.03 sec) mysql > DROP TABLE tb_dept2; # cannot delete parent table ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint failsmysql > ALTER TABLE tb_emp DROP FOREIGN KEY fk_emp_dept; # Delete child table foreign key Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > DROP TABLE tb_dept2 # deleted parent table Query OK successfully, 0 rows affected (0.02 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.