MySQL初级

MySQL初级

一、散知识

1、sqlyog快捷键

sqlyog：格式化代码快捷键 F12

2. 命令行备份数据库

mysqldump -uroot -p[密码] eshop > E:\mysqlBackup\eshop.sql

3. MySQL设置字符集

#1、names
set names 字符集;
#2、character
set character 字符集;

名字	作用	有效范围
set names	character_set_client	当前connection
	character_set_connection
	character_set_result
set character	character_set_client	当前connection
set character	character_set_result	当前connection

二、DDL数据定义语言

一、库的管理
创建、修改、删除

二、表的管理
创建、修改、删除

创建：create
修改：alter
删除：drop

1、库的管理

1. 库的创建

create database [if not exists] 库名 [character set 字符集];

2. 库的修改

#修改库名
rename database 库名 to 新库名;

#修改库的字符集
alter database 库名 character set 新字符集;

3.库的删除

drop database [if exists] 库名;

2、表的管理

1. 常见的数据类型

数值型：
	整型
	小数：
		定点数
		浮点数
字符型：
	较短文本：char、varchar
	较长文本：text、blob（较长的二进制数据）
日期型：
	date、time、year、datetime、timestamp

1. 整型

分类：
	tinyint、smallint、mediumint、int/integer、bigint
		1       2        3           4          8

无符号：0为临界值，不能有负数
有符号：有负数

特点：
	如果不设置无符号还是有符号，则默认是有符号。设置无符号需要在类型后面添加unsigned关键字
	如果插入的数值超出了整型的范围，会报1264： out of range value错误
	如果不设置长度，会有默认的长度
	这里的长度代表的是数值显示的宽度

设置了长度，并使用了zerofill关键字，如果插入数值不满这个长度，就会以0填充

2. 小数

分类：
	浮点型：
		float[(M,D)]
		double[(M,D)]
	定点型：
		dec[(M,D)]
		decimal[(M,D)]
		
注意：
	M：整数部位+小数部位共M位，即整数位为M-D
	D：为小数点后D位
	M和D可以不加，但是dec和decimal会默认为 （10，0）
	定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用

新建一个测试表

按要求插入数值

不按要求插入数值

1、整数位未超出，小数位超出，插入时将数值四舍五入到设置的小数位位数，如123.456——123.46

2、整数位超出，插入时会报错：1264 out of range

M和D可以不加，但是dec和decimal会默认为（10，0）

3. 字符型

分类：
	较短的文本：
		char[(M)]：固定长度的字符，M可以省略，默认为1
		varchar(M)：可变长度的字符，M不可以省略
		binary和varbinary：用于保存较短的二进制
		enum：用于保存枚举
		set：用于保存集合
	较长的文本：
		text
		blob（较大的二进制）
注意：
	M：字符数

枚举enum

创建一个测试表

插入enum里定义的值

插入不是enum里定义的值

set集合
注意：同上，不区分大小写

4. 日期

date：只保存日期	最大值9999-12-31	最小值1000-01-01
time：只保存时间	最大值838：59：59	最小值-838：59：59
year：只保存年	 最大值2155	最小值1901
datetime：最大值9999-12-31 23：59：59		最小值1000-01-01 00：00：00
timestamp：最大值2038年的某个时刻		最小值19700101080001

测试二者的区别

1、新建表，插入数据，查看

2、查看当前时区，更改当前时区（例：+9:00）

3、再查看，发现timestamp类型的值随时区更改而变化，timestamp比较容易受时区、语法模式、版本的影响，更能反应当时时区的真实时间

2. 常见约束

含义：一种限制，用于限制表中的数据，为了保证表中数据的准确性和可靠性

