29 июля 2019

Немного о базах данных

Содержимое

0.1 1 Общая информация
0.2 2 SQL (MySQL SQL)
0.3 3 PostgreSQL
0.4 3.2 Backup
- 0.4.1 Backup и restore

1 4 MySQL
2 5 Oracle DB
- 2.1 5.1 Все одним списком (систематизирую позже)

1 Общая информация

Типы таблиц:
Транзакционные(TST): innodb, bdb
Нетранзакционнные(NTST): myisam, merge, memory

2 SQL (MySQL SQL)

SQL синтаксис на примере MySQL, может различаться в зависимости от БД

2.1 SHOW и DESC

SHOW DATABASES;               # смотрим БД к которым есть доступ
SHOW create database db_name; # смотрим инстркцию для создания БД db_name
SHOW COLUMNS FROM table_name; # смотрим столбцы в таблице
SHOW CREATE TABLE table_name; # инструкция для создания table_name
SHOW CHARACTER SET;           # описание кодировок
SHOW VARIABLES LIKE 'c%';     # текущие переменные на сервере
SHOW INDEX FROM table_name;   # смотрим индексы по таблице
SHOW TABLES;                  # таблицы в БД
SHOW COLUMNS FROM table;      # смотрим информацию по колонкам в таблице
DESC TABLES;                  # смотрим информацию по колонкам в таблице

SHOW DATABASES; # смотрим БД к которым есть доступ

SHOW create database db_name; # смотрим инстркцию для создания БД db_name

SHOW COLUMNS FROM table_name; # смотрим столбцы в таблице

SHOW CREATE TABLE table_name; # инструкция для создания table_name

SHOW CHARACTER SET; # описание кодировок

SHOW VARIABLES LIKE 'c%'; # текущие переменные на сервере

SHOW INDEX FROM table_name; # смотрим индексы по таблице

SHOW TABLES; # таблицы в БД

SHOW COLUMNS FROM table; # смотрим информацию по колонкам в таблице

DESC TABLES; # смотрим информацию по колонкам в таблице

2.2 SELECT и operators

SELECT DATABASE();                        # выбираем бд
SELECT * from table;                      # смотрим таблицу
SELECT column_name, column_id from table;
SELECT * FROM bd.table WHERE table_name = "my name";
SELECT * FROM bool WHERE bit IS NOT NULL;
SELECT column_name FROM table where column_info &lt; 5;         # [=,!=,&gt;=,&lt;=,&gt;,&lt;]
SELECT column_name FROM table_name WHERE name LIKE "test% nomer__" OR name LIKE "test%";
#LIKE - только со строками по шаблону %,_обобщение одного символа 
SELECT * FROM table WHERE id &gt; 2 AND NOT (ID = 4 or ID = 6); # ()-объединение, OR, NOT, AND ,NOT LIKE

select SUM(column) RFOM bd.table WHERE table_name = "my name"; # SUM(column) - суммируем значения поля column
select MAX(column_id) from table;                    # MAX() - максимальное значение
select * FROM table ORDER BY column_name;            # сортировка в алфавитном парядке по полю column_name
select * FROM table ORDER BY column_name, column_oth;# сортировка по двум полям
SELECT * FROM table ORDER BY column_name DESC; # сортировка в порядке убывания
                                               # BINARY - учитывать регистр

SELECT column FROM table_name ORDER BY CAST(table AS CHAR);

SELECT DATABASE(); # выбираем бд

SELECT * from table; # смотрим таблицу

SELECT column_name, column_id from table;

SELECT * FROM bd.table WHERE table_name = "my name";

SELECT * FROM bool WHERE bit IS NOT NULL;

SELECT column_name FROM table where column_info < 5; # [=,!=,>=,<=,>,<]

SELECT column_name FROM table_name WHERE name LIKE "test% nomer__" OR name LIKE "test%";

#LIKE - только со строками по шаблону %,_обобщение одного символа

SELECT * FROM table WHERE id > 2 AND NOT (ID = 4 or ID = 6); # ()-объединение, OR, NOT, AND ,NOT LIKE

select SUM(column) RFOM bd.table WHERE table_name = "my name"; # SUM(column) - суммируем значения поля column

select MAX(column_id) from table; # MAX() - максимальное значение

select * FROM table ORDER BY column_name; # сортировка в алфавитном парядке по полю column_name

select * FROM table ORDER BY column_name, column_oth;# сортировка по двум полям

SELECT * FROM table ORDER BY column_name DESC; # сортировка в порядке убывания

# BINARY - учитывать регистр

SELECT column FROM table_name ORDER BY CAST(table AS CHAR);

AS BINARY — сортировка по двоичным кодам
AS SIGNED — целые числа со знаками
AS UNSIGNED — сортировка целых чисел без знака
AS CHAR — сортировать по строкам
AS DATE — по дате
AS DATETIME — сортировка по дате и времени
AS TIME — сортировка по времени

