Mysql技術(shù)紀要

1、根據(jù)表注釋查找表名

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

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = '數(shù)據(jù)庫名' AND TABLE_COMMENT LIKE '%收藏%';

2、根據(jù)字段注釋查找表名

SELECT COLUMN_NAME,column_comment,Table_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE table_schema='數(shù)據(jù)庫名' AND column_comment LIKE '%代課%';

3苔悦、查看mysql版本號

select version();

PS：select 函數(shù)();

4、常用函數(shù)

  • 字符串函數(shù)：CONCAT(s1,s2...sn)勤讽、LOCATE(s1,s)脚牍、REPLACE(s,s1,s2)诸狭、SPACE(n)
  • 數(shù)字函數(shù)：LEAST(expr1, expr2, expr3, ...)券膀、RAND()君纫、POW(x,y)=POWER(x,y)
  • 日期函數(shù)：CURDATE()、SYSDATE()三娩、CURRENT_TIMESTAMP()
  • 高級函數(shù)：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事件測試

-- 創(chuàng)建測試表 id為主鍵

CREATE TABLE whp_test(

id INT AUTO_INCREMENT,

message VARCHAR(100),

PRIMARY KEY (id)

)

-- 表名區(qū)分大小寫

SELECT * from whp_test；

SELECT * from Whp_test

-- lower_case_table_names參數(shù)詳解：

-- 其中 0：區(qū)分大小寫蔚万，1：不區(qū)分大小寫

-- MySQL在Linux下數(shù)據(jù)庫名、表名淮蜈、列名侧蘸、別名大小寫規(guī)則是這樣的：

-- 1、數(shù)據(jù)庫名與表名是嚴格區(qū)分大小寫的逢艘；

-- 2娩怎、表的別名是嚴格區(qū)分大小寫的爬泥；

-- 3却桶、列名與列的別名在所有的情況下均是忽略大小寫的；

-- 4信粮、變量名也是嚴格區(qū)分大小寫的旅掂；

-- 創(chuàng)建一個事件

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

-- 查看是否開啟事件調(diào)度器

SHOW variables like '%event_scheduler%';

-- 設(shè)置開啟事件調(diào)度器

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 '結(jié)果送達備注' after result_is_delivered

8沦偎、查看用戶登陸過期時間

show global variables like 'wait_timeout'搔驼？扔字？

9舵鳞、使用MySQL執(zhí)行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 當(dāng)然可以用 select form table1 where not in （select from table2）來代替，另一種用left join的方法的思想則是運用在B不在A中的項用Left Join 會填入NULL這一性質(zhì)。

11糠聪、查看一張表的信息（存儲引擎、大小题造、自增值...）

SHOW TABLE STATUS LIKE 't_subject_detail2item_detail';

12淮悼、比較運算符

安全等于 <=> 【結(jié)果不是0就是1】

eg.

select null = null; -- 不安全等于 結(jié)果：null

select null<=>null; -- 安全等于 結(jié)果：1

select null = 1; -- 不安全等于 結(jié)果：null

select null<=>1; -- 安全等于 結(jié)果：0

在倆者之間 between ... and ...

eg.

select 5 between 5 and 10; -- 相當(dāng)于 >= min && <= max（5>=5 && 5<=10） 結(jié)果：1

select 5 not between 5 and 10; -- 相當(dāng)于 < min || > max （5<5 || 5>10） 結(jié)果：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、樹形分級排序問題（關(guān)聯(lián)自身，先排后面）

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個字節(jié)，不包括text兆蕉、blob等大字段類型，varchar長度受此長度限制，和其他非大字段加起來不能超過65535個字節(jié)

nvarchar(national character varying):包含 n 個字符的可變長度 Unicode 字符數(shù)據(jù)冒嫡。在存儲時瓣赂，無論是全角還是半角，每個字符都占用兩個字節(jié)色徘。在定義時，無論全角或是半角溪猿，都是定義字符個數(shù)而不是字節(jié)數(shù)。最多顯示4000個字符（無論全角或半角）

Mysql 4.0版本以下凉逛，varchar(50)菩佑，指的是50字節(jié)枪向，如果存放UTF8漢字時，只能存16個（每個中文3字節(jié)）

Mysql 5.0版本以上叠赐，varchar(50)，指的是50字符鸽粉，無論存放的是數(shù)字偏友、字母還是UTF8中文（每個中文3字節(jié)）京景，都可以存放50個

MySQL限制每個表最多存儲4096列鼻吮，并且每一行數(shù)據(jù)的大小不能超過65535字節(jié)

