wpisywanie danych do tabeli, MySQL Opcje

[syksyk]

podczas tworzenia tabeli wyskakuje mi taki oto blad:



ktos wie co powinienem zrobic??
dotyczy to tabeli order (opis tutaj http://lisp.vse.cz/pkdd99/berka.htm )
z 7 pozostalymi tabelami nie bylo zadngo problemu

Ten post edytował syksyk 2.02.2007, 00:40:05

[Cienki1980]

Zmień nazwę tabeli. ORDER jest w końcu poleceniem MySQL'a więc może mu coś nie pasować. Ewentualnie weź nazwę tabeli wsadź w jakieś ciapki ( ` lub ' lub " )

[syksyk]

oooo zadzialalo
no to teraz z innej beczki :]

[SQL] pobierz, plaintext 
CREATE TABLE district (
A1 TINYINT UNSIGNED,
   A2 CHAR(25) NOT NULL,
   A3 CHAR(25) NOT NULL,
   A4 MEDIUMINT UNSIGNED NOT NULL,
   A5 TINYINT UNSIGNED NOT NULL,
   A6 TINYINT UNSIGNED NOT NULL,
   A7 TINYINT UNSIGNED NOT NULL,
   A8 TINYINT UNSIGNED NOT NULL,
   A9 TINYINT UNSIGNED ,
   A10 DECIMAL(5,1) NOT NULL,
   A11 MEDIUMINT UNSIGNED NOT NULL,
   A12 DECIMAL(4,2) NOT NULL ,
   A13 DECIMAL(4,2) NOT NULL ,
   A14 SMALLINT UNSIGNED NOT NULL ,
   A15 MEDIUMINT UNSIGNED ,
   A16 MEDIUMINT UNSIGNED NOT NULL,
PRIMARY KEY (A1)
) ENGINE=INNODB;
 
CREATE TABLE account (
   account_id SMALLINT UNSIGNED,
   A1 TINYINT UNSIGNED,
   INDEX A1(A1),
   frequency CHAR(25) NOT NULL,
   date integer NOT NULL,
PRIMARY KEY (account_id),
constraint r_o FOREIGN KEY(A1) REFERENCES district(A1) ON UPDATE CASCADE ON DELETE
RESTRICT
 ) ENGINE=INNODB;
 
CREATE TABLE client (
   client_id SMALLINT UNSIGNED,
   birth_number CHAR(6) NOT NULL,
   A1 TINYINT UNSIGNED,
   INDEX A1(A1),    
PRIMARY KEY (client_id) ,
CONSTRAINT k_o FOREIGN KEY(A1) REFERENCES district(A1) ON UPDATE CASCADE ON DELETE
RESTRICT
) ENGINE=INNODB;
 
CREATE TABLE disp (
   disp_id SMALLINT UNSIGNED,
   client_id SMALLINT UNSIGNED,
   account_id SMALLINT UNSIGNED,
   type ENUM('OWNER', 'DISPONENT') NOT NULL,
PRIMARY KEY (disp_id) ,
CONSTRAINT d_r FOREIGN KEY(account_id) REFERENCES account(account_id) ON UPDATE CASCADE ON DELETE
RESTRICT,
CONSTRAINT d_k FOREIGN KEY(client_id) REFERENCES client(client_id) ON UPDATE CASCADE ON DELETE
RESTRICT
 ) ENGINE=INNODB;
 
CREATE TABLE card ( 
   card_id SMALLINT UNSIGNED,
   disp_id SMALLINT UNSIGNED,
   type CHAR(10) NOT NULL,
   issued INT UNSIGNED NOT NULL,
PRIMARY KEY (card_id),
CONSTRAINT ka_d FOREIGN KEY(disp_id) REFERENCES disp(disp_id) ON UPDATE CASCADE ON
DELETE RESTRICT
 ) ENGINE=INNODB;
 
CREATE TABLE loan ( 
   loan_id SMALLINT UNSIGNED,
   account_id SMALLINT UNSIGNED,
   date int UNSIGNED NOT NULL,
   amount INT UNSIGNED NOT NULL,
   duration SMALLINT UNSIGNED NOT NULL,
   payments DECIMAL(10,2)  NOT NULL,
   STATUS CHAR(1)  NOT NULL,
PRIMARY KEY (loan_id) ,
CONSTRAINT p_r FOREIGN KEY(account_id) REFERENCES account(account_id) ON UPDATE CASCADE ON DELETE
RESTRICT
) TYPE=INNODB;
 
CREATE TABLE trans ( 
   trans_id INT UNSIGNED,
   account_id SMALLINT UNSIGNED,
   date int UNSIGNED NOT NULL,
   type CHAR(25)  NOT NULL,
   operation CHAR(25),
   amount DECIMAL(10,2)  NOT NULL,
   balance DECIMAL(10,2) NOT NULL,
   k_symbol CHAR(25) ,
   bank CHAR(2) ,
   account char(8),
PRIMARY KEY (trans_id) ,
CONSTRAINT t_r FOREIGN KEY(account_id) REFERENCES account(account_id) ON UPDATE CASCADE ON DELETE
RESTRICT
) TYPE=INNODB;
 
CREATE TABLE order_ ( 
   order_id SMALLINT UNSIGNED,
   account_id SMALLINT UNSIGNED,
   bank_to CHAR(2) NOT NULL,
   account_to int UNSIGNED NOT NULL,
   amount DECIMAL(10,2)  NOT NULL,
   k_symbol CHAR(25) ,
PRIMARY KEY (order_id) ,
CONSTRAINT z_r FOREIGN KEY(account_id) REFERENCES account(account_id) ON UPDATE CASCADE ON DELETE
RESTRICT
) TYPE=INNODB;
[SQL] pobierz, plaintext 

