MySql必知必会笔记

于 2022-03-30 23:32:43 发布
阅读量1.1k 收藏 1
点赞数 1
文章标签： sql mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44542899/article/details/123860215
版权

《MySQL必知必会》学习笔记

此笔记内容大体来源于《MySQL必知必会》，少部分是个人补充，该笔记用于本人复习《MySQL必知必会》使用，不建议使用本笔记学习MySQL

可以看《MySQL必知必会》这本书进行学习MySQL，个人认为，这是一本不错的书，相比于网课部分，多了储存过程，游标等的详细讲解。

文章目录

一. 数据库概述

数据库（database） 保存有组织的数据的容器（通常是一个文 件或一组文件）

在这里插入图片描述

1.1 数据库格式

1.1.1行列格式

表中的数据是按行存储的，如图。

1.1.2 数据类型（datatype）

数据类型（datatype） 所容许的数据的类型。

每个表列都有相应的数据类型，它限制（或容许）该列中存储的数据

1.1.3 主键

每个列的唯一标识符，如现实中的身份证号

在如上图的表里，学号可以作为唯一标识符，使用主键可以区分每一行

  • 任意两行都不具有相同的主键值
  • 每个行都必须具有一个主键值(不允许为NULL)

应该坚持的 几个普遍认可的最好习惯为：

  • 不更新主键列中的值
  • 不重用主键列的值；
  • 不在主键列中使用可能会更改的值。（例如，如果使用一个 名字作为主键以标识某个供应商，当该供应商合并和更改其 名字时，必须更改这个主键。)
1.1.4 SQL简述

SQL（发音为字母S-Q-L或sequel）是结构化查询语言（Structured Query Language）的缩写，SQL是一种专门用来与数据库通信的语言。

SQL的语法大部分一样，但不同DBMS细致上不同。

二. MySQL概述

2.1 MySQL

2.1.1 什么是MySQL

MySQL是一种DBMS，即它是一种数据库软件。

MySQL相对于其他DBMS的优势：

  • 成本——MySQL是开放源代码的，一般可以免费使用
  • 性能——MySQL执行很快
  • 可信赖——某些非常重要和声望很高的公司、站点使用MySQL， 这些公司和站点都用MySQL来处理自己的重要数据
  • 简单——MySQL很容易安装和使用
2.1.2 MySQL的操作模式

DBMS可分为两类：一类为基于共享文件系统的DBMS，另一类为基 于客户机—服务器的DBMS。前者（包括诸如Microsoft Access和FileMaker） 用于桌面用途，通常不用于高端或更关键的应用。

MySQL属于 客户机-服务器的DBMS

在服务器上运行MySQL 服务，其他客户机访问相应端口，即可访问到数据库

2.1.3 MySQL版本

下面是最近版本中引入的主要更改

  • 4——InnoDB引擎，增加事务处理、并、改进全文本搜索等的支持

  • 4.1——对函数库、子查询、集成帮助等的重要增加

  • 5——存储过程、触发器、游标、 视图等。

  • 8 —— 账户与安全、优化器索引、通用表表达式 、窗口函数 、InnoDB增强 、JSON 增强

2.1.4 MySQL 登陆

为了指定用户登录名ben，应该使 用mysql -u ben。为了给出用户名、主机名、端口和口令， 应该使用mysql -u ben -p -h myserver -P 9999。

三. 使用MySQL

3.1 连接到MySQL

需要以下信息

  • 主机名（计算机名）——如果连接到本地MySQL服务器，为localhost
  • 端口（如果使用默认端口3306之外的端口）
  • 一个合法的用户名
  • 用户口令（如果需要）

3.2 数据库操作

3.2.1 进入数据库

数据库名字为databaseName

USE DATABASENAME;
3.2.2 展示指令的引用
-- 展示数据库
SHOW DATABASE;
-- 展示表
SHOW TABLES;
-- 展示表列从customers表(表的第一行信息)
SHOW COLUMNS FROM customers;
-- ============================================== ---
-- 用于显示广泛的服务器状态信息
SHOW STATUS;
-- 分别用来显示创建特定数据库或表的MySQL语句
SHOW CREATE DATABASE和SHOW CREATE TABLE
-- 用来显示授予用户（所有用户或特定用户）的安全权限
SHOW GRANTS;
-- 用来显示服务器错误或警告消息
SHOW ERRORS和SHOW WARNINGS

四. 检索数据

4.1 SELECT语句

4.1.1 检索单个列
-- prod_name 列名，products 表名
SELECT prod_name
from products;
4.1.2 检索多个列
-- * 全部列，products 表名
SELECT *
from products;
4.1.3 检索不同的行
-- 使用DISTINCT可以返回唯一值
SELECT DISTINCT vend_id
from products;
4.1.4 使用LIMIT限制结果

LIMIT可以用来实现分页功能

-- 从第五行开始，输出5条数据
SELECT prod_name
from products
limit 5 OFFSET 5;

五. 排序检索数据

5.1 排序数据

-- order by 可以为检索添加排序条件
SELECT prod_name
from products
order by prod_name;
5.1.1 按多个列排序
-- 先按照prod_price排序，price相同，按照prod_name排序
SELECT prod_id,prod_price,prod_name
from products
order by prod_price,prod_name;
5.1.2 指定排序方向
-- mysql默认使用升序排列(ASC)
-- 降序排列需要使用 DESC关键字
SELECT prod_id,prod_price,prod_name
from products
order by prod_price DESC;

六. 过滤数据

6.1 使用where子句

where等于判断条件，符合条件的语句返回，不符合被过滤

SELECT prod_price,prod_name
from products
WHERE prod_price = 2.50;
6.1.1 WHERE子句操作符

6.2 操作实例
-- 不等于
SELECT prod_price,prod_name
from products
WHERE prod_price != 2.50; -- WHERE prod_price <> 2.50 同意
-- BETWEEN
SELECT prod_price,prod_name
from products
WHERE prod_price BETWEEN 10 AND 20;

七. 数据过滤

7.1 组合WHERE子句

MySQL允许给出多个WHERE子句。这些子 句可以两种方式使用：以AND子句的方式或OR子句的方式使用。

7.1.1 AND
-- VEND_ID = 1003
-- PROD_PRICE <=10
-- 两个条件需要同时满足
SELECT prod_id,prod_price,prod_name
from products
where VEND_ID = 1003 AND prod_price <=10;
7.1.1 OR
-- VEND_ID = 1003
-- PROD_PRICE <=10
-- 两个条件满足一个
SELECT prod_id,prod_price,prod_name
from products
where VEND_ID = 1003 OR prod_price <=10;
7.1.3 计算次序

SQL（像多数语言一样）在处理OR操作符前，优先处理AND操 作符。

-- 先处理AND 再处理OR
SELECT prod_id,prod_price,prod_name
from products
where VEND_ID = 1002 OR VEND_ID = 1003 AND prod_price <=10;

实际使用中建议使用（）进行限定

任何时候使用具有AND和OR操作 符的WHERE子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序，即使它确实是你想要的东西也是如 此。使用圆括号没有什么坏处，它能消除歧义

7.2 IN操作符

-- 两种SELECT语句作用相同
SELECT prod_id,prod_price,prod_name
from products
where VEND_ID in(1002,1003);
---------------------------------------
SELECT prod_id,prod_price,prod_name
from products
where VEND_ID = 1002 OR VEND_ID = 1003;

八. 通配符

8.1 LIKE操作符

通配符（wildcard） 用来匹配值的一部分的特殊字符。

搜索模式（search pattern） 由字面值、通配符或两者组合构 成的搜索条件

8.1.1 百分号（%）通配符

%表示无论右多少字符，如’%123’ 表示了无论123之前有多少字符都能匹配，如’asd123’,‘a123’…等

SELECT prod_id,prod_name
from products
where prod_name LIKE 'jet%'
-- 匹配任意位置% %
SELECT prod_id,prod_name
from products
where prod_name LIKE '%anvil%'
8.1.2 下划线（_）通配符

下划线的用途与%一样，但下划 线只匹配单个字符而不是多个字符

SELECT prod_id,prod_name
from products
where prod_name LIKE '_ ton anvil'
-- 该SELECT 可以匹配 1 ton avnil 和 2 ton avnil 而不能匹配.5 ton avnil

8.2 使用通配符的技巧

  • 不要过度使用通配符。如果其他操作符能达到相同的目的，应该 使用其他操作符。
  • 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
  • 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据

九. 正则表达式

​ 正则表达式，又称规则表达式**。**（英语：Regular Expression，在代码中常简写为regex、regexp或RE），计算机科学的一个概念。正则表达式通常被用来检索、替换那些符合某个模式(规则)的文本。

9.2 使用MySQL正则表达式

MySQL 用WHERE子句对正则表达式提供了初步的支持，允许你指定正则表达式， 过滤SELECT检索出的数据。

9.2.1 基本字符匹配
-- 下面的语句检索列prod_name包含文本1000的所有行
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name

LIKE '1000'
REGEXP '1000'

LIKE匹配整个列。如果被匹配的文本在列值 中出现，LIKE将不会找到它，相应的行也不被返回（除非使用 通配符）。而REGEXP在列值内进行匹配，如果被匹配的文本在 列值中出现，REGEXP将会找到它，相应的行将被返回。这是一 个非常重要的差别

9.2.2 进行OR匹配
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name
9.2.3 匹配几个字符之一
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123]Ton' -- [1|2|3]Ton [1-3]Ton三者相等
ORDER BY prod_name
---------------------------
9.2.5 匹配特殊字符

使用***\\***进行转义

\\也用来引用元字符（具有特殊含义的字符），如表9-1所列

匹配\ 为了匹配反斜杠（\）字符本身，需要使用\\

9.2.6 匹配字符类

就是mysql的带的字符类,简化使用

在这里插入图片描述

9.2.7 匹配多个实例

在这里插入图片描述

SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
-- \\(  \\) 转义为括号
-- [0-9]匹配0-9
-- sticks? 匹配stick与sticks ?表示s有0-1个匹配
9.2.8 定位符

在这里插入图片描述

-- ^匹配串的开始。因此，^[0-9\\.]只在.或任意数字为串中第一个字符时才匹配它们。没有^，则还要多检索出4个别的行（那些中间有数字的行）。

十. 创建计算字段

10.1 计算字段

  • 如果想在一个字段中既显示公司名，又显示公司的地址，但这两 个信息一般包含在不同的表列中
  • 城市、州和邮政编码存储在不同的列中（应该这样），但邮件标签 打印程序却需要把它们作为一个恰当格式的字段检索出来

​ 存储在表中的数据都不是应用程序所需要的。 我们需要直接从数据库中检索出转换、计算或格式化过的数据；而不是检索出数据，然后再在客户机应用程序或报告程序中重新格式化

10.2 拼接字段

MySQL的不同之处 多数DBMS使用+或||来实现拼接， MySQL则使用Concat()函数来实现。当把SQL语句转换成 MySQL语句时一定要把这个区别铭记在心

SELECT Concat(vend_name,'(',vend_country,')')
FROM vendors
ORDER BY vend_name;
10.2.1 删除空格

Trim()函数 删除两边

RTrim() 删除右边

SELECT Concat(Trim(vend_name),'(',vend_country,')')
FROM vendors
ORDER BY vend_name;
10.2.2 使用别名
SELECT Concat(Trim(vend_name),'(',vend_country,')') AS vend_title
FROM vendors
ORDER BY vend_name;

​ SELECT语句本身与以前使用的相同，只不过这里的语句中计算 字段之后跟了文本AS vend_title。它指示SQL创建一个包含 指定计算的名为vend_title的计算字段。从输出中可以看到，结果与以 前的相同，但现在列名为vend_title，任何客户机应用都可以按名引用 这个列，就像它是一个实际的表列一样。

10.3 执行算术计算

输出中显示的expanded_price列为一个计算字段，此计算为quantity*item_price。客户机应用现在可以使用这个新计算列就像使用其他列一样

十一. 函数

与其他大多数计算机语言一样，SQL支持利用函数来处理数据。函数 一般是在数据上执行的，它给数据的转换和处理提供了方便。

为了代码的可移植，许多SQL程序员不赞成使用特殊实现的功 能。虽然这样做很有好处，但不总是利于应用程序的性能。如 果不使用这些函数，编写某些应用程序代码会很艰难。必须利 用其他方法来实现DBMS非常有效地完成的工作。 如果你决定使用函数，应该保证做好代码注释，以便以后你（或 其他人）能确切地知道所编写SQL代码的含义

11.2 使用函数

11.2.1 文本处理函数
  • Upper()函数

    转换为大写字符

表11-1列出了某些常用的文本处理函数

在这里插入图片描述

  • Soundex()函数

    进行发音比较 如Lee与Lie 会被判定为相等 如下例
    在这里插入图片描述

在这里插入图片描述

11.2.2 日期和时间处理函数

在这里插入图片描述

首先需要注意的是MySQL使用的日期格式。无论你什么时候指定个日期，不管是插入或更新表值还是用WHERE子句进行过滤，日期必须为 格式yyyy-mm-dd。因此，2005年9月1日，给出为2005-09-01。虽然其他的日期格式可能也行，但这是首选的日期格式，因为它排除了多义性（如， 04/05/06是2006年5月4日或2006年4月5日或2004年5月6日或……）。

如果要的是日期，请使用Date() 如果你想要的仅是日期， 则使用Date()是一个良好的习惯，即使你知道相应的列只包含日期也是如此。这样，如果由于某种原因表中以后有日期和时间值，你的SQL代码也不用改变。当然，也存在一个Time() 函数，在你只想要时间时应该使用它。

SELECT cust_id,order_num
FROM orders
WHERE Data(order_date) = '2005-09-01'
-- 避免直接使用order_date = '2005-09-01'
-- 因为order_date 有可能带有时分秒
11.2.3 数值处理函数

在主要DBMS的函数中，数值函数是最一致最统 一的函数。

在这里插入图片描述

十二. 汇总数据

为了获得汇总信息，MySQL给出了5个聚集函数

聚集函数（aggregate function） 运行在行组上，计算和返回单 个值的函数

在这里插入图片描述

使用DISTINCT 可使AVG() 聚集不同值，有相同值则只计算几个

SELECT AVG(DISTINCT prod_price) as avg_price
FROM products
where vend_id = 1003;

DISTINCT可用于指定列名的COUNT() 不能用于COUNT(*)

取别名 在指定别名以包含某个聚集函数的结果时，不应该使 用表中实际的列名。虽然这样做并非不合法，但使用唯一的名 字会使你的SQL更易于理解和使用（以及将来容易排除故障）

十三. 分组数据

13.1 数据分组

分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算

13.2 创建分组

ELECT vend_id,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

在具体使用GROUP BY子句前，需要知道一些重要的规定

  • GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套， 为数据分组提供更细致的控制
  • 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算
  • GROUP BY子句中列出的每个列都必须是检索列或有效的表达式 （但不能是聚集函数）。如果在SELECT中使用表达式，则必须在 GROUP BY子句中指定相同的表达式。不能使用别名。
  • 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子 句中给出
  • 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列 中有多行NULL值，它们将分为一组
  • GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

13.3 过滤分组

SELECT cust_id,COUNT(*) as orders
FROM orders
GROUP by cust_id
HAVING COUNT(*) >=2

HAVING支持所有WHERE操作符 在第6章和第7章中，我们学习 了WHERE子句的条件（包括通配符条件和带多个操作符的子 句）。所学过的有关WHERE的所有这些技术和选项都适用于 HAVING。它们的句法是相同的，只是关键字有差别

HAVING和WHERE的差别

HAVING和WHERE的差别 这里有另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组

13.4 分组和排序

虽然GROUP BY和ORDER BY经常完成相同的工作，但它们是非常不同的

在这里插入图片描述

不要忘记ORDER BY 一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY排序数据

SELECT order_num,SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP by order_num
HAVING ordertotal>=50
ORDER BY ordertotal;

13.5 SELECT子句顺序

(img-dNFzjbNM-1648653523709)(C:\Users\chaos\AppData\Roaming\Typora\typora-user-images\image-20220330150030102.png)]
在这里插入图片描述

十四. 使用子查询

版本要求 MySQL 4.1引入了对子查询的支持，所以要想使用本章描述的SQL，必须使用MySQL 4.1或更高级的版本

子查询（subquery），即嵌套在其他查询中的查询。
在这里插入图片描述

orders表存储顾客订单（但不是订单细节）。每个订单唯一地编号（order_num列）。订单用cust_id列（它关联到customer表的顾客唯一ID） 与相应的顾客关联。 287
在这里插入图片描述

在这里插入图片描述

假如需要列出订购物品TNT2的所有客户，应该怎样检索？下 面列出具体的步骤。

  • 1.检索包含物品TNT2的所有订单的编号。

    • 对于prod_id为TNT2的所有订单物品，它检索其order_num列。输出列出两个包含此物品的订单(orderitems表)

    • SELECT order_num
FROM oderitems
WHERE prod_id = 'TNT2'

  • 2.检索具有前一步骤列出的订单编号的所有客户的ID

  • 查询具有订单20005和20007的客户ID。利用第7章介绍的IN 子句，编写如下的SELECT语句

  • SELECT cust_id 
FROM orders
WHERE order_num IN(20005,20007)

  • 可以把1，2组合使用

  • 把步骤一的SELECT 语句和步骤二的语句组合

  • SELECT cust_id 
FROM orders
WHERE order_num IN(SELECT order_num
					FROM oderitems
					WHERE prod_id = 'TNT2')

  • 3.检索这些客户 ID的客户信息。

    • 检索两列的SQL语句为

    • SELECT cust_name ,cust_contact
from customers
where cust_di IN(10001,10004)

    • 可也以使用子查询将(10001,10004)中的数字替换为1，2组合的语句

格式化SQL 包含子查询的SELECT语句难以阅读和调试，特 别是它们较为复杂时更是如此。如上所示把子查询分解为多行 并且适当地进行缩进，能极大地简化子查询的使用

虽然子查询一般与IN操作符结合使用，但也可以用于测试等于（=）、 不等于（<>）等

14.3 作为计算字段使用子查询

假如需要显示customers 表中每个客户的订单总数。订单与相应的客户ID存储在orders表中

为了执行这个操作，遵循下面的步骤

  • (1) 从customers表中检索客户列表
  • (2) 对于检索出的每个客户，统计其在orders表中的订单数目。
SELECT cust_name,
	   cust_state,
	   (SELECT COUNT(*)
       	FROM orders
        WHERE orders.cust_id = customers.cust_id) AS orders
 FROM customers
 ORDER By cust_name;

这条 SELECT 语句对 customers 表中每个客户返回 3 列 ：cust_name、cust_state和orders。orders是一个计算字段， 它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一 次。在此例子中，该子查询执行了5次，因为检索出了5个客户。

相关子查询（correlated subquery） 涉及外部查询的子查询

十五. 联结表

15.1 联结

SQL最强大的功能之一就是能在数据检索查询的执行中联结（join） 表。联结是利用SQL的SELECT能执行的最重要的操作，很好地理解联结及其语法是学习SQL的一个极为重要的组成部分

15.1.1 关系表

建立两个表，一个存储供应商信息，另一个存储产品信息。vendors表包含所有供应商信息，每个供应商占一行，每个供应商具有唯一的标识。此标识称为主键（primary key）（在第1章中首次提到），可以是供应商ID或任何其他唯一值。

products表只存储产品信息，它除了存储供应商ID（vendors表的主 键）外不存储其他供应商信息。vendors表的主键又叫作products的外键， 它将vendors表与products表关联，利用供应商ID能从vendors表中找出 相应供应商的详细信息

外键（foreign key） 外键为某个表中的一列，它包含另一个表 的主键值，定义了两个表之间的关系

这样做的好处如下

  • 供应商信息不重复，从而不浪费时间和空间
  • 如果供应商信息变动，可以只更新vendors表中的单个记录，相关表中的数据不用改动
  • 由于数据无重复，显然数据是一致的，这使得处理数据更简单。

可伸缩性（scale） 能够适应不断增加的工作量而不失败。设 计良好的数据库或应用程序称之为可伸缩性好（scale well）

15.2 创建联结

SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id = products.vend_id
order by vend_name,prod_name;

如果不使用WHERE子句，则会产生笛卡尔积，最终的行数为count(a)+count(b)

15.2.2 内部联结

联结的7种方式

7

在这里插入图片描述

内连接查询出的数据是两张表的交集，即上图中红色所表示的部分。

SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

十六. 创建高级联结

16.1 使用表别名

别名除了用于列名和计算字段外，SQL还允许给表名起别名。这样做有两个主要理由：

  • 缩短SQL语句
  • 允许在单条SELECT语句中多次使用相同的表

16.2 使用不同类型的联结

16.2.1 自联结

假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。下面是解决此问题的一种方法：

在这里插入图片描述

现在来看使用联结的相同查询

在这里插入图片描述

16.2.2 自然联结

自然连接(Natural join)是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉

16.2.3 外部联结

左外连接
左外链接如图中红色部分的内容，即包含左边表的全部行（不管右边的表中是否存在与它匹配的行），和右边表中全部匹配的行。

img

右外链接
右外链接如图中红色部分的内容，即包含右边表的全部行（不管左边的表中是否存在与它匹配的行），和左边表中全部匹配行。

img

十七. 组合查询

使用UNION 将两个select语句连一起，返回一个结果集

十八. 全文本搜索

十九. 数据插入

19.2 插入完整的行

在这里插入图片描述

在这里插入图片描述

省略列 如果表的定义允许，则可以在INSERT操作中省略某 些列。省略的列必须满足以下某个条件。

 该列定义为允许NULL值（无值或空值）。

 在表定义中给出默认值。这表示如果不给出值，将使用默 认值。 如果对表中不允许NULL值且没有默认值的列不给出值，则 MySQL将产生一条错误消息，并且相应的行插入不成功

提高整体性能 数据库经常被多个客户访问，对处理什么请 求以及用什么次序处理进行管理是MySQL的任务。INSERT操 作可能很耗时（特别是有很多索引需要更新时），而且它可能 降低等待处理的SELECT语句的性能。 如果数据检索是最重要的（通常是这样），则你可以通过在 INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL 降低INSERT语句的优先级，如下所示： 顺便说一下，这也适用于下一章介绍的UPDATE和DELETE语句

二十. 更新和删除数据

20.1 更新数据

UPDATE语句非常容易使用，甚至可以说是太容易使用了。基本的 UPDATE语句由3部分组成，分别是

  • 要更新的表；
  • 列名和它们的新值；
  • 确定要更新行的过滤条件。
update customers
SET cust_email = 'elmer@fudd.com'
where cust_id = 10005;

20.2 删除数据

为了从一个表中删除（去掉）数据，使用DELETE语句。可以两种方 式使用DELETE

  • 从表中删除特定的行
  • 从表中删除所有行。

在这里插入图片描述

DELETE FROM customers
where cust_id = 10006;

二十一. 创建和操作表

21.3 删除表

DROP TABLE customers

21.4 重命名

RENAME TABLE customers to customers2

二十二. 使用视图

视图等于一个虚构的表，方便查询使用

create view productcustomers AS 
SELECT cust_name, cust_contact,prod_id
FROM customers,orders,orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;

这条语句创建一个名为productcustomers的视图，它联结三个 表，以返回已订购了任意产品的所有客户的列表。如果执行 SELECT * FROM productcustomers，将列出订购了任意产品的客户。

二十三. 储存过程(Procedure)

23.1 什么是储存过程

存储过程简单来说，就是为以后的使用而保存 的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用 不仅限于批处理

23.2 使用储存过程

MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句 为CALL。CALL接受存储过程的名字以及需要传递给它的任意参数。请看 以下例子：

CALL productionpricing(@pricelow,
                       @pricehigh,
                       @priceavg);

其中，执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格

23.3 创建存储过程

CREATE PROCEDURE productionpricing()
BEGIN
	SELECT avg(prod_price) as priceaverage
	FROM products;
END;

23.4删除存储过程

DROP procedure productionpricing;

23.5 使用参数

MySQL支持IN（传递给存储过程）、OUT（从存储过程传出，如这里所用）和INOUT（对存储过程传入和传出）类型的参数。

在这里插入图片描述

-- 在储存过程中，使用INTO将SELECT查询结果放在变量里
-- 在参数名中，使用OUT将SELECT查询结果传出

因为创建时使用OUT传参了，所以调用时应该给出参数

CALL productpricing(@pricelow,
                    @pricehigh,
                    @priceavg);

调用CALL后，可以使用

SELECT @pricelow

进行查询相应的参数

23.6 更智能的储存过程

在这里插入图片描述

Create procedure ordertotal(
	in onumber int,
    in taxable boolean,
    OUT ototal DECIMAL(8,2)
)COMMENT'Obtain order total, optionally adding tax'
-- 获取订单总额，可选择加税 
BEGIN

 -- Declare variable for total
 -- 局部变量total 用于改变OTOTAL的值
 DECLARE total DECIMAL(8,2);
 -- Declare tax percentage
 DECLARE taxrate INT DEFAULT 6;
 
 -- Get the order total
 SELECT Sum(item_price*quantity)
 FROM oderitems
 WHERE order_num = onumber
 INTO total;
 
 
 -- IS this taxable?
 IF taxable THEN
 	-- YES
 	SELECT total+(total/100*taxrate) INTO total
 END IF;
 	SELECT total INTO ototal;
 END;

COMMENT关键字 本例子中的存储过程在CREATE PROCEDURE语 句中包含了一个COMMENT值。它不是必需的，但如果给出，将 在SHOW PROCEDURE STATUS的结果中显示。

-- 显示用来创建一个存储过程的CREATE语句
SHOW CREATE PROCEDURE语句

二十四. 使用游标(cursor)

在存储了游 标之后，应用程序可以根据需要滚动或浏览其中的数据。 游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对 数据进行浏览或做出更改

只能用于存储过程 不像多数DBMS，MySQL游标只能用于 存储过程（和函数）

24.2 使用游标

使用游标涉及几个明确的步骤

  • 在能够使用游标前，必须声明（定义）它。这个过程实际上没有 检索数据，它只是定义要使用的SELECT语句。
  • 一旦声明后，必须打开游标以供使用。这个过程用前面定义的 SELECT语句把数据实际检索出来。
  • 对于填有数据的游标，根据需要取出（检索）各行
  • 在结束游标使用时，必须关闭游标
24.2.1 创建游标(cursor)

游标用DECLARE语句创建（参见第23章）。

DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE和其他子句。例如，下面的语句定义了名为ordernumbers的游标，使用了可以检索所有订单的SELECT语句

CREATE PROCEDURE processorders()
BEGIN 
	DECLARE ordernumbers cursor
	FOR
	SELECT order_num FROM orders;
END;

存储过程处理完成后，游标就消失（因为它局限于存储过程）。

24.2.2 打开和关闭游标
open ordernumbers;
close ordernumbers;

隐含关闭 如果你不明确关闭游标，MySQL将会在到达END语句时自动关闭它

24.2.3 使用游标数据(fetch)

fetch 拿来

在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。 FETCH指定检索什么数据（所需的列），检索出来的数据存储在什么地方。 它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行（不 重复读取同一行）。

CREATE PROCEDURE processorders()
BEGIN 
	Declare o INT;

	DECLARE ordernumbers cursor
	FOR
	SELECT order_num FROM orders;
	
	open ordernumbers;
	
	Fetch ordernumbers into o;
	
	close ordernumbers;
END;

其中FETCH用来检索当前行的order_num列（将自动从第一行开 始）到一个名为o的局部声明的变量中。对检索出的数据不做 任何处理

CREATE PROCEDURE processorders()
BEGIN 
	Declare done boolean default 0;
	Declare o INT;

	DECLARE ordernumbers cursor
	FOR
	SELECT order_num FROM orders;
	
	-- declare continue handler
	Declare continue handler for sqlstate '02000' SET done =1;
-- 这条语句定义了一个CONTINUE HANDLER，它是在条件出现时被执行的代码。这里，它指出当SQLSTATE '02000'出现时，SET done=1。SQLSTATE '02000'是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件

	open ordernumbers;
	
	-- Loop through all rows
	Repeat 
		-- Get order number
		Fetch ordernumbers into o;
	until done end repeat;
	
	close ordernumbers;
END;

在这里插入图片描述

CREATE PROCEDURE processorders()
BEGIN 
	Declare done boolean default 0;
	Declare o INT;
	Declare t DECIMAL(8,2);

	DECLARE ordernumbers cursor
	FOR
	SELECT order_num FROM orders;
	
	-- declare continue handler
	Declare continue handler for sqlstate '02000' SET done =1;
	
	create table if NOT exists ordertotals
	(order num int,total DECIMAL(8,2));
	
	open ordernumbers;
	
	-- Loop through all rows
	Repeat 
		-- Get order number
		Fetch ordernumbers into o;
		CALL ordertotal(0,1,t);
		
		INSERT INTO ordertotals(order_num,total)
		values(0,t);
		
	until done end repeat;
	
	close ordernumbers;
END;

在这个例子中，我们增加了另一个名为t的变量（存储每个订 单的合计）。此存储过程还在运行中创建了一个新表（如果它不存在的话），名为ordertotals。这个表将保存存储过程生成的结果。FETCH像以前一样取每个order_num，然后用CALL执行另一个存储过程（我们在前一章中创建）来计算每个订单的带税的合计（结果存储到t）。最后， 用INSERT保存每个订单的订单号和合计