SELECT column FROM table_name LIMIT 5, 5; # LIMIT 5 OFFSET 5 - выводим 5 строк начиная с 5й строки
select version();
SELECT column_name, column_name2 FROM table_name1 INNER JOIN table_name2 USING (column_name_common_id);
  # выборка из 2х таблиц
  # INNER JOIN - оператор разделяющий 2 таблицы
  # USING - столбец или столбцы для связывания
SELECT column_name, column_name2 FROM table_name1 INNER JOIN table_name2 USING (column_name_common_id,column_name_common_id2)
                    ORDER BY column_name_common_id2 LIMIT 15;
  # отсортированный вывод по 15 строк из 2х таблиц объединенных по 2м колонкам

SELECT column FROM table_name LIMIT 5, 5; # LIMIT 5 OFFSET 5 - выводим 5 строк начиная с 5й строки

select version();

SELECT column_name, column_name2 FROM table_name1 INNER JOIN table_name2 USING (column_name_common_id);

# выборка из 2х таблиц

# INNER JOIN - оператор разделяющий 2 таблицы

# USING - столбец или столбцы для связывания

SELECT column_name, column_name2 FROM table_name1 INNER JOIN table_name2 USING (column_name_common_id,column_name_common_id2)

ORDER BY column_name_common_id2 LIMIT 15;

# отсортированный вывод по 15 строк из 2х таблиц объединенных по 2м колонкам

Вложеные запросы

SELECT column1 from table1 WHERE name = (SELECT column1 FROM table2 WHERE name = "zabrosov" );
SELECT column1 from table1 WHERE name = (SELECT MAX(column1) FROM table2  );# можно использовать ANY, ALL, IN, IN NOT
SELECT name1 FROM men1 WHERE years &gt; ANY (SELECT years FROM players );
SELECT name1 FROM men1 WHERE name1 IN (SELECT name FROM bad_players_name ); # определим слабое звено из другой таблицы
SELECT name1 FROM men1 WHERE EXIST (SELECT * FROM players WHERE status = "good" ); # есть ли у нас настоящие игроки?
SELECT name, months FROM (SELECT name, years*12 AS months FROM players) AS good_players; # запрос к FROM; обратить внимание на алиасы!

SELECT column1 from table1 WHERE name = (SELECT column1 FROM table2 WHERE name = "zabrosov" );

SELECT column1 from table1 WHERE name = (SELECT MAX(column1) FROM table2 );# можно использовать ANY, ALL, IN, IN NOT

SELECT name1 FROM men1 WHERE years > ANY (SELECT years FROM players );

SELECT name1 FROM men1 WHERE name1 IN (SELECT name FROM bad_players_name ); # определим слабое звено из другой таблицы

SELECT name1 FROM men1 WHERE EXIST (SELECT * FROM players WHERE status = "good" ); # есть ли у нас настоящие игроки?

SELECT name, months FROM (SELECT name, years*12 AS months FROM players) AS good_players; # запрос к FROM; обратить внимание на алиасы!

2.3 DELETE

DELETE FROM table_name;                                # удаляем таблицу
DELETE FROM table_name order by column_name LIMIT 200; # удаляем первые 200 строк после сортировки в таблице
TRUNCATE TABLE table_name;                             # быстрое удаление таблицы (сброс таблицы)

DELETE column_players FROM table_players WHERE NOT EXISTS 
       (SELECT * FROM table_results WHERE table_players.player_id = table_results.player_id); 
       # удаляем строки в таблице  table_players  с игроками которые не играли (не существует записей в table_results )

DELETE FROM table_name; # удаляем таблицу

DELETE FROM table_name order by column_name LIMIT 200; # удаляем первые 200 строк после сортировки в таблице

TRUNCATE TABLE table_name; # быстрое удаление таблицы (сброс таблицы)

DELETE column_players FROM table_players WHERE NOT EXISTS

(SELECT * FROM table_results WHERE table_players.player_id = table_results.player_id);

# удаляем строки в таблице table_players с игроками которые не играли (не существует записей в table_results )

2.4 INSERT; UPDATE; SOURCE

INSERT INTO table () VALUES (); #дабавляем строку со всеми значениями по умолчанию
INSERT INTO table (column_name1, column_name2, column_name3) VALUES (7,1,2)(8,2,DEFAULT);
       #групповая всавка в таблицу, DEFAULT - использование значения по умолчанию
INSERT INTO table_name SET column_name1 = 7, column_name2 = 1, column_name3 = DEFAULT;
       #вставка где можно указать имя столбца вместе со значением

INSERT INTO table () VALUES (); #дабавляем строку со всеми значениями по умолчанию

INSERT INTO table (column_name1, column_name2, column_name3) VALUES (7,1,2)(8,2,DEFAULT);

