1. The background

This article covers the basic operations of tables.

2. Knowledge

In the database, the data table is the basic operation object, is the basic unit of data storage. A data table is defined as a collection of columns and is stored in a row and column format. Each row represents a unique data record, and each column represents an attribute of an object in the record.

Example 3.

(1) the new table

CREATE TABLE tb_table1
(
  id INT(11),
  name VARCHAR(25),
  deptId INT(11),
  salary FLOAT
);
Copy the code

(2) Check the existing tables

show tables;
Copy the code

(3) Primary key constraint, foreign key constraint, non-null constraint, unique constraint, default constraint

  • A primary key uniquely identifies a record in a table, like an ID card. It can be a single-field primary key or a multi-field joint primary key.
  • Foreign keys are used to establish a join between data in two tables. It typically corresponds to the primary key of another table. The purpose of foreign keys is to ensure the integrity of data references. A table’s foreign key can be null, or if not, it must be the value of the primary key in a table.
  • Non-null constraint: Use NOT NULL to specify that the value of a field cannot be NULL
  • The uniqueness constraint is used to state that the value of this column must be unique and can be null but cannot be repeated.
  • The difference between a primary key constraint and a unique constraint: A table can have only one primary key, and can have multiple unique keys. Primary keys cannot have null values, while unique keys can have null values.
  • A default constraint is used to specify the default value of a column. For example, when a numeric column is inserted, 0 is inserted into the column without specifying the value.
  • AUTO_INCREMENT: Uses the AUTO_INCREMENT keyword to declare self-increment columns. Each time a new record is added, the value of the column is automatically increased by 1. A table can have only one increment column.

Example:

CREATE TABLE tb_table2
(
  id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(25) NULL UNIQUE,
  deptId INT(11) NULL DEFAULT 0,
  salary FLOAT
);
Copy the code

Use DESCRIBE or DESC to DESCRIBE the table columns.

DESCRIBE tb_table3;
或者：
DESC tb_table3;
Copy the code

The SHOW CREATE TABLE statement can be used to display the SQL statements when the TABLE is created. Example:

show create table tb_table1;
或者： 
show create table tb_table1 \G;
Copy the code

To modify a TABLE, use the ALTER TABLE statement to modify the TABLE structure in the database. Common operations include:

  • Modify the name of the table
  • Modify the field type or field name
  • Add and remove fields
  • Change the field position
  • Change the storage engine for the table
  • Delete foreign key constraints

Example:

ALTER TABLE tb_table1 RENAME tb_table3; ALTER TABLE tb_table3 MODIFY name varchar(50); ALTER TABLE tb_table3 CHANGE deptId dept int; ALTER TABLE tb_table3 ADD shotName varchar(50); ALTER TABLE tb_table3 ADD nicktName varchar(50) AFTER name; ALTER TABLE tb_table3 DROP shotName; ALTER TABLE tb_table3 ENGINE=MyISAM;Copy the code

Use DROP TABLE to DROP one or more tables.

DROP TABLE tb_table3;
Copy the code

END