[SQL] Query example

사용 데이터베이스 : mysql

-- DATABASE --

SHOW databases;

CREATE database test;

DROP DATABASE test;

-- TABLE --

SHOW TABLES
CREATE TABLE user (id int PRIMARY KEY AUTO_INCREMENT, name varchar(255) not null, email varchar(255), age int, gender varchar(255))
CREATE TABLE book (id int PRIMARY KEY AUTO_INCREMENT, name varchar(255) not null, user varchar(255), date datetime)
DROP TABLE user

DESCRIBE user

-- SELECT --

SELECT * FROM user
SELECT * FROM user WHERE name='a'
SELECT * FROM user ORDER BY age ASC
SELECT * FROM user ORDER BY age DESC
SELECT * FROM user WHERE name='a' ORDER BY age DESC
SELECT * FROM user WHERE Not name='a' ORDER BY age DESC
SELECT * FROM user WHERE gender IS NULL
SELECT * FROM user WHERE gender LIKE "%_ale"
SELECT * FROM user WHERE gender LIKE "fe_ale"
SELECT * FROM user WHERE gender LIKE "_ale"
SELECT * FROM user LIMIT 5
SELECT COUNT(*) FROM user;
SELECT COUNT(age) FROM user;
SELECT COUNT(*) FROM user WHERE gender="male";
SELECT COUNT(age) FROM user WHERE name="a";
SELECT * FROM user WHERE name BETWEEN 'c' AND 'F'
SELECT DISTINCT name FROM user;
SELECT * FROM user WHERE age IN ('24', '21') AND name IN ('a', 'abcd');

-- INSERT --

INSERT INTO user(name, email, age, gender) VALUES ('a', 'b', '23', 'male');
INSERT INTO user(name) VALUES ('a');
INSERT INTO user(name, gender) VALUES ('a', 'female');
INSERT INTO user2(gender) VALUES ('female');

-- UPDATE --

UPDATE user SET name='abcd' WHERE age='24'

-- DELETE --

DELETE FROM user WHERE id=10

-- ALTER --

ALTER TABLE user ADD birth datetime
ALTER TABLE user DROP birth	
ALTER TABLE user MODIFY COLUMN age varchar(255) NOT NULL

-- GROUP BY -- 

SELECT COUNT(*) FROM user GROUP BY gender

-- JOIN --

INSERT INTO user(name, email, age, gender) VALUES ('홍길동', 'hong@gmail.com', '25', 'male');
INSERT INTO user(name, email, age, gender) VALUES ('홍길동2', 'hong2@gmail.om', '30', 'female');
INSERT INTO user(name, email, age, gender) VALUES ('홍길동3', 'hong3@gmail.om', '40', 'male');
INSERT INTO user(name, email, age, gender) VALUES ('홍길동4', 'hong4@gmail.om', '17', 'female');
INSERT INTO user(name, email, age, gender) VALUES ('홍길동5', 'hong5@gmail.om', '32', 'male');
INSERT INTO user(name, email, age, gender) VALUES ('홍길동6', 'hong5@gmail.om', '32', 'male');

INSERT INTO book(name, user, date) VALUES ('예약1', '홍길동', now());
INSERT INTO book(name, user, date) VALUES ('예약2', '홍길동2', now());
INSERT INTO book(name, user, date) VALUES ('예약3', '홍길동3', now());
INSERT INTO book(name, user, date) VALUES ('예약4', '홍길동4', now());
INSERT INTO book(name, user, date) VALUES ('예약5', '홍길동5', now());

SELECT * FROM book INNER JOIN user ON book.user=user.name

SELECT * FROM book LEFT OUTER JOIN user ON book.user=user.name
SELECT * FROM book RIGHT OUTER JOIN user ON book.user=user.name