sqlite基础

Posted on

sqlite 命令

DDL 数据定义语言

  1. create 创建一个表,一个表的视图, 或数据库中的其他对象
  2. alter 修改数据库中的某个已有的数据库对象, 比如一个表
  3. drop 删除表或视图, 或其他对象

DML 数据操作语言

  1. insert 创建一条记录
  2. update 修改记录
  3. delete 删除记录

DQL 数据查询语言

select 检索记录

点命令

  1. .help 查看帮助
  2. .backup 可以备份
  3. .tables 查看数据库里面的表
  4. .schema tab_name : 查看表结构
  5. …其他

sqlite语法

  1. 不区分大小写, 但有些命令大小写敏感,如GLOB 和glob;
  2. 注释: – 注释 或 / 注释 /

sqlite 数据类型

存储类型

  1. NULL : NULL值;
  2. integer : 带符号的整数, 根据大小存储在1,2,3,4,6或8字节中;
  3. real : 8字节的IEEE浮点数字;
  4. text : 文本字符串;
  5. blob : 根据输入存储;

亲和类型

当数据插入时, 该字段的数据会优先采用亲缘类型作为该值的存储方式

  1. text : 数值型数据在被插入前, 需要先被转换为文本格式, 之后再插入到目标字段中;
  2. numeric : 当文本数据被插入到到此字段时, 如果转换操作不会导致数据信息丢失以及完全可逆, 则转换为real
    或integer类型的数据, 如果失败,则以text存储;如'3000.00'的浮点数, 也会存储未real或integer;
  3. integer : 同上, 差别在于转换操作;
  4. real : 同numeric, 差别在于不会转换’3000.00’的形式;
  5. none : 不做转换;

亲和类型及类型名称

  1. int,integer,tinyint,smallint,mediumint,bigint,unsigned big int, int2, int8 : integer;
  2. character(20), varchar(255), varying, character(255), nchar(55), native character(70),
    nvarchar(100), text, clob: text;
  3. blob, no datatype specified: none;
  4. real, double, double precision, float: real;
  5. numeric, decimal(10,5), boolean(0, 1) date, datetime: numeric;

sqlite 使用

创建表

1
2
3
4
5
6
7
8
-- 例子
create table company(
id int primary key not null,
name text not null,
age int not null,
address char(50),
salary real
);

删除表

1
drop table name;

插入数据

  1. insert into table_name (column1, column2, column3, …) values(value1, value2, value3, …)
  2. insert into table_name values (value1, value2,value3, ..) : 为所有列添加值,且顺序按定义的来
1
2
3
4
5
6
7
8
9
10
11
12
-- 第一种插入
-- 对于非空项必须插入
insert into company (id, name, age, address, salary) values(1, 'paul', 32, 'california', 20000.0);
insert into company (id, name, age, address, salary) values(2, 'allen', 22, 'texas', 20000.0);
insert into company (id, name, age, address, salary) values(3, 'teddy', 42, 'norway', 15000.0);
insert into company (id, name, age, address, salary) values(4, 'mark', 38, 'rich-mond', 65000.0);
insert into company (id, name, age, address, salary) values(5, 'david', 42, 'texas', 85000.0);
insert into company (id, name, age, address, salary) values(6, 'kim', 42, 'south-hall', 40000.0);
insert into company (id, name, age) values(8, 'wade', 33)

-- 第二种插入
insert into company values (7, 'james', 33, 'houston', 10000.0);

select 语句, 显示表中数据

查看表中信息

  1. 有选择的查看: select column1, column2, columnN from table_name;
  2. 查看所有数据: select * from table_name;
  3. 为友好的查看数据, 可以使用:
    • .header on
    • .mode column
    • .width 10,20,10

查看数据库的表

1
2
3
4
5
-- 查看数据库中创建的表
select tbl_name from sqlite_master where type='table';

-- 列出表中的完整信息
select sql from sqlite_master where type='table' and tbl_name='company';

算术运算符

+ - * / %

比较运算符

  1. == = != <> : 是否相等或不等的;
  2. > < >= <= !< !> :大小比较;

逻辑运算符

  1. AND : 多个条件并存;
  2. BETWEEN : 给定一个范围;
  3. EXISTS : 在满足一定条件的指定表中搜索行的存在;
  4. IN : 指定值与一系列指定列表的值进行比较;
  5. NOT IN : 与IN相反;
  6. LIKE : 用于把某个值与使用通配符运算符的近似值进行比较;
  7. GLOB : 与LIKE类似,但是大小写敏感;
  8. NOT : 逻辑运算符的对立面, 如NOT EXISTS, NOT BETWEEN, NOT IN等;
  9. OR : 或;
  10. IS NULL : 与NULL值进行比较的结果;
  11. IS : 与 = 运算符类似;
  12. IS NOT : 与 != 类似;
  13. || : 连接俩个不同的字符串, 得到一个新的字符串;

