sql数据库第四课

use students
select * from students
insert into students (SName,Age,Sex,Createdate)
values ('刘1',22,1,DateAdd(day,-50,getdate()))

select * from students
where datediff(day,CreateDate,getdate())>=50

--select DateAdd(day,-50,getdate())

--通配符：_,%,[0-9],[^0-9]
--通配符必须配合like（模糊查询）关键字来使用
select * from students where SName like '张%'
select * from students where Address like '浙江%'

--范围查询between
select * from stumark where score between 60 and 80

select * from students where Age between 10 and 20

--in  关键字
select * from students where address in ('浙江金华','河北','')

select * from students where address not in ('浙江金华','')

--聚合函数
--sum:计算总和
select sum(score) as 总成绩 from stumark
select sum(score) as 总成绩 from stumark where courseID=1

--avg:计算平均分
select sum(score)as 总成绩,avg(score)as平均分 from stumark

--max, min:最大最小值
select sum(score)as 总成绩,avg(score)as 平均分,max(score)as 最大值,
min(score) as 最小值 from stumark

--count:计数
select sum(score)as 总成绩,avg(score)as 平均分,
max(score)as 最大值,min(score) as 最小值,
count (*) as 考试人数
from stumark where CourseID=1

--求学生信息表：平均年龄，男生总数，最大，最小年龄，学生人数
select avg(Age) as 平均年龄,sum(convert(int,Sex)) as 男生总数,
max(Age) as 最大年龄,min (Age) as 最小年龄,
count(*) as 学生人数
from students

转载于:https://www.cnblogs.com/xyb1117/archive/2012/03/05/2380043.html