首页  思维导图  详情

1.MySql基础和存储过程

2021-04-14 09:47:29   0  举报





AI智能生成

GPNU老师的课堂💭 整理成笔记💬 方便考试 🏴‍☠️🏴‍☠️🏴‍☠️🏴‍☠️

数据库

存储过程

mysql

作者其他创作

大纲/内容

1、数据库基础操作

1-0、简述

show DATABASES; -- 查询所有的数据库

use （mysql）; -- 选择括号内的数据库
show tables; -- 然后查询所选数据库的所有的表

1-1、登陆数据库

1、进入mysql：cd D:\MySQL\mysql-5.7.30-winx64\bin

2 . \mysql -u root -p

3、输入密码

4、成功进入数据库

1-2、创建数据库

create database xlqdb;

1-3、数据类型

-- 在开发的过程中，不建议使用的，因为这两种类型在存储方式上是区别其他基础类型的

char(10) 定长的 'xlq '

varchar(10) 变长的 'xlq'

blob 二进制

text 字符串

1-4、创建表

1、得用语句说明使用哪个数据库： use xlqdb;

2、写创建数据库表的语句：create table table_name(列名, 类型);

create table demo_tbl(
demo_id INT AUTO_INCREMENT,
demo_title varchar(100) NOT NULL,
demo_desc varchar(400),
submission_date date,
primary key(demo_id) -- 建立一个主键索引
) ENGINE = InnoDB default charset = utf8;

1-5、删除表

drop table table_name;

drop table xlqdb;

2、存储过程

2-0、前提概要

简介：MySql 5.0 以后才出现的，触发器、事件。存储过程是一段编译过后的SQL语句

优点：

1、有助于提高应用程序的性能，直接放在缓存

2、有助于减少应用程序与数据库服务器之间的通信，而存储只需要发送名字和参数

3、可重用、透明的，与编程语言没有关系

4、存储过程是安全的，存储过程是可能通过授权才能调用

缺点

1、加大连接的内存，CPU的使用率会增加

2、基于存储过程开发业务逻辑比较复杂

3、不能调试

4、不能跨库，不能迁移

delimiter $$
create procedure pro_test_1()
begin
select 'Hello MySql'; -- 写SQL语句
end $$
call pro_test_1();
delimiter ;

2-1、创建存储过程

delimiter $$
create procedure pro_test_1()
begin
select 'Hello MySql'; -- 写SQL语句
end $$

2-2、使用存储过程

call pro_test_1();
delimiter ;

2-3、查看存储过程

-- 查询指定数据库的一个procedure的名字

select name from mysql.proc where db = 'xlqdb';

-- 查询存储过程的状态信息

show procedure status;

查到了所有数据库的的存储过程的状态信息

-- 查看某个存储过程的定义

show create procedure xlqdb.pro_test_1 ;-- 命令行

2-4、删除存储过程

drop procedure pro_test_1;

3、存储过程的语法结构

变量

赋值（有两种方式）

set 变量名 = 值;

delimiter $$
create procedure pro_test_3()
begin
-- 定义一个局部变量
declare num int default 0;
set num = 20; -- set 赋值方式
select num + 20;
end $$
delimiter ;
call pro_test_3();

select 列名 into 变量名

delimiter $$
create procedure pro_test_4()
begin
-- 定义两个局部变量
declare var_demo_title varchar(100) default '';
declare var_demo_desc varchar(100) default '';
select t.demo_title, t.demo_desc into var_demo_title, var_demo_desc from demo_tbl t where demo_id = 1;
select concat(var_demo_title, '-', var_demo_desc);
-- select var_demo_title, '-', var_demo_desc;
end $$
delimiter ;
call pro_test_4();

select concat(var_demo_title, '-', var_demo_desc);

子主题

select var_demo_title, '-', var_demo_desc;

子主题

局部变量

声明（(通过 declare 进行声明)）

在 begin end 代码块中有效

delimiter $$
create procedure pro_test_2()
begin
-- 定义一个局部变量
declare num int default 0;
select num + 5;
end $$
delimiter ;（与下面的语句在上在下都可以）
call pro_test_2();

用户变量(不需要进行声明 @变量名)

set @age = 19;
select @age * 10;

-- 使用 select 使用用户变量 := 对变量赋值

select @name := 'xlq';
select concat(@name, '-', 'Hello');

