This is the second day of my participation in the November Gwen Challenge. Check out the details: the last Gwen Challenge 2021

Creating a database

A database can be created using the sqlite3 databasename. db command. The following command creates a database named test.db:

zyf@zyf-Ubuntu:/media/zyf/ mobile disk /Project/SqliteProject$sqlite3 test.db SQLite version 3.32.2 2020-06-04 12:58:43 Enter ".help" for usage hints.Copy the code

Note that in the above command, although we provided the name of the database, if the database does not exist, Sqlite does not actually create the database until something (such as a table or view) is created inside the database. The reason for this is that it gives us the opportunity to do various permanent database Settings, such as page size, before the database structure is committed to disk. Once a database is created, some Settings such as page size, character set (UTF-8), etc. cannot be easily changed.

zyf@zyf-Ubuntu:/media/zyf/ mobile disk /Project/SqliteProject$ls -a.. zyf@zyf-Ubuntu:/media/zyf/ removable disk /Project/SqliteProject$Copy the code

As you can see, there is no data in the folder.

Now we use the default database configuration. If we need to create the database on disk, we can create a table like this:

sqlite> create table test(id integer primary key,value text);

Copy the code

Now we have a database file named test.db on disk, and the database contains a table named test. According to the semantics when we created the table, this table contains two fields, i.e. two columns:

  • A primary key column named ID that has properties that grow automatically by default. When an integer primary key column is defined, Sqlite applies a single increment function to that column to create a single increment. That is, if the value of that column is not provided during an INSERT statement, Sqlite will automatically generate it by looking for the next value of that column.

  • The other field is a simple text field named value.

Now let’s add a few rows to the table to verify the above conclusion:

sqlite> insert into test(id,value) values (1,"one");  
sqlite> insert into test(id, value) values (10,"ten");
sqlite> insert into test(value) values ("three");
sqlite> insert into test(value) values ("four"); 
Copy the code

In the insert statement above, we specify the id and value of the data to be inserted, and then we specify only value. In addition, the id values we specify are not contiguous and can be successfully inserted.

Now let’s look at the data we inserted earlier:

sqlite> .mode column
sqlite> .headers on
sqlite> 
sqlite> 
sqlite> select * from test;
id          value     
----------  ----------
1           one       
10          ten       
11          three     
12          four
Copy the code

In the above query we first specified the.mode and.headers attributes, which allow us to view the data in any way we like.

After we execute the query command, we can see that it is the data we inserted before. Although we did not specify the ID value when we inserted the last two data, it will automatically increase according to the ID value of the last data. In addition, we can also find that our ID value is discontinuous at the time of insertion, and the query here still retains the data we inserted before.

In the above table, we already know that ids grow automatically, so most of the time we do not insert the corresponding id, but we still care about the id value of the last inserted data. At this point, we can use the last_insert_rowid() function in SQLite to get the auto-growing increment value that was inserted last.

sqlite> select last_insert_rowid();
last_insert_rowid()
-------------------
12 
Copy the code

As you can see, using this function returns the value of the id of the last data we inserted before.

Before exiting, we can add an index and view for the rest of the demonstration:

sqlite> create index test_idx on test (value);
sqlite> create view schema as select * from sqlite_master;
Copy the code

You can exit the shell by running the. Exit or. Quit command. You can also exit the shell by pressing Ctrl + C on Windows or Ctrl + D on Linux.

Get the Schema information for the database

Sometimes, after creating a database, we want to get information about the database. The following shell commands can help us do this.

.tables [pattern]

This command returns a list of all tables and views, where pattern can be any SQL understood by the like operator. Executing this command returns all tables and views that meet the criteria, or all tables and views if no pattern argument is provided.

sqlite> .tables
schema  test 
Copy the code

Previously, we created a test table and a view named schema in the test.db database, and used the. Tables command to output both data directly.

.indices [tableName]

This command returns the index we created, if we specify a table name, returns the index we created in the table, if we do not specify a table name, returns all index information.

sqlite> .indices test
test_idx


sqlite> .indices
test_idx
Copy the code

.schema [tableName]

This command returns a table or view definition statement or, if no table name is provided, all database objects (including table,index,view, and trigger).

sqlite> .schema test
CREATE TABLE test(id integer primary key,value text);
CREATE INDEX test_idx on test (value);
Copy the code

SQL > create index (test); SQL > create index (test);

sqlite> .schema
CREATE TABLE test(id integer primary key,value text);
CREATE INDEX test_idx on test (value);
CREATE VIEW schema as select * from sqlite_master
/* schema(type,name,tbl_name,rootpage,sql) */;
Copy the code

Without specifying a table name, all definition statements related to the current database object are printed.

The detailed information

More detailed schema information can be obtained by querying SQLite’s important system view sqlite_master. This view is a system directory with the following structure:

Serial number The field name instructions
1 type Object types (Table, Index, View,trigger)
2 name The name of the object
3 tbl_name Object associated with the table
4 Rootpage The index (starting number) of the object root page in the database
5 sql SQL Definition of objects (DDL)

Query the sqlite_master table of the current database, and you can see the following:

