常用數據庫之sql server的使用和搭建

1.1 介紹

?? 關系型數據庫，SQLServer是由微軟公司開發的一種關系型據庫管理系統，它已廣泛用于電子商務、銀行、保險、電力等行業。SQLServer提供了對XML和Internet標準的支持，具有強大的、靈活的、基于Web的應用程序管理功能。

1.2 優缺點

優點：

1>通過創建唯一性索引，可以保證數據庫表中每一行數據的唯一性。
2>通過創建索引可以大大加快數據的檢索速度
3>通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的性能。
4>在使用分組和排序子句進行數據檢索時，同樣可以顯著減少查詢中分組和排序的時間

缺點：

1>只能運行在windows平臺，平臺單一不具備開放性
2>并行實施與共存模型不成熟，難以處理大規模的數據，伸縮性有限
3>當用戶連接多時性能會變得很差并且不穩定
4>客戶端支持應用模式，僅支持C/S模式

1.3 服務器安裝

1.3.1 安裝軟件：

1> 下載sql server 2019 下載地址：https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads

2> 選擇自定義類型進行安裝：

3> 選擇語言和安裝位置,點擊安裝

4>安裝成功后出現以下界面，選擇安裝，選擇全新的SQLServer 獨立安裝或向現有安裝添加功能

5>選擇指定可用版本Developer，點擊下一步

6>更新選擇，可點可不點

7>安裝執行完畢后繼續下一步

8>功能選擇中，可以不勾選R、Python、java，其他都勾選后，點擊下一步

9>選擇默認實例，點擊下一步，下一步

10>選擇混合模式，設置密碼，和添加當前用戶，然后點擊下一步

11>選擇表格模式，添加當前用戶后，點擊下一步

12>默認配置無需更改，點擊下一步，下一步

13>Distributed Replay的訪問權限,選擇的是當前用戶，點擊下一步，下一步

14>脫機安裝Microsoft機器學習服務器組件

將頁面4個藍色鏈接分別復制到瀏覽器，下載4個壓縮包，將4個壓縮包放在同一個文件下，無需解壓，然后把每個壓縮包的名字里的1033替換成2052

以上操作都ok后，接下來就可以回到sql sever安裝界面中選擇包含4個壓縮包的文件目錄了，就會出現下一步按鈕了,我們就可以繼續點擊下一步

15>進入準備安裝界面，點擊安裝，耐心等待安裝完成

17>下載SQL Server Management Studio軟件，用于數據庫的連接與管理，下載地址：https://aka.ms/ssmsfullsetup，安裝后，進入連接

18>連接后，右鍵彈出界面點擊屬性

19>在屬性頁面里，選擇安全性，選擇SQL Server和windows身份驗證模式，然后點擊確定

20>在安全性-》登錄名-sa，右鍵點擊，彈出屬性界面，在常規界面上確認密碼

21>屬性界面，在狀態欄，上勾選授予和啟用

22>設置允許遠程連接，打開sql server congfiguration Manager，啟用TCP/IP

23>右鍵TCP/IP可以打開屬性界面，檢查和配置IP、端口

24>都設置好后，可以在SQL Server Management Studio重啟，也可以在sql server congfiguration Manager的重啟第三個服務

1.4 簡單使用

1>使用navicat連接數據庫:

2> 連接后，右鍵新建數據庫，在新的數據庫上新建表格

3>客戶端連接數據庫代碼

database.h

/* * Filename: DataBase.h* Modify: ybLin* Description: database* *******************************************************/ #ifndef __EXSKY_DATA_BASE_H__ #define __EXSKY_DATA_BASE_H__ #include <basic/myThread.h>#import "..\WinLib\Others\msado15.dll" no_namespace rename("EOF","adoEOF")typedef struct {char sMateriel[256];char sOrderNo[256];char sModel[256];char sLens[256];char sSNCode[256];char sMacId[256];char sAddTime[64]; } _mac_info;class CDataBase { public:CDataBase(const char* hostIP = NULL);~CDataBase();public:bool m_nCheckModel;void UpdateDB();bool InitDB();bool Init(const char* hostIP = "192.168.8.121", const char* dataBase = "ManageDB", int hostPort = 1433,const char* user = "sa", const char* pwd = "123456");bool Free();//useint InsertMacInfo(_mac_info* _info);int GetMacInfo(_mac_info* _info)private:bool ADOExecute(const char* sql);int DataWrite(const char* sql); private:bool m_bInitOk;my_mutex_t* m_pDbMutex;char m_sHostIp[256];int m_nHostPort;char m_sDataBase[256];char m_sAccount[256];char m_sPwd[256];BOOL m_bTest;_ConnectionPtr m_pConnection;_RecordsetPtr m_pADOSet;_RecordsetPtr m_pADOSetAuto; };#endif

