SQL refers to "Structured Query Language", which is a language for operating databases, including creating databases, deleting databases, querying records, modifying records, adding fields, etc. SQL is the standard language for relational databases. All relational database management systems (RDBMS), such as MySQL, Oracle, SQL Server, MS Access, Sybase, Informix, Postgres, etc., use SQL as their standard processing language.
The operating environment of this tutorial: Windows 7 system, GO version 1.18, Dell G3 computer.
What is SQL
SQL is a language for operating databases, including creating databases, deleting databases, querying records, modifying records, and adding fields etc. Although SQL is a language standardized by ANSI, it has many different implementations.
SQL is the abbreviation of Structured Query Language, which is translated into Chinese as "Structured Query Language". SQL is a computer language used to store, retrieve, and modify data stored in relational databases.
SQL is the standard language for relational databases. All relational database management systems (RDBMS), such as MySQL, Oracle, SQL Server, MS Access, Sybase, Informix, Postgres, etc., use SQL as their standard Processing language.
Purposes of SQL
SQL is popular because of its uses:
Allow user access Data in a relational database system;
allows users to describe data;
allows users to define data in the database and process the data;
Allows SQL modules, libraries or preprocessors to be embedded into other programming languages;
Allows users to create and delete databases, tables, Data items (records);
Allows users to create views, stored procedures, and functions in the database;
Allows users to set up tables and storage Permissions for procedures and views.
Go language operation database (MySQL)
The Go language standard library provides a sql library for database operations , you can use SQL language to operate the database.
go get -u github.com/go-sql-driver/mysql
func Open(driverName, dataSourceName string) (*DB, error)
OpenOpen a database specified by dirverName, specified by dataSourceName Data source , generally includes at least the database file name and other information necessary for connection.
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
)
func main() {
// DSN:Data Source Name
dsn := "user:password@tcp(127.0.0.1:3306)/dbname"
db, err := sql.Open("mysql", dsn)
if err != nil {
panic(err)
}
defer db.Close() // 注意这行代码要写在上面err判断的下面
}The Open function may only verify whether its parameter format is correct, but actually does not No connection to the database is created. If you want to check whether the data source name is real and valid , you should call the Ping method
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
//需要注意 这里需要引用自己的mysql文件
var db *sql.DB
func initDB()(err error) {
//账号 密码 端口号(tcp)127.0.0.1:3306 表名 字符集 校验时间
dsn := "root:123456@tcp(127.0.0.1:3306)/gomysql?charset=utf8mb4&parseTime=true"
//加载驱动
//这里需要是=而不是:=因为我们是给全局变量(db)赋值
db,err = sql.Open("mysql",dsn)
if err!=nil {
return err
}
//尝试和数据库建立连接(校验dsn正确)
//然后用了ping命令
err=db.Ping()
if err!=nil {
return err
}
return nil
}
func main() {
err := initDB()
if err!=nil {
fmt.Printf("connect failed,err:%v\n",err)
return
}
}Set the maximum number of connections to be established with the database. If n is greater than 0 and less than the maximum number of idle connections, the maximum number of idle connections will be reduced to a limit that matches the maximum number of open connections. If n<=0, the maximum number of open connections will not be limited, The default is 0 (no limit)
func (db *DB) SetMaxIdleConns(n int)
If n is greater than the maximum number of open connections, the new maximum number of idle connections will be reduced to Matches the
limit of the maximum number of open connections. If n<=0, idle connections will not be retained.
1.2CRUDCREATE DATABASE sql_test;
use sql_test;
Execute the following command to create a data table for testing:CREATE TABLE `user` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT '',
`age` INT(11) DEFAULT '0',
PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;1.2.2 Query
func (db *DB) QueryRow(query string, args ...interface{}) *Rowfunc queryRowDemo() {
sqlStr := "select id, name, age from user where id=?"
var u user
// 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.name, &u.age)
if err != nil {
fmt.Printf("scan failed, err:%v\n", err)
return
}
fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
}Multiple row query
func (db *DB) Query(query string, args ...interface{}) (*Rows, error)// 查询多条数据示例
func queryMultiRowDemo() {
sqlStr := "select id, name, age from user where id > ?"
rows, err := db.Query(sqlStr, 0)
if err != nil {
fmt.Printf("query failed, err:%v\n", err)
return
}
// 非常重要：关闭rows释放持有的数据库链接
defer rows.Close()
// 循环读取结果集中的数据
for rows.Next() {
var u user
err := rows.Scan(&u.id, &u.name, &u.age)
if err != nil {
fmt.Printf("scan failed, err:%v\n", err)
return
}
fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
}
}func (db *DB) Exec(query string, args ...interface{}) (Result, error)The specific sample code for inserting data is as follows:
// 插入数据
func insertRowDemo() {
sqlStr := "insert into user(name, age) values (?,?)"
ret, err := db.Exec(sqlStr, "王五", 38)
if err != nil {
fmt.Printf("insert failed, err:%v\n", err)
return
}
theID, err := ret.LastInsertId() // 新插入数据的id
if err != nil {
fmt.Printf("get lastinsert ID failed, err:%v\n", err)
return
}
fmt.Printf("insert success, the id is %d.\n", theID)
}// 更新数据
func updateRowDemo() {
sqlStr := "update user set age=? where id = ?"
ret, err := db.Exec(sqlStr, 39, 3)
if err != nil {
fmt.Printf("update failed, err:%v\n", err)
return
}
n, err := ret.RowsAffected() // 操作影响的行数
if err != nil {
fmt.Printf("get RowsAffected failed, err:%v\n", err)
return
}
fmt.Printf("update success, affected rows:%d\n", n)
}具体删除数据的示例代码如下：
// 删除数据
func deleteRowDemo() {
sqlStr := "delete from user where id = ?"
ret, err := db.Exec(sqlStr, 3)
if err != nil {
fmt.Printf("delete failed, err:%v\n", err)
return
}
n, err := ret.RowsAffected() // 操作影响的行数
if err != nil {
fmt.Printf("get RowsAffected failed, err:%v\n", err)
return
}
fmt.Printf("delete success, affected rows:%d\n", n)
}package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
// 定义一个全局对象db
var db *sql.DB
// 定义一个初始化数据库的函数
func initDB() (err error) {
// DSN:Data Source Name
dsn := "root:123456@tcp(127.0.0.1:3306)/sql_test?charset=utf8&parseTime=True"
// 不会校验账号密码是否正确
// 注意！！！这里不要使用:=，我们是给全局变量赋值，然后在main函数中使用全局变量db
db, err = sql.Open("mysql", dsn)
if err != nil {
return err
}
// 尝试与数据库建立连接（校验dsn是否正确）
err = db.Ping()
if err != nil {
return err
}
return nil
}
type user struct {
id int
age int
name string
}
func queryRowDemo() {
sqlStr := "select id, name, age from user where id=?"
var u user
// 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.name, &u.age)
if err != nil {
fmt.Printf("scan failed, err:%v\n", err)
return
}
fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
}
// 查询多条数据示例
func queryMultiRowDemo() {
sqlStr := "select id, name, age from user where id > ?"
rows, err := db.Query(sqlStr, 0)
if err != nil {
fmt.Printf("query failed, err:%v\n", err)
return
}
// 非常重要：关闭rows释放持有的数据库链接
defer rows.Close()
// 循环读取结果集中的数据
for rows.Next() {
var u user
err := rows.Scan(&u.id, &u.name, &u.age)
if err != nil {
fmt.Printf("scan failed, err:%v\n", err)
return
}
fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
}
}
func insertRowDemo() {
sqlStr := "insert into user(name,age) value (?,?)"
//
ret,err := db.Exec(sqlStr,"王五",40)
if err!=nil {
fmt.Printf("inserf failed,err:%v\n",err)
return
}
//插入成功之后需要返回这个id
theID,err:=ret.LastInsertId()
if err != nil{
fmt.Printf("get the last insertid failed,err:%v\n",theID)
return
}
fmt.Printf("insert success,theID is:%v\n",theID)
}
func updateRowDemo() {
sqlStr := "update user set name =? where id = ?"
//执行含有sqlStr参数的语句
ret,err:=db.Exec(sqlStr,"赵四",4)
if err!=nil {
fmt.Printf("update failed,err:%v\n",err)
return
}
AnoID,err:=ret.RowsAffected()
if err!=nil {
fmt.Printf("updateRowAffected failed,err:%v\n",err)
return
}
fmt.Printf("update success AnoID:%v\n",AnoID)
}
// 删除数据
func deleteRowDemo() {
sqlStr := "delete from user where id = ?"
ret, err := db.Exec(sqlStr, 5)
if err != nil {
fmt.Printf("delete failed, err:%v\n", err)
return
}
n, err := ret.RowsAffected() // 操作影响的行数
if err != nil {
fmt.Printf("get RowsAffected failed, err:%v\n", err)
return
}
fmt.Printf("delete success, affected rows:%d\n", n)
}
func main() {
err := initDB() // 调用输出化数据库的函数
if err != nil {
fmt.Printf("init db failed,err:%v\n", err)
return
}
//queryRowDemo()
//insertRowDemo()
//updateRowDemo()
deleteRowDemo()
queryMultiRowDemo()
}普通SQL语句执行过程：
客户端对SQL语句进行占位符替换得到完整的SQL语句。
客户端发送完整SQL语句到MySQL服务端
MySQL服务端执行完整的SQL语句并将结果返回给客户端。
预处理执行过程：
把SQL语句分成两部分，命令部分与数据部分。
先把命令部分发送给MySQL服务端，MySQL服务端进行SQL预处理。
然后把数据部分发送给MySQL服务端，MySQL服务端对SQL语句进行占位符替换。
MySQL服务端执行完整的SQL语句并将结果返回给客户端
优化MySQL服务器重复执行SQL的方法，可以提升服务器性能，提前让服务器编译，一次编译多次执行，节省后续编译的成本。
避免SQL注入问题。
func (db *DB) Prepare(query string) (*Stmt, error)
查询操作的预处理示例代码如下：
// 预处理查询示例
func prepareQueryDemo() {
sqlStr := "select id, name, age from user where id > ?"
stmt, err := db.Prepare(sqlStr)
if err != nil {
fmt.Printf("prepare failed, err:%v\n", err)
return
}
defer stmt.Close()
rows, err := stmt.Query(0)
if err != nil {
fmt.Printf("query failed, err:%v\n", err)
return
}
defer rows.Close()
// 循环读取结果集中的数据
for rows.Next() {
var u user
err := rows.Scan(&u.id, &u.name, &u.age)
if err != nil {
fmt.Printf("scan failed, err:%v\n", err)
return
}
fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
}
}插入、更新和删除操作的预处理十分类似，这里以插入操作的预处理为例：
// 预处理插入示例
func prepareInsertDemo() {
sqlStr := "insert into user(name, age) values (?,?)"
stmt, err := db.Prepare(sqlStr)
if err != nil {
fmt.Printf("prepare failed, err:%v\n", err)
return
}
defer stmt.Close()
_, err = stmt.Exec("小王子", 18)
if err != nil {
fmt.Printf("insert failed, err:%v\n", err)
return
}
_, err = stmt.Exec("沙河娜扎", 18)
if err != nil {
fmt.Printf("insert failed, err:%v\n", err)
return
}
fmt.Println("insert success.")
}我们任何时候都不应该自己拼接SQL语句！
// sql注入示例
func sqlInjectDemo(name string) {
sqlStr := fmt.Sprintf("select id, name, age from user where name='%s'", name)
fmt.Printf("SQL:%s\n", sqlStr)
var u user
err := db.QueryRow(sqlStr).Scan(&u.id, &u.name, &u.age)
if err != nil {
fmt.Printf("exec failed, err:%v\n", err)
return
}
fmt.Printf("user:%#v\n", u)
}此时以下输入字符串都可以引发SQL注入问题：
sqlInjectDemo("xxx' or 1=1#")
sqlInjectDemo("xxx' union select * from user #")
sqlInjectDemo("xxx' and (select count(*) from user) <10 #")| 数据库 | 占位符语法 |
|---|---|
| MySQL | <span style="font-family:Microsoft Yahei, Hiragino Sans GB, Helvetica, Helvetica Neue, 微软雅黑, Tahoma, Arial, sans-serif">?</span> |
| PostgreSQL | $1, $2等 |
| SQLite | ? 和$1 |
| Oracle | <span style="font-family:Microsoft Yahei, Hiragino Sans GB, Helvetica, Helvetica Neue, 微软雅黑, Tahoma, Arial, sans-serif">:name</span> |
The above is the detailed content of What is sql in go language. For more information, please follow other related articles on the PHP Chinese website!
Usage of Type keyword in Go
How to implement linked list in go
What are the Go language programming software?
How to learn go language from 0 basics
How to define variables in golang
What are the methods to implement operator overloading in Go language?
What are the operators in Go language?
What are the data conversion methods in golang?
![Quickly get started with the Golang Gin framework [Use Gin to build a million-level concurrent IM instant messaging system]](https://img.php.cn/upload/course/000/000/068/63a2b21046723283.jpg)
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
