Создание и удаление баз в MySQL/MariaDB

В данных примерах используется командная оболочка mysql и phpMyAdmin.

Если работа ведется на продуктивном сервере баз данных, рекомендуется сделать резервные копии.

Подключение к СУБД

Если мы планируем работать в командной строке, заходим в среду управления MySQL.

а) В Linux вводим команду:

* где root — пользователь, под которым мы будем подключаться к оболочке; ключ -p потребует ввода пароля.

б) В Windows запускаем командную строку — в меню пуск или найдя ее в поиске. Переходим в каталог, с установленной СУБД и запускаем одноименную команду mysql, например:

cd «%ProgramFiles%\MySQL\MySQL Server 5.5\bin\»

* в данном примере предполагается, что у нас установлена MySQL версии 5.5.

* здесь, как и в Linux, идет подключение к mysql/mariadb под учетной записью root с запросом пароля.

Создание новой базы

Для создания базы используется SQL-запрос CREATE DATABASE. Рассмотрим подробнее его использование.

Командная строка

Используйте данный шаблон команды:

> CREATE DATABASE newdb DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

* вышеописанная команда создаст базу данных с названием newdb и кодировкой UTF-8 (самая распространенная и универсальная).

Проверить, что база появилась можно командой:

* данная команда выводит в консоль список баз, созданных в СУБД.

Подключиться к базе можно командой:

phpMyAdmin

В phpMyAdmin переходим в раздел Базы данных — вводим название новой базы — выбираем кодировку и нажимаем Создать:

Настройка прав доступа

Чтобы к созданной базе можно было подключиться, добавим пользователя:

> GRANT ALL PRIVILEGES ON newdb.* TO dbuser@localhost IDENTIFIED BY ‘password’ WITH GRANT OPTION;

* где newdb.* — наша база и все ее таблицы; dbuser@localhost — имя учетной записи, которая будет подключаться с локального сервера; password — придуманный нами пароль.
** В данном примере, учетной записи будут предоставлены полные права (ALL PRIVILEGES). Подробнее о правах в MySQL читайте статью Как создать пользователя MySQL и дать ему права.

Посмотреть список пользователей, которые имеют доступ к базе можно командой:

> SELECT db, host, user FROM mysql.db WHERE db=’newdb’;

* в данном примере мы выведем учетные записи, которым был дан прямой доступ к созданной нами базе. В данном списке не будут отражены пользователи с глобальными правами (например, root).

Поменять пароль пользователю можно одной из команд (в зависимости от версии СУБД):

> SET PASSWORD FOR ‘dbuser’@’localhost’ = PASSWORD(‘new_password’);

> ALTER USER ‘dbuser’@’localhost’ IDENTIFIED BY ‘new_password’;

> UPDATE mysql.user SET Password=PASSWORD(‘new_password’) WHERE USER=’dbuser’ AND Host=’localhost’;

* все 3 команды меняют пароль для пользователя dbuser@localhost на новый — new_password.

При необходимости, удалить пользователя можно командами:

> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ‘dbuser’@’localhost’;

> DROP USER ‘dbuser’@’localhost’;

* первая команда отнимает все привилегии, выданные пользователю. Вторая удаляет самого пользователя.

Удаление базы MySQL

Удаление выполняется командой DROP DATABASE.

Командная консоль

Попробуем удалить ранее созданную базу:

> DROP DATABASE newdb;

phpMyAdmin

Выбираем нужную базу галочкой и кликаем по Удалить:

MySQL CREATE DATABASE

Summary: in this tutorial, you will learn how to use the MySQL CREATE DATABASE statement to create a new database in the server.

MySQL implements a database as a directory that contains all files which correspond to tables in the database.

To create a new database in MySQL, you use the CREATE DATABASE statement with the following syntax:

First, specify the database_name following the CREATE DATABASE clause. The database name must be unique within the MySQL server instance. If you try to create a database with a name that already exists, MySQL issues an error.

