MySQL 学习笔记

最新推荐文章于 2022-08-11 10:31:04 发布

「已注销」

最新推荐文章于 2022-08-11 10:31:04 发布

阅读量680

点赞数 1

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/o_hoooo/article/details/116486600

版权

MySQL数据库介绍及安装
官方定义MySQL DBA工作内容
运维DBA
初级：各版本、各平台安装搭建、升级
中级：体系结构原理、基础管理（启动关闭、初始化配置文件管理、多实例管理、用户权限管理、基本SQL（增删改查））、
日志管理、备份与恢复、主从复制（构建、状态监控）
高级：高可用（MGR、InnoDB Cluster）、高性能（优化）
开发DBA
懂至少一门开发语言：JAVA、Python
基本SQL语句深入学习（增删改查）、数据库结构设计（建模）
高级SQL：存储过程、函数、触发器、视图、事件
数据是什么
认为：账号密码、图片、视频、文字、链接等
计算机：二进制数据
数据如何储存
–使用专用的软件将数据转换为二进制存储在计算机指定位置
需要考虑：安全，存储限制
数据库管理系统
EDBMS --关系型数据库（重要信息）：Oracle、MySQL、MSSQL、PG
NoSQL --非关系型数据库（没那么重要）a. EDBMS 关系型数据库：MongoDB、ES、Redis
MySQL产品线
MySQL厂家：Oracle、MariaDB、Percona
Oracle MySQL企业版本选择：5.6 (5.6.36)/ 5.7 / 8.0
MySQL安装方式（Linux）
通用二进制版本：解压即用
rpm, yum版本：下载rpm包或者配置yum源
源码包：编译安装，非常慢，研究MySQL源码

MySQL 5.7.28二进制包安装
环境准备
准备虚拟机；
IP:
CPU:
清理历史环境
创建用户和组
root@howell-PC:/home/howell/Downloads# useradd mysql -s /sbin/nologin
创建软件目录
root@howell-PC:/home/howell/Downloads# mkdir -p /data/3306/
root@howell-PC:/home/howell/Downloads# mkdir -p /binlog/3306
root@howell-PC:/home/howell/Downloads# mkdir -p /app/database/
设置权限
root@howell-PC:/home/howell/DownloMySQL企业版本选择：5.6 (5.6.36)/ 5.7 / 8.0ads# chown -R mysql.mysql /app/ /data/ /binlog/
上传并解压MySQL软件
root@howell-PC:/app/database# tar xf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
制作软件接：ln -s mysql-5.7.28-linux-glibc2.12-2-x86_64 mysql设置环境变量
设置环境变量
vim /etc/profile (即修改tec/profile文件)
添加一行：export PATH=/app/database/mysql/bin:PATH
生效配置：source /etc/profile

初始化系统库表
mysqld --initialize-insecure --user=mysql --basedir=/app/database/mysql --datadir=/data/3306/
拓展
Mysqld --initialize
初始完密码完成后，会有12位临时密码，但是必须在使用mysql之前重置这个密码；
mysqld --initialize-insecure
mysqld --initialize-insecure （5.7+）
初始完之后为空密码；

配置文件设置
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/app/database/mysql
datadir=/data/3306
server_id=6
port=3306
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock
EOF

准备MySQL启动脚本
拷贝mysql启动脚本至管理目录cp mysql.server /etc/init.d/mysqld

MySQL体系结构及基础管理
MySQL客户端/服务起工作模型（C/S）

本地socket连接方式
mysql -S /tmp/mysql.sock
说明：只能在本地使用，不依赖ip和端口
远程TCP/IP连接方式
mysql -uroot -p123 -h 127.0.0.1 -p 3306

服务器端实例
实例：mysqld+ 工作线程+ 预分配的内存结构
功能：管理数据

mysqld程序结构
由两大主层：server层、引擎层；官方
细致：三主层：suerver层（链接层、SQL层）、引擎层

连接层
提供链接协议（socket, TCP/IP）；
验证；
提供专用连接线程；
SQL层
语法检查；
语义(DDL, DCL, DML, DTL)，权限检查;
解析器：解析预处理（评估执行方法消耗），得出执行计划；
优化器：选择最优方案（基于代价cost）；
执行器：按照优化器的选择执行SQL语句，得出执行结果，你需要的数据在磁盘的具体位置
查询缓存(query cache 默认不开启8.0后取消)：缓存SQL执行语句及查询过程；可以用redis替代自带的查询缓存；
日志记录：binlog二进制日志，默认未开启。

