create database xinlianxi
go
use xinlianxi
go
create table student
(
code int,
name varchar(10),
sex varchar(10),
age int,
yunwen decimal(18,2),
shuxue decimal(18,2),
yingyu decimal(18,2),
cid int,
banji varchar(10)
)
go
insert into student values(1001,'鲁鲁修','男',21,75,88,51,3636,'一班')
insert into student values(1002,'CC', '女',18,70,77,76,3637,'一班')
insert into student values(1003,'曹焱兵','男',20,33,88,65,3666,'一班')
insert into student values(1004,'曹玄亮','男',16,44,96,99,3676,'一班')
insert into student values(1005,'罗宾', '女',23,75,34,78,3686,'一班')
insert into student values(1006,'路飞', '男',24,66,65,33,3612,'二班')
insert into student values(1007,'爱德华','男',21,76,84,54,3634,'二班')
insert into student values(1008,'娜娜丽','女',19,44,86,53,3653,'二班')
insert into student values(1009,'露琪亚','女',31,54,43,57,3654,'二班')
insert into student values(1010,'鸣人', '男',17,65,65,65,3677,'二班')
insert into student values(1011,'佐助', '男',23,76,32,35,3699,'二班')
go
select * from student
--查看分数超过30并且人数超过2个人的班级以及班级及格人数
select banji ,count(*)from student where age>20 group by banji having count (*)>3
select banji ,COUNT(*) from student where yunwen >20 group by banji having COUNT(*)>2 order by COUNT(*)
select name,sex,cid from student where banji='二班'
update student set name='漩涡鸣人' where code=1010
select *from student where code like('%2%')
select yunwen as '语文'from student order by yunwen desc
select top 6 shuxue as 数学 from student order by shuxue desc
select AVG(yunwen) from student where age>20
select count(shuxue) from student where sex='女'and banji='一班'
select count(*)from student where age>20
select max(yingyu) from student where banji='一班'
select min (shuxue) from student where banji='二班'
select sum(shuxue)from student where banji='一班'
select banji ,count(shuxue) from student group by banji
select charindex('def','abcdefhjikla')
select right('abcdefghiklmn',4)
select left('sajdhakjhskdjahsdja',5)
select dateadd(day,3,'2016-08-31')
select datediff(day,'2016-08-31','2015-08-31')