常用sql查询

表名admin  字段名 adminId, adminName,adminPass,adminAge
表名class  字段名 classId,className,classInfo

 

select top 2 * from admin order by adminId desc  //返回最大的前两个

select top 2 * from admin order by adminId asc   //返回最小的前两个

select len(adminId) from admin  //返回adminid长度

select left(adminName,2) from admin

select * from admin where adminName is null  //查询adminName为空的所有

select charindex('1',adminName) from admin

select sum(adminAge) from admin //算出年龄的总数

select * from admin where adminName like '00%'

select REPLACE(adminName,'77','11') from admin //替换adminName 为77 的 替换成11 但不更改数据

select getdate()  //返回当前系统时间

select dateadd(dd,4,'1/1/99')

select power(3,2)  //返回3的倍数

select CURRENT_USER  //当前登陆用户名

select datalength('啊啊')  //返回字符串的长度

select USER_NAME(1)  //返回登陆的用户

update admin set adminPass=REPLACE(adminPass,'77',(select getdate()))  //将adminPass字段为“77”的 替换成当前时间

select adminId, adminPass FROM admin WHERE adminPass BETWEEN 60 AND 80   //列出adminPass字段 60到80 之间的搜有数值

select adminId,adminName,adminPass,adminAge FROM admin WHERE adminAge IN ('40')  //返回 adminpass 为40的所有数值

select "adminName为77的数值"=count(*) from admin where adminName='77'   //返回adminName为77 的个数

select count(*) from admin   //显示有多少数据

select "最小"=min(adminAge),"最大"=max(adminAge),"平均"=avg(adminAge) from admin

select max(adminAge),count(*) from admin group by adminId  //查询出每个班最大年龄学生的姓名

select * from admin compute max(adminAge) //查询出所有学生的年龄并且把最大的年龄显示在底部

select * from admin order by adminId asc compute max(adminAge), sum(adminAge) by adminId  //查询出每个班的学生的信息并把最大年龄显示在底部

select count(distinct adminName) from admin //查出所有学生来自几个地方

select adminName,className from admin,class  //多表查询

select admin.adminName,class.className,adminAge from admin join class on admin.adminId=class.classId

select st.student_name,cl.class_name from student as st join class as cl on st.class_id=cl.class_id join on st.student_id=sub.student_id

select adminName from admin union  select className from class

delete from admin where adminAge='47'  //删除

insert into admin(adminId,adminName,adminPass,adminAge)values('555','76','32','47') //添加

select adminName from admin where adminName=(select classId from class where className='aa') // 查询出所有aa的学生