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(●'◡'●)