存储引擎层
相当于linux中的文件系统，和磁盘交互的模块。

MySQL的逻辑结构（对象）
库 database：库名、库的属性；
表 tables：表名、表的属性、表的内容、表的列
MySQL的物理存储结构
段：一个表就是一个段(分区表除外)，可以由一个或者多个区构成
区：一个区或者簇，默认1M，连续的64个page；
页：一个page，默认16k，连续的4个OS block，最小的IO单元；
1个区或者簇/1MB(连续64个page) -> 1个page/16KB(连续4个block) -> 一个block/4KB(连续8个扇区) -> 一个扇区512/KB;
一个表就是一个段；

MySQL 基础管理
用户管理
用户的作用
Linux用户：登录linux系统、管理系统：文件；
MySQL：登录MySQL数据库、管理MySQL对象：表；
用户的定义
Linux用户：用户名；
MySQL用户：用户名@‘白名单’；
白名单：地址列表，允许通过白名单IP登录MySQL；
MySQL用户
howell@‘localhost’：howell用户能够通过本地登录MySQL(socket)；
howell@‘10.0.0.10’：howell用户能够通过10.0.0.10远程登录MySQL；
howell@‘10.0.0.%’：howell用户能够通过10.0.0.xx/24远程登录MySQL服务器；
howell@‘10.0.0.5%’：howell用户能够通过10.0.0.50-10.0.0.59远程登录MySQL服务器；
howell@‘10.0.0.0/255.255.254.0’；
howell@’%’；
用户查询
use mysql;
show tables;
desc user;
mysql> select user,host,authentication_string from mysql.user;
root@‘localhost’：超级管理员用户，只能通过本地socket登录；
创建用户
mysql> create user howell@‘localhost’;
select user,host,authentication_string from mysql.user;#新建用户并设置密码
修改用户
mysql> alter user howell@‘localhost’ identified by ‘zxcv’;#修改用户密码
删除用户
mysql> drop user howell2@‘192.168.1.5’;
mysql> drop user howell@‘localhost’;
注意： 8.0 版本之前，是可以通过grant命令建立用户+授权；8.0之后只能先建立用户，后授权；

权限管理
作用
用户对数据库对象，有哪些管理能力？
权限的表现方式
具体的命令
mysql> show privileges;#查询MySQL可设置权限的所有命令

8.0以后：
create user 用户 identified by ‘密码’;
grant 权限1,权限2 on 对象 to 用户；#分两步，需先创建用户后授权
权限:可以在上表选择；
ALL：管理员；
权限1,权限2,…：普通用户；
grant option：给别的用户授权，后面添加 with grant option;
对象：库或者表
. ：-------> chmod -R 755 /；所有授权
howell.* ：--------> howell这个子目录所有授权；
howell.t1 ： ----------> howell数据库下单表t1；
howell.t1() ： ------------> 给howell数据库下t1表某个列授权；
授权的例子
例子1：创建并授权管理员用户howellroot，能够通过192.168.1.5网段登录并管理数据库
mysql> grant all on . to howellroot@‘192.168.1.5’ identified by ‘zxcv’ with grant option;
select user,host,authentication_string from mysql.user;#查看用户
mysql> show grants for howellroot@‘192.168.1.5’;#查询howellroot@'192.168.1.5’用户权限；
mysql> select * from mysql.user\G;#可以查看用户的权限
例子2：用户授权
mysql> grant create,update,select,insert,delete on app.* to app@‘192.168.1.5’ identified by ‘zxcv’;
mysql> show grants for app@‘192.168.1.5’;#查看权限
拓展
MySQL授权表：
user —> .
db ----> app.*
tables_priv ----> app.t1
columns_priv 列
回收权限
Linux：chmod -R 644 /howell -----> chmod -R 755 /howell 可以通过重复授权修改权限；
MySQL：不能通过重复授权的方式修改权限，只能通过回收权限的方式修改权限；
mysql> revoke create on app.* from app@‘192.168.1.5’;#回收app@'192.168.1.5’用户create权限
mysql> show grants for app@‘192.168.1.5’;#查看权限
超级管理员密码忘记了，怎么处理？
关闭连接层用户验证阶段：
–skip-grant-tables 跳过授权表
–skip-networking 跳过TCP/IP连接
etc/init.d/mysqld stop #关闭mysqld
which mysqld_safe
mysqld_safe --skip-grant-tables & #这种方式谁都可以登录，不安全

