search
HomeDatabaseMysql Tutorial[Sqlite]移动嵌入式数据库Sqlite的日常SQL操作语句汇总

序言: 嵌入式数据库Sqlite的基本sql使用汇总,使用测试起来,与关系型数据库mysql在语法上有很多的相似之处,先准备测试数据: CREATE TABLE COMPANY(ID INT NOT NULL, NAME VARCHAR(20),AGE INT,ADDRESS VARCHAR(20),SALARY DECIMAL(7,2)); INSERT INTO CO

序言:

嵌入式数据库Sqlite的基本sql使用汇总,使用测试起来,与关系型数据库mysql在语法上有很多的相似之处,先准备测试数据:

CREATE TABLE COMPANY(ID INT NOT NULL, NAME VARCHAR(20),AGE INT,ADDRESS VARCHAR(20),SALARY DECIMAL(7,2));

INSERT INTO "COMPANY" VALUES(1,'Paul',32,'California',20000);

INSERT INTO "COMPANY" VALUES(2,'Allen',25,'Texas',15000);

INSERT INTO "COMPANY" VALUES(3,'Teddy',23,'Norway',20000);

INSERT INTO "COMPANY" VALUES(4,'Mark',25,'Rich-Mond',65000);

INSERT INTO "COMPANY" VALUES(5,'David',27,'Texas',85000);

INSERT INTO "COMPANY" VALUES(6,'Kim',22,'South-Hall',45000);

INSERT INTO "COMPANY" VALUES(7,'James',24,NULL,10000);

INSERT INTO "COMPANY" VALUES(8,'Xiaoteng',29,NULL,NULL);

1,分组统计排序

GROUP BY 进行分组统计数据,命令如下:

sqlite> SELECT NAME, SUM(SALARY) SALARY_SUM, COUNT(1) COUNT_NUM FROM COMPANY GROUP BY NAME; 

\

ORDER BY 进行排序,命令如下:

sqlite> SELECT NAME, SUM(SALARY) SALARY_SUM, COUNT(1) COUNT_NUM FROM COMPANY GROUP BY NAME ORDER BY SALARY_SUM ASC;

\

HAVING 字句过滤数据记录,命令如下:

SELECT c.*,COUNT(1) COUNT_NUM FROM COMPANY c GROUP BY c.NAME HAVING (COUNT_NUM) > 1 ORDER BY COUNT_NUM ;

PS:在一个查询中,HAVING 子句必须放在 GROUP BY 子句之后,必须放在 ORDER BY 子句之前。下面是包含 HAVING 子句的 SELECT 语句的语法:

\

2,Limit分页统计语句

SQLite 的 LIMIT 子句用于限制由 SELECT 语句返回的数据数量。

第一页取值SQL: SELECT * FROM COMPANY ORDER BY ID LIMIT 0,3; 也可以从一个特定的偏移开始提取记录,从第四位开始提取 3 个记录,使用OFFSET关键字,SELECT * FROM COMPANY ORDER BY ID LIMIT 3 OFFSET 0;PS:首页从0开始取值。
\

第二页取值SQL: SELECT * FROM COMPANY ORDER BY ID LIMIT 3,3; 也可以从一个特定的偏移开始提取记录,从第四位开始提取 3 个记录,使用OFFSET关键字,SELECT * FROM COMPANY ORDER BY ID LIMIT 3 OFFSET 3;如下图所示:
\

3,Glob匹配字句

SQLite 的 GLOB 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配,GLOB 运算符将返回真(true),也就是 1。与 LIKE 运算符不同的是,GLOB 是大小写敏感的,对于下面的通配符,它遵循 UNIX 的语法。

星号 (*)

问号 (?)

星号(*)代表零个、一个或多个数字或字符。问号(?)代表一个单一的数字或字符。这些符号可以被组合使用。

下面一些实例演示了 带有 '*' 和 '?' 运算符的 GLOB 子句不同的地方:

\

下面是一个实例,它显示 COMPANY 表中 AGE 以 2 开头的所有记录,如下所示:

\ 

下面是一个实例,它显示 COMPANY 表中 ADDRESS 文本里包含一个连字符(-)的所有记录:

\

4,Distinct关键字过滤重复记录

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

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

5,字符串连接操作

问题地址:http://bbs.csdn.net/topics/390886865

sqlite> CREATE TABLE t1(id int, name varchar(60));

sqlite> INSERT INTO "t1" VALUES(4,'1@test.cn');

sqlite> select * from t1;

id name

---------- ----------

4 1@test.cn

sqlite> update t1 set name=(id/2)||substr(name,instr(name,'@'),length(name)-instr(name,'@')+1) where id=4;

