How to query and create tables in MySQL

MySQL is one of the most popular relational database systems and is widely used in website development, data analysis and other fields. In MySQL, query is one of the most basic operations, and table creation is to create the table structure in the database. This article will introduce how to query and create tables in MySQL.

1. Query

In MySQL, the query uses the SELECT statement. The SELECT statement has many different options that allow you to retrieve data based on established conditions, as shown below:

SELECT column_name FROM table_name WHERE condition;

Where column_name is the column name to be retrieved, table_name is the name of the table to be queried, and condition is the query condition to be satisfied. For example, to query the last_name, first_name, salary FROM employees;

After executing the query, MySQL will return a table containing the query results. If you want to delete duplicate records from the query results, you can use the DISTINCT keyword, as shown below:

SELECT DISTINCT last_name, first_name FROM employees;

In the query, you can also use ORDER BY Sort the returned results, for example, in alphabetical order by last name:

SELECT last_name, first_name FROM employees ORDER BY last_name;

You can also use the LIKE operator to use wildcards in the query. For example, to query all employees whose last name starts with S, you can use the following statement:

SELECT last_name, first_name FROM employees WHERE last_name LIKE 'S%';

% in the above statement is a wildcard character , meaning to match any number of characters.

2. Create a table

In MySQL, use the CREATE TABLE statement to create a table. The CREATE TABLE statement needs to specify the table name and the columns, data types and constraints contained in the table, for example:

CREATE TABLE customers (

id INT PRIMARY KEY,

name VARCHAR(50),
email VARCHAR(100) UNIQUE,
phone VARCHAR(20) NULL
);

The above statement creates a table named customers, which contains id, name, email and phone Four columns. The id column is of integer type and is set as the primary key; the name and email columns are of text type; the phone column can be null. The UNIQUE keyword after the email column indicates that each value in the email column must be unique.

In addition to basic column definitions, you can also specify table constraints. Constraints are used to enforce rules to prevent invalid or incorrect data from being inserted. For example, you can use the following statement to create a foreign key constraint for the customers table:

CREATE TABLE orders (

id INT PRIMARY KEY,

customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);

The above statement creates a table named orders, which contains three columns: id, customer_id and order_date. The customer_id column is a foreign key that refers to the id column in the customers table to achieve the relationship between the two tables.

3. Conclusion

In MySQL, querying and creating tables are very common operations. This article briefly introduces how to use the SELECT statement to query and the CREATE TABLE statement to create tables. In practice, attention needs to be paid to query performance and writing effective constraints to ensure data integrity. Through good database design and query practices, you can help improve the efficiency and reliability of your applications.

The above is the detailed content of How to query and create tables in MySQL. For more information, please follow other related articles on the PHP Chinese website!