forked from go/golangs_learn
You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
162 lines
4.6 KiB
162 lines
4.6 KiB
package main
|
|
|
|
import (
|
|
"database/sql"
|
|
"fmt"
|
|
|
|
_ "github.com/go-sql-driver/mysql"
|
|
)
|
|
|
|
func main() {
|
|
//打开数据库
|
|
//DSN数据源字符串:用户名:密码@协议(地址:端口)/数据库?参数=参数值
|
|
db, err := sql.Open("mysql", "root:admin@tcp(127.0.0.1:45017)/zwhzyq?charset=utf8")
|
|
if err != nil {
|
|
fmt.Println(err)
|
|
}
|
|
|
|
//关闭数据库,db会被多个goroutine共享,可以不调用
|
|
//defer db.Close()
|
|
|
|
//查询数据,指定字段名,返回sql.Rows结果集
|
|
rows, _ := db.Query("select id,name from test")
|
|
id := 0
|
|
name := ""
|
|
for rows.Next() {
|
|
rows.Scan(&id, &name)
|
|
fmt.Println(id, name)
|
|
}
|
|
|
|
//查询数据,指定字段名,返回sql.Rows结果集
|
|
rows_, _ := db.Query("select id,name from test")
|
|
cols_, _ := rows_.Columns()
|
|
for rows.Next() {
|
|
rows.Scan(&cols_[0], &cols_[1])
|
|
fmt.Println(cols_[0], cols_[1])
|
|
}
|
|
|
|
//查询数据,取所有字段
|
|
rows2, _ := db.Query("select * from test")
|
|
//返回所有列
|
|
cols, _ := rows2.Columns()
|
|
//这里表示一行所有列的值,用[]byte表示
|
|
vals := make([][]byte, len(cols))
|
|
//这里表示一行填充数据
|
|
scans := make([]interface{}, len(cols))
|
|
//这里scans引用vals,把数据填充到[]byte里
|
|
for k, _ := range vals {
|
|
scans[k] = &vals[k]
|
|
}
|
|
|
|
i := 0
|
|
result := make(map[int]map[string]string)
|
|
for rows2.Next() {
|
|
//填充数据
|
|
rows2.Scan(scans...)
|
|
//每行数据
|
|
row := make(map[string]string)
|
|
//把vals中的数据复制到row中
|
|
for k, v := range vals {
|
|
key := cols[k]
|
|
//这里把[]byte数据转成string
|
|
row[key] = string(v)
|
|
}
|
|
//放入结果集
|
|
result[i] = row
|
|
i++
|
|
}
|
|
fmt.Println(result)
|
|
|
|
//查询一行数据
|
|
rows3 := db.QueryRow("select id,name from test where id = ?", 1)
|
|
rows3.Scan(&id, &name)
|
|
fmt.Println(id, name)
|
|
|
|
//插入一行数据
|
|
ret, _ := db.Exec("insert into test(id,name) values(3, '444')")
|
|
//获取插入ID
|
|
ins_id, _ := ret.LastInsertId()
|
|
fmt.Println(ins_id)
|
|
|
|
//更新数据
|
|
ret2, _ := db.Exec("update test set name = '000' where id > ?", 2)
|
|
//获取影响行数
|
|
aff_nums, _ := ret2.RowsAffected()
|
|
fmt.Println(aff_nums)
|
|
|
|
//删除数据
|
|
ret3, _ := db.Exec("delete from test where id = ?", 3)
|
|
//获取影响行数
|
|
del_nums, _ := ret3.RowsAffected()
|
|
fmt.Println(del_nums)
|
|
|
|
//预处理语句
|
|
stmt, _ := db.Prepare("select id,name from test where id = ?")
|
|
rows4, _ := stmt.Query(3)
|
|
//注意这里需要Next()下,不然下面取不到值
|
|
rows4.Next()
|
|
rows4.Scan(&id, &name)
|
|
fmt.Println(id, name)
|
|
|
|
stmt2, _ := db.Prepare("insert into test values(null, ?)")
|
|
rows5, _ := stmt2.Exec("666", 66)
|
|
fmt.Println(rows5.RowsAffected())
|
|
|
|
//事务处理
|
|
tx, _ := db.Begin()
|
|
|
|
ret4, _ := tx.Exec("update test set price = price + 100 where id = ?", 1)
|
|
ret5, _ := tx.Exec("update test set price = price - 100 where id = ?", 2)
|
|
upd_nums1, _ := ret4.RowsAffected()
|
|
upd_nums2, _ := ret5.RowsAffected()
|
|
|
|
if upd_nums1 > 0 && upd_nums2 > 0 {
|
|
//只有两条更新同时成功,那么才提交
|
|
tx.Commit()
|
|
} else {
|
|
//否则回滚
|
|
tx.Rollback()
|
|
}
|
|
}
|
|
|
|
func GetCode_value() {
|
|
//打开数据库
|
|
//DSN数据源字符串:用户名:密码@协议(地址:端口)/数据库?参数=参数值
|
|
db, err := sql.Open("mysql", "root:admin@tcp(127.0.0.1:45017)/zwhzyq?charset=utf8")
|
|
if err != nil {
|
|
fmt.Println(err)
|
|
}
|
|
|
|
//关闭数据库,db会被多个goroutine共享,可以不调用
|
|
defer db.Close()
|
|
fmt.Println("测试开始!")
|
|
|
|
codeValues, _ := db.Query("select code_value, code_name from code_value where code_type = 'GB2261';")
|
|
//返回所有列
|
|
codeValue := CODE_VALUE{}
|
|
for codeValues.Next() {
|
|
codeValues.Scan(&codeValue.CODE_VALUE, &codeValue.CODE_NAME)
|
|
fmt.Println(codeValue)
|
|
}
|
|
}
|
|
|
|
type CODE_VALUE struct {
|
|
CODE_VALUE_SEQ string // 代码项序号CODE_VALUE_SEQ
|
|
CODE_TYPE string // 代码类别编码CODE_TYPE
|
|
CODE_COLUMN_NAME string // CODE_COLUMN_NAME
|
|
CODE_LEVEL string // CODE_LEVEL
|
|
CODE_VALUE string // 代码值CODE_VALUE
|
|
ININO string // ININO
|
|
SUB_CODE_VALUE string // 父代码值SUB_CODE_VALUE
|
|
CODE_NAME string // 代码名称CODE_NAME
|
|
CODE_NAME2 string // 代码名称2CODE_NAME2
|
|
CODE_NAME3 string // 代码名称3CODE_NAME3
|
|
CODE_REMARK string // CODE_REMARK
|
|
CODE_SPELLING string // 代码名称首字母CODE_SPELLING
|
|
ISCUSTOMIZE string // 是否为自定义代码(0:否,1:是)ISCUSTOMIZE
|
|
CODE_ASSIST string // CODE_ASSIST
|
|
CODE_STATUS string // 代码状态(0:不显示,1:显示)CODE_STATUS
|
|
CODE_LEAF string // 是否可选(0:不可,1:可以选择)CODE_LEAF
|
|
START_DATE string // START_DATE
|
|
STOP_DATE string // STOP_DATE
|
|
}
|
|
|