Mysql技术纪要

2020-09-17 本文已影响0人舞鹤Roc

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

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，逗号和点都被截取了

Mysql技术纪要

猜你喜欢

热点阅读