一、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) } } |
#执行结果：
| 1 | 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 2 3 4 5 6 | 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 |