C, PHP, VB, .NET

Дневникът на Филип Петров


* Създаване на база от данни

Публикувано на 01 февруари 2009 в раздел Бази от Данни.

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

    CREATE DATABASE <име на базата от данни>;

От примера ни в предишната статия с университет, това може да бъде:

    CREATE DATABASE university;

Повечето системи за управление на бази от данни предоставят възможност за допълнителни характеристики за базата от данни, като например разположение на файла, максимален размер, кодировка за низове по подразбиране и т.н.

За създаването на таблици се използва следният опростен формат:

    CREATE TABLE <име на таблица>
    (
        <име на колона> <тип данни>,
        <име на колона> <тип данни>,
        ...
        <име на колона> <тип данни>
    );

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

	CREATE TABLE `university`.`students` (
		`firstname` TINYTEXT NOT NULL ,
		`middlename` TINYTEXT NULL ,
		`lastname` TINYTEXT NOT NULL ,
		`phone` VARCHAR( 32 ) NULL ,
		`address` TEXT NULL ,
		`faknum` BIGINT( 12 ) NOT NULL ,
		`id` INT NOT NULL AUTO_INCREMENT ,
		PRIMARY KEY ( `id` )
	) ENGINE = MYISAM ;

Както забелязвате от примера можем да добавяме и допълнителни параметри. В случая указахме, че някои от колоните могат да не приемат стойност (да бъдат NULL), а други не (NOT NULL). Освен това указахме, че първичният ключ ще бъде полето id. То от своя страна е “AUTO_INCREMENT”, което значи, че ще се записва автоматично при изпълнение на заявка за попълване на данни и освен това всеки нов ред ще съдържа стойност по-голяма с 1 от предишния запис. ENGINE = MYISAM е специфично за MySQL допълнително условие, което указва, че базата от данни ще се запише в MYISAM хранилище. Всяка MyISAM таблица се разполага на три физически файла:

  • .FRM за дефиниция на таблицата;
  • .MYD (MYData) за запис на информацията;
  • .MYI (MYIndex) за индекси.

MyISAM не поддържа трансакции и затова тенденцията е да бъде заменен с InnoDB (от версия 5.5 нататък хранилището по подразбиране ще е именно това). Затова ние ще се фокусираме именно към втората система. При нея стандартно всички бази данни на системата се съхраняват в един общ файл. При добавяне на параметър “innodb_file_per_table” в конфигурационния файл my.cnf може да се направи така, че всяка таблица да се разполага в отделен файл, подобно на MyISAM.

Що се отнася до FOREIGN KEY (засега са налични само при InnoDB, но не и за MyISAM), нека разгледаме следният пример от официалната документация:

	CREATE TABLE parent (
		id INT NOT NULL,
		PRIMARY KEY (id)
	) ENGINE=INNODB;

	CREATE TABLE child (
		id INT NOT NULL,
		parent_id INT NULL,
		FOREIGN KEY (parent_id) REFERENCES parent(id)
                ON DELETE CASCADE ON UPDATE CASCADE
	) ENGINE=INNODB;

Това е елементарен пример, в който създаваме две таблици, като едната е зависима от другата. В случая таблицата parent e клас, в който се записва единствена стойност "id" от тип INT. Таблицата child е негов подклас, като съдържа собствено (независимо) поле id и второ поле parent_id - и двете от тип INT.

Чрез реда "FOREIGN KEY (parent_id) REFERENCES parent(id)" сме указали, че полето parent_id от таблица child ще бъде външен ключ, който сочи към полето id на таблица parent. Понеже този външен ключ не е “unique” (ключовата дума в дефиницията на колоната parent_id липсва), то в тази колона може да има повторения на стойности, следователно връзката е 1:M. Записа "ON DELETE CASCADE" означава, че при изтриване на конкретно "id" от таблица parent ще бъдат изтрити всички записи от таблица child, чийто външен ключ "сочи" към него. Алтернативата е “ON DELETE SET NULL” (при изтриване на ред от parent ще бъде изтрита стойността на съответния външния ключ в таблицата child) или “ON DELETE RESTRICT” (забранява се изтриването на редове от parent ако има външен ключ към тях в child – това се използва по подразбиране ако не е указано друго).

Към всяка колона в таблиците можете да добавяте и следните ограничения (CONSTRAINTS):

  • NULL или NOT NULL: указва дали е позволено на клетка от тази колона да не приема данни;
  • UNIQUE: указва дали е възможно да има повторение на данни в колоната. Прилага се винаги върху външен ключ при моделиране на връзки 1:1. Полета, които са дефинирани като PRIMARY KEY са винаги NOT NULL и UNIQUE;
  • DEFAULT: указва стойност по подразбиране (ако при вмъкване на информация не подадем стойност). Например "salary DOUBLE NOT NULL DEFAULT 600";
  • CHECK: прави проверка на информацията по зададен критерии. Например “salary DOUBLE CHECK (salary > 240)” ще прави проверка дали не сме записали заплата по-ниска от минималната възможна. Засега (до последната стабилна версия по време на писане на статията) MySQL приема клаузите в CHECK ограниченията, но в последствие не ги използва. По-нататък ще разгледаме методи за използване на алтернативи в тази среда.

Задача: Създайте таблиците от базата от данни в задачата в края на темата Entity Relationship (ER) модел

 



20 коментара


  1. Здравейте! Започнах да се уча на SQL и бази данни от този сайт. Сега го закучих на тази статия и бих се радвал ако можете да побликувате "резултата" на задачата зададена в края. Имам затруднения при прилагането на връзките между отделните таблици и техните ключове. Би било страхотно ако можете да драснете решението и да го качите в някой doc share сайт като issuu.com Благодаря във всеки случай (*:

  2. Здравей Ясен,

    Има я в по-следваща статия - "изпълняване на заявки от текстови файл". Имай обаче в предвид, че тези материали са учебни, но не са "учебник". Тоест на места има нарочно (а понякога не) допуснати грешки, които да се дискутират със студентите. Но все пак като "старт" ще ти бъде достатъчно. Със сигурност ще трябва да продължиш с прочитането на сериозен учебник след като си прочел статиите ми.

  3. Нека имаме клас работници (пазим им името) и подклас програмисти (пазим на какъв език за програмиране могат да работят). Ето как ще се създадат таблиците:

    CREATE TABLE employees(
       id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(255) NOT NULL
    )Engine = InnoDB;
    
    CREATE TABLE programmers(
       employee_id INT UNSIGNED NOT NULL,
       FOREIGN KEY(employee_id) REFERENCES employees(id),
       lang ENUM("Java", "C", "C++", "C#") NOT NULL,
       PRIMARY KEY(employee_id, lang)
    )Engine = InnoDB;
  4. Здравейте,
    имам малък проблем относно PRIMARY KEY.
    Мога ли да направя такъв от тип TINYTEXT?
    Това ми дава като ерор:
    create table `dictionaryTable` (
    `word` TINYTEXT NOT NULL ,
    `description` TINYTEXT NULL,
    PRIMARY KEY (`word`)
    )ENGINE = MYISAM
    Error Code: 1170. BLOB/TEXT column 'word' used in key specification without a key length 0.016 sec

  5. MySQL може да интедксира само първите N символа от BLOB/TEXT полета. Не е добре да се използват за PK, даже никога не се използват. Впрочем и Varchar също не е подходящ избор. Обикновено правим PK по числови полета.

  6. Ами в случая искам да направя бази от данни за речник. Според мен няма какво друго да е първичен ключ освен самата дума.

  7. Само да видя дали съм разбрал: с PK търсим нещото което ни трябва? С този id как ще намирам търсеното значение на думата.
    Примерно плана ми беше такъв:

    SELECT description
    FROM dictionary.dictionarytable
    WHERE word = "proba";

    ДА не би да предлагате самата дума да се съхранява бинарно в базата данни?

  8. Не си разбрал правилно. Първичен ключ (primary key) е атрибут (колона в таблицата), който служи да идентифицира по уникален начин всеки запис (ред) на релацията (таблицата). Нищо не ти пречи да търсиш по която и да е колона в таблицата, независимо дали е ключ или не е.

  9. Ясно. Мерси много!
    Значи в моя случай създавам id за го направя първичен ключ, но не го използвам в заявките?

  10. Ако базата ти от данни е само с една таблица и наистина никога няма да ползваш тази допълнителна колона, тогава можеш да я направиш и без първичен ключ. Просто си направи колоната с думите "UNIQUE NOT NULL" и това ще е достатъчно.

  11. Ако в programmers добавим няколко програмисти с едно и също employee_id и различна стойност за lang, това е един и същи човек, който владее повече езици, или греша?

  12. Здравейте, защо тук:
    CREATE TABLE programmers(
    employee_id INT UNSIGNED NOT NULL,
    FOREIGN KEY(employee_id) REFERENCES employees(id),
    lang ENUM("Java", "C", "C++", "C#") NOT NULL,
    PRIMARY KEY(employee_id, lang)
    )Engine = InnoDB;

    за ПК има посочени 2 неща? Не е ли възможно например няколко на брой служители да знаят едни и същи езици?

  13. Здравей Деница,

    Няколко служители могат да знаят едни и същи езици без проблем. Няма проблем да запишеш следното:

    employee_id, land
    1            C++
    1            Java
    2            C++
    3            Java

    Тук когато кажем, че първичен ключ е комбинация от двете колони, това означава, че няма проблем да има повторения във всяка една поотделно, но не може да има двойка еднакви! Например следното е забранено:

    employee_id, land
    1            C++
    1            C++

    Тази комбинация се повтаря, а не може - първичния ключ го забранява.

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

Адресът на електронната поща няма да се публикува


*