MYSQL

MYSQL的思维导图，从数据库的概念、DDL/DML、DQL、DCL这几个方面作了详细的归纳与整理。

mysql

• 数据库概念
  • 数据库相关概念
    • DB（数据库）
      • 按照数据结构来组织、存储和管理数据的仓库
    • DBMS（数据库管理系统）
    • DBA（数据库管理员）
  • 数据库的分类
    • 关系型数据库
      • 释义
        • 关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
      • 表现形式
        • 以表的形式存储
      • 实例
        • oracle，access，sql server，MySQL
    • 非关系型数据库
      • 表现形式
        • 以键值对：key-value方式存储
      • 实例
        • mangoDB、redis、memcache
  • mysql数据库的引擎
    • 数据库存储引擎：5.6版本之后的默认为InnoDB存储，5.5版本之前的默认是MyISAM存储引擎： 1.第一种myisam：三个文件 （1）表结构存文件、数据文件、搜索目录三个文件都存放在硬盘上 （2）应用于以读和插入为主的操作，很少的更新和删除 （2）不支持事务安全、不支持外键、不支持行级锁,表级锁严重影响并发 2.第二种innodb:两个文件 （1）表结构文件、数据和搜索目录文件都存放在硬盘上 （2）支持事务安全、支持外键、支持行级锁、表级锁 3.第三种memory:两个文件 （1）表结构存储在硬盘，数据存储在内存 （2）读写速度快，关机会导致内存数据的丢失，多用于热新闻 查看当前的默认存储引擎: mysql> show variables like "default_storage_engine"; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | default_storage_engine | InnoDB | +------------------------+--------+ 1 row in set (0.00 sec) 查询当前数据库支持的存储引擎： mysql> show engines \G;
    • InnoDB
      • 5.6之后的版本默认，两个文件存储在硬盘：表结构和带有数据内容的索引；支持事务、行级锁、表级锁、支持外键
    • MyISAM
      • 5.5及以前版本默认，三个文件存储在硬盘：表结构、索引目录、数据内容；不支持事务、不支持行级锁、表解锁降低并发效率、不支持外键
    • Memory
      • 两个文件：表结构在硬盘，数据在内存，关机丢失，可用于热新闻等
  • SQL语言共分为四大类
    • 数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。
• DDL/DML
  • 数据库操作
    • 创建数据库
      • -- create database 数据库名 charset=utf8 collate=utf8_general_ci；
    • 查看数据库
      • -- show create database 数据库名；
    • 使用数据库
      • -- use 数据库名；
    • 查看当前数据库
      • -- 查看当前数据库: select database() ；
    • 修改数据库
      • -- alter database 数据库名 default character set 编码格式 default collate 排序规则；
    • 删除数据库
      • -- drop database 数据库名;
    • 查看所有数据库
      • show databases;
  • 约束
    • 主键（primary key）
      • 能唯⼀标识表中的每⼀条记录的属性组
    • ⾮空（not null）
      • 此字段不允许填写空值
    • 唯⼀（unique）
      • 此字段的值不允许重复
    • 默认值（default）
      • 当不填写此值时会使⽤默认值，如果填写时以填写为准
    • 外键（foreign key）
      • ⼀个表中的⼀个字段引⽤另⼀个表的主键
  • 数据类型
    • 整数: int
      • 有符号范围（-2147483648 ~2147483647），⽆符号 （unsigned）范围（0 ~4294967295），可进行无符号设置无负数
    • ⼩数: decimal
      • 例如：decimal(5,2) 表示共存5位数，⼩数占2位，整数占3 位
    • 字符串: varchar
      • 范围（0~65533），例如：varchar(3) 表示最多存3个字 符，⼀个中⽂或⼀个字⺟都占⼀个字符
    • ⽇期时间: datetime
      • 范围（1000-01-01 00:00:00 ~ 9999-12-31 23:59:59），例如：'2020-01-01 12:29:59'
  • 数据表操作
    • 创建表
      • -- create table 表名( 字段名 类型 约束， ... )ENGINE=InnoDB DEFAULT CHARSET=utf8;
      • 拓展判断表是否存在, 存在时先删除再创建：drop table if exists students;
    • 插入数据
      • --insert into table_name ( field1, field2,...fieldn ) values ( value1, value2,...valuen );
      • 多行：-- insert into 表名(列1,...) values(值1,...),(值1,...)...
    • 修改数据
      • -- update 表名 set 列1=值1,列2=值2... where 条件
    • 删除数据
      • -- delete from 表名 where 条件;
    • 拓展删除方式
      • -- delete from 表名 : 删除所有数据, 但是不重置主键字段的计数 -- truncate table 表名 : 删除所有数据, 并重置主键字段的计数 -- drop table 表名 : 删掉表(字段和数据均不再存在)
