SQL server数据库加深学习

数据库查询模板：

一般我们的查询都是会按照这个模板进行

select 字段
from 表名
where 字段表达式
group by 字段,字段..
having 聚合函数表达式
order by 字段,字段..

原则：

1，找表：

根据题目确定表

2，找关键字：

①升序降序：order by。
②最大值，最小值，平均值，求和，求数量：聚合函数。
③不同的什么，按什么分组，有穷的什么，每一种：分组。

链接查询：

交叉连接：

作用：

将两张表或者多张表中的，相互匹配的数据展示出来。

select * from 表1 cross join 表2 where 表1.字段 = 表2.字段

示例：

 select * from Orders o cross join OrdersDetail od where o.ordersID = od.ordersID
 go

内连接：

作用：

将两张表或多张表中的相互匹配的数据展示出来。

笛卡尔积：

• 笛卡尔积的行数：表1行数*表2行数
• 笛卡尔积的列数：表1列数+表2列数

示例1：

select * from 表1 inner join 表2
//要是未加条件的内连接，会出现笛卡尔积。一旦加了条件，则相互匹配的数据就展示出来了。

语法：

select * from 表1 inner join 表2 on 表1.字段=表2.字段

示例：

select * from Orders o inner join  OrdersDetail od on o.ordersID = od.ordersID
 go

外连接：

左外连接：

左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

语法1：

select * from 表1 left join 表2 on 表1.字段 = 表2.字段

语法2：

select * from 表1 left outer join 表2 on 表1.字段 = 表2.字段

示例1：

select * from Orders o left join OrdersDetail od on o.ordersID = od.ordersID
 go

示例2：

select * from Orders o left outer join OrdersDetail od ?on o.ordersID = od.ordersID
 go 

右外连接：

左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

语法1：

select * from 表1 right join 表2 on 表1.字段 = 表2.字段

语法2：

select * from 表1 right outer join 表2 on 表1.字段 = 表2.字段 

示例1：

 select * from Orders o right join OrdersDetail od on o.ordersID = od.ordersID
 go

示例2：

select * from Orders o right outer join OrdersDetail od on o.ordersID = od.ordersID
 go

全连接(全外连接)：

左表和右表都不做限制，所有的记录都显示，两表不足的地方用null 填充。

语法：

select * from 表1 full join 表2 on 表1.字段 = 表2.字段

示例：

 select * from Orders o full join OrdersDetail od on o.ordersID = od.ordersID
 go

子查询：

定义：

子查询是一个嵌套在 select，insert，delete，upate 语句或者其他子查询的查询条件。任何预习使用表达式的地方都可以使用子查询。

执行原理：

先执行最内层的子查询并得到结果，将结果作为外部查询的条件，进行查询【由内到外】

子查询结果的分类：

单行子查询：

特点：

单行子查询返回的结果是单行单列。一般和 >,>=,<,<=,<>,!=一起使用。

示例：

select 商品标题=p.title,p.currentPrice 团购价,p.areaID 地区编号 from Product p where 
 p.areaID in(select a.areaID from Area a where a.areaName in('江汉路','中南路','取水楼'))
 go

多行子查询：

特点：

多行子查询的返回结果是多行数据，一般和 in，all，any|some使用。

①in

in类似于or

age in(10,20) 类似于?age = 10 or age = 20

语法：

select * from 表名 where 字段 in（子查询）

注意：in 后面子查询的结果是，任意类型。

示例：查询江汉路，中南路，取水楼地区的商品信息。要求输出商品标题，团购价和地区编号。

select 商品标题=p.title,p.currentPrice 团购价,p.areaID 地区编号 from Product p where 
 p.areaID in(select a.areaID from Area a where a.areaName in('江汉路','中南路','取水楼'))
 go

示例2：5，查询所有男顾客所购的商品信息，要求输出商品标题和商品团购价，并按照团购价升序排列。

select p.title 标题,p.currentPrice 团购价 from Product p where
 p.productID in ( select od.productID from OrdersDetail od where
 od.ordersID in(select o.ordersID from Orders o where
 o.customerID in(select c.customerID from Customer c where c.gender = '男')))
 order by 团购价 asc
 go

②all

all类似于最大值，或者最小值和关系运算符一起使用。

1, >all(15,20) 大于最大值，表示的就是大于15和20中，最大值20的数据。

2，< all(15,20) 小于最小值

语法：

select * from 表名 where 字段 > all(子查询)

注意：子查询的结果是，数字，字母，日期。

示例1：查询团购价比所有服装类商品团购价都高的商品信息，要求输出商品标题和团购价。

select p.title 商品标题,p.currentPrice as 团购价 from Product p where p.currentPrice > all (select p.currentPrice ?from Product p where p.categoryID = (select c.categoryID from Category c where c.categoryName = '火锅')) 
 go

③ any|some

any (18,20)或>some (18,20) 大于最小值。 大于18 <any (18,20)或<some (18,20) 小于最大值 =any (18,20)或=some (18,20) 与IN运算符等价。

子查询还可以出现在其他位置：

select子查询和insert，delete，update，sselect一起使用

语法1：

insert  into 新表 select * from 旧表

示例：

insert into admin1 select * from admin
//将admin表中数据拷贝至admin1表中，admin1已经创建

语法2：

delete from 表名  where 子查询

示例：

 delete from admin1 ?where adminID=(select a.adminID from Admin a where a.adminID = 1)
 go

语法3：

update 表名 set 字段=值 where 子查询

示例：

update admin1 set pwd = '666' where adminID = (select a.adminID from Admin a where a.adminID = '2')
 go

语法4：

select * from 表名 where 子查询

示例：

 select  
  COUNT(*) 商品数量,
  (select count(o.customerID) from Orders o)
 from Product p
 go

语法5：子查询还可以放在select子句中。

select 
(子查询),
(子查询)
from 表名

示例：查询商品数，已订购商品个数和和已订购商品件数。

