The first day of learning SQLite.

1. Download SQLite 3.3.4 from www.sqlite.org

For convenience, I decompress it and put it in the Windows directory as sqlite3.exe.

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.net driver 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;

 

;