What are DDL and DML?
The following is adapted from here MySQL What is DDL, DML and DCL?:
DDL
DDL is short name of Data Definition Language, which deals with
database schemas and descriptions, of how the data should reside in
the database.
- CREATE – to create database and its objects like (table, index, views, store procedure, function and triggers).
- ALTER – alters the structure of the existing database.
- DROP – delete objects from the database.
- TRUNCATE – remove all records from a table; also, all spaces allocated for the records are removed.
- COMMENT – add comments to the data dictionary.
- RENAME – rename an object.
DML
DML is short name of Data Manipulation Language which deals with data
manipulation, and includes most common SQL statements such SELECT,
INSERT, UPDATE, DELETE etc, and it is used to store, modify, retrieve,
delete and update data in database.
- SELECT – retrieve data from one or more tables.
- INSERT – insert data into a table.
- UPDATE – updates existing data within a table.
- DELETE – delete all records from a table.
- MERGE – UPSERT operation (insert or update)
- CALL – call a PL/SQL or Java subprogram.
- EXPLAIN PLAN – interpretation of the data access path.
- LOCK TABLE – concurrency control.
DCL
DCL is short name of Data Control Language which includes commands
such as GRANT, and mostly concerned with rights, permissions and other
controls of the database system.
- GRANT – allow users access privileges to database.
- REVOKE – withdraw users access privileges given by using the GRANT command.
TCL
TCL is short name of Transaction Control Language which deals with
transaction within a database.
- COMMIT – commits a transaction.
- ROLLBACK – rollback a transaction in case of any error occurs.
- SAVEPOINT – a point inside a transaction that allows rollback state to what it was at the time of the savepoint.
- SET TRANSACTION – specify characteristics for the transaction.
How can Delete be both a DDL and a DML statement
I agree with you, DELETE
is DML. Moreover, I dare say, TRUNCATE
should also be considered DML, since logically is equivalent to a DELETE
statement. The fact that TRUNCATE
is a DROP
and CREATE
is not enough in my opinion to justify assigning it to DDL, since the two together, carried out as one atomic operation, do not affect the schema of the database.
Why alter command is referred as DDL and not DML?
ALTER command is used to alter the structure of the database. And this is what DDL does i.e., DDL statements are used to define the database structure or schema.
Whereas DML statement is used to manage data within schema objects.
Is Select a DML or DDL?
- Inserting data that is selected from TABLE1 to TABLE 2
- It is data manipulation
- From the look of it they are the same
EDIT:
To answer the question title as well, SELECT
is not a DDL (Data definition language). DDL statements modify database layout, like CREATE TABLE
. DML statements are queries data, like SELECT
; or modifies data, like INSERT
.
Another edit:
It seems SELECT INTO
is not fully supported in mysql. Even though answer is still valid for general SQL. For me, SELECT INTO
's table creation aspect is a side effect. thus it is still a DML.
Why are SQL statements divided into DDL, DML, DCL and TCL statements?
These are classifications, they exits for the same reason every other classification on anything else exists...
Regarding use cases, I hardly see a situation where you would use those terms but maybe if you are the Business Analyst for a SQL Server management tool you could say that you dont want users to have access to any sort of DDL statments, meaning the users cant create objects on the DB...
When would you call java's thread.run() instead of thread.start()?
You might want to call run() in a particular unit test that is concerned strictly with functionality and not with concurrency.
How to find whether the SQL query type is DML or DDL?
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency
Related Topics
How to Simulate Unpivot in Access
How to Order by With Union in Sql
Dynamic SQL - Exec(@Sql) Versus Exec Sp_Executesql(@Sql)
SQL Server 2005 How Create a Unique Constraint
Is There Any Boolean Type in Oracle Databases
MySQL on Duplicate Key Update For Multiple Rows Insert in Single Query
How to Pass Variable as a Parameter in Execute SQL Task Ssis
How to Use "Date" Datatype in SQL Server
Export SQL Query Data to Excel
How to Use a Calculated Column to Calculate Another Column in the Same View
Create a Pivot Table With Postgresql
Passing an Array of Parameters to a Stored Procedure
Return Multiple Columns of the Same Row as Json Array of Objects
How to Run a SQL Query on an Excel Table
How to Use Distinct and Order by in Same Select Statement
What MySQL Database Tables and Relationships Would Support a Q&A Survey With Conditional Questions