首页 > 数据库 >软件设计之MySQL(7)

软件设计之MySQL(7)

时间：2024-08-25 18:22:32浏览次数：15  
标签：存储 name 软件设计 CREATE 视图 MySQL id SELECT

软件设计之MySQL(7)

此篇应在JavaSE之后进行学习:
路线图推荐：
【Java学习路线-极速版】【Java架构师技术图谱】
Navicat可以在软件管家下载
``
使用navicat连接mysql数据库创建数据库、表、转储sql文件，导入sql数据
MySQL数据库入门到大牛，mysql安装到优化，百科全书级，全网天花板
资料可以去尚硅谷官网免费领取

学习内容：

  1. 视图概述
  2. 存储过程概述
  3. 存储函数
  4. 存储过程和函数的比较
  5. 存储过程使用的优缺点

1、视图概述

1、视图是一种 虚拟表 ，本身是不具有数据的，占用很少的内存空间，它是 SQL 中的一个重要概念。
2、 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表
在这里插入图片描述
3、视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然
4、向视图提供数据内容的语句为SELECT，可以将视图理解为存储起来的SELECT语句

创建视图

准备工作
#准备工作
CREATE DATABASE dbtest;
USE dbtest;
CREATE TABLE emps
AS
SELECT * 
FROM atguigudb.`employees`;

CREATE TABLE depts
AS
SELECT *
FROM atguigudb.`departments`;
针对单表情况
#针对于单表
#情况1：视图中的字段在基表中有对应字段
CREATE VIEW vu_emp1
AS
SELECT employee_id,last_name,salary
FROM emps;
#确定视图字段名的方式1：
CREATE VIEW vu_emp2
AS 
#查询语句中字段的别名会作为字段的名称出现
SELECT employee_id emp_id,last_name lname,salary
FROM emps
WHERE salary > 8000;
#确定视图字段名的方式2：
CREATE VIEW vu_emp3(emp_id,NAME,monthly_sal)#括号内的字段别名个数与SELECT字段个数相同
AS 
SELECT employee_id emp_id,last_name lname,salary
FROM emps
WHERE salary > 8000;
#情况2：视图中的字段在基表中可能没对应字段
CREATE VIEW vu_emp_sal
AS
SELECT department_id,AVG(salary) avg_sal
FROM emps
WHERE department_id IS NOT NULL
GROUP BY department_id;
针对多表情况
#针对于多表
CREATE VIEW vu_emp_dept
AS
SELECT e.employee_id,e.department_id,d.department_name
FROM emps e JOIN depts d
ON e.`department_id` = d.`department_id`
#利用视图对数据进行格式化
CREATE VIEW vu_emp_dept1
AS
SELECT CONCAT(e.last_name,'(',d.department_name,')')
FROM emps e JOIN depts d
ON e.`department_id` = d.`department_id`;
基于视图创建视图
CREATE VIEW vu_emp4
AS 
SELECT employee_id,last_name
FROM vu_emp1;
查看视图
#语法1：查看数据库的表对象、视图对象
SHOW TABLES;
#语法2：查看视图的结构
DESC / DESCRIBE 视图名称;
#语法3：查看视图的属性信息
# 查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
SHOW TABLE STATUS LIKE '视图名称'\G
#语法4：查看视图的详细定义信息
SHOW CREATE VIEW 视图名称;
更新视图中的数据

不能更新的视图情况：
1、在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，视图将不支持INSERT和DELETE操作；
2、视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作；
3、在定义视图的SELECT语句中使用了JOIN联合查询，视图将不支持INSERT和DELETE操作；
4、在定义视图的SELECT语句后的字段列表中使用了 数学表达式 或 子查询 ，视图将不支持INSERT，也不支持UPDATE使用了数学表达式、子查询的字段值；
在定义视图的SELECT语句后的字段列表中使用 DISTINCT 、 聚合函数 、 GROUP BY 、 HAVING 、UNION 等，视图将不支持INSERT、UPDATE、DELETE；
5、在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持INSERT、UPDATE、DELETE；
6、视图定义基于一个 不可更新视图 ；
7、常量视图。

