SQL
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
4ALTER TABLE students
ADD CONSTRAINT fk_class_id # 外键名称任意
FOREIGN KEY (class_id) # 指定 students 表的外键
REFERENCES classes (id); # 关联到 classes 表 id 这个字段
索引: 对表的某一列或几列预排序加快查找速度(具体索引的算法,有散列表、B树、红黑等等).
索引的使用是有成本的,每次插入/删除/更新数据都需要更新有关的索引,索引使用越多,更改数据的成本越高.
一般都会对主键进行索引,因为主键唯一标识了一个记录.
现在数据库一般都有自动优化的功能,随着使用,会自动优化索引.
例:
1
2ALTER 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;
两列的分组聚合
- 例:
- 统计筛选的结果.求求总和/平均值/最大值XX的.
多表查询
也成为笛卡尔查询.
SELECT * FROM <表1> <表2>
从两个表的笛卡尔积结果查询.- 每一行两两拼接
千万注意,笛卡尔积结果的数目是两个表行数的乘积.不当的查询结果没出来倒好,内存爆掉.
如果两个表中的有字段相同,笛卡尔积也不会合并,此时最好给相同的字段取别名(利用投影).
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18SELECT
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;
- 插入或替换: 如果记录已存在替换,记录不存在插入.
相关文章