sqlite> select * from t1;

id name

---------- ----------

4 2@test.cn

sqlite>

6,对Null值的处理

往表里面录入Null值

sqlite> INSERT INTO COMPANY(ID,NAME,AGE,ADDRESS,SALARY) VALUES(8,'Xiaoteng',29,NULL,18000);

sqlite>

修改某个字段为null值

sqlite> UPDATE COMPANY SET SALARY = NULL WHERE ID=8;

sqlite>

查询为null的记录

sqlite> SELECT * FROM COMPANY WHERE ADDRESS IS NULL;

ID NAME AGE ADDRESS SALARY

---------- ---------- ---------- ---------- ----------

7 James 24 10000

8 Xiaoteng 29

sqlite>

查询不为null的记录

sqlite> SELECT * FROM COMPANY WHERE ADDRESS IS NOT NULL;

ID NAME AGE ADDRESS SALARY

---------- ---------- ---------- ---------- ----------

1 Paul 32 California 20000

2 Allen 25 Texas 15000

3 Teddy 23 Norway 20000

4 Mark 25 Rich-Mond 65000

5 David 27 Texas 85000

6 Kim 22 South-Hall 45000

sqlite>

7,子查询

SELECt中的基本语法如下:

SELECT column_name [, column_name ]

FROM table1 [, table2 ]

WHERE column_name OPERATOR

(SELECT column_name [, column_name ]

FROM table1 [, table2 ]

[WHERE])

实例如下:

sqlite> SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY WHERE SALARY > 45000);

INSERT语句中的子查询使用,基本语法:

INSERT INTO table_name [ (column1 [, column2 ]) ]

