网站建设需要些什么设备,wordpress投稿,网站推荐界面,广告联盟有哪些文章目录1. SQL语法简介2. MySQL最佳实践3. Go SQL驱动接口解读4. 数据库增删改查5. stmt6. SQLBuilder6.1 Go-SQLBuilder6.2 Gendry6.3 自行实现SQLBuilder7. GORM8. Go操作MongoDB1. SQL语法简介
SQL#xff08;Structured Query Language#xff09;是一套语法标准#…
文章目录1. SQL语法简介2. MySQL最佳实践3. Go SQL驱动接口解读4. 数据库增删改查5. stmt6. SQLBuilder6.1 Go-SQLBuilder6.2 Gendry6.3 自行实现SQLBuilder7. GORM8. Go操作MongoDB1. SQL语法简介
SQLStructured Query Language是一套语法标准不区分大小写 MySQL、sql-server和Oracle都是关系型数据库在一些高级语法上跟标准SQL略有出入 Linux用户安装MySQL服务端
yum install mysql-server安装MySQL客户端
yum install mysql启动MySQL服务端
service mysqld start以管理员登录
mysql -h localhost -P 3306 -u root -p123456-hmysql server host不写时默认是localhost-Pmysql server port不写时默认是3306-uuser name-u后可以加空格也可以不加-ppassword密码中可能包含空格所以要加引号高版本的mysql不允许在命令行中直接输入密码此时只输入-p后面不要写密码即可创建账号
create user tester identified by 123456创建database
create database test把特定database的操作权限授予一个普通用户
grant all on test.* to tester;以普通用户登录
mysql -utester -p123456使用database
use test创建表
create table if not exists student(id int not null auto_increment comment 主键自增id,name char(10) not null comment 姓名,province char(6) not null comment 省,city char(10) not null comment 城市,addr varchar(100) default comment 地址,score float not null default 0 comment 考试成绩,enrollment date not null comment 入学时间,primary key (id), unique key idx_name (name), key idx_location (province,city)
)default charsetutf8 comment 学员基本信息;新增记录必须给not null且无default值的列赋值
insert into student (name,province,city,enrollment) values(张三,北京,北京,2021-03-05),(李四,河南,郑州,2021-04-25),(小丽,四川,成都,2021-03-10);查询
select id,name from student where id0;select province,avg(score) as avg_score from student where score0 group by province having avg_score50 order by avg_score desc;修改
update student set scorescore10,addr海淀 where province北京;update student setscorecase provincewhen 北京 then score10 when 四川 then score5 else score7end,addrcase provincewhen 北京 then 东城区 when 四川 then 幸福里 else 朝阳区 end
where id0;删除
delete from student where city 郑州;
delete from student; --删除表里的所有行
drop table student; --删除表2. MySQL最佳实践
写sql时一律使用小写建表时先判断表是否已存在if not exists所有的列和表都加comment字符串长度比较短时尽量使用char定长有利于内存对齐读写性能更好而varchar字段频繁修改时容易产生内存碎片满足需求的前提下尽量使用短的数据类型如tinyint vs int, float vs double, date vs datetime
null
default null有别于default 和default 0is null, is not null有别于! ‘’, !0尽量设为not null 有些DB索引列不允许包含null对含有null的列进行统计结果可能不符合预期null值有时候会严重拖慢系统性能
索引 B即Balance对于m叉树每个节点上最多有m个数据最少有m/2个数据根节点除外 叶节点上存储了所有数据把叶节点链接起来可以顺序遍历所有数据 每个节点设计成内存页的整倍数。MySQL的m1200树的前两层放在内存中 MySQL索引默认使用B树 主键默认会加索引。按主键构建的B树里包含所有列的数据而普通索引的B树里只存储了主键还需要再查一次主键对应的B树回表 联合索引的前缀同样具有索引的效果 sql语句前加explain可以查看索引使用情况 如果MySQL没有选择最优的索引方案可以在where前force indexindex_name
规避慢查询
大部分的慢查询都是因为没有正确地使用索引。查看一条SQL语句使用索引的情况只需要在SQL前加个explain一次select不要超过1000行分页查询limit m,n会检索前mn行只是返回后n行通常用idx来代替这种分页方式stmt一节会展示遍历整个table的正确姿势批量操作时最好一条sql语句搞定其次打包成一个事务一次性提交高并发情况下减少对共享资源的争用不要使用连表操作join逻辑在业务代码里完成
3. Go SQL驱动接口解读
Go官方没有提供数据库驱动而是为开发数据库驱动定义了一些标准接口即database/sql开发者可以根据定义的接口来开发相应的数据库驱动 Go中支持MySQL的驱动比较多如
github.com/go-sql-driver/mysql 支持 database/sqlgithub.com/ziutek/mymysql 支持 database/sql也支持自定义的接口github.com/Philio/GoMySQL 不支持 database/sql自定义接口
Driver
type Driver interface { Open(name string) (Conn, error)
}
var d Driver{proto: tcp, raddr: 127.0.0.1:3306}
sql.Register(mysql, d) // 注册数据库驱动Conn
type Conn interface {Prepare(query string) (Stmt, error) // 把一个查询query传给Prepare返回Stmt(statement)Close() error // 关闭数据库连接Begin() (Tx, error) // 返回一个事务Tx(transaction)
}Stmt
type Stmt interface {Close() error // 关闭当前的链接状态NumInput() int // 返回当前预留参数的个数Exec(args []Value) (Result, error) // 执行Prepare准备好的 sql传入参数执行 update/insert 等操作返回 Result 数据Query(args []Value) (Rows, error) // 执行Prepare准备好的 sql传入需要的参数执行 select 操作返回 Rows 结果集
}Tx
type Tx interface {Commit() error // 提交事务Rollback() error // 回滚事务
}Result
type Result interface {LastInsertId() (int64, error) // 返回由数据库执行插入操作得到的自增ID号如果使用单个INSERT将多行插入到表中则LastInsertId是第一条数据使用的idRowsAffected() (int64, error) // 返回操作影响的数据条目数
}RowsAffected RowsAffected是int64的别名它实现了Result接口
type RowsAffected int64
func (RowsAffected) LastInsertId() (int64, error)
func (v RowsAffected) RowsAffected() (int64, error)Rows
type Rows interface {Columns() []string // 查询所需要的表字段Close() error // 关闭迭代器Next(dest []Value) error // 返回下一条数据把数据赋值给destdest里面的元素必须是 driver.Value的值如果最后没数据了Next 函数返回 io.EOF
}Value
type Value interface{}Value 要么是 nil要么是下面的任意一种
int64float64bool[]bytestringtime.Time
ValueConverter
type ValueConverter interface {//把数据库里的数据类型转换成Value允许的数据类型ConvertValue(v interface{}) (Value, error)
}4. 数据库增删改查
下载第三方库
go get github.com/go-sql-driver/mysql连接数据库
db, err : sql.Open(mysql, root:tcp(localhost:3306)/test?charsetutf8)DSN(data source name)格式
[username[:password]][protocol[(address)]]/dbname[?param1value1...paramNvalueN] 例如user:passwordtcp(localhost:5555)/dbname?charsetutf8mb4parseTimeTrue 如果是本地MySQl且采用默认的3306端口可简写为user:password/dbname 连接参数要支持完整的UTF-8编码您需要将charsetutf8更改为charsetutf8mb4想要正确的处理time.Time 您需要带上parseTime参数
增删改
func (*sql.DB).Exec(sql string) (sql.Result, error)查
func (*sql.DB).Query(sql string) (*sql.Rows, error)crud.go import (database/sqlfmtgo-course/databasetime_ github.com/go-sql-driver/mysql
)const TIME_LAYOUT 2006-01-02var (loc *time.Location
)func init() {loc, _ time.LoadLocation(Asia/Shanghai)
}// insert 插入数据
func insert(db *sql.DB) {// 一条sql插入2行记录res, err : db.Exec(insert into student (name,province,city,enrollment) values (小明, 深圳, 深圳, 2021-04-18), (小红, 上海, 上海, 2021-04-26))database.CheckError(err)lastId, err : res.LastInsertId() // ID自增用过的id即使对应的行已delete不会重复使用。如果使用单个INSERT语句将多行插入到表中则LastInsertId是第一条数据使用的iddatabase.CheckError(err)fmt.Printf(after insert last id %d\n, lastId)rows, err : res.RowsAffected() // 插入2行所以影响了2行database.CheckError(err)fmt.Printf(insert affect %d row\n, rows)
}// replace 插入(覆盖)数据
func replace(db *sql.DB) {// 由于name字段上有唯一索引insert重复的name会报错而使用replace会先删除再插入res, err : db.Exec(replace into student (name,province,city,enrollment) values (小明, 深圳, 深圳, 2021-04-18), (小红, 上海, 上海, 2021-04-26))database.CheckError(err)lastId, err : res.LastInsertId() // ID自增用过的id即使对应的行已delete不会重复使用database.CheckError(err)fmt.Printf(after insert last id %d\n, lastId)rows, err : res.RowsAffected() // 先删除后插入影响了4行database.CheckError(err)fmt.Printf(insert affect %d row\n, rows)
}// update 修改数据
func update(db *sql.DB) {// 不同的city加不同的分数res, err : db.Exec(update student set scorescore10 where city上海) // 上海加10分database.CheckError(err)lastId, err : res.LastInsertId() // 0, 仅插入操作才会给LastInsertId赋值database.CheckError(err)fmt.Printf(after update last id %d\n, lastId)rows, err : res.RowsAffected() // where city?命中了几行就会影响几行database.CheckError(err)fmt.Printf(update affect %d row\n, rows)
}// query 查询数据
func query(db *sql.DB) {rows, err : db.Query(select id,name,city,score from student where id2) // 查询得分大于2的记录database.CheckError(err)// 没有数据或发生error时返回falsefor rows.Next() {var id intvar score float32var name, city stringerr rows.Scan(id, name, city, score) // 通过scan把db里的数据赋给go变量database.CheckError(err)fmt.Printf(id%d, score%.2f, name%s, city%s \n, id, score, name, city)}
}// delete 删除数据
func delete(db *sql.DB) {res, err : db.Exec(delete from student where id13) // 删除得分大于13的记录database.CheckError(err)rows, err : res.RowsAffected() // where id13命中了几行就会影响几行database.CheckError(err)fmt.Printf(delete affect %d row\n, rows)
}5. stmt
首先看两个sql注入攻击的例子
sql select username,password from user where username username and password password ; 变量username和password从前端输入框获取如果用户输入的username为lily password为aaa’ or ‘1’1则完整的sql为select username,password from user where username‘lily’ and password‘aaa’ or ‘1’‘1’会返回表里的所有记录如果记录数大于0就允许登录则lily的账号被盗
sqlinsert into student (name) values (username ) ;变量username从前端输入框获取如果用户输入的username为lily’); drop table student;–完整sql为insert into student (name) values (‘lily’); drop table student;–‘)通过注释符–屏蔽掉了末尾的’)删除了整个表
防止sql注入的方法
前端输入要加正则校验、长度限制对特殊符号(*; 等)进行转义或编码转换Go的text/template 包里面的HTMLEscapeString函数可以对字符串进行转义处理不要将用户输入直接嵌入到sql语句中而应该使用参数化查询接口如Prepare、Query、Exec(query string, args …interface{})使用专业的SQL注入检测工具进行检测如sqlmap、SQLninja避免网站打印出SQL错误信息以防止攻击者利用这些错误信息进行SQL注入
参数化查询
db.Where(merchant_id ?, merchantId)拼接sql
db.Where(fmt.Sprintf(merchant_id %s, merchantId))定义一个sql模板
stmt, err : db.Prepare(update student set scorescore? where city?)多次使用模板
res, err : stmt.Exec(10, 上海)
res, err stmt.Exec(9, 深圳) SQL预编译 DB执行sql分为3步
词法和语义解析优化SQL语句制定执行计划执行并返回结果
SQL预编译技术是指将用户输入用占位符?代替先对这个模板化的sql进行预编译实际运行时再将用户输入代入除了可以防止SQL注入还可以对预编译的SQL语句进行缓存之后的运行就省去了解析优化SQL语句的过程
stmt_demo.go
// update 通过stmt修改数据
func update(db *sql.DB) {// 不同的city加不同的分数stmt, err : db.Prepare(update student set scorescore? where city?)database.CheckError(err)// 执行修改操作通过stmt.Exec执行查询操作通过stmt.Queryres, err : stmt.Exec(10, 上海) // 上海加10分database.CheckError(err)res, err stmt.Exec(9, 深圳) // 深圳加9分database.CheckError(err)lastId, err : res.LastInsertId() // 0, 仅插入操作才会给LastInsertId赋值database.CheckError(err)fmt.Printf(after update last id %d\n, lastId)rows, err : res.RowsAffected() // where city?命中了几行就会影响几行database.CheckError(err)fmt.Printf(update affect %d row\n, rows)
}// query 通过stmt查询数据
func query(db *sql.DB) {stmt, err : db.Prepare(select id,name,city,score from student where id?)database.CheckError(err)// 执行修改操作通过stmt.Exec执行查询操作通过stmt.Queryrows, err : stmt.Query(2) // 查询得分大于2的记录database.CheckError(err)// 没有数据或发生error时返回falsefor rows.Next() {var id intvar score float32var name, city stringerr rows.Scan(id, name, city, score) //通过scan把db里的数据赋给go变量database.CheckError(err)fmt.Printf(id%d, score%.2f, name%s, city%s \n, id, score, name, city)}
}遍历一张表的正确姿势
// traverse 借助于主健自增ID通过where idmaxid遍历表
func traverse(db *sql.DB) {var maxid intbegin : time.Now()stmt, _ : db.Prepare(select id,name,province from student where id? limit 100) //limit m,n limit 0,nfor i : 0; i 100; i {t0 : time.Now()rows, _ : stmt.Query(maxid)fmt.Println(i, time.Since(t0))for rows.Next() {var id intvar name stringvar province stringrows.Scan(id, name, province)if id maxid {maxid id}}}fmt.Println(total, time.Since(begin))
}6. SQLBuilder
6.1 Go-SQLBuilder
Go-SQLBuilder是一个用于创建SQL语句的工具函数库提供一系列灵活的、与原生SQL语法一致的链式函数
安装方式
go get -u github.com/parkingwang/go-sqlbuilderGo-SQLBuilder通过函数链来构造sql语句比如select语句的构造
func query() {sql : gsb.NewContext().Select(id, name, score, city).From(student).OrderBy(score).DESC().Column(name).ASC().Limit(10).Offset(20).ToSQL()fmt.Println(sql)
}为什么需要SQLBuilder
写一句很长的sql容易出错且出错后不好定位函数式编程可以直接定位到是哪个函数的问题函数式编程比一长串sql更容易编写和理解
6.2 Gendry
Gendry是一个用于辅助操作数据库的Go包基于go-sql-driver /mysql它提供了一系列的方法来为你调用标准库database/sql中的方法准备参数
安装方式
go get –u github.com/didi/gendryGendry倾向于把复杂的筛选条件放在map中并且跟stmt技术结合得比较紧密
func query(db *sql.DB) {where : map[string]interface{}{city: []string{北京, 上海, 杭州},score: 30,addr: builder.IsNotNull,_orderby: score desc,}table : studentfields : []string{id, name, city, score}//准备stmt模板template, values, err : builder.BuildSelect(table, where, fields)database.CheckError(err)//执行stmt模板rows, err : db.Query(template, values...)database.CheckError(err)for rows.Next() {var id intvar name, city stringvar score float32err : rows.Scan(id, name, city, score)database.CheckError(err)fmt.Printf(%d %s %s %.2f\n, id, name, city, score)}
}6.3 自行实现SQLBuilder
作为练习我们自行实现一个SQLBuilder它最终应该支持如下函数链式的编程风格
sql : NewSelectBuilder(student).Column(id,name,city).Where(id0).And(city郑州).Or(city北京).OrderBy(score).Desc().Limit(0, 10).ToString()Builder设计模式的精髓在于Builder对象的方法还是返回一个Builder首先定义一个Builder接口
type Builder interface {toString() stringgetPrev() Builder
}select、where、limit、orderby这些都是Builder这里详细讲解WhereBuilder的设计与实现
type WhereBuilder struct {sb strings.Builder // 拼接where条件字符串orderby *OrderByBuilder // where后面可能会接order bylimit *LimitBuilder // where后面可能会接limitprev Builder // where前面是select
}WhereBuilder中的sb负责当下orderby和limit负责维护后面节点prev负责维护前面的节点 where表达式中可能包含and和or把它们定义为WhereBuilder的方法并且这两个方法依赖返回WhereBuilder自身
func (self *WhereBuilder) And(condition string) *WhereBuilder {self.sb.WriteString( and )self.sb.WriteString(condition)return self
}
func (self *WhereBuilder) Or(condition string) *WhereBuilder {self.sb.WriteString( or )self.sb.WriteString(condition)return self
}where表达式后面可能会跟order by表达式把OrderBy定义为WhereBuilder的方法该方法返回OrderByBuilder
func (self *WhereBuilder) OrderBy(column string) *OrderByBuilder {orderby : newOrderByBuilder(column)self.orderby orderbyorderby.prev selfreturn orderby
}函数链上的最后一个Builder调用ToString()方法生成写成的sql语句
func (self *LimitBuilder) ToString() string {var root Builderroot selffor root.getPrev() ! nil {root root.getPrev() // 递归找到最前面的Builder}return root.toString() // 在最前面的Builder即SelectBuilder上调用toString()
}每个Builder都有toString()方法以WhereBuilder为例它在构造函数里把where表达式放入sb成员变量里WhereBuilder在toString()方法里调用where后面的节点的toString()方法
func newWhereBuilder(condition string) *WhereBuilder {builder : WhereBuilder{}builder.sb.WriteString( where )builder.sb.WriteString(condition)return builder
}
func (self *WhereBuilder) toString() string {// 递归调用后续Builder的ToString()if self.orderby ! nil {self.sb.WriteString(self.orderby.toString())}if self.limit ! nil {self.sb.WriteString(self.limit.toString())}return self.sb.String()
}7. GORM
ORM即Object Relational Mapping对象关系映射 Relational指各种sql类的关系型数据库Object指面向对象编程(object-oriented programming)中的对象 ORM在数据库记录和程序对象之间做一层映射转换使程序中不用再去编写原生SQL而是面向对象的思想去编写类、对象、调用相应的方法来完成数据库操作
go get -u gorm.io/gorm
go get -u gorm.io/driver/mysqlGORM是一个全能的、友好的、基于golang的ORM库 GORM 倾向于约定而不是配置。默认情况下GORM 使用ID作为主键使用结构体名的【蛇形复数】作为表名字段名的【蛇形】作为列名并使用CreatedAt、UpdatedAt字段追踪创建、更新时间 GORM完全是在操作struct看不到sql的影子
type Student struct {Id int gorm:column:id;primaryKeyName string gorm:column:nameProvince stringCity string gorm:column:cityAddress string gorm:column:addrScore float32 gorm:column:scoreEnrollment time.Time gorm:column:enrollment;type:date
}
student : Student{Name: 光绪, Province: 北京, City: 北京, Score: 38, Enrollment: time.Now()
}
db.Create(student)GORM同时支持使用函数链的方式写sql语句
func query(db *gorm.DB) {// 返回一条记录var student Studentdb.Where(city?, 郑州).First(student) // 有First就有Lastfmt.Println(student.Name)fmt.Println()// 返回多条记录var students []Studentdb.Where(city?, 郑州).Find(students)for _, ele : range students {fmt.Printf(id%d, name%s\n, ele.Id, ele.Name)}fmt.Println()students []Student{} // 清空student防止前后影响db.Where(city in ?, []string{郑州, 北京}).Find(students)for _, ele : range students {fmt.Printf(id%d, name%s\n, ele.Id, ele.Name)}fmt.Println(where end)// 根据主键查询student Student{} // 清空student防止前后影响students []Student{}db.First(student, 1)fmt.Println(student.Name)fmt.Println()db.Find(students, []int{1, 2, 3})for _, ele : range students {fmt.Printf(id%d, name%s\n, ele.Id, ele.Name)}fmt.Println(primary key end)// 根据map查询student Student{}students []Student{}db.Where(map[string]interface{}{city: 郑州, score: 0}).Find(students)for _, ele : range students {fmt.Printf(id%d, name%s\n, ele.Id, ele.Name)}fmt.Println(map end)// OR查询student Student{}students []Student{}db.Where(city?, 郑州).Or(city?, 北京).Find(students)for _, ele : range students {fmt.Printf(id%d, name%s\n, ele.Id, ele.Name)}fmt.Println(or end)// order bystudent Student{}students []Student{}db.Where(city?, 郑州).Order(score).Find(students)for _, ele : range students {fmt.Printf(id%d, name%s\n, ele.Id, ele.Name)}fmt.Println(order end)// limitstudent Student{}students []Student{}db.Where(city?, 郑州).Order(score).Limit(1).Offset(0).Find(students)for _, ele : range students {fmt.Printf(id%d, name%s\n, ele.Id, ele.Name)}fmt.Println(limit end)// 选择特定的字段student Student{}db.Select(name).Take(student) // Take从结果中取一个不保证是第一个或最后一个fmt.Printf(name%s, province%s\n, student.Name, student.Province) // 只select了name所以province是空的
}8. Go操作MongoDB
NoSQL泛指非关系型数据库如mongo、redis、HBase mongo使用高效的二进制数据存储文件存储格式为 BSON 一种json的扩展比json性能更好功能更强大MySQL中表的概念在mongo里叫集合collection MySQL中行的概念在mongo中叫文档document一个文档看上去像一个json
安装mongo前先配置yum源vim /etc/yum.repos.d/mongodb-org-4.2.repo
[mongodb-org-4.2]
nameMongoDB Repository baseurlhttps://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/4.2/x86_64/ gpgcheck1 enabled1 gpgkeyhttps://www.mongodb.org/static/pgp/server-4.2.asc一键安装mongosudo yum install -y mongodb-org 启动mongosystemctl start mongod
mongo常用命令
use test; 切换到test库如果没有则创建集合时会自动创建
db.createCollection(student); 创建collection
db.createUser({user: tester,pwd: 123456, roles: [{role: dbAdmin, db: test}]});创建用户
登录mongo --port 27017 -u tester -p 123456 --authenticationDatabase test
db.student.createIndex({name:1});在name上创建索引,不是唯一索引db.student.insertOne({name:张三,city:北京});
db.student.find({name:张三});
db.student.update({name:张三},{name:张三,city:上海})
db.student.deleteOne({name:张三});安装go mongo-driver
go get go.mongodb.org/mongo-driver
go get go.mongodb.org/mongo-driver/x/bsonx/bsoncorev1.7.1
go get go.mongodb.org/mongo-driver/x/mongo/driverv1.7.1
go get go.mongodb.org/mongo-driver/mongo/optionsv1.7.1
go get go.mongodb.org/mongo-driver/x/mongo/driver/topologyv1.7.1
go get go.mongodb.org/mongo-driver/mongov1.7.1连接db
option : options.Client().ApplyURI(mongodb://127.0.0.1:27017).
SetConnectTimeout(time.Second).//连接超时时长
SetAuth(options.Credential{Username: tester, Password: 123456, AuthSource: test}) // 指定用户名和密码AuthSource代表Database
client, err : mongo.Connect(context.Background(), option)
err client.Ping(ctx, nil) 注意Ping成功才代表连接成功 查询mongo
sort : bson.D{{name, 1}} // 1升序-1降序
filter : bson.D{{score, bson.D{{$gt, 3}}}} //score3
findOption : options.Find()
findOption.SetSort(sort) // 按name排序
findOption.SetLimit(10) // 最多返回10个
findOption.SetSkip(3) // 跳过前3个
cursor, err : collection.Find(ctx, filter, findOption)
defer cursor.Close(ctx) // 关闭迭代器
for cursor.Next(ctx) {var doc Studenterr : cursor.Decode(doc)database.CheckError(err)fmt.Printf(%s %s %.2f\n, doc.Name, doc.City, doc.Score)
}
