前言最近公司开发同学A在群聊中说某某系统的某条SQL有问题，抛出异常，而同样的SQL在开发同学B（SQL由B同学编写）的环境没有问题，带着好奇心我上前协助一看，最终发现异常大致内容是：sql_mode=ONLY_FULL_GROUP_BY，SQL大致类似如下格式：SELECT name, age, SUM(sales) FROM orders GROUP BY name; 此时让我想起了年轻的自己，当初也犯过如此问题，于是我耐心的告知开发同学B：SELECT 中的非聚合字段必须出现在 GROUP BY 子句中所有非聚合字段需通过聚合函数（如MAX、MIN、SUM）处理，或显式声明分组依据解释完毕后，开发同学B提出疑问，为什么开发同学A的环境不行，而我的就可以，于是我在他们电脑分别使用了SELECT VERSION(); 语句进行查看版本，然后告知开发同学A和开发同学B，自MySQL 5.7.5 起：官方将ONLY_FULL_GROUP_BY 加入默认的sql_mode，以增强 SQL 的标准兼容性和数据一致性，当我说完 打算扬长而去时，两位开发同学随即又抛出疑问：sql_mode是什么？我：......简介MySQL为了支持在不同的环境下运行，允许我们给它设置不同的运行模式（sql_mode）sql_mode是MySQL中用于设置sql语法和行为的系统变量控制MySQL的sql解析和执行的方式，使其与sql标准或其他数据库系统的行为一致，通过设置sql_mode，可以改变MySQL处理待定sql操作的方式[scode type="green"]我们在安装mysql时就要确定它的运行模式（sql_mode），不建议在中途更改它的运行模式[/scode][scode type="green"]建议在跨mysql实例备份还原数据时，保持双方mysql实例具有相同的运行模式[/scode]sql_mode模式MySQL 支持的 sql_mode 模式模式名含义STRICT_TRANS_TABLES事务表中对无效数据报错，非事务表警告STRICT_ALL_TABLES所有表对无效数据都报错NO_ZERO_IN_DATE不允许日期中的月或日为 0（如 2023-00-10）NO_ZERO_DATE不允许日期为 '0000-00-00'ERROR_FOR_DIVISION_BY_ZERO除以 0 报错NO_AUTO_CREATE_USER禁止 GRANT 自动创建用户（MySQL 8.0 移除）NO_ENGINE_SUBSTITUTION禁止在指定引擎不可用时自动替代ONLY_FULL_GROUP_BYGROUP BY 中必须列出所有非聚合列ANSI_QUOTES用双引号表示标识符（字段、表名等）PIPES_AS_CONCAT使用 “||” 表示字符串拼接IGNORE_SPACE函数名后允许加空格ANSI启用符合 ANSI SQL 的语法和行为TRADITIONAL启用接近标准 SQL 的严格模式组合NO_BACKSLASH_ESCAPES禁用反斜杠 “\” 作为转义字符HIGH_NOT_PRECEDENCE改变 “NOT” 运算符的优先级NO_UNSIGNED_SUBTRACTION无符号数减法结果转换为有符号类型REAL_AS_FLOAT将 “REAL” 类型作为 “FLOAT” 处理PAD_CHAR_TO_FULL_LENGTH使用 “CHAR” 比较时右侧填充空格至固定长度ALLOW_INVALID_DATES允许插入无效日期（非严格模式时有效）TIME_TRUNCATE_FRACTIONAL时间字段截断小数秒部分而非四舍五入sql_mode对比对比项MySQL 5.7 默认值MySQL 8.0 默认值默认 sql_modeONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONNO_AUTO_CREATE_USER默认启用已移除（不再支持 GRANT 创建用户）严格性中等更严格，接近 SQL 标准查看和修改sql_modesql_mode，分为全局的、会话的（即：针对当前链接有效），在mysql运行时查看和更改sql_mode的sql如下：-- 查看当前会话的sql_mode select @@sql_mode SELECT @@SESSION.sql_mode; -- 查看全局的sql_mode SELECT @@GLOBAL.sql_mode; -- 设置当前会话的sql_mode set sql_mode="TRADITIONAL"; SET SESSION sql_mode = 'TRADITIONAL'; -- 设置全局的sql_mode SET GLOBAL sql_mode = 'TRADITIONAL'; -- 添加 sql_mode SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY'); SET GLOBAL sql_mode = sys.list_add(@@global.sql_mode, 'ONLY_FULL_GROUP_BY'); 不过，上述的修改当myql服务器重启时就自动失效了，为了长久有效，我们可以在my.cnf 或 my.ini文件中进行配置
[scode type="red"]以下示例基于PostgreSQL[/scode][collapse status="false" title="常用DDL"]DDLDDL：[Data Mefinition Language] 数据定义语言关键点： CREATE TABLE、ALTER TABLE、DROP TABLE、CREATE/DROP INDEX 等主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用DDL操作是隐性提交的！不能rollback [scode type="blue"]创建表[/scode]CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), birth_date DATE, hire_date DATE ); [scode type="blue"]修改表[/scode]#添加列 ALTER TABLE employees ADD COLUMN email VARCHAR(100); #修改列类型 ALTER TABLE employees ALTER COLUMN birth_date TYPE TIMESTAMP; #删除列 ALTER TABLE employees DROP COLUMN email; [scode type="blue"]删除表[/scode]DROP TABLE employees; [scode type="blue"]创建视图[/scode]CREATE VIEW employee_names AS SELECT first_name, last_name FROM employees; [scode type="blue"]删除视图[/scode]DROP VIEW employee_names; [/collapse][collapse status="false" title="常用DML"]DMLDML：[Data Manipulation Language] 数据操纵语言关键点： INSERT、UPDATE、DELETE主要是对表数据进行操作，如对表数据进行增、删、改[scode type="blue"]插入数据[/scode]#插入单行数据 INSERT INTO employees (first_name, last_name, birth_date, hire_date) VALUES ('John', 'Doe', '1990-01-01', '2020-01-01'); #插入多行数据 INSERT INTO employees (first_name, last_name, birth_date, hire_date) VALUES ('Jane', 'Doe', '1992-02-02', '2021-02-02'), ('Jim', 'Beam', '1985-03-03', '2019-03-03'); [scode type="blue"]更新数据[/scode]#更新单个字段 UPDATE employees SET hire_date = '2021-01-01' WHERE employee_id = 1; #更新多个字段 UPDATE employees SET first_name = 'Jonathan', last_name = 'Smith' WHERE employee_id = 2; [scode type="blue"]删除数据[/scode]#删除符合条件的行 DELETE FROM employees WHERE employee_id = 3; #删除所有数据（但保留表结构） DELETE FROM employees; [/collapse][collapse status="false" title="常用DCL"]DCLDCL： [Data Control Language] 数据控制语言关键点： GRANT、REVOKE主要主要用于控制数据库的访问权限和安全性[scode type="blue"]授予权限[/scode]#授予用户对表的全部权限 GRANT ALL PRIVILEGES ON TABLE employees TO user_name; #授予用户对表的特定权限 GRANT SELECT, INSERT, UPDATE ON TABLE employees TO user_name; #授予角色权限 GRANT SELECT ON TABLE employees TO role_name; #授予用户对数据库的连接权限 GRANT CONNECT ON DATABASE database_name TO user_name; [scode type="blue"]撤销权限[/scode]#撤销用户对表的特定权限 REVOKE SELECT, INSERT ON TABLE employees FROM user_name; #撤销用户对表的全部权限 REVOKE ALL PRIVILEGES ON TABLE employees FROM user_name; #撤销角色权限 REVOKE CONNECT ON DATABASE database_name FROM user_name; [scode type="blue"]授予和撤销角色[/scode]#创建角色 CREATE ROLE role_name; #授予用户角色 GRANT role_name TO user_name; #撤销用户角色 REVOKE role_name FROM user_name; [/collapse][collapse status="false" title="常用DQL"]DQLDQL： [Data Query Language ] 数据查询语言关键点： SELECTDQL基本结构是由SELECT语句，FROM语句，WHERE语句组成的查询块：基本语法：SELECT <字段名表> FROM <表或视图名> WHERE <查询条件>[scode type="blue"]基本查询[/scode]#查询所有列 SELECT * FROM employees; #查询特定列 SELECT first_name, last_name FROM employees; [scode type="blue"]条件查询[/scode]#查询满足特定条件的行 SELECT first_name, last_name FROM employees WHERE last_name = 'Doe'; #使用比较运算符 SELECT first_name, last_name FROM employees WHERE hire_date > '2020-01-01'; [scode type="blue"]使用逻辑运算符[/scode]#AND运算符 SELECT first_name, last_name FROM employees WHERE last_name = 'Doe' AND hire_date > '2020-01-01'; #OR运算符 SELECT first_name, last_name FROM employees WHERE last_name = 'Doe' OR hire_date > '2020-01-01'; #NOT运算符 SELECT first_name, last_name FROM employees WHERE NOT (last_name = 'Doe'); [scode type="blue"]使用ORDER BY子句排序[/scode]#按一个列排序 SELECT first_name, last_name FROM employees ORDER BY last_name ASC; #按多个列排序 SELECT first_name, last_name, hire_date FROM employees ORDER BY last_name ASC, hire_date DESC; [scode type="blue"]使用LIMIT子句限制返回的行数[/scode]SELECT first_name, last_name FROM employees ORDER BY hire_date DESC LIMIT 5 OFFSET 3; [scode type="blue"]使用聚合函数和GROUP BY子句[/scode]#计数 SELECT COUNT(*) FROM employees; #分组计数 SELECT department_id, COUNT(*) FROM employees GROUP BY department_id; #其他聚合函数（如SUM, AVG, MAX, MIN） SELECT department_id, AVG(salary) FROM employees GROUP BY department_id; [scode type="blue"]使用JOIN子句连接表[/scode]#内连接 SELECT employees.first_name, employees.last_name, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id; #左连接 SELECT employees.first_name, employees.last_name, departments.department_name FROM employees LEFT JOIN departments ON employees.department_id = departments.department_id; [/collapse]
前言在数据时代，无论是单机环境还是分布式环境，都需要保持数据ID的唯一性针对常见的唯一ID，一般有三种，分别是：自增ID、UUID和雪花ID自增ID自增ID（Auto-increment ID）是一种在数据库中常用的主键生成方法，通常用于唯一标识数据库表中的每一行数据优点唯一性：自增ID保证了每个表中的行都有唯一的标识符，避免了重复数据的问题。简单性：自增ID是一种简单且直观的主键生成方法，易于实现和维护。性能：自增ID可以提高数据库的性能，因为数据库引擎会自动管理并优化自增ID的索引，使数据检索更快速。插入效率：自增ID的顺序插入可以减少数据页的分裂，提高插入效率。隐私性：自增ID不透露关于数据的任何信息，可以保护数据的隐私性。缺点可预测性：自增ID是连续的数字序列，可能暴露了数据库中数据的规律性，存在安全风险。分布式环境下的问题：在分布式系统中，自增ID的生成可能存在冲突和同步问题，需要额外的处理来确保唯一性。删除数据后的空洞：当删除表中的数据时，可能会留下空洞，导致ID不再是连续的。数据迁移问题：在数据迁移或合并时，可能需要额外考虑自增ID的处理，避免冲突或重复。只适合单机数据库，不能用于分库分表的情况，会产生重复id。UUIDUUID（Universally Unique Identifier）是一种用于标识信息的唯一标识符，拥有随机、无序，具有非常好的全局唯一性优点全局唯一性：UUID是全局唯一的标识符，几乎可以保证在不同系统和数据库中生成的UUID都是唯一的。分布式系统支持：在分布式系统中，UUID可以在不同节点上生成唯一的标识符，避免了冲突。随机性：UUID是基于时间和随机数生成的，不易被猜测，提高了安全性。不依赖中心化机制：生成UUID不需要中心化的机制，每个节点都可以独立生成唯一的标识符。无序性：UUID是无序的，不会暴露数据之间的关系，保护了数据的隐私性。缺点长度：UUID相对于自增ID来说比较长（128位），在存储和索引时占用的空间较大。可读性：UUID是一串十六进制数字和字符，不如自增ID那样直观和易读。性能：生成UUID的算法相对复杂，可能会影响性能，尤其在高并发环境下。不适用于某些场景：在某些情况下，需要连续递增的ID，UUID并不适合。数据库索引问题：由于UUID的无序性，可能会导致索引效率下降，需要额外的处理来优化索引。雪花ID有这样一种说法，自然界中并不存在两片完全一样的雪花(世界上没有两片相同的树叶)，每一片雪花都拥有自己漂亮独特的形状且是独一无二。雪花算法也表示生成的 ID 如雪花般独一无二，是一种用于生成分布式系统中唯一ID的算法，由Twitter开发。它的核心思想是将一个64位的ID分成不同的部分，每部分表示不同的信息，例如时间戳、机器ID和序列号。雪花算法满足唯一性和有序性，避免了分布式系统环境下的ID冲突；并且生成ID的过程中无需依赖数据库等外部系统，减少了系统复杂性。[scode type="green"]SnowFlake 算法结构如下：大致分为了无效位、时间位、机器位和序列号位[/scode]1）第一位 占用1bit，其值始终是0，没有实际作用（因为二进制中最高位是符号位，1表示负数，0表示正数。生成的id一般都是用整数，所以最高位固定为0）。2）时间戳 占用41bit，精确到毫秒，总共可以容纳约69年的时间。3）工作机器id 占用10bit，其中高位5bit是数据中心ID，低位5bit是工作节点ID，做多可以容纳1024个节点。4）序列号 占用12bit，每个节点每毫秒0开始不断累加，最多可以累加到4095，一共可以产生4096个ID。那么SnowFlake算法在同一毫秒内最多可以生成多少个全局唯一ID呢？同一毫秒的ID数量 = 1024 X 4096 = 4194304优点全局唯一性：雪花算法生成的ID在分布式系统中保证全局唯一，避免了ID冲突的问题。分布式系统支持：雪花算法适用于分布式系统，每个节点可以独立生成唯一的ID，无需中心化的管理。高性能：雪花算法生成ID的速度较快，适用于高并发的场景。简单：相对于一些复杂的ID生成方案，雪花算法相对简单易懂，易于实现和维护。时间有序：雪花算法生成的ID中包含时间戳，可以保证生成的ID在一定程度上是有序的。缺点时钟回拨问题：如果系统时钟发生回拨，可能会导致生成的ID重复，需要额外处理时钟同步的问题。依赖机器ID：雪花算法中需要分配机器ID，如果分配不当或者机器ID冲突，可能会导致ID冲突。单点故障：如果分配的机器ID集中在少数几台机器上，这些机器出现故障可能会影响整个系统的ID生成。有序性：虽然时间戳可以保证一定的有序性，但在高并发场景下，可能会出现ID递增速度过快导致性能问题。长度固定：雪花算法生成的ID长度固定，可能会占用较多的存储空间。比较生成算法优点缺点长度 自增ID实现简单，数据递增DB单点故障等，需要DBA专业维护递增 UUID实现简单，不占带宽ID是无序的，查询慢，不适合建立索引32字节 雪花ID低位趋势递增，不占带宽，性能高依赖于服务器时间18字节
Windows系统[scode type="green"]下载[/scode][scode type="share"]Redis 官方网站现在没有提供 Windows 版的安装包（Redis-x64-3.0.504之后不再提供）[/scode]以下有四种方案可供选择1.下述Windows 安装包是某位民间“大神”根据 Redis 源码改造的，并非 Redis 官方网站提供[button color="success" icon="glyphicon glyphicon-arrow-right" url="https://github.com/tporadowski/redis/releases" type=""]Github传送门[/button]2.使用微软维护版（Redis-x64-3.0.504）[button color="success" icon="glyphicon glyphicon-arrow-right" url="https://github.com/MicrosoftArchive/redis/releases" type=""]Github传送门[/button]3.使用社区维护版（基于官方源码编译）[button color="success" icon="glyphicon glyphicon-arrow-right" url="https://github.com/redis-windows/redis-windows/releases" type=""]Github传送门[/button]4.安装虚拟机或根据官网提示安装WSL2（适用于 Linux 的 Windows 子系统）WSL2 允许您在 Windows 上本地运行 Linux 二进制文件。要使用此方法，您需要运行 Windows 10 2004 版及更高版本或 Windows 11[scode type="green"]安装[/scode]选择“添加Redis目录到环境变量PATH中”，这样方便系统自动识别Redis执行文件在哪里（可手动配置）端口号可保持默认的6379，并选择防火墙例外，从而保证外部可以正常访问Redis服务设定redis内存使用最大值打开Redis服务配置文件（注意：不要找错了，通常为redis.windows-service.conf，而不是redis.windows.conf。后者是以非系统服务方式启动程序使用的配置文件）找到含有requirepass字样的地方，追加一行，输入requirepass 123456。这是访问Redis时所需的密码，一般测试情况下可以不用设定密码点击“开始”>右击“计算机”>选择“管理”。在左侧栏中依次找到并点击“计算机管理（本地）”>服务和应用程序>服务。再在右侧找到Redis名称的服务，查看启动情况。如未启动，则手动启动之。正常情况下，服务应该正常启动并运行了（没有服务的的话见下面tips）启动Redis客户端redis-cli -h ip地址 -p 端口号 -n 数据库编号 -a 密码 关闭Redis服务redis-server --service-stop redis-cli shutdown 检查是否连接成功（测试客户端和服务端是否成功连接。输出PING命令，若返回PONG则证明成功连接。）其他命令redis-cli -h ip地址 -p 端口号 -n 数据库编号 -a 密码 输入了数据库编码，连接数据库执行如下命令： # 切换数据库 select 数据库编号 # 清空redis数据库 flushall # 清理当前db数据 flushdb [scode type="blue"]tips[/scode]如果计算机服务内没有Redis的服务,我们可以手动创建1.注册Redis服务（通过 CMD 命令行工具进入 Redis 安装目录，将 Redis 服务注册到 Windows 服务中，执行以下命令）redis-server.exe --service-install redis.windows.conf --loglevel verbose 2.执行完后，得到以下类似输出，说明注册成功[1868] 07 Jan 15:00:08.223 # Granting read/write access to 'NT AUTHORITY\NetworkService' on: "D:\Redis-x64-5.0.10" "D:\Redis-x64-5.0.10\" [1868] 07 Jan 15:00:08.230 # Redis successfully installed as a service. 3.启动Redis服务redis-server --service-start redis-server redis.windows.conf # 通过配置服务启动 Mac 系统使用 Homebrew 在 macOS 上安装 Redis[scode type="red"]安装前置包管理器：Homebrew（目前比较流行的包管理器）[/scode][button color="success" icon="glyphicon glyphicon-arrow-right" url="https://brew.sh/" type=""]官网传送门[/button]复制官方提供的命令安装（需要科学上网）Homebrew安装完毕后，后续步骤可参考Redis官方文档：在macOS上安装Redis可视化工具Navicat Premium 17RedisDesktopManager[scode type="green"]上述是两款比较常见的图形可视化工具[/scode]Navicat在常用开发中比较流行，安装方式在此处不在赘述RedisDesktopManager(RDM)是一个用于Windows，Linux和MacOS的快速开源Redis数据库管理应用程序1.官方-新版[button color="success" icon="glyphicon glyphicon-arrow-right" url="https://github.com/RedisInsight/RedisDesktopManager" type=""]Github传送门[/button]2.官方-最后一版免费版（0.9.3 是最最后一版免费版，新版要收费）[button color="success" icon="glyphicon glyphicon-arrow-right" url="https://github.com/RedisInsight/RedisDesktopManager/releases/tag/0.9.3" type=""]0.9.3传送门[/button]3.v2021.7.0.0版本（来源于网络，仅供使用/学习）[button color="info" icon="glyphicon glyphicon-download-alt" url="https://disk.windseeker.link/share/f833169bda3048fd804945b864ed11c8" type=""]追风网盘 / 提取码：201721[/button][scode type="green"]安装/使用[/scode]1.同意协议开始安装2.编辑Redis连接信息
[scode type="red"]以下示例基于MYSQL[/scode][collapse status="false" title="常用DDL"]DDLDDL：[Data Mefinition Language] 数据定义语言关键点： CREATE TABLE、ALTER TABLE、DROP TABLE、CREATE/DROP INDEX 等主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用DDL操作是隐性提交的！不能rollback [scode type="blue"]创建数据库[/scode] # 创建 user 数据库 CREATE DATABASE user; [scode type="blue"]删除数据库[/scode] # 删除user数据库 DROP DATABASE user; [scode type="blue"] 创建表[/scode] # 语法： CREATE TABLE [IF NOT EXISTS] 表名( 字段名 字段类型 【字段约束】, 字段名 字段类型 【字段约束】, 字段名 字段类型 【字段约束】 ); # 案例：创建一张表【没有添加约束】 CREATE TABLE IF NOT EXISTS stuinfo ( stuid INT, stuname VARCHAR (20), stugender CHAR(1), email VARCHAR (20), borndate DATETIME ) ; -- 案例： DROP TABLE IF EXISTS stuinfo; CREATE TABLE IF NOT EXISTS stuinfo( stuid INT PRIMARY KEY, --添加了 主键约束 stuname VARCHAR(20) UNIQUE NOT NULL, -- 添加了 唯一约束+非空 stugender CHAR(1) DEFAULT '男', -- 添加了默认约束 email VARCHAR(20) NOT NULL, age INT CHECK( age BETWEEN 0 AND 100), -- 添加了检查约束，mysql不支持 majorid INT, CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major(id) -- 添加了外键约束-- majorid列 与 major表内的id列 ); [scode type="blue"]修改表[/scode] # 语法： ALTER TABLE 表名 ADD|CHANGE|MODIFY|DROP COLUMN 字段名 字段类型 【字段约束】 # 1，修改表名 ALTER TABLE stuinfo RENAME TO students ; # 2.添加字段 ALTER TABLE students ADD COLUMN borndate TIMESTAMP NOT NULL ; # 3.修改字段名 ALTER TABLE students CHANGE COLUMN borndate birthday DATETIME NULL ; # 4.修改字段类型 ALTER TABLE students MODIFY COLUMN birthday TIMESTAMP ; # 5.删除字段 ALTER TABLE students DROP COLUMN birthday ; [scode type="blue"]删除表[/scode] DROP TABLE IF EXISTS students ; [scode type="blue"] 复制表[/scode] # 复制表 CREATE TABLE newTable LIKE students ; # 复制表的结构+数据 CREATE TABLE newTable SELECT * FROM girls.`admin` #案例：复制employees表中的last_name，department_id，salary字段到新表 xx 表，但不复制数据 CREATE TABLE xx SELECT last_name, department_id, salary FROM myemployees.`employees` WHERE 1=2; # 恒不成立 [/collapse][collapse status="false" title="常用DML"]DMLDML：[Data Manipulation Language] 数据操纵语言关键点： INSERT、UPDATE、DELETE主要是对表数据进行操作，如对表数据进行增、删、改[scode type="blue"]插入数据[/scode]#语法： #插入单行： insert into 表名(字段名1,字段名2,....) value(值1, 值2....) #插入多行： insert into 表名(字段名1,字段名2,....) values(值1, 值2....),(值1, 值2....),... # 案例1： 要求字段和值列表一一对应，且遵循类型和约束的限制 INSERT INTO students(stuid, stuname, stugender, email) VALUES(1, '侠奢', '男', 'xxxx.com'); # 案例2： 可以为空的字段如何插入（email可以为空） INSERT INTO students(stuid, stuname, stugender) VALUES(2, '猪猪', '男'); INSERT INTO students(stuid, stuname, stugender, email) VALUES(3, '笨笨', '男', NULL); # 案例3： 默认字段如何插入（stugerder默认为娚） INSERT INTO students(stuid, stuname, stugender, email) VALUES(4, '傻傻', DEFAULT, NULL); # 案例4： 可以省略字段列表，默认所有字段 INSERT INTO students VALUES(5, '呆呆', '女', NULL) [scode type="blue"]修改数据[/scode]/* 语法： update 表明 set 字段名 = 新值, 字段名 = 新值, ... where 筛选条件 */ # 案例： 将姓名为笨笨的同学的专业编号修改为13号，且邮箱更改为 xx@126.com UPDATE students SET stuid = 13, email = 'xx@126.com' WHERE stuname = '笨笨'; [scode type="blue"]删除数据[/scode]/* 方式1： delete 语句 语法：delete from 表名 where 筛选条件 方式2： truncate 语句 语法：turncate table 表名 */ # 案例1： 删除所有叫名字里带有 傻 的同学的信息 DELETE FROM students WHERE stuname LIKE '%傻%'; # 案例2 ：删除表中所有数据 TRUNCATE TABLE students; # 区别 1. DELETE 可以添加WHERE 条件 TRUNCATE 不能添加，一次全删 2. TRUNCATE 效率高 3. 如果删除带自增长列的表 使用DELETE删除后，重新插入数据，记录容断点处开始 使用TRUNCATE删除后，重新插入数据，记录从1开始 4. DELETE 删除数据，会返回受影响的函数 TRUNCATE 删除数据，不返回受影响的行数 5. DELETE 删除数据，可以支持事务回滚 TRUNCATE 删除数据，不支持事务回滚 [/collapse][collapse status="false" title="常用DCL"]DCLDCL： [Data Control Language] 数据控制语言关键点： GRANT、REVOKE、ROLLBACK、COMMIT主要用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：用户授权---GRANT回滚---ROLLBACKROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。回滚命令使数据库状态回到上次最后提交的状态。其格式为：SQL>ROLLBACK;提交事务---COMMIT [WORK]对数据进行增、删、改操作时，只有当事务在提交到数据库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成后才可以看到。提交数据有三种类型：显式提交、隐式提交和自动提交。显式提交用COMMIT命令直接完成的提交为显式提交。其格式为：SQL>COMMIT；隐式提交用SQL命令间接完成的提交为隐式提交。这些命令是：ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。自动提交若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。其格式为：SQL>SET AUTOCOMMIT ON；开启SQL>SET AUTOCOMMIT OFF；关闭在实际开发中，创建事务的时候需要关闭自动提交（默认是开启的），等操作成功全部成功后显性提交事务。事务提交完毕后重新开启自动提交。[/collapse][collapse status="false" title="常用DQL"]DQLDQL： [Data Query Language ] 数据查询语言关键点： SELECTDQL基本结构是由SELECT语句，FROM语句，WHERE语句组成的查询块：基本语法：SELECT <字段名表> FROM <表或视图名> WHERE <查询条件>[scode type="blue"]基础查询[/scode]# SELECT 查询列表（字段；函数...） FROM 表名； # 1. 查询常量 SELECT 100; # 2. 查询表达式 SELECT 100%3; # 3. 查询单个字段 SELECT `last_name` FROM `employees`; # 4. 查询多个字段 SELECT `last_name`,`email`,`employee_id` FROM `employees`; # 5. 查询所有字段; 格式化 F12 SELECT * FROM `employees`; SELECT `employee_id`, `first_name`, `last_name`, `email` , `phone_number` FROM `employees` # 6. 查询函数（调用方法，获取返回值） SELECT DATABASE();# 查询当前所在数据库 SELECT VERSION();# 查询当前mysql版本 SELECT USER();# root 用户 # 7. 起别名 # 方式一：使用as关键字 SELECT USER() AS "用户名"; SELECT last_name AS "姓 名" FROM employees; # 方式二：使用空格 SELECT last_name "姓名" FROM employees; # 8. 拼接字段 -- 需求：拆线呢 first_name 和last_name 拼接成的全名， -- 最终起别名为： 姓名 # 方案一： 使用+ 错误！ SELECT first_name+last_name AS "姓 名" FROM employees # 方案二： 使用concat SELECT CONCAT(first_name, last_name) AS "姓 名" FROM employees # 9. distinct的使用 -- 需求 查询员工涉及到的部门编号有哪些 SELECT DISTINCT department_id FROM employees; # 查询所有并去重 # 10. 查询表的结构 DESC employees; SHOW COLUMNS FROM employees; # 11. IFNULL的使用 -- 如果表达式1为null，则显示表达式2 SELECT commission_pct,IFNULL(commission_pct,'空') FROM employees; [scode type="blue"]条件查询[/scode]/* select 查询列表 from 表名 where 筛选条件； 执行顺序： 1. from子句 2. where子句 select last_name, first_name from employees where salary > 20000 特点： 1. 按关系表达式筛选 关系运算符：> < >= <= = <>(!=) 2. 按逻辑表达式筛选： 逻辑运算符：and（&&） or（||） not（!） 3. 模糊查询 like in between and is null */ 实例 # 一、按关系表达式查询 # 案例1、 查询部门编号不是100的员工信息 SELECT * FROM employees WHERE job_id <> 100; # 案例2、查询工资小于15000的姓名、工资 SELECT last_name,salary FROM employees WHERE salary < 15000; # 二、按逻辑表达式查询 # 案例1、查询部门标号不是50-100之间员工姓名、部门编号、邮箱 # 方式1 SELECT last_name,department_id,email FROM employees WHERE department_id < 50 OR department_id > 100 # 方式2 SELECT last_name,department_id,email FROM employees WHERE NOT(department_id >= 50 AND department_id <= 100) # 案例2、奖金率大于0.03 或者 员工编号在 60-110 之间的员工信息 SELECT * FROM employees WHERE commission_pct > 0.03 OR (employee_id >= 60 AND employee_id <= 100) # 三、模糊查询 -- 一. LIKE /* 功能：一般和通配符搭配使用，对字符型数据进行匹配查询 常见通配符 _ 任意单个字符 % 任意多个字符 支持0个-多个 */ # 案例1： 查询姓名中包含字符a的员工信息 SELECT * FROM employees WHERE last_name LIKE '%a%'; # 案例2： 查询姓名中最后一个字符为e的员工信息 SELECT * FROM employees WHERE last_name LIKE '%e'; # 案例3： 查询姓名中第一个字符为e的员工信息 SELECT * FROM employees WHERE last_name LIKE 'e%'; # 案例4： 查询姓名中第三个字符为x的员工信息 SELECT * FROM employees WHERE last_name LIKE '__x%'; # 案例5： 查询姓名中第二个字符为_的员工信息 SELECT * FROM employees WHERE last_name LIKE '_\_%'; SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$'; # 设置$为转义字符 -- 二. IN/NOT IN /* 功能：查询某字段的值是否属于指定的列表之内 字段a in（常量值1，常量值2，常量值3，...） 字段a not in（常量值1，常量值2，常量值3，...） */ # 案例1：查询部门编号是30/50/90的员工名、部门编号 SELECT last_name, department_id FROM employees WHERE department_id IN(30,50,90) # 方式2，如果用逻辑表达式： SELECT last_name, department_id FROM employees WHERE department_id = 30 OR department_id = 50 OR department_id = 90; # 案例2：查询工种编号不是SH_CLERK或IT_PROG的员工信息 SELECT * FROM employees WHERE job_id NOT IN ('SH_CLERK','IT_PROG'); # 方式2： SELECT * FROM employees WHERE NOT (job_id = 'SH_CLERK' OR job_id = 'IT_PROG'); -- 三. between and/not between and /* 功能：判断某个字段的值是否介于xx之间 between and/ not between */ # 案例1： 查询部门编号是30-90之间的部门编号、员工姓名 SELECT department_id, last_name FROM employees WHERE department_id BETWEEN 30 AND 90; # 方式2： SELECT department_id, last_name FROM employees WHERE department_id >=30 AND department_id <= 90; # 案例2： 查询年薪不是1000000-2000000之间的员工姓名，工资，年薪 SELECT last_name, salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees WHERE salary*12*(1+IFNULL(commission_pct,0)) NOT BETWEEN 100000 AND 200000; -- 四. is null/ is not null # 案例1： 查询没有奖金的员工信息 SELECT * FROM employees WHERE commission_pct IS NULL; /* --------------------三种等于-------------------------- = 只能判断普通内容 IS 只能判断null值 <=> 安全等于，既能判断普通内容，又能判断null值 */ [scode type="blue"]排序查询[/scode]# 排序查询 /* 语法： SELECT 查询列表 FROM 表名 WHERE 筛选条件 order by 排序列表 执行顺序： 1. from 子句 2. where 子句 3. select 子句 4. order by 子句 举例： SELECT * FROM employees WHERE commission_pct IS NULL; 特点： 1. 排序列表可以是单个字段、多个字段、表达式、函数、列数、以及以上的组合 2. 升序，通过asc, 默认行为 降序，通过desc */ -- 一、 按单个字段排序 # 案例1： 将员工编号>120的员工信息进行工资的升序 SELECT * FROM employees WHERE employee_id > 120 ORDER BY salary ASC -- 二、按表达式排序 # 案例1：对有奖金的员工，按年薪降序 SELECT *, salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC; -- 三、按别名排序 # 案例1：对有奖金的员工，按年薪降序 SELECT *, salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees WHERE commission_pct IS NOT NULL ORDER BY 年薪 DESC; -- 四、按函数的结果排序 # 案例1： 按姓名的字数长度进行升序 SELECT LENGTH(last_name), last_name FROM employees ORDER BY LENGTH(last_name); -- 五、按多个字段排序 # 案例1：查询员工的姓名、工资、部门编号，先按工资升序，再按部门编号降序 SELECT last_name, salary, department_id FROM employees ORDER BY salary ASC, department_id DESC; -- 六、按列数排序 # 案例1： 按第二列字符升序排序 SELECT * FROM employees ORDER BY 2 [scode type="blue"]分组查询[/scode]执行顺序： 1. from 子句 2. where 子句 3. group by 子句 4. having 子句 5. select 子句 6. order by 子句 特点： 1. 查询列表往往是 分组函数和被分组的字段 2. 分组查询中的筛选分为两类 筛选的基表 使用的关键词 位置 分组前筛选 原始表 where group by 之前 分组后筛选 分组后的结果集 having group by 之后 注： 分组函数做条件只能放在having之后 */ -- 一、基本操作 # 案例1：查询每个工种的员工的平均工资 SELECT AVG(salary), job_id FROM employees GROUP BY job_id; # 案例2：查询每个领导的手下人数 SELECT COUNT(*), manager_id FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id -- 二、可以实现分组前的筛选 # 案例1：查询邮箱中包含a字符的每个部门的最高工资 SELECT MAX(salary) 最高工资, email, department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id; # 案例2：查询每个领导手下有奖金的员工的平均工资 SELECT AVG(salary) 平均工资, manager_id, commission_pct FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id; -- 三、可以实现分组后的筛选 ---> HAVING # 案例1：查询那个部门的员工个数>5 1. 查询每个部门的员工个数 SELECT COUNT(*) 员工个数, department_id FROM employees GROUP BY department_id; 2. 在刚才的结果基础上，筛选哪个部门的员工个数>5 SELECT COUNT(*) 员工个数, department_id FROM employees GROUP BY department_id; HAVING COUNT(*) > 5 # 案例2：每个工种有奖金的员工的最高工资 > 12000的工种编号和最高工资 SELECT job_id 工种编号, MAX(salary) 最高工资 FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary) > 1000; # 案例3：领导编号>102的每个领导手下的最低工资大于5000的最低工资 SELECT MIN(salary) 最低工资, manager_id FROM employees WHERE manager_id > 102 GROUP BY manager_id HAVING MIN(salary) > 5000; -- 四、可以实现排序 # 案例：查询有奖金的员工的最高工资>6000的工种编号和最高工资，按最高工资升序 # 分析1： 按工种分组，查询每个工种有奖金的员工的最高工资 SELECT MAX(salary) 最高工资, job_id 工种编号 FROM employees WHERE commission_pct IS NOT NULL GROUP BY salary HAVING MAX(salary) > 6000; ORDER BY MAX(salary) ASC; -- 五、按多个字段分组 # 案例：查询每个工种每个部门的最低工资，并按最低工资降序 # 注：工种和部门相同的，为一组 SELECT MIN(salary) 最低工资, job_id, department_id FROM employees GROUP BY job_id, department_id ORDER BY MIN(salary) DESC; [scode type="red"]连接查询[/scode][scode type="red"]子查询[/scode][scode type="red"]分页查询[/scode][scode type="red"]联合查询[/scode][scode type="blue"]常见函数-字符函数[/scode]-- 一、字符函数 # 1. concat 拼接字符 SELECT CONCAT('hello', first_name, last_name) 备注 FROM employees # 2. length 获取字节长度 SELECT LENGTH('hello,大侠'); # 3. char_length 获取字符长度 SELECT CHAR_LENGTH('hello,大侠'); # 4. substring 截取子串 # substring(str,起始索引（从1开始）,截取字符长度) # substring(str,起始索引（从1开始）) SELECT SUBSTRING('你好哇！李银河',1,3); # 5. instr 获取字符串第一次出现的索引 SELECT INSTR('hello,world,我是侠奢', '侠奢'); # 6. trim 去除前后空格(任意字符) SELECT TRIM(' 侠奢 ') AS 我; SELECT TRIM('a' FROM 'aaaaaaa侠奢aaa') AS 我; # 7. lpad/rpad 左填充/ 右填充 SELECT LPAD('侠奢',10,'帅'); SELECT RPAD('侠奢',10,'帅'); # 8. upper/lower 变大写/小写 # 案例1：查询员工表的姓名，要求格式：姓首字母大写，其他字符小写，名所有字符大写，且姓和名之间用_分割。 # 最后起别名“OUTPUT” SELECT UPPER(SUBSTR(first_name,1,1)), first_name FROM employees; SELECT LOWER(SUBSTR(first_name,2)) FROM employees; SELECT UPPER(last_name) FROM employees; SELECT CONCAT(UPPER(SUBSTR(first_name,1,1)), LOWER(SUBSTR(first_name,2)) ,'_', UPPER(last_name)) "OUTPUT" FROM employees; # 9. strtmp 比较两个字符大小 # 前面大返回1，后面大返回-1.否则返回0 SELECT STRCMP('abc', 'abc'); # 10. left/right 截取子串 SELECT LEFT('golang天下第一',6); # 返回golang [scode type="blue"]常见函数-数学函数[/scode] -- 二、数学函数 # 1. ABS 求绝对值 SELECT ABS(-2.4); # 2. ceil 向上取整 SELECT CEIL(1.09) # 返回2 # 3. floor 向下取整 SELECT FLOOR(1.99)； # 返回1 # 4. round 四舍五入 SELECT ROUND（4.6） # 返回5 # 5. truncate 截断 SELECT TRUNCATE(1.876567, 1); # 返回1.8 SELECT TRUNCATE(1.876567, 2); # 返回1.87 # 6. mod 取余 SELECT MOD(-10, 3); # 返回1 SELECT -10%3; [scode type="blue"]常见函数-日期函数[/scode]-- 三、日期函数 # 1. NOW 当前日期+时间 SELECT NOW(); # 2. curdate 当前日期 SELECT CURDATE(); # 3. curtime 当前时间 SELECT CURTIME(); # 4. datediff 日期之差 SELECT DATEDIFF('2020-1-1','2000-1-1'); # 5. date_farmat 日期固定格式 SELECT DATE_FORMAT('2010-2-2','%Y年%M月%d日 %H时%i分%s秒')； # 返回 ；2010年February月02日 00时00分00秒 # 6. str_to_date 按指定格式解析字符串为日期类型 SELECT STR_TO_DATE('3/15 1998', '%m/%d %Y')； [/collapse]
从15个方面，分享sql优化的一些小技巧避免使用select *很多时候，我们写sql语句时，为了方便，喜欢直接使用select *，一次性查出表中所有列的数据，例如:select * from user where id=1; 在实际业务场景中，可能我们真正需要使用的只有其中一两列。查了很多数据，但是不用，白白浪费了数据库资源，比如：内存或者cpu。此外，多查出来的数据，通过网络IO传输的过程中，也会增加数据传输的时间。还有一个最重要的问题是：select *不会走覆盖索引，会出现大量的回表操作，而从导致查询sql的性能很低。我们需要sql语句查询时，只查需要用到的列，多余的列根本无需查出来select name,age from user where id=1; 用union all代替union我们都知道sql语句使用union关键字后，可以获取排重后的数据。而如果使用union all关键字，可以获取所有数据，包含重复的数据。(select * from user where id=1) union (select * from user where id=2); 排重的过程需要遍历、排序和比较，它更耗时，更消耗cpu资源。所以如果能用union all的时候，尽量不用union。(select * from user where id=1) union all (select * from user where id=2); 小表驱动大表小表驱动大表，也就是说用小表的数据集驱动大表的数据集。假如有order和user两张表，其中order表有10000条数据，而user表有100条数据。这时如果想查一下，所有有效的用户下过的订单列表。可以使用in关键字实现：select * from order where user_id in (select id from user where status=1) 也可以使用exists关键字实现：select * from order where exists (select 1 from user where order.user_id = user.id and status=1) 前面提到的这种业务场景，使用in关键字去实现业务需求，更加合适。为什么呢？因为如果sql语句中包含了in关键字，则它会优先执行in里面的子查询语句，然后再执行in外面的语句。如果in里面的数据量很少，作为条件查询速度更快。而如果sql语句中包含了exists关键字，它优先执行exists左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。如果匹配上，则可以查询出数据。如果匹配不上，数据就被过滤掉了。这个需求中，order表有10000条数据，而user表有100条数据。order表是大表，user表是小表。如果order表在左边，则用in关键字性能更好。总结：in 适用于左边大表，右边小表exists 适用于左边小表，右边大表不管是用in，还是exists关键字，其核心思想都是用小表驱动大表。批量操作有一批数据经过业务处理之后，需要插入数据for(Order order: list){ orderMapper.insert(order): } 然后在循环中逐条插入数据。insert into order(id,code,user_id) values(123,'001',100); 该操作需要多次请求数据库，才能完成这批数据的插入。应该优化为：orderMapper.insertBatch(list)； 提供一个批量插入数据的方法。insert into order(id,code,user_id) values(123,'001',100),(124,'002',100),(125,'003',101); 这样只需要远程请求一次数据库，sql性能会得到提升，数据量越多，提升越大。但需要注意的是，不建议一次批量操作太多的数据，如果数据太多数据库响应也会很慢。批量操作需要把握一个度，建议每批数据尽量控制在500以内。如果数据多于500，则分多批次处理。多用limit有时候，我们需要查询某些数据中的第一条，比如：查询某个用户下的第一个订单，想看看他第一次的首单时间select id, create_date from order where user_id=123 order by create_date asc; 根据用户id查询订单，按下单时间排序，先查出该用户所有的订单数据，得到一个订单集合。然后在代码中，获取第一个元素的数据，即首单的数据，就能获取首单时间。List<Order> list = orderMapper.getOrderList(); Order order = list.get(0); 虽说这种做法在功能上没有问题，但它的效率非常不高，需要先查询出所有的数据，有点浪费资源。优化为下述：select id, create_date from order where user_id=123 order by create_date asc limit 1; 使用limit 1，只返回该用户下单时间最小的那一条数据即可。此外，在删除或者修改数据时，为了防止误操作，导致删除或修改了不相干的数据，也可以在sql语句最后加上limit。例如：update order set status=0,edit_time=now(3) where id>=100 and id<200 limit 100; 这样即使误操作，比如把id搞错了，也不会对太多的数据造成影响。in中值太多对于批量查询接口，我们通常会使用in关键字过滤出数据。比如：想通过指定的一些id，批量查询出用户信息。sql语句如下：select id,name from category where id in (1,2,3...100000000); 如果我们不做任何限制，该查询语句一次性可能会查询出非常多的数据，很容易导致接口超时select id,name from category where id in (1,2,3...100) limit 500; 可以在sql中对数据用limit做限制。不过我们更多的是要在业务代码中加限制，伪代码如下：public List<Category> getCategory(List<Long> ids) { if(CollectionUtils.isEmpty(ids)) { return null; } if(ids.size() > 500) { throw new BusinessException("一次最多允许查询500条记录") } return mapper.getCategoryList(ids); } 还有一个方案就是：如果ids超过500条记录，可以分批用多线程去查询数据。每批只查500条记录，最后把查询到的数据汇总到一起返回。增量查询有时候，我们需要通过远程接口查询数据，然后同步到另外一个数据库。示例：select * from user; 如果直接获取所有的数据，然后同步过去。这样虽说非常方便，但是带来了一个非常大的问题，就是如果数据很多的话，查询性能会非常差，将其改为：select * from user where id>#{lastId} and create_time >= #{lastCreateTime} limit 100; 按id和时间升序，每次只同步一批数据，这一批数据只有100条记录。每次同步完成之后，保存这100条数据中最大的id和时间，给同步下一批数据的时候用。通过这种增量查询的方式，能够提升单次查询的效率。高效的分页有时候，列表页在查询数据时，为了避免一次性返回过多的数据影响接口性能，我们一般会对查询接口做分页处理。在mysql中分页一般用的limit关键字：select id,name,age from user limit 10,20; 如果表中数据量少，用limit关键字做分页，没啥问题。但如果表中数据量很多，用它就会出现性能问题。比如现在分页参数变成了：select id,name,age from user limit 1000000,20; mysql会查到1000020条数据，然后丢弃前面的1000000条，只查后面的20条数据，这个是非常浪费资源的，优化sql：select id,name,age from user where id > 1000000 limit 20; 先找到上次分页最大的id，然后利用id上的索引查询。不过该方案，要求id是连续的，并且有序的同时我们还能使用between优化分页select id,name,age from user where id between 1000000 and 1000020; 需要注意的是between要在唯一索引上分页，不然会出现每页大小不一致的问题用连接查询代替子查询mysql中如果需要从两张以上的表中查询出数据的话，一般有两种实现方式：子查询 和 连接查询。子查询的例子如下：select * from order where user_id in (select id from user where status=1) 子查询语句可以通过in关键字实现，一个查询语句的条件落在另一个select语句的查询结果中。程序先运行在嵌套在最内层的语句，再运行外层的语句。子查询语句的优点是简单，结构化，如果涉及的表数量不多的话。但缺点是mysql执行子查询时，需要创建临时表，查询完毕后，需要再删除这些临时表，有一些额外的性能消耗。这时可以改成连接查询。具体例子如下：select o.* from order o inner join user u on o.user_id = u.id where u.status=1 join的表不宜过多根据阿里巴巴开发者手册的规定，join表的数量不应该超过3个错误示例：select a.name,b.name.c.name,d.name from a inner join b on a.id = b.a_id inner join c on c.b_id = b.id inner join d on d.c_id = c.id inner join e on e.d_id = d.id inner join f on f.e_id = e.id inner join g on g.f_id = f.id 如果join太多，mysql在选择索引的时候会非常复杂，很容易选错索引并且如果没有命中中，nested loop join 就是分别从两个表读一行数据进行两两对比，复杂度是 n^2所以我们应该尽量控制join表的数量join时要注意在涉及到多张表联合查询的时候，一般会使用join关键字，而join使用最多的是left join和inner joinleft join：求两个表的交集外加左表剩下的数据inner join：求两个表交集的数据示例如下：select o.id,o.code,u.name from order o inner join user u on o.user_id = u.id where u.status=1; 如果两张表使用inner join关联，mysql会自动选择两张表中的小表，去驱动大表，所以性能上不会有太大的问题。使用left join的示例如下：select o.id,o.code,u.name from order o left join user u on o.user_id = u.id where u.status=1; 如果两张表使用left join关联，mysql会默认用left join关键字左边的表，去驱动它右边的表。如果左边的表数据很多时，就会出现性能问题要特别注意的是在用left join关联查询时，左边要用小表，右边可以用大表。如果能用inner join的地方，尽量少用left join控制索引的数量众所周知，索引能够显著的提升查询sql的性能，但索引数量并非越多越好因为表中新增数据时，需要同时为它创建索引，而索引是需要额外的存储空间的，而且还会有一定的性能消耗，阿里巴巴的开发者手册中规定，单表的索引数量应该尽量控制在5个以内，并且单个索引中的字段数不超过5个mysql使用的B+树的结构来保存索引的，在insert、update和delete操作时，需要更新B+树索引。如果索引过多，会消耗很多额外的性能。[scode type="red"]如果表中的索引太多，超过了5个该怎么办？[/scode]这个问题要辩证的看，如果你的系统并发量不高，表中的数据量也不多，其实超过5个也可以，只要不要超过太多就行。但对于一些高并发的系统，请务必遵守单表索引数量不要超过5的限制那么，高并发系统如何优化索引数量？能够建联合索引，就别建单个索引，可以删除无用的单个索引。将部分查询功能迁移到其他类型的数据库中，比如：Elastic Seach、HBase等，在业务表中只需要建几个关键索引即可选择合理的字段类型选择字段类型时，应该遵循这样的原则：能用数字类型，就不用字符串，因为字符的处理往往比数字要慢尽可能使用小的类型，比如：用bit存布尔值，用tinyint存枚举值等长度固定的字符串字段，用char类型长度可变的字符串字段，用varchar类型金额字段用decimal，避免精度丢失问题......提升group by的效率我们有很多业务场景需要使用group by关键字，它主要的功能是去重和分组。通常它会跟having一起配合使用，表示分组后再根据一定的条件过滤数据，示例：select user_id,user_name from order group by user_id having user_id <= 200; 这种写法性能不好，它先把所有的订单根据用户id分组之后，再去过滤用户id大于等于200的用户。分组是一个相对耗时的操作，为什么我们不先缩小数据的范围之后，再分组呢？示例：select user_id,user_name from order where user_id <= 200 group by user_id 使用where条件在分组前，就把多余的数据过滤掉了，这样分组时效率就会更高一些索引优化[scode type="blue"]sql优化当中，有一个非常重要的内容就是：索引优化[/scode]很多时候sql语句，走了索引，和没有走索引，执行效率差别很大所以索引优化被作为sql优化的首选。索引优化的第一步是：检查sql语句有没有走索引。那么，如何查看sql走了索引没？可以使用explain命令，查看mysql的执行计划。例如：EXPLAIN SELECT * FROM sys_user WHERE nick_name='Lehman'; 结果：通过这几列可以判断索引使用情况，执行计划包含列的含义如下图所示：sql语句没有走索引，排除没有建索引之外，最大的可能性是索引失效了，常见索引失效的常见原因：
[scode type="red"]基于MYSQL[/scode][collapse status="false" title="常见命令"]# root密码登录 mysql -u root -p # 指定ip地址密码登录 mysql -u root -h 192.168.1.1 -p # 查询用户和主机(以root身份) use mysql; select user,host from user; [/collapse][collapse status="false" title="远程主机无法访问"][scode type="yellow"]一般情况：修改host,然后问题解决[/scode]Host XXX is not allowed to connect to this MySQL server修改host# 以root身份登录 use mysql; # 查看host的状态 select host from user where user='root'; # 将Host设置为通配符% update user set host = '%' where user ='root'; # 刷新权限即可 flush privileges; [scode type="yellow"]特殊情况：修改host仍然未解决[/scode]1.3306端口冲突解决方法：# 打开命令行，查看3306端口 netstat -ano | findstr "3306" # 查看占用端口的程序 tasklist | findstr "9158" # 发现有两个mysql进程都占据了3306端口； # 可使用快捷键win+r,输入`services.msc`,在服务中关闭一个mysql或者直接使用命令杀掉一个进程（如杀掉9158进程） taskkill /pid 9158 # 然后问题解决 [/collapse][collapse status="false" title="忘记密码"][scode type="yellow"]windows环境[/scode]1.在mysql安装目录下,找到my.ini文件，打开文件，在[mysqld]后面任意行，加入skip-grant-tables；2.重启MySQL：使用快捷键win+r打开运行，输入services.msc,回车打开服务，找到mysql服务，然后右击选择重启MySQL；3.打开cmd命令行，输入mysql -u root -p,直接登录mysql;4.修改root密码use mysql; # 旧的修改语句 update user set password=password("你的新密码") where user="root"; # 使用旧的修改语句如果报错：Unknown column ‘password’ in ‘field list’，则使用下面语句： # 注：新版本的mysql对密码有要求，具体见附录 update mysql.user set authentication_string=password('root') where user='root' ; # 刷新权限 flush privileges; 5.再次打开my.ini文件，删除skip-grant-tables,然后重启mysql服务。[scode type="yellow"]linux环境[/scode]1.打开my.cnf文件，vi /etc/my.cnf，[mysqld]后面任意行，加入skip-grant-tables，用来跳过密码验证的过程；2.使用/etc/init.d/mysql restart(或可能需要使用/etc/init.d/mysqld restart)重启mysql服务；3.登录mysql,并修改密码，具体过程与windows环境一致；4.修改完成后再打开my.cnf文件，删除skip-grant-tables,然后重启mysql服务。[/collapse][collapse status="false" title="添加用户并授权"]# 以root用户登录mysql mysql -u root -p use mysql; # 添加只能本地访问的新用户 create user 'user'@'localhost' identified by 'user!123456'; # 添加外网ip可以访问的新用户 create user 'user'@'%' identified by 'user!123456'; # 用户创建完成后需要授权 flush privileges; # 基于业务的授权 grant all privileges on dbname.* to user@'%' identified by 'user!123456'; grant all privileges on dbname.* to user@'%' identified by 'user!123456' with grant option; # 基于管理员的授权(两者差别见参考地址1) grant all privileges on *.* to user@'%' identified by 'user!123456'; grant all privileges on *.* to user@'%' identified by 'user!123456' with grant option; # 授权后，刷新权限 flush privileges; 参考地址：同样是MySQL的all privileges有啥不同？使用工具可参考：》》》局域网数据库共享[post cid="32" /][/collapse][collapse status="false" title="数据库表大小写区分"]mysql默认情况下是否区分大小写，使用show Variables like '%table_names' // 查看lower_case_table_names的值 0代表区分，1代表不区分。设置不区分大小写（反之同理修改为0）lower_case_table_names=1 [/collapse][collapse status="false" title="创建函数出错"]ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)这是我们开启了bin-log, 我们就必须指定我们的函数是否是DETERMINISTIC 确定性的NO SQL 没有SQl语句，当然也不会修改数据READS SQL DATA 只是读取数据，当然也不会修改数据MODIFIES SQL DATA 要修改数据CONTAINS SQL 包含了SQL语句其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。在MySQL中创建函数时出现这种错误的解决方法：set global log_bin_trust_function_creators=TRUE; [/collapse][collapse status="false" title="创建表出错-提示表空间已存在"]ERROR 1813 (HY000): Tablespace ‘`库名`.`表名`‘ exists. 出现这个问题的大部分原因，在使用 InnoDB 引擎的数据库中，所有已经存在的表都使在使用 InnoDB 引擎的数据库中，所有已经存在的表都使用两个文件保存。假设表名为 test1，则在数据库的数据目录下会有两个文件：test1.frm 文件，存储数据表的定义信息test1.ibd 文件，存储数据表的内容如果上述的 frm 文件某种原因丢失或者损坏，再去操作（比如删除这个表重新创建表"drop table if exists tbl_test;"）可能就会报上面的错。解决问题[scode type="yellow"]适用于 MySQL 5.6 及以前的版本[/scode]先关闭 MySQL 的数据库服务，找到 MySQL 的安装目录下的 data 目录，找到报错的对应的库的目录进去，对应的表的 .ibd 文件直接删掉，重启服务即可。data目录可以通过下面的SQL获取show global variables like "%datadir%"; [scode type="yellow"]MySQL 5.7 版本的解决办法[/scode]MySQL 5.7 之后因为数据库会默认使用表空间隔离，所以上面的第二种办法就不好使了，因为这个时候怎么创建临时的库和相同名的表，它们的 .frm 和 .ibd 这两个文件并不能通用。第一种直接删除然而从新创建相同表还是会报错。那如何解决呢？先关闭 MySQL 的数据库服务，删除掉报错的那个表的老的 .ibd 文件，比如 test1.ibd。修改 my.ini 文件（Linux 系统自行对应 my.cnf），修改 innodb_file_per_table=0，如果没有在最后面添加即可，保存文件，重启数据库服务。新建一个临时的库（库名随便取，比如 testdb），并创建一个同名的表，比如上面的假设表为 test1。这时候会在这个临时的库（testdb）的目录下出现这个表的 .frm 文件，比如 test1.frm。将这个文件拷贝到报错的数据库目录下覆盖粘贴即可。回到之前报错的库删除或重建这个表就不会报错了最后不要忘了把修改的 innodb_file_per_table=0 换成 innodb_file_per_table=1 ，或者直接屏蔽或删除掉，保存重启数据库服务data目录可以通过下面的SQL获取show global variables like "%datadir%"; [/collapse]
[collapse status="false" title="表生成Excel"]SELECT COLUMN_NAME 列名, COLUMN_TYPE 数据类型, DATA_TYPE 字段类型, CHARACTER_MAXIMUM_LENGTH 长度, IS_NULLABLE 是否为空, COLUMN_DEFAULT 默认值, COLUMN_COMMENT 备注 FROM INFORMATION_SCHEMA. COLUMNS WHERE table_schema = 'sxepp' AND table_name = 'sx_jc_car' sxepp为数据库名称，到时候只需要修改成你要导出表结构的数据库即可sx_jc_car为表名，到时候换成你要导出的表的名称table_name 如果不写的话，默认会查询出所有表中的数据，这样可能就分不清到底哪些字段是哪张表中的了，所以还是建议写上要导出的名名称[/collapse][collapse status="false" title="插入大量数据"]创建随机生成字符串函数delimiter $$ CREATE FUNCTION rand_string(n int) RETURNS varchar(255) begin declare chars_str varchar(100) default "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"; declare return_str varchar(255) default ""; declare i int default 0; while i < n do set return_str=concat(return_str,substring(chars_str,floor(1+rand()*62),1)); set i= i+1; end while; return return_str; end $$ delimiter ; 创建循环插入函数delimiter $$ CREATE PROCEDURE `insert_data`(IN n int) BEGIN DECLARE i INT DEFAULT 1; WHILE (i <= n ) DO INSERT into system_inventory (i_on,related_units,opening_date ) VALUEs (rand_string(20),FLOOR(RAND() * 100) ,now() ); set i=i+1; END WHILE; END $$ delimiter ; 执行插入函数call insert_data(10000); [/collapse][collapse status="false" title="递归查询"]查询子节点的函数 查询时 包含自身（示例）create function getChildrenByFolder(folderId INT) returns varchar(4000) BEGIN DECLARE temp VARCHAR(4000); DECLARE tempChild VARCHAR(4000); SET temp = ''; SET tempChild = CAST(folderId AS CHAR); WHILE tempChild IS NOT NULL DO SET temp = CONCAT(temp,',',tempChild); SELECT GROUP_CONCAT(folder_id) INTO tempChild FROM system_base_folder WHERE FIND_IN_SET(parent_id,tempChild) > 0; END WHILE; RETURN temp; END 使用函数 进行递归查询SELECT * FROM system_base_folder WHERE FIND_IN_SET(folder_id,getChildrenByFolder(4)); 查询父节点 查询的时候 包含自身（示例）CREATE FUNCTION `getParentByOrganizationr`(organizationId longtext) RETURNS longtext CHARSET utf8 BEGIN DECLARE temp longtext default ''; DECLARE tempParent longtext default organizationId; WHILE organizationId is not null do SET temp =(SELECT DISTINCT parent_id FROM sys_organization WHERE organization_id = organizationId); IF temp is not null THEN SET tempParent = concat(tempParent, ',', temp); SET organizationId = temp; ELSE SET organizationId = temp; END IF; END WHILE; return tempParent; END 使用函数 进行递归查询SELECT * FROM sys_organization WHERE FIND_IN_SET(organization_id ,getParentByOrganizationr(4)) [scode type="red"]Tips:如果创建函数失败出现并为以下错误，请参考SQL常见问题[/scode]ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)MYSQL8.0及其版本以上 可以使用 WITH RECURSIVE 实现递归写法比较简单，也比较灵活，但是只适用于MySQL8.0及以上版本，这种写法其实和 PostgreSQL 的写法是一样的。[/collapse][collapse status="false" title="局域网数据库共享"]追加用户/派发权限GRANT ALL PRIVILEGES ON *.* TO 'user'@'%'IDENTIFIED BY '123456' WITH GRANT OPTION; FLUSH PRIVILEGES 局域网用户访问缓存清空flush hosts [scode type="yellow"]也可以使用Navicat工具添加用户并分配指定库和权限[/scode][/collapse][collapse status="false" title="修改最大连接数"]查看最大连接数SHOW VARIABLES LIKE '%max_connections%'; 修改最大连接数SET GLOBAL max_connections = 2000; [/collapse][collapse status="false" title="修改GROUP_CONCAT最大长度的限制"][scode type="red"]GROUP_CONCAT有个最大长度的限制，超过最大长度就会被截断掉[/scode]查看目前最大获得长度SELECT @@global.group_concat_max_len; 修改最大长度SET GLOBAL group_concat_max_len=18446744073709551615 ; mysql版本号最小值最大值备注32 位442949672952^3264 位4184467440737095516152^64)[scode type="red"]有效最大长度受max_allowed_packet的值约束，默认值为4M[/scode]查看目前配置show VARIABLES like '%max_allowed_packet%'; 修改MySQL配置文件中my.conf或my.ini中添加或修改:#[mysqld] max_allowed_packet = 500M group_concat_max_len=18446744073709551615 [/collapse][collapse status="false" title="修改wait_timeout"][scode type="share"]interactive_timeout针对交互式连接，wait_timeout针对非交互式连接。所谓的交互式连接，即在mysql_real_connect()函数中使用了CLIENT_INTERACTIVE选项，说得直白一点，通过mysql客户端连接数据库是交互式连接，通过jdbc连接数据库是非交互式连接。[/scode]查看非交互式超时时间SHOW GLOBAL VARIABLES LIKE 'wait_timeout'; 修改非交互式超时时间，单位为毫秒SET GLOBAL wait_timeout = 604800; [/collapse][collapse status="false" title="修改interactive_timeout"][scode type="share"]interactive_timeout针对交互式连接，wait_timeout针对非交互式连接。所谓的交互式连接，即在mysql_real_connect()函数中使用了CLIENT_INTERACTIVE选项，说得直白一点，通过mysql客户端连接数据库是交互式连接，通过jdbc连接数据库是非交互式连接。[/scode]查看交互式超时时间SHOW GLOBAL VARIABLES LIKE 'interactive_timeout'; 修改交互式超时时间(毫秒)SET GLOBAL interactive_timeout = 604800; [/collapse][collapse status="false" title="查看所有用户的当前连接"]SHOW FULL PROCESSLIST; [/collapse][collapse status="false" title="查看MySQL服务器所有状态"]SHOW STATUS; 属性说明Aborted_clients由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。Aborted_connects尝试已经失败的MySQL服务器的连接的次数。Connections试图连接MySQL服务器的次数。Created_tmp_tables当执行语句时，已经被创造了的隐含临时表的数量。Delayed_insert_threads正在使用的延迟插入处理器线程的数量。Delayed_writes用INSERT DELAYED写入的行数。Delayed_errors用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。Flush_commands执行FLUSH命令的次数。Handler_delete请求从一张表中删除行的次数。Handler_read_first请求读入表中第一行的次数。Handler_read_key请求数字基于键读行。Handler_read_next请求读入基于一个键的一行的次数。Handler_read_rnd请求读入基于一个固定位置的一行的次数。Handler_update请求更新表中一行的次数。Handler_write请求向表中插入一行的次数。Key_blocks_used用于关键字缓存的块的数量。Key_read_requests请求从缓存读入一个键值的次数。Key_reads从磁盘物理读入一个键值的次数。Key_write_requests请求将一个关键字块写入缓存次数。Key_writes将一个键值块物理写入磁盘的次数。Max_used_connections同时使用的连接的最大数目。Not_flushed_key_blocks在键缓存中已经改变但是还没被清空到磁盘上的键块。Not_flushed_delayed_rows在INSERT DELAY队列中等待写入的行的数量。Open_tables打开表的数量。Open_files打开文件的数量。Open_streams打开流的数量(主要用于日志记载)Opened_tables已经打开的表的数量。Questions发往服务器的查询的数量。Slow_queries要花超过long_query_time时间的查询数量。Threads_connected当前打开的连接的数量。Threads_running不在睡眠的线程数量。Uptime服务器工作了多少秒。[/collapse][collapse status="false" title="SQL优化"]请参考另一篇文章[post cid="317" /][/collapse]