<>SQLite3 Introduction to database
◆SQLite first Alpha Version was born in 2000 year 5 month . So far 10 It's been three years . SQLite There is also a version SQLite 3 Has been released .
◆SQLite Is a lightweight database , It's compliance ACID Relational database management system based on XML , Its design goal is embedded , And it has been used in many embedded products , It takes a very low amount of resources , In embedded devices , It might only take a few hundred K That's enough memory . It can support Windows/Linux/Unix And so on mainstream operating system , At the same time, it can be combined with many programming languages , such as Tcl,PHP,Java etc. , also ODBC Interface , Also compared with Mysql,PostgreSQL These two open source world famous database management systems , It's faster than all of them .
<> establish SQLite database
stay Linux Under the command line , start-up sqlite3
Examples ：sqlite3 mydb.txt
explain ： Open database file mydb.txt, The default database name is main. If mydb.txt non-existent , Then create a new file
<> Additional database Attach
Additional database Attach sentence ：
Is to attach a database that already exists , Make multiple databases coexist , from sqlite unified management
Command format ： ATTACH [DATABASE] database-filename AS database-name
sqlite>attach database ‘mydb.dat’ as mydb be careful ： The file name is . Symbol , Quotation marks are required （sqlite>
It means here is in linux Running under ）
Separate database Detach sentence
Command format ：DETACH [DATABASE] database-name
It is not allowed to add a table with the same name as an existing table in the attached database . But you can attach a database with a table with the same name as a table in the main database . You can also attach the same database multiple times . use database-name.table-name To reference tables in the attached database
<>SQLite3 Click command
Command description remarks
.database view the database
.tables Look at the database tables ?
.schema[ Table name ] View the structure of all or a single table
.output[file 1 stdout] Output the results to a file or standard screen .output file1.txt
.separator[ Separator ] Sets the separator between column data .separator
.mode Change output format .mode insert tab
.quit sign out sqlite3 command line
<>SQLite Database logical object
surface TABLE, Indexes INDEX, view VIEW, trigger TRIGGER
View all logical objects in the database
sqlite>Select * from sqlite_master sqlite>.schema sqlite_master CREATE TABLE
sqlite_master( type text, name text, tbl_name text, rootpage integer, sql text )
ISO/ANSI and IEC A structured query language for database operation is developed SQL,92 International standards launched in , be called SQL-92
All relational database manufacturers are following the SQL-92 On the basis of standards , Expanded on its own products SQL; such as ：SQL SERVER-TSQL,ORACLE-PL/SQL wait
SQL92 Standards include ：
① Data definition language (DDL)： for example ：CREATE,DROP,ALTER And so on .
② Data operation language (DML)： for example ：INSERT（ insert ）,UPDATE（ modify ）,DELETE（ delete ） sentence .
③ Data query language (DQL)： for example ：SELECT sentence
④ Data control language (DCL)： for example ：GRANT,REVOKE,COMMIT,ROLLBACK And so on
<>SQLite Unsupported feature
User defined function , stored procedure
Foreign key constraints （ However, it can be replaced by a custom trigger ）
right out join/full out join
<>SQLite data type
Data type description compatible SQL92
INTEGER Used to store an integer , It can be used according to the size 1,2,3,4,6,8 position int,smallint
REALIEEE Floating point number float,numeric
TEXT Store as string varchar,char
BLOB Store by binary value
Fixed static data type for general data , and SQLite The dynamic data type is used ;
TEXT: TEXT Column usage NULL,TEXT perhaps BLOB Stores any data inserted into this column , If the data is a number , Then convert to TEXT.
NUMERIC Columns can use any storage type , It first attempts to convert the inserted data to REAL or INTEGER Type , If successful, store as REAL and INTEGER type , Otherwise, there will be no change .
INTEGER: and NUMERIC similar , It's just that it can be converted to INTEGER All values are converted to INTEGER, If it is REAL type , And there is no decimal part , Also turn to INTEGER.
REAL: and NUMERIC type It's just that it can be converted to REAL and INTEGER All values are converted to REAL.
NONE: Try not to make any changes .