Shulou(Shulou.com)06/01 Report--

The SQLlite database is probably the lightest and most widely used SQL database at present. It is also open source and is contained in a relatively small library in C #. It supports ACID transactions, zero configuration, and a complete database stored in a single disk file. It uses very low resources and can run stably in a memory environment of hundreds of kilogrammes. At present, it supports data as small as 2TB, and it does not have any additional dependency, good portability, and supports multiple development languages. So SQLlite is widely used in various system platforms, some software applications, small websites, as well as embedded applications, simple data analysis, instead of disk temporary files, file files, cache and so on. Under the Unix system, it is generally installed by default, and even if it is not installed, you can download the binary package on the official website (http://www.sqlite.org/download.html) as in Windows or macOS and configure the PATH environment variable to use it, or download the source package to compile it. You can usually use shell in the system to interact directly or use each gui. An established schema is a file stored on disk in SQLlite. Note that once the data file of the table is deleted, the data will be lost, and it is easy to use.

[root@localhost data] # sqlite3 # call binaries directly into the command interface [root@localhost data] # sqlite3 / data/my_test.db # create a schema suffix called my_test with anything, but note that different file names with the same suffix are different files, which are two completely independent schema. It is recommended that .db is standard and easy to distinguish. If a schema with this file name does not create a data file, a database with a main is built by default, and the schema established by default is not encrypted. If it is important data, it is recommended to encrypt [root@localhost data] # sqlite3 / data/my_test.db # to enter the schema of my_test. Note that If the newly created schema does not create any projects in it, it will not produce the SQLite version 3.6.20Enter ".help" for instructionsEnter SQL statements terminated with an of the corresponding data file "sqlite > .database # View the database information under the current schema seq name file -0 main / data/my_test.dbsqlite > .help # help information sqlite > .exit # exit sqlite > SELECT * FROM sqlite_master # system table, the only one in SQLite, is read-only and cannot be deleted. Delete the table is doing an operation similar to flush sqlite > .show echo: off explain: off headers: off mode: listnullvalue: "" output: stdoutseparator: "|" width:

These are the interactive commands commonly used in SQLlite databases. As for more interactive commands that can be viewed through help, we will not introduce too much here. Normally, you can learn all the tables, views, indexes, triggers and other information under the current SQLlite by querying the only system table sqlite_master in the schema database. There are 4 columns in the sqlite_ master table:

The type column records the type of item, such as table, view, index, trigger name column records the name of the item, such as table name, index name, view name, etc. The name of the table to which the tbl_name column record belongs, such as the table name where the index is located. For a table, this column is the number of the table name itself, the rootpage column that records the item stored in the database page. For the view, the column value is 0, and the trigger column value is NULL. The sql column records the DDL of the table creation

Some commonly used system table queries:

Sqlite > SELECT * FROM sqlite_master WHERE type = 'table'; # View all table information under the current schema sqlite > SELECT * FROM sqlite_master WHERE rootpage = 0; # View all view information under the current schema sqlite > SELECT * FROM sqlite_master WHERE rootpage IS NULL; # View all view information under the current schema sqlite > SELECT sql FROM sqlite_master WHERE type =' table' AND tbl_name = 'tablename' # View tablenameDDL or directly use the schma command to view sqlite > .schema sqlite_masterCREATE TABLE sqlite_master (type text, name text, tbl_name text, rootpage integer, sql text)

There are currently five types of data storage under SQLlite:

Description of data storage type

The NULL value is a NULL value, the INTEGER value is a signed integer, the real value is a floating-point value stored in 1, 2, 3, 4, 6 or 8 bytes, the IEEE floating-point text value stored as 8 bytes is a text string, and the blob value is a blob data stored using database encoding (UTF-8, UTF-16BE, or UTF-16LE), stored entirely according to its input

SQLlite itself has the concept of affinity data types and affinity data names that support columns. Any column can still store any type of data, and when the data is inserted, the data in the field will give priority to the kinship type as the way to store the value.

For example, various int types are stored in INTEGER, and INTEGER types or other data type names are used in the built tables, but in fact, there is still INTEGER. It should be noted that SQLlite itself is only a small lightweight database. Usually, do not use SQLlite as other large data. Secondly, because SQLlite data files are generally stored in the form of files. In peacetime, you can compress and back up its files.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.