Sqllite Concise Tutorials

SQL DML and DDL
SQL can be divided into two parts: Data manipulation Language (DML) and data definition language (DDL).
Note: the "--" double minus sign is the line comment
SQL (Structured Query language) is the syntax for executing queries. However, the SQL language also contains syntax for updating, inserting, and deleting records.
The query and update Directives form the DML portion of SQL:
SELECT-Get data from a database table
Update-updating data in a database table
Delete-Deletes data from the database table
INSERT into-inserts data into a database table
The Data definition language (DDL) portion of SQL gives us the ability to create or delete tables. We can also define indexes (keys), specify links between tables, and impose constraints between tables.
The most important DDL statement in SQL:
Create database-Creating new databases
ALTER DATABASE-Modify databases
CREATE table-Creates a new table
ALTER TABLE-Change (change) database table
drop table-Delete tables
Create index-Creating indexes (search key)
Drop INDEX-Delete indexes
Data manipulation Language (DML)
operator annotation operand operator annotation operand
|| String connection string < relationship less than full
* Arithmetic multiplication number <= relation is less than equal to full
/arithmetic in addition to numbers > relationships greater than full
The% arithmetic takes the remainder number >= the relation is equal to the whole
+ Arithmetic plus digital =,== relationship equals full
-Arithmetic minus number!=,<> relationship is not equal to full
<< position arithmetic right displacement digit-take negative number
>> bit arithmetic left displacement number + positive number
& bit arithmetic and numbers! (not) non (not) number
| Bitwise arithmetic or number ~ bit XOR or number
Operator comments
and Conditions and
or condition or
Like pattern matching
Between range value matching
In matches multiple values
As alias definition
Limit limit result set record count
Order by sort
is [NOT] null to determine whether the field is or is not NULL


And and OR operators
And and or can combine two or more conditions in a where sub-statement. If both the first condition and the second condition are true, the AND operator displays a record. If only one of the first and second conditions is true, the OR operator displays a record.

LIMIT operator
The LIMIT clause is used to specify the number of records to return. The LIMIT clause is useful for large tables that have thousands of records.
WHERE expression LIMIT Number

Like operator
The LIKE operator is used to search for a specified pattern in a column in a WHERE clause. Like match pattern, not-like mismatch pattern, "%" can be used to define wildcards (multiple letters) "_" Single Letter ("__" double underline to match a kanji).
Grammar:
WHERE field [not] like pattern string

In operator
The in operator allows us to specify multiple values in the WHERE clause.
Grammar:
WHERE field name in (value 1, value 2 ...)

Between operator
operator between ... and selects a range of data between two values. These values can be numeric, text, or date.
Grammar:
WHERE field name between start value and terminating value

Alias (AS)
By using SQL, you can specify the alias (as) for the field and base table, or change the field/base table display name.
Grammar:
Field name as field alias
base table name as base table alias

ORDER by statement
The order BY statement is used to sort the result set based on the specified column. The order BY statement sorts records by default in ascending order. If you want to sort records in descending alphabetical order, you can use the DESC keyword and the ASC keyword to reverse the numbers.
Grammar:
WHERE expression ORDER by field name [desc| ASC]

SELECT statement
Select statements are used to select data from a table, and the results are stored in a result table, called a result set.
SQL SELECT Syntax:
SELECT Field name | table name. Field name |*| DISTINCT field name from base table name

DISTINCT keywords
In a table, a field may contain duplicate values. That's not a problem, but sometimes you might want to just list different values (distinct). Keyword DISTINCT is used to return only different field values.
Grammar:
SELECT DISTINCT field name from base table name

WHERE clause
To conditionally select data from a table, you can add a WHERE clause to the SELECT statement.
Grammar:
SELECT field from base table name WHERE expression

Update statement
The Update statement is used to modify the data in the table.
Grammar:
UPDATE base table name SET field = expression [, field 2 = Expression ....] WHERE expression (used to determine a field or set of fields)

DELETE statement
The DELETE statement is used to delete rows in a table.
Grammar:
DELETE from base table name WHERE expression
You can delete all rows without deleting the table. This means that the structure, properties, and indexes of the table are complete:
DELETE from base table name

