字符串函数、数据类型转换函数、连接查询
字符串函数、数据类型转换函数、连接查询
1、字符串函数
--将身高的平均值转化为asc码
select ASCII(AVG(shengao))from sg
--将体重的最大值转化为字符型
select CHAR (MAX(tizhong))from sg
--返回一个字符串的四位soundex码
select SOUNDEX ('lskd')
--比较两个soundex码有几位相同
select DIFFERENCE ('dgghsf','dfs')
--打印空格
select SPACE (11)
select 'a'+SPACE(11)+'b'
--查找对应通配符字符段的符合条件的第一个首字母的索引
select PATINDEX ('%ds%','sdsfdsff')
--复制
select REPLICATE ('ssdd ',2)
--将float型截取之后转为字符型
select STR (2322.32,7,1)
--从指定位置删除指定长度的字符段后,再插入新的字符段
select STUFF ('jdsklfjsjl',4,3,'AAA')
--查询AS在HGDkgjadfjhgashASGHJGHgf中的位置
select CHARINDEX('AS','HGDkgjadfjhgashASGHJGHgf')
--截取开头位
select LEFT ('afsjflsfkldsks',3)
--截取结尾位
select RIGHT ('sdfkjdskkflsj',3)
--去掉结尾空格后,计算长度,返回整数
select LEN (' jdkfk jdsklfj ')
--大写转小写
select LOWER (' DdSDJ JDdK ')
--小写转大写
select UPPER (' sdj dDK kfd')
--去掉字符串前面的空格
select LTRIM (' jdf dk ')
--去掉字符串后面的空格
select RTRIM (' ds dd ')
--查找替换
select REPLACE ('sdjkfjkd','jk','111')
--反转
select REVERSE ('asdfsfgg')
--截取字符串
select SUBSTRING ('djfklsjdkl',6,2)
2、
--数据类型转换函数
select cast ('123' as int)
select convert (int , '123')
例：
select *from sg
--查询姓名、性别、出生日期（带年月日）
--substring
select xingming,xingbie,substring(cast(chushengriqi as varchar(20)),7,4)+'年'+
substring(cast(chushengriqi as varchar(20)),1,2)+'月'+
substring(cast(chushengriqi as varchar(20)),4,2)+'日' from sg
--年月日加转换类型拼接
select xingming,xingbie,CAST(year(chushengriqi) as varchar(20))+'年'+
CAST(month(chushengriqi) as varchar(20))+'月'+
CAST(day(chushengriqi) as varchar(20))+'日'from sg
3、连接查询
（1）join on
select score.sno,sname,score.cno,cnome,degree from score
join course on score.cno=course.cno
join student on student.sno=score.sno
（2）两表直接拼接，where建立条件
select score.sno,sname,cno,degree from score,student
where score.sno =student.sno
select teacher.tno ,tname,cnome from teacher
left join course on course.tno =teacher.tno
select teacher.tno ,tname,cnome from teacher
right join course on course.tno =teacher.tno
select teacher.tno ,tname,cnome from teacher
join course on course.tno =teacher.tno