#групповая всавка в таблицу, DEFAULT - использование значения по умолчанию

INSERT INTO table_name SET column_name1 = 7, column_name2 = 1, column_name3 = DEFAULT;

#вставка где можно указать имя столбца вместе со значением

Вставка данных с помощью запросов

INSERT INTO table1 (id, player, score) SELECT id, player, score FRPM players OREDR BY RAND(); # вставляем случайную выборку
INSERT IGNORE INTO table1 (id, player, score) SELECT id, player, 10*score FRPM players LIMIT 1; 
       # IGNORE - игнорирование дубликатов,score умножили на 10

LOAD DATA INFILE '/file/path/data.cvs' INTO TABLE table_data FIELDS TERMINATED BY ','; # загрузка данных из файла с разделителем ',' 
SELECT id, player, 10*score FRPM players INTO OUTFILE /file/path/data.cvs 'FIELDS TERMINATED BY ','; # выгрузка в файл

INSERT INTO table1 (id, player, score) SELECT id, player, score FRPM players OREDR BY RAND(); # вставляем случайную выборку

INSERT IGNORE INTO table1 (id, player, score) SELECT id, player, 10*score FRPM players LIMIT 1;

# IGNORE - игнорирование дубликатов,score умножили на 10

LOAD DATA INFILE '/file/path/data.cvs' INTO TABLE table_data FIELDS TERMINATED BY ','; # загрузка данных из файла с разделителем ','

SELECT id, player, 10*score FRPM players INTO OUTFILE /file/path/data.cvs 'FIELDS TERMINATED BY ','; # выгрузка в файл

UPDATE — обновление существующих данных в таблицах

UPDATE table_name SET column_name = UPPER(column_name); #обновление данных в таблице
                                                        #LOWER() UPPER() - обновление в нижних регистр и в верхний регистр
UPDATE table_name SET table_name = NULL;
UPDATE table_name SET name = "zabrosov" WHERE name = "petr" AND surname = "anon";# обновляем поля name с условием
UPDATE table_name SET column_name = NULL ORDER BY column_name DESC LIMIT 10;     # обновляем последнии 10 записей на дефолтные значения

UPDATE table_player INNER JOIN table_results USING(player_id) INNER JOIN table_scedule (player_id, team_id) SET name = UPPER(name); 
       # обновление данных
REPLACE INTO table_player VALUES (1, "zabrosov", 100); 
       # REPLACE - перезаписывает строку если ервичный ключ совпал или создает новую как INSERT

UPDATE table_name SET column_name = UPPER(column_name); #обновление данных в таблице

#LOWER() UPPER() - обновление в нижних регистр и в верхний регистр

UPDATE table_name SET table_name = NULL;

UPDATE table_name SET name = "zabrosov" WHERE name = "petr" AND surname = "anon";# обновляем поля name с условием

UPDATE table_name SET column_name = NULL ORDER BY column_name DESC LIMIT 10; # обновляем последнии 10 записей на дефолтные значения

UPDATE table_player INNER JOIN table_results USING(player_id) INNER JOIN table_scedule (player_id, team_id) SET name = UPPER(name);

# обновление данных

REPLACE INTO table_player VALUES (1, "zabrosov", 100);

# REPLACE - перезаписывает строку если ервичный ключ совпал или создает новую как INSERT

SOURCE — пакетное выполнение команд из файла

SOURCE /file/path.sql;# пакетное выполнение команд из файла

1	SOURCE /file/path.sql;# пакетное выполнение команд из файла

2.5 CREATE

Создаем базу данных

DROP DATABASE db_name;
CREATE DATABASE IF NOT EXIST db_name;
USE db_name;
CREATE TABLE table_name (
column_name_id SMALLINT(5) NOT NULL DEFAULT 0,
column_name CHAR(128) DEFAULT NULL,
PRIMARY KEY (column_name_id)
);

RENAME DATABASE db_name db_name_new; # переименовали БД

CREATE TABLE table_name (
id  INT(4) NOT NULL DEFAULT 0,
firstname CHAR(50),
surname CHAR(50),
PRIMARY KEY (id),
KEY names (firstname, surname)); # KEY - создем индекс первичного ключа, name индекс по 2м столбцам

CREATE TABLE table_name (
id  INT(4) NOT NULL AUTO_INCREMENT,
firstname CHAR(50),
surname CHAR(50),
PRIMARY KEY (id),
KEY names (firstname, surname)); # используем автоинкремент для поля первичного ключа

DROP DATABASE db_name;

CREATE DATABASE IF NOT EXIST db_name;

USE db_name;

CREATE TABLE table_name (

column_name_id SMALLINT(5) NOT NULL DEFAULT 0,

column_name CHAR(128) DEFAULT NULL,

PRIMARY KEY (column_name_id)

);

RENAME DATABASE db_name db_name_new; # переименовали БД

