sqlites数据库1（增删改查）

//建立数据库
linux@ubuntu:~$ sqilte3 kaka.db
//创建表格1，表名为学生，包含三列（字段）即id，name，age
sqlite> create table student (id int,name char,age int);
//插入信息（每一行为一条记录）
sqlite> insert into student values (1,‘zhangjiao’,26);
sqlite> insert into student values (2,‘tangqingqing’,27);
sqlite> insert into student values (3,‘xulihong’,25);
sqlite> .header on//显示表头
sqlite> select * from student;//查看表内信息
//结果
id|name|age
1|zhangjiao|26
2|tangqingqing|27
3|xulihong|25
//按条件查找用where = 为精确查找
sqlite> select * from student where name = ‘zhangjiao’;
//查找结果
id|name|age
1|zhangjiao|26
//再插入信息
sqlite> insert into student values (5,‘林俊杰’,36);
sqlite> insert into student values (6,‘张杰’,37);
//模糊查找 like，_0-1个字符
sqlite> select * from student where name like ‘_杰’;
//结果
id|name|age
6|张杰|37
//%0~n个字符
sqlite> select * from student where name like ‘%杰’;
//结果
id|name|age
5|林俊杰|36
6|张杰|37
//按多个条件查找，and 或 or
sqlite> select * from student where age > 28 and age < 40;
id|name|age
5|林俊杰|36
6|张杰|37
sqlite> insert into student values (4,‘王源’,19);
sqlite> select * from student;
id|name|age
1|zhangjiao|26
2|tangqingqing|27
3|xulihong|25
5|林俊杰|36
6|张杰|37
4|王源|19
//排序（正序）
sqlite> select * from student order by age;
id|name|age
4|王源|19
3|xulihong|25
1|zhangjiao|26
2|tangqingqing|27
5|林俊杰|36
6|张杰|37
//排序（逆序）
sqlite> select * from student order by age desc;
id|name|age
6|张杰|37
5|林俊杰|36
2|tangqingqing|27
1|zhangjiao|26
3|xulihong|25
4|王源|19
//排序（逆序)且只取前两个
sqlite> select * from student order by age desc limit 2;
id|name|age
6|张杰|37
5|林俊杰|36
//数据修改
sqlite> update student set name = ‘王俊凯’ where id = 3;
sqlite> update student set name = ‘王一博’ where id = 2;
sqlite> update student set name = ‘何炅’ where id = 1;
sqlite> select * from student;
id|name|age
1|何炅|26
2|王一博|27
3|王俊凯|25
5|林俊杰|36
6|张杰|37
4|王源|19
//删除记录
sqlite> delete from student where id = 5;
sqlite> select * from student;
id|name|age
1|何炅|26
2|王一博|27
3|王俊凯|25
6|张杰|37
4|王源|19
//删除表
sqlite> drop table student;
//创建表2time
//插入时间列
sqlite> create table time (id int,name char,int age,dt datetime);
//不+8 则有时差
sqlite> insert into time values (1,‘王源’,19,datetime(‘now’));
//+8 显示北京时间
sqlite> insert into time values (2,‘王俊凯’,20,datetime(‘now’,’+8 hours’));
//北京时间去年
sqlite> insert into time values (3,‘王俊凯’,23,datetime(‘now’,’+8 hours’,’-1 years’));
sqlite> select * from time;
id|name|int|dt
1|王源|19|2020-03-08 12:19:31
2|王俊凯|20|2020-03-08 20:19:45
3|王俊凯|23|2019-03-08 20:20:52
//创建表3
//自动增长列
sqlite> create table zizeng (id INTEGER PRIMARY KEY ASC,name char,int age,dt datetime);
sqlite> insert into zizeng values (1,‘王俊凯’,20,datetime(‘now’));
sqlite> insert into zizeng values (NULL,‘王源’,19,datetime(‘now’));
sqlite> select * from zizeng;
id|name|int|dt
1|王俊凯|20|2020-03-08 12:34:21
2|王源|19|2020-03-08 12:34:28