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);

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);

위에서 예를 들었던 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;

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);