资源描述:
《SQL Server高级技巧》由会员上传分享,免费在线阅读,更多相关内容在教育资源-天天文库。
1、SQLServerT-SQL高级查询高级查询在数据库中用得是最频繁的,也是应用最广泛的。Ø基本常用查询--selectselect * from student; --all 查询所有select all sex from student; --distinct 过滤重复select distinct sex from student; --count 统计select count(*) from student;select count(sex) from student;select count(distinct sex) from student; -
2、-top 取前N条记录select top 3* from student; --alias column name列重命名select id as 编号,name '名称',sex性别 from student; --alias table name表重命名select id,name,s.id,s.name from students; --column 列运算select (age+id)col from student;select s.name+ '-' +c.name from classesc,students where s.cid=c.id
3、; --where 条件select * from student where id=2;select * from student where id>7;select * from student where id<3;select * from student where id<>3;select * from student where id>=3;select * from student where id<=5;select * from student where id!>3;select * from student where id!<5;
4、--and 并且select * from student where id>2 and sex=1; --or 或者select * from student where id=2 or sex=1; --between ... and ...相当于并且select * from student where id between 2 and 5;select * from student where id not between 2 and 5; --like 模糊查询select * from student where name like '%a%';
5、select * from student where name like '%[a][o]%';select * from student where name not like '%a%';select * from student where name like 'ja%';select * from student where name not like '%[j,n]%';select * from student where name like '%[j,n,a]%';select * from student where name like '
6、%[^ja,as,on]%';select * from student where name like '%[ja_on]%'; --in 子查询select * from student where id in (1,2); --not in 不在其中select * from student where id not in (1,2); --is null 是空select * from student where age is null; --is not null 不为空select * from student where age is not
7、null; --order by 排序select * from student order by name;select * from student order by name desc;select * from student order by name asc; --group by 分组按照年龄进行分组统计select count(age),age from student group by age;按照性别进行分组统计select count(*),sex from student group by sex;按照年龄和性别组合分组统计,并排序s
8、elect count(*),sex from st