MySQL Primer

Updated 2,011 Days AgoPublic

Actions

Basic Ideas and Syntax

Identifiers

Identifier Names: Databases, tables, indexes, columns, aliases, views, stored routines, triggers, events, variables, partitions, tablespaces, savepoints, labels, users, roles, are collectively known as identifiers, and have certain rules for naming.

Identifiers may be quoted using the backtick character (`). Quoting is optional for identifiers that don't contain special characters, or is a reserved word. LibreOffice Base can be picky if you don't quote things, though.

Identifier Qualifiers: Qualifiers are used within SQL statements to reference data structures, such as databases, tables, or columns. For example, typically a [[ https://mariadb.com/kb/en/library/select/ | SELECT ]] query contains references to some columns and at least one table.

# This selects the CLIENTID and SOLD columns from the Prosp_EW table, which is in turn in the client database.
SELECT `CLIENTID`, `SOLD` FROM `client`.`Prosp_EW`;

Qualifiers can be composed by one or more identifiers, where the initial parts affect the context within which the final identifier is interpreted:

• For a database, only the database identifier needs to be specified.
• For objects which are contained in a database (like tables, views, functions, etc) the database identifier can be specified. If no database is specified, the current database is assumed.
• For column names, the table and the database are generally obvious from the context of the statement. It is however possible to specify the table identifier, or the database identifier plus the table identifier.

If a qualifier is composed by more than one identifier, a dot (.) must be used as a separator. All identifiers can be quoted individually. Extra spacing (including new lines and tabs) is allowed.

# This refers to the CLIENTID column in the Prosp_EW table, which is in turn in the client database.
`client`.`Prosp_EW`.`CLIENTID`

Literals (ie. Values)

Strings are sequences of characters and are enclosed with quotes ('). Strings can either be enclosed in single quotes or in double quotes (the same character must be used to both open and close the string). LibreOffice base seems to confuse itself sometimes if you use double-quotes, unfortunately.

Numeric literals are unquoted.

Date and time values can be represented in several formats, such as quoted strings or as numbers, depending on the exact type of the value and other factors. For example, in contexts where MySQL expects a date, it interprets any of '2015-07-21', '20150721', and 20150721 as a date.

# This selects all columns from entries in the Prosp_EW table where the date in the last_updated column is more recent that 2015-07-21 and the value in the CDNUM column is greater than 3
SELECT * FROM `client`.`Prosp_EW` WHERE `last_updated` > '2015-07-21' AND `CDNUM` > 3;