|
| 1 | +--��������ע�� |
| 2 | +comment on table emp is '��Ա��'; |
| 3 | +--��������ע�� |
| 4 | +comment on column emp.ename is '��Ա����'; |
| 5 | + |
| 6 | +/*sql���ѧϰ |
| 7 | + |
| 8 | +SELECT [DISTINCT] {*,column alias,..} |
| 9 | +FROM table alias |
| 10 | +Where ��������ʽ |
| 11 | + |
| 12 | +*/ |
| 13 | + |
| 14 | +--��ѯ��Ա���в��ű����10��Ա�� |
| 15 | +select empno,ename,job from emp where deptno = 10; |
| 16 | +--dinstinct ȥ���ظ����� |
| 17 | +select distinct deptno from emp; |
| 18 | +--ȥ��Ҳ������Զ���ֶΣ�����ֶ�ֵֻҪ��һ����ƥ������Dz�ͬ�ļ�¼ |
| 19 | +select distinct deptno,sal from emp; |
| 20 | + |
| 21 | + |
| 22 | +--�ڲ�ѯ�Ĺ����п��Ը������ӱ�����ͬʱҲ���Ը������ӱ��� |
| 23 | +select e.empno ��Ա���,e.ename ��Ա����,e.job ��Ա���� from emp e where e.deptno = 10; |
| 24 | +--������������Լ�as��Ҳ���Բ��ӣ��������� |
| 25 | +select e.empno as ��Ա���,e.ename as ��Ա����,e.job as ��Ա���� from emp e where e.deptno = 10; |
| 26 | +--�������������������а����ո���ô��Ҫ�����������á����������� |
| 27 | +select e.empno as "��Ա ���",e.ename as "��Ա ����",e.job as "��Ա ����" from emp e where e.deptno = 10; |
| 28 | +--��ѯ���е������ֶ�,����ʹ��*,��������Ŀ��ǧ��Ҫ���ʹ��*,���ױ����� |
| 29 | +select * from emp; |
| 30 | + |
| 31 | + |
| 32 | +/* |
| 33 | +��������,<>��<,>,<=,>=,any,some,all |
| 34 | +is null,is not null |
| 35 | +between x and y |
| 36 | +in��list����not in��list�� |
| 37 | +exists��sub��query�� |
| 38 | +like _ ,%,escape ��\�� _\% escape ��\�� |
| 39 | + |
| 40 | +*/ |
| 41 | +-- = |
| 42 | +select * from emp where deptno = 20; |
| 43 | +--!= |
| 44 | +select * from emp where deptno !=20; |
| 45 | +--<> ������ |
| 46 | +select * from emp where deptno <> 20; |
| 47 | +--<, |
| 48 | +select sal from emp where sal <1500; |
| 49 | +-->, |
| 50 | +select sal from emp where sal >1500; |
| 51 | +--<=, |
| 52 | +select sal from emp where sal <=1500; |
| 53 | +-->=, |
| 54 | +select sal from emp where sal >=1500; |
| 55 | +--any,ȡ��������һ�� |
| 56 | +select sal from emp where sal > any(1000,1500,3000); |
| 57 | +--some,some��any��ͬһ��Ч����ֻҪ��������ijһ��ֵ������� |
| 58 | +select sal from emp where sal > some(1000,1500,3000); |
| 59 | +--all���������е�ֵ�Ż���� |
| 60 | +select sal from emp where sal > all(1000,1500,3000); |
| 61 | +--is null,��sql����У�null��ʾһ������ĺ��壬null != null,����ʹ��=����=�жϣ���Ҫʹ��is ,is not |
| 62 | +select * from emp where comm is null; |
| 63 | +--,is not null |
| 64 | +select * from emp where comm is not null; |
| 65 | +select * from emp where null is null; |
| 66 | +--between x and y,����x��y��ֵ |
| 67 | +select * from emp where sal between 1500 and 3000; |
| 68 | +select * from emp where sal >=1500 and sal <=3000; |
| 69 | +--��Ҫ����ijЩֵ�ĵ�ֵ�жϵ�ʱ�����ʹ��in��not in |
| 70 | +--in��list���� |
| 71 | +select * from emp where deptno in(10,20); |
| 72 | +--������and ��or�����Ĺؼ��֣�and�൱�����������or�൱���ǻ���� |
| 73 | +--and��or���ܳ�����ͬһ��sql����У���ʱ��Ҫע��and��or�����ȼ� |
| 74 | +--and �����ȼ�Ҫ����or������һ��Ҫ��or����ز����ã�����������������ȼ� |
| 75 | +select * from emp where deptno = 10 or deptno = 20; |
| 76 | +--not in��list�� |
| 77 | +select * from emp where deptno not in(10,20); |
| 78 | +select * from emp where deptno != 10 and deptno !=20; |
| 79 | +/*exists��sub��query��,��exists�е��Ӳ�ѯ����ܲ鵽��Ӧ�����ʱ�� |
| 80 | +����������� |
| 81 | +�൱��˫��forѭ�� |
| 82 | +--����Ҫ��ѯ���ű��Ϊ10��20��Ա����Ҫ��ʹ��existsʵ�� |
| 83 | +*/ |
| 84 | +select * from emp where deptno = 10 or deptno = 20; |
| 85 | +--ͨ�����ѭ�����淶�ڲ�ѭ�� |
| 86 | +select * |
| 87 | + from emp e |
| 88 | + where exists (select deptno |
| 89 | + from dept d |
| 90 | + where (d.deptno = 10 or d.deptno = 20) |
| 91 | + and e.deptno = d.deptno) |
| 92 | +/* |
| 93 | +ģ����ѯ�� |
| 94 | +like _ ,%,escape ��\�� _\% escape ��\�� |
| 95 | + |
| 96 | +��like������У���Ҫʹ��ռλ������ͨ��� |
| 97 | +_,ij���ַ��������ֽ�����һ�� |
| 98 | +%�������ַ������������ |
| 99 | +escape,ʹ��ת���ַ�,�����Լ��涨ת���ַ� |
| 100 | + |
| 101 | +ʹ��like��ʱ��Ҫ���أ���Ϊlike��Ч�ʱȽϵ� |
| 102 | +ʹ��like���Բο�ʹ������������Ҫ������%��ͷ |
| 103 | +�漰�����ı��ļ�����ʱ����ʹ��ijЩ��� luence��solr��elastic search |
| 104 | +*/ |
| 105 | +--��ѯ������S��ͷ���û� |
| 106 | +select * from emp where ename like('S%') |
| 107 | +--��ѯ������S��ͷ�ҵ����ڶ����ַ�ΪT���û� |
| 108 | +select * from emp where ename like('S%T_'); |
| 109 | +select * from emp where ename like('S%T%'); |
| 110 | +--��ѯ�����д�%���û� |
| 111 | +select * from emp where ename like('%\%%') escape('\') |
| 112 | +/* |
| 113 | + |
| 114 | +order by����������� |
| 115 | +Ĭ���������ɵ�������IJ����� |
| 116 | +asc:��Ĭ�ϵ�����ʽ����ʾ���� |
| 117 | +desc�����������ʽ |
| 118 | + |
| 119 | +�����ǰ�����Ȼ˳���������� |
| 120 | +�������ֵ����ô���մӴ�С |
| 121 | +������ַ�������ô�����ֵ������� |
| 122 | + |
| 123 | +�ڽ��������ʱ�����ָ������ֶΣ����Ҷ���ֶο���ʹ�ò�ͬ������ʽ |
| 124 | + |
| 125 | +ÿ����ִ��order by��ʱ���൱��������ȫ����˼��ȫ�����Ч�� |
| 126 | +��ȽϺķ�ϵͳ����Դ�����ѡ����ҵ��̫��æ��ʱ����� |
| 127 | +*/ |
| 128 | +select * from emp order by sal; |
| 129 | +select * from emp order by sal desc; |
| 130 | +select * from emp order by ename; |
| 131 | +select * from emp order by sal desc,ename asc; |
| 132 | +--ʹ�ü����ֶ� |
| 133 | +--�ַ������ӷ� |
| 134 | +select 'my name is '||ename name from emp; |
| 135 | +select concat('my name is ',ename) from emp; |
| 136 | +--��������Ա������н |
| 137 | +select ename,(e.sal+e.comm)*12 from emp e; |
| 138 | +--null�DZȽ�����Ĵ��ڣ�null���κ����㶼����Ϊnull�����Ҫ���ս���ת�� |
| 139 | +--���뺯��nvl��nvl(arg1,arg2),���arg1�ǿգ���ô����arg2��������ǿգ���ԭ����ֵ |
| 140 | +select ename,(e.sal+nvl(e.comm,0))*12 from emp e; |
| 141 | +--dual��oracle���ݿ��е�һ���������û��ʵ�ʵ����ݣ��������������� |
| 142 | +select 100+null from dual; |
| 143 | +--A |
| 144 | +select * from emp where deptno =30; |
| 145 | +--B |
| 146 | +select * from emp where sal >1000; |
| 147 | +--�����������������е��������ݶ�������ʾ�����Dz������ظ������� |
| 148 | +select * from emp where deptno =30 union |
| 149 | +select * from emp where sal >1000; |
| 150 | +--ȫ�������������ϵ�����ȫ����ʾ���������ȥ�صIJ��� |
| 151 | +select * from emp where deptno =30 union all |
| 152 | +select * from emp where sal >1000; |
| 153 | +--���������������н�������ݼ���ֻ��ʾһ�� |
| 154 | +select * from emp where deptno =30 intersect |
| 155 | +select * from emp where sal >1000; |
| 156 | +--�,������A���϶���������B�����е����ݣ���A��B�ļ���˳����� |
| 157 | +select * from emp where deptno =30 minus |
| 158 | +select * from emp where sal >1000; |
| 159 | + |
| 160 | + |
| 161 | + |
| 162 | + |
| 163 | + |
| 164 | + |
0 commit comments