SE途乐VEKuga数据库学习总括,数据库基础笔记分享

前言

  对于SQL SE大切诺基FVELX570数据库也学了有说话了,自身也对团结所学做了一部分总计。

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

DataSet的内容介绍,二种单例方式(并发量的虚拟),SQL高等中的case语句、连接操作、子查询、派生表

本文是私有学习SQL Server 数据库时的过去笔记的股盘的整理,内容注重是对数据库的着力增删改查的SQL语句操作自律,视图,存款和储蓄进度,触发器的骨干精通。

   大家首先学习数据库设计的片段知识点和用SQL语句建库。

实践流程:
from子句 -> where子句 ->group by子句 ->having子句 ->select子句 ->order by子句

 

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

  设计数据库步骤:供给深入分析阶段,概要设计阶段,详细设计阶段,

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

————————————————————————————————

正文

   建数据库的SQL语句如下(包罗了一旦有该数据库先删除在创设数据库)

-- 子查询
-- 就是在二个查询中嵌套贰个查询
-- 一般意义正是利用多张表查询八个新闻
-- 比如查询"榆林语儿"的战表
select * from TestDataBase..Student;
select * from TestDataBase..Score;
-- 在上学的小孩子表中查得stuId,然后再到分数表中询问分数
select stuId from TestDataBase..Student where stuName ='呼伦贝尔语儿';

1.Case的用法

1.子查询

use master
GO
if exists(select * from sysdatabases where name='Wages')
DROP database Wages
CREATE DATABASE Wages
 ON
 (
  NAME='Wages_data',
  FILENAME='e:projectWages_data.mdf',
  SIZE=5mb,
  FILEGROWTH=15%
 )
 LOG ON
 (
  NAME= 'Wages_log',
  FILENAME='e:projectWages_log.ldf',
  SIZE=3mb,
  FILEGROWTH=15%
 )
GO

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 '英语'

运用方法一:(类似C#中的case的用法)

--把一个询问结果作为别的一个询问的查询源
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)

下边是成立表的SQL语句(蕴含了一旦有该表先删除在建表)

-- 分页

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

 

 

 


Case  表达式

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

USE Wages
GO
if exists(select * from sysobjects where name='WageInfo')
DROP table WageInfo
CREATE TABLE WageInfo 
(
  CompanyID INT primary key IDENTITY(1,1),
  CompanyName varchar(50) NOT NULL,
  Poste varchar(50) NOT NULL,
)
GO
if exists(select * from sysobjects where name='StudentInfo')
DROP table StudentInfo
CREATE TABLE StudentInfo
(
  ID  INT primary key IDENTITY(1,1),
  Name  VARCHAR(50) NOT NULL,
  CompanyID INT ,
  PosteDate  DATETIME ,
  Wage  int,
)
GO

-- 事务

-- 最主题的操作以作业为单位
-- 将一个曾经实现的数据库操作行为规定为贰个业务
-- 特点:
-- 原子性——推行的结果是01表征(要么完结、要么退步)
-- 悠久性——推行的结果不可反败为胜
-- 一致性——一旦产生业务,各类版本的结果都一样
-- 隔开性——事务与业务之间不冲突

-- 事务严刻定义: 找三个操作,假使满意原子性、持久性、一致性和隔断性就称为二个作业

select * from Exe2.LoginTbl;

insert into Exe2.LoginTbl(uid, pwd)
values
('苏坤', 'susu123')
, ('Nicolas洛夫斯基斯巴达司机四司机司机司机司机', '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;
;

 

-- 存款和储蓄进度
-- 轻易的当作数据库中的方法
-- 函数、视图、存款和储蓄进程

-- 正是一段施行代码

when 值1 then 返回值

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

下边是创设约束

-- 系统中常用的积存进程 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

when 值2 then 返回值

--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

alter table 表名

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)

End

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

add constraint 约束名 约束类型 具体的自律表明

->案例:把客户表中数据,全部查询出来,需求把DelFlag=0的显得成未删减

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

示例

select *,case DelFlag

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

创设外键约束

when 0 then N'未删除'

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

alter table StudentInfo
add constraint pk_CompanyID foreign key(CompanyID) references WageInfo(CompanyID)
GO

when 1 then N'删除'

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

安顿数据说话如下

end as 删除状态

2.连表查询

insert into WageInfo(CompanyName, Poste)values
('微软','职员'),
('百度','职员'),
('腾讯','职员'),
('爱奇艺','职员'),
('华为','职员')
insert into StudentInfo(Name, CompanyID, PosteDate, Wage)values
('张三',1,'2016-05-05',2500),
('李四',2,'2016-05-06',2600),
('王五',3,'2016-05-07',3000),
('赵二',4,'2016-05-08',4000),
('钱一',5,'2016-05-09',5000)
insert into StudentInfo(Name, CompanyID, PosteDate, Wage)values('钱二',null,'2016-05-09',NULL)

,UserName

--查询全数学员的全名、年龄及所在班级 (班级在另四个表中)
--当八个列在区别的表中时,要跨表查询,所以一般可以利用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(只询问多少个表中都部分数据)

接下来大家上学了变量,变量分全局变量和一部分变量。

from

--》》》full join 是询问全部的数量(未有的为空)

创设变量语法如下是

UserInfo

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

declare @变量名 数据类型

 

--查询学生姓名、年龄、班级及战表(战绩属于第三张表)
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

一些变量有二种赋值方法

 

 

