在Ubuntu使用SQL Server创建Go应用程序的图文教程

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl Https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list | sudo tee /etc/apt/sources.list.d/mssql-server-2017.list

sudo apt-get update
sudo apt-get install mssql-server

sudo /opt/mssql/bin/mssql-conf setup

curl -O https://storage.googleapis.com/golang/go1.8.linux-amd64.tar.gz
tar xvf go1.8.linux-amd64.tar.gz
sudo chown -R root:root ./go
sudo mv go /usr/local

export GOPATH=$HOME/work
export PATH=$PATH:/usr/local/go/bin:$GOPATH/bin

sudo su
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

#Download appropriate package for the OS version
#Choose only ONE of the following, corresponding to your OS version

#Ubuntu 16.04
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

#Ubuntu 18.04
curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

#Ubuntu 19.10
curl https://packages.microsoft.com/config/ubuntu/19.10/prod.list > /etc/apt/sources.list.d/mssql-release.list

exit
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install msodbcsql17
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for UnixODBC development headers
sudo apt-get install unixodbc-dev

sqlcmd -S localhost -U sa -P yourpassword
1> # You're connected! Type your T-SQL statements here. Use the keyword 'GO' to execute each BATch of statements.

sqlcmd -S localhost -U sa -P yourpassword -Q "SELECT @@VERSION"

cd ~/

#Create Project Directory
mkdir SqlServerSample
cd SqlServerSample

# Get and install the SQL Server driver for Go
go get GitHub.com/denisenkom/go-mssqldb
go install github.com/denisenkom/go-mssqldb

sqlcmd -S 127.0.0.1 -U sa -P <你的> -Q "CREATE DATABASE SampleDB;"

package main

import (
    _ "github.com/denisenkom/go-mssqldb"
    "database/sql"
    "context"
    "log"
    "fmt"
)

// Replace with your own connection parameters
var server = "localhost"
var port = 1433
var user = "sa"
var password = "xxxxxx"

var db *sql.DB

func main() {
    var err error

    // Create connection string
    connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d",
        server, user, password, port)

    // Create connection pool
    db, err = sql.Open("sqlserver", connString)
    if err != nil {
        log.Fatal("Error creating connection pool: " + err.Error())
    }
    log.Printf("Connected!\n")

    // Close the database connection pool after program executes
    defer db.Close()

    SelectVersion()
}

// Gets and prints SQL Server version
func SelectVersion(){
    // Use background context
    ctx := context.Background()

    // Ping database to see if it's still alive.
    // Important for handling network issues and long queries.
    err := db.PinGContext(ctx)
    if err != nil {
        log.Fatal("Error pinging database: " + err.Error())
    }

    var result string

    // Run query and scan for result
    err = db.QueryRowContext(ctx, "SELECT @@version").Scan(&result)
    if err != nil {
        log.Fatal("Scan failed:", err.Error())
    }
    fmt.Printf("%s\n", result)
}

go run connect.go

CREATE SCHEMA TestSchema;
GO

CREATE TABLE TestSchema.Employees (
  Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  Name NVARCHAR(50),
  Location NVARCHAR(50)
);
GO

INSERT INTO TestSchema.Employees (Name, Location) VALUES
(N'jared', N'Australia'),
(N'Nikita', N'India'),
(N'Tom', N'Germany');
GO

SELECT * FROM TestSchema.Employees;
GO

sqlcmd -S 127.0.0.1 -U sa -P <你的> -d SampleDB -i ./CreateTestData.sql

CREATE SCHEMA TestSchema;

Executed in 0 ms
CREATE TABLE TestSchema.Employees (
  Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  Name NVARCHAR(50),
  Location NVARCHAR(50)
);

Executed in 0 ms
INSERT INTO TestSchema.Employees (Name, Location) VALUES
(N'Jared', N'Australia'),
(N'Nikita', N'India'),
(N'Tom', N'Germany');

Executed in 0 ms
SELECT * FROM TestSchema.Employees;
Id  Name    Location
--  ------  ---------
1   Jared   Australia
2   Nikita  India
3   Tom     Germany

3 row(s) returned

Executed in 1 ms

package main

import (
    _ "github.com/denisenkom/go-mssqldb"
    "database/sql"
    "context"
    "log"
    "fmt"
    "errors"
)

var db *sql.DB

var server = "localhost"
var port = 1433
var user = "sa"
var password = "你的"
var database = "SampleDB"

