SQL

发表于 分类于 阅读次数： Disqus： 本文字数： 3.9k 阅读时长 ≈ 4 分钟

  • SQL 复习

  • 资料来源:

    https://www.liaoxuefeng.com/wiki/1177760294764384

  • 更新

    1
    20.02.26 初始化

导语

  • 复习 Room 顺便复习一些 SQL.
  • 资料来源于 廖雪峰SQL教程
  • 还是声明这里仅仅是复习一点 SQL.省略了很多还记着的地方.

简述

  • ORM 数据库: 对象关系映射数据库,面向对象编程和关系型数据库不太兼容,就有了这么一个中间的层次,方便保存对象.
  • SQL(Structured Query Language)结构化查询语言,最基础的关系型数据库的查询语言.最基础的操作增删改查.

关系模型

  • 关系模型本质上就是若干个存储数据的二维表.

    • 表的每一行称为记录,记录是一个逻辑意义上的数据.
    • 表的每一列称为字段,同一个表的每一行记录都拥有相同的若干字段.

  • 主键: 能唯一标识某个记录的字段名,这个字段被称为主键.

    • 主键的基本原则是：不使用任何业务相关的字段作为主键
    • 身份证号、手机号、邮箱地址这些看上去可以唯一的字段,均不可用作主键(这个真没在意..).
    • 最简单的一个随着插入自增的整数(最好是 BIGINT,INT 在自己应用中当然不会有问题).或者随机生成 UUID/GUID.
    • 主键可以有多个字段,称为联合主键.(一般不用,除非二般)

  • 外键: 标明表彼此的关系的键值,A 表的主键可能在 B 表中也存在,这样的情况下这个主键可以称为外键.

    • 外键使用一般在一对多,多对多.也有一对一使用的,但是这种情况下一般是为了性能,拆分了一个大表成为一个个小表.

    • 为了性能也可以不设外键约束,只靠程序的逻辑判断.

    • 例:

      1
      2
      3
      4
      ALTER TABLE students
      ADD CONSTRAINT fk_class_id # 外键名称任意
      FOREIGN KEY (class_id)     # 指定 students 表的外键
      REFERENCES classes (id);   # 关联到 classes 表 id 这个字段

  • 索引: 对表的某一列或几列预排序加快查找速度(具体索引的算法,有散列表、B树、红黑等等).

    • 索引的使用是有成本的,每次插入/删除/更新数据都需要更新有关的索引,索引使用越多,更改数据的成本越高.

    • 一般都会对主键进行索引,因为主键唯一标识了一个记录.

    • 现在数据库一般都有自动优化的功能,随着使用,会自动优化索引.

    • 例:

      1
      2
      ALTER TABLE students # students 表
      ADD INDEX idx_name_score (name, score); # 指定 name, score 两个字段,新建名为 idx_name_score 的索引.

语句

  • 增删改查.
  • ps: SQL 里面的 ; 标志着这个查询语句的结束.除此以外多行没有 ;.视为同一条查询语句.

