函数

MySQL中提供了很多函数，为我们的SQL操作提供便利，例如：

mysql> select * from d1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 武沛齐    |
|  3 | xxx       |
|  4 | pyyu      |
+----+-----------+
3 rows in set (0.00 sec)

mysql> select count(id), max(id),min(id),avg(id) from d1;
+-----------+---------+---------+---------+
| count(id) | max(id) | min(id) | avg(id) |
+-----------+---------+---------+---------+
|         3 |       4 |       1 |  2.6667 |
+-----------+---------+---------+---------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> select id,reverse(name) from d1;
+----+---------------+
| id | reverse(name) |
+----+---------------+
|  1 | 齐沛武        |
|  3 | xxx           |
|  4 | uyyp          |
+----+---------------+
3 rows in set (0.00 sec)

mysql> select id, reverse(name),concat(name,name), NOW(), DATE_FORMAT( NOW(),'%Y-%m-%d %H:%i:%s')  from d1;
+----+---------------+--------------------+---------------------+-----------------------------------------+
| id | reverse(name) | concat(name,name)  | NOW()               | DATE_FORMAT( NOW(),'%Y-%m-%d %H:%i:%s') |
+----+---------------+--------------------+---------------------+-----------------------------------------+
|  1 | 齐沛武        | 武沛齐武沛齐       | 2021-05-27 09:18:07 | 2021-05-27 09:18:07                     |
|  3 | xxx           | xxxxxx             | 2021-05-27 09:18:07 | 2021-05-27 09:18:07                     |
|  4 | uyyp          | pyyupyyu           | 2021-05-27 09:18:07 | 2021-05-27 09:18:07                     |
+----+---------------+--------------------+---------------------+-----------------------------------------+
3 rows in set (0.00 sec)

mysql> select concat("alex","sb");
+---------------------+
| concat("alex","sb") |
+---------------------+
| alexsb              |
+---------------------+
1 row in set (0.00 sec)

mysql> select sleep(1);
+----------+
| sleep(1) |
+----------+
|        0 |
+----------+
1 row in set (1.00 sec)

部分函数列表：

sql

CHAR_LENGTH(str)
    返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
    对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。

CONCAT(str1,str2,...)
    字符串拼接
    如有任何一个参数为NULL ，则返回值为 NULL。
CONCAT_WS(separator,str1,str2,...)
    字符串拼接（自定义连接符）
    CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。

CONV(N,from_base,to_base)
    进制转换
    例如：
        SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示

FORMAT(X,D)
    将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点，或不含小数部分。
    例如：
        SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
INSERT(str,pos,len,newstr)
    在str的指定位置插入字符串
        pos：要替换位置其实位置
        len：替换的长度
        newstr：新字符串
    特别的：
        如果pos超过原字符串长度，则返回原字符串
        如果len超过原字符串长度，则由新字符串完全替换
INSTR(str,substr)
    返回字符串 str 中子字符串的第一个出现位置。

LEFT(str,len)
    返回字符串str 从开始的len位置的子序列字符。

LOWER(str)
    变小写

UPPER(str)
    变大写

LTRIM(str)
    返回字符串 str ，其引导空格字符被删除。
RTRIM(str)
    返回字符串 str ，结尾空格字符被删去。
SUBSTRING(str,pos,len)
    获取字符串子序列

LOCATE(substr,str,pos)
    获取子序列索引位置

REPEAT(str,count)
    返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。
    若 count <= 0,则返回一个空字符串。
    若str 或 count 为 NULL，则返回 NULL 。
REPLACE(str,from_str,to_str)
    返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
REVERSE(str)
    返回字符串 str ，顺序和字符顺序相反。
RIGHT(str,len)
    从字符串str 开始，返回从后边开始len个字符组成的子序列

SPACE(N)
    返回一个由N空格组成的字符串。

SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
    不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

    mysql> SELECT SUBSTRING('Quadratically',5);
        -> 'ratically'

    mysql> SELECT SUBSTRING('foobarbar' FROM 4);
        -> 'barbar'

    mysql> SELECT SUBSTRING('Quadratically',5,6);
        -> 'ratica'

    mysql> SELECT SUBSTRING('Sakila', -3);
        -> 'ila'

    mysql> SELECT SUBSTRING('Sakila', -5, 3);
        -> 'aki'

    mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
        -> 'ki'

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
    返回字符串 str ， 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。

    mysql> SELECT TRIM('  bar   ');
            -> 'bar'

    mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
            -> 'barxxx'

    mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
            -> 'bar'

    mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
            -> 'barx'

自定义函数。

(专业DBA搞这个，暂时了解即可)

创建函数

sql

delimiter $$
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    declare maxId int;
    select max(id) from big into maxId;
    
    set num = i1 + i2 + maxId;
    return(num);
END $$
delimiter ;

执行函数

sql

select f1(11,22);

select f1(11,id),name from d1;

删除函数
sql
```
drop function f1;
```

存储过程（

存储过程（SQL Stored Procedure），是一个存储在MySQL中的SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。

为什么使用存储过程

通常，复杂的业务逻辑需要多条SQL 语句。
使用存储过程只在创建（类似于写一个函数）时进行编译，并将其存储在sql端服务器，以后每次执行存储过程都不需再重新编译。
而不使用存储过程时，SQL 语句每执行一次就编译一次。
因此使用存储过程可以大大提高数据库执行速度。】、
- 减少通过网络连接发过去的数据量，比如实现一个复杂业务逻辑要100条sql语句，创建存储过程并将其保存在sql端服务器后，只需要发过去存储过程名，就可以实现业务逻辑。
- 减少了网络传输的数据量，自然就快了。
优点就是缺点：当要修改存储过程时（类似于函数），需要在sql端和程序端两边修改，比较麻烦。

