mysql-py与关系表

您还可以使用X DevAPI来处理关系表。在MySQL中，每个关系表都与一个特定的存储引擎关联。本节中的示例使用架构中的InnoDB表world_x。

确认架构

要显示分配给db全局变量的模式，请发出db。

mysql-py> db
<Schema:world_x>

如果返回的值不是Schema:world_x，db则按如下所示设置变量：

mysql-py> \use world_x
Schema `world_x` accessible through db.

显示所有表格

要在world_x架构中显示所有关系表，请get_tables()在db对象上使用方法。

mysql-py> db.get_tables()
[
    <Table:city>,
    <Table:country>,
    <Table:countrylanguage>
]

基本表操作

表所涵盖的基本操作包括：

操作形式	描述
`db.name.insert()`	所述插入件（）方法插入一个或多个记录到指定的表。
`db.name.select()`	该选择（）方法返回的指定表中的部分或全部记录。
`db.name.update()`	该更新（）方法更新命名表中的记录。
`db.name.delete()`	该删除（）方法删除指定表中的一个或多个记录。

将记录插入表

您可以将insert()方法与方法一起使用，values()以将记录插入现有的关系表中。该insert()方法接受表中的单个列或所有列。使用一种或多种values()方法来指定要插入的值。

插入完整记录

要插入完整的记录，请将insert()表中的所有列传递给该方法。然后将values()每一列的值传递给该方法。例如，要将新记录添加到world_x数据库的城市表中，请插入以下记录，然后按两次Enter。

mysql-py> db.city.insert("ID", "Name", "CountryCode", "District", "Info").values(
None, "Olympia", "USA", "Washington", '{"Population": 5000}')

城市表有五列：ID，名称，国家/地区代码，地区和信息。每个值必须匹配其表示的列的数据类型。

插入部分记录

下面的示例将值插入到城市表的ID，Name和CountryCode列中。

mysql-py> db.city.insert("ID", "Name", "CountryCode").values(
None, "Little Falls", "USA").values(None, "Happy Valley", "USA")

使用insert()方法指定列时，值的数量必须与列的数量匹配。在上一个示例中，您必须提供三个值以匹配指定的三列。

选择表

您可以使用该select()方法查询数据库中的表并从中返回记录。X DevAPI提供了其他方法，可以与该方法一起使用，select()以对返回的记录进行过滤和排序。

MySQL提供以下操作符来指定搜索条件：OR（||），（AND），&&，，XOR，IS，NOT，BETWEEN，IN，LIKE，!=，<>，>，>=，<，<=，&，|，<<，>>，+，，-，和。*/~%

选择所有记录

要发出查询以返回现有表中的所有记录的查询，请使用该select()方法而不指定搜索条件。下面的示例从world_x数据库的城市表中选择所有记录。

注意
将empty select()方法的使用限制为交互式语句。始终在应用程序代码中使用显式的列名选择。

mysql-py> db.city.select()
+------	+------------	+-------------	+------------	+-------------------------	+
| ID	| Name	| CountryCode	| District	| Info	|
+------	+------------	+-------------	+------------	+-------------------------	+
|    1	| Kabul	| AFG	| Kabol	|{"Population": 1780000}	|
|    2	| Qandahar	| AFG	| Qandahar	|{"Population": 237500}	|
|    3	| Herat	| AFG	| Herat	|{"Population": 186800}	|
...    ...          ...           ...          ...
| 4079	| Rafah	| PSE	| Rafah	|{"Population": 92020}	|
+------+------- ----+-------------+------------+-------------------------+
4082 rows in set (0.01 sec)

空集（无匹配记录）将返回以下信息：

空置（0.00秒）

筛选搜寻

要发出返回一组表列的查询，请使用select()方法并指定要在方括号之间返回的列。该查询从城市表返回Name和CountryCode列。

mysql-py> db.city.select(["Name", "CountryCode"])
+-------------------	+-------------	+
| Name	| CountryCode	|
+-------------------	+-------------	+
| Kabul	| AFG	|
| Qandahar	| AFG	|
| Herat	| AFG	|
| Mazar-e-Sharif	| AFG	|
| Amsterdam	| NLD	|
...                 ...
| Rafah	| PSE	|
| Olympia	| USA	|
| Little Falls	| USA	|
| Happy Valley	| USA	|
+-------------------	+-------------	+
4082 rows in set (0.00 sec)

要发出返回与特定搜索条件匹配的行的查询，请使用该where()方法来包括那些条件。例如，以下示例返回以字母Z开头的城市的名称和国家/地区代码。

