子查询-事务-存储引擎-变量-视图-分支结构

## -- 子查询：

-- 	在一个select语句中，嵌入了另一个select语句，那么被嵌入的select语句被称为子查询语句，

-- 主查询：
-- 	主要的查询对象，第一条select语句

-- 二者关系：
-- 1，子查询是嵌入到主查询中
-- 2，子查询是辅助主查询的，要么充当了条件，要么充当了数据源
-- 子查询是有一个可以独立存在的语句，是一个完整的select语句

-- 子查询的分类：
（按照结果集的行列数分）
-- 1，标量子查询：返回的结果是一个数据（一行一列）
-- 2，列子查询：返回结果是一列（一列多行）
-- 3，行子查询：返回结果是一行（一行多列）
-- 4，表子查询：返回的结果是多行多列
（按照子查询出现的位置来分）
1，select后面---标量子查询
2，from后面---表字表子查询
3，where或者having的后面
4，exists后面相关子查询：表子查询

子查询的特点：
1，将子查询放在小括号中
2，子查询的优先级大于主查询
3，子查询一般放在条件的右侧使用，配合>,<,=,<=,>=

​```python
#找出单价比短裤的价格高的商品
1.0，找出短裤的价格
select goods.price from goods where goods.name='短裤'；
2.0,在1的基础上找出价格比当前高的商品
select * from goods where goods.price>(select goods.price from goods where goods.name='短裤')


#查询cate_id和香梨的cate_id一致的，并且价格大于大蒜的商品的名字和价格
1.0,找出香梨的cate_id
select goods.cate_id 
from goods 
where goods.name='香梨'；
2.0,找出大蒜的价格
select goods.price 
from goods 
where goods.name='大蒜';
3.0,结果
select * 
from goods 
where goods,cate_id=(
				select goods.cate_id 
				from goods 
				where goods.name='香梨' 
				and goods.prive>(
									select goods.price 
									from goods 
									where goods,
									name='大蒜'
												)
										)
#查询价格最低的商品的名字
1.0,找出最低价
select min(goods.price) from goods;
2.0找商品
select * from goods where goods.price=(select min(goods.price) from goods);

#查询出所有商品最高价格大于生鲜商品最高价格的种类以及最高价格
1.0,查询生鲜商品最高价格
select max(goods.price) 
from goods 
inner join cate 
on goods.cate_id=cate.id 
where cate.name='生鲜';

2.0,找每种商品的最高价格
select goods.cate_id,max(goods.price) 
from goods 
group by goods.cate_id;

3.0找出价格高于
select goods.cate_id,max(goods.price) 
from goods 
group by goods.cate_id 
having max(price>(
							select max(goods.price) 
							from goods 
							inner join cate 
							on goods.cate_id=cate.id 
							where cate.name='生鲜'
									)
					);

#返回的数据不是标量子查询
select * from goods where price>(select price from goods where cate_id=2);


#找出含有cate_id=2或者3的品牌中所有商品的价格和名字
1.0,找出所有商品的价格和名字
select goods.price,goods.name from goods where goods.brande_id in (select distinct goods.brande_id from goods where goods.cate_id=2 or goods.cate_id=3);
2.0，找出品牌中cate_id=2或者3的品牌
select distinct goods.brande_id from goods where goods.cate_id=2 or goods.cate_id=3;


#找出所有的价格低于天猫一小时达价格的商品
1.0,找出天猫一小时达的最小价格
select min(goods.price) 
from goods 
inner join brand 
on goods.brande_id=brand.id 
where brand.brandename='天猫一小时达';
2.0，找出价格低于的商品
select * from goods where goods.price<
(
select min(goods.price) 
from goods 
inner join brand 
on goods.brande_id=brand.id 
where brand.brandename='天猫一小时达'
);


#查询每一个品牌的信息以及品牌对应的个数
1.0，查询每个品牌的信息
select * from brand;
2.0,查个数
select count(*) from goods 
3.0拼接
select brand.*,(select count(*) from goods where goods.brande_id=brand.id) from brand
-- select后面只支持标量子查询


#查询每个品牌的平均价格，价格等级

1.0，查询平均价格
select goods.brande_id,avg(goods.price) 
from goods 
GROUP BY goods.brande_id

2.0,进行两张表的合表
select * from 
(
select goods.brande_id,avg(goods.price) 
from goods 
GROUP BY goods.brande_id

)
as avg_price_table
inner join price_grade
on avg_price_table.avg_price
between price_grade.min_price and price_grade.max_price

-- 将子查询放在from后面，充当了数据源，是一张表，要求必须取名字



#exists用来搞判断的，返回的结果是0/1

查询有商品的品牌名字
1.0,查询出goods表中的数据
select * from goods
2.0,查询所有商品
select * from brand
where id in(
select goods.brande_id from goods)


#判断是否存在一个品牌京东


​```



