SQL-Server中数据检索（高级查询）

1.多表连接
ANSI语法:
select *
from 表1 join 表2
on 表1.列=表2.列

Sql Server语法：
select*
from 表1,表2......
where 表1.列=表2.列 and......

select*
from student join sc
on student.sno=sc.sno
where sname='赵鹏'

select student.*
from sc join student
on sc.sno=student.sno
where cno='c101'

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

1)内连接
[inner] join
2)左外连接
left [outer] join
3)右外连接
right [outer] join
4)全连接(左右都显示)
full [outer] join
5)交叉连接
cross join
select *
from sc cross join student
注意：1.列名必须明确，重复的列名用表明来限定
      2.如果使用别名代替了表明，在连接时必须用别名
      3.默认内连接
      4.表名.*表示一张表的所有列

a.查询c101课程的学生的学号、姓名、出生日期
select sc.sno,sname,sbrithday
select student.*,grade
from student join sc
on student.sno=sc.sno
where cno='c101'

b.查询001同学的所有考试成绩
select *
from sc
where sno='001'

c.查询刘鹏的所有考试信息
select*
from student join sc
on student.sno=sc.sno
where sname='刘鹏'

d.查询选修了课程名为数据库的学生的学号和姓名
select sc.sno,sname
from student join sc
on student.sno=sc.sno
join course
on course.cno=sc.cno
where cname='数据库原理与应用'

e.查询每个学生的学号、姓名、选修课的课程名和成绩
select *
from student join sc
on student.sno=sc.sno
join course
on course.cno=sc.cno
f.查询每个学生的学号，姓名，选修的课程号和成绩
g.查询每个学生的选课情况
select *
from student left join sc
on student.sno=sc.sno
right join course
on course.cno=sc.cno

练习
1.查询orderid为10248的顾客id，顾客姓名，销售员id,销售员姓名
select orders.customerid,companyname,
       orders.saleid,salename

from orders join customers
on orders.customerid=customer.customers.customerid
join salers
on orders.saleid=salers.saleid
where orderid='10248'

2.显示所有顾客的信息和他们订购产品的订单id，销售员id和订购日期
select customers.*,orderid,saleid,orderdate
from customers left join orders
on customers.custormerid=orders.customerid

select *
from customers

2.分组和汇总
1)汇总(聚合函数)
select 函数名（列）

select avg(grade)
from sc
select max(grade)
from sc
select min(grade)
from sc

在select的后面如果用了聚合函数就不能出现列
null 不参与计数

select count(*)
from student

select min(price)
from products

select top 1*
from products
order by price

select sum(price*stocks) '总价',max(price*stocks),min(price*stocks)
from products

select distinct sno
from sc

select count(distinct sno)
from sc
where grade<60 or grade is null

count(distinct|all 列)

select avg(grade)
from sc
where cno='c101'

2)分组汇总
注意：
在select的后面可以跟聚合函数或group by后面指定的列
关键字：group by 列名1，列名2...

having 条件 --对分组汇总的结果筛选

where 条件 --对源表结果筛选
where>group by>having

select avg(grade),cno
from sc
group by cno

select avg(grade) '平均分',sum(grade),count(sno),sno
from sc
group by sno
having avg(grade)>80

及格的成绩中 每个人平均分大于80的
select avg(grade),sno
from sc
where grade>60
group by sno
having avg(grade)>80

sales数据库中，按照种类分组，对于所有价格》5而且平均价格》15的价格条目，列出这些产品的平均价格和总库率
select  avg(price),sum(stocks),categoryID
from products
where price>5
group by categoryID
having avg(price)>15

多重分组
select*
from products
where categoryID='1'

select sum(stocks),categoryid,price
from products
group by categoryid,price

3)明细汇总（选）
compute 聚合函数

order by 列
compute by 聚合函数
by 列
注：在by后面出现列的个数和顺序要和order by保持一致

select *
from sc
order by sno
compute avg(grade)
by sno

统计每门课程的总分和平均分，课程分和明细信息
select *
from sc
order by cno
compute avg(grade),sum(grade)
by cno

select*
from sc
compute avg(grade)

4)嵌套查询
a.单值查询
如果是等号,后面必须是单个的值
select*
from student
where sdept=(
select sdept
from student
where sname='杜刚'
)

select*
from student
where ssex=(
select ssex
from student
where sname='王梅')

 

查询和杜刚在同一个系的人的信息
select*
from student
where sname='杜刚'

select *
from student
where sdept='计算机科学与技术系'

订单号为10249的顾客信息
select *
from customers join orders
on customers.customerID=orders.customerID
where orderid='10249'

select*
from customers
where customerid=(
select customerid
from orders
where orderid='10249'
)

c101考试成绩>平均分的学生的学号
select avg(grade)
from sc
where cno='c101'

select sno
from sc
where cno='c101' and grade>(
select avg(grade)
from sc
where cno='c101'
)

b.单列多值
any,all,in

>any(大于里面任意一个值)
<>any(不等于any,)
=any

>all(表示和里面没一个值比较)
<>all

in  =any(在其中一个就行了)
not in =<>all

查询订单10249订购的产品信息
select productid
from orderDetails
where orderid='10249'

select*                                                                  
from products                            
where productid=any(                   
select productid                      
from orderDetails                       
where orderid='10249'                  
)                                        

select stocks
from products
where stocks>all(

)

5).创建新表
select 列
into 新表
from 源表

select*
into girls
from student
where ssex='女'

查询计科系学生的姓名，学号，年龄
select sname,sno,year(getdate())-year(sbrithday) '年龄'
into t1
from student
where sdept='计算机科学与技术系'

select *
from t1

 

总结：
select [ALL|DISTINCT] [TOP n PERCENT]列
[INTO 新表]
from 表
[where 筛选条件]
[group by 分组列]
[having 筛选聚合条件]
[order by 派序列[ASC|DESC]]