CREATE TABLE table_name (

id INT(4) NOT NULL DEFAULT 0,

firstname CHAR(50),

surname CHAR(50),

PRIMARY KEY (id),

KEY names (firstname, surname)); # KEY - создем индекс первичного ключа, name индекс по 2м столбцам

CREATE TABLE table_name (

id INT(4) NOT NULL AUTO_INCREMENT,

firstname CHAR(50),

surname CHAR(50),

PRIMARY KEY (id),

KEY names (firstname, surname)); # используем автоинкремент для поля первичного ключа

CREATE TABLE player_table LIKE player; # создаем таблицу без данных по подобию player
CREATE TABLE player_table SELECT * FROM player; # создаем таблицу с данными

CREATE TABLE player_table (id SMALLINT(5) NOT NULL AUTO_INCREMENT, 
       player CHAR(128) DEFAULT "zabrosov", 
       PROMARY KEY(id)) SELECT * FROM player; # создали таблицу с преобразованием структуры

CREATE TABLE player_table LIKE player; # создаем таблицу без данных по подобию player

CREATE TABLE player_table SELECT * FROM player; # создаем таблицу с данными

CREATE TABLE player_table (id SMALLINT(5) NOT NULL AUTO_INCREMENT,

player CHAR(128) DEFAULT "zabrosov",

PROMARY KEY(id)) SELECT * FROM player; # создали таблицу с преобразованием структуры

2.6 EXPLAIN

EXPLAIN — план построения запроса к базе

EXPLAIN SELECT * FROM table_name WHERE firstname; 
   #EXPLAIN проверяем статистику как строится statement, задействуют индексы или нет

1 2	EXPLAIN SELECT * FROM table_name WHERE firstname; #EXPLAIN проверяем статистику как строится statement, задействуют индексы или нет

2.7 ALTER

ALTER — изменение структуры таблиц

ALTER TABLE table_name ADD column_name YEAR FIRST; 
    #добавляем колонку column_name в таблицу в качестве первого стобца (FIRST), YEAR - тип данных
ALTER TABLE table_name ADD column_name YEAR AFTER id; 
    #вставляем столбец в спец место (в примере, после колонки id)
ALTER TABLE table_name DROP column_name; #удаляем столбец
ALTER TABLE table_name ADD INDEX by_column_name (column_name); #добавляем индекс by_column_name после создания таблицы
ALTER TABLE table_name ADD INDEX by_column_name (column_name(10)); #индекс только по 10 символам column_name поля
ALTER TABLE table_name ADD PRIMARY KEY (column_name); #первичный ключ для таблицы после создания
ALTER TABLE table_name DROP INDEX by_column_name; #удаляем индекс
ALTER TABLE table_name DROP PRIMARY KEY; #сброс индекс первичного ключа
ALTER TABLE table_name RENAME TO table_name_new; #переименование таблицы

ALTER TABLE table_name ADD column_name YEAR FIRST;

#добавляем колонку column_name в таблицу в качестве первого стобца (FIRST), YEAR - тип данных

ALTER TABLE table_name ADD column_name YEAR AFTER id;

#вставляем столбец в спец место (в примере, после колонки id)

ALTER TABLE table_name DROP column_name; #удаляем столбец

ALTER TABLE table_name ADD INDEX by_column_name (column_name); #добавляем индекс by_column_name после создания таблицы

ALTER TABLE table_name ADD INDEX by_column_name (column_name(10)); #индекс только по 10 символам column_name поля

ALTER TABLE table_name ADD PRIMARY KEY (column_name); #первичный ключ для таблицы после создания

ALTER TABLE table_name DROP INDEX by_column_name; #удаляем индекс

ALTER TABLE table_name DROP PRIMARY KEY; #сброс индекс первичного ключа

ALTER TABLE table_name RENAME TO table_name_new; #переименование таблицы

2.8 Псевдонимы

SELECT column AS cl FROM table_name; # cl алиас к column
SELECT column cl FROM table_name; #cl алиас к column
SELECT t1.column_name,t2.column.name FROM table_name1 t1 INNER JOIN table_name t2 USING(column_common_id) WHERE t1.column_name="test__";

SELECT column AS cl FROM table_name; # cl алиас к column

SELECT column cl FROM table_name; #cl алиас к column

SELECT t1.column_name,t2.column.name FROM table_name1 t1 INNER JOIN table_name t2 USING(column_common_id) WHERE t1.column_name="test__";

2.9 Доп. операции

SELECT CONTACT(column_name, " thesame ", column_name2) AS column FROM table_name1 INNER JOIN table_name2 USING(column_id) ORDER BY column;
   #CONTACT() - объединение строк являющихся параметрами с последующим выводом с сортировкой по псевдониму из 2х таблиц;

SELECT DISTINCT column_name, column_name2 FROM table_name1 INNER JOIN table_name2 USING (column_name_common_id);
   # DISTINCT - убираем дубликаты из вывода