mysql-py> db.city.select(["Name", "CountryCode"]).where("Name like 'Z%'")
+-------------------	+-------------	+
| Name	| CountryCode	|
+-------------------	+-------------	+
| Zaanstad	| NLD	|
| Zoetermeer	| NLD	|
| Zwolle	| NLD	|
| Zenica	| BIH	|
| Zagazig	| EGY	|
| Zaragoza	| ESP	|
| Zamboanga	| PHL	|
| Zahedan	| IRN	|
| Zanjan	| IRN	|
| Zabol	| IRN	|
| Zama	| JPN	|
| Zhezqazghan	| KAZ	|
| Zhengzhou	| CHN	|
...                 ...
| Zeleznogorsk	| RUS	|
+-------------------	+-------------	+
59 rows in set (0.00 sec)

您可以使用bind()方法将值与搜索条件分开。例如，不使用“ Name ='Z％'”作为条件，而是替换一个由冒号组成的命名占位符，后跟一个以字母开头的名称，例如name。然后，在bind()方法中包含占位符和值，如下所示：

mysql-py> db.city.select(["Name", "CountryCode"]).where(
"Name like :name").bind("name", "Z%")

注意

在程序中，绑定使您可以在表达式中指定占位符，这些占位符在执行前会用值填充，并且可以根据需要从自动转义中受益。

始终使用绑定来清理输入。避免在使用字符串连接的查询中引入值，这会产生无效的输入，并在某些情况下会导致安全问题。

项目成果

要使用AND运算符发出查询，请在where()方法的搜索条件之间添加运算符。

mysql-py> db.city.select(["Name", "CountryCode"]).where(
"Name like 'Z%' and CountryCode = 'CHN'")
+----------------	+-------------	+
| Name	| CountryCode	|
+----------------	+-------------	+
| Zhengzhou	| CHN	|
| Zibo	| CHN	|
| Zhangjiakou	| CHN	|
| Zhuzhou	| CHN	|
| Zhangjiang	| CHN	|
| Zigong	| CHN	|
| Zaozhuang	| CHN	|
...              ...
| Zhangjiagang	| CHN	|
+----------------	+-------------	+
22 rows in set (0.01 sec)

要指定多个条件运算符，可以将搜索条件括在括号中以更改运算符优先级。以下示例演示了AND和OR运算符的位置。

mysql-py> db.city.select(["Name", "CountryCode"]).where(
"Name like 'Z%' and (CountryCode = 'CHN' or CountryCode = 'RUS')")
+-------------------	+-------------	+
| Name	| CountryCode	|
+-------------------	+-------------	+
| Zhengzhou	| CHN	|
| Zibo	| CHN	|
| Zhangjiakou	| CHN	|
| Zhuzhou	| CHN	|
...                 ...
| Zeleznogorsk	| RUS	|
+-------------------	+-------------	+
29 rows in set (0.01 sec)

限价，定单和抵消结果

您可以应用limit()，order_by()和offset()方法来管理由返回的记录的数量和顺序select()的方法。

若要指定结果集中包含的记录数，请在limit()方法后附加一个值select()。例如，以下查询返回国家表中的前五个记录。

mysql-py> db.country.select(["Code", "Name"]).limit(5)
+------	+-------------	+
| Code	| Name	|
+------	+-------------	+
| ABW	| Aruba	|
| AFG	| Afghanistan	|
| AGO	| Angola	|
| AIA	| Anguilla	|
| ALB	| Albania	|
+------	+-------------	+
5 rows in set (0.00 sec)

要指定结果的顺序，请将order_by()方法附加到select()方法中。将order_by()一列或多列的列表传递给该方法，以按适当的降序（desc）或升序（asc）属性进行排序。升序是默认的订单类型。

例如，以下查询按“名称”列对所有记录进行排序，然后以降序返回前三个记录。

mysql-py> db.country.select(["Code", "Name"]).order_by(["Name desc"]).limit(3)
+------	+------------	+
| Code	| Name	|
+------	+------------	+
| ZWE	| Zimbabwe	|
| ZMB	| Zambia	|
| YUG	| Yugoslavia	|
+------	+------------	+
3 rows in set (0.00 sec)

默认情况下，该limit()方法从表中的第一条记录开始。您可以使用该offset()方法来更改起始记录。例如，要忽略第一条记录并返回与条件匹配的后三条记录，请向offset()方法传递值1。

mysql-py> db.country.select(["Code", "Name"]).order_by(["Name desc"]).limit(3).offset(1)
+------	+------------	+
| Code	| Name	|
+------	+------------	+
| ZMB	| Zambia	|
| YUG	| Yugoslavia	|
| YEM	| Yemen	|
+------	+------------	+
3 rows in set (0.00 sec)