#一般情况下，可以更新视图数据的情况
SELECT employee_id,last_name,salary
FROM emps;
#更新视图的数据，会导致基表中的数据修改
UPDATE vu_emp1
SET salary = 20000
WHERE employee_id = 101;
#更新表中的数据，也会导致视图中的数据修改
UPDATE emps
SET salary = 10000
WHERE employee_id = 101;
修改视图
#方式1
CREATE OR REPLACE VIEW vu_emp1
AS
SELECT employee_id,last_name,salary,email
FROM emps
WHERE salary > 7000;
#方式2
ALTER VIEW vu_emp1
AS
SELECT employee_id,last_name,salary,email,hire_date
FROM emps
删除视图

基于视图a、b创建了新的视图c，如果将视图a或者视图b删除，会导致视图c的查询失败。这样的视图c需要手动删除或修改，否则影响使用

SHOW TABLES;
DROP VIEW vu_emp4;
DROP VIEW  IF EXISTS vu_emp2,vu_emp3;

2、存储过程概述

1、含义：存储过程是一组经过 预先编译 的 SQL 语句的封装。
2、执行过程：存储过程预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。

语法结构

IN ：当前参数为输入参数，也就是表示入参；存储过程只是读取这个参数的值。如果没有定义参数种类， 默认就是 IN ，表示输入参数。
OUT ：当前参数为输出参数，也就是表示出参；执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
INOUT ：当前参数既可以为输入参数，也可以为输出参数。
characteristics表示创建存储过程时指定的对存储过程的约束条件（不细讲）
DELIMITER设置新的结束标记，因为MySQL默认的语句结束符号为分号‘;’。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符
注意： navicat执行存储过程的时候，会默认就自动设置delimiter 为其他符号，不需要额外修改!!

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
	存储过程体
END
创建与调用存储过程
#这是在Navicat中运行的，所以这里没设置结束标记
# 创建存储过程
#类型1：无参
CREATE PROCEDURE  select_all_data()
BEGIN
	SELECT *
	FROM employees;
END 
#调用1：
CALL select_all_data();
#类型2：带OUT
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
		SELECT MIN(salary) INTO ms
		FROM employees;
END
#调用2：
CALL show_min_salary(@ms);
#查看变量值
SELECT @ms;
#类型3：带IN
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
		SELECT salary 
		FROM employees
		WHERE last_name = empname;
END
#调用3：
CALL show_someone_salary('Abel');
#类型4：带IN和OUT
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DOUBLE)
BEGIN
		SELECT salary INTO empsalary
		FROM employees
		WHERE last_name = empname;
END
#调用4：
CALL show_someone_salary2('Abel',@empsalary);
SELECT @empsalary;

#类型5：带INOUT
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
BEGIN
		SELECT last_name INTO empname
		FROM employees 
		WHERE employee_id = (
													SELECT manager_id
													FROM employees
													WHERE last_name = empname
													);
END
#调用5：
SET @empname := 'Abel';
CALL show_mgr_name(@empname);
SELECT  @empname;

3、存储函数

语法结构

1、参数列表：指定参数为IN、OUT或INOUT只对PROCEDURE是合法的，FUNCTION中总是默认为IN参数。
2、RETURNS type 语句表示函数返回数据的类型；RETURNS子句只能对FUNCTION做指定，对函数而言这是 强制 的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value 语句。
3、characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同，这里不再赘述
4、函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略BEGIN…END

CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics ...]
BEGIN
函数体 #函数体中肯定有 RETURN 语句
END
注意点1

在这里插入图片描述

注意点2

在这里插入图片描述

创建与调用存储函数
SET GLOBAL log_bin_trust_function_creators = 1;
#类型1
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
BEGIN
			RETURN(SELECT email FROM employees WHERE last_name = 'Abel');
END
#调用
SELECT email_by_name();
#类型2

CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
BEGIN
			RETURN(SELECT email FROM employees WHERE employee_id = emp_id);
END
#调用
 SELECT email_by_id(101);

4、存储过程和函数的比较

在这里插入图片描述

存储过程和函数的查看
#1. 使用SHOW CREATE语句查看存储过程和函数的创建信息
SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名
#2. 使用SHOW STATUS语句查看存储过程和函数的状态信息
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
#3. 从information_schema.Routines表中查看存储过程和函数的信息
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];
存储过程和函数的修改

