mysql必知必会-Code

关注

原创最新推荐文章于 2026-03-30 23:38:06 发布 639 阅读 AI 写同款· GEO 优化 ›

MySQL code解析 # Query入口 MySQL的代码比较messy，为了清楚理解MySQL的已有功能，本篇文章从MySQL的query层面进行分析，目的是理清MySQL的query处理流程和代码架构。本文基于MySQL5.7版本。 MySQL的入口代码的skeloton可以参见官方文档：https://dev.mysql.com/doc/internals/en/guided-tour-skeleto 阅读详情

emm

把书里的Code例程敲了一遍，做一个初步的学习，Mark一下

1）安装MySQL Server8.0

password： …

2）MySQL练习题

…

chap1-了解sql

DBMS：数据库管理系统

SQL : 数据库结构化查询语言

chap2-mysql简介

mySQL–客户端-服务器DBMS

MySQL–开放源代码

mySQL 执行速度很快

还有一种基于共享文件系统的DBMS；

chap3-使用MySql语句

基本指令

#创建数据库
create database crashcourse	;
#use
use crashcourse;
#show指令
show databases;
show tables;
show columns from customers;
describe == show columns from ...;
show status;
show create database;
show create table;
show errors;
show warnings;
# 查看详细介绍
help show;

chap4-检索数据

SELECT语句使用

SELECT prod_name
FROM products;

SELECT pro1 , pro2, pro3
FROM products
LIMIT 5,5;

SELECT *
FROM PRODUCTS;
//检索不同值
SELECT DISTINCT pro1，pro2
FROM products;

//完全限定的表名
SELECT products.pro1
FROM crashcourse.products;

chap5-排序检索数据

如果不排序，数据一般将以它在底层表中出现的顺序显示；

SELECT prod_name
FROM products
ORDER BY prod_name;

SELECT prod1 , prod2 , prod3
FROM products 
ORDER BY prod1 , prod2;

//指定排序方向  DESC：降序   ASC：升序
SELECT prod1 , prod2 , prod3
FROM products
ORDER BY prod1 DESC , prod2 , prod3 DESC;

//找出最昂贵物品
SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;

chap6-过滤数据

WHERE子句的使用:

where子句操作符：

= 、 <> 、 != 、 < 、 > 、 <= >= BETWEEN

特殊的where子句： IS NULL ， IS NOT NULL

MySQL执行匹配时默认不区分大小写

单引号用来限定字符串

# 相等测试
SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.0;

#检查单个值
SELECT prod_name , prod_price
FROM products
WHERE product_name = 'fuses';

SELECT prod_name, prod_price
FROM products
WHERE prod_price <= 10;

#不匹配检查
SELECT vend_id , prod_name
FROM products
WHERE vend_id <> 1003;

#范围检查
SELECT prod_name , prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;	//包括5-10

#空值检查
SELECT prod_name
FROM products
WHERE prod_price IS NULL;

SELECT cust_id
FROM customers
WHERE cust_email IS NULL;

chap7-数据过滤

运算符： AND 、 OR 、 () 、 IN 、NOT

AND优先级高于OR

MySQL支持使用NOT对IN、BETWEEN、EXISTS子句进行取反

SELECT prod_id , prod_price , prod_name
FROM products
WHERE end_id = 1003 AND prod_price <= 10;

SELECT prod_name , prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003;

# AND 优先级高于OR
SELECT prod_name , prod_price
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND pord_price <= 10;

# IN
SELECT prod_name, prod_price
FROM products
WHERE vend_id IN (1002,1003)
ORDER BY prod_name;

# NOT
SELECT prod_name , prod_price
FROM products
WHERE vend_id NOT IN (1002,1003)
ORDER BY prod_name;

chap8-用通配符进行过滤

LIKE操作符，%，_通配符

% : 匹配0 ， 1， 2个字符，任何字符出现任意次数

_ ：匹配单个字符

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

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

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

SELECT prod_id , prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil';

注意：

不要过度使用通配符，效率较低；
不要在搜索模式的开始处使用通配符，速度较慢；
仔细注意通配符的位置；

chap9-用正则表达式进行搜索

正则表达式用来匹配文本特殊的串（子串）

MySQL仅支持多数正则表达式实现的一个很小的子集

REGEXP : 正则表达式

. : 匹配任意一个字符

| : OR匹配，匹配任意一个字符

[] : [123] === [1|2|3] ,另一种or语句

- : 定义一个范围， [0-9]

MySQL 为了匹配特殊字符，必须使用\\为前导， \\-表示查找- , \\. 表示查找 . ;(转义字符)

\\f 换页

\\n 换行

\\r 回车

\\t 制表

\\v 纵向制表

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;

SELECT prod_name
FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;

#LIKE 匹配整个列
SELECT prod_name
FROM products
WHERE prod_name LIKE '1000'
ORDER BY prod_name;

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000|3000'
ORDER BY prod_name;

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name;

# 1 or 2 or 3 Ton
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1|2|3 Ton'
ORDER BY prod_name;

# 集合可以定义要匹配的一个或多个字符
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[0-5] Ton'
ORDER BY prod_name;

