MySQL Shell  8.0.16
Unified development interface for MySQL Products
Methods | List of all members
TableSelect Class Reference

Handler for record selection on a Table. More...

Methods

TableSelect select (List searchExprStr)
 Defines the columns to be retrieved from the table. More...
 
TableSelect where (String expression)
 Sets the search condition to filter the records to be retrieved from the Table. More...
 
TableSelect groupBy (List searchExprStr)
 Sets a grouping criteria for the retrieved rows. More...
 
TableSelect having (String condition)
 Sets a condition for records to be considered in agregate function operations. More...
 
TableSelect orderBy (List sortExprStr)
 Sets the order in which the records will be retrieved. More...
 
TableSelect limit (Integer numberOfRows)
 Sets the maximum number of rows to be returned on the select operation. More...
 
TableSelect offset (Integer numberOfRows)
 Sets number of rows to skip on the resultset when a limit has been defined. More...
 
TableSelect bind (String name, Value value)
 Binds a value to a specific placeholder used on this operation. More...
 
RowResult execute ()
 Executes the select operation with all the configured options. More...
 
TableSelect lockShared (String lockContention)
 
TableSelect lockExclusive (String lockContention)
 

Detailed Description

Handler for record selection on a Table.

This object provides the necessary functions to allow selecting record data from a table.

This object should only be created by calling the select function on the table object from which the record data will be retrieved.

See also
Table

Member Function Documentation

◆ select()

TableSelect select ( List  searchExprStr)

Defines the columns to be retrieved from the table.

Returns
This TableSelect object.

Defines the columns that will be retrieved from the Table.

To define the column list either use a list object containing the column definition or pass each column definition on a separate parameter

If the function is called without specifying any column definition, all the columns in the table will be retrieved.

Method Chaining

After this function invocation, the following functions can be invoked:

See also
Usage examples at execute().

◆ where()

TableSelect where ( String  expression)

Sets the search condition to filter the records to be retrieved from the Table.

Parameters
expressionOptional condition to filter the records to be retrieved.
Returns
This TableSelect object.

If used, only those rows satisfying the expression will be retrieved

The searchCondition supports Parameter Binding.

Method Chaining

This function can be invoked only once after:

After this function invocation, the following functions can be invoked:

See also
Usage examples at execute().

◆ groupBy()

TableSelect groupBy ( List  searchExprStr)

Sets a grouping criteria for the retrieved rows.

Returns
This TableSelect object.

Method Chaining

This function can be invoked only once after:

After this function invocation the following functions can be invoked:

See also
Usage examples at execute().

◆ having()

TableSelect having ( String  condition)

Sets a condition for records to be considered in agregate function operations.

Parameters
conditionA condition to be used with agregate functions.
Returns
This TableSelect object.

If used the TableSelect operation will only consider the records matching the established criteria.

The searchCondition supports Parameter Binding.

Method Chaining

This function can be invoked only once after:

After this function invocation, the following functions can be invoked:

See also
Usage examples at execute().

◆ orderBy()

TableSelect orderBy ( List  sortExprStr)

Sets the order in which the records will be retrieved.

Returns
This TableSelect object.

If used the records will be sorted with the defined criteria.

The elements of sortExprStr list are strings defining the column name on which the sorting will be based.

The format is as follows: columnIdentifier [ ASC | DESC ]

If no order criteria is specified, ascending will be used by default.

Method Chaining

This function can be invoked only once after:

After this function invocation, the following functions can be invoked:

See also
Usage examples at execute().

◆ limit()

TableSelect limit ( Integer  numberOfRows)

Sets the maximum number of rows to be returned on the select operation.

Parameters
numberOfRowsThe maximum number of rows to be retrieved.
Returns
This TableSelect object.

If used, the operation will return at most numberOfRows rows.

Method Chaining

This function can be invoked only once after:

This function can be called every time the statement is executed.

After this function invocation, the following functions can be invoked:

See also
Usage examples at execute().

◆ offset()

TableSelect offset ( Integer  numberOfRows)

Sets number of rows to skip on the resultset when a limit has been defined.

Parameters
numberOfRowsThe number of rows to skip before start including them on the RowResult.
Returns
This CollectionFind object.

