前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >技术分享 | MySQL SHELL 是如何操作关系表的?

技术分享 | MySQL SHELL 是如何操作关系表的?

作者头像
爱可生开源社区
发布2021-04-23 10:21:20
2.1K0
发布2021-04-23 10:21:20
举报
文章被收录于专栏:爱可生开源社区

作者:杨涛涛

资深数据库专家,专研 MySQL 十余年。擅长 MySQL、PostgreSQL、MongoDB 等开源数据库相关的备份恢复、SQL 调优、监控运维、高可用架构设计等。目前任职于爱可生,为各大运营商及银行金融企业提供 MySQL 相关技术支持、MySQL 相关课程培训等工作。

本文来源:原创投稿

* 爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。


前言

我之前有一篇介绍在 MySQL SHELL 环境中如何对文档类数据进行操作的文章(MySQL 在NOSQL 领域冲锋陷阵),但是 MySQL SHELL 功能很多,除了可以操作文档类数据,也可以对关系表进行各种 DDL,DML 等操作。这里我就用几个简单例子来示范下如何用 MySQL SHELL 操作关系表。

此处引用的数据库示例基于官方的 SAMPLE DATABASE:WORLD,表结构以及数据可以自行下载。

MySQL SHELL 对关系型数据库的操作涉及到三个组件:

  1. MySQL:传统 mysql,操作比较简单,除了写法有些差异外,基本上等同于 SQL 操作。
  2. MySQL X:基于 X DEV 协议操作 mysql,其中包含很多类,除了可以操作文档数据,也可以操作关系表。
  3. SHELL:包含了以上两个组件,可以随意切换,重点在于如何选择连接协议。

我们来依次看看各个组件对关系表的常用检索方式。

一、mysql 组件

连接数据库:mysql.get_session 或者 mysql.get_classic_session

可以用如下传统拼串方式连接数据库:

代码语言:javascript
复制
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>

也可以用字典的方式连接数据库:

代码语言:javascript
复制
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 查询:

代码语言:javascript
复制
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 插入:

代码语言:javascript
复制
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 更新:

代码语言:javascript
复制
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: 0
MySQL  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 删除:

代码语言:javascript
复制
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)

开启一个事务块:

代码语言:javascript
复制
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)

关闭连接:

代码语言:javascript
复制
MySQL  Py > ytt_cn1.close();
MySQL  Py > ytt_cn1
<ClassicSession:disconnected>
二、mysqlx 组件

MySQL X 组件包含了很多类,下面我来举几个常用的例子:

依然是先连接数据库 world:X 协议端口 33060 或者 X SOCKET(用 mysqlx.get_session 方法)。

代码语言:javascript
复制
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的城市并且列出对应的国家名字:

代码语言:javascript
复制
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);
2.1 SQLRESULT 类:类似于 mysql 游标用法
代码语言:javascript
复制
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)

获取前两行:默认不带字段名

代码语言:javascript
复制
MySQL  Py > sql_result1.fetch_one()
[
    62,
    "The Valley",
    "Anguilla",
    595
]
MySQL  Py > sql_result1.fetch_one()
[
    1791,
    "Flying Fish Cove",
    "Christmas Island",
    700
]

获取带字段名的记录:

代码语言:javascript
复制
MySQL  Py > sql_result1.fetch_one_object();
{
    "country_name": "Cocos (Keeling) Islands", 
    "id": 2316, 
    "name": "Bantam", 
    "population": 503
}

一次性获取剩余的行:

代码语言:javascript
复制
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
    ]
]
2.2 SqlExecute 类:类似于 prepare 语句用法

比如把之前的人口判断条件替换为绑定变量(?或者变量(:a)),这样可以方便多个条件一起查询。

代码语言:javascript
复制
 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);

给定两个不同的人口条件:

代码语言:javascript
复制
MySQL  Py > a=800
MySQL  Py > b=500

绑定变量执行结果:

代码语言:javascript
复制
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)
2.3 Table 类:获取当前连接数据库下单张表,可以对这张表进行任何 DML 操作。(获取 Table 类之前,得先获取 Schema 类)
代码语言:javascript
复制
MySQL  Py > ytt_schema1=ytt_cnx1.get_schema('world')

MySQL  Py > ytt_tbname1=ytt_schema1.get_table('city');

查找人口少于 800 的记录:

代码语言:javascript
复制
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)

还可以继续排序以及限制记录数输出:

代码语言:javascript
复制
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)
2.4 Table 类包含几个子类:TableSelect、TableInsert、TableUpdate、TableDelete。

1)TableSelect:保存查询结果

之前查找人口小于 800 的记录结果即为 TableSelect,可以基于此类来后续操作。

代码语言:javascript
复制
MySQL  Py > tbselect1=ytt_tbname1.select().where("population<800")

只拿出部分字段:

代码语言:javascript
复制
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)

2)TableInsert:执行插入语句

插入一行:

代码语言:javascript
复制
MySQL  Py > ytt_tbname1.count()
4081
MySQL  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 形式:

代码语言:javascript
复制
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: 0
MySQL  Py > 

MySQL  Py > ytt_tbname1.count()
4084

多次执行或者包含在事务块里:

代码语言:javascript
复制
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 > tbinsert1
Query OK, 1 item affected (0.0008 sec)
MySQL  Py > tbinsert1
Query OK, 1 item affected (0.0006 sec)
MySQL  Py > tbinsert1
Query OK, 1 item affected (0.0008 sec)
MySQL  Py > tbinsert1
Query 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()
4088

3)TableUpdate:执行更新语句

代码语言:javascript
复制
MySQL  Py > tbupdate1=ytt_tbname1.update().set('district','nothing').where("name='test'")
MySQL  Py > tbupdate1
Query OK, 0 items affected (0.0048 sec)

Rows matched: 9  Changed: 9  Warnings: 0

4)TableDelete:执行删除语句

代码语言:javascript
复制
MySQL  Py > tbdelete1=ytt_tbname1.delete().where("district='nothing'");
MySQL  Py > tbdelete1
Query 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()

代码语言:javascript
复制
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 - GPL
Default schema `world` accessible through db

依然还是操作表 city,

代码语言:javascript
复制
 MySQL  localhost+ ssl  world  Py > ytt_tbname2=db.get_table("city")
 MySQL  localhost+ ssl  world  Py > ytt_tbname2
<Table:city>

之后的操作和之前 mysqlx 的一样。

代码语言:javascript
复制
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 组件的方式,非常灵活。

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2021-04-14,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 爱可生开源社区 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 前言
  • MySQL SHELL 对关系型数据库的操作涉及到三个组件:
    • 一、mysql 组件
      • 二、mysqlx 组件
        • 三、SHELL 组件
        相关产品与服务
        云数据库 SQL Server
        腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
        领券
        问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档