SQL commands are divided into four major categories, or
languages. Each language defines a subset of
commands that serve a related purpose. The first language is the
Data Definition Language, or
DDL, which refers to commands that define the
structure of tables, views, indexes, and other data containers and objects
within the database. CREATE TABLE (used
to define a new table) and DROP VIEW
(used to delete a view) are examples of DDL commands.
The second category of commands is known as
Data Manipulation Language, or
DML. These are all of the commands that insert,
update, delete, and query actual data values from the data structures
defined by the DDL. INSERT (used to
insert new values into a table) and SELECT (used to query or look up data from tables) are
examples of DML commands.
Related to the DML and DDL is the Transaction Control Language, or
TCL. TCL commands can be used to control
transactions of DML and DDL commands. BEGIN (used to begin a multistatement transaction) and
COMMIT (used to end and accept a
transaction) are examples of TCL commands.
The last category is the Data Control Language, or
DCL. The main purpose of the DCL is to grant or
revoke access control. Much like file permissions, DCL commands are used to
allow (or deny) specific database users (or groups of users) permission to
utilize or access specific resources within a database. These permissions
can apply to both the DDL and the DML. DDL permissions might include the
ability to create a real or temporary table, while DML permissions might
include the ability to read, update, or delete the records of a specific
table. GRANT (used to assign a
permission) and REVOKE (used to delete an
existing permission) are the primary DCL commands.
SQLite supports the majority of standardized DDL, DML, and TCL commands but lacks any DCL commands. Because SQLite does not have user names or logins, it does not have any concept of assigned permissions. Rather, SQLite depends on datatype permissions to define who can open and access a database.
Get Using SQLite now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
