Mysql


数据库基础概念


1、数据分类

  • 结构化数据:一般存储在关系型数据库中
  • 半结构化数据:如:YAML、XML、JSON…
  • 非结构化数据:如:图片、日志文件、;通常存储于NoSQL,文件系统等

2、MySQL数据库的逻辑结构

  • 客户端通过各种MySQL的连接驱动程序连接到MySQL服务器,MySQL服务器的链接线程池与客户端建立连接
  • 连接线程经过诸如判断连接进来的用户身份的认证,权限的识别等处理后,建立连接
  • 查询缓存:MySQL是可以将查询结果进行缓存的,缓存时key/value类型数据,key是查询语句本身的hash值,value是查询语句对应的查询结果,如果用户查询的数据命中了缓存项,则直接从MySQL自身的缓存中响应结果给用户
  • 分析器:对用户执行的操作进行词法分析、语法分析之后,生成多条查询路径,将生成的多条查询路径交给优化器
  • 优化器:从生成的多条查询路径中,选择一个开销最小的路径交给存储引擎
  • 存储引擎:完成对上端的请求和底层的文件系统之间的交互操作

微观结构

3、安装MySQL后的相关工具

  • 服务器端程序:mysqld、mysqld_safe、mysqld_multi
    • 默认情况下启动mysql服务,实际是用的mysql_safe程序启动的服务
  • 客户端程序:mysql、mysqldump、musqladmin
  • 工具程序:myisampack…

4、mysql服务器的配置文件

  • 配置文件查找次序 /etc/mysql/my.cnf—>/etc/my.cnf—> ~/.my.cnf
    由三个文件中的配置组合成真正生效的配置,如果某个配置项在几个文件中都有定义,则后加载的生效

  • 配置文件格式:ini风格的配置文件,能够为mysql的各种应用程序提供配置信息
    [mysqld]
    [mysqld-safe]
    [mysqld-multi]
    [server] :定义在此配置段的,对mysqld、mysqld_safe、mysqld_multi都生效
    [mysql]
    [mysqldump]
    [client] :定义在此配置段的,对mysql、mysqldump等客户端工具都生效

    配置文件的指令一般是: 参数 = 值
    参数中各个单词一般是下划线_连接

5、MySQL支持的数据类型

  • 字符型

    
    
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
      定长字符型:最多只能存储255个字节
          CHAR(数字)  不区分字符大小写,数字表示多少个字节,一个汉字需要2个字节的大小
          BINARY(数字)  区分字符大小写
      变长字符型:最多只能存储65535个字节
          VARCHAR(数字)  不区分字符大小写的变长的字符型
          VARBINARY(数字)  区分字符字符大小写的变长的字符型
      对象存储:
          在数据库表中存储的是指向真实数据所在位置的指针信息
          TEXT  存储文本信息,最大可存储2^32个字节的文本,也就是4G大小的文本,不区分字符大小写
          BLOB  二进制大对象,与TEXT一样,最大可存储2^32个字节的文本,也就是4G大小的文本,区分字符大小写
      内置类型:
          SET  集合类型,在给定的字符中任意字符进行组合
              假设定义了abc,则可填入的数据位a、b、c任意组合
          ENUM  枚举类型,在给定的值中选取某一个
              如:ENUM("aa","bb","cc")
  • 数值型

    
    
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
      精确数值型:
          INT:
              TINYINT  1个字节表示,也就是数字可取范围为0-255或-127~128
              SMALLINT  2个字节表示,表示的最大数字范围为2^16
              MEDIUMEINT  3个字节表示,能表示的最大数值范围为2^24
              INT   4个字节表示 , 能表示的最大数值范围为2^32
              BIGINT  8个字节表示,能表示的最大数值范围为2^64
      近似数值型:
          FLOAT  单精度浮点型
          DOUBLE  双精度浮点型
  • 日期时间型

    
    
    1
    2
    3
    4
    5
      日期型:DATE
      时间型:TIME
      日期时间型:DATETIME
      时间戳计时:TIMESTAMP
      年份:YEAR(2),YEAR(4)
  • 数据类型的修饰符

    
    
    1
    2
    3
    4
    5
    6
      字符类型数据修饰符:
          NOT NULL、NULL、DEFAULT 'STRING'、CHARACET 'CHARSET'(指定字符集)、COLLATION 'collocation'(指定排序规则)
      整型类型数据修饰符:
          NOT NULL、NULL、UNSINGED(表示无符号)、DEFAULT value(设定默认值)、AUTO_INCREMENT(自动增长)
      日期时间型数据修饰符:
          NOT NULL、NULL、DEFAULT

