SQL 실습_db

데이터베이스 생성

MySQL에 데이터 베이스 생성

create database 생성할 데이터베이스 이름 옵션(인코딩 방식)

데이터 베이스 조회

SELECT * FROM tb_user;

MySQL에 사용자 아이디 생성

MySQL에 사용자 아이디 삭제

데이터베이스 권한

MySQL에 생성된 DB에 사용자 권한 부여

변경된 내용을 메모리에 반영

MySQL에 생성된 db에 사용자 권한 회수

데이터베이스 생성

테이블 생성

MySQL 에 생성된 DB 선택

USE ksmart42db;

선택된 DB에 테이블 생성

CREATE TABLE tb_user

( u_id VARCHAR(100) NOT NULL PRIMARY KEY COMMENT '사용자 아이디'

,u_pw VARCHAR(100) NOT NULL COMMENT '사용자 비밀번호'

,u_name VARCHAR(100) NOT NULL COMMENT '사용자 이름'

,u_birth DATE COMMENT '사용자 생년월일'

,u_add VARCHAR(100) NOT NULL COMMENT '사용자 주소'

,u_mobile1 VARCHAR(100) COMMENT '사용자 연락처1'

,u_mobile2 VARCHAR(100) COMMENT '사용자 연락처2'

,u_date DATE COMMENT '등록일자'

);

​

컬럼명 변경

ALTER TABLE tb_user CHANGE COLUMN u_reg_date u_date DATE;

ALTER TABLE 테이블명 CHANGE 기존 컬럼명 변경할 컬럼명 컬럼타입;

컬럼 순서변경

ALTER TABLE tb_user MODIFY u_mobile2 VARCHAR(100) AFTER u_mobile1;

ALTER TABLE 테이블명 MODIFY 순서 변경할 컬럼명 컬럼타입 AFTER 앞에 오는 컬럼명;

컬럼 디폴트값 변경

ALTER TABLE tb_user ALTER COLUMN u_add SET DEFAULT NULL;

ALTER TABLE 테이블명 ALTER COLUMN 변경할 컬럼명 SET DEFAULT 디폴트 값;

컬럼 타입변경

ALTER TABLE tb_user MODIFY u_add VARCHAR(150);

ALTER TABLE 테이블명 MODIFY 컬럼명 변경할 컬럼타입;

컬럼 추가

ALTER TABLE tb_user ADD COLUMN u_email VARCHAR(100) AFTER u_add;

ALTER TABLE 테이블명 ADD COLUMN 추가할 컬럼명 컬럼타입 DEFAULT 디폴트 값 컬럼위치;

컬럼 삭제

ALTER TABLE tb_user DROP COLUMN u_email;

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

​

테이블 데이터 삽입

INSERT INTO tb_user

( u_id, u_pw, u_name, u_birth, u_add, u_mobile1, u_mobile2, u_date

)VALUES(

'id001','pw001','홍01',NOW(),'덕진동','010','00010001',NOW()

);

 

테이블 데이터 조회

SELECT * FROM tb_user;

 

테이블 데이터 삽입

 

테이블의 데이터 조회

SELECT

u_id AS '아이디'

,u_pw AS '비밀번호'

,u_name AS '이름'

,u_birth AS '생년월일'

,u_add AS '주소'

,u_mobile1 AS '연락처1'

,u_mobile2 AS '연락처2'

,u_date AS '등록날짜'

FROM

tb_user;

SELECT

u_id AS '아이디'

,u_pw AS '비밀번호'

,u_name AS '이름'

,u_birth AS '생년월일'

,u_add AS '주소'

,u_mobile1 AS '연락처1'

,u_mobile2 AS '연락처2'

,u_date AS '등록날짜'

FROM

tb_user

WHERE

u_id='id010';

 

테이블의 데이터 변경

UPDATE tb_user

SET

u_pw='pw011'

,u_name='홍길동'

,u_birth=NOW()

,u_add='인후동'

,u_mobile1='010'

,u_mobile2='00110011'

,u_date=NOW()

WHERE u_id='id010';

 

테이블 데이터 삭제

테이블의 데이터 삭제

DELETE FROM tb_user

WHERE

u_id='id010';

테이블의 모든 데이터 삭제

DELETE FROM tb_user;

TRUNCATE TABLE tb_user; <- DDL

 

테이블 데이터 여러 행 삽입

INSERT INTO tb_user(u_id, u_pw, u_name, u_birth, u_add, u_mobile1, u_mobile2, u_date)

VALUES

('id001', 'pw001', '홍01', '2020-03-10', '덕진동', '010', '00010001', '2020-03-10'),

('id002', 'pw002', '홍02', '2020-03-10', '호성동', '010', '00020002', '2020-03-10'),

