第 6 章 使用关系表

X DevAPI SQL CRUD 函数允许您以类似于使用传统 SQL 语句的方式使用关系表。以下代码示例演示了如何使用 X DevAPI SQL CRUD 函数的 add() 和 select() 方法，这类似于在表上使用 SQL 客户端运行 INSERT 和 SELECT 语句。将此与 第 4.3 节，“Collection CRUD 函数概述” 中找到的示例进行比较，以了解 X DevAPI 中表和集合的 CRUD 函数之间的差异和相似之处。

MySQL Shell JavaScript 代码

// Working with Relational Tables
var mysqlx = require('mysqlx');

// Connect to server using a connection URL
var mySession = mysqlx.getSession( {
  host: 'localhost', port: 33060,
  user: 'user', password: 'password'} )

var myDb = mySession.getSchema('test');

// Accessing an existing table
var myTable = myDb.getTable('my_table');

// Insert SQL Table data
myTable.insert(['name', 'birthday', 'age']).
  values('Laurie', mysqlx.dateValue(2000, 5, 27), 19).execute();

// Find a row in the SQL Table
var myResult = myTable.select(['_id', 'name', 'birthday']).
  where('name like :name AND age < :age').
  bind('name', 'L%').bind('age', 30).execute();

// Print result
print(myResult.fetchOne());

MySQL Shell Python 代码

# Working with Relational Tables
from mysqlsh import mysqlx

# Connect to server using a connection URL
mySession = mysqlx.get_session( {
  'host': 'localhost', 'port': 33060,
  'user': 'user', 'password': 'password'} )

myDb = mySession.get_schema('test')

# Accessing an existing table
myTable = myDb.get_table('my_table')

# Insert SQL Table data
myTable.insert(['name','birthday','age']) \
  .values('Laurie', mysqlx.date_value(2000, 5, 27), 19).execute()

# Find a row in the SQL Table
myResult = myTable.select(['_id', 'name', 'birthday']) \
  .where('name like :name AND age < :age') \
  .bind('name', 'L%') \
  .bind('age', 30).execute()

# Print result
print(myResult.fetch_all())

Node.js JavaScript 代码

// Working with Relational Tables
var mysqlx = require('@mysql/xdevapi');
var myTable;

// Connect to server using a connection URL
mysqlx
  .getSession({
    user: 'user',
    password: 'password',
    host: 'localhost',
    port: 33060
  })
  .then(function (session) {
    // Accessing an existing table
    myTable = session.getSchema('test').getTable('my_table');

    // Insert SQL Table data
    return myTable
      .insert(['name', 'birthday', 'age'])
      .values(['Laurie', '2000-5-27', 19])
      .execute()
  })
  .then(function () {
    // Find a row in the SQL Table
    return myTable
        .select(['_id', 'name', 'birthday'])
        .where('name like :name && age < :age)')
        .bind('name', 'L%')
        .bind('age', 30)
        .execute();
  })
  .then(function (myResult) {
    console.log(myResult.fetchAll());
  });

C# 代码

// Working with Relational Tables

// Connect to server using a connection
var db = MySQLX.GetSession(
  "server=localhost;port=33060;user=user;password=password")
.GetSchema("test");

// Accessing an existing table
var myTable = db.GetTable("my_table");

// Insert SQL Table data
myTable.Insert("name", "age")
.Values("Laurie", "19").Execute();

// Find a row in the SQL Table
var myResult = myTable.Select("_id, name, age")
.Where("name like :name AND age < :age")
.Bind(new { name = "L%", age = 30 }).Execute();

// Print result
PrintResult(myResult.FetchAll());

Python 代码

# Working with Relational Tables
import mysqlx

# Connect to server using a connection URL
my_session = mysqlx.get_session({
    'host': 'localhost', 'port': 33060,
    'user': 'user', 'password': 'password'
})

my_schema = my_session.get_schema('test')

# Accessing an existing table
my_table = my_schema.get_table('my_table')

# Insert SQL Table data
my_table.insert(['name', 'birthday', 'age']) \
    .values('Laurie', mysqlx.date_value(2000, 5, 27), 19).execute()

# Find a row in the SQL Table
result = my_table.select(['_id', 'name', 'birthday']) \
    .where('name like :name AND age < :age') \
    .bind('name', 'L%') \
    .bind('age', 30).execute()

# Print result
print(result.fetch_all())

Java 代码

// Working with Relational Tables
import com.mysql.cj.xdevapi.*;

// Connect to server using a connection URL
Session mySession = new SessionFactory().getSession("mysqlx://127.0.0.1:33060/test?user=user&password=password");
Schema db = mySession.getSchema("test");

// Accessing an existing table
Table myTable = db.getTable("my_table");

// Insert SQL Table data
myTable.insert("name", "birthday", "age").values("Laurie", "2000-05-27", 19).execute();

// Find a row in the SQL Table
RowResult myResult = myTable.select("_id, name, birthday")
     .where("name like :name AND age < :age")
     .bind("name", "L%").bind("age", 30).execute();

// Print result
myResult.forEach(r -> 
      System.out.println(r.getString(1) + ": " + r.getDate(2)));

C++ 代码

// Working with Relational Tables
#include <mysqlx/xdevapi.h>

// Connect to server using a connection URL
Session mySession(33060, "user", "password");

Schema myDb = mySession.getSchema("test");

// Accessing an existing table
Table myTable = myDb.getTable("my_table");

// Insert SQL Table data
myTable.insert("name", "birthday", "age")
       .values("Laurie", "2000-5-27", 19).execute();

// Find a row in the SQL Table
RowResult myResult = myTable.select("_id", "name", "birthday")
  .where("name like :name AND age < :age")
  .bind("name", "L%").bind("age", 30).execute();

// Print result
Row row = myResult.fetchOne();
cout << "     _id: " << row[0] << endl;
cout << "    name: " << row[1] << endl;
cout << "birthday: " << row[2] << endl;