修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性。使用ALTER语句实现。

ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]
存储过程和函数的删除
DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名

5、 存储过程使用的优缺点

优点

在这里插入图片描述

缺点

在这里插入图片描述

标签：存储,name,软件设计,CREATE,视图,MySQL,id,SELECT
From： https://blog.csdn.net/thm19990903/article/details/141503998

相关文章

  • zblog网站提示MySQL数据库无法连接
    前天搭建好的博客网站，在睡了一觉之后，网站竟然出现故障无法正常运行了，在此期间，没有进行任何的操作。就连主页都无法进入，页面一直提示“MySQL数据库无法连接”。不管怎样刷新页面，还是重启服务器，都毫无作用。最终，从中午1点一直忙活到凌晨2点，还好皇天不负有心人，总算把问题解决了......
  • sql server导入mysql，使用python多线程
    概述在上一篇文章中，链接：https://www.cnblogs.com/xiao987334176/p/18377915使用工具SQLyog进行导入，传输过程是单进程的，一个表一个表的传，一条条数据插入，所以传输速度会比较慢。如果sqlservermdf文件在200m左右，传输需要花费30分钟左右。如果来了一个10GB左右的mdf的文件，需要25......
  • 面试官问什么？Python基础与进阶？介绍Django框架？MySQL数据库索引？
    Catalog自我介绍Python001.Python支持哪些数据类型？01.数字类型02.序列类型03.映射类型04.集合类型05.其他类型06.特点总结002.什么是模块（module），如何导入一个模块？01.导入模块02.`import`和`from...import...`的区别003.高频发问题004.数据去重005.Python中......
  • linux下试验中间件canal的example示例-binlog日志的实时获取显示以及阿里巴巴中间件ca
    一、linux下试验中间件canal的example示例-binlog日志的实时获取显示    今天重装mysql后，进行了canal的再次试验，原来用的mysql5.7,今天重装直接换了5.6算了。反正测试服务器的mysql也不常用。canal启动后日志显示examplepreparetofindstartpositionjustshowmaste......
  • MySQL数据库的带参数的存储过程
    在MySQL数据库中，带参数的存储过程是一种接受输入参数的存储过程。通过使用参数，您可以在存储过程内部访问和处理外部传递的数据。创建带参数的存储过程：使用CREATEPROCEDURE语句可以创建带参数的存储过程。参数可以是输入参数（IN）、输出参数（OUT）或输入输出参数（INOUT）。参数可以指......
  • MySQL里面的子查询
    一、子查询定义定义：子查询允许把一个查询嵌套在另一个查询当中。子查询，又叫内部查询，相对于内部查询，包含内部查询的就称为外部查询。子查询可以包含普通select可以包括的任何子句，比如：distinct、groupby、orderby、limit、join和union等；但是对应的外部查询必......
  • mysql having的用法
    having的用法having字句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在groupby和having字句前。而having子句在聚合后对组记录进行筛选。我的理解就是真实表中没有此数据，这些数据是通过一些函数生存。SQL实例：一、显示每个地区的总人口数和总面积......
  • Windows系统安装MySQL
    在Windows中安装mysql不够幸运的话，会遇到相当多的坑，当然这也算是一种财富吧，让自己碰到问题去查找解决方案。有时候不是一时半会就可以解决的。有同学说过安装mysql安装两天还没有装上。不用担心，我安装mysql用了一天吧，下面记录下来安装过程及可能遇到的问题。工欲善其事，必先利其......
  • MySQL变量的使用
    在mysql文档中，mysql变量可分为两大类，即系统变量和用户变量。但根据实际应用又被细化为四种类型，即局部变量、用户变量、会话变量和全局变量。 一、局部变量mysql局部变量，只能用在begin/end语句块中，比如存储过程中的begin/end语句块。其作用域仅限于该语句块。--declare语......
  • MySQL常用字符串函数
    1、LOWER(column|str)：将字符串参数值转换为全小写字母后返回mysql>selectlower('SQLCourse');+---------------------+|lower('SQLCourse')|+---------------------+|sqlcourse|+---------------------+ 2、UPPER(column|str)：将字符串参数值转换为......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99