ENCES dbo.Teachers (idTeacher)

GO

SET NOEXEC OFF GO

 

При работе с оболочкой dbForge Studio для MySQL используется другой путь к пункту меню: «База данных» -> «Резервная копия» -> «Создать резервную копию БД» (аналогично можно использовать гиперссылку на стартовой странице в разделе «Миграция данных»). Приведем содержимое этого файла:

--

-- Скрипт сгенерирован Devart dbForge Studio for MySQL, Версия 6.2.233.0

-- Домашняя страница продукта: http://www.devart.com/ru/dbforge/mysql/studio

-- Дата скрипта: 04.08.2014 23:47:11

-- Версия сервера: 5.0.67-community-nt

-- Версия клиента: 4.1

--

-- 

-- Отключение внешних ключей

-- 

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0

*/; 

-- 

-- Установить режим SQL (SQL mode)

-- 

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; -- 

-- Установка базы данных по умолчанию

--

USE decanat;

--

-- Описание для таблицы departments

--

DROP TABLE IF EXISTS departments; CREATE TABLE departments (

idDepartment INT(11) NOT NULL AUTO_INCREMENT,

TitleDepartment VARCHAR(255) NOT NULL,

PhoneDepartment VARCHAR(255) NOT NULL,

PRIMARY KEY (idDepartment)

)

ENGINE = INNODB

AUTO_INCREMENT = 1

CHARACTER SET utf8

COLLATE utf8_general_ci;

 

--

-- Описание для таблицы marks

--

DROP TABLE IF EXISTS marks; CREATE TABLE marks (idMark INT(11) NOT NULL, LowBalls INT(11) NOT NULL,

HighBalls INT(11) NOT NULL,

PRIMARY KEY (idMark)

)

ENGINE = INNODB

CHARACTER SET utf8

COLLATE utf8_general_ci;

 

--

-- Описание для таблицы students

--

DROP TABLE IF EXISTS students; CREATE TABLE students (

idStudent INT(11) NOT NULL AUTO_INCREMENT,

FIOStudent VARCHAR(255) NOT NULL,

NumGroup INT(11) NOT NULL,

PRIMARY KEY (idStudent)

)

ENGINE = INNODB

AUTO_INCREMENT = 1

CHARACTER SET utf8

COLLATE utf8_general_ci;

 

--

-- Описание для таблицы subjects

--

DROP TABLE IF EXISTS subjects;

CREATE TABLE subjects (

idSubject INT(11) NOT NULL AUTO_INCREMENT,

TitleSubject VARCHAR(255) NOT NULL,

PRIMARY KEY (idSubject)

)

ENGINE = INNODB

AUTO_INCREMENT = 1

CHARACTER SET utf8

COLLATE utf8_general_ci;

 

--

-- Описание для таблицы teachers

--

DROP TABLE IF EXISTS teachers; CREATE TABLE teachers (

idTeacher INT(11) NOT NULL AUTO_INCREMENT, FIOTeacher VARCHAR(255) NOT NULL, idDepartment INT(11) NOT NULL,

PRIMARY KEY (idTeacher),

CONSTRAINT FK_teachers_departments_idDepartment FOREIGN KEY (idDepartment)

REFERENCES departments(idDepartment) ON DELETE NO ACTION ON UPDATE NO ACTION

)

ENGINE = INNODB

AUTO_INCREMENT = 1

CHARACTER SET utf8

COLLATE utf8_general_ci;

 

--

-- Описание для таблицы results

--

DROP TABLE IF EXISTS results; CREATE TABLE results (idStudent INT(11) NOT NULL, idSubject INT(11) NOT NULL, idTeacher INT(11) NOT NULL, DateExam DATETIME NOT NULL,

NumSemestr INT(11) NOT NULL,

Balls INT(11) NOT NULL,

Mark INT(11) NOT NULL,

PRIMARY KEY (idStudent, idSubject, idTeacher, NumSemestr),

CONSTRAINT FK_results_students_idStudent FOREIGN KEY (idStudent)

REFERENCES students(idStudent) ON DELETE NO ACTION ON UPDATE NO ACTION,

CONSTRAINT FK_results_subjects_idSubject FOREIGN KEY (idSubject)

REFERENCES subjects(idSubject) ON DELETE NO ACTION ON UPDATE NO ACTION,

CONSTRAINT FK_results_teachers_idTeacher FOREIGN KEY (idTeacher)

REFERENCES teachers(idTeacher) ON DELETE NO ACTION ON UPDATE NO ACTION)

