![image.png](/static/img/2e86909040e9a3045b63c4e41c3ed0ee.image.webp)



![image.png](/static/img/a17860755c964f37ff2818f67268db70.image.webp)

![image.png](/static/img/7d927d5b31c7064d4740c51a276e88da.image.webp)

```sql
CREATE TABLE Users (
  user_id INT PRIMARY KEY,
  join_date DATE,
  favorite_brand VARCHAR(250)
);

CREATE TABLE Orders (
  order_id INT PRIMARY KEY,
  order_date DATE,
  item_id INT,
  buyer_id INT,
  seller_id INT,
  FOREIGN KEY (item_id) REFERENCES Items(item_id),
  FOREIGN KEY (buyer_id) REFERENCES Users(user_id),
  FOREIGN KEY (seller_id) REFERENCES Users(user_id)
);

CREATE TABLE Items (
  item_id INT PRIMARY KEY,
  item_brand VARCHAR(250)
);

INSERT INTO Users (user_id, join_date, favorite_brand)
VALUES
  (1, '2018-01-01', 'Lenovo'),
  (2, '2018-02-09', 'Samsung'),
  (3, '2018-01-19', 'LG'),
  (4, '2018-05-21', 'HP');

INSERT INTO Orders (order_id, order_date, item_id, buyer_id, seller_id)
VALUES
  (1, '2019-08-01', 4, 1, 2),
  (2, '2018-08-02', 2, 1, 3),
  (3, '2019-08-03', 3, 2, 3),
  (4, '2018-08-04', 1, 4, 2),
  (5, '2018-08-04', 1, 3, 4),
  (6, '2019-08-05', 2, 2, 4);

INSERT INTO Items (item_id, item_brand)
VALUES
  (1, 'Samsung'),
  (2, 'Lenovo'),
  (3, 'LG'),
  (4, 'HP');

select * from Items;

select Users.user_id as buyer_id, join_date, ifnull(UserBuy.cnt, 0) as orders_in_2019
from Users
left join (
    select buyer_id, count(order_id) cnt
    from Orders
    where order_date between '2019-01-01' and '2019-12-31'
    group by buyer_id
) UserBuy
on Users.user_id = UserBuy.buyer_id


SELECT
  u.user_id AS buyer_id,
  u.join_date,
  COUNT(CASE WHEN YEAR(o.order_date) = 2019 THEN 1 ELSE NULL END) AS orders_in_2019
FROM
  Users u
LEFT JOIN
  Orders o ON u.user_id = o.buyer_id
GROUP BY
  u.user_id,
  u.join_date;


select user_id as buyer_id, join_date, count(order_id) 
as orders_in_2019
from Users as u left join 
(select * from Orders where year(order_date)='2019') as o on u.user_id = o.buyer_id
group by user_id

```


![image.png](/static/img/06201021231a0fedbf6d5c91d34f2ec0.image.webp)

![image.png](/static/img/ade58a6abce53ffe119c8c120635589d.image.webp)

```sql

CREATE TABLE Products (
  product_id INT,
  new_price INT,
  change_date DATE,
  PRIMARY KEY (product_id, change_date)
);

INSERT INTO Products (product_id, new_price, change_date)
VALUES
  (1, 20, '2019-08-14'),
  (2, 50, '2019-08-14'),
  (1, 30, '2019-08-15'),
  (1, 35, '2019-08-16'),
  (2, 65, '2019-08-17'),
  (3, 20, '2019-08-18');



select p1.product_id, ifnull(p2.new_price, 10) as price
from (
    select distinct product_id
    from Products
) as p1 -- 所有的产品
left join (
    select product_id, new_price
    from Products
    where (product_id, change_date) in (
        select product_id, max(change_date)
        from Products
        where change_date <= '2019-08-16'
        group by product_id
    )
) as p2 -- 在 2019-08-16 之前有过修改的产品和最新的价格
on p1.product_id = p2.product_id


SELECT
    product_id,
    IFNULL(new_price, 10) AS price
FROM
    Products
    RIGHT JOIN (
        SELECT
            product_id,
            MAX(IF(change_date <= '2019-08-16', change_date, NULL)) AS change_date
        FROM
            Products
        GROUP BY
            product_id
    ) AS t2 USING(product_id, change_date);
    


SELECT DISTINCT product_id, IF(filter_date IS NULL, 10, new_price) AS price
FROM (
  SELECT *, RANK() OVER(PARTITION BY product_id ORDER BY filter_date DESC) AS RANKING
  FROM (
    SELECT *, IF(change_date > '2019-08-16', NULL, change_date) AS filter_date
    FROM Products
  ) T
) TT
WHERE TT.RANKING = 1
```

`only_full_group_by`是MySQL中的一个SQL模式，它影响了GROUP BY子句的行为。在默认情况下，MySQL允许在GROUP BY子句中只列出部分列，并且可以在SELECT列表中引用未在GROUP BY子句中列出的非聚合列。

