SQLite Common Commands-additions and deletions to search

First, view the version information:

#sqlite3-version

Second, sqlite3 commonly used commands

1, the current directory to establish or open the Test.db database file, and enter the SQLite command terminal to sqlite> prefix identification:

2. Output Help information:

Sqlite>.help

3. View the database file Information command (note the character '. ' Before the command) ：

Sqlite>.database

4. Exit the SQLite terminal command:

Sqlite>.quit

Or

Sqlite>.exit

Lists the configurations for the current display format:
Sqlite>.show

6. Display database structure:. Schema

Show the structure of the table:. Schema table Name

is actually some SQL statements, they describe the structure of the database,

7. Export data for a table:. Dump table Name

8. Set Export target:

. output file Name

Or

. Output stdout

Run the. Output cars.sql first, and then run the. Dump command and try again? If you want to revert to export to the terminal (standard output), run the. Output stdout

10. Set separator:. Separator delimiter

We can run SELECT * from Cars First, and you can see that the default delimiter is |

Run. Separator: Later, SELECT * from Cars; you can see that the delimiter has become:

11. Display the title bar:. Headers on

12, set the display mode:. Mode

There are several display modes, the default is the list display mode, generally we use the column display mode, there are several other display modes. Help to see the relevant content of mode. Look at the picture below, is it not the same as it appears above?

13. Set the null value to show what it looks like:. nullvalue the null value format you want

By default, null values are not displayed, and you can set them to the way you want them.

14. configuration file. Sqliterc

If we have to reset the display format every time we go to the command line, it's a hassle, actually. All the settings listed in the show command can be saved to a. sqliterc file so that each entry to the command line is automatically set up. The. sqlterc file is saved under Linux in the user's Home Directory, under Windows can be saved to any directory, but need to set environment variables so that the database engine can find it, interested can see help.

Iii. related commands for databases and tables

1. Create a new database: Sqlite3 file name

Create a test.db database file and open the console window with the following command:

Sqlite>sqlite3 test.db

2. Open an existing database: Sqlite3 file name already exists

Creating a new database is exactly the same as opening a database command that already exists, and if the file does not exist in the current directory, it is new, or opens if it exists.

3. Set up a data sheet

CREATE TABLE table_name (field1 type1, Field2 type1, ...);

TABLE_NAME is to create a data table name, FIELDX is the field name in the datasheet, and Typex is the field type.
For example, this statement creates a data table that records student information.

SQL instruction format: All SQL instructions are represented by a semicolon (;) end, and two minus signs (--) are the comments.

4. Adding Data records

INSERT INTO table_name (column field1, Field2, ...) values (value Val1, Val2, ...);

Valx is the value that needs to be stored in the field.
example, add data to the Teacher information table:

It's easy to create a Teachers table and add four data to it, set some constraints, with auto-incremented primary keys, default values, and so on.

5. Modify the data

UPDATE Table Set column = ' new value ' ' WHERE condition statement '

The UPDATE statement is used to update a column in the table, and if the condition is not set, the column for all records is updated, and if the condition is set, the column of the qualifying record is updated, and the WHERE clause is used to set the condition, as in the following example:

6. Delete data delete from table "WHERE condition statement"

If a WHERE condition clause is set, the data record that meets the criteria is deleted, and if no conditional statement is set, all records are deleted

7. Import data:. read Data file

Open Notepad and copy the following SQL statements into Notepad, save as Test.sql, and enter in the command-line environment

. Read Test.sql

All data will be imported into the TEST.DB database.

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, ' Volvo ', 29000);
INSERT into Cars VALUES (5, ' Bentley ', 350000);
INSERT into Cars VALUES (6, ' Citroen ', 21000);
INSERT into Cars VALUES (7, ' Hummer ', 41400);
INSERT into Cars VALUES (8, ' Volkswagen ', 21600);
COMMIT;

