මෙන්න එහනම් ඉතිරි code  ටික

 ටිකක් අපැහැදිලියි ගනන්  ගන්න  එපා හොදේ

අපි table 3   හදාගන්න විදිය බලමු හොදේ

 ඔයාල දැන් handout එකේ තියනවා  වගේ Publisher  කියන table  එක හදාගන්ඩ ඕනි මෙන්න ඒකට  code


ඔන්න හොද point  එකක් අවා  අපි table  හදනකොට order  එකට තමා  හදන්ඩ ඕනි මොකද සමහර table  හදනකොට කලින් ටබ්ලේ එක referanse  විදියට දෙනවා එතං එහම දෙන්න  ඒ table  එක තියෙන්න  ඕනි කියල ඔයාලට තෙරනවනේ

mysql> Create table Publisher(pubID varchar(5) not NULL,pubName text(50),city text(10),state text(2),contry text(10),SSN varchar(15) NOT NULL,PRIMARY KEY (pubID),UNIQUE KEY SSN(SSN));
Query OK, 0 rows affected (0.02 sec)

table  එක හැදුවම හරියට හැදිලද කියල බලාගන්න පුළුවන් code  එක මතකද

mysql> desc Publisher;
+---------+-------------+------+-----+---------+-------+
| Field        | Type            | Null    | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| pubID      | varchar(5)  | NO   | PRI    | NULL    |       |
| pubName | tinytext       | YES  |          | NULL    |       |
| city          | tinytext        | YES  |          | NULL    |       |
| state        | tinytext        | YES  |          | NULL    |       |
| contry     | tinytext        | YES   |          | NULL    |       |
| SSN       | varchar(15) | NO     | UNI | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)


mysql> CREATE TABLE Author( auID char(3) NOT NULL,name text,phone varchar(15) DEFAULT NULL,address varchar(50) DEFAULT NULL,state char(2),PRIMARY KEY (auID));
Query OK, 0 rows affected (0.00 sec)

mysql> desc Author;
+---------+-------------+------+-----+---------+-------+
| Field     | Type               | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| auID    | char(3)            | NO   | PRI | NULL  |       |
| name    | text                 | YES  |       | NULL   |       |
| phone   | varchar(15)    | YES  |       | NULL   |       |
| address | varchar(50)    | YES  |       | NULL   |       |
| state   | char(2)              | YES  |       | NULL  |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

CREATE TABLE Book(ID varchar(5) NOT NULL,Title varchar(54) DEFAULT NULL,type text,pubID varchar(5) NOT NULL,price float(4,2) DEFAULT NULL,yedsales int(11) DEFAULT NULL,auID varchar(5) NOT NULL,pubDate date DEFAULT NULL,PRIMARY KEY(ID),KEY auID(auID),CONSTRAINT Book_idfk_1 FOREIGN KEY (auID) REFERENCES Author (auID),CONSTRAINT fk_auid FOREIGN KEY (auID) REFERENCES Author (auID));
Query OK, 0 rows affected (0.02 sec)

mysql> desc Book;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID       | varchar(5)  | NO   | PRI | NULL    |       |
| Title    | varchar(54) | YES  |     | NULL    |       |
| type     | text        | YES  |     | NULL    |       |
| pubID   | varchar(5)  | NO   |     | NULL    |       |
| price     | float(4,2)  | YES  |     | NULL    |       |
| yedsales | int(11)     | YES  |     | NULL    |       |
| auID     | varchar(5)  | NO   | MUL | NULL    |       |
| pubDate  | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

එහනම් ඔන්න කට්ට කාලා  table  3 හදා ගත්තනේ දැන් එතං ඕකට data  දන්න  අර insert  කියල code  එක use  කෙරේ නැති එකනම් පරණ පිනක්
මේකට අපි පිට්න්  file  එක්ක තියන data  තමා insert  කරන්නේ  ඔන්න බලන්ඩකෝ

load data local infile 'path'  into table 'table name' 

 path  එක type  කෙරන්න කම්මැලි වගේම වරදින නිසාත් ලේසි ක්‍රමයක් කියල දේනම් මේක windows  වලත් වැඩ කරනවා හොදේ
එක තමා ඔයාලට add  කෙරන්න ඕනි file  එක drag  කරලා ඒ කියනේ ඇදල terminal  එකට දන්න  එතකොට path  එක එතනට add  වෙනවා  කරලම බලන්ඩකෝ 



mysql> load data local infile '/home/DSAD16011/Downloads/Publisher.sql' into table Publisher;
Query OK, 7 rows affected, 5 warnings (0.01 sec)
Records: 7  Deleted: 0  Skipped: 0  Warnings: 5

mysql> load data local infile '/home/DSAD16011/Downloads/Author_Table.sql' into table Author;
Query OK, 9 rows affected (0.00 sec)
Records: 9  Deleted: 0  Skipped: 0  Warnings: 0

