MySQL 기본문법

- 테이블 생성하기.

use yhdb; --야후db에서 작업하겠다.

create table tweets(username varchar(15), -- username컬럼을 생성할 것이고, varchar로 15글자까지만 받을것이다.
                    content varchar(140),  --content 컬럼도 마찬가지로 varchar로 140글자까지만 받을것이다.
                    favorites int -- favorites컬럼은 int형의 숫자만 받을것이다.
);

여기서 varchar는 문자와 숫자를 보관할 수 있는 데이터베이스 관리 시스템의 필드의 자료형의 하나로

입력된 자료의 크기만큼만 메모리를 할당하기 때문에 메모리의 낭비를 줄일 수 있음.

select * from tweets; --tweet 테이블 불러오기

not null -- 테이블 생성시 컬럼에 null 문자 입력 방지를 위함.

data_id int not null auto_increment - 테이블 생성 시 자동으로 DB가 ID 값 등을 채우도록 할 수 있음.

primary key -- 각 테이블의 유일한 값으로 중복값을 만들지 않게함. id 생성 등에 사용.

insert into 테이블(name, age)  values('Jetson',7); -- 테이블에 데이터 값 입력

insert into cats(name, age) values('chalie',10), ('sadie',3), ('lazy bear',1); -- Multiple insert로 여러 데이터 값을 한번에 입력가능

 

 

-데이터 쿼리

show tables -- yh에 존재하는 모든 테이블을 보기


show columns from tweets; -- 해당 테이블의 컬럼명을 알고싶을때.


select * from tweets; - tweet 테이블에서 모든 데이터를 쿼리.


select NAME frotweets -- tweet 테이블에서 name 컬럼을 쿼리.(컬럼의 대소문자는 상관없이 불러올 수 있음.)


select * from tweets where age = 4; -- tweet 테이블에서 age컬럼의 값이 4인 모든 데이터를 쿼리.


select NAME, breed 
from tweets 
where age = 4, and breed = 'Persian'; 
-- tweet 테이블에서 age컬럼의 값이 4이고 종이 페르시안인 NAME, breed 컬럼 데이터를 쿼리.

 

 

UPDATE

update tweets set breed = 'shorthair' where breed = 'Tabby'; -- 종이 Tabby인 고양이를 shorthair로 변경. 


update cat set age = '12' where breed = 'Maine coon' ; -- 종이 Maine coon 인 고양이의 나이를 12로

 

DELETE

delete from cat where age = 12;

 

데이터 가공

books 테이블 생성 및 데이터 입력

use yhdb;


create table books(
	book_id int not null auto_increment,
	title varchar(100),
    author_fname varchar(100),
    author_lname varchar(100),
    released_year int,
    stock_quantity int,
    pages int,
	primary key(book_id)
);


INSERT INTO books (title, author_fname, author_lname, released_year, stock_quantity, pages)
VALUES
('The Namesake', 'Jhumpa', 'Lahiri', 2003, 32, 291),
('Norse Mythology', 'Neil', 'Gaiman',2016, 43, 304),
('American Gods', 'Neil', 'Gaiman', 2001, 12, 465),
('Interpreter of Maladies', 'Jhumpa', 'Lahiri', 1996, 97, 198),
('A Hologram for the King: A Novel', 'Dave', 'Eggers', 2012, 154, 352),
('The Circle', 'Dave', 'Eggers', 2013, 26, 504),
('The Amazing Adventures of Kavalier & Clay', 'Michael', 'Chabon', 2000, 68, 634),
('Just Kids', 'Patti', 'Smith', 2010, 55, 304),
('A Heartbreaking Work of Staggering Genius', 'Dave', 'Eggers', 2001, 104, 437),
('Coraline', 'Neil', 'Gaiman', 2003, 100, 208),
('What We Talk About When We Talk About Love: Stories', 'Raymond', 'Carver', 1981, 23, 176),
("Where I'm Calling From: Selected Stories", 'Raymond', 'Carver', 1989, 12, 526),
('White Noise', 'Don', 'DeLillo', 1985, 49, 320),
('Cannery Row', 'John', 'Steinbeck', 1945, 95, 181),
('Oblivion: Stories', 'David', 'Foster Wallace', 2004, 172, 329),
('Consider the Lobster', 'David', 'Foster Wallace', 2005, 92, 343);

 

concat

--concat으로 first name + lastname을 full name으로 만들기
concat(author_fname,' ',author_lname) as 'fullname' from books;


-- 위의 퍼스트 라스트 풀네임으로 데이터를 조회하시오
select author_fname as first, author_lname as last,
concat( author_fname, ' ', author_lname) as fullname from books;

select concat_ws(' - ', title, author_fname, author_lname, ) from books;

 

substring

-- 문자열 안의 일부를 가져올 떄 substring 함수

select substring('Hello world', 1, 4); 슬라이싱과같이 사용


select * from books;

select substring(title, 1,10) 
as title, author_fname, released_year, pages from books;
-- 1~10까지의 타이틀 텍스트가 출력됨.

 

replace

-- replace 함수

