设计数据库步骤,本文是个人学习SQL Server 数
分类:巴黎人-数据库

前言

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

  对于SQL SERFVER数据库也学了有一阵子了,自己也对自己所学做了一些总结。

DataSet的内容介绍,两种单例模式(并发量的考虑),SQL高级中的case语句、连接操作、子查询、派生表

1.什么是SQL语句

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

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

   我们首先学习数据库设计的一些知识点和用SQL语句建库。

 

sql语言:结构化的查询语言。(Structured Query Language),是关系数据库管理系统的标准语言。

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

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

  设计数据库步骤:需求分析阶段,概要设计阶段,详细设计阶段,

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

它是一种解释语言:写一句执行一句,不需要整体编译执行。

正文

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

   建数据库的SQL语句如下(包含了如果有该数据库先删除在建立数据库)

1.Case的用法

语法特点:

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

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

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

1.没有“ ”,字符串使用‘ '包含
2.没有逻辑相等,赋值和逻辑相等都是=
3.类型不再是最严格的。任何数据都可以包含在‘ '以内
4.没有bool值的概念,但是在视图中可以输入true/false
5.它也有关系运算符:> < >= <= = <> != ,它返回一个bool值
6.它也有逻辑运算符: !(not) &&(and) ||(or)
7.它不区别大小写

--把一个查询结果作为另外一个查询的查询源
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 = '濮阳语儿';


为了创建良好的数据库需满足三大范式。

->语法:

2.使用sql语句创建数据库和表

--把另外一个查询的结果作为当前查询的条件来使用。
--子查询中=、!= 、< 、> 、<= 、>=之后只能返回单个值,如果多个值就会报错
--解决办法 可以用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

 

 

 


下面是创建表的SQL语句(包含了如果有该表先删除在建表)

Case  表达式

语法:

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

 

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

-- 就是一段执行代码

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

when 值1 then 返回值

create database 数据库名称
on primary --默认在主文件组上
(
name='逻辑名称_data' , --当你发现它不是一句完整的sql语句,而仅仅是一个处理结构中的某一句的时候,就需要添加 ,
size=初始大小,--数值不包含在‘'以内
filegrowth=文件增长 ,
maxsize=最大容量,
filename='物理路径'
)
log on
(
name='逻辑名称_log' , --当你发现它不是一句完整的sql语句,而仅仅是一个处理结构中的某一句的时候,就需要添加 ,
size=初始大小,--数值不包含在‘'以内
filegrowth=文件增长 ,
maxsize=最大容量, --一般来说日志文件不限制最大容量
filename='物理路径'
)

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

-- 系统中常用的存储过程 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 返回值

--判断数据库文件是否已经存在 :数据库的记录都存储在master库中的sysdatabases表中
--自动切换当前数据库
--使用代码开启外围应该配置器

--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 执行时间:

语法如下

exec sp_configure 'show advanced options' ,1
RECONFIGURE
exec sp_configure 'xp_cmdshell',1
RECONFIGURE

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

alter table 表名

End

--自定义目录  xp_cmdshell可以创建出目录   'mkdir f:project':指定创建目录
exec xp_cmdshell 'mkdir f:project'

--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的显示成未删除

use master
--exists 函数判断()中的查询语句是否返回结果集,如果返回了结果集则得到true,否则得到false

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

if exists( select * from sysdatabases where name='School')
  drop database School --删除当前指定名称的数据库
create database School
on primary
(
 name='School_data',--逻辑名称.说明最多能够存储100mb数据,如果没有限制就可以将硬盘存储满
 size=3mb,--初始大小
 maxsize=100mb,--最大容量
 filegrowth=10%,--文件增长一次增长10%
 filename='f:projectSchool_data.mdf'  
),

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

创建外键约束

when 0 then N'未删除'

--创建文件组

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

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

when 1 then N'删除'

filegroup mygroup
(
 name='School_data1',--逻辑名称.说明最多能够存储100mb数据,如果没有限制就可以将硬盘存储满
 size=3mb,--初始大小
 maxsize=100mb,--最大容量
 filegrowth=10%,--文件增长一次增长10%
 filename='F:qiyiSchool_data1.ndf'  
)
log on
(
 name='School_log',--逻辑名称
 size=3mb,--初始大小
 --maxsize=100mb,--最大容量
 filegrowth=10%,--文件增长一次增长10%
 filename='f:projectSchool_log.ldf'  
),
(
 name='School_log1',--逻辑名称
 size=3mb,--初始大小
 --maxsize=100mb,--最大容量
 filegrowth=10%,--文件增长一次增长10%
 filename='F:qiyiSchool_log1.ldf'  
)

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 删除状态

3.创建数据表

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

create table 表名
(
字段名称 字段类型 字段特征(是否为null,默认值 标识列 主键 唯一键 外键 check约束),
字段名称 字段类型 字段特征(是否为null,默认值 标识列 主键 唯一键 外键 check约束)
)
创建老师表Teacher :Id、Name、Gender、Age、Salary、Birthday

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

创建变量语法如下是

UserInfo

