SQLite必知必会————学习笔记

0 背景

因为项目要涉及到大量的操作数据库的操作,所以要熟练掌握基本的SQL(Structured Query Language)语句。其实大二的时候,学校就开设有专门的数据库课程(使用的是SQL Server),但是没有好好的学习,学过后,又很少接触到涉及到这方面的项目,于是大部分知识都忘记了。这次就是通过再次系统的学习一遍SQL语句,捡起曾经学过的知识。由于项目用到的数据库为SQLite,本文就是这次学习SQLite的一个笔记,因此可能不适用于其他数据库。

1 常识

  • 1,数据库软件是指DBMS(Data base manager system),而数据库是指有组织的数据库容器(一个数据库文件或一组文件,如带有db后缀的数据库文件)
  • 2,市场上有很多数据库软件如SQL Server、Orccle、MySQL、MariaDB、PostgreSQL、SQLite等等,不同的数据库软件数据类型和名称是不同的,这就造成了SQL不兼容,我们学习的话一般可以学习ANSI SQL(美国标准学会SQL)的语法规范,然后再根据自己实际使用的数据库学习对应特有的语法(如存储过程SQLite就没有)
  • 3,一般使用全大写的书写SQL的关键词,对列名和表名使用小写,来让代码更易阅读和理解,虽然SQL不区分大小写

2 SELECT语句

SELECT 列名/(列名 ,COUNT(*) AS 别名)/(列名 || 拼接字符串) FROM 表名 
WHERE 条件 
GROUP BY 列名 HAVING COUNT(*) >/</>=/<=/!=LIMIT 显示的行数 OFFSET 从第几行显示的行数 
ORDER BY 依据的排序的列 DESC(降序)/ASC(默认升序)

混淆点:

  • WHERE:过滤行,在分组前过滤;HAVING:过滤分组,在分组后过滤
  • GROUP BY:只能对选择的列和表达式使用,在有聚集函数的时候必须使用;ORDER BY:对非选择列也可以使用,对输出顺序排序(GROUP BY不排序)

2.0 别名

AS(Oracle中不使用它,直接把取得名字用于表名和列名后)
作用:给导出的列命名

2.1 筛选

WHERE:

  • 1
WHERE 条件 BETWEENAND
  • 2,
WHERE 条件  AND 条件
  • 3,
    • AND的优先级大于OR
WHERE 条件  OR 条件
  • 4,
    • OR与IN作用相等,一般推荐使用IN
WHERE 条件  IN(1、值2)
  • 5,
WHERE NOT 条件
  • 6,
    • 通配符
    • %:任何字符出现任何次数,但不匹配NULL
    • _:匹配单个字符
    • 不要过度使用通配符
WHERE 条件 LIKE

2.2 函数

  • 1,文本处理函数
    • 1,去空格
      RTRIM(去空格右边),LIRIM(去空格左边),TRIM(去空格左右两边)
    • 2,大小写转换
      UPPER(转大写),LOWE(将字符转转小写)
    • 3,SOUNDEX
      将任何文串转换为其语音表示的字母数字模式的算法
  • 2,时间处理函数
    • 1 strftime(’%格式’, 列名)
      作用:根据第一个参数指定的格式字符串返回格式化的日期
时间格式:
%d 日期, 01-31
%f 小数形式的秒,SS.SSS
%H 小时, 00-23
%j 算出某一天是该年的第几天,001-366
%m 月份,00-12
%M 分钟, 00-59
%s 从197011日到现在的秒数
%S 秒, 00-59
%w 星期, 0-6 (0是星期天)
%W 算出某一天属于该年的第几周, 01-53
%Y 年, YYYY
%% 百分号
  • 2,返回时间
date(timestring, modifier, modifier, ...):以 YYYY-MM-DD 格式返回日期。
time(timestring, modifier, modifier, ...):以 HH:MM:SS 格式返回时间。
datetime(timestring, modifier, modifier, ...):以 YYYY-MM-DD HH:MM:SS 格式返回。
julianday(timestring, modifier, modifier, ...):这将返回从格林尼治时间的公元前 47141124 日正午算起的天数。

timestring:
在这里插入图片描述
修饰语(modifier):

NNN days

NNN hours

NNN minutes

NNN.NNNN seconds

NNN months

NNN years

start of month

start of year

start of day

weekday N

unixepoch

localtime

utc

示例:
计算本月最后一天:

SELECT date('now','start of month','+1 month','-1 day');

由时间戳得到相对本地的日期:

SELECT datetime(1092941466, 'unixepoch', 'localtime');

由日期得到时间戳:

SELECT strftime('%s','now');

计算美国"独立宣言"签署以来的天数

 SELECT julianday('now') - julianday('1776-07-04');

从 2004 年某一特定时刻以来的秒数:

SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');

下面是计算当年 10 月的第一个星期二的日期:

SELECT date('now','start of year','+9 months','weekday 2');

本小节的列子和图片摘自runoob网站

  • 3,数值处理函数
    ABS()绝对值、COS()余弦值、TAN()正切、SIN()正弦、EXP()返回一个数的指数、PI()返回圆周率、SQRT()一个数的平方根、

  • 4,聚集函数(对某些列运行的并计算出一个值)

  • AVG:某列平均值

  • COUNT:某列的行数

    • COUNT(*):对空值也计算
    • DISTINCT:只包含不同值;只能用于COUNT(),不用于CONT(*),必须使用列名;
      • SELECT AVG(DISTINCT price) AS avg_price FROM product WHERE id = '1'
    • ALL:对所有行执行(默认)
  • MAX:某列的最大值

  • MIN:某列的最小值

  • SUM:某列之和

2.3 过滤分组

  • 1 GROUP BY(分组)
    将数据分为多个逻辑组,然后对每个组进行聚集计算
  • 2,HAVING(过滤分组)

3 联结(最重要的特性)

3.1 完全限定名

在使用列名的时候,指名表名,例如SELECT goods.id,price.id FROM goods,price,用于当两个表中有相同的列名时避免混淆;

3.2 笛卡尔积(叉联结)

返回两个第一个表的行数*第二个表的行数的检索列数
在这里插入图片描述

3.2,等值联结(内联结)

返回两个表得中的相等的值
在这里插入图片描述

SELECT name FROM students,teachers WHERE students.id = teachers.id

等同于

SELECT name FROM students JOIN teachers ON students.id = teachers.id

3.3 自联结和子查询、自然联结

  • 1,self-join(不止一次使用相同的表)
    例如:
    子联结:
SELECT id FROM students WHERE name = (SELECT course FROM students WHERE  = 'math')

使用联结:

SELECT s1.id FROM students AS s1 ,students AS s2 WHERE s1.name = s2.name AND s2.course = 'math'

一般使用联结要比使用子查询快许多

  • 2,nature join(自然联结)
    自动使用所有匹配的列名进行连接【未能证实:可以排除相同列的多次出现,使每一个列值返回一次
    要求两个表中要有相同的列名和属性值,否则返回笛卡尔积
SELECT C.*,O.* FROM Customers C  NATURAL JOIN Orders O 

3.4 外联结

下图是外联结中的左联结,SQLite中不支持右联结,但是只要挑换FROM或WHERE中的表顺序即可将左联结转为右联结。
在这里插入图片描述

3.4 全联结(SQLite不支持)

FULL OUTER JOIN:返回两个表中的所有行,并关联可以关联的行。

4 并/复合查询

对不同表或者一个表进行多次查询。

4.1 UNION(会自动去重)

  • 1,两条及以上的SELECT语句使用;
  • 2,每个查询包含相同的列表达式、聚集函数;
  • 3,只允许使用一条ORDER BY语句。

4.2 UNION ALL

不取消重复行,

5 插入语句

INSERT INTO/IGNORE 表名(列名) VALUES(1,值2,。。。)

IGNORE:如果中已经存在相同的记录,则忽略当前新数据,并忽略错误;INTO:如果有相同数据则报错

5.1 插入检索的数据

