《MySQL 必知必会》学习笔记

SQL必知必会

1 了解SQL

1.1 数据库基础

1.1.1 是什么是数据库

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

DBMS（Database Manage System）：数据库软件，我们通过DBMS来对数据库进行操作

1.1.2 表

在你将资料放入自己的文件柜时，并不是随便将它们扔进某个抽屉就完事了，而是在文件柜中创建文件，然后将相关的资料放入特定的文件中。在数据库领域中，这种文件称为表。表是一种结构化的文件，可用来存储某种特定类型的数据。表可以保存顾客清单、产品目录，或者其他信息清单。

表（table）：某种特定类型数据的结构化清单。

存储在表中的数据是同一种类型的数据或一个清单，否则在后续的检索与访问中会产生巨大的麻烦。数据库中的每个表都有一个名字，用来标识自己。此名字是唯一的，这表示数据库中没有同名的表。

模式（schema）： 关于数据库和表的布局及特性的信息。

表具有一些特性，这些特性定义了数据在表中如何存储，如可以存储什么样的数据，数据如何分解，各部分信息如何命名，等等。描述表的这组信息就是所谓的模式，模式可以用来描述数据库中特定的表以及整个数据库（和其中表的关系）。

1.1.3 列和数据类型

列（column）： 表中的一个字段。

所有表都是由一个或多个列组成的。列中存储着表中某部分的信息。

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

每个表列都有相应的数据类型，它限制（或容许）该列中存储的数据。数据库中每个列都有相应的数据类型。数据类型定义列可以存储的数据种类。例如，如果列中存储的为数字（或许是订单中的物品数），则相应的数据类型应该为数值类型。如果列中存储的是日期、文本、注释、金额等，则应该用恰当的数据类型规定出来。

1.1.4 行

行（row）： 表中的一个记录。

表中的数据是按行存储的，所保存的每个记录存储在自己的行内。如果将表想象为网格，网格中垂直的列为表列，水平行为表行。

1.1.5 主键

主键（primary key）：一列（或一组列），其值能够唯一区分表中每个行。

表中每一行都应该有可以唯一标识自己的一列（或一组列）。一个顾客表可以使用顾客编号列，而订单表可以使用订单ID，雇员表可以使用雇员ID或雇员社会保险号。

作为主键的条件：

任意两行都不具有相同的主键值；
每个行都必须具有一个主键值（主键列不允许NULL值）。
不更新主键列中的值；
不重用主键列的值；
不在主键列中使用可能会更改的值。

1.2 什么是SQL

SQL（Structured QueryLanguage），它是一种专门用来与数据库通信的语言

SQL的优点：

SQL不是某个特定数据库供应商专有的语言。几乎所有重要的DBMS都支持SQL，所以，学习此语言使你几乎能与所有数据库打交道。
SQL简单易学。
QL尽管看上去很简单，但它实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。

地方

2 MySQL简介

2.1 是什么是MySQL

MySQL是一种DBMS，也就是一种数据库软件。

2.1.1 客户机—服务器软件

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

MySQL、Oracle以及Microsoft SQL Server等数据库是基于客户机—服务器的数据库。客户机—服务器应用分为两个不同的部分。服务器部分是负责所有数据访问和处理的一个软件。这个软件运行在称为数据库服务器的计算机上。

与数据文件打交道的只有服务器软件。关于数据、数据添加、删除和数据更新的所有请求都由服务器软件完成。这些请求或更改来自运行客户机软件的计算机。

服务器软件为MySQL DBMS。你可以在本地安装的副本上运行，也可以连接到运行在你具有访问权的远程服务器上的一个副本。
客户机可以是MySQL提供的工具、脚本语言（如Perl）、Web应用开发语言（如ASP、ColdFusion、JSP和PHP）、程序设计语言（如C、C++、Java）等。

2.1.2 MySQL版本

2.2 MySQL工具

MySQL有三大工具

2.2.1 mysql命令行实用程序

mysql.exe，配合参数使用，使用--help参数获取帮助、

2.2.2 MySQL Administrator

2.2.3 MySQL Query Browser

可以用来执行保存的脚本。

3 使用MySQL

3.1 连接

一般通过此命令连接mysql，图形化界面除外。

mysql -uroot -p

