Programming/SQL

이것이MySQL이다 Chap06.SQL 기본

티샤 2022. 2. 22. 17:40
  • SELECT : DB 내의 테이블에서 원하는 정보를 추출하는 명령어
    SELECT 열이름 FROM 테이블이름 WHERE 조건​

    SELECT * FROM titles; -- titles 테이블에서 모든 열의 내용을 가져와라.​

    SELECT * FROM employees.titles; 
SELECT * FROM titles; 
--둘다 동일한 결과값이다.​

    SELECT first_name FROM employees --employees의 DB에서 first_name열만 가져온다.​

    여러개의 열을 가지고 오고 싶으면 콤마(,)로 구분하면 됨.
    SELECT first_name,last_name,gender FROM employees;
--employees DB에서 first_name,last_name,gender만 출력.​


    [실습1.db이름,테이블이름,필드 이름이 정확히 기억나지 않거나 이름이 철자가 확실하지 않을때 조회하는 방법!]
    조회하고자 하는 내용이 employees 데이터베이스에 있는 employees 테이블의 first_name 및 gender 열.
    ①일단 서버에 어떤 데이터베이스가 있는지 조회해보자.
    SHOW databases;​
    ②DB이름이 employees이니, 이제 테이블 이름을 알아보기 위해 테이블 정보를 조회합시다.
    SHOW tables;​
    ③employees 테이블의 열에 뭐가 있는지 확인해볼까!
    describe employees;​
    여기서 first_name과 gender를 확인했다.
    ④최종적으로 데이터를 조회한다.
    select first_name, gender from employees;​
    • where문 : 조건을 지정하기!
      [실습2 : 새로운 db를 만듭니다..]
      ①일단 DB를 만듭니다. 이건 sqldb라는 이름의 데이터베이스가 있으면 삭제 후 sqldb를 만드는 것.
      DROP DATABASE IF EXISTS sqldb;
create database sqldb;​

      ②다음처럼 테이블을 만들어봅시다
      use sqldb;
create table usertbl
(	userID char(8) not null primary key,
	name varchar(10) not null,
    birthYear int not null,
    addr char(2) not null,
    mobile1 char(3),
    mobile2 char(8),
    height smallint,
    mDate date
);

create table buytbl
(	num int auto_increment not null primary key,
	userID char(8) not null,
	prodName char(6) not null,
    groupName char(4),
    price int not null,
    amount smallint not null,
    foreign key (userID) references usertbl(userID)
);​
      ※ 주의 : SQL은 { } 가 아니라 ( ) 입니다 > 이거때문에 오류났었음 ㅠㅠ

      ③테이블 내에 데이터를 입력하고 확인도 합시다.

      INSERT INTO usertbl values('LSG','이승기','1987','서울','011','11111111',182,'2008-8-8');
INSERT INTO usertbl values('KBS','김범수','1979','경남','011','22222222',173,'2012-4-4');
INSERT INTO usertbl values('KKH','김경호','1971','전남','019','33333333',177,'2007-7-7');
INSERT INTO usertbl values('JYP','조용필','1950','경기','011','4444444',166,'2009-4-4');
INSERT INTO usertbl values('SSK','성시경','1979','서울',NULL,NULL,186,'2013-12-12');
INSERT INTO usertbl values('LJB','임재범','1963','서울','016','66666666',182,'2009-9-9');
INSERT INTO usertbl values('YJS','윤종신','1969','경남',NULL,NULL,170,'2005-5-5');
INSERT INTO usertbl values('EJW','은지원','1972','경북','011','88888888',174,'2014-3-3');
INSERT INTO usertbl values('JKW','조관우','1965','경기','018','99999999',172,'2010-10-10');
INSERT INTO usertbl values('BBK','바비킴','1973','서울','010','00000000',176,'2013-5-5');

