SQLite3简单笔记-03

SQLite语法了解：
SQLite 是不区分大小写的，但GLOB区分大小写。

-- 附加数据库名
ATTACH DATABASE 'database_name' AS 'alias'

-- 去掉数据库附加名称
DETACH DATABASE 'alias'

-- 表中创建去重的索引
CREATE UNIQUE INDEX index_name ON table_name ( column1, column2, column3)

-- 建表
CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype)

-- 建触发器
CREATE TRIGGER  database_name.trigger_name  BEFORE INSERT ON table_name FOR EACH ROW BEGIN stmt1; stmt2; END

-- 创建视图
CREATE VIEW  database_name.view_name AS SELECT statment

-- 创建虚表， sqlite3_create_module（）已使用即已经在sqlite中注册
CREATE VIRTUAL TABLE database_name.table_name USING module_name(module_argument1,module_argument2)

-- count的使用
SELECT COUNT(conlumn_nmae) FROM table_name WHERE codition

-- DELETE使用
DELETE FROM table_name WHERE {codition}

-- 数据去重
SELECT DISTINCT column1, column2 FROM table_name

-- 删除索引
DROP INDEX database_name.index_name

-- 删除表
DROP TABLE database_name.table_name

-- 删除触发器
DROP TRIGGER trigger_name

-- 区别：exists 根据查询结果进行一条一条轮询匹配，in 根据查询结果进行笛卡尔积计算
-- exists
SELECT column1, column2 FROM table_name WHERE column_name EXISTS ( SELECT * FROM table_name)

-- in
SELECT column1, column2 FROM table_name WHERE column_name IN ( SELECT * FROM table_name)

-- not in
SELECT column1, column2 FROM table_name WHERE column_name NOT IN ( SELECT * FROM table_name)

-- explain
EXPLAIN SELECT DISTINCT column1, column2 FROM table_name

-- 通配符 零个或多个 一个
-- 通配符使用 glob * ?
SELECT DISTINCT column1, column2 FROM table_name WHERE column_name GLOB {pattern}

-- 通配符使用 like % _
SELECT column1, column2 FROM table_name WHERE column_name LIKE {pattern}

-- 分组
SELECT SUM( column_name ) FROM table_name WHERE  'condition' GROUP BY column_name

-- having 优先级 where > group by > having
SELECT SUM ( column_name ) FROM table_name WHERE 'condition' GROUP BY column_name HAVING ( 'condition' )

-- 插入数据
INSERT INTO table_name ( column1, column2, column3) VALUES ( value1, value2, value3 )

-- 排序
SELECT column1, column2 FROM table_name WHERE 'condition' ORDER BY column_name { ASC | DESC }

-- 更新数据
UPDATE table_name SET column1 = value1, column2 = value2