文章

MySQL版《出师表》

最近,有程序员用 SQL 重新翻译《出师表》,意外发现惊人契合度!原来治国平天下和写代码竟有这些共通逻辑……

先帝创业未半而中道崩殂

DELETE FROM `蜀国`
WHERE name = '刘玄德'AND `创业进度` < 0.5AND `存活状态` = true

今天下三分,益州疲弊

UPDATE `国家表` 
SET `国力` = '疲弊' 
WHERE `地区` = '益州' AND `分裂状态` = '三分天下';

侍卫之臣不懈于内,忠志之士忘身于外者

INSERT INTO `忠臣表` (`姓名`, `岗位`, `状态`) 
VALUES 
('侍中郭攸之', '宫内', '不懈工作'),
('将军向宠', '军营', '忘身作战');

诚宜开张圣听,以光先帝遗德

ALTER TABLE `皇帝表` 
ADD COLUMN `圣听开启` BOOLEAN DEFAULT TRUE,
MODIFY COLUMN `继承遗志` VARCHAR(255) DEFAULT '光复汉室';

不宜妄自菲薄,引喻失义,以塞忠谏之路也

DELETE FROM `官员表` 
WHERE `心理状态` = '妄自菲薄' 
AND `言论记录` LIKE '%引喻失义%' 
AND `谏言次数` < 1;

宫中府中,俱为一体,陟罚臧否,不宜异同

CREATE VIEW `统一考核视图` AS
SELECT * FROM `皇宫人员表`
UNION ALL
SELECT * FROM `丞相府人员表`
WITH CHECK OPTION `奖惩标准` = '公平一致';

亲贤臣,远小人

  • 贤臣组
UPDATE `官员表` 
SET `亲密度` = `亲密度` + 10 
WHERE `品德评分` >= 90;
  • 小人组
UPDATE `官员表` 
SET `任职状态` = '流放' 
WHERE `恶行记录` IS NOT NULL 
AND `悔改状态` = FALSE;

先帝在时,每与臣论此事

SELECT `谈话内容` 
FROM `先帝谈话记录` 
INNER JOIN `诸葛亮工作日志` 
ON `谈话时间` BETWEEN '建安十三年' AND '章武三年'
WHERE `议题分类` = '国家大事';

受命以来,夙夜忧叹

INSERT INTO `任务表` (`任务内容`, `负责人`, `状态`) 
VALUES ('北定中原', '诸葛亮', '进行中') 
ON DUPLICATE KEY UPDATE `最后进度` = NOW();

今南方已定,兵甲已足

UPDATE `军事部署表` 
SET 
`南部状态` = '已平定',
`装备库存` = 1000000 
WHERE `年度` = YEAR(CURDATE());

当奖率三军,北定中原

CALL 发布军事任务('北伐中原', '诸葛亮', '赵云', '魏延');

此臣所以报先帝而忠陛下之职分也

CREATE TRIGGER `履行职责` 
AFTER INSERT ON `皇帝诏令表`
FOR EACH ROW
BEGIN
UPDATE `大臣职责表`    
SET `完成状态` = TRUE
WHERE `大臣ID` = 1; -- 诸葛亮ID
END;

愿陛下托臣以讨贼兴复之效

...

10个高级的SQL查询技巧

随着数据量持续增长,对合格数据专业人员的需求也会增长。具体而言,对SQL流利的专业人士的需求日益增长,而不仅仅是在初级层面。

因此,Stratascratch的创始人Nathan Rosidi以及我觉得我认为10个最重要和相关的中级到高级SQL概念。

那个说,我们走了!

常见表表达式(CTEs) #

如果您想要查询子查询,那就是CTEs施展身手的时候 - CTEs基本上创建了一个临时表。

使用常用表表达式(CTEs)是模块化和分解代码的好方法,与您将文章分解为几个段落的方式相同。

请在Where子句中使用子查询进行以下查询。

SELECT
    name,
    salary
FROM
    People
WHERE
        NAME IN ( SELECT DISTINCT NAME FROM population WHERE country = "Canada" AND city = "Toronto" )
  AND salary >= (
    SELECT
        AVG( salary )
    FROM
        salaries
    WHERE
        gender = "Female")

这似乎似乎难以理解,但如果在查询中有许多子查询,那么怎么样?这就是CTEs发挥作用的地方。

