Mysql技术纪要

1、根据表注释查找表名

SELECT table_name '表名',TABLE_COMMENT '表注释'

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = '数据库名' AND TABLE_COMMENT LIKE '%收藏%';

2、根据字段注释查找表名

SELECT COLUMN_NAME,column_comment,Table_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE table_schema='数据库名' AND column_comment LIKE '%代课%';

3、查看mysql版本号

select version();

PS：select 函数();

4、常用函数

  • 字符串函数：CONCAT(s1,s2...sn)、LOCATE(s1,s)、REPLACE(s,s1,s2)、SPACE(n)
  • 数字函数：LEAST(expr1, expr2, expr3, ...)、RAND()、POW(x,y)=POWER(x,y)
  • 日期函数：CURDATE()、SYSDATE()、CURRENT_TIMESTAMP()
  • 高级函数：VERSION()、CURRENT_USER()、COALESCE(expr1, expr2, ...., expr_n)、IF(expr,v1,v2)、IFNULL(v1,v2)、CASE WHEN

5、使用正则表达式做查询（regexp或者rlike）

select * from t_user where user_name regexp '^176';

6、mysql事件测试

-- 创建测试表 id为主键

CREATE TABLE whp_test(

id INT AUTO_INCREMENT,

message VARCHAR(100),

PRIMARY KEY (id)

)

-- 表名区分大小写

SELECT * from whp_test；

SELECT * from Whp_test

-- lower_case_table_names参数详解：

-- 其中 0：区分大小写，1：不区分大小写

-- MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：

-- 1、数据库名与表名是严格区分大小写的；

-- 2、表的别名是严格区分大小写的；

-- 3、列名与列的别名在所有的情况下均是忽略大小写的；

-- 4、变量名也是严格区分大小写的；

-- 创建一个事件

CREATE EVENT E_WHP_PLAY ON

SCHEDULE EVERY 1 SECOND STARTS NOW()

ON COMPLETION PRESERVE ENABLE

DO INSERT INTO whp_test(MESSAGE) VALUES (now())

-- 删除一个事件

DROP EVENT E_whp_play

-- 查看所有事件

show events

-- 查看是否开启事件调度器

SHOW variables like '%event_scheduler%';

-- 设置开启事件调度器

SET GLOBAL event_scheduler = ON;

7、修改表中某字段的位置

ALTER TABLE wisdomgov.t_item_implement_detail

MODIFY COLUMN result_deliver_comment varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '结果送达备注' after result_is_delivered

8、查看用户登陆过期时间

show global variables like 'wait_timeout'？？

9、使用MySQL执行update的时候报错：Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, t..

SET SQL_SAFE_UPDATES = 0;即可

10、MySQL不支持 intersect 和 except（minus） ，一个是交运算一个是差运算。感觉这两个还挺好用的说，幸运的是我们完全可以用其他方法替这两个。intersect 可以用一个 A inner join B using attr 来代替except 当然可以用 select form table1 where not in （select from table2）来代替，另一种用left join的方法的思想则是运用在B不在A中的项用Left Join 会填入NULL这一性质。

11、查看一张表的信息（存储引擎、大小、自增值...）

SHOW TABLE STATUS LIKE 't_subject_detail2item_detail';

12、比较运算符

安全等于 <=> 【结果不是0就是1】

eg.

select null = null; -- 不安全等于 结果：null

select null<=>null; -- 安全等于 结果：1

select null = 1; -- 不安全等于 结果：null

select null<=>1; -- 安全等于 结果：0

在俩者之间 between ... and ...

eg.

select 5 between 5 and 10; -- 相当于 >= min && <= max（5>=5 && 5<=10） 结果：1

select 5 not between 5 and 10; -- 相当于 < min || > max （5<5 || 5>10） 结果：0

13、排序的时候默认null在最前，把null换到后面的方法：加个 order_number is null

eg：order by order_number is null,order_number asc

14、用分隔符连接字符串CONCAT_WS(separator,str1,str2,...)方法

eg.select CONCAT_WS(-,'1','2','3') -> 1-2-3

15、树形分级排序问题（关联自身，先排后面）

