mysql数据库简单了解

Z先生09
49 阅读9分钟

mysql数据库简单了解

基本操作

数据库

  • 创建数据库

    create database 数据库名

表

  • 创建表

    CREATE TABLE 表名（...）

  • 修改表

    alter table 表名

  • 删除表

    DROP TABLE 表名

数据

  • 新增

    单行插入数据
INSERT INTO 表名(col1, col2) VALUES(val1, val2)

插入查询出来的数据
INSERT INTO 新表名(col1, col2)SELECT col1, col2 FROM 表名

将一个表的内容插入到一个新表
oCREATE TABLE 新表名 ASSELECT * FROM 表名

  • 修改

    UPDATE 表名 SET 列名 = 值  WHERE xx = 1

  • 删除

    DELETE FROM 表名 WHERE id = 1

  • 查询表

    SELECT * FROM 表名

  • 清空表

    TRUNCATE TABLE 表名

字符集

在MySQL 8.0版本之前，MySQL 5.7 默认的客户端和服务器都用了 latin1，从MySQL 8.0开始，数据库的默认编码将改为 utf8mb4

常用命令

  • 查看MySQL数据库所支持的字符集

    show character set\G;

  • 查看MySQL数据库中关于字符集的相关设置

    show variables like 'character%';

    说明:
character_set_server
服务器级别的字符集

character_set_database
当前数据库的字符集

character_set_client
服务器解码请求时使用的字符集

character_set_connection
服务器处理请求时会把请求字符串从character_set_client转为 character_set_connection

character_set_results
服务器向客户端返回数据时使用的字符集

  • 修改已创建数据库的字符集

    alter database 数据库名 character set 'utf8mb4';

  • 修改已创建数据表的字符集

    alter table 表名 convert to character set 'utf8';

权限

  • 创建用户

    CREATE USER 用户名 IDENTIFIED BY '密码';

  • 修改用户名

    RENAME 旧用户名 TO 新用户名;

  • 删除用户

    DROP USER 用户名;

  • 查看用户权限

    SHOW GRANTS FOR 用户名;

  • 授予用户权限

    GRANT SELECT, INSERT ON 数据库名.表名 TO 用户名;

GRANT SELECT, INSERT ON 数据库名.* TO 用户名;

  • 删除用户权限

    REVOKE SELECT, INSERT ON 数据库.* FROM 用户名;

  • 更改用户密码

    SET PASSWROD FOR myuser = Password('新密码');

存储引擎

常用存储引擎

InnoDB存储引擎

  • MySql 5.6 版本默认的存储引擎
  • 是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎
  • 实现了四个标准的隔离级别，默认级别是可重复读(REPEATABLE READ)。在可重复读隔离级别下，通过多版本并发控制(MVCC)+ 间隙锁(Next-Key Locking)防止幻影读
  • 主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升
  • 内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等
  • 支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取
  • InnoDB 的行级别锁定以及 Oracle 风格的一致性无锁读提升了它的多用户并发数以及性能

MyISAM存储引擎

  • 设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它
  • 提供了大量的特性，包括压缩表、空间数据索引等
  • 不支持事务
  • 不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入(CONCURRENT INSERT)
  • 可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的
  • 如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，只会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会刷入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作

MEMORY存储引擎

  • 在内存中存储所有数据，应用于对非关键数据由快速查找的场景。Memory类型的表访问数据非常快，因为它的数据是存放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失

InnoDB引擎和MyISAM引擎对比

  • 事务: InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句
  • 并发: MyISAM 只支持表级锁，而 InnoDB 还支持行级锁
  • 外键: InnoDB 支持外键
  • 备份: InnoDB 支持在线热备份
  • 崩溃恢复: MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢
  • 其它特性: MyISAM 支持压缩表和空间数据索引

查看数据库支持的引擎

SHOW ENGINES;

数据类型

整型

  • TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间，一般情况下越小的列越好
  • INT(11) 中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的