ENGINE = INNODB

CHARACTER SET utf8

COLLATE utf8_general_ci;

 

--

-- Описание для таблицы sessions

--

DROP TABLE IF EXISTS sessions; CREATE TABLE sessions (

NumGroup INT(11) NOT NULL,

NumSemestr INT(11) NOT NULL, idSubject INT(11) NOT NULL, idTeacher INT(11) NOT NULL,

Zach_Exam VARCHAR(7) NOT NULL,

PRIMARY KEY (NumGroup, NumSemestr, idSubject, idTeacher),

CONSTRAINT FK_sessions_subjects_idSubject FOREIGN KEY (idSubject)

REFERENCES subjects(idSubject) ON DELETE NO ACTION ON UPDATE NO ACTION,

CONSTRAINT FK_sessions_teachers_idTeacher FOREIGN KEY (idTeacher)

REFERENCES teachers(idTeacher) ON DELETE NO ACTION ON UPDATE NO ACTION)

ENGINE = INNODB

CHARACTER SET utf8

COLLATE utf8_general_ci;

 

-- 

-- секция для команд вставки данных из таблиц

--

-- 

-- Включение внешних ключей

-- 

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

 

В PostgreSQL резервную копию можно создать с помощью пункта контекстного меню «Резервная копия» для элемента дерева, соответствующего копируемой базе данных. В результате будет сгенерирован следующий SQL-код:

 

--

-- PostgreSQL database dump

--

-- Dumped from database version 9.3.5

-- Dumped by pg_dump version 9.3.5

-- Started on 2014-08-04 23:54:32

 

SET statement_timeout = 0;

SET lock_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SET check_function_bodies = false;

SET client_min_messages = warning;

--

-- TOC entry 177 (class 3079 OID 11750)

-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 

--

 

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

 

--

-- TOC entry 1991 (class 0 OID 0)

-- Dependencies: 177

-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 

--

 

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

 

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--

-- TOC entry 171 (class 1259 OID 16397)

-- Name: Departments; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 

--

 

CREATE TABLE "Departments" (

"idDepartment" oid NOT NULL,

"TitleDepartment" text NOT NULL,

"PhoneDepartment" text

);

 

ALTER TABLE public."Departments" OWNER TO postgres;

 

--

-- TOC entry 170 (class 1259 OID 16394)

-- Name: Marks; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 

--

 

CREATE TABLE "Marks" (

"idMark" integer NOT NULL,

"LowBalls" integer NOT NULL,

"HighBalls" integer NOT NULL

);

 

ALTER TABLE public."Marks" OWNER TO postgres;

--

-- TOC entry 176 (class 1259 OID 16431)

-- Name: Results; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 

--

CREATE TABLE "Results" (

"idStudent" integer NOT NULL,

"idSubject" integer NOT NULL,

"idTeacher" integer NOT NULL,

"NumSemestr" integer NOT NULL,

"DateExam" date,

"Balls" integer, "Mark" integer

);

 

ALTER TABLE public."Results" OWNER TO postgres;

 

--

-- TOC entry 174 (class 1259 OID 16415)

-- Name: Sessions; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 

--

CREATE TABLE "Sessions" (

"NumGroup" integer NOT NULL,

"NumSemestr" integer NOT NULL,

"idSubject" integer NOT NULL,

"idTeacher" integer NOT NULL,

"Zach_Exam" text

);

 

ALTER TABLE public."Sessions" OWNER TO postgres;

--

-- TOC entry 175 (class 1259 OID 16423)

-- Name: Students; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 

--

 

CREATE TABLE "Students" (

"idStudent" oid NOT NULL,

"FIOStudent" text,

"NumGroup" integer

);

 

ALTER TABLE public."Students" OWNER TO postgres;

--

-- TOC entry 172 (class 1259 OID 16403)

-- Name: Subjects; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 

--

 

