sql语句总结

单表查询 
列名的别名和表名的别名

select Sno xuehao, Sname as xingming,2008-Sage shengri from student  

给列名取别名既可以加as也可以不加

select Sno,Sname,2008-Sage as shengri from student order by shengri where shengri > 1983

shengri字段即属性的别名是不能用来作为判断条件使用的,但是可以排序

select ss.Sno, Sname,2008-Sage from student as ss order by ss.Snoselect ss.Sno as n, Sname ,2008-Sage from student as ss order by ss.n

这种排序是错误的.
表名也可以有别名，给表名取别名既可以加as也可以不加；用表名或者表名的别名来引用列名，既可以排序也可以作为判断条件；用表名或者表名的别名来引用列名的别名，既不可以排序也不可以作为判断条件
消除重复行distinct

select distinct Sno from SC

消除取值重复的行，关键字是distinct，默认缺省值all，这个是针对某一行而不是某一个字段，也就是说各行之间的各个字段完全相同才算是重复的，而不是只有某一个字段相同就算重复了
比较运算符not运算符

select distinct Sno from SC where not grade < 60

not运算符：是针对某一个逻辑表达式（bool表达式）的，是对逻辑表达式取反的，而不是针对一个符号，比如grade not< 60，所以运算符NOT可以与比较运算符同用，对条件求非，比如not grade < 60
确定取值范围between...and...

select Sname,Sage from student where sage not between 22 and 20

between A  and  B 这个相当于>= A  and<= B, 顺序不能错！不会对A,B的大小进行检查
确定集合:in、not in

select Sname,Sage,sdept from student where sdeptin ('CS','ma')

模糊查询like

select *from student where sno= '95001'select *from student where sno like '95001'select *from student where sname like '刘%'select *from student where sname like '刘_'select *from course where cname like 'DB\_M' escape '\'select *from course where cname like 'MA\%S' escape '\'select *from course where cname like 'MA%K__'select *from student where sname not like '刘%'

当like后面的字符串没有包含通配符的时候，like就是=
 %：通配符，代表任意长度的字符串包括0.
_: 代表一个字符.默认每一个字符（数值，字母，汉字）采用ascii进行编码的.但是
管用什么方式进行编码，_代表的是一个字符，而不是一个字节.

escape 定义某一个字符，这个字符后面紧跟的通配符是不起通配作用的，当like比较的时候会忽略字符串中的escape定义某一个字符，而把其后的通配符当成一个普通的字符。

涉及空值null的查询

select *from student where sname= ''select *from student where ssex is nullselect *from student where sname is not null

当是空值的时候只能用is来判断，其他的都不可以.
null表示什么都没有，而''表示的是长度为零的字符串
null与任何类型的值进行算术运算的结果都为null

多重条件查询and、or

-and 的优先级比or高，但是（）可以改变优先级别

对查询结果进行排序

select *from SCwhere cno= '3' order by grade descselect * from SC order by sno,grade

asc 是升序，是默认值，desc是降序的。
order by a，b，c，d像这种按照多个字段进行的排序，先按照第一个字段排序，再按照第二个字段排序，以此类推，但是第二个字段的排序是在第一个的基础上进行的，它不会去打乱第一个排序的，第一个排序其实就已经分成组了，第二个排序是在第一个排序的各个小组里面进行的排序.
集函数（count、avg、sum、max、min）

select *from studentselect count(*)from studentselect count(all sno) numfrom studentselect count(distinct sno) numfrom student--select count(distinct *)from student--为什么*的时候不能加distinct？--select count(all *)from student--为什么*的时候不能加all？select avg(distinct sage)from student--必须是数字，非null值的加和除以非null值的个数select sum(distinct sage)from student--必须是数字，非null值的加和select max(distinct sno)from student--只要是可以比较大小的就可以，如果该列全是null，那么结果也是nullselect min(distinct sno)from student--只要是可以比较大小的就可以，如果该列全是null，那么结果也是null--对于这些集函数,dinstinct表示消除了重复的行，而默认缺损为all

分组查询group by
select cno from sc group by cno
分组之后，一组中只取一个值，而不会取出多个重复的值，哪怕没有distinct也是这样的，所以如果要加其他的列，这些列在一组中要保证只有一个值。所以每一组只会有一行。

