DROP DATABASE. Создание и удаление баз данных в MySQL

Summary: in this tutorial, you will learn how to use the MySQL DROP DATABASE statement to delete an existing database in the server.

Introduction to the MySQL DROP DATABASE statement

The DROP DATABASE statement drops all tables in the database and deletes the database permanently. Therefore, you need to be very careful when using this statement.

SQL (Structured Query Language)

In this statement, you specify the name of the database which you want to delete after the DROP DATABASE keywords.

If you drop a database that does not exist, MySQL will issue an error.

To prevent an error from occurring if you delete a non-existing database, you can use the IF EXISTS option. In this case, MySQL will terminate the statement without issuing any error.

The DROP DATABASE statement returns the number of tables it deleted.

In MySQL, the schema is the synonym for the database. Therefore, you can use them interchangeably:

MySQL DROP DATABASE using mysql program example

mysql -u root -p

Enter password: ********

Second, display all the databases using the SHOW DATABASES statement:

Third, drop the testdb database by issuing the DROP DATABASE statement:

testdb; SQL (Structured Query Language)

Query OK, 0 rows affected (0.03 sec)

MySQL returned zero affected rows indicating that the testdb database has no tables.

DROP DATABASE using MySQL Workbench

First, launch the MySQL workbench and log in to the MySQL Server.

Third, MySQL Workbench displays a dialog to confirm the deletion.

If you choose Review SQL, you’ll see the SQL statement that will be executed. If you choose Drop Now, it’ll delete the database immediately.

To be safe, let’s choose Review SQL:

Fourth, once you are sure that the SQL statement is going to drop the right database, you can click the Execute button to execute the statement.

If you view the schemas pane, you will see that the testdb2 is not on the list anymore.

Summary

Was this tutorial helpful?

Steps for a Fresh Installation of MySQL

• Go to the download page for the MySQL APT repository at
  https://dev.mysql.com/downloads/repo/apt/.
• Select and download the release package for your Linux
  distribution.
• During the installation of the package, you will be
  asked to choose the versions of the MySQL server and
  other components (for example, the MySQL Workbench) that
  you want to install. If you are not sure which version
  to choose, do not change the default options selected
  for you. You can also choose
  if you do not want a particular component to be
  installed. After making the choices for all components,
  choose to finish the
  configuration and installation of the release package.

  You can always change your choices for the versions
  later; see Selecting a Major Release Version
  for instructions.

A few third-party native repository packages that have
dependencies on the native MySQL packages may not work with the
MySQL APT repository packages and should not be used together
with them; these include akonadi-backend-mysql,
handlersocket-mysql-5.5, and zoneminder.

Selecting a Major Release Version

A dialogue box then asks you to choose the major release version
you want. Make your selection and choose .
After returning to the command prompt, update package information
from the MySQL APT repository with this command:

The latest version in the selected series will then be installed
when you use the command next
time.

You can use the same method to change the version for any other
MySQL component you want to install with the MySQL APT repository.

Installing Additional MySQL Products and Components

For example, to install the MySQL Workbench:

Installing MySQL from Source with the MySQL APT Repository

This feature is only supported on 64-bit systems.

You can download the source code for MySQL and build it using the
MySQL APT Repository:

• Add the MySQL APT repository to your system’s repository list
  and choose the major release series you want (see
  Adding the MySQL APT Repository for instructions).
• If you are being warned of unmet dependencies by
  , you can fix them using
  :
  sudo apt-get -f install
• All configuration files (like
  my.cnf) are under
  /etc/mysql
• All binaries, libraries, headers, etc., are under
  /usr/bin and
  /usr/sbin
• The data directory is under
  /var/lib/mysql

See also information given in
Starting and Stopping the MySQL Server.

Upgrading MySQL with the MySQL APT Repository

• Make sure you already have the MySQL APT repository on your
  system’s repository list (see Adding the MySQL APT Repository
  for instructions).
• As a general rule, to upgrade from one release series to
  another, go to the next series rather than skipping a series.
  For example, if you are currently running MySQL 5.6 and wish
  to upgrade to a newer series, upgrade to MySQL 5.7 first
  before upgrading to 8.0.
• The MySQL server always restarts after an update by APT. Prior
  to MySQL 8.0.16, run after
  the server restarts to check and possibly resolve any
  incompatibilities between the old data and the upgraded
  software. also performs other
  functions; see mysql_upgrade — Check and Upgrade MySQL Tables for details. As
  of MySQL 8.0.16, this step is not required, as the server
  performs all tasks previously handled by
  .