with toronto_ppl as (
    SELECT DISTINCT name
    FROM population
    WHERE country = "Canada"
      AND city = "Toronto"
)
   , avg_female_salary as (
        SELECT AVG(salary) as avgSalary
        FROM salaries
        WHERE gender = "Female"
    )
SELECT name
     , salary
FROM People
WHERE name in (SELECT DISTINCT FROM toronto_ppl)
  AND salary >= (SELECT avgSalary FROM avg_female_salary)

现在很清楚,Where子句是在多伦多的名称中过滤。如果您注意到,CTE很有用,因为您可以将代码分解为较小的块,但它们也很有用,因为它允许您为每个CTE分配变量名称(即toronto_ppl和avg_female_salary)

...

SQL 速成手册

SQL(Structured Query Language,结构化查询语言)是一种用于管理和操作关系型数据库的标准化编程语言。

SQL 被广泛用于数据库系统中(如 MySQL、PostgreSQL、Oracle、SQL Server),用于执行查询、更新数据、管理数据库结构和控制数据库访问权限。

SQL 的主要功能 #

  • 基本查询语句

    • SELECT - 用于从数据库中选择数据,返回结果集。
    • WHERE - 用于过滤记录,指定查询条件。
    • ORDER BY - 用于对结果集进行排序,可以按升序或降序排列。
    • DISTINCT - 用于返回唯一不同的值,去除重复记录。
    • LIMIT - 用于指定返回的记录数量,常用于分页。
  • 表操作语句

    • CREATE TABLE - 用于创建新表,定义表的列和数据类型。
    • ALTER TABLE - 用于修改现有表的结构,例如添加、删除或修改列。
    • DROP TABLE - 用于删除表及其所有数据。
  • 数据操作语句

    • INSERT INTO - 用于向表中插入新记录。
    • UPDATE - 用于修改表中的现有记录。
    • DELETE - 用于删除表中的记录。
  • 函数与聚合操作

    • COUNT - 用于返回匹配条件的行数。
    • SUM - 用于返回列的总和。
    • AVG - 用于返回列的平均值。
    • MIN - 用于返回列的最小值。
    • MAX - 用于返回列的最大值。
  • 子查询与联接

    • INNER JOIN - 用于返回两个表中都存在的匹配记录。
    • LEFT JOIN - 用于返回左表中的所有记录和右表中的匹配记录。
    • RIGHT JOIN - 用于返回右表中的所有记录和左表中的匹配记录。
    • FULL JOIN - 用于返回两个表中所有记录,不管是否匹配。
    • SUBQUERY - 用于在查询语句中嵌套另一个查询。
  • 高级操作

    ...

20个SQL优化方法

查询SQL尽量不要使用select *,而是具体字段 #

  • 反例
SELECT * FROM user
  • 正例
SELECT id,username,tel FROM user
  • 理由
    • 节省资源、减少网络开销。
    • 可能用到覆盖索引,减少回表,提高查询效率。

注意:为节省时间,下面的样例字段都用*代替了。

避免在where子句中使用 or 来连接条件 #

  • 反例
SELECT * FROM user WHERE id=1 OR salary=5000
  • 正例
    • 使用union all
SELECT * FROM user WHERE id=1 
UNION ALL
SELECT * FROM user WHERE salary=5000
- 分开两条SQL写
SELECT * FROM user WHERE id=1
SELECT * FROM user WHERE salary=5000
  • 理由
    • 使用or可能会使索引失效,从而全表扫描;
    • 对于or没有索引的salary这种情况,假设它走了id的索引,但是走到salary查询条件时,它还得全表扫描;
    • 也就是说整个过程需要三步:全表扫描+索引扫描+合并。如果它一开始就走全表扫描,直接一遍扫描就搞定;
    • 虽然mysql是有优化器的,出于效率与成本考虑,遇到or条件,索引还是可能失效的;

尽量使用数值替代字符串类型 #

  • 正例

    • 主键(id):primary key优先使用数值类型int,tinyint
    • 性别(sex):0代表女,1代表男;数据库没有布尔类型,mysql推荐使用tinyint
  • 理由

    • 因为引擎在处理查询和连接时会逐个比较字符串中每一个字符;
    • 而对于数字型而言只需要比较一次就够了;
    • 字符会降低查询和连接的性能,并会增加存储开销。

