Head first SQL (mySQL) 정리

• DB
  • DB생성 : CREATE DATABASE test;
  • DB목록 조회 :  SHOW DATABASES;
  • DB삭제 : DROP DATABASE test;
• 사용자 관리
  • DB를 사용할 사용자 계정을 추가
  • root 사용자만이 사용자를 관리
  • 사용자 추가 – id:tiger    pw:dbgood
    • create user ‘tiger’@’localhost’ IDENTIFIED BY ‘dbgood’;
    • SELECT host, user FROM user;    /*확인*/
  • 사용자 제거
    • DROP USER ‘tiger’@’localhost”;
• 사용자 권한 관리
  • 사용자가 특정 DB를 사용할 수 있도록 권한 부여
  • 권한 추가 – id:tiger   대상DB : test
    • GRANT ALL ON test.* TO ‘tiger’@’localhost’ WITH GRANT OPTION;
  • 권한 제거
    • REVOKE ALL PRIVILEGES, GRANT OPTION FROM ‘tiger’@’localhost’;
    • FLUSH PRIVILEGES;    /*권한 변경 후 바로 적용*/
• 테이블 만들기
  • 테이블 생성
    • 먼저 test데이터베이스 사용한다 명시해야함
    • use test;
    • CREATE TABLE user(
    • id CHAR(10) NOT NULL DEFAULT id,  //null값이 들어가면 에러남. 디폴트값에 id넣어줌
    • name CHAR(20),
    • passwd CHAR(10)
    • );
  • 테이블 조회
    • SHOW TABLES;
    • DESC user;      /*describe. */
    • SHOW CREATE TABLE user;
  •  테이블 삭제
    • DROP TABLE user;
  • 데이터 삽입
    • INSERT INTO user(id, name, passwd) VALUES(‘lee’, ‘leegood’, ‘dbgood’);
  • 데이터 확인
    • SELECT * FROM user;
    • SELECT drink_name, main FROM easy_drinks;
  • SHOW
    • SHOW COLUMNS FROM tablename;
      • 테이블의 모든 열 표시하고 데이터타입도.
    • SHOW CREATE DATABASE databasename;
      • SHOW CREATE TABLE처럼 데이터베이스를 생성하는 명령을 얻을 수 있다.
    • SHOW INDEX FROM tablename;
      • 인덱싱되어있는 열과 무슨 타입의 인덱스를 가지고 있는지를 표시.
    • SHOW WARNINGS;
      • 실제 경고메세지를 확인
• 기타
  • 뒤에  N

 

<4. SQL-데이터 생성>

 

학습목표

• 데이터 생성을 위한 CREATE TABLE관련 SQL을 알고 사용.
  • CREATE TABLE, 제약조건, KEY, UNIQUE
• 데이터 생성을 위한 CREATE TABLE관련 SQL을 알고 사용
  • 단건, 대량건

CREATE TABLE

• 테이블 만들기
  • CREATE TABLE user(
  •     id INT,
  •     passwd CHAR(10),
  •     registry_date DATE,
  •     access_hours DEC(8,1)    //정수부 4, 소수부 1자리를 갖는 5자리 숫자
  • );
• 생성한 테이블 정보 조회하기
  • DESC user;
  • 아니면
  • SHOW CREATE TABLE tablename;

 

INSERT INTO

• 데이터 삽입 명령
  • INSERT INTO book(id, title, publication, inventory)
  •     values(100, ‘head first SQL”, ‘한빛미디어’, 3);
  • 혹은
  • INSERT INTO book values(100, ‘head first SQL”, ‘한빛미디어’, 3);

테이블의 제약조건

• NOT NULL : 속성값이 반드시 값이 존재해야함을 명시
  • NULL값의 의미는 정의불가(프로그래밍 언어에서 널과 다름)
• PRIMARY KEY(id) : 레코드값 중복되지 않고 유일해야 하도록 제약
• UNIQUE(title) : Primary key가 아닌데 유일해야 하는 속성값
• DEFAULT 0 : 값이 입력되지 않았을 때 기본적으로 값을 0으로 함.

데이터 타입