use School
if exists(select * from sysobjects where name='Classes')
 drop table Classes
create table Classes
(
 Classid int identity(1,1),
 ClassName nvarchar(50) not null 
)
if exists(select * from sysobjects where name='teacher')
 drop table teacher
create table Teacher
(
 Id int identity(1,1),--可以同时创建多个特征,用空格 分隔开。 identity是标识列,第一个参数是种子,第二个是增量
Name nvarchar(50) not null,-- not null标记它的值不能为null--不能不填写
ClassId int not null,
 Gender bit not null,
Age int  ,
Salary money, --如果不标记为 not null.那么就相当于标记了null
Birthday datetime 
)

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

declare @变量名 数据类型

 

4.数据完整性约束

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

主键:非空和唯一.一个表只有一个主键,但是一个主键可以是由多个字段组成的 组合键
标识列:系统自动生成,永远不重复
唯一键:唯一,但是可以为null,只能null一次
域完整性:域就是指字段,它是为了保证字段的值是准和有效,合理值

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

select @变量名=value

->语法

类型 是否null,默认值,check约束,关系
自定义完整性:

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

区别是select可一次对多个变量赋值,set只能一次对一个变量赋值。

Case

check约束 , 存储过程 触发器
引用完整性:一个表的某个字段的值是引用自另外一个表的某个字段的值。引用的表就是外键表,被引用的表就是主键表

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

全局变量只能用不能自己创建和赋值!!!

when 表达式 then 返回值

1.建立引用的字段类型必须一致
2.建立引用的字段的意义一样
3.建立主外键关系的时候选择 外键表 去建立主外键关系
4.建立主外键关系的字段在主表中必须是主键或者唯一键

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

输出语句

when 表达式 then 返回值

5.对于操作的影响

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

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的显示成删除

级联的操作:不建议使用:会破坏数据完整性
不执行任何操作:该报错就报错,该删除就删除
级联:删除主表记录,从表引用该值的记录也被删除
设置null:删除主表记录,从表对应的字段值设置为null,前提是可以为null
设置为default:删除主表记录,从表对应的字段值设置为default,前提是可以为default
主键约束(PK Primary key)唯一键约束(UQ unique) 外键约束(FK foreign key) 默认值约束(DF default) check约束(CK check)

3.视图

go

select *,case

语法:

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

select * from StuInfos

when DelFlag=0 then N'未删除'

alter table 表名
add constraint 前缀_约束名称 约束类型 约束说明(字段 关系表达式 值)
use School
if exists(select * from sysobjects where name='PK_Classes_Classid')
 alter table classes  drop constraint PK_Classes_Classid
alter table classes
add constraint PK_Classes_Classid primary key(classid)
--为id添加主键
alter table teacher
add constraint PK_teacher_id primary key(id)
--为name添加唯一键
alter table teacher
add constraint UQ_Teacher_Name unique(Name)
--同时创建salary的默认约束和age的check约束
alter table teacher
add constraint DF_Teacher_Salary default(5000) for salary,
constraint CK_Teacher_Age check(age>0 and age<=100)
--为teacher表的classid字段创建主外键
if exists(select * from sysobjects where name='FK_Teacher_Classes_Classid')
 alter table teacher  drop constraint FK_Teacher_Classes_Classid
alter table teacher
with nocheck --不检查现有数据
add constraint FK_Teacher_Classes_Classid foreign key(classid) references classes(classid)
--on delete set default  级联操作
--不执行任何操作:该报错就报错,该删除就删除  --no action --默认选择
--级联:删除主表记录,从表引用该值的记录也被删除 --cascade
--设置null:删除主表记录,从表对应的字段值设置为null,前提是可以为null   --set null
--设置为default:删除主表记录,从表对应的字段值设置为default,前提是可以为default  --set default

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

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

when DelFlag=1 then N'删除'

5.四中基本字符类型说明

--在视图中的查询语句,必须为每一列创建一个列名
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'未知'

--len(参数) --获取指定参数内容的字符个数
select LEN('abcd') 【4】运行结果
select LEN('中华人民共和国') 【7】

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

drop table Classes

end as 删除状态

--DataLength(参数):获取指定内占据的字节数--空间大小
select DataLength('abcd') 【4】
select DataLength('中华人民共和国') 【14】

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

  go  

,UserName

--char类型:当空间分配后,不会因为存储的内容比分配的空间小就回收分配的空间。但是如果存储的内容超出了指定的空间大小,就会报错,当你存储的内容的长度变化区间不大的时候可以考虑使用char
select LEN(char) from CharTest 【2】
select DataLength(char) from CharTest 【10】

4.声明变量与使用

 create table Classes  

from

--varchar  var--变化的:当你存储的内容小于分配的空间的时候,多余的空间会自动收缩。但是如果存储的内容超出了指定的空间大小,就会报错 当存储的内容波动区间比较大时候使用varchar
select LEN(varchar) from CharTest 【2】
select DataLength(varchar) from CharTest 【2】

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

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

UserInfo

