mySQL账号管理与引擎

**Hi ~o(￣▽￣)ブ **我是【小阿飞_】😜

本期为大家带来mySQL系列文章之一：mySQL账号管理与引擎

本期精彩：以带大家了解mySQL为目的，将mySQL相关的知识、常识整理后简单呈现给大家便于查找了解(●'◡'●)

mysql基础知识

1、mysql的元数据库

元数据库：记录自身数据的数据库 ( $ _ $ )

mysql中常用元数据库👇

information_schema：信息数据库，保存mysql所维护的其他数据库信息，例如：数据库名，数据库的表，表栏的数据类型与访问权限等

mysql：核心数据库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息

performance_schema：用于mysql的监控数据的存放

mysql切换数据库：use 数据库名

2、mysq账户管理

创建账户

命令格式：create user 用户名 identified by ‘密码’

注意：identified by会将纯文本密码加密作为散列值存储

例：CREATE USER test IDENTIFIED BY '123456'

查看账户

命令格式：select host,user,password from user;

host列说明👇

%：匹配所有主机

localhost：不会被解析成IP地址，直接通过UNIXsocket连接（同一主机通讯，不经过网络协议栈，不用打包拆包，计算校验和、维护序列号应答等。只是将应用层数据从一个进程拷贝到另一个进程）

127.0.0.1：会通过TCP/IP协议连接，并且只能在本机访问

::1 ：兼容支持ipv6的，表示同ipv4的127.0.0.1

删除账户

命令格式：drop user 用户名，如DROP USER test;

注：一般不用，用时需谨慎

修改密码

命令格式：set password for 用户名=password('新密码')

如：SET PASSWORD FOR test=PASSWORD('123456');

刷新配置

命令格式：flush privileges

设置权限

命令格式：grant privileges on databasename.tablename to username@'host'

👆解释：

privileges：指定select，update等权限，全部权限使用all

databasename：指定数据库，所有数据库请使用*

tablename：指定数据表，所有表请使用*

username：需要赋权的用户名，@带的是Host，表示赋权操作针对那些链接，详情查看【host列说明】

👆使用示例：

GRANT SELECT, UPDATE ON bookshop.t_book TO test@'%'; 代表将bookshop数据库中的t_book表的select，update权限赋予test用户，并且不对ip地址限制

grant all on . to dba@'localhost' 以192.168.0开头的账户名deveoper用户针对testdb数据库赋予创建视图的权限

grant create view on testdb. to developer@'192.168.0.%'; 以192.168.0开头的账户名deveoper用户针对testdb数据库赋予创建视图的权限*

grant show view on testdb. to developer@'192.168.0.%' 以192.168.0开头的账户名deveoper用户针对testdb数据库赋予查看视图的权限*

grant index on testdb. to developer@'192.168.0.%'; 以192.168.0开头的账户名deveoper用户针对testdb数据库赋予创建索引的权限*

*grant create routine on testdb. to developer@'192.168.0.%'**grant 数据库开发人员，创建表、索引、视图、存储过程、函数等权限，这里是创建MySQL 数据表结构权限。

grant alter routine on testdb. to developer@'192.168.0.%'; 以192.168.0开头的账户名deveoper用户针对testdb数据库赋予操作存储过程与函数的权限*

授权用户可以将拥有的权限再赋予其他用户👇

命令格式：grant privileges on databasename.tablename to username@'host' with grant option

例：grant select on testdb. to dba@localhost with grant option*

🐖：一般不用，建议与数据库管理员（DBA）统一管理

查看权限

show grants：查看当前用户（自己）权限

show grants for dba@localhost; 查看其他 MySQL 用户权限

撤销权限

命令格式：revoke privileges on databasename.tablename from username@'host'；

例：REVOKE UPDATE ON bookshop.t_book FROM test@'%';

注：收回test用户对于bookshop库中t_book表的update权限（ip不限）

3、mysql引擎

数据库引擎的概念：数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据，不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能

查看数据引擎

命令：show engines

Support字段说明👇

DEFAULT的为默认的引擎

为YES表示可以使用

为NO表示不能使用

mysql常用引擎👇

** 1、MyISAM引擎**

MYISAM强调了快速读取操作

使用场景：大量查询，很少修改的场景

存储限制：256T

事务支持：不支持事务

** 2、memory引擎**

所有的数据都保存在内存中，一旦服务器重启，所有Memory存储引擎的表数据会消失但是表结构会保存下来

使用场景：由于易失性，可以用于存储在分析中产生的中间表

存储限制：取决与RAM

事务支持：不支持事务

** 3、InnoDB引擎**

后者修改快，支持事务

使用场景：一般事务性，均使用该引擎，用途最广，如果把握不准使用何种引擎，就使用该引擎

存储限制：64TG

事务支持：支持事务

** 4、ARCHIVE引擎**

只允许插入和查询，不允许修改和删除，压缩存储，节约空间，可以实现高并发的插入，支持在自增id上建立索引

使用场景：在日志和数据采集的时候可以使用

特点：

Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%

不支持索引（自增id列除外）

支持insert，select操作，但不支持delete，update操作

4、mysql数据库命令