六大约束：
	NOT NULL：非空约束，字段不能为空
	DEFAULT：
		默认约束，字段具有默认值
		一个表至多有一个主键，但是可以组合主键（并不是多个主键，而是多个列构成一个主键），如下例
	PRIMARY KEY：主键约束，字段具有唯一性，且非空
	UNIQUE：
		唯一约束，字段具有唯一性
		一个表可以有多个唯一键，也能使用组合的方式写（表示这些列都为唯一键）
	CHECK：检查约束【MySQL中不支持，语法不会报错，但是没效果
	FOREIGN KEY：
		外键约束，用于限制两个表的关系，在从表添加外键约束，用于引用主表中某列的值
		从表的外键列的类型和主类的关联列的类型要一致或兼容，名称无要求
		主表的关联列必须是一个key（一般是主键或唯一键）
	
约束的添加分类：
	列级约束：不支持外键
	
	表级约束：
		不支持非空、默认约束
		语法：[constraint 约束名] 约束类型（字段名）
		
注意：
	主键、外键、唯一约束添加成功后，都会生成索引

组合主键案例：

3. 标识列

#关键字 auto_increment
#又称自增长列
#可以不用手动插入值，系统提供默认的序列值

#特点：
#标识列不一定和逐渐搭配使用，唯一键也可以。只要是key就行
#一个表中只能有一个标识列
#标识列的类型只能是数值型

#mysql中不支持设置自增的起始值，即使设置了也没效果
#可以通过在插入第一条数据时自定义插入一个数值来达到设置改变自增起始值的目的，然后就会跟着自增了

#可以设置步长	
set auto_increment_increment = 步长;

设置自增的步长：

1、在创建表时设置标识列：

2、在修改表时设置/删除标识列：

4. 表的创建

create table [if not exists] 表名(
	列名 列的类型[(长度))] [列级约束],
    列名 列的类型[(长度))] [列级约束],
    列名 列的类型[(长度))] [列级约束],
    ……
    列名 列的类型[(长度))] [列级约束],
    [表级约束]
);

1、添加列级约束

先创建major表，再建stuinfo表，因为stuinfo里引用了major的列

添加成功的约束

外键没有效果

2、添加表级约束

mysql里主键名固定为PRIMARY

5. 表的修改

alter table 表名 add|drop|modify|change column 列名 [列类型 约束];

#column：使用change可以不加column，其余必须加
#列类型：和列类型有关的要加上，改列名的时候也要加上，如例1

#修改列名
alter table 表名 change column 旧列名 新列名 数据类型;

#修改列的类型或约束
alter table 表名 modify column 列名 新数据类型 [新约束];

#注意
#添加表级约束
alter table 表名 add [constraint 约束名] 约束类型（字段名） [外键的引用];

1、修改类型

2、修改约束（新增约束）

可以通过以下两种方式来删除主表的记录

注意：
级联删除就是，比如删除里主表里专业为3的数据，那么从表里所有外键值为3的数据都删除
级联指控就是，比如删除里主表里专业为3的数据，那么从表里所有外键值为3的字段数据设置为空
如果不设置的话，因为从表里有主表关联的外键值，直接删除主表的数据就会报错，除非先把从表中有关联值的数据先删除

#添加新列
alter table 表名 add column 列名 数据类型 [first| after 字段名];

#注意
#first：放在第一列
#after 字段名：放在指定的字段名后面

#删除列
alter table 表名 drop column 列名;

#修改表名
alter table 表名 rename to 新表名;

6. 表的删除

drop table [if exists] 表名;

7. 表的复制

#创建表并复制表结构
create table [if not exists] 新表名 like 已存在的某个表名;

#创建表并复制表结构+数据
create table [if not exists] 新表名
select 
列1，列2，…… | * 
from 已存在的某个表名
where 筛选条件;

三、DQL数据查询语言

1、常见函数

1、单行函数

1、字符函数

1. concat()函数

拼接字符串：concat(str1,str2,,,,)函数（str1，str2，，，都是字符串或者字段）

null和任何字段拼接都为null：

2. length()函数

length(str)，获取参数值的字节个数，中文特别一点，主要还是看字符集是什么。如果是utf8，那么字母占一个字节，中文占三个字节

3. upper()函数，lower()函数

upper(str)，返回大写，lower(str)返回小写

4. substr()函数