• MySQL 데이터 타입
  • CHAR(m) : 고정길이 문자열, m=1~255
  • VARCHAR(m) : 가변길이 문자열. m=1~255. CHAR보다 검색속도가 느리지만 공간 효율적
  • 수: TINYINT, SMALLINT, INT, UNSIGNED INT, FLOAT, DOUBLE, DECIMAL(=NUMERIC, DEC)
  • 시간 : DATE, TIME, YEAR, TIMESTAMP, DATETIME
    • TIMESTAMP: 값 미입력시 자동으로 현재시간 입력됨
  • 대용량
    • tinyBLOB/tinyText : 최대길이 255개의 문자 저장
    • BLOB/ Text : 최대길이 65535
    • midiumBLOB/mediumText : 16777215
    • LongBLOB/ LongText : 4294967295(4GB)
  • 이미지 데이터 저장, 조회
    • DROP TABLE IF EXISTS blob_table;
    • CREATE TABLE blog_table)
    •   id INT PRIMARY KEY AUTO_INCREMENT,
    •   file VARCHAR(64),
    •   img_blob MEDIUMBLOB
    • );
    • INSERT INTO blob_table(img_blob, file) values(load_file(“C://test.jpg”), ‘test.jpg’);
    • SELECT * FROM blob_table;    //BLOB데이터는 이렇게 보면 안대용!
    • SELECT img_blob INTO DUMPFILE ‘c://dump.jpg’ FROM blob_table WHERE file=’test.jpg’;
  • 대량 데이터 생성
    • 생성, 입력해야할 데이터가 수천건이 넘어도 모두 INSERT INTO 로 생성해야하나요?
      • 가능은 하지만 그렇게하진 않아요.
    • 다른 db나 텍스트 파일 형태로 있는 대량 데이터를 MySQL로 대량입력할 수 없나요?
      • 파일로 저장된 대량 데이터 입력가능, DB간 데이터 입력도 가능.
  • 형식 맞아야 한다.
    • 작은따옴표 사용: CHAR, VARCHAR, DATE, DATETIME, TIME, TIMESTAMP, BLOB
    • 안사용 : DEC, INT
    • 작은따옴표 붙이려면 백슬래시 사용.
      • INSERT INTO hi VALUES(‘Jay Jin\’s Blog);

SELECT

• 많은 앤 중에 앤찾기!
  • 전체 테이블을 힘들게 찾지 말고 셀렉트문으로 찾아라.
  • SELECT * FROM my_contacts WHERE first_name=’Anne’;
  • SELECT doughnut, rating FROM my_contacts WHERE first_name=’Anne’;
• 쿼리들의 결합
  • SELECT location FROM doughnut_ratings WHERE type=’plain glazed’ AND rating>5;
  • OR도 사용가능.
• NULL찾기
  • SELECT drink_name FROM drink_info WHERE calories IS NULL;
• 문자 일부로 검색
  • SELECT * FROM my_contacts WHERE location LIKE ‘%CA’;
    • 퍼센트 기호를 작은따옴표 안에 : location열이 CA로 끝나는 모든 값을 찾기
    • ‘_im’ : _은 단 하나의 불특정 문자를 대신하는 문자
• BETWEEN
  • SELECT drink_name FROM drink_info WHERE calories BETWEEN 30 AND 60;
• IN
  • SELECT date_name FROM note WHERE rating=’good’ OR rating=’fabulous’ OR…;
  • 이걸 IN이란 키워드를 사용. 열의 값이 괄호안의 값들 중 하나와 일치하면 그 행 또는 지정된 열들이 반환.
  • …FROM note WHERE raiting IN (‘good’, ‘fabulous’, delightful’);
  • NOT IN : IN이 표시하는 집합과 일치하지 않는 행을 반환.
• NOT
  • BETWEEN이나 LIKE와 함께도 사용가능.
    • 단, NOT이 WHERE바로 다음에 와야한다.
  • NOT IN, WHERE NOT, AND NOT…

 

DELETE

• DELETE FROM clown_info WHERE activities = ‘dancing’;
  • WHERE조건에 맞는 모든 레코드가 테이블에서 지워짐
  • DELETE FROM your_table
    • 테이블의 모든 행을 지운다!
• INSERT-DELETE
  • SELECT * FROM clown_info WHERE activities=’dancing’;
  • INSERT INTO clown_info VALUES(‘Zippo’,ddddd);
  • DELETE FROM clown_info WHERE activities = ‘dancing’;

UPDATE

• WHERE조건을 만족하는 투플의 특정 속성값을 수정
  • *주의: WHERE생략시 테이블의 모든 투플의 속성값 수정
• UPDATE doughnut SET type=’glaze’ WHERE type=’plain glazed’;
• UPDATE doughnut SET first_col=’new’, second-col=’new2′;
• UPDATE drink_info SET cost=cost+1 WHERE drink_name=’blue moon’ OF drink_name=’oh’;
• UPDATE my_contacts SET state = RIGHT(location, 2);
  • state라는 새로운 열에 location열에서 두 문자를 추출해서 집어넣음

CASE

• 기존 열의 값과 조건을 비교하여 UPDATE문을 합치긔
  • UPDATE my_table SET new_column =
  • CASE
  •   WHEN column1 = somevalue1
  •     THEN newvalue1
  •   WHEN column2 = somevalue2
  •     THEN newvalue2
  •   ELSE newvalue3
  • END;

문자 함수

• SUBSTRING_INDEX(interests, ‘,’, 2) = ‘animals’;
  • 두 번째 콤마를 찾아서 그 앞의 모든 걸 반환
• SUBSTR(interests, LENGTH(interest1)+2);
  • 문자열을 가져다 괄호안에 명시한 일부를 자르고 나머지를 반환
  • interest1에 있는 문자+2(콤마랑 공백) 해서 잘라서 버려버림.
• 마지막 두 문자 추출
  • RIGHT()와 LEFT()를 사용해 열로부터 정해진 수의 문자들 선택
  • SELECT RIGHT(location, 2) FROM my_contacts;
• 콤마 앞의 모든 문자 추출
  • 특정 문자나 문자열 앞의 모든 문자열을 반환
  • SELECT SUBSTRING_INDEX(location, ‘,’) FROM my_contacts;
• SELECT SUBSTRING(your_string, start_pos, length)
  • sart_post에 있는 문자에서부터 시작해서 your_string의 일부를 반환. length는 반환되는 문자열의 길이.
• SELECT UPPER(‘uSa’);
  • 문자열 uSa를 모두 대문자로 변환(<–>LOWER)
• SELECT REVERSE(‘love’);
  • 문자열 순서 역순으로
• SELECT LTRIM(‘    dogfood’);
  • 문자열의 앞에 있는 공백문자들을 제거한 문자열반환(<–>RTRIM)
• SELECT LENGTH(‘san Antonio’);
  • 문자수 반환

ALTER

• ex)
  • ALTER TABLE my_contacts
  • ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST,
  • DROP COLUMN start_date,
  • ADD PRIMARY KEY(id);
• 키워드
  • FIRST: 모든 열 앞에 ㅇㅇ를 추가
  • LAST: 모든 열 뒤에 ㅇㅇ를 추가
  • FIFTH: 위치지정가능.
  • BEFORE last_name : last_name열 전에 추가.
• 테이블 이름 바꾸기
  • ALTER TABLE projekts RENAME TO project;
• CHANGE COLUMN
  • 이름과 데이터타입 모두 변경
  • ALTER TABLE project CHANGE COLUMN number proj_id INT NOT NULL AUTO_INCREMENT,
  • ADD PRIMARY KEY(proj_id);
  • number열을 새 이름 proj_id로 변경하고 AUTO_INCREMENT, 그리고 기본키로 설정
• 하나의 SQL문으로 두개열 변경
  • ALTER TABLE project_list
  • CHANGE COLUMN dee proj_desc VARCHAR(100),
  • CHANGE COLUMN coo con_name VARCHAR(30);
• MODIFY
  • ALTER TABLE project_list MODIFY COLUMN prom_desc VARCHAR(120);
  • 열의 데이터 타입만 바꾸고 이름은 그대로 사용.
• 열 제거
  • ALTER TABLE project DROP COLUMN start_date;
• 기본키 없애기
  • ALTER TABLE table DROP PRIMARY KEY;

ORDER BY

• 알파벳순 정렬
  • SELECT title, category, purchased
  • FROM movie_table
  • WHERE category=’family’
  • ORDER BY title, purchased DESC //DESC: 역순으로 정렬
  • LIMIT 2; //결과를 2개로 제한

연산

• SUM
  • SELECT SUM(sales) FROM cookie_sales
  • WHERE name=’Nicole’;
• 그룹합
  • SELECT name, SUM(sales) FROM cookie_sales
  • GROUP BY name ORDER BY SUM(sales) DESC;    //304쪽 참고
• 기타
  • MAX(sales)   MIN(sales)
  • COUNT(sale_date)    //sale date열의 행의 수를 반환
  • AVG(salary)    //평균연봉

DISTINCT

• ex)
  • SELECT DISTINCT sale_date
  • FROM cookie_sales
  • ORDER BY sale_date;