创建：

create database 数据库名

create database if not exists 数据库名 default charset utf8 collate utf8_general_ci;

注：默认的数据库编码集：utf8，collate表示校验规则为utf8_general_ci。常用排序类型：utf8_general_cs（区分大小写）和 utf8_genera_ci（不区分大小写）

查看：show databases

删除：drop database 数据库名，危险操作，先备份

5、mysq基本数据类型及优化

mysq基几种本数据类型的特点(●ˇ∀ˇ●)

一、整数

表数范围：从 2的（n-1）次方到 2的（n-1）次方再减 1

不要指定宽度

TINYINT 8位（-128 - 127）

SMALLINT 16位（-32768 - 32767）

MEDIUMINT 24位（-8388608 - 8388607）

INT 32位大约正负21亿(可以为整数指定宽度，如INT（11），但对大多数应用是没有意义的，它不会限制值的合法表数范围，只是规定了mysql客户端在显示是的字符个数)

**BIGINT 64位 **

👆：都可选择 UNSIGNED修饰（用UNSIGNED修饰，表示无符号型整数，不允许出现负值，大致可以使正数的上限提高一倍）

二、实数（带有小数点）

FLOAT 4个字节

DOUBLE 8个字节

** 👆FLOAT、DOUBLE使用标准的浮点运算进行近似计算，不要指定精度**

DECIMAL 最多允许65个数字

** 👆DECIMAL示例：decimal(5,2)，说明：5位长度，2位小数精度，如果整数部分+2位小数超长，则报错，如果只是小数部分超出2两位，则四舍五入到两位小数**

** 👆DECIMAL精确计算：尽量在需要精确计算时使用decimal类型，例如：财务数据。在数据量很大时可以可以考虑使用bigint来代替decimal类型，将需要存储的货币单位根据小数的位数乘以一个倍数，转换为整数保存**

** 三、字符串**

CHAR

定长，msql根据定义字符串的长度一次分配足够的空间

适用场景：较短的字符串，且所有值接近同一长度

VARCHAR 变长字符串

比定长类型节约空间

但是ROW_FOMAT=FIXED每行使用定长

适用场景：字符串的最大长度比评估长度大很多，列的更新较少

缺点：频繁修改，且字符串的长度变化大时，可能出现页分裂

**不要盲目的给过大的长度(**过长的列会消耗更多的内存，mysql通常会分配固定大小的值来保存内部值，尤其是使用内存临时表进行排序或操作时性能会比较糟糕，在利用磁盘临时表进行排序时也同样糟糕。最好的策略是只分配必要的空间)

在临时表或排序时可能遭遇最大长度分配内存问题

四、TEXT、BLOB

TEXT存储字符数据👇

TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT

BLOB存储二进制数据👇

TINYBLOB

SMALLBLOB

BLOB

MEDIUMBLOB

LONGBLOB

注👇

都为存放很大的数据而设计

与其他数据不同，都作为独立的对象存储

当值太大时，使用外部存储区存储，每行只要使用1-4字节存放一个指针

五、****日期时间

datetime

精度：秒

与时区无关，8个字节存储空间

范围：1001 至 9999 年

timestamp

保存1970年1月1日午夜以来的秒数（与UNIX时间戳相同****）

占用4个字节存储空间

范围：1970年至 2038年

与时区有关

默认为NOT NULL

通常尽量使用timestamp

精度：秒

date

yyyy-MM-dd

time

HH:mm:ss

六、选择标识符

用来进行关联操作

在其他表中作为外键

整型通常是标识列的最好选择

相关的表中使用相同的数据类型

尽量避免字符串作为标识列，尤其是随机生成的字符串，（如：uuid）导致insert与select都很慢 (Φ皿Φ)👇

插入值被随机写到索引的不同位置，insert慢，容易导致页分裂，磁盘随机读取

逻辑上相邻的行被分布在磁盘和内存的不同地方，select慢

使mysql查询缓存失效

如果需要存储uuid，则应将“-”去除

**再结合mysq基本数据类型来讲几个可以优化的点叭┗|｀O′|┛ **

优化原则

原则一：更小通常更好

** 原因：**

更小通常更快
更少的cpu周期
更小的磁盘空间，内存，cpu缓存

原则二：简单就好

** 原因：**

更少的cpu周期
整型比字符串操作代价更小(字符串操作需要考虑字符集和校验规则)
用内建类型表示日期（date，datatime等）而不用字符串
用整型值而不是字符串表示IP地址：INET_ATON()、INET_NTOA()，例如👇

/**
 * IP地址与整数之间的互相转换
 * @author Administrator
 * @create 2019-12-0813:47
 */
