졸업시험을 맞이하여(?) 데이터베이스 과목을 대비할 겸으로 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;

기본 조작

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';
SELECT version FROM linux WHERE people BETWEEN 1 AND 5;
SELECT * FROM linux WHERE name IS NULL;
SELECT * FROM linux WHERE name IS NOT NULL;
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);
  • computer/database/sql_핵심가이드.txt
  • Last modified: 4 years ago
  • by likewind