Advanced SQL statement
Huahuahu? 2021-07-20 04:25:32


Changing data

insert record

  • Insert a row
insert into foods (name, type_id) values ('Cinnamon Bobka', 1);

If in insert A value is provided for each column in the statement , You don't have to list , The order is the order in which the table was created .

insert into foods values(NULL, 1, 'Blueberry Bobka');
  • Insert a set of rows Subquery (subqueries) Can be in insert Use in statement . Either as part of the value to be inserted , It can also be used as a complete value to insert .
insert into foods
values (null,
       (select id from food_types where name='Bakery'),
       'Blackberry Bobka');

As part of .

select * from foods where name like '%Bobka';

As a whole .

  • Insert multiple columns
insert into foods
select last_insert_rowid()+1, type_id, name from foods
where name='Chocolate Bobka';

This creation method (Create Table As Select), You lose constraint information , Including auto increment column 、indexes、UNIQUE Constraints, etc .

update record

update table set update_list where predicate;

delete records

delete from table where predicate;

Data integrity (data integrity)

Data integrity is value creation and retention table The relationship between . There are four kinds of completeness

  1. domain integrity. Data in column
  2. entity integrity. The data of the rows in the table .
  3. referential integrity. The relationship of columns between tables , For example, foreign keys .
  4. user-defined integrity.catchall for everything else.

Data integrity is achieved through constraints . stay SQLite in , Data integrity includes support for conflict resolution .

entity integrity

The main value is to ensure that every data can be addressed . Principal values include one or more columns , Among them is unique constraint .

  • unique constraint
  • primary key constraint . establish table It's going to be created , Whether you define it or not . The list is rowid, There are two aliases _rowid_ and oid.

    If the constraint of a column is integer primary key, that SQLite A default value will be created for this column , Make sure the value is different from the other lines . This is also true. rowid Another name for . because SQLite Use a sign 64 Bit integer to store the main value , So the maximum of the principal value is 9,223,372,036,854,775,807.

    • If you delete a record , This rowid Will be recycled . therefore , The newly created rowid It doesn't have to be strictly incremental .
    • If you want to rowid For strictly incremental , Can be in integer primary key Add after autoincrement keyword .
    • If the main value definition includes autoinrement, that rowid It won't be reused , Only values larger than the current maximum will be generated .SQLite Will keep the biggest rowid In system table in , be called sqlite_sequence. If it is greater than int64max, When you insert it, it produces SQLITE_FULL error .

      sqlite> select * from sqlite_sequence;
      name        seq
      ----------  ----------
      maxed_out   10
      sqlite> insert into maxed_out values(2120, 'works');
      sqlite> insert into maxed_out values(null, 'works');
      Error: database or disk is full

Domain integrity

Include type and range Two aspects .

- The default value is

default Keywords provide default values . There are three reserved words

    - current_time. (HH:MM:SS) Format
    - current_data. (YYYY-MM-DD) Format
    - current_timestamp. (YYYY-MM-DD HH:MM:SS) Format 
  • NOT NULL constraint
  • check constraints
    check Constraints are checked before any changes take effect . Triggers can do check The function of constraints , And more can be done .

Foreign key constraints