3.2 选择数据库

使用use关键字

use name_of_database;

mysql中，必须先选择数据库之后才能对其进行操作

3.3 了解数据库和表

使用关键字show可以查看软件内内所有数据库

show databases;

同样，使用show也可以查看数据库内的所有表（前提时已经选择了一个数据库）

show tables;

还可以用show或者describe来显示来显示表的列信息

show columns from customers;	#	查看customers表的列信息
describe customers;	#效果同上

3.5 小结

如图所示，sql_test是新建的一个是数据库，其中的customers, orderitems...等都是表，

再看customer表，其中的cust_id, cust_name...都是列，每一列都有其特定的数据类型，而每一行则是一组用户的全部数据，这其中，cust_id可以作为主键，因为它会是唯一区别用户的标识

运行sql脚本

登录mysql，用use选择数据库，使用source执行sql脚本。

注意：脚本路径不能存在中文。

4 检索数据

4.1 SSELECT语句

它的用途是从一个或多个表中检索信息，必须至少给出两条信息——想选择什么，以及从什么地方选择。

4.2 检索单个列

SELECT prod_name FROM products;	#从products表中检索prod_name列

4.3 检索多个列

SELECT prod_id, prod_name, prod_price FROM products;	#从products表中检索prod_name列

4.4 检索所有列

SELECT * FROM products;	#使用正则表达式达到目的

4.5 检索不同的行

使用DISTINCT关键字，如果使用DISTINCT关键字，它必须直接放在列名的前面。

SELECT DISTINCT vend_id FROM products;

4.6 限制结果

使用LIMIT关键字

SELECT prod_name FROM products
LIMIT [a], b;	#a是可选参数，取值范围是[0,n]，0代表从第1行开始，b表示后续要检索的长度

#从第5行开始，显示4条数据
SELECT prod_name FROM products
LIMIT 4, 4;

#用offset代替','
SELECT prod_name FROM products
LIMIT 4 offset 4;

4.7 使用完全限定的表名

SELECT products.prod_name FROM products;

SELECT products.prod_name FROM sql_test.products;

5 排序检索数据

主要是对ORDER BY语句的使用

5.1 排序数据

子句（clause）:SQL语句由子句构成，有些子句是必需的，而有的是可选的。一个子句通常由一个关键字和所提供的数据组成。子句的例子有SELECT语句的FROM子句。

SELECT prod_name
FROM products
ORDER BY prod_name;

结果如下：

5.2 按多个列排序

经常需要按不止一个列进行数据排序。例如，如果要显示雇员清单，可能希望按姓和名排序（首先按姓排序，然后在每个姓中再按名排序）。如果多个雇员具有相同的姓，这样做很有用。

SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price, prod_name;

5.3 指定排序方向

ORDER BY默认按照A-Z升序排列，可以收i用DESC关键字使其降序排列。

#仅对名称降序排列
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price, prod_name DESC;

#仅对价格降序排列
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC, prod_name;

#对价格和名称都降序排列
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC, prod_name DESC;

同时使用ORDER和LIMIT可以查询指定大小的数据，这两个关键字的次序是唯一的，否则会产生语法错误

#查看最贵的商品
SELECT prod_name, prod_price
FROM products
ORDER BY prod_price DESC 
LIMIT 1;

6 过滤数据

6.1 使用WHERE子句

#以下语句将会显示价格为2.5的产品的名称和价格
SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.50;

WHERE语句的位置：在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后（先过滤，再排序）

6.2 WHERE子句操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定的两个值之间

6.2.1 检测单个值

单个地应用上表中的符号

6.2.2 不匹配检查

使用不等于号

6.2.3 范围值检查

使用BETWEEN操作符

#使用BETWEEN操作符需要两个操作数，并且用AND间隔
SELECT prod_name, prod_price
FROM products
WHERE pord_price BETWEEN 5 AND 10；

6.2.4 空值检查

WHERE * IS NULL

SELECT prod_name, prod_price
FROM products
WHERE pord_price IS NULL；

注意：NULL具有特殊含义，在匹配过滤或不匹配过滤时不返回它们

7 数据过滤

7.1 组合WHERE子句

将WHERE与AND和OR搭配使用

7.1.1 AND操作符

