sqlite高级-2

Posted on

sqlite alter 命令

alter table命令不通过执行一个完整的转储和数据的重载来修改已有的表.
alter table语句来重命名表, 还可以在已有的表中添加额外的列.
sqlite中, 除了重命名表和添加列外, 不支持其他操作

语法

1
2
3
4
5
-- 重命名表
alter table datebase_name.table_name rename to new_table_name;

-- 添加新列
alter table datebase_name.table_name add column column_def...;

实例

1
2
3
alter table company rename to old_company;

alter table old_company add column sex char(1);

sqlite truncate table

sqlite 中, 并没有truncate table命令,可以使用sqlte的delete命令删除表中已有的数据.
但是如果删除整个表,建议使用drop table命令.

语法

1
2
3
delete from table_name;

drop table table_name;

实例

1
delete from company;

sqlite 视图(view)

  1. 视图(view)是通过相关的名称存储在数据库中的一个sqlite语句;
  2. 视图(view)是一个以预定义的sqlite查询形式存在的表的组合;
  3. 视图(view)可以包含一个表的所有行或从一个或多个表选定行;
  4. 视图是一种虚表, 允许用户实现以下几点:
    • 用户或用户组查找结构数据的方式更自然或直观
    • 限制数据访问, 用户只能看到有限的数据, 而不是完整的表
    • 汇总各种表中的数据, 用户生成报告
  5. 视图是只读的, 无法进行delete, insert或update操作, 但是可以创建触发器.

创建视图

1
2
3
create [temp | temporary] view view_name as select column1, column2...
from table_name where [condition];
-- 如果使用temp 或temporary, 则将在临时数据库中创建视图.

实例

1
2
3
4
create view company company_view as select id, name, age from company;

-- 查询视图, 和查询表类似
select * from company_view;

删除视图

1
2
3
4
drop view view_name;

-- 实例
drop view company_view;

sqlite 事务(Transaction)

  1. 事务是一个对数据库执行工作单元. 事务是以逻辑顺序完成的工作单位或序列, 可以是由用户手动操作完成,
    也可以是由某种数据库程序自动完成.
  2. 事务是一个或多个更改数据库的扩展. 例如, 如果您正在创建一个记录或更新或删除, 那您正在改表执行事务.
  3. todo

事务的属性

  1. 原子性(atomicity): 确保工作单位内的所有操作都成功完成, 否则, 事务会在出现故障时终止,
    之前的操作也会回滚到之前的状态.
  2. 一致性(consistency): 确保数据库在成功提交的事务上正确的改变状态.
  3. 隔离性(isolation) : 使事务操作相互独立和透明.
  4. 持久性(durability) : 确保已提交事务的结果或效果在系统发生故障的情况下仍然存在.

事务控制

  1. begin 或 begin transaction : 开始处理事务.
  2. commit : 保存修改, 或者可以使用end transaction.
  3. rollback: 撤销未保存到数据库的事务, commit后就无法撤销了.

实例

1
2
3
4
5
6
7
8
9
10
-- 假设有age为25的记录
begin;
delete from company where age=25;
rollback;
-- 查看记录仍然存在

begin;
delete from company where age=25;
commit;
-- 查看已经删除, 并且rollback已经无用

sqlite子查询

  1. 子查询或内部查询或嵌套查询是在另一个sqlite查询内嵌入在where子句中的查询, 使用子查询返回的数据将被
    用于在主查询中作为条件, 以进一步限制要检索的数据.
  2. 子查询与select, insert, update, delete一起使用, 伴随运算符等.
  3. 遵循的规则:
    • 子查询必须用括号括起来
    • 子查询在select子句中只能有一列. 除非在主查询中有多列, 与子查询的所选列进行比较.
    • order by 不能用于子查询中, 虽然主查询可以使用order by; 可以使用group by, 功能与order by相同.
    • 子查询返回多于一行, 只能与多值运算符一起使用, 如IN运算符.
    • between运算符不能与子查询一起使用, 但是, between可在子查询内使用.

select语句中的子查询使用

1
2
3
4
5
6
7
8
9
select column_name [, column_name] from table1 [,table2]
where column_name operator (select column_name[,...] from table1 [,...] [where]);

-- example
select * from company where id in(select id from company where salary>45000);

select * from department where id in(select id from company where salary>45000);

select * from company where id in(select id from department);

insert语句中的子查询使用

1
2
3
4
5
insert into table_name[(column1, column2)] select [* | column1[,column2]]
from table1 [, table2] [where value operator];

-- 假设company_bkp的结构与company表类似
insert into company_bkp select * from company where id in(select id from company);

update语句中的子查询使用

1
2
3
4
5
update table set column_name = new_value
[ where operator [ value ] (select column_name from table_name) [ where) ]

update company set salary = salary * 0.50
where age in (select age from company_bkp where age >= 27 );

delete语句中的子查询使用

1
2
3
4
5
delete from table_name
[ where operator [ value ] (select column_name from table_name) [ where) ]

delete from company
where age in (select age from company_bkp where age > 27 );

sqlite autoincrement(自动递增)

  1. sqlite 的 autoincrement 是一个关键字，用于表中的字段值自动递增。
  2. 可以在创建表时在特定的列名称上使用 autoincrement 关键字实现该字段值的自动增加。
  3. 关键字 AUTOINCREMENT 只能用于整型（INTEGER）字段
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- 语法
create table table_name(
   column1 integer autoincrement,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

-- 实例
 create table company(
   id integer primary key   autoincrement,
   name           text      not null,
   age            int       not null,
   address        char(50),
   salary         real
);
insert into company (name,age,address,salary)
values ( 'paul', 32, 'california', 20000.00 );

insert into company (name,age,address,salary)
values ('allen', 25, 'texas', 15000.00 );

insert into company (name,age,address,salary)
values ('teddy', 23, 'norway', 20000.00 );

insert into company (name,age,address,salary)
values ( 'mark', 25, 'rich-mond ', 65000.00 );

insert into company (name,age,address,salary)
values ( 'david', 27, 'texas', 85000.00 );

insert into company (name,age,address,salary)
values ( 'kim', 22, 'south-hall', 45000.00 );

insert into company (name,age,address,salary)
values ( 'james', 24, 'houston', 10000.00 );

-- 查询:.header on, .mode column
select * from company;

sqlite Explain

TODO:

sqlite vacuum

TODO: