当前位置： 首页 > news >正文

sqlite3数据库-sqlite语句1(五)

news 2025/9/2 7:03:42

DML(Data Manipulation Language,数据操作语言)

SELECT:查询表中的数据;

  1. SELECT语句中使用WHERE子句
SELECT <列名>,... FROM <表名> WHERE <条件表达式>;
SELECT id,name,purchase_price FROM Product; /*使用逗号分隔查询多列,顺序同子句顺序*/
SELECT * FROM 表名; /*查询所有*/
SELECT purchase_price AS price FORM Product; /*为列设定别名*/
SELECT DSITINCT type FROM Product; /*使用DISTINCT删除type列中重复数据*//*从表Product中查询type等于衣服的name列*/
SELECT name FROM Product WHERE type='衣服';/*使用运算表达式 从Product表中查询name和sale_price,并将sale_price乘2作为"sale_price_x2"列出*/
SELECT name,sale_price,sale_price * 2 AS "sale_price_x2" FROM Product;/* 所有包含NULL的计算,结果都是NULL *//*比较运算符 等于(==) 不相等(<>) 大于等于(>=) 大于(>) 小于等于(<=) 小于(<) */
/* 查询出sale_price等于500的name和type,列出 */
SELECT name,type FROM Product WHERE sale_price = 500;/* 查询出sale_price大于等于1000的name和type及sale_price记录 */
SELECT name,type,sale_price FROM Product WHERE sale_price >= 1000;/*查询登记日期在2022年11月12日前的记录 */
SELECT name,type,date FROM Product WHERE data < '2022-11-12';/*查询sale_price减去purchase_price大于等于500*/
SELECT name,type,purchar_price FROM Product WHERE Product WHERE sale_price - purchar_price >= 500;
  1. NULL特殊处理
/*查询NULL的记录*/
SELECT name,price FROM Product WHERE purchase_price IS NULL;
/*查询不为NULL的记录*/
SELECT name,price FROM Product WHERE purchase_price IS NOT NULL;
  1. AND运算符和OR运算符
/*查询商品类型为Tool且销售价价格大于等于1000的商品,列出name和purchar_price*/
SELECT name,purchar_price FROM Product WHERE type='Tool' AND sale_price >= 1000;/*查询商品类型为Tool或销售价价格大于等于1000的商品,列出name和purchar_price*/
SELECT name,purchar_price FROM Product WHERE type='Tool' OR sale_price >= 1000;
  1. 通过括号强化处理

注意:AND优先级高于OR, 想要优先执行OR运算符可以使用括号;

SELECT name,type,date FROM Product WHERE type='Work' AND (date = '02-11' OR date = '02-15');

INSERT:向表中插入新数据；

  1. 清单法插入一行
/*
sql = "CREATE TABLE ProductIns(" \"product_id     CHAR(4)     PRIMARY KEY    NOT NULL," \"product_name    VARCHAR(100)     NOT NULL," \"product_type    VARCHAR(32)     NOT NULL," \"sale_price     INTEGER        DEFAULT 0," \"purchase_price    INTEGER," \"regist_date DATE)";
*/INSERT INTO 表名 (列1,列2,...,列n) VALUES (val1,val2,...,valn);
/*
sql = "INSERT INTO ProductIns (product_id,product_name,product_type,sale_price,purchase_price,regist_date) VALUES ('001','毛衣','衣服',1000,500,'2009-09-20')";        
*/
2. 列清单的省略
INSERT INTO 表名 VALUES (val1,val2,...,valn);
/*
sql = "INSERT INTO ProductIns VALUES ('002','夹克','衣服',1000,600,'2009-09-21')";          
*/
3. 插入多行
sql = "INSERT INTO ProductIns VALUES ('003','拖鞋','鞋',1000,600,'2009-09-21'),"\"('004','内衣','衣服',1000,300,'2009-09-22'),""('005','皮鞋','鞋',1000,700,'2009-09-22')";
4. 插入NULL
sql = "INSERT INTO ProductIns VALUES ('0026','皮裤','裤子',1000,NULL,'2009-09-26')";

提示:插入NULL位置属性不能设置为NOT NUILL

5. 插入默认值
sql = "INSERT INTO ProductIns (product_id,product_name,product_type,purchase_price,regist_date) VALUES ('007','内裤','裤子',100,'2009-09-26')";