BEGIN TRANSACTION;
CREATE TABLE Orders (Id integer PRIMARY KEY, orderprice integer CHECK (orderprice>0),
Customer text);
INSERT into Orders (Orderprice, Customer) VALUES ("Williamson");
INSERT into Orders (Orderprice, Customer) VALUES ("Robertson");
INSERT into Orders (Orderprice, Customer) VALUES ("Robertson");
INSERT into Orders (Orderprice, Customer) VALUES (1640, "Smith");
INSERT into Orders (Orderprice, Customer) VALUES ("Robertson");
INSERT into Orders (Orderprice, Customer) VALUES ("Williamson");
INSERT into Orders (Orderprice, Customer) VALUES ("Smith");
INSERT into Orders (Orderprice, Customer) VALUES ("Smith");
INSERT into Orders (Orderprice, Customer) VALUES (840, "Brown");
INSERT into Orders (Orderprice, Customer) VALUES ("Black");
INSERT into Orders (Orderprice, Customer) VALUES ("Brown");
COMMIT;

8. Query data record

A query outputs all data records
SELECT * FROM table_name;

b limit the number of output data records

If there is too much data in the database, return all can not, you can limit the number of returns, you can also set the return starting position
SELECT * FROM table_name limit Val;

C Output data record in ascending order
SELECT * FROM table_name order BY field ASC;

D Descending Output data record
SELECT * FROM table_name order BY field DESC;

E conditional Query
SELECT * from table_name where expression;

In (collection)
SELECT * FROM table_name where field in (' Val1 ', ' val2 ', ' val3 ');

Between value 1 and value 2
SELECT * FROM table_name where field between Val1 and Val2;

F. Number of query records

Select COUNT (*) from TABLE_NAME;

g, distinguish column data
SELECT DISTINCT field from table_name;
Some of the values of the fields may recur, distinct remove duplicates, and each field value in the column is listed individually.

H, alias SELECT column as alias, column as Alias from

You can give a more intuitive name to some of the columns in the returned data set, such as changing the cost to "price of Car"

I, conditional query Select column from table "WHERE condition statement"

General conditional statements are greater than, less than, equal to, and there are several special conditional statements

Like

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

Like to match a string with a wildcard character

Underline _ matches a string

Percent percent% matches multiple strings

Like match string is case insensitive

GLOB

J Distinguishing DISTINCT columns

Some of the values of the fields may be duplicated, such as in the Order table, where a customer may have several orders, so the customer's name repeats itself.

In the end, which customers placed orders? The following statement distinguishes the customer name.

K Grouping GROUP By column

Grouping is a bit similar to the previous distinction. The distinction is only for the purpose of removing duplicates, and grouping is for the purpose of statistical calculation of different types of items.

For example above, we distinguish 5 customers, the 5 customers have a total of 11 orders, indicating that many customers have placed more than one order.

The following statement counts how much money each customer spends on an order.

Here Sum is the built-in statistical function of SQLite, which is used in this example to ask for the sum of each customer's order price.

Statistical results can also be set to return conditions, but not with the WHERE clause, but with the HAVING clause, as in the following example, return the order total of more than 1000 customers.

9. Building an Index
When you say that a data table has a large number of records, indexing helps speed up finding data tables.
CREATE INDEX index_name on table_name (field);
example, for the Student Table Stu_no field, create an index:
Create INDEX Student_index on student_table (STU_NO);
When Setup is complete, Sqlite3 automatically uses the index when it queries the field.

10. Delete data tables or indexes
DROP TABLE table_name;
Drop index index_name;

Iv. types of sqlite3 stored data
Null: Identifies a null value
Interger: Integer type
REAL: Floating point
TEXT: String
BLOB: Binary number

Five, sqlite3 the restriction condition of storing data
The usual constraints for SQLite are as follows:
PRIMARY Key-PRIMARY key:
1) The value of the primary key must be unique to identify each record, such as student's number
2) Primary key is also an index, the primary key to find records faster
3) If the primary key is an integer type, the value of the column can grow automatically
Not null-non-null:
Constraint column record cannot be empty, otherwise an error
Unique-Single:
The value of the data that constrains the other columns is unique except for the primary key
Check-condition checking:
The value that constrains the column must meet the criteria before it can be deposited
Default-Defaults:
The values in the column data are basically the same, so the field column can be set to the default value

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, ' 2009-22-11 ');

CREATE TABLE Books (Id integer PRIMARY KEY, Title text, Author text,
ISBN text default ' not available ');

How to insert a column in a table that has already been created

ALTER  TABLE   table-name  ADD COLUMN  column-name column-type

SQLite Common Commands-additions and deletions to search