sqlite高级-1

Posted on

sqlite pragma

以后追加

sqlite 约束

NOT NULL 约束

默认情况下, 列可以保存为NULL值; 如果不想, 则设置为NOT NULL;NULL和没有数据是不一样的, 代表未知的数据,
如下:

1
2
3
4
5
6
7
8
create table employee(
id int primary key not null
name text not null
age int not null
address char(50)
salary real
);
-- id, name, age不接受null值;

default 约束

在insert时没有提供特定的值时, 提供一个默认值, 如下:

1
2
3
4
5
6
7
create table employee(
id int primary key not null
name text not null
age int not null
address char(50)
salary real default 40000
);

unique 约束

防止一个特定的列出现列个记录相同的值, 如下:

1
2
3
4
5
6
7
8
create table employee(
id int primary key not null
name text not null unique
age int not null
address char(50)
salary real
);
-- 防止雇佣相同名称的人

primary key 约束

  1. 约束唯一标识数据库表中的每个记录. 在一个表中可以有多个unique列, 但是只能有一个主键.
  2. 通过主键来引用表中的行. 可将主键设为其他表的外键, 来创建表的关系.
  3. 在sqlite中, 主键可以为NULL, 与其他数据库不同的地方.
  4. 如果一个表在任何字段上定义了一个主键, 那么该字段就不能有俩个记录相同的值;

check 约束

启用输入一条记录要检查值的条件. 如果为false, 则不能创建.

1
2
3
4
5
6
create table company3(
id int primary key not null,
name text not null,
age int not null,
salary real check(salary>0)
);

删除约束

  1. sqlite支持alter table的有限子集.
  2. 在sqlite中, alter table命令允许用户重命名表, 或向现有表中添加一个新的列.
  3. 重命名列, 删除一列, 或从一个表中添加或删除约束列都是不可能的.

sqlite joins

joins子句用于结合俩个或多个数据库中表的记录.
假设我们已经有表company, department, 往department中插入如下记录:

1
2
3
insert into department values(1, 'IT Billing', 1);
insert into department values(2, 'Engineering', 2);
insert into department values(3, 'Finance', 7);

交叉连接

把第一个表的每一行与第二个表的每一行进行匹配. 如果第一表有n行x列, 第二个表有m行y列, 则会产生n*m行
x+y列的结果表, 如下;

select * from company cross join department;

内连接

根据连接条件结合俩个表. 查询会把table1中的每一行与table2中的每一行进行比较, 如果满足连接条件,
就会进行合并. 内连接是默认连接, inner可以省略. 如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select ... from table1 [inner] join table2 on condition_expression
-- 为了避免冗余, 并保持较短的措辞, 可以使用using表达式
select ... from table1 [inner] join table2 using(column1 ...)
-- 自然连接(natural join) 类似于join ... using , 只是它自会自动测试存在俩个表中每一列的值之间相等值.
select ... from table1 natural join table2;

--例子
select * from company inner join department on company.id=department.id;
-- 上面产生的效果类似于如下产生的效果:
select * from company natural join department;
-- using: id 俩个表都有, 所以正确, 连接后id列合并
select * from company inner join department using(id);
-- error: 由于name, dept不是俩个表共有的.
select * from company inner join department using(id, name, dept);
select * from company inner join department using(name, dept);

外连接

  1. 外连接是内连接的扩展, sqlite支持SQL标准left, right, full三种的左外连接(left outer join).
  2. 基本同上
1
2
3
4
5
6
7
8
9
10
11
select ... from table1 left outer join table2 on condition_expression
-- 为了避免冗余, 并保持较短的措辞, 可以使用using表达式
select ... from table1 left outer join table2 using(column1 ...)

--例子
select * from company left outer join department on company.id=department.id;
-- using: id 俩个表都有, 所以正确, 连接后id列合并
select * from company left outer join department using(id);
-- error: 由于name, dept不是俩个表共有的.
select * from company left outer join department using(id, name, dept);
select * from company left outer join department using(name, dept);

sqlite unions子句

UNION子句/运算符用于合并俩个或多个select语句的结果, 不返回任何重复的行.
为了使用UNION, 每个select被选择的列数必须是相同的, 相同数目的列表达式, 相同的数据类型,
并确保他们有相同的顺序, 但他们不必有相同的长度.

1
2
3
4
5
6
7
8
-- 语法
select column1 [,column2] from table1 [,table2] [where condition] union select column1 [,column2]
from table1 [,table2] [where condition]

-- 实例
select emp_id, name, dept from company inner join department on company.id = department.emp_id
union
select emp_id, name, dept from company left outer join department on company.id = department.emp_id;

union all子句

同上, 但是会返回重复的行

1
2
3
4
5
6
7
8
9
-- 语法
select column1 [,column2] from table1 [,table2] [where condition]
union all
select column1 [,column2] from table1 [,table2] [where condition]

-- 实例
select emp_id, name, dept from company inner join department on company.id = department.emp_id
union all
select emp_id, name, dept from company left outer join department on company.id = department.emp_id;

sqlite null 值

