SQLite3的DDL,DML,TCL,DCL

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

SQL语言分成DDL,DML,TCL和DCL这四类,本文记录SQLite3的支持情况。

DDL

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.
临时表的两个特性:(1)只能创建者能看到临时表;(2)数据库连接关闭,临时表自动删除。

管理方式有:create,alter,drop。

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.
view创建后不会自动删除,每一次访问view,用于创建view的select语句就会被执行一次,因此每次得到的都是最新的select结果。

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

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

python3的sqlite3模块,isolation_level只对DML有效,如果是execute一条DDL,不在transaction里面,也是autocommit。

DML

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。

python3的sqlite3模块,默认在使用DML语句的时候,需要显示调用commit哦!除非自己设置了合适的isolation_level,或者显示的使用transaction。

insert一般一次插入一行,如果insert后面跟select语句,就可以实现同时插入多行。update和delete,只要满足条件,都是多行模式!

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

TCL

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的三种事务模式

事务要满足ACID(Atomic,Consistent,Isolated,Durable)要求。

DCL

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.

SQLite没有DCL部分,因为它是基于文件的数据库,文件访问控制权限,是Linux操作系统的事儿。

-- EOF --

本文链接:https://www.pynote.net/archives/2653

留言区

《SQLite3的DDL,DML,TCL,DCL》有1条留言

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

  • 麦新杰

    delete from tablename where condition [回复]


前一篇:
后一篇:

More


©Copyright 麦新杰 Since 2019 Python笔记

go to top