SQLite 二周目 - RayAlto's Blog

类型	解释
`NULL`	顾名思义
`INTEGER`	根据实际值，占用 1 2 4 6 8 字节的有符号整数
`REAL`	8 字节 IEEE 浮点
`TEXT`	貌似没有长度限制的字符串
`BLOB`	貌似没有长度限制的二进制数据

关键词	解释
database	顾名思义，但 SQLite 和 MariaDB 之类称为 DBMS
table	顾名思义
schema	模式，数据库和表的布局及特征信息，比如 SQLite 每个数据库都有一个 sqlite_schema
column	顾名思义
datatype	顾名思义
row	顾名思义
primary key	主键，顾名思义
clause	子句，比如 `SELECT` , `FROM` 和 `ORDER BY` 都算子句
operator	顾名思义，一般用在 `WHERE` 子句，也叫 logical operator ，比如 `AND` , `OR`

          
               SELECT * FROM foo;
SELECT foo, bar FROM baf;
SELECT baf.foo FROM baf;

          
               SELECT DISTINCT foo FROM bar;
SELECT DISTINCT foo, bar FROM baf;

          
               SELECT foo FROM bar LIMIT 5;
SELECT foo FROM bar LIMIT 5, 5;
SELECT foo FROM bar LIMIT 5 OFFSET 5;

          
               SELECT a, b FROM foo ORDER BY c, d; -- 默认为升序 (ASC)
SELECT a, b FROM foo ORDER BY c DESC, d ASC; -- c 列降序， d 列升序

          
               SELECT a FROM foo WHERE b == 'c';
-- 注意 WHERE 和 ORDER BY 的前后顺序决定了语句的语义
SELECT a FROM foo WHERE b == 'c' ORDER BY d;

SELECT a FROM foo WHERE b BETWEEN 114 AND 514;
SELECT a FROM foo WHERE b IS NULL; -- 不能写成 `b == NULL`

          
               SELECT a FROM foo WHERE b == 'c' AND c == 'd';
SELECT a FROM foo WHERE (b == 'c' OR b == 'd') AND c == 'E';
SELECT a FROM foo WHERE (b IN ('c', 'd')) AND c == 'E';
SELECT a FROM foo WHERE (b NOT IN ('c', 'd')) AND c == 'E';

          
               SELECT a FROM foo WHERE b LIKE '114_1419%';

          
               SELECT a FROM foo WHERE b REGEXP '114\d+19810';

          
           
            
              
              
               -- 字符串
SELECT upper('foo') AS foo;
SELECT lower('FOO') AS foo;
SELECT length('foo');
SELECT concat('114', '514');
SELECT '114' || '514'; -- 同样是拼接
SELECT ltrim('  114');
SELECT rtrim('514  ');
SELECT trim('  114514  ');
SELECT substring('foobar', 2, 2); -- 等同于 shell `${foobar:2:2}`
SELECT hex('abc'); -- `616263`
SELECT hex(randomblob(8)); -- hex str of random 8 bytes
SELECT unicode('😂'); -- code point of '😂'

-- 数数数数学
SELECT a * b AS foo, c FROM bar;
SELECT abs(-114);
SELECT sin(45);
SELECT acos(45);
SELECT exp(10);
SELECT sqrt(16);
SELECT mod(10, 3);
SELECT pi();
SELECT random();

-- 日期和时间
SELECT time();
SELECT date();
SELECT datetime();
SELECT unixepoch();
SELECT date('now', 'start of month', '+1 month', '-1 day'); -- 这个月的最后一天
SELECT datetime(1145141919, 'auto');

-- 数据聚合
SELECT avg(a) FROM foo;
SELECT avg(DISTINCT a) FROM foo; -- 只取样不重复的值
SELECT count(a) FROM foo GROUP BY b;
SELECT group_concat(a) FROM foo GROUP BY b;
SELECT group_concat(a, ', ') FROM foo GROUP BY b;
SELECT max(a) FROM foo;
SELECT sum(a * b) FROM foo;

              
             

           
          
         




    

          
               SELECT a, count(*) AS a_count FROM foo GROUP BY a;
SELECT a, count(*) AS a_count FROM foo GROUP BY a HAVING count(*) >= 2;

子句说明使用情况

SELECT 选择表的某个（些）列或表达式必须使用

FROM 选择某个（些）表需要选择表时使用