查

  • 查是最基础,使用频率最高的操作.

  • 基本查询

    • SELECT * FROM <表名>: 从表中查询所有记录.
    • FROM 不是必须选项, SELECT 1+2 也能执行计算,但一般不用,仅用于测试数据库是否正常连接.
    • SELECT 查询得到的结果依旧是个二维表.可以再加其他的命令.

  • 条件查询

    • SELECT * FROM <表名> WHERE <条件表达式>: 按照 WHERE 后面的条件过滤.
    • 多个条件 NOT AND OR.默认优先级依次降低,()改变一切.
    • 判断: =, >, >=, <, <=, <>(不等于).
      • LIKE(字符串相似): name LIKE ‘%bc%’ %表示任意字符,任意数量.’ab%’将匹配’ab’，’abc’，’abcd’
    • 例: SELECT * FROM students WHERE (score >= 80) AND (name LIKE 'js&');: 这里是 score 字段要大于等于 80 并且 name 以 js 开头的记录.

  • 投影查询

    • SELECT 列1, 列2, 列3 FROM ...: 只查询字段 1 2 3.
    • 相当于对原表做了一个投影.还可以重命名字段.SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ....
    • 其他命令自由组合.
    • 列x 可以替换为函数

  • 排序

    • ORDER BY 默认是升序.显示声明倒序 ORDER BY id DESC; 按照 id 倒序.
    • 如果字段有相同数据还可以指定多个字段排序.不同排序条件之间逗号隔开.ORDER BY id DESC, gender 按照 id 倒序排序,相同 id 再按照 gender 排序.
    • 实际上默认的升序是 ASC,可写可不写.
    • 排序的结果依旧可以向后继续传递..(管道?)

  • 分页

    • 当一次查询的结果过多,一页放不下,分页显示一次抽取一屏幕.
    • LIMIT <M> OFFSET <N>: 从前面的记录,从索引 N 开始抽取 M 条. 注意:索引从 0 开始.
    • 没有 OFFSET 默认为 OFFSET 0.超过索引限制的部分返回 NULL.
    • MySQL中,LIMIT 15 OFFSET 30 还可以简写成 LIMIT 30,15.

  • 聚合查询

    • 统计筛选的结果.求求总和/平均值/最大值XX的.
      • COUNT: 总条目数量.
      • SUM: 某一字段总和(字段必须为数值).
      • AVG: 某一列平均值(字段必须为数值).
      • MAX/MIN: 某一列最大/最小值(字段不一定必须是数字,字符串也行).
    • 其结果仍然是二维表;一行一列; 可以起别名; SELECT COUNT(*) num FROM students;
    • 分组聚合.按照 GROUP BY 后面的字段依次聚合查询.
      • 例: SELECT COUNT(*) num FROM students GROUP BY class_id;: 依次计算相同 class_id 的条目数量.
      • SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender; 两列的分组聚合

  • 多表查询

    • 也成为笛卡尔查询.SELECT * FROM <表1> <表2> 从两个表的笛卡尔积结果查询.

      • 每一行两两拼接

    • 千万注意,笛卡尔积结果的数目是两个表行数的乘积.不当的查询结果没出来倒好,内存爆掉.

    • 如果两个表中的有字段相同,笛卡尔积也不会合并,此时最好给相同的字段取别名(利用投影).

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      SELECT
        students.id sid,
        students.name,
        students.gender,
        students.score,
        classes.id cid,
        classes.name cname
      FROM students, classes;

      # 还是太麻烦就表别名
      SELECT
        s.id sid,
        s.name,
        s.gender,
        s.score,
        c.id cid,
        c.name cname
      FROM students s, classes c;

    • 自由组合.

  • 连接查询

    • 大概可以算另一种类型的多表查询.与笛卡尔积的无脑连接,连接查询是有选择性的.与其说是连接倒不如是对主表选择一部分再附加一些字段.
    • 假设条件都是 ON s.class_id = c.id;
    • INNER JOIN ON : 内连接,只取连接有效部分.即在 s.class_id 和 c.id 都存在的字段对应的记录才进行连接.
    • OUTER JOIN ON : 外连接,取主表的所有结果连接.即 s.class_id 对应的所有记录都会被连接,对那些记录 s.class_id字段值在 c.id 没有的对应记录的,连接后附加的字段值全部为 NULL.
    • FULL OUTER JOIN ON : 全外连接?.不分主次,对所有结果进行连接.无法对应的字段就置为 NULL.

  • 以上全部自由组合.

改删增

  • 增

    • INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
    • 字段的顺序没有要求,但值要与字段对应.
    • 可以一次性插入多组值.用逗号隔开.

  • 改

    • UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
    • WHERE 与查的 WHERE 完全相同.
    • 可以使用表达式比如 UPDATE students SET score=score+10 WHERE score<80; 80 以下+10分
    • 一次性更新多组值也可,只要 WHERE 查到多个记录.
    • 警告: UPDATE语句没有WHERE条件 = 将表的所有字段全部更新.

  • 删

    • DELETE FROM <表名> WHERE ...;
    • WHERE 不加赘述.
    • 警告: DELETE 同样可以没有 WHERE ,但是你想删库跑路??

MySQL

  • MySQL 是一个应用广泛的开源数据库,全家桶 LAMP 的一员.现在是甲骨文 (Oracle) 旗下的产品,因此 LAMP 的 M 更多的变成了 MariaDB.
  • 一些常用命令
    • 插入或替换: 如果记录已存在替换,记录不存在插入. REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
    • 插入或更新: 记录已经存在,更新该记录.记录不存在插入. INSERT INTO ... ON DUPLICATE KEY UPDATE ...
    • 插入或忽略: 记录已存在,忽略.记录不存在插入. INSERT IGNORE INTO ...
    • 快照: 复制一份. CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;
    • 写入查询结果集: 把结果写回表中.INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;
    • 强制使用指定索引: 手动指定索引. SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;
相关文章