18、IP的處理

select inet_aton('2.222.0.2');

select inet_ntoa(48103426);

19万矾、WHERE從句中禁止對列進行函數(shù)轉(zhuǎn)換和計算（PS：這樣可能是沒辦法的事）

原因：對列進行函數(shù)轉(zhuǎn)換或計算時會導(dǎo)致無法使用索引

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

20窥突、mysql索引使用B+樹來存儲

哈希不能范圍查詢，B+樹比B冗余存了數(shù)據(jù)尸执，但是效率高

21脆丁、mysql模糊查詢不區(qū)分大小寫問題（默認不區(qū)分大小寫）

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

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

2設(shè)置字段加上binary歼培。對于CHAR、VARCHAR和TEXT類型丐枉，BINARY屬性可以為列分配該列字符集的校對規(guī)則瘦锹。

22听绳、mysql事務(wù)問題

-- 查看自動提交是否打開

show variables like 'autocommit';

-- 打開session級的自動提交

set session autocommit = on;

23鼠证、獲取插入一條記錄的id（在一個事務(wù)中）

select last_insert_id();

24、修改自增主鍵的值

alter table 表名 = 27;

25猴贰、日期函數(shù)

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

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

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

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

select year(curdate());-- 當(dāng)前年

select mouth(curdate());-- 當(dāng)前月

select day(curdate());-- 當(dāng)前日

select date(curdate());-- 當(dāng)前日期

select dayofweek(curdate());-- 當(dāng)前周數(shù)

........

%S, %s 兩位數(shù)字形式的秒（ 00,01, ..., 59）

%I, %i 兩位數(shù)字形式的分（ 00,01, ..., 59）

%H 兩位數(shù)字形式的小時米绕，24 小時（00,01, ..., 23）

%h 兩位數(shù)字形式的小時瑟捣，12 小時（01,02, ..., 12）

%k 數(shù)字形式的小時馋艺，24 小時（0,1, ..., 23）

%l 數(shù)字形式的小時，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 兩位數(shù)字表示月中的天數(shù)（00, 01,..., 31）

%e 數(shù)字形式表示月中的天數(shù)（1, 2迈套， ..., 31）

%D 英文后綴表示月中的天數(shù)（1st, 2nd, 3rd,...）

%w 以數(shù)字形式表示周中的天數(shù)（ 0 = Sunday, 1=Monday, ..., 6=Saturday）

%j 以三位數(shù)字表示年中的天數(shù)（ 001, 002, ..., 366）

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

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

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

%b 縮寫的月名（ January, February,...., December）

%m 兩位數(shù)字表示的月份（01, 02, ..., 12）

%c 數(shù)字表示的月份（1, 2, ...., 12）

%Y 四位數(shù)字表示的年份

%y 兩位數(shù)字表示的年份

%% 直接值“%”

26桑李、行列轉(zhuǎn)置測試


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,'數(shù)學(xué)',72);

insert into zzztest(sno,subject,score) values(3,'數(shù)學(xué)',88);

insert into zzztest(sno,subject,score) values(13,'數(shù)學(xué)',88);

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

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

轉(zhuǎn)置后：

select * from zzztest;

select

sno,

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

max(case subject when '數(shù)學(xué)' then score end) as math,

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

from zzztest

group by sno;

-- 這里相信大家都知道了為什么要加聚合函數(shù)max（）踱蛀，min()等等，是因為分組函數(shù)導(dǎo)致的贵白，跟case when沒有很大關(guān)系星岗，分組函數(shù)一定和聚合函數(shù)一同存在，要不然你想戒洼，比如上述數(shù)據(jù)俏橘，按照名字分組后，每個組內(nèi)都有三個數(shù)據(jù)圈浇，而展示的時候就只展示一條寥掐，所以必須從中選擇一條展示所以才出現(xiàn)了上述數(shù)據(jù)不完全正確狀況，所以以后大家在使用分組函數(shù)時一定要使用聚合函數(shù)

drop table zzztest;

27磷蜀、explain分析SQL執(zhí)行計劃

image.jpeg
image.jpeg

28召耘、使用儲存過程批量插入大量數(shù)據(jù)

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褐隆、查看執(zhí)行階段時間

set profiling = 1;

select * from XX;

show profiles;

31污它、sql優(yōu)化技巧

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

count(指定的有效值)--執(zhí)行計劃都會轉(zhuǎn)化為count(*)

如果指定的是列名，會判斷是否有null庶弃，null不計算

