3.3.1 Создание и выбор базы данных

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

MySQL – это одна из востребованных систем управления базами данных (СУБД). База данных предназначена для хранения массивов данных в структурированном виде. Для работы с данными используется язык программирования SQL (Structured Query Language, в переводе – структурированный язык запросов).

MySQL – не единственная система управления базами данных. Также широко используются MariaDB, PostgreSQL, Microsoft SQL Server, SQLite и продукты от Percona. На серверах LITE. HOST используется сервер базы данных MariaDB 10.3, который является аналогом MySQL 5.7. Обратите внимание, что поменять версию СУБД на хостинге нельзя, для этого нужно использовать наши виртуальные серверы на базе операционной системы Linux.

Небольшая шпаргалка по работе в консоли postgreSQL.

Чтобы полноценно работать с консолью, лучше всего переключиться на супер-юзера базы данных. Для postgresql — этот юзер (чаще всего) — postgres
.

Консоль позволяет вводить непосредственно команды SQL. Каждая из них должна заканчиваться символом «;», нажатие «ENTER» — лишь перенесет ввод на следующую строку.

Помимо команд языка SQL, консоль знает ещё несколько команд. Все они начинаются с обратного слеша.

Выбор базы данных в консоли PostgreSql

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

Получить список баз данных:

где dbname — это имя базы

Остальные команды консоли

Команд много, вот наиболее часто используемые:

• \dt — список всех таблиц.
• \d table — структура таблицы table.
• \du — список всех пользователей и их привилегий.
• \dt+ — список всех таблиц с описанием.
• \dt *s* — список всех таблиц, содержащих s в имени.
• \i FILE — выполнить команды из файла FILE.
• \o FILE — сохранить результат запроса в файл FILE.
• \a — переключение между режимами вывода: с/без выравнивания.

Чтобы посмотреть весь список команд, введите

Выход из консоли PostgreSQL

Чтобы выйти из консоли, наберите

3.3.1 Creating and Selecting a Database

   mysql> CREATE DATABASE menagerie;  
  

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.)

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

   mysql> USE menagerie
Database changed  
  

Your database needs to be created only once, but you must select
it for use each time you begin a

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
. Just
specify its name after any connection parameters that you might
need to provide. For example:

  
  (♀4gt; mysql -h host -u user -p menagerie
Enter password: ********)gt; mysql -h host -u user -p menagerie
Enter password: ********gt; mysql -h host -u user -p menagerie
Enter password: ********  
gt; mysql -h  host 
 -u  user 
 -p menagerie
 Enter password: ********
 You can see at any time which database is currently selected
 using  SELECT
 
  DATABASE()
 
 .
 День добрый, друзья! 🙂 Для подключения к mysql наберите
  mysql -h 127.0.0.1 -u root -p  
 h — хост базы данных, если коннектимся к локальной базе (по ip 127.0.0.1), этот параметр можно опустить
 u — имя пользователя
 p — пароль, его нужно будет ввести после нажатия enter
 Например, если нужно законнектиться к локальной быдухе под root`ом и пароль у него не установлен (пустой пароль — это плохо), достаточно написать
  mysql -u root  
 Если на экране появляется приветствие mysql, то всё прошло ok.
  Welcome to the MySQL monitor.    Commands end with ; or \g.
Your MySQL connection id is 344
Server version: 5.5.28-0ubuntu0.12.04.3 (Ubuntu)

Copyright (c) 2000, 2012, Oracle and/or its affiliates.  All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates.  Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help.  Type '\c' to clear the current input statement.

mysql>  
 Для выхода из командной строки mysql можно написать exit, в linux достаточно нажать ctrl+C.  Теперь можно писать любые mysql-запросы.  Чтобы запрос выполнился, в конце обязательно нужно поставит точку с запятой «;».  При написании длинных запросов удобно делать enter`ом переход на следующую строку.  А после полного написание запроса поставить «;» и нажать «enter».
 Для работы с конкретной базой данных нужно её выбрать с помощью оператора use.  Показать все базы данных:
  show databases;  
 Результатом будет что то вроде этого:
  mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| jeka |
| linky |
| mysql |
| performance_schema |
| phpmyadmin |
| test |
+--------------------+
7 rows in set (0.00 sec)
  
 Выбрать базу данных jeka:
  mysql> use jeka
Database changed
  
 Посмотреть названия всех таблиц в конкретной базе данных (после выполнения use):
  mysql> show tables;  
 Просмотреть структуру таблицы (имена и тип полей):
  mysql> describe post_estimations;
+------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| post_id | int
 unsigned | YES | MUL | NULL | |
