Shulou(Shulou.com)05/31 Report--

This article will explain in detail what are the basic knowledge points about SQLLite. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

SQLLite study notes

SQLite, a lightweight database, is an ACID-compliant relational database management system, which is contained in a relatively small C library. It can support mainstream operating systems such as Windows/Linux/Unix, and can be combined with many programming languages, such as Tcl, C #, PHP, Java and so on. Its processing speed is very fast.

1. Basic operations to build tables:

Keyword: AUTOINCREMENT can be used to automatically increment the value of a column, and can only be used in shaping fields. This keyword can be added when creating a table.

Delete table: insert new data into the table

Or

The second way of writing must ensure that the order of value is the same as the order of the columns in the table.

Query table

You can then do a conditional query with WHERE. "*" indicates all the columns in the query table, or you can do so.

A subquery or an internal query or a nested query is a query embedded in the where clause in another SQL Litt query.

Here are a few rules that subqueries must follow:

Subqueries must be enclosed in parentheses.

A subquery can have only one column in the SELECT clause, unless there are multiple columns in the main query, compared to the selected columns of the subquery.

ORDER BY cannot be used in subqueries, although the main query can use ORDER BY. You can use GROUP BY in a subquery with the same functionality as ORDER BY.

The subquery returns more than one row and can only be used with multivalued operators, such as the IN operator.

The BETWEEN operator cannot be used with a subquery, but BETWEEN can be used within a subquery.

Modify table data delete table data II. Operator

Suppose the variable axi10 and the variable baked 20, then:

Arithmetic operator

Operator

Description

Example

+

Addition-adds the values on both sides of the operator

A + b will get 30

-

Subtraction-left Operand minus right Operand

A-b will get-10

*

Multiplication-multiplies the values on both sides of the operator

A * b will get 200

/

Division-left Operand divided by right Operand

B / a will get 2.

%

The remainder obtained by dividing the left Operand by the right Operand.

B a will give 0

Comparison operator

Operator

Description

Example

= =

Check whether the values of the two operands are equal, and if so, the condition is true.

(a = = b) is not true.

=

Check whether the values of the two operands are equal, and if so, the condition is true.

(a = b) is not true.

! =

Check whether the values of the two operands are equal, and if not, the condition is true.

(a! = b) true.

Check whether the values of the two operands are equal, and if not, the condition is true.

(a / b) is true.

>

Check whether the value of the left Operand is greater than that of the right Operand, and if so, the condition is true.

(a > b) not true.

=

Check whether the value of the left Operand is greater than or equal to the value of the right Operand, and if so, the condition is true.

(a > = b) not true.

2 will get 15, that is, 0000 1111

Third, uncommonly used clauses

1 、 GLOB

In our sql, it is not commonly used that the role of GLOB,GLOB is basically the same as that of LIKE. The biggest difference is that GLOB is case sensitive. The asterisk (*) represents zero, one, or more numbers or characters. The question mark (?) represents a single number or character.

Compared with LIKE: the effect of GLOB * is consistent with that of LIKE.

GLOB's? Consistent with the action of LIKE

2 、 HAVING

Specify conditional filtering, so we might think of WHERE, so what's the difference between WHERE and HAVING? In fact, the difference between the two is actually quite big.

(1) the HAVING clause allows you to specify conditions to filter the grouped results that will appear in the final result.

The WHERE clause sets the condition on the selected column, while the HAVING clause sets the condition on the grouping created by the GROUP BY clause.

(3) HAVING can only be used with SELECT statements.

(4) HAVING is usually used in the GROUPBY clause.

(5) if you do not use the GROUPBY clause, HAVING behaves the same as the WHERE clause.

(6) in a query, the HAVING clause must be placed after the GROUP BY clause and before the ORDER BY clause.

-- for example: query the table table to query the number of men over 20 and gender in each class.

SelectCOUNT (*) as' > 20', classidfromTable1wheresex=' male 'groupbyclassid,agehavingage > 20

-- Note: when there is a where clause, a groupby clause, a having clause and an aggregate function, the execution order is as follows:

-- execute the where clause to find eligible data

-- use the groupby clause to group the data; run the aggregate function on the group formed by the groupby clause to calculate the value of each group; and finally use the having clause to remove the groups that do not meet the criteria.

