SQL 문

1. 데이터 베이스 접속

mysql -h[호스트명] -u[DB계정명] -p [데이터베이스명]

2. 계정 생성하기 

GRANT ALL PRIVILEGES ON DB명.* to 계정명@'%' IDENTIFIED BY '비밀번호'; (*는 모든 권한을 부여함을 의미)
GRANT ALL PRIVILEGES ON DB명.* to 계정명@'localhost' IDENTIFIED BY '비밀번호';

FLUSH PRIVILEGES; (grant 테이블을 reload함으로서 변경 사항을 즉시 반영)

• 만약 INSERT, DELETE, UPDATE와 같은 SQL문을 사용하지 않고 바로 grant 명령어를 사용하여 작업하였다면 FLUSH PRIVILEGES를 실행할 필요가 없다. 

3. mysql 버전과 현재 날짜 구하기

SELECT VERSION(), CURRENT_DATE;

4. 계산식 결과 출력

SELECT SIN(P1()/4), (4+1) * 5;

5. 쿼리 취소하기

• 쿼리를 중간에 취소할 때는 \c를 입력

 

6. SQL 파일 실행

mysql -u[DB계정명] -p [데이터베이스명] < test.sql

또는 mysql에 접속한 후 아래 명령 실행

source test.sql

또한 source 대신 \. 을 사용해도 된다.

\. test.sql

 

7. prompt에서 query 실행

$ mysql -u[DB계정명] -p -e "show tables" -t

-e : Execute command and quit. (Disables --force and history file )

-t : Output in table format.

 

8. SELECT 문

• 중복행 제거

SELECT DISTINC * FROM [table명]

• 컬럼 합성(Concatenation)

SELECT concat(empno, '-', deptno) AS '사번-부서번호';

• 정렬 (ORDER BY)
  • ASC : 오름차순 정렬, 기본값
  • DESC : 내림차순

SELECT(DISTINCT) [칼럼명] (AS ALIAS)
FROM [테이블명]
ORDER BY [칼럼이나 표현식] (ASC 또는 DESC)

ex) 첫번째 컬럼을 기준으로 내림차순 정렬
SELECT * FROM [테이블명] ORDER BY 1 DESC;

• BETWEEN절

SELECT [컬럼명]
FROM [테이블명] 
WHERE [칼럼명] BETWEEN [조건1] AND [조건2] 

• LIKE 키워드
  • 와일드 카드를 사용하여 특정 문자를 포함한 값에 대한 조건을 처리
  • %는 0에서부터 여러 개의 문자열을 나타냄.
  • _는 단 하나의 문자를 나타내는 와일드 카드
  • 예시

SELECT * FROM employee WHERE name like 'A%'
SELECT * FROM employee WHERE name like '%A%'
SELECT * FROM employee WHERE name like '_A%'

• 단일함수
  • UCASE, UPPER
  • LCASE, LOWER
  • SUBSTRING
  • LPAD, RPAD
  • TRIM, LTRIM, RTRIM
  • ABS(x) : x의 절대값 출력.
  • MOD (n, m): n을 m으로 나눈 나머지 값 출력.
  • FLOOR(x) : x보다 크지 않은 가장 큰 정수를 반환. (BIGINT로 자동 변환됨.)
  • CEILING(x) : x보다 작지 않은 가장 작은 정수를 반환.
  • ROUND(x) : x에 가장 근접한 정수를 반환.
  • POW(x, y), POWER(x, y) : x의 y제곱 승을 반환.
  • GREATEST(x, y, ...) : 가장 큰 갑 반환
  • LEAST(x, y,...) : 가장 작은 값 반환.
  • CURDATE(), CURRENT_DATE : 오늘 날짜를 YYYY-MM-DD나 YYYYDDDD 형식으로 반환.
  • CURTIME(), CURRENT_TIME : 현재 시각을 HH:MM:SS나 HHMMSS형식으로 반환.
  • NOW(), SYSDATE(), CURRENT_TIMESTAMP : 오늘 현식각을 YYYY-MM-DD HH:MM:SS나 YYYYMMDDHHMMSS형식으로 반환.
  • DATE_FORMAT(date, format) : 입력된 date를 format 형식으로 반환.
  • PERIOD_DIFF(p1, p2) : YYMM이나 YYYYMM으로 표기되는 p1과 p2의 개월 수 차이를 반환.