INSERT INTO buytbl values(NULL,'KBS','운동화',NULL,30,2);
INSERT INTO buytbl values(NULL,'KBS','노트북','전자',1000,2);
INSERT INTO buytbl values(NULL,'JYP','모니터','전자',200,1);
INSERT INTO buytbl values(NULL,'BBK','모니터','전자',200,5);
INSERT INTO buytbl values(NULL,'KBS','청바지','의류',50,3);
INSERT INTO buytbl values(NULL,'BBK','메모리','전자',80,10);
INSERT INTO buytbl values(NULL,'SSK','책','서적',15,5);
INSERT INTO buytbl values(NULL,'EJW','책','전자',15,2);
INSERT INTO buytbl values(NULL,'EJW','청바지','의류',50,1);
INSERT INTO buytbl values(NULL,'BBK','운동화',NULL,30,2);
INSERT INTO buytbl values(NULL,'EJW','책','서적',15,1);
INSERT INTO buytbl values(NULL,'BBK','운동화',NULL,30,2);​

      SELECT * FROM usertbl;
SELECT * FROM buytbl;​
       결과가 다음처럼 나온다.


    • SELECT ... FROM ... WHERE ... : 특정조건 데이터만 조회!
      일단 sqldb를 택해서 usertbl을 본다.
      USE sqldb;
SELECT * FROM usertbl;​

      '김경호'라는 이름을 검색해보자.
      SELECT * FROM usertbl where name='김경호';​

      • 관계연산자 사용 
        1970년 이후에 출생, 신장이 182 이상인 사람의 아이디와 이름 조회해보기
        select userID,name from usertbl where birthYear>=1970 and height>=182;​

        1970년 이후에 출생했거나, 신장이 182이상인 사람의 아이디와 이름 조회
        select userID,name from usertbl where birthYear>=1970 or height>=182;​
      • BETWEEN ... AND과 IN() 그리고 LIKE
        키가 180~183인 사람을 조회해보자.
        SELECT * FROM usertbl where height between 180 and 183;
SELECT * FROM usertbl where height >=180 and height <=183;​

        지역이 경북,전남,경북인 사람의 정보를 찾아보자.
        IN()은 이산적인 값을 위해 사용함!
        select * from usertbl where addr='경남' or addr='전남'or addr='경북';
