테이블 생성 SQL (DDL)

CREATE

[!NOTE]

• DDL : Data Definition Language

#mysql/create #mysql/select

create table book_list (
	book_no		varchar(16)	not null,
	book_name	varchar(50),
	writer		varchar(50),
	publisher	varchar(30),
	reg_date	date,
	price		int
);

show databases;
use mysql;

ALTER

#mysql/alter/add #mysql/alter/modify #mysql/alter/change #mysql/alter/drop #mysql/alter/rename

alter table book_list add column description varchar(1000);
alter table book_list modify column book_name varchar(100);
alter table book_list change column description book_desc varchar(1000);
alter table book_list drop column book_desc;
alter table book_list rename book_info;

DROP_TRUNCATE

#mysql/truncate #mysql/drop

[!NOTE] trincate 와 drop

• truncate : 데이터만 삭제
  - 모든 행 삭제
• drop : 데이터와 테이블 삭제
• 둘 다 롤백이 안 됨

truncate table book_list;
drop table book_list;

테이블 생성 SQL (DML)

[!NOTE]

• DML : Data Manipulation Language

INSERT

#mysql/insert

insert into book_list values ('9791168473690', '세이노의 가르침', '세이노', '데이원', '20230302',7200);

insert into book_list (book_no, book_name, writer, publisher, price)
values ('2138092823', '몽', '모모', 'ㅇㅇ', 10000)

insert into book_list values ('97asd1asd1000', '세이노의 가르침', '세이노', '데이원', '20230302',7200);

UPDATE

#mysql/update

[!NOTE] UPDATE

• 조건절 (where) 이 없으면 모든 row 에 대해 업데이트

update book_list set reg_date = '20230815' where book_name='몽';

DELETE

#mysql/delete

[!NOTE] DELETE

• 조건절 (where) 이 없으면 모든 데이터 삭제
• 컬럼 데이터 삭제는 update 을 이용해 null 로 만들자

delete from book_list where publisher = 'ㅇㅇ';

테이블 조회 SQL (Easy)

SELECT, ALIAS

#mysql/select #mysql/distinct #mysql/count

select * from melon_chart limit 3;

select singer from melon_chart;
select distinct singer from melon_chart;
select count(distinct singer) as cnt from melon_chart;
select count(distinct singer) "가수" from melon_chart as mc;

select ranking, song from melon_chart where singer = 'NewJeans';

select * from melon_chart where singer = 'NewJeans' and ranking <= 5;

select * from melon_chart where singer = '정국' or singer = '박재정';

WHERE (IN, LIKE, BETWEEN)

#mysql/where #mysql/where/in #mysql/where/like #mysql/where/between

select * from melon_chart where song = 'I AM';

select * from melon_chart where song in ('I AM', 'Super Shy', 'Spicy');
select * from melon_chart where song = 'I AM' or song = 'Super Shy' or song = 'Spicy';

select * from melon_chart where song = 'I AM' or song = 'Super Shy' or song = 'Spicy' and singer = 'NewJeans';
select * from melon_chart where (song = 'I AM' or song = 'Super Shy' or song = 'Spicy') and singer = 'NewJeans';

select * from melon_chart where song like '_카%';

select * from like_test where col like '%\%%';
select * from like_test where col like '%\_%';
select * from like_test where col like '%#%%' escape '#';
select * from like_test where col like '%$_%' escape '$';

select * from melon_chart where like_no between 100000 and 150000;

ORDER BY (LIMIT)

#mysql/order_by

select * from melon_chart order by ranking desc;

select song, singer from melon_chart order by ranking;

select * from melon_chart where singer not in ('정국', '박재정') order by song;

select * from melon_chart order by singer asc, like_no desc;

select * from melon_chart order by like_no desc limit 3, 5;

집계 함수

#mysql/count #mysql/sum #mysql/avg #mysql/min #mysql/max

select count(*), count(1), count(col1), count(col2) from function_test;
select count(*) from melon_chart where like_no > 100000;

select sum(col1), sum(col2) from function_test;
select avg(col1), avg(col2) from function_test;
select min(col1), min(col2) from function_test;
select max(col1), max(col2) from function_test;

GROUP BY

#mysql/group_by

select * from animal_info group by animal;
select animal, type, count(*) from animal_info where animal='강아지' group by animal, type;
select animal, type, min(age), max(age) from animal_info group by animal, type order by animal;

HAVING

#mysql/having

select animal, type, count(*)
	from animal_info
	where count(*) > 2
	group by animal, type;

select animal, type, count(*) 
	from animal_info 
	where animal = '고양이'
	group by animal, type
	having count(*) > 2
	order by count(*) desc;