SELECT DISTINCT column_name1, COUNT(column_name1) FROM table_name1 INNER JOIN table_name2 USING (column_name_common_id) GROUP BY column_name1 ; 
   #GROUP BY группировка подобных, COUNT() - счетчик строк;

SELECT AVG(cost) FROM table_prices GROUP BY shop; 
# AVG()-среднее значение всех строк в группе (GROUP BY shop) для указанного столбца
# MAX()- возвращает макс значение для строк в группе
# MIN()- минимальное знаечение
# STD(),STDDEV() - отклонение, разброс значений строк
# SUM() - сумма значений строк
# RAND() - генератор псевдослучайных чисел

SELECT column1, column2, COUNT(*) FROM table1 INNER JOIN table2 USING(column_id) INNER JOIN table3 USING(column_id, table3_column_id)
GROUP BY table2.column_id, table3.table3_column_id HAVING COUNT(*) &gt; 10;
  # HAVING() - доп агрегация, аргумент должен быть в SELECT, тут это COUNT()

SELECT column1, column2 FROM table1 INNER JOIN table2 USING(column_id);# тоже но с where
SELECT column1, column2 FROM table1,table2 WHERE table1.column_id=table2.column_id;

SELECT column1 FROM table1 UNION (SELECT column2 FROM table2 UNION SELECT column3 FROM table3);
#UNION объединение резудьтатов разных запросов
#колво столбцов должно быть одинаковое, именование столбцов берем из первого запроса, сопоставленные столбцы должны иметь один и тот же тип

SELECT CONTACT(column_name, " thesame ", column_name2) AS column FROM table_name1 INNER JOIN table_name2 USING(column_id) ORDER BY column;

#CONTACT() - объединение строк являющихся параметрами с последующим выводом с сортировкой по псевдониму из 2х таблиц;

SELECT DISTINCT column_name, column_name2 FROM table_name1 INNER JOIN table_name2 USING (column_name_common_id);

# DISTINCT - убираем дубликаты из вывода

SELECT DISTINCT column_name1, COUNT(column_name1) FROM table_name1 INNER JOIN table_name2 USING (column_name_common_id) GROUP BY column_name1 ;

#GROUP BY группировка подобных, COUNT() - счетчик строк;

SELECT AVG(cost) FROM table_prices GROUP BY shop;

# AVG()-среднее значение всех строк в группе (GROUP BY shop) для указанного столбца

# MAX()- возвращает макс значение для строк в группе

# MIN()- минимальное знаечение

# STD(),STDDEV() - отклонение, разброс значений строк

# SUM() - сумма значений строк

# RAND() - генератор псевдослучайных чисел

SELECT column1, column2, COUNT(*) FROM table1 INNER JOIN table2 USING(column_id) INNER JOIN table3 USING(column_id, table3_column_id)

GROUP BY table2.column_id, table3.table3_column_id HAVING COUNT(*) > 10;

# HAVING() - доп агрегация, аргумент должен быть в SELECT, тут это COUNT()

SELECT column1, column2 FROM table1 INNER JOIN table2 USING(column_id);# тоже но с where

SELECT column1, column2 FROM table1,table2 WHERE table1.column_id=table2.column_id;

SELECT column1 FROM table1 UNION (SELECT column2 FROM table2 UNION SELECT column3 FROM table3);

#UNION объединение резудьтатов разных запросов

#колво столбцов должно быть одинаковое, именование столбцов берем из первого запроса, сопоставленные столбцы должны иметь один и тот же тип

INNER JOIN — вывод только строк имеющихся в обоих таблицах по общему полю
LEFT JOIN, RIGHT JOIN — вывод всех строк в сравнении с левой или правой таблицей, и результат, если нет то null

SELECT column1, column2 FROM table1 RIGHT JOIN table2 ON table1.column_id = table2.column_id; или USING(column_id);

1	SELECT column1, column2 FROM table1 RIGHT JOIN table2 ON table1.column_id = table2.column_id; или USING(column_id);

2.10 Переменные

SELECT @player_name:=name FROM player WHERE player_score &gt; 100;# @ обозначение переменной player_name,:= присвоение значения
SELECT @player_name;                                           # получение результата
SET @player_name:="zabrosov";                                  # запомним значение
SELECT name FROM player WHERE player_name = @player_name;      # ищем себя любимого

SELECT @player_name:=name FROM player WHERE player_score > 100;# @ обозначение переменной player_name,:= присвоение значения

SELECT @player_name; # получение результата

SET @player_name:="zabrosov"; # запомним значение

SELECT name FROM player WHERE player_name = @player_name; # ищем себя любимого

2.11 Транзакции

mySQL работает в режиме AUTOCOMMIT по умолчанию