select cno,sno from sc group by cno,sno--分组是不会排序的select cno,sno from sc group by cno,sno order by cnoselect cno,count(sno)from sc group by cnoselect sno,count(cno) cnoNum from sc group by sno having count(distinct cno)>=3select cno,count(sno)from sc where cno>1group by cno,sno having count(sno)>0 order by cno

执行的顺序是先分组，然后查询计算，然后在查询的结果中判断where和having条件，最后去排序.分组与不分组的区别是：不分组的时候，（如果是多表的话，先要去连接）先查询然后在查询结果中按照where子句的条件来筛选。而分组的时候，（如果是多表的话，先要去连接）是先分组，然后再执行查询，然后在查询结果中按照where子句和having子句的条件来筛选。Having后面的集函数是对每一最小组(按照group by后面的所有列进行的分组，是所有列而非某一列分组)的那些行进行运算的。另外，having后面也可以对某列进行非集函数的条件判断，这种判断也是针对每一最小组的那些行。

select top 10 a.sno,count(distinct cno) cno_num from student as a,sc as b where a.sno= b.sno and a.sno ='95001' group by a.sno having count(distinct cno)>= 2 order by a.sno

查询的执行步骤：先连接，然后按照条件去分组，然后按照组查询，先查询第一组（第一行），然后用where子句和having子句的条件来筛选，满足条件就保留，否则就舍去，然后查询第二行......，最后在查询出的结果中排序，然后在排好序的结果中去求top n。
多表连接查询

连接查询概述     

1、无条件连接
无条件连接其实就是广义笛卡尔乘积，即一个表（m行）中的每一行都与另外表（n行）中的每一行联合，最终构成了一个m*n行的一个表。
2、有条件连接
有条件连接顾名思义就是两个表之间的连接是有条件的，其中一个表的某一个字段与另外一个表中的某一个字段以某一种条件联系起来，连接的过程就是先扫面表1的 第一行，去与表二中的每一行对比，满足条件的就选择出来，然后拿表一中的第二行去做相同的操作，一直到最后完成。很显然，这个连接后的表其实是广义笛卡尔 乘积的子集。这种条件如果是不相等的话，那么就是非等值连接，反之就是等值连接。非等值连接用的比较少，主要是等值连接。
3、等值连接
当一个表的某一个字段与另外一个表中的某一个字段的联系条件是相等的时候，那么这种连 接就是等值连接。在等值连接中，如果消除了重复的列，那么这样的等值连接就是自然连接。在自然连接中，满足条件的才会被选出来成为新的行，不满足条件的是 不会被选择出来的。其实自然连接就是内连接。
4、外连接
在两个表之间，如果某一张表的行与另外一张表对应的时候，出现了根据条件去筛选另外一 张表的全部行，而前一张表仍有未对应的行，那么这些行是不会成为新表中的行的，但是如果想把这些没有对应的行显示出来，就要用到外连接了。如果表一和表二 连接，在表一中存在没有连接的行，表二中全部用NULL来补充，缺损的就是NULL，所以此时称为左外连接，反之则是右外连接。
左外连接：就是以左边的行为主，左边的全部都要显示出来，右边与之对应的要显示出来，没有与之对应的就补null。至于右边是否对应完了，不用考虑。
右外连接：就是以右边的行为主，右边的行要全部显示出来，左边与之对应的显示出来，没有对应的补null。至于左边是否对应完了，不用考虑。
4、外连接
在两个表之间，如果某一张表的行与另外一张表对应的时候，出现了根据条件去筛选另外一 张表的全部行，而前一张表仍有未对应的行，那么这些行是不会成为新表中的行的，但是如果想把这些没有对应的行显示出来，就要用到外连接了。如果表一和表二 连接，在表一中存在没有连接的行，表二中全部用NULL来补充，缺损的就是NULL，所以此时称为左外连接，反之则是右外连接。
左外连接：就是以左边的行为主，左边的全部都要显示出来，右边与之对应的要显示出来，没有与之对应的就补null。至于右边是否对应完了，不用考虑。
右外连接：就是以右边的行为主，右边的行要全部显示出来，左边与之对应的显示出来，没有对应的补null。至于左边是否对应完了，不用考虑。

