MySQL核心知识概要（二）
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常用函数汇总
数值型函数
| 函数名称 | 作用 |
|---|---|
| abs(n) | 求绝对值 |
| sqrt(n) | 求二次方根 |
| mod(m, n) | 求余数 |
| ceil(n)、ceiling(n) | 返回不小于参数的最小整数，也即向上取整 |
| floor(n) | 向下取整，返回值转化为BIGINT |
| rand() | 生成一个0~1之间的随机数，传入整数参数用来产生重复序列 |
| round(n) | 对所传参数进行四舍五入 |
| sign(n) | 返回参数的符号 |
| pow(x, y)、power(x, y) | 计算所传参数的次方结果值 |
| sin(n)、asin(n) | 求正弦、反正弦 |
| cos(n)、acos(n) | 求余弦、反余弦 |
| tan(n)、atan(n) | 求正切、反正切 |
| cot(n) | 求余切 |
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);
字符串函数
| 函数名称 | 作用 |
|---|---|
| length(s) | 计算字符串长度，返回字节长度 |
| concat(s1, s2...) | 合并字符串函数，若有任何一个参数为NULL则返回值为NULL，若参数中含有任一二进制字符串，则结果为一个二进制字符串 |
| insert(s, r) | 替换字符串函数 |
| lower(s) | 将字符串中的字母转换为小写 |
| upper(s) | 将字符串中的字母转换为大写 |
| left(s, n) | 从左侧截取字符串，返回字符串左边的若干字符 |
| right(s, n) | 从右侧截取字符串，返回字符串右边的若干字符 |
| trim(s) | 删除字符串左右两侧的空格 |
| replace(s, r) | 字符串替换函数，返回替换后的新字符串 |
| substr(s, m, n)、substring() | 截取字符串，返回从指定位置开始的指定长度的字符串 |
| reverse(s) | 字符串反转函数 |
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');
日期和时间函数
| 函数名称 | 作用 |
|---|---|
| curdate()、current_date() | 返回当前系统日期 |
| curtime()、current_time() | 返回当前系统时间 |
| now()、sysdate() | 返回当前系统的日期和时间值 |
| unix_timestamp() | 返回一个以UNIX时间戳为基础的无符号整数 |
| from_unixtime() | 将UNIX时间戳转换为时间格式 |
| month() | 获取指定日期中的月份 |
| monthname() | 获取指定日期中月份英文名称 |
| dayname() | 获取指定日期对应的星期几英文名 |
| dayofweek() | 获取指定日期是一周中第几天，返回值范围1~7 |
| week() | 获取指定日期是一年中的第几周，范围为052或153 |
| dayofyear() | 获取指定日期是一年中的第几天，范围1~366 |
| dayofmonth() | 获取指定日期是一月中的第几天，范围1~31 |
| year() | 获取年份，范围1970~2069 |
| time_to_sec() | 将时间转换为秒 |
| sec_to_time() | 将秒转换为时间 |
| date_add()、adddate() | 向日期添加指定的时间间隔 |
| date_sub()、subdate() | 向日期减去指定的时间间隔 |
| addtime() | 在原时间上添加指定的时间 |
| subtime() | 在原时间上减去指定的时间 |
| datediff() | 获取两个日期之间的间隔，参数一减去参数二 |
| date_format() | 格式化指定的日期 |
| weekday() | 获取指定日期在一周内的对应工作日索引 |
聚合函数
| 函数名 | 作用 |
|---|---|
| max | 查询指定列的最大值 |
| min | 查询指定列的最小值 |
| count | 统计查询结果的行数 |
| sum | 求和，返回指定列的总和 |
| avg | 求平均值 |
流程控制函数
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;
其他函数
| 函数名 | 作用 |
|---|---|
| version | 数据库版本 |
| database | 当前数据库名称 |
| user | 当前连接的用户名 |
| password | 返回字符串密码形式（8以后版本已废弃） |
| md5 | 返回字符串的md5加密 |
连接查询及原理
笛卡尔积
表示两个集合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笔记》，本随笔仅供学习交流
浙公网安备 33010602011771号