Sqlite3 command Overview

Sqlite3 command Overview

1. View sqllite version information:

2. Create a database

3. Exit sqlite command line:

Sqlite>. quit or sqlite>. exit command

4. List the configurations of the current display format

5. display the database table structure

6. insert data into the table

7. Set the export target

12. Set the display mode:. mode.

There are several display modes, the default is the list display mode, we generally use the column Display mode, there are several other display modes to view the mode-related content. help. Look at the figure below. Is it different from the one shown above?

13. What is the format of setting the NULL value:. nullvalue

By default, the NULL value is not displayed. You can set it to what you want.

14 configuration file. sqliterc

If we need to reset the display format every time we enter the command line, it is very troublesome. all settings listed by the show command can be saved to one. in the sqliterc file, it is automatically set every time you enter the command line .. The sqlterc file is stored in the Home directory of the user in Linux and can be saved to any directory in Windows. However, you need to set the environment variable so that the database engine can find it, for more information, see help.

15. Custom Separator

16 display title bar. header on

Do Not Display title bar:. header off

 

Iii. Database and table commands

1. Create a new database: sqlite3 file name

Create a test. db database file and open the console window. The command is as follows:

 

2. Open an existing database: sqlite3 file name

Creating a database is the same as opening an existing database command.

3. Create a data table

Create table table_name (field type1, fieldtype1 ,....);

Table_name is the name of the data table to be created, field x is the field name in the database table, and typex is the field type.

For example, this statement creates a data table that records student information.

 

SQL command format: All SQL commands end with a semicolon (;), and two minus signs (--) indicate comments.

4. Add data records

Insert into table_name (column field1, field2 ,....) Values (value: val1, val2 ,....);

Val x is the value of the field to be saved.

For example, add data to the instructor information table:

It is easy to create a Teachers table, add four data records to it, and set some constraints, including automatically added primary keys and default values.

5. modify data

UPDATE table SET column = 'new value' [WHERE Condition Statement]

The UPDATE statement is used to UPDATE a column in the table. If no conditions are set, this column of all records is updated. If conditions are set, this column that meets the conditions is updated, the WHERE clause is used to set conditions, for example:

6. DELETE data in the delete from table [WHERE Condition Statement]

If the WHERE Condition Clause is set, data records that meet the condition are deleted. If no conditional statement is set, all records are deleted.

7. import data:. read data file

Open notepad, copy the following SQL statements to notepad, save as test. SQL, and enter

. Read test. SQL

Begin transaction; Create table Cars (Id integer primary key, Name text, Cost integer ); Insert into Cars VALUES (1, 'audi ', 52642 ); Insert into Cars VALUES (2, 'mercedes ', 57127 ); Insert into Cars VALUES (3, 'skoda ', 9000 ); Insert into Cars VALUES (4, 'volv', 29000 ); Insert into Cars VALUES (5, 'bentlil', 350000 ); Insert into Cars VALUES (6, 'citroen', 21000 ); Insert into Cars VALUES (7, 'hummer ', 41400 ); Insert into Cars VALUES (8, 'volk', 21600 ); COMMIT; Begin transaction; Create table Orders (Id integer primary key, OrderPrice integer CHECK (OrderPrice> 0), Customer text ); Insert into Orders (OrderPrice, Customer) VALUES (1200, "William "); Insert into Orders (OrderPrice, Customer) VALUES (200, "Robert tson "); Insert into Orders (OrderPrice, Customer) VALUES (40, "Robert tson "); Insert into Orders (OrderPrice, Customer) VALUES (1640, "Smith "); Insert into Orders (OrderPrice, Customer) VALUES (100, "Robert tson "); Insert into Orders (OrderPrice, Customer) VALUES (50, "William "); Insert into Orders (OrderPrice, Customer) VALUES (150, "Smith "); Insert into Orders (OrderPrice, Customer) VALUES (250, "Smith "); Insert into Orders (OrderPrice, Customer) VALUES (840, "Brown "); Insert into Orders (OrderPrice, Customer) VALUES (440, "Black "); Insert into Orders (OrderPrice, Customer) VALUES (20, "Brown "); COMMIT;

8. query data records

A. query and output data records.

Select * from table_name;

B. Limit the number of output data records

If there is too much data in the database, you can limit the number of returned results and set the start position.

Select * from table_name limit val;

C output data records in ascending order

Select * from table_name order by fieldasc;

D. Output data records in descending order

Select * from table order by field desc;

E-condition Query

Select * from table_name where expression;

IN (SET)

Select * from table_name where field in ('val1', 'val2', 'val3 ');

BETWEEN value 1 AND value 2

Select * from table_name where fieldbetween val1 and val2;

Select * from Cars where cost between 41400and 350000;

F. Number of query records

Select count (*) from table_name;

G. differentiate column data

Select distinct field from table_name;

Some field values may be repeated. distinct removes the repeated items and lists each field value in the column.

H alias SELECT column AS Alias, column AS Alias FROM

You can return some columns in the dataset with an intuitive name, for example, changing the Const to "Price Of Car"

L conditional query SELECT column FROM table [WHERE Condition Statement]

Generally, the condition statements are Shiite greater than, less than, or equal to. Here are several special condition statements.

LIKE

-------------------------------

LIKE matches strings with wildcards

Underline _ match a string

Percent % matches multiple strings

LIKE is case insensitive when matching strings

GLOB

J differentiate DISTINCT Columns

The values of some fields may be repeated. For example, in the order table, a customer may have several orders, so the customer's name may be repeated.

Which customers have placed their orders? The following statements differentiate Customer names.

K group by Column

Grouping is similar to the preceding distinction. Differentiation is only to remove duplicate items, while grouping is to calculate statistics for different items.

For example, in the above example, we have divided five customers and these five customers have a total of 11 orders, indicating that many customers have placed more than one order.

The following statement calculates the total cost of each customer on the order.

9 create an index

When there are a large number of records in the data table, the index helps to speed up data table searching.
Create index index_name on table_name (field );
For example, create an index for the stu_no field in the student table:
Create index student_index on student_table (stu_no );

After the index is created, sqlite3 automatically uses the index when querying this field.

10 Delete A data table or index

Drop table table_name;

Drop index index_name;

11 sqlite3 data storage type

NULL: identifies a NULL value.

INTEGER: INTEGER type

REAL: Floating Point Number

TEXT: String

BLOB: Binary Number

12 sqlite3 constraints on data storage

Sqlite has the following constraints:

Primary key-PRIMARY KEY

1) The value of the primary key must be unique and used to identify each record, such as the student's student ID.

2) The primary key is also an index, and it is faster to query records through the primary key.

3) if the primary key is of the integer type, the value of this column can automatically increase

Not null-NOT empty

The constraint column record cannot be blank; otherwise, an error is returned.

UNIQUE-UNIQUE:

Except the primary key, the data values of other columns are restricted to be unique.

CHECK-condition CHECK:

The value of this column must meet the conditions before it can be saved.

DEFAULT-DEFAULT value:

The values in column data are basically the same. Such field columns can be set as default values.

Create table Orders (Id integer primary key, OrderPrice integer CHECK (OrderPrice> 0 ), Customer text );   Create table Friends (Id integer primary key, Name text unique not null, Sex text CHECK (Sex IN ('M', 'F ')));       Create table if not exists Reservations (Id integer primary key, CustomerId integer, Day text ); Insert into Reservations (CustomerId, Day) VALUES (1, '2017-22-11 ');   Create table Books (Id integer primary key, Title text, Author text, Isbn text default 'not available ');

13. How can I insert a column in a created table?

Alter table table-name

Add column column-name column-type;