INSERT INTO 表名(列名) SELECT 列名 FROM 表名

5.2 从一个表复制到另一个表

  SELECT 列名 INTO 表名 FROM 表名

DB2不支持上述语法

CREATE TABLE 表名 AS SELECT 列名 FROM 表名 

6 更新值

更改值前,先用WHERE确定一遍WHERE过滤的值是否正确

UPDATE 表名 SET 列名 =WHERE 过滤条件 

7 删除值

7.1 删除列值

UPDATE 表名 SET 列名 =WHERE 过滤条件 

设值为NULL,即可删除列值

7.2 删除行值

删除行值:

DELETE FROM 表名 过滤条件

删除所有行一般使用:

TRUNCATE TABLE 表名

SQLite没有上述语法,一般使用DELETE FROM 表名

7.3 删除表

DROP TABLE 表名

8 创建表/更新表

创建表

CRETAE TABLE表名(列名 类型 是否为空 DEFAULT 默认值,...列名 类型 是否为空 DEFAULT 默认值)

更新表

ALTER TABLE 表名 (ADD 列名 属性)/(RENAME TO 新表名)

SQLite不支持ALTER TABLE定义主键和外键

9 视图

SQLite支持可读视图

  • 1, 把查询包装成一个虚拟表
  • 2,重用SQL语句,使用表的一部分,保护数据、更改数据格式和表示

创建视图:

CREATE VIEW 表名 AS 别名 语句

删除视图:

DROP VIEW 表名

10 存储过程(SQLite不支持)

关键词:PROCEDURE
作用:为以后使用而保存一条而多条SQL语句

11 事务

创建事务:

BEGIN TRANSACTION/BEGIN -- 开始事务
..事务
COMMIT/END TRANSACTION  -- 提交

回退(不能回退CRETAE、DROP、SELECT操作):

ROLLBACK;

保留点:事务处理的临时占位符,可对它发布回退

12 游标/步骤(Step)

作用:操作结果集的行
用于:

  • 1,滚动屏幕上的数据,并对数据左出游览或更改;
  • 2,一旦声明,就必须打开游标以供使用,结束使用时,必须关闭游标

13 约束

作用:管理插入或处理数据库的规则

13.1 主键

  • 1,任意两行的主键值不同
  • 2,每行只有一个主键且唯一,且不为NULL
  • 3,主键列不修改、不更新
  • 4,主键不重复
    关键词:PRIMARY KEY
    SQLite不允许使用ALTER TABLE定义主键

13.2 外键

含义:表中一列,其值为另一个表中的主键

  • 1,防止意外删除(有些DBMS可用级联删除)
    关键词:REFERENCES

13.3 唯一约束

  • 1,不能用于定义外键
  • 2,区别与外键,它可以是多个、可包含NULL、可修改、可更新、可重复使用
    关键词:UNIQUE

检查约束:CHECK

14 索引

作用:恰当的排序

  • 1,改善检查操作的性能,降低了数据插入、删除、修改的性能
  • 2,占用大量的存储空间
  • 3,尽量用于数据过滤或排序的列
  • 4,可在索引定义多个列
  • 5,定期检查索引并调整

15 触发器(特殊的存储过程)

作用: 在特定的数据活动发生时出发

  • 1,数据访问权:INSERT/UPDATE/DELETE 所有数据库
  • 2,常见用途:保证数据一致,基于表的变动在其他表执行活动,进行额外的验证并根据需要回退;计算值或更新时间戳

约束比出发器更快,应尽量使用约束

CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name 
ON table_name
BEGIN
 -- 触发器逻辑....
END;

event_name:INSERT/UPDATE 等

16 安全性

GRANT/REVOKE:管理访问

17 数据规范

字符串:

  • 1,变长性能远低于鼎昌的
  • 2,值在单引号内
  • 3,计算的值存在数据类型,否则存在字符串(如0123,在数值中可能会丢值)

数值类型:
货币:MONEY/CURRENY(也定的取值范围DECIMAL可变浮点)

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 岁月 设计师:pinMode 返回首页