概述SQL的数据删除有两种：删除指定数据：单表，多表清空数据表格式单表删除deletefrom表where筛选条件多表删除（不常用）delete别名1,别名2from表1别名1join表2别名2on连接条件where筛选条件清空表truncattable表名;使用删除数据单表deletefromgirls.boyswhereid=6;多表deleteb,b2fromgirls.boysbjoingirls.beautyb2onb.id=b2.boyfriend_idwhereb.id=1;清空数据表使用很简单truncatetablespark.`user`;deletefrom表名;不加筛选条件，也能实现清空表
概述联合查询的本质就是，多个SQL查询结果的虚拟表上下合并。#第一个结果==================name|salary|--------|------|Steven|24000|Neena|17000|#第二个结果==================id|salary|--|------|1|24000|2|17000|#联合之后一个字段对应一个字段的这样合并==================name|salary|--------|------|Steven|24000|Neena|17000|1|24000|2|17000|格式select语句1unionselect语句2unionselect语句3...例子select的结果字段的个数需要一样union会对结果自动去重selectgby.id,gby.namefromgirls.beautygbyunionselectgbs.id,gbs.boyNamefromgirls.boysgbs=========================id|name|--|----------|1|柳岩|2|苍老师|3|Angelababy|4|热巴|11|夏雪|12|赵敏|1|张无忌|2|鹿晗|3|黄晓明|4|段誉|
概述分页一般使用查询，在网站使用过程中，一些数据需量太大，需要翻页显示，这是就需要SQL的分页查询。格式limitindex,sizeindex:起始索引（从0开始，从0开始获取可以省略）size：条目数select字段from表1[join表2on连接表达式][where表达式][groupby字段][having表达式][limit[index,]size];使用#获取工资第一页10条工资selectfirst_name,salaryfrommyemployees.employeeswheresalaryisnotnulllimit0,10;=========================first_name|salary|----------|------|Steven|24000|Neena|17000|Lex|17000|Alexander|9000|Bruce|6000|David|4800|Valli|4800|Diana|4200|Nancy|12000|Daniel|9000|技巧页数为page，每页条目数是size。可以有一个公式：limit(page-1)*size,size;
概述连接查询又称多表查询，查询的字段来自于多个表，推荐使用SQL99语法内连接（SQL92语法不推荐使用）就是1992年推出的语法笛卡尔乘积在连接查询不加以限制时，就会产生笛卡尔乘积现象。笛卡尔乘积：A表字段3个值，B表字段4个值，最终结果3X4=12个值。selectb.name,b2.boyNamefrombeautyb,boysb2#----------------------#name|boyName|----------|-------|柳岩|张无忌|柳岩|鹿晗|柳岩|黄晓明|柳岩|段誉|Angelababy|张无忌Angelababy|鹿晗|Angelababy|黄晓明Angelababy|段誉|热巴|张无忌|热巴|鹿晗|热巴|黄晓明|热巴|段誉|等值连接对字段进行一定的限制selectb.name,b2.boyNamefrombeautyb,boysb2whereb.boyfriend_id=b2.id#--------------------------#name|boyName|----------|-------|热巴|鹿晗|Angelababy|黄晓明|非等值连接#统计不同工资的等级selecte.salaryas工资,jg.grade_levelas等级frommyemployees.employeese,myemployees.job_gradesjgwheree.salarybetweenjg.lowest_salandjg.highest_salorderby等级,工资#------------------------------------------------------#工资|等级|-----|--|2900|A|2900|A|3000|B|3000|B|3100|B|4800|B|4800|B|5800|B|6000|C|6000|C|自连接两个需要作连接条件的字段在一张表上。就是把同一张表重名为不同的名字，就可以当做两张表查询了selecte.employee_id,e.first_name,m.first_name,m.manager_idfrommyemployees.employeese,myemployees.employeesmwheree.employee_id=m.manager_id连接查询(SQL99)语法格式select字段from表1[连接格式]join表2on连接条件[连接格式]join表3on连接条件[where限定条件][groupby分组][having查询后限定条件][orderby排序]内连接内连接：查询的是两表的交集等值连接on后的连接条件是相等#每个职位的员工个数selectj.job_title职位名称,count(*)数量frommyemployees.jobsjjoinmyemployees.employeeseonj.job_id=e.job_id#相等groupby职位名称非等值连接on后的连接条件是不等的#职工的工资等级selecte.first_name名称,e.salary工资,j.grade_level等级frommyemployees.employeesejoinmyemployees.job_gradesjone.salarybetweenj.lowest_salandj.highest_sal============================名称|工资|等级|-----------|-----|--|Steven|24000|E|Neena|17000|E|Lex|17000|E|Alexander|9000|C|Bruce|6000|C|David|4800|B|Valli|4800|B|自连接连接的两张表数据来自于同一张，只要别名不同就好selecte.employee_id领导编号,e.first_name领导名称,e1.first_name职工名称,e1.manager_id职工编号frommyemployees.employeesejoinmyemployees.employeese1one.employee_id=e1.manager_id;=================================领导编号|领导名称|职工名称|职工编号|----|---------|-----------|----|100|Steven|Neena|100|100|Steven|Lex|100|102|Lex|Alexander|102|103|Alexander|Bruce|103|103|Alexander|David|103|103|Alexander|Valli|103|外连接外连接其实是：内连接的结果+主表中有而从表里没有的可以使用where、having添加条件去掉null值左外连接左外连接：from后的主表#查询女生里没有男朋友的selectgby.name,gbs.boyNamefromgirls.beautygbyleftjoingirls.boysgbsongby.boyfriend_id=gbs.id============================name|boyName|----------|-------|周芷若|张无忌|小昭|张无忌|赵敏|张无忌|热巴|鹿晗|Angelababy|黄晓明|王语嫣|段誉|柳岩||苍老师||周冬雨||岳灵珊||双儿||夏雪||右外连接结果与上面一样，但是换成了右外连接，主从表的位置也换了，现在就是join后是主表#查询女生里没有男朋友的selectgby.name,gbs.boyNamefromgirls.boysgbsrightjoingirls.beautygbyongby.boyfriend_id=gbs.id============================name|boyName|----------|-------|周芷若|张无忌|小昭|张无忌|赵敏|张无忌|热巴|鹿晗|Angelababy|黄晓明|王语嫣|段誉|柳岩||苍老师||周冬雨||岳灵珊||双儿||夏雪||交叉连接(笛卡尔乘积)注意没有on关键字selectgby.*,gbs.*fromgirls.beautygbycrossjoingirls.boysgbs=========================id|name|sex|borndate|phone|photo|boyfriend_id|id|boyName|userCP|--|----------|---|---------------------|-----------|-----|------------|--|-------|------|3|Angelababy|女|1989-02-0300:00:00.0|18209876567||3|1|张无忌|100|3|Angelababy|女|1989-02-0300:00:00.0|18209876567||3|2|鹿晗|800|3|Angelababy|女|1989-02-0300:00:00.0|18209876567||3|3|黄晓明|50|3|Angelababy|女|1989-02-0300:00:00.0|18209876567||3|4|段誉|300|4|热巴|女|1993-02-0300:00:00.0|18209876579||2|1|张无忌|100|4|热巴|女|1993-02-0300:00:00.0|18209876579||2|2|鹿晗|800|4|热巴|女|1993-02-0300:00:00.0|18209876579||2|3|黄晓明|50|4|热巴|女|1993-02-0300:00:00.0|18209876579||2|4|段誉|300|
格式为了结果有意义，分组查询一般和分组函数一起使用。selectavg(age),name#这个字段需要在后面groupby出现fromspark.user[where子句]groupbyname[orderby子句];统计每个分组行数selectcount(*),namefromspark.usergroupbyname;--------------count(*)|name|--------|----------|1|Bigdataboy|2|Black|2|Bob|分组前过滤关键字where，位置在groupby前面。需要过滤的数据在原始表里就在分组前过滤。格式select字段from表where过滤条件groupby分组分组后过滤关键字having，位置在groupby后面。需要过滤的数据是在分组后产生，就分组后过滤。格式select字段from表groupby分组having过滤条件通过函数、表达式分组其实MySQL是可以在groupby语句后使用别名的，但其他数据库就可能不支持。#先通过email的长度进行分组，然后再统计name的列数。selectcount(*),namefromspark.`user`groupbylength(email)多个字段分组分组有多个字段时，是按照多个字段的值相同分一组。查询数据Id|name|age|email|--|----------|---|-----|1|Bigdataboy|18|aaa|2|Bob|17|aaa|3|Black|18|bbb|4|Black|16|bbb|5|Bob|16|bbb|6|Bob|16|aaa|7|Bob|17|aaa|按照name,email两个字段分组selectcount(*),name,emailfromspark.`user`groupbyname,email;#--------------------------#count(*)|name|email|--------|----------|-----|1|Bigdataboy|aaa|2|Black|bbb|3|Bob|aaa|1|Bob|bbb|
概述大体分为两类单行函数：执行之后，返回结果里字段的值不会减少，一般用于对字段进行操作。比如：length()、ifnull()、concat()聚合函数：又称分组函数，执行之后，字段里的值会减少，一般用于统计。比如：单行函数字符函数length()参数的字节长度selectlength("aaa");拼接concat()selectconcat("aaa","bbb");大小写upper|lowerselectupper("aa");selectlower("AA");截取索引从1开始#第三个参数是截取长度selectsubstring("abc",2,3);instr返回子串第一次出现的位置，没有返回0selectinstr("abcd","b")asoutput;trim()去掉前后空格，可以指定去掉的字符串selecttrim("aaa");selecttrim("a"from"aba")asoutput;--------output|------|b|lpad()左填充到指定长度，可指定填充字符selectlpad("吼吼吼",10,"*")asoutpat;rpad()右填充到指定长度，可指定填充字符selectrpad("吼吼吼",10,"*")asoutpat;replace()替换指定字符串，是全部替换selectreplace("aa吼吼吼","aa","bbb")asoutpat;数学函数四舍五入selectround(4.56);selectround(4.567,2);#保留两位小数向上取整，>=这个数selectceil(4.00);向下取整selectfloor(4.56);取余数，跟%一样selectmod(10,3);保留几位小数，不会进行四舍五入selecttruncate(1.5665,2);日期函数现在时间selectnow()as现在时间;------------------------现在时间|---------------------|2020-02-2816:40:44.0|现在日期selectcurdate()as现在日期;----------------------------现在日期|----------|2020-02-28|现在时间selectcurtime()as现在时间;----------------------------现在时间|--------|16:45:56|只截取日期格式里的某一部分，如年、月、日…其他就比如：year()、month()、day()、hour()、minute()、second()selectyear(now())as现在的年;-------------------------------现在的年|----|2020|提取字符串中的时间selectstr_to_date('2019--01-2016:01:45','%Y--%m-%d')as提取日期;-------------------------提取日期|----------|2019-01-20|时间转换时间字符串selectdate_format("1999-1-12","%Y年%m月%d日")as日期字符串;------------------------------------日期字符串|-----------|1999年01月12日|其他函数版本号selectversion();当前数据库selectdatabase();当前用户selectuser();流程控制函数if流程控制，实现ifelse效果selectif(10>5,"大","小")as结果;---------------------------------结果|--|大|case相当于Scala的模式匹配第一种匹配#格式selectcase字段或者表达式when待匹配值then值或表达式when待匹配值then值或表达式...else未匹配到的处理end结束标志----------------------------selectcase10%3when1then"奇数"when0then"偶数"else"未知"endas"类型"-----------------类型|--|奇数|第二中匹配，主要区别是case不用写值，其他都一样。#注意先匹配的先后顺序selectName,Population,casewhenPopulation>120000then"一级"whenPopulation>110000then"二级"else"其他"endas"等级"fromworld.city---------------Name|Population|等级|---------------------------------|----------|--|Serang|122400|一级|Probolinggo|120770|一级|Cilegon|117000|二级|Cianjur|114300|二级|Ciparay|111500|二级|Lhokseumawe|109600|其他|聚合函数简单的函数max():最大值min():最小值avg():平均值count():统计数量sum():求和以上函数都忽略null值，比如:count()结果不会统计null值，所以结果并不是字段中有多少个值。都可以与distinct去重配合使用，常与count()搭配更多。count(distinctPopulation)单独使用count():count(*)：这样返回的就是真正的行数，全部字段一排里只要有一个字段不为null就会加1。count(1)：会生成一行虚拟的值(值全为1)，统计这一行。和分组函数一同查询的字段的要求是groupby操作后的字段。selectmax(Population)最大值,min(Population)最小值,avg(Population)平均值,count(Population)数量,sum(Population)和fromworld.city---------------最大值|最小值|平均值|数量|和|--------|---|-----------|----|----------|10500000|42|350468.2236|4079|1429559884|