0


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列说明👇
  1. %: 匹配所有主机
  2. localhost:不会被解析成IP地址,直接通过UNIXsocket连接(同一主机通讯,不经过网络协议栈,不用打包拆包,计算校验和、维护序列号应答等。只是将应用层数据从一个进程拷贝到另一个进程)
  3. 127.0.0.1:会通过TCP/IP协议连接,并且只能在本机访问
  4. ::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'
  • 👆解释:
  1. privileges:指定select,update等权限,全部权限使用all
  2. databasename:指定数据库,所有数据库请使用*
  3. tablename:指定数据表,所有表请使用*
  4. username:需要赋权的用户名,@带的是Host,表示赋权操作针对那些链接,详情查看【host列说明】
  • 👆使用示例:
  1. GRANT SELECT, UPDATE ON bookshop.t_book TO test@'%'; 代表将bookshop数据库中的t_book表的select,update权限赋予test用户,并且不对ip地址限制
  2. grant all on . to dba@'localhost' 以192.168.0开头的账户名deveoper用户针对testdb数据库赋予创建视图的权限
  3. grant create view on testdb. to developer@'192.168.0.%'; 以192.168.0开头的账户名deveoper用户针对testdb数据库赋予创建视图的权限*
  4. grant show view on testdb. to developer@'192.168.0.%' 以192.168.0开头的账户名deveoper用户针对testdb数据库赋予查看视图的权限*
  5. grant index on testdb. to developer@'192.168.0.%'; 以192.168.0开头的账户名deveoper用户针对testdb数据库赋予创建索引的权限*
  6. *grant create routine on testdb. to developer@'192.168.0.%'**grant 数据库开发人员,创建表、索引、视图、存储过程、函数等权限,这里是创建MySQL 数据表结构权限。
  7. grant alter routine on testdb. to developer@'192.168.0.%'; 以192.168.0开头的账户名deveoper用户针对testdb数据库赋予操作存储过程与函数的权限*
  • 授权用户可以将拥有的权限再赋予其他用户👇
  1. 命令格式:grant privileges on databasename.tablename to username@'host' with grant option
  2. 例:grant select on testdb. to dba@localhost with grant option*
  3. 🐖:一般不用,建议与数据库管理员(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字段说明👇
  1. DEFAULT的为默认的引擎
  2. 为YES表示可以使用
  3. 为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
  • 不要指定宽度
  1. TINYINT 8位 (-128 - 127)
  2. SMALLINT 16位 (-32768 - 32767)
  3. MEDIUMINT 24位 (-8388608 - 8388607)
  4. INT 32位 大约正负21亿(可以为整数指定宽度,如INT(11),但对大多数应用是没有意义的,它不会限制值的合法表数范围,只是规定了mysql客户端在显示是的字符个数)
  5. **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
  1. 定长,msql根据定义字符串的长度一次分配足够的空间
  2. 适用场景:较短的字符串,且所有值接近同一长度
  • VARCHAR 变长字符串
  1. 比定长类型节约空间
  2. 但是ROW_FOMAT=FIXED每行使用定长
  3. 适用场景:字符串的最大长度比评估长度大很多,列的更新较少
  4. 缺点:频繁修改,且字符串的长度变化大时,可能出现页分裂
  5. **不要盲目的给过大的长度(**过长的列会消耗更多的内存,mysql通常会分配固定大小的值来保存内部值,尤其是使用内存临时表进行排序或操作时性能会比较糟糕,在利用磁盘临时表进行排序时也同样糟糕。最好的策略是只分配必要的空间)
  6. 在临时表或排序时可能遭遇最大长度分配内存问题

四、TEXT、BLOB

  • TEXT存储字符数据👇
  1. TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT
  • BLOB存储二进制数据👇
  1. TINYBLOB
  2. SMALLBLOB
  3. BLOB
  4. MEDIUMBLOB
  5. LONGBLOB
  • 注👇
  1. 都为存放很大的数据而设计
  2. 与其他数据不同,都作为独立的对象存储
  3. 当值太大时,使用外部存储区存储,每行只要使用1-4字节存放一个指针

五、****日期时间

  • datetime
  1. 精度:秒
  2. 与时区无关,8个字节存储空间
  3. 范围:1001 至 9999 年
  • timestamp
  1. 保存1970年1月1日午夜以来的秒数(与UNIX时间戳相同****)
  • 占用4个字节存储空间
  1. 范围:1970年 至 2038年
  2. 与时区有关
  3. 默认为NOT NULL
  4. 通常尽量使用timestamp
  5. 精度:秒
  • date
  1. yyyy-MM-dd
  2. time
  3. HH:mm:ss

六、选择标识符

  1. 用来进行关联操作
  2. 在其他表中作为外键
  3. 整型通常是标识列的最好选择
  4. 相关的表中使用相同的数据类型
  5. 尽量避免字符串作为标识列,尤其是随机生成的字符串,(如:uuid)导致insert与select都很慢 (Φ皿Φ)👇
  • 插入值被随机写到索引的不同位置,insert慢,容易导致页分裂,磁盘随机读取
  • 逻辑上相邻的行被分布在磁盘和内存的不同地方,select慢
  • 使mysql查询缓存失效
  • 如果需要存储uuid,则应将“-”去除

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

优化原则

  • 原则一:更小通常更好

** 原因:**

  1. 更小通常更快
  2. 更少的cpu周期
  3. 更小的磁盘空间,内存,cpu缓存
  • 原则二:简单就好

** 原因:**

  1. 更少的cpu周期
  2. 整型比字符串操作代价更小(字符串操作需要考虑字符集和校验规则)
  3. 用内建类型表示日期(date,datatime等)而不用字符串
  4. 用整型值而不是字符串表示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
  1. NULL为列的默认值,但除非确实需要,应尽量避免使用NULL
  2. 尽量指定列为NOT NULL,特别是需要建索引的列
  3. 查询中包括有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账号管理与引擎”的评论:

还没有评论