操作数据表的命令
概述
在这篇学习笔记中，主要是学习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列的默认数据为空，但是我们又指定了其不能为空，所以造成了冲突。因此我们在插入新的列的时候，如果新列的值不能为空，则需要向其设置一个默认值，解决冲突。