create table table_name
( column_definition refernces foreign_table(column_name)
on {delete|update} integrity_action
[not] deferrable [initially {deferred|immeiate},]

integrity_action There are five kinds of .

  1. set null
  2. set default
  3. cascade
  4. restrict
  5. `no action


Collation It refers to how texts are compared .

  1. binary. Use memcmp() Function comparison
  2. nocase. Case case .
  3. reverse. reverse of binary collation.

collate The keyword defines the... Of a line collation

Storage class

  1. integer. vary in size 1,2,3,4,6,8 bytes.SQLite Will judge the size of the space according to the size of the value .
  2. real. There is a fractional part or an exponential part , The storage mode is 8 Byte float .
  3. text. Support all kinds of character encoding (UTF8,UTF16).
  4. bolb. Binary large object.x'ABCD' start .
  5. NULL.

Different... Can be included in a column storage class Value . The comparison is as follows :

  1. NULL Minimum .NULL There is no specific order between .
  2. integer and real second .
  3. text second .text The comparison is made by collation decision .
  4. blob Maximum . Use memcmp() Function comparison .


view It's a virtual table , Also known as derived tables, Because values are generated by other tables , It is generated dynamically when it is used .

create view name as select-stmt

view stay SQLite China does not support it. update, You can use triggers to achieve the same effect .

indexes( Indexes )

Indexes are used to speed up queries in some cases .

Indexes increase the size of the database , Because the columns that need to be indexed are copied .

The index needs to be maintained . So it reduces the insertion 、 The speed of update and other operations .

create index [unique] index_name on table_name (columns)

unique Will add constraints to indexes and index columns .

drop index index_name


create [temp|temporary] trigger name 
[before|after] [insert|delete|update|update of columns] on table
  • update trigger You can specify trigger conditions for certain columns .
    create trigger name [before|after] update of column on table action
  • Error handling
    raise(resolution, error_message);
    resolution It's a conflict resolution strategy (abort,fail,ignore,rollback...)
  • Renewable view trigger Can be in view On the use of instead of Keyword definition

Business (transactions)

A transaction defines a set of SQL The boundaries of command , This group of commands or all of them succeeded , Or all failure . Database integrity atomic principle .

transaction scopes

The transaction has to do with three commands

1. begin
2. commit
3. rollback
Default to each SQL Statement runs in its own transaction , Every statement is rolled back after success or failure . This type of operation is called autocommit mode

SQLite Support savepoint and release command .
savepoint The user sets a point in the transaction ,SQLite Sure revert To this point .

savepoint justincase
rollback [transaction] to justincase

Conflict resolution

The order of severity is as follows

  1. replace.
  • unique constraint . Remove old Columns , Insert a new column .SQL The operation continues .
  • not null constraint . NULL The value is replaced by the default value . If there is no default value , Use abort Strategy .
  • Delete does not trigger .
ignore. When a constraint is violated , Order to go on , The trigger violation line remains unchanged .fail. When a constraint is violated ,transaction end , Changes that have occurred will not be rolled back .abort. Restore the operations that have been performed by the current command ,transaction end . Default policy .rollback. abort The current command and the whole transaction.

update or resolution table set (value_list) where predicate  

The database lock

Locks and transactions are closely related .SQLite Using coarse grained locks , When one session When writing data to the database , Other session They're locked in .

  1. unlocked
  2. shared. When reading a database, you need to get shared lock
  3. reserved. Get... Before you write data reserved lock. obtain reserved lock after , It's time to write data , But it won't be written to disk , Will be written to the cache .
  4. pending. After writing the data , To get exclusive lock Before you get pending lock. At this time, other shared locks cannot be acquired , Can continue to read .
  5. exclusive. After all the other shared locks are over , You can get mutex . All cache changes are written to memory .


Transaction type

  1. deferred. Don't get any locks , Until you have to get .( default )
  2. immediate. obtain reserved lock When begin At the beginning of the .
  3. exclusive. Get the mutex at the beginning of the transaction .

When there is only one session when , Use the default .

Database management

attaching databases

attach [database] filename as database_name

cleaning Databases

  • reindex. rebuild index.
  • vacuum. Clean up unused space by rebuilding database files .

Database configuration

SQLite No profile , All the configuration is through pragmas Realization .

Database connection cache size

One session How many database pages can be cached in memory (pages).

sqlite> pragma cache_size = 10000;
sqlite> pragma cache_size;

Get database information

  • database_list
  • index_info
  • index_list
  • table_info

Write synchronously

  1. FULL There will be a pause at the critical moment , Make sure the data has been written to disk . If the operating system crashes or loses power , The database will still not be damaged after restart .
  2. Normal It's going to pause at most critical moments . It may be damaged after power failure .
  3. Off After the data is handed over to the operating system, it will continue to execute . It may speed up the operation . If the program crashes , Data is secure . If the operating system crashes or the computer loses power , The database can be corrupted .

Temporary Storage

temp_store:, default,file or memory. The location of the temporary data

temp_store_directory:, The location of the temporary data storage file .

page size, encoding, autovacuum

You must set... Before creating the database .


system catalog

sqlite_master Yes, including the table 、view、index、trigger System table of .

View the query plan (viewsing query plans)

sqlite> explain query plan select * from sqlite_master;
selectid    order       from        detail
----------  ----------  ----------  ------------------------
0           0           0           SCAN TABLE sqlite_master

Please bring the original link to reprint ,thank
Similar articles