Sqlite3数据库基本操作

下载

下载源码，配系统环境变量

增删改查

一、查

#查看全表
select * from tablename;

#查看符合条件下的数据
select * from tablename where id is null;    //id值为空
select * from tablename where id = 1 and column1 = 1; //id值为1和column1的值为1的所有数据
select * from tablename where id = 1 or column1 = 1;  //id值为1或column1的值为1的所有数据
select max(id) from tablename;               //id的最大值
select distinct id from tablename;           //去掉重复值

 二、增

#插入数据
insert into tablename (column1,column2) values (key1,key2);    //列名column1的值为key1


#/*表tablename中id为null的列名column1的值为表tablename1的id为1的column3的值*/
insert into tablename (column1,column2) values ((select column3 from tablename1 where id = 1),(select column4 from tablename3 where id = 3)) where id is null;          
                                    /*列名column1的值为表tablename1的id为1的column3的值*/



#将tablename1表的column3和column4列的所有的数据导入到tablename表的column1和column2列
insert into tablename (column1,column2) values select column3,column4 from tablename1;

三、改

#改(id值为1的column1列的值修改为key)
update tablename set column1 = key where id = 1;

四、删

#删除tablename表的所有数据
delete from tablename;


#删除tablename表的id值为1的数据
delete from tablename where id = 1;

附属

创建新表

#如果列的属性设为自动递增，增加autoincrement即可

CREATE TABLE database_name.table_name(
   column1 datatype  PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

删除表

#删除数据库databasename的tablename表
DROP TABLE databasename.tablename;

#删除表tablename
DROP TABLE tablename;

alter（表重命名、添加新的列）

#重命名表
alter table tablename rename to newtablename;

#添加新列
alter table tablename add newcolumnname int;

实现查询两张表的两个不同名称的列，查询结果并为一列

select netid as com from nettable union all select comid as com from comtable;

#升序
sqlite> select netid as com from nettable union all select comid as com from comtable order by com asc;

#降序
sqlite> select netid as com from nettable union all select comid as com from comtable order by com desc;

#升序且去除重复值
sqlite> select distinct netid as com from nettable union all select distinct comid as com from comtable order by com asc;

实现当插入的表无任何数据是，从1开始排列（coalesce）

sprintf(sql, "insert into %s ('%s', id) values ('%s', COALESCE((select max(id) from qtqktable), 0) + 1);", adddata[1], comname, adddata[2]);//该代码处于循环插入语句中


/*
使用了 COALESCE 函数来处理 id 列的自增。这个语句的目的是将一条新记录插入到指定的表中，并且 id 列的值是当前表中最大 id 值加 1。如果表中没有记录，则 id 从 1 开始。
*/

查看两个表中的对应列的最大值

//最大值
SELECT MAX(id) AS max_id FROM (
    SELECT netid AS id FROM nettable
    UNION
    SELECT comid AS id FROM comtable
);



//合并为一个列查看
    SELECT netid AS id FROM nettable
    UNION
    SELECT comid AS id FROM comtable


//在上面的例子，增加列名为wh的列，值为com值来源得表的前三个字母
SELECT comid AS com, 'com' AS wh FROM comtable WHERE updown = 'down'
UNION ALL
SELECT netid AS com, 'net' AS wh FROM nettable WHERE updown = 'down'
ORDER BY com ASC;

一对多复杂查询

已知表数据为：
 select * from devtable;
id          com         dev         cip         cport       jieshiqi      jiange
----------  ----------  ----------  ----------  ----------  ------------  ----------
1           0           1           0           0           mp501d-h.xml  601
2           3           1
4           2           1
5           2           2
6           1           1           12.1        665         NULL          566
7           0           2
8           0           3
查询的效果为：
com	dev
0		1,2,3
1		1
2		1,2
3		1

实现命令为：
select com,group_concat(dev) as dev from devtable group by com order by com asc;