数据库多表查询的视图和存储过程

于 2024-07-14 10:59:09 发布
阅读量737 收藏 16
点赞数 16
文章标签： 数据库 oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2302_76732016/article/details/140383045
版权

创建两张表：部门（dept）和员工（emp），并插入数据，代码如下
    create table dept(
        dept_id int primary key auto_increment comment '部门编号',
        dept_name char(20) comment '部门名称'
    );
    insert into dept(dept_name) values('销售部'),('财务部'),('生产部'),('人事部');

    create table emp(
        emp_id int primary key auto_increment comment '员工号',
        emp_name char(20) not null default '' comment '员工姓名',
        gender char(2) not null default '男' comment '性别',
        birth datetime not null default '1990-1-1' comment '出生日期',
        salary decimal(10,2) not null default 0 comment '工资',
        address varchar(200) not null default '' comment '通讯地址',
        dept_id int comment '部门编号'
    );

    create index idx_name on emp(emp_name);
    create index idx_birth on emp(birth);
    create index idx_deptid_name on emp(dept_id,emp_name);

    insert into emp(emp_name,gender,birth,salary,address,dept_id) 
    values('张晓红','女','1980-1-23',5800,'河南省郑州市中原路10号',1),
    ('张静静','女','1987-10-3',5400,'河南省新乡市平原路38号',1),
    ('王云飞','男','1992-11-15',5600,'河南省新乡市人民路28号',1),
    ('王鹏飞','男','1987-10-1',6800,'河南省新乡市东明大道12号',1),
    ('王大鹏','男','1989-2-11',5900,'河南省郑州市东风路15号',1),
    ('王萌萌','女','1986-12-30',5000,'河南省开封市五一路14号',2),
    ('王大光','男','1988-11-8',6200,'河南省开封市八一路124号',2),
    ('王小明','男','1998-1-3',4800,'河南省驻马店市雪松路128号',2),
    ('王娜娜','女','1994-3-5',5200,'河南省驻马店市车站路2号',2),
    ('刘云飞','男','1992-8-13',6800,'河南省南阳市民生路255号',3),
    ('张陆军','男','1991-9-6',6200,'河南省南阳市张仲景路14号',3);

mysql> create table dept(
    -> dept_id int primary key auto_increment comment'部门编号',
    -> dept_name char(20) comment'部门名称');
Query OK, 0 rows affected (0.02 sec)

mysql> insert into dept(dept_name) values('销售部'),('财务部'),('生产部'),('人事
部');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> create table emp(
    -> emp_id int primary key auto_increment comment'员工号',
    -> emp_name char(20) not null default'' comment'员工姓名',
    -> gender char(2) not null default'男' comment'性别',
    -> birth datetime not  null default'1990-1-1' comment'出生日期',
    -> salary decimal(10,2) not null default 0 comment'工资',
    -> address varchar(200) not null default'' comment'通讯地址',
    -> dept_id int comment'部门编号');
Query OK, 0 rows affected (0.02 sec)