SELECT vend_name
FROM vendors
WHERE vend_name REGEXP '\\.'
ORDER BY vend_name;

MySQL 中可以使用预定义的字符集，称为字符类（character class）

[:alnum:]
[:alpha:]
[:blank:]
[:cntrl:]
[:digit:]
[:lower:]
[:print:]
[:punct:]
[:space:]
[:upper:]
[:xdigit:]

匹配多个实例：

*   :  0个或多个匹配
+   ： 1个或多个匹配 ==  {1，}
？  ： 0个或一个匹配 ==  {0,1}
{n} :  指定数目匹配
{n,} : 不少于指定数目匹配
{n,m} : 匹配数目范围

SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)'

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[[:digit:]]{4}'
ORDER BY prod_name;

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]'
ORDER BY prod_name;

正则表达式匹配特定位置的文本：

^ : 文本的开始

$ : 文本的结束

[[:<:]] : 词的开始

[[:>:]] : 词的结尾

SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name;

//简单的正则表达式测试（不使用数据库表）
SELECT 'hello' REGXEP '[0-9]';

chap10-创建计算字段

计算字段并不存在于数据库表中，计算字段是运行时在SELECT语句内创建的；
术语字段通常用在计算字段的连接上
一般来说，在数据库服务器上完成这些操作（转换和格式化）比在客户机中完成要快的多；
使用别名： AS 关键字；
使用计算字段进行算术计算；支持 + - * /

Concat() : 用来拼接列，把多个串连接起来形成一个较长的串，需要指定一个或多个串，各个串之间用逗号隔开；

RTrim() : 去除值右边所有空格

LTrim() : 去除值左边所有空格

Trim() : 去掉串左右两边的空格

SELECT Concat(vend_name , ' (' , vend_country, ')')
FROM vendors
ORDER BY vend_name;

SELECT Concat(RTrim(vend_name) , ' (' , RTrim(vend_country) , ')')
FROM vendors
ORDER BY vend_name;

//AS  : 对计算字段取别名
SELECT Concat(RTrim(vend_name) , ' (' , RTrim(vend_country) , ')') AS vend_title
FROM vendors
ORDER BY vend_name;

SELECT prod_id , quantity, item_price
FROM orderitems
WHERE order_num = 20005;

SELECT prod_id,
	quantity,
	item_price,
	quantity * item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;

chap11-使用数据处理函数

SQL支持使用函数来处理数据；
SQL支持的函数类型：处理文本串、在数值数据上进行算术操作、处理日期和时间值并从中提取特定成分、返回DBMS的特定信息；
SOUNDEX : 一个将任何文本串转换为描述其语音表示的字母数字模式的算法
日期和时间采用相应的数据类型和特殊的格式存储，以便能够快速和有效地排序和过滤，并且节省物理存储空间；yyyy-mm-dd

日期和时间处理函数：

AddDate() AddTime() CurDate() CurTime() Date() DateDiff() Day() …

数值处理函数：

Abs() Cos() Exp() Mod() Pi() Rand() Sin() Sqrt() Tan() …

SELECT vend_name , Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;

//寻找发音相似的匹配列值
 mysql> SELECT cust_name , cust_contact
    -> FROM customers
    -> WHERE Soundex(cust_contact) = Soundex('Y Lie');
    
mysql> SELECT cust_id , order_num
    -> FROM orders
    -> WHERE order_date = '2005-09-01';  
 //使用Date()函数
    mysql> SELECT cust_id , order_num
    -> FROM orders
    -> WHERE Date(order_date) = '2005-09-01';
 //匹配日期中的天数
 mysql> SELECT cust_id  , order_num
    -> FROM orders
    -> WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
 //另一种写法
 mysql> SELECT cust_id, order_num
    -> FROM orders
    -> WHERE Year(order_date) = 2005 AND MONTH(order_date) = 9;

chap12-汇总数据

需要汇总数据而不是把它们实际检索出来，MySQL提供了专门的函数（聚集函数）；
MySQL 提供了5个聚集函数（运行在行组上，计算和返回单个值的函数）；
这5个聚集函数都可以如下使用：
- 对所有行执行计算（指定ALL参数或不指定（默认））
- 只包含不同的值（指定DISTINCT参数）
聚集函数用来汇总数据，MySQL支持一系列的聚集函数；

// SQL聚集函数
AVG()    ： 返回某列的平均值 ，，忽略NULL值的行
COUNT()  :  返回某列的行数
MAX()   :  某列的最大值
MIN()   :  某列的最小值
SUM()   :  某列值之和

mysql> SELECT AVG(prod_price) AS avg_price
    -> FROM products;
//确定特定行和列的平均值
mysql> SELECT AVG(prod_price) AS avg_price
    -> FROM products
    -> WHERE vend_id = 1003;

COUNT(*) ：对表中行的数目进行计数，无论是空值（NULL）还是非空值

COUNT(column) : 对特定列中具有值的行进行计数

mysql> SELECT COUNT(*) AS num_cust
    -> FROM customers;

