CentOS7部署k8s

环境

  • 下载了CentOS7.6，用vmware安装了三个虚拟机，选择GNOME Desktop环境进行安装，安装完成之后执行了yum upgrade进行更新。最开始是选择Minimal Install，不过无法进行复制粘贴，反复折腾没解决就换了一个不需要折腾的

  • 配置如下：

    • 1 号机(master): ip: 172.16.126.133, 2核2G
    • 2 号机(node-1): ip: 172.16.126.134, 2核2G
    • 3 号机(node-2): ip: 172.16.126.135, 2核2G

  • 为了方便编辑文件，我还安装了vim和增强插件spf13-vim(有可能需要翻墙)

  • 可以只先安装 1 号机，等装完环境，k8s，然后用vmware提供的创建完整克隆的方式创建出 2 号机和 3 号机

  • 如果主机启用了防火墙，需要配置开放k8s各个组件所需要的端口，这里为了简便，先关闭防火墙

    1
    2
    3
    4
    systemctl stop firewalld
    systemctl disable firewalld
    # 查看状态
    systemctl status firewalld

  • 试了试直接yum install kubernetes，版本太低，才1.5.2，写本文的时候，1.13正式版已经发布了，后文中使用官方提供的kubeadm来安装

  • k8s要求系统关闭swap: swapoff -a，使用free -m确认已经关闭了swap

  • 如果不需要复制粘贴功能了，可以将系统切换为文字模式(init 3)

    1
    2
    3
    4
    # 多用户状态
    systemctl set-default multi-user.target
    # 图形界面
    systemctl set-default graphical.target

安装 docker

  • 参考: Get Docker CE for CentOS

  • yum install -y yum-utils device-mapper-persistent-data lvm2

  • yum-config-manager --add-repo https://download.docker.com/linux/centos/docker-ce.repo

  • yum-config-manager --enable docker-ce-edge或者yum-config-manager --enable docker-ce-test，我选第一个

  • yum install docker-ce

  • 查看最新版本: yum list docker-ce --showduplicates | sort -r和我们安装的是否符合

  • 查看版本

    1
    2
    # docker --version
    Docker version 18.09.1, build 4c52b90

安装 kubectl, kubelet, kubeadm

  • 配置yum文件(使用国内源)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    # 阿里源 /etc/yum.repos.d/kubernetes.repo
    [kubernetes]
    name=Kubernetes
    baseurl=http://mirrors.aliyun.com/kubernetes/yum/repos/kubernetes-el7-x86_64/
    enabled=1
    gpgcheck=1
    repo_gpgcheck=1
    gpgkey=https://mirrors.aliyun.com/kubernetes/yum/doc/yum-key.gpg
        https://mirrors.aliyun.com/kubernetes/yum/doc/rpm-package-key.gpg

  • 安装：yum install -y kubectl kubelet kubeadm

  • 安装结果的版本显示

    1
    2
    3
    4
    5
    Installed:
    kubeadm.x86_64 0:1.13.2-0            kubectl.x86_64 0:1.13.2-0            kubelet.x86_64 0:1.13.2-0

    Dependency Installed:
    cri-tools.x86_64 0:1.12.0-0        kubernetes-cni.x86_64 0:0.6.0-0        socat.x86_64 0:1.7.3.2-2.el7

创建 2，3 号机

  • 至此，基本环境已经创建完毕。
  • 用vmware提供的创建完整克隆的方式创建出 2 号机和 3 号机

参考

CSS3学习笔记

0 兼容

  • 添加以下前缀
    • -webkit: safari，chrome 私有属性
    • -ms: ie>9 浏览器 私有属性
    • -moz: firefox 私有属性
    • -o: opera 私有属性
  • border-image: -webkit(Safari 5 以及更早的版本), -o
  • transform: -webkit
  • transition: -webkit(Safari，Chrome25 及更早的版本)
  • column-*: -webkit, -moz，Internet Explorer 9 以及更早的版本不支持多列属性

1 边框

1-1 border-radius添加圆角边框

1
2
3
4
div {
    border:2px solid;
    border-radius:25px;
}

1-2 box-shadow用于添加阴影

  • box-shadow: X轴，Y轴，R轴，阴影颜色，inset(可选项，内阴影，不填则默认外阴影)

    1
    2
    3
    div {
        box-shadow: 0.5rem 0 1rem red;
    }

1-3 border-image使用图片来创建边框

2 背景

2-1 background-size规定背景图片的尺寸

  • 在 CSS3 之前，背景图片的尺寸是由图片的实际尺寸决定的

2-2 background-clip和background-origin

  • 它们均有三个属性:

    • border-box: 背景是从边框开始绘制，但当背景是图片的时候，左边和上边没有绘制图片，下边和右边有
    • padding-box: 背景从边框内部开始绘制，不包含边框
    • content-box: 背景从内容部分绘制

  • 用处:

    • 二者都是处理背景的样式
    • background-origin只作用于背景图片

3 文本效果

3-1 text-shadow文本应用阴影

  • 语法: text-shadow: h-shadow v-shadow blur color
    • h-shadow: 必需，水平阴影的位置
    • v-shadow: 必需，垂直阴影的位置
    • blur: 可选，模糊的距离
    • color: 可选，阴影的颜色

3-2 word-wrap允许对不可分割的单词进行分割并换行

  • 语法: word-wrap: normal | break-word;

4 字体

  • 允许将字体文件存放到服务器上，会在用户需要的时候下载到用户的计算机上

    • Firefox、Chrome、Safari 以及 Opera 支持 .ttf (True Type Fonts) 和 .otf (OpenType Fonts) 类型的字体
    • Internet Explorer 9+ 支持新的 @font-face 规则，但是仅支持 .eot 类型的字体

  • 使用。首先必须自定义字体名称(比如testFont)，然后后使用

    1
    2
    3
    4
    5
    6
    7
    @font-face {
        font-family: testFont;
        src: url('https://xxx.ttf'), url('https://xxx.eot'); /* IE9+ */
    }
    body {
        font-family: testFont;
    }

  • 粗体需要另外包含，需要指定font-weight

    1
    2
    3
    4
    5
    6
    7
    8
    @font-face {
        font-family: testFont;
        src: url('https://xxx.bold.ttf'), url('https://xxx.bold.eot'); /* IE9+ */
        font-weight:bold;
    }
    body {
        font-family: testFont;
    }

  • 经过以上两步，如果文本需要显示粗体，浏览器就会使用粗体。通过这样的方式，可以为相同的字体设置许多@font-face规则

  • @font-face包含:

    • font-family: 必需，规定字体的名称
    • src: 必需，定义字体文件的 URL
    • font-stretch: 可选，定义如何拉伸字体，默认normal，包含
      • normal
      • condensed
      • ultra-condensed
      • extra-condensed
      • semi-condensed
      • expanded
      • semi-expanded
      • extra-expanded
      • ultra-expanded
    • font-style: 可选，定义字体的样式，默认normal，包含
      • normal
      • italic
      • oblique
    • font-weight: 可选，定义字体的粗细，默认是normal，包含
      • normal
      • bold
      • 100
      • 200
      • 300
      • 400
      • 500
      • 600
      • 700
      • 800
      • 900
    • unicode-range: 可选，定义字体支持的UNICODE字体范围，默认是U+0-10FFFF

5 2D 转换

  • transform 关键字用于 2D 转换
  • 包含:
    • translate(x,y) 定义 2D 转换，沿着 X 和 Y 轴移动元素
    • translateX(n) 定义 2D 转换，沿着 X 轴移动元素
    • translateY(n) 定义 2D 转换，沿着 Y 轴移动元素
    • scale(x,y) 定义 2D 缩放转换，改变元素的宽度和高度
    • scaleX(n) 定义 2D 缩放转换，改变元素的宽度
    • scaleY(n) 定义 2D 缩放转换，改变元素的高度
    • rotate(angle) 定义 2D 旋转，在参数中规定角度，单位: deg
    • skew(x-angle,y-angle) 定义 2D 倾斜转换，沿着 X 和 Y 轴
    • skewX(angle) 定义 2D 倾斜转换，沿着 X 轴
    • skewY(angle)

6 3D 转换

  • transform也可以用于 3D 转换
  • 包含:
    • matrix3d(n,n,n,n,n,n,n,n,n,n,n,n,n,n,n,n) 定义 3D 转换，使用 16 个值的 4x4 矩阵
    • translate3d(x,y,z) 定义 3D 转化
    • translateX(x) 定义 3D 转化，仅使用用于 X 轴的值
    • translateY(y) 定义 3D 转化，仅使用用于 Y 轴的值
    • translateZ(z) 定义 3D 转化，仅使用用于 Z 轴的值
    • scale3d(x,y,z) 定义 3D 缩放转换
    • scaleX(x) 定义 3D 缩放转换，通过给定一个 X 轴的值
    • scaleY(y) 定义 3D 缩放转换，通过给定一个 Y 轴的值
    • scaleZ(z) 定义 3D 缩放转换，通过给定一个 Z 轴的值
    • rotate3d(x,y,z,angle) 定义 3D 旋转
    • rotateX(angle) 定义沿 X 轴的 3D 旋转
    • rotateY(angle) 定义沿 Y 轴的 3D 旋转
    • rotateZ(angle) 定义沿 Z 轴的 3D 旋转
    • perspective(n) 定义 3D 转换元素的透视视图

7 过度

  • transition用于处理样式变化间的过渡

  • 以下示例中，鼠标放上去后，内容会在 2 秒后，在 4 秒内逐渐拉长，3 秒内颜色变成绿色

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    .test {
        text-align: center;
        width: 30%;
        margin: 0 auto;
        background-color: aqua;
        transition: width 4s, background-color 3s;
        transition-delay: 2s;
    }
    .test:hover {
        width: 50%;
        background-color: green;
    }

  • 属性

    • transition 简写属性，用于在一个属性中设置四个过渡属性
    • transition-property 规定应用过渡的 CSS 属性的名称
    • transition-duration 定义过渡效果花费的时间。默认是 0
    • transition-timing-function 规定过渡效果的时间曲线。默认是 “ease”
    • transition-delay 规定过渡效果何时开始。默认是 0

8 动画

  • (略)

9 多列

  • 能够创建多个列来对文本进行布局 - 就像报纸那样

  • 属性

    • column-count 规定元素应该被分隔的列数
    • column-fill 规定如何填充列
    • column-gap 规定列之间的间隔
    • column-rule 设置所有 column-rule-* 属性的简写属性
    • column-rule-color 规定列之间规则的颜色
    • column-rule-style 规定列之间规则的样式
    • column-rule-width 规定列之间规则的宽度
    • column-span 规定元素应该横跨的列数
    • column-width 规定列的宽度
    • columns 规定设置 column-width 和 column-count 的简写属性

  • 以下示例将内容分为三列，间隔 2rem，中间用红线隔开

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    .test {
        width: 30%;
        margin: 0 auto;

        -moz-column-count: 3;
        -webkit-column-count: 3;
        column-count: 3;

        -moz-column-gap: 2rem;
        -webkit-column-gap: 2rem;
        column-gap: 2rem;

        -moz-column-rule: 0.0625rem outset red;
        -webkit-column-rule: 0.0625rem outset red;
        column-rule: 0.0625rem outset red;
    }

CSS学习笔记

1 选择器

1-1 元素选择器

  • 文档的元素就是最基本的选择器，比如: body，p，h1

    1
    2
    body {font-size:100%;}
    p {text-align: center}

1-2 类选择器

  • 需要使用class配合使用

    1
    2
    3
    4
    5
    6
    .txt {
        text-align: center;
        color: aquamarine;
    }

    <p class="txt">这里使用了class="txt"</p>

  • 也可以配合元素选择器指定响应范围

    1
    2
    3
    4
    5
    6
    7
    // 指定了段落中的 class="txt" 才会变成红色
    p.txt {
        color: red;
    }

    <p class="txt">这里的文字会变成红色</p>
    <div class="txt">这里的文字不会变成红色</div>

  • 多个类选择器

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    .txt {
        color: red;
    }

    .txt2 {
        font-style: italic;
    }

    // class 值可以是一个列表，使用空格隔开
    <p class="txt txt2">这里的文字斜体，红色</p>

  • 多个类选择器相连，元素中需要同时包含这些类名

    1
    2
    3
    4
    5
    .txt.txt2 {
        color: red;
    }
    <p class="txt txt2">这里的文字是红色的</p>
    <p class="txt txt3">这里的文字 不 是红色的</p>

  • 多个类选择器中具有相同属性，以选择器的定义顺序，使用后定义的

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    .txt {
        color: red;
    }

    .txt2 {
        color: green;
    }

    // 无论 txt txt2 位置如何替换，该段落中的文字显示一直为绿色
    <p class="txt txt2">这里使用了class="txt txt2"</p>

    但如果类选择器定义的顺序换了，则会影响段落中文字的变换

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    .txt2 {
        color: green;
    }

    .txt {
        color: red;
    }

    // 虽然 txt 在前，但最终显示的红色，以选择器定义的顺序为准
    <p class="txt txt2">这里使用了class="txt txt2"</p>

1-3 ID 选择器

  • 使用#定义，需要配合id使用

    1
    2
    3
    4
    5
    #red {
        color: red;
    }

    <div id="red">这里的文字是红色的</div>

  • 注意点

    • ID在同一个HTML文档中，只能出现一次
    • ID和class不同，没有值列表

1-4 属性选择器

  • 选择具有某个/某些属性的元素[attribute]

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    // 匹配一个属性
    [data] {color: red}
    <span data="enen">这里是红色的</span>

    // 同时匹配多个属性
    [href][title] {color: green}
    <a href="#">这里是默认的颜色</a>
    <a href="#" title="enen">这里是绿色的</a>
    <p href="#" title="enen">这里也是绿色的</p>

    // 再加一个限制，只有链接才会变绿色
    a[href][title] {color: green}
    <a href="#" title="enen">这里是绿色的</a>
    <p href="#" title="enen">这里不会变成绿色</p>

  • 指定属性和值[attribute=value]

    1
    2
    3
    4
    5
    6
    [href="https://qsyx.top"] {
        color: red;
    }

    <a href="https://qsyx.top">这里会变成红色</a>
    <a href="https://google.com">这里不会变成红色</a>

  • 值中包含指定词汇[attribute~=value]

    1
    2
    3
    4
    5
    6
    7
        [data~="a"] {
        color: red;
    }

    <p data="a b">包含 a 这里会变成红色</p>
    <p data="a c">包含 a 这里会变成红色</p>
    <p data="d e">不包含 a 这里不会变成红色</p>

  • 以指定值开头的属性值的元素 [attribute|=value]，value必须是一个单词

    1
    2
    3
    4
    5
    6
    7
    8
    9
        [data~="a"] {
        color: red;
    }

    <p lang="en">lang="en" 红色</p>
    <p lang="en-zh">lang="en-zh" 红色</p>
    <p lang="en_zh">lang="en_zh" 默认黑色</p>
    <p lang="enZH">lang="enZH" 默认黑色</p>
    <p lang="zh">lang="zh" 默认黑色</p>

  • 匹配指定值开头的属性值的元素 [attribute^=value]

    1
    2
    3
    4
    5
    6
    7
    8
    9
        [data^="a"] {
        color: red;
    }

    <p lang="en">lang="en" 红色</p>
    <p lang="en-zh">lang="en-zh" 红色</p>
    <p lang="en_zh">lang="en_zh" 红色</p>
    <p lang="enZH">lang="enZH" 红色</p>
    <p lang="zh">lang="zh" 默认黑色</p>

  • 匹配属性值以指定值结尾的每个元素[attribute$=value]

  • 匹配属性值中包含指定值的每个元素[attribute*=value]

1-5 后代选择器

  • 比如我们打算把h1中的span都加上strong，这样h2或者p中的span不会收到影响

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    h1 span {
        color: red;
    }

    <h1>
      <em>em 不会变红</em>
      <span>span 变红</span>
    </h1>
    <h2>
      <em>em 不会变红</em>
      <span>span 不会变红</span>
    </h2>

  • 也可以通过类选择器加强选择

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    div.import span {
        color: red;
    }

    // 仅有包含了 import 的 div 中的 span 才会匹配成功
    <div class="import">
      <em>em 不会变红</em>
      <span>span 变红</span>
    </div>
    <div>
      <em>em 不会变红</em>
      <span>span 不会变红</span>
    </div>

  • 以上例子中，无论div与span隔了多远，都会作用到。如果想缩小范围，请用子元素选择器

    1
    2
    3
    4
    5
    div span {
        color: red;
    }

    <p><p><p><p><span>span 变红</span></p></p></p></p>

1-6 子元素选择器

  • 子元素使用子结合符>，子结合符两边可以包含空格

  • 子元素可以是类选择器，子选择器，元素选择器

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    div > span {
        color: red;
    }
    div > #hi {
        color: green;
    }
    div > .hi {
        color: blue;
    }

    <div>
      <span>span 1 变红</span>
      <p><span>span 2 不变红</span></p>
      <span>span 1 变红</span>
      <p id="hi">可以是 ID 选择器</p>
      <p class="hi">可以是 类 选择器</p>
    </div>

1-7 相邻兄弟选择器

  • 使用+和~表示兄弟选择器，但有些区别

  • +是相邻，紧挨着的

    1
    2
    3
    4
    5
    6
    7
    8
    h1 + p {
        color: red;
    }

    <h1>h1 + p 同一个父元素 body，不会变化，它紧挨着的兄弟才会受影响</h1>
    <p>h1 + p 同一个父元素 body，红色</p>
    <p>h1 + p 同一个父元素 body，但非相邻，不是红色</p>
    <h1><p>非同一个父元素，不是红色</p></h1>

  • ~相邻的所有指定元素

    1
    2
    3
    4
    5
    6
    7
    h1 ~ p {
        color: red;
    }

    <h1>h1 + p 同一个父元素 body，不会变化，它的兄弟们才会受影响</h1>
    <p>h1 + p 同一个父元素 body，红色</p>
    <p>h1 + p 同一个父元素 body，红色</p>

  • 一定是相邻的，以上示例中，如果h1与p中间隔了div，则匹配失败

1-8 伪类

  • 锚伪类

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    /* 未访问过的链接 */
    a:link {
        color: red;
    }
    /* 已访问过的链接 */
    a:visited {
        color: blueviolet;
    }
    /* 鼠标移动到链接上 */
    a:hover {
        color: green;
        font-size: 2rem;
    }
    /* 选定的链接 */
    a:active {
        background-color: greenyellow;
    }

    <a href="#">锚伪类</a>
    • 在CSS定义中，a:hover 必须被置于 a:link 和 a:visited 之后，才是有效的
    • 在CSS定义中，a:active 必须被置于 a:hover 之后，才是有效的

  • first-child伪类，标记在哪个元素就是哪个元素，而不是其的第一个子元素

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    // 匹配第一个p
    p:first-child {
        color: red;
    }

    <p>
        hi 1-1 红色
        <span>1-1 span 红色</span>
        <p>hi 1-2 非红色</p>
        <span>1-1 span 非红色</span>
    </p>
    <p>hi 2-1 非红色</p>

    // 所有p中的第一个span
    <p><span>1 红色</span> <span> 2 非红色</span></p>
    <p><span>1 红色</span> <span> 2 非红色</span></p>

  • lang伪类为不同的语言定义特殊的规则

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    q:lang(python) {
        quotes: "'" "'";
    }
    q:lang(cpp) {
        quotes: '"' '"';
    }

    <p><q lang="python">python</q></p>
    <p><q lang="cpp">c++</q></p>

    // 输出:
    // 'python'
    // "c++"

  • focus伪类在元素获得焦点时向元素添加样式，ie堪忧

    1
    2
    3
    4
    5
    6
    input:focus {
        background: green;
    }

    // 点击输入框，获得焦点时背景呈现绿色
    <input type="text" />

1-9 伪元素

  • first-line用于向文本的首行设置特殊样式

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    p::first-line {
        color: red;
    }

    <p>
      hi first line <br />
      second line
    </p>

    // `first-line`只能用于块级元素
    // 以下属性可以应用于`first-line`
    > font
    > color
    > background
    > word-spacing
    > letter-spacing
    > text-decoration
    > vertical-align
    > text-transform
    > line-height
    > clear

  • first-letter用于向文本的首字母设置特殊样式，中文首字

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    p::first-letter {
        color: red;
        font-size: xx-large;
    }

    <p>hi first line second line</p>

    // `first-letter`只能用于块级元素
    // 以下属性可以应用于`first-letter`
    > font
    > color
    > background
    > margin
    > padding
    > border
    > text-decoration
    > vertical-align (仅当 float 为 none 时)
    > text-transform
    > line-height
    > float
    > clear

  • :before, :after元素的内容之前/后插入新内容

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    // 在列表中插入红色的 |
    li {
        display: inline;
    }
    li:not(:last-child)::after {
        content: '|';
        color: red;
    }

    <ul>
      <li>C++</li>
      <li>Python</li>
      <li>PHP</li>
    </ul>

2 样式

2-1 背景

  • background-color: 背景色

  • background-image: 背景图像，body {background-image: url(http://xxcdn.xx.jpg)}

  • background-repeat: 背景重复，分别在repeat-x和repeat-y方向上重复, no-repeat则不允许在任何方向上平铺

  • background-position: 背景定位，可以使用关键字: top，bottom，left，right，center，不可超过两个关键字，默认为center。也可以使用百分比或者是具体的长度值，左上角为起始点

  • background-attachment: scroll 默认值，图片会随着页面滚动，fixed 背景图片不会一定

2-2 文本

  • text-indent: 缩进文本，p {text-indent: 1rem}，缩进值也可以为负数，也可以使用百分比

  • text-align: left，right，center，justify，其中, justify是两端文本对齐

  • word-space: 改变字的间隔，默认值为normal和0，可以为负数

  • letter-spacing: 改变字母间间隔

  • text-transform: 处理文本，uppercase 大写，lowercase 小写，capitalize 对每个单词的首字母大写

  • text-decoration: 文本装饰

    • underline: 下划线
    • overline: 顶端划线
    • line-through: 贯穿线
    • blink: 文本闪烁

  • white-space: 处理空白符

    • pre: 空白会被浏览器保留
    • nowrap: 文本不会换行，文本会在在同一行上继续，直到遇到
      标签为止
    • pre-wrap: 保留空白符序列，但是正常地进行换行
    • pre-line: 合并空白符序列，但是保留换行符

2-3 列表

  • list-style-type: 列表类型，常用类型如下

    • none: 无标记
    • disc: 默认，标记是实心圆
    • circle: 标记是空心圆
    • square: 标记是实心方块
    • decimal: 标记是数字
    • decimal-leading-zero: 0 开头的数字标记，(01, 02, 03 …)
    • lower-roman: 小写罗马数字(i, ii, iii, iv, v …)
    • upper-roman: 大写罗马数字(I, II, III, IV, V …)
    • lower-alpha: 小写英文字母(a, b, c, d, e …)
    • upper-alpha: 大写英文字母(A, B, C, D, E …)

  • list-type-image: 列表项图像，常用标志不够用，使用自定义图像，ul li {list-type-image: url(xxx.gif)}

2-4 表格

  • border-collapse: 设置是否把表格边框合并为单一的边框，separate 默认分离，collapse则合并

  • border-spacing: 设置分隔单元格边框的距离，一共两个参数: 水平，垂直；如果就一个参数，则水平和垂直都是用该值

  • caption-side: 设置表格标题的位置，top 顶部，默认值；bottom 底部

  • empty-cells: 设置是否显示表格中的空单元格，show 显示，默认值; hide 不在空单元格周围绘制边框

  • table-layout: 设置显示单元、行和列的算法，automatic 宽度由单元格内容设定；fixed 由表格宽度和列宽度设定

2-5 轮廓

  • outline-color: 设置颜色

  • outline-style: 设置样式

    • none: 默认，无轮廓
    • dotted: 定义点状的轮廓
    • dashed: 定义虚线轮廓
    • solid: 定义实线轮廓
    • double: 定义双线轮廓，双线的宽度等同于 outline-width 的值
    • groove: 定义 3D 凹槽轮廓，此效果取决于 outline-color 值
    • ridge: 定义 3D 凸槽轮廓，此效果取决于 outline-color 值
    • inset: 定义 3D 凹边轮廓，此效果取决于 outline-color 值
    • outset: 定义 3D 凸边轮廓，此效果取决于 outline-color 值

  • outline-width: 设置宽度

  • outline: 可按顺序设置color，style，width

3 框模型

3-1 概述

  • 从内到外依次是: 元素(element) -> 内边距(padding) -> 边框(border) -> 外边框(margin)

  • 内边距，边框，外边距默认值都是0

  • width和height是元素区域的宽度和高度，增加内边距和外边距不会影响这两个值

3-2 内边距

  • 在padding中，可以按照上，右，下，左的顺序设置各边的内边框，各边的可以使用不同的单位或者百分比: p {padding: 1px 2rem 3ex 40%}

  • 如果就只有一个参数，则上，右，下，左都使用该值

  • 也可以通过以下属性分别设置内边距

    • padding-top
    • padding-right
    • padding-bottom
    • padding-left

  • 百分比数值是基于父元素的width计算的，包括上，下内边距也是基于父元素的width计算的

3-3 边框

  • 样式

    • 可能的值

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      none	定义无边框
      hidden	与 "none" 相同，不过应用于表时除外，对于表，hidden 用于解决边框冲突
      dotted	定义点状边框，在大多数浏览器中呈现为实线
      dashed	定义虚线，在大多数浏览器中呈现为实线
      solid	定义实线
      double	定义双线，双线的宽度等于 border-width 的值
      groove	定义 3D 凹槽边框，其效果取决于 border-color 的值
      ridge	定义 3D 垄状边框，其效果取决于 border-color 的值
      inset	定义 3D inset 边框，其效果取决于 border-color 的值
      outset	定义 3D outset 边框，其效果取决于 border-color 的值
      inherit	规定应该从父元素继承边框样式

    • 在border中，可以使用border-style按照上，右，下，左的顺序设置各边框样式，#container {border-style: solid dotted dashed double}

    • 也可以通过以下属性分别设置边框样式

      • border-top-style
      • border-right-style
      • border-bottom-style
      • border-left-style

  • 宽度

    • 使用border-width按照上，右，下，左的顺序设置各边框宽度
    • 可以指定长度值，或者使用三个关键字之一: thin，medium(默认)，thick，但css并没有定义这三个关键字的具体宽度
    • 也可以分别设置边框宽度
      • border-top-width
      • border-right-width
      • border-bottom-width
      • border-left-width

  • 颜色

    • 使用border-color按照上，右，下，左的顺序设置各边框颜色
    • 也可以分别设置边框颜色
      • border-top-color
      • border-right-color
      • border-bottom-color
      • border-left-color

3-4 外边距

  • 外边距margin与内边距padding相似

4 定位

4-1 概述

  • 一切皆为框, div，h1，p等都称之为块级元素，span，strong为行内元素。可以使用display属性改变生成的框的内行，设置为block可以让行内元素，比如span表现的像块级元素一样

  • 有三种基本的定位机制: 普通流(默认)，浮动，绝对定位

  • position包含 4 个不同的定位

    • static: 默认值，没有定位。元素做为文档流的一部分
    • relative: 相对定位，比如left:20，会向左边添加 20px
    • absolute: 绝对定位，比如top:20，距离父元素顶部 20px。元素从文档流完全删除
    • fixed: 绝对定位，相对于浏览器窗口

4-2 绝对定位

  • 设置为绝对定位的元素从文档流移除了，所占用的空间完全关闭，比如以下示例中，div 3会占用div 2的位置，div 2会遮住大部分div 3。也可以通过设置z-index来控制层次

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    div {
        border-style: solid;
        width: 3rem;
        height: 3rem;
        color: white;
    }

    <div style="background-color: red">div 1</div>
    <div style="background-color: black; position: absolute; left: 1rem">div 2</div>
    <div style="background-color: lightgreen">div 3</div>

5 高级

5-1 对齐

  • 块元素是指占据全部可用宽度的元素，并且在其前后都会换行

  • 使用margin属性来水平居中对齐，可通过将左和右外边距设置为 “auto”，来对齐块元素，除非已经声明了 !DOCTYPE，否则使用 margin:auto 在 IE8 以及更早的版本中是无效的。把左和右外边距设置为auto，规定的是均等地分配可用的外边距，结果就是居中的元素。以下代码可以发现元素居中了

    1
    2
    3
    4
    5
    6
    7
    8
    .test {
        margin: 0 auto;
        width: 70%;
        border-style: solid;
        border-color: red;
    }

    <div class="test">hi, I am here.</div>

  • 使用position属性进行左和右对齐，使用绝对定位

    1
    2
    3
    4
    5
    6
    7
    8
    9
    .test {
        position: absolute;
        right: 0;
        width: 18.75rem;
        border-style: solid;
        border-color: red;
    }

    <div class="test">hi, I am here.</div>

  • 使用float属性进行左和右对齐，如float:left和float:right

5-2 尺寸

  • height 设置元素的高度
  • width 设置元素的宽度
  • line-height 设置行高
  • max-height 设置元素的最大高度
  • max-width 设置元素的最大宽度
  • min-height 设置元素的最小高度
  • min-width 设置元素的最小宽度

5-3 分类

  • clear，设置一个元素的侧面是否允许其他的浮动元素，

    • left 在左侧不允许浮动元素
    • right 在右侧不允许浮动元素
    • both 在左右两侧均不允许浮动元素
    • none 默认值。允许浮动元素出现在两侧

  • cursor，规定要显示的光标的类型（形状）

    • url 需使用的自定义光标的 URL，
    • default 默认光标（通常是一个箭头）
    • auto 默认。浏览器设置的光标
    • crosshair 光标呈现为十字线
    • pointer 光标呈现为指示链接的指针（一只手）
    • move 此光标指示某对象可被移动
    • e-resize 此光标指示矩形框的边缘可被向右（东）移动
    • ne-resize 此光标指示矩形框的边缘可被向上及向右移动（北/东）
    • nw-resize 此光标指示矩形框的边缘可被向上及向左移动（北/西）
    • n-resize 此光标指示矩形框的边缘可被向上（北）移动
    • se-resize 此光标指示矩形框的边缘可被向下及向右移动（南/东）
    • sw-resize 此光标指示矩形框的边缘可被向下及向左移动（南/西）
    • s-resize 此光标指示矩形框的边缘可被向下移动（南）
    • w-resize 此光标指示矩形框的边缘可被向左移动（西）
    • text 此光标指示文本
    • wait 此光标指示程序正忙（通常是一只表或沙漏）
    • help 此光标指示可用的帮助（通常是一个问号或一个气球）

深入浅出MySQL

之前先看了 MySQL 必知必会一书。这二者很多内容相同，所以本书只节选了部分内容来看。

1 索引的设计和使用

1 设计索引原则

  • 最适合索引的列是出现在WHERE/JOIN/ORDER BY/GROUP BY/DISTINCT中的列。而不是出现在 SELECT 中的列。

  • 使用唯一索引。索引的基数越大，效果越好。比如存放身份号码的列具有不同的值，很容易区分各行。而用来记录性别的列，只含有’M’和’F’，对这样的列进行索引，没多大用处。

  • 使用短索引。如果索引的值很长，那么查询的速度会受到影响。

  • 利用最左索引。假设建立了 user_id, user_name, status(按该顺序建立)复合索引， 实际上是创建了三个 MySQL 可利用的索引。

    1
    2
    3
    user_id, user_name, status
    user_id, user_name
    user_id

    只要在查询中指定了 user_id 的值，无论是否有 user_name 或者 status，MySQL 都可以使用这个索引。但是，如果不包含 user_id，只包含了 user_name 或 status，那么，MySQL 不能利用这个索引。

  • 限制索引的数目。并非索引越多越好。除了要占用额外的磁盘空间外，还会降低写操作的性能。在修改表的时候，索引必须进行更新，索引越多，所花的时间也越多。

  • 删除不再使用或者很少使用的索引。从而减少对表更新操作的影响。

2 小常识

  • 系统自动创建 primary key 的索引。
  • 系统自动创建 unique key 的索引。

3 BTRee 索引

  • MyISAM 和 InnoDB 默认创建的都是 BTRee 索引。
  • 当使用>,<,>=,<=,BETWEEN,!=,<>或者LIKE 'pattern'(pattern不以通配符开始)操作符时，都可以使用相关列上的索引。

2 SQL 中的安全问题

  • SQL 注入攻击

    1
    2
    -- 通过账号密码获取账号id, 如果存在，则登陆成功
    SELECT id FROM account WHERE user_name='admin' AND password='opds123456';

    但如果没有任何过滤，传入的user_name=admin'#，就会出现大问题。

    1
    SELECT id FROM account WHERE user_name='admin '# ' AND password='123456';

    因为#会将后面的句子注释。因此密码验证功能就丢失了。

    又如传入user_name=admin' OR 1=1

    1
    SELECT id FROM account WHERE user_name='admin' OR 1=1 AND password='123456';

  • 防范

    • 使用 PrepareStatement，预编译 sql 后，通过绑定参数来执行。

    • 对特殊字符进行转换

      1
      2
      3
      4
      // MySQL C API
      mysql_real_escape_string()
      // PHP
      mysql_real_escape_string()

    • 定义函数对输入进行校验。

3 常用 SQL 技巧

  • 使用 RAND()获取随随即行

    1
    SELECT * FROM user ORDER BY RAND() LIMIT 10;

4 SQL 优化过程

1 了解 SQL 执行频率

使用 SHOW [SESSION | GLOBAL] STATUSA 来获得服务器状态信息。
SESSION 表示当前连接，如果直接写 SHOW STATUS, 则默认是 SESSION。
GLOBAL 表示从数据库上次启动至今的统计结果。

1
SHOW GLOBAL STATUS LIKE 'Com_%';

在结果中可以看下

1
2
3
4
Com_select: 执行select操作次数
Com_insert: 执行insert操作次数，批量插入只累加1
Com_update: 执行update操作次数
Com_delete: 执行delete操作次数

通过以上信息，就可以了解到是以更新为主还是查询为主了。

2 EXPLAIN 分析

假设有一张 account 表存在 id(主键), phone 字段,且未对 phone 建立索引。

通过 id 查找:

1
EXPLAIN SELECT * FROM account WHERE id = 10839792;

输出:

1
2
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	    account	const	PRIMARY	        PRIMARY	4	const	1	NULL

我们可以看到，rows=1, 表示扫描了 1 行接得到了结果。

通过 phone 查找:

1
EXPLAIN SELECT * FROM account WHERE phone = 1899713xxxx;

输出:

1
2
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	account	    ALL	    NULL	NULL	NULL	NULL	48130	Using where

可以看到，扫描了 48130 行才得到想要的结果。

如果这个查询很常用，就有必要对 phone 建立索引，特别是 account 表很庞大的时候，速度优势很明显。

EXPLAIN 显示了 MySQL 如何使用索引来处理 SELECT 语句以及连接表。可以帮助选择更好的引擎和写出更佳的查询语句。

  • select_type, SELECT 的类型，有以下几种值:

    • SIMPLE: 表示简单的 SELECT，没有 UNION 和子查询。

    • PRIMARY: 查询中包含任何复杂的子查询, 最外层被标记为 PRIMARY。
      示例a

      1
      2
      3
      EXPLAIN
      SELECT a.role_id, a.role_name, (SELECT SUM(money) AS total_recharge
      FROM user_pay b WHERE a.role_id = b.role_id) FROM user a;

    • SUBQUERY: 在示例a中, 子句就被标记为 SUBQUERY。

    • DERIVED: 在 FROM 列表中的子查询被标记为 DERIVED。MySQL 会将这个子查询的结果放到一个临时表中，相当于该临时表是从子查询中派生出来的。

      1
      2
      EXPLAIN
      SELECT t.role_id, t.role_name FROM (SELECT role_id, role_name FROM user LIMIT 10) t;

    • UNION: 若第二个 SELECT 出现在 UNION 之后,就会被标记为 UNION。
      示例b

      1
      2
      3
      4
      EXPLAIN
      SELECT role_id FROM user_pay WHERE money=6
      UNION
      SELECT role_id FROM user_pay WHERE money=30;

    • UNION RESULT: 从 UNION 获取结果被标记为 UNION RESULT。见示例b的结果。

    • SUBQUERY 和 UNION 还可以被标记为 DEPENDENT 和 UNCACHEABLE

      • DEPENDENT: 意味着子句依赖于外层发现的结果，见示例a。
      • UNCACHEABLE: 意味着 SELECT 某些特性阻止结果缓存于一个 item_cache 中(TODO:需要更多的资料)。

  • type, 表示在表中找到所需行的方式，也称访问类型。

    • 常见有ALL, index, range, ref, eq_ref, const, system, NULL, 从左到右，性能从最差到最好。

    • NULL: 执行时甚至不用访问表或使用索引，比如找出找出最小用户 id（主键）

      1
      2
      EXPLAIN
      SELECT MIN(role_id) FROM user;

    • const: 表中最多只有一个匹配行，用于 primary key 或者 unique 索引。因为只匹配一行，所以速度快。

      1
      2
      EXPLAIN
      SELECT * FROM user WHERE role_id=123;

    • system: 是 const 的特殊类型，为表中只有一行的时候。

    • eq_ref: 简单的说就是在多表连接中使用 primary key 和 unique key 做为关联条件。

      1
      2
      3
      -- role_id是user和user_activity的主键
      EXPLAIN
      SELECT * FROM user, user_activity WHERE user.role_id=user_activity.role_id;

    • ref: 搜索时使用的索引不是 primary key 或 unique，但可以是复合索引的第一个值。

      1
      2
      3
      -- 存在复合索引 (channel_id, xx, xx)
      EXPLAIN
      SELECT * FROM user WHERE channel_id=1;

    • 以上都是很理想的索引使用情况。

    • range: 用索引来检索一定范围的行, BETWEEN, <, >。除此之外, IN, OR 也会显示 range，但性能有差异。

      1
      2
      3
      4
      5
      -- level是索引，channel_id不是索引。
      -- type=range
      EXPLAIN SELECT role_id, name FROM user WHERE level BETWEEN 10 AND 30 AND channel_id < 10;
      -- type=all
      EXPLAIN SELECT role_id, name FROM user WHERE channel_id < 10;

    • index: 只查找索引，不能包含非索引值。

      1
      2
      3
      4
      5
      -- level是索引，channel_id不是索引
      -- type=index
      EXPLAIN SELECT level FROM user;
      -- type=all
      EXPLAIN SELECT level, channel_id FROM user;

    • ALL: 将遍历全表以查找匹配的行。

  • possible_keys: 提示使用了哪些索引可以找到该行。

  • keys: 使用的索引。

  • key_len: 索引使用的长度。

  • ref: 哪些列或常量被用于查找索引列上的值。

    1
    2
    3
    EXPLAIN
    SELECT a.role_id, a.role_name, (SELECT SUM(money) AS total_recharge
    FROM user_pay b WHERE a.role_id = b.role_id) FROM user a;

    输出:

    1
    2
    3
    id	table	type	possible_keys	key	    key_len	ref	    rows
    1	a	    ALL	    NULL	        NULL	NULL	NULL	40
    2	b		ref	    role_id	        role_id	8	    test.a.role_id	3

    子句中使用 a.role_id 来查找匹配。

  • rows: 估算的找到所需的记录所需要读取的行数。

  • filtered: 显示了通过条件过滤出的行数的百分比估计值。

  • Extra: 比较重要的额外信息。

3 查看索引使用情况

1
SHOW STATUS LIKE 'Handler_read%';

输出:

1
2
3
4
5
6
7
8
Variable_name	        Value
Handler_read_first	    38
Handler_read_key	    2044
Handler_read_last	    0
Handler_read_next	    988904
Handler_read_prev	    0
Handler_read_rnd	    2154
Handler_read_rnd_next	1022038
  • Handler_read_key: 这个值表示一个行被索引值读的次数，很低的值表示增加的索引对性能改善不高，因为索引并不经常使用。
  • Handler_read_rnd_next: 表示在数据文件中读下一行的请求书。如果值很高，表示进行了大量的扫描。通常说明索引不正确或写入的查询没有利用索引。
  • 按照搜索出的数据，这个库的索引情况并不理想。

4 定期分析表和检查表

  • 分析表 ANALYZE TABLE account;
  • 检查表 CHECK TABLE account;
    输出: 1 client is using or hasn't closed the table properly
    修复该错误:
    1
    REPAIR TABLE account;

5 定期优化表

  • 优化表 OPTIMIZE TABLE account, user, …
  • 该命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的更新浪费。
  • 如果已经删除了表中的一大部分，或者对含有可变长度行的表进行了很多更改，可以使用该命令进行优化。
  • 该命令支队 MyISAM,BDB,InnoDB 起作用。

6 常用 sql 优化

  • 优化 INSERT 语句

    • 插入多行时,尽量使用多个值表的 INSERT 语句，减少客户端与服务端的链接，关闭消耗。

      1
      INSERT INTO account VALUES(1, 2), (3, 4)...

    • 使用 INSERT DELAYED INTO 替代 INSERT INTO。仅限于 ISAM 和 MyISAM 表。需要插入的数据会在内存中排队，直到有空闲。而客户端会立即得到 OK 响应。好处是极高的插入速度，客户端不需要等待太长的时间。坏处是如果没有来得及插入数据，在内存队列中的数据将会丢失，而且不能反悔自动递增 ID。

    • 批量插入时，可以增加 bulk_insert_buffer_size 变量值来提高速度，只有 MyISAM 有效。这个参数是批量插入缓存大小，默认 8M。

    • 使用 LOAD DATA INFILE 载入(未使用过)

  • 优化 GROUP BY 语句
    在包含了 GROUP BY 语句，会有一个默认的排序，如果没有必要对结果进行排序，可以用 ORDER BY NULL 取消排序。

    1
    SELECT role_id, SUM(money) AS total_recharge FROM user_pay GROUP BY role_id ORDER BY NULL;

  • 优化 ORDER BY 语句
    当以下情况时,ORDER BY 也可以借助索引来排序

    • 索引满足 WHERE 和 ORDER BY
    • ORDER BY 的顺序和索引顺序相同
    • ORDER BY 都是升序或者降序的
      TODO 需要更多的资料

  • 优化嵌套查询

    • 使用 JOIN 来替代子查询，速度会快很多，尤其是对子查询中的列建有索引的情况下，性能会更好。
    • MySQL 不需要再内存中创建临时表来完成这个逻辑上需要两个步骤的工作。

  • 使用 SQL 提示

    • USE INDEX: 添加希望 MySQL 去参考的索引列表

      1
      SELECT * FROM user USE INDEX(user_id_phone) WHERE user_id=1 AND phone=123456;

    • IGNORE INDEX: 忽略指定的索引

      1
      SELECT * FROM user IGNORE INDEX(user_id_phone) WHERE user_id=1 AND phone=123456;

      假设就这一个索引，忽略之后，MySQL 会使用全表扫描

    • FORCE INDEX: 强制使用指定索引

      1
      SELECT * FROM user FORCE INDEX(user_id_phone) WHERE user_id=1 AND phone=12356;

5 优化数据库对象

1 优化表的数据类型

  • 大利器 PROCEDURE ANALYSE()
  • 用法 SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]])
    • max_elements 默认值 256, 查找每一列不同值时所需关注的最大不同值的数量。还用这个值来检查给予建议的值是否是 ENUM。
    • max_memory 查找每一列所有不同值时可能分配的最大的内存数量。
  • 示例 SELECT * FROM user PROCEDURE ANALYSE(16, 256);,注意看Optimal_fieldtype中给予的建议。如果表中数据量小，要注意区分建议的局限性。

