https://blog.boychai.xyz/index.php/tag/SQL/ https://blog.boychai.xyz/index.php/archives/64/ 2023-09-05T04:51:00+00:00 插入数据批量插入数据推荐使用下面语句INSERT INTO 表名(字段1，字段2，......) VALUES (值1，值2，......),(值1，值2，......),(值1，值2，......);如果数据量过于庞大，数据条数大于1000推荐使用文件导入的方式进行插入，方法如下## 登录mysql的时候需要添加--local-infile参数 mysql --local-infile -u root -p ## 登录之后设置local_infile为1,开启从本地加载文件导入数据的开关 set global local_infile=1; ## 使用load命令导入数据 load data local infile '文件' into table `表名` fields terminated by `文件里面的分隔符` lines terminated by '\n';文件里面的内容格式如下1,a,b,c 2,b,c,dPS:主键顺序插入性能高于乱序插入主键优化数据组织方式在InnoDB存储引擎中，表数据都是根据逐渐顺序组织存放的，这种存储方式的表成为索引组织表(index organized table IOT)页分裂页可以为空 ，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果 一行数据多大，会溢出)，根据主键排列。页合并当删除一条记录时，实际上记录并没有被物理删除，只是被标记(flaged)为啥暗处并且他的空间变得允许被其他记录声明使用。当页面中删除的记录达到MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并已优化空间使用。主键设置原则满足业务要求的情况下,尽量降低主键长度。插入数据的时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。尽量不要使用UUID做主键或者是其他自然主键，如身份证。业务操作时，避免对主键的修改。order by优化Using filesort通过表的索引或全表扫描,读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。Using index通过有序索引顺序扫描直接返回有序数据，这种情况即为using index,不需要额外排序，操作效率更高。优化根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。尽量使用覆盖索引。多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)如果不可避免出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。group by优化分组操作时，可以通过索引来提高效率。分组操作时，索引的使用也是满足最左前缀法则的。limit优化一般分页查询时，通过创建 覆盖索引 能够比较好的提高性能，可以通过覆盖索引加子查询形式进行优化。count优化在MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高。InnoDB引擎就麻烦了，他执行count(*)的时候，需要把数据一行一行的从引擎里面读出来，让后累计计数。count(主键)InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行累加(主键不可能为null)。count(字段)没有not null约束时: InnoDB疫情会便利整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。有not null约束时: InnoDB疫情会便利整张表把每一行的字段值都取出来，返回给服务层们直接按行进行累加。count(1)InnoDB疫情遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行累加。count(*)InnoDB疫情并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。优化方案可以自己做count，使用缓存数据库做这类工作。按照效率排序的话，count(字段)<count(主键)<count(1)≈count(*)，所以尽量使用count(*)。update优化InnoDB的行锁是针对索引加的锁，不是针对记录家的锁，并且该索引不能失效，否则会从行锁升级为表锁。规避这种情况就需要在对应字段创建索引。 https://blog.boychai.xyz/index.php/archives/61/ 2023-08-10T05:36:00+00:00 SQL分类类别全称概述DDLData Definition Language数据定义语言，用来定义数据库对象(数据库，表，字段)DMLData Manipulation Language数据操作语言，用来对数据库表中的数据进行删增改查DQLData Query Language数据查询语言，用来查询数据库中表的记录DCLData Control Language数据控制语言，用来创建数据库用户，控制数据库的访问权限DDL数据库操作查询查询所有数据库SHOW DATABASES;查询当前数据库SELECT DATABASE();创建CREATE DATABASE [ IF NOT EXISTS ] 数据库名称 [ DEFAULT CHARSET 字符集 ] [ COLLATE 排序规则 ];使用时可以在数据库名称前面加入"IF NOT EXISTS"，意为当数据库不存在则创建否则不作为。删除DROP DATABASE [ IF EXISTS ] 数据库名称;使用时可以在数据库名称前面加入"IF EXISTS"，意为当数据库存在则删除否则不作为。使用USE 数据库名称;表操作ps：表操作需要使用数据库之后才能操作查询查询当前数据库中所有的表SHOW TABLES;查询表字段结构DESC 表名称;查询指定表的建表语句SHOW CREATE TABLE 表名;创建CREATE TABLE 表名(​ 字段1 字段1数据类型 [ COMMENT 字段1注释 ],​ 字段2 字段2数据类型 [ COMMENT 字段2注释 ],​ 字段3 字段3数据类型 [ COMMENT 字段3注释 ]​ ......) [ COMMENT 表格注释 ];修改修改表名称ALTER TEABLE 表名 RENAME TO 新表名;删除删除表DROP TABLE [ IF EXISTS ] 表名;"IF EXISTS" 意为有则删除否则不作为删除指定表，并且重新创建该表(一般用于格式化)TRUNCATE TABLE 表名;字段操作修改添加字段ALTER TABLE 表名 ADD 字段名 类型(长度) [ COMMENT 注释 ] [约束];修改数据类型ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);修改字段名和字段类型ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [ COMMENT 注释 ] [约束];删除ALTER TABLE 表名 DROP 字段名数据类型概述MYSQL的数据类型主要分为三种：数值类型、字符串类型、日期时间类型。数值类型类型大小有符号范围(SIGNED)无符号范围(UNSIGNED)概述TINYINT1 byte(-128，127)(0，255)小整数值SMALLINT2 byte(-32768，32767)(0，65535)大整数值MEDIUMINT3 byte(-8388608，8388607)(0，16777215)大整数值INT或INTEGER4 byte(-8388608，8388607)(0，4294967295)大整数值BIGINT8 byte(-2^63，2^63-1)(0，2^64-1)极大整数值FLOAT4 byte(-3.402823466 E+38，3.402823466351 E+38)0 和 (1.175494351 E38，3.402823466 E+38)单精度浮点数值DOUBLE8 byte(-1.7976931348623157E+308，1.7976931348623157E+308)0 和(2.2250738585072014E-308，1.7976931348623157E+308)双精度浮点数值DECIMAL 依赖于M(精度)和D(标度)的值依赖于M(精度)和D(标度)的值小数值(精确定点数)字符串类型类型大小概述CHAR0-255 bytes定长字符串(需要指定长度)VARCHAR0-65535 bytes变长字符串(需要指定长度)TINYBLOB0-255 bytes不超过255个字符的二进制数据TINYTEXT0-255 bytes短文本字符串BLOB0-65 535 bytes二进制形式的长文本数据TEXT0-65 535 bytes长文本数据MEDIUMBLOB0-16 777 215 bytes二进制形式的中等长度文本数据MEDIUMTEXT0-16 777 215 bytes中等长度文本数据LONGBLOB0-4 294 967 295 bytes二进制形式的极大文本数据LONGTEXT0-4 294 967 295 bytes 极极大文本数据日期类型类型大小范围格式概述DATE31000-01-01 至 9999-12-31YYYY-MM-DD日期值TIME3-838:59:59 至 838:59:59HH:MM:SS时间值或持续时间YEAR11901 至 2155YYYY年份值DATETIME81000-01-01 00:00:00 至9999-12-31 23:59:59YYYY-MM-DDHH:MM:SS混合日期和时间值TIMESTAMP41970-01-01 00:00:01 至2038-01-19 03:14:07YYYY-MM-DDHH:MM:SS混合日期和时间值，时间戳DML添加数据给指定字段添加数据INSERT INTO 表名(字段1，字段2，......) VALUES (值1，值2，......);给全部字段添加数据INSERT INTO 表名 VALUES (值1,值2，......);批量添加数据INSERT INTO 表名(字段1，字段2，......) VALUES (值1，值2，......),(值1，值2，......),(值1，值2，......);INSERT INTO 表名 VALUES (值1,值2，......),(值1,值2，......),(值1,值2，......);修改数据UPDATE 表名 SET 字段1=值1,字段2=值2,...... [ WHERE 条件 ]删除数据DELETE FROM 表名 [ WHERE 条件 ]注意修改删除数据的时候，如果不加where判断条件则调整的整张表的内容。DQL语法SELECT​ 字段列表FROM​ 表名列表WHERE​ 条件列表GROUP BY​ 分组字段列表HAVING​ 分组后条件列表ORDER BY​ 排序字段列表LIMIT​ 分页参数基本查询查询多个字段SELECT 字段1，字段2，.... FROM 表名;SELECT * FRIN 表名;设置别名SELECT 字段1 [ AS '别名' ],字段2 [ AS '别名2' ] FROM 表名;输出的时候字段名称会 替换成别名，这段SQL中的AS可以不写，例如SELECT 字段 '别名' FROM 表名;去除重复记录SELECT distinct 字段列表 FROM 表名;条件查询语法SELECT 字段列表 FROM 表名 WHERE 条件列表;条件比较运算符运算符功能>大于\>=大于等于<小于<=小于等于=等于<> 或 !=不等于BETWEEN ... AND ...在某个范围之内(含最小,最大值)IN(...)在in之后的列表中的值，多选一LIKE 占位符模糊匹配(_匹配单个字符,%匹配任意多个字符)IS NULL是NULL逻辑运算符运算符功能AND 或 &&并且(多个条件同时成立)OR 或 \\ 或者(多个条件任意一个成立)NOT 或 !非，不是聚合函数语法SELECT 聚合函数(字段) FROM 表名;函数函数功能count统计数量max最大值min最小值avg平均值sum求和分组查询语法SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ]排序查询语法SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2，排序方式2排序方式ASC:升序(默认)DESC:降序分页查询语法SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;执行顺序DQL的执行顺序为FROM 表 > WHERE 条件查询 > GROUP BY 分组查询 > SELECT 字段查询 > ORDER BY 排序查询 > LIMIT 分页查询DCL用户管理查询用户USE mysql; SELECT * FROM user;创建用户CREATE USER `用户名`@`主机名` IDENTIFIED BY `密码`;修改用户密码ALTER USER `用户名`@`主机名` IDENTIFIED WITH mysql_native_password BY `新密码`;删除用户DROP USER `用户名`@`主机名`;权限管理权限常用权限如下表权限说明ALL,ALL PRIVILEGES所有权限SELECT数据查询INSERT插入数据UPDATE更新数据DELETE删除数据ALTER修改表DROP删除数据库、表、试图CREATE创建数据库、表这是常用的 其他的可以去官网查看管理查询权限SHOW GRANTS FOR `用户名`@`主机名`;授予权限GRANT 权限列表 ON 数据库名.表名 TO `用户名`@`主机名`;撤销权限REVOKE 权限列表 ON 数据库名.表名 FROM `用户名`@`主机名`;