SELECT COUNT(productID) 商品个数,(SELECT COUNT(DISTINCT productID) FROM OrdersDetail) 已订购商品个数,(SELECT SUM(saleCount) FROM Product)  已订购商品件数 FROM Product
go

语法6：

语法：

select * INTO 新表 from 旧表

示例：

select * from admin1 from admin
go
//创建一个新表，把旧表的数据，赋值到新表中。不会赋值表约束。

查询表中全部数据：

select * from admin where 1=1
go

查询表没有数据：

select * from admin where 1<>1

非相关子查询：

特点：依赖于外部主查询而存在的子查询。

执行原理：由内向外，子查询只执行一次，并将子查询的结果，作为外部查询的条件。

相关子查询：

特点：依赖于外部主查询，的子查询。

执行原理：

1，从主查询中取出一条记录，将该行记录相关列的值传给子查询。
2，执行子查询，得到子查询执行后的值。
3，主查询依据子查询返回的结果或者结果集得到满足条件的行。
4，主查询取出下一条记录，重复步骤1～3，直至外层主查询的记录全部处理完毕。

示例：查询“江汉路”地区的商品信息，要求输出商品标题和商品团购价。

相关子查询：

 select p.title 商品标题,p.currentPrice 团购价 from Product p 
 where '江汉路' = (select a.areaName from Area a where a.areaID = p.areaID)
 go

非相关子查询：

select p.title 商品标题,p.currentPrice 团购价 from Product p
 where  p.areaID = (select a.areaID from Area a where a.areaName = '江汉路')
 go

示例：

查询所有参与订购的服装类商品信息，要求输出商品标题和原价，按照原价升序排列。

select title 商品标题,originalPrice 原价 from product p 
where exists(select od.productID from OrdersDetail od where od.productID=p.productID
 and p.categoryID =(select categoryID from category where categoryName=‘服装’))
 order by originalPrice )

exists子查询：

语法：

1，exists（子查询）

子查询返回记录行，则表达式为true，否则为false

2，not exists(子查询)

子查询返回结果记录行，则表达式为false，否则为true

示例：

if exists (select * from sysdatabases  where name = 'meitao')
 print 'true'
 else
 print 'false'
 go

exists和in的使用场合：

①in适用于主查询结果行数较大，子查询结果较小的情况。外表大，内表小。
②exists适用于主查询结果行数较小，子查询结果较大。外表小，内表大。

连接查询和子查询的区别：

1，子查询就是查询中又嵌套的查询。

表连接都可以用子查询。但是不是所有的子查询都能用表连接查询。

2，子查询不一定需要两个表有关联字段，而连接查询必须要有字段连接（主外键关系）。

表连接的效率要高于子查询，因为子查询走的是笛卡尔积
表关联可能有多条记录，子查询只有一条记录，如果需要唯一的列，最好使用子查询。

T_SQL标识符命名规则：

标识符：

以ASCII字母，Unicode字母，下划线（_），@或#开头。其后可跟一个或若干个ASCII字符，Unicode字符，下划线（_），美元符号（$），@或#，但不能全为下划线(_)，@或#。

包含：数据库名，表名，视图名，索引名，存储过程，变量名，函数名等。

命名要求：

建议纯字母，且具备描述意义。

常规标识符不能是T_SQL保留字，如select，update等。

数据类型：

T_SQL中有两种数据类型：

1，系统数据类型。2，自定义类型。

①精确数字数据类型：

①int     存储整型数值
②bigint  bigint比int能存储更大的数值

②近似数字数据类型：

①float ? ? 存储小数点不固定的数值，存储的数值范围
②real ? ? ? 与float非常相似

③货币数据类型：

① money
② smallmoney

④字符数据类型：

①char ? 长度固定，最多可以定义8000个字符。
②nchar  与char类型相似，但最多可以定义4000个字符。
③varchar  用于存储字母数字数据，最多可定义8000个字符。varchar的每一行可以有不同的字符数，最大字符数未定义的最大长度。例如列定义为varchar(50)，则该列数据最多可以有50个字符长。然而，如果列中只存储了3个字符长的字符串，则只会使用3个字符的存储空间。如果定义列时没有指定大小，即varchar()，则其长度默认为1。
④ncarchar ? ? 与varchar相似

⑤日期和时间数据类型：

①date ? 存储日期。date数据类型的格式是YYYY-MM-DD。
②time ? 只存储基于24小时制的时间，其格式为hh:mm:ss[.nnnnnnn]。
③datetime  存储从1753年1月1日到9999年12月31日之间的任何日期和时间。datetime不仅存储日期，而且会在日期的旁边存储时间。

局部变量：

特点：作用域局限在一定范围内，由用户定义和维护，局部变量必须先定义后使用。

标识：

@

语法SQL server：

declare  @变量名 [as] 数据类型
declare  @ids ? [as] int  //示例

注意：

①局部变量名必须以@开始开头
②as可以省略
③赋初始值为null

定义SQL server：

①一次声明一个变量：
    declare @变量名  数据类型
②一次声明多个变量：
    declare @变量名 数据类型，@变量名 数据类型

局部变量赋值的三种方式SQL server：

1）在变量定义的时候赋值：

declare @变量名 [as] 数据类型  = 值

2）set

特点：一次只能给一个变量赋值，可以是值，也可以是直接查询出来的结果。

案例：

declare @ids int
set @ids = 10
set @ids = select语句

3）select

特点：可以一次给一个变量赋值，也可以给多个变量赋值。

案例：

declare @ids1 int ,@ids2 int
set @ids1 = 20
set @ids2 = 40
select @ids1 = 20,@ids2 = 40
select @i=字段 from 表名 where 字段表达式

全局变量：

全局变量由SQL Server系统定义，通常用来跟踪服务器范围和特定会话期间的信息，不能被用户显式地定义和赋值。通过访问全局变量来了解系统目前的一些状态信息。

全局变量以@@开头

常用的全局变量：

