作者:h0we777
免责声明:本文仅供学习研究,严禁从事非法活动,任何后果由使用者本人负责。
0x00 前言
MYSQL是最流行的关系型数据库管理系统,在WEB应用方面MYSQL是最好的RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。
0x01 MYSQL简介
1.1 什么是数据库
数据库(Database,简称DB)是按照数据结构来组织、存储和管理数据的仓库,其本身可看作电子化的文件柜,用户可以对文件中的数据进行增加、删除、修改、查找等操作。需要注意的是,这里所说的数据(Data)不仅包括普通意义上的数字,还包括文字、图像、声音等,也就是说,凡是在计算机中用来描述事物的记录都可称作数据。
现在通常使用关系型数据库管理系统(RDBMS)来存储和管理大数据量。所谓关系型数据库,就是建立在关系模型基础上的数据库,借助集合代数等数学概念和方法来处理数据库中的数据。
1.2 RDBMS的特点
RDBMS(全称 Relational Database Management System)即关系型数据库管理系统的特点:
数据以表格的形式出现;
每行为各种记录名称;
每列为记录名称所对应的数据域;
许多的行和列组成一张表格;
若干的表单组成Database。
1.3 RDBMS术语
关系型数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
RDBMS常见术语:
数据库:若干个数据表的集合。
数据表:表是数据的矩阵;在一个数据库中的表看起来像一个简单的电子表格。
行:每一行(元组/记录)都是一组相关的数据,如一条用户订阅的数据。
列:每一列(数据元素)包含了相同类型的数据,如邮政编码的数据。
冗余:存储两倍数据,冗余降低了性能,但提高了数据的安全性。
主键:在一个数据表中具有唯一性,可以使用主键来查询数据。
主键值必须唯一标识表中的每一行,且不能为null,即表中不可能存在两行数据有相同的主值键。
每个表只能定义一个主键,可以是表中的某一列或者多列的组合。
外键:外键用于关联两个表。
复合键:复合键(组合键)将多个列作为一个索引键,一般用于复合索引。
索引:索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息,类似于书籍的目录。
参照完整性:参照的完整性要求关系中不允许引用不存在的实体。与实体的完整性是关系模型中必须满足的完整性约束条件,目的是保证数据的一致性。
1.4 MYSQL数据库
Mysql是一种关联数据库管理系统,由瑞典MYSQL AB公司开发,目前属于Oracle公司。
Mysql是开源的。
Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
Mysql使用标准的SQL数据语言形式。
Mysql可以运行于多个系统上,并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
Mysql是可以定制的,采用了GPL协议,可以修改源码来开发自己的Mysql系统。
Mysql对PHP有很好的支持。
Mysql支持大型数据库,支持5000万条记录的数据仓库,32位系统表文件最大可支持4GB,64位系统支持最大的表文件为8TB。
0x02 MYSQL基础知识
2.1 MYSQL 监视器
2.1.1 创建数据库前的准备事项
安装好mysql;
设置好mysql安装文件夹的路径;
完成mysql的中文设置。
2.1.2 什么是MYSQL监视器
安装好 MySQL 之后,就可以使用被称为 MySQL 监视器的程序了。MySQL 监视器这个客户端程序以用于操作 MySQL 的 CUI(Character User Interface,字符用户界面)为基础。MySQL 监视器不支持使用鼠标来进行点击或拖曳,用户需要使用键盘直接输入命令来操作数据库,输出结果也全部以文本的形式显示出来。
2.1.3 启动终端软件
Windows 的命令提示符需要通过选择“开始”菜单的“Windows 系统”→“命令提示符”启动(在 Windows 7 的情况下,选择“开始”→“所有程序”→“附件”→“命令提示符”),或者同时按下 Win 键和 R 键,显示出“运行”对话框后,输入“cmd”,点击“确定”,这样也可以启动命令提示符。
2.1.4 启动MYSQL监视器
在命令提示符里输入mysql命令,就可以启动mysql监视器了。具体格式如下:
mysql -u 用户名 -p 密码
“-u 用户名”和“-p 密码”称为选项(option)。执行 mysql 命令的同时也指定了用户名和密码。如果没有设置密码,则不需要输入“-p 密码”选项。
“mysql”和“-u”之间,以及“用户名”和“-p”之间,都需要输入半角空格。但是“-p”和“密码”之间不能有空格。注意,“-p”和“密码”之间有空格会发生错误。
在通过 MAMP 安装的 MySQL 中,初始设置的用户名和密码如下:
用户名 → root
密 码 → root
在命令提示符中输入以下命令,启动mysql监视器:
mysql -u root -proot
启动 MySQL 监视器后,如果显示出 Welcome to the MySQL monitor. Commands end with ; or \g. 之类的消息,就意味着成功连接了 MySQL。
2.1.5 当监视器无法启动时
如果没有显示“Welcome…”消息,发生错误,我们可以从以下几个方面来考虑。
密码错误
输入正确的密码,重新尝试连接。
没有设置密码,但指定了 -p 选项
省略 -p 的指定,直接执行 mysql –u root。
路径设置不正确
正确设置 MySQL 路径。另外,即使路径设置错误,在命令提示符中输入 cd C:\MAMP\bin\mysql\bin,移动所在文件夹,也能使用相同的命令启动 MySQL 监视器。
“-p”和“密码”之间有空格
二者中间不要输入空格。
各单词之间的空格是全角空格
各单词之间的空格必须是半角空格。
-p、root、-u 变成了大写字母
再次确认是否所有的字母都是小写字母。
2.1.6 确认MYSQL中字符编码的设置情况
启动mysql监视器,输入status,然后按回车(Enter)键,就能看见使用的环境了。
2.1.7 退出MYSQL监视器
在提示符状态下,通过输入exit或者quit,然后按下回车键来退出mysql监视器。
exit(或者quit)
退出mysql监视器后会返回到命令提示符">"。退出命令提示符或者终端时也要执行exit。
exit(或者直接关闭窗口)
2.1.8 安全的输入密码
该方法可以避免通过命令历史盗取密码。
mysql 命令的选项
在启动 MySQL 监视器的时候,输入了 mysql –u root –proot, 这个命令其实就是在 mysql 命令的基础上添加了 -u 和 -p 两个选项。像这样通过给命令添加选项,就可以指定命令完成设置的各种处理了。
给 mysql 命令设置选项的方法主要有以下两种。
① 给“-”指定选项名,然后加上设置的值
例:-u root(在这种情况下,选项名只占 1 个字符)
② 在“-- 选项名 =”后加上设置的值
例:mysql --user=root --password=root
例如,在指定字符编码启动 MySQL 监视器的情况下,可以写成 mysql –u root –proot -–default-character-set=gbk。
2.1.9 修改root用户的密码
启动mysql监视器,把下面的命令输入到“>”(提示符)的后面,然后回车。
SET PASSWORD FOR root@localhost=PASSWORD('666666');
2.1.10 修改MAMP的设置
在修改了 root 用户密码的情况下,如果不修改 MAMP 的设置文件内容,就无法退出和启动MySQL 服务器。打开C:\MAMP 文件夹中的 MAMP.exe.config 文件修改文件内容(把root改为666666)。保存文件后,在 MAMP 画面上点击“Stop Servers”,然后确认 Apache 和 MySQL 是否已经正常停止。再次点击“Start Servers”,Apache 和 MySQL 就会启动起来。
2.1.11 修改phpmyadmin的设置
修改了mysql管理员root的密码后,如果要使用phpmyadmin,就需要修改phpmyadmin的设置文件。打开C:\MAMP\bin\phpMyAdmin 文件夹中的 config.inc.php 文件,进行以下修改(把root改为666666):
0x03 创建数据库
3.1 创建数据库
创建数据库语法:
CREATE DATABASE 数据库名;
3.2 确认数据库
SHOW命令用于确认数据库的信息。大部分信息能通过SHOW命令显示出来。
SHOW DATABASES;
3.3 指定数据库
使用use命令指定数据库:
USE 数据库名;
显示当前使用的数据库:
SELECT DATABASE();
3.4 创建表db1
3.4.1 列和字段
字段:构成表的项目。
列:构成记录的各项目数据。
3.4.2 数据类型
数据类型:列中保存的数据的种类。
3.4.3 创建表
创建CREATE TABLE命令创建表。在()内使用空格分开列名和数据类型,各个列之间使用","分割。
CREATE TABLE 表名(列名1 数据类型1,列名2 数据类型2...);
3.5 显示所有的表
显示数据库中所有表时需要使用SHOW TABLES命令。
SHOW TABLES;
3.5.1 指定字符编码创建表
在 MySQL 中输入字符到表中时,会因为各种原因出现字符乱码的情况。这时有一个方法可以解决这个问题,那就是指定字符编码创建表。例如在指定 UTF-8 创建表时,在“CREATE TABLE …”的命令中加上 CHARSET=utf8 选项。如果无论如何都解决不了字符乱码的问题,不妨尝试一下下面这种方法。
CREATE TABLE tb1(empid VARCHAR(10),name VARCHAR(10),age INT) CHARSET=utf8;
3.6 确认表的列结构
用于显示表的列结构命令是DESC或者DESCRIBE。
DESC 表名;
Null 表示“允许不输入任何值”,Default 表示“如果什么值都不输入就用这个值”。Field 表示列名,Type 表示数据类型。
3.7 插入数据
插入数据语句:
INSERT INTO 表名 VALUES(数据1,数据2...);
指定列名插入:
INSERT INTO 表名 (列名1,列名2...) VALUES(数据1,数据2...);
一次性输入:
INSERT INTO 表名(列名1,列名2...) VALUES(数据1,数据2...),(数据1,数据2...),(数据1,数据2...)...;
3.8 显示数据
使用SELECT命令能够让列的数据显示出来:
SELECT 列名1,列名2...FROM 表名;
使用“*”(星号)会方便很多:
SELECT * FROM 表名;
0x04 数据类型和数据输入
4.1 什么是数据类型
数据库的表中只能输入各个列指定格式的数据。例如,指定为“数值类型”的列中不能输入字符等数据。指定为“日期类型”的列中,只能输入日期数据。这种数据的格式称为数据类型。
4.2 数值类型
常用数值类型:
4.3 字符串类型
常用字符串类型:
4.4 日期和时间类型
常用日期和时间类型:
0x05 修改表
5.1 ALTER TABLE命令
使用ALTER TABLE命令修改列的结构。根据需要修改的类型,可以像以下这样使用带有MODIFY、ADD、CHANGE、DROP的语句。
当修改列的定义时:ALTER TABLE … MODIFY …
当添加列时:ALTER TABLE … ADD …
当修改列名和定义时:ALTER TABLE … CHANGE …
当删除列时:ALTER TABLE … DROP …
5.2 修改列的数据类型
修改列的数据类型:
ALTER TABLE 表名 MODIFY 列名 数据类型;
5.3 添加列
将新建的列添加到最后的位置:
ALTER TABLE tb1 add birth datetime;
5.4 修改列的位置
5.4.1 把列添加到最前面
使用FIRST能够把列添加到最前面:
ALTER TABLE tb1 birth DATETIME FIRST;
5.4.2 把列添加到任意位置
使用AFTER能够把列添加到指定的位置:
ALTER TABLE tb1 birth DATETIME AFTER empid;
5.4.3 修改列的顺序
alter table tb1 modify birth datetime first;
5.5 修改列名和数据类型
修改列的数据类型或者位置的同时也修改列名:
ALTER TABLE 表名 CHANGE 修改前的列名 修改后的列名 修改后的数据类型;
5.6 删除列
在sql中,不仅仅是列,对数据库和表执行删除操作时也会使用drop命令:
ALTER TABLE 表名 DROP 列名;
5.7 设置主键
唯一:“只会确定一个”的独一无二的状态。
主键:在多条记录中用于确定一条记录时使用的标识符。
主键特征:
没有重复的值
不允许输入空值(noll)
在创建表的时候设置主键:
CREATE TABLE 表名(列名 数据类型 PRIMARY KEY ...);
5.7.1 创建主键
5.7.2 确认主键
5.7.3 设置唯一键
5.8 使列具有自动连接编号功能
数据类型为INT等整数类型
加上AUTO_INCREMENT
设置PRIMARY KEY,使列具有唯一性
5.9 设置连续编号的初始值
初始化AUTO_INCREMENT的值:
ALTER TABLE 表名 AUTO_INCREMENT=1;
5.10 设置列的默认值
设置列的默认值:
ALTER TABLE 表名(列名 数据类型 DEFAULT 默认值...);
5.11 创建索引
创建索引:
CREATE INDEX 索引名 ON 表名(列名);
确认索引:
SHOW INDEX FROM 表名;
查看索引:
SHOW INDEX FROM 表名 \G
删除索引:
DROP INDEX 索引名 ON 表名;
0x06 复制、删除表和记录
6.1 复制表的列结构和记录
三种复制方法:
复制表的列结构和记录
仅复制表的列结构
仅复制记录
复制表的列结构和记录:
CREATE TABLE 新表名 SELECT * FROM 元素名;
仅复制表的列结构:
CREATE TABLE 新表名 LIKE 元素名;
复制其他表的记录:
INSERT INTO 表名 SELECT * FROM 元素名;
选择某一列进行复制:
6.2 删除表
使用DROP命令删除表:
DROP TABLE 表名;
当目标表存在时将其删除:
一般来说,在目标表不存在的情况下执行DROP命令会发生错误,但如果加上了IF EXISTS就能够抑制错误的发生。在DROP TABLE的后面加上IF EXISTS ,就表示如果表存在就将其删除:
DROP TABLE IF EXISTS 表名;
6.3 删除数据库
删除数据库格式:
DROP DATABASE 数据库名;
6.4 删除所有记录
删除所有记录格式:
DELETE FROM 表名;
0x07 使用各种条件进行提取
7.1 设计列的显示内容并执行SELECT
7.1.1 改变列的显示顺序
7.1.2 使用别名
指定别名:
SELECT 列名 AS 别名 FROM 表名;
7.2 使用列值进行计算并显示
算术运算符:
7.3 使用函数进行计算
传入数据后,函数会执行指定的处理并返回结果。例如,AVG 这个函数会返回传入数据的平均值。函数名的后面要加上 (),() 里面是需要处理的数据。放在 () 中的数据称为参数。
显示平均值
SELECT AVG(列名) FROM 表;
显示总和
SELECT SUM(列名) FROM 表;
显示个数
SELECT COUNT(列名) FROM 表;
返回圆周率
SELECT PI();
显示mysql版本
SELECT VERSION();
显示当前使用的数据库
SELECT DATABASE();
显示当前用户
SELECT USER();
显示由参数指定的字符的字符编码
SELECT CHARSET('这个字符');
7.4 字符串操作中的常用函数
连接字符串
SELECT CONCAT(empid,name,'先生') FROM tb1;
从右取出:RIGHT函数
SELECT RIGHT(empid,2) FROM tb1;
从左取出:LEFT函数
SELECT LEFT(empid,2) FROM tb1;
从第x个字符开始截取X个字符:SUBSTRING 函数
SELECT SUBSTRING(empid,2,3) FROM tb1;
重复显示:REPEAT函数
SELECT REPEAT('.',age) FROM tb1;
反转显示:REVERSE函数
SELECT REVERSE(name) FROM tb1;
7.5 日期和时间函数
now是用于返回当前日期和时间的函数。
select now()
7.6 设置条件进行显示
7.6.1 确定记录数并显示
限制要显示的记录数:
SELECT 列名 FROM 表名 LIMIT 显示的记录数;
7.6.2 使用WHERE提取纪录
仅显示符合条件的记录:
SELECT 列名 FROM 表名 WHERE 条件;
7.6.3 比较运算符
常用的比较运算符:
列sales的值小于50
SELECT * FROM tb WHERE sales<50;
列month的值不等于4
SELECT * FROM tb WHERE month<>4;
列sales的值在50到100之间(大于等于50、小于等于100)
SELECT * FROM tb WHERE sales BETWEEN 50 AND 100;
列sales到值不在50 到200之间(小于50或大于200)
SELECT * FROM tb WHERE sales NOT BETWEEN 50 AND 200;
列month的值等于5或者6
SELECT * FROM tb WHERE month IN(5,6);
7.6.4 使用字符串作为条件
SELECT * FROM tb WHERE empid='A101';
LIKE:模糊查询
SELECT * FROM tb WHERE empid LIKE 'A101';
字符串的通配符及其使用示例
SELECT * FROM tb WHERE empid LIKE '%1';
提取不包含指定字符的记录
SELECT * FROM tb1 WHERE name NOT LIKE '佐%';
7.6.5 使用NULL作为条件
INSERT INTO tb1 (name) VALUES('仅仅是姓名');
当列值为NULL时
SELECT * FROM tb1 WHERE age IS NULL;
当值为非NULL时
SELECT * FROM tb1 WHERE age IS NOT NULL;
7.7 指定多个条件进行选择
7.7.1 使用AND
SELECT * FROM tb WHERE sales>=50 AND sales<=100;
7.7.2 使用OR
7.7.4 使用多个AND或OR
当and 和or 混合使用时,会优先处理and
SELECT * FROM tb WHERE empid LIKE '%1' AND month=4 OR sales>=200;
7.7.4 使用CASE WHEN
根据条件改变并显示值:
CASE WHEN 条件1 THEN 显示的值 WHEN 条件2 THEN 显示的值 WHEN 条件3 THEN 显示的值 ... ELSE 不满足所有条件时的值 END
7.8 排序
7.8.1 按升序排序显示记录
按升序显示:
SELECT 列名 FROM 表名 ORDER BY 作为键的列 asc(升序);
7.8.2 按降序排列并显示
按降序显示:
SELECT 列名 FROM 表名 ORDER BY 作为键的列 desc(降序)
7.8.3 指定记录的显示范围
指定范围并显示:
SELECT 列名 FROM 表名 LIMIT 显示的记录数 OFFSET 开始显示记录的移位数;
7.9 分组显示
SELECT 列名 FROM 表名 GROUP BY 用于分组的列名;
计算各组的记录数
显示各组的总和以及平均值
7.10 设置条件分组显示
7.10.1 按组处理
分组并设置记录的提取条件:
SELECT 统计列 FROM 表名 GROUP BY 分组列 HAVING 条件;
提取纪录后的分组
分组后排序
分组方法总结
1、提取纪录后分组。
2、分组后提取纪录。
0x08 编辑数据
8.1 瞬间更新列中所有的记录
8.2 排序后复制
8.3 删除所有记录
DELETE FROM 表名;
8.4 删除指定的记录
DELETE FROM 表名 WHERE 条件;
0x09 使用多个表
9.1 显示多个表的记录
将两个表的记录合并起来显示(换行+缩进):
换行:
SELECT 列名1 FROM 表名1 UNION SELECT 列名2 FROM 表名2;
缩进:
SELECT 列名1 FROM 表名1 UNION SELECT 列名2FROM 表名2;
按条件合并多条提取结果进行显示
合并显示多条提取结果(允许重复)
9.2 连接多个表并显示(内连接)
连接两个表:
SELECT 列名 FROM 表1 JOIN 要连接多表2 ON 表1的列=表2的列;
选择列进行显示
给表添加别名
表名 AS 别名 SELECT * FROM tb AS x;
使用USING使ON的部分更容易阅读
通过WHERE设置条件从连接表中提取记录
提取多个表中的记录
SELECT ~ FROM 表名1 JOIN 表名2 连接条件 JOIN 表名3 连接条件 ...
9.3 显示多个表的所有记录(外连接)
9.3.1 外连接的种类
左外连接(LEFT JOIN)
右外连接(RIGHT JOIN)
9.3.2 使用左外连接
左外连接:
SELECT 列名 FROM 表1 LEFT JOIN 要连接的表2 ON 表1的列=表2的列;
9.3.3 使用右外连接
右外连接:
SELECT 列名 FROM 表1 RIGHT JOIN 要连接的表2ON 表1的列=表2的列;
9.4 自连接
自连接格式:
SELECT 列名 FROM 表名 AS 别名1 JOIN 表名 AS 别名2;
自连接的验证
9.5 从SELECT的记录中SELECT(子查询)
显示最大的值
提取大于等于平均值的记录
9.6 使用IN(返回列的子查询)
子查询的语句:
SELECT 显示的列 FROM 表名 WHERE 列名 IN (通过子查询SELECT语句提取的列);
使用EXISTS,仅以存在的记录为对象
NOT EXISTS
排序的技巧
0x10 总结
由于mysql基础的知识点很多、篇幅又过长,暂时就写到这里。学习mysql主要抓住两点:一是理论知识的理解,一是实际操作。只有两者结合起来,才能真正的理解。
0x11 了解更多安全知识
欢迎关注我们的安全公众号,学习更多安全知识!!!
欢迎关注我们的安全公众号,学习更多安全知识!!!
欢迎关注我们的安全公众号,学习更多安全知识!!!