select 1000*0.1+10 as money
select *,address as '地址' from userinfo
select *,u.address from userinfo as u
select count(*) from userinfo--找最短的列就行统计
select count(1) from userinfo--引入常数列,并进行统计
select GETDATE()
select @@VERSION
select *,ROW_NUMBER() over(order by id desc)from userinfo
--显示最大值最小值
select max(UnitPrice)as max,min(UnitPrice) as min from Products
--选择按UnitPrice升序排序,同样的UnitPrice按照UnitsInStock升序排序
select * from Products order by UnitPrice asc,UnitsInStock asc
--选择UnitPrice最大前三名
select top 3 * from Products order by UnitPrice desc
--选取所有的UnitPrice,要求不要重复的
select distinct UnitPrice from Products order by UnitPrice asc
--选取CategoryID,UnitPrice,并且没有两行重复的,distinct对选取的左右列一起使用
select distinct CategoryID,UnitPrice from Products
--选择ProductName以ch开头的
select * from Products where ProductName like 'ch%'
--选择ProductName第二个字母是h的
select * from Products where ProductName like '_h%'
--选择ProductName中含有'的,两个单引号表示一个单引号
select * from Products where ProductName like '%''%'
--选择Address中含有数字的
select * from Suppliers where Address like '%[0-9]%'
--选择Address中含有中括号的
select * from Suppliers where Address like '%[[]%'
--选择Region为null的
select * from Suppliers where Region is null
--选择每个国家供应商个数
--只要用了group by,select 后面只能跟group by 后面的列和聚合函数
select Country,COUNT(1) as Count from Suppliers group by Country
--sql中int和字符串不能直接连接,需要转换成nvarchar类型
select CONVERT(nvarchar(32),SupplierID)+CompanyName from Suppliers
--cast 转换
select Cast(SupplierID as nvarchar(32))+CompanyName from Suppliers
--获取时间
select GETDATE()
--给当前时间加上一天
--参数定义:单位,个数,日期
select DATEADD(day,1,'2015-3-1')
--两个时间差
select DATEDIFF(month,'2015-5-1','2015-9-8')
select DATEDIFF(SECOND,'2015-5-1','2015-9-8')
--取时间的某一部分
select DATEPART(YEAR,'2015-6-5')
select DATEPART(MONTH,'2015-6-5')
select DATEPART(DAY,'2015-6-5')
--另外一种获得时间的某一部分
select YEAR('2015-8-9')
select month('2015-8-9')
select day('2015-8-9')
select * from Suppliers
--字符串大小写转换
select LOWER(CompanyName) from Suppliers
select UPPER(CompanyName) from Suppliers
--获得ASCII码
select ASCII('A')
--截取字符串
select LEFT('123456789',2)
select RIGHT('123456789',3)
--获取字符串长度
select LEN(N'123456789')
--获取数据长度
select DATALENGTH(N'123456789')--18
select DATALENGTH('123456789')--9
--删除空格
select LTRIM(' 1234 ')+'Hello'
select RTRIM(' 1234 ')+'Hello'
--CASE WHEN THEN用法一
select
*,
case delflag
when 0 then N'未删除'
when 1 then N'删除'
end as 删除状态
from
userinfo
--CASE WHEN THEN用法二
select
*,
case
when id<5 then N'小id'
when id<10 then N'中id'
else N'大id'
end as id范围
from userinfo
--ISNULL函数使用
select gender,ISNULL(gender,'true') from userinfo
--变量的使用
declare @i int
set @i=(select COUNT(1) from userinfo)
print @i
declare @i int
select @i=COUNT(1) from userinfo
print @i
--if else 的使用
declare @num int
select @num = COUNT(1) from userinfo where id<5
if @num<1
begin
print N'小于1'
end
else
begin
print N'大于1'
end
--while的使用
declare @i int
set @i=0
while(@i<=100)
begin
print @i;
set @i = @i+1
end
--子查询-将查询结果作为表
select T.id from
(select * from userinfo where id<5) as T
where T.id<3
--子查询-将查询结果作为表达式
select id from userinfo where id<
(select AVG(id) from userinfo)
--分页操作sql语句一-越过多少个取出多少个
select top 3 EmployeeID from Employees where EmployeeID not in
(
select top ((2-1)*3) EmployeeID from Employees order by EmployeeID
)
order by EmployeeID
--分页查询sql语句二-利用row_number()和开窗函数
select * from
(
select *,ROW_NUMBER() over( order by employeeID) as num from Employees
) as T
where T.num between 4 and 6
--开窗函数使用
select *,AVG(id) over() from userinfo