sqlite> select type,name,tbl_name,rootpage,sql from sqlite_master;
type        name        tbl_name    rootpage    sql                                                 
----------  ----------  ----------  ----------  ----------------------------------------------------
table       test        test        3           CREATE TABLE test(id integer primary key,value text)
index       test_idx    test        4           CREATE INDEX test_idx on test (value)               
view        schema      schema      0           CREATE VIEW schema as select * from sqlite_master
Copy the code

From the output above we can see the complete listing of the current test.db database objects: a table, an index, and a view, each with its own original DLL creation statement.

Export data

We can use the.dump command to export database objects into SQL format. With no arguments, the.dump command exports the entire database as database Definition Language (DDL) and Database Operation Language (DML) commands, which are written to text or displayed on standard output suitable for recreating the database and its data. If arguments are provided, the shell parses them as table names or views, exports any tables or views that match the given arguments, and those that don’t are ignored. In shell mode, the output of the.dump command is directed to the screen by default. Dump [fileName] if you want to redirect output to a file, you can use the.dump [fileName] command, which redirects all output to the specified file. To restore output to the screen, simply execute.output stdout. Therefore, if we need to export the current database object to the file test_backup.sql, we can use the following command:

sqlite> .output test_backup.sql
sqlite> .dump
sqlite> .output stdout
Copy the code

Test_backup.sql (test_backup.sql, test_backup.sql, test_backup.sql, test_backup.sql, test_backup.sql, test_backup.sql)

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(id integer primary key,value text);
INSERT INTO test VALUES(1,'one');
INSERT INTO test VALUES(10,'ten');
INSERT INTO test VALUES(11,'three');
INSERT INTO test VALUES(12,'four');
CREATE INDEX test_idx on test (value);
CREATE VIEW schema as select * from sqlite_master;
COMMIT;
zyf@zyf-
Copy the code

With SQL redirection and various shell formatting options, you have a great deal of control over the exported data.

Import data

There are two ways to import data from external files, depending on the format of the file to be imported:

  • If the file consists of SQL statements, you can use the.read command and execute the commands contained in the file.

  • Import [file][table] if the file contains values separated by commas or other delimiters, use the. Import [file][table] command, which attempts to parse the specified file and insert data into the specified table. It by using pipe (” | “) as a delimiter characters parsed files in each row, column and will have the analysis of the insert to the table. Note that the data in the parsed fields in the file should match the columns in the table. You can use the. Separator command to specify different separators. To see the current value of the delimiter, use the.show command, as shown below:

sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: on
        mode: column
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width: 
    filename: test.db

Copy the code

Dump is used to import files. If test_backup. SQL is used as a backup file, you need to remove existing database objects (test table and schema view), and then use the.read command to import:

sqlite> drop table test;
sqlite> drop view schema;
sqlite> .tables 
sqlite> select * from sqlite_master;
Copy the code

The command above deletes the previous database object and verifies that the deletion was successful. Use the.read command to import database information:

sqlite> .read test_backup.sql
sqlite> .tables
schema  test  
sqlite> select * from sqlite_master;
type        name        tbl_name    rootpage    sql                                                 
----------  ----------  ----------  ----------  ----------------------------------------------------
table       test        test        3           CREATE TABLE test(id integer primary key,value text)
index       test_idx    test        4           CREATE INDEX test_idx on test (value)               
view        schema      schema      0           CREATE VIEW schema as select * from sqlite_master   
Copy the code

As you can see, the database information is the same after the.read command is used to restore the database object.

formatting

The CLP provides several formatting options commands that can make the result set and output neat and tidy.

.echo

This command will echo the entered command, as follows:

sqlite> .echo on
sqlite> .tables
.tables
schema  test  
sqlite> select * from test;
select * from test;
1|one
10|ten
11|three
12|four
Copy the code

In the above command, we set the.echo property to ON, then we typed the.tables command and a query command, and then we saw the two commands we typed and the results.

.headers

When this command is set to ON, we should see that the result of the query will display the field names as follows:

sqlite> .headers on
.headers on

sqlite> .tables
.tables
schema  test  

sqlite> select * from test;
select * from test;
id|value
1|one
10|ten
11|three
12|four
Copy the code

As you can see, the output here has two more field names, ID and value, compared to the previous query command.

.nullvalue

When a NULL value is encountered, use the. Nullvalue command to set the output data. If a string NULL is required to represent a NULL value, run the. Nullvalue NULL command to specify it. By default, this null is displayed as an empty string.

sqlite> .nullvalue NULL
.nullvalue NULL

sqlite> insert into test(id) values (15);
insert into test(id) values (15);

sqlite> select * from test;
select * from test;
id|value
1|one
10|ten
11|three
12|four
13|2
15|NULL

Copy the code

In the above code, we first set NULL to display NULL values, and then we insert a piece of data into it. The value of this piece of data is a NULL value. Finally, we print the data and find that the value of the data just inserted is displayed as NULL.

.prompt [value]

This command can change the CLP shell prompt as follows:

sqlite>.prompt sqlite3>
.prompt sqlite3>
sqlite3>
Copy the code