mysql> SELECT COUNT(cust_email) AS num_cust
    -> FROM customers;

MAX() : 返回指定列中的最大值 , 一般用来找出最大的数值或日期值，忽略空行

允许用来返回任意列中最大值，包括返回文本列中的最大值

mysql> SELECT MAX(prod_price) AS max_price
    -> FROM products;

MIN() : 返回指定列的最小值 , 忽略空行

mysql> SELECT MIN(prod_price) AS min_price
    -> FROM products;

SUM() : 返回指定列值的和

mysql> SELECT SUM(quantity) AS items_ordered
    -> FROM orderitems
    -> WHERE order_num = 20005;

mysql> SELECT SUM(quantity * item_price) AS total_price
    -> FROM orderitems
    -> WHERE order_num = 20005;

DISTINCT只能用于COUNT() , 不能用于COUNT(DISTINCT)

//平均值只考虑各个不同的价格
mysql> SELECT AVG(DISTINCT prod_price) AS avg_price
    -> FROM products
    -> WHERE vend_id = 1003;

组合聚集函数：

mysql> 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;

chap13-汇总数据

SQL聚集函数可用来汇总数据；
数据分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算；
GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集；
GROUP BY子句的一些规定：
- GROUP BY子句可以包含任意数目的列，能够对分组进行嵌套；
- 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总；
- GROUP BY子句列出的每个列都必须是检索列或有效地表达式，不能使用聚集函数或别名；
- 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出；
- 如果分组中有NULL值，NULL将作为一个分组返回；
- GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前
MySQL支持过滤分组，WHERE过滤指定的是行而不是分组 ， WHERE没有分组的概念，HAVING子句类似WHERE子句，WHERE过滤行，而HAVING过滤分组；
HAVING子句：过滤针对的是基于分组聚集值而不是特定行值；
WHERE子句在分组前进行过滤，HAVING子句在分组后进行过滤；
GROUP BY 和ORDER BY经常完成相同的工作，但它们并不完全一样：
- ORDER BY ：排序产生的输出，任意列都可以使用；
- GROUP BY：分组行，但输出可能不是分组的顺序，只可能使用选择列或表达式列；
- 如果需要对输出进行排序，一定要明确提供ORDER BY子句；

SELECT : 要返回的列或表达式

FROM ：从中检索数据的表

WHERE ：行级过滤

GROUP BY ：分组说明

HAVING ：组级过滤

ORDER BY : 输出排序顺序

LIMIT：要检索的行数

mysql> SELECT vend_id , COUNT(*) AS num_prods
    -> FROM products
    -> GROUP BY vend_id;

//使用 WITH ROLLUP关键字
mysql> SELECT vend_id , COUNT(*) AS num_prods
    -> FROM products
    -> GROUP BY vend_id WITH ROLLUP;
    
//HAVING过滤分组
mysql> SELECT cust_id , COUNT(*) AS orders
    -> FROM orders
    -> GROUP BY cust_id
    -> HAVING COUNT(*) >= 2;
//同时使用WHERE和HAVING
mysql> SELECT vend_id , COUNT(*) AS num_prods
    -> FROM products
    -> WHERE prod_price >= 10
    -> GROUP BY vend_id
    -> HAVING COUNT(*) >= 2;

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

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

chap14-使用子查询

查询（query）：任何SQL语句都是查询，但此术语一般指SELECT语句；
子查询：嵌套在其他查询中的查询；
在SELECT语句中，子查询总是从内向外处理
WHERE子句中使用子查询能够编写出功能很强并且很灵活的SQL语句，对于能嵌套的子查询的数目没有限制，不过在实际使用中，由于性能的限制，不能嵌套太多子查询；
作为计算字段使用子查询；
相关子查询：涉及到外部查询的子查询，任何时候只要列名可能有多义性，就必须使用这种语法；
子查询最常用于WHERE子句中的IN操作符，以及用来填充计算列；

mysql> SELECT order_num
    -> FROM orderitems
    -> WHERE prod_id = 'TNT2';
mysql> SELECT cust_id
    -> FROM orders
    -> WHERE order_num IN (20005, 20007);
//合并 子查询
mysql> SELECT cust_id
    -> FROM orders
    -> WHERE order_num IN (SELECT order_num
    ->                     FROM orderitems
    ->                     WHERE prod_id = 'TNT2');

//WHERE语句使用子查询
mysql> SELECT cust_name , cust_contact
    -> FROM customers
    -> WHERE cust_id IN (SELECT cust_id
    ->                   FROM orders
    ->                   WHERE order_num IN (SELECT order_num
    ->                                      FROM orderitems
    ->                                     WHERE prod_id = 'TNT2'));

mysql> SELECT COUNT(*) AS orders
    -> FROM orders
    -> WHERE cust_id = 10001;

//计算字段使用子查询
mysql> SELECT cust_name,
    -> cust_state,
    -> (SELECT COUNT(*)
    -> FROM orders
    -> WHERE orders.cust_id = customers.cust_id) AS orders
    -> FROM customers
    -> ORDER BY cust_name;

chap15-联结表