①@@error ? ? ? 上一条SQL语句报告的错误号。
②@@nestlevel ? 当前存储过程或触发器的嵌套级别。
③@@rowcount ? ? 上一条SQL语句处理的行数。
④@@servername ?  本地服务器名称。
⑤@@identity ? ?  最后插入的标识值。
⑥@@spid ? ? ? ? ? 当前用户进程的会话id。
⑦@@cpu_busy ? ? ?  SQL Server自上次启动后的时间状态。

示例：

select @@error ?  //137
select @@nestlevel ? ?  //0
select @@rowcount ? ? ? //1
select @@servername ?  //DESKTOP-3STU78B
select @@spid ? ? ? ? //52

逻辑判断：

begin ... end

作用：将多条sql语句限制在其中，作为一个逻辑块执行【类似于{}】

注意：至少包含一条sql语句，否者就会报错。

示例：

declare @xx int = 1
declare @total int = 0
while @xx <= 10
begin
set @total += @xx
set @xx +=1
end
print 'total的最后结果是：'+convert(varchar(20),@total)
go
//total的最后结果是：55

if ... else

作用：分支判断

语法：

if()
	begin
		....
	end
 else
 	begin
 		....
 	end

示例：输出1~10之间的奇数和偶数：

declare @x int = 1
declare @total int = 0
while @x <= 10
begin
	if(@x % 2 = 0)
		print '是偶数'+CONVERT(VARCHAR(20),@x)
	else
	print '是奇数：'+convert(varchar(20),@x)
	set @x += 1
end
go

case语句：可以分为简单的表达式和选择表达式。

case的简单表达式：

示例：

select  categoryName 商品小类名称,商品大类名称=
	case p_categoryID
			when 1 then '美食'
			when 2 then '酒店'
			when 3 then '电影'
			when 4 then '购物'
			when 5 then '休闲娱乐'
			when 6 then '生活服务'
	else categoryName
end
from Category
go

case的选择表达式：

示例：

select p.title as 商品标题,
	case
		when currentPrice/originalPrice <= 0.4 then '巨优惠'
		when currentPrice/originalPrice > 0.4 and 
		 currentPrice/originalPrice <= 0.6 then '很优惠'
		 when currentPrice/originalPrice > 0.6 and
		  currentPrice/originalPrice <= 0.8 then '一般优惠'
		  else '普通优惠'
end
from Product p

运算符：

1，算术运算符：

+、-、*、/、%(求余)

2，字符串运算符：

+（连接）

3，比较运算符：

=、>、>=、<、<=、<>(不等于)、!>(不大于)、!<(不小于)

4，逻辑运算符：

NOT、AND、OR、ALL(所有)、ANY(或SOME，任意一个)、BETWEEN…AND，EXISTS(存在)、IN(在范围内)、LIKE(匹配)

4，按位运算符：

&(位与)、|(位或)、^(按位异或)

5，一元运算符：

+(正)、-(负)、~(按位取反)

6，赋值运算符：

=(等于)

函数：

• 1，函数是用来完成，某种特定的功能。处理结果成为“返回值”，处理过程成为“函数体”。
• 2，函数又分为系统内置函数和用户自动以函数。SQL Server提供了大量系统内置函数，包括一下几类：数学函数、字符串函数、日期函数、convert函数、聚合函数。

数字函数：

①abs() ? ? 返回绝对值。
②ROUND(n,p,[t]) 返回n的四舍五入值，精确为p为正数时，就对小数点右边的数字进行四舍五入；如果为负数，就对小数点左边的数字进行四舍五入。可选参数t就删除n。
③trunc() ? ?  将数字截断到指定的位数
④% ? ? ? ? ? 求余数，SQL Server没有 mod(m,n)，而用m%n代替。
⑤PI() ? ? 返回圆周率(3.14…)
⑥RAND() ? 返回0-1之间的随机值，结果属于FLOAT数据类型。

示例：

select ABS(-55) ? ? //55
select PI() ? ? ? ? //3.14159265358979
SELECT ROUND(5.4567,3)； 结果为：5.4570
SELECT ROUND(5.4567,3,1)； 结果为：5.4560
SELECT ROUND(345.4567,-1)； 结果为:350.0000
select rand() ? ? ? ? ? //0.490677664392022

字符串函数：

①len ?  求字符串的长度。
②left(str,n) ? 返回字符串从左边开始的指定个数的字符。
③replace(str,search_str,rep_str) ?  替换一个字符串中的子串。search_str表示要搜索的子字符串，rep_str表示要替换的目标字符串。
④substring(str,position,length)  求子字符串
⑤ltrim(str,substr)/rtrim(str,substr) ? str表示要操作的字符串，substr表示要裁剪的子串，若裁剪空格，则可以省略。

示例：

select len('fsfs') ?  // 4
select left('sundaoen',3) ? // sun
select REPLACE ('sundaoen','dao','')            //sunen
select REPLACE ('sundaoen','d','sss')
        // sunsssaoen
select substring('hello',1,3)  // hel
Select RTRIM('hello ? ') ?  （hello ? ）

时间函数：

日期时间元素 ? ? ?  缩写 ? ? ? ? ? ? ? 含义
①year ? ? ? ? ? ?  yy,yyyy ? ? ? ? ? 年
②month ? ? ? ? ?  m,mm ? ? ? ? ? ? ?  月
③day ? ? ? ? ? ?  d,dd ? ? ? ? ? ? ?  日
④dayofyear ? ? ?  dy ? ? ? ? ? ? ?  年的天数
⑤week ? ? ? ? ? ? wk ? ? ? ? ? ? ? ? 星期数
⑥weekday ? ? ? ?  dw ? ? ? ? ? ? ? ? 星期几
⑦hour ? ? ? ? ? ? hh ? ? ? ? ? ? ? ? 时
⑧minute ? ? ? ? ? mi ? ? ? ? ? ? ? ? 分
⑨second ? ? ? ?  ss ? ? ? ? ? ? ? ?  秒

示例：

SELECT YEAR('20230811')--结果：2023
SELECT MONTH('20231115')--结果：1
SELECT DAY('20230817')--结果：17

?

日期函数：