但是，启用`only_full_group_by`模式后，MySQL要求GROUP BY子句中列出的列必须包含所有未在聚合函数中使用的非聚合列。换句话说，所有未聚合的列必须在GROUP BY子句中显式列出。

这个模式的目的是确保查询结果的一致性和可预测性。在启用`only_full_group_by`模式的情况下，如果查询中的GROUP BY子句不符合规定，MySQL将抛出错误，以避免可能产生模糊或不确定结果的查询。

需要注意的是，`only_full_group_by`模式在MySQL 5.7.5及更高版本中默认启用。如果需要在该模式下使用非聚合列，必须将这些列添加到GROUP BY子句中或使用聚合函数对它们进行处理。

可以通过以下方式查看或更改SQL模式设置：

```sql
-- 查看当前SQL模式设置
SELECT @@sql_mode;

-- 更改SQL模式设置（示例）
SET sql_mode = 'modes_to_enable';
```

在`modes_to_enable`中，可以指定所需的SQL模式，多个模式之间使用逗号分隔。要启用`only_full_group_by`模式，可以将其包含在`modes_to_enable`中。

**using**
`USING`关键字在SQL中用于指定两个或多个表之间的共享列或连接列。

在`JOIN`操作中，`USING`关键字可以代替`ON`关键字来指定连接条件。它适用于两个表具有相同列名且希望使用这些列进行连接的情况。

以下是使用`USING`关键字进行连接的示例：

```sql
SELECT *
FROM table1
JOIN table2 USING (common_column);
```

在上述示例中，`table1`和`table2`是要连接的两个表，它们都有一个名为`common_column`的列。通过使用`USING`关键字，我们指定了连接条件为两个表中的`common_column`列。

使用`USING`关键字的连接具有以下特点：

- 只能指定连接条件中的共享列，且这些列的名称在连接的两个表中必须相同。
- 在连接结果中，共享列只出现一次，而不会重复显示。
- `USING`关键字只能用于内连接（INNER JOIN）和自然连接（NATURAL JOIN），不能用于外连接（LEFT JOIN、RIGHT JOIN等）。


使用`USING`关键字进行连接时，如果两个表中的共享列有相同的列名但具有不同的数据类型，则可能会导致语法错误或意外的结果。因此，在使用`USING`关键字连接表之前，应确保共享列具有相同的列名和数据类型。

SQL语句练习（10.15）

常用的加密和密码验证



```go
package hash 
  
 import ( 
         "fmt" 
         "golang.org/x/crypto/bcrypt" 
 ) 
  
 func BcryptHash(password string) string { 
  
         bytes, err := bcrypt.GenerateFromPassword([]byte(password), 14) 
         if err != nil { 
                 fmt.Println("加密失败") 
                 panic(err) 
         } 
         return string(bytes) 
 } 
  
 func BcryptCheck(password, hash string) bool { 
         err := bcrypt.CompareHashAndPassword([]byte(hash), []byte(password)) 
         return err == nil 
 } 
  
 func BcryptIsHashed(str string) bool { 
         // bcrypt 加密后的长度等于 60 
         return len(str) == 60 
 }
```

sha256加密

之前讲解过两种升级方式和原理



```go
package ws 
  
 import ( 
         "github.com/gorilla/websocket" 
         "net/http" 
 ) 
  
 var upgrade = websocket.Upgrader{ 
         ReadBufferSize:  1024, 
         WriteBufferSize: 1024, 
         CheckOrigin: func(r *http.Request) bool { 
                 return true 
         }, 
 } 
  
 var ( 
         Websocket *websocket.Conn 
         err       error 
 ) 
  
 func App(w http.ResponseWriter, r *http.Request) (*websocket.Conn, error) { 
         Websocket, err = upgrade.Upgrade(w, r, nil) 
         return Websocket, err 
 }
```

websocket快速升级

用go-redis连接redis



```go
package redis 
  
 import ( 
         "github.com/go-redis/redis" 
         "im-services/internal/config" 
         "log" 
         "time" 
 ) 
  
 var RedisDB *redis.Client 
  
 // InitClient redis 连接 
 func InitClient() { 
         RedisDB = redis.NewClient(&redis.Options{ 
                 Network:      "tcp", 
                 Addr:         config.Conf.Redis.Host + ":" + config.Conf.Redis.Port, 
                 Password:     config.Conf.Redis.Password, 
                 DB:           config.Conf.Redis.DB, 
                 PoolSize:     config.Conf.Redis.Poll, //连接池 默认为4倍cpu数 
                 MinIdleConns: config.Conf.Redis.Conn, //在启动阶段创建指定数量的Idle连接，并长期维持idle状态的连接数不少于指定数量 
                 DialTimeout:  5 * time.Second, 
                 ReadTimeout:  5 * time.Second, 
                 WriteTimeout: 5 * time.Second, 
                 PoolTimeout:  5 * time.Second, 
         }) 
         _, err := RedisDB.Ping().Result() 
  
         if err != nil { 
                 log.Fatalln(err) 
         } 
 }
```

