|
Помогите пожалуйста заполнить таблицу Otsutstvovavhie
Вот код, она внизу
create database ZA4ET;
CREATE TABLE IF NOT EXISTS Groups (
Code INT AUTO_INCREMENT COMMENT 'Код группы',
Name VARCHAR(52) NOT NULL COMMENT 'Название группы',
PRIMARY KEY (Code)
)ENGINE=innoDB character set utf8 collate utf8_general_ci;
CREATE TABLE IF NOT EXISTS Prichina (
Code INT AUTO_INCREMENT COMMENT 'Код причины',
Opisanie VARCHAR(100) NOT NULL default '' COMMENT 'Описание',
PRIMARY KEY (Code)
)ENGINE=innoDB character set utf8 collate utf8_general_ci;
CREATE TABLE IF NOT EXISTS Disciplins (
Code INT AUTO_INCREMENT COMMENT 'Код дисциплины',
Opisanie VARCHAR(50) NOT NULL COMMENT 'Название дисциплины',
PRIMARY KEY (Code)
)ENGINE=innoDB character set utf8 collate utf8_general_ci;
CREATE TABLE IF NOT EXISTS Students (
Code INT AUTO_INCREMENT COMMENT 'Код студента',
Surname VARCHAR(30) NOT NULL COMMENT 'Фамилия',
Name VARCHAR(29) NOT NULL COMMENT 'Имя',
Otchestvo VARCHAR(31) NOT NULL COMMENT 'Отчество',
GroupsCode INT(10) NOT NULL COMMENT 'Код группы',
PRIMARY KEY (Code),
FOREIGN KEY (GroupsCode) REFERENCES Groups (Code) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=innoDB character set utf8 collate utf8_general_ci;
CREATE TABLE IF NOT EXISTS DataZaniatia (
Code INT AUTO_INCREMENT COMMENT 'Код занятия',
DataZan VARCHAR(35) NOT NULL COMMENT 'Дата занятия',
GroupsCode INT(10) NOT NULL COMMENT 'Код группы',
DisciplinsCode INT(13) NOT NULL COMMENT 'Код дисциплины',
PRIMARY KEY (Code),
FOREIGN KEY (GroupsCode) REFERENCES Groups (Code) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (DisciplinsCode) REFERENCES Disciplins (Code) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=innoDB character set utf8 collate utf8_general_ci;
CREATE TABLE IF NOT EXISTS Otsutstvovavhie (
Code INT AUTO_INCREMENT COMMENT 'Код отсутствующего',
ZanCode INT(15) NOT NULL COMMENT 'Код занятия',
StudentsCode INT(14) NOT NULL COMMENT 'Код студента',
PrichinaCode INT(12) NOT NULL COMMENT 'Код дисциплины',
PRIMARY KEY (Code),
FOREIGN KEY (ZanCode) REFERENCES DataZaniatia (Code) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (StudentsCode) REFERENCES Students (Code) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (PrichinaCode) REFERENCES Prichina (Code) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=innoDB character set utf8 collate utf8_general_ci;
INSERT INTO Groups VALUES ('1','IB1'),
('2','IB2'),
('3','IS1'),
('4','IS2'),
('5','F1'),
('6','F2'),
('7','OS1'),
('8','OS2'),
('9','A1'),
('10','A2');
INSERT INTO Prichina VALUES ('1','Prospal'),
('2','Bil_v_poleklinnike'),
('3','Probka'),
('4','Bolel_givot'),
('5','Zabil_studak');
INSERT INTO Disciplins VALUES ('1','Russkii'),
('2','Fizika'),
('3','Fizra'),
('4','Matematika'),
('5','English'),
('6','Ximia'),
('7','Informatika'),
('8','Istoria'),
('9','Literatura'),
('10','Biologia');
INSERT INTO Students VALUES ('1','Tihonov','Ilia','Borisovich','1'),
('2','Medvedev','Ilia','Antonovich','2'),
('3','Dolmatov','Anton','Aleksandrovich','3'),
('4','Golopolosov','Nikolai','Denisovich','4'),
('5','Girinovskii','Denis','Borisovich','5'),
('6','Volia','Maksim','Nikolaevich','6'),
('7','Naumov','Ilia','Maksimovich','7'),
('8','Dgigurda','Nikita','Evgenevich','8'),
('9','Davidov','Ruslan','Borisovich','9'),
('10','Kartashov','Konstantin','Nikolaevich','10'),
('11','Fedorv','Nikolai','Aleksandrovich','1'),
('12','Gigulin','Gleb','Denisovich','2'),
('13','Klopov','Denis','Maksimovich','3'),
('14','Guravlev','Vladislav','Borisovich','4'),
('15','Karnauh','Maksim','Aleksandrovich','5'),
('16','Elanev','Anton','Vladimirovich','6'),
('17','Nikitin','Andrei','Antonovich','7'),
('18','Lubchenko','Konstantin','Vladimirovich','8'),
('19','Tihonov','Maksim','Borisovich','9'),
('20','Rakov','Nikita','Evgenevich','10'),
('21','Riabova','Anna','Grishina','1'),
('22','Sky','Viktoria','Borisovna','2'),
('23','Seminovich','Karina','Denisovna','3'),
('24','Sambuka','Ksenia','Andreeva','4'),
('25','Grey','Anastasia','Aleksandrovna','5'),
('26','Dainenko','Maria','Nikolaevna','6'),
('27','Tihonova','Alena','Borisovna','7'),
('28','Dobrodushnaia','Galina','Nikolaivna','8'),
('29','Kuraeva','Ulia','Maksimovna','9'),
('30','Soboleva','Sonia','Andreeva','10');
INSERT INTO DataZaniatia VALUES ('1','01_11_2013','1','1'),
('2','01_11_2013','1','1'),
('3','01_11_2013','1','1'),
('4','01_11_2013','2','2'),
('5','01_11_2013','2','2'),
('6','01_11_2013','2','2'),
('7','01_11_2013','3','3'),
('8','01_11_2013','3','3'),
('9','01_11_2013','3','3'),
('10','01_11_2013','4','4'),
('11','01_11_2013','4','4'),
('12','01_11_2013','4','4'),
('13','01_11_2013','5','5'),
('14','01_11_2013','5','5'),
('15','01_11_2013','5','5'),
('16','01_11_2013','6','6'),
('17','01_11_2013','6','6'),
('18','01_11_2013','6','6'),
('19','01_11_2013','7','7'),
('20','01_11_2013','7','7'),
('21','01_11_2013','7','7'),
('22','01_11_2013','8','8'),
('23','01_11_2013','8','8'),
('24','01_11_2013','8','8'),
('25','01_11_2013','9','9'),
('26','01_11_2013','9','9'),
('27','01_11_2013','9','9'),
('28','01_11_2013','10','10'),
('29','01_11_2013','10','10'),
('30','01_11_2013','10','10');
INSERT INTO Otsutstvovavhie VALUES ('1','?','?','?'),
('2','','',''),
('3','','',''),
('4','','',''),
('5','','',''),
('6','','',''),
('7','','',''),
('8','','',''),
('9','','',''),
('10','','',''),
...
('90','','','');
|
