25.第19章 MYSQL数据库
一.mysql数据库基于二进制包一键安装脚本
#mysq5.6基于二进制包一键安装脚本
[root@centos7 ~]# cat install_mysql5.6_v3.sh
#!/bin/bash
#
#******************************************************************************
#Author: zhanghui
#QQ: 19661891
#Date: 2021-06-01
#FileName: install_mysql5.6_v3.sh
#URL: www.cnblogs.com/neteagles
#Description: install_mysql5.6 for centos 7/8 & ubuntu 18.04/20.04
#Copyright (C): 2021 All rights reserved
#******************************************************************************
SRC_DIR=/usr/local/src
COLOR="echo -e \\033[01;31m"
END='\033[0m'
MYSQL_URL=https://mirrors.tuna.tsinghua.edu.cn/mysql/downloads/MySQL-
MYSQL_VERSION=5.6
MYSQL_APP='mysql-5.6.51-linux-glibc2.12-x86_64.tar.gz'
os(){
if grep -Eqi "CentOS" /etc/issue || grep -Eq "CentOS" /etc/*-release;then
rpm -q redhat-lsb-core &> /dev/null || { ${COLOR}"安装lsb_release工具"${END};yum -y install redhat-lsb-core &> /dev/null; }
fi
OS_ID=`lsb_release -is`
OS_RELEASE_VERSION=`lsb_release -rs |awk -F'.' '{print $1}'`
}
check_file(){
cd ${SRC_DIR}
if [ ${OS_ID} == "CentOS" ] &> /dev/null;then
rpm -q wget &> /dev/null || yum -y install wget &> /dev/null
fi
if [ ! -e ${MYSQL_APP} ];then
${COLOR}"缺少${MYSQL_APP}文件"${END}
${COLOR}'开始下载MYSQL二进制安装包'${END}
wget ${MYSQL_URL}${MYSQL_VERSION}/${MYSQL_APP} || { ${COLOR}"MYSQL二进制安装包下载失败"${END}; exit; }
else
${COLOR}"相关文件已准备好"${END}
fi
}
install_mysql(){
[ -d /usr/local/mysql ] && { ${COLOR}"数据库已存在，安装失败"${END};exit; }
${COLOR}"开始安装MySQL数据库..."${END}
cd ${SRC_DIR}
${COLOR}'开始安装MYSQL依赖包'${END}
if [[ ${OS_RELEASE_VERSION} == 8 ]] &> /dev/null;then
yum install -y libaio perl-Data-Dumper autoconf ncurses-compat-libs &> /dev/null
elif [[ ${OS_RELEASE_VERSION} == 7 ]] &> /dev/null;then
yum install -y libaio perl-Data-Dumper &> /dev/null
else
apt update &> /dev/null;apt -y install numactl libaio-dev libtinfo5 &> /dev/null
fi
tar xf ${MYSQL_APP} -C /usr/local/
MYSQL_DIR=`echo ${MYSQL_APP}| sed -nr 's/^(.*[0-9]).*/\1/p'`
ln -s /usr/local/${MYSQL_DIR} /usr/local/mysql
id mysql &> /dev/null || { useradd -s /sbin/nologin -r mysql ; ${COLOR}"创建mysql用户"${END}; }
chown -R mysql.mysql /usr/local/mysql/
[ -d /data/mysql ] || mkdir -pv /data/mysql &> /dev/null
chown -R mysql.mysql /data/mysql
/usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/mysql --basedir=/usr/local/mysql/ &> /dev/null
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod a+x /etc/init.d/mysqld
echo 'PATH=/usr/local/mysql/bin/:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh
cat > /etc/my.cnf <<-EOF
[mysqld]
socket=/data/mysql/mysql.sock
user=mysql
symbolic-links=0
datadir=/data/mysql
innodb_file_per_table=1
[client]
port=3306
socket=/data/mysql/mysql.sock
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/tmp/mysql.sock
EOF
if [ ${OS_ID} == "CentOS" ] &> /dev/null;then
chkconfig --add mysqld
else
update-rc.d -f mysqld defaults
fi
cat > /lib/systemd/system/mysqld.service <<-EOF
[Unit]
Description=mysql database server
After=network.target
[Service]
Type=notify
PrivateNetwork=false
Type=forking
Restart=no
TimeoutSec=5min
IgnoreSIGPIPE=no
KillMode=process
GuessMainPID=no
RemainAfterExit=yes
SuccessExitStatus=5 6
ExecStart=/etc/init.d/mysqld start
ExecStop=/etc/init.d/mysqld stop
ExecReload=/etc/init.d/mysqld reload
[Install]
WantedBy=multi-user.target
Alias=mysqld.service
EOF
systemctl enable --now mysqld &> /dev/null
[ $? -ne 0 ] && { ${COLOR}"数据库启动失败，退出!"${END};exit; }
${COLOR}"数据库安装完成"${END}
}
main(){
os
check_file
install_mysql
}
main
#mysq5.7基于二进制包一键安装脚本
[root@centos7 ~]# cat install_mysql5.7_v3.sh
#!/bin/bash
#
#******************************************************************************
#Author: zhanghui
#QQ: 19661891
#Date: 2021-06-01
#FileName: install_mysql5.7_v3.sh
#URL: www.cnblogs.com/neteagles
#Description: install_mysql5.7 for centos 7/8 & ubuntu 18.04/20.04
#Copyright (C): 2021 All rights reserved
#******************************************************************************
SRC_DIR=/usr/local/src
COLOR="echo -e \\033[01;31m"
END='\033[0m'
MYSQL_URL=https://mirrors.cloud.tencent.com/mysql/downloads/MySQL-
MYSQL_VERSION='5.7/'
MYSQL_FILE='mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz'
MYSQL_ROOT_PASSWORD=123456
os(){
if grep -Eqi "CentOS" /etc/issue || grep -Eq "CentOS" /etc/*-release;then
rpm -q redhat-lsb-core &> /dev/null || { ${COLOR}"安装lsb_release工具"${END};yum -y install redhat-lsb-core &> /dev/null; }
fi
OS_ID=`lsb_release -is`
OS_RELEASE_VERSION=`lsb_release -rs |awk -F'.' '{print $1}'`
}
check_file(){
cd ${SRC_DIR}
if [ ${OS_ID} == "CentOS" ] &> /dev/null;then
rpm -q wget &> /dev/null || yum -y install wget &> /dev/null
fi
if [ ! -e ${MYSQL_FILE} ];then
${COLOR}"缺少${MYSQL_FILE}文件"${END}
${COLOR}'开始下载MYSQL二进制安装包'${END}
wget ${MYSQL_URL}${MYSQL_VERSION}${MYSQL_FILE} || { ${COLOR}"MYSQL二进制安装包下载失败"${END}; exit; }
else
${COLOR}"${MYSQL_FILE}文件已准备好"${END}
fi
}
install_mysql(){
[ -d /usr/local/mysql ] && { ${COLOR}"MySQL数据库已存在，安装失败"${END};exit; }
${COLOR}"开始安装MySQL数据库..."${END}
${COLOR}'开始安装MYSQL依赖包'${END}
if [[ ${OS_RELEASE_VERSION} == 8 ]] &> /dev/null;then
yum -y install libaio perl-Data-Dumper ncurses-compat-libs &> /dev/null
elif [[ ${OS_RELEASE_VERSION} == 7 ]] &> /dev/null;then
yum -y install libaio perl-Data-Dumper &> /dev/null
else
apt update &> /dev/null;apt -y install numactl libaio-dev libtinfo5 &> /dev/null
fi
cd ${SRC_DIR}
tar xf ${MYSQL_FILE} -C /usr/local/
MYSQL_DIR=`echo ${MYSQL_FILE}| sed -nr 's/^(.*[0-9]).*/\1/p'`
ln -s /usr/local/${MYSQL_DIR} /usr/local/mysql
id mysql &> /dev/null || { useradd -s /sbin/nologin -r mysql ; ${COLOR}"创建mysql用户"${END}; }
chown -R mysql.mysql /usr/local/mysql/
echo 'PATH=/usr/local/mysql/bin/:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh
cat > /etc/my.cnf <<-EOF
[mysqld]
server-id=1
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
EOF
[ -d /data/mysql ] || mkdir -p /data/mysql &> /dev/null
chown -R mysql.mysql /data/mysql
mysqld --initialize --user=mysql --datadir=/data/mysql
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
if [ ${OS_ID} == "CentOS" ] &> /dev/null;then
chkconfig --add mysqld
else
update-rc.d -f mysqld defaults
fi
cat > /lib/systemd/system/mysqld.service <<-EOF
[Unit]
Description=mysql database server
After=network.target
[Service]
Type=notify
PrivateNetwork=false
Type=forking
Restart=no
TimeoutSec=5min
IgnoreSIGPIPE=no
KillMode=process
GuessMainPID=no
RemainAfterExit=yes
SuccessExitStatus=5 6
ExecStart=/etc/init.d/mysqld start
ExecStop=/etc/init.d//mysqld stop
ExecReload=/etc/init.d/mysqld reload
[Install]
WantedBy=multi-user.target
Alias=mysqld.service
EOF
systemctl daemon-reload
systemctl enable --now mysqld &> /dev/null
[ $? -ne 0 ] && { ${COLOR}"数据库启动失败，退出!"${END};exit; }
MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}' /data/mysql/mysql.log`
mysqladmin -uroot -p${MYSQL_OLDPASSWORD} password ${MYSQL_ROOT_PASSWORD} &>/dev/null
${COLOR}"MySQL数据库安装完成"${END}
}
main(){
os
check_file
install_mysql
}
main
#mysq8.0基于二进制包一键安装脚本
[root@centos7 ~]# cat install_mysql8.0_v3.sh
#!/bin/bash
#
#******************************************************************************
#Author: zhanghui
#QQ: 19661891
#Date: 2021-06-01
#FileName: install_mysql8.0_v3.sh
#URL: www.cnblogs.com/neteagles
#Description: install_mysql8.0 for centos 7/8 & ubuntu 18.04/20.04
#Copyright (C): 2021 All rights reserved
#******************************************************************************
SRC_DIR=/usr/local/src
COLOR="echo -e \\033[01;31m"
END='\033[0m'
MYSQL_URL=https://mirrors.cloud.tencent.com/mysql/downloads/MySQL-
MYSQL_VERSION='8.0/'
MYSQL_FILE='mysql-8.0.25-linux-glibc2.12-x86_64.tar.xz'
MYSQL_ROOT_PASSWORD=123456
os(){
if grep -Eqi "CentOS" /etc/issue || grep -Eq "CentOS" /etc/*-release;then
rpm -q redhat-lsb-core &> /dev/null || { ${COLOR}"安装lsb_release工具"${END};yum -y install redhat-lsb-core &> /dev/null; }
fi
OS_ID=`lsb_release -is`
OS_RELEASE_VERSION=`lsb_release -rs |awk -F'.' '{print $1}'`
}
check_file(){
cd ${SRC_DIR}
if [ ${OS_ID} == "CentOS" ] &> /dev/null;then
rpm -q wget &> /dev/null || yum -y install wget &> /dev/null
fi
if [ ! -e ${MYSQL_FILE} ];then
${COLOR}"缺少${MYSQL_FILE}文件"${END}
${COLOR}'开始下载MYSQL二进制安装包'${END}
wget ${MYSQL_URL}${MYSQL_VERSION}${MYSQL_FILE} || { ${COLOR}"MYSQL二进制安装包下载失败"${END}; exit; }
else
${COLOR}"${MYSQL_FILE}文件已准备好"${END}
fi
}
install_mysql(){
[ -d /usr/local/mysql ] && { ${COLOR}"MySQL数据库已存在，安装失败"${END};exit; }
${COLOR}"开始安装MySQL数据库..."${END}
${COLOR}'开始安装MYSQL依赖包'${END}
if [[ ${OS_RELEASE_VERSION} == 8 ]] &> /dev/null;then
yum -y install libaio perl-Data-Dumper ncurses-compat-libs &> /dev/null
elif [[ ${OS_RELEASE_VERSION} == 7 ]] &> /dev/null;then
yum -y install libaio perl-Data-Dumper &> /dev/null
else
apt update &> /dev/null;apt -y install numactl libaio-dev libtinfo5 &> /dev/null
fi
cd ${SRC_DIR}
tar xf ${MYSQL_FILE} -C /usr/local/
MYSQL_DIR=`echo ${MYSQL_FILE}| sed -nr 's/^(.*[0-9]).*/\1/p'`
ln -s /usr/local/${MYSQL_DIR} /usr/local/mysql
id mysql &> /dev/null || { useradd -s /sbin/nologin -r mysql ; ${COLOR}"创建mysql用户"${END}; }
chown -R mysql.mysql /usr/local/mysql/
echo 'PATH=/usr/local/mysql/bin/:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh
cat > /etc/my.cnf <<-EOF
[mysqld]
server-id=1
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
EOF
[ -d /data/mysql ] || mkdir -p /data/mysql &> /dev/null
chown -R mysql.mysql /data/mysql
mysqld --initialize --user=mysql --datadir=/data/mysql
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
if [ ${OS_ID} == "CentOS" ] &> /dev/null;then
chkconfig --add mysqld
else
update-rc.d -f mysqld defaults
fi
cat > /lib/systemd/system/mysqld.service <<-EOF
[Unit]
Description=mysql database server
After=network.target
[Service]
Type=notify
PrivateNetwork=false
Type=forking
Restart=no
TimeoutSec=5min
IgnoreSIGPIPE=no
KillMode=process
GuessMainPID=no
RemainAfterExit=yes
SuccessExitStatus=5 6
ExecStart=/etc/init.d/mysqld start
ExecStop=/etc/init.d//mysqld stop
ExecReload=/etc/init.d/mysqld reload
[Install]
WantedBy=multi-user.target
Alias=mysqld.service
EOF
systemctl daemon-reload
systemctl enable --now mysqld &> /dev/null
[ $? -ne 0 ] && { ${COLOR}"数据库启动失败，退出!"${END};exit; }
MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}' /data/mysql/mysql.log`
mysqladmin -uroot -p${MYSQL_OLDPASSWORD} password ${MYSQL_ROOT_PASSWORD} &>/dev/null
${COLOR}"MySQL数据库安装完成"${END}
}
main(){
os
check_file
install_mysql
}
main
二.时间字段进行过滤查询,并且timestamp可以随其它字段的更新自动更新
mysql> create table testdate (id int auto_increment primary key,name varchar(10),date timestamp DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.01 sec)
mysql> desc testdate;
+-------+-------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+-------------------+-------------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| date | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-------+-------------+------+-----+-------------------+-------------------+
3 rows in set (0.00 sec)
mysql> insert testdate (name) value('alice');
Query OK, 1 row affected (0.01 sec)
mysql> select * from testdate;
+----+-------+---------------------+
| id | name | date |
+----+-------+---------------------+
| 1 | alice | 2021-01-30 23:40:41 |
+----+-------+---------------------+
1 row in set (0.00 sec)
mysql> insert testdate (name) value('bom');
Query OK, 1 row affected (0.00 sec)
mysql> select * from testdate;
+----+-------+---------------------+
| id | name | date |
+----+-------+---------------------+
| 1 | alice | 2021-01-30 23:40:41 |
| 2 | bom | 2021-01-30 23:41:29 |
+----+-------+---------------------+
2 rows in set (0.00 sec)
mysql> update testdate set name='hong' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from testdate;
+----+------+---------------------+
| id | name | date |
+----+------+---------------------+
| 1 | hong | 2021-01-30 23:40:41 |
| 2 | bom | 2021-01-30 23:41:29 |
+----+------+---------------------+
2 rows in set (0.00 sec)
mysql> insert testdate (name) value('ming');
Query OK, 1 row affected (0.00 sec)
mysql> insert testdate (name) value('zhang');
Query OK, 1 row affected (0.00 sec)
mysql> select * from testdate;
+----+-------+---------------------+
| id | name | date |
+----+-------+---------------------+
| 1 | hong | 2021-01-30 23:40:41 |
| 2 | bom | 2021-01-30 23:41:29 |
| 3 | ming | 2021-01-30 23:47:12 |
| 4 | zhang | 2021-01-30 23:47:16 |
+----+-------+---------------------+
4 rows in set (0.00 sec)
mysql> select * from testdate where date >= '2021-01-30 23:40:00' and date <= '2021-01-30 23:47:00';
+----+------+---------------------+
| id | name | date |
+----+------+---------------------+
| 1 | hong | 2021-01-30 23:40:41 |
| 2 | bom | 2021-01-30 23:41:29 |
+----+------+---------------------+
2 rows in set (0.00 sec)
mysql> select * from testdate where date between '2021-01-30 23:40:00' and '2021-01-30 23:47:00';
+----+------+---------------------+
| id | name | date |
+----+------+---------------------+
| 1 | hong | 2021-01-30 23:40:41 |
| 2 | bom | 2021-01-30 23:41:29 |
+----+------+---------------------+
2 rows in set (0.00 sec)
三.Mysql数据库DQL语句-select查询
[root@centos8 ~]# cat hellodb_innodb.sql
-- MySQL dump 10.13 Distrib 5.5.33, for Linux (x86_64)
--
-- Host: localhost Database: hellodb
-- ------------------------------------------------------
-- Server version 5.5.33-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `hellodb`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `hellodb`;
--
-- Table structure for table `classes`
--
DROP TABLE IF EXISTS `classes`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `classes` (
`ClassID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`Class` varchar(100) DEFAULT NULL,
`NumOfStu` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`ClassID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `classes`
--
LOCK TABLES `classes` WRITE;
/*!40000 ALTER TABLE `classes` DISABLE KEYS */;
INSERT INTO `classes` VALUES (1,'Shaolin Pai',10),(2,'Emei Pai',7),(3,'QingCheng Pai',11),(4,'Wudang Pai',12),(5,'Riyue Shenjiao',31),(6,'Lianshan Pai',27),(7,'Ming Jiao',27),(8,'Xiaoyao Pai',15);
/*!40000 ALTER TABLE `classes` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `coc`
--
DROP TABLE IF EXISTS `coc`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `coc` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ClassID` tinyint(3) unsigned NOT NULL,
`CourseID` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `coc`
--
LOCK TABLES `coc` WRITE;
/*!40000 ALTER TABLE `coc` DISABLE KEYS */;
INSERT INTO `coc` VALUES (1,1,2),(2,1,5),(3,2,2),(4,2,6),(5,3,1),(6,3,7),(7,4,5),(8,4,2),(9,5,1),(10,5,9),(11,6,3),(12,6,4),(13,7,4),(14,7,3);
/*!40000 ALTER TABLE `coc` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `courses`
--
DROP TABLE IF EXISTS `courses`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `courses` (
`CourseID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`Course` varchar(100) NOT NULL,
PRIMARY KEY (`CourseID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `courses`
--
LOCK TABLES `courses` WRITE;
/*!40000 ALTER TABLE `courses` DISABLE KEYS */;
INSERT INTO `courses` VALUES (1,'Hamo Gong'),(2,'Kuihua Baodian'),(3,'Jinshe Jianfa'),(4,'Taiji Quan'),(5,'Daiyu Zanghua'),(6,'Weituo Zhang'),(7,'Dagou Bangfa');
/*!40000 ALTER TABLE `courses` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `scores`
--
DROP TABLE IF EXISTS `scores`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `scores` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`StuID` int(10) unsigned NOT NULL,
`CourseID` smallint(5) unsigned NOT NULL,
`Score` tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `scores`
--
LOCK TABLES `scores` WRITE;
/*!40000 ALTER TABLE `scores` DISABLE KEYS */;
INSERT INTO `scores` VALUES (1,1,2,77),(2,1,6,93),(3,2,2,47),(4,2,5,97),(5,3,2,88),(6,3,6,75),(7,4,5,71),(8,4,2,89),(9,5,1,39),(10,5,7,63),(11,6,1,96),(12,7,1,86),(13,7,7,83),(14,8,4,57),(15,8,3,93);
/*!40000 ALTER TABLE `scores` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `students`
--
DROP TABLE IF EXISTS `students`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `students` (
`StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
`Age` tinyint(3) unsigned NOT NULL,
`Gender` enum('F','M') NOT NULL,
`ClassID` tinyint(3) unsigned DEFAULT NULL,
`TeacherID` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`StuID`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `students`
--
LOCK TABLES `students` WRITE;
/*!40000 ALTER TABLE `students` DISABLE KEYS */;
INSERT INTO `students` VALUES (1,'Shi Zhongyu',22,'M',2,3),(2,'Shi Potian',22,'M',1,7),(3,'Xie Yanke',53,'M',2,16),(4,'Ding Dian',32,'M',4,4),(5,'Yu Yutong',26,'M',3,1),(6,'Shi Qing',46,'M',5,NULL),(7,'Xi Ren',19,'F',3,NULL),(8,'Lin Daiyu',17,'F',7,NULL),(9,'Ren Yingying',20,'F',6,NULL),(10,'Yue Lingshan',19,'F',3,NULL),(11,'Yuan Chengzhi',23,'M',6,NULL),(12,'Wen Qingqing',19,'F',1,NULL),(13,'Tian Boguang',33,'M',2,NULL),(14,'Lu Wushuang',17,'F',3,NULL),(15,'Duan Yu',19,'M',4,NULL),(16,'Xu Zhu',21,'M',1,NULL),(17,'Lin Chong',25,'M',4,NULL),(18,'Hua Rong',23,'M',7,NULL),(19,'Xue Baochai',18,'F',6,NULL),(20,'Diao Chan',19,'F',7,NULL),(21,'Huang Yueying',22,'F',6,NULL),(22,'Xiao Qiao',20,'F',1,NULL),(23,'Ma Chao',23,'M',4,NULL),(24,'Xu Xian',27,'M',NULL,NULL),(25,'Sun Dasheng',100,'M',NULL,NULL);
/*!40000 ALTER TABLE `students` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `teachers`
--
DROP TABLE IF EXISTS `teachers`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `teachers` (
`TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(100) NOT NULL,
`Age` tinyint(3) unsigned NOT NULL,
`Gender` enum('F','M') DEFAULT NULL,
PRIMARY KEY (`TID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `teachers`
--
LOCK TABLES `teachers` WRITE;
/*!40000 ALTER TABLE `teachers` DISABLE KEYS */;
INSERT INTO `teachers` VALUES (1,'Song Jiang',45,'M'),(2,'Zhang Sanfeng',94,'M'),(3,'Miejue Shitai',77,'F'),(4,'Lin Chaoying',93,'F');
/*!40000 ALTER TABLE `teachers` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `toc`
--
DROP TABLE IF EXISTS `toc`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `toc` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`CourseID` smallint(5) unsigned DEFAULT NULL,
`TID` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `toc`
--
LOCK TABLES `toc` WRITE;
/*!40000 ALTER TABLE `toc` DISABLE KEYS */;
/*!40000 ALTER TABLE `toc` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2013-09-03 2:51:27
#导入hellodb.sql生成数据库
[root@centos8 ~]# mysql < hellodb_innodb.sql
[root@centos8 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.21 Source distribution
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
mysql> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
mysql> select * from courses;
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
+----------+----------------+
7 rows in set (0.00 sec)
mysql> select * from scores;
+----+-------+----------+-------+
| ID | StuID | CourseID | Score |
+----+-------+----------+-------+
| 1 | 1 | 2 | 77 |
| 2 | 1 | 6 | 93 |
| 3 | 2 | 2 | 47 |
| 4 | 2 | 5 | 97 |
| 5 | 3 | 2 | 88 |
| 6 | 3 | 6 | 75 |
| 7 | 4 | 5 | 71 |
| 8 | 4 | 2 | 89 |
| 9 | 5 | 1 | 39 |
| 10 | 5 | 7 | 63 |
| 11 | 6 | 1 | 96 |
| 12 | 7 | 1 | 86 |
| 13 | 7 | 7 | 83 |
| 14 | 8 | 4 | 57 |
| 15 | 8 | 3 | 93 |
+----+-------+----------+-------+
15 rows in set (0.00 sec)
3.1在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
mysql> select name,age from students where age >25 and gender = 'M';
+--------------+-----+
| name | age |
+--------------+-----+
| Xie Yanke | 53 |
| Ding Dian | 32 |
| Yu Yutong | 26 |
| Shi Qing | 46 |
| Tian Boguang | 33 |
| Xu Xian | 27 |
| Sun Dasheng | 100 |
+--------------+-----+
7 rows in set (0.00 sec)
3.2以ClassID为分组依据，显示每组的平均年龄
mysql> select classid,avg(age) 平均年龄 from students group by classid;
+---------+--------------+
| classid | 平均年龄 |
+---------+--------------+
| 2 | 36.0000 |
| 1 | 20.5000 |
| 4 | 24.7500 |
| 3 | 20.2500 |
| 5 | 46.0000 |
| 7 | 19.6667 |
| 6 | 20.7500 |
| NULL | 63.5000 |
+---------+--------------+
8 rows in set (0.00 sec)
3.3显示第2题中平均年龄大于30的分组及平均年龄
mysql> select classid,avg(age) 平均年龄 from students group by classid having 平均年龄 > 30;
+---------+--------------+
| classid | 平均年龄 |
+---------+--------------+
| 2 | 36.0000 |
| 5 | 46.0000 |
| NULL | 63.5000 |
+---------+--------------+
3 rows in set (0.00 sec)
3.4显示以L开头的名字的同学的信息
mysql> select * from students where name like 'L%';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.01 sec)
3.5显示TeacherID非空的同学的相关信息
mysql> select * from students where teacherid is not null;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
+-------+-------------+-----+--------+---------+-----------+
5 rows in set (0.01 sec)
mysql> select * from students where teacherid > 0;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
+-------+-------------+-----+--------+---------+-----------+
5 rows in set (0.00 sec)
3.6以年龄排序后，显示年龄最大的前10位同学的信息
mysql> select * from students order by age desc limit 10;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
+-------+--------------+-----+--------+---------+-----------+
10 rows in set (0.00 sec)
3.7查询年龄大于等于20岁，小于等于25岁的同学的信息
mysql> select * from students where age >= 20 and age <= 25;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
+-------+---------------+-----+--------+---------+-----------+
10 rows in set (0.00 sec)
mysql> select * from students where age between 20 and 25;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
+-------+---------------+-----+--------+---------+-----------+
10 rows in set (0.00 sec)
3.8以ClassID分组，显示每班的同学的人数
mysql> select classid,count(*) 数量 from students group by classid;
+---------+--------+
| classid | 数量 |
+---------+--------+
| 2 | 3 |
| 1 | 4 |
| 4 | 4 |
| 3 | 4 |
| 5 | 1 |
| 7 | 3 |
| 6 | 4 |
| NULL | 2 |
+---------+--------+
8 rows in set (0.00 sec)
mysql> select classid,count(stuid) 数量 from students group by classid;
+---------+--------+
| classid | 数量 |
+---------+--------+
| 2 | 3 |
| 1 | 4 |
| 4 | 4 |
| 3 | 4 |
| 5 | 1 |
| 7 | 3 |
| 6 | 4 |
| NULL | 2 |
+---------+--------+
8 rows in set (0.01 sec)
3.9以Gender分组，显示其年龄之和
mysql> select gender,sum(age) from students group by gender;
+--------+----------+
| gender | sum(age) |
+--------+----------+
| M | 495 |
| F | 190 |
+--------+----------+
2 rows in set (0.00 sec)
3.10以ClassID分组，显示其平均年龄大于25的班级
mysql> select classid,avg(age) 平均年龄 from students group by classid having 平均年龄 > 25;
+---------+--------------+
| classid | 平均年龄 |
+---------+--------------+
| 2 | 36.0000 |
| 5 | 46.0000 |
| NULL | 63.5000 |
+---------+--------------+
3 rows in set (0.00 sec)
3.11以Gender分组，显示各组中年龄大于25的学员的年龄之和
mysql> select gender,sum(age) from students where age > 25 group by gender;
+--------+----------+
| gender | sum(age) |
+--------+----------+
| M | 317 |
+--------+----------+
1 row in set (0.00 sec)
3.12显示前5位同学的姓名、课程及成绩
mysql> select * from students st inner join scores sc on st.stuid=sc.stuid;
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+
| StuID | Name | Age | Gender | ClassID | TeacherID | ID | StuID | CourseID | Score |
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 1 | 1 | 2 | 77 |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 2 | 1 | 6 | 93 |
| 2 | Shi Potian | 22 | M | 1 | 7 | 3 | 2 | 2 | 47 |
| 2 | Shi Potian | 22 | M | 1 | 7 | 4 | 2 | 5 | 97 |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 5 | 3 | 2 | 88 |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 6 | 3 | 6 | 75 |
| 4 | Ding Dian | 32 | M | 4 | 4 | 7 | 4 | 5 | 71 |
| 4 | Ding Dian | 32 | M | 4 | 4 | 8 | 4 | 2 | 89 |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 9 | 5 | 1 | 39 |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 10 | 5 | 7 | 63 |
| 6 | Shi Qing | 46 | M | 5 | NULL | 11 | 6 | 1 | 96 |
| 7 | Xi Ren | 19 | F | 3 | NULL | 12 | 7 | 1 | 86 |
| 7 | Xi Ren | 19 | F | 3 | NULL | 13 | 7 | 7 | 83 |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | 14 | 8 | 4 | 57 |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | 15 | 8 | 3 | 93 |
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+
15 rows in set (0.00 sec)
mysql> select * from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid ;
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+----------+----------------+
| StuID | Name | Age | Gender | ClassID | TeacherID | ID | StuID | CourseID | Score | CourseID | Course |
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+----------+----------------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 1 | 1 | 2 | 77 | 2 | Kuihua Baodian |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 2 | 1 | 6 | 93 | 6 | Weituo Zhang |
| 2 | Shi Potian | 22 | M | 1 | 7 | 3 | 2 | 2 | 47 | 2 | Kuihua Baodian |
| 2 | Shi Potian | 22 | M | 1 | 7 | 4 | 2 | 5 | 97 | 5 | Daiyu Zanghua |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 5 | 3 | 2 | 88 | 2 | Kuihua Baodian |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 6 | 3 | 6 | 75 | 6 | Weituo Zhang |
| 4 | Ding Dian | 32 | M | 4 | 4 | 7 | 4 | 5 | 71 | 5 | Daiyu Zanghua |
| 4 | Ding Dian | 32 | M | 4 | 4 | 8 | 4 | 2 | 89 | 2 | Kuihua Baodian |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 9 | 5 | 1 | 39 | 1 | Hamo Gong |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 10 | 5 | 7 | 63 | 7 | Dagou Bangfa |
| 6 | Shi Qing | 46 | M | 5 | NULL | 11 | 6 | 1 | 96 | 1 | Hamo Gong |
| 7 | Xi Ren | 19 | F | 3 | NULL | 12 | 7 | 1 | 86 | 1 | Hamo Gong |
| 7 | Xi Ren | 19 | F | 3 | NULL | 13 | 7 | 7 | 83 | 7 | Dagou Bangfa |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | 14 | 8 | 4 | 57 | 4 | Taiji Quan |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | 15 | 8 | 3 | 93 | 3 | Jinshe Jianfa |
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+----------+----------------+
15 rows in set (0.00 sec)
#以stuid前5排序
mysql> select name,course,score from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid where st.stuid between 1 and 5;
+-------------+----------------+-------+
| name | course | score |
+-------------+----------------+-------+
| Shi Zhongyu | Kuihua Baodian | 77 |
| Shi Zhongyu | Weituo Zhang | 93 |
| Shi Potian | Kuihua Baodian | 47 |
| Shi Potian | Daiyu Zanghua | 97 |
| Xie Yanke | Kuihua Baodian | 88 |
| Xie Yanke | Weituo Zhang | 75 |
| Ding Dian | Daiyu Zanghua | 71 |
| Ding Dian | Kuihua Baodian | 89 |
| Yu Yutong | Hamo Gong | 39 |
| Yu Yutong | Dagou Bangfa | 63 |
+-------------+----------------+-------+
10 rows in set (0.01 sec)
mysql> select name,course,score from students st inner join scores sc inner join courses co on st.stuid=sc.stuid and sc.courseid=co.courseid where st.stuid between 1 and 5;
+-------------+----------------+-------+
| name | course | score |
+-------------+----------------+-------+
| Shi Zhongyu | Kuihua Baodian | 77 |
| Shi Zhongyu | Weituo Zhang | 93 |
| Shi Potian | Kuihua Baodian | 47 |
| Shi Potian | Daiyu Zanghua | 97 |
| Xie Yanke | Kuihua Baodian | 88 |
| Xie Yanke | Weituo Zhang | 75 |
| Ding Dian | Daiyu Zanghua | 71 |
| Ding Dian | Kuihua Baodian | 89 |
| Yu Yutong | Hamo Gong | 39 |
| Yu Yutong | Dagou Bangfa | 63 |
+-------------+----------------+-------+
10 rows in set (0.00 sec)
#以score前五排序
mysql> select name,course,score from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid order by score desc limit 5;
+-------------+----------------+-------+
| name | course | score |
+-------------+----------------+-------+
| Shi Potian | Daiyu Zanghua | 97 |
| Shi Qing | Hamo Gong | 96 |
| Shi Zhongyu | Weituo Zhang | 93 |
| Lin Daiyu | Jinshe Jianfa | 93 |
| Ding Dian | Kuihua Baodian | 89 |
+-------------+----------------+-------+
5 rows in set (0.01 sec)
mysql> select name,course,score from students st inner join scores sc inner join courses co on st.stuid=sc.stuid and sc.courseid=co.courseid order by score desc limit 5;
+-------------+----------------+-------+
| name | course | score |
+-------------+----------------+-------+
| Shi Potian | Daiyu Zanghua | 97 |
| Shi Qing | Hamo Gong | 96 |
| Shi Zhongyu | Weituo Zhang | 93 |
| Lin Daiyu | Jinshe Jianfa | 93 |
| Ding Dian | Kuihua Baodian | 89 |
+-------------+----------------+-------+
5 rows in set (0.00 sec)
3.13显示其成绩高于80的同学的名称及课程
mysql> select name,course from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid where score > 80;
+-------------+----------------+
| name | course |
+-------------+----------------+
| Shi Zhongyu | Weituo Zhang |
| Shi Potian | Daiyu Zanghua |
| Xie Yanke | Kuihua Baodian |
| Ding Dian | Kuihua Baodian |
| Shi Qing | Hamo Gong |
| Xi Ren | Hamo Gong |
| Xi Ren | Dagou Bangfa |
| Lin Daiyu | Jinshe Jianfa |
+-------------+----------------+
8 rows in set (0.00 sec)
mysql> select name,course,score from students inner join scores inner join courses on students.stuid=scores.stuid and scores.courseid=courses.courseid where score>80;
+-------------+----------------+-------+
| name | course | score |
+-------------+----------------+-------+
| Shi Zhongyu | Weituo Zhang | 93 |
| Shi Potian | Daiyu Zanghua | 97 |
| Xie Yanke | Kuihua Baodian | 88 |
| Ding Dian | Kuihua Baodian | 89 |
| Shi Qing | Hamo Gong | 96 |
| Xi Ren | Hamo Gong | 86 |
| Xi Ren | Dagou Bangfa | 83 |
| Lin Daiyu | Jinshe Jianfa | 93 |
+-------------+----------------+-------+
8 rows in set (0.00 sec)
select name,course,score
from students,scores,courses
where students.stuid=scores.stuid and scores.courseid=courses.courseid and score>80;
+-------------+----------------+-------+
| name | course | score |
+-------------+----------------+-------+
| Shi Zhongyu | Weituo Zhang | 93 |
| Shi Potian | Daiyu Zanghua | 97 |
| Xie Yanke | Kuihua Baodian | 88 |
| Ding Dian | Kuihua Baodian | 89 |
| Shi Qing | Hamo Gong | 96 |
| Xi Ren | Hamo Gong | 86 |
| Xi Ren | Dagou Bangfa | 83 |
| Lin Daiyu | Jinshe Jianfa | 93 |
+-------------+----------------+-------+
8 rows in set (0.00 sec)
3.14取每位同学各门课的平均成绩，显示成绩前三名的同学的姓名和平均成绩
mysql> select name,avg(score) 平均成绩 from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid group by name order by 平均成绩 desc limit 3;
+-------------+--------------+
| name | 平均成绩 |
+-------------+--------------+
| Shi Qing | 96.0000 |
| Shi Zhongyu | 85.0000 |
| Xi Ren | 84.5000 |
+-------------+--------------+
3 rows in set (0.00 sec)
3.15显示每门课程课程名称及学习了这门课的同学的个数
mysql> select course,count(*) from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid group by course;
+----------------+----------+
| course | count(*) |
+----------------+----------+
| Kuihua Baodian | 4 |
| Weituo Zhang | 2 |
| Daiyu Zanghua | 2 |
| Hamo Gong | 3 |
| Dagou Bangfa | 2 |
| Taiji Quan | 1 |
| Jinshe Jianfa | 1 |
+----------------+----------+
7 rows in set (0.00 sec)
3.16显示其年龄大于平均年龄的同学的名字
mysql> select avg(age) 平均年龄 from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid ;
+--------------+
| 平均年龄 |
+--------------+
| 28.5333 |
+--------------+
1 row in set (0.00 sec)
mysql> select distinct name from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.couurseid where age > (select avg(age) 平均年龄 from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on scc.courseid=co.courseid );
+-----------+
| name |
+-----------+
| Xie Yanke |
| Ding Dian |
| Shi Qing |
+-----------+
3 rows in set (0.01 sec)
mysql> select avg(age) 平均年龄 from students;
+--------------+
| 平均年龄 |
+--------------+
| 27.4000 |
+--------------+
1 row in set (0.00 sec)
mysql> select name,age from students where age > (select avg(age) 平均年龄 from students);
+--------------+-----+
| name | age |
+--------------+-----+
| Xie Yanke | 53 |
| Ding Dian | 32 |
| Shi Qing | 46 |
| Tian Boguang | 33 |
| Sun Dasheng | 100 |
+--------------+-----+
5 rows in set (0.00 sec)
3.17显示其学习的课程为第1、2，4或第7门课的同学的名字
#students表和scores 关联
mysql> select * from students st inner join scores sc on st.stuid=sc.stuid where sc.courseid in (1,2,4) or sc.courseid = 7;
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+
| StuID | Name | Age | Gender | ClassID | TeacherID | ID | StuID | CourseID | Score |
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 1 | 1 | 2 | 77 |
| 2 | Shi Potian | 22 | M | 1 | 7 | 3 | 2 | 2 | 47 |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 5 | 3 | 2 | 88 |
| 4 | Ding Dian | 32 | M | 4 | 4 | 8 | 4 | 2 | 89 |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 9 | 5 | 1 | 39 |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 10 | 5 | 7 | 63 |
| 6 | Shi Qing | 46 | M | 5 | NULL | 11 | 6 | 1 | 96 |
| 7 | Xi Ren | 19 | F | 3 | NULL | 12 | 7 | 1 | 86 |
| 7 | Xi Ren | 19 | F | 3 | NULL | 13 | 7 | 7 | 83 |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | 14 | 8 | 4 | 57 |
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+
10 rows in set (0.00 sec)
mysql> select distinct st.name from students st inner join scores sc on st.stuid=sc.stuid where sc.courseid in (1,2,4) or sc.courseid = 7;
+-------------+
| name |
+-------------+
| Shi Zhongyu |
| Shi Potian |
| Xie Yanke |
| Ding Dian |
| Yu Yutong |
| Shi Qing |
| Xi Ren |
| Lin Daiyu |
+-------------+
8 rows in set (0.000 sec)
mysql> select distinct st.name from students st inner join scores sc on st.stuid=sc.stuid where sc.courseid in (1,2,4,7);
+-------------+
| name |
+-------------+
| Shi Zhongyu |
| Shi Potian |
| Xie Yanke |
| Ding Dian |
| Yu Yutong |
| Shi Qing |
| Xi Ren |
| Lin Daiyu |
+-------------+
8 rows in set (0.000 sec)
#students表和coc 关联
MariaDB [hellodb]> select * from students st inner join coc co on st.classid=co.classid where co.courseid in (1,2,4) or co.courseid = 7;
+-------+---------------+-----+--------+---------+-----------+----+---------+----------+
| StuID | Name | Age | Gender | ClassID | TeacherID | ID | ClassID | CourseID |
+-------+---------------+-----+--------+---------+-----------+----+---------+----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | 2 | 2 |
| 2 | Shi Potian | 22 | M | 1 | 7 | 1 | 1 | 2 |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 3 | 2 | 2 |
| 4 | Ding Dian | 32 | M | 4 | 4 | 8 | 4 | 2 |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 5 | 3 | 1 |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 6 | 3 | 7 |
| 6 | Shi Qing | 46 | M | 5 | NULL | 9 | 5 | 1 |
| 7 | Xi Ren | 19 | F | 3 | NULL | 5 | 3 | 1 |
| 7 | Xi Ren | 19 | F | 3 | NULL | 6 | 3 | 7 |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | 13 | 7 | 4 |
| 9 | Ren Yingying | 20 | F | 6 | NULL | 12 | 6 | 4 |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | 5 | 3 | 1 |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | 6 | 3 | 7 |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 12 | 6 | 4 |
| 12 | Wen Qingqing | 19 | F | 1 | NULL | 1 | 1 | 2 |
| 13 | Tian Boguang | 33 | M | 2 | NULL | 3 | 2 | 2 |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | 5 | 3 | 1 |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | 6 | 3 | 7 |
| 15 | Duan Yu | 19 | M | 4 | NULL | 8 | 4 | 2 |
| 16 | Xu Zhu | 21 | M | 1 | NULL | 1 | 1 | 2 |
| 17 | Lin Chong | 25 | M | 4 | NULL | 8 | 4 | 2 |
| 18 | Hua Rong | 23 | M | 7 | NULL | 13 | 7 | 4 |
| 19 | Xue Baochai | 18 | F | 6 | NULL | 12 | 6 | 4 |
| 20 | Diao Chan | 19 | F | 7 | NULL | 13 | 7 | 4 |
| 21 | Huang Yueying | 22 | F | 6 | NULL | 12 | 6 | 4 |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | 1 | 1 | 2 |
| 23 | Ma Chao | 23 | M | 4 | NULL | 8 | 4 | 2 |
+-------+---------------+-----+--------+---------+-----------+----+---------+----------+
27 rows in set (0.001 sec)
MariaDB [hellodb]> select distinct name from students st inner join coc co on st.classid=co.classid where co.courseid in (1,2,4) or co.courseid = 7;
+---------------+
| name |
+---------------+
| Shi Zhongyu |
| Shi Potian |
| Xie Yanke |
| Ding Dian |
| Yu Yutong |
| Shi Qing |
| Xi Ren |
| Lin Daiyu |
| Ren Yingying |
| Yue Lingshan |
| Yuan Chengzhi |
| Wen Qingqing |
| Tian Boguang |
| Lu Wushuang |
| Duan Yu |
| Xu Zhu |
| Lin Chong |
| Hua Rong |
| Xue Baochai |
| Diao Chan |
| Huang Yueying |
| Xiao Qiao |
| Ma Chao |
+---------------+
23 rows in set (0.001 sec)
MariaDB [hellodb]> select distinct name from students st inner join coc co on st.classid=co.classid where co.courseid in (1,2,4,7);
+---------------+
| name |
+---------------+
| Shi Zhongyu |
| Shi Potian |
| Xie Yanke |
| Ding Dian |
| Yu Yutong |
| Shi Qing |
| Xi Ren |
| Lin Daiyu |
| Ren Yingying |
| Yue Lingshan |
| Yuan Chengzhi |
| Wen Qingqing |
| Tian Boguang |
| Lu Wushuang |
| Duan Yu |
| Xu Zhu |
| Lin Chong |
| Hua Rong |
| Xue Baochai |
| Diao Chan |
| Huang Yueying |
| Xiao Qiao |
| Ma Chao |
+---------------+
23 rows in set (0.001 sec)
3.18显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学
#方法1
mysql> select classid,count(stuid),avg(age) 平均年龄 from students group by classid having count(stuid)>=3;
+---------+--------------+--------------+
| classid | count(stuid) | 平均年龄 |
+---------+--------------+--------------+
| 2 | 3 | 36.0000 |
| 1 | 4 | 20.5000 |
| 4 | 4 | 24.7500 |
| 3 | 4 | 20.2500 |
| 7 | 3 | 19.6667 |
| 6 | 4 | 20.7500 |
+---------+--------------+--------------+
6 rows in set (0.00 sec)
mysql> select * from students a inner join (select classid,count(stuid),avg(age) 平均年龄 from students group by classid having couunt(stuid)>=3) b on a.classid=b.classid where a.age > b.平均年龄;
+-------+---------------+-----+--------+---------+-----------+---------+--------------+--------------+
| StuID | Name | Age | Gender | ClassID | TeacherID | classid | count(stuid) | 平均年龄 |
+-------+---------------+-----+--------+---------+-----------+---------+--------------+--------------+
| 2 | Shi Potian | 22 | M | 1 | 7 | 1 | 4 | 20.5000 |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 2 | 3 | 36.0000 |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | 4 | 24.7500 |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 3 | 4 | 20.2500 |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 6 | 4 | 20.7500 |
| 16 | Xu Zhu | 21 | M | 1 | NULL | 1 | 4 | 20.5000 |
| 17 | Lin Chong | 25 | M | 4 | NULL | 4 | 4 | 24.7500 |
| 18 | Hua Rong | 23 | M | 7 | NULL | 7 | 3 | 19.6667 |
| 21 | Huang Yueying | 22 | F | 6 | NULL | 6 | 4 | 20.7500 |
+-------+---------------+-----+--------+---------+-----------+---------+--------------+--------------+
9 rows in set (0.00 sec)
mysql> select name from students a inner join (select classid,count(stuid),avg(age) 平均年龄 from students group by classid having count(stuid)>=3) b on a.classid=b.classid where a.age > b.平均年龄;
+---------------+
| name |
+---------------+
| Shi Potian |
| Xie Yanke |
| Ding Dian |
| Yu Yutong |
| Yuan Chengzhi |
| Xu Zhu |
| Lin Chong |
| Hua Rong |
| Huang Yueying |
+---------------+
9 rows in set (0.00 sec)
#方法2
mysql> select classid,count(*) 人数,avg(age) 平均年龄 from students group by classid having count(stuid)>=3;
+---------+--------+--------------+
| classid | 人数 | 平均年龄 |
+---------+--------+--------------+
| 2 | 3 | 36.0000 |
| 1 | 4 | 20.5000 |
| 4 | 4 | 24.7500 |
| 3 | 4 | 20.2500 |
| 7 | 3 | 19.6667 |
| 6 | 4 | 20.7500 |
+---------+--------+--------------+
6 rows in set (0.00 sec)
mysql> select * from students a inner join (select classid,count(*) 人数,avg(age) 平均年龄 from students group by classid having count(stuid)>=3) b on a.classid=b.classid where a.age > b.平均年龄;
+-------+---------------+-----+--------+---------+-----------+---------+--------+--------------+
| StuID | Name | Age | Gender | ClassID | TeacherID | classid | 人数 | 平均年龄 |
+-------+---------------+-----+--------+---------+-----------+---------+--------+--------------+
| 2 | Shi Potian | 22 | M | 1 | 7 | 1 | 4 | 20.5000 |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 2 | 3 | 36.0000 |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | 4 | 24.7500 |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 3 | 4 | 20.2500 |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 6 | 4 | 20.7500 |
| 16 | Xu Zhu | 21 | M | 1 | NULL | 1 | 4 | 20.5000 |
| 17 | Lin Chong | 25 | M | 4 | NULL | 4 | 4 | 24.7500 |
| 18 | Hua Rong | 23 | M | 7 | NULL | 7 | 3 | 19.6667 |
| 21 | Huang Yueying | 22 | F | 6 | NULL | 6 | 4 | 20.7500 |
+-------+---------------+-----+--------+---------+-----------+---------+--------+--------------+
9 rows in set (0.00 sec)
mysql> select name from students a inner join (select classid,count(*) 人数,avg(age) 平均年龄 from students group by classid havingg count(stuid)>=3) b on a.classid=b.classid where a.age > b.平均年龄;
+---------------+
| name |
+---------------+
| Shi Potian |
| Xie Yanke |
| Ding Dian |
| Yu Yutong |
| Yuan Chengzhi |
| Xu Zhu |
| Lin Chong |
| Hua Rong |
| Huang Yueying |
+---------------+
9 rows in set (0.00 sec)
3.19统计各班级中年龄大于全校同学平均年龄的同学
mysql> select avg(age) from students;
+----------+
| avg(age) |
+----------+
| 27.4000 |
+----------+
1 row in set (0.00 sec)
mysql> select * from students where age > (select avg(age) from students);
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+--------------+-----+--------+---------+-----------+
5 rows in set (0.00 sec)
mysql> select name from students where age > (select avg(age) from students);
+--------------+
| name |
+--------------+
| Xie Yanke |
| Ding Dian |
| Shi Qing |
| Tian Boguang |
| Sun Dasheng |
+--------------+
5 rows in set (0.00 sec)
四.MyISAM存储引擎和InnoDB存储引擎的区别
MyISAM存储引擎
不支持聚集索引 Clustered indexes
不支持数据缓存 Data caches
不支持外键 Foreign key support
支持锁定粒度:table (加表级锁) Locking granularity
不支持MVCC （多版本并发控制机制）
不支持事务 Transactions
InnoDB存储引擎
支持聚集索引 Clustered indexes
支持数据缓存 Data caches
支持外键 Foreign key support
支持锁定粒度:Row （加行级锁） Locking granularity
支持MVCC （多版本并发控制机制）
支持事务 Transactions
五.InnoDB中一颗的B+树可以存放多少行数据？
假设定义一颗B+树高度为2，即一个根节点和若干叶子节点。那么这棵B+树的存放总行记录数=根节点指针数*单个叶子记录的行数。这里先计算叶子节点，B+树中的单个叶子节点的大小为16K，假设每一条目为1K，那么记录数即为16(16k/1K=16)，然后计算非叶子节点能够存放多少个指针，假设主键ID为bigint类型，那么长度为8字节，而指针大小在InnoDB中是设置为6个字节，这样加起来一共是14个字节。那么通过页大小/(主键ID大小+指针大小），即16384/14=1170个指针，所以一颗高度为2的B+树能存放16*1170=18720条这样的记录。根据这个原理就可以算出一颗高度为3的B+树可以存放16*1170*1170=21902400条记录。所以在InnoDB中B+树高度一般为2-3层，它就能满足千万级的数据存储
六.事务特性
ACID特性：
A：atomicity原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态
I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔
离级别，实现并发
D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中
七.事务隔离级别
READ UNCOMMITTED
可读取到未提交数据，产生脏读
READ COMMITTED
可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次
读取数据不一致
REPEATABLE READ
可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍
只能读取到未修改前的旧数据。此为MySQL默认设置
SERIALIZABLE
可串行化，未提交的读事务阻塞修改事务（加读锁，但不阻塞读事务），或者未提交的修改事务阻
塞读事务（加写锁，其它事务的读，写都不可以执行）。会导致并发性能差
八.慢查询日志设置多少秒
mysql> select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
mysql> set global slow_query_log=1; #开启慢查询日志
Query OK, 0 rows affected (0.00 sec)
mysql> select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
mysql> select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 10.000000 |
+-------------------+
1 row in set (0.00 sec)
#默认设置10秒
mysql> select @@slow_query_log_file;
+---------------------------------+
| @@slow_query_log_file |
+---------------------------------+
| /var/lib/mysql/centos8-slow.log |
+---------------------------------+
1 row in set (0.00 sec)
#慢查询日志存放路径
[root@centos8 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
slow_query_log #开启慢查询日志
long_query_time=3 #设置慢查询日志时间为3秒
:wq
[root@centos8 ~]# systemctl restart mysqld
mysql> select sleep(1) from teachers;
+----------+
| sleep(1) |
+----------+
| 0 |
| 0 |
| 0 |
| 0 |
+----------+
4 rows in set (4.03 sec)
[root@centos8 ~]# tail -f /var/lib/mysql/centos8-slow.log
SET timestamp=1612688316;
select sleep(1) from teachers;
#超过3秒的慢查询日志，都会记录
九.mysql 主从复制原理
1.主节点数据更新
2.生成Bin Log(二进制日志)
3.在主节点开启dump Thread线程，负责把二进制日志从主节点发送给从节点
4.从节点开启I/O Thread线程，负责接收网络发过来的二进制日志
5.把接收到的二进制日志放到中继日志中
6.开启SQL Thread线程，把中继日志中的内容，读取到内存中执行
7.进行执行就更改了数据库，数据库中的内容保持和主节点的内容保持一致，从而最终就实现了主从复制
十.MySQL主从数据不一致
1.造成主从不一致的原因
- 主库binlog格式为Statement（语句型），同步到从库执行后可能造成主从不一致。（把binlog 改成行型）
- 主库执行更改前有执行set sql_log_bin=0，会使主库不记录binlog，从库也无法变更这部分数据。
- 从节点未设置只读，误操作写入数据
- 主库或从库意外宕机，宕机可能会造成binlog或者relaylog文件出现损坏，导致主从不一致主从实例版本不一致，特别是高版本是主，低版本为从的情况下，主数据库上面支持的功能，从数据库上面可能不支持该功能
- MySQL自身bug导致
2.主从不一致修复方法
- 将从库重新实现
虽然这也是一种解决方法，但是这个方案恢复时间比较慢，而且有时候从库也是承担一部分的查询操作的，不能贸然重建。 - 使用percona-toolkit工具辅助
PT工具包中包含pt-table-checksum和pt-table-sync两个工具，主要用于检测主从是否一致以及修复数据不一致情况。这种方案优点是修复速度快，不需要停止主从辅助，缺点是需要知识积累，需要时间去学习，去测试，特别是在生产环境，还是要小心使用
关于使用方法，可以参考下面链接：https://www.cnblogs.com/feiren/p/7777218.html - 手动重建不一致的表
在从库发现某几张表与主库数据不一致，而这几张表数据量也比较大，手工比对数据不现实，并且重做整个库也比较慢，这个时候可以只重做这几张表来修复主从不一致这种方案缺点是在执行导入期间需要暂时停止从库复制，不过也是可以接受的
3.如何避免主从不一致
- 主库binlog采用ROW格式
- 主从实例数据库版本保持一致
- 主库做好账号权限把控，不可以执行set sql_log_bin=0
- 从库开启只读，不允许人为写入
- 定期进行主从一致性检验