systemctl stop mysqld #关闭数据库
mysqld_safe --skip-grant-tables --skip-networking & #安全模式启动
拓展：也可以使用 service mysqld start --skip-grant-tables --skip-networking & 安全模式启动
mysql #登录数据库
flush privileges; 手工加载授权表；
alter user root@‘localhost’ identified by ‘zxcv’; #更改密码
service restart mysqld #重启数据库到正常模式

MySQL 连接管理
MySQL自带客户端程序、MysQL远程客户端程序、程序链接

MySQL 自带客户端程序
(1) mysql
参数列表：
-u 用户名
-p 密码
-S 本地socket文件
-h 数据库IP地址
-P 数据库端口号
-e 免交互执行数据库命令
< 导入SQL脚本
scoket连接方式：
前提：数据库中必须实现授权root@'localhost’用户
mysql -uroot -pzxcv -S /tmp/mysql.sock #最完整
mysql -uroot -p -S /tmp/mysql.sock #推荐方式
mysql -uroot -p
TCP/IP连接方式：
前提：必须提前创建好远程连接的用户例如 howellwindows@‘192.168.1.5’
mysql -uhowellwindows -pzxcv -h 192.168.1.5 -P 3306 #远程连接，不加端口号默认3306
例子1：授权howell管理用户，可以通过本地socket登录
mysql> grant all on . to howell@‘localhost’ identified by ‘zxcv’; #创建howell用户
mysql> mysql -uhowell -p -S /tmp/mysql.sock #测试连接
例子2：授权ychowell用户，可以通过192.168.1.%远程登录
mysql> grant all on . to ychowell@‘192.168.1.%’ identified by ‘zxcv’; #创建ychowell用户通过192.168.1.5登录mysql
mysql> mysql -uychowell -p -h 192.168.1.6 -P 3306 #登录测试
查看连接user及ip,谁是远程连接谁是本地连接
grant all on . to ychowell@‘192.168.1.%’ identified by ‘zxcv’;

初始化配置
方式
源码安装：编译过程中设置初始化参数；
配置文件：数据库启动之前，设定配置文件参数 /etc/my.cnf；
启动脚本命令行：如mysqld_safe --skip-grant-tables --skip-networking &.
配置文件应用
(1) 配置文件读取顺序
howell@howell-PC:~$ mysqld --help --verbose |grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf #mysqld会从这几个路径找配置文件
my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default
(2)意外情况：
手工定制配置文件位置点：/opt/my.cnf，/data/3306/my.cnf
mysqld --default-file=/opt/my.cnf & #启动时
mysqld_safe --default-file=/opt/my.cnf &
(3)配置文件的书写格式
[mysqld] #服务器端标签
user=mysql #负责数据库管理用户
basedir=/app/database/mysql #软件的安装位置
datadir=/data/3306 #数据的存放位置
server_id=6 #标识节点的唯一编号（主从有用）
port=3306 #端口号
socket=/tmp/mysql.sock #套接字文件连接数据库中的 -S 参数
[mysql] #客户端标签
socket=/tmp/mysql.sock #MySQL登录时默认读取socket文件的位置
[标签]
配置参数
标签是什么？
标签是用来区分不同程序的参数；
服务器端标签：负责数据库服务器端运行参数设定
客户端标签：影响客户端连接，只影响本地客户端连接，不影响远程客户端连接
服务器端常见标签类型：
[mysqld]
[mysqld_safe]
客户端厂家爱你标签类型：
[mysql]
[mysqldump]

MySQL启动与关闭
启动

systemctl start mysqld -->mysql.server -->mysqld_safe -->mysqld
mysqld_safe和mysqld，可以在启动数据库时计入自己执行的参数，例如
–skip-grant-tables
–skip-networking
–default-file=/opt/my.cnf
关闭
systemctl stop m ysqld
service mysqld stop
/etc/init.d/mysqld stop
mysqladmin -uroot -pzxcv shutdown
mysql -uroot -pzxcv -e “shutdown”

