связи
20.04.2012, 00:47. Показов 726. Ответов 1
Создал базу в MySQL Workbench 5.2 CE, экспортировал в MySQL Query Browser, написал в том же "квери" запросы, всё работает (выводит запросы, заголовки столбцов, т.к. база была не заполнена) начал закидывать всё в php, и столкнулся с тем, что при выводе запроса, не выводятся данные... есть подозрения на то, что со связями, типом базы где-то что-то начудил, есть какая-нить инфа для чайников? или тот, кто может помочь в решении проблем.
| SQL | 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
| SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
CREATE SCHEMA IF NOT EXISTS `gibdd_db` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `gibdd_db` ;
-- -----------------------------------------------------
-- Table `gibdd_db`.`pts_znak`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gibdd_db`.`pts_znak` (
`number_pts` VARCHAR(45) NOT NULL ,
`znak` VARCHAR(45) NULL ,
PRIMARY KEY (`number_pts`) )
ENGINE = MyISAM;
-- -----------------------------------------------------
-- Table `gibdd_db`.`vladelec_pts`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gibdd_db`.`vladelec_pts` (
`id_vladelec` INT NOT NULL AUTO_INCREMENT ,
`number_pts` VARCHAR(45) NULL ,
PRIMARY KEY (`id_vladelec`) )
ENGINE = MyISAM;
-- -----------------------------------------------------
-- Table `gibdd_db`.`pts_vin`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gibdd_db`.`pts_vin` (
`number_pts` VARCHAR(45) NOT NULL ,
`vin` VARCHAR(45) NULL ,
PRIMARY KEY (`number_pts`) )
ENGINE = MyISAM;
-- -----------------------------------------------------
-- Table `gibdd_db`.`vin`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gibdd_db`.`vin` (
`vin` INT NOT NULL ,
`number_eng` VARCHAR(45) NULL ,
`id_color` INT NULL ,
`id_type` INT NULL ,
`id_category` INT NULL ,
`id_vin` INT NULL ,
PRIMARY KEY (`vin`) )
ENGINE = MyISAM;
-- -----------------------------------------------------
-- Table `gibdd_db`.`avto`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gibdd_db`.`avto` (
`number_eng` VARCHAR(45) NOT NULL ,
`power` FLOAT NULL ,
`vol` INT NULL ,
`id_avto` INT NULL ,
`id_vin` INT NULL ,
PRIMARY KEY (`number_eng`) )
ENGINE = MyISAM;
-- -----------------------------------------------------
-- Table `gibdd_db`.`avto_sprav`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gibdd_db`.`avto_sprav` (
`id_avto` INT NOT NULL AUTO_INCREMENT ,
`marka` VARCHAR(45) NULL ,
`model` VARCHAR(45) NULL ,
`year` YEAR NULL ,
PRIMARY KEY (`id_avto`) )
ENGINE = MyISAM
COMMENT = ' ';
-- -----------------------------------------------------
-- Table `gibdd_db`.`vin_color`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gibdd_db`.`vin_color` (
`id_color` INT NOT NULL AUTO_INCREMENT ,
`color` VARCHAR(45) NULL ,
PRIMARY KEY (`id_color`) )
ENGINE = MyISAM
COMMENT = ' ';
-- -----------------------------------------------------
-- Table `gibdd_db`.`vin_type`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gibdd_db`.`vin_type` (
`id_type` INT NOT NULL AUTO_INCREMENT ,
`type` VARCHAR(45) NULL ,
PRIMARY KEY (`id_type`) )
ENGINE = MyISAM;
-- -----------------------------------------------------
-- Table `gibdd_db`.`vin_category`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gibdd_db`.`vin_category` (
`id_category` INT NOT NULL AUTO_INCREMENT ,
`category` VARCHAR(45) NULL ,
PRIMARY KEY (`id_category`) )
ENGINE = MyISAM;
-- -----------------------------------------------------
-- Table `gibdd_db`.`driver`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gibdd_db`.`driver` (
`id_driver` INT NOT NULL AUTO_INCREMENT ,
`id_vladelec` INT NULL ,
`number_prav` VARCHAR(45) NULL ,
`id_strahovka` INT NULL ,
PRIMARY KEY (`id_driver`) )
ENGINE = MyISAM;
-- -----------------------------------------------------
-- Table `gibdd_db`.`vladelec`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gibdd_db`.`vladelec` (
`id_vladelec` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(45) NULL ,
`last_name` VARCHAR(45) NULL ,
`middle_name` VARCHAR(45) NULL ,
`date_of_b` DATE NULL ,
`city` VARCHAR(45) NULL ,
`street` VARCHAR(45) NULL ,
`number_house` INT NULL ,
PRIMARY KEY (`id_vladelec`) )
ENGINE = MyISAM;
-- -----------------------------------------------------
-- Table `gibdd_db`.`strahovka`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gibdd_db`.`strahovka` (
`id_strahovka` INT NOT NULL AUTO_INCREMENT COMMENT ' ' ,
`in` DATE NULL ,
`out` DATE NULL ,
`kasko` SMALLINT NULL ,
`osago` SMALLINT NULL ,
PRIMARY KEY (`id_strahovka`) )
ENGINE = MyISAM;
-- -----------------------------------------------------
-- Table `gibdd_db`.`protocol`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gibdd_db`.`protocol` (
`id_protocol` INT NOT NULL AUTO_INCREMENT ,
`date_violation` DATE NULL ,
`mesto` VARCHAR(45) NULL ,
`type_violation` VARCHAR(45) NULL ,
`summa_penalty` VARCHAR(45) NULL ,
`lishenie` VARCHAR(45) NULL ,
`date_pay` DATE NULL ,
PRIMARY KEY (`id_protocol`) )
ENGINE = MyISAM;
-- -----------------------------------------------------
-- Table `gibdd_db`.`penalty_protocol`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gibdd_db`.`penalty_protocol` (
`number_prav` INT NOT NULL ,
`id_protocol` INT NOT NULL ,
PRIMARY KEY (`id_protocol`, `number_prav`) ,
INDEX `fk_penalty_protocol_protocol1` (`id_protocol` ASC) )
ENGINE = MyISAM;
-- -----------------------------------------------------
-- Table `gibdd_db`.`number_prav`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gibdd_db`.`number_prav` (
`number_prav` VARCHAR(45) NOT NULL ,
`data_issue` DATE NULL ,
`issued_by` VARCHAR(45) NULL ,
`id_penalty` INT NULL ,
PRIMARY KEY (`number_prav`) )
ENGINE = MyISAM;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; |
|
запросы вот такие
| SQL | 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
| 1. Запрос о выводе владельца по номеру ПТС
SELECT Vladelec_pts.number_pts, vladelec.last_name, vladelec.name, vladelec.middle_name, vladelec.date_of_b
FROM vladelec_Pts INNER JOIN
Vladelec ON Vladelec_pts.ID_Vladelec = vladelec.ID_Vladelec
2. Запрос о выводе информации о нарушении и его участнике
SELECT vladelec.last_name, vladelec.name, vladelec.middle_name, number_prav.number_prav, protocol.ID_protocol, protocol.Date_Violation, protocol.Mesto, protocol.Type_vioation, protocol.Summa_Penalty, protocol.Lishenie, protocol.Date_pay
FROM number_prav INNER JOIN
Penalty_protocol ON number_prav.ID_Penalty = Penalty_protocol.ID_Penalty INNER JOIN
protocol ON Penalty_protocol.ID_protocol = protocol.ID_protocol INNER JOIN
Driver ON number_prav.number_prav = Driver.number_prav INNER JOIN
vladelec ON Driver.ID_vladelec = vladelec.ID_vladelec
3. Запрос о выводе информации об участнике нарушения и сумма штрафа
SELECT vladelec.last_name, vladelec.name, vladelec.middle_name, number_prav.number_prav, protocol.ID_protocol, protocol.Date_Violation, protocol.Mesto, protocol.Type_vioation, protocol.Summa_Penalty, protocol.Lishenie, protocol.Date_pay
FROM number_prav INNER JOIN
Penalty_protocol ON number_prav.ID_Penalty = Penalty_protocol.ID_Penalty INNER JOIN
protocol ON Penalty_protocol.ID_protocol = protocol.ID_protocol INNER JOIN
Driver ON number_prav.number_prav = Driver.number_prav INNER JOIN
vladelec ON driver.ID_vladelec = vladelec.ID_vladelec
WHERE protocol.summa_penalty >1500
4. Запрос по VIN коду информации о владельце и страховке
SELECT PTS_VIN.VIN, Vladelec.Last_Name, Vladelec.Name, Vladelec.Middle_Name, Strahovka.ID_Strahovka, Strahovka.in, Strahovka.out, Strahovka.KASKO, Strahovka.OSAGO
FROM PTS_VIN INNER JOIN
Vladelec_PTS ON PTS_VIN.Number_PTS = Vladelec_PTS.Number_PTS INNER JOIN
Vladelec ON Vladelec_PTS.ID_Vladelec = Vladelec.ID_Vladelec INNER JOIN
Driver ON Vladelec.ID_Vladelec = Driver.ID_Vladelec INNER JOIN
Strahovka ON Driver.ID_Strahovka = Strahovka.ID_Strahovka
5. Запрос по государственному номеру информации о машине
SELECT Avto.Number_eng, Avto.Power, Avto.Vol, Avto.Marka, Avto.Model, Avto.Year, pts_znak.znak, VIN.VIN
FROM Avto INNER JOIN
VIN ON Avto.Number_eng = VIN.Number_eng INNER JOIN
PTS_VIN ON VIN.VIN = PTS_VIN.VIN INNER JOIN
pts_znak ON PTS_VIN.Number_PTS = pts_znak.Number_PTS
6. Запрос по поиску владельцев определенных марок машин и моделей
SELECT Vladelec.Last_Name, Vladelec.Name, Vladelec.Middle_Name, Avto.Marka, Avto.Model, Avto.Year
FROM Avto INNER JOIN
VIN ON Avto.Number_eng = VIN.Number_eng INNER JOIN
PTS_VIN ON VIN.VIN = PTS_VIN.VIN INNER JOIN
Vladelec_PTS ON PTS_VIN.Number_PTS = Vladelec_PTS.number_PTS INNER JOIN
Vladelec ON Vladelec_PTS.ID_Vladelec = Vladelec.ID_Vladelec
WHERE ((Avto.Marka = 'Saab') OR (Avto.marka = 'Volvo'))
7. Запрос о наличии ОСАГО и КАСКО
SELECT Vladelec.Last_Name, Vladelec.Name, Vladelec.Middle_Name, Driver.Number_prav, Strahovka.KASKO
FROM Vladelec INNER JOIN
Driver ON Vladelec.ID_Vladelec = Driver.ID_Vladelec INNER JOIN
Strahovka ON Driver.ID_Strahovka = Strahovka.ID_Strahovka
WHERE (Strahovka.KASKO = N'??')
8. Запрос на добавление данных
INSERT INTO avto (Number_eng, POWER, Vol, Marka, Model, YEAR, ID_VIN)
VALUES('DSFFQQSF', '117', '1800', 'VW', 'Passat', '1989', '1')
9. Запрос на удаление данных
DELETE FROM avto WHERE number_eng='DSFFQQSF' |
|
0
|
(
