三、对SQLite数据库的一些常用的语法,和SQL SERVER 的语法一致的,只是在cmd下运行
1、创建表
语法:
CREATE TABLE database_name.table_name( column1 datatype PRIMARY KEY(one or more columns), column2 datatype, column3 datatype, ..... columnN datatype, );
案例:
可以使用 SQLIte 命令中的 .tables 命令来验证表是否已成功创建,该命令用于列出附加数据库中的所有表
2、删除表
语法:
DROP TABLE database_name.table_name;
案例:
注意:一定到打上分号(;)不然系统认为你语句还没结束
3、INSERT INTO 添加
语法:
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)] VALUES (value1, value2, value3,...valueN);
案例:
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'David', 27, 'Texas', 85000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
第二种语法在 COMPANY 表中创建一个记录,如下所示:
INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );
4、查询语句:
语法:
SELECT column1, column2, columnN FROM table_name;
语法二:
SELECT * FROM table_name;
案例:前两个命令被用来设置正确格式化的输出的语法
.header on .mode column SELECT * FROM COMPANY;
5、修改语句:
语法:
UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];
案例,修改上的第六条信息的ADDRESS
UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;
6、删除语句:
语法:
DELETE FROM table_name WHERE [condition];
案例:删除上面的地七条数据:
DELETE FROM COMPANY WHERE ID = 7;
7、模糊查询
语法:
SELECT column_list FROM table_name WHERE column LIKE 'XXXX%'
SELECT column_list FROM table_name WHERE column LIKE 'XXXX_'
案例:
SELECT * FROM COMPANY WHERE AGE LIKE '2%';
SELECT * FROM COMPANY WHERE AGE LIKE '2_';
注意:百分号(%)代表零个、一个或多个数字或字符。下划线(_)代表一个单一的数字或字符。这些符号可以被组合使用。
8、glob字句
语法:
SELECT FROM table_name
WHERE column GLOB 'XXXX*'
SELECT FROM table_name
WHERE column GLOB '?XXXX?'
案例:
SELECT * FROM COMPANY WHERE AGE GLOB '2*';
SELECT * FROM COMPANY WHERE ADDRESS GLOB '*-*'
注意:星号(*)代表零个、一个或多个数字或字符。问号(?)代表一个单一的数字或字符。这些符号可以被组合使用。
9、Limit字句
语法:
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]
LIMIT 子句与 OFFSET 子句一起使用时的语法:
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]
案例:
SELECT * FROM COMPANY LIMIT 6;
SELECT * FROM COMPANY LIMIT 3 OFFSET 2;
10、Oreder by 语句
语法:
SELECT column-list
FROM table_name [WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
案例:
SELECT * FROM COMPANY ORDER BY SALARY ASC;
SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;
SELECT * FROM COMPANY ORDER BY NAME DESC;
11、Group by 语句
语法:
SELECT column-list
FROM table_name WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN
案例:
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;
ORDER BY 子句与 GROUP BY 子句一起使用,如下所示:
SELECT NAME, SUM(SALARY)
FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;
12、Having 语句
语法:
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
案例:
SELECT * FROM COMPANY
GROUP BY name
HAVING count(name) < 2;
注意:HAVING 子句必须放在 GROUP BY 子句之后,必须放在 ORDER BY 子句之前
13、Distinct 关键字
语法:用于消除重复记录的 DISTINCT 关键字
SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]
案例:
SELECT DISTINCT name FROM COMPANY;
参考资料:http://www.runoob.com/sqlite/sqlite-tutorial.html