Папярэдні занятак

Што такое база дадзеных?

Дагэтуль мы пісалі праграмы якім не было трэба дзесьці захоўваць іх дадзеныя. Як толькі праграма скончвае сваё выкананьне – ўсе дадзеныя страчваюцца. Нам патрэбен спосаб як захоўваць дадзеныя. І гэта базы дадзеных.

Вы можаце думаць пра базу дадзеных як пра некалькі зьвязаных адна з другой табліц у Эксэле. У база дадзеных ёсьць тэрмін, які так і завецца – табліца. Давайце створым табліцу з нумарамі Вашіх сяброў:

friends
-------

 id | name    | phone
 ---+---------+-----------
 1  | Vania   | 9165552212
 2  | Ales    | 3239551212
 3  | Jahajla | 4155501212

Вось ужо й ёсьць у Вас табліца. Гэта не было складана.

Адзін-да-шмат

Самая галоўная рэч якая ёсьць у базах дадзеных гэта тое, што яны могуць ведаць якія адносіны табліцы маюць паміж сабой. Што калі я хачу дадаць хатні й рабочы тэлефон? Як бы Вы гэта зрабілі? Лепшым чынам гэта вось так:

friends
-------

 id | name
 ---+--------
  1 | Vania
  2 | Ales
  3 | Jahajla

phones
------

 id | number     | type | friend_id
 ---+------------+------+----------
  1 | 9164551212 | cell | 1
  2 | 3231551212 | cell | 2
  3 | 4157551212 | cell | 3
  4 | 9169552020 | work | 1
  5 | 3235552030 | work | 2
  6 | 4155552090 | work | 3
 

Калёнка friend_id кажа базе дадзеных што яна трымае значэньне (мае спасылку на) id у табліцы friends. Так як кожны сябра (friend) можа мець шмат тэлефонаў (phones), мы кажам што паміж friends і phones ёсьць адносіны – (адзін-да-шмат) one-to-many.

Падумайце як бы Вы стварылі табліцы рэстарацый Мінска ў залежнасьці ад тыпаў ежы? Напэўна будзе restaurants і types_of_food.

Шмат-да-шмат

А што калі мы хочам стварыць базу дадзеных гарадоў, дзе былі нашыя сябры?

friends
-------

 id | name
 ---+--------
  1 | Vania
  2 | Ales
  3 | Jahajla


cities
------

 id | city      | country | friend_id
 ---+-----------+---------+----------
  1 | Horadnia  | BE      | 1
  2 | Chicago   | US      | 1
  3 | New Delhi | IN      | 2
  4 | Kyiv      | UA      | 3

Нармалёва пакуль. А што калі Алесь таксама паедзе ў Кіеў? Тады наша табліца cities будзе выглядаць вось так:

cities
------

 id | city      | country | friend_id
 ---+-----------+---------+----------
  1 | Horadnia  | BE      | 1
  2 | Chicago   | US      | 1
  3 | New Delhi | IN      | 2
  4 | Kyiv      | UA      | 3
  5 | Kyiv      | UA      | 2

Зараз інфармацыя пра Кіеў адлюстроўваецца двойчы. Гэта ня вельмі добры. Мы больш ня маем адносінаў адзін-да-шмат, калі адзні чалавек мог даведваць шмат гарадоў; зараз мы павінны мець адносіны шмат-да-шмат (many-to-many). Тады кожны чалавек зможа наведваць шмат гарадоў і кожны горад будзе мець шмат наведвальнікаў.

Мы бачылі што калі мы дадаем friend_id у табліцу cities, мы дублюем нашыя дадзеныя. Падумайце што будзе калі дадаць city_id у табліцу friends. Ці вырашыць гэта праблему?

Атрымалася тое ж самае, толькі наадварот.

Што тады застаецца? Падумайце.

Адносіны шмат-да-шмат патрабуюць спецыяльнай табліцы, якая завецца табліца сувязей (join table):

friends
-------

 id | name
 ---+--------
  1 | Vania
  2 | Ales
  3 | Jahajla