存储过程操作(增删改)

创建存储过程

sql

delimiter $$
create procedure p1()
BEGIN
    select * from d1;
END $$
delimiter ;

执行存储过程

sql客户端执行

sql

call p1();

python端执行

python

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p1')
result = cursor.fetchall()

cursor.close()
conn.close()

print(result)

删除存储过程
sql
```
drop procedure proc_name;
```

修改存储过程

sql

DELIMITER $$
ALTER PROCEDURE p1()
BEGIN
    -- 修改后的存储过程逻辑
    SELECT id FROM d1;
END $$
DELIMITER ;

查看当前所有的存储过程
sql
```
SHOW PROCEDURE STATUS;
```

参数类型

存储过程的参数可以有如下三种：

in，仅用于传入参数用
out，仅用于返回值用
inout，既可以传入又可以当作返回值

sql

delimiter $$
create procedure p2(
    in i1 int,
    in i2 int,
    inout i3 int,
    out r1 int
)
BEGIN
    DECLARE temp1 int;
    DECLARE temp2 int default 0;
    
    set temp1 = 1;

    set r1 = i1 + i2 + temp1 + temp2;
    
    set i3 = i3 + 100;

end $$
delimiter ;

sql

set @t1 =4;
set @t2 = 0;
CALL p2 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;

python

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 执行存储过程
cursor.callproc('p2',args=(1, 22, 3, 4))

# 获取执行完存储的参数
cursor.execute("select @_p2_0,@_p2_1,@_p2_2,@_p2_3")
result = cursor.fetchall()
# {"@_p2_0":11 }

cursor.close()
conn.close()

print(result)

返回值 & 结果集

sql

delimiter $$
create procedure p3(
    in n1 int,
    inout n2 int,
    out n3 int
)
begin
    set n2 = n1 + 100;
    set n3 = n2 + n1 + 100;
    select * from d1;
end $$
delimiter ;

sql

set @t1 =4;
set @t2 = 0;
CALL p3 (1,@t1, @t2);
SELECT @t1,@t2;

python

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p3',args=(22, 3, 4))
table = cursor.fetchall() # 得到执行存储过中的结果集

# 获取执行完存储的参数
cursor.execute("select @_p3_0,@_p3_1,@_p3_2")
rets = cursor.fetchall()

cursor.close()
conn.close()

print(table)
print(rets)

事务 & 异常

事务，成功都成功，失败都失败。

sql

delimiter $$
create PROCEDURE p4(
    OUT p_return_code tinyint
)
BEGIN 
  DECLARE exit handler for sqlexception 
  BEGIN 
    -- ERROR 
    set p_return_code = 1; 
    rollback; 
  END; 
 
  DECLARE exit handler for sqlwarning 
  BEGIN 
    -- WARNING 
    set p_return_code = 2; 
    rollback; 
  END; 
 
  START TRANSACTION;  -- 开启事务
    delete from d1;
    insert into tb(name)values('seven');
  COMMIT;  -- 提交事务
 
  -- SUCCESS 
  set p_return_code = 0; 
 
  END $$
delimiter ;

sql

set @ret =100;
CALL p4(@ret);
SELECT @ret;

python

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p4',args=(100))

# 获取执行完存储的参数
cursor.execute("select @_p4_0")
rets = cursor.fetchall()

cursor.close()
conn.close()

print(table)
print(rets)

游标

sql

delimiter $$
create procedure p5()
begin 
    declare sid int;
    declare sname varchar(50); 
    declare done int default false;


    declare my_cursor CURSOR FOR select id,name from d1;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    open my_cursor;
        xxoo: LOOP
            fetch my_cursor into sid,sname;
            IF done then 
                leave xxoo;
            END IF;
            insert into t1(name) values(sname);
        end loop xxoo;
    close my_cursor;
end $$
delimiter ;

sql

call p5();

视图

视图其实是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。

使用视图时必须给子表起别名！！！！不然报错1025

sql

SELECT
    *
FROM
    (SELECT nid,name FROM tb1 WHERE nid > 2) AS A
WHERE
    A.name > 'alex';

创建视图

sql

create view v1 as select id,name from d1 where id > 1;

使用视图

sql

select * from v1;

-- select * from (select id,name from d1 where id > 1) as v1;

删除视图
sql
```
drop view v1;
```
修改视图
sql
```
alter view v1 as SQL语句
```

查看当前所有视图

table_schema：视图所属的库。

sql

SELECT * FROM INFORMATION_SCHEMA.VIEWS 

SELECT * FROM INFORMATION_SCHEMA.VIEWS where TABLE_SCHEMA = 'Practice';

注意：基于视图只能查询，针对视图不能执行增加、修改、删除。如果源表发生变化，视图表也会发生变化。

触发器

对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器。

sql

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

sql

DROP TRIGGER tri_after_insert_tb1;

在MySQL触发器中，NEW和OLD关键字是指代正在操作的行的关键字。当触发器是INSERT类型时，NEW表示要插入的行。当触发器是UPDATE类型时，OLD表示被更新的行，而NEW表示更新后的行。当触发器是DELETE类型时，OLD表示将要被删除的行。

