MySQL学习笔记——基础与进阶篇

目录

　　一、###MySQL登录和退出

　　二、###MySQL常用命令

　　三、###MySQL语法规范

　　四、###基础查询

　　五、###条件查询

　　六、###排序查询

　　七、###常见函数的学习

　　八、###分组查询

　　九、###连接查询

　　十、###子查询

　　十一、###分页查询

　　十二、###联合查询

　　十三、###DML语言

　　十四、###DDL语言

　　十五、###常见的数据类型

　　十六、###常见的约束

　　十七、###标识列

　　十八、###TCL

　　十九、###视图

　　二十、###变量

　　二十一、###存储过程和函数

###1.MySQL登录和退出
登录：
Mysql (-h主机名 -p端口号)-u用户名 -p密码
退出：
exit或者ctrl+c



###2.MySQL基础命令
1.查看所有数据库
show databases;
2.打开库
use 库名;
3.查看其他库中的表
show tables from 库名;
4.创建表
creat table 表名(

列名 列类型，
列名 列类型，
。。。
);
5.查看表结构
desc 表名;



###3.MySQL语法规范

不区分大小写 建议关键字大写，表名、列名小写
2.每条命令用分号结尾
3.每条命令根据需要可以缩进 换行
4.注释
单行注释：#注释文字
单行注释: - -注释文字
多行注释：




###4.基础查询

#语法：
select 查询的东西
from 表名;

#特点：
1.查询的东西可以是：字段、常量值、表达式、函数
2.查询的结果是一个虚拟的表格。

#1.查询表中的单个字段
SELECT NAME FROM city;

#2.查询表中的多个字段
SELECT NAME,id FROM city;

#3.查询表中所有的字段
SELECT * FROM city;

#4.查询常量值
SELECT 100;
SELECT "beijing";

#5.查询表达式
SELECT 100*98;

#6.查询函数
SELECT version(); //得到的是查询函数的返回值

#7.为字段起别名
①便于理解
②如果查询的字段有重名情况，使用别名可以区分开

#方式一：使用As
SELECT 100*98 As 结果;
SELECT "beijing" AS 首都; 

#方式二：使用空格
SELECT "beijing" 首都; 

#案例：如果别名中存在关键字或特殊符号，加双引号
（单引号也可，但建议双引号）

#8.去重
SELECT DISTINCT "beijing" from city;

#9.+的作用
运算符： 
例如：select 100+10;//两个操作数都为数值型，则作加法运算
select "123"+90;//一个操作数是字符，另个一是数值，会
尝试将字符型转换为数值型 结果：213;
SELECT DISTINCT "beijing" + 10;//若不能转换，则将字符型
视作0 结果：10;
SELECT DISTINCT "beijing" + null;//若有一方是null，则
结果为null




###5.条件查询

#语法：
select 
查询列表
from
表名
where
筛选条件;
#分类
一、按条件表达式筛选
条件运算符> < = != <= >= 建议不等于用<>
案例：筛选id>50的城市
SELECT 
*
FROM 
city
WHERE
id>50


二、 按逻辑运算符筛选
逻辑运算符：
与：$$ 或：|| 非：!
建议使用： and or not
案例：查询id 50到100的城市：
SELECT 
*
FROM 
city
WHERE
id>50 AND id<100


三、模糊查询
like
between and
in
is null | is not null

#1.like
特点：
①一般和通配符搭配使用
通配符：
% 任意多个字符
_ 任意单个字符
如果查询的中含有通配符，应转译 例如 \_


案例：查找ch开头的国家
SELECT 
*
FROM 
country

WHERE 
NAME LIKE "ch%"

#2.between and

特点：
①包含临界值
②调换顺序不报错，但是错误

案例：查找id在100到120的城市
SELECT 
*
FROM 
city

WHERE 
id BETWEEN 100 AND 120


#3.in
特点：
①in列表的值必须一致或兼容
②不能使用通配符

案例：查询国家代码是CHN和NLD的城市名和ID
SELECT 
id,
NAME
FROM 
city
WHERE 
countrycode IN ("CHN","NLD")


