mysql how to create a table

MySQL is a commonly used relational database management system used to store and manage various types of data. Creating a table is one of the most basic tasks in MySQL and in this article we will learn how to create a table using MySQL.

Create database

Before creating a table, you first need to create a database. In MySQL, you can use the following command to create a database:

CREATE DATABASE databasename;

where databasename is the name of the database you want to create. For example, to create a database named mydatabase, you can execute the following command:

CREATE DATABASE mydatabase;

Create table

When creating a table, you need to first select which database to create the table in. You can use the following command to select a database:

USE databasename;

Where databasename is the name of the database you want to select. For example, if you want to create a table in the mydatabase database, you can execute the following command:

USE mydatabase;

Next, you can use the following command to create a table:

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

Where, tablename is the value you want to create The name of the table. column1, column2 and column3 are the column names in the table, and datatype is the data type of the corresponding column. Each column in the table needs to be assigned a name and corresponding data type. If the table you are creating has multiple columns, you can list them one after another in the same format.

The following is an example showing how to create a table named customers that contains three columns: name, address and phone:

CREATE TABLE customers
(
name VARCHAR(50),
address VARCHAR(100),
phone VARCHAR(20)
);

In the above example, each column Both use the VARCHAR data type, with lengths of 50, 100 and 20 characters respectively. These three columns store the customer's name, address, and phone number.

Add data

After creating the table, you can use the following command to add data to the table:

INSERT INTO tablename
VALUES (value1, value2, value3, ...);

Where, tablename is the name of the table to insert data, value1, value2 and value3 is the value to be inserted into the corresponding column in the table.

The following is an example showing how to add data to the customers table:

INSERT INTO customers
VALUES ('John Doe', '123 Main St, Anytown USA', '555-1234');
INSERT INTO customers
VALUES ('Jane Smith', '456 Oak St, Anytown USA', '555-5678');

In the above example, we added two rows of data to the customers table, each row of data includes the customer's Name, address and phone number.

Query data

After adding data, you can use the following command to query the data in the table:

SELECT * FROM tablename;

Where, tablename is the name of the table to be queried. Use the * wildcard character to represent all columns in the query table.

The following is an example that shows how to query the data in the customers table:

SELECT * FROM customers;

In the above example, executing the query operation will display all column and row data in the customers table.

Changing tables

After creating a table, you may need to change the table structure, such as adding new columns, deleting columns, or changing data types. You can use the following command to change the table:

1. Add a new column:

ALTER TABLE tablename
ADD COLUMN columnname datatype;

where columnname is the name of the new column to be added, and datatype is the data type of the new column.

The following is an example showing how to add a new email column to the customers table:

ALTER TABLE customers
ADD COLUMN email VARCHAR(50);

In the above example, we have added a new column email to the customers table with its data Type is VARCHAR, length is 50 characters.

2. Delete a column:

ALTER TABLE tablename
DROP COLUMN columnname;

Among them, columnname is the name of the column to be deleted.

The following is an example showing how to delete the phone column from the customers table:

ALTER TABLE customers
DROP COLUMN phone;

In the above example, we have deleted the phone column from the customers table.

3. Change the data type:

ALTER TABLE tablename
MODIFY COLUMN columnname datatype;

Among them, columnname is the name of the column whose data type is to be changed, and datatype is the new column data type.

The following is an example showing how to change the address column of the customers table to CHAR data type:

ALTER TABLE customers
MODIFY COLUMN address CHAR(100);

In the above example, we change the data type of the address column of the customers table to CHAR , 100 characters in length.

Summary

Creating tables in MySQL is a basic database task. You need to select a database and create the table using the CREATE TABLE command. After creating a table, you can use the INSERT INTO command to add data to the table and the SELECT command to query the data in the table. If you need to change the table structure, you can use the ALTER TABLE command to add, delete, or change columns in the table.

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