SQLLite related content

Um. . . Commonly used knowledge compiled some time ago:

SQLLiteStudy Notes

SQLite, is a lightweight The 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, etc. Its processing speed is very fast.

1. Basic operations

Create table:

Keywords: AUTOINCREMENT Can be used to automatically increment the value of a certain column. It can only be used for integer fields. You can add this keyword when creating the table.

Delete the table:

Insert new data into the table

or

The second way of writing must ensure that the order of values ​​is consistent with the order of columns in the table

Query table

After You can perform conditional query with WHERE, "*" means all columns in the query table, or you can do this

--subquery or internal query or nested query, embedded in the where subquery in another SQL special query You have to query in the sentence.

The following are several rules that the query must follow:

子 The query must be included in parentheses.

·                                                                                                                                                                                                Multiple columns, compared to the selected columns of the subquery. ·           ORDER BY

cannot be used in a subquery, although the main query can use

ORDER BY . You can use GROUP BY in a subquery, which has the same function as ORDER BY . ##·                            Subquery returns more than one row and can only be used with multi-valued operators, such as IN

operator. ·               BETWEEN operators cannot be used with subqueries, however,

BETWEEN Can be used within subqueries. Modify table dataDelete table data2. Operator

Assume variable a=10

, variable b=20, then:

arithmetic operator

##operator

##%Modulo- b % a will give 0

Comparison Operator

Description	Example
Addition - Add 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 - Multiply 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 after dividing the left operand by the right operand

##a8093152e673feb7aba1828c43532094 Check two operations Whether the values ​​of the numbers are equal, if not the condition is true. (a a8093152e673feb7aba1828c43532094 b) ##> ##4d76610d7f5c28db8ccd998deccd9bda=

Operator	Description	Example
##==	Checks whether the values ​​of the two operands are equal. If they are equal, the condition is true.	(a == b) is not true.
=	Check the value of both operands Whether they are equal, if so, the condition is true.	(a = b) is not true.
##!=	Check the two operands Whether the values ​​are equal, if not the condition is true.	(a != b) is true.
		is true.
Check the value of the left operand Is it greater than the value of the right operand? If so, the condition is true.	(a > b)	is not true.
Check the left operand Whether the value is greater than or equal to the value of the right operand, if so, the condition is true.	(a >= b) is not true.	##4d496f38cf26fff94f4d096d2999136f	Check whether the value of the left operand is not greater than the value of the right operand, if so, the condition is true.	(a !> b) is true.

Logical Operator

##IN ##NOT IN##GLOB ##NOT operator is used For combining multiple conditions in the WHERE clause of a SQL statement. Operator Used to compare a value with a NULL value. IS Similar.

Operator	Description
AND	##AND operator Allows the existence of multiple conditions in the WHERE clause of a SQL statement.
BETWEEN	##BETWEEN operator is used Searches for a value within a range of values ​​within a given minimum and maximum range.
EXISTS	EXISTS Operators are used to search for the existence of rows in a specified table that meet certain conditions.
IN	Operators are used to compare a value to a specified list of values.
##IN ## The opposite of the # operator, used to compare a value with values ​​that are not in a specified list.	LIKE
##LIKE Operators are used to compare a value with similar values ​​using wildcard operators.	##GLOB
Operators are used to compare a value with similar values ​​using wildcard operators. GLOB differs from LIKE in that it is case-sensitive.	NOT
operator is The opposite of the logical operator used. For example, NOT EXISTS , NOT BETWEEN, NOT IN, etc. It is the negation operator.	##OR ##OR
	IS NULL ##NULL
	##IS Operators and =
IS NOT	##IS NOT Operation The symbol is similar to != .
||	Concatenate two different strings to get a new string.
UNIQUE	UNIQUE The operator searches every row in the specified table to ensure uniqueness (no duplicates).

1. Bit operators