#4.is null
id = null 不成立 
id isnull





###6.排序查询
语法：
select 查询列表
from 表
【where 筛选条件】
order by 排序列表 【asc|desc】
特点：
①asc是升序 desc是降序，如果不写，默认升序
②支持单个字段，也支持多个字段、表达式、函数、别名
③order by子句一般放在查询语句的最后，limit子句除外

案例1：查询城市名字和id，按id降序
SELECT 
id AS 序号,NAME AS 名字
FROM 
city
ORDER BY 
id DESC
案例2：按照城市名的字节长度排序【按函数排序】
SELECT 
*,LENGTH(NAME)
FROM 
city
ORDER BY LENGTH(NAME) DESC

案例3：按别名排序

案例4：先按照名字长度排序，再按id排序【按多个字段排序】
SELECT 
*,LENGTH(NAME)
FROM 
city
ORDER BY LENGTH(NAME) DESC,id ASC



###7.常见函数的学习

概念：类似java的方法

调用：select 函数名(参数列表) 【from 表】

分类：
1.单行函数
如 concat、length、ifnull等
2.分组函数
功能：做统计使用，又称作统计函数、聚合函数、组函数


# 单行函数

#一.字符函数

#1.length 获取参数值的字节个数

#2.concat 拼接字符串

#3.upper、lower 变大写，变小写

#4.substr 、substring 截取字符串
注意：索引从1开始
#一个参数：截取从指定索引到结尾
SELECT 
SUBSTR("李莫愁爱上了陆湛远",7)
输出陆湛远
#两个参数：截取从指定索引指定长度
SELECT 
SUBSTR("李莫愁爱上了陆湛远",1,3)
输出李莫愁

#5.instr 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR("杨不悔爱上了殷六侠","殷六侠")
输出7

#6.trim
SELECT TRIM(" 周福利 ")
输出：周福利

SELECT TRIM("a" FROM "aaaaa周aaaa福aaaaa利aaaaa")
输出：周aaaa福aaaaa利

#7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD("周福里",10,"*")
输出：*******周福里
#8.rpad 用指定的字符实现右填充指定长度

#9.replace 替换
SELECT REPLACE("周芷若周芷若周芷若张无忌爱上了周芷若周芷若","周芷若","赵敏")
输出：赵敏赵敏赵敏张无忌爱上了赵敏赵敏


#二、数学函数

#1.round 四舍五入
先转化为绝对值，再四舍五入
SELECT ROUND(-1.51)
SELECT ROUND(-1.515,2)小数点后保存两位

#2.ceil 向上取整
返回>=该参数的最小整数

#3.floor 向下取整
返回<=该参数的最大整数

#4.truncate 截断
SELECT TRUNCATE(1.6999,1)
输出：1.6

#5.mod 取余
mod(a,b) : a-a/b*b;


#三、日期函数

#1.now 返回当前系统日期+时间

#2.curdate 返回当前系统日期，不包含时间

#3.curtime 返回当前时间，不含日期

#4.可以获取指定部分，年、月、日、小时、分钟、秒
select year(now()) 年;
select year("1998-1-1") 年;
输出1998

select month(now());
9
select monthname(now());
December

#5.str_to_date:将字符转换为指定类型的日期
select str_to_date("4-3 1992","%c-%d %Y")
输出：1998-03-02
%Y 四位年份的年
%y 2位年份的年
%m 月份（01，02，...,11，12）
%c 月份（1，2，3.。。。，11，12）
%d 日（01，02.。。。）
%H 小时（24小时制）
%h 小时（12小时制）
%i 分钟（00，01，02，....，59）
%s 秒（00，01，02，....，59）

#6.date_fORMat将时间类型转化为字符串
SELECT DATE_FORMAT(NOW(),"%Y年%m月%d日")
输出：2019年12月06日

#7.datediff（ ， ）
查找两个日期相差的时间


#四、其他函数
version（）查看版本
database（）查看当前数据库
user（）查看当前用户


#五、流程控制函数
#1.if函数： if else的效果
SELECT IF(10>5,"大","小")
输出：大