getdate() ? ?  返回当前的日期和时间
year(日期) ? ?  返回指定日期的“年”部分的整数
month(日期) ? ? 返回指定日期的“月”部分的整数
day(日期) ? ? ? 返回指定日期的“日”部分的整数
SYSDATETIME()  返回当前系统时间
datepart() ?
datename(日期元素，日期)  以字符串的形式返回日期元素指定时间的日期名称回日期元素指定的日期部分的整数。
datediff(日期元素，日期1，日期2) 返回两个日期间的差值并将其转换为指定日期元素的形式。
dateadd(日期元素，数值，日期)  按照“日期元素”给定的日期单位，返回“日期”加上“数值”的新日期
isdate() 判断指定的字符串是否可以转换为时间格式 1可以转换，0不能转换。

示例：

1，select GETDATE()   // 2023-08-17 22:50:17.217
#当前日期加1天
1，SELECT DATEADD(Day,1,GETDATE())
//2023-08-19 10:38:46.297
#当前日期减1天
3，SELECT DATEADD(Day,-1,GETDATE())
//2023-08-17 10:38:46.297
#计算两个日期之间间隔几天
4，SELECT DATEDIFF(DAY,'2023-07-25','2023-08-18')                         //24
#计算两个日期之间间隔几个月
5，SELECT DATEDIFF(MONTH,'2023-01-01','2023-08-02')                         // 7
#计算两个日期之间间隔的年数
6，select DATEDIFF (YEAR,'2021-5-12','2023-08-18')                          //2
#判断字符串是否可以转换成时间格式
7，select ISDATE('20230812')   // 1
	select ISDATE('2023-10-32') // 0

数据类型转换函数：

• 1，隐式转换是SQL server自动地将数据从一行数据，转换为另一种数据类型，用户不可见。
• 2，显式转换使用convert函数，将一种数据类型的表达式强制转换为另一种数据类型。该函数的作用是：把数值型或日期型数据转换为字符串。

语法格式：

convert (数据类型(长度),表达式[,n])
函数的第四个参数n是可选的，用于时间日期型数据类型和字符数据类型转换。

不带世纪数位    带世纪数位   格式
① 1           101        mm/dd/yyyy
② 2           102        yy.mm.dd
③ 3           103        dd/mm/yyyy
④ 4           104        dd.mm.yy
⑤ 5           105        dd-mm-yy
⑥ 6           108        hh:mi:ss

示例：把日期时间转换为字符串类型。

1，select convert(varchar(20),GETDATE(),120)
//2023-08-18 15:53:26
2，select convert(varchar(20),GETDATE(),108)
//15:53:40
3，select convert(varchar(20),GETDATE(),101)
//08/18/2023
4，select convert(varchar(20),GETDATE(),105)
//18-08-2023

示例：把数字类型转换为日期时间类型。

1，select CONVERT(nvarchar(20),1234)
// 把整数123， 转换为字符串1234
2，select CONVERT(nvarchar(20),3.1415926)
//把浮点数 4.1415926 转换成字符串 3.1415926

示例：把字符类型转换为数字类型：

select CONVERT(int,'123') 
// 把字符串类型的123 转换为 整型 123
select CONVERT(float,'3.14')
//把字符型的3。14，转换成整型的3.14

数据库事务：

示例：

begin tran
declare @er int = 0
update bank set moneys -= 500 where bankId = 1
set @er += @@error
update bank set moneys+= 1000 where bankId = 3
set @er += @@error
if(@er <> 0)
	begin
	rollback tran
	print '转账失败'
	end
else
	begin
	commit tran
	print '转账成功'
	end
go

（一）什么是事务：

事务是逻辑上的一组数据库操作，要么都执行，要么都不执行。

事务的特性：

①原子性：

事务是最小的执行单元，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用。不允许部分成功和失败。

②一致性：

确保从一个正确的状态转换到另外一个正确的状态。举例，张三把钱转账给李四100元，张三少了100，李四多了100.但是他俩的钱加起来的钱数，还是和转账之前加起来的钱数相同。

③隔离性：

并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的。

④持久性：

事务被提交之后，对数据库中数据的改变是持久的，即使数据库发生故障，也不会对其有影响。

（二）事务之间的相互影响：

事务之间的相互影响分为一下几种：

脏读，不可重复读，幻读，丢失更新

脏读：

事例：老板给我发工资，1.8万每月。但是老板发工资的时候不下心按错了数字，按成了2.1万。该钱已经打到我的账户，但是事务还没有提交。就在这时，我查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成1.8万再提交。

当事务T1，正在访问字段A并且进行了修改，而这种修改还没有提交到数据库中。这时另一个事务，T2 也访问和使用字段A，但是由于事务T1修改字段A后还没有提交 commit，而那么事务T2读到的字段A，是“脏数据”。
?
(当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有commit提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据)

示例：

张三的工资为5000,事务A中把他的工资改为8000,但事务A尚未提交。
与此同时，
事务B正在读取张三的工资，读取到张三的工资为8000。
随后，
事务A发生异常，而回滚了事务。张三的工资又回滚为5000。
最后，
事务B读取到的张三工资为8000的数据即为脏数据，事务B做了一次脏读。

不可重复读：

事例：我拿着信用卡去享受生活（当然卡里只有1.8万），当我买单时（我的事务开启）。收费事先系统检测到我的卡里有1.8万，就在这个时候，我的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。

不可重复读取是指同一个事务在整个事务过程中对同一笔数据进行读取，每次读取结果都不同。如果事务1在事务2的更新操作之前读取一次数据，在事务2的更新操作之后再读取同一笔数据一次，两次结果是不同的。
?
在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

示例：

在事务A中，读取到张三的工资为5000，操作没有完成，事务还没提交。
与此同时，
事务B把张三的工资改为8000，并提交了事务。
随后，
在事务A中，再次读取张三的工资，此时工资变为8000。在一个事务中前后两次读取的结果并不致，导致了不可重复读。

幻读：