CREATE TABLE "Subjects" (

"idSubject" oid NOT NULL,

"TitleSubject" text NOT NULL

);

 

ALTER TABLE public."Subjects" OWNER TO postgres;

 

--

-- TOC entry 173 (class 1259 OID 16409)

-- Name: Teachers; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 

--

 

CREATE TABLE "Teachers" (

"idTeacher" oid NOT NULL,

"FIOTeacher" text NOT NULL,

"idDepartment" integer NOT NULL

);

 

ALTER TABLE public."Teachers" OWNER TO postgres;

--

-- TOC entry 1978 (class 0 OID 16397)

-- Dependencies: 171

-- Data for Name: Departments; Type: TABLE DATA; Schema: public; Owner: postgres

--

 

COPY "Departments" ("idDepartment", "TitleDepartment", "PhoneDepartment") FROM stdin; \. 

--

-- TOC entry 1977 (class 0 OID 16394)

-- Dependencies: 170

-- Data for Name: Marks; Type: TABLE DATA; Schema: public; Owner: postgres

--

 

COPY "Marks" ("idMark", "LowBalls", "HighBalls") FROM stdin;

\. 

--

-- TOC entry 1983 (class 0 OID 16431)

-- Dependencies: 176

-- Data for Name: Results; Type: TABLE DATA; Schema: public; Owner: postgres

--

 

COPY "Results" ("idStudent", "idSubject", "idTeacher", "NumSemestr", "DateExam",

"Balls", "Mark") FROM stdin; \. 

--

-- TOC entry 1981 (class 0 OID 16415)

-- Dependencies: 174

-- Data for Name: Sessions; Type: TABLE DATA; Schema: public; Owner: postgres

--

 

COPY "Sessions" ("NumGroup", "NumSemestr", "idSubject", "idTeacher",

"Zach_Exam") FROM stdin; \. 

--

-- TOC entry 1982 (class 0 OID 16423)

-- Dependencies: 175

-- Data for Name: Students; Type: TABLE DATA; Schema: public; Owner: postgres

--

 

COPY "Students" ("idStudent", "FIOStudent", "NumGroup") FROM stdin; \. 

--

-- TOC entry 1979 (class 0 OID 16403)

-- Dependencies: 172

-- Data for Name: Subjects; Type: TABLE DATA; Schema: public; Owner: postgres

--

 

COPY "Subjects" ("idSubject", "TitleSubject") FROM stdin; \. 

--

-- TOC entry 1980 (class 0 OID 16409)

-- Dependencies: 173

-- Data for Name: Teachers; Type: TABLE DATA; Schema: public; Owner: postgres

--

 

COPY "Teachers" ("idTeacher", "FIOTeacher", "idDepartment") FROM stdin; \. 

--

-- TOC entry 1853 (class 2606 OID 16439)

-- Name: pk_department; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 

--

 

ALTER TABLE ONLY "Departments"

ADD CONSTRAINT pk_department PRIMARY KEY ("idDepartment");

 

--

-- TOC entry 1851 (class 2606 OID 16441)

-- Name: pk_mark; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 

--

 

ALTER TABLE ONLY "Marks"

ADD CONSTRAINT pk_mark PRIMARY KEY ("idMark");

 

--

-- TOC entry 1863 (class 2606 OID 16435)

-- Name: pk_results; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: --

 

ALTER TABLE ONLY "Results"

ADD CONSTRAINT pk_results PRIMARY KEY ("idStudent", "idTeacher", "idSubject", "NumSemestr");

 

--

-- TOC entry 1859 (class 2606 OID 16422)

-- Name: pk_sessions; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: --

 

ALTER TABLE ONLY "Sessions"

ADD CONSTRAINT pk_sessions PRIMARY KEY ("NumGroup", "NumSemestr", "idSubject", "idTeacher");

 

--

-- TOC entry 1861 (class 2606 OID 16430)

-- Name: pk_students; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: --

 

ALTER TABLE ONLY "Students"

ADD CONSTRAINT pk_students PRIMARY KEY ("idStudent");

 

--

-- TOC entry 1855 (class 2606 OID 16443)

-- Name: pk_subject; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: --

 

ALTER TABLE ONLY "Subjects"

ADD CONSTRAINT pk_subject PRIMARY KEY ("idSubject");

 