#2.case函数的使用一： switch case的效果
语法：
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
....
else 要显示的值n或语句n;
end

案例：
SELECT ID 序号, NAME 名字,
CASE id
WHEN 1 THEN "test"
ELSE NAME
END AS 新姓名
FROM city

#3.case 函数的使用二： 类似于多重if
语法：
case 
when 条件1 then 要显示的值1或语句1;
when 条件2 then 要显示的值2或语句2;
.。。
else 要显示的值n或语句n
end

案例：
SELECT ID 序号, NAME 名字,
CASE 
WHEN id<=10 THEN "A"
WHEN id>10 AND id<=20 THEN "B"
ELSE "C"
END AS 新姓名
FROM city


#二、分组函数


#1、简单的使用
SELECT SUM(id) FROM city
SELECT COUNT(id) FROM city
.....

#2.参数支持哪些类型
sum和avg只支持数值类型 不支持字符型 不支持日期类型

max和min值支持字符型、数字型、日期型

count任何类型都支持 

#3.是否忽略null值
以上分组函数都忽略null值

#4.可以和distinct搭配

#5.count函数的详细介绍
SELECT COUNT(1) FROM city//显示所有字段
SELECT COUNT(*) FROM city

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

###8.分组查询
语法：
select 分组函数，列（要求出现在group by的后面）
from 表
【where 筛选条件】
group by 分组列表
【order by 子句】

特点：
①分组查询中的筛选条件可以分为分组前查询和分组后查询
②分组查询支持单个字段分组，也支持多个字段分组（中间用逗号隔开，顺序可变）
③也可以添加排序（放在最后）

案例1：查询每个国家的最大城市id
SELECT MAX(id) 最大id,`CountryCode`
FROM city
GROUP BY countrycode
ORDER BY 最大id

案例2：查询每个国家的城市数
SELECT COUNT(*),`CountryCode`
FROM city
GROUP BY countrycode

案例3：添加筛选条件
SELECT COUNT(*),`CountryCode`
FROM city
WHERE `CountryCode` ="chn"
GROUP BY countrycode

#添加复杂的筛选条件
案例：查询每个城市数大于50的国家
SELECT COUNT(*) 城市数,`CountryCode`
FROM city
GROUP BY countrycode
HAVING 城市数>50
由于where只能跟在from后面。所以用了新的having




###9.连接查询：
含义：又称多表查询，查询的字段来自不同表时使用

分类：
按年代分类：
sql192标准 仅仅支持内连接：
sql199标准【推荐】支持内+外（左外+右外）+交叉连接

按功能分类：
内连接：
等值连接
非等值连接
自连接
外连接
左外连接
右外连接
自连接
交叉连接

#一、sql92标准
#1.等值连接
#1.案例：查询城市名对应的国家名
SELECT city.`Name`,country.`Name`
FROM city,country
WHERE city.`CountryCode`=country.`Code`

#2.为表起别名
#与给字段起别名一至
#缩短表名长度，提高效率
#起完别名后则查询字段不能使用原来的表名限定

#3.两个表的顺序可以调换

#4.可以加筛选
#案例：
SELECT a.`Name`,b.`Name`
FROM city a,country b

WHERE a.`CountryCode`=b.`Code`
AND a.`ID`>10

#5.可以分组

#6.可以排序

#7.可以实现三表连接

#2.非等值连接

#3.自连接
使用别名进行区分

#二、sql99语法


#连接类型关键字：
内连接：inner
外连接：
左外 left 【outer】
右外 right 【outer】
全外 full 【outer】
交叉连接：cross

#(一)内连接
select 查询列表
from 表1 别名
innet join 表2 别名
on 连接条件

#案例：
SELECT DISTINCT `CountryCode`,b.`Name`
FROM `city` a
INNER JOIN `country` b
ON a.`CountryCode`=b.`Code`


#(二)外连接


#案例：
select b.name,bo.*
frome beauty b
left outer join boys bo
on b.id = bo.id

select b.name ,bo.*
frome boys bo
right outer join beauty b
on b.id = bo.id

