In this MariaDB tutorial, we will look at various MariaDB Reserved Words and also look at several examples. There are lists of the topic that comes under discussion:
- MariaDB Reserved Words
- MariaDB Escape Reserved Words
- MariaDB 10 Reserved Words
- MariaDB Reserved Words Exception
MariaDB Reserved Words
In this section, we will know about reserved words and their examples.
In MariaDB, the implementation of SQL is made up of keywords that are used in performing SQL operations. We have to take care that these keywords should not be used while naming databases, tables, columns, or any other database objects. Thus keywords are considered reserved. This is the list of all of MariaDB’s reserved words from the official website.
In MariaDB, we can’t use reserved words during the creation of a table or any other database objects. Here is one example to use the reserved words by the following query:
RESERVED WORDS EXAMPLE:
CREATE TABLE ADD( name VARCHAR(20), Date_value DATE);
In the query, we tried to use the reserved words as ADD in the table_name by using the CREATE TABLE statement. This leads to an error because ADD function is normally used to add two numeric values.
So, reserved words in MariaDB can be utilized only in their specific use case. To understand this, let’s take a look at an example.
The example of the MariaDB reserved word using the IN keyword in the following query:
SELECT * FROM LIFE WHERE LIFE_EXPECTATION IN ('GOOD','BAD');
As we see in the above query, we have used the IN keyword in the LIFE table with the help of the SELECT statement on the LIFE_EXPECTATION column. So, as the result, it will check whether these values exist in the LIFE_EXPECTATION column or not and bring the result based on that.
Read: MariaDB ENUM – Helpful Guide
MariaDB Escape Reserved Words
In this section, we will learn how to use the escape reserved words and it is explained with an example.
In MariaDB, the Backticks (‘) is used to escape reserved terms in DDL statements. Enclose reserved keywords in double quotations (“) in SQL CREATE TABLE statements and queries on views to escape them.
First, let’s see the EMPLOYEE table by the following query:
CREATE TABLE IF NOT EXISTS music_info( id INTEGER AUTO_INCREMENT PRIMARY KEY NOT NULL, `key_1` TEXT UNIQUE NOT NULL, id_value TEXT NOT NULL);
In the first query, we have used with CREATE TABLE statement with the IF NOT EXISTS clause to create a table called music_info. In the column called key_1, we have used the escape reserved words with the data type as TEXT.
Read: MariaDB vs Postgres – Detailed Comparison
MariaDB 10 reserved words
In this sub-topic, we will know about the top 10 reserved words and which are explained with the help of an example.
Here are the top 10 reserved words as the keywords in the MariaDB are given below:
|BETWEEN||It is used as the condition in the DDL statement to get the output in the query.|
|ASC||It is used to arrange in ascending order and it is used as an ASC keyword after the ORDER BY clause.|
|ADD||It is used as the function to add two or more values in numeric. And it is also used in the ALTER TABLE statement to add a new column with the data type.|
|AS||It is called an alias name of the column or function used for the temporary session.|
|PRIMARY||It is a key constraint used only once in the table and only one column can contain the primary key value as an integer or string with no NULL value or an empty string in it.|
|OR||It is a condition-based clause used after the WHERE condition and it is used to choose between condition_1 or condition_2.|
|DESC||It is used to arrange the column or records in descending order. It is used after the ORDER BY clause with the DESC keyword.|
|ELSEIF||It is used in the function or the CASE statement for the condition if it gets a true or false base and returns the value as numeric or string in the output.|
|RANDOM||It is a function to bring a random value or string as the result set in the query.|
Read: MariaDB If Null + Examples
MariaDB Reserved Words Exception
In this section, we will talk about historic exceptions of the reserved words in the MariaDB and it is explained with the help of an example.
Some keywords in MariaDB are exceptions for historical reasons and are allowed as unquoted identifiers. These are some of them:
|Exception Reserved Words||DEFINITION|
|BIT||It is a bit-field type and it contains values from 1 to 64. The default value is 1 if M is omitted.|
|DATE||It is a date and displays the date in YYYY-MM-DD format. It contains dates in loose assigned values including strings or numbers.|
|ENUM||It is an enumeration and it chooses a value from the list of values. The ENUM column still can contain 65,535 distinct values.|
|TEXT||A text column can contain a maximum value of 65,535 characters. If we want to store the bigger text we use the MEDIUM TEXT data type.|
|TIME||In MariaDB, we can store time value in HH:MM: SSSS|
|TIMESTAMP||When a record is inserted or changed, the timestamp field is automatically assigned the current DateTime. The timestamp format is YYYY-MM-DD HH:MM:SS: ffff|
In MariaDB, the ENUM datatype chooses the value from the list of values in the parameters of the column. The sample example of the MariaDB Reserved Words Exception as ENUM keywords by the following query:
CREATE TABLE LIFE( NAME VARCHAR(20), LIFE_EXPECTATION ENUM('GOOD','BAD','BETTER')); INSERT INTO LIFE (NAME,LIFE_EXPECTATION) VALUES('JOHN SMITH','GOOD'), ('MR. ANDRESON','BAD'); SELECT * FROM LIFE;
In the first query, we have created a table called LIFE under which we have used the ENUM data type on the LIFE_EXPECTATION column as GOOD, BAD, and BETTER as the value list. After that, we have inserted new records in the LIFE table and chosen the ENUM value as the value list for each row by using the INSERT INTO statement.
In the last query, we have retrieved all records from the LIFE table by using the SELECT statement.
You may also like to read the following MariaDB tutorials.
- What is MariaDB Sharding
- MariaDB Add Column With Default Value
- How to load files into MariaDB
- MariaDB Foreign Key + Examples
- MariaDB Reset Root Password
- MariaDB Between + Examples
- MariaDB Temporary Table + Examples
- MariaDB Truncate Table + Examples
In this MariaDB tutorial, we have discussed the MariaDB reserved words and also discusses some sample examples related to it. There are lists of the topic that comes under discussion:
- MariaDB reserved words
- MariaDB escape reserved words
- MariaDB 10 reserved words
- MariaDB Reserved Words Exception
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.