SQLize | PHPize | SQLtest

CREATE TABLE patients ( id INT PRIMARY KEY AUTO_INCREMENT, last_name VARCHAR(20) NOT NULL, first_name VARCHAR(20) NOT NULL, middle_name VARCHAR(20) NOT NULL ); CREATE TABLE specialityDoctors ( id INT PRIMARY KEY AUTO_INCREMENT, speciality_doctor VARCHAR(30) NOT NULL ); CREATE TABLE doctors ( id INT PRIMARY KEY AUTO_INCREMENT, last_name VARCHAR(20) NOT NULL, first_name VARCHAR(20) NOT NULL, middle_name VARCHAR(20) NOT NULL, speciality_doctor_id INT NOT NULL, FOREIGN KEY (speciality_doctor_id) REFERENCES specialityDoctors (id) ); CREATE TABLE timeTags ( id INT PRIMARY KEY AUTO_INCREMENT, doctor_id INT NOT NULL, date_prm DATE NOT NULL, time_tag TIME NOT NULL, isBusy BOOLEAN DEFAULT FALSE, FOREIGN KEY (doctor_id) REFERENCES doctors (id) ); CREATE TABLE wishTable ( id INT PRIMARY KEY AUTO_INCREMENT, speciality_doctor_id INT NOT NULL, patient_id INT NOT NULL, id_time_tag INT DEFAULT NULL, FOREIGN KEY (speciality_doctor_id) REFERENCES doctors (id), FOREIGN KEY (patient_id) REFERENCES patients (id), FOREIGN KEY (id_time_tag) REFERENCES timeTags (id) ); -- CREATE TABLE appointments -- ( -- id INT PRIMARY KEY, -- doctor_id INT NOT NULL, -- patient_id INT NOT NULL, -- time_tags_id INT NOT NULL, -- FOREIGN KEY (doctor_id) REFERENCES doctors (id), -- FOREIGN KEY (patient_id) REFERENCES patients (id), -- FOREIGN KEY (time_tags_id) REFERENCES timeTags (id) -- ); INSERT specialityDoctors (speciality_doctor) VALUES ('Терапевт'), ('Отоларинголог'), ('Невропатолог'), ('Окулист'); INSERT doctors (last_name, first_name, middle_name, speciality_doctor_id) VALUES ('Кукунин', 'Владислав', 'Валерьевич', 1), ('Каменских', 'Елена', 'Федоровна', 2), ('Польская', 'Анна', 'Мартыновна', 1), ('Соколов', 'Даниил', 'Маркович', 2); INSERT patients (last_name, first_name, middle_name) VALUES ('Горемыко', 'Наталья', 'Борисовна'), ('Грац', 'Николай', 'Германович'), ('Акулова', 'Анастасия', 'Петровна'), ('Гололедова', 'Инна', 'Ивановна'); INSERT wishTable (speciality_doctor_id, patient_id) VALUES (2, 1), (2, 2), (3, 1), (1, 4), (1, 2), (4, 1); INSERT timeTags (doctor_id, date_prm, time_tag) VALUES (1, '2023-12-01', '08:00'), (1, '2023-12-01', '10:00'), (1, '2023-12-01', '12:00'), (1, '2023-12-01', '14:00'), (2, '2023-12-01', '08:00'), (2, '2023-12-01', '10:00'); SELECT "doctors" AS title; SELECT * FROM doctors; SELECT "specialityDoctors" AS title; SELECT * FROM specialityDoctors; SELECT "patients" AS title; SELECT * FROM patients; SELECT "timeTags" AS title; SELECT * FROM timeTags; SELECT "wishTable" AS title; SELECT * FROM wishTable; -- Таблица пожеланий пациентов на прием -- по определенной специальности врача SELECT "Пожелания пациентов по специальностям врачей" AS title; SELECT id, ( SELECT speciality_doctor FROM specialityDoctors WHERE id = wishTable.speciality_doctor_id ) AS 'Специальность врача', ( SELECT CONCAT(last_name, ' ', first_name, ' ', middle_name) FROM patients WHERE id = wishTable.patient_id ) AS 'Пациент', ( SELECT id FROM timeTags WHERE id = wishTable.id_time_tag ) AS 'ID записи к врачу' FROM wishTable;