跳转错误

MySQL SQL 优化命令行&问题 SQL 抓取方式

Sun, 07 Feb 2021 18:12:32 +0800

墨墨导读：优化的道路永无止境。

对于数据库来说安装，部署几乎是一次性的。后期的管理和优化是持续性的工作。
对于MySQL来说，可以说90%问题都在SQL语句上面。从问题SQL的筛选和优化，在MySQL环境下常用哪些方式。(以下版本是MySQL8.0.23）

MySQL优化前置知识基础

1. MySQL优化基础

传统关系型数据库里面的优化器分为CBO(Cost_Based Potimizer)和RBO(Rule-Based Optimizer)两种

RBO :
RBO所用的判断规则是一组内置的规则，这些规则是硬编码在数据库的编码中的，RBO会根据这些规则去从SQL诸多的路径中来选择一条作为执行计划
RBO最大问题在于硬编码在数据库里面的一系列固定规则，来决定执行计划。并没有考虑目标SQL中所涉及的对象的实际数量，实际数据的分布情况，这样一旦规则不适用于该SQL，那么很可能选出来的执行计划就不是最优执行计划。

CBO :
CBO在会从目标诸多的执行路径中选择一个成本最小的执行路径来作为执行计划。这里的成本实际代表了MySQL根据相关统计信息计算出来目标SQL对应的步骤的IO，CPU等消耗。也就是意味着执行目标SQL所需要IO,CPU等资源的一个估计值。而成本值是根据索引，表，行的统计信息计算出来的。

MySQL里Cardinality是CBO特有的概念，它是指指定集合包含的记录数，说白了就是指定结果集的行数。Cardinality和成本值的估计息息相关，特别是IO资源消耗，随着该结果集的递增而递增。

通过SHOW INDEX结果中的列Cardinality来观察

2. MySQL索引特点：

很多技术文章谈到MySQL都提到索引组织表，必须有主键。

1. 索引组织表：

打个比喻，主键就是所有数据的大脑,所有的操作必须通过大脑来获取。所以SQL语句里有效使用索引是重之又重的手段。

2. 二级索引-回表操作

仅对二级索引获取结果是非常有效的，做到隔离的其他数据，但对于不在二级索引范围内的，就是回表操作，这部分需要谨慎考虑。

3. 索引-其他：

返回20%以上数据使用全表扫描数据；
数据集合小的表作为驱动表；
多个条件使用，组合索引；
索引键上不做运算；

备注：上面内容了解到MySQL成本计算方式，还有MySQL里索引组织表，应该有效使用索引，当捕捉到到一个SQL语句的时候通过什么方式优化。

MySQL优化命令行

1. EXPLAIN

在不执行实际SQL语句的情况下，EXPLAIN可以说dba，开发人员常用的SQL优化命令行，EXPLAIN适用于SELECT、DELETE、INSERT、REPLACE和UPDATE语句,当EXPLAIN语句一起使用时，MySQL会显示优化器关于语句执行计划的信息。也就是说，MySQL解释了它将如何处理这条语句，包括有关表如何连接和以何种顺序连接的信息，EXPLAIN会展示预执行计划信息.

语法如下：


EXPLAIN EXPLAIN    FORMAT=TREEEXPLAIN    FORMAT=JSONEXPLAIN    ANALYZE EXPLAIN    [options] FOR CONNECTION

EXPLAIN
MYSQL执行计划顺序普遍原则是：在所有组中，id值越大，优先级越高，越先执行，id如果相同，可以认为是一组，从上往下顺序执行
执行之前，要了解下显示字段代表意义：

EXPLAIN：

EXPLAIN FORMAT=TREE：

EXPLAIN FORMAT=JSON：

备注：3种方式算是预评估计划，但实际可能按照当时的情况，会变动。3种方式下多了一些字段：如 cost ,read_cost,eval_cost prefix_cost,data_read_per_join都是估计的成本相关信息，所以这些成本信息仅作为参考。毫秒为单位

EXPLAIN ANALYZE
MySQL 8.0.18引入了EXPLAIN ANALYZE，输出的信息是关于优化器估计执行成本和实际成本。
EXPLAIN ANALYZE可以用于SELECT语句，多表UPDATE和DELETE语句。

备注：结果集显示里多了actual time。为了确认这个是否实际成本。通过status观察值，EXPLAIN ANALYZE之后实际执行SELECT时 page是否也没变化。

EXPLAIN CONNECTION


EXPLAIN [options] FOR CONNECTION connection_id;

EXPLAIN FOR CONNECTION返回当前用于在给定连接中执行查询的解释信息。对于长时间正在执行的SQL可以通过这个方式，获取当前执行的执行计划，延迟等原因。
connection_id是连接标识符，从INFORMATION_SCHEMA PROCESSLIST表或SHOW PROCESSLIST语句获得.对于自己是connection id 无效。


##session1mysql> SELECT CONNECTION_ID();+-----------------+| CONNECTION_ID() |+-----------------+|              14 |+-----------------+1 row in set (0.00 sec)mysql> select * from employees;
#session2mysql> EXPLAIN FOR CONNECTION 14;+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299556 |   100.00 | NULL  |+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+


备注：第一次执行有效，后面执行多次无效，目前来看不完善的功能，但很便利。

2. PROFILE

PROFILE语句显示当前会话过程中执行的语句的资源使用情况。


SHOW PROFILE [type [, type] ... ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]] type: { ALL | BLOCK IO | CONTEXT SWITCHES | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE | SWAPS }

ALL 显示所有性能信息
BLOCK IO 显示块IO操作的次数
CONTEXT SWITCHES 显示上下文切换次数，不管是主动还是被动
CPU 显示用户CPU时间、系统CPU时间
IPC 显示发送和接收的消息数量
MEMORY [当前没有实现]
PAGE FAULTS 显示页错误数量
SOURCE 显示源码中的函数名称与位置
SWAPS 显示SWAP的次数


mysql> show variables like '%profiling%';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| have_profiling         | YES   || profiling              | OFF   || profiling_history_size | 15    |+------------------------+-------+mysql> SET profiling = 1;Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SELECT * FROM employees WHERE first_name like 'Ho%' limit 5;+--------+------------+------------+-----------+--------+------------+| emp_no | birth_date | first_name | last_name | gender | hire_date  |+--------+------------+------------+-----------+--------+------------+|  11105 | 1956-12-17 | Holgard    | McAlpine  | M      | 1990-01-13 ||  11484 | 1959-04-08 | Holgard    | Siepmann  | M      | 1995-01-22 ||  15162 | 1960-05-01 | Holgard    | Koblitz   | F      | 1990-08-07 ||  16372 | 1961-02-12 | Holgard    | Terkki    | M      | 1991-06-08 ||  16374 | 1963-05-23 | Holgard    | Nergos    | F      | 1987-05-21 |+--------+------------+------------+-----------+--------+------------+5 rows in set (0.00 sec)
mysql> SHOW PROFILES;+----------+------------+-------------------------------------------------------------+| Query_ID | Duration   | Query                                                       |+----------+------------+-------------------------------------------------------------+|        1 | 0.00568950 | SELECT * FROM employees WHERE first_name like 'Ho%' limit 5 |+----------+------------+-------------------------------------------------------------+1 row in set, 1 warning (0.00 sec)
mysql> SHOW PROFILE FOR QUERY 1;+--------------------------------+----------+| Status                         | Duration |+--------------------------------+----------+| starting                       | 0.004549 || Executing hook on transaction  | 0.000030 || starting                       | 0.000016 || checking permissions           | 0.000319 || Opening tables                 | 0.000055 || init                           | 0.000008 || System lock                    | 0.000009 || optimizing                     | 0.000011 || statistics                     | 0.000071 || preparing                      | 0.000205 || executing                      | 0.000357 || end                            | 0.000007 || query end                      | 0.000005 || waiting for handler commit     | 0.000011 || closing tables                 | 0.000008 || freeing items                  | 0.000022 || cleaning up                    | 0.000010 |+--------------------------------+----------+17 rows in set, 1 warning (0.00 sec)


mysql> SHOW PROFILE CPU FOR QUERY 1;

备注：资源方面影响SQL语句执行效率的时候，可通过这个方式获取信息，特别是IO，CPU，网络等方面的问题，能有效的定位。

3. OPTIMIZER_TRACE

优化器跟踪实际执行的过程，以帮助理解MySQL优化器所采取的决策和行动。

optimizer_trace：enabled：启用/禁用optimizer_trace功能， one_line：决定了跟踪信息的存储方式，为on表示使用单行存储，否则以JSON树的标准展示形式存储。
optimizer_trace_features：该变量中存储了跟踪信息中可控的打印项，可以通过调整该变量，greedy_search,range_optimizerdynamic_range,repeated_subselect
optimizer_trace_max_mem_size ：optimizer_trace内存的大小，如果跟踪信息超过这个大小，信息将会被截断
optimizer_trace_offset：则是约束偏移量。和 LIMIT 一样，optimizer_trace_offset 从0开始计算

使用方式：


1.SET OPTIMIZER_TRACE="enabled=on";2.执行 sql 语句3. SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE limit 30 \G;4.关闭 SET OPTIMIZER_TRACE="enabled=off";

TRACE：过程

备注：3个大步骤，9个子步骤把每个过程都列出来。
在主从架构下（所有条件相等下）碰到SQL执行得到不一样的执行时间，可通过这个方式查找原因。

注意：
必须真正的执行才能得到结果，所以合理使用：

EXPLAIN ANALYZE；
PROFILE；
OPTIMIZER_TRACE；

问题SQL语句抓取

MySQL怎样抓有问题的sql 语句。目前接触的经验，可以归纳为3个方面：

慢日志；
performance_schema系统性能表；
业务的一些反馈，死锁检查，jdbc探针，网络流量镜像等方式；
下面只普遍的前2种方式说明。

1. MySQL慢日志

最普遍常用的方式，当语句执行时间较长时，通过日志的方式进行记录，这种方式就是慢查询的日志。

开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

参数说明：

慢日志分析方式：
1）使用MySQL官方提供的开源工具mysqldumpslow进行分析


 mysqldumpslow -t 10  /data/mysql/mysql-slow.log  #显示出慢查询日志中最慢的10条sql

2）perconal提供的pt-query-digest工具进行分析


 pt-query-digest  /data/mysql/mysql-slow.log

备注：
第一部分：显示出了日志的时间范围，以及总的sql数量和不同的sql数量。
第二部分：显示出统计信息。
第三部分：每一个sql具体的分析

如何通过pt-query-digest 慢查询日志发现有问题的sql

查询次数多且每次查询占用时间长的sql
通常为pt-query-digest分析的前几个查询
IO消耗大的sql
注意pt-query-digest分析中的Rows examine项
索引命中统计
注意pt-query-digest分析中Rows examine（扫描行数）和 Rows sent （发送行数）的对比，如果扫描行数远远大于发送行数，则说明索引命中率并不高。

2. events_statements_summary_by_digest统计的SQL语句

性能监控performance_schema下记录SQL执行情况：
events_statements_summary_by_digest：sql语句汇总统计数据，表结构说明如下:

备注：数据行数performance_schema_digests_size控制，默认是10000，如果超过这个最大值，新的sql语句无法插入。

4. 总结

SQL语句优化的需要了解MySQL的基础架构和一些体系架构方面的知识，再结合提供的命令行进行优化，也少不了问题SQL抓取方法。

从小的细节开始关注。

作者

崔虎龙：云和恩墨MySQL技术顾问，长期服务于金融、游戏、物流等行业的数据中心，设计数据存储架构，并熟悉数据中心运营管理的流程及规范，自动化运维等。擅长MySQL、Redis、MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自学通过了MySQL OCP 5.6和MySQL OCP 5.7认证。2年多开发经验，10年数据库运维工作经验，其中专职做MySQL工作8年；曾经担任过项目经理、数据库经理、数据仓库架构师、MySQL技术专家、DBA等职务；涉及行业：金融（银行、理财）、物流、游戏、医疗、重工业等。

墨天轮原文链接：https://www.modb.pro/db/43315（复制到浏览器或者点击“阅读原文”立即查看）

推荐阅读：267页！2020年度数据库技术年刊

推荐下载：2020数据技术嘉年华PPT下载

2020数据技术嘉年华近50个PPT下载、视频回放已上传墨天轮平台，可在“数据和云”公众号回复关键词“2020DTC”获得！

视频号，新的分享时代，关注我们，看看有什么新发现？

数据和云

ID：OraNews

如有收获，请划至底部，点击“在看”，谢谢！

点击下图查看更多 ↓

云和恩墨大讲堂 | 一个分享交流的地方

长按，识别二维码，加入万人交流社群

