数据库系列你想要的sql全都有plus

　　目录
　　详细sql数据准备一、ddl（datadefinelanguage）数据定义语言1、库管理1、库的创建（create）2、库的修改（alter）3、库的删除（drop）2、表管理1、表的创建（create）2、表的修改（alter）3、表的删除（drop）4、表的查询（desc）5、复制表数据6、重命名表7、清空表二、dml（datamanipulationlanguage）数据操作语言1、增（insert）2、删（delete）3、改（update）三、dql（dataquerylanguage）数据查询语言1、基础查询（select）2、条件查询（where）3、分页查询（limit）4、连接查询（join）5、子查询（in）6、排序查询（orderby）7、分组查询（groupby）8、时间查询9、其它查询四、dcl（datacontrollanguage）数据控制语言1、创建用户（create）2、用户授权（grant）3、撤销权限（revoke）4、查看权限（show）5、删除用户（drop）6、修改用户（update）五、索引操作1、索引创建（create）2、索引删除（drop）3、索引查询（show）六、存储过程1、简单查询2、批量插入数据七、相关函数1、时间函数2、随机数3、其它函数八、其它内容1、查看数据库大小2、设置变量3、系统变量4、explain5、批量删除表数据准备（前提：先执行下方创库创表sql）INSERTINTOdbname。ttableinfo（id，name，age，sex，job，jsonset，text，blob，createtime，updatetime）VALUES（1，张三，1，男，律师，｛uuid：123｝，文本1，NULL，2021102723：28：35，2022010912：37：07）；INSERTINTOdbname。ttableinfo（id，name，age，sex，job，jsonset，text，blob，createtime，updatetime）VALUES（2，李四，2，男，律师，｛uuid：123｝，文本2，NULL，2021101323：28：38，2022010912：37：09）；INSERTINTOdbname。ttableinfo（id，name，age，sex，job，jsonset，text，blob，createtime，updatetime）VALUES（3，王五，3，男，医生，｛uuid：123｝，文本3，NULL，2021102023：30：56，2022010912：37：11）；INSERTINTOdbname。ttableinfo（id，name，age，sex，job，jsonset，text，blob，createtime，updatetime）VALUES（4，赵六，4，女，医生，｛uuid：456｝，文本4，NULL，2021102723：31：28，2022010912：37：12）；INSERTINTOdbname。ttableinfo（id，name，age，sex，job，jsonset，text，blob，createtime，updatetime）VALUES（5，嘎嘎大王，5，女，教师，｛uuid：456｝，文本5，NULL，2021112121：58：17，2022010912：37：14）；INSERTINTOdbname。ttableinfo（id，name，age，sex，job，jsonset，text，blob，createtime，updatetime）VALUES（6，呱呱大王，6，女，教师，｛uuid：456｝，文本6，NULL，2021112121：59：32，2022010912：37：16）；一、ddl（datadefinelanguage）数据定义语言1、库管理1、库的创建（create）2、库的修改（alter）alterdatabasedbnamecharactersetutf8；3、库的删除（drop）2、表管理1、表的创建（create）createtablettableinfo（idint（0）notnull，namevarchar（255）charactersetutf8mb4collateutf8mb40900aicinulldefaultnullcomment姓名（varchar（m）存放长度不固定的字符），ageint（0）nulldefaultnullcomment年龄，sexvarchar（255）charactersetutf8mb4collateutf8mb40900aicinulldefaultnullcomment性别，jobvarchar（255）charactersetutf8mb4collateutf8mb40900aicinulldefaultnullcomment职业，jsonsetvarchar（100）charactersetutf8mb4collateutf8mb40900aicinulldefaultnullcommentjson集合，texttextcharactersetutf8mb4collateutf8mb40900aicinullcomment文本（text存放长文本），blobblobnullcomment不录歌（blob存放较大的二进制，如图片等），createtimedatetime（0）nulldefaultnullcomment创建时间，updatetimedatetime（0）nulldefaultnullonupdatecurrenttimestamp（0）comment更新时间，primarykey（id）usingbtree，uniqueindexindexid（id）usingbtree，indexindexname（name）usingbtree，indexindexagesex（age，sex）usingbtree）engineinnodbcharactersetutf8mb4collateutf8mb40900复制表（create）createtabledbname。ttableinfov2likedbname。2、表的修改（alter）添加列altertabledbname。ttableinfoaddnewcolvarchar（255）nullcomment新增列；修改列altertabledbname。ttableinfochangenewcolnewcolv2varchar（50）；删除列altertabledbname。ttableinfodropcolumnnewcolv2；3、表的删除（drop）droptabledbname。ttableinfov2；4、表的查询（desc）descdbname。showcolumnsfromdbname。5、复制表数据insertintodbname。ttableinfov2selectfromdbname。6、重命名表renametabledbname。ttableinfov2todbname。ttableinfov3；7、清空表truncatetabledbname。ttableinfov3；二、dml（datamanipulationlanguage）数据操作语言1、增（insert）insertintodbname。ttableinfo（id，name，age，sex，job，jsonset，text，blob，createtime，updatetime，newcolv2）values（23，呱呱大王，7，女，教师，｛uuid：456｝，null，null，now（），now（），null）；2、删（delete）deletefromdbname。ttableinfowhereid1；3、改（update）updatedbname。ttableinfosetname呱呱大王，age7，sex女，job教师，jsonset｛uuid：456｝，textnull，blobnull，createtimenow（），updatetimenow（），newcolv2nullwhereid1；三、dql（dataquerylanguage）数据查询语言1、基础查询（select）selectfromdbname。2、条件查询（where）selectfromdbname。ttableinfowhereid1；and和or查询selectfromdbname。ttableinfowherejobin（律师）and（idin（1）oragein（5））；between筛选某个范围内的值selectfromdbname。ttableinfowhereagebetween1and5；模糊查询selectfromdbname。ttableinfowherenamelike大王；多条件模糊查询selectfromdbname。ttableinfowherenamelike大王orjoblike律师；3、分页查询（limit）查询5条数据selectfromdbname。ttableinfolimit5；查询最新一条数据selectfromdbname。ttableinfoorderbyupdatetimedesclimit1；4、连接查询（join）内连接1：内连接查询操作列出与连接条件匹配的数据行selectfromttableinfoa，ttableinfov2bwherea。idb。内连接2：内连接查询操作列出与连接条件匹配的数据行selectfromttableinfoainnerjointtableinfov2bona。idb。外连接左连接：以左表为基准，右表的列为nullselectfromttableinfoaleftjointtableinfov2bona。idb。外连接右连接：以右表为基准，左表的列为nullselectfromttableinfoarightjointtableinfov2bona。idb。交叉连接：交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。selectfromttableinfoacrossjointtableinfov2asbona。idb。5、子查询（in）selectfromdbname。ttableinfowhereidin（1，5）；6、排序查询（orderby）selectfromdbname。ttableinfoorderbyupdatetimedesc，7、分组查询（groupby）selectjob，count（0）as重复次数fromdbname。selectjob，count（job）as重复次数fromdbname。ttableinfogroupbyjobhavingcount（job）1；8、时间查询selectfromdbname。查询今天的数据selectfromdbname。ttableinfowheretodays（createtime）todays（now（））；查询昨天的数据selectfromdbname。ttableinfowheredatediff（createtime，now（））1；查询最近7天的数据selectfromdbname。ttableinfowheredatesub（curdate（），interval7day）date（createtime）；查询本月的数据selectfromdbname。ttableinfowheredateformat（createtime，ym）dateformat（curdate（），ym）；查询上月数据selectfromdbname。ttableinfowhereperioddiff（dateformat（now（），ym），dateformat（createtime，ym））1；9、其它查询查询json：jsonunquote（）去除双引号；jsonextract（）提取json的keyselectjsonunquote（jsonextract（jsonset，。uuid））fromdbname。四、dcl（datacontrollanguage）数据控制语言查看用户表selectfrommysql。1、创建用户（create）2、用户号授权（grant）grantselect，insert，update，deleteondbname。3、撤销权限（revoke）revokedeleteondbname。4、查看权限（show）5、删除用户（drop）6、修改用户（update）updatemysql。usersetuserstudentv2五、索引操作1、索引创建（create）创建唯一索引createuniqueindexindexidondbname。ttableinfo（id）；创建普通索引，允许重复值，1个索引createindexindexnameondbname。ttableinfo（name）；创建普通索引，允许重复值，2个索引createindexindexagesexondbname。ttableinfo（age，sex）；2、索引删除（drop）dropindexindexnameondbname。3、索引查询（show）showindexfromdbname。六、存储过程简单查询createproceduregetinfo（）beginselectfromdbname。callgetinfo（）；批量插入数据createprocedurebatchdata（）seti7；while（i8）doinsertintodbname。ttableinfo（id，name，age，sex，job，jsonset，createtime，updatetime）values（i，concat（name，id），1，男，律师，｛uuid：123｝，now（），now（））；setii1；callbatchdata（）；七、相关函数1、时间函数获取当前时间selectnow（），curdate（），curtime（3），currentdate（），currenttime（3），currenttimestamp（2），currentdate，currenttime，currenttimestamp，unixtimestamp（now（））；获取当前日期selectdate（curdate（））；获取当前月份selectmonth（curdate（））；获取本月第一天selectdateadd（curdate（），intervalday（curdate（））1day）；获取本月最后一天selectlastday（curdate（））；获取本月天数selectday（lastday（curdate（）））；获取当前年份selectyear（curdate（））；dateformat（date，format）函数用于以不同的格式显示日期时间数据，date是日期列，format是格式selectdateformat（now（），ymd）；strtodate（str，format）将字符串转成日期selectstrtodate（now（），ymd）fromdbname。查询当前日期是哪个季度的selectextract（quarterfromstrtodate（now（），ymd））；时间区间selectdatediff（20221231，now（））；加减某个时间间隔函数dateadd（）与datesub（）dateadd（某个日期时间，interval1时间种类名）；quarter：季，week：周，day：天，hour：小时，minute：分钟，second：秒，microsecond：毫秒加1天selectdateadd（20220101，interval1day）；加1月selectdateadd（now（），interval1month）；日期相减selectdatediff（20221231，20220101）；时间相减selecttimediff（2022010100：00：00，2021010200：00：00）；selecttimediff（12：00：00，12：30：00）；时间相减转换为秒数selecttimetosec（timediff（12：00：00，12：02：00））；查询当年月份，前提：dbname。ttableinfo大于等于12条数据selectcasewhenlength（mon）1thenconcat（left（currentdate，5），0，mon）elseconcat（left（currentdate，5），mon）endmonthsfrom（selectm：m1monfromdbname。ttableinfo，（selectm：0）a）aalimit12；2、随机数生成小于1的随机数selectrand（）；生成100以内的随机数selectfloor（1（rand（）100））；生成3位随机数selectceiling（rand（）900100）；生成4位随机数selectceiling（rand（）9000100）；3、其它函数统计selectcount（）fromdbname。查询平均值selectavg（id）fromdbname。查询最大值selectmax（id）fromdbname。查询最小值selectmin（id）fromdbname。求和selectsum（id）fromdbname。concat：在字段值前加上前缀aaaupdatedbname。ttableinfosetnameconcat（aaa，name）wherenamein（张三，李四）；replace：去掉字段值的某一部分updatedbname。ttableinfosetnamereplace（name，aaa，）wherenamein（aaa张三，aaa李四）；case举例selectname，（casewhenname张三then张三呀whenname李四then李四呀whennameisnullthen空值else其它情况end）as备注fromdbname。八、其它内容1、查看数据库大小查询所有数据库的表大小selecttablename，concat（truncate（datalength10241024，2），mb）asdatasize，concat（truncate（indexlength10241024，2），mb）asindexsizefrominformationschema。查询某个数据库的表大小selecttablename，concat（truncate（datalength10241024，2），mb）asdatasize，concat（truncate（indexlength10241024，2），mb）asindexsizefrominformationschema。2、设置变量set设置变量setjob教师；setage（selectagefromdbname。ttableinfowhereidin（6））；selectfromdbname。ttableinfowherejobin（job）andagein（age）；select设置变量selectjob：教师；selectfromdbname。ttableinfowherejobin（job）；查询结果并赋值selectage，jobintoage，jobfromdbname。ttableinfowhereidin（6）；selectfromdbname。ttableinfowhereagein（age）andjobin（job）；3、系统变量查看所有的会话变量查看所有的全局变量查看数据库隔离级别selectglobal。查看mysql版本查看端口、目录、数据存放目录、服务器idselectport，basedir，datadir，4、explain分析查询的sql语句explainselectfromdbname。5、批量删除表（将结果复制出来并执行）selectconcat（droptableifexists，tablename，；）frominformationschema。
　　关注【嘎嘎软件测试】
　　搞测试，不迷路
　　呱呱大王本呱带你飞！
　　嘎嘎软件测试将分享个人成长、团队管理、软件测试技能知识等内容，更新频率一周两篇，做到有思想、有观点、有深度，欢迎订阅。