mysql 기초 명령 예제 (총정리)

1. 위와 같은 releation schema를 갖는 cust, book, orders 테이블을 생성하라.

create table cust{
    cid int primary key,
    name varchar(20),
    addr varchar(20),
    hp varchar(20)};
 
create table book{
    bid int primary key,
    title varchar(20),
    publisher varchar(20),
    price int};
 
create table orders{		##튜플 입력하고 생성해야 함
    oid int primary key,
    cid int,
    bid int,
    odate varchar(20),
    foreign key(cid) references cust(cid),
    foreign key(bid) references book(bid)};

2. 3개의 테이블에 위와 같이 tuple을 입력하라.

insert into cust values
    (1,'hong','anyang','010-999-1111'),
    (2,'kim','seoul','010-555-3456'),
    (3,'kang','busan','010-333-7894'),
    (4,'park','anyang','010-321-5678');
 
 
insert into book values
    (1,'database','mac',34000),
    (2,'java','hanbit',29000),
    (3,'c++','abc',23000),
    (4,'python','hanbit',41000),
    (5,'data structure','mac',12000);
 
insert into book(bid,title,publisher) values (6,'network','abc')  ##NULL을 넣고 싶은 경우 이렇게 지정해서
 
 
set foreign_key_checks = 0;
 
insert into orders values
    (1,1,1,'2020-03-15'),
    (2,1,2,'2020-03-16'),
    (3,1,3,'2020-04-15'),
    (4,2,1,'2021-05-05'),
    (5,2,2,'2021-05-06'),
    (6,3,4,'2022-01-03'),
    (7,100,4,'2022-03,05);
    
set foreign_key_checks = 1;

3. 모든 도서 목록을 검색하라.

select *
from book;

4. ‘anyang’ 사는 고객을 검색하라.

select *
from cust
where addr = 'anyang';

5. 가격이 3만원 이상 4만원 이하인 도서를 검색하라.

select *
from book
where price >= 30000 and price <= 40000;

6. hanbit 출판사에서 출간한 책 중 가격이 4만원 이상인 도서를 검색하라.

select *
from book
where publisher = 'hanbit' and price >= 40000;

7. 도서명에 ‘data’가 포함된 도서 목록을 검색하라.

select *
from book
where title like '%data%';

8. 도서명의 2번째 문자가 ‘a’인 도서 목록을 검색하라.

select *
from book
where title like '_a%';

9. 가격이 정해지지 않은 도서 목록을 보여라.

select *
from book
where price is null;

10. 고객명으로 내림차순하여 고객목록을 보여라.

select *
from cust
order by name desc;

11. 고객순로 내림차순, 고객명으로 오름차순하여 고객목록을 보여라.

select *
from cust
order by cid desc, name asc;

12. 주소별 거주하는 고객 수를 보여라.

select addr, count(addr)
from cust
group by addr;

13. 거주하는 고객 수가 2명 이상인 지역과 고객수를 보여라.

select addr, count(addr)
from cust
group by addr
having count(addr) >= 2;

14. 가격이 3만원 이상인 책에서 2권 이상 출간한 출판사의 책 수를 보여라.

select publisher, count(publisher)
from book
where price >= 30000
group by publisher
having count(publisher) >= 2;

15. cust, orders 테이블의 카티전 프로덕트를 수행한 결과를 보여라.

select *
from cust, orders;

16. book,orders 테이블의 카티전 프로덕트를 수행한 결과를 보이되 도서명으로 오름차순하여 보여라.

select *
from book, orders
order by title asc;

17. cust, orders, book 테이블의 카티전 프로덕트를 수행한 결과를 보여라.

select *
from cust, book, orders;

18. 주문된 도서의 oid, title, odate를 보이세요.

select oid, title, odate
from book, orders
where book.bid = orders.bid;

19. 주문한 고객의 oid, name, addr을 보이세요.

select oid, name, addr
from cust, orders
where cust.cid = orders.cid;

20. 안양 사는 고객의 주문내역(oid,name,title,addr,odate)를 보이세요.

select oid, name, title, addr, odate
from cust, book, orders
where cust.cid = orders.cid and book.bid = orders.bid and addr = 'anyang';

21. ‘hanbit’ 출판사의 주문내역(oid,name,title,addr,odate)을 보이세요.

select oid, name, title, addr, odate
from cust, book, orders
where cust.cid = orders.cid and book.bid = orders.bid and publisher = 'hanbit';

22. 주문한 고객의 주문내역을 보이되, 주문하지 않은 고객의 정보도 보이세요.(oid,cid,name,odate)

select oid, cust.cid, name, odate
from cust left outer join orders on cust.cid = orders.cid;

23. 주문된 책의 주문내역을 보이되, 주문되지 않은 책의 정보도 보이세요.(oid,cid,title,odate)

select oid, book.bid, title, odate
from book left outer join orders on book.bid = orders.bid;

24. 주문한 고객의 주문내역을 보이되 탈퇴한 고객의 주문내역도 모두 보이세요.(oid,cid,name,odate)

select oid, cust.cid, name, odate
from cust right outer join orders on cust.cid = orders.cid;

25. 주문한 고객의 주문내역을 보이되, 주문하지 않은 고객의 정보도 보이고, 탈퇴한 고객의 주문내역도 모두 보이세요.(oid,cid,name,odate)

select oid, cid, name, odate
from cust left outer join orders on cust.cid = orders.cid
union
select oid, cid, name, odate
from cust right outer join orders on cust.cid = orders.cid;

---

1. 주문한 고객의 name, addr을 보이시오(부속질의)

select name, addr
from cust
where cid in (
    select cid
    from orders
    where cust.cid = orders.cid);

2. 주문하지 않은 고객의 name, addr을 보이시오(outer join, is null 사용)

select name, addr
from cust
where cid in (
    select cid
    from cust left outer join orders on cust.cid = orders.cid
    where orders.cid is null);

3. 주문한 고객의 name, addr을 보이시오(상관부속질의: exists 사용)

select name, addr
from cust
where exists (
    select *
    from orders
    where cust.cid = orders.cid);

4. 주문하지 않은 고객의 name, addr을 보이시오(상관부속질의: not exists 사용)

select name, addr
from cust
where not exists (
    select *
    from orders
    where cust.cid = orders.cid);