--nchar--  n代表它是一个unicode字符。规定不管什么样的字符都占据两个字节。  char:空间是固定的
select LEN(nchar) from CharTest 【10】
select DataLength(nchar) from CharTest 【20】

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

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

 

--nvarchar  n  var  char
select LEN(nvarchar) from CharTest 【2】
select DataLength(nvarchar) from CharTest 【4】

--输出值
print @name
print @age

go   

 

6.SQL基本语句

--使用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控制语句

insert into 表名([字段列表]) values(值列表) --数据必须要符合数据完整性
插入操作是单个表的操作
插入操作insert一次只能插入一条记录
use School
--插入teacher所有字段的数据.如果在表后没有指定需要插入的字段名称,那么就默认为所有字段添加值
--但是一定需要注意的是:标识列永远不能自定义值--不能人为插入值
--仅当使用了列列表并且 IDENTITY_INSERT 为 ON 时,才能为表'Teacher'中的标识列指定显式值。
insert into Teacher values('张三',5,1,30,4000,'1984-9-11')
insert into Teacher(Name,ClassId,Gender,Age,Salary,Birthday) values('张三',5,1,30,4000,'1984-9-11')
--不为可以为null的字段插入值  :可以null的字段可以不赋值
--列名或所提供值的数目与表定义不匹配
insert into Teacher(Name,ClassId,Gender,Age,Salary) values('李四',5,1,30,4000)
--非空字段一定需要赋值 :不能将值 NULL 插入列 'Gender',表 'School.dbo.Teacher';列不允许有 Null 值。INSERT 失败
insert into Teacher(Name,ClassId,Age,Salary) values('李四',5,30,4000)
--为有默认值的字段插入值:
--1.不写这一列让系统自动赋值
insert into Teacher(Name,ClassId,Gender,Age) values('王五',5,1,30)
--指定 null或者default
insert into Teacher(Name,ClassId,Gender,Age,Salary,Birthday) values('赵六',5,1,30,default,null)
--数据必须完全符合表的完整性约束
insert into Teacher(Name,ClassId,Gender,Age,Salary,Birthday) values('赵六1',5,1,300,default,null)

--声明变量并初始化

 alter table Classes

->SQL中的变量

--任意类型的数据都可以包含在''以内,     不包括关键字
insert into Teacher(Name,ClassId,Gender,Age,Salary,Birthday) values('马鹏飞','5','0','15',default,null)
--但是字符串值如果没有包含在''以内.会报错   列名 '兰鹏' 无效。
insert into Teacher(Name,ClassId,Gender,Age,Salary,Birthday) values('兰鹏','5','0','15',default,null)
--但是数值组成的字符串可以不使用''包含
insert into Teacher(Name,ClassId,Gender,Age,Salary,Birthday) values(123,'5','0','15',default,null)
--日期值必须包含在'‘以内,否则就是默认值
insert into Teacher(Name,ClassId,Gender,Age,Salary,Birthday) values('邹元标2','5','0','15',default,'1991-9-11')
数据删除

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 @变量名 变量类型

delete [from] 表名 where 条件
delete from Teacher where Age<20
--特点:
--1.删除是一条一条进行删除的
--2.每一条记录的删除都需要将操作写入到日志文件中
--3.标识列不会从种子值重新计算,以从上次最后一条标识列值往下计算
--4.这种删除可以触发delete触发器

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

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

->给变量赋值:

--truncate table 表名 --没有条件,它是一次性删除所有数据
--特点:
--1.一次性删除所有数据,没有条件,那么日志文件只以最小化的数据写入
--2.它可以使用标识列从种子值重新计算
--3.它不能触发delete触发器
truncate table teacher
数据更新(数据修改):一定需要考虑是否有条件

--打开事务
begin transaction

2.外键约束   为学生表添加一个clsid列来表示其班级   alter table StuInfos add clsid int   外键约束   

->set @参数名=值

语法:

--提交事务
commit transaction

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

->select @参数名=值

update 表名 set 字段=值,字段=值 。。where 条件
update Teacher set Gender='true'
--修改时添加条件
update Teacher set Gender=0 where Id=20
--多字段修改
update Teacher set ClassId=4,Age+=5,Salary=5000 where Id=22
--修改班级id=4,同时年龄》20岁的人员工资+500
update Teacher set Salary=Salary+500 where ClassId=4 and Age>20
数据检索--查询

--回滚事务
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

删除约束   

 

select */字段名称列表 from 表列表
select StudentNo,StudentName,Sex,[Address] from Student
--可以为标题设置  别名,别名可以是中文别名
select StudentNo as 学号,StudentName 姓名,性别=Sex,[Address] from Student
--添加常量列
select StudentNo as 学号,StudentName 姓名,性别=Sex,[Address] ,国籍='中华人民共和国' from Student
--select的作用
--1.查询
--2.输出
select 1+1
--+是运算符,系统会自动为你做类型转换
select 1+'1'
select '1'+1
--如果+两边都是字符串,那么它就是一字符串连接符
select '1'+'1'
select 'a'+1
--可以输出多列值
select 1,2,34,3,545,67,567,6,7
--Top、Distinct
select * from Student
--top可以获取指定的记录数,值可以大于总记录数.但是不能是负值
select top 100 * from Student
--百分比是取ceiling()
select top 10 percent * from Student

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

