MariaDB Tutorial

MariaDB Tutorial MariaDB Drop Database MariaDB Alter Database MariaDB Create a Database MariaDB Aggregate Functions MariaDB Group By Clause MariaDB Drop And Truncate Table MariaDB Like Clause MariaDB Order By Clause MariaDB Intersect Operator MariaDB Having Clause MariaDB Union MariaDB String Functions MariaDB Numieric Funcitons Difference Between MariaDB & MySQL

MariaDB-ALTER DATABASE
2021-06-25 15:09:33

MariaDB-ALTER DATABASE

MariaDB-ALTER DATABASE

And

SHOW DATABASES

To modify the database and change the database's comprehensive characteristics, we can use ALTER DATABASE command. We can alter the database characteristics that are saved in the db.opt file. To perform this operation, we need to employ ALTER DATABASE. This command is equivalent to ALTER SCHEMA. To perform this operation to alter the database, we need ALTER privilege.

Syntax:

ALTER {DATABASE | SCHEMA} [database_name] alter_specification..;
 Alter_specification:[DEFAULT CHARACTER SER [=] charset_name | COLLATE [=] Collation_name | COMMENT [=] ‘comment’;
 ALTER SCHEMA is equivalent to the ALTER DATABASE statement.
 ALTER and DATABASE are the keywords.
  • We can exclude the database name from the syntax; in that case, MariaDB will apply the command to the default database.
  • The CHARACTER SET clause is used to modify the default database character set.
  • The COLLATE clause enables us to change the default database collation.
  • We can view the accessible character sets and collation using SHOW CHARACTER and SHOW COLLATION.

For example:

 MariaDB [(none)]> SHOW DATABASES;
 +--------------------+
 | Database           |
 +--------------------+
 | department         |
 | information_schema |
 | mysql              |
 | performance_schema |
 | student            |
 | test               |
 | university         |
 +--------------------+
 7 rows in set (0.001 sec)
 MariaDB [(none)]> ALTER DATABASE student CHARACTER SET = 'utf8' COLLATE = 'utf8_bin';
 Query OK, 1 row affected (0.001 sec)
MariaDB-ALTER DATABASE

Here we can see the list databases using SHOW DATABASES, and we are altering the student database.

Example 2:

 MariaDB [(none)]> SHOW DATABASES;
 +--------------------+
 | Database           |
 +--------------------+
 | department         |
 | information_schema |
 | mysql              |
 | performance_schema |
 | student            |
 | test               |
 | university         |
 +--------------------+
 7 rows in set (0.001 sec)
 MariaDB [(none)]> ALTER DATABASE test CHARACTER SET = 'ucs2' COLLATE = 'ucs2_bin';
 Query OK, 1 row affected (0.001 sec)
MariaDB-ALTER DATABASE

Here we have modified test database, Character set is changed to “ucs2” and Collate to “ucs2_bin”

MariaDB-ALTER DATABASE

Above we are using SHOW CHARACTER SET command which returns the available character sets. The query returns in all 40 rows available.

MariaDB-ALTER DATABASE

 Here we have used SHOW COLLATION command in MariaDB which returns available collations. Here the query returns total 322 rows in the set.

ALTER DIRECTORY NAME

Syntax:

ALTER DATABASE | SCHEMA database_name UPGRADE DATA DIRECTORY NAME;

The clause 'UPGRADE DATA DIRECTORY NAME' is the new feature that was included in MySQL 5.1.23.

  • It allows us to modify the name of the directory related to the database to use the encoding implemented in MySQL 5.1 for mapping the names of the database to directory names. We can use this command in the MySQL 5.1 or later version of

MySQL 5.1.

  • We can use this command when the database name consists of any special characters to modify it to the present encoding format.

For instance, if a database is named m-a-e, the name has a '-’ instance character. In the earlier version of MySQL, i.e., before MySQL 5.1, the database directory name would be m-a-e which is not secure for the systems. In the later versions of MySQL, i.e., MySQL 5.1 and onwards, the name would be encoded as m@002da@002de so that all names are in identical format.

            When we upgrade the server to MySQL 5.1, the server shows the directory name m-a-e as #mysql#m-a-e. To refer to the name, we have to use the #mysql50# prefix. If we want to reencode the database directory name externally, we can ask the server using the upgrade data directory name to present encoding in the following way:

ALTER DATABASE ‘#mysql50m-a-e’ UPGRADE DATA DIRECTORY NAME;

We can refer to the name as m-a-e without using the defined prefix subsequently executing the above statement.

MariaDB-SHOW DATABASES

To see all the stored databases in the MariaDB server host, we use SHOW DATABASES. This command is equivalent to SHOW SCHEMAS. We want a particular database; we can mention it in the LIKE clause. To employ this command, we need SHOW Privilege.

Syntax:

SHOW {DATABASES | SCHEMAS} [LIKE ‘pattern’ | WHERE expr];

Example 1:

  MariaDB [(none)]> SHOW DATABASES;
 +--------------------+
 | Database           |
 +--------------------+
 | department         |
 | information_schema |
 | mysql              |
 | performance_schema |
 | student            |
 | test               |
 | university         |
 +--------------------+
 7 rows in set (0.001 sec)   

Here the query returns the list of all databases.

MariaDB-ALTER DATABASE

Example 2:

 MariaDB [(none)]> SHOW DATABASES LIKE 's%';
 +---------------+
 | Database (s%) |
 +---------------+
 | student       |
 +---------------+
 1 row in set (0.040 sec)

In this example we have only shown the databases those names start with s.

MariaDB-ALTER DATABASE