--

-- TOC entry 1857 (class 2606 OID 16445)

-- Name: pk_teacher; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: --

 

ALTER TABLE ONLY "Teachers"

ADD CONSTRAINT pk_teacher PRIMARY KEY ("idTeacher");

 

--

-- TOC entry 1864 (class 2606 OID 16446)

-- Name: fk_dep_tea; Type: FK CONSTRAINT; Schema: public; Owner: postgres

--

 

ALTER TABLE ONLY "Teachers"

ADD CONSTRAINT fk_dep_tea FOREIGN KEY ("idDepartment") REFERENCES "Departments"("idDepartment");

 

--

-- TOC entry 1867 (class 2606 OID 16461)

-- Name: fk_res_stud; Type: FK CONSTRAINT; Schema: public; Owner: postgres

--

 

ALTER TABLE ONLY "Results"

ADD CONSTRAINT fk_res_stud FOREIGN KEY ("idStudent") REFERENCES "Students"("idStudent");

 

--

-- TOC entry 1868 (class 2606 OID 16466)

-- Name: fk_res_sub; Type: FK CONSTRAINT; Schema: public; Owner: postgres

--

 

ALTER TABLE ONLY "Results"

ADD CONSTRAINT fk_res_sub FOREIGN KEY ("idSubject") REFERENCES "Subjects"("idSubject");

 

 

--

-- TOC entry 1869 (class 2606 OID 16471)

-- Name: fk_res_tea; Type: FK CONSTRAINT; Schema: public; Owner: postgres

--

 

ALTER TABLE ONLY "Results"

ADD CONSTRAINT fk_res_tea FOREIGN KEY ("idTeacher") REFERENCES "Teachers"("idTeacher");

 

--

-- TOC entry 1865 (class 2606 OID 16451)

-- Name: fk_sess_subj; Type: FK CONSTRAINT; Schema: public; Owner: postgres

--

 

ALTER TABLE ONLY "Sessions"

ADD CONSTRAINT fk_sess_subj FOREIGN KEY ("idSubject") REFERENCES "Subjects"("idSubject");

 

--

-- TOC entry 1866 (class 2606 OID 16456)

-- Name: fk_sess_tea; Type: FK CONSTRAINT; Schema: public; Owner: postgres

--

 

ALTER TABLE ONLY "Sessions"

ADD CONSTRAINT fk_sess_tea FOREIGN KEY ("idTeacher") REFERENCES "Teachers"("idTeacher");

 

--

-- TOC entry 1990 (class 0 OID 0)

-- Dependencies: 5

-- Name: public; Type: ACL; Schema: -; Owner: postgres

--

 

REVOKE ALL ON SCHEMA public FROM PUBLIC;

REVOKE ALL ON SCHEMA public FROM postgres;

GRANT ALL ON SCHEMA public TO postgres;

GRANT ALL ON SCHEMA public TO PUBLIC;

 

-- Completed on 2014-08-04 23:54:32

--

-- PostgreSQL database dump complete

--

 

При внимательном рассмотрении трех сгенерированных скриптов видно, что основные команды по созданию таблиц и ограничений первичного и внешнего ключей почти не отличаются для этих трех СУБД. Дело в том, что язык SQL является стандартом для работы с базами данных и все современные реляционные базы данных стараются соблюдать этот стандарт. В основном, существенные различия заключаются только в используемых типах данных и небольших дополнениях, связанных с особенностями задания владельца таблицы (пользователя, который создал таблицу и, следовательно, имеет максимальные права для работы с ней), используемой кодировки и других настроек СУБД.

Нередко первоначальное проектирование выполняется с ошибками или недочетами (не все условия учтены, требуются новые столбцы или, напротив, какие-то столбцы являются лишними). Очевидно, что необходимы средства для обеспечения простого внесения изменений в таблицы. Этим средством является команда SQL ALTER TABLE, которую используют для корректировки списка столбцов таблицы и наложения разных ограничений как на отдельные столбцы, так и на таблицу в целом. Покажем на нескольких примерах, как можно использовать эту команду.