INSERT into statement
The INSERT INTO statement is used to insert a new row (record) into the table. There are two basic forms of INSERT statements. A keyword with the "VALUES" that inserts a new row into an existing table. If you do not define a field list, the number of values will be the same as the number of fields in the table. Otherwise, the number of values must be the same as the number of fields in the field list. Fields that are not in the field list are assigned a default value or null (when no default value is defined).
The second form of insert gets the data from the SELECT statement. If no field list is defined, the number of fields from select must be the same as the number of fields in the table, or it should be the same as the number of fields in the Defined field list. The result of each row of select Inserts a new entry into the table. The select can be simple or composite. If the SELECT statement has an ORDER BY clause, order BY is ignored.
When using this command, an alternative constraint conflict determination algorithm can be defined using the optional on conflict clause. For more information, see on CONFLICT. To be compatible with MySQL, you can use replace instead of INSERT OR Replace.
Grammar:
INSERT into base table name [(Field 1, Field 2,...)] VALUES (field value 1, field value 2,....)
We can also specify the fields for which you want to insert data:
INSERT into base table name [(Field 1, Field 2,...)] VALUES SELECT statement

UNION operator
The UNION operator is used to combine the result set of two or more SELECT statements.
Note that the SELECT statement inside the UNION must have the same number of fields. A field must also have a similar data type. Also, the order of the columns in each SELECT statement must be the same.
Grammar:
SELECT field from base table
UNION
SELECT field from base table
By default, the UNION operator chooses a different value. If duplicate values are allowed, use UNION all.
SELECT field from base table
UNION All
SELECT field from base table

Data definition language (DDL)
A total of 7 SQL constraints:
SQL not NULL constraint
The NOT NULL constraint enforces that the column does not accept null values. A NOT NULL constraint forces a field to always contain a value. This means that if you do not add a value to a field, you cannot insert a new record or update it.
SQL NULL Constraint
A NULL value is an unknown data that is missing. By default, the columns of the table can hold NULL values.
SQL UNIQUE constraints
Unique constraints uniquely identify each record in a database table. Both the unique and PRIMARY KEY constraints provide a unique guarantee for a column or column collection. PRIMARY KEY has a UNIQUE constraint that is automatically defined.
Note that each table can have multiple UNIQUE constraints, but there can be only one PRIMARY KEY constraint per table.
SQL PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a database table. The primary key must contain a unique value. Primary key columns cannot contain NULL values. Each table should have a primary key, and each table can have only one primary key.
SQL autoincrement constraints
We usually want to automatically create a value for the primary key field each time we insert a new record. We can create a autoincrement field in the table. Auto-increment with a starting value of 1.
SQL CHECK constraints
A CHECK constraint is used to limit the range of values in a field. If you define a CHECK constraint on a single column, the column only allows a specific value. If a CHECK constraint is defined on a table, the constraint restricts the value in a specific field.
CHECK (field value range expression [and field value range expression ...])
SQL DEFAULT [DI?F?:LT] Constraints
The default constraint is used to insert defaults into the column. If no other value is specified, the default value is added to all new records.
Default Defaults

CREATE TABLE Statement
The CREATE TABLE statement is used for creating tables in the database.
Grammar:
CREATE Table base table name
(Field 1 data type constraint list [, field 2 data type constraint list] ...)

ALTER TABLE Statement
The SQLite version of the ALTER TABLE command allows the user to rename or add new fields to an existing table and cannot remove fields from the table.
The RENAME to syntax is used to rename table names. This command cannot be used to move tables between attached databases, and tables can only be renamed in the same database. If the tables that need to be renamed have triggers or indexes, they still belong to the table after renaming. However, if a view is defined, or if the trigger executes a statement that mentions the name of the table, they are not automatically changed to use the new table name. To make this kind of modification, you need to undo it manually and rebuild the trigger or view with the new table name.
The add [COLUMN] syntax is used to add a new field to an existing table. The new field is always added to the end of the existing field list, and [COLUMN] is a read-only property. The field definition can be any form that the defined field in the Create table allows, and is subject to the following restrictions:
· A field cannot have a primary key or a unique constraint.
· Fields cannot have these default values: Current_time, current_date, or Current_timestamp
· If a NOT NULL constraint is defined, the field must have a non-empty default value.
The ALTER table statement's execution time is independent of the amount of data in the table, which is the same as when you operate a table with 10 million rows, and when you manipulate a table with only one row.
After you run the Add column against the database, the database will not be readable by SQLite 3.1.3 and earlier, unless you run the vacuum command.
Grammar:
ALTER Table Table Name
RENAME to renamed table name | ADD [COLUMN] Field definition statement (field name data type constraint)

