转载 2014年05月10日 10:38:21
转自:http://www.cnblogs.com/myqiao/archive/2011/07/13/2105800.html
一、插入数据 INSERT INTO 表(列...) VALUES(值...)
根据前面几篇的内容,我们可以很轻送的创建一个数据表,并向其中插入一些数据,不多说,看例子:
myqiao@ubuntu:~/My Documents/db$ sqlite3 test.db
-- Loading resources from /home/myqiao/.sqliterc
SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
sqlite>
sqlite> CREATE TABLE Teachers(
...> Id integer PRIMARY KEY,
...> Name text NOT NULL,
...> Age integer CHECK(Age>22),
...> Country text DEFAULT 'USA');
sqlite> .tables
Teachers
sqlite>
sqlite> INSERT INTO Teachers VALUES(1,'Alice',25,'CHN');
sqlite> INSERT INTO Teachers VALUES(2,'Bob',25,'BRA');
sqlite> INSERT INTO Teachers(Id,Name,Age,Country) VALUES(3,'Charls',33,'USA');
sqlite> INSERT INTO Teachers(Name,Age) VALUES('Jhon',43);
sqlite> SELECT * FROM Teachers;
Id Name Age Country
---- --------------- --------------- ---------------
1 Alice 25 CHN
2 Bob 25 BRA
3 Charls 33 USA
4 Jhon 43 USA
sqlite>
很简单,创建了一个 Teachers 表并向其中添加了四条数据,设定了一些约束,其中有自动增加的主键、默认值等等。
二、修改数据 UPDATE 表 SET 列 = '新值' 【WHERE 条件语句】
UPDATE 语句用来更新表中的某个列,如果不设定条件,则所有记录的这一列都被更新; 如果设定了条件,则符合条件的记录的这一列被更新, WHERE 子句被用来设定条件,如下例:
sqlite>
sqlite> SELECT * FROM Teachers;
Id Name Age Country
---- --------------- --------------- ---------------
1 Alice 25 CHN
2 Bob 25 BRA
3 Charls 33 USA
4 Jhon 43 USA
sqlite>
sqlite>
sqlite> UPDATE Teachers SET Country='China';
sqlite> SELECT * FROM Teachers;
Id Name Age Country
---- --------------- --------------- ---------------
1 Alice 25 China
2 Bob 25 China
3 Charls 33 China
4 Jhon 43 China
sqlite>
sqlite>
sqlite> UPDATE Teachers SET Country='America' WHERE Id=3;
sqlite> SELECT * FROM Teachers;
Id Name Age Country
---- --------------- --------------- ---------------
1 Alice 25 China
2 Bob 25 China
3 Charls 33 America
4 Jhon 43 China
sqlite>
sqlite>
sqlite> UPDATE Teachers SET Country='India' WHERE Age<30;
sqlite> SELECT * FROM Teachers;
Id Name Age Country
---- --------------- --------------- ---------------
1 Alice 25 India
2 Bob 25 India
3 Charls 33 America
4 Jhon 43 China
sqlite>
三、删除数据 DELETE FROM 表 【WHERE 条件语句】
如果设定 WHERE 条件子句,则删除符合条件的数据记录;如果没有设定条件语句,则删除所有记录
sqlite>
sqlite> SELECT * FROM Teachers;
Id Name Age Country
---- --------------- --------------- ---------------
1 Alice 25 India
2 Bob 25 India
3 Charls 33 America
4 Jhon 43 China
sqlite>
sqlite>
sqlite> DELETE FROM Teachers WHERE Age>30;
sqlite> SELECT * FROM Teachers;
Id Name Age Country
---- --------------- --------------- ---------------
1 Alice 25 India
2 Bob 25 India
sqlite>
sqlite>
sqlite> DELETE FROM Teachers;
sqlite> SELECT * FROM Teachers;
sqlite>