mysql> load data local infile '/home/DSAD16011/Downloads/Book_table.sql' into table Book;
Query OK, 15 rows affected (0.00 sec)
Records: 15  Deleted: 0  Skipped: 0  Warnings: 0




mysql> select * from Publisher;
+-------+---------------------+------------+-------+---------+-------------+
| pubID | pubName                  | city             | state     | contry    | SSN         |
+-------+---------------------+------------+-------+---------+-------------+
|           | NULL                      | NULL         | NULL  | NULL    |             |
 |1389  | Algodata Infosystem  | Berkeley      | CA      | USA       | 845479592v
 |1756  | Ramona Publishers    | Dallas          | TX       | USA       | 811968706v
 |736   | New Moon Books     | Boston       | MA     | USA       | 943349641v
 |877   | Binnet & Hardley       | Washington    | CA      | USA       | 836521325v
 |9901  | GGG&G                  | Muchen          | PA       | Germany | 767211346v
| 9999  | Lucernc Publishing    | Paris              | KS     | France     | 753862175v  |
+-------+---------------------+------------+-------+---------+-------------+
7 rows in set (0.00 sec)

mysql> select * from Author;
+------+--------------------+----------+----------------------+-------+
| auID | name               | phone    | address              | state |
+------+--------------------+----------+----------------------+-------+
| 172  | Johnson White      | 408-7223 | 10932 Biggs Rd       | CR    |
| 213  | Marjorie Green     | 415-7020 | 30963 rd St #411     | CA    |
| 238  | Cheryl Carson      | 415-7723 | 589 Darwin Ln        | MA    |
| 267  | Michael O'Leary    | 408-2428 | 22 Cleveland Av # 14 | CA    |
| 341  | Meander Smith      | 913-0462 | 10 Mississippi Dr    | KS    |
| 409  | Abraham Bennet     | 415-9932 | 6223 Bateman St      | CA    |
| 427  | Ann Dull           | 415-7128 | 3410 Blonde St       | CA    |
| 486  | Charien Locksley   | 415-4620 | 18 Broadway Av       | OR    |
| 527  | Morningstar Greene | 615-2723 | 22 Graybar House Rd  | TX    |
+------+--------------------+----------+----------------------+-------+
9 rows in set (0.00 sec)

mysql> select * from Book;
+------+-----------------------------------------------------+--------------+-------+-------+----------+------+------------+
| ID   | Title                                               | type         | pubID | price | yedsales | auID | pubDate    |
+------+-----------------------------------------------------+--------------+-------+-------+----------+------+------------+
| BU03 | The Busy Executive's Database guide                 | Business     | 1389  | 19.99 |     4095 | 527  | 1991-09-12 |
| BU07 | You can combat Computer stress!                     | Business     | 877   |  2.99 |    18722 | 213  | 1992-06-30 |
| BU11 | Cooking With Computers                              | Business     | 9901  | 11.95 |     3876 | 172  | 1991-06-09 |
| BU83 | Straight talk about computers                       | Business     | 1389  | 19.99 |     4095 | 172  | 1991-07-22 |
| MC02 | The Gourmet Microwave                               | Mod_cook     | 877   |  2.99 |   222246 | 213  | 1992-06-18 |
| MC22 | Silicon Valley Gastronomic Treats                   | Mod_cook     | 1389  | 19.99 |     2032 | 409  | 1991-06-09 |
| PC03 | But is it user friendly?                            | Popular_comp | 1389  | 22.95 |     8780 | 486  | 1994-05-10 |
| PC88 | Secrets of silicon valley                           | Popular_comp | 1389  | 20.00 |     4095 | 409  | 1994-06-12 |
| PS09 | Is anger the Enemy?                                 | Psychology   | 736   | 10.95 |     2045 | 486  | 1991-06-15 |
| PS10 | Life Without fear                                   | Psychology   | 1389  |  7.00 |      111 | 341  | 1992-10-05 |
| PS33 | Prolonged data Deprivation four case Studies        | Psychology   | 9999  | 19.99 |    40722 | 341  | 1991-09-12 |
| PS77 | Emotional Security:A new Algorithm                  | Psychology   | 736   |  7.99 |     3336 | 486  | 1991-06-01 |
| TC21 | Onions, Leeks and Garlic;Cooking Secrets of the med | Trad_cook    | 877   | 20.95 |      375 | 213  | 1993-10-21 |
| TC40 | Fifty years in Buckingham palace Kitchen            | Trad_cook    | 1389  | 11.95 |    15096 | 427  | 1991-06-12 |
| TC77 | Sushi, Anyone?                                      | Trad_cook    | 877   | 14.99 |     4095 | 213  | 1992-06-08 |
+------+-----------------------------------------------------+--------------+-------+-------+----------+------+------------+
15 rows in set (0.00 sec)

මට මතක විදියට ඔචරයි වගේ කරේ 
උදේ කරපු C  ටිකත් දැන්නම් හොදේ