1.1 介紹
?? 關(guān)系型數(shù)據(jù)庫(kù)，SQLServer是由微軟公司開(kāi)發(fā)的一種關(guān)系型據(jù)庫(kù)管理系統(tǒng)，它已廣泛用于電子商務(wù)、銀行、保險(xiǎn)、電力等行業(yè)。SQLServer提供了對(duì)XML和Internet標(biāo)準(zhǔn)的支持，具有強(qiáng)大的、靈活的、基于Web的應(yīng)用程序管理功能。
1.2 優(yōu)缺點(diǎn)
優(yōu)點(diǎn)：
1>通過(guò)創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫(kù)表中每一行數(shù)據(jù)的唯一性。
2>通過(guò)創(chuàng)建索引可以大大加快數(shù)據(jù)的檢索速度
3>通過(guò)使用索引，可以在查詢的過(guò)程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。
4>在使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時(shí)，同樣可以顯著減少查詢中分組和排序的時(shí)間
缺點(diǎn)：
1>只能運(yùn)行在windows平臺(tái)，平臺(tái)單一不具備開(kāi)放性
2>并行實(shí)施與共存模型不成熟，難以處理大規(guī)模的數(shù)據(jù)，伸縮性有限
3>當(dāng)用戶連接多時(shí)性能會(huì)變得很差并且不穩(wěn)定
4>客戶端支持應(yīng)用模式，僅支持C/S模式
1.3 服務(wù)器安裝
1.3.1 安裝軟件：
1> 下載sql server 2019 下載地址：https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads
2> 選擇自定義類型進(jìn)行安裝：
3> 選擇語(yǔ)言和安裝位置,點(diǎn)擊安裝
4>安裝成功后出現(xiàn)以下界面，選擇安裝，選擇全新的SQLServer 獨(dú)立安裝或向現(xiàn)有安裝添加功能
5>選擇指定可用版本Developer，點(diǎn)擊下一步
6>更新選擇，可點(diǎn)可不點(diǎn)
7>安裝執(zhí)行完畢后繼續(xù)下一步
8>功能選擇中，可以不勾選R、Python、java，其他都勾選后，點(diǎn)擊下一步
9>選擇默認(rèn)實(shí)例，點(diǎn)擊下一步，下一步
10>選擇混合模式，設(shè)置密碼，和添加當(dāng)前用戶，然后點(diǎn)擊下一步
11>選擇表格模式，添加當(dāng)前用戶后，點(diǎn)擊下一步
12>默認(rèn)配置無(wú)需更改，點(diǎn)擊下一步，下一步
13>Distributed Replay的訪問(wèn)權(quán)限,選擇的是當(dāng)前用戶，點(diǎn)擊下一步，下一步
14>脫機(jī)安裝Microsoft機(jī)器學(xué)習(xí)服務(wù)器組件
將頁(yè)面4個(gè)藍(lán)色鏈接分別復(fù)制到瀏覽器，下載4個(gè)壓縮包，將4個(gè)壓縮包放在同一個(gè)文件下，無(wú)需解壓，然后把每個(gè)壓縮包的名字里的1033替換成2052
以上操作都o(jì)k后，接下來(lái)就可以回到sql sever安裝界面中選擇包含4個(gè)壓縮包的文件目錄了，就會(huì)出現(xiàn)下一步按鈕了,我們就可以繼續(xù)點(diǎn)擊下一步
15>進(jìn)入準(zhǔn)備安裝界面，點(diǎn)擊安裝，耐心等待安裝完成
17>下載SQL Server Management Studio軟件，用于數(shù)據(jù)庫(kù)的連接與管理，下載地址：https://aka.ms/ssmsfullsetup，安裝后，進(jìn)入連接
18>連接后，右鍵彈出界面點(diǎn)擊屬性
19>在屬性頁(yè)面里，選擇安全性，選擇SQL Server和windows身份驗(yàn)證模式，然后點(diǎn)擊確定
20>在安全性-》登錄名-sa，右鍵點(diǎn)擊，彈出屬性界面，在常規(guī)界面上確認(rèn)密碼
21>屬性界面，在狀態(tài)欄，上勾選授予和啟用
22>設(shè)置允許遠(yuǎn)程連接，打開(kāi)sql server congfiguration Manager，啟用TCP/IP
23>右鍵TCP/IP可以打開(kāi)屬性界面，檢查和配置IP、端口
24>都設(shè)置好后，可以在SQL Server Management Studio重啟，也可以在sql server congfiguration Manager的重啟第三個(gè)服務(wù)
1.4 解決數(shù)據(jù)庫(kù)遠(yuǎn)程連接不上問(wèn)題
如出現(xiàn)這個(gè)問(wèn)題為防火墻問(wèn)題，解決辦法如下：
另外，修改了防火墻也可能導(dǎo)致其他主機(jī)ping不通當(dāng)前主機(jī)，這是因?yàn)楸緳C(jī)的防火墻關(guān)閉了ICMP回顯功能,只要把這回顯功能打開(kāi)就行了，解決辦法是：
以管理員身份運(yùn)行CMD,輸入以下命令 netsh firewall set icmpsetting 8 注: netsh firewall set icmpsetting 8 – 開(kāi)啟ICMP回顯
1.5 簡(jiǎn)單使用
1>使用navicat連接數(shù)據(jù)庫(kù):
2> 連接后，右鍵新建數(shù)據(jù)庫(kù)，在新的數(shù)據(jù)庫(kù)上新建表格
3>客戶端連接數(shù)據(jù)庫(kù)代碼
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();
//use
int 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文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-443168.html
/*
* 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->ConnectionString = "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, "數(shù)據(jù)庫(kù)連接錯(cuò)誤:%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;
//addTime
SYSTEMTIME 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;
}
//addTime
char 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.6 結(jié)果展示
文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-443168.html
到了這里，關(guān)于常用數(shù)據(jù)庫(kù)之sql server的使用和搭建的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
