Github Page

mysql

2020/10/09 数据库 共 2658 字，约 8 分钟

MySQL数据库

配置

  1. docker+mysql8
     # 启动容器挂载配置
 docker run -d -p 3306:3306 -v /home/ys/mysql/conf.d:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456 -e TZ=Asia/Shanghai --name mysql mysql:8
 chmod 644 /home/ys/mysql/conf.d/mysql.cnf
 chmod 644 /home/ys/mysql/conf.d/docker.cnf
    
 # 进入容器后换源
 sed -i 's/deb.debian.org/mirrors.ustc.edu.cn/g' /etc/apt/sources.list
 apt-get update && apt-get upgrade
 apt-get indtall vim
    
 # 修改配置文件
 vi /etc/mysql/conf.d/mysql.cnf
 # 添加
 [client]
 default-character-set = utf8mb4
    
 [mysql]
 default-character-set = utf8mb4
    
 [mysqld]
 character_set_server = utf8mb4
 default-time-zone = '+08:00' # 修改时区

only_full_group_by

  1. order by后面的列必须是在select后面存在的

  2. select、having或order by后面存在的非聚合列必须全部在group by中存在

  3. 关闭方法

    # mysql命令行
SET @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

引擎

  1. MyISAM：全文索引，B+树索引，数据压缩

  2. Memory：B+树索引，hash索引，数据索引

  3. InnoDB：支持事务，外键，B+树索引，数据索引，集群索引

索引

B+树索引

  1. B+树的优点：非叶子节点不存数据，扇出数量多，可存数据量大； 三层即可达到千万数据规模，便于单行查询
    叶子节点形成有序链表，便于范围查询

  2. 主键索引：InnoDB以主键为索引
    B+树聚簇索引，叶节点data域保存了完整的数据（MyISAM是非聚簇索引）
    建议使用自增主键，非单调的主键在插入时为了维持B+树特性会频繁调整
    不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引

  3. 辅助索引：非聚簇索引，叶节点data域保存主键
    最左前缀，每个字段都默认升序

  4. 覆盖索引：并非具体的索引类型，而是一种概念
    如果要查询的字段值在辅助索引上存在，就不用再查聚集索引

操作索引

# 添加索引
create index index_name on table_name(column_name1,column_name2);
# 删除索引
drop index index_name on table_name;

sql命令

  1. 判断null
    ‘a’ IS NOT NULL相当于NOT(‘a’ <=> NULL)

  2. 去重
    select distinct c1 from t;

  3. union all将所有结果合并，union去重合并
    select * from t where c1=’1’ union all select * from t where c5=’2’;

  4. in和exists，用来筛选主查询某条记录中的字段是否在子查询中，可用于去重
    exists的子查询只要查到一条数据，就立刻返回true，不用遍历所有的数据
    in的子查询需要查所有结果，效率比exists低
    select name from user where id in(select uid from indent);
    select name from user where exists(select 1 from indent where uid=user.id);

  5. into，查询完再插入另一张表
    select u.name,u.age into people from user u where u.id=1;

  6. 统计
    group by进行分组，having进行过滤（where后面不能使用统计函数）
    select u.name,sum(i.bill) from user u join indent i on u.id=i.uid group by u.name having sum(i.bill)>2

  7. 分页查询

    1. 查主键无条件
      select oid from indent limit 2,3;
    2. 查主键有条件
      create index uid_oid_index on indent(uid,oid);
      select oid from indent where uid=1 limit 2,3;
    3. 查多字段
      select * from indent where oid>=(select oid from indent limit 2,1)limit 3;
      select * from indent a join (select oid from indent limit 2,3)b on a.oid=b.oid;

多表查询

内连接

  1. 显式内连接，速度比隐式快
    select * from t1 join t2 on t1.a=t2.a;

  2. 自然连接，字段名相同的列必须完全相同，才会被筛选出来
    select * from t1 natural join t2;

  3. 自连接，一张表当两张表用
    select b.* from user a join user b on a.name=”一” and a.age<b.age;

外连接

  1. 左连接，左表返回所有行，右表无数据则为null
    select * from t1 left join t2 on t1.a = t2.a;

  2. 右连接，右表返回所有行，左表无数据则为null
    select * from t1 right join t2 on t1.a = t2.a;

  3. 全连接，返回所有数据，无数据则为null
    select * from t1 full join t2 on t1.a = t2.a;

交叉连接

两张表每行数据的所有组合情况
select * from t1 cross join t2;

sql优化

  1. 在where及order by涉及的列上建立索引

  2. 避免在where子句中对字段进行null值判断，否则无法使用索引，可以通过设置默认值实现

  3. 避免在where子句中使用or来连接条件（同一个字段且最左前缀才会使用索引），用union all代替

  4. 对于连续的数值，用between

  5. 避免在where子句中对字段进行函数或表达式操作

  6. 若只含数值信息的字段尽量不要设计为字符型，因为字符需要更大空间

  7. 模糊查询尽量使用like ‘a%’，否则无法使用索引

文档信息

Search

    Table of Contents