---
# System prepended metadata

title: MSSQL

---

docker run -d \
--name=sql1 \
-e 'ACCEPT_EULA=Y' \
-e 'SA_PASSWORD=yourpassword' \
-p 1433:1433 \
--log-opt max-size=10m \
--log-opt max-file=3 \
mcr.microsoft.com/mssql/server:2019-latest
進入容器：sudo docker exec -it sql1 "bash"
以SA身份進入sqlcmd:/opt/mssql-tools/bin/sqlcmd -S localhost -U SA

停止容器：docker stop sqlserver
刪除容器：docker rm sqlserver
建立測試資料庫:CREATE DATABASE TestDB;
查詢以傳回您伺服器上所有資料庫的名稱：SELECT Name from sys.databases;
**將內容切換至 TestDB 資料庫：USE TestDB;「確保位置下操作指令前必須使用」**
確認用戶以存在資料庫中：SELECT name FROM sys.database_principals WHERE type = 'S' AND name = 'yuyu';
确保你已连接到你希望新增用户的数据库:CREATE USER yuyu FOR LOGIN yuyu;
授权用户访问数据库:GRANT SELECT, INSERT, UPDATE, DELETE ON schemaName.tableName TO yuyu;

創建架構：CREATE SCHEMA A3SmartTag;
授予架構權限：GRANT CONTROL ON SCHEMA::A3SmartTag TO yuyu;

建立資料表：CREATE TABLE dbo.A3_SMARTTAG_DECAY_DATA (id INT,SLOPE_S INT,SLOPE_X INT,SLOPE_Y INT,SLOPE_Z INT, SENSOR_MAC VARCHAR(30), quantity INT,UPDATE_TIME DATE);
針對資料表授權：GRANT SELECT ON dbo.A3_SMARTTAG_DECAY_DATA TO yuyu;

创建登录名：CREATE LOGIN yuyu WITH PASSWORD = 'Tp6ej04m6';
一个数据库中选择数据并直接创建一个新表到另一个数据库中：
USE SourceDatabase;
SELECT * INTO TestDB.dbo.A3_SMARTTAG_DECAY_DATA FROM dbo.A3_SMARTTAG_DECAY_DATA;
GO
顯示schema:SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'A3_SMARTTAG_DECAY_DATA' AND TABLE_SCHEMA = 'dbo';
import csv:
BULK INSERT dbo.DIE_INFO
FROM '/home/DIE_INFO2.csv'
WITH (
    FORMATFILE = '/home/formatfile.xml',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2  -- 如果文件包含列名行，则从第二行开始
);
顯示所有資料表：SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

reference：https://learn.microsoft.com/zh-tw/sql/linux/quickstart-install-connect-docker?view=sql-server-ver16&tabs=cli&pivots=cs1-bash
