Reading notes of the authoritative guide for SQLite (i)

A constant

string constants, enclosed in single quotation marks. If there is a single quotation mark in the constant, use two single quotation marks to represent it. Case sensitive)

Numeric constants

Binary constants

Two key words

Keyword is not case sensitive

Three notes

Single-line comment using--xxxxxxx

Multi-line annotations using/*xxxxxx*/

Four Create a table

CREATE [TEMP | TEMPORARY] TABLE table_name (column_definitions [Constraints,]);

Five modification tables

ALTER TABLE table_name {RENAME to New_table_name | ADD COLUMN column_definitions};

Six-relationship operation

Basic operations

restriction (limit)

Projection (projection)

Cartesian product (Cartesian product)

Union (Union)

Difference (poor)

Rename (rename)

Additional actions

Intersection (Cross)

Natural Join (Natural connection)

Assign (assigned value)

Extended operations

Generalized Projection (generalized projection)

Outer join (left OUTER join)

Right Outer join (left OUTER join)

Full Outer Join (All-out connection)

Seven like and GLOB

SELECT column_name from table_name WHERE column_name like ' xxx ';

The pattern followed by the like can be string-matched.

% can match any of 0 or more characters. Match for greed. Casing is not sensitive.

'%x ' means ending with X

' x x ' means start with X.

'%x% ' means include X

_ can match any one of the characters.

You can use not like ' xxx ' to indicate that a pattern is not included.

PS: ' xx ' represents a pattern that is not only matched to columns of type text, but can also be matched to columns of type Integer.

You can change the like to Glob, which is very similar, but glob case sensitive, and * and _ to represent.

Eight qualifying and sorting

SELECT column_name from table_name ORDER by column_name [asc| DESC] LIMIT 1...9 OFFSET 1....9;

Order by indicates a sort. The default is ascending, and Desc is added in descending order. You can have multiple columns, separated by commas. The first field is repeated, sorted according to the second field, and so on.

The number after LIMIT indicates the number of rows returned in the sort result.

The number after offset indicates the number of skipped rows. For example: OFFSET 1 means skipping a line and starting with the second row.

You can write a brief limit 2,3 and limit 3 OFFSET 2 the same meaning.

Nine functions and aggregations

Function: You can take Upper (column_name) as a column, execute select UPPER (name) ... You can change all the lines in the Name column to uppercase.

Aggregation: Understand to do some action on each row in a table. Executes select COUNT (*) from table, which returns the number of rows in the table tables.

Ten groups

"The main part of aggregation is grouping, that is, aggregation is not just the ability to calculate aggregate values for an entire result set, but also to divide the result set into groups and then calculate the aggregated values for each group. ”

Select Type_id,name from foods Group by type_id;

This sentence is grouped according to TYPE_ID, and name returns the last bar of the group.

Select Type_id,count (*) from foods Group by type_id;

This sentence is still grouped according to type_id, and COUNT (*) is a reorganization of the aggregation job.

11 Removing duplicates

SELECT DISTINCT column_name from table_name;

The sentence gets all first, then the delete operation

12 Multi-Table connection

Inner Connection Example:

SELECT * FROM Foods INNER joins food_types on foods.id = = Food_types.id;

Cross Connect Example:

SELECT * from Foods,food_types;

Left JOIN Example:

SELECT * FROM Foods left OUTER joins food_types on foods.id = = Food_types.id;

Implicit connections should be avoided, although they can be implemented concisely.

Standard form:

Select heading from left_table join_type right_table on join_condition;

Insert Record

INSERT into table_name (column_list) VALUES (value_list);

Insert a line example

INSERT into foods (name, type_id) VALUES (' Cinnamon Bobka ', 1);

Column_list can contain primary key fields, but to ensure uniqueness of the inserted primary key field, the "PRIMARY key must be unique" is reported.

Last_insert_rowid (); The function returns the last growth value.

Insert a set of line sentences

INSERT into Foods

SELECT NULL, type_id, name from foods where name = ' Choco ';
Insert multiple lines of sentences

CREATE TABLE Foods2 as SELECT * from FOODS;

Update data