Выполнение SQL-команд осуществляется в оболочках с помощью специальных окон редактирования и выполнения SQL-скриптов. В dbForge Studio его можно создать с помощью меню «Новый»-> «SQL». В pgAdmin окно выполнения пользовательских запросов можно вызвать с помощью специальной кнопки на панели инструментов . После создания ограничения можно увидеть как объекты соответствующих таблиц в дереве элементов базы данных (ограничения или индексы):

 

Рис. 19. Ограничение на  

проверку баллов в dbForge для SQL Server.

Рис.20. Ограничение на проверку баллов в pgAdmin.

 

Пример 1. Добавим ограничение уникальности на название кафедры в таблице Departments:

MS SQL Server, MySQL:

ALTER TABLE Departments ADD CONSTRAINT un_title 

                   UNIQUE (TitleDepartment);

 

PostgreSQL (отличием является заключением в кавычки имен таблиц, ограничения, столбцов):

ALTER TABLE "Departments" ADD CONSTRAINT "un_title" 

                     UNIQUE ("TitleDepartment");

 

Пример 2. Требуется добавить ограничение проверки условия для таблицы оценок, означающее, что нижняя граница баллов должна быть меньше верхней.

MS SQL Server:

ALTER TABLE Marks ADD CONSTRAINT check_balls 

             CHECK (LowBalls<HighBalls);

 

Для MySQL эта команда выполняется, но как таковой объект базы данных не создается.

 

PostgreSQL:

ALTER TABLE "Marks" ADD CONSTRAINT "check_balls" 

               CHECK ("LowBalls"<"HighBalls");

 

Пример 3. Требуется добавить ограничение проверки условия для поля телефона кафедры – телефон должен состоять из 7 цифр и иметь формат «ххххх-хх». Можно также, как и в предыдущем примере, добавить ограничение CHECK. Однако мы для демонстрации возможностей команды ALTER TABLE сначала удалим столбец телефона кафедры, а потом добавим новый столбец с учетом ограничения. В телефоне первая цифра 2 или 5, остальные цифры могут быть любыми. Для этого используется конструкция языка LIKE, задающая шаблон записи телефона (для PostgreSQL это конструкция SIMILAR TO):

 

MS SQL Server, MySQL:

ALTER TABLE Departments DROP COLUMN PhoneDepartment;

ALTER TABLE Departments ADD PhoneDepartment VARCHAR(9) CHECK 

(PhoneDepartment LIKE '[2,5][0-9][0-9]-[0-9][0-9]-[0-9][0-9]');

 

PostgreSQL:

ALTER TABLE "Departments" DROP COLUMN "PhoneDepartment";

ALTER TABLE "Departments" ADD "PhoneDepartment" text CHECK

("PhoneDepartment" SIMILAR TO 

              '(2|5)[0-9][0-9]-[0-9][0-9]-[0-9][0-9]');

 

Пример 4. Введем ограничение на согласованность баллов и оценки в таблице результатов сессии. Будем полагать для простоты, что в таблицу заносятся только положительные результаты сдачи зачетов и экзаменов. Таким образом, оценка должна быть только 3, 4 или 5. При этом должна быть учтена согласованность баллов исходя из шкалы принятой балльно-рейтинговой системы:

MS SQL Server, MySQL:

ALTER TABLE Results ADD CONSTRAINT ch_res_marks CHECK (Mark IN (3,4,5) AND ((Mark=3 AND Balls BETWEEN 55 AND 70) OR (Mark=4 AND Balls BETWEEN 71 AND 85) OR (Mark=5 AND Balls BETWEEN 86 AND 100)));

 PostgreSQL:

ALTER TABLE "Results" ADD CONSTRAINT "ch_res_marks" CHECK ("Mark" IN (3,4,5) AND (("Mark"=3 AND "Balls" BETWEEN 55 AND 70) OR ("Mark"=4 AND "Balls" BETWEEN 71 AND 85) OR ("Mark"=5 AND "Balls" BETWEEN 86 AND 100)));

 

Заметим, что для аналогичных целей была предназначена таблица Marks.

В дальнейшем мы будем использовать ее.

Пример 5. В MySQL иногда требуется явно указать кодировку данных таблиц. Это также можно сделать с помощью команды ALTER TABLE:

ALTER TABLE `Departments` CONVERT TO CHARACTER SET utf8;