Shulou(Shulou.com)06/01 Report--

Create a new MySQL database create a MySQL database with a specific character set (UTF8) Delete an existing MySQL database create a MySQL database only if it does not exist will change the db.opt database property upgrade data catalog option for migration and encoding only if the MySQL database exists

1. Create a new MySQL database

To create an MariaDB database, use the create database command, as shown below.

The following will create a database named "study".

MariaDB [(none)] > CREATE DATABASE study;Query OK, 1 row affected (0.00 sec)

If you have background information on the Oracle database, please do not confuse the term "database" here.

When we create a "database" in MySQL, we are actually creating a "schema". But in the MySQL and MariaDB world, it is really called "database" rather than "schema".

However, for some reason, you can also use the following "create schema" command to create a database if you prefer. Creating a schema is just a synonym for creating a database, and the following command is exactly the same as the create database above.

MariaDB [(none)] > CREATE SCHEMA study

Note that only users with database CREATE privileges can execute the above command.

In a typical case, you will log in to mysql as root and execute the above create database command.

# mysql-u root-pMySecretPWD

CREATE DATABASE creates a database with the given name. To use this statement, you need CREATE permissions for the database. CREATE SCHEMA is synonymous with CREATE DATABASE. If you are new to MySQL, this will explain how to install MySQL MariaDB on Linux.

two。 Create a MySQL database with a specific character set (UTF8)

When creating a new database, creating the database will use any default character set in the system, but if you know exactly which character set you want, you can specify them during database creation, as shown below. In the following example, we are using the "utf8" character set to create a mariadb database named "boys". Here we also specify collation and character sets.

MariaDB [(none)] > CREATE DATABASE boys CHARACTER SET = utf8 COLLATE = utf8_general_ci;Query OK, 1 row affected (0.00 sec)

If you want to view all available character sets on your system, use the following show character set command.

MariaDB [(none)] > SHOW CHARACTER SET +-+ | Charset | Description | Default collation | Maxlen | +-+- -+ | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | hp8 | HP West European | | hp8_english_ci | 1 | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | latin1 | cp1252 West European | latin1_swedish_ci | 1 | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |.... | utf8 | UTF-8 Unicode | utf8_general_ci | 3 |

When you specify a character set in a database, this information is stored in the db.opt file for that particular database.

For example, for the "boys" database, the db.opt file will be located in the / var / lib / mysql / boys directory, as shown below.

# cat / var/lib/mysql/boys/db.opt default-character-set=utf8default-collation=utf8_general_ci

After you create the database, you can create tables in the MySQL database.

3. Delete an existing MySQL database

To remove an existing mysql database from the system, use the drop database command, as shown below.

The "study" database will be deleted below.

MariaDB [(none)] > DROP DATABASE study

Please remember:

This is a dangerous command because it deletes all tables and data in the database, and then deletes the database itself. To execute this command, you will need DROP privileges on the database. In addition, similar to creating databases and schemas, you can also use drop database and drop schema.

The following two commands are exactly the same.

MariaDB [(none)] > DROP DATABASE study;MariaDB [(none)] > DROP SCHEMA study

After you delete the database, execute the show database to ensure that the database is no longer listed.

MariaDB [(none)] > SHOW DATABASES;+-+ | Database | +-+ | information_schema | | mysql | | performance_schema | +-+

When you delete a database, the database directory and all files containing db.opt are also deleted from the / var / lib / mysql folder, as shown below.

# ls-l / var/lib/mysql/study/ls: cannot access / var/lib/mysql/study/: No such file or directory

One thing to remember is that if you have manually created some files under the database directory (that is, / var / lib / mysql / study), the above drop database command will not delete your custom files or the database directory itself. However, it deletes all other files created by the mysql server itself, including table files, and so on. In the related comments, if you create any TEMPORARY tables, they are not deleted. However, when the specific session created ends, they are automatically deleted.