SHOW TABLE STATUS;                 # тех информация о хранении и типе страниц
SHOW ENGINES;                      # смотрим типы хранилищ
ALTER TABLE players TYPE = InnoDB; # задаем или меняем тип хранилища
START TRANSACTION;                 #открыли транзакцию
INSERT INTO players VALUES (1, "zabrosov");
COMMIT;                            # закрыли транзакцию
ROLBACK;                           # откат транзакции

SHOW TABLE STATUS; # тех информация о хранении и типе страниц

SHOW ENGINES; # смотрим типы хранилищ

ALTER TABLE players TYPE = InnoDB; # задаем или меняем тип хранилища

START TRANSACTION; #открыли транзакцию

INSERT INTO players VALUES (1, "zabrosov");

COMMIT; # закрыли транзакцию

ROLBACK; # откат транзакции

3 PostgreSQL

3.1 Пользователи

Смена пароля root или пароля пользователя

# psql -d template1 -U username
&gt; alter user username with password 'username_password';

1 2	# psql -d template1 -U username > alter user username with password 'username_password';

Создание пользователя и базы данных

# createuser -U pgsql -P bob         # Создание пользователя bob,-P для ввода пароля
# createdb -U pgsql -O bob bobdb     # Создать базу данных bobdb, владелец bob
# dropdb bobdb                       # Удалить базу дфнных bobdb
# dropuser bob                       # Удалить пользователя bob

# createuser -U pgsql -P bob # Создание пользователя bob,-P для ввода пароля

# createdb -U pgsql -O bob bobdb # Создать базу данных bobdb, владелец bob

# dropdb bobdb # Удалить базу дфнных bobdb

# dropuser bob # Удалить пользователя bob

Механизм авторизации в базе, настраивается в файле pg_hba.conf

Разрешить удаленный доступ к базе данных

Файл $PGSQL_DATA_D/postgresql.conf определяет слушающие адреса. Обычно listen_addresses = ‘*’ для Postgres 8.x

Файл $PGSQL_DATA_D/pg_hba.conf назначает уровни доступа.

# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK          METHOD
host    bobdb       bob        212.117.81.42     255.255.255.255   password
host    all         all        0.0.0.0/0                           password

# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD

host bobdb bob 212.117.81.42 255.255.255.255 password

host all all 0.0.0.0/0 password

3.2 Backup

Backup и restore

# pg_dump --clean dbname > dbname_sql.dump  # Резервное копирование и восстановление одной базы данных
# psql dbname < dbname_sql.dump             # Делается пользователем pgsql или postgres

# pg_dumpall --clean > full.dump # Резервное копирование и восстановление всех баз(включая пользователей)
# psql -f full.dump postgres

# pg_dump --clean dbname > dbname_sql.dump # Резервное копирование и восстановление одной базы данных

# psql dbname < dbname_sql.dump # Делается пользователем pgsql или postgres

# pg_dumpall --clean > full.dump # Резервное копирование и восстановление всех баз(включая пользователей)

# psql -f full.dump postgres

4 MySQL

Типы хранилищ:

MyISAM: блокировка всей таблицы, опимизация данных для быстрого доступа на диске, быстрый доступ к таблицам
Memmory: хранение в памяти, после останова нужно загружать данные, не поддерживется типы TEXT BLOB
InnoDB: транзакции, расширенное восстановление, блокировка строк, внешний ключ, индексирование

MySQL работает в режиме AUTOCOMMIT по умолчанию

Команды сервера БД:

\W — показывать предупреждения после каждого утверждения
\w — отключить предупреждения
\! — выполнить команду из sys shell
\s — статус сервера
\. — выполнить sql файл (source)
\R — изменить sql promt
\n — вывод в stdout
\d — вывод информации по разделителю, дефолт построчно
\G — форматирование вывода, SELECT column_name FROM table where column_info < 5;\G

4.1 Пользователи

Смена пароля root или пароля пользователя

# способ 1
# /etc/init.d/mysql stop
# mysqld --skip-grant-tables
# mysqladmin -u root password 'newpasswd'
# /etc/init.d/mysql start
# способ 2
# mysql -u root mysql
mysql> UPDATE USER SET PASSWORD=PASSWORD("newpassword") where user='root'; # Имя "пользователя" вместо "root"
mysql> FLUSH PRIVILEGES;                           
mysql> quit

# способ 1

# /etc/init.d/mysql stop

# mysqld --skip-grant-tables

# mysqladmin -u root password 'newpasswd'

# /etc/init.d/mysql start

# способ 2

# mysql -u root mysql

mysql> UPDATE USER SET PASSWORD=PASSWORD("newpassword") where user='root'; # Имя "пользователя" вместо "root"

mysql> FLUSH PRIVILEGES;

mysql> quit

Создаём пользователя и базу данных

