Simple SQLite tutorial

The earliest use of SQLite was because sql2005 was really heavy, and my old machine was cool.
In addition, access is not used to-it refers to. Net access.
Later, I searched for cnblogs and thought SQLite was good because my framework did not use stored procedures.
Let's get down to the truth.
1. Download SQLite 3.3.4 from www.sqlite.org
For convenience, I solved the issue and put sqlite3.exe in the Windows directory.
Run cmd to enter the command line
1)
Create a database file:
> Sqlite3 D: \ test. DB press ENTER
A test. DB is generated on disk D.
In this way, sqlite3 also hangs the test. DB
2)
Use. Help to see what commands are available
>. Help press Enter.
3) You can enter the SQL statement to create a table. After that, press enter to view the table.
4) Check the number of tables created.
>. Tables
5) view the table structure
>. Schema table name
6) Check the database currently on.
>. Database
7) If you want to output the query to a file
>. Output file name
> Query statement;
The query result is output to the file c: \ query.txt.

Output query results on the screen
>. Output stdout

8) output the table structure and the index will also output
. Dump table name
9) Exit
>. Exit or. Quit

2. Download the ado.netdriver from http://sqlite.phxsoftware.com.
Downloaded and installed. The installation directory contains system. Data. SQLite. dll.
We only need to copy this file to the Reference Directory and add the reference to operate on the SQLite database.
All ado.net objects start with SQLite, such as sqliteconnection.
The connection string only needs the following method:
Data Source = D: \ test. DB or datasource = test. DB -- Application in and ApplicationProgramOr. NET directories that can be automatically found
The rest is simple ~~

3. SQL syntax
Since SQL Server or iseries were used in the past, the DDL syntax is very embarrassing.
1) create a table with a single primary key
Create Table [admin] (
[Username] [nvarchar] (20) primary key not null,
[Password] [nvarchar] (50) not null,
[Rank] [smallint] not null,
[MailServer] [nvarchar] (50) not null,
[Mailuser] [nvarchar] (50) not null,
[Mailpassword] [nvarchar] (50) not null,
[Mail] [nvarchar] (50) not null
);
2) create a table with multiple primary keys
Create Table [codedetail] (
[Cdtype] [nvarchar] (10) Not null,
[Cdcode] [nvarchar] (20) not null,
[Cdstring1] [ntext] not null,
[Cdstring2] [ntext] not null,
[Cdstring3] [ntext] not null,
Primary Key (cdtype, cdcode)

);
3) create an index
Create index [ix_account] on [account] ([ischeck], [username]);

View and so on.
4. Useful SQL statements
Select * From sqlite_master
Select datetime ('now ')
Select date ('now ')
Select time ('now ')
For more information, see the SQLite wiki.

Oh, some people say that it seems that starting a transaction during batch insertion is n times faster than not starting a transaction.
In addition, it is estimated that automatic prepare can be used as much as possible using parameterized SQL statements as commercial databases.

==============

SQLite can directly execute the command under shell/DOS command:

Sqlite3 film. DB "select * from film ;"

Output HTML table:

Sqlite3-HTML film. DB "select * from film ;"

"Pour out" the database 」:

Sqlite3 film. DB ". Dump"> output. SQL

Use the output data to create an identical database (with the preceding command, the standard SQL database is backed up ):

Sqlite3 film. DB <output. SQL

When inserting a large amount of data, you may need to run the following command first:

Begin;

After inserting the data, remember to execute this command before the data is written into the database:

Commit;

;