Every element in the having clause must also appear in the select list. There are some database exceptions, such as oracle.

Both the having clause and the where clause can be used to set constraints so that the query results meet certain conditions.

The having clause restricts groups, not rows. Aggregate functions cannot be used in the where clause, but can be used in the having clause.

IV. Distinct keyword

The DISTINCT keyword of SQLite is used with the SELECT statement to eliminate all duplicate records and get only one record.

It is possible to have multiple duplicate records in a table. The DISTINCT keyword makes a lot of sense when extracting such a record, which only gets a single record, not a duplicate record.

V. restraint

Constraints are rules that are enforced on the data columns of a table. These are used to limit the types of data that can be inserted into the table. This ensures the accuracy and reliability of the data in the database.

Constraints can be at the column or table level. Column-level constraints apply only to columns, and table-level constraints are applied to the entire table.

The following constraints are commonly used in SQLite.

NOT NULL constraint: ensures that a column cannot have a NULL value.

DEFAULT constraint: provides a default value for a column when it does not have a specified value.

UNIQUE constraint: ensure that all values in a column are different.

PRIMARY Key constraint: uniquely identifies each row / record in the database table.

CHECK constraint: the CHECK constraint ensures that all values in a column meet certain conditions.

VI. SQLite Joins

The Joins clause of SQLite is used to combine records of tables in two or more databases. JOIN is a means of combining fields in two tables through common values.

SQL defines three main types of connections:

Cross connect-CROSS JOIN

Internal connection-INNER JOIN

External connection-OUTER JOIN

1. Cross-connect CROSS JOIN

CROSSJOIN has no WHERE clause, it returns the Cartesian product of all data rows in the join table, and the number of data rows in the result set is equal to the number of rows in the first table that meet the query criteria multiplied by the number of rows in the second table that meet the query criteria. Because cross-joins (CROSS JOIN) have the potential to produce very large tables, you must be careful to use them only when appropriate.

2. Internal connection inner join

The result of an inner join is to select data that meets the join criteria from a combination of two or more tables. If the data cannot meet the connection conditions, it is discarded. In an internal join, the status of the tables participating in the join is equal.

The inner join query operation lists the rows of data that match the join condition, which uses the comparison operator to compare the column values of the joined column. There are three types of internal connections:

1) equivalent join: use the equal sign (=) operator to compare the column values of the connected columns in the join condition, and all columns in the joined table, including duplicate columns, are listed in the query results.

2) unequal concatenation: compare the column values of the connected columns using comparison operators other than the equal operator in the join condition. These operators include >, > =, and 30 to change the rows in table table_2 and table table_1 id equal to the new id.

IX. Index

Index is a special look-up table, which is used by database search engine to speed up data retrieval. An index helps speed up SELECT queries and WHERE clauses, but it slows down data entry when using UPDATE and INSERT statements. Indexes can be created or deleted, but the data is not affected.

Single column index

A single-column index is an index created on only one column of a table. The basic syntax is as follows:

CREATE INDEX index_nameON table_name (column_name); unique index

Unique indexes are used not only for performance, but also for data integrity. Unique indexes do not allow any duplicate values to be inserted into the table. The basic syntax is as follows:

CREATE UNIQUE INDEX index_nameon table_name (column_name); composite index

A composite index is an index created on two or more columns of a table. The basic syntax is as follows:

CREATE INDEX index_nameon table_name (column1, column2)

Whether you want to create a single-column index or a combined index, take into account the columns that you use very frequently in the WHERE clause that serves as a query filter.

If the value uses a column, choose to use a single-column index. If two or more columns are frequently used in the WHERE clause as a filter, choose to use a combined index.

Implicit index

An implicit index is an index created automatically by the database server when an object is created. Indexes are automatically created as primary key constraints and unique constraints.

SELECT * FROM sqlite_master WHEREtype = 'index'; view all indexes in the database

DROP INDEX index_name; an index can be deleted using the SQLite DROP command

Under what circumstances should indexes be avoided?

Although the purpose of indexing is to improve database performance, there are several situations where indexes need to be avoided. When using an index, you should reconsider the following guidelines:

Indexes should not be used on smaller tables.

Indexes should not be used on tables that have frequent bulk updates or inserts.