示例：

在 t1 表中插入数据之前，先在 t2 表中插入一行数据。

sql

delimiter $$
CREATE TRIGGER tri_before_insert_t1 BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
	-- NEW.id  NEW.name  NEW.email
	-- INSERT INTO t2 (name) VALUES();
	IF NEW.name = 'alex' THEN
        INSERT INTO t2 (name) VALUES(NEW.id);
    END IF;

END $$
delimiter ;

insert into t1(id,name,email)values(1,"alex","xxx@qq.com")

在t1表中删除数据之后，再在t2表中插入一行数据。

sql

delimiter $$
CREATE TRIGGER tri_after_insert_t1 AFTER DELETE ON t1 FOR EACH ROW
BEGIN

IF OLD.name = 'alex' THEN
    INSERT INTO t2 (name) VALUES(OLD.id);
END IF;

END $$
delimiter ;

特别的：NEW表示新数据，OLD表示原来的数据。

总结

对于Python开发人员，其实在开发过程中触发器、视图、存储过程用的很少（以前搞C#经常写存储过程），最常用的其实就是正确的使用索引以及常见的函数。

索引，加速查找 & 约束。
- innodb和myisam的区别，聚簇索引和非聚簇索引。
- 常见的索引：主键、唯一、普通。
- 命中索引
- 执行explain计划
函数，提供了一些常见操作 & 配合SQL语句，执行后返回结果。
存储过程，一个SQL语句的集合，可以出发复杂的情况，最终可以返回结果 + 数据集。
视图，一个虚拟的表。
触发器，在表中数据行执行前后自定义一些操作。

作业

根据你掌握的索引知识重新设计 day27 博客系统的表结构，让查询数据库的速度可以变得更快。
了解函数、存储过程、触发器、视图。

事务

(要成功都成功，要失败都失败)

innodb引擎中支持事务，myisam不支持。

sql

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(32) DEFAULT NULL,
  `amount` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

例如：李杰给武沛齐转账 100，那就会涉及2个步骤。

李杰账户减100
武沛齐账户加 100

这两个步骤必须同时完成才算完成，并且如果第一个完成、第二步失败，还是回滚到初始状态。

事务，就是来解决这种情况的。大白话：要成功都成功；要失败都失败。

事务的具有四大特性（ACID）：

原子性（Atomicity）

原子性是指事务包含的所有操作不可分割，要么全部成功，要么全部失败回滚。

一致性（Consistency）

执行的前后数据的完整性保持一致。

隔离性（Isolation）

一个事务执行的过程中,不应该受到其他事务的干扰。

持久性（Durability）

事务一旦结束,数据就持久到数据库

MySQL客户端

sql

mysql> select * from users;
+----+---------+---------+
| id | name    | amount  |
+----+---------+---------+
|  1 | wupeiqi |    5    |
|  2 |  alex   |    6    |
+----+---------+---------+
3 rows in set (0.00 sec)

mysql> begin;  -- 开启事务 start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update users set amount=amount-2 where id=1;   -- 执行操作
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update users set amount=amount+2 where id=2;   -- 执行操作
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;  -- 提交事务  
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users;
+----+---------+---------+
| id | name    | amount  |
+----+---------+---------+
|  1 | wupeiqi |    3    |
|  2 |  ale x  |    8    |
+----+---------+---------+
3 rows in set (0.00 sec)

sql

mysql> select * from users;
+----+---------+---------+
| id | name    | amount  |
+----+---------+---------+
|  1 | wupeiqi |    3    |
|  2 |  ale x  |    8    |
+----+---------+---------+
3 rows in set (0.00 sec)

mysql> begin; -- 开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> update users set amount=amount-2 where id=1; -- 执行操作（此时数据库中的值已修改）
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback; -- 事务回滚（回到原来的状态）
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users;
+----+---------+---------+
| id | name    | amount  |
+----+---------+---------+
|  1 | wupeiqi |    3    |
|  2 |  ale x  |    8    |
+----+---------+---------+
3 rows in set (0.00 sec)

Python代码（！）

sql

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
cursor = conn.cursor()

# 开启事务
conn.begin()

try:
    cursor.execute("update users set amount=1 where id=1")
    int('asdf')
    cursor.execute("update tran set amount=2 where id=2")
except Exception as e:
    # 回滚
    print("回滚")
    conn.rollback()
else:
    # 提交
    print("提交")
    conn.commit()

cursor.close()
conn.close()

数据库连接池

在操作数据库时需要使用数据库连接池。

pip3.9 install pymysql
pip3.9 install dbutils

python

import threading
import pymysql
from dbutils.pooled_db import PooledDB

MYSQL_DB_POOL = PooledDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxconnections=5,  # 连接池允许的最大连接数，0和None表示不限制连接数
    mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
    maxcached=3,  # 链接池中最多闲置的链接，0和None不限制
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0,
    # ping MySQL服务端，检查是否服务可用。
    # 如：0 = None = never, 1 = default = whenever it is requested, 
    # 2 = when a cursor is created, 4 = when a query is executed, 7 = always
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root123',
    database='userdb',
    charset='utf8'
)


def task():
    # 去连接池获取一个连接
    conn = MYSQL_DB_POOL.connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    cursor.execute('select sleep(2)')
    result = cursor.fetchall()
    print(result)

    cursor.close()
    # 将连接交换给连接池
    conn.close()

def run():
    for i in range(10):
        t = threading.Thread(target=task)
        t.start()


