SQL command Encyclopedia

1. View the version information for Sqllite:

2 Creating a Database

3 How to exit the SQLite command line:

Sqlite>.quit or Sqlite>.exit command

4 List The configuration of the current display format

5 Displaying the database table structure

6 inserting data into a table

7 Setting export targets

12 Setting 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.

15 Custom Separators

16 Display the title bar. Header on

Do not show title bar:. Header off

Three 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:

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

Creating a database and opening a database command that already exists is identical and opens if it exists.

3 Creating a data sheet

CREATE TABLE table_name (field Type1,fieldtype1,....);

TABLE_NAME is the name of the data table to create, field X is the name of the fields in the database table, and Typex is the field type.

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

SQL instruction format: All SQL directives end with a semicolon (;), and two minus signs (--) indicate comments

4 Adding data records

INSERT INTO table_name (column field1,field2,....) VALUES (value Val1,val2,....);

Val x is the value that needs to be stored in the field.

For example, add data to the Teacher information table:

It is simple to create a teachers table and add four data to it, setting some constraints, including 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 that meets the criteria 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 all records are deleted if no conditional statement is set.

7 Importing data:. Read data files

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

. 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, ' 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 output lists 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 FIELDASC;

D Descending output data record

SELECT * FROM table 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 fieldbetween val1 and val2;

SELECT * from Cars where cost between 41400and 350000;

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 return some of the columns in the data set to a more intuitive name, such as changing the const to "price of Car"

L, conditional query Select column from table "Where Condition statement"

General conditional statements are Shiite 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, these 5 customers 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.

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 Deleting a data table or index

DROP TABLE table_name;

Drop index index_name;

Sqlite3 Types of data stored

Null: Identifies a null value

Integer: int type

REAL: Floating point

TEXT: String

BLOB: Binary number

Constraints for storing data in Sqlite3

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-empty

Constraint column record cannot be empty, otherwise an error

unique– Unique:

In addition to the primary key, the value of the data that constrains the other columns is unique

check– condition Check:

The value that constrains the column must meet the criteria before it can be deposited

default– Default value:

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 ');

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

ALTER TABLE Table-name

ADD COLUMN column-name Column-type;

SQL command Encyclopedia