SQL Several

第一章：数据库基本知识储备
- 1.1 数据库简介
- 1.2 连接服务器
- 1.3 界面操作
- 1.4 知识补充
- 1.5 约束
第二章：基本数据库和表的搭建
- 2.1 建立数据库
- 2.2 建立表
- 2.3 基本知识储备
- 2.4 数据类型
第三章检索数据
- 3.1 selete语句
- 3.2 检索单个列
- 3.3 检索多个列
- 3.4 检索所有列
- 3.5 为列设置别名
- 3.6 常数查询
- 3.7 从结果中删除重复行
- 3.8 使用where选择记录
- 3.9运算符
第四章：排序检索数据
- 4.1 排序数据
- 4.2 按多个列排序数据
- 4.3 按列位置进行排序
- 4.4 制定排序方向
第五章：数据过滤
- 5.1 过滤数据
- 5.2 高级数据过滤
- 5.3 用通配符进行过滤
第六章：创建计算字段
第七章：用函数处理数据
- 7.1 处理数据
  - 7.1.1类型转换函数
- 7.2 汇总数据
  - 7.2.1聚集函数
  - 7.2.2开窗函数
- 7.3 分组数据
第八章：子查询
第九章：联结表
- 9.1 联结
- 9.2 高级联结
第十章：组合查询
- 10.1 union的使用
- 10.2 union的规则
- 10.3 对组合查询结果进行排序
- 10.4 分页
第十一章：插入更新删除数据
- 11.1 插入完整的行
- 11.2 插入部分行
  - 11.2.1 dentity
- 11.3 插入检索出的数据
- 11.4 从一个表复制到另一个表
- 11.5 更新数据
- 11.6 删除数据
第十二章：创建和操纵表
- 12.1 表创建基础
- 12.2 使用null
- 12.3 指定默认值
- 12.4 更新表
- 12.5 删除表
- 12.6 重命名表
第十三章：使用视图
第十四章：使用存储过程
第十五章：管理事务处理
- 事务的四大特性：
- 如何创建事务
- 注意问题
- 事务小例子
第十六章：使用游标
第十七章数据透视and tsql
- 数据透视
- tsql
第十八章索引
第二十章存储过程
第二十章后为补充笔记
第二十一章快速形成数据库
第二十二章基本知识补充
- 22.1批处理
- 22.2标识符
代表临时表和存储过程
- 代表一个全局临时表
- 22.3数据类型
- 22.4变量
- 22.5系统函数
- 22.5.4
- 22.6试验A 使用SQL Server联机帮助
第二十三章使用Transact-SQL的查询工具
- 23.1 SQL查询分析器
- 23.2 使用SQL 查询分析器中的对象浏览工具
- 23.3 执行Transact-SQL语句
第二十四章查询补充
第二十四章：高级SQL特性
- 锁

第一章：数据库基本知识储备

1.1 数据库简介

解决的问题：持久化存储，优化读写，保证数据的有效性
关系型数据库：基于E-R模型，使用SQL语句进行操作
分类：文档型和服务性
数据库设计: 三范式(列不可拆分，唯一标识，引用主键），关系及存储（1对1，一对多，多对多）
ddl:create drop alter
dml:select insert update delete
dcl:commit roolback grant revoke
--字符串和日期需要用单引号括起来
--数字常数无需单引号

1.2 连接服务器

开启服务
登录方式及设置
添加用户名
连接局域网中的数据库，启用tcp-ip协议

1.3 界面操作

1.3.1 连接

1.3.1 分离

1.3.1 附加

1.3.1 新建SQL sever账户权限说明

1.4 知识补充

1）字段属性列
2）元组记录行
3）表
4）主键外键
5) 常用辅助命令行
ctrl + e :执行
ctrl + r :隐藏消息栏
set statistics time on/off :
ctrl + l :执行任务

1.5 约束

主键约束：不允许重复元素，避免了数据的冗余
外键约束：通过外键约束保证了本事物所关联的其他事物一定是存在的（事物和事物之间的关系时通过外键来体现的）
check约束：保证事物的取值在合法的范围内

create table student
(
    stuid int primary key; --设置主键后必须赋值
    stu_sal int check (stu_sal >= 1000 and stu_sal <= 2000)
)

default约束：保证事物的属性一定会有一个值

create table student
(
    stuid int primary key;
    stu_sal int check (stu_sal >= 1000 and stu_sal <= 2000);
    stu_sex nchar(1) default('男') 
)

unique约束：保证了事物的取值不允许重复但允许为空（有且只有一列允许为空）

create table student
(
    stuid int primary key;
    stu_sal int check (stu_sal >= 1000 and stu_sal <= 2000);
    stu_sex nchar(1) default('男') ;
    stu_name nvarcjar(200) unique
)

not null 要求改用户必须为该属性附一个值，否则出错
isnull

select ename, sal * 12 + isnull(comm, 0) from emp
-isnull(comm, 0)如果comm是null就返回零，如果不是零就反回comm

第二章：基本数据库和表的搭建

2.1 建立数据库

on primary 
(
  name = 'stuDB_data';--主文件的逻辑名称
  filename = ' D:/stuDB_data.mdf';--主数据文件的物理名称
  size = 5mb;--主数据文件的初始大小
  maxsize = 100mb;--主数据文件增长的最大值
  filegrowth = 15%;--主数据文件的增长率
log on
(
  name = 'stuDB_log';
  filename = 'D:/stuDB_log.ldf';
  size = 2mb;
  filegrowth = 1mb
)
>使用数据库：use 数据库名

2.2 建立表

(
  列名 1 数据类型 列的约束；
  列名 2 数据类型 列的约束；
  列名 3 数据类型 列的约束；
  列名 4 数据类型 列的约束；
  ……
  表的约束1，表的约束2……
)

表的名称必须以半角英文名称作开头，表的名称不可以重复

2.3 基本知识储备

1)不区分大小写
2）脚本功能分类

DDL:数据定义语言，create，alter，drop
DML:数据管理语言，insert， update， selete
DCL:数据控制语言，revoke，grant
注释：-单行注释，/* */多行注释，Ctrl + e执行代码
-表的别名最好用双引号括起来，不要使用单引号

2.4 数据类型

2.4.1 字符型数据类型

char：1-255个字符的定长字符串
nchar：用来支持多字节和Unicode编码
nvarchar：用来支持多字节和Unicode编码

2.4.2 数值数据类型

int：4个字节整数值
float：浮点值
decimal：定长或精度可变的浮点值

2.4.3 日期和时间数据类型

date：日期值
datetime：日期时间值
time：时间值

2.4.4 二进制数据类型

ram：定长二进制数据
long ram:变长二进制数据

第三章检索数据

3.1 selete语句

select …… from …… where……
-select语句不回去除重复的记录
-select后如果更两个表，为两个表的笛卡尔积

3.2 检索单个列

select 列名 from 表名

3.3 检索多个列

select 列名一，列名二 from 表名

3.4 检索所有列

select * from 表名

3.5 为列设置别名

select 列名一 as 别名一，列名二 as 别名二 from 表名

3.6 常数查询

selete '名称' as string ,20 as number,'2019-09-15' as date,列名一，列名二 from 表名

3.7 从结果中删除重复行

select distinct 列名 from 表名
select distinct 列名一，列名二 from 表名
-distinct关键字只可以用在第一个列名前，区分的是后面列的组合
-使用distinct时，null也被视为一类数据

3.8 使用where选择记录

select 列名一 from 表名 where 条件表达式

3.9运算符

3.9.1算术运算符

/
select 列名一，列名二 * 3 from 表名
select ename, epno * 12 "年薪" from emp

其中列名二的数据类型为整形的时候，直接进行数学运算
所有包含null的计算，结果都为null，即使是null/0的运算（null不可以参与任何的计算，否则结果永远为空）

