블로그 이미지

내 인생의 가장 젊은 날

내 인생에 있어서 가장 젊은 날, 젊기에 고민이 즐겁다. by 하늘치


'JOIN'에 해당되는 글 1건

  1. 2008.01.15 MySQL : select, update, alter, delete, drop...

MySQL : select, update, alter, delete, drop...


==============================================================
*
select c.customerid, c.name, o.orderid
from customers c, orders o join
orders on c.customerid=o.customerid;

select customers.customerid, customers.name, orders.orderid
from customers left join orders orders on customers.customerid=orders.customerid;




============= left join =================
p.314

left join ... on(where와 비슷한 기능)
left join ... using(사용할 공통항목을 넣어주는 곳.)
-----------------------------------------

mysql> select * from customers;
+------------+-----------------+--------------------+--------------+
| customerid | name            | address            | city         |
+------------+-----------------+--------------------+--------------+
|          3 | Julie Smith     | 25 Oak Street      | Airport West |
|          4 | Alan Wong       | 1/47 Haines Avenue | Box Hill     |
|          5 | Michelle Arthur | 357 North Road     | Yarraville   |
+------------+-----------------+--------------------+--------------+
3 rows in set (0.59 sec)


mysql> select * from orders;
+---------+------------+--------+------------+
| orderid | customerid | amount | date       |
+---------+------------+--------+------------+
|       1 |          5 |  69.98 | 2000-04-02 |
|       2 |          3 |  49.99 | 2000-04-15 |
|       3 |          4 |  74.98 | 2000-04-19 |
|       4 |          5 |  24.99 | 2000-05-01 |
+---------+------------+--------+------------+
4 rows in set (0.08 sec)


============ 결과의 차이 ==============

mysql> select customers.customerid, customers.name, orders.orderid
    -> from customers join orders orders on customers.customerid=orders.customerid;
+------------+-----------------+---------+
| customerid | name            | orderid |
+------------+-----------------+---------+
|          5 | Michelle Arthur |       1 |
|          3 | Julie Smith     |       2 |
|          4 | Alan Wong       |       3 |
|          5 | Michelle Arthur |       4 |
+------------+-----------------+---------+
4 rows in set (0.00 sec)


mysql> select customers.customerid, customers.name, orders.orderid
    -> from customers left join orders orders on customers.customerid=orders.customerid;
+------------+-----------------+---------+
| customerid | name            | orderid |
+------------+-----------------+---------+
|          3 | Julie Smith     |       2 |
|          4 | Alan Wong       |       3 |
|          5 | Michelle Arthur |       1 |
|          5 | Michelle Arthur |       4 |
+------------+-----------------+---------+
4 rows in set (0.00 sec)













mysql> select * from orders order by customerid;
+---------+------------+--------+------------+
| orderid | customerid | amount | date       |
+---------+------------+--------+------------+
|       2 |          3 |  49.99 | 2000-04-15 |
|       3 |          4 |  74.98 | 2000-04-19 |
|       1 |          5 |  69.98 | 2000-04-02 |
|       4 |          5 |  24.99 | 2000-05-01 |
+---------+------------+--------+------------+
4 rows in set (0.00 sec)



mysql> select customerid, avg(amount) from orders
    -> group by customerid;
+------------+-------------+
| customerid | avg(amount) |
+------------+-------------+
|          3 |   49.990002 |
|          4 |   74.980003 |
|          5 |   47.485002 |
+------------+-------------+


mysql> select customerid, avg(amount) from orders
    -> group by customerid
    -> having avg(amount)>=50;
+------------+-------------+
| customerid | avg(amount) |
+------------+-------------+
|          4 |   74.980003 |
+------------+-------------+
1 row in set (0.00 sec)











mysql> select * from orders;
+---------+------------+--------+------------+
| orderid | customerid | amount | date       |
+---------+------------+--------+------------+
|       1 |          5 |  69.98 | 2000-04-02 |
|       2 |          3 |  49.99 | 2000-04-15 |
|       3 |          4 |  74.98 | 2000-04-19 |
|       4 |          5 |  24.99 | 2000-05-01 |
+---------+------------+--------+------------+
4 rows in set (0.00 sec)

