API tools faq
paste
Advertisement
andrejic89

MySQL 6/8

andrejic89
Mar 27th, 2017
86
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 9.14 KB | None | 0 0
raw download clone embed print report
  1. - obican (inner) join
  2. SELECT customer.first_name, customer.last_name, address.address
  3. FROM customer
  4. JOIN address
  5. ON address.address_id = customer.address_id
  6.  
  7. +-------------+--------------+----------------------------------------+
  8. | first_name  | last_name    | address                                |
  9. +-------------+--------------+----------------------------------------+
  10. | MARY        | SMITH        | 1913 Hanoi Way                         |
  11. | PATRICIA    | JOHNSON      | 1121 Loja Avenue                       |
  12. | LINDA       | WILLIAMS     | 692 Joliet Street                      |
  13. | BARBARA     | JONES        | 1566 Inegl Manor                       |
  14. | ELIZABETH   | BROWN        | 53 Idfu Parkway                        |
  15. | JENNIFER    | DAVIS        | 1795 Santiago de Compostela Way        |
  16. | MARIA       | MILLER       | 900 Santiago de Compostela Parkway     |
  17.  
  18.  
  19. •
  20. Inner Join je zapravo Join koji smo videli na prethodnom primeru. Takoreći,
  21. potpuno identičan efekat ima korišćenje ključne reči JOIN i INNER JOIN, i u oba
  22. slučaja biće selektovani redovi koji za definisane kolone imaju vrednosti u obe
  23. tabele. Ovo znači da, ako govorimo hipotetički, ukoliko neki od naših mušterija
  24. nemaju unetu adresu, oni neće biti prikazani u rezultatima.
  25.  
  26. ------------------------------------------------------------------------
  27.  
  28. - left join
  29. mysql> SELECT customer.first_name, customer.last_name, address.address, address.district, address.postal_code FROM customer LEFT JOIN address ON address.address_id = customer.address_id limit 10;
  30. +------------+-----------+------------------------------------+----------------+-------------+
  31. | first_name | last_name | address                            | district       | postal_code |
  32. +------------+-----------+------------------------------------+----------------+-------------+
  33. |            | SMITH     | 1913 Hanoi Way                     | Nagasaki       | NULL        |
  34. | PATRICIA   | JOHNSON   | 1121 Loja Avenue                   | California     | 17886       |
  35. | LINDA      | WILLIAMS  | 692 Joliet Street                  | Attika         | 83579       |
  36. | BARBARA    | JONES     | 1566 Inegl Manor                   | Mandalay       | 53561       |
  37. | ELIZABETH  | BROWN     | 53 Idfu Parkway                    | Nantou         | 42399       |
  38. | JENNIFER   | DAVIS     | 1795 Santiago de Compostela Way    | Texas          | 18743       |
  39. | MARIA      | MILLER    | 900 Santiago de Compostela Parkway | Central Serbia | 93896       |
  40. | SUSAN      | WILSON    | 478 Joliet Way                     | Hamilton       | 77948       |
  41. | MARGARET   | MOORE     | 613 Korolev Drive                  | Masqat         | 45844       |
  42. | DOROTHY    | TAYLOR    | 1531 Sal Drive                     | Esfahan        | 53628       |
  43. +------------+-----------+------------------------------------+----------------+-------------+
  44. 10 rows in set (0,00 sec)
  45.  
  46.  
  47. •
  48. Spajanje tabela definisano LEFT JOIN-om vratiće sve zapise iz leve tabele (tabele
  49. A), pa čak i ako neki od zapisa za tražene kolone nema vrednost
  50.  
  51. ------------------------------------------------------------------------
  52.  
  53. - right join
  54. mysql> SELECT customer.first_name, customer.last_name, address.address, address.district, address.postal_code FROM customer RIGHT JOIN address ON address.address_id = customer.address_id limit 10;
  55. +------------+-----------+---------------------------------+------------+-------------+
  56. | first_name | last_name | address                         | district   | postal_code |
  57. +------------+-----------+---------------------------------+------------+-------------+
  58. | NULL       | NULL      | 47 MySakila Drive               | Alberta    | NULL        |
  59. | NULL       | NULL      | 28 MySQL Boulevard              | QLD        |             |
  60. | NULL       | NULL      | 23 Workhaven Lane               | Alberta    |             |
  61. | NULL       | NULL      | 1411 Lillydale Drive            | QLD        |             |
  62. |            | SMITH     | 1913 Hanoi Way                  | Nagasaki   | NULL        |
  63. | PATRICIA   | JOHNSON   | 1121 Loja Avenue                | California | 17886       |
  64. | LINDA      | WILLIAMS  | 692 Joliet Street               | Attika     | 83579       |
  65. | BARBARA    | JONES     | 1566 Inegl Manor                | Mandalay   | 53561       |
  66. | ELIZABETH  | BROWN     | 53 Idfu Parkway                 | Nantou     | 42399       |
  67. | JENNIFER   | DAVIS     | 1795 Santiago de Compostela Way | Texas      | 18743       |
  68. +------------+-----------+---------------------------------+------------+-------------+
  69. 10 rows in set (0,00 sec)
  70.  
  71.  
  72.  
  73. •
  74. Slično kao kod prethodnog JOIN-a, sa RIGHT JOIN-om
  75. dobićemo sve zapise iz desne tabele (tabele B), i samo one
  76. koji imaju vrednosti za definisane kolone iz tabele A.
  77.  
  78. ------------------------------------------------------------------------
  79.  
  80. - cross join
  81. mysql> SELECT * FROM customer cross JOIN address limit 10;
  82. +-------------+----------+------------+-----------+-------------------------------------+------------+--------+---------------------+---------------------+------------+-------------------+----------+----------+---------+-------------+-------+---------------------------+---------------------+
  83. | customer_id | store_id | first_name | last_name | email                               | address_id | active | create_date         | last_update         | address_id | address           | address2 | district | city_id | postal_code | phone | location                  | last_update         |
  84. +-------------+----------+------------+-----------+-------------------------------------+------------+--------+---------------------+---------------------+------------+-------------------+----------+----------+---------+-------------+-------+---------------------------+---------------------+
  85. |           1 |        1 |            | SMITH     | MARY.SMITH@sakilacustomer.org       |          5 |      1 | 2006-02-14 22:04:36 | 2017-03-27 12:33:10 |          1 | 47 MySakila Drive | NULL     | Alberta  |     300 | NULL        |       |        >
  86. 2]c4\�vۍ��H@     | 2017-03-27 12:34:45 |
  87. |           2 |        1 | PATRICIA   | JOHNSON   | PATRICIA.JOHNSON@sakilacustomer.org |          6 |      1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 |          1 | 47 MySakila Drive | NULL     | Alberta  |     300 | NULL        |       |        >
  88. 2]c4\�vۍ��H@     | 2017-03-27 12:34:45 |
  89. |           3 |        1 | LINDA      | WILLIAMS  | LINDA.WILLIAMS@sakilacustomer.org   |          7 |      1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 |          1 | 47 MySakila Drive | NULL     | Alberta  |     300 | NULL        |       |        >
  90. 2]c4\�vۍ��H@     | 2017-03-27 12:34:45 |
  91. |           4 |        2 | BARBARA    | JONES     | BARBARA.JONES@sakilacustomer.org    |          8 |      1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 |          1 | 47 MySakila Drive | NULL     | Alberta  |     300 | NULL        |       |        >
  92. 2]c4\�vۍ��H@     | 2017-03-27 12:34:45 |
  93. |           5 |        1 | ELIZABETH  | BROWN     | ELIZABETH.BROWN@sakilacustomer.org  |          9 |      1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 |          1 | 47 MySakila Drive | NULL     | Alberta  |     300 | NULL        |       |        >
  94. 2]c4\�vۍ��H@     | 2017-03-27 12:34:45 |
  95. |           6 |        2 | JENNIFER   | DAVIS     | JENNIFER.DAVIS@sakilacustomer.org   |         10 |      1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 |          1 | 47 MySakila Drive | NULL     | Alberta  |     300 | NULL        |       |        >
  96. 2]c4\�vۍ��H@     | 2017-03-27 12:34:45 |
  97. |           7 |        1 | MARIA      | MILLER    | MARIA.MILLER@sakilacustomer.org     |         11 |      1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 |          1 | 47 MySakila Drive | NULL     | Alberta  |     300 | NULL        |       |        >
  98. 2]c4\�vۍ��H@     | 2017-03-27 12:34:45 |
  99. |           8 |        2 | SUSAN      | WILSON    | SUSAN.WILSON@sakilacustomer.org     |         12 |      1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 |          1 | 47 MySakila Drive | NULL     | Alberta  |     300 | NULL        |       |        >
  100. 2]c4\�vۍ��H@     | 2017-03-27 12:34:45 |
  101. |           9 |        2 | MARGARET   | MOORE     | MARGARET.MOORE@sakilacustomer.org   |         13 |      1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 |          1 | 47 MySakila Drive | NULL     | Alberta  |     300 | NULL        |       |        >
  102. 2]c4\�vۍ��H@     | 2017-03-27 12:34:45 |
  103. |          10 |        1 | DOROTHY    | TAYLOR    | DOROTHY.TAYLOR@sakilacustomer.org   |         14 |      1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 |          1 | 47 MySakila Drive | NULL     | Alberta  |     300 | NULL        |       |        >
  104. 2]c4\�vۍ��H@     | 2017-03-27 12:34:45 |
  105. +-------------+----------+------------+-----------+-------------------------------------+------------+--------+---------------------+---------------------+------------+-------------------+----------+----------+---------+-------------+-------+---------------------------+---------------------+
  106. 10 rows in set (0,00 sec)
  107.  
  108.  
  109.  
  110.  
  111. • Spajaju se sve kolone sa svim kolonama
  112.  
  113. ------------------------------------------------------------------------
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!