MySQL基础总结，机械基础总结
关于MySQL
官方文档：https://dev.mysql.com/doc/refman/8.0/en/
MySQL是Oracle公司开发、发布和支持的最流行的开源SQL数据库管理系统。
【主要特点】
- 开源
- 使用BTree索引
- 支持多线程，对多核CPU性能可以达到更好的发挥
- 用C和C++编写
MySQL 8.0特性
添加的特性
数据字典
原子数据定义语句(原子DDL)
安全和账户管理
可用一种新的caching_sha2_password认证插件，可以使用缓存解决连接时的延时问题【8.0默认身份验证插件】
新增角色特性【据说Oracle才有】
资源管理【优化CPU资源分配】
MySQL8.0有了资源组的概念。也就是说可以创建并管理一个资源组。
将工作线程分配给特定的组。以便DBA限制，调配资源。目前只针对CPU。
InnoDB增强
自增列【消除了以往重启实例自增列不连续的问题】
Btree索引【索引损坏，恢复后依旧可以保持一致性】
可禁用死锁检测
一个新的动态变量，innodb_deadlock_detect，可用于禁用死锁检测。在高并发性系统上，当多个线程等待同一锁时，死锁检测会导致减速。有时，禁用死锁检测并依赖于innodb_lock_wait_timeout在发生死锁时设置事务回滚。
不推荐特性
FLOAT(M,D)和DOUBLE(M,D)语法指定类型列的位数。FLOAT和DOUBLE(任何同义词)都是一个非标准的MySQL扩展。不建议使用此语法。ZEROFILL属性对数值数据类型不推荐，对于整数数据类型也不推荐使用显示宽度属性。考虑使用另一种方法来产生这些属性的效果。例如，应用程序可以使用LPAD()函数设置为零垫数字，直到所需宽度，或者它们可以将格式化的数字存储在CHAR柱子。
删除特性
innodb_locks_unsafe_for_binlog系统变量被删除。这个READ COMMITTED隔离级别提供了类似的功能。
表1.1重命名的InnoDB信息架构视图
旧名
新名称
INNODB_SYS_COLUMNSINNODB_COLUMNS
INNODB_SYS_DATAFILESINNODB_DATAFILES
INNODB_SYS_FIELDSINNODB_FIELDS
INNODB_SYS_FOREIGNINNODB_FOREIGN
INNODB_SYS_FOREIGN_COLSINNODB_FOREIGN_COLS
INNODB_SYS_INDEXESINNODB_INDEXES
INNODB_SYS_TABLESINNODB_TABLES
INNODB_SYS_TABLESPACESINNODB_TABLESPACES
INNODB_SYS_TABLESTATSINNODB_TABLESTATS
INNODB_SYS_VIRTUALINNODB_VIRTUAL
CentOS7.6 安装MySQL8.0
环境准备
- 卸载mariadb
rpm -qa | grep mariadb
rpm -e --nodeps mariadb-libs-5.5.60-1.el7_5.x86_64
数据字典
原子数据定义语句(原子DDL)
安全和账户管理
可用一种新的caching_sha2_password认证插件，可以使用缓存解决连接时的延时问题【8.0默认身份验证插件】
新增角色特性【据说Oracle才有】
资源管理【优化CPU资源分配】
MySQL8.0有了资源组的概念。也就是说可以创建并管理一个资源组。
将工作线程分配给特定的组。以便DBA限制，调配资源。目前只针对CPU。
InnoDB增强
自增列【消除了以往重启实例自增列不连续的问题】
Btree索引【索引损坏，恢复后依旧可以保持一致性】
可禁用死锁检测
一个新的动态变量，
innodb_deadlock_detect，可用于禁用死锁检测。在高并发性系统上，当多个线程等待同一锁时，死锁检测会导致减速。有时，禁用死锁检测并依赖于innodb_lock_wait_timeout在发生死锁时设置事务回滚。
FLOAT(M,D)和DOUBLE(M,D)语法指定类型列的位数。FLOAT和DOUBLE(任何同义词)都是一个非标准的MySQL扩展。不建议使用此语法。ZEROFILL属性对数值数据类型不推荐，对于整数数据类型也不推荐使用显示宽度属性。考虑使用另一种方法来产生这些属性的效果。例如，应用程序可以使用LPAD()函数设置为零垫数字，直到所需宽度，或者它们可以将格式化的数字存储在CHAR柱子。innodb_locks_unsafe_for_binlog系统变量被删除。这个READ COMMITTED隔离级别提供了类似的功能。
表1.1重命名的InnoDB信息架构视图
| 旧名 | 新名称 |
|---|---|
INNODB_SYS_COLUMNS |
INNODB_COLUMNS |
INNODB_SYS_DATAFILES |
INNODB_DATAFILES |
INNODB_SYS_FIELDS |
INNODB_FIELDS |
INNODB_SYS_FOREIGN |
INNODB_FOREIGN |
INNODB_SYS_FOREIGN_COLS |
INNODB_FOREIGN_COLS |
INNODB_SYS_INDEXES |
INNODB_INDEXES |
INNODB_SYS_TABLES |
INNODB_TABLES |
INNODB_SYS_TABLESPACES |
INNODB_TABLESPACES |
INNODB_SYS_TABLESTATS |
INNODB_TABLESTATS |
INNODB_SYS_VIRTUAL |
INNODB_VIRTUAL |
环境准备
- 卸载mariadb
rpm -qa | grep mariadb
rpm -e --nodeps mariadb-libs-5.5.60-1.el7_5.x86_64centos7 内部集成了mariadb，而安装mysql的话会和mariadb的文件冲突，所以需要先卸载掉mariadb。
【如果之前安装了mysql需要先卸载】
yum remove mysql*删除安装目录
whereis mysql
rm -rf /usr/share/mysql安装MySQL
获取yum源（MySQL官网）
安装yum源
rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm查看各版本启动状况
yum repolist all | grep mysql默认开启最新版8.0
[调整命令】禁用8.0，开启5.7
yum-config-manager --disable mysql80-community yum-config-manager --enable mysql57-community命令在yum-utils 包里，安装既可以解决无法找到yum-config-manager命令：
yum -y install yum-utils安装mysqll
yum -y install mysql-community-server
配置MySQL远程连接
查看mysql版本
mysql -V启动mysql&&设置开机自启
systemctl start mysqld systemctl enable mysqld查看默认生成密码
grep 'temporary password' /var/log/mysqld.log登录修改密码
mysql -uroot -p修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NoPassword564925080!'; mysql5.7之后默认安装了密码安全检查插件（validate_password），默认密码检查策略要求密码必须包含：大小写字母、数字和特殊符号，并且长度不能少于8位。否则会提示ERROR 1819 (HY000): Your password does not satisfy the current policy requirements错误.授权远程登录用户
默认的密码加密方式是：caching_sha2_password，而现在很多客户端工具还不支持这种加密认证方式，连接测试的时候就会报错：client does not support authentication protocol requested by server; consider upgrading MySQL client
CREATE USER 'noneplus'@'%' IDENTIFIED BY 'Noneplus564925080!'; GRANT ALL ON *.* TO 'noneplus'@'%'; //修改认证方式为mysql_native_password ALTER USER 'noneplus'@'%' IDENTIFIED WITH mysql_native_password BY 'Noneplus564925080!'; flush privileges;开放服务器3306端口
远程连接
MySQL常用命令
登录
mysql -u root -p
数据库相关命令
show databases;
create database database_name;
use database_name;
drop database_name;
数据库表相关命令
mysql -u root -p
show databases;
create database database_name;
use database_name;
drop database_name;
【数据库和数据库表相关命令都属于DDL数据定义语言】
show tables; [先切换到指定数据库]
//创建表
CREATE TABLE `user_info` (
`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '用户id',
`username` varchar(10) DEFAULT NULL COMMENT '用户姓名',
`password` varchar(20) DEFAULT NULL COMMENT '用户密码',
`age` int(5) DEFAULT NULL COMMENT '年龄',
`email` varchar(20) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 COMMENT='用户信息表'
//查看表定义
show create table user_info \G;
drop table user_info;
//删除表字段
alter table user_info column age;
//修改表
Alter table user_info modify username varchar(15);
//增加表的字段
alter table user_info add column gender int(1);
//字段改名
alter table user_info change age age1 int(3);
//modify,add,change都可以进行排序
Alter table user_info modify username varchar(15) first; //放在最前面
alter table user_info add column gender int(1) after age;//字段至于age之后
//修改表名
alter table user_info rename person_info;
增删改查命令
【增删改查属于DML数据操作语言】
插入
INSERT INTO user_info(username,password,age,email) VALUES('hq','123456789',22,'glaring@sina.com')
更新
UPDATE user_info SET username='hq',age=23,email='56492508@qq.comm' WHERE id=5
删除
DELETE FROM user_info WHERE id=6
查询
SELECT * FROM user_info WHERE id = 6
SELECT * FROM user_info WHERE id = 6 and age<30
排序【默认升序】
SELECT * FROM user_info ORDER BY ID DESC LIMIT 10 //查询最后十条数据
SELECT * FROM user_info ORDER BY ID DESC LIMIT 10,20 //查询最后20条数据的前10条
统计数据总条数
SELECT COUNT(1) FROM user_info;
统计最大值，最小值，求和
SELECT MAX(age),MIN(age) ,SUM(age) FROM user_info;
表连接查询
select ename,deptname from emp,dept where emp.deptno=dept.deptno;
MySQL常用数据类型
数值类型
| 整数类型 | 字节 | 最小值 | 最大值 |
|---|---|---|---|
| tinyint | 1 | 有符号-128 无符号0 | 有符号127 无符号255 |
| smallint | 2 | 有符号-32768 无符号0 | 有符号32767 无符号65535 |
| mediumint | 3 | 有符号-800w 无符号0 | 有符号800w 无符号167w |
| int，integer | 4 | 有符号-21亿 无符号0 | 有符号21亿 无符号42亿 |
| bigint | 8 | 有符号-92w兆 无符号0 | 有符号92w兆 无符号184w兆 |
int（5）指定显式宽度【不显式指定默认int（11）】，当数值宽度小于五位的时候，默认填满。zerofill指用0填充。
unsigned表示是否带符号
| 浮点数类型 | 字节 | 最小值 | 最大值 |
|---|---|---|---|
| float | 4 | ||
| double | 8 |
| 定点数类型 | 字节 | 最小值 | 最大值 |
|---|---|---|---|
| decimal（M，D） | M+2 | 有符号-128 无符号0 | 有符号127 无符号255 |
表示一共显示M位数字，包括整数位和小数位，其中D位代表小数点有几位
decimal不指定精度默认整数位为10，小数位为0.
日期时间类型
| 类型 | 字节 | 最小值 | 最大值 |
|---|---|---|---|
| date | 4 | 1000-01-01 | 9999-12-31 |
| datetime | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
| timestamp | 4 | 1970010108001 | 2038年的某个时刻 |
记录系统当前时间可用timestamp，支持不同地方的时区差异
TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07
字符串类型
| 类型 | 描述 |
|---|---|
| char | 0-255字节 |
| varchar | 0-65535字节 |
| tinyblob | 0-255字节 |
| blob | 0-65535字节 |
| mediumblob | 0-16772150字节 |
| longblob | 0-4294967295字节 |
| tinytext | 0-255字节 |
| text | 0-65535字节 |
| mediumtext | 0-16772150字节 |
| longtext | 0-4294967295字节 |
| varbinary（M） | 0-M字节 |
| binary（M） | 0-M字节 |
MySQL支持的运算符
算术运算符
| 算术运算符 | 说明 |
|---|---|
| + | 加法运算 |
| - | 减法运算 |
| * | 乘法运算 |
| / | 除法运算，返回商 |
| % | 求余运算，返回余数 |
比较运算符
| 比较运算符 | 说明 |
|---|---|
| = | 等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| <=> | 安全的等于，不会返回 UNKNOWN |
| <> 或!= | 不等于 |
| IS NULL 或 ISNULL | 判断一个值是否为 NULL |
| IS NOT NULL | 判断一个值是否不为 NULL |
| LEAST | 当有两个或多个参数时，返回最小值 |
| GREATEST | 当有两个或多个参数时，返回最大值 |
| BETWEEN AND | 判断一个值是否落在两个值之间 |
| IN | 判断一个值是IN列表中的任意一个值 |
| NOT IN | 判断一个值不是IN列表中的任意一个值 |
| LIKE | 通配符匹配 |
| REGEXP | 正则表达式匹配 |
逻辑运算符
| 逻辑运算符 | 说明 |
|---|---|
| NOT 或者 ! | 逻辑非 |
| AND 或者 && | 逻辑与 |
| OR 或者 || | 逻辑或 |
| XOR | 逻辑异或【相同为0，不同为1】 |
位运算符
| 位运算符 | 说明 |
|---|---|
| | | 按位或 |
| & | 按位与 |
| ^ | 按位异或 |
| << | 按位左移 |
| >> | 按位右移 |
| ~ | 按位取反，反转所有比特 |
运算符优先级
| 优先级由低到高排列 | 运算符 |
|---|---|
| 1 | =(赋值运算）、:= |
| 2 | II、OR |
| 3 | XOR |
| 4 | &&、AND |
| 5 | NOT |
| 6 | BETWEEN、CASE、WHEN、THEN、ELSE |
| 7 | =(比较运算）、<=>、>=、>、<=、<、<>、!=、 IS、LIKE、REGEXP、IN |
| 8 | | |
| 9 | & |
| 10 | <<、>> |
| 11 | -(减号）、+ |
| 12 | *、/、% |
| 13 | ^ |
| 14 | -(负号）、〜（位反转） |
| 15 | ! |