学习之旅4-mysql基础

1.MySQL简介与安装

1.1、mysql版本介绍

• 5.6/5.7/8.0
  存在的差异

1.2、安装

1.2.1、rpm/yum

• rpm软件包管理 链接: rpm/yum.

1.2.2、 二进制安装

• 1)环境准备：创建软件/数据目录 分开
• 2)创建对应的用户，方便特定的用户管理
• 3)授权对应的软件/数据目录 chown -R mysql.mysql /usr/loacl/mysql /test/mysql
• 4)上传二进制包，解压指定目录，做安装前装备
  上传：#yum -y install lrzsz 上传至软件目录、usr/local/mysql
  解压：#tar -zxvf mysql-5.7…tar.gz
  改名：mv mysql-5.7.20-linux-glibc2.12-x86_64 mysql
• 5)配置环境变量：
  #vim /etc/profile 添加export PATH=/usr/local/mysql/bin:$PATH (注意添加的位置） 刷新配置文件：#source /etc/profile
• 6)初始化设置：
  mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/test/data
• 7)配置文件：# vim /etc/my.cnf
• 8)启动停止：
  /etc/init.d/mysqld(cp mysql.server) service mysqld start/stop/restart
• 9)忘记密码：
  a、关闭数据库：#service mysqld stop
  b、跳过授权表 --skip-grant-tables --skip-networking &
  c、修改密码： A.alter user root@‘localhost’ idendified by ‘root’; B.update mysql.user set authentication_string=password(‘root’) where user=‘root’; C.grant all on . to root@‘localhost’ identified by ‘root’ with grant option;
  d、刷新授权表：flush privileges;
  e、重启mysql：service mysqld start;

1.3、源码编译安装

参考： 源码编译安装.

2、MySQL服务结构与管理

2.1、C/S模型（客户端/服务端模型）

• 本地socket: sock文件时启动mysql时才指定在哪个目录生成的
  tcp/ip 远程

2.2、服务器端：实例

• 主程序 守护进程+多线程（干活）+预分配内存

2.3、MySQL进程结构

• server层（连接层）：1.提供连接协议 2.提供验证 3.提供连接线程 （show processlist 查看）
• sql层：1.语法检查 2.语义检查 3.是否有权限 4.解析预处理 5.优化 6. 执行/缓存、日志
• engine层：负责根据SQL层执行的结果，从磁盘上拿数据。

2.4、MySQL存储结构

• 逻辑存储结构： 库+表
• 数据物理存储结构：数据目录下，对于不同的存储引擎，文件类型不同 InnoDB存储引擎（MySQL-5.5 以及之后版本默认存储引擎）： .frm文件：存储表结构（列，列属性） .ibd文件：存储的数据记录和索引 ibdata1文件（数据目录中）：数据字典信息 MyISAM存储引擎： .frm文件：存储表结构（列，列属性） .ibd文件：存储的数据记录 .myi文件：存储索引
• 段： 一个表就是一个段，可以右一个或多个区构成
• 区： 一个区（簇）默认1M，是连续的64个pages
• 页： 一个页默认16KB，是连续的4个block（块） 4*4B，最小IO单元

2.5、MySQL基础管理

2.5.1、用户管理：

用于登录，管理mysql对象（库，表）

• 增：create user zd@‘localhost’ identified by ‘123’;
• 删：drop user zd@‘localhost’;
• 改：alter user zd@‘localhost’ identified by ‘456’;
• 查：select user,host,autentication_string from mysql.user;

2.5.2、权限管理：

针对用户对mysql对象的权限

• 授权：grant 权限 on 对象（库.表） to 用户； 创建用户并授权（8.0以前）：grant 权限 on 对象（库.表） to 用户 identified by ‘123’; 先建用户，后授权（8.0以后）
• 回收：revoke 权限 on 对象 from zd@‘localhost’;
• 查看：show grants for zd@‘localhost’;

2.5.3、连接管理

• 本地socket：msyql -uroot -p -S /tmp/mysql.sock
• tcp/ip：mysql -uzd -p -h 192.168.28.129 注意：必须创建该网段白名单用户，并且授权，且开放3306端口才能访问 vim /etc/sysconfig/iptables 注意不能再最后添加 重启防火墙：service iptables restart
• 免交互执行sql：mysql -uroot -p -e “select user,host from mysql.user;”

2.5.4、初始化配置

• 作用 ：
  影响数据库启动，影响客户端功能
• 配置方法
  启动前初始化配置文件：/etc/my.cnf
  启动时mysql命令行 进行设置
  编译过程中设置
• 配置文件
  多个配置文件，依次读取，如果有重复选项，会以最后一个文件设置的为准 #mysqld --help --verbose|grep my.cnf
  /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
  启动时 --defaults-file=xxxx时，以上的所有文件都不会读取
  书写方式 客户端[mysql][mysqladmin][mysqldump]/服务器端[mysqld][mysql_safe] [标签] xxx=xxx

2.5.5、多种启动方式：

mysql.server–>mysqld_safe–>mysqld
以上多种方式，都可以单独启动MySQL服务 mysqld_safe和mysqld一般是在临时维护时使用。 另外，从Centos 7系统开始，支持systemd直接调用mysqld的方式进行启动数据库
注意：mysqld_safe --skip-grant-tables --skip-networking & 报错原因

2.5.6、启动故障

• 日志查看：默认路径在数据目录下 主机名.err

3、SQL基础