#从products中显示ven_id为1003 且 价格不高于10的产品
select prod_id, prod_price, prod_name
FROM
products
WHERE vend_id = 1003 and prod_price <= 10;

7.1.2 OR操作符

#从products中显示ven_id为1002 或者 1003的产品
SSELECT prod_id, prod_price, prod_name
FROM
products
WHERE vend_id = 1002 or vend_id = 1003;

7.1.3 计算次序

AND的优先级高于OR，同时使用时注意括号的使用。

7.2 IN操作符

IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN取合法值的由逗号分隔的清单，全都括在圆括号中。

#显示产品中vend_id属于【1002， 2003】的产品，并且按照价格降序排列
SSELECT prod_name, prod_price
FROM products
WHERE vend_id IN (1002, 1003)
order by prod_price DESC;

7.3 NOT操作符

WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后所跟的任何条件。

#显示产品中vend_id不属于【1002， 2003】的产品，并且按照价格降序排列
SSELECT prod_name, prod_price
FROM products
WHERE vend_id NOT IN (1002, 1003)	#效果同使用BETWEEN
order by prod_price DESC;

8 用通配符进行过滤

8.1 LIKE操作符

为在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示MySQL，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

8.1.1 百分号（%）通配符

最常使用的通配符是百分号（%）。在搜索串中，%表示任何字符出现任意次数。

SELECT prod_name FROM products WHERE prod_name LIKE 'jet%';

SELECT prod_name FROM products WHERE prod_name LIKE '%anvil%';

重要的是要注意到，除了一个或多个字符外，%还能匹配0个字符。%代表搜索模式中给定位置的0个、1个或多个字符。但是，它不能匹配NULL。

8.1.2 下划线（_）通配符

另一个有用的通配符是下划线（_）。下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符。

#不能匹配'.5 ton anvil'
SELECT prod_name FROM products WHERE prod_name LIKE '_ ton anvil%';

#能匹配'.5 ton anvil'
SELECT prod_name FROM products WHERE prod_name LIKE '% ton anvil%';

8.2 使用通配符的技巧

使用通配符的代价：通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长

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

9 用正则表达式进行搜索

9.1 正则表达式介绍

9.2 使用MySQL正则表达式

REGEXP关键字告诉WHERE后面使用的是正则表达式

9.2.1 基本字符匹配

 select prod_name from products where prod_name REGEXP '1000' order by prod_name;

like与regexp，前者匹配整个列值，后者在列值内进行匹配；

#只匹配1000开头的列值
like '1000'

#匹配列值中含有1000的数据
regexp '1000'

# 'anvil 1000'	前者不能匹配而后者能够匹配

大小写区分：正则表达式默认不区分大小写，要想区分大小写，可以在regexp后跟上binary关键字

点（.）匹配：匹配一个任意字符。

9.2.2 进行OR匹配

 #它表示匹配'1000'和'2000'其中之一
 select prod_name from products where prod_name REGEXP '1000|2000' order by prod_name;

9.2.3 匹配几个字符之一

[ ]元字符

#匹配的字符串中含有'1/2/3 ton'
select prod_name from products where prod_name REGEXP '[123] Ton' order by prod_name;

#匹配除开1/2/3以外的所有数据
select prod_name from products where prod_name REGEXP '[^123]' order by prod_name;

9.2.4 匹配范围

#匹配含有1/2/3/4/5 ton的数据
select prod_name from products where prod_name REGEXP '[1-5] Ton' order by prod_name;

9.2.5 匹配特殊字符

要想匹配元字符，必须使用\\转义

#匹配带有'.'的数据
select vend_name from vendors where vend_name regexp '\\.' order by vend_name;

多数正则表达式实现使用单个反斜杠转义特殊字符，以便能使用这些字符本身。但MySQL要求两个反斜（MySQL自己解释一个，正则表达式库解释另一个）。

9.2.6 匹配字符类

使用预定义的字符集，称为字符类：

9.2.7 匹配多个实例

正则表达式重复元字符：

元字符	说明
*	0个或多个匹配
+	1个或多个匹配（等于{1,}）
?	0个或1个匹配（等于{0,1}）
	指定数目的匹配
	不少于指定数目的匹配
	匹配数目的范围（m不超过255）