数据检索查询中使用联结（Join）表；
关系表的设计就是要保证把信息分解成多个表，一类数据一个表，各表通过某些常用的值（关系设计中的关系）互相关联；
外键：外键为某个表中的一列，它包含另一个表的主键值，定义了2个表之间的关系；
分解数据为多个表能够更有效地存储，更方便的处理，具有更强的可伸缩性；
联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结，使用特殊语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行；
在引用的列可能出现二义性时，必须使用完全限定列名；
在一条SELECT语句中联结几个表时，相应的关系是在运行中构造的；
笛卡尔积：由没有联结条件的表关系返回的结果为笛卡尔积，有时又称为叉联结（cross join），检索出的行的数目将是第一个表中的行数乘以第二个表中的行数；
应该保证所有的联结都有WHERE子句，否则MySQL将返回比期望值多的多的数据；
等值联结：基于2个表之间的相等测试，有时又称为内部联结（INNER JOIN）；
使用WHERE… AND子句可以联结多个表；
MySQL在运行时关联指定的每个表以处理联结，这种处理可能是非常耗费资源的，因此，应该仔细，不要联结不必要的表，联结的表越多，性能下降的越厉害；

//FROM子句有2个表，WHERE子句进行联结
mysql> SELECT vend_name , prod_name , prod_price
    -> FROM vendors , products
    -> WHERE vendors.vend_id = products.vend_id
    -> ORDER BY vend_name , prod_name;

//笛卡尔积   联结表
mysql> SELECT vend_name, prod_name, prod_price
    -> FROM vendors, products
    -> ORDER BY vend_name , prod_name;

//INNER JOIN ON 语法
mysql> SELECT vend_name , prod_name , prod_price
    -> FROM vendors INNER JOIN products
    -> ON vendors.vend_id = products.vend_id;

mysql> 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;
    
//子查询写法
mysql> SELECT cust_name , cust_contact
    -> FROM customers
    -> WHERE cust_id IN (SELECT cust_id
    ->                   FROM orders
    ->                   WHERE order_num IN (SELECT order_num
    ->                                       FROM orderitems
    ->                                       WHERE prod_id = 'TNT2'));

//联结写法 ： WHERE子句中前两个关联联结表，最后一个进行过滤
mysql> SELECT cust_name , cust_contact
    -> FROM customers , orders, orderitems
    -> WHERE customers.cust_id = orders.cust_id
    -> AND orders.order_num = orderitems.order_num
    -> AND prod_id = 'TNT2';

chap16-创建高级联结

别名除了用于列名和计算字段外，还可以给表名取别名；
表别名只在查询中使用，与列别名不一样，表别名不返回到客户机；
不同类型的联结：内部联结，自联结，自然联结和外部联结；
自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句
标准的联结返回所有数据，甚至相同的列出现多次，自然联结排除多次出现，使每个列只返回一次，自然联结通过使用SELECT *实现；
外部联结：许多联结将一个表中的行与另一个表中的行相关联，但有时会需要包含没有关联行的哪些行；外部联结使用OUTTER JOIN FROM ... ON..
在使用OUTER JOIN关键字时，必须使用LEFT或RIGHT指定包括其所有行的表，RIGHT是OUTER JOIN 右边的表，LEFT是OUTER JOIN左边的表
注意：
- 注意使用的联结类型，一般使用内部联结；
- 保证使用正确的联结条件；
- 应该总是提供联结条件，否则会得出笛卡尔积；
- 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型；

mysql> SELECT Concat(RTrim(vend_name), ' (' , RTrim(vend_country), ')') AS
    -> vend_title
    -> FROM vendors
    -> ORDER BY vend_name;

//使用表别名
mysql> 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';

//自联结
// 子查询
mysql> SELECT prod_id , prod_name
    -> FROM products
    -> WHERE vend_id = (SELECT vend_id
    ->                  FROM products
    ->                  WHERE prod_id = 'DTNER');

mysql> 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 = 'DTNER';
//自然联结
mysql> 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';

//内部联结
mysql> SELECT customers.cust_id , orders.order_num
    -> FROM customers INNER JOIN orders
    -> ON customers.cust_id = orders.cust_id;

//外部联结
mysql> SELECT customers.cust_id , orders.order_num
    -> FROM customers LEFT OUTER JOIN orders
    -> ON customers.cust_id = orders.cust_id;
//外部联结 ， RIGHT子句
mysql> SELECT customers.cust_id , orders.order_num
    -> FROM customers RIGHT OUTER JOIN orders
    -> ON orders.cust_id = customers.cust_id;

//GROUP BY子句按客户分组数据
mysql> 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;

mysql> 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;

chap17-组合查询

MySQL允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回，这些组合查询通常称为并（UNION）或复合查询；
多数情况下，组合查询与多条WHERE子句查询完成的工作相同；
UNION的规则：
- UNION必须由2条或2条以上的SELECT语句组成，语句之间使用UNION分隔；
- UNION中的每个查询必须包含相同的列，表达式或聚集函数；
- 列数据类型必须兼容，类型不必完全相同；
UNION自动从查询结果集中去除重复的行， UNION ALL匹配所有的行；
在使用UNION时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后；
利用UNION，可以将多条查询的结果作为一条组合查询返回，不管他们的结果中包含还是不包含重复；