cities
------

 id | city      | country
 ---+-----------+---------
  1 | Horadnia  | BE
  2 | Chicago   | US
  3 | New Delhi | IN
  4 | Kyiv      | UA


friends_cities
--------------

 id | city_id | friend_id
 ---+---------+----------
 1  | 1       | 1
 2  | 1       | 2
 3  | 2       | 3
 4  | 2       | 1

friends_cities мае калёнкі ID з абедзьвух табліц, якія яна злучае, таму Вы можаце мець любыя камбінацыі ID у гэтай табліцы.

Калі Вы ня можаце прыдумаць выразнае імя для табліцы сувязей, Вы можаце зваць назваць імёнамі табліц, якія яна злучае пра ніжняе падкрэсліваньне. Але лепей калі нешта больше тлумачальнае. Напрыклад visits.

Схемы

Дагэтуль мы разглядалі табліцы заўсёды зь іх дадзенымі. Але калі Вы хочаце апісаць усе табліцы якія ёсьць у базе дадзеных, адлюстроўваць самі дадзеныя ня вельмі зручна. Лепей проста адлюстраваць калёнкі якія мае табліца:

friends
-------
name
phone
birthday

Назвы калёнак завуцца палямі табліцы.

Для распрацаваньня схемы базы дадзеных нашмат лепей выкарыстоўваць схемы. Напрыклад як гэта:

simple schema

Вельмі будзе дапамагаць калі мы маем ужо штосьці большае, чым проста некалькі табліц:

complex schema

Вы можаце ствараць схемы як гэта на сайце WWW SQL Designer. SQL Designer мае шмат наладаў, але ігнаруйце іх – проста стварайце табліцы й палі, не хвалюйцеся наконт тыпаў палёў. Каб зрабіць сувязь паміж табліцамі, цісніце па ID табліцы, потым connection foreign key (справа) і на ID табліцы якую злучаеце.

Зараз стварыце схему студэнтаў course.by і тых курсаў якія яны наведваюць.

Усталёўваньне sqlite

Sqlite гэта вельмі простая сістэма кіраваньня базамі дадзеных.

Спампуйце й разархівуйце sqlite. У архіве будзе файл sqlite3.exe. Пакладзіце яго ў C:\Windows\system32. Зараз Вы змодаце выконваць каманды з sqlite адразу з кансолі.

Стварэньне баз дадзеных і табліц

Ну вось і дайшлі да моманта стварэньня :) Перайдзіце ў тэчку са сваім праектам і ў кансолі выканайце:

  • sqlite3 test_database;

Гэта каманда створыць файл test_database у якім будзе знаходзіцца Ваша база дадзеных.

Зараз давайце створым першую табліцу:

  • CREATE TABLE contacts (name varchar, age int, birthday timestamp);

Вы толькі што напісалі сваю першую SQL каманду. SQL (Structured Query Language) – самая папулярная мова для ўзаемадзеяньня з базамі дадзеных. Усе каманды SQL мусяць скончвацца кропкай з коскай.

Каманда якую Вы напісалі стварыла табліцу contacts з калёнкамі імя, узрост і дзень народзінаў. Гэтак жа як аб’екты Ruby маюць клас, калёнкі SQL маюць тып дадзеных. У гэтым выпадку, name мае тып varchar, нешта накшталт радка; age – гэта int або цэлае; і birthday – гэта timestamp, што значыць дата й час.

SQL мае шмат тыпаў дадзеных, але вось тыя, якія Вы будзеце выкарыстоўваць найбольш часьцей: int, float, varchar, text, timestamp і boolean. Для поўнага сьпіса глядзіце тыпы дадзеных sqlite.

Каб праглядзець калёнкі табліцы надрукуйце # .schema contacts.

Now, create a couple tables of your own: perhaps one for phone numbers, and another email addresses.

Каб праглядзець усе базы дадзеных надрукуйце # .tables.

Калі штосьці пайшло ня так, надрукуйце # DROP TABLE table_name;, дзе table_name імя Вашае табліцы.