select name,addr from usertbl where addr in('경남','전남','경북');​
        '김'씨인 사람을 찾자. : LIKE > 문자열 내용 검색

        select * from usertbl where name like '김%';​
         
      • 이외에도 '_용%'이면 앞에 아무거나 한글자, 두번째는 용, 세번째는 몇글자든 아무거나 오는값 추출
        SELECT name,height FROM usertbl WHERE name LIKE '_용%';​
        결과는
    • ANY/ALL/SOME 그리고 서브쿼리(SubQuery,하위쿼리)
      • 서브쿼리 : 쿼리문 내부의 쿼리문
        ex.1.김경호보다 키가 크거나 같은 사람의 이름과 키를 출력하려면 where로 김경호 키를 써줘야함.
        SELECT name,height FROM usertbl where height>177;​


        근데 177을 직접 써주는게 아니라 쿼리를 통해 작성하기!!!
        SELECT name,height FROM usertbl
	WHERE height > (SELECT height FROM usertbl WHERE name='김경호');​
        ex2.지역이 '경남'사람의 키보다 키가 크거나 같은 사람 추출
        select name,height from usertbl where height >= (select height from usertbl where addr='경남');​
        이렇게 쓰면..
        이런 오류가 난다. 하위쿼리가 2개이기 때문이다!
        그래서 필요한 구문이 ANY 구문.

        select name,height from usertbl where height >= any(select height from usertbl where addr='경남');​

        이때 실행하면 173보다 크거나 키가 170보다 크거나 같은 사람이 모두 출력됨 > 결국 170보다 크거나 같은 사람이 해당됨.

        any 대신 all로 바꿔 실행해보면..
        select name,height from usertbl where height >= all(select height from usertbl where addr='경남');​


        173 이상이 출력된다.
        ANY는 여러개의 결과 중 하나만 만족해도 되고, ALL은 모두 만족해야함.

        이번엔 >=ANY 대신 '=ANY'를 써보자.
        select name,height from usertbl where height = any(select height from usertbl where addr='경남');​

        그러면 173,170에 해당되는 사람만 출력된다.
        즉 '=ANY'(서브쿼리)는 'IN(서브쿼리)'와 동일한 것!!!!
    • ORDER BY : 결과가 출력되는 순서를 조절하는 구문.
      • 오름차순 : ASC, 디폴트값.1→2→3→4
      • 내림차순 : DESC, 4→3→2→1

        먼저 가입한 순서로 회원들을 출력해보자.
         
        SELECT name,mDate from usertbl order by mdate;

        그러면 원래 이렇게 나온다. 기본적으론 오름차순 기준.
        키가 큰 순서로 정렬하되, 키가 같은면 이름순으로 정렬하자.
         
        SELECT name,height from usertbl order by height desc, name;

    • DISTINCT : 중복된 것은 하나만 남긴다.
      회원테이블에서 회원들의 거주지역이 몇군데인지 출력해보자.
       
      SELECT addr from usertbl;

      이렇게 스타트하면..중복값이 한가득 나와준다.
      그러면 일단 한번 정렬을 해보자.
       
      SELECT addr from usertbl order by addr;

      이렇게 해도 중복된거 골라 세기가 귀찮다. 이 때 사용하는 것이 DISTINCT.
       
      SELECT distinct addr FROM usertbl;

      중복된 것은 1개씩만 보여주면서 출력됨

    • LIMIT : 출력개수 제한
       
      select emp_no,hire_date from employees order by hire_date limit 5;


      딱 5개만 출력!

  • USE : 현재 사용하는 데이터 지정 or 변경하는 명령어
    USE 데이터베이스_이름;​

    "지금부터 데이터베이스_이름을 사용하겠으니, 모든 쿼리는 이 DB에서 수행하라'
    >USE 대신 Workbench에서 사용하고자 하는 db를 더블클릭하면 됨.
  • CREATE TABLE ... SELECT : 테이블을 복사해서 사용할 경우
    CREATE TABLE 새로운테이블 (SELECT 복사할열 FROM 기존테이블)​
     ex. buytbl 테이블을 buytbl2로 복사하는 구문
    CREATE TABLE buytbl2 (select * from buytbl);
select * from buytbl2;​
    필요하다면 지정한 일부 열만 복사할 수도 있음
    CREATE TABLE buytbl3(SELECT userID,prodName, FROM buytbl); 
SELECT * FROM buytbl3;​

    ▷이 때 제약조건은(Primary Key,Foreign Key) 복사되지 않는다.

 

  • GROUP BY 및 HAVING 그리고 집계 함수
    • GROUP BY절 : 그룹으로 묶어주는 역할
      SQLdb의 buytbl(구매테이블)에서 사용자(userID)가 구매한 물품 개수를 보려면...
      select * from buytbl;
