Sqlite 简明教程

SQLite - Overview

本章帮助你理解什么是 SQLite,它与 SQL 的区别,为什么需要它,以及它如何处理应用程序数据库。

This chapter helps you understand what is SQLite, how it differs from SQL, why it is needed and the way in which it handles the applications Database.

SQLite 是一个实现一个独立的、无服务器的、零配置的、事务型 SQL 数据库引擎的软件库。SQLite 是周围发展最快的数据库引擎之一,但这仅仅是流行程度的增长,与它的规模无关。SQLite 的源代码是公有域的。

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is one of the fastest-growing database engines around, but that’s growth in terms of popularity, not anything to do with its size. The source code for SQLite is in the public domain.

What is SQLite?

SQLite 是一个实现独立的、无服务器的、零配置的、事务型 SQL 数据库引擎的进程内库。它是一个数据库,零配置,意味着和其他的数据库不同,你不需要在系统中配置它。

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. It is a database, which is zero-configured, which means like other databases you do not need to configure it in your system.

SQLite 引擎不是一个像其他数据库一样的独立进程,你可以根据你的需求与你的应用程序静态或动态地链接它。SQLite 直接访问它的存储文件。

SQLite engine is not a standalone process like other databases, you can link it statically or dynamically as per your requirement with your application. SQLite accesses its storage files directly.

Why SQLite?

  1. SQLite does not require a separate server process or system to operate (serverless).

  2. SQLite comes with zero-configuration, which means no setup or administration needed.

  3. A complete SQLite database is stored in a single cross-platform disk file.

  4. SQLite is very small and light weight, less than 400KiB fully configured or less than 250KiB with optional features omitted.

  5. SQLite is self-contained, which means no external dependencies.

  6. SQLite transactions are fully ACID-compliant, allowing safe access from multiple processes or threads.

  7. SQLite supports most of the query language features found in SQL92 (SQL2) standard.

  8. SQLite is written in ANSI-C and provides simple and easy-to-use API.

  9. SQLite is available on UNIX (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT).

SQLite A Brief History

  1. 2000 - D. Richard Hipp designed SQLite for the purpose of no administration required for operating a program.

  2. 2000 - In August, SQLite 1.0 released with GNU Database Manager.

  3. 2011 - Hipp announced to add UNQl interface to SQLite DB and to develop UNQLite (Document oriented database).

SQLite Limitations

SQLite 中有一些不受 SQL92 支持的功能,如下表所示。

There are few unsupported features of SQL92 in SQLite which are listed in the following table.

Sr.No.

Feature & Description

1

RIGHT OUTER JOIN Only LEFT OUTER JOIN is implemented.

2

FULL OUTER JOIN Only LEFT OUTER JOIN is implemented.

3

ALTER TABLE The RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. The DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT are not supported.

4

Trigger support FOR EACH ROW triggers are supported but not FOR EACH STATEMENT triggers.

5

VIEWs VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view.

6

GRANT and REVOKE The only access permissions that can be applied are the normal file access permissions of the underlying operating system.

SQLite Commands

与关系数据库交互的标准 SQLite 命令类似于 SQL。它们是 CREATE、SELECT、INSERT、UPDATE、DELETE 和 DROP。可以根据操作性质将这些命令分类为不同的组 −

The standard SQLite commands to interact with relational databases are similar to SQL. They are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their operational nature −

DDL - Data Definition Language

Sr.No.

Command & Description

1

CREATE Creates a new table, a view of a table, or other object in database.

2

ALTER Modifies an existing database object, such as a table.

3

DROP Deletes an entire table, a view of a table or other object in the database.

DML - Data Manipulation Language

Sr.No.

Command & Description

1

INSERT Creates a record

2

UPDATE Modifies records

3

DELETE Deletes records

DQL - Data Query Language

Sr.No.

Command & Description

1

SELECT Retrieves certain records from one or more tables