데이터베이스

SQL workbench

방법 1. SQL문에서 코드로 데이터베이스 만들기 (SQL 코드)
ex) CREATE SCHEMA madang ;

방법 2. 데이터베이스 이름 작성 -> apply -> 코드 작성 (sql문 코드작성)

CREATE SCHEMA `madang` ;
	'bookid' INT NOT NULL,
	'bookname' varchar(40) DEFAULT NULL,
	'publisher' varchar(40) DEFAULT NULL,
	'price' INT DEFAULT NULL,
	PRIMARY KEY ('bookid')
);

(데이터 입력)

SELECT * FROM madang.book;
INSERT INTO `madang`.`book` (`bookid`, `bookname`, `publisher`, `price`) VALUES ('1', '축구의 역사', '굿스포츠', '7000');
INSERT INTO `madang`.`book` (`bookid`, `bookname`, `publisher`, `price`) VALUES ('2', '축구아는 여자', '나무수', '13000');
INSERT INTO `madang`.`book` (`bookid`, `bookname`, `publisher`, `price`) VALUES ('3', '축구의 이해', '대한미디어 ', '22000');
INSERT INTO `madang`.`book` (`bookid`, `bookname`, `publisher`, `price`) VALUES ('4', '골프 바이블', '대한미디어', '35000');
INSERT INTO `madang`.`book` (`bookid`, `bookname`, `publisher`, `price`) VALUES ('5', '피겨 교본', '굿스포츠', '8000');
INSERT INTO `madang`.`book` (`bookid`, `bookname`, `publisher`, `price`) VALUES ('6', '역도 단계별기술', '굿스포츠', '6000');
INSERT INTO `madang`.`book` (`bookid`, `bookname`, `publisher`, `price`) VALUES ('7', '야구의 추억', '이상미디어', '20000');
INSERT INTO `madang`.`book` (`bookid`, `bookname`, `publisher`, `price`) VALUES ('8', '야구를 부탁해', '이상미디어', '13000');
INSERT INTO `madang`.`book` (`bookid`, `bookname`, `publisher`, `price`) VALUES ('9', '올림픽 이야기', '삼성당', '7500');
INSERT INTO `madang`.`book` (`bookid`, `bookname`, `publisher`, `price`) VALUES ('10', 'Olympic Champions', 'Pearson', '13000');

(데이터 값 업데이트)

UPDATE `madang`.`book` SET `publisher` = '대한미디어' WHERE (`bookid` = '3');

SELECT bookname, price FROM book;

SELECT * FROM book;

SELECT publisher FROM book;

SELECT DISTINCT publisher FROM book; -- 거래 실적이 있는 출판사만 제공UPDATE `madang`.`book`

-- 집합 

SELECT * FROM book WHERE price < 20000;
SELECT * FROM book WHERE price BETWEEN 10000 AND 20000;
SELECT * FROM book WHERE price >= 10000 <= 20000;
SELECT * FROM book WHERE publisher IN ('굿스포츠', '대한미디어'); -- OR 비슷 
SELECT * FROM book WHERE publisher ='굿스포츠' OR publisher ='대한미디어';
SELECT * FROM book WHERE publisher NOT IN('굿스포츠', '대한미디어');

-- 패턴 

SELECT bookname, publisher FROM book WHERE bookname LIKE '%구%'
SELECT * FROM book WHERE bookname LIKE'_구%';
SELECT * FROM book WHERE bookname LIKE'__의%'; 몇 번째의 글자가 '__의'만 나오게 

-- 복합조건

ORDER BY 순서 -- (desc 역순)
SELECT * FROM book ORDER BY price desc; -- asc 
SELECT * FROM book ORDER BY price, bookname;
SELECT * FROM book ORDER BY price DESC, publisher ASC;

-- 함수

SELECT SUM(saleprice) FROM order;
SELECT SUM(saleprice) FROM

SELECT SUM(saleprice) AS 총매출 FROM orders WHERE custid=2;

SELECT SUM(saleprice) AS Total, AVG(saleprice) AS Average, MIN(saleprice) AS Minimum, MAX(saleprice) AS MAximum FROM orders;

SELECT custid, COUNT(*)AS 도서수량, SUM(saleprice) AS 총액 FROM orders GROUP BY custid;

데이터베이스

SQL workbench