select userID,amount from buytbl;​


      사용자별로 여러번 구매가 이루어져서 별도 출력됨. > 각 사용자가 총 구매한 거 계산 > 집계함수 사용
      GROUP BY와 SUM 만들기
      SELECT userID,SUM(amount) from buytbl GROUP BY userID;​

      이름 예쁘게 바꿔주기..
      userID → 사용자아이디 , SUM(amount) → 총 구매 개수
      SELECT userID as '사용자 아이디', sum(amount) as '총 구매 개수' from buytbl group by userID;​


      • 집계함수
        • sum() : 합
        • AVG() : 평균
        • MIN() : 최솟값
        • MAX() : 최댓값
        • COUNT() :행의 개수 세기
        • COUNT(DISTINCT) : 행의 개수 세기(중복은 1개만 잉ㄴ정)
        • STDEV() : 표준편차 구하기
        • VAR_SAMP() : 분산 구하기

          전체구매자가 구매한 물품 개수의 평균 구하기
          SELECT AVG(amount) as '평균 구매 개수' from buytbl;​
          각 사용자 별로 한번 구매시 물건을 평균 몇 개 구매했는지 평균내기
          select userID,avg(amount) from buytbl group by userID;​
          가장 큰 키와 가장 작은 키의 회원 이름과 키를 출력하기를 하려면...
          select name, max(height),min(height) from usertbl;​
          내가 원한건 이게 아니다..
          select name, max(height),min(height) from usertbl group by name;​
          이것도 원하는 것이 아니다...(ㅠㅠ)
          그러면 서브쿼리와 함께 조합해야함.
          select name, height from usertbl 
	where height = (select max(height)from usertbl) 
	or height = (select min(height) from usertbl);​


          이번에는 휴대폰이 있는 사용자 수를 카운트하자.
          select count(mobile1) as '휴대폰이 있는 사용자' from usertbl;​

      • Having 절
        sum()을 이용해서 사용자별 총 구매액 구하기
        select userID, sum(amount) as '총구매액' from buytbl group by userID;​


        이중 구매액이 1000이상인  사용자에게만 사은품을 중정하고 싶다면 앞에서 조건 포함하는 where 구문 생각해볼 수 있지.. > 이 경우 오류가 난다.

        HAVING은 집계함수에 대하여 조건을 제한하는 것!!!! GROUP BY 절 다음에 나와야함!!!
        select userID AS '사용자',sum(price*amount) as '총 구매액' 
	from buytbl 
	group by userID 
    having sum(price*amount)>1000;​




    • ROLLUP : 총합 or 중간 합계 구하기!
      분류별로 합계 및 그 총합 구하기
      SELECT num,groupName,sum(price*amount) as '비용' 
	from buytbl 
    group by groupName,num 
	with rollup;​
      그러면 이렇게 소합계와 총합계를 구할 수 있다.
      만약 소합계와 총합만 필요하면 num을 빼면 됨.
      SELECT groupName,sum(price*amount) as '비용' from buytbl group by groupName with rollup;​

  • SQL문의 분류 : DML, DDL,DCL
    • DML(Data Manipulation Language : 데이터 조작언어) : 데이터를 조작(선택,삽입,수정,삭제) 하는데 사용되는 언어
      • 사용 대상 : 테이블의 행 > 사용전에 꼭 테이블이 정의되어있어야함!
      • SELECT, INSERT, UPDATE, DELETE, 트랜잭션 발생 SQL
        (트랜잭션) : 테이블 데이터 변경시 임시 적용.
    • DDL(Data Definition Lanugae : 데이터 정의 언어) : 데이터베이스 객체(데이터베이스, 테이블,뷰,인덱스)를 생성,삭제 변경함
      • CREATE, DROP, ALTER
    • DCL(Data Control Lanugae : 데이터 제어 언어) : 사용자에게 어떤 권한을 부여하거나 빼앗을 때 주로 사용하는 구문
      • GRANT, REVOKE, DENY
  • INSERT : 테이블에 데이터를 삽입하는 방식
    -- 기본형식
INSERT [INTO] 테이블[(열1,열2, ...)] VALUES (값1,값2,...)​
     우선 테이블 이름 다음에 나오는 열은 생략 가능. 하지만 생략시 values 다음에 나오는 값들의 순서 및 개수가 테이블 정의 열순서 및 개수가 동일해야함

USE sqldb;
CREATE TABLE testtbl1(id int,userName char(3),age int);
INSERT INTO testtbl1 VALUES (1,'홍길동',25);

위에서 ID와 이름만을 입력하고 나이 입력을 안하고 싶으면 입력할 열의 목록을 나열해줘야함. 생략된 AGE에는 null임

INSERT INTO testtbl1(id,userName) VALUES(2,'설현');

  • AUTO_INCREMENT : 자동증가. 자동으로 1부터 증가하는 값을 입력해준다.

USE sqldb;
create TABLE testtbl2
	(id int auto_increment PRIMARY KEY,
    userName char(3),
    age int);
insert into testtbl2 values(NULL,'지민',25);
insert into testtbl2 values(NULL,'유나',22);
insert into testtbl2 values(NULL,'유경',21);
select * from testtbl2;

