常用数据库之sql server的使用和搭建

1.1 介绍

关系型数据库，SQLServer是由微软公司开发的一种关系型据库管理系统，它已广泛用于电子商务、银行、保险、电力等行业。SQLServer提供了对XML和Internet标准的支持，具有强SEO靠我大的、灵活的、基于Web的应用程序管理功能。

1.2 优缺点

优点：

1>通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

2>通过创建索引可以大大加快数据的检索速度

3>通过使用索引，可以在查询的过SEO靠我程中，使用优化隐藏器，提高系统的性能。

4>在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间

缺点：

1>只能运行在windows平台，平台单一不具备开放性

2>并行实施与共存模型不SEO靠我成熟，难以处理大规模的数据，伸缩性有限

3>当用户连接多时性能会变得很差并且不稳定

4>客户端支持应用模式，仅支持C/S模式

1.3 服务器安装

1.3.1 安装软件：

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

2> 选择自定义类型进行安装：

3> 选择语言和安装位置,点击安SEO靠我装

4>安装成功后出现以下界面，选择安装，选择全新的SQLServer 独立安装或向现有安装添加功能

5>选择指定可用版本Developer，点击下一步

6>更新选择，可点可不点

7>安装执行完毕后继续下一步SEO靠我

8>功能选择中，可以不勾选R、Python、java，其他都勾选后，点击下一步

9>选择默认实例，点击下一步，下一步

10>选择混合模式，设置密码，和添加当前用户，然后点击下一步

11>选择表格模式，添加当SEO靠我前用户后，点击下一步

12>默认配置无需更改，点击下一步，下一步

13>Distributed Replay的访问权限,选择的是当前用户，点击下一步，下一步

14>脱机安装Microsoft机器学习服务器组SEO靠我件

将页面4个蓝色链接分别复制到浏览器，下载4个压缩包，将4个压缩包放在同一个文件下，无需解压，然后把每个压缩包的名字里的1033替换成2052

以上操作都ok后，接下来就可以回到sql sever安装界SEO靠我面中选择包含4个压缩包的文件目录了，就会出现下一步按钮了,我们就可以继续点击下一步

15>进入准备安装界面，点击安装，耐心等待安装完成

17>下载SQL Server Management Studio软SEO靠我件，用于数据库的连接与管理，下载地址：https://aka.ms/ssmsfullsetup，安装后，进入连接

18>连接后，右键弹出界面点击属性

19>在属性页面里，选择安全性，选择SQL ServeSEO靠我r和windows身份验证模式，然后点击确定

20>在安全性-》登录名-sa，右键点击，弹出属性界面，在常规界面上确认密码

21>属性界面，在状态栏，上勾选授予和启用

22>设置允许远程连接，打开sql sSEO靠我erver congfiguration Manager，启用TCP/IP

23>右键TCP/IP可以打开属性界面，检查和配置IP、端口

24>都设置好后，可以在SQL Server ManagementSEO靠我 Studio重启，也可以在sql server congfiguration Manager的重启第三个服务

1.4 简单使用

1>使用navicat连接数据库:

2> 连接后，右键新建数据库，在新的数据SEO靠我库上新建表格

3>客户端连接数据库代码

database.h

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

database.cpp