//UNION:  组合查询
mysql> 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);

mysql> SELECT vend_id , prod_id , prod_price
    -> FROM products
    -> WHERE prod_price <= 5
    -> OR vend_id IN (1001,1002);
// UNION ALL 匹配所有行
mysql> SELECT vend_id , prod_id, prod_price
    -> FROM products
    -> WHERE prod_price <= 5
    -> UNION ALL
    -> SELECT vend_id, prod_id, prod_price
    -> FROM products
    -> WHERE vend_id IN (1001,1002);

//ORDER BY 子句进行排序
mysql> 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)
    -> ORDER BY vend_id, prod_price;

chap18-全文本搜索

MyISAM支持全文本搜索，InnoDB不支持全文本搜索；
使用LIKE能查找包含特殊值或部分值的行；
在使用全文本搜索时，MySQL不需要分别查看每个行，MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行；
为了进行全文本搜索，必须索引被搜索的列；
全文本搜索会对结果进行排序，具有较高等级的行会优先返回；
查询扩展用来放宽所返回的全文本搜索结果的范围；关键字WITH QUERY EXPANSION；
MySQL支持全文本搜素ode另外一种形式，称为布尔文本搜索，布尔文本搜索即使没有定义FULLTEXT索引，也可以使用它；关键字IN BOOLEAN MODE；
在布尔文本搜索中，不按等级值降序排序返回行；

注意事项：

在索引全文本数据是，短词被忽略并且从索引中排除，短词指3个或3个以下字符的词；
MySQL带有一个内建的非用词列表，这些词在索引全文本数据时总是被忽略，该列表可覆盖；
50%规则：**如果一个词出现在50%以上的行中，则它将作为一个非用词忽略，**该规则不适用于IN BOOLEAN MODE；
如果表中行数少于3行，则全文本搜索不返回结果；
忽略词中的单引号，don’t —> dont;
不具有词分割符的语言不能恰当地返回全文本搜索结果；
仅MyISAM数据库引擎支持全文本搜索；

SELECT 与Match()和Against()一起使用进行全文本搜索；

Match()指定被搜索的列；传递给Match()的值必须与FULLTEXT()定义中相同；

Against()指定要使用的搜索表达式；

布尔文本搜索可以使用全文本布尔操作符：

+ : 包含
- : 排除
> :
<:
()
~

“”

CREATE TABLE productnotes
(
	note_id   int     NOT NULL AUTO_INCREMENT,
    prod_id    char(10)  NOT NULL , 
    note_date  datetime  NOT NULL , 
    note_text  text NULL,
    PRIMARY KEY(note_id),
    FULLTEXT(note_text)
)ENGINE=MyISAM;

//全文本搜索
mysql> SELECT note_text
    -> FROM productnotes
    -> WHERE Match(note_text) Against('rabbit');

mysql> SELECT note_text
    -> FROM productnotes
    -> WHERE note_text LIKE '%rabbit%';

//使用查询扩展
mysql> SELECT note_text
    -> FROM productnotes
    -> WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

//布尔文本搜索
mysql> SELECT note_text
    -> FROM productnotes
    -> WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);

//全文本布尔搜索（使用布尔操作符）
//2个词都必须包含
mysql> SELECT note_text
    -> FROM productnotes
    -> WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);
//包含2个词中至少1个
mysql> SELECT note_text
    -> FROM productnotes
    -> WHERE Match(note_text) Against('rabit bait' IN BOOLEAN MODE);
//将"rabbit bait"看为1个词
mysql> SELECT note_text
    -> FROM productnotes
    -> WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);

mysql> SELECT note_text
    -> FROM productnotes
    -> WHERE Match(note_text) Against('>rabbit <bait' IN BOOLEAN MODE);

mysql> SELECT note_text
    -> FROM productnotes
    -> WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);

chap19-插入数据

INSERT用来向数据库表中插入或添加行；
INSERT语句一般不产生输出；
INSERT语句更安全的用法是在表名的括号里明确给出列名，VALUES中的值与之对应；；
INSERT语句应该总是使用有列的列表，这样即使表结构发生了变化仍然能够正常起作用；
INSERT语句如果给出列名，则必须对每个列出的列给出一个值，使用这种语法，可以省略不需要的列；
INSERT语句插入多行，VALUES语句使用逗号进行分割；
可以用INSERT语句将一条SELECT语句查询的结果插入表中；

INSERT INTO …(…)

VALUES(

…),

VALUES(

…)；

//插入一行 ： 该语句高度依赖表中列的定义次序 ，语法简单，但并不安全
mysql> INSERT INTO Customers
    -> VALUES(NULL,
    -> 'Pep E. Lapew',
    -> '100 Main Street',
    -> 'LOS Angles',
    -> 'CA',
    -> '90046',
    -> 'USA',
    -> NULL,
    -> NULL);
