SQLite学习--操作数据表的命令

这是我参与11月更文挑战的第3天，活动详情查看：2021最后一次更文挑战

概述

经过上一部分的学习，我们已经能够简单操作SQLite数据库了，在这篇学习笔记中，主要是学习SQL中操作数据表的命令。

首先创建一个新的数据库和对应的数据表:

这是一个有关食物的数据库，其中包括以下数据表:

episodes

sqlite3-> create table episodes (
   ...> id integer primary key,
   ...> season int,
   ...> name text);

foods

sqlite3-> create table foods (
   ...> id integer primary key,
   ...> type_id integer,
   ...> name text);
sqlite3-> .tables
episodes  foods

food_types

sqlite3-> create table food_types(
   ...> id integer primary key,
   ...> name text);
sqlite3-> .tables
episodes    food_types  foods

foods_episodes

sqlite3-> create table foods_episodes(
   ...> food_id integer,      
   ...> episode_id integer);
sqlite3-> .tables
episodes        food_types      foods           foods_episodes

上面这几个表中主要的表是foods表，其中每一行都对应一种不同的食物，名字存储在name字段中。type_id则是引用的food_types表，用来存储各种食物的分类。最后，foods_episodes表将foods表中的food和episodes表的episodes连接起来。

语法

SQL的声明式语法读起来就像自然语言，以命令模式表达语句，以描述行为的动词开始，如下是一条简单的查询语句，其中包含了主语和谓语:

select id from foods where name = 'apple';

其中，select是动词，表示我们要执行的动作，id from foods是主语，表示我们从哪里取值，取哪些值；where name ='apple'是谓语，表示我们取的这些值应该满足什么样的条件。

命令

SQL由命令组成，每条命令以分号;结束。下面是3条独立的命令:

select id, name from foods;
insert into foods values(null,'Whataburger');
delete from foods where id = 413;

反过来，命令是由一系列记号组成的，这些记号可以是常量，关键字，标识符，表达式或者特殊字符串。

常量

常量也称为constants,表示确切的值。它包含3种类型：[字符串常量],[数字常量]和[二进制常量]。

字符串常量由单引号引起来的一个或多个字母或数字字符组成，例如：

'Jerry','Newman','JujyFruit'

虽然也可以使用双引号将常量引起来，但是仍然建议使用单引号来做，因为这是SQL的标准。

另外，如果字符串中本身就已经包含了单引号，则需要连续使用两个单引号，例如Kenny's chicken需要写成:

Kenny''s chicken
数字常量有整数，十进制数和科学计数法表示的数，下面是一些例子:
```
-1,
3.14,
6.00342423E23
```
二进制值则使用x'0000'的表示方法，其中每位是一个16进制数。二进制值必须由两个16进制数的整数倍(8bits)组成，下面是一些例子:
```
x'01'
X'0fff'
x'0F0EFF'
X'0f0effeb'
```

关键字和标识符

关键字是SQL中有指定含义的单词，这些单词包括select,update,insert,create,drop和begin等。

标识符是指数据库里的具体对象，如表或索引。

关键字是保留的单词，不能用作标识符。

另外，SQL不区分关键字和标识符的大小写，例如，下面是相同的语句:

SELECT * FROM foo;
Select * frOm Foo;

但是需要注意的是：SQL中默认的字符常量值是大小写敏感的。因此，'Mike'和'mike'是不同的。

注释

SQL中的单行注释是用两个连续的连字符--表示的。多行注释使用的是C语言风格的/**/形式，例如:

sqlite> .headers on
sqlite> .mode column
sqlite> select *
   ...> --食物表
   ...> from foods;
id          type_id     name      
----------  ----------  ----------
1           1           apple

多行注释如下:

sqlite> select *
   ...> /*
   ...> *食物表中id为1的食物
   ...> */
   ...> from foods where id = 1;
id          type_id     name      
----------  ----------  ----------
1           1           apple

需要注意的是：如果不是非常有必要，建议使用--作为SQL的注释使用。

创建表