事例：我某一天去消费，花了2000元。然乎我的妻子去查看我今天的消费记录（全表扫描FTS，妻子事务开启），看到确实花了2000元，就在这个时候，我花了1万元买了一部电脑，新增INSERT了一条消费记录，并提交。当我妻子打我的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉。

在同一事务中，同一个查询多次返回的结果不一致。事务A新增了一条记录，事务B在事务A提交前后各执行了一次查询操作，发现后一次比前一次多了一条记录, 就好像发生了幻觉一样。


指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

示例：

目前工资为5000的员工有10人，事务A读取所有工资为5000的人数为10人。
此时，
事务B插入一条工资也为5000的记录。
这是，事务A再次读取工资为5000的员工，记录为11人。此时产生了幻读。

（三）数据库隔离级别和原理：

数据库索引：

索引存放在系统表 sysindexes表中

创建索引：

语法：

create [unique 或者 clustered  或者 nonclustered] index 索引名 on 表名（字段名）
go

默认情况下，创建的索引是非聚集的。

示例：

① create index ix_p on product(title)
go
② create unique index  ix_p1 on product(title)
go
③ ?create clustered ?index ix_p2 on product(title)
go  //报错了 表中有主键的情况下，不能创建聚集索引
④ ?create nonclustered ?index ix_p2 on product(title)
go

使用索引：

语法：

select * from 表名 with (index = 索引名)
go

示例：

select * from Product p with (index = ix_p) where p.categoryID in (select c.categoryID from Category c  )
go
 select * from Product with(index = ix_p1)
go

删除索引：

语法：

drop index 表名.索引名 
go

示例：

drop index product.ix_p
go   //删除在product表中的ix_p索引

修改索引：

语法：

 [索引名 或者 all] on 表名或者视图名

示例：

?

alter index ix_p on area

索引的分类：

唯一索引：

唯一索引不允许两行具有相同的索引值。一般在主键列或者在创建有唯一约束的列上创建。

主键索引：

为表定义一个主键，就会自动创建一个主键索引。主键索引是唯一索引的特殊类型。主键索引要求主键中的每一个值是唯一的，并且不能为空。

聚集索引(clustered)：

表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表中只能有一个。一个表中有主键存在，就不能创建聚集索引。

非聚集索引（nonclustered）：

非聚集索引指定表中的逻辑顺序。数据存储在一个位置，索引存储在另一个位置。索引中包含指向数据存储位置的指针，可以有多个，小于249个。

索引的优缺点：

优点：

①加快访问速度
②将强行的唯一性

缺点：

①浪费存储空间
②对数据更新的同时还需要对索引进行更新
③更新效率变低

创建索引的依据：

推荐：

①该列频繁用于手搜索
②该列用于对数据进行更新
③该列重复值少

不推荐：

①表中重复的值
②表中数据量较少
③频繁的进行插入列

索引的注意事项：

①一个列可以创建多个索引，并且在一个列上可以创建聚集索引也可以创建非聚集索引。
##创建非聚集索引
①一个表中主键约束，就不能创建聚集索引了。
②一个表只能有一个聚集索引
③主键索引一定是唯一索引，也是聚集索引，同时也是主键索引。
⑤唯一索引不一定是聚集索引或非聚集索引

索引失效的几种情况：

1，查询的数据是大表的大部分：

单次查询如果查出表的大部分数据，这会导致编译器认为全表扫描比通过索引更好，从而导致索引失效。一般单次查询数量大概栈大表的30%以上，索引就会失效。

2，索引本身失效：

说明：索引需要定期重建。

重建的原因主要包括：
①删除的空间没有重用，导致索引出现碎片化。
②删除大量的表数据后，空间美有重用，导致索引‘虚高’
③索引的 clustering_facto 和表不一致。

3，查询条件使用函数在索引项上：

select * from admin where round(id) = 10
//此时id索引就不起作用了。
正确的例子：
首先建立函数索引：create index ix_id on admin(round(id));
这样函数索引就起作用了。

4，对小表进行查询：

对于数据量少的表，本身不需要创建索引，如果建立索引，索引未必生效。

5，隐式转换导致索引失效：

这一点要引起重视，也是开发过程中经常翻的错。
admin表中的字段，pwd定义为varchar(20)，但是在查询时把该字段作为number类型以where条件传给oracle，这样会导致索引失效。
##错误的例子：
select * from admin where pwd = 123
go
select * from admin where pwd = '123'
go

6，对索引进行运算导致索引失效：

对索引的运算包括（+，-，*，/，！）等
##错误的例子：
select * from admin where id - 1 = 9
go
##正确的例子：
select * from admin where id = 10
go

7，在where子句中似乎用<>或者!=

形如：
select * from admin where 1 <> 1这种情况一般不走索引

8，like ‘%......’，百分号在前（like '%XX' 或者like '%XX%'）

形如：
select * from admin where name line '%孙'
//不走索引
select * from admin where name like '%道%'
//不走索引

9，not in，not exists

说明：使用not in，not exist一般不走索引
在使用not in的时候，需要保证子查询的匹配字段是非空的。不然就会导致 not in返回的整个觉果集为空。

10，is null或者is not null

?

11，条件中有or

select * from admin where id = 10 or pid =10
go ? //此类查询必须满足id和pid均有索引，如果只是id或者pid存在索引，索引就不会生效。
##修改为：
select * from admin where id = 10 union select * from admin where pid = 10
go ?  //这样修改就不需要满足id和pid均有所有的条件。
?

数据库视图：

视图存放在系统表sysobjects表中

视图定义:

包含一个或多个表的部分数据的一个虚拟表

创建视图：

语法：

create view 视图名 操作表
go

示例：

create view v_p_c1 as
select c.categoryName,p.title,p.currentPrice  from Product p,Category c where p.categoryID = c.categoryID
go

使用视图：

语法：

select * from 视图名

示例：

select * from v_p_c1
go

修改视图：

语法：

alter view 视图名 as select新的查询条件

示例：

alter view v_p_c1 as select p.title from Product p where p.areaID in (select c.categoryID from Category c group by c.categoryID)
go

