Hi ~o(*￣▽￣*)ブ****我是【】??
本期为大家带来mySQL系列文章之一：mySQL账号管理与引擎
本期精彩：以带大家了解mySQL为目的，将mySQL相关的知识、常识整理后简单呈现给大家便于查找了解(●’'●)
目录
元数据库：记录自身数据的数据库 ( $ _ $ )
mysql中常用元数据库??
- information_schema：信息数据库，保存mysql所维护的其他数据库信息，例如：数据库名，数据库的表，表栏的数据类型与访问权 限等
- mysql：核心数据库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息
- performance_schema：用于mysql的监控数据的存放
mysql切换数据库 ：use 数据库名
创建账户
- 命令格式：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不限）
数据库引擎的概念：数据库存储引擎是数据库底层软件组织，数据库管理系统（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操作
创建：
- 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 数据库名，危险操作，先备份
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ㄒ)/~~
一、建表
命令格式
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删除掉
- 1
- 2
删除主键
ALTER TABLE 表名 DROP PRIMARY KEY
- 1
非空约束
?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 主表名称(主键名称)
- 1
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 约束名
- 1
- 唯一约束****UNIQUE KEY 约束名 (字段)
创建唯一约束
ALTER TABLE 表名 ADD UNIQUE(列名称1[,列名称2,..]) create unique index UserNameIndex on 't_user' ('username')
- 1
- 2
删除唯一约束
ALTER TABLE 表名 DROP INDEX 唯一约束缩影名称
- 1
- 默认值约束：default
感谢阅读：本期关于mySQL相关常识知识的分享到这里就结束啦，关注[]，下期带你更加深入了解mySQL(●’'●)
先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。添加下方名片，即可获取全套学习资料哦