## -- ======================================联合查询=====================================

注意点：
1，联合查询的时候，查询的字段个数必须是一致的
2，尽量使得相同意义的字段放在一起
3，union 可以自动去重，union all 不可以去重

​```
1，查询品牌id大于3或者名字中带火的商品
select *
from goods
join brand
on goods.brande_id=brand.id
where goods.`name` like '%火%' or brand.id>3

select * from goods where goods.brande_id>3
union
select * from goods where goods.`name` like '%火%'

2,查询商品id>3的商品，展示id和商品名，找出品牌id>3的id和品牌名
select goods.id,goods.`name` from goods where goods.id>3
union
select brand.id,brand.brandename from brand where brand.id>3


​```



## -- ==================================查询的完整格式===================================

​```
select 字段1，字段2，...
from 表名
where 条件筛选（原始数据上直接筛选）
group by 分组条件1 desc|asc,分组条件2...
having 条件筛选
order by 排序的字段1 asc|desc,字段2 asc|desc
limit 起始位置，数量

-- select goods.brande_id,GROUP_CONCAT(name) from goods order by goods.brande_id desc

-- 执行顺序
from 表名
where 条件
group by...
select distinct 字段
having 条件
order by 字段
limit
​```



## ================================交叉连接==================================

​```
select * from goods cross join brand 
​```



## ================================事物============================================

一个或者一组SQL语句组成一个执行单元，这个单元要么全部完成，要么全部失败
案例（下订单）：订单表，详情表，商品表

show engines

ACID 
-- 事物的属性：
1，原子性：一个事物是不可再分的工作单元，要么全部成功，要么全部失败
2，一致性：事物必须是数据库从一个一致的状态转换到另一个一致的状态
3，隔离性：一个事物的执行不能被其他事物干扰
4，持久性：一个事物一旦被提交他对数据库的改变是永久性的，接下来的其他操作或者数据库故障都不会对他造成任何影响

​```
-- 事物的创造
1，隐式事物：事物的开启与结束没有明显的标记
insert 
show variables like 'autocommit'#查看事物有没有开启
-- 显示开启事物
-- 前提条件：关闭自动提交
set autocommit=0
show variables like 'autocommit'#查看事物有没有开启
向yone表中插入数据
set autocommit=0;
start transaction;
insert into yone values(...)
commit    #提交
rollback  #回滚
select * from yone
2，显示事物：事物具有明显的开启和结束标志
set autocommit=0;
start transaction;#开启事物
执行语句1
执行语句2
。。。。。
commit （提交）|rollback  （回滚）

delete 可以回滚
truncate 不可以回滚
​```



## -- ==========================数据库底层==========================

1，连接层：提供与客户端连接的服务

2，服务层：提供各种用户使用的窗口；提供sql优化器

3，引擎层：提供各种存储数据的方式

4，存储层：存储数据

mysql的架构原理
1，连接池：负责存储和管理客户端与数据库连接的，一个线程负责管理一个

## -- ======================================存储引擎===================================

定义：数据库中使用不同的存储技术将数据存储在文件中
/*
查看当前数据库支持的存储引擎
show engines;
查看当前数据库默认的存储引擎
show variables like '%storage_engine%'
show create table userssss

注意点：1，innodb是mysql默认的存储引擎
				2，innodb是支持事物的，myisam,memory不支持事物
*/