请备注：云和恩墨大讲堂

点个“在看”

你的喜欢会被看到❤

本篇文章来源于微信公众号: 数据和云

CentOS 落幕，将于2021年底结束维护

Sun, 07 Feb 2021 14:07:06 +0800

官方最新消息：

译文：

CentOS项目的未来是CentOS Stream，明年，我们将把重点从重建Red Hat Enterprise Linux（RHEL）的CentOS Linux转移到CentOS Stream，后者紧跟当前RHEL版本发布。作为对RHEL 8的重建，CentOS Linux 8将于2021年底结束。CentOSStream在此日期之后继续，作为Red Hat Enterprise Linux的上游（开发）分支。

同时，我们了解到你们中的许多人都对CentOS Linux 7进行了深入的投资，我们将在RHEL 7整个生命周期的剩余时间内继续生产该版本。

CentOS Stream也将成为CentOS特殊兴趣小组（SIG）之间合作重大转变的核心。这样可以确保SIG在开发和测试RHEL的下一版本。这也为SIG提供了明确的单一目标，而不必为两个发行版进行构建和测试。它给CentOS贡献者社区带来了对RHEL未来的巨大影响。并且消除了在Linux发行生态系统中“ CentOS”的含义的困惑。

当CentOS Linux 8（RHEL8的重建）结束时，最好的选择是迁移到CentOS Stream 8，这是CentOS Linux 8的一个很小的变化，并具有像传统CentOS Linux版本一样的定期更新。如果您在生产环境中使用CentOS Linux 8，并且担心CentOS Stream不能满足您的需求，我们建议您与Red Hat联系以获取选项。

当您弄清楚项目重点的这种转变可能对您有何影响时，我们将提供一个常见问题解答来帮助您解决信息和计划需求。

本篇文章来源于微信公众号: Linux运维交流社区

红旗Linux桌面操作系统11来了

Sun, 07 Feb 2021 14:04:28 +0800

据红旗 Linux 官方，红旗 Linux 桌面操作系统 V11 社区预览版近期发布，2021 年 1 月 10 日将开放下载。

红旗 Linux 桌面操作系统 V11 全面优化、广泛兼容、稳定可靠、智能交互。良好的硬件兼容，支持多款国产自主 CPU 品牌，同时还具有丰富的外设支持及海量的易用生态软件，打造全新的 UI 设计风格，带来更灵动的视觉效果和便捷的操作体验。

良好的硬件兼容

兼容x86、ARM、MIPS、SW等

CPU指令集架构；

支持国产自主CPU品牌：

龙芯、申威、鲲鹏、麒麟、飞腾、

海光、兆芯。

丰富的外设支持

兼容主流厂商的打印机、扫描仪、

摄像头、高拍仪、读卡器、Raid卡、

HBA卡、生物识别设备等外设，

满足多场景使用需求。

易用的软件生态

满足用户在各场景下的使用需求，

提供办公学习、社交沟通、编程开发、

图片处理等高频应用软件，

更有游戏、影音等娱乐软件可供选择。

全新的UI设计风格

从文字到图形，从终端到窗口，

全新的布局和图标设计，

带来更灵动的视觉效果和便捷的操作体验。

版本信息

版本11.0.1-Alpha

基于debian 10.6开发，

