How to create a data table using MySQL

MySQL is a widely used open source relational database management system. Creating a table is a basic operation of MySQL. In this article, we will learn how to create a data table using MySQL.

1. Basic syntax for creating a table

To create a MySQL table, you need to use the following basic syntax:

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  column3 datatype,
  .....
);

Where, table_nameSpecifies the name of the table to be created. column1, column2, etc. are the column names in the table, and datatype is the data type of the column.

For example, the following is an example of creating a table named students, which contains id, name, age and genderFour columns:

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  gender VARCHAR(10)
);

2. Add the primary key when creating the table

The primary key is the column used to uniquely identify each row of data in the table. In MySQL, auto-increment columns are usually used as primary keys.

To add a primary key to a column, you need to add PRIMARY KEY after the column name.

For example, the following is an example of creating a table named students, which contains an auto-increment column id and a name column as the primary key:

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

3. Set reference relationships for foreign keys when creating tables

In MySQL, foreign keys are used to establish relationships between tables. A foreign key is a column in a table that points to a primary key in the main table.

To add a foreign key to a column, you specify the name of the primary table and the primary key column in the primary table using the REFERENCES keyword in the column definition. For example:

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

In the above example, the orders table contains a customer_id column, which points to the customer_id in the customers table List. This is useful when querying, as you can retrieve all the information associated with the customers table from the orders table.

4. Set default values ​​when creating a table

In MySQL, you can set default values ​​for columns. When new data is inserted, if a value for the column is not specified, the default value is used.

To set a default value for a column, you can use the DEFAULT keyword in the column definition. For example:

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT DEFAULT 0,
  order_date DATE DEFAULT '2022-01-01'
);

In the above example, the default value of the customer_id column is 0, and the default value of the order_date column is ' 2022-01-01'. When new data is inserted, if values ​​for these columns are not specified, the default values ​​are used.

5. Add constraints to columns when creating a table

Constraints in MySQL are used to ensure data integrity in the table. Common constraints include NOT NULL, UNIQUE, CHECK, PRIMARY KEY, and FOREIGN KEY, etc.

To add a constraint to a column, you need to use constraint syntax between data_type and the column name. For example:

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  employee_name VARCHAR(50) NOT NULL,
  email VARCHAR(50) UNIQUE,
  age INT CHECK (age >= 18),
  manager_id INT,
  FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

In the above example, the employee_name column is a NOT NULL column, and the email column is a UNIQUEColumn, the age column contains a CHECK constraint that requires the value in the age column to be greater than or equal to 18. The manager_id column is a foreign key that points to the primary key column employee_id in the employees table.

Conclusion

In this article, we introduced how to create tables using MySQL queries. You can create a table following the basic syntax above, or add additional options or constraints to implement a more customized table. The created database tables can facilitate data storage, management and query, making your database operations more efficient, accurate and stable.

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