본문 바로가기
개발일지/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 제품.제품번호 = 주문.주문제품;

'개발일지 > SQL' 카테고리의 다른 글

[Oracle] 기본 용어 및 상식  (2) 2021.11.17
SQL 복습 (옛날거)  (0) 2021.10.25
테이블 한 번에 등록하기  (1) 2021.10.11
데이터베이스, 스키마 이름 바꾸기  (0) 2021.10.11
MySQL - 첫번째 수업 (기초)  (0) 2021.10.05

'개발일지/SQL' Related Articles

티스토리툴바