반응형
invalid-file==============================================================
*
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)
반응형