[TOC]
PDF: MySQL必知必会.pdf
PDF: [SQL学习指南]()
这一份笔记以 《MySQL必知必会》为基础，按照个人需求持续补充，目前已经内容已经不局限于书本知识了。
文章链接: https://segmentfault.com/a/11...
基础概念
MySQL 的两种发音：
- My-S-Q-L
sequel['siːkw(ə)l]
数据库中的 schema : 关于数据库和表的布局及特性的信息
有时，schema 用作数据库的同义词。遗憾的是，schema 的含义通常在上下文中并不是很清晰。
主键（primary key）： 一列（或一组列）， 其值能唯一区分表中每一行。
- 任意两行都不具有相同的主键值
- 每个行都必须具有一个主键值（不允许为NULL值）
- 使用多个列作为主键值， 多个列值的组合必须是唯一（但单个列的值可以不唯一）
子句（clause） SQL语句由子句构成，有些子句是必需的，而有的是可选的。
- 一个子句通常由一个关键字和所提供的数据组成。
子句的例子有 SELECT 语句的 FROM 子句，
MariaDB 与 MySQL 版本替代:
- MySQL 5.1 -> MariaDB 5.1, 5.2, 5.3
- MySQL 5.5 -> MariaDB 5.5, 10.0
- MySQL 5.6 -> MariaDB 10.0
- MySQL 5.7 -> MariaDB 10.2
安装
yum 安装 mysql
下载对应的yum仓库: https://dev.mysql.com/downloa...
################################### 确认系统版本 ##############################
# RHEL6
https://dev.mysql.com/get/mysql80-community-release-el6-3.noarch.rpm
# RHEL7 - 该 repo 默认提供 MySQL 8.0, 可以自行修改 repo 配置文件
https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
http://mirrors.ustc.edu.cn/mysql-repo/mysql80-community-release-el7.rpm
https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql80-community-el7/mysql80-community-release-el7-3.noarch.rpm
#############################################################################
######################## 配置源(使用 MySQL 5.7 对应仓库) ############################
# 由于官方的源在国内访问速度过慢,因此此处使用清华大学镜像
cat > /etc/yum.repos.d/mysql-community.repo <<'EOF'
[mysql-connectors-community]
name=MySQL Connectors Community
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-connectors-community-el7-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql
[mysql-tools-community]
name=MySQL Tools Community
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-tools-community-el7-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql
[mysql-5.6-community]
name=MySQL 5.6 Community Server
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.6-community-el7-$basearch/
enabled=0
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql
[mysql-5.7-community]
name=MySQL 5.7 Community Server
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql
[mysql-8.0-community]
name=MySQL 8.0 Community Server
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-8.0-community-el7-$basearch/
enabled=0
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql
EOF
# 确认是否已成功安装
#yum repolist enabled | grep "mysql.*-community.*"
# 由于默认指定最新的版本(MySQL 8.0), 因此需要手动指定我们想安装的版本 (MySQL 5.7)
#yum repolist all | grep mysql
# 安装 yum-utils, 其提供了 yum-config-manager 命令
#yum install -y yum-utils
# 禁止8.0系列的子存储库
#yum-config-manager --disable mysql80-community
# 启用5.7系列的子存储库
#yum-config-manager --enable mysql57-community
# 或在安装时指定仓库 --disablerepo="*" --enablerepo="mysql57-community"
#############################################################################
################################ 安装 ###################################
# 安装MySQL
## 会自动安装依赖: mysql-community-client, mysql-community-common, mysql-community-libs, mysql-community-libs-compat
yum install mysql-community-server
# 启动mysql server
systemctl start mysqld
systemctl enable mysqld
systemctl status mysqld
#######################################################################
################################## 修改 root 密码 #########################
# 查看初始密码
# mysql server初始化时会创建账号 'root'@'localhost', 默认密码存放在错误日志中
grep 'temporary password' /var/log/mysqld.log
# mysql 5.7 的默认密码安全策略对密码复杂度有要求, 若是烦的话可以调低
cat >> /etc/my.cnf <<'EOF'
validate_password_policy=0
EOF
# 修改账号密码
mysql -uroot -p
# 临时调低密码安全策略强度
mysql> set global validate_password_policy=0;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码需包含大小写,数字,字符';
# 创建新账号
mysql> GRANT ALL ON *.* TO yjx@'%' IDENTIFIED BY '复杂密码';
#######################################################################
# 查看当前mysql编码
show variables like 'character%';
# 修改mysql server字符集
# 修改 /etc/my.cnf
# 设置
#
# [mysqld]
# character_set_server=utf8
#
# 设置完后重启mysqld
# 不使用service来关闭mysqld的方法
mysqladmin -uroot -p shutdownmysql_secure_installation是用于设置root密码, 移除匿名用户等MySQL 5.7 请不要运行
mysql_secure_installation, 因为安装时已经默认执行过了.
开启多实例
关闭默认实例
# 取消开机启动
chkconfig mysqld off
# 关闭默认mysqld
service mysqld stop!!! mysql 5.7 以下没有 --initialize-insecure 和 --initialize , 因此初始化数据库必须使用 mysql_install_db --datadir=【数据目录】
mariadb 好像也没有--initialize, 也需要用mysql_install_db来初始化数据库目录
手动管理
分别配置实例配置
mkdir -p /data/mysql_3307
mkdir -p /data/mysql_3308
chown -R mysql:mysql /data/mysql_33*
# 一份简单的配置文件
# basedir 指的是mysqld的安装目录
cat > /data/mysql_3307/my.cnf <<\EOF
[mysqld]
user = mysql
port = 3307
socket = /data/mysql_3307/mysqld.sock
pid-file = /data/mysql_3307/mysqld.pid
datadir = /data/mysql_3307/data
basedir = /usr
bind-address = 0.0.0.0
character_set_server = utf8
symbolic-links = 0
log_error = /data/mysql_3307/error.log
slow_query_log = 1
slow_query_log_file = /data/mysql_3307/slow.log
long_query_time = 2
EOF
# 复制配置文件
sed "s/3307/3308/g" /data/mysql_3307/my.cnf > /data/mysql_3308/my.cnf
# 初始化数据库
# --initialize-insecure 生成无密码的root账号
# --initialize 生成带随机密码的root账号
# 注意参数顺序, 必须先指定 --defaults-file=配置文件, 否则会报错
mysqld --defaults-file=/data/mysql_3307/my.cnf --initialize-insecure
mysqld --defaults-file=/data/mysql_3308/my.cnf --initialize-insecure
# 启动实例
mysqld_safe --defaults-file=/data/mysql_3307/my.cnf &
mysqld_safe --defaults-file=/data/mysql_3308/my.cnf &
# 关闭实例
mysqladmin -S /data/mysql_3307/mysqld.sock shutdown
mysqladmin -S /data/mysql_3308/mysqld.sock shutdown
# 连接实例
mysql -uroot -p -S /data/mysql_3307/mysqld.sock
# # 注意, 默认只有 root@localhost, 需自行创建 root@'%' 账号才能远程登录
mysql -uroot -h192.168.190.100 -P 3308 mysqld_multi 管理(推荐)
# 创建目录
mkdir -p /data/mysql_3307
mkdir -p /data/mysql_3308
chown -R mysql:mysql /data/mysql_33*
# 初始化数据库目录
## 读取配置文件初始化
###mysqld --defaults-file=/data/mysql_3309/my.cnf --initialize-insecure
###mysqld --defaults-file=/data/mysql_3310/my.cnf --initialize-insecure
## [推荐]不读取配置文件, 直接指定目录
mysqld -u mysql --basedir=/usr --datadir=/data/mysql_3309 --initialize-insecure
mysqld -u mysql --basedir=/usr --datadir=/data/mysql_3310 --initialize-insecure
## mysql 5.7 以下或 mariadb 应使用 mysql_install_db 来初始化
#mysql_install_db --user=mysql --datadir=/data/mysql_3309
#mysql_install_db --user=mysql --datadir=/data/mysql_3310
# 直接修改 /etc/my.cnf 或新创建一份multi配置 /data/multi.cnf
# 若是新创建multi配置, 则执行mysql_multi时需指定该配置文件
cat > /etc/multi.cnf <<EOF
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
[mysqld3309]
user = mysql
port = 3309
server-id = 3309
socket = /data/mysql_3309/mysqld.sock
pid-file = /data/mysql_3309/mysqld.pid
datadir = /data/mysql_3309
basedir = /usr
log_error = /data/mysql_3309/error.log
log_bin = /data/mysql/mysql-bin
slow_query_log_file = /data/mysql_3309/slow.log
slow_query_log = 1
long_query_time = 2
bind-address = 0.0.0.0
character_set_server = utf8
symbolic-links = 0
max_allowed_packet = 100m
EOF
[mysqld3310]
user = mysql
port = 3310
server-id = 3310
socket = /data/mysql_3310/mysqld.sock
pid-file = /data/mysql_3310/mysqld.pid
datadir = /data/mysql_3310
basedir = /usr
log_error = /data/mysql_3310/error.log
log_bin = /data/mysql/mysql-bin
slow_query_log_file = /data/mysql_3310/slow.log
slow_query_log = 1
bind-address = 0.0.0.0
character_set_server = utf8
symbolic-links = 0
long_query_time = 2
max_allowed_packet = 100m
EOF
# 默认会读取 /etc/my.cnf 文件, 因此若不使用该配置则应通过 --defaults-file 指定配置
## 启动实例 3309 和 3310, 支持使用连字符语法或用逗号分隔
mysqld_multi --defaults-file=/etc/multi.cnf start 3309,3310
## 关闭实例
mysqld_multi --defaults-file=/etc/multi.cnf stop 3309-3310
## 查看实例状态
mysqld_multi --defaults-file=/etc/multi.cnf report 3309-3310!!! mysqld_multi 不支持 !include 或 !includedireg. 采用 heartbeat + drbd + mysql 实现mysql高可用双机热备方案
未实践
MariaDB 和 MySQL 共存
新增 MariaDB
这里讨论的是在已有 MySQL 前提下通过编译安装 MariaDB
参考:
# 1. 下载二进制包
wget http://nyc2.mirrors.digitalocean.com/mariadb//mariadb-10.2.31/bintar-linux-x86_64/mariadb-10.2.31-linux-x86_64.tar.gz
tar -xvf mariadb-10.2.31-linux-x86_64.tar.gz
mv mariadb-10.2.31-linux-x86_64 /usr/local/mariadb-10.2.31
ln -s /usr/local/mariadb-10.2.31 /usr/local/mariadb
# 2. 复制 my.cnf 配置文件
cp /usr/local/mariadb/support-files/my-innodb-heavy-4G.cnf /data/mariadb/my.cnf
## 手动修改其他配置:
### port = 3307
### socket = /data/mariadb/mariadb.sock
### pid-file = /data/mariadb/mariadb.pid
### datadir = /data/mariadb
### basedir = /usr/local/mariadb
### bind-address = 0.0.0.0
### log_error = /data/mariadb/error.log
### slow_query_log = 1
### slow_query_log_file = /data/mariadb/slow.log
### default-storage-engine = InnoDB
vim /data/mariadb/my.cnf
# 3. 复制 service 配置文件
cp /usr/local/mariadb/support-files/mysql.server /etc/init.d/mariadb
## 手动修改文件内容
## # Provides: mariadb
## basedir="/usr/local/mariadb"
## datadir="/data/mariadb"
## lock_file_path="$lockdir/mariadb"
## mysqld_pid_file_path="/data/mariadb/mariadb.pid"
## $bindir/mysqld_safe --defaults-file="/data/mariadb/my.cnf" --datadir="$datadir" --pid-file="$mysqld_pid_file_path" "$@" &
## if $bindir/mysqladmin --defaults-file="/data/mariadb/my.cnf" ping >/dev/null 2>&1; then
vim /etc/init.d/mariadb
chmod +x /etc/init.d/mariadb
# 4. 初始化数据库数据
/usr/local/mariadb/scripts/mysql_install_db --defaults-file="/data/mariadb/my.cnf"
# 5. 启动 mariadb
service mariadb start
# 6. 设置开机自启动
chkconfig mariadb on
# 7. Test
mysql -e "SELECT VERSION();" --socket=/data/mariadb/mariadb.sock这里依旧使用 mysql 用户来运行 mariadb, 因此并未创建新的 mariadb 用户.
此处假设 mariadb 相关数据保存在
/data/mariadb在实际操作中, 由于我并未将
my.cnf放在和数据库数据同一个目录, 因此启动时一直出现读取旧的配置文件/etc/my.cnf的问题, 解决方式是修改了/etc/init.d/mariadb下的部分内容# 在此处指定解析的 my.cnf 文件, 否则会默认解析 /etc/my.cnf 文件读取其中的 datadir 配置...蛋疼 parse_server_arguments `$print_defaults -c /data/mariadb/my.cnf $extra_args --mysqld mysql.server`若要安装其他版本的 MariaDB, 则需自行到 https://downloads.mariadb.org... 下载(注意是下载二进制包, 而不是源码包)
主从复制
目的:
- 实时备份
- 读写分离, 主写, 从读
eg. MySQL 主从复制
简单原理
- Master的IO线程将操作记录到二进制日志(Binary log)中
- Slave的IO线程会同步拉取Master的二进制日志并写入本地中继日志(Relay log)
- Slave的SQL线程会从中继日志中读取操作并在当前实例上重放操作.
配置及操作
Master 配置文件
log_bin = /data/3306/mysql-bin
server-id = 1
#expire_logs_days = 10
#max_binlog_size = 100M必须打开 master 端的 Binary Log
Slave 配置文件
read-only = 1
log_bin = /data/3307/mysql-bin
server-id = 2
# 可选, 指定中继日志的位置和命名
relay_log = /data/3307/relay.log
# 允许备库将其重放的事件也记录到自身的二进制日志中
log_slave_updates = 11. 确认Master 开启了二进制日志
mysql> SHOW MASTER STATUS;Tip. 锁表
FLUSH TABLE WITH READ LOCK; UNLOCK TABLES;
2. Master 创建专门用于主从复制的账号
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'rep'@'192.168.100.%' IDENTIFIED BY '123456';复制账号在主库上只需要REPLICATION SLAVE权限,1.用来监控和管理复制的账号需要REPLICATION CLIENT 权限，并且针对这两种目的使用同一个账号更加容易(而不是为了两个目的各创建一个账号)。
2.如果在主库上建立了账号，然后从主库将数据克隆到备库上时，备库也就设置好了－变成主库所需要的配置。这样后续有需要可以方便的交换主备库角色。
3. 主-从 数据保持一致
# 主库导出数据快照
# 若表全都使用InnoDB引擎, 则可使用 --single-transaction 来代替 --lock-all-tables
# --master-data=1 导出sql中会包含CHANGE MASTER语句(包含 binlog 的文件名及 pos)
## eg. CHANGE MASTER TO MASTER_LOG_FILE='bin.000003', MASTER_LOG_POS=25239;
# --master-data=2 导出CHANGE MASTER语句,但是会被注释(仅在日常备份时导出使用)
mysqldump -uroot -p -S /data/3306/mysql.sock -A -F --master-data=1 --single-transaction > master.sql
# 从库导入
mysql -uroot -p -S /data/3307/mysql.sock < master.sql此处假设主数据库已经在使用, 而从数据库是新的, 因此需要先保持两边数据一致
4. Slave 更改从库的连接参数
# 尝试在此处不设置 MASTER_LOG_FILE,MASTER_LOG_POS, 结果后面 START SLAVE 后一直出错
# 此处的 MASTER_LOG_FILE,MASTER_LOG_POS 可以在日志中查看
mysql> CHANGE MASTER TO MASTER_HOST='192.168.190.100',MASTER_PORT=3309,MASTER_USER='rep',MASTER_PASSWORD='123456',MASTER_LOG_FILE='bin.000003', MASTER_LOG_POS=25239;
# 确认一下配置文件正确
cat /data/3307/data/master.info
# 从库连接主库
mysql> START SLAVE;
# 确认连接正常
mysql> SHOW SLAVE STATUS\G;管理
-- 查看master的状态, 尤其是当前的日志及位置
show master status;
-- 查看slave的状态.
show slave status;
-- 重置slave状态，用于删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件.会忘记 主从关系，它删除master.info文件和relay-log.info 文件
reset slave;
-- 启动slave 状态(开始监听msater的变化)
start slave;
-- 暂停slave状态;
stop slave;
-- 跳过导致复制终止的n个事件，仅在slave线程没运行的状况下使用
set global sql_slave_skip_counter = n; 李玥老师: 如果你配置了多个从库，推荐你使用“HAProxy+Keepalived”这对儿经典的组合，来给所有的从节点做一个高可用负载均衡方案，既可以避免某个从节点宕机导致业务可用率降低，也方便你后续随时扩容从库的实例数量。因为 HAProxy 可以做 L4 层代理，也就是说它转发的是 TCP 请求，所以用“HAProxy+Keepalived”代理 MySQL 请求，在部署和配置上也没什么特殊的地方，正常配置和部署就可以了。 -- 后端存储实战课
MySQL 关键参数配置
以下参数中, 部分参数只对InnoDB引擎有效
| 参数名 | 含义 | 建议 |
|---|---|---|
max_connections | 最大客户端连接数 | 默认好像是 151 |
innodb_buffer_pool_size | Innodb存储引擎缓存池大小 | 建议设置为物理内存的 80% 左右<br/>默认是128MB左右 |
innodb_file_per_table | Innodb表包含两部分: 表结构定义(.frm文件)和数据, 若该参数值为0, 则表的数据就存在共享表空间 , 若只为1, 则单独存放在一个.ibd的文件中.若放在共享表空间, drop 表时, 空间是不会回收的. | 设为 1, 表示每个表使用独立表空间(.ibd文件), 方便回收空间.MySQL 5.6.6 开始默认为1 |
innodb_log_file_size | 事务日志(Redo Log)单个大小(文件 ib_logfile* | 总的日志大小足以容纳1个小时的量 默认是 5MB 左右 |
innodb_log_files_in_group | 事务日志数量 | 默认是 2 |
innodb_flush_logs_at_trx_commit | 事务提交时写日志的方式 0: 每秒将日志持久化到磁盘. 数据库崩溃时丢失最多1秒数据 1: 默认, 每次事务提交都将日志持久化到磁盘, 最安全, 性能最一般. 2: 每次事务提交都写入磁盘(指磁盘缓冲区), 具体何时持久化到磁盘则由操作系统控制. 系统崩溃时丢失数据 | 不推荐设为 0. 对性能要求较高可以设置为 2. 默认好像是 1 |
sync_binlog | MySQL 控制写入 BinLog 的方式 0 : 每次事务提交写入磁盘缓冲区, 由操作系统控制何时持久化 N: 每进行N个事务提交后持久化到磁盘, 当N=1时最安全但性能最差 | 5.7.7及以后默认是1, 之前默认是0. |
log_bin | 设置 bin_log 文件存储路径 | 默认好像是不写 bin log. |
mysql 自带 mysqlslap 压测工具, 可以自行测试, 个人未使用过.
计算合适的 Redo Log 大小
调整Redo Log大小一般是通过修改 innodb_log_file_size 配置.
在业务高峰期, 计算出1分钟写入的redo log量
# 只显示结果中 Log 相关 > pager grep Log; # 查看日志位置, sleep > show engine innodb status\G; select sleep(60); show engine innodb status\G; # 取消结果过滤 > nopager;通过查看两次的
Log sequence number值, 计算出差值, 单位是字节.- 评估1个小时的 redo log 量
将上述值乘以 60 得到合理的 Redo Log 大小. 因此
innodb_log_file_size推荐设置成 估算的Redo Log 大小 / 日志文件数(innodb_log_files_in_group) - 正常关闭 mysql
- 修改配置中
innodb_log_file_size值, 并将数据目录下的所有ib_logfile*文件move走(先不删, 防止出问题无法启动) - 启动 mysql, 确认没问题后就可以删除
ib_logfile*
备注: 有看到说 mysql5.6 版本及以后无需手动删除 ib_logfile* 文件.如果Redo Log太小, 会导致频繁刷脏页??
太大会导致故障恢复时恢复时间太长(甚至长到不可接受的程度)
pager 可以理解为利用 linux 的管道来处理结果, 比如 pager less 可以方便浏览大量结果集, 或者是计算结果校验和(比较两个查询结果是否完全一致) pager md5sum
示例
# 仅查看锁状态那一行的数据
> pager grep "lock(s)";
> show engine innodb status;
> nopaper;基本使用
USE 数据库名; -- 选择数据库
SHOW DATABASES; -- 查看数据库列表
SHOW TABLES; -- 查看当前数据库内的表的列表
SHOW COLUMNS FROM `表名`; -- 查看表结构
SHOW STATUS; -- 用于显示广泛的服务器状态信息
SHOW CREATE DATABASE `数据库名`; -- 显示创建特定数据库的MySQL语句
SHOW CREATE TABLE `表名`; -- 显示创建表的MySQL语句；
SHOW GRANTS; -- 显示授权指定用户的安全权限, 默认是当前用户
SHOW GRANTS FOR 用户@"..." -- eg. root@'%' 或 root@localhost 或 root@0.0.0.0
SHOW ERRORS; -- 用来显示服务器错误消息
SHOW WARNINGS; -- 用来显示服务器警告消息DESCRIBE 表名等同于
SHOW COLUMNS FROM 表名, MySQL独有STATUS快速查看当前实例状态, eg.
-------------- mysql Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using EditLine wrapper Connection id: 28 Current database: mysql_learn Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.22-0ubuntu18.04.1 (Ubuntu) Protocol version: 10 Connection: 127.0.0.1 via TCP/IP Insert id: 114 Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 7 days 23 hours 29 min 13 sec Threads: 6 Questions: 817 Slow queries: 0 Opens: 205 Flush tables: 1 Open tables: 150 Queries per second avg: 0.001 --------------
数据类型
数值数据类型
<u>有符号或无符号</u>所有数值数据类型（除 BIT 和 BOOLEAN 外）都可以有符号或无符号。有符号数值列可以存储正或负的数值，无符号数值列只能存储正数。默认情况为有符号，但如果你知道自己不需要存储负值，可以使用 UNSIGNED 关键字，这样做将允许你存储两倍大小的值。
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 字节 | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 字节 | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 字节 | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 字节 | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 字节 | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 字节 | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 浮点数值 |
| DOUBLE | 8 字节 | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
| DECIMAL | DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。
int(m) 里的m是表示 SELECT 查询结果集中的显示宽度，并不影响实际的取值范围.
FLOAT 类型
float(m,d)DOUBLE 类型
double(m,d)DECIMAL 类型
- 精确值
decimal(m,d)m<=65, d<=30, m是总位数, d是小数位数
字符串数据类型
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255字节 | 定长字符串 |
| VARCHAR | 0-65535 字节 | 变长字符串 |
| TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255字节 | 短文本字符串 |
| BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
| TEXT | 0-65 535字节 | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
CHAR 类型
char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以 char 类型存储的字符串末尾不能有空格- 效率比 VARCHAR 高点
VARCHAR 类型
- 长度设置, 在MySQL 5之后是按<u>字符数</u>, 而不是字节数.
varchar(20)可以存储20个<u>字符</u>- varchar 头部会占用 1个(n<=255)或2个字节(n>255)保存字符串长度, 若值可设为 null, 则还需要一个1字节记录null, 因此保存utf8编码的字符串最多可存 (65535 - 3)/3 = 21844
- 若是utf8编码
- 效率比 TEXT 高
TEXT 类型
- 创建索引时要指定前多少个字符
- 不能有默认值
- text 头部会占用 2个字节来保存长度
日期和时间类型
| 类型 | 大小 (字节) | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性
update_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP- 保存毫秒数(类似php 的
microtime(true))- timestamp列默认not null。没有显式指定nullable,那么default null不合法
- mysql不会给timestamp设置默认值，除非显式设置default约束或者可空null。特例：mysql会给表第一个timestamp类型的字段同时添加
default current_timestamp和on update timestamp- 其他情况均会引起不合法报错
- ↑ 总结: 最好手动设置 NULL 以免出错
日期函数:
CURRENT_TIMESTAMP,CURRENT_TIMESTAMP()是NOW()的同义词SYSDATE()获取的是函数执行时的时间,NOW()获取的是执行语句时的<u>开始时间</u>.CURDATE()今日
二进制数据类型
检索数据
SELECT 语句
子句的书写顺序很重要:
SELECT ... FROM ... JOIN ... ON ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...
执行顺序:
FROM(包含JOIN) > WHERE > GROUP BY > 聚集函数字段计算 > HAVING > SELECT 的字段 > ORDER BY > LIMIT
举例:
SELECT player.team_id, count(*) as num -- 顺序5
FROM player JOIN team ON player.team_id = team.team_id -- 顺序1
WHERE height > 1.80 -- 顺序2
GROUP BY player.team_id -- 顺序3
HAVING num > 2 -- 顺序4
ORDER BY num DESC -- 顺序6
LIMIT 2; -- 顺序7基本检索
SELECT `列名1`,`列名2` FROM `表名`; -- 检索指定列
SELECT * FROM `表名`; -- 检索所有列DISTINCT关键字: 只返回不同的值
SELECT DISTINCT `列名1` FROM `表名`; -- 只返回唯一的 `列名1`行
SELECT DISTINCT `列名1`,`列名2` FROM `表名`; -- DISTINCT应用于所有的列, 返回 (`列名1`, `列名2`) 不同的行LIMIT关键字: 限制结果
SELECT * FROM `表名` LIMIT <limit>; -- 限制返回最多 <limit> 条, 等同 0,<limit>
SELECT * FROM `表名` LIMIT <offset>,<limit>; -- 略过前 <offset> 条记录, 返回最多 <limit> 条
SELECT * FROM `表名` LIMIT <limit> OFFSET <offset>; -- MySQL 5 语法糖完全限定名
SELECT `表名`.`列名` FROM `数据库名`.`表名`; -- 有一些情形需要完全限定名ORDER 子句
SELECT * FROM `表名` ORDER BY `列名` <dir>; -- <dir> 默认是 ASC, 可指定 DESC.
SELECT * FROM `表名` ORDER BY `列名1` <dir1>,`列名2` <dir2>; -- 仅在`列名1`等值时才按`列名2`排序排序方式可选:
- ASC 升序(默认), ASCENDING
- DESC 降序
- ORDER BY 字句使用的排序列不一定是显示的列, 这是允许的.
- ORDER BY
- 对文本数据排序时, 大小写的排序取决于数据库如何设置
COLLATE
WHERE 子句
| 操作符 | 说明 |
|---|---|
= | 等于 |
<> | 不等于 |
!= | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
BETWEEN ... AND ... | 在指定的两个值之间 |
... IS NULL | 没有值 |
... IS NOT NULL | 非空, 有值 |
| IN (…) | 在元组 |
| NOT IN (...) | 不在元组 |
- 一个列不包含值时, 称其为空值NULL
AND, OR 操作符
计算次序: AND 运算符优先级更高, 在处理OR操作符前会先处理AND操作符, 举例:
SELECT prod_name,prod_price,vend_id FROM products WHERE vend_id=1002 OR vend_id=1003 AND prod_price>=10;
-- 等价于
SELECT prod_name,prod_price,vend_id FROM products WHERE (vend_id=1002) OR (vend_id=1003 AND prod_price>=10);IN操作符
SELECT * FROM `表名` WHERE `列名` IN (值1, 值2, ..., 值N);IN 功能等同于 OR, 那么为什么用 IN:
- 语法清晰直观
- 执行更快
- <u>可以包含其他SELECT 语句, 得能够更动态地建立 WHERE 子句</u>
在使用 IN 或 EXISTS 时, 谨记一个原则: 小表驱动大表：即小的数据集驱动大的数据集。
举例, 有两个表
- 表 A 是小表, cc 字段有索引
- 表 B 是大表, cc 字段有索引
-- 用到了 B 表上 cc 索引
SELECT * FROM B WHERE cc IN (SELECT cc FROM A);
-- 用到了 B 表上的 cc 索引
SELECT * FROM A WHERE EXISTS(SELECT 1 FROM B WHERE B.cc = A.cc);NOT操作符
否定它之后所跟的任何条件
... WHERE `列名` NOT IN (值1, ..., 值N);
... WHERE `列名` IS NOT NULL;
... WHERE `列名` NOT BETWEEN 1 AND 10;
... WHERE `列名` NOT EXISTS (...);NOT 在复杂的WHERE字句中很有用.
例如，在与 IN 操作符联合使用时， NOT 使找出与条件列表不匹配的行非常简单。MySQL支持使用 NOT 对 IN 、 BETWEEN 和
EXISTS子句取反，这与多数其他 DBMS允许使用 NOT 对各种条件
取反有很大的差别。
LIKE操作符
通配符(wildcard)
| 通配符 | 含义 |
|---|---|
% | 任何字符出现任意次数(0,1,N), 注意不匹配 NULL |
_ | 匹配单个字符(1) |
like 匹配完整的列.
通配符置于搜索模式开始处, 不会使用索引.
注意NULL 虽然似乎 % 通配符可以匹配任何东西，但有一个例外，即 NULL 。即使是 WHERE prod_name LIKE '%' 也不能匹配用值 NULL 作为产品名的行。
SELECT * FROM `表名` WHRER `列名` LIKE `a%d`;
SELECT * FROM `表名` WHRER `列名` LIKE `a_cd`;REGEXP 正则表达式
MySQL仅支持多数正则表达式实现的一个很小的子集。
eg. 不支持\d,\s等
SELECT * FROM `表名` WHERE `列名` REGEXP '^[0-9]'; -- 匹配数字开头的, 默认不区分大小写
SELECT * FROM `表名` WHERE `列名` REGEXP BINARY "正则表达式"; -- 区分大小写多数正则表达式实现使用单个反斜杠转义特殊字符，以便能使用这些字符本身。但MySQL要求两个反斜杠（MySQL自己解释一个，正则表达式库解释另一个）。
转义.时, 需使用\\.而非\.
计算字段
计算字段并不实际存在于数据库表中。计算字段是运行时在 SELECT 语句内创建的。
字段（field） 基本上与列（column）的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常用在计算字段的连接上。
AS 别名
别名（alias）是一个字段或值的替换名。
SELECT `列名` AS `别名` FROM `表名`;别名的其他常见用途:
- 在实际的表列名包含不符合规定的字符（如空格）时重新命名它
- 在原来的名字含混或容易误解时扩充它，等等。
算数运算
圆括号可用来区分优先顺序。
控制表达式
CASE WHEN 表达式
update table
set 字段1=case
when 条件1 then 值1
when 条件2 then 值2
else 值3
end
where …… select 字段1, 字段2,
case 字段3
when 值1 then 新值1
when 值2 then 新值2
else 新值3
end as 重新命名字段3的名字
from table
where ……
order by ……IF(cond, trueExpr, falseExpr) 表达式
select *, if(sex=1,'男','女') as ssex from user;IF ELSE 流程控制
IF search_condition THEN
statement_list
[ELSEIF search_condition THEN]
statement_list ...
[ELSE
statement_list]
END IF 该语句更多的是应用过在编写存储过程
IFNULL(condExpr, trueExpr) 表达式
若 condExpr 为 null, 则返回 trueExpr, 否则返回该 condExpr.
-- 查询结果: 10
SELECT IFNULL(NULL, 10);
-- 如果 price 字段有 null, 会导致最终结果值为 null
-- 因此需使用 IFNULL 来处理
SELECT SUM(IFNULL(price, 0)) from order;函数
函数没有SQL的可移植性强, 几乎每种主要的DBMS的实现都支持其他实现不支持的函数，而且有时差异还很大。
字符串处理
concat() 字符串拼接
SELECT concat(`列名1`, '(', `列名2`, ')') FROM `表名`; -- 组成: `列名1`(`列名2`)MySQL的不同之处 多数DBMS使用 + 或 || 来实现拼接，MySQL则使用 Concat() 函数来实现。当把SQL语句转换成MySQL语句时一定要把这个区别铭记在心
LOCATE(substr,str)返回子串 substr 在字符串 str 中第一次出现的位置。如果子串 substr 在 str 中不存在，返回值为 0：
LOCATE(substr,str,pos)多字节安全
返回子串 substr 在字符串 str 中的第 pos 位置后第一次出现的位置。如果 substr 不在 str 中返回 0
substr(str,pos[, len])等同于
substringpos参数表示从该位置(包含)开始截取substring_index返回从字符串str分隔符delim中的计数发生前的子字符串。 如果计数是正的，则返回一切到最终定界符(从左边算起)的左侧。如果count为负，则从右边开始截取
concat_ws(separator, str1, str2, ...)使用指定分隔符拼接参数, 忽略NULL
group_concat(...)函数返回一个字符串结果，该结果由分组中的值连接组合而成。
find_in_set(str, strlist)返回子串在字符串(以
,分隔)中的位置.典型应用:
查找一组 id 对应的记录, 需按照数组中 id 顺序来返回.
// id 数组: [2, 14, 7, 13] SELECT * FROM `表名` WHERE `id` in (2,14,7,14) ORDER BY FIND_IN_SET(`id`, "2,14,7,13");上述的的逗号分隔的字符串通常是在外部由调用者构建的.
!!! 注意 MySQL字符串位置是从1开始
日期和时间
| 函数 | 说明 | 示例 |
|---|---|---|
DATE_SUB() | 日期减少,类似 DATE_ADD | date_sub('2016-08-01',interval 1 day) |
UNIX_TIMESTAMP(date)
日期或日期字符串 -> 时间戳
-- 时间戳: 1610533706
select unix_timestamp(Now());
-- 1451664000
select unix_timestamp('2016-01-02');FROM_UNIXTIME(timestamp[, format])
时间戳转日期
-- 日期: 2021-01-13 18:28:26
select from_unixtime(1610533706);
-- 日期: 2021-01-13
select from_unixtime(1610533706, "%Y-%m-%d");DATE_FORMAT(date,format)
日期转字符串.
format 格式
%M月名字(January……December)%W星期名字(Sunday……Saturday)%D有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）%Y年, 数字, 4 位%y年, 数字, 2 位%a缩写的星期名字(Sun……Sat)%d月份中的天数, 数字(00……31)%e月份中的天数, 数字(0……31)%m月, 数字(01……12)%c月, 数字(1……12)%b缩写的月份名字(Jan……Dec)%j一年中的天数(001……366)%H小时(00……23)%k小时(0……23)%h小时(01……12)%I小时(01……12)%l小时(1……12)%i分钟, 数字(00……59)%r时间,12 小时(hh:mm:ss [AP]M)%T时间,24 小时(hh:mm:ss)%S秒(00……59)%s秒(00……59)%pAM或PM%w一个星期中的天数(0=Sunday ……6=Saturday ）%U星期(0……52), 这里星期天是星期的第一天%u星期(0……52), 这里星期一是星期的第一天%%一个文字“%”。
-- 日期: 2021-01-13
SELECT DATE_FORMAT(NOW(), "%Y-%m-%d");STR_TO_DATE(str, format)
字符串转日期
select str_to_date('2016-01-02', '%Y-%m-%d %H');数值处理
聚集函数
标准偏差 MySQL还支持一系列的标准偏差聚集函数
AVG(),MAX(),MIN(),SUM()函数忽略列值为 NULL 的行COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（ NULL ）还是非空值COUNT(column)对特定列中具有值的行进行计数，忽略NULL 值-- 若 score < 90, 则返回 null, 此时 count 不会计数 select count(`score`>=90 or null) from scores; select count(if(`score` >= 90, `score`, 0)>=90 or null) from scores;
上述聚集函数可配合 DISTINCT 来使用
SELECT COUNT(DISTINCT `列名`) FROM `表名`;
SELECT AVG(DISTINCT `列名`) FROM `表名`;
SELECT SUM(DISTINCT `列名`) FROM `表名`;COUNT 性能问题
对于MyISAM引擎, 每张表保存了一个总行数的字段, 统计总行数直接返回该字段值即可.
对于InnoDB引擎, 由于MVCC(多版本并发控制)的存在, 因此每次都需要全表扫描, 这里只讨论InnoDB引擎需要注意的点.
对于统计表的总行数, 从效率角度看从高到低顺序如下:
COUNT(*)优化器做了语义优化: 取行数
Innodb遍历整张表, 但不取值, 直接计数.
COUNT(1)Innodb遍历整张表, 但不取值,返回空行, server层对于返回的每一行自行放入一个 1, 并按行累加.
COUNT(主键id)Innodb会遍历整张表, 取出每一行的主键id返回给server层, server层拿到主键id后按行累加.
COUNT(字段)Innodb遍历整张表, 取出每一行的该字段返回给server层, server层拿到该字段后, 此时分2种情况:
- 若该字段允许 null, 则需要先判断一下
- 若该字段 not null, 则直接按行累加
COUNT(字段) < COUNT(主键id) < COUNT(1) ≈ COUNT(*)
对于上述的 COUNT, Innodb 会优先考虑使用最小的索引树(前提是要有COUNT所需的字段), 这样扫描的页比较少.
字段允许为null的二级索引, 它的主键是存在的, 因此也可以用于统计总行数.
优化考虑:
- 尽量使用
COUNT(*) - 对于较为频繁的
COUNT操作, 可以考虑找一个字段长度最小的对其建立索引, 以提高COUNT效率. - 对于非常频繁的
COUNT操作, 同样记录数多, 可以考虑使用缓存系统来保存计数(eg. Redis), 这种方式会存在一定程度上的不一致性(包括计数丢失和计数不精确), 如果能接受的话这是一种很好的方案,如果不能接受, 则可以考虑下面的这个方案. 对于非常频繁的
COUNT操作, 同样记录数多, 也可以考虑使用数据库来保存计数, 通过建立一个单独的计数表, 每次insert或delete的同时在同一个事物中对该计数的记录做修改. 但有一些点要注意:- 考虑到锁对性能的影响, 因此修改计数记录的操作应放在事务的最后面来操作.
- 同样考虑到锁对性能的影响, 可以将计数记录扩展到N条, 每次随机选取一条来操作. 最后统计总行数时, 再选取所有记录进行
SUM操作.
类型转换函数
MySQL 提供了以下数据类型转换函数
- CAST()函数
CAST(value as type)就是CAST(xxx AS 类型) - CONCERT()函数
CONVERT(value, type)就是CONVERT(xxx,类型)
支持的类型如下:
- 二进制: BINARY
- 字符型，可带参数 : CHAR()
- 日期 : DATE
- 时间: TIME
- 日期时间型 : DATETIME
- 浮点数 : DECIMAL
- 整数 : SIGNED
- 无符号整数 : UNSIGNED
其他函数-待整理
CHAR_LENGTH(str)
返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。
CONCAT(str1,str2,...)
字符串拼接
如有任何一个参数为NULL ，则返回值为 NULL。
CONCAT_WS(separator,str1,str2,...)
字符串拼接（自定义连接符）
CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。
CONV(N,from_base,to_base)
进制转换
例如：
SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示
FORMAT(X,D)
将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。
例如：
SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
INSERT(str,pos,len,newstr)
在str的指定位置插入字符串
pos：要替换位置其实位置
len：替换的长度
newstr：新字符串
特别的：
如果pos超过原字符串长度，则返回原字符串
如果len超过原字符串长度，则由新字符串完全替换
INSTR(str,substr)
返回字符串 str 中子字符串的第一个出现位置。
LEFT(str,len)
返回字符串str 从开始的len位置的子序列字符。
LOWER(str)
变小写
UPPER(str)
变大写
LTRIM(str)
返回字符串 str ，其引导空格字符被删除。
RTRIM(str)
返回字符串 str ，结尾空格字符被删去。
SUBSTRING(str,pos,len)
获取字符串子序列
LOCATE(substr,str,pos)
获取子序列索引位置
REPEAT(str,count)
返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。
若 count <= 0,则返回一个空字符串。
若str 或 count 为 NULL，则返回 NULL 。
REPLACE(str,from_str,to_str)
返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
REVERSE(str)
返回字符串 str ，顺序和字符顺序相反。
RIGHT(str,len)
从字符串str 开始，返回从后边开始len个字符组成的子序列
SPACE(N)
返回一个由N空格组成的字符串。
SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。
mysql> SELECT SUBSTRING('Quadratically',5);
-> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
-> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
-> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
-> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
-> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
-> 'ki'
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
返回字符串 str ， 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。
mysql> SELECT TRIM(' bar ');
-> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
-> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
-> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
-> 'barx'分组
GROUP BY ... HAVING ...
创建分组
聚集 配合 分组 GROUP BY 子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集。
使用 GROUP BY 的重要规定
- ??? GROUP BY 子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
- ??? 如果在 GROUP BY 子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
- <u>GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在 SELECT 中使用表达式，则必须在GROUP BY 子句中指定相同的表达式。不能使用别名。</u>
- 除聚集计算语句外， SELECT 语句中的每个列都必须在 GROUP BY 子句中给出。
- 如果分组列中具有 NULL 值，则 NULL 将作为一个分组返回。如果列中有多行 NULL 值，它们将分为一组。
- GROUP BY 子句必须出现在 WHERE 子句之后， ORDER BY 子句之前。
??
想要SELECT 非分组字段
在SQL92以及更早的SQL标准中不允许查询除了GROUP BY之外的非聚合的列 .
https://dev.mysql.com/doc/ref...
在MYSQL的5.7.5以及以上版本默认的设置是：ONLY_FULL_GROUP_BY ，该设置则约束查询必须是聚合的列。但是在其版本之前则允许查询非聚合的列。
如果在开启 ONLY_FULL_GROUP_BY 但是还想查询非聚合的列可以使用ANY_VALUE(非聚合列)进行查询，ANY_VALUE参考文档。还有一种情况开启ONLY_FULL_GROUP_BY时，如果GROUP BY是主键或者 unique NOT NULL 时是可以查询非聚合的列的，原因是此时分组的key是主键，则每一个分组只有一条数据，因此是可以进行查询非聚合的列的。最后对于高于5.7.5的版本如果想查询非聚合的列可以关闭ONLY_FULL_GROUP_BY 属性，即：
set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';这部分参考
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;过滤分组
HAVING 过滤的是分组
WHERE 过滤的是行HAVING可以使用别名
HAVING 和 WHERE 的差别
这里有另一种理解方法， WHERE 在数据分组前进行过滤， HAVING 在数据分组后进行过滤。这是一个重要的区别， WHERE 排除的行不包括在分组中。这可能会改变计算值，从而影响 HAVING 子句中基于这些值过滤掉的分组。
子查询
根据子查询执行的次数, 对子查询进行分类:
- 非关联子查询
查询不依赖外层(即与主查询无关), 该子查询只需执行一次, 得到的数据结果可以作为外层查询的条件直接使用.
- 关联子查询
查询时依赖外层(用到外层表数据, 与主查询相关), 因此每次外层查询都要根据外层查询结果再进行子查询, 该子查询需要执行多次.
在实际使用时由于性能的限制，不能嵌套太多的子查询。
eg. 返回订购产品 TNT2 的客户列表
select * from customers
where cust_id in (select distinct cust_id
from orders
where order_num in (select order_num
from orderitems
where prod_id="TNT2")
);子查询的关键字
存在性子查询
- EXISTS
判断条件是否满足, 满足为True, 否则为False
集合比较子查询
- IN
判断是否在集合中
ANY
必须与比较操作符一起使用, 与子查询中<u>任意值</u>比较
SELECT * FROM A WHERE A.cc > ANY (SELECT cc FROM B);- SOME
是ANY的别名, 等价于 ANY, 一般常用 ANY
ALL
必须与比较操作符一起使用, 与子查询中<u>所有值</u>比较
SELECT * FROM A WHERE A.cc > ALL (SELECT cc FROM B);
Q. 对于 表 A, B 的子查询, EXISTS 和 IN 要选哪个?
A. 需要根据表A 和 表B 的表大小及索引情况而定.
通常使用 IN 及 EXISTS 情况可以概括为以下 SQL 语句:
-- 语句1
SELECT * FROM A WHERE cc IN (SELECT cc FROM B);
-- 语句2
SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE A.cc = B.cc);原则: 小表驱动大表.
结论:
- 如果 B表 比较小, 且 A表在 cc 列上有索引, 则推荐使用语句1.
这里的表大小指的是根据where筛选后的大小, 而非表的原始大小
- 如果 A表 比较小, 且 B表在 cc 列上有索引, 则推荐使用语句2.
表联结/连接 Join
联结是一种机制，用来在一条 SELECT语句中关联表，因此称之为联结。
使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。
使用联结的要点:
- 注意所使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。
- 保证使用正确的联结条件，否则将返回不正确的数据。
- 应该总是提供联结条件，否则会得出笛卡儿积。
- 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。
<u>表别名</u>只在查询执行中使用。与列别名不一样，表别名不返回到客户机。
<u>完全限定列名</u> 在引用的列可能出现二义性时，必须使用完全限定列名（用一个点分隔的表名和列名）。如果引用一个没有用表名限制的具有二义性的列名，MySQL将返回错误。
在联结两个表时，你实际上做的是将第一个表中的每一行与第二个表中的每一行配对。 WHERE 子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行。没有WHERE 子句，第一个表中的每个行将与第二个表中的每个行配对，而不管它们逻辑上是否可以配在一起。
示例代码↓
-- 将表 vendors 与表 products 联结, 联结条件是: vendors.vend_id = products.vend_id
SELECT prod_id,vend_name,prod_name,prod_price FROM vendors,products WHERE vendors.vend_id = products.vend_id;
-- 表 vendors 每一行都将于表 products 每一行配对
-- 此时返回的结果为 笛卡尔积, 返回行数目是: count(表A) * count(表B)
SELECT prod_id,vend_name,prod_name,prod_price FROM vendors,products;根据获取到的结果集的范围将连接进行分类:
- <u>内连接</u>: 取多个表之间满足条件的数据行(交集)
隐式的内连接: 不写
INNER JOIN显式的内连接: 写
INNER JOIN - <u>外连接</u>: 除了取满足条件的交集外, 还会取某一方不满足条件的记录.
左外连接 LEFT OUTER JOIN
右外连接 RIGHT OUTER JOIN
全外连接 FULL OUTER JOIN
书写时
OUTER可以忽略 - <u>交叉连接</u>: 笛卡尔积（cartesian product）(所有集合的所有组合).
CROSS JOIN
返回记录的条数是每个表的行数的乘积.
根据连接时的测试条件, 将连接进行分类:
- <u>等值连接</u>: 连接条件是等号
- <u>非等值连接</u>: 连接条件是非等号
当自身与自身进行连接时, 称为<u>自连接</u>.
内部联结 INNER JOIN
<u>内部联结</u>即上面的<u>等值联结</u>, 它基于两个表之间的相等测试。
SELECT vendors.vend_id,vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;<u>使用哪种语法?</u> ANSI SQL规范首选 INNER JOIN 语法。此外，尽管使用 WHERE 子句定义联结的确比较简单，但是使用明确的联结语法能够确保不会忘记联结条件，有时候这样做也能影响性能。
<u>性能考虑</u> MySQL在运行时关联指定的每个表以处理联结。这种处理可能是非常耗费资源的，因此应该仔细，不要联结不必要的表。联结的表越多，性能下降越厉害。
eg. 返回订购产品 TNT2 的客户列表
-- 子查询方式
select * from customers
where cust_id in (select distinct cust_id
from orders
where order_num in (select order_num
from orderitems
where prod_id="TNT2")
);
-- 表联结方式1
SELECT cust_name,cust_contact FROM customers,orders,orderitems WHERE customers.cust_id = orders.cust_id AND orders.order_num = orderitems.order_num AND orderitems.prod_id = 'TNT2';
-- 表联结方式2
SELECT cust_name,cust_contact FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id INNER JOIN orderitems ON orders.order_num = orderitems.order_num WHERE orderitems.prod_id = "TNT2";自联结
Eg. 假如你发现某物品（其ID为 DTNTR ）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。
-- 子查询方式
SELECT prod_id,prod_name
FROM products
WHERE vend_id = (
SELECT vend_id
FROM products
WHERE prod_id = 'DTNTR'
);
-- 自联结方式
SELECT p1.prod_id,p1.prod_name
FROM products as p1,products as p2
WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';<u>用自联结而不用子查询</u> 自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好
外部联结 OUTER JOIN
<u>外部联结</u>: 联结包含了那些在相关表中没有关联行的行。
许多联结将一个表中的行与另一个表中的行相关联。但有时候会需
要包含没有关联行的那些行。例如，可能需要使用联结来完成以下工作：
- 对每个客户下了多少订单进行计数，包括那些至今尚未下订单的
客户；- 列出所有产品以及订购数量，包括没有人订购的产品；
- 计算平均销售规模，包括那些至今尚未下订单的客户。
-- 使用 LEFT OUTER JOIN 从 FROM子句的左边表（ customers 表）中选择所有行
select c.cust_id,o.order_num
from customers as c
left outer join orders as o
on c.cust_id = o.cust_id;
-- 查看所有客户的订单数量(聚集函数), 包含从没下过单的客户
SELECT c.cust_id,cust_name,count(distinct o.order_num)
FROM customers as c
LEFT OUTER JOIN orders as o
ON c.cust_id=o.cust_id
GROUP BY c.cust_id;与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。在使用 OUTER JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表（ RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT指出的是 OUTER JOIN 左边的表）。
- LEFT OUTER JOIN 左外联结, 包含坐标的全部记录, 若无对应的右边记录, 则其值为 NULL
- RIGHT OUTER JOIN
OUTER 关键字可以省略不写.
<u>外部联结的类型</u> 存在两种基本的外部联结形式：左外部联结和右外部联结。它们之间的唯一差别是所关联的表的顺序不同。换句话说，左外部联结可通过颠倒 FROM 或 WHERE 子句中表的顺序转换为右外部联结。因此，两种类型的外部联结可互换使用，而究竟使用哪一种纯粹是根据方便而定。
组合查询 UNION
MySQL也允许执行多个查询（多条 SELECT 语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。
<u>组合查询和多个 WHERE 条件</u> 多数情况下，组合相同表的两个查询完成的工作与具有多个 WHERE 子句条件的单条查询完成的工作相同。
-- 返回查询(过滤重复行)
SELECT ... FROM ...
UNION
SELECT ... FROM ...
ORDER BY ...
-- 返回查询(保留所有行)
SELECT ... FROM ...
UNION ALL
SELECT ... FROM ...
ORDER BY ...对于更复杂的过滤条件，或者从多个表（而不是单个表）中检索数据的情形，使用 UNION 可能会使处理更简单。
UNION规则
- UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION 分隔（因此，如果组合4条 SELECT 语句，将要使用3个UNION 关键字）。
- UNION 中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
- 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。
- 组合查询可以引用于不同的表
特点
- UNION 默认会去除重复的行, 如果要返回所有匹配行, 则要使用 UNION ALL
- UNION查询只能使用一条ORDER BY 子句, 只能出现在最后一条 SELECT 语句之后.
全文本搜索
重要说明
- 在索引全文本数据时，短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）。
- MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表（请参阅MySQL文档以了解如何完成此工作）。
- 许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。因此，MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于 IN BOOLEANMODE 。
- 如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）。
- 忽略词中的单引号。例如， don't 索引为 dont 。
- 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果。
- 如前所述，仅在 MyISAM 数据库引擎中支持全文本搜索。
启用全文本搜索支持 FULLTEXT
MyISAM 引擎支持, InnoDB 引擎不支持.
为了进行全文本搜索，必须索引被搜索的列
CREATE TABLE table_name(
note_id int NOT NULL AUTO_INCREMENT,
note_text text NULL,
PRIMARY KEY (note_id),
FULLTEXT(note_text), -- 创建全文本索引
) ENGINE=MyISAM;<u>!!不要在导入数据时使用 FULLTEXT</u>更新索引要花时间，虽然不是很多，但毕竟要花时间。如果正在导入数据到一个新表，此时不应该启用 FULLTEXT 索引。应该首先导入所有数据，然后再修改表，定义 FULLTEXT 。这样有助于更快地导入数据（而且使索引数据的总时间小于在导入每行时分别进行索引所需的总时间）。
进行全文本搜索 MATCH…AGAINST…
全文本搜索返回的结果默认排序是按照关联程度最高的排在最前面
-- 针对指定的列进行搜索
SELECT * FROM `表名` WHERE Match(`列名`) Against('搜索词');Match(列名) Against('搜索词')实际上是计算出一个代表关联程度的数值, 该数值可以在 SELECT 中直接查看.
?? <u>使用完整的 Match() 说明</u> 传递给 Match() 的值必须与FULLTEXT() 定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。
<u>!!搜索不区分大小写</u> 除非使用 BINARY 方式（本章中没有介绍），否则全文本搜索不区分大小写。
查询扩展 WITH EXPANSION
-- WITH QUERY EXPANSION 使用查询扩展
SELECT note_id,note_text
FROM productnotes
WHERE match(note_text) against('anvils' WITH QUERY EXPANSION); MySQL对数据和索引进行两遍扫描来完成搜索：
- 首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；
- 其次，MySQL检查这些匹配行并选择所有有用的词
- 再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。
利用查询扩展，能找出可能相关的结果，即使它们并不精确包含所查找的词。
<u>行越多越好</u> 表中的行越多（这些行中的文本就越多），使用查询扩展返回的结果越好。
布尔文本搜索
布尔方式（booleanmode）
- 要匹配的词；
- 要排斥的词（如果某行包含这个词，则不返回该行，即使它包含
其他指定的词也是如此）； - 排列提示（指定某些词比其他词更重要，更重要的词等级更高）；
- 表达式分组；
- 另外一些内容。
<u>即使没有 FULLTEXT 索引也可以使用</u> 布尔方式不同于迄今为止使用的全文本搜索语法的地方在于，即使没有定义
FULLTEXT 索引，也可以使用它。但这是一种非常缓慢的操作（其性能将随着数据量的增加而降低）。
SELECT note_id,note_text
FROM productnotes
WHERE match(note_text) against('heavy -rope*' IN BOOLEAN MODE);说明:
- 匹配
heavy- 排除
rope开头的词
插入数据
INSERT INTO
几种使用方式
- 插入完整的行；
- 插入行的一部分；
- 插入多行；
- 插入某些查询的结果
插入时必须对每个列必须提供一个值.
-- 简单但不安全, 依赖表中列的定义次序
INSERT INTO customer VALUES(NULL,'pep', '100 main', 'los angles', 'CA', '90046', 'USA', NULL, NULL);
-- 指定插入的列, 推荐(但很繁琐)
INSERT INTO customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email) VALUES('pep', '100 main', 'los angles', 'CA', '90046', 'USA', NULL, NULL);
-- 插入多行
INSERT INTO `表名`(`列名1`, `列名2`) VALUES("值1", "值2"),("值3", "值4"),("值5", "值6");
-- 插入检索出的数据, 注意避免主键的冲突
INSERT INTO `表1`(`列名1`, `列名2`) SELECT `列名1`, `列名2` FROM `表2`;<u>插入时省略列需满足以下任一条件</u>:
- 该列定义为允许 NULL 值（无值或空值）。
- 在表定义中给出默认值。这表示如果不给出值，将使用默认值。
<u>降低插入优先级</u> INSERT LOW PRIORITY INTO
↑ LOW PRIORITY 同样适用于 UPDATE 和 DELETE 语句
提高 INSERT 的性能 一次插入多条记录可以提高数据库处理的性能，因为MySQL用单条 INSERT 语句处理多个插入比使用多条 INSERT语句快。
<u>INSERT SELECT 中的列名</u> MySQL不关心 SELECT 返回的列名。它使用的是列的位置，因此 SELECT 中的第一列（不管其列名）将用来填充表列中指定的第一个列，第二列将用来填充表列中指定的第二个列，如此等等。这对于从使用不同列名的表中导入数据是非常有用的。
REPLACE INTO
# 插入/替换一条记录
replace into `表名`(`列名1`,`列名2`) VALUES("值1", "值2");
# 将表 A 所有记录插入/替换到表 B
replace into `表A`(`列名1`,`列名2`) select `列名3`,`列名4` from `表B`;replace into 首先尝试插入数据到表中, 若存在冲突(主键, 唯一索引), 那么会先删除该行数据再插入新的数据, 否则直接插入该数据.
注意: 写入记录的字段中需要至少有一个主键或唯一键.
更新和删除数据
好习惯:
- 除非确实打算更新和删除每一行，否则绝对不要使用不带 WHERE子句的 UPDATE 或 DELETE 语句。
- 在对 UPDATE 或 DELETE 语句使用 WHERE 子句前，应该先用 SELECT 进行测试，保证它过滤的是正确的记录，以防编写的 WHERE 子句不正确。
- 使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行。
更新数据 UPDATE
UPDATE `表名`
SET `列1`="值1", `列2`="值2"
WHERE ...;
-- IGNORE, 更新多行时, 忽略错误
UPDATE IGNORE `表名`
SET ...
WHERE ...;<u>IGNORE 关键字</u> 如果用 UPDATE 语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个 UPDATE 操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。为即使是发生错误，也继续进行更新，可使用 IGNORE 关键字
删除数据 DELETE
DELETE FROM `表名`
WHERE ...;<u>删除表的内容而不是表</u> DELETE 语句从表中删除行，甚至是删除表中所有行。但是， DELETE 不删除表本身。
<u>更快的删除</u> 如果想从表中删除所有行，不要使用 DELETE 。可使用 TRUNCATE TABLE 语句，它完成相同的工作，但速度更快（ TRUNCATE 实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）。
创建和操纵表
创建表 CREATE TABLE
CREATE DATABASE `samp_db` DEFAULT charset utf8 collate utf8_unicode_ci;
-- 移除数据库表 user_accounts
DROP TABLE IF EXISTS `user_accounts`;
-- 示例
CREATE TABLE IF NOT EXISTS `user_accounts`(
`id` int(100) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
`password` varchar(64) NOT NULL COMMENT '用户密码',
`reset_password` tinyint(2) NOT NULL DEFAULT 0 COMMENT '用户类型：0－不需要重置密码；1-需要重置密码',
`mobile` varchar(20) NOT NULL DEFAULT '' COMMENT '手机',
`create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- 创建唯一索引, 不允许重复
UNIQUE KEY idx_user_mobile(`mobile`) -- 索引名可忽略: UNIQUE KEY (`mobile`)
-- 创建外键
-- FOREIGN KEY (`dept_id`) REFERENCES `depts`(`id`) ON DELETE cascade
-- 创建主键的另外一种方式
-- PRIMARY KEY (`id`)
-- PRIMARY KEY (`key1`,`key2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;主键值 必须唯一。表中的每个行必须具有唯一的主键值。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。
eg. 多个列的组合作为主键
CREATE TABLE IF NOT EXISTS orderitems ( order_num int NOT NULL, order_item int NOT NULL, prod_id char(10) NOT NULL, quantity int NOT NULL, item_price decimal(8,2) NOT NULL, PRIMARY KEY(order_num, order_item) ) ENGINE=InnoDB;orderitems 表包含orders表中每个订单的细节。每个订单有多项物品，但每个订单任何时候都只有1个第一项物品，1个第二项物品，如此等等。因此，订单号（ order_num 列）和订单物品（ order_item 列）的组
合是唯一的，从而适合作为主键
NULL值就是没有值或缺值。允许 NULL 值的列也允许在插入行时不给出该列的值。不允许 NULL 值的列不接受该列没有值的行，
<u>理解 NULL</u> 不要把 NULL 值与空串相混淆。 NULL 值是没有值，它不是空串。如果指定 '' （两个单引号，其间没有字符），这在 NOT NULL 列中是允许的。空串是一个有效的值，它不是无值。 NULL 值用关键字 NULL 而不是空串指定。
主键和 NULL 值 主键为其值唯一标识表中每个行的列。<u>主键中只能使用不允许 NULL 值的列</u>。允许 NULL 值的
列不能作为唯一标识。
AUTO_INCREMENT
- 每个表只允许一个 AUTO_INCREMENT 列，而且它必须被索引（如，通过使它成为主键）
- 在 INSERT 语句中指定一个值，只要它是唯一的（至今尚未使用过）即可，该值将被用来替代自动生成的值。后续的增量将开始使用该手工插入的值。
last_insert_id()函数返回最后一个 AUTO_INCREMENT 值.eg. 增加一个新订单
- orders 表中创建一行
- 使用
last_insert_id()获取自动生成的order_num值 - 在 orderitms 表中对订购的每项物品创建一行。 order_num 在 orderitems 表中与订单细节一起存储。
DEFAULT
使用当前时间作为默认值
CREATE TABLE `表名`( ..., `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP );- 许多数据库开发人员使用默认值而不是 NULL 列，特别是对用于计算或数据分组的列更是如此。
ENGINE
- InnoDB 是一个可靠的事务处理引擎，它不支持全文本搜索；
- MEMORY 在功能等同于 MyISAM ，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
- MyISAM 是一个性能极高的引擎，它支持全文本搜索（参见第18章），但不支持事务处理。
<u>外键不能跨引擎</u> 混用引擎类型有一个大缺陷。外键（用于强制实施引用完整性，如第1章所述）不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。
更新表 ALTER TABLE
列和外键的操作
-- 新增列
ALTER TABLE `表名` ADD COLUMN `列名` 列属性;
-- 删除列
ALTER TABLE `表名` DROP COLUMN `列名`;
-- 修改列(属性替换)
-- CHANGE 可以重命名列名, MODIFY 不能
ALTER TABLE `表名` CHANGE COLUMN `旧列名` `新列名` 列属性;
ALTER TABLE `表名` MODIFY `列名` 列属性;
-- 删除表
DROP TABLE `表名`;
-- 重命名表
RENAME TABLE `表名1` TO `表名2`;复杂的表结构更改一般需要手动删除过程
用新的列布局创建一个新表；
- 使用 INSERT SELECT 语句从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段；
- 检验包含所需数据的新表；
- 重命名旧表（如果确定，可以删除它）；
- 用旧表原来的名字重命名新表；
- 根据需要，重新创建触发器、存储过程、索引和外键。
<u>小心使用 ALTER TABLE</u> 使用 ALTER TABLE 要极为小心，应该在进行改动前做一个完整的备份（模式和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，可能不能删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。
约束, 索引
-- 删除外键
-- 约束名可以用 show create table `表名` 语句来查看
ALTER TABLE `表名` DROP FOREIGN KEY `约束名`;
-- 查看索引
SHOW INDEX FROM `表名`;
SHOW KEY FROM `表名`;
-- 创建普通索引(省略索引名)
ALTER TABLE `表名` ADD INDEX (`列名`);
ALTER TABLE `表名` ADD UNIQUE KEY(`列名`);
ALTER TABLE `表名` ADD PRIMARY KEY(`列名`);
ALTER TABLE `表名` ADD FOREIGN KEY(`列名`) REFERENCES `关联表名`(`关联列名`);
ALTER TABLE `表1` ADD CONSTRAINT `约束名` FOREIGN KEY (`外键`) REFERENCES `表2` (`表2的键`);
-- CREATE INDEX 只可对表增加普通索引或UNIQUE索引
CREATE INDEX `索引名` ON `表名` (`列名`);
CREATE UNIQUE INDEX `索引名` ON `表名` (`列名`);
-- 删除索引
ALTER TABLE `表名` DROP PRIMARY KEY;
ALTER TABLE `表名` DROP INDEX `索引名`;
ALTER TABLE `表名` DROP FOREIGN KEY `约束名`;
DROP INDEX `索引名` ON `表名`;索引
2019年5月29日17:18:22 开始补充
种类:
INDEX普通索引：仅加速查询UNIQUE KEY唯一索引：加速查询 + 列值唯一（可以有null）PRIMARY KEY主键索引：加速查询 + 列值唯一 + 表中只有一个（不可以有null）INDEX组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并FULLTEXT全文索引：对文本的内容进行分词，进行搜索
<u>术语</u>
- 索引合并：使用多个单列索引组合查询搜索
- 覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖
组合索引
- 同时搜索多个条件时，组合索引的性能效率好过于多个单一索引合并
索引列的选择
重要的条件有
查询频繁
索引的维护是有额外消耗的, 因此一般只建立有必要的索引
区分度高
区分度太低的话, 跟全表扫描(指聚簇索引)没差多少, 还额外增加维护索引的消耗
长度小
字段越长, 则索引越大, 额外空间的占用也越大.
当需要极致优化字符串字段的索引占用时, 可以对字符串(char, varchar)字段索引时可以考虑使用前N个字符, 但要注意测试是否有足够的区分度:
select (count(distinct left(字段,2)) / count(distinct 字段)) as diff_rate from 表名;或者是对该字符串 hash, 增加额外字段(crc32)
- 尽可能重用索引(联合索引)
联合索引
左前缀规则
索引举例：index(a,b,c)
| 条件 | 索引是否发挥作用 | 用了哪些列 |
|---|---|---|
| Where a=3 | 是 | 只使用了a列 |
| Where a=3 and b=5 | 是 | 使用了a,b列 |
| Where a=3 and b=5 and c=4 | 是 | 使用了abc |
| Where b=3 or where c=4 | 否 | |
| Where a=3 and c=4 | 是 | a列能发挥索引,c不能 |
| Where a=3 and b>10 and c=7 | 是 | a能利用,b能利用, c不能利用 |
| where a=3 and b like ‘xxxx%’ and c=7 | 是 | a能用,b能用,c不能用 |
索引的创建及管理
-- CREATE TABLE 时创建
CREATE TABLE IF NOT EXISTS `users` (
-- 省略字段定义
PRIMARY KEY (`id`),
UNIQUE KEY `users_phone` (`phone`),
KEY `users_name` (`name`),
) Engine=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
ALTER TABLE table_name ADD INDEX index_name (column_list)
-- 可忽略索引名
-- ALTER TABLE table_name ADD INDEX (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
-- 一个语句建多个索引
ALTER TABLE HeadOfState ADD PRIMARY KEY (ID), ADD INDEX (LastName,FirstName);视图
视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
视图包含的是一个SQL查询, 它不包含数据!!个人理解: 视图即别名~
-- 创建视图
CREATE VIEW `视图名` AS SELECT ...
-- 查看创建指定视图的语句
SHOW CREATE VIEW `视图名`;
-- 删除视图
DROP VIEW `视图名`;
-- 更新视图
CREATE OR REPLACE VIEW AS SELECT ...视图的作用:
- 简化数据处理
- 重新格式化基础数据
- 保护基础数据
为什么使用视图:
- 重用SQL语句。
- 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。
- 使用表的组成部分而不是整个表。
- 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
- 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
视图的规则和限制
- 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。
- 对于可以创建的视图数目没有限制。
- 为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
- 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
- ORDER BY 可以用在视图中，但如果从该视图检索数据 SELECT 中也含有 ORDER BY ，那么该视图中的 ORDER BY 将被覆盖。
- 视图不能索引，也不能有关联的触发器或默认值。
- 视图可以和表一起使用。例如，编写一条联结表和视图的 SELECT语句。
<u>创建可重用的视图</u>
创建不受特定数据限制的视图是一种好办法。例如，上面创建的视图返回生产所有产品的客户而不仅仅是 生产TNT2 的客户。扩展视图的范围不仅使得它能被重用，而且甚至更有用。这样做不需要创建和维护多个类似视图。
<u>将视图用于检索</u>
一般，应该将视图用于检索（ SELECT 语句）而不用于更新（ INSERT 、 UPDATE 和 DELETE ）。
存储过程
存储过程
简单来说: 存储过程是为以后的使用而保存的一条或多条MySQL语句的集合.
使用存储过程的原因:
- 封装复杂操作, 统一调用
提高性能
使用存储过程比使用单独的SQL语句要快
- 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码
存储过程一般并不显示结果, 而是把结果返回给你指定的变量.
-- 调用存储过程
CALL `过程名`()
-- 更改mysql命令行客户端语句分隔符, 除了 \ 符号外,其他字符都可以作为语句分隔符.
DELIMITER //
-- 创建存储过程
CREATE PROCEDURE `过程名`()
BEGIN
END//
-- 还原mysql命令行客户端语句分隔符
DELIMITER ;
-- 删除存储过程
DROP PROCEDURE IF EXISTS `过程名`;
-- 检查(查看)存储过程
SHOW CREATE PROCEDURE `过程名`;
-- 查看存储过程的元数据(创建时间, 创建人..)
SHOW PROCEDURE STATUS LIKE '过程名';MySQL支持存储过程的参数:
- IN
- OUT
- INOUT
<u>参数的数据类型</u>
存储过程的参数允许的数据类型与表中使用的数据类型相同。!!! 记录集不是允许的类型，因此，不能通过一个参数返回多个行和列。
一个简单的示例: 计算商品的最低、最高、平均价格
DELIMITER //
CREATE PROCEDURE productpricing(
OUT pl DECIMAL(8,2),
OUT ph DECIMAL(8,2),
OUT pm DECIMAL(8,2)
)
BEGIN
SELECT Min(prod_price) INTO pl FROM products;
SELECT Max(prod_price) INTO ph FROM products;
SELECT Avg(prod_price) INTO pm FROM products;
END//
DELIMITER ;
CALL productpricing(@pricelow, @pricehigh, @priceaverage);
SELECT @pricelow, @pricehigh, @priceaverage;另一个简单示例： 接受订单号并返回该订单的统计
DELIMITER //
CREATE PROCEDURE ordertotal(IN onumber INT, OUT ototal DECIMAL(6,2))
BEGIN
SELECT Sum(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
INTO ototal;
END//
DELIMITER ;
CALL ordertotal(20005, @total);
SELECT @total;变量
<u>变量（variable）</u>
内存中一个特定的位置，用来临时存储数据。变量名不区分大小写
用户变量
@变量名, 仅对当前连接有效
可以使用 SET @变量=值 或 SELECT @变量:=值; 来赋值给变量
--
SET @变量=值;
-- 在SELECT中, = 是比较符, 因此需要用 :=
SELECT @变量:=值;系统变量
全局变量
对当前mysqld实例有效
SET GLOBAL 变量名=值;
SET @@变量名=值;需要 SUPER 权限, 需重新连接后才生效.
会话变量
只对当前连接有效
-- 设置变量值
SET SESSION 变量名=值;
-- LOCAL 是SESSION的同义词
SET LOCAL 变量名=值;
-- 不指定 GLOBAL,SESSION 时, 默认就是 SESSION
-- 此处没有 @
SET 变量名=值;
-- 若存在会话变量则返回, 否则返回全局变量.
SELECT @@变量名;
SHOW VARIABLES LIKE '变量名';局部变量
declare定义, 仅在当前块有效(begin...end)
语法
条件语句
IF ... THEN
ELSEIF ... THEN
ELSE
END IF; 循环语句
-- WHILE 循环
WHILE ... DO
END WHILE;
-- ---------------------------------------------------
-- REPEAT 循环
REPEAT
UNTIL ...
END REPEAT;
-- ---------------------------------------------------
-- LOOP 循环
loop标记: LOOP
IF ... THEN
LEAVE loop标记;
END IF;
END LOOP;
-- LOOP 示例
CREATE PROCEDURE proc_loop ()
BEGIN
declare i int default 0;
loop_label: loop
select i;
set i=i+1;
if i>=5 then
leave loop_label;
end if;
end loop;
END动态执行SQL语句
PREPARE 变量 FROM "...";
EXECUTE 变量 USING @p1;
DEALLOCATE PREPARE 变量;
-- 示例
SET @num = 20005;
PREPARE stmt FROM 'SELECT * FROM orders WHERE order_num = ?';
EXECUTE stmt USING @num;
DEALLOCATE PREPARE stmt;参数只能使用<u>用户变量</u>来传递
智能存储过程
在存储过程中包含:
- 业务规则
- 智能处理
-- Name: ordertotal
-- Parameters: onumber = order number
-- taxable = 0 if not taxable, 1 if taxable
-- ototal = order total VARIABLES
CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
) COMMENT '获取订单总额, 可选增加营业税'
BEGIN
-- Declare variable for total
DECLARE total DECIMAL(8,2);
-- Declare tax percentage
DECLARE taxrate INT DEFAULT 6;
-- Get the order total
SELECT sum(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO total;
-- Is this taxable?
IF taxable THEN
-- yes, so add taxrate to the total
SELECT total+(total/100*taxrate) INTO total;
END IF;
-- And finally, save to out vaiable
SELECT total INTO ototal;
END;- DECLARE 定义了两个局部变量, 支持可选的默认值
游标
游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
<u>只能用于存储过程</u> 不像多数DBMS，MySQL游标只能用于存储过程（和函数）。
使用游标的步骤:
- 声明游标(仅定义查询语句)
- 打开游标(执行查询)
- 使用游标检索数据(遍历)
关闭游标
- 游标关闭后必须重新打开才能再次使用
- 存储过程结束时会自动将已打开的游标关闭
CREATE PROCEDURE `processorders`()
BEGIN
-- create cursor
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- open cursor
OPEN ordernumbers;
-- close cursor
CLOSE ordernumbers;
END书上示例
DROP PROCEDURE processorders;
CREATE PROCEDURE processorders()
BEGIN
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE t DECIMAL(8,2);
-- Declare CURSOR
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- Declare continue handler
-- FOR NOT FOUND
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
-- Create a table to store the results
CREATE TABLE IF NOT EXISTS ordertotals(
order_num INT,
total DECIMAL(8,2)
);
OPEN ordernumbers;
REPEAT
-- Get order number
FETCH ordernumbers INTO o;
CALL ordertotal(o,1,t);
INSERT INTO ordertotals(order_num,total) VALUES(o,t);
UNTIL done
END REPEAT;
-- Close the cursor
CLOSE ordernumbers;
END改进示例
DROP PROCEDURE processorders;
CREATE PROCEDURE processorders()
BEGIN
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE t DECIMAL(8,2);
-- Declare CURSOR
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- Declare continue handler
-- FOR NOT FOUND
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
-- Create a table to store the results
CREATE TABLE IF NOT EXISTS ordertotals(
order_num INT,
total DECIMAL(8,2)
);
OPEN ordernumbers;
FETCH ordernumbers INTO o;
-- 避免插入 (NULL,NULL) 到 ordertotals 表
WHILE NOT done DO
CALL ordertotal(o,1,t);
SELECT o,t;
INSERT INTO ordertotals(order_num,total) VALUES(o,t);
FETCH ordernumbers INTO o;
END WHILE;
-- Close the cursor
CLOSE ordernumbers;
END触发器
触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于 BEGIN 和 END 语句之间的一组语
句）：
- DELETE ；
- INSERT ；
- UPDATE
创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。如果 INSERT 、 UPDATE 或 DELETE 语句能够执行，则相关的触发器也能执行。
- 应该用触发器来保证数据的一致性（大小写、格式等）。在触发器中执行这种类型的处理的优点是它总是进行这种处理，而且是透明地进行，与客户机应用无关。
- 触发器的一种非常有意义的使用是创建审计跟踪。使用触发器，把更改（如果需要，甚至还有之前和之后的状态）记录到另一个表非常容易。
- 遗憾的是，MySQL触发器中不支持 CALL 语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。
创建触发器
在创建触发器时，需要给出4条信息：
- 唯一的触发器名；
- 触发器关联的表；
- 触发器应该响应的活动（ DELETE 、 INSERT 或 UPDATE ）；
- 触发器何时执行（处理之前或之后）。
触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条 INSERT 、 UPDATE和 DELETE 的之前和之后）。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对 INSERT 和 UPDATE 操作执行的触发器，则应该定义两个触发器。
<u>仅支持表</u> 只有表才支持触发器，视图不支持（临时表也不支持）
-- 创建触发器
CREATE TRIGGER `触发器名`
AFTER|BEFORE
INSERT|UPDATE|DELETE
ON `表名`
FOR EACH ROW
...
-- 删除触发器
DROP TRIGGER `触发器名`;
<u>BEFORE 或 AFTER ？</u> 通常，将 BEFORE 用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）。本提示也适用于 UPDATE 触发器。
INSERT 触发器
- 在 INSERT 触发器代码内，可引用一个名为 NEW 的虚拟表，访问被插入的行；
- 在 BEFORE INSERT 触发器中， NEW 中的值也可以被更新（允许更改被插入的值）；
- 对于 AUTO_INCREMENT 列， NEW 在 INSERT 执行之前包含 0 ，在 INSERT执行之后包含新的自动生成值。
-- mysql中无法执行: 禁止触发器返回结果集
-- ERROR 1415 (0A000): Not allowed to return a result set from a trigger
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW
SELECT NEW.order_num;DELETE 触发器
- 在 DELETE 触发器代码内，你可以引用一个名为 OLD 的虚拟表，访问被删除的行；
- OLD 中的值全都是只读的，不能更新。
在任意订单被删除前将执行此触发器。它使用一条 INSERT 语句将 OLD 中的值（要被删除的订单）保存到一个名为 archive_orders 的存档表中
CREATE TRIGGER deleteorders BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
INSERT INTO archive_orders(order_num,order_date,cust_id)
VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);
END使用 BEFORE DELETE 触发器的优点（相对于 AFTER DELETE 触发器来说）为，如果由于某种原因，订单不能存档， DELETE 本身将被放弃。
UPDATE 触发器
- 在 UPDATE 触发器代码中，你可以引用一个名为 OLD 的虚拟表访问以前（ UPDATE 语句前）的值，引用一个名为 NEW 的虚拟表访问新更新的值；
- 在 BEFORE UPDATE 触发器中， NEW 中的值可能也被更新（允许更改将要用于 UPDATE 语句中的值）；
- OLD 中的值全都是只读的，不能更新。
CREATE TRIGGER updatevendor
BEFORE UPDATE ON vendors
FOR EACH ROW
SET NEW.vend_state=Upper(NEW.vend_state);事务
基本要素 ACID
MySQL的四种事务隔离级别
- 原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。
- 一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。
- 隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
- 持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。
并发与隔离级别
事务存在的问题:
- 脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
- 不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
- 幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表
数据库事务隔离级别:
- read-uncommited 读未提交
存在所有问题, 最低的隔离级别。一个事务可以读取另一个事务并未提交的更新结果。
- read-commited 读已提交
解决"脏读", 大部分数据库采用的默认隔离级别。一个事务的更新操作结果只有在该事务提交之后，另一个事务才可以的读取到同一笔数据更新后的结果。
- repeatable-read 可重复读
解决"不可重复读", 整个事务过程中，对同一笔数据的读取结果是相同的，不管其他事务是否在对共享数据进行更新，也不管更新提交与否。
- serializable 序列化
解决"幻读", 最高隔离级别。所有事务操作依次顺序执行。注意这会导致并发度下降，性能最差。通常会用其他并发级别加上相应的并发锁机制来取代它。
- 不可重复读
- 可重复读
- 幻读
MySQL 默认级别是 repeatable-read, 由于其使用了间隙锁, 因此尽管是可重复读级别, 但并不会产生幻读问题.
术语
- 事务（ transaction ）指一组SQL语句；
- 回退（ rollback ）指撤销指定SQL语句的过程；
- 提交（ commit ）指将未存储的SQL语句结果写入数据库表；
- 保留点（ savepoint ）指事务处理中设置的临时占位符（place-holder），你可以对它发布回退（与回退整个事务处理不同）。
-- 标识事务开始
START TRANSACTION;
-- ROLLBACK;
-- COMMIT;<u>哪些语句可以回退？</u> 事务处理用来管理 INSERT 、 UPDATE 和DELETE 语句。你不能回退 SELECT 语句。（这样做也没有什么意义。）你不能回退 CREATE 或 DROP 操作。事务处理块中可以使用这两条语句，但如果你执行回退，它们不会被撤销。
-- 关闭本次连接的mysql自动提交
SET autocommit=0;保留点 SavePoint
为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。
-- 创建保留点
SAVEPOINT `保留点名`;
-- 回退到指定保留点
ROLLBACK TO `保留点名`;
字符集
术语
- 字符集为字母和符号的集合；
- 编码为某个字符集成员的内部表示；
- 校对为规定字符如何比较的指令。
-- 查看可用的字符集
SHOW CHARACTER SET;
SHOW CHARSET;
-- 查看可用的校对
SHOW COLLATION;
-- 查看当前使用的字符集
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';通常系统管理在安装时定义一个默认的字符集和校对。此外，也可以在创建数据库时，指定默认的字符集和校对。
-- 以下语句都可以
CREATE DATABASE `数据库名` DEFAULT character set utf8 collate utf8_unicode_ci;
CREATE DATABASE `数据库名` default charset utf8mb4 collate utf8mb4_unicode_ci;
-- 关于字符集的设定也可以如下
CREATE DATABASE `数据库名` DEFAULT charset utf8 collate utf8_unicode_ci;实际上，字符集很少是服务器范围（甚至数据库范围）的设置。不同的表，甚至不同的列都可能需要不同的字符集，而且两者都可以在创建表时指定。
CREATE TABLE mytable(
column1 INT,
column2 VARCHAR(10),
-- 指定特定列使用特定的字符集及校对
column3 VARCHAR(10) CHARSET latin1 COLLATE latin1_general_ci
) Engine=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- 临时区分大小写排序
SELECT * FROM mytable
ORDER BY column3 COLLATE latin1_general_cs;<u>SELECT 的其他 COLLATE 子句</u> 除了这里看到的在 ORDER BY子句 中使用以外， COLLATE 还可以用于 GROUP BY 、 HAVING 、聚集函数、别名等。
串可以在字符集之间进行转换。为此，使用 Cast() 或 Convert ()函数
安全管理
用户应该对他们需要的数据具有适当的访问权，既不能多也不能少
用户
<u>不要使用 root</u> 应该严肃对待 root 登录的使用。仅在绝对需要时使用它（或许在你不能登录其他管理账号时使用）。不应该在日常的MySQL操作中使用 root 。
- MySQL用户账号和信息存储在名为 mysql 的MySQL数据库中。
- mysql 数据库有一个名为 user 的表，它包含所有用户账号。
用户名中的
Hostlocalhost: 仅本地%: 通配符, 允许从任意主机连接数据库若 "user"@"%" 无法从本地登陆(不存在 "user"@"localhost"的时候), 则要看是否存在匿名账号, 有则删掉.
- 主机名
- ip地址
关于匿名用户(即 User 为空的账户, 可以匹配任意用户名)
# 查看当前是否有匿名账号 SELECT User, Host from mysql.user WHERE Host = 'localhost' AND User = ''; # 删除匿名账号 DROP USER ''@'localhost';
-- 查看当前所有用户
SELECT host,user FROM mysql.user;
-- 创建用户(若不指定 host, 则默认是 %), 此时该用户权限为 USAGE(即无权限)
CREATE USER 用户名 IDENTIFIED BY '密码';
-- 创建用户(原始方式, 不推荐)
insert into mysql.user(Host,User,Password,select_priv,insert_priv,update_priv,delete_priv) values('localhost', 'guest', PASSWORD('123456'), 'Y','Y','Y','Y');
FLUSH PRIVILEGES;
-- 重命名用户
RENAME USER 旧用户名 TO 新用户名;
-- 删除用户
DROP USER 用户名;
-- 删除用户(原始方式, 不推荐)
DELETE FROM mysql.user WHERE Host = '%' AND User = 'yjx'
FLUSH PRIVILEGES;
-- 修改用户名
RENAME USER '旧用户名@..' TO '新用户名@..';
-- 更改自己口令
SET PASSWORD = Password('密码');
-- 更改指定用户口令
SET PASSWORD FOR 用户名 = Password('密码');
-- 低版本好像不支持该语法
ALTER USER 用户名 IDENTIFIED BY '密码';使用 mysqladmin 修改密码
-- 设置密码(若修改密码, 则需输入原密码)
mysqladmin -u root password权限
GRANT 语法
GRANT priv_type ON database.table
TO user[IDENTIFIED BY [PASSWORD] 'password']
[,user [IDENTIFIED BY [PASSWORD] 'password']...]设置访问权限
-- 查看赋予当前用户账号的权限
SHOW GRANTS;
-- 查看赋予某个用户账号的权限
-- 完整的用户定义: user@host
-- 不指定主机名时使用默认的主机名 %
-- 默认查询: 用户名@'%'
SHOW GRANTS FOR 用户名;
-- 创建完整权限的账号(默认 ALL 权限是不包含 GRANT 权限的)
GRANT ALL ON *.* TO '用户名'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION;
-- 创建拥有部分权限的用户
GRANT ALTER,INSERT,UPDATE,DELETE,SELECT,EXECUTE,CREATE ON *.* TO '用户名'@'localhost' IDENTIFIED BY 'password'
-- 创建一个允许完全操作某个数据库的用户(不给权限传递能力)
GRANT ALL ON `db1`.* to 'new_user'@'%' IDENTIFIED BY 'user_password';
-- 创建一个只允许读某个数据库权限的用户
GRANT SELECT ON `db1`.`tb1` to 'readonly'@'%' IDENTIFIED BY 'user_password';
-- 赋予 SELECT 权限
GRANT SELECT ON `数据库名`.* TO 用户名;
-- 收回所有权限
REVOKE ALL PRIVILEGES,GRANT OPTION ON *.* FROM 'yjx'@'%'
-- 收回部分权限
REVOKE SELECT,DELETE FROM *.* FROM 'yjx'@'%'
-- 撤销 SELECT 权限
REVOKE SELECT ON `数据库名`.* FROM 用户名;WITH关键字后面带有一个或多个with_option参数。有5个选项：
- GRANT OPTION：被授权的用户可以将这些权限赋予给别的用户；
- MAX_QUERIES_PER_HOUR count：设置没消失可以允许执行count次查询；
- MAX_UPDATES_PER_HOUR count：设置每个消失可以允许执行count次更新；
- MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立count个连接；
- MAX_USER_CONNECTIONS count：设置单个用户可以同时具有的count个连接数；
新用户的权限为: GRANT USAGE ON *.* TO 'yjx'@'%', 即没有任何权限.
常用权限
| 权限 | 说明 |
|---|---|
| ALL | 除GRANT OPTION外的所有权限 |
| SELECT | 仅查询 |
| SELECT,INSERT | 查询和插入 |
| USAGE | 无权限 |
目标
| 目标 | 说明 |
|---|---|
| 数据库名.* | 指定数据库所有 |
| 数据库名.表 | 指定数据库的指定表 |
| 数据库名.存储过程 | 指定数据库的指定存储过程 |
| . | 所有数据库 |
用户
| 用户 | 说明 |
|---|---|
| 用户名@ip | 指定ip登陆的用户 |
| 用户名@'192.168.1.%' | 指定ip段登陆的用户 |
| 用户名@'%' | 任意ip下登陆的用户 |
| 用户名@localhost | 本地登陆的用户 |
用户名@‘192.168.200.0/255.255.255.0’ | (子网掩码配置) |
GRANT 和 REVOKE 可在几个层次上控制访问权限：
- 整个服务器，使用 GRANT ALL 和 REVOKE ALL；
- 整个数据库，使用 ON database.*；
- 特定的表，使用 ON database.table；
- 特定的列；
- 特定的存储过程。
权限与 user 表中的权限列对应表
| 权限名称 | 对应user表中的列 | 权限的范围 |
|---|---|---|
| CREATE | Create_priv | 数据库、表或索引 |
| DROP | Drop_priv | 数据库或表 |
| GRANT OPTION | Grant_priv | 数据库、表、存储过程或函数 |
| REFERENCES | References_priv | 数据库或表 |
| ALTER | Alter_priv | 修改表 |
| DELETE | Delete_priv | 删除表 |
| INDEX | Index_priv | 用索引查询表 |
| INSERT | Insert_priv | 插入表 |
| SELECT | Select_priv | 查询表 |
| UPDATE | Update_priv | 更新表 |
| CREATE VIEW | Create_view_priv | 创建视图 |
| SHOW VIEW | Show_view_priv | 查看视图 |
| ALTER ROUTINE | Alter_routine | 修改存储过程或存储函数 |
| CREATE ROUTINE | Create_routine_priv | 创建存储过程或存储函数 |
| EXECUTE | Execute_priv | 执行存储过程或存储函数 |
| FILE | File_priv | 加载服务器主机上的文件 |
| CREATE TEMPORARY TABLES | Create_temp_table_priv | 创建临时表 |
| LOCK TABLES | Lock_tables_priv | 锁定表 |
| CREATE USER | Create_user_priv | 创建用户 |
| PROCESS | Process_priv | 服务器管理 |
| RELOAD | Reload_priv | 重新加载权限表 |
| REPLICATION CLIENT | Repl_client_priv | 服务器管理 |
| REPLICATION SLAVE | Repl_slave_priv | 服务器管理 |
| SHOW DATABASES | Show_db_priv | 查看数据库 |
| SHUTDOWN | Shutdown_priv | 关闭服务器 |
| SUPER | Super_priv | 超级权限 |
数据库维护
数据库备份
- msyqldump 程序
BACKUP TABLE或SELECT INTO OUTFILE语句转储数据到外部文件, 使用RESTORE TABLE还原select * from '表' into outfile '/path/to/file.sql'; /* 将结果导出到指定 csv 文件 - 以 , 分隔字段 - 每个字段用 "" 包裹
*/
select name,age from users into outfile '/path/to/file.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n';
*mysqldump 备份*
到处某个库的某些表
mysqldump -uroot -p '数据库名' '表名'...
导出为文本文件
mysqldump -uroot -p -B '数据库名1' '数据库名2' > /tmp/mysql.bak
直接导出为压缩文件
mysqldump -uroot -p -B '数据库名1' '数据库名2' | gzip > /tmp/mysql.bak.gz
-A, --all-databases 备份所有库
-B, --database 备份指定库
-F 刷新binlog日志
-x，--lock-all-tables
-l,--locktables
--single-transaction 适合innodb事务数据库备份
--default-character-set=utf8 字符集
--triggers 备份触发器
-d, --no-data 只备份表结构
-t, --no-create-info 只备份数据, 无 create table 语句
--master-data 增加binlog日志文件名及对应的位置点
生产环境全备份
进行数据库全备，（生产环境还通过定时任务每日凌晨执行）
mysqldump -uroot -p123456 -S /data/3306/mysql.sock --single-transaction -F -B "数据库名" | gzip > /server/backup/mysql_$(date +%F).sql.gz
innodb引擎备份
mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -F --single-transaction -A | gzip > $DATA_FILE
myisam引擎备份
mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -F -A -B --lock-all-tables |gzip >$DATA_FILE
*恢复*
直接从sql文件恢复
mysql -uroot -p < /tmp/mysql.sql
从压缩文件中恢复
gunzip < /tmp/mysql.bak.gz | mysql -uroot -p
若备份文件未指定库, 则此处需要自行指定要恢复到哪个库
mysql -uroot -p <数据库名> < /tmp/mysql.sql
*复制表*
-- 此时要求 目标表 这个表不能存在, 同时该语句会自动创建 目标表 这个表.
select * into 目标表 from 原始表;
-- mysql 不支持上述的 select into 语法, 因此可以用以下语句来处理
-- 但是这种方式仅仅会创建表基本结构一致, 其他的索引之类的一概没有.
create table 目标表 (select * from 原始表)
-- mysql 可以用这种方式来处理
create table 目标表 like 原始表;
insert into 目标表 select * from 原始表;
# 改善性能
- `SHOW PROCESSLIST` 显示所有活动进程（以及它们的线程ID和执行时间）。你还可以用 `KILL` 命令终结某个特定的进程（使用这个命令需要作为管理员登录）。
- 使用 EXPLAIN 语句让MySQL解释它将如何执行一条 SELECT 语句。
- 在导入数据时，应该关闭自动提交。你可能还想删除索引（包括FULLTEXT 索引），然后在导入完成后再重建它们。
- 你的 SELECT 语句中有一系列复杂的 OR 条件吗？通过使用多条SELECT 语句和连接它们的 UNION 语句，你能看到极大的性能改进。
- 索引改善数据检索的性能，但损害数据插入、删除和更新的性能。如果你有一些表，它们收集数据且不经常被搜索，则在有必要之前不要索引它们。（索引可根据需要添加和删除。）
- LIKE 很慢。一般来说，最好是使用 FULLTEXT 而不是 LIKE 。
## 查看表锁情况
mysql> show open tables where in_use > 0;
| Database | Table | In_use | Name_locked |
|---|---|---|---|
| test_yjx | user | 1 | 0 |
## 加快数据库恢复速度
在恢复数据时，可能会导入大量的数据。
此时有一些技巧可以提高导入速度:
- 导入时禁用索引, 导入结束后再开启索引
ALTER TABLE 表名 disable keys;
ALTER TABLE 表名 enable keys;
- 对于InnoDB, 由于默认 `autocommit=1` , 即每条语句作为单独的事务, 因此可以将多条合并成少数几条事务以加快速度.
-- 关闭自动提交, 也可以用 begin 或 start transaction
set autocommit = 0;
-- 插入若干条提交
insert into ...;
...
insert into ...;
commit;
-- 插入若干条提交
insert into ...;
...
insert into ...;
commit;
set autocommit = 1;
# 设计范式
## 表的键和属性概念
**超键** 唯一标识元组(数据行)的属性集叫做超键.
> 比如普通表中主键 id 是超键, (id, name) 也是超键, (id, age) 也是超键, 因为都可以唯一标识元组(数据行).
**候选键** 最小超键, 不包含无用字段, 也称为 **码**.
> 以 **超键** 中的例子来讲, (id, name) 因为包含无用的 name 字段, 所以显然它不是候选键. 而单独的 id 是候选键
**主键** 从候选键中选择一个, 也称为 **主码**.
**外键** 数据表中的字段是别的数据表的主键, 则称它为外键.
**主属性** 包含在任意候选键中的属性称为主属性.
## 范式
所有范式(按照严格顺序排列):
- 1NF(第一范式)
关键: 表中任何属性都是原子性的, 不可再分.
解释: 字段不要是可以由其他字段组合/计算的.
- 2NF(第二范式)
需要保证表中的非主属性与候选键(码)完全依赖 (即消除了部分依赖)
- 3NF(第三范式)
需要保证表中的非主属性与候选键(码)不存在传递依赖
通常只要求到这个级别.
- BCNF(巴斯-科德范式)
消除主属性之间的部分依赖和传递依赖
- 4NF(第四范式)
- 5NF(完美范式)
> 这里的
>
> - 部分依赖 也称为 部分函数依赖
>
> - 传递依赖 也称为 传递函数依赖
通常要求: <u>**3NF**</u>
> 根据实际情况, 必须时可以新增冗余字段来提高查询效率, 需要权衡.
范式的严格程度是依次递增, 且高级别范式肯定是满足低级别范式的.
# 存储引擎
该部分主要来自: https://juejin.im/post/5c2c53396fb9a04a053fc7fe
## 功能差异
show engines
| Engine | Support | Comment |
| ------ | ------- | ------------------------------------------------------------ |
| InnoDB | DEFAULT | **Supports transactions, row-level locking, and foreign keys** |
| MyISAM | YES | **MyISAM storage engine** |
## 存储差异
| | MyISAM | Innodb |
| ------------------------------------------------------------ | ------------------------------------------------- | ---------------------------------------- |
| 文件格式 | 数据和索引是分别存储的，数据`.MYD`，索引`.MYI` | 数据和索引是集中存储的，`.ibd` |
| 文件能否移动 | 能，一张表就对应`.frm`、`MYD`、`MYI`3个文件 | 否，因为关联的还有`data`下的其它文件 |
| 记录存储顺序 | 按记录插入顺序保存 | 按主键大小有序插入 |
| 空间碎片（删除记录并`flush table 表名`之后，表文件大小不变） | 产生。定时整理：使用命令`optimize table 表名`实现 | 不产生 |
| 事务 | 不支持 | 支持 |
| 外键 | 不支持 | 支持 |
| 锁支持（锁是避免资源争用的一个机制，MySQL锁对用户几乎是透明的） | 表级锁定 | 行级锁定、表级锁定，锁定力度小并发能力高 |
> 锁扩展
>
> 表级锁（`table-level lock`）：`lock tables <table_name1>,<table_name2>... read/write`，`unlock tables <table_name1>,<table_name2>...`。其中`read`是共享锁，一旦锁定任何客户端都不可读；`write`是独占/写锁，只有加锁的客户端可读可写，其他客户端既不可读也不可写。锁定的是一张表或几张表。
>
> 行级锁（`row-level lock`）：锁定的是一行或几行记录。共享锁：`select * from <table_name> where <条件> LOCK IN SHARE MODE;`，对查询的记录增加共享锁；`select * from <table_name> where <条件> FOR UPDATE;`，对查询的记录增加排他锁。这里**值得注意**的是：`innodb`的行锁，其实是一个子范围锁，依据条件锁定部分范围，而不是就映射到具体的行上，因此还有一个学名：间隙锁。比如`select * from stu where id < 20 LOCK IN SHARE MODE`会锁定`id`在`20`左右以下的范围，你可能无法插入`id`为`18`或`22`的一条新纪录。
# 课程数据
*create.sql*
MySQL Crash Course
http://www.forta.com/books/06...
Example table creation scripts
Create customers table
CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL ,
cust_address char(50) NULL ,
cust_city char(50) NULL ,
cust_state char(5) NULL ,
cust_zip char(10) NULL ,
cust_country char(50) NULL ,
cust_contact char(50) NULL ,
cust_email char(255) NULL ,
PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
Create orderitems table
CREATE TABLE orderitems
(
order_num int NOT NULL ,
order_item int NOT NULL ,
prod_id char(10) NOT NULL ,
quantity int NOT NULL ,
item_price decimal(8,2) NOT NULL ,
PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;
Create orders table
CREATE TABLE orders
(
order_num int NOT NULL AUTO_INCREMENT,
order_date datetime NOT NULL ,
cust_id int NOT NULL ,
PRIMARY KEY (order_num)
) ENGINE=InnoDB;
Create products table
CREATE TABLE products
(
prod_id char(10) NOT NULL,
vend_id int NOT NULL ,
prod_name char(255) NOT NULL ,
prod_price decimal(8,2) NOT NULL ,
prod_desc text NULL ,
PRIMARY KEY(prod_id)
) ENGINE=InnoDB;
Create vendors table
CREATE TABLE vendors
(
vend_id int NOT NULL AUTO_INCREMENT,
vend_name char(50) NOT NULL ,
vend_address char(50) NULL ,
vend_city char(50) NULL ,
vend_state char(5) NULL ,
vend_zip char(10) NULL ,
vend_country char(50) NULL ,
PRIMARY KEY (vend_id)
) ENGINE=InnoDB;
Create productnotes table
CREATE TABLE productnotes
(
note_id int NOT NULL AUTO_INCREMENT,
prod_id char(10) NOT NULL,
note_date datetime NOT NULL,
note_text text NULL ,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
) ENGINE=MyISAM;
Define foreign keys
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);
*populate.sql*
MySQL Crash Course
http://www.forta.com/books/06...
Example table population scripts
Populate customers table
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');
Populate vendors table
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');
Populate products table
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');
Populate orders table
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20005, '2005-09-01', 10001);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20006, '2005-09-12', 10003);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20007, '2005-09-30', 10004);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20008, '2005-10-03', 10005);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20009, '2005-10-08', 10001);
Populate orderitems table
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'ANV01', 10, 5.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'ANV02', 3, 9.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 3, 'TNT2', 5, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 4, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'JP2000', 1, 55);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'TNT2', 100, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'FC', 50, 2.50);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'OL1', 1, 8.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'SLING', 1, 4.49);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 4, 'ANV03', 1, 14.99);
Populate productnotes table
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(101, 'TNT2', '2005-08-17',
'Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(102, 'OL1', '2005-08-18',
'Can shipped full, refills not available.
Need to order new can if refill needed.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(103, 'SAFE', '2005-08-18',
'Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(104, 'FC', '2005-08-19',
'Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(105, 'TNT2', '2005-08-20',
'Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(106, 'TNT2', '2005-08-22',
'Matches not included, recommend purchase of matches or detonator (item DTNTR).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(107, 'SAFE', '2005-08-23',
'Please note that no returns will be accepted if safe opened using explosives.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(108, 'ANV01', '2005-08-25',
'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(109, 'ANV03', '2005-09-01',
'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(110, 'FC', '2005-09-01',
'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(111, 'SLING', '2005-09-02',
'Shipped unassembled, requires common tools (including oversized hammer).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(112, 'SAFE', '2005-09-02',
'Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(113, 'ANV01', '2005-09-05',
'Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(114, 'SAFE', '2005-09-07',
'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.'
);
mysql
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。