位运算符

& | ~ >> << : 与c语言中类似

1
2
3
4
5
6
7
8
select * from company where age >= 25 AND salary >= 65000;
select * from company where age >= 25 OR salary >= 65000;
select * from company where salary IS NOT NULL;
select * from company where name LIKE 'ki%';
select * from company where name LIKE 'ki*';
select * from company where age IN(32, 40);
select * from company where age NOT IN(32, 40);
select * from company where age BETWEEN 25 AND 27;

sqlite 表达式

1
2
3
4
5
6
7
-- 布尔表达式
select * from company where salary=10000;
-- 数值表达式
select (3+5) as addition;
select count(*) as "records" from company;
-- 日期表达式
select current_timestamp;

update语句

用于修改表中的已有的记录, 可以使用带有where子句的update查询来更新选定行, 否则都会更新

1
2
3
4
-- 更新一条记录
update company set address='zhengning' where id=6;
-- 更新所有记录
update company set address='gansu', salary=33333;

delete语句

用于删除表中已有的记录, 可以使用带有where子句的delete查询来删除记录, 否则会全部删除

1
2
3
4
-- 删除一条记录
delete from company where id=6;
-- 删除所有记录
delete from company;

like 子句

用来匹配通配符指定模式的文本值. 如果搜索表达式与模式表达式匹配,返回真; 通配符如下:

  • 百分号(%) : 代表0个, 1个或多个数字或字符;
  • 下划线(_) : 代表一个单一的数字或字符;
1
2
3
4
5
6
7
-- 通用式
select * from table_name where column like ['xxxx%' or '%xxxx%' or 'xxxx_' or '_xxxx' or '_xxxx_']
-- 一些例子
-- 查找以200开头的任意值
select * from company where salary like '200%'
-- 查找第二位为2, 且以3结尾的任意值
select * from company where salary like '_2%3'

glob 子句

用来匹配通配符指定模式的文本值. 与like不同的是, GLOB 是大小写敏感的; 通配符如下:

  • 星号(*) : 代表0个, 1个或多个字符或数字;
  • 问好(?) : 一个单一的数字或字符;
  • 例子同上;

limit 子句

基本语法

1
2
3
4
-- 基本的
select column1, column2, columnN from tab_name limit [no of rows];
-- 返回从下一行开始直到给定的offset为止的所有行
select column1, column2, columnN from tab_name limit [no of rows] offset [row num];

实例

1
2
select * from company limit 6;
select * from company limit 3 offset 2;

order by 子句

select column-list from table_name [where condition] [order by column1, column2, ] [asc(升)|desc(降)]

实例:

1
2
3
4
5
6
-- 按薪水升序排列
select * from company order by salary asc;
-- 按薪水降序排列
select * from company order by salary desc;
-- name, 薪水
select * from company order by name, salary asc;

group by 子句

select column-list from table where [conditions] group by column1,columnN order by column1,columnN

实例:

1
2
3
4
5
6
7
-- 如果想了解客户的工资总额
select into company values(9, 'teddy', 42, 'ggg', 90000);
-- 添加一条记录就可以看的更透彻
select * name,sum(salary) from company group by name;

-- 与order by一起使用
select name,sum(salary) from company group by name order by name desc;

having 子句

1
2
允许指定条件来过滤将出现在最终结果中的分组结果. where子句在所选列上设置条件, 而having子句则在由
group by子句创建的分组上设置条件.

语法

在select中的位置: select from where group by having order by, 必须在group by之后, order by之前.

实例

1
2
3
4
-- 显示名称计数小于2的所有记录
select * from company group by name having count(name)<2;
insert into company values(10, 'wade', 34, 'waiami', 33333);
-- 添加上条记录后再观察效果

distinct 子句

1
2
3
distinct关键字和select一起使用, 来消除所有重复的记录, 并只获取唯一一次记录. 有可能出现一种情况,
在一个表中有多个重复的记录. 当提取这样的记录时, distinct关键字就显得有意义, 它只获取唯一一次记录,
而不是重复获取.

语法

select distinct column1,column2,columnN from table_name where [condition]

例子

1
2
3
4
-- 返回的记录中有重复的
select name from company;
-- 返回不重复的记录
select distinct name from company;