A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://www.w3resource.com/mysql/mysql-language-structure.php below:

Website Navigation


MySQL Language Structure - w3resource

This page discusses the syntactical and structural rules for writing the following elements of MySQL statements.

The terms literal refer to a fixed data value. MySQL evaluates seven types of literal values numeric, character string, date and time, hexadecimal, boolean, bit-field, and NULL Values

Numeric literal notation is used to specify fixed and floating-point numbers. Floating-point numbers use '.' as a decimal separator. Both types of numbers may be preceded by '+' or '-' to indicate a positive or negative numbers. You can use the integer notation in expressions, conditions, SQL functions, and SQL statements. The examples of integer is as follows :

A string is a sequence of bytes or characters, enclosed within a single quote ('w3resource') or double quote ("w3resource"). You can use string literal notation in expressions, conditions, SQL functions, and SQL statements. Here are some examples :

Note : If ANSI_QUOTES SQL mode is enabled, you cannot use double quotation marks to quote literal strings, because it is interpreted as an identifier.

A binary string is a string of bytes that has no character set or collation whereas a nonbinary string is a string of characters that has a character set and collation. For both types of strings, comparisons are based on the numeric values of the string unit.

Within a string, certain sequences have special meaning unless the NO_BACKSLASH_ESCAPES SQL mode (Disable the use of the backslash character (“\”) as an escape character within strings) is enabled. Each of these sequences begins with a backslash ("\"), known as the escape character. MySQL recognizes the escape sequences shown in the following table.

Escape Sequence Character represented by Sequence \0 An ASCII NUL (0x00) character. \' A single quote ("'") character. \" A double quote (“"”) character. \b A backspace character. \n A newline (linefeed) character. \r A carriage return character. \t A tab character. \Z ASCII 26 (Control+Z). See note following the table. \\ A backslash (“\”) character. \% A "%" character. See note following the table. \_ A "_" character. See note following the table.

Here are some examples :

MySQL> SELECT 'w3r', '"w3r"', "'w3r'", '""w3r""', 'w3''resource', '\'w3r', '\"w3r';
+-----+-------+-------+---------+-------------+------+------+
| w3r | "w3r" | 'w3r' | ""w3r"" | w3'resource | 'w3r | "w3r |
+-----+-------+-------+---------+-------------+------+------+
| w3r | "w3r" | 'w3r' | ""w3r"" | w3'resource | 'w3r | "w3r |
+-----+-------+-------+---------+-------------+------+------+
1 row in set (0.00 sec)

MySQL> SELECT 'The\nQuick\nBrown\nFox';
+---------------------+
| The
Quick
Brown
Fox |
+---------------------+
| The
Quick
Brown
Fox |
+---------------------+
1 row in set (0.00 sec)

Date Time Literals

You can specify a date and time values in several formates, such as numbers or as quoted strings, depending on the exact type of the value and other factors. MySQL interprets '2017-08-22', '20170822', and 20170822 as a date.

MySQL recognizes DATE values in these formats :

MySQL recognizes DATETIME and TIMESTAMP values in these formats :

MySQL recognizes TIME values in these formats :

Hexadecimal Literals

MySQL supports hexadecimal values, written using X'val', x'val', or 0xval format, where val contains hexadecimal digits (0..9, A..F). Lettercase of the digits does not matter. Here are some examples :

MySQL> SELECT X'773372736F75726365';
+-----------------------+
| X'773372736F75726365' |
+-----------------------+
| w3rsource             |
+-----------------------+
1 row in set (0.01 sec)

MySQL> SELECT 0xC8+0;
+--------+
| 0xC8+0 |
+--------+
|    200 |
+--------+
1 row in set (0.05 sec)

MySQL>SELECT 0x555341;
+----------+
| 0x555341 |
+----------+
| USA      |
+----------+
1 row in set (0.00 sec)

Boolean Literals

The constants TRUE and FALSE evaluate to 1 and 0, respectively. The constant names can be written both upper and lower case letters. See the following examples :

MySQL> SELECT TRUE, true;
+------+------+
| TRUE | TRUE |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

MySQL> SELECT FALSE, false;
+-------+-------+
| FALSE | FALSE |
+-------+-------+
|     0 |     0 |
+-------+-------+
1 row in set (0.00 sec)

Bit-Field Literals

