SQL

基础

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