set @变量名=value

运用格局二:(类似C#中的三个if else)

--》》》左外联接(中国左翼诗人联盟接)

select @变量名=value

->语法

--查询没有到场考试的学习者的人名与数码
--把左表(left join 关键字左侧的表)中的全体记下都突显出来,对于那贰个在右表中能找到相称的笔录,展现对应极度数据,对于那贰个右表中找不到极其的记录突显为null
select
ts.tsid,
ts.tsname,
TblScore.*
from TblStudent as ts
left outer join TblSore.tsid=ts.tsid   --outer能够不写

区分是select可二次对两个变量赋值,set只好一回对二个变量赋值。

Case

--》》》右外对接
--代表要将右表(right join 右侧的表)中的全数数据都突显,左表中只展现那二个相称的数目。

全局变量只可以用无法友好创制和赋值!!!

when 表达式 then 返回值

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

出口语句

when 表达式 then 返回值

--右外联与左外联都以先将相配的数额找到,然后再将那多少个并没有匹配的多少增进进去,(注意:不是同台查询出来的,有先后顺序)

print 和select

else 值

--》》》练习:查询全体学生(参预和未参预的试验)的学员姓名、年龄、战表,若无出席考试展现缺考,假使小于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

use MySchool

->案例:把客户表中数据总体查询出来,须求把DelFlag=0的突显成删除

3.视图

go

select *,case

视图本身并不存储数据,只是存款和储蓄的查询语句,假若把实际表中的数据修改后,则经过视图查询到的结果也变了。

select * from StuInfos

when DelFlag=0 then N'未删除'

视图的目标是有益查询,所以一般景色下不可能对视图实行增加和删除改查

1.班级表   班级编号 (主键)   班级名   (长度固定3位)   班级人数 (暗中认可30)

when DelFlag=1 then N'删除'

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

  if exists(select * from sysobjects where name='Classes')

else N'未知'

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

drop table Classes

end as 删除状态

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

  go  

,UserName

4.宣称变量与运用

 create table Classes  

from

--》》》局地变量
--证明变量
declare @name varchar(10)
declare @age int

 (   clsid int identity(1,1), --班级编号  

UserInfo

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

' clsname varchar(3), --班级名称   clsnums int --班级人数   )   

 

--输出值
print @name
print @age

go   

 

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

'alter table Classes

2.填补:ISNULL(表明式,替换的值)

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

add constraint pk_clsid_Classes   primary key(clsid)  

select CreateDate,ISNULL(CreateDate,getdate()) from UserInfo

--》》》全局变量
print @@language
print @@version
print 'aaa' 100
--通过推断@@error变量中是不是不为0,就能够肯定上一条sql语句奉行是或不是出错了
--假设@@error为0,表示上一条sql语句实践没出错,纵然@@error不为0,则表示上一条sql语句出错了。
print@@error

 alter table Classes

 

--》》》通过while计算1-100中间全体奇数的和

add constraint ck_clsname_Classes   check(len(clsname)=3)  

3.SQL说了算语句

--证明变量并起先化

 alter table Classes

->SQL中的变量

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

add constraint df_clsnums_Classes   default 30 for clsnums   

->定义变量:

5.事务

go  

->declare @变量名 变量类型

事务有三个属性:原子性 一致性 隔开分离性 长久性
原子性:对于数据修改,要么全都推行,要么全都不执行
一致性:当数码产生时,数据必得处于相同状态
隔开分离性:对数码进行修改的有所并发事务时互相隔断的。那评释专门的学业必需是单独的,它不应以别的措施注重于或影响其余事情
长久性:事务完毕后,他对数据库的改变被长久保持,事务日志可以保持职业的长久性

 insert into Classes select 't10',25 union select 't11',18 union select 't12',23

->给变量赋值:

--展开专门的学问
begin transaction

2.外键约束   为学员表增加二个clsid列来代表其班级   alter table StuInfos add clsid int   外键约束   

->set @参数名=值

--提交事务
commit transaction

alter table StuInfos with nocheck --不对表现一些数据作限制操作

->select @参数名=值

--回滚事务
rollback transaction

add constraint fk_StuInfos_Classes    foreign key(clsid) references Classes(clsid)   

select @i=count(1) from UserInfo;

--账户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

alter table StuInfos drop constraint fk_StuInfos_Classes

->打印 : Print @变量名

6.储存进程

3.建表同不日常间建约束,老师表   编号     (主键 标记列)   名称     (非空)

 

--创立二个自定义的蕴藏进度
create proc usp_HelloWorld
as
begin
print 'hello world'
end

性别     (男或女,暗中同意男)   年龄     (12-60)   电话     (唯一)   班级编号 (外键)   

->IF ELSE

--输出存款和储蓄进程
exec usp_HelloWorld

if exists(select * from sysobjects where name='TeaInfos')

->语法格式:

--创设叁个囤积进程总括多个数的和
create procedure usp_Add
@num1 int,
@num2 int
as
begin
print @num1 @num2
end

drop table TeaInfos  

if(表达式)

--输出值
exec usp_Add 100,230

 go  

begin

--存款和储蓄进程中的参数的标题
--存款和储蓄进程假设有参数,则调用的时候必需为参数赋值
exec usp_Add --不传参数则报错

 create table TeaInfos  

SQL语句

--次之个参数假使客户不传,则有叁个暗许值
create procedure usp_Add
@num1 int,
@num2 int 1000 --为存款和储蓄进程的参数设置暗中认可值
as
begin
print @num1 @num2
end

 ( id int primary key identity(1,1), --编号

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

name varchar(10) not null, --名称

else

--删除一个仓库储存进程
drop proc usp_Add

sex char(2) check(sex='男' or sex='女') default '男', --性别

begin

7.触发器

age int check(age>=12 and age<=60), --年龄

语句

尽量幸免在触发器中进行耗费时间操作,因为触发器会与sql语句以为在同四个作业中(事务不收场,就无法自由锁)

tel char(11) unique, --电话

end                

--创立插入数据触发器
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

clsid int foreign key references Classes(clsid) --班级   )

->WHILE

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

  go

->语法:

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

一:操作变量 --1-- 证明变量@temp值为1并打字与印刷出来 表明变量关键字:

While(表达式)

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

declare   declare @temp int --声明   

begin

--删除数据
--sql server中的触发器是表级触发器,无论删除多少行照旧插入多少行,只触发三回
--是按语句来触发的,每一遍实施贰遍讲话,触发一次触发器
delete from Teacher where tcid>18

set @temp=1       --赋值  

SQL语句

8.游标

 print @temp       --输出   

end

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

  declare @temp int=1 --注明的同期赋值  

 

--2.开荒游标
open cur_Student

 print @temp         --输出   

4.数量的接二连三

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

 

主键不插足专门的学业逻辑

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

赋值方式一:用set 输出方式一:用print   declare @temp int --申明   

->交叉连接

--将游标循环向后运动,直到最终
while @@fetch_status=0
begin
fetch next from cur_Student
end

select @temp=1 --赋值   select @temp   --输出

一张表中有M条数据,另一张表中有N条记录,那么交叉连接后便是M*N

--3.关门游标
close cur_Student

赋值格局二:用select 输出格局二:用select --

 

--4.获释能源
deallocate cur_Student

2-- 注脚多少个变量存款和储蓄'姓名、性别、年龄',再打字与印刷新闻,格式如下: 姓名:杰达姆,性别:男,年龄:18 --注明

->内连接

9.(补充)全局一时表,局地一时表

declare @name varchar(10),         @sex char(2),         @age int

一张表中有M条数据,另一张表中有N条记录,那么内接连后的数据不分明条数,要依照外键去研商

有的不经常表:表名以#为始发。只在当前对话中央银立见成效,不能够跨连接待上访谈。如若直接在接连会话中开创,则当前连日断开后去除,假诺是在存款和储蓄进程中开创的,则存款和储蓄进程施行完结后去除

--赋值 select @name='杰达姆',@sex='男',@age=18

->外连接

全局不时表:表名以##为早先。几个会话可分享全局有时表。当创造全局有的时候表的对话断开,并且未有客户正在访谈全局有的时候表时删除

--输出 print  '姓名:' @name ',性别:' @sex ',年龄:'    cast(@age as varchar)   

select * from 表1 as t1 right jion 表2 as t2 on t1.列名=t2.列名;

10.(补充)约束

--convert(varchar,@age)

代表将表1和表2通过“列名”作为对应关系张开一连,倘若有数据尚未相关对应,就看中间的连年情势right/left/full,假若是right就以右边手的数据为主,要是是left就以左边手的数量为主,要是是full就整个都显得,未有相应就补null;

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

--多少个转型函数:   1.convert -- 语法:  convert(类型,数据)   2.cast   

 

--扩展一列(扩展一列EmpAddr varchar(一千))
alter table Class Add EmpAddr varchar(1000)

-- 语法:  case(数据 as 类型)

 

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

--3-- select * from StuInfo

 

--为EmpId扩大贰个主键约束
alter table Class add constraint PK_Class_EmpId primary key(EmpId)

打印张秋丽的消息(来自于student数据库的多寡),格式如下: 姓名:张秋丽 性别:男 年龄:18

5.子查询

--为EmpName扩充一个独一约束
alter table Class add constraint UQ_Class_EmpName unique(EmpName)

--声明 declare  @name varchar(10)='张秋丽', @sex char(2), @age int

亟需将四个二个询问的结果作为另多少个查询的基准

--为性别增添贰个暗中同意约束,默感觉男
alter table Class add constraint DF_Class_EmpGender default('男') for EmpGender

--赋值:来自于表内 select @sex=stuSex,@age=stuAge from stuinfo  where stuName=@name

 

--为年龄扩充五个检查约束,年龄必得在1—120岁之间(富含)
alter table Class add constraint CK_Class_EmpAge check(EmpAge>=0 and EmpAge<=120)

--输出 print '姓名:' @name print '性别:' @sex print '年龄:' cast(@age as varchar)

常常,将里面包车型大巴查询叫做子查询,外面包车型大巴询问叫做外界查询

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

--4-- 查询李文才的左右同室的新闻 declare @seat int select @seat=stuSeatfrom stuinfo where stuname='李文才' select * from stuinfo  where stuSeat=@seat-1 or stuSeat=@seat 1

注意:子查询内部 必得询问一个数额

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

--5-- 查询比张秋丽大10岁以上的学习者音讯 declare @age int  select @age=stuAge from stuinfo where stuName='张秋丽' select * from stuinfo where stuAge>=10 @age 

 

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

总结: set和select的区别  

回去结果

--用一条语句为表扩展四个约束
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

 1. set壹遍只好为二个变量赋值,而select能同有时间为两个变量赋值  

单值(二个单元格)    标量子查询(日常放在where子句中作为标准,恐怕在select中作为五个值)

后记

 2. set只好赋三个永久的值,而select能动态的赋值(值来自于表内) select的效果  

多值    多值子查询

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

 1. 查询SQL语句,如: select * from 表名  

行(略)

 

 2. 赋值操作,   如: select 变量名=值  

列    一般位于in中作为批量删减、修改等操作或询问

 3. 输出操作,   如: select 变量名 二:调控流程结构:if,else

表    一般作为数据源进行再一次搜索

--1-- 注脚变量number,并赋值,然后剖断是偶数依旧奇数,结果如下: 当前值为11,它是三个奇数 declare @number int set @number=12 if(@number%2=0) print '该数为偶数' else print '该数为奇数' -

 

-2-- 依照输入的年纪显得人的发育阶段 未中年人<18,弱冠之年人<30,成人<60,花甲之年人<100,超人>=100

6.表表达式

declare @age int set @age=21

->派生表

if(@age<18) print '未中年人' else if(@age<30)

select top 30

print '青年人'

t1.stuId as 编号

else if(@age<60)

, t1.stuName as 姓名

print '成年人'

, case t1.stuSex when 'm' then '男' else '女' end as 性别

else if(@age<100)

, t3.final as 期末总评

print '老年人'

, t2.className as 课程

else print '超人'

, t1.stuPhone as 电话

select * from StuInfo select * from StuExam

, t1.stuAddress as 家庭住址

--3-- 总结笔试平均分,若是平均分超过70分打字与印刷'出色' 同不常间展现前三名学员的试验音信不然      打字与印刷'非常糟糕'显示后三名学生音信

from

declare @avgScore int select @avgScore=AVG(writtenExam) from StuExam if(@avgScore>=70) begin print '本班战绩优异'

TestDataBase..Student as t1

select top 3 * from StuExam order by writtenExam desc end else begin print '本班战绩相当糟糕' select top 3 * from StuExam order by writtenExam end

inner join

三:循环结构 --1-- 输出九伍遍'小编爱您' declare @i int=1  while(@i<=99) begin print '第' convert(varchar,@i) '作者爱你' set @i =1 end

TestDataBase..Course as t2

--2-- 计算1-100的拉长和,结果格式:1-100的丰盛和为:xxx declare  @i int=1, @sum int=0 while(@i<=100) begin set @sum =@i set @i =1 end print '1-100的拉长和为:' convert(varchar,@sum)

on t1.classId = t2.classId

--3-- 不停的加强学生笔试战表2分,让具备学员的笔试战表都合格

inner join

declare @count int --用来记录不如格的人头 while(1=1) begin --总括比不上格的人头 select @count=COUNT(*) from StuExam   where writtenExam<60 --剖断 if(@count=0)   break --退出死循环 else   update StuExam set writtenExam =2 end select * from StuExam

(select

四:多分支语句

stuId

--1-- 请宣示变量@name表示学生姓名,对该学员年龄进行划等级 具体如下: 12周岁前展现:'小学生' 12-17出示'初级中学生'  18-22展现'高级中学生' 23-28显得'博士' 28以上'超人' 结果格式:xxx是三个xxx

, avg(testBase testBeyond testPro) as final 

declare  @name varchar(20)='小强', @age int=23, @result varchar(10)

from

--多分支 set @result=case     when @age<12 then '小学生'     when @age<17 then '初中生'     when @age<22 then '高中生'     when @age<28 then '大学生'     else '超人'    end

TestDataBase..Score

--输出 print @name '是一个' @result

group by

--2-- 显示学号、笔试成绩、品级,数据如下:  笔试战绩:90以上的--A等         80之上的-- B等         70上述的-- C等         60上述的-- D等         60之下的-- E等 stuNo   writtenExam   grade s25303  60            D等 s25302  40            E等 s25301  77            C等 s25318  45            E等 select  stuNo, writtenExam, grade=case    when writtenExam>=90 then 'A等'    when writtenExam>=80 then 'B等'    when writtenExam>=70 then 'C等'    when writtenExam>=60 then 'D等'    else 'E等'     end from StuExam --3-- 请遵照平均分和底下的评分准则,编写T-SQL语句询问学员的实绩 优 :90分以上    良 :80-89分     中 :70-79分     差 :60-69分     极差 :60分以下         

stuId) as t3

select AVG(writtenExam),A=case      when Avg(writtenExam)>90 then '优'      when Avg(writtenExam)>80 and Avg(writtenExam)<89 then '良'      when Avg(writtenExam)>70 and Avg(writtenExam)<79 THEN '中'      when Avg(writtenExam)>60 and Avg(writtenExam)<69 then '差'      when Avg(writtenExam)<60 then '极差'      end      from stuExam    

on t1.stuId = t3.stuId

--4-- 难题: 依据如下法规对机试成绩举办频仍加分,       直到机试平均分超过85分成止       请编写T-SQL语句完毕,注:(循环 多分支) 90分以上:  不加分 80-89分:   加1分 70-79分:   加2分 60-69分:   加3分 60分以下:  加5分 declare @a int   while(1=1) begin select @a=AVG(labExam)from dbo.stuExam if(@a<85) begin update dbo.stuExam set labExam =case          when labExam>=90 then  labExam          when labExam>=80 then labExam 1          when labExam>=70 then labExam 2          when labExam>=60 then labExam 3          else          labExam 5         end end else break end select AVG(labExam) AS 机试战绩平均分 from dbo.stuExam

order by

查询

t3.final desc;

子查询:查询中再查询,通常是以二个询问作为标准来供另一个查询利用
语法:
   select 列表 from 表名 
 where >(子查询)
注意:
 使用相比运算符时,供给子查询只可以回去一条或空的记录!
示例:
 供给获知比小编小的学员消息
 select * from student
 where studentno in
 (select studentno from student
 where studentname='我')

个中,将结果集作为一张表来使用的使用就叫做派生表

NOT IN:明确给定的值是或不是与子查询或列表中的值相匹配,借使不匹配则反回真。
采用方式:
 在必要子查询重回大多据时选拔。
语法:
 select 列表 from 表名 
 where 列名 not in(子查询)
示例:
 查询未到位过考试的学生消息
 select  * from student 
 where studentno not in
 (select studentno from Result)

骨子里那张表是一纸空文的,

1.EXISTS 子查询
 EXISTS: exists 关键字能够检验数据是或不是留存,假使存在再次来到真。
语法
 if exists(子查询)
 语句
示例:
 查询那几个大学学生有未有叫张三的
 if exists( select * from student
 where studentname='张三' )
 begin
 print '有叫张三的'
 end

汇编语言:机器型语言

  1. NOT EXISTS 子查询
     NOT EXISTS: exists 关键字能够检测数据是或不是存在,假诺子虚乌有重回真
    语法
     if not exists(子查询)
     语句
    示例:
     查询本校学生是或不是绝非叫张三的
     if not exists( select * from student
     where studentname='张三' )
     begin
     print '未有叫张三的'
     end

尖端语言:描述性语言

1:非相关子查询是独自于表面查询的子查询,子查询总共试行贰次,实践完成后将值传递给外界查询。  2:相关子查询的实施信赖于外界查询的数目,外界查询实践一行,子查询就实行二回。数据是不是留存,假若不设有重回真

其三种语言:职务型语言

亲自过问:检索出在work表中每三个机关的最高基本报酬的职薪俸料  select * from work a where 基本薪资=(select max(基本报酬) from work b where a.部门名称=b.部门名称)

 

 事物视图索引

表表明式的意味是表参加运算获得的结果要么表,常用的表表达式:

语法

->派生表

begin transaction

->分页(重要)

commit transaction

引出:Tmall京东购物网址,第一页展现不断 ,会分页展现

rollback transaction

select * from Student;

视图

总共有6万条数据,贰次突显会崩溃,所以分页展现

创设视图索引

标题:每页有M条记录,要呈现第N页,怎么样得以实现?

if exists(select * from sysobjects where name='视图名')

实质上不佳写,凡是遭遇这种有规律性难点的时候,你供给给协和安装具体的数字,根据数字的原理先写个三次、九回的结果,从中找到结果;

drop view 视图名

每页10条,第1页

create view  视图名

select top 10 * from TestDabase..Student;

as

每页10条,第2页

SQL语句

select * from TestDatabase..Student where stuId between 11 and 20;

索引

每页10条,第3页

if exists(select name from sysindex where name='索引')

select * from TestDatabase..Student where stuId between 21 and 30;

drop index 表名。索引名

……

create 索引类型 index 索引名

规律:

on 表名(列名)

每页M条,第N页

with fillfactor=30

select * from TestDatabase..Student

GO

where stuId between M*(N-1) and M*N;

一 存储进程的概念

 

储存进度是在数据库管理种类保留的,预先编写翻译的,能兑现某种意义的SQL程序,它是数据库应用中利用相比较常见的一种多少对象。

假若不接二连三,那么能够考虑加四个列,让其总是

缘何要求仓储进度?

--row_number() over(order by 字段)

1.储存进程只在开马上进行编译,未来每趟推行存款和储蓄进度都不需再重复编写翻译,而一般SQL语句每执行三回就编写翻译一回,所以选取存储进度可提升数据库试行进程。 2.当对数据库实行理并答复杂操作时,可将此复杂操功能存款和储蓄进度封装起来与数据库提供的事务管理结合一同使用。 3.存款和储蓄进程能够重复使用,可削减数据库开拓人士的职业量。 4.安全性高,可设定只有少数客商才享有对点名存款和储蓄进程的使用权

 

积攒过程的独到之处:

->公用表表明式(CTE)

1.模块化程序设计

-- 公用表表明式(CTE, common table expression)

2.实践进程块,功用高

-- 语法

3.减小网络流量

/*

4.具有卓越的安全性

with 别名

 

as

二 系统存款和储蓄进程

(

SQL_SE奥迪Q5VEPRADO 提供系统存款和储蓄进度,它们是一组预编译的T-SQL语句,系统存款和储蓄进度提供了管住数据库和更新表的体制,并充当从系统表中检索新闻的飞速格局。

结果集

常用的系统存储过程

系统存储过程

说明

sp_databases

列出服务上的所有数据库

sp_helpdb

报告有关指定数据库或所有数据库的信息

sp_renamedb

更改数据库的名称

sp_tables

返回当前环境下可查询的对象的列表

sp_columns

返回某个表列的信息

sp_help

返回某个表的所有信息

sp_helpconstraint

查看某个表的约束

sp_helpindex

查看某个表的索引

sp_stored_procedures

列出当前环境中的所有存储过程

sp_password

添加或修改登录账户的密码

 

)

三 客商自定义的积累进度

紧跟查询

1.创设不带参数的仓库储存过程

*/

Create proc usp_selectstu

 

As

with t

Select StudentName,Gender,GradeId,Phone from dbo.Student

as

 

(

调用存款和储蓄进程:exec usp_selectstu

select

2.创办带入参数的存放进度

row_number() over(order by stuid) as num

Create proc usp_stuInfo @gradeid int=2       (默认)

, stuId

As

, stuName

Select * from student where gradeId=@gradeid

, stuSex

调用存款和储蓄进程:exec usp_stuInfo 2

, stuBirthdate

3.制造带出参数的存款和储蓄进程

, stuStudydate

create proc usp_selectGrade @name nvarchar(10),@gradeid int output

, stuAddress

As

, stuEmail

Select @gradeid=gradeid from student where  studentname=@name

, stuPhone

print @gradeid

, stuIsDel

 

, stuInputtime

调用存款和储蓄进度:

, classId

declare @id int

from

exec usp_selectGrade '李小龙',@id output 

TestDataBase..Student

 

where

  4、 带通配符参数存款和储蓄过程

stuIsDel = 0

Create proc usp_one  @name nvarchar(10)

)

as

select -- 紧跟查询

select * from dbo.Student where StudentName like @name

num

 

, stuId

exec usp_one '李%'

, stuName

   5、 不缓存存款和储蓄过程

, stuSex

缓存便是数据调换的缓冲区(称作Cache),当某一硬件要读取数据时,会率先从缓存中搜寻须要的多寡,假设找到了则直接实施,找不到的话则从内部存款和储蓄器中找。由于缓存的周转速度比内存快得多,故缓存的效劳正是帮扶硬件越来越快地运作。

, stuBirthdate

Sql Server系统内部存款和储蓄器管理在未曾安排内部存款和储蓄器最大值,比较多时候大家会发掘运维Sql Server的连串内部存款和储蓄器往往只扩大不减少。那是由于她对于内存使用的国策是有多少闲置的内部存款和储蓄器就据有多少,直到内部存款和储蓄器使用虑达到系统峰值时(预留内部存款和储蓄器依照系统默许预留使用为准,至少4M),才会解决一些缓存释放一丢丢的内部存款和储蓄器为新的缓存腾出空间。

, stuStudydate

这一个内部存储器一般都是Sql Server运营时候作为缓存的,举个例子你运维叁个select语句, 实行个存款和储蓄进程,调用函数;

, stuAddress

  1. 多少缓存:实践个查询语句,Sql Server会将有关的数据页(Sql Server操作的数据都以以页为单位的)加载到内部存款和储蓄器中来,下贰回要是再度恳请此页的数指标时候,就无需读取磁盘了,大大进步了快慢。

, stuEmail

2.实践命令缓存:在试行存款和储蓄进度,自定函数时,Sql Server须要先二进制编译再运营,编写翻译后的结果也会缓存起来, 再一次调用时就不需求再一次编写翻译。

, stuPhone

 

, stuIsDel

create proc proc_temp with recompile as     select * from student exec proc_temp

, stuInputtime

 

, classId

6,加密存储进度

from t

exec sp_helptext 积存进程名      能够查看积攒进程代码

where t.num between (10-1)*9 1 and 9*10;

create proc proc_temp_encryption

 

with encryption

->视图(View)

as

可长久化的派生表

    select * from student;

create view vw_FenYeable    --视图名须求以vw早先

go

因为必需是批管理实践的语句 ,所以须要在前后加go

--存款和储蓄进度的内容不会被私行看出(尽管解密也可能有望的)。

go

--应用这些,我们得以对一些首要的积累进程举行加密。

 

--但此时,存款和储蓄进程仍是可以够被execute、alter和drop。

create view vw_FenYeable

exec proc_temp_encryption;

as

exec sp_helptext 'proc_temp'

select

exec sp_helptext 'proc_temp_encryption'

row_number() over(order by stuid) as num

(注意:加密囤积进程前相应备份原始存款和储蓄进程,且加密应该在配置到生育情状前成功。) 

, stuId

 

, stuName

一 存款和储蓄进程的概念

, stuSex

积存进度是在数据库管理连串保留的,预先编译的,能兑现某种意义的SQL程序,它是数据库应用中利用比较广泛的一种多少对象。

, stuBirthdate

何以需求仓库储存进度?

, stuStudydate

1.存款和储蓄进程只在创设时开展编写翻译,今后每一次实行存储进程都不需再另行编写翻译,而一般SQL语句每推行三次就编写翻译壹回,所以采取存款和储蓄进程可巩固数据库实施进度。 2.当对数据库实行复杂操作时,可将此复杂操功用存款和储蓄进程封装起来与数据库提供的事务管理结合一齐利用。 3.存储进程能够重复使用,可削减数据库开垦人士的工作量。 4.安全性高,可设定唯有少数客商手艺备对点名存款和储蓄进度的使用权

, stuAddress

储存进程的帮助和益处:

, stuEmail

1.模块化程序设计

, stuPhone

2.实行进度块,作用高

, stuIsDel

3.减弱互联网流量

, stuInputtime

4.具备卓越的安全性

, classId

 

from

二 系统存储进度

TestDataBase..Student

SQL_SE冠道VE大切诺基 提供系统存款和储蓄进程,它们是一组预编译的T-SQL语句,系统存储进程提供了管住数据库和更新表的机制,并出任从系统表中检索消息的飞快格局。

where

常用的系统存储过程

系统存储过程

说明

sp_databases

列出服务上的所有数据库

sp_helpdb

报告有关指定数据库或所有数据库的信息

sp_renamedb

更改数据库的名称

sp_tables

返回当前环境下可查询的对象的列表

sp_columns

返回某个表列的信息

sp_help

返回某个表的所有信息

sp_helpconstraint

查看某个表的约束

sp_helpindex

查看某个表的索引

sp_stored_procedures

列出当前环境中的所有存储过程

sp_password

添加或修改登录账户的密码

 

stuIsDel = 0

三 客户自定义的囤积进度

go

1.创设不带参数的存款和储蓄过程

 

Create proc usp_selectstu

select * from vw_FenYeable where vw_FenYeable.num between 9 * 10 1 and 10 * 10;

As

--视图无法积累数据,本质上依旧查询,目标是越来越好的赞助咱们实行查询。倘若老是查询三个繁杂操作的时候,选择使用视图

Select StudentName,Gender,GradeId,Phone from dbo.Student

 

 

->内联表值函数(*不做需要)

调用存款和储蓄进程:exec usp_selectstu

-- 正是包括参数的“视图”

2.创办带入参数的积累进程

-- 语法

Create proc usp_stuInfo @gradeid int=2       (默认)

/*

As

create function fn_函数名

Select * from student where gradeId=@gradeid

(@参数名 as 类型, @参数名 as 类型, ...)

调用存款和储蓄进度:exec usp_stuInfo 2

returns table

3.创办带出参数的蕴藏进度

as

create proc usp_selectGrade @name nvarchar(10),@gradeid int output

return

As

结果集

Select @gradeid=gradeid from student where  studentname=@name

*/

print @gradeid

 

 

定义时:

调用存款和储蓄进度:

go

declare @id int

create function fn_FenYe

exec usp_selectGrade '李小龙',@id output 

(@pageCount as int, @pageIndex as int)

 

returns table

  4、 带通配符参数存款和储蓄进程

as

Create proc usp_one  @name nvarchar(10)

return

as

select

select * from dbo.Student where StudentName like @name

num

 

, stuId

exec usp_one '李%'

, stuName

   5、 不缓存存款和储蓄进度

, stuSex

缓存便是数据交流的缓冲区(称作Cache),当某一硬件要读取数据时,会率先从缓存中检索须要的多少,假使找到了则直接实践,找不到的话则从内部存款和储蓄器中找。由于缓存的运维速度比内部存储器快得多,故缓存的功效正是帮忙硬件更快地运营。

, stuBirthdate

Sql Server系统内部存款和储蓄器管理在未有配备内部存款和储蓄器最大值,比非常多时候我们会意识运维Sql Server的体系内部存款和储蓄器往往只多相当多。那是由于她对于内部存款和储蓄器使用的攻略是有微微闲置的内部存款和储蓄器就占用多少,直到内部存储器使用虑达到系统峰值时(预留内部存款和储蓄器依照系统暗中同意预留使用为准,至少4M),才会去掉一些缓存释放一点点的内部存款和储蓄器为新的缓存腾出空间。

, stuStudydate

那些内部存款和储蓄器一般都以Sql Server运转时候作为缓存的,比如你运维一个select语句, 实施个存款和储蓄进程,调用函数;

, stuAddress

  1. 数码缓存:施行个查询语句,Sql Server会将有关的数据页(Sql Server操作的数量都以以页为单位的)加载到内部存储器中来,下一回如若重新伸手此页的数码的时候,就不要求读取磁盘了,大大进步了速度。

, stuEmail

2.实施命令缓存:在施行存款和储蓄进度,自定函数时,Sql Server需求先二进制编写翻译再运维,编写翻译后的结果也会缓存起来, 再度调用时就无需重新编译。

, stuPhone

 

, stuIsDel

create proc proc_temp with recompile as     select * from student exec proc_temp

, stuInputtime

 

, classId

6,加密存款和储蓄进程

from vw_FenYeable

exec sp_helptext 储存进度名      能够查看储存进度代码

where

create proc proc_temp_encryption

vw_FenYeable.num

with encryption

between (@pageIndex - 1) * @pageCount 1 and @pageCount * @pageIndex;

as

go

    select * from student;

 

go

使用时:

--存款和储蓄进度的内容不会被私自看出(尽管解密也许有相当的大可能率的)。

select * from fn_FenYe(20, 9);

--应用那些,我们能够对一些入眼的储存进度进展加密。

 

--但此时,存储进度仍旧能被execute、alter和drop。

 

exec proc_temp_encryption;

-- 将结果集实行演算,获得叁个新的结果集,并将其看作数据源实行查询

exec sp_helptext 'proc_temp'

 

exec sp_helptext 'proc_temp_encryption'

 

(注意:加密仓库储存进度前应当备份原始存款和储蓄进程,且加密应该在布置到生产条件前形成。) 

7.表的透视转换

 

将原始给的横表彰显成竖排情势:

 

 

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 学号

 

 

8.事务

在SQL Server中,私下认可的每一条语句都是三个事务 。

begin transaction

事务内容

->生效

commit transaction

->不生效

rollback transaction

 

如何知道事情是还是不是见效?

@@rowcount    记录上条语句受影响的行数

@@error    假使有错记录错误编码

 

 

 

 

事业:四个完好无缺,要么全体实行成功,要么全体推行倒闭

严峻的定义:假诺一个操作满意原子性、悠久性、隔开性与一致性,那么那个操作称为贰个专门的工作

原子性:不可划分,要么全体打响依旧全部前功尽弃

长久性:一旦事情试行到位,不可销毁

隔开性:假诺事情在实施操作时还未到位,别的三个事情必要实行那个操作相关时索要等待(阻塞)

一致性:一旦事情实现,不管在文书中、数据文件中、内部存款和储蓄器中的全体数据都以一模一样的

万一严峻依据事务的操作,会很影响属性可是进步安全性

 

->隔开性的兑现机制:锁

数据文件分块存款和储蓄 文件存款和储蓄模型:文件块

颗粒度:是锁定一行数据如故锁定一页数据就叫做颗粒度

共享锁(读)、排他锁(增删改)

->事务隔开等级

未提交读、已提交读、可再度读、种类化读、快速照相

 

9.存款和储蓄进度    procedure

将SQL语句封装起来,如同C#中的方法一致

意义:将一个实践的长河(也许会很复杂)封装成二个名字,然后利用那些名字就可以实行这些历程

语法:

create peoc[edure] 存款和储蓄进度名字

参数 as 类型 [默认值|output]    --此处的as能够省略

as

begin

代码

end

e.g.

go

create proc usp_test1

as

begin

begin transaction

declare @myError int;

update bank set balance=balance - 900 where cid='0002'

set @myError = (select @@ERROR);

update bank set balance=balance 900 where cid='0001'

set @myError = (select @@ERROR);

if(@myError = 0)

begin

commit

end

else

begin

rollback

end

end

go

 

->施行存款和储蓄进度

exec usp_test1;

 

->带参数的囤积进程

go

create proc usp_test2

@from as char(4)

, @to as char(4)

, @money as money

as

begin

begin transaction

declare @myError int;

update bank set balance=balance - @money where cid=@from

set @myError = (select @@ERROR);

update bank set balance=balance @money where cid=@to

set @myError = (select @@ERROR);

if(@myError = 0)

begin

commit

end

else

begin

rollback

end

end

go

 

->执行

exec usp_test2 '0001', '0002', -900;

 

exec usp_test2 @to='0002', @money=100, @from='0001';

 

->上述推行是或不是成功不能够看到,须要有重回值的储存进程

go

create proc usp_test3

@from as char(4)

, @to as char(4)

, @money as money

, @isSuccess int output -- 与C#一致,在积累进程里面赋值就可以

as

begin

begin transaction

declare @myError int;

update bank set balance=balance - @money where cid=@from

set @myError = (select @@ERROR);

update bank set balance=balance @money where cid=@to

set @myError = (select @@ERROR);

if(@myError = 0)

begin

commit

set @isSuccess = 1;

end

else

begin

rollback

set @isSuccess = 0;

end

end

go

->执行

 

 

而是此间即便能显得大家的结果,不过倘使出错结果为0可是依旧会报错,怎么工夫让它不报错呢?

->在SQL Server中的Try-catch

go

create proc usp_test4

@from as char(4)

, @to as char(4)

, @money as money

, @isSuccess int outputas

begin

begin transaction

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

 

积累进程是为了用贰个名字取代一串Sql语句,传输会快,分析无需也会快

之所以不管是大集团大概小商铺都会用存款和储蓄进程,可是也许有的公司会要求整个用C#的逻辑来写

->系统存储进程

exec sp_help;

->私下认可参数的仓库储存进度

go

create proc usp_FenYe1

@pageIndex int = 1        -- 第几页

,@pageCount int = 10-- 每页条数

as

begin

select * from fn_FenYe(@pageCount, @pageIndex);

end

go

 

10.触发器

近年来来说,用触发器的火候还十分少

触发器是哪些?

是三个出奇的蕴藏进程,假设用C#来比喻的话本质依然办法

不是协调调用,而是因为推行某一个错做二自动的接触,就疑似事件

使用

->监视操作

(能够记录操作的光阴、人物……)

->补充约束

(约束只好在一张表里,假若超过多张表还要对字段实行约束的话能够虚构进行封锁)

->语法

create trigger tr_类型_触发器的名字 on 表名

触发类型:after(在此之前)|instead of(代替,一般不要)

操作类型:insert|delete|update

as

begin

代码

end

两张不时表

inserted和deleted

 

 

 

 

 

11.逻辑与流程序调控制

-- 逻辑管理与流程序调节制

-- 接纳结构

/*

if        表达式

begin

 

end

else if        表达式

begin

 

end

else

begin

 

end

*/

 

-- 循环结构

/*

while 表达式

begin

 

end

*/

-- 定义变量

declare @num int;

-- 为变量赋值

set @num = 123;

-- 使用

select @num;

 

-- 求1到100的和

declare @sum int;

declare @i int;

set @sum=0;

set @i = 0;

 

while @i <= 100

begin

set @sum = @sum @i;

set @i = @i 1;

end

 

select @sum;

 

go

 

declare @sum int;

declare @i int;

set @sum=0;

set @i = 0;

 

while @i <= 100

begin

if @i % 2 = 0

begin

set @sum = @sum @i;

end

set @i = @i 1;

end

 

select @sum;

 

 

go

 

 

-- 系统变量平日使用@@来指引迷津

select @@VERSION

 

select @@ERROR;

 

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:SE途乐VEKuga数据库学习总括,数据库基础笔记分享

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。