select prod_name 
from products 
where prod_name regexp '\\([0-9] sticks?\\)' 
order by prod_name;

搜索结果如下：

#匹配连续的四个数字
select prod_name 
from products 
where prod_name regexp '[0-9]{4}' 
order by prod_name;

9.2.8 定位符

元字符	说明
^	文本的开始
$	文本的结束
[[:<:]]	词的开始
[[:>:]]	词的结束

#搜索以数字或者'.'开头的数据
select prod_name 
from products 
where prod_name regexp '^[0-9|\\.]' 
order by prod_name;

^的双重用途：^有两种用法。在集合中（用[和]定义），用它来否定该集合，否则，用来指串的开始处。

简单的正则表达式测试：可以在MySQL数据库内使用select 'hello' regexp '^[a-zA-Z]';的语句来进行简单的正则表达式测试，匹配返回1，否则返回0。

10 创建计算字段

10.1 计算字段

比如所选商品地总价，所选商品供应商所在的城市、邮编等这一些连续的信息，这些信息并不会直接存储在数据库中，而是需要我们“计算”得到。

字段（field）：基本上与列（column）的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常用在计算字段的连接上。

10.2 拼接字段

vendors表包含供应商名和位置信息。假如要生成一个供应商报表，需要在供应商的名字中按照name(location)这样的格式列出供应商的位置。

拼接（concatenate）：将值联结到一起构成单个值。

Concat()函数：

#Concta()函数将所包含的字段连接起来
select concat(vend_name, '(', vend_country, ')')
from vendors
order by vend_name;

#RTrim(), LTrim(), Trim()分别能够去掉数据左/又/两侧的空格

使用别名

#使用别名之后，任何客户机都可以按名引用该列
select concat(rtrim(vend_name), '(', rtrim(vend_country), ')') as
vend_title
from vendors
order by vend_name;

10.3 执行算术计算

#最后显示的字段是附加计算得到的
select prod_id, quantity, item_price, 
	quantity*item_price
	as expanded_price
from orderitems
where order_num = 20005;

11 使用数据处理函数

11.1 函数

函数的可移植性不强

11.2 使用函数

大多数SQL实现支持以下类型的函数。

用于处理文本串
用于在数值数据上进行算术操作
用于处理日期和时间值并从这些值中提取特定成分
返回DBMS正使用的特殊信息

11.2.1 文本处理函数

UPPER()

#将vend_name大写输出
select vend_name, upper(vend_name) as uper_vend_name
from vendors
order by vend_name;

#Lower() 将串转换为小写

SOUNDEX()

#SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法
select cust_name, cust_contact
from customers
where soundex(cust_contact) = soundex('Y. Lie');	#注意有两个soundex()

11.2.2 日期和时间处理函数

日期和时间采用相应的数据类型和特殊的格式存储，以便能快速和有效地排序或过滤，并且节省物理存储空间。

MySQL使用的日期格式：无论你什么时候指定一个日期，不管是插入或更新表值还是用WHERE子句进行过滤，日期必须为格式”yyyy-mm-dd“，因此，2005年9月1日，给出为2005-09-01

select cust_id, order_num
from orders
where order_date = '2005-09-01';

#但是，若数据存储的order_date 值为2005-09-01 11:30:05，则WHERE order_date = '2005-09-01'失败

#Date()函数。Date(order_date)指示MySQL仅提取列的日期部分
select cust_id, order_num
from orders
where date(order_date) = '2005-09-01';

#Time()可以返回其后的具体时间

#返回一个日期范围的数据
select cust_id, order_num
from orders
where year(order_date) = 2005 and month(order_date) = 9;

11.2.3 数值处理函数

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

函数	说明
Abs()	返回一个数的绝对值
Cos()	返回一个角度的余弦
Exp()	返回一个数的指数值
Mod()	返回除操作的余数
Pi()	返回圆周率
Rand()	返回一个随机数
Sin()	返回一个角度的正弦
Sqrt()	返回一个数的平方根
Tan()	返回一个角度的正切

12 汇总数据

12.1 聚集函数

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

12.1.1 AVG函数

AVG()通过对表中行数计数并计算特定列值之和，求得该列的平均值。AVG()可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。

#显示产品的平均价格
select avg(prod_price) as avg_price from products;