3.9.2比较运算符

<,>,>=,<=,<>
-数据库中不等于有两种表示方法，<>,!=,但是在数据库中尽量使用<>
-小于某个日期就是在该日期之前，某日起之后的查询条件使用>=
-对字符串进行比较运算时：
该字符串的数据原则上按照字典顺序进行排序，改规则适用于长字符串和可变长字符串
-不可以对null使用比较运算符（选取null是应该使用is null，选取不是null应该使用is not null）

3.9.1逻辑运算符

not：用来否定某个条件
and：其两侧的查询条件都成立时才成立
or：其两侧的查询条件只要有一个成立时就成立
-and的优先级高于or

第四章：排序检索数据

4.1 排序数据

select 列名 from 表名 order by 列名

4.2 按多个列排序数据

select 列名 from 表名 order by 列名一，列名二
-先按列名一进行排序，再按列名二进行排序

4.3 按列位置进行排序

select 列名一，列名二，列名三 from 表名 order by 2， 3

4.4 制定排序方向

select 列名 from 表名 where 条件表达式 order by 列名 desc
-默认是升序排列，asc，若改为desc，则为降序排列

第五章：数据过滤

5.1 过滤数据

检查单个值：select 列名 from 表明 where 条件表达式
范围检查：select 列名 from 表名 where 列名 between …… and ……
不匹配检查：where 后可以加<>或者 !=
空值检查：select 列名 from 表名 where 列名 is null

5.2 高级数据过滤

and
select 列名一，，列名二 from 表名 where 列名 = 值一 and 列名2 > 值二
or
select 列名一，，列名二 from 表名 where 列名 = 值一 or 列名2 > 值二
in：表示一个连续的范围内，可以代替多个and
not：
select 列名一，，列名二 from 表名 where 列名 is not null

5.3 用通配符进行过滤

-like操作符

%通配符：表示任何字符出现任意次（匹配零个一个或多个）

select * from student where tsname like '张%' and len(tsname = 2)
--等价于
select 8 from student where tsname liek '张_'

_通配符：匹配单个字符

[]通配符：匹配一个字符集，必须匹配指定位置的字符（此通配符可以使用^来否定，或者使用not）

%_写在[]中表示他们本身

select * from student where tsname like '张[%]%'

escape

select * from student where name like '%\_%' escape '\'
-这表示把name中所有包含‘_’字符的记录输出
-escape‘\’表示把‘\’字符当做转义字符的标志

第六章：创建计算字段

第七章：用函数处理数据

单行函数
多行函数

select lower(ename) from emp;--返回多行数据
select max(sal) from emp;--返回单行数据
-注意：select 后不可以同时存在单行和多行函数
select max(sal), lower(ename) from emp;--错误

7.1 处理数据

文本处理函数
| 函数 | 功能 |
| ---- | ---- |
| left | |
| right | |
| substring | |
| len | |
| lower | |
| upper | |
| ltrim | |
| rtrim | |
--索引从一开始而不是零
日期和时间处理函数
| 函数 | 功能 |
| ---- | ---- |
| getdate | |
| dateadd | |
| datediff | |
| datepart | |
| year | |
| month | |
| day | |
--注意：datepart， datediff， dateadd第一个参数适用双引号

数值处理函数
表格

7.1.1类型转换函数

cast(expression as data_type):将任意类型转化到任意类型
convert(data_type, expression[,style]):将任意类型转化到任意类型
如果目标是字符串，则style可以指定格式

--89.000000000
select cast(89.0000000 as decimal(4, 1))
--等价于
select convert(decimal(4, 1), 89.00000000)
--1 + '1'
select 1 + '1'--2
select cast(1 as char(1)) + '1'--11

7.2 汇总数据

7.2.1聚集函数

avg()函数:返回某列的平均值

--求语文科目的平均分
select avg(scorevalue) from subjectInfo
inner join ScoreInfo on subId = subjectInfo.sId
where stitle = '语文'

count()函数：返回某列的行数

1. 返回表中所有记录的个数 select count(*) from emp;
2. 返回字段非空的记录个数，重复的记录也会被当成有效记录 select count(deptno) from emp;
3. 返回字段不重复而且非空的记录个数 select count(distinct deptno) from emp;

max()函数：返回某列的最大值

--求学号为二的同学的最高分
select max(scorevalue) from ScoreInfo
where subId = 2

min()函数：返回某列的最小值

--求学号为二的同学的最低分
select min(scorevalue) from ScoreInfo
where subId = 2

sum()函数：返回某列值之和

7.2.2开窗函数

将统计的结果分布到原表的每一行，和聚合函数，排名函数使用

--聚合函数and开窗函数,将统计信息分不到行中
select scoreInfo.*, sum(scorevalue)
from scoreInfo
where sunId = 1
--报错，scoreINfo无效，因为该列没有包含到聚合函数group by 当中
select scoreInfo.*, avg(scorevalue) over()
from scoreInfo
where subId = 1

7.3 分组数据

7.3.1 创建分组

--分组
--男女各有多少人
select sGender, count(*) 
from StudentInfo
group by sGender
--每个人的平均分
select avg(scorevalue) from scoreInfo
group by subId
--根据多列分组
select subid, avg(scorevalue) from scoreInfo
group by sGender, subId

7.3.2 过滤分组

--统计学生编号大于二的各班级学生人数
select * 
from StudentInfo
where sid > 2
group by cid, sgender
--统计学生编号大于二的编号为三的班级的各性别学生人数
select cid, sgender,count(*)
from studentInfo
where sid > 2 and cid = 3
group by sgender
--统计学生编号大于二的各班级的各性别的学生人数大于三的信息
select cid, sgender, count(*) 
from studentInfo
where sid > 2
group by cid, sgender
having count(*) > 3

7.3.3 分组和排序

7.3.4 select子句顺序

--
select 
from
where 
group by
having
order by
--
select dinstinct top n *
from t1 join t2 on 
where 
group by 
having 
order by

7.3.5 判断下列SQL语句是否正确

select deptno, job, avg(sal) from emp 
    group by deptno, job;
    select deptno, job, avg(sal), count(*), sum(sal) from emp 
    group by deptno, job 
    order by deptno;
select deptno,avg(sal) as "平均工资
    from emp
    group by deptno
    having avg(sal) > 2000;
select deptno, avg(sal) as "平均工资"
    from emp
    group by deptno
    having count(*) > 3
select deptno, avg(sal) as "平均工资"
    from emp
    group by deptno
    having ename like '%A%'
--聚合函数是对最小分组信息进行查询
--having子句中不可以使用使用过聚合函数后赋值的别名，having子句里只可以有整体信息，不可以有详细信息

7.3.6 where和having的异同

相同点
都是对数据进行过滤
where和having一样，都不允许出现字段的别名，只允许出现最原始字段的名字（前提是where中出现经过聚集函数后赋值的别名）
不同点
where是对原始的记录过滤，而having是对分组后的数据进行过滤
where必须写在having的前面，顺序不可以颠倒，否则运行出错

select deptno，avg(sal) "平均工资", count(*) "部门人数"
    from emp 
    where sal > 2000
    group by deptno
    having avg(sal) > 3000

第八章：子查询

将一个查询语句嵌入到另一个查询语句中
出现在条件部分常用的运算符 = in exists
分页：已知页大小，页索引，查询当前页的数据

--查询参加过考试的学生信息
select * from StudentInfo
where sId in(select distinct stuid from ScoreInfo)
--等价于
select * from StudentInfo
where exists 
(select ScoreInfo where ScoreInfo.stuid = StudentInfo.stuid)
--分页查询：已知页大小（3）（一页显示多少条数据），页索引（1， 2， 3， 4）
select * from(
select *, row_number() over(order by sid desc) as row_index
from StudentInfo) as t1
where rowindex between 5 and 8
--1, 3  1, 3
--2, 3  4, 6
--3, 3  7, 9
--4, 3  10, 12
--(pageIndex - 1) * pagesize + 1
--pageIndex * pageSize