The following table lists the bit operations supported by the SQLite language symbol. Assume variable A=60 and variable B=13, then:

##08889b2ff96a808db595d306c906b542> will get 15

##Operator

Description

Instance

##&

The binary AND

operator copies one bit to the result if present in both operands.

(A & B)

will get 12, which is 0000 1100

|

The binary OR

operator copies one bit to the result if present in either operand.

(A | B)

will get 61, which is 0011 1101

~

The two's complement operator is a unary operator and has the "

flip" bit effect, that is, 0 becomes becomes 1, and 1 becomes 0.

(~A )

will get -61, which is 1100 0011, the complement form of a signed binary number.

##Binary right shift operator. The value of the left operand is shifted to the right by the number of bits specified by the right operand.

A >> 2

, which is 0000 1111

3. Uncommonly used clauses

1. GLOB

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

Comparison with LIKE: The * of GLOB has the same effect as the % of LIKE

                                                                           . Consistent with the function of LIKE

2. HAVING

Specifies conditional filtering. It seems that we may think of WHERE. So what is 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.

(2) The WHERE clause sets conditions on the selected columns, while the HAVING clause sets conditions on the grouping created by the GROUP BY clause.

(3)HAVING can only be used with the SELECT statement.
(4)HAVING is usually in the GROUPBY clause use.
(5)If the GROUPBY clause is not used, Then 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.

--Example: Query tableTable query for the number of people in each class who are older than 20 and whose gender is male

selectCOUNT(*)as'>Number of people aged 20', classidfromTable1 wheresex='male'groupbyclassid,age havingage>20

##-- needs to be noted: when it contains both where clause and groupby# When ## clauses, having clauses and aggregate functions are used, the execution sequence is as follows:

--

Execute whereClause to find data that meets the conditions;

--

Use the groupby clause to group data; group The groups formed by the by clause run the aggregate function to calculate the value of each group; finally, use the having clause to remove groups that do not meet the conditions.

--Every element in the having

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

--having

clause and where clause can be used to set restrictions so that the query results meet certain conditions. limit. The

--having

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

四、Distinct 关键字

SQLite 的 DISTINCT 关键字与 SELECT 语句一起使用，来消除所有重复的记录，并只获取唯一一次记录。

有可能出现一种情况，在一个表中有多个重复的记录。当提取这样的记录时，DISTINCT 关键字就显得特别有意义，它只获取唯一一次记录，而不是获取重复记录。

五、约束

约束是在表的数据列上强制执行的规则。这些是用来限制可以插入到表中的数据类型。这确保了数据库中数据的准确性和可靠性。

约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。

以下是在 SQLite 中常用的约束。

·          NOT NULL 约束：确保某列不能有 NULL 值。

·          DEFAULT 约束：当某列没有指定值时，为该列提供默认值。

·          UNIQUE 约束：确保某列中的所有值是不同的。

·          PRIMARY Key 约束：唯一标识数据库表中的各行/记录。

·          CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。

六、SQLite Joins

SQLite 的 Joins 子句用于结合两个或多个数据库中表的记录。JOIN 是一种通过共同值来结合两个表中字段的手段。

SQL 定义了三种主要类型的连接：

·          交叉连接 - CROSS JOIN

·          内连接 - INNER JOIN

·          外连接 - OUTER JOIN

1、交叉连接CROSS JOIN

交叉连接(CROSSJOIN)没有WHERE 子句，它返回连接表中所有数据行的笛卡尔积，其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。由于交叉连接（CROSS JOIN）有可能产生非常大的表，使用时必须谨慎，只在适当的时候使用它们。

2、内连接inner join

内连接的结果是从两个或者两个以上的表的组合中挑选出符合连接条件的数据。如果数据无法满足连接条件则将其丢弃。在内连接中，参与连接的表的地位是平等的。

内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分三种：

1)等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。

2)不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、816ff14aff4448eec8a656c2ab5ef5de、!<和a8093152e673feb7aba1828c43532094。