删除视图：

语法：

drop view 视图名

示例：

drop view v_P_c1
go

视图的优缺点：

1，视点集中：

允许用户只关心某些特定的数据及特定任务

2，简化操作：

隐藏表与表之间复杂的连接操作

3，定制数据：

实现以不同的方式查看数据集

4，合并分割数据：

使用视图水平或垂直分割数据量大的表，可以重新保持原有的结构关系，从而使外模式保持不变，原有的应用程序仍可以通过视图来访问数据。

5，安全性：

视图作为一种安全机制，用户只能查看和修改它们所能看到的数据，其他数据库或表也不可以访问。

视图的注意事项：

①如果不违背任何约束则可以通过操作视图来操作基表（视图的操作会影响基表）
update v_p_c1 set currentPrice +=5
go
?
②视图是用于显示并非是用于存储的

数据库的同义词：

同义词存在与sysobjects系统表中

定义：同义词就是为了简化对数据库对象的访问而创建的对象的别名。

作用：是访问更简洁，方便。

创建同义词：

语法：

①采用sql可视化工具创建
②create synonym 同义词名 for 表名 
go

示例：

create synonym ad for admin
 go

使用同义词：

语法：

select * from 同义词名

示例：

select * from ad
go

查看所有同义词：

select * from sys.synonyms
go

删除同义词：

语法：

drop synonym 同义词名

示例：

drop synonym ad
go

同义词的注意事项：

①同义词可以支持的对象类型分为4类：
    表视图，存储过程，及函数（聚合函数除外）
②当同义词的基础对象变了，同义词也要修改 (就像程序改动位置了，快捷方式也要修改)   

数据库函数和存储过程：

函数和存储过程时t_sql结构化开发的两个重要组成部分。

既可以实现模块化开发，也可以提高程序的执行效率。

函数：

定义：函数是一种封装一条或者多条SQL语句的结构。

分类：系统函数和用户自定义函数

用户自定义函数分类：按返回值划分。

标量值函数和表值函数

标量值函数：

定义：返回类型是基本数据类型的单个值或者是单个表达式的函数。

标量值函数存在，sysobjects系统表中。

创建标量值函数：

语法：

create function 标量值函数名 (@变量名 数据类型)
returns 返回基本数据类型 ? ?--返回类型
as
begin
--语句
return 值 ? --返回基本类型的单个值或单个表达式
end
go

示例：

create function getNumByCateId(@name varchar(20))
returns int
as 
    begin
    declare @sum int = 0
    select @sum = COUNT(p.productID) from Product p where p.categoryID = (select c.categoryID from Category c where categoryName = @name ?group by c.categoryID )
    return @sum
    end
go

使用标量值函数：

语法：

①select * from dbo.标量值函数名
②select * from dbo.标量函数名(参数)
##接收返回值：
declare @变量名 类型 = dbo.标量函数名(参数)

示例：

select dbo.getNumByCateId('火锅')
go

删除标量值函数：

语法：

drop function 标量值函数名

示例：

DROP FUNCTION getNumByCateId
go 

修改标量函数语法：

语法：

alter function 原函数名称(@变量名 数据类型)--新参数
returns 返回数据类型 ? --新返回类型
as
    begin
    declare @变量名 数据类型
        select 语句
    return
end
go

示例：

alter function getproductname(@name varchar(20))
returns varchar(20)
as
begin
 declare @na varchar(20) = null
 select @na =p.title FROM Product P WHERE p.areaID = (select a.areaID from Area a where a.areaName = @name)
return @na
end
go

特点：

①返回类型是基本数据类型的单个值或者表达式
②函数体可以是一条或者多条语句（若多条语句需加begin end）
③标量值函数可以被其他标量值函数或者表值函数调用

表值函数：

定义：返回结果为数据表的函数

分类：多语句表值函数和内联表值函数。

多语句表值函数：

特点：

①返回类型是TABLE类型
②需要在函数定义的时候returns关键字后面确定返回的表的结构
③return关键字后面不需要返回指定的值或表达式

创建语法：

create function 函数名(@变量名 数据类型)
returns @自定义的表名 table(字段1，数据类型，字段2,数据类型...)
as
    begin
        insert into @自定义的表名 select 查询满足条件的旧表语句
    end
go ? ?

示例：

create function getArea(@aid int)
    returns @aaa table(aid int,aname varchar(20))
as
    begin
    insert into @aaa select a.areaID,a.areaName from Area a 
    return
    end
go

使用表值函数：

语法：

select * from dbo.表值函数(参数列表)
go

示例：

select * from dbo.getArea(1)
go

删除表值函数：

语法：

drop function 表值函数名
go

示例：

?

drop function getArea
go

查看表值函数：

语法：

select * from dbo.表值函数名
go

示例：

select * from dbo.getArea
go

修改表值函数：

语法：

alter function 原表值函数名(@变量名 数据类型)
returns @自定义表名 table (字段 数据类型，字段 数据类型) ?as
begin
insert into @自定义表名 select子句
return
end
go

alter function getArea(@aName varchar(20))
returns @newName table (newId int,newName varchar(20),pId int)
as
    begin
    insert into @newName select a.areaID,a.areaName,a.p_areaID from Area a
    return
    end
go

示例：

?

表值函数的注意事项：

表值函数和视图都返回数据表，但表值函数功能更大可以带参数而视图则不可以  [as 可以省略]

存储过程：

定义：存储过程就是SQL语句和流程控制语句的预编译集合

优点：

①允许模块化程序设计
②执行速度更快
③减少网络流量
④可以作为安全机制使用

分类：

①系统存储过程函数和自定义存储过程

创建无参存储过程：

语法：

create proc 存储过程名
as
select子句
go

示例：

create proc proc_p
as
select * from Product
go

查看存储过程：

语法1查看不带参数的：

exec 存储过程名
go

示例：

exec proc_p
go

语法2查看带1参数的：

exec 存储过程名 参数,参数

示例：

exec getCustomerName 1
go

示例2：

