赞
踩
目录
4,取左/右 边的字符 LEFT RIGHT('要截取的字符',截取个数)
5,去除出左/右边的空格 LTRIM RTRIM('字符')
6,替换REPLACE('字符串','需要替换的值','替换后的值')
7,截取SUBSTRING('字符','从第几位开始','到第几位结束')
8,找指定字符 CHARINDEX ('指定字符','字符串')
6,求男生人数大于1人以上的地方求所有的男生 根据地方进行分组并统计 筛选人数大于1人以上
1,inner join 内联 查询两张表中共有的数据, 默认内联可以省略inner
2,left join 左联 :以左边的表为基准,左表有的数据都会显示出来
3,right join 右联: 以右边的表为基准,右表有的数据都会显示出来
4,full join 全联:将左右两边表的数据全部展示出来
select len('哈哈abc')--返回5
select upper('abc')--返回ABC
select lower('ABC')--返回abc
- select left('abcdefg',2)--返回ab
- select right('abcdefg',2)--返回fg
- select len(ltrim(' abc'))
- select len(rtrim('abcd '))
select replace('我喜欢你','喜欢','like')--返回我like你
select SUBSTRING('我喜欢你',2,4)--返回喜欢你
select charindex('s','goodgoodstudy')--返回9
select getdate()--返回当前时间
- select year('2021-2-12')--返回2021
- select month('2021-2-12')--返回2
- select day('2021-2-12')--返回12
- select DATEPART(yy,'2021-12-3')--返回2021
- select DATEPART(mm,'2021-12-3')--返回12
- select DATEPART(dd,'2021-12-3')--返回3
- select DATEDIFF(yy,'2008-12-2','2021-12-4')
- select DATEDIFF(mm,'2018-12-2','2020-12-4')
- select DATEDIFF(dd,'2018-12-2','2020-12-4')
- select dateadd(yy,2,'2020-12-2')--返回2022-12-02 00:00:00.000
- select dateadd(mm,2,'2020-12-2')--返回2021-02-02 00:00:00.000
- select dateadd(dd,2,'2020-12-30')--返回2021-01-01 00:00:00.000
select dateName(DW,'2022-2-12')--返回星期六
select abs(-4)--返回4
select power(5,3)--返回125
select sqrt(9)--返回3
select floor(3234.9999)--返回3234
select ceiling(3234.00001)--返回33235
select round(234.2563,2)--返回234.2600
- select sign(0)--返回0
- select sign(8)--返回1
- select sign(-19)--返回-1
select datalength('哈哈abc')--返回7
- select CONVERT(int ,'123')
- select cast('123' as int)
返回结果集为单行单列
- --求全班总共有多少人
- select count(*) from student
- --求全班同学的年龄总和
- select sum(datediff(yy,SBirth,getdate())) from student
- --求全班同学的平均年龄
- select avg(datediff(yy,SBirth,getdate())) from student
- --求班上年龄最大的学生
- select max(datediff(yy,SBirth,getdate())) from student
- --求班上年龄最小的学生
- select min(datediff(yy,SBirth,getdate())) from student
特点: 必须与聚合函数搭档使用
group by 分组 having 分组后过滤
select后接列名的个数必须小于或等于group by 后接列的个数 除去聚合函数
select ssex ,count(*) from Student group by ssex
select ssex,count(*) from Student where sname like '张%' group by ssex
select saddress, count(*) from student group by saddress
select saddress,ssex, count(*) from student group by saddress,ssex
select SAddress,count(*) from student group by SAddress having count(*)>1
- select SAddress,count(*) from Student where ssex='男'
- group by SAddress
- having count(*)>1
- select a.SCode,a.SName,b.Course,b.Score from Student a
- join score b
- on a.SCode = b.StudentID
-
- select a.SCode,a.SName,b.Course,b.Score from Student a, score b
- where a.SCode = b.StudentID
- select a.SCode,a.SName,b.Course,b.Score from Student a
- left join score b
- on a.SCode = b.StudentID
- select a.SCode,a.SName,b.Course,b.Score from Student a
- right join score b
- on a.SCode = b.StudentID
- select a.SCode,a.SName,b.Course,b.Score from Student a
- full join score b
- on a.SCode = b.StudentID
- select a.sname,b.cname,c.score from a
- join c
- on a.sid = c.sidss
- join b
- on c.cid = b.cid
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。