1.多表连接
ANSI语法:
select *
from 表1 join 表2
on 表1.列=表2.列
ANSI语法:
select *
from 表1 join 表2
on 表1.列=表2.列
Sql Server语法:
select*
from 表1,表2......
where 表1.列=表2.列 and......
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'
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'
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'
select *
from sc
where sno='001'
c.查询刘鹏的所有考试信息
select*
from student join sc
on student.sno=sc.sno
where sname='刘鹏'
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
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
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'
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
from customers
2.分组和汇总
1)汇总(聚合函数)
select 函数名(列)
1)汇总(聚合函数)
select 函数名(列)
select avg(grade)
from sc
select max(grade)
from sc
select min(grade)
from sc
from sc
select max(grade)
from sc
select min(grade)
from sc
在select的后面如果用了聚合函数就不能出现列
null 不参与计数
null 不参与计数
select count(*)
from student
from student
select min(price)
from products
from products
select top 1*
from products
order by price
from products
order by price
select sum(price*stocks) '总价',max(price*stocks),min(price*stocks)
from products
from products
select distinct sno
from sc
from sc
select count(distinct sno)
from sc
where grade<60 or grade is null
from sc
where grade<60 or grade is null
count(distinct|all 列)
select avg(grade)
from sc
where cno='c101'
from sc
where cno='c101'
2)分组汇总
注意:
在select的后面可以跟聚合函数或group by后面指定的列
关键字:group by 列名1,列名2...
注意:
在select的后面可以跟聚合函数或group by后面指定的列
关键字:group by 列名1,列名2...
having 条件 --对分组汇总的结果筛选
where 条件 --对源表结果筛选
where>group by>having
where>group by>having
select avg(grade),cno
from sc
group by 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
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 avg(price),sum(stocks),categoryID
from products
where price>5
group by categoryID
having avg(price)>15
多重分组
select*
from products
where categoryID='1'
select*
from products
where categoryID='1'
select sum(stocks),categoryid,price
from products
group by categoryid,price
from products
group by categoryid,price
3)明细汇总(选)
compute 聚合函数
compute 聚合函数
order by 列
compute by 聚合函数
by 列
注:在by后面出现列的个数和顺序要和order by保持一致
select *
from sc
order by sno
compute avg(grade)
by sno
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
order by cno
compute avg(grade),sum(grade)
by cno
select*
from sc
compute avg(grade)
from sc
compute avg(grade)
4)嵌套查询
a.单值查询
如果是等号,后面必须是单个的值
select*
from student
where sdept=(
select sdept
from student
where sname='杜刚'
)
a.单值查询
如果是等号,后面必须是单个的值
select*
from student
where sdept=(
select sdept
from student
where sname='杜刚'
)
select*
from student
where ssex=(
select ssex
from student
where sname='王梅')
from student
where ssex=(
select ssex
from student
where sname='王梅')
查询和杜刚在同一个系的人的信息
select*
from student
where sname='杜刚'
select*
from student
where sname='杜刚'
select *
from student
where sdept='计算机科学与技术系'
from student
where sdept='计算机科学与技术系'
订单号为10249的顾客信息
select *
from customers join orders
on customers.customerID=orders.customerID
where orderid='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'
)
from customers
where customerid=(
select customerid
from orders
where orderid='10249'
)
c101考试成绩>平均分的学生的学号
select avg(grade)
from sc
where cno='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'
)
from sc
where cno='c101' and grade>(
select avg(grade)
from sc
where cno='c101'
)
b.单列多值
any,all,in
any,all,in
>any(大于里面任意一个值)
<>any(不等于any,)
=any
<>any(不等于any,)
=any
>all(表示和里面没一个值比较)
<>all
<>all
in =any(在其中一个就行了)
not in =<>all
not in =<>all
查询订单10249订购的产品信息
select productid
from orderDetails
where orderid='10249'
select productid
from orderDetails
where orderid='10249'
select*
from products
where productid=any(
select productid
from orderDetails
where orderid='10249'
)
from products
where productid=any(
select productid
from orderDetails
where orderid='10249'
)
select stocks
from products
where stocks>all(
from products
where stocks>all(
)
5).创建新表
select 列
into 新表
from 源表
select 列
into 新表
from 源表
select*
into girls
from student
where ssex='女'
into girls
from student
where ssex='女'
查询计科系学生的姓名,学号,年龄
select sname,sno,year(getdate())-year(sbrithday) '年龄'
into t1
from student
where sdept='计算机科学与技术系'
select sname,sno,year(getdate())-year(sbrithday) '年龄'
into t1
from student
where sdept='计算机科学与技术系'
select *
from t1
from t1
总结:
select [ALL|DISTINCT] [TOP n PERCENT]列
[INTO 新表]
from 表
[where 筛选条件]
[group by 分组列]
[having 筛选聚合条件]
[order by 派序列[ASC|DESC]]
select [ALL|DISTINCT] [TOP n PERCENT]列
[INTO 新表]
from 表
[where 筛选条件]
[group by 分组列]
[having 筛选聚合条件]
[order by 派序列[ASC|DESC]]