Notes from instructor Xiao Bu's "SQLite Quick Start video tutorial"

I took notes after reading instructor Xiao Bu's "SQLite Quick Start video tutorial ".

 

Lecture 1

What is SQLite?
Why?
How to get it? How to install?
How to manage it?
How to Use Perl to access SQLite data;
How to use C or C ++;
SQL features not supported by SQLite;

Self-contained, no server, Zero Configuration, support for transactions.

SQLite databases have only one file, including multiple tables, indexes, triggers, and views;
SQLite directly reads and writes files on the disk.

Four features of transactions (acid) Atomic (atomic), consistent (Consistent), isolated (independent), durable (persistent)
Implement most sql92 standards
The entire database is stored in a cross-platform data file;
The size of the supported database can reach TB, and supports GB strings or blobs (binary information ).
All functions are compiled into kb, and some features can be reduced to kb;
SQLite does not support concurrent access, not two processes simultaneously, exclusive
High code quality, 99% of the Code has been tested;
Self-contained means that it does not rely on any external library;

Code can be applied in any field, including the business field;

SQLite is designed to simplify everything:
Convenient Management;
Easy to use;
It is easy to nest into other programs;
Easy Maintenance and customization;

SQLite is not intended to replace Oracle, but to replace fopen ();

Famous SQLite customers:
Adobe
Apple
Firefox
Ge
Google
Microsoft
Coffee
Skype

SQLite can work well;
Save the application configuration information;
Embedded devices and applications;
Small website, prototype system. If the concurrency of large websites is high, MySQL is recommended;
Replace random and chaotic disk files;
Internal or temporary databases;
Command Line data analysis tools;
During demonstration or test;
Read the source code of SQLite and learn about database technology;
Extends the new features of the SQL language;

The background System of the caster uses the MySQL database;

Lecture 2

Management tools
Source code-level Installation
Compiled executable files, for Windows or Linux;
Download the source code and compile it yourself;

Source code:
The amalgamation version combines a lot of small things into a big guy.
Demonstrate how to compile in Linux;

The operating system is centos5.2.
Chmod + x sqlite3-3.6.3.bin
/Sqlite3-3.6.3.bin
Similar to Windows, go to SQLite Shell

Tar zxvf sqlite-amalgamation-3.6.3.tar.gz

Source code installation trilogy:
Configure;
Make;
Make install;

Generally, readme and install files are both text files;

SQLite source code installation process:
./Configure -- prefix =/opt/sqlite3
-- Prefix indicates the directory in which the target file is installed, without specifying/usr/local/in the default directory.
GCC must be installed

Enter make to start compilation;

Enter make install to copy the file to the target directory;

After installation, go to/opt/sqlite3 and check the three directories: Bin, include, and Lib.
Standard software layout structure
Static and dynamic libraries are available in Lib, and dynamic libraries are recommended.

Sqlite3.exe is shell. C, with over lines of source code implementing a simple command line tool;
The dynamic library is sqlite3.c.

Functions of the sqlite3 tool:
Create a database;
Execute SQL commands;
Queries the table structure of the database or the structure of other objects;
Import and export data;
Change the output format;
Use in shell scripts; learn shell automated script programming;

Create a database;
Sqlite3 database file name

When you enter an SQL statement, you must end with a semicolon, which is also the standard of SQL;

Add SQLite to the path in Linux, so that sqlite3 can be used in any path;
# Echo $ path
CP/opt/sqlite3/bin/sqlite3/usr/local/bin

System Table sqlite_master, which stores database schema
You cannot perform drop table, update, insert, or delete operations on the sqlite_master table.

Temporary tables are stored in sqlite_temp_master.
In addition to database files, SQLite generates temporary files during running.

Sqlite3 is just a shell. If it is actually executed, it will still call the SQLite library. You can study it.
Sqlite3 commands start with ".",. help;

SQL plus has three types of commands:
Submit SQL commands to the database engine;
The pl SQL command is submitted to the database engine;
SQL plus commands are executed locally;

Lecture 3

Sqlite3 command
. Help

Command to change the output format;
CSV records separated by commas;
The. Mode Command switches between the eight display formats;
The default is the list mode, and the default separator is "| ";
The list mode is particularly suitable for awk;
Line Mode. Each column has a separate row. The column name is equal to the column value and there are empty rows between records;
Column mode, which is the same as the default mode in SQL plus;. header on opens the header,. header off closes the header; this mode is more readable;
. Width 3 30, the first column is up to 3, and the second column is up to 30;
Insert mode: it is interesting to generate an insert SQL statement;. Mode: insert target table name;
HTML mode, XHTML table, suitable for CGI processing;

