golang.mysql

一、mysql操作基本语法

1、创建名称nulige的数据库 

CREATE DATABASE nulige DEFAULT CHARSET utf8 COLLATE utf8_general_ci;<br><br>use nulige

2、建表，Id自增

create table user_info(

    nid int not null auto_increment primary key,

    username varchar(20),

    departname varchar(20),

    create_time timestamp default "0000-00-00 00:00:00"

);　

3、往表中插入数据

INSERT INTO user_info (username,departname,create_time) VALUES ("nulige","行政部","2019-1-13 12:23:00");

INSERT INTO user_info (username,departname,create_time) VALUES ("jojo","财务部","2019-1-14 1:23:00");

INSERT INTO user_info (username,departname,create_time) VALUES ("huhui","销售部","2019-1-15 2:23:00");

4、查询表中内容

mysql> select * from user_info;

+-----+----------+------------+---------------------+

| nid | username | departname | create_time         |

+-----+----------+------------+---------------------+

|   1 | nulige   | 行政部   | 2019-01-13 12:23:00 |

|   2 | jojo      | 财务部    | 2019-01-14 01:23:00 |

|   3 | huhui    | 销售部    | 2019-01-15 02:23:00 |

+-----+----------+------------+---------------------+

3 rows in set (0.00 sec)　

5、删除数据库

mysql> drop database nulige;

Query OK, 1 row affected (0.01 sec)

6、清空表中数据，保留表结构

mysql> delete from user_info;

Query OK, 5 rows affected (0.00 sec)

　　

 二、mysql的增，删，改，查

2.1、增  (备注：往表中插入一条数据)

package main

 

import (

    "database/sql"

    "fmt"

 

    //调用mysql初始化包

    _ "github.com/go-sql-driver/mysql"

)

 

func main() {

    db, err := sql.Open("mysql", "root:qwe!23@tcp(127.0.0.1:3306)/nulige?charset=utf8")

    if err != nil {

        panic(err)

    }

 

    //fmt.Println(db.Ping())  检查是否连接成功数据库

    stmt, err := db.Prepare("INSERT user_info SET username=?,departname=?,create_time=?")

    if err != nil {

        fmt.Println(err)

        return

    }

    res, err := stmt.Exec("eeee", "采购部", "2019-1-29")

    id, err := res.LastInsertId()

    if err != nil {

        panic(err)

    }

 

    fmt.Println(id)

}

执行结果：

2.2、删

 示例：

package main

 

import (

    "database/sql"

    "fmt"

 

    //调用mysql初始化包

    _ "github.com/go-sql-driver/mysql"

)

 

func main() {

    db, err := sql.Open("mysql", "root:qwe!23@tcp(127.0.0.1:3306)/nulige?charset=utf8")

    if err != nil {

        panic(err)

    }

 

    stmt, err := db.Prepare("DELETE FROM user_info WHERE nid=?")

    check(err)

 

    res, err := stmt.Exec(5)

    check(err)

 

    num, err := res.RowsAffected()

    check(err)

 

    fmt.Println(num)

    stmt.Close()

}

 

func check(err error) {

    if err != nil {

        fmt.Println(err)

        panic(err)

    }

}

执行结果：

 

2.3、改（更新）

 原数据：

更新数据示例：

package main

 

import (

    "database/sql"

    "fmt"

 

    //调用mysql初始化包

    _ "github.com/go-sql-driver/mysql"

)

 

func main() {

    db, err := sql.Open("mysql", "root:qwe!23@tcp(127.0.0.1:3306)/nulige?charset=utf8")

    if err != nil {

        panic(err)

    }

 

    //fmt.Println(db.Ping())  检查是否连接成功数据库

    stmt, err := db.Prepare("update user_info SET username=? where nid=?")

    if err != nil {

        fmt.Println(err)

        return

    }

    res, err := stmt.Exec("dddd", 5)

    id, err := res.RowsAffected()

    if err != nil {

        panic(err)

    }

 

    fmt.Println(id)

}

执行结果：  把username:cccc,修改成dddd

 

2.4、查

示例 ： 查id=? 的一条记录

package main

 

import (

    "database/sql"

    "fmt"

 

    //调用mysql初始化包

    _ "github.com/go-sql-driver/mysql"

)

 

func main() {

    db, err := sql.Open("mysql", "root:qwe!23@tcp(127.0.0.1:3306)/nulige?charset=utf8")

    if err != nil {

        panic(err)

    }

 

    rows, err := db.Query(" SELECT * FROM user_info where nid=3")

    if err != nil {

        panic(err)

    }

 

    for rows.Next() {

        var nid int

        var username string

        var department string

        var create_time string

        err = rows.Scan(&nid, &username, &department, &create_time)

        fmt.Println(nid, username, department, create_time)

    }

}

　　#执行结果：

1	3 huhui 销售部 2019-01-15 02:23:00

　　

 示例2：查看所有记录

package main

 

import (

    "database/sql"

    "fmt"

 

    //调用mysql初始化包

    _ "github.com/go-sql-driver/mysql"

)

 

func main() {

    db, err := sql.Open("mysql", "root:qwe!23@tcp(127.0.0.1:3306)/nulige?charset=utf8")

    if err != nil {

        panic(err)

    }

 

    rows, err := db.Query(" SELECT * FROM user_info")

    if err != nil {

        panic(err)

    }

 

    for rows.Next() {

        var nid int

        var username string

        var department string

        var create_time string

        err = rows.Scan(&nid, &username, &department, &create_time)

        fmt.Println(nid, username, department, create_time)

    }

}

执行结果：

1 2 3 4 5 6

1 nulige 行政部 2019-01-13 12:23:00 2 jojo 财务部 2019-01-14 01:23:00 3 huhui 销售部 2019-01-15 02:23:00 4 aaaa 销售部 2019-01-15 02:23:00 5 dddd 销售部 2019-01-15 02:23:00 6 eeee 采购部 2019-01-29 00:00:00