If used, the first numberOfRows records will not be included on the result.

Method Chaining

This function can be invoked only once after:

After this function invocation, the following functions can be invoked:

See also
Usage examples at execute().

◆ lockShared()

TableSelect lockShared ( String  lockContention)

Method Chaining

This function can be invoked at any time before bind or execute are called.

After this function invocation, the following functions can be invoked:

If lockExclusive() is called, it will override the lock type to be used on on the selected documents.

◆ lockExclusive()

TableSelect lockExclusive ( String  lockContention)

Method Chaining

This function can be invoked at any time before bind or execute are called.

After this function invocation, the following functions can be invoked:

If lockShared() is called, it will override the lock type to be used on on the selected documents.

◆ bind()

TableSelect bind ( String  name,
Value  value 
)

Binds a value to a specific placeholder used on this operation.

Parameters
nameThe name of the placeholder to which the value will be bound.
valueThe value to be bound on the placeholder.
Returns
This TableSelect object.

An error will be raised if the placeholder indicated by name does not exist.

This function must be called once for each used placeholder or an error will be raised when the execute method is called.

Method Chaining

This function can be invoked multiple times right before calling execute:

After this function invocation, the following functions can be invoked:

An error will be raised if the placeholder indicated by name does not exist.

This function must be called once for each used placeholder or an error will be raised when the execute method is called.

See also
Usage examples at execute().

◆ execute()

RowResult execute ( )

Executes the select operation with all the configured options.

Returns
A RowResult object that can be used to traverse the rows returned by this operation.

Method Chaining

This function can be invoked after any other function on this class.

Examples

//@ Table.Select All
var records = table.select().execute().fetchAll();
print("All:", records.length, "\n");
//@ Table.Select Filtering
var records = table.select().where('gender = "male"').execute().fetchAll();
print("Males:", records.length, "\n");
var records = table.select().where('gender = "female"').execute().fetchAll();
print("Females:", records.length, "\n");
var records = table.select().where('age = 13').execute().fetchAll();
print("13 Years:", records.length, "\n");
var records = table.select().where('age = 14').execute().fetchAll();
print("14 Years:", records.length, "\n");
var records = table.select().where('age < 17').execute().fetchAll();
print("Under 17:", records.length, "\n");
var records = table.select().where('name like "a%"').execute().fetchAll();
print("Names With A:", records.length, "\n");
var records = table.select().where('name LIKE "a%"').execute().fetchAll();
print("Names With A:", records.length, "\n");
var records = table.select().where('NOT (age = 14)').execute().fetchAll();
print("Not 14 Years:", records.length, "\n");
//@ Table.Select Field Selection
var result = table.select(['name', 'age']).execute();
var record = result.fetchOne();
var columns = dir(record)
print(columns)
print('1-Metadata Length:', columns.length, '\n');
print('1-Metadata Field:', columns[1], '\n');
print('1-Metadata Field:', columns[2], '\n');
var result = table.select(['age']).execute();
var record = result.fetchOne();
var columns = dir(record)
print('2-Metadata Length:', columns.length, '\n');
print('2-Metadata Field:', columns[1], '\n');
//@ Table.Select Sorting
var records = table.select().orderBy(['name']).execute().fetchAll();
for (index = 0; index < 7; index++) {
print('Select Asc', index, ':', records[index].name, '\n');
}
var records = table.select().orderBy(['name desc']).execute().fetchAll();
for (index = 0; index < 7; index++) {
print('Select Desc', index, ':', records[index].name, '\n');
}
//@ Table.Select Limit and Offset
var records = table.select().limit(4).execute().fetchAll();
print('Limit-Offset 0 :', records.length, '\n');
for (index = 1; index < 8; index++) {
var records = table.select().limit(4).offset(index).execute().fetchAll();
print('Limit-Offset', index, ':', records.length, '\n');
}
//@ Table.Select Parameter Binding through a View
var view = schema.getTable('view1');
var records = view.select().where('my_age = :years and my_gender = :heorshe').bind('years', 13).bind('heorshe', 'female').execute().fetchAll();
print('Select Binding Length:', records.length, '\n');
print('Select Binding Name:', records[0].my_name, '\n');