目录
删除表
DROP TABLE '表名';
判断表是否存在
select count(*) from sqlite_master where type='table' and name = '表名';
删除记录
DELETE FROM '表名';//删除表所有记录
DELETE FROM '表名' WHERE '条件'//删除表中满足条件的记录
保留小数位数
select cast(mian_ji as decimal(18,4)) from '表名'
添加字段
ALTER TABLE 'LIFE' ADD 'MUSIC' VARCHAR( 10 )
判断字段是否存在
select * from sqlite_master where name='tablename' and sql like '%字段名%';
修改记录
update 表名 set 字段1= '1' where 字段2= '2'
计数
SELECT COUNT(*) FROM TBPLOTFACTOR WHERE '条件'
select plot_type , count(*) as num from 表 group by plot_type having '条件' //对某一字段按条件计数
子查询
SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY WHERE SALARY > 45000) ;
附加
//条件附加
ATTACH DATABASE '数据库完整路径' as 'TEST'
//附加表后可以联合操作:创建表、增删改
create table FS_DESIGNBOOK_LABEL as select * from TEST.FS_DESIGNBOOK_LABEL
//解除附加
DETACH DATABASE 'TEST'
创建表
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,//主键 且不能为null
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);
删除一列
1.根据原表创建一张新表
2.删除原表
3.将新表重名为旧表的名称
具体过程
1.创建一张旧表Student,包含id(主码),name, tel
create table student (
id integer primary key,
name text, tel text
)
2.接下来我们删除电话这个列,首先根据student表创建一张新表teacher
create table teacher as select id,name from student
3.然后我们删除student这个表
drop table if exists student
4.将teacher这个表重命名为student
alter table teacher rename to student
多表关联update
由于sqlite不支持inner jion操作:
update table1
set num1 = t2.num2
FROM table1 t1 INNER JOIN table2 t2
ON t1.id=t2.pid;
sqlite更新操作为:
update table1
set num1 = (select num2 from table2 where table2.pid=table1.id),
num11 = (select num22 from table2 where table2.pid=table1.id)
where...
update OutDates set KeshiId = (select KeshiId from KehuKeshi where KehuKeshi.KehuId=OutDates.KehuId )
创建索引
create index 索引名 on 表名(字段名);
如:
create index if not exists index_TBPLOTTREE_plot_no_and_tree_no_and_jchi_type on TBPLOTTREE (plot_no,tree_no,jchi_type)