SELECT [ *|column1 [, column2 ]

FROM table1 [, table2 ]

[ WHERE VALUE OPERATOR ]

实例如下:

sqlite> INSERT INTO COMPANY_BKP

SELECT * FROM COMPANY

WHERE ID IN (SELECT ID

FROM COMPANY) ;

UPDATE语句中的子查询使用,基本语法如下:

UPDATE table

SET column_name = new_value

[ WHERE OPERATOR [ VALUE ]

(SELECT COLUMN_NAME

FROM TABLE_NAME)

[ WHERE) ]

实例如下:

sqlite> UPDATE COMPANY

SET SALARY = SALARY * 0.50

WHERE AGE IN (SELECT AGE FROM COMPANY_BKP

WHERE AGE >= 27 );

DELETE语句中的子查询使用,语法如下:

DELETE FROM TABLE_NAME

[ WHERE OPERATOR [ VALUE ]

(SELECT COLUMN_NAME

FROM TABLE_NAME)

[ WHERE) ]

实例如下:

sqlite> DELETE FROM COMPANY

WHERE AGE IN (SELECT AGE FROM COMPANY_BKP

WHERE AGE > 27 );

8,EXPLAIN分析

没有建立索引之前,分析都是表扫描:

sqlite> EXPLAIN SELECT * FROM COMPANY WHERE Salary

addr opcode p1 p2 p3 p4 p5 comment

---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

0 Trace 0 0 0 00

1 Integer 20000 1 0 00

2 Goto 0 16 0 00

3 OpenRead 0 2 0 5 00

4 Rewind 0 14 0 00

5 Column 0 4 2 00

6 Ge 1 13 2 collseq(BI 6b

7 Column 0 0 4 00

8 Column 0 1 5 00

9 Column 0 2 6 00

10 Column 0 3 7 00

11 Column 0 4 8 00

12 ResultRow 4 5 0 00

13 Next 0 5 0 01

14 Close 0 0 0 00

15 Halt 0 0 0 00

16 Transactio 0 0 0 00

17 VerifyCook 0 1 0 00

18 TableLock 0 2 0 COMPANY 00

19 Goto 0 3 0 00

sqlite> EXPLAIN QUERY PLAN SELECT * FROM COMPANY WHERE Salary

order from detail

---------- ---------- -------------

0 0 TABLE COMPANY

sqlite>

建立索引,再进行EXPLAIN分析查看结果,走了idx_sal索引扫描:

sqlite> CREATE INDEX idx_sal ON COMPANY(SALARY);

sqlite> EXPLAIN SELECT * FROM COMPANY WHERE Salary

addr opcode p1 p2 p3 p4 p5 comment

---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

0 Trace 0 0 0 00

1 Integer 20000 1 0 00

2 Goto 0 25 0 00

3 OpenRead 0 2 0 5 00

4 OpenRead 1 3 0 keyinfo(1, 00

5 Affinity 2 0 0 cb 00

6 Rewind 1 22 2 0 00

7 SCopy 1 2 0 00

8 IsNull 2 22 0 00

9 Affinity 2 1 0 cb 00

10 IdxGE 1 22 2 1 00

11 Column 1 0 3 00

12 IsNull 3 21 0 00

13 IdxRowid 1 3 0 00

14 Seek 0 3 0 00

15 Column 0 0 4 00

16 Column 0 1 5 00

17 Column 0 2 6 00

18 Column 0 3 7 00

19 Column 1 0 8 00

20 ResultRow 4 5 0 00

21 Next 1 10 0 00

22 Close 0 0 0 00

23 Close 1 0 0 00

24 Halt 0 0 0 00

25 Transactio 0 0 0 00

26 VerifyCook 0 2 0 00

27 TableLock 0 2 0 COMPANY 00

28 Goto 0 3 0 00

sqlite> EXPLAIN QUERY PLAN SELECT * FROM COMPANY WHERE Salary

order from detail

---------- ---------- --------------------------------

0 0 TABLE COMPANY WITH INDEX idx_sal

sqlite>

9,删除重复数据并且保留最新一条记录

录入测试数据

sqlite> .dump

PRAGMA foreign_keys=OFF;

BEGIN TRANSACTION;

CREATE TABLE COMPANY(ID INT NOT NULL, NAME VARCHAR(20),AGE INT,ADDRESS VARCHAR(20),SALARY DECIMAL(7,2));

INSERT INTO "COMPANY" VALUES(2,'Allen',25,'Texas',15000);

INSERT INTO "COMPANY" VALUES(3,'Teddy',23,'Norway',20000);

INSERT INTO "COMPANY" VALUES(4,'Mark',25,'Rich-Mond',65000);

INSERT INTO "COMPANY" VALUES(5,'David',27,'Texas',85000);

INSERT INTO "COMPANY" VALUES(6,'Kim',22,'South-Hall',45000);

INSERT INTO "COMPANY" VALUES(7,'James',24,'Houston',10000);

INSERT INTO "COMPANY" VALUES(7,'James',28,'Houston',20000);

INSERT INTO "COMPANY" VALUES(4,'Mark',29,'Rich-Mond',95000);

COMMIT;

sqlite> 

查看重复记录数

sqlite> select * from company order by name;

ID NAME AGE ADDRESS SALARY

---------- ---------- ---------- ---------- ----------

2 Allen 25 Texas 15000

5 David 27 Texas 85000

7 James 24 Houston 10000

7 James 28 Houston 20000

6 Kim 22 South-Hall 45000

4 Mark 25 Rich-Mond 65000

4 Mark 29 Rich-Mond 95000

3 Teddy 23 Norway 20000

sqlite>

通过rowid来删除重复记录

sqlite> DELETE FROM COMPANY WHERE rowid NOT IN(SELECT MAX(rowid) rowid FROM COMPANY GROUP BY NAME);

sqlite> 

再查看最新的数据记录,已经删除了重复NAME的记录

sqlite> select * from company;

ID NAME AGE ADDRESS SALARY

---------- ---------- ---------- ---------- ----------

2 Allen 25 Texas 15000

3 Teddy 23 Norway 20000

5 David 27 Texas 85000

6 Kim 22 South-Hall 45000

7 James 28 Houston 20000

4 Mark 29 Rich-Mond 95000

sqlite>

\

\

参考文章:http://www.w3cschool.cc/sqlite/sqlite-tutorial.html

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How do you handle database upgrades in MySQL?How do you handle database upgrades in MySQL?Apr 30, 2025 am 12:28 AM

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

What are the different backup strategies you can use for MySQL?What are the different backup strategies you can use for MySQL?Apr 30, 2025 am 12:28 AM

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

What is MySQL clustering?What is MySQL clustering?Apr 30, 2025 am 12:28 AM

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

How do you optimize database schema design for performance in MySQL?How do you optimize database schema design for performance in MySQL?Apr 30, 2025 am 12:27 AM

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

How can you optimize MySQL performance?How can you optimize MySQL performance?Apr 30, 2025 am 12:26 AM

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

How to use MySQL functions for data processing and calculationHow to use MySQL functions for data processing and calculationApr 29, 2025 pm 04:21 PM

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

An efficient way to batch insert data in MySQLAn efficient way to batch insert data in MySQLApr 29, 2025 pm 04:18 PM

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

Steps to add and delete fields to MySQL tablesSteps to add and delete fields to MySQL tablesApr 29, 2025 pm 04:15 PM

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

MinGW - Minimalist GNU for Windows

MinGW - Minimalist GNU for Windows

This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function