A brief discussion on DDL and DML in MySQL

Preface

In the previous article, we learned about MySQL and became familiar with how to use MySQL query statements.

We have already introduced the query in the data operation of adding, deleting, querying and modifying. This article will introduce the remaining additions, deletions, modifications and common operations of databases and tables, such as creation, deletion and modification.

1. DDL

Some readers may ask, what is DDL? Here the blogger briefly introduces that the full name of DDL is Data Define Languge. From English, we can easily translate it into data definition language , which is used to create, modify and delete libraries and tables.

1.1 Database Operations

1.1.1 Creating and using a database

#Create a database CREATE DATABASE database name;
 
#Create a database. First determine whether the database exists, then create it. CREATE DATABASE IF NOT EXISTS database name;
 
#Create a database and specify the character set CREATE DATABASE database name CHARACTER SET character set;
 
#Use database USE database name;

1.1.2 Querying the Database

#Query the names of all databases SHOW DATABASES;
 
#Query the character set of a database: query the creation statement of a database SHOW CREATE DATABASE database name;
 
#Query the name of the database being used SELECT DATABASE();

1.1.3 Modify the character set of the database

#Modify the character set of the database ALTER DATABASE database library name CHARACTER SET character set name;

1.1.4 Deleting a database

#Delete the database DROP DATABASE database name;
 
#Judge whether the database exists, and then delete it DROP DATABASE IF EXISTS database name;

1.2 Data Table Operation

1.2.1 Create a data table

#Create a data table CREATE TABLE table name (
	Column 1 Data Type [Constraint],
	Column 2 Data Type [Constraint],
	...
	Column n Data type [constraint]	
);

Note : No commas are needed in the last sentence.

1.2.2 Query Data Table

#Query all tables in the current database SHOW TABLES;
 
#Query all tables in a database SHOW TABLES FROM database name;
 
#Query table structure DESC table name;

1.2.3 Modify the data table

#Change the table name ALTER TABLE table name RENAME TO new table name;
 
#Modify the character set of the table ALTER TABLE table name CHARACTER SET character set;
 
#Add a column ALTER TABLE table name ADD column name data type;
 
#Modify column name and data type ALTER TABLE table name CHANGE column name new column type new data type;
ALTER TABLE table name MODIFY column name new data type;
 
#Delete columns ALTER TABLE table name DROP column name;

1.2.4 Deleting a Data Table

#Delete the data table DROP TABLE data table;
 
#First determine whether the data table exists, then delete the DROP TABLE IF EXISTS data table;

In fact, the keywords for operating on databases and data tables are the same, except that it is indicated afterwards whether the operation is on a database or a data table. If you are operating on a database , add database ; if you are operating on a data table , add table.

1.3 Common Data Types

1.4 Constraints

Concept: Limit the data in the table to ensure the correctness, validity and completeness of the data.

​​​​​​​​ ​​​​​​​

1.5 Identity Column

Auto-increment

Add auto_increment after the field

2. DML

After introducing DDL, let's take a look at DML. The full name of DML is Data Manipulate Language, which is also literally translated as data processing language . DML is used to add, delete, modify database records, and check data integrity.

2.1 Add data

#Add data INSERT INTO table name (column name 1,...) VALUES (value 1,...);
 
INSERT INTO table name SET column name 1 = value 1,..., column name n = value n;

When adding data, please note :

1. Column names and values ​​need to correspond one to one.

2. The number of columns and values ​​must be consistent.

3. If the column name is omitted, values ​​are added to all columns by default.

2.2 Modify data

#Modify single table data UPDATE table name SET column name 1 = value 1,..., column name n = value n [WHERE filter condition];
 
#Modify multi-table data UPDATE table 1 alias, table 2 alias SET field = new value,..., WHERE connection condition AND filter condition;

Note : If no condition is added, all data in the table will be modified.

2.3 Deleting Data

#Delete data DELETE FROM table name [WHERE condition]; #If no condition is added, all records in the table will be deleted.
 
#Delete all records in the table DELETE FROM table name; ----Not recommended.
TRUNCATE TABLE table name; -----Recommended, highly efficient.

Comparison between delete and truncate :

​​​​​​​​

This is the end of this article about DDL and DML in MySQL. For more information about DDL and DML in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!