Replacing a Native Distribution of MySQL Using the MySQL APT Repository

Variants and forks of MySQL are distributed by different parties
through their own software repositories or download sites. You can
replace a native distribution of MySQL installed from your Linux
platform’s software repository with a distribution from the MySQL
APT repository in a few steps.

• Backing Up Your Database
  To avoid loss of data, always back up your database before
  trying to replace your MySQL installation using the MySQL APT
  repository. See Backup and Recovery for
  instructions.
• Adding the MySQL APT Repository and Selecting a Release Series
• Replacing the Native Distribution by an APT Update

Once the native distribution of MySQL has been replaced using
the MySQL APT repository, purging the old MySQL packages from
the native repository using the apt-get
purge, apt-get remove —purge, or
command might impact the newly
installed MySQL server in various ways. Therefore, do
not purge the old MySQL packages from the native repository
packages.

Replacing a MySQL Server Installed by a Direct deb
Package Download

deb packages from MySQL for installing the
MySQL server and its components can either be downloaded from the
MySQL Developer Zone’s MySQL
Download page or from the MySQL APT repository. The
deb packages from the two sources are
different, and they install and configure MySQL in different ways.

• Back up your database. See
  Backup and Recovery for instructions.
• If needed, restore the data on the new MySQL installation. See
  Backup and Recovery for instructions.

Removing MySQL with APT

Then, remove any other software that was installed automatically
with the MySQL server:

Special Notes on Upgrading the Shared Client Libraries

• The MySQL APT repository supports installation of MySQL NDB Cluster on
  Debian and Ubuntu systems. For methods to install NDB Cluster on
  other Debian-based systems, see
  Installing NDB Cluster Using .deb Files.
• If you already have the MySQL server or MySQL NDB Cluster installed on
  your system, make sure it is stopped and you have your data
  and configuration files backed up before proceeding.

• Configuring and Starting MySQL NDB Cluster

  All configuration files (like my.cnf)
  are under /etc/mysql

  All binaries, libraries, headers, etc., are under
  /usr/bin and
  /usr/sbin

  The data directory is /var/lib/mysql

• All configuration files (like my.cnf)
  are under /etc/mysql
• All binaries, libraries, headers, etc., are under
  /usr/bin and
  /usr/sbin
• The data directory is /var/lib/mysql

Installing Additional MySQL NDB Cluster Products and Components

Table 1 Available Packages from the MySQL APT Repository

The
mysql-cluster-community-auto-installer
package cannot be installed on Ubuntu 20.04 or later, which does
not support the python-paramiko package, on
which the
mysql-cluster-community-auto-installer
package depends.

Adding and Configuring the MySQL APT Repository Manually

Here are the steps for adding manually the MySQL APT repository to
your system’s software repository list and configuring it, without
using the release packages provided by MySQL:

• Choose or
  according to your platform.
• Choose the appropriate version name for the version of
  your system; examples include (for
  Debian 10) and (for Ubuntu 18.04).
• For installing MySQL server, client, and database common
  files, choose or
  according to the MySQL version
  you want. To switch to another release series later,
  come back and adjust the entry with your new choice.

  If you already have a version of MySQL installed on
  your system, do not choose a lower version at this
  step, or it might result in an unsupported downgrade
  operation.

• For installing components like MySQL Workbench or MySQL Connector/Python,
  create a single entry for each of them, specifying
  respectively or
  at the end of each
  entry.

You have configured your system to use the MySQL APT repository
and are now ready to continue with
Installing MySQL with APT or
Installing Additional MySQL Products and Components with APT.

Legal Notices

This software and related documentation are provided under a license
agreement containing restrictions on use and disclosure and are
protected by intellectual property laws. Except as expressly
permitted in your license agreement or allowed by law, you may not
use, copy, reproduce, translate, broadcast, modify, license,
transmit, distribute, exhibit, perform, publish, or display any
part, in any form, or by any means. Reverse engineering,
disassembly, or decompilation of this software, unless required by
law for interoperability, is prohibited.

The information contained herein is subject to change without notice
and is not warranted to be error-free. If you find any errors,
please report them to us in writing.

This software or hardware is developed for general use in a variety
of information management applications. It is not developed or
intended for use in any inherently dangerous applications, including
applications that may create a risk of personal injury. If you use
this software or hardware in dangerous applications, then you shall
be responsible to take all appropriate fail-safe, backup,
redundancy, and other measures to ensure its safe use. Oracle
Corporation and its affiliates disclaim any liability for any
damages caused by use of this software or hardware in dangerous
applications.

