sqlite3 SQL常用语句

1. select

   SELECT LastName,FirstName FROM Persons;

   SELECT * FROM Persons;

2. where

   SELECT * FROM Persons WHERE City='Beijing';

   SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter'

   SELECT * FROM Persons WHERE (FirstName='Thomas' OR FirstName='William') AND LastName='Carter'

3. order by

   SELECT Company, OrderNumber FROM Orders ORDER BY Company;//默认升序

   SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC

4. insert

   INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

   INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')；

   INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')；

5. update

   UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

   UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'；

   UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson';

6. delete

   DELETE FROM 表名称 WHERE 列名称 = 值

   DELETE FROM Person WHERE LastName = 'Wilson';

   可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：

   DELETE FROM table_name;

7. limit

   SELECT column_name(s) FROM table_name LIMIT number

8. like

   SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern

   // N开头

   SELECT * FROM Persons WHERE City LIKE 'N%'；

   // g结尾

   SELECT * FROM Persons WHERE City LIKE '%g'；

   // 包含lon

   SELECT * FROM Persons WHERE City LIKE '%lon%'；

   // NOT组合

   SELECT * FROM Persons WHERE City NOT LIKE '%lon%'

   // 第一个字母之后是eorge

   SELECT * FROM Persons WHERE FirstName LIKE '_eorge'；

   // 以 "C" 开头，然后是一个任意字符，然后是 "r"，然后是任意字符，然后是 "er"

   SELECT * FROM Persons WHERE LastName LIKE 'C_r_er'

   // [charlist] 选取居住的城市以 "A" 或 "L" 或 "N" 开头的人

   SELECT * FROM Persons WHERE City LIKE '[ALN]%'；

   // 选取居住的城市不以 "A" 或 "L" 或 "N" 开头的人

   SELECT * FROM Persons WHERE City LIKE '[!ALN]%'；

9. in

   // 选取姓氏为 Adams 和 Carter 的人

   SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')；

10.between

   // 如需以字母顺序显示介于 "Adams"（包括）和 "Carter"（不包括）之间的人，请使用下面的 SQL：

   SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter'；

   // 如需使用上面的例子显示范围之外的人，请使用 NOT 操作符

   SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Adams' AND 'Carter'；

11.join

   // 引用两个表

   SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons, Orders 

   WHERE Persons.Id_P = Orders.Id_P；

   // INNER JOIN

   SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN

   Orders ON Persons.Id_P = Orders.Id_P ORDER BY Persons.LastName；

12.INNER JOIN 关键字语法

   SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON

   table_name1.column_name=table_name2.column_name;

   SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM

   Persons INNER JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName;

13.LEFT JOIN

   // LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有

      匹配的行。

   SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON 

   table_name1.column_name=table_name2.column_name;

14.RIGHT JOIN

   // RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有

      匹配的行。

   SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2  ON

   table_name1.column_name=table_name2.column_name

15.FULL JOIN

   只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。

   SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON 

   table_name1.column_name=table_name2.column_name

   // SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL JOIN

   Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName;