MySQL核心知识概要（二）

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL核心知识概要（二），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含14645字，纯文字阅读大概需要21分钟。

内容图文

DQL

select查询

select column_name [[as] column_alias] from table_name [[as] table_alias];
select 常量...;
select 表达式;
select 函数;
#使用列别名时应使用as，否则可能会出现无法预料的情况：
select 'a' 'b';

条件查询

select column_names from table_name where column_name operator value;

• 算术运算符：+ - * / %
• 比较运算符： =、 <> !=、 >、 <、 >=、 <=、 BETWEEN、 NOT BETWEEN、 IN、 NOT IN、 LIKE、 IS NULL、 IS NOT NULL
  字符比较时按照ASCII码对应的值进行比较，比较时按照字符顺序逐一比较
  BETWEEN AND会选取介于两个值之间的数据范围，可以是数值、文本或者日期，且是闭区间查询；两个临界值不能调换位置，只能大于等于左边小于等于右边
  LIKE中%可以匹配一个到多个字符，_可以匹配任意一个字符
• 逻辑查询运算符： AND、 OR、 NOT
  IN列表的值类型必须一致或者兼容，不支持通配符

关于NULL值的坑

• 查询运算符、LIKE、BETWEEN AND、IN、NOT IN对于NULL值查询不起作用，无法查询值为NULL的记录
• 查询值为NULL的记录需要使用IS NULL、IS NOT NULL
• <=>（安全等于）既可以判断NULL值又可以判断普通数值，但可读性较低不常用，不建议使用
• 创建表时应尽量设置表字段不能为空，给字段设置默认值

排序和分页

select column_name from table_name order by column1 [asc | desc [, column2 [asc | desc]]];
#按照函数进行排序
select id 编号, birth 出生日期, year(birth) 出生年份, name 姓名 from student order by year(birth);

#offset表示偏移量也即跳过的行数，可以省略；count查询的记录数
select column_name from table_name limit [offset,] count;
#分页查询
select column_name from table_name limit (page-1)*pagesize, pagesize;

• limit中不能使用表达式，两个数值不能为负
• 当排序使用的字段出现相同的值时，如果没有其他排序规则可能会出现相邻的几页出现相同数据的情况应再指定一个排序规则（如主键排序）消除二义性

分组查询

select column_name, 聚合函数,... from table_name [where...] group by... [having...];
#聚合函数：max、min、count、sum、avg

#where&group by & having & order by & limit
select column_name from table_name where... group by... having... order by... limite...;

分组中select后面的列只能是出现在group by后面的列或者使用聚合函数的列；sql_mode中包含了ONLY_FULL_GROUP_BY表示select后面的列必须符合前面两点规范，删除ONLY_FULL_GROUP_BY后select后面可以加任意列

where和having的区别

• where在分组前对记录进行筛选，而having是在分组结束后的结果中筛选
• having后可以跟聚合函数，且不必与select后面的聚合函数相同

MySQL常用函数汇总

数值型函数

select abs(5), abs(-2.5), abs(0);
select sqrt(25), sqrt(2), sqrt(-9);
select mod(6, 4), mod(10, 10), mod(5.5, 5);
select ceil(-2.1), ceil(2.1);
select floor(5), floor(5.5), floor(-5.5);
select rand(), rand(), rand(1), rand(1), rand(2);
select round(-5.6), round(-5.4), round(-5.5), round(3.3);
select sign(-5), sign(0), sign(3);
select pow(5, -2), pow(2, 3), pow(100, 0);

字符串函数

select length('hello'), length('java'), length('架构师');
select concat('hello ', 'world'), concat(null, 'what?');
select insert('hello world', 7, 5, 'jojo') r1, insert('hello world', -1, 4, '*') r2, insert('hello world', 4, 50, '*') r3;
select lower('HELLO 你好'), upper('hello world');
select left('hello world', 4), right('hello world', 4), right('abcde', -1);
select trim(' is there spaces in this string? ');
select replace('hello laogong', 'laogong', 'laowang');
select substr('apple', 3), substring('apple', -3), substr('banana', 2, 3), substring('strawberry', -2, 5), substr('peach' from 2), substring('pineapple' from 4 for 5);
select reverse('white'), reverse('black');

日期和时间函数

聚合函数

流程控制函数

if、ifnull、case

if(expression, value1, value2)

ifnull(value1, value2)

case <expression>
      when <value1> then <operation1>
      when <value2> then <operation2>
      ...
      else <operation>
end case;
case 
      when <condition1> then <operation1>
      when <condition2> then <operation2>
      ...
      else <operation>
end case;

其他函数

连接查询及原理

笛卡尔积

表示两个集合A、B中元素任意组合产生的所有可能结果

//Java表示
for(Object aElement : aCollection) {
      for(Object bElement : bCollection) {
            System.out.println(aElement + ", " + bElement);
      }
}

#SQL语法
select columns from table1, table2 [, tablen...];
select columns from table1 join table2 [join tablen...];

内连接

select columns from table1 inner join table2 on join_condition;
select columns from table1 join table2 on ...;
select columns from table1, table2 where join_condition;

//Java表示
for(Object aElement : aCollection) {
      for(Object bElement : bCollection) {
            if(连接条件) {
                  System.out.println(aElement + ", " + bElement);
            }
      }
}

外连接

查询结果 = 内连接结果 + 主表中有而内连接结果中没有的记录

• 左外连接
• 右外连接

Java实现连接查询

package join;

import org.junit.Test;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.stream.Collectors;

public class Test1 {

    public static class Table1 {
        int a;

        public int getA() {
            return a;
        }

        public void setA(int a) {
            this.a = a;
        }

        public Table1(int a) {
            this.a = a;
        }

        @Override
        public String toString() {
            return "Table1{" +
                    "a=" + a +
                    '}';
        }

        public static Table1 build(int a) {
            return new Table1(a);
        }
    }

    public static class Table2 {
        int b;

        public int getB() {
            return b;
        }

        public void setB(int b) {
            this.b = b;
        }

        public Table2(int b) {
            this.b = b;
        }

        public static Table2 build(int b) {
            return new Table2(b);
        }

        @Override
        public String toString() {
            return "Table2{" +
                    "b=" + b +
                    '}';
        }
    }

    public static class Record<R1, R2> {
        R1 r1;
        R2 r2;

        public R1 getR1() {
            return r1;
        }

        public void setR1(R1 r1) {
            this.r1 = r1;
        }

        public R2 getR2() {
            return r2;
        }

        public void setR2(R2 r2) {
            this.r2 = r2;
        }

        public Record(R1 r1, R2 r2) {
            this.r1 = r1;
            this.r2 = r2;
        }

        @Override
        public String toString() {
            return "Record{" +
                    "r1=" + r1 +
                    ", r2=" + r2 +
                    '}';
        }

        public static <R1, R2> Record<R1, R2> build(R1 r1, R2 r2) {
            return new Record<>(r1, r2);
        }
    }

    public static enum JoinType {
        innerJoin, leftJoin
    }

    public static interface Filter<R1, R2> {
        boolean accept(R1 r1, R2 r2);
    }

    public static <R1, R2> List<Record<R1, R2>> join(List<R1> table1, List<R2> table2, JoinType joinType, Filter<R1, R2> onFilter, Filter<R1, R2> whereFilter) {
        if(Objects.isNull(table1) || Objects.isNull(table2) || joinType == null) {
            return new ArrayList<>();
        }

        List<Record<R1, R2>> result = new CopyOnWriteArrayList<>();

        //笛卡尔积
        for(R1 r1 : table1) {
            List<Record<R1, R2>> onceJoinResult = joinOn(r1, table2, onFilter);
            result.addAll(onceJoinResult);
        }

        if(joinType == JoinType.leftJoin) {
            List<R1> r1Record = result.stream().map(Record::getR1).collect(Collectors.toList());
            List<Record<R1, R2>> leftAppendList = new ArrayList<>();
            for (R1 r1 : table1) {
                if (!r1Record.contains(r1)) {
                    leftAppendList.add(Record.build(r1, null));
                }
            }
            result.addAll(leftAppendList);
        }
        if (Objects.nonNull(whereFilter)) {
            for (Record<R1, R2> record : result) {
                if (!whereFilter.accept(record.r1, record.r2)) {
                    result.remove(record);
                }
            }
        }
        return result;
    }

    public static <R1, R2> List<Record<R1, R2>> joinOn(R1 r1, List<R2> table2, Filter<R1, R2> onFilter) {
        List<Record<R1, R2>> result = new ArrayList<>();
        for (R2 r2 : table2) {
            if (Objects.nonNull(onFilter) ? onFilter.accept(r1, r2) : true) {
                result.add(Record.build(r1, r2));
            }
        }
        return result;
    }

    @Test
    public void innerJoin () {
        List<Table1> table1 = Arrays.asList(Table1.build(1), Table1.build(2), Table1.build(3));
        List<Table2> table2 = Arrays.asList(Table2.build(3), Table2.build(4), Table2.build(5));

        join(table1, table2, JoinType.innerJoin, null, null).forEach(System.out::println);
        System.out.println("------------------");
        join(table1, table2, JoinType.innerJoin, (r1, r2) -> r1.a == r2.b, null).forEach(System.out::println);
    }

    @Test
    public void leftJoin() {
        List<Table1> table1 = Arrays.asList(Table1.build(1), Table1.build(2), Table1.build(3));
        List<Table2> table2 = Arrays.asList(Table2.build(3), Table2.build(4), Table2.build(5));

        join(table1, table2, JoinType.leftJoin, null, null).forEach(System.out::println);
        System.out.println("------------------");
        join(table1, table2, JoinType.leftJoin, (r1, r2) -> r1.a > 10, null).forEach(System.out::println);
    }

}

上面代码的执行结果与MySQL查询结果存在顺序上的不一致，原因：

上面java代码中两个表的连接查询使用了嵌套循环，外循环每执行一次，内循环的表都会全部遍历一次，如果放到mysql中，就相当于内表（被驱动表）全部扫描了一次（一次全表io读取操作），主表（外循环）如果有n条数据，那么从表就需要全表扫描n次，表的数据是存储在磁盘中，每次全表扫描都需要做io操作，io操作是最耗时间的，如果mysql按照上面的java方式实现，那效率肯定很低。

MySQL对于连接查询的优化：

msql内部使用了一个内存缓存空间，就叫他 join_buffer 吧，先把外循环的数据放到 join_buffer 中，然后对从表进行遍历，从表中取一条数据和 join_buffer 的数据进行比较， 然后从表中再取第2条和 join_buffer 数据进行比较，直到从表遍历完成，使用这方方式来减少 从表的io扫描次数，当 join_buffer 足够大的时候，大到可以存放主表所有数据，那么从表只需 要全表扫描一次（即只需要一次全表io读取操作）。MySQL中这种方式叫做Block Nested Loop。

package join;

import org.junit.Test;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.stream.Collectors;

public class Test2 {

    public static int joinBufferSize = 10000;
    public static List<?> joinBufferList = new ArrayList<>();

    public static class Table1 {
        int a;

        public int getA() {
            return a;
        }

        public void setA(int a) {
            this.a = a;
        }

        public Table1(int a) {
            this.a = a;
        }

        @Override
        public String toString() {
            return "Table1{" +
                    "a=" + a +
                    '}';
        }

        public static Table1 build(int a) {
            return new Table1(a);
        }
    }

    public static class Table2 {
        int b;

        public int getB() {
            return b;
        }

        public void setB(int b) {
            this.b = b;
        }

        public Table2(int b) {
            this.b = b;
        }

        public static Table2 build(int b) {
            return new Table2(b);
        }

        @Override
        public String toString() {
            return "Table2{" +
                    "b=" + b +
                    '}';
        }
    }

    public static class Record<R1, R2> {
        R1 r1;
        R2 r2;

        public R1 getR1() {
            return r1;
        }

        public void setR1(R1 r1) {
            this.r1 = r1;
        }

        public R2 getR2() {
            return r2;
        }

        public void setR2(R2 r2) {
            this.r2 = r2;
        }

        public Record(R1 r1, R2 r2) {
            this.r1 = r1;
            this.r2 = r2;
        }

        @Override
        public String toString() {
            return "Record{" +
                    "r1=" + r1 +
                    ", r2=" + r2 +
                    '}';
        }

        public static <R1, R2> Record<R1, R2> build(R1 r1, R2 r2) {
            return new Record<>(r1, r2);
        }
    }

    public static enum JoinType {
        innerJoin, leftJoin
    }

    public static interface Filter<R1, R2> {
        boolean accept(R1 r1, R2 r2);
    }

    public static <R1, R2> List<Record<R1, R2>> join(List<R1> table1, List<R2> table2,
                                                     JoinType joinType, Filter<R1, R2> onFilter, Filter<R1, R2> whereFilter) {
        if(Objects.isNull(table1) || Objects.isNull(table2) || joinType == null) {
            return new ArrayList<>();
        }

        List<Record<R1, R2>> result = new CopyOnWriteArrayList<>();

        int table1Size = table1.size();
        int fromIndex = 0, toIndex = joinBufferSize;
        toIndex = Integer.min(table1Size, toIndex);
        while (fromIndex < table1Size && toIndex <= table1Size) {
            joinBufferList = table1.subList(fromIndex, toIndex);
            fromIndex = toIndex;
            toIndex += joinBufferSize;
            toIndex = Integer.min(table1Size, toIndex);

            List<Record<R1, R2>> blockNestedLoopResult = blockNestedLoop((List<R1>) joinBufferList, table2, onFilter);
            result.addAll(blockNestedLoopResult);
        }

        if (joinType == JoinType.leftJoin) {
            List<R1> r1Record = result.stream().map(Record::getR1).collect(Collectors.toList());
            List<Record<R1, R2>> leftAppendList = new ArrayList<>();
            for (R1 r1 : table1) {
                if (!r1Record.contains(r1)) {
                    leftAppendList.add(Record.build(r1, null));
                }
            }
            result.addAll(leftAppendList);
        }
        if (Objects.nonNull(whereFilter)) {
            for (Record<R1, R2> record : result) {
                if (!whereFilter.accept(record.r1, record.r2)) {
                    result.remove(record);
                }
            }
        }
        return result;
    }

    public static <R1, R2> List<Record<R1, R2>> blockNestedLoop(List<R1> joinBufferList, List<R2> table2, Filter<R1, R2> onFilter) {
        List<Record<R1, R2>> result = new ArrayList<>();
        for (R2 r2 : table2) {
            for (R1 r1 : joinBufferList) {
                if (Objects.nonNull(onFilter) ? onFilter.accept(r1, r2) : true) {
                    result.add(Record.build(r1, r2));
                }
            }
        }
        return result;
    }

    @Test
    public void innerJoin() {
        List<Table1> table1 = Arrays.asList(Table1.build(1), Table1.build(2), Table1.build(3));
        List<Table2> table2 = Arrays.asList(Table2.build(3), Table2.build(4), Table2.build(5));

        join(table1, table2, JoinType.innerJoin, null, null).forEach(System.out::println);
        System.out.println("-----------------------");
        join(table1, table2, JoinType.innerJoin, (r1, r2) -> r1.a == r2.b, null).forEach(System.out::println);
    }

    @Test
    public void leftJoin() {
        List<Table1> table1 = Arrays.asList(Table1.build(1), Table1.build(2), Table1.build(3));
        List<Table2> table2 = Arrays.asList(Table2.build(3), Table2.build(4), Table2.build(5));

        join(table1, table2, JoinType.leftJoin, (r1, r2) -> r1.a == r2.b, null).forEach(System.out::println);
        System.out.println("-----------------------");
        join(table1, table2, JoinType.leftJoin, (r1, r2) -> r1.a > 10, null).forEach(System.out::println);
    }
}

以上内容源自微信：itsoku的《MySQL笔记》，本随笔仅供学习交流

内容总结

以上是互联网集市为您收集整理的MySQL核心知识概要（二）全部内容，希望文章能够帮你解决MySQL核心知识概要（二）所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端