美高梅网投网站-美高梅手机网投-美高梅官方网站
做最好的网站

您的位置:美高梅网投网址 > 数据库 > 包含字段表达式,内容主要是对数据库的基本

包含字段表达式,内容主要是对数据库的基本

发布时间:2019-09-20 17:23编辑:数据库浏览(185)

    前言

    select语句
    语法:select distinct | top 数字 [percent] 字段1 as 别名 ,包含字段表达式,函数,常量
    from 表或结果集
    where 逻辑条件 | 模糊处理 | 范围处理 | null值处理
    group by 分组字段
    having 筛选条件
    order by 排序依据;

    本文是个人学习SQL Server 数据库时的以往笔记的整理,内容主要是对数据库的基本增删改查的SQL语句操作约束,视图,存储过程,触发器的基本了解。

    执行流程:
    from子句 -> where子句 ->group by子句 ->having子句 ->select子句 ->order by子句

    注:内容比较基础,适合入门者对SQL Server 数据库的了解!!!

    -- 名字
    -- 作用(例子)
    -- 语法

    正文

    -- 子查询
    -- 就是在一个查询中嵌套一个查询
    -- 一般作用就是利用多张表查询一个信息
    -- 例如查询"濮阳语儿"的成绩
    select * from TestDataBase..Student;
    select * from TestDataBase..Score;
    -- 在学生表中查得stuId,然后再到分数表中查询分数
    select stuId from TestDataBase..Student where stuName ='濮阳语儿';

    1.子查询

    select * from TestDataBase..Score where stuId = 5723;

    -- 外部查询
    select *
    from TestDataBase..Score
    where stuId in
    ( -- 子查询、内部查询
    select stuId from TestDataBase..Student where stuName ='濮阳语儿'
    );
    -- 外部查询(子查询)

    -- 将一个查询的结果作为另一个查询的条件

    -- 考试成绩与课程查出来 Course
    select * from TestDataBase..Course;

    select className from TestDataBase..Course where classId in
    (
    select top 1 classId from TestDataBase..Student where stuName='濮阳语儿'
    );

    -- 多个单值 外部查询 where 字段 in (子查询)
    select '濮阳语儿' , (select className from TestDataBase..Course where classId in
    (
    select top 1 classId from TestDataBase..Student where stuName='濮阳语儿'
    ));

    -- 表值 select * from (子查询) as 别名
    select * from (
    select stuName, case stuSex when 'f' then '女' else '男' end as stuSex, DATEDIFF(YEAR, stuBirthdate, GETDATE()) as stuAge from TestDataBase..Student where stuId <= 10
    ) as t
    where t.stuAge between 20 and 30;

    --
    -- 员工编号 基本工资 请假扣款 补贴 绩效奖金 项目奖金 社保扣款
    /*
    select
    来自员工表的查询
    , 来自工资级别表的查询
    , 考勤表的查询
    ... ...
    */

    -- 独立子查询(标量、多值)

    -- 相关子查询
    -- 查询濮阳语儿的三科平均分
    select AVG(testBase), AVG(testBeyond), AVG(testPro) from TestDataBase..Score where stuId = (select top 1 stuId from TestDataBase..Student where stuName='濮阳语儿');

    select
    stuName
    , (select AVG(TestBase) from TestDataBase..Score where stuId = t.stuId) as 基础平均分
    , (select AVG(testBeyond) from TestDataBase..Score where stuId = t.stuId) as 中级平均分
    , (select AVG(testPro) from TestDataBase..Score where stuId = t.stuId) as 高级平均分
    from
    TestDataBase..Student as t
    where
    stuName = '濮阳语儿';


    use HeiMa8;

    create table Score
    (
    学号 nvarchar(10),
    课程 nvarchar(10),
    成绩 int
    )

    insert into Score values('0001','语文',87);
    insert into Score values('0001','数学',79);
    insert into Score values('0001','英语',95);
    insert into Score values('0002','语文',69);
    insert into Score values('0002','数学',84);

    case表达式:
    --if-else结构
    case
    when 条件1 then 值1
    when 条件2 then 值2
    。。。
    else 值n
    end

    --switch-case结构
    case 字段
    when 匹配1 then 值1
    when 匹配2 then 值2
    。。。
    else 值n
    end
    Set statistics io on;--打开监视磁盘IO操作
    Set statistics time on;

    select * from Score;
    -- 分组
    select 学号, '语文', '数学', '英语' from Score group by 学号;
    --第一种结构示例:switch--case
    select
    学号
    , case when 课程='语文' then 成绩 else 0 end as '语文'
    , case when 课程='数学' then 成绩 else 0 end as '数学'
    , case when 课程='英语' then 成绩 else 0 end as '英语'

    --把一个查询结果作为另外一个查询的查询源
    select * from (select * from Student where tbage between 3 and 5)
    as ct where tbname=5 --ct是新创的表名

    from Score

    select
    学号
    , sum(case when 课程='语文' then 成绩 else 0 end) as '语文'
    , sum(case when 课程='数学' then 成绩 else 0 end) as '数学'
    , sum(case when 课程='英语' then 成绩 else 0 end) as '英语'
    from Score
    group by 学号;

    第二种结构示例:if--else
    select
    sum(case when T.充值金额>=500 then T.充值金额 end) as '鲸鱼用户'
    ,sum(case when T.充值金额>=100 and T.充值金额<500 then T.充值金额 end) as '海豚用户'
    ,sum(case when T.充值金额>=10 and T.充值金额<100 then T.充值金额 end) as '小鱼用户'
    from
    (
    select [ChannelUserKey] as 用户ID,sum(convert(float,[RechargeAmount])/100) as 充值金额,sum([RechargeCount]) as 充值用户
    from [dbo].[FactRecharge]
    where datekey>=20141201 and datekey<=20141210
    and ChannelKey=1
    group by [ChannelUserKey]
    ) T


    -- 透视变换

    select * from Score pivot(
    sum(成绩) for 课程 in (语文,数学,英语)
    ) as t


    -- 表连接
    -- 作用:将多张表变成一张表
    -- 用法与分类(案例)
    -- 分类:交叉连接、内连接、外连接

    create table joinPerson
    (
    pId int identity(1,1) not null
    , pName nvarchar(10) not null
    , titleId int null
    );
    alter table joinPerson
    add constraint PK_joinPerson_pId primary key(pId);

    create table joinTitle
    (
    titleId int identity(1,1) not null
    , titleName varchar(10) not null
    );
    alter table joinTitle
    add constraint PK_joinTitle_titleId primary key(titleId);

    insert into joinTitle(titleName) values('Teacher'),('Master');
    insert into joinPerson(pName, titleId) values('牛亮亮', 1),('苏坤', 2),('杨中科', NULL);

    select * from joinPerson;
    select * from joinTitle;

    select pName, titleName from joinPerson cross join joinTitle;
    -- 如果两章表中有重名的字段,就会出问题,就需要给表加别名
    select t1.pName, t2.titleName from joinPerson as t1 cross join joinTitle as t2;

    -- 内连接
    select
    *
    from
    joinPerson as t1
    inner join
    joinTitle as t2
    on t1.titleId = t2.titleId;

    -- 左外连接
    select
    *
    from
    joinPerson as t1
    left join
    joinTitle as t2
    on t1.titleId = t2.titleId;

    -- 右外连接
    insert into joinTitle(titleName) values('班主任');

    select
    *
    from
    joinPerson as t1
    right join
    joinTitle as t2
    on t1.titleId = t2.titleId;

    -- 全连接
    select
    *
    from
    joinPerson as t1
    full join
    joinTitle as t2
    on t1.titleId = t2.titleId;

    -- 表表达式
    -- 就是通过表与表的运算,得到一个结果集作为from后面的数据源
    -- 1、派生表 返回结果集的子查询
    -- 语法: select ... from (select 查询) as 别名;
    -- 注意: 不能使用游标
    -- 2、公用表表达式CTE
    -- 3、视图
    -- 4、内联表值函数

    -- 查询学生信息
    select * from
    TestDataBase..Student as t1
    inner join
    TestDataBase..Course as t2
    on t1.classId = t2.classId
    inner join
    TestDataBase..Score as t3
    on t1.stuId = t3.stuId
    where
    stuName = '濮阳语儿';


    select * from
    (
    select
    t1.stuId
    , t1.stuName
    , case t1.stuSex when 'f' then '女' else '男' end as stuSex
    , datediff(year, t1.stuBirthdate, GETDATE()) as stuAge
    , t1.stuEmail
    , t1.stuAddress
    , t1.stuPhone
    , t2.className
    , t3.testBase
    , t3.testBeyond
    , t3.testPro
    from
    TestDataBase..Student as t1
    inner join
    TestDataBase..Course as t2
    on t1.classId = t2.classId
    inner join
    TestDataBase..Score as t3
    on t1.stuId = t3.stuId
    ) as t
    where t.stuName = '濮阳语儿';


    --把另外一个查询的结果作为当前查询的条件来使用。
    --子查询中=、!= 、< 、> 、<= 、>=之后只能返回单个值,如果多个值就会报错
    --解决办法 可以用in 代替
    select * from Student
    where tbage in(select tbage from Student where tbname=3)

    -- 分页

    select * from TestDataBase..Student;
    -- 当前页数、每页显示的条数 10
    -- SQL Server 2005+ row_number() 可以为表生成一个连续的数字列
    -- 语法 row_number() over(order by 字段)
    -- select ROW_NUMBER() over(order by stuId), * from TestDataBase..Student

    select top 10 * from TestDataBase..Student;

    select top 10 * from TestDataBase..Student
    where stuId not in(select top 10 stuId from TestDataBase..Student);

    set statistics io on;
    set statistics time on;

    select top 10 * from TestDataBase..Student
    where stuId not in(select top ((100-1)*10) stuId from TestDataBase..Student);

    --
    select * from
    (
    select ROW_NUMBER() over(order by stuId) as num, * from TestDataBase..Student
    ) as t
    where
    t.num between 1 and 10;

    select * from
    (
    select ROW_NUMBER() over(order by stuId) as num, * from TestDataBase..Student
    ) as t
    where
    t.num between 21 and 30;
    /*
    select * from
    (
    select ROW_NUMBER() over(order by stuId) as num, * from TestDataBase..Student
    ) as t
    where
    t.num between (n-1) * m + 1 and n * m;
    */

    -- 公用表表达式(CTE)
    -- 语法
    /*
    with 别名
    as
    (
    结果集
    )
    使用别名的一个查询;
    */
    with t
    as
    (
    select
    t1.stuId
    , t1.stuName
    , case t1.stuSex when 'f' then '女' else '男' end as stuSex
    , datediff(year, t1.stuBirthdate, GETDATE()) as stuAge
    , t1.stuEmail
    , t1.stuAddress
    , t1.stuPhone
    , t2.className
    , t3.testBase
    , t3.testBeyond
    , t3.testPro
    from
    TestDataBase..Student as t1
    inner join
    TestDataBase..Course as t2
    on t1.classId = t2.classId
    inner join
    TestDataBase..Score as t3
    on t1.stuId = t3.stuId
    )
    select * from t where t.stuName = '濮阳语儿';

    -- t可以重用
    -- 自交差
    -- 生成一个数字表

    select num from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as t(num);

    -- 自交差 10000
    select
    t1.num * 10 + t2.num + 1
    from
    (select num from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as t(num)) as t1
    cross join
    (select num from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as t(num)) as t2
    ;
    -- 用公用表表达式
    with t
    as
    (
    select num from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as tt(num)
    )
    select
    t1.num * 1000 + t2.num * 100 + t3.num * 10 + t4.num + 1 as orderId
    from
    t as t1
    cross join
    t as t2
    cross join
    t as t3
    cross join
    t as t4
    order by
    orderId;

    select * from HeiMa8..AreaFull as t1 inner join HeiMa8..AreaFull as t2 on t1.AreaPid = t2.AreaId ;

    --
    -- 在需要频繁的操作一些表表达式的时候
    -- 视图和内联表值函数

    -- 视图
    -- 就是将查询的语句封装成一个对象,每次查询的时候直接操作这个对象即可
    -- 虚拟表
    -- 使用派生表
    select * from
    (
    select
    t1.stuId
    , t1.stuName
    , case t1.stuSex when 'f' then '女' else '男' end as stuSex
    , datediff(year, t1.stuBirthdate, GETDATE()) as stuAge
    , t1.stuEmail
    , t1.stuAddress
    , t1.stuPhone
    , t2.className
    , t3.testBase
    , t3.testBeyond
    , t3.testPro
    from
    TestDataBase..Student as t1
    inner join
    TestDataBase..Course as t2
    on t1.classId = t2.classId
    inner join
    TestDataBase..Score as t3
    on t1.stuId = t3.stuId
    ) as t
    where t.stuName = '濮阳语儿';

    -- 创建视图
    -- 语法:
    /*
    create view vw_视图名
    as
    select语句
    ;
    */

    use TestDataBase;
    go
    create view vw_StuInfo
    as
    select
    ROW_NUMBER() over(order by t1.stuId) as n
    , t1.stuId
    , t1.stuName
    , case t1.stuSex when 'f' then '女' else '男' end as stuSex
    , datediff(year, t1.stuBirthdate, GETDATE()) as stuAge
    , t1.stuEmail
    , t1.stuAddress
    , t1.stuPhone
    , t2.className
    , t3.testBase
    , t3.testBeyond
    , t3.testPro
    from
    TestDataBase..Student as t1
    inner join
    TestDataBase..Course as t2
    on t1.classId = t2.classId
    inner join
    TestDataBase..Score as t3
    on t1.stuId = t3.stuId
    ;
    go

    -- 虚拟的表
    select * from vw_StuInfo where stuName='濮阳语儿';

    select * from vw_StuInfo where stuId = 304;

    update Testdatabase..Student set stuName = '嘉嘉' where stuId=304;

    --
    -- 视图可以更新数据,但是不建议更新和增加以及删除
    -- 连接多张表、视图并没有显式所有的字段

    --
    -- 视图的一个主要作用(数据安全)
    use HeiMa8;
    go
    create view Exe3.vw_StuInfo
    as
    select * from TestDataBase..vw_StuInfo;
    go

    -- HeiMa8
    select * from Exe3.vw_StuInfo;

    -- select * from sys.databases;

    -- 内联表值函数
    -- 带有参数的视图
    -- 作用: 将一个可变条件的查询封装成一个函数对象,执行结果是一张表
    /*
    create function fn_函数名
    (@参数名 as 类型, ...)
    returns table
    as
    return 查询语句;
    */
    -- 分页
    -- @pageIndex 当前页码
    -- @pageSize 每页条数
    use TestDataBase;
    go

    create function fn_FenYe
    (@pageSize as int, @pageIndex as int)
    returns table
    as
    return
    select * from
    (
    select ROW_NUMBER() over(order by stuId) as num, * from Student
    ) as t
    where t.num between (@pageIndex-1) * @pageSize + 1 and @pageIndex * @pageSize;
    go
    -- 分页

    -- 1008
    select * from fn_FenYe(10, 1008);

    -- 视图怎么提供权限,怎么安全
    -- 角色 访问能力的集合
    -- 架构 可访问对象的集合
    -- 视图 表的一个结果集

     

    -- 变量
    -- int num = 10;
    -- 声明 赋值 使用
    -- declare @变量名 类型;
    -- set @变量名 = 值;

    declare @num varchar(2);
    set @num = '10';

    select @num + 'a';
    go

    -- SQL Server 2008+
    declare @num int = 10;
    -- 局部变量
    -- 系统变量 @@开头

    select @@connections
    select @@CPU_BUSY;

    -- @@error 最近一次执行SQL语句的错误码
    select @@ERROR;
    -- @@version
    select @@version
    print @@version;

    -- @@identity 最近一次插入数据的自动增长编号
    select @@IDENTITY;
    use HeiMa8 ;
    select * from Exe3.newStudent where stuId = (select @@IDENTITY);

    insert into Exe3.newStudent
    (stuName, stuSex, stuBirthdate, stuStudydate, stuAddress, stuEmail, stuPhone, classId)
    values('牛亮亮', 'm', '1999-9-9 9:9:9.123', '2001-1-1 1:1:1.111','123', '123', '12345678909', 2);

    insert into Exe3.newStudent(stuName, stuSex, stuBirthdate, stuStudydate, stuAddress, stuEmail, stuPhone, classId)
    output inserted.*
    values('刘琦', 'm', '1999-9-9 9:9:9.123', '2001-1-1 1:1:1.111','123', '123', '12345678909', 2);

    -- @@trancount
    select @@TRANCOUNT -- 获得当前事务的深度

    -- @@SPID
    select @@SPID; -- session_id
    kill 54

    -- set
    -- select
    -- select @变量=值
    go

    declare @num int;
    select @num = 10;
    select @num;
    go

    select top 3 * from Exe3.newStudent

    declare @name nvarchar(1000) = '';
    -- set @name = (select top 1 stuName from Exe3.newStudent);
    select @name+=stuName from Exe3.newStudent where stuId<10;
    select @name;

    select @@ERROR;

    select * from sys.messages where message_id = 208;

    -- 流程控制(C#的编程)
    -- 选择结构 if-else
    -- 循环结构 while
    /*
    if (bool表达式)
    begin -- {
    脚本
    end -- }
    else if bool表达式
    begin
    -- 脚本
    end
    */

    declare @num int;
    --set @num = 12;

    if @num is null
    select '是NULL';
    else if @num % 2 = 0
    begin
    select '是偶数';
    end
    else
    begin
    select '是奇数';
    end
    go
    -- 循环结构
    /*
    while bool表达式
    begin
    脚本
    end
    */
    declare @sum int;-- = 0;
    declare @i int = 0;

    while @i <= 100
    begin
    set @sum += @i;
    set @i += 1; -- 没有 @i++ 的语法
    end
    select @sum;

    -- 注意set

     

     

     


    select * from Student
    where tbage=(select tbage from Student where tbname=3)

    -- 事务

    -- 最基本的操作以事务为单位
    -- 将一个已经完成的数据库操作行为规定为一个事务
    -- 特点:
    -- 原子性——执行的结果是01特征(要么完成、要么失败)
    -- 持久性——执行的结果不可逆转
    -- 一致性——一旦完成事务,各个版本的结果都一样
    -- 隔离性——事务与事务之间不冲突

    -- 事务严格定义: 找一个操作,如果满足原子性、持久性、一致性和隔离性就称为一个事务

    select * from Exe2.LoginTbl;

    insert into Exe2.LoginTbl(uid, pwd)
    values
    ('苏坤', 'susu123')
    , ('尼古拉斯洛夫斯基斯巴达司机四司机司机司机司机', '123')
    , ('牛亮亮', 'niuniu123');


    -- 自己写的事务
    --事务
    create table bank
    (
    cId char(4) primary key,
    balance money, --余额
    )

    alter table bank
    add constraint CH_balance check(balance >=10)

    go
    --delete from bank
    insert into bank values('0001',1000)
    insert into bank values('0002',10)
    go

    select * from bank

    -- 0001 -> 0002 1000元

    -- 默认的事务方式——隐式事务
    update bank set balance=balance - 1000 where cid='0001';
    update bank set balance=balance + 1000 where cid='0002';
    -- 手动执行事务
    -- 开启事务 -> 执行语句 -> 判断满足与否 -> 提交或回滚
    -- 语法
    -- 开启 begin transaction
    -- 提交 commit
    -- 回滚 rollback

    select @@TRANCOUNT;

    begin transaction

    delete from TestDataBase..Score;
    delete from TestDataBase..Student;

    select * from HeiMa8.Exe3.vw_StuInfo;

    rollback

    --
    select * from bank;

    begin tran
    declare @myError int;
    update bank set balance=balance - 900 where cid='0001';
    set @myError = @@ERROR;
    update bank set balance=balance + 900 where cid='0002';
    set @myError += @@ERROR;
    if @myError > 0
    rollback
    else
    commit
    ;

    -- try-catch
    --begin try
    --end try
    --begin catch
    --end catch

    begin tran
    begin try
    update bank set balance=balance - 1000 where cid='0001';
    update bank set balance=balance + 1000 where cid='0002';
    commit;
    end try
    begin catch
    rollback;
    end catch
    ;

    -- 怎么知道成功没有? -- 使用变量
    -- 怎么使用事务? -- 存储过程
    declare @isSuccess bit;
    begin tran
    begin try
    update bank set balance=balance - 900 where cid='0001';
    update bank set balance=balance + 900 where cid='0002';
    commit;
    set @isSuccess = 'true';
    end try
    begin catch
    rollback;
    set @isSuccess = 'false';
    end catch
    select @isSuccess;
    ;

     

    -- 存储过程
    -- 简单的看成数据库中的方法
    -- 函数、视图、存储过程

    -- 就是一段执行代码

    》》》》》》子查询分页《《《《《《

    -- 系统中常用的存储过程 sp_ stored procedure

    --sp_renamedb -- 修改数据库的名字
    --sp_detach_db -- 分离
    -- sp_attach_db -- 附加

    --sp_executesql

    -- sp_renamedb

    -- exec 存储过程名 参数;
    exec sp_renamedb 'MyThirdDataBase', 'my3thDataBase';

    exec sp_renamedb @dbname='my3thDataBase', @newname='我的第三个数据库';

    -- sp_executesql
    exec sp_executesql N'select @@version'; -- unicode编码
    exec('select ''张三'', ''李四''');

    -- 存储过程的语法
    /*
    create proc[edure] usp_存储过程名
    参数名 类型名 [= 默认值] [output]
    , 参数名 类型名 [= 默认值] [output]
    , ...
    as
    begin
    脚本
    end
    */
    -- 无参无返回值的存储过程
    go
    create proc Exe3.usp_StuInfo
    as
    select * from vw_StuInfo;
    go
    exec Exe3.usp_StuInfo;
    go

    -- 有参有默认值的存储过程
    -- 带有参数的
    create proc Exe3.usp_StuSearch
    @stuName nvarchar(10)
    as
    select * from Exe3.vw_StuInfo where stuName = @stuName;
    go

    exec Exe3.usp_StuSearch @stuName='濮阳语儿';

    exec Exe3.usp_StuSearch '濮阳语儿';

    -- 带有默认值的存储过程
    -- 分页
    go

    create proc Exe3.usp_FenYe
    @pageIndex int = 1
    , @pageSize int = 10
    as
    begin
    select '今天天气很好';
    select * from
    (
    select ROW_NUMBER() over(order by stuId) as num
    , * from Exe3.newStudent
    )as t
    where t.num between (@pageIndex - 1) * @pageSize + 1 and @pageIndex * @pageSize;
    end
    go

    exec Exe3.usp_FenYe 2, 5;

    exec Exe3.usp_FenYe @pageSize=11, @pageIndex=3;

    -- 有参有返回值的存储过程
    -- return output

    go
    -- return 返回值
    create proc Exe3.usp_ReturnTest
    as
    return 123;
    go

    /*
    public string Func()
    {
    return "赵晓虎就是牛,你让牛亮亮怎么办?";
    }
    */
    declare @num int;
    exec @num = Exe3.usp_ReturnTest;

    select @num;
    go

    --1》显示第一页的数据
    --分页查询的时候首先是将数据排序
    select * from Student order by id desc

    -- 银行转账的案例

    create proc Exe3.usp_ZhuanZhang
    @from char(4)
    , @to char(4)
    , @money money
    as
    begin
    begin tran
    begin try
    update bank set balance=balance - @money where cid=@from;
    update bank set balance=balance + @money where cid=@to;
    commit;
    end try
    begin catch
    rollback;
    end catch
    end
    go

    --
    select * from bank;

    exec Exe3.usp_ZhuanZhang '0002', '0001', 900;

    go
    -- 考虑返回值
    create proc Exe3.usp_ZhuanZhangExt
    @from char(4)
    , @to char(4)
    , @money money
    , @isSuccess int output -- 表示需要在存储过程中赋值,传出去
    as
    begin
    begin tran
    begin try
    update bank set balance=balance - @money where cid=@from;
    update bank set balance=balance + @money where cid=@to;
    commit;
    set @isSuccess = 1;
    end try
    begin catch
    rollback;
    set @isSuccess = 0;
    end catch
    end
    go

    -- 关键使用法
    -- 定义一个变量,不赋值,调用存储过程,将参数传入 后跟output

    declare @isSuccess int;

    -- exec Exe3.usp_ZhuanZhangExt '0001', '0002', 500, @isSuccess output;
    exec Exe3.usp_ZhuanZhangExt
    @from = '0001',
    @to = '0002',
    @money = -500,
    @isSuccess = @isSuccess output;

    select @isSuccess;

    -- 注意,不要将变量名命名为与存储过程的参数一致
    go

    create proc Exe2.usp_Login
    @uid nvarchar(20)
    , @pwd varchar(20)
    , @isLogin int output
    as
    select @isLogin=COUNT(*) from Exe2.LoginTbl
    where uid=@uid and pwd=@pwd;
    go

    --
    select * from Exe2.LoginTbl;

    declare @isTrue int;

    exec Exe2.usp_Login '苏坤1', '1234', @isTrue output;

    select @isTrue;

    -- 用C#执行存储过程
    -- 步骤
    -- -> 将sql语句改为存储过程名
    -- -> 修改CommandType命令(text)
    -- -> 看返回结果调用指定方法
    -- -> 如果有参数,与参数化查询用法一样
    -- -> 如果有返回值,设置参数方向即可(难度)

    exec Exe3.usp_FenYe;

    -- 触发器
    -- 在你执行一个操作的时候,自动的执行的一个存储过程

    -- DML DDL
    -- 对行为的分类 update、delete、insert
    -- 发生方式 after | instead of

    -- 语法
    /*
    create trigger tr_in|del|up_触发器的名字 on 表名
    for | after | instead of
    update | delete | insert
    as
    begin
    脚本
    end
    */

    -- inserted deleted

    select * from inserted;

    --
    select * from Exe2.LoginTbl;
    go
    create trigger Exe2.tr_del_deleteReturn on Exe2.loginTbl
    after delete
    as
    insert into Exe2.LoginTbl(uid, pwd)
    select uid, PWD from deleted;
    go

    delete from Exe2.LoginTbl;

    -- 作为数据验证的补充

    --

    -- 索引就是数据的目录
    -- 新华字典
    -- 拼音(聚集索引) she 与正文一致
    -- 部首(非聚集索引) 厍 相当于存储的一个键值对表

    -- 字 拼音 意思 词组。。。

    -- 如何添加索引

    set statistics io on;
    set statistics time on;

    select * from Exe3.newStudent where stuName='苍昊天';
    /*
    SQL Server 分析和编译时间:
    CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
    SQL Server 分析和编译时间:
    CPU 时间 = 0 毫秒,占用时间 = 20 毫秒。

    (2 行受影响)
    表 'newStudent'。扫描计数 1,逻辑读取 2264 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

    SQL Server 执行时间:

    --2》第一页 显示5条数据
    select Top 5 * from Student order by id desc

    CPU 时间 = 16 毫秒,占用时间 = 31 毫秒。

    SQL Server 分析和编译时间:
    CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
    SQL Server 分析和编译时间:
    CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
    SQL Server 分析和编译时间:
    CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。

    (2 行受影响)
    表 'newStudent'。扫描计数 1,逻辑读取 5 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

    SQL Server 执行时间:
    CPU 时间 = 15 毫秒,占用时间 = 24 毫秒。
    */

    -- 索引不一定好

    create unique nonclustered index ix_索引名字 on 表
    (字段 desc, 字段 desc)

    --3》第二页
    select top 5 * from Student
    where id not in (select top 5 * from Student order by id desc)
    order by id desc

    --4》第三页
    select top 5 * from Student
    where id not in (select top (2*5) * from Student order by id desc)
    order by id desc

    》》》开窗函数分页《《《

    --第七页数据 每页5条数据
    --over属于开窗函数

    select * from
    (
    select * ,row_number() over( order by id desc) as paixu from Student
    ) as tbl
    where tbl.paixu between 6*5+1 and 7*5

    2.连表查询

    --查询所有学生的姓名、年龄及所在班级 (班级在另一个表中)
    --当多个列在不同的表中时,要跨表查询,所以一般可以使用inner join
    --tc ts是对表名起的别名
    select
    ts.tsname,
    ts.tsage,
    tc.tclassname
    from TblStudent as ts
    inner join TblClass as tc on ts.tsclassid=tc.tclassid(只查询两个表中都有的数据)

    --》》》full join 是查询所有的数据(没有的为空)

    ---子查询写法
    select
    tsname,
    tsage,
    班级名称=(select tclassname from TblClass where TblClass.tclassid=TblStudent.tsclassid)
    from TblStudent

    --查询学生姓名、年龄、班级及成绩(成绩属于第三张表)
    select
    ts.tsname,
    ts.tsage,
    tc.tclasssname,
    tscore.tenglish,
    tscore.tmath
    from TblStudent as ts
    inner join TblClass as tc on ts.tsclassid=tc.tclassid 
    inner join TblScore as tscore on tscore.tsid=ts.tsid

     

    --》》》左外联接(左联接)

    --查询没有参加考试的学生的姓名与编号
    --把左表(left join 关键字左边的表)中的全部记录都显示出来,对于那些在右表中能找到匹配的记录,显示对应匹配数据,对于那些右表中找不到匹配的记录显示为null
    select
    ts.tsid,
    ts.tsname,
    TblScore.*
    from TblStudent as ts
    left outer join TblSore.tsid=ts.tsid   --outer可以不写

    --》》》右外联接
    --表示要将右表(right join 右边的表)中的所有数据都显示,左表中只显示那些匹配的数据。

    select
    ts.tsid,
    ts.tsname,
    TblScore.*
    from TblStudent as ts
    right outer join TblSore.tsid=ts.tsid

    --右外联与左外联都是先将匹配的数据找到,然后再将那些没有匹配的数据添加进来,(注意:不是一起查询出来的,有先后顺序)

    --》》》练习:查询所有学生(参加和未参加的考试)的学生姓名、年龄、成绩,如果没有参加考试显示缺考,如果小于english或者math 小于60分显示不及格
    select
    ts.tsname,
    ts.tsage,
    tscore.tsid,
    case
    when tscore.tenglish is null then '缺考'
    else convert(varchar(10),tscore.tenglish)
    end as 英语成绩,
    case
    when tscore.tmath id null then '缺考'
    else convert (varchar(10),tscore.tmath)
    end as 数学成绩,
    是否报考=
    case
    when tscore.tscoreid is null then '是'
    else '否'
    end
    from TblStudent as ts
    left join TblScore as tscore on ts.tsid=tscore.tsid

    3.视图

    视图本身并不存储数据,只是存储的查询语句,如果把真实表中的数据修改后,则通过视图查询到的结果也变了。

    视图的目的是方便查询,所以一般情况下不能对视图进行增删改查

    --在视图中的查询语句,必须为每一列创建一个列名
    create view vw2
    as
    select
    tsname,
    case
    when tsage>13 and tsage<=16 then '少年'
    when tsage>50 then '老年'
    else '青壮年'
    end as 称呼
    from TblStudent

    --在视图中不能使用order by语句。除非:另外还指定了top 或for xml
    --错误
    create view vw3
    as
    select * from TblStudent order by tsage desc

    --正确
    create view vw3
    as
    select top 3 * from TblStudent order by tsage desc

    4.声明变量与使用

    --》》》局部变量
    --声明变量
    declare @name varchar(10)
    declare @age int

    --赋值
    set @name='yhz'
    set @age=17

    --输出值
    print @name
    print @age

    --使用set与select为变量赋值的区别
    declare @rcount int
    set @rcount=(select count(*) from TblStudent)
    print @rcount

    declare @rcount int
    select @rcount=count(*) from TblStudent
    print @rcount

    --》》》全局变量
    print @@language
    print @@version
    print 'aaa'+100
    --通过判断@@error变量中是否不为0,就可以判断上一条sql语句执行是否出错了
    --如果@@error为0,表示上一条sql语句执行没出错,如果@@error不为0,则表示上一条sql语句出错了。
    print@@error

    --》》》通过while计算1-100之间所有奇数的和

    --声明变量并初始化

    declare @sum int=0
    declare @i int =1
    while @i<=100
    begin
    if @i%2<>0
    begin
    set @sum=@sum+@i
    end
    end
    print @sum

    5.事务

    事务有四个属性:原子性 一致性 隔离性 持久性
    原子性:对于数据修改,要么全都执行,要么全都不执行
    一致性:当数据完成时,数据必须处于一致状态
    隔离性:对数据进行修改的所有并发事务时彼此隔离的。这表明事务必须是独立的,它不应以任何方式依赖于或影响其他事务
    永久性:事务完成后,他对数据库的修改被永久保持,事务日志能够保持事务的永久性

    --打开事务
    begin transaction

    --提交事务
    commit transaction

    --回滚事务
    rollback transaction

    --账户A给账户B转账 当一方出问题时,两个语句都不执行
    begin tran
    declare @sum int=0
    update bank set balance =balance-1000 where cid='0001'
    set @sum=@sum+@@error
    update banl set balance =balance+1000 where cid='0002'
    set @sum=@sum+@@error

    if @sum<>0
    begin
    rollback tran
    print '回滚'
    end
    else
    begin
    commit tran
    print '提交了'
    end

    6.存储过程

    --创建一个自定义的存储过程
    create proc usp_HelloWorld
    as
    begin
    print 'hello world'
    end

    --输出存储过程
    exec usp_HelloWorld

    --创建一个存储过程计算两个数的和
    create procedure usp_Add
    @num1 int,
    @num2 int
    as
    begin
    print @num1+@num2
    end

    --输出值
    exec usp_Add 100,230

    --存储过程中的参数的问题
    --存储过程如果有参数,则调用的时候必须为参数赋值
    exec usp_Add --不传参数则报错

    --第二个参数如果用户不传,则有一个默认值
    create procedure usp_Add
    @num1 int,
    @num2 int 1000 --为存储过程的参数设置默认值
    as
    begin
    print @num1+@num2
    end

    --创建分页存储过程
    create proc usp_PageBum
    @pageSize int, --每页显示的数量
    @pageIndex int --第几页
    as
    begin
    select * from (select *,row_number()over (order by CityID asc)as num from S_City )as s
    where s.num between (@pageIndex -1)*@pageSize +1 and @pageIndex *@pageSize
    end
    --查询第5页内容每页显示10条数据
    exec usp_PageBum 10,5

    --删除一个存储过程
    drop proc usp_Add

    7.触发器

    尽量避免在触发器中执行耗时操作,因为触发器会与sql语句认为在同一个事务中(事务不结束,就无法释放锁)

    --创建插入数据触发器
    create trigger tri_Teacher_insert_after
    on Teacher after insert
    as
    begin
    declare @id int
    declare @name varchar(10)
    declare @phone int
    declare @mail varchar(50)
    select @id=tcid,@name=tcname,@phone=tcphone,@mail=tcmail from inserted

    print @id
    print @name
    print @phone
    print @mail
    end

    --插入数据
    insert into Teacher values('网名好','12352536','Wjifdfji@qq.com')

    --创建删除数据触发器
    --不能有主键
    create trigger tri_Teacher_after
    on Teacher after delete
    as
    begin
    insert into TeacherBak
    select * from deleted
    end

    --删除数据
    --sql server中的触发器是表级触发器,无论删除多少行或者插入多少行,只触发一次
    --是按语句来触发的,每次执行一次语句,触发一次触发器
    delete from Teacher where tcid>18

    8.游标

    --1.定义游标
    declare cur_Student cursor fast_forward for select * from Student

    --2.打开游标
    open cur_Student

    --2.1 对游标的操作
    --将每条数据读取并输出

    --2.1.1将游标向后移动一条
    fetch next from cur_Student

    --将游标循环向后移动,直到末尾
    while @@fetch_status=0
    begin
    fetch next from cur_Student
    end

    --3.关闭游标
    close cur_Student

    --4.释放资源
    deallocate cur_Student

    9.(补充)全局临时表,局部临时表

    局部临时表:表名以#为开头。只在当前会话中有效,不能跨连接访问。如果直接在连接会话中创建,则当前连接断开后删除,如果是在存储过程中创建的,则存储过程执行完毕后删除

    全局临时表:表名以##为开头。多个会话可共享全局临时表。当创建全局临时表的会话断开,并且没有用户正在访问全局临时表时删除

    10.(补充)约束

    --删除一列(EmpAddress列)
    alter table Class drop column EmpAddress

    --增加一列(增加一列EmpAddr varchar(1000))
    alter table Class Add EmpAddr varchar(1000)

    --修改一下Emp 的数据类型(varchar(200))
    alter table Class alter column Emp varchar(200)

    --为EmpId增加一个主键约束
    alter table Class add constraint PK_Class_EmpId primary key(EmpId)

    --为EmpName增加一个唯一约束
    alter table Class add constraint UQ_Class_EmpName unique(EmpName)

    --为性别增加一个默认约束,默认为男
    alter table Class add constraint DF_Class_EmpGender default('男') for EmpGender

    --为年龄增加一个检查约束,年龄必须在1—120岁之间(包含)
    alter table Class add constraint CK_Class_EmpAge check(EmpAge>=0 and EmpAge<=120)

    --增加外键约束,表Class中有一列EmpDeptId引用Student表中的DeptId
    alter table Class add EmpDeptId int not null
    alter table Student add constraint PK_Student_DeptId primary key(DeptId)

    alter table Class add constraint FK_Class_Student foreign key(EmpDeptId)
    references Student(DeptId)

    --一条语句删除多个约束,约束名用 逗号 隔开
    alter table Class drop constraint
    PK_Student_DeptId,
    FK_Class_Student,
    CK_Class_EmpAge

    --用一条语句为表增加多个约束
    alter table Class add
    constraint PK_Student_DeptId primary key(DeptId),
    constraint CK_Class_EmpAge check(EmpAge>=0 and EmpAge<=120),
    add constraint DF_Class_EmpGender default('男') for EmpGender

    后记

    笔记不全,还请见谅!希望对你有所提高。

     

    本文由美高梅网投网址发布于数据库,转载请注明出处:包含字段表达式,内容主要是对数据库的基本

    关键词:

上一篇:没有了

下一篇:没有了