substr()函数（为substring()函数的缩写）：截断字符串

有四个重载

注意：所有sql中，索引从1开始

1、截取从指定索引处后面所有字符

2、截取从指定索引处指定长度的字符

5. instr()函数

instr(str, substr)，返回substr在str里第一次出现的起始索引。如果找不到substr，则返回0

6. trim()函数

trim(str)，默认去前后空格

可自定义前后去掉什么

7. lpad()函数，rpad()函数

lpad(str,len,padstr)，即左填充，用指定的字符实现左填充指定长度。用padstr填充str，满足len。len少于str，则截断。

rpad(str,len,padstr)，即右填充，用指定的字符实现右填充指定长度

8. replace()函数

replace(str,from_str,to_str)，把str里的to_str替换from_str。

2、数字函数

1. round()函数

四舍五入，不管正数负数，先取绝对值的四舍五入，再加上符号

有两个重载

1、round(x)，取x的四舍五入

2、round(x,d)，取小数点后d位的x的四舍五入

2. ceil()函数

ceil(x)，向上取整，返回>=该参数x的最小整数

3. floor()函数

floor(x)，向下取整，返回<=该参数x的最大整数

4. truncate()函数

truncate(x, d)，截断，即小数点后保留几位

5. mod()函数

mod(n, m)，取余（就是普通的取余计算），总结：n为负数，结果就为负数

6. rand()函数

返回介于0和1之间的随机浮点数

有两个重载方法

rand()：就是随机生成一个0-1之间的数

rand(N)：如果指定N，则返回可重复的随机数序列。如果未指定N，它将返回一个完全随机的数字。N为种子值

3、日期函数

1. now()函数

返回当前系统日期+时间

2.curdate()函数

返回当前系统日期，不包含时间

3. curtime()函数

返回当前系统的时间，不包含日期

4. year()函数

year(date)，获取指定日期的年

5. month()函数

month(date)，获取指定日期的月

6. monthname()函数

monthname(date)，获取指定日期的月的英文表示

7. day()函数

day(date)，获取指定日期的日

8. hour()函数

hour(time)，获取指定时间的小时

9. minute()函数

minute(time)，获得指定时间的分钟

10. second()函数

second(time)，获得指定时间的秒数

11. str_to_date()函数

str_to_date(str, format)，将字符串通过指定的格式转换成日期

使用场景：前台用户输入日期查询数据，后台接收的是字符串格式，而且有可能格式和数据库里的不一致。此时就需要该函数来根据指定格式转换它了。具体如下例：

12. date_format()函数

date_format(date, format)，将日期转成字符串

13. datediff()函数

datediff(expr1, expr2)，求两个指定日期之间相差的天数，用expr1-expr2

14. 日期格式符

4、流程控制函数

1. if函数

if(erp1, erp2, erp3)，if-else效果

2. ifnull()函数

ifnull(erp1，erp2)，如果erp1表达式结果为null，则返回erp2的结果，不为null，则返回erp1的结果

3. isnull()函数

isnull(erp)，如果erp为null，则返回1（表示为空，即为true），不为null（表示不为空，即为false），返回0

4. case()函数

1、case使用1

相当于switch-case

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
……
else 默认显示的值n或语句n
end

2、case使用2

相当于多重if

case 
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
……
else 默认显示的值n或语句n
end

5、其他函数

1. version()函数

返回当前mysql版本

2. datebase()函数

返回当前数据库

3. user()函数

返回当前用户

4. password()函数

password(str)，给str加密，mysql8.0中移除了该函数

5. md5()函数

md5(str)，给str加密

2、分组函数

分组函数，用作统计使用，又称为聚合函数或统计函数或组函数

限制：和分组函数一同查询的字段要求是group by后的字段

1. sum()函数

sum([distinct] expr)，求和，不包括null值

2. avg()函数

avg([distinct] expr)，求平均数，不包括null值

3.max()函数

max([distinct] expr)，求最大值，支持数字，字符串，日期时间等，不包括null值

4. min()函数