像关系模型一样，SQL是由几个部分组成的，它有结构化部分，例如：用来创建和销毁数据库对象的部分，这一部分语言通常称为数据库定义语言(DDL)。类似地，还有一些在这些对象上执行操作的功能部分，例如获取和控制数据，这一部分语言称为数据库操作语言(DML)。创建表的语句属于DDL部分，可以通过create table命令创建数据表，create table命令的一般结构为:

create [temp] table table_name(column_definitions [,constraints]);

上面的命令中用temp或者temporary关键字声明的表是临时表，所谓临时表，是指只存活于当前会话，一旦连接断开，就会被自动销毁(如果没有手动销毁的话)。temp旁边的方括号[]表示该项是可选项，另外，管道符号|则表示两者选其一，是OR 或的意思，例如下面的语法:
```
create [temp|temporary] table ...;
```
上面的命令意味着temp或者temporary是可选的，可以写成create temp table table_name...，也可以写成create temporary table table_name...,这两个语句实质上是相同的。
如果没有明确地指出创建临时表，则创建的是基本表，将会在数据库中持久存在。这也是数据库中最常见的表。一般而言，基本表用来区分由create table创建的表与系统表，以及其它类似表的结构，例如视图。

create table命令至少需要一个表名和一个字段名，命令中的table_name表示表名，表名必须与其它所有的标识符不同。

column_definations由用逗号分隔的字段列表组成，每个字段定义包括一个名称，一个域和一个逗号分隔的字段约束。类型有时候也称为域，它们都是指存储在该列的信息的类型。

SQLite中有5种本地类型:integer,real,text,blob和null。

约束constraints用来控制什么样的值可以存储在表中或特定的字段中。例如：可以使用unique约束来规定所有的记录中某个字段的值要各不相同。

create table命令允许在字段列表后面跟随一个附加的字段约束，如下所示:

create table contacts(
   id integer primary key,
   name text not null collate nocase,
   phone text not null default 'UNKNOW',
   unique(name,phone)
);

上面的命令创建了一个contacts表，字段id声明为integer类型，限制为主键。这种组合在SQLite中有特殊含义，整型主键基本上表示该字段是自增长字段。
字段name则声明为text类型，约束不能为空，并且排序不区分大小写。
字段phone也是text类型，也有两条约束，不能为空，默认为UNKNOW。
之后，表一级的约束是unique,定义在字段name和phone上。

下面的操作演示了上面的总结:

-- 创建contacts表
   sqlite> create table contacts(
...> --id
...> id integer primary key,
...> --name
...> name text not null collate nocase,
...> --phone
...> phone text not null default 'UNKNOW',
...> unique(name,phone));

-- 打印contacts表的定义信息
sqlite> .schema contacts
CREATE TABLE contacts(
--id
id integer primary key,
--name
name text not null collate nocase,
--phone
phone text not null default 'UNKNOW',
unique(name,phone));

-- 向其中插入一条数据
sqlite> insert into contacts(name,phone) values ('zyf','13567890987');   
sqlite> select * from contacts;
id          name        phone      
----------  ----------  -----------
1           zyf         13567890987

-- 继续向其中插入一条数据
sqlite> insert into contacts(name) values ('ddd');                  
sqlite> select * from contacts;
id          name        phone      
----------  ----------  -----------
1           zyf         13567890987
2           ddd         UNKNOW     

-- 继续插入数据
sqlite> insert into contacts(name) values ('eee');
sqlite> select * from contacts;
id          name        phone      
----------  ----------  -----------
1           zyf         13567890987
2           ddd         UNKNOW     
3           eee         UNKNOW     

-- 插入一条和第一条数据想用name的数据
sqlite> 
sqlite> insert into contacts(name) values ('zyf');
sqlite> select * from contacts;
id          name        phone      
----------  ----------  -----------
1           zyf         13567890987
2           ddd         UNKNOW     
3           eee         UNKNOW     
4           zyf         UNKNOW  