Indexes should not be used on columns that contain a large number of NULL values.

Indexes should not be used on columns that operate frequently.

Indexed By

The "INDEXEDBY index-name" clause states that a named index is required to find the values in the previous table.

If the index name index-name does not exist or cannot be used for the query, then the preparation of the SQLite statement fails.

The "NOTINDEXED" clause states that indexes are not used when accessing previous tables, including implicit indexes created by UNIQUE and PRIMARYKEY constraints.

However, even if "NOT INDEXED" is specified, INTEGER PRIMARY KEY can still be used to find entries.

INDEXED BY can be used with DELETE, UPDATE, or SELECT statements:

SELECT | DELETE | UPDATE column1, column2...INDEXED BY (index_name) table_nameWHERE (CONDITION); rename the table or insert a new field into the table

The basic syntax for ALTERTABLE to rename an existing table is as follows:

ALTERTABLE database_name.table_name RENAME TOnew_table_name

The basic syntax for ALTERTABLE to add a new column to an existing table is as follows:

ALTERTABLE database_name.table_name ADD COLUMNcolumn_def...

Eleventh, View

A View is nothing more than a SQLite statement stored in the database by the associated name. A View is actually a combination of tables that exist in the form of a predefined SQLite query.

A View can contain all rows of a table or select rows from one or more tables. Views (View) can be created from one or more tables, depending on the SQLite query in which you want to create the view. 、

A View is a virtual table that allows the user to implement the following:

① users or user groups find structural data in a more natural or intuitive way.

② restricts data access, and users can only see limited data, not complete tables.

③ aggregates data from various tables to generate reports.

The SQLite view is read-only, so DELETE, INSERT, or UPDATE statements may not be executed on the view. However, you can create a trigger on the view that is triggered when you try a DELETE, INSERT, or UPDATE view, and the action you need to do is defined in the trigger content.

1. Create a view

CREATE [TEMP | TEMPORARY] VIEW view_name ASSELECT column1, column2.FROM table_nameWHERE [condition]

2. Delete the view

DROP VIEW view_name; XII. Date, time and time

SQLite supports the following five date and time functions:

Serial number

Function

Example

one

Date (timestring, modifier, modifier,...)

Returns the date in YYYY-MM-DD format.

two

Time (timestring, modifier, modifier,...)

Returns the time in HH:MM:SS format.

three

Datetime (timestring, modifier, modifier,...)

Returned in YYYY-MM-DD HH:MM:SS format.

four

Julianday (timestring, modifier, modifier,...)

This returns the number of days from noon on November 24, 4714 B.C., GMT.

five

Strftime (format, timestring, modifier, modifier,...)

This returns the formatted date based on the format string specified by the first parameter. The specific format is explained below.

The above five date and time functions take a time string as an argument. The time string is followed by zero or more modifier modifiers. The strftime () function can also take the format string format as its first argument. The different types of time strings and modifiers are explained in detail below.

Time string

A time string can be in any of the following formats:

Serial number

Time string

Example

one

YYYY-MM-DD

2010-12-30

two

YYYY-MM-DD HH:MM

2010-12-30 12:10

three

YYYY-MM-DD HH:MM:SS.SSS

2010-12-30 12-10-10-12-12-12-12-12-30-12-12-12-30-12-12-30-12-12-30 12-10-12-30 10-12-30 12-12-30 12-12-30 12-12-30 12-12-30 12-12-30 12-12-30 12

four

MM-DD-YYYY HH:MM

30-12-2010 12:10

five

HH:MM

12:10

six

YYYY-MM-DDTHH:MM

2010-12-30 12:10

seven

HH:MM:SS

12:10:01

eight

YYYYMMDD HHMMSS

20101230 121001

nine

Now

2013-05-07

You can use "T" as a text character that separates date and time.

Modifier (Modifier)

The time string can be followed by zero or more modifiers, which will change the date and / or time returned by the above five functions. Any of the above five functions return time. Modifiers should be used from left to right, and the modifiers that can be used in SQLite are listed below:

(1) increase the date and time of the specified value (either positive or negative)

NNN days

NNN hours

NNN minutes

NNN.NNNN seconds

NNN months

NNN years

(2) return the start of the current date

Start of month

Start of year

Start of day

(3) return the date and time that the next week is N