3)自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。

3、外连接

外连接（OUTERJOIN）是内连接（INNER JOIN）的扩展。虽然 SQL标准定义了三种类型的外连接：LEFT、RIGHT、FULL，但 SQLite 只支持 左外连接（LEFT OUTER JOIN）。外连接（OUTER JOIN）声明条件的方法与内连接（INNER JOIN）是相同的，使用 ON、USING 或 NATURAL 关键字来表达。最初的结果表以相同的方式进行计算。一旦主连接计算完成，外连接（OUTER JOIN）将从一个或两个表中任何未连接的行合并进来，外连接的列使用 NULL值，将它们附加到结果表中。

七、unions子句

1、SQLite的 UNION 子句/运算符用于合并两个或多个 SELECT 语句的结果，不返回任何重复的行。

In order to use UNION, the number of columns selected by each SELECT must be the same, the same number of column expressions, the same data type, and make sure they have the same order, but they do not have to be the same length.

2. UNIONALL clause

The UNION ALL operator is used to combine the results of two SELECT statements, including duplicate rows.

The rules that apply to UNION also apply to the UNION ALL operator.

8. Trigger

SQLite trigger (Trigger) is the callback function of the database, which will automatically execute/call when the specified database event occurs. The following are the key points about SQLite's triggers:

• SQLite's triggers can be specified to trigger when DELETE, INSERT, or UPDATE occurs in a specific database table, or when Fires when one or more columns of the specified table are updated.

• SQLite only supports FOR EACH ROW triggers (Triggers), not FOR EACH STATEMENT triggers (Triggers). Therefore, explicitly specifying FOR EACH ROW is optional.

• WHEN clause and trigger (Trigger) actions may access row elements that are inserted, deleted, or updated using references of the form NEW.column-name and OLD.column-name, where column -name is the name of the column from the table associated with the trigger.

• If a WHEN clause is provided, the SQL statement will only be executed for the specified rows where the WHEN clause is true. If no WHEN clause is provided, the SQL statement is executed for all rows.

• The BEFORE or AFTER keyword determines when to execute the trigger action before or after the insertion, modification, or deletion of the associated row.

• When the table associated with the trigger is deleted, the trigger (Trigger) is automatically deleted.

• The table to be modified must exist in the same database as the table or view to which the trigger is attached, and only tablename must be used, not database.tablename.

• A special SQL function RAISE() can be used to trigger exceptions thrown within the program.

Example:

SELECT name FROM sqlite_master WHEREtype = 'trigger';You can view all triggers

for each row is triggered once every time the operation statement affects a row, that is, it is triggered once if 10 rows are deleted 10 times, and for each state An operation statement is triggered once, and sometimes unaffected rows are also executed. sqlite only implements the triggering of for each row . when and for each row The usage is like this:

CREATETRIGGER trigger_name

AFTERUPDATE OF id ON table_1

FOR EACHROW

WHEN new.id>30

BEGIN

UPDATEtable_2 SET id=new.id WHEREtable_2.id=old.id;

END ;

The above trigger is in table_1 When changing the id, if the new id>30, the rows in table table_2 with the same id as table_1 will be changed to the new id

9 , Index

The index (Index) is a special lookup table that database search engines use 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 without affecting the data.

Single column index

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

CREATE INDEX index_name

ON table_name (column_name);

Unique index

Using unique index is not only for performance, but also for data integrity. A unique index does not allow any duplicate values ​​to be inserted into the table. The basic syntax is as follows:

CREATE UNIQUE INDEX index_name

on table_name (column_name);

Combined 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_name

on table_name (column1, column2);

Whether you want to create a single column index or a combined index, you must consider the WHERE you use as the query filter condition. Columns that are used very frequently in the clause.

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

Implicit index

Implicit index is an index automatically created by the database server when creating an object. Indexes are automatically created with 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 DROP command of SQLite

