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

This article mainly explains "how to use clauses and functions in embedded database Sqlite3". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to use clauses and functions in embedded database Sqlite3".

Database preparation

Create a new database, company.db. Design a table employee with the following contents:

Idnameagedepsalary1 Jack Ma 21market60002 Ma Huateng 22tech70003 Li Yanhong 23trs86004 Zhang Chaoyang 24trs60005 Luo Yonghao 26tech89006 Wang Xin 25market4000

Based on the above table, we first determine that the primary key is id. The command to create the table is as follows:

CREATE TABLE employee (id integer primary key, name text,age integer, dep text, salary integer); insert into employee values (1pm'Ma Yun', 21 pencils' marketplace pr. 6000); insert into employee values (2pas'Ma Huateng', 22pl 'techtech' pr. 7000); insert into employee values (3'Li Yanhong', 23 pencils' trs' pill 8000); insert into employee values (4 'Zhang Chaoyang', 24 pas'pr é cor pr 6000); insert into employee values (5pines' Luo Yonghao', 26 tech 'tech' cum 8900) Insert into employee values (6 recordings' Wang Xin', 25 pencils marketplace pr 4000); insert into employee values (7 pas' one mouthfuls', 18 pencils marketplace pours 4000)

Order clause

We can use the order clause to sort records:

Select * from employee order by age

Insert a picture description here

Where clause and logical operator

The WHERE clause of SQLite is used to specify the conditions under which data is obtained from one or more tables. Returns a specific value from the table if the given condition, that is, true, is met. You can use the WHERE clause to filter records and get only the records you need. The WHERE clause can be used not only in SELECT statements, but also in UPDATE, DELETE statements, and so on, which we will learn in later chapters.

Grammar

The basic syntax of SQLite's SELECT statement with a WHERE clause is as follows:

SELECT column1, column2, columnN FROM table_name WHERE [condition]

Example

You can also use comparison or logical operators to specify conditions, such as >, =, = 25 AND SALARY > = 6500

Insert a picture description here

The following SELECT statement lists all records with an AGE greater than or equal to 25 or a salary greater than or equal to 65000.00:

SELECT * FROM EMPLOYEE WHERE AGE > = 25 OR SALARY > = 65000

Insert a picture description here

The following SELECT statement lists all records whose AGE is not NULL, and the result shows all records, meaning that no record has an AGE equal to NULL:

SELECT * FROM EMPLOYEE WHERE AGE IS NOT NULL

The where clause can also be used with the like clause. The following SELECT statement lists all records that NAME begins with 'Ki',' with no restrictions on the characters after 'Ki':

SELECT * FROM EMPLOYEE WHERE NAME LIKE 'Horse%'

The following SELECT statement lists all records with a value of 22 or 25 for AGE:

SELECT * FROM EMPLOYEE WHERE AGE IN (22,25)

Insert a picture description here

The following SELECT statement lists all records where the value of AGE is neither 25 nor 27:

SELECT * FROM EMPLOYEE WHERE AGE NOT IN (22,25)

Insert a picture description here

The following SELECT statement lists all records with a value between 22 and 25 for AGE:

SELECT * FROM EMPLOYEE WHERE AGE BETWEEN 22 AND 25

Insert a picture description here

Group by clause

The GROUP BY statement is used in conjunction with aggregate functions to group result sets according to one or more columns. For example:

Calculate the total salary of the whole company:

Select sum (salary) from employee

Insert a picture description here

Count the sum of wages in each department:

Select dep, sum (salary) from employee group by dep

Count the total wages of each department and require the id value to be greater than 3

Select dep, sum (salary) from employee where id > 3 group by dep; where clause should be placed before group by.

Insert a picture description here

Having clause

The having clause is the conditional clause of group by. The where clause occurs first, and then the having clause executes. There are three elements that can be used in the HAVING clause: constant, aggregate function, column name specified in the GROUP BY clause (aggregate build). If you use having, you must use it with group by. If you use group by, there are different having (it is only used for a filter).

Give an example

Count the total wages of each department and require the id value to be greater than 3

Select dep, sum (salary) from employee where id > 3 group by dep having sum (salary) > 5000

The method of finding duplicate records

Let's insert a record with the same name.

Insert into employee values (8 ~ (1)), 19 ~ (th)); select id,name from employee group by name having count (*) > 1

As you can see, the result shows a duplicate record of the same name.

Display multiple items with the same name

Select id,name,dep,salary from employee group by name having count (*) > 1

Show all records in table

Select count (*) from employee

Show the number of all records

Select dep,avg (salary) from employee group by dep

Show the average of each group under dep

Select * from employee where id > 3 intersect select * from employee where id

< 9; 显示id >

3 & & id