6、mysql命令行客户端工具交互式模式下的客户端命令


1
2
3
4
5
6
7
8
9
10
11
在mysql的交互式命令提示符下用:help来获取相关命令
    \u或use DB_NAME  设定默认使用的数据库
    \q  退出交互式的接口
    \d 符号   设定服务器端命令的语句结束符
    \g  语句结束标记
    \G  语句结束标记,结果是竖排方式显示
    \s  返回服务器端和客户端之间连接的状态,以及服务器状态的信息
    \c  表示取消运行该指令,当一条命令写到一半,不想执行了,可以到后面用\c,相当于取消
    \!SHELL_COMMAND   可以在mysql交互式界面下执行系统的命令
    \. /PATH/TO/SQL_FILE  从shell文件系统中读取SQL命令的文件,到mysql交互式命令行模式下进行执行,前提是运行mysql服务的用户(一般是mysql用户),对该文件有读权限
    \?或help来获取可用的命令列表

7、mysql命令行客户端工具交互式模式下的服务器端管理命令


1
2
3
4
5
6
7
8
9
10
11
12
服务端命令:通过mysql连接发往服务器执行并取回结果
    DDL:数据定义语言,主要用于数据库组件,如:数据库、表、索引、视图、触发器、事件调度器、存储过程、存储函数等
        CREATE ALTER  DROP
    DML:数据操纵语言,主要用于操作表中的数据
        INSERT  DELETE  UPDATE  SELECT
    DCL:数据控制语言
        GRANT  REVOKE
    注意:每个语句必须有语句结束符,默认为;分号
获取命令的帮助:
    在mysql交互式界面下 ,输入HELP COMMAND或? COMMAND即可
        如:HELP CREATE
            HELP CREATE DATABASE

8、MySQL的约束

  • 主键约束:一张表中只能有一个主键、主键的值不能为空,主键的值要唯一
  • 唯一键约束:唯一键的值要唯一,可以存在多个唯一键、唯一键的值可以为空
  • 外键约束:也叫参考性约束,表示当一张表的某个字段存在外键时,此字段的值的取值范围受限于另外的一张表的相应字段的值
  • 检查性约束:如定义字段时NOT NULL、CHAR(10)类似的约束条件

9、MySQL自身的缓存的简单说明

  • MySQL自身是具有查询缓存的:缓存的是查询的执行结果,缓存项是key/value类型:
    key:查询语句的hash值
    value:查询语句的执行结果

  • 查询执行路径:
    请求—>查询缓存
    请求—>查询缓存—>解析器—> 预处理器—>优化器—>查询执行引擎—>存储引擎—>缓存—>响应

  • 为了提高缓存命中率,需要将SQL的编写方式进行统一:
    SELECT name FROM tb1和select name from tb1的缓存项是不一样的,因为缓存项的key是查询语句的hash值,只要查询语句不一样,缓存项就不一样

