MySQL-4

Destiny_9991

已于 2022-03-18 22:05:36 修改

阅读量465

点赞数

分类专栏：自学java

于 2022-03-18 22:04:18 首次发布

本文链接：https://blog.csdn.net/CTSOL/article/details/123585626

版权

存储过程函数触发器权限控制数据库管理

关键词由CSDN通过智能技术生成

自学java 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

十八、存储过程

18.1 是什么？

存储过程是一个sql语句的集合，和我们java中的方法一样。

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

特点

1、能完成较复杂的判断和运算
2、可编程行强，灵活
3、SQL编程的代码可重复使用
4、执行的速度相对快一些
5、减少网络之间的数据传输，节省开销

18.2 创建存储过程

语法：

create    procedure     过程名（参数）

begin

过程体：解决问题的语句组；

end；

在这里插入图片描述

18.3 删除存储过程

语法：

drop procedure 过程名;

18.4 调用存储过程

语法：

call 过程名(实参)；

在这里插入图片描述

18.5 过程体中声明变量

声明：

declare 变量名数据类型【default(缺省值)】；

赋值：

set 变量名 = 值；

select 查询字段 into 变量名 …

在这里插入图片描述

18.6 流程控制语句

（1）分支

第一种：

if-then-else
在这里插入图片描述

第二种：

case-when-else

（2）循环

第一种：while

在这里插入图片描述

第二种：loop
在这里插入图片描述

第三种：repeat

在这里插入图片描述

18.7 参数

参数类型：in out inout

输入值使用in参数。
返回值使用out参数。
inout参数就尽量的少用。

（1）in

在这里插入图片描述

（2）out

在这里插入图片描述

18.8 游标

存储查询结果的集合。

--  游标
CREATE PROCEDURE myway () BEGIN
	DECLARE
		deptno INT;
	DECLARE
		dname VARCHAR ( 20 );
	DECLARE
		loc VARCHAR ( 20 );
	DECLARE
		done INT DEFAULT FALSE;
		-- 声明游标
	DECLARE
		cs CURSOR FOR SELECT
		* 
	FROM
		dept;
		-- 游标读写完毕标记
	DECLARE
		CONTINUE HANDLER FOR NOT found 
		SET done = TRUE;
-- 遍历游标：从游标中取数据
-- (1)打开游标
	OPEN cs;
	-- (2) 取一行数据
	cc :
	LOOP
			FETCH cs INTO deptno,
			dname,
			loc;
		IF
			done THEN
				LEAVE cc;
			ELSE SELECT
				deptno,
				dname,
				loc;
			
		END IF;
		
	END LOOP;
	-- (3)关闭游标
	CLOSE cs;
	
END;
CALL myway ();

18.9 存储过程的优缺点

原来的开发，金融、电信、银行、国家政府系统项目，他们都喜欢使用存储过程。

优点：

将复杂的业务封装到存储过程中，我们Java开发将变得很简单。

将核心业务隐藏在存储过程中，对外透明，提升系统的安全性。

缺点：

不同的数据库，存储过程的语法有区别，不方便移植。

数据库的负载比较重。

十九、函数

和java中有返回值的方法一样。

19.1 系统函数

（1）数字函数

abs(x) 求x的绝对值

rand() 返回0到1的随机数

round(x) x四舍五入取整

select  abs(-10);
select  rand();
select  round(5.5);

(2)字符串函数

ascii(str) 返回str的ascii码

length(str) 返回字符串的长度

concat(str1,str2) 字符串拼接

Lcase(str)转换成小写

Ucase(str)转换成大写

trim(str) 取消前后的空格

substr（str,开始位置，长度）字符串截取

replace(str,旧字符，新字符) 替换

select   ascii('a');
select  LENGTH('adfalmdfjkl');
select  concat('asss','12222');
select  LCASE('aaaaSSSS');
select  UCASE('aaaaSSSS');
select  trim('    d d    ');
select  substr('abcdefg',2,3);
select  REPLACE('abacda','a','f');

(3)日期函数

now() 返回当前日期时间

curDate() 返回当前日期

curTime() 返回当前时间

date(d) 获取d中年月日

day（d）获取日

month（d）获取月

year(d) 获取年

dateDiff(d1,d2)返回两个日期的差值

select  now();
select  curdate();
select  curtime();
select  date('2020-2-18 17:00:20')
select  day('2020-2-18 17:00:20')
select  month('2020-2-18 17:00:20');
select  year('2020-2-18 17:00:20');

select  datediff('2020-2-08 17:00:20','2020-2-10 17:00:20')

(4)聚合函数

用于统计的函数

max（列名）

min（列名）

sum（列名）

avg（列名）

count（列名）

（5）其他函数

if（exp,v1,v2）如果表达式成立返回v1,否则返回v2。

ifnull(exp1,exp2) 如果exp1为null，返回exp2的结果；否则返回exp1。

database() 获取当前的数据库名

CURRENT_USER() 获取当前用户

version() 获取mysql版本

select  if(1<0,'yes','no');
select  IFNULL(null,0);

select  DATABASE();
select  CURRENT_USER();
select  version();

19.2 自定义函数

语法：

create function 函数名([参数列表]) returns 数据类型
DETERMINISTIC
begin
 sql语句;
 return 值;
end;

-- 自定义函数
CREATE FUNCTION myway2 ( a INT, b INT ) RETURNS INT DETERMINISTIC BEGIN
	RETURN a + b;
	