< 9 的所有记录：即4 - 8 的记录 select * from employee where id >

3 union all select * from employee where id

< 9; 显示所有的大于3并且小于9的，并集(如果有相同的，会重复显示) select * from studentnew where id >

3 union all select * from studentnew where id

< 9; 显示大于9的记录 select * from employee where id >

3 union all select * from studentnew where id < 6

Show records greater than 6 (compare with previous one)

Select * from employee where salary= (select salary from employee order by salary desc limit 1); select * from employee where salary= (select max (salary) from employee)

Show the records of all employees with the highest wage

Select name,max (salary) from employee

Insert a picture description here

Display employees with different names in table

Select distinct name from employee

Show the names of all employees

Select all name from employee

Function

SQLite has many built-in functions for dealing with string or numeric data. Here are some useful SQLite built-in functions, all of which are case-insensitive, which means you can use these functions in lowercase or uppercase or mixed form. For more descriptions of functions, please refer to the official documentation of SQLite. Here are some commonly used functions.

Ordinal function-describes the 1.COUNTSQLite COUNT aggregate function that is used to calculate the number of rows in a database table. The 2.MAXSQLite MAX aggregate function allows us to select the maximum value of a column. The 3.MINSQLite MIN aggregate function allows us to select the minimum value of a column. The 4.AVGSQLite AVG aggregate function calculates the average of a column. The 5.SUMSQLite SUM aggregate function allows you to calculate the sum for a numeric column. The 6.RANDOMSQLite RANDOM function returns a pseudo-random integer between-9223372036854775808 and + 9223372036854775807. The 7.ABSSQLite ABS function returns the absolute value of a numeric parameter. The 8.UPPERSQLite UPPER function converts a string to uppercase letters. The 9.LOWERSQLite LOWER function converts a string to lowercase letters. The 10.LENGTHSQLite LENGTH function returns the length of the string. The 11.sqlite_versionSQLite sqlite_version function returns the version of the SQLite library.

SQLite COUNT function

The SQLite COUNT aggregate function is used to calculate the number of rows in a database table. Here is an example:

SELECT count (*) FROM EMPLOYEE

Execution result:

Insert a picture description here

SQLite MAX function

The SQLite MAX aggregate function allows us to select the maximum value of a column. Here is an example:

SELECT max (salary) FROM EMPLOYEE

Execution result:

SQLite MIN function

The SQLite MIN aggregate function allows us to select the minimum value of a column. Here is an example:

SELECT min (salary) FROM EMPLOYEE

Execution result:

Insert a picture description here

SQLite AVG function

The SQLite AVG aggregate function calculates the average of a column. Here is an example:

SELECT avg (salary) FROM EMPLOYEE

Execution result:

SQLite SUM function

The SQLite SUM aggregate function allows you to calculate the sum for a numeric column. Here is an example:

SELECT sum (salary) FROM EMPLOYEE

Execution result:

Insert a picture description here

SQLite RANDOM function

The SQLite RANDOM function returns a pseudo-random integer between-9223372036854775808 and + 9223372036854775807. Here is an example:

SELECT random () AS Random

Execution result:

Insert a picture description here

SQLite ABS function

The SQLite ABS function returns the absolute value of a numeric parameter. Here is an example:

SELECT abs (5), abs (- 15), abs (NULL), abs (0), abs ("ABC")

Execution result:

Insert a picture description here

SQLite UPPER function

The SQLite UPPER function converts a string to uppercase letters. Here is an example:

Insert into employee values; SELECT upper (name) FROM EMPLOYEE

Execution result:

SQLite LOWER function

The SQLite LOWER function converts a string to lowercase letters. Here is an example:

SELECT lower (name) FROM EMPLOYEE

Execution result:

SQLite LENGTH function

The SQLite LENGTH function returns the length of the string. Here is an example:

SELECT name, length (name) FROM EMPLOYEE

Execution result:

SQLite sqlite_version function

The SQLite sqlite_version function returns the version of the SQLite library. Here is an example:

SELECT sqlite_version () AS 'SQLite Version'

Execution result:

SQLite time / date function:

Datetime ()

Date and time generated by datetime () No parameter indicates that the current time and date are obtained * *

Select datetime ()

Insert a picture description here

If you have a string parameter, convert the string to a date.

Select datetime ('2012-01-07 12 purl 01purl 30')

2012-01-07 12:01:30

Select date ('2019-09-09-09: 1 day','+1 year')

2013-01-09

Select datetime ('2019-09-09 00, 20 20, 8: 00, 1 hour','-12 minute')

2019-09-09 01:08:00

Select datetime ('now','start of year')

2020-01-01 00:00:00

Select datetime ('now','start of month')

2020-09-01 00:00:00