• DQL
  • 多表查询
    • 内连接查询
      • 隐式内连接
        • 使用where 条件
      • 显式内连接
        • select 字段列表 from 表名1 inner join 表名2 on 条件 (inner)可以省略
    • 外连接查询
      • 左外连接
        • select 字段列表 from 表1 left [outer] join 表2 on 条件
      • 右外连接
        • select 字段列表 from 表1 right [outer] join 表2 on 条件
    • 子查询
      • 子查询的结果是单行单列的
        • 子查询可以作为条件，使用运算符去判断。
      • 子查询的结果是多选单列的
        • 子查询可以作为条件，使用运算符in来判断
      • 子查询的结果是多选多列的
        • 子查询可以作为一张虚拟表参与查询
  • • 排序查询 order by
      • *order by 排序字段1 排序方式1,排序字段2 排序方式2
        • ASC正序
        • DESC倒序
    • 查询 where
      • select 字段名1,字段名2。。。from表名，
      • 去除重复
        • select distinct 列名 from表名
      • ifnull
      • AS 起别名
      • 条件查询
        • *BETWEEN。。。AND
        • *IN集合
        • *>、<、<=、>=、=、<>不等于(==)
        • *is null
        • *and 或&&
        • *or 或||
        • *not 或!
      • 模糊查询like
        • *占位符： * _：单个任意字符 *%：多个任意字符
    • 分组查询 group by having
      • group by 分组字段;
      • where 和 having的区别？
        • 1.where 在分组之前进行限定，如果不满足条件，不参与分组 Having是在分组之后进行限定,如果不满足,就不会被查询出来 2.where后不可跟聚合函数,having可以进行聚合函数的判断。
    • 聚合函数
      • count
      • max
      • min
      • sum
      • avg
    • 分布查询 limit
      • limit 开始的索引，每页查询的条数;
        • 分页公式：开始的索引 = (当前的页码-1) *每页显示的条数
• DCL
  • 用来定义访问权限和安全级别。数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。
  • GRANT：授权。 ROLLBACK [WORK] TO[SAVEPOINT]：回滚命令，回退到某一点。回滚命令使数据库状态回到上次最后提交的状态。其格式为：SQL>ROLLBACK; COMMIT [WORK]：提交。
  • 管理用户
    • 添加用户
      • CREATE USER '用户名' @'主机名' IDENTIFIED BY '密码'
      • CREATE USER '用户名' @'%(任意主机)' IDENTIFIED BY '密码'
    • 删除用户
      • DROP USER '用户名'@'主机名';
    • 修改用户密码
      • SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
    • 查询用户
      • SELECT *FROM USER;
  • 权限管理
    • 查询权限
      • SHOW GRANTS FOR '用户名'@'主机名';
    • 授予权限
      • GRANT 权限列表,权限列表 ON 数据库.表名 TO '用户名'@'主机名';
    • 撤销权限
      • REVOKE 权限列表,权限列表 ON 数名库.表名 FROM '用户名' @'主机名';