mysql> select customerid, amount
    -> from orders
    -> where amount = (select max(amount) from orders);
+------------+--------+
| customerid | amount |
+------------+--------+
|          4 |  74.98 |
+------------+--------+
1 row in set (0.00 sec)

=> 이 경우 하위 쿼리에서 단 하나의 값(최대 양)만이 리턴된다. 그 결과는 상위 쿼리에서 비교하기

위해 사용된다. 이런 것은 ANSI SQL의 조인을 사용해서는 멋지게 해낼 수 없는 쿼리이기 때문에 하위

쿼리 예로 좋은 것 같다.


mysql> select customerid, amount
    -> from orders
    -> order by amount desc
    -> limit 1;
+------------+--------+
| customerid | amount |
+------------+--------+
|          4 |  74.98 |
+------------+--------+
1 row in set (0.03 sec)

=> Limit에 의존..
















mysql> select isbn, title from books
    -> where not exists
    -> (select * from order_items where order_items.isbn=books.isbn);
+---------------+-------------+
| isbn          | title       |
+---------------+-------------+
| 0-672-31557-3 | Great Grace |
+---------------+-------------+
1 row in set (0.00 sec)

mysql> select * from books;
+---------------+----------------+---------------------------------------------------+-------+
| isbn          | author         | title                                             | price |
+---------------+----------------+---------------------------------------------------+-------+
| 0-672-31509-2 | Pruitt, et al. | Teach Yourself GIMP in 24 Hours                   | 24.99 |
| 0-672-31557-3 | Philips        | Great Grace                                       | 30.99 |
| 0-672-31697-8 | Michael Morgan | Java 2 for Professional Developers                | 34.99 |
| 0-672-31745-1 | Thomas Down    | Installing Debian GNU/Linux                       | 24.99 |
| 0-672-31769-9 | Thomas Schenk  | Caldera OpenLinux System Administration Unleashed | 49.99 |
+---------------+----------------+---------------------------------------------------+-------+
5 rows in set (0.00 sec)

mysql> select * from order_items;
+---------+---------------+----------+
| orderid | isbn          | quantity |
+---------+---------------+----------+
|       1 | 0-672-31697-8 |        2 |
|       2 | 0-672-31769-9 |        1 |
|       3 | 0-672-31509-2 |        1 |
|       3 | 0-672-31769-9 |        1 |
|       4 | 0-672-31745-1 |        3 |
+---------+---------------+----------+
5 rows in set (0.00 sec)












p.321 하위 쿼리를 임시테이블로 사용하기.

mysql> select * from
    -> (select customerid, name from customers where city='Box Hill')
    -> as box_hill_customers;
+------------+-----------+
| customerid | name      |
+------------+-----------+
|          4 | Alan Wong |
+------------+-----------+
1 row in set (0.03 sec)



mysql> select customerid, name from customers where city='Box Hill';
+------------+-----------+
| customerid | name      |
+------------+-----------+
|          4 | Alan Wong |
+------------+-----------+
1 row in set (0.00 sec)











mysql> select * from books;
+---------------+----------------+---------------------------------------------------+-------+
| isbn          | author         | title                                             | price |
+---------------+----------------+---------------------------------------------------+-------+
| 0-672-31509-2 | Pruitt, et al. | Teach Yourself GIMP in 24 Hours                   | 24.99 |
| 0-672-31557-3 | Philips        | Great Grace                                       | 30.99 |
| 0-672-31697-8 | Michael Morgan | Java 2 for Professional Developers                | 34.99 |
| 0-672-31745-1 | Thomas Down    | Installing Debian GNU/Linux                       | 24.99 |
| 0-672-31769-9 | Thomas Schenk  | Caldera OpenLinux System Administration Unleashed | 49.99 |
+---------------+----------------+---------------------------------------------------+-------+
5 rows in set (0.00 sec)

mysql> update books
    -> set price=price*1.1;