//更安全方法
mysql> INSERT INTO Customers(cust_name,
    -> cust_address,
    -> cust_city,
    -> cust_state,
    -> cust_zip,
    -> cust_country,
    -> cust_contact,
    -> cust_email)
    -> VALUES('Pep E. Lapew',
    -> '100 Main Street',
    -> 'Los Angeles',
    -> 'CA',
    -> '90046',
    -> 'USA',
    -> NULL,
    -> NULL);
//插入多行
mysql> INSERT INTO customers(cust_name,
    -> cust_address,
    -> cust_city,
    -> cust_state,
    -> cust_zip ,
    -> cust_country)
    -> VALUES(
    ->          'Pep E. Lapew',
    ->          '100 Main Street',
    ->          'Los Angeles',
    ->          'CA',
    ->          '90046',
    ->          'USA'
    -> ),
    -> VALUES(
    ->          'M. Martian',
    ->          '42 Galaxy Way',
    ->          'New York',
    ->          'NY',
    ->          '11213',
    ->          'USA'
    -> );

//INSERT SELECT语句 : 将SELECT 语句检索出的结果插入表customers中
INSERT INTO customers(cust_id,
                     cust_contact,
                     cust_email)
SELECT cust_id,
	   cust_contact,
	   cust_email
FROM custnew;

chap20-更新和删除数据

upadate delete使用: UPDATE 用来更新表中的数据，可更新表中特定行或所有行；
UPDATE 如果没有WHERE过滤子句，将过滤表中的所有行；
UPDATE IGNORE : 更新出错时仍强制更新；
使用DELETE语句从表中删除数据：可删除特定的行或表的所有行；
DELETE不删除表本身；
使用强制实施引用完整性的数据库，MySQL不允许删除具有与其他表相关联的数据的行；

//UPDATE 表中数据
mysql> UPDATE customers
    -> SET cust_email = 'elmer@fudd.com'
    -> WHERE cust_id = 10005;
//更新多个列
mysql> UPDATE customers
    -> SET cust_name = 'The Fudds',
    ->    cust_email = 'elmer@fudd.com'
    -> WHERE cust_id = 10005;

//更新出错时仍强制更新
UPDATE IGNORE customers 
....
//删除特定行
mysql> DELETE FROM customers
    -> WHERE cust_id = 10006;

chap21-创建和操纵表

CREATE TABLE必须给出下列信息：
- 新表的名字；
- 表列的名字和定义，用逗号隔开；
MySQL 要求在创建新表时，指定的表名必须不存在，不能直接覆盖，必须对要覆盖的表进行手动删除；仅在表不存在时创建：CREATE TABLE ... IF NOT EXISTS
NULL值：没有值或缺值，允许NULL值的列也允许在插入行时不给出该列的值，不允许NULL值的列不接受该列没有值的行；
不要把NULL值和空串混淆，NULL值是没有值，不是空串；
主键值必须唯一，表中每一行都必须具有唯一的主键值，主键如果使用多个列，则这些列的组合必须唯一；
AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量，每次执行一个INSERT操作时，MySQL自动对该列增量，给该列赋予下一个可用的值，每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（可通过成为主键）；
MySQL具有一个具体管理和处理数据的内部引擎，MySQL具有多种引擎，他们各自具有不同的功能和特性：
- InnoDB : 可靠事务处理引擎
- MEMORY ：数据存储在内存，速度快
- MyISAM : 性能极高的引擎，支持全文本搜索，不支持事务处理
引擎类型可以混用，但是外键不能跨引擎；
更新表：ALTER TABLE ， ALTER TABLE也可以用来定义外键；

SELECT last_insert_id() ; //返回最后一个AUTO_INCREMENT的索引值

DEFAULT : 可以指定表列的默认值；

删除表： DROP TABLE …

重命名表： RENAME TABLE … TO … ,

… TO … ;

//创建一个自己的数据库
CREATE DATABASE hello;
//创建一个Table
mysql> CREATE TABLE customers
    -> (
    -> cust_id    int    NOT NULL       AUTO_INCREMENT,
    -> cust_name   char(50)     NOT NULL,
    -> cust_address     char(50)        NULL,
    -> cust_city        char(50)        NULL,
    -> cust_state       char(5)         NULL,
    -> cust_zip         char(10)        NULL,
    -> cust_country     char(50)        NULL,
    -> cust_contact     char(50)        NULL,
    -> cust_email       char(255)       NULL,
    -> PRIMARY KEY(cust_id)
    -> )ENGINE=InnoDB;

//创建orders表
mysql> CREATE TABLE orders
    -> (
    -> order_num   int   NOT NULL AUTO_INCREMENT,
    -> order_date  datetime  NOT NULL,
    -> cust_id     int   NOT NULL,
    -> PRIMARY KEY(order_num)
    -> )ENGINE = InnoDB;

//创建vendors表
mysql> CREATE TABLE vendors
    -> (
    -> vend_id    int    NOT NULL    AUTO_INCREMENT,
    -> vend_name  char(50)   NOT NULL,
    -> vend_address char(50)  NULL,
    -> vend_city   char(50)   NULL,
    -> vend_state   char(50)  NULL,
    -> vend_zip     char(5)  NULL,
    -> vend_country  char(50) NULL,
    -> PRIMARY KEY(vend_id)
    -> )ENGINE = InnoDB;