#用where限定求平均范围
select avg(prod_price) as avg_price from products
where vend_id = 1003;

#求多个列的平均值必须使用多个avg()函数

12.1.2 COUNT函数

COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目。

使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

select count(*) from customers;	#对customers表的行进行计数
select count(cust_id) from customers;	#对有cust_id的列的行进行计数
select count(cust_email) from customers;	#对cust_email的行进行计数

12.1.3 MAX函数

select max(prod_price) from products;	#return the max price in all prices

MAX()处理文本数据时，仅返回最后一行

12.1.3 MIN函数

与MAX()函数相反。

12.1.4 SUM函数

SUM()用来返回指定列值的和（总计）。

#返回order_num为20005的订单总数
select sum(quantity) as items_ordered
from orderitems
where order_num = 20005;

#返回order_num为20005的订单总价
select sum(quantity*item_price) as items_ordered
from orderitems
where order_num = 20005;

12.2 聚集不同值

聚集函数的参数：

对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认行为）；
只包含不同的值，指定DISTINCT参数。

#只计算价格不同的产品价格的平均数
select avg(distinct prod_price) as avg_price
from products
where vend_id = 1003;

12.3 组合聚集函数

#显示多个聚集数
select count(*) as num_items,
min(prod_price) as price_min,
max(prod_price) as price_max,
avg(prod_price) as price_avg
from products;

13 分组数据

13.1 数据分组

如果要返回每个供应商提供的产品数目怎么办？或者返回只提供单项产品的供应商所提供的产品，或返回提供10个以上产品的供应商怎么办？

13.2 创建分组

#返回每一个供货商的产品的数量
select vend_id, count(*) as num_prods
from products
group by vend_id;

#返回每一个供货商的产品的数量
select vend_id, count(*) as num_prods
from products
group by vend_id with rollup;	#最后一行显示汇总值，也就是每一个分组的汇总值的汇总值

GROUP BY子句指示MySQL按vend_id排序并分组数据。这导致对每个vend_id而不是整个表计算num_prods一次。

使用GROUP BY的规则：

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

13.3 过滤分组

#显示按照cust_id分组的行数大于2的汇总值
select cust_id, count(*) as orders
from orders
group by cust_id
having count(*) >= 2;

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

#同时使用WHERE和HAVING
select vend_id, count(*) as num_prods
from products
where prod_price >= 10
group by vend_id
having count(*) >= 2;
#可以理解为先用where对产品进行过滤，只剩下价格大于等于10的产品，再按照分组进行统计

13.4 分组和排序

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

ORDER BY	GROUP BY
排序产生的输出	分组行。但输出可能不是分组的顺序
任意列都可以使用（甚至非选择的列也可以使用）	只可能使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要	如果与聚集函数一起使用列（或表达式），则必须使用

#1
select order_num, sum(quantity*item_price) as ordertatal
from orderitems
group by order_num
having sum(quantity*item_price) >= 50;

#2
select order_num, sum(quantity*item_price) as ordertatal
from orderitems
group by order_num
having sum(quantity*item_price) >= 50
order by ordertatal;

13.5 SELECT子句顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否

15 联结表

15.1 联结（join）

15.1.1 关系表

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

简而言之，就是使用某些共有的信息将两张表格串联起来

15.1.2 为什么使用联结

可以检索在多个表中的相关数据

15.2 创建联结

规定要联结的所有表以及它们如何关联

#where + 完全限定
select vend_name, prod_name, prod_price	#要检索的信息
from vendors, products	#信息的来源（表）
where vendors.vend_id = products.vend_id	#联结的条件
order by vend_name, prod_name;	#显示的顺序

15.2.1 WHERE子句的重要性

笛卡儿积（cartesian product）： 由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

#不使用where限定
select vend_name, prod_name, prod_price	#要检索的信息
from vendors, products	#信息的来源（表）
order by vend_name, prod_name;	#显示的顺序
#其显示结果将为vendors中的行数*products表的行数的结果

15.2.2 内部联结

目前为止所用的联结称为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内部联结。其实，对于这种联结可以使用稍微不同的语法来明确指定联结的类型。

#使用join & on
select vend_name, prod_name, prod_price
from vendors inner join products
on vendors.vend_id = products.vend_id;