sqlite的null是用来表示一个缺失值的项. 表中的一个NULL值是在字段中显示为空白的一个值.
带有null值的字段是一个不带有值的字段. null值和零值或包含空格的字段是不同的.

语法, 实例

参照创建表

is not null运算符

1
select * from company where salary is not null;

sqlite 别名

可以暂时把表或列重命名为另一个名字, 就是别名. 使用表别名是指在一个特定的sqlite语句中重命名表.
重命名是临时的改变, 数据库中实机的表是不会改变的. 列别名同表别名.

语法

1
2
3
4
-- 表别名
select column1, column2, ... from table_name as alias_name where [condition]
-- 列别名
select column1 as alias_name from table_name where [condition]

实例

1
2
3
4
5
6
-- 表别名
select c.id, c.name, c.age, d.dept from company as c, department as d where c.id=d.emp_id;

-- 列别名
select c.id as company_id, c.name as company_name, c.age, d.dept from company as c,
department as d where c.id = d.emp_id;

sqlite 触发器(trigger)

触发器是数据库的回调函数, 它会在指定的数据库事件发生时自动执行或调用. 以下是相关要点:

  1. 指定在特定的数据库表发生delete, insert, 或update时触发, 或在一个或多个指定表的列发生更新是触发;
  2. 只支持for each row触发器, 没有for each statement触发器, 因此for each row是可选的.
  3. when 子句与触发器动作可能访问使用表单new.column-name 和 old.column-name的引用插入,
    删除或更新的行元素, 其中column-name是从与触发器关联的表的列的名称.
  4. 如果提供when子句, 则只针对when子句为真的指定行执行sql语句. 否则, 为所有行执行.
  5. defore 或 after 关键字决定何时执行触发器动作. 决定是在关联行的插入, 修改或删除之前或之后执行.
  6. 当触发器关联的表删除时, 自动删除触发器.
  7. 要修改的表必须存在与同一数据库中, 作为触发器被附加的表或视图, 且必须只使用tabname,
    而不是database.tablename.
  8. 一个特殊的sql函数raise() 用于触发器程序内抛出异常.

语法

1
2
3
4
5
6
7
8
9
10
-- 创建触发器
create trigger trigger_name [before | after] event_name on table_name
begin
-- trigger 逻辑代码
end;

create trigger trigger_name [before | after ] update of column_name on table_name
begin
-- 触发器逻辑代码
end;

实例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 为company表的每一个记录保持审计试验
-- 创建一个audit表
create table audit(emp_id int not null, entry_date text not null);

-- 创建一个触发器
create trigger audit_log after insert on company
begin
insert into audit(emp_id, entry_date) values(new.id, datetime('now'));
end;

-- 往company中插入一条记录
insert into company(id, name, age, address, salary) values(11, 'feng', 28, 6722);

-- 执行上步后, 也会在audit中插入一个记录
select * from audit;

列出触发器和删除触发器

1
2
3
4
-- 列出
select name from sqlite_master where type='trigger';
-- 删除
drop trigger trigger_name

sqlite 索引(index)

  1. 索引是一种特殊的查找表, 数据库搜索引擎用来加快数据检索;
  2. 索引有助于加快select查询和where子句, 但是会减慢update和insert语句时的数据输入;
  3. 索引可以创建和删除, 不会影响源数据;
  4. 使用create index创建索引, 允许命名索引, 指定表及要索引的一列或多列, 并指示索引是升序还是降序;
  5. 索引也可以是唯一的, 与unique约束类似, 在列上或列组合上防止重复条目;

创建索引(create index)

  1. 如果查询一般使用一个列, 则使用单列索引;
  2. 如果使用多列, 则使用组合索引;

单列索引

只基于表的一个列上创建的索引.
create index index_name on table_name(column_name);

唯一索引

唯一索引不允许任何重复的值插入到表中
create unique index index_name on table_name(column_name);

组合索引

基于表的俩个或多个列上创建的索引
create index index_name on table_name(column1, column2);

隐式索引

创建表时, 由数据库服务器自动创建的索引. 索引自动创建为主键约束和唯一约束.

删除索引

drop index index_name

以下情况避免使用索引

  1. 不应该使用在较小的表上;
  2. 不应该使用在有频繁的大批量的更新或插入操作的表上;
  3. 不应该使用在含有大量的null值的列上;
  4. 不应该使用在频繁操作的列上;

实例

1
2
3
4
5
6
7
create index salary_index on company(salary);

-- 查看所有索引
.indices company

-- 列出数据所有的索引
select * from sqlite_master where type='index';

sqlite indexed by

“indexed by index_name” 子句规定必须需要命名的索引来查找前面表中值. 如果不存在或不能用于查询,
语句失败. “not indexed” 子句规定当访问前面的表时, 没有使用索引. 然而, 即使指定了”not indexed”,
integer primary key仍然可以用于查找条目.

语法

1
2
select|delete|update column1, column2 indexed by (index_name)
table_name where (condition);

实例

1
2
3
4
-- 假设有表
create index age_index on company(age);
-- 使用
select * from company indexed by age_index where age>30;