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)
}
执行结果：
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)
}
}
执行结果：
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
