MySQL-笔记说明一、基础1、建库2、建表3、编辑表4、创建索引5、清空表数据6、删除表7、增删改查8、查询顺序二、实用技巧1、数据类型转换2、清除30天前数据3、保留小数点后2位4、字符拼接5、修改时间6、查询时间如果Null用当前时间7、变量三、其他1、死锁2、查看哪个表有Name字段3、查看表大小4、分析查询语句说明你好 这是Mysql学习日记本。一、基础1、建库CREATEDATABASEIFNOTEXISTSdemoDEFAULTCHARACTERSETutf8mb4-- 通用字符集支持emoji、全字符DEFAULTCOLLATEutf8mb4_unicode_ci;-- 通用排序规则2、建表// demo:库名log_history表名// AUTO_INCREMENTId自增CREATETABLEdemo.log_history(IdBIGINT(20)NOTNULLAUTO_INCREMENTCOMMENTId,CodeVARCHAR(45)NOTNULLCOMMENT编码,NameVARCHAR(45)NULLCOMMENT名称,SeqINT(11)NULLCOMMENT序号,CreateTimeDATETIMENULLDEFAULTNULLCOMMENT创建时间,PRIMARYKEY(Id),UNIQUEINDEXCode_UNIQUE(CodeASC))ENGINEInnoDBDEFAULTCHARACTERSETutf8COLLATEutf8_binCOMMENT日志;3、编辑表添加列ALTERTABLEdemo.log_historyADDCOLUMNNameVARCHAR(45)NULLCOMMENT名称AFTERCode;删除列ALTERTABLEdemo.log_historyDROPCOLUMNName;4、创建索引ALTERTABLEdemo.log_historyADDINDEXNameUSINGBTREE(Name);ALTERTABLEdemo.log_historyADDINDEXName(NameASC);5、清空表数据TRUNCATEdemo.log_history;6、删除表DROPtabledemo.log_history;加条件如果存在表DROPtableifexistsdemo.log_history;7、增删改查增// 因为Id自增所以Id不用插入数据INSERTINTOdemo.log_history(Code,Name,Seq,CreationTime)VALUES(A001,A001,1,2025-11-01 14:48:22);删DELETEFROMdemo.log_historyWHERE(id1);改UPDATEdemo.log_historySETCodeA002,NameA002WHERE(id1);UPDATEt2JOINt1ONt2.RelIdt1.IdSETt2.RelCodet1.CodeWHEREt2.RelCodeISNULL查SELECTt.*,a.Seq,a.CodeasHistCodeFROMsql_equipment tleftjoinlog_history aont.Codea.Codewheret.Namelike%A%orderbyt.Codedesclimit1000;8、查询顺序selectfromleft join … on …wheregroup byhavingorder bylimit二、实用技巧1、数据类型转换convert(t.Seq,CHAR)asSeq,cast(t.Seqaschar)asSeq,2、清除30天前数据deletefromlog_historywheredate(CreateTime)date_sub(now(),interval30day)limit1000000;3、保留小数点后2位round(t.Price,2)asPrice,4、字符拼接CONCAT(CONCAT(Code,-),Name)asCodeName,5、修改时间1.将日期修改为1号updatelog_historysetCreationTimestr_to_date(concat(date_format(CreationTime,%Y-%m-),01,date_format(CreationTime,%H:%i:%s)),%Y-%m-%d %H:%i:%s)whereCreationTimebetween2025-11-30and2025-12-01limit1000;2.将日期加减30天updatelog_historysetCreationTimedate_add(CreationTime,interval-30Day)whereidin(2,3)limit1000;6、查询时间如果Null用当前时间1.IFNULL()selectifnull(t.CreationTime,now())asCreationTimefromlog_history t;2.COALESCE()selectcoalesce(t.CreationTime,now())asCreationTimefromlog_history t;CASE WHEN THEN ELSE ENDselectcasewhent.CreationTimeisnullthennow()elset.CreationTimeendasCreationTimefromlog_history t;7、变量SETrow_number1;UPDATElog_historySETseq(row_number:row_number1)WHEREidBETWEEN1AND100ORDERBYid;三、其他1、死锁查看死锁表SHOWOPENTABLESWHEREDatabaselog_history;查看死锁进程SHOWprocesslist;终结死锁进程(其中186111为processlist查出的Id)kill186111;2、查看哪个表有Name字段SELECTTABLE_SCHEMA,TABLE_NAMEFROMINFORMATION_SCHEMA.COLUMNSWHERECOLUMN_NAMENameANDTABLE_SCHEMAdemo;SELECTCONCAT(ALTER TABLE ,TABLE_SCHEMA,.,TABLE_NAME, MODIFY COLUMN Name VARCHAR(300);)ASalter_sqlFROMINFORMATION_SCHEMA.COLUMNSWHERECOLUMN_NAMENameANDDATA_TYPEvarcharANDCHARACTER_MAXIMUM_LENGTH300ANDTABLE_SCHEMAdemoORDERBYTABLE_NAME;3、查看表大小查看库总大小SELECTtable_schemaASDatabase,SUM(data_lengthindex_length)/1024/1024ASSize (MB)FROMinformation_schema.TABLESWHEREtable_schemademoGROUPBYtable_schema;查看每个表大小SELECTtable_schemaASDatabase,table_nameASTable,ROUND((data_lengthindex_length)/1024/1024,2)ASSize in MBFROMinformation_schema.TABLESWHEREtable_schemademoORDERBY(data_lengthindex_length)DESC;4、分析查询语句EXPLAINEXPLAINSELECTt.*,a.Seq,a.CodeasHistCodeFROMsql_equipment tLEFTJOINlog_history aont.Codea.CodeWHEREt.Namelike%A%