In list mode, one record is displayed in one row, and each column is separated by a vertical line;

For Linux and UNIX, the design concept is small and elegant. One tool only does one thing. Concatenate commands through pipelines;

By default, the query result is written to the screen or a file. Use the. Output command;
. Output stdout, and the output is returned to the screen;

Query the database schema, that is, the table structure and database structure;
. Tables to view all tables in the database;
. Indices, which can list all indexes of a specified table;
. Schema: The create statement used to create tables and indexes. You can also specify the table name later;
. Databases, to view all the currently opened databases, usually a main temp;

Lecture 4

Function of importing the entire database into an ASC text file;
. Dump command; the exported SQL statement can be executed in Oracle;
A command for generating database archive copy;
$ Echo ". Dump" | sqlite3 ex1 | gzip-C> ex1dmp.gz
Database reconstruction command:
Zcat ex1dmp.gz | sqlite3 ex2
Extract data from sqlite3 and import it to other popular databases;

. Explain command
The explain command is used to query the execution plan finally parsed by an SQL statement. It is also available in Oracle and is useful for SQL statement optimization;
The process of processing an SQL statement: parsing, analyzing, and executing to check the efficiency of the SQL statement;
>. Explain
> Explain select * from T;

. Timeout command
The default timeout value is 0. When a table or index is queried and the table or index is locked, the system returns immediately without waiting;
SQLite is only suitable for access by one process and is not suitable for concurrent access by multiple processes;

Use the sqlite3 command in shell;
The input and output of sqlite3 commands can be redirected in common usage modes;
In SQL statements, you can run go or/In addition to the semicolon;
# Sqlite3 demo. DB "select * from t"

A simple shell script
A shell script must start "#! ", This is a rule.
Items Between paired eof are imported into SQLite as they are; here document technology;
The method of calling the interactive program in shell;

How to Use Perl scripts to access the SQLite database;
Perl, a well-established language with a history of 20 years, is gradually replaced by Python or Ruby as it goes downhill;
Perl is popular in North America;
The tools used at work are Perl + shell;
Describe Perl as a Swiss Army knife, but its readability is poor;
Ruby is suitable for rapid web development;
Perl processes system management and biological information badly;

Activestat Perl www.activestate.com for free on Windows
Linux/Unix www.perl.org

DBI
Run a small program to check whether Perl supports SQLite;
Perl needs the SQLite driver to access the SQLite database. If it is not installed, install the driver;

Lecture 5

This topic describes how to use Perl and C to access the sqlite3 database;

Use Perl to access the SQLite Database
Check whether the SQLite driver code is installed in Perl;
Perl uses the DBI interface to access the database, and DBD (Database driver) is available under DBI, which supports MySQL and Oracle respectively;

A basic insert operation code demonstration;
First establish a connection with the database;
Make some preparations;
Execute the statement;
Determines whether an error has occurred;
Submit;
Disconnected;

A basic query operation code Demonstration:
Execute prepare before executing the query statement;
Then execute;
Extract the result set, bind each column to the variable, and then perform fetch in the while loop;
Finished. Release the result set;
Disconnect;

Perl is powerful in processing text files and can operate databases;

Access SQLite using C language
Two objects
Database connection object: sqlite3; Control connection;
Statement execution object: sqlite3_stmt; Control SQL statements;

Six commonly used interface functions are used to operate SQLite core functions:
Sqlite3_open ()
Sqlite3_prepare ()
Sqlite3_step (), similar to the fetch function;
Sqlite3_column ()
Sqlite3_finalize ()
Sqlite3_close (), disconnect;

Sqlitedemo. c
# Include

Sqlite3_errmsg (db); obtain the cause of failure;

The function of callback;
After the query is executed, the callback function is automatically called to print the query results.

The header file and Lib library are used to compile the program. This time, the static library is used.

# Gcc-V check whether there is gcc
Compile command
# Gcc-I/opt/sqlite3/include-L/opt/sqlite3/lib-lsqlite3 sqlite3demo. C-o dbtest
-I header file search path
-L library file search path
-L library file

Limitations of SQLite:
The official website supports large and small data volumes;
Foreign key constraints are not supported;
Incomplete support for triggers;
The support for alter table is incomplete;
Embedded transactions are not supported;
Does not support holding connections and full outer join.
View write operations are not supported;
Grant and revoke are not supported;

As a single user, SQLite has a good standalone database.