标签：00 err 23 fmt sql 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) } }
|
执行结果：
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
|
标签：00,
err,
23,
fmt,
sql,
golang,
mysql
From： https://blog.51cto.com/u_13912516/5938643