MySQL的多实例
同版本多实例
（1）规划
软件1份
配置文件多份：/data/3307/my.cnf /data/3308/my.cnf /data/3309/my.cnf
数据目录多份 --初始化数据多次：/data/330(7…9)
日志目录多份：/binlog/330(7…9)
socket多份：/tmp/mysql330(7…9).sock
端口多份：port=3307,3308,3309
server_id多份：server_id=7,8,9
（2）配置过程
1)创建需要目录
mkdir -p /data/330{7…9}/data #创建数据目录
mkdir -p /binlog/330{7…9} #创建日志目录
2）创建配置文件
配置文件读取顺序：
cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/app/database/mysql
datadir=/data/3307/data
socket=/tmp/mysql3307.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/binlog/3307/mysql-bin
EOF

cat > /data/3308/my.cnf <<EOF
[mysqld]
basedir=/app/database/mysql
datadir=/data/3308/data
socket=/tmp/mysql3308.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/binlog/3308/mysql-bin
EOF

cat > /data/3309/my.cnf <<EOF
[mysqld]
basedir=/app/database/mysql
datadir=/data/3309/data
socket=/tmp/mysql3309.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
log_bin=/binlog/3307/mysql-bin
EOF
3）初始化数据
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/app/database/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/app/database/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/app/database/mysql
4）准备启动脚本
cat >/etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation =man:mysqld(8)
…
不同版本多实例
1）软连接不同版本

ln -s mysql-5.6.46-linux-glibc2.12-x86_64 mysql56

ln -s mysql-8.0.18-linux-glibc2.12-x86_64 mysql80

mv /etc/my.cnf /etc/my.cnf.bak

#注释以下信息
#export PATH=/app/database/mysql/bin:$PATH
2）准备不同目录
3）准备配置文件
4）初始化数据
5）准备启动脚本

第四章 MySQL的SQL基础
SQL介绍
结构化查询语言
结构化查询语言：关系型数据库中通用的一类语言。
SQL常用类型
MySQL客户端自带的功能
mysql>help #可以使用help打印出来
MySQL Server端分类命令
mysql>help contents

DDL：数据定义语言
DCL：数据控制语言
DML：数据操作语言
DQL：数据查询语言
SQL的各种名词
SQL_modes SQL模式
SQL-mode作用：规范SQL语句书写的方式(例如：分母为0，或者0年0月0日)，影响SQL执行行为
SQL_mode是一个参数
select @@sql_mode;
| @@sql_mode |
±------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,
STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE,
NO_ZERO_DATE,E
RROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,
NO_ENGINE_SUBSTITUTION |
±------------------------------------------------------------------------------------------------------------------------------------------+
字符集（charset）及校对规则（collation）
字符集
show charset;
utf8：单个字符最多3个字节
utf8mb4(建议)：单个字符最多4个字节
差别：（1）utf8mb4支持的编码比utf8多，比如，emoji字符mb4中支持，utf8不支持，因为emoji字符，1个字符占4个字节；
create database zabbix charset utf8mb4;
show create database zabbix;
校对规则（collation）
每种字符集有多种校对规则（排序规则）;
show collation;
作用：（1）影响排序的操作，简单来说就是大小写是否敏感；
#大小写敏感 | utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 |
#大小写不敏感 | utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 |
数据类型（data_type）
数字：整数、小数
mysql> create database study;
mysql> use study;
mysql> create table t1(id int,name varchar(64),age tinyint);

tinyint1B=8 bitint4Bbigint8B
字符串类型
char(长度)：定长字符串类型最多255个字符varchar(长度)：变长字符串类型最多65535字符enum(‘bj’, sh)：枚举类型enum(‘sd’, ‘sdsd’, ‘sa’)
char和varchar的区别例如：
char(10)：最多存10个字符，如果存储的字符不够10个，自动用空格填充剩余的空间，对于磁盘空间都会占满；
varchar(10)：最多存10个字符，按需分配存储空间；
补充：

