[MySQL] 기본 문법 정리

1. 데이터베이스 기본 문법

-- 데이터베이스 목록 조회 
show databases;
-- 특정 데이터베이스 선택 (기본데이터베이스 선택) 끌어서 가져오기 가능!!
use database이름;

show table status; -- table에 대한 상세정보 볼 수 있음. 
desc dept_emp; -- 선택한 table의 명세서 보기 

-- 특정 테이블의 row 개수 계산하기
select count(*) from table명;

select count(*) from db명.table명; -- 기본 데이터베이스가 아닌 다른 데이터 베이스에서 table가져오려면 db명.table명

-- select 문 
select column1,column2.. 
from table명 
where 조건 
group by column명 
order by column명 또는 숫자

1.1 DDL(Data Definition Language) 구문 

-- create 타입 이름;  테이블 생성

-- 테이블을 복사해서 생성 (구조 + 데이터, PK/FK 복사안됨)
create table userDB1 (select * from userDB);

-- 일부 열만 복사해서 신규테이블을 생성할수도 있다.
create table userDB! (select userID, Name from userDB);

-- 테이블의 구조만 복사해서 신규테이블을 생성
create table userDB1 (select userID, Name from userDB where 1=0); -- where 1=0 는 무조건 거짓이기 때문에 어떠한 데이터도 검색하지 않는다.
desc userDB1;

-- alter 타입 이름; 데이터 추가/ 삭제 및 조건 추가
-- drop 타입 이름; 테이블 제거

 

1.2 DB 특징

-- DBMS에서 키워드는 대소문자구별이 없다(테이블명, 컬럼명 등등)
-- 데이터는 대소문자구별이 있다. (DBMS의 종류와 버전에 따라서 달라질 수 있다.)

2. Select문 사용 방법

2.1 특정 자음으로 시작하는 데이터 찾기

select * from DB명 where name = "ㄱ"; -- or where name < "ㄴ"

2.2 문자 및 숫자로 데이터 찾기

select * from salaries where emp_no = 10001;

-- 서울 또는 경기에 사는 회원
select * from userDB where addr='서울' or  addr='경기';
select * from userDB where addr in('서울' ,'경기'); -- in 은 or조건의 배열문처럼 쓴다.

* 은 쓰지말자! 안 좋은 습관이다, Column의 명이나 순서가 바뀌거나 추가가되면 오류가 날 수도 있다. shift 키로 칼럼에서 긁어오기

2.3 범위로 데이터 찾기

select userID, name, height from userDB where height >= 170 and height <= 180;
select userID, name, height from userDB where height between 170 and 180; -- 기준점을 모두 포함한다. 170부터 180

2.4 특정 조건별 데이터 찾기

-- 이름의 성이 김씨인 회원  like는 = 보다 오래걸리니 = 이 안될때만 like!
select userID, name from userDB where name = "김"; -- 데이터 안나옴/ =는 그 자체로 같은지 보는 것
select userID, name from userDB where name < "ㄴ"; ㄱ으로 시작하는 데이터 모두 검색
select userID, name from userDB where name like "김__"; -- _는 글자수가 명확할 때
select userID, name from userDB where name like "김%"; -- %는 글자수가 명확하지 않을때
select userID, name from userDB where name like "%관%";
select userID, name from userDB where name like "김%" or name like "조%"; -- like는 in, between 안됨

 

2.5 order by 정렬조건

• 순서로 지정하기

select addr,birthYear 
from userDB 
order by 1,2; -- select에서 검색할 column순서대로 1번부터 지정된다. addr순으로 정렬 후에 birthyear로 정렬

• 특정 column 명으로 지정하기

select addr, birthYear from userDB
group by addr, birthYear 
order by addr;

• 직접 지정하기

select userID, name, addr 
from userDB 
where height <= 180
order by field(addr,"서울","경기","경북","경남","전남"); 
-- field(칼럼명, "조건1","조건2"...)

2.6 where 조건 검색

-- 1986년에 입사한 사원목록
select emp_no, birth_date, first_name, last_name, gender, hire_date
from employees 
where year(hire_date) =1986;

-- 1986년에 입사한 남자 사원목록
select emp_no, birth_date, first_name, last_name, gender, hire_date
from employees 
where year(hire_date) =1986 and gender ="M"; -- where hire_date between '1986-01-01' and '1986-12-31';

-- 1986년에 입사한 남자가 아닌 사원목록
select emp_no, birth_date, first_name, last_name, gender, hire_date
from employees 
where year(hire_date) =1986 and gender <> "M";

2.7 날짜 관련 함수

-- 입사당시의 나이가 25세 이상인 사원목록
-- DATEDIFF(날짜1, 날짜2) : 날짜 일수 계산
-- TIMESTAMPDIFF(단위, 날짜1, 날짜2) : 구분으로 차이를 구함
select emp_no, birth_date, first_name, last_name, gender, hire_date
from employees 
where timestampdiff(year,birth_date,hire_date) >= 25; -- where year(hire_date)-year(birth_date) >=25;

2.8 서브쿼리를 사용한 where 조건문 

-- 서브쿼리에서 =, >, <, >=, <= 결과가 1개의 값이어야한다.
-- 서브쿼리결과가 복수개 행으로 나온다면, in(or), all, any

select userID, name, height
from userDB
where height > (select height
from userDB
where name = "홍길동");
                
select userID, name, height
from userDB
where height in (select height
from userDB
where name in("홍길동","청길동"));

 2.9 limit 조건

-- 나이가 많은 회원 top 5 목록
select userID, name, birthYear
from userDB
order by birthYear
limit 5;

-- 나이가 많은 회원 top 3-8 목록
select userID, name, birthYear
from userDB
order by birthYear
limit 2,5; -- 시작인덱스, 갯수   / limit은 시작인덱스가 0부터 시작

2.10 집계함수

- 집게함수는 null인 값은 빼고 계산한다. 

• sum()

-- 회원별 총 구매액을 구하시오
-- group by를 사용하는 경우의 select 절에는 group by절에 있는 컬럼, 집계합수만 올 수 있다. 
select userID, sum(price * amount) totalAmt, prodName -- as totalAmt as는 안 써도 된다.
from buytbl
group by userID  -- groupby에 없는 칼럼을 사용하면 오류는 안날 수도 있지만 데이터의 오판단이 일어난다
order by 1; -- sum(price * amount), totalAmt도 가능하다!

• avg()

-- 회월별 평균 구매개수를 구하시오
select userID, avg(amount) as "평균구매개수"
from buytbl
group by userID
order by 1;

• min(), max()

-- 회원중에서 가장 큰키와 가장 큰 작은키인 회원은?
select name, height
from usertbl
where height = (select max(height) from usertbl) 
  or height = (select min(height) from usertbl);

 

2.11 union 쿼리

• 여러개의 테이블을 하나로 합칠 때 사용한다. select로 지정하는 column수가 같아야한다.

select name, height
from usertbl
where height = (select max(height) from usertbl)
union  
select name, height
from usertbl
where height = (select min(height) from usertbl);