连接查询的列名
对于查询时候的字段名字，某个字段如果在两个表中都是唯一的，没有重复的，那么就直接用字段名字，当然，如果用表名.列名更稳妥，但是建议只用字段名；对于那些在两个表中有相同的字段的，那么一定要用表名.列名，此时的列名一般就是按照例名来编号，这样显然不好，一般要用别名比较好，那么这样处理后在应用程序中引用的时候就非常方便，也不会出错了。
注意：只要使用了别名，显示出来的就是别名，而且应用程序中也是要通过这个别名来引用才不会出错的。使用函数的时候一般也要用到别名。
两张表以上的连接查询
对于两张表以上的内连接或者外连接，可以认为先让前面两张表连接起来，构成一个记录集，然后让把这个记录集当成新的表与后面的表相连接。
内连接查询实例
方法一：用 =号内连接

select student.*,sc.*,sc.Grade from student,sc where student.sno= sc.sno

表名.*表示的是选择了这个表中的所有列，所有列名都用了表名.列名

select student.*,course.* from student,sc,course where student.sno= sc.sno and sc.cno = course.cno

不管最终查询的列是否在某个表中，但是只要这个查询的条件中涉及到了这张表，那么就一定要在from中添加进来这张表
方法二：用 inner join内连接

select student.*,sc.*from student innerjoin sc on student.sno= sc.snoselect student.*,sc.*,course.*from student inner join sc on student.sno= sc.sno inner join course on sc.cno= course.cno

以上是内连接的第二种写法.这个连接的过程是前面两张表先连接好了，然后再来连接第三张表。连接的条件已经在from里面了．当然还可以用where来添加新的条件
select student.* ,sc.* from student inner join sc where  student.sno = sc.sno
select student.*,sc.*,course.* from student inner join sc inner join course where  student.sno = sc.sno and sc.cno = course.cno
以上的这两种写法是完全错误的，不能个这样写.

外连接查询实例

select student.*,sc.* from student left outer join SC on student.Sno= SC.Sno where ……select student.*,sc.* from student right outer join SC on student.Sno= SC.Snoselect student.*,sc.*,course.* from student left outer JOIN SC ON student.Sno= SC.Sno right outer join course on SC.cno = course.cno

内外混合连接查询实例

select student.*,sc.*,course.*from student left outer join SC on student.Sno= SC.Sno inner join course on SC.cno= course.cno

子查询作为主查询的条件
带IN谓词的子查询