select last_insert_id();

마지막에 입력된 값을 보여준다. 이 경우에는 3을 보여줌.
근데 이후에 100부터 입력되도록 변경하고 싶다면...

ALTER TABLE testtbl2 AUTO_INCREMENT=100;
INSERT INTO testtbl2 values(null,'찬미',23);
select * from testtbl2;

증가값을 지정하려면 서버변수인 @@auto_increment_increment변수를 변경시켜야함.
다음 예제는 초깃값을 1000으로 하고 증가값은 3으로 변경하는 예제.

USE sqldb;
create table testtbl3
	(id int auto_increment primary key,
    userName char(3),
    age int);
alter table testtbl3 auto_increment=1000;
set @@auto_increment_increment=3;
insert into testtbl3 values(null,'나연',20);
insert into testtbl3 values(null,'정연',18);
insert into testtbl3 values(null,'모모',149);
select *from testtbl3;

  • 대량의 샘플 데이터 생성 : 직접 키보드로 입력하려면 많은 시간이 걸림.

INSET INTO 테이블이름(열 이름1, 열이름2,....)
SELECT문 ;

예제... employees의 데이터를 가져올때..

USE sqldb;
CREATE TABLE testtbl4(id int,Fname varchar(50),Lname varchar(50));
INSERT INTO testtbl4
	select emp_no,first_name,last_name from employees.employees;

  • 데이터수정 : UPDATE - 기존에 입력되어있는 값을 변경하기 위함
    형식은

UPDATE 테이블이름
	SET 열1=값1, 열2=값2....
    WHERE 조건;

WHERE절은 생략가능하지만 생략하면 테이블 전체의 행이 변경된다.
testtbl4의 'Kyoichi'dml Lname을 '없음'으로 변경해보자.
원래 이랬는데,

update testtbl4 set Lname = '없음' where Fname='Kyoichi';

이렇게 바뀜. where 절이 없다면 전체행의 Lname이 모두 '없음'으로 변경됨. 

* 전체 테이블의 내용을 변경하고 싶을때 where 생략가능.

ex)buytbl(구매테이블)의 현재 단가가 1.5배 인상됐다면...

원래 이상태였다면

update buytbl set price=price*1.5;

price가 1.5배 증가한 걸 확인할 수 있다!

 

 

  • 데이터의 삭제 : DELETE FROM / 행단위로 삭제함

DELETE FROM 테이블이름 WHERE 조건; --형식

testtbl4에서 Aamer 사용자가 필요없다면 다음처럼 해보자.

delete from testtbl4 where Fname='Aamer';

만약 상위 몇건만 삭제하려면 LIMIT 구문을 함께 사용하면 된다.

delete from testtbl4 where Fname='Aamer' LIMIT 5;

대용량 테이블 삭제를 해볼까!

 

1.대용량 테이블 3개를 생성하자. 

USE sqldb;
CREATE TABLE bigtbl1(select * from employees.employees);
CREATE TABLE bigtbl2(select * from employees.employees);
CREATE TABLE bigtbl3(select * from employees.employees);

이렇게 만들어지고 나서... delete, drop, truncate 문으로 세 테이블을 모두 삭제해보자.

delete from bigtbl1;
drop table bigtbl2;
truncate table bigtbl3;

① DELETE는 시간이 오래 걸림. DML문일므로, 트랜잭션 로그를 기록하는 작업때문에 오래 걸림

② DROP : 금방 끝남. 테이블자체를 삭제함. DDL문. 테이블 자체가 필요없을 경우.

③ TRUNCATE : DDL문. 금방끝남. DLELETE와 동일함. 트랜잭션 로그를 기록하지 않아서 속도가 무척 빠름. 테이블 구조는 남겨놓고 싶다면 이쪽으로.

 

조건부 데이터 입력,변경 
1. 멤버테이블을 정의하고 데이터 입력. 기존 usertbl에서 아이디,이름,주소만 가져와서 간단히 만들자.

