MSSQL 요일별, 주별 검색 쿼리 참고용

select datepart(week,datefield) as '주차'
,min(convert(char(10),datefield,120)) as '시작일'
,max(convert(char(10),datefield,120)) as '종료일'
,min(case when DATENAME(WEEKDAY,datefield)='일요일' then convert(char(10),datefield,120) end) as '일요일'
,min(case when DATENAME(WEEKDAY,datefield)='월요일' then convert(char(10),datefield,120) end) as '월요일'
,min(case when DATENAME(WEEKDAY,datefield)='화요일' then convert(char(10),datefield,120) end) as '화요일'
,min(case when DATENAME(WEEKDAY,datefield)='수요일' then convert(char(10),datefield,120) end) as '수요일'
,min(case when DATENAME(WEEKDAY,datefield)='목요일' then convert(char(10),datefield,120) end) as '목요일'
,min(case when DATENAME(WEEKDAY,datefield)='금요일' then convert(char(10),datefield,120) end) as '금요일'
,min(case when DATENAME(WEEKDAY,datefield)='토요일' then convert(char(10),datefield,120) end) as '토요일'
from searchTable
where datefield between '2018-01-01 00:00:00' and '2018-12-31 23:59:59'
group by datepart(week,datefield)
order by datepart(week,datefield)

내역이 제대로 들어가있으면 달력이된다.... ㅡ.ㅡ;;

VB로 MS-SQL 연동

insert,update,delete

Imports System.Data
Imports System.Data.SqlClient
Partial Class _Default
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles MyBase.Load
        Dim connectionString As String
        Dim sqlConn As New SqlConnection
        Dim sqlComm As New SqlCommand
        connectionString = "server = 127.0.0.1,1433; uid = sa; pwd = password; database = member;"
        sqlConn = New SqlConnection(connectionString)
        sqlComm = New SqlCommand()
        sqlComm.Connection = sqlConn
        sqlComm.CommandText = "insert into tbl_member (code,id,addr) values (@param1,@param2,param3)"
        'sqlComm.CommandText = "update tbl_member set addr=@param3 where code=@param1 and id=param2"
        'sqlComm.CommandText = "delete tbl_member where code=@param1 and id=param2"
        sqlComm.Parameters.AddWithValue("@param1", "1")
        sqlComm.Parameters.AddWithValue("@param2", "abc")
        sqlComm.Parameters.AddWithValue("@param3", "서울")
        Try
            sqlConn.Open()
            sqlComm.ExecuteNonQuery()
            sqlConn.Close()
        Catch ex As Exception
            Response.Write(ex)
        End Try
    End Sub
End Class

---

select

Imports System.Data
Imports System.Data.SqlClient
Partial Class _Default
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles MyBase.Load
        Dim connectionString As String
        Dim sqlConn As New SqlConnection
        Dim sqlComm As New SqlCommand
        Dim dt As DataTable
        dt = New DataTable("MEMBER")
        dt.Columns.Add("code", GetType(Integer))
        dt.Columns.Add("id", GetType(String))
        dt.Columns.Add("addr", GetType(String))
        connectionString = "server = 127.0.0.1,1433; uid = sa; pwd = password; database = member;"
        sqlConn = New SqlConnection(connectionString)
        sqlComm = New SqlCommand()
        sqlComm.Connection = sqlConn
        sqlComm.CommandText = "select top 10 code,id,addr from tbl_member where m_id=@param1 order by m_id asc"
        sqlComm.Parameters.AddWithValue("@param1", "master")
        Try
            sqlConn.Open()
            Dim rs As SqlDataReader = sqlComm.ExecuteReader()
            If rs.HasRows Then
                Do While rs.Read()
                    dt.Rows.Add(rs(0), rs(1), rs(2))
                Loop
            End If
            rs.Close()
            sqlConn.Close()
        Catch ex As Exception
            Response.Write(ex)
        End Try
        Response.Write("<table border=""1""><thead><tr><th>CODE</th><th>ID</th><th>ADDR</th></tr></thead><tbody>")
        For i As Integer = 0 To dt.Rows.Count - 1
            Response.Write(String.Format("<tr><td>{0}</td><td>{1}</td><td>{2}</td></tr>", dt.Rows(i).Item("code"), dt.Rows(i).Item("id"), dt.Rows(i).Item("addr")))
        Next
        Response.Write("</tbody></table>")
    End Sub
End Class

PHP에서 ODBC 이용 MS-SQL 연동