As you can see, the initial Shell prompt is SQLite and the subsequent prompt is SQlite3.

.mode

This command can set the results of several kinds of output format, optional CSV format, the column, HTML, inserts, line, list, tabs, and TCL. Each format serves a different purpose. The default value is list, and the columns are separated by default delimiters when the result set is displayed in list mode. Output in other formats is as follows:

  1. csv:
sqlite3>select * from test;
select * from test;
id,value
1.one
10,ten
11,three
12,four
13.2
15,this_is_null_value
Copy the code

As you can see, the columns are separated by,.

As we learned earlier, we can specify the delimiter ourselves. In addition to specifying the value of.mode, we can also modify the delimiter to achieve the above effect:

sqlite3>.mode list    
.mode list

sqlite3>.separator ,
.separator ,

sqlite3>select * from test;
select * from test;
id,value
1.one
10,ten
11,three
12,four
13.2
15,this_is_null_value
Copy the code

Here need to pay attention to the execution order, and if we executed first. The separator, and execution. Mode list, still is the result of the final output | as a delimiter, if like above. First mode the list, and then perform the separator, and then finally is to, as a delimiter.

  1. column
sqlite3>.mode column
.mode column

sqlite3>select * from test;
select * from test;
id          value     
---------- ----------
1           one       
10          ten       
11          three     
12          four      
13          2         
15          this_is_nu
Copy the code
  1. html
sqlite3>.mode html
.mode html
sqlite3>select * from test;
select * from test;
<TR><TH>id</TH>
<TH>value</TH>
</TR>
<TR><TD>1</TD>
<TD>one</TD>
</TR>
<TR><TD>10</TD>
<TD>ten</TD>
</TR>
<TR><TD>11</TD>
<TD>three</TD>
</TR>
<TR><TD>12</TD>
<TD>four</TD>
</TR>
<TR><TD>13</TD>
<TD>2</TD>
</TR>
<TR><TD>15</TD>
<TD>this_is_null_value</TD>
</TR>
Copy the code
  1. insert
sqlite3>select * from test;
select * from test;
INSERT INTO "table"(id,value) VALUES(1.'one');
INSERT INTO "table"(id,value) VALUES(10.'ten');
INSERT INTO "table"(id,value) VALUES(11.'three');
INSERT INTO "table"(id,value) VALUES(12.'four');
INSERT INTO "table"(id,value) VALUES(13.'2');
INSERT INTO "table"(id,value) VALUES(15.NULL);
Copy the code
  1. line
sqlite3>select * from test;
select * from test;
   id = 1
value = one

   id = 10
value = ten

   id = 11
value = three

   id = 12
value = four

   id = 13
value = 2

   id = 15
value = this_is_null_value
Copy the code
  1. tabs
select * from test;
id	value
1	one
10	ten
11	three
12	four
13	2
15	this_is_null_value
Copy the code
  1. tcl
sqlite3>.mode tcl 
.mode tcl
sqlite3>select * from test;
select * from test;
"id" "value"
"1" "one"
"10" "ten"
"11" "three"
"12" "four"
"13" "2"
"15" "this_is_null_value"
Copy the code

As you can see, the output format of each style is different, and we can set different. Mode properties for the desired output format. For example, if we need to export the data to a CSV file for preview, we can do the following:

sqlite3>.mode csv
.mode csv

sqlite3>.output test_csv.csv
.output test_csv.csv

sqlite3>select * from test;

sqlite3>.output stdout
.output stdout
Copy the code

First we set the current mode to CSV, then we redirect the output to the test_csv.csv file, then execute the query statement, the results of the query statement will be entered into the specified CSV file, then we redirect the output to the screen.

For example, if we want to preview in an HTML file, we can do the following:

//Set the output format to HTML SQlite3>.mode html
.mode html

//Redirects the output to position sqlite3>.output test_html.html
.output test_html.html

//Query data sqlite3>select * from test;

//Redirect the output location to screen SQlite3>.output stdout
.output stdout
Copy the code

After the above operation, we can now get the output HTML file, just open the file directly and do not preview as expected in the form of a table. We need to add some HTML details to the file to preview in the form of a table. The simplest way is to add the

tag directly to the outermost layer.

Export delimited data

We’ve already seen some simple SQLite commands, such as import and export, formatting data, etc. Sometimes we need to import and export data using specified delimiters. We’ve already seen some examples of how to do this. Exporting test data starting with t to a specified.csv file, as shown below:

sqlite> .mode csv
sqlite> .separator ', '
sqlite> .output test_first_t.csv
sqlite> select * from test where value like 't%';
sqlite> .output stdout
Copy the code

With the above command, we successfully exported the data to the test_first_T.csv file.

If we also need to import data from the file into another table with the same structure as the test table, we can execute the following command:

sqlite> .import test_first_t.csv test2
sqlite> 
sqlite> 
sqlite> .mode column
sqlite> .headers on
sqlite> select * from test2;
id          value     
---------- ----------
10          ten       
11          three   
Copy the code

As you can see, we successfully imported the data from the test_first_T.csv file into table test2.