varchar类型在存储数据时会先判断字符长度，然后合理分配存储空间，而char类型不会判断，立即分配。所以在固定长度列中，还是会选择char类型。
varchar类型，除了会存储字符串之外，还会额外使用1-2个字节存储字符长度。
例：varchar(10)
asdf —> 1.判断字符长度 —> 2.申请空间 —> 3. 存字符 —> 4.申请一个字节，存储字符长度；
3.应用场景：
字符串固定长度，char类型；不固定，varchar类型；
4.括号中数字问题
括号中设置的是字符的个数，无关字符类型。
但是不同种类的字符，占用的储存空间是不一样的。
对于数字和英文，每个字符占1个字节长度。
对于中文，占用空间大小要考虑字符集，utf,utf8mb4每个中文占3个字节长度。emoji字符占4个字节长度。
总长度不能超过数据类型的最大长度。
彩蛋：以上两种数据类型选择需考虑周到索引应用。

时间类型

DATATIME（常用）：范围从1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999；占用8个字节长度
TIMESTAMP（常用）：范围从1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07:999999；占用4个字节长度；会收到时区的影响。
二进制类型
json数据类型
{
id: 101
name: ‘zhangsan’
}
表属性
约束
PK(Primary key) ：主键约束；作用：唯一+ 非空；每张表只有一个主键，作为聚簇索引。
not null ：非空约束；作用：必须非空，我们建议每个列都设置为非空。
unique key ：唯一约束；作用：必须是不重复的。
unsigned ：针对数字列，非负数。
其他属性
default ：默认值（例如非空值里面填写默认值）
comment ：注释
auto_increment：自增长

