Polaris6G's blog

Stay humble,stay hungry.

0%

MySQL初步

Posted on Edited on In Valine:

MySQL

摘要：

MySQL 是最流行的关系型数据库管理系统，后端必会！！

下面我们对MySQL做一个系统的介绍：

数据库

常见的数据库SQLlite、MySQL、 SQLServer、postgreSQL、Oracle

主流的关系型数据库，类似的还有postgreSQL

关系型数据库：

用表来存一类数据。

表结构设计的三大范式：《漫画数据库》

MySQL知识点

SQL语句

DDL: 操作数据库的

DML: 表的增删改查

DCL：用户及权限

存储引擎

MySQL 支持插件式的存储引擎。

常见的存储引擎：MylSAM和InnoDB

MyLSAM：

​ 1.查询速度快

​ 2.只支持表锁

​ 3.不支持事务

InnoDB：

​ 1.整体速度快

​ 2.支持表锁和行锁

​ 3.支持事务

事务：

把多个操作当成一个整体

事务的特点：

ACID:

​ 1.原子性：事务要么成功要么失败，没有中间状态。

​ 2.一致性：数据库的完整性没有被破坏。

​ 3.隔离性：事务之间是相互隔离的。

​ （1）隔离的四个级别

​ 4.持久性：事务操作的结果是不会丢失的。

索引：

索引的原理：B树和B+树

索引的类型

索引的命中

分库分表

SQL注入

SQL慢查询优化

MySQL主从：

​ binlog

MySQL读写分离

Mysql安装

①安装服务：

1
mysqld --install

②初始化：

1
　mysqld --initialize --console

③开启服务：

1
net start mysql

④关闭服务：

1
net stop mysql

⑤登录mysql：

1
mysql -u root -p

　Enter Password：(密码）

⑥修改密码：

1
alter user 'root'@'localhost' identified by 'root'(by 接着的是密码)

⑦标记删除mysql服务：

1
sc delete mysql

Go操作MySQL：

database/sql

原生支持连接池，是并发安全的

这个标准库没有具体的实现，知识列出了一些需要第三方库实现的具体内容

下载驱动

1
go get -u github.com/go-sql-driver/mysql

go get包的路径就是下载第三方的依赖

将第三方的依赖默认保存在&GOPATH/src

使用

初始化连接
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
package main

import (
   "database/sql"
   "fmt"
   _"github.com/go-sql-driver/mysql"
)
//Go连接MySQL示例my

func main() {
   //连数据库
   dsn:= "root:Polaris6G123@tcp127.0.0.1:3306/sql_test"
   //连接数据库
   db, err := sql.Open("mysql",dsn)//不会校验用户名和密码是否匹配和正确
   if err !=nil{//dsn格式不正确的时候会报错
      fmt.Printf("dsn:%s invaild, err:%v\n",dsn,err)
      return
   }
   err = db.Ping()//尝试连接数据库（真正需要密码匹配）
   if err != nil{//密码不正确会报错
      fmt.Printf("open %s failes, err:%v\n",dsn,err)
      return
   }
   fmt.Println("连接数据库资源成功！")
}
SetMaxOpenConns
1
func (db *DB) SetMaxOpenConns(n int)

SetMaxOpenConns设置与数据库建立连接的最大数目。 如果n大于0且小于最大闲置连接数，会将最大闲置连接数减小到匹配最大开启连接数的限制。 如果n<=0，不会限制最大开启连接数，默认为0（无限制）。

SetMaxIdleConns
1
func (db *DB) SetMaxIdleConns(n int)

SetMaxIdleConns设置连接池中的最大闲置连接数。 如果n大于最大开启连接数，则新的最大闲置连接数会减小到匹配最大开启连接数的限制。 如果n<=0，不会保留闲置连接。

单条+多条查询

单行查询db.QueryRow()执行一次查询，并期望返回最多一行结果（即Row）。QueryRow总是返回非nil的值，直到返回值的Scan方法被调用时，才会返回被延迟的错误。（如：未找到结果）

1
func (db *DB) QueryRow(query string, args ...interface{}) *Row

多行查询db.Query()执行一次查询，返回多行结果（即Rows），一般用于执行select命令。参数args表示query中的占位参数。

1
func (db *DB) Query(query string, args ...interface{}) (*Rows, error)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
package main

import (
   "database/sql"
   "fmt"
   _"github.com/go-sql-driver/mysql"
)
//Go连接MySQL示例my
var db *sql.DB //定义一个全局变量，是一个数据库连接池对象

func initDB()(err error){
   //数据库信息
   //用户名：密码@tcp(ip:端口)/数据库名字
   dsn:= "root:Polaris6G@tcp(127.0.0.1:3306)/sql_test"
   //连接数据库
   db, err = sql.Open("mysql",dsn)//不会校验用户名和密码是否匹配和正确//是=不是:=这里细节拉满
   if err !=nil{//dsn格式不正确的时候会报错
      return
   }
   err = db.Ping()//尝试连接数据库（真正需要密码匹配）
   if err != nil{
      return
   }
   db.SetMaxOpenConns(10)//设置数据库连接池的最大连接数
   db.SetMaxIdleConns(5)//设置最大空闲连接数
   return
}
type user struct {
   id int
   name string
   age int
}
//查询单个记录
func queryOne(id int)  {
   var u1 user
   //1.查询单条记录的sql语句
   sqlStr := `select id, name, age from user where id=?;`//?占位符表示不确定，由下面的代码确定，也可写死
   //2.执行
   rowObj :=db.QueryRow(sqlStr,id)//从连接池里拿出来一个连接去数据库查询单条记录
   //3.拿到结果
   rowObj.Scan(&u1.id, &u1.name, &u1.age )//必须对rowObj对象调用Scan方法，因为该方法会释放数据库链接
   //打印结果
   fmt.Printf("u1: %v\n", u1)
   //或者合并
   //db.QueryRow(sqlStr, 1).Scan(&u1.id, &u1.name, &u1.age)
}
//查询多行记录
func queryMore(n int){
	//1.sql语句
	sqlStr := `select id ,name,age from user where id>=?;`
	//2.执行
	rows, err :=db.Query(sqlStr, n)
	if err != nil{
		fmt.Printf("exed %s query failed, err:%v\n",sqlStr, err)
		return
	}
	//3.一定要关闭rows
	defer rows.Close()
	//4.循环取值
	for rows.Next() {
		var u2 user
		err := rows.Scan(&u2.id, &u2.name, &u2.age)
		if err != nil{
			fmt.Printf("scan failed,err=%v\n ",err)
			return
		}
		fmt.Printf("u2:%v\n",u2)
	}
	return
}
func main() {
   err := initDB()
   if err != nil {
      fmt.Printf("init DB failed, err:%v\n", err)
      return
   }
   fmt.Println("连接数据库成功！")
   queryOne(2)
   queryMore(2)
   return
}

插入、更新和删除操作都使用Exec方法。

1
func (db *DB) Exec(query string, args ...interface{}) (Result, error)
插入数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
//插入数据函数
func insert(){
   //1.写sql语句
   sqlStr :=`insert into user(name, age)values(?, ?)`
   //2.exec
   ret, err := db.Exec(sqlStr,"Jason", 23)
   if err !=nil{
      fmt.Printf("insert failed,err:%v\n",err)
      return
   }
   //3.如果是插入数据的操作，能拿到插入数据的id和被影响行数
   id, err := ret.LastInsertId()
   if err !=nil{
      fmt.Printf("get id failed,err:%v\n", err)
      return
   }
   fmt.Println("id:",id)
   num, err :=ret.RowsAffected()
   if err !=nil{
      fmt.Printf("get row number failed,err:%v\n", err)
      return
   }
   fmt.Println("affected row:",num)
}
更新数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
//更新数据
func updaterow(newage int, id int) {
   sqlStr := "update user set age=? where id =?"
   ret, err := db.Exec(sqlStr, newage, id)
   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)
}
删除数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
func deleterow(id int){
   sqlStr := `delete from user where id=?`
   ret, err :=db.Exec(sqlStr, id)
   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)
}
预处理

