Sql查询技巧:
select * from T_User where address=’邯郸’ and Name=’小泗’ and是和的意思 取交集
select * from T_User where address=’邯郸’ or Name=’小泗’ or 是或的意思 取并集
select * from T_User order by asc 这里 order by是为了排序 最后是asc是升序排列,Desc是降序排列;
select Top 2*from T_User Top2 查询前两条
select * from T_User where Name Like ‘%泗%’ 模糊查询 :%泗% 小% _泗 小_
select * from T_User where Age in (27,21,19) 查询年龄为27,21,19的人
select * from T_User where Age between 19and27 查询年龄在19-27的人
select* from T_User where Name=’小泗’
union
select* from T_User where Age=15 union将两条语句合并取并集,这样的用法前提是几个字段的个数和顺序必须一样,数据类型必须一样;
select Round(AVG (numbers),1) fromtestgrounp; avg求平均数 遇到NULL时做0处理; Round(avg(3.222),1)取一位小数;
select SUM (numbers) from testgrounp; 求和
select count(Name) from T_User Name字段的行数;Null做空处理;
select MAX(Numbers) from T_User 球最大值
select MIN(Numbers) from T_User 求最小值 NULL看作不存在,是不算的
select name,SUM(number) numb from test group by(name) order by(numb)按姓名分组,求 各个名字后的number
--创建视图
create view v1 as
select top 3* from T_UserInfo WHERE Age>21
SELECT * FROM v1
UPDATE T_UserInfo SET Age=26 WHERE Name='姚宏波'
select * from T_UserInfo
UPDATE v1 SET Age=20 WHERE Name='李英哲'
DELETE FROM v1 WHERE Name='姚宏波'