直接去掉该字段,提前是该字段有DEFAULT属性;

6. 从其他表复制数据
sql = "CREATE TABLE ProductCopy(" \"product_id    CHAR(4)     PRIMARY KEY NOT NULL," \"product_name VARCHAR(100)    NOT NULL," \"product_type VARCHAR(32)     NOT NULL," \"sale_price    INTEGER     DEFAULT 0," \"purchase_price    INTEGER," \"regist_date DATE)";sql = "INSERT INTO ProductCopy (product_id,product_name,product_type,sale_price,purchase_price,regist_date) SELECT * FROM ProductIns;";
或者
sql = "INSERT INTO ProductCopy (product_id,product_name,product_type,sale_price,purchase_price,regist_date) "\"SELECT product_id,product_name,product_type,sale_price,purchase_price,regist_date FROM ProductIns;";
6. 使用GROUP BY子句
sql = "CREATE TABLE ProductType(" \"product_type VARCHAR(32)     PRIMARY KEY NOT NULL," \"sale_price    INTEGER     DEFAULT 0," \"purchase_price    INTEGER)" ;sql = "INSERT INTO ProductType (product_type,sale_price,purchase_price) "\"SELECT product_type,SUM(sale_price),COUNT(purchase_price) FROM ProductIns GROUP BY product_type;";

补充:GROUP BY,HAVING,ORDER BY子句

GROUP BY,HAVING:从表中选取数据时用来改变抽取数据形式的;

ORDER BY:指定取得结果显示顺序的；

UPDATE:更新表中的数据;

  1. UPDATE语句+SET子句
UPDATE 表名 SET 列名 = 表达式sql = "UPDATE ProductIns SET regist_date = '2023-02-20'";//将表中regist_data全部更新成02-20
2. 搜索型UPDATE
UPDATE 表名 SET 列名=表达式 WHERE 条件sql = "UPDATE ProductIns SET sale_price = sale_price*5 WHERE product_type='衣服'";
3. NULL情空

只有未设置NOT NULL约束的列才可设置;

sql = "UPDATE ProductIns SET sale_price = NULL WHERE product_type='鞋'";
4. 更新多列
sql = "UPDATE ProductIns SET sale_price = 500,purchase_price=200 WHERE product_type='鞋'";

DROP/DELETE:删除表中的数据；

sql = "DROP TABLE ProductCopy;"; //删除表 sql = "DELETE FROM ProductType"; //删除后会保留数据表,仅删除内容;sql = "DELETE FROM ProductIns WHERE purchase_price <= 300";

不能使用GROUP BY,HAVING,ORDER BY; 删除表中数据时起不到什么作用;

不可恢复，谨慎删除;

事务

批量执行数据库语句时，使用事务能极大提高效率;实测在嵌入式开发板上update语句，开始事务后能从5s缩短到ms级别；

sql = "BEGIN TRANSACTION;"; //开启事务
sqlite3_exec(ppDb,sql, NULL ,NULL, &errMsg);...... 批量插入/更新if(条件){sql = "COMMIT;"; //提交事务sqlite3_exec(ppDb,sql, NULL ,NULL, &errMsg);
}else{sql = "ROLLBACK;"; //回滚  
}