#全外（msql不支持！）
select b.*,bo.*
from beauty b
full outer join boys b
on b.id = bo.id


#交叉连接
案例：
select b.*,bo.*
from beauty b
cross join boys bo
on b.id = bo.id 
产生笛卡尔积


#sql92和sql99
功能：sql99功能较多
可读性：sql99可读性更高



###10.子查询


#(一)where或having后面的子查询
1.标量子查询（单行子查询）
2.列子查询（多行子查询）

3.行子查询（多行多列）

#一、标量子查询

#特点：
①子查询都要放在小括号内
②子查询一般放在条件的右侧
③标量子查询一般搭配着单行操作符使用
< > >= <= 
列子查询一般搭配多行操作符使用
in、any/some、all
④子查询的执行要优先于主查询

#非法使用标量子查询


#案例：查询谁的工资比Abel高？
#①查询Abel的工资
select salary
from employee
where last_name = "Able"

#②查询员工的信息，满足salary>①的结果
select *
from employee 
where salary > (
select salary
from employee
where last_name = "Able"
) 


#(二)列子查询（多行子查询）
关键字： IN any/some all
#案例：
select *
from employee
where asalry
) 


#(三)行子查询（结果集一行多列或多行多列）
不常用


#二、select后面



#三、将子查询放在from后面


#四、exists后面（相关子查询）





###11.分页查询


#案例：
查询前五条员工信息
SELECT * FROM employees LIMIT 0,5;

#特点：
①limit语句放在查询语句的最后
②公式
显示的页数是page，每页条目数是size
select 查询列表
from 表
limit (page -1)*size,size;


#应用场景：
要查询的结果来自于多个表，多个表没有直接关系，但查询的信息相同


#注意事项：
①要求多条查询语句的查询列数是一致的
②查询的每列类型和顺序是一致的
③uNIOn关键字默认是去重的，如果使用union all会保留所有项



###12.联合查询


#案例1：查询部门编号>90或邮箱中包含a的员工信息
做法1：
select *
from employees
where email like "%a%" or department_id>90

做法2：
select *
from employees
where email like "%a%"
union
select *
from employees 
where department_id >90

#案例2：查询中国用户男士的信息和外国用户男士的信息

###13.DML语言
 

#一、插入语言：

#方式一：

#方式二：


#两种方式pk 
①方式1支持多行输入，方式二不支持
②方式1支持子查询，方式二不支持



#二、修改语句




#三、删除语句






###14.DDL语言


#一、库的管理

#1.库的创建

#语法：
create datebase 库名;

#案例：
CREATE DATABASE books

#2.库的修改（基本不修改，会导致数据丢失，不安全）

#更改库的字符集
alter database books character set gbk

#3.库的删除

drop database if exists books

#二、表的管理


#1.表的创建
create table 表名(
列名 列的类型【（长度） 约束】,
....
)

#2.表的修改
#①修改列名
alter table book change column publishdate pubDate datetime

#②修改列的类型或约束
alter table book modify column pubdate timestamp

#③添加新列
alter table author add column annual double

#④删除列
alter table author drop column annual

#⑤修改表名
alter table author rename to book_author


#3.表的删除
drop table book_author;

#通用的写法
drop database if exists 旧库名;
create database 新库名;

drop table if exists 旧表名;
create table 新表名();

#4.表的复制
#1.仅仅赋值表的结构
CREATE TABLE copy LIKE book_author`copy`

#2.复制表的结构+数据（或部分数据）
CREATE TABLE copy2 
SELECT * FROM book_author
【where】

只复制某些字段
CREATE TABLE copy3
SELECT id,au_name
FROM book_author

###15.常见的数据类型

#一、整形

#1.如何设置有符号和无符号
unsigned 约束关键字


#二、小数
1.浮点型
float(M,D)
double(M,D)
2.定点型
dec(M,D)
decimal(M,D)

特点：
①M:整数部位+小数部位
D:小数部位
如果超过范围，则插入临界值
②M和D可以省略
如果是decimal，则M默认是10，D默认是0
如果是float和double则会根据插入数值的精度确认精度
③定点型的精确度较高，如果对插入数值的精度要求较高可以使用，如货币