database.cpp

/* * Filename: DataBase.cpp* Modify: ybLin* Description: database* *******************************************************/#ifdef WIN32 #include <time.h> #include <Windows.h> #include <io.h> #endif#include "DataBase.h" #include <time.h> #include <stdio.h> #include <Windows.h> #include <time.h> #include <basic/myThread.h>int SwitchPath(char* target, const char* src) {while (*src != '\0') {if (*src == '\\')*target++ = *src;*target++ = *src++;}*target = '\0';return 0; }CDataBase::CDataBase(const char* hostIP) {m_nCheckModel = false;m_pDbMutex = my_mutex_init();memset(m_sDataBase, 0, sizeof(m_sDataBase));memset(m_sHostIp, 0, sizeof(m_sHostIp));m_nHostPort = 0;memset(m_sAccount, 0, sizeof(m_sAccount));memset(m_sPwd, 0, sizeof(m_sPwd));m_bInitOk = false; }CDataBase::~CDataBase() {if (m_bInitOk) Free();my_mutex_destroy(m_pDbMutex); }void CDataBase::UpdateDB() {char sql[1024] = {0};snprintf(sql, 1023, "if not exists(select * from syscolumns where id=object_id('sninfo') and name='model') ALTER table sninfo add model varchar(30)");DataWrite(sql);char sql2[1024] = {0};snprintf(sql2, 1023, "if not exists(select * from syscolumns where id=object_id('sninfo') and name='model2') ALTER table sninfo add model2 varchar(30)");DataWrite(sql2); }bool CDataBase::Init(const char* hostIP, const char* dataBase, int hostPort, const char* user, const char* pwd) {snprintf(m_sHostIp, 255, "%s", hostIP);m_nHostPort = hostPort;snprintf(m_sDataBase, 255, "%s", dataBase);snprintf(m_sAccount, 255, "%s", user);snprintf(m_sPwd, 255, "%s", pwd);return InitDB(); }bool CDataBase::InitDB() {if (m_bInitOk)return false;HRESULT hr;try{my_print("CDataBase::InitDB Start.");char sConnectStr[256] = {0};snprintf(sConnectStr, sizeof(sConnectStr)-1, "pConn-&gt;ConnectionString = &quot;driver={SQL Server};Server=%s,%d;DATABASE=%s;", m_sHostIp, m_nHostPort, m_sDataBase);hr = m_pConnection.CreateInstance("ADODB.Connection");if(SUCCEEDED(hr)){m_pConnection->ConnectionTimeout = 5;hr = m_pConnection->Open(sConnectStr, m_sAccount, m_sPwd, 0);}my_print("yibin test CDataBase::InitDB Success.");}catch(_com_error e){char sErrorMsg[256];snprintf(sErrorMsg, sizeof(sErrorMsg)-1, "數據庫連接錯誤:%s", e.Description());my_print(sErrorMsg);return FALSE;} m_pADOSet.CreateInstance(__uuidof(Recordset));m_pADOSetAuto.CreateInstance(__uuidof(Recordset));m_bInitOk = true;return m_bInitOk; }bool CDataBase::Free() {if(m_bInitOk){if(adStateOpen == m_pConnection->State) m_pConnection->Close();m_pConnection.Release();if(adStateOpen == m_pADOSet->State) m_pADOSet->Close();m_pADOSet.Release();if(adStateOpen == m_pADOSetAuto->State) m_pADOSetAuto->Close();m_pADOSetAuto.Release();m_bInitOk = false;} else {return false;}return true; }bool CDataBase::ADOExecute(const char* sql) {if ( m_pADOSet->State){m_pADOSet->Close();}try{m_pADOSet->Open(sql, m_pConnection.GetInterfacePtr(), adOpenStatic, adLockOptimistic, adCmdUnknown);return true;}catch(_com_error &e){char sErrorMsg[256];snprintf(sErrorMsg, sizeof(sErrorMsg)-1, "Execute SQL:%s ERROR:%s", sql, e.ErrorMessage());my_print(sErrorMsg);return false;}return true; }int CDataBase::DataWrite(const char* sql) {int nRet = 0;char str[1024] = {0};my_mutex_lock(m_pDbMutex);if (!m_bInitOk) {my_mutex_unlock(m_pDbMutex);return -1;}SwitchPath(str, sql);if (ADOExecute(str) != true) {Free();InitDB();if (m_bInitOk && ADOExecute(str) == true) {} else {nRet = -1;}}my_mutex_unlock(m_pDbMutex);return nRet; }//use int CDataBase::InsertMacInfo(_mac_info* _info) {char sql[1024] = {0};snprintf(sql, 1023, "select Mac,SNCode,AddTime from IPCInfo WHERE Mac = '%s'", _info->sMacId);DataWrite(sql);if(!m_pADOSet->adoEOF)return -10;//addTimeSYSTEMTIME sysTime = {0};GetLocalTime(&sysTime);_snprintf(_info->sAddTime, sizeof(_info->sAddTime)-1, "%d-%02d-%02d %02d:%02d:%02d", sysTime.wYear, sysTime.wMonth, sysTime.wDay, sysTime.wHour, sysTime.wMinute, sysTime.wSecond);snprintf(sql, 1023, "INSERT INTO IPCInfo (Materiel, OrderNo, Model, Lens, SNCode, Mac, AddTime) VALUES('%s', '%s', '%s', '%s', '%s', '%s', '%s')", _info->sMateriel, _info->sOrderNo, _info->sModel, _info->sLens, _info->sSNCode, _info->sMacId, _info->sAddTime);return DataWrite(sql); }int CDataBase::GetMaxMacInfo(_mac_info* _info) {char sql[1024] = {0};snprintf(sql, 1023, "select Mac,SNCode,AddTime from IPCInfo order by Mac desc");unsigned mac_calc[6] = {0};if (DataWrite(sql) != 0) {my_print("DataWrite Fail \n");return -2;} else {_variant_t Holder;if(!m_pADOSet->adoEOF) {Holder = m_pADOSet->GetCollect("Mac");snprintf(_info->sMacId, sizeof(_info->sMacId)-1, Holder.vt==VT_NULL?"":(char*)(_bstr_t)Holder);Holder = m_pADOSet->GetCollect("SNCode");snprintf(_info->sSNCode, sizeof(_info->sSNCode)-1, Holder.vt==VT_NULL?"":(char*)(_bstr_t)Holder);Holder = m_pADOSet->GetCollect("AddTime");snprintf(_info->sAddTime, sizeof(_info->sAddTime)-1, Holder.vt==VT_NULL?"":(char*)(_bstr_t)Holder);}}if(sscanf(_info->sMacId, "%02X%02X%02X%02X%02X%02X", &mac_calc[5], &mac_calc[4], &mac_calc[3], &mac_calc[2], &mac_calc[1], &mac_calc[0]) != 6){my_print("sscanf Fail \n");return -3;}//addTimechar szTime[32] = {0};char AddDate[32] = {0};char AddTime[32] = {0};SYSTEMTIME sysTime = {0};GetLocalTime(&sysTime);snprintf(szTime, sizeof(szTime)-1, "%d-%02d-%02d", sysTime.wYear, sysTime.wMonth, sysTime.wDay);time_t iTime = time(NULL);char sTime[32] = {0};struct tm* ptm = localtime(&iTime);_snprintf(sTime, sizeof(sTime)-1, "%4d-%02d-%02d", ptm->tm_year + 1900,ptm->tm_mon + 1, ptm->tm_mday);sscanf(_info->sAddTime, "%s %s", AddDate, AddTime);char SNPre[32] = {0};char SNDate[32] = {0};if(strcmp(szTime, AddDate) == 0){strncpy (SNPre, _info->sSNCode, 3);int year;int month;int day;char date[32] = {0};sscanf(AddDate, "%d-%2d-%2d", &year, &month, &day);_snprintf(date, sizeof(date)-1, "%d%02d%02d", year, month, day);int snNum = atof(_info->sSNCode+11)+1;memset(_info->sSNCode, 0, sizeof(_info->sSNCode));_snprintf(_info->sSNCode, sizeof(_info->sSNCode)-1, "%s%s%04d", SNPre, date, snNum);}else{strncpy (SNPre, _info->sSNCode, 3);snprintf(SNDate, sizeof(szTime)-1, "%d%02d%02d", ptm->tm_year + 1900,ptm->tm_mon + 1, ptm->tm_mday);snprintf(_info->sSNCode, sizeof(_info->sSNCode), "%s%s%s",SNPre, SNDate, "0001");}_snprintf(_info->sMacId, sizeof(_info->sMacId)-1, "%02X%02X%02X%02X%02X%02X",mac_calc[5], mac_calc[4], mac_calc[3], mac_calc[2], mac_calc[1], mac_calc[0]);return 0; }

1.5 結果展示