Sqlite3 基本使用
2018-07-02 本文已影响0人
改名_f64e
Sqlite 命令
格式化输出内容
.header on
.timer on
.mode column
显示配置信息
.show
打开指定数据库
.open <数据库名称>
打开数据库/创建数据库
.database <数据库名称>(存在打开,不存在创建)
导出数据库变成sql文本
sqlite3 <test.db> .dump > <test.sql>
从sql文本回复到数据库
sqlite3 <test.db> < <test.sql>
设置显示列的宽度
.width 20 20 20 20 20 20 20 ...
显示创建表的sql语句,查看表中列,数据类型等
.schema <表名>(不要加 ; )
退出Sqlite
.quit
Sqlite 存储类型
NULL NULL值(不是空值)
INTEGER 带符号的整数
REAL 浮点值(8字节)
TEXT 文本字符串(UTF-8,UTF-16BE,UTF-16LE)
BLOB BLOB数据类型
(Sqlite 没有单独的Boolean类型,在数据库中存储为整数0(false),1(true))
(Sqlite 没有单独的Date类,存储时会变成TEXT,INTEGER,REAL类型)
创建表
语法: create table <tableName> (ID <TYPE> primary key autoincrement <condition>,
name <type> <condition> ...);
例子: create table test (id integer primary key autoincrement not null,name text);
autoincrement:只能用于primary key
删除表
DROP TABLE table_name;
例子: drop table test1;
插入数据
语法: insert into <tableName> (cloumnName1, cloumnName2, cloumnName3,...columnN)
values(value1, value2, value3,...valueN);
(insert into <TABLE_NAME> (colume_1_value,colume_2_value,....>))
(这种方法必须每一列都要传值)
例子: insert into test (name) values ('胖虎');
数据库中数据
sqlite> select * from test;
id name age price
---------- ---------- ---------- ----------
1 qqq 10 100.0
2 www 11 101.0
3 eee 12 102.0
4 rrr 13 103.0
5 ttt 14 104.0
6 yyy 15 1055.0
7 uuu 15 106.0
8 iii 16 1077.0
9 iippp 16 1088.0
10 yyuuuu 17 10999.0
Run Time: real 0.021 user 0.010000 sys 0.000000
查询数据
语法: select <columnName1>,<columnName2>,... from <table_name>;
例子: select * from test;(显示test表中所有的列,显示所有的数据)
select id from test;(显示test表中id那一列,显示所有数据)
运算符
算数运算符
+(加), -(减), *(乘), /(除), %(取余)
.mode line (设置模式)
例子: select 10 + 20 = 30
...
比较运算符
a==b(两个值相等,true)
a!=b(两个值不等,ture)
<>a(大于并且小于,true)
a>b(大于,true)
a<b(小于,true)>
a>=b(大于等于,true)
a<=b(小于等于,true)
a!>b(不大于,true)
a!<b(不小于,true)>
语法: select <columnName>,... from <tableName> where <columnName> <运算符> value;
例子: select * from test where sex = 100;
select * from test where sex !=100;
逻辑运算符
and :在where中可以连接多个条件
sqlite> select * from test where age > 13 and price >= 105;
id name age price
---------- ---------- ---------- ----------
6 yyy 15 1055.0
7 uuu 15 106.0
8 iii 16 1077.0
9 iippp 16 1088.0
10 yyuuuu 17 10999.0
Run Time: real 0.002 user 0.000000 sys 0.000000
between :在两个值之间进行搜索
sqlite> select * from test where age between 12 and 17;
id name age price
---------- ---------- ---------- ----------
3 eee 12 102.0
4 rrr 13 103.0
5 ttt 14 104.0
6 yyy 15 1055.0
7 uuu 15 106.0
8 iii 16 1077.0
9 iippp 16 1088.0
10 yyuuuu 17 10999.0
Run Time: real 0.003 user 0.000000 sys 0.010000
exists :
in :返回值等于匹配值的数据
sqlite> select * from test where age in (12,15);
id name age price
---------- ---------- ---------- ----------
3 eee 12 102.0
6 yyy 15 1055.0
7 uuu 15 106.0
Run Time: real 0.002 user 0.000000 sys 0.000000
not in :和in相反
sqlite> select * from test where age not in (12,15);
id name age price
---------- ---------- ---------- ----------
1 qqq 10 100.0
2 www 11 101.0
4 rrr 13 103.0
5 ttt 14 104.0
8 iii 16 1077.0
9 iippp 16 1088.0
10 yyuuuu 17 10999.0
Run Time: real 0.002 user 0.000000 sys 0.000000
like :相似匹配,后面字符不检查用 % ,大小写不敏感
sqlite> select * from test where name glob 'y*';
id name age price
---------- ---------- ---------- ----------
6 yyy 15 1055.0
10 yyuuuu 17 10999.0
Run Time: real 0.002 user 0.000000 sys 0.000000
sqlite> select * from test where name like 'Y%';
id name age price
---------- ---------- ---------- ----------
6 yyy 15 1055.0
10 yyuuuu 17 10999.0
Run Time: real 0.002 user 0.000000 sys 0.000000
glob :相似匹配,后面字符不检查用 % ,大小写不敏感
sqlite> select * from test where name glob 'y*';
id name age price
---------- ---------- ---------- ----------
6 yyy 15 1055.0
10 yyuuuu 17 10999.0
Run Time: real 0.002 user 0.000000 sys 0.000000
sqlite> select * from test where name glob 'Y*';
Run Time: real 0.003 user 0.000000 sys 0.000000
not :否定,一般和not in / not between 等合用
or :或
sqlite> select * from test where age > 13 or price < 200;
is null :用于把某个值和NULL进行比对
sqlite> select * from test where name is null;
Run Time: real 0.001 user 0.000000 sys 0.000000
is :类似于 =
is not :类似于 !=
更新 update
语法: update <tableName> set columnName1 = value,columnName2 = value ,...where ...;
例子: sqlite> update test set name = 'vvvvv' where id =1;
sqlite> update test set name = 'vvvvv' ;(不指定条件,那么所有的数据都会修改)
删除 delete
语法: delete from <tableName> where ...;
例子: sqlite> delete from test where id = 1;
like
语法: % 和 _
%:表示匹配0个,1个,或者多个数据或字符
_:表示匹配1个字符,用来占位(比如说长度为3的字符串,___)
例子: '1%': 表示以1开头的数字或字符串
'%2': 表示以2结尾的数字或字符串
'2_3': 以2开头3结尾长度为3的数据或字符串
glob
语法: * 和 ?
*:表示匹配0个,1个,或者多个数据或字符(大小写敏感)
?:表示匹配1个字符,用来占位(比如说长度为3的字符串,???)(大小写敏感)
例子: 和like一样
limit
语法: select <columnName1>,<columnName2>... from <tableName> where <condition> limit <number> offset <number>;
解释:默认的offset = 0, limit : 从offset之后的多少行
例子: sqlite> select * from test where price > 105 limit 5;
order by
语法: select <columnName1> ,... from <tableName> where <condition> order by <columnName> <ASC||DESC> limit 4;
默认是 ASC:升序,DESC:降序
例子: sqlite> select * from test where price < 103 order by price desc limit 5 ;
group by
语法: select <columnName> , ... from <tableName> where <condition>
group by <columnName> order by <columnName> <ASC||DESC> limit 4;
解释:group by : 根据列名进行分组,列名的值相同的会合并,和sum(),max等方法合用
having
语法: select <columnName> , ... from <tableName> where <condition>
group by <columnName> having <condition> order by <columnName> <ASC||DESC> limit 4;
解释:对 group by 的结果再次进行条件匹配
例子: select * from test where price > 102 group by name having count(name) < 2
order by name limit 4;
distinct
语法: select distinct ....
解释:过滤相同的数据