order by ：

using index

using filesort

32衫贬、mysql架構(gòu)

  • MySQL 8.0版本直接將查詢緩存的整塊功能刪掉了，也就是說8.0開始徹底沒有這個功能了

33歇攻、在 MySQL 最常見的存儲引擎 InnoDB 中固惯，事務(wù)日志其實有兩種，一種是回滾日志（undo log ）缴守，另一種是重做日志（redo log）葬毫，其中前者保證事務(wù)的原子性，后者保證事務(wù)的持久性屡穗，兩者可以統(tǒng)稱為事務(wù)日志贴捡。

33、根據(jù)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中對于字符串轉(zhuǎn)化為數(shù)值

SELECT CAST('123.12,83' AS DECIMAL);
SELECT CAST('123.123' AS DECIMAL(3));
// 結(jié)果都為123，逗號和點都被截取了
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末，一起剝皮案震驚了整個濱河市源祈，隨后出現(xiàn)的幾起案子煎源，更是在濱河造成了極大的恐慌，老刑警劉巖香缺，帶你破解...
    沈念sama閱讀 219,110評論 6贊 508
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件手销，死亡現(xiàn)場離奇詭異，居然都是意外死亡图张，警方通過查閱死者的電腦和手機锋拖，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 93,443評論 3贊 395
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來祸轮，“玉大人兽埃，你說我怎么就攤上這事∈释啵” “怎么了柄错？”我有些...
    開封第一講書人閱讀 165,474評論 0贊 356
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵芜抒，是天一觀的道長瓤逼。 經(jīng)常有香客問我，道長丙号，這世上最難降的妖魔是什么疫萤？ 我笑而不...
    開封第一講書人閱讀 58,881評論 1贊 295
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任颂跨，我火速辦了婚禮，結(jié)果婚禮上扯饶，老公的妹妹穿的比我還像新娘恒削。我一直安慰自己，他們只是感情好尾序，可當(dāng)我...
    茶點故事閱讀 67,902評論 6贊 392
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布钓丰。 她就那樣靜靜地躺著，像睡著了一般蹲诀。 火紅的嫁衣襯著肌膚如雪斑粱。 梳的紋絲不亂的頭發(fā)上弃揽，一...
    開封第一講書人閱讀 51,698評論 1贊 305
  • 城市分裂傳說
    那天脯爪，我揣著相機與錄音，去河邊找鬼矿微。 笑死痕慢，一個胖子當(dāng)著我的面吹牛，可吹牛的內(nèi)容都是我干的涌矢。 我是一名探鬼主播掖举，決...
    沈念sama閱讀 40,418評論 3贊 419
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼，長吁一口氣：“原來是場噩夢啊……” “哼娜庇！你這毒婦竟也來了塔次？” 一聲冷哼從身側(cè)響起方篮，我...
    開封第一講書人閱讀 39,332評論 0贊 276
  • 萬榮殺人案實錄
    序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎励负，沒想到半個月后藕溅，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
    沈念sama閱讀 45,796評論 1贊 316
  • ?護林員之死
    正文 獨居荒郊野嶺守林人離奇死亡继榆，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 37,968評論 3贊 337
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年巾表，在試婚紗的時候發(fā)現(xiàn)自己被綠了。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片略吨。...
    茶點故事閱讀 40,110評論 1贊 351
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡集币，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出翠忠，到底是詐尸還是另有隱情鞠苟，我是刑警寧澤，帶...
    沈念sama閱讀 35,792評論 5贊 346
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布秽之，位于F島的核電站偶妖，受9級特大地震影響，放射性物質(zhì)發(fā)生泄漏政溃。R本人自食惡果不足惜趾访，卻給世界環(huán)境...
    茶點故事閱讀 41,455評論 3贊 331
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望董虱。 院中可真熱鬧扼鞋，春花似錦、人聲如沸愤诱。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,003評論 0贊 22
  • 一樁弒父案，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽淫半。三九已至溃槐，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間科吭，已是汗流浹背昏滴。 一陣腳步聲響...
    開封第一講書人閱讀 33,130評論 1贊 272
  • 情欲美人皮
    我被黑心中介騙來泰國打工， 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留对人，地道東北人谣殊。 一個月前我還...
    沈念sama閱讀 48,348評論 3贊 373
  • 代替公主和親
    正文 我出身青樓，卻偏偏與公主長得像牺弄，于是被迫代替她去往敵國和親姻几。 傳聞我的和親對象是個殘疾皇子，可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 45,047評論 2贊 355