<실습> 1.mysql 기본

drop database if exists sqlDB; -- 만약 sqlDB가 있으면

CREATE database sqlDB;

use sqlDB;

create table userTbl -- 회원 테이블

( userID CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 ID (PK)

  name varchar(10) NOT NULL, -- 이름

  birthYear INT NOT NULL, -- 출생년도

  addr char(2) not null, -- 지역

  mobile1 char(3), -- 폰번호 앞자리 3 자리

  mobile2 char(8),

  height SMALLINT, -- 키

  mDate DATE -- 가입일

);

create TABLE buyTbl -- 회원 구매 테이블 (

( num int auto_increment not null primary key, -- 순번 pk

userID CHAR(8) NOT NULL, -- ID(FK) 

prodName CHAR(6) NOT NULL, -- 물품명

groupName char(4), -- 분류

price int not null, -- 단가

amount smallint NOT NULL, -- 수량

foreign key (userID) references userTbl(userID)

);

INSERT INTO userTbl values('LSG','이승기',1987,'서울','011','1111111',182,'2008-8-8');

INSERT INTO userTbl values('KBS','김범수',1979,'경남','011','2222222',173,'2012-4-4');

INSERT INTO userTbl values('KKH','김경호',1971,'전남','019','3333333',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','6666666',182,'2009-9-9');

INSERT INTO userTbl values('YJS','윤종신',1969,'경남',NULL,NULL,170,'2005-5-5');

INSERT INTO userTbl values('EJW','은지원',1972,'경북','011','8888888',174,'2014-3-3');

INSERT INTO userTbl values('JKW','조관우',1965,'경기','018','9999999',172,'2013-5-5');

INSERT INTO userTbl values('BBK','바비킴',1973,'서울','010','0000000',176,'2013-5-5');

-- 구매테이블에 데이터 삽입

INSERT INTO buyTbl values(NULL, 'KBS','운동화',NULL, 30, 2);

INSERT INTO buyTbl values(NULL, 'KBS','노트북','전자', 1000, 1);

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

--  문자형 (CHAR , VARCHAR) 에 데이터를 입력하려면 홀따옴표 (' ')로 묶어 줘야 한다.

SELECT * FROM userTbl;

select * from buyTbl;

-- 회원 테이블의 결과 순서가 입력한 순서와 다른 이유는 userID를 Primary key 로 지정했기 때문에 자동으로 클러스터형 인텍스가

-- 생성돼서 입력 시에 userID열로 정렬이 되기 때문이다.

-- 기본적인 WHERE 절

-- SELECT 필드이름 FROM 테이블 이름 WHERE 조건식;

SELECT * 

FROM userTbl;

-- where 절 사용

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;

-- 했거나는 or , 하고, 면, 그리고 는 and

-- between ..and, in() 그리고 like

-- 키가 180 - 183인 사람을 조회

select name, height 

from userTbl

where height >= 180 and height <= 183;

-- 방법2 (숫자나 연속적인 데이터에는 between and 사용)

select name, height 

from userTbl

where height between 180 and 183;

-- in()

-- 지역이 겨안ㅁ, 전남, 경북인 사람의 정보 확인

select name, addr

from userTbl

where addr = '경남'

or addr = '전남'

or addr = '경북';

-- 방법 2

select name, addr

from userTbl

where addr in('경남','전남','경북');

-- 문자열 내용검색 like

-- 성이 김씨인 사람

select name, height from userTbl where name like '김%';

-- 아무거나 앞에 한글자 오고 뒤에 이름만 매치

select name, height from userTbl where name like '_종신';

-- % 나 _ 가 검색할 문자열의 제일 앞에 들어가는 것은 mysql 성능에 나쁜 영향을 줄 수 있다. 예로 name열을 %용 이나

-- _ 용필등으로 검색하면 name 열에 인덱스가 있어도 인덱스를 사용하지 않고 전체 데이터를 검색하게 된다.

-- any/all/some 그리고 서브쿼리(쿼리 안에 또 다른 쿼리가 있는 것 )

-- 김경호 보다 키가 큰 사람 출력

-- 방법1

select name, height 

from userTBL

where height > 177;

-- 김경호의 키를 모른다고 가정했을 때

select name, height 

from userTBL

where height > (select height from userTBL where name = '김경호'); 

-- 서브 쿼리에서 출력되는 열은 반드시 하나 이어야 한다.

-- 지역이 경남인 사람의 키보다 크거나 같은 사람을 추출해 보자

select name, height 

from userTBL

where height >=(select height from userTBL where addr ='경남');

-- Error Code: 1242. Subquery returns more than 1 row

-- any 구문 (서브쿼리의 여러개 결과 중 한 가지만 만족해도 되며, all은 서브쿼리의 여러개의 결과를 모두 만족시켜야 한다.)

-- some은 any와 동일한 의미로 사용된다.

select name, height 

from userTBL

where height >= any (select height from userTBL where addr ='경남');

-- all

select name, height 

from userTBL

where height >= all (select height from userTBL where addr ='경남');

-- =any(서브쿼리)는 in(서브쿼리)와 동일한 의미

select name, height 

from userTBL

where height =any (select height from userTBL where addr ='경남');

-- in(서브쿼리)

select name, height 

from userTBL

where height in (select height from userTBL where addr ='경남');

-- 원하는 순서대로 정렬하여 출력 : order by 

-- 기본 오름차순 정렬

select name, mDate

from userTbl

order by mDate;

-- 내림차순

select name, mDate

from userTbl

order by mDate desc;

-- order by는 성능을 떨어뜨릴 소지가 있으므로 되도록 사용하지는 말것

-- 중복된것은 하나만 남기는 distinct

select addr from userTbl;

-- distic

select distinct addr from userTbl;

-- 출력 개수를 제한 하는 limit

-- 나이가 제일 많은 회원 5명

select name, birthYear

from userTbl

order by birthYear asc;

-- 전체 다볼필요 없는데, 전체를 보여준다...낭비

-- limit 사용

select name, birthYear

from userTbl

order by birthYear asc

limit 5;

-- limit 시작개수, 마지막수

select name, birthYear

from userTbl

order by birthYear asc

limit 0, 5;

-- 테이블을 복사하는 CREATE TABLE ...SELECT 

-- 형식 : create TABLE 새로운 테이블 (SELECT 복사할열 FROM 기존 테이블)

-- 단 pk나 fk 등의 제약 조건은 복사되지 않는다.

CREATE TABLE buyTbl2(select * from buyTbl);

select * from buyTbl2;

-- group by 및 having 그리고 집계 함수

-- group by 절은 그룹으로 묶어주는 역할을 한다.

-- 구매 테이블에서 사용자가 구매한 물품의 개수 구하기

select userID, amount 

from buyTbl

order by userID;

-- 결과는 사용자별로 여러번의 물건 구매가 이루어져 각각의 행이 별도로 출력된다.

-- 집계함수 사용. 집계함수는 주로 GROUP BY 저로가 함께 쓰이며 데이터를 그룹화 해주는 기능을 한다.

SELECT userID, SUM(amount)

from buyTbl 

group by userID;

SELECT userID AS '사용자ID', SUM(amount) AS '총 구매 개수'

from buyTbl 

group by userID;

-- 구매액의 총합출력 . 가격 * 수량

SELECT userID AS '사용자ID', SUM(price*amount) AS '총 구매액'

from buyTbl 

group by userID;

-- 전체 구매자가 구매한 물품의 개수

select avg(amount) as '평균 구매 개수' 

from buyTbl;

-- 각 사용자별로 한 번 구매시 물건을 평균 몇개 구매했는지 보자

select userID,avg(amount) as '평균 구매 개수' 

from buyTbl

group by userID;

-- 전체 회원의 수 카운트

SELECT COUNT(*) FROM userTbl;

-- 휴대폰 이 있는 회원만 조회

SELECT COUNT(mobile1) FROM userTbl;

-- 가장 큰 키와 가장 작은키의 회원 이름과 키를 출력

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

-- having 절

-- 앞에서 했던 sum()을 사용해서 사용자별 총구매액을 구해보자

SELECT userID AS '사용자ID', SUM(price*amount) AS '총 구매액'

from buyTbl 

group by userID;

-- 이 중에서 총 구매액이 1,000 이상인 사용자에게만 사은품을 증정하고 싶다면?

SELECT userID AS '사용자ID', SUM(price*amount) AS '총 구매액'

from buyTbl

where SUM(price*amount) > 1000 

group by userID;

-- 오류가 뜬다  having을 사용! having은 집계함수에 대한 조건!

SELECT userID AS '사용자ID', SUM(price*amount) AS '총 구매액'

from buyTbl 

group by userID

having sum(price*amount);

-- rollup

-- 총합 또는 중간합계가 필요하다면 grop by 절과 함께 with rollup 문을 사용하면 된다.

-- 만약 분류 groupname 별로 합계 및 그 총합을 구하고 싶다면 다음의 구문을 사용하자

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

-데이터베이스,테이블, 뷰, 인덱스 등의 데이터베이스 개체를 생성/삭제/변경하는 역할 

-CREATE, DROP, ALTER (ROLLBACK 이나 COMMIT 을 못시킴)

DCL

- 사용자에게 어떤 권한을 부여하거나 빼앗을 때 주로 사용하는 구문

- GRANT/REVOKE/DENY 등

*/

-- 자동으로 증가하는 AUTO_INCREMENT

-- AUTO_INCREMENT로 지정할때 PK 또는 UNIQUE로 지정해줘야 하고 숫자만 가능

-- 대량의 샘플 데이터 생성

/*

CREATE table TEST1(id int, name varchar(50),lname varchar(50));

insert into TEST1

SELECT EMP_NO, FIRST_NAME, LAST_NAME

FROM EMPLOYESS.EMPLOYESS;

*/

-- 데이터의 수정 update

/*

UPDATE 테이블이름 

SET 열1=값1, 열2=값2

WHERE 조건;

*/

-- WHERE절은 생략이 가능하지만 생략하면 테이블의 전체의 행이 변경된다.

-- 구매 테이블에서 현재의 단가가 모두 1.5배 인상되었다면..

-- UPDATE buyTbl set price = price * 1.5;

-- 테이블 삭제 종류 3가지

-- delete from tbl; -- 가장 오래 거린다.

-- drop table tbl; -- 테이블 자체를 삭제한다.

-- truncate table tbl; -- 테이블 구조를 남겨 놓고 데이터만 삭제

-- 조건부 데이터 입력, 변경

-- 첫행에 오류가 발생해도 뒷행은 실행 되게 하는 법

-- ignore

-- insert ignore into membertbl value('bbk','비비코','미국')

-- insert ignore into membertbl value('djm','동짜몽','서울')