SQL
2022-07-21 本文已影响0人
山猪打不过家猪
基础
1.执行顺序
1.from + 表
2.where + 条件
3.group by + 列
4.having + 筛选条件
5.order by 列
2.查询
1.查询最高的tradeMoney，他的店铺id和businessDate
#使用聚合
select MAX(tradeMoney),shopId,businessDate FROM [new_test].[dbo].[oprHotShop] group by shopId,businessDate
#使用排序
select top 1 * FROM [new_test].[dbo].[oprHotShop] order by tradeMoney desc
2.查询数据有多少条（伪造查询）
select count(1) from oprHotShop
SELECT count(1) FROM [MTGoods_Down].[dbo].[sycmRXDP_new] where businessDate>'20220701'
3.使用聚合查询最大，最小，和平均
select MAX(tradeMoney),MIN(tradeMoney),AVG(tradeMoney)
FROM [new_test].[dbo].[oprHotShop
where shopId =14 and tradeIndexCrc >0
- 不等于某个数字可以使用
<>，不等于Nullis not null
3.分组
group by 结合聚合函数，根据一列或者多列进行数据分组
having 用于分组过后的晒选，但是效率没有where高
/****** Script for SelectTopNRows command from SSMS ******/
SELECT businessDate,COUNT(1) FROM [MTGoods_Down].[dbo].[sycmRXDP_new] where businessDate>'20220701' group by businessDate having businessDate>'20220710' order by businessDate desc
image.png
#1
select 商品名称,销售数量 = SUM(销售数量) from MyGoods group by 商品名称 order by 销售数量 desc
#2
select 商品名称,销售总额 = SUM(销售数量*单价) from MyGoods group by 商品名称 having SUM(销售数量*单价) > 50000 order by 销售总额 desc
#3
select 购买人,销售总数量 = SUM(销售数量) from MyGoods where 商品名称 = '可口可乐' group by 购买人 order by 销售总数量 desc
4.union联合查询
将表变成了横表，将多个表的结果，变成一个表来显示
注意：列数要相同，列的类型要统一，如果不统一需要使用convert(类型，字段)转换，union（去重），union all（不去重）
select payAmt '销售额',shopId '店铺Id', shopName '店铺名称' FROM [sycm].[dbo].[oprCurrentInfo]
union
select tradeIndex,shopId,shopTitle FROM [sycm].[dbo].[sycmComRank] where inTime<'2018-08-07' order by '销售额' desc
image.png
5.复制数据
1.把现有的表的数据插入到新表，为表建备份
select * into newStudent from student
注意：newStudent不用手动建立，会自动创建
2.复制一张相同表结构的新表
select * into astrStudent1 from student where 1 =0
3.拷贝表的结构，效率更加高的方式
select top 0 * into astrStudent2 from student
6.时间差
1.输出所有数据中通话之间最长的5条记录
select top 5 from CallRecords
order by DATADIFF(SECOND,StartDateTime,EndDateTime) desc
2.输出所有数据中心通话时间最长的5条记录
select 总时长=SUM(DATADIFF(SECOND,StartDateTime,EndDateTime) ) from CallRecords where TelNum like '0%'
7.case-end
- 注意必须写成
1000<xx and xx<10000这种形式
SELECT
[shopTitle]
,[shop_tradeIndex]
,[businessDate],
销售情况= (
case
when shop_tradeIndex> 10000 then '优秀'
when shop_tradeIndex <1000 then '差'
when 100<shop_tradeIndex and shop_tradeIndex <10000 then '普通'
else '最强王者'
end
)
FROM [MTGoods_Down].[dbo].[oprJKJDDown] where businessDate = '20220701' order by shop_tradeIndex
image.png
8.分页查询
方法一：
每页7条数据，查 第三页的数据
select top 7 * from student where tsid not in (select top (2*7) tsid from student )
方法二：
id无序,分页
selec 编号=ROW_NUMBER()over(order by tsid), * from student
- 每页6条，第四页
select * from (select 编号=ROW_NUMBER() over(order by id), * from MyOrders ) as ts where ts.编号 between (4-1)*6+1 and 4*6
9.over
- 名次
select tsmath,名次=rank()over(order by tsmath
desc) from TblScorce
image.png
- 分区
select id, 商品名称,行号 = ROW_NUMBER()over(partition by 商品名称 order by id asc) from MyOrders
image.png
10.变量
-局部变量@
数据库声明变量 declare @变量名 类型
如果想赋值，可以直接=赋值，也可使用set
declare @name nvarchar(10)
set @name = 'fxx'
select @name +'dadada'
- 全局变量
@@
@@ERROR
- 判断
declare @name nvarchar(10)
set @name = 'fxx'
if(@name = 'fxx')
begin
print 'yes'
end
else
begin
print 'no'
end
11.while
begin相当于c#中的{
end相当于c#中的}
declare @num int=0,@i int =0
while(@i<=100)
begin
set @num = @num+@i;
set @i = @i+1;
end
select @num
12.事务
成功commit 失败 rollback
BEGIN TRANSACTION
declare @sumError int =0
update bank set balance-1000 where cid = '001'
set @sumError = @sumError+@@ERROR
update bank set balance = balance +1000 where cid = '002'
set @sumError = @sumError+@@ERROR
if(@sumError<>0)
begin
ROLLBACK TRANSACTION
end
else
begin
COMMIT TRANSACTION
end
13.存储过程
- 例1
传参方式
#1
declare @n1 int=10,@n2 int =20
exec usp_2add @num1 = @n1, @num2=@n2
#2
exec usp_2add 10,20
- 用存储过程计算两个数的差
#先创建存储过程
create proc usp_2add
@num1 int,
@num2 int,
@result int output
as
begin
set @result = @num1+@num2
end
#使用
declare @s int=0
exec usp_2add 5,2,@s output
select @s
print @s
#删除
DROP PROCEDURE usp_2add
- 例2
模糊查询，用户传入 姓和年龄 返回来有多少数据，并把数据显示出来（姓张 20岁的人的信息）
create proc usp_mhcx
@name nvarchar(10),
@age int,
@count int output
as
begin
--条数
set @count =(select COUNT(1) from tblstudent where tsname like @name +'%' and tsage >@age)
select *from tblstudent where tsname like @name +'%' and tsage>@age
end
declare @ct int
exec usp_mhcx '张',20,@ct output
- 例3
①需要3个参数②参数1：及格分数线：65③参数2：每次提分2分④参数3：循环提分的次数output ⑤要求提分后，不及格人数必须小于总人数的一半
解析：班里及格人数>总人数1/2 ，①获取班里总人数②及格人数是多少人 ③提分 ④直到满足标准
create proc usp_tf
@scoreLine int = 60,
@addScore int=2,
@ct int output
as
begin
declare @ct int = 0
--总人数
declare @sumPerson int = (select COUNT(1) from tblscore)
--不及格的人
declare @under_score int = (select COUNT(1) from tblscore where tmath<@scoreLine)
--判断
while(@under_score >@sumPerson/2)
begin
update tblscore set tmath = tmath +@addScore --提分
--再次获取不及格人数
set @under_score int = (select COUNT(1) from tblscore where tmath<@scoreLine)
set @ct= @ct+1
end
end
declare @c int
exec usp_tf 200,2,@c output
select @c
- 例4
分页存储过程
第几页，每页多少条,总页数返回来
create proc usp_pageStudent
@page int, --页数
@count int, --条数
@sumPage int outPut --总页数
as
begin
set @sumPag=(CEILING((select COUNT(1) from tblstudent)*1.0/@count))--向上取整总页数
select * from
(select 编号= ROW_NUMBER() over (order by tsid),* from tblstudent)as tstu
where tstu.编号 between(@page-1)*@count+1 and @page*@count
end
declare @t int
exec usp_pageStudent 4,6,@t output
select @t
14.索引
15.游标
image.png
image.png
内连接
表名+inner join 表名+on+等值条件+where
使用比较运算符 进行表之间的比较，查询与条件相匹配的数据，等值链接。如果没有匹配到就无结果，切查询出的另外yi
- 显式写法
SELECT * FROM [MTGoods_Down].[dbo].[sycmRXDP_new] r inner join [MTGoods_Down].[dbo].shop s on r.shopId = s.erpId where businessDate= '20220701' and cateId = 30
- 隐式写法
SELECT * FROM [MTGoods_Down].[dbo].[sycmRXDP_new] r ,[MTGoods_Down].[dbo].shop s where businessDate= '20220701' and cateId = 30 and r.shopId = s.erpId
SELECT s.shop_title,r.shopId FROM [MTGoods_Down].[dbo].[sycmRXDP_new] r ,[MTGoods_Down].[dbo].shop s where businessDate= '20220701' and cateId = 30 and r.shopId = s.erpId
外连接
左连接左边全，
右连接右边全，
左连接
表左+ left join +表右+ on，返回左边的所有行，如果右表没匹配上，那么就显示null，右表显示的行数以左表为基准，没有的就为空
SELECT * FROM [MTGoods_Down].[dbo].[sycmRXDP_new] r left join [MTGoods_Down].[dbo].shop s on r.shopId = s.erpId where businessDate= '20220701' and cateId = 30
image.png
右连接
表左+ right join +表右+ on，与左连接刚好相反
image.png
类型转换
convert(需要转的类型（长度），表达式)
convert(varchar(10),2)
cast(表达式 as 类型)
cast(2 as varchar)
注意：cast不可以转换日期
索引（需补充）
什么是索引：类似于书的目录
索引的作用：提高数据查询，处理速度
索引的缺点：占存储空间，太多并不能提高系统性能
索引分类：聚集索引（主键索引），非聚集索引（唯一索引）
视图
什么是视图：虚拟表，由一个或者多个查询而定义，将查询的定义保存起来，实际不包含数据。
作用：简化查询，尤其是多表连接
缺点：并不能提高速度，增加了维护成本
分类：标准视图，索引视图，分区视图
连接C#
首先安装SqlConnection
image.png
namespace test2
{
class Program
{
static void Main(string[] args)
{
#region 连接数据库
//连接本地服务器的new_test表
/* string conn_setting = "server=127.0.0.1;uid=sa;pwd=shangxi;database=new_test";
SqlConnection conn1 = new SqlConnection(conn_setting);
conn1.Open();//打开链接
Console.WriteLine("打开数据库");
Thread.Sleep(1000);
conn1.Close(); //关闭链接
conn1.Dispose();//关闭管道
Console.WriteLine("数据库关闭了");*/
#endregion
#region 使用
/*
string conn_setting = "server=127.0.0.1;uid=sa;pwd=shangxi;database=new_test";
SqlConnection _01_conn = new SqlConnection(conn_setting);
try
{
//创建一个sql 命令对象
SqlCommand _01_cmd = new SqlCommand();
//给命令对象指定链接对象
_01_cmd.Connection = _01_conn;
_01_conn.Open();//一定要在执行命令前打开
_01_cmd.CommandText = ""; //
_01_cmd.ExecuteNonQuery();
}
finally
{
_01_conn.Close(); //关闭连接
_01_conn.Dispose();//关闭管道
Console.WriteLine("数据库关闭了");
}
*/
#endregion
#region 语法糖自动释放
/* string conn_setting = "server=127.0.0.1;uid=sa;pwd=shangxi;database=new_test";
using (SqlConnection _01_conn = new SqlConnection(conn_setting))
{
//创建一个sql 命令对象
SqlCommand _01_cmd = new SqlCommand();
//给命令对象指定链接对象
_01_cmd.Connection = _01_conn;
_01_conn.Open();//一定要在执行命令前打开
_01_cmd.CommandText = ""; //语句
_01_cmd.ExecuteNonQuery();//执行
}*/
#endregion
#region 连接池
string conn_pool = "Data Source = 127.0.0.1;Initial Catalog = new_test;User ID =sa; Password=shangxi;Pooling=true;Min Pool Size=4";
#endregion
}
}
}
C#l链接数据库的坑
1.如果数据库中 某列的值是float 小数 c#中 double
2.关于 null c#中的Null 不是一个
3.bit类型的数据 在C#中什么显示 true false