min([distinct] expr)，求最小值，支持数字，字符串，日期时间等，不包括null值

5. count()函数

求个数，不包括null值

常使用count(*)，或者count(常量)（相当于在表里加了一列这个常量），来统计行数

二者查询效率：

MYISAM存储引擎下，count(*)的效率高；

INNODB存储引擎下，count(*)和count(1)效率差不多，比count(字段)要高一些（因为这里还要判断值是否为null）

有四个重载

2、模糊查询

① 在特殊字符前加转义符 \

② 在要转义的字符前随意添加一个字符，在模糊查询末尾加 escape '添加的哪个字符'

下例中：$就充当与转义，转义它后面哪个_

3、分组查询

group by 字句语法

select 分组函数，列（要求出现在group by的后面）
from 表
[where 筛选条件（只涉及原表里的字段）]
[group by 分组的列表]
[having 分组后的筛选条件]
[order by 子句];

注意：
	查询列表必须特殊，要求是分组函数和group by后出现的字段
特点：
	1、分组查询中的筛选条件分为两类
				数据源				位置					关键字
	分组前筛选	原始表             group by子句的前面	   where
	分组后筛选	分组后的结果集      group by子句的后面 	    having
	
	分组函数做条件肯定放在having子句中
	能用分组前筛选，就优先考虑使用分组前筛选
	
	2、group by子句支持单个字段分组，多个字段分组（无顺序要求），表达式或函数（用得较少）

4、连接查询

1. 笛卡尔乘积现象

发生原因：没有有效的连接条件

如何避免：添加有效的连接条件

2. 分类

按年代分类：
	① sql192标准：在mysql里，仅仅支持内连接
	② sql199标准【推荐】：在mysql里，支持内连接+外连接（左外和右外）+交叉连接
 
按功能分类：
	① 内连接：
		等值连接
		非等值连接
		自连接
	② 外连接：
		左外连接
		右外连接
		全外连接
	③ 交叉连接

3. sql192标准

内连接

1.等值连接

select 查询列表
from 表1，表2 [别名]
where 表1.key = 表2.key
[and 筛选条件]
[group by 分组字段]
[having 分组后的筛选条件]
[order by 排序字段]

① 多表等值连接的结果为多表的交集部分
② n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④ 一般需要为表起别名
⑤ 可以搭配排序、分组、筛选等子句使用

2. 非等值连接

select 查询列表
from 表1，表2 [别名]
where 非等值的连接
[and 筛选条件]
[group by 分组字段]
[having 分组后的筛选条件]
[order by 排序字段]

就是除等号外的

3. 自连接

select 查询列表
from 表 [别名1]，表 [别名2]
where 等值的连接
[and 筛选条件]
[group by 分组字段]
[having 分组后的筛选条件]
[order by 排序字段]

就是自己和自己连接

4. sql199标准

select 查询列表
from 表1 [别名]
[连接类型] join 表2 [别名]
on 连接条件
[where 筛选条件]
[group by 分组]
[having 分组后的筛选条件]
[order by 排序列表]

1. 内连接

[inner]

① 等值连接

② 非等值连接

③ 自连接

2. 外连接

① 左外连接

left [outer]，左边的是主表

② 右外连接

right [outer]，右边的是主表

③ 全外

full [outer]，内连接的结果+表1中有但表2没有的+表2中有但表1没有的

mysql不支持全外连接

3. 交叉连接

cross

5、子查询

出现在其他语句中的select语句，称为子查询或内查询

特点：
子查询放在小括号内
子查询一般放在条件的右侧

分类：
按子查询出现的位置：
	select 后面：仅仅支持标量子查询
	from 后面：支持表子查询
	where或having 后面：支持标量子查询、列子查询、行子查询
	exists 后面（相关子查询）：表子查询

按结果集的行列数不同：
	标量子查询（结果集只有一行一列）：一般搭配着单行操作符使用，如> < = >= <= <>
	列子查询（结果集只有一列多行）：一般搭配着多行操作符使用，如in any/some（符合任意一个即可） all（符合所有的）
	行子查询（结果集只有一行多列）
	表子查询（结果集一般为多行多列）