select a.*,b.*
from t_map_server_subject a
left join t_map_server_subject b on a.parent_id=b.id
order by b.parent_id,b.sort_by, b.create_time,a.parent_id,a.sort_by, a.create_time;

16、添加唯一约束

alter table wisdomgov.t_item_implement_detail add constraint uk_publish_code unique (publish_code);

17、MySQL要求一个行定义长度不能超过65535个字节，不包括text、blob等大字段类型，varchar长度受此长度限制，和其他非大字段加起来不能超过65535个字节

nvarchar(national character varying):包含 n 个字符的可变长度 Unicode 字符数据。在存储时，无论是全角还是半角，每个字符都占用两个字节。在定义时，无论全角或是半角，都是定义字符个数而不是字节数。最多显示4000个字符（无论全角或半角）

Mysql 4.0版本以下，varchar(50)，指的是50字节，如果存放UTF8汉字时，只能存16个（每个中文3字节）

Mysql 5.0版本以上，varchar(50)，指的是50字符，无论存放的是数字、字母还是UTF8中文（每个中文3字节），都可以存放50个

MySQL限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节

18、IP的处理

select inet_aton('2.222.0.2');

select inet_ntoa(48103426);

19、WHERE从句中禁止对列进行函数转换和计算（PS：这样可能是没办法的事）

原因：对列进行函数转换或计算时会导致无法使用索引

<if test="operationTime != null and operationTime != ''">AND date(a.operation_time) = #{operationTime}</if>

20、mysql索引使用B+树来存储

哈希不能范围查询，B+树比B冗余存了数据，但是效率高

21、mysql模糊查询不区分大小写问题（默认不区分大小写）

1匹配字段加上binary或者使用binary(匹配字段)

select * from t_item_filling_record where binary(item_version) like '%v%';（ binary item_version like '%v%'）

2设置字段加上binary。对于CHAR、VARCHAR和TEXT类型，BINARY属性可以为列分配该列字符集的校对规则。

22、mysql事务问题

-- 查看自动提交是否打开

show variables like 'autocommit';

-- 打开session级的自动提交

set session autocommit = on;

23、获取插入一条记录的id（在一个事务中）

select last_insert_id();

24、修改自增主键的值

alter table 表名 = 27;

25、日期函数

select date_format(now(),'%Y')

select date_format(now(),'%m')

select date_format(now(),'%e')

select date_format(now(),'%U')

select year(curdate());-- 当前年

select mouth(curdate());-- 当前月

select day(curdate());-- 当前日

select date(curdate());-- 当前日期

select dayofweek(curdate());-- 当前周数

........

%S, %s 两位数字形式的秒（ 00,01, ..., 59）

%I, %i 两位数字形式的分（ 00,01, ..., 59）

%H 两位数字形式的小时，24 小时（00,01, ..., 23）

%h 两位数字形式的小时，12 小时（01,02, ..., 12）

%k 数字形式的小时，24 小时（0,1, ..., 23）

%l 数字形式的小时，12 小时（1, 2, ..., 12）

%T 24 小时的时间形式（hh:mm:ss）

%r 12 小时的时间形式（hh:mm:ss AM 或hh:mm:ss PM）

%p AM或PM

%W 一周中每一天的名称（Sunday, Monday, ..., Saturday）

%a 一周中每一天名称的缩写（Sun, Mon, ..., Sat）

%d 两位数字表示月中的天数（00, 01,..., 31）

%e 数字形式表示月中的天数（1, 2， ..., 31）

%D 英文后缀表示月中的天数（1st, 2nd, 3rd,...）

%w 以数字形式表示周中的天数（ 0 = Sunday, 1=Monday, ..., 6=Saturday）

%j 以三位数字表示年中的天数（ 001, 002, ..., 366）

%U 周（0, 1, 52），其中Sunday 为周中的第一天

%u 周（0, 1, 52），其中Monday 为周中的第一天

%M 月名（January, February, ..., December）

%b 缩写的月名（ January, February,...., December）

%m 两位数字表示的月份（01, 02, ..., 12）

%c 数字表示的月份（1, 2, ...., 12）

%Y 四位数字表示的年份

%y 两位数字表示的年份

%% 直接值“%”

