기술 ≫ IT ≫ 인터넷

SQL 명령어

HTML,CSS
- sql
- 윈도, 프로그램 ASP, VB 팁
- 문자표, 샘플
- 식품관련 S/W

1. 테이블을 생성하면서 테이블의 데이타 복사
select * into 생성될테이블명 from 원본테이블명
테이블 구조만 복사하겠다면
select * into 생성될테이블명 from 원본테이블명 where 1=2

2. 테이블이 이미 생성되어 있는경우 데이타만 복사
insert into 카피될테이블명 select * from 원본테이블명
특정 데이타만 복사 하겠다면
insert into 카피될테이블명 select * from 원본테이블명 where 검색조건


UPDATE 테이블명 SET 컬럼 = 값 where 조건절;
SELECT * FROM 테이블명 WHERE 조건절          /* 조건에 해당하는 데이타 가져오기 */
SELECT * FROM 테이블명 WHERE 칼럼 BETWEEN x AND y    
  SELECT * FROM 테이블명 WHERE 칼럼 LIKE '패턴'           /* 칼럼이 패턴과 같은 데이타 가져오기 */
   ** 패턴에 사용되는 기호는 %, _가 있다
      'k%'(k로 시작되는), '%k%'(중간에 k가 있는), '%k'(k로 끝나는)
      'p_'(p로 시작하는 2자리), 'p___'(p로 시작하는 4자리), '__p'(3자리 데이타중 p로 끝나는)
  SELECT * FROM 테이블명 WHERE 칼럼 IS NULL               /* 칼럼이 NULL인 데이타 가져오기 */
  SELECT * FROM 테이블명 WHERE 칼럼 IS NOT NULL           /* 칼럼이 NULL이 아닌 데이타 가져오기 */
  SELECT * FROM 테이블명 WHERE 칼럼>=x AND 칼럼<=y        

  SELECT * FROM 테이블명 ORDER BY 칼럼1 ASC, 칼럼2 DESC   /* 복수 칼럼 재배열하기 */


  1. SELECT 문의 연산
      SELECT 칼럼1, 칼럼2, 칼럼3+칼럼4 AS '별명' FROM 테이블명
  2. 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
  3. WHERE 구의 연산
      SELECT 칼럼1, 칼럼2, 칼럼3+칼럼4 AS '별명' FROM 테이블명 WHERE 칼럼2>=(칼럼3+칼럼4)
  5. 날짜 연산  
     SELECT GETDATE()                                    /* 서버의 현재 날짜를 구한다 */
     SELECT 날짜칼럼, 날짜칼럼-7 FROM 테이블명
     SELECT 날짜칼럼, 날짜칼럼+30 FROM 테이블명
     SELECT 날짜칼럼, DATEDIFF(day, 날짜칼럼, GETDATE()) FROM 테이블명

  2. 문자열 함수
     SUBSTRING(문자열, 위치, 문자수)      /* 특정 위치 문자열 추출 */
     LEFT(문자열, 문자수)                 /* 왼쪽부터 지정한 갯수 문자열 추출 */
     RIGHT(문자열, 문자수)                /* 오른쪽부터 지정한 갯수 문자열 추출 */
     LEN(문자열)                          /* 문자열의 문자수 */
     LTRIM(문자열)                        /* 문자열 왼쪽의 스페이스를 삭제 */
     RTRIM(문자열)                        /* 문자열 오른쪽의 스페이스를 삭제 */
     UPPER(문자열)                        /* 대문자로 변환 */
     LOWER(문자열)                        /* 소문자로 변환 */

  3. 날짜 함수
     GETDATE()                                /* 현재 날짜와 시각 */
     DATEADD(돌려주는값, 기간, 날짜)          /* 날짜 및 시간의 더하기와 빼기 */
     DATEDIFF(돌려주는값, 시작날짜, 끝날짜)   /* 두 날짜와 시간의 차이 */
     DATEPART(돌려주는값, 날짜)               /* 날짜와 시간의 특정값을 돌려준다 */
   ** 돌려주는값(약어)
      Year-yy, Quarter-qq, Month-mm, DayofYear-dy, Day-dd, Week-wk,
      Hour-hh, Minute-mi, Second-ss, Milisecond-ms
      SELECT DATEADD(dd, 7, 날짜칼럼)  

  함수
   SELECT COUNT(*) FROM 테이블명               /* 전체 데이타의 갯수 가져오기 */
   SELECT SUM(칼럼) FROM 테이블명              /* 칼럼의 합계 구하기 */
   SELECT MAX(칼럼) FROM 테이블명              /* 칼럼의 최대값 구하기 */
   SELECT MIN(칼럼) FROM 테이블명              /* 칼럼의 최소값 구하기 */
   SELECT AVG(칼럼) FROM 테이블명              /* 칼럼의 평균값 구하기 */

  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

일별,주별,월별 통계
-- 주 단위
Select DATEPART(ww, order_dt), count(order_no) From 주문테이블
group by DATEPART(ww, order_dt)
order by DATEPART(ww, order_dt)