<?php
 $ListArr = array();
 $conn = odbc_connect('dbserver', 'ID', 'PASSWORD');
 if($conn){
  $sql = "select top 10".PHP_EOL.
    "m_code,m_id,convert(char(10),m_regdate,120) as m_regdate".PHP_EOL.
    "from tbl_member".PHP_EOL.
    "order by m_code desc".PHP_EOL;
  $rs = odbc_exec($conn, $sql);
  while (odbc_fetch_row($rs)){
   array_push($ListArr,
    array(
     odbc_result($rs,"m_code"),
     iconv('euc-kr','utf-8',odbc_result($rs,"m_id")),
     odbc_result($rs,"m_regdate")
    )
   );
  }
  odbc_close($conn);
 }
?>
<table border="1">
<thead><tr><th>CODE</th><th>ID</th><th>DATE</th></tr></thead>
<tbody>
<?php
 if(count($ListArr)>0){
  foreach($ListArr as $arr1){
   echo "<tr><td>".$arr1[0]."</td><td>".$arr1[1]."</td><td>".$arr1[2]."</td></tr>";
  }
 }else{
  echo "<tr><td colspan=\"3\" align=\"center\">No Exist</td></tr>";
 }
?>
</tbody>
</table>

C#으로 MS-SQL 연동

insert,update,delete

using System;
using System.Data.SqlClient;

namespace MsSql
{
    class Program
    {
        
        static void Main(string[] args)
        {
            // Sql 연결정보(서버:127.0.0.1, 포트:3535, 아이디:sa, 비밀번호 : password, db : member)
            string connectionString = "server = 127.0.0.1,3535; uid = sa; pwd = password; database = member;";
            // Sql 새연결정보 생성
            SqlConnection sqlConn = new SqlConnection(connectionString);
            SqlCommand sqlComm = new SqlCommand();
            sqlComm.Connection = sqlConn;
            sqlComm.CommandText = "insert into tbl_member (id,name,addr) values (@param1,@param2,@param3)";
            //sqlComm.CommandText = "update tbl_member set addr=@param3 where id=@param1 and name=@param2";
            //sqlComm.CommandText = "delete tbl_member where id=@param1 and name=@param2 and addr=@param3";
            sqlComm.Parameters.AddWithValue("@param1", "abc");
            sqlComm.Parameters.AddWithValue("@param2", "홍길동");
            sqlComm.Parameters.AddWithValue("@param3", "서울");
            sqlConn.Open();
            sqlComm.ExecuteNonQuery();
            sqlConn.Close();
        }
    }
}

---

select

using System;
using System.Data.SqlClient;

namespace MsSql
{
    class Program
    {
        
        static void Main(string[] args)
        {
            // Sql 연결정보(서버:127.0.0.1, 포트:3535, 아이디:sa, 비밀번호 : password, db : member)
            string connectionString = "server = 127.0.0.1,3535; uid = sa; pwd = password; database = member;";
            // Sql 새연결정보 생성
            SqlConnection sqlConn = new SqlConnection(connectionString);
            SqlCommand sqlComm = new SqlCommand();
            sqlComm.Connection = sqlConn;
            sqlComm.CommandText = "select top 10 id,addr from tbl_member where name=@param1 order by id asc";
            sqlComm.Parameters.AddWithValue("@param1", "김준");
            sqlConn.Open();
            using (SqlDataReader SqlRs = sqlComm.ExecuteReader())
            {
                Console.WriteLine("ID \t \t | Address");
                while (SqlRs.Read())
                {
                    Console.WriteLine(string.Format("{0} \t \t | {1}", SqlRs[0].ToString(),SqlRs[1].ToString()));
                }
            }
            sqlConn.Close();
        }
    }
}

MS-SQL 테이블 정보 얻는 쿼리(테이블스키마)

SELECT
 A.TABLE_NAME, 
 C.VALUE AS TABLE_COMMENT,
 A.COLUMN_NAME,
 A.DATA_TYPE, 
 ISNULL(CAST(A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR),  
 CAST(A.NUMERIC_PRECISION AS VARCHAR) + ',' + CAST(A.NUMERIC_SCALE AS VARCHAR)) AS COLUMN_LENGTH,
 A.COLUMN_DEFAULT, A.IS_NULLABLE,
 B.VALUE AS COLUM_COMMENT
FROM
 INFORMATION_SCHEMA.COLUMNS A
 LEFT OUTER JOIN 
 SYS.EXTENDED_PROPERTIES B
  ON B.major_id = object_id(A.TABLE_NAME) 
  AND A.ORDINAL_POSITION = B.minor_id
 LEFT OUTER JOIN 
  (SELECT object_id(objname) AS TABLE_ID, 
  VALUE
  FROM ::FN_LISTEXTENDEDPROPERTY
  (NULL, 'User','dbo','table',NULL, NULL, NULL)) C
  ON object_id(A.TABLE_NAME) = C.TABLE_ID
