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

Содержание[Скрыть]

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

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 > 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 FROM 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 FROM 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, задействуют индексы или нет

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(*) > 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 > 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
> 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    allall        0.0.0.0/0password

# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD

host bobdb bob 212.117.81.42 255.255.255.255 password

host allall 0.0.0.0/0password

3.2 Backup

Backup и restore

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

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

# psql dbname 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

1	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  full.dump # Все базы
# mysql -u root -psecret

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

# mysql -u root -psecret -D dbname full.dump # Все базы

# mysql -u root -psecret

В данном случае «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; # вывод плана и статистики без выполнения запроса

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

2 SQL (MySQL SQL)

2.1 SHOW и DESC

2.2 SELECT и operators

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

2.3 DELETE

2.4 INSERT; UPDATE; SOURCE

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

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

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

2.5 CREATE

2.6 EXPLAIN

2.7 ALTER

2.8 Псевдонимы

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

2.10 Переменные

2.11 Транзакции

3 PostgreSQL

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

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

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

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

3.2 Backup

Backup и restore

4 MySQL

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

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

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

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

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

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

4.2 Backup

Backup и restore

5 Oracle DB

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

Ваш комментарий будет первым

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

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

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

2 SQL (MySQL SQL)

2.1 SHOW и DESC

2.2 SELECT и operators

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

2.3 DELETE

2.4 INSERT; UPDATE; SOURCE

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

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

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

2.5 CREATE

2.6 EXPLAIN

2.7 ALTER

2.8 Псевдонимы

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

2.10 Переменные

2.11 Транзакции

3 PostgreSQL

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

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

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

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

3.2 Backup

Backup и restore

4 MySQL

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

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

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

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

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

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

4.2 Backup

Backup и restore

5 Oracle DB

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

﻿Ваш комментарий будет первым

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

Ваш комментарий будет первым