alter table StuInfos drop constraint fk_StuInfos_Classes

->打印 : Print @变量名

--重复记录与原始的数据表数据无关,只与你查询的结果集有关系 distinct可以去除结果集中的重复记录--结果集中每一列的值都一样
select distinct LoginPwd,Sex,Email from Student
select distinct Sex from Student

6.存储过程

3.建表同时建约束,老师表   编号     (主键 标识列)   名称     (非空)

 

select的作用
--聚合函数:
--1.对null过滤
--2.都需要有一个参数
--3.都是返回一个数值
--sum():求和:只能对数值而言,对字符串和日期无效
--avg():求平均值
--count():计数:得到满足条件的记录数
--max():求最大值:可以对任意类型的数据进行聚合,如果是字符串就比较拼音字母进行排序
--min():求最小值
--获取学员总人数
select COUNT(*) from Student
--查询最大年龄值
select  MIN(BornDate) from Student
select  max(BornDate) from Student

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

性别     (男或女,默认男)   年龄     (12-60)   电话     (唯一)   班级编号 (外键)   

->IF ELSE

--查询总分
select SUM(StudentResult) from Result where StudentNo=2
--平均分
select avg(StudentResult) from Result where SubjectId=1
--注意细节:
select  SUM(StudentName) from Student
select  SUM(BornDate) from Student

--输出存储过程
exec usp_HelloWorld

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

->语法格式:

select  min(StudentName) from Student
select  max(StudentName) from Student

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

drop table TeaInfos  

if(表达式)

--查询学号,姓名,性别,年龄,电话,地址 ---查询女生
select StudentNo,StudentName,Sex,BornDate,Address from Student where Sex='女' and BornDate >'1990-1-1' and Address='广州传智播客'
--指定区间范围
select StudentNo,StudentName,Sex,BornDate,Address from Student where  BornDate >='1990-1-1' and BornDate<='1993-1-1'
--between...and  >=  <=
select StudentNo,StudentName,Sex,BornDate,Address from Student where BornDate  between '1990-1-1' and '1993-1-1'
--查询班级id  1  3 5  7的学员信息
select * from Student where ClassId=1 or ClassId=3 or ClassId=5 or ClassId=7
--指定具体的取值范围--可以是任意类型的范围.值的类型需要一致--可以相互转换
select * from Student where ClassId in(1,3,'5',7)
select * from Student where ClassId not in(1,3,'5',7)

--输出值
exec usp_Add 100,230

 go  

begin

聚合函数
--带条件的查询-模糊查询-- 只针对字符串而言

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

 create table TeaInfos  

SQL语句

--查询  姓 林 的女生信息
--=是一种精确查询,需要完全匹配
select * from Student where Sex='女' and StudentName='林'
--通配符--元字符
--%:任意个任意字段  window:*  正则表达式 :.*
--_:任意的单个字符
--[]:代表一个指定的范围,范围可以是连续也可以是间断的。与正则表达式完全一样[0-9a-zA-Z].可以从这个范围中取一个字符
--[^]:取反值
select * from Student where Sex='女' and StudentName='林%'
--通配符必须在模糊查询关键的中才可以做为通配符使用,否则就是普通字符
--like   像 。。。。一样
select * from Student where Sex='女' and StudentName  like '林%'
select * from Student where Sex='女' and StudentName  like '林_'
--[]的使用  学号在11~15之间的学员信息
select * from Student where StudentNo like '[13579]'

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

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

end  

---处理null值
--null:不是地址没有分配,而是不知道你需要存储什么值  所以null是指   不知道。但是=只能匹配具体的值,而null根本就不是一个值
select COUNT(email) from Student where Email !=null
select COUNT(email) from Student where Email  is null
select count(email) from Student where Email  is not null
--将null值替换为指定的字符串值
select StudentName,ISNULL(Email,'没有填写电子邮箱') from Student where ClassId=2

--创建分页存储过程
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

模糊查询
--当你看到  每一个,,各自,不同,,分别  需要考虑分组
--查询每一个班级的男生人数
--与聚合函数一起出现在查询中的列,要么也被聚合,要么被分组
select classid,Sex,COUNT(*) from Student where Sex='男' group by ClassId,sex
--查询每一个班级的总人数,显示人数>=2的信息
--1.聚合不应出现在 WHERE 子句中--语法错误
select ClassId ,COUNT(*) as num from Student where Email is not null   GROUP by ClassId having COUNT(*)>=2 order by num desc
--完整的sql查询家庭
 --5                            1                      2                                 3                                     4                                           6                                                
--select 字段列表 from 表列表  where 数据源做筛选 group by 分组字段列表 having 分组结果集做筛选 Order by  对结果集做记录重排

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

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

begin

select ClassId ,COUNT(*) as num from Student where Email is not null   GROUP by ClassId order by ClassId desc

7.触发器

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

语句