func main() {
    // Build connection string
    connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s;",
        server, user, password, port, database)

    var err error

    // Create connection pool
    db, err = sql.Open("sqlserver", connString)
    if err != nil {
        log.Fatal("Error creating connection pool: ", err.Error())
    }
    ctx := context.Background()
    err = db.PingContext(ctx)
    if err != nil {
        log.Fatal(err.Error())
    }
    fmt.Printf("Connected!\n")

    // Create employee
    createID, err := CreateEmployee("Jake", "United States")
    if err != nil {
        log.Fatal("Error creating Employee: ", err.Error())
    }
    fmt.Printf("Inserted ID: %d successfully.\n", createID)

    // Read employees
    count, err := ReadEmployees()
    if err != nil {
        log.Fatal("Error reading Employees: ", err.Error())
    }
    fmt.Printf("Read %d row(s) successfully.n", count)

    // Update from database
    updatedRows, err := UpdateEmployee("Jake", "Poland")
    if err != nil {
        log.Fatal("Error updating Employee: ", err.Error())
    }
    fmt.Printf("Updated %d row(s) successfully.\n", updatedRows)

    // Delete from database
    deletedRows, err := DeleteEmployee("Jake")
    if err != nil {
        log.Fatal("Error deleting Employee: ", err.Error())
    }
    fmt.Printf("Deleted %d row(s) successfully.\n", deletedRows)
}

// CreateEmployee inserts an employee record
func CreateEmployee(name string, location string) (int64, error) {
    ctx := context.Background()
    var err error

    if db == nil {
        err = errors.New("CreateEmployee: db is null")
        return -1, err
    }

    // Check if database is alive.
    err = db.PingContext(ctx)
    if err != nil {
        return -1, err
    }

    tsql := "INSERT INTO TestSchema.Employees (Name, Location) VALUES (@Name, @Location); select convert(bigint, SCOPE_IDENTITY());"

    stmt, err := db.Prepare(tsql)
    if err != nil {
        return -1, err
    }
    defer stmt.Close()

    row := stmt.QueryRowContext(
        ctx,
        sql.Named("Name", name),
        sql.Named("Location", location))
    var newID int64
    err = row.Scan(&newID)
    if err != nil {
        return -1, err
    }

    return newID, nil
}

// ReadEmployees reads all employee records
func ReadEmployees() (int, error) {
    ctx := context.Background()

    // Check if database is alive.
    err := db.PingContext(ctx)
    if err != nil {
        return -1, err
    }

    tsql := fmt.Sprintf("SELECT Id, Name, Location FROM TestSchema.Employees;")

    // Execute query
    rows, err := db.QueryContext(ctx, tsql)
    if err != nil {
        return -1, err
    }

    defer rows.Close()

    var count int

    // Iterate through the result set.
    for rows.Next() {
        var name, location string
        var id int

        // Get values from row.
        err := rows.Scan(&id, &name, &location)
        if err != nil {
            return -1, err
        }

        fmt.Printf("ID: %d, Name: %s, Location: %s\n", id, name, location)
        count++
    }

    return count, nil
}

// UpdateEmployee updates an employee's information
func UpdateEmployee(name string, location string) (int64, error) {
    ctx := context.Background()

    // Check if database is alive.
    err := db.PingContext(ctx)
    if err != nil {
        return -1, err
    }

    tsql := fmt.Sprintf("UPDATE TestSchema.Employees SET Location = @Location WHERE Name = @Name")

    // Execute non-query with named parameters
    result, err := db.ExecContext(
        ctx,
        tsql,
        sql.Named("Location", location),
        sql.Named("Name", name))
    if err != nil {
        return -1, err
    }

    return result.RowsAffected()
}

// DeleteEmployee deletes an employee from the database
func DeleteEmployee(name string) (int64, error) {
    ctx := context.Background()

    // Check if database is alive.
    err := db.PingContext(ctx)
    if err != nil {
        return -1, err
    }

    tsql := fmt.Sprintf("DELETE FROM TestSchema.Employees WHERE Name = @Name;")

    // Execute non-query with named parameters
    result, err := db.ExecContext(ctx, tsql, sql.Named("Name", name))
    if err != nil {
        return -1, err
    }

    return result.RowsAffected()
}

go run crud.go

cd ~/
mkdir SqlServerGormSample
cd SqlServerGormSample

# Get and install the SQL Server driver for Go
go get github.com/denisenkom/go-mssqldb
go install github.com/denisenkom/go-mssqldb

package main

import (
    "fmt"
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/mssql"
    "log"
)

var server = "localhost"
var port = 1433
var user = "sa"
var password = "你的"
var database = "SampleDB"

// Define a User model struct
type User struct {
    gorm.Model
    FirstName string
    LastName string
}

// Define a Task model struct
type Task struct {
    gorm.Model
    Title string
    DueDate string
    IsComplete bool
    UserID  uint
}

// Read and print all the tasks
func ReadAllTasks(db *gorm.DB){
    var users []User
    var tasks []Task
    db.Find(&users)

    for _, user := range users{
        db.Model(&user).Related(&tasks)
        fmt.Printf("%s %s's tasks:\n", user.FirstName, user.LastName)
        for _, task := range tasks {
            fmt.Printf("Title: %s\nDueDate: %s\nIsComplete:%t\n\n",
                            task.Title, task.DueDate, task.IsComplete)
        }
    }
}