/* * Filename: DataBase.cpp* Modify: ybLin* Description: database* **SEO靠我*****************************************************/#ifdef WIN32 #include <time.h> SEO靠我 #include <Windows.h> #include <io.h> #endif#include "DataBase.h" #include <SEO靠我time.h> #include <stdio.h> #include <Windows.h> #include <time.h> #iSEO靠我nclude <basic/myThread.h>int SwitchPath(char* target, const char* src) {while (*src != \0) {SEO靠我if (*src == \\)*target++ = *src;*target++ = *src++;}*target = \0;return 0; }CDataBase::CDataSEO靠我Base(const char* hostIP) {m_nCheckModel = false;m_pDbMutex = my_mutex_init();memset(m_sDataBSEO靠我ase, 0, sizeof(m_sDataBase));memset(m_sHostIp, 0, sizeof(m_sHostIp));m_nHostPort = 0;memset(m_sAccouSEO靠我nt, 0, sizeof(m_sAccount));memset(m_sPwd, 0, sizeof(m_sPwd));m_bInitOk = false; }CDataBase::SEO靠我~CDataBase() {if (m_bInitOk) Free();my_mutex_destroy(m_pDbMutex); }void CDataBase::USEO靠我pdateDB() {char sql[1024] = {0};snprintf(sql, 1023, "if not exists(select * from syscolumns SEO靠我where id=object_id(sninfo) and name=model) ALTER table sninfo add model varchar(30)");DataWrite(sql)SEO靠我;char sql2[1024] = {0};snprintf(sql2, 1023, "if not exists(select * from syscolumns where id=object_SEO靠我id(sninfo) and name=model2) ALTER table sninfo add model2 varchar(30)");DataWrite(sql2); }boSEO靠我ol CDataBase::Init(const char* hostIP, const char* dataBase, int hostPort, const char* user,SEO靠我 const char* pwd) {snprintf(m_sHostIp, 255, "%s", hostIP);m_nHostPort = hostPort;snprintf(m_SEO靠我sDataBase, 255, "%s", dataBase);snprintf(m_sAccount, 255, "%s", user);snprintf(m_sPwd, 255, "%s", pwSEO靠我d);return InitDB(); }bool CDataBase::InitDB() {if (m_bInitOk)return false;HRESULT hrSEO靠我;try{my_print("CDataBase::InitDB Start.");char sConnectStr[256] = {0};snprintf(sConnectStr, sizeof(sSEO靠我ConnectStr)-1, "pConn-&gt;ConnectionString = &quot;driver={SQL Server};Server=%s,%d;DATABASE=%s;", mSEO靠我_sHostIp, m_nHostPort, m_sDataBase);hr = m_pConnection.CreateInstance("ADODB.Connection");if(SUCCEEDSEO靠我ED(hr)){m_pConnection->ConnectionTimeout = 5;hr = m_pConnection->Open(sConnectStr, m_sAccount, m_sPwSEO靠我d, 0);}my_print("yibin test CDataBase::InitDB Success.");}catch(_com_error e){char sErrorMsg[256];snSEO靠我printf(sErrorMsg, sizeof(sErrorMsg)-1, "数据库连接错误:%s", e.Description());my_print(sErrorMsg);return FALSEO靠我SE;} m_pADOSet.CreateInstance(__uuidof(Recordset));m_pADOSetAuto.CreateInstance(__uuidof(Recordset))SEO靠我;m_bInitOk = true;return m_bInitOk; }bool CDataBase::Free() {if(m_bInitOk){if(adStatSEO靠我eOpen == m_pConnection->State) m_pConnection->Close();m_pConnection.Release();if(adStateOpen == m_pASEO靠我DOSet->State) m_pADOSet->Close();m_pADOSet.Release();if(adStateOpen == m_pADOSetAuto->State) m_pADOSSEO靠我etAuto->Close();m_pADOSetAuto.Release();m_bInitOk = false;} else {return false;}return true; SEO靠我 }bool CDataBase::ADOExecute(const char* sql) {if ( m_pADOSet->State){m_pADOSet->Close();}trSEO靠我y{m_pADOSet->Open(sql, m_pConnection.GetInterfacePtr(), adOpenStatic, adLockOptimistic, adCmdUnknownSEO靠我);return true;}catch(_com_error &e){char sErrorMsg[256];snprintf(sErrorMsg, sizeof(sErrorMsg)-1, "ExSEO靠我ecute SQL:%s ERROR:%s", sql, e.ErrorMessage());my_print(sErrorMsg);return false;}return true; SEO靠我 }int CDataBase::DataWrite(const char* sql) {int nRet = 0;char str[1024] = {0};my_mutex_locSEO靠我k(m_pDbMutex);if (!m_bInitOk) {my_mutex_unlock(m_pDbMutex);return -1;}SwitchPath(str, sql);if (ADOExSEO靠我ecute(str) != true) {Free();InitDB();if (m_bInitOk && ADOExecute(str) == true) {} else {nRet = -1;}}SEO靠我my_mutex_unlock(m_pDbMutex);return nRet; }//use int CDataBase::InsertMacInfo(_mac_inSEO靠我fo* _info) {char sql[1024] = {0};snprintf(sql, 1023, "select Mac,SNCode,AddTime from IPCInfoSEO靠我 WHERE Mac = %s", _info->sMacId);DataWrite(sql);if(!m_pADOSet->adoEOF)return -10;//addTimeSYSTEMTIMESEO靠我 sysTime = {0};GetLocalTime(&sysTime);_snprintf(_info->sAddTime, sizeof(_info->sAddTime)-1, "%d-%02dSEO靠我-%02d %02d:%02d:%02d", sysTime.wYear, sysTime.wMonth, sysTime.wDay, sysTime.wHour, sysTime.wMinute, SEO靠我sysTime.wSecond);snprintf(sql, 1023, "INSERT INTO IPCInfo (Materiel, OrderNo, Model, Lens, SNCode, MSEO靠我ac, AddTime) VALUES(%s, %s, %s, %s, %s, %s, %s)", _info->sMateriel, _info->sOrderNo, _info->sModel, SEO靠我_info->sLens, _info->sSNCode, _info->sMacId, _info->sAddTime);return DataWrite(sql); }int CDSEO靠我ataBase::GetMaxMacInfo(_mac_info* _info) {char sql[1024] = {0};snprintf(sql, 1023, "select MSEO靠我ac,SNCode,AddTime from IPCInfo order by Mac desc");unsigned mac_calc[6] = {0};if (DataWrite(sql) != SEO靠我0) {my_print("DataWrite Fail \n");return -2;} else {_variant_t Holder;if(!m_pADOSet->adoEOF) {HolderSEO靠我 = m_pADOSet->GetCollect("Mac");snprintf(_info->sMacId, sizeof(_info->sMacId)-1, Holder.vt==VT_NULL?SEO靠我"":(char*)(_bstr_t)Holder);Holder = m_pADOSet->GetCollect("SNCode");snprintf(_info->sSNCode, sizeof(SEO靠我_info->sSNCode)-1, Holder.vt==VT_NULL?"":(char*)(_bstr_t)Holder);Holder = m_pADOSet->GetCollect("AddSEO靠我Time");snprintf(_info->sAddTime, sizeof(_info->sAddTime)-1, Holder.vt==VT_NULL?"":(char*)(_bstr_t)HoSEO靠我lder);}}if(sscanf(_info->sMacId, "%02X%02X%02X%02X%02X%02X", &mac_calc[5], &mac_calc[4], &mac_calc[3SEO靠我], &mac_calc[2], &mac_calc[1], &mac_calc[0]) != 6){my_print("sscanf Fail \n");return -3;}//addTimechSEO靠我ar szTime[32] = {0};char AddDate[32] = {0};char AddTime[32] = {0};SYSTEMTIME sysTime = {0};GetLocalTSEO靠我ime(&sysTime);snprintf(szTime, sizeof(szTime)-1, "%d-%02d-%02d", sysTime.wYear, sysTime.wMonth, sysTSEO靠我ime.wDay);time_t iTime = time(NULL);char sTime[32] = {0};struct tm* ptm = localtime(&iTime);_snprintSEO靠我f(sTime, sizeof(sTime)-1, "%4d-%02d-%02d", ptm->tm_year + 1900,ptm->tm_mon + 1, ptm->tm_mday);sscanfSEO靠我(_info->sAddTime, "%s %s", AddDate, AddTime);char SNPre[32] = {0};char SNDate[32] = {0};if(strcmp(szSEO靠我Time, AddDate) == 0){strncpy (SNPre, _info->sSNCode, 3);int year;int month;int day;char date[32] = {SEO靠我0};sscanf(AddDate, "%d-%2d-%2d", &year, &month, &day);_snprintf(date, sizeof(date)-1, "%d%02d%02d", SEO靠我year, month, day);int snNum = atof(_info->sSNCode+11)+1;memset(_info->sSNCode, 0, sizeof(_info->sSNCSEO靠我ode));_snprintf(_info->sSNCode, sizeof(_info->sSNCode)-1, "%s%s%04d", SNPre, date, snNum);}else{strnSEO靠我cpy (SNPre, _info->sSNCode, 3);snprintf(SNDate, sizeof(szTime)-1, "%d%02d%02d", ptm->tm_year + 1900,SEO靠我ptm->tm_mon + 1, ptm->tm_mday);snprintf(_info->sSNCode, sizeof(_info->sSNCode), "%s%s%s",SNPre, SNDaSEO靠我te, "0001");}_snprintf(_info->sMacId, sizeof(_info->sMacId)-1, "%02X%02X%02X%02X%02X%02X",mac_calc[5SEO靠我], mac_calc[4], mac_calc[3], mac_calc[2], mac_calc[1], mac_calc[0]);return 0; }

1.5 结果展示