select * from UserInfo 从userInfo表中查询所有字段select Username from UserInfo 从userInfo表中查询Username字段select Username,Pwd from UserInfo 从userInfo表中查询Username和Pwd俩字段select * from UserInfo where age>15 查询表中年龄大于15的数据select Username,Pwd from UserInfo where age>15 查询表中年龄大于15的用户名和密码select * from UserInfo where username='admin' and pwd=123 查询出用户名为admin 密码为123 的用户select age from UserInfo where username='admin' and pwd=123 查询出用户名为admin 密码为123 的年龄信息select * from UserInfo where sex='男' 查询出所有男生select * from UserInfo where sex='男' and country='上海' 查询出所有在上海的男生select * from UserInfo where age>30 and sex='女' and (country='上海' or country='北京') 查询所有在上海或者北京的女生，并且年龄在30之上select * from UserInfo where age>20 and age<50
select * from UserInfo where age between 20 and 50
查询出年龄大于20小于50之间的学生select COUNT(*) from UserInfo where country='上海' 查询出上海的学员总数select COUNT(*) as 总数 from UserInfo where country='上海' 起别名(给查询出来的列起了个名字)select MIN(age) from UserInfo where country='上海' 查询上海地区年龄最小的 (最大MAX)select SUM(age) from UserInfo where country='上海' 上海地区年龄总和select AVG(age) from UserInfo where country='上海' 上海地区平均年龄select * from UserInfo where age>(select AVG(age) from UserInfo where country='上海') 上海地区大于平均值的select * from UserInfo order by age asc 按照年龄升序排列(desc降序)select * from UserInfo where username like '%a%' 中间包含a
select * from UserInfo where username like 'a%' 以a开头
select * from UserInfo where username like '%a' 以a结尾select top(3) from UserInfo 前3条数据select COUNT(*) country from UserInfo group by country 给城市分组查询select COUNT(*) country from UserInfo group by country having COUNT(*)>2 分组查询之后的条件查询select distinct country from UserInfo 去掉重复select top 5 * from (
select ROW_NUMBER() over (order by userid) as rowNum,* from UserInfo
) e where rowNum>0 分页查询(0表示第一页，每页5条，通过userid查询)select top 5 * from (
select ROW_NUMBER() over (order by userid) as rowNum,* from UserInfo
) e where rowNum>0 and sex='男' 分页查询 男(0表示第一页，每页5条，通过userid查询)select userid,age,sex,
case
when age<30 then '大一'
when age>30 and age<40 then '大二'
when age>40 and age<50 then '大三'
else '大四'
end as classname from UserInfo 条件查询 类似switch case对null值进行设置，为空就以0填充select ISNULL(age,0) from UserInforselect GETDATE() 获取到年月日时分秒select YEAR(GETDATE()) 获取年select MONTH(GETDATE()) 获取月select DAY(GETDATE()) 获取日select DATEADD(yy,100,GETDATE()) 设置到未来的某个时间(100年)select DATEADD(mm,1,GETDATE()) 一个月之后select DATEADD(dd,10,GETDATE()) 10天之后select DATEDIFF(dd,GETDATE(),2019/12/12) 倒计时 到某个时间节点的update UserInfo set username='aa' and pwd='111' where userid=1 修改 id=1 里的username和密码delete UserInfo where userid=1 删除id=1的这条数据insert into UserInfo values('admin','111',11,'2019-08-07','男','null')添加一条数据，不需要的字段用nullinsert into UserInfo (username,pwd) values('admin123','222') 按需求添加字段内容打开表--userInfo -- 找到age字段 --右键-- check 约束 -- 添加 --右键填写表达式(check约束 比如年龄不能大于200这种)