Oracle, Java, and MySQL are registered trademarks of Oracle and/or its
affiliates. Other names may be trademarks of their respective
owners.

Intel and Intel Inside are trademarks or registered trademarks of
Intel Corporation. All SPARC trademarks are used under license and
are trademarks or registered trademarks of SPARC International, Inc.
AMD, Epyc, and the AMD logo are trademarks or registered trademarks
of Advanced Micro Devices. UNIX is a registered trademark of The
Open Group.

This software or hardware and documentation may provide access to or
information about content, products, and services from third
parties. Oracle Corporation and its affiliates are not responsible
for and expressly disclaim all warranties of any kind with respect
to third-party content, products, and services unless otherwise set
forth in an applicable agreement between you and Oracle. Oracle
Corporation and its affiliates will not be responsible for any loss,
costs, or damages incurred due to your access to or use of
third-party content, products, or services, except as set forth in
an applicable agreement between you and Oracle.

You may create a printed copy of this documentation solely for your
own personal use. Conversion to other formats is allowed as long as
the actual content is not altered or edited in any way. You shall
not publish or distribute this documentation in any form or on any
media, except if you distribute the documentation in a manner
similar to how Oracle disseminates it (that is, electronically for
download on a Web site with the software) or on a CD-ROM or similar
medium, provided however that the documentation is disseminated
together with the software on the same medium. Any other use, such
as any dissemination of printed copies or use of this documentation,
in whole or in part, in another publication, requires the prior
written consent from an authorized representative of Oracle. Oracle
and/or its affiliates reserve any and all rights to this
documentation not expressly granted above.

24 DROP DATABASE Statement

DROP DATABASE drops all tables in
the database and deletes the database. Be
careful with this statement! To use
DROP DATABASE, you need the
DROP privilege on the database.
DROP
SCHEMA is a synonym for DROP
DATABASE.

When a database is dropped, privileges granted specifically for
the database are automatically dropped.
They must be dropped manually. See Section 13.7.1.6, “GRANT Statement”.

IF EXISTS is used to prevent an error from
occurring if the database does not exist.

If the default database is dropped, the default database is unset
(the DATABASE() function returns
NULL).

If you use DROP DATABASE on a
symbolically linked database, both the link and the original
database are deleted.

DROP DATABASE returns the number of
tables that were removed.

If other files or directories remain in the database directory
after MySQL removes those just listed, the database directory
cannot be removed. In this case, you must remove any remaining
files or directories manually and issue the
DROP DATABASE statement again.

Dropping a database does not remove any
TEMPORARY tables that were created in that
database. TEMPORARY tables are automatically
removed when the session that created them ends. See
Section 13.1.20.2, “CREATE TEMPORARY TABLE Statement”.

Prerequisites

• Server running CentOS or AlmaLinux with MySQL or MariaDB.
• Root access to the server and log in as root.
• Create a database if one does not exist.

Video by Andrew Casares

How to Delete a MySQL or MariaDB Database via Command Line

Once complete, you reach a MySQL prompt.

Delete a Database in MySQL or MariaDB

Note: Removing a MySQL or MariaDB database cannot be undone.

DROP DATABASE tutorial_database;

ERROR 1008 (HY000): Can’t drop database ‘tutorial_database’; database doesn’t exist

DROP DATABASE IF EXISTS tutorial_database;

View All MySQL Databases

This tutorial and video covers how to view and delete a MySQL database from the command line in Linux. It is useful for removing databases that are no longer in use.

Как создать базу данных в MySQL?

Для создания базы данных необходимо придумать ей имя.

Создание БД с помощью запроса в терминале

При создании базы данных рекомендую сразу прописывать кодировку для неё, чтобы потом не было проблем с русским языком в таблицах.

Синтаксис создания базы данных.

Создание БД с помощью утилиты mysqladmin

Создание БД, используя модуль PDO для PHP.

Создание БД, используя модуль MySQLi (объектно-ориентированный подход).

Создание БД, используя модуль MySQLi (процедурный подход).

Как удалить базу данных в MySQL?

Удаление БД, используя модуль PDO.

Удаление БД, используя модуль MySQLi (объектно-ориентированный подход).

Удаление БД, используя модуль MySQLi (процедурный подход).