SQL проблем и теория (бази данни за обучение) GitHub

Какво представляват базите данни

Разбира се, можете да опитате да направите свое собствено хранилище (например върху файлове), но е малко вероятно то да работи толкова бързо и надеждно, колкото професионална база данни. Добрата база данни гарантира липса на загуба на съхранени данни, дори ако токът спре неочаквано, няма проблеми с много потребители, работещи едновременно, позволява ви да търсите информация по произволни критерии.

Има различни видове бази данни, но този урок се фокусира върху бази данни, които поддържат езика SQL. В тях всякакви операции с данни - добавяне, изтриване, търсене - се извършват чрез изпращане на SQL заявки. Самият език е доста прост и заявките в него наподобяват обикновени изречения на английски. Ами, например, заявка за изтриване на потребител с имейл [email protected] от базата данни изглежда така: DELETE FROM users WHERE email = '[email protected]' . Ако знаете английски ("премахване от потребители, където имейлът е равен на '[email protected]'"), тогава смисълът на заявката е лесен за разбиране, дори и без да знаете SQL. Заявките могат да се изпращат от самия разработчик или от програма, написана от него.

SQL е нещо като стандарт в света на базите данни. Познавайки този език, можете да работите с различни бази данни от различни производители.

Програми, които реализират базата данни

Има различни програми, които ви позволяват да създавате и управлявате база данни. Те се наричат ​​СУБД (системи за управление на бази данни). От безплатните най-известните са MySQL и PostgreSQL. MySQL (през 2016 г.) е по-често срещан, а PostgreSQL има по-интересни нестандартни функции (и също така се счита, че поддържа стандарта по-пълно).

Има и търговски СУБД - например MSSQL, Oracle DB.

И накрая, има ощевградени СУБД, които не се използват отделно, а са вградени в друга програма и се използват само от нея. Е, например (през 2016 г.) вградената безплатна SQLite DBMS се използва от браузъра Chrome, който съхранява история и отметки с него, Skype за съхранение на съобщения и много мобилни приложения за Android и iOS.

Можете да работите с всички тези бази данни, като знаете езика SQL.

Устройство за база данни

Базата данни съхранява данни в таблици. Таблиците се създават от разработчика и обикновено всяка от тях е предназначена за свой собствен обект - например таблица със списък на потребители, таблица с теми във форум, таблица със съобщения във форум. Таблицата се състои от колони, всяка от които има определен тип (число, низ). Е, например, таблица за съхраняване на информация за потребителите на форума може да изглежда така:

id име имейл password_hash сол регистриран
1Администратор[email protected]abbs09s7s6s6gt9xbxvx4x302014-08-02
2Иван[email protected]hd6bc00c8c7c665cegs65s4s4sb0x2015-01-01

И ето как може да изглежда таблица със съобщения, оставени от потребители във форума. За простота, нека си представим, че нямаме отделни теми, но има една голяма обща нишка от съобщения:

id author_id публикуван текст
112014-08-03Добре дошли в нашия форум! Очаквам вашите съобщения.
212014-08-04Там няма никой.
312014-08-05Нито една душа.
422015-01-01Здравейте всички. Нов съм тук.

Работа с базата данни

Обикновено самият сървър на база данни (програматакоето осигурява работата му) няма собствен интерфейс и някои прозорци, бутони за взаимодействие с него. Управлението на базата данни се извършва чрез стартиране на клиентска програма, която се свързва със сървъра, изпраща SQL заявки към него и показва получените отговори. Няколко клиента могат да се свързват към базата данни едновременно.

Обикновено всяка база данни има клиент за команден ред. Това е програма с минималистичен интерфейс, в която можете да пишете SQL заявки и да виждате получените отговори. Това трябва да използва начинаещият.

Тези, които са усвоили основите, могат да използват по-сложни GUI клиенти. Те могат да показват информация от базата данни под формата на таблици, да навигират в тях, да променят стойностите в тях. Можете също да изпълнявате ръчно написани SQL заявки. Тук няма да пиша имената на конкретни програми, но те лесно се намират по думи като "mysql gui", "mysql admin", "postgresql gui" и т.н. Бих ви посъветвал първо да се научите как да работите изключително в клиента на командния ред и едва след това да преминете към тези програми.

И накрая, можете да се свържете и да изпращате заявки към базата данни от програмата. Така например PHP скрипт може да избира данни от базата данни и да ги показва на уеб страница. Това изисква библиотека или клиентско разширение за базата данни. PHP дори има 2 разширения за това (PDO и MySQLi), препоръчвам да използвате разширението PDO, тъй като поддържа изключения, което улеснява получаването на информация за него, когато възникне грешка.

Учебни бази данни

Теория и уроци за начинаещи:

Ако съхранявате данни в няколко таблици, тогава трябва да можете да създавате връзки между тях. Ето уроци по тази тема:

След това трябва да се научитеправилно проектиране на таблици и връзки между тях. За да направите това, трябва да проучите нормализирането на базата данни. Има различни статии по тази тема - някои са написани на разбираем език, други не. Това е важна тема, ако не следвате принципите на нормализиране, тогава ще бъде неудобно да работите с такава база.

Ще ви дам пример. Теорията изисква избягване на дублиране на данни. Да предположим, че разработчикът не е проучил нормализирането и е направил така, че във форума, в таблицата със съобщения, всяко съобщение да съхранява името на потребителя, който го е написал. По този начин, ако потребителят остави много съобщения, името му ще бъде запазено много пъти. Ако потребителят сега иска да промени името, тогава ще трябва да го променим в таблицата с потребители, както и да намерим всичките му съобщения и да променим името там. В най-добрия случай това ще изисква допълнителна работа (намерете всички таблици, където се споменава това име), в най-лошия случай можем да направим грешка и да променим името на едно място, да забравим да го променим на друго. На базата на няколко маси това е малко вероятно, но в системи с големи таблици може да има десетки или дори стотици. Оказва се, че неспазването на принципите на дизайна на базата данни води до факта, че писането на код ще отнеме повече време за разработчиците.

Междувременно още няколко полезни връзки:

Българските знаци не работят на командния ред под Windows

Необходимо е да се изпълни командата SET NAMES cp866; след свързване: http://gahcep.github.io/blog/2013/01/05/mysql-utf8/

Още връзки по темата за кодировките при свързване към mysql от php:

На какво трябва да обърнете внимание?