int main(int argc, const char *argv[])
{int ret;sqlite3 *ppDb = NULL;char *errMsg = NULL;ret = sqlite3_open("./config.db", &ppDb);if(ret != SQLITE_OK){printf("open config.db failed %s\n",sqlite3_errmsg(ppDb));return -1;}printf("open config.db ok\n");/* 创建表ProductIns */char *sql = NULL;sql = "CREATE TABLE ProductIns(" \"product_id     CHAR(4)     PRIMARY KEY    NOT NULL," \"product_name    VARCHAR(100)     NOT NULL," \"product_type    VARCHAR(32)     NOT NULL," \"sale_price     INTEGER        DEFAULT 0," \"purchase_price    INTEGER," \"regist_date DATE)";ret = sqlite3_exec(ppDb,sql, NULL ,NULL, &errMsg);if(ret != SQLITE_OK){printf("sqlite3_exec create err %s\n",errMsg);sqlite3_free(errMsg);}/* 创建表ProductCopy */sql = "CREATE TABLE ProductCopy(" \"product_id    CHAR(4)     PRIMARY KEY NOT NULL," \"product_name VARCHAR(100)    NOT NULL," \"product_type VARCHAR(32)     NOT NULL," \"sale_price    INTEGER     DEFAULT 0," \"purchase_price    INTEGER," \"regist_date DATE)";ret = sqlite3_exec(ppDb,sql, NULL ,NULL, &errMsg);if(ret != SQLITE_OK){printf("sqlite3_exec create err %s\n",errMsg);sqlite3_free(errMsg);}//创建表ProductTypesql = "CREATE TABLE ProductType(" \"product_type VARCHAR(32)     PRIMARY KEY NOT NULL," \"sale_price    INTEGER     DEFAULT 0," \"purchase_price    INTEGER)" ;ret = sqlite3_exec(ppDb,sql, NULL ,NULL, &errMsg);if(ret != SQLITE_OK){printf("sqlite3_exec create err %s\n",errMsg);sqlite3_free(errMsg);}printf("crate config.db table ok\n");//开启事务sql = "BEGIN TRANSACTION;";ret = sqlite3_exec(ppDb,sql, NULL ,NULL, &errMsg);if(ret != SQLITE_OK){printf("sqlite3_exec create err %s\n",errMsg);sqlite3_free(errMsg);}/* insert sql */sql = "INSERT INTO ProductIns (product_id,product_name,product_type,sale_price,purchase_price,regist_date)"\"VALUES ('001','毛衣','衣服',1000,500,'2009-09-20')";          ret = sqlite3_exec(ppDb,sql, NULL ,NULL, &errMsg);if(ret != SQLITE_OK){printf("sqlite3_exec insert err %s\n",errMsg);sqlite3_free(errMsg);}sql = "INSERT INTO ProductIns VALUES ('002','夹克','衣服',1000,600,'2009-09-21')";          ret = sqlite3_exec(ppDb,sql, NULL ,NULL, &errMsg);if(ret != SQLITE_OK){printf("sqlite3_exec insert err %s\n",errMsg);sqlite3_free(errMsg);}sql = "INSERT INTO ProductIns VALUES ('003','拖鞋','鞋',1000,600,'2009-09-21'),"\"('004','内衣','衣服',1000,300,'2009-09-22'),""('005','皮鞋','鞋',1000,700,'2009-09-22')";          ret = sqlite3_exec(ppDb,sql, NULL ,NULL, &errMsg);if(ret != SQLITE_OK){printf("sqlite3_exec insert err %s\n",errMsg);sqlite3_free(errMsg);}sql = "INSERT INTO ProductIns VALUES ('006','皮裤','裤子',1000,NULL,'2009-09-26')";          ret = sqlite3_exec(ppDb,sql, NULL ,NULL, &errMsg);if(ret != SQLITE_OK){printf("sqlite3_exec insert err %s\n",errMsg);sqlite3_free(errMsg);}sql = "INSERT INTO ProductIns (product_id,product_name,product_type,purchase_price,regist_date) "\"VALUES ('007','内裤','裤子',100,'2009-09-26')";          ret = sqlite3_exec(ppDb,sql, NULL ,NULL, &errMsg);if(ret != SQLITE_OK){printf("sqlite3_exec insert err %s\n",errMsg);sqlite3_free(errMsg);}printf("insert config.db table ok\n");//从ProductIns表复制到ProductCopysql = "INSERT INTO ProductCopy (product_id,product_name,product_type,sale_price,purchase_price,regist_date) "\"SELECT product_id,product_name,product_type,sale_price,purchase_price,regist_date FROM ProductIns;";ret = sqlite3_exec(ppDb,sql, NULL ,NULL, &errMsg);if(ret != SQLITE_OK){printf("sqlite3_exec create err %s\n",errMsg);sqlite3_free(errMsg);}sql = "INSERT INTO ProductType (product_type,sale_price,purchase_price) "\"SELECT product_type,SUM(sale_price),COUNT(purchase_price) FROM ProductIns GROUP BY product_type;";ret = sqlite3_exec(ppDb,sql, NULL ,NULL, &errMsg);if(ret != SQLITE_OK){printf("sqlite3_exec create err %s\n",errMsg);sqlite3_free(errMsg);}sql = "COMMIT;";ret = sqlite3_exec(ppDb,sql, NULL ,NULL, &errMsg);if(ret != SQLITE_OK){printf("sqlite3_exec create err %s\n",errMsg);sqlite3_free(errMsg);}//更新sql = "UPDATE ProductIns SET regist_date = '2023-02-20'"; ret = sqlite3_exec(ppDb,sql, NULL ,NULL, &errMsg);if(ret != SQLITE_OK){printf("sqlite3_exec create err %s\n",errMsg);sqlite3_free(errMsg);}sql = "UPDATE ProductIns SET sale_price = sale_price*5 WHERE product_type='衣服'"; ret = sqlite3_exec(ppDb,sql, NULL ,NULL, &errMsg);if(ret != SQLITE_OK){printf("sqlite3_exec create err %s\n",errMsg);sqlite3_free(errMsg);}sql = "UPDATE ProductIns SET sale_price = NULL WHERE product_type='鞋'"; ret = sqlite3_exec(ppDb,sql, NULL ,NULL, &errMsg);if(ret != SQLITE_OK){printf("sqlite3_exec create err %s\n",errMsg);sqlite3_free(errMsg);}sql = "UPDATE ProductIns SET sale_price = 500,purchase_price=200 WHERE product_type='鞋'"; ret = sqlite3_exec(ppDb,sql, NULL ,NULL, &errMsg);if(ret != SQLITE_OK){printf("sqlite3_exec create err %s\n",errMsg);sqlite3_free(errMsg);}//删除sql = "DELETE FROM ProductIns WHERE purchase_price <= 300"; //删除后会保留数据表,仅删除内容;ret = sqlite3_exec(ppDb,sql, NULL ,NULL, &errMsg);if(ret != SQLITE_OK){printf("sqlite3_exec create err %s\n",errMsg);sqlite3_free(errMsg);}/* select sql */#if 0 sql = "SELECT * from ProductIns";int nRow, nCol;char **pazResult = NULL;ret = sqlite3_get_table(ppDb, sql, &pazResult, &nRow, &nCol, &errMsg);if(ret != SQLITE_OK){printf("sqlite3_get_table err %s\n",errMsg);sqlite3_free(errMsg);return -1;}printf("nRow = %d , nCol = %d\n", nRow, nCol);for(int i = 0; i < nCol*(nRow+1); i++){printf("pazResult[%d] = %s\n",i,pazResult[i]);}//正确且安全的释放内存sqlite3_free_table(pazResult);#endif sqlite3_close(ppDb);return 0;
}
http://www.lryc.cn/news/14937.html

