How to create mysql table

MySQL is a popular relational database management system that is the primary choice for data storage and processing for many applications. When using MySQL, table creation is a very important skill that must be mastered. This article will introduce how to create tables in MySQL.

1. Prerequisites
   Before creating a MySQL table, you must install and configure the MySQL server, and be familiar with using MySQL client tools (such as the command line or MySQL Workbench). Additionally, sufficient permissions are required to create the table. If the operation is performed locally, you can log in as the root user; if the operation is performed on a remote server, corresponding permissions are required.
2. Select database
   Before creating the table, you need to select the target database where you want to create the table. If a database already exists, you can switch to it using the following command:

use database_name;

where database_name is the name of the target database.

If the target database does not yet exist, you need to use the following command to create the database and set it as the current database:

CREATE DATABASE database_name;
USE database_name;

3. Create table
   We can use the CREATE TABLE statement to Create a new MySQL table. Typically, the CREATE TABLE statement contains the following components:

• Table name: The name of the new table.
• Column name and data type: Each column to be created in the table and the data type of each column.
• Primary key: Every table should have a primary key to uniquely identify each row of data.
• Constraints: used to define restrictions and requirements for specific columns.
• Other options: such as partitioning and indexing.

The following is an example of a basic MySQL table creation statement:

CREATE TABLE table_name (
    column_1 datatype constraint,
    column_2 datatype constraint,
    PRIMARY KEY (column_1)
);

Where table_name is the name of the new table, column_1 and column_2 are the column names to be created, and datatype is the column The data type, while constraint is the restriction or requirement of the column. PRIMARY KEY is used to specify the primary key of the table, which should be the column that uniquely identifies each row of data.

For example, the following is an example create statement for a MySQL table named students:

CREATE TABLE students (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    age INT,
    gender ENUM('male','female') NOT NULL,
    PRIMARY KEY (id)
);

In this example, students is the name of the table, id is an auto-increment integer column, and Designated as primary key. name is a 255-character text column, age is an integer column, and gender is an enumeration column that can only contain the two values ​​​​of 'male' or 'female'.

4. Add Column Constraints
   When creating a column, you can use a variety of constraints to limit or require the value of the column, including the following:

• NOT NULL: Column cannot be empty.
• UNIQUE: All values ​​of the column must be unique.
• DEFAULT: Set the default value for the column.
• CHECK: Specify certain restrictions, such as limiting the range of values ​​under specific conditions.
• PRIMARY KEY: Specify this column as the primary key of the table.
• FOREIGN KEY: Specifies that this column is associated with a foreign key to another table.

Here are some examples that demonstrate how to use these constraints:

• Null values ​​and uniqueness are prohibited:

CREATE TABLE employees (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    age INT NOT NULL,
    PRIMARY KEY (id)
);

In this example , the email column must be unique, so use the UNIQUE constraint.

• Add default value:

CREATE TABLE customers (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    address TEXT,
    created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

In this example, the created_date column has a default value set to the current timestamp, so there is no need to manually specify it every time a new record is created. The value of this column.

• Check the range of values:

CREATE TABLE orders (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    product_name VARCHAR(255) NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    status ENUM('pending','processing','shipped') DEFAULT 'pending',
    CONSTRAINT ck_price CHECK (price > 0),
    PRIMARY KEY (id)
);

In this example, the price column limits the range of values ​​through CHECK constraints, requiring the column value to be greater than 0. The default value of the status column is 'pending', so there is no need to manually specify the value of this column when creating a new record.

• Add foreign key:

CREATE TABLE orders (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id INT UNSIGNED NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

In this example, the customer_id column is associated with the id column of the customer table, so the FOREIGN KEY constraint is used to create the foreign key. This means that the value of the customer_id column must be consistent with the value of the id column in the customer table.

5. Summary
   Creating tables in MySQL is a necessary skill. This article introduces how to use the CREATE TABLE statement and various column constraints to create MySQL tables. There are many other options and constraints that require continued learning and exploration in practice.

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