提示一：排名函数row_number(),结合开窗函数over（order by）进行查询
提示二：between and

第九章：联结表

9.1 联结

where

-工资大于2000的员工信息输出
select * 
	from emp "E", dept "D"
	where "E".sal > 2000 and "E".deptno = "D".deptno

inner join

-工资大于2000的员工信息输出
select *
	from emp "E"
	join dept "D"
	on "E".deptno = "D".deptno
	where "E".sal > 2000
-join on中的on既可以写过滤条件也可以写连接条件
select * from emp
    join on emp.deptno = dept.deptno and emp.sal > 2000

join and where

select "E" from ename, "D".dname, "S".grade
	from emp "E"
	join dept "D"
	on "E".deptno = "D".deptno
	join salgrade "S"
	on "E".sal >= "S".losal and "E".sal <= "S".hisal
	where "E".sal > 2000
--join中如果含有where，where放在join后面
--select ……from A,B where ……与select ……from A join B on 获得的结果是一样的，但是推荐使用的第二种

小例题

--输出部门名称不包含A的所有员工姓名，其中工资最高的前三名的每个员工的姓名，及工资
select top 3 "E"/ename, "E".sal, "S".grade, "D".dname
	from emp "E"
	join dept "D"
	on "E".deptno = "D".deptno
	join salgrade "S"
	on "E".sal between "S".losal and "S".hisal
	where "D".dname not like "%A%"
	order by "E".sal desc
--求出平均薪水最高的部门编号和部门的平均工资
第一步：
select top 1 deptno "部门的编号", avg(sal) "平均工资"
	from emp
	group by deptno
	order by avg(sal) desc
第二步：
select "E".*
	from(
			select deptno, avg(sal) "avg_sal"
				from emp
				group by deptno
		) "E"
	where "E".avg_sal = (
					select max(avg_sal) from (
										select deptno, avg(sal) "avg_sal
											from emp
											group by deptno
											)
						)
--好像有问题：记得重新听一遍课
--有一个人工资最低，把他排除掉，把剩下的工资最低的三个人的姓名，工资，部门编号，工资等级输出
select top 3 "E".ename, "E".sal, "E".deptno, "D".dname, "S".grade
	from(
		select *
			from emp "E"
			where sal > (select min(sal) from emp)
	) "E"
	join dept "D".deptno = "D".deptno
	join salgrade "S"
	on "E".sal between "S".losal and "S".hisal
	order by "E".sal asc
--求出emp表所有领导的信息
select  * from emp
    where empno not in (select mgr from emp)
-此语句有问题，会将null筛选出来（in 与 null 组合带来的问题）
--查找每个部门的编号，该部门所有员工的平均工资，平均工资的等级
第一步：
select deptno, avg(sal) as "avg_sal"
	from emp
	group by deptno
第二步：
select "T".deptno, "T"."avg_sal" "", "S".grade ""
	from(
		select deptno, avg(sal) as "avg_sal"
			from emp
			group by deptno
	) "T"
	join salgrade "S"
	on "T".avg_sal between "S".losal and "S".hisal
--注意：进过查询获得的临时表，如果想与另一张表进行连接，需要在写一个select，而不可以直接使用join
学科3 班级3
求： 每科 每个班 总成绩
select subJectName,Class,sum(mark) 总分from dbo.TStudent a join dbo.TScore b on a.StudentID=b.StudentID join
dbo.TSubject c on b.subJectID=c.subJectID
group by subJectName,Class
order by 总分desc

联合使用group by 子句和操作符rollup，将两列的详细信息和分组汇总
select subJectName,Class,sum(mark) from
TStudent a join TScore b on a.StudentID=b.StudentID join TSubject c on b.subJectID=c.subJectID
group by subJectName,Class with rollup

9.2 高级联结

使用表别名

自连接

