- 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;
- 테이블 삭제
- 데이터 삽입
- 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;
- 기타
<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값이면 오른쪽 테이블에 왼쪽테이블에 상응하는 값이 없다는 의미.
블로그 이사갔어요! https://milooy.github.io/ 