Select datetime ('now','start of day')

2020-09-13 00:00:00

Select datetime ('now','localtime')

Result: 2020-09-12 20:26:35

Date ()

Date () is used to generate the date

Select date ('2019-09-09 12 purl 01purl 30')

2019-09-09

Select date ('now','start of year')

2020-01-01

Select date ('2019-09-09-09: 1 month')

2019-10-09

Time ()

Time () is used to generate time.

Select time ()

03:28:49

Select time ('23, 18, 15, 59)

23:18:59

Select time ('23VO18 VOLLING 59MULTHING start of day')

00:00:00

You can use a string of the following format as a parameter in the time / date function:

YYYY-MM-DD

YYYY-MM-DD HH:MM

YYYY-MM-DD HH:MM:SS

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

HH:MM

HH:MM:SS

HH:MM:SS.SSS

Now

Where now is generated at the present time.

Date cannot be compared correctly. It will be compared by string. The default format of date is dd-mm-yyyy

Strftime ()

Strftime () formats the date and time generated by the above three functions.

The strftime () function converts a date string in YYYY-MM-DD HH:MM:SS format into a string of other forms. Strftime (format, date / time, modifier, …)

It can format the date and time with the following symbols:

% d in the day of the month, 01-31% f decimal seconds, SS.SSS% H hours, 00-23% j calculate the day of the year, 001-366% m month 00-12% M minutes, 00-59% s seconds from January 1, 1970 to the present% S-seconds, 00-59% w weeks, 0-6 (0 is Sunday)% W calculates the week of the year, 01-53% Y, YYYY%% select strftime ('% Y.%m.%d% Hvv% MJV% SJV)

2020.09.13 03:32:49

Select strftime ('% Y.%m.%d% HRV% MRV% SJT)

2020.09.12 20:33:24

SELECT LIKE usage

In SQL structured query language, LIKE statement plays an important role.

Grammar

The syntax format of the LIKE statement is:

Select * from table name where field name like corresponding value (substring)

It is mainly aimed at character fields, and its function is to retrieve the corresponding substrings in a character field column.

A): any string containing zero or more characters:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

LIKE'Mc%' will search for all strings that begin with the letter Mc, such as McBadden.

LIKE'%inger' searches all strings (such as Ringer, Stringer) that end with the letter inger.

LIKE'%en%' will search for all strings (such as Bennet, Green, McBadden) that contain the letter en anywhere.

Any single character: LIKE'_heryl' will search for the names of all six letters ending with the letter heryl (for example, Cheryl, Sheryl).

C: [] specify any single character in the range ([a murf]) or collection ([abcdef]):

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

LIKE' [CK] ars [EO] n 'will search for the following strings: Carsen, Karsen, Carson, and Karson (such as Carson).

LIKE' [Mmurz] inger' will search for all names (such as Ringer) that end with the string inger and begin with any single letter from M to Z.

D: [^] any single character that does not fall within the specified range ([a murf]) or collection ([abcdef]): LIKE'M [^ c]% 'will search for all names that start with the letter M and the second letter is not a c (such as MacFeather).

EPlus * is the same as the wildcard character in the DOS command and represents multiple characters: Centrc represents multiple characters such as cc,cBc,cbc,cabdfec.

F:? The same as in the DOS command? Wildcard characters, representing a single character: breadb for brb,bFb, etc.

Granger # is roughly the same as above, except that generations can only represent a single number. Kenzk stands for k1k.k8k.k0k.

F: [!] Rule out that it only represents a single character

Give an example

Let's give an example:

Example 1, the query name field contains the word "Ming".

Select * from table1 where name like'% Ming%'

Example 2, the query name field begins with the word "Li".

Select * from table1 where name like'Li *'

Example 3, query the number in the name field.

Select * from table1 where name like'% [0-9]%'

Example 4, query for lowercase letters in the name field.

Select * from table1 where name like'% [amurz]%'

Example 5, the query name field does not contain numbers.

Select * from table1 where name like'% [! 0-9]%'

What values can be listed in the above example is obvious. But here, it is important to explain the difference between the wildcard characters "" and "%". Many friends will ask, why do I use "%" instead of "" when I individually represent all characters in the above query? Let's see what happens with the following examples:

Select * from table1 where name like'* Ming * select * from table1 where name like'% Ming%'

You will see that the former statement lists all the records, while the latter shows the records that contain "clear" in the name field, so when we make a query with a substring in the character field, it is best to use "%" instead of "", only at the beginning or only at the end, and cannot replace any character by "*" at both ends.

Thank you for reading, the above is the content of "how to use clauses and functions in embedded database Sqlite3". After the study of this article, I believe you have a deeper understanding of how to use clauses and functions in embedded database Sqlite3, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.