T-SQL 聚合

Coke


--  字符串聚合查询
SELECT SUM(c) from(
SELECT dbo.ToDecimalVal('12.3',0) c
UNION ALL
SELECT dbo.ToDecimalVal('11.3',0) c
) t

以创建Bank数据库为例子

Create database Bank
on primary
(
   NAME=Bank_data,   --数据库的逻辑名
   FILENAME='D:\Sql-Service-Demo\DB\Bank_data.mdf', --路径注意后面的后缀名
   size=1MB,                --数据库初始大小
   MAXSIZE=15MB,            --数据库最大大小
   FILEGROWTH=10%                --数据库增长速度
)
 log ON
(
  NAME=Bank_log,    --表示日志文件的逻辑名
  FILENAME='D:\Sql-Service-Demo\DB\Bank_log.ldf', --表示日志文件的物理文件名
  SIZE=1mb,  --表示日志文件的初始大小
  MAXSIZE=15mb,  --表示日志文件的最大大小
  FILEGROWTH=10%  --表示日志文件的增长速度
)

# 创建一个Product表

use Bank
go
--创建表
create table Product
(
   Id int identity(1,1) primary key not null, --表示种子，增量 第一个参数为从1开始 新增 1
   ProNo nvarchar(36),
   ProName nvarchar(36),
   TypeId int not null,
   Price decimal (18,2) default (0.00) not null, --默认值
)
go

--添加一列
alter table Product add PrdAdd nvarchar(36) null

--删除一列
alter table Product drop column PrdAdd

--修改一列
alter table Product alter column ProName nvarchar(36) not null

--修改列名执行存储过程 慎用
exec sp_rename 'Product.Count','ProNos','column'

--第一个参数表示要替换的数据库字段 第二个表示替换后的字段，第三个表示列号

一、查询 1.1升序

升序注意这里必须设置ID为自增字段

select User_id,Card_Number,Amount_Number from [User]

1.2降序

--降序

select User_id,Card_Number,Amount_Number from [User]
order by User_id desc

1.3条件查询

--条件查询

select User_id,Card_Number,Amount_Number from [User]
where User_id>=7

1.4设置别名

--设置别名

select User_id as 用户编号,Card_Number 用户卡号,密码=
Card_Password from [User]

1.5模糊查询

--模糊查询
-- % 0个或多个
-- _ 匹配单个字符
-- [] 范围匹配，括号中所有字符中的一个
-- [^] 不在括号中所以字符之内的单个字符

--1、 % 0个或多个  匹配任意类型和长度  效率不高
--1.1 like '%ad%' 包含于
select * from [User] where Amount_Number like '%0%'
--1.2 like '%42'  以匹配字符或字符串结尾
select * from [User] where Amount_Number like '%9'
--1.3 like 'w%'  以匹配字符或者字符串开头
select *from [User] where Amount_Number like '1%'

--2._ 匹配单个字符 限制表达式的字符长度
--2.1一个下划线代表一个字符 
select *from [User] where Amount_Number like '___' --三个下划线

--3.[] 范围匹配，括号中所有字符中的一个 |表示括号内的范围内 可不写
select *from [User] where Amount_Number like '1[8|1|0][0|1]'

--3.1[0-9]表示 指定范围 
select *from [User] where Amount_Number like '1[0-9][0-9]'

--4.[^] 不在括号中所以字符之内的单个字符
select *from [User] where Amount_Number like '1[^1-7][^1-9]'

参考图

1.6范围查询

--范围查询
--1、select from where 子句 条件 给定范围
--1.1 比较运算符 > <  >=  <=  <>
select * from [User] where User_id < 9

--1.2 in()   not in() --在这个范围内 和不在这个范围内
select * from [User] where User_id in (6,8)

--1.3不在这个范围内
select * from [User] where User_id not in (6,8)

--1.4 between and  等价于 >= and <=  效率高
select * from [User]
where Amount_Number between 100 and 200  --Amount_Number>=100 and Amount_Number<=200

百分比查询

--2、前面多少条、百分比
select top 10 * from [User] --查询前10条数据不够只有4条数据
select top 50 percent * from [User] --查询总数的百分之50数据

二、聚合函数

--聚合函数
-- count 记录个数
-- sum 求和
-- avg 求平均值
-- max 求最大值
-- min 求最小值
select COUNT(*)总和 from [User]  --一般统计一个表的记录数

-- sum 求和
select sum(User_id) from [User]  --只能用于数字类型

---- avg 求平均值
select avg(User_id) from [User]

-- max 求最大值
select max(User_id) from [User]

-- min 求最小值
select min(User_id) from [User]

三、多表查询 --1.1分组查询