使用varchar代替char #

  • 反例
`address` char(100) DEFAULT NULL COMMENT '地址'
  • 正例
`address` varchar(100) DEFAULT NULL COMMENT '地址'
  • 理由
    • varchar变长字段按数据内容实际长度存储,存储空间小,可以节省存储空间;
    • char按声明大小存储,不足补空格;
    • 其次对于查询来说,在一个相对较小的字段内搜索,效率更高;

char与varchar2的区别? #

  1. char的长度是固定的,而varchar2的长度是可以变化的。 比如,存储字符串“101”,对于char(10),表示你存储的字符将占10个字节(包括7个空字符),在数据库中它是以空格占位的,而同样的varchar2(10)则只占用3个字节的长度,10只是最大值,当你存储的字符小于10时,按实际长度存储。
  2. char的效率比varchar2的效率稍高。

何时用char,何时用varchar2? #

char和varchar2是一对矛盾的统一体,两者是互补的关系,varchar2比char节省空间,在效率上比char会稍微差一点,既想获取效率,就必须牺牲一点空间,这就是我们在数据库设计上常说的“以空间换效率”。

...

Excel数据按sheet导入SQLite

以下为python实例脚本,将名为test.xlsx的表格,按每个sheet名新建table,导入SQlite3数据库中。

## 导入库
import pandas as pd
import sqlite3

##建立数据库文件
con=sqlite3.connect('test.db')

##读取excel
xlsx=pd.read_excel('test.xlsx', sheet_name=None)

##遍历每个sheet
for sheet in xlsx.keys():
	## 按sheet读取excel内容
	df=pd.read_excel('test.xlsx',sheet_name=sheet)
	##按sheet名命名table,写入数据库
	df.to_sql(sheet,con, index=False,if_exists="replace")
con.commit()
con.close()

MyBatis 动态 SQL 教程

MyBatis动态sql是什么 #

动态 SQL 是 MyBatis 的强大特性之一。在 JDBC 或其它类似的框架中,开发人员通常需要手动拼接 SQL 语句。根据不同的条件拼接 SQL 语句是一件极其痛苦的工作。例如,拼接时要确保添加了必要的空格,还要注意去掉列表最后一个列名的逗号。而动态 SQL 恰好解决了这一问题,可以根据场景动态的构建查询。

动态SQL(code that is executed dynamically),它一般是根据用户输入或外部条件动态组合的SQL语句块。动态SQL能灵活的发挥SQL强大的功能、方便的解决一些其它方法难以解决的问题。相信使用过动态SQL的人都能体会到它带来的便利,然而动态SQL有时候在执行性能 (效率)上面不如静态SQL,而且使用不恰当,往往会在安全方面存在隐患 (SQL 注入式攻击)。

Mybatis 动态sql是做什么的? #

Mybatis 动态 sql 可以让我们在 Xml 映射文件内,以标签的形式编写动态 sql,完成逻辑判断和动态拼接 sql 的功能。

Mybatis 的9种动态sql标签有哪些? #

sql标签

动态sql的执行原理? #

原理为:使用 OGNL 从 sql 参数对象中计算表达式的值,根据表达式的值动态拼接 sql,以此来完成动态 sql 的功能。

MyBatis标签 #

if标签:条件判断 #

MyBatis if 类似于 Java 中的 if 语句,是 MyBatis 中最常用的判断语句。使用 if 标签可以节省许多拼接 SQL 的工作,把精力集中在 XML 的维护上。

...

innodb 是如何存数据的?

如果你使用过mysql数据库,对它的存储引擎:innodb,一定不会感到陌生。

众所周知,在mysql5以前,默认的存储引擎是:myslam。但mysql5之后,默认的存储引擎已经变成了:innodb,它是我们建表的首选存储引擎。

那么,问题来了:

  • innodb底层是如何存储数据的?
  • 表中有哪些隐藏列?
  • 用户记录之间是如何关联起来的?

如果你想知道上面三个问题的答案,那么,请继续往下面看。

1.磁盘or内存? #

1.1 磁盘 #

数据对系统来说是非常重要的东西,比如:用户的身份证、手机号、银行号、会员过期时间、积分等等。一旦丢失,会对用户造成很大的影响。

那么问题来了,如何才能保证这些重要的数据不丢呢?

答案:把数据存在磁盘上。

当然有人会说,如果磁盘坏了怎么办?