相关文章：

  • 【图像分类】卷积神经网络之LeNet5网络模型实现MNIST手写数字识别
  • 前端开发环境搭建
  • 学习Flask之四、网页表单
  • CenterMask paper笔记
  • 06- OpenCV查找图像轮廓 (OpenCV基础) (机器视觉)
  • OpenGL学习日记之模型绘制
  • Springboot接口多个DTO入参的Postman上传方式
  • 软考各科目考核内容详细介绍，看这里
  • 连续时间信号与离散时间信号
  • TPM密钥管理、使用
  • return和finally执行顺序、运行时异常与一般异常异同、error和exception区别、Java异常处理机制原理与应用
  • 我为什么放弃WinUI3
  • 2023年全国最新安全员精选真题及答案2
  • 计算机408考研先导课---C语言难点
  • K8S 部署 Redis-Cluster 集群
  • [oeasy]python0089_大型机的衰落_Dec小型机崛起_PDP_VAX网络
  • Apache Shiro与Spring Security对比
  • 23春招-mysql事务相关高频面试题
  • 天线理论知识1——基础概念介绍
  • 【云原生之Docker实战】使用Docker部署StackEdit在线Markdown编辑器
  • 特征工程：特征构造以及时间序列特征构造
  • 单master部署简要步骤
  • 【算法基础】(一)基础算法 --- 前缀和与差分
  • c++提高篇——stack容器
  • HTTP安全与HTTPS协议
  • 【c++】类和对象4—c++对象模型和this指针
  • 嵌入式Qt 开发一个视频播放器
  • 阿里巴巴内网 Spring Cloud Alibaba 强势来袭，开创微服务的新时代
  • 边界检测方法总结
  • Softing dataFEED OPC Suite Extended新版本支持从XML文件中读取生产数据