END;
SELECT
	myway2 ( 11, 66 );

调用：

select 函数名(实参);

删除函数：

drop function 函数名;

19.3 函数和存储过程区别

存储函数有返回值，存储过程没有返回值（可以输出数据）；

存储函数使用select调用，存储过程使用call调用。

二十、触发器

20.1 作用

mysql的触发器和java的监听器作用是一样。

当数据库中执行对应的操作，就会触发执行我们触发器中的代码。

触发器包含两个功能：监听和处理。

20.2 创建触发器

语法：

触发时机

before:在操作之前执行处理代码

after:在操作完毕后执行处理代码

监听的操作：

insert / delete /update

操作的表：

on 表名

create trigger     触发器名称  

**after**/before      **insert**/**update**/**delete**       **on** 表名 

 **for** each row

begin

//处理操作

end;

-- 触发器
CREATE TRIGGER dept_insert_tri AFTER INSERT ON dept FOR EACH ROW
BEGIN
	DECLARE
		a INT;
	
	SET a = 1;
	INSERT INTO test ( count )
	VALUES
		( a );
END;

insert into dept values(90,'mysql','guangzhou');
insert into dept values(100,'html','hangzhou');

在这里插入图片描述

20.3 测试触发器

在这里插入图片描述

20.4 删除触发器

drop trigger 触发器名

二十一、用户和权限

21.1 创建用户

系统安装时，会自动创建超级管理员root，拥有任何权限。

但是在实际开发时，不同的工作人员一般会分配相应权限的用户，不会使用root。

java开发人员，主要的数据库操作是：数据的CRUD。

创建新用户：

create user 用户名 @主机 IDENTIFIED by 密码；

create user gao@localhost identified by '115598';

21.2 删除用户

drop user 用户名;

21.3 修改密码

使用管理员登录：

alter user 用户名@主机 identified by ‘新密码’;

21.4 授权

给用户赋予相应的操作权限

使用管理员登录：

grant 权限 to 用户名@主机名;

21.5 mysql权限

GRANT语句中的<权限类型>的使用说明如下：

授予数据库权限时，<权限类型>可以指定为以下值：

SELECT：表示授予用户可以使用 SELECT 语句访问特定数据库中所有表和视图的权限。
INSERT：表示授予用户可以使用 INSERT 语句向特定数据库中所有表添加数据行的权限。
DELETE：表示授予用户可以使用 DELETE 语句删除特定数据库中所有表的数据行的权限。
UPDATE：表示授予用户可以使用 UPDATE 语句更新特定数据库中所有数据表的值的权限。
REFERENCES：表示授予用户可以创建指向特定的数据库中的表外键的权限。
CREATE：表示授权用户可以使用 CREATE TABLE 语句在特定数据库中创建新表的权限。
ALTER：表示授予用户可以使用 ALTER TABLE 语句修改特定数据库中所有数据表的权限。
SHOW VIEW：表示授予用户可以查看特定数据库中已有视图的视图定义的权限。
CREATE ROUTINE：表示授予用户可以为特定的数据库创建存储过程和存储函数的权限。
ALTER ROUTINE：表示授予用户可以更新和删除数据库中已有的存储过程和存储函数的权限。
INDEX：表示授予用户可以在特定数据库中的所有数据表上定义和删除索引的权限。
DROP：表示授予用户可以删除特定数据库中所有表和视图的权限。
CREATE TEMPORARY TABLES：表示授予用户可以在特定数据库中创建临时表的权限。
CREATE VIEW：表示授予用户可以在特定数据库中创建新的视图的权限。
EXECUTE ROUTINE：表示授予用户可以调用特定数据库的存储过程和存储函数的权限。
LOCK TABLES：表示授予用户可以锁定特定数据库的已有数据表的权限。
ALL 或 ALL PRIVILEGES：表示以上所有权限。

2）授予表权限时，<权限类型>可以指定为以下值：

SELECT：授予用户可以使用 SELECT 语句进行访问特定表的权限。
INSERT：授予用户可以使用 INSERT 语句向一个特定表中添加数据行的权限。
DELETE：授予用户可以使用 DELETE 语句从一个特定表中删除数据行的权限。
DROP：授予用户可以删除数据表的权限。
UPDATE：授予用户可以使用 UPDATE 语句更新特定数据表的权限。
ALTER：授予用户可以使用 ALTER TABLE 语句修改数据表的权限。
REFERENCES：授予用户可以创建一个外键来参照特定数据表的权限。
CREATE：授予用户可以使用特定的名字创建一个数据表的权限。
INDEX：授予用户可以在表上定义索引的权限。
ALL 或 ALL PRIVILEGES：所有的权限名。

3）授予列权限时，<权限类型>的值只能指定为 SELECT、INSERT 和 UPDATE，同时权限的后面需要加上列名列表 column-list。

最有效率的权限是用户权限。

授予用户权限时，<权限类型>除了可以指定为授予数据库权限时的所有值之外，还可以是下面这些值：

CREATE USER：表示授予用户可以创建和删除新用户的权限。
SHOW DATABASES：表示授予用户可以使用 SHOW DATABASES 语句查看所有已有的数据库的定义的权限。

二十二、数据的备份和操作

22.1 备份

1.直接在可视化软件里找到需要备份的数据库，鼠标右键点击，转储SQL文件，选择数据和结构或者仅结构

22.2 恢复

新建数据库，导入SQL文件即可。

-- 2.单表的备份
create table dept2 as select * from dept;

Destiny_9991

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录