作者：杨涛涛
资深数据库专家，专研 MySQL 十余年。善于 MySQL、PostgreSQL、MongoDB 等开源数据库相干的备份复原、SQL 调优、监控运维、高可用架构设计等。目前任职于爱可生，为各大运营商及银行金融企业提供 MySQL 相干技术支持、MySQL 相干课程培训等工作。
本文起源：原创投稿
* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。
前言
我之前有一篇介绍在 MySQL SHELL 环境中如何对文档类数据进行操作，然而 MySQL SHELL 性能很多，除了能够操作文档类数据，也能够对关系表进行各种 DDL,DML 等操作。这里我就用几个简略例子来示范下如何用 MySQL SHELL 操作关系表。
此处援用的数据库示例基于官网的 SAMPLE DATABASE：WORLD，表构造以及数据能够自行下载。
MySQL SHELL 对关系型数据库的操作波及到三个组件：
- MySQL：传统 mysql，操作比较简单，除了写法有些差别外，基本上等同于 SQL 操作。
- MySQL X：基于 X DEV 协定操作 mysql，其中蕴含很多类，除了能够操作文档数据，也能够操作关系表。
- SHELL：蕴含了以上两个组件，能够随便切换，重点在于如何抉择连贯协定。
咱们来顺次看看各个组件对关系表的罕用检索形式。
第一：mysql 组件
连贯数据库：mysql.get_session 或者 mysql.get_classic_session
能够用如下传统拼串形式连贯数据库：
MySQL Py > connection_url="mysql://root:root@localhost/world?socket=(/var/lib/mysql/official/mysql.sock)"MySQL Py > ytt_cn1 = mysql.get_session(connection_url);MySQL Py > ytt_cn1<ClassicSession:root@localhost>也能够用字典的形式连贯数据库：
MySQL Py > connection_url={"schema":"world","user":"root","password":"root","socket":"/var/lib/mysql/official/mysql.sock"}MySQL Py > ytt_cn1=mysql.get_session(connection_url);MySQL Py > ytt_cn1<ClassicSession:root@/var%2Flib%2Fmysql%2Fofficial%2Fmysql.sock>接下来能够用 ClassicSession 类提供的各种办法来对关系表进行相干操作，所有的操作都能够间接用函数 run_sql 来执行：
对表 city 查问：
MySQL Py > ytt_cn1.run_sql("table city limit 1")+----+-------+-------------+----------+------------+| ID | Name | CountryCode | District | Population |+----+-------+-------------+----------+------------+| 1 | Kabul | AFG | Kabol | 1780000 |+----+-------+-------------+----------+------------+1 row in set (0.0005 sec)对表 city 插入：
MySQL Py > ytt_cn1.run_sql("insert into city(name,countrycode,population,district) values ('test','CHN',1000000,'dd')")Query OK, 1 row affected (0.0079 sec)MySQL Py > ytt_cn1.run_sql("select * from city where name ='test'")+------+------+-------------+----------+------------+| ID | Name | CountryCode | District | Population |+------+------+-------------+----------+------------+| 4097 | test | CHN | dd | 1000000 |+------+------+-------------+----------+------------+1 row in set (0.0032 sec)对表 city 更新：
MySQL Py > ytt_cn1.run_sql("update city set name='who know ?' where id=4097")Query OK, 1 row affected (0.0894 sec)Rows matched: 1 Changed: 1 Warnings: 0MySQL Py > ytt_cn1.run_sql("select * from city where id=4097")+------+------------+-------------+----------+------------+| ID | Name | CountryCode | District | Population |+------+------------+-------------+----------+------------+| 4097 | who know ? | CHN | dd | 1000000 |+------+------------+-------------+----------+------------+1 row in set (0.0005 sec)对表 city 删除：
MySQL Py > ytt_cn1.run_sql("delete from city where id=4097")Query OK, 1 row affected (0.0739 sec)MySQL Py > ytt_cn1.run_sql("select * from city where id=4097")Empty set (0.0004 sec)开启一个事务块:
MySQL Py > ytt_cn1.start_transaction();Query OK, 0 rows affected (0.0003 sec)MySQL Py > ytt_cn1.run_sql("delete from city where id =1")Query OK, 1 row affected (0.0006 sec)MySQL Py > ytt_cn1.rollback();Query OK, 0 rows affected (0.2070 sec)MySQL Py > ytt_cn1.run_sql("select * from city where id = 1")+----+-------+-------------+----------+------------+| ID | Name | CountryCode | District | Population |+----+-------+-------------+----------+------------+| 1 | Kabul | AFG | Kabol | 1780000 |+----+-------+-------------+----------+------------+1 row in set (0.0004 sec)敞开连贯：
MySQL Py > ytt_cn1.close();MySQL Py > ytt_cn1<ClassicSession:disconnected>第二：mysqlx 组件
MySQL X 组件蕴含了很多类，上面我来举几个罕用的例子：
仍然是先连贯数据库 world：X 协定端口 33060 或者 X SOCKET（用 mysqlx.get_session 办法）。
MySQL Py > connection_urlx="mysqlx://root:root@localhost/world?socket=(/var/lib/mysql/official/mysqlx.sock)"MySQL Py > ytt_cnx1=mysqlx.get_session(connection_urlx);比方找出人口小于800的城市并且列出对应的国家名字：
SQL： select a.id,a.name,b.name country_name, a.population from city a join country b on (a.countrycode = b.code and a.population < 800);SQLRESULT 类：相似于 mysql 游标用法
MySQL Py > sql1="select a.id,a.name,b.name country_name, a.population from city a join country b on (a.countrycode = b.code and a.population < 800)"MySQL Py > sql_result1=ytt_cnx1.run_sql(sql1)获取前两行：默认不带字段名
MySQL Py > sql_result1.fetch_one()[ 62, "The Valley", "Anguilla", 595]MySQL Py > sql_result1.fetch_one()[ 1791, "Flying Fish Cove", "Christmas Island", 700]获取带字段名的记录：
MySQL Py > sql_result1.fetch_one_object();{ "country_name": "Cocos (Keeling) Islands", "id": 2316, "name": "Bantam", "population": 503}一次性获取残余的行：
MySQL Py > sql_result1.fetch_all()[ [ 2317, "West Island", "Cocos (Keeling) Islands", 167 ], [ 2728, "Yaren", "Nauru", 559 ], [ 2805, "Alofi", "Niue", 682 ], [ 2912, "Adamstown", "Pitcairn", 42 ], [ 3333, "Fakaofo", "Tokelau", 300 ], [ 3538, "Città del Vaticano", "Holy See (Vatican City State)", 455 ]]SqlExecute 类：相似于 prepare 语句用法
比方把之前的人口判断条件替换为绑定变量（？或者变量(:a)），这样能够不便多个条件一起查问。
MySQL Py > sql2="select a.id,a.name,b.name country_name, a.population from city a join country b on (a.countrycode = b.code and a.population < ?)"MySQL Py > sql_result2=ytt_cnx1.sql(sql2);给定两个不同的人口条件：
MySQL Py > a=800MySQL Py > b=500绑定变量执行后果：
MySQL Py > sql_result2.bind(a)+------+--------------------+-------------------------------+------------+| id | name | country_name | population |+------+--------------------+-------------------------------+------------+| 62 | The Valley | Anguilla | 595 || 1791 | Flying Fish Cove | Christmas Island | 700 || 2316 | Bantam | Cocos (Keeling) Islands | 503 || 2317 | West Island | Cocos (Keeling) Islands | 167 || 2728 | Yaren | Nauru | 559 || 2805 | Alofi | Niue | 682 || 2912 | Adamstown | Pitcairn | 42 || 3333 | Fakaofo | Tokelau | 300 || 3538 | Città del Vaticano | Holy See (Vatican City State) | 455 |+------+--------------------+-------------------------------+------------+9 rows in set (0.0022 sec)MySQL Py > sql_result2.bind(b)+------+--------------------+-------------------------------+------------+| id | name | country_name | population |+------+--------------------+-------------------------------+------------+| 2317 | West Island | Cocos (Keeling) Islands | 167 || 2912 | Adamstown | Pitcairn | 42 || 3333 | Fakaofo | Tokelau | 300 || 3538 | Città del Vaticano | Holy See (Vatican City State) | 455 |+------+--------------------+-------------------------------+------------+4 rows in set (0.0023 sec)Table 类：获取以后连贯数据库下单张表，能够对这张表进行任何 DML 操作。（获取 Table 类之前，得先获取 Schema 类）
MySQL Py > ytt_schema1=ytt_cnx1.get_schema('world')MySQL Py > ytt_tbname1=ytt_schema1.get_table('city'); 查找人口少于 800 的记录：
MySQL Py > ytt_tbname1.select().where("population<800")+------+--------------------+-------------+-------------+------------+| ID | Name | CountryCode | District | Population |+------+--------------------+-------------+-------------+------------+| 62 | The Valley | AIA | – | 595 || 1791 | Flying Fish Cove | CXR | – | 700 || 2316 | Bantam | CCK | Home Island | 503 || 2317 | West Island | CCK | West Island | 167 || 2728 | Yaren | NRU | – | 559 || 2805 | Alofi | NIU | – | 682 || 2912 | Adamstown | PCN | – | 42 || 3333 | Fakaofo | TKL | Fakaofo | 300 || 3538 | Città del Vaticano | VAT | – | 455 |+------+--------------------+-------------+-------------+------------+9 rows in set (0.0024 sec)还能够持续排序以及限度记录数输入：
MySQL Py > ytt_tbname1.select().where("population<800").order_by("population desc ").limit(3)+------+------------------+-------------+----------+------------+| ID | Name | CountryCode | District | Population |+------+------------------+-------------+----------+------------+| 1791 | Flying Fish Cove | CXR | – | 700 || 2805 | Alofi | NIU | – | 682 || 62 | The Valley | AIA | – | 595 |+------+------------------+-------------+----------+------------+3 rows in set (0.0024 sec)Table 类蕴含几个子类：TableSelect、TableInsert、TableUpdate、TableDelete。
TableSelect：保留查问后果
之前查找人口小于 800 的记录后果即为 TableSelect，能够基于此类来后续操作
MySQL Py > tbselect1=ytt_tbname1.select().where("population<800")只拿出局部字段：
MySQL Py > tbselect1.select("[id,name]").order_by("population desc").limit(2);+----------------------------+| JSON_ARRAY(`id`,`name`) |+----------------------------+| [1791, "Flying Fish Cove"] || [2805, "Alofi"] |+----------------------------+2 rows in set (0.0031 sec)TableInsert：执行插入语句
插入一行：
MySQL Py > ytt_tbname1.count()4081MySQL Py > tbinsert1=ytt_tbname1.insert(["name","population","countrycode","district"]).values('test',1000000,'CHN','dd');MySQL Py > tbinsert1.execute();Query OK, 1 item affected (0.0054 sec)MySQL Py > ytt_tbname1.count()4082插入多行：有两种办法
多 VALUES 模式：
MySQL Py > tbinsert1=ytt_tbname1.insert(["name","population","countrycode","district"]).values('test',1000000,'CHN','dd').values('test',1000000,'CHN','dd');MySQL Py > tbinsert1.execute()Query OK, 2 items affected (0.0325 sec)Records: 2 Duplicates: 0 Warnings: 0MySQL Py > MySQL Py > ytt_tbname1.count()4084屡次执行或者蕴含在事务块里：
MySQL Py > ytt_cnx1.start_transaction();Query OK, 0 rows affected (0.0004 sec)MySQL Py > tbinsert1=ytt_tbname1.insert(["name","population","countrycode","district"]).values('test',1000000,'CHN','dd');MySQL Py > tbinsert1Query OK, 1 item affected (0.0008 sec)MySQL Py > tbinsert1Query OK, 1 item affected (0.0006 sec)MySQL Py > tbinsert1Query OK, 1 item affected (0.0008 sec)MySQL Py > tbinsert1Query OK, 1 item affected (0.0006 sec)MySQL Py > ytt_cnx1.commit();Query OK, 0 rows affected (0.2737 sec)MySQL Py > ytt_tbname1.count()4088TableUpdate：执行更新语句
MySQL Py > tbupdate1=ytt_tbname1.update().set('district','nothing').where("name='test'")MySQL Py > tbupdate1Query OK, 0 items affected (0.0048 sec)Rows matched: 9 Changed: 9 Warnings: 0TableDelete：执行删除语句
MySQL Py > tbdelete1=ytt_tbname1.delete().where("district='nothing'");MySQL Py > tbdelete1Query OK, 9 items affected (0.0112 sec)MySQL Py > ytt_tbname1.count()4079第三：SHELL 组件
SHELL 组件能够在 MySQL 和 MySQL X 间随便切换，并且连贯后，蕴含了一个默认数据库类 “db”，db 等价于 ytt_cnx1.get_current_schema()，
MySQL Py > ytt_cnx_shell1=shell.connect(connection_urlx)Creating an X protocol session to 'root@localhost/world'Fetching schema names for autocompletion... Press ^C to stop.Your MySQL connection id is 10 (X protocol)Server version: 8.0.23 MySQL Community Server - GPLDefault schema `world` accessible through db仍然还是操作表 city，
MySQL localhost+ ssl world Py > ytt_tbname2=db.get_table("city")MySQL localhost+ ssl world Py > ytt_tbname2<Table:city>之后的操作和之前 mysqlx 的一样。
MySQL localhost+ ssl world Py > ytt_tbname2.select(['id','name']).where("population<800").order_by("id desc").limit(3);+------+--------------------+| id | name |+------+--------------------+| 3538 | Città del Vaticano || 3333 | Fakaofo || 2912 | Adamstown |+------+--------------------+3 rows in set (0.0011 sec)所以如果用 MySQL SHELL 来操作 mysql 关系表，举荐用 SHELL 组件的形式，非常灵活。