sqlite学习笔记

发表于

本文以Ubuntu中的sqlite为例

安装sqlite

sudo apt-get install sqlite

创建数据库

sqlite3 testDB.db

创建表

1
2
3
4
5
6
7
sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

查看数据库中已有的表

sqlite>.tables

查看表的结构

SELECT * FROM COMPANY;

删除表

DROP TABLE COMPANY;

创建记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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 );

或者
INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );

显示表

sqlite> SELECT * FROM COMPANY;

优化显示格式

1
2
sqlite>.header yes
sqlite>.mode column

选择不展示重复结果

SELECT DISTINCT ADDRESS FROM COMPANY;

选择前几行数据

SELECT * FROM COMPANY LIMIT 10
SELECT * FROM COMPANY LIMIT 1, 5 – 输出2-6行

排序输出

SELECT * FROM COMPANY ORDER BY AGE;

多列的排序

SELECT * FROM COMPANY ORDER BY AGE,SALARY;

降序排序

SELECT * FROM COMPANY ORDER BY AGE DESC, SALARY;

使用WHERE指定搜索条件

SELECT * FROM COMPANY WHERE AGE=25;
SELECT * FROM COMPANY WHERE AGE>25;
SELECT * FROM COMPANY WHERE AGE BETWEEN 20 AND 30;
SELECT * FROM COMPANY WHERE AGE >25 AND SALARY>30000;
SELECT * FROM COMPANY WHERE AGE IN (25,32);

使用通配符进行过滤

SELECT * FROM COMPANY WHERE NAME LIKE ('Paul%'); – 以Paul开头的
SELECT * FROM COMPANY WHERE NAME LIKE ('%a%'); – 包含a的
SELECT * FROM COMPANY WHERE NAME LIKE ('Pa_l'); – _匹配单个字符

拼接字段

SELECT NAME || '(' || ADDRESS || ')' AS ALAS FROM COMPANY ORDER BY NAME; – 别名alas

分组数据

SELECT ADDRESS, COUNT(*) AS num_address FROM COMPANY GROUP BY ADDRESS;
SELECT AGE, COUNT(*) AS NUM_AGE FROM COMPANY GROUP BY AGE HAVING NUM_AGE>=2; – HAVING 用于过滤分组

子查询

SELECT ID,NAME,ADDRESS FROM COMPANY WHERE ID IN (SELECT ID FROM DEPARTMENT WHERE DEPT='IT'); – 子查询语句总是由内向外执行，并只能查单列

SELECT ID,NAME,(SELECT COUNT(*) FROM DEPARTMENT WHERE(COMPANY.ID=DEPARTMENT.ID)) AS NUMS FROM COMPANY;

联结表

SELECT NAME,ADDRESS FROM COMPANY,DEPARTMENT WHERE(COMPANY.ID=DEPARTMENT.ID); – WHERE 用于联结

更新数据

UPDATE DEPARTMENT SET DEPT='XX' WHERE ID=2;

相关文章