网站建设 客户定位免费开发软件制作平台
DML(Data Manipulation Language,数据操作语言)
SELECT:查询表中的数据;
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;NULL特殊处理
/*查询NULL的记录*/
SELECT name,price FROM Product WHERE purchase_price IS NULL;
/*查询不为NULL的记录*/
SELECT name,price FROM Product WHERE purchase_price IS NOT NULL;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;
通过括号强化处理
注意:AND优先级高于OR, 想要优先执行OR运算符可以使用括号;
SELECT name,type,date FROM Product WHERE type='Work' AND (date = '02-11' OR date = '02-15');INSERT:向表中插入新数据；
清单法插入一行
/*
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:更新表中的数据;
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;
}