Немного о базах данных
Содержимое
- 0.1 1 Общая информация
-
0.2 2 SQL (MySQL SQL)
- 0.2.1 2.1 SHOW и DESC
- 0.2.2 2.2 SELECT и operators
- 0.2.3 Вложеные запросы
- 0.2.4 2.3 DELETE
- 0.2.5 2.4 INSERT; UPDATE; SOURCE
- 0.2.6 Вставка данных с помощью запросов
- 0.2.7 UPDATE — обновление существующих данных в таблицах
- 0.2.8 SOURCE — пакетное выполнение команд из файла
- 0.2.9 2.5 CREATE
- 0.2.10 2.6 EXPLAIN
- 0.2.11 2.7 ALTER
- 0.2.12 2.8 Псевдонимы
- 0.2.13 2.9 Доп. операции
- 0.2.14 2.10 Переменные
- 0.2.15 2.11 Транзакции
- 0.3 3 PostgreSQL
- 0.4 3.2 Backup
- 1 4 MySQL
- 2 5 Oracle DB
1 Общая информация
Типы таблиц:
Транзакционные(TST): innodb, bdb
Нетранзакционнные(NTST): myisam, merge, memory
2 SQL (MySQL SQL)
SQL синтаксис на примере MySQL, может различаться в зависимости от БД
2.1 SHOW и DESC
1
2
3
4
5
6
7
8
9
10
|
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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 — сортировка по времени
1
2
3
4
5
6
7
8
9
|
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м колонкам
|
Вложеные запросы
1
2
3
4
5
6
|
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
1
2
3
4
5
6
7
|
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
1
2
3
4
5
|
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;
#вставка где можно указать имя столбца вместе со значением
|
Вставка данных с помощью запросов
1
2
3
4
5
6
|
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 — обновление существующих данных в таблицах
1
2
3
4
5
6
7
8
9
10
|
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 — пакетное выполнение команд из файла
1
|
SOURCE /file/path.sql;# пакетное выполнение команд из файла
|
2.5 CREATE
Создаем базу данных
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
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)); # используем автоинкремент для поля первичного ключа
|
1
2
3
4
5
6
|
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 — план построения запроса к базе
1
2
|
EXPLAIN SELECT * FROM table_name WHERE firstname;
#EXPLAIN проверяем статистику как строится statement, задействуют индексы или нет
|
2.7 ALTER
ALTER — изменение структуры таблиц
1
2
3
4
5
6
7
8
9
10
11
|
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 Псевдонимы
1
2
3
|
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 Доп. операции
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
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
1
|
SELECT column1, column2 FROM table1 RIGHT JOIN table2 ON table1.column_id = table2.column_id; или USING(column_id);
|
2.10 Переменные
1
2
3
4
|
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 по умолчанию
1
2
3
4
5
6
7
|
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 или пароля пользователя
1
2
|
# psql -d template1 -U username
> alter user username with password 'username_password';
|
Создание пользователя и базы данных
1
2
3
4
|
# 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 назначает уровни доступа.
1
2
3
|
# 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
1
2
3
4
5
|
# 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
2
3
4
5
6
7
8
9
10
|
# способ 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
2
3
4
5
6
7
8
9
|
# 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
1
2
3
4
|
# 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
1
2
3
4
5
|
# 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 Все одним списком (систематизирую позже)
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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 —
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
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; # вывод плана и статистики без выполнения запроса
|