mysql> create index idx_name on emp(emp_name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx_birth on emp(birth);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx_deptid_name on emp(dept_id,emp_name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into emp(emp_name,gender,birth,salary,address,dept_id)
    -> values('张晓红','女','1980-1-23',5800,'河南省郑州市中原路10号',1),
    -> ('张静静','女','1987-10-3',5400,'河南省新乡市平原路38号',1),
    -> ('王云飞','男','1992-11-15',5600,'河南省新乡市人民路28号',1),
    -> ('王鹏飞','男','1987-10-1',6800,'河南省新乡市东明大道12号',1),
    -> ('王大鹏','男','1989-2-11',5900,'河南省郑州市东风路15号',1),
    -> ('王萌萌','女','1986-12-30',5000,'河南省开封市五一路14号',2),
    -> ('王大光','男','1988-11-8',6200,'河南省开封市八一路124号',2),
    -> ('王小明','男','1998-1-3',4800,'河南省驻马店市雪松路128号',2),
    -> ('王娜娜','女','1994-3-5',5200,'河南省驻马店市车站路2号',2),
    -> ('刘云飞','男','1992-8-13',6800,'河南省南阳市民生路255号',3),
    -> ('张陆军','男','1991-9-6',6200,'河南省南阳市张仲景路14号',3);
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

1、创建视图v_emp_dept_id_1，查询销售部门的员工姓名和家庭住址

mysql> create view v_emp_dept_id_1 as
    -> select emp_name,address from emp e
    -> inner join dept d
    -> on e.dept_id=d.dept_id
    -> where dept_name='销售部';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v_emp_dept_id_1;
+----------+--------------------------+
| emp_name | address                  |
+----------+--------------------------+
| 张晓红   | 河南省郑州市中原路10号   |
| 张静静   | 河南省新乡市平原路38号   |
| 王云飞   | 河南省新乡市人民路28号   |
| 王大鹏   | 河南省郑州市东风路15号   |
| 王鹏飞   | 河南省新乡市东明大道12号 |
+----------+--------------------------+
5 rows in set (0.00 sec)


2、创建视图v_emp_dept，查询销售部门员工姓名和家庭住址及部门名称。

mysql> create view v_emp_dept as
    -> select emp_name,address,dept_name from emp e
    -> inner join dept d
    -> on e.dept_id=d.dept_id
    -> where dept_name='销售部';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v_emp_dept;
+----------+--------------------------+-----------+
| emp_name | address                  | dept_name |
+----------+--------------------------+-----------+
| 张晓红   | 河南省郑州市中原路10号   | 销售部    |
| 张静静   | 河南省新乡市平原路38号   | 销售部    |
| 王云飞   | 河南省新乡市人民路28号   | 销售部    |
| 王大鹏   | 河南省郑州市东风路15号   | 销售部    |
| 王鹏飞   | 河南省新乡市东明大道12号 | 销售部    |
+----------+--------------------------+-----------+
5 rows in set (0.00 sec)


3、创建视图v_dept_emp_count(dept_name,emp_count,avg_salay)，统计每个部门人数并计算平均工资。

mysql> create view v_dept_emp_count(dept_name,emp_count,avg_salay) as
    -> select dept_name,count(1),avg(salary)
    -> from emp e inner join dept d
    -> on e.dept_id=d.dept_id
    -> group by dept_name;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from  v_dept_emp_count;
+-----------+-----------+-------------+
| dept_name | emp_count | avg_salay   |
+-----------+-----------+-------------+
| 销售部    |         5 | 5900.000000 |
| 财务部    |         4 | 5300.000000 |
| 生产部    |         2 | 6500.000000 |
+-----------+-----------+-------------+
3 rows in set (0.00 sec)


4、修改视图v_emp_dept，查询销售部门员工姓名、家庭住址、工资和部门名称。

mysql> alter view v_emp_dept as
    ->   select emp_name,address,salary,dept_name from emp e
    ->      inner join dept d
    ->    on e.dept_id=d.dept_id
    ->    where dept_name='销售部';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v_emp_dept;
+----------+--------------------------+---------+-----------+
| emp_name | address                  | salary  | dept_name |
+----------+--------------------------+---------+-----------+
| 张晓红   | 河南省郑州市中原路10号   | 5800.00 | 销售部    |
| 张静静   | 河南省新乡市平原路38号   | 5400.00 | 销售部    |
| 王云飞   | 河南省新乡市人民路28号   | 5600.00 | 销售部    |
| 王大鹏   | 河南省郑州市东风路15号   | 5900.00 | 销售部    |
| 王鹏飞   | 河南省新乡市东明大道12号 | 6800.00 | 销售部    |
+----------+--------------------------+---------+-----------+
5 rows in set (0.00 sec)


5、查看视图名称；

mysql> show full tables;
+------------------+------------+
| Tables_in_data   | Table_type |
+------------------+------------+
| dept             | BASE TABLE |
| emp              | BASE TABLE |
| grade            | BASE TABLE |
| v_dept_emp_count | VIEW       |
| v_emp_dept       | VIEW       |
| v_emp_dept_id_1  | VIEW       |
+------------------+------------+
6 rows in set (0.01 sec)


    查看视图结构；

mysql> desc v_dept_emp_count;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| dept_name | char(20)      | YES  |     | NULL    |       |
| emp_count | bigint        | NO   |     | 0       |       |
| avg_salay | decimal(14,6) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


    查看创建视图语句；

mysql> show create view v_dept_emp_count \G
*************************** 1. row ***************************
                View: v_dept_emp_count
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY
 DEFINER VIEW `v_dept_emp_count` (`dept_name`,`emp_count`,`avg_salay`) AS select
 `d`.`dept_name` AS `dept_name`,count(1) AS `count(1)`,avg(`e`.`salary`) AS `avg
(salary)` from (`emp` `e` join `dept` `d` on((`e`.`dept_id` = `d`.`dept_id`))) g
roup by `d`.`dept_name`
character_set_client: gbk
collation_connection: gbk_chinese_ci
1 row in set (0.00 sec)


6、删除以上三个视图。

mysql> drop view v_dept_emp_count,v_emp_dept,v_emp_dept_id_1;
Query OK, 0 rows affected (0.00 sec)

mysql> show full tables;
+----------------+------------+
| Tables_in_data | Table_type |
+----------------+------------+
| dept           | BASE TABLE |
| emp            | BASE TABLE |
| grade          | BASE TABLE |
+----------------+------------+
3 rows in set (0.00 sec)

存储过程作业：

1、创建一个提取emp表所有员工工资和的存储过程s1

mysql> DELIMITER //
mysql> CREATE PROCEDURE s1()
    -> BEGIN
    ->     SELECT SUM(salary) as total_salary from emp;
    -> END //
Query OK, 0 rows affected (0.01 sec)


2、调用存储过程s1

mysql> call s1();
+--------------+
| total_salary |
+--------------+
|     63700.00 |
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


3、创建存储过程s2，实现输入员工姓名后返回员工的家庭住址。

mysql> DELIMITER $$
mysql> CREATE
    -> PROCEDURE `data`.`s2`(IN NAME CHAR(20))
    -> BEGIN
    ->         SELECT address FROM emp
    ->         WHERE emp_name=NAME;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

4、调用存储过程s2

mysql> call s2('王云飞');
    -> end$$
+------------------------+
| address                |
+------------------------+
| 河南省新乡市人民路28号 |
+------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


5、创建一个存储过程avg_sai，有3个参数，分别是dept，gender，接收平均工资，功能查询emp表dept_id为1，gender为男的平均工资

mysql> DELIMITER $$
mysql> CREATE
    -> PROCEDURE `data`.`avg_sai`(IN dept CHAR(20),IN sex CHAR(2),OUT avg_sai DE
CIMAL(10,2))
    -> BEGIN
    ->         SELECT AVG(salary) INTO avg_sai FROM emp e
    ->         INNER JOIN dept d
    ->         ON e.dept_id=d.dept_id
    ->         WHERE dept_name=dept
    ->         AND gender=sex;
    -> END$$
Query OK, 0 rows affected (0.00 sec)


6、调用存储过程avg_sai


7、删除以上存储过程

炭烧小肥杨
关注
  • 16
    点赞
  • 踩
  • 16
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
SQLServer数据库的查询和视图ppt课件.ppt
11-16
视图是从一个或多个表中选择数据的虚拟表，不存储实际数据，而是存储查询的定义。视图可以简化复杂的查询，提供数据的安全性，并允许对敏感数据进行抽象。创建视图的语法如下： ```sql CREATE VIEW 视图名 AS ...
通过系统数据库获取用户所有数据库中的视图、表、存储过程
09-10
在数据库管理中，有时我们需要获取特定用户在所有数据库中的视图、表和存储过程的相关信息。这通常是出于数据审计、备份、迁移或者性能优化等目的。以下将详细讲解如何通过系统数据库来实现这一目标，主要关注SQL ...
【数据库对象-视图和存储过程】
qq_45466520的博客
03-18 1887
视图 【1】视图的概念 视图（view）是一个从单张或多张基础数据表或其他视图中构建出来的虚拟表。同基础表一样，视图中也包含了一系列带有名称的列和行数据，但是数据库中只是存放视图的定义，也就是动态检索数据的查询语句，而并不存放视图中的数据，这些数据依旧存放于构建视图的基础表中，只有当用户使用视图时才去数据库请求相对应的数据，即视图中的数据是在引用视图时动态生成的。因此视图中的数据依赖于构建视图的基础表，如果基本表中的数据发生了变化，视图中相应的数据也会跟着改变。 **PS:**视图本质上就是：一个查询语句，
MySQL数据库，视图、存储过程与存储函数
二狗mao的博客
12-14 1103
数据库对象：常见的数据库对象： 精简版：关于视图中字段的字段名：（若不起字段名，视图中的字段的字段名即为查询字段的字段名）例： 例： 比如想显示员工的信息的格式为 员工号--姓名--部门号--部门所在地： 或 例： 查看数据库的表对象、视图对象：查看视图的结构：查看视图的属性信息查看视图的详细信息：类似于更新表的数据例： 方式一：方式二：假如基于一个或多个视图创建了额外的视图，将此一个或多个视图删除后，会导致此额外的视图的查询的失败。视图的优点：视图的缺点：存储过程与存储函
mysql 查询存储过程数量_查询数据库中表或视图或存储过程的数量　sql 语句
weixin_35976295的博客
01-19 995
如果一个数据库中表的数量较多的话，那么在统计数据库中表的数量或视图或存储过程的数量时，如果还有一个一个去数，那就太麻烦了，其实可以通过　sql 语句来查询的，sql 语句的查询方法如下：sql server : select count(1) from sysobjects 　where xtype='U' 数据表select count(1) from　sysobjects where xtyp...
SQL数据库查询某个表、视图、存储过程总数量
柠夏
06-16 899
查询某个数据库含表、视图多少个
数据库视图&存储过程&存储函数&触发器总结
m0_58725148的博客
11-02 575
数据库视图&存储过程&存储函数&触发器总结
数据库视图、存储过程
小小本科生
05-11 1649
这里写目录标题一:视图1.视图的概念2.视图的作用3.视图的创建、修改、查看和删除4.视图的介绍一级目录二级目录三级目录一级目录二级目录三级目录一级目录二级目录三级目录 一:视图 1.视图的概念 视图是由从数据库的基本表中选取出来的数据组成的逻辑窗口，是基本表的部分行和列数据的组合。它与基本表不同的是，视图是一个虚表。数据库中只存放视图的定义，而不存放视图包含的数据，这些数据仍存放在原来的基本表中。 2.视图的作用 1.重用SQL语句。 2.简化复杂的SQL操作，不必知道它的查询细节。 3.使用户可以从多角
表、视图、存储过程、函数、触发器、事件
LHX_ldm的博客
07-30 1160
表：里面存储的都是真实的数据 视图：就是一个表或者多个表的一个映射，一般只做查询使用。比如你想要的数据存在两个表里，但你查询时不想每次都写关联，那么你创建一个视图，以后只查询这个视图就可以（查询时视图与查询表语法一样）。 触发器：做数据库操作的时候，还希望相关的数据同步操作就用触发器，比如想要向A表插入数据的时候，同时向B表插入，这样写过触发器每次向A表插入数据之后就会自动向B表插入。 存储过程...
MySQL 子查询，多表联合查询，视图，存储过程详解
jinjuana521的博客
03-30 3249
-----------MySQL数据库中多表联合查询---------- 多表联合查询的理论依据：笛卡尔积 通过笛卡尔乘积，把两个或者多个表变为一个大表，里面包含了有效的，无效的记录（需要加条件进行过滤数据） – 总结 1、多表联合查询，你想要的数据在多个表中，得从多个表中查询获取，但是必须添加条件过滤， 如果不加条件，会出现大量错误数据。 2、条件，连接条件：先分析表跟表之间的关系，分析数据和数据之间的关系，把关系写成 SQL 语句： 多数情况下，表和表之间的关系是主外键关系，但是有特殊，有例外。 3、一
mysql中的视图、触发器和存储过程
金色%夕阳的博客
07-15 825
mysql中的视图、触发器和存储过程 1.视图 1.1 什么是视图 1.2 视图的特点 1.3 视图的使用场景 1.4 语法 1）创建视图 2）查询 3）修改 4）删除 1.5 视图的优点 1.6 视图的缺点 2.触发器 2.1 什么是触发器 2.2 语法
Oracle查询表、视图、存储过程所属用户
时光下的旅途
07-22 3907
1、Oracle查询表所属用户 方式一： SELECT * FROM DBA_TABLES WHERE TABLE_NAME='表名'; 方式二： SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE='TABLE' and OBJECT_NAME='表名' 2、Oracle查询视图所属用户 方式一： SELECT * FROM ALL_VIEWS WHERE VIEW_NAME='视图名'; 方式二： SELECT * FROM AL...
VS2008 C#动态获取数据库视图和存储过程信息
05-09
内容索引:C#源码,数据库应用,存储过程　VS2008编写一C#程序，动态获取数据库中的所有视图和存储过程信息，仅包括代码，暂时不包括数据库，需要手动还愿出数据库。
MySQL数据库：视图与存储过程
07-11
MySQL数据库：视图与存储过程
set user & password for database 设置数据库 用户和密码
DavidsonGong的博客
07-12 139
USE sbms;
7.10飞书一面面经
东篱君的博客
07-10 1174
B+树的⾮叶⼦节点不存放实际的记录数据，仅存放索引，所以数据量相同的情况下，相⽐存储即存索引⼜存记录的 B 树，B+树的⾮叶⼦节点可以存放更多的索引，因此 B+ 树可以⽐ B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。主从复制：单节点Redis的并发能力是有上限的，要进一步提高Redis的并发能力，可以搭建主从集群，实现读写分离。B+ 树有⼤量的冗余节点（所有⾮叶⼦节点都是冗余索引），这些冗余索引让 B+ 树在插⼊、删除的效率都更⾼，⽐如删除根节点的时候，不会像 B 树那样会发⽣复杂的树的变化；
mysql笔记1
m0_62975692的博客
07-11 344
首先查看systemctl status mysqld，如果是关闭的可以进入的配置文件打开mysql，我的mysql配置文件就是在/usr/local/mysql/support-filess/下，在此目录下执行./mysql.server start,打开mysql服务，也可以通过systmectl start mysqld，如果你嫌麻烦的话可以systemctl enable mysqld 一直打开mysql的服务;重新给字段添加约束的时候不可以添加not null约束的！
Redis集群和高可用
最新发布
xiaogengtongxu的博客
07-13 395
主从架构无法实现master和slave角色的自动切换，即当master出现redis服务异常、主机断电、磁盘损坏等问题导致master无法使用，而redis主从复制无法实现自动的故障转移(将slave 自动提升为新master)，需要手动修改环境配置,才能切换到slave redis服务器，另外当单台Redis服务器性能无法满足业务写入需求的时候,也无法横向扩展Redis服务的并行写入性能。master和slave角色的无缝切换，让业务无感知从而不影响业务使用。
Redis数据结构-String篇
时间如瑾 代码如诗
07-12 884
对⼀个内部表示成long型的string执行append, setbit, getrange这些命令，针对的仍然是string的值（即⼗进制表示的字符串），而不是针对内部表⽰的long型进⾏操作。因此，在这些命令的实现中，会把long型先转成字符串再进行相应的操作。String 的内部存储结构⼀般是 sds（Simple Dynamic String，可以动态扩展内存），但是如果⼀个String 类型的 value 的值是数字，那么 Redis 内部会把它转成 long 类型来存储，从⽽减少内存的使用。
如何用sql语句查询数据库中特定的视图和存储过程
07-12
要使用 SQL 语句查询数据库中特定的视图和存储过程，您可以使用以下方法： 1. 查询特定视图： ```sql SELECT * FROM sys.views WHERE name = 'YourViewName'; -- 将 'YourViewName' 替换为要查询的视图名称 ``` 上述查询将返回与指定名称匹配的视图信息，包括名称、所有者、创建日期等。 2. 查询特定存储过程： ```sql SELECT * FROM sys.procedures WHERE name = 'YourProcedureName'; -- 将 'YourProcedureName' 替换为要查询的存储过程名称 ``` 上述查询将返回与指定名称匹配的存储过程信息，包括名称、所有者、创建日期等。 请注意，上述查询将返回系统级别的视图和存储过程信息。如果要查询特定数据库中的视图和存储过程，请使用完全限定的对象名称，例如 `[database_name].sys.views` 或 `[database_name].sys.procedures`。 如果您需要根据其他条件来查询特定的视图和存储过程，可以在上述查询中添加适当的筛选条件，例如按所有者、创建日期等进行筛选。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值