Learning SQLite from Niu Yi

To the http://www.sqlite.org site (found a lot of Non-Profit Institutions love to use org, huh, it seems that my http: // www.niunan.org is still applied for, alas, unfortunately niunan.com was taken, otherwise, adding my current niunan.net and niunan.org would be too Prefect !) Download the latest SQLite and compress it to the sqlitetest directory of disk D,


Open the CMD command window and switch to the sqlitetest directory of disk D,


Create a new database and enter [quote] sqlite3 test [/quote] In the CMD window.


Test is the name of our database file. Note that there is no suffix here. You can also give it a suffix, such as sqlite3 test. DB, or even sqlite3 test. PHP: After executing this command, if the test database is not created in the current sqlitetest directory, an empty database named test is created, otherwise, open the test database under the current sqlitetest directory. Note that if a new database is created, the database is empty, at this time, the new database test in the current sqlitetest directory is not displayed ., This is the sqlitetest directory after the command is created:


In this case, after we enter the [quote]. Database [/quote] command to view the current database information, the sqlitetest directory will generate a test file, that is, the database we created,


Now we can see the test file generated by the sqlitetest directory.

The next step is to create a table. I checked it on the official website. In fact, the English instructions on the official website are also in simple English, basically, you can understand what it means by concatenating sentences. The fields in the SQLite database table mentioned above are actually five types (text, numeric, integer, real, none ), it seems that when creating a table, you do not have to write the full names of the types listed above.

1. If the type you write contains int characters, it is an integer.

2. If the written type contains "char", "clob", or "text", it is the text type.

3. If the write type contains "real", "floa", or "doub" characters, the real type is used.

4, other words of English do not understand what to say, huh, good English friends can come here to see, http://www.sqlite.org/datatype3.html

Let's try to create a table. Here, we also set the primary key while creating the table. I don't know if this setting is true for this SQLite primary key. We enter the command to create a table: [quote] Create Table person (ID int primary key, name nvarchar (10), age integer, address text); [/quote]


We can see that if an error occurs in the input statement, it will be prompted directly in the current command window. If the input is correct, nothing will be prompted, and each SQL statement must end with a semicolon. This is the same as MySQL.

We can use the [quote]. Table [/quote] command to view information about all the tables in the current database,


So what other commands besides the. Database and. Table SQLite commands are available? We can view it through [quote]. Help [/quote,


Note: Do not add a semicolon (;) to the end when entering the SQLite command...

We can see that it is also a large string of e-text, which is actually some simple e-text, and the concatenation of sentences is also basically confused ..

We can see a [quote]. schema [/quote] command. Let's try it. The table name follows,


We can see that this command output the table creation statement to us.

Let's insert a piece of data and try again. Here I will not insert the ID column to see if there is any problem.


We can see that it is inserted, but the selected ID number is blank. Let's insert another one and choose to see it.


Dizzy, can also be inserted, can also be selected, so that the primary key in the SQL statement I created earlier does not work, and then I searched the internet, it is found that the integer primary key must be written when the primary key is set during table creation, so that the field will automatically grow every time a database is inserted and a primary key function is provided accordingly, I deleted the entire table and created it again,


OK. When we insert data, we can see that the ID field in front is auto-incrementing.

What if we want to insert multiple statements in batches? We have created a SQL _ins.txt text document under the sqlitetestobject, which contains our insert statement,


Then we can use the. read command in SQLite to read data,


In this way, we have successfully imported our data.

The following is a field connection problem. A friend in this problem group mentioned that he tried to use the MSSQL method to test the problem. Normally, it should be the following SQL statement:

[SQL] Select 'name: '+ name +', age: '+ age from person;

[/SQL] But when the data is executed in SQLite, the wrong data will be selected. After searching, we find that the connection character in SQLite is | or a symbol. The correct SQL statement should be:

[SQL] Select 'name: '| Name |', age: '| age from person;

[/SQL]
Next, the most exciting time so far is SQLite paging. Like MySQL, you can directly use limit for paging, I started to have the urge to convert my access blog into SQLite to store data. Haha...


The command line operation is here, and the graphic management tool should be used .. After trying it out, I feel that sqlitespy is good. In fact, I have used sqlitespy, and I have never used any other management tools .. Haha .. You can go to the site to download the latest version: http://www.yunqa.de/delphi/doku.php/products/sqlitespy/index download decompress it, in fact, there are two files, one EXE, the other should be the sample database:


Run the EXE file and you can see that it is an English interface, but it is all in simple English. You should be able to understand it. Open the database we just created in the command line and note that it is opened, do not select the new .... the database is created.


After double-clicking the EXE file and running it, there will be another sqlitespy. db3 database in the directory. It should have been automatically generated by him.


The test database we just created cannot be seen in the window for selecting a database. Because we do not use the suffix, the software displays db3 and DB suffix files by default, we select to display all the files, and then we can see


Select the test database and open the person table. If the problem arises, it turns out to be garbled and dizzy .. However, we can query Chinese characters in the CMD command window.


Let's execute a statement. F9 is the execution statement.


Then we can check the table data. We can see that the inserted Chinese characters are displayed normally.


Dizzy .. I don't know what the problem is. It should be code, but I haven't found any solution after Google for a long time.

Ignore this and continue the next step ,. net query SQLite, find online, download system. data. SQLite. DLL, you can download the http://sqlite.phxsoftware.com/use vs to create a website, put the DLL downloaded above in the bin directory, and then write Code The MSSQL code written in peacetime is basically the same, and it is really good.


GV is a gridview control. The data in the table is simply displayed. As expected, the result is garbled and depressing, but the last record is not garbled, that record was added in sqlitespy.


As for how to solve this garbled problem, I have googled many times and cannot find a solution. I can only stay for a senior to solve it !!!

It's really tiring to write a blog... It took a whole morning... Because of this garbled problem, the decision to switch me from access to SQLite has been reduced a lot !!!