Open3

SQL

Joel LeeJoel Lee

DBMS

#DBMS

[!info] DBMS

  • DBMS = Database Management System
  • RDBMS = Relational DBMS : 関係型データベース。関係型モデルを使用。

  • NoSQL - Not only SQL

テーブルとモデリング

#Modeling

[!info] モデリング

  • データをどのようにグループ化して保存するかを事前に設計するプロセス。
Joel LeeJoel Lee

テーブル生成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);

アップデート

#mysql/update

[!NOTE] UPDATE

  • **条件節(where)**がなければ全ての row について更新
update book_list set reg_date = '20230815' where book_name='몽';

削除

#mysql/delete

[!NOTE] DELETE

  • **条件節(where)**がなければ全てのデータ削除
  • カラムデータ削除は update を使って null にしよう。
delete from book_list where publisher = 'ㅇㅇ';

テーブル照会SQL (Easy)

セレクト、エイリアス

#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;