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);
}
//创建表ProductType
sql = "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表复制到ProductCopy
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;";
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;
}
文章评论