2 通过拆分表提高表的访问效率

对于 MyISAM 类型的表:

  • 垂直拆分: 把主键和一些列放在一张表，把主键和另一些列放在另一张表。特别在一张表中，有的列常用，而有些列不常用的时候，可以这么拆。好处是使得数据行变小，查询的时候会减少 I/O 次数。缺点是查询所有数据的时候要 JOIN 操作。
  • 水平拆分: 把很大的表拆成好几张，比如最近 3 个月的数据一张，3 个月以前的表一张。

3 使用中间表提供统计查询速度

  • 如果表很大，要在上面对一定范围内的数据做查询统计。可以把这部分数据导出到另一张一模一样的表中，然后在做处理。
  • 这边省略了导出的时间。
  • 在新表上处理，不会对应用产生负面影响。
  • 可以在新表上增加索引，临时字段等，提供性能。

6 锁问题

1 锁

  • 表级锁: 开销小，加锁快，不会出现死锁，锁定粒度大，引起冲突概率最高，并发度最低。
  • 行级锁：开销大，加锁慢，会出现思索，锁定粒度最小，引起冲突概率最低，并发度最高。
  • 页面所: 会出现死锁，介于表级锁和行级锁中间。
  • MyISAM 支持表级锁。
  • InnoDB 支持表级锁和行级锁，默认采用行级锁。
  • 仅从锁的角度来说:
    • 表级锁更适合以查询为主，只有少量按索引条件更新数据的应用。
    • 行级锁适合有大量按索引条件并发更新数据的应用。