Second, to avoid an error in case you accidentally create a database that already exists, you can specify the IF NOT EXISTS option. In this case, MySQL does not issue an error but terminates the CREATE DATABASE statement instead.

Third, specify the character set and collation for the new database at creation time. If you omit the CHARACTER SET and COLLATE clauses, MySQL uses the default character set and collation for the new database.

Creating a new database using mysql program

To create a new database via the mysql program, you use the following steps:

First, log in to the MySQL Server using the root user

Type the password for the root user and press Enter .

Next, to display the existing database in the server to make sure that you are not creating a new database that already exists, you use the SHOW DATABASES command as follows:

MySQL returns five existing databases in the current server.

Then, issue the CREATE DATABASE command with the database e.g., testdb and press Enter:

After that, if you want to review the created database, you can use the SHOW CREATE DATABASE command:

MySQL returns the database name and the character set and collation of the database.

Finally, to access the newly created database, you use the USE database command as follows:

Now, you can start creating tables and other databases objects within the testdb database.

To quit the mysql program, type exit command:

Creating a new database using MySQL Workbench

To create a new database using the MySQL Workbench, you follow these steps:

First, launch the MySQL Workbench and click the setup new connection button as shown in the following screenshot:

Second, type the name for the connection and click the Test Connection button.

MySQL Workbench displays a dialog asking for the password of the root user:

You need to (1) type the password for the root user, (2) check the Save password in vault, and (3) click OK button.

Third, double-click the connection name Local to connect to the MySQL Server.

MySQL Workbench opens the following window which consists of four parts: Navigator, Query, Information, and Output.

Fourth, click the create a new schema in the connected server button from the toolbar:

In MySQL, the schema is the synonym for the database. Creating a new schema also means creating a new database.

Fifth, the following window is open. You need to (1) enter the schema name, (2) change the character set and collation if necessary, and click the Apply button:

Sixth, MySQL Workbench opens the following window that displays the SQL script which will be executed. Note that the CREATE SCHEMA statement command has the same effect as the CREATE DATABASE statement.

If everything is fine, you will see the new database created and showed in the schemas tab of the Navigator section.

Seventh, to select the testdb2 database, (1) right click the database name and (2) choose Set as Default Schema menu item:

The testdb2 node is open as shown in the following screenshot.

Now, you can work with testdb2 from the MySQL Workbench.

In this tutorial, you have learned how to create a new database from mysql program using the MySQL CREATE DATABASE statement and from MySQL Workbench using the CREATE SCHEMA statement.

Creating database in mysql windows

If the administrator creates your database for you when setting up your permissions, you can begin using it. Otherwise, you need to create it yourself:

Under Unix, database names are case-sensitive (unlike SQL keywords), so you must always refer to your database as menagerie , not as Menagerie , MENAGERIE , or some other variant. This is also true for table names. (Under Windows, this restriction does not apply, although you must refer to databases and tables using the same lettercase throughout a given query. However, for a variety of reasons, the recommended best practice is always to use the same lettercase that was used when the database was created.)

If you get an error such as ERROR 1044 (42000): Access denied for user ‘micah’@’localhost’ to database ‘menagerie’ when attempting to create a database, this means that your user account does not have the necessary privileges to do so. Discuss this with the administrator or see Section 6.2, “Access Control and Account Management”.

Creating a database does not select it for use; you must do that explicitly. To make menagerie the current database, use this statement:

Your database needs to be created only once, but you must select it for use each time you begin a mysql session. You can do this by issuing a USE statement as shown in the example. Alternatively, you can select the database on the command line when you invoke mysql . Just specify its name after any connection parameters that you might need to provide. For example:

menagerie in the command just shown is not your password. If you want to supply your password on the command line after the -p option, you must do so with no intervening space (for example, as -p password , not as -p password ). However, putting your password on the command line is not recommended, because doing so exposes it to snooping by other users logged in on your machine.

You can see at any time which database is currently selected using SELECT DATABASE() .

Creating database in mysql windows

If the administrator creates your database for you when setting up your permissions, you can begin using it. Otherwise, you need to create it yourself:

Under Unix, database names are case-sensitive (unlike SQL keywords), so you must always refer to your database as menagerie , not as Menagerie , MENAGERIE , or some other variant. This is also true for table names. (Under Windows, this restriction does not apply, although you must refer to databases and tables using the same lettercase throughout a given query. However, for a variety of reasons, the recommended best practice is always to use the same lettercase that was used when the database was created.)

If you get an error such as ERROR 1044 (42000): Access denied for user ‘micah’@’localhost’ to database ‘menagerie’ when attempting to create a database, this means that your user account does not have the necessary privileges to do so. Discuss this with the administrator or see Section 6.2, “Access Control and Account Management”.

Creating a database does not select it for use; you must do that explicitly. To make menagerie the current database, use this statement:

Your database needs to be created only once, but you must select it for use each time you begin a mysql session. You can do this by issuing a USE statement as shown in the example. Alternatively, you can select the database on the command line when you invoke mysql . Just specify its name after any connection parameters that you might need to provide. For example:

menagerie in the command just shown is not your password. If you want to supply your password on the command line after the -p option, you must do so with no intervening space (for example, as -p password , not as -p password ). However, putting your password on the command line is not recommended, because doing so exposes it to snooping by other users logged in on your machine.

You can see at any time which database is currently selected using SELECT DATABASE() .

MySQL Create Table | How to Create Database in MySQL

Steps to Create Database in MySQL

Create Database in two ways

1) By executing a simple SQL query

2) By using forward engineering in MySQL Workbench

As SQL beginner, let’s look into the query method first.

How to Create Database

Here is how to create a database in MySQL:

CREATE DATABASE is the SQL command used for creating a database in MySQL.

Imagine you need to create a database with name «movies». You can create a database in MySQL by executing following SQL command.

Note: you can also use the command CREATE SCHEMA instead of CREATE DATABASE

Now let’s improve our SQL query adding more parameters and specifications.

IF NOT EXISTS

A single MySQL server could have multiple databases. If you are not the only one accessing the same MySQL server or if you have to deal with multiple databases there is a probability of attempting to create a new database with name of an existing database . IF NOT EXISTS let you to instruct MySQL server to check the existence of a database with a similar name prior to creating database.

When IF NOT EXISTS is used database is created only if given name does not conflict with an existing database’s name. Without the use of IF NOT EXISTS MySQL throws an error.

Collation and Character Set

Collation is set of rules used in comparison. Many people use MySQL to store data other than English. Data is stored in MySQL using a specific character set. The character set can be defined at different levels viz, server , database , table and columns.

You need to select the rules of collation which in turn depend on the character set chosen.

For instance, the Latin1 character set uses the latin1_swedish_ci collation which is the Swedish case insensitive order.

The best practice while using local languages like Arabic , Chinese etc is to select Unicode (utf-8) character set which has several collations or just stick to default collation utf8-general-ci.

You can find the list of all collations and character sets here

You can see list of existing databases by running following SQL command.

How to Create Table in MySQL

CREATE TABLE command is used to create tables in a database

Tables can be created using CREATE TABLE statement and it actually has the following syntax.

HERE

• «CREATE TABLE» is the one responsible for the creation of the table in the database.
• «[IF NOT EXISTS]» is optional and only create the table if no matching table name is found.
• «`fieldName`» is the name of the field and «data Type» defines the nature of the data to be stored in the field.
• «[optional parameters]» additional information about a field such as » AUTO_INCREMENT» , NOT NULL etc.

MySQL Create Table Example

Below is a MySQL example to create a table in database:

Now let’s see what the MySQL’s data types are. You can use any of them depending on your need. You should always try to not to underestimate or overestimate potential range of data when creating a database.

DATA TYPES

Data types define the nature of the data that can be stored in a particular column of a table

MySQL has 3 main categories of data types namely

1. Numeric,
2. Text
3. Date/time.

Numeric Data types