| user_id | int unsigned | YES | MUL | NULL | |
| estimation | float(2,1) unsigned | NO | | NULL | |
| date | int unsigned | NO | | NULL | |
+------------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
  
 Достать информацию о юзере с id=22:
  mysql> SELECT * FROM users WHERE id=22;
  
 Бэкап структуры таблицы, а точнее sql-запрос на её создание
  show create table table_name;
  
 Если в конце запроса поставить не  ; 
 а  \G 
 , то результат будет показан другим образом — иногда весьма удобно, если таблица имеет много полей:
  select * from update__v4_errors where id = 1102003\G  
 
 
 Посмотреть, какие запросы выполняются в данный момент на сервере MySQL:
  show processlist;  
 Учтите, что будут отображены еще не выполненные (активные) запросы, поэтому show processlist;
 актуальная команды только для нагруженных БД, в других случаях маловероятно, что команда что то покажет.
 Подробнее о том, как изменять  структуру mysql-таблиц 
 (alter table).
 Удаление пользователя
   mysql> DROP USER 'admin'@'localhost';
Query OK, 0 rows affected (0.01 sec)  
  
 Удаление таблицы
   mysql> DROP TABLE `users`;
Query OK, 0 rows affected (0.02 sec)  
  
 Мало букафф? Читайте есчо !
  Экспорт данных из PostgreSQL в MySQL 
 Март 19, 2017 г.
 
 
 Просмотр списка таблиц
 Отобразим список текущих таблиц с помощью знакомой нам команды SHOW
 .
   mysql> SHOW TABLES;
Empty set (0.01 sec)  
  
 Empty set
 говорит нам о том, что в базе данных нет таблиц.
 Включить удаленный PostgreSQL доступ для пользователей
 Нам нужно отредактировать файл /etc/postgresql/<VERSION>/main/pg_hba.conf, задав опцию md5 вместо peer.
 <VERSION> может быть 10, 11, 12 и т.д.
  sudo vim /etc/postgresql/12/main/pg_hba.conf  
 После этого сделать restart PostgreSQL:
  sudo service postgresql restart  
 Изменение данных в таблице
 Давайте обновим имя пользователя с помощью команды UPDATE
 , подробнее об этом в  документации 
 .
   mysql> UPDATE `users` SET `name` = 'Elena Popova' WHERE `id` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0  
  
 При повторном получении данных из таблицы, можно увидеть, что имя было изменено.  Когда в таблице много пользователей, то при указании конкретных идентификаторов будут происходить изменения для выбранных пользователей.  А если, условие WHERE
 опустить полностью, то изменения будут внесены для всех пользователей в таблице.
   mysql> SELECT * FROM `users`;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | Elena Popova |
+----+--------------+
1 row in set (0.00 sec)  
  
 Удаление базы данных
 Удалим базу данных test
 , чтобы вернуться к первоначальному состоянию.  Сделать это можно с помощью команды  DROP DATABASE
 
 .
   mysql> DROP DATABASE `test`;
Query OK, 0 rows affected (0.00 sec)  
  
 PostgreSQL создание новой роли и базы данных
  sudo su - postgres -c "createuser admin"  
 Создание новой базы данных:
  sudo su - postgres -c "createdb testDb"  
 Дать права роли на базу данных:
  grant all privileges on database testDb to admin;  
 Выбор базы данных
 Для выбора базы данных используйте USE
 , если это не сделать, то в командах SELECT
 , UPDATE
 , INSERT
 и DELETE
 нужно будет всегда указывать название базы данных, что неудобно.
   mysql> USE test;
Database changed  
  
 Как сделать бэкап базы mysql через консоль Linux?
 Сделать бэкап базы database в файл dump_name.sql
  mysqldump -u [username] -p [password] [database] > [dump_name.sql]
  
 Развернуть базу из файла через командную строку
  mysql -u [username] -p [password] [database] < [dump_to_restore.sql]
  
 Сделать дамп структуры одной таблицы mysql (без данных):
  mysqldump -u[user] -p[password] -h[host] [database] [table_name] --no-data > /path/dump_name.sql
  
  mysqldump -uroot mydatabase users > users.dump.sql  
 Развернуть mysql-dump в БД с именем database_name:
  mysql -uroot database_name < users.dump.sql  
 Ещё одна  интересная 
 штука.  Выполнять из консоли, не требуется предварительно подключаться к mysql (mysql>).  Вывести список таблиц базы данных, не подключаясь непосредственно к mysql:
  mysql -uroot -e 'show tables' database_name && echo done  
  mysql> show variables like '%incr%';
  
 Выберите базу данных MySQL из окна командной строки
 В MySQL> окно командной строки может быть очень простой, чтобы выбрать определенную базу данных.  Вы можете выбрать, чтобы указать базу данных с помощью команд SQL.
 примеров
 Следующие примеры выбраны w3big базы данных:
  [root@host]# mysql -u root -p
