高级查询:
- 函数的使用
1、显示employee表中运维工程师的姓名和基本工资
select '运维工程师'+姓名+'的基本工资'+CAST(基本工资 as varchar(10))+'元'
from employee
where 职务='运维工程师'
2、显示距离当前10天之后的日期和时间
select DATEADD(dd,10,getdate())
3、显示employee表中所有人的姓名和年龄
select 姓名,DATEDIFF(yy,出生日期,getdate()) as 年龄 from employee
4、显示employee表中所有90后员工的姓名和出生年份
select 姓名,DATEname(YEAR,出生日期) as 出生年份
from employee
where 出生日期 between '1990-1-1' and '1999-12-31'
5、查询employee表中所有员工基本工资的总和
select SUM(基本工资) as 总工资 from employee
6、查询employee表中所有员工平均基本工资
select avg(基本工资) as 平均工资 from employee
7、查询employee表中最高和最低的基本工资
select MAX(基本工资) as 最高工资,MIN(基本工资) as 最低工资 from employee
8、查询employee表中全部行数
select COUNT(*) as 总行数 from employee
9、查询employee表中90后员工的人数
select COUNT(出生日期) as '90后人数'
from employee
where 出生日期>='1990-1-1'
10、查询employee表中每个职务的平均工资
select 职务,AVG(基本工资) as 职务平均工资 from employee group by 职务
11、查询employee表中平均工资小于10000的职务
select 职务,AVG(基本工资) as 职务平均工资 from employee group by 职务
having AVG (基本工资)<10000
12、查询employee表中平均工资小于10000的职务,前提是员工段誉不计算在内
select 职务,AVG(基本工资) as 职务总工资 from employee
where 姓名 !='段誉'
group by 职务
having AVG (基本工资)<10000
13、查询employee表中所有人的平均工资,用CEILING()取整
select CEILING(avg(基本工资)) as 平均工资 from employee
14、查询未满30岁的员工的生日和年龄,并计算出距离30岁的天数,用字符串拼接显示结果
select 姓名,出生日期,DATEDIFF(yy,出生日期,GETDATE()) as 年龄, DATEDIFF(dd,getdate(),
dateadd(yy,30,出生日期)) as 距离岁天数
from employee
15. 将结果用字符串拼接
select
'员工'+姓名+
'生日是'+CONVERT (varchar(10),出生日期,111)+
',现在年龄是'+CAST(datediff (yy,出生日期,GETDATE ()) as varchar (10))+'岁'+
',距离岁生日还有'+
CAST(datediff (dd,getdate(),dateadd(yy,30,出生日期))as varchar (10))+'天'
from employee
where DATEDIFF (yy,出生日期,GETDATE())<=30
order by 出生日期
案例二:多表查询
15、在表A和表B中使用内联接查询学生姓名、学校和职业
select A.name 姓名,A.school 学校,b.name 姓名,b.job 职业
from A,B
where A.name=B.name
16、在表A和表B中使用左外联接查询学生姓名、学校和职业
SELECT A.name 姓名,A.school 学校,b.job 职业
FROM A LEFT JOIN B ON A.name=B.name
17、在表A和表B中使用右外联接查询学生姓名、学校和职业
SELECT A.name 姓名,A.school 学校,b.job 职业
FROM A RIGHT JOIN B ON A.name=B.name
18、在表A和表B中使用完整外联接查询学生姓名、学校和职业
SELECT A.name 姓名,A.school 学校,b.job 职业
FROM A FULL JOIN B ON A.name=B.name
案例三(使用函数查询统计信息)
1.查询出厂日期晚于2014年4月的水果信息..
select * from products where 出厂日期>2017-4-1 and 种类='水果'
2.分组查询所有水果,蔬菜,坚果的总成本
select 种类,sum(成本) as 总成本 from products group by 种类
3.查询所有水果的名称和出厂日期,以特定格式拼接字符串,如”西瓜的出厂日期是:2017/05/06”.
select 名称+'的出厂日期是:'+CAST(DATEPART(YEAR,出厂日期) as nvarchar(50))+'年'+CAST(DATEPART(MONTH,出厂日期) as nvarchar(50))+'月'+CAST(DATEPART(day,出厂日期) as nvarchar(50))+'日'
from products
where 种类='水果'
4.查询所有蔬菜的平均成绩
select 种类 ,AVG (成本) as 平均成本
from products
group by 种类
having 种类='蔬菜'
案例四(多表查询)
- 在products表和sales中使用内连接查询产品的,名称种类成本销售地点和销售价格
select products.名称,products.种类,products.成本,sales.名称,sales.销售价格,sales.销售地点
from products, sales
where products.名称=sales.名称
- 在products表和sales中使用内连接查询销往海南的和北京的,名称种类成本销售地点和销售价格,
select products.名称,products.种类,products.成本,sales.名称,sales.销售价格,sales.销售地点
from products, sales
where products.名称=sales.名称 and 销售地点='海南'
- 在products表和sales中使用内连接查询销往海南的和北京的,名称种类成本销售地点和销售价格,
select products.名称,products.种类,products.成本,sales.名称,sales.销售价格,sales.销售地点
from products, sales
where products.名称=sales.名称 and 销售地点='北京' and 种类='蔬菜'