01.15(3일차)

 

 

drop database if exists book;
 
 create database book;
 use book;
 
 create table book(
 bookno int primary key,
 title varchar(50) not null,
 author varchar(20) not null,
 company varchar(30) not null);
 
 create table customer(
 id varchar(20) primary key,
 name varchar(20) not null,
 pw varchar(20) not null,
 tel varchar(20) not null);
 
 #defualt 옵션이 있을 때 주로 사용하는 방식 OR auto_increment 
insert into book(bookno, title, author, company) value (1,'title','author','company');

#전체 입력 방식
insert into book value(2,'title2','author2','company2');
 
#여러개를 한번 에 입력하는방식
insert into book value(3,'title3','author3','company3'),
  (4,'title4','author4','company4'),
  (5,'title5','author5','company5'),
    (6,'title6','author6','company6'),
  (7,'title7','author7','company7'),
  (8,'title8','author8','company8'),
                      (9,'title9','author9','company9'),
                      (10,'title10','author10','company10'),
                      (11,'title11','author11','company11'),
                      (12,'title12','author12','company12'),
                      (13,'title13','author13','company13'),
                      (14,'title14','author14','company14'),
                      (15,'title15','author15','company15'),
                      (16,'title16','author16','company16'),
                      (17,'title17','author17','company17'),
                      (18,'title18','author18','company18'),
                      (19,'title19','author19','company19'),
                      (20,'title20','author20','company20');

#여러개를 한번 에 입력하는방식
insert into customer value
  ('id1','title1','author1','company1'),
  ('id2','title2','author2','company2'),
  ('id3','title3','author3','company3'),
      ('id4','title4','author4','company4'),
  ('id5','title5','author5','company5'),
    ('id6','title6','author6','company6'),
  ('id7','title7','author7','company7'),
     ('id8','title8','author8','company8'),
                        ('id9','title9','author9','company9'),
                        ('id10','title10','author10','company10'),
                        ('id11','title11','author11','company11'),
  ('id12','title12','author12','company12'),
                       ('id13','title13','author13','company13'),
                       ('id14','title14','author14','company14'),
                        ('id15','title15','author15','company15'),
                        ('id16','title16','author16','company16'),
                         ('id17','title17','author17','company17'),
                         ('id18','title18','author18','company18'),
                        ('id19','title19','author19','company19'),
                         ('id20','title20','author20','company20');
                                   

 drop table customer;
  
 #모든 속성 조회
select * from book;
select * from customer;

desc book;

select count(*) from book
union all
select count(*) from customer;

#특정조회
select bookno, title from book;
select id, pw from customer;

#조건검색
select * from book where title = 10;
select * from book where bookno between 1 and 10;
select * from book where bookno in(1,3,5,7,9);
select * from book where bookno not in(1,3,5,7,9);
select * from book where bookno =10 or bookno=11;
select * from book where bookno =10 and title=11;
select * from book where title like('title%');