--¸ø±íÌí¼Ó×¢ÊÍ comment on table emp is '¹ÍÔ±±í'; --¸øÁÐÌí¼Ó×¢ÊÍ comment on column emp.ename is '¹ÍÔ±ÐÕÃû'; /*sqlÓï¾äѧϰ SELECT [DISTINCT] {*,column alias,..} FROM table alias Where Ìõ¼þ±í´ïʽ */ --²éѯ¹ÍÔ±±íÖв¿ÃűàºÅÊÇ10µÄÔ±¹¤ select empno,ename,job from emp where deptno = 10; --dinstinct È¥³ýÖظ´Êý¾Ý select distinct deptno from emp; --È¥ÖØÒ²¿ÉÒÔÕë¶Ô¶à¸ö×ֶΣ¬¶à¸ö×Ö¶ÎÖµÖ»ÒªÓÐÒ»¸ö²»Æ¥Åä¾ÍËãÊDz»Í¬µÄ¼Ç¼ select distinct deptno,sal from emp; --ÔÚ²éѯµÄ¹ý³ÌÖпÉÒÔ¸øÁÐÌí¼Ó±ðÃû£¬Í¬Ê±Ò²¿ÉÒÔ¸ø±íÌí¼Ó±ðÃû select e.empno ¹ÍÔ±±àºÅ,e.ename ¹ÍÔ±Ãû³Æ,e.job ¹ÍÔ±¹¤×÷ from emp e where e.deptno = 10; --¸øÁÐÆð±ðÃû¿ÉÒÔ¼Óas£¬Ò²¿ÉÒÔ²»¼Ó£¬¿´ÄãÐÄÇé select e.empno as ¹ÍÔ±±àºÅ,e.ename as ¹ÍÔ±Ãû³Æ,e.job as ¹ÍÔ±¹¤×÷ from emp e where e.deptno = 10; --¸øÁÐÆð±ðÃû£¬Èç¹û±ðÃûÖаüº¬¿Õ¸ñ£¬ÄÇôÐèÒª½«±ðÃûÕûÌåÓá°¡±°üº¬ÆðÀ´ select e.empno as "¹ÍÔ± ±àºÅ",e.ename as "¹ÍÔ± Ãû³Æ",e.job as "¹ÍÔ± ¹¤×÷" from emp e where e.deptno = 10; --²éѯ±íÖеÄËùÓÐ×Ö¶Î,¿ÉÒÔʹÓÃ*,µ«ÊÇÔÚÏîÄ¿ÖÐǧÍò²»ÒªËæ±ãʹÓÃ*,ÈÝÒ×±»´òËÀ select * from emp; /* £½£¬£¡£½,<>£¬<,>,<=,>=,any,some,all is null,is not null between x and y in£¨list£©£¬not in£¨list£© exists£¨sub£­query£© like _ ,%,escape ¡®\¡® _\% escape ¡®\¡¯ */ -- = select * from emp where deptno = 20; --!= select * from emp where deptno !=20; --<> ²»µÈÓÚ select * from emp where deptno <> 20; --<, select sal from emp where sal <1500; -->, select sal from emp where sal >1500; --<=, select sal from emp where sal <=1500; -->=, select sal from emp where sal >=1500; --any,È¡ÆäÖÐÈÎÒâÒ»¸ö select sal from emp where sal > any(1000,1500,3000); --some,some¸úanyÊÇͬһ¸öЧ¹û£¬Ö»Òª´óÓÚÆäÖÐijһ¸öÖµ¶¼»á³ÉÁ¢ select sal from emp where sal > some(1000,1500,3000); --all£¬´óÓÚËùÓеÄÖµ²Å»á³ÉÁ¢ select sal from emp where sal > all(1000,1500,3000); --is null,ÔÚsqlµÄÓï·¨ÖУ¬null±íʾһ¸öÌØÊâµÄº¬Ò壬null != null,²»ÄÜʹÓÃ=£¬£¡=Åжϣ¬ÐèҪʹÓÃis ,is not select * from emp where comm is null; --,is not null select * from emp where comm is not null; select * from emp where null is null; --between x and y,°üº¬xºÍyµÄÖµ select * from emp where sal between 1500 and 3000; select * from emp where sal >=1500 and sal <=3000; --ÐèÒª½øÐÐijЩֵµÄµÈÖµÅжϵÄʱºò¿ÉÒÔʹÓÃinºÍnot in --in£¨list£©£¬ select * from emp where deptno in(10,20); --¿ÉÊÇÓÃand ºÍorÕâÑùµÄ¹Ø¼ü×Ö£¬andÏ൱ÓÚÊÇÓë²Ù×÷£¬orÏ൱ÓÚÊÇ»ò²Ù×÷ --andºÍor¿ÉÄܳöÏÖÔÚͬһ¸ösqlÓï¾äÖУ¬´ËʱÐèҪעÒâandºÍorµÄÓÅÏȼ¶ --and µÄÓÅÏȼ¶Òª¸ßÓÚor£¬ËùÒÔÒ»¶¨Òª½«orµÄÏà¹Ø²Ù×÷Ó㨣©À¨ÆðÀ´£¬Ìá¸ßÓÅÏȼ¶ select * from emp where deptno = 10 or deptno = 20; --not in£¨list£© select * from emp where deptno not in(10,20); select * from emp where deptno != 10 and deptno !=20; /*exists£¨sub£­query£©,µ±existsÖеÄ×Ó²éѯÓï¾äÄܲ鵽¶ÔÓ¦½á¹ûµÄʱºò£¬ Òâζ×ÅÌõ¼þÂú×ã Ï൱ÓÚË«²ãforÑ­»· --ÏÖÔÚÒª²éѯ²¿ÃűàºÅΪ10ºÍ20µÄÔ±¹¤£¬ÒªÇóʹÓÃexistsʵÏÖ */ select * from emp where deptno = 10 or deptno = 20; --ͨ¹ýÍâ²ãÑ­»·À´¹æ·¶ÄÚ²ãÑ­»· select * from emp e where exists (select deptno from dept d where (d.deptno = 10 or d.deptno = 20) and e.deptno = d.deptno) /* Ä£ºý²éѯ£º like _ ,%,escape ¡®\¡® _\% escape ¡®\¡¯ ÔÚlikeµÄÓï¾äÖУ¬ÐèҪʹÓÃռλ·û»òÕßͨÅä·û _,ij¸ö×Ö·û»òÕßÊý×Ö½ö³öÏÖÒ»´Î %£¬ÈÎÒâ×Ö·û³öÏÖÈÎÒâ´ÎÊý escape,ʹÓÃתÒå×Ö·û,¿ÉÒÔ×Ô¼º¹æ¶¨×ªÒå×Ö·û ʹÓÃlikeµÄʱºòÒªÉ÷ÖØ£¬ÒòΪlikeµÄЧÂÊ±È½ÏµÍ Ê¹ÓÃlike¿ÉÒԲο¼Ê¹ÓÃË÷Òý£¬µ«ÊÇÒªÇó²»ÄÜÒÔ%¿ªÍ· Éæ¼°µ½´óÎı¾µÄ¼ìË÷µÄʱºò£¬¿ÉÒÔʹÓÃijЩ¿ò¼Ü luence£¬solr£¬elastic search */ --²éѯÃû×ÖÒÔS¿ªÍ·µÄÓû§ select * from emp where ename like('S%') --²éѯÃû×ÖÒÔS¿ªÍ·ÇÒµ¹ÊýµÚ¶þ¸ö×Ö·ûΪTµÄÓû§ select * from emp where ename like('S%T_'); select * from emp where ename like('S%T%'); --²éѯÃû×ÖÖдø%µÄÓû§ select * from emp where ename like('%\%%') escape('\') /* order by½øÐÐÅÅÐò²Ù×÷ ĬÈÏÇé¿öÏÂÍê³ÉµÄÊÇÉýÐòµÄ²Ù×÷£¬ asc:ÊÇĬÈϵÄÅÅÐò·½Ê½£¬±íʾÉýÐò desc£º½µÐòµÄÅÅÐò·½Ê½ ÅÅÐòÊÇ°´ÕÕ×ÔȻ˳Ðò½øÐÐÅÅÐòµÄ Èç¹ûÊÇÊýÖµ£¬ÄÇô°´ÕÕ´Ó´óµ½Ð¡ Èç¹ûÊÇ×Ö·û´®£¬ÄÇô°´ÕÕ×ÖµäÐòÅÅÐò ÔÚ½øÐÐÅÅÐòµÄʱºò¿ÉÒÔÖ¸¶¨¶à¸ö×ֶΣ¬¶øÇÒ¶à¸ö×ֶοÉÒÔʹÓò»Í¬µÄÅÅÐò·½Ê½ ÿ´ÎÔÚÖ´ÐÐorder byµÄʱºòÏ൱ÓÚÊÇ×öÁËÈ«ÅÅÐò£¬Ë¼¿¼È«ÅÅÐòµÄЧÂÊ »á±È½ÏºÄ·ÑϵͳµÄ×ÊÔ´£¬Òò´ËÑ¡ÔñÔÚÒµÎñ²»Ì«·±Ã¦µÄʱºò½øÐÐ */ select * from emp order by sal; select * from emp order by sal desc; select * from emp order by ename; select * from emp order by sal desc,ename asc; --ʹÓüÆËã×ֶΠ--×Ö·û´®Á¬½Ó·û select 'my name is '||ename name from emp; select concat('my name is ',ename) from emp; --¼ÆËãËùÓÐÔ±¹¤µÄÄêн select ename,(e.sal+e.comm)*12 from emp e; --nullÊDZȽÏÌØÊâµÄ´æÔÚ£¬null×öÈκÎÔËË㶼»¹ÊÇΪnull£¬Òò´ËÒª½«¿Õ½øÐÐת»» --ÒýÈ뺯Êýnvl£¬nvl(arg1,arg2),Èç¹ûarg1ÊÇ¿Õ£¬ÄÇô·µ»Øarg2£¬Èç¹û²»ÊÇ¿Õ£¬Ôò·µ»ØÔ­À´µÄÖµ select ename,(e.sal+nvl(e.comm,0))*12 from emp e; --dualÊÇoracleÊý¾Ý¿âÖеÄÒ»ÕÅÐéÄâ±í£¬Ã»ÓÐʵ¼ÊµÄÊý¾Ý£¬¿ÉÒÔÓÃÀ´×ö²âÊÔ select 100+null from dual; --A select * from emp where deptno =30; --B select * from emp where sal >1000; --²¢¼¯£¬½«Á½¸ö¼¯ºÏÖеÄËùÓÐÊý¾Ý¶¼½øÐÐÏÔʾ£¬µ«ÊDz»°üº¬Öظ´µÄÊý¾Ý select * from emp where deptno =30 union select * from emp where sal >1000; --È«¼¯£¬½«Á½¸ö¼¯ºÏµÄÊý¾ÝÈ«²¿ÏÔʾ£¬²»»áÍê³ÉÈ¥ÖصIJÙ×÷ select * from emp where deptno =30 union all select * from emp where sal >1000; --½»¼¯£¬Á½¸ö¼¯ºÏÖн»²æµÄÊý¾Ý¼¯£¬Ö»ÏÔʾһ´Î select * from emp where deptno =30 intersect select * from emp where sal >1000; --²î¼¯,°üº¬ÔÚA¼¯ºÏ¶ø²»°üº¬ÔÚB¼¯ºÏÖеÄÊý¾Ý£¬¸úAºÍBµÄ¼¯ºÏ˳ÐòÏà¹Ø select * from emp where deptno =30 minus select * from emp where sal >1000;