#原则：
所选择的类型越简单越好，能保存数值的类型越小越好


#三、字符型

特点：

#char(M)
#varchar(M)
char和varchar区别：carchar可变，char不可变
varchar省空间 但是char效率较高
例如性别，可以用char
char的m可以省略，默认1 ，varchar不能省略



#enum类型： 枚举类型
案例：
create table season(
s1 ENUM("春","夏","秋","冬")
)
insert into season values ("春")

#set类型：
create table tab_set(
s1 set("a","b","c","d")
)
insert into tab_set values ("a")
insert into tab_set values ("a,b")

#binary和varbinary用于保存较短的二进制

#四、日期型
分类：
date 只保存日期
time 只保存时间
year 只保存年

datetime 日期+时间 受时区英雄
timestamp 日期+时间 不受时区影响




###16.常见的约束


#一、创建表时添加约束
#1.添加列级约束
语法：
直接在字段名和类型名后面追加约束

CREATE TABLE stuinfo(
id INT PRIMARY KEY, #主键
stuname VARCHAR(20) NOT NULL, #非空
gender CHAR(1) CHECK(gender="男" OR gender = "女"),#检查（无效）
seat INT UNIQUE , #唯一
age INT DEFAULT 18 #默认

)

#2.添加表级约束
语法：
在各个字段的最下面
【CONSTRAINT 约束名】 约束类型（字段名）
取名可以省略（中括号内的）

CREATE TABLE stuinfo(
id INT ,
stuname VARCHAR(20), 
gender CHAR(1) ,
seat INT ,
age INT ,
maGorid INT,

CONSTRAINT pk PRIMARY KEY(ID),
CONSTRAINT ck UNIQUE(seat),
CONSTRAINT fk_s_m FOREIGN KEY(magorid) REFERENCES magor(`m_id`)
)

#通用写法：
外键约束用表级，其他都用列级约束


#主键和唯一的对比：
保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 √ X 至多有一个 √(组合主键，不推荐)
唯一 √ √ 可以有多个 √(不推荐)

#外键：
1.要求在从表设置外键的关系
2.从表的外键列的类型和主表的关联列的类型一致
3.主表的关联列必须是一个key（一般是主键或唯一键）
4.插入数据时，先插入主表，再插入从表
删除数据时，先删除从表，再删除主表


#二、修改表时添加约束


#1.添加非空、默认约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOTNULL

#2.添加主键、唯一
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id)

#3.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_s_m FOREIGN KEY(majorid) REFRENCES major(id)

#三、修改表时删除约束
基本与添加一致
#删除唯一
alter table s drop index 约束名
#删除外键
alter table s drop foreign key 约束名




###17.标识列


#一、创建表时设置标识列
CREATE TABLE test1(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)

)

#二、修改表时设置标识列
alter table s modify colunm id primary key auto_increment


###18.TCL









###19.视图

#案例：查询J开头的员工的姓名和部门名
SELECT first_name ,department_name
FROM employees a
INNER JOIN departments b
ON a.`department_id` = b.`department_id`
WHERE a.`first_name` LIKE "J%"

建立视图：
CREATE VIEW v1
AS
SELECT first_name ,department_name
FROM employees a
INNER JOIN departments b
ON a.`department_id` = b.`department_id`

从视图中查询：
SELECT * FROM v1
WHERE first_name LIKE "J%"


#一、创建视图

#语法：
create view 视图名
as
查询语句;

# 


#二、视图的修改

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


#方式二：
alter view 视图名
as
查询语句;

#三、删除视图
#语法：
drop view 视图名,视图名,....;
DROP VIEW v1,v2

#四、查看视图
desc 视图
show create view 视图


#五、视图的更新
#1.插入
insert into 视图 values()
与表的插入类似

#2.修改
与表的更新类似

#3.修改
DELETE FROM v1 
WHERE last_name = "展飞"

