Analysis on common management tools of SQLite database development

SQLite database development is mainly for mobile phones and other mobile devices. Generally for the SQLite database development, many developers are not unfamiliar. Here will introduce several SQLite database development of the practical tools, I hope to help you.

Although I have been using SQL Server2000 and 2005 development projects, but sometimes want to develop some small applications, using such a database is not appropriate, think you can use access to do the database,

But always feel that access is something of the past, and C # is not very collocation. So I searched the internet and found that a sqlite is a green database that supports. NET development, directly a System.Data.SQLite.DLL can access the SQLite database, perform various operations, and support transactions.

Found the appropriate database, but to the official website under the installation package a DLL file, and does not come with a management tool, how to build a database table ah? It seems that only third-party development tools can be found online.

Online a search, or there are a lot of management tools, think or follow my installation order to introduce it.

The first to find is the SQLite Administrator, the latest version is 0.8.3.2, but it seems to have been updated a long time ago. Read the author's introduction, the basic functions of the database have, software look is also full of beautiful.

Advantages: This software is easy to get started because of the Chinese language. You can write SQL statements and view table data at any time, modifying table data

Disadvantage: It seems that Chinese support is not very good, because I enter Chinese through the program in it is garbled, but in it to see the Chinese in the display to the development program is garbled.

Their first SQLite database is it built, the table has been built, to the development of the time to find that the Chinese support is not good, but the software has no place to change language. My software is developed, but debugging is written by myself

interface to see the results, can not be changed through the management tool efficiency or a bit not high, there is no way to finally cruel and online search

The second one found is sharpplus SQlite Developer, a commercial development.

Advantages: Also support the Chinese interface, and character encoding can support UTF-8, etc., so there is no garbled problem. Because it is commercial, more functions, more convenient operation.

Cons: Feel a little bit of dirt on the interface

The third one is SQLite Expert Personal 1.7.13

This developer is better, provides a free version, since the business of collecting money, then first use with a free look.

Advantages: Support UTF-8 encoding, no Chinese garbled, interface with SQLite administrator as beautiful, easy to operate.

Cons: English interface .

Because SQLite does not need the service side, and does not support stored procedures, custom functions and other functions, so its management tool function is not so complex, but SQLite does not have other databases so good to deal with Ah!

1. Download the version of SQLite 3.3.4 from www.sqlite.org

For convenience, I unzipped it, just a SQLite3.exe, into the Windows directory.

CMD into command line

2. 1) Create the database file:
4. >sqlite3 D:\test.db Enter
6. A test.db is generated on the D-disk.
8. And SQLite3 hung up on this test.db.
10. 2)
12. Use. Help to see what the orders are.
14. >.help Enter
16. 3) You can enter the SQL statement directly here to create a table with; end, then enter to see
18. 4) See how many tables have been created
20. >.tables
22. 5) Look at the table structure
24. . Schema Table name
26. 6) Look at the currently hanging database
28. . Database  
30. 7) If you want to output the query to a file
32. . Output file name
34. > Query statements;
36. The query results are output to the file C:\query.txt
40. Use the screen output for query results
42. . Output stdout
46. 8) Output The table structure, and the index will also output
48. . Dump Table Name
50. 9) exit
52. >.exit or. Quit

2. Download the ADO driver from http://sqlite.phxsoftware.com/ .

Download the installation, in the installation directory exists System.Data.SQLite.dll we just need to copy this file to the reference directory, and add a reference to the SQLite data The library operates all of the ADO objects that start with SQLite, such as the Sqliteconnection connection string, which requires only the following data source=d:\test.db or datasource= test.db--applies to directories that are automatically found with applications or. Net

The rest is very simple ~ ~

3. SQL syntax

Because of the previous use of SQL Server or iseries, so the DDL syntax is very embarrassed

1) Create a table with a single primary key

2. CREATE TABLE [Admin] (
4. [UserName] [nvarchar] (a) PRIMARY KEY not NULL,
6. [Password] [nvarchar] (a) not NULL ,
8. [Rank] [smallint] not NULL ,
10. [mailserver] [nvarchar] (a) not NULL ,
12. [mailuser] [nvarchar] (a) not NULL ,
14. [MailPassword] [nvarchar] (a) not NULL ,
16. [Mail] [nvarchar] (+) not NULL
18. ) ;

2) Create a table with multiple primary keys

2. CREATE TABLE [Codedetail] (
4. [Cdtype] [nvarchar] (Ten) not NULL ,
6. [Cdcode] [nvarchar] (a) not NULL ,
8. [CdString1] [ntext] not NULL ,
10. [CdString2] [ntext] not NULL ,
12. [CDSTRING3] [ntext] not NULL,
14. PRIMARY KEY (cdtype,cdcode)
16. ) ;

3) Create an index

1. CREATE INDEX [ix_account] on [Account] ([Ischeck], [UserName]);

You can also view and so on.

4. There is also a useful SQL

1. Select  *  from Sqlite_master
2. Select datetime (' now ')
3. Select Date(' now ')
4. Select time(' now ')

And a lot of functions, you can refer to the SQLite wiki.
Oh, and you see someone say, like when a batch is inserted, starting a transaction is faster than not starting the transaction n times
There is also the use of parameterized SQL as much as possible, and the same as commercial db can be automatically prepare.

===========

SQLite can execute commands directly under the Shell/dos command:

Sqlite3 film.db "select * from film;"

Output HTML table:

sqlite3-html film.db "select * from film;"

To "pour out" the database:

Sqlite3 film.db ". Dump" > Output.sql

Using the output data, create an identical database (plus the above directive, which is the standard SQL database backup):

Sqlite3 Film.db < Output.sql

When inserting a large amount of data, you may need to call this command first:

Begin

After inserting the data, remember to call this command, the data will be written into the database:

Commit

Original title: Common management tools for developing SQLite database

Links:http://www.cnblogs.com/meiyou/