show engines;
-- 

## -- ================================数据库的隔离级别========================

对于多个事物运行时，如果事物访问的是相同的数据，如果没有设置隔离级别，就会出现并发问题
-- 1，并发问题：
脏读：脏读即为事务1第二次读取时，读到了事务2未提交的数据。若事务2回滚，则事务1第二次读取时，读到了脏数据。


不可重复读：不可重复读与脏读逻辑类似。主要在于事务2在事务1第二次读取时，提交了数据。导致事务1前后两次读取的数据不一致。
不可重复读更加侧重于对数据的修改，解决这个问题：将满足条件的行锁起来

幻读：事务1第二次查询时，读到了事务2提交的数据。
对于两个事物T1,T2,幻读更加侧重于新增数据或者删除数据，解决这个问题：

-- 2，隔离级别：解决并发问题
a,未提交读（读取未提交的数据）：允许事物读取未被其他事物提交的更改，所以脏读、不可重复读、幻读都会出现

b,已提交读（读取提交内容）：只允许事物读取已经被其他事物提交的更改，能够解决脏读问题，但是不可重复读和幻读没有得到解决

c,可重复读：确保事物可以多次从一个字段中读取相同的值，在这个事物持续期间，禁止其他事物对这个字段进行更新，可以避免脏读和不可重复读，但是幻读还是会出现

d,可序列化（可串行化）：确保事物从一个表中读取相同的行，在这个事物持续期间，不允许其他事物对表数据执行插入、更新和操作，会解决所有的并发问题，但是性能很低。


oracle:2种，已提交读、可序列化，默认的是已提交读
mysql :4种，默认的是可重复读

3，设置隔离级别

​```
#未提交读()
set session transaction isolation level read uncommitted;

#可提交读()
set session transaction isolation level read committed;

#可重复读()
set session transaction isolation level REPEATABLE-READ;



select @@tx_isolation;#查看隔离级别

show variables like '%autocommit%';#自动提交
​```



## -- =====================================变量===================================

1，定义：变量由用户定义的，不是由系统提供的

2，变量使用的步骤：声明----赋值----使用

3，自定义变量：作用在每一个会话当中变量是用户自己定义的，不是系统提供的
	对于使用步骤而言：先声明，再赋值，后使用

（1），用户变量
作用域：针对当前的会话，和会话变量很相似

​```
set @uname1='zhangsan'
set @uname2：='zhanger'
set @uname3='zhangyi'
select @uname4：='zhangling'
查看
select @uname1
select @uname4
​```

(2),局部变量
-- 作用域：begin...end 

-----声明

​```
declare 变量名 类型
declare 变量名 类型 default 
​```

-----赋值

​```
方式1 :set 局部变量名=值
				set 局部变量名：=值
				select @局部变量名：=值
方式2：select 字段 into 局部变量名 from 表：
​```

-----使用

​```
select 局部变量
create procedure n1()
​```



4，系统变量：由系统提供的，属于服务器层面的，启动之后提供，有初始值

​```
A，全局变量：show global variables
B，会话变量：show session variables
​```

#查看所有的系统变量

​```
show global variables
show session variables
​```

#查看部分满足条件的系统变量

​```
show global|session variables like '%条件%'
​```

全局变量在查看的过程中，需要GLOABLE,如果说时会话变量，可以省略

​```
session
show global variables like '%char%'
select @@global.autocommit
set @@global.autocommit=1

show session variables like '%char%'

#修改某个具体的变量
set @@session.aitocommit=0
set session autocommit=1
select @@session.autocommit
​```



## -- ====================================存储过程=====================================

1，好处：提高代码的重用；简化操作；减少编译次数；减少与数据库服务器的见解次数

2，存储过程：
-- 作用：一般情况下用于数据库的增、删、改
-- 格式：
			

​```
	create procedure 存储过程名（参数列表）
				begin
							存储过程体（合法有效的sql语句）
				end
​```

-- 注意点：
					