4. If it does not exist, create a MySQL database

By default, as shown below, the create database command fails when you try to create an existing database.

MariaDB [(none)] > CREATE DATABASE study;ERROR 1007 (HY000): Can't create database 'study'; database exists

If you just do this on the command line, it might be good, because you can imply that you ignore and move on. However, if you do this in a script that checks for any error messages from the SQL command and there is a script, then we have a problem.

In this case, use the "if not exists" clause and create database, as shown below.

MariaDB [(none)] > CREATE DATABASE IF NOT EXISTS study;Query OK, 1 row affected, 1 warning (0.00 sec)

This will create the database only if it does not exist. However, when the database exists, it does not throw any error messages.

5. If it exists, discard the MySQL database

DROP DATABASE IF EXISTS study; / / if it exists, discard the MySQL database DROP SCHEMA IF EXISTS study; / / if it exists, discard the MySQL database

When you try to delete a database that does not exist, you will receive the following error message.

MariaDB [none] > DROP DATABASE study;ERROR 1008 (HY000): Can't drop database 'study'; database doesn't exist

This can happen when you operate interactively from the command line. However, if you execute the drop command from an automated script and you don't see an error message, you might stop the script and then use the IF EXISTS clause, as shown below.

MariaDB [none] > DROP DATABASE IF EXISTS study;Query OK, 0 rows affected, 1 warning (0.00 sec)

The same behavior as the above command, but does not return any error messages.

6. Change the database properties of db.opt

Using the alter database command, you can change some properties of the database. Typically, you will use it to change the database options set in the db.opt file. For example, here we define the character set of the study database in db.opt as LATIN

# cat / var/lib/mysql/study/db.opt default-character-set=latin1default-collation=latin1_swedish_ci..

Now, to change it to UTF8, we can use the alter database command, as shown below.

MariaDB [(none)] > ALTER DATABASE study CHARACTER SET = utf8 COLLATE = utf8_general_ci;Query OK, 1 row affected (0.00 sec)

Any time you execute the ALTER database command, you will see that the updated values are reflected in the db.opt file, as shown below.

# cat / var/lib/mysql/study/db.opt

Default-character-set=utf8

Default-collation=utf8_general_ci

To execute the alter database command, you will see the ALTER privileges on the database.

Just like creating and deleting, you can also use "schema". The following two are exactly the same. ALTER SCHEMA is just a synonym for alter database.

ALTER DATABASE study CHARACTER SET = utf8 COLLATE = utf8_general_ci;ALTER SCHEMA study CHARACTER SET = utf8 COLLATE = utf8_general_ci

Also, note that if you are already in the database, you can omit the database name in the "ALTER" command, as shown below.

MariaDB [(none)] > USE study;MariaDB [study] > ALTER DATABASE CHARACTER SET = utf8 COLLATE = utf8_general_ci;Query OK, 1 row affected (0.00 sec)

As you can see here, I used to change the database. So, in my ALTER DATABASE, I didn't specify a database name.

7. Upgrade migrated and encoded data catalog options

This is required if you are running a changed version of the MySQL database (prior to 5.1) and are trying to upgrade to a MySQL / MariaDB version of 5.1 or later. The following command with the upgrade data Catalog name option updates the name of the database directory with the appropriate coding implementation that matches MySQL 5.1 or later. This ensures that the database name and database directory are mapped correctly and that there are no coding problems in the name.

Third, this feature is used only if you upgrade from an older version of MySQL to version 5.1 or later. When you have special characters in the database name, as shown below, the database name has a hyphen (that is,-ekek- something).

Also, note that "# mysql11#" is the keyword that should be used, followed by the database name.

ALTER DATABASE `# mysql11#study- boys` UPGRADE DATA DIRECTORY NAME

In this example, the above command correctly encodes the database name as "@ 002dstudy @ 002dboys", using @ 002d as the dash special character in the database name.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.