1. where后面

1. 标量子查询

2. 列子查询

3. 行子查询

2. select后面

3. from后面

4. exists后面

6、分页查询

select 查询列表
from 表
[type join 表2 on 连接条件]
[where 筛选条件]
[group by 分组]
[having 分组后筛选条件]
[order by 排序字段]
limit [offset],size;

#注意：
#offset：要显示条目的起始索引（从0开始）,(page-1)*size
#size：要显示的条目个数

7、联合查询

查询语句1
union
查询语句2
union
……;

#应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询信息一致（查询列数一致，每一列的类型和顺序最好一致）
#union关键字默认去重
#union all可以包含重复项

8、其他

1. 在当前数据库查看另一个数据库的所有表

show tables from 另一个数据库名;

2. 起别名

as 或空格（可单引号、双引号、不加引号（如果有关键词，要加引号，如案例））

3. MySQL中的+号

只有一个作用，运算符

4. ``着重号

为了有时候区别和关键字同名的字段名等

5. 安全等于 <=>

6. 查找MySQL里的关键字的值

show variables like '%关键字%';
select @@关键字名;

7. 查看MySQL里支持的引擎

show engines;

四、DML数据操作语言

1、插入

#语法1（支持子查询插入）

#单条插入
#1、
insert into 表名[(列名，……)] 
values(值1，……);
#2、
insert into 表名[(列名，……)]
select 值1，……;（子查询）

#批量插入
insert into 表名[(列名，……)] 
values
(值1，……),
(值2，……),
……;

#语法2

insert into 表名
set 列名1=值1,列名2=值2,……;

#注意：
#如果指定了列，并且那个列可以为null，插入时就写null，没指定就不用管。
#如果主键自增，插入时就写null，没指定就不用管。
#列的顺序可以和数据表里的不一致，但是每列自己的数据类型要一致
#不为空的都得插入数据

2、修改

#修改单表数据update 表名set 列名1=新值1,列名2=新值2,……where 筛选条件;

#修改多表数据
#1、sql92语法
update 表1 别名，表2 别名
set 列名1=新值1,列名2=新值2,……
where 连接条件
and 筛选条件;

#2、sql99语法
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列名1=新值1,列名2=新值2,……
where 筛选条件;

3、删除

#语法1：
#单表删除
delete from 表名 where 筛选条件 [limit 条目数]

#多表删除
#1、sql92语法
delete 表1的别名，表2的别名
from 表1 别名，表2 别名
where 连接条件
and 筛选条件

#2、sql99语法
delete 表1的别名，表2的别名
from 表1 别名
inner|left|right join 表2 别名
on 连接条件
where 筛选条件
[limit 条目数]

#语法2：
truncate table 表名;

#注意：
#删除全部数据时，推荐使用truncate，比delete效率高一点点点
#如果表中有自增长列，用delete删除，再插入数据，自增长列的值从断点开始。用truncate删除，再插入数据，自增长列的值从原点开始
#truncate删除没有影响行数返回值，delete删除有影响行数返回值
#truncate删除不能回滚，delete删除可以回滚

五、TCL事务控制语言

1、事务

一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行
举例：当转账时刚操作完当前用户扣除金额，准备操作将扣除金额添加到转账用户时出现故障，导致不能继续操作，但是当前用户的钱已经被扣了，这时数据就不可靠了。

1. 事务的ACID特性

1、原子性（Atomicity）
事务是一个不可分割的工作单元，事务中的操作要么都发生，要么都不发生
2、一致性（Consistency）
在事务开始之前和事务结束以后，数据库的完整性没有被破坏，如转账前总额为2000，转账后总额还是2000
3、隔离性（Isolation）
一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务的隔离的，并发执行的各个事务之间不能互相干扰
4、持久性（Durability）
一个事务一旦提交，它对数据库中数据的改变就是永久的

2. 事务的创建

