为了学SQL注入和存储型XSS特地跑过来开一帖复习一手SQL…
内容大部分摘自廖雪峰老师的站,这一帖权当抄书,也加了一些自己的理解,就不投转载了~
0x01 intro
数据库?
因为我们需要保存用户的数据,比如Word需要把用户文档保存起来,以便下次继续编辑或者拷贝到另一台电脑。
这种方法在自己的电脑上做一些简单的事情是可行的,但是在大规模的项目上容易造成管理的混乱。因为我们需要:
- 快速读写文件并解析出数据
- 从成千上万的数据中快速查询出指定数据
而由许多人维护的项目又需要做到比如统一接口以及同步化等等要求,所以,数据库作为一种专门管理数据的软件(以及标准)就出现了:
应用程序通过数据库软件提供的接口来读写数据。至于数据本身如何存储到文件,那是数据库软件的事情,应用程序就不管了~
关系型数据库?
数据库按照数据结构来组织、存储和管理数据,实际上,数据库一共有三种组织数据的模型:
- 层次模型:类比数据结构中的二叉树
- 网状模型:类比数据结构中的图
- 关系模型:类比Excel中的sheets
关系模型理解和使用起来最简单,关系数据库获得了最广泛的应用。
主流的关系数据库
目前,主流的关系数据库主要分为以下几类:
- 商用数据库,例如:Oracle,SQL Server,DB2等;
- 开源数据库,例如:MySQL,PostgreSQL等;
- 桌面数据库,以微软Access为代表,适合桌面应用程序使用;
- 嵌入式数据库,以Sqlite为代表,适合手机应用和桌面程序。
SQL?
结构化查询语言,说人话就是用来对关系型数据库做增删改查的计算机标准语言。
SQL语句既可以查询数据库中的数据,也可以添加、更新和删除数据库中的数据,还可以对数据库进行管理和维护操作。
各个不同的数据库对标准的SQL支持不太一致,并且大部分数据库都在标准的SQL上做了扩展。
SQL定义了下面几种操作数据库的功能:
- Data Definition # 表级操作(一般是admin行为)
- Data Manipulation # 增删改
- Data Query # 查
注意:SQL关键字不分大小写!
0x02 安装
略。有空再补。
廖雪峰老师homepage或者FreeAIHub上有在线版本。
直接安装一个docker然后在cmd中输入:
docker pull mysql:latest
运行MySQL时:
docker run -itd --name mysql-test -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql
docker真香
未尽事项参阅:Docker 安装 MySQL
0x03 关系模型
关系数据库建立在关系模型上,而关系模型本质上就是若干个存储数据的二维表,可以把它们看作很多Excel表。
表的每一行称为记录(Record),记录是一个逻辑意义上的数据。
表的每一列称为字段(Column),同一个表的每一行记录都拥有相同的若干字段。
字段会指定数据类型,以及是否可以为NULL。某个记录的某个字段为NULL则说明它的数据不存在。
和EXCEL不同,关系数据库的表和表之间需要建立“一对多”,“多对一”和“一对一”的关系,这样才能够按照应用程序的逻辑来组织和存储数据。
这里的一对多、多对一、一对一指的是不同表的不同记录之间的对应关系。
在关系数据库中,关系是通过主键和外键来维护的。我们在后面会分别深入讲解。
0x04 主键
主键
一张表中用来唯一标识某一条记录的字段。
但是,唯一标识某一条记录的字段做主键不一定合适。
例如,学号就可以做唯一标识的字段,而性别无法做到唯一标识。
对主键的要求,最关键的一点是:记录一旦插入到表中,主键最好不要再修改,因为主键是用来唯一定位记录的,修改了主键,会造成一系列的影响。
选取主键的一个基本原则是:不使用任何业务相关的字段作为主键。比如,身份证号也有变动的时候。
作为主键最好是完全业务无关的字段,我们一般把这个字段命名为id。一般地,有下面几种选择:
- 自增整数,但是INT最大只到20亿,用它来统计全球人口就凉凉了~
- 全局唯一GUID类型:做法是找点随机数、MAC地址和当前时间段做一个哈希,可以很好地保证唯一性。
通常自增整数就差不多够了…
联合主键
关系数据库实际上还允许通过多个字段唯一标识记录,即两个或更多的字段都设置为主键,这种主键被称为联合主键。
对于联合主键,允许一列有重复,只要不是所有主键列都重复即可。(一般不用)
0x05 外键
通过在一张表里加入某个特殊的字段,可以对应另一张表的相关信息。这样的字段被称为外键。
外键并不是通过列名实现,而是通过外键约束实现:
ALTER TABLE students; # 选择更改student表
ADD CONSTRAINT waijianyueshu1234236642342; # 添加一个名字瞎起的约束
FOREIGN KEY (class_id); # 在student表里指定class_id作为外键
REFERENCES classes (id); # 指定外键和classes表中id字段相符
外键约束可以保证防止一些奇奇怪怪的输入:比如我想要插入一条班级id是”fhwhfoiwfvcws“的学生记录,这个时候在classes表里没有id=”fhwhfoiwfvcws“这么一条记录,于是…
要删除一个外键约束,也是通过ALTER TABLE实现的:
ALTER TABLE students;
DROP FOREIGN KEY waijianyueshu1234236642342;
# 删除外键约束 != 删除字段
注:外键约束会降低数据库的性能,因此大部分互联网应用程序为了追求速度,并不设置外键约束,而是仅靠应用程序自身来保证逻辑的正确性。这种情况下,class_id仅仅是一个普通的列,只是它起到了外键的作用而已。
多对多
通过一个表的外键关联到另一个表,我们可以定义出一对多关系。有些时候,还需要定义“多对多”关系。例如,一个老师可以对应多个班级,一个班级也可以对应多个老师,因此,班级表和老师表存在多对多关系。
此时,通过一个中间表就可以实现多对多的关系。
比如,一个老师对应多个班,一个班对应多个老师:这个时候我们可以新建一个各个老师在各个班级的授课情况表,表里包括班级和老师的id,再分别和老师、学生表相联系就可以了。
一对一
一对一:一个表的记录对应到另一个表的唯一一个记录。
例如,我们有两个表,一个是学生成绩表,另一个是学生家长联系方式表。那么成绩表的学生id唯一对应学生,也唯一对应联系方式表中的联系方式。
实际上,一对一关系准确地说,是联系方式表一一对应成绩表。
为什么我们要把这两个表拆开?放到一起不行吗?
有一些应用会把一个大表拆成两个一对一的表,目的是把经常读取和不经常读取的字段分开,以获得更高的性能。
当然放到一起也不是不行,开心就好
0x06 索引
查找记录的时候,如果记录数量极多,为了保证速度,则需要索引。
索引是关系数据库中对列的值作预排列的数据结构(功能类似于哈希表,数据库系统不必扫描整个表,而是直接根据数据就可以定位到符合条件的记录)
eg 下面的表:
如果score字段经常用(比如一周一考试的话就有高频IO),就创建score列的索引:
ALTER TABLE students;
ADD INDEX idx_score(score)
即创建了一个名为idx_score使用score的索引。
索引也不只有一个,可以加入多个字段作为索引:
APTER TABLE students;
ADD INDEX idx_name_score (name, score)
索引的效率取决于索引字段的值是不是足够散,比如要是所有学生都是class 1 的,而我把class作为索引,那么这个索引就做得毫无意义…
索引可以提高查询效率,但是在增删改时,索引也会变,因此会对运行速度有一些减慢。
对于主键,关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的,因为主键会保证绝对唯一。
唯一索引
在设计关系型数据表的生活,我们说过,身份证号/电话号码/学号这种有业务意义的字段不宜作为主键。
但是这些东西确实又是唯一的…这个时候,为了确保唯一性,我们可以给这些字段添加唯一索引:
ALTER students;
ADD CONSTRAINT nui_name UNIQUE (student_id)
这种情况下student_id列无索引,但是仍然是唯一的。
创建索引与否对用户的影响只会是速度的变化,而在数据库的增删改查语句的使用不会有任何区别。
当我们在数据库中查询时,如果有相应的索引可用,数据库系统就会自动使用索引来提高查询效率,如果没有索引,查询也能正常执行,只是速度会变慢。
此即,数据库索引对于用户和应用来说都是透明的。
0x07 在线SQL
AlaSQL实际上是在浏览器页面运行的一个JavaScript编写的内存型SQL数据库。不必运行MySQL等实际的数据库软件,即可在线编写并执行SQL语句。
总结:AlaSQL yyds !
0x08 查询数据
关系型数据库中,最常用的操作是增删改查,最最常用的是查询。 ——廖雪峰
我们下面玩两个表:
students表存储学生信息:
classes表存储班级信息:
真正的MySQL数据库在退出之后重新登录时还会保存当前数据。
0x09 基本查询
查询数据:使用下面语句
SELECT * FROM students;
# 选择 所有字段 从 students表
SELECT 其实不需要有FROM,例如:
SELECT 1 + 1;
0x0a 条件查询
使用SELECT * FROM <表名>
可以查询到一张表的所有记录。但是,很多时候,我们并不希望获得所有记录,而是根据条件选择性地获取指定条件的记录。
例如:选取高分段学生:
SELECT * FROM students WHERE score>=80;
# SELECT * FROM <表名> WHERE <条件表达式>
条件查询可以使用与或非:AND/OR/NOT
各种条件表达式:AND/OR/NOT
SELECT * FROM students WHERE score >= 80 AND gender == "M"
# 这里gender = "M"和gender == "M"似乎都可以,即对于SQL来说=和==是等价的,但是=============这种乱入的就不行了
也可以通过括号对查询逻辑进行限制:
SELECT * FROM students WHERE (1=1 OR id == 2) AND id=3;
如果不加括号,可以按照优先级NOT > AND > OR进行。
常用的逻辑表达式:
0x0b 投影查询
说人话,就是选择我们想要的列,毕竟查成绩的话不需要学号、政治面貌、担任职务这一类无用信息~
再比如已经按班号查成绩了,这个时候班号就约等于无意义了…
其实就是把上面的 * 换成字段名啦~
也可以给输出的字段指定别名:
select name n, score from students where score > 80;
# select <column> <alias>, ... from students where <cond>;
于是显示的列名就成了我们指定的别名n:
0x0c 排序
(题外话,SELECT查询给我的感觉就像是选择后面语句里输出的东西x
SELECT 排序时,查询结果集通常按照ID排序,即根据主键排序。
但是如果需要其他的排序方式怎么办?
可以使用ORDER BY 字句,同时使用DESC可以完成降序。
0x0d 分页查询
使用SELECT查询时,结果集的数据量如果很大(例如SELECT * FROM people WHERE gender=“F”)的话,为了可读性不如分页显示一手。
分页的原理就是从结果中抓取一些(比如前100条)记录作为第一页,再抓一些作为第二页…
因此,分页实际上就是从结果集中“截取”出第M~N条记录。
实现方法:
SELECT id, name, gender, score FROM students ORDER BY score LIMIT 3 OFFSET 0;
# LIMIT:一页中3条记录;OFFSET:从分页前的表中第x条记录开始算
# 即从第OFFSET条记录开始显示,显示LIMIT个
如果原本记录集一共就10条记录,但我们把OFFSET设置为20,会得到什么结果呢?
此时OFFSET超出的部分记录会被默认为空,也就是说,SQL会返回一个空的结果集。
注意
- OFFSET是可选的,如果只写LIMIT=123则默认从第0条数据开始显示;
- MySQL中,LIMIT $M OFFSET $N 也可以写成:LIMIT $N, $M
- 查询效率随着查询结果的增多而降低
0x0e 聚合查询
业务场景:我想知道全年级的学生里优良分段有多少人。
显然,执行下面命令后人工数一手是可以的
select * from students where score >= 80;
好了,解是存在的
但是对于统计总数、平均数这类计算,SQL提供了专门的聚合函数,使用聚合函数进行查询,就是聚合查询,它可以快速获得结果。
一些例子:
SELECT COUNT(*) FROM students;
返回结果:
这里COUNT(*)表示查询结果的行数。要注意:聚合的计算结果虽然是一个数字,但查询的结果仍然是一个二维表
使用前面讲的设置别名的技术,我们可以给列名设置alias,便于处理结果:
SELECT COUNT(*) n FROM students;
除了COUNT之外,SQL还有下面几个常用的聚合函数:
下面来一个“大查烩”:
SELECT AVG(score), MAX(score), COUNT(*) FROM students WHERE gender = 'M';
要特别注意:如果聚合查询的WHERE条件没有匹配到任何行,COUNT()会返回0,而SUM()、AVG()、MAX()和MIN()会返回NULL:
SELECT COUNT(id), AVG(score), SUM(score), MAX(score), MIN(score) FROM students WHERE gender="?"
结果如下:
分组
业务场景:我们要统计各个班的学生数量。现在我们可以统计每个班的学生数量,但是难道还要挨个挨个敲WHERE?
对于聚合查询,SQL有“分组聚合”的功能:
SELECT COUNT(*) FROM students GROUP BY class_id;
# 执行这个查询,COUNT()的结果不再是一个,而是3个,这是因为,GROUP BY子句指定了按
# class_id分组,因此,执行该SELECT语句时,会把class_id相同的列先分组,再分别计算,因
# 此,得到了3行结果。
得到结果如下:
但是哪个班的似乎并不好看出来,所以也可以将class_id也放入结果集中。
SELECT COUNT(*), class_id FROM students GROUP BY class_id;
此外,也可以使用多个列进行分组。例如,我们想统计各班的男生和女生人数:
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
结果如下:
0x0f 多表查询
SQL可以从大于等于一张表中查询数据。
查询语法:
SELECT * FROM <表1> <表2>
在继续我们的内容之前先稍微思考一下,这里表一表二可没说是啥表吧,那么检索的东西会议什么形式出现在咱们的面前呢?
比如说,一张表是学生成绩表,另一张表是牢厂员工工资表,这两个表可能没有一个相同的字段…
这个时候,SQL的返回结果就是两个表的笛卡尔积。
以廖雪峰老师的在线SQL为例,我们输入:
SELECT * FROM students, classes;
可以看出这时students表的每一行与classes表的每一行都两两拼在一起返回。
这种多表查询又称笛卡尔查询。
使用笛卡尔查询时要非常小心,例如牢厂员工工资表和年级成绩表的多表查询
(血压逐渐升高
此外,从刚刚得到的结果来看,两个表都有id字段,重复了…
为了解决这个问题,我们可以给列设置alias:
SELECT
students.id sid,
students.name,
students.gender,
students.score,
classes.id cid,
classes.name cname
FROM students, classes;
# (其中.运算符是不是很熟悉
注意,多表查询时,要使用表名.列名
这样的方式来引用列和设置别名,这样就避免了结果集的列名重复问题。
但是表名太长怎么办?我们也可以给它设置一些alias:
SELECT
s.id sid,
s.name,
s.gender,
s.score,
c.id cid,
c.name cname
FROM students s, classes c;
多表查询也可以添加WHERE子句来进行筛选…(懒得写例子了…)
0x10 连接查询
(个人感觉这一块内容还是挺重点的)
另一个类型的多表查询。
连接查询对多个表进行JOIN运算,简单地说,就是先确定一个主表作为结果集,然后,把其他表的行有选择性地“连接”在主表结果集上。
内连接
举个例子,想要选出students表的所有学生信息的话,可以直接无脑SELECT:
SELECT * FROM students s;
但是如果我们希望把学生班级信息和考试成绩都统计在一块的话…
此时,存放班级名称的name
列存储在classes
表中,只有根据students
表的class_id
,找到classes
表对应的行,再取出name
列,就可以获得班级名称。
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score FROM students s INNER JOIN classes c ON s.class_id == c.id;
INNER JOIN的写法是:
- 先确定主表,主表使用
FROM <table 1>
的语法 - 确定需要连接的表,使用
INNER JOIN
的语法 - 确定连接条件,
ON <cond>
,表明符合cond的行需要拼接; - 可选WHERE, ORDER BY
外连接
有INNER JOIN,应该也有OUTER JOIN…
OUTER JOIN 也分LEFT/RIGHT/FULL JOIN,其区别是:
INNER JOIN只返回同时存在于两张表的行数据,例如上面的例子中我们有
students
表中的class_id
包含1,2,3班,但是classes
表中有1,2,3,4班,所以INNER JOIN 只返回123班的学生成绩(没有4班的)。
(LEFT 指 FROM 关键词后面的数据表;RIGHT指 JOIN 关键词后面的表)
RIGHT OUTER JOIN返回右表都存在的行。假设有下面的语句:
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score FROM students s RIGHT OUTER JOIN classes c ON s.class_id == c.id;
结果如下:
我们可以看出,RIGHT OUTER JOIN相当于把右表classes中的所有记录都加进来了。
LEFT OUTER JOIN 同理,假设有一个114514班的学生小田所在students表内,那么因为是LEFT,所以这条记录必定会出现在查询结果中,但是没有114514班的话,班级的名称啊啥的就只能是NULL了…
FULL OUTER JOIN 的意思是说把两个表先扔一块,然后能匹配的就匹配,不能的就写NULL…
这数据库太臭了
0x11 修改数据
增 删 改
增 INSERT
(这一块因为我之前在FREEAIHUB上学的时候写过比较多,所以我就直接放代码实例了
半途而废第一名
INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80);
-- 查询并观察结果:
SELECT * FROM students;
(可看最后一行)
还可以一次性添加多条记录,代码就不写了
改 UPDATE
UPDATE students SET name="大牛", score=66 WHERE id==1;
SELECT * FROM students WHERE id == 1;
查看结果:
如果WHERE条件没有匹配到任何记录,UPDATE语句不会报错,也不会有任何记录被更新。
最后,要特别小心的是,UPDATE语句可以没有WHERE条件,这个时候SQL会直接改变所有的匹配数据。
删 DELETE
DELETE FROM students WHERE id=1;
现在牛哥已经没了:
如果WHERE条件没有匹配到任何记录,DELETE语句不会报错,也不会有任何记录被删除。
和UPDATE类似,不带WHERE条件的DELETE语句会删除整个表的数据。
在使用真正的关系数据库时,会返回DELETE,UPDATE变动的行数以及WHERE匹配的行数。
0x12 管理MySQL
MySQL提供了大量的SQL语句用于管理。
当然GUI还是香啊
数据库
在一个运行MySQL的服务器上,实际上可以创建多个数据库(Database)。要列出所有数据库,使用命令:
SHOW DATABASES;
创建新数据库:
CREATE DATABASE test;
删除数据库:
DROP DATABASE test;
对数据库进行操作时,首先要切换为当前数据库:
USE test;
表
列出当前数据库的所有表:
SHOW TABLES;
要查看一个表的结构:
DESC students;
创建表:
CREATE TABLE 'students' (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`class_id` bigint(20) NOT NULL,
`name` varchar(100) NOT NULL,
`gender` varchar(1) NOT NULL,
`score` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
删除表:
DROP TABLE students;
修改表就比较复杂了。
给students表新增birth字段:
ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;
把birth改成birthday:
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;
删除列:
ALTER TABLE students DROP COLUMN birthday;
退出MySQL:
EXIT
0x13 实用SQL语句
编写SQL的时候,灵活运用一些技巧的话可以大大简化程序逻辑。
插入和替换
插入一条新纪录,但是这条记录在数据库中已经存在的话,我们会删了这个记录再加一个新的。但是我们不需要DELETE之后再INSERT INTO,可以直接:
REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, "小明", "F", 99);
插入或更新
如果我们希望插入新的记录,但是记录已经存在的话,我们会为它指定更新的内容。此时,可以:
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, "小明", "F", 99) ON DUPLICATE KEY UPDATE name="小明", gender="F", score=99;
插入或忽略
插入记录时,如果记录存在,则什么事都不干。此时,可以使用:
INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, "小明", "F", 99);
Snapshot
复制一份当前表的数据到一个新表:
CREATE TABLE snap SELECT * FROM students WHERE class_id=1;
写到查询结果集
如果查询结果集需要写入到表中,可以结合INSERT和SELECT,将SELECT语句的结果集直接插入到指定表中。
CREATE TABLE statistics (
id BIGINT NOT NULL AUTO_INCREMENT,
class_id BIGINT NOT NULL,
average DOUBLE NOT NULL,
PRIMARY KEY (id)
);
然后写入各个班的平均成绩:
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;
当然,这里的idx_class_id得存在。
0x14 事务
举一个例子:A给B转了100块钱。那么,在银行的数据库中,就需要完成两件事:给A扣100块钱和给B加100块钱。
这两个事情要么同时完成,要么同时不完成,如果完成一部分的话,就需要回滚到完成之前的状态。
这种把多条语句作为一个整体进行操作的功能,被称为数据库事务。
数据库事务可以把多条语句作为一个整体来执行,确保该事务范围内的所有语句全部成功或者全部失败。如果存在失败,则会撤销这个事务中已经执行的所有操作。
数据库事务有下面几个特性:
- 原子性,事务里的语句作为整体被执行。单条sql语句被称为隐式事务;
- 一致性,在执行完成后,所有数据的状态是一致的;
- 隔离性,各个事务并发执行时,事务需要隔离;
- 持久性,持久地存储事务执行后的结果。
单条sql语句被称为隐式事务。要想手动地把多条语句攒成一个事务,需要用BEGIN
, COMMIT
等语句。
例如:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; # 提交事务,指永久保存
有些时候,我们还会希望主动让事务失败,这时,可以用ROLLBACK
回滚事务,整个事务会失败。
隔离级别
事务的并发执行就容易出现同步化的问题。其出现原因本质上还是CPU的分时机制。
并发操作带来的数据的不一致性包括脏读、不可重复读、幻读等。数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离级别,避免数据不一致的问题。