10、 定义mysqld的工作特性、变量

  • 查看服务器参数/变量:设定MySQL的运行特性:
    
    
    1
    2
    3
      SHOW GLOBAL | [SESSION] VARIABLES [LIKE 条件];
      GLOBAL表示全局变量,对全局生效,临时有效,对当前会话无效,但对下个新建的会话有效
      SESSION表示会话变量,对当前会话有效,临时有效,对其他会话无效
  • 查看状态(统计)参数/变量:保存MySQL运行中的统计数据或状态数据;

    
    
    1
      SHOW GLOBA|[SESSION] STATUS [LIKE 条件];
  • 变量/参数级别:
    全局:为所有会话设定默认;
    会话:跟单个会话相关;会话建立会从全局继承;

  • 服务器变量的调整方式:

    • 运行时修改:
      global:仅对修改后新建立的会话有效;
      session:仅对当前会话有效,且立即生效;
    • 启动前通过配置文件修改:
      重启MySQL服务后生效;
  • 显示单个变量设定值的方法:
    mysql> SELECT @@[global.|session.]system_var_name

    
    
    1
    2
      %:匹配任意长度的任意字符;
      _:匹配任意单个字符;
  • 修改服务器的变量来定义mysql的相关工作特性

    
    
    1
      SET GLOBAL | SESSION var_name=value
    • 修改运行时全局的变量,会即时生效,临时有效,对当前会话无效,只会对新建会话有效
    • 修改运行时的会话的变量,即时生效,临时游侠,对当前会话有效,对其他会话无效
    • 要想永久有效,需要将var_name=value写入my.cnf配置文件中,重启服务

1
2
3
    如: SET SESSION sql_mod ='TRADITIONAL';
    修改mysql的模式,让其对不符合约束条件的数据插入无法进行;
    在默认情况下,假设定义了某个字段的数据类型为CHAR(5)时,如果插入的数据为abcdefg,此时发现插入的操作依然可以正常进行,只是保存的值为abcde,此种情况下,需要修改sql的模式,让其修改为不符合约束条件时插入动作无法正常进行

 
  
  
  
  
  


库的管理操作


  • 创建数据库:

    
    
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
      语法:CREATE DATABASE|SCHEMA  [IF NOT EXISTS] db_name;
      在创建库时,还可利用:
          [DEFAULT] CHARACTER SET [=] character_name
              指定字符集,加上DEFAULT表示定义默认字符集,查看mysql具体支持所有字符集,可以用:
                  SHOW CHARACTER SET; 来查看
          [DEFAULT] COLLATE [=] collation_name
              指定排序规则,查看支持所有的排序规则,可以用:
                  SHOW COLLATION;
          每个字符集可以有多个排序规则

      例如:
          CREATE SCHEMA testdb;
          CREATE DATABASE IF NOT EXISTS testdb
  • 修改数据库的属性:(主要是修改其字符集和排序规则)

    
    
    1
    2
    3
    4
    5
    6
    7
      语法:ALTER DATABASE|SCHEMA [db_name]
          后面跟上:
              [DEFAULT] CHARACTER SET [=] character_name
              修改其字符集
          或
              [DEFAULT] COLLATE [=] collation_name
              修改其排序规则
  • 删除数据库:

    
    
    1
      语法:DROP DATABASE|SCHEMA [IF EXISTS] db_name;
  • 查看数据库相关命令:

    
    
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
      SHOW DATABASES;
          查看有哪些库
      SHOW DATABASES LIKE 'PATTERN';
          表示查看数据库的名称满足LIKE指明的PATTERN的数据库,PATTERN支持%和_通配符
      SHOW CHARACTER SET;  
          查看支持的字符集
      SHOW COLLATION;  
           显示支持排序规则
      SHOW CREATE DATABASE db_name;  
          查看创建数据库时所用的语句和定义的属性信息

   
  


表的管理操作


1、创建:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
语法:CREATE TABLE [IF NOT EXISTS] table_name (create_defination,...) [table_options]

create_defination的介绍:
    都是以逗号,隔开的一些的定义
    如果是字段,则:column_name data_type  即字段名和字段的数据类型
        还可以加上一些修饰,如:
            UNSIGNED  无符号,其必须要紧跟着数据类型的后面进行指定,而不能跟在其他修饰符后面
            NULL  可以为空
            NOT NULL  不能为空
            AUTO_INCREMENT   自动增长字段
            UNIQUE [KEY]  唯一键
            PRIMARY [KEY]  主键
      &