#隐式事务：
#事务没有明显的开启和结束标记
#比如insert、update、delete语句
#查询自动提交，默认为1开启
show variables like '%autocommit%';

#显式事务：
#事务具有明显的开启和结束标记
#前提：禁用自动提交功能,autocommit变为0关闭。只针对当前会话有效
set autocommit = 0;

#步骤1：开启事务
set autocommit = 0;
start transaction;#可选的
#步骤2：编写事务中的sql语句（select、insert、update、delete）
#步骤3：结束事务
commit; #提交事务
rollback; #回滚事务

savepoint 节点名;#设置保存点，只能搭配rollback

3. 事务的隔离级别

事务并发产生的问题：

对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题：
1、脏读：
	当前事务读到另一个事务还没提交的数据。就是读到了不该读的东西所以叫脏读
	即对于两个事务T1和T2，T1读取了已经被T2更新但还没有提交的数据。若T2回滚，T1读取的内容就是临时且无效的
2、不可重复读：
	当前事务里有两个相同的查询语句，中间没有ddl操作，两次读取结果不一致。就是同一事务里相同查询，结果却不一致，所以叫不可重复读
	即对于两个事务T1和T2，T1读取到了一个数据，然后T2更新了这个数据。T1再次读取同一个数据，数据和之前读取的不一样了
3、幻读：
	当前事务修改了数据，另一个并行的事务也修改了数据，当前事务查询修改结果发现不一致。就是明明改了数据，但是数据却没变，跟出现幻觉一样，所以叫幻读
	即对于两个事务T1和T2，T1修改了某条数据，T2又把这条数据修改成原来的数据了，T1查看修改结果时就会发现数据没有变化

数据库的四种事务隔离级别：

1、读未提交（READ UNCOMMITTED）
	允许事务读取到未被其他事务提交的变更
	脏读、不可重复读、幻读的问题都会出现
2、读已提交（READ COMMITTED）
	只允许事务读取已经被其他事务提交的变更
	可以避免脏读，不可重复读、幻读问题依然会出现
3、可重复读（REPEATABLE READ）
	确保事务可以多次从一个字段中读取相同的值，在这个事务持续间，禁止其他事务对这个字段进行更新
	可以避免脏读和不可重复读，幻读问题依然会出现
4、串行化（SERIALIZABLE）
	确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入，更新和删除操作
	所有并发问题都可避免，但性能降低
	
注意：
Oracle支持2种事务隔离级别：READ COMMITTED,SERIALIZABLE
Oracle默认的事务隔离级别为：READ COMMITTED
MySQL支持4种事务隔离级别
MySQL默认的事务隔离级别为：REPEATABLE READ

查询事务隔离级别：

#会话事务隔离级别
#mysql8
select @@transaction_isolation;
select @@session.transaction_isolation;
#mysql5
select @@tx_isolation;

#全局事务隔离级别
select @@global.transaction_isolation;

#可以先模糊查询isolation查看isolation名是什么
#这个查询的是当前会话的事务隔离级别
show variables like '%isolation%';

设置隔离级别：

#设置会话事务隔离级别
set [session] transaction isolation level 隔离级别;
#设置全局事务隔离级别
set global transaction isolation level 隔离级别;

2、存储引擎

MySQL中的存储引擎
概念：在MySQL中的数据用各种不同的技术存储在文件（或内存）中
通过show engines：来查看MySQL支持的存储引擎
在MySQL中用的最多的存储引擎有：InnoDB、MyISAM、MEMORY等。其中InnoDB支持事务，而MyISAM、MEMORY不支持事务

六、视图

虚拟表，和普通表一样，但是没有实际占用物理内存，只保存sql逻辑，不保存查询结果
mysql5.1版本出现的新特性，是通过表动态生成的数据

应用场景：
	多个地方用到同样的查询结果
	该查询结果使用的sql语句较复杂

优点；
	重用sql语句
	简化复杂的sql操作，不必知道它的查询细节
	保护数据，提高安全性

1、创建视图

create view 视图名
as
sql查询语句

2、修改视图

