MySQL Syntax

There are some SQL constructs that MySQL supports and a few it doesn't support. during this section, we'll check out all the clauses that SQL allows. MySQL follows specific rules for naming variables and databases. we'll take a glance at those rules too.

MySQL allows the subsequent constructs:

USE
CREATE DATABASE
DROP DATABASE
ALTER DATABASE
CREATE TABLE
DROP TABLE
CREATE INDEX
DROP INDEX
ALTER TABLE
DESCRIBE
SHOW
SELECT
UNION
BEGIN
COMMIT
ROLLBACK
SET AUTOCOMMIT
DELETE
INSERT
LOAD DATA
REPLACE
UPDATE
FLUSH
GRANT
REVOKE
If we use the other SQL constructs like, for instance , INTERSECT, MySQL will throw a mistake .

Next, we'll check out the principles MYSQL follows for naming conventions.

  1. Unquoted names can contains any alphanumeric characters within the server's default list , plus the characters '_' and '.'
  2. Names can start with any character that is legal in a name, including a digit. However, a name cannot consist entirely of digits because that would make it indistinguishable from a number.
  3. As of MySQL 3.23.6, names are often quoted within backtick characters ('´'), allowing the utilization of any character except backtick, ASCII 0, and ASCII 255.
  4. We cannot use the '.' character because it's the separator between schema name and table name.
  5. We cannot use pathname separator characters ('/' or '\'). The separator characters are disallowed in database and table names because databases are represented on disk by directories, and tables are represented on disk by a minimum of one file. Consequently, these sorts of names must not contain characters that are illegal in directory names and filenames.
  6. Names for databases, tables, columns, and indexes are often up to 64 characters long. Alias names are often up to 256 characters long.
    Depending on the context, a reputation may have to be qualified to clarify what the term refers to. To ask a database, specify its name. One method is to use the ‘USE’ keyword.
USE <dbname>;

Select * from <table_name>

The second method is to use the fully qualified database name

Select * from <dbname>.<table_name>;

The same applies to column names as well.

  • MySQL is case-insensitive. So we can write the keywords, table names, and database names in the upper or lower or mixed case.