redis连接

利用viper进行配置热加载示例，以后直接复制粘贴即可





```go
package config 
  
 import ( 
         "github.com/fsnotify/fsnotify" 
         "github.com/spf13/viper" 
 ) 
  
 type Config struct { 
         Server ServerConf 
         Mysql  MysqlConf 
         Log    LogConf 
         JWT    JWTConf 
         Redis  RedisConf 
         Mail   MailConf 
         Kafka  KafkaConf 
         Nsq    NsqConf 
         QiNiu  QiNiuConfig 
         Github GithubConfig 
         GoBot  BotConfig 
         Gitee  GiteeConfig 
 } 
  
 type ServerConf struct { 
         Name          string `json:"name"` 
         Listen        string `json:"listen"` 
         Mode          string `json:"mode"` 
         Env           string `json:"env"` 
         Lang          string `json:"lang"` 
         CoroutinePoll int    `json:"coroutinePoll"` 
         Node          string `json:"node"` 
         ServiceOpen   bool   `json:"serviceOpen"` 
         GrpcListen    string `json:"grpcListen"` 
         FilePath      string `json:"filePath"` 
 } 
  
 type JWTConf struct { 
         Secret string `json:"secret"` 
         Ttl    int64  `json:"ttl"` 
 } 
  
 type MysqlConf struct { 
         Host     string `json:"host"` 
         Port     string `json:"port"` 
         Username string `json:"username"` 
         Password string `json:"password"` 
         Database string `json:"database"` 
         Charset  string `json:"charset"` 
 } 
  
 type LogConf struct { 
         Level     string `json:"level"` 
         Type      string `json:"type"` 
         FileName  string `json:"filename"` 
         MaxSize   int    `json:"maxSize"` 
         MaxBackup int    `json:"maxBackup"` 
         MaxAge    int    `json:"maxAge"` 
         Compress  bool   `json:"compress"` 
 } 
  
 type RedisConf struct { 
         Host     string `json:"host"` 
         Port     string `json:"port"` 
         Password string `json:"password"` 
         DB       int    `json:"db"` 
         Poll     int    `json:"poll"` 
         Conn     int    `json:"conn"` 
 } 
  
 type MailConf struct { 
         Driver     string `json:"driver"` 
         Host       string `json:"host"` 
         Name       string `json:"name"` 
         Port       int    `json:"port"` 
         Password   string `json:"password"` 
         Encryption string `json:"encryption"` 
         FromName   string `json:"fromName"` 
 } 
  
 type KafkaConf struct { 
         Host string `json:"host"` 
         Port string `json:"port"` 
 } 
  
 type NsqConf struct { 
         LookupHost string `json:"lookupHost"` 
         NsqHost    string `json:"nsqHost"` 
 } 
  
 type QiNiuConfig struct { 
         AccessKey string `json:"access_key"` 
         SecretKey string `json:"secretKey"` 
         Bucket    string `json:"bucket"` 
         Domain    string `json:"domain"` 
 } 
  
 type GithubConfig struct { 
         ClientId     string `json:"clientId"` 
         ClientSecret string `json:"clientSecret"` 
         RedirectUrl  string `json:"redirectUrl"` 
 } 
  
 type BotConfig struct { 
         Email    string `json:"email"` 
         Password string `json:"password"` 
         Name     string `json:"name"` 
         Avatar   string `json:"avatar"` 
 } 
 type GiteeConfig struct { 
         ClientId     string `json:"clientId"` 
         ClientSecret string `json:"clientSecret"` 
         RedirectUrl  string `json:"redirectUrl"` 
 } 
  
 var Conf = &Config{} 
  
 // InitConfig 初始化配置函数 
 func InitConfig(configPath string) *Config { 
  
         // 设置文件类型 
         viper.SetConfigType("yaml") 
  
         // 读取文件配置 
         viper.SetConfigFile(configPath) 
  
         // 判断是否读取成功 
         err := viper.ReadInConfig() 
         if err != nil { 
                 panic(err) 
         } 
  
         // 将配置解析到Struct 
         err = viper.Unmarshal(&Conf) 
         if err != nil { 
                 panic(err) 
         } 
  
         // 热加载配置信息 
         viper.WatchConfig() 
         viper.OnConfigChange(func(in fsnotify.Event) { 
                 err = viper.Unmarshal(&Conf) 
                 if err != nil { 
                         panic(err) 
                 } 
         }) 
  
         return Conf 
  
 } 
  
 func IsLocal() bool { 
         return Conf.Log.Level == "local" 
 } 
  
 func IsProduction() bool { 
         return Conf.Log.Level == "production" 
 } 
  
 func IsTesting() bool { 
         return Conf.Log.Level == "testing" 
 }
```