select replace('What the hell','hell','***');  -- what the ***로 표출
select replace('cheese bread coffee milk',' ',',' ); -- 띄어쓰기를 ,로 표출


-- 타이틀 컬럼의 문자열에서, e를 3으로 변경하여 조회하라

select replace(title,'e',3) from books;

 

reverse

-- 문자열의 순서를 바꾸는 함수 reverse함수


select reverse(author_fname) from books;  -- 데이터의 순서를 바꿔줌

 

char_length

-- 문자열의 길이를 알려주는 함수 char_length함수
select char_length('Hello World'); -- 글자의 갯수를 표출해줌


-- 책 제목의 길이를 구하시오
select char_length(title) as title_length from books;


-- author_lname의 길이를 구해서, 'mike의 이름 길이는 4'로 나오게 하자
select concat(author_lname,'의 이름길이는 ',char_length(author_lname)) as 'desc' from books;

 

upper(), lower()

-- 대문자 소문자를 바꿔주는 함수 upper(), lower()함수 


select upper('Hello World');  -- 모두 대문자
select lower('Hello World');  -- 모두 소문자
select upper(title) as title from books;

 

distinct

-- 유니크한 데이터로만 데이터 가져오기 distinct함수 - 중복제거


select author_lname from books;
select distinct author_lname from books; -- 중복이 제거되어 unique 데이터만 표출됨

 

order by

-- 정렬하기 order by 키워드


select author_lname from books order by author_lname; -- author_lname로 정렬함.

select * from books order by title; -- title로 정렬함.

 

desc // asc

-- asc = 오름차순으로 //  desc = 내림차순으로


--released_year을 오름차순으로 정렬
select title, released_year from books order by released_year asc; 

-- 작가의 fname lname으로 정렬하여 모든컬럼 조회
select * from books order by author_lname, author_fname asc;

-- 각각 따로 조회 가능
select * from books order by author_lname desc, author_fname asc;

 

offset // limit

-- limit와 offset


select * from books limit 5; -- 처음부터 5개 가져오기
select * from books limit 5, 5; -- 5개 이후부터 5개 가져오기
select * from books limit 10,5; -- 10개 이후부터 5개 가져오기


-- 원래는 클라이언트에서 변수를 받아서 숫자를 직접처리하지 않아도 됨.
-- 'select * from books limit {},{}.format(offset, limit)' 형태로 사용함.


-- 출간년도가 최신인 책 5권을 최신순으로 조회하시오.

select * from books order by released_year desc limit 5;
select * from books order by released_year desc limit 5, 5;

 

like

-- 문자열에 포함된 단어 검색 like


-- 작가의 fname에 da라는 글자가 있는 작가의 제목과 이름 조회

select title, author_fname, author_lname from books 
where author_fname like '%da%' -- 일치하는 단어의 유무를 알 수 있다.


select title, author_fname, author_lname from books 
where author_fname like 'da%' -- da로 시작하는 데이터 쿼리하기

select title, author_fname, author_lname from books 
where author_fname like '%da' -- da로 끝나는 데이터 쿼리하기


-- like 언더스코어 _

select title, stock_quantity from books
where stock_quantity like '___'; -- (언더스코어 3개) = 3개의 문자로 구성된 데이터를 찾음.
-- (032)864-3607 like '(___)___-____'

 

count()

-- 전체 데이터의 갯수 구하기 count() 함수



-- books 테이블의 행의 갯수(데이터의 갯수)

select count(*) from books;
select count(*) from cats



-- the가 들어가는 책 제못은 몇개?

select count(title) from books where title like '%the%';

 

summarize // aggregate  

요약과 집계

 

group by

-- author_lname으로 묶어 lname과 그 사람의 책의 갯수를 조회하시오. like groupby

select author_lname, count(*) 
from books 
group by author_lname



-- 이름과 성으로 묶어서 동일인이 몇권의 책을 쓴것인지 이름, 성과 함께 조회하시오

select count(*), author_fname,author_lname
from books 
group by author_fname,author_lname;



-- 연도별로 몇권의 책이 발간되어있는가? 년도와 책의갯수로 조회

select released_year, stock_quantity, count(*)
from books 
group by released_year
order by released_year;

 

 

Min // Max

-- 최솟값, 최댓값

select max(released_year)
from books 


-- 페이지가 가장 많은 책

select max(pages) from books;


-- subquery방식으로 위의 문제 해결

select * from books
where pages =(select max(pages) from books);


-- 연도가 최대인 연도의 데이터 조회

select *
from books
where released_year =(select max(released_year) from books);


select *
from books
order by released_year desc limit 1;

 

sum // avg

-- 이 테이블의 책들의 모든 페이지 합
select sum(pages)
from books


-- 이 테이블의 책들의 모든 페이지 평균
select avg(pages)
from books;

 

between

-- a 부터 z 까지
-- a~z
-- a:z
-- between a and z
select * from books where released_year between 2004 and 2015;

select * from people
-- 생일이(birthdt) 1999년~2021년 사람의 사람데이터
select * from people where birthdt between '1999-01-01' and '2021-01-01'
select * from people where birthdt between '1999-01-01' and '2022-01-01'