2 MyISAM 表锁

  • 可以通过SHOW STATUS LIKE 'table_locks_%';来查看表锁定争夺。

    1
    2
    3
    Variable_name	        Value
    Table_locks_immediate	29029735
    Table_locks_waited	    30

    如果 Table_locks_waited 值比较高，说明存在着较严重的表级锁竞争。

  • 加读锁: 不会阻塞其它用户对表的读操作，但是，会阻塞对该表的写操作，包括加锁的这个用户。

  • 加写锁: 只有加锁的这个用户可以对表进行读写操作，其它用户读写操作都会等待。

  • 示例:

    1
    2
    3
    4
    5
    6
    7
    -- 加锁
    LOCK TABLE user WRITE;
    -- 可以进行读写操作
    -- 但其它用户则需要等待，比如另起终端执行读操作。
    SELECT * FROM user LIMIT 10;
    -- 直到释放了锁
    UNLOCK TABLES;

  • MyISAM 会在 SELECT 前给锁设计的表添加读锁。在 UPDATE,INSERT,DELETE 时，加写锁。

  • MyISAM 会一次获得所需要的锁，不会变更。比如获得了读锁，则不能执行写操作。

  • 并发插入

    • concurrent_insert 用于控制并发插入行为。SHOW VARIABLES LIKE 'concurrent_insert';
    • 当 concurrent_insert=0 的时候，不允许并发插入。
    • 当 concurrent_insert=1 的时候，表中间没有被删除的行，加读锁的时候，允许另一个用户从表尾插入数据，这个也是默认设置。
    • 当 concurrent_insert=2 的时候，无论表中间有没有删除的行，都允许另一个用户从表尾插入数据。
    • 我这边显示的是 concurrent_insert=auto, 行为和 concurrent_insert=1 一样。
    • 可以在空闲的时候使用 OPTIMIZE TABLE 来整理空间碎片，收回因删除记录而产生的中间空洞。

  • 锁调度

    • MyISAM 的读写是互斥的，串行的。
    • 假设同时有读和写请求到来，写会获得锁。
    • 即使读请求先在获取锁的等待队列中，写请求后到，写会获得锁。
    • 因为 MySQL 认为写操作比读操作重要。
    • 这也是 MyISAM 不适合有大量更新操作同时又有很多读操作的原因。
    • 大量的写操作会造成读操作难以获得锁。
    • 可以使用一些设置来调节这些行为:
      • 启动时指定参数 low-priority-updates，使 MyISAM 默认给予读请求以优先的权利。
      • SET LOW_PRIORITY_UPDATES=1,使得该连接发出的写操作优先级降低。
      • 通过指定 INSERT,UPDATE,DELETE 语句的 LOW_PRIORITY 属性,降低该语句的优先级。
      • 给系统参数 max_write_lock_count 指定一定的值，当读请求达到这个值后，就将写请求的优先级降低。

3 InnoDB 锁

TODO

MySQL必知必会

《MySQL 必知必会》是一本适合入门的书籍，非常适合CURD boy

1 基础知识

1 主键

  • 唯一标识表中每行的这个列(这组列)称为主键。
  • 应该总是定义主键，虽然并不总是需要主键。
  • 任意两行都不具有相同的主键值。
  • 每一行都必须具有一个主键值，不可为 NULL。

2 常用命令

  • SHOW DATABASES;
  • SHOW TABLES;
  • SHOW COLUMNS FROM table;
  • SHOW STATUS;
  • SHOW CREATE DATABASE database;
  • SHOW CREATE TABLE table;

2 检索数据

除非确实需要绝大部分列或全部列，否则最好不使用通配符* 来获取所有的列。检索不需要的列通常会降低搜索和应用程序的性能。

1 DISTINCT 和 GROUP BY

1
2
SELECT DISTINCT `role_id` FROM user;
SELECT `role_id` FROM user GROUP BY `role_id`;
  • 二者都可以达到去重的效果。
  • DISTINCT 把列中的全部内容存储到内存中，可以理解为一个 hash，最后的到 hash 中的 key 就可以得到结果。比较耗内存。
  • GROUP BY 先将列排序，然后去重。排序比较耗时间。

2 LIMIT

从 0 开始算

1
2
SELECT `role_id` FROM user LIMIT 5 OFFSET 3;
SELECT `role_id` FROM user LIMIT 3, 5;

以上两条命令都表示从 3 开始的 5 行。

3 排序数据

默认为升序 ASC。

1
SELECT `role_id`, `account_id` FROM user_pay ORDER BY `account_id`, `role_id` DESC LIMIT 20;

以上语句中, account_id 默认为升序排列，也可以写上 ASC。

4 过滤数据

1 IN 操作符

1
SELECT * FROM user WHERE role_id IN (1000001, 1000002);

2 NOT 操作符

1
SELECT * FROM user WHERE role_id IN (1000001, 1000002) LIMIT 10;