3.1、结构化查询语言

关系型数据库通用
sql标准 89 92 99 03标准

3.2、 server端 sql常用命令种类

• DDL 数据定义语言
• DCL 数据控制语言
• DML 数据操作语言
• DQL 数据查询语言

3.3、sql 引入-数据库的逻辑结构

库
库名
库属性：字符集 ，排序规则
表
表名
表属性：存储引擎，字符集，排序规则
列名
列属性：数据类型，约束，排序规则
数据行

3.4、 字符集（charset）和校对规则（collaction)

字符集
建库建表指定 5.7默认latin1 建议使用utf8mb4（支持4字节 ）
mysql>show charset; 查看支持的字符集
mysql>show variabes like ‘%char%’; 查看当前数据库的字符集
mysql>show create database db1; 查看建库db1时 设置的字符集

校对规则
mysql>show collaction; 查看校对规则
字符集内用于字符比较和排序的一套规则，有的规则区分大小写，有的则无视。
同一字符集有不同校对规则，不同文字类型对应不同校对规则 ： utf8mb4_general_ci 大小写不敏感 utf8mb4_bin 大小写敏感

3.5、数据类型

• 1）整型
• 2）浮点型
• 3）字符串
  char 定长 指定多少字节 存多少
  varchar 变长 多一个字节来存放长度值
• 4）enum 枚举
• 5）时间类型
• 6）二进制
• 7）json 键值对

4、SQL 应用

4.1、 DDL 数据定义语言

库定义

• 建库（字符集，校对规则）： create database db1 charset utf8mb4 collaction utf8mb4_bin;
• 修改：alter database db1 charset utf8;修改库字符集，对于之前已经建好的则无法修改，只影响后续建的表。一般来说无法修改库名。
• 查看： mysql>show create databas db1; 查看建库时的设置。 mysql>select database(); 查看当前使用的数据库。
  库名要求：一般小写，简洁易懂，不能用关键词，必须字母
  表定义
• 建表（字符集，存储引擎）、字段（指定类型，约束条件）： create table t2(id int primary key not null,name varchar(20) not null comment ‘姓名’) engine=‘Innodb’ charset=utf8mb4;
• 修改：
  1）alter table t1 rename to t2;修改表名
  2）alter table t1 add column 字段 类型 属性 注释 after 字段A：增加字段
  3）alter table t1 modify 字段 类型;（必须加上原有属性 注释） 修改数据类型+属性
  4）alter table t1 change 旧字段 新字段 类型 属性 注释； 只能修改字段名（原有属性必须加上）
  5）alter table t1 drop 列；删除列 注意：对于属性 为unique 的 ,若要删除该属性，则删除索引即可，因为对于唯一键，会默认建立索引，alter table t1 drop index 唯一索引；
• 查看：
  1）desc tb1;查看表结构
  2）show create table t1;查看建表结构
  3）show full columns from tb1;查看列信息
• 建表规范：
  1）字母开头，一般小写，简洁易懂；
  2）必须设置字段类型 属性约束 注释 索引
  3）指定存储引擎，字符集与库一样
• 表的复制
  1、复制表结构： 1）不含主键，索引等：create table t_1 as select * from t1 where 1=2; 2）含主键，索引等：create table t_2 like t1;可复制旧表主键、索引，但不包含触发器、外键等 3）完全复制：查询原表建表语句：show create table t1；复制修改表名创建新表 mysql -uroot -p -e “show create table t1\G” >> 1.sql 输出重定向
  2、复制表结构+数据 ：
  1）不含主键，索引等：create table t_2 as select * from t1; 2）含主键，索引等：首先复制表 create table t_3 like t1; 然后再将原表数据插入新表即可：insert into t_3 select * from t1;
  3）完全复制：mysqldump备份表出来后，修改表名导入 备份指定库.表 ：mysqldump -uroot -p db1.t1 >/test/db1-t1.sql vim /test/db-t1.sql “： 进入末行模式，：%s/搜索内容（t1）/替换内容（t2)" wq或者x 保存退出 导入指定库：mysql -uroot -p db1</test/db1-t1.sql
  3、复制旧表数据到新表： 表结构一样：insert into t_4 select * from t1; 表结构不一样：insert into t_4(字段1，字段2) select 字段1,字段2 from t1;

4.2、 DCL 数据控制语言

• grant 授权： grant 权限 on 对象（库.表） to 用户白名单 （with grant option) 8.0以前该语句可以用户创建用户+授权，8.0以后必须先建用户后授权。 查看权限： show grants for 用户白名单; show grants;
• revoke 回收权限: revoke 权限 on 对象（库.表） from 用户白名单；

4.3、 DML 数据操作语言

• insert 插入： insert into table values();插入所有 insert into table(列1,列2,…) values (,);插入指定列
• delete 删除：（谨慎，务必备份）
  delete from table where ;删除指定条件数据行
  drop/delete/truncate: drop table t1;删除表结构+数据
  delete from t1;逐行删除，操作慢，id不会覆盖
  truncate table t1;清空数据页，磁盘空间立即释放
• update 更新： update t1 set 字段=‘更新内容’ where 条件; 更新指定条件某字段数据

4.4、 DQL 数据查询语言

sql_mode:only_full_group_by(5.7)
select @@sql_mode;定义了你MySQL应该支持的sql语法，对数据的校验等等,配置文件中修改。

参考文献： 2019-MySQL 5.7-标杆班课程大纲.