Ето списък с концепции, които си струва да знаете, ако искате да станете много добри в MySQL:

  • управление на бази данни: СЪЗДАВАНЕ НА БАЗА ДАННИ, ИЗХВЪРЛЯНЕ НА БАЗА ДАННИ, ПОКАЖЕТЕ БАЗИ ДАННИ
  • управление на таблици: CREATE TABLE, ALTER TABLE,DROP TABLE, SHOW TABLES, SHOW CREATE TABLE, DESC таблица, TRUNCATE таблица
  • управление на правата за достъп: GRANT, SHOW GRANTS
  • типове колони: ENUM, SET, CHAR, VARCHAR, TEXT, DATE, TIME, DATETIME, TIMESTAMP, INT, FLOAT, TINYINT, DECIMAL, MEDIUMTEXT, LONGTEXT. Каква е разликата между TIMESTAMP и DATETIME? Между FLOAT и DECIMAL? CHAR и VARCHAR?
  • DECIMAL е тип с фиксирана точност. За разлика от FLOAT/DOUBLE, които са приблизителни и могат да загубят десетични знаци, DECIMAL съхранява даден брой знаци. Използва се например за съхраняване на сума пари.
  • NULL и троична логика (в базата данни NULL означава „неизвестно“. Например възрастта на потребителя е неизвестна. Съответно всички операции с NULL вземат това предвид: NULL + 5 също води до NULL (5 + неизвестно число дава неизвестно число), сравнението (NULL = NULL) връща false, за да провери дали полето NULL е равно, използвайте IS NULL / IS NOT NULL. http://ru.wikipedia.org/wiki /NULL_(SQL))
  • възможно ли е да търсите празни полета с условието WHERE x = NULL?
  • когато създавате таблица, можете да направите полетата задължителни, като посочите NOT NULL
  • ИЗБЕРЕТЕ/ВМЪКНЕТЕ/ИЗТРИЙТЕ/АКТУАЛИЗИРАНЕ
  • ред на изпълнение на заявка за избор: FROM+JOIN, WHERE, GROUP, HAVING, ORDER, LIMIT, SELECT (трябва да го знаете наизуст)
  • ЗАМЕНИТЕ, ВМЪКНЕТЕ ИГНОРИРАЙТЕ, ВМЪКНЕТЕ .. ПРИ АКТУАЛИЗАЦИЯ НА ДУБЛИРАН КЛЮЧ
  • избор на данни: DISTINCT, JOIN, ORDER BY, GROUP BY, HAVING, LIMIT
  • групиращи и обобщаващи функции: ГРУПИРАНЕ ПО, БРОЙ, MAX, MIN, AVG, SUM
  • транзакции: BEGIN, ROLLBACK, COMMIT
  • външни ключове: FOREIGN KEY. Външен ключ е поле, което съхранява >

Теория на дизайна на бази данни

За да можете да проектирате бази данни и нови таблици, трябва да знаете следното:

  • видовевръзки между таблиците: едно към едно, едно към много, много към много
  • принципи на нормализиране на бази данни. В интернет можете да намерите статии, където "нормалните форми" се обясняват с прости думи, например http://club.shelek.ru/viewart.php? >Ето поредица от статии на Habré, която е подходяща като въведение: 1-3, 4-6, 7-9, 10-13, 14-15, бонус

Каква е разликата между MyISAM и InnoDB машини за таблици?

Накратко: MyISAM е по-опростен и не поддържа външни ключове и транзакции. И почти винаги са необходими. Следователно в 99% от случаите имате нужда от InnoDB.

Индексите ви позволяват да ускорите търсенето на условия като x = ? , x , x МЕЖДУ ? И? , x LIKE 'xxx%' , x IN (?, ?, ?) , както и сортиране (полетата, сортирани по, трябва да са в края на индекса). Разликата на голяма маса може да бъде огромна - от порядъка на 1 хилядна от секундата срещу няколко секунди. Ами, например, ако имаме таблица с размер милион записа и направим заявка

след това без индекс, MySQL ще трябва да прочете милион стойности от диска в паметта, като ги сортира само за да вземе първите 10. Ако има индекс на полето y (което съхранява стойностите на това поле, сортирани във възходящ ред), тогава MySQL просто ще вземе първите 10 записа от него. Разликата в скоростта ще е огромна.

Ето статии за начинаещи за индексите:

Ако прочетете всичко внимателно, отговорете на въпроса, може ли индекс (ако да, кой) да ускори такива заявки:

  • ИЗБЕРЕТЕ * ОТ таблицата WHERE x <> 1
  • ИЗБЕРЕТЕ * ОТ таблицата WHERE x + y
  • ИЗБЕРЕТЕ MAX(a) ОТ таблицата WHERE b = 2
  • SELECT * FROM table WHERE име LIKE '%John%'
  • ИЗБЕРЕТЕ * ОТ таблицата WHERE b = 1 И a

Въпрос за харесванията

С натрупаните знания можете лесно да вземете решениетази задача: има потребители (id, име) и те могат да се харесват. Направете таблици за съхраняване на цялата тази информация и напишете заявка, която ще покаже таблица като тази:

  • потребителско име
  • Име
  • получени харесвания
  • набор от харесвания
  • взаимни харесвания

След това покажете списък с всички потребители, които са харесали потребители A и B, но не са харесали потребител C. Тук има няколко решения.

Труден? Добре, нека започнем с по-проста задача: просто покажете 5-те най-популярни потребители.

  • Ако използвате няколко свързани таблици, релациите трябва да бъдат маркирани с външни ключове.
  • Желателно е на ниво база данни да се забрани възможността за харесване на потребител на друг потребител два пъти
  • Съвет: тази задача се решава без подзаявки
  • Съвет: просто използвайте 2 обединения и групиране

Сложна (но по-важна) задача за харесванията

  • потребителят не може да даде 2 харесвания на едно и също лице (например на една и съща снимка)
  • потребителят може да отмени харесването
  • необходимо е да можете да преброите броя харесвания, получени от субекта, и да изведете списък с потребители, които са поставили харесвания
  • в бъдеще може да има нови типове обекти, които можете да харесате

Тук има няколко решения.

Проблем за киното

Ето един допълнителен, по-труден проблем. Има кино, където се прожектират филми. Филмът има заглавие, продължителност (нека бъде 60, 90 или 120 минути за по-лесно), цена на билета (може да е различна в различни часове и дни), начален час на сесията (един филм може да бъде показван няколко пъти в различно време за различницена). Също така има информация за закупени билети (номер на билета, за коя сесия).

  • направете компетентна нормализирана схема за съхранение на тези данни в DB. Въведете 4-5 филма, програма за един ден и няколко продадени билета.

Направете заявки, които се броят и показват по разбираем начин:

  • грешки в графика (филми се припокриват), сортирани по време във възходящ ред. Необходимо е да се изведат колоните "филм 1", "начален час", "продължителност", "филм 2", "начален час", "продължителност".
  • почивките, по-големи или равни на 30 минути между филмите, се получават чрез намаляване на дължината на паузата. Необходимо е да се покажат колоните "филм 1", "начален час", "продължителност", "начален час на втори филм", "продължителност на паузата".
  • списък с филми, всеки от които показва общия брой посетители за всички времена, средния брой зрители на сесия и общата събрана сума за всеки, сортирани в низходящ ред на печалба. В долната част на таблицата трябва да има ред „общо“, съдържащ данни за всички филми наведнъж.
  • брой посетители и касови приходи, групирани по начален час на филма: от 9 до 15 ч., от 15 до 18 ч., от 18 до 21 ч. и от 21 до 00 ч. (тоест колко посетители са дошли от 9 до 15 часа, колко от 15 до 18 и т.н.).

Трудна задача за календара

Решихте ли предишни проблеми и те са твърде лесни? Добре, нека се заемем с една наистина трудна задача. Напишете SQL код, който показва календара за текущия месец във формата:

Пон Вт Ср Чет Пет Сб Нед

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