SQL
select*from memberTBL;
select*from producttbl;
select name,address from memberTBL;
-- memberTBL에 name이 지운이인 데이터 가져오기
select*from memberTBL where name = '지운이';
-- memberTBL에 있는 지운이라는 이름을 가진 자의 주소 데이터 가져오기
select address from memberTBL where name = '지운이';
-- 테이블 만들기
CREATE TABLE my TestTBL(id INT);
-- 테이블 삭제
DROP TABLE my TestTBL;
-- productTBL스키마에 productName이 세탁기인 데이터 찾기
SELECT*FROM productTBL WHERE productName='세탁기';
-- indexTBL 테이블 만들기
CREATE TABLE indexTBL (
first_name varchar(14),
last_name varchar(16),
hire_date date
);
-- 다른 스키마에 있는 데이터 추가하기
-- employees 스키마에 employees 테이블 데이터를 indexTBL에 500개까지 넣는다.
INSERT INTO indexTBL
SELECT first_name, last_name, hire_date
FROM employees.employees
limit 500;
-- indexTBL에서 first_name이 Mary인 사람 가져오기
SELECT*FROM indexTBL WHERE first_name = 'Mary';
-- 인덱스를 만드는데 이름이 idx_indexTBL_firstname 이고 기준은 first_name(테이블:indexTBL)
CREATE INDEX idx_indexTBL_firstname ON indexTBL(first_name);
-- VIEW 테이블 만들기
CREATE VIEW uv_memberTBL
AS
SELECT name, address FROM memberTBL;
select*from uv_memberTBL;
DELIMITER //
CREATE PROCEDURE myProc()
BEGIN
SELECTFROM memberTBL WHERE name = '당탕이';
SELECTFROM productTBL WHERE productName = '냉장고';
END //
DELIMITER ;
CALL myProc();
-- CRUD
-- C: Create R: Read U: Upadte D: Delete
-- insert select update delete
-- insert 데이터 추가
INSERT INTO memberTBL VALUES('Figure', '연아', '경기도 군포시 당정동');
-- update 데이터 수정
UPDATE memberTBL SET address = '서울 강남구 역삼동' WHERE name = '연아';
select*from memberTBL;
-- 회원 삭제 시 백업 데이터를 저장하는 테이블 생성
CREATE TABLE deletedMemberTBL(
memberID chAR(8),
MEMberName char(5),
memberAddress CHAR(20),
deletedDate DATE -- 삭제한 날짜
);
-- 회원 삭제 시 삭제된 데이터를 백업 테이블에 저장하는 트리거 생성
DELIMITER //
CREATE TRIGGER TRg_deletedMemberTBL -- 트리거 이름
AFTER DELETE -- 삭제 후에 작동하게 지정
ON memberTBL -- 트리거를 부착할 테이블
FOR EACH ROW -- 각 행마다 적용시킴
BEGIN
-- OLD 테이블의 내용을 백업 테이블에 삽입
INSERT INTO deletedMemberTBL
VALUES(OLD.id, OLD.name, old.address, CURDATE());
END //
DELIMITER ;
DELETE FROM memberTBL WHERE name = '연아';
select*from memberTBL;
select*from deletedmembertbl;
SHOW TABLES;
-- 현재 데이터베이스의 테이블 목록 조회
USE employees;
-- employees 데이터베이스 선택
SELECT * FROM employees;
-- employees 테이블의 모든 데이터 조회
USE shopDB;
-- shopDB 데이터베이스 선택
SELECT * FROM memberTBL;
-- memberTBL의 모든 데이터 조회
CREATE TABLE test(id INT);
-- test 테이블 생성
CREATE TABLE test(id INT); -- 에러
-- 이미 존재하는 test 테이블을 다시 생성하여 오류 발생
INSERT INTO test VALUES(1);
-- test 테이블에 데이터 1개 삽입
SELECT * FROM test;
-- test 테이블의 모든 데이터 조회
-- 권한을 주는 쿼리문
USE employees;
SELECT*FROM employees;
CREATE USER director@'%' IDENTIFIED BY 'director';
GRANT ALL ON . TO director@'%' WITH GRANT OPTION;
CREATE USER ceo@'%' IDENTIFIED BY 'ceo';
GRANT SELECT ON . TO ceo@'%';
CREATE USER staff@'%' IDENTIFIED BY 'staff';
GRANT SELECT, INSERT, UPDATE, DELETE ON shopdb.TO staff@'%';
GRANT SELECT ON employees.TO staff@'%';
-- employees 데이터베이스 선택
USE employees;
SELECT * FROM employees;
-- employees 테이블의 모든 데이터 조회
SELECT * FROM titles;
-- titles 테이블의 모든 데이터 조회
SELECT first_name FROM employees;
-- 직원의 이름(first_name)만 조회
SELECT first_name, last_name FROM employees;
-- 직원의 이름과 성 조회
SHOW DATABASES;
-- 데이터베이스 목록 조회
SHOW TABLE STATUS;
-- 현재 데이터베이스의 테이블 정보 조회
SELECT first_name AS 이름, gender 성별, hire_date '회사 입사일'
FROM employees;
-- 조회 결과의 컬럼명을 별칭으로 변경하여 출력
DROP DATABASE IF EXISTS sqldb; -- 만약 sqldb가 존재하면 우선 삭제한다.
CREATE DATABASE sqldb;
USE sqldb;
CREATE TABLE usertbl -- 회원 테이블
(userID CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 ID (PK)
name VARCHAR(10) NOT NULL, -- 이름
birthYear INT NOT NULL, -- 출생년도
addr CHAR(2) NOT NULL, -- 지역(경기, 서울, 인천 식으로 2글자만 입력)
mobile1 CHAR(3), -- 휴대폰의 국번
mobile2 CHAR(8), -- 나머지 전화번호 (하이픈 제외)
height SMALLINT, -- 키
mDate DATE -- 회원 가입일
);
CREATE TABLE buytbl -- 회원 구매 테이블(Buy Table의 약자)
(num INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 순번(PK)
userID CHAR(8) NOT NULL, -- 아이디 (FK)
prodName CHAR(6) NOT NULL, -- 물품명
groupName CHAR(4), -- 분류
price INT NOT NULL, -- 단가
amount SMALLINT NOT NULL, -- 수량
FOREIGN KEY (userID) REFERENCES usertbl(userID)
);
INSERT INTO usertbl VALUES('LSG', '이승기', 1987, '서울', '011', '11111111', 182, '2008-8-8');
INSERT INTO usertbl VALUES('KBS', '김범수', 1979, '경남', '011', '22222222', 173, '2008-4-4');
INSERT INTO usertbl VALUES('KKH', '김경호', 1971, '전남', '019', '33333333', 177, '2008-7-7');
INSERT INTO usertbl VALUES('JYP', '조용필', 1950, '경기', '011', '44444444', 166, '2008-4-4');
INSERT INTO usertbl VALUES('SSK', '성시경', 1979, '서울', NULL, NULL, 186, '2008-12-12');
INSERT INTO usertbl VALUES('LJB', '임재범', 1963, '서울', '016', '66666666', 182, '2008-9-9');
INSERT INTO usertbl VALUES('YJS', '윤종신', 1969, '경남', NULL, NULL, 170, '2008-5-5');
INSERT INTO usertbl VALUES('EJW', '은지원', 1972, '경북', '011', '88888888', 174, '2008-3-3');
INSERT INTO usertbl VALUES('HKW', '조관우', 1965, '경기', '018', '99999999', 172, '2008-10-10');
INSERT INTO usertbl VALUES('BBK', '바비킴', 1973, '서울', '010', '00000000', 176, '2008-5-5');
INSERT INTO buytbl VALUES(NULL, 'KBS', '운동화', NULL, 30, 2);
INSERT INTO buytbl VALUES(NULL, 'KBS', '노트북', '전자', 1000, 1);
INSERT INTO buytbl VALUES(NULL, 'JYP', '모니터', '전자', 200, 1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '모니터', '전자', 200, 5);
INSERT INTO buytbl VALUES(NULL, 'KBS', '청바지', '의류', 50, 3);
INSERT INTO buytbl VALUES(NULL, 'BBK', '메모리', '전자', 80, 10);
INSERT INTO buytbl VALUES(NULL, 'SSK', '책', '서적', 15, 5);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책', '서적', 15, 2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '청바지', '의류', 50, 1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화', NULL, 30, 2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책', '서적', 15, 1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화', NULL, 30, 2);
SELECTFROM usertbl;
SELECTFROM buytbl;
SELECT*FROM usertbl WHERE name = '김경호'; -- where 조건절
SELECT userID, Name FROM usertble WHERE birthYear >= 1970; -- 비교 연산자
SELECT userID, Name FROM usertbl WHERE birthYear >= 1970 AND height >= 182;
SELECT userID, Name FROM usertbl WHERE birthYear >= 1970 OR height >= 182;
SELECT name, height FROM usertbl WHERE height >= 180 AND height <= 183;
SELECT name, height FROM usertbl WHERE height BETWEEN 180 AND 183;
SELECT name, addr FROM usertbl WHERE addr='경남' OR addr='전남' OR addr='경남';
SELECT name, addr FROM usertbl WHERE addr IN ('경남','전남','경북');
SELECT name, height FROM usertbl WHERE name LIKE '김%'; -- 김씨 성인 사람
SELECT name, height FROM usertbl WHERE name LIKE '_종신'; -- 이름 종신인 사람
SELECT name, height FROM usertbl WHERE height > 177;
SELECT name, height FROM usertbl
WHERE height > (SELECT height FROM usertbl WHERE Name = '김경호'); -- 서브쿼리
SELECT name, height FROM usertbl
WHERE height >= (SELECT height FROM usertbl WHERE addr = '경남'); -- 에러!
SELECT height FROM usertbl WHERE addr = '경남';
SELECT name, height FROM usertbl
WHERE height >= ANY (SELECT height FROM usertbl WHERE addr = '경남');
SELECT name, height FROM usertbl
WHERE height >= ALL (SELECT height FROM usertbl WHERE addr = '경남');
SELECT name, mDate FROM usertbl ORDER BY mDate; -- 기본이 오름차순
SELECT name, mDate FROM usertbl ORDER BY mDate DESC; -- 내림차순
SELECT name, height FROM usertbl ORDER BY height DESC, name ASC;
-- 키로 우선 내림차순 그 이후에 이름으로 오름차순
SELECT addr FROM usertbl;
SELECT addr FROM usertbl ORDER BY addr;
SELECT DISTINCT addr FROM usertbl; -- DISTINCT 중복 제거
SELECT DISTINCT addr FROM usertbl order by addr;
USE employees;
SELECT emp_no, hire_date FROM employees
ORDER BY hire_date ASC;
SELECT emp_no, hire_date FROM employees
ORDER BY hire_date ASC
LIMIT 5;
SELECT emp_no, hire_date FROM employees
ORDER BY hire_date ASC
LIMIT 0, 5; -- LIMIT 5 OFFSET 0과 동일
use sqldb;
CREATE TABLE buytbl2(SELECTFROM buytbl); -- SELECT를 이용한 테이블 생성
SELECTFROM buytbl2;
CREATE TABLE buytbl3(SELECT userID, prodName FROM buytbl);
SELECT*FROM buytbl3;
select*from buytbl;
select userID, sum(amount) from buytbl group by userID;
SELECT userID AS '사용자 아이디', SUM(amount) AS '총 구매 개수'
FROM buytbl GROUP BY userID;
SELECT AVG(amount) AS '평균 구매 개수' FROM buytbl;
SELECT userID, AVG(amount) AS '평균 구매 개수' FROM buytbl GROUP BY userID;
SELECT userID AS '사용자 아이디', SUM(price*amount) AS '총 구매액'
FROM buytbl GROUP BY userID;
SELECT name, height
from usertbl
where height = (select max(height)from usertbl)
or height = (select min(height) from usertbl);
selectfrom usertbl;
select count() from usertbl;
SELECT COUNT(mobile1)AS '휴대폰이 있는 사용자' FROM usertbl;
select count(*) AS '휴대폰이 있는 사용자' from usertbl where mobile1 is not Null;
-- 에러!!
SELECT userID AS '사용자', SUM(priceamount) AS '총 구매액'
FROM buytbl
WHERE SUM(priceamount)>100
GROUP BY userID;
SELECT userID AS '사용자', SUM(priceamount) AS '총 구매액'
FROM buytbl
GROUP BY userID
HAVING SUM(priceamount)>1000;
SELECT userID AS '사용자', SUM(price amount) AS '총 구매액'
FROM buytbl
GROUP BY userID
HAVING SUM(priceamount) > 1000
ORDER BY SUM(price*amount) desc;
SELECT num, groupName, SUM(price*amount) AS '비용'
FROM buytbl
GROUP BY groupName, num
WITH ROLLUP;
SELECT num, groupName, SUM(price*amount) AS '비용'
FROM buytbl
GROUP BY groupName
WITH ROLLUP;
CREATE TABLE testTbl1 (id int, userName char(3), age int);
INSERT INTO testTbl1 VALUES(1, '홍길동', 25); -- 순서대로 전체 다 넣은 경우
INSERT INTO testTbl1(id, userName) VALUES(2, '설현'); -- 컬럼 중에 안넣는 것이 생기면
INSERT INTO testTbl1(userName, age, id) VALUES('하니', 26, 3); -- 전체 다 넣은 것은 맞는데 순서가 바뀌었다
SELECT*FROM testtbl1;
CREATE TABLE testTbl2
(id int AUTO_INCREMENT PRIMARY KEY,
userName char(3),
age int);
INSERT INTO testTbl2 VALUES (NULL, '지민', 25);
INSERT INTO testTbl2 VALUES (NULL, '유나', 22);
INSERT INTO testTbl2 VALUES (NULL, '유경', 21);
SELECT*FROM testTbl2;
SELECT LAST_INSERT_ID();
ALTER TABLE testTbl2 AUTO_INCREMENT=100;
INSERT INTO testTbl2 VALUES (NULL, '찬미', 23);
SELECT*FROM testTbl2;
마무리
오늘은 sql에 대해 배워보았다.
쿼리문 작성도 하고 이것저것 명령문도 많이 쳐봤다.
재미있었다.