WHERE A.TABLE_NAME = '테이블명'
ORDER BY A.TABLE_NAME, A.ORDINAL_POSITION 

MS-SQL 테이블 조회

select name from sysobjects where type='u'

MS-SQL Database 이동 및 로그용량 관리

USE master

--sql 목록제거
EXEC sp_detach_db 'DB명', 'true'

--C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
--DB파일,DB_log파일 d:\SQL DATA\로 이동

--sql 목록추가
EXEC sp_attach_db @dbname = N'DB명', 
   @filename1 = N'd:\SQL Data\DB명.mdf', 
   @filename2 = N'd:\SQL Data\DB명_log.ldf'

--sql 로그용량 줄이기
use DB명
backup log DB명 with no_log 
dbcc shrinkfile('DB명_log', 50)

-- 트렌젝션 로그사이즈 제한
dbcc shrinkdatabase(db명,1000)

-- 로그용량 줄이기
USE [DB명];
GO
ALTER DATABASE DB명
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE ('DB명_Log', 10);
GO
ALTER DATABASE DB명
SET RECOVERY FULL;

Classic ASP 와 MS-SQL 연동

<OBJECT RUNAT="server" PROGID=ADODB.Connection id="ObjConn"> </OBJECT>
<%
   dim fs,objFile
   dim ObjSql ,WhereSql ,Rs,i,ListArr
   dim Field0,Field1,,Field2,Field3,Field4
   dim Val1, Val12,Url 
   dim intNowPage ,intPageSize ,intBlockPage 

   Set fs = Server.CreateObject("Scripting.FileSystemObject")
   Set objFile = fs.OpenTextFile("c:\DB_file.dat",1)
   ' c:\DB_file.dat 내용
   ' Provider=SQLOLEDB.1;User ID=sa;Password=1111;Initial Catalog=master;Data Source=123.123.123.123,1433
   ObjConn.open objFile.readline
 
   Val1 = Request.QueryString("Val1")
   Val2 = Request.QueryString("Val2")
 
   If Val1<>"" and Val12<>"" Then
      WhereSql = " WHERE " & Val1 & "='" & Val2 & & "'"
      Url = "&Val1=" & Val1 & "&Val2 =" & Val2
   End If
 
   ObjSql = "SELECT * FROM DB_TABLE" & WhereSql 
 
   Set Rs=ObjConn.Execute(ObjSql)
   If Not Rs.Eof Then ListArr = Rs.Getrows ' DB를 ListArr 에 저장
   Rs.Close
   Set Rs=Nothing
 
   intNowPage = Request.QueryString("page")  ' 현제페이지
   intPageSize = 10    ' 페이지크기
   intBlockPage = 10   ' 페이지묶음크기
 
    If Len(intNowPage) = 0 Then      ' 현제페이지값이없을경우
        intNowPage = 1
    End If

    If isArray(ListArr) then               ' 목록이 있을경우 페이지 수
       intTotalCount = Ubound(ListArr,2)+1
       If (intTotalCount Mod intPageSize)=0 Then
          intTotalPage = int(intTotalCount/intPageSize)
       Else
          intTotalPage = int(intTotalCount/intPageSize)+1
       End if
    End if

   If isArray(ListArr) Then   '자료 있다면 변수에 저장
     ' 페이지 나눌경우(없을경우 0 To Ubound(ListArr,2))
      For i=0+(intNowPage*intPageSize-intPageSize) to (intNowPage*intPageSize)-1   
        If i<=Ubound(ListArr,2) then
           Field0 = ListArr(0,i)
           Field1 = ListArr(1,i)
           Field2 = ListArr(2,i)
           Field3 = ListArr(3,i)
           Field4 = ListArr(4,i)
        End If
     Next
 
   ' 페이지 나누기
If isArray(ListArr) Then 
    intTemp = Int((intNowPage - 1) / intBlockPage) * intBlockPage + 1
 
    If intTemp = 1 Then
     Response.Write("◀")
    Else 
     Response.Write("<a href=""?page=" & intTemp - intBlockPage & Url & """>◀</a>")
    End If
 
    intLoop = 1
 
    Do Until intLoop > intBlockPage Or intTemp > intTotalPage
     If intTemp = CInt(intNowPage) Then
      Response.Write("<font size= 3><b>" & intTemp &"</b></font> " )
     Else
      Response.Write("<a href=""?page=" & intTemp &url &""">" & intTemp& "</a> ")
     End If
     intTemp = intTemp + 1
     intLoop = intLoop + 1
    Loop
 
     If intTemp > intTotalPage Then
       Response.Write("▶")
     Else
       Response.Write("<a href=""?page=" & intTemp & Url &  """>▶</a>")
     End If
   End if
%>