第22章，mysql数据库-1
更多内容请点击：
Linux学习从入门到打死也不放弃，完全笔记整理（持续更新，求收藏，求点赞~~~~）
https://blog.51cto.com/13683480/2095439
第22章，mysql数据库-1
本章内容：
关系型数据库基础
mariadb 安装和使用
二进制安装mariadb
源码编译安装mariadb
MYSQL体系架构
存储引擎介绍
SQL语句使用
视图，函数，存储过程
流程控制和触发器
数据库基础：
数据的时代：
涉及的数据量越来越大
数据不随程序的结束而消失
数据被多个程序共享
大数据
数据库的发展史：
萌芽阶段：使用磁盘文件系统来存储数据
初级阶段：使用层级模型，网状模型的数据库
中级阶段：关系型数据库和结构化查询语言
高级阶段：”关系-对象“ 型数据库
文件管理系统的缺点：
编写应用程序不方便
数据冗余不可避免
应用程序依赖性
不支持对文件的并发访问
数据间联系弱
难以按用户视图表示数据
无安全控制功能
数据库管理系统的优点：
相关关联的数据的集合
较少的数据冗余
程序和数据相互独立
保证数据的安全、可靠
最大限度地保证数据的正确性
数据可以并发使用并能同时保证一致性
数据库管理系统：
DBMS（database management system）
数据库是数据的汇集，它以一定的组织形式存于存储介质上
DBMS是管理数据库的系统软件，它实现数据库系统的各种功能。是数据库系统的核心
DBA：（database administrator）负责数据库的规划，设计，协调，维护和管理等工作
应用程序指以数据库为基础的应用程序
数据库管理系统的基本功能：
数据定义
数据处理
数据安全
数据备份
数据库系统架构：
单机架构
大型主机/终端架构
C/S主从式架构
分布式架构
关系型数据库：
RDBMS：
关系：关系就是二维表，并满足一定规则
行：row，表中的每一行，又称为一条记录
列：column，表中的每一列，称为属性，字段
主键：（Primary key）：用于唯一确定一个记录的字段
域：domain：属性的取值范围，如性别只能是男和 女 两个值
事务：transaction，多个操作被当做一个整体对待，具有ACID特性
ACID:
A: 原子性，整个事务被封装成一个整体，具有整体原子性，要么执行完成，
如执行途中被中断，会将已执行的操作一一撤销，回滚（rollback）到事务执行之前的状态
C： 一致性，
I： 隔离性，并发执行时互相隔离，互补干涉
D： 一旦处理完成，将永久保留。不会回滚
数据三要素：
数据结构：
包含两类：
1 数据类型、内容、性质有关的对象，比如关系模型中的域、属性和关系等
2 与数据之间联系有关的对象，它从数据组织层表达数据记录与字段的结构
数据的操作：
数据提取：在数据集合中提取感兴趣的内容。（select）
数据更新：变更数据库中的数据。（insert delete update）
数据的约束条件：是一组完整性规则的集合
实体（行）完整性 entity integrity
域（列）完整性 domain integrity
参考完整性 referential integrity
简易数据规划流程：
第一阶段：收集数据，得到字段
收集必要且完整的数据项
转换成数据表的字段
第二阶段：把字段分类，归入表，建立表的关联
分隔数据表并建立关联的有点：
节省空间
减少输入错误
方便数据修改
第三阶段：
规范化数据库
数据库的正规化分析：
RDBMS设计范式基础概念：
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些
不同的规范被称为不同的范式
范式呈递次规范，越高的范式数据库冗余越小
目前关系数据库有6种范式：1NF,2NF,3NF,巴德斯科范式BCNF,4NF,5NF
满足最低要求的范式是第一范式（1NF）
在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF）
....
一般说来，数据库只需要满足第三范式（3NF）即可
范式：
1NF：
无重复的列
每一列都是不可分割的基本数据项，同一列中不能有多个值。即实体中的某个属性
不能有多个值或者不能有重复的属性。
第一范式（1NF）是对关系模式的基本要求，不满足第一范式的数据库就不是关系数据库
2NF：
满足第一范式1NF
要求表中的每行必须可以被唯一的区分，（通过一个字段，或多个字段）
通常为表加上一个列，以存储各个实例的唯一标识PK
属性完全依赖于主键
非PK的字段需要与整个PK有直接相关性
3NF：
满足第二范式2NF
属性不依赖与其它非主属性。
要求一张表中不能包含已在其他表中已包含的且为非主键的字段，
即，如果一张表的非主键字段包含在另一张表中，则必须要主键
非PK的字段间不能有从属关系
SQL概念：
SQL: Structure Query Language
机构化查询语言
sql解释器
数据存储协议：基于C/S架构的应用层协议
S： server，监听于套接字，接受并处理客户端的应用请求
C： client
程序接口：
CLI 字符接口
GUI 图形接口
应用编程接口：
ODBC: Open Database Connectivity 开放式数据库连接
JDBC：Java Database Connettivity java数据库连接
约束：constraint，表中的数据要遵守的限制
主键： 一个个多个字段的组合，填入的数据必须能在本表中唯一标识本行
必须提供数据，即NOT NULL，一个表只能有一个主键
唯一键：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行
允许为NULL，一个表可以存在多个
外键： 一个表中的某字段可填入的数据取决于另一个表的主键或唯一键已有的数据
检查： 字段值在一定范围内
基本概念：
索引：
将表中的一个或多个字段中的数据复制一份另存，并且此些需要按特定次序排列存储
关系运算：
选择：挑选出符合条件的行 row
投影：挑选出符合条件的列 column
连接：表间字段的关联
数据模型：
数据抽象：
物理层：数据存储格式，级RDBMS 在磁盘上如何组织文件
逻辑层：DBA 角度，描述存储什么数据，以及数据间存在什么样的关系
视图层：用户角度，描述DB中的部分数据
关系模型的分类：
关系模型
基于对象的关系模型
半结构化的关系模型，xml数据
mariadb 安装和简单使用：---------------------------------------------------------------------
mariadb安装方式：
1 源码编译安装
2 二进制格式的程序包安装
3 程序包管理器安装
方法1 使用安装光盘
方法2 项目官方yum源，安装最新版
mariadb 安全初始化：
1 先使用yum安装mariadb-server
yum install mariadb-server
安装会自动安装客户端工具：mariadb
2 stytemctl start mariadb 启动服务
使用客户端工具：
/usr/bin/mysql 连接服务器
注意此时本机用户，可以任意连接服务器，且无需密码，所有没有安全保障
此时执行：
select user,host,password from mysql.user;
MariaDB [mysql]> select user,host,password from user;
+------+-------------+----------+
| user | host | password |
+------+-------------+----------+
| root | localhost | |
| root | 2-centos7.5 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | 2-centos7.5 | |
+------+-------------+----------+
6 rows in set (0.00 sec)
可以看到password为空，且包含2个匿名账号
3 使用exit退出
执行：mysql_secure_installation 进行初始化
包括：
是否设置root账号密码
是否删除匿名账号
是否禁止root 远程登录
是否删除test数据库
4 执行完毕之后，（建议设置密码），再次连接，需使用：
mysql -uroot -p
再次执行:
select user,host,password from mysql.user;
MariaDB [mysql]> select user,host,password from user;
+------+-------------+-------------------------------------------+
| user | host | password |
+------+-------------+-------------------------------------------+
| root | localhost | *128977E278358FF80A246B5046F51043A2B1FCED |
| root | 2-centos7.5 | *128977E278358FF80A246B5046F51043A2B1FCED |
| root | 127.0.0.1 | *128977E278358FF80A246B5046F51043A2B1FCED |
| root | ::1 | *128977E278358FF80A246B5046F51043A2B1FCED |
+------+-------------+-------------------------------------------+
4 rows in set (0.00 sec)
mariadb 程序：
客户端程序：
mysql： 交互式的CLI工具
mysqldump：备份工具，基于mysql协议向mysqld发起查询请求，并将查询的所有数据
装换成insert等写操作语句保存文本文件中
mysqladmin：基于mysql协议管理mysqld
mysqllimport： 数据导入工具
myisam存储引擎的管理工具：
myisamchk： 检查myisam库
myisampack： 打包myisam表，只读
服务器端程序：
mysqld_safe
mysqld 获取默认配置：mysqld --print-defaults
mysqld_multi
用户账号:
mysql用户账号由两部分组成：
'username'@'host'
说明：
host限制此用户可通过哪些远程主机连接mysql服务器
支持使用通配符：
% 匹配任意长度的任意字符
172.20.0.0/255.255.0.0 或172.16.%.%
下划线 _ 匹配任意单个字符
mysql客户端：
连接数据库
mysql -uroot -p
交互式模式：
可运行命令有两类：
客户端命令：\h,help 获取帮助选项
\u,use 切换数据库
\s,status 查看状态
\!,system 执行shell命令
\R,prompt 修改mysql提示符
服务器端命令：
SQL语句; 必须使用;结尾
脚本模式：
mysql -uUSER -pPASSWORD < file.sql
mysql > source /path/from/somefile.sql
mysql客户端可用选项：
-A,--no-auto-rehash 禁止补全
-u,--user= 用户名，默认为root
-h,--host= 服务器主机，默认为localhost
-p,--port= 服务器端口
-S,--socket= 指定连接socket文件路径
-D,--database= 指定默认数据库
-C,--compress 启用压缩
-e,"SQL" 执行SQL命令
-V,--version 显示版本
-v,--verbose 显示详细信息
--print-default 获取程序默认使用的配置
--safe-updates| --i-am-a-dummy|-U 删除提醒模式
命令示例：
use mysql 切换数据库为mysql
show tables;|databases 查看当前所有表|数据库
select user（）； 查看当前用户
select version（）； 查看数据库版本
socket地址：
服务器监听的两种socket地址：
ip socket： 监听在tcp的3306端口，支持远程通信
unix sock： 监听在sock文件上，仅支持本机通信
如：/var/lib/mysql/mysql.socket
说明：host为localhost，127.0.0.1时自动使用unix.socket
服务器端配置：
服务器端：工作特性有多种配置方式
1 命令行选项：
2 配置文件：类ini格式
集中式的配置，能够为mysql的各应用程序提供配置信息
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
[mysqld_multi]
[mysql]
[mysqldump]
[server]
[client]
格式说明：
parameter=value
_和- 相同
0，off，false 意义相同
1,on，true 意义相同
配置文件：
后面覆盖前面的配置文件，顺序如下
/etc/my.cnf
/etc/mysql/my.cnf /etc/my.cnf.d/*.cnf
sysconfdir/my.cnf
$MYSQL_HOME/my.cnf server-specific选项
--default-extra-file
~/.my.cnf user-specific选项
建议开启的配置项：
innodb_file_per_table =on
skip_name_resolve =on
获取可用参数列表:
mysqld --help --verbose
侦听3306/tcp端口可以绑定在一个或全部接口IP上
vim /etc/my.cnf
[mysqld]
skip-networking=1 关闭网络连接，只侦听本地客户端
通用二级制格式安装 mariadb ：-----------------------------------------------------------
1 准备用户和组
groupadd -r -g 306 mysql
useradd -r -g 306 -u 306 -d /mysqldb -s /sbin/nologin mysql
2 准备数据目录，建议使用逻辑卷
使用单独逻辑卷分区挂载 /mysqldb
chown mysql: /mysqldb
3 准备二进制程序，解压缩，并创建软链接，修改目录权限
tar -xf mariadb-10.2.15-linux-x86_64.tar.gz -C /usr/local/
cd /usr/local/
ln -sv mariadb-10.2.15-linux-x86_64/ mysql
chown root:mysql mysql/
4 添加PATH路径
vim /etc/profile.d/mysql.sh
PATH=/usr/local/mysql/bin:$PATH
5 准备配置文件,复制并适当修改
cd /usr/local/mysql/
cp support-files/my-huge.cnf /etc/mysql/my.cnf
vim /etc/mysql/my.cnf
添加项：
datadir =/mysqldb
innodb_file_per_table =on
skip_name_resolve =on
6 创建数据库文件
cd /usr/local/mysql/
./scripts/mysql_install_db --datadir=/mysqldb --user=mysql
7 创建日志文件
touch /var/log/mysqld.log
chown mysql /var/log/mysqld.log
8 准备服务脚本，启动脚本
cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld
chkconfig --add mysqld
systemctl start mysqld
9 安全初始化：
mysql_secure_installation
源码编译安装 mariadb ：--------------------------------------------------------------
1 安装开发包组：
yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel \
boost devel gcc gcc-c++ cmake libevent-devel gnutls-devel libaio-devel \
openssl-devel ncurses-devel libxml2-devel
2 添加用户和组
groupadd -r -g 306 mysql
useradd -r -g 306 -u 306 -d /data/mysqldb mysql
3 准备数据目录，建议使用逻辑卷
mkdir /data/mysqldb
chown mysql:mysql /data/mysqldb
4 解压源码包，并cd进解压之后的目录
编译选项
https://dev.mysql.com/doc/refman/5.7/en/source-configuration-options.html
执行cmake
cmake . \
-DCMAKE_INSTALL_PREFIX=/app/mysql \
-DMYSQL_DATADIR=/data/mysqldb/ \
-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=/app/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci
5 编译并安装
make && make install
6 添加path变量
echo 'PATH=/app/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
7 准备配置文件
mkdir /etc/mysql
cp support-files/my-huge.cnf /etc/mysql/my.cnf
vim /etc/mysql/my.cnf
添加：
datadir=/data/mysqldb
8 生成数据库文件：
chown -R myslq:mysql /app/mysql
cd /app/mysql
./scripts/mysql_install_db --datadir=/data/mysqldb --user=mysql
9 准备启动脚本
cp ./support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
10 启动服务并测试
service mysqld start
mysql删除过程：------------------------------------------------------------------
1 删除安装目录，rm -rf /app/mysql
2 删除配置文件，rm -rf /etc/mysql;rm -rf /etc/my.cnf
3 删除数据目录，如有需要需提前备份或者转移数据，rm -rf /data/mysqldb
4 如有需要，删除二进制日志目录，rm -rf /data/mylog
5 删除服务脚本，rm -rf /etc/init.d/mysqld
6 删除编译源码目录，rm -rf mariadb-10.2.15
7 删除压缩包 rm -rf mariadb-10.2.15.tar.gz
8 删除mysql账号和组 userdel -r mysql;groupdel mysql
9 删除PASH变量
实现mariadb多实例：-------------------------------------------------------------
现在需要data目录下新建3个多实例，基于端口开放3306，3307，3308
实现流程：
1 安装mariadb，yum 编译或者二进制安装都可以
2 创建所需目录
mkdir -pv /data/{3306,3307,3308}/{etc,data,pid,log,socket}
chown -R /data/*
3 复制并修改配置文件
cp /etc/my.cnf /data/3306/etc/my.cnf
vim /data/3306/etc/my.cnf
需要修改如下配置：
[mysqld]
port=3306
datadir=/data/3306/data
socket=/data/3306/socket/mysql.sock
[mysql_safe]
log-error=/data/3306/log/mariadb.log
pid-file=/data/3306/pid/mariadb.pid
#!includedir /etc/my.cnf.d 注释掉此项
cp /data/3306/etc/my.cnf /data/3307/etc/my.cnf
vim /data/3307/etc/my.cnf
将3306改成3307
cp /data/3306/etc/my.cnf /data/3308/etc/my.cnf
vim /data/3308/etc/my.cnf
将3306改成3308
4 安装生成数据库文件
cd /basedir
./scripts/mysql_install_db --datadir=/data/3306/data --user=mysql
./scripts/mysql_install_db --datadir=/data/3307/data --user=mysql
./scripts/mysql_install_db --datadir=/data/3308/data --user=mysql
5 准配服务启动脚本：
脚本会在步骤8 提供
cp /root/mysqld /data/3306/mysqld
vim /data/3306/mysqld
需要修改的项目：
port=3306 使用端口
mysql_pwd= 密码，由于新装mairadb，还未安全初始化，密码为空
cmd_path="/usr/local/mysql/bin" 安装二进制程序目录
mysql_basedir="/data" 3306的父目录
stop 函数中 -p 选项删除，因为现在不需要密码
如需使用，可以设置密码之后使用 -p 选项，
cp /data/3306/mysqld /data/3307/
cp /data/3306/mysqld /data/3308/
复制并修改端口号
6 启动服务，执行
/data/3306/mysqld start |stop
/data/3307/mysqld start |stop
/data/3308/mysqld start |stap
7 连接数据库：使用：
mysql -S /data/3306/socket/mysql.sock
mysql -S /data/3307/socket/mysql.sock
mysql -S /data/3308/socket/mysql.sock
8 启动服务脚本：
#!/bin/bash
port=3306
mysql_user="root"
mysql_pwd=
cmd_path="/usr/local/mysql/bin"
mysql_basedir="/data"
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} -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
---------------------------------------------------------------------------
MYSQL体系架构：-------------------------------------------------------------------
关系型数据库的常用组件：
常用组件：
数据库： database
表： table
行： row
列： column
索引： index
视图： view
用户： user
权限： privilege
存储过程： proceduce，无返回值
存储函数： function ， 有返回值
触发器： trigger
时间调度器：event scheduler ，任务计划
命名规则：
1 必须以字母开头
2 可包括数字和三个特殊字符（ # _ $ ）
3 不要使用mysql的保留字
create database select index table 等等
4 同一database （schema）下的对象不能同名
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-86， -89，-92 -99，-03
SQL语法规范：
1 在数据库系统中，SQL语句不区分大小写（建议用大写）
2 但字符串常量区分大小写
3 SQL语句可单行或多行书写，以;结尾
4 关键词不能跨多行或简写
5 用空格和缩进来提高语句的可读性
6 子句通常位于独立行，便于编辑，提高可读性
7 注释：
SQL标准：
/*注释内容*/ 多行注释，也可单行，可插入
-- 注释内容 单行注释，注意有空格
MYSQL：
#
SQL 语句分类：
DDL：Data Defination Language 数据定义语言
CREATE,DROP,ALTER
DML: Data Manipulation Language 数据操作语言
INSERT,DELETE,UPDATE
DCL：Data Control Language 数据控制语言
GRANT,REVOKE
DQL：Data Query Language 数据查询语言，有时也归为DML
SELECT
keyword：
SELECT
FROM
WHERE
SQL 语句构成：
keyword 组成 clause（条目，字句）
多条clause 组成语句
示例：
SELECT * SELECT 子句
FROM students FROM 子句
WHERE age>10 WHERR 子句
SELECT * FROM students WHERE age>10; 完整语句
MYSQL体系架构：------------------------------------------------------------------
mysql组成：
1 Connectors：
指的是不同语言中与SQL的交互
2 Management Serveices & Utilities：
系统管理和控制工具，例如备份恢复、Mysql复制、集群等
3 Connection Pool: 连接池：
管理缓冲用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求
4 SQL Interface: SQL接口：
接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
5 Parser: 解析器，
SQL命令传递到解析器的时候会被解析器验证和解析。
解析器是由Lex和YACC实现的，是一个很长的脚本， 主要功能：
a . 将SQL语句分解成数据结构，并将这个结构传递到后续步骤，
以后SQL语句的传递和处理就是基于这个结构的
b. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的
6 Optimizer:
查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。
他使用的是“选取-投影-联接”策略进行查询
7 Cache和Buffer（高速缓存区）： 查询缓存，
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
8 Engine ：存储引擎。
存储引擎是MySql中具体的与文件打交道的子系统。也是Mysql最具有特色的一个地方。
Mysql的存储引擎是插件式的。它根据MySql AB公司提供的文件访问层的一个抽象接口
来定制一种文件访问机制（这种访问机制就叫存储引擎）
mariadb特性：
插件式存储引擎：也称为"表类型"，存储管理器有多种实现版本，功能和特性可能均略有差别
用户可以根据需要灵活选择，Mysql5.5.5开始默认引擎为innodb
单线程，多线程
诸多扩展和新特性
提供了较多测试组件
开源
存储引擎：-----------------------------------------------------------------------
MyISAM 引擎特点：
不支持事务
表级锁定
读写相互阻塞，写入不能读，读时不能写
只缓存索引
不支持外键约束
不支持聚簇索引
读取数据较快，占用资源较少
不支持MVCC（多版本并发控制机制）高并发
崩溃恢复性较差
Mysql 5.5.5之前默认的数据库引擎
适合场景：
只读（或者写较少）、表较少（可以接受长时间进行修复操作）
引擎文件：
tbl_name.frm 表格式定义
tbl_name.MYD 数据文件
tbl_name.MYI 索引文件
InnoDB引擎特点：
支持事务，适合处理大量短期事务
行级锁
有多种事务隔离级别，读写阻塞与隔离级别相关
可缓存数据和索引
支持聚簇索引
崩溃恢复性更好
支持MVCC高并发
从MYSQL5.5后支持全文索引
从mysql5.5.5开始为默认的数据库引擎
innodb数据库文件：
1 所有innodb表的数据和索引放置于同一个表空间中
表空间文件：datadir定义的目录下
数据文件：ibddata1，idbdata2，...
2 每个表单独使用一个表空间存储表的数据和索引
启用： innodb_file_table=ON
mariadb5.5之后默认开启
两类文件放在数据库独立目录中：
数据文件（存储数据和索引）：tb_name.ibd
表格式定义：tb_name.frm
其他存储引擎：
performance_schema:
performance_schema数据库
memory：
将所有数据存储在RAM中，以便在需要快速查找参考和其他类似数据的
环境中进行快速访问。使用存放临时数据，引擎以前被称为HEAP引擎
MRG_MyISAM:
是MySQL DBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组，
并将它们作为一个对象引用。适用于VLDB（Very Large Data Base）环境，如数据仓库
Archive：为存储和检索大量很少参考的存档或安全审核信息，只支持SELECTHE INSERT操作
支持行级锁和专用缓存区
Federated联合：
用于访问其他远程mysql服务器一个代理，它通过创建一个远程mysql服务器的客户端
连接，并将查询传输到远程服务器执行，而后完成数据存取，提供链接单独mysql的能力，
以便从多个物理服务器创建一个逻辑数据库。非常适合分布式或数据集市环境。
DBD:
可替代innodb的事物引擎，支持commit、rollback和其他事物特性
cluster/NDB:
mysql的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序
这类查找需求还要求具有最高的正常工作时间和可用性
CSV:
CSV存储引擎使用逗号分隔值格式将数据库存储在文本文件中。可以使用
CSV引擎以CSV格式导入和导出其他软件的应用程序之间的数据交换
BLACKHOLE：
黑洞存储引擎接受但不存储数据，检索总是返回一个空集。该功能可用于分布式
数据库设计，数据自动复制，但不是本地存储。
example：
"stub"引擎，它什么都不做，可以使用此引擎创建表，但不能将数据存储
在其中或从中检索。目的是作为例子来说明如何开始编写新的
mariadb支持的其他存储引擎：
qqgraph
sphnxse
tokudb
cassandra
connect
squence
管理存储引擎：
查看mysql支持的存储引擎：
SHOW ENGINES
查看当前默认的存储引擎：
SHOW VARIABLES LIKE '%engine%';
default_storage_engine 默认引擎
storage_engine 当前引擎
设置默认的存储引擎：
[mysqld]
default_storage_engine= innodb;
查看库中所有使用的存储引擎：
SHOW TABLES STATUS LIKE'tb_name'\G
SHOW CREATE TABLE tb_name;
设置表的存储引擎：
CREATE TABLE tb_name() ENGINE=innodb;
ALTER TABLE tb_name ENGINE=innodb;
数据库操作：-------------------------------------------------------------------------
查看操作: SHOW
HELP SHOW;
SHOW DATABASES;
SHOW TABLES;
SHOW TABLE STATUS FROM db_name; | LIKE 'tb_name';
SHOW CREATE DATABASE 'db_name';
SHOW CREATE TABLE 'tb_name';
SHOW CHARACTER SET;
SHOW COLLATION;
.....
创建数据库：
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name 字符集
| [DEFAULT] COLLATE [=] collation_name 排序规则
删除数据库：
DROP DATABASE|SCHEMA [IF EXISTS] db_name;
查看支持的所有字符集： SHOW CHARACTER SET；
查看支持的所有排序规则：SHOW COLLATION;
获取命令使用帮助： HELP keyword；
查看数据库列表： SHOW DATABASES;
创建表：------------------------------------------------------------------------
查看帮助：HELP CREATE TABLE;
1 直接创建：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
简写为：
CREATE TABEL [IF NOT EXISTS] tb_name
(col1 type1 修饰符;col2 type2 修饰符,...)
例如：
CREATE TABLE students (sid TINYINT(4) UNSIGNED PRIMARY KEY,
name CHAR(30),gender ENUM('M','F'),age TINYINT(2) UNSIGNED);
使用DESC tb_name查看表头，如下
MariaDB [laa]> DESC students;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| sid | tinyint(4) unsigned | NO | PRI | NULL | |
| name | char(30) | YES | | NULL | |
| gender | enum('M','F') | YES | | NULL | |
| age | tinyint(2) unsigned | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
2 通过查询现存表创建，新表会被直接插入查询而来的数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
select_statement
例如：
CREATE TABLE hello1 SELECT * FROM emp;
CREATE TABLE hello2 SELECT id,sex FROM emp;
3 通过复制现存的表的表结构创建，但不复制数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
例如：
CREATE TABLE IF NOT EXISTS hello3 LIKE students;
注意： [[STORAGE] ENGINE [=] engine_name]
此项指明创建表时使用的存储引擎，同一库中不同表可以使用不同的存储引擎
但是建议使用同一种
示例：
查看使用的引擎：
SHOW TABLE STATUS FROM db_name\G;|LIKE 'tb_name'\G;
查看所有引擎：
SHOW ENGINES\G;
查看表：
SHOW TABLES [FROM db_name]
查看表结构：
DESC [db_name.]tb_name;
删除表：
DROP TABLE [IF EXISTS] [db_name.]tb_name;
查看表创建命令：
SHOW CREATE TABLE tb_name；
查看表状态：
SHOW TABLE STATUS LIKE 'tb_name';
查看数据库中所有表状态：
SHOW TABLE STATUS FROM db_name;
数据类型：----------------------------------------------------------------------
创建表过程：
CREATE TABEL [IF NOT EXISTS] tb_name
(col1 type1 修饰符;col2 type2 修饰符,...)
字段信息：
col：column，列名称
type： 列使用的数据类型
修饰符：PRIMARY KEY,INDEX,UNIQUE KEY,...
MYSQL支持多种列类型：
数值类型
日期/时间类型
字符串（字符）类型
选择正确的数据类型对于获得高性能至关重要，三大原则
更小的通常更好，尽量使用可正确存储数据的最小数据类型
简单更好，简单数据类型的操作通常需要更少的cpu周期
尽量避免nul，包含为null的列，对MYSQL更难优化
1 ×××：
TINYINT(m) [UNSIGNED] 1个字节（-128~127）加上UNSIGNED（0~255）
SMALLINT(m) [UNSIGNED] 2个字节（-32768~32767）
MEDIUMINT(m) [UNSIGNED] 3个字节 (-8388608~8388607)
INT(m) [UNSIGNED] 4个字节
BIGINT(m) [] 8个字节
BOOL,BOOLEAN: 布尔型，TINYINT(1)的同义词，
zero被视为假，非zero 被视为真
UNSIGNED 必须直接加在int类型之后，中间不能隔其他字段
UNSIGNED 表示去掉负值范围，从0开始计数，最大值翻倍
(m),表示SELECT查询结果之中的显示宽度，并不影响实际的取值范围，规定了MYSQL
的一些交互工具（例如命令行客户端）用来显示字符的个数。
对于存储和计算来说，INT(1)和INT(20) 是相同的
2 浮点型：
float(m,d): 单精度浮点型，4字节（7位精度），m总个数，d小数位
double（m,d）:双精度浮点型，8字节（15位精度），m总个数，d小数位
设一个字段定义为float(6,3),如果插入一个数123.45678，实际数据库里存放
的是123.457，但总个数还以实际为准，即6位
3 定点数：decimal(m,d)
·在数据库中存放的是精确值，存为十进制
·decimal(m,d) 参数m<65 是总个数，d<30且d<m 是小数位
·MYSQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。
例如：decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节，小数点前
的数字用4个字节，小数点后的数字用4个字节，小数点本身占一个字节
·浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。flout使用4字节，double使用8字节
·因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用
decimal，例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal
4 字符型
CHAR(n) 固定长度，1字节存储长度，最多255个字符， 不区分大小写
VARCHAR(n) 可变长度，2字节存储长度，最多65535个字符， 不区分大小写
BINARY(M) 固定长度，1字节存储长度，最多255个字符， 区分大小写
VARBINARY(M)可变长度，2字节存储长度，最多65535个字符， 区分大小写
TINYTEST 可变长度，最多255 1字节 区分大小写
TEXT 可变长度，最多65535 2字节
MEDIUMTEXT 可变长度，3字节存储长度
LONGTEXT 可变长度，4字节存储长度
内建类型： ENUM：枚举类型
SET：集合
char和varchar：
. char(n)若存入字符数小于n，则以空格补齐，查询时再将空格去掉。
所以char类型存储的字符串末尾不能有空格，varchar不限于此。
. char(n),固定长度，char(4)不管是存入几个字符，都将占用4个字节，
varchar是存入的实际字符数+1个字节，所以varchar(4),存入3个字符
将占用4个字节。
. char类型的字符串检索速度要比varchar类型的快
varchar和text：
. varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数
+1个字节，text是实际字符数+2个字节。
. text类型不能有默认值
. varchar 可直接创建索引，text创建索引要指定前多少个字符。
varchar查询熟读快与text
5 二进制数据：BLOB
BLOB和TEXT存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而blob
是以二进制方式存储，不区分大小写
blob存储的数据只能整体读出
text可以指定字符集，blob不用指定字符街
6 日期时间类型
date 日期'2008-12-2' 4字节
time 时间'12:25:36' 3字节
datetime 日期时间'2008-12-2 22:06:44' 8字节
需要手动写入
timestamp 自动存储记录修改时间，使用%s 秒时间记录 4字节
无需手动写入，系统自己记录
year(2),year(4): 年份
TIMESTAMP字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的
字段可以存放这条记录最后被修改的时间
修饰符：
所有类型：
NULL 数据列可包含null值
NOT NULL 数据列不允许包含null值
DEFAULT XX 默认值
PRIMARY KEY 主键
UNIQUE KEY 唯一键
CHARACTER SET name 指定一个字符集
数值型：
AUTO_INCREMENT 自动递增，适用于整数类型
UNSIGNED 无符号
示例：
CREATE TABLE students (id INT UNSIGNED NOT NULL PRIMARY KEY,
name VARCHAR(20) NOT NULL,age TINYINT UNSIGNED);
CREATE TABLE students2 (id INT UNSIGNED NOT NULL,name VARCHAR(30)
NOT NULL,age TINYINT UNSIGNED,PRIMARY KEY(id,name));
修改和删除表：---------------------------------------------------------------------
删除表：
DROP TABLE [IF EXISTS] tb_name[,tb_name2...]
[RESTRICT| CASCADE ]; 关联删除
修改表：HELP ALTER TABLE;
ALTER TABLE tb_name ...
添加字段（列）：
ALTER TABLE age ADD age TINYINT UNSIGNED [FIRST | AFTER col_name];
ALTER TABLE age ADD [COLUMN](age TINYINT UNSIGNED,name...);
....
注意使用()的情况无法使用FIRST|AFTER col
()括号内可以添加多字段，但是会居于末尾
删除字段（列）
ALTER TABLE age DROP col_name;
一次只能删除一列
修改字段：
修改列的默认值,
ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
例如：
ALTER TABLE AGE ALTER uid SET DEFAULT 123;
ALTER TABLE AGE ALTER uid DROP DEFAULT;
修改列名称，并重新定义列属性和排列位置
CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]
例如：
ALTER TABLE AGE CHANGE AGE age TINYINT UNSIGNED AFTER NAME;
修改列属性
MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
例如：
ALTER TABLE AGE CHANGE uid UID INT UNSIGNED UNIQUE;
ALTER TABLE AGE MODIFY UID VARCHAR(10);
修改索引：
添加索引：ADD INDEX
删除索引：DROP INDEX
示例：
添加key：
ALTER TABLE age ADD UNIQUE KEY(gid);
添加索引：
ALTER TABLE AGE ADD INDEX(gid);
查看表中已添加索引
SHOW INDEXES FROM AGE;
DML语句：------------------------------------------------------------------------
DML: INSERT,DELETE,UPDATE,SELECT
INSERT:
一次插入一行或多行数据
获取帮助：HELP INSERT
注意：
添加字符必须使用''
数值不能使用''
语法1：
INSERT [INTO] tb_name [(col_name,..)] VALUE|VALUES
({expr|DEFAULT},...),(...),...
[ON DUPLICATE KEY UPDATE]
例如：
INSERT INTO age VALUES('laly',18),('mike',25);
INSERT INTO newage VALUES(01,'alily',19),(02,'blily',18);
语法2：
INSERT [INTO] tb_name SET col1={expr|DEFAULT},.....
例如：
INSERT INTO age SET NAME='pite',age=34;
一直只能插入一条记录
语法3：
INSERT [INTO] tb_name [(col_name),..] SELECT...
例如：
INSERT INTO age(NAME,age) SELECT NAME,age FROM AGE WHERE age=19;
INSERT INTO age(NAME,age) SELECT NAME,uid FROM AGE WHERE age=19;
INSERT INTO age(NAME,age) SELECT age,id FROM AGE WHERE age=55;
要求：
插入的列不要求名称必须相同，如以上将uid这一列插入到了age中
但是数据类型有要求：
数值类型的可以插入数值和字符类型中
字符类型无法插入数值类型
UPDATE：
修改一行或多行数据：HELP UPDATE;
语法：
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
例如：
UPDATE age SET NAME='hello' WHERE age=18;
DELETE：
删除一行或多行数据：
语法：
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
例如:
DELETE FROM age WHERE age IS NULL;
其他：
truncate table emp; 快速删除表
操作示例：
CREATE TABLE scroe(id INT UNSIGNED PRIMARY KEY,name VARCHAR(20),score INT UNSIGNED);
DROP TABLE scroe;
CREATE TABLE score(id INT UNSIGNED PRIMARY KEY,name VARCHAR(20),score INT UNSIGNED);
ALTER TABLE score ADD gender ENUM('M','F');
ALTER TABLE score ALTER gender SET DEFAULT 'M';
ALTER TABLE score CHANGE id sid INT UNSIGNED;
ALTER TABLE score CHANGE gender sex CHAR(10);
ALTER TABLE score MODIFY sex ENUM('M','F');
ALTER TABLE score ADD fa VARCHAR(20);
ALTER TABLE score DROP fa;
ALTER TABLE score ALTER sex SET DEFAULT 'M';
INSERT score VALUES(1,'lily',96,'F'),(2,'lucy',66,'F');
INSERT score SET sid=3,name='tom',score=87;
DELETE FROM score WHERE sid=3;
SELECT * FROM score;
UPDATE score SET score=100 WHERE sex='F';
DELETE FROM score;
SELECT :-------------------------------------------------------------------------------
语法格式：
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
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}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]
选项：
1 查询条件区分大小写：binary
例如：
MariaDB [hellodb]> SELECT STUID,NAME FROM students WHERE NAME='SHI ZHONGYU';
+-------+-------------+
| STUID | NAME |
+-------+-------------+
| 1 | Shi Zhongyu |
+-------+-------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> SELECT STUID,NAME FROM students WHERE BINARY NAME='SHI ZHONGYU';
Empty set (0.00 sec)
2 字段显示可以使用别名：
col1 AS alias1，col1 AS alias2,...
例如：
SELECT stuid AS id,name as studentname FROM students;
3 WHERE 子句：
指明过滤条件以实现"选择"的功能：
过滤条件：布尔型表达式
算术操作符：+，-，*,/,%
比较操作符：=,!=,<>,,>,>=,<,<=，<=>(安全比较运算符，用来做 NULL 值的关系运算)
BETWEEN min_num AND max_num
IN (element1,element2,...)
IS NULL
IS NOT NULL
LIKE
%: 任意长度任意字符
_: 任意单个字符
RLIKE: 正则表达式，会使索引失效，如非必要，不建议使用
REGEXP: 匹配字符串可用正则表达式书写模式，同上
逻辑操作符：
NOT
AND
OR
XOR
例如：
SELECT * FROM students WHERE name='hua rong';
SELECT * FROM students WHERE stuid>5 AND stuid <10;
SELECT stuid,name FROM students WHERE stuid BETWEEN 5 AND 10;
SELECT * FROM students WHERE stuid>5+10;
SELECT * FROM students WHERE name IN ('SHI ZHONGYU','YU TUTONG','XU ZHU');
SELECT * FROM students WHERE name LIKE 'S%';
SELECT * FROM students WHERE name LIKE '%S%';
SELECT * FROM students WHERE name LIKE 'S_I%';
SELECT * FROM students WHERE teacherid IS NULL;
SELECT * FROM students WHERE teacherid IS NOT NULL;
SELECT * FROM students WHERE teacherid IS NOT NULL OR gender='F';
SELECT * FROM students WHERE teacherid IS NULL AND gender='F'
4 GROUP:
根据指定的条件把查询结果进行“分组”以用于做“聚合”运算
avg(): 均值
max(): 最大值
min(): 最小值
count(): 总数
sum(): 和
HAVING: 对分组聚合运算后的结果指定过滤条件
例如：
SELECT count(stuid),sum(stuid),avg(stuid),gender FROM students GROUP BY gender;
SELECT count(stuid),gender FROM students GROUP BY gender HAVING gender='F';
SELECT count(stuid),gender FROM students WHERE stuid>10 GROUP BY gender HAVING count(stuid)<10;
5 ORDER BY：
根据指定的字段对查询结果进行排序
升序： ASC
降序： DESC
NULL值处理：
默认null值为最小，即ASC排序，null值在最前端，DESC排序，null值在最后端
可以在被排序的列 col 前加"-",单独对col值反向排序
如 -col DESC 可以保持null在后的情况对col 做升序（从小到大）排序
例如：
SELECT id,score FROM scores ORDER BY score DESC;
SELECT stuid,teacherid FROM students ORDER BY -teacherid DESC;
SELECT * FROM students WHERE name LIKE 'X%' ORDER BY -classid DESC;
6 LIMIT [[offset,]row_count]:
对查询的结果进行输出行数数量限制
LIMIT 5 只显示前5行
LIMIT 3,5 跳过3行之后显示5行
7 对查询结果中的数据请求施加锁：
FOR UPDATE: 写锁，独占或排它锁，只有一个读和写
LOCK IN SHARE MODE: 读锁，共享锁，同时多个读
练习：
导入hellodb.sql生成数据库,（表结构会在后面附上）
(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
SELECT name,age FROM students WHERE age>25;
(2) 以ClassID为分组依据，显示每组的平均年龄
SELECT classid,avg(age) FROM students GROUP BY classid;
(3) 显示第2题中平均年龄大于30的分组及平均年龄
SELECT classid,avg(age) FROM students GROUP BY classid HAVING avg(age)>30;
(4) 显示以L开头的名字的同学的信息
SELECT * FROM students WHERE name LIKE 'L%';
(5) 显示TeacherID非空的同学的相关信息
SELECT * FROM students WHERE teacherid is NOT NULL;
SELECT * FROM students WHERE NOT teacherid='NULL';
(6) 以年龄排序后，显示年龄最大的前10位同学的信息
SELECT * FROM students ORDER BY age DESC LIMIT 10;
(7) 查询年龄大于等于20岁，小于等于25岁的同学的信息
SELECT * FROM students WHERE age<=25 AND age>=20;
SELECT * FROM students WHERE age BETWEEN 20 AND 25;
SELECT * FROM students WHERE age IN(20,21,22,23,24,25);
SQL JOINS: 多表查询
1 交叉连接：
笛卡尔乘积
两张表叠加，第一张表的每一行会单独对应第二张表的每一行
例如：
SELECT * FROM students,classes;
2 内连接：inner join(默认)
等值连接： 让表之间的字段以"等值"建立连接关系
写法一：
1 SELECT s.stuid,s.name,s.age,c.class FROM students AS s,classes AS
c WHERE s.classid=c.classid;
2 SELECT * FROM students AS st,scores AS sc WHERE st.stuid=sc.stuid;
3 SELECT s.name AS stu_name,s.age,s.gender,t.name AS tea_name FROM
students AS s,teachers AS t WHERE s.teacherid=t.tid;
写法二：SELECT .. FROM tb_a INNER JOIN ON tb_b ON tb_a.key=tb_b.key [WHERE] ..
1 SELECT s.stuid,s.name,s.age,c.class FROM students AS s INNER JOIN
classes AS c ON s.classid=c.classid;
2 SELECT * FROM students AS st INNER JOIN scores AS sc ON st.stuid=sc.stuid;
3 SELECT s.name AS stu_name,s.age,s.gender,t.name AS tea_name FROM students AS s
INNER JOIN teachers AS t ON s.teacherid=t.tid ;
[WHERE s.age>30]
自然连接：去掉重复列的 INNER JOIN 连接，需要两张表中有相同的column。
使用natural join 连接，不需要 "ON",
1 SELECT s.stuid,s.name,s.age,c.class FROM students AS s NATURAL JOIN classes AS c;
2 SELECT * FROM students AS st NATURAL JOIN scores AS sc;
自连接：将单张表当成两张表用，内连接
4 SELECT a.name,a.age,b.teacherid FROM students a INNER JOIN students b
ON a.stuid=b.teacherid;
3 外链接
左外连接：left join
左表全显示，右表中没有对应项会显示为NULL
1 SELECT s.stuid,s.name,s.age,c.class FROM students AS s LEFT JOIN
classes AS c ON s.classid=c.classid ;
右外连接：right join
右表全显示，左表中没有对应项会显示为NULL
2 SELECT * FROM students AS st RIGHT JOIN scores AS sc on
st.stuid=sc.stuid;
2 SELECT * FROM students AS st RIGHT JOIN scores AS sc ON
st.stuid=sc.stuid WHERE score>70;
左外半连接：
SELECT .. FROM tb_a LEFT JOIN ON tb_b ON tb_a.key=tb_b.key
WHERE b.key IS NULL
左外连接加上WHERE b.key is NULL
例如：
SELECT * FROM students s LEFT JOIN teachers t ON s.teacherid=t.tid
WHERE t.tid IS NULL;
右外半连接：
左外连接加上WHERE a.key is NULL
例如：
SELECT * FROM students AS st RIGHT JOIN scores AS sc on
st.stuid=sc.stuid;
WHERE st.stuid IS NULL
4 子查询：在查询语句中嵌套着查询语句，性能较差。基于某语句的查询结果再次查询
a 用在WHERE 子句中的子查询：
用于比较表达式中的子查询；子查询仅能返回单个值
SELECT name,age FROM students WHERE age>(SELECT avg(age) FROM students);
用于IN中的子查询：子查询应该单键查询并返回一个或多个值构成列表
SELECT stuid,name,age FROM students WHERE stuid IN (SELECT tid FROM teachers);
b 用于FROM子句中的子查询：
{ 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>20;
}
5 联合查询：UNION,将查询的结果上下粘合在一起，要求列的数量必须相同
例如：
SELECT stuid,name FROM students UNION SELECT stuid,score FROM scores;
可以用来粘合左外连接和右外连接，实现全连接
{ SELECT * FROM students as s LEFT JOIN scores AS c ON s.stuid=c.stuid
UNION
SELECT * FROM students as s RIGHT JOIN scores AS c ON s.stuid=c.stuid;
}
粘合左外半连接和 右外半连接，实现空心连接（全连接中去掉内连接）
{ SELECT * FROM students as s LEFT JOIN scores AS c ON s.stuid=c.stuid
WHERE c.stuid IS NULL
UNION
SELECT * FROM students as s RIGHT JOIN scores AS c ON s.stuid=c.stuid
WHERE s.stuid IS NULL;
}
SELECT 语句执行过程：
start---> FROM ---> WHERE ---> GROUP BY ---> HAVING ---> ORDER BY
---> SELECT ---> LIMIT ---> end result
练习：
导入hellodb.sql，以下操作在students表上执行（表结构会在后面附上）
1、以ClassID分组，显示每班的同学的人数
SELECT classid,count(stuid) FROM students GROUP BY classid ORDER BY -classid DESC;
2、以Gender分组，显示其年龄之和
SELECT gender,sum(age) FROM students GROUP BY gender;
3、以ClassID分组，显示其平均年龄大于25的班级
SELECT classid,avg(age) FROM students GROUP BY classid HAVING avg(age)>25;
4、以Gender分组，显示各组中年龄大于25的学员的年龄之和
SELECT gender,sum(age) FROM students WHERE age>25 GROUP BY gender;
5、显示前5位同学的姓名、课程及成绩
{ SELECT st.name,c.class,st.score FROM
(SELECT s.name,s.classid,sc.score FROM students AS s LEFT JOIN
scores AS sc ON s.stuid=sc.stuid) AS st
LEFT JOIN classes as c ON st.classid=c.classid ORDER BY score DESC LIMIT 5;
} 错误答案，题目前5名同学应该指的是学号前5.。。
{ SELECT st.name,c.class,st.score FROM
(SELECT s.name,s.classid,sc.score FROM students AS s INNER JOIN
scores AS sc ON s.stuid=sc.stuid WHERE s.stuid BETWEEN 1 AND 5) AS st
INNER JOIN classes as c ON st.classid=c.classid;
}
6、显示其成绩高于80的同学的名称及课程；
{ SELECT st.name,c.class FROM (SELECT s.name,s.classid,sc.score FROM
students AS s LEFT JOIN scores AS sc ON s.stuid=sc.stuid) AS st
LEFT JOIN classes as c ON st.classid=c.classid
WHERE score>80;
}
{ SELECT s.name,c.class FROM (SELECT name,classid FROM students WHERE
stuid IN (SELECT stuid FROM scores WHERE score>80)) AS s
LEFT JOIN classes AS c ON s.classid=c.classid;
} 错误答案，原因是两门课程，这里去掉了重复stuid
7、求前8位同学每位同学自己两门课的平均成绩，并按降序排列
{ SELECT f.stuid,f.name,avg(score),f.class FROM
(SELECT st.stuid,st.name,c.class,st.score FROM
(SELECT s.stuid,s.name,s.classid,sc.score FROM students AS s INNER JOIN
scores AS sc ON s.stuid=sc.stuid) AS st
INNER JOIN classes as c ON st.classid=c.classid) AS f
GROUP BY stuid ORDER BY avg(score) DESC;
} 错误答案，结果中同时显示了班级，过程中多插入了一张班级表
{ SELECT f.stuid,f.name,avg(score) FROM
(SELECT s.stuid,s.name,s.classid,sc.score FROM students AS s INNER JOIN
scores AS sc ON s.stuid=sc.stuid) AS f
GROUP BY stuid ORDER BY avg(score) DESC;
}
8、显示每门课程课程名称及学习了这门课的同学的个数
SELECT f.course,count(stuid) FROM
(SELECT sc.stuid,co.course FROM scores AS sc INNER JOIN courses
AS co ON sc.courseid=co.courseid) AS f GROUP BY course;
9、显示其年龄大于平均年龄的同学的名字
SELECT name FROM students WHERE age>(SELECT avg(age) FROM students);
10、显示其学习的课程为第1、2，4或第7门课的同学的名字
SELECT s.name FROM students AS s INNER JOIN
(SELECT stuid,courseid FROM scores WHERE courseid IN(1,2,4,7)) AS c
ON s.stuid=c.stuid;
11、显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学
{ SELECT name,age,avg(age) FROM students WHERE classid IN
(SELECT classid FROM students GROUP BY classid HAVING count(stuid)>=3)
GROUP BY classid HAVING age>avg(age);
} 错误答案
{ SELECT s.name,s.age,c.aavg FROM
(SELECT name,age,classid FROM students WHERE classid IN
(SELECT classid FROM students GROUP BY classid HAVING count(stuid)>=3))
AS s INNER JOIN
(SELECT classid,avg(age) AS aavg FROM students GROUP BY classid
HAVING count(stuid)>=3) AS c
ON s.classid=c.classid WHERE age>aavg;
}
12、统计各班级中年龄大于全校同学平均年龄的同学
SELECT name,age,classid FROM students WHERE age>(SELECT avg(age) FROM students);
视图，函数，存储过程：---------------------------------------------------------------------
视图：
视图： view，虚表，保存有实表的查询结果（SELECT语句）
创建方法：
CREATE VIEW view_name [(colmun_list)] AS select_statement
[WITH [CASCADED|LOCAL]] CHECK OPTION]
例如：
CREATE VIEW name_score AS SELECT s.stuid,s.name,sc.score FROM
students AS s INNER JOIN scores AS sc ON s.stuid=sc.stuid;
查看视图定义：
SHOW CREATE VIEW view_name;
删除视图：
DROP VIEW [IF EXISTS] view_name [...] [RESTRICT|CASCADE]
注意： 视图中的数据事实上存储于"基表"中，因此，其修改操作也会针对基表实现
其修改操作受基表限制
函数：
函数：FUNCTION,分为系统函数和自定义函数
系统函数:https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html
自定义函数：（user-defined function UDF）
保存在mysql.proc表中
创建UDF:
CREATE [AGGREGATE] FUNCTION function_name
(parameter_name type,[parameter_name type,...])
RETURNS {STRING|INTEGER|REAL}
runtime_body
说明： 参数可以有多个，也可以没有参数
必须有且只有一个返回值
查看UDF函数列表：
SHOW FUNCTION STATUS;
查看函数定义：
SHOW CREATE FUNCTION function_name;
删除UDF:
DROP FUNCTION function_name
调用自定义函数语法：
SELECT function_name(parameter_value,...)
注意：对于太过简单的函数，系统默认不予创建，需要设置变量
log_bin_trust_function_creators=ON;
示例：无参UDF
SET GLOBAL log_bin_trust_function_creators=ON;
CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "HELLO WORLD!";
示例：有参数UDF
MariaDB [hellodb]> DELIMITER //
MariaDB [hellodb]> CREATE FUNCTION deletebyid(uid SMALLINT UNSIGNED) RETURNS VARCHAR(20)
-> BEGIN
-> DELETE FROM students WHERE stuid = uid;
-> RETURN(SELECT COUNT(uid) FROM students);
-> END//
Query OK, 0 rows affected (0.03 sec)
MariaDB [hellodb]> DELIMITER ;
MariaDB [hellodb]> SELECT deletebyid(4);
+---------------+
| deletebyid(4) |
+---------------+
| 22 |
+---------------+
DELIMITER: 设置语句结束符
自定义函数中定义局部变量语法：
DECLARE 变量1[,变量2,..]变量类型[DEFAULT 默认值]
说明：局部变量的作用范围是在BEGIN...END 程序中，而且定义局部变量语句必须
在BEGIN...END的第一行定义
示例：
MariaDB [hellodb]> DELIMITER //
MariaDB [hellodb]> CREATE FUNCTION addtwonumber(x SMALLINT UNSIGNED,y SMALLINT UNSIGNED)
-> RETURNS SMALLINT
-> BEGIN
-> DECLARE a,b SMALLINT UNSIGNED DEFAULT 10;
-> SET a = x,b = y;
-> RETURN a+b;
-> END//
DELIMITER ;
MariaDB [hellodb]> SELECT addtwonumber(4,5);
+-------------------+
| addtwonumber(4,5) |
+-------------------+
| 9 |
+-------------------+
为变量赋值语法：
SET parameter_name = value[,parameter_name=value,..]
SELECT INTO parameter_name
示例：
DECLARE x INT;
SELECT COUNT(id) FROM tb_name INTO x;
RETURN x;
END//
存储过程：
存储过程：PROCEDURE 存储过程保存在mysql.proc表中
创建存储过程：
CREATE PROCEDURE sp_name ([proc_parameter[,proc_parameter,...]])
routime_body
其中：
proc_parameter:[IN|OUT|INOUT] parameter_name type
IN 表示输入参数
OUT表示输出参数
INOUT表示即可以输入也可以输出
parameter_name表示参数名称；type表示参数的类型
查看存储过程列表：
SHOW PROCEDURE STATUS;
查看存储过程定义：
SHOW CREATE PROCEDURE sp_name;
调用存储过程：
CALL sp_name([proc_parameter,...])
CALL sp_name
说明：当无参数时，可以省略"()",有参数数不可省略
存储过程修改：
ALTER语句修改存储过程只能修改存储过程的注释等无关信息
不能修改存储过程体，所以要修改存储过程，方法是删除重建
删除存储过程：
DROP PROCEDURE [IF EXISTS] sp_name
示例：创建无参存储过程：
MariaDB [hellodb]> DELIMITER //
MariaDB [hellodb]> CREATE PROCEDURE showtime()
-> BEGIN
-> SELECT now();
-> END//
MariaDB [hellodb]> CALL SHOWTIME //
+---------------------+
| now() |
+---------------------+
| 2018-06-13 19:53:24 |
+---------------------+
1 row in set (0.00 sec)
示例：创建含参存储过程：只有一个IN参数
MariaDB [hellodb]> DELIMITER //
MariaDB [hellodb]> CREATE PROCEDURE selebyid(IN id SMALLINT UNSIGNED)
-> BEGIN
-> SELECT * FROM students WHERE stuid = id;
-> END//
MariaDB [hellodb]> DELIMITER ;
MariaDB [hellodb]> CALL selebyid(15);
+-------+---------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------+-----+--------+---------+-----------+
| 15 | Duan Yu | 19 | M | 4 | NULL |
+-------+---------+-----+--------+---------+-----------+
示例：
MariaDB [hellodb]> DELIMITER //
MariaDB [hellodb]> CREATE PROCEDURE dorepeat(p1 INT)
-> BEGIN
-> SET @x = 0;
-> REPEAT SET @x = @x + 1;UNTIL @x > p1
-> END REPEAT;
-> END//
Query OK, 0 rows affected (0.01 sec)
MariaDB [hellodb]> DELIMITER ;
MariaDB [hellodb]> CALL dorepeat(1000);
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> SELECT @x;
+------+
| @x |
+------+
| 1001 |
+------+
示例：创建含参存储过程：包含IN参数和OUT 参数
MariaDB [hellodb]> DELIMITER //
MariaDB [hellodb]> CREATE PROCEDURE deletebyid(IN id SMALLINT UNSIGNED,OUT num SMALLINT UNSIGNED)
-> BEGIN
-> DELETE FROM students WHERE stuid = id;
-> SELECT row_count() INTO num;
-> END//
MariaDB [hellodb]> CALL deletebyid(2,@Line);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> SELECT @line;
+-------+
| @line |
+-------+
| 1 |
+-------+
存储过程优势：
1 存储过程把经常使用的sql语句或业务逻辑封装起来，预编译保存在数据库中，
当需要是从数据库中直接调用，省去了编译的过程
2 提高了运行速度
3 同时降低网络数据传输量
存储过程与自定义函数的区别：
1 存储过程实现的过程要复杂一些，而函数的针对性较强
2 存储过程可以有多个返回值，而自定义函数只有一个返回值
3 存储过程一般独立的来执行，而函数往往是作为其他sql语句的一部分来使用
流程控制和触发器：----------------------------------------------------------------
存储过程和函数中可以使用流程控制来控制语句的执行
流程控制：
IF: 用来进行条件判断。根据是否满足条件，执行不同语句
CASE： 用来进行条件判断，可实现比IF 语句更复杂的条件判断
LOOP： 重复指定特定的语句，实现一个简单的循环
LEAVE： 用于跳出循环控制
ITERATE: 跳出本次循环，然后直接进行下一次循环
REPEAT: 有条件控制的循环语句，当满足特定条件时，就会跳出循环语句
WHILE： 有条件控制的循环语句
TRIGGER: 触发器
触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发，激活从而实现执行
创建触发器：
CREATE [DEFINER = {user|CURRENT_USER}] TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body
说明：
trigger_name: 触发器的名称
trigger_time: {BEFORE|AFTER},表示在事件之前或之后触发
trigger_event: {INSERT|UPDATE|DELETE},触发的具体事件
tbl_name：该触发器作用在表明
触发器示例：创建触发器，在向学生表INSERT 数据时，学生数增加，DELETE学生时，
学生数减少
CREATE TRIGGER trigger_students_count_insert
-> AFTER INSERT
-> ON students FOR EACH ROW
-> UPDATE student_count SET student_count=student_count+1;
CREATE TRIGGER trigger_student_count_delete
-> AFTER DELETE
-> ON students FOR EACH ROW
-> UPDATE student_count SET student_count=student_count+1;
查看触发器：
1 SHOW TRIGGERS\G
2 查询系统表information_schema.triggers 的方式指定查询条件，查看指定
触发器信息。
use information_schema;
SELECT * FROM triggers WHERE trigger_name='trigger_student_count_delete'\G
删除触发器：
DROP TRIGGER trigger_name;
DROP TRIGGER trigger_students_count_insert;
转载于:https://blog.51cto.com/13683480/2138839