* Изпълняване на заявки от текстов файл
Публикувано на 14 февруари 2009 в раздел Бази от Данни.
Често се налага да изпълним серия от заявки към база от данни. Понякога те може да са изключително много на брой и въвеждането им една по една ще бъде изключително неудобно. Поради тази причина е въведена възможност за импортиране на серия от заявки, които са записани в текстов файл.
Това всъщност е доста честа практика, когато например създаваме база данни. Нека разгледаме например следната ER диаграма:
И съответните CREATE заявки за нея:
DROP DATABASE IF EXISTS `university`;
CREATE DATABASE `university`;
CREATE TABLE `university`.`university` (
`id` TINYINT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 255 ) NOT NULL ,
`founded` DATE NULL DEFAULT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;
CREATE TABLE `university`.`faculties` (
`id` TINYINT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 255 ) NOT NULL ,
`univ_id` TINYINT NOT NULL ,
`dekan_id` INT NULL DEFAULT NULL UNIQUE ,
PRIMARY KEY ( `id` ) ,
FOREIGN KEY ( `univ_id` )
REFERENCES `university`.`university`( `id` )
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB;
CREATE TABLE `university`.`professors` (
`id` INT NOT NULL AUTO_INCREMENT ,
`firstname` TINYTEXT NOT NULL ,
`middlename` TINYTEXT NULL DEFAULT NULL ,
`lastname` TINYTEXT NOT NULL ,
`phone` VARCHAR( 32 ) NULL DEFAULT NULL ,
`address` TEXT NULL DEFAULT NULL ,
`EGN` BIGINT( 10 ) NOT NULL ,
`faculty_id` TINYINT NOT NULL,
PRIMARY KEY ( `id` ),
FOREIGN KEY ( `faculty_id` )
REFERENCES `university`.`faculties`( `id` )
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB;
ALTER TABLE `university`.`faculties`
ADD CONSTRAINT FOREIGN KEY (`dekan_id`) REFERENCES `professors`(`id`);
CREATE TABLE `university`.`subjects` (
`id` TINYINT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 255 ) NOT NULL ,
`horarium_hr` SMALLINT NOT NULL ,
`lead_professor_id` INT NOT NULL ,
PRIMARY KEY ( `id` ) ,
FOREIGN KEY ( `lead_professor_id` )
REFERENCES `university`.`professors`( `id` )
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB;
CREATE TABLE `university`.`students` (
`firstname` TINYTEXT NOT NULL ,
`middlename` TINYTEXT NULL DEFAULT NULL ,
`lastname` TINYTEXT NOT NULL ,
`phone` VARCHAR( 32 ) NULL DEFAULT NULL ,
`address` TEXT NULL DEFAULT NULL ,
`faknum` BIGINT( 12 ) NOT NULL ,
PRIMARY KEY ( `faknum` )
) ENGINE = InnoDB;
CREATE TABLE `university`.`zapisani_studenti` (
`student_fnum` BIGINT( 12 ) NOT NULL ,
`subject_id` TINYINT NOT NULL ,
FOREIGN KEY ( `student_fnum` )
REFERENCES `university`.`students`( `faknum` )
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY ( `subject_id` )
REFERENCES `university`.`subjects`( `id` )
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY( `student_fnum`, `subject_id` )
) ENGINE = InnoDB;
CREATE TABLE `university`.`zadochnici` (
`student_fnum` BIGINT( 12 ) NOT NULL ,
`firma` VARCHAR( 255 ) NULL DEFAULT NULL ,
FOREIGN KEY ( `student_fnum` )
REFERENCES `university`.`students`( `faknum` )
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY( `student_fnum` )
) ENGINE = InnoDB;
Обърнете внимание на първата заявка. Тя означава „ако съществува база данни с име university, то я изтрий“. Напомняме, че изпълненията на заявки са необратими – веднъж изтрита всичката информация от базата данни изчезва перманентно.
Сега копирайте текста и го запишете в текстов файл. Нека например той е с име „university.sql“ и е записан в директория „c:\“. За да „вмъкнете“ заявките, то влезте в „MySQL Client“ и изпълнете командата:
source c:/university.sql;
Тук специално отбелязваме, че в пътищата до файловете трябва да използвате обратната наклонена черта „/“ вместо стандартната за Windows „\“. Резултатът след изпълнение на командата ще бъде следния:
Тук ще ви покажем как можете да разглеждате структурата на съществуващи бази данни. Чрез командата „SHOW databases“ можете да видите наличните бази данни на сървъра. Ще видите новосъздадената база данни „university“ в списъка. Сега бихме искали да „прелистим“ тази база данни и да видим какви таблици има. Първо трябва да кажем, че ще „използваме“ тази база данни:
USE university;
След това изпълнете:
SHOW tables;
Ако желаете да видите полетата на дадена таблица (например professors) използвайте командата DESC:
DESC professors;
Резултатът би трябвало да бъде следния:
Разгледайте системната таблица mysql и се опитайте да разберете какво съдържа тя…
Защо за имената използвате TINYTEXT вместо VARCHAR( 255 ) например ?
Няма смисъл от това, VARCHAR(255) е много по-добре.
Защо `dekan_id` INT NULL DEFAULT NULL след създаването на таблицата professors не е насочено към нея ?
Може да се насочи с:
ALTER TABLE faculties
ADD CONSTRAINT FOREIGN KEY (dekan_id) REFERENCES professors(id);
Да знам как може да се насочи :) интересуваше ме не е ли това точно логиката на връзката 1:1.За да бъде осъществена тя трябва да се направи горепосоченото то вас.
За да бъде връзката 1:1 освен да бъде FOREIGN KEY трябва колоната да е UNIQUE. Сега ще го поправя в статията.
Благодаря много за разясненията :)
CREATE TABLE `university`.`zadochnici` (
`student_fnum` BIGINT( 12 ) NOT NULL ,
`firma` VARCHAR( 255 ) NULL DEFAULT NULL ,
FOREIGN KEY ( `student_fnum` )
REFERENCES `university`.`students`( `faknum` )
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY( `student_fnum` )
) ENGINE = InnoDB;
Защо тук първичния ключ е и външен ?
А тук със същата база данни е по различен начин ?
CREATE TABLE `university`.`zadochnici` (
`student_fnum` BIGINT( 12 ) NOT NULL ,
`firma` VARCHAR( 255 ) NULL DEFAULT NULL ,
UNIQUE( `student_fnum` ),
FOREIGN KEY ( `student_fnum` )
REFERENCES `university`.`students`( `faknum` )
ON DELETE CASCADE
) ENGINE = InnoDB;
Каква е разликата а това UNIQUE показва, че връзката е 1:1 ако не греша ?
Класа zadochnici е подклас на students. С други думи в тази таблица изнасяме допълнителна информация, но само за част от студентите, а не за всички.
Когато една колона е primary key, то тя е едновременно unique и not null. И в двата примера връзката е 1:1, но първия е по-правилен. Във вторият пример няма първичен ключ.
1.А не трябва ли когато е Primary key да е auto_increment ?
2.Или по друг начин да попитам ако това ни е последния клас от ER диаграмата той трябва ли да има и първичен и външен ключ ?
3.И може ли една променлива едновременно да бъде и първичен и външен ключ както е в примера ?
4. Втория пример като няма първичен ключ той също е правилен, така ли ?
1. Не, никак не е задължително. Auto_increment се използва за удобство тогава, когато първичните ключове са поредни номера 1,2,3… В случая първичния ключ е факултетен номер.
2. Препоръчително е всяка таблица да има първичен ключ. Как са разделени и свързани таблиците (къде и какви външни ключове има) е друг въпрос. Понятие като „последна таблица“ няма.
3. Не „променлива“, а „колона“. Да, очевидно може. Както е показано в примера.
4. Да, ще работи. Но все пак първия продължава да е по-правилен. Опитайте се да спазвате условието „всяка таблица да си има първичен ключ“.
Имаме следния пример:
Create table `example`(
`id` int not null unique,
`name` varchar(19) not null,
Foreign key(`id`) references `ex_2` (`id`) on delete set null on update cascade,
Primary key (`id`),
);
Целта ми тук е `id`да е външен ключ връзката е 1:1 но да мога да го изтривам и променям (т,е, трябва да няма Not null, но пък ако го няма неможе да бъде първичен ключ) как бихте ме посъветвали да го направя ?
Благодаря!
С така зададеното условие id не може да е първичен ключ и просто не го правете такъв – ще работи.
Когато имате таблица, в която няма първичен ключ и няма адекватни данни, които да бъдат първичен ключ, то се повдигат въпросителни относно дали въобще тези данни трябва да са в отделна таблица.
А от какъв тип е връзката между клас и подклас в горната ER диаграма?
Между „студенти“ и „задочници“ е реализирано като връзка 1:1. Външния ключ е и първичен, т.е. стойностите са уникални и няма повторения.
Добър вечер.
Бих желал да попитам нещо относно заявките от този тип >
`id` TINYINT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 255 ) NOT NULL ,
`founded` DATE NULL DEFAULT NULL ,
PRIMARY KEY ( `id` )
Необходимо ли е всеки път да изписваме на primary key – а NOT NULL и AUTO_INCREMENT ?
Това че го „опаковаме“ в PRIMARY KEY(`id`)
не гарантира ли именно това ?
Тоест не AUTO_INCREMENT, а UNIQUE и NOT NULL
Или това е валидно само при обявяването на атрибута за primary key още в началото, като например:
`id` TINYINT AUTO_INCREMENT PRIMARY KEY ,
Здравей Георги,
Ако едно поле е PRIMARY KEY, не е необходимо изрично да се посочи NOT NULL и UNIQUE – то автоматично ще стане такова.
Защо няма съответствие между атрибутите в диаграмата и полетата в заявките?
и как се осъществява връзката предмети:студенти М:М?
Намерих отговор на втория ми въпрос: – Връзки от тип M:M между две таблици се представя като отделна таблица, в която има два външни ключа, сочещи към свързваните таблици.
А не може ли да се представи без отделна таблица?
Имената не съвпадат (кирилица-лат), иначе всичко съответства. Връзката М:М е показана – таблица zapisani_studenti. Няма друг начин за представяне.
За първия ми въпрос имах предвид следното:
Примерно в таблица университет има полета id, name и founded, а в ER диаграмата има град и име. Не трябва ли и полетата да са град и име?
Добавен е поради липсата на друг практически подходящ първичен ключ.
Между другото базата от данни нарочно не е нормализирана. Проблемите се дискутират на упражнения.
Какво точно значи грешка:
ERROR 1215 (HY000): Cannot add foreign key constraint ?
Объркал си реда, в който създаваш foreign key.
Здравейте,
Ползвам МySQL 5.7 и командата
source c:/university.sql; винаги ми дава:
Failed to open file ‘c:\university.sql’, error: 2
а файла university.sql си е точно там. Изчетох доста неща в нета, но нищо не свърши работа. Някакви предложения ?
Сега се получи. Тъй като файла е текстов, не бях добавил разширението txt. в командния ред.