1、sqllite 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 COMPANY VALUES(1,Paul,32,California,20000);INSERT INTO COMPANY VALUES(2,Allen,25,Texas,15000);
2、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,2
3、9,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.*,COUN
4、T(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个记录,使用O
5、FFSET关键字,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),
6、也就是1。与LIKE运算符不同的是,GLOB是大小写敏感的,对于下面的通配符,它遵循UNIX的语法。星号 (*)问号 (?)星号(*)代表零个、一个或多个数字或字符。问号(?)代表一个单一的数字或字符。这些符号可以被组合使用。下面一些实例演示了 带有*和?运算符的GLOB子句不同的地方:下面是一个实例,它显示COMPANY表中AGE以2开头的所有记录,如下所示:下面是一个实例,它显示COMPANY表中ADDRESS文本里包含一个连字符(-)的所有记录:4,Distinct关键字过滤重复记录SQLite 的DISTINCT关键字与 SELECT 语句一起使用,来消除所有重复的记录,并只获取唯一一
7、次记录。有可能出现一种情况,在一个表中有多个重复的记录。当提取这样的记录时,DISTINCT 关键字就显得特别有意义,它只获取唯一一次记录,而不是获取重复记录。5,字符串连接操作问题地址:sqlite CREATE TABLE t1(id int, name varchar(60);sqlite INSERT INTO t1 VALUES(4,1);sqlite select * from t1;id name- -4 1sqlite update t1 set name=(id/2)|substr(name,instr(name,),length(name)-instr(name,)+1)
8、where id=4;sqlite select * from t1;id name- -4 2sqlite6,对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;
9、ID NAME AGE ADDRESS SALARY- - - - -7 James 24 100008 Xiaoteng 29sqlite查询不为null的记录sqlite SELECT * FROM COMPANY WHERE ADDRESS IS NOT NULL;ID NAME AGE ADDRESS SALARY- - - - -1 Paul 32 California 200002 Allen 25 Texas 150003 Teddy 23 Norway 200004 Mark 25 Rich-Mond 650005 David 27 Texas 850006 Kim 22 So
10、uth-Hall 45000sqlite7,子查询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
11、INTO table_name (column1 , column2 ) SELECT *|column1 , column2 FROM table1 , table2 WHERE VALUE OPERATOR 实例如下:sqlite INSERT INTO COMPANY_BKPSELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY) ;UPDATE语句中的子查询使用,基本语法如下:UPDATE tableSET column_name = new_value WHERE OPERATOR VALUE (SELECT COLUMN_
12、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 (SEL
13、ECT AGE FROM COMPANY_BKP WHERE AGE 27 );8,EXPLAIN分析没有建立索引之前,分析都是表扫描:sqlite EXPLAIN SELECT * FROM COMPANY WHERE Salary EXPLAIN QUERY PLAN SELECT * FROM COMPANY WHERE Salary 建立索引,再进行EXPLAIN分析查看结果,走了idx_sal索引扫描:sqlite CREATE INDEX idx_sal ON COMPANY(SALARY);sqlite EXPLAIN SELECT * FROM COMPANY WHERE Sa
14、lary 20000;addr opcode p1 p2 p3 p4 p5 comment- - - - - - - -0 Trace 0 0 0 001 Integer 20000 1 0 002 Goto 0 25 0 003 OpenRead 0 2 0 5 004 OpenRead 1 3 0 keyinfo(1, 005 Affinity 2 0 0 cb 006 Rewind 1 22 2 0 007 SCopy 1 2 0 008 IsNull 2 22 0 009 Affinity 2 1 0 cb 0010 IdxGE 1 22 2 1 0011 Column 1 0 3 0012 IsNull 3 21 0 0013 IdxRowid 1 3 0 0014 Seek 0 3 0 0015 Column 0 0 4 0016 Column 0 1 5 0017 Column 0 2 6 00 18 Column 0 3 7 0019 Column 1 0 8 0020 ResultRow 4 5 0 0021 Next 1 10 0 0022 Close 0 0 0 0023 Close 1 0 0 0024 Halt 0 0 0 0025 Transactio 0 0 0
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1