//创建主键为多列的表
mysql> CREATE TABLE orderitems
    -> (
    -> order_num        int     NOT NULL,
    -> order_item       int     NOT NULL,
    -> prod_id          char(10)        NOT NULL,
    -> quantity         int     NOT NULL,
    -> item_price       decimal(8,2)    NOT NULL,
    -> PRIMARY KEY(order_num,order_item)
    -> )ENGINE = InnoDB;
//使用DEFAULT 
mysql> CREATE TABLE orderitems
    -> (
    ->  order_num       int     NOT NULL,
    -> order_item       int     NOT NULL,
    ->  prod_id         char(10) NOT NULL,
    -> quantity         int     NOT NULL DEFAULT 1,
    -> item_price       decimal(8,2) NOT NULL,
    -> PRIMARY KEY(order_num , order_item)
    -> )ENGINE = InnoDB;

//ALTER TABLE  :  更新表
mysql> ALTER TABLE vendors
    -> ADD vend_phone CHAR(20);
//查看表结构
DESCRIBE vendors;
//ALTER TABLE ： 删除表的一列
mysql> ALTER TABLE vendors
    -> DROP COLUMN vend_phone;

//删除表
mysql> DROP table customers2;

chap22-使用视图

视图是虚拟的表，视图只包含使用时动态检索数据的查询；视图不包含表中应该有的任何列或数据，它包含的是一个SQL查询；
视图的好处：
- 重用SQL语句；
- 简化复杂的SQL操作；
- 使用表的组成部分而不是整个表；
- 保护数据；
- 更改数据格式和表示；
视图的一些限制：
- 视图必须唯一命名；
- 视图可以嵌套；
- ORDER BY可以用在视图中，如果从该视图检索数据的SELECT也包含了ORDER BY，则视图中的ORDER BY将被覆盖；
- 视图不能索引，也不能有关联的触发器或默认值；
- 视图可以和表一起使用；
视图可以一次创建，多次使用；
视图的另一作用是重新格式化检索出的数据；
视图主要用于数据检索，是可以更新的，但是以下情况不能更新视图：
- 分组
- 联结
- 子查询
- 并
- 聚集函数
- DISTINCT
- 导出列
视图是虚拟的表，不包含数据，而是检索数据的查询，对SELECT语句的层次封装；创建视图后可以像表一样查看视图

CREATE VIEW … AS … ;

//使用视图  VIEW 简化联结SQL 语句书写  productcustomers联结3个表
mysql> 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;
mysql> SELECT cust_name, cust_contact
    -> FROM productcustomers
    -> WHERE prod_id = 'TNT2';

//用视图格式化数据
mysql> CREATE VIEW vendorlocations AS
    -> SELECT Concat(RTrim(vend_name) , ' (',RTrim(vend_country), ')')
    -> AS vend_title
    -> FROM vendors
    -> Order BY vend_name;
mysql> SELECT *
    -> FROM vendorlocations;

//用视图过滤数据
mysql> CREATE VIEW customeremaillist AS
    -> SELECT cust_id , cust_name, cust_email
    -> FROM customers
    -> WHERE cust_email IS NOT NULL;
mysql> SELECT *
    -> FROM customeremaillist;

//使用视图简化计算字段使用
mysql> CREATE VIEW orderitemsexpanded AS
    -> SELECT order_num,
    ->          prod_id,
    ->          quantity,
    ->          item_price,
    ->          quantity * item_price AS expanded_price
    -> FROM orderitems;
mysql> SELECT *
    -> FROM orderitemsexpanded
    -> WHERE order_num = 20005;

chap23-使用存储过程

存储过程：为以后的使用而保存一条或多条MySQL语句的集合，可将其视为批文件；
PROCEDURE可以使用变量：

执行存储过程：

CALL productpricing(@pricelow,

@pricehigh

@priceaverage);

DELIMITER // : 告诉命名行实用程序使用//作为新的语句分割结束符

IN : 传给存储过程

OUT ：从存储过程传出

INOUT : 对存储过程传入和传出

INTO ：指定变量保存位置

mysql> DELIMITER  //
mysql> CREATE PROCEDURE productpricing()
    -> BEGIN
    ->    SELECT   Avg(prod_price)  AS   priceaverage
    ->    FROM   products;
    -> END //
//使用  ;  作为分割符
DELIMITER ;
//调用
mysql> CALL productpricing();
//删除   注意存储过程后没加()
mysql> DROP PROCEDURE productpricing;

//创建带参数的存储过程 PROCEDURE
mysql> CREATE PROCEDURE productpricing(
    -> OUT      pl      DECIMAL(8,2),
    -> OUT      ph      DECIMAL(8,2),
    -> OUT      pa      DECIMAL(8,2)
    -> )
    -> 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 \\