Query OK, 5 rows affected (0.09 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select * from books;
+---------------+----------------+---------------------------------------------------+-------+
| isbn          | author         | title                                             | price |
+---------------+----------------+---------------------------------------------------+-------+
| 0-672-31509-2 | Pruitt, et al. | Teach Yourself GIMP in 24 Hours                   | 27.49 |
| 0-672-31557-3 | Philips        | Great Grace                                       | 34.09 |
| 0-672-31697-8 | Michael Morgan | Java 2 for Professional Developers                | 38.49 |
| 0-672-31745-1 | Thomas Down    | Installing Debian GNU/Linux                       | 27.49 |
| 0-672-31769-9 | Thomas Schenk  | Caldera OpenLinux System Administration Unleashed | 54.99 |
+---------------+----------------+---------------------------------------------------+-------+
5 rows in set (0.00 sec)










=================================================
update customers
set address = '250 Olsens Road'
where customerid = 4;
=================================================

mysql> select * from customers;
+------------+-----------------+--------------------+--------------+
| customerid | name            | address            | city         |
+------------+-----------------+--------------------+--------------+
|          3 | Julie Smith     | 25 Oak Street      | Airport West |
|          4 | Alan Wong       | 1/47 Haines Avenue | Box Hill     |
|          5 | Michelle Arthur | 357 North Road     | Yarraville   |
+------------+-----------------+--------------------+--------------+
3 rows in set (0.00 sec)

mysql> update customers
    -> set address = '250 Olsens Road'
    -> where customerid = 4;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from customers;
+------------+-----------------+-----------------+--------------+
| customerid | name            | address         | city         |
+------------+-----------------+-----------------+--------------+
|          3 | Julie Smith     | 25 Oak Street   | Airport West |
|          4 | Alan Wong       | 250 Olsens Road | Box Hill     |
|          5 | Michelle Arthur | 357 North Road  | Yarraville   |
+------------+-----------------+-----------------+--------------+
3 rows in set (0.00 sec)













p324
=================================================
alter table customers
modify name char(70) not null;
=================================================

mysql> desc customers;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| customerid | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name       | char(50)         | NO   |     |         |                |
| address    | char(100)        | NO   |     |         |                |
| city       | char(30)         | NO   |     |         |                |
+------------+------------------+------+-----+---------+----------------+
4 rows in set (0.09 sec)


mysql> alter table customers
    -> modify name char(70) not null;
Query OK, 3 rows affected (0.47 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> desc customers;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| customerid | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name       | char(70)         | NO   |     |         |                |
| address    | char(100)        | NO   |     |         |                |
| city       | char(30)         | NO   |     |         |                |
+------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)








=================================================
alter table orders
add tax float(6.2) after amount;
=================================================


mysql> select * from orders;
+---------+------------+--------+------------+
| orderid | customerid | amount | date       |
+---------+------------+--------+------------+
|       1 |          5 |  69.98 | 2000-04-02 |
|       2 |          3 |  49.99 | 2000-04-15 |
|       3 |          4 |  74.98 | 2000-04-19 |
|       4 |          5 |  24.99 | 2000-05-01 |
+---------+------------+--------+------------+
4 rows in set (0.00 sec)


mysql> alter table orders
    -> add tax float(6,2) after amount;
Query OK, 4 rows affected (0.23 sec)
Records: 4  Duplicates: 0  Warnings: 0


mysql> select * from orders;
+---------+------------+--------+------+------------+
| orderid | customerid | amount | tax  | date       |
+---------+------------+--------+------+------------+
|       1 |          5 |  69.98 | NULL | 2000-04-02 |
|       2 |          3 |  49.99 | NULL | 2000-04-15 |
|       3 |          4 |  74.98 | NULL | 2000-04-19 |
|       4 |          5 |  24.99 | NULL | 2000-05-01 |
+---------+------------+--------+------+------------+
4 rows in set (0.00 sec)






삭제
=================================================
delete from 'table명';
ex) delete from customers where customerid=5;

drop table 'table명';

drop database 'database명';
=================================================



mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bookorama          |
| company            |
| mysql              |
| sqltest            |
| test               |
+--------------------+
6 rows in set (0.03 sec)


mysql> drop database bookorama;
Query OK, 5 rows affected (0.27 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| company            |
| mysql              |
| sqltest            |
| test               |
+--------------------+
5 rows in set (0.00 sec)


Comment 0 Trackback 0
Top

prev 1 next