Enter password:******
mysql> use w3big;
Database changed
mysql>
  
 После выполнения указанной выше команды, вы успешно выбраны w3big базы данных, база данных будет выполняться в w3big в последующих операциях.
  Примечание: 
 Все имена баз данных, имена таблиц, таблиц поля чувствительны к регистру.  Таким образом, при использовании команды SQL необходимо ввести правильное имя.
 Изменение пароля пользователя
   mysql> ALTER USER 'admin'@'localhost' IDENTIFIED BY '2XdoiZrsDN67ozCd';
Query OK, 0 rows affected (0.00 sec)  
  
 Создание пользователей
   mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'KJ2aqCa89vjYsrFh';
Query OK, 0 rows affected (0.01 sec)  
  
 Полезные команды PostgreSQL
 Выйти из клиента PostgreSQL:
 Показать список баз данных PostgreSQL:
 Показать список таблиц:
 Показать список пользователей (ролей):
 Показать структуру таблицы:
  \d table_name  
 Переименовать базу данных:
  ALTER DATABASE db RENAME TO newdb;  
 Удалить базу данных:
  drop database db_name;  
 Изменить текущую базу данных в PostgreSQL (вы не сможете переименовать или удалить текущую базу данных):
  \connect 
 db_name или более короткий alias:  \c db_name 
 Удалить роль (пользователя):
  DROP ROLE user_name;
  
 Роль не будет удалена, если у нее есть привелегии — возникнет ошибка  ERROR: role cannot be dropped because some objects depend on it 
 .
  db=# REASSIGN OWNED BY admin2 TO postgres;
REASSIGN OWNED
db=# DROP OWNED BY admin2;
DROP OWNED
db=# drop role admin2;
DROP ROLE  
  ALTER ROLE admin2 WITH login;  
 Выбор shema psql в консоли:
 Посмотреть список всех схем:
  \dn  
 Подключиться к конкретной схеме:
  SET search_path TO schema_name  
 Sequences
 Получить имена всех созданных sequences:
  select relname from pg_class where relkind='S';  
 Получить последнее значение sequence, которые будет присвоено новой вставляемой в таблицу записи:
  SELECT last_value FROM order_id_seq;  
 Просмотр списка баз данных
 Для просмотра доступных баз данных пользователю, используйте команду SHOW
 .
Обратите внимание, при работе с MySQL после каждой команды обязательно ставится точка с запятой ;
 .
   mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)  
  
 В нашем случае на сервере только системные базы данных, поэтому создадим новую базу test для хранения в ней своих данных.
 Выберите базу данных MySQL с помощью PHP скрипт
 PHP предоставляет функции mysql_select_db выбрать базу данных.  После успешной реализации функции возвращает значение TRUE, в противном случае она возвращает FALSE.
 грамматика
  bool mysql_select_db( db_name, connection );
  
 примеров
 В следующем примере показано, как использовать функцию mysql_select_db, чтобы выбрать базу данных:
  <html>
<head>
<meta charset="utf-8"> 
<title>选择 MySQL 数据库</title>
</head>
<body>
<?php
$dbhost = 'localhost:3036';
$dbuser = 'guest';
$dbpass = 'guest123';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
 die('连接失败: ' .  mysql_error());
}
echo '连接成功';
mysql_select_db( 'w3big' );
mysql_close($conn);
?>
</body>
</html>
  
 Создание базы данных
 Для создания базы данных необходимо воспользоваться командой CREATE DATABASE
 .  Обратите внимание, что на виртуальном хостинге  создание баз данных 
 должно производиться только через панель управления хостингом.
   mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.01 sec)  
  
 Далее, с помощью SHOW DATABASES
 можете убедиться, что новая база данных создана
   mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)  
  
 Выборка данных из таблицы
   mysql> SELECT * FROM `users`;
+----+---------------+
| id | name          |
+----+---------------+
|  1 | Elena Ivanova |
+----+---------------+
1 row in set (0.00 sec)  
  
 Обратите внимание, пользователю «Elena Ivanova» система базы данных присвоила идентификатор 1, с помощью него можно менять данные для отдельных строк.
 В документации можно вдобавок узнать про такие полезные операторы как ORDER
 , GROUP
 и LIMIT
 — для сортировки, объединения и ограничения числа записей.  К тому же, мы не рекомендуем использовать ORDER BY RAND()
 на таблицах с большим числом данных, это будет приводить к созданию временных таблиц и медленному выполнению запросов.
 Также, при выборке данных мы использовали символ *
 , который получил все столбцы из таблицы.  Однако, если в таблице много столбцов, то оптимальнее будет делать выборку тех, которые нужны для дальнейшей работы.  Например, мы можем получить только имена пользователей:
   mysql> SELECT `name` FROM `users`;
