SQL01_sql

---

데이터베이스 생성

MySQL에 데이터 베이스 생성

CREATE DATABASE ksmart42db DEFAULT CHARACTER SET UTF8;

MySQL에 생성된 모든 데이터 베이스 조회

SHOW DATABASES;

MySQL에 사용자 아이디 생성

CREATE USER 'ksmart42id'@'%' IDENTIFIED BY 'ksmart42pw';

MySQL에 사용자 아이디 조회

USE mysql;   SELECT user, host FROM USER;

MySQL에 사용자 아이디 삭제

DROP USER ksmart42id@'%';

---

데이터베이스 권한

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

GRANT ALL PRIVILEGES ON ksmart42db.* TO 'ksmart42id'@'%';

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

FLUSH PRIVILEGES;

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

REVOKE ALL ON ksmart42db.* FROM ksmart42id;

---

테이블 생성

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

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

---

테이블 복제 

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

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’;

테이

---

테이블 데이터 조회 정렬 – ORDER BY

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

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;

---

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

테이블의 데이터 조회

SELECT

   DISTINCT u_add AS '주소'

FROM

   tb_user;

테이블의 데이터 한글 정렬

SELECT

   DISTINCT u_add AS '주소’

FROM

   tb_user

ORDER BY CONVERT(u_add USING BINARY);

---

사용자 지정 정렬- ORDER BY FIELD

SELECT

   DISTINCT u_add AS '주소'

FROM

   tb_user

ORDER BY FIELD(u_add,'금암동','덕진동',

              '송천동','호성동','서신동',

              '조촌동','서서학동','효자동',

              '삼천동');

 

---

테이블 데이터 그룹 출력 – GROUP BY

SELECT

   u_add  AS '주소’

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

FROM

   tb_user

GROUP BY u_add;

---

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

SELECT

   u_add  AS '주소’

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

FROM

   tb_user

GROUP BY u_add

HAVING COUNT(u_id) > 1;