15.2.3 联结多个表

在FROM后跟上多个表，并在其后的WHERE中用AND或者OR限制条件

select prod_name, vend_name, prod_price, quantity
from orderitems, products, vendors
where products.vend_id = vendors.vend_id
and orderitems.prod_id = products.prod_id
and order_num = 20005;

除此之外，还可以用联结来替换子句嵌套

16 创建高级联结

16.1 使用表别名

#为表使用别名，简化代码
select cust_name, cust_contact
from customers as c, orders as o, orderitems as oi
where c.cust_id = o.cust_id
and oi.order_num = o.order_num
and prod_id = 'TNT2';

16.2 使用不同类型的联结

16.2.1 自联结

#表联结自己本身
select p1.prod_id, p1.prod_name
from products as p1, products as p2
where p1.vend_id = p2.vend_id
and p2.prod_id = 'DTNTR';

16.2.2 自然联结

标准的联结返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。

#通配符只对第一个表使用。所有其他列明确列出，所以没有重复的列被检索出来。
select c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, OI.item_price
from customers as c, orders as o, orderitems as oi
where c.cust_id = o.cust_id
and oi.order_num = o.order_num
and prod_id = 'FB';

16.2.2 外部联结

#联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结。
#大致就是将一个列作为表头，显示出另一个表中所有含有这一列相关信息的数据
select customers.cust_id, orders.order_num
from customers LEFT OUTER JOIN orders
on customers.cust_id = orders.cust_id;

16.3 使用带聚集函数的联结

#使用联结和聚集函数进行统计任务
SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id


SELECT customers.cust_name,
	customers.cust_id,
	COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id

16.4 使用联结和联结条件

注意所使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。
保证使用正确的联结条件，否则将返回不正确的数据。
应该总是提供联结条件，否则会得出笛卡儿积。
在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。

17 组合查询

17.1 简介

多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。使用的便是UNION关键词

需要使用和组合查询的情况：

在单个查询中从不同的表返回类似结构的数据
对单个表执行多个查询，按单个查询返回数据

17.2 创建组合查询

17.2.1 使用UNION

给出查询语句，在各语句之间用UNION连接

select vend_id, prod_id, prod_price	#
from products
where prod_price <= 5
union
select vend_id, prod_id, prod_price	#
from products
where vend_id in (1001,1002);
#	要注意的是#号的两行必须一致，也就是所查询的列必须是一样的（次序可以不一样，以第一条查询为准）

17.2.2 UNION规则

必须有两条以及以上的查询语句
UNION中的每个查询必须包含相同的列、表达式或聚集函数
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。

17.2.3 包含或取消重复的行

UNION自动在查询结果集中去除了重复的行，若想显示重复出现的行，使用UNION ALL关键词

17.2.4 对组合查询结果排序

在最后添加ORDER BY语句

注意：使用order by时，一定要保证SELECT语句中的列顺序一致，否则会有些预期之外的错误

18 全文本搜索？？？

在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。这样，MySQL可以快速有效地决定哪些词匹配（哪些行包含它们），哪些词不匹配，它们匹配的频率，等等。

18.2 使用全文本搜索

使用全文本搜索的前提是对将要进行select的列进行索引，在索引之后，SELECT可与Match()和Against()一起使用以实际执行搜索。

18.2.1 启用全文本搜索支持

建立TABLE的时候添加note_text列即可

18.2.2 进行全文本搜索

#	查找有关‘rabbit’的项目
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) Against('rabbit')

#	make it use 'like' sentence
SELECT note_text
FROM productnotes
WHERE note_text LIKE '%rabbit%'

#	不同之处在于前者的返回有优先度，检索的内容越靠前，其优先级就越高

18.2.3 使用查询扩展

19 插入数据

-- 插入单行
INSERT INTO tableName(att1, att2, att3...)
VALUES(val1, val2, val3...);

-- 插入多行
INSERT INTO tableName(att1, att2, att3...)
VALUES(val1, val2, val3...), (val4, val5, val6...);

21 创建和操作表

AUTO_INCREMENT

可以使用 AUTO_INCREMENT 关键字使得属性值实现自增，如果手动指定，那么后续递增会从指定的这个值开始。

但设置这个属性的属性必须是表的主键，否则mysql将不予执行；