-不使用聚合函数，求薪水最高的员工信息
select * from emp
	where empno not in (
		select distinct "E1".empno
			from emp "E1"
			join emp "E2"
			on "E1".sal < "E2".sal
-对于两个表都存在的列，不可以直接写列名，而应该写别名

自然连接

外连接

交叉连接

select * from emp cross join dept
等价于
select * from emp, dept

使用带聚集函数的连接

第十章：组合查询

10.1 union的使用

--输出每个员工的姓名，工资及上司姓名
select "E1".*, "E2".ename "上司的姓名"
    from emp "E1"
    join "E2"
    on "E1".mgr = "E2".empno
--这样是错误的，因为最大老板的上司是null，会存在一个员工的记录没有被查询出来
select "E1".*, "E2".ename "上司的姓名"
    from emp "E1"
    join "E2"
    on "E1".mgr = "E2".empno
union 
select ename, sal, '已近是最大的老板' from emp where mgr is null

10.2 union的规则

--联合查询
select cid from ClassInfo
--union 两者的共同部分，去除重复的部分
--union all两者的共同部分，不去除重复的部分
--except 差集
--intersect交集
select cid from ClassInfo

--uoion.表与表的纵向连接，列数相同，列的数据类型需要兼容

10.3 对组合查询结果进行排序

10.4 分页

-输出工资前三名的员工的所有信息
select top 3 * 
    from emp 
    order by sal desc
-工资由高到低，输出工资4-6的员工信息
select top 3 * 
    from emp
    where empno not in(
                        select top 3 * 
                            from emp 
                            order by sal desc
                        )
    order by sal desc
-工资由高到低，输出工资7-9的员工信息

第十一章：插入更新删除数据

11.1 插入完整的行

create table student
(
	student_id int primary key,
	student_name nvarchar(200) not null
)
insert into student values(1, '')--True
insert into student values('')--error
--若设置了主键必须为其赋值

11.2 插入部分行

create table student
(
	student_id int primary key,
	student_name nvarchar(200) not null
)
insert into student(student_name) values('')--True

11.2.1 dentity

主键自动增长，用户不需要为identity修饰的主键赋值
create table student
(
	student_id int primary key identity(100, 5),
	student_name nvarchar(200) not null
)
insert into student(student_name) values('')--True
insert into student values('')--True
--表中的数据删除，主键不在递增

11.3 插入检索出的数据

-- 将dbo.TStudent表中Class='网络班'的信息插入到dbo.TNetwork表中
insert dbo.TNetwork select * from dbo.TStudent where Class='网络班'

11.4 从一个表复制到另一个表

向未有表备份：select 列名 into 备份表名 from 源表名
说明：备份表可以不存在，会新建表，表的结构完全一致，但是不包含约束
如果只是想包含结构不包含数据，可以加上一个top 0
向已有表备份：insert into 备份表名 select列名 from 源表名
--想一个不存在的表中插入数据
select * into test1 from classInfo
--向一个存在的表中插入数据
insert into test2(列名)
select 列名 from ClassInfo

11.5 更新数据


8.7 更新数据

11.6 删除数据

删除基于其他表的行
下面的例子使用一个带delete语句的连接操作，删除不及格学生
Delete 条件使用子查询
delete dbo.TStudent where StudentID in (select distinct StudentID from dbo.TScore where mark<60)
dstinct 将重复的变为不重复。
Delete 后面多表连接
delete dbo.TStudent from dbo.TStudent a join dbo.TScore b on a.StudentID=b.StudentID where mark<60

第十二章：创建和操纵表

12.1 表创建基础

12.2 使用null

12.3 指定默认值

12.4 更新表

12.5 删除表

12.6 重命名表

第十三章：使用视图

什么是视图
视图从代码上看视图是一个select语句
视图从逻辑上来看是一个逻辑表
视图的格式

create view 视图的名字
    as 
          select 语句

视图的优缺点
优点：简化查询，增加数据的保密性
缺点：增加了数据库维护的成本，视图只是简化了查询，但是并不能加快查询的速度
使用视图的小例子1

--如果不使用视图
求出平均工资最高的部门编号和部门的平均工资
select * 
	from (
			select deptno, avg(sal) "avg_sal"
				from emp
				group by deptno
		) "T"
	where "T"."avg_sal" = (
		select max("E".avg_sal") from (
			select deptno, avg(sal) "avg_sal"
				from emp
				group by deptno
			) "E"
--如果使用视图
create view v$_emp_l
as 
	select depton, avg(sal) "avg_sal"
		from emp
		group by deptno
select * from v$_emp_l
	where avg_sal = (select max(avg_sal) from v$_emp_l)

*使用视图的小例子2

select * from StudentiInfo
inner join ClassInfo on Student.cod = ClassInfo.cid
where IsDelete = 0 and Ctitle = ''
--创建视图
create view Student_Class
as 
select * from StudentiInfo
inner join ClassInfo on Student.cod = ClassInfo.cid
--报错，各视图或者函数包含的列名必须唯一，StudentiInfo和ClassInfo都存在cid
--正确
create view Student_Class
as 
select StudentInfo.*, ClassInfo.Ctitle from StudentiInfo
inner join ClassInfo on Student.cod = ClassInfo.cid
--视图中存储的是select语句而不是结果集
select * from Student_Class
where IsDelete = 0 and Ctitle = ''
--删除视图
drop view Student_Class
--可以将视图名称当做一个表名，进行查询操作
--不要通过视图进行增删改，主要进行查询操作，若想删除多重视图，需要使用cascade选项删除关联视图
* 使用视图的限制
通常情况下：定义试图不要使用order by子句
定义的SQL语句满足某些条间时，那么这个视图就可以更新
* select 语句中未使用distinct，from语句中只有一张表，未使用group by 和 having

第十四章：使用存储过程

第十五章：管理事务处理

事务的四大特性：

事务是保证多个操作全部成功的时候才认为使用是一次有效的操作
（只有当数据发生改变的时候，增加删除修改才会引起事务，查询不会引发事务）

原子性：事务是一个完整的操作
一致性：事务完成时，数据必须处于一致状态，要么处于开始，要么处于结束
隔离性：当前事务与其他未完成的事务是隔离的
持久性：事务修改后，他对数据库的修改将永远被保存

如何创建事务

开始事务：begin transaction
提交事务：commit transaction
回滚/撤销事务：roolback transaction

注意问题

不可以在SQL中单独使用commit，roolback

事务小例子

begin transaction
declare @errorSum int 
set @errorSum = 0
update bank set currentMoney = currentMoney - 1000
    where customerEname = ''
set @errorSum = @erroeSum + @@error
update bank set currentMoney = currentMoney + 1000
    where customerEname = ''
set @errorSum = @errorSum + @@error
if(@errorSum <> 0)
    begin
            print ''
            rollback transaction
    end
--事务
select * from userInfo
begin try
	begin tran --设置反悔点
	insert into userInfo(username) values('123')
	insert into userInfo values('456')
	commit tran--不反悔提交
end try
begin catch
	rollback tran--反悔了，回滚事务
end catch

第十六章：使用游标

declare @stno char(6), @stname char(8) 
declare cur cursor for select stno, stname from student
open cur
fetch from cur into @stno, @stname
while @@FETCH_STATUS = 0 begin
	print @stno + ':' + @stname
end
close cur
deallocate cur

第十七章数据透视and tsql

数据透视

原表

经过转化的表

使用聚合函数的表

原表

经过转化的表

使用聚合函数的表

tsql

声明：declare 变量名类型-变量名要求以@开头
设置：set/select 变量名 = 值
输出：print/select 变量名
全局变量：使用双@符号
@@version 数据库版本
@@identity 进行插入后调用，返回最近的标识值
@@servername 服务器名称
@@error 返回执行上一个transacal-sql 语句的错误号
@@rowcount 返回上一条语句影响的行数
选择语句if
循环语句while
异常处理语句

begin try 
end try

--变量
declare @name nvarchar(10)
set @name = '有范围广'
print @name
--version
select @@version
--identity
insert into classinfo values('')
select @@identity
--servername
print @@servername
--error
insert into userinfo values('abc')
print @@error
--选择语句
declare @id int
set @id = 10
if @id > 5
begin
	print 'ok'
end
else
	print 'no'
end
--循环
declare @id int
set @id = 1
while @id < 10
begin
	print @id
	set @id = @id + 1
end
--小例题
--输出1-10之间的所有偶数
declare @num int
set @num = 1
while @num < 11
begin
	if @num % 2 =0
	begin 
		print @num
	end
	set @num = @num + 1
end
--异常处理
begin try 
	delete from classINfo
end try
begin catch
	print @@error
end catch

第十八章索引

第二十章存储过程

第二十章后为补充笔记

第二十一章快速形成数据库

形成数据库后对数据库的操作

--将以下语句拷贝到SQL manager，分部执行

--创建数据库
create database schoolDB
go
use schoolDB
go
--创建学生表
create TABLE TStudent (
  StudentID varchar(10) NOT NULL,
  Sname varchar(10) DEFAULT NULL,
  sex char(2) DEFAULT NULL,
  cardID varchar(20) DEFAULT NULL,
  Birthday datetime DEFAULT NULL,
  Email varchar(40) DEFAULT NULL,
  Class varchar(20) DEFAULT NULL,
  enterTime datetime DEFAULT NULL
 )
go
--创建课程表
create table TSubject
(
subJectID nvarchar(4),
subJectName nvarchar(30),
BookName nvarchar(30),
Publisher nvarchar(20)
)
go
--创建分数表
create table TScore
(
StudentID nvarchar(10),
subJectID nvarchar(4),
mark decimal
)
go
--插入课程信息
insert into TSubject values ('0001','网络管理','奠基计算机网络','清华出版社');
insert into TSubject values ('0002','软件测试','功能测试','人邮出版社');
insert into TSubject values ('0003','软件开发','企业级开发','人邮出版社')
go
--创建函数该函数能够产生学生姓名
create function create_name(@s float)
RETURNS varchar(10)
begin
DECLARE @LN VARCHAR(300);
DECLARE @MN VARCHAR(200);
DECLARE @FN VARCHAR(200);
DECLARE @LN_N INT;
DECLARE @MN_N INT;
DECLARE @FN_N INT;
SET @LN='李王张刘陈杨黄赵周吴徐孙朱马胡郭林何高梁郑罗宋谢唐韩曹许邓萧冯曾程蔡彭潘袁于董余苏叶吕魏蒋田杜丁沈姜范江傅钟卢汪戴崔任陆廖姚方金邱夏谭韦贾邹石熊孟秦阎薛侯雷白龙段郝孔邵史毛常万顾赖武康贺严尹钱施牛洪龚';
SET @MN='伟刚勇春菊毅俊峰强军平保东文辉力明永健世广志瑗琰韵融园艺咏卿聪澜纯毓悦昭冰爽琬茗羽希宁欣飘育滢馥新利筠柔竹霭凝晓欢霄枫芸菲寒伊亚宜可姬舒义兴良海山仁波宁贵福生龙元全国胜学祥亮政谦亨奇固之岚苑富顺信子杰涛昌成康星光天达安岩中茂进林有坚和彪博诚先敬震振壮会思群豪清飞彬娜静淑惠珠翠雅芝妍茜秋珊莎锦黛青倩婷姣婉娴瑾颖露瑶怡婵雁蓓纨仪荷丹蓉眉君琴蕊薇菁梦素伟刚勇毅俊峰强军平保东文辉力明永健世广志义兴良海山仁波宁贵福生龙元全国胜学祥才发武新利清飞彬富顺信子杰涛昌成康星光天达安岩中茂进林有坚和彪博诚先敬震振壮会思群豪心邦承乐绍功松善厚庆磊民友裕河哲江超浩亮政谦亨奇固之轮翰朗伯宏言若鸣朋斌梁栋维启克伦翔旭鹏泽晨辰士以建家致树炎德行时泰盛雄琛钧冠策腾楠榕风航弘';
SET @FN='伟刚勇毅俊云莲真环雪荣爱妹霞香月莺媛艳瑞凡佳嘉琼勤珍贞莉桂娣叶璧才发武丽琳轮翰朗伯宏言若鸣朋斌梁栋维启克伦翔旭鹏泽晨辰士以建家致树炎德河哲江超浩璐娅琦晶裕华慧巧美婕馨影荔枝思心邦承乐绍功松善厚庆磊民友玉萍红娥玲芬芳燕彩兰凤洁梅秀娟英行时泰盛雄琛钧冠策腾楠榕风航弘峰强军平保东文辉力明永健世广志义兴良海山仁波宁贵福生龙元全国胜学祥才发武新利清飞彬富顺信子杰涛昌成康星光天达安岩中茂进林有坚和彪博诚先敬震振壮会思群豪心邦承乐绍功松善厚庆磊民友裕河哲江超浩亮政谦亨奇固之轮翰朗伯宏言若鸣朋斌梁栋维启克伦翔旭鹏泽晨辰士以建家致树炎德行时泰盛雄琛钧冠策腾楠榕风航弘';
SET @LN_N=len(@LN)*@s;
SET @MN_N=len(@MN)*@s;
SET @FN_N=len(@FN)*@s;

return substring(@LN,@LN_N,1)+substring(@MN,@MN_N,1)+substring(@FN,@FN_N,1);
end
go
--测试是否能产生新的姓名
select dbo.create_name(rand())
go
--创建汉字转拼音的函数，用来产生用户的邮箱
create function dbo.fn_GetPinyin(@words nvarchar(2000))  
returns varchar(8000)  
as  
begin  
declare @word nchar(1)  
declare @pinyin varchar(8000)  
declare @i int  
declare @words_len int  
declare @unicode int  
set @i = 1  
set @words = ltrim(rtrim(@words))  
set @words_len = len(@words)  
while (@i <= @words_len) --循环取字符
begin  
set @word = substring(@words, @i, 1)  
set @unicode = unicode(@word)  
set @pinyin = ISNULL(@pinyin +SPACE(0),'')+   
(case when unicode(@word) between 19968 and 19968+20901 then   
(select top 1 py from (  
select 'a' as py,N'厑' as word  
union all select 'ai',N'靉'  
union all select 'an',N'黯'  
union all select 'ang',N'醠'  
union all select 'ao',N'驁'  
union all select 'ba',N'欛'  
union all select 'bai',N'瓸' --韛兡瓸
union all select 'ban',N'瓣'  
union all select 'bang',N'鎊'  
union all select 'bao',N'鑤'  
union all select 'bei',N'鐾'  
union all select 'ben',N'輽'  
union all select 'beng',N'鏰'  
union all select 'bi',N'鼊'  
union all select 'bian',N'變'  
union all select 'biao',N'鰾'  
union all select 'bie',N'彆'  
union all select 'bin',N'鬢'  
union all select 'bing',N'靐'  
union all select 'bo',N'蔔'  
union all select 'bu',N'簿'  
union all select 'ca',N'囃'  
union all select 'cai',N'乲' --縩乲
union all select 'can',N'爘'  
union all select 'cang',N'賶'  
union all select 'cao',N'鼜'  
union all select 'ce',N'簎'  
union all select 'cen',N'笒'  
union all select 'ceng',N'乽' --硛硳岾猠乽
union all select 'cha',N'詫'  
union all select 'chai',N'囆'  
union all select 'chan',N'顫'  
union all select 'chang',N'韔'  
union all select 'chao',N'觘'  
union all select 'che',N'爡'  
union all select 'chen',N'讖'  
union all select 'cheng',N'秤'  
union all select 'chi',N'鷘'  
union all select 'chong',N'銃'  
union all select 'chou',N'殠'  
union all select 'chu',N'矗'  
union all select 'chuai',N'踹'  
union all select 'chuan',N'鶨'  
union all select 'chuang',N'愴'  
union all select 'chui',N'顀'  
union all select 'chun',N'蠢'  
union all select 'chuo',N'縒'  
union all select 'ci',N'嗭' --賜嗭
union all select 'cong',N'謥'  
union all select 'cou',N'輳'  
union all select 'cu',N'顣'  
union all select 'cuan',N'爨'  
union all select 'cui',N'臎'  
union all select 'cun',N'籿'  
union all select 'cuo',N'錯'  
union all select 'da',N'橽'  
union all select 'dai',N'靆'  
union all select 'dan',N'饏'  
union all select 'dang',N'闣'  
union all select 'dao',N'纛'  
union all select 'de',N'的'  
union all select 'den',N'扽'  
union all select 'deng',N'鐙'  
union all select 'di',N'螮'  
union all select 'dia',N'嗲'  
union all select 'dian',N'驔'  
union all select 'diao',N'鑃'  
union all select 'die',N'嚸' --眰嚸
union all select 'ding',N'顁'  
union all select 'diu',N'銩'  
union all select 'dong',N'霘'  
union all select 'dou',N'鬭'  
union all select 'du',N'蠹'  
union all select 'duan',N'叾' --籪叾
union all select 'dui',N'譵'  
union all select 'dun',N'踲'  
union all select 'duo',N'鵽'  
union all select 'e',N'鱷'  
union all select 'en',N'摁'  
union all select 'eng',N'鞥'  
union all select 'er',N'樲'  
union all select 'fa',N'髮'  
union all select 'fan',N'瀪'  
union all select 'fang',N'放'  
union all select 'fei',N'靅'  
union all select 'fen',N'鱝'  
union all select 'feng',N'覅'  
union all select 'fo',N'梻'  
union all select 'fou',N'鴀'  
union all select 'fu',N'猤' --鰒猤
union all select 'ga',N'魀'  
union all select 'gai',N'瓂'  
union all select 'gan',N'灨'  
union all select 'gang',N'戇'  
union all select 'gao',N'鋯'  
union all select 'ge',N'獦'  
union all select 'gei',N'給'  
union all select 'gen',N'搄'  
union all select 'geng',N'堩' --亙堩啹喼嗰
union all select 'gong',N'兣' --熕贑兝兣
union all select 'gou',N'購'  
union all select 'gu',N'顧'  
union all select 'gua',N'詿'  
union all select 'guai',N'恠'  
union all select 'guan',N'鱹'  
union all select 'guang',N'撗'  
union all select 'gui',N'鱥'  
union all select 'gun',N'謴'  
union all select 'guo',N'腂'  
union all select 'ha',N'哈'  
union all select 'hai',N'饚'  
union all select 'han',N'鶾'  
union all select 'hang',N'沆'  
union all select 'hao',N'兞'  
union all select 'he',N'靏'  
union all select 'hei',N'嬒'  
union all select 'hen',N'恨'  
union all select 'heng',N'堼' --堼囍
union all select 'hong',N'鬨'  
union all select 'hou',N'鱟'  
union all select 'hu',N'鸌'  
union all select 'hua',N'蘳'  
union all select 'huai',N'蘾'  
union all select 'huan',N'鰀'  
union all select 'huang',N'鎤'  
union all select 'hui',N'顪'  
union all select 'hun',N'諢'  
union all select 'huo',N'夻'  
union all select 'ji',N'驥'  
union all select 'jia',N'嗧'  
union all select 'jian',N'鑳'  
union all select 'jiang',N'謽'  
union all select 'jiao',N'釂'  
union all select 'jie',N'繲'  
union all select 'jin',N'齽'  
union all select 'jing',N'竸'  
union all select 'jiong',N'蘔'  
union all select 'jiu',N'欍'  
union all select 'ju',N'爠'  
union all select 'juan',N'羂'  
union all select 'jue',N'钁'  
union all select 'jun',N'攈'  
union all select 'ka',N'鉲'  
union all select 'kai',N'乫' --鎎乫
union all select 'kan',N'矙'  
union all select 'kang',N'閌'  
union all select 'kao',N'鯌'  
union all select 'ke',N'騍'  
union all select 'ken',N'褃'  
union all select 'keng',N'鏗' --巪乬唟厼怾
union all select 'kong',N'廤'  
union all select 'kou',N'鷇'  
union all select 'ku',N'嚳'  
union all select 'kua',N'骻'  
union all select 'kuai',N'鱠'  
union all select 'kuan',N'窾'  
union all select 'kuang',N'鑛'  
union all select 'kui',N'鑎'  
union all select 'kun',N'睏'  
union all select 'kuo',N'穒'  
union all select 'la',N'鞡'  
union all select 'lai',N'籟'  
union all select 'lan',N'糷'  
union all select 'lang',N'唥'  
union all select 'lao',N'軂'  
union all select 'le',N'餎'  
union all select 'lei',N'脷' --嘞脷
union all select 'leng',N'睖'  
union all select 'li',N'瓈'  
union all select 'lia',N'倆'  
union all select 'lian',N'纞'  
union all select 'liang',N'鍄'  
union all select 'liao',N'瞭'  
union all select 'lie',N'鱲'  
union all select 'lin',N'轥' --轥拎
union all select 'ling',N'炩'  
union all select 'liu',N'咯' --瓼甅囖咯
union all select 'long',N'贚'  
union all select 'lou',N'鏤'  
union all select 'lu',N'氇'  
union all select 'lv',N'鑢'  
union all select 'luan',N'亂'  
union all select 'lue',N'擽'  
union all select 'lun',N'論'  
union all select 'luo',N'鱳'  
union all select 'ma',N'嘛'  
union all select 'mai',N'霢'  
union all select 'man',N'蘰'  
union all select 'mang',N'蠎'  
union all select 'mao',N'唜'  
union all select 'me',N'癦' --癦呅
union all select 'mei',N'嚜'  
union all select 'men',N'們'  
union all select 'meng',N'霥' --霿踎
union all select 'mi',N'羃'  
union all select 'mian',N'麵'  
union all select 'miao',N'廟'  
union all select 'mie',N'鱴' --鱴瓱
union all select 'min',N'鰵'  
union all select 'ming',N'詺'  
union all select 'miu',N'謬'  
union all select 'mo',N'耱' --耱乮
union all select 'mou',N'麰' --麰蟱
union all select 'mu',N'旀'  
union all select 'na',N'魶'  
union all select 'nai',N'錼'  
union all select 'nan',N'婻'  
union all select 'nang',N'齉'  
union all select 'nao',N'臑'  
union all select 'ne',N'呢'  
union all select 'nei',N'焾' --嫩焾
union all select 'nen',N'嫩'  
union all select 'neng',N'能' --莻嗯鈪銰啱
union all select 'ni',N'嬺'  
union all select 'nian',N'艌'  
union all select 'niang',N'釀'  
union all select 'niao',N'脲'  
union all select 'nie',N'钀'  
union all select 'nin',N'拰'  
union all select 'ning',N'濘'  
union all select 'niu',N'靵'  
union all select 'nong',N'齈'  
union all select 'nou',N'譳'  
union all select 'nu',N'搙'  
union all select 'nv',N'衄'  
union all select 'nue',N'瘧'  
union all select 'nuan',N'燶' --硸黁燶郍
union all select 'nuo',N'桛'  
union all select 'o',N'鞰' --毮夞乯鞰
union all select 'ou',N'漚'  
union all select 'pa',N'袙'  
union all select 'pai',N'磗' --鎃磗
union all select 'pan',N'鑻'  
union all select 'pang',N'胖'  
union all select 'pao',N'礮'  
union all select 'pei',N'轡'  
union all select 'pen',N'喯'  
union all select 'peng',N'喸' --浌巼闏乶喸
union all select 'pi',N'鸊'  
union all select 'pian',N'騙'  
union all select 'piao',N'慓'  
union all select 'pie',N'嫳'  
union all select 'pin',N'聘'  
union all select 'ping',N'蘋'  
union all select 'po',N'魄'  
union all select 'pou',N'哛' --兺哛
union all select 'pu',N'曝'  
union all select 'qi',N'蟿'  
union all select 'qia',N'髂'  
union all select 'qian',N'縴'  
union all select 'qiang',N'瓩' --羻兛瓩
union all select 'qiao',N'躈'  
union all select 'qie',N'籡'  
union all select 'qin',N'藽'  
union all select 'qing',N'櫦'  
union all select 'qiong',N'瓗'  
union all select 'qiu',N'糗'  
union all select 'qu',N'覻'  
union all select 'quan',N'勸'  
union all select 'que',N'礭'  
union all select 'qun',N'囕'  
union all select 'ran',N'橪'  
union all select 'rang',N'讓'  
union all select 'rao',N'繞'  
union all select 're',N'熱'  
union all select 'ren',N'餁'  
union all select 'reng',N'陾'  
union all select 'ri',N'馹'  
union all select 'rong',N'穃'  
union all select 'rou',N'嶿'  
union all select 'ru',N'擩'  
union all select 'ruan',N'礝'  
union all select 'rui',N'壡'  
union all select 'run',N'橍' --橍挼
union all select 'ruo',N'鶸'  
union all select 'sa',N'栍' --櫒栍
union all select 'sai',N'虄' --簺虄
union all select 'san',N'閐'  
union all select 'sang',N'喪'  
union all select 'sao',N'髞'  
union all select 'se',N'飋' --裇聓
union all select 'sen',N'篸'  
union all select 'seng',N'縇' --閪縇
union all select 'sha',N'霎'  
union all select 'shai',N'曬'  
union all select 'shan',N'鱔'  
union all select 'shang',N'緔'  
union all select 'shao',N'潲'  
union all select 'she',N'欇'  
union all select 'shen',N'瘮'  
union all select 'sheng',N'賸'  
union all select 'shi',N'瓧' --鰘齛兙瓧
union all select 'shou',N'鏉'  
union all select 'shu',N'虪'  
union all select 'shua',N'誜'  
union all select 'shuai',N'卛'  
union all select 'shuan',N'腨'  
union all select 'shuang',N'灀'  
union all select 'shui',N'睡'  
union all select 'shun',N'鬊'  
union all select 'shuo',N'鑠'  
union all select 'si',N'乺' --瀃螦乺
union all select 'song',N'鎹'  
union all select 'sou',N'瘶'  
union all select 'su',N'鷫'  
union all select 'suan',N'算'  
union all select 'sui',N'鐩'  
union all select 'sun',N'潠'  
union all select 'suo',N'蜶'  
union all select 'ta',N'襨' --躢襨
union all select 'tai',N'燤'  
union all select 'tan',N'賧'  
union all select 'tang',N'燙'  
union all select 'tao',N'畓' --討畓
union all select 'te',N'蟘'  
union all select 'teng',N'朰' --霯唞朰
union all select 'ti',N'趯'  
union all select 'tian',N'舚'  
union all select 'tiao',N'糶'  
union all select 'tie',N'餮'  
union all select 'ting',N'乭' --濎乭
union all select 'tong',N'憅'  
union all select 'tou',N'透'  
union all select 'tu',N'鵵'  
union all select 'tuan',N'褖'  
union all select 'tui',N'駾'  
union all select 'tun',N'坉'  
union all select 'tuo',N'籜'  
union all select 'wa',N'韤'  
union all select 'wai',N'顡'  
union all select 'wan',N'贎'  
union all select 'wang',N'朢'  
union all select 'wei',N'躛'  
union all select 'wen',N'璺'  
union all select 'weng',N'齆'  
union all select 'wo',N'齷'  
union all select 'wu',N'鶩'  
union all select 'xi',N'衋'  
union all select 'xia',N'鏬'  
union all select 'xian',N'鼸'  
union all select 'xiang',N'鱌'  
union all select 'xiao',N'斆'  
union all select 'xie',N'躞'  
union all select 'xin',N'釁'  
union all select 'xing',N'臖'  
union all select 'xiong',N'敻'  
union all select 'xiu',N'齅'  
union all select 'xu',N'蓿'  
union all select 'xuan',N'贙'  
union all select 'xue',N'瀥'  
union all select 'xun',N'鑂'  
union all select 'ya',N'齾'  
union all select 'yan',N'灩'  
union all select 'yang',N'樣'  
union all select 'yao',N'鑰'  
union all select 'ye',N'岃' --鸈膶岃
union all select 'yi',N'齸'  
union all select 'yin',N'檼'  
union all select 'ying',N'譍'  
union all select 'yo',N'喲'  
union all select 'yong',N'醟'  
union all select 'you',N'鼬'  
union all select 'yu',N'爩'  
union all select 'yuan',N'願'  
union all select 'yue',N'鸙'  
union all select 'yun',N'韻'  
union all select 'za',N'雥'  
union all select 'zai',N'縡'  
union all select 'zan',N'饡'  
union all select 'zang',N'臟'  
union all select 'zao',N'竈'  
union all select 'ze',N'稄'  
union all select 'zei',N'鱡'  
union all select 'zen',N'囎'  
union all select 'zeng',N'贈'  
union all select 'zha',N'醡'  
union all select 'zhai',N'瘵'  
union all select 'zhan',N'驏'  
union all select 'zhang',N'瞕'  
union all select 'zhao',N'羄'  
union all select 'zhe',N'鷓'  
union all select 'zhen',N'黮'  
union all select 'zheng',N'證'  
union all select 'zhi',N'豒'  
union all select 'zhong',N'諥'  
union all select 'zhou',N'驟'  
union all select 'zhu',N'鑄'  
union all select 'zhua',N'爪'  
union all select 'zhuai',N'跩'  
union all select 'zhuan',N'籑'  
union all select 'zhuang',N'戅'  
union all select 'zhui',N'鑆'  
union all select 'zhun',N'稕'  
union all select 'zhuo',N'籱'  
union all select 'zi',N'漬' --漬唨
union all select 'zong',N'縱'  
union all select 'zou',N'媰'  
union all select 'zu',N'謯'  
union all select 'zuan',N'攥'  
union all select 'zui',N'欈'  
union all select 'zun',N'銌'  
union all select 'zuo',N'咗') t   
where word >= @word collate Chinese_PRC_CS_AS_KS_WS   
order by word collate Chinese_PRC_CS_AS_KS_WS ASC) else @word end)  
set @i = @i + 1  
end  
return @pinyin  
END  
go
--执行以下命令查看函数效果
select dbo.fn_GetPinyin('韩立刚')
go
--创建添加学生的存储过程

create procedure addStudent @num int
as
begin
declare @i int;
set @i=1;
delete TStudent;
while @num>=@i
begin 
declare @sname varchar(40)
declare @sub int
set @sname=dbo.create_name(rand())
set @sub=rand()*4
insert TStudent (StudentID,Sname,sex,cardID,Birthday,Email,Class,enterTime)values (
right(replicate('0',10)+ltrim(@i),10),
@sname,
case when(rand()>0.5) then '男' else '女' end,
convert(nvarchar(16),convert(bigint,rand()*10000000000000000)),
'198'+convert(char(1),convert(int,rand()*9)+1)+'-'+convert(char(2),convert(int,rand()*11+1))+'-'+convert(char(2),convert(int,rand()*27+1)),
dbo.fn_GetPinyin(@sname)+'@91xueit.com',
case when(@sub<1) then '网络班' when (@sub>=1 and @sub<2) then '测试班' else '开发班' end,
getdate()
)
set @i=@i+1;
end 
end

--添加个学生
exec dbo.addStudent 1900
--查看添加的学生
select * from dbo.TStudent
go
--创建插入学生成绩的存储过程学生成绩在50-100分之间
create procedure fillSore 
as
DECLARE @St_Num INT;
DECLARE @Sb_Num INT;
DECLARE @i1 INT;
DECLARE @i2 INT;
set @i1=1;
set @i2=1;
delete TScore;
select @St_Num=count(*) from TStudent;
select @Sb_Num=count(*) from TSubject;
while @St_Num>=@i1
begin
set @i2=1;
while @Sb_Num>=@i2
begin
insert TScore values
 (right(replicate('0',10)+ltrim(@i1),10),right(replicate('0',4)+ltrim(@i2),4),50+rand()*50)
set @i2=@i2+1
end
set @i1=@i1+1
end
--插入成绩
--调用存储过程插入学生成绩
exec fillSore
select * from dbo.TScore 
--查看三张表连接的结果
select a.*,b.*,c.* from TStudent a join TScore b on a.StudentID=b.StudentID join TSubject c on b.subJectID=c.subJectID

第二十二章基本知识补充

22.1批处理

1.go
一个批处理命令通知SQLServer分析并运行一个批处理的所有指令
实际上不是 Transact-SQL语句，只是描述一个批处理。局部变量作用范围局限在一个批内，必须独占一行。
2.Exec
用于执行用户自定义的函数，系统存储过程，用户自定义的存储过程或扩展存储过程
注释语句
作为注释或在测试时是一些语句无效
嵌于行内的注释语句
USE schoolDB
go
select * from dbo.TScore–从那张表查找
where mark+9<60
GO

22.2标识符

-标准标识符
第一个字符是a－z 或A－Z
第一个字符后可以是数字，字母，或各种符号@,$，_
第一个字母是符号时表示有特殊的用途
@代表局部变量或参数

代表临时表和存储过程

代表一个全局临时表

-限定表示符
当对象名包含空格时,当用保留关键字被用作对象的名字时，必须使用括号和引号把限定标识符括起来
Use schoolDB
create table [order detail]
(
OrderID nvarchar(10),
OrderTime datetime
)

22.3数据类型

数字型代表数字 int tinyint smallint bigint 十进制小数 money smallmoney decimal 浮点数和real
日期型 datetime 可以精确到0.333毫秒 small
字符型包括char 和nchar 也包含变长字符类型varchar和nvarchar
定长字符 char(20)
变长字符 varchar(20)
Char 适合存放英文一个字符占用1个字节
Nchar 适合存放中文一个字符占用2个字节
二进制型 Binary和varbinary，bit代表一位的值0或1，rowversion代表数据库中唯一的8位二进制。
唯一标识代表一个全局特殊标识符（GUID），是一个16位16进制的值。
SQL变量包括SQL所支持的各种数据类型，但不包括text，ntext, rowversion和sql_variant这几种数据类

位图和文本（immage&text）属于大型二进制对象结构
表这种数据类型代表一个表结构，将一个表保存在一个字段中
游标这种数据类型用于存储过程的编程
用户自定义数据类型由数据库管理员生成，它基于系统数据类型，当多个表需要在一个字段存储同一类型数据时，这些字段具有相同的数据类型、长度和可控属性时，选择实用用户自定义的数据类型。

22.4变量

使用Declare定义局部变量，@局部变量作用域仅限于一个批处理中，@@全局变量在整个会话有效

use schoolDB
go
declare @sname nvarchar(11),@studentid nvarchar(20)
set @studentid ='0000000022'
select @sname=Sname from dbo.TStudent where StudentID=@studentid
select @sname as 姓名

22.5系统函数

聚集函数—对于一个集合中的值进行运算，返回一个单一的，汇总的值。

use northwind
select avg(unitprice) as AvgPrice from products
go

22.5.1

把时间转化成其他各式

select 'ansi:',convert(varchar(30),getdate(),102) as style
union
select 'Japanese',convert(varchar(30),getdate(),111)
union
select 'European',convert(varchar(30),getdate(),113)
go

22.5.2

运算符
比较运算符 = <> >=
字符串联运算符 + 空字符不等于空值
逻辑运算符 and or not

22.5.3

时间函数
select dateadd(yy,4,(getdate())) 表示对当时时间加4年
select dateadd(qq,2,(getdate())) 季度
select dateadd(mm,2,(getdate())) 月
select dateadd(dd,2,(getdate())) 日
select dateadd(wk,2,(getdate())) 周
select dateadd(hh,2,(getdate())) 小时
select dateadd(mi,2,(getdate())) 分钟
select dateadd(ss,2,(getdate())) 秒
select dateadd(dy,3,getdate())) 一年中的第几天
select datediff(day,‘2005-3-4’,getdate()) 返回时间差
select datepart(day,getdate())

22.5.4

流控制语言

判断语句
if 条件… else …
如：
declare @score decimal
set @score=rand()100
if (@score>60) print '*** 考试及格*****’
else print ‘**** 考试不及格*****’

case 条件 when 5 …
when 6 …
when 7 …
else … end

case when @score<60 then …
when @score>60 and @score <70 then…
else …
end

--判断条件
declare @score int 

set @score=(50+rand()*50)/10

select case @score when 5 then '**不及格**'

					when 6 then  '**及格**'

					when 7 then  '**良好**'

					else '**优秀**'

					end

--循环
declare @times int,@a varchar(100)

set @times=0

set @a='*'

while (@times<10)

begin 

print @a

set @a=@a+'*'

set @times=@times+1

end

declare @times int,@a varchar(100)

set @times=0

set @a='*'

while (@times<10)

begin 

print @a

set @a=@a+'*'

set @times=@times+1

end
while (@times>0)

begin 

print @a

set @a=left(@a,@times)

set  @times =@times -1

end

22.6试验A 使用SQL Server联机帮助

使用SQL帮助
使用管理工具产生创建表删除表修改表的SQL语句
动态构造SQL语句：

declare @tableName nvarchar(20)

set @tableName=convert(varchar(4),year(getdate()))+'年'+convert(varchar(2),month(getdate()))+'月'+convert(varchar(2),day(getdate()))+'日'

select @tableName 
--动态构造SQL语句
execute ('create table' +'  @tableName (studentid int,studentname nvarchar(10))')

下面是日期型转化成变长字符串，再将变长字符转化成日期型

declare @ch varchar(19)
declare @t datetime
set @t=getdate()
select @t
set @ch=convert(varchar(19),year(@t))+'-'+convert(varchar(19),month(@t))+'-'+convert(varchar(19),day(@t))
select @ch

第二十三章使用Transact-SQL的查询工具

23.1 SQL查询分析器

一个使用方便的文本编辑器
代码颜色转换
带有网格或文本输出的多重查询窗口
执行脚本一部分
查询执行的信息

23.2 使用SQL 查询分析器中的对象浏览工具

对象浏览器有如下功能
为对象生成脚本—生成创建对象的脚本，select insert update
执行存储过程—执行存储过程，当执行有参数的存储过程时，对象浏览器提示你输入参数的值
打开表----显示查询结果，可以编辑、插入或删除行
改变数据库中的对象
使用Transact-SQL模板
生成数据库，表，视图，索引，存储过程，统计数字和函数
管理扩展属性、连接的服务器、登陆帐号、角色和用户
声明及使用游标
定制脚本
执行Transact-SQL语句

23.3 执行Transact-SQL语句

在运行时动态构造并执行Transact-SQL语句
使用批组织一起要执行的语句
使用脚本文件保存批语句，以供日后使用

declare @dbname varchar(30),@tblname varchar(30)
set @dbname='northwind'
set @tblname='products'
execute
('use '+@dbname+' select * from '+@tblname)
Go

使用批
提交一个语句或同时提交多个语句，只有在SQL查询分析器或osql工具接受这个语句，在ODBC或ODBC API的应用程序使用go将会出现错误。SQL Server优化、编译并执行批中的语句，用户自定义的变量有效范围局限于一个批内部。
对象生成语句必须使用单独的批，批中不能同时出现的语句
Create procedure
Create view
Create trigger
Create rule as
Create default
使用脚本
使用SQL查询分析器或写字板之类的任何其它文本编辑器，编写并保存脚本，扩展名.sql

    最佳实践
    使用SQL查询分析器可以建立和SQL Server的多个连接，可为对象生成脚本的功能
    使用对象浏览工具来查找表和字段名，或为他们生成脚本
    使用模板生成对象
    运行批文件或执行重复性任务时，使用osql
    把经常使用的Transact-SQL语句保存在文件中，可以以后使用 。
    试验A 创建并执行Transact—SQL脚本

第二十四章查询补充

查询的工作原理

--非缓存的查询
所有查询在执行前，都需要经过一下步骤：解释、解析、优化和编译
解析----检查语句的语法是否正确
解析----校验语句中出现的对象名称是否有效，同时检查对象的所有权的权限
优化----检查是否能够使用索引并决定联合（jion）策略
编译----把查询翻译为一个可执行的表（from）
执行----把编译过程的查询要求提交并进行处理
--缓存的查询
为了提高性能SQLServer能够保存编译过的查询计划供以后使用，查询计划是经过优化的指令，他指定了如何处理插叙并访问数据。

查询是如何被自动缓存的
被缓存的查询保存在内存中一个叫做过程缓存的地方，在下列两种情况下，查询将被自动缓存—特定的批和自动参数化。
特殊的批-----SQL Server将为特殊的批保存查询计划，如果接下来的批和上一个批类似，SQLServer将使用缓存的计划。脚本文本必须匹配。

select * from products where unitprice = $ 12.5
select * from products where unitprice = 12.5
select * from products where unitprice = $ 13.5
go
查询语句1和3可以使用一个查询计划，但语句2必须使用另外的查询计划。
自动参数化
use library
select * from member where member_no=7890
select * from member where member_no=1234
select * from member where member_no=7890
go

影响性能的注意事项
尽量使用正逻辑而不是非逻辑，非逻辑操作（no between 、 not in 和not null）可能会降低查询速度，因为它要检索数据表中的所有行；
如果能够使用一个更确定的查询，就尽量避免使用关键字LIKE，使用LIKE查询，数据查询速度可能会降低；
只要有可能，尽量在搜索条件中使用精确的比较或值的域；
使用Order by 子句可能会降低数据查询速度，
-- 查询数据并操作结果集
计算数据，然后返回计算出的值，并使用字段别名
--通过使用字符串函数对结果集进行格式化

--拆分姓名为两列

select left(Sname,1) '姓',right(Sname,2) '名' from dbo.TStudentgo

查询过程中可使用系统函数。
如何得到服务器的进程ID
sp_who 显示服务器上所有正在产生的活动
select @@Spid 如果想知道那些活动是你的
exec sp_who 56 将显示和你的服务器进程号有关的所有活动
查询运行环境信息
select user_name()
select DB_name()
select @@servername

第二十四章：高级SQL特性

锁

--锁防止其他成员的误操作
select * from userInfo
begin tran
update userinfo set username = 'abc1' where userid = 10
rollback

第二十五章函数，case表达式
函数的种类：

算术函数
字符串函数
日期函数
转化函数
聚合函数

posted @ 2021-04-18 13:52 W-forever 阅读(334) 评论(0) 收藏举报

刷新页面返回顶部

W-forever