DROP TABLE Statement
The DROP TABLE statement deletes the table created by the CREATE TABLE statement. The table is completely removed from the database structure and the disk file and cannot be recovered. All indexes of the table are also deleted at the same time. The DROP TABLE statement does not reduce the size of the database file in the default mode. The space is left for later insert statements. You can use the VACUUM command to release the space that is generated by the deletion. If the autovacuum mode is turned on, the space is automatically freed by the drop table. If the optional if EXISTS clause is used, no error will occur if the deleted table does not exist.
Grammar:
DROP table [IF EXISTS] base table name

CREATE INDEX Statement
The CREATE INDEX statement is used for creating indexes in the table. Indexes enable database applications to find data faster without reading the entire table. You can create indexes in a table to query data more quickly and efficiently. Users cannot see the index, they can only be used to speed up search/query.
Note: Updating a table that contains an index requires more time than updating a table that does not have an index, because the index itself needs to be updated. Therefore, it is ideal to create indexes only on columns (and tables) that are often searched.
Grammar:
CREATE [UNIQUE] Index name
On base table name (field [desc| ASC] [, Field 2 [desc| ASC]])
Unique value is not unique; DESC character descending; ASC Number Descending

DROP INDEX Statement
The DROP INDEX statement deletes the index created by the CREATE INDEX statement. The index is completely removed from the database structure and the disk file, and the only recovery method is to re-enter the corresponding CREATE INDEX command. The DROP TABLE statement does not reduce the size of the database file in the default mode. The space is left for later insert statements. You can use the vacuum command to release the space that is generated by the deletion. If the autovacuum mode is on, the space is automatically released by the drop Index.
Grammar:
DROP index [IF EXISTS] Indexed name

CREATE VIEW Statement
In SQL, a view is a table of visualizations based on the result set of an SQL statement.
The view contains records and fields, just like a real table. A field in a view is a field from a real table in one or more databases. We can add SQL functions, WHERE, and JOIN statements to the view, and we can also submit the data as if they came from a single table.
Note: Views always display the most recent data. Each time a user queries the view, the database engine rebuilds the data by using SQL statements.
Grammar:
CREATE View name as SELECT query statement

DROP VIEW Statement
The DROP VIEW statement deletes the view created by the CREATE view. The view is removed from the database schema and the data in the table is not changed.
Grammar:
DROP View Name

GROUP by statement
The GROUP BY statement is used to combine aggregate functions to group result sets based on one or more fields.
HAVING clause
The addition of the HAVING clause in SQL is because the WHERE keyword cannot be used with the aggregate function.

Common Sqllite built-in functions
Common mathematical functions
ABS (x) returns the absolute value of the parameter x.
AVG (x) returns the average of a set of non-null X. Non-numeric values are treated as 0. The result of AVG () is always a floating-point number, even if all input variables are integers.
COUNT (*) returns the number of rows in the group.
COUNT (x) returns the number of times that X is a non-null value in a set.
Max (X) returns the maximum value in a group. Size is determined by the usual sorting method.
MIN (X) returns the smallest non-null value in a group. Size is determined by the usual sorting method. Returns null only if all values are empty.
SUM (X) returns the number of all non-null values in a set. If there is no non-empty line, sum () returns Null,sum () can be an integer, and is accurate when all non-null inputs are integers. SUM () returns a floating-point number close to true if any of the inputs of sum () are neither integers nor null, or when an overflow of integer type is generated in the calculation.
Total (X) returns the number of all non-null values in a set. If there is no non-empty line, total () returns 0.0, and Total () is a floating-point number in the return value.
Max (x, y,...) Returns the maximum value. A parameter can be not just a number, it can be a string. The order of size is determined by the usual sorting rules. Note that Max () is a simple function (returns the maximum value of a set of fields) when there are 2 or more parameters, but when only one parameter is given it becomes the aggregate function (the maximum value of the field is returned).
Min (x, y,...) Returns the minimum value. With Max (x, y,...) Similar.
Random (*) returns a randomly-integer between 2147483648 and +2147483647.
Round (x) rounding integer x
Round (x, Y) rounds × to preserve the Y-bit after the decimal point. If the y parameter is omitted, the default is 0

