Go语言之进阶篇mysql增 删 改 查

一、mysql操作基本语法

1、创建名称nulige的数据库

CREATE DATABASE nulige DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

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

执行结果:

Go语言之进阶篇mysql增 删 改 查

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

执行结果:

Go语言之进阶篇mysql增 删 改 查

2.3、改(更新)

原数据:

Go语言之进阶篇mysql增 删 改 查

更新数据示例:

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

Go语言之进阶篇mysql增 删 改 查

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

执行结果:

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

  

上一篇:python基础中的四大天王-增-删-改-查


下一篇:MongoDB增 删 改 查