目录
1.介绍和安装
1.1 数据库分类
RDBMS(关系型数据库):Relational Database Management System
代表产品:Oracle MySQL MSSQL PG
NoSQL(非关系型数据库):Not Only SQL
代表作品:MongoDB Redis ES
NewSQL(对各种新的可扩展/高性能数据库的简称)
代表作品:spanner PolarDB(X) TDSQL TiDB 高斯
1.2 MySQL分支
1.3 MySQL获取
1.4 MySQL安装
# 1.解压 [root@localhost opt]# tar xf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz # 2.软连接 ln -s /opt/mysql-8.0.20-linux-glibc2.12-x86_64 /usr/local/mysql # 3.修改环境变量 vim /etc/profile 添加下面的内容 export PATH=/usr/local/mysql/bin:$PATH source /etc/profile # 4.创建用户 目录 配置文件 [root@localhost mysql]# useradd mysql [root@localhost mysql]# mkdir -p /data/3306/data [root@localhost mysql]# chown -R mysql.mysql /data [root@localhost mysql]# vim /etc/my.cnf [mysqld] user=mysql basedir=/usr/local/mysql datadir=/data/3306/data socket=/tmp/mysql.sock [mysql] socket=/tmp/mysql.sock # 5.初始化数据 如果没有配置文件: mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data 如果有配置文件: [root@localhost data]# mysqld --initialize-insecure # 6.准备启动脚本 [root@localhost ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld [root@localhost ~]# /etc/init.d/mysqld stop Shutting down MySQL... SUCCESS! [root@localhost ~]# /etc/init.d/mysqld start Starting MySQL.. SUCCESS! [root@localhost ~]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS! [root@localhost ~]# /etc/init.d/mysqld status SUCCESS! MySQL running (12134)
2.基础管理
2.1 用户管理
2.1.1 作用
2.1.2 定义
语法:用户名@'白名单'
什么是白名单?? 答:IP地址范围
常见形式:
root@'%'
root@'localhost'
root@'10.0.0.%'
root@'10.0.0.2'
2.1.3 管理用户
1.查询用户
-- 查询用户 mysql> select user,host ,authentication_string ,plugin from mysql.user; +------------------+-----------+------------------------------------------------------------------------+-----------------------+ | user | host | authentication_string | plugin | +------------------+-----------+------------------------------------------------------------------------+-----------------------+ | mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | | mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | | mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | | root | localhost | | caching_sha2_password | +------------------+-----------+------------------------------------------------------------------------+-----------------------+
2.创建用户/修改用户/删除用户
-- 创建用户 mysql> create user root@'10.0.0.%' identified by '123'; mysql> create user user1@'10.0.0.%' identified with mysql_native_password by '123'; -- 修改用户 mysql> alter user root@'10.0.0.%' identified with mysql_native_password by '123'; mysql> alter user user1@'10.0.0.%' account lock; mysql> alter user user1@'10.0.0.%' account unlock; -- 删除用户 mysql> drop user user1@'10.0.0.%';
2.2 权限管理
2.2.1 权限列表
mysql> show privileges;
2.2.2 授权和回收权限
-- 授权 mysql> grant all on *.* to root@'10.0.0.%'; -- 查询权限 mysql> show grants for root@'10.0.0.%'; -- 回收权限 mysql> revoke drop on *.* from root@'10.0.0.%' ;
2.3 连接管理
2.3.1 socket文件连接
-- 前提: 需要提前创建localhost白名单的用户 [root@localhost ~]# mysql -uroot -p -S /tmp/mysql.sock
2.3.2 TCP/IP方式
-- 前提 需要将登陆客户端IP加入白名单 [root@localhost ~]# mysql -uroot -p123 -h 10.0.0.111 -P3306
2.4 配置管理
2.4.1 离线配置
-- 配置文件应用顺序 [root@localhost ~]# mysqld --help --verbose |grep my.cnf /etc/my.cnf ---> /etc/mysql/my.cnf ---> /usr/local/mysql/etc/my.cnf --> ~/.my.cnf
-- 配置文件结构 [root@localhost ~]# cat /etc/my.cnf
[mysqld] user=mysql basedir=/usr/local/mysql datadir=/data/3306/data socket=/tmp/mysql.sock [mysql] socket=/tmp/mysql.sock
2.4.2 在线配置
-- 通过专用配置命令进行修改. mysql> set global innodb_buffer_pool_size=16777216;
-- 查看所有可以在线配置的参数 show variables show variables like '%date%';
3.MySQL的体系结构
不多说,直接上图.
4.SQL
4.1 什么是SQL?
4.2 SQL_MODE
mysql> select @@sql_mode; +-----------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +-----------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------------------------------------------------------+
4.3 数据类型
4.4 字符集
4.5 SQL种类
4.6 DDL的应用规范
4.6.1 对库(Database)的操作
4.6.2 对表(Table)的操作
4.7 关于DDL/DML和MySQL执行阶段的问题
5.索引和执行计划
5.1 什么是索引?
5.2 索引类型
5.3 BTREE 结构认识
5.4 MySQL中如何应用BTREE?
5.4.1 聚簇索引
5.4.2 辅助索引
...