Bit-field values can be written using b'value' or 0bvalue notation. value is a binary value written using zeros and ones.

NULL Values

The NULL value means “no data.” NULL can be written in any lettercase.

Schema Object Names

A schema is a collection of logical structures of data or schema objects. A schema is owned by a database user. In MySQL, some objects including database, table, column, alias, view, stored procedure, partition, tablespace, and other object names are known as identifiers. An identifier may be quoted or unquoted. If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it.
Identifiers are converted to Unicode internally. They may contain these characters :

The following table shows the maximum length for each type of identifier

Identifier Maximum Length
(characters) Database 64 Table 64 Column 64 Index 64 Constraint 64 Stored Procedure or Function 64 Trigger 64 View 64 Event 64 Tablespace 64 Server 64 Log File Group 64 Alias 256 (see exception) Compound Statement Label 16

Identifier Qualifiers

MySQL allows names that consist of a single identifier or multiple identifiers. The components of a multiple-part name must be separated by period (".") characters. The initial parts of a multiple-part name act as qualifiers that affect the context within which the final identifier is interpreted.

In MySQL, you can refer to a table column using any of the following forms.

Column Reference Meaning col_name The column col_name from whichever table used in the statement contains a column of that name. tbl_name.col_name The column col_name from table tbl_name of the default database. db_name.tbl_name.col_name The column col_name from table tbl_name of the database db_name.

The qualifier character is a separate token and needs not be contiguous with the associated identifiers. For example, tbl_name.col_name and tbl_name . col_name are equivalent.

Identifier Case Sensitivity

In MySQL database, table, and trigger names are not case sensitive in Windows, but are case sensitive in most varieties of Unix. In Mac OS X, which is Unix-based but uses a default file system type (HFS+) that is not case sensitive. Column, index, stored routine, and event names are not case sensitive on any platform, nor are column aliases, but logfile groups are case sensitive. This differs from standard SQL. By default, table aliases are case sensitive on Unix, but not so on Windows or Mac OS X.

Mapping of Identifiers to File Names

There is a correspondence between database and table identifiers and names in the file system. MySQL represents each database as a directory in the data directory, and each table by one or more files in the appropriate database directory. For the table format files (.FRM), the data is always stored in this structure and location.

For the data and index files, the exact representation on disk is storage engine specific. These files may be stored in the same location as the FRM files, or the information may be stored in a separate file. InnoDB data is stored in the InnoDB data files. If you are using tablespaces with InnoDB, then the specific tablespace files you create are used instead.

Any character is legal in database or table identifiers except ASCII NUL (0x00). MySQL encodes any characters that are problematic in the corresponding file system objects when it creates database directories or table files:

Mapping of Identifiers to File Names

MySQL 5.6 supports built-in (native) functions, user-defined functions (UDFs), and stored functions.

Built-In Function Name Parsing:

The parser uses default rules for parsing names of built-in functions. These rules can be changed by enabling the IGNORE_SPACE SQL mode. When the parser encounters a word that is the name of a built-in function, it must determine whether the name signifies a function call or is instead a nonexpression reference to an identifier such as a table or column name

Function Name Resolution : The following rules describe how the server resolves references to function names for function creation and invocation :

MySQL: User-Defined Variables

In MySQL, you can store a value in a user-defined variable in one statement and later you can refer in another statement. This enables you to pass the values from one statement to another. User-defined variables are session-specific, therefore these variables are private to a particular user and another user can not see or use these. All variables for a given client session are automatically freed when that client exits.

Syntax : @var_name
var_name consists of alphanumeric characters, ".", "_", and "$". A user variable name can contain other characters if you quote it as a string or identifier (for example, @'my-var', @"my-var", or @`my-var`).
Note : User variable names are not case sensitive in MySQL 5.0 and up.

One way to set a user-defined variable is by issuing a SET statement :

SET @var_name = expr [, @var_name = expr] ...

Example :

MySQL>  SET @x = 10; @y = 20; @z: = 30;
Query OK, 0 rows affected (0.07 sec)

MySQL> SELECT @x, @y, @z;
+------+------+------+
| @x   | @y   | @z   |
+------+------+------+
|   10 |   20 |   30 |
+------+------+------+
1 row in set (0.00 sec) 

For more details read variables in Stored Programs.

MySQL: Expression Syntax

