sol函数初级查询,去重、分组、排序
create table student1 ( code int primary key, name varchar(10) unique, birthday datetime, sex varchar(5), height decimal(18,0) ) select*from student insert into student values (001,'向立凯','男' ,'1989/03/06',186) select *from student UPDATE student set birthday='1987/12/21',sex='男'where name ='董昌盛' --条件查询:where筛选:> < >= <= != and or select * from car where Oil<8 and powers>160 --模糊查询 like select *from car where name like '宝马3系%' and price<45 --排序 select *from car order by oil asc select *from car order by oil desc select *from car where name like '%宝马%' order by oil desc --去重 select distinct brand from car --分组 select brand from car group by brand select *From car --聚合函数:avg() max() min() sum() select avg(price) from car select avg(price) from car where name like '%宝马%' select max(price) 最贵,MIN(price) 最低 from car where name like '%宝马%' select COUNT(*) from car where name like '%宝马%' select brand,COUNT(*),AVG(price) from car group by brand --日期时间函数 select SYSDATETIME()--系统的时间 select GETDATE()--数据库服务的时间戳 select YEAR('1999-09-23') select MONTH('1999-09-23') select ISDATE('2015-06-30') select DATEADD(week,5,) select DATENAME(dayofyear,'2015-06-26') select DATEpart(weekday,'2015-06-26') --字符串函数 select upper(pic) From car select LTRIM(' 123213 ') select ' 12313' select LEFT('abcdef',3) select LEN('afdfasfd') select LOWER('AcDFdd') select REPLACE('ac123123123ac123123123','ac','haha') select REPLICATE('abc',10) select REVERSE('abc') select RIGHT('123123adf',3) select STR(1.567,3,2) select SUBSTRING('abcdefg',2,3) --数学函数:abs ceiling floor square sqrt round pi select code,name,ceiling(price) from car select *from car where name like '%奥迪%' and square(floor(price))>=2500 --转换函数 select code,name,'油耗'+cast(oil as varchar(20)) from car select code,name,'油耗'+convert(varchar(20),oil) from car