什么是预处理？

普通SQL语句执行过程：

  1. 客户端对SQL语句进行占位符替换得到完整的SQL语句。
  2. 客户端发送完整SQL语句到MySQL服务端
  3. MySQL服务端执行完整的SQL语句并将结果返回给客户端。

预处理执行过程：

  1. 把SQL语句分成两部分，命令部分与数据部分。
  2. 先把命令部分发送给MySQL服务端，MySQL服务端进行SQL预处理。
  3. 然后把数据部分发送给MySQL服务端，MySQL服务端对SQL语句进行占位符替换。
  4. MySQL服务端执行完整的SQL语句并将结果返回给客户端。

为什么要预处理？

  1. 优化MySQL服务器重复执行SQL的方法，可以提升服务器性能，提前让服务器编译，一次编译多次执行，节省后续编译的成本。
  2. 避免SQL注入问题。

具体实现

database/sql中使用下面的Prepare方法来实现预处理操作。

1
func (db *DB) Prepare(query string) (*Stmt, error)

Prepare方法会先将sql语句发送给MySQL服务端，返回一个准备好的状态用于之后的查询和命令。返回值可以同时执行多个查询和命令。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
func prepareinsert(){
   sqlStr :=`insert into user(name, age)values(?,?)`//把sql语句先发给MySQL预处理
   stmt, err := db.Prepare(sqlStr)
   if err!=nil {
      fmt.Printf("prepare failed,err:%v\n", err)
   }
   defer stmt.Close()
   var m = map[string]int{
      "王铁柱":33,
      "田二妞":32,
   }
   //后续只需要拿到stmt去执行一些操作
   for k, v :=range m{
      stmt.Exec(k,v)
   }
}

对应关系：

数据表<—>结构体

数据行<—>结构体实例

字段<—>结构体字段

1
2
3
4
5
6
7
8
9
10
type UserInfo struct{
	ID uint
	Name string
	Gender string
	Hobby string
}
func main(){
	u1:=UserInfo{1,"Polaris","男","足球"}
	//将u1数据存入数据库
}