MySQL и PostgreSQL: простейшие операции

Обновлена 26.11.2014

Ниже будут приведены наиболее часто встречающиеся операции, ошибки а так же их решения. Следите за обновлениями: статья будет дополнятся.

1) Просмотр баз данных

mysql:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| puppet_dash        |
| test               |
+--------------------+
4 rows in set (0.11 sec)

postgresql:

Вариант1

$ psql -l
List of databases
Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres  | postgres | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 |
template0 | postgres | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres
: postgres=CTc/postgres
template1 | postgres | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres
: postgres=CTc/postgres
(3 rows)

Вариант2

postgres=# \l
List of databases
Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres  | postgres | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 |
template0 | postgres | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres
: postgres=CTc/postgres
template1 | postgres | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres
: postgres=CTc/postgres
(3 rows)

Вариант3

postgres=# SELECT datname FROM pg_database;
datname
-----------
template1
template0
postgres
(3 rows)

2) Просмотр таблиц

mysql:

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| proc                      |
| procs_priv                |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
17 rows in set (0.00 sec)

postgresql:

Вариант1

asterisk=# \dt;
List of relations
Schema |        Name         | Type  |  Owner
--------+---------------------+-------+----------
public | bill                | table | postgres
public | calls               | table | postgres
(2 rows)

Вариант2

asterisk=# select * from information_schema.tables where table_schema='public' and table_type='BASE TABLE';
table_catalog | table_schema |     table_name      | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defi
ned_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action
---------------+--------------+---------------------+------------+------------------------------+----------------------+---------------------------+----------
----------------+------------------------+--------------------+----------+---------------
asterisk      | public       | bill                | BASE TABLE |                              |                      |                           |
|                        | YES                | NO       |
asterisk      | public       | calls               | BASE TABLE |                              |                      |                           |
|                        | YES                | NO       |

(2 rows)

3) Просмотр структуры таблицы

mysql

mysql> SHOW COLUMNS FROM user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | char(60)                          | NO   | PRI |         |       |
| User                  | char(16)                          | NO   | PRI |         |       |
| Password              | char(41)                          | NO   |     |         |       |
| Select_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv          | enum('N','Y')                     | NO   |     | N       |       |
| File_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv            | enum('N','Y')                     | NO   |     | N       |       |
| References_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv      | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher            | blob                              | NO   |     | NULL    |       |
| x509_issuer           | blob                              | NO   |     | NULL    |       |
| x509_subject          | blob                              | NO   |     | NULL    |       |
| max_questions         | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates           | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections       | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       |
+-----------------------+-----------------------------------+------+-----+---------+-------+
37 rows in set (0.03 sec)

postgresql:

asterisk=# SELECT column_name FROM information_schema.columns WHERE table_name ='cdr';
column_name
-------------
cdr_pkey
calldate
clid
src
dst
dcontext
channel
dstchannel
lastapp
lastdata
duration
billsec
disposition
amaflags
accountcode
uniqueid
userfield
src1
(18 rows)

4) Загрузить большой дамп.

Если у вас дамп слишком большой и загрузить через phpmyadmin не удаётся (ругается, что размер слишком большой или таймаут истёк) то на помощь приходит консоль mysql.

mysql:

$mysql -u user -p DataBase
Enter password:
mysql>source /path/to/dump.sql

После этого загрузится дамп успешно загрузится.

5)Ошибка «LOCK TABLES»

Если при выполнении дампа базы данных возникает такая ошибка:

mysqldump: Got error: 1044: Access denied for user ‘root’@'localhost’ to database ‘information_schema’ when using LOCK TABLES

то нужно добавить параметр —single-transaction:

/usr/local/bin/mysqldump --single-transaction -uroot -p > dump.sql

6) Просмотр текущих сессий

mysql

Вариант1

mysql> show processlist;
+-----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+------+---------+------+-------+------------------+
| 112 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+-----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.04 sec)

Вариант2

# mysqladmin -p processlist
Enter password:
+-----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+----+---------+------+-------+------------------+
| 113 | root | localhost | | Query | 0 | | show processlist |
+-----+------+-----------+----+---------+------+-------+------------------+

postgresql

Вариант1

postgres=# SELECT * from pg_stat_activity;
datid | datname | procpid | usesysid | usename | application_name | client_addr | client_port | backend_start | xact_start | query_start | waiting | current_query
-------+--------------+---------+----------+--------------+------------------+-------------+-------------+-------------------------------+-------------------------------+-------------------------------+---------+---------------------------------
16385 | postfixadmin | 33916 | 16384 | postfixadmin | | 127.0.0.1 | 44414 | 2011-08-05 17:23:27.244198+03 | | 2011-08-05 17:25:30.894716+03 | f | <IDLE>
11874 | postgres | 50281 | 10 | pgsql | psql | | -1 | 2011-08-19 17:21:51.918969+03 | 2011-08-19 17:21:55.192393+03 | 2011-08-19 17:21:55.192393+03 | f | SELECT * from pg_stat_activity;
(2 rows)

Вариант2

postgres=# select now() - query_start as duration,* from pg_stat_activity order by duration desc

Вариант3

# ps axu | grep postgres
pgsql 33916 0.0 3.6 48820 8988 ?? Is Tue08PM 0:00.04 postgres: postfixadmin postfixadmin 127.0.0.1(44414) (postgres)
pgsql 99281 0.0 2.0 47796 4948 ?? Ss Tue07PM 0:11.81 /usr/local/bin/postgres -D /usr/local/pgsql
pgsql 99283 0.0 2.9 47796 7124 ?? Ss Tue07PM 0:32.19 postgres: writer process (postgres)
pgsql 99284 0.0 2.0 47796 4964 ?? Ss Tue07PM 0:38.46 postgres: wal writer process (postgres)
pgsql 99285 0.0 2.2 48820 5548 ?? Ss Tue07PM 0:14.67 postgres: autovacuum launcher process (postgres)
pgsql 99286 0.0 1.8 33596 4408 ?? Ss Tue07PM 0:11.54 postgres: stats collector process (postgres)

7) Просмотр привилегий

mysql

mysql> show grants for 'user'@'host';

postgresql

postgres=# \du

postgres=# SELECT table_catalog, table_schema, table_name, privilege_type
FROM information_schema.table_privileges WHERE grantee = 'MY_USER'

8 ) Просмотр встроенной справки

— общая

mysql> help
postgres=#\?

— по командам

mysql> help <command>
postgres=#\h <command>

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *