How to create a table in mysql

Mysql method of creating a table: first specify the name of the table to be created after the CREATE TABLE clause; then specify the list of tables in the [column_list] section; finally, you need to specify the storage engine for the table in the engine clause .

The operating environment of this tutorial: Windows 7 system, mysql version 8.0.22, DELL G3 computer.

Mysql method of creating a table:

The syntax of the CREATE TABLE statement is explained in simple form:

CREATE TABLE [IF NOT EXISTS] table_name(
        column_list
) engine=table_type;

• First, specify the name of the table to be created after the CREATE TABLE clause. Table names must be unique within the database. IF NOT EXISTS is an optional part of the statement that allows you to check whether the table being created already exists in the database. If this is the case, MySQL will ignore the entire statement and will not create any new tables. It is strongly recommended to use IF NOT EXISTS in every CREATE TABLE statement to prevent errors from creating a new table that already exists.

• Secondly, specify the list of tables in the column_list section. Columns of fields are separated by commas (,). We'll show you how to define columns (fields) in more detail in the next section.

• Third, you need to specify the storage engine for the table in the engine clause. Any storage engine can be used such as: InnoDB, MyISAM, HEAP, EXAMPLE, CSV, ARCHIVE, MERGE, FEDERATED or NDBCLUSTER. If you do not explicitly declare a storage engine, MySQL will use InnoDB by default.

Note: InnoDB has become the default storage engine since MySQL 5.5. The InnoDB table type brings many benefits of relational database management systems such as ACID transactions, referential integrity, and crash recovery. In previous versions, MySQL used MyISAM as the default storage engine.

To define columns for a table in a CREATE TABLE statement, use the following syntax:

column_name data_type[size] [NOT NULL|NULL] [DEFAULT value] 
[AUTO_INCREMENT]

The most important component of the above syntax is:

• column_nameSpecifies the name of the column. Each column has a specific data type and size, for example: VARCHAR(255).

• NOT NULL or NULL indicates whether the column accepts NULL values. The

• DEFAULT value is used to specify the default value for the column.

• AUTO_INCREMENTIndicates that the column's value is automatically incremented whenever a new row is inserted into the table. Each table has one and only one AUTO_INCREMENT column.

For example:

CREATE TABLE IF NOT EXISTS tasks (
  task_id INT(11) NOT NULL AUTO_INCREMENT,
  subject VARCHAR(45) DEFAULT NULL,
  start_date DATE DEFAULT NULL,
  end_date DATE DEFAULT NULL,
  description VARCHAR(200) DEFAULT NULL,
  PRIMARY KEY (task_id)
) ENGINE=InnoDB;

Related free learning recommendations: mysql video tutorial

The above is the detailed content of How to create a table in mysql. For more information, please follow other related articles on the PHP Chinese website!