26、行列转置测试


create table zzztest(

`id` int auto_increment,

`sno` int,

`subject` varchar(10),

`score` int,

primary key(`id`)

)

insert into zzztest(sno,subject,score) values(2,'语文',98);

insert into zzztest(sno,subject,score) values(3,'语文',18);

insert into zzztest(sno,subject,score) values(2,'数学',72);

insert into zzztest(sno,subject,score) values(3,'数学',88);

insert into zzztest(sno,subject,score) values(13,'数学',88);

insert into zzztest(sno,subject,score) values(13,'语文',68);

insert into zzztest(sno,subject,score) values(13,'英语',100);

转置后：

select * from zzztest;

select

sno,

max(case when subject = '语文' then score end) as chinese,

max(case subject when '数学' then score end) as math,

max(case subject when '英语' then score end) as english

from zzztest

group by sno;

-- 这里相信大家都知道了为什么要加聚合函数max（），min()等等，是因为分组函数导致的，跟case when没有很大关系，分组函数一定和聚合函数一同存在，要不然你想，比如上述数据，按照名字分组后，每个组内都有三个数据，而展示的时候就只展示一条，所以必须从中选择一条展示所以才出现了上述数据不完全正确状况，所以以后大家在使用分组函数时一定要使用聚合函数

drop table zzztest;

27、explain分析SQL执行计划

image.jpeg
image.jpeg

28、使用储存过程批量插入大量数据

CREATE TABLE test.my_table (

id INT NOT NULL AUTO_INCREMENT,

name varchar(100) NULL,

status TINYINT NULL,

create_time DATETIME NULL,

primary key(id)

)

ENGINE=InnoDB

DEFAULT CHARSET=utf8mb4

COLLATE=utf8mb4_0900_ai_ci;

call P_init_data();

CREATE DEFINER=`root`@`%` PROCEDURE `test`.`P_init_data`()

BEGIN

DECLARE I INT(11);

DECLARE CNT INT(11);

SET I = 1;

SET CNT = 96;

WHILE I < CNT

DO

INSERT INTO test.my_table(NAME,status,CREATE_TIME)VALUES(CONCAT('name-',I),cast(rand()* 10 % 3 as UNSIGNED INTEGER),,CURRENT_TIMESTAMP);

SET I = I + 1;

END WHILE;

END

29、客户端时间问题

show variables like "%time_zone%";

set global time_zone = '+8:00';

set time_zone = '+8:00';

flush privileges;

30、查看执行阶段时间

set profiling = 1;

select * from XX;

show profiles;

31、sql优化技巧

count(0)=count(1)=count(*)

count(指定的有效值)--执行计划都会转化为count(*)

如果指定的是列名，会判断是否有null，null不计算

order by ：

using index

using filesort

32、mysql架构

  • MySQL 8.0版本直接将查询缓存的整块功能删掉了，也就是说8.0开始彻底没有这个功能了

33、在 MySQL 最常见的存储引擎 InnoDB 中，事务日志其实有两种，一种是回滚日志（undo log ），另一种是重做日志（redo log），其中前者保证事务的原子性，后者保证事务的持久性，两者可以统称为事务日志。

33、根据A、B表查出的值进行A表的批量更新

update
    my_table myc
inner join(
    select
        b.status, b.table_id
    from
        my_table a
    left join my_table_copy b on
        a.id = b.table_id
    where
        a.status=1) my on
    my.table_id = myc.id 
set
    myc.status = my.status

34、sql中对于字符串转化为数值

SELECT CAST('123.12,83' AS DECIMAL);
SELECT CAST('123.123' AS DECIMAL(3));
// 结果都为123，逗号和点都被截取了
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 215,133评论 6赞 497
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 91,682评论 3赞 390
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 160,784评论 0赞 350
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 57,508评论 1赞 288
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 66,603评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 50,607评论 1赞 293
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,604评论 3赞 415
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,359评论 0赞 270
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,805评论 1赞 307
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,121评论 2赞 330
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,280评论 1赞 344
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,959评论 5赞 339
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,588评论 3赞 322
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,206评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,442评论 1赞 268
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,193评论 2赞 367
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,144评论 2赞 352