SQL DML

Summary

DML is used for managing and manipulating the data within the database. It deals with querying, inserting, updating, and deleting data. Not any structure related.

Details

DML is used to manage and manipulate the data within a database. Unlike DDL, which deals with the structure of the database, DML focuses on querying and modifying the data. Common DML commands include SELECT, INSERT, UPDATE & DELETE. These commands are essential for interacting with and managing the data stored within a database. See more about subqueries.

Select statement

The select statement is used to query and retrieve data from one or more tables. The result is a multiset (bag), and the results aren’t ordered.

SELECT component
FROM component
(WHERE component)
(GROUP component)
(HAVING component)
(ORDER BY component)

See more about select statements. See also: joins.

AS (attribute renaming)

We can use the AS if we want the result to have a different attribute name.

SELECT name, id AS user_id, age FROM user;

Insert statement

The insert statement adds data to a relational database.

The values are entered according to the order in the columns

INSERT INTO my_table VALUES (myValues)

This explicitly mentions the column names to avoid confusion

INSERT INTO my_table(column1,column2,column3)
VALUES (myval1,myval2,myval3)

You can also specify only a few columns

INSERT INTO my_table(column1,column3)
VALUES (myval1,myval3)

The unspecified values become NULL if allowed or a default value if set.

SQL DELETE Statement

The delete statement removes data to a relational database.

Remove all rows from a table

DELETE FROM my_table;

Remove a specific row

DELETE FROM my_table WHERE column = something;

Removes all rows from table where column is null

DELETE FROM my_table WHERE column IS NULL;

Delete can also include subqueries.

SQL UPDATE Statement

The update is used to modify data in rows.

Updates rows based on condition

UPDATE my_table SET column = my_value WHERE column = something

Update rows to default

UPDATE my_table SET column = DEFAULT;

Update can also include subqueries.

INDBS Noter

Explorer