那就需要备份,或者做主从了。。。

好了,打住,这不是今天的重点。

言归正传。

大家都知道,从磁盘上读写数据,至少需要两次IO请求才能完成。一次是读IO,另一次是写IO。

而IO请求是比较耗时的操作,如果频繁的进行IO请求势必会影响数据库的性能。

那么,如何才能解决数据库的性能问题呢?

1.2 内存 #

把数据存在寄存器?

没错,操作系统从寄存器中读取数据是最快的,因为它离CPU最近。

但是寄存器有个非常致命的问题是:它只能存储非常少量的数据,设计它的目的主要是用来暂存指令和地址,并非存储大量用户数据的。

这样看来,只能把数据存在内存中了。

因为内存同样能满足我们,快速读取和写入数据的需求,而且性能是非常可观的,只是比较寄存器稍稍慢了一丢丢而已。

不过有个让人讨厌的地方是,内存相对于磁盘来说,是更加昂贵的资源。通常情况下,500G或者1T的磁盘,是很常见的。但你有听说过有500G的内存吗?别人会以为你疯了。内存大小讨论的数量级一般是16G或32G。

内存可以存储一些用户数据,但无法存储所有的用户数据,因为如果数据量太大了,它可能还是存不下。

此外,即使用户数据能刚好存在内存,以后万一有一天,数据库服务器或者部署节点挂了,或者重启了,数据不就丢了?

怎么做,才能不会因为异常情况,而丢数据。同时,又能保证数据的读写速度呢?

2.数据页 #

我们可以把一批数据放在一起。

写操作时,先将数据写到内存的某个批次中,然后再将该批次的数据一次性刷到磁盘上。如下图所示:

读操作时,从磁盘上一次读一批数据,然后加载到内存当中,以后就在内存中操作。如下图所示:

将内存中的数据刷到磁盘,或者将磁盘中的数据加载到内存,都是以批次为单位,这个批次就是我们常说的:数据页

当然innodb中存在多种不同类型的页,数据页只是其中一种,我们在这里重点介绍一下数据页。

那么问题来了,什么是数据页?

数据页主要是用来存储表中记录的,它在磁盘中是用双向链表相连的,方便查找,能够非常快速得从一个数据页,定位到另一个数据页。

很多时候,由于我们表中的数据比较多,在磁盘中可能存放在多个数据页当中。

有一天,我们要根据某个条件查询数据时,需要从一个数据页找到另一个数据页,这时候的双向链表就派上大用场了。磁盘中各数据页的整体结构如下图所示:

通常情况下,单个数据页默认的大小是16kb。当然,我们也可以通过参数:innodb_page_size,来重新设置大小。不过,一般情况下,用它的默认值就够了。

好吧,数据页的整体结构已经搞明白了。

那么,单个数据页包含哪些内容呢?

从上图中可以看出,数据页主要包含如下几个部分:

  • 文件头部
  • 页头部
  • 最大和最小记录
  • 用户记录
  • 空闲空间
  • 页目录
  • 文件尾部

3.用户记录 #

对于新申请的数据页,用户记录是空的。当插入数据时,innodb会将一部分空闲空间分配给用户记录。

用户记录是innodb的重中之重,我们平时保存到数据库中的数据,就存储在它里面。那么,它里面又包含哪些内容呢?你不好奇吗?

其实在innodb支持的数据行格式有四种:

  • compact行格式
  • redundant行格式
  • dynamic行格式
  • compressed行格式 我们以compact行格式为例:

...

SQL多表查询inner join用法

  • inner join(等值连接):只返回两个表中联结字段相等的行。
  • left join(左联接):返回包括左表中的所有记录和右表中联结字段相等的记录。
  • right join(右联接):返回包括右表中的所有记录和左表中联结字段相等的记录。

INNER JOIN 语法: #

INNER JOIN 连接两个数据表的用法: #

SELECT * FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号

INNER JOIN 连接三个数据表的用法: #

SELECT * FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号

INNER JOIN 连接四个数据表的用法: #

SELECT * FROM ((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) 
INNER JOIN 表4 ON Member.字段号=表4.字段号

INNER JOIN 连接五个数据表的用法: #

SELECT * FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) 
INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表5 ON Member.字段号=表5.字段号

连接六个数据表的用法:略,与上述联接方法类似!

...