SQL Server Express
来自维基百科,自由的百科全书
来自维基百科,自由的百科全书
Microsoft SQL Server Express是微軟公司所開發的關聯式資料庫產品SQL Server的免費下載版本,可自由下載、分發(需經註冊)及使用。這個版本的設計是專門為嵌入式系統或較小型的程式而設計。本產品可遡源至過往隨同SQL Server 2000軟件分發的Microsoft Database Engine (MSDE),用以讓程式開發者在其測試其程式時建立一個系統需求較低的測試環境。自SQL Server 2005開始,本產品改用現時的名稱。
它繼承了多數的SQL Server功能與特性,像是Transact-SQL、SQL CLR等,相當適合使用在小型的網站,或者是小型的桌上型應用程式,它也可以和 SQL Server 整合,作為資料庫複製(Replication)的訂閱端。
SQL Server Express提供了付費的完整版本的多項功能[3],然而其技術限制使之無法適用於大規模的部署。這兩個版本的差異在於:
MSDE有一個並行工作負債監視器並限制在少量用戶並行訪問,SQL Server Express沒有這個並行限制[8]。
SQL Server Express包括了幾個資料庫管理的圖形化使用者介面工具:
MSDE 沒有管理介面
SQL Server Express不包括全功能標準版的幾個工具:
SQL Server Express的各版本是可以遠端連接的,但需要用戶做一定的組態。
版本號碼 | 發行日期 | 主流支援完結日期 | 延伸支援完結日期 | 支援作業系統 |
---|---|---|---|---|
SQL Server 2005 Express Edition | 2005-11-07[10] | 2011-04-12[11] | 2016-04-12[11] | Windows 2000 Service Pack 4, Windows XP Service Pack 2, Windows Server 2003 Service Pack, Windows 7 Service Pack 1 (only SQL Server 2005 Express Edition SP4)[12] |
SQL Server 2008 Express | 2009-02-08[13] | 2014-07-08[14] | 2019-07-09[14] | Windows XP Service Pack 2, Windows XP Service Pack 3, Windows Vista, Windows Vista Service Pack 1, Windows Server 2003 Service Pack 2, Windows Server 2008[15] |
SQL Server 2008 R2 Express | 2010-04-16[16] | 2014-07-08[17] | 2019-07-09[17] | Windows XP, Windows Vista, Windows 7, Windows 8, Windows Server 2003, Windows Server 2008, Windows Server 2008 R2, Windows Server 2012, Windows Server 2012 R2[18] |
SQL Server 2012 Express | 2012-05-14[19] | 2017-07-11[20] | 2022-07-12[20] | Windows Vista Service Pack 2, Windows 7, Windows 7 Service Pack 1, Windows 8, Windows 8.1, Windows Server 2008,[21] Windows Server 2008 R2, Windows Server 2008 R2 SP1, Windows Server 2012, Windows Server 2012 R2[22] |
SQL Server 2014 Express | 2014-04-01[23] | 2019-07-09[24] | 2024-07-09[24] | Windows 7 Service Pack 1, Windows 8, Windows 8.1, Windows 10, Windows Server 2008 SP2,[25] Windows Server 2008 R2 SP1, Windows Server 2012, Windows Server 2012 R2[26] |
SQL Server 2016 Express | 2016-06-01[27] | 2021-07-13[28] | 2026-07-14[28] | Windows 8, Windows 8.1, Windows 10, Windows Server 2012, Windows Server 2012 R2, Windows Server 2016 |
SQL Server 2017 Express | 2017-09-29[29] | 2022-10-11[30] | 2027-10-12[30] | Windows 8, Windows 8.1, Windows 10, Windows Server 2012, Windows Server 2012 R2, Windows Server 2016*, Red Hat Enterprise Linux 7.3 or 7.4, SUSE Enterprise Linux Server v12 SP2, Ubuntu 16.04LTS, Docker Engine 1.8+ (on Windows, Mac, or Linux) |
SQL Server Express 2017 或 2019,SQL Server Express有三款安裝版本:
SQL Server包括Express版本,有3種到資料庫伺服器的連接(connection)方式:
其中後兩種需要在SQL Server Configuration Manager軟件中,SQL Server Network Configuration下的「Protocols for SQLEXPRESS」,把Nameed Pipes與TCP/IP手工置為Enable。對於第三種,還需要在上述的組態時,在TCP/IP Properties的IP Addresses中把IPALL的「TCP Port」設為1433.
$sqlcmd -S thisisaserver.com -U username Password: 1> SELECT name FROM SYSOBJECTS WHERE xtype = 'U' 2> GO name -------- EquitySecMstr (1 rows affected) 1>
LocalDB的全稱是Microsoft SQL Server Express LocalDB。
從Microsoft下載的SqlLocalDB.msi約54MB。也可以使用Microsoft SQL Server Express的安裝程式,在第一頁選擇第3項「Download Media」,隨後在下一頁選擇第3項「localDB(54MB)」即可下載。
命令列工具SqlLocalDB,用於建立和管理LocalDB的實例[32]。
C:\> SqlLocalDb info
#输出默认实例名字:MSSQLLocalDB
#创建一个实例(相当于一个server):
C:\> SqlLocalDb create "MyInstance"
#输出:LocalDB instance "MyInstance" created with version 15.0.2000.5.
C:\> sqllocaldb info "MyInstance"
#输出:
#Name: MyInstance
#Version: 15.0.2000.5
#Shared name:
#Owner: MyDomainName\MyUserName
#Auto-create: No
#State: Stopped
#Last start time: 2022/3/9 19:04:41
#Instance pipe name:
C:\> sqllocaldb start "MyInstance"
#输出: LocalDB instance "MyInstance" started.
#类似的,可以有:
#C:\> SqlLocalDb stop "MyInstance"
#C:\> SqlLocalDb delete "MyInstance"
系統資料庫檔案儲存於本地AppData路徑下,通常為C:\Users\<user>\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\
。
LocalDB 是在Windows當前用戶的用戶行程中執行。而SQL Server Express是作為Windows Service執行。所以,LocalDB的數據儲存是Windows用戶私有的。在Visual Studio啟動IIS Express也是當前用戶的用戶行程執行,所以可以訪問LocalDB。[33]
把私有的數據儲存變為共用實例。例如,原儲存為「(localdb)\v11.0」:
sqllocaldb share v11.0 IIS_DB
現在為公共名字IIS_DB。該電腦所有用戶均可以用「(localdb)\.\IIS_DB」訪問該實例。注意,名字中的字首「\.」表示這是共用實例名字。即其儲存的檔案的路徑不會放在特定用戶的documents目錄中。連接字串可為:
Data Source=(localdb)\.\IIS_DB;Initial Catalog=OldFashionedDB;Integrated Security=True
在Visual Studio中通過 SQL Server Object Explorer 連接上述localDB實例,在query窗口執行下述指令碼可以授予「」帳號數據實例的管理員權限:
create login [IIS APPPOOL\ASP.NET v4.0] from windows; exec sp_addsrvrolemember N'IIS APPPOOL\ASP.NET v4.0', sysadmin
C:\>sqlcmd -S "(localdb)\MyInstance"
就可以在互動環境下執行查詢:
SELECT @@VERSION;
GO
CREATE DATABASE blat;
GO
USE blat;
GO
CREATE TABLE dbo.splunge(Mort int);
GO
INSERT dbo.Splune(Mort) VALUES(1);
SELECT * FROM dbo.splunge;
GO
Server Name使用(localdb)\MyInstance
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace Connection
{
class Program
{
static void Main(string[] args)
{
//构造连接字符串
SqlConnectionStringBuilder connStr = new SqlConnectionStringBuilder();
connStr.DataSource = @".\SQLEXPRESS";
connStr.InitialCatalog = "master";
connStr.IntegratedSecurity = true;
SqlConnection conn = new SqlConnection();//创建连接对象
conn.ConnectionString = connStr.ConnectionString;//设置连接字符串
conn.Open();//打开连接
if(conn.State == ConnectionState.Open)
{
Console.WriteLine("Database is linked.");
Console.WriteLine("\nDataSource:{0}",conn.DataSource);
Console.WriteLine("Database:{0}",conn.Database);
Console.WriteLine("ConnectionTimeOut:{0}",conn.ConnectionTimeout);
}
conn.Close();//关闭连接
conn.Dispose();//释放资源
if(conn.State == ConnectionState.Closed)
{
Console.WriteLine("\nDatabase is closed.");
}
Console.Read();
}
}
}
對於較高版本,如SQL Server Express大於版本2010的,可以通過在Visual Studio,依次點擊「視圖」(選單欄)->「伺服器檔案總管(Server Explorer)」,進入「伺服器檔案總管」窗口。右鍵點擊「數據連接(Data Connection)」,在彈出的上下文選單中選擇「添加連接(Add Connection)」,在彈出的窗口中將Data Source選擇為Microsoft SQL Server,「伺服器名」設置為(LocalDb)\MSSQLLocalDB,附加伺服器檔案為具體的mdf檔案並點擊確定。此時,通過Microsoft SQL Server(SQL Client)方式訪問指定的mdf檔案。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApp
{
class Program
{
static void Main(string[] args)
{
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection();
connection.ConnectionString =
@"Data Source=(localdb)\MSSQLLocalDB;AttachDbFilename=D:\apps\Documents\NORTHWND.mdf;Integrated Security = True;Connect Timeout = 30;";
//@"Data Source=(localdb)\MyInstance;InitialCatalog =blat;Integrated Security = True;Connect Timeout = 30;";
connection.Open();
if (connection.State == ConnectionState.Open)
{
Console.WriteLine("Database is linked.");
Console.WriteLine("DataSource:{0}", connection.DataSource);
Console.WriteLine("Database:{0}", connection.Database);
Console.WriteLine("ConnectionTimeOut:{0}", connection.ConnectionTimeout);
}
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Employees", connection);
DataTable table = new DataTable();
adapter.Fill(table);
var result = (from row
in table.Rows.Cast<DataRow>()
select
new { ID = (int)row[0], LastName = (string)row[1], FirstName = (string)row[2] });
result.ToList().ForEach(x => Console.WriteLine($"{x.ID},{x.LastName},{x.FirstName}"));
}
}
}
對於較低版本,如SQL Server Express 2005的mdf檔案,可以直接用如下connection string打開訪問(可能需要administrator權限):
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApp
{
class Program
{
static void Main(string[] args)
{
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection();
connection.ConnectionString =
@"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\apps\Documents\NORTHWND.mdf;Integrated Security = True;Connect Timeout = 30;";
connection.Open();
if (connection.State == ConnectionState.Open)
{
Console.WriteLine("Database is linked.");
Console.WriteLine("DataSource:{0}", connection.DataSource);
Console.WriteLine("Database:{0}", connection.Database);
Console.WriteLine("ConnectionTimeOut:{0}", connection.ConnectionTimeout);
}
}
}
}
需要安裝pymssql包
from sqlalchemy import create_engine
import pandas as pd
user = 'username'
pwd = 'password'
url = 'thisisaserver.com'
port = 1433
db = 'database'
engine = create_engine(f'mssql+pymssql://{user}:{pwd}@{url}:{port}/{db}')
sql_stmt = "SELECT name FROM SYSOBJECTS WHERE xtype = 'U'"
pd.read_sql_query(sql_stmt, engine)
Seamless Wikipedia browsing. On steroids.
Every time you click a link to Wikipedia, Wiktionary or Wikiquote in your browser's search results, it will show the modern Wikiwand interface.
Wikiwand extension is a five stars, simple, with minimum permission required to keep your browsing private, safe and transparent.