Sqlite数据库-使用的查询语句大全

妖狐艹你老母 2022-12-04 01:02 395阅读 0赞

## 常规查询 ##

1.  查询所有字段：`select * from 表名;`
2.  查询指定字段：`select 列1,列2,... from 表名;`
3.  使用 as 给字段起别名： `select 字段 as 名字.... from 表名;`
4.  查询某个表的某个字段：`select 表名.字段 .... from 表名；`
5.  可以通过 as 给表起别名： `select 别名.字段 .... from 表名 as 别名;`
6.  消除重复行： `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 ##

待补充完善-------------  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d5bWFpc3ls_size_16_color_FFFFFF_t_70]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d5bWFpc3ls_size_16_color_FFFFFF_t_70]: /images/20221123/5655f77081bf47da9f05953f26322004.png