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