sqlclass05.sql

SQL高级技巧

USE STUDENT
SELECT * FROM STUDENTMESSAGE WHERE NOT ( PHONE  IS  NULL)

SELECT
   [NAME] AS STUDENTNAME,    --
   [SEX] AS [STUDENT SEX]        --
FROM
     STUDENTMESSAGE 
WHERE 
     BIRTHDAY >= '1982-3-1' AND BIRTHDAY<='1999-5-5'


select * from studentmessage where remark not in 
(select city from city where  grade =1 )

select * from student where result in(select max(result) from student)

select class,bb 
from (select class,avg(result) as bb from student group by class) as aaa
 where bb>=83

select class,avg(result) as bb from student group by class having avg(result)>83

select * from student
select * from class
select 
     [id], [name],[classname]
 from
      student,class
 where 
       student.class=class.[classid]

select 
     s.[id],
     s.[name],
     s.[manager],
     c.[name],
     m.[name],
     m.sex
 from
      student as s  
      right join class as c on s.class=c.[classid]
      full join manager as m on s.manager=m.[id]