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)
    }
}

　　#执行结果：

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)
    }
}

执行结果：

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