create table membertbl(select userID,name,addr from  usertbl limit 3); -- 3건만 가져옴
alter table membertbl add constraint pk_membertbl primary key(userID); -- pk 지정
select * from membertbl;

2.데이터를 추가로 3건 입력하기. 근데 첫번째에서 PK를 중복 하는 실수를 했다.

insert into membertbl values('BBK','비비코','미국');
insert into membertbl values('SJH','서장훈','서울');
insert into membertbl values('HJY','현주엽','경기');

3.INSERT IGNORE문으로 바꿔서 다시 실행하기

insert iGNORE membertbl values('BBK','비비코','미국');
insert ignore membertbl values('SJH','서장훈','서울');
insert ignore membertbl values('HJY','현주엽','경기');
select * from membertbl;

첫번째 데이터는 오류로 안들어가지만, 2건은 추가로 입력됨!

4.입력시에 기본키가 중복되면 데이터가 수정되도록 해보자.

insert into membertbl values('BBK','비비코','미국') on duplicate key update name='비비코',addr='미국';
insert into membertbl values('DJM','동짜몽','일본') on duplicate key update name='동짜몽',addr='일본';
select * from membertbl;

첫번째 BBK는 중복이므로 UPDATE 수행이 됨. 두번째 입력한 DJM은 없으므로 INSERT처럼 데이터 됨.

두번째 DJM은 없으므로 일반적인 INSERT처럼 데이터 입력됨. 

ON DUPLICATE KEY UPDATE는 PK 중복없으면 INSET, 중복되면 그 뒤의 UPDATE문이 수행됨.

 

  • WITH 절과 CTE
    - WITH 절 : CTE(Common Table Expression)를 표현하기 위한 구문/ 기존의 뷰, 파생테이블, 임시테이블 등으로 사용되던 것을 대신할 수 있음. 더 간결한 식으로 보여짐.
    - CTE  : 비재귀적 CTE, 재귀적 CTE로 나뉨.

비재귀적 CTE : 재귀적이지 않은 CTE

WITHE CTE_테이블이름(열이름)
AS
(
  <쿼리문>
)
SELECT 열 이름 FROM_테이블이름; -- 비재귀적CTE 형식

쉬운 이해를 위해 buytbl에서 총 구매액 구하기를 다시 살펴보자..

SELECT userID as '사용자', sum(price*amount) as '총구매액' from buytbl group by userid;

이 결과를 총 구매액이 많은 사용자 순서대로 정렬하고 싶다면..

①앞의 쿼리에 이어 ORDER BY문을 붙여도됨

②결과값 테이블이름이 abc라는 이름이면 

select * from abc order by 총구매액 desc;

이런 느낌으로 더 간단해짐.

즉..

WITH abc(userid,total)
as
(select userID,sum(price*amount) 
 from buytbl group by userid)
 select * from abc order by total desc;

이렇게 할 수 있단 소리임.

 

또다른 예시로 연습을 해보자.

회원테이블(usertbl)에서 각 지역별로 큰 키를 1명식 뽑고 그 사람들의 키 평균을 내보자. 

①"각 지역별로 가장 큰 키"를 뽑는 쿼리

SELECT addr,max(height) from usertbl group by addr;

②이 쿼리를 WITH 구문으로 묶는다.

WITH cte_usertbl(addr,maxHeight)
as
(SELECT addr,max(height) from usertbl group by addr)

③키의 평균을 구하는 쿼리를 작성한다.

select avg(maxHeight*1.0) as '각 지역별 최고키의 평균' from cte_usertbl;

④ ②,③의 쿼리를 합친다...

WITH cte_usertbl(addr,maxHeight)
as
(SELECT addr,max(height) from usertbl group by addr)
select avg(maxHeight*1.0) as '각 지역별 최고키의 평균' from cte_usertbl;

[CTE와 뷰의 차이점]

뷰 : 계속 존재해서 다른 구문에서도 사용 가능

CTE : 파생 테이블 구문이 끝나면 같이 소멸됨.