mysql

这是一篇关于mysql的思维导图，知识点系统且全面，希望对大家有用。感兴趣的小伙伴可以收藏一下！

mysql

• DDL 数据定义语言
  • 库
    • create database if not exists db2
    • select database()
    • use 库名
    • show create database XD;
  • 表
    • 常见数据类型
      • 整数
        • TINYINT 1 字节
        • SMALLINT 2 字节
        • MEDIUMINT 3 字节
        • INT 4 字节
        • BIGINT 8 字节（(0，2的64次方减1)）
      • 浮点型
        • FLOAT(m,d） 4 字节 单精度浮点型
        • DOUBLE(m,d） 8 字节 双精度浮点型
      • 定点型
        • DECIMAL(m,d）
      • 字符串类型
        • CHAR 0-255字节 定长字符串
        • VARCHAR 0-65535字节 变长字符串
        • TINYTEXT 0-255字节 短文本字符串
        • TEXT 0-65535字节 长文本数据
        • MEDIUMTEXT 0-16777215字节 中等长度文本数据
        • LONGTEXT 0-4294967295字节 极大文本数据
        • 备注：char的优缺点：存取速度比varchar更快，但是比varchar更占用空间
      • 时间型
        • date 3 yyyy-MM-dd
        • time 3 HH:mm:ss
        • year 1 yyyy
        • datetime 8 yyyy-MM-dd HH:mm:ss
        • timestamp 4 yyyy-MM-dd HH:mm:ss 存储日期+时间，可作时间戳
    • 创建表
      • CREATE TABLE 表名 ( 字段名 类型 约束条件);
      • create table 新表名 like 旧表名;
      • 查看数据库中的所有表：show tables；
      • 查看表结构：desc 表名;
      • 查看创建表的sql语句：show create table 表名;
      • \G :有结束sql语句的作用，还有把显示的数据纵向旋转90度
      • \g :有结束sql语句的作用
    • 约束条件
      • comment ----说明解释
      • not null ----不为空
      • default ----默认值
      • unsigned ----无符号（即正数）
      • auto_increment ----自增
      • zerofill ----自动填充
      • unique key ----唯一值
    • 表结构维护
      • 修改表名
        • rename table 旧表名 to 新表名
      • 添加列
        • alter table add 列名 类型 comment '说明'
        • 给表最前面添加一列：alter table 表名 add 列名 类型 first;
        • 给表某个字段后添加一列：alter table 表名 add 列名 类型 after 字段名;
      • 修改列类型
        • alter table 表名 change 旧列名 新列名 类型;
      • 删除列
        • alter table 表名 drop 列名;
      • 修改字符集
        • alter table 表名 character set 字符集;
      • 删除
        • drop table (if exists) 表名
• DML 数据操纵语言
  • 新增
    • insert into 表名（字段名） values（字段对应值）
    • 蠕虫复制（将一张表的数据复制到另一张表中）
      • insert into 表名1（字段名1，字段名2） select 字段名1，字段名2 from 表名2;
      • insert into 表名1（字段名1，字段名2） select 字段名1，字段名2 from 表名2;
    • 建表复制
      • create table 表名1 as select 字段名1，字段名2 from 表名2
    • 插入多个数据
      • insert into 表名 (字段名) values (对应值1),(对应值2),(对应值3)
  • 修改
    • update 表名 set 字段名1=值1 where 字段名=值
  • 删除
    • delete from 表名 where 字段名=值
    • 备注：delele 会把删除的操作记录给记录起来，以便数据回退，不会释放空间，而且不会删除定义。 truncate不会记录删除操作，会把表占用的空间恢复到最初，不会删除定义 drop会删除整张表，释放表占用的空间。速度： drop > truncate > delete
• DQL 数据查询语言
  • where
    • 简单查询
      • select * from employee
    • 精确查询
      • select * from employee where sal != 50000
    • 模糊查询
      • select * from employee where ename like '林%'
    • 范围查询
      • select * from employee where sal between 10000 and 30000
    • 离散查询
      • select * from employee where ename in ('猴子','林俊杰','小红','小胡'
    • 清除重复值
      • select distinct(job) from employee
    • 统计查询（聚合函数）
      • count(code)或者count(*)
      • sum(sal) 计算总和
      • max() 计算最大值
      • avg() 计算平均值
      • min() 计算最低值
      • concat函数： 起到连接作用
  • 分组查询
    • group by 列1，列2....列N
    • having（筛选）
      • group by job having job ='文员'
  • 排序查询
    • select * from employee order by sal
    • where ---- group by ----- having ------ order by
  • limit限制查询
    • limit 4,5 4开始 ，取出5条
  • exists
    • exists跟 not exists
      • 返回boolean
  • 外连接查询
    • 左连接
      • left join 表名 on 条件 / left outer 表名 join on 条件
        • 左表记录全部， 右表 符合条件记录
    • 右连接
      • right join 表名 on 条件/ right outer 表名 join on 条件
        • 右表记录全部， 左表 符合条件记录
  • 内连接
    • INNER JOIN 表名 ON 条件
  • 联合查询
    • ... UNION ... （去除重复）
    • ... UNION ALL ...（不去重复）
    • 例：(select * from employee a where a.job = '销售员' order by a.sal limit 999999 ) union (select * from employee b where b.job = '文员' order by b.sal desc limit 999999)
• DCL 数据控制语言
  • 权限
    • select user,host from mysql.user
    • 刷新权限：flush privileges
  • 修改密码
    • set password for root@localhost = password('mima')
    • mysqladmin -u用户 -p旧密码 password 新密码
    • update mysql.user set authentication_string=password('密码') where user='用户' and host='ip'
  • 忘记密码
    • ① my.cnf (默认在/etc/my.cnf)，在[mysqld]下面加上 skip-grant-tables
    • ②重启mysql服务
    • ③mysql -uroot -p 无需密码登录进入
    • ④修改密码
  • 创建用户
    • create user 'username'@'host' identified by 'password'
  • 权限设置
    • 授权
      • 已有用户pig用户所有库表权限：grant all privileges on *.* to 'pig'
      • 新用户：grant select,update on XD.employee to 'cat'@'%' identified by '123456' 创建新用户，授予xd.exployee表的select，update权限
      • all privileges 所有权限
      • . 所有库表
    • 回收权限
      • revoke all privileges on *.* from 'pig' @ '%'
• 备份
  • 分类
    • 完全备份
    • 部分备份
      • 增量备份：
        • 是以上一次备份为基础来备份变更数据
      • 差异备份：
        • 是以第一次完全备份的基础来备份
  • 备份的方式
    • 逻辑备份
      • 直接生成sql语句，执行备份的sql恢复数据的时候
    • 物理备份
      • 直接拷贝相关的物理数据
  • 备份的场景
    • 热备份
      • 备份时，读写都可
    • 温备份
      • 读可，写不能
    • 冷备份
      • 不能进行任何操作
  • mysqldump备份（跨机器）
    • 备份
      • mysqldump -u 用户 -h host -p 密码 dbname table > 路径
      • 单库
        • mysqldump -uroot -pabc123456 -h120.25.93.69 zabbix | gzip > /mysql_data_back/zabbix_users.sql.gz
      • 多库
        • mysqldump -uroot -pabc123456 -h120.25.93.69 --databases zabbix XD | gzip > /mysql_data_back/zabbix_XD.sql.gz
      • 全库
        • mysqldump -uroot -pabc123456 -h120.25.93.69 --all-databases | gzip > /mysql_data_back/all.sql.gz
      • 单库例子并保留创建库语句
        • mysqldump -uroot -pabc123456 -h120.25.93.69 --databases zabbix | gzip > /mysql_data_back/zabbix_bak.sql.gz
      • 单表
        • mysqldump -uroot -pabc123456 -h120.25.93.69 zabbix users | gzip > /mysql_data_back/zabbix_users.sql.gz
    • 恢复
      • mysql -uroot -pabc123456 -h120.25.93.69
      • 没有创建库的语句
        • mysql -uroot -pabc123456 -h120.25.93.69 zabbix
      • 有创建库的语句
        • mysql -uroot -pabc123456 -h120.25.93.69 zabbix
    • mysqldump -uroot -p XD user -F > user_bak.sql
      • 加上-F选项可以重新生成一个新的二进制日志文件
  • 二进制日志mysqlbinlog备份
    • 二进制日志
      • 二进制日志
        • mysql数据库中的一些写入性操作，增删改
      • 是否开启二进制
        • show variables like 'log_bin%'
        • 开启二进制日志 : vi /etc/my.cnf
          • [mysqld] log-bin=/data/mydata/log_bin/mysql-bin server-id=1
      • 查看所有的binlog日志列表
        • show master logs
      • 刷新二进制日志
        • flush logs
      • 重置(清空）二进制日志文件
        • reset master
    • 备份
      • 第一步：日志文件导出成普通文件
        • mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000002 > mysqlbin.sql
      • 第二步：找出要恢复的位置
        • mysqlbinlog --no-defaults mysql-bin.000002 | cat -n | grep -iw 'drop'
      • mysqlbinlog --no-defaults --set-charset=utf8 --stop-position="59674" /data/mydata/log_bin/mysql-bin.000002 | mysql -uroot -p
• 优化
  • 避免使用select *from
  • 避免条件使用or
  • 加上limit 限制行数
• 引擎
  • show engines
  • create table yingqin (id int,name varchar(20)) engine='InnoDB'
  • • alter table 表名 engine='MyiSAm'
  • MyISAM与InnoDB
    • MyISAM
      • 支持全文索引（full text）;不支持事务;表级锁;保存表的具体行数;奔溃恢复不好
    • InnoDB
      • 支持事务;5.6之前的版本是不支持全文索引;行级锁;不保存表 的具体行数;奔溃恢复好
• 索引
  • index：普通索引
    • 允许空（null）
    • 添加索引
      • alter table 表名 add index 索引名称 (字段名称)
      • create index 索引 on 表名 (字段名)
    • 查看索引
      • show index from 表名\G
    • 删除索引
      • drop index 索引名称 on 表名
      • alter table 表名 drop index 索引名
  • unique：唯一索引
    • 允许空（null）；唯一值
    • add unique 索引名称 (字段名称)
  • primary key：主键索引主题
    • drop primary key
  • foreign key：外键索引
    • add foreign key (字段名) references 关联的表名
    • （1）俩个表，主键跟外键的字段类型一定要相同 （2）要使用外键约束表的引擎一定得是InnoDB引擎，MyISAM是不起作用的 （3）在干掉外键索引之前必须先把外键约束删除，才能删除索引
  • fulltext: 全文索引
    • alter table command add fulltext(字段名)
    • select * from 表名 where match (字段名) against ('检索内容')
    • alter table command drop index instruction
    • • 1、一般情况下创建全文索引的字段数据类型为 char、varchar、text 。其它字段类型不可以 • 2、全文索引不针对非常频繁的词做索引。比如is，no，not，you，me，yes这些，我们称之为停止词 • 3、对英文检索时忽略大小写
  • 联合索引
    • alter table test add index(username,servnumber,password)
    • alter table test drop index username
    • 多列数据进行查询时，可以考虑建立联合索引
• 事务
  • 特性
    • 原子性(Atomicity)
      • 事务必须是原子工作单元，一个事务中的所有语句，应该做到：要么全做，要么一个都不 做
    • 一致性(Consistency)
      • 让数据保持逻辑上的“合理性”
    • 隔离性(Isolation)
      • 如果多个事务同时并发执行，但每个事务就像各自独立执行一样
    • 持久性(Durability)
      • 一个事务执行成功，则对数据来说应该是一个明确的硬盘数据更改（而不仅仅是内存中的 变化）
    • 备注：表的引擎要为innodb引擎
  • 操作命令
    • 开始：begin; start transaction;
    • 提交：commit
    • 回滚：rollback
• 命令
  • 启动关闭
    • 启动命令
      • service mysql start
    • 关闭命令
      • service mysql stop
    • 重新启动命令
      • service mysql restart
    • 查看状态命令
      • service mysql status
  • 登录
    • mysql -uroot -p
  • 默认端口号
    • 3306
  • 配置文件
    • /etc/my.cnf
      • 默认引擎、默认提交、跳过权限、二进制日志开启、慢查询开启...