• ex)
  • SELECT COUNT (DISTINCT sale_date)
  • FROM cookie_sales;

LIMIT

 

• 오잉

AS

 

• 새로 만드는 테이블을 SELECT의 결과로 채운다.
• 예
  • CREATE TABLE profession
  • (
  •   id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  •   profession VARCHAR(20)
  • ) AS                 //SELECT의 결과를 모아 새 테이블에 넣는다.
  •   SELECT profession FROM my_contacts
  •   GROUP BY profession
  •   ORDER BY profession;
• 별명 만들기
  • CREATE TABLE profession
  • (
  •   id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  •   profession VARCHAR(20)
  • ) AS                 //SELECT의 결과를 모아 새 테이블에 넣는다.
  •   SELECT profession AS mc_prof FROM my_contacts AS ac       //AS생략가능.
  •   GROUP BY mc_prof
  •   ORDER BY mc_prof;

카티젼 조인

• 한번에 두 테이블에 쿼리 수행. toys테이블에서 toy열과, boys테이블에서 boy열 가져오는 경우 결과값을 카티젼 결과값이라 한다.
• 예
  • SELECT t.toy, b.boy
  • FROM toys AS t CROSS JOIN boys AS b;

내부 조인

• 조건을 사용하여 두 테이블의 레코드를 결합
• 결합된 행들이 조건을 만족할 경우에만 열들이 반환.
• 예
  • SELECT somecolumns FROM table1
  •   INNER JOIN table 2                     //INNER안써도 됨.
  • ON somecondition;