• CAST 형변환
  • type을 변경(지정)하는데 사용.

CAST(expression AS type) 또는
CONVERT(expression, type)
CONVERT(expression USING transcoing_name)

ex. SELECT CAST(now() as date);
    SELECT CAST(1-2 as unsigned);

• 그룹함수
  • COUNT(expr) : non-NULL인 row의 개수를 반환.
  • COUNT(DISTINCT expr, [expr...]) : non-NULL인 중복되지 않은 row의 개수를 반환.
  • COUNT(*) : row의 개수를 반환.
  • AVG(expr) : expr의 평균값을 반환
  • MIN(expr) : expr의 최솟값을 반환.
  • MAX(expr) : expr의 최댓값을 반환
  • SUM(expr) : expr의 합계를 반환.
  • GROUP_CONCAT(expr) : 그룹에서 concatenated한 문자를 반환.
  • VARIANCE(expr) : expr의 분산을 반환 
  • STDDEV(expr) : expr의 표준 편차를 반환
• GROUP BY절

 SELECT (DISTINCT) [컬럼명] (ALIAS)
 FROM 테이블명
 (WHERE 조건식)
 GROUP BY [컬럼이나 표현식];

• HAVING절

SELECT (DISTINCT) [컬럼명] (ALIAS)
FROM 테이블명
(WHERE 조건식)
GROUP BY [컬럼이나 표현식]
HAVING [그룹 조건식];

 

9. DCL

• Mysql 데이터 타입

• 테이블 생성
  • DEFAULT 키워드로 초기값을 지정할 수 있다.
  • 속성값의 빈 값 허용 여부는 NULL 또는 NOT NULL로 설정

CREATE table [table명](
    필드명1 타입 (NULL | NOT NULL) [DEFAULT] [AUTO_INCREMENT] [PRIMARY KEY],
    필드명2 타입 (NULL | NOT NULL) [DEFAULT] [AUTO_INCREMENT] [PRIMARY KEY],
    필드명3 타입 (NULL | NOT NULL) [DEFAULT] [AUTO_INCREMENT] [PRIMARY KEY],
    필드명4 타입 (NULL | NOT NULL) [DEFAULT] [AUTO_INCREMENT] [PRIMARY KEY],
    ..........
    PRIMARY KEY(필드명);
);

• 테이블 수정 (컬럼 추가 / 삭제)

ALTER TABLE [table명]
ADD 필드명 타입 [NULL | NOT NULL] [DEFAULT] [AUTO_INCREMENT];

ALTER TABLE [table명]
DROP [필드명];

• 테이블 수정 (칼럼 수정)

ALTER TABLE [table명]
change 필드명 새필드명 타입 [NULL | NOT NULL] [DEFAULT] [AUTO_INCREMENT];

• 테이블명 변경

ALTER TABLE [table명] rename [변경할 table명]

• 테이블 삭제
  • 제약 조건이 있을 경우에는 DROP table 명령으로 테이블이 삭제되지 않을 수 있다. 이 경우에는 테이블을 생성한 반대 순서로 삭제 해야한다. (ex. Forein Key가 등록된 테이블을 삭제하려고 할 때)

DROP TABLE [table명]

 

 

참조 URL

• https://sarc.io/index.php/mariadb/355-mysql-flush-privileges
• https://jybaek.tistory.com/316
• https://www.edwith.org/boostcourse-web/lecture/16721/
• https://www.edwith.org/boostcourse-web/lecture/16722/