Зьмяненьне табліцы

Вось як дадаць калёнку Ва ўжо існую табліцу:

  • ALTER TABLE contacts ADD family boolean;

Дадайце яшчэ калёнак у Вашую табліцу.

Sqlite вельмі проста база дадзеных, таму яна не падтрымлівая апэрацый зьмяньне калёнак і табліц. Таму калі штосьці пайшло ня так, Вам трэба выдаляць табліцу й ствараць яе зноў

Добрая практыка мець унікальны, аўтаматычна павялічваючыся ID для кожнай запісі ў базе дадзеных. Вось як стварыць табліцу з такім полем:

  • CREATE TABLE contacts (id int primary key, name varchar, age int, birthday timestamp);

Primary key значыць што гэта ўнікальны ідэнтыфікатар гэтай табліцы. Ён аўтаматычна павялічваецца калі Вы дадаеце запісы.

Перарабіце ўсе табліцы так.

Даданьне дадзеных

Зараз давайце дададзім дадзеныя у табліцы:

  • INSERT INTO contacts (name, age, birthday) VALUES ('Ales', 43, '1969-05-01');

Дадайце інфармацыю пра сябе й сваіх сяброў.

Тое ж самае й з другімі табліцамі.

Запыты

Зараз калі Вы маеце дадзеныя ў табліцах, давайце праглядзім іх. Чытаньне дадзеных з базы дадзеных завецца запытваньне (querying). Вось некалькі запытаў паспрабаваць:

  • SELECT name FROM contacts;

Гэта дае Вам сьпіс усіх сяброў з contacts table.

You can select multiple columns from a table:

  • SELECT name, birthday FROM contacts;

Калі Вы хочаце выбраць усе палі з табліцы выканайце:

  • SELECT * FROM contacts;

Выканайце некалькі селектаў на тых табліцах якія Вы стварылі.

Вы можаце абмяжоўваць Вашыя селекты па якому-небудзь крытэру:

  • SELECT * FROM contacts WHERE age >= 18;

Гэта адлюстроўвае усіх дарослых з табліцы contacts.

Частка WHERE прымае наступныя апэратары: =, !=, >, <, >=, <=, BETWEEN, LIKE і IN. Вось прыклад некалькіх зь іх:

  • SELECT * FROM contacts WHERE age BETWEEN 13 AND 17;
  • SELECT * FROM contacts WHERE name BETWEEN 'Judith' AND 'Wilma';
  • SELECT * FROM contacts WHERE name LIKE 'We%';
  • SELECT * FROM contacts WHERE birthday IN ('1969-01-01', '1999-01-01');

У камандзе LIKE, % гэта сымбал які значыць што там могуць быць любыя іншыя сымбалы.

Паспрабуйце яшчэ селекты.

Зьмяньне й выдаленьне запісаў

Каб зьмяніць які-небудзь запіс нам трэба выбраць яго па яго ключу й абнавіць дадзеныя:

  • UPDATE contacts SET name = 'Ales Nosbit' WHERE id = 1;

Паспрабуйце гэтую каманду са сваімі табліцамі.

Вось як выдаляць:

  • DELETE FROM contacts WHERE id = 1;

Зараз усе разам

Мы ўжо шмат пра што даведаліся. Давайце зьбярэм усе веды разам і паглядзім што атрымаецца.

Стварыце базу дадзеных школ і вучняў. Спачатку напалюйце схему, потым стварыце адпавядаючыя табліцы. Яшчэ дадайце табліцу з выкладчыкамі. Але некаторыя выкладчыкі могуць выкладаць у некалькіх школах. Дадайце туды запісы.

Зараз мы паспрабуем запыт які завецца join. Вось як ён можа выглядаць:

  • SELECT students.name, schools.name FROM students, schools WHERE students.school_id = schools.id;

Гэта дасьць Вам сьпіс усіх вучняў і школаў у якія яны ходзюць.

Зараз стварыце запыт які адлюструе выкладчыкаў і іх школы.

Наступны занятак