MySQL in a Nutshell [64]
MBRContains(square_points, GeomFromText('POINT(1 2)')) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: squares
type: range
possible_keys: square_index
key: square_index
key_len: 32
ref: NULL
rows: 1
Extra: Using where
Notice that the SQL statement is using the square_index spatial index that we created.
Name
CREATE SCHEMA
Synopsis
CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] database [options]
This statement is synonymous with CREATE DATABASE. See the description of that statement earlier in this chapter for more information and examples.
Name
CREATE SERVER
Synopsis
CREATE SERVER server
FOREIGN DATA WRAPPER wrapper
OPTIONS (
{ DATABASE database, |
HOST host, |
USER user, |
PASSWORD password, |
SOCKET socket, |
OWNER character, |
PORT port }
)
Use this SQL statement with the FEDERATED storage engine to set the connection parameters. The values given are stored in the mysql database, in the server table, in a new row. The server name given cannot exceed 63 characters, and it’s not case-sensitive. The only wrapper permitted at this time is mysql. Options are given in a comma-separated list. You’re not required to specify all options listed in the example syntax. If an option is not given, the default will be an empty string. To change options after a server has been created, use the ALTER SERVER statement, described earlier in this chapter. For option values, character or numeric literals (UTF-8; maximum length of 64 characters) must be given. This statement was introduced in version 5.1.15 of MySQL and requires SUPER privileges.
The host may be a hostname or an IP address. The username and password given are those that are required for accessing the server. Provide either the name of the socket or the port to use for connecting to the server. The owner is the filesystem username to use for accessing the server:
CREATE SERVER server1
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'russell', HOST 'dyerhouse.com', DATABASE 'db1', PORT 3306,
OWNER 'root');
CREATE TABLE table1 (col1 INT)
ENGINE = FEDERATED CONNECTION='server1';
Name
CREATE TABLE
Synopsis
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table
{[(definition)][options]|[[AS] SELECT...]|[LIKE table]}
Use this statement to create a new table within a database. This statement has many clauses and options; however, when creating a basic table, you can omit most of them. The TEMPORARY keyword is used to create a temporary table that can be accessed only by the current connection thread and is not accessible by other users. The IF NOT EXISTS flag is used to suppress error messages caused by attempting to create a table by the same name as an existing one. After the table name is given, either the table definition is given (i.e., a list of columns and their data types) along with table options or properties, or a table can be created based on another table. The subsections that follow describe how to:
Set column properties regarding NULL and default values (see the CREATE TABLE: Column flags” subsection)
Create an index for a table based on one or more columns (see the CREATE TABLE: Index and key definitions” subsection)
Reference a foreign key constraint (see the CREATE TABLE: Foreign key references” subsection)
Specify various table options (see the CREATE TABLE: Table options” subsection)
Create a table exactly like another table (see the CREATE TABLE: Based on an existing table” subsection)
Create a table with filesystem partitions (see the three subsections on partitioning: CREATE TABLE: Partitioning,” CREATE TABLE: Partition definitions,” and CREATE TABLE: Subpartition definitions”)
Here is a simple example of how you can use the CREATE TABLE statement:
CREATE TABLE clients
(client_id INT AUTO_INCREMENT PRIMARY KEY,
client_name VARCHAR(75),
telephone CHAR(15));
This creates a table with three columns. The first column is called client_id and may contain integers. It will be incremented automatically as records are created. It will also be the primary