3 LIKE 操作符

1
2
3
SELECT * FROM user WHERE name LIKE 't%';
SELECT * FROM user WHERE name LIKE 't_';    -- t1, t2
SELECT * FROM user WHERE name LIKE 't__';   -- t123, t34
  • 以上两句都是模糊匹配用户名以 t 开头。
  • %: 匹配任意 0 个或者多个字符。
  • _: 一个_匹配 1 个任意字符，且必须有一个。

5 正则表达式搜索

MYSQL 仅支持多数正则表达式实现的一个很小的子集。

1 基本字符匹配

1
2
SELECT name FROM user WHERE name REGEXP 't';    -- t, t1, t2
SELECT name FROM user WHERE name REGEXP 't.';   -- t1, t2
  • .表示匹配任意一个字符。
  • LIKE 和 REGEXP 区别:
  • LIKE 要求整个列匹配(使用通配符除外), REGEXP 只要列中某个片段匹配即可。
  • 假设有用户名为 s123。则以下例子中, LIKE 没有得到结果。
1
2
SELECT name FROM user WHERE name LIKE 's1';     -- 没有结果
SELECT name FROM user WHERE name REGEXP 's1';   -- s123

2 OR 匹配

1
SELECT name FROM user WHERE name REGEXP 's1|s2' ORDER BY name; -- s123, s2, s234

使用|功能上类似于 SELECT 中的 OR 语句。多个 OR 语句可以使用正则表达式替代，更简洁。

3 匹配几个字符之一

1
SELECT name FROM user WHERE name REGEXP 's[1238]' ORDER BY name;  -- s123, s2, s234, s89

相当于

1
SELECT name FROM user WHERE name REGEXP 's1|s2|s3|s8' ORDER BY name;

也可以添加^，来匹配除指定以外的内容

1
SELECT name FROM user WHERE name REGEXP 's[^1238]' ORDER BY name;  -- s4, s5

4 匹配范围

1
2
3
SELECT name FROM user WHERE name REGEXP 's[1-8]' ORDER BY name;  -- s123, s2, s89..

SELECT name FROM user WHERE name REGEXP '[a-z][1-8]' ORDER BY name;  -- a1, b2, c3

5 匹配特殊字符

为了匹配特殊字符，必须用\\为前导。

1
SELECT name FROM user WHERE name REGEXP 's\\-' ORDER BY name;  -- s-5

\\也用来引用具有特殊含义的字符

特殊字符 含义
\\f 换页
\\n 换行
\\r 回车
\\t 制表
\\v 纵向制表

多数正则表达式使用\转义特殊字符，以便能使用这些字符本身。但 MySQL 要求用\\。
MySQL 解释一个，正则表达式解释另外一个。

6 匹配字符类

为了方便工作，可以使用预定义的字符集

类 说明
[:alnum:] 任意字母和数字 ([a-zA-Z0-9])
[:alpha:] 任意字符 ([a-zA-Z])
[:blank:] 空格和指标 (\\t)
[:cntrl:] ASCII 控制字符 (ASCII 0~31, 127)
[:digit:] 任意数字 ([0-9])
[:graph:] 与[:print:]相同，但不包括空格
[:lower:] 任意小写字母 ([a-z])
[:print:] 任意可打印的数字
[:punct:] 同时不在[:alnum:][:cntrl:]中的任意字符
[:space:] 包括空格在内的任意空白字符 ([\\t\\n\\r\\t\\v])
[:upper:] 任意大写字母 ([A-Z])
[:xdigit:] 任意十六进制数字 ([a-fA-F0-9])

示例:

1
SELECT name FROM user WHERE name REGEXP '[[:alpha:]]1' ORDER BY name; -- h1, m1, s123

7 匹配多个实例

字符 说明
* 0 个或多个匹配
+ 1 个或多个匹配 ({1, })
? 0 个或 1 个匹配 ({0, 1})
{n} 指定数目的匹配
{n, } 不少于指定数目的匹配
{n, m} 匹配数目范围, m 不超过 255 
1
SELECT name FROM user WHERE name REGEXP '[[:digit:]]{4}' ORDER BY name; -- s4444, 21111

8 定位符

元字符 说明
^ 文本的开始
$ 文本的结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾
  • 示例 1
    假设要找到以字母开头的用户名
1
SELECT  name FROM user WHERE name REGEXP '[a-zA-Z]';

以上语句将会在文本任意位置进行查找匹配，并不符合以字母开头这依规定. 这里可以使用^

1
SELECT  name FROM user WHERE name REGEXP '^[a-zA-Z]';

6 创建计算字段

存储在表中的数据不一定是应用程序所需要的。我们可以直接从数据库中检索出转换，计算或格式化过的数据。而不是检索出原始数据然后在应用程序中重新格式化。

1 拼接 CONCAT

表中含有 role_id, name 字段，应用程序需要这样的格式 role_name(role_id)

1
2
SELECT CONCAT(name, '(', role_id, ')') FROM user LIMIT 1;   -- s123 (1000001)
SELECT CONCAT(RTRIM(name), '(', role_id, ')') FROM user LIMIT 1;   -- s123(1000001)
  • RTRIM()函数去掉了值右边的所有空格。其余有 LTRIM(), TRIM()

2 别名 AS

拼接处的结果没有名字，应用程序没法引用。可以使用别名解决这个问题。

1
SELECT CONCAT(name, '(', role_id, ')') AS info FROM user LIMIT 1;

这样，应用程序就可以使用 info 这个列，就像它本来就存在于表中一样。

3 执行算术计算

假设用户充值了 money(元),每元可以换成 10 个代币,这里通过计算直接得出获得的总代币。

1
SELECT role_id, money, money * 10 AS total_gold FROM user LIMIT 10;

7 使用数据处理函数

1 字符串函数

函数 说明 示例 结果
CHAR_LENGTH(S) 返回字符串 s 字符数 SELECT CHAR_LENGTH(‘abc 你好’); 6
LENGTH(S) 返回字符串 s 的长度 SELECT LENGTH(‘abc 你好’); 10
CONCAT(S1,S2,…) 合并为一个字符串 SELECT CONCAT(‘hello’, ‘ abc’); hello abc
CONCAT_WS(x, s1, s2,…) 同 CONCAT,但会加上 x SELECT CONCAT_WS(‘+’, ‘1’, ‘2’, ‘3’); 1+2+3
INSERT(s1, x, length, s2) 将字符串 s2 替换 s1 的 x 位置开始长度为 length 的字符串 SELECT INSERT(‘abcdefg’, 2, 3, ‘123’); a123efg
UPPER(s) 将字符串 s 的所有字母变成大写字母 SELECT UPPER(‘abcd’); ABCD
LOWER(s) 将字符串 s 的所有字母变成小写字母 SELECT LOWER(‘ABCD’); abcd
LEFT(s, n) 返回字符串 s 的前 n 个字符 SELECT LEFT(‘abcdef’, 3); abc
RIGHT(s, n) 返回字符串 s 的后 n 个字符 SELECT RIGHT(‘abcdef’, 3); def
LPAD(s1, length, s2) 字符串 s2 来填充 s1 的开始处，使字符串长度达到 length SELECT LPAD(‘abc’, 8, ‘123’); 12312abc
RPAD(s1, length, s2) 字符串 s2 来填充 s1 的结尾处，使字符串的长度达到 length SELECT RPAD(‘abc’, 8, ‘123’); abc12312
LTRIM(s) 去掉字符串 s 开始处的空格 SELECT LTRIM(‘ abc ‘); ‘abc ‘
RTRIM(s) 去掉字符串 s 结尾处的空格 SELECT RTRIM(‘ abc ‘); ‘ abc’
TRIM(s) 去掉字符串 s 开始和结尾处的空格 SELECT TRIM(‘ abc ‘); ‘abc’
TRIM(s1 FROM s) 去掉字符串 s 中开始处和结尾处的字符串 s1 SELECT TRIM(‘-‘ FROM ‘—hello–’); hello
REPEAT(s, n) 将字符串 s 重复 n 次 SELECT REPEAT(‘abc’, 3); abcabcabc
SPACE(n) 返回 n 个空格 SELECT SPACE(3); ‘   ’
REPLACE(s, s1, s2) 将字符串 s2 替代字符串 s 中的字符串 s1 SELECT REPLACE(‘abcdef’, ‘abc’, ‘12’); 12def
STRCMP(s1, s2) 比较字符串 s1 和 s2 SELECT STRCMP(‘abc’, ‘abc’); 0
STRCMP(s1, s2) 比较字符串 s1 和 s2 SELECT STRCMP(‘abc’, ‘abcd’); -1
STRCMP(s1, s2) 比较字符串 s1 和 s2 SELECT STRCMP(‘abc’, ‘ab’); 1
SUBSTRING(s, n, length) 获取从字符串 s 中的第 n 个位置开始长度为 length 的字符串 SELECT SUBSTRING(‘abcdefg’, 2, 3); bcd
MID(s, n, length) 同 SUBSTRING SELECT MID(‘abcdefg’, 3, 2); cd
LOCATE(s1, s) 从字符串 s 中获取 s1 的开始位置 SELECT LOCATE(‘de’, ‘abcdefg’); 4
POSITION(s1, s) 从字符串 s 中获取 s1 的开始位置 SELECT POSITION(‘de’ IN ‘abcdefg’); 4
INSTR(s, s1) 从字符串 s 中获取 s1 的开始位置 SELECT INSTR(‘abcdefg’, ‘de’); 4
REVERSE(s) 将字符串 s 的顺序反过来 SELECT REVERSE(‘a,b,c,d,e,f’); f,e,d,c,b,a
ELT(n, s1, s2, …) 返回第 n 个字符串 SELECT ELT(3, ‘abc’, ‘def’, ‘ghi’, ‘jkl’); ghi
EXPORT_SET(…) 见示例 SELECT EXPORTSET(6, ‘y’, ‘n’, ‘‘, 3); n_y_y
FIELD(s, s1, s2, …) 返回第一个与字符串 s 匹配的字符串位置 SELECT FIELD(‘b’, ‘a’, ‘b’, ‘c’); 2
FIND_IN_SET(str, str_list) 见示例 SELECT FIND_IN_SET(‘4’, ‘6,5,4,3,2,1’); 3