+---------------+
| name          |
+---------------+
| Elena Ivanova |
+---------------+
1 row in set (0.00 sec)  
  
 Удаление строки из таблицы
 Пользователя можно удалить с помощью команды  DELETE
 
 , также с использованием идентификатора.
   mysql> DELETE FROM `users` WHERE `id` = 1;
Query OK, 1 row affected (0.01 sec)  
  
 Если опустить указание идентификатора через WHERE
 , то запрос удалит все строки из таблицы.
 Подключение к базе данных
   [~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32227
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  
  
 Что делать если вижу знаки вопросов вместо текста в MySQL?
 Это проблема с кодировкой.  Вероятно в my.ini файле mysql-сервера не добавлено:
  [mysqld]
init-connect='SET NAMES utf8'  
 Нужно добавить в .ini файл либо выполнить в консоли команду «SET NAMES utf8», после чего во время текущей сессии отображаться русский текст в mysql-консоли будет без проблем:
  mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)  
 PostgreSQL Подключение, Пользователи (Роли) и Базы Данных
  sudo -i -u postgres
psql  
 Выйти из клиента PostgreSQL:
 Для подключения к базе данных PostgreSQL можно использовать команду:
  psql -U<USERNAME> -h<HOSTNAME> -d<DB_NAME>  
 Если такая команда не просит ввести пароль пользователя, то можно еще добавить опцию -W.
  $ psql -Usrv161924_dom -hpgsql-161924.srv.hoster.ru -dsrv161924_dom -W
Password for user srv161924_dom:  
 После ввода пароля и успешного подключения к базе данных PostgreSQL, можно посылать SQL-запросы и psql-команды.
 Создание таблицы
 Создадим таблицу пользователей с помощью команды CREATE
 .
   mysql> CREATE TABLE `users` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `name` VARCHAR
 NOT NULL , PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.02 sec)  
  
 С более подробной информацией о команде CREATE
 можно ознакомиться на сайте  mariadb.com 
 .  Теперь с помощью SHOW
 можно увидеть созданную таблицу.
   mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| users          |
+----------------+
1 row in set (0.00 sec)  
  
 В обязательном порядке рекомендуем изучить создание индексов, так как без этого скорость выполнения запросов на больших таблицах может быть медленной.  Очень часто разработчики этим пренебрегают, из-за чего с ростом размера проекта (числа пользователей, товаров, заказов) сайт начинает работать медленно.
 Вставка данных в таблицу
 Теперь можно добавить нового пользователя в таблицу с помощью команды INSERT
 , подробнее о синтаксисе данной команды можно узнать на сайте  mariadb.com 
 .
   mysql> INSERT `users` SET `name` = 'Elena Ivanova';
Query OK, 1 row affected (0.00 sec)  
  
 Установка PostgreSQL на Linux (Mint)
 Для подключения к базе данных PostgreSQL понадобится установленный PostgreSQL клиент:
  sudo apt install postgresql-client-<VERSION>  
  sudo apt install postgresql-client-12  
 Для установки PostgreSQL сервера:
  sudo apt install postgresql  
 Проверим, можем ли мы подключиться к базе данных PostgreSQL:
  sudo -u postgres psql -c "SELECT version();"
  
 Вывод команды должен быть примерно таким:
  $ sudo -u postgres psql -c "SELECT version();"
 version 
----------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.10 (Ubuntu 10.10-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
(1 row)
  
 Настройка прав пользователя
 Создав пользователя, по умолчанию он не будет иметь доступа к базам данных.  Чтобы пользователь мог читать и изменять данные в базе данных, нужно его наделить этими правами с помощью команды  GRANT
 
 .
   mysql> GRANT ALL PRIVILEGES ON test.* TO 'admin'@'localhost';
Query OK, 0 rows affected (0.01 sec)  
  
 Заключение
 Описанные команды SQL выполняются на любом сайте, где происходит хранение каких-либо данных в базе данных и это лишь малая часть функций сервера базы данных.  Вы можете дальше изучить разные типы движков MyISAM, InnoDB и MEMORY.  Настройку индексов для более быстрой работы выборки данных, различные типы данных в таблицах (INT, CHAR, ENUM, DATE и прочие).