Shulou(Shulou.com)06/02 Report--

This article will explain in detail the example analysis of DDL and DML in MySQL. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

1. DDL

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

1.1 Database operation

1.1.1 creating and using databases

# create the database CREATE DATABASE database name; # create the database, first determine whether the database exists, then create the CREATE DATABASE IF NOT EXISTS database name; # create the database and specify the character set CREATE DATABASE database name CHARACTER SET character set; # use the database USE database name

1.1.2 query the database

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

1.1.3 modify the character set of the database

# modify the character set of the database ALTER DATABASE database name CHARACTER SET character set name

1.1.4 deleting the database

# Delete the database DROP DATABASE database name; # determine whether the database exists, and then delete the DROP DATABASE IF EXISTS database name; 1.2 data table operation

1.2.1 create a datasheet

# create data table CREATE TABLE table name (column 1 data type [constraint], column 2 data type [constraint],... Column n data type [constraint])

Note: there is no need to add a comma to 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

# modify table name ALTER TABLE table name RENAME TO new table name; # modify table character set ALTER TABLE table name CHARACTER SET character set; # add a column ALTER TABLE table name ADD column name data type; # modify column name, data type ALTER TABLE table name CHANGE column name new column data type; ALTER TABLE table name MODIFY column name new data type; # delete column ALTER TABLE table name DROP column name

1.2.4 Delete data Table

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

In fact, the keywords for both the database and the data table are the same, except that it indicates whether to operate on the database or the data table later. Add database if you are operating on the database, and table if you are operating on the data table

1.3 Common data types

1.4 constraint

Concept: limit the data in the table to ensure the correctness, validity and integrity of the data.

1.5 identity column

Self-increasing

Add auto_increment after the field

II. DML

After introducing DDL, let's take a look at DML,DML 's full name Data Manipulate Language, which is also literally translated as a 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, SET, column name 1 = value 1, column name n = value n, etc.)

Note when adding data:

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

two。 The number of columns and values must be the same.

3. Omit column names, values are added to all columns by default.

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

Note: if no conditions are added, all data in the table is modified.

Delete data # Delete data DELETE FROM table name [WHERE condition]; # if no condition is added, delete all records in the table. # Delete DELETE FROM table names of all records in the table;-not recommended. TRUNCATE TABLE table name;-recommended for efficient use.

Comparison of delete and truncate:

This is the end of this article on "sample Analysis of DDL and DML in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.