select Sno as 學(xué)號(hào),Sname as 姓名 from student;
select Sno,Sname from student;

select * from student;

select Sname as 姓名,(2014-Sage) as 出生年 from student;
select Sname ,(2014-Sage) from student;

select distinct Sno as 選修了課程的學(xué)生學(xué)號(hào) from SC;
select distinct Sno from SC;

select Sname as 學(xué)生姓名 from student where Sdept='IS';

select Sname as 姓名,Sage as 年齡 from student where Sage<20;

select Sname as 姓名,Sdept as 系別,Sage as 年齡 from student where Sage between20 and 23;

select Sname as 姓名,Ssex as 性別 from student where Sdept='IS' or Sdept='CS';
select Sname as 姓名,Ssex as 性別 from student where Sdept in ('IS','CS');

select Sname as 姓名,Sage as 年齡 from student where Sdept !='IS'and Sdept!='CS';
select Sname as 姓名,Sage as 年齡 from student where Sdept not in('IS','MA');

select Sname as 姓名,Ssex as 性別 from student where Sname like '李%';

select Sno as 學(xué)號(hào),Sname as 姓名,Sdept as 系別 from student where Sno like'2002%';

select * from student where Sname not like'劉%';

select Cno as 課程號(hào),Cname as 課程名,Ccredit as 學(xué)分 from course where Cname like '%數(shù)據(jù)%';

select * from course where cname like '%數(shù)據(jù)%';包含數(shù)據(jù)的字符串 
select * from course where cname like '數(shù)據(jù)%';以數(shù)據(jù)開頭的字符串
select * from course where cname like '%數(shù)據(jù)'; 以數(shù)據(jù)結(jié)尾的字符串

select Cno as 課程號(hào),Cname as 課程名,Cpno from course where Cpno is null;

select Sno as 學(xué)號(hào),Cno as 課程號(hào),Grade as 成績 from SC where Grade is not null;

select Sno as 學(xué)號(hào),Grade as 成績 from SC where Cno=3 order by Grade desc;

select Sno as 學(xué)號(hào),Grade as 成績 from SC where Cno=3 order by Grade asc;

select count(*) as 學(xué)生總數(shù) from student;

select sum(Ccredit) as 所有課程總學(xué)分 from course;

select avg(Sage) as 平均年齡 from student;

select max(Grade) as 1號(hào)課程的最高分 from SC where Cno=1;

select Ssex as 性別,count(*) as 人數(shù) from student group by Ssex;

select Cno as 課程號(hào),avg(Grade) as 平均分 from SC group by Cno;

select Sno as 學(xué)號(hào) ,count(course.Cno) as 選修課程數(shù)
From SC,course
Where course.Cno=SC.Cno
Group by Sno
Having Count(course.Cno)>=3;

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

select Sno as 學(xué)號(hào) ,count(course.Cno) as 選修課程數(shù)
From SC,course
Where course.Cno=SC.Cno and course.Cno !=1
Group by Sno
Having Count(course.Cno)>=2;

Select Sno
from sc
Where sc.Grade<60
Group by Sno
Having count(Cno)>=2;

Select Cno,count(Sno)
From SC
Group by Cno
Having count(sno)>=2

select student.Sno as 學(xué)號(hào),course.Cno as 選修課號(hào),SC.Grade as 成績 
from student,course,SC 
where student.Sno=SC.Sno and course.Cno=SC.Cno ;

select SC.Sno as 學(xué)號(hào),
FIRST.Cname as 直接選修課,
SECOND.Cname as 間接選修課
from SC,
course as FIRST,
course as SECOND
where FIRST.Cno=SC.Cno
and FIRST.Cpno=SECOND.Cno;

select student.Sno as 學(xué)號(hào),
Sname as 姓名,
sc.Cno as 選修課程號(hào)
from student 
LEFT OUTER JOIN SC ON student.Sno=SC.Sno;

JOIN: 如果表中有至少一個(gè)匹配，則返回行
LEFT JOIN: 即使右表中沒有匹配，也從左表返回所有的行
RIGHT JOIN: 即使左表中沒有匹配，也從右表返回所有的行
FULL JOIN: 只要其中一個(gè)表中存在匹配，就返回行

UNION 操作符用于合并兩個(gè)或多個(gè) SELECT 語句的結(jié)果集。
請(qǐng)注意，UNION 內(nèi)部的 SELECT 語句必須擁有相同數(shù)量的列。列也必須擁有相似的數(shù)據(jù)類型。同時(shí)，每條 SELECT 語句中的列的順序必須相同。

select *
from student
where Sdept in (
 select Sdept
 from student
 where Sname='王敏'
);

select *
from student
where Sdept not in (
 select Sdept
 from student
 whereSname='王敏'
);

select student.Sno as 學(xué)號(hào), Sname as 姓名
from student,SC
where student.Sno=SC.Sno and Cno in (
 select Cno
 from course
 where Cname='信息系統(tǒng)'
)

select distinct student.Sno as 學(xué)號(hào), Sname as 姓名
from student,SC
where student.Sno=SC.Sno and Cno in (
 select Cno
 from SC,student
 where SC.Sno=student.Sno and student.Sno in (
 select Sno
 from student
 where student.Sname='劉晨'
 )
)

select *
from student
where Sdept=(
 select Sdept
 from student
 where Sname='王敏'
)

select Cno
from SC a
where Grade> (
 select min(Grade)
 from SC b
 where a.Cno=b.Cno
)

select Cno
from SC a
where Grade> (
 select avg(Grade)
 from SC b
 where a.Sno=b.Sno
)

select Sname as 姓名,Ssex as 性別, Sage as 年齡, Sdept as 所在系
from student
where Sage <(
 select Sage
 from student
 where Sdept='CS'
);

select Sname as 姓名, Sage as 年齡
from student
where Sdept<>'CS' and Sage <ALL (
 select Sage
 from student
 where Sdept='CS'
);

select Sname as 姓名
from student
where Exists (
 select *
 from SC
 where Cno=1 and Sno=Student.Sno
);

select *
from student
where student.Sdept='CS'
union
select *
from student
where student.Sage<=19;

Select *
from student,SC
where student.Sno=SC.Sno and SC.Cno=1
INTERSECT
Select *
from student
where student.Sage<=19;

select *
from student
where student.Sdept='SC'
EXCEPT
select *
from student
where student.Sage<=19;