-- 월단위
Select DATEPART(mm, order_dt), count(order_no) From 주문테이블
group by DATEPART(mm, order_dt)
order by DATEPART(mm, order_dt)

-- 년단위
Select DATEPART(yy, order_dt), count(order_no) From 주문테이블
group by DATEPART(yy, order_dt)
order by DATEPART(yy, order_dt)

데이타 편집
  추가
   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
  
  갱신
   UPDATE 테이블명 SET 칼럼1=값1, 칼럼2=값2 WHERE 조건               /* 조건에 해당되는 데이타 갱신 */
  
  삭제
   DELETE FROM 테이블명                                              /* 전체 데이타 삭제 */
   DELETE FROM 테이블명 WHERE 조건       /* 조건에 해당되는 데이타 삭제 */

   CREATE TABLE 데이타베이스이름.소유자이름.테이블이름 (칼럼 데이타형 제약, ...) /* 테이블 만들기 */

  3. Stored Procedure(저장 프로시저)
   ** 데이타베이스내에서 SQL 명령을 컴파일할때 캐시를 이용할 수 있으므로 처리가 매우 빠르다
      반복적으로 SQL 명령을 실행할 경우 매회 명령마다 네트워크를 경유할 필요가 없다
      어플리케이션마다 새로 만들 필요없이 이미 만들어진 프로시저를 반복 사용한다
      데이타베이스 로직을 수정시 프로시저는 서버측에 있으므로 어플리케이션을 다시 컴파일할 필요가 없다
   ** 저장 프로시저의 소스 코드를 보고 싶으면 SP_HELPTEXT 프로시저명 을 사용한다

   CREATE PROC 프로시저명 AS SQL문   /* 저장 프로시저 */
   CREATE PROC 프로시저명 변수선언 AS SQL문 /* 인수를 가지는 저장 프로시저 */
   CREATE PROC 프로시저명 WITH ENCRYPTION AS SQL문 /* 저장 프로시저 보안 설정 */

  4. Trigger(트리거)
   ** 한 테이블의 데이타가 편집(INSERT/UPDATE/DELETE)된 경우에 자동으로 다른 테이블의
      데이타를 삽입, 수정, 삭제한다
   ** 트리거 내용을 보고 싶으면 SP_HELPTRIGGER 트리거명 을 사용한다

   CREATE TRIGGER 트리거명 ON 테이블명 FOR INSERT AS SQL문         /* INSERT 작업이 수행될때 */
   CREATE TRIGGER 트리거명 ON 테이블명 AFTER UPDATE AS SQL문       /* UPDATE 작업이 수행되고 난 후 */
   CREATE TRIGGER 트리거명 ON 테이블명 INSTEAD OF DELETE AS SQL문  
   DROP TRIGGER 트리거명

MS-SQL 백업 및 복원

전체 백업 (Full backup)
   - 처음 DB 생성시
   - 트랜잭션 로그를 비웠을시
   - DB에 변경이 생겼을때(ALTER DB)
   - 차등, 로그 백업 전 한번 이상
   BACKUP DATABASE 디비이름 TO 파일&장치

차등 백업 (Differential Backup)
   - 마지막 백업 이후에 변경된 모든 데이터를 백업
   - 복구시 마지막 차등백업과
      마지막과 가장 가까운 전체백업 파일
      BACKUP DATABASE 디비이름 TO 파일&장치
      WITH DIFFERENTIAL

미러 백업 (Mirror Backup)
  - 백업 데이터의 유실이나 파손 시
      데이터 유실을 막기 위해 사용
  - 백업 장치 이외에 하나의 장치에 똑같이 백업

      BACKUP DATABASE 디비명
      TO DISK = 'C:\a.bak'
      MIRROR TO DISK = 'D:\a.bak'
      WITH FORMAT

복사 전용 백업 (Copy Backup)
   - 데이터 베이스의 복사나 이동 테스트시 사용
      BACKUP DATABASE 디비이름 TO 장치
      WITH COPY_ONLY

      RESTORE DATABASE 디비이름 FROM 장치

---Excel 열기

Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet : Dim filteext As String = ""
'check for the file type
If IO.Path.GetExtension(fileName) = "xls" Then
                filteext = "Excel 8.0"
ElseIf IO.Path.GetExtension(fileName) = ".xlsx" Then
                filteext = "Excel 12.0"
End If
'open connection
MyConnection = New System.Data.OleDb.OleDbConnection _
               ("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & fileName & "';Extended Properties=" & filteext & ";")
            MyConnection.Open()
  Dim myTableName = MyConnection.GetSchema("Tables").Rows(0)("TABLE_NAME")
  Dim MyCommand As OleDbDataAdapter = New OleDbDataAdapter(String.Format("SELECT * FROM [{0}]", myTableName), MyConnection)
   MyCommand.TableMappings.Add("Table", "TestTable")
            DtSet = New System.Data.DataSet
    MyCommand.Fill(DtSet)
    DataGridView1.DataSource = DtSet.Tables(0)
            'DtSet.DataSetName.
    MyConnection.Close()