· 5 years ago · Jun 12, 2020, 12:38 PM
1CREATE DATABASE IF NOT EXISTS `clinica`;
2USE `clinica`;
3
4CREATE TABLE IF NOT EXISTS `ambulatorios` (
5 `idambulatorios` int(10) unsigned NOT NULL AUTO_INCREMENT,
6 `numero` varchar(45) NOT NULL,
7 `andar` int(11) NOT NULL,
8 `capacidade` int(11) NOT NULL,
9 PRIMARY KEY (`idambulatorios`)
10);
11
12REPLACE INTO `ambulatorios` (`numero`, `andar`, `capacidade`) VALUES
13 ('1', 1, 30),
14 ('2', 1, 25),
15 ('3', 2, 40),
16 ('4', 2, 30),
17 ('5', 1, 25),
18 ('6', 3, 50),
19 ('7', 3, 50);
20
21CREATE TABLE IF NOT EXISTS `consultas` (
22 `idmedicos` int(10) unsigned NOT NULL,
23 `idpaciente` int(10) unsigned NOT NULL,
24 `idambulatorios` int(10) unsigned NOT NULL,
25 `dia` date NOT NULL,
26 `hora` time NOT NULL,
27 `iddoencas` int(5) unsigned NOT NULL,
28 KEY `fkIdx_53` (`idmedicos`),
29 KEY `fkIdx_59` (`idpaciente`),
30 FOREIGN KEY (`idmedicos`) REFERENCES `medicos` (`idmedicos`),
31 FOREIGN KEY (`idpaciente`) REFERENCES `pacientes` (`idpaciente`)
32) ENGINE=InnoDB DEFAULT CHARSET=latin1;
33
34REPLACE INTO `consultas` (`idmedicos`, `idpaciente`, `idambulatorios`, `dia`, `hora`, `iddoencas`) VALUES
35 (6, 12, 5, '2017-06-03', '08:30:00', 4),
36 (12, 12, 2, '2016-08-05', '17:00:00', 1),
37 (12, 12, 1, '2016-11-11', '16:00:00', 3),
38 (6, 12, 5, '2016-11-03', '08:30:00', 3),
39 (12, 12, 2, '2016-04-02', '17:00:00', 4),
40 (12, 12, 1, '2016-12-21', '16:00:00', 3),
41 (7, 11, 1, '2017-05-15', '09:00:00', 3),
42 (11, 11, 3, '2016-04-05', '17:30:00', 6),
43 (11, 11, 2, '2016-02-12', '16:30:00', 9),
44 (7, 11, 1, '2017-06-02', '09:00:00', 9),
45 (11, 11, 3, '2017-10-09', '17:30:00', 9),
46 (11, 11, 2, '2016-03-20', '16:30:00', 8),
47 (12, 10, 5, '2017-01-01', '13:00:00', 2),
48 (7, 10, 2, '2017-11-09', '09:30:00', 4),
49 (10, 10, 1, '2017-05-25', '08:30:00', 7),
50 (10, 10, 3, '2016-03-23', '17:00:00', 2),
51 (12, 10, 5, '2017-06-29', '13:00:00', 7),
52 (7, 10, 2, '2016-10-15', '09:30:00', 2),
53 (10, 10, 1, '2016-12-15', '08:30:00', 7),
54 (10, 10, 3, '2017-12-15', '17:00:00', 9),
55 (9, 9, 4, '2017-03-17', '12:30:00', 5),
56 (6, 9, 4, '2016-01-09', '17:30:00', 6),
57 (9, 9, 1, '2016-02-18', '16:30:00', 4),
58 (9, 9, 3, '2017-08-10', '15:30:00', 5),
59 (9, 9, 4, '2017-07-24', '12:30:00', 4),
60 (6, 9, 4, '2016-02-22', '17:30:00', 1),
61 (9, 9, 1, '2016-05-19', '16:30:00', 7),
62 (9, 9, 3, '2017-04-24', '15:30:00', 9),
63 (5, 8, 3, '2016-09-18', '17:00:00', 5),
64 (8, 8, 3, '2016-03-17', '16:00:00', 9),
65 (8, 8, 2, '2016-02-16', '15:00:00', 9),
66 (5, 8, 3, '2016-08-16', '17:00:00', 8),
67 (8, 8, 3, '2017-02-18', '16:00:00', 5),
68 (8, 8, 2, '2016-08-06', '15:00:00', 1),
69 (7, 7, 2, '2017-02-11', '11:30:00', 8),
70 (5, 7, 2, '2016-01-14', '16:30:00', 9),
71 (7, 7, 2, '2016-03-05', '15:30:00', 1),
72 (7, 7, 1, '2017-04-27', '14:30:00', 8),
73 (7, 7, 2, '2016-04-02', '11:30:00', 6),
74 (5, 7, 2, '2016-01-23', '16:30:00', 8),
75 (7, 7, 2, '2017-12-04', '15:30:00', 0),
76 (7, 7, 1, '2016-11-16', '14:30:00', 7),
77 (6, 6, 1, '2016-01-03', '11:00:00', 6),
78 (4, 6, 1, '2016-09-01', '16:00:00', 9),
79 (6, 6, 1, '2017-10-05', '15:00:00', 8),
80 (6, 6, 3, '2017-11-21', '14:00:00', 1),
81 (6, 6, 1, '2016-10-20', '11:00:00', 1),
82 (4, 6, 1, '2016-11-27', '16:00:00', 4),
83 (6, 6, 1, '2017-10-25', '15:00:00', 6),
84 (6, 6, 3, '2016-03-18', '14:00:00', 9),
85 (5, 5, 5, '2016-11-13', '10:30:00', 7),
86 (3, 5, 5, '2017-07-11', '15:30:00', 8),
87 (9, 5, 1, '2016-07-21', '12:00:00', 0),
88 (5, 5, 3, '2016-03-05', '14:30:00', 6),
89 (5, 5, 3, '2016-01-08', '11:00:00', 7),
90 (5, 5, 2, '2017-12-26', '13:30:00', 10),
91 (5, 5, 2, '2017-08-20', '10:00:00', 6),
92 (5, 5, 5, '2016-06-13', '10:30:00', 3),
93 (3, 5, 5, '2017-07-20', '15:30:00', 5),
94 (9, 5, 1, '2017-09-08', '12:00:00', 6),
95 (5, 5, 3, '2017-12-05', '14:30:00', 5),
96 (5, 5, 3, '2017-09-08', '11:00:00', 6),
97 (5, 5, 2, '2016-09-28', '13:30:00', 7),
98 (5, 5, 2, '2016-06-29', '10:00:00', 6),
99 (4, 4, 4, '2017-03-27', '10:00:00', 9),
100 (8, 4, 3, '2017-03-24', '12:00:00', 5),
101 (2, 4, 4, '2016-09-08', '15:00:00', 1),
102 (9, 4, 3, '2017-07-12', '11:30:00', 10),
103 (4, 4, 2, '2017-03-20', '14:00:00', 4),
104 (4, 4, 2, '2017-07-08', '10:30:00', 4),
105 (4, 4, 1, '2017-01-08', '13:00:00', 5),
106 (4, 4, 1, '2016-02-17', '09:30:00', 3),
107 (4, 4, 4, '2017-11-22', '10:00:00', 1),
108 (8, 4, 3, '2017-06-04', '12:00:00', 5),
109 (2, 4, 4, '2017-02-28', '15:00:00', 4),
110 (9, 4, 3, '2016-01-05', '11:30:00', 3),
111 (4, 4, 2, '2017-07-04', '14:00:00', 4),
112 (4, 4, 2, '2017-12-22', '10:30:00', 1),
113 (4, 4, 1, '2017-04-22', '13:00:00', 4),
114 (4, 4, 1, '2017-07-29', '09:30:00', 7),
115 (3, 3, 3, '2016-07-22', '09:30:00', 2),
116 (1, 3, 3, '2016-04-27', '14:30:00', 9),
117 (8, 3, 2, '2017-05-13', '11:00:00', 9),
118 (3, 3, 1, '2017-12-22', '13:30:00', 8),
119 (3, 3, 1, '2016-09-15', '10:00:00', 4),
120 (3, 3, 3, '2017-10-24', '12:30:00', 4),
121 (3, 3, 3, '2017-01-20', '09:00:00', 1),
122 (3, 3, 3, '2016-11-28', '09:30:00', 0),
123 (1, 3, 3, '2017-09-06', '14:30:00', 9),
124 (8, 3, 2, '2016-01-02', '11:00:00', 5),
125 (3, 3, 1, '2017-10-25', '13:30:00', 7),
126 (3, 3, 1, '2016-08-01', '10:00:00', 2),
127 (3, 3, 3, '2017-03-15', '12:30:00', 6),
128 (3, 3, 3, '2016-08-01', '09:00:00', 5),
129 (2, 2, 2, '2017-03-24', '09:00:00', 6),
130 (10, 2, 2, '2016-10-18', '14:00:00', 6),
131 (8, 2, 1, '2016-01-19', '10:30:00', 0),
132 (2, 2, 3, '2017-03-14', '13:00:00', 4),
133 (2, 2, 3, '2017-06-25', '09:30:00', 8),
134 (2, 2, 2, '2017-03-20', '12:00:00', 10),
135 (2, 2, 2, '2017-12-07', '08:30:00', 3),
136 (2, 2, 2, '2017-08-24', '09:00:00', 8),
137 (10, 2, 2, '2016-07-26', '14:00:00', 9),
138 (8, 2, 1, '2016-06-13', '10:30:00', 1),
139 (2, 2, 3, '2017-04-26', '13:00:00', 8),
140 (2, 2, 3, '2016-02-05', '09:30:00', 6),
141 (2, 2, 2, '2016-06-13', '12:00:00', 7),
142 (2, 2, 2, '2017-01-18', '08:30:00', 7),
143 (1, 1, 1, '2016-03-21', '08:30:00', 5),
144 (11, 1, 1, '2016-03-08', '13:30:00', 1),
145 (8, 1, 3, '2017-07-07', '10:00:00', 3),
146 (1, 1, 2, '2017-07-14', '12:30:00', 2),
147 (1, 1, 2, '2017-02-12', '09:00:00', 2),
148 (1, 1, 1, '2017-02-12', '11:30:00', 2),
149 (1, 1, 1, '2016-06-01', '17:30:00', 6),
150 (1, 1, 1, '2016-09-18', '08:30:00', 1),
151 (11, 1, 1, '2016-06-03', '13:30:00', 9),
152 (8, 1, 3, '2017-11-28', '10:00:00', 2),
153 (1, 1, 2, '2017-03-29', '12:30:00', 2),
154 (1, 1, 2, '2016-03-22', '09:00:00', 4),
155 (1, 1, 1, '2016-03-13', '11:30:00', 6),
156 (1, 1, 1, '2017-12-07', '17:30:00', 6),
157 (1, 1, 1, '2019-11-20', '08:00:00', 2);
158
159CREATE TABLE IF NOT EXISTS `doencas` (
160 `iddoencas` int(5) unsigned NOT NULL AUTO_INCREMENT,
161 `descricao` varchar(50) DEFAULT NULL,
162 PRIMARY KEY (`iddoencas`)
163);
164
165 REPLACE INTO `doencas` (`descricao`) VALUES
166 ('Anemia'),
167 ('Gripe'),
168 ('Hepatite'),
169 ('Gengivite'),
170 ('Câncer'),
171 ('Diarréia'),
172 ('Sarampo'),
173 ('Diabetes'),
174 ('Faringite'),
175 ('Herpes'),
176 ('HPV'),
177 ('Depressão'),
178 ('Lesão Muscular'),
179 ('Osso quebrado'),
180 ('LER'),
181 ('Febre tifoide');
182
183CREATE TABLE IF NOT EXISTS `especialidades` (
184 `idespecialidades` int(5) unsigned NOT NULL AUTO_INCREMENT,
185 `descricao` varchar(50) DEFAULT NULL,
186 PRIMARY KEY (`idespecialidades`)
187);
188
189REPLACE INTO `especialidades` (`descricao`) VALUES
190 ('Cardiologia'),
191 ('Ortopedia'),
192 ('Dermatologia'),
193 ('Endoscopia'),
194 ('Clínico Geral'),
195 ('Imunologia'),
196 ('Geriatria'),
197 ('Pediatria'),
198 ('Oftalmologia'),
199 ('Pneumologia'),
200 ('Psiquiatria'),
201 ('Urologia'),
202 ('Neurologia'),
203 ('Infectologia');
204
205CREATE TABLE IF NOT EXISTS `funcionarios` (
206 `idfuncionario` int(10) unsigned NOT NULL AUTO_INCREMENT,
207 `idambulatorios` int(10) unsigned NOT NULL,
208 `nome` varchar(45) NOT NULL,
209 `idade` int(11) NOT NULL,
210 `sexo` char(1) NOT NULL,
211 `salario` float NOT NULL,
212 `cidade` varchar(45) NOT NULL,
213 PRIMARY KEY (`idfuncionario`,`idambulatorios`),
214 FOREIGN KEY (`idambulatorios`) REFERENCES `ambulatorios` (`idambulatorios`)
215);
216
217REPLACE INTO `funcionarios` (`idambulatorios`, `nome`, `idade`, `sexo`, `salario`, `cidade`) VALUES
218 (1, 'Fernando Sac\'Anna', 33, 'm', 2800, 'Salvador'),
219 (1, 'Paul Miss Era', 21, 'm', 1800, 'Salvador'),
220 (2, 'Ringo Estrela', 40, 'm', 2000, 'Camaçari'),
221 (2, 'Mick Jegue', 41, 'm', 3000, 'Aratu'),
222 (2, 'André Robber', 29, 'm', 2300, 'Valença'),
223 (3, 'Márcia Betrayer', 28, 'f', 3000, 'Camaçari'),
224 (3, 'Alex Travel', 19, 'm', 1800, 'Aratu'),
225 (2, 'Monica Lev', 29, 'f', 2100, 'Salvador'),
226 (2, 'Cris Bonde', 32, 'f', 3000, 'Valença'),
227 (2, 'Anália Cam', 23, 'f', 2500, 'Lauro de Freitas'),
228 (1, 'Cláudia Never', 27, 'f', 1955, 'Itaparica'),
229 (4, 'Faby Tricky', 41, 'f', 2600, 'Amélia Rodrigues'),
230 (4, 'Camila Speed', 18, 'f', 1500, 'Salvador'),
231 (5, 'Jô Acapombo', 33, 'f', 2400, 'Aratu');
232
233-- Copiando estrutura para tabela clinica2019.medicos
234CREATE TABLE IF NOT EXISTS `medicos` (
235 `idmedicos` int(10) unsigned NOT NULL AUTO_INCREMENT,
236 `idambulatorios` int(10) unsigned NOT NULL,
237 `nome` varchar(45) NOT NULL,
238 `crm` varchar(45) NOT NULL,
239 `salario` float NOT NULL,
240 `idade` int(11) NOT NULL,
241 `idespecialidades` int(5) unsigned NOT NULL,
242 PRIMARY KEY (`idmedicos`,`idambulatorios`),
243 FOREIGN KEY (`idambulatorios`) REFERENCES `ambulatorios` (`idambulatorios`)
244);
245
246
247REPLACE INTO `medicos` (`idambulatorios`, `nome`, `crm`, `salario`, `idade`, `idespecialidades`) VALUES
248 (1, 'João Souza', '1234', 6000, 34, 3),
249 (1, 'Paulo Cascata', '2341', 12000, 62, 2),
250 (2, 'Zico Mário', '3344', 9000, 30, 10),
251 (2, 'Nanda Ghost', '6661', 10000, 33, 4),
252 (3, 'Érica White', '9991', 4500, 28, 1),
253 (2, 'Márcio Mucky', '1171', 6000, 26, 1),
254 (3, 'Jorge Lafond', '4477', 7000, 34, 5),
255 (3, 'Miguel Black', '7171', 8800, 50, 2),
256 (1, 'Robin Scroque', '0199', 4000, 32, 2),
257 (2, 'Paula Tapioca', '0178', 4200, 28, 6),
258 (2, 'Bruce Simões', '9171', 3000, 44, 4),
259 (1, 'Liv Gossip', '8987', 3600, 22, 3),
260 (3, 'Ana Smith', '8811', 4800, 31, 4),
261 (1, 'jorge', '4566', 1000, 50, 5),
262 (1, 'Jorge de novo', '123456', 6000, 40, 14);
263
264CREATE TABLE IF NOT EXISTS `pacientes` (
265 `idpaciente` int(10) unsigned NOT NULL AUTO_INCREMENT,
266 `nome` varchar(45) NOT NULL,
267 `sexo` char(1) NOT NULL,
268 `idade` int(11) NOT NULL,
269 `doenca_inicial` varchar(50) DEFAULT NULL,
270 PRIMARY KEY (`idpaciente`)
271);
272
273REPLACE INTO `pacientes` (`nome`, `sexo`, `idade`, `doenca_inicial`) VALUES
274 ('Paty Franca', 'f', 33, 'gripe'),
275 ('Roberto Sinc', 'm', 18, 'miopia'),
276 ('Marc Iolan', 'm', 24, 'gonorréia'),
277 ('Fê Pascal', 'f', 19, 'herpes'),
278 ('John Greg', 'm', 61, 'pressão alta'),
279 ('Livia Casta', 'f', 15, ''),
280 ('Su Edden', 'f', 30, 'pneumonia'),
281 ('Cássia Paula', 'f', 40, ''),
282 ('Marcos das Antas', 'm', 55, ''),
283 ('Aline Santos', 'f', 22, ''),
284 ('Marcelo Ferreira', 'm', 41, ''),
285 ('Paulo Paulada', 'm', 29, ''),
286 ('Joana Dark', 'f', 21, ''),
287 ('Paulo Peroba', 'm', 8, ''),
288 ('Pâmela Zoeira', 'f', 19, ''),
289 ('Juliana Pazes', 'f', 28, ''),
290 ('João Knower', 'm', 43, 'cegueira'),
291 ('Leandro Leonardo', 'm', 13, 'gripe'),
292 ('Tiago da Gota', 'm', 71, 'cardiaco'),
293 ('Rosa Flor', 'f', 61, 'diabetes'),
294 ('Manoel Padeiro', 'm', 37, ''),
295 ('Caroline Pinheiro', 'm', 18, ''),
296 ('jorge misera', 'm', 45, 'febre alta'),
297 ('Vovo velhinha', 'f', 88, 'diarreia'),
298 ('carlos', 'm', 30, 'chegou melado'),
299 ('cazuza', 'f', 21, 'ficou rouco'),
300 ('Jorge Bahia de FEira', 'm', 40, 'catapora');
301
302
303CREATE TABLE IF NOT EXISTS `telefonesmedicos` (
304 `idtelefones` int(10) unsigned NOT NULL AUTO_INCREMENT,
305 `numero` varchar(15) NOT NULL,
306 `idmedicos` int(10) unsigned NOT NULL,
307 PRIMARY KEY (`idtelefones`),
308 FOREIGN KEY (`idmedicos`) REFERENCES `medicos` (`idmedicos`)
309);