Заметки и напоминалки о базах данных, SQL и связанных с ними процессах.
С удовольствием приму от вас уточнения, исправления, tools & hacks на: zabrosov[at]gmail.com
Немного о базах данных
Типы таблиц:
Транзакционные(TST): innodb, bdb
Нетранзакционнные(NTST): myisam, merge, memory
info info
SQL синтаксис на примере MySQL, может различаться в зависимости от БД
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; # смотрим информацию по колонкам в таблице
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 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; обратить внимание на алиасы!
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 )
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 ','; # выгрузка в файл
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
SOURCE - пакетное выполнение команд из файла
SOURCE /file/path.sql;# пакетное выполнение команд из файла
Создаем базу данных
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; # создали таблицу с преобразованием структуры
EXPLAIN - план построения запроса к базе
EXPLAIN SELECT * FROM table_name WHERE firstname;
#EXPLAIN проверяем статистику как строится statement, задействуют индексы или нет
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; #переименование таблицы
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 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);
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; # ищем себя любимого
mySQL работает в режиме AUTOCOMMIT по умолчанию
SHOW TABLE STATUS; # тех информация о хранении и типе страниц SHOW ENGINES; # смотрим типы хранилищ ALTER TABLE players TYPE = InnoDB; # задаем или меняем тип хранилища START TRANSACTION; #открыли транзакцию INSERT INTO players VALUES (1, "zabrosov"); COMMIT; # закрыли транзакцию ROLBACK; # откат транзакции
# 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
Механизм авторизации в базе, настраивается в файле 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
# pg_dump --clean dbname > dbname_sql.dump # Резервное копирование и восстановление одной базы данных # psql dbname < dbname_sql.dump # Делается пользователем pgsql или postgres # pg_dumpall --clean > full.dump # Резервное копирование и восстановление всех баз(включая пользователей) # psql -f full.dump postgres
MySQL работает в режиме AUTOCOMMIT по умолчанию
# способ 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;
Обычно удаленный доступ разрешен не ко всем базам данных. В файл /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;
# 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 будет использована без следеющего за ней пароля, он будет запрошен интерактивно.
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
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; # вывод плана и статистики без выполнения запроса
