MySQL查询语法汇总
前言：
本篇文章主要简介下MySQL中where,groupby,orderby,limit,join,union,unionall,子表等查询语法。
测试数据准备
createtableemp(empnonumeric(4)notnull,enamevarchar(10),jobvarchar(9),mgrnumeric(4),hiredatedatetime,salnumeric(7,2),commnumeric(7,2),deptnonumeric(2)); createtabledept(deptnonumeric(2),dnamevarchar(14),locvarchar(13)); createtablesalgrade(gradenumeric,losalnumeric,hisalnumeric); insertintodeptvalues(10,'ACCOUNTING','NEWYORK');insertintodeptvalues(20,'RESEARCH','DALLAS');insertintodeptvalues(30,'SALES','CHICAGO');insertintodeptvalues(40,'OPERATIONS','BOSTON'); insertintosalgradevalues(1,700,1200);insertintosalgradevalues(2,1201,1400);insertintosalgradevalues(3,1401,2000);insertintosalgradevalues(4,2001,3000);insertintosalgradevalues(5,3001,9999); insertintoempvalues(7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20);insertintoempvalues(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);insertintoempvalues(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);insertintoempvalues(7566,'JONES','MANAGER',7839,'1981-04-02',2975,null,20);insertintoempvalues(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);insertintoempvalues(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,null,30);insertintoempvalues(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,null,10);insertintoempvalues(7788,'SCOTT','ANALYST',7566,'1982-12-09',3000,null,20);insertintoempvalues(7839,'KING','PRESIDENT',null,'1981-11-17',5000,null,10);insertintoempvalues(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);insertintoempvalues(7876,'ADAMS','CLERK',7788,'1983-01-12',1100,null,20);insertintoempvalues(7900,'JAMES','CLERK',7698,'1981-12-03',950,null,30);insertintoempvalues(7902,'FORD','ANALYST',7566,'1981-12-03',3000,null,20);insertintoempvalues(7934,'MILLER','CLERK',7782,'1982-01-23',1300,null,10);
1.模糊查询
mysql>select*fromempwhereenamelike'%S%';+-------+-------+---------+------+---------------------+---------+------+--------+|empno|ename|job|mgr|hiredate|sal|comm|deptno|+-------+-------+---------+------+---------------------+---------+------+--------+|7369|SMITH|CLERK|7902|1980-12-1700:00:00|800.00|NULL|20||7566|JONES|MANAGER|7839|1981-04-0200:00:00|2975.00|NULL|20||7788|SCOTT|ANALYST|7566|1982-12-0900:00:00|3000.00|NULL|20||7876|ADAMS|CLERK|7788|1983-01-1200:00:00|1100.00|NULL|20||7900|JAMES|CLERK|7698|1981-12-0300:00:00|950.00|NULL|30|+-------+-------+---------+------+---------------------+---------+------+--------+5rowsinset(0.00sec) mysql>select*fromempwhereenamelike'S%';+-------+-------+---------+------+---------------------+---------+------+--------+|empno|ename|job|mgr|hiredate|sal|comm|deptno|+-------+-------+---------+------+---------------------+---------+------+--------+|7369|SMITH|CLERK|7902|1980-12-1700:00:00|800.00|NULL|20||7788|SCOTT|ANALYST|7566|1982-12-0900:00:00|3000.00|NULL|20|+-------+-------+---------+------+---------------------+---------+------+--------+2rowsinset(0.01sec) mysql>select*fromempwhereenamelike'%S';+-------+-------+---------+------+---------------------+---------+------+--------+|empno|ename|job|mgr|hiredate|sal|comm|deptno|+-------+-------+---------+------+---------------------+---------+------+--------+|7566|JONES|MANAGER|7839|1981-04-0200:00:00|2975.00|NULL|20||7876|ADAMS|CLERK|7788|1983-01-1200:00:00|1100.00|NULL|20||7900|JAMES|CLERK|7698|1981-12-0300:00:00|950.00|NULL|30|+-------+-------+---------+------+---------------------+---------+------+--------+3rowsinset(0.00sec) mysql>select*fromempwhereenamelike'_O%';+-------+-------+---------+------+---------------------+---------+------+--------+|empno|ename|job|mgr|hiredate|sal|comm|deptno|+-------+-------+---------+------+---------------------+---------+------+--------+|7566|JONES|MANAGER|7839|1981-04-0200:00:00|2975.00|NULL|20||7902|FORD|ANALYST|7566|1981-12-0300:00:00|3000.00|NULL|20|+-------+-------+---------+------+---------------------+---------+------+--------+2rowsinset(0.00sec)
总结：%表示任意0个或多个字符，可匹配任意类型和长度的字符；_表示任意单个字符，匹配单个任意字符。
2.排序
mysql>select*fromemporderbysal;+-------+--------+-----------+------+---------------------+---------+---------+--------+|empno|ename|job|mgr|hiredate|sal|comm|deptno|+-------+--------+-----------+------+---------------------+---------+---------+--------+|7369|SMITH|CLERK|7902|1980-12-1700:00:00|800.00|NULL|20||7900|JAMES|CLERK|7698|1981-12-0300:00:00|950.00|NULL|30||7876|ADAMS|CLERK|7788|1983-01-1200:00:00|1100.00|NULL|20||7521|WARD|SALESMAN|7698|1981-02-2200:00:00|1250.00|500.00|30||7654|MARTIN|SALESMAN|7698|1981-09-2800:00:00|1250.00|1400.00|30||7934|MILLER|CLERK|7782|1982-01-2300:00:00|1300.00|NULL|10||7844|TURNER|SALESMAN|7698|1981-09-0800:00:00|1500.00|0.00|30||7499|ALLEN|SALESMAN|7698|1981-02-2000:00:00|1600.00|300.00|30||7782|CLARK|MANAGER|7839|1981-06-0900:00:00|2450.00|NULL|10||7698|BLAKE|MANAGER|7839|1981-05-0100:00:00|2850.00|NULL|30||7566|JONES|MANAGER|7839|1981-04-0200:00:00|2975.00|NULL|20||7788|SCOTT|ANALYST|7566|1982-12-0900:00:00|3000.00|NULL|20||7902|FORD|ANALYST|7566|1981-12-0300:00:00|3000.00|NULL|20||7839|KING|PRESIDENT|NULL|1981-11-1700:00:00|5000.00|NULL|10|+-------+--------+-----------+------+---------------------+---------+---------+--------+14rowsinset(0.00sec) mysql>select*fromemporderbysalasc;+-------+--------+-----------+------+---------------------+---------+---------+--------+|empno|ename|job|mgr|hiredate|sal|comm|deptno|+-------+--------+-----------+------+---------------------+---------+---------+--------+|7369|SMITH|CLERK|7902|1980-12-1700:00:00|800.00|NULL|20||7900|JAMES|CLERK|7698|1981-12-0300:00:00|950.00|NULL|30||7876|ADAMS|CLERK|7788|1983-01-1200:00:00|1100.00|NULL|20||7521|WARD|SALESMAN|7698|1981-02-2200:00:00|1250.00|500.00|30||7654|MARTIN|SALESMAN|7698|1981-09-2800:00:00|1250.00|1400.00|30||7934|MILLER|CLERK|7782|1982-01-2300:00:00|1300.00|NULL|10||7844|TURNER|SALESMAN|7698|1981-09-0800:00:00|1500.00|0.00|30||7499|ALLEN|SALESMAN|7698|1981-02-2000:00:00|1600.00|300.00|30||7782|CLARK|MANAGER|7839|1981-06-0900:00:00|2450.00|NULL|10||7698|BLAKE|MANAGER|7839|1981-05-0100:00:00|2850.00|NULL|30||7566|JONES|MANAGER|7839|1981-04-0200:00:00|2975.00|NULL|20||7788|SCOTT|ANALYST|7566|1982-12-0900:00:00|3000.00|NULL|20||7902|FORD|ANALYST|7566|1981-12-0300:00:00|3000.00|NULL|20||7839|KING|PRESIDENT|NULL|1981-11-1700:00:00|5000.00|NULL|10|+-------+--------+-----------+------+---------------------+---------+---------+--------+14rowsinset(0.00sec) mysql>select*fromemporderbysaldesc;+-------+--------+-----------+------+---------------------+---------+---------+--------+|empno|ename|job|mgr|hiredate|sal|comm|deptno|+-------+--------+-----------+------+---------------------+---------+---------+--------+|7839|KING|PRESIDENT|NULL|1981-11-1700:00:00|5000.00|NULL|10||7788|SCOTT|ANALYST|7566|1982-12-0900:00:00|3000.00|NULL|20||7902|FORD|ANALYST|7566|1981-12-0300:00:00|3000.00|NULL|20||7566|JONES|MANAGER|7839|1981-04-0200:00:00|2975.00|NULL|20||7698|BLAKE|MANAGER|7839|1981-05-0100:00:00|2850.00|NULL|30||7782|CLARK|MANAGER|7839|1981-06-0900:00:00|2450.00|NULL|10||7499|ALLEN|SALESMAN|7698|1981-02-2000:00:00|1600.00|300.00|30||7844|TURNER|SALESMAN|7698|1981-09-0800:00:00|1500.00|0.00|30||7934|MILLER|CLERK|7782|1982-01-2300:00:00|1300.00|NULL|10||7521|WARD|SALESMAN|7698|1981-02-2200:00:00|1250.00|500.00|30||7654|MARTIN|SALESMAN|7698|1981-09-2800:00:00|1250.00|1400.00|30||7876|ADAMS|CLERK|7788|1983-01-1200:00:00|1100.00|NULL|20||7900|JAMES|CLERK|7698|1981-12-0300:00:00|950.00|NULL|30||7369|SMITH|CLERK|7902|1980-12-1700:00:00|800.00|NULL|20|+-------+--------+-----------+------+---------------------+---------+---------+--------+14rowsinset(0.00sec)
总结：orderby排序默认按asc升序来排列也可指定desc降序排列
3.限制多少行
mysql>select*fromemplimit3;+-------+-------+----------+------+---------------------+---------+--------+--------+|empno|ename|job|mgr|hiredate|sal|comm|deptno|+-------+-------+----------+------+---------------------+---------+--------+--------+|7369|SMITH|CLERK|7902|1980-12-1700:00:00|800.00|NULL|20||7499|ALLEN|SALESMAN|7698|1981-02-2000:00:00|1600.00|300.00|30||7521|WARD|SALESMAN|7698|1981-02-2200:00:00|1250.00|500.00|30|+-------+-------+----------+------+---------------------+---------+--------+--------+3rowsinset(0.00sec) mysql>select*fromemporderbysaldesclimit3;+-------+-------+-----------+------+---------------------+---------+------+--------+|empno|ename|job|mgr|hiredate|sal|comm|deptno|+-------+-------+-----------+------+---------------------+---------+------+--------+|7839|KING|PRESIDENT|NULL|1981-11-1700:00:00|5000.00|NULL|10||7902|FORD|ANALYST|7566|1981-12-0300:00:00|3000.00|NULL|20||7788|SCOTT|ANALYST|7566|1982-12-0900:00:00|3000.00|NULL|20|+-------+-------+-----------+------+---------------------+---------+------+--------+3rowsinset(0.00sec)
总结：limit限定显示前多少行，可与orderby联合使用
4.聚合函
count()sum()函数用法：#1.各个部门的薪水和mysql>selectdeptno,sum(sal)fromempgroupbydeptno;+--------+----------+|deptno|sum(sal)|+--------+----------+|10|8750.00||20|10875.00||30|9400.00|+--------+----------+3rowsinset(0.01sec) #2.groupby字段必须出现在select字段后面各个部门的各个岗位的薪水和mysql>selectdeptno,job,sum(sal)fromempgroupbydeptno,job;+--------+-----------+----------+|deptno|job|sum(sal)|+--------+-----------+----------+|10|CLERK|1300.00||10|MANAGER|2450.00||10|PRESIDENT|5000.00||20|ANALYST|6000.00||20|CLERK|1900.00||20|MANAGER|2975.00||30|CLERK|950.00||30|MANAGER|2850.00||30|SALESMAN|5600.00|+--------+-----------+----------+9rowsinset(0.01sec) #3.having薪水和>5000的各个部门的各个岗位mysql>selectdeptno,job,sum(sal)->fromemp->groupbydeptno,job->havingsum(sal)>5000;+--------+----------+----------+|deptno|job|sum(sal)|+--------+----------+----------+|20|ANALYST|6000.00||30|SALESMAN|5600.00|+--------+----------+----------+2rowsinset(0.00sec)#4.常用组合whereorderlimitselectdeptno,job,sum(sal)assum_salfromempwherejob='SALESMAN'groupbydeptno,jobhavingsum(sal)>5000orderbysum(sal)desclimit1;
下面介绍下join及union的用法
数据准备：
createtabletesta(aidint,anamevarchar(40)); createtabletestb(bidint,bnamevarchar(40),ageint); insertintotestavalues(1,'xiaoming');insertintotestavalues(2,'LY');insertintotestavalues(3,'KUN');insertintotestavalues(4,'ZIDONG');insertintotestavalues(5,'HB'); insertintotestbvalues(1,'xiaoming',10);insertintotestbvalues(2,'LY',100);insertintotestbvalues(3,'KUN',200);insertintotestbvalues(4,'ZIDONG',110);insertintotestbvalues(6,'niu',120);insertintotestbvalues(7,'meng',130);insertintotestbvalues(8,'mi',170);
5.leftjoin
mysql>select->a.aid,a.aname,->b.bid,b.bname,b.age->fromtestaasa->leftjointestbasbona.aid=b.bid;+------+----------+------+----------+------+|aid|aname|bid|bname|age|+------+----------+------+----------+------+|1|xiaoming|1|xiaoming|10||2|LY|2|LY|100||3|KUN|3|KUN|200||4|ZIDONG|4|ZIDONG|110||5|HB|NULL|NULL|NULL|+------+----------+------+----------+------+5rowsinset(0.00sec)
总结：aleftjoinb a表全，用b表去匹配a表LEFTJOIN关键字会从左表(a)那里返回所有的行，即使在右表(b)中没有匹配的行,匹配不到的列用NULL代替
6.rightjoin
mysql>select->a.aid,a.aname,->b.bid,b.bname,b.age->fromtestaasa->rightjointestbasbona.aid=b.bid;+------+----------+------+----------+------+|aid|aname|bid|bname|age|+------+----------+------+----------+------+|1|xiaoming|1|xiaoming|10||2|LY|2|LY|100||3|KUN|3|KUN|200||4|ZIDONG|4|ZIDONG|110||NULL|NULL|6|niu|120||NULL|NULL|7|meng|130||NULL|NULL|8|mi|170|+------+----------+------+----------+------+7rowsinset(0.00sec)
总结：arightjoinb b表全，用a表去匹配b表RIGHTJOIN关键字会右表(b)那里返回所有的行，即使在左表(a)中没有匹配的行,匹配不到的列用NULL代替
7.innerjoin
mysql>select->a.aid,a.aname,->b.bid,b.bname,b.age->fromtestaasa->innerjointestbasbona.aid=b.bid;+------+----------+------+----------+------+|aid|aname|bid|bname|age|+------+----------+------+----------+------+|1|xiaoming|1|xiaoming|10||2|LY|2|LY|100||3|KUN|3|KUN|200||4|ZIDONG|4|ZIDONG|110|+------+----------+------+----------+------+4rowsinset(0.00sec) mysql>select->a.aid,a.aname,->b.bid,b.bname,b.age->fromtestaasa->jointestbasbona.aid=b.bid;+------+----------+------+----------+------+|aid|aname|bid|bname|age|+------+----------+------+----------+------+|1|xiaoming|1|xiaoming|10||2|LY|2|LY|100||3|KUN|3|KUN|200||4|ZIDONG|4|ZIDONG|110|+------+----------+------+----------+------+4rowsinset(0.00sec)
总结：innerjoin与join效果一样在表中存在至少一个匹配时，INNERJOIN关键字返回行
8.union与unionall
mysql>selectaid,anamefromtesta->union->selectbid,bnamefromtestb;+------+----------+|aid|aname|+------+----------+|1|xiaoming||2|LY||3|KUN||4|ZIDONG||5|HB||6|niu||7|meng||8|mi|+------+----------+8rowsinset(0.01sec) mysql>selectaid,anamefromtesta->unionall->selectbid,bnamefromtestb;+------+----------+|aid|aname|+------+----------+|1|xiaoming||2|LY||3|KUN||4|ZIDONG||5|HB||1|xiaoming||2|LY||3|KUN||4|ZIDONG||6|niu||7|meng||8|mi|+------+----------+12rowsinset(0.00sec)
总结：union会去重unionall不去重
以上就是MySQL查询语法汇总的详细内容，更多关于MySQL查询语法的资料请关注毛票票其它相关文章！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。