--分组查询
--select ... where... group by 列名，列名
--select by 结合聚合函数，根据一列或多列对结果集 进行分组
select Uid,count(*) 用户数 from TokenInfo group by Uid
--分组筛选
select Uid,count(*) 用户数 from TokenInfo group by Uid
   having Uid>1  --Uid 大于1的用户总和几个
   order by Uid desc -- desc 降序排列

--1.2多表查询 内连接查询

--多表查询
--inner join 内连接查询
select Goods_id,u.Goods_Type,Type_Name
from GoodsInfo u
inner join GoodsType d on d.Type_id=u.Goods_Type

--隐式写法
select Goods_id,u.Goods_Type,Type_Name
from GoodsInfo u,GoodsType d
where d.Type_id=u.Goods_Type 

--1.3左连接

--外连接查询
--左连接 left join on 返回左表所有行，右表如果没有对应列显示null
select * from GoodsInfo u  --左表  数据全部返回
left join GoodsType d         --右表  没有对应返回null
on u.Goods_Type=d.Type_id

--1.4右连接

--右连接 right(outer) join on 与左连接相反，返回右表的所有行，左表进行匹配，左表中没有匹配上的对应列显示NULL
select * from GoodsInfo u  --左表  没有对应返回null
right outer join GoodsType d  --outer 可省略右表数据全部返回
on u.Goods_Type=d.Type_id

--1.5全连接

--全连接 full (outer) join 全外连接 
--返回左表和右表中所有，当没有对应返回 NULL
select * from GoodsInfo u  
full join GoodsType d         
on u.Goods_Type=d.Type_id

--1.6交叉连接 cross (outer) join

--交叉连接 cross (outer) join   等价于 inner join
--如果不带where子句式，返回被连接的两个表的迪卡尔积，返回的行数是两个表行数的乘积
--带where子句 ，等价于 inner join 返回匹配的数据
select * from GoodsInfo u  
cross join GoodsType d        --outer可以省略
where u.Goods_Type=d.Type_id   --显示匹配出来的行数

三、类型转换函数

--类型转换函数 convert (类型(长度),表达式)
select 'ac'+CONVERT(nvarchar,3)  --把数字转换成字符串 --ac3
select CONVERT(varchar(10),getdate(),126) --日期转换  --2020-07-27

--cast函数 cast(表达式 as 类型)
select 'ac'+cast(2 as nvarchar)  --ac2

四、字符串操作函数

--字符串操作函数 
--返回字符串中指定的字符串出现的开始位置
select CHARINDEX('c','acadas') --数据库索引从1开始
--返回字符串中指定的字符串出现的开始位置 字符串前后必须带%
select PATINDEX('%c%','aCadaasa') 

--大小写转换函数
select UPPER('aadaasa')  --转换为大写

select lower('ADACADA')  --转换为小写

五、取长度函数

--取长度函数
select len('ABC')
--去空格
select LTRIM('     aa   ') -- 去掉左边的空格
select rtrim('     aa   ') -- 去掉右边边的空格

六、取子串函数

--取子串函数
select LEFT('dacadsa',4) --daca--返回字符串从左边起4个字符
select RIGHT('dacadsa',4) --adsa --返回字符串从右边边起4个字符
select SUBSTRING('dacadsa',3,4) --返回字符串从左边第3个开始，取4个字符串 cads

select REPLICATE('da',4) --将指定字符串重复4次产生的字符串 dadadada

select reverse('ABCD') --字符串反转 --DCBA

select REPLACE('ABCD','A','aC')  --字符串的替换 --aCBCD

--指定字符串替换指定位置指定长度的字串后生成的字符串
select STUFF('ABCDEFG',1,3,'TT')  --TTDEFG

七、SQL server 数据库函数

SubString(表达式,起始,长度)：取子串
Right(表达式,长度) ：右边取子串
Str(浮点数[,总长度[,小数位]])：数值型转换字符型
LTrim(表达式)、RTrim(表达式)：去左、右空格
CharIndex(子串,母串)  ：返回子串起始位置
Convert(数据类型[(长度)],表达式[,日期转字符样式])：表达式类型转换
Cast(表达式 As 数据类型[(长度)])：表达式类型转换  
Abs(表达式)：取绝对值
Power(底,指数)：底的指数次方
Rand([整形数])；随机数产生器
Round(表达式,精度)：按精度四舍五入
Sqrt(表达式)：算术平方根
GetDate()：当前的日期和时间
Day(表达式)：表达式的日期值
Month(表达式)：表达式的月份值
Year(表达式)：表达式的年份值 
IsDate(表达式)：是否合理日期  
IsNULL(是否空值,替代值)：若空用指定值代替
IsNumeric(表达式)：是否为合理的数值
Exists(子查询)：检查结果集
Avg([Distinct 列名])：取均值
Count([Distinct 列名])：行数
Max([Distinct 列名])：最大值
Min([Distinct 列名])：最小值
Sum([Distinct 列名])：求和