# mysql -u root mysql
mysql> CREATE DATABASE bobdb;                      # Cоздать базу данных bobdb
mysql> GRANT ALL ON *.* TO 'bob'@'%' IDENTIFIED BY 'pwd'; 
# Используйте localhost вместо % что-бы запретить доступ к базе данных извне
# % - Разрешает сетевой доступ пользователя с любого IP адреса
mysql> DROP DATABASE bobdb;                        # Удалмть базу данных
mysql> DROP USER bob;                              # Удалить пользователя bob
mysql> DELETE FROM mysql.user WHERE user='bob and host='hostname'; # Аналог
mysql> FLUSH PRIVILEGES;

# mysql -u root mysql

mysql> CREATE DATABASE bobdb; # Cоздать базу данных bobdb

mysql> GRANT ALL ON *.* TO 'bob'@'%' IDENTIFIED BY 'pwd';

# Используйте localhost вместо % что-бы запретить доступ к базе данных извне

# % - Разрешает сетевой доступ пользователя с любого IP адреса

mysql> DROP DATABASE bobdb; # Удалмть базу данных

mysql> DROP USER bob; # Удалить пользователя bob

mysql> DELETE FROM mysql.user WHERE user='bob and host='hostname'; # Аналог

mysql> FLUSH PRIVILEGES;

Разрешить удаленный доступ

Обычно удаленный доступ разрешен не ко всем базам данных. В файл /etc/my.cnf прописан адрес для слушающего сокета, как правило достаточно раскомментировать строку bind-address = out

# mysql -u root mysql
mysql> GRANT ALL ON bobdb.* TO bob@'xxx.xxx.xxx.xxx' IDENTIFIED BY 'PASSWORD';
mysql> REVOKE GRANT OPTION ON foo.* FROM bar@'xxx.xxx.xxx.xxx'; # Используем 'hostname' или '%' для всех
mysql> FLUSH PRIVILEGES;

# mysql -u root mysql

mysql> GRANT ALL ON bobdb.* TO bob@'xxx.xxx.xxx.xxx' IDENTIFIED BY 'PASSWORD';

mysql> REVOKE GRANT OPTION ON foo.* FROM bar@'xxx.xxx.xxx.xxx'; # Используем 'hostname' или '%' для всех

mysql> FLUSH PRIVILEGES;

4.2 Backup

Backup и restore

# mysqldump -u root -psecret --add-drop-database dbname > dbname_sql.dump # Одна база
# mysql -u root -psecret -D dbname < dbname_sql.dump

# mysqldump -u root -psecret --add-drop-database --all-databases > full.dump # Все базы
# mysql -u root -psecret < full.dump

# mysqldump -u root -psecret --add-drop-database dbname > dbname_sql.dump # Одна база

# mysql -u root -psecret -D dbname < dbname_sql.dump

# mysqldump -u root -psecret --add-drop-database --all-databases > full.dump # Все базы

# mysql -u root -psecret < full.dump

В данном случае «secret», пароль пользователя root для mysql, после опции -p, пробел не ставится. Если опция -p будет использована без следеющего за ней пароля, он будет запрошен интерактивно.

5 Oracle DB

5.1 Все одним списком (систематизирую позже)

select name,value from v$parameter where name like %diag%; # узнаем о местоположении диагностического каталога
alter system set diagnostic_name="/new/path" scope = both; # переносим каталог и немедленно применяем изменения с сохранением в sfile

1 2	select name,value from v$parameter where name like %diag%; # узнаем о местоположении диагностического каталога alter system set diagnostic_name="/new/path" scope = both; # переносим каталог и немедленно применяем изменения с сохранением в sfile

режимы работы базы — shutdown, nomount, mount, open
переход между режимами — startup mount
cretae pfaile
reset admin pass

recreate sfile: startup nomount pfile="path/path/to/file"
create sfile from pfile="path/to/pfile"
startup nomount force;
create pfile from spfile;

turn off login without pass - 

memory_target param and memoty_max_target;                           # изменния при работе с памятью
select name,value from v$parameter where name like 'control_failes'; # узнаем о местоположении диагностического каталога
alter system set contro_files="/patho/to/disk1", "patch/to/disc2", "path/to/disc3" scope=spfile; # контрол файлы храним на разных дисках!
alter database backup controlfiles to trace;                         # бекапим в журнал вывод контрол файлов

# @ - выпоняем внешний sql/etc скрипт из под sqlplus

alter database mount;                                                # монтируем БД
alter databse open;                                                  # переводим БД в режим опен

recovery database;                                                   # sync scn
select * from dual;        # спец таблица с одной записью - менять нельзя, сломается вся логика
                           # копировать control файлы можно только после shutdown

recreate sfile: startup nomount pfile="path/path/to/file"

create sfile from pfile="path/to/pfile"

startup nomount force;

create pfile from spfile;

turn off login without pass -

memory_target param and memoty_max_target; # изменния при работе с памятью

select name,value from v$parameter where name like 'control_failes'; # узнаем о местоположении диагностического каталога

