AI 助理
备案控制台
开发者社区 数据库 文章 正文

11：高级部分-MySQL

2023-12-14 51
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，高可用系列 2核4GB
简介： 11：高级部分-MySQL

（一）view 视图

1. 开场

高级部分不属于实习内容，已经超过了实习范围

尤其是培训机构不会讲这些，主要是经验规范之谈

2. view视图创建、使用以及作用

视图主要负责筛选，有意隐藏敏感数据与结构

create view vw_stu as 
select * from eatery natural on stu;

6fc94cb4bcdd47be988c53d608c140e9.png

create view vw_stu_eatery_na as 
select * from stu natural join eatery;

0d396913fe8c44e68113fe671e2ec525.png

3. 显示视图

终端上体现

mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| eatery            |
| emp               |
| score             |
| stu               |
| t_1               |
| t_10              |
| t_11              |
| t_12              |
| t_2               |
| t_3               |
| t_4               |
| t_5               |
| t_6               |
| t_7               |
| t_8               |
| t_9               |
| teacher           |
| vw_stu            |
| vw_stu_eatery_na  |
+-------------------+
19 rows in set (0.00 sec)
mysql> desc vw_stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| stuId | int(4)      | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

查看数据库所有视图

mysql> show create view vw_stu;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View   | Create View
                                                                                  | character_set_client | collation_connection |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| vw_stu | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_stu` AS select `stu`.`stuId` AS `stuId`,`stu`.`name` AS `name` from `stu` | utf8mb4              | utf8mb4_general_ci   |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

视图其实就是一张虚拟的表，所以show table status

但视图与普通的表不同，所以status where comment='view'

\G表示全部

mysql> show table status where comment ='view' \G
*************************** 1. row ***************************
           Name: vw_stu
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
*************************** 2. row ***************************
           Name: vw_stu_eatery_na
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
2 rows in set (0.02 sec)

4. 更新和删除视图

更新视图

alter view vw_stu as 
select name from stu;

32c3108312a54c90b75f76ee3ac29497.png

drop view vw_stu

9178eb9e6f6c4e368156c32b15c85b60.png


5. 视图算法： temptable, merge

视图中使用子查询会出现问题

视图算法有合并算法merge、 临时表算法temptable、未定义undefined

（二）transaction 事务

1. 事务的提出

假设你下单一个东西，按常理来说是不是taobao的钱数据增加了，你的钱数据减少了

那如果没付款，那这钱去哪了呢？确认收货的钱又去哪了呢？

难道没付款也是taobao的钱数据增加了，你的钱数据减少了吗？


引用自菜鸟教程：MySQL事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务


2. transaction

开始事务

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    500.87 |
|  2 |    636.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)
mysql> update wallet set balance=balance-50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> update wallet set balance=balance+50 where id =2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    450.87 |
|  2 |    686.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)

一旦commit就不能rollback了，先rollback再commit，查询结果未提交，可以回溯

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update wallet set balance=balance+50 where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    500.87 |
|  2 |    736.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    450.87 |
|  2 |    736.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    450.87 |
|  2 |    736.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)

先commit再rollback，查询结果已经提交，无法回溯

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update wallet set balance=balance+50 where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    500.87 |
|  2 |    736.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    500.87 |
|  2 |    736.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)

3. rollback to point

相当于快照功能，设置回滚点savepoint，回到回滚点rollback to point

mysql> select * from wallet order by id ;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    550.87 |
|  2 |    786.57 |
|  3 | 888938.00 |
|  4 |   1050.00 |
+----+-----------+
4 rows in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into wallet values(5,1000);
Query OK, 1 row affected (0.01 sec)
mysql> savepoint five;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into wallet values(6,5000);
Query OK, 1 row affected (0.00 sec)
mysql> savepoint six;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from wallet order by id ;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    550.87 |
|  2 |    786.57 |
|  3 | 888938.00 |
|  4 |   1050.00 |
|  5 |   1000.00 |
|  6 |   5000.00 |
+----+-----------+
6 rows in set (0.00 sec)
mysql> rollback to five;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from wallet order by id ;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    550.87 |
|  2 |    786.57 |
|  3 | 888938.00 |
|  4 |   1050.00 |
|  5 |   1000.00 |
+----+-----------+
5 rows in set (0.00 sec)

4. ACID

事务是必须满足4个条件（ACID）：

  • 原子性（Atomicity，或称不可分割性）一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节
  • 一致性（Consistency）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏
  • 隔离性（Isolation，又称独立性）：并发事务同时对其数据进行读写和修改的能力
  • 持久性（Durability）：对数据的修改就是永久的

5. 注意事项

事务不是所有数据库都能用，指定数据库引擎为INNODB才能用

（三）索引

1. 四大索引

索引 index

优点：帮助你快速查询到数据

缺点：数据设置为索引，增删改查效率低且占空间

创建目的：索引数据经常被查询，用于提高查询效率

索引分类：普通索引index、主键索引primary key 、唯一索引unique、联合索引、全文索引、空间索引

mysql不支持中文，全局索引搜索引擎可以用sphinx

（四）存储过程

1. delimiter定界符

数据库管理员（Database Administrator，简称DBA）模块化设计

存储过程相当于一个函数，调用大量SQL

当有很多sql语句，每一个分号就是一条语句，直接发送服务区肯定不行

mysql> ;
ERROR:
No query specified

怎么解决这个问题呢?

delimiter用于设定SQL语句分隔符，可以设置以双斜线为结尾

mysql> delimiter //
mysql> select * from wallet;
    -> //
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    500.87 |
|  2 |    736.57 |
|  4 |   1000.00 |
|  3 | 888888.00 |
+----+-----------+
4 rows in set (0.00 sec)

用完之后就还原

mysql> delimiter ;
mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    500.87 |
|  2 |    736.57 |
|  4 |   1000.00 |
|  3 | 888888.00 |
+----+-----------+
4 rows in set (0.00 sec)

2. procedure存储过程的用途

创建存储过程

mysql> delimiter //
mysql> create procedure proc()
    -> begin
    -> update wallet set balance=balance+50;
    -> update teacher set name='Frank';
    -> end //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call proc();
Query OK, 4 rows affected (0.01 sec)
mysql> select * from wallet natural join  teacher;
+----+-----------+-------+--------+----------+
| id | balance   | name  | phone  | address  |
+----+-----------+-------+--------+----------+
|  1 |    550.87 | Frank | NULL   | 暂时未知 |
|  2 |    786.57 | Frank | NULL   | 暂时未知 |
|  3 | 888938.00 | Frank | 123456 | ShangHai |
|  4 |   1050.00 | Frank | NULL   | NULL     |
+----+-----------+-------+--------+----------+
4 rows in set (0.00 sec)

显示存储过程

mysql> delimiter ;
mysql> show create procedure proc;
+-----------+----------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                       | Create Procedure
                                               | character_set_client | collation_connection | Database Collation |
+-----------+----------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| proc      | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `proc`()
begin
update wallet set balance=balance+50;
update teacher set name='Frank';
end | gbk                  | gbk_chinese_ci       | gbk_chinese_ci     |
+-----------+----------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

显示所有存储过程

mysql> show procedure status \G

删除存储过程

mysql> drop procedure proc;
Query OK, 0 rows affected (0.01 sec)

（五）有趣的函数

1. number

随机数

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.7103542850449256 |
+--------------------+
1 row in set (0.00 sec)

随机排序

mysql> select * from wallet order by rand() ;
+----+-----------+
| id | balance   |
+----+-----------+
|  4 |   1050.00 |
|  3 | 888938.00 |
|  1 |    550.87 |
|  2 |    786.57 |
+----+-----------+
4 rows in set (0.00 sec)

向上取整

mysql> select ceil(3.1);
+-----------+
| ceil(3.1) |
+-----------+
|         4 |
+-----------+
1 row in set (0.00 sec)

向下取整

mysql> select floor(3.1);
+------------+
| floor(3.1) |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)

四舍五入

mysql> select round(3.1);
+------------+
| round(3.1) |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)

截取数字

mysql> select truncate(3.141592654,2);
+-------------------------+
| truncate(3.141592654,2) |
+-------------------------+
|                    3.14 |
+-------------------------+
1 row in set (0.00 sec)

2. string

字符串操作

小写字母变大写

mysql> select ucase('abc');
+--------------+
| ucase('abc') |
+--------------+
| ABC          |
+--------------+
1 row in set (0.00 sec)

大写字母变小写

mysql> select lcase('ABC');
+--------------+
| lcase('ABC') |
+--------------+
| abc          |
+--------------+
1 row in set (0.00 sec)

从左截取字符串

mysql> select left('ABC',1);
+---------------+
| left('ABC',1) |
+---------------+
| A             |
+---------------+
1 row in set (0.00 sec)

从右截取字符串

mysql> select right('ABC',1);
+----------------+
| right('ABC',1) |
+----------------+
| C              |
+----------------+
1 row in set (0.00 sec)

分割字符串

mysql> select substring('ABC',1,2);
+----------------------+
| substring('ABC',1,2) |
+----------------------+
| AB                   |
+----------------------+
1 row in set (0.00 sec)

concat()函数用于将多个字符串连接成一个字符串

mysql> select concat('ABC','DEF');
+---------------------+
| concat('ABC','DEF') |
+---------------------+
| ABCDEF              |
+---------------------+
1 row in set (0.00 sec)

mysql> select concat(name,'|',stuId) from stu;
+------------------------+
| concat(name,'|',stuId) |
+------------------------+
| frank|4                |
| Tom|5                  |
+------------------------+
2 rows in set (0.00 sec)

3. others

设置年月日

mysql>  select year(now()) year, month(now()) month, day(now()) day;
+------+-------+------+
| year | month | day  |
+------+-------+------+
| 2021 |    10 |    1 |
+------+-------+------+
1 row in set (0.00 sec)

加密字符串

mysql> select sha("123");
+------------------------------------------+
| sha("123")                               |
+------------------------------------------+
| 40bd001563085fc35165329ea1ff5c5ecbdbbeef |
+------------------------------------------+
1 row in set (0.00 sec)

ifnull()函数用于判断第一个表达式是否为NULL，如果为NULL则返回第二个参数的值，如果不为NULL则返回第一个参数的值

mysql> select ifnull(null,"frank");
+----------------------+
| ifnull(null,"frank") |
+----------------------+
| frank                |
+----------------------+
1 row in set (0.00 sec)
mysql> select ifnull("123","frank");
+-----------------------+
| ifnull("123","frank") |
+-----------------------+
| 123                   |
+-----------------------+
1 row in set (0.00 sec)


文章标签：
云数据库 RDS MySQL 版
索引
关系型数据库
MySQL
存储
数据库
关键词：
云数据库 RDS MySQL 版高级
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
Yeats_Liao
目录
相关文章
江帅帅
|
6月前
|
存储 JSON 关系型数据库
《MySQL 简易速速上手小册》第9章：高级 MySQL 特性和技巧（2024 最新版）
《MySQL 简易速速上手小册》第9章：高级 MySQL 特性和技巧（2024 最新版）
江帅帅
77 1
云松雪
|
6月前
|
存储 SQL 关系型数据库
二、MySQL高级分享2
二、MySQL高级分享2
云松雪
71 0
小小小林
|
存储 关系型数据库 MySQL
MySQL基础详讲（高级部分）
MySQL是一种关系型数据库管理系统（RDBMS），它是一种开源数据库软件，广泛用于Web应用程序的开发和数据存储
小小小林
88 3
海拥
|
6月前
|
关系型数据库 MySQL 数据库
深入理解MySQL：从基础到高级数据库管理
深入理解MySQL：从基础到高级数据库管理
海拥
171 0
数据库指南针
|
4月前
|
关系型数据库 MySQL 调度
MySQL高级功能与优化策略深度探索
MySQL高级功能与优化策略深度探索
数据库指南针
56 1
1100237741946300
|
5月前
|
SQL 存储 关系型数据库
精通MySQL：从基础到高级应用与最佳实践
第一章：MySQL基础入门 1.1 MySQL概述 介绍MySQL的历史、发展、优势以及应用领域
1100237741946300
608 1
啊猪是的读来过倒
|
5月前
|
SQL 关系型数据库 MySQL
深入探索MySQL SELECT查询：从基础到高级，解锁数据宝藏的密钥
深入探索MySQL SELECT查询：从基础到高级，解锁数据宝藏的密钥
啊猪是的读来过倒
106 0
1100237741946300
|
5月前
|
SQL 安全 关系型数据库
深入理解MySQL：从基础到高级应用及安全管理
第一章：MySQL基础入门 1.1 MySQL简介 简要介绍MySQL的历史、发展以及它在当前数据库领域的应用
1100237741946300
96 0
1100237741946300
|
5月前
|
SQL 存储 关系型数据库
精通MySQL：从基础到高级应用
第一章：MySQL入门 1.1 MySQL简介 介绍MySQL的历史、特点以及它作为关系型数据库管理系统（RDBMS）的优势
1100237741946300
455 0
1100237741946300
|
5月前
|
存储 关系型数据库 MySQL
精通MySQL：从基础到高级应用及性能优化
第一章：MySQL入门 1.1 MySQL简介 介绍MySQL的历史、版本和开源特性
1100237741946300
70 0

热门文章

最新文章

  • 1
    测试开启MySQL performance_schema后对性能的影响
  • 2
    RDS入门——Excel文件转存到RDS数据库实践
  • 3
    应用MySQL MGR
  • 4
    mysql中char与varchar的区别分析
  • 5
    RDS审计中心--数据库安全监控利器
  • 6
    干货分享 ｜ 揭秘下一代云数据库引擎MyBase for MySQL
  • 7
    MySQl复制---基于行的复制
  • 8
    将日志记录在是mysql中，实现loganalyzer
  • 9
    icinga2 check_mysql_health插件
  • 10
    mysql查询拼接
  • 1
    中间件MySQL性能瓶颈
    55
  • 2
    mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file
    267
  • 3
    MySQL上亿数据查询优化：实践与技巧
    285
  • 4
    如何理解Mysql的索引及他们的原理--------二叉查找树和平衡二叉树和B树和B+树
    49
  • 5
    MySQL：union all与union区别详解
    87
  • 6
    学习mysql中使用inner join，left join 等
    48
  • 7
    【最佳实践】MySQL数据库迁移到PXC集群
    91
  • 8
    MySQL PXC集群配置IPv6
    49
  • 9
    【实操记录】MySQL主从配置
    138
  • 10
    【实操记录】MySQL二进制安装包部署
    187

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL
  • One Box：解读事务与分析一体化数据库HybridDB for MySQL
  • 如何支撑HTAP场景-HybridDB for MySQL系统架构和技术演进

    • 相关实验场景

    更多
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 5分钟上手Flink MySQL连接器
  • 云端MySQL的安装及应用
  • 手动部署MySQL数据库（Alibaba Cloud Linux 2）
  • 手动部署MySQL数据库（CentOS 7）

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    阿里云OSS设置跨域访问