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

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;

2. NULL特殊处理

/*查询NULL的记录*/
SELECT name,price FROM Product WHERE purchase_price IS NULL;
/*查询不为NULL的记录*/
SELECT name,price FROM Product WHERE purchase_price IS NOT NULL;

3. 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;

4. 通过括号强化处理

注意: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;
}