DML
: 데이터 조작 언어, 로 테이블의 행을 대상으로 한다. 트랜잭션이 발생한다.
DDL
: 데이터 정의 언어, 데이터베이스, 테이블, 뷰, 인덱스 등 데이터베이스 개체를 생성,삭제,변경하는 역할을 한다. 트랜잭션을 발생시키지 않는다.
DCL
: 사용자에게 권한을 부여하거나 빼앗을 때
source C:\Users\cha\Downloads\ig_clone_data.sql
SHOW database : 현재 서버에 있는 데이터베이스들을 조회
USE DB이름 : 사용할 DB 지정
SHOW TABLE STATUS : 현재 DB의 테이블들을 조회
DESC 테이블이름 : 해당 테이블의 열을 조회
열의 이름에 별칭 설정 : 컬럼 뒤에 AS 로 설정
DROP DATABASE (IF EXISTS) 이름 : DB 삭제
SELECT database(); : 현재 사용중인 DB를 확인할 때 사용
SELECT NAME AS 이름 , GENDER 성별 FROM employees;
SELECT name, height FROM USERTBL WHERE height BETWEEN 180 AND 183;
SELECT name, addr FROM USERTBL WHERE addr IN ('경남', '경북', '전남');
SELECT name, addr FROM USERTBL WHERE name LIKE '김%';
SELECT name, addr FROM USERTBL WHERE name LIKE '_길동';
ANY
(조건 중 하나만 만족해도 됨), ALL
(모두 만족) SELECT name, height FROM USERTBL WHERE height >
(SELECT height FROM USERTBL WHERE Name = '홍길동');
SELECT name, height FROM usertbl WHERE
height = (SELECT MAX(height) FROM usertbl)
OR
height = (SELECT MIN(height) FROM usertbl)
SELECT name, mDate FROM USERTBL ORDER BY mDate DESC, name ASC;
SELECT DISTINCT addr FROM USERTBL;
SELECT name, mDate FROM USERTBL ORDER BY mDate DESC LIMIT 5;
CREATE TABLE buytbl2 (SELECT userID, proName FROM buytbl1);
SELECT userId as '사용자 아이디', SUM(price*amount) as '총 구매액'
FROM buytbl GROUP BY userId;
SELECT userID AS '사용자', SUM(price*amount) AS '총구매액'
FROM buytbl GROUP BY userID
HAVING SUM(price*amount) > 1000;
SELECT
title, AVG(rating)
FROM
full_reviews
GROUP BY title WITH ROLLUP; --> title별 평균값과 전체 title의 평균을 반환
SELECT
title, COUNT(rating)
FROM
full_reviews
GROUP BY title WITH ROLLUP;
SELECT
first_name, released_year, genre, AVG(rating)
FROM
full_reviews
GROUP BY released_year , genre WITH ROLLUP;
->연도와 장르별 평균, 연도별 평균, 연도와 장르의 전체 평균
// 초기값을 1000으로 설정하고 3씩 증가한다.
ALTER TABLE testTable AUTO_INCREMENT=1000;
SET @@auto_increment_increment=3;
CREATE TABLE testTable (id int, name varchar(50));
INSERT INTO testTable VALUES(1, '차승준');
INSERT INTO testTable
SELECT emp_no, name
FROM employees;
UPDATE testTbl SET name = '없음'
// 이렇게하면 모든 name 데이터가 '없음'으로 변경된다
DELETE FROM testTbl WHERE name = 'cha';
TRUNCATE TABLE 테이블이름
TRUNCATE는 테이블은 남기고 안에 데이터만 삭제한다, DDL이므로 트랜잭션이 발생하지 않아서 속도가 빠르다. 하지만 삭제된 데이터를 복구할 수 없다. DELETE문은 ROLLBACK이 가능하다.
INSERT (IGNORE) INTO : INSERT과정에서 문제가 생긴 행은 제외하고 나머지 INSERT 수행
ON DUPLICATE KEY UPDATE : pk가 중복되면 update문이 실행되고, 중복되지 않으면 일반 insert문으로 실행된다.
INSERT INTO testTable VALUES ('BBK', '비비코', '미국')
ON DUPLICATE KEY UPDATE name='비비코', addr='미국';
DROP TABLE 테이블이름;
여러 테이블을 동시에 삭제하려면
DROP TABLE 테이블1, 테이블2, 테이블3;
1. 열의 추가
ALTER TABLE 테이블이름
ADD 컬럼명 속성
2. 열의 삭제
ALTER TABLE 테이블이름
DROP 컬럼명
3. 컬럼명 변경
ALTER TABLE 테이블명
RENAME COLUMN address to address2;
4. 컬럼명과 데이터 형식을 같이 수정할 때는 change (잘 사용안한다)
ALTER TABLE 테이블이름
CHANGE 현재컬럼명 새로운컬럼명 VARCHAR(20) NULL;
5. 컬럼의 데이터 형식을 수정
ALTER TABLE 테이블이름
MODIFY 컬럼명 VARCHAR(20) NULL;
4. 열의 제약조건 추가 및 삭제
ALTER TABLE 테이블이름
DROP PRIMARY KEY;
5. 테이블 명 변경
rename table 원래이름 to 변경할 이름;
ALTER TABLE 테이블이름
DROP FOREIGN KEY 외래키이름;
INSERT INTO PEOPLE (first_name, last_name, age)
VALUES
("linda", "belcher", 45),
("Phillip", "Frond", 38),
("Calvin", "Fischoeder", 70);
SELECT DISTINCT CONCAT(author_fname,' ', author_lname) FROM books;
SELECT DISTINCT author_fname, author_lname FROM books;
-> 둘 다 같은 결과를 반환한다.
SELECT * FROM books
ORDER BY author_lname;
SELECT * FROM books
ORDER BY author_lname DESC;
SELECT * FROM books
ORDER BY released_year;
-------------------------
SELECT book_id, author_fname, author_lname, pages
FROM books ORDER BY 2 desc; -> author_fname 기준으로 정렬
SELECT book_id, author_fname, author_lname, pages
FROM books ORDER BY author_lname, author_fname;
-> 첫번째 정렬기준 충족 후 두번째 정렬기준을 적용한다
select released_year as ry
from books order by ry asc; -> 별칭을 이렇게 사용할 수 있다.
SELECT title, author_fname, author_lname, pages
FROM books
WHERE author_fname LIKE '%da%';
SELECT title, author_fname, author_lname, pages
FROM books
WHERE title LIKE '%:%';
SELECT * FROM books
WHERE author_fname LIKE '____';
-> _는 한개의 문자를 의미한다. author_fname가 네글자인 books 데이터를 찾는 것이다.
SELECT * FROM books
WHERE author_fname LIKE '_a_';
---------------------------------
-- To select books with '%' in their title:
SELECT * FROM books
WHERE title LIKE '%\%%';
-> %가 들어간 데이터를 찾고 싶을 때는 앞에 역슬래쉬를 붙여준다
-- To select books with an underscore '_' in title:
SELECT * FROM books
WHERE title LIKE '%\_%';
-> _가 들어간 데이터를 찾고 싶을 때는 앞에 역슬래쉬를 붙여준다
SELECT
title,
AVG(rating),
COUNT(rating) AS review_count
FROM full_reviews
GROUP BY title HAVING COUNT(rating) > 1;
SELECT @@GLOBAL.sql_mode; - 글로벌 설정
SELECT @@SESSION.sql_mode; - 현재 세션을 설정
jpa:
properties:
hibernate:
physical_naming_strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
select first_name, last_name, IFNULL(sum(amount), 0) from customers
left join orders on customers.id = customer_id
group by first_name, last_name;
UNSIGNED
(부호 없는 정수를 지원)CREATE TABLE CAT(
NAME VARCHAR(20) NOT NULL,
AGE TINYINT UNSIGNED NOT NULL,
PRICE DECIMAL(5,2) NOT NULL
);
CHAR(N)
: 고정길이 문자형으로 자릿수가 고정되어 있다. CHAR(100)에 ABC를 저장해도 100자리를 모두 확보한 후 97자리는 공백이 추가된다. 삽입되는 데이터가 항상 같은 크기(고정된 길이)라면 CHAR가 효율적이다.ex)
YES/NO flags: Y/N
zip code: 16953, 58971
VARCHAR(N)
: 가변길이 문자형으로 VARCHAR(100)는 3글자를 저장할 경우 3자리만 사용하게된다. 공간을 효율적으로 사용할 수 있지만 CHAR형식이 INSERT/UPDATE 시에 더 좋은 성능을 발휘한다.DATE
: YYYY-MM-DD (날짜만 저장하고 시간은 저장하지 않는다)TIME
: HH:MM:SSDATETIME
: YYYY-MM-DD HH:MM:SS, 값의 범위가 1000년 ~ 9999년이다.TIMESTAMP
: YYYY-MM-DD HH:MM:SS, 값의 범위가 1970년 ~ 2038년이다. 세계적으로 국가마다 다른 표준시간을 정해놓고 사용하는데, TIMESTAMP는 해당 국가의 표준시간에 맞춰 저장을 하게 도와준다. CURDATE()
: 현재 날짜CURRENT_TIME() = CURTIME()
: 현재 시간CURRENT_TIMESTAMP() = NOW()
: 현재 날짜와 시간-----------------------------------------------
CREATE TABLE people (
name VARCHAR(100),
birthdate DATE,
birthtime TIME,
birthdt DATETIME
);
INSERT INTO people (name, birthdate, birthtime, birthdt)
VALUES ('Hazel', CURDATE(), CURTIME(), NOW());
DAY()
: 날짜 중 일 가져오기SELECT DAY(birthdate) FROM people;
DAYOFWEEK()
: 요일 가져오기(일요일=1, 월요일=2 .. 토요일=7)date_format(date, format)
: 날짜 표현 형식변경time_format(time, format)
datediff(날짜1, 날짜2), timediff(시간1, 시간2)
: 날짜1 - 날짜2(또는 시간)를 하고 일수(시간)를 반환한다date_add, date_sub
: 날짜를 더하거나 뺀다. (interval
을 사용한다)SELECT birthdate, DATE_FORMAT(birthdate, '%a %b %D') FROM people;
SELECT birthdt, TIME_FORMAT(birthdt, '%H:%i') FROM people;
select birthdate, datediff(curdate(), birthdate) from people;
select date_add(curdate(), interval 1 year);
select NOW() - interval 18 YEAR;
current_timestamp, on update current_timestamp
: TIMESTAMP, DATETIME 타입 둘 다 적용이 가능하다.CREATE TABLE captions2 (
text VARCHAR(150),
created_at TIMESTAMP default CURRENT_TIMESTAMP,
updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
SELECT * FROM PEOPLE WHERE YEAR(birthdate) < 2000;
SELECT * FROM PEOPLE WHERE HOUR(birthdate) < 11 ;
SET @TESTVAR = 5;
SELECT @TESTVAR;
SET @TESTVAR2 = '가수 이름 ==> `;
SET @TESTVAR = 5;
PREPARE 쿼리이름 FROM '쿼리문';
PREPARE TESTQUERY FROM 'SELECT name, height FROM USERTBL
ORDER BY height LIMIT ?;
EXECUTE TESTQUERY USING @TESTVAR;
SELECT JSON_OBJECT('name', name, 'age', age) AS person_json FROM usertbl;
+-------------------------+
| person_json |
+-------------------------+
| {"name": "John", "age": 30} |
+-------------------------+
select concat(author_fname, ' ' ,author_lname) as author_name from books;
select concat_ws('!', author_fname ,author_lname) as author_name from books
-> 각각의 값뒤에 !가 붙으면서 문자열이 결합된다
SELECT SUBSTRING('Hello World', 1, 4); -> Hell
SELECT SUBSTRING('Hello World', 7);
SELECT SUBSTRING('Hello World', -3);
SELECT SUBSTRING(title, 1, 10) AS 'short title' FROM books;
SELECT SUBSTR(title, 1, 10) AS 'short title' FROM books;
SELECT CONCAT
(
SUBSTRING(title, 1, 10),
'...'
) AS 'short title'
FROM books;
----------------------------------------
SELECT CONCAT
(
SUBSTRING(author_fname, 1, 1),
'.',
SUBSTRING(author_lname, 1, 1),
'.'
) AS 'short author_Name'
FROM books; -> J.L.
SELECT REPLACE('Hello World', 'Hell', '%$#@'); -> %$#@o World
SELECT REPLACE('Hello World', 'l', '7');
SELECT REPLACE('Hello World', 'o', '0');
SELECT REPLACE('HellO World', 'o', '*');
SELECT
REPLACE('cheese bread coffee milk', ' ', ' and ');
SELECT REPLACE(title, 'e ', '3') FROM books;
SELECT REPLACE(title, ' ', '-') FROM books;
SELECT REVERSE('Hello World');
SELECT REVERSE('meow meow');
SELECT REVERSE(author_fname) FROM books;
SELECT CONCAT('woof', REVERSE('woof'));
SELECT CONCAT(author_fname, REVERSE(author_fname)) FROM books;
SELECT CHAR_LENGTH('Hello World');
SELECT CHAR_LENGTH(title) as length, title FROM books;
SELECT author_lname, CHAR_LENGTH(author_lname) AS 'length' FROM books;
SELECT CONCAT(author_lname, ' is ', CHAR_LENGTH(author_lname), ' characters long') FROM books;
SELECT UPPER('Hello World');
SELECT LOWER('Hello World');
SELECT UPPER(title) FROM books;
SELECT CONCAT('MY FAVORITE BOOK IS ', UPPER(title)) FROM books;
SELECT CONCAT('MY FAVORITE BOOK IS ', LOWER(title)) FROM books;
SELECT COUNT(*) FROM books;
SELECT COUNT(author_lname) FROM books;
SELECT COUNT(DISTINCT author_lname) FROM books;
(~별)
SELECT author_fname, author_lname, COUNT(*)
FROM books
GROUP BY author_lname, author_fname;
--------------------------------------
SELECT CONCAT(author_fname, ' ', author_lname) AS author, COUNT(*)
FROM books
GROUP BY author;
author별 COUNT(*)
-----------------------------------------
select released_year, avg(stock_quantity) from books GROUP BY released_year;
released_year별 avg(stock_quantity)
SELECT MAX(pages) FROM books;
SELECT MIN(author_lname) FROM books;
SELECT title, pages FROM books
WHERE pages = (SELECT MAX(pages) FROM books);
SELECT title, released_year FROM books
WHERE released_year = (SELECT MIN(released_year) FROM books);
select author_fname, author_lname, sum(pages)
from books
GROUP BY author_fname, author_lname;
select released_year, avg(stock_quantity)
from books
GROUP BY released_year;
!=
SELECT * FROM books
WHERE released_year != 2017;
LIKE
, NOT LIKE
SELECT * FROM books
WHERE title NOT LIKE '%e%';
제목에 e가 들어가 있지 않은 책 검색
AND
: 모든 조건을 만족하는 결과를 출력SELECT title, author_lname, released_year FROM books
WHERE released_year > 2010
AND author_lname = 'Eggers'
AND title LIKE '%novel%';
OR
: 조건들 중 하나만 만족해도 출력IN, NOT IN
: 조건이 많을 때는 IN을 사용하는 것이 효율적 SELECT title, author_lname, released_year FROM books
WHERE author_lname='Eggers' OR
released_year > 2010;
SELECT title, pages FROM books
WHERE pages < 200
OR title LIKE '%stories%';
-------------------------------
<IN>
SELECT title, author_lname FROM books
WHERE author_lname IN ('Carver', 'Lahiri', 'Smith');
SELECT * FROM BOOKS WHERE released_year NOT IN('2001', '2004', '2010');
SELECT title, released_year FROM books
WHERE released_year >= 2000
AND released_year % 2 = 1;
BETWEEN, NOT BETWEEN
: 범위의 양쪽 끝이 포함된다select * from books where released_year >= 2004 and released_year <= 2015;
select * from books where released_year BETWEEN 2004 and 2015;
select * from books where released_year NOT BETWEEN 2004 and 2015;
시간, 날짜 비교하기
SELECT * FROM PEOPLE WHERE YEAR(birthdate) < 2000;
SELECT * FROM PEOPLE WHERE HOUR(birthdate) < 11 ;
SELECT * FROM people WHERE HOUR(birthtime)
BETWEEN 12 AND 16;
SELECT * FROM people WHERE birthtime
BETWEEN CAST('12:00:00' AS TIME)
AND CAST('16:00:00' AS TIME);
CASE WHEN THEN ELSE END AS
SELECT title, released_year,
CASE
WHEN released_year >= 2000 THEN 'modern lit'
ELSE '20th century lit'
END AS genre
FROM books;
--------------------------------------------
SELECT
title,
stock_quantity,
CASE
WHEN stock_quantity <= 40 THEN '*'
WHEN stock_quantity <= 70 THEN '**'
WHEN stock_quantity <= 100 THEN '***'
WHEN stock_quantity <= 140 THEN '****'
ELSE '*****'
END AS stock
FROM
books;
---------------------------------
SELECT author_fname, author_lname,
CASE
WHEN COUNT(*) = 1 THEN '1 book'
ELSE CONCAT(COUNT(*), ' books')
END AS count
FROM books
WHERE author_lname IS NOT NULL
GROUP BY author_fname, author_lname;
IS NULL, IS NOT NULL
: NULL인 것과 NULL이 아닌 것을 선택DELETE FROM BOOKS WHERE title IS NULL;
OVER()
select department, avg(salary) from employees GROUP BY department;
select department, avg(salary) over() from employees;
-> 전체 employees의 평균값이 전체 행에 같이 나온다.
2. OVER(PARTITION BY )
: GROUP BY와 같은 기능
select department, avg(salary) over(PARTITION BY department)
from employees;
-> 부서별로 묶는다. 단 전체 행을 출력한다.
select emp_no, department, salary,
avg(salary) OVER(PARTITION BY department) AS department_avg,
avg(salary) OVER() AS company_avg from employees;
-> 이렇게 개인임금, 부서별 평균임금과 회사의 평균임금을 비교가능
OVER(PARTITION BY ' ' ORDER BY ' ')
select emp_no, department, salary,
SUM(salary) over(PARTITION BY department ORDER BY salary DESC) AS rolling_depy_sal,
SUM(salary) over(PARTITION BY department) AS department_sum
FROM employees;
SUM(salary)과 같은 집계연산을 할 때, PARTITION BY는 윈도우별로 값을 계산하는데 ORDER BY를 추가하면 차례대로 연산한 값을 출력한다. (롤링합계)
RANK()를 사용하여 순위를 계산할 수도 있다.
select emp_no, department, salary,
RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS department_sal_rank,
RANK() OVER(ORDER BY salary DESC) AS overall_sal_rank
FROM employees ORDER BY emp_no;
4. ROW_NUMBER(), DENSE_RANK()
: RANK()는 순위를 매기기 때문에 동점이 있으면 다음 숫자로 넘어간다. (4등에서 동점이라면 5등없이 6등으로 넘어간다).
하지만 ROW_NUMBER()
는 행의 개수가 몇개인지 체크한다. DENSE_RANK()
는 중복되는 점수가 있더라도 순위를 건너띄지 않는다.
SELECT emp_no, department, salary,
RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS department_sal_rank,
RANK() OVER(ORDER BY salary DESC) overall_sal_rank,
DENSE_RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS overall_dense_rank,
ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) as dept_row_number
FROM employees ORDER BY overall_sal_rank;
NTILE(4)
: 분위를 4로 나눈다. 분위가 낮을수록 값이 높다.SELECT emp_no, department, salary,
NTILE(4) OVER(ORDER BY salary DESC) AS salary_qurt,
NTILE(4) OVER(PARTITION BY department) AS dept_salary_qurt
FROM employees;
6. FIRST_VALUE()
: 첫번째 값을 가져온다. 전체 사원중 급여가 가장 높은 사원의 번호, 부서별로 급여가 가장 높은 사원의 번호
SELECT emp_no, department, salary,
FIRST_VALUE(emp_no) OVER(ORDER BY salary DESC),
FIRST_VALUE(emp_no) OVER(PARTITION BY department ORDER BY salary DESC)
FROM employees;
LAG()
: 이전 행의 값을 가져온다. 이전 행과의 급여차이를 계산한다.SELECT emp_no, department, salary,
salary - LAG(salary) OVER(PARTITION BY department ORDER BY salary DESC)
FROM employees;
8. LEAD()
: 다음 행의 값을 가져온다. 다음 행과의 차이를 계산한다.
CREATE TABLE companies(
name VARCHAR(20) NOT NULL ,
address VARCHAR(20) NOT NULL
COMSTRAINT companies_name_address UNIQUE(name, address)
name이 같을 수 있고, address도 같을 수 있지만 ame, address 둘 다 일치해서는 안된다.
(다중열 제약조건)
);
CREATE TABLE people (
age INT
COMSTRAINT age_over_0 CHECK(age > 0)
COMSTRAINT로 설정한 제약조건에 이름을 설정한다.