docker run -d --name mssql --hostname mssql \
-e "ACCEPT_EULA=Y" \
-e "MSSQL_SA_PASSWORD=YourPassword123" \ # 配置自己的密码
-e "MSSQL_AGENT_ENABLED=True" \ # 启动agent
-p 1433:1433 \
mcr.microsoft.com/mssql/server:2019-latest
docker exec -it mssql "bash"
/opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "YourPassword123" -No # -No 关闭ssl认证
创建基本表结构：
-- Create Database
CREATE DATABASE LegalManagementDB;
GO
USE LegalManagementDB;
-- Natural Persons Table
CREATE TABLE NaturalPersons (
IDNumber VARCHAR(18) PRIMARY KEY,
FullName NVARCHAR(50) NOT NULL,
Gender CHAR(1) CHECK (Gender IN ('M', 'F')),
Mobile VARCHAR(20),
Address NVARCHAR(200)
);
-- Business Entities Table
CREATE TABLE BusinessInfo (
OrganizationCode VARCHAR(50) PRIMARY KEY,
CompanyName NVARCHAR(100) NOT NULL,
Address NVARCHAR(200),
LegalRepName NVARCHAR(50),
LegalRepID VARCHAR(18) FOREIGN KEY REFERENCES NaturalPersons(IDNumber),
RegisteredCapital DECIMAL(18,2),
RegistrationDate DATE,
BusinessStatus NVARCHAR(50)
);
-- Case Details Table
CREATE TABLE CaseDetails (
CaseNumber VARCHAR(50) PRIMARY KEY,
CaseReason NVARCHAR(500),
FilingDate DATE,
ClosingDate DATE
);
-- Shareholders Table
CREATE TABLE Shareholders (
OrganizationCode VARCHAR(50) FOREIGN KEY REFERENCES BusinessInfo(OrganizationCode),
ShareholderName NVARCHAR(50),
ShareholderID VARCHAR(18) FOREIGN KEY REFERENCES NaturalPersons(IDNumber),
PRIMARY KEY (OrganizationCode, ShareholderName, ShareholderID)
);
-- Case Participants Table
CREATE TABLE CaseParticipants (
CaseNumber VARCHAR(50) FOREIGN KEY REFERENCES CaseDetails(CaseNumber),
ParticipantType VARCHAR(20) CHECK (ParticipantType IN ('Company', 'Organization', 'Individual')),
ParticipantID VARCHAR(50),
PRIMARY KEY (CaseNumber, ParticipantType, ParticipantID)
);
-- Index Optimization
CREATE INDEX idx_np_mobile ON NaturalPersons(Mobile);
CREATE INDEX idx_np_name ON NaturalPersons(FullName);
CREATE INDEX idx_business_status ON BusinessInfo(BusinessStatus);
CREATE INDEX idx_business_name ON BusinessInfo(CompanyName);
CREATE INDEX idx_case_filing_date ON CaseDetails(FilingDate);
CREATE INDEX idx_case_closing_date ON CaseDetails(ClosingDate);
CREATE INDEX idx_shareholder_name ON Shareholders(ShareholderName);
CREATE INDEX idx_participant_id ON CaseParticipants(ParticipantID);
-- Sample Test Data
INSERT INTO NaturalPersons (IDNumber, FullName, Gender, Mobile, Address) VALUES
('110101199001011234', 'Zhang San', 'M', '13800138000', '100 Main St, Beijing'),
('110101198502021357', 'Li Si', 'F', '13900139000', '200 Oak Rd, Shanghai'),
('11010119771212456X', 'Wang Wu', 'M', '13600136000', '300 Pine Ave, Guangzhou');
INSERT INTO BusinessInfo (OrganizationCode, CompanyName, Address, LegalRepName, LegalRepID, RegisteredCapital, RegistrationDate, BusinessStatus) VALUES
('123456789', 'ABC Corporation', '500 Tech Park, Shenzhen', 'Zhang San', '110101199001011234', 5000000.00, '2010-05-15', 'Active'),
('987654321', 'XYZ Ltd', '600 Business Center, Hangzhou', 'Li Si', '110101198502021357', 3000000.00, '2015-08-20', 'Inactive');
INSERT INTO CaseDetails (CaseNumber, CaseReason, FilingDate, ClosingDate) VALUES
('2023-CR-001', 'Contract Dispute', '2023-01-15', '2023-06-30'),
('2023-CR-002', 'Intellectual Property', '2023-03-10', NULL);
INSERT INTO Shareholders (OrganizationCode, ShareholderName, ShareholderID) VALUES
('123456789', 'Wang Wu', '11010119771212456X'),
('987654321', 'Zhang San', '110101199001011234');
INSERT INTO CaseParticipants (CaseNumber, ParticipantType, ParticipantID) VALUES
('2023-CR-001', 'Company', '123456789'),
('2023-CR-001', 'Individual', '110101198502021357'),
('2023-CR-002', 'Organization', '987654321');
开启cdc：
USE LegalManagementDB
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'NaturalPersons',
@role_name = NULL,
@supports_net_changes = 1
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'BusinessInfo',
@role_name = NULL,
@supports_net_changes = 1
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'CaseDetails',
@role_name = NULL,
@supports_net_changes = 1
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Shareholders',
@role_name = NULL,
@supports_net_changes = 1
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'CaseParticipants',
@role_name = NULL,
@supports_net_changes = 1
GO