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

 

转载于:https://www.cnblogs.com/wang-kaifeng/p/4942160.html