#具备以下特点的视图是不允许更新的
1.包含 group by,distinct,分组函数,having,union ,union all
2.常量视图
3.select中包含子查询
4.join
5.from一个不能更新的视图
6.where子句的子查询引用了from子句中的表

###20.变量


#一、系统变量
#说明：变量由系统提供，不是用户定义，属于服务器层面

#使用的语法：
1.查看所有的系统变量
show global|【session】 variables;
2.查看满足条件的部分系统变量
show global|【session】 variables like "%char%"
3.查看指定的某个系统变量的值
select @@ gloable|【session】.系统变量名; //跨连接有效
4.为某个系统变量复制
set gloabal|【session】 系统变量名 = 值;
注意：如果是全局级别，需要加global
如果是会话级别，则需要加session 
如果不写，默认session

#1、全局变量
#作用域：服务器每次启动将为所有的全局变量赋初始值，修改针对于所有的连接都有效，
但是不能跨重启(重启会初始化)

#2、会话变量
#作用域：仅仅针对当前会话(连接)有效


#二、自定义变量


#1.用户变量
#作用域：针对有当前会话（连接）有效的，同于会话变量的作用域


#应用：可以放在begin end任何地方
#①声明并初始化
赋值的操作符：= 或 :=
set @用户变量名 = 值;
set @用户变量名:=值;
select @用户变量名 :=值;

#②赋值(更新用户变量的值)
方式一：
set @用户变量名 = 值;
set @用户变量名:=值;
select @用户变量名 :=值;

方式二：通过select 字段 into @变量名

select 字段 into @变量名
from 表;

#③使用(查看用户变量的值)
select @用户变量


#2.局部变量
#作用域：仅仅再定义在他的begin end 中有效

#应用：应用在begin end中的第一句话

#①声明
declear 变量名 类型;
declear 变量名 类型 default 值;

#②赋值
方式一：
set 局部变量名 = 值;
set 局部变量名:=值;
select @局部变量名 :=值;

方式二：通过select 字段 into 变量名

select 字段 into 变量名
from 表;

#③使用
select 局部变量名;


#3.对比用户变量和局部变量
作用域 定义和使用的位置 语法
用户变量 当前会话 会话中的任何位置 必须加@符号，不用限定类型
局部变量 begin end中 只能在begin end中，且为第一句 一般不用加@符号，需要限定类型






###21.存储过程和函数


#存储过程

#含义：一组预先编译好的sql语句的集合。

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

#一、创建语法
#语法：
create procedure 储存过程名(参数列表)
begin
存储过程体(一组合法的sql语句)
end
注意：
1.参数列表包含三部分
参数模式 参数名 参数类型
举例 IN stuname VARCHAR(20)

参数模式：
IN：该参数可以作为输入，也就是说该参数需掉用方法传入值
OUT：该参数可以作为输出，也就是该参数可以作为返回值
INOUT：该参数既可以作为输入，又可以作为输出，即该参数既可以传入值，也可以返回值
2.如果存储过程体仅仅只有一句话，begin end 可以省略
存储过程体中的每条sql语句的结尾必须加分号
存储过程的结尾可以使用delimiter重新设置
语法： delimiter 结束标记
案例: delimiter $

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


#1.空参列表
#案例：插入到admin表中五条记录
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN 
INSERT INTO admin (username,PASSWord)
VALUES("j2","0000"),("j3","0000"),("j4","0000"),("j5","0000"),("j1","0000");
END $
调用：
CALL myp1()$

#2.创建带in模式参数的存储过程
#案例1：创建存储过程实现 根据女神名，查询对应的男神信息
DELIMITER $
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN 
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON b.boyfriend_id = bo.id
WHERE b.name = beautyName;
END $

call myp2("关晓彤")$
#案例2：创建存储过程实现，用户是否登录成功
DELIMITER $
CREATE PROCEDURE myp3 (IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN 
DECLARE result INT DEFAULT 0;

SELECT COUNT(*) INTO result
FROM admin a
WHERE a.username = username
AND a.password = PASSWORD;

SELECT IF(result>0,"成功","失败");

END $

#3、创建带out模式的存储过程

#案例1：根据女神名，返回对应的男神名
DELIMITER $
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT bName VARCHAR(20))
BEGIN
SELECT bo.boyName INTO bName
FROM boys bo
INNER JOIN beauty b ON b.boyfriend_id = bo.id
WHERE b.name = beautyName;

