2020年10月26日 / 15次阅读 / Last Modified 2020年11月6日



Data Definition Language,数据定义语言。

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.

DDL用来管理container,比如table,index,view。table是SQL数据库的基本数据结构,还有 临时table (create temp table ...),和 virtual table。

Temporary tables have two specific features. First, temporary tables can only be seen by the database connection that created them. This allows the simultaneous re-use of table names without any worry of conflict between different clients. Second, all associated temporary tables are automatically dropped and deleted whenever a data-base connection is closed.


A view, on the other hand, is fully dynamic. Every time the view is referenced or queried, the underlying SELECT statement is run to regenerate the view. This means the data seen in a view automatically updates as the data changes in the underlying tables. In a sense,views are almost like named queries.

约束(constraint)可以应用于一个或多个column,后者有时也被称为 table constraint,表级约束。

Index可以单独创建,也可以在create table的时候,设置unique约束,SQLite3会自动为有unique约束的column,创建一个index。单独创建index,就可以选择是否要加上unique约束,有一些column的数据也很散列,但是不满足unique,也可以考虑增加index。



Data Manipulation Language,数据操作语言。

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.

就是 select,insert,update,delete。



select语句是SQL中最复杂,最多变的。(select的结果一般是一个table,此table可用于insert,或者create temp table或者view等操作)


Transaction Control language,事务控制语言。

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.

有 begin,commit,rollback。(SQLite的三种事务模式



Data Control Language,数据控制语言,用于权限控制。

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.


-- EOF --




电子邮件地址不会被公开。 必填项已用*标注

  • 麦新杰

    delete from tablename where condition [回复]



©Copyright 麦新杰 Since 2019 Python笔记

go to top