--关于top的执行顺序 排序之后再取top值
select top 1 ClassId ,COUNT(*) as num from Student  GROUP by ClassId  order by num desc

尽量避免在触发器中执行耗时操作,因为触发器会与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

7.类型转换函数

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

  go

->语法:

--select :输出为结果集--虚拟表
--print:以文本形式输出  只能输出一个字符串值.

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

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

While(表达式)

print 1+'a'
select 1,2

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

declare   declare @temp int --声明   

begin

select * from Student

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

set @temp=1       --赋值  

SQL语句

--类型转换
--Convert(目标类型,源数据,[格式]) --日期有格式
print '我的成绩是:'+convert(char(3),100)

8.游标

 print @temp       --输出   

end

print '今天是个大日子:'+convert(varchar(30),getdate(),120)
select getdate()
select len(getdate())

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

  declare @temp int=1 --声明的同时赋值  

 

--cast(源数据  as  目标类型)  它没有格式
print '我的成绩是:'+cast(100 as char(3))

--2.打开游标
open cur_Student

 print @temp         --输出   

4.数据的连接

8.日期函数

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

 

主键不参与业务逻辑

--getdate():获取当前服务器日期
select GETDATE()
--可以在源日期值是追加指定时间间隔的日期数
select DATEADD(dd,-90,GETDATE())
--dateDiff:找到两个日期之间指定格式的差异值
select StudentName,DATEDIFF(yyyy,getdate(),BornDate) as age from Student order by  age
--DATENAME:可以获取日期的指定格式的字符串表现形式
select DATENAME(dw,getdate())
--DATEPART:可以获取指定的日期部分
select cast(DATEPART(yyyy,getdate()) as CHAR(4))+'-' +cast(DATEPART(mm,getdate()) as CHAR(2))+'-' +cast(DATEPART(dd,getdate()) as CHAR(2))

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

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

->交叉连接

9.数学函数

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

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

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

--rand:随机数:返回0到1之间的数,理论上说可以返回0但是不能返回1
select RAND()
--abs:absolute:取绝对值
select ABS(-100)
--ceiling:获取比当前数大的最小整数
select CEILING(1.00)
--floor:获取比当前数小的最大整数
select floor(1.99999)
power:
select POWER(3,4)
--round():四舍五入.只关注指定位数后一位
select ROUND(1.549,1)
--sign:正数==1  负数 ==-1  0=0
select SIGN(-100)

--3.关闭游标
close cur_Student

赋值方式二:用select 输出方式二:用select --

 

select ceiling(17*1.0/5)

--4.释放资源
deallocate cur_Student

2-- 声明三个变量存储'姓名、性别、年龄',再打印信息,格式如下: 姓名:杰达姆,性别:男,年龄:18 --声明

->内连接

10.字符串函数

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

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

一张表中有M条数据,另一张表中有N条记录,那么内连接后的数据不确定条数,要根据外键去讨论

--1.CHARINDEX --IndexOf():能够返回一个字符串在源字符串的起始位置。找不到就返回0,如果可以找到就返回从1开始的索引--没有数组的概念
--第一个参数是指需要查询的字符串,第二个是源字符串,第三个参数是指从源字符的那个索引位置开始查找
select CHARINDEX('人民','中华人民共和国人民',4)
--LEN():可以返回指定字符串的字符个数
select LEN('中华人民共和国')
--UPPER():小写字母转换为大写字母  LOWER():大写转小写
select LOWER(UPPER('sadfasdfa'))
--LTRIM:去除左空格  RTIRM:去除右空格
select lTRIM(RTRIM('                   sdfsd             '))+'a'
--RIGHT:可以从字符串右边开始截取指定位数的字符串  如果数值走出范围,不会报错,只会返回所有字符串值,但是不能是负值
select RIGHT('中华人民共和国',40)
select LEFT('中华人民共和国',2)
--SUBSTRING()
select SUBSTRING('中华人民共和国',3,2)
--REPLACE 第一个参数是源字符串,第二个参数是需要替换的字符串,第三个参数是需要替换为什么
select REPLACE('中华人民共和国','人民','居民')
select REPLACE('中        华      人民       共        和       国',' ','')
--STUFF:将源字符串中从第几个开始,一共几个字符串替换为指定的字符串
select STUFF('中华人民共和国',3,2,'你懂的')

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

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

->外连接

--sudyfsagfyas@12fasdf6.fsadfdsaf

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

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

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

declare @email varchar(50)='sudyfsagfyas@12fasdf6.fsadfdsaf'
select CHARINDEX('@',@email)
select LEFT(@email,CHARINDEX('@',@email)-1)

10.(补充)约束

--convert(varchar,@age)

表示将表1和表2通过“列名”作为对应关系进行连接,如果有数据没有相关对应,就看中间的连接方式right/left/full,如果是right就以右侧的数据为主,如果是left就以左侧的数据为主,如果是full就全部都显示,没有对应就补null;

--使用right
select right(@email,len(@email)-CHARINDEX('@',@email))
--使用substring
select SUBSTRING(@email,CHARINDEX('@',@email)+1,LEN(@email))
--使用stuff
select STUFF(@email,1,CHARINDEX('@',@email),'')

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

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

 