UPDATE table_name SET update_list WHERE predicate;

For example:

Update foods Set name = ' Apple ', food_types = 3 where name = = ' Banana ';

Delete data

DELETE from table_name WHERE predicate;

For example:

Delete from foods where ID > 500;

Data integrity

Domain Integrity entity Integrity referential integrity user-defined integrity

Entity integrity

A primary key consists of one or a set of fields with at least a unique constraint.

Uniqueness Constraint--Unique

Primary key

If not defined, the system is automatically defined. is a 64bit shaping field named rowID, also known as _rowid,oid. The SQLite primary key field provides self-growing properties. If you define a field type of integer primary Key,sqlite will create a default value for the field and ensure its uniqueness. In fact, this field is the alias of rowID. The maximum value is 2 of 64, and when the maximum value is reached, unused values are searched. Therefore, the newly created rowid are not always incremented.

AutoIncrement will organize SQLite to reclaim the primary key and stop when the maximum value is reached.

Primary key The system maintains an ROWID field internally, even if no integer value is used.

For example:

Sqlite> CREATE table Pkey (x text, y text, primary key (Y X));
sqlite> INSERT into Pkey values (' x ', ' Y ');
sqlite> INSERT into Pkey values (' x ', ' X ');
Sqlite> Select rowID, x, y from Pkey;

Rowid|x|y
1|x|y
2|x|x

Domain integrity

Defaults (default)

If you insert a record with an INSERT statement without setting a value for the field, the keyword default provides a default value for the field.

is a policy that handles non-null.

Format is default value

For example, name not NULL when the default Nuknow INSERT statement is the value of the specified name field, and ' Nuknow ' is automatically used as the value of name.

In addition, default accepts three predefined reserved words for Current_time,current_data,curremt_timestamp

Not NULL constraint

Encountering a null value will cause an error.

Check Constraint

You can define an expression to determine the value, for example: check (Old > 0) indicates that the age must be greater than 0, inserting-1 years of age will be an error.

FOREIGN KEY constraints

The syntax is as follows

CREATE TABLE table_name

(Column_definition references foreign_table (column_name)

On {delete | update} integrity_action

[NOT] deferrable [initially {deferred|immediate},]

);

Sorting rules

The keyword is: collate

Collate nocase case insensitivity A==a

Storage class

SQLite has 5 primitive data types, called storage classes.

Integer integers have no decimal point assigned to this value.

Real real numbers have decimal points ....

Text text is enclosed in quotation marks ....

Blob binary large object x ' ABCD ' ....

NULL indicates no value with null description value ...

typeof () returns the type of the value.

A field can have different data types

Sorting rules are as follows

Blob > Text > Integer real (sort by size of value) > NULL

View

Here is an introductory article on views

Introduction to the View

The definition view syntax is as follows:

Create view name as SELECT-STMT;

The syntax for deleting a view is as follows:

Drop view name;

Index

To create an index syntax:

CREATE INDEX [unique] index_name on table_name (columns);

To delete an index:

Drop index index_name;

Collation of the Index

Create syntax:

CREATE index Foods_name_idx on foods (name collate nocase);

Trigger

Grammar:

Create [temp|temporary] Trigger name

[Before|after] [Insert|delete|update|update of columns] on table

Action

You can use triggers to implement updatable views in SQLite

Transaction

A transaction is controlled by 3 commands: Begin commit Rollback

Begin a transaction, before the end of the connection, without a commit, all commands are revoked, and rollback restores all operations after begin.

SavePoint XXX Save a location, rollback to XXX, return to a location

Database lock

SQLite has five different lock states: Unlocked shared reserved Pending exclusive

Transaction type

SQLite has three different types of errors, in order to avoid deadlocks, because SQLite allows multiple connections to read data at a time, but allows only one connection to write data.

begin[deferred | immediate | exclusive] transaction;

Basic rule: If you are using a database that has no other connection, it is enough to begin with, but the database should use begin immediate or begin exclusive if there are other connections that will perform write operations on the database.

Database management

Attaching a database

attach [database] filename as database_name;

Detaching a database

Detach [Database] database_name;

Reading notes of the authoritative guide for SQLite (i)