好像是即刻显示的临时变量？(不懂)

会话变量(@@变量名)

show session VARIABLES;
select @@session.unique_checks;

set @@session.unique_checks = 0;

select @@session.unique_checks;

实验了重复运行这条语句，会储存你的历史值

全局变量(整个数据库的配置文件，数据库必须重启)

show global variables like '%char%'

参数

delimiter $$
create procedure sp_param_1(in age int)
begin
set @user_age = age;
set @user_age = @user_age + 10;
select @user_age;
end $$

delimiter ;
call sp_param_1(10);
select @user_age;

有两个结果（一样的）

子主题

OUT

delimiter $$
create procedure sp_param_2(in id int, out demo_title varchar(100))
begin
select t.demo_title into demo_title from demo_tbl t where demo_id = id;
end $$

delimiter ;
-- 测试
set @demo_title = '';
call sp_param_2(2, @demo_title);
select @demo_title;

代码分析分析地我头晕晕的

INOUT

delimiter $$
create procedure sp_param_3(inout name varchar(100))
begin
set name = concat('Hello-', name);
end $$

delimiter ;
set @user_name = 'xieliqing';
call sp_param_3(@user_name);
select @user_name;

改变变量@user_name

子主题

修改SQL解释的结束标记

delimiter ;
drop procedure if exists pro_demo

-- 计算 a + b, 结果通过 c 返回
create procedure pro_demo(in a int, in b int, out c int)
begin
declare var_a int;
declare var_b int;
set var_a = a;
set var_b = b;
set c = var_a + var_b;
end $$

delimiter ;
call pro_demo(100, 200, @c);
select @c;
drop procedure if exists pro_demo;

似乎是方便后面操作，万一重复了就不好了

因为我们是做实验，删除了就可以重复操作，在最后的时候，不会被卡

选择结构

delimiter $$
create procedure pro_demo()
begin
declare result varchar(32) default '';
if timestampdiff(year, '2000-01-01', now()) > 40 then
set result = '元老';
elseif timestampdiff(year, '2000-01-01', now()) > 30 then
set result = '老司机';
else
set result = '小萌新';
end if;
select result;
end $$
delimiter ;
call pro_demo();
drop procedure if exists pro_demo;

timestampdiff(unit, exp1, exp2) MySQL的时间差函数

case

delimiter $$
drop procedure if exists pro_demo $$
create procedure pro_demo()
begin
declare result varchar(32) default '';
declare message varchar(32) default '';
case
when timestampdiff(year, '1900-01-01', now()) > 40 then
set result = 'yuan lao';
set message = 'old grad';
when timestampdiff(year, '1900-01-01', now()) > 30 then
set result = 'old car driver';
set message = 'middle';
else
set result = 'new one';
set message = 'frist';
end case;
select concat(result, ':', message);
end $$
delimiter ;
call pro_demo();
drop procedure if exists pro_demo;

delimiter $$
drop procedure if exists pro_demo $$
create procedure pro_demo(in score int)
begin
declare result varchar(32);
case score
when 90 then
set result = 'good';
when 50 then
set result = 'fail';
else
set result = 'just so so ';
end case;
select result;
end $$
delimiter ;
call pro_demo(90);
drop procedure if exists pro_demo;

循环结构

loop leave 示例

delimiter $$
drop procedure if exists pro_demo $$
create procedure pro_demo()
begin
-- 声明变量
declare result varchar(100) default '1';
declare i int default 2;
demo_loop:
loop
if i >= 11 then
leave demo_loop;
end if;
set result = concat(result, ',', i);
set i = i + 1;
end loop demo_loop;
select result;
end $$
delimiter ;
call pro_demo();
drop procedure if exists pro_demo;

loop iterate 示例

delimiter $$
drop procedure if exists pro_demo $$
create procedure pro_demo()
begin
-- 声明变量
declare result varchar(100) default '1';
declare i int default 2;
demo_loop:
loop
set result = concat(result, ',', i);
set i = i + 1;
if i <= 10 then
iterate demo_loop;
end if;
leave demo_loop; -- 退出
end loop demo_loop;
select result;
end $$
delimiter ;
call pro_demo();
drop procedure if exists pro_demo;

repeat 示例