exec getcustomer 1,123
go
?
exec getcustomer @cid = 1,@cpwd = 123
go

产看带返回值的存储过程：

语法：

declare @变量名 数据类型
exec 存储过程名 @变量名 = 变量值，@output的变量值 = @变量名
print @变量名
go

示例：

declare @id int
exec getCustomerById ?@ccname='郝琼琼',@ccid=@id output
print @id
go

修改存储过程:

语法：

alter proc 存储过程名
as
新的select子句
go

示例：

alter proc proc_p
as
select p.title from product p
go

删除存储过程：

语法：

drop proc 存储过程名
go

示例：

drop proc proc_p
go

创建带参数的存储过程：

语法1带一个参数：

create proc 存储过程名(@变量名 数据类型)
as
select子句
go

示例：

create proc getCustomerName(@cid int)
as
select c.customerName from Customer c 
go

语法2带两个参数：

create proc 存储过程名(@变量名 数据类型,@变量名 数据类型)
as
select子句
go

示例:

create ?proc getcustomer(@cid int,@cpwd varchar(20))
as
select c.customerName from Customer c where c.customerID = @cid and c.pwd = @cpwd
go

创建有返回值的存储过程：

语法：

create proc 存储过程名(@变量名 数据类型,@变量名 数据类型 output)
as
select子句
go

示例：

create proc getCustomerById(@ccid int,@ccname varchar(20) output)
as
select @ccname = c.customerName from Customer c where c.customerID = @ccid
go

创建分页存储过程：

语法：

create proc 存储过程名
    @tableName varchar(20), --表名
    @pageRow int=0, --每页显示的行数（0为全部）
    @pageNow int=1, --要显示第几页（默认为）
    @orderCol varchar(20) = null, --排序字段
    @order BIT=0 
as
declare @变量名 nvarchar(max)
set @变量名
with t as
(
rowNum = row_number() over (order by '+ isnull(@orderCol,' getdate()')+case @order when 1 then 'desc' else 'asc'end+'),*
from '+@tablename +'
)
select * from t'
if @pageRow > 0
set @变量名 = @变量名+'
where rowNum between '+ltrim(@pageRow*(@pagenow - 1) +1 )+'
and '+ltrim(@pageRow * @pageNow)
exec @存储过程名 '分页的表名',2,3
go

示例：

