常规查询
- 查询所有字段:
select * from 表名;
- 查询指定字段:
select 列1,列2,... from 表名;
- 使用 as 给字段起别名:
select 字段 as 名字.... from 表名;
- 查询某个表的某个字段:
select 表名.字段 .... from 表名;
- 可以通过 as 给表起别名:
select 别名.字段 .... from 表名 as 别名;
- 消除重复行:
distinct 字段
sqlite> select * from stu;
id name class score
-- ---- ----- -----
1 小宁 3 60
2 小雷 5 70
3 小徐 1 80
4 小伟 2 80
5 小涛 3 90
6 小梦 4 90
7 小吴 1 85
8 小张 2 95
sqlite> select name,score from stu;
name score
---- -----
小宁 60
小雷 70
小徐 80
小伟 80
小涛 90
小梦 90
小吴 85
小张 95
sqlite> select name as 姓名 from stu;
姓名
--
小宁
小雷
小徐
小伟
小涛
小梦
小吴
小张
sqlite> select stu.name,stu.score from stu;
name score
---- -----
小宁 60
小雷 70
小徐 80
小伟 80
小涛 90
小梦 90
小吴 85
小张 95
sqlite> select s.name from stu as s;
name
----
小宁
小雷
小徐
小伟
小涛
小梦
小吴
小张
sqlite> select distinct score from stu;
score
-----
60
70
80
90
85
95
条件查询
㈠比较运算符:>, <, >=, <=, =, !=, <>
sqlite> select * from stu where score > 80;
id name class score
-- ---- ----- -----
5 小涛 3 90
6 小梦 4 90
7 小吴 1 85
8 小张 2 95
sqlite>
sqlite> select * from stu where score = 80;
id name class score
-- ---- ----- -----
3 小徐 1 80
4 小伟 2 80
㈡逻辑运算符:and, or, not
sqlite> select * from stu where class >1 and score >80;
id name class score
-- ---- ----- -----
5 小涛 3 90
6 小梦 4 90
8 小张 2 95
sqlite>
sqlite> select * from stu where class >1 or score >80;
id name class score
-- ---- ----- -----
1 小宁 3 60
2 小雷 5 70
4 小伟 2 80
5 小涛 3 90
6 小梦 4 90
7 小吴 1 85
8 小张 2 95
sqlite>
sqlite> select * from stu where not (class >1 and score >80);
id name class score
-- ---- ----- -----
1 小宁 3 60
2 小雷 5 70
3 小徐 1 80
4 小伟 2 80
7 小吴 1 85
㈢模糊查询:like, GLOB
like: 百分号(%)代表零个、一个或多个数字或字符。下划线(_)代表一个单一的数字或字符。
glob: 星号(*)代表零个、一个或多个数字或字符。问号(?)代表一个单一的数字或字符。
like不区分大小写, glob区分大小写
sqlite> select * from stu where name like "宁";
sqlite>
sqlite> select * from stu where name like "%宁"; # 查询以宁结尾的名字
id name class score
-- ---- ----- -----
1 小宁 3 60
sqlite>
sqlite> select * from stu where name like "宁%"; # 查询以宁开头的名字
sqlite>
sqlite>
sqlite> select * from stu where name like "%宁%"; # 查询包含宁的名字
id name class score
-- ---- ----- -----
1 小宁 3 60
sqlite>
sqlite>
sqlite> select * from stu where name like "_"; # 名字是1个字的
sqlite>
sqlite> select * from stu where name like "__"; # 名字是2个字的
id name class score
-- ---- ----- -----
1 小宁 3 60
2 小雷 5 70
3 小徐 1 80
4 小伟 2 80
5 小涛 3 90
6 小梦 4 90
7 小吴 1 85
8 小张 2 95
sqlite>
sqlite>
sqlite> select * from stu where name like "__%"; # 名字是2个字以上的
id name class score
-- ---- ----- -----
1 小宁 3 60
2 小雷 5 70
3 小徐 1 80
4 小伟 2 80
5 小涛 3 90
6 小梦 4 90
7 小吴 1 85
8 小张 2 95
sqlite> select * from stu where name GLOB "*宁";
id name class score
-- ---- ----- -----
1 小宁 3 60
sqlite>
sqlite>
sqlite> select * from stu where name GLOB "??";
id name class score
-- ---- ----- -----
1 小宁 3 60
2 小雷 5 70
3 小徐 1 80
4 小伟 2 80
5 小涛 3 90
6 小梦 4 90
7 小吴 1 85
8 小张 2 95
sqlite> select * from stu where name like "aa";
id name class score
-- ---- ----- -----
9 AA 2 90
10 aa 3 90
sqlite>
sqlite> select * from stu where name glob "aa";
id name class score
-- ---- ----- -----
10 aa 3 90
㈣范围查询:in,not in,between…and,not between…and
sqlite> select * from stu where score in (70,80,90); # in 表示在一个非连续的范围内
id name class score
-- ---- ----- -----
2 小雷 5 70
3 小徐 1 80
4 小伟 2 80
5 小涛 3 90
6 小梦 4 90
9 AA 2 90
10 aa 3 90
sqlite>
sqlite> select * from stu where score not in (70,80,90); # 不在该非连续范围内
id name class score
-- ---- ----- -----
1 小宁 3 60
7 小吴 1 85
8 小张 2 95
sqlite>
sqlite>
sqlite> select * from stu where score between 80 and 100; # 在一个连续的范围内
id name class score
-- ---- ----- -----
3 小徐 1 80
4 小伟 2 80
5 小涛 3 90
6 小梦 4 90
7 小吴 1 85
8 小张 2 95
9 AA 2 90
10 aa 3 90
sqlite>
sqlite>
sqlite> select * from stu where score not between 80 and 100; # 不在一个连续的范围内
id name class score
-- ---- ----- -----
1 小宁 3 60
2 小雷 5 70
空判断
select * from table(表名) where trim(字段)='';
is null 不起作用
sqlite> select * from stu where score is not null;
id name class score
-- ---- ----- -----
1 小宁 3 60
2 小雷 5 70
3 小徐 1 80
4 小伟 2 80
5 小涛 3 90
6 小梦 4 90
7 小吴 1 85
8 小张 2 95
9 AA 2
10 aa 3
sqlite>
sqlite> select * from stu where trim(score)='';
id name class score
-- ---- ----- -----
9 AA 2
10 aa 3
sqlite>
sqlite>
sqlite> select * from stu where trim(score)!='';
id name class score
-- ---- ----- -----
1 小宁 3 60
2 小雷 5 70
3 小徐 1 80
4 小伟 2 80
5 小涛 3 90
6 小梦 4 90
7 小吴 1 85
8 小张 2 95
排序:order_by
asc:从小到大排列,即为升序
desc:从大到小排列,即为降序
sqlite> select * from stu order by score;
id name class score
-- ---- ----- -----
1 小宁 3 60
2 小雷 5 70
3 小徐 1 80
4 小伟 2 80
7 小吴 1 85
5 小涛 3 90
6 小梦 4 90
8 小张 2 95
9 AA 2
10 aa 3
sqlite>
sqlite> select * from stu order by score asc;
id name class score
-- ---- ----- -----
1 小宁 3 60
2 小雷 5 70
3 小徐 1 80
4 小伟 2 80
7 小吴 1 85
5 小涛 3 90
6 小梦 4 90
8 小张 2 95
9 AA 2
10 aa 3
sqlite>
sqlite>
sqlite> select * from stu order by score desc;
id name class score
-- ---- ----- -----
9 AA 2
10 aa 3
8 小张 2 95
5 小涛 3 90
6 小梦 4 90
7 小吴 1 85
3 小徐 1 80
4 小伟 2 80
2 小雷 5 70
1 小宁 3 60
sqlite>
sqlite>
sqlite>
sqlite> select * from stu order by score desc, class asc;
id name class score
-- ---- ----- -----
9 AA 2
10 aa 3
8 小张 2 95
5 小涛 3 90
6 小梦 4 90
7 小吴 1 85
3 小徐 1 80
4 小伟 2 80
2 小雷 5 70
1 小宁 3 60
sqlite>
sqlite>
sqlite>
sqlite>
sqlite> select * from stu order by score desc, class desc;
id name class score
-- ---- ----- -----
10 aa 3
9 AA 2
8 小张 2 95
6 小梦 4 90
5 小涛 3 90
7 小吴 1 85
4 小伟 2 80
3 小徐 1 80
2 小雷 5 70
1 小宁 3 60
聚合函数:count(), max(), min(), sum(), avg(), round(),random(),abs(),length()
sqlite> select count(*) from stu;
count(*)
--------
10
sqlite>
sqlite> select max(score) from stu;
max(score)
----------
95
sqlite>
sqlite> select min(score) from stu;
min(score)
----------
60
sqlite>
sqlite> select avg(score) from stu;
avg(score)
----------
81.25
sqlite>
sqlite>
sqlite> select sum(score) from stu;
sum(score)
----------
650
sqlite> select random() as r;
r
-------------------
-852070754439209131
sqlite>
sqlite>
sqlite> select abs(11), abs(-11), abs(null),abs(''),abs("abc"),abs("小美");
abs(11) abs(-11) abs(null) abs('') abs("abc") abs("小美")
------- -------- --------- ------- ---------- ---------
11 11 0.0 0.0 0.0
sqlite>
sqlite> select name,length(name) from stu;
name length(name)
---- ------------
小宁 2
小雷 2
小徐 2
小伟 2
小涛 2
小梦 2
小吴 2
小张 2
分组:group_by, group_concat():查询内容, having
sqlite> select class, count(*) from stu group by class; # 按照班级分组,展示班级号和每个班总人数
class count(*)
----- --------
1 2
2 2
3 2
4 1
5 1
sqlite> select class, group_concat(name) from stu group by class; # 按照班级分组,展示班级号和每个班级的学生名字
class group_concat(name)
----- ------------------
1 小徐,小吴
2 小伟,小张
3 小宁,小涛
4 小梦
5 小雷
sqlite>
sqlite> select class, group_concat(name),score from stu where score>=80 group by class ;# group by 和where条件一起使用,where首先对整个数据表的数据进行判断筛选后,再进行group by 操作
class group_concat(name) score
----- ------------------ -----
1 小徐,小吴 80
2 小伟,小张 80
3 小涛 90
4 小梦 90
sqlite>
sqlite> select class, group_concat(name),avg(score) from stu group by class having avg(score) > 80; # having 对于分组后的数据进行判断筛选
class group_concat(name) avg(score)
----- ------------------ ----------
1 小徐,小吴 82.5
2 小伟,小张 87.5
4 小梦 90.0
分页: limit
limit:开始索引,条数 (索引从0开始)
sqlite> select * from stu limit 1;
id name class score
-- ---- ----- -----
1 小宁 3 60
sqlite>
sqlite>
sqlite> select * from stu limit 1,4;
id name class score
-- ---- ----- -----
2 小雷 5 70
3 小徐 1 80
4 小伟 2 80
5 小涛 3 90
sqlite>
sqlite> select * from stu limit 0,4; # limit在使用的时候,需要放在sql语句最后面
id name class score
-- ---- ----- -----
1 小宁 3 60
2 小雷 5 70
3 小徐 1 80
4 小伟 2 80
连接查询 :inner join, left join, right join
待补充完善-------------