在对这样的表进行插入时，可以不指定这一列的值，mysql将会自动增序填充

Create table nums(num int not null auto_increment primary key, id int);
insert into nums(id) values(10);
insert into nums(id) values(10);
insert into nums(id) values(10);

DEFAULT

Create table nums(num int not null auto_increment primary key, id int deault 3);
-- 设置默认值

ALTER TABLE nums ALTER id SET DEFAULT 1000;	-- 修改默认值

ALTER

-- 添加一个属性
ALTER TABLE tableName
ADD attName attType;

-- 删除一个属性
ALTER TABLE tableName
DROP COLUMN attName;


-- 重命名表
RENAME TABLE tableName1 TO tableName2;

22 视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

对普通视图的使用最终只是会转换成响应的子查询语句，效率并不会特别高，因此需要使用物化视图

23 使用存储过程

一些定义

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

换句话说，使用存储过程有3个主要的好处，即简单、安全、高性能。

执行存储过程

MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。

CALL接受存储过程的名字以及需要传递给它的任意参数。

CALL producting (@pricelow,
                @pricehigh,
                @priceevarage);
-- 其中，执行名为productpricing的存储过程
-- 它计算并返回产品的最低、最高和平均价格。

创建存储过程

DELIMITER //	-- 重新定义结束符号，否则命令敲到 FROM products; 就会被执行

CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage
	FROM products;
END //

DELIMITER ;	-- 恢复结束符号

调用存储过程

CALL procedureName(para1, para2)

删除存储过程

DROP PROCEDURE procedureName;

使用参数

一般，存储过程并不显示结果，而是把结果返回给你指定的变量

-- 重新定义结束符号，否则命令敲到 FROM products; 就会被执行
delimiter //

CREATE PROCEDURE productpricing(
	OUT pl INTEGER,
	OUT ph INTEGER,
	OUT pa INTEGER)
BEGIN
	SELECT Min(prod_price) INTO pl FROM products;
	SELECT Max(prod_price) INTO ph FROM products;
	SELECT Avg(prod_price) INTO pa FROM products;
END //
delimiter ;
-- pl, ph, pa 是从存储过程中输出的变量

-- 调用时，变量名必须要使用 @ 修饰
CALL productpricing(@pricelow,
                   @pricehigh,
                   @priceaverage);
select @pricelow;




-- 使用 IN 和 OUT 的存储过程
DELIMITER //

CREATE PROCEDURE ordertotal(
	IN onnumber INT,
    OUT total INT
)
BEGIN
	SELECT Sum(item_price * quantity) FROM orderitems WHERE ordeer_num = onnumber INTO total;
END //

-- 调用这个存储过程
CALL ordertotal(20005, @total);
SELECT @total;

更加复杂的存储过程