The following rules define expression syntax in MySQL. The grammar shown here is based on that given in the sql/sql_yacc.yy file of MySQL source distributions.

expr :

boolean_primary :

comparison_operator :

= | >= | > | <= | < | <> | !=

predicate :

gbit_expr :

simple_expr :

MySQL Comment Syntax

MySQL supports three comment styles :

See the following examples :

MySQL> SELECT 1+1; # Single line comment

MySQL> SELECT 1+1; -- Single line comment

MySQL>  SELECT 1 /* In-line comment*/ + 1;

MySQL>  SELECT 1+ 
     /*
     this is a 
     multiple-line comment
     */
     1;

MySQL Reserved Words

Certain words such as SELECT, DELETE, or BIGINT are reserved and require special treatment for use as identifiers such as table and column names. The following table lists MySQL reserved wordS.

Reserved Words in MySQL 5.6

ACCESSIBLE ADD ALL ALTER ANALYZE AND AS ASC ASENSITIVE BEFORE BETWEEN BIGINT BINARY BLOB BOTH BY CALL CASCADE CASE CHANGE CHAR CHARACTER CHECK COLLATE COLUMN CONDITION CONSTRAINT CONTINUE CONVERT CREATE CROSS CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR DATABASE DATABASES DAY_HOUR DAY_MICROSECOND DAY_MINUTE DAY_SECOND DEC DECIMAL DECLARE DEFAULT DELAYED DELETE DESC DESCRIBE DETERMINISTIC DISTINCT DISTINCTROW DIV DOUBLE DROP DUAL EACH ELSE ELSEIF ENCLOSED ESCAPED EXISTS EXIT EXPLAIN FALSE FETCH FLOAT FLOAT4 FLOAT8 FOR FORCE FOREIGN FROM FULLTEXT GENERAL GET GRANT GROUP HAVING HIGH_PRIORITY HOUR_MICROSECOND HOUR_MINUTE HOUR_SECOND IF IGNORE IGNORE_SERVER_IDS IN INDEX INFILE INNER INOUT INSENSITIVE INSERT INT INT1 INT2 INT3 INT4 INT8 INTEGER INTERVAL INTO IO_AFTER_GTIDS IO_BEFORE_GTIDS IS ITERATE JOIN KEY KEYS KILL LEADING LEAVE LEFT LIKE LIMIT LINEAR LINES LOAD LOCALTIME LOCALTIMESTAMP LOCK LONG LONGBLOB LONGTEXT LOOP LOW_PRIORITY MASTER_BIND MASTER_HEARTBEAT_PERIOD MASTER_SSL_VERIFY_SERVER_CERT MATCH MAXVALUE MEDIUMBLOB MEDIUMINT MEDIUMTEXT MIDDLEINT MINUTE_MICROSECOND MINUTE_SECOND MOD MODIFIES NATURAL NOT NO_WRITE_TO_BINLOG NULL NUMERIC ON ONE_SHOT OPTIMIZE OPTION OPTIONALLY OR ORDER OUT OUTER OUTFILE PARTITION PRECISION PRIMARY PROCEDURE PURGE RANGE READ READS READ_WRITE REAL REFERENCES REGEXP RELEASE RENAME REPEAT REPLACE REQUIRE RESIGNAL RESTRICT RETURN REVOKE RIGHT RLIKE SCHEMA SCHEMAS SECOND_MICROSECOND SELECT SENSITIVE SEPARATOR SET SHOW SIGNAL SLOW SMALLINT SPATIAL SPECIFIC SQL SQLEXCEPTION SQLSTATE SQLWARNING SQL_AFTER_GTIDS SQL_BEFORE_GTIDS SQL_BIG_RESULT SQL_CALC_FOUND_ROWS SQL_SMALL_RESULT SSL STARTING STRAIGHT_JOIN TABLE TERMINATED THEN TINYBLOB TINYINT TINYTEXT TO TRAILING TRIGGER TRUE UNDO UNION UNIQUE UNLOCK UNSIGNED UPDATE USAGE USE USING UTC_DATE UTC_TIME UTC_TIMESTAMP VALUES VARBINARY VARCHAR VARCHARACTER VARYING WHEN WHERE WHILE WITH WRITE XOR YEAR_MONTH ZEROFILL    

Previous: MySQL Storage Engines (table types)
Next: MySQL Data Types


RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4