Under what circumstances should we avoid using an index?

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

·       Indexes should not be used on smaller tables.

·        Indexes should not be used on tables with frequent large batch update or insert operations.

·                                                                                                                                                                                                                                                                                       ·        Indexes should not be used on frequently operated columns.

Indexed ByThe "INDEXEDBY index-name" clause specifies that a named index must be needed to find the value 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 specifies that no index is used when accessing the preceding table (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_name

WHERE (CONDITION);

十、重命名表或向表中插入新的字段

用来重命名已有的表的 ALTERTABLE 的基本语法如下：

ALTERTABLE database_name.table_name RENAME TOnew_table_name;

用来在已有的表中添加一个新的列的 ALTERTABLE 的基本语法如下：

ALTERTABLE database_name.table_name ADD COLUMNcolumn_def...;

十一、视图

视图（View）只不过是通过相关的名称存储在数据库中的一个 SQLite 语句。视图（View）实际上是一个以预定义的 SQLite 查询形式存在的表的组合。

视图（View）可以包含一个表的所有行或从一个或多个表选定行。视图（View）可以从一个或多个表创建，这取决于要创建视图的 SQLite 查询。、

视图（View）是一种虚表，允许用户实现以下几点：

① 用户或用户组查找结构数据的方式更自然或直观。

② 限制数据访问，用户只能看到有限的数据，而不是完整的表。

③ 汇总各种表中的数据，用于生成报告。

SQLite 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，需要做的动作在触发器内容中定义。

1、创建视图

CREATE [TEMP | TEMPORARY] VIEW view_name AS

SELECT column1, column2.....

FROM table_name

WHERE [condition];

2、删除视图

DROP VIEW view_name;

十二、日期时和时间

SQLite 支持以下五个日期和时间函数：

The above five date and time functions take time strings as parameters. A time string followed by zero or more modifier modifiers. strftime() The function can also take the format string format as its first parameter. The different types of time strings and modifiers will be explained to you in detail below.

Time string

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

序号	函数	实例
1	date(timestring, modifier, modifier, ...)	以 YYYY-MM-DD 格式返回日期。
2	time(timestring, modifier, modifier, ...)	以 HH:MM:SS 格式返回时间。
3	datetime(timestring, modifier, modifier, ...)	以 YYYY-MM-DD HH:MM:SS 格式返回。
4	julianday(timestring, modifier, modifier, ...)	This will return 4714 BC from GMT year 11 month The number of days from noon on 24 .
##5	#strftime(format, timestring, modifier , modifier, ...)	This will return a formatted date based on the format string specified by the first argument. The specific format is explained below.

##YYYY-MM-DD HH:MM2010-12-30 12:10##3##12:10##YYYY-MM-DDT##7 HH:MM:SS##2013-05-07

You can use "T" as the literal character that separates date and time.

Modifier

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

(1)Increase the date and time of the specified value (positive or negative numbers are acceptable)

·        NNN days

·        NNN hours

##·                                                                                      NNN.NNNN seconds·                                                 

## (2) ## Respanse Back to the beginning of the current date

Start of Month

·            

start of year##·                

start of day (3)

Returns the date and time of N in the next week

·       weekday N

(4)Returns the number of seconds since

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 to any date and time. You can format dates and times using the following replacements:

Serial number	##Time string	Example
##1	YYYY-MM-DD	2010-12-30
2
YYYY-MM-DD HH:MM:SS.SSS	##2010-12-30 12:10:04.100	##4
#MM-DD-YYYY HH:MM	30-12-2010 12:10	##5
HH:MM		6
HH:MM	2010-12-30 12:10
##12:10:01	8	##YYYYMMDD HHMMSS
20101230 121001	#9	now

##Day of the year, ##%J##%m00-12year,% symbol

replacement	Description
##%d	The day of the month, 01-31
##%f	Seconds with decimal part, SS.SSS
%H	hours, 00-23
%j	001 -366
Julian day number,	DDDD.DDDD
月,	#%M
points, 00-59	##%s
The number of seconds since 1970-01-01	%S
seconds, 00-59	%w
Day of the week, 0-6 (0 is Sunday)	##%W
一Week in the year, 01-53	##%Y
YYYY	##%%
selectdatetime('now','localtime');--当前时间读取本地得时间 selectdatetime('now','start of month');--本月第一天 selectdatetime('now','start of month','+1 month','-1 day');--本月最后一天 selectdatetime('now','start of year','+1 year','start of month','-1 day');--今年最后一天 SELECTjulianday(date('now','localtime')) - julianday('2018-03-20');；--计算今天到2018-03-20相差多少天 十三、函数大全 1、count 用于计算一个数据库表中得行数 如：select count（*）from k_user 2、max 选择某列最大值 3、min 选择某列最小值 4、avg 计算某列平均值 5、sum 允许一个数值列计算总和 6、random 返回一个介于 -9223372036854775808 和 +9223372036854775807之间的伪随机整数 7、abs 返回数值参数得绝对值 8、upper 吧字符串转换为大写字母 9、lower 把字符串转换为小写字母 10、length 返回字符串长度 11、sqlite_version 返回数据库版本 12、coalesce(X,Y,……) 返回第一个非空参数的副本。若所有的参数均为NULL，返回NULL。至少2个参数。 13、ifnull(X,Y) 返回第一个非空参数的副本。若两个参数均为NULL，返回NULL。 14、last_insert_rowid() 返回当前数据库连接最后插入行的RowID。 15、nullif(X,Y) 当两参数不同时返回X，否则返回NULL。 16、quote(X) 返回参数的适于插入其他SQL语句中的值。字符串会被添加单引号。 17、round(X)或round(X,Y) 将X四舍五入，保留小数点后Y位。若忽略Y参数，则默认其为0。 18、zeroblob(N)     返回一个 N 字节长、全部由 0x00 组成的 BLOB。SQLite 或以很有效的组织这些 zeroblob。它可以被用于为以后的使用预留空间。以后可以使用 incremental BLOB I/O 来写入 BLOB 数据。 19、typeof(X) 返回表达式X的类型 20、change_count() 返回受上一语句影响的行数。（好像不能用） 21、total(X) 返回一组中所有非空值的数字和。若没有非空行，sum()返回null而total()返回0.0。total()的返回值为浮点数，sum()可以为整数。 22、ltrim(X) lrtrim(X,Y)     返回从 X 的左边边去除所有出现在 Y 中字符以后的字符串。如果省略 Y，则去除空格。 23、 replace(X,Y,Z)     返回一个将 X 字符串中每一个出现 Y 的位置替换为 Z 后的字符串。它使用二进制对照序列进行比较。 24、randomblob(N)     返回一个 N 字节长的包含伪随机字节的 BLOG。 N 应该是正整数 25、rtrim(X) rtrim(X,Y)     返回从 X 的右边去除所有出现在 Y 中字符以后的字符串。如果省略 Y，则去除空格。 26、soundex(X)     计算字符串 X的读音编码。如果参数为 NULL，则返回 "?000"。默认情况下 SQLite 忽略该函数。仅当在编译时指定 -DSQLITE_SOUNDEX=1 时才有效。 27、substr(X,Y,Z) substr(X,Y) Return string X A string starting from the Y-th character and having 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, then the subscripted value will refer to actual UTF-8 characters, not bytes. If X is a BLOB, then the subscript refers to bytes. This article explains the relevant content of SQLLite. For more related content, please pay attention to the php Chinese website. Related recommendations: Spark SQL implements log offline batch processing Detailed explanation about MySQL-retrieving data sql Compare the time difference between two adjacent records

The above is the detailed content of SQLLite related content. For more information, please follow other related articles on the PHP Chinese website!