-- 再次尝试插入和第一条数据相同name的数据的时候便不能插入数据了，因为经过上一步的操作，contacts表中已经存在一条
-- name为zyf，phone为UNKNOW的数据了   
sqlite> 
sqlite> insert into contacts(name) values ('zyf');
Error: UNIQUE constraint failed: contacts.name, contacts.phone
sqlite>

修改表

在已经创建了一个表之后，我们可以通过alter table命令改变表的部分结构。在SQLite中，alter table命令既可以改变表名，也可以增加字段。该命令的一般格式为:

alter table table_name {rename to new_name | add column column_def}

上面的命令中出现了花括号{}，花括号括起来一个选项列表，表示必须从选项中选择一个。对于上面的命令来说，支持我们执行alter table table_name rename to new_name修改表名或者执行alter table table_name add column column_def添加新的列。

如果需要修改表名，只需要提供新的表名即可，如果想要添加新的列，对于新的列的定义，应该和创建表时对于列的定义是一样的，也就是列表约束条件。

修改表名

下面的操作将contacts表的名称修改为了new_contacts，如下代码所示:

--原始数据库中的表
sqlite> .tables
contacts  schema    test      test2

--可以看到其中包含一个contacts表，打印其中的数据
sqlite> select * from contacts；
id          name        phone      
----------  ----------  -----------
1           zyf         13567890987
2           ddd         UNKNOW     
3           eee         UNKNOW     
4           zyf         UNKNOW     
5           ddd         12321321 

--修改表名
sqlite> --修改contacts表名为new_contacts
sqlite> alter table contacts rename to new_contacts;

--重新打印数据库中的表
sqlite> .tables
new_contacts  schema        test          test2


--可以看到,contacts表不见了，取而代之的是new_contacts表，重新打印其中的数据
sqlite> select * from new_contacts;
id          name        phone      
----------  ----------  -----------
1           zyf         13567890987
2           ddd         UNKNOW     
3           eee         UNKNOW     
4           zyf         UNKNOW     
5           ddd         12321321

通过上面打印的信息可以看到：我们成功修改了contacts表的表名为new_contacts，其中的数据也和修改之前一样，没有造成数据的异常。

添加新列

而如果需要添加新的列，则可以使用下面的代码:

sqlite> --向new_contacts表中添加一个名为email的新列
sqlite> alter table new_contacts add column email TEXT NOT NULL DEFAULT '' COLLATE NOCASE;
sqlite> 
sqlite> --查看new_contacts表的定义
sqlite> .schema new_contacts
CREATE TABLE IF NOT EXISTS "new_contacts"(
--id
id integer primary key,
--name
name text not null collate nocase,
--phone
phone text not null default 'UNKNOW', email TEXT NOT NULL DEFAULT '' COLLATE NOCASE,
unique(name,phone));
sqlite>     
sqlite> --查询new_contacts表中的数据
sqlite> select * from new_contacts;
id          name        phone        email     
----------  ----------  -----------  ----------
1           zyf         13567890987            
2           ddd         UNKNOW                 
3           eee         UNKNOW                 
4           zyf         UNKNOW                 
5           ddd         12321321

在上面的代码中，我们首先向new_contacts表中添加了一个名为email的新列，并且约束它不能为空，默认为空字符串，排序时不区分大小写。然后查看表的定义，就能够发现这一列已经被添加到表中了。最后打印数据，发现数据也没有什么异常。

上面的代码中，我们指定email的时候设置其不能为空，并指定了默认值，我们知道，在创建表的时候我们设置一个字段不能为空可以不指定默认值，那如果我们在修改表的时候也不指定默认值呢，下面的代码演示了这种情况:

sqlite> --向new_contacts表中添加一个名为address的列
sqlite> alter table new_contacts
   ...> add column
   ...> --地址 
   ...> address text not null;
Error: Cannot add a NOT NULL column with default value NULL

可以看到，我们尝试向new_contacts表中添加一个名为address的新列，我们指定了其不能为空,但是会报错，报错的原因是address列的默认数据为空，但是我们又指定了其不能为空，所以造成了冲突。因此我们在插入新的列的时候，如果新列的值不能为空，则需要向其设置一个默认值，解决冲突。