更新表

您可以使用该update()方法来修改表中的一个或多个记录。该update()方法通过过滤查询以仅包括要更新的记录，然后将您指定的操作应用于这些记录来工作。

要替换城市表中的城市名称，set()请将新的城市名称传递给方法。然后，将where()方法传递给城市名称以查找和替换。以下示例将北京替换为北京。

mysql-py> db.city.update().set("Name", "Beijing").where("Name = 'Peking'")

使用该select()方法来验证更改。

mysql-py> db.city.select(["ID", "Name", "CountryCode", "District", "Info"]).where("Name = 'Beijing'")
+------	+-----------	+-------------	+----------	+-----------------------------	+
| ID	| Name	| CountryCode	| District	| Info	|
+------	+-----------	+-------------	+----------	+-----------------------------	+
| 1891	| Beijing	| CHN	| Peking	| {"Population": 7472000}	|
+------	+-----------	+-------------	+----------	+-----------------------------	+
1 row in set (0.00 sec)

删除表

您可以使用该delete()方法从数据库的表中删除部分或全部记录。X DevAPI提供了其他方法，可以与该方法一起使用，delete()以过滤和排序要删除的记录。

使用条件删除记录

下面的示例将搜索条件传递给该delete()方法。所有符合条件的记录将从城市表中删除。在此示例中，一条记录符合条件。

mysql-py> db.city.delete().where("Name = 'Olympia'")

删除第一条记录

要删除城市表中的第一条记录，请使用limit()值为1 的方法。

mysql-py> db.city.delete().limit(1)

删除表中的所有记录

您可以删除表中的所有记录。为此，请使用该delete()方法而不指定搜索条件。

警告
在删除记录而不指定搜索条件时要小心。此操作将从表中删除所有记录。

放一张桌子

该drop_collection()方法在MySQL Shell中也用于从数据库中删除关系表。例如，citytest要从world_x数据库中删除表，请发出：

mysql-py> db.drop_collection("citytest")

表中的文件

在MySQL中，表可能包含传统的关系数据和/或JSON值。您可以通过将文档存储在具有本机JSON数据类型的列中来将传统数据与JSON文档结合在一起。

本节中的示例使用world_x架构中的city表。

城市表说明

城市表有五列（或字段）。

+ --------------- + ------------ + ------- + ------- + ---- ----- + ------------------ +
| 领域| 类型 空| 关键 默认值| 额外|
+ --------------- + ------------ + ------- + ------- + ---- ----- + ------------------ +
| ID | int（11）| 否| PRI | 空| auto_increment |
| 姓名| 字符（35）| 否| | | |
| 国家代码| char（3）| 否| | | |
| 区| 字符（20）| 否| | | |
| 信息| json | 是的 | 空| |
+ --------------- + ------------ + ------- + ------- + ---- ----- + ------------------ +

插入记录

要将文档插入到表的列中，请以values()正确的顺序将格式正确的JSON文档传递给该方法。在以下示例中，将传递文档作为最终值，以将其插入到“信息”列中。

mysql-py> db.city.insert().values(
None, "San Francisco", "USA", "California", '{"Population":830000}')

选择一条记录

您可以使用查询条件来发出查询，该查询条件可以评估表达式中的文档值。

mysql-py> db.city.select(["ID", "Name", "CountryCode", "District", "Info"]).where(
"CountryCode = :country and Info->'$.Population' > 1000000").bind(
'country', 'USA')
+------	+----------------	+-------------	+----------------	+-----------------------------	+
| ID	| Name	| CountryCode	| District	| Info	|
+------	+----------------	+-------------	+----------------	+-----------------------------	+
| 3793	| New York	| USA	| New York	| {"Population": 8008278}	|
| 3794	| Los Angeles	| USA	| California	| {"Population": 3694820}	|
| 3795	| Chicago	| USA	| Illinois	| {"Population": 2896016}	|
| 3796	| Houston	| USA	| Texas	| {"Population": 1953631}	|
| 3797	| Philadelphia	| USA	| Pennsylvania	| {"Population": 1517550}	|
| 3798	| Phoenix	| USA	| Arizona	| {"Population": 1321045}	|
| 3799	| San Diego	| USA	| California	| {"Population": 1223400}	|
| 3800	| Dallas	| USA	| Texas	| {"Population": 1188580}	|
| 3801	| San Antonio	| USA	| Texas	| {"Population": 1144646}	|
+------	+----------------	+-------------	+----------------	+-----------------------------	+
9 rows in set (0.01 sec)