Database is generally divided into relational and object type, the relationship is the mainstream, object-based database is directly put objects into the database.
Common relational database:
pc:oracle MySQL SQL Server DB2
Embedded/Mobile: SQLite
This article mainly introduces the basic situation of SQLite and SQL Basic statement.
The 1.SQLite is virtually untyped, but the surface partition data is of the following types. integer ShapingReal floating-pointtext literalblob binary DataThere are types just for the sake of specification and readability, no substantive use.
2.SQL language is a language that defines and operates data in a relational database. common operations for additions and deletions (crud,create,read,update, delete),SQL is case insensitive. note the strings in the database are in single quotation marks, and the statement specification is capitalized on the keyword.
3. Database Statement ClassificationData definition statements (ddl:data definition Language), including create and drop operations, for creating and deleting tables, respectively. Data manipulation statements (Dml:data manipulation Language), including inserts, update, delete, and so on. data query Statement (dql:data query Language), including select operations.
4. Common StatementsTo Create a table:
CREATE table IF not EXISTS name (field 1, type, field 2, type,...);
To Delete a table:
DROP table IF EXISTS name;
conditional statements:the WHERE field (=,!=,>,<,is,is not,) value;Condition Statement: Condition 1 and/or condition 2;
Sorting criteria:ORDER by field Asc/desc;
additions and deletions to check:
INSERT into table name (Field 1, Field 2,...) Values (value 1, value 2,...);
UPDATE Table name SET Field 1 = value 1, field 2 = value 2 condition;
DELETE from table name condition;
wildcard characters: *
alias as: Field name as Alias, alias Support Chinese.
counts the number of records: Count function, received parameter is field, General put * canFor example, SELECT count (*) from table name. Note that this write will only count the number of records at a time, and will not be repeated because of the number of fields.
Paging Querylimit can precisely control the number of query results. the SELECT field from the table name is LIMIT x, y,/* To skip the previous X data and fetch y data. */commonly used for paging queries, such as 5 per page( 0,5) (5,5) (10,5) Such treatment, which note the coherence of the numbers without jumping 1. page N: (5* (N-1), 5). omitted notation: limit y, indicating x=0
Constraints① When creating a table specifies that a field is non-null, unique, and that multiple conditions are directly tied without any modifiers. CREATE Table table name (field FirstName type not NULL UNIQUE);② Specifies the default default value, which can also be written in parallel. ③primary key for the primary key, the default is an integer, generally want to have a primary key as the ID to be the only sign of the data, in order to let the ID from the growth, followed by the AutoIncrement. ④ Composite primary key, multiple fields specify primary key for multiple fields while determining the uniqueness of the data.
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
(i) SQLite and SQL statement Basics