if __name__ == '__main__':
    run()

SQL工具类

基于数据库连接池开发一个公共的SQL操作类，方便以后操作数据库。

单例和方法

python

# db.py
import pymysql
from dbutils.pooled_db import PooledDB


class DBHelper(object):

    def __init__(self):
        # TODO 此处配置，可以去配置文件中读取。
        self.pool = PooledDB(
            creator=pymysql,  # 使用链接数据库的模块
            maxconnections=5,  # 连接池允许的最大连接数，0和None表示不限制连接数
            mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
            maxcached=3,  # 链接池中最多闲置的链接，0和None不限制
            blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
            setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
            ping=0,
            # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
            host='127.0.0.1',
            port=3306,
            user='root',
            password='root123',
            database='userdb',
            charset='utf8'
        )

    def get_conn_cursor(self):
        conn = self.pool.connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        return conn, cursor

    def close_conn_cursor(self, *args):
        for item in args:
            item.close()

    def exec(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()

        cursor.execute(sql, kwargs)
        conn.commit()

        self.close_conn_cursor(conn, cursor)

    def fetch_one(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()

        cursor.execute(sql, kwargs)
        result = cursor.fetchone()

        self.close_conn_cursor(conn, cursor)
        return result

    def fetch_all(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()

        cursor.execute(sql, kwargs)
        result = cursor.fetchall()

        self.close_conn_cursor(conn, cursor)

        return result


db = DBHelper()

python

from db import db

db.exec("insert into d1(name) values(%(name)s)", name="武沛齐666")

ret = db.fetch_one("select * from d1")
print(ret)

ret = db.fetch_one("select * from d1 where id=%(nid)s", nid=3)
print(ret)

ret = db.fetch_all("select * from d1")
print(ret)

ret = db.fetch_all("select * from d1 where id>%(nid)s", nid=2)
print(ret)

我写的

python

import pymysql
from dbutils.pooled_db import PooledDB


class SQLHelper:
    def __init__(self):
        self.pool = PooledDB(
            creator=pymysql,  # 使用链接数据库的模块
            maxconnections=5,  # 连接池允许的最大连接数，0和None表示不限制连接数
            mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
            maxcached=3,  # 链接池中最多闲置的链接，0和None不限制
            blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
            setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
            ping=0,
            # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
            host='127.0.0.1',
            port=3306,
            user='root',
            password='root123',
            database='todaydb',
            charset='utf8'
        )

    def get_conn_cursor(self):
        conn = self.pool.connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        return conn, cursor

    def close_cursor_conn(self, *args):
        for item in args:
            item.close()

    def exec(self, sql, *args, **kwargs):
        conn, cursor = self.get_conn_cursor()
        cursor.execute(sql, args)
        conn.commit()
        self.close_cursor_conn(cursor, conn)

    def fetchone(self, sql, *args, **kwargs):
        conn, cursor = self.get_conn_cursor()
        cursor.execute(sql, *args, **kwargs)
        res = cursor.fetchone()
        self.close_cursor_conn(cursor, conn)
        return res

    def fetchall(self, sql, *args, **kwargs):
        conn, cursor = self.get_conn_cursor()
        cursor.execute(sql, *args, **kwargs)
        res = cursor.fetchall()
        self.close_cursor_conn(cursor, conn)
        return res

python

helper = SQLHelper()
helper.exec('update test.md set name=%s where id =%s', 'fxx', 1)
res = helper.fetchone('select * from test.md where id =1')
print(res)

# {'id': 1, 'name': 'fxx', 'count': 5}

上下文管理

如果你想要让他也支持 with 上下文管理。

python

with 获取连接：
	执行SQL（执行完毕后，自动将连接交还给连接池）

python

# db_context.py
import threading
import pymysql
from dbutils.pooled_db import PooledDB

POOL = PooledDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxconnections=5,  # 连接池允许的最大连接数，0和None表示不限制连接数
    mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
    maxcached=3,  # 链接池中最多闲置的链接，0和None不限制
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0,
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root123',
    database='userdb',
    charset='utf8'
)


class Connect(object):
    def __init__(self):
        self.conn = conn = POOL.connection()
        self.cursor = conn.cursor(pymysql.cursors.DictCursor)

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.cursor.close()
        self.conn.close()

    def exec(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        self.conn.commit()

    def fetch_one(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        result = self.cursor.fetchone()
        return result

    def fetch_all(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        result = self.cursor.fetchall()
        return result

python

from db_context import Connect

with Connect() as obj:
    # print(obj.conn)
    # print(obj.cursor)
    ret = obj.fetch_one("select * from d1")
    print(ret)

    ret = obj.fetch_one("select * from d1 where id=%(id)s", id=3)
    print(ret)

交叉连接和自链接

交叉连接

【SQL入门教程第27集交叉连接和自连接-哔哩哔哩】 https://b23.tv/EINZLkR

但需要两张表的全部的一一对应信息时可以用，比如：

一张商品信息表
一张商品包装表
交叉连接后，可以得到商品和包装的所有配对可能性。

sql

-- 创建商品表和包装表
create table goods(
gid int primary key auto_increment,
gname varchar(32)
)default charset = utf8;

create table packages(
pid int primary key auto_increment,
pname varchar(32)
)default charset = utf8;

-- 插入模拟数据
insert into goods(gname) values('A'),('B'),('C');
insert into packages(pname) values('a'),('b');


-- 交叉连接语法
select * from goods cross join packages;

自链接

https://developer.aliyun.com/article/870215 阿里

自连接就是结构和数据完全一样的两张表进行连接。
在做数据处理的时候，通常会给它们分别重命名来加以区分，然后关联
不分别起名字，sql脸盲。
使用自连接的目的并不是自己和自己关联，而是和表里的其他字段进行组合。

self-join(很常用)

自链接就是两张表自乘，最重要的是可以实现分组的效果
记得排除两张表一样的数据

sql

-- 选择世界表中的大洲、名称和面积，其中面积大于等于与同一大洲的所有国家的面积。

select continent,name,area 
from world x
where area >= all(select area from world y 
                  where x.continent = y.continent )

sql

-- 某些国家的人口是其所有邻国（同一大洲内）人口的三倍以上。给出这些国家和它们所在的大洲。
select name ,continent from world x
where population >= all(select population*3 from world y
where x.continent = y.continent and x.name <> y.name )

举例：黑产行为筛选

已知同一用户在移动端（platform=21）连续点击两次时间间隔不高于2秒，则可能产生黑产行为
筛选出21年3月份所有有可能为黑产行为的点击记录

sql

-- 创建模拟表

create table if not exists tencent_video_click(
vuid int,
`date` date,                 -- 点击日期
`time` int,                  -- '点击时间时间戳格式',
platform int default 21      -- 21：移动端。22:网页端
);
select unix_timestamp('2021-03-01');
-- 插入造的数据
insert into tencent_video_click values
(1, '2021-03-01', 1614528000, 21),
(1, '2021-03-01', 1614528001, 22),
(1, '2021-03-01', 1614528001, 21),
(2, '2021-03-01', 1614528004, 21),
(3, '2021-03-01', 1614528000, 21),
(2, '2021-03-01', 1614528005, 21),
(3, '2021-03-01', 1614528000, 22),
(4, '2021-03-01', 1614528007, 21),
(4, '2021-03-01', 1614528009, 22),
(4, '2021-03-01', 1614528010, 21);

-- 要求：已知同一用户在移动端连续点击两次时间间隔不高于2秒，则可能产生黑产行为
-- 筛选出21年3月份所有有可能为黑产行为的点击记录

注意：

上述例子中，第一次点击表和第二次点击表只是人为命名的，并不是真的按照时间顺序产生的表
所以，自链接后，要将第二次点击时间大与第一次点击时间作为筛选条件
实际上，没有任何筛选条件下的自链接就是表的自乘，因此注意使用筛选条件进行去重！！！。
面试题第十题就是自链接的题
自链接前：

自链接后：

因此自链接的重点是利用筛选条件，达到目的。

其他

navicat，是一个桌面应用，让我们可以更加方便的管理MySQL数据库。

mac系统：https://www.macdo.cn/17030.html
win系统：
- 链接: https://pan.baidu.com/s/13cjbrBquz9vjVqKgWoCQ1w 密码: qstp
- 链接: https://pan.baidu.com/s/1JULIIwQA5s0qN98KP8UXHA 密码: p18f

总结

本节内容比较重要，也是开发中经常会使用到的技能。

事务，解决批量操作同时成功或失败的问题。
锁，解决并发处理的问题。
数据库连接池，解决多个人请求连接数据库的问题。
SQL工具类，解决连接数据库代码重复的问题。
navicat工具

大作业：开发博客系统

请基于你掌握的所有技能，实现 day27 博客系统的所有功能。

根据如下的业务需求设计相应的表结构，内部需涵盖如下功能。

注册
登录
发布博客
查看博客列表，显示博客标题、创建时间、阅读数量、评论数量、赞数量等。（支持分页查看）
博客详细，显示博文详细、评论等。
- 发表评论
- 赞 or 踩
- 阅读数量 + 1

可参考如下图片来设计相应的表结构。

1. 注册和登录

2. 文章列表

3. 文章详细

4. 评论 & 阅读 & 赞 & 踩

注意：假设都是一级评论（不能回复评论）。

MySQL8窗口函数

是MySQL8中新增的特性

使用场景：

TopN【排名问题】【每个课程成绩的前3名】
将表中某一指标与分组之后数学指标做对比【找到成绩低于课程成绩平均分】
累计销售额【排序操作没有归纳一说】

窗口函数显示数据的时候不会让表中的行数发生变化，可以对数据表中的数据进行对应的分析

sql

语法：
窗口函数() over(partiton by 分组字段1, 分组字段2, .. 分组字段n
			order by 排序字段 asc|desc, 排序字段1 asc|desc...) <name> -- name是给个查询的名字

窗口函数的分类

排名的函数
1. rank()
2. dense_rank()
3. row_number()
聚合函数
sum(字段) --- 求和的
count(字段) --- 统计个数
max(字段) --- 最大值
min(字段) --- 最小值
avg(字段) -- 平均值

partition by 将数据根据指标归纳

和 group by的区别是

group by 会改变显示结果的行数【相当于按照字段折叠把同一组的数据折叠在一起】

partiton by 不会改变表显示的行数【与原表显示是一样的】只是把相同组的数据归纳纵向相连在一起

order by 就是根据指定的字段进行排序

排名开窗函数

1 查询每个班级分数最高的学生信息 2 查询个班级分数最高的前两名学生姓名

数据集：student.csv

sql

select * from stu;
-- 窗口函数
-- 1. 排名问题
-- 查询每个班级的分数最高的学生信息
select *, max(num) as 最高分 from student group by caption;

-- 查询每个班级的分数最高排名为前2的学生信息
-- 窗口函数 over(partition by 字段  order by 字段) name 

select *, rank() over(partition by caption order by num desc) 排名 from student order by 排名;

select *, dense_rank() over(partition by caption order by num desc) 排名 from student order by 排名;

select * from 
(select *, dense_rank() over(partition by caption order by num desc) 排名 from student) as e
where 排名 <= 2;

-- 练习: 使用窗口函数查找每个班级成绩最高的所有学员信息
select * from 
(select *, dense_rank() over(partition by caption order by num desc) 排名 from student) as e
where 排名 <= 1;

-- 练习:使用窗口函数查找成绩最高的前3的所有学员信息
select * from 
(select *, dense_rank() over(order by num desc) 排名 from student) as e 
where 排名 <= 3;

-- row_number 
select *, dense_rank() over(partition by cname order by num desc) 排名,
row_number() over(order by num desc) 序号 from student;

聚合开窗函数

sql

-- 聚合开窗函数
-- sum  max min avg count 

-- 练习 查询每一个学科的平均分数
select cname, avg(num) 平均分数 from student group by cname;

-- a. 每个学生的及格科目的数量
select sname, count(cname) 学科个数 from 
(select * from student where num >= 60) as e 
group by sname;
-- a. 开窗函数版
select sname, count(cname) over(partition by sname) 学科个数 from student where num >= 60;

-- b. 计算每个学生的成绩与自己的平均分的差距
select * from student;

select *, num - e.平均分 as 差 from 
(select *, avg(num) over(partition by sname) 平均分 from student) as e
order by num;

众数，中位数之类的聚合函数

众数：mode()

sql

select store_cd,
mode() within group(order by product_cd) as product_cd_mode 
from receipt 
group by store_cd
limit 10 

-- within group 后面跟的是在聚合之前要根据那个字段排序，因为求众数之前肯定要排序

分位数percentile_cont()，注意拼写，是cont，continuous的缩写，不是count

括号里写小数，比如求中位数的话，percentile_cont(0.5)

sql

select store_cd,
percentile_cont(0.5) within group(order by amount) as middle_amount
from receipt 
group by store_cd
order by middle_amount desc
limit 5

-- within group 后面跟的是在聚合之前要根据那个字段排序，因为求分位数之前肯定要排序
-- 求中位数的话就把参数设置为0.5

方差：VAR_POP() ,population variance

sql

select store_cd ,
var_pop(amount)  as amount_var
from receipt 
group by store_cd 
order by amount_var
limit 5

-- 求分散前不需要排序，所以用不着within group

标准差：STDDEV_POP(), （population standard deviation）

sql

select store_cd,
stddev_pop(amount) as amount_std
from receipt 
group by store_cd 
order by amount_std desc
limit 5

累计运算

sql

-- 订单表
create table if not exists sale_order(
id int primary key auto_increment, -- 订单id
sale_dt date, -- 订单时间
user_id int, -- 用户id
sku_id int, -- 商品类型id 
sale_count int, -- 销售数量
price int, -- 销售单价
amount int -- 销售金额
);
-- 清空数据表的内容 还把配置信息重置 如何操作 
truncate table sale_order;
insert into sale_order(sale_dt, user_id, sku_id, sale_count, price, amount)
values('2019-01-01', 1, 1001, 2, 100, 200),
('2019-01-02', 2, 1001, 1, 100, 100),
('2019-02-10', 3, 1001, 2, 80, 160),
('2019-02-11', 2, 1002, 2, 100, 200),
('2019-03-01', 3, 1002, 1, 100, 100),
('2019-03-01', 3, 1001, 1, 50, 50),
('2019-03-01', 3, 1003, 4, 100, 400);
select * from sale_order;

-- 完成累计  需要求出每个月销售量及其销售额 
-- 对数据进行归类时是按照年月来进行归类的 所以需要对时间进行处理 只包含年月
-- 如何对时间做处理 只包含年月？？？  对时间最好的处理方式 格式化 
select *, date_format(sale_dt, '%Y-%m') 月份 from sale_order;
-- 每个月的销量与销售额
select date_format(sale_dt, '%Y-%m') 月份, sum(sale_count) 销量, sum(amount) 销售额
from sale_order
group by 月份;

-- 按月份进行销量和销售额的统计
select date_format(sale_dt, '%Y-%m') 月份, sum(sale_count) 总销量, sum(amount) 销售额 from sale_order group by 月份; 

-- sum 如果不是分组中使用， 获取指定字段所有行的和
select sum(sale_count) from sale_order where sale_count < 2;

-- sum 如果有分组，统计每个组内对应字段的和


-- 开窗函数，可以直接求累和

select * from sale_order;

-- 创建一个视图
create view v_sales as 
(select date_format(sale_dt, '%Y-%m') 月份, sum(sale_count) 总销量, sum(amount) 销售额 from sale_order group by 月份);

-- 累计销售的运算
select * from v_sales;

select *, sum(总销量) over(order by 总销量) 累计销量 from v_sales;

-- 分别统计每一年内的累计销量
select*, sum(总销量) over(partition by substr(月份,1,4) order by substr(月份,6)) as 累计销量 from v_sales;

Lag()

LAG 函数用于访问前一行的数据。它是一个窗口函数，可用于在结果集中的当前行中访问前一行的列值。通常情况下，LAG 函数与 OVER 子句一起使用，以指定窗口范围。

以下是 LAG 函数的基本语法：

sql

LAG(column_name, offset, default_value) OVER (ORDER BY order_column) AS lagged_value

其中：

column_name 是要获取前一行值的列名。
offset 是指定前几行的偏移量，默认为 1，表示前一行。
default_value 是可选参数，用于指定当偏移位置超出边界时要返回的默认值。
ORDER BY order_column 是指定排序的列，以确保正确地确定前一行。
lagged_value 是 LAG 函数返回的列名。

sql

-- 举个例子，假设您有一个 sales 表，其中包含 date 和 revenue 列，您想要查找每天的收入与前一天的收入之间的差异，可以使用 LAG 函数：
SELECT date,
       revenue,
       revenue - LAG(revenue, 1, 0) OVER (ORDER BY date) AS revenue_difference
FROM sales;

开窗函数的分组（partition by）和传统分组（group by）的区别

GROUP BY通常用于将数据分组，并在每个组内进行聚合操作，例如求和或计数。
而开窗函数允许你在结果集中对每一行应用聚合函数，而不需要将数据分成不同的组。
- 这意味着开窗函数可以在不改变结果行数的情况下对数据进行聚合操作，而GROUP BY则会减少结果行数到分组的数量
- 开窗函数聚合完之后每组会有重复数据，重复几次该组内就有几条数据
- 所以用开窗函数之后记得去重！！！！

时间相关的函数

时间戳函数unix_timestamp()

就是把时间转换成时间戳格式

sql

select UNIX_TIMESTAMP('2020-01-25');

-- 1579878000

计算时间差的函数timestampdiff()

timestampdiff 是一种SQL函数，用于计算两个日期或时间之间的差异。
它接受三个参数：时间单位（如年、月、日等）、开始时间和结束时间
返回两个时间之间的差异值，单位由第一个参数指定。
例如，如果要计算两个日期之间的天数差异，可以使用以下语法：

sql

SELECT TIMESTAMPDIFF(DAY, '2023-01-01', '2023-01-10');

Date_sub() 函数

date_sub 是在 SQL 中用于减去日期或时间值的函数。它的作用是从给定的日期或时间中减去指定的时间间隔。

sql

-- 语法
SELECT date_sub(order_date, INTERVAL 7 DAY) AS new_order_date
FROM your_table;

提取时间里的年月日

Mysql

sql

SELECT YEAR(datetime_column) AS year,
       MONTH(datetime_column) AS month,
       DAY(datetime_column) AS day,
       HOUR(datetime_column) AS hour,
       MINUTE(datetime_column) AS minute,
       SECOND(datetime_column) AS second
FROM your_table;

获取当前日期

Mysql

sql

SELECT YEAR(CURDATE()) AS year,
       MONTH(CURDATE()) AS month,
       DAY(CURDATE()) AS day;

数值型转换为日期型

Mysql

sql

SELECT 
STR_TO_DATE（CONVERT(your_numeric_column, CHAR), '%Y-%m-%d'） AS converted_string
from table;

时间戳类型转换为日期类型

Mysql

sql

SELECT FROM_UNIXTIME(your_timestamp_column) AS converted_date;

字符串格式转换为时间格式

Mysql

sql

SELECT STR_TO_DATE('2024-05-13', '%Y-%m-%d') AS converted_date
from table

时间格式转换为字符串并格式化

Mysql

sql

SELECT 
DATE_FORMAT(your_date_column, '%Y-%m-%d %H:%i:%s') AS formatted_date
FROM your_table;

Trunc（）

可以理解为取整函数

TRUNC() 函数用于截断日期或数字的部分，具体取决于参数的类型。在大多数 SQL 实现中，TRUNC() 函数有两种用法：

对日期值进行截断：将日期的时间部分截断，只保留日期部分或日期的指定部分。
- 比如5月13，截断后变成5月1
对数字值进行截断：将数字截断到指定的位数或最近的 10 的倍数。
- 比如42截断后变成40

以下是 TRUNC() 函数的常见用法：

截断日期值：

sql

TRUNC(date_value, format)

date_value 是要截断的日期值。
format是指定截断的格式，可以是以下之一：
- 'YEAR'：截断到年份的第一天。
- 'MONTH'：截断到月份的第一天。
- 'DAY'：保留日期的年、月和日部分，将时间部分设置为零。

例子

sql

TRUNC(TO_DATE('2024-05-15 15:30:00', 'YYYY-MM-DD HH24:MI:SS'), 'MONTH')
-- 返回 '2024-05-01 00:00:00'

TRUNC(TO_DATE('2024-05-15 15:30:00', 'YYYY-MM-DD HH24:MI:SS'), 'YEAR')
-- 返回 '2024-01-01 00:00:00'

截断数字值：

TRUNC(number_value, decimal_places)

number_value 是要截断的数字值。
decimal_places 是要保留的小数位数。
- 如果为正数，则截断到小数点后指定位数；
- 如果为负数，则截断到最近的 10 的倍数、100 的倍数等。

示例：

sql

TRUNC(42.5678, 1)
-- 返回 42.5

TRUNC(42.5678, -1)
-- 返回 40

零零碎碎

sql里的字符串拼接

使用sql的内置函数concat

mysql

update dahong set name = concat(name,'123') where id = 1

sql里的注释

mysql

#sql里的注释是-- ，注意sql注入的问题

sql里的正则

用关键字REGEXP 和 RLIKE

sql

SELECT name FROM world WHERE name REGEXP '[aeiou]';

SELECT name FROM world WHERE name RLIKE '[aeiou]';

各种报错

报错1055

1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'practice.score.num' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

mysql


select max(student.sid), score.num as sname from student 
left join score on score.student_id=student.sid 
left join course on course.cid=score.course_id 
left join teacher on teacher.tid=course.teacher_id 
group by student.sid


上面的sql语句报错1055，原因是查询字段不在分组结果中
说人话就是
用的是student.sid分组，那么在sql的严格模式下，select后的查询字段应全部是student这张表里的字段，
但现在我想查score这张表里的num

有两种解决方案：
1. 修改sql配置文件，将严格模式改为宽松模式，不推荐
2，使用any_value函数,"ANY_VALUE"是MySQL 8.0版本中引入的一个聚合函数,通常在使用GROUP BY时，如果要选择非聚合列的值，可以使用ANY_VALUE函数.

select max(student.sid), any_value(score.num) as sname from student 
left join score on score.student_id=student.sid 
left join course on course.cid=score.course_id 
left join teacher on teacher.tid=course.teacher_id 
group by student.sid

运行结果如下图

报错1055（8.0版本）

上图中的sql语句在5.7版本不会报错，但在8.0会报错，使用any_value即可。

报错1248

1248 - Every derived table must have its own alias

sql

-- 查询中使用了派生表（Derived Table），但没有为这个派生表指定别名（Alias）

-- 错误代码如下：
select * from 
(select *,
rank() over (partition by caption order by num desc) ranking 
from student) 
where ranking =1;

-- 修改后代码
select * from 
(select *,
rank() over (partition by caption order by num desc) ranking 
from student)  
as e
where ranking =1;

报错1060

创建视图时，Duplicate column name问题的解决

sql

-- 单独连表查询时没有问题，但创建视图后会
-- 报错1060 - Duplicate column name 'shop_id'

-- 原因：
-- shops表里和orders表里都有shop_id，sql不知道用哪个

-- 解决方案：
-- 修改查询字段，不要直接用*

-- 修改后代码

报错代码如下

修改后代码如下

函数 ​

部分函数列表： ​

自定义函数。 ​

存储过程（ ​

为什么使用存储过程 ​

存储过程操作(增删改) ​

参数类型 ​

返回值 & 结果集 ​

事务 & 异常 ​

游标 ​

视图 ​

触发器 ​

总结 ​

作业 ​

事务 ​

MySQL客户端 ​

Python代码（！） ​

数据库连接池 ​

SQL工具类 ​

单例和方法 ​

上下文管理 ​

交叉连接和自链接 ​

交叉连接 ​

自链接 ​

self-join(很常用) ​

举例： 黑产行为筛选 ​

注意： ​

其他 ​

总结 ​

大作业：开发博客系统 ​

1. 注册和登录 ​

2. 文章列表 ​

3. 文章详细 ​

4. 评论 & 阅读 & 赞 & 踩 ​

MySQL8窗口函数 ​

使用场景： ​

排名开窗函数 ​

聚合开窗函数 ​

众数，中位数之类的聚合函数 ​

累计运算 ​

Lag() ​

开窗函数的分组（partition by）和传统分组（group by）的区别 ​

时间相关的函数 ​

时间戳函数unix_timestamp() ​

计算时间差的函数timestampdiff() ​

Date_sub() 函数 ​

提取时间里的年月日 ​

Mysql ​

获取当前日期 ​

Mysql ​

数值型转换为日期型 ​

Mysql ​

时间戳类型转换为日期类型 ​

Mysql ​

字符串格式转换为时间格式 ​

Mysql ​

时间格式转换为字符串并格式化 ​

Mysql ​

Trunc（） ​

截断日期值： ​

例子 ​

截断数字值： ​

示例： ​

零零碎碎 ​

sql里的字符串拼接 ​

sql里的注释 ​

sql里的正则 ​

各种报错 ​

报错1055 ​

报错1055（8.0版本） ​

报错1248 ​

报错1060 ​

函数

部分函数列表：

自定义函数。

存储过程（

为什么使用存储过程

存储过程操作(增删改)

参数类型

返回值 & 结果集

事务 & 异常

游标

视图

触发器

总结

作业

事务

MySQL客户端

Python代码（！）

数据库连接池

SQL工具类

单例和方法

上下文管理

交叉连接和自链接

交叉连接

自链接

self-join(很常用)

举例：黑产行为筛选

注意：

其他

总结

大作业：开发博客系统

1. 注册和登录

2. 文章列表

3. 文章详细

4. 评论 & 阅读 & 赞 & 踩

MySQL8窗口函数

使用场景：

排名开窗函数

聚合开窗函数

众数，中位数之类的聚合函数

累计运算

Lag()

开窗函数的分组（partition by）和传统分组（group by）的区别

时间相关的函数

时间戳函数unix_timestamp()

计算时间差的函数timestampdiff()

Date_sub() 函数

提取时间里的年月日

Mysql

获取当前日期

Mysql

数值型转换为日期型

Mysql

时间戳类型转换为日期类型

Mysql

字符串格式转换为时间格式

Mysql

时间格式转换为字符串并格式化

Mysql

Trunc（）

截断日期值：

例子

截断数字值：

示例：

零零碎碎

sql里的字符串拼接

sql里的注释

sql里的正则

各种报错

报错1055

报错1055（8.0版本）

报错1248

报错1060