Data Manipulation Language (DML)
DML statements are used to manipulate data stored in a database. They can be used to retrieve and aggregate data for analysis, insert new rows, or edit existing rows. Table 2.12 lists the four main DML commands and the common statement structures associated with each of them.
TABLE 2.12 DML commands
| Command | Description | Statement Structure |
| SELECT | Read rows from a table or view | SELECT <list of columns> FROM <table name> WHERE <filter condition> GROUP BY <group by expression> HAVING <search condition> ORDER BY <columns to sort by> |
| INSERT | Insert new rows into a table | INSERT INTO <table name> ( <list of columns> ) VALUES ( <values to insert> ) |
| UPDATE | Update existing rows | UPDATE <table name> SET <column> = <new value> WHERE <filter condition> |
| DELETE | Remove existing rows | DELETE FROM <table name> WHERE <filter condition> |
Select statements are often more sophisticated than the example structure illustrated in Table 2.12. Queries can retrieve data from multiple tables, convert column data types, and perform aggregations. The UNION, EXCEPT, and INTERSECT operators can also be used to combine or contrast results from multiple queries into one result set. There will be more sophisticated query examples in the following sections, but it is important to note that processing order of operations in a select statement does not match the order they are written. This order, also known as the logical processing order, determines when the results from one step are made available to subsequent steps. The logical processing order is defined as follows:
- FROM
- ON
- JOIN
- WHERE
- GROUP BY
- WITH CUBE or WITH ROLLUP
- HAVING
- SELECT
- DISTINCT
- ORDER BY
- TOP
More information on the structure of a T-SQL select statement can be found at https://docs.microsoft.com/en-US/sql/t-sql/queries/select-transact-sql?view=sql-server-ver15.