delimiter $$
drop procedure if exists pro_demo $$
create procedure pro_demo()
begin
-- 声明变量
declare result varchar(100) default '1';
declare i int default 2;
demo_repeat:
repeat
set result = concat(result, ',', i);
set i = i + 1;
until i >= 11 end repeat demo_repeat;
select result;
end $$
delimiter ;
call pro_demo();
drop procedure if exists pro_demo;

while 循环结构

delimiter $$
drop procedure if exists pro_demo $$
create procedure pro_demo()
begin
-- 声明变量
declare result varchar(100) default '1';
declare i int default 2;
demo_while:
while i <= 10 do
set result = concat(result, ',', i);
set i = i + 1;
end while demo_while;
select result;
end $$
delimiter ;
call pro_demo();
drop procedure if exists pro_demo;

课程小作业

请打印出 100 ~ 999 之间所有的回文数，如：101, 212, 999

delimiter $$
drop procedure if exists pro_demo $$
drop table if exists demo_data $$
create table demo_data(
id int auto_increment,
info int,
PRIMARY KEY(id)
) ENGINE=INNODB DEFAULT CHARSET = utf8 $$
create procedure pro_demo()
begin
declare i int default 100;
declare a int; -- 百位数
declare b int; -- 个位数
demo_while:
while i <= 999 do
set a = floor(i / 100);
set b = i % 10;
if a = b then
insert into demo_data (info) values (i);
end if;
set i = i + 1;
end while demo_while;
end $$
delimiter ;
call pro_demo();
select * from demo_data;
drop procedure if exists pro_demo;
drop table if exists demo_data;

4.补充知识

游标

作用

1）类似于 Java 当中的 ResultSet

2）为了处理数据集当中的行，一次性只能处理一行

需要严格按顺序创建，打开，关闭游标

语法

创建

declare 游标名字 cursor for SQL语句(已经确定了所选择的列)

打开游标

open 游标名字

从游标中获取数据

fetch 游标名字 into 变量1,.....

关闭游标

close 游标名字

示例

先创建一张表

actor

变量

first_name

last_name

actor_id

运行

delimiter $$
drop procedure if exists pro_demo $$

create procedure pro_demo()
begin
declare var_first_name varchar(100);
declare var_last_name varchar(100);
-- 一定在最前面声明游标结束的标记
declare flag int default 1; -- 有数据

-- 声明游标
declare cursor_actor cursor for
select first_name, last_name from actor where actor_id <= 5;

-- 使用 handler 来决定什么时候结束游标 NOT FOUND
declare CONTINUE HANDLER for 1329 set flag = 0;

-- 打开游标
open cursor_actor;

demo_loop:
loop
-- 从游标中获取数据
fetch cursor_actor into var_first_name, var_last_name;
if flag = 1 then
select var_first_name, var_last_name;
else
leave demo_loop;
end if;
end loop demo_loop;
end $$
delimiter ;
call pro_demo();
drop procedure if exists pro_demo;

5.课后作业

（1）写一个存储过程，打印水仙花数

-- 水仙花数
delimiter $$
drop procedure if exists pro_demo $$
drop table if exists demo_data $$
-- 创建临时数据表：存放水仙数
create table demo_data(id int, info int) $$
create procedure pro_demo()
begin
-- 100 ~ 999
declare i int default 100;
declare a int default 0; -- 百位
declare b int default 0; -- 十位
declare c int default 0; -- 个位
declare id int default 1; -- 水仙花数的个数
demo_while:
while i < 1000 do
set a = floor(i / 100);
set b = floor(i / 10) % 10;
set c = i % 10;
if i = a*a*a + b*b*b + c*c*c then
insert into demo_data(id, info) value (id, i);
set id = id + 1;
end if;
set i = i + 1;
end while demo_while;
end $$
delimiter ;
call pro_demo();
select * from demo_data;
drop procedure if exists pro_demo $$
drop table if exists demo_data $$

水仙花数是指一个 3 位数，它的每个位上的数字的 3次幂之和等于它本身（例如：1^3 + 5^3+ 3^3 = 153）。

（2）写一个存储过程，要求有两个参数，一个IN(n)，一个OUNT(result)
请计算 1+...+n的结果，并通过参数 result 返回

delimiter $$
drop procedure if exists p2 $$
create procedure p2(in n int,out total int)

begin
set total=0;
declare num int default 0;

do_while:
while num < n do
set num=num+1;
set total=total+num;
end while;
end$$

delimiter ;
call p2(10,@sumq);
select @sumq;