此存储过程不返回数据，但它能够创建和填充另一个表，可以用一 条简单的SELECT语句查看该表：

SELECT * from ordertotals

二十五. 使用触发器(trigger)

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）

  • DELETE
  • INSERT
  • UPDATE

25.2 创建触发器(trigger)

在创建触发器时，需要给出4条信息

  • 唯一的触发器名；
  • 触发器关联的表
  • 触发器应该响应的活动（DELETE、INSERT或UPDATE）；
  • 触发器何时执行（处理之前或之后）

在这里插入图片描述

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added'

-- CREATE TRIGGER用来创建名为newproduct的新触发器
-- AFTER INSERT，所以此触发器将在INSERT语句成功执行后执行
-- FOR EACH ROW，因此代码对每个插入行执行
-- 文本Product added将对每个插入的行显示一次

仅支持表 只有表才支持触发器，视图不支持（临时表也不支持）

在这里插入图片描述

25.3 删除触发器

DROP trigger newproduct;
-- 触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建

25.4 使用触发器

25.4.1 INSERT触发器

INSERT触发器在INSERT语句执行之前或之后执行。需要知道以下几点

  • 在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行
  • 在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）
  • 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。
CREATE trigger neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num

new.order_num 表示从新的表单里取出新的订单号，而不是返回旧的订单号

25.4.2 DELETE触发器
  • DELETE触发器在DELETE语句执行之前或之后执行。需要知道以下两点
  • OLD中的值全都是只读的，不能更新。
CREATE trigger deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
	INSERT INTO archive_orders(order_num,order_date,cust_id)
	Value(OLD.order_num,OLD.order_date,OLD.cust_id);
END;

在任意订单被删除前将执行此触发器。它使用一条INSERT语句将OLD中的值（要被删除的订单）保存到一个名为archive_ orders的存档表。

在这里插入图片描述

25.4.3 UPDATE触发器

UPDATE触发器在UPDATE语句执行之前或之后执行。需要知道以下几 点

  •  在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问 以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新 更新的值

  • 在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；

  • OLD中的值全都是只读的，不能更新

-- 替换大小写
CREATE TRIGGERT updatevendor before update on vendors
FOR each row set new.vend_state = Upper(NEW.ven_state);

二十六. 管理事务处理(transaction)

26.1 事务管理

InnoDB才支持事务管理

事务处理是一种 机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完 整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们 或者作为整体执行，或者完全不执行（除非明确指示）。如果没有错误发 生，整组语句提交给（写到）数据库表。如果发生错误，则进行回退（撤 销）以恢复数据库到某个已知且安全的状态

下面是关于 事务处理需要知道的几个术语：

  • 事务（transaction）指一组SQL语句；
  • 回退（rollback）指撤销指定SQL语句的过程；
  • 提交（commit）指将未存储的SQL语句结果写入数据库表；
  • 保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）。

26.2 控制事务处理

START TRANSACTION
26.2.1 使用ROLLBACK

ROLLBACK语句可以回退 START TRANSACTION之后的所有语句。

SELECT * from products
------- ROLLBACK点位
START TRANSACTION
....
....
ROLLBACK;-- 返回值ROLLBACK点位
26.2.2 使用COMMIT
-- 使用COMMIT可以使事务中的语句执行成功

在这里插入图片描述

26.2.3 使用保留点
SAVEPOINT deletel;
ROLLBACK to deletel;

在这里插入图片描述

在这里插入图片描述

26.2.4 更改默认的提交行为
SET autocommit = 0;

二十七. 全球化和本地化

27.1 字符集和校对顺序

在讨论多种语言和字符集时，将会遇到以下重要术语

  • 字符集为字母和符号的集合；
  • 编码为某个字符集成员的内部表示
  • 校对为规定字符如何比较的指令

27.2 使用字符集和校对顺序

-- 查看完整字符集
SHOW CHARACTER SET;
-- 查看校对
SHOW COLLATION;
-- CREATE
create table tablename()
DEFAULT character set utf8
 		collate utf8_general_ci;

在这里插入图片描述

二十八. 安全管理(grant)

  • 多数用户只需要对表进行读和写，但少数用户甚至需要能创建和删除表；

  • 某些用户需要读表，但可能不需要更新表

  • 你可能想允许用户添加数据，但不允许他们删除数据；

  • 某些用户（管理员）可能需要处理用户账号的权限，但多数用户不需要

  • 你可能想让用户通过存储过程访问数据，但不允许他们直接访问数据

  • 你可能想根据用户登录的地点限制对某些功能的访问。

在这里插入图片描述

28.2 管理用户

USE mysql;
SELECT user from user;
28.2.1 创建用户账号
CREATE USER ben identified by 'password'

在这里插入图片描述

在这里插入图片描述

28.2.2 删除用户账号
DROP user bforta;
28.2.3 设置访问权限
-- 查看用户权限
SHOW GRANTS FOR