商用维护的内核版本(4.19）；

提供内核自主升级优化解决方案，

满足开发者需求；

KDE框架版本5.54.0，

KDE Plasma版本5.14.5；

支持Java 11，详见JDK 11；

桌面环境和系统基础应用使用DTK和QT5进行研发，

QT版本5.11.3；

提供终端域管平台解决方案，

全方位解决终端管理问题；

支持移动安装、网络安装、

Minimal Install、

UEFI模式等方式安装系统；

提供引导权限控制、安全启动机制、

用户权限限制、应用证书签名等

系统级安全策略。

预置壁纸

携手视觉中国签约摄影师，

多种风格壁纸资源，

营造不一样的桌面风景。

往期推荐

本篇文章来源于微信公众号: Linux技术社区

Linux 下自动应答工具 Expect 使用指南

Sun, 07 Feb 2021 14:00:00 +0800

公众号关注「奇妙的 Linux 世界」

设为「星标」，每天带你玩转 Linux ！

Expect 是用于自动化交互式应用程序

1. 软件介绍

现代的 Shell 对程序提供了最小限度的控制(程序的开始/停止/关闭等)，而把交互的特性留给了用户。这意味着有些程序，你不能非交互的运行，比如说 passwd 命令。有一些程序可以非交互的运行，但在很大程度上丧失了灵活性，比如说 fsck 命令。这表明 Unix 的工具构造逻辑开始出现问题。Expect 恰恰填补了其中的一些裂痕，解决了在 Unix 环境中长期存在着的一些问题。

Expect 使用 Tcl 作为语言核心，不管程序是交互和还是非交互的，Expect 都能运用。Tcl 实际上是一个子程序库，这些子程序库可以嵌入到程序里从而提供语言服务。最终的语言有点象一个典型的 Shell 语言。里面有给变量赋值的 set 命令，控制程序执行的 if, for, continue 等命令，还能进行普通的数学和字符串操作。

Expect 是在 Tcl 基础上创建起来的并且还提供了一些 Tcl 所没有的命令：

spawn命令激活一个 Unix 程序来进行交互式的运行
send命令向进程发送字符串
expect命令等待进程的某些字符串且支持正规表达式并能同时等待多个字符串

# 命令格式
expect patlist1 action1 patlist2 action2.....

该命令一直等到当前进程的输出和以上的某一个模式相匹配，或者等到时间超过一个特定的时间长度，或者等到遇到了文件的结束为止。每一个 patlist 都由一个模式或者模式的表(lists)组成。如果有一个模式匹配成功，相应的 action 就被执行，执行的结果从 expect 返回。

被精确匹配的字符串(或者当超时发生时，已经读取但未进行匹配的字符串)被存贮在变量 expect_match 里面。如果 patlist 是 eof 或者 timeout 的情况，则发生文件结束或者超时时才执行相应的 action 动作。一般超时的默认值是 10 秒，但可以用类似 "set timeout 30" 之类的命令把超时时值设定为 30 秒。

# 下面的一个程序段是从一个有关登录的脚本里面摘取的
# abort是在脚本的别处定义的过程，而其他的action使用类似与C语言的Tcl原语
expect "*welcome*" break
  "*busy*" {print busy;continue}
  "*failed*" abort
  timeout abort

模式是通常的 C Shell 风格的正规表达式，模式必须匹配当前进程的从上一个 expect 或者 interact 开始的所有输出(所以统配符*****使用的非常的普遍)。但是，一旦输出超过 2000 个字节，前面的字符就会被忘记，这可以通过设定 match_max 的值来改变。

字符可以使用反斜杠来单独的引用，反斜杠也被用于对语句的延续，如果不加反斜杠的话，语句到一行的结尾处就结束了。这和 Tcl 也是一致的。Tcl 在发现有开的单引号或者开的双引号时都会继续扫描。而且，分号可以用于在一行中分割多个语句。这乍听起来有点让人困惑，但是，这是解释性语言的风格，但是，这确实是 Tcl 的不太漂亮的部分。

spawn passwd [lindex $argv 1]

expect eof               {exit 1}
    timeout              {exit 2}
    "*No such user.*"    {exit 3}
    "*New password:"
send "[index $argv 2]\r"
expect eof                   {exit 4}
    timeout                  {exit 2}
    "*Password too long*"    {exit 5}
    "*Password too short*"   {exit 5}
    "*Retype new password:"
send "[index $argv 3] "
expect timeout               {exit 2}
    "*Mismatch*"             {exit 6}
    "*Password unchanged*"   {exit 7}
    " "
expect timeout               {exit 2}
    "*"                      {exit 6}
    eof

这个脚本退出时用一个数字来表示所发生的情况。0 表示 passwd 程序正常运行，1 表示非预期的死亡，2 表示锁定，等等。使用数字是为了简单起见。expect 返回字符串和返回数字是一样简单的，即使是派生程序自身产生的消息也是一样的。实际上，典型的做法是把整个交互的过程存到一个文件里面，只有当程序的运行和预期一样的时候才把这个文件删除。否则这个 log 被留待以后进一步的检查。

这个 passwd 检查脚本被设计成由别的脚本来驱动。这第二个脚本从一个文件里面读取参数和预期的结果。对于每一个输入参数集，它调用第一个脚本并且把结果和预期的结果相比较。(因为这个任务是非交互的，一个普通的老式 shell 就可以用来解释第二个脚本)。比如说，一个 passwd 的数据文件很有可能就象下面一样。

passwd.exp    3    bogus    -        -
passwd.exp    0    fred    abledabl    abledabl
passwd.exp    5    fred    abcdefghijklm    -
passwd.exp    5    fred    abc        -
passwd.exp    6    fred    foobar        bar
passwd.exp    4    fred    ^C        -

第一个域的名字是要被运行的回归脚本。第二个域是需要和结果相匹配的退出值。第三个域就是用户名。第四个域和第五个域就是提示时应该输入的密码。减号仅仅表示那里有一个域，这个域其实绝对不会用到。在第一个行中，bogus 表示用户名是非法的，因此 passwd 会响应说：没有此用户。expect 在退出时会返回 3，3 恰好就是第二个域。在最后一行中，^C 就是被切实的送给程序来验证程序是否恰当的退出。

2. 工具安装

源代码和下载地址都是由 Linux 软件基金会维护的(sourceforge)

# 因为Expect需要Tcl编程语言的支持
$ sudo yum install -y gcc
$ sudo yum install -y tcl tclx tcl-devel

# centos
$ sudo yum  install expect

# 因为Expect需要Tcl编程语言的支持
$ sudo apt install -y gcc
$ sudo apt install tcl


# ubuntu
$ sudo apt install expect

# 下载源代码包
# 官网主页地址: http://sourceforge.net/projects/expect/
wget "https://sourceforge.net/projects/expect/files/Expect/5.45.4/expect5.45.4.tar.gz/download"

# 源代码编译
$ sudo ./configure \
    --with-tcl=/usr/lib \
    --with-tclinclude=/usr/include/tcl-private/generic

# 源代码安装
$ sudo make && make install

3. 基础知识

主要介绍常见的 4 个命令的使用方式

我们知道，send 命令用于发送信息到进程中，expect 命令则是根据进程反馈的信息进行对应逻辑的交互的。而 spawn 命令后的 send 和 expect 命令其实都是和使用 spawn 命令打开的进程进行交互的。
需要说明的是 interact 命令其实用的不多，一般情况下使用 spawn、send 和 expect 命令就可以很好的完成任务了。但在一些特殊场合下，使用 interact 命令还是能够发挥很好作用的。interact 命令主要用于退出自动化进入人工交互。比如我们使用 spawn、send 和 expect 命令完成了 ftp 登陆主机，执行下载文件任务，但是我们希望在文件下载结束以后，仍然可以停留在 ftp 命令行状态，以便手动的执行后续命令，此时使用 interact 命令就可以很好的完成这个任务。

编号	命令	作用
1	`send`	send 命令接收一个字符串并将该参数发送到进程中
2	`expect`	expect 通常用来等待进程的反馈再发送对应的交互命令
3	`spawn`	spawn 命令用来启动新的进程
4	`interact`	允许退出自动化进入人工交互

4. 控制结构

介绍 TCL 语言的控制结构

[1] if else

#!/usr/bin/expect

set timeout 10
set alias_host [lindex $argv 0]
set b1_password ASJZOMxlgM^9
set b2_password a0yDuePSLUGM

if {$argc!=1} {
    echo "请输入想要远程连接的服务器: [b1|b2]"
    exit 1
}

if {$alias_host=="b1"} {
    spawn ssh escape@192.168.100.100 -p 22
    expect "*password*" {send "$b1_password\r"}
    interact
} elseif {$alias_host=="b2"} {
    spawn ssh escape@192.168.100.101 -p 22
    expect "*password*" {send "$b2_password\r"}
    interact
} else {
    send "请输入想要远程连接的服务器: [b1|b2]"
}

[2] switch

#!/usr/bin/expect

set timeout 10
set alias_host [lindex $argv 0]
set b1_password ASJZOMxlgM^9
set b2_password a0yDuePSLUGM

switch -glob -- $file1 {
    b1 {
        spawn ssh escape@192.168.100.100 -p 22
        expect "*password*" {send "$b1_password\r"}
        interact
    }
    b2 {
        spawn ssh escape@192.168.100.101 -p 22
        expect "*password*" {send "$b2_password\r"}
        interact
}

[3] while

#!/usr/bin/expect

set test 0
while {$test<10} {
    set test [expr {$test + 1}]
    if {$test > 7}
        break
    if "$test < 3"
        continue
}

[4] catch

#!/usr/bin/expect

proc Error {} {
    error "This is a error for test"
}

catch Error test
puts $test

5. 简单使用

下面是一些简单的示例代码，主要帮助我们理解 expect 的使用。

[1] 系统指定修改用户密码

[escape@linuxworld ~]$ passwd
Changing password for user escape.
Changing password for escape.
(current) UNIX password:
New password:
Retype new password:
passwd: all authentication tokens updated successfully.

# password.exp
#!/usr/bin/expect -d

set timeout 30

spawn passwd [lindex $argv 1]
set password [lindex $argv 2]
expect "*New password:*" {send "$password\r"}
expect "*Retype new password:*" {send "$password\r"}
expect eof

[2] 登陆远程服务器并停留在远程服务器上

# login.exp
#!/usr/bin/expect

set timeout 30  # 设置超时时间
set host "100.200.200.200"
set username "root"
set password "123456"

# 给ssh运行进程加个壳用来传递交互指令
spawn ssh $username@$host
# 判断上次输出结果里是否包含指定的字符串
expect {
    # exp_continue表示继续执行下一步
    "*yes/no" {send "yes\r";exp_continue}
    # 匹配即可发送密码到对应进程中
    "*password*" {send "$password\r"}
}
# 执行完成后保持交互状态
interact

[3] 传输参数执行登并停留在远程服务器上

# login.exp
#!/usr/tcl/bin/expect

# $argc表示参数个数
if {$argc < 3} {
    puts "Usage:cmd <host>:<username> -p <port>"
    exit 1
}

# 获取第几个参数的内容
set timeout 30
set host [lindex $argv 0]
set username [lindex $argv 1]
set password [lindex $argv 2]
set port [lindex $argv 3]

spawn ssh $username@$host -p $port
expect "*password*" {send "$password\r"}
interact

[4] 在 shell 脚本中使用 expect

# [1] 直接添加expect脚本文件

#!/bin/bash
read -p "please input you user:" -t30 remote_user
read -p "please input you ip:" -t30 remote_ip
read -p "please input you port:" -t30 remote_port
echo "ssh $remote_user:$remote_ip -p $remote_port"
./login.exp $remote_user $remote_ip $remote_port

# [2] 在shell脚本直接写入expect命令

#!/bin/bash
read -p "please input you user:" -t30 remote_user
read -p "please input you ip:" -t30 remote_ip
read -p "please input you port:" -t30 remote_port
echo "ssh $remote_user:$remote_ip -p $remote_port"

expect -d <<EOF
spawn ssh $remote_ip
expect {
"*yes/no" {send "yes\r";exp_continue}
"*password:" {send "Xuansiwei123!\r"}
}
exit
expect eof;
EOF

6. 高级示例

弄懂下面的高级玩法，就可以应对日常的工作使用了。

[1] 自动 telnet 会话

#!/usr/bin/expect -f

set ip [lindex $argv 0 ]         # 接收第1个参数,作为IP
set userid [lindex $argv 1 ]     # 接收第2个参数,作为userid
set mypassword [lindex $argv 2 ] # 接收第3个参数,作为密码
set mycommand [lindex $argv 3 ]  # 接收第4个参数，作为命令
set timeout 10                   # 设置超时时间

# 向远程服务器请求打开一个telnet会话，并等待服务器询问用户名
spawn telnet $ip
    expect "username:"
    # 输入用户名，并等待服务器询问密码
    send "$userid\r"
    expect "password:"
    # 输入密码，并等待键入需要运行的命令
    send "$mypassword\r"
    expect "%"
    # 输入预先定好的密码，等待运行结果
    send "$mycommand\r"
    expect "%"
    # 将运行结果存入到变量中，显示出来或者写到磁盘中
    set results $expect_out(buffer)
    # 退出telnet会话，等待服务器的退出提示EOF
    send "exit\r"
    expect eof

[2] 自动建立 FTP 会话

#!/usr/bin/expect -f

set ip [lindex $argv 0 ]         # 接收第1个参数,作为IP
set userid [lindex $argv 1 ]     # 接收第2个参数,作为Userid
set mypassword [lindex $argv 2 ] # 接收第3个参数,作为密码
set timeout 10                   # 设置超时时间

# 向远程服务器请求打开一个FTP会话，并等待服务器询问用户名
spawn ftp $ip
    expect "username:"
    # 输入用户名，并等待服务器询问密码
    send "$userid\r"
    expect "password:"
    # 输入密码，并等待FTP提示符的出现
    send "$mypassword\r"
    expect "ftp>"
    # 切换到二进制模式，并等待FTP提示符的出现
    send "bin\r"
    expect "ftp>"
    # 关闭ftp的提示符
    send "prompt\r"
    expect "ftp>"
    # 下载所有文件
    send "mget *\r"
    expect "ftp>"
    # 退出此次ftp会话，并等待服务器的退出提示EOF
    send "bye\r"
    expect eof

[3] 自动登录 ssh

#!/usr/bin/expect -f

set ip [lindex $argv 0 ]         # 接收第1个参数,作为IP
set username [lindex $argv 1 ]   # 接收第2个参数,作为username
set mypassword [lindex $argv 2 ] # 接收第3个参数,作为密码
set timeout 10                   # 设置超时时间

spawn ssh $username@$ip       # 发送ssh请求
expect {                      # 返回信息匹配
    "*yes/no" { send "yes\r"; exp_continue}  # 第一次ssh连接会提示yes/no,继续
    "*password:" { send "$mypassword\r" }    # 出现密码提示,发送密码
}
interact        # 交互模式,用户会停留在远程服务器上面

[4] 自动登录 ssh 执行命令

#!/usr/bin/expect

set IP     [lindex $argv 0]
set USER   [lindex $argv 1]
set PASSWD [lindex $argv 2]
set CMD    [lindex $argv 3]

spawn ssh $USER@$IP $CMD
expect {
    "(yes/no)?" {
        send "yes\r"
        expect "password:"
        send "$PASSWD\r"
        }
    "password:" {send "$PASSWD\r"}
    "* to host" {exit 1}
    }
expect eof

[5] 批量登录 ssh 服务器执行操作范例 => for 循环

#!/usr/bin/expect

for {set i 10} {$i <= 12} {incr i} {
    set timeout 30
    set ssh_user [lindex $argv 0]
    spawn ssh -i .ssh/$ssh_user abc$i.com

    expect_before "no)?" {
    send "yes\r" }
    sleep 1
    expect "password*"
    send "hello\r"
    expect "*#"
    send "echo hello expect! > /tmp/expect.txt\r"
    expect "*#"
    send "echo\r"
}

exit

[6] 批量登录 ssh 并执行命令 => foreach 语法

#!/usr/bin/expect

if {$argc!=2} {
    send_user "usage: ./expect ssh_user password\n"
    exit
}

foreach i {11 12} {
    set timeout 30
    set ssh_user [lindex $argv 0]
    set password [lindex $argv 1]
    spawn ssh -i .ssh/$ssh_user root@xxx.yy.com
    expect_before "no)?" {
    send "yes\r" }
    sleep 1

    expect "Enter passphrase for key*"
    send "password\r"
    expect "*#"
    send "echo hello expect! > /tmp/expect.txt\r"
    expect "*#"
    send "echo\r"
}

exit

[7] 批量 ssh 执行命令 => 用 shell 调用 tclsh 方式、多进程同时执行

#!/bin/sh

exec tclsh $0 "$@"
package require Expect
set username [lindex $argv 0]
set password [lindex $argv 1]
set argv [lrange $argv 2 end]
set prompt "(%|#|\\$) $"

foreach ip $argv {
    spawn ssh -t $username@$ip sh
    lappend ids $spawn_id
}

expect_before -i ids eof {
    set index [lsearch $ids $expect_out(spawn_id)]
    set ids [lreplace $ids $index $index]
    if [llength $ids] exp_continue
}
expect -i ids "(yes/no)\\?" {
    send -i $expect_out(spawn_id) yes\r
    exp_continue
} -i ids "Enter passphrase for key" {
    send -i $expect_out(spawn_id) \r
    exp_continue
} -i ids "assword:" {
    send -i $expect_out(spawn_id) $password\r
    exp_continue
} -i ids -re $prompt {
    set spawn_id $expect_out(spawn_id)
    send "echo hello; exit\r"
    exp_continue
} timeout {
    exit 1
}

[8] 使用 ssh 自动登录 expect 脚本 => ssh.expect

The authenticity of host '192.168.xxx.xxx (192.168.xxx.xxx)' can't be established.
RSA key fingerprint is 25:e8:4c:89:a3:b2:06:ee:de:66:c7:7e:1b:fa:1c:c5.
Are you sure you want to continue connecting (yes/no)?

Warning: Permanently added '192.168.xxx.xxx' (RSA) to the list of known hosts.
Enter passphrase for key '/data/key/my_dsa':

Last login: Sun Jan 26 13:39:37 2019 from 192.168.xxx.xxx
[root@master003 ~]#
root@192.168.xxx.xxx's password:

Last login: Thu Jan 23 17:50:43 2019 from 192.168.xxx.xxx

#!/usr/bin/expect -f

if {$argc < 4} {
    send_user "Usage:\n  $argv0 IPaddr User Passwd Port Passphrase\n"
    puts stderr "argv error!\n"
    sleep 1
    exit 1
}

set timeout 30
set ip         [lindex $argv 0 ]
set user       [lindex $argv 1 ]
set passwd     [lindex $argv 2 ]
set port       [lindex $argv 3 ]
set passphrase [lindex $argv 4 ]

if {$port == ""} {
    set port 22
}

spawn ssh $user@$ip -p $port

expect_before "(yes/no)\\?" {
    send "yes\r"}

expect \
"Enter passphrase for key*" {
    send "$passphrase\r"
    exp_continue
} " password:?" {
    send "$passwd\r"
    exp_continue
} "*\[#\\\$]" {
    interact
} "* to host" {
    send_user "Connect faild!"
    exit 2
} timeout {
    send_user "Connect timeout!"
    exit 2
} eof {
    send_user "Lost connect!"
    exit
}

7. 参考博客

expect - 教程中文版
expect - 正则模式说明
expect - 自动交互脚本

本文转载自：「 Escape 的博客」，原文：https://tinyurl.com/y4wa98s9，版权归原作者所有。欢迎投稿，投稿邮箱: editor@hi-linux.com。

你可能还喜欢

点击下方图片即可阅读

一文读懂 Kubernetes 容器网络

点击上方图片，打开小程序，加入「玩转 Linux」圈子

更多有趣的互联网新鲜事，关注「奇妙的互联网」视频号全了解！

本篇文章来源于微信公众号: 奇妙的Linux世界

Oracle云时代MySQL HTAP解决方案

Sun, 07 Feb 2021 13:55:03 +0800

Oracle Cloud 在2020年终于大张旗鼓的上线了推动，终于搞出来一个本地MySQL和线上MySQL大差异点云上HTAP MySQL方案：MySQL HeatWave。架构如下：

在MySQL HeatWave架构下实现了数据请求单一入口，数据写入先写InnoDB，如果该表定义为Rapid引擎，同时从会InnoDB则推送给HeatWave; 对于SELECT请求，优化器会判断，如果从InnoDB中请求快，则从InnoDB层请求数据返回，如果涉及到复杂运算则从HeatWave中请求及返回。可以说这个结构是行(InnoDB)+列（HeatWave)混合存储的结构，同一个架构完美解决OLTP，OLAP需求（感觉也是一个高富帅的解决方法），从油管上看BenchMark基本是同行业无敌。

这里大概也罗列一下HeatWave的一些限制，供使用者一些参考：

1. HeatWave Cluster目前支持最多24个节点，每个节点最大内存420G，共9T数据，Rapid引擎数据在内存中，Youtube上有一些详细介绍，自行翻墙观看。

2. HeatWave只能处理SELECT请求

3. 表的数据需要自动用HeatWave处理的，引擎需要改为：rapid为第二个引擎，数据还是持久化在InnoDB中, 对于不支持的列需要定义加上：not secondary 参考支持的数据类型：

https://dev.mysql.com/doc/heatwave/en/heatwave-supported-data-types.html

4. 表引擎没有指定为Rapid的，如果想用HeatWave，需要手工加载到HeatWave中，如：alter table TBname secondary_load;

因为这个架构是基于云上基础能力开发的架构，目前没办法线下部署想体验，只能用Oracle Cloud上的MySQL Service，在Oracle MySQL Service中也可以看到低于9T的数据，HTAP基本随意造了。

如果你没办法使用Oracle Cloud，也可以考虑使用clickhouse扮演一部分这个功能，参考：https://clickhouse.tech/docs/en/engines/database-engines/materialize-mysql/

用好这个功能也可以给MySQL手工安装一个OLAP的翅膀。

本篇文章来源于微信公众号: MySQLBeginner

MySQL查看及杀掉链接方法大全

Sun, 07 Feb 2021 10:33:26 +0800

前言：

在数据库运维过程中，我们时常会关注数据库的链接情况，比如总共有多少链接、有多少活跃链接、有没有执行时间过长的链接等。数据库的各种异常也能通过链接情况间接反应出来，特别是数据库出现死锁或严重卡顿的时候，我们首先应该查看数据库是否有异常链接，并杀掉这些异常链接。本篇文章将主要介绍如何查看数据库链接及如何杀掉异常链接的方法。

1.查看数据库链接

查看数据库链接最常用的语句就是 show processlist 了，这条语句可以查看数据库中存在的线程状态。普通用户只可以查看当前用户发起的链接，具有 PROCESS 全局权限的用户则可以查看所有用户的链接。

show processlist 结果中的 Info 字段仅显示每个语句的前 100 个字符，如果需要显示更多信息，可以使用 show full processlist 。同样的，查看 information_schema.processlist 表也可以看到数据库链接状态信息。

# 普通用户只能看到当前用户发起的链接
mysql> select user();
+--------------------+
| user()             |
+--------------------+
| testuser@localhost |
+--------------------+
1 row in set (0.00 sec)

mysql> show grants;
+----------------------------------------------------------------------+
| Grants for testuser@%                                                |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%'                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `testdb`.* TO 'testuser'@'%' |
+----------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show processlist;
+--------+----------+-----------+--------+---------+------+----------+------------------+
| Id     | User     | Host      | db     | Command | Time | State    | Info             |
+--------+----------+-----------+--------+---------+------+----------+------------------+
| 769386 | testuser | localhost | NULL   | Sleep   |  201 |          | NULL             |
| 769390 | testuser | localhost | testdb | Query   |    0 | starting | show processlist |
+--------+----------+-----------+--------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

mysql> select * from information_schema.processlist;
+--------+----------+-----------+--------+---------+------+-----------+----------------------------------------------+
| ID     | USER     | HOST      | DB     | COMMAND | TIME | STATE     | INFO                                         |
+--------+----------+-----------+--------+---------+------+-----------+----------------------------------------------+
| 769386 | testuser | localhost | NULL   | Sleep   |  210 |           | NULL                                         |
| 769390 | testuser | localhost | testdb | Query   |    0 | executing | select * from information_schema.processlist |
+--------+----------+-----------+--------+---------+------+-----------+----------------------------------------------+
2 rows in set (0.00 sec)

# 授予了PROCESS权限后，可以看到所有用户的链接
mysql> grant process on *.* to 'testuser'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants;
+----------------------------------------------------------------------+
| Grants for testuser@%                                                |
+----------------------------------------------------------------------+
| GRANT PROCESS ON *.* TO 'testuser'@'%'                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `testdb`.* TO 'testuser'@'%' |
+----------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show processlist;
+--------+----------+--------------------+--------+---------+------+----------+------------------+
| Id     | User     | Host               | db     | Command | Time | State    | Info             |
+--------+----------+--------------------+--------+---------+------+----------+------------------+
| 769347 | root     | localhost          | testdb | Sleep   |   53 |          | NULL             |
| 769357 | root     | 192.168.85.0:61709 | NULL   | Sleep   |  521 |          | NULL             |
| 769386 | testuser | localhost          | NULL   | Sleep   |  406 |          | NULL             |
| 769473 | testuser | localhost          | testdb | Query   |    0 | starting | show processlist |
+--------+----------+--------------------+--------+---------+------+----------+------------------+
4 rows in set (0.00 sec)

通过 show processlist 所得结果，我们可以清晰了解各线程链接的详细信息。具体字段含义还是比较容易理解的，下面具体来解释下各个字段代表的意思：

Id：就是这个链接的唯一标识，可通过 kill 命令，加上这个Id值将此链接杀掉。
User：就是指发起这个链接的用户名。
Host：记录了发送请求的客户端的 IP 和端口号，可以定位到是哪个客户端的哪个进程发送的请求。
db：当前执行的命令是在哪一个数据库上。如果没有指定数据库，则该值为 NULL 。
Command：是指此刻该线程链接正在执行的命令。
Time：表示该线程链接处于当前状态的时间。
State：线程的状态，和 Command 对应。
Info：记录的是线程执行的具体语句。

当数据库链接数过多时，筛选有用信息又成了一件麻烦事，比如我们只想查某个用户或某个状态的链接。这个时候用 show processlist 则会查找出一些我们不需要的信息，此时使用 information_schema.processlist 进行筛选会变得容易许多，下面展示几个常见筛选需求：

# 只查看某个ID的链接信息
select * from information_schema.processlist where id = 705207;

# 筛选出某个用户的链接
select * from information_schema.processlist where user = 'testuser';

# 筛选出所有非空闲的链接
select * from information_schema.processlist where command != 'Sleep';

# 筛选出空闲时间在600秒以上的链接
select * from information_schema.processlist where command = 'Sleep' and time > 600;

# 筛选出处于某个状态的链接
select * from information_schema.processlist where state = 'Sending data';

# 筛选某个客户端IP的链接
select * from information_schema.processlist where host like '192.168.85.0%';

2.杀掉数据库链接

如果某个数据库链接异常，我们可以通过 kill 语句来杀掉该链接，kill 标准语法是：KILL [CONNECTION | QUERY] processlist_id;

KILL 允许使用可选的 CONNECTION 或 QUERY 修饰符：

KILL CONNECTION 与不含修改符的 KILL 一样，它会终止该 process 相关链接。
KILL QUERY 终止链接当前正在执行的语句，但保持链接本身不变。

杀掉链接的能力取决于 SUPER 权限：

如果没有 SUPER 权限，则只能杀掉当前用户发起的链接。
具有 SUPER 权限的用户，可以杀掉所有链接。

遇到突发情况，需要批量杀链接时，可以通过拼接 SQL 得到 kill 语句，然后再执行，这样会方便很多，分享几个可能用到的杀链接的 SQL ：

# 杀掉空闲时间在600秒以上的链接，拼接得到kill语句
select concat('KILL ',id,';') from information_schema.`processlist` 
where command = 'Sleep' and time > 600;

# 杀掉处于某个状态的链接，拼接得到kill语句
select concat('KILL ',id,';') from information_schema.`processlist` 
where state = 'Sending data';

select concat('KILL ',id,';') from information_schema.`processlist` 
where state = 'Waiting for table metadata lock';

# 杀掉某个用户发起的链接，拼接得到kill语句
select concat('KILL ',id,';') from information_schema.`processlist` 
 user = 'testuser';

这里提醒下，kill 语句一定要慎用！特别是此链接执行的是更新语句或表结构变动语句时，杀掉链接可能需要比较长时间的回滚操作。

总结：

本篇文章讲解了查看及杀掉数据库链接的方法，以后怀疑数据库有问题，可以第一时间看下数据库链接情况。

最后祝各位新年快乐！年前最后一篇技术文，点个在看可好？等着我，年前有波红包抽奖要来的！！

推荐阅读

（点击标题可跳转阅读）

MySQL字段类型最全解析

简单说几个MySQL高频面试题

年底了，你的数据库密码安全吗

- End -

动动手指转发、在看

是对我最大的鼓励

本篇文章来源于微信公众号: MySQL技术

null调整为not null default xxx，不得不注意的坑

Fri, 29 Jan 2021 20:27:56 +0800

最近碰到一个case，值得分享一下。

现象

一个DDL，将列的属性从null调整为not null default xxx，

alter table slowtech.t1 modify name varchar(10) not null default 'slowtech';

通过平台执行（平台调用的是pt-online-schema-change）。

但在执行的过程中，业务SQL报错，提示“ERROR 1048 (23000): Column 'name' cannot be null”。

在剖析具体的问题之前，首先，我们看看pt-online-schema-change的原理。

PT-OSC的实现原理

从原理图中可以看到，

1. 对于全量数据的同步，pt-online-schema-change是以chunk为单位分批来拷贝的。

2. 对于增量数据的同步，pt-online-schema-change是通过触发器来实现的。

结合pt-online-schema-change的原理，我们来重现下问题场景。

mysql> create table slowtech.t1(id int primary key,name varchar(10));
mysql> create table slowtech._t1_new(id int primary key,name varchar(10));
mysql> alter table slowtech._t1_new modify name varchar(10) not null default 'slowtech';
mysql> create trigger slowtech.`pt_osc_slowtech_t1_ins` after insert on `slowtech`.`t1` for each row replace into `slowtech`.`_t1_new` (`id`, `name`) values (new.`id`, new.`name`);
mysql> insert into slowtech.t1(id) values(1);ERROR 1048 (23000): Column 'name' cannot be null

问题完美呈现，有的童鞋可能会有疑问，t1的name列默认不是null么？为什么不允许null值的插入？

问题原因

问题出在触发器上面。

触发器会将业务SQL（“insert into slowtech.t1(id) values(1)”）和触发操作（“replace into slowtech._t1_new (id, name) values(1, null)”）放到一个事务内执行。

“insert into slowtech.t1(id) values(1)”并不违反t1表的约束，但违反了_t1_new表的约束。

通过上面的分析，我们得到了两点启示：

1. 类似DDL（将列的属性从null修改为not null default 'abc'）要注意。

从原理上看，既然涉及到全量数据+增量数据的同步，都会存在这种问题，不单单是pt-online-schema-change，包括Online DDL，gh-ost同样如此

只不过，触发器这种方案会将业务SQL和触发操作耦合在一起，相对来说，对业务有一定的侵入性。

2. 既然触发器会将业务SQL和触发操作放到一个事务内执行，如果pt-online-schema-change异常退出，留下了触发器和中间表（_t1_new），在清理现场时，应首先删除触发器，再删除中间表。

如果首先删除中间表，会导致针对原表的所有DML操作失败。

mysql> drop table slowtech._t1_new;
mysql> insert into slowtech.t1 values(1,'victor');ERROR 1146 (42S02): Table 'slowtech._t1_new' doesn't exist

数据拷贝也有坑

在执行DDL之前，还有一段小插曲。

在执行DDL之前，开发提单将该列的null值修改为了默认值。这样就导致了，问题是在业务SQL插入的过程中暴露的，而不是在数据拷贝过程中暴露。

在数据拷贝的过程中，如果拷贝的数据中，该列存在null值，pt-online-schema-change会直接报错退出。

mysql> create table slowtech.t1(id int primary key,name varchar(10));
mysql> insert into slowtech.t1(id) values(1);
# pt-online-schema-change h=xxxxx,u=root,p=123456,D=slowtech,t=t1 --alter "modify name varchar(10) not null default 'slowtech'" --executeNo slaves found.  See --recursion-method if host xxxx has slaves.Not checking slave lag because no slaves were found and --check-slave-lag was not specified.Operation, tries, wait:  analyze_table, 10, 1  copy_rows, 10, 0.25  create_triggers, 10, 1  drop_triggers, 10, 1  swap_tables, 10, 1  update_foreign_keys, 10, 1Altering `slowtech`.`t1`...Creating new table...Created new table slowtech._t1_new OK.Altering new table...Altered `slowtech`.`_t1_new` OK.2020-09-07T09:13:25 Creating triggers...2020-09-07T09:13:25 Created triggers OK.2020-09-07T09:13:25 Copying approximately 1 rows...2020-09-07T09:13:25 Dropping triggers...2020-09-07T09:13:25 Dropped triggers OK.2020-09-07T09:13:25 Dropping new table...2020-09-07T09:13:25 Dropped new table OK.`slowtech`.`t1` was not altered.        (in cleanup) 2020-09-07T09:13:25 Error copying rows from `slowtech`.`t1` to `slowtech`.`_t1_new`: 2020-09-07T09:13:25 Copying rows caused a MySQL error 1048:    Level: Warning     Code: 1048  Message: Column 'name' cannot be null    Query: INSERT LOW_PRIORITY IGNORE INTO `slowtech`.`_t1_new` (`id`, `name`) SELECT `id`, `name` FROM `slowtech`.`t1` LOCK IN SHARE MODE /*pt-online-schema-change 9234 copy table*/2020-09-07T09:13:25 Dropping triggers...2020-09-07T09:13:25 Dropped triggers OK.`slowtech`.`t1` was not altered.

上述报错，pt-online-schema-change加个参数即可规避（--null-to-not-null）。

在实现上，该参数会忽略1048错误，此时，对于字符类型的列，会填充空字符，对于数字类型的列，会填充0。

mysql> create table slowtech.t1(id int primary key,name varchar(10));
mysql> create table slowtech._t1_new(id int primary key,name varchar(10));
mysql> alter table slowtech._t1_new modify name varchar(10) not null default 'slowtech';
mysql> insert into slowtech.t1(id) values(1);
mysql> select * from slowtech.t1;+----+------+| id | name |+----+------+|  1 | NULL |+----+------+1 row in set (0.00 sec)
mysql> insert low_priority ignore into slowtech._t1_new (id, name) select id, name from slowtech.t1 lock in share mode;Query OK, 1 row affected, 1 warning (0.01 sec)Records: 1  Duplicates: 0  Warnings: 1
mysql> show warnings;+---------+------+------------------------------+| Level   | Code | Message                      |+---------+------+------------------------------+| Warning | 1048 | Column 'name' cannot be null |+---------+------+------------------------------+1 row in set (0.00 sec)
mysql> select * from slowtech._t1_new;+----+------+| id | name |+----+------+|  1 |      |+----+------+1 row in set (0.00 sec)

所以，线上使用该参数要注意，要确认被填充的值是否符合自己的预期行为。

从目前的分析来看，要将一个列的属性从null直接修改为not null default xxx，几乎是不可能的，除非：

1. 该列不存在null值。

2. 在DDL的过程中，没有类似于“insert into slowtech.t1(id) values(1)”的业务SQL出现。

结论

很显然，这两个条件很难同时满足。既然如此，这个需求还能实现吗？能！只不过比较复杂。

下面，看看具体的实施步骤。

1. 首先，将列的属性调整为null default xxx，这样做的目的是为了避免增量同步过程中，类似“insert into slowtech.t1(id) values(1)”的业务SQL，产生新的null值。

2. 其次，手动将null值调整为默认值。需要注意的是，如果记录数较多，这一步的操作难度也是极大的。

3. 最后，将列的属性调整为not null default xxx。

对于not null default xxx的正确理解

在很多数据库规范里面，都推荐将列定义为not null default xxx，但很多童鞋，对这段定义的实际效果却相当模糊。

下面具体来说说，这段定义的实际作用。这段定义实际上由两部分组成：

1. not null，约束，指的是不可显式插入null值，如，

mysql> create table slowtech.t1(id int primary key,name varchar(10) not null default 'slowtech');
mysql> insert into slowtech.t1 values(1,null);ERROR 1048 (23000): Column 'name' cannot be null

2. default 'slowtech'，如果在插入时，没有显式指定值，则以默认值填充。

mysql> insert into slowtech.t1(id) values(1);
mysql> select * from slowtech.t1;+----+----------+| id | name     |+----+----------+|  1 | slowtech |+----+----------+1 row in set (0.00 sec)

可以看到，这两部分其实没有任何关系，对于一个列，我们同样可以定义为null default xxx。

往期 · 推荐

END

扫二维码｜欢迎关注

公众号｜MySQL实战

功夫就是时间！

本篇文章来源于微信公众号: MySQL实战

常用 Linux 发行版操作系统大盘点！

Thu, 08 Oct 2020 18:16:39 +0800

在之前的两篇文章中：

详细演示了如何在Windows系统和macOS系统上基于虚拟机来打造学习和实验用的Linux环境，这两篇文章中安装的Linux系统都是CentOS这个发行版，于是一些小伙伴发出了致命三连击：

为啥不选用其他类型的Linux系统呢？
还有哪些Linux系统可供选用呢？
我到底该选哪一种Linux操作系统呢？

得嘞，我写文章还不行嘛。

不过由于个人能力和见识有限，如有不当之处，还请批评指出，一起交流进步。

眼花缭乱！

Linux系统的种类实在是太多了，具体的发行版种类让人看得眼花缭乱，本文就试着来总结一波，理一理、顺一顺，帮助我们做到心里有数。

单谈Linux的概念实在是太泛了。严格来讲，Linux只能说是一种操作系统的内核，所以我们通常所说的 “Linux操作系统” 一般指的是 采用Linux作为内核 的操作系统！

首先我们选两个常见的维度，来做个分类吧。

按是否商业版来划分

现有的Linux发行版操作系统，可以划分为由商业公司维护的商业版本和由开源社区维护的免费发行版本。

商业版Linux系统典型的代表那就是RedHat Enterprise Linux，而平时我们所常用的Ubuntu、CentOS、Debain这种则一般属于免费发行版。

有人会说，Linux不是开源的吗？不应该都是免费的吗？

是的，Linux内核是开源的，但是 开源≠免费，我们可以理解为付费的是它的服务或者商业支持，所以有些Linux的发行版它也有它所谓的商业版。

当然这个角度我们不用纠结太多，一般个人使用，用免费发行版比较多。

按包管理器来划分

首先，什么是包管理器呢？

这么说吧，如果没有包管理器这个东西的存在，那估计仅仅是Linux系统上的软件安装，怕是就要劝退很多用户了，因为很多的软件依赖处理会让人抓狂。

因此简单起见，我们可以将包管理器理解为，用来为Linux系统上的软件安装、卸载、升级、查询提供支持的组件，所以对于用户使用来说，一般是一组工具命令集。

目前Linux世界里的包管理种类繁荣，选几个最主流的，可以大致梳理成如下表格所示，每一种都有对应的Linux发行版代表：

包管理器名称	常用标志性指令	代表系统举例
DPKG	dpkg、apt	Debain、Ubuntu等
RPM	rpm、yum	RedHat、CentOS等
Pacman	pacman	Arch、Manjaro等
DNF	dnf	Fedora
Zypper	zypper	SUSE
Portage	emerge	Gentoo

用一个脑图来总结一下可能会更加形象：

（建议横屏观看）

所接下来分别针对这里面的几个主流使用的Linux发行版系统来大致唠一唠。

Debian

Debian和Ubuntu是一个派系的，但界面可能没有Ubuntu那么华丽，但是比较稳定，也适合用作服务器操作系统。Debian在开源圈子用得十分广泛，也有一大批开源爱好者活跃于其中。

Ubuntu

Ubuntu界面比较华丽，包管理器很完善，社区也非常活跃，个人用户确实很多，所以从市面上看的确比较大众化。

RedHat

RHEL（Red Hat Enterprise Linux）毕竟是商业版Linux系统，一般多用于企业生产环境，提供完善的商业支持，在性能、稳定性方面也有很大的保障。

CentOS

CentOS可以理解为是基于RedHat商业版系统的社区编译重发布版，完全开源免费，因此相较于其他一些免费的Linux发行版会更加稳定，也因此一般企业里常用作服务器操作系统。

Fedora

Fedora其实和RedHat也同属一个派系，背后的支撑企业也是红帽子公司。但是Fedora是免费发行版，而且更加侧重于新技术的试验和加持，因此稳定性方面的考量较CentOS会稍微次要一些。

SUSE

SUSE背后也算是有大公司的支持了，目前主要也还是多用于企业用户。

Arch

Arch的确比较适合好奇心强的人尝鲜，它的官方Wiki做得好，AUR仓库很繁荣，适合DIY玩家去折腾，确实也吸引了不少粉丝。

Manjaro

Manjaro可以看成是Arch Linux的衍生分支，既包含了Arch的常见优点，但也对用户友好，注重体验和稳定性。Manjaro的安装和使用都比较方便，目前使用用户非常多。

Gentoo

Gentoo适合极客范化的折腾，也比较适合有特殊需要和特殊化定制的需求，总体来说比较小众。但是用得很6的大佬们都说好，因为这些大佬们大多有系统洁癖，控制欲很强，什么都需要自己编译、自己定制，这个对他们来说简直就是天堂了。

其他Linux发行版

上文中只是介绍了比较主流和常用的Linux发行版，实际上各种各样的Linux发行版和其衍生系统数不胜数，在此也不一一列举了。

选用建议

如果是完全0基础的新手，只是想入门Linux的生态，体验Linux界面，那Ubuntu就非常合适
如果喜欢折腾和DIY，好奇心满满，可以试试Arch、Manjaro、Gentoo这些
如果想用来部署服务，考虑稳定性，那CentOS、Debian都是不错的选择

当然这仅仅只是参考，最终的选择还是看个人需求和兴趣！

本篇文章来源于微信公众号: CodeSheep

Linus：我们都老了，但Linux维护后继无人

Thu, 08 Oct 2020 18:11:25 +0800

编译 | 核子可乐、Tina

在本周召开的线上开源峰会与嵌入式 Linux 大会上，Linux 缔造者 Linus Torvalds 谈到了为开源操作系统寻找未来维护者时的种种挑战。Torvalds 近年来已经不再发表主题演讲，但这一次他与 VMware 公司首席开源官 Dirk Hohndel 展开了远程对话。

这次讨论很快就涉及到了一个令人不安的问题：在目前这一代维护者逐渐老去之后，Linux 项目将走向何处？面对 Torvalds 等这批五零后、六零后项目管理者，Hohndel 提到：“我们这个社区终归要考虑代际变更的问题。到那时，我们该怎么办？”

VMware 公司的 Dirk Hohndel（图左）在本届线上开源峰会中与 Linus Torvalds 进行了对话。

Torvalds 的回应是，Linux 内核社区的参与者们年纪不算太大。他表示，“很多新人都在 50 岁以下，他们才是目前开发工作的主力。当然，跟那些 30 岁上下的人们相比，我们确实是越来越老了。好在我们这些长期参与项目的早期成员还能做做维护与管理工作。”

维护者在社区内建立信任需要花费不少时间，Torvalds 指出，“这种信任不仅来自其他维护者，同时也来自所有代码贡献者……这肯定需要时间。”Torvalds 强调，“事实证明，维护者真的不好找。只要开始接管内核维护工作，就得一直坚持下去。每天都不能放松。我们得阅读电子邮件、做出回复，总之得一直待在那儿。而且维护工作属于那种要求不低但却需求面不大的小众岗位。”

“我们的维护者确实不够。能编写代码的人很多，能处理一部分维护工作的也不少，但很难找到那种可以吸纳他人代码贡献并立足上游将一切整合起来的人才。这也是我们目前面临的一大主要问题。”

另外，Linux 内核大部分是由 C 语言编写的。为此，Hohndel 问道：“C 语言是否会被 GO 及 Rust 语言取代，我们这些用 C 的人有没有可能在二十年后变得像现在的 COBOL 程序员一样？”Torvalds 的回应是，“C 语言目前仍是全球十大人气语言之一。但对语言的具体选择并不会对内核造成太大的影响。与驱动程序相似，内核团队正在研究多种语言接口，相信不久之后就能实现。总而言之，我们必将使用不同的模型编写 Linux 代码，而 C 绝对不会是其中唯一的模型选项。”

顶尖 Linux 开发者们已至暮年

上一代顶级程序员们确实在逐渐老去，Linus Torvalds 本人今年也超过了 50 岁。

Linux 社区需要新鲜血液，这也是事实。根据 Linux 基金会营销与开发人员计划副总裁 Amanda McPherson 所言，“目前 Linux 项目的参与者数量已经达到历史最高点。而且自 2005 年以来，已经有超过 8000 人为 Linux 内核做出贡献。”但从参与者数字来看，老一辈 Linux 程序员仍然是项目的主力。

软件开发分析公司 Bitergia 创始者之一 Jesús M González-Barahona 就发现，在以“参与项目的时间”作为“年龄”指标对 Linux 内核开发者进行统计时，可以看到新生代程序员的占比一直在逐年下降。目前占比最高的参与者们，一般是十多年前就加入了 Linux 社区，之后几代的比例则呈现出下降趋势。

Linux 社区当然早就意识到了这个问题。

2010 年，资深 Linux 开发者兼 Linux Driver 项目负责人 Greg Kroah-Harman 就在 Linux 基金会协作峰会的内核小组讨论上指出，“项目高层的更迭一直没能成功完成。”

Parallels 公司服务器虚拟化 CTO James Bottomley 也表示，“老一辈贡献者仍是项目主力。Linux 内核开发工作一直无法接棒，几年之后不知道还能剩下多少早期成员。”

谷歌软件工程师兼高级 Linux 内核开发者 Andrew Morton 则总结道，“没错，我们正在变老，精力也越来越差。从现在来看，年轻一代也不像当初的贡献者们那样对内核开发充满热情。”

从多年前开始，Linux 基金会就一直试图解决问题。作为思路之一，Linux 基金会正努力吸引更多业余程序员加入进来。McPherson 补充道，“虽然 Linux 项目的参与者数量创下历史新高，但我们一直在努力吸引更多新的人才。而且大家基本达成了共识，人才匮乏已经成为 Linux 实现进一步增长的最大障碍。我们希望通过 LinuxCon 在新生代程序员中建立影响力，但目前看来这张网撒得太大，导致很多人搞不清 LinuxCon 到底是以开发者为中心、还是以系统管理员 / 架构师为中心。”

“怼天怼地怼空气”的 Linus

尽管 Linus Torvalds 有着无可置疑的天赋，但他对待社区参与者的方式使他成为一个极具争议性的人物。

对他行为的相关抱怨可以追溯到多年前。2013 年，Intel 公司的内核开发人员 Sarah Sharp 称 Torvalds 的行为是不专业的，称 Torvalds“主张进行人身恐吓和暴力行为。”Torvalds 随后指责 Sharp 把自己描述成受害者博取同情，不接受任何劝他应该改变的建议。

在 2015 年发表演讲说到英伟达时，Torvalds 还曾转向一台摄像机说“so Nvidia fuck you”并竖起了中指。

2015 年底，Sarah Sharp 宣布退出（Closing a door）内核社区。Sarah Sharp 当时说道，过去一年多时间她已经逐步终止了手中的各项社区工作，转交了 USB 3.0 主控制器驱动的维护工作，不再担任开源会议的内核协调员。她不再递交任何补丁和 bug 报告，不再向内核邮件列表写任何的建议。她声称，Linux 内核社区的互动是一种“潜在有毒的背景辐射”，充满了性别歧视、语言暴力和不尊重人。

Torvalds 最终也意识到他的言行会伤害到社区发展。2018 年，他决定休假并反思自己的行为，在 4.19-rc4 版本发布公告中他写道：“我将抽出时间休息并寻求一些帮助，了解如何理解他人的情绪并做出适当的反应... 我不是一个能对他人的感受感同身受的人，很多人对此也并不惊讶。多年来，我误解了很多人，而我自己并没有意识到我对某些情况的判断有多么糟糕，这样造成了一种不专业的环境，这样不太好。”

写在最后

去年 8 月 7 日，首个专注于报道 Linux 内核及其发行版的杂志 Linux Journal 宣布停刊。杂志主编在官网公告上表示，因资金断裂，永久关停，并解雇了所有员工。很多人选择 Linux，是因为 Linux 开源免费。免费使用，但是又不愿意花钱或参与贡献。当时有人评论说：“今天死的是一个 Linux 杂志，明天就可能就是 Linux Mint 或者 LibreOffice。”

如今，Linux 作为最流行的操作系统，在超过 20 亿的设备上运行，已经成为人类技术发展中的不可或缺的一部分。我们难以想象无人维护的后果会是什么样。

参考阅读：

https://www.zdnet.com/article/graying-linux-developers-look-for-new-blood/

https://www.theregister.com/2020/06/30/hard_to_find_linux_maintainers_says_torvalds/

本篇文章来源于微信公众号: InfoQ

20000 字干货笔记，一天搞定 MySQL ！

Thu, 08 Oct 2020 18:06:46 +0800

今天给大家分享的是我学习MySQL记录的详细笔记，有基础知识，也有实战案例，文章较长，建议收藏~

基本语法

-- 显示所有数据库
show databases;

-- 创建数据库
CREATE DATABASE test;

-- 切换数据库
use test;

-- 显示数据库中的所有表
show tables;

-- 创建数据表
CREATE TABLE pet (
    name VARCHAR(20),
    owner VARCHAR(20),
    species VARCHAR(20),
    sex CHAR(1),
    birth DATE,
    death DATE
);

-- 查看数据表结构
-- describe pet;
desc pet;

-- 查询表
SELECT * from pet;

-- 插入数据
INSERT INTO pet VALUES ('puffball', 'Diane', 'hamster', 'f', '1990-03-30', NULL);

-- 修改数据
UPDATE pet SET name = 'squirrel' where owner = 'Diane';

-- 删除数据
DELETE FROM pet where name = 'squirrel';

-- 删除表
DROP TABLE myorder;

数据库大三大设计范式

1NF

不可分割性，只要字段值还可以继续拆分，就不满足第一范式。

不可分割的意思就按字面理解就是最小单位，不能再分成更小单位了。

字段只能是一个值，不能被拆分成多个字段，否则的话，它就是可分割的，就不符合一范式。

2NF

第二范式就是要有主键，要求其他字段都依赖于主键。

为什么要有主键？没有主键就没有唯一性，没有唯一性在集合中就定位不到这行记录，所以要主键。

在满足第一范式的前提下，主键外的每一列都必须完全依赖于主键。如果出现不完全依赖，只可能发生在联合主键的情况下。

3NF

在满足第二范式的前提下，除了主键列之外，其他列之间不能有传递依赖关系，即“消除冗余”。

消除冗余，就是各种信息只在一个地方存储，不出现在多张表中。

范式总结：范式，其实是用来学习参考的，设计的时候根据情况，未必一定要遵守，要灵活结合业务实际情况决定。

查询练习

准备数据

-- 创建数据库
CREATE DATABASE select_test;
-- 切换数据库
USE select_test;

-- 创建学生表
CREATE TABLE student (
    no VARCHAR(20) PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    sex VARCHAR(10) NOT NULL,
    birthday DATE, -- 生日
    class VARCHAR(20) -- 所在班级
);

-- 创建教师表
CREATE TABLE teacher (
    no VARCHAR(20) PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    sex VARCHAR(10) NOT NULL,
    birthday DATE,
    profession VARCHAR(20) NOT NULL, -- 职称
    department VARCHAR(20) NOT NULL -- 部门
);

-- 创建课程表
CREATE TABLE course (
    no VARCHAR(20) PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    t_no VARCHAR(20) NOT NULL, -- 教师编号
    -- 表示该 tno 来自于 teacher 表中的 no 字段值
    FOREIGN KEY(t_no) REFERENCES teacher(no)
);

-- 成绩表
CREATE TABLE score (
    s_no VARCHAR(20) NOT NULL, -- 学生编号
    c_no VARCHAR(20) NOT NULL, -- 课程号
    degree DECIMAL,  -- 成绩
    -- 表示该 s_no, c_no 分别来自于 student, course 表中的 no 字段值
    FOREIGN KEY(s_no) REFERENCES student(no),
    FOREIGN KEY(c_no) REFERENCES course(no),
    -- 设置 s_no, c_no 为联合主键
    PRIMARY KEY(s_no, c_no)
);

-- 查看所有表
SHOW TABLES;

-- 添加学生表数据
INSERT INTO student VALUES('101', '曾华', '男', '1977-09-01', '95033');
INSERT INTO student VALUES('102', '匡明', '男', '1975-10-02', '95031');
INSERT INTO student VALUES('103', '王丽', '女', '1976-01-23', '95033');
INSERT INTO student VALUES('104', '李军', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('105', '王芳', '女', '1975-02-10', '95031');
INSERT INTO student VALUES('106', '陆军', '男', '1974-06-03', '95031');
INSERT INTO student VALUES('107', '王尼玛', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('108', '张全蛋', '男', '1975-02-10', '95031');
INSERT INTO student VALUES('109', '赵铁柱', '男', '1974-06-03', '95031');

-- 添加教师表数据
INSERT INTO teacher VALUES('804', '李诚', '男', '1958-12-02', '副教授', '计算机系');
INSERT INTO teacher VALUES('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系');
INSERT INTO teacher VALUES('825', '王萍', '女', '1972-05-05', '助教', '计算机系');
INSERT INTO teacher VALUES('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系');

-- 添加课程表数据
INSERT INTO course VALUES('3-105', '计算机导论', '825');
INSERT INTO course VALUES('3-245', '操作系统', '804');
INSERT INTO course VALUES('6-166', '数字电路', '856');
INSERT INTO course VALUES('9-888', '高等数学', '831');

-- 添加添加成绩表数据
INSERT INTO score VALUES('103', '3-105', '92');
INSERT INTO score VALUES('103', '3-245', '86');
INSERT INTO score VALUES('103', '6-166', '85');
INSERT INTO score VALUES('105', '3-105', '88');
INSERT INTO score VALUES('105', '3-245', '75');
INSERT INTO score VALUES('105', '6-166', '79');
INSERT INTO score VALUES('109', '3-105', '76');
INSERT INTO score VALUES('109', '3-245', '68');
INSERT INTO score VALUES('109', '6-166', '81');

-- 查看表结构
SELECT * FROM course;
SELECT * FROM score;
SELECT * FROM student;
SELECT * FROM teacher;

基础查询

-- 查询 student 表的所有行
SELECT * FROM student;

-- 查询 student 表中的 name、sex 和 class 字段的所有行
SELECT name, sex, class FROM student;

-- 查询 teacher 表中不重复的 department 列
-- department: 去重查询
SELECT DISTINCT department FROM teacher;

-- 查询 score 表中成绩在60-80之间的所有行（区间查询和运算符查询）
-- BETWEEN xx AND xx: 查询区间, AND 表示 "并且"
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;
SELECT * FROM score WHERE degree > 60 AND degree < 80;

-- 查询 score 表中成绩为 85, 86 或 88 的行
-- IN: 查询规定中的多个值
SELECT * FROM score WHERE degree IN (85, 86, 88);

-- 查询 student 表中 '95031' 班或性别为 '女' 的所有行
-- or: 表示或者关系
SELECT * FROM student WHERE class = '95031' or sex = '女';

-- 以 class 降序的方式查询 student 表的所有行
-- DESC: 降序，从高到低
-- ASC（默认）: 升序，从低到高
SELECT * FROM student ORDER BY class DESC;
SELECT * FROM student ORDER BY class ASC;

-- 以 c_no 升序、degree 降序查询 score 表的所有行
SELECT * FROM score ORDER BY c_no ASC, degree DESC;

-- 查询 "95031" 班的学生人数
-- COUNT: 统计
SELECT COUNT(*) FROM student WHERE class = '95031';

-- 查询 score 表中的最高分的学生学号和课程编号（子查询或排序查询）。
-- (SELECT MAX(degree) FROM score): 子查询，算出最高分
SELECT s_no, c_no FROM score WHERE degree = (SELECT MAX(degree) FROM score);

--  排序查询
-- LIMIT r, n: 表示从第r行开始，查询n条数据
SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 0, 1;
-- LIMIT n offset r: 表示查询n条数据,从第r行开始
SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 1 offse

分组计算平均成绩

查询每门课的平均成绩

-- AVG: 平均值
SELECT AVG(degree) FROM score WHERE c_no = '3-105';
SELECT AVG(degree) FROM score WHERE c_no = '3-245';
SELECT AVG(degree) FROM score WHERE c_no = '6-166';

-- GROUP BY: 分组查询
SELECT c_no, AVG(degree) FROM score GROUP BY c_no;

分组条件与模糊查询

查询score表中至少有 2 名学生选修，并以 3 开头的课程的平均分数

分析表发现，至少有 2 名学生选修的课程是3-105、3-245、6-166，以 3 开头的课程是3-105、3-245。也就是说，我们要查询所有3-105和3-245的degree平均分。

-- 首先把 c_no, AVG(degree) 通过分组查询出来
SELECT c_no, AVG(degree) FROM score GROUP BY c_no
+-------+-------------+
| c_no  | AVG(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+

-- 再查询出至少有 2 名学生选修的课程
-- HAVING: 表示持有
HAVING COUNT(c_no) >= 2

-- 并且是以 3 开头的课程
-- LIKE 表示模糊查询，"%" 是一个通配符，匹配 "3" 后面的任意字符。
AND c_no LIKE '3%';

-- 把前面的SQL语句拼接起来，
-- 后面加上一个 COUNT(*)，表示将每个分组的个数也查询出来。
SELECT c_no, AVG(degree), COUNT(*) FROM score GROUP BY c_no
HAVING COUNT(c_no) >= 2 AND c_no LIKE '3%';
+-------+-------------+----------+
| c_no  | AVG(degree) | COUNT(*) |
+-------+-------------+----------+
| 3-105 |     85.3333 |        3 |
| 3-245 |     76.3333 |        3 |
+-------+-------------+----------+

多表查询 - 1

查询所有学生的name，以及该学生在score表中对应的c_no和degree

SELECT no, name FROM student;
+-----+-----------+
| no  | name      |
+-----+-----------+
| 101 | 曾华      |
| 102 | 匡明      |
| 103 | 王丽      |
| 104 | 李军      |
| 105 | 王芳      |
| 106 | 陆军      |
| 107 | 王尼玛    |
| 108 | 张全蛋    |
| 109 | 赵铁柱    |
+-----+-----------+

SELECT s_no, c_no, degree FROM score;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

通过分析可以发现，只要把score表中的s_no字段值替换成student表中对应的name字段值就可以了，如何做呢？

-- FROM...: 表示从 student, score 表中查询
-- WHERE 的条件表示为，只有在 student.no 和 score.s_no 相等时才显示出来。
SELECT name, c_no, degree FROM student, score
WHERE student.no = score.s_no;
+-----------+-------+--------+
| name      | c_no  | degree |
+-----------+-------+--------+
| 王丽      | 3-105 |     92 |
| 王丽      | 3-245 |     86 |
| 王丽      | 6-166 |     85 |
| 王芳      | 3-105 |     88 |
| 王芳      | 3-245 |     75 |
| 王芳      | 6-166 |     79 |
| 赵铁柱    | 3-105 |     76 |
| 赵铁柱    | 3-245 |     68 |
| 赵铁柱    | 6-166 |     81 |
+-----------+-------+--------+

多表查询 - 2

查询所有学生的 no 、课程名称 ( course 表中的 name ) 和成绩 ( score 表中的degree ) 列。

只有score关联学生的no，因此只要查询score表，就能找出所有和学生相关的no和degree：

SELECT s_no, c_no, degree FROM score;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

然后查询course表：

SELECT no, name FROM course;
+-------+-----------------+
| no    | name            |
+-------+-----------------+
| 3-105 | 计算机导论      |
| 3-245 | 操作系统        |
| 6-166 | 数字电路        |
| 9-888 | 高等数学        |
+-------+-----------------+

只要把score表中的c_no替换成course表中对应的name字段值就可以了。

-- 增加一个查询字段 name，分别从 score、course 这两个表中查询。
-- as 表示取一个该字段的别名。
SELECT s_no, name as c_name, degree FROM score, course
WHERE score.c_no = course.no;
+------+-----------------+--------+
| s_no | c_name          | degree |
+------+-----------------+--------+
| 103  | 计算机导论      |     92 |
| 105  | 计算机导论      |     88 |
| 109  | 计算机导论      |     76 |
| 103  | 操作系统        |     86 |
| 105  | 操作系统        |     75 |
| 109  | 操作系统        |     68 |
| 103  | 数字电路        |     85 |
| 105  | 数字电路        |     79 |
| 109  | 数字电路        |     81 |
+------+-----------------+--------+

三表关联查询

查询所有学生的 name 、课程名 ( course 表中的 name ) 和 degree 。

只有score表中关联学生的学号和课堂号，我们只要围绕着score这张表查询就好了。

SELECT * FROM score;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

只要把 s_no 和 c_no 替换成 student 和 course 表中对应的 name 字段值就好了。

首先把 s_no 替换成 student 表中的 name 字段：

SELECT name, c_no, degree FROM student, score WHERE student.no = score.s_no;
+-----------+-------+--------+
| name      | c_no  | degree |
+-----------+-------+--------+
| 王丽      | 3-105 |     92 |
| 王丽      | 3-245 |     86 |
| 王丽      | 6-166 |     85 |
| 王芳      | 3-105 |     88 |
| 王芳      | 3-245 |     75 |
| 王芳      | 6-166 |     79 |
| 赵铁柱    | 3-105 |     76 |
| 赵铁柱    | 3-245 |     68 |
| 赵铁柱    | 6-166 |     81 |
+-----------+-------+--------+

再把c_no替换成course表中的name字段：

-- 课程表
SELECT no, name FROM course;
+-------+-----------------+
| no    | name            |
+-------+-----------------+
| 3-105 | 计算机导论      |
| 3-245 | 操作系统        |
| 6-166 | 数字电路        |
| 9-888 | 高等数学        |
+-------+-----------------+

-- 由于字段名存在重复，使用 "表名.字段名 as 别名" 代替。
SELECT student.name as s_name, course.name as c_name, degree
FROM student, score, course
WHERE student.NO = score.s_no
AND score.c_no = course.no;

子查询加分组求平均分

查询95031班学生每门课程的平均成绩。

在score表中根据student表的学生编号筛选出学生的课堂号和成绩：

-- IN (..): 将筛选出的学生号当做 s_no 的条件查询
SELECT s_no, c_no, degree FROM score
WHERE s_no IN (SELECT no FROM student WHERE class = '95031');
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

这时只要将c_no分组一下就能得出95031班学生每门课的平均成绩：

SELECT c_no, AVG(degree) FROM score
WHERE s_no IN (SELECT no FROM student WHERE class = '95031')
GROUP BY c_no;
+-------+-------------+
| c_no  | AVG(degree) |
+-------+-------------+
| 3-105 |     82.0000 |
| 3-245 |     71.5000 |
| 6-166 |     80.0000 |
+-------+-------------+

子查询 - 1

查询在 3-105 课程中，所有成绩高于 109 号同学的记录。

先用子查询查找出109同学在3-105中的成绩

select * from score
where c_no ='3-105' and s_no='109'

然后再以课程3-105为条件，查找成绩大76的记录

select * from score
where c_no = '3-105'
and degree>
(select degree from score
where c_no = '3-105' and s_no='109');

子查询 - 2

查询所有成绩高于 109 号同学的 3-105 课程成绩记录。

-- 不限制课程号，只要成绩大于109号同学的3-105课程成绩就可以。
SELECT * FROM score
WHERE degree > (SELECT degree FROM score WHERE s_no = '109' AND c_no = '3-105');

YEAR 函数与带 IN 关键字查询

查询所有和 101 、108 号学生同年出生的 no 、name 、birthday 列。

select no, name, birthday from student
where year(birthday) in (
select year(birthday) from student
where no in (101,108));

多层嵌套子查询

查询 '张旭' 教师任课的学生成绩表。

用的三张表teacher、course、score，首先找到教师编号：

SELECT NO FROM teacher WHERE NAME = '张旭'

通过sourse表找到该教师课程号：

select no from course
where t_no = (SELECT NO FROM teacher WHERE NAME = '张旭')

通过筛选出的课程号查询成绩表：

select * from score where c_no = (
select no from course where t_no = (
select no from teacher
where name = '张旭'));

多表查询

查询某选修课程多于5个同学的教师姓名。

首先在teacher表中，根据no字段来判断该教师的同一门课程是否有至少5名学员选修：

-- 查询 teacher 表
SELECT no, name FROM teacher;
+-----+--------+
| no  | name   |
+-----+--------+
| 804 | 李诚   |
| 825 | 王萍   |
| 831 | 刘冰   |
| 856 | 张旭   |
+-----+--------+

SELECT name FROM teacher WHERE no IN (
    -- 在这里找到对应的条件
);

查看和教师编号有有关的表的信息：

SELECT * FROM course;
-- t_no: 教师编号
+-------+-----------------+------+
| no    | name            | t_no |
+-------+-----------------+------+
| 3-105 | 计算机导论      | 825  |
| 3-245 | 操作系统        | 804  |
| 6-166 | 数字电路        | 856  |
| 9-888 | 高等数学        | 831  |
+-------+-----------------+------+

我们已经找到和教师编号有关的字段就在course表中，但是还无法知道哪门课程至少有5名学生选修，所以还需要根据score表来查询：

-- 在此之前向 score 插入一些数据，以便丰富查询条件。
INSERT INTO score VALUES ('101', '3-105', '90');
INSERT INTO score VALUES ('102', '3-105', '91');
INSERT INTO score VALUES ('104', '3-105', '89');

-- 查询 score 表
SELECT * FROM score;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

-- 在 score 表中将 c_no 作为分组，并且限制 c_no 持有至少 5 条数据。
SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5;
+-------+
| c_no  |
+-------+
| 3-105 |
+-------+

根据筛选出来的课程号，找出在某课程中，拥有至少5名学员的教师编号：

SELECT t_no FROM course WHERE no IN (
    SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5
);
+------+
| t_no |
+------+
| 825  |
+------+在teacher表中，根据筛选出来的教师编号找到教师姓名：SELECT name FROM teacher WHERE no IN (
    -- 最终条件
    SELECT t_no FROM course WHERE no IN (
        SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5
    )
);

子查询 - 3

查询 “计算机系” 课程的成绩表。

思路是，先找出teacher表中所有计算机系课程的编号，根据这个编号查询course表中的课程编号，再用课程编号查找score表

-- 通过 teacher 表查询所有 `计算机系` 的教师编号
SELECT no, name, department FROM teacher WHERE department = '计算机系'
+-----+--------+--------------+
| no  | name   | department   |
+-----+--------+--------------+
| 804 | 李诚   | 计算机系     |
| 825 | 王萍   | 计算机系     |
+-----+--------+--------------+

-- 通过 course 表查询该教师的课程编号
SELECT no FROM course WHERE t_no IN (
    SELECT no FROM teacher WHERE department = '计算机系'
);
+-------+
| no    |
+-------+
| 3-245 |
| 3-105 |
+-------+

-- 根据筛选出来的课程号查询成绩表
SELECT * FROM score WHERE c_no IN (
    SELECT no FROM course WHERE t_no IN (
        SELECT no FROM teacher WHERE department = '计算机系'
    )
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-245 |     86 |
| 105  | 3-245 |     75 |
| 109  | 3-245 |     68 |
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 109  | 3-105 |     76 |
+------+-------+--------+

UNION 和 NOT IN 的使用

查询计算机系与电子工程系中的不同职称的教师。

+-----+------+-----+------------+------------+------------+
| no  | name | sex | birthday   | profession | department |
+-----+------+-----+------------+------------+------------+
| 804 | 李诚 | 男  | 1958-12-02 | 副教授     | 计算机系   |
| 825 | 王萍 | 女  | 1972-05-05 | 助教       | 计算机系   |
| 831 | 刘冰 | 女  | 1977-08-14 | 助教       | 电子工程系 |
| 856 | 张旭 | 男  | 1969-03-12 | 讲师       | 电子工程系 |
+-----+------+-----+------------+------------+------------+

-- NOT: 代表逻辑非
SELECT * FROM teacher WHERE department = '计算机系' AND profession NOT IN (
    SELECT profession FROM teacher WHERE department = '电子工程系'
)
-- 合并两个集
UNION
SELECT * FROM teacher WHERE department = '电子工程系' AND profession NOT IN (
    SELECT profession FROM teacher WHERE department = '计算机系'
);

ANY 表示至少一个 - DESC ( 降序 )

查询课程 3-105 且成绩至少高于 3-245 的 score 表。

SELECT * FROM score WHERE c_no = '3-105';
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 109  | 3-105 |     76 |
+------+-------+--------+

SELECT * FROM score WHERE c_no = '3-245';
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-245 |     86 |
| 105  | 3-245 |     75 |
| 109  | 3-245 |     68 |
+------+-------+--------+

-- ANY: 符合SQL语句中的任意条件。
-- 也就是说，在 3-105 成绩中，只要有一个大于从 3-245 筛选出来的任意行就符合条件，
-- 最后根据降序查询结果。
SELECT * FROM score WHERE c_no = '3-105' AND degree > ANY(
    SELECT degree FROM score WHERE c_no = '3-245'
) ORDER BY degree DESC;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 102  | 3-105 |     91 |
| 101  | 3-105 |     90 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 109  | 3-105 |     76 |
+------+-------+--------+

表示所有的 ALL

查询课程 3-105 且成绩高于 3-245 的 score 表。

-- 只需对上一道题稍作修改。
-- ALL: 符合SQL语句中的所有条件。
-- 也就是说，在 3-105 每一行成绩中，都要大于从 3-245 筛选出来全部行才算符合条件。
SELECT * FROM score WHERE c_no = '3-105' AND degree > ALL(
    SELECT degree FROM score WHERE c_no = '3-245'
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
+------+-------+--------+

复制表的数据作为条件查询

查询某课程成绩比该课程平均成绩低的score表。

-- 查询平均分
SELECT c_no, AVG(degree) FROM score GROUP BY c_no;
b表
+-------+-------------+
| c_no  | AVG(degree) |
+-------+-------------+
| 3-105 |     87.6667 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+

-- 查询 score 表
select * from score;
a表
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

-- 将表 b 作用于表 a 中查询数据
-- score a (b): 将表声明为 a (b)，
-- 如此就能用 a.c_no = b.c_no 作为条件执行查询了。
SELECT * FROM score a WHERE degree < (
    (SELECT AVG(degree) FROM score b WHERE a.c_no = b.c_no)
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

子查询 - 4

查询所有任课 ( 在 course 表里有课程 ) 教师的 name 和 department 。

SELECT name, department FROM teacher WHERE no IN (SELECT t_no FROM course);
+--------+-----------------+
| name   | department      |
+--------+-----------------+
| 李诚   | 计算机系        |
| 王萍   | 计算机系        |
| 刘冰   | 电子工程系      |
| 张旭   | 电子工程系      |
+--------+-----------------+

条件加组筛选

查询 student 表中至少有 2 名男生的 class 。

-- 查看学生表信息
SELECT * FROM student;
+-----+-----------+-----+------------+-------+
| no  | name      | sex | birthday   | class |
+-----+-----------+-----+------------+-------+
| 101 | 曾华      | 男  | 1977-09-01 | 95033 |
| 102 | 匡明      | 男  | 1975-10-02 | 95031 |
| 103 | 王丽      | 女  | 1976-01-23 | 95033 |
| 104 | 李军      | 男  | 1976-02-20 | 95033 |
| 105 | 王芳      | 女  | 1975-02-10 | 95031 |
| 106 | 陆军      | 男  | 1974-06-03 | 95031 |
| 107 | 王尼玛    | 男  | 1976-02-20 | 95033 |
| 108 | 张全蛋    | 男  | 1975-02-10 | 95031 |
| 109 | 赵铁柱    | 男  | 1974-06-03 | 95031 |
| 110 | 张飞      | 男  | 1974-06-03 | 95038 |
+-----+-----------+-----+------------+-------+
-- 只查询性别为男，然后按 class 分组，并限制 class 行大于 1。
select * from student where sex='男'
group by class
having count(class)>=2；
+-----+------+-----+------------+-------+
| no  | name | sex | birthday   | class |
+-----+------+-----+------------+-------+
| 101 | 曾华 | 男  | 1977-09-01 | 95033 |
| 102 | 匡明 | 男  | 1975-10-02 | 95031 |
+-----+------+-----+------------+-------+

NOT LIKE 模糊查询取反

查询 student 表中不姓 "王" 的同学记录。

-- NOT: 取反
-- LIKE: 模糊查询
mysql> SELECT * FROM student WHERE name NOT LIKE '王%';
+-----+-----------+-----+------------+-------+
| no  | name      | sex | birthday   | class |
+-----+-----------+-----+------------+-------+
| 101 | 曾华      | 男  | 1977-09-01 | 95033 |
| 102 | 匡明      | 男  | 1975-10-02 | 95031 |
| 104 | 李军      | 男  | 1976-02-20 | 95033 |
| 106 | 陆军      | 男  | 1974-06-03 | 95031 |
| 108 | 张全蛋    | 男  | 1975-02-10 | 95031 |
| 109 | 赵铁柱    | 男  | 1974-06-03 | 95031 |
| 110 | 张飞      | 男  | 1974-06-03 | 95038 |
+-----+-----------+-----+------------+-------+

YEAR 与 NOW 函数

查询 student 表中每个学生的姓名和年龄。

select name, year(now())-year(birthday) as age
from student;
+-----------+------+
| name      | age  |
+-----------+------+
| 曾华      |   42 |
| 匡明      |   44 |
| 王丽      |   43 |
| 李军      |   43 |
| 王芳      |   44 |
| 陆军      |   45 |
| 王尼玛    |   43 |
| 张全蛋    |   44 |
| 赵铁柱    |   45 |
| 张飞      |   45 |
+-----------+------+

MAX 与 MIN 函数

查询 student 表中最大和最小的 birthday 值。

SELECT MAX(birthday), MIN(birthday) FROM student;
+---------------+---------------+
| MAX(birthday) | MIN(birthday) |
+---------------+---------------+
| 1977-09-01    | 1974-06-03    |
+---------------+---------------+

多段排序

以 class 和 birthday 从大到小的顺序查询 student 表。

SELECT * FROM student ORDER BY class DESC, birthday DESC;
+-----+--------+-----+------------+-------+
| no  | name   | sex | birthday   | class |
+-----+--------+-----+------------+-------+
| 110 | 张飞   | 男  | 1974-06-03 | 95038 |
| 101 | 曾华   | 男  | 1977-09-01 | 95033 |
| 104 | 李军   | 男  | 1976-02-20 | 95033 |
| 107 | 王尼玛 | 男  | 1976-02-20 | 95033 |
| 103 | 王丽   | 女  | 1976-01-23 | 95033 |
| 102 | 匡明   | 男  | 1975-10-02 | 95031 |
| 105 | 王芳   | 女  | 1975-02-10 | 95031 |
| 108 | 张全蛋 | 男  | 1975-02-10 | 95031 |
| 106 | 陆军   | 男  | 1974-06-03 | 95031 |
| 109 | 赵铁柱 | 男  | 1974-06-03 | 95031 |
+-----+--------+-----+------------+-------+

子查询 - 5

查询 "男" 教师及其所上的课程。

SELECT * FROM course WHERE t_no in (SELECT no FROM teacher WHERE sex = '男');
+-------+--------------+------+
| no    | name         | t_no |
+-------+--------------+------+
| 3-245 | 操作系统     | 804  |
| 6-166 | 数字电路     | 856  |
+-------+--------------+------+

MAX 函数与子查询

查询最高分同学的 score 表。

-- 找出最高成绩（该查询只能有一个结果）
SELECT MAX(degree) FROM score;

-- 根据上面的条件筛选出所有最高成绩表，
-- 该查询可能有多个结果，假设 degree 值多次符合条件。
SELECT * FROM score WHERE degree = (SELECT MAX(degree) FROM score);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
+------+-------+--------+

子查询 - 6

查询和 "李军" 同性别的所有同学 name 。

select name from student where sex = (
SELECT sex FROM student where name='李军'
);
+--------+
| name   |
+--------+
| 曾华   |
| 匡明   |
| 李军   |
| 陆军   |
| 王尼玛 |
| 张全蛋 |
| 赵铁柱 |
| 张飞   |
+--------+

子查询 - 7

查询和 "李军" 同性别且同班的同学 name 。

SELECT name, sex, class FROM student WHERE sex = (
    SELECT sex FROM student WHERE name = '李军'
) AND class = (
    SELECT class FROM student WHERE name = '李军'
);
+-----------+-----+-------+
| name      | sex | class |
+-----------+-----+-------+
| 曾华      | 男  | 95033 |
| 李军      | 男  | 95033 |
| 王尼玛    | 男  | 95033 |
+-----------+-----+-------+

子查询 - 8

查询所有选修 "计算机导论" 课程的 "男" 同学成绩表。

需要的 "计算机导论" 和性别为 "男" 的编号可以在 course 和 student 表中找到。

SELECT * FROM score WHERE c_no = (
    SELECT no FROM course WHERE name = '计算机导论'
) AND s_no IN (
    SELECT no FROM student WHERE sex = '男'
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 104  | 3-105 |     89 |
| 109  | 3-105 |     76 |
+------+-------+--------+

按等级查询

建立一个 grade 表代表学生的成绩等级，并插入数据：

CREATE TABLE grade (
    low INT(3),
    upp INT(3),
    grade char(1)
);

INSERT INTO grade VALUES (90, 100, 'A');
INSERT INTO grade VALUES (80, 89, 'B');
INSERT INTO grade VALUES (70, 79, 'C');
INSERT INTO grade VALUES (60, 69, 'D');
INSERT INTO grade VALUES (0, 59, 'E');

SELECT * FROM grade;
+------+------+-------+
| low  | upp  | grade |
+------+------+-------+
|   90 |  100 | A     |
|   80 |   89 | B     |
|   70 |   79 | C     |
|   60 |   69 | D     |
|    0 |   59 | E     |
+------+------+-------+

查询所有学生的 s_no 、c_no 和 grade 列。

思路是，使用区间 ( BETWEEN ) 查询，判断学生的成绩 ( degree ) 在 grade 表的 low和 upp 之间。

SELECT s_no, c_no, grade FROM score, grade
WHERE degree BETWEEN low AND upp;
+------+-------+-------+
| s_no | c_no  | grade |
+------+-------+-------+
| 101  | 3-105 | A     |
| 102  | 3-105 | A     |
| 103  | 3-105 | A     |
| 103  | 3-245 | B     |
| 103  | 6-166 | B     |
| 104  | 3-105 | B     |
| 105  | 3-105 | B     |
| 105  | 3-245 | C     |
| 105  | 6-166 | C     |
| 109  | 3-105 | C     |
| 109  | 3-245 | D     |
| 109  | 6-166 | B     |
+------+-------+-------+

连接查询

准备用于测试连接查询的数据：

CREATE DATABASE testJoin;

CREATE TABLE person (
    id INT,
    name VARCHAR(20),
    cardId INT
);

CREATE TABLE card (
    id INT,
    name VARCHAR(20)
);

INSERT INTO card VALUES (1, '饭卡'), (2, '建行卡'), (3, '农行卡'), (4, '工商卡'), (5, '邮政卡');
SELECT * FROM card;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 饭卡      |
|    2 | 建行卡    |
|    3 | 农行卡    |
|    4 | 工商卡    |
|    5 | 邮政卡    |
+------+-----------+

INSERT INTO person VALUES (1, '张三', 1), (2, '李四', 3), (3, '王五', 6);
SELECT * FROM person;
+------+--------+--------+
| id   | name   | cardId |
+------+--------+--------+
|    1 | 张三   |      1 |
|    2 | 李四   |      3 |
|    3 | 王五   |      6 |
+------+--------+--------+

分析两张表发现，person 表并没有为 cardId 字段设置一个在 card 表中对应的 id外键。如果设置了的话，person 中 cardId 字段值为 6 的行就插不进去，因为该cardId 值在 card 表中并没有。

内连接

要查询这两张表中有关系的数据，可以使用 INNER JOIN ( 内连接 ) 将它们连接在一起。

-- INNER JOIN: 表示为内连接，将两张表拼接在一起。
-- on: 表示要执行某个条件。
SELECT * FROM person INNER JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
+------+--------+--------+------+-----------+

-- 将 INNER 关键字省略掉，结果也是一样的。
-- SELECT * FROM person JOIN card on person.cardId = card.id;注意：card 的整张表被连接到了右边。

左外连接

完整显示左边的表 ( person ) ，右边的表如果符合条件就显示，不符合则补 NULL 。

-- LEFT JOIN 也叫做 LEFT OUTER JOIN，用这两种方式的查询结果是一样的。
SELECT * FROM person LEFT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
+------+--------+--------+------+-----------+

右外链接

完整显示右边的表 ( card ) ，左边的表如果符合条件就显示，不符合则补 NULL 。

SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+

全外链接

完整显示两张表的全部数据。

-- MySQL 不支持这种语法的全外连接
-- SELECT * FROM person FULL JOIN card on person.cardId = card.id;
-- 出现错误：
-- ERROR 1054 (42S22): Unknown column 'person.cardId' in 'on clause'

-- MySQL全连接语法，使用 UNION 将两张表合并在一起。
SELECT * FROM person LEFT JOIN card on person.cardId = card.id
UNION
SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+

全文到这里撒花完结~

跳转错误

MySQL SQL 优化命令行&问题 SQL 抓取方式

1. MySQL优化基础

2. MySQL索引特点：

1. EXPLAIN

3. OPTIMIZER_TRACE

1. MySQL慢日志

2. events_statements_summary_by_digest统计的SQL语句

CentOS 落幕，将于2021年底结束维护

红旗Linux桌面操作系统11来了

Linux 下自动应答工具 Expect 使用指南

1. 软件介绍

2. 工具安装

3. 基础知识

4. 控制结构

5. 简单使用

6. 高级示例

7. 参考博客

Oracle云时代MySQL HTAP解决方案

MySQL查看及杀掉链接方法大全

1.查看数据库链接

2.杀掉数据库链接

null调整为not null default xxx，不得不注意的坑

常用 Linux 发行版操作系统大盘点！

眼花缭乱！

按是否商业版来划分

按包管理器来划分

Debian

Ubuntu

RedHat

CentOS

Fedora

SUSE

Arch

Manjaro

Gentoo

其他Linux发行版

选用建议

Linus：我们都老了，但Linux维护后继无人

20000 字干货笔记，一天搞定 MySQL ！

分组计算平均成绩

多表查询 - 1

多表查询 - 2

三表关联查询

子查询加分组求平均分

子查询 - 1

子查询 - 2

YEAR 函数与带 IN 关键字查询

多层嵌套子查询

多表查询

子查询 - 3

ANY 表示至少一个 - DESC ( 降序 )

表示所有的 ALL

复制表的数据作为条件查询

子查询 - 4

条件加组筛选

NOT LIKE 模糊查询取反

YEAR 与 NOW 函数

MAX 与 MIN 函数

多段排序

子查询 - 5

MAX 函数与子查询

子查询 - 6

子查询 - 7

子查询 - 8

按等级查询

连接查询

内连接

左外连接

右外链接