alter system set contro_files="/patho/to/disk1", "patch/to/disc2", "path/to/disc3" scope=spfile; # контрол файлы храним на разных дисках!

alter database backup controlfiles to trace; # бекапим в журнал вывод контрол файлов

# @ - выпоняем внешний sql/etc скрипт из под sqlplus

alter database mount; # монтируем БД

alter databse open; # переводим БД в режим опен

recovery database; # sync scn

select * from dual; # спец таблица с одной записью - менять нельзя, сломается вся логика

# копировать control файлы можно только после shutdown

lsnrctl — управление listner ($Home_ora/networl/admin/listener.ora) (status/stop/start)
netmgr —
tnsping —

select * from resource_view;                           # все ресурсы сервера
select * from v$sga;                                   # смотрим память

Select * from v$sql where sql_text like '%Mycomment%'; # поиск по комменту
alter system set cursor_sharing=similar scope=memory;  # шарим курсор - оптимизация шаред пулл меморри
show sga;                                              # смотрим состояние SGA памяти

create table b as select * from base where rownum=100; 
       # создаем таблицу из другой таблицы (будет 101 строка, 1 уйдет в юзерс)
select * from user_tables where table_name='b'; # смотрим состояние таблицы
alter table b move tablespace mytbs;            # перемещаем таблицу в другое табичное пространство

select * from user user_segments where segment_name='b'; # смотрим метаданные сегмента
select * from user user_extents where segment_name='B';  # смотрим метаданыые экстента

insert into b (select * from base);                      # создали новую таблицу на основе старой (копия);
alter database backup controlfaile to trace;             # сохранемт трасеровку контролфайла
select * from v$instance;                                # узнаем что инстенс базы работает - смотрим статус
select * from v$databse;                                 # статус базы, ошибка - база не смонтирована

select * from dictionary;                                # глобальный словарь - содержит все представления
select * from dictionary where lover(comments) like %transac%;
select * from dict_columns;                              # значение колонок - словарь
create global temporary table;                           # глобальное определение в словаре метаданных
alter system checkpoint;                                 # контрольный сброс на диск по времени
alter system set fast_start_mttr_target=60 scope=both;   # инкрементальный сброс (по статистике)

alter database clear logfile group 4;                    # очищаем редолог пространство\файлы в группе 4
select * from v$logfile;                                 # смотрим логфайлы
select * from v$log;                                     # состояние логиррования (active unused current)
alter system switch logfile;                             # переключение между логфайлами
alter system checkpoit;                                  # выполнение контрольной точки,сброс блоков на диск
select * from v$parameter where name = 'optimizer_mode'; # цель оптимизации в бд
set autotrace on;                                        # вывод статистики и плана выполнения запроса
set autotrace trace only;                                # вывод плана и статистики без выполнения запроса

select * from resource_view; # все ресурсы сервера

select * from v$sga; # смотрим память

Select * from v$sql where sql_text like '%Mycomment%'; # поиск по комменту

alter system set cursor_sharing=similar scope=memory; # шарим курсор - оптимизация шаред пулл меморри

show sga; # смотрим состояние SGA памяти

create table b as select * from base where rownum=100;

# создаем таблицу из другой таблицы (будет 101 строка, 1 уйдет в юзерс)

select * from user_tables where table_name='b'; # смотрим состояние таблицы

alter table b move tablespace mytbs; # перемещаем таблицу в другое табичное пространство

select * from user user_segments where segment_name='b'; # смотрим метаданные сегмента

select * from user user_extents where segment_name='B'; # смотрим метаданыые экстента

insert into b (select * from base); # создали новую таблицу на основе старой (копия);

alter database backup controlfaile to trace; # сохранемт трасеровку контролфайла

select * from v$instance; # узнаем что инстенс базы работает - смотрим статус

select * from v$databse; # статус базы, ошибка - база не смонтирована

select * from dictionary; # глобальный словарь - содержит все представления

select * from dictionary where lover(comments) like %transac%;

select * from dict_columns; # значение колонок - словарь

create global temporary table; # глобальное определение в словаре метаданных

alter system checkpoint; # контрольный сброс на диск по времени

alter system set fast_start_mttr_target=60 scope=both; # инкрементальный сброс (по статистике)

alter database clear logfile group 4; # очищаем редолог пространство\файлы в группе 4

select * from v$logfile; # смотрим логфайлы

select * from v$log; # состояние логиррования (active unused current)

alter system switch logfile; # переключение между логфайлами

alter system checkpoit; # выполнение контрольной точки,сброс блоков на диск

select * from v$parameter where name = 'optimizer_mode'; # цель оптимизации в бд

set autotrace on; # вывод статистики и плана выполнения запроса

set autotrace trace only; # вывод плана и статистики без выполнения запроса

Метки: SQL

Опубликовано 29.07.2019 от evgeniyalf в категории "Базы данных