How to create and manage databases in MySQL using SQL statements?

How to create and manage a database in MySQL using SQL statements?

MySQL is a widely used relational database management system that provides rich SQL statements to create and manage databases. In this article, we will introduce in detail how to use SQL statements to create and manage databases in MySQL, and provide specific code examples.

1. Create a database

To create a database, you can use the following SQL statement:

CREATE DATABASE database_name;

Among them, database_name is to be created The name of the database. For example, to create a database named "mydb", you can execute the following SQL statement:

CREATE DATABASE mydb;

2. Select the database

Before using the database, you need to First select the database you want to operate on. You can use the following SQL statement:

USE database_name;

where database_name is the name of the database to be selected. For example, to select the database named "mydb", you can execute the following SQL statement:

USE mydb;

3. Create a data table

In the database, the data table It is the basic unit for storing data. To create a data table, you can use the following SQL statement:

CREATE TABLE table_name (

column1 datatype constraint,
column2 datatype constraint,
...

);

where table_name is the name of the data table to be created, column1 and column2 is the column name of the data table, datatype is the data type of the column, and constraint is the constraint condition of the column. The following is an example of creating a data table:

CREATE TABLE employees (

id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
email VARCHAR(100) UNIQUE

);

In the above example, we created a data named "employees" The table includes four columns: id, name, age and email. The id column is the primary key column, the name and email columns have length restrictions, and the email column also has a unique constraint.

4. Insert data

After creating the data table, you can insert data into the data table. You can use the following SQL statement:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Among them, table_name is the The name of the data table into which data is inserted, column1 and column2 are the column names of the data table, and value1 and value2 are the data values ​​to be inserted. The following is an example of inserting data:

INSERT INTO employees (id, name, age, email)
VALUES (1, 'Alice', 25, 'alice@gmail.com');

In the above example, we inserted a piece of data into the "employees" data table, including the values ​​of the four columns of id, name, age and email.

5. Query data

To query the data in the data table, you can use the following SQL statement:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Among them, table_name is the name of the data table to be queried, column1 and column2 are the column names to be queried, and condition is the query condition. The following is an example of querying data:

SELECT name, age, email
FROM employees
WHERE age > 30;

In the above example, we query "employees "All data in the data table with age greater than 30, and returns the values ​​of the three columns of name, age and email.

6. Update data

To update the data in the data table, you can use the following SQL statement:

UPDATE table_name
SET column1 = value1, column2 = value2 , ...
WHERE condition;

Among them, table_name is the name of the data table to be updated, column1 and column2 are the column names to be updated, value1 and value2 are the values ​​to be updated, condition is Conditions for updating data. The following is an example of updating data:

UPDATE employees
SET age = 35
WHERE name = 'Alice';

In the above example, we will "employees" data The age of the employee named "Alice" in the table is updated to 35.

7. Delete data

To delete data in the data table, you can use the following SQL statement:

DELETE FROM table_name
WHERE condition;

Among them, table_name is the name of the data table to be deleted, and condition is the condition for deleting data. The following is an example of deleting data:

DELETE FROM employees
WHERE age > 40;

In the above example, we deleted those whose age is greater than 40 in the "employees" data table All data.

In summary, we have introduced how to use SQL statements to create and manage databases in MySQL. By using the corresponding SQL statements, we can easily create data tables, insert data, query data, update data and delete data to achieve effective management of the database.

The above is the detailed content of How to create and manage databases in MySQL using SQL statements?. For more information, please follow other related articles on the PHP Chinese website!