내부조인-동등 조인

• 같은지를 테스트하는 내부 조인

• 예
  • SELECT boys.boy, toys.toy
  • FROM boys INNER JOIN toys
  • ON boys.toy_id = toys.toy_id
  • ORDER BY boys.boy;

내부조인-비동등조인

• 같지 않은 모든 행들 반환.
• 예
  • SELECT boys.boy, toys.toy
  • FROM boys INNER JOIN toys
  • ON boys.toy_id <> toys.toy_id
  • ORDER BY boys.boy;

내부조인-자연조인

• 두 테이블에 같은 이름의 열이 있을때만 동작
• 예
  • SELECT boys.boy, toys.toy
  • FROM boys NATURAL JOIN toys;

서브 쿼리(내부쿼리)

• 다른 쿼리에 쌓여진 쿼리
• 비상관 서브쿼리
  • 내부쿼리 수행하고 그 값을 사용해서 외부쿼리 결과 알아냄
  • 서브쿼리가 외부쿼리의 어떤 것도 참조하지 않을때.
  • IN이나 NOT IN을 사용하여 값이 서브 쿼리에서 반환된 집합의 원소인지(아닌지)를 확인
• 상관 서브쿼리
  • 내부 쿼리의 값이 결정되는데 외부 쿼리에 의존
• 응답속도
  • 크로스조인, 상관서브쿼리는 느린 편
  • 조인이 서브쿼리보단 빠름

 

<10. 외부 조인, 셀프 조인, 그리고 유니온>

 

외부 조인

• 조인되는 테이블 중 하나의 모든 행을 다른 테이블에서 일치하는 정보와 함께 반환.
• 두 테이블 사이의 관계와 좀더 관련.
• 왼쪽 외부조인
  • 왼쪽 테이블의 모든 행을 가져다 오른쪽테이블의 행과 비교.
  • 왼쪽 테이블과 오른쪽 테이블이 일대다 관계에 있을때 유용.
  • ex
    • SELECT g.girl, t.toy
    • FROM girls g
    • LEFT OUTER JOIN toys t
    • ON g.toy_id = t.toy_id;
  • 다른 테이블과 일치하는 것이 있는가에 상관없이 행을 반환.
    • 일치하는게 없으면 NULL
    • 왼쪽 외부조인의 결과가 NULL값이면 오른쪽 테이블에 왼쪽테이블에 상응하는 값이 없다는 의미.