06.07 example data Import
sql있는 주소창에서 'cmd'입력 후
C:\Users\admin\Downloads\test_db-master\test_db-master>mysql -u root -p
Enter password: *****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 8.0.33 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> source employees.sql
-> database에 적용 완료
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| employees |
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sqldb |
| sys |
| testdb |
| world |
+--------------------+
9 rows in set (0.00 sec)
-- 한줄 주석
/*
DATABASE TABLE에 정의 데이터를 조작하는 조작어(DML)
SELECT 검색 질의로 따로 빼기도 함.
- 테이블에 저장된 실제 데이터를 조작(추가, 수정, 삭제)
INSERT UPDATE DELETE
*/
-- 특정 테이블의 정보를 검색-추출하는 명령어 SELECT
use employees; -- 사원 정보를 저장하는 DataBase
show tables;
-- titles table의 모든 속성과 행 정보를 검색
-- SELECT 검색할 속성 FROM table명;
SELECT * FROM titles;
DESC titles;
-- 사원 테이블의 구조
DESC employees;
SELECT first_name, last_name FROM employees;
-- emp 테이블의 회원 정보들 중에 사원 이름, 성별을 검색
SELECT first_name AS '이름',
last_name AS '성',
gender AS '성별'
FROM employees;
use sqlDB;
show tables;
DESC userTbl;
-- userTbl에 등록된 회원 이름 검색
SELECT name FROM userTbl;
-- userTbl 테이블에서
-- mDate, name userId 열을 순서대로 검색
DESC userTbl;
SELECT mDate, name, userID FROM userTbl;
-- sqlDB의 userTbl 테이블에서
-- userID는 아이디로 name은 이름으로 mDate는 가입일로
-- 별칭을 사용하여 검색
SELECT userID AS'아이디',
name '이름',
mDate AS'가입일'
FROM userTbl;
-- table에 행의 값을 삽입하는 INSERT 문
-- 행의 데이터를 추가하는 것이기 때문에 필수 값 전체가 필요
SELECT * FROM buytbl;
-- user tbl에 회원 정보 추가
-- INSERT INTO 'table명' VALUES(속성값, 속성값...);
INSERT INTO usertbl
VALUES('HGD','홍길동',1000,'한양','010','00000000',176,20130505);
DESC usertbl;
SELECT * FROM userTbl;
DESC buyTbl;
-- 홍길동 구매 목록 추가
INSERT INTO buyTbl
VALUES(null,'HGD','책',null,30,5);
SELECT * FROM buyTbl;
INSERT INTO buyTbl(userid,prodName,price,amount)
VALUES('HGD','책',50,1);
-- num field의 기본 키 값이 중복됨으로 오류 발생
INSERT INTO buyTbl
VALUES(1,"KBS","티셔츠",'의류',30,1);
-- 키가 중복이 되면 현재 INSERT 문을 실행하지 않고 무시한다.
INSERT IGNORE INTO buyTbl
VALUES(1,"KBS","티셔츠",'의류',30,1);
-- '1','KBS','운동화',NULL,'30','2'
SELECT * FROM buyTbl;
-- REPLACE INTO
-- 키가 중복이 되면 기존데이터를 삭제하고 새로운 데이터 추가
-- 키가 중복되지 않으면 새로운 행을 추가
REPLACE INTO buytbl VALUES(1,"KBS","티셔츠",'의류',30,1);
-- 다중행 삽입
INSERT INTO buyTbl(userId,prodName,groupName,price,amount)
VALUES
('KBS','티셔츠','의류',30,3),
('KBS','모니터','전자',150,1);
/****************************************
테이블의 특정 행의 속성(열) 값을 변경(수정)
UPDATE 문
UPDATE `table명` SET `수정할 속성이름` = 변경할 값
WHERE `수정할 행 구분 열이름` = 수정할 행 구분 열 값
*/
-- buyTbl 테이블의 모니터 가격을 250으로 변경
SELECT * FROM buyTbl;
UPDATE buyTbl SET price = 250 WHERE prodName = '모니터';
/**
TCL(Transaction Control Language)
- 논리적인 작업 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(Transaction) 별로 제어하는 명령어
- commit; 현 Session에서 실행된 작업을 적용
- rollback; 현 session에서 실행된 작업을 이전 commit; 시점으로 돌림
*/
-- @변수이름 : 변수
-- @@시스템변수이름
SELECT @@autocommit;
-- 자동 commit 설정 정보
-- 1 : autocommit;
-- 0 : 자동으로 commit 설정 해제;
-- (:= 대입연산 확실하게 표시해주는거)
-- auto commit 설정 변경
SET @@autocommit := 0; -- 0 or 1
-- test용 sample table 생성
-- table 복제
-- buytbl table의 열 속성과 행정보를 이용하여 새로운 테이블 생성
CREATE TABLE buytbl2(
SELECT * FROM buytbl
);
SELECT * FROM buytbl2;
-- buytbl2 테이블의 모든 행의 price 속성값을 0으로 수정
UPDATE buytbl2 SET price = 0;
rollback;
-- 기존에 상품 가격을 전부 50씩 증가
UPDATE buytbl2 SET price = price + 50;
SELECT * FROM buytbl2;
commit; -- transaction 종료
-- 새로운 작업 transaction 생성
rollback;
UPDATE buytbl2 SET price = price -100;
SELECT * FROM buytbl2;
-- DML(SELECT INSERT UPDATE DELETE)만 transaction 제어가능. DML를 제외한
-- DDL DCL은 단일 transaction 수행
-- 명령문이 수행되면 자동으로 commit; 되므로
-- transaction 작업 수행 시 조심
CREATE TABLE buytbl3(
SELECT * FROM buytbl
);
SELECT * FROM buytbl3;
-- buytbl3 의 상품 중 '청바지'의 가격을 60으로 변경하고 판매개수를 5로 변경
UPDATE buytbl3 SET
price = 60,
amount = 5
WHERE prodname = '청바지';
SELECT * FROM buytbl3;
/*********************************************
table에 삽입되어 있는 행의 정보를 삭제하는 DELETE문
DELETE FROM `삭제작업을 진행할 table`
OPTINAL
WHERE 삭제할 행의 칼럼 = `비교할 값`
*/
DELETE FROM buytbl2;
SELECT * FROM buytbl2;
rollback;
DELETE FROM buytbl2 WHERE userID = 'BBK';
/**************
저장된 데이터(table)에서 필요한 정보를 추출
SELECT ~ FROM ~ WHERE 조건절
*/
-- usertbl table에 저장된 사용자 중에 이름이 '김경호'인 사용자의 정보 검색
SELECT * FROM userTbl WHERE name = '김경호';
-- userTbl에서 1970년 이후에 출생하고 키가 182 이상인 사람의 아이디와 이름을 검색
SELECT userID, name, birthyear, height FROM userTbl
-- WHERE birthYear >= 1970 && height >= 182;
WHERE birthYear >= 1970 AND height >= 182;
-- userTbl 에서 1970년 이후에 출생했거나 키가 182 이상인 사람의 정보를 검색
SELECT userID, name, birthyear, height FROM userTbl
-- WHERE birthYear >= 1970 || height >= 182;
WHERE birthYear >= 1970 OR height >= 182;
-- userTbl에서 키가 180이상, 183 이하인 사람을 검색
SELECT * FROM userTbl
WHERE height >= 180 && height<= 183;
-- BTWEEN AND 절 - 범위 검색 (값 포함)
SELECT * FROM userTbl WHERE height BETWEEN 180 AND 183;
-- userTbl에서 주소가 '경남'이거나 '전남'이거나 '경북'인 사람을 검색
SELECT * FROM userTbl
-- WHERE addr = '경남' || addr = '전남' OR addr = '경북';
WHERE addr IN('경남','전남','경북');
-- userTbl 에서 1970년대 생의 정보를 검색
SELECT * FROM userTbl
WHERE birthYear between 1970 AND 1979;
-- WHERE LIKE 절 : 특정 글자 또는 문자가 포함되어 있는 패턴을 만족하는 다수의 정보를 검색
-- % : 글자 수 상관없이(없거나 많거나)
-- _ : 한개의 문자(한자리)
SELECT * FROM userTbl WHERE birthYear LIKE '197_';
-- userTbl에서 성이 '김'씨인 모든 사용자 정보 검색
SELECT * FROM userTbl WHERE name LIKE'김__';
SELECT * FROM userTbl WHERE name LIKE'김%';
-- userTbl 테이블에서 등록된 사용자 이름에 '수'가 포함이 된 사용자 검색
SELECT * FROM userTbl WHERE name LIKE '%수%';
-- userID가 'HGD'인 사용자의 회원 가입일
SELECT mdate FROM userTbl WHERE userID = 'HGD';
-- 2013년 이전에 가입한 사용자
SELECT * FROM userTbl WHERE mdate < '2013-01-01';
-- Null 값 비교
-- userTbl에서 mobile1 핸드폰 번호가 없는 사용자 목록 검색
SELECT * FROM userTbl WHERE mobile1 = NULL; -- Null 값을 비교할 수 없으므로 사용 불가
SELECT * FROM userTbl WHERE mobile1 IS NULL;
UPDATE userTbl SET mobile1 = '' WHERE userID = 'SSK';
SELECT * FROM userTbl WHERE mobile1 = '';
-- 거주지(주소:addr)가 서울이 아닌 사람 검색
SELECT * FROM userTbl WHERE addr != '서울';
SELECT * FROM userTbl WHERE NOT addr = '서울';
SELECT * FROM userTbl WHERE addr <> '서울'; -- NOT == <> == !=
-- 전화번호 시작(mobile1) 이 016, 018, 019인 사람 검색
SELECT * FROM userTbl WHERE mobile1 IN('016','018','019');
-- 전화번호 시작(mobile1) 이 016, 018, 019 아닌 사람 검색
SELECT * FROM userTbl WHERE NOT mobile1 IN('016','018','019');
-- 정렬, 검색된 행의 정보를 정렬해주는 명령어
-- ORDER BY 절
-- ORDER BY 검색 기준 열 이름 ASC / DESC;
-- 거주지 지역 별로 정렬
SELECT * FROM userTbl ORDER BY addr ASC;
SELECT * FROM userTbl ORDER BY addr DESC;
-- 가장 최근에 가입한 순서대로 정렬
SELECT * FROM userTbl ORDER BY mdate DESC;
-- 먼저 가입한 순서대로 정렬 - 정렬은 기본이 ASC(오름차순), 생략하면 ASC
SELECT * FROM userTbl ORDER BY mdate;
-- 거주지역 순으로 정렬하고 동일 지역이면 나이순으로 정렬
SELECT * FROM userTbl
ORDER BY addr , birthYear DESC;
-- 키가 175가 넘는 사용자의 거주지역이 어디인지 검색
-- DISTINCT 검색 결과 내에 중복 데이터 제거
-- 중복 제거 시 검색 결과에 포함된 column을 제외한 속성을 사용할 수 없음
SELECT DISTINCT addr, height FROM userTbl
WHERE height >= 175 ORDER BY height;
-- 검색 결과내에서 제공되는 결과의 개수를 제한하는 LIMIT 문
-- LIMIT 절은 MySQL에서만 제공되는 강력한 기능
-- userTbl에서 가장 최근에 가입한 사용자 5명의 정보를 검색
SELECT * FROM userTbl ORDER BY mDate DESC LIMIT 5;
SELECT * FROM userTbl ORDER BY mDate DESC;
SELECT * FROM userTbl
ORDER BY mDate DESC LIMIT 5, 5;
-- 검색 결과 내에 정렬된 목록에서 0에서 부터 시작하는 인덱스 번호를 기준으로
-- LIMIT 시작인덱스값, 개수;
SELECT * FROM userTbl
ORDER BY mDate DESC LIMIT 5 OFFSET 6;
-- LIMIT 개수 OFFSET 시작인덱스;