浮点数

  • FLOAT 和 DOUBLE 为浮点类型，DECIMAL 为高精度小数类型。CPU 原生支持浮点运算，但是不支持 DECIMAl 类型的计算，因此 DECIMAL 的计算比浮点类型需要更高的代价。FLOAT、DOUBLE 和 DECIMAL 都可以指定列宽，例如 DECIMAL(18, 9) 表示总共 18 位，取 9 位存储小数部分，剩下 9 位存储整数部分

字符串

  • 主要有 CHAR 和 VARCHAR 两种类型，一种是定长的，一种是变长的
  • VARCHAR 这种变长类型能够节省空间，因为只需要存储必要的内容。但是在执行 UPDATE 时可能会使行变得比原来长，当超出一个页所能容纳的大小时，就要执行额外的操作。MyISAM 会将行拆成不同的片段存储，而 InnoDB 则需要分裂页来使行放进页内
  • VARCHAR 会保留字符串末尾的空格，而 CHAR 会删除

时间和日期

  • MySQL 提供了两种相似的日期时间类型: DATETIME 和 TIMESTAMP
  • DATETIME能够保存从 1001 年到 9999 年的日期和时间，精度为秒，使用 8 字节的存储空间。它与时区无关
  • TIMESTAMP和 UNIX 时间戳相同，保存从 1970 年 1 月 1 日午夜(格林威治时间)以来的秒数，使用 4 个字节，只能表示从 1970 年 到 2038 年。它和时区有关，也就是说一个时间戳在不同的时区所代表的具体时间是不同的。默认情况下，如果插入时没有指定 TIMESTAMP 列的值，会将这个值设置为当前时间。应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高

BLOB 和 TEXT

  • BLOB和TEXT都是为存储很大的数据而设计的数据类型，分别采用二进制和字符方式存储
  • 与其他类型不同，MySQL把每个BLOB和TEXT值当做一个独立的对象去处理。当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值

其它

数据类型优化思路

  • 更小的通常更好，更小的数据类型通常更快。因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少
  • 简单就好；例如，整形比字符串操作代价更低；实用内建类型而不是字符串来存储日期和时间；用整形存储IP地址等
  • 尽量避免NULL；如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL 的列使得索引、索引统计和值比较都更复杂。尽管把可为NULL的列改为NOT NULL带来的性能提升比较小，但如果计划在列上创建索引，就应该尽量避免设计成可为NULL的列

选择表示符（identifier）

  • 整数类型通常是标识列的最佳选择。如果可能，应该避免使用字符串类型作为标识列，因为它们很耗空间，并且比数字类型慢
  • 对于完全随机的字符串也需要多加注意，这些函数生成的新值会任意分布在很大的空间内，这会导致INSERT以及一些SELECT语句变得很慢

函数

统计处理

  • AVG（返回某列的平均值）
  • COUNT（返回某列的行数）
  • MAX（返回某列的最大值）
  • MIN（返回某列的最小值）
  • SUM（返回某列值之和）

文本处理

  • LEFT（左边的字符）
  • RIGHT（右边的字符）
  • LOWER（转换为小写字符）
  • UPPER（转换为大写字符）
  • LTRIM（去除左边的空格）
  • RTRIM（去除右边的空格）
  • LENGTH（长度）
  • SOUNDEX（转换为语音值）

时间处理

  • AddDate。增加一个日期(天、周等)
  • AddTime。增加一个时间(时、分等)
  • CurDate。返回当前日期
  • CurTime。返回当前时间
  • Date。返回日期时间的日期部分
  • DateDiff。计算两个日期之差
  • Date_Add。高度灵活的日期运算函数
  • Date_Format。返回一个格式化的日期或时间串
  • Day。返回一个日期的天数部分
  • DayOfWeek。对于一个日期，返回对应的星期几
  • Hour。返回一个时间的小时部分
  • Minute。返回一个时间的分钟部分
  • Month。返回一个日期的月份部分
  • Now。返回当前日期和时间
  • Second。返回一个时间的秒部分
  • Time。返回一个日期时间的时间部分
  • Year。返回一个日期的年份部分

数值处理

  • SIN。正弦
  • COS。余弦
  • TAN。正切
  • ABS。绝对值
  • SQRT。平方根
  • MOD。余数
avatar
文章
阅读
粉丝