​```
1，参数列表：包含三个部分（参数的模式、参数名、参数类型）（in username 							varchar(20)）
						参数模式：
							in(表示该参数作为输入，该参数作为调用的时候传入值)，
							out(该参数作为输出，就是返回值)，
							Inout(既可以当做输入又可以当做输出，表示需要传入，也可以输出)
​```

​						

​```
当参数是空参列表时：
					-- 向品牌表输入数据
					delimiter $ 
					create procedure p1()
					begin
								insert into brand(brandname)
								value('asd','asd123','asd234');

					end $
					call pi()


​```





					2，begin  ...  end，如果存储过程只有一条语句，可以省略
					
					3，存储过程体的每一条sql语句都是合法有效的，一定家；号，存储过程的结尾							需要重新声明结束符，delimiter 结束符
					4,调用

-- 					单个in 参数与多个in参数
					根据插入的品牌id，查询品牌和商品
					delimiter $
					create procedure p2(in brandid int)
					begin
					

							select goods.`name`,brand.brandename 
							from goods inner join brand 
							on goods.brand_id=brand.id
							where goods.brande_id=brandid;
							
					end $
					call p2(1)

-- 					out 参数
					根据传入的商品id,返回商品的名字
					delimiter $
					create procedure p3(in goodsid int,out goodsname varchar(20))
					begin
					

								select goods.`name` into goodsname from goods where goods.id=goodsid;


​					

					end$
					set @gnanme=''
					select @gname
					call p3(1,@gname)

-- 					根据传入的商品id,输出品牌id和商品名
					delimiter $
					create procedure p4(in goodsid int,out goodsname varchar(20),out brandid int)
					begin
					

								select goods.`name`,goods.brande_id into goodsname from goods where goods.id=goodsid;


​					

					end$
					call p4(1,@gname,@bid)
					select @gname
					select @bid

-- 					inout
		传入两个数据，将数据翻倍输出
		delimiter $
		create procedure p5(inout a int,inout b int)
		begin
		set a=a*2;
		set b=b*2;
		end$
		set @m=1$
		set @n=2$
		select @m
		select @n
		call p4(@m,@n)
					
#删除存储过程
drop procedure p1;
#查看存储过程
show create procedure p1;

#创建存储过程输入用户名或者密码，插入到用户表
delimiter $
create procedure v1(in username varchar(20),in cypher varchar(20))
begin
	insert into table brand
end$