// Update a task based on a user
func UpdateSomeonesTask(db *gorm.DB, userId int){
    var task Task
    db.Where("user_id = ?", userId).First(&task).Update("Title", "Buy donuts for Luis")
    fmt.Printf("Title: %s\nDueDate: %s\nIsComplete:%t\n\n",
                    task.Title, task.DueDate, task.IsComplete)
}

// Delete all the tasks for a user
func DeleteSomeonesTasks(db *gorm.DB, userId int){
    db.Where("user_id = ?", userId).Delete(&Task{})
    fmt.Printf("Deleted all tasks for user %d", userId)
}

func main() {
    connectionString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s",
                                        server, user, password, port, database)
    db, err := gorm.Open("mssql", connectionString)

    if err != nil {
        log.Fatal("Failed to create connection pool. Error: " + err.Error())
    }
    gorm.DefaultCallback.Create().Remove("mssql:set_identity_insert")
    defer db.Close()

    fmt.Println("Migrating models...")
    db.AutoMigrate(&User{})
    db.AutoMigrate(&Task{})

    // Create awesome Users
    fmt.Println("Creating awesome users...")
    db.Create(&User{FirstName: "Andrea", LastName: "Lam"})      //UserID: 1
    db.Create(&User{FirstName: "Meet", LastName: "Bhagdev"})    //UserID: 2
    db.Create(&User{FirstName: "Luis", LastName: "Bosquez"})    //UserID: 3

    // Create appropriate Tasks for each user
    fmt.Println("Creating new appropriate tasks...")
    db.Create(&Task{
        Title: "Do laundry", DueDate: "2017-03-30", IsComplete: false, UserID: 1})
    db.Create(&Task{
        Title: "Mow the lawn", DueDate: "2017-03-30", IsComplete: false, UserID: 2})
    db.Create(&Task{
        Title: "Do more laundry", DueDate: "2017-03-30", IsComplete: false, UserID: 3})
    db.Create(&Task{
        Title: "Watch TV", DueDate: "2017-03-30", IsComplete: false, UserID: 3})

    // Read
    fmt.Println("\nReading all the tasks...")
    ReadAllTasks(db)

    // Update - update Task title to something more appropriate
    fmt.Println("Updating Andrea's task...")
    UpdateSomeonesTask(db, 1)

    // Delete - delete Luis's task
    DeleteSomeonesTasks(db, 3)
}

go run orm.go

cd ~/mkdir SqlServerColumnstoreSamplecd SqlServerColumnstoreSample

WITH a AS (SELECT * FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a))
SELECT TOP(5000000)
ROW_NUMBER() OVER (ORDER BY a.a) AS OrderItemId
,a.a + b.a + c.a + d.a + e.a + f.a + g.a + h.a AS OrderId
,a.a * 10 AS Price
,CONCAT(a.a, N' ', b.a, N' ', c.a, N' ', d.a, N' ', e.a, N' ', f.a, N' ', g.a, N' ', h.a) AS ProductName
INTO Table_with_5M_rows
FROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h;

sqlcmd -S 127.0.0.1 -U sa -P <你的> -d SampleDB -i ./CreateSampleTable.sql

go get github.com/denisenkom/go-mssqldb
go install github.com/denisenkom/go-mssqldb

package main

import (
    _ "github.com/denisenkom/go-mssqldb"
    "database/sql"
    "context"
    "log"
    "fmt"
    "time"
)

var server = "localhost"
var port = 1433
var user = "sa"
var password = "你的"
var database = "SampleDB"

var db *sql.DB

// Delete an employee from database
func ExecuteAggregateStatement(db *sql.DB) {
    ctx := context.Background()

    // Ping database to see if it's still alive.
    // Important for handling network issues and long queries.
    err := db.PingContext(ctx)
    if err != nil {
        log.Fatal("Error pinging database: " + err.Error())
    }

    var result string

    // Execute long non-query to aggregate rows
    err = db.QueryRowContext(ctx, "SELECT SUM(Price) as sum FROM Table_with_5M_rows").Scan(&result)
    if err != nil {
        log.Fatal("Error executing query: " + err.Error())
    }

    fmt.Printf("Sum: %s\n", result)
}

func main() {
    // Connect to database
    connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s;",
                                server, user, password, port, database)
    var err error

    // Create connection pool
    db, err = sql.Open("sqlserver", connString)
    if err != nil {
        log.Fatal("Open connection failed:", err.Error())
    }
    fmt.Printf("Connected!\n")

    defer db.Close()

    t1 := time.Now()
    fmt.Printf("Start time: %s\n", t1)

    ExecuteAggregateStatement(db)

    t2 := time.Since(t1)
    fmt.Printf("The query took: %s\n", t2)
}

go run columnstore.go

sqlcmd -S localhost -U sa -P <你的> -d SampleDB -Q "CREATE CLUSTERED COLUMNSTORE INDEX Columnstoreindex ON Table_with_5M_rows;"

go run columnstore.go