('id003', 'pw003', '홍03', '2020-03-10', '금암동', '010', '00030003', '2020-03-10'),

('id004', 'pw004', '홍04', '2020-03-10', '조촌동', '010', '00040004', '2020-03-10'),

('id005', 'pw005', '홍05', '2020-03-10', '서서학동', '010', '00050005', '2020-03-10'),

('id006', 'pw006', '홍06', '2020-03-10', '금암동', '010', '00060006', '2020-03-10'),

('id007', 'pw007', '홍07', '2020-03-10', '송천동', '010', '00070007', '2020-03-10'),

('id008', 'pw008', '홍08', '2020-03-10', '서신동', '010', '00080008', '2020-03-10'),

('id009', 'pw009', '홍09', '2020-03-10', '효자동', '010', '00090009', '2020-03-10'),

('id010', 'pw010', '홍10', '2020-03-10', '삼천동', '010', '00100010', '2020-03-10');

 

테이블 복제

특정 테이블의 특정 컬럼만 복제

CREATE TABLE tb_user_backup

(SELECT

b.u_id

,b.u_pw

,b.u_name

FROM

tb_user AS b);

특정 테이블 전체 복제

CREATE TABLE tb_user_backup_all

(SELECT

*

FROM

tb_user);

 

테이블 데이터 삽입 키 중복 시 데이터 수정

INSERT INTO tb_user(u_id, u_pw, u_name, u_birth, u_add, u_mobile1, u_mobile2, u_date)

VALUES

('id001', 'pw001', '홍01', '2020-01-10', '덕진동', '010', '00010001', '2020-03-10')

ON DUPLICATE KEY UPDATE

u_birth = '2019-01-10’;

 

테이블 데이터 삽입 키 중복 시 데이터 무시

INSERT INTO tb_user(u_id, u_pw, u_name, u_birth, u_add, u_mobile1, u_mobile2, u_date)

VALUES('id010','pw010','홍10','2020-10-10','덕진동','010','00010001','2020-03-10');

INSERT INTO tb_user(u_id, u_pw, u_name, u_birth, u_add, u_mobile1, u_mobile2, u_date)

VALUES('id011','pw011','홍11','2020-11-10','덕진동','010','00010001','2020-03-10');

INSERT IGNORE INTO tb_user (u_id, u_pw, u_name, u_birth, u_add, u_mobile1, u_mobile2, u_date)

VALUES('id010','pw010','홍10','2020-10-10','덕진동','010','00010001','2020-03-10');

INSERT IGNORE INTO tb_user (u_id, u_pw, u_name, u_birth, u_add, u_mobile1, u_mobile2, u_date)

VALUES('id011','pw011','홍11','2020-11-10','덕진동','010','00010001','2020-03-10');

 

테이블 데이터 조회 정렬

테이블의 데이터 조회

SELECT

u_id AS '아이디’

,u_pw AS '비밀번호’

,u_name AS '이름’

,u_birth AS '생년월일’

,u_add AS '주소’

,u_mobile1 AS '연락처1’

,u_mobile2 AS '연락처2’

,u_date AS '등록날짜'

FROM

tb_user

ORDER BY u_id DESC;​

 

테이블 데이터 출력 개수 제한

테이블의 데이터 조회

SELECT

u_id AS '아이디’

,u_pw AS '비밀번호’

,u_name AS '이름’

,u_birth AS '생년월일’

,u_add AS '주소’

,u_mobile1 AS '연락처1’

,u_mobile2 AS '연락처2’

,u_date AS '등록날짜'

FROM

tb_user

ORDER BY u_id DESC

LIMIT 5;

 

테이블 중복 데이터 하나만 조회

테이블의 데이터 조회

SELECT

DISTINCT u_add AS '주소'

FROM

tb_user;

테이블의 데이터 한글 정렬

SELECT

DISTINCT u_add AS '주소’

FROM

tb_user

ORDER BY CONVERT(u_add USING BINARY);

 

사용자 지정 정렬

SELECT

DISTINCT u_add AS '주소'

FROM

tb_user

ORDER BY FIELD(u_add,'금암동','덕진동','송천동','호성동','서신동','조촌동','서서학동','효자동','삼천동');

 

테이블 데이터 그룹 출력

 

 

테이블의 데이터 조회

SELECT

u_add AS '주소’

,COUNT(u_id) AS '고객분포수'

FROM

tb_user

GROUP BY u_add;

테이블 데이터 그룹 출력 조건식

테이블의 데이터 조회

SELECT

u_add AS '주소’

,COUNT(u_id) AS '고객분포수'

FROM

tb_user

GROUP BY u_add

HAVING COUNT(u_id) > 1;