Weekday N

(4) returns the number of seconds from 1970-01-01

Unixepoch

(5) return to local time

Localtime

(6) International Standard time

Utc

Formatting

SQLite provides a very convenient function strftime () to format any date and time. You can format the date and time using the following replacements:

Replace

Description

% d

The day of the month, 01-31

% f

Seconds with decimal parts, SS.SSS

% H

Hours, 00-23

% j

The day of the year, 001-366

% J

Julian days, DDDD.DDDD

% m

Month, 00-12

% M

Score, 00-59

% s

Seconds from 1970-01-01

% S

Seconds, 00-59

% w

Day of the week, 0-6 (0 is Sunday)

% W

The week of the year, 01-53

% Y

Year, YYYY

%%

% symbol

Selectdatetime ('now','localtime');-- current time read local time selectdatetime (' now','start of month');-- first day of this month selectdatetime ('now','start of month','+1 month','-1 day');-- last day of this month selectdatetime (' now','start of year','+1 year','start of month','-1 day') -- the last day of the year SELECTjulianday (date ('now','localtime'))-julianday (' 2018-03-20') Calculate the difference between today and 2018-03-20. 13.

1. Count is used to calculate the number of rows in a database table.

Such as: select count (*) from k_user

2. Max selects the maximum value of a column

3. Min selects the minimum value of a column

4. Avg calculates the average of a column

5. Sum allows a numeric column to calculate the sum

6. Random returns a pseudo-random integer between-9223372036854775808 and + 9223372036854775807

7. Abs returns the absolute value of the numeric parameter

8. Upper converts strings to uppercase letters

9. Lower converts strings to lowercase letters

10. Length returns the length of the string

11. Sqlite_version returns the database version

12. Coalesce (XQuery Y, …) Returns a copy of the first non-empty parameter. If all parameters are NULL, NULL is returned. At least 2 parameters.

13. Ifnull (XQuery Y) returns a copy of the first non-empty parameter. If both parameters are NULL, NULL is returned.

14. Last_insert_rowid () returns the RowID of the last row inserted in the current database connection.

15. Nullif (XQuery Y) returns X if the two parameters are different, otherwise it returns NULL.

16. Quote (X) returns the value of the parameter that is suitable for insertion into other SQL statements. The string is appended in single quotation marks.

17. Round (X) or round (Xpene Y) rounds X to keep the Y place after the decimal point. If you omit the Y parameter, it defaults to 0.

18. Zeroblob (N) returns a BLOB that is N bytes long and consists entirely of 0x00. SQLite may organize these zeroblob very effectively. It can be used to reserve space for future use. You can write BLOB data later using incremental BLOB Iramp O.

19. Typeof (X) returns the type of expression X

20. Change_count () returns the number of rows affected by the previous statement. (it doesn't seem to work)

21. Total (X) returns the sum of all non-null values in a set. If there are no non-blank lines, sum () returns null and total () returns 0.0. The return value of total () is a floating point number, and sum () can be an integer.

22. Ltrim (X)

Lrtrim (XQuery Y) returns a string that removes all characters that appear in Y from the left side of X. If Y is omitted, spaces are removed.

23. Replace (XMagol Y _ Z) returns a string that replaces the position where Y appears in the X string with the position of Z. It uses binary contrast sequences for comparison.

24. Randomblob (N) returns an N-byte long BLOG containing pseudorandom bytes. N should be a positive integer

25. Rtrim (X)

Rtrim (XQuery Y) returns a string that removes all characters that appear in Y from the right side of X. If Y is omitted, spaces are removed.

26. Soundex (X) calculates the pronunciation coding of the string X. Returns "? 000" if the parameter is NULL. By default, SQLite ignores this function. Valid only when-DSQLITE_SOUNDEX=1 is specified at compile time.

27. Substr (Xmai YMagi Z)

Substr (XQuery Y) returns the string X, which starts with the Y character and has a length of Z. If Z is omitted, the string up to the end of the string is returned. The first character of X starts with 1. If Y is negative, count from the right. If X is a UTF-8 string, the subscript value refers to the actual UTF-8 characters character, not the byte. If X is a BLOB, then the subscript refers to bytes.

This is the end of this article on "what are the basic knowledge points of SQLLite". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.