SQL...2

SQL...2

SQL CLI

• insert into

  CLI를 통해 값을 넣을 때 쓰며

     insert into 'table 명'
    (값,값,값,값)-> 설정한 table의 값을 넣는다
    values(0,0,0,0);

    insert into member
    (no,name,tel,intro)
    values(1,'홍길동','010-111','첫 번쨰 유저');
  
    insert into member
    (no,name,tel,intro)
    values(2,'김길동','010-222','두 번째 유저');
    select*from member;
  
    insert into book
    (isbn,title,publisher,author,price)
    values('1100','자바기본','한빛','박길동',30000);
  
    insert into book
    (isbn,title,publisher,author,price)
    values('1200','파이썬기본','한빛','이길동',20000);
  
    insert into rent(
    member_no,book_isbn,rentdate)
    values(1,'1100','2021-10-01');
  
    insert into rent(
    member_no,book_isbn,rentdate)
    values(2,'1200','2021-10-02');

• alter

    alter table 'table명'
    add column '컬럼명' 타입 ;

    -- 주소 칼럼 추가
    alter table member
    add column addr varchar(1000) not null;

• create table

    create table 'table 명'(
    ~~~
    ~~~
    ~~~
    primary key());

    -- 도서테이블
    create table book(
    isbn varchar(100),
    title varchar(100) not null,
    publisher varchar(100) not null,
    author varchar(100) not null,
    price int not null,
    primary key(isbn));

• foreign key

    foreign key(컬럼명) references 'table 명'(컬럼명);

    -- 대여 테이블
    create table rent(
    no int not null auto_increment,
    member_no int not null,
    book_isbn varchar(100) not null,
    rentdate date not null,
    enddate date,
    primary key(no),
    foreign key(member_no) references member(no),
    foreign key(book_isbn) references book(isbn)
    );

• review

    select*from rent
    where member_no=1 and book_isbn=1200;
  
    select*from rent
    order by rentdate desc;
  
    -- 1번 회원이
    update rent
    set enddate = '2021-11-10'
    where member_no =1 and book_isbn='1200';
  
    select*from rent
  
    update rent
    set enddate = '2021-11-10'
    where member_no =1 and book_isbn='1200';
  
    select*from rent
  
    select member.name, book.title
    from member
        left outer join rent
        on member.no = rent.member_no
        left outer join book
        on rent.book_isbn = book.isbn;
  
    select member.name ,book.title
    from member
        left outer join rent
        on member.no = rent.member_no
        left outer join book
        on rent.book_isbn= book.isbn
        where enddate is null;
  
    select member.name, count(rent.book_isbn)
    from member
    left outer join rent
    on member.no =rent.member_no
    where rent.enddate is null
    group by member.no;