Programming/데이터베이스 관리(DBMS) \ 관리자(DBA)

mysql(데이터 베이스)/ SQL(query 쿼리문) 기본k

esoog Polaris 2023. 6. 1. 23:10

# 기본적으로 쿼리문은

 

워크벤치-file에서 sql script open 혹은 save가 가능함.

예약어 대,소문자는 워크벤치-edit-preferences-query edit-use uppercase로 설정 가능.

리눅스는 소문자로 해야함.(처음부터 그럼 소문자로 익히는게 나으려나)


 

 

 

# SQL 기본

 

-- SQL 주석 처리
USE employees;
-- 사용할 데이터베이스 선택
-- 워크벤치 왼쪽 schema 태그에서 이름 드래그 가능
-- 기본 데이터베이스 참조
SHOW DATABASES;
-- 데이터베이스 목록 보여줘
SHOW TABLES;
-- 테이블 목록 보여줘
SHOW TABLE STATUS;
-- 현재 데이터베이스에 어떤 테이블들 있는지 상태 출력
DESCRIBE employees;
-- 또는 DESC employees; : 테이블의 정보
-- 데이터베이스 초기화 및 생성.
DROP DATABASE IF EXISTS sqldb;
-- 만약 sqldb가 존재하면 우선 삭제.
-- 단, 외래키 제약의 기준 테이블은 외래키 삭제후 삭제 가능.
CREATE DATABASE sqldb;
--데이터베이스(데이터) 생성
--데이터 베이스(데이터)> 테이블(데이터 테이블) > 레코드(데이터 행)> 속성(데이터 값)
-- 기본키, 외래키 사용
USE sqldb;
-- 테이블 생성 및 열 속성 지정
CREATE TABLE usertbl
( userID CHAR(8) NOT NULL PRIMARY KEY,
-- 사용자 아이디(PK로 사용). 최대 8자
-- CHAR(정적)는 내부적으로도 최대글자/ VARCHAR(동적)는 내부적으로는 입력값글자만
name VARCHAR(10) NOT NULL,
-- 이름
birthYear INT NOT NULL,
-- 출생년도
addr CHAR(2) NOT NULL,
-- 각 개체들의 이름은 영문으로 사용
-- 지역(경기,서울,경남 식으로 2글자만입력)
mobile1 CHAR(3),
-- 휴대폰의 국번(011, 016, 017, 018, 019, 010 등)
mobile2 CHAR(8),
-- 휴대폰의 나머지 전화번호(하이픈제외)
height SMALLINT,
-- 키, 작은 정수
mDate DATE
-- 회원 가입일
);
CREATE TABLE buytbl
( num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
-- 순번(PK), INT AUTO_INCREMENT 자동 증가 수
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)
-- usertbl(userID)값을 참조하여 현재 테이블의 (userID)값으로 함.
);
INSERT INTO usertbl VALUES('KBS', '김범수', 1979, '경남', '011', '2222222', 173, '2012-4-4');
INSERT INTO buytbl VALUES(NULL, 'KBS', '운동화', NULL , 30, 2);
INSERT INTO buytbl VALUES(NULL, 'KBS', '노트북', '전자', 1000, 1);
-- SELECT문 사용 순서
SELECT -- 열 이름
FROM -- 테이블 이름
WHERE -- 조건
-- 이까지 기본 SELECT문
GROUP BY --
HAVING --
ORDER BY -- 정렬 순서
SELECT * FROM employees;
-- SELECT 열이름 FROM 테이블 이름;
SELECT * FROM employees.titles;
-- use를 사용 안 했다면,
-- SELECT 열이름 FROM 데이터베이스.테이블이름;
SELECT first_name, last_name, gender FROM employees;
-- 열 지정 여러개 선택 가능.
-- SELECT문 WHERE 조건절 상세
USE sqldb;
SELECT * FROM usertbl WHERE name = '김경호';
SELECT userID, Name FROM usertbl WHERE birthYear >= 1970 AND height >= 182;
-- userID, Name열을 추출하는데, 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;
-- BETWEEN A AND B; 사이 연속적인 값에서 사용 가능
SELECT name, addr FROM usertbl WHERE addr='경남' OR addr='전남' OR addr='경북';
SELECT name, addr FROM usertbl WHERE addr IN ('경남','전남','경북');
-- IN ( ); 중에 하나 값으로 이산적인 값에서 사용 가능
SELECT name, height FROM usertbl WHERE name LIKE '김%';
-- 문자열 검색. 상세 조건으로 LIKE '%':모든 타입(mysql에서 모든을 의미), '_':문자, '#':숫자 로 가능
SELECT name, height FROM usertbl WHERE name LIKE '_종신';
-- 앞에 조건타입이 들어가면 별로 좋지 않음...
-- 서브쿼리문(조건 안에 (조건) 입력 가능)
SELECT name, height FROM usertbl
WHERE height > (SELECT height FROM usertbl WHERE Name = '김경호');
-- 김경호 height 보다 큰 height의 name, height
-- * 서브 조건 값이 여러 명일 경우 오류발생함.
-- 그래서 아래처럼 사용.
SELECT name, height FROM usertbl
WHERE height > ANY (SELECT height FROM usertbl WHERE addr = '경남');
-- ANY (아무거나 해당되는), =ANY와 IN은 같음.
SELECT name, height FROM usertbl
WHERE height > ALL (SELECT height FROM usertbl WHERE addr = '경남');
-- ALL (모든 사람을 조건으로)
SELECT name, mDate FROM usertbl ORDER BY mDate;
-- ORDER BY mDate 오름차순(디폴트), 순서정렬은 성능에 악영향...
SELECT name, mDate FROM usertbl ORDER BY mDate DESC;
-- ORDER BY mDate DESC 내림차순
SELECT name, height FROM usertbl ORDER BY height DESC, name ASC;
-- height 내림차순으로 나열하고, 같은 값이 있으면 뒤에 조건 name 오름차순으로 나열
-- 특수문
SELECT DISTINCT addr FROM usertbl;
-- SELECT DISTINCT 중복된 값 제거 후 출력
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 과 동일(0~5행까지)
CREATE TABLE buytbl2 (SELECT * FROM buytbl);
-- 테이블 복사 방법 CREATE문 (참조 식별자);
CREATE TABLE buytbl3 (SELECT userID, prodName FROM buytbl);
-- 다만, 이렇게 쿼리문으로 복사하면, 기본키나 외래키 등은 복사x
-- GROUP BY
SELECT userID, SUM(amount) FROM buytbl GROUP BY userID;
-- 그룹으로 묶어서 amount를 SUM()합계해서 출력
SELECT userID AS '사용자 아이디', SUM(amount) AS '총 구매 개수'
FROM buytbl GROUP BY userID;
-- AS 는 별칭으로 출력 가능
-- 집계함수 자체가 GROUP BY 함수와 같이 쓰는 걸로 인식하면 된다.
-- SUM() AVG() MAX() MIN() ...
USE sqldb;
SELECT AVG(amount) AS '평균 구매 개수' FROM buytbl ;
SELECT userID, AVG(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) ;
SELECT COUNT(*) FROM usertbl;
--이렇게 하면 테이블의 모든 행개수 빈칸이 있어도 모두 포함해서 나온다
SELECT COUNT(mobile1) AS '휴대폰이 있는 사용자' FROM usertbl;
-- 그래서 이렇게 컬럼을 지정하면 null 제거하고 있는 값 개수만 나온다.
SELECT userID AS '사용자', SUM(price*amount) AS '총구매액'
FROM buytbl
GROUP BY userID
HAVING SUM(price*amount) > 1000 ;
ORDER BY SUM(price*amount) ;
-- 집계함수는 WHERE절에서는 사용 불가하므로. 조건 절에서 집계함수를 사용하기 위해서는
-- HAVING 절로 사용. 꼭 GROUP BY 뒤에 나와야 한다. 그룹화된 내용에서 조건으로 사용하니까.
SELECT num, groupName, SUM(price * amount) AS '비용'
FROM buytbl
GROUP BY groupName, num
WITH ROLLUP;
-- WITH ROLLUP;은 GROUP BY절과 함께, 그룹 내 소합계 및 총합계를 내기 위함
-- *SQL분류
-- DDL(데이타 정의 언어) : CREATE, DROP, ALTER - 트랜잭션x, 바로 mysql적용.
-- DML(데이티 조작 언어) : SELECT, INSERT, DELETE, UPDATE - 트랜잭션o(임시 적용)
-- DCL(데이터 제어 언어) : GRANT, REVOKE, DENY - 사용자 권한리관리
-- INSERT 문
-- INSERT INTO 열 VALUES(값);
USE sqldb;
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);
USE sqldb;
CREATE TABLE testTbl2
(id int AUTO_INCREMENT PRIMARY KEY,
-- int AUTO_INCREMENT: 자동 증가 1~. 보통 프라이머리 키로 사용. id값
userName char(3),
age int );
ALTER TABLE testTbl2 AUTO_INCREMENT=100;
-- ALTER TABLE testTbl2 AUTO_INCREMENT=100; 현재 마지막 행 이후의 행부터 순서값을 지정해서 사용할 수 있다.
USE sqldb;
CREATE TABLE testTbl3
(id int AUTO_INCREMENT PRIMARY KEY,
userName char(3),
age int );
ALTER TABLE testTbl3 AUTO_INCREMENT=1000;
SET @@auto_increment_increment=3;
-- SET @@auto_increment_increment=3; 자동 증가값을 정할 수 있다.
USE sqldb;
CREATE TABLE testTbl4 (id int, Fname varchar(50), Lname varchar(50));
INSERT INTO testTbl4
SELECT emp_no, first_name, last_name
FROM employees.employees ;
-- 다른 데이터 복사 입력(SELECT해서)
-- UPDATE 테이블 SET 열='값' WHERE 조건
UPDATE testTbl4
SET Lname = '없음'
-- 기존 열의 어떤 값을 '없음'으로 바꿔라
WHERE Fname = 'Kyoichi';
-- 조건인 ~인 경우에 대해
UPDATE buytbl SET price = price * 1.5 ;
-- DELETE FROM 테이블 WHERE 조건
USE sqldb;
DELETE FROM testTbl4 WHERE Fname = 'Aamer';
DELETE FROM testTbl4 WHERE Fname = 'Aamer' LIMIT 5;
DELETE FROM bigTbl1;
-- DELETE FROM 테이블: 한 행씩 삭제(다만, WHERE 조건 없으면 전체 테이블 삭제)
DROP TABLE bigTbl2;
-- DROP TABLE 테이블: 테이블 자체 삭제
TRUNCATE TABLE bigTbl3;
-- TRUNCATE TABLE 테이블: 테이블의 구조는 남아있지만, 딜리트와 같이 삭제
-- 조건부 데이타 입력
USE sqldb;
CREATE TABLE memberTBL (SELECT userID, name, addr FROM usertbl LIMIT 3);
-- 3건만 가져옴
ALTER TABLE memberTBL
ADD CONSTRAINT pk_memberTBL PRIMARY KEY (userID);
-- PK를 지정함
SELECT * FROM memberTBL;
-- INSERT IGNORE INTO ~ 삽입하는데, 오류나도 무시하고 넘어가라
INSERT IGNORE INTO memberTBL VALUES('BBK' , '비비코', '미국');
INSERT INTO memberTBL VALUES('BBK' , '비비코', '미국')
ON DUPLICATE KEY UPDATE name='비비코', addr='미국';
-- ON DUPLICATE KEY UPDATE 중복되면 업데이트 해라. 아니면 그냥 입력
USE sqldb;
SELECT userid AS '사용자', SUM(price*amount) AS '총구매액'
FROM buyTBL GROUP BY userid;
--검색 결과 자체를 테이블로 사용
WITH abc(userid, total)
--abc라는 이름으로(열이름)
AS
-- 원본
(SELECT userid, SUM(price*amount)
FROM buyTBL GROUP BY userid )
WITH cte_usertbl(addr, maxHeight)
AS
( SELECT addr, MAX(height) FROM usertbl GROUP BY addr)
SELECT AVG(maxHeight*1.0) AS '각 지역별 최고키의 평균' FROM cte_usertbl;
view raw mysql.sql hosted with ❤ by GitHub

 

 

 

 

# 데이터를 일반 파일로 추출(csv, xml, ...)

: 데이터 조회후 그리드 상단 가운데, export/import 

 

반응형
저작자표시 변경금지 (새창열림)