tak stworzylem tabele. i ok jest ich tyle ile ma byc. ale teraz chce te tabele wypelnic danymi ktore mam w pliku *.asc.
wyglada to mniej wiecej tak:
A1;A2;A3;A4;A5;A6;A7;A8;A9;A10;A11;A12;A13;A14;A15;A16
1;"Hl.m. Praha";"Prague";1204953;0;0;0;1;1;100.0;12541;0.29;0.43;167;85677;99107
2;"Benesov";"central Bohemia";88884;80;26;6;2;5;46.7;8507;1.67;1.85;132;2159;2674
3;"Beroun";"central Bohemia";75232;55;26;4;1;5;41.7;8980;1.95;2.21;111;2824;2813
4;"Kladno";"central Bohemia";149893;63;29;6;2;6;67.4;9753;4.64;5.05;109;5244;5892
5;"Kolin";"central Bohemia";95616;65;30;4;1;6;51.4;9307;3.85;4.43;118;2616;3040

to tylko czesc z tego pliku
i pytanie: jak te dane tam wpakowac :| ? ? ?

Ten post edytował syksyk 2.02.2007, 00:34:01

[mokry]

Cóż ja bym na Twoim miejscu zrobił sobie małą klasę, która ładowałaby takie pliczki do bazy danych...
Nie jest to trudne, trzeba tylko troszeczkę pobawić się z tablicami

Ew. ręcznie, tylko nie wyobrażam sobie tego przy ilości kilku tysięci wpisów :/

[strife]

tak stworzylem tabele. i ok jest ich tyle ile ma byc. ale teraz chce te tabele wypelnic danymi ktore mam w pliku *.asc.
wyglada to mniej wiecej tak:
A1;A2;A3;A4;A5;A6;A7;A8;A9;A10;A11;A12;A13;A14;A15;A16
1;"Hl.m. Praha";"Prague";1204953;0;0;0;1;1;100.0;12541;0.29;0.43;167;85677;99107
2;"Benesov";"central Bohemia";88884;80;26;6;2;5;46.7;8507;1.67;1.85;132;2159;2674
3;"Beroun";"central Bohemia";75232;55;26;4;1;5;41.7;8980;1.95;2.21;111;2824;2813
4;"Kladno";"central Bohemia";149893;63;29;6;2;6;67.4;9753;4.64;5.05;109;5244;5892
5;"Kolin";"central Bohemia";95616;65;30;4;1;6;51.4;9307;3.85;4.43;118;2616;3040

Poczytaj w manualu o string'ach, m.in przyda Ci się metoda explode" title="Zobacz w manualu php" target="_manual i pochodne. Następnie stwórz sobie jakąś klase/funkcję do dodawania tak przedstawionych danych do bazy.

Pozdrawiam!

[syksyk]

ok wprowadzile sobie dane za pomoca:

[SQL] pobierz, plaintext 
LOAD DATA INFILE 'c:/bank/district.asc' INTO TABLE district FIELDS terminated BY ';'
OPTIONALLY ENCLOSED BY '"' ;
 
LOAD DATA INFILE 'c:/bank/account.asc' INTO TABLE account FIELDS terminated BY ';'
OPTIONALLY ENCLOSED BY '"' ;
 
LOAD DATA INFILE 'c:/bank/client.asc' INTO TABLE client FIELDS terminated BY ';'
OPTIONALLY ENCLOSED BY '"' ;
 
LOAD DATA INFILE 'c:/bank/disp.asc' INTO TABLE disp FIELDS terminated BY
';'OPTIONALLY ENCLOSED BY '"' LINES Terminated BY '\r\n' ;
 
LOAD DATA INFILE 'c:/bank/order.asc' INTO TABLE order_ FIELDS terminated BY '\;'
OPTIONALLY ENCLOSED BY '"' LINES Terminated BY '\r\n' ;
 
LOAD DATA INFILE 'c:/bank/loan.asc' INTO TABLE loan FIELDS terminated BY '\;'
OPTIONALLY ENCLOSED BY '"' LINES Terminated BY '\r\n' IGNORE 1 LINES; 
 
LOAD DATA INFILE 'c:/bank/trans.asc' INTO TABLE trans FIELDS terminated BY '\;'
OPTIONALLY ENCLOSED BY '"' LINES Terminated BY '\r\n' IGNORE 1 LINES  ;
[SQL] pobierz, plaintext 

i niby wszystko jest ok ale... w jednym pliku mam takie cos:

1005;9285;"classic";931107 00:00:00
104;588;"classic";940119 00:00:00
747;4915;"classic";940205 00:00:00
70;439;"classic";940208 00:00:00
577;3687;"classic";940215 00:00:00
377;2429;"classic";940303 00:00:00
721;4680;"junior";940405 00:00:00
437;2762;"classic";940601 00:00:00
188;1146;"classic";940619 00:00:00

sa to odpowiednio:
"card_id";"disp_id";"type";"issued"
i po wpisaniu:

[SQL] pobierz, plaintext 
LOAD DATA INFILE 'c:/bank/card.asc' INTO TABLE card FIELDS terminated BY ';' OPTIONALLY ENCLOSED BY '"' ;
[SQL] pobierz, plaintext 

mysql wywala takie cos:


Ten post edytował syksyk 2.02.2007, 11:11:14