11.联合结果集union

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

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

 

--联合结果集union
select * from Student where Sex='男'
--union
select * from Student where Sex='女'

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

--3-- select * from StuInfo

 

--联合的前提是:
--1.列的数量需要一致:使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式
--2.列的类型需要可以相互转换
select StudentName,Sex from Student --在字符串排序的时候,空格是最小的,排列在最前面
union
select cast(ClassId as CHAR(3)),classname from grade

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

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

5.子查询

--union和union all的区别
--union是去除重复记录的
--union all不去除重复 :效率更高,因为不需要判断记录是否重复,也没有必须在结果庥是执行去除重复记录的操作。但是可以需要消耗更多的内存存储空间
select * from Student where ClassId=2
union all
select * from Student where ClassId=2

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

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

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

--查询office这科目的全体学员的成绩,同时在最后显示它的平均分,最高分,最低分
select ' '+cast(StudentNo as CHAR(3)),cast(SubjectId as CHAR(2)),StudentResult from Result where SubjectId=1
union
select '1','平均分',AVG(StudentResult) from Result where SubjectId=1
union
select '1','最高分',max(StudentResult) from Result where SubjectId=1
union
select '1','最低分',min(StudentResult) from Result where SubjectId=1

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

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

 

--一次性插入多条数据
--1.先将数据复制到另外一个新表中,删除源数据表,再将新表的数据插入到源数据表中
--1.select */字段  into 新表 from 源表
--1.新表是系统自动生成的,不能人为创建,如果新表名称已经存在就报错
--2.新表的表结构与查询语句所获取的列一致,但是列的属性消失,只保留非空和标识列。其它全部消失,如主键,唯一键,关系,约束,默认值
select * into newGrade from grade

--为年龄增加一个检查约束,年龄必须在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)

通常,将里面的查询叫做子查询,外面的查询叫做外部查询

