<>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
Examples :
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
.mode column
.help help
.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 )
<>SQL92 standard

SQL-92 summary

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
NULL vacant
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 .

©2019-2020 Toolsou All rights reserved,
Solve in servlet The Chinese output in is a question mark C String function and character function in language MySQL management 35 A small coup optimization Java performance —— Concise article Seven sorting algorithms (java code ) use Ansible Batch deployment SSH Password free login to remote host according to excel generate create Build table SQL sentence Spring Source code series ( sixteen )Spring merge BeanDefinition Principle of Virtual machine installation Linux course What are the common exception classes ?