23-MYSQL数据库(一)
2020-03-25 本文已影响0人
Liang_JC
本章内容
◆ 关系型数据库基础
◆ 安装MySQL
◆ 管理数据库和表
◆ 用户和权限管理
◆ 函数，存储过程和触发器
◆ MySQL架构
◆ 存储引擎
◆ 服务器选项，系统和状态变量
◆ 优化查询和索引管理
◆ 锁和事务管理
◆ 日志管理
◆ 备份还原
◆ MySQL集群
数据的时代
● 涉及的数据量大
● 数据不随程序的结束而消失
● 数据被多个应用程序共享
● 大数据
数据库的发展史
萌芽阶段：文件系统
使用磁盘文件来存储数据
初级阶段：第一代数据库
出现了网状模型、层次模型的数据库
中级阶段：第二代数据库
关系型数据库和结构化查询语言
高级阶段：新一代数据库
“关系-对象”型数据库
数据库管理系统
● 数据库是数据的汇集，它以一定的组织形式存于存储介质上
● DBMS是管理数据库的系统软件，它实现数据库系统的各种功能。是数据库系统的核心
● DBA：负责数据库的规划、设计、协调、维护和管理等工作
● 应用程序指以数据库为基础的应用程序
数据库管理系统的优点
● 相互关联的数据的集合
● 较少的数据冗余
● 程序与数据相互独立
● 保证数据的安全、可靠
● 最大限度地保证数据的正确性
● 数据可以并发使用并能同时保证一致性
文件管理系统的缺点
● 编写应用程序不方便
● 数据冗余不可避免
● 应用程序依赖性
● 不支持对文件的并发访问
● 数据间联系弱
● 难以按用户视图表示数据
● 无安全控制功能
数据库管理系统的基本功能
● 数据定义
● 数据处理
● 数据安全
● 数据备份
数据库系统的架构
● 单机架构
● 大型主机/终端架构
● 主从式架构（C/S）
● 分布式架构
网状数据库
最早出现的是网状DBMS，1964年通用电气公司的Charles Bachman成功地开发出世界上第一个网状IDS，也是第一个数据库管理系统，IDS 具有数据模式和日志的特征，只能在GE主机运行
image.png
层次数据库
image.png
关系型数据库
关系 ：关系就是二维表，其中：表中的行、列次序并不重要
行row：表中的每一行，又称为一条记录
列column：表中的每一列，称为属性，字段
主键Primary key：用于惟一确定一个记录的字段
域domain：属性的取值范围，如，性别只能是‘男’和‘女’两个值
关系数据库
RDBMS：
MySQL: MySQL, MariaDB, Percona Server
PostgreSQL: 简称为pgsql，EnterpriseDB
Oracle
MSSQL
DB2
数据库排名：
https://db-engines.com/en/ranking
数据库排名
image.png
联系类型
数据的操作：
数据提取：在数据集合中提取感兴趣的内容。 SELECT
数据更新：变更数据库中的数据。 INSERT、 DELETE、 UPDATE
联系的类型
一对一联系(1:1)
一对多联系(1:n)
多对多联系(m:n)
简易数据规划流程
第一阶段：收集数据，得到字段
收集必要且完整的数据项
转换成数据表的字段
第二阶段：把字段分类，归入表，建立表的关联
关联：表和表间的关系
分割数据表并建立关联的优点
节省空间
减少输入错误
方便数据修改
第三阶段：
规范化数据库
数据库的正规化分析
数据库规范化，又称数据库或资料库的正规化、标准化，是数据库设计中的一
系列原理和技术，以减少数据库中数据冗余，增进数据的一致性。关系模型的
发明者埃德加·科德最早提出这一概念，并于1970年代初定义了第一范式、第二
范式和第三范式的概念
RDMBS设计范式基础概念
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，不同
的规范要求被称为不同范式，各种范式呈递次规范，越高的范式数据库冗余越小
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式
（3NF）、巴德斯科范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称
完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上
进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般
数据库只需满足第三范式(3NF）即可
范式
1NF：无重复的列，每一列都是不可分割的基本数据项，同一列中不能有多个
值，即实体中的某个属性不能有多个值或者不能有重复的属性，确保每一列的
原子性。除去同类型的字段，就是无重复的列
说明：第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）
的数据库就不是关系数据库
2NF：属性完全依赖于主键，第二范式必须先满足第一范式，要求表中的每个
行必须可以被唯一地区分，通常为表加上每行的唯一标识PK，非PK的字段需要
与整个PK有直接相关性
3NF：属性不依赖于其它非主属性，满足第三范式必须先满足第二范式。第三
范式要求一个数据表中不包含已在其它表中已包含的非主关键字信息，非PK的
字段间不能有从属关系
SQL概念
SQL: Structure Query Language
结构化查询语言
SQL解释器：
数据存储协议：应用层协议，C/S
S：server, 监听于套接字，接收并处理客户端的应用请求
C：Client
客户端程序接口
CLI
GUI
应用编程接口
ODBC：Open Database Connectivity
JDBC：Java Data Base Connectivity
基本概念
约束：constraint，表中的数据要遵守的限制
主键：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行；
必须提供数据，即NOT NULL，一个表只能有一个
惟一键：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行；
允许为NULL，一个表可以存在多个
外键：一个表中的某字段可填入的数据取决于另一个表的主键或唯一键已有的数据
检查：字段值在一定范围内
索引：将表中的一个或多个字段中的数据复制一份另存，并且按特定次序排序存储
关系运算：
选择：挑选出符合条件的行
投影：挑选出需要的字段
连接：表间字段的关联
数据模型
数据抽象：
物理层：数据存储格式，即RDBMS在磁盘上如何组织文件
逻辑层：DBA角度，描述存储什么数据，以及数据间存在什么样的关系
视图层：用户角度，描述DB中的部分数据
关系模型的分类：
关系模型
基于对象的关系模型
半结构化的关系模型：XML数据
MySQL历史
1979年：TcX公司 Monty Widenius，Unireg
1996年：发布MySQL1.0，Solaris版本，Linux版本
1999年：MySQL AB公司，瑞典
2003年：MySQL 5.0版本，提供视图、存储过程等功能
2008年：Sun 收购
2009年：Oracle收购sun
2009年：Monty成立MariaDB
MySQL系列
官方网址：
https://www.mysql.com/
http://mariadb.org/
https://www.percona.com
官方文档
https://dev.mysql.com/doc/
https://mariadb.com/kb/en/
https://www.percona.com/software/mysql-database/percona-server
版本演变：
MySQL：5.1 --> 5.5 --> 5.6 --> 5.7 -->8.0
MariaDB：5.5 -->10.0--> 10.1 --> 10.2 --> 10.3
MYSQL的特性
插件式存储引擎：也称为“表类型”，存储管理器有多种实现版本，功能和特
性可能均略有差别；用户可根据需要灵活选择,Mysql5.5.5开始innoDB引擎是
MYSQL默认引擎
MyISAM ==> Aria
InnoDB ==> XtraDB
单进程，多线程
诸多扩展和新特性
提供了较多测试组件
开源
安装MYSQL
Mariadb安装方式：
1、源代码：编译安装
2、二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
3、程序包管理器管理的程序包
CentOS 安装光盘
项目官方：https://downloads.mariadb.org/mariadb/repositories/
国内镜像：https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/
https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/
yum install mariadb-server #centos7
yum install mysql-server #centos6
vim /etc/repos.d/mariadb.repo #10.2版本mariadb
[mariadb]
name = MariaDB
baseurl = https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/10.2/centos7-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
:wq
RPM包安装MySQL
RPM包安装
CentOS 7：安装光盘直接提供
mariadb-server 服务器包
mariadb 客户端工具包
CentOS 6
提高安全性
mysql_secure_installation
设置数据库管理员root口令
禁止root远程登录
删除anonymous用户帐号
删除test数据库
/var/lib/mysql #存放数据库目录
使用
mysql #直接可以连接(默认用户root，密码空)
mysql -uroot -p #密码方式登录
#命令
status
show databases; #显示所有数据库
use mysql #使用mysql数据库
show tables; #显示所有表
select * from user; #查询user表
desc user; #查看表结构
select user,host,password form user; #显示user表user,host,password列
MariaDB程序
客户端程序：
mysql: 交互式的CLI工具
mysqldump：备份工具，基于mysql协议向mysqld发起查询请求，并将查得的所
有数据转换成insert等写操作语句保存文本文件中
mysqladmin：基于mysql协议管理mysqld
mysqlimport：数据导入工具
MyISAM存储引擎的管理工具：
myisamchk：检查MyISAM库
myisampack：打包MyISAM表，只读
服务器端程序
mysqld_safe
mysqld
mysqld_multi 多实例 ，示例：mysqld_multi --example
用户账号
mysql用户账号由两部分组成：
'USERNAME'@'HOST‘
说明：
HOST限制此用户可通过哪些远程主机连接mysql服务器
支持使用通配符：
% 匹配任意长度的任意字符
172.16.0.0/255.255.0.0 或 172.16.%.%
_ 匹配任意单个字符
Mysql客户端
mysql客户端常用选项：
-A, --no-auto-rehash 禁止补全
-u, --user= 用户名,默认为root
-h, --host= 服务器主机,默认为localhost
-p, --passowrd= 用户密码,建议使用-p,默认为空密码
-P, --port= 服务器端口
-S, --socket= 指定连接socket文件路径
-D, --database= 指定默认数据库
-C, --compress 启用压缩
-e “SQL“ 执行SQL命令
-V, --version 显示版本
-v --verbose 显示详细信息
--print-defaults 获取程序默认使用的配置
socket地址
服务器监听的两种socket地址：
ip socket: 监听在tcp的3306端口，支持远程通信
unix sock: 监听在sock文件上，仅支持本机通信
如：/var/lib/mysql/mysql.sock)
说明：host为localhost,127.0.0.1时自动使用unix sock
执行命令
运行mysql命令：默认空密码登录
mysql>use mysql
mysql>select user();查看当前用户
mysql>SELECT User,Host,Password FROM user;
登录系统：mysql –uroot –p
客户端命令：本地执行
mysql> help
每个命令都完整形式和简写格式
mysql> status 或 \s
服务端命令：通过mysql协议发往服务器执行并取回结果
每个命令末尾都必须使用命令结束符号，默认为分号
示例：SELECT VERSION();
#其他命令
system hostname #执行系统命令
source test.sql #执行sql脚本
mysql --prompt="(\u@\h) [\d]> " #临时修改mysql提示符
#永久修改mysql提示符
vim /etc/my.cnf.d/mysql-clients.cnf
[mysql]
prompt="(\\u@\\h) [\\d]>\\ "
:wq
/etc/my.cnf #mysql主配置文件
安全加固
mysql_secure_installation
mysqladmin
mysqladmin –help
查看mysql服务是否正常，如果正常提示mysqld is alive
mysqladmin -uroot -pcentos ping
关闭mysql服务，但mysqladmin命令无法开启
mysqladmin –uroot –pcentos shutdown
创建数据库testdb
mysqladmin -uroot –pcentos create testdb
删除数据库testdb
mysqladmin -uroot -pcentos drop testdb
修改root密码
mysqladmin –uroot –pcentos password ‘magedu’
日志滚动,生成新文件/var/lib/mysql/ mariadb-bin.00000N
mysqladmin -uroot -pcentos flush-logs
#使用
mysqladmin ping #检测服务是否在运行
mysqladmin shutdown #关闭服务
mysqladmin -uroot password ceontos #加口令
mysqladmin -uroot -pcentos password magedu #修改口令
服务器端配置
服务器端(mysqld)：工作特性有多种配置方式
1、命令行选项：
2、配置文件：类ini格式
集中式的配置，能够为mysql的各应用程序提供配置信息
[mysqld]
[mysqld_safe]
[mysqld_multi]
[mysql]
[mysqldump]
[server]
[client]
格式：parameter = value
说明：_和- 相同
1，ON，TRUE意义相同， 0，OFF，FALSE意义相同
配置文件
后面覆盖前面的配置文件,顺序如下：
/etc/my.cnf Global选项
/etc/mysql/my.cnf Global选项
SYSCONFDIR/my.cnf Global选项
$MYSQL_HOME/my.cnf Server-specific 选项
--defaults-extra-file=path
~/.my.cnf User-specific 选项
MairaDB配置
侦听3306/tcp端口可以在绑定有一个或全部接口IP上
vim /etc/my.cnf
[mysqld]
skip-networking=1
关闭网络连接，只侦听本地客户端， 所有和服务器的交互都通过一个socket实现，socket的配置存放在/var/lib/mysql/mysql.sock） 可在/etc/my.cnf修改
通用二进制格式安装过程
二进制格式安装过程
(1) 准备用户
groupadd -r -g 306 mysql
useradd -r -g 306 -u 306 –d /data/mysql mysql
(2) 准备数据目录，建议使用逻辑卷
mkdir /data/mysql
chown mysql:mysql /data/mysql
(3) 准备二进制程序
tar xf mariadb-VERSION-linux-x86_64.tar.gz -C /usr/local
cd /usr/local
ln -sv mariadb-VERSION mysql
chown -R root:mysql /usr/local/mysql/
(4) 准备配置文件
mkdir /etc/mysql/
cp support-files/my-large.cnf /etc/mysql/my.cnf
[mysqld]中添加三个选项：
datadir = /data/mysql
innodb_file_per_table = on
skip_name_resolve = on 禁止主机名解析，建议使用
(5)创建数据库文件
cd /usr/local/mysql/
./scripts/mysql_install_db --datadir=/data/mysql --user=mysql
(6)准备服务脚本，并启动服务
cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld
chkconfig --add mysqld
service mysqld start
(7)PATH路径
echo ‘PATH=/user/local/mysql/bin:$PATH’ > /etc/profile.d/mysql.sh
(8)安全初始化
/user/local/mysql/bin/mysql_secure_installation
小笔记：二进制安装
useradd -r -s /sbin/nologin -d /data/mysql mysql
tar -xf mariadb-10.2.25-linux-x86_64.tar.gz -C /usr/local
cd /usr/local
ln -s mariadb-10.2.25-linux-x86_64/ mysql
chown -R root:root mysql/
cd mysql
scripts/mysql_install_db --datadir=/data/mysql --user=mysql
cp support-files/my-huge.cnf /etc/mysql/my.cnf
vim /etc/mysql/my.cnf
[mysqld]
datadir=/data/mysql #添加数据库路径
:wq
cp support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on
service mysqld start
echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh
mysql_secure_installation #安全加固
源码编译安装mariadb
安装包
yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost
devel gcc gcc-c++ cmake ncurses-devel gnutls-devel libxml2-devel openssl
devel libevent-devel libaio-devel
做准备用户和数据目录
useradd –r –s /sbin/nologin –d /data/mysql/ mysql
mkdir /data/mysql
chown mysql.mysql /data/mysql
tar xvf mariadb-10.2.18.tar.gz
cmake 编译安装
cmake的重要特性之一是其独立于源码(out-of-source)的编译功能，即编译工作可以在
另一个指定的目录中而非源码目录中进行，这可以保证源码目录不受任何一次编译的影
响，因此在同一个源码树上可以进行多次不同的编译，如针对于不同平台编译
编译选项:https://dev.mysql.com/doc/refman/5.7/en/source-configuration-options.html
cd mariadb-10.2.18/
cmake . \
-DCMAKE_INSTALL_PREFIX=/app/mysql \
-DMYSQL_DATADIR=/data/mysql/ \
-DSYSCONFDIR=/etc/ \
-DMYSQL_USER=mysql \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \
-DWITH_DEBUG=0 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci
make && make install
提示：如果出错，执行rm -f CMakeCache.txt
编译后一些准备工作
准备环境变量
echo 'PATH=/app/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh
生成数据库文件
cd /app/mysql/
scripts/mysql_install_db --datadir=/data/mysql/ --user=mysql
准备配置文件
cp /app/mysql/support-files/my-huge.cnf /etc/my.cnf
准备启动脚本
cp /app/mysql/support-files/mysql.server /etc/init.d/mysqld
启动服务
chkconfig --add mysqld ;service mysqld start
小笔记：编译mariadb
#创建一个逻辑卷
echo -e 'n\n\n\nt\n\n8e\nw\n' | fdisk /dev/sda #剩余空间创建分区
partprobe
pvcreate /dev/sda6
vgcreate vg0 /dev/sda6 -s 16M
lvcreate -n mysql -l +100%free vg0
mkdir /data/mysql
mkfs.xfs /dev/vg0/mysql
vim /etc/fstab
/dev/vg0/mysql /data/mysql xfs defaults 0 0
:wq
mount -a
#创建用户
useradd -r -s /sbin/nologin -d /data/mysql mysql
chown mysql:mysql /data/mysql/
#编译安装
yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel gcc gcc-c++ \
cmake ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel \
libdb-cxx-devel -y #文档缺少了libdb-cxx-devel
tar -xf mariadb-10.2.25.tar.gz
cd mariadb-10.2.25
cmake . \
-DCMAKE_INSTALL_PREFIX=/app/mysql \
-DMYSQL_DATADIR=/data/mysql/ \
-DSYSCONFDIR=/etc/ \
-DMYSQL_USER=mysql \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \
-DWITH_DEBUG=0 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci
make -j 4 && make install
小笔记：多实例
#安装mariadb,以它为模板
yum install mariadb-server -y
#创建多实例目录
mkdir -pv /mysql/{3306,3307,3308}/{bin,data,etc,log,pid,socket}
chown -R myslq:mysql /mysql
mysql_install_db --datadir=/mysql/3306/data --user=mysql
mysql_install_db --datadir=/mysql/3307/data --user=mysql
mysql_install_db --datadir=/mysql/3308/data --user=mysql
cp /etc/my.cnf /mysql/3306/etc
cd /mysql
vim 3306/etc/my.cnf
[mysqld]
port=3306
datadir=/mysql/3306/data/
socket=/mysql/3306/socket/mysql.sock
[mysqld_safe]
log-error=/mysql/3306/log/mariadb.log
pid-file=/mysql/3306/pid/mariadb.pid
:wq
chown mysql.mysql 3306/etc/my.cnf
cp -p 3306/etc/my.cnf {3307,3308}/etc
sed -i 's/3306/3307/' 3307/etc/my.cnf
sed -i 's/3306/3308/' 3308/etc/my.cnf
#服务配置文件
cat > 3306/bin/mysqld <<EOF
#!/bin/bash
port=3306
mysql_user="root"
mysql_pwd="centos"
cmd_path="/usr/bin"
mysql_basedir="/mysql"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf &> /dev/null &
else
printf "MySQL is running...\n"
exit
fi
}
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit
else
printf "Stoping MySQL...\n"
${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
fi
}
function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
sleep 2
function_start_mysql
}
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac
EOF
chown mysql.mysql 3306/bin/mysqld
chmod +x 3306/bin/mysqld
cp -p 3306/bin/mysqld {3307,3308}/bin/mysqld
sed -i 's/3306/3307/' 3307/bin/mysqld
sed -i 's/3306/3308/' 3308/bin/mysqld
#启动服务
/mysql/3306/bin/mysqld start
/mysql/3307/bin/mysqld start
/mysql/3308/bin/mysqld start
#客户端连接
mysql -S /mysql/3306/socket/mysql.sock
#修改密码
mysqladmin -S /mysql/3306/socket/mysql.sock password centos #密码设置为centos
#设为开机启动
vim /etc/profile.d/mysql.sh
/mysql/3306/bin/mysqld start
/mysql/3307/bin/mysqld start
/mysql/3308/bin/mysqld start
:wq
关系型数据库的常见组件
数据库：database
表：table
行：row
列：column
索引：index
视图：view
用户：user
权限：privilege
存储过程：procedure
存储函数：function
触发器：trigger
事件调度器：event scheduler，任务计划
SQL语言的兴起与语法标准
20世纪70年代，IBM开发出SQL，用于DB2
1981年，IBM推出SQL/DS数据库
业内标准微软和Sybase的T-SQL，Oracle的PL/SQL
SQL作为关系型数据库所使用的标准语言，最初是基于IBM的实现在1986年被批准的。 1987年，“国际标准化组织(ISO)” 把ANSI(美国国家标准化组织)SQL作为国际标准。
SQL：ANSI SQL
SQL-1986, SQL-1989, SQL-1992, SQL-1999, SQL-2003
SQL-2008, SQL-2011
SQL语言规范
在数据库系统中，SQL语句不区分大小写(建议用大写)
SQL语句可单行或多行书写，以“;” 结尾
关键词不能跨多行或简写
用空格和缩进来提高语句的可读性
子句通常位于独立行，便于编辑，提高可读性
注释：
SQL标准：
/*注释内容*/ 多行注释
-- 注释内容 单行注释，注意有空格
MySQL注释：
\#
数据库对象
数据库的组件(对象)：
数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等
命名规则：
必须以字母开头
可包括数字和三个特殊字符（# _ $）
不要使用MySQL的保留字
同一database(Schema)下的对象不能同名
SQL语句分类
SQL语句分类：
DDL: Data Defination Language 数据定义语言
CREATE，DROP，ALTER
DML: Data Manipulation Language 数据操纵语言
INSERT，DELETE，UPDATE
DCL：Data Control Language 数据控制语言
GRANT，REVOKE，COMMIT，ROLLBACK
DQL：Data Query Language 数据查询语言
SELECT
SQL语句构成
SQL语句构成：
Keyword组成clause
多条clause组成语句
示例：
SELECT * SELECT子句
FROM products FROM子句
WHERE price>400 WHERE子句
说明：一组SQL语句，由三个子句构成，SELECT,FROM和WHERE是关键字
数据库操作
创建数据库：
CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME';
CHARACTER SET 'character set name’COLLATE 'collate name'
修改数据库：
ALTER DATABASE DB_NAME character set utf8;
删除数据库
DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';
查看支持所有字符集：SHOW CHARACTER SET;
查看支持所有排序规则：SHOW COLLATION;
获取命令使用帮助：
mysql> HELP KEYWORD;
查看数据库列表：
mysql> SHOW DATABASES;
小笔记：create database
mysql -uroot -p
create database test2;
create datebase test3 character set utf8mb4;
cat /var/lib/mysql/test3/db.opt #查看数据库所使用的字符集
show character; #查看字符集
show collation; #查看排序规则
表
表：二维关系
设计表：遵循规范
定义：字段，索引
字段：字段名，字段数据类型，修饰符
约束，索引：应该创建在经常用作查询条件的字段上
创建表
创建表：CREATE TABLE
(1) 直接创建
(2) 通过查询现存表创建；新表会被直接插入查询而来的数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)] [table_options]
[partition_options] select_statement
(3) 通过复制现存的表的表结构创建，但不复制数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE
old_tbl_name | (LIKE old_tbl_name) }
注意：
Storage Engine是指表类型，也即在表创建时指明其使用的存储引擎，同一库中不同表可以使用不同的存储引擎
同一个库中表建议要使用同一种存储引擎类型
创建表
CREATE TABLE [IF NOT EXISTS] ‘tbl_name’ (col1 type1 修饰符, col2 type2 修饰符, ...)
字段信息
col type1
PRIMARY KEY(col1,...)
INDEX(col1, ...)
UNIQUE KEY(col1, ...)
表选项：
ENGINE [=] engine_name
SHOW ENGINES;查看支持的engine类型
ROW_FORMAT [=]
{DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
获取帮助：mysql> HELP CREATE TABLE;
表操作
查看所有的引擎：SHOW ENGINES
查看表：SHOW TABLES [FROM db_name]
查看表结构：DESC [db_name.]tb_name
SHOW COLUMNS FROM [db_name.]tb_name
删除表：DROP TABLE [IF EXISTS] tb_name
查看表创建命令：SHOW CREATE TABLE tbl_name
查看表状态：SHOW TABLE STATUS LIKE 'tbl_name’
查看库中所有表状态：SHOW TABLE STATUS FROM db_name
数据类型
数据类型：
数据长什么样
数据需要多少空间来存放
系统内置数据类型和用户定义数据类型
MySql支持多种列类型：
数值类型
日期/时间类型
字符串(字符)类型
https://dev.mysql.com/doc/refman/5.5/en/data-types.html
选择正确的数据类型对于获得高性能至关重要，三大原则：
更小的通常更好，尽量使用可正确存储数据的最小数据类型
简单就好，简单数据类型的操作通常需要更少的CPU周期
尽量避免NULL，包含为NULL的列，对MySQL更难优化
image.png
1、整型
tinyint(m) 1个字节 范围(-128~127)
smallint(m) 2个字节 范围(-32768~32767)
mediumint(m) 3个字节 范围(-8388608~8388607)
int(m) 4个字节 范围(-2147483648~2147483647)
bigint(m) 8个字节 范围(+-9.22*10的18次方)
加了unsigned，则最大值翻倍，如：tinyint unsigned的取值范围为(0~255)int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，Int(1)和Int(20)是相同的
BOOL，BOOLEAN：布尔型，是TINYINT(1)的同义词。 zero值被视为假，非zero值视为真
2、浮点型(float和double)，近似值
float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位
设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位
3、定点数
在数据库中存放的是精确值,存为十进制
decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位
MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。例如，decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节
浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。 float使用4个字节存储。 double占用8个字节
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal，例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal
4、字符串(char,varchar,_text)
char(n) 固定长度，最多255个字符
varchar(n) 可变长度，最多65535个字符
tinytext 可变长度，最多255个字符
text 可变长度，最多65535个字符
mediumtext 可变长度，最多2的24次方-1个字符
longtext 可变长度，最多2的32次方-1个字符
BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节
VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节
内建类型：ENUM枚举, SET集合
char和varchar：
1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，所以char类型存储的字符串末尾不能有空格，varchar不限于此
2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n< n>255)，所以varchar(4),存入3个字符将占用4个字节
3.char类型的字符串检索速度要比varchar类型的快
varchar和text：
1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n< n>255)，text是实际字符数+2个字节。
2.text类型不能有默认值
3.varchar可直接创建索引，text创建索引要指定前多少个字符。 varchar查询速度快于text
5.二进制数据：BLOB
BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，
而Blob是以二进制方式存储，不分大小写
BLOB存储的数据只能整体读出
TEXT可以指定字符集，BLOB不用指定字符集
6.日期时间类型
date 日期 '2008-12-2'
time 时间 '12:25:36'
datetime 日期时间 '2008-12-2 22:06:44'
timestamp 自动存储记录修改时间
YEAR(2), YEAR(4)：年份
timestamp字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的字段可以存放这条记录最后被修改的时间
修饰符
所有类型：
NULL 数据列可包含NULL值
NOT NULL 数据列不允许包含NULL值
DEFAULT 默认值
PRIMARY KEY 主键
UNIQUE KEY 唯一键
CHARACTER SET name 指定一个字符集
数值型
AUTO_INCREMENT 自动递增，适用于整数类型
UNSIGNED 无符号
小笔记：Create table
use test2
-- unsigned-无符号 auto_increment-自动递增
create table student(id int unsigned auto_increment primary key,name varchar(20) not null,gender ENUM('m','f') default 'm', mobile char(11));
desc student -- 查询表结构
create table student2 like test2.student -- 创建一样结构的表，继承字符集
create table student3 select * from test2.student -- 创建一样结构和内容的表，但字符集不继承
示例
CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY,name VARCHAR（20）NOT NULL,age tinyint NSIGNED);
DESC students;
CREATE TABLE students2 (id int UNSIGNED NOT NULL ,name VARCHAR(20) NOT NULL,age tinyint NSIGNED,PRIMARY KEY(id,name));
表操作
DROP TABLE [IF EXISTS] 'tbl_name';
ALTER TABLE 'tbl_name'
字段：
添加字段：add
ADD col1 data_type [FIRST|AFTER col_name]
删除字段：drop
修改字段：
alter（默认值）, change（字段名）, modify（字段属性）
索引:
添加索引：add index
删除索引：drop index
表选项
修改:
查看表上的索引：SHOW INDEXES FROM [db_name.]tbl_name;
查看帮助：Help ALTER TABLE
修改表示例
ALTER TABLE students RENAME s1;
ALTER TABLE s1 ADD phone varchar(11) AFTER name;
ALTER TABLE s1 MODIFY phone int;
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
ALTER TABLE s1 DROP COLUMN mobile;
ALTER TABLE s1 character set utf8;
ALTER TABLE s1 change name name varchar(20) character set utf8;
Help ALTER TABLE 查看帮助
ALTER TABLE students ADD gender ENUM('m','f');
ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;
ALTER TABLE students drop primary key ;
ALTER TABLE students ADD UNIQUE KEY(name);
ALTER TABLE students ADD INDEX(age);
ALTER TABLE students drop primary key ;
DESC students;
SHOW INDEXES FROM students;
ALTER TABLE students DROP age;
DML语句
DML: INSERT, DELETE, UPDATE
INSERT：
一次插入一行或多行数据
语法
INSERT [L OW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE 如果重复更新之
col_name=expr
[, col_name=expr] ... ]
简化写法：
INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
UPDATE：
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT}
[, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
注意：一定要有限制条件，否则将修改所有行的指定字段
限制条件：
WHERE
LIMIT
mysql 选项：-U|--safe-updates| --i-am-a-dummy
DELETE:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_condition] [ORDER BY ...]
[LIMIT row_count]
可先排序再指定删除的行数
注意：一定要有限制条件，否则将清空表中的所有数据
限制条件：
WHERE
LIMIT
TRUNCATE TABLE tbl_name; 清空表
小笔记：insert使用
insert student(name,mobile)values('tom','13800138000');
insert student(name,mobile)values('xiaoming','10086'),('xiaohong','10010');
insert student(name,mobile,gender)values('alice','10000','f');
alter table student change name name varchar(20), character set utf8mb4; -- 字段改字符集
show full columns from student -- 查看字段字符集
show table status --查看表字段
小笔记：mysql客户端设置字符集
mysql --default-character-set=utf8mb4 -uroot -p #临时生效
mysql> status
小笔记：设置默认字符集(永久生效)
vim /etc/my.cnf #服务端
[mysqld]
character_set_server=utf8mb4
:wq
vim /etc/my.cnf.d/mysql-clients.cnf #客户端
[mysql]
default-character-set=utf8mb4
:wq
systemctl restart mariadb
范例数据库导入
mysql -uroot -p < hellodb_innodb.sql
use hellodb
desc hellodb
insert students set name="mage",age=30,gender="M"; #另一种插入方式
insert students(name,age) select name,age from teachers; #搜索结果插入表
小笔记：update
update students set ClassID=1 where StuID=25;
小笔记：delete
delete from students where stuid>20;
delete from students; -- 清空表(记录日志)
truncate table students; -- 清空表(不记录日志，速度快)
DQL语句
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[SQL_CACHE | SQL_NO_CACHE]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[FOR UPDATE | LOCK IN SHARE MODE]
SELECT
字段显示可以使用别名：
col1 AS alias1, col2 AS alias2, ...
WHERE子句：指明过滤条件以实现“选择”的功能：
过滤条件：布尔型表达式
算术操作符：+, -, *, /, %
比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <=
BETWEEN min_num AND max_num
IN (element1, element2, ...)
IS NULL
IS NOT NULL
DISTINCT 去除重复列
SELECT DISTINCT gender FROM students;
LIKE:
% 任意长度的任意字符
_ 任意单个字符
RLIKE：正则表达式，索引失效，不建议使用
REGEXP：匹配字符串可用正则表达式书写模式，同上
逻辑操作符：
NOT
AND
OR
XOR
GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算
avg(), max(), min(), count(), sum()
平均，最大，最小，数量，合计
HAVING: 对分组聚合运算后的结果指定过滤条件
ORDER BY: 根据指定的字段对查询结果进行排序
升序：ASC
降序：DESC
LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制
对查询结果中的数据请求施加“锁”
FOR UPDATE: 写锁，独占或排它锁，只有一个读和写
LOCK IN SHARE MODE: 读锁，共享锁，同时多个读
示例
DESC students;
INSERT INTO students VALUES(1,'tom'，'m'),(2,'alice','f');
INSERT INTO students(id,name) VALUES(3,'jack'),(4,'allen');
SELECT * FROM students WHERE id < 3;
SELECT * FROM students WHERE gender='m';
SELECT * FROM students WHERE gender IS NULL;
SELECT * FROM students WHERE gender IS NOT NULL;
SELECT * FROM students ORDER BY name DESC LIMIT 2;
SELECT * FROM students ORDER BY name DESC LIMIT 1,2;
SELECT * FROM students WHERE id >=2 and id <=4
SELECT * FROM students WHERE BETWEEN 2 AND 4
SELECT * FROM students WHERE name LIKE ‘t%’
SELECT * FROM students WHERE name RLIKE '.*[lo].*';
SELECT id stuid,name as stuname FROM students;
单表查询
select "hello" #打印hello
use hellodb
select * from students;
select NAME,age,stuid from students;
select NAME as 姓名,age 年龄,stuid from students as 学生表; #字段以别名显示
select * from studnets where age>10 and gender='f';
-- 构建用户表
create table user (id int,username char(30).password char(30));
insert user values(1,'admin','magedu');
insert user values(2,'mage','magedu');
insert user values(3,'wang','centos');
-- 查询
select * from user where username='admin' and password='magedu';
select * from user where username='admin' and password='' or '1'='1'; -- sql注入攻击
select * from user where username='admin'--' and password='''; -- sql注入攻击
select * from user where age between 20 and 30;
select * from studnet where name like 's%'; -- 匹配s开头(不区分大小写)
select * from studnet where name like '%yu%'; -- 匹配包含yu的字符串(不推荐使用，影响性能)
select * from studnet where name rlike '^s'; -- rlike正则表达式
select distinct age from students; -- distinct去重
select * from students where classid is null; --空字段
select * from students where classid is not null; --非空字段
select count(stuid) from students; -- 字段记录数量(非空)
-- 系统函数
select gender,avg(age) as 记录数 from students group by gender;
+--------+-----------+
| gender | 记录数 |
+--------+-----------+
| F | 19.0000 |
| M | 33.0000 |
+--------+----------+
2 rows in set (0.00 sec)
-- 分组
select gender,avg(age) from students group by gender; -- group by必须配合函数使用，否则没意义
select gender,age,stuid from students group by gender; -- 不配合函数只对第一行处理
select classid,avg(age) from students group by classid; -- 对classid做平均年龄
select classid,avg(age) from students group by classid having classid > 3; --having分组加条件
select classid,avg(age) from students where classid > 3 group by classid;
select classid,avg(age) as 平均年龄 from students where classid > 3 group by classid having 平均年龄 > 30;
-- 多次分组
select classid,gender,avg(age) from students group by classid,gender;
+---------+--------+----------+
| classid | gender | avg(age) |
+---------+--------+----------+
| NULL | M | 63.5000 |
| 1 | F | 19.5000 |
| 1 | M | 21.5000 |
| 2 | M | 36.0000 |
| 3 | F | 18.3333 |
| 3 | M | 26.0000 |
| 4 | M | 24.7500 |
| 5 | M | 46.0000 |
| 6 | F | 20.0000 |
| 6 | M | 23.0000 |
| 7 | F | 18.0000 |
| 7 | M | 23.0000 |
+---------+--------+----------+
12 rows in set (0.00 sec)
-- 排序
select * from students order by age desc; -- 倒序
select * from students order by -classid desc; --倒序，NULL在最后
-- 组合使用
select classid,sum(age) from students group by classid order by classid;
select classid,sum(age) from students where classid is not null group by classid order by classid; --高效率处理
select classid,sum(age) from students group by classid having classid is not null order by classid; --低效率处理
-- limit
select classid,sum(age) from students where classid is not null group by classid order by classid limit 3; --显示前三
+---------+----------+
| classid | sum(age) |
+---------+----------+
| 1 | 82 |
| 2 | 108 |
| 3 | 81 |
+---------+----------+
3 rows in set (0.00 sec)
select classid,sum(age) from students where classid is not null group by classid order by classid limit 2,3; --跳过前2行，显示后续的3行(3-5)
+---------+----------+
| classid | sum(age) |
+---------+----------+
| 3 | 81 |
| 4 | 99 |
| 5 | 46 |
+---------+----------+
3 rows in set (0.00 sec)
select * from students where classid in (1,3,5); -- 条件为classid1、3、5
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
+-------+--------------+-----+--------+---------+-----------+
9 rows in set (0.00 sec)
SQL JOINS
image.png
多表查询
交叉连接：笛卡尔乘积(横向组合)
内连接(inner)：
等值连接：让表之间的字段以“等值”建立连接关系；
不等值连接
自然连接:去掉重复列的等值连接
自连接
外连接：
左外连接：
FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
右外连接
FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
子查询：在查询语句嵌套着查询语句，性能较差
基于某语句的查询结果再次进行的查询
用在WHERE子句中的子查询
用于比较表达式中的子查询；子查询仅能返回单个值
SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students);
用于IN中的子查询：子查询应该单键查询并返回一个或多个值从构成列表
SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
用于EXISTS
用于FROM子句中的子查询
使用格式：SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias
WHERE Clause;
示例：
SELECT s.aage,s.ClassID FROM (SELECT avg(Age) AS aage,ClassID
FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID) AS s
WHERE s.aage>30;
联合查询：UNION
SELECT Name,Age FROM students UNION SELECT Name,Age FROM
teachers;
-- 多表查询
select * from user union select * from user; -- 去重
select * from students cross join teachers; --组合在一起，没有实际意义
-- 内连接
select * from students inner join teachers on students.teacherid=teachers.tid;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.00 sec)
select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s inner join teachers as t on s.teacherid=t.tid; --新写法
+-------+-------------+-----+-----+---------------+-----+
| stuid | name | age | tid | name | age |
+-------+-------------+-----+-----+---------------+-----+
| 5 | Yu Yutong | 26 | 1 | Song Jiang | 45 |
| 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai | 77 |
| 4 | Ding Dian | 32 | 4 | Lin Chaoying | 93 |
+-------+-------------+-----+-----+---------------+-----+
3 rows in set (0.00 sec)
select s.stuid,s.name,s.age,t.tid,t.name,t.age from students s,teachers t where s.teacherid=t.tid; --旧写法
+-------+-------------+-----+-----+---------------+-----+
| stuid | name | age | tid | name | age |
+-------+-------------+-----+-----+---------------+-----+
| 5 | Yu Yutong | 26 | 1 | Song Jiang | 45 |
| 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai | 77 |
| 4 | Ding Dian | 32 | 4 | Lin Chaoying | 93 |
+-------+-------------+-----+-----+---------------+-----+
3 rows in set (0.00 sec)
-- 外连接
select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s left outer join teachers as t on s.teacherid=t.tid; -- 左连接
+-------+---------------+-----+------+---------------+------+
| stuid | name | age | tid | name | age |
+-------+---------------+-----+------+---------------+------+
| 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai | 77 |
| 2 | Shi Potian | 22 | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | NULL | NULL | NULL |
| 4 | Ding Dian | 32 | 4 | Lin Chaoying | 93 |
| 5 | Yu Yutong | 26 | 1 | Song Jiang | 45 |
| 6 | Shi Qing | 46 | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | NULL | NULL | NULL |
+-------+---------------+-----+------+---------------+------+
25 rows in set (0.01 sec)
select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s left outer join teachers as t on s.teacherid=t.tid where t.tid is null; -- where最后处理
+-------+---------------+-----+------+------+------+
| stuid | name | age | tid | name | age |
+-------+---------------+-----+------+------+------+
| 2 | Shi Potian | 22 | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | NULL | NULL | NULL |
| 6 | Shi Qing | 46 | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | NULL | NULL | NULL |
+-------+---------------+-----+------+------+------+
22 rows in set (0.00 sec)
select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s right outer join teachers as t on s.teacherid=t.tid; -- 右连接
+-------+-------------+------+-----+---------------+-----+
| stuid | name | age | tid | name | age |
+-------+-------------+------+-----+---------------+-----+
| 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai | 77 |
| 4 | Ding Dian | 32 | 4 | Lin Chaoying | 93 |
| 5 | Yu Yutong | 26 | 1 | Song Jiang | 45 |
| NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 |
+-------+-------------+------+-----+---------------+-----+
4 rows in set (0.00 sec)
select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s right outer join teachers as t on s.teacherid=t.tid and s.age > 30; -- and左右先处理
+-------+-----------+------+-----+---------------+-----+
| stuid | name | age | tid | name | age |
+-------+-----------+------+-----+---------------+-----+
| 4 | Ding Dian | 32 | 4 | Lin Chaoying | 93 |
| NULL | NULL | NULL | 1 | Song Jiang | 45 |
| NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 |
| NULL | NULL | NULL | 3 | Miejue Shitai | 77 |
+-------+-----------+------+-----+---------------+-----+
4 rows in set (0.00 sec)
-- 左右+交集都要
select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s left outer join teachers as t on s.teacherid=t.tid union select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s right outer join teachers as t on s.teacherid=t.tid;
+-------+---------------+------+------+---------------+------+
| stuid | name | age | tid | name | age |
+-------+---------------+------+------+---------------+------+
| 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai | 77 |
| 2 | Shi Potian | 22 | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | NULL | NULL | NULL |
| 4 | Ding Dian | 32 | 4 | Lin Chaoying | 93 |
| 5 | Yu Yutong | 26 | 1 | Song Jiang | 45 |
| 6 | Shi Qing | 46 | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | NULL | NULL | NULL |
| NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 |
+-------+---------------+------+------+---------------+------+
26 rows in set (0.00 sec)
-- 左右减交集
select * from (select s.stuid,s.name s_name,s.teacherid,t.tid,t.name t_name from students s left outer join teachers t on s.teacherid=t.tid union select s.stuid,s.name,s.teacherid,t.tid,t.name from students s right outer join teachers t on s.teacherid=t.tid) as a where a.teacherid is null or a.tid is null;
+-------+---------------+-----------+------+---------------+
| stuid | s_name | teacherid | tid | t_name |
+-------+---------------+-----------+------+---------------+
| 2 | Shi Potian | 7 | NULL | NULL |
| 3 | Xie Yanke | 16 | NULL | NULL |
| 6 | Shi Qing | NULL | NULL | NULL |
| 7 | Xi Ren | NULL | NULL | NULL |
| 8 | Lin Daiyu | NULL | NULL | NULL |
| 9 | Ren Yingying | NULL | NULL | NULL |
| 10 | Yue Lingshan | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | NULL | NULL | NULL |
| 12 | Wen Qingqing | NULL | NULL | NULL |
| 13 | Tian Boguang | NULL | NULL | NULL |
| 14 | Lu Wushuang | NULL | NULL | NULL |
| 15 | Duan Yu | NULL | NULL | NULL |
| 16 | Xu Zhu | NULL | NULL | NULL |
| 17 | Lin Chong | NULL | NULL | NULL |
| 18 | Hua Rong | NULL | NULL | NULL |
| 19 | Xue Baochai | NULL | NULL | NULL |
| 20 | Diao Chan | NULL | NULL | NULL |
| 21 | Huang Yueying | NULL | NULL | NULL |
| 22 | Xiao Qiao | NULL | NULL | NULL |
| 23 | Ma Chao | NULL | NULL | NULL |
| 24 | Xu Xian | NULL | NULL | NULL |
| 25 | Sun Dasheng | NULL | NULL | NULL |
| NULL | NULL | NULL | 2 | Zhang Sanfeng |
+-------+---------------+-----------+------+---------------+
23 rows in set (0.00 sec)
-- 子查询
在子查询中，如果子查询得出多个值，那么要在括号外写上any(some),all,in。
> any 大于最小值
> all 大于最大值
< any 小于最大值
< all 小于最小值
select * from students where age < (select avg(age) from students);
select stuid,name,age from students where age > (select avg(age) from students);
update students set age=(select avg(age) from teachers) where stuid=25; ```
MariaDB [hellodb]> 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)
-- 添加新表以做实验
create table emp (id int,name char(20),leaderid int);
insert emp value(1,'mage',null);
insert emp value(2,'zhangsir',1);
insert emp value(3,'wang',2);
insert emp value(4,'zhangsir',3);
-- 自连接
MariaDB [hellodb]> select * from emp;
+------+----------+----------+
| id | name | leaderid |
+------+----------+----------+
| 1 | mage | NULL |
| 2 | zhangsir | 1 |
| 3 | wang | 2 |
| 4 | zhang | 3 |
+------+----------+----------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> select e.name,l.name from emp as e left join emp as l on e.leaderid=l.id;
+----------+----------+
| name | name |
+----------+----------+
| zhangsir | mage |
| wang | zhangsir |
| zhang | wang |
| mage | NULL |
+----------+----------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> select e.name,l.name from emp as e inner join emp as l on e.leaderid=l.id;
+----------+----------+
| name | name |
+----------+----------+
| zhangsir | mage |
| wang | zhangsir |
| zhang | wang |
+----------+----------+
3 rows in set (0.00 sec)
-- 三张表连接示例
MariaDB [hellodb]> select st.name,co.course,sc.score from students as st inner join scores as sc on st.stuid=sc.stuid inner join courses as co on sc.courseid=co.CourseID;
+-------------+----------------+-------+
| 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 |
| Shi Qing | Hamo Gong | 96 |
| Xi Ren | Hamo Gong | 86 |
| Xi Ren | Dagou Bangfa | 83 |
| Lin Daiyu | Taiji Quan | 57 |
| Lin Daiyu | Jinshe Jianfa | 93 |
+-------------+----------------+-------+
15 rows in set (0.00 sec)
SELECT语句
image.png
练习
导入hellodb.sql生成数据库
(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
MariaDB [hellodb]> 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)
(2) 以ClassID为分组依据，显示每组的平均年龄
MariaDB [hellodb]> select classid,avg(age) from students where classid is not null group by classid;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| 1 | 20.5000 |
| 2 | 36.0000 |
| 3 | 20.2500 |
| 4 | 24.7500 |
| 5 | 46.0000 |
| 6 | 20.7500 |
| 7 | 19.6667 |
+---------+----------+
7 rows in set (0.00 sec)
(3) 显示第2题中平均年龄大于30的分组及平均年龄
MariaDB [hellodb]> select classid,avg(age) from students where classid is not null group by classid having avg(age)>30;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| 2 | 36.0000 |
| 5 | 46.0000 |
+---------+----------+
3 rows in set (0.00 sec)
MariaDB [hellodb]> select classid,avg(age) as 平均年龄 from students where classid is not null group by classid having 平均年龄 > 30;
+---------+--------------+
| classid | 平均年龄 |
+---------+--------------+
| 2 | 36.0000 |
| 5 | 46.0000 |
+---------+--------------+
3 rows in set (0.00 sec)
(4) 显示以L开头的名字的同学的信息
MariaDB [hellodb]> 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.00 sec)
(5) 显示TeacherID非空的同学的相关信息
MariaDB [hellodb]> 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.00 sec)
(6) 以年龄排序后，显示年龄最大的前10位同学的信息
MariaDB [hellodb]> 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 |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
+-------+--------------+-----+--------+---------+-----------+
10 rows in set (0.00 sec)
(7) 查询年龄大于等于20岁，小于等于25岁的同学的信息
MariaDB [hellodb]> 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)
MariaDB [hellodb]> 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)
练习
1、以ClassID分组，显示每班的同学的人数
MariaDB [hellodb]> select classid as 班级,count(classid) as 人数 from students where classid is not null group by classid;
+--------+--------+
| 班级 | 人数 |
+--------+--------+
| 1 | 4 |
| 2 | 3 |
| 3 | 4 |
| 4 | 4 |
| 5 | 1 |
| 6 | 4 |
| 7 | 3 |
+--------+--------+
7 rows in set (0.00 sec)
MariaDB [hellodb]>
2、以Gender分组，显示其年龄之和
MariaDB [hellodb]> select gender as 性别,sum(age) as 年龄 from students group by gender;
+--------+--------+
| 性别 | 年龄 |
+--------+--------+
| F | 190 |
| M | 495 |
+--------+--------+
2 rows in set (0.00 sec)
3、以ClassID分组，显示其平均年龄大于25的班级
MariaDB [hellodb]> select classid as 班级,avg(age) as 年龄 from students where classid is not null group by classid having 年龄>25;
+--------+---------+
| 班级 | 年龄 |
+--------+---------+
| 2 | 36.0000 |
| 5 | 46.0000 |
+--------+---------+
2 rows in set (0.00 sec)
4、以Gender分组，显示各组中年龄大于25的学员的年龄之和
MariaDB [hellodb]> select gender as 性别,sum(age) as 年龄 from students where age > 25 group by gender;
+--------+--------+
| 性别 | 年龄 |
+--------+--------+
| M | 317 |
+--------+--------+
1 row in set (0.00 sec)
5、显示前5位同学的姓名、课程及成绩
MariaDB [hellodb]> select st.name as 姓名,co.course as 课程,sc.score as 分数 from students as st
inner join scores as sc on st.stuid=sc.stuid inner join courses as co on sc.courseid=co.courseid limit 5;
+-------------+----------------+--------+
| 姓名 | 课程 | 分数 |
+-------------+----------------+--------+
| 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 |
+-------------+----------------+--------+
5 rows in set (0.00 sec)
6、显示其成绩高于80的同学的名称及课程
MariaDB [hellodb]> MariaDB [hellodb]> select st.name as 姓名,co.course as 课程 from students as st
inner join scores as sc on st.stuid=sc.stuid inner join courses as co on sc.courseid=co.courseid where score>80;
+-------------+----------------+
| 姓名 | 课程 |
+-------------+----------------+
| 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)
7、取每位同学各门课的平均成绩，显示成绩前三名的同学的姓名和平均成绩
MariaDB [hellodb]> select st.name as 姓名,avg(sc.score) as 成绩 from scores as sc
inner join students as st on st.stuid=sc.stuid group by 姓名 order by 成绩 desc limit 3;
+-------------+---------+
| 姓名 | 成绩 |
+-------------+---------+
| Shi Qing | 96.0000 |
| Shi Zhongyu | 85.0000 |
| Xi Ren | 84.5000 |
+-------------+---------+
3 rows in set (0.00 sec)
8、显示每门课程课程名称及学习了这门课的同学的个数
MariaDB [hellodb]> select co.course as 课程,count(st.name) as 人数 from students as st
inner join scores as sc on st.stuid=sc.stuid inner join courses as co on sc.courseid=co.courseid group by 课程;
+----------------+--------+
| 课程 | 人数 |
+----------------+--------+
| Dagou Bangfa | 2 |
| Daiyu Zanghua | 2 |
| Hamo Gong | 3 |
| Jinshe Jianfa | 1 |
| Kuihua Baodian | 4 |
| Taiji Quan | 1 |
| Weituo Zhang | 2 |
+----------------+--------+
7 rows in set (0.00 sec)
9、显示其年龄大于平均年龄的同学的名字
MariaDB [hellodb]> 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)
10、显示其学习的课程为第1、 2，4或第7门课的同学的名字
MariaDB [hellodb]> select st.name as 姓名,sc.score as 成绩,co.course as 课程 from scores as sc
inner join students as st on st.stuid=sc.stuid inner join courses as co on co.courseid=sc.courseid
where co.courseid in (1,2,4) or co.courseid = 7;
+-------------+--------+----------------+
| 姓名 | 成绩 | 课程 |
+-------------+--------+----------------+
| Yu Yutong | 39 | Hamo Gong |
| Shi Qing | 96 | Hamo Gong |
| Xi Ren | 86 | Hamo Gong |
| Shi Zhongyu | 77 | Kuihua Baodian |
| Shi Potian | 47 | Kuihua Baodian |
| Xie Yanke | 88 | Kuihua Baodian |
| Ding Dian | 89 | Kuihua Baodian |
| Lin Daiyu | 57 | Taiji Quan |
| Yu Yutong | 63 | Dagou Bangfa |
| Xi Ren | 83 | Dagou Bangfa |
+-------------+--------+----------------+
10 rows in set (0.00 sec)
11、显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学
MariaDB [hellodb]> select st.name as '姓名',st.age as '年龄',st.classid as '班级',tmp.平均年龄 from students as st
left join (select classid from students group by classid having classid is not null and count(name) > 3) as c
on st.classid=c.classid,(select classid,avg(age) as '平均年龄' from students group by classid ) as tmp
where st.classid=tmp.classid and st.age > tmp.平均年龄 order by 班级;
+---------------+--------+--------+--------------+
| 姓名 | 年龄 | 班级 | 平均年龄 |
+---------------+--------+--------+--------------+
| Shi Potian | 22 | 1 | 20.5000 |
| Xu Zhu | 21 | 1 | 20.5000 |
| Xie Yanke | 53 | 2 | 36.0000 |
| Yu Yutong | 26 | 3 | 20.2500 |
| Lin Chong | 25 | 4 | 24.7500 |
| Ding Dian | 32 | 4 | 24.7500 |
| Yuan Chengzhi | 23 | 6 | 20.7500 |
| Huang Yueying | 22 | 6 | 20.7500 |
| Hua Rong | 23 | 7 | 19.6667 |
+---------------+--------+--------+--------------+
9 rows in set (0.00 sec)
12、统计各班级中年龄大于全校同学平均年龄的同学
MariaDB [hellodb]> select name as 姓名,age as 年龄,classid as 班级 from students where age > (select avg(age) from students);
+--------------+--------+--------+
| 姓名 | 年龄 | 班级 |
+--------------+--------+--------+
| Xie Yanke | 53 | 2 |
| Ding Dian | 32 | 4 |
| Shi Qing | 46 | 5 |
| Tian Boguang | 33 | 2 |
| Sun Dasheng | 100 | NULL |
+--------------+--------+--------+
5 rows in set (0.00 sec)