GRANT用法

  • 要授予的权限
  • 被授予访问权限的数据库或表；
  • 用户名。
grant select on crashcourse.* to bforta;
-- 此GRANT允许用户在crashcourse.*（crashcourse数据库的所有表）上使用SELECT。通过只授予SELECT访问权限，用户bforta对crashcourse数据库中的所有数据具有只读访问权限
SHOW grant for bforta;


-- REVOKE,用它来撤销特定的权限
REVOKE SELECT ON crashcourse.* from bforta;

在这里插入图片描述
在这里插入图片描述

28.2.4 更改口令
set password for bforta = password('n3w p@$$w0rd');

二十九. 数据库维护

29.1 备份数据

由于这些文件总是处于打开和使用状态，普通的文件副本备份不 一定总是有效,下面列出这个问题的可能解决方案。

  • 使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行，以便能正确地备份转储文件

  • 可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据

  • 可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所 有数据到某个外部文件。这两条语句都接受将要创建的系统文件 名，此系统文件必须不存在，否则会出错。数据可以用RESTORE TABLE来复原

在这里插入图片描述

29.2 进行数据库维护

MySQL提供了一系列的语句，可以（应该）用来保证数据库正确和正常运行

  • ANALYZE TABLE用来检查表键是否正确。
  • CHECK TABLE 用来针对许多问题对表进行检查。

29.3 诊断启动问题

 --help显示帮助——一个选项列表
 --safe-mode装载减去某些最佳配置的服务器；
 --verbose显示全文本消息（为获得更详细的帮助消息与--help
联合使用）；
 --version显示版本信息然后退出。

29.4 查看日志文件

MySQL维护管理员依赖的一系列日志文件。主要的日志文件有以下几种

  • 错误日志。它包含启动和关闭问题以及任意关键错误的细节。此日志通常名为hostname.err，位于data目录中。此日志名可用 --log-error命令行选项更改。
  • 查询日志。它记录所有MySQL活动，在诊断问题时非常有用。此日志文件可能会很快地变得非常大，因此不应该长期使用它。此日志通常名为hostname.log，位于data目录中。此名字可以用 --log命令行选项更改
  • 二进制日志。它记录更新过数据（或者可能更新过数据）的所有语句。此日志通常名为hostname-bin，位于data目录内。此名字可以用–log-bin命令行选项更改。注意，这个日志文件是MySQL5中添加的，以前的MySQL版本中使用的是更新日志
  • 缓慢查询日志。顾名思义，此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。此日志通常名为 hostname-slow.log ，位于 data 目录中。此名字可以用 --log-slow-queries命令行选项更改。

FLUISH LOGS刷新和重新开始所有日志文件。

`

[外链图片转存中…(img-jtr29kn9-1648653523721)]

[外链图片转存中…(img-TYF5d450-1648653523722)]

28.2.4 更改口令
set password for bforta = password('n3w p@$$w0rd');

二十九. 数据库维护

29.1 备份数据

由于这些文件总是处于打开和使用状态，普通的文件副本备份不 一定总是有效,下面列出这个问题的可能解决方案。

  • 使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行，以便能正确地备份转储文件

  • 可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据

  • 可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所 有数据到某个外部文件。这两条语句都接受将要创建的系统文件 名，此系统文件必须不存在，否则会出错。数据可以用RESTORE TABLE来复原

[外链图片转存中…(img-brA8npIs-1648653523722)]

29.2 进行数据库维护

MySQL提供了一系列的语句，可以（应该）用来保证数据库正确和正常运行

  • ANALYZE TABLE用来检查表键是否正确。
  • CHECK TABLE 用来针对许多问题对表进行检查。

29.3 诊断启动问题

 --help显示帮助——一个选项列表
 --safe-mode装载减去某些最佳配置的服务器；
 --verbose显示全文本消息（为获得更详细的帮助消息与--help
联合使用）；
 --version显示版本信息然后退出。

29.4 查看日志文件

MySQL维护管理员依赖的一系列日志文件。主要的日志文件有以下几种

  • 错误日志。它包含启动和关闭问题以及任意关键错误的细节。此日志通常名为hostname.err，位于data目录中。此日志名可用 --log-error命令行选项更改。
  • 查询日志。它记录所有MySQL活动，在诊断问题时非常有用。此日志文件可能会很快地变得非常大，因此不应该长期使用它。此日志通常名为hostname.log，位于data目录中。此名字可以用 --log命令行选项更改
  • 二进制日志。它记录更新过数据（或者可能更新过数据）的所有语句。此日志通常名为hostname-bin，位于data目录内。此名字可以用–log-bin命令行选项更改。注意，这个日志文件是MySQL5中添加的，以前的MySQL版本中使用的是更新日志
  • 缓慢查询日志。顾名思义，此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。此日志通常名为 hostname-slow.log ，位于 data 目录中。此名字可以用 --log-slow-queries命令行选项更改。

FLUISH LOGS刷新和重新开始所有日志文件。

朝诗
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值