WHERE 行级过滤对每行进行限定时使用

GROUP BY 进行分组对行进行分组时使用

HAVING 组级过滤对组进行限定时使用

ORDER BY 输出排序顾名思义

LIMIT 限制输出行数顾名思义

子句	说明	使用情况
`SELECT`	选择表的某个（些）列或表达式	必须使用
`FROM`	选择某个（些）表	需要选择表时使用
`WHERE`	行级过滤	对每行进行限定时使用
`GROUP BY`	进行分组	对行进行分组时使用
`HAVING`	组级过滤	对组进行限定时使用
`ORDER BY`	输出排序	顾名思义
`LIMIT`	限制输出行数	顾名思义

          
               sqlite> SELECT unhex('e9ab98e7abafe6938de4bd9c') AS 队友呢队友呢救一下啊;
┌──────────────────────┐
│ 队友呢队友呢救一下啊 │
├──────────────────────┤
│ 高端操作             │
└──────────────────────┘

          
               SELECT a FROM foo WHERE b IN (
  SELECT b FROM bar
);

SELECT a, (
  SELECT count(*) FROM bar WHERE foo.b == bar.b
) AS b_count FROM foo;

          
               SELECT a, b FROM foo, bar WHERE foo.c == bar.c;

          
               SELECT a, b FROM foo INNER JOIN bar ON foo.c == bar.c;

          
               SELECT id, name FROM products WHERE id == (
    SELECT id FROM products WHERE type == 'foo'
)

SELECT p1.id, p1.name FROM products AS p1, products AS p2 WHERE p2.type == 'foo' AND p1.id == p2.id;

          
               SELECT a, b FROM foo INNER JOIN bar ON foo.c == bar.c;

          
               SELECT foo.a, bar.b FROM foo LEFT OUTER JOIN bar ON foo.c == bar.c;

          
               SELECT a FROM foo WHERE b < 5
UNION
SELECT a FROM foo WHERE c IN (114, 514);

          
               SELECT a FROM foo WHERE b < 5
UNION ALL
SELECT a FROM foo WHERE c IN (114, 514);

          
               SELECT a FROM foo WHERE b < 5
UNION ALL
SELECT a FROM foo WHERE c IN (114, 514)
ORDER BY d;

          
               -- 狠狠地向纳西妲注入脱氧核糖核酸
INSERT INTO nahida VALUES('DNA');
-- 刚刚有点操之过急了
INSERT INTO nahida VALUES('おちんちん'), ('DNA');
-- 刚刚是不是放错地方了
INSERT INTO nahida(おまんこ) VALUES('おちんちん'), ('DNA');
-- 别问，问就是恨不得把。。。
INSERT INTO nahida(おまんこ) SELECT * FROM me;
-- 狠狠地开发
INSERT INTO nahida(おまんこ, 口) VALUES('おちんちん', 'おちんちん'), ('DNA', 'DNA');

          
               UPDATE foo
SET a = NULL
WHERE b = 114514;

          
               DELETE FROM foo
WHERE b = 114514;

          
               CREATE TABLE foo (
  id   INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  name TEXT    DEFAULT 'bar'
)

          
               ALTER TABLE foo ADD COLUMN type TEXT;
ALTER TABLE foo DROP COLUMN type;
ALTER TABLE foo RENAME TO bar;

          
               DROP TABLE foo;

          
               CREATE VIEW foobar AS
SELECT a, b, c FROM foo, bar WHERE foo.d == bar.d;

          
               SELECT a, b FROM foobar WHERE c == '114514';

          
               CREATE TRIGGER newfoo AFTER INSERT ON foo FOR EACH ROW BEGIN
    SELECT 'Row added';
END;

          
               DROP TRIGGER newfoo;

          
               SELECT * FROM foo; -- foo 表本来有数据
BEGIN TRANSACTION; -- 事务开始（学习龙王的品德）
DELETE FROM foo;   -- rm -rf /*
SELECT * FROM foo; -- 数据都没了
ROLLBACK;          -- 还好我学习龙王的品德，给自己留后路了
SELECT * FROM foo; -- 你复活辣

          
               BEGIN TRANSACTION;
DELETE FROM foo;
COMMIT;

          
               BEGIN TRANSACTION;
-- foo
SAVEPOINT foo;
-- bar
ROLLBACK TO foo;

          
               RELEASE SAVEPOINT foo;