졸업시험을 맞이하여(?) 데이터베이스 과목을 대비할 겸으로 SQL 명령어 문법에 대해서 정리했다.
여기서 정리한 내용의 근원(?)은 'SQL 이 보이는 그림책' 이라는 책이다.
SQL 기본 문법
데이터 베이스 만들기
CREATE DATABASE test;
테이블 만들기
CREATE TABLE linux (name varchar(10), version int, people int, arch char(20));
데이터 베이스 지정하기
USE test;
데이터 등록하기
INSERT INTO linux (name, version, arch) VALUES ('fat', 2.4, 'arm'); INSERT INTO linux VALUES ('fat', 2.4, 10, 'arm');
데이터 가져오기
SELECT * FROM linux; SELECT name FROM linux;
데이터 정렬
SELECT * FROM linux ORDER BY version ASC; SELECT * FROM linux ORDER BY version DESC; SELECT * FROM linux ORDER BY version, arch ASC;
데이터 그룹화하기
SELECT name, COUNT(version) FROM linux GROUP BY name;
중복 데이터 제거하기
SELECT DISTINCT name FROM linux;
열 이름 바꿔서 표시하기
SELECT name AS '이름' FROM linux;
기본 조작
WHERE 절
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;
산술 연산자
SELECT name, (version + people) AS sum FROM linux;
논리 연산자
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);
문자열 비교
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';
BETWEEN 연산자
SELECT version FROM linux WHERE people BETWEEN 1 AND 5;
IS NULL 연산자
SELECT * FROM linux WHERE name IS NULL; SELECT * FROM linux WHERE name IS NOT NULL;
IN 연산자
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);
함수
수치 함수
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);
문자열 함수
SELECT LENGTH('fat81'); SELECT SUBSTRING('fater81', 3, 2); SELECT LTRIM(' ABC'); SELECT RTRIM('ABC '); SELECT UPPER('fat81'); SELECT LOWER('FAT81');
집합 함수
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);
기본 조작 II
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;
서브 쿼리
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);
JOIN
위에서 예를 들었던 linux 라는 테이블과 같은 구조로 bsd 라는 테이블이 또하나 있다고 가정한다.
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;
VIEW
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;
집합 연산자
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;
한정 술어
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');
테이블 포맷 변경
열 추가하기
ALTER TABLE linux ADD media VARCHAR(10); ALTER TABLE linux ADD media VARCHAR(10) DEFAULT 'not have';
열 삭제하기
ALTER TABLE linux DROP media;
테이블명 변경하기
ALTER TABLE linux RENAME TO new_linux;
열 이름 변경하기
ALTER TABLE linux CHANGE name user_id CHAR(20);