本周重点
①Linux防火墙iptables
②shell编程
1、变量、条件判断、for循环、函数调用；
2、SED和AWK文本编辑的应用；
③DOCKER容器的应用
④MySQL数据库
1、MySQL的基本概念
2、MySQL数据库的操作（新建、操作、查询、控制）
3、MySQL数据库B+树（索引）
本周主要内容
DAY1 Linux防火墙iptables
①iptables概念
基于5元组的包过滤防火墙。
②iptables安装配置
关闭firewalld
#systemctl一般是用于关闭或者开启服务用的。 systemctl stop firewalld systemctl disable firewalld
下载iptables的service
yum install iptables-services
启动iptables
#启动iptables systemctl start iptables #查看iptables的状态 systemctl status iptables #使iptables能够开机启动 systemctl enable iptables
③iptables四表五链
四表
五链
-
Chain PREROUTING pre前，routing 路由，路由（转发）前 规则
-
Chain INPUT 入站流量规则
-
Chain FORWARD 转发的时候规则
-
Chain OUTPUT 出站流量的规则
-
Chain POSTROUTINGPOST后，路由转发后的规则
● 入站：PREROUTING—>INPUT ● 出站：OUTPUT —> POSTROUTING ● 转发入站：PREROUTING—>FORWARD ● 转发出站：FORWARD —> POSTROUTING
④iptables应用例子
iptables例1：
查询iptables： iptables -nL iptables -t filter -nL 不允许所有的机器进行ping的操作： iptables -t filter -I INPUT -p icmp -j REJECT #代表拒绝但是会给回复 iptables -t filter -I INPUT -p icmp -j DROP #代表拒绝并且不给任何回复
INPUT链分析：
Chain INPUT (policy ACCEPT) target prot opt source destination ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED #RELATED代表此次报文和前面又关系（三次握手中发的报文），ESTABLISHED 代表在建立好的连接的基础上发的报文（三次握手之后发送的报文） ACCEPT icmp -- 0.0.0.0/0 0.0.0.0/0 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:22 #代表SSH的协议，因为远程连接是通过ssh协议。 REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited #如果要告诉对方不能通过，是通过icmp协议发送的拒绝信息。
iptables语法：
iptables [-t 表名] [-I 链名] [-p 协议] [-j 动作] 动作：REJECT DROP ACCEPT
iptables例2：
#删除 [root@localhost ~]# iptables -t filter -D INPUT 1 #1代表的是编号，从上往下第一条是1 #查看端口命令（查看80端口是否开启）： netstat -anlp(自己可以查看使用手册) #开启80端口 yum install -y httpd（-y可以省略） systemctl start httpd（开启http服务） netstat -anpt（查看80端口是否开启） 开启之后测试是否可以通过80端口访问，如果不能，需要配置一条iptables的策略： [root@localhost ~]# iptables -t filter -I INPUT -p tcp --dport 80 -j ACCEPT # --dport：代表的是把该选项名称全部写出来，因为-d可能会存在歧义（--dport, --dip）；
iptables例3-按接口进行过滤：
Chain INPUT (policy ACCEPT) target prot opt source destination ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED ACCEPT icmp -- 0.0.0.0/0 0.0.0.0/0 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:22 REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited 按照默认的匹配规则，则无法进行http访问； 添加一条规则：iptables -I INPUT -j ACCEPT，则可以进行http访问 target prot opt source destination ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED ACCEPT icmp -- 0.0.0.0/0 0.0.0.0/0 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:22 REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited 两个ACCEPT all是不一样的，一个代表的是所有的网卡，一个代表的是只有lo lo：loopback，本地环回接口。 #按照网卡进行过滤 iptables -I INPUT -i ens33 -j ACCEPT #持久化文件的地址 cat /etc/sysconfig/iptables
iptables例4
开放80, 8080端口 iptables -I INPUT -p tcp -m multiport --dport 80,8080 -j ACCEPT
iptable例5-icmp来回测试
#只允许主机ping其它的机器，不允许其它的机器ping主机 [root@localhost ~]# iptables -I INPUT -p icmp --icmp-type ECHO-Request -j REJECT [root@localhost ~]# iptables -I INPUT -p icmp --icmp-type ECHO-Reply -j ACCEPT
iptable例6-按时间冻结ip地址
#按照时间执行定时任务； #下载安装at yum install at #拒绝访问80端口 iptables -I INPUT -p tcp --dport 80 -j REJECT 开启atd服务 systemctl start atd 开启定时任务（什么时候执行什么内容） [root@localhost ~]# at now +2 minutes #从当前时间开始之后的两分钟 at> iptables -I INPUT -p tcp --dport 80 -j ACCEPT #按回车 at> <EOT> #ctrl + d,代表保存
⑤iptables常规应用
-
管理选项
| 类别 | 选项 | 用途 | 示例 |
|---|---|---|---|
| 添加新的规则 | -A | 在链的末尾追加一条规则 | iptables -t filter -A INPUT -p tcp -j ACCEPT |
-I | 在链的开头（或指定序号）插入一条规则 | iptables -I INPUT -p udp -j ACCEPT | |
| 查看规则列表 | -L | 列出所有的规则条目 | iptables [-t filter] -L |
-n | 以数字形式显示地址、端口等信息 | iptables [-t filter] -nL | |
-v | 以更详细的方式显示规则信息，in和out的网卡信息 | iptables [-t filter] -vL | |
--line-numbers | 查看规则时，显示规则的序号 | iptables [-t filter] -nL —line-numbers | |
| 删除、清空规则 | -D | 删除链内指定序号（或内容）的一条规则 | iptables -D INPUT 2 |
-F | 清空所有的规则 | iptables [-t filter] -F OUTPUT | |
| 设置默认策略 | -P | 为指定的链设置如果都匹配失败后的规则 | iptables -P OUTPUT DROP |
| 修改默认策略 | -R | 可以修改目标规则链 | iptables -t filter -R INPUT 2 -p icmp -j REJECT |
NAT地址转换
内访外（1.2.3.4访问 101.37.65.91），需要用到NAT的转换（内—->外），SNAT(source)源地址转换；
配置步骤：
1. 准备两台centos的虚拟机； 2. 要在iptables里面添加网卡（在vmware的虚拟机的设置里添加网络设备，添加好了ip addr查看，应该会多一张网卡信息，ens36）； 3. 最好是把（vment2）ip地址设置成固定的，原来是dhcp，现在要改成static，并且在后面添加相应的ip地址和子网掩码等信息（自己查） 4. 测试：两台centos的相互ping是不是能ping通； 5. 测试：iptables是否可以访问外网； 6. 开启转发功能：详细参考【配置转发】 7. 配置允许转发： iptables -I FORWARD -j ACCEPT（转发的都要放行） 8. 配置NAT的转换：iptables -t nat -I POSTROUTING -p tcp -s 1.2.3.4 -o ens33 -j SNAT --to 192.168.100.15 9. 通过1.2.3.4测试是否能够访问外网：curl -v --insecure https://101.37.65.91
配置转发：
6: 配转发 第一步：检查防火墙是否具有转发功能： sysctl net.ipv4.ip_forward ， 如果响应结果是 net.ipv4.ip_forward=1 表示有转发，否则没有开启 第二步：开启的方式，vi /etc/sysctl.conf 增加一行代码：net.ipv4.ip_forward=1 ，然后执行 sysctl -p /etc/sysctl.conf 激活配置
DNAT地址转换：
后面讲。
DAY2 shell编程
Linux的一个特殊的应用，文件是以.sh结尾。shell是用C语言开发的，是解释型语言。
主要是做辅助的。
目标：
用自己写的shell根据网段扫描哪些ip是可以连接的。
①shell变量
yourname="小黑" echo $yourname #单引号和双引号的区别，单引号是不能直接解析变量的 name="xiaoming" echo "$name's age is 18" echo '$name age is 18' echo `$name age is 18` : 【`】是数字1左边的按键，代表是先执行【``】里面的内容，再执行外面的内容； 变量的接收方式： read -p "input your password:" password #代表接收键盘输入的字符串，并把输入的内容赋给password $echo password
变量的运算
| 运算符 | 说明 |
|---|---|
| + | |
| - | |
\* | 乘法需要加上转义符 |
| / | 除法 |
| % | 取模 |
运算：
#expr执行算数运算，用法expr [变量1] 运算符 [变量2] sum = `expr $x + $y` sum=$(expr $x + $y) #用【``】和用【$()】作用是一样的 可以通过$1，$2,$3接收变量： sum=`expr $1 + $2 + $3`：代表对第一、二、三个参数进行求和。
练习：
输入x,y,z 输出：(x+y)*z 输入x,y,z(试一试z=0的时候，会出现什么东西) 输出：(x+y)/z
判断：
#文件或者目录是否存在 [root@localhost os]# [ -e /home/os/111111.txt ] [root@localhost os]# echo $? #文件是否存在（正常文件，不是文件夹） [root@localhost os]# [ -f /home/os/111111.txt ] [root@localhost os]# echo $? #目录是否存在 [root@localhost os]# [ -d /home/os/pip ] #【&】和【&&】的区分 &：与运算符 &&：判断前后的条件是否都为真【true】 #【|】和【||】的区分 |：或运算符 ||：判断前后条件是否存在一个为【true】 [root@localhost shell_test]# [ -e /root/dassad ] && echo yes [root@localhost shell_test]# [ -e /root/dassad ] || echo yes 如果是前面为【真】&&后就会执行 如果是前面为【假】||后就会执行
②shell条件判断
-gt：大于(greater than) -lt: 小于(less than) -eq：等于(equal) -ge：大于或等于(greater equal) -le：小于或等于(less equal)
If else 结构
read -p "请输入一个数字" num if [ $num -gt 50 ] then echo "$num 大于50" else echo "$num 小于50" fi
练习：
输入一个数字，代表年纪；
18以下：违法；
18~25：1000；
25~45：800；
45~55：200；
>55：0；
#!/bin/bash read -p "输入一个年纪：" num if [ "$num" -lt 18 ]; then echo "违法" elif [ "$num" -ge 18 ] && [ "$num" -lt 25 ]; then echo "罚款1000" elif [ "$num" -ge 25 ] && [ "$num" -lt 45 ]; then echo "罚款800" elif [ "$num" -ge 45 ] && [ "$num" -lt 55 ]; then echo "罚款200" else echo "不罚款" fi
③for循环
#语法结构
for 变量名 in 变量范围
do
业务代码
done
for i in {1..100}
do
echo $i
done
#打印从1^2 到 100^2
for i in {1..100}
do
result=$(expr $i \* $i)
echo "$i * $i = $result"
done
#输入一个路径，判断里面的每一个【内容】是不是文件
files=`ls $1`
for file in $files
do
if [ -d $1/$file ]
then
echo "$1/$file 是个文件夹"
elif [ -e $1/$file ]&&[ -f $1/$file ]
then
echo "$1/$file 是个文件"
else
echo "$1/$file 文件有问题"
fi
done
#判断192.168.100这个网段内哪些ip地址是可以通的
segment=$1
for((i=1;i<255;i++))
do
ping -w 1 -c 1 $segment$i &> /dev/null
if [ $? -eq 0 ]
then
echo "存活的ip是：$segment$i"
fi
done
④函数（方法）
用函数可以使代码变得简介，可以减少代码的冗余；
function scan_host() {
segment=$1
for((i=1;i<255;i++))
do
ping -w 1 -c 1 $segment$i &> /dev/null
if [ $? -eq 0 ]
then
echo "存活的ip是：$segment$i"
fi
done
}
function scan_host_spec() {
// 代表可以根据具体的ip查看是否可以连同
}
function scan_host_both() {
// 对方输入的是一个ip地址，可以查看该ip地址是否能通；
// 如果该ip地址不能连同，就查看该ip地址所属的网段，网段默认按照24位子网掩码
}
read -p "请输入一个网段：" segement
scan_host $segement
⑤SED文本编辑
sed：stream editor，属于一种比较好用的文本编辑工具；
sed工作机制：
-
读取内容，从文件、管道、stdin读入一行内容；
-
执行命令：在缓冲区里执行；
-
显示：执行之后显示；
常用于文本过滤
cat /etc/passwd >> users
#打印4到8行
nl users|sed -n '4,8{p}'
奇数行的打印，结合n和p,打印一行跳一行；
nl users|sed -n '{p;n}'
偶数行的打印，结合n和p,打印一行跳一行；
nl users|sed -n '{n;p}'
从3行打印到最后一行
nl users|sed -n '3,${p}'
模糊匹配，用【/内容/】
nl users|sed -n '/ro/p'
查找包含【/】的，就需要转义
nl users|sed -n '/\/bin\/bash/p'
去掉注释，用!p
cat /etc/sysconfig/selinux|sed -n '/^#/!p'
⑥AWK文本编辑
实用性强一点。
例：
cat /etc/passwd|awk -F ":" '{print $1}'
-F ":" 代表按照【:】进行分割，分割成一个数组；
print $1，代表取数组的第1个元素。
处理逻辑
常用
awk -F ':' 'NR==3{print $1}' /etc/passwd
#或的关系，是并集
awk -F ':' 'NR==3||NR==6{print $1}' /etc/passwd
#输出3到6行
awk -F ':' 'NR==3,NR==6{print $1}' /etc/passwd
#输出偶数行，反之亦然
awk -F ':' 'NR%2==0{print $1}' /etc/passwd
awk -F ':' 'NR%2!=1{print $1}' /etc/passwd
#模糊匹配
awk -F ':' '/\/bin\/bash/{print $1}' /etc/passwd
DAY3 Docker容器的应用
①Docker的基本概念
现在很多项目都是用docker部署的，主要强调怎么使用；
简介
用GO语言开发的。
轻量级的一个容器，可以让我们能够快速的打包自己的应用并且移植到其它系统取。
Docker容器技术，依托其其它的操作系统存在(Linux)；
Docker的优势
快速安装；
多个版本共存；
快速分发；
重要的概念
把dock当作一个沙箱环境。
镜像：文件；
容器：进程；
和虚拟机的区别
VM是一个运行在宿主机上的完整的操作系统，VM运行的时候，需要用到较多的资源，CPU，内存，硬盘。。
Docker它主要依赖各种【依赖库】
②Docker安装
更新Linux的版本
查看版本：uname -r 更新: yum update
安装docker依赖的插件
yum install -y yum-utils device-mapper-persistent-data lvm2
安装Docker
yum install -y docker-1.13.1-162.git64e9980.el7.centos.x86_64
配置启动
systemctl enable docker systemctl start docker
删除docker
删除容器：docker rm 容器名（id) 删除镜像：docker rmi 镜像名（id） 删除有先后顺序，要先删容器，再删镜像；
③MYSQL的镜像安装
#安装mysql5.6的镜像 docker pull mysql:5.6 #查看docker的镜像 docker images
把镜像变成容器
docker run --name mysql -p 3308:3306 --privileged -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.6 docker ps -a
④tomcat的镜像安装
如果没有tomcat，服务器开发的人，需要自己写socket监听，随时处理客户端的请求，如果处理一个客户端的请求需要1s，100个用户同时访问，最后的那个用户需要等待100s的时间。所以需要线程池。前面处理的步骤基本上都一样，所以有一个专门的叫”WEB容器”来统一的处理和解析用户的请求，全世界用的最多的web容器就是tomcat。
Docker安装tomcat
安装tomcat镜像
docker pull daocloud.io/library/tomcat:8.5.15-jre8 docker images
运行镜像
docker run -d -p 8080:8080 --name tomcat -v /usr/local/tomcat/webapps:/usr/local/tomcat/webapps --privileged daocloud.io/library/tomcat:8.5.15-jre8
挂载
上传war包
E:\download\WEB项目\woniusales>scp WoniuSales1.4.war root@192.168.100.183:/usr/local/tomcat/webapps
创建数据库（用Navicat Premium 16）
修改应用系统的连接信息
vi /usr/local/tomcat/webapps/WoniuSales1.4/WEB-INF/classes/db.properties
关闭防火墙(防止干扰)
systemctl stop firewalld systemctl stop iptables
重启tomcat容器
docker restart tomcat
DAY4 MySQL数据库-常用语句
①MySQL数据库的基本概念
数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。 每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。 MySQL 为关系型数据库(Relational Database Management System), 这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成。
关系型数据，hadoop（大数据的），neo4j（图数据库），redis（key—>value），Elastic search ；
关系型数据库：实时查询，关系复杂（GB）； hadoop：离线数据库，存储的数据量很大很大(TB)； neo4j：关系，侧重点是比较方便做图的计算； redis：查询速度要快，数据变化小，结构相对简单； ES: 倒排索引，适合非常大的文本检索（“苹果电脑真是好啊”）；
②MySQL数据库操作
了解了数据和数据库的概念，下一个问题就是如何科学地组织和存储数据，如何高效地获取和维护数据，完成这个任务的是一个系统软件 一数据库管理系统(英文缩写为DBMS，即DataBase Management System)。数据库管理系统是位于用户与操作系统之间的一层数据管理软件，其主要目标是使数据作为一种可管理的资源来处理，主要功能如下:
1.数据定义功能: DBMS提供数据定义语言(Data Definition Language，简称DDL)，用户通过它可以方便地对数据库中的数据对象进行定义。 2.数据操纵功能:DBMS也提供数据操作语言 (Data Manipulation Language，简称DML)，供用户实现对数据的基本操作，如插入、删除、修改。 3.数据查询功能: DBMS还提供数据查询语言(Data Query Language，简称DQL)，供用户实现对数据的查询操作. 4.数据控制功能:DBMS还提供数据控制语言(Data Control Language，简称DCL)，主要用于对数据库的用户、角色和权限进行管理和控制。
总体上，对数据的操作，基本上都支持CRUD: Create，Retrieve，Update，Delete
1、DDL数据定义语言
创建表格
CREATE TABLE dept ( id INT NOT NULL AUTO_INCREMENT, p_id INT, name VARCHAR(100) NOT NULL, PRIMARY KEY (id), FOREIGN KEY (p_id) REFERENCES dept(id) ); #CREATE TABLE dept (: 开始创建表的语句，表名为 dept。 #id INT NOT NULL AUTO_INCREMENT,: 定义一个名为 id 的列，数据类型为整数 (INT)，不能为空 (NOT NULL)，并且使用 AUTO_INCREMENT 属性，表示该列是一个自增长的主键，每次插入新记录时会自动递增。 #p_id INT,: 定义一个名为 p_id 的列，数据类型为整数 (INT)，表示部门的父部门ID。 #name VARCHAR(100) NOT NULL,: 定义一个名为 name 的列，数据类型为可变长度字符串 (VARCHAR)，最大长度为100，不能为空 (NOT NULL)，表示部门的名称。 #PRIMARY KEY (id): 定义 id 列为主键，确保表中每条记录都有唯一的标识符。 #FOREIGN KEY (p_id) REFERENCES dept(id) 表示 p_id 列是 dept 表中 id 列的外键。这意味着 p_id 列的值必须在 dept 表的 id 列中存在，确保了每个部门的父部门ID都是有效的 dept 表中的一个 id。
成都|重庆|西安|上海IT|Java|软件测试|Python开发培训学校|机构-蜗牛学苑 (woniuxy.com)
2、DML数据操作语言
新增
语法：insert into 表(字段1, 字段2, 字段3）values（值1，值2, 值3）
-- 插入根部门 INSERT INTO dept (p_id, name) VALUES (0, 'BOSS'); -- 插入第2级部门 INSERT INTO dept (p_id, name) VALUES (1, 'Dept A'); INSERT INTO dept (p_id, name) VALUES (1, 'Dept B');
修改
语法：update 表名 set 字段名1=’新的值1’, 字段名2=’新的值2‘ where 字段名=字段的值
update user set username='zjz', phone='158' where userid = 6 update user set realname='zjz3', phone='158' where username = 'zjq' and xxxx update user set realname='zjz3', phone='158' where username = 'zjq' or xxxx
删除
语法: delete from 表名 where 字段名=字段值
delete from user where userid=17
3、DQL数据查询语言(重点)
查询
语法：select {*|<字段名，3阶段sql注入会用到>} from 表名
[
[where <条件表达式>]
[group by]
[order by 字段 desc | asc]
[having 当作是where的补充，对聚合后的内容做条件过滤]
]
普通查询
select * from student;
查询具体的字段
select name from student
按顺序查询
#在分数是降序的前提下，对年龄进行降序处理。 select * from student order by score desc, age desc;
按照某些条件查询
#成绩大于100小于40 select * from student where age < 100 and age > 40 #查询年龄大于40并且分数小于500 select * from student where age > 40 and score <=500 select name, score from student where class_id = 4 select name, score from student where name <> '热dog' and class_id=2
分组和聚合
#统计student这张表的记录数 select count(*) from student #统计每个班的人数， 【as】是取别名 select class_id, count(*) as num from student group by class_id #统计班级的总分 select class_id, sum(score) from student group by class_id #统计班级的平均分 select class_id, avg(score) from student group by class_id #计算最高分，聚合函数是把多条记录变成1条，max和min都能会存在多条一样的，这个时候会选择其中的1条 select name, class_id, max(score) from student group by class_id #查询3班和4班的平均分，也可以加在having后面 select class_id, avg(score) from student where class_id=3 or class_id=4 group by class_id #查询平均分大于400的班级 select class_id, avg(score) from student group by class_id having avg(score) > 400 #指定表 select student.`name`, student.class_id, max(student.score) from student group by class_id
模糊匹配
#%分号代表匹配任意的字符； select * from student where name like '%g%'
范围查询
limit n,m； 代表从n+1条开始，查询后面的m条 一页10行，m=10； 第5页数据，n=(5-1)*m, m =10 #先取4000010数据，再丢掉后面的4000000条数据，会导致性能很低，深度分页问题； select * from person_info limit 4000000,10
多表查询
内连接
#查询所有商品所属的用户； select * from user, goods where user.userid = goods.useridselect * from user inner join goods on user.userid = goods.userid
左连接
#每个用户拥有哪些商品 select * from user left join goods on user.userid = goods.userid
右连接
#每个用户拥有哪些商品 select * from goods right join user on user.userid = goods.userid
练习：
1. 查询每个用户拥有哪些商品，并且查询该商品的价格（totalprice）大于500的； 2. 查询每个用户拥有哪些商品，并且按照价格（totalprice）进行排序； 3. 查询每个用户拥有的商品的总的价格（totalprice进行求和）； 4. 查询每个用户的平均消费（costunitprice）； select user.*,sum(goods.totalprice) as total from user left join goods on user.userid = goods.userid where goods.totalprice > 500 GROUP BY user.userid having total > 15000
Union联合查询
#两张表得用户合在一起； select username from user where userid=1 union select customername from customer
函数(SQL注入可能会用到的)
select MID('www.woniuxy.com',5,8)
select SUBSTR('www.woniuxy.com' FROM 5 FOR 8)
select SUBSTRING('www.woniuxy.com' FROM 5 FOR 8)
select ORD('a')
select ASCII('a')
select CHAR(97)
select BIN(ORD('a'))
select LEFT('www.woniuxy.com', 10)
select RIGHT('www.woniuxy.com', 10)
查询作业运用
#查父部门
SELECT
d.name AS dept,
p.name AS parent
FROM
dept d
LEFT JOIN
dept p ON d.p_id = p.id
WHERE
d.id = 15;
#查子部门
SELECT
d.name AS dept,
c.name AS children
FROM
dept d
LEFT JOIN
dept c ON d.id = c.p_id
WHERE
d.id = 5;
#查爷爷部门
SELECT
d.name AS dept,
gp.name AS grandpa
FROM
dept d
LEFT JOIN
dept p ON d.p_id = p.id
LEFT JOIN
dept gp ON p.p_id = gp.id
WHERE
d.id = 15;
#查父部门
SELECT * FROM dept WHERE id IN (SELECT p_id FROM dept WHERE id = 15);
#查子部门
select * from dept where id in (select id from dept where p_id = 5)
#查爷爷部门
select * from dept where id in (select p_id from dept where id in (select p_id from dept where id = 15))
4、DCL数据控制语言
DAY5 MySQL数据库 - B+树（索引）
未讲内容
三范式 事务（锁）
①B+树-基本组成
数据库的单条记录
组
多条（4~8条）数据形成一个组；
页（page=16K）
多个组会变成一页； 每页的大小最大不超过是16K 页目录：pageDirectory； 单个页里面查询的过程（2分查找法查询）： 查找：6 * mid = (4+0)/2 = 2 ---> slot[2].maxKey = 8 * 8 > 6 ---> mid = (2+0)/2 = 1 ---> slot[1].maxKey =4 * 4 < 6 --> 可以知道6是在slot[2]里面； * 在slot[2]里面进行线性查找； 简单归纳： 1. 通过2分查找定位到【组】； 2. 组内线性查找。
页和页
1. 页和页之间在内存里面不是连续的； 2. 如何查找？
不用索引查找
一个页里面：
-
二分+线性；
多个页：
-
线性查找，逐页的遍历；
②B+树-页的插入
一个页的数据
例子：
假如某个页内（页10）存在这3条数据(1, '4', 'u'),(3, '9', 'd'), (5, '3', 'y')，而且只能存放3条数据，新增一条数据与，(4, '4', 'a')
页的分裂
维持页的规范
根据页查找
目录项也是存放在页里面
页目录存放满之后
新的问题： 1. 目录项查找是不是又要跨页？
目录的目录
聚簇索引
聚簇索引：叶子节点存放的完整的数据；根据”主键“进行索引。
查找：ID=209，第一次查找的是第30页，第二次查找是在页20，然后再业内进行二分+线性查找；
主键默认就是聚簇索引；
二级索引
二级索引：叶子节点存放不是完整的数据，存放的是主键的值；
例子：
比如：一共又3列，c1是主键，c2是自己指定索引（二级索引），c3是一个普通的字段； 查找c1=12，根据聚簇索引查询的; 如果查找c2=7，会查出两条数据，分别是c1=8和c1=12，再把这两条数据拿到聚簇索引里面做查找；
回表
性能低：会有重复的数据（跨页查找，回表的次数会很多，意味着需要多次访问磁盘）； mysql会做优化，如果回表的次数太多（mysql会有一个阈值判断），会直接在树的叶子节点进行逐页的遍历；
联合索引
和二级索引一样的：在二级索引里面进行查找，然后再回表。
| age | name |
|---|---|
| 18 | zhangjize |
| 19 | tongmengchao |
| 20 | zhangjiaqinh |
| 20 | huangyunhui |
唯一性
| C1(key) | C2 | C3 |
|---|---|---|
| 1 | 1 | ‘u’ |
| 3 | 1 | ‘d’ |
| 5 | 1 | ‘y’ |
| 7 | 1 | ‘a’ |
插入一条记录：
(9, 1, 'w')二级索引不唯一，插入的时候不知道是插在前面还是插在后面，懵逼了。
归纳
从一条记录，变成组，多个组构成一个页（16KB），多个页构成B+树，每一层里面每一页的数据是链表结构；
主键是默认的索引，主键构造的索引叫聚簇索引，聚簇索引会存放完整的数据；
非主键构造的索引是二级索引，而今索引的叶子节点不会存放完整的数据，只会存放主键的值；
查找：
1. 根据主键查找，直接找聚簇索引，只会查找一次树。 2. 根据二级索引字段查找，会先在二级索引里查找到对应的主键，然后根据主键再到聚簇索引里回表（回表次数太多会直接在叶子节点里逐页的查找数据）； 3. 回表会增加IO。
③B+树-索引
索引的代价
空间；
1. 一组索引会重新建一棵树
性能；
每次查询的数据就有限，往缓存里面拿的数据很容易被替换掉，会引起内存抖动；
执行计划
MYSQL提供分析性能的工具：(EXPLAN)
性能强弱：const > ref > range > index > all
EXPLAIN select * from person_info where name='aAaCA' and birthday = '2008-03-13' AND phone_number='37410280060' EXPLAIN select * from person_info where country = 'bHQcZUlyjU' #也会用到索引，mysql会做优化，把后面(name)的条件放到前面来 EXPLAIN select * from person_info where birthday = '2008-03-13' and name='aAaCA' and phone_number='37410280060'
匹配最左边的列
#名称是最左边的（ref） EXPLAIN select * from person_info where name='aAaCA' #名称是最左边的（ref） EXPLAIN select * from person_info where name='aAaCA' and birthday = '1990-09-27' #生日不是最左边的(ALL) EXPLAIN select * from person_info where birthday = '1990-09-27' #名称是最左边的，但是后面的or条件(ALL) EXPLAIN select * from person_info where name='aAaCA' or country='China' #like是左边的(ALL) EXPLAIN select * from person_info where name like 'As%' EXPLAIN select * from person_info where name like '%As' EXPLAIN select * from person_info where name like '%As%'
范围匹配
#会用到索引 select * from person_info where name > 'Asa' and name < 'Asb' #不会，因为mysql会判断回表的次数会太多，导致性能下降还不如全表扫描； select * from person_info where name > 'A' and name < 'Z' EXPLAIN select * from person_info where name > 'Asa' and name < 'Barlow' AND birthday = '1980-01-01' EXPLAIN select * from person_info where name > 'Asa' and name < 'Barlow' OR birthday = '1980-01-01' EXPLAIN select * from person_info where name = 'Ashburn' And (birthday = '1980-01-01' OR birthday < '2000-12-31') and phone_number = '151010101010' EXPLAIN select * from person_info where name = 'Ashburn' And birthday = '1980-01-01' OR birthday < '2000-12-31' and phone_number = '151010101010' # 没有用到索引，是因为括号里面的内容取值范围会非常大，mysql会认为还不如全表扫描； EXPLAIN select * from person_info where (name > 'Asa' or name < 'Barlow') And birthday = '1980-01-01' And birthday < '2000-12-31' and phone_number = '151010101010' * MySQL可能会判断失误，导致用了索引性能反而更差； * 磁盘查找会占用时间，数据的IO也会占用很长的时间。
排序
EXPLAIN select * from person_info order by country EXPLAIN select * from person_info order by phone_number EXPLAIN select * from person_info order by name EXPLAIN select * from person_info order by name, phone_number #会用到索引，直接查找二级索引，因为聚簇索引的内容会多 EXPLAIN select name from person_info order by name EXPLAIN select * from person_info order by name limit 10; EXPLAIN select * from person_info order by name, birthday, phone_number limit 10 EXPLAIN select * from person_info order by birthday, name, phone_number limit 10 EXPLAIN select * from person_info where name = 'A' order by birthday, phone_number select * from person_info where name > 'Asa' and name < 'Asb' order by birthday, phone_number select * from person_info order by name , birthday limit 10 EXPLAIN select * from person_info order by name DESC, birthday DESC limit 10 #书的102页（TODO） EXPLAIN select * from person_info order by name ,birthday DESC limit 10 EXPLAIN select * from person_info order by name ,country limit 10 * 排序一般不会用到索引，因为会直接全表扫描；
分组
EXPLAIN select name, birthday, phone_number, count(*) from person_info group by name, birthday, phone_number TODO-是不是会用到索引，并解释原因 EXPLAIN select name, birthday, phone_number, count(*) from person_info group by birthday, name, phone_number
索引覆盖
#会用到索引，因为要查的字段都在二级索引里面 EXPLAIN select name, birthday, phone_number from person_info where name > 'A' and name < 'Z' EXPLAIN select name, birthday, phone_number from person_info order by name, birthday, phone_number TODO-是不是会用到索引，并解释原因 EXPLAIN select name, birthday, phone_number from person_info order by birthday，name, phone_number
④挑选索引
-
只为用于搜索、排序或者分组的列创建索引；
-
为【基数】大的列建立索引（性别：男和女）；
-
索引列尽可能的小（数据类型小）
索引小：1. 数据小；2. 单页能放的数据多；3. 缓存命中率高；4. CPU处理的数据小；
-
为字符串的前N位建立索引
yangkaiming yangan 如果名称最大的长度是20位，一般用前10位就可以比较出大小；
-
主键具有auto_increment的属性；
多表查询
多表查询一般数据多的表里面条件字段（on）要用索引；
如果两张表的数据都没有索引，就会形成笛卡尔积；
如果有一边数据没办法用索引，会有多少次？
大约会是 N*log(N)
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