public class IPUtil {
    /**
      * ip地址转成long型数字
      * 将IP地址转化成整数的方法如下：
      * 1、通过String的split方法按.分隔得到4个长度的数组
      * 2、通过左移位操作（<<）给每一段的数字加权，第一段的权为2的24次方，第二段的权为2的16次方，第三段的权为2的8次方，最后一段的权为1
      * @param strIp
      * @return
      */
    public static long ipToLong(String ipStr) {
        String[] ip = ipStr.split("\\.");
        return (Long.parseLong(ip[0]) << 24)
                + (Long.parseLong(ip[1]) << 16)
                + (Long.parseLong(ip[2]) << 8)
                + (Long.parseLong(ip[3]));
    }
    /**
      * 将十进制整数形式转换成127.0.0.1形式的ip地址
      * 将整数形式的IP地址转化成字符串的方法如下：
      * 1、将整数值进行右移位操作（>>>），右移24位，右移时高位补0，得到的数字即为第一段IP。
      * 2、通过与操作符（&）将整数值的高8位设为0，再右移16位，得到的数字即为第二段IP。
      * 3、通过与操作符吧整数值的高16位设为0，再右移8位，得到的数字即为第三段IP。
      * 4、通过与操作符吧整数值的高24位设为0，得到的数字即为第四段IP。
      * @param longIp
      * @return
      */
    public static String longToIP(long longIP) {
        StringBuffer sb = new StringBuffer("");
        // 直接右移24位
        sb.append(String.valueOf((longIP >>> 24)));
        sb.append(".");
        // 将高8位置0，然后右移16位
        sb.append(String.valueOf((longIP & 0x00FFFFFF) >>> 16));
        sb.append(".");
        // 将高16位置0，然后右移8位
        sb.append(String.valueOf((longIP & 0x0000FFFF) >>> 8));
        sb.append(".");
        // 将高24位置0
        sb.append(String.valueOf((longIP & 0x000000FF)));
        return sb.toString();
    }
    public static void main(String[] args) {
        System.out.println(ipToLong("219.239.110.138"));
        System.out.println(longToIP(18537472));
    }

}

原则三：尽量避免NULL

NULL为列的默认值，但除非确实需要，应尽量避免使用NULL
尽量指定列为NOT NULL，特别是需要建索引的列
查询中包括有NULL的类，mysql更难优化👇原因👇

不利于使用索引 /(ㄒoㄒ)/~~
索引统计更复杂 /(ㄒoㄒ)/~~
值比较更复杂 /(ㄒoㄒ)/~~

6、mysq建表与约束

一、建表

命令格式

CREATE TABLE 表名称(
        列名称1 数据类型 NOT NULL,
        列名称2 数据类型,
        列名称3 数据类型,
        unique(列名称1[,列名称2,...,列名称N])
    )

示例

create table t_student
(
   sid                  int not null comment '学号',
   sname                varchar(60) not null comment '姓名',
   sex                  tinyint not null default 1 comment '性别：1男， 2女',
   age                  tinyint not null comment ' 年龄',
   icard                varchar(18) not null comment '身份证，唯一约束',
   primary key (sid),
   unique key AK_Key_2 (icard)
) comment '学生信息表';
ALTER TABLE 表名 ADD PRIMARY KEY(主键名称)

二、约束

主键约束：PRIMARY KEY

** 增加主键**

alter table table_test add primary key(id);
--注:在增加主键之前,必须先把反复的id删除掉

** 删除主键**

ALTER TABLE 表名 DROP PRIMARY KEY

非空约束

 sid INT NOT NULL COMMENT '学号',

外键约束

DROP TABLE IF EXISTS t_score;
CREATE TABLE t_score
(
   id                   INT NOT NULL COMMENT '记录流水号',
   sid                  INT NOT NULL COMMENT '学号',
   cid                  INT NOT NULL COMMENT '课程ID',
   score                FLOAT COMMENT '成绩',
   PRIMARY KEY (id),
   FOREIGN KEY (sid) REFERENCES t_student (sid) ON DELETE RESTRICT ON UPDATE RESTRICT,
   UNIQUE KEY AK_Key_2 (sid, cid)
);
说明： sid为本表的外键，关联t_student表中的的sid主键，ON DELETE RESTRICT ON UPDATE RESTRICT说明在本表有数据的情况下，主表的关联键不能删除或更新。

** 增加外键**

ALTER TABLE 表名 ADD FOREIGN KEY(外键名称) REFERENCES 主表名称(主键名称)

** 删除外键**

ALTER TABLE 表名 DROP FOREIGN KEY 约束名

唯一约束 UNIQUE KEY 约束名 (字段)

** 创建唯一约束**

ALTER TABLE 表名 ADD UNIQUE(列名称1[,列名称2,..])
create unique index UserNameIndex on 't_user' ('username')

** 删除唯一约束**

ALTER TABLE 表名 DROP INDEX 唯一约束缩影名称

**默认值约束：default **

感谢阅读：本期关于mySQL相关常识知识的分享到这里就结束啦，关注[小阿飞_]，下期带你更加深入了解mySQL(●'◡'●)

标签：数据库 mySQL sql

本文转载自: https://blog.csdn.net/yifei_345678/article/details/125832711
版权归原作者 小阿飞_ 所有，如有侵权，请联系我们删除。

mySQL账号管理与引擎

mysql基础知识

1、mysql的元数据库

2、mysq账户管理

3、mysql引擎

4、mysql数据库命令

5、mysq基本数据类型及优化

6、mysq建表与约束

发表评论

“mySQL账号管理与引擎”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航