CREATE PROCEDURE ordertotal(
	IN onnumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
	DECLARE total DECIMAL(8,2);
    DECLARE taxrate INT DEFAULT 6;
    
    SELECT Sum(item_price * quantity)
    FROM orderitems
    WHERE order_num = onumber
    INTO total;
    
    IF taxable THEN
    	SELECT total +  (total/100 * taxrate) INTO total;
    END IF;
    
    SELECT total INTO ototal;
END //


CALL ordertotal(20005, 0, @total);
SELECT @total;

25 触发器

想要某条语句（或某些语句）在事件发生时自动执行

MySQL支持且仅仅支持三种触发

DELETE
INSERT
UPDATE

创建触发器

CREATE TRIGGER newproduct
AFTER INSERT ON products	-- 在 INSERT 之后生效
FOR EACH ROW SELECT 'Product added';	-- 每个成功插入都会显示 Product added
-- 但是这个触发器已经不被支持，因为 trigger 已经不支持子查询

删除触发器

DROP TRIGGER newproduct

触发器的差异

`INSERT`触发器

可以引用一个名为NEW的虚拟表，访问那些被插入的行
即使使用BEFORE INSERT，NEW表中的值也可以被修改
对于那些会自动生成值的插入（比如插入一个订单，自动增序为其分配一个序号），在BEFORE时NEW.num将会永远是0，因此这种情况要使用AFTER来获取正确的序号

BEFORE or AFTER?

BEFORE通常用来进行数据验证和净化

`DELETE`触发器

可以引用一个名为OLD的虚拟表，访问那些被删除的行
OLD中的值是只读的，无法写

-- 这个触发器将会在删除之前对每一个删除的项目存档
CREATE TRIGGER deleteorder
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
	INSERT INTO archive_orders(order_num, order_date, cust_id)
    VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END

`UPDATE`触发器

可以引用一个名为OLD的虚拟表，访问那些被更新之前的行
可以引用一个名为NEW的虚拟表，访问那些被更新之后的行
BEFORE UPDATE允许更新NEW中的值
OLD只读

-- 对插入的地名取大写再插入
CREATE TRIGGER updatevendor
BEFORE UPDATE ON vendors
FOR EACH ROW
BEGIN
	SET NEW.vend_stat = Upper(NEW.vend_stat);
END

26 事务管理

事务处理（transaction processing）可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。

事务的关键字

事务（transaction）
回退（rollback）
提交（commit）
保留点（savepoint）

事务的示例

开始事务

START TRANSACTION

使用rollback

SELECT * from movies;
start transaction;
delete from movies;
select * from movies;
rollback;
select * from movies;

事务处理用来管理INSERT、UPDATE和DELETE语句。你不能回退CREATE或DROP操作。事务处理块中可以使用这两条语句，但如果你执行回退，它们不会被撤销。

28 安全管理

为了防止用户管理员的无意或者恶意操作对数据库内的数据产生不可逆转的影响

-- 查看数据库用户
use mysql;
select user from user;


-- 创建一个 user 并且将其命名为 ermao
Create user ermao IDENTIFIED by 'amor19.';

-- 重命名用户
RENAME USER ermao to amor;

-- 删除用户
DROP user amor;

-- 查看权限
SHOW GRANTS FOR ermao;

啥权限都没有

grant

要授予的权限
被授予访问权限的数据库或表
用户名

-- 设置权限
GRANT SELECT ON (database.tableName) TO userName
grant select on sql3.* TO ermao;

revoke

-- 撤销授权
REVOKE SELECT ON (database.tableName) FROM userName
revoke select on sql3.* FROM ermao;

整个数据库范围上的操作

可以赋予的权限

MySQL官方文档

全局权限

-- 对整个数据库
*.*

-- 所有权限
all

-- 创建数据库
create

这些通过grant和revoke对数据库或者表权限的授权仍然将会在数据库或者表格被删除后生效，如果数据库或者表格被恢复，这些权限依旧生效

还可以将权限和用户列表包括要授权的数据库用逗号间隔，一次授予多个用户对多张表的多个权限

grant select, insert on sql3.* to ermao, amor

更改密码

set password for ermao = Password('newPassword');
-- 后面的Password函数是用于进行加密的函数

SET password = Password('newPassword');
-- 为当前用户修改密码

29 数据库维护

备份数据

使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行，以便能正确地备份转储文件
可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据
可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件。这两条语句都接受将要创建的系统文件名，此系统文件必须不存在，否则会出错。数据可以用RESTORETABLE来复原
为了保证所有数据被写到磁盘（包括索引数据），可能需要在进行备份前使用FLUSH TABLES语句。

进行数据库维护

使用ANALYZE TABLE来检查表键是否正确

查看日志文件

hostname.err，它包含启动和关闭问题以及任意关键错误的细节
hostname.log，查询日志
hostname-bin，二进制日志，它记录更新过数据（或者可能更新过数据）的所有语句
hostname-slow.log，缓慢查询日志。顾名思义，此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。此日志通常名为

作者：locustree

出处：https://www.cnblogs.com/locustree/p/16258363.html

版权：本作品采用「署名-非商业性使用-相同方式共享 4.0 国际」许可协议进行许可。

posted @ 2022-05-11 16:03 槐下阅读(71) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· JUnit 简史及 JUnit3 设计思路

· 源码阅读之 ArrayList 类

· 【SQL必知必会】-12月

· MySQL必知必会

· MySQL基础

阅读排行：
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
· 记一次.NET内存居高不下排查解决与启示

公告

昵称：槐下
园龄： 3年9个月
粉丝： 4
关注： 2

+加关注

2025年3月

日

一

二

三

四

五

六

Loading

槐下