2 数学函数

函数 说明 示例 结果
ABS(x) 返回 x 的绝对值
CEIL(x) 返回大于或等于 x 的最小整数
CEILING(x) 返回大于或等于 x 的最小整数
FLOOR(x) 返回小于或等于 x 的最大整数
RAND() 返回 0->1 的随机数
RAND(x) 返回 0->1 的随机数，x 值相同时返回的随机数相同
SIGN(x) 返回 x 的符号，x 是负数、0、正数分别返回-1、0 和 1
PI() 返回圆周率(3.141593）
TRUNCATE(x, y) 返回数值 x 保留到小数点后 y 位的值(不会四舍五入)
ROUND(x) 返回离 x 最近的整数
ROUND(x, y) 保留 x 小数点后 y 位的值(四舍五入)
POW(x, y) 返回 x 的 y 次方
POWER(x, y) 返回 x 的 y 次方
SQRT(x) 返回 x 的平方根
EXP(x) 返回 e 的 x 次方
MOD(x, y) 返回 x 除以 y 以后的余数
LOG(x) 返回自然对数(以 e 为底的对数)
LOG10(x) 返回以 10 为底的对数
RADIANS(x) 将角度转换为弧度
DEGREES(x) 将弧度转换为角度
SIN(x) 求正弦值(参数是弧度)
ASIN(x) 求反正弦值(参数是弧度)
COS(x) 求余弦值(参数是弧度)
ACOS(x) 求反余弦值(参数是弧度)
TAN(x) 求正切值(参数是弧度)
ATAN(), ATAN2() 求反正切值(参数是弧度)
COT() 求余切值(参数是弧度)

3 日期时间函数

函数 说明 示例 结果
CURDATE(), CURRENT_DATE() 返回当前日期 SELECT CURRENT_DATE(); 2017-05-11
CURTIME(), CURRENT_TIME 返回当前时间 SELECT CURRENT_TIME(); 19:01:11
NOW() 返回当前日期和时间 SELECT NOW(); 2017-05-11 19:01:30
CURRENT_TIMESTAMP() 返回当前日期和时间 同上
LOCALTIME() 返回当前日期和时间 同上
SYSDATE() 返回当前日期和时间 同上
LOCALTIMESTAMP() 返回当前日期和时间 同上
UNIX_TIMESTAMP() 以 UNIX 时间戳的形式返回当前时间 SELECT UNIX_TIMESTAMP(); 1494500521
UNIX_TIMESTAMP(d) 将时间 d 以 UNIX 时间戳的形式返回 SELECT UNIX_TIMESTAMP(‘2017-05-11 19:02:01’); 1494500521
FROM_UNIXTIME(d) 将 UNIX 时间戳的时间转换为普通格式的时间 SELECT FROM_UNIXTIME(1494500521); 2017-05-11 19:02:01
UTC_DATE() 返回 UTC 日期 SELECT UTC_DATE(); 2017-05-11
UTC_TIME() 返回 UTC 时间 SELECT UTC_TIME(); 11:06:13
MONTH(d) 返回日期 d 中的月份值，1->12 SELECT MONTH(‘2017-05-11’); 5
MONTHNAME(d) 返回日期当中的月份名称 SELECT MONTHNAME(‘2017-05-11’); May
DAYNAME(d) 返回日期 d 是星期几 SELECT DAYNAME(‘2017-05-11 19:07:12’); Thursday
DAYOFWEEK(d) 日期 d 今天是星期几，1 星期日，2 星期一 SELECT DAYOFWEEK(‘2017-05-11’); 5
WEEKDAY(d) 日期 d 今天是星期几，0 表示星期一，1 表示星期二 SELECT WEEKDAY(‘2017-05-11’); 3
WEEK(d)，WEEKOFYEAR(d) 计算日期 d 是本年的第几个星期，范围是 0->53 SELECT WEEK(‘2017-05-11’); 19
DAYOFYEAR(d) 计算日期 d 是本年的第几天 SELECT DAYOFYEAR(‘2017-05-11’); 131
DAYOFMONTH(d) 计算日期 d 是本月的第几天 SELECT DAYOFMONTH(‘2017-05-11’); 11
QUARTER(d) 返回日期 d 是第几季节，返回 1->4 SELECT QUARTER(‘2017-05-11’); 2
HOUR(t) 返回 t 中的小时值 SELECT HOUR(‘2017-05-11 19:11:23’); 19
MINUTE(t) 返回 t 中的分钟值 SELECT MINUTE(‘2017-05-11 19:11:23’); 11
SECOND(t) 返回 t 中的秒钟值 SELECT SECOND(‘2017-05-11 19:11:23’); 23
EXTRACT(type FROM d) 从日期 d 中获取指定的值，type 指定返回的值(见下文) SELECT EXTRACT(WEEK FROM ‘2017-05-11 19:11:23’); 19
TIME_TO_SEC(t) 将时间 t 转换为秒 SELECT TIME_TO_SEC(‘19:11:23’); 69083
SEC_TO_TIME(s) 将以秒为单位的时间 s 转换为时分秒的格式 SELECT SEC_TO_TIME(69083); 19:11:23
TO_DAYS(d) 计算日期 d 距离 0000 年 1 月 1 日的天数 SELECT TO_DAYS(‘2017-05-11 19:11:23’); 736825
FROM_DAYS(n) 计算从 0000 年 1 月 1 日开始 n 天后的日期 SELECT FROM_DAYS(736825); 2017-05-11
DATEDIFF(d1,d2) 计算日期 d1->d2 之间相隔的天数 SELECT DATEDIFF(‘2017-05-11’, ‘2017-05-12’); -1
ADDDATE(d,n) 计算其实日期 d 加上 n 天的日期 SELECT ADDDATE(‘2017-05-11 19:11:23’, 3); 2017-05-14 19:11:23
ADDDATE(d，INTERVAL expr type) 计算起始日期 d 加上一个时间段后的日期 SELECT ADDDATE(‘2017-05-11 19:11:23’, INTERVAL 3 HOUR); 2017-05-11 22:11:23
DATE_ADD(d,INTERVAL expr type) 同上 SELECT DATE_ADD(‘2017-05-11 19:11:23’, INTERVAL 10 HOUR); 2017-05-12 05:11:23
SUBDATE(d,n) 日期 d 减去 n 天后的日期 SELECT SUBDATE(‘2017-05-12 05:11:23’, 13); 2017-04-29 05:11:23
SUBDATE(d,INTERVAL expr type) 日期 d 减去一个时间段后的日期 SELECT SUBDATE(‘2017-04-29 05:11:23’, INTERVAL 10 MINUTE); 2017-04-29 05:01:23
ADDTIME(t,n) 时间 t 加上 n 秒的时间 SELECT ADDTIME(‘2017-04-29 05:01:23’, 30); 2017-04-29 05:01:53
SUBTIME(t,n) 时间 t 减去 n 秒的时间 SELECT SUBTIME(‘2017-04-29 05:01:53’, 30); 2017-04-29 05:01:23
DATE_FORMAT(d,f) 按表达式 f 的要求显示日期 d SELECT DATE_FORMAT(‘2017-04-29 05:01:23’, ‘%Y-%m-%d’); 2017-04-29
TIME_FORMAT(t,f) 按表达式 f 的要求显示时间 t SELECT TIME_FORMAT(‘2017-04-29 05:01:23’, ‘%r’); 05:01:23 AM
  • type 的值可以为:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

4 条件判断函数

  • IF (expr, v1, v2);
1
SELECT IF (1 > 0, 'Y', 'N');	-- Y
  • IFNULL(v1, v2);
    如果 v1 不为 NULL, 返回 v1，否则返回 v2
1
SELECT IFNULL('a', 'b');	-- a

5 系统信息函数

函数 说明 示例 结果
VERSION() 返回数据库的版本号 SELECT VERSION(); 5.7.11
CONNECTION_ID() 返回服务器的连接数 SELECT CONNECTION_ID(); 13
DATABASE() 返回当前数据库名 SELECT DATABASE(); database-learn
USER() 返回当前用户 SELECT USER(); root@localhost
CHARSET(s) 返回字符串 s 的字符集 SELECT CHARSET(“123”); utf8
COLLATION(s) 返回字符串 s 的字符排列方式 SELECT COLLATION(“a123”); utf8_general_ci
LAST_INSERT_ID() 返回最近生成的 AUTO_INCREMENT 值 SELECT LAST_INSERT_ID(); 0

8 分组数据

1 数据分组

假设要获取用户的充值次数，最低充值额度，最高充值额度，平均充值额度，可以用以下命令:

1
SELECT role_id, COUNT(*) AS num, MIN(money) as min_money, MAX(money) as max_money, AVG(money) AS avg_money FROM user_pay;

以上得出的是总的信息，如果要获取每个用户的这些信息，就可以使用分组了。

1
SELECT role_id, COUNT(*) AS num, MIN(money) as min_money, MAX(money) as max_money, AVG(money) AS avg_money FROM user_pay GROUP BY role_id ORDER BY num;

以上按照每个用户来计算结果。

  • 需要注意的是，GROUP BY 必须出现在 WHERE 之后，ORDER BY 之前
  • 可以使用 WITH ROLLUP 得到汇总的值
1
SELECT role_id, COUNT(*) AS num, MIN(money) as min_money, MAX(money) as max_money, AVG(money) AS avg_money FROM user_pay GROUP BY role_id WITH ROLLUP;

以上在在结果的最后，会附上总的结果。

2 分组过滤

假设只需要得到充值 2 次(包含)以上用户的数据，则需要使用 HAVING 来过滤。

1
SELECT role_id, COUNT(*) AS num, MIN(money) as min_money, MAX(money) as max_money, AVG(money) AS avg_money FROM user_pay GROUP BY role_id HAVING num >= 2 ORDER BY num;
  • 注意 HAVING 跟 GROUP BY 后面。
  • 也可以同时使用 WHERE 和 HAVING。
1
SELECT role_id, COUNT(*) AS num, MIN(money) as min_money, MAX(money) as max_money, AVG(money) AS avg_money FROM user_pay WHERE time >= 1483200000 GROUP BY role_id HAVING num >= 2 ORDER BY num;

以上通过 WHERE 新增了条件，2017 年以来充值的。

  • 当 sql_mode 为 ONLY_FULL_GROUP_BY 需要注意
  • 查看 sql_mode 值
1
SELECT @@sql_mode;

结果:

1
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
  • 在 sql_mode=ONLY_FULL_GROUP_BY 的模式下，以下句子报错
1
SELECT role_id, money FROM user_pay GROUP BY role_id;

错误: SELECT list is not in GROUP BY clause and contains nonaggregated column ...
表中的列，出现在 SELECT 中时，也得出现在 GROUP BY 中。

1
SELECT role_id, money FROM user_pay GROUP BY role_id, money;

同样，ORDER BY 也需要注意这个问题。

3 SELECT 字句顺序

1
SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY > LIMIT

9 子查询

1 子查询过滤

假设要得出充值用户的用户信息

1
SELECT role_id, name FROM user WHERE role_id in (SELECT role_id FROM user_pay);
  • 在 SELECT 语句中，子查询总是从内向外处理。
  • 需要保证 WHERE 语句中需要和子 SELECT 语句中有相同数目的列。二者名称可以不相同。
1
2
... WHERE role_id in (SELECT role_id ...)
... WHERE role_id in (SELECT r_id ...)

2 做为计算字段使用子查询

假设要得出用户的充值次数(user_pay)以及用户信息(user)

1
2
3
4
5
6
7
SELECT  role_id,
name,
(SELECT COUNT(*)
FROM user_pay
WHERE user_pay.role_id = user.role_id) AS recharge_count
FROM user
LIMIT 10;

10 联结

1 内联结

同 9.2 假设要得出用户的充值次数(user_pay)以及用户信息(user), 以下两种方法都可以获得结果。

使用 WHERE 子句

1
2
3
4
SELECT role_id, COUNT(money)
FROM user, user_pay
WHERE user.role_id = user_pay.rid
GROUP BY role_id;

使用 INNER JOIN

1
2
3
4
5
6
SELECT  role_id,
COUNT(money)
FROM user
INNER JOIN user_pay
ON user_pay.role_id = user.role_id
GROUP BY role_id;
  • ANSI SQL 规范首选 INNER JOIN。

2 外联结

外联结使用 OUTER JOIN 来表示。
必须在 OUTER 前加上 LEFT 或 RIGHT 关键字。OUTER 可以省略不写。
LEFT: 表示选中OUTER左侧表的所有行。 RIGHT: 表示选中OUTER右侧表的所有行。

1
2
3
4
5
SELECT a.role_id, SUM(b.money) AS total_recharge
FROM user a
LEFT JOIN user_pay b
ON a.role_id = b.role_id
GROUP BY a.role_id;

以上信息获取用户的充值信息，如果有用户没有充值，则 total_recharge=NULL。
如果使用 RIGHT JOIN，如果 user_pay 中有用户数据在 user 表中找不到，则 role_id=NULL。

11 组合查询

1 UNION

假设需要获取充值额度为 30 的用户, 以及渠道为 1001 的用户，使用组合查询:

1
2
3
SELECT role_id, money FROM user_pay WHERE money = 30
UNION
SELECT role_id, money FROM user_pay WHERE channel_id = 1001;
  • 组合使用 UNION 将独立的 SELECT 相连。
  • 每个 SELECT 查询都必须包含相同的列，表达式或函数。但次序不必相同。

2 UNION ALL

UNION 从查询结果中自动去除了重复的行。比如渠道 1001 也有人充值 30 的。
如果不想被去除重复的行，可以使用 UNION ALL。

3 组合查询结果排序

可以在最后一条的 SELECT 后添加 ORDER BY 语句对结果进行排序。

12 全文本搜索

1 引擎支持

  • MyISAM 和 InnoDB(5.6)都支持全文本搜索。
    TODO

13 视图

视图可以简化操作，保护数据。

1 创建视图

  • 使用 CREATE VIEW 创建视图。
  • 使用 DROP VIEW 删除视图。
  • 这边使用 CREATE OR REPLACE VIEW
  • 创建一个视图，该视图从用户表(user), 用户充值表(user_pay)获取用户基本信息，总充值额度。
1
2
3
4
5
6
CREATE OR REPLACE VIEW user_pay_info AS
SELECT b.role_id, b.name, SUM(a.money) AS total_money
FROM user_pay a
RIGHT JOIN user b
ON a.role_id = b.role_id
GROUP BY b.role_id;

使用 SHOW TABLES 可以发现多了一个表，user_pay_info。

2 使用视图 SELECT

  • 创建好视图后，再想获得用户充值信息，可以通过以下语句:
1
SELECT * FROM user_pay_info;

十分便捷。

  • 虽然表面看是从 user_pay_info 中获取数据，但实际上仍然是从 user, user_pay 中获取数据。

3 更新视图 UPDATE

视图中存在以下操作，则不可更新:

  • 分组 (GROUP BY, HAVING)
  • 联结
  • 子查询
  • 聚集函数 (MIN, COUNT, SUM)
  • DISTINCT

但凡 MySQL 不能确定能够正确更新到实际表(user, user_pay),则不允许进行视图更新。
一般，应该将视图用于检索，而不用于更新。

14 存储过程

相当于调用预编译好的 sql 集合。

1 创建存储过程 CREATE PROCEDURE

假设要知道每个用户的充值总额

1
2
3
4
5
6
CREATE PROCEDURE user_pay_total()
BEGIN
SELECT role_id, SUM(money) AS total_recharge
FROM user_pay
GROUP BY role_id;
END

以上就创建好了。
需要注意的是，如果在命令行工具中直接用以上语句创建，会报错。
因为命令行工具也用;做为分隔符，sql 语句中也是用;做为分隔符，存在冲突。
是用 DELIMITER 可以自定义命令行工具的分隔符

1
2
3
4
5
6
7
8
DELIMITER //
CREATE PROCEDURE user_pay_total()
BEGIN
SELECT role_id, SUM(money) AS total_recharge
FROM user_pay
GROUP BY role_id;
END //
DELIMITER ;

以上 DELIMITER 告诉命令行工具，使用//做为分隔符。最后一句恢复回;做为分隔符。

2 使用存储过程 CALL

1
CALL user_pay_total();

以上语句会执行刚才创建的存储过程。

3 删除存储过程 DROP

可以直接使用 DROP 删除

1
DROP PROCEDURE user_pay_total;

但是，如果不存在 user_pay_total()，就会报错。
所以，建议用以下命令:

1
DROP PROCEDURE IF EXISTS user_pay_total;

4 使用参数

参数可以用 IN, OUT, INOUT 修饰。
TODO

5 检测存储过程

以下语句可以显示创建存储过程的鳄鱼局

1
SHOW CREATE PROCEDURE user_pay_info;

15 触发器

1 创建触发器

  • MySQL 触发器只响应以下语句: INSERT, UPDATE, DELETE
  • 保持每个数据库触发器名称唯一。
  • 只有表才支持触发器，视图，临时表不支持。

创建 tb1, tb2 同 tb1

1
2
3
4
5
CREATE TABLE `tb1` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '索引值',
`value` int(11) NOT NULL COMMENT '数据',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

创建触发器，命令行下别忘了 DELIMITER

1
2
3
4
5
6
7
8
9
10
DELIMITER //

CREATE TRIGGER tb1_cp_tb2
AFTER INSERT ON tb1
FOR EACH ROW
BEGIN
INSERT INTO tb2(id, value) VALUES (NEW.id, NEW.value);
END //

DELIMITER ;

以上触发器在 tb1 执行 INSERT 操作时触发，会给 tb2 插入相同的数据。

2 删除触发器

1
DROP TRIGGER IF EXISTS tb1_cp_tb2;

3 触发说明

  • INSERT
  1. INSERT 触发器可在 INSERT 执行之前或之后触发。
  2. 在触发器代码内，可以使用一个名为 NEW 的虚拟表，访问被插入的行。
  3. 对于 AUTO_INCREMENT 列，NEW 在 INSERT 之前为 0，在 INSERT 执行之后为自动生成的值。
  • UPDATE
    同 INSERT
  • DELETE
  1. DELETE 触发器可在 DELETE 执行之前或之后触发。
  2. 在触发器代码内，可以使用一个名为 OLD 的虚拟表，访问被插入的行。
1
2
3
4
5
6
7
8
9
10
DELIMITER //

CREATE TRIGGER tb1_cp_tb2
AFTER DELETE ON tb1
FOR EACH ROW
BEGIN
INSERT INTO tb2(id, value) VALUES (OLD.id, OLD.value);
END //

DELIMITER ;

从 tb1 删除的数据会被复制到 tb2 中。

16 事务处理

事务处理可以用来维护数据库的完整性，保证多个 SQL 命令要么完全执行，要么完全不执行。

1 事务处理示例

1
2
3
4
5
6
SELECT * FROM tb1;
START TRANSACTION;
DELETE FROM tb1;
SELECT * FROM tb1;
ROLLBACK;
SELECT * FROM tb1;
  • 以上语句中，当删除 tb1 后，再次查询，没有内容。当回滚后，数据又出现了。
  • 可以使用 COMMIT 将事务提交上去执行。
  • 不能回退 SELECT, CREATE, DROP 操作。
  • 当执行 COMMIT 或 ROLLBACK 后，事务会自动关闭。

2 保留点

复杂的事务处理中，可能存在需要部分回退或者部分提交的情况。
可以使用保留点来处理。

1
2
3
SAVEPOINT d1; -- 创建了保留点
...
ROLLBACK TO d1;  -- 回滚到保留点

当事务关闭后，保留点会自动释放。

3 autocommit

InnoDB 默认 autocommit=on，即每一条 sql 语句都是当成一个事务，执行后就提交。
当写下 START TRANSACTION 时，autocommit 的设置就无效了。需要等待 COMMIT 或 ROLLBACK 来结束事务。
autocommit 针对的是每个与 MySQL 的链接，改变其值不会影响其它链接。