当前位置：首页 > news >正文

sqlite

news 2025/5/24 9:31:58

对已有表添加新的列:

PRAGMA foreign_keys = 0;CREATE TABLE sqlitestudio_temp_table AS SELECT *FROM Students;DROP TABLE Students;CREATE TABLE Students (Id    INTEGER PRIMARY KEY AUTOINCREMENTNOT NULL,Name  STRING  NOT NULL,Age   INTEGER NOT NULL,Addr  STRING,Grade INTEGER NOT NULL
);INSERT INTO Students (Id,Name,Age,Addr)SELECT Id,Name,Age,AddrFROM sqlitestudio_temp_table;DROP TABLE sqlitestudio_temp_table;PRAGMA foreign_keys = 1;

使用group by 分组查询

select Age,Max(Grade),MIN(Grade),AVG(Grade),SUM(Grade),COUNT(Grade)/COUNT(*) from Students  group by Age

以Age 为分组(相同的Age为一组) 显示每组中最高的分数和对应的年纪

select COUNT(*) from Students

排序

select* from 表名 order by 列名 ACS/ DESC(降序) ，列名

高级数据过滤

1、名字以T 开头

select * from 表名 where name like 'T%'

2、名字以ke结尾

select* from 表名 where name like '%T'

3、名字中包含的

select * from 表名 where name like '%T%'

多值检测

Select age,name from Students where age = 1 or age = 18 or age = 23

Select age,name from Students where age in(1,18,23)

NULL 值检测

select 1, 2,3

select 1,2,3,4,6=6,null++1,null+null 与null 作任何运算都是null

限制查询结果

insert into Students (Name,Age,addr,grade) select Name,age,addr,grade from Students

将查询结果作为值插入表中

select * from Students order by Id limit 3 offset 2 从第二行开始（0开始）取最多三条数据

select distinct Age from Students order by age limit 3

数据库表之间的关系 (外键)

Join 语句

select * from T_Authors a join T_books b where a.Id == b.authorid

select * from T_Authors a join T_books b on a.Id == b.authorid

select b.id as bookId from T_Authors a join T_books b on a.Id == b.authorid

表间多对多关系