개발일지/SQL
MySQL 두번째 수업
연습용365
2021. 10. 6. 13:51
create database Academydb;
use Academydb;
create table Book (
bookid int primary key,
bookname varchar(40),
publisher varchar(40),
price int);
CREATE TABLE Customer (
custid INT PRIMARY KEY,
name VARCHAR(40),
address VARCHAR(40),
phone VARCHAR(30));
insert into Customer values(1, '박지성', '영국 맨체스터', '010-5000-0001');
INSERT INTO Customer VALUES(2, '김연아', '대한민국 서울', '000-6000-0002');
INSERT INTO Customer VALUES(3, '장미란', '대한민국 강원도', '000-7000-0003');
INSERT INTO Customer VALUES(4, '추신수', '미국 클리블랜드', '000-8000-0004');
INSERT INTO Customer VALUES(5, '박세리', '대한민국 대천', NULL);
CREATE TABLE Orders (
orderid INT PRIMARY KEY,
custid INT REFERENCES Customer(custid),
bookid INT REFERENCES Book(bookid),
saleprice INT,
orderdate DATE);
INSERT INTO Orders VALUES(1, 1, 1, 6000, '2021-07-01');
INSERT INTO Orders VALUES(2, 1, 3, 21000, '2021-07-03');
INSERT INTO Orders VALUES(3, 2, 5, 8000, '2021-07-03');
INSERT INTO Orders VALUES(4, 3, 6, 6000, '2021-07-04');
INSERT INTO Orders VALUES(5, 4, 7, 20000, '2021-07-05');
INSERT INTO Orders VALUES(6, 1, 2, 12000, '2021-07-07');
INSERT INTO Orders VALUES(7, 4, 8, 13000, '2021-07-07');
INSERT INTO Orders VALUES(8, 3, 10, 12000, '2021-07-08');
INSERT INTO Orders VALUES(9, 2, 10, 7000, '2021-07-09');
INSERT INTO Orders VALUES(10, 3, 8, 13000, '2021-07-10');
insert into Book values(1, '축구의 역사', '굿스포츠', 7000);
INSERT INTO Book VALUES(2, '축구 아는 여자', '나무수', 13000);
INSERT INTO Book VALUES(3, '축구의 이해', '대한미디어', 22000);
INSERT INTO Book VALUES(4, '골프 바이블i', '대한미디어', 35000);
INSERT INTO Book VALUES(5, '피겨교본', '굿스포츠', 8000);
INSERT INTO Book VALUES(6, '역도 단계별 기술', '굿스포츠', 6000);
INSERT INTO Book VALUES(7, '야구의 추억', '이상미디어', 20000);
INSERT INTO Book VALUES(8, '야구를 부탁해', '이상미디어', 13000);
INSERT INTO Book VALUES(9, '올림픽 이야기', '상성당', 7500);
INSERT INTO Book VALUES(10, 'Olympic Champions', 'Pearson', 13000);
select * from Book;
select bookid, bookname, publisher, price from Book;
insert into Customer values(1, '박지성', '영국 맨체스터', '010-5000-0001');
INSERT INTO Customer VALUES(2, '김연아', '대한민국 서울', '000-6000-0002');
INSERT INTO Customer VALUES(3, '장미란', '대한민국 강원도', '000-7000-0003');
INSERT INTO Customer VALUES(4, '추신수', '미국 클리블랜드', '000-8000-0004');
INSERT INTO Customer VALUES(5, '박세리', '대한민국 대천', NULL);
테이블 안에 항목을 삽입 할 때 데이터 타입과 개수가 같아야 한다.
SELECT * FROM Customer;
항목들이 잘 들어가 있는지 확인하기 위해서 모든 항목을 확인한다.
select * from Book order by bookname;
이름순으로 정렬하는 예약어
select * from Book order by price, bookname;
가격으로 먼저 정렬하고, 책이름으로 정렬
select * from Book order by price desc, publisher asc;
도서 가격은 내림차순, 같은 가격인 경우 출판사는 오름차순
desc 내림차순, asc는 오름차순인데 지정하지 않아도 자동으로 붙음
입력한 순서대로 출력이 됨
CREATE TABLE Orders (
orderid INT PRIMARY KEY,
custid INT REFERENCES Customer(custid),
bookid INT REFERENCES Book(bookid),
saleprice INT,
orderdate DATE);
INSERT INTO Orders VALUES(1, 1, 1, 6000, '2021-07-01');
INSERT INTO Orders VALUES(2, 1, 3, 21000, '2021-07-03');
INSERT INTO Orders VALUES(3, 2, 5, 8000, '2021-07-03');
INSERT INTO Orders VALUES(4, 3, 6, 6000, '2021-07-04');
INSERT INTO Orders VALUES(5, 4, 7, 20000, '2021-07-05');
INSERT INTO Orders VALUES(6, 1, 2, 12000, '2021-07-07');
INSERT INTO Orders VALUES(7, 4, 8, 13000, '2021-07-07');
INSERT INTO Orders VALUES(8, 3, 10, 12000, '2021-07-08');
INSERT INTO Orders VALUES(9, 2, 10, 7000, '2021-07-09');
INSERT INTO Orders VALUES(10, 3, 8, 13000, '2021-07-10');
SELECT SUM(saleprice) From Orders;
총 세일 가격 더하기
SELECT SUM(saleprice) as 총매출 From Orders;
테이블에 다른 이름이 나올 때 as 총매출을 써주면 테이블에 총매출이라는 글이 써진다.
SUM(saleprice)이 뜨는 이유
SELECT SUM(saleprice) AS 총매출,
AVG(saleprice) AS 평균,
MIN(saleprice) AS 최저가,
MAX(saleprice) AS 최고가
FROM Orders;
합계, 평균, 가장 큰 값, 가장 작은 값
SELECT COUNT(*) FROM Orders;판매권수 검색
SELECT custid, COUNT(*) AS 도서수량, SUM(saleprice) AS 총액 FROM Orders group by custid;고객별로 주문한 도서의 총수량(도서수량)과 총판매액(총액)을 구하시오.
orderid가 아니라 custid를 써야 하는 이유는.. 커스터머의 기본키이면서 Orders 테이블의 외래키이다.
SELECT * FROM Customer, Orders;고객과 고객의 주문에 관한 데이터를 모두 검색
두 개의 테이블을 검색한다.
SELECT * FROM Customer, Orders WHERE Customer.custid = Orders.custid;고객 이름과 고객의 주문한 것을 모두 검색 (?)
Customer 테이블의 custid와 Orders 테이블의 custid 같은 것을 검색
SELECT name,saleprice FROM Customer, Orders WHERE Customer.custid = Orders.custid;고객의 이름과 고객이 주문한 도서의 판매가격을 검색
SELECT Customer.name, Book.bookname From Customer, Orders, Book WHERE Customer.custid = Orders.custid AND Orders.bookid = Book.bookid AND Book.price = 20000;고객이름과 고객이 주문한 도서중에서 가격이 20000원인 고객이름 책이름을 구하시오
CREATE DATABASE managementdb; /* CREATE SCHEMA */
USE managementdb;
CREATE TABLE 고객 (
고객아이디 VARCHAR(20) NOT NULL,
고객이름 VARCHAR(10) NOT NULL,
나이 INT,
등급 VARCHAR(10) NOT NULL,
직업 VARCHAR(20),
적립금 INT DEFAULT 0,
PRIMARY KEY(고객아이디)
);
INSERT INTO 고객 VALUES('apple', '정소화', 20, 'gold', '학생', 1000);
INSERT INTO 고객 VALUES('banana', '김선우', 25, 'vip', '간호사', 2500);
INSERT INTO 고객 VALUES('carrot', '고명석', 28, 'gold', '교사', 4500);
INSERT INTO 고객 VALUES('orange', '김용욱', 22, 'silver', '학생', 0);
INSERT INTO 고객 VALUES('melon', '성원용', 35, 'gold', '회사원', 5000);
INSERT INTO 고객 VALUES('peach', '오형준', NULL, 'silver', '의사', 300);
INSERT INTO 고객 VALUES('pear', '채광주', 31, 'silver', '회사원', 500);
SELECT * FROM 고객;
NOT NULL이 있으면 값을 꼭 넣어야 한다. 없으면 안 넣어도 무방
ALTER TABLE 고객 ADD 가입날짜 DATE;
테이블에 가입날짜 DATE 항목(Field) 추가
ALTER TABLE 고객 DROP COLUMN 가입날짜;
가입날짜 항목 없애기
CREATE DATABASE managementdb; /* CEATE SCHEMA */
USE managementdb;
CREATE TABLE 고객 (
고객아이디 VARCHAR(20) NOT NULL,
고객이름 VARCHAR(10) NOT NULL,
나이 INT,
등급 VARCHAR(10) NOT NULL,
직업 VARCHAR(20),
적립금 INT DEFAULT 0,
PRIMARY KEY(고객아이디)
);
ALTER TABLE 고객 ADD 가입날짜 DATE;
ALTER TABLE 고객 DROP COLUMN 가입날짜;
INSERT INTO 고객 VALUES('apple', '정소화', 20, 'gold', '학생', 1000);
INSERT INTO 고객 VALUES('banana', '김선우', 25, 'vip', '간호사', 2500);
INSERT INTO 고객 VALUES('carrot', '고명석', 28, 'gold', '교사', 4500);
INSERT INTO 고객 VALUES('orange', '김용욱', 22, 'silver', '학생', 0);
INSERT INTO 고객 VALUES('melon', '성원용', 35, 'gold', '회사원', 5000);
INSERT INTO 고객 VALUES('peach', '오형준', NULL, 'silver', '의사', 300);
INSERT INTO 고객 VALUES('pear', '채광주', 31, 'silver', '회사원', 500);
SELECT * FROM 고객;
CREATE TABLE 제품 (
제품번호 CHAR(3) NOT NULL,
제품명 VARCHAR(20),
재고량 INT,
단가 INT,
제조업체 VARCHAR(20),
PRIMARY KEY(제품번호),
CHECK(재고량 >= 0 AND 재고량 <= 10000)
);
CREATE TABLE 주문 (
주문번호 CHAR(3) NOT NULL,
주문고객 VARCHAR(20),
주문제품 CHAR(3),
수량 INT,
배송지 VARCHAR(30),
주문일자 DATE,
PRIMARY KEY(주문번호),
FOREIGN KEY(주문고객) REFERENCES 고객(고객아이디),
FOREIGN KEY(주문제품) REFERENCES 제품(제품번호)
);
CREATE TABLE 배송업체 (
업체번호 CHAR(3) NOT NULL,
업체명 VARCHAR(20),
주소 CHAR(100),
전화번호 CHAR(20),
PRIMARY KEY(업체번호)
);
SHOW TABLES;
DROP table 배송업체;
SELECT * FROM 주문;
INSERT INTO 제품 VALUES ('p01', '그냥만두', 5000, 4500, '대한식품');
INSERT INTO 제품 VALUES ('p02', '매운쫄면', 2500, 5500, '민국푸드');
INSERT INTO 제품 VALUES ('p03', '쿵떡파이', 3600, 2600, '한빛제과');
INSERT INTO 제품 VALUES ('p04', '맛난초콜릿', 1250, 2500, '한빛제과');
INSERT INTO 제품 VALUES ('p05', '얼큰라면', 2200, 1200, '대한식품');
INSERT INTO 제품 VALUES ('p06', '통통우동', 1000, 1550, '민국푸드');
INSERT INTO 제품 VALUES ('p07', '달콤비스킷', 1650, 1500, '한빛제과');
INSERT INTO 주문 VALUES ('o01', 'apple', 'p03', 10, '서울시 마포구', '19/01/01');
INSERT INTO 주문 VALUES ('o02', 'melon', 'p01', 5, '인천시 계양구', '19/01/10');
INSERT INTO 주문 VALUES ('o03', 'banana', 'p06', 45, '경기도 부천시', '19/01/11');
INSERT INTO 주문 VALUES ('o04', 'carrot', 'p02', 8, '부산시 금정구', '19/02/01');
INSERT INTO 주문 VALUES ('o05', 'melon', 'p06', 36, '경기도 용인시', '19/02/20');
INSERT INTO 주문 VALUES ('o06', 'banana', 'p01', 19, '충청북도 보은군', '19/03/02');
INSERT INTO 주문 VALUES ('o07', 'apple', 'p03', 22, '서울시 영등포구', '19/03/15');
INSERT INTO 주문 VALUES ('o08', 'pear', 'p02', 50, '강원도 춘천시', '19/04/10');
INSERT INTO 주문 VALUES ('o09', 'banana', 'p04', 15, '전라남도 목포시', '19/04/11');
INSERT INTO 주문 VALUES ('o10', 'carrot', 'p03', 20, '경기도 안양시', '19/05/22');
ALTER TABLE 고객 ADD 가입날짜 DATE;
ALTER TABLE 고객 DROP COLUMN 가입날짜;
항목 추가 / 지우기
DROP table 배송업체;
테이블 자체를 지우려면?
SELECT 고객아이디, 고객이름, 등급 FROM 고객;
SELECT 고객아이디, 고객이름, 나이, 등급, 직업, 적립금 FROM 고객;
SELECT * FROM 고객;
SELECT 제조업체 FROM 제품;
SELECT ALL 제조업체 FROM 제품; /*ALL 키워드*/
SELECT DISTINCT 제조업체 FROM 제품;
SELECT 제품명, 단가 AS 가격 FROM 제품;
SELECT 제품명, 단가+500 AS 조정단가 FROM 제품;
SELECT 제품명, 재고량, 단가 FROM 제품 WHERE 제조업체 = "한빛제과";
SELECT 주문고객, 주문제품, 수량, 주문일자 FROM 주문 WHERE 주문고객 = 'apple' AND 수량 >= 15;SELECT 주문제품, 수량, 주문일자, 주문고객 FROM 주문 WHERE 주문고객 = 'apple' AND 수량 >= 15;
SELECT 제품명, 단가, 제조업체 FROM 제품 WHERE 단가 BETWEEN 2000 AND 3000;
SELECT 제품명, 단가, 제조업체 FROM 제품 WHERE 단가 >= 2000 AND 단가 <= 3000;
SELECT 고객이름, 나이, 등급, 적립금 FROM 고객 WHERE 고객이름 LIKE "김%";
SELECT 고객아이디, 고객이름, 등급 FROM 고객 WHERE 고객아이디 LIKE "_____";
SELECT 고객이름 FROM 고객 WHERE 나이 IS NULL;
SELECT 고객이름 FROM 고객 WHERE 나이 IS NOT NULL;
SELECT 고객이름, 등급, 나이 FROM 고객 ORDER BY 나이 DESC;
SELECT 주문고객, 주문제품, 수량, 주문일자 FROM 주문 WHERE 수량 >= 10 ORDER BY 주문제품 ASC, 수량 DESC;
SELECT AVG(단가) FROM 제품;
SELECT SUM(재고량) FROM 제품 WHERE 제조업체 = "한빛제과";SELECT COUNT(고객아이디) AS 고객수 FROM 고객;
SELECT COUNT(DISTINCT 제조업체) AS '제조업체 수' FROM 제품;
SELECT SUM(수량) AS 총주문수량 FROM 주문;
SELECT 제조업체, COUNT(*) AS 제품수, MAX(단가) AS 최고가 FROM 제품 GROUP BY 제조업체;
SELECT 제조업체, COUNT(*) AS 개수, MAX(단가) AS 최고가 FROM 제품 GROUP BY 제조업체 HAVING COUNT(*) >= 3;
/* HAVING */
SELECT 등급, COUNT(*) AS 제품수, AVG(적립금) AS 평균적립금 FROM 고객 GROUP BY 등급 HAVING AVG(적립금) >= 1000;
SELECT 제품.제품명 FROM 제품, 주문 WHERE 주문.주문고객="banana" AND 제품.제품번호 = 주문.주문제품;
SELECT 주문.주문제품, 주문.주문일자 FROM 고객, 주문 WHERE 고객.나이 >= 30 AND 고객.고객아이디 = 주문.주문고객;
SELECT 주문번호, 주문고객, 수량 FROM 고객, 주문 WHERE 수량 IN(36, 45, 55);
SELECT 주문번호, 주문고객, 수량 FROM 주문 WHERE 수량 BETWEEN 20 AND 45;
SELECT 제품.제품명 FROM 고객, 제품, 주문 WHERE 고객.고객이름 = "고명석" AND 고객.고객아이디 = 주문.주문고객 AND 제품.제품번호 = 주문.주문제품;