Numeric data types are used to store numeric values. It is very important to make sure range of your data is between lower and upper boundaries of numeric data types.

TINYINT( )	-128 to 127 normal 0 to 255 UNSIGNED.
SMALLINT( )	-32768 to 32767 normal 0 to 65535 UNSIGNED.
MEDIUMINT( )	-8388608 to 8388607 normal 0 to 16777215 UNSIGNED.
INT( )	-2147483648 to 2147483647 normal 0 to 4294967295 UNSIGNED.
BIGINT( )	-9223372036854775808 to 9223372036854775807 normal 0 to 18446744073709551615 UNSIGNED.
FLOAT	A small approximate number with a floating decimal point.
DOUBLE( , )	A large number with a floating decimal point.
DECIMAL( , )	A DOUBLE stored as a string , allowing for a fixed decimal point. Choice for storing currency values.

Text Data Types

As data type category name implies these are used to store text values. Always make sure you length of your textual data do not exceed maximum lengths.

CHAR( )	A fixed section from 0 to 255 characters long.
VARCHAR( )	A variable section from 0 to 255 characters long.
TINYTEXT	A string with a maximum length of 255 characters.
TEXT	A string with a maximum length of 65535 characters.
BLOB	A string with a maximum length of 65535 characters.
MEDIUMTEXT	A string with a maximum length of 16777215 characters.
MEDIUMBLOB	A string with a maximum length of 16777215 characters.
LONGTEXT	A string with a maximum length of 4294967295 characters.
LONGBLOB	A string with a maximum length of 4294967295 characters.

Date / Time

DATE	YYYY-MM-DD
DATETIME	YYYY-MM-DD HH:MM:SS
TIMESTAMP	YYYYMMDDHHMMSS
TIME	HH:MM:SS

Apart from above there are some other data types in MySQL.

ENUM	To store text value chosen from a list of predefined text values
SET	This is also used for storing text values chosen from a list of predefined text values. It can have multiple values.
BOOL	Synonym for TINYINT(1), used to store Boolean values
BINARY	Similar to CHAR, difference is texts are stored in binary format.
VARBINARY	Similar to VARCHAR, difference is texts are stored in binary format.

Now let’s see a query for creating a table which has data of all data types. Study it and identify how each data type is defined in the below create table MySQL example.

Best practices

• Use upper case letters for SQL keywords i.e. «DROP SCHEMA IF EXISTS `MyFlixDB`;»
• End all your SQL commands using semi colons.
• Avoid using spaces in schema, table and field names. Use underscores instead to separate schema, table or field names.

MySQL workbench ER diagram forward engineering

MySQL workbench has utilities that support forward engineering. Forward engineering is a technical term is to describe the process of translating a logical model into a physical implement automatically.

We created an ER diagram on our ER modeling tutorial. We will now use that ER model to generate the SQL scripts that will create our database.

Creating the MyFlix database from the MyFlix ER model

1. Open the ER model of MyFlix database that you created in earlier tutorial.

2. Click on the database menu. Select forward engineer

3. The next window, allows you to connect to an instance of MySQL server. Click on the stored connection drop down list and select local host. Click Execute

4. Select the options shown below in the wizard that appears. Click next

5. The next screen shows the summary of objects in our EER diagram. Our MyFlix DB has 5 tables. Keep the selections default and click Next.

6.. The window shown below appears. This window allows you to preview the SQL script to create our database. We can save the scripts to a *.sql» file or copy the scripts to the clipboard. Click on next button

7. The window shown below appears after successfully creating the database on the selected MySQL server instance.

Summary

• Creating a database involves translating the logical database design model into the physical database.
• MySQL supports a number of data types for numeric, dates and strings values.
• CREATE DATABASE command is used to create a database
• CREATE TABLE command is used to create tables in a database
• MySQL workbench supports forward engineering which involves automatically generating SQL scripts from the logical database model that can be executed to create the physical database

The Database along with Dummy Data is attached. We will be using this DB for all our further tutorials. Simple import the DB in MySQL Workbench to get started