SQL的应用
Client
List of all MySQL commands:
Note that all text commands must be first on line and end with ‘;’
? (?) Synonym for `help’.
clear (\c) Clear the current input statement.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don’t write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (#) Rebuild completion hash.
source (.) Execute an SQL script file.（导入SQL脚本） Takes a file name as an argument.
status (\s) Get status information from the server.
system (!) Execute a system shell command（调用系统的命令如linux）.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don’t show warnings after every statement.
resetconnection(\x) Clean session context.

Server
Linux中，一切皆命令，一切皆文件。
MySQL中，一切皆SQL，一切皆表。
DDL：数据定义语言
（1）库定义：库名& 库属性
1）创建库定义
CREATE DATABASE howell CHARSET utf8mb4;
规范：
1.库名：小写，与业务有关，不要数字开头，库名不要太长，不能使用保留字符串；
2.必须制定字符集
2）查询库定义
SHOW DATABASES;
SHOW CREATE DATABASE howell;
3）修改库定义
ALTER DATABASE howell CHARSET utf8;
注意：要从小往大了改，A-----B；那么需要B是A的严格超集；
4）删除库：注意，不代表生产操作
注意：除了管理员，任何人没有删库权限。
DROP howell;

（2）表定义
1）创建表
CREATE TABLE wp_user(
id INT NOT NULL AUTO_INCREMENT COMMENT ‘用户序号’,
name VARCHAR(64) NOT NULL COMMENT ‘用户名’,
age TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT ‘年龄’,
gender CHAR(1) NOT NULL DEFAULT ‘F’ COMMENT ‘性别’,
cometime DATETIME NOT NULL COMMENT ‘注册时间’,
shengfen ENUM(‘北京市’,‘上海市’,‘天津市’,‘重庆市’) NOT NULL DEFAULT ‘北京市’ COMMENT’省份’,
PRIMARY KEY (id)
)ENGINE=INNODB CHARSET=utf8mb4;
建表规范：
i）表名：小写(多平台兼容性问题，windows不区分大小写)，不能是数字开头，表名要和业务有关，名字不要太长，不能使用关键字；
ii）表属性必须设置存储引擎和字符集；
iii）数据类型：合适，简短，足够；
iv）必须要有主键；
v）每个列尽量设置not null，不知道填啥设定默认值(default)；
vi）每个列要有注释；
2）查询表
mysql> show tables;
mysql> desc wp_user;
mysql> show create table wp_user;
3）修改表
#1.添加手机号列；
mysql> alter table wp_user add column shouji bigint not null unique key comment ‘手机号’;
#2.将手机列数据类型修改为char(11)
mysql> alter table wp_user modify shouji char(11) not null unique key comment’手机号’;
#3.删除手机号列
mysql> alter table wp_user drop shouji;
4）删除表
mysql> drop table wp_user;
业务流程

（3）DDL补充
1）手撕一张表：设计一张student表；
USE howell
CREATE TABLE howell.student(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ‘学号’,
sname VARCHAR(64) NOT NULL COMMENT ‘姓名’,
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT ‘年龄’,
gender TINYINT NOT NULL DEFAULT 2 COMMENT ‘0代表女，1代表男，2代表未知’,
addr ENUM(‘北京市’,‘上海市’,‘天津市’,‘重庆市’) NOT NULL DEFAULT ‘北京市’ COMMENT ‘地址’,
cometime DATETIME NOT NULL DEFAULT NOW() COMMENT ‘入学时间’,
telnum BIGINT NOT NULL UNIQUE KEY COMMENT ‘手机号’
)ENGINE=INNODB CHARSET=utf8mb4;
2）线上DDL操作（alter）对于生产的影响

i）SQL审核平台：yearing、inception
ii）说明：在MySQL中，DDL语句在对表进行操作时，是要锁“元数据表”的（数据库此时所有修改类的命令无法正常运行），所以在对于大表（业务繁忙的表）进行线上DDL操作时，要谨慎，尽量避开业务繁忙期。
iii）扩展：元数据是什么？类似于Linux Inode信息
iv）面试题回答要点：1.SQL语句的意思是什么：以上四条语句是对2张核心业务表进行DDL加列操作；2.以上操作带来的影响：对于大表（业务繁忙的表）进行线上DDL操作时，尽量避开业务繁忙时期；3.我们的建议：尽量避开业务繁忙期，走流程，建议使用：pt-osc工具(pt-online-scheme-change)工具减少锁表的影响，如果是8.0版本，可以不用工具。
扩展
pt-osc工具的使用

DCL：数据控制语言
grant命令
revoke命令

DML：数具操作语言
作用：对表中的数具行进行操作。
（1）insert的应用
–INSERT 应用
–规范用法
DESC student;
INSERT INTO
student(id,sname,age,gender,addr,cometime,telnum)
VALUES(1,‘张三’,18,1,‘北京市’,‘2020-02-16 11:50:00’,110);

–简约的方法
INSERT INTO
student
VALUES(2,‘李四’,20,0,‘重庆市’,‘2020-08-26 12:56:00’,112);

–部分录入数据(如含有默认值和自增)
INSERT INTO
student(sname,telnum)
VALUES(‘王五’,‘119’);

–批量录取方法
INSERT INTO
student(sname,telnum)
VALUES(‘howell1’,‘180’),(‘howell2’,‘181’),(‘howell3’,‘182’);

（2）update的应用
–update的应用
–修改指定数据行的值;
前提：需要明确修改哪一行，一般update语句到都有where的条件
UPDATE student SET sname=‘miaomiao’ WHERE id=6;

（3）delete的应用
–删除指定行的值
–前提：需要明确删除哪一行，一般delete语句都有where的条件
DELETE FROM student where id=5;

–扩展
–1.伪删除
–删除id为1的数据行;
–原操作
DELETE FROM student where id=1;
–删除原因：不想查询到该条数据
–修改表结构，添加state状态列
ALTER TABLE student ADD COLUMN state TINYINT NOT NULL DEFAULT 1;
–删除数据改为update
UPDATE student SET state=0 WHERE id=1;
–修改查询语句
SELECT * FROM student where state=1;

2.面试题：delete from student, drop table student, trucate table student 区别？
说明：1）以上三条命令都可以删除student表；
2）区别：
delete from student :逻辑上，逐行删除，数据行多，操作很慢，
并没有真正从磁盘上删除，只是在存储层打标记，磁盘空间不立即释放，HWM高水位不会降低;
drop table student ：将表结构（元数据）和数据行物理层次删除;
truncate table student ：清空表段中的所有数据页，物理层次
删除全表数据，磁盘空间立即释放，HWM高水位线会降低。
问题：delete\drop\truncate如果不小心删除了，他们可以恢复吗？
答：可以;常规方法：以上三种问题，都可以通过备份+日志，恢复数据，也可以通过延时库进行恢复
灵活方法：delete可以通过，翻转日志（binlog）

DQL：数据查询语言
（1）select：官方角度属于DML
1.功能：获取表中的数据行；
2.select 单独使用（MySQL独享）
1）select 配合内置函数(可以使用help查看)使用
–显示当前时间
SELECT NOW();
–显示当前使用数据库
SELECT DATABASE();
–命令拼接
SELECT CONCAT(“Hello World!”);
SELECT CONCAT(user,"@",host) FROM mysql.user;
–查看当前数据库版本
SELECT VERSION();
–查看当前登录用户
SELECT USER();
2）计算
SELECT 10*10;
3）查询数据库的参数(MySQL特有)
SELECT @@PORT;
SELECT @@SOCKET;
SELECT @@DATADIR;
–查看MySQL中所有参数
SHOW RIABLES；
–模糊查找法
SHOW VARIABLES LIKE ‘%trx%’;

3.select 标准用法-配合其他子句使用
1）单表
–默认执行顺序
SELECT
1.FROM 表1,表2…
2.WHERE 过滤条件1,过滤条件2…
3.GROUP BY 条件列1,条件列2…
3.5.SELECT LIST 列名
4.HAVING 过滤条件1,过滤条件2…
5.ORDER BY 条件列1,条件列2…
6.LIMIT 限制

–准备学习数据库
mysql -uroot -p <world.sql
DESC city;
SELECT * FROM city WHERE id<10;

–1. SELECT 配合 FROM 子句的使用
–语法
–SELECT 列 FROM 表;
–例子1：查询表中所有数据
SELECT * FROM world.city;
SELECT ID,Name,CountryCode,Districe,Population from world.city;
–查询部分列值
–例子2：查询城市名和对应的城市人口
SELECT Name,Population FROM world.city LIMIT 20,10;

–2. SELECT + FROM + WHERE 配合使用
–2.1 WHERE 配合比较比较判断符号 =,>,<,>=,<=,!=
–例子1：查询city中，中国所有城市信息;
SELECT * FROM world.city where ConutryCode=‘CHN’;
–例子2：查询city中人口数小于1000的城市；
SELECT * FROM world.city where Population<1000;

–2.2 WHERE 配合 LIKE 语句进行模糊查询;
–例子3：查询city中，国家代号是CH开头的城市信息;
SELECT * FROM world.city WHERE CountryCode LIKE ‘CH%’;
–注意：LIKE 语句使用时切忌不要出现前面带%的模糊查询，不走索引，而且只能是字符串类型；
–问题例子4：
SELECT * FROM world.city WHERE CountryCode LIKE ‘%CH%’;

–2.3 WHERE 配合逻辑连接符 AND OR
–例子5：查询中国城市中，人口大于500万的城市；
SELECT * FROM world.city
WHERE CountryCode=‘CHN’ AND Population>5000000;
–例子6：查询中国或美国的城市
SELECT * FROM world.city
WHERE CountryCode=‘CHN’ OR CountryCode=‘USA’;
–例子7：查询中国和美国城市中人口超过5000000的城市
SELECT * FROM world.city
WHERE CountryCode IN (‘CHN’,‘USA’) AND Population>500000;

–2.4 WHERE 配合 BETWEEN AND
–例子8：查询城市人口数在100万到200万之间的城市
SELECT * FROM world.city
WHERE Popualtion>=1000000 AND Population<=2000000;
SELECT * FROM world.city
WHERE Population BETWEEN 1000000 AND 2000000;

–3. SELECT + FROM + WHERE + GROUP BY
–GROUP BY 配合
–聚合函数（MAX(),MIN(),AVG(),COUNT(),SUM(),GROUP_CANCAT()）使用
–GROUP_CONCAT:列转行
–说明：碰到GROUP BY 必然有聚合函数一起使用；
–例子1：统计city表中每个国家城市个数
SELECT CountryCode,COUNT(ID) FROM world.city
GROUP BY CountryCode;
–例子2：统计中国每个省的城市个数
SELECT District,COUNT(ID) FROM world.city
WHERE CountryCode=‘CHN’
GROUP BY District;
–例子3：统计每个国家的总人口
SELECT CountryCode,SUM(Population) FROM world.city
GROUP BY CountryCode;
–例子4：统计中国每个省的总人口
SELECT District,SUM(Population) FROM world.city
WHERE CountryCode=‘CHN’
GROUP BY District;
–例子5：统计中国每个省总人口，城市个数，城市名列表
SELECT District,SUM(Population),COUNT(id),GROUP_CONCAT(Name) FROM world.city
WHERE CountryCode=‘CHN’
GROUP BY District;
–说明：select list 中的列，要么时GROUP BY的条件，要么在聚合函数中出现，否则会违反SQL_mode不兼容
–原理：MySQL不支持，结果集是1行对多行的显示方式；

–4. HAVING 语句
–作用：与WHERE语句类似,HAVING属于后过滤
–场景：需要在GROUP BY+ 聚合函数后，再做判断过滤时使用
–例子1：统计中国，每个省的总人口，只显示人口数大于500w信息
SELECT District,SUM(Population) FROM world.city
WHERE CountryCode=“CHN”
GROUP BY District
HAVING SUM(Population)>5000000;

–5. ORDER BY 应用
–例子1：统计中国每个省的总人口，只显示总人口大于500w信息，并且按照总人口数从大到小排列
SELECT District,SUM(Population) FROM world.city
WHERE CountryCode=“CHN”
GROUP BY District
HAVING SUM(Popualtion)>5000000
ORDER BY SUM(Popualtion) DESC;

–6.0 LIMIT
–作用：分页显示结果集
–例子1：统计中国每个省的总人口，只显示总人口大于500w信息，并且按照总人口数从大到小排列
–只显示前五名
SELECT District,SUM(Population) FROM world.city
WHERE CountryCode=“CHN”
GROUP BY District
HAVING SUM(Popualtion)>5000000
ORDER BY SUM(Popualtion) DESC
LIMIT 5;
–显示6-10名
LIMIT 5,5;
LIMIT 5 OFFSET 5;
–显示3-5名
LIMIT 2,3;
LIMIT 3 OFFSET 2;
GROUP BY 原理：

作业2：GROUP BY + 聚合函数使用方法

应用场景：需要对一张表中，按照不同数句特点，需要分组计算统计时，会使用GROUP BY聚合函数;
GROUP BY 使用核心使用方法：
1）根据需求，找出分组条件;
2）根据需求，使用合适的聚合函数;
–练习
USE school;

student:学生表
sno:学号
sname:学生姓名
sage:学生年龄
ssex:学生性别

teacher:教师表
tno:教师编号
tname:教师姓名

course:课程表
cno:课程编号
cname:课程名字
tno:教师编号

score:成绩表
sno:学号
cno:课程编号
score:成绩

–创建项目
DROP database SCHOOL;
CREATE DATABASE school CHARSET utf8mb4;
USE school;

CREATE TABLE student(
sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ‘学号’,
sname VARCHAR(20) NOT NULL COMMENT ‘名字’,
sage TINYINT UNSIGNED NOT NULL COMMENT ‘年龄’,
ssex ENUM(‘f’,‘m’) NOT NULL DEFAULT ‘m’ COMMENT ‘性别’
)ENGINE=INNODB CHARSET=utf8mb4;

CREATE TABLE course(
cno INT NOT NULL PRIMARY KEY COMMENT ‘课程编号’,
cname VARCHAR(20) NOT NULL COMMENT ‘课程名字’,
tno INT NOT NULL COMMENT ‘教师编号’
)ENGINE=INNODB CHARSET=utf8mb4;

CREATE TABLE sc(
sno INT NOT NULL COMMENT ‘学号’,
cno INT NOT NULL COMMENT ‘课程编号’,
score INT NOT NULL DEFAULT 0 COMMENT ‘成绩’
)ENGINE=INNODB CHARSET=utf8mb4;

CREATE TABLE teacher(
tno INT NOT NULL PRIMARY KEY COMMENT ‘教师编号’,
tname VARCHAR(20

最低0.47元/天解锁文章

「已注销」

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL 学习笔记

MySQL数据库介绍及安装官方定义MySQL DBA工作内容运维DBA初级：各版本、各平台安装搭建、升级中级：体系结构原理、基础管理（启动关闭、初始化配置文件管理、多实例管理、用户权限管理、基本SQL（增删改查））、日志管理、备份与恢复、主从复制（构建、状态监控）高级：高可用（MGR、InnoDB Cluster）、高性能（优化）开发DBA懂至少一门开发语言：JAVA、Python基本SQL语句深入学习（增删改查）、数据库结构设计（建模）高级SQL：存储过程、函数、触发器、视图、事件
复制链接

扫一扫