#创建存储过程，传入分类名字，返回该品种下面的所有商品信息
delimiter $
create procedure v1(in `name` varchar(20),out goods* varchar(20)
begin

end$

#实现存储过程传入两个商品的价格，返回价格高的那个
delimiter $
create procedure v3(in goodsprice1 varchar(20),in goodsprice2 varchar(20),out res decimal(8,2))
begin
		select if ((goodsprice1-goodsprice2)>0,goodsprice1,goodsprice2) into res;
end$
call v3(10,2,@res)
select @res
#创建存储过程，传入一个时间，格式化年月日
delimiter $
create procedure v4(in ctime datetime,out strtime varchar(20))
begin
		select DATE_FORMAT(ctime,'%y年%m月%d日') into strtime;
		
end$
call v4(now(),@str)
select @str

## -- =====================================函数==========================================

1，函数的优点：提高代码重用；简化操作；减少变异次数；减少了与数据库服务器的见解次数提高效率。
2，函数与存储过程的区别：
	存储过程：可以有返回值，也可以没有返回值按照业务需求来定，适合批量输入，适合批量更新，更多的时候用在
	函数：有且只有一个返回值，适合处理完成后返回一个结果。
3，语法：
				create function 函数名(参数列表) returns 返回类型
				begin
						函数体
				end
4，注意点：
（1）函数参数：参数列表包含两个部分（函数名和参数类型）
（2）函数返回值：函数体需要returns

5,无参数有返回值
查看当前在售的商品条目

delimiter $
create function newfunc() returns int
begin
			declare cnum int default 0;
			select count(*) into cnum from goods where is_show=1;
			return cnum;
end$
select newfunc()

6，有参数有返回值
根据传入的id ，返回商品的名字
delimiter $
create function newfuncf(gid int) returns varchar(20)
begin
		declare gname varchar(20) default ' ';
		select goods.name into gname from goods where goods.id =gid;
		return gname;
end
select newfuncf(2)

7，根据品牌名字，返回商品的平均价格
delimiter $
create function newfunc5(bname varchar(20)) returns varchar(20)
begin
		declare gprice int default '';
		select avg(goods.price) into gprice from goods,brand where goods.brande_id=brand.id and brand.brandename=bname;
		return gprice;
end$
select newfunc5(大润发);

8,查看
show create function 函数名；
show create function newfunc5；


9,删除
drop function 函数名；
drop function newfunc3；

## -- =================================分支结构==========================================

1，if函数： 实现了简单的双分支
						IF(表达式1，表达式2，表达式3)
						执行顺序：如果表达式1成立，返回表达式2的结果，否则返回表达式3

2，case语句： 实现等值判断
							语法1：case 变量|表达式|字段
										when 要判断的值 then 返回的值或者语句1；
										when 要判断的值 then 返回的值或者语句2；
										when 要判断的值 then 返回的值或者语句3；
										...
										else 返回的值或者语句；
										end case;
							语法2：类似if语句，实现区间判断
										case
										when 判断添加1 then 返回的值或者语句；								
										when 判断添加1 then 返回的值或者语句；
										when 判断添加1 then 返回的值或者语句；
										...
										

										else 返回的值或者语句
										end case;

3，if结构
					语法：if 条件1 then 语句1；
								elseif 条件2 then 语句2；
								elseif 条件3 then 语句3；
								...
								else 语句；
								end if;
								应用在begin...end
								
4,根据传入的水果半径，判断水果的等级
delimiter $
create function func1(radis float) return varchar(20)
begin
		if 1<radis and radis<2 then return '一般';
		elseif 2<radis and radis<4 then return 'lianghao ';
		else return '非常好';
		end if;
end

select func1(1.5)

## -- ====================================视图=========================================

1，定义：一种虚拟存在的表，行和列的数据来自于定义视图的查询中所用的表，并且是使用视图的时候动态生成的，只保存sql逻辑，不保存结果

视图：虚拟的表，和普通表一样，通过表，动态生成新数据
表：数据库中真是存在的表
#查询地址表中id>2的数据
select * from goods where id>2;
#创建视图
create view v1 as select * from goods where id>2;
#使用视图
select * from v1；#查看刚创建的视图

2，视图的使用场景
在不同的使用场景下，比如商品展示的商品列表、商品详情，可能会用到相同的业务逻辑，而且这个逻辑写出的sql语句特别麻烦，没有必要重复书写多次，可以使用视图一次创建，下一次使用相同的逻辑的时候，使用视图。
#查出名字中带有以州结尾的城市
create view v2
as
select * from areas where areas.atitle like '%区'

select * from v2;#查看刚创建的视图

#查出商品名带火的品牌名
create view v3
as
select brand.brandename from goods inner join brand on goods.brande_id=brand.id where goods.`name` like '%火%'；#两个表查询时，不能写select * from 

select * from v3；#查看刚创建的视图

#查出商品名带火的品牌名
create view v4
as
select brand.id,brand.brandename from goods inner join brand on goods.brande_id=brand.id where goods.`name` like '%火%'
#两个表查询时，不能写select * from ...

select * from v4；#查看刚创建的视图


3，基本的视图格式
create view 视图名
as
标准的sql查询语句

4，修改视图

（1），方法1：create or replace view 视图名字 as sql查询语句
select * from price_grade;

#查询各个品牌平均价格的级别
create view v5
as
select 

#查询平均价格最低的品牌信息
create view v6
as
select b.* 
from goods g 
inner join brand b 
on g.brande_id=b.id 
group by g.brande_id
having min(g.price)

#查询哪个品牌的均价最低并且展示商品名字
create view v7
as
select g.`name` from goods g inner join brand b on g.brande_id=b.id

(2)，方法2：

alter view 视图名
as
select 字段名 from 表名

5，查看视图

desc 视图名

show create view 视图名

6，更新视图：insert into,update,delete

涉及原始表，视图设置权限，只给读权限1

#向视图中插入数据,会修改原始表
insert into 视图名 values(值)


#修改表数据，不会修改原始表
update 视图名 set 字段名=值... where 提交

#不能更新的视图：
1，包含关键字：order by、distinct、having 、union、union all.
2，常量视图：
create view v9
as 
select '张三毛' name

select * from v9
update v9 set name='张四毛'

3，select 里面包含子查询

#总结：视图和表
         创建语句--------是否占用物理空间
视图：  create view------占用
表：    create table-----不占用



## -- ================================循环结构===========================================

'''
while,loop,repeat

iterate(continue)结束当前循环，继续下一次循环

leave(break)跳出循环，结束当前循环

'''

1，while语法结构：
标签：while 循环条件 DO 
					循环体
			END WHILE 标签

#向cate表中批量插入数据
select * from cate;
-- 存储过程实现
delimiter $
create procedure proc1(in insertcount int)
begin
			declare i int default 1;
			while i<insertcount do
			insert into cate value(0,'月饼1');
			set i=i+1;
			end while;
end$
call proc1(10)
select * from cate;


delimiter $
create procedure proc5(in insertcount int)
begin
			declare i int default 1;
			while i<insertcount do
			insert into cate value(0,concat('月饼',i));
			set i=i+1;
			end while;
end$
call proc5(20)
select * from cate;


#批量插入数据，但插入次数大于3就停止插入数据
delimiter $
create procedure pro1(in insertnum int)
begin
			declare i int default 1;
			cate1:while i<insertnum do
			insert into cate values(0,concat('女装',i));
			if i >3 then leave cate1;
			end if;
			set i=i+1;
			end while cate1;

end$
call pro1(10)
select * from cate;

#批量插入10条数据，偶数次插入
delimiter $
create procedure pro2(in insertnum int)
begin
			declare i int default 1;
			cate1:while i<insertnum do
			set i=i+1;
			if mod(i,2)=0 then iterate cate1;
			end if;
			insert into cate values(0,concat('女装',i));
			

			end while cate1;

end$
call pro2(10)
select * from cate;



2,loop语句
标签：loop
循环体
end loop

3,repeat语句
标签：repeat
循环体
until 结束循环的条件
end repeat 标签

'''4,while,loop,repeat的区别
语法：特点：位置（都放在begin...end中）
-- while：先判断后执行
标签：while 循环条件 DO 
					循环体
			END WHILE 标签
-- loop：没有条件的死循环
标签：loop
			循环体
			end loop
-- repeat：先执行后判断
标签：repeat
			循环体
			until 结束循环的条件
			end repeat 标签'''

5，案例
已知表strtable，id 主键自增长，content 字符串类型，向该表中插入指定个数随机的字符串

#创建表
DROP TABLE IF EXISTS strtable;

CREATE TABLE strtable(
 id INT PRIMARY KEY AUTO_INCREMENT,
 content VARCHAR(20)
);

#创建流程
DELIMITER $
CREATE PROCEDURE test(IN insertcount INT)
BEGIN
 DECLARE i INT DEFAULT 1;#定义一个循环变量i，表示插入次数
 DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
 DECLARE startindex INT DEFAULT 1;#代表起始索引
 DECLARE len INT DEFAULT 1;#代表截取的字符的长度

 WHILE i<=insertcount DO
#产生一个随机的整数，代表截取长度，1到(26-startindex+1)
  SET len=FLOOR(RAND()*(20-startindex+1)+1);

#产生一个随机整数，代表起始索引，1到26取str里的字符
  SET startindex=FLOOR(RAND()*26+1);#floor向下取整

  INSERT INTO strtable(content) VALUES(SUBSTR(str,startIndex,len));

  SET i=i+1;#循环变量更新
  END WHILE;
END $

CALL test(10)#调用
select * from strtable;

## -- ===========================索引============================