select s1.*,s2.*from student as s1,student as s2 where  s1.sdept= s2.sdept and s1.sname ='刘呈'--与刘呈同系（单表连接）select s1.*from student as s1,studentas s2 where  s1.sdept= s2.sdeptand s2.sname ='刘呈'--与刘呈同系（单表连接）select *from studentwhere sdept in (select sdept from student where sname= '刘呈')--与刘呈同系select *from student where sdept in (select sdept from studentwhere sname= (select snamefrom studentwhere sno= '95002')select sno,snamefrom student where sno in (select snofrom sc where cno in (select cnofrom coursewhere cname = '信息系统'))and ssex = '女'

--选修了信息系统,而且性别为女

小结：
1、嵌套查询中的IN,表示某一个字段在某一个集合里面，集合中可能有多个也可能只有一个值，但是只会有一个字段
2、任何一个子查询都是select块，一定要加上（），否则就会报错.
3、任何一个子查询都不能用order by来排序，只能对最终的查询结果进行排序
4、子查询的结果只是作为父查询的某一个条件而已，还可以用AND,OR等追加更多的条件
带比较运算符(>,>=,<,<=,=,!=,<>)的子查询

select *from student where sdept = (select sdept from student where sname= '刘呈')

当确定了一个子查询的结果是单值的时候，父查询的查询条件就可以使用比较运算符，子查
询必须在比较运算符的后面，而不能是前面
exists谓词

if not exists(select sdept from student where sage>=30) select *from student if exists(select*from student where sage<30) select *from student

exists，not exists只能针对一个select语句，它返回的是一个逻辑值：true或者false，它主要应用于if语句的判断的，特别是在插入某张表之前来判断某条记录是否存在的，它还可以作为where判断条件

子查询作为主查询的列
case表达式作为主查询的列

select sno,cno,grade,(casewhen sno = (select sno from student where sname= 'james')then 'james'else'others' end)as namefrom sc  

上面这个查询是一个不相关子查询，即子查询与父查询之间没有关联，执行的步骤是先查询第一行的sno，cno，grade，case语句，然后查询第二行的sno，cno，grade，case，第三行......
case是一个表达式，它会返回单值

select sno,cno,grade,(casewhen sno = (select sno from student where sno= sc.sno)then 'one'else'others' end)as namefrom sc

上面这个查询是一个相关子查询，即子查询与父查询之间有关联，执行的步骤是先查询第一行的sno，cno，grade，case语句，然后查询第二行的sno，cno，grade，case，第三行......
case是一个表达式，它会返回单值
查询表达式作为主查询的列

select sno,cno,grade,(select sname from student where sno= sc.sno )as name from sc

当子查询的结果只有一行一列的时候，还可以直接将子查询作为主查询的列，子查询与父查询相关联
小结：以上两种查询的列都出现了特殊的列，而不是一个普通的列，这个特殊列就是一个表达式，这个表达式永远都只会返回一行一列，即返回单值。每查询一行都会执行一次特殊列表达式，得到一个列出来。子查询与主查询可能是关联的，也可能是非关联的。
子查询作为主查询的from对象

select a.sno,sname, grade from student a ,(select sno,cno,grade from sc) b where a.sno= b.sno  select count(*) from(select clsbdh,count(*)as su from  z_hgzzz group by clsbdh having count(*)> 1) a

在from中使用子查询，子查询的结果就是一个新的表，一定要给新表加一个别名，否则会出错。这种在from中使用子查询，是把子查询作为from的对象了，这种子查询与主查询是永远不会有关联的，否则就不要把子查询作为主查询的from对象了。
关联子查询
子查询的查询条件不依赖于父查询时，我们称之为不相关子查询，反之，则成为相关子查询。相关子查询只会发生在嵌套查询中，但是在嵌套查询中的一种情况下是不会发生相关子查询的，这就是子查询作为主查询的from对象的时候。

关联子查询的执行过程如下，每一个子查询被外部查询的每一行执行一次：

执行过程：先查询第一行，然后执行where的判断，如果满足条件，那么这一行就选择出来，否则就舍去。在这一行中，子查询中的outer.department_id就是父查询中这一行的department_id的值，然后查询第二行、第三行......

执行过程：先查询第一行，然后执行where的判断，如果满足条件，那么这一行就选择出来，否则就舍去。在这一行中，子查询中的e.employee_id就是父查询中这一行的employee_id的值，然后查询第二行、第三行......

执行过程：先查询第一行，然后执行where的判断，如果子查询查出来确实存在，那么exist就会返回true，那么这一行就选择出来，否则就舍去。在这一行中，子查询中的outer.employee_id就是父查询中这一行的employee_id的值，然后查询第二行、第三行......

拼sql字符串的查询
在存储过程中，有些时候，要动态的查询一些列，动态的变换条件，那么就要拼sql语句：

declare @sqlSelect as varchar(5000)    set @sqlSelect = 'select 1,1,1,null from #zwj1'

拼完之后，可以用exec命令去执行，但是执行结果是一个记录集，哪怕只有一个值也是记录集，执行的结果只能用一种方式取得，即定义一个与查询结果相同的表，然后将结果插入到表中，该表可以是临时表也可以不是，这个表一定要之前就定义好，列固定：

insert into zwj exec(@sqlSelect)

交叉查询

每个行列转换，都要经过三个逻辑处理阶段，扩展阶段是新增多个列，这样将导致新增了一些行；分组阶段将按照未转换的列分组；聚合阶段将对每个组进行处理，从而实现行的减少得到最终的结果。

以下是一张记录表，phaseType这一列只有四个值，phase1、phase2、phase3、phase4，现在要把phaseValue这一列分成phase1、phase2、phase3、phase4四列的形式显示出来：

那么就要交叉查询：

select dmcode,klcode,phaseType,case when phaseType='phase1'thenphasevalueelse 0endphase1 ,case when phaseType='phase2'thenphasevalueelse 0endphase2 ,case when phaseType='phase3'thenphasevalueelse 0endphase3,case when phaseType='phase4'thenphasevalueelse 0endphase4from PhaseInfo

查询结果如下：

对以上查询结果再进行一次转换：

select dmcode,klcode,sum(phase1) phase1,sum(phase2) phase2,sum(phase3) phase3,sum(phase4) phase4from (select dmcode,klcode,phaseType,case when phaseType='phase1'thenphasevalueelse 0endphase1 ,case when phaseType='phase2'thenphasevalueelse 0endphase2 ,case when phaseType='phase3'thenphasevalueelse 0endphase3,case when phaseType='phase4'thenphasevalueelse 0endphase4from PhaseInfo) Agroup by dmcode,klcodeorder by dmcode,klcode

查询结果如下：