truncate table grade
select *  from newGrade
--select * into grade from newGrade
--2.insert into  目标表  select 字段列表/* from  数据源表
--1、目标表必须先存在,如果没有就报错
--2.查询的数据必须符合目标表的数据完整性
--3.查询的数据列的数量和类型必须的目标的列的数量和对象完全对应
insert into grade select classname from newGrade
delete from admin
--使用union一次性插入多条记录
--insert into 表(字段列表)
--select 值。。。。 用户自定义数据
--union
--select 值 。。。。
insert into Admin
select 'a','a'
union all
select 'a','a'
union all
select 'a','a'
union all
select 'a',null

--增加外键约束,表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=stuSeat from stuinfo where stuname='李文才' select * from stuinfo  where stuSeat=@seat-1 or stuSeat=@seat+1

注意:子查询内部 必须查询一个数据

12.CASE函数用法

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 

 

相当于switch case---c#中的switch...case只能做等值判断
这可以对字段值或者表达式进行判断,返回一个用户自定义的值,它会生成一个新列。
2.要求then后面数据的类型一致
1.第一种做等值判断的case..end

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

总结: set和select的区别  

返回结果

case 字段或者表达式
when .值..then .自定义值
when .值..then .自定义值
.....
 else 如果不满足上面所有的when就满足这个else
end
--显示具体班级的名称
select StudentNo,StudentName,
case ClassId  --如果case后面接有表达式或者字段,那么这种结构就只能做等值判断,真的相当于switch..case
  when 1 then '一班'
  when 2 then '2班'
  when 3 then '3班'
  when null  then 'aa' --不能判断null值
  else  '搞不清白'
end,
sex
 from Student
--2.做范围判断,相当于if..else,它可以做null值判断
--case  --如果没有表达式或者字段就可实现范围判断
-- when  表达式  then 值   --不要求表达式对同一字段进行判断
-- when  表达式  then 值 
-- .....
--else  其它情况 
--end
select StudentNo,StudentName,
case
 when BornDate>'2000-1-1' then '小屁孩'
 when BornDate>'1990-1-1' then '小青年'
 when BornDate>'1980-1-1' then '青年' 
 --when Sex='女'  then '是女的'
 when BornDate is null then '出生不详'
 else  '中年'
end
 from Student

--用一条语句为表增加多个约束
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中作为一个值)

--百分制转换为素质教育  90 -A   80--B  70 --C  60 --D  <60 E  NULL--没有参加考试
select StudentNo,SubjectId,
case
    when StudentResult>=90 then 'A'
    when StudentResult>=80 then 'B'
    when StudentResult>=70 then 'C'
    when StudentResult>=60 then 'D'
    when StudentResult is null then '没有参加考试'
    else 'E'
end 成绩,
ExamDate
 from Result

后记

 2. set只能赋一个固定的值,而select能动态的赋值(值来自于表内) select的作用  

多值    多值子查询

13.IF ELSE语法

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

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

行(略)

1.没有{},使用begin..end.如果后面只有一句,可以不使用begin..end包含
2.没有bool值,只能使用关系运算符表达式
3.也可以嵌套和多重
4.if后面的()可以省略

 

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

列    一般放在in中作为批量删除、修改等操作或查询

declare @subjectname nvarchar(50)='office' --科目名称
declare @subjectId int=(select Subjectid from Subject where SubjectName=@subjectname) --科目ID
declare @avg int --平均分
set @avg=(select AVG(StudentResult) from Result where SubjectId=@subjectId and StudentResult is not null) --获取平均分
print @avg
if @avg>=60
 begin
   print '成绩不错,输出前三名:'
   select top 3 * from Result where SubjectId=@subjectId order by StudentResult desc
 end
else
  begin
    print '成绩不好,输出后三名:'
    select top 3 * from Result where SubjectId=@subjectId order by StudentResult 
  end

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

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

14.WHILE循环语法

--1-- 声明变量number,并赋值,然后判断是偶数还是奇数,结果如下: 当前值为11,它是一个奇数 declare @number int set @number=12 if(@number%2=0) print '该数为偶数' else print '该数为奇数' -

 

1.没有{},使用begin..end
2.没有bool值,需要使用条件表达式
3.可以嵌套
4.也可以使用break,continue

-2-- 根据输入的年龄显示人的生长阶段 未成年人<18,青年人<30,成年人<60,老年人<100,超人>=100

6.表表达式

go
declare @subjectName nvarchar(50)='office' --科目名称
declare @subjectId int--科目ID
declare @classid int =(select classid from Subject where SubjectName=@subjectName) --查询当前科目属于那一个班级
set @subjectId=(select SubjectId from Subject where SubjectName=@subjectName) --获取科目ID
declare @totalCount int --总人数 :那一个班级需要考试这一科目 
set @totalCount=(select COUNT(*) from Student where ClassId=@classid)
print @totalcount --14
declare @unpassNum int --不及格人数
set @unpassNum=(select COUNT(distinct Studentno) from Result where SubjectId=@subjectId and StudentNo in(select StudentNo from Student where ClassId=@classid) and StudentResult<60)
while(@unpassNum>@totalCount/2)
begin
 --执行循环加分
 update Result set StudentResult+=2 where SubjectId=@subjectId and StudentNo in(select StudentNo from Student where ClassId=@classid) and StudentResult<=98
 --重新计算不及格人数
 set @unpassNum=(select COUNT(distinct Studentno) from Result where SubjectId=@subjectId and StudentNo in(select StudentNo from  Student where ClassId=@classid) and StudentResult<60)
end

go
declare @subjectName nvarchar(50)='office' --科目名称
declare @subjectId int--科目ID
declare @classid int =(select classid from Subject where SubjectName=@subjectName) --查询当前科目属于那一个班级
set @subjectId=(select SubjectId from Subject where SubjectName=@subjectName) --获取科目ID
declare @totalCount int --总人数
set @totalCount=(select COUNT(*) from Student where ClassId=@classid)
print @totalcount --14
declare @unpassNum int --不及格人数
while(1=1)
 begin
   set @unpassNum=(select COUNT(distinct Studentno) from Result where SubjectId=@subjectId and StudentNo in(select StudentNo  from  Student where ClassId=@classid) and StudentResult<60)
  if(@unpassNum>@totalCount/2)   
    update Result set StudentResult+=2 where SubjectId=@subjectId and StudentNo in(select StudentNo from Student where ClassId=@classid) and StudentResult<=98
  else
     break
 end

declare @age int set @age=21

->派生表

15.子查询

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

select top 30

子查询:一个查询中包含另外一个查询。被包含的查询就称为子查询,包含它的查询就称父查询。
1.子查询的使用方式:使用()包含子查询
2.子查询分类:

print '青年人'

t1.stuId as 编号

独立子查询:子查询可以直接独立运行.
查询比“王八”年龄大的学员信息
select * from Student where BornDate<(select BornDate from Student where StudentName='王八')
相关子查询:子查询使用了父查询中的结果
--子查询的三种使用方式
--1.子查询做为条件,子查询接在关系运算符后面  >  < >= <= = <> !=,如果是接这关系运算符后面,必须保证 子查询只返回一个值
--查询六期班的学员信息
select * from Student where ClassId=(select ClassId from grade where classname='八期班')
--子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
select * from Student where ClassId=(select ClassId from grade)
--查询八期班以外的学员信息
--当子查询返回多个值(多行一列),可以使用in来指定这个范围
select * from Student where ClassId in(select ClassId from grade where classname<>'八期班')
--当没有用 EXISTS 引入子查询时,在选择列表中只能指定一个表达式。如果是多行多列或者一行多列就需要使用exists
--使用 EXISTS 关键字引入子查询后,子查询的作用就相当于进行存在测试。外部查询的 WHERE 子句测试子查询返回的行是否存在
select * from Student where  EXISTS(select * from grade)
select * from Student where  ClassId in(select * from grade)

else if(@age<60)

, t1.stuName as 姓名

--2.子查询做为结果集--
select top 5 * from Student --前五条
--使用top分页
select top 5 * from Student where StudentNo not in(select top 5 studentno from Student)
--使用函数分页  ROW_NUMBER() over(order by studentno),可以生成行号,排序的原因是因为不同的排序方式获取的记录顺序不一样
select ROW_NUMBER() over(order by studentno),* from Student
--查询拥有新生成行号的结果集  注意:1.子查询必须的别名  2.必须为子查询中所有字段命名,也就意味着需要为新生成的行号列命名
select * from (select ROW_NUMBER() over(order by studentno) id,* from Student) temp where temp.id>0 and temp.id<=5
select * from (select ROW_NUMBER() over(order by studentno) id,* from Student) temp where temp.id>5 and temp.id<=10
select * from (select ROW_NUMBER() over(order by studentno) id,* from Student) temp where temp.id>10 and temp.id<=15

print '成年人'

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

--3.子查询还可以做为列的值
select (select studentname from student where studentno=result.studentno),(select subjectname from subject where subjectid=result.SubjectId), StudentResult from Result

else if(@age<100)

, t3.final as 期末总评

--使用Row_number over()实现分页
--1.先写出有行号的结果集
select ROW_NUMBER() over(order by studentno),* from Student
--2.查询有行号的结果集 子查询做为结果集必须添加别名,子查询的列必须都有名称
select * from (select ROW_NUMBER() over(order by studentno) id,* from Student) temp where id>0 and id<=5
--查询年龄比“廖杨”大的学员,显示这些学员的信息
select * from Student where BornDate<(select BornDate from Student where StudentName='廖杨')
--查询二期班开设的课程
select * from Subject where ClassId=(select ClassId from grade where classname='二期班')
--查询参加最近一次“office”考试成绩最高分和最低分
--1查询出科目 ID
select subjectid fromSubjectwhereSubjectName='office'--2.查询出这一科目的考试日期select MAX(ExamDate)fromResultwhereSubjectId=(select subjectid fromSubjectwhereSubjectName='office')--3,写出查询的框架select MAX(StudentResult),MIN(StudentResult)fromResultwhereSubjectId=()andExamDate=()--4.使用子查询做为条件select MAX(StudentResult),MIN(StudentResult)fromResultwhereSubjectId=(select subjectid fromSubjectwhereSubjectName='office')andExamDate=(select MAX(ExamDate)fromResultwhereSubjectId=(select subjectid fromSubjectwhereSubjectName='office'))

print '老年人'

, t2.className as 课程

16.表连接Join

else print '超人'

, t1.stuPhone as 电话

--1.inner join :能够找到两个表中建立连接字段值相等的记录
--查询学员信息显示班级名称
select Student.StudentNo,Student.StudentName,grade.classname
from Student
inner join grade on Student.ClassId=grade.ClassId
--左连接: 关键字前面的表是左表,后面的表是右表
--左连接可以得到左表所有数据,如果建立关联的字段值在右表中不存在,那么右表的数据就以null值替换
select PhoneNum.*,PhoneType.*
from   PhoneNum 
left join  PhoneType on PhoneNum.pTypeId=PhoneType.ptId
--右连接: 关键字前面的表是左表,后面的表是右表
--右连接可以得到右表所有数据,如果建立关联的字段值在右左表中不存在,那么左表的数据就以null值替换
select PhoneNum.*,PhoneType.*
from   PhoneNum 
right join  PhoneType on PhoneNum.pTypeId=PhoneType.ptId
--full join :可以得到左右连接的综合结果--去重复
select PhoneNum.*,PhoneType.*
from   PhoneNum 
full join  PhoneType on PhoneNum.pTypeId=PhoneType.ptId

select * from StuInfo select * from StuExam

, t1.stuAddress as 家庭住址

17.事务

--3-- 统计笔试平均分,如果平均分超过70分打印'优秀' 同时显示前三名学员的考试信息否则      打印'较差'显示后三名学生信息

from

一种处理机制。以事务处理的操作,要么都能成功执行,要么都不执行。

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

TestDataBase..Student as t1

事务的四个特点 ACID:

select top 3 * from StuExam order by writtenExam desc end else begin print '本班成绩较差' select top 3 * from StuExam order by writtenExam end

inner join

A:原子性:事务必须是原子工作单元;对于其数据修改,要么全都执行,要么全都不执行。它是一个整体,不能再拆分
C:一致性:事务在完成时,必须使所有的数据都保持一致状态。。某种程度的一致
I:隔离性:事务中隔离,每一个事务是单独的请求将单独的处理,与其它事务没有关系,互不影响
D:持久性:如果事务一旦提交,就对数据的修改永久保留
使用事务:

三:循环结构 --1-- 输出九九次'我爱你' declare @i int=1  while(@i<=99) begin print '第'+convert(varchar,@i)+'我爱你' set @i+=1 end

TestDataBase..Course as t2

将你需要操作的sql命令包含在事务中。

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

1.在事务的开启和事务的提交之间
2.在事务的开启和事务的回滚之间

本文由巴黎人手机版发布于巴黎人-数据库,转载请注明出处:  设计数据库步骤,本文是个人学习SQL Server 数

上一篇:没有了 下一篇:没有了
猜你喜欢
热门排行
精彩图文