Common String Functions
Ifnull (x, y) determines whether x is empty, returns Y, and no returns X.
Length (x) returns the lengths of X, measured in characters. If SQLite is configured to support UTF-8, the number of UTF-8 characters is returned instead of bytes.
Lower (x) returns the lowercase version of all characters of the X string. Good support for UTF-8 characters is not available.
Upper (x) returns the uppercase version of all characters of the X string. Good support for UTF-8 characters is not available.
Nullif (x, y) returns null if the two arguments do not return X at the same time.
Sqlite_version (*) returns the version number string for the SQLite library that is running. such as "2.8.0".
substr (x, y, z) returns substrings in the input string X beginning with the nth character and Z characters long. The left-most character ordinal of x is 1. If Y is negative, the number is from right to left. If the SQLite configuration supports UTF-8, then "character" represents the UTF-8 character rather than the byte.
typeof (X) returns the type of an expression X. The return value may be "null", "integer", "real", "text", and "blob". The type processing of SQLite is described in the data type of SQLite3.
Time function (5 total):
Date (DateTime string or field, modifier, modifier, ...)
Time (DateTime string or field, modifier, modifier, ...)
DateTime (Date time string or field, modifier, modifier, ...)
Julianday (Date time string or field, modifier, modifier, ...)
Strftime (Date time format, datetime string, modifier, modifier, ...)
The above five functions require a DateTime string to be used as arguments, followed by 0 to more modifier parameters. The Strftime () function also requires a DateTime format string to do the first argument.

The date () function returns an "YYYY-MM-DD" as the format;
The time () function returns an "HH:MM:SS" as the format;
The datetime () function returns a DateTime in the form of "Yyyy-mm-dd HH:MM:SS";
The Julianday () function returns a number of days starting from November 24, 4714 GMT BC;
The strftime () function returns a formatted date and time, which can be formatted with the following symbol for the date and time:
%d January the day of the first 01-31
%f decimal form of the second, SS. SSSS
%H hours 00-24
%j Day of the year 01-366
%J Julian Day Numbers
%m Month 01-12
%M min 00-59
%s The number of seconds to calculate from the date of 1970-01-01
%s seconds 00-59
%w Week, 0-6,0 is Sunday
%W Week of the year 00-53
%Y year 0000-9999
Percent%%
The other four functions can be represented by the strftime () function:

Date (...) Strftime ("%y-%m-%d",...)
Time (...) Strftime ("%h:%m:%s",...)
DateTime (...) Strftime ("%y-%m-%d%h:%m:%s",...)
Julianday (...) Strftime ("%J",...)
Date time strings can be in the following formats:
Yyyy-mm-dd
YYYY-MM-DD hh:mm
YYYY-MM-DD HH:MM:SS
Yyyy-mm-dd HH:MM:SS. Sss
yyyy-mm-ddthh:mm
Yyyy-mm-ddthh:mm:ss
Yyyy-mm-ddthh:mm:ss. Sss
hh:mm
HH:MM:SS
HH:MM:SS. Sss
Now
DDDD. DDDD
The "T" in the fifth to seventh format is a character that splits the date and time, and the eighth to tenth format represents only the time of 2000-01-01, and the 11th format ' Now ' represents the return of a current date and time, using GMT (UTC), and a 12th format that represents a Julian Day Numbers.

Modifier

Date and time you can change the date or time using the following modifier:

NNN years (year increment date)
NNN months (month increment date)
NNN days (day increment date)
NNN hours (hourly increment time)
NNN minutes (minutes increments time)
NNN. NNNN seconds (seconds increment time)
Start of month (the beginning of the current month, number 1th, D)
Start of year (beginning of current years January 1, D)
Start of week (start of current week, D)
Start of day (the beginning of the current days, T)
Weekday N (the date of the next one weeks is N, D)
Unixepoch (returns the number of seconds starting from 1970-01-01)
LocalTime (local time Datatime)
Utc
Here are some examples:

Current time of the computer
SELECT date (' Now ')
The last day of the current month of the computer
SELECT date (' Now ', ' start of Month ', ' +1 month ', '-1 day ')
Calculates the date and time that Unix timestamp 1092941466 represents
SELECT datetime (' 1092941466 ', ' Unixepoch ')
Calculates the local date and time represented by the UNIX timestamp 1092941466
SELECT datetime (' 1092941466 ', ' Unixepoch ', ' localtime ')
Computer current UNIX Timestamp
SELECT strftime ('%s ', ' Now ')
Number of days between two dates
SELECT jolianday (' Now ')-jolianday (' 1981-12-23 ')
Two number of seconds between datetime
SELECT julianday (' Now ') *86400-julianday (' 2004-01-01 02:34:56 ') *86400
Calculate the date of the first Tuesday of this year October
SELECT date (' Now ', ' start of Year ', ' +9 months ', ' weekday 2 ');

Sqllite Concise Tutorials