====== SQL 핵심가이드 ======
졸업시험을 맞이하여(?) 데이터베이스 과목을 대비할 겸으로 SQL 명령어 문법에 대해서 정리했다.
여기서 정리한 내용의 근원(?)은 'SQL 이 보이는 그림책' 이라는 책이다.
====== SQL 기본 문법 ======
===== 데이터 베이스 만들기 =====
<code text>
CREATE DATABASE test;
</code>
===== 테이블 만들기 =====
<code text>
CREATE TABLE linux (name varchar(10), version int, people int, arch char(20));
</code>
===== 데이터 베이스 지정하기 =====
<code text>
USE test;
</code>
===== 데이터 등록하기 =====
<code text>
INSERT INTO linux (name, version, arch) VALUES ('fat', 2.4, 'arm');
INSERT INTO linux VALUES ('fat', 2.4, 10, 'arm');
</code>
===== 데이터 가져오기 =====
<code text>
SELECT * FROM linux;
SELECT name FROM linux;
</code>
===== 데이터 정렬 =====
<code text>
SELECT * FROM linux ORDER BY version ASC;
SELECT * FROM linux ORDER BY version DESC;
SELECT * FROM linux ORDER BY version, arch ASC;
</code>
===== 데이터 그룹화하기 =====
<code text>
SELECT name, COUNT(version) FROM linux GROUP BY name; 
</code>
===== 중복 데이터 제거하기 =====
<code text>
SELECT DISTINCT name FROM linux;
</code>
===== 열 이름 바꿔서 표시하기 =====
<code text>
SELECT name AS '이름' FROM linux;
</code>
====== 기본 조작 ======
===== WHERE 절 =====
<code text>
SELECT name FROM linux WHERE name = 'fat';
SELECT version FROM linux WHERE version >= 2.5;
SELECT * FROM linux WHERE version >= 2.4 ORDER BY name DESC;
</code>
===== 산술 연산자 =====
<code text>
SELECT name, (version + people) AS sum FROM linux;
</code>
===== 논리 연산자 =====
<code text>
SELECT * FROM linux WHERE (version >= 2.4) OR (people <= 2.6);
SELECT * FROM linux WHERE (version >= 2.4) AND (people <= 2.6);
SELECT * FROM linux WHERE NOT (version >= 2.4) OR (people <= 2.6);
</code>
===== 문자열 비교 =====
<code text>
SELECT name FROM linux WHERE name LIKE 'fa%';
SELECT name FROM linux WHERE name LIKE 'fa_;
SELECT name FROM linux WHERE name LIKE '%a%';
SELECT name FROM linux WHERE name LIKE '__t';
</code>
===== BETWEEN 연산자 =====
<code text>
SELECT version FROM linux WHERE people BETWEEN 1 AND 5;
</code>
===== IS NULL 연산자 =====
<code text>
SELECT * FROM linux WHERE name IS NULL;
SELECT * FROM linux WHERE name IS NOT NULL;
</code>
===== IN 연산자 =====
<code text>
SELECT people FROM linux WHERE version IN (2.4, 2.5, 2.6);
SELECT people FROM linux WHERE version NOT IN (2.4, 2.5, 2.6);
</code>
====== 함수 ======
===== 수치 함수 =====
<code text>
SELECT CEILING(55.44);
SELECT FLOOR(2.58);
SELECT RAND();
SELECT RAND(5);
SELECT ABS(-4);
SELECT ROUND(3.345, 2);
SELECT ROUND(3.345, 4);
SELECT POWER(2, 4)
SELECT SQRT(81);
SELECT MOD(10, 3);
SELECT SIN(90);
SELECT COS(90);
SELECT TAN(90);
SELECT SIGN(-4);
SELECT SIGN(0);
SELECT SIGN(3);
</code>
===== 문자열 함수 =====
<code text>
SELECT LENGTH('fat81');
SELECT SUBSTRING('fater81', 3, 2);
SELECT LTRIM('   ABC');
SELECT RTRIM('ABC   ');
SELECT UPPER('fat81');
SELECT LOWER('FAT81');
</code>
===== 집합 함수 =====
<code text>
SELECT AVG(people) AS '평균 사람수' FROM linux;
SELECT SUM(people) AS '모든 사람수' FROM linux;
SELECT COUNT(people) AS '열의 개수' FROM linux;
SELECT COUNT(DISTINCT people) AS '열의 개수' FROM linux;
SELECT MAX(people) AS '최대 인원' FROM linux;
SELECT MIN(people) AS '최소 인원' FROM linux'
SELECT name, MAX(people) AS '최대 인원' FROM linux GROUP BY name;
SELECT name, MAX(people) AS '최대 인원' FROM linux GROUP BY name HAVING (MAX(people) > 10);
</code>
====== 기본 조작 II ======
<code text>
INSERT INTO linux (name, people) SELECT arch, version FROM linux;
UPDATE linux SET version = 2.4 WHERE name = 'fat81';
UPDATE linux SET version = 2.4, arch = 'arm' WHERE people = 12;
DELETE FROM linux WHERE name = 'fat81';
DELETE FROM linux;
</code>
====== 서브 쿼리 ======
<code text>
SELECT * FROM linux WHERE people >= (SELECT AVG(people) FROM linux);
SELECT name, MIN(people) FROM linux GROUP BY name HAVING MIN(people) < (SELECT AVG(version) FROM linux);
SELECT MIN(people) FROM (SELECT * FROM linux WHERE people >= 20) AS c_people;
INSERT INTO linux SELECT * FROM linux WHERE people > (SELECT AVG(version) FROM linux);
UPDATE linux SET people = 20 WHERE version < (SELECT MAX(version) FROM linux);
DELETE FROM linux WHERE people > (SELECT AVG(people) FROM linux WHERE version >= 2.5);
</code>
====== JOIN ======
위에서 예를 들었던 linux 라는 테이블과 같은 구조로 bsd 라는 테이블이 또하나 있다고 가정한다.
<code text>
SELECT * FROM linux CROSS JOIN bsd;
SELECT * FROM linux INNER JOIN bsd ON people = version;
SELECT * FROM linux INNER JOIN bsd ON linux.people = bsd.version;
SELECT * FROM linux LEFT JOIN bsd ON people = version;
SELECT * FROM linux LEFT JOIN bsd ON linux.people = bsd.version;
SELECT * FROM linux RIGHT JOIN bsd ON people = version;
SELECT * FROM linux RIGHT JOIN bsd ON linux.people = bsd.version;
SELECT * FROM linux FULL JOIN bsd ON people = version;
SELECT * FROM linux FULL JOIN bsd ON linux.people = bsd.version;
</code>
====== VIEW ======
<code text>
CREATE VIEW viw_linux AS SELECT name, people FROM linux WHERE version = 2.5;
CREATE VIEW viw_linux AS SELECT name, people FROM linux, bsd WHERE linux.people = bsd.people;
INSERT INTO viw_linux (name, people) VALUES('fat81', 3);
UPDATE viw_linux SET name = 'fat81' WHERE people = 3;
DELETE FROM viw_linux WHERE people = 3;
DROP VIEW viw_linux;
</code>
====== 집합 연산자 ======
<code text>
SELECT name, version FROM linux UNION SELECT name, version FROM bsd;
SELECT name, version FROM linux UNION ALL SELECT name, version FROM bsd;
SELECT name, version FROM linux INTERSECT SELECT name, version FROM bsd;
SELECT name, version FROM linux EXCEPT SELECT name, version FROM bsd;
</code>
====== 한정 술어 ======
<code text>
SELECT * FROM linux WHERE people >  ALL (SELECT people FROM linux WHERE name LIKE '%a%');
SELECT * FROM linux WHERE EXISTS (SELECT name FROM linux WHERE no = 5);
SELECT * FROM linux WHERE NOT EXISTS (SELECT name FROM linux WHERE no = 5);
SELECT * FROM linux WHERE people = ANY (SELECT people FROM linux WHERE name LIKE '__t');
</code>
====== 테이블 포맷 변경 ======
===== 열 추가하기 =====
<code text>
ALTER TABLE linux ADD media VARCHAR(10);
ALTER TABLE linux ADD media VARCHAR(10) DEFAULT 'not have';
</code>
===== 열 삭제하기 =====
<code text>
ALTER TABLE linux DROP media;
</code>
===== 테이블명 변경하기 =====
<code text>
ALTER TABLE linux RENAME TO new_linux;
</code>
===== 열 이름 변경하기 =====
<code text>
ALTER TABLE linux CHANGE name user_id CHAR(20);
</code>

----
{{indexmenu>:#1|skipns=/^(wiki|etc|diary|playground)$/ skipfile=/^(todays|about|guestbook)$/ nsort rsort}}
----