//创建存储过程
mysql> CREATE PROCEDURE ordertotal(
    ->  IN onumber  INT,
    ->  OUT ototal  DECIMAL(8,2)
    -> )
    -> BEGIN
    ->    SELECT  Sum(item_price * quantity)
    ->    FROM orderitems
    ->    WHERE order_num = onumber
    ->    INTO ototal;
    -> END //
//调用
mysql> CALL ordertotal(20005, @total) //
//查看变量值
mysql> SELECT @total //

mysql> 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
    ->    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;
    ->    ENDIF;
    ->
    ->    SELECT total INTO ototal;
    -> END //

chap24-使用游标

游标是存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集；
MySQL游标（Cursor）只能用于存储过程和函数；
游标使用：
- 在能够使用游标前，必须声明它；
- 一旦声明以后，必须打开游标以供使用；
- 对于填有数据的游标，根据需要检索各行；
- 在结束游标使用时，必须关闭游标；
DECLARE 定义的局部变量必须定义在任意游标或句柄之前，句柄必须在游标之后定义；

DECLARE … CURSOR

FOR

OPEN …

FETCH …

CLOSE …

//更改终止结束符
DELIMITER //
//创建游标
mysql> CREATE PROCEDURE processorders()
    -> BEGIN
    ->   DECLARE ordernumbers CURSOR
    ->   FOR
    ->   SELECT order_num FROM orders;
    -> END //

mysql> CREATE PROCEDURE processorders()
    -> BEGIN
    ->  DECLARE ordernumbers CURSOR
    ->  FOR
    ->  SELECT order_num FROM orders;
    ->  OPEN ordernumbers;
    ->  CLOSE ordernumbers;
    -> END //

mysql> DROP PROCEDURE processorders //

//循环检索数据，从第一行到最后一行
mysql> CREATE PROCEDURE processorders()
    -> BEGIN
    ->  DECLARE done BOOLEAN DEFAULT 0 ;
    ->  DECLARE o INT;
    ->  DECLARE ordernumbers CURSOR
    ->  FOR
    ->  SELECT order_num FROM orders;
    ->  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    ->  OPEN ordernumbers;
    ->  REPEAT
    ->          FETCH ordernumbers INTO o;
    ->  UNTIL done END REPEAT;
    ->  CLOSE ordernumbers;
    -> END //

mysql> 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 FOR SQLSTATE '02000' SET done =1;
    ->   CREATE TABLE IF NOT EXISTS ordertotalS
    ->     (order_num INT , total DECIMAL(8,2));
    ->   OPEN ordernumbers;
    ->   REPEAT
    ->      FETCH ordernumbers INTO o;
    ->      CALL ordertotal(o , 1, t);
    ->      INSERT INTO ordertotals(order_num , total)
    ->      VALUES(o,t);
    ->   UNTIL done END REPEAT;
    ->   CLOSE ordernumbers;
    -> END //

chap25-使用触发器

用于某条语句在事件发生时自动执行，可以使用触发器，MySQL的触发器只响应以下语句：
- DELETE；
- INSERT；
- UPDATE；
应该保证每个数据库的触发器名唯一；
使用CREATE TRIGGER 创建触发器

INSERT 插入语句可引用一个NEW的虚拟表；

DELETE触发器可以引用OLD的虚拟表；

UPDATE触发器可以引用OLD和NEW虚拟表；

//创建触发器
mysql> CREATE TRIGGER newproduct AFTER INSERT ON products
    -> FOR EACH ROW SELECT 'Product added' //
//删除触发器
DROP TRIGGER newproduct;

chap25-管理事务处理

事务处理保证成批的SQL操作要么完全执行，要么完全不执行；
事务处理：用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果；
一般的SQL语句是针对数据库表执行和编写的，提交操作是自动进行的，在事务处理块中，提交不会隐含地进行；

事务： TRANSACTION

回退：ROLLBACK

提交：COMMIT

保留单：SAVEPOINT

SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

//使用保留点
SAVEPOINT delete1;
ROLLBACK TO delete1;

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

chap27-全球化和本地化

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

//查看支持字符集列表
SHOW CHARACTER SET;
//查看校对
SHOW COLLATION;
...

chap28-安全管理

获取用户列表：
```
USE mysql;
SELECT user FROM user;
```
创建用户账号：CREATE USER ben IDENTIFIED BY '123';
用户名重命名：RENAME USER ben TO bforta；
删除用户： DROP USER ben;
查看访问权限：SHOW GRANTS FOR bforta;
设置权限：GRANT SELECT ON crashcourse.* TO bforta;
更改口令（密码）：SET PASSWORD FOR bforta = Password('12rrre');

chap29-数据库维护

…

chap30-改善性能

…

收录于

数据库

标签

#数据库 #mysql

使用 Visual Studio Code(VS Code) 连接 Mysql 数据库 How to connect MySQL to VS Code 案例背景：最近重拾SQL Cookbook - Anthony Molinaro，就想着配置个Mysql数据库，用VS Code作为编辑器练练手 1、准备工作 1.1 Visual Studio Code 下载安装 VS Code 官网下载链接 1.2 MySQL 8 下载 MySQL 社区版链接对于自己使用的话，就下载MySQL的社区版,下载之后都基本默认安装... 阅读详情