ms sql server 2005 select 用法大全

表信息：

数据库一中的表：

Student表：

数据库2中的表：

Student表：

 

Class表：

 

 

--查询不等信息
select * from Student where S_Address<>'北京';
--查询相等信息
select * from Student where S_Address='北京';
--IN 查询
select * from Student where S_Address IN('天津','北京');
--top查询
select top 3 * from Student
select top 60 percent * from Student
--DISTINCT 查询
select distinct S_Address from Student
--or查询
select * from Student where S_Address='天津' or S_Address='北京';
--and 查询
select * from Student where S_Address='北京' and S_Name='李云龙';
--and or 查询
select * from Student where (S_Address='北京' or S_Address='天津')
or S_Name='天策';
--order by 排序
select * from Student order by S_ID asc  --升序
select * from Student order by S_ID desc --降序
--< 查询
select * from Student where S_ID<3;
-- > 查询
select * from Student where S_ID>3

--多表连接查询
select * from Student 
inner join Class
on Student.StudentClass=Class.ClassID
--< 查询
select * from Student where S_ID<3;
-- > 查询
select * from Student where S_ID>3
-- between and 查询
select *from Student where S_ID between 1 AND 3
--LIKE 模糊查询
select * from Student where love like 'm%'
select * from Student where love like '%';
select * from Student where love like 'music';
select * from Student where love like '_usic';
select * from Student where love like '_u_ic';
select * from Student where love like '[m运]%'
select * from Student where love like '[!m]%'
--日期相关查询

select * from Student where StudentBirthday='1990-09-12';

select * from Student where StudentBirthday between '1990-09-12' and '2013-01-01';

select * from Student where StudentBirthday>='1990-09-12'

--查询非空信息
select * from Student where StudentBirthday is not null;

 

--转换为大写
select UPPER(ClassName) as ClassName,ClassID from Class;
--转换为小写
select lower(ClassName) as ClassName,ClassID FROM Class;
--去除字符串中的空格
select ClassID,ltrim(ClassName) as 去除左边空格,RTRIM(ClassName) as 去除右边空格 from Class;

--提取子串
select  ClassID,ClassName,left(ClassName,2) as ClassName左边提取2个,
right(ClassName,2) as ClassName右边提取2个,
SUBSTRING(ClassName,2,2) as ClassName左边2的位置开始提起2个
from Class

--charindex 查询子串首次出现的位置
select ClassName,charindex('m',ClassName) as m首次出现的位置 from Class
--replace 查找并替换字符串
select ClassName,replace(ClassName,'math','数学') as 替换的ClassName from Class;

--stuff删除子串
--下面参数是指从第四的位置开始删除3个字符,删除后的位置用'm'替换
select ClassName,stuff(ClassName,4,3,'m') as 删除后 from Class
--str将数值数据转为字符串
select ClassID,'班级'+STR(ClassID) as 转换后 from Class

--year,month,day,获取日期中的年，月，日

select StudentBirthday,year(StudentBirthday) as 年,month(StudentBirthday) as 月,
day(StudentBirthday) as 日 from Student

----dateadd将日期递增
select StudentBirthday,DATEADD(YEAR,1,StudentBirthday) AS 年份 from Student
select StudentBirthday,DATEADD(month,1,StudentBirthday) AS 月份 from Student
select StudentBirthday,DATEADD(day,1,StudentBirthday) AS 日期 from Student

--datediff获取日期间隔
select StudentBirthday,datediff(year,StudentBirthday,'2013-10-04') as 间隔年数 from Student
select StudentBirthday,datediff(month,StudentBirthday,'2013-10-04') as 间隔月数 from Student
select StudentBirthday,datediff(day,StudentBirthday,'2013-10-04') as 间隔天数 from Student

--getdate()获取当前系统时间
select StudentBirthday,datediff(day,StudentBirthday,getdate()) as 间隔天数 from Student

--round四舍五入,保留2位小数
select StudentClass,round(StudentClass,2) as 四舍五入后 from Student

--floor取整
select StudentClass,floor(StudentClass) as 取整 from Student
--ceiling取整
select StudentClass,ceiling(StudentClass) as 取整 from Student
--数学函数
select sin(30) as 正弦值,cos(30) as 余弦值,tan(90) as 正切值

--声明变量，并求对数值
declare @vars float
set @vars=30
select str(@vars) as 原数值,convert(varchar,log(@vars)) as 自然对数

--求幂
declare @values int,@counter int
set @values=9
set @counter=2
select power(@values,@counter) AS 九的二次幂     
  
--求指数
declare @vars float
set @vars=10
select convert(varchar,EXP(@vars)) as 输出指数值
--rand()随机数
DECLARE @numbers smallint                   --声明一个变量
SET @numbers=1                              --设置变量的初值为1
--通过循环语句与RAND()函数的组合方式产生随机数
WHILE @numbers<4
  BEGIN
    select RAND(@numbers) AS 随机数        
    SET NOCOUNT ON
    SET @numbers=@numbers+1  --改变循环条件的值
    SET NOCOUNT OFF
  END
--sqrt求平方根
declare @val float
set @val=120
select sqrt(@val) as 开方
--sum求和
select SUM(StudentClass) AS 求和 from Student where StudentClass=1

--max求最大值
select max(ClassID) AS 最大值 from Class

--min求最小值
select min(ClassID) AS 最小值 from Class

--avg求平均值
select avg(ClassID) AS 平均值 from Class

--count求记录总数
select count(*) As 记录总数  from Student where StudentClass=1

--cast强制转换
SELECT CAST('456' AS INT) + 123 
AS 计算结果

--获取当前数据库时间戳
select @@DBTS as 当前数据库时间戳值

--获取当前数据库语言
select @@LANGUAGE as 语言名称
--获取当前数据库版本
select @@VERSION as 当前数据库版本
--获取注册表项的名称
SELECT @@SERVICENAME AS 注册表项的名称