CREATE PROC sp_page
@tablename NVARCHAR(50),
@pagerow INT=0,
@pagenow INT=1,
@ordercol VARCHAR(50)=NULL, 
@order BIT=0 
AS
DECLARE @s NVARCHAR(MAX)
SET @s='
WITH t AS 
(
SELECT 
rownum=ROW_NUMBER()OVER(ORDER BY '+ISNULL(@ordercol,'GETDATE()')+
CASE @order WHEN 1 THEN ' desc' ELSE ' asc' END+'),
*
FROM '+@tablename +'
)
SELECT *
FROM t'
IF @pagerow>0
SET @s=@s+'
WHERE rownum BETWEEN '+LTRIM(@pagerow*(@pagenow-1)+1)+'
AND '+LTRIM(@pagerow*@pagenow)
EXEC(@s)
GO
EXEC sp_page 'product',2,3
go

数据库表误删如何恢复数据：

①利用navicat实现数据库误删表的恢复：

• 1，右击需要备份的数据库，依次选择‘转储到SQL文件’->‘结构和文件’。

• 2，在新窗口中，选择SQL文件的另存位置。

路径和文件名字，自定义。

• 3，上面第2步点击保存后，进行SQL文件的本地转储，完成100%后，点击关闭。

测试：

• 1，删除刚刚备份的数据库中所有的表，然后右键所要还原的数据库，选择“运行SQL文件”

• 2，找到刚刚备份的sql文件的位置，点击开始，进行恢复。

恢复完成，不要忘记刷新一下数据库。

数据恢复思路：

（一）mysqlbinlog

在mysql中binlog记录着所有的DDl和DML，前提是开启了binlog。这个时候如果我们误删除了数据或者误修改了数据，就可以通过 binlog 日志文件进行查找恢复。

查看是否开启binlog：

show variables like '%log_bin%';

查看binlog输出格式：

show variables like '%binlog_format%';

上面的开启好就可以进行下面的实验了。

（二）少量数据误删修改恢复方案：

环境准备

创建一张表，下面基于该表进行实验。

CREATE table user(
	userId int PRIMARY key auto_increment not null,
	name VARCHAR(20) not null,
	pwd VARCHAR(20) not NULL
);
INSERT INTO USER values(NULL,'张三','123');
INSERT INTO USER values(NULL,'李四','123');
INSERT INTO USER values(NULL,'熊大','666');
INSERT INTO USER values(NULL,'熊二','111');
INSERT INTO USER values(NULL,'光头强','888');

select * from USER;

首先查看当前正在写入的binlog文件：

show master status;

查看当前有多少个binlog日志文件：

show binary logs;

某条数据误修改：

如果只是某几条数据误操作，我们可以通过日志拿到原有的数据进行恢复，将 张三和 李四的pwd修改为 001

下面在 SQL 控制台查看下 binlog 信息：

show binlog events in 'DESKTOP-3STU78B-bin.000002';

使用mysqlbinlog 工具：

这还不能看出刚才修改前的内容，使用 mysqlbinlog 工具进行分析了，首先进入到 binlog 日志的位置，根据时间点过滤下：

mysqlbinlog --no-defaults --base64-output=decode-rows -v --start-datetime="2022-03-13 19:00:00" --stop-datetime="2022-03-13 19:10:00" ./binlog.000002 

还没写完！！！！

练习题：

连接查询

1，显示不同类型的商品数量和平均团购价，按照数量升序排列，如果商品数量相同，则按平均团购价降序排列。

select count(*)商品数量, 平均团购价=AVG(p.currentPrice) from Product p
group by p.categoryID
order by count(*),AVG(p.currentPrice) desc
go

2，显示火锅类商品的最高团购价，最低团购价，平均团购价，数量，以及销售数量合计。

select 最高团购价=max(p.currentPrice),最低团购价=MIN(p.currentPrice),平均团购价=AVG(p.currentPrice),数量=COUNT(*),销售数量合计=SUM(p.salesCount)
 from Product p where p.categoryID=(select c.categoryID from Category c where c.categoryID = 7)
 go

3，显示有商家信息的每一种类型的商品数量和平均团购价，按照商品数量升序排列，如果商品数量相同，则按照平均团购价降序排列，但仅显示平均团购价大于100元的分组信息。

select 商品数量=count(*),平均团购价格=AVG(p.currentPrice) from Product p
 where p.shopID is not null  group by p.categoryID having AVG(p.currentPrice) > 100
 order by count(*),AVG(p.currentPrice) desc
 go

4，显示顾客“雷亚波”的所有订购明细，查询信息包括订单号、商品标题、团购价、团购商品数量以及下单时间，结果按照下单时间升序排列。

 select o.ordersID 订单号,p.currentPrice 商品标题,p.currentPrice 团购价,SUM(p.salesCount) 团购数量,o.ordersDate 下单时间 from  Orders o,OrdersDetail od,Product p,Customer c
 where o.ordersID = od.ordersID and od.ordersID = o.ordersID and od.productID = p.categoryID and c.customerID = o.customerID and c.customerName = '雷亚波' group by o.ordersID,p.currentPrice,o.ordersDate
 order by o.ordersDate asc
 go

5，显示有商品订购信息的全部顾客姓名。

 select distinct  顾客姓名=c.customerName from Customer c,Orders o,OrdersDetail od
 where c.customerID = o.customerID and o.ordersID = od.ordersID
 go

6，显示商品大类名为，购物的所以商品小类的名称。

select c2.categoryID, c1.categoryName,c2.categoryName from  Category c1,Category c2 where c1.p_categoryID = c2.categoryID and c2.categoryName = '购物'
 go

7，汇总每个订单的金额。

select o.ordersID 订单编号,sum(od.quantity*p.originalPrice)  from Orders o,OrdersDetail od,Product p where o.ordersID = od.ordersID and od.productID = p.productID
 group by o.ordersID
 go

8，汇总计算每个客户所下订单的金额，要求显示客户名和订单总金额。

select c.customerName 客户名,订单金额=SUM(od.quantity*p.currentPrice) from  Customer c,Orders o,OrdersDetail od,Product p
 where c.customerID = o.customerID and o.ordersID = od.ordersID and od.productID = p.productID
 group by c.customerName
 go

---------------

子查询

1，查询江汉路地区的商品信息，要求输出商品标题和商品团购价。

select 标题=p.title,团购价=p.currentPrice from Product p where p.areaID = ( select a.areaID from Area a where a.areaName = '江汉路')
 go

2，查询团购价大于平均团购价的商品信息，要求输出商品标题和商品团购价。

select p.title 商品标题,团购价=p.currentPrice from Product p where p.currentPrice > (select AVG(p.currentPrice) from Product p)
 go

3，查询团购价大于“服装”类最高团购价的商品信息，要求输出商品标题和商品团购价。

select  p.title 商品标题,p.currentPrice 团购价 from Product p,Category c where
 p.categoryID = c.categoryID and
 p.currentPrice > ( select MAX(p.currentPrice) from Product p where p.categoryID = (select c.categoryID from Category c where c.categoryName = '火锅'))
 go

4，查询江汉路，中南路，取水楼地区的商品信息。要求输出商品标题，团购价和地区编号。

select 商品标题=p.title,p.currentPrice 团购价,p.areaID 地区编号 from Product p where 
 p.areaID in(select a.areaID from Area a where a.areaName in('江汉路','中南路','取水楼'))
 go

5，查询所有男顾客所购的商品信息，要求输出商品标题和商品团购价，并按照团购价升序排列。

select p.title 标题,p.currentPrice 团购价 from Product p where
 p.productID in ( select od.productID from OrdersDetail od where
 od.ordersID in(select o.ordersID from Orders o where
 o.customerID in(select c.customerID from Customer c where c.gender = '男')))
 order by 团购价 asc
 go

6，查询团购价比所有服装类商品团购价都高的商品信息，要求输出商品标题和团购价。

select p.title 商品标题,p.currentPrice as 团购价 from Product p where
 p.currentPrice > all (select p.currentPrice  from Product p where
 p.categoryID = (select c.categoryID from Category c where
 c.categoryName = '火锅')) 
 go

7，为帮助商家提升定价能力，优化产品销售策略，美淘网平台在向每位商家提供产品信息的同时，还提供了该类商品的平均团购价。

 select p.productID 产品标题,p.title 产品标题,p.currentPrice 团购价,b.平均价  from Product p,
 (select categoryID,AVG(currentPrice) 平均价 from Product group by categoryID) b
 where p.categoryID = b.categoryID
 go

8，查询商品数，已订购商品个数和和已订购商品件数。

 select  
   COUNT(*) 商品个数,
   (select count(od.quantity) from Orders o,OrdersDetail od where
  o.ordersID = od.ordersID) 已购商品件数,
  ( select sum(od.quantity) from Orders o,OrdersDetail od where
  o.ordersID = od.ordersID) 已购商品件数
  from Product p
  go

9，在换季时，美淘网为促销，决定将所有服装类商品的团购价降低百分之10

update  Product  set  currentPrice = currentPrice*0.9  where exists (select c.categoryID from Category c where c.categoryName = '食品' and c.categoryID = categoryID)
 go

10，统计所有商品的总订购数量，并用此数量更新相应商品的‘销售数量’字段

 update Product  set salesCount = (
	select SUM(quantity) from OrdersDetail where productID = Product.productID group by productID
 )
 go

11，删除用客户李慧娟，所有的订购信息。

delete   from  OrdersDetail  where ordersID in(
	select ordersID  from Orders where exists
	(select customerName from Customer where customerName = '李慧娟') and customerID = Orders.ordersID
 )  
 go