[데이터베이스] Microsoft SQL 사용법
코더 제로 2020. 9. 10.
1. 데이터베이스(DATABASE)
1.1 데이터베이스 생성
CREATE DATABASE 데이터베이스명;
1.2 데이터베이스 선택
USE 데이터베이스명;
1.3 데이터베이스 삭제
DROP DATABASE 데이터베이스명;
2. 테이블(TABLE)
2.1 데이터 정의어(DDL) : CREATE, ALTER, DROP
데이터베이스, 테이블, 뷰 생성 및 수정
2.1.1 테이블 생성(CREATE)
① 기본 테이블 생성
CREATE TABLE
(
컬럼명 데이터형식 제약조건,
...
)
create table user(
id int constraint pk_code primary key,
name char(16),
phone char(16)
);
② 디폴트 지정
CREATE TABLE 테이블명 (컬럼 데이터형 DEFAULT 디폴트값, ...);
③ 유니크 설정
CREATE TABLE 테이블명 (컬럼명 데이터형 CONSTRAINT 이름 UNIQUE, ...);
④ NOT NULL 설정
CREATE TABLE 테이블명 (컬럼 데이터형 CONSTRAINT 이름 NOT NULL, ...);
⑤ 기본키 설정
CREATE TABLE 테이블명 (컬럼 데이터형 CONSTRAINT 이름 PRIMARY KEY, ...);
⑥ 외부키 설정
CREATE TABLE 테이블명 (컬럼 데이터형 CONSTRAINT 이름 FOREIGN KEY REFERENCES 부모테이블이름(부모컬럼), ...);
⑦ CHECK 설정
CREATE TABLE 테이블명 (컬럼 데이터형 CONSTRAINT 이름 CHECK(조건), ...);
2.1.1.1 데이터 형식
① 숫자형 데이터 형식
|
데이터 형식 |
바이트 수 |
범위 |
설명 |
|
BIT |
1 |
0, 1, NULL |
Boolean형. 참 (True, 1) 거짓 (False, 0) |
|
TINYINT |
1 |
0 ~ 255 |
양의 정수 |
|
SMALLINT |
2 |
-32,768 ~ 32,767 |
정수 |
|
INT |
4 |
-2,147,483,648 ~ -2,147,483,647 |
정수 |
|
BIGINT |
8 |
-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807 |
정수 |
|
DECIMAL(p, s) |
5 ~ 17 |
|
고정 정밀도(p)와 배율(s)을 가진 숫자형. |
|
NUMERIC |
5 ~ 17 |
|
DECIMAL과 같은 데이터 형식 |
|
FLOAT[(p)] |
4 ~ 8 |
- 1.79E+308 ~ -2.23E-308 |
p가 1~24 이면 4바이트 |
|
REAL |
4 |
- 3.40E+38 ~ -1.18E - 38 |
FLOAT(24)와 같음 |
② 문자형 데이터 형식
|
데이터 형식 |
바이트 수 |
설명 |
|
CHAR[(n)] |
0 ~ 8,000 |
고정길이 문자형 |
|
NCHAR([n]) |
0 ~ 8,000 |
0~4,000자 글자. 유니코드 고정길이 문자형 |
|
VARCHAR[(n | max)] |
0 ~ (2GB) |
가변길이 문자형. |
|
NVARCHAR[(n | max)] |
0 ~ |
유니코드 가변길이 문자형. |
|
BINARY[(n)] |
0 ~ 8,000 |
고정길이의 이진 데이터 값 |
|
VARBINARY[(n | max)] |
0 ~ |
가변길이의 이진 데이터 값. |
③ 날짜 및 시간 데이터 형식
|
데이터 형식 |
바이트 수 |
설명 |
|
DATETIME |
8 |
날짜 1753-1-1 ~ 9999-12-31, 시간 00:00:00 ~ 23:59:59.997 |
|
DATETIME2 |
6 ~ 8 |
날짜 0001-1-1~9999-12-31, 시간 00:00:00~23:59:59.9999999 |
|
DATE |
3 |
0001-1-1~9999-12-31 YYYY-MM-DD |
|
TIME |
5 |
00:00:00.00000000~23:59:59.9999999 hh:mm:ss |
④ 통화 데이터 형식
|
데이터 형식 |
바이트 수 |
범위 |
설명 |
|
MONEY |
8 |
-922,337,203,685,477.5808 ~ 922,337,203,685,477.5807 |
화폐 단위로 사용 |
|
SMALLMONEY |
4 |
-214,748.3648 ~ 214,748.3647 |
화폐 단위로 사용 |
⑤ 기타 데이터 형식
|
데이터 형식 |
바이트 수 |
설명 |
|
TIMESTAMP |
8 |
VARBINARY(8)과 동일. |
|
ROWVERSION |
8 |
TIMESTAMP와 동일 |
|
SYSNAME |
128 |
NVARCHAR(128)과 동일. 데이터베이스 개체의 이름에 사용 |
|
CURSOR |
1 |
T-SQL 커서 |
|
TABLE |
|
테이블 저장 |
|
UNIQUEIDENTIFIER |
16 |
복제(Replication)에서 사용되는 자료형. |
|
SQL_VARIANT |
|
다른 데이터 형식의 저장이 가능한 데이터형 (예외도 있음). |
|
XML |
|
XML 데이터를 저장하기 위한 형식. |
2.1.1.2 제약조건
- PRIMARY KEY: 기본키. 중복키 방지
- FOREIGN KEY: 외래키. 하나의 테이블이 다른 테이블에 의존함.
- UNIQUE : 필드의 데이터 값이 유일해야 함.
- CHECK : 주어진 조건에 해당하는 값만 입력 가능.
- DEFAULT : 해당 필드의 데이터 값이 기본값으로 설정함.
- NULL : 필드의 데이터 값이 NULL이면 허용.
2.1.2 변경(ALTER)
① 컬럼 추가
ALTER TABLE 테이블명 ADD 컬럼 데이터형 제약, ... ;
② 컬럼 삭제
ALTER TABLE 테이블명 DROP COLUMN 컬럼;
③ 제약 추가
ALTER TABLE 테이블명 ADD CONSTRAINT 이름 제약문;
④ 제약 삭제
ALTER TABLE 테이블명 DROP CONSTRAINT 제약명;
⑤ 제약 효력 정지
ALTER TABLE 테이블명 NOCHECK CONSTRAINT 제약명;
⑥ 제약 효력 유효
ALTER TABLE 테이블명 CHECK CONSTRAINT 제약명;
2.1.3 제거(DROP)
DROP TABLE 테이블명;
2.2 데이터 조작어(DML) : SELECT, INSERT, UPDATE, DELETE
테이블에 데이터를 검색하거나 삽입, 삭제, 수정
2.2.1 데이터 조회(SELECT)
① 모든 컬럼 불러오기
SELECT * FROM 데이블명;
② 상위 n개 데이터만 가져오기
SELECT TOP n * FROM 테이블명;
③ 특정 컬럼 가져오기
SELECT 컬럼1, 컬럼2, 컬럼3 FROM 테이블명;
④ 내컬럼에 별명 붙이기용
SELECT 컬럼1 별명1, 컬럼2 AS 별명2 FROM 테이블명;
⑤ 컬럼 별명에 스페이스가 들어갈 경우는 작은따옴표 사용
SELECT 컬럼3 '별 명3' FROM 테이블명;
⑥ 중복되지 않는 데이터만 가져오기 : 데이터는 오름차순으로 재배열 됨
SELECT DISTINCT 컬럼 FROM 테이블명;
⑦ 조건에 해당하는 데이터 가져오기
SELECT * FROM 테이블명 WHERE 조건절;
⑧ 컬럼이 x>=와 y<=사이의 데이터 가져오기
SELECT * FROM 테이블명 WHERE 컬럼 BETWEEN x AND y;
⑨ 컬럼이 a이거나 b인 데이터 가져오기
SELECT * FROM 테이블명 WHERE 컬럼 IN (a, b...);
⑩ 컬럼이 패턴과 같은 데이터 가져오기
SELECT * FROM 테이블명 WHERE 컬럼 LIKE '패턴';
패턴
% : 글자가 포함됨. 'a%'(a로 시작), '%a%'(중간에 a가 있는), '%a'(a로 끝나는)
_ : _만큼의 글자 포함. 'a_'(a로 시작하는 2자리), 'a___'(a로 시작하는 4자리), '__a'(3자리 데이터 중 a로 끝나는)
⑪ WHERE
컬럼이 NULL인 데이터 가져오기
SELECT * FROM 테이블명 WHERE 컬럼 IS NULL;
컬럼이 x와 y 사이가 아닌 데이터 가져오기
SELECT * FROM 테이블명 WHERE 컬럼 NOT BETWEEN x AND y;
컬럼이 a나 b가 아닌 데이터 가져오기
SELECT * FROM 테이블명 WHERE 컬럼 NOT IN (a, b...);
컬럼이 패턴과 같지 않은 데이터 가져오기
SELECT * FROM 테이블명 WHERE 컬럼 NOT LIKE '패턴';
컬럼이 NULL이 아닌 데이터 가져오기
SELECT * FROM 테이블명 WHERE 컬럼 IS NOT NULL;
SELECT * FROM 테이블명 WHERE 컬럼>=x AND 컬럼<=y;
SELECT * FROM 테이블명 WHERE 컬럼=a or 컬럼=b;
SELECT * FROM 데이블명 WHERE 컬럼1>=x AND (컬럼2=a OR 컬럼2=b);
⑫ ORDER BY
컬럼을 오름차순으로 재배열하기
SELECT * FROM 테이블명 ORDER BY 컬럼;
SELECT * FROM 테이블명 ORDER BY 컬럼 ASC;
컬럼을 내림차순으로 재배열하기
SELECT * FROM 테이블명 ORDER BY 컬럼 DESC;
복수 컬럼 재배열하기
SELECT * FROM 테이블명 ORDER BY 컬럼1 ASC, 컬럼2 DESC;
컬럼 순서로 재배열하기
SELECT * FROM 테이블명 ORDER BY 1 ASC, DESC 3;
2.2.2 데이터 삽입(INSERT)
① 모든 필드에 데이터를 넣을 때
INSERT INTO 테이블명 VALUES (값1, 값2, ...);
② 특정 컬럼에만 데이터를 넣을 때
INSERT INTO 테이블명 (컬럼1, 컬럼2, ...) VALUES (값1, 값2, ...);
③ 이미 존재하는 테이블에 데이터 추가
INSERT INTO 테이블명 SELECT * FROM 테이블명2;
INSERT INTO 테이블명(컬럼1, 컬럼2, ...) SELECT 컬럼1, 컬럼2, ...) FROM 테이블명2;
2.2.3 데이터 수정(UPDATE)
① 전체 데이터 갱신
UPDATE 테이블명 SET 컬럼1=값1, 컬럼2=값2;
② 조건에 해당되는 데이터 갱신
UPDATE 테이블명 SET 컬럼1=값1, 컬럼2=값2 WHERE 조건;
2.2.4 데이터 제거(DELETE)
① 전체 데이터 삭제
DELETE FROM 테이블명;
② 조건에 해당되는 데이터 삭제
DELETE FROM 테이블명 WHERE 조건;
2.3 데이터 제어어(DCL) : GRANT, REVOKE
데이터에 대한 사용 권한을 제어, 관리
2.3.1 권한부여(GRANT)
GRANT 개체권한명 ( 컬럼1, ... )
ON 보안개체 TO 보안주체명
WITH GRANT OPTION;
① 개체권한명
ALTER, DELETE, INSERT, SELECT, UPDATE 등
② 보안개체
TABLE, VIEW, SEQUENCE, PROCEDURE 등
③ 보안주체명
애플리케이션 역할, 어셈블리, 비대칭 키, 가용성 그룹, 인증서, 계약, 데이터베이스, 데이터베이스 범위 자격 증명, 엔드포인트, 전체 텍스트 카탈로그, 전체 텍스트 중지 목록, 함수, 로그인, 메시지 유형, Object, 큐, 원격 서비스 바인딩, 역할, 우팅, 스키마, 검색 속성 목록, 서버, 서비스, 저장 프로시저, 대칭 키, 동의어, 시스템 개체, 테이블, Type, 사용자, 보기, XML 스키마 컬렉션
2.3.2 권한제거(REVOTE)
REVOKE 개체권한명
ON 객체명 FROM 보안개체
CASCADE;
3. 뷰(VIEW)
3.1 뷰 생성 (CREATE)
① 뷰 만들기
CREATE VIEW 뷰명 AS SELECT문;
② 컬럼의 별칭 붙이기
CREATE VIEW 뷰명 (별칭1, 별칭2, ...) AS SELECT문;
CREATE VIEW 뷰명 AS (SELECT 컬럼1 AS 별칭1, 컬럼2 AS 별칭2, ...);
③ 뷰 암호
CREATE VIEW 뷰명 WITH ENCRYPTION AS SELECT문;
④ WITH CHECK OPTION 설정
CREATE VIEW 뷰명 AS SELECT문 WITH CHECK OPTION;
3.2 변경(ALTER)
ALTER VIEW 뷰명 AS SELECT문;
3.3 데이터 삽입(INSERT)
INSERT INTO 뷰명 (컬럼1, 컬럼2, ...) VALUES (값1, 값2, ...);
3.4 데이터 수정(UPDATE)
UPDATE 뷰명 SET 컬럼=값 WHERE 조건;
3.5 제거(DROP)
DROP VIEW 뷰명;
4. 트리거(Trigger)
4.1 삽입(INSERT)
CREATE TRIGGER 트리거명 on 테이블명 FOR INSERT AS SQL문;
CREATE TRIGGER 트리거명 on 테이블명 INSTEAD OF DELETE AS SQL문;
4.2 수정(UPDATE)
CREATE TRIGGER 트리거명 on 테이블명 AFTER UPDATE AS SQL문;
4.3 제거(DROP)
DROP TRIGGER 트리거명;
5. SQL 연산자
5.1 연산자
- 산술 연산자 : +, -, *, /
- 비교 연산자 : =, !=, <>, <=, <, >=, >
- 논리 연산자 : OR, AND, NOT
- 비트 논리 연산자 : &, |, ~
- 연결 키워드 : +
① SELECT 문의 연산
SELECT 컬럼1, 컬럼2, 컬럼3+컬럼4 AS '별명' FROM 테이블명;
② ORDER BY 구의 연산
SELECT 컬럼1, 컬럼2, 컬럼3+컬럼4 AS '별명' FROM 테이블명 ORDER BY 컬럼3+컬럼4 DESC;
SELECT 컬럼1, 컬럼2, 컬럼3+컬럼4 AS '별명' FROM 테이블명 ORDER BY 3 DESC;
③ WHERE 구의 연산
SELECT 컬럼1, 컬럼2, 컬럼3+컬럼4 AS '별명' FROM 테이블명 WHERE 컬럼2>=(컬럼3+컬럼4);
④ NULL 연산
SELECT 컬럼1, 컬럼2, ISNULL(컬럼3, 0) + ISNULL(컬럼4, 0) AS '별명' FROM 테이블명;
⑤ 날짜 연산
현재 날짜
SELECT GETDATE();
SELECT 날짜컬럼, 날짜컬럼-30 FROM 테이블명;
SELECT 날짜컬럼, DATEDIFF(day, 날짜컬럼, GETDATE()) FROM 테이블명;
⑥ 문자 연산
SELECT 컬럼1 + 컬럼2 FROM 테이블명;
SELECT 컬럼 + '문자열' FROM 테이블명;
SELECT 컬럼1 + '문자열' + 컬럼2 FROM 테이블명;
5.2 IN 연산자
컬럼/값 IN (값1, 값2)
SELECT name,id from user WHERE ID IN('admin','guest');
NOT IN도 존재함.
5.2 LIKE 연산자
조회 조건이 명확하지 않을 경우 사용.
wildcard
- % : 모든 문자 의미
- _ : 한 글자 의미
id like 'abc%' : abc 뒤에 어떤 값이 와도 상관없음.
id like 'abc_' : abc 뒤에 한 글자만 있는 값
6. SQL 함수
6.1 수치 함수
- 반올림 및 자르기 : ROUND(수치값, 반올림위치)
- 절대값 : ABS(수치 데이터)
- 부호 : SIGN(수치 데이터)
- 제곱근 : SQRT(수치값)
- n승 : POWER(수치값, n)
6.2 문자열 함수 정리
6.2.1 Ascii()
문자열의 제일 왼쪽 문자의 아스키 코드 값을 반환(Integer)
SELECT Ascii('abcd');결과 : a의 아스키 코드값인 97 반환
6.2.2 Char()
정수 아스키 코드를 문자로 반환(Char)
SELECT Char(97);결과 : a
6.2.3 Charindex()
문자열에서 지정한 식의 위치를 반환
SELECT Charindex('b','abcde'); -- 결과 : 2
SELECT Charindex('b','abcde',2); -- 결과 : 2
SELECT Charindex('b','abcde',3); -- 결과 : 0
6.2.4 Difference()
두 문자식에 SUONDEX 값 간의 차이를 정수로 반환
SELECT Difference('a','b');
6.2.5 Left()
문자열에서 왼쪽에서부터 지정한 수만큼의 문자를 반환
SELECT Left('abced',3);결과 : 3
6.2.6 Len()
문자열의 길이 반환
SELECT Len('abced'); 결과 : 5
6.2.7 Lower()
대문자를 소문자로 반환
SELECT Lower('ABCDE');결과 : abcde
6.2.8 Ltrim()
문자열의 왼쪽 공백 제거
SELECT Ltrim(' AB CDE');결과 : AB CDE
6.2.9 Nchar()
지정한 정수 코드의 유니코드 문자 반환
SELECT Nchar(20);결과 :
6.2.10 Replace
문자열에서 바꾸고 싶은 문자 다른 문자로 변환
SELECT Replace('abcde','a','1');결과 : 1bcde
6.2.11 Replicate()
문자식을 지정한 횟수만큼 반복
SELECT Replicate('abc',3);결과 : abcabcabc
6.2.12 Reverse()
문자열을 역순으로 출력
SELECT Reverse('abcde');결과 : edcba
6.2.13 Right()
문자열의 오른쪽에서 부터 지정한 수 만큼 반환(Left() 와 비슷 )
SELECT Right('abcde',3);결과 : cde
6.2.14 Rtrim()
문자열의 오른쪽 공백 제거
SELECT Rtrim(' ab cde ');결과 : ' ab cde' <-- 공백구분을위해 ' 표시
6.2.15 Space()
지정한 수만큼의 공백 문자 반환
SELECT Space(10);결과 : ' ' -- 그냥 공백이 나옴
확인을 위해서 SELECT 'S'+Space(10)+'E' 결과 >> S E
6.2.16 Substring()
문자,이진,텍스트 또는 이미지 식의 일부를 반환
SELECT Substring('abcde',2,3);결과 : bcd
6.2.17 Unicode()
식에 있는 첫번째 문자의 유니코드 정수 값을 반환
SELECT Unicode('abcde');결과 : 97
6.2.18 Upper()
소문자를 대문자로 반환
SELECT Upper('abcde');결과 : ABCDE
6.2.19 Isnumeric
해당 문자열이 숫자형이면 1 아니면 0을 반환. 숫자 : 1 , 숫자X : 0
SELECT Isnumeric('12'); --결과 : 1
SELECT Isnumeric('1z'); --결과 : 0
6.2.20 Isdate()
해당 문자열이 Datetime이면 1 아니면 0. 날짜 : 1 , 날짜 X : 0
SELECT Isdate('20071231'); --결과 : 1
SELECT Isdate(getdate()); --결과 : 1
SELECT Isdate('2007123'); --결과 : 0
SELECT Isdate('aa'); --결과 : 0
6.3 날짜및 시간함수 정리
6.3.1 getdate()
오늘 날짜를 반환(datetime)
6.3.2 DateAdd()
지정한 날짜에 일정 간격을 + 새 일정을 반환
SELECT Dateadd(s,2000,getdate());
6.3.3 Datediff()
지정한 두 날짜의 간의 겹치는 날짜 및 시간 범위 반환
SELECT DateDiff(d,getdate(),(getdate()+31));
6.3.4 Datename()
지정한 날짜에 특정 날짜부분을 나타내는 문자열을 반환
SELECT Datename(d,getdate());
6.3.5 Datepart()
지정한 날짜에 특정 날짜부분을 나타내는 정수를 반환
SELECT Datepart(d,getdate());돌려주는값(약어) : Year-yy, Quarter-qq, Month-mm, DayofYear-dy, Day-dd, Week-wk, Hour-hh, Minute-mi, Second-ss, Milisecond-ms
SELECT DATEADD(dd, 7, 날짜컬럼);Datename , Datepart 은 결과 값은 같으나 반환 값의 타입이 틀림.
6.3.6 Day()
지정한 날짜에 일 부분을 나타내는 정수를 반환
SELECT Day(getdate()); -- 일 반환
SELECT Month(getdate()); -- 월 반환
SELECT Year(getdate()); -- 년 반환
6.4. 형변환 함수
6.4.1 컬럼을 원하는 데이터 타입으로 변환
CONVERT(데이터 타입, 컬럼);
6.4.2 원하는 날짜 스타일로 변환
CONVERT(데이터 타입, 컬럼, 날짜형 스타일);
6.4.3 컬럼을 원하는 데이터 타입으로 변환
CAST(컬럼 AS 데이터 타입);
6.4.4 스타일
1->mm/dd/yy, 2->yy.mm.dd, 3->dd/mm/yy, 4->dd.mm.yy, 5->dd-mm-yy, 8->hh:mm:ss, 10->mm-dd-yy,
11->yy/mm/dd, 12->yymmdd
SELECT CONVERT(varchar(10), 날짜컬럼, 2);
7. 그룹화 함수
7.1 전체 데이터의 갯수 가져오기
SELECT COUNT(*) FROM 테이블명;
7.2 NULL은 제외한 컬럼의 데이터 갯수 가져오기
SEELECT COUNT(컬럼) FROM 테이블명;
7.3 컬럼의 합계 구하기
SELECT SUM(컬럼) FROM 테이블명;
7.4 컬럼의 최대값 구하기
SELECT MAX(컬럼) FROM 테이블명;
7.5 컬럼의 최소값 구하기
SELECT MIN(컬럼) FROM 테이블명;
7.6 컬럼의 평균값 구하기
SELECT AVG(컬럼) FROM 테이블명;
7.7 GROUP BY문
SELECT 컬럼 FROM 테이블명 GROUP BY 컬럼;
SELECT 컬럼1, SUM(컬럼2) FROM 테이블명 GROUP BY 컬럼1;
SELECT 컬럼1, COUNT(*) FROM 테이블명 GROUP BY 컬럼1;
SELECT 컬럼1, 컬럼2, MAX(컬럼3) FROM 테이블명 GROUP BY 컬럼1, 컬럼2;
8. 조건
- HAVING : 그룹 함수를 사용할 경우의 조건을 지정한다
- HAVING의 위치 : GROUP BY의 뒤 ORDER BY의 앞에 지정한다
- COMPUTE: 각 그룹의 소계를 요약해서 보여준다
ORDER BY가 항상 선행해서 나와야 한다
SELECT 컬럼1, SUM(컬럼2) FROM 테이블명 GROUP BY 컬럼1 HAVING SUM(컬럼2) < a;
SELECT 컬럼1, SUM(컬럼2) FROM 테이블명 ORDER BY 컬럼1 COMPUTE SUM(컬럼2);
.참고사이트
Transact-SQL 참조(데이터베이스 엔진) :
https://docs.microsoft.com/ko-kr/sql/t-sql/language-reference?view=sql-server-2017
'프로그래밍' 카테고리의 다른 글
| [데이터베이스] SSMS(SQL Server Management Studio) 사용법 (0) | 2020.12.30 |
|---|---|
| [데이터베이스] Microsoft SQL 설치 (0) | 2020.12.30 |
| SQL SERVER 2019 Express 설치하기 (0) | 2020.11.03 |
| SVN 이란?(정의) SVN 사용 이유 (0) | 2020.10.27 |
| [DataBase] 스키마(Schema)란?? (0) | 2020.10.27 |