END$

调用：
DELIMITER $
CALL myp5("周芷若",@bName)$
SELECT @bName$

#4.创建带inout模式参数的存储过程
#案例1：传入a和b两个值，最终a和b都翻倍并返回


#二、删除存储过程
语法：drop procedure 存储过程


#三、查看存储过程的信息
show create procedure myp2;


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

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


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





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


#1.无参有返回
#案例1：返回公司的员工个数
CREATE FUNCTION myf1()RETURNS INT 
BEGIN 
DECLARE c INT DEFAULT 0; 
SELECT COUNT(*) INTO c 
FROM employees; RETURN c; 
END$


SELECT myf1()

#2.有参有返回
#案例：根据员工名，返回他的工资


#三、查看函数
show create function 函数名

#四、删除函数
drop function 函数名

###流程控制结构


#一、分支结构
#1.if函数
功能：实现简单的双分支

语法：select if(表达式1,表达式2,表达式3)

执行顺序：
如果表达式一成立，则if函数返回表达式2的值，否则返回表达式3的值

应用：任何地方

#2.case结构

情况1：类似于java中的switch语句，一般用于实现等值判断

语法：
case 变量|表达式|字段
when 要判断的值1 then 返回的值1或语句1;
when 要判断的值2 then 返回的值2或语句2;
...
else 要返回的值n或语句n;
end case;

情况2：类似于java中的多重if语句，一般用于实现区间判断

语法：
case 
when 要判断的条件1 then 返回的值1或语句1;
when 要判断的条件2 then 返回的值2或语句2 ;
...
else 要返回的值n或语句n;
end case;

特点：
①既可以作为表达式，嵌套在其他语句中使用，begin end中或begin end后面
可以作为独立的语句去使用，只能放在begin end中

②如果when中的值满足或条件成立，则执行对应的then后面的语句，并结束case
如果都不满足，则执行else中的语句或值

③else可以省略，如果else省略并且所有when都不满足时，则返回null


#案例：创建存储过程，根据传入成绩，来显示等级，如果传入90-100 A 80-90B 60-80 C 其他D
CREATE PROCEDURE test_case(IN score INT) 
BEGIN CASE WHEN score>=90 
AND score<=100 THEN SELECT "A";
WHEN score>=80 THEN SELECT "B";
WHEN score>=60 THEN SELECT "C"; 
ELSE SELECT "D"; 
END CASE; 
END$


#3.if结构
功能：实现多重分支

语法：
if 条件一 then 语句1;
elseif 条件2 then 语句2;
...
【else 语句n;】
end if;

应用：
在begin end中

#案例：创建存储过程，根据传入成绩，来显示等级，如果传入90-100 A 80-90B 60-80 C 其他D


#二、循环结构
分类：
while、loop、repeat

循环控制：
iterate类似于 continue
leave 类似于break


#1.while
语法：
【标签:】while 循环条件 do 
循环体;
end while【标签】;

#2.loop
语法：
【标签：】loop
循环体;
end loop 【标签】;

可以用来模拟简单的死循环

#3.repeat
语法：
【标签:】repeat
循环体;
until 循环结束的条件
end repeat 【标签】;


#没有添加循环空语句
#案例：批量插入，根据次数插入到admin表中多条记录
DELIMITER $
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=insertCount DO
INSERT INTO admin(username ,`password`) VALUE ("rose","6666");
SET i = i +1;
END WHILE;
END$
CALL pro_while1(100)$


#添加一个leave语句
#案例：和上一个相同，如果次数》20则停止
DELIMITER $
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
INSERT INTO admin(username ,`password`) VALUE (CONCAT("rose",i),"6666");
IF i>=20 THEN LEAVE a;
END IF; 
SET i = i +1;
END WHILE a;
END$
CALL pro_while1(100)$