#方式1：
create or replace view 视图名
as
sql查询语句

#方式2：
alter view 视图名
as
sql查询语句

3、删除视图

drop view 视图名1，视图名2，...

4、查看视图

#查看视图结构
desc 视图名

#查看视图创建语句
show create view 视图名

5、更新视图

视图的可更新行和视图中查询的定义有关系，以下类型的视图是不能更新的；
1. 包含以下关键字的sql语句：分组函数、distinct、group by、having、union、union all
2. 常量视图
3. select中包含子查询
4. join
5. from 一个不能更新的视图
6. where子句的子查询引用了from子句中的表

七、变量

系统变量：
	全局变量
	会话变量
自定义变量：
	用户变量
	局部变量

1、系统变量

#由系统提供，不是用户定义的，属于服务器层面

#使用的语法：
#1、查看所有的系统变量
show global | [session] variables;

#2、查看满足条件的某个系统变量的值
show global | [session] variables like '%想查询的变量名%';

#3、查看指定的某个系统变量的值
select @@global.系统变量名;
select @@session.系统变量名;

#4、为某个系统变量赋值
#方式一
set global | [session] 系统变量名 = 值;

#方式二
set @@global.系统变量名 = 值;
set @@session.系统变量名 = 值;

#注意：
#如果是全局级别，则需要加global
#如果是会话级别，则需要加session
#如果不写，则默认session

1. 全局变量

作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话（连接）有效，但是不能跨重启
如设置了禁用自动提交，重启MySQL后，自动提交还是默认为开启的

2. 会话变量

作用域：仅仅针对当前会话（连接）有效

2、自定义变量

变量是用户自定义的
使用步骤：声明、赋值、使用

1. 用户变量

作用域：针对于当前会话（连接）有效，同于会话变量的作用域
用户变量可以应用在任何地方

2. 局部变量

作用域：仅仅在定义它的begin end中有效
局部变量只能应用在beign end中的第一句话

八、存储过程和函数

存储过程和函数，类似于Java中的方法
一组预先编译好的SQL语句的集合，可以理解成批处理语句

好处：
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

区别：

存储过程：可以有0个返回，也可以多个返回。适合做批量插入，批量更新
函数：有且仅有一个返回。适合做处理数据返回一个结果

1、存储过程

1. 创建调用存储过程

#一、创建语法

create procedure 存储过程名(参数列表)
begin
	存储过程体(一组合法的SQL语句)
end

#注意
#1、参数列表包含三部分（参数模式 参数名 参数类型）
#参数模式：
#in：调用方法需要传入值
#out：作为调用方法的返回值
#inout：既需要传入值，又可以做返回值
#举例
in stuname varchar(20)

#2、如果存储过程体仅仅有一句话，begin end就可以忽略不写

#3、存储过程体中的每条SQL语句的结尾要求必须加上分号

#4、存储过程的结尾可以使用delimiter重新设置
#语法：
delimiter 结束标记

#二、调用语法
call 存储过程名(实参列表);

sqlyog可视化界面点击新建存储过程给的默认模板：

举例：

#设置结束标记
DELIMITER $
CREATE PROCEDURE test(IN id INT, OUT test VARCHAR(20))
BEGIN
	#声明局部变量
	DECLARE result VARCHAR(20) DEFAULT '';
	#把查询结果赋值给局部变量
	SELECT stu.`stuName` INTO result FROM stu WHERE stu.`stuId` = 1;
	#把局部变量赋值给输出参数	
	SET test = result;
#告诉MySQL结束了
END $
#把结束标记设置为原来的分号;
delimiter ;

#设置用户变量
SET @test = '';
#调用存储过程test，并将@test作为返回值
#这个@test也可以不事先声明
CALL test(1,@test);
#打印获得返回值的用户变量
SELECT @test;

创建带inout模式参数的存储过程举例：

2. 删除存储过程

#语法：
drop procedure 存储过程名;

3. 查看存储过程

#语法：
show create procedure 存储过程名;

2、函数

1. 创建调用函数

#一、创建语法
create function 函数名(参数列表) returns 返回类型
begin
	函数体
end

#注意：
#1、参数列表 包含两部分：参数名 参数类型

#2、函数体：肯定会有return语句，如果没有会报错
#如果return语句没有放在函数体的最后不会报错，但不推荐这么干
return 值;

#3、函数体中仅有一句话，可以省略begin end

#4、使用delimiter语句设置结束标记
delimiter 结束标记;

#二、调用语法
select 函数名(参数列表)

sqlyog可视化界面点击新建函数给的默认模板：

mysql8创建函数报错

解决方法：

#这是我们开启了bin-log, 我们就必须指定我们的函数是否是
DETERMINISTIC #确定性的
NO SQL #没有SQl语句，当然也不会修改数据
READS SQL DATA #只是读取数据，当然也不会修改数据
MODIFIES SQL DATA #要修改数据
CONTAINS SQL #包含了SQL语句

#其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。
#如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。

#设置结束标记
DELIMITER $
CREATE FUNCTION test(id INT) 
#返回值类型
RETURNS VARCHAR(20)
#声明只是读取sql数据
READS SQL DATA
BEGIN
	#声明局部变量
	DECLARE result VARCHAR(20) DEFAULT '';
	#查询并赋值
	SELECT stu.`stuName` INTO result FROM stu WHERE stu.`stuId` = id;
	#返回值
	RETURN result;
#告诉MySQL结束了
END $
#把结束标记设置为原来的分号;
DELIMITER ;

#调用函数
SELECT test(1);

2. 查看函数

show create function 函数名;

3. 删除函数

drop function 函数名;

九、流程控制结构

顺序结构：程序从上往下依次执行
分支结构：程序从两条或多条路径中选择一条去执行
循环结构：程序在满足一定条件基础上，重复执行一段代码

1、分支结构

1. if函数

功能：实现简单的双分支
语法：if(表达式1，表达式2，表达式3)
执行顺序：如果表达式1成立，则返回表达式2的值，否则返回表达式3的值
应该：任何地方

2. case结构

#情况1：类似于Java中的switch语句，一般用于实现等值判断
#语法：
case 变量|表达式|字段
when 要判断的值 then 返回的值或语句
……
[else 返回的值或语句]
end case;

#情况2：类似于Java中的多重if，一般用于实现区间判断
#语法：
case
when 要判断的条件 then 返回的值或语句
……
[else 返回代的值或语句]
end case;

#特点：
#可以作为表达式，嵌套在其他语句中，可以放在任何地方
#可以作为独立的语句去使用，只能放在begin end 中
#如果when中的值满足或条件成立，则执行对于then后面的语句或值，并结束case
#如果都不满足，则执行else后的语句或值
#else可以省略，如果省略了，且所有where都不满足，则返回null

case结构作为表达式：

#情况1
case 表达式
when 值1 then 值1
……
else 值n
end;

#情况2
case
when 条件1 then 值1
……
else 值n
end;

case结构作为独立的语句：

#情况1
case 表达式
when 值1 then 语句1;
……
else 语句n;
end case;

#情况2
case	
when 条件1 then 语句1;
……
else 语句n;
end case;

3. if结构

#功能：实现多重分支

#语法：
if 条件1 then 语句1;
elseif 条件2 then 语句2;
……
[else 语句n;]
end if;

#应用场合：应用在begin end中

2、循环结构

分类：while、loop、repeat

循环控制：
iterator类似于continue，继续，结束本次循环，继续下一个
leave 类似于break，跳出，结束当前所在循环

1. while结构

#语法：
[标签:]while 循环条件 do
	循环体;
end while [标签];

while+标签+leave：

while+标签+iterator：

2. loop结构

#语法：
[标签:]loop
	循环体;
end loop [标签];

3. repeat结构

#语法：
[标签:]repeat
	循环体;
until 结束循环的条件
end repeat [标签];

posted @ 2021-06-01 11:38 JadeXu07 阅读(69) 评论(0) 收藏举报

刷新页面返回顶部