MySQL必知必会 笔记 01-10

使用前提: 填充 MySQL必知必会里面的用例, 需要下载2个SQL脚本文件。

书籍链接：http://www.forta.com/books/0672327120/

SQL 脚本文件：https://forta.com/wp-content/uploads/books/0672327120/mysql_scripts.zip

下载脚本之后，开始创建和填充书中各章所用的表：

（1）创建一个新的数据源

        CREATE DATABASE crashcourse; （这里使用书中的数据源名称）

（2）选择新的数据源

        USE crashcourse;

（3）执行creat.sql脚本

        SOURCE d:/databasedownload/create.sql;   （使用mysql命令行使用程序的方法，source+文件的完全路径）

（4）用populate.sql文件填充各个新表

         SOURCE d:/databasedownload/populate.sql; 

如果用 Navicat 的话，可以新建数据库后，选中 表 右键 运行 SQL 文件 ， 把下载好的两个表导入

3 了解数据库

选择 数据库(选择 crashcourse 数据库)

USE crashcourse;

SHOW 语句

用 SHOW 查看MySQL数据库、表和内部信息

显示 可用数据库 的列表

SHOW DATABASES;

显示一个数据库内的表的列表(这个数据库是上面已经选择好的 crashcourse 数据库)

SHOW TABLES;

显示指定表名的表列(显示 customers 表的信息)

# 默认
SHOW COLUMNS FROM customers;
# 使用 DESCRIBE 语句, 是 SHOW COLUMNS FROM customers; 的快捷方式
DESCRIBE customers;

其他 SHOW 语句

SHOW STATUS;  # 显示广泛的服务器状态信息
SHOW CREATE DATABASE name;  # 显示创建特定数据库
SHOW CREATE TABLE name;  # 显示创建特定的表
SHOW GRANTS FOR user;  # 显示授予用户 user 安全权限
SHOW ERRORS;  # 显示服务器错误
SHOW WARNINGS;  # 显示服务器警告

4. 检索数据

SELECT 检索语句

检索单个列(SELECT + FROM, 从 products 表中检索一个名为 prod_name 的列)

SELECT prod_name
FROM products;

检索多个列(从 products 表中检索 3 列)

SELECT prod_id, prod_name, prod_price
FROM products;

检索所有的列(使用通配符 * , 从 products 表中检索所有的列)

SELECT *
FROM products;

DISTINCT 去重

检索一个列, 并去重, 返回唯一不同的值(应用于所有列而不仅是前置它的列)

SELECT DISTINCT vend_id
FROM products;

LIMIT 限制结果

检索单个列, 并返回不多于 5 行

SELECT prod_name
FROM products
LIMIT 5;  # LIMIT 0, 5;

检索单个列, 并返回 行5 开始的 5行

SELECT prod_name
FROM products
LIMIT 5, 5;

完全限定表名

使用 表名.列字 来 完全限定列 (绝对路径)
使用 数据库名.表名 来 完全限定表 (绝对路径)

完全限定 列 , 之前都是通过列名引用列的(相对路径), 这里可以完全限定名字来引用列(绝对路径)

SELECT products.prod_name
FROM procucts;

完全限定 列 和 表

SELECT products.prod_name
FROM crashcourse.products;

5. 排序检索数据

检索出来的数据, 默认是数据添加到表中的数据, 可以进行排序

ORDER BY 排序子句

检索数据的同时使用 ORDER BY 进行排序, 排序规则为字母顺序

SELECT prod_name
FROM products
ORDER BY prod_name;

多个列排序, 检索3个列,排序其中的两个列, 先价格, 后名称

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

指定排序方向

使用 DESC 降序

默认是升序排列的(从小到大, 从A到Z), 若需要降序则需要指定 DESC 关键字, DESC 关键字只应用到位于其前面的列名

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

多个排序, 先给产品价格降序, 然后给产品名默认排序

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

涉及到数据库的设置, 比如改变排序顺序, 视 a 与 A 不同, a 在 A 的之前或之后, 使用 ORDER BY 子句无法做到, 需要请求数据库管理员帮助

使用ORDER BY + LIMIT, 找出列中最高或最低的值

SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;

过滤数据

都检索出来的数据进行过滤, 即指定搜索条件

使用 WHERE 子句

使用 = , 指定搜索条件为数字 prod_price=2.50

SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.50;

WHERE 子句操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于

| 大于
= | 大于等于
BETWEEN | 在指定的两个值之间

使用 = , 指定搜索条件为字符串 prod_name='fuses'

SELECT prod_name, prod_price
FROM products
WHERE prod_name = 'fuses';

使用 < , 指定搜索条件为 小于10美元的产品

SELECT prod_name, prod_price
FROM products
WHERE prod_price < 10;

使用 <> 或 != , 指定搜索条件为 不是供应商1003制造的所以产品

SELECT vend_id, prod_name
FROM products
WHERE vend_id <> 1003;
# WHERE vend_id != 1003;

使用 BETWEEN , 指定搜索条件为 介于 5 美元和 10 美元之间的产品

SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;

使用 IS NULL, 指定搜索条件为 cust_email 为空的 cust_id

SELECT cust_id
FROM customers
WHERE cust_email IS NULL

数据过滤

WHERE 初步过滤, 可以组合 WHERE 建立高级搜索条件

使用 AND 结合 WHERE 子句

使用 且 AND 结合搜索条件, 指定搜索条件为 vend_id=1003 和 prod_price<=10

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

使用 OR 结合 WHERE 子句

使用 或 OR 结合搜索条件, 指定搜索条件为 vend_id=1002 或 vend_id=1003

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

使用 OR + AND 结合 WHERE 子句

组合 OR 和 AND , 指定搜索条件为 vend_id = 1002 OR vend_id = 1003 AND prod_prive >= 10

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

但是因为 AND 在计算次序中优先级最高, 所以预期结果会有所不同, 可以使用() 避免歧义

SELECT prod_name, prod_price
FROM products
WHERE (vend_id = 1002 OR vned_id = 1003) AND prod_price >= 10;

IN 操作符

IN 操作符用来指定条件范围, 需要结合 () 使用, 范围内的每个条件都可以进行匹配

使用 IN , 搜索条件为 (1002, 1003), 且指定列 prod_name 进行排序

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

IN 的搜索结果与 'OR' 类似,可以使用 OR进行重写, 但是 IN 更加清晰直观

使用 'OR', 完成上面的搜索

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

NOT 操作符

NOT 操作符是否定它之后所跟的任何条件

使用 NOT, 搜索条件为 NOT IN (1002, 1003), 且指定列 prod_name 进行排序

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

使用通配符进行过滤

使用通配符对数据进行复杂过滤

LIKE 操作符

搜索模糊数据, 需要使用通配符进行匹配, 虽然简便, 但这是牺牲了性能为代价的

技术上说, LIKE 是谓语, 而不是操作符

百分号 % 通配符

% 通配符 表示任何字符(除了 NULL)出现任意次数, 相当于正则的贪婪匹配 .*

使用通配符 %, 搜索条件为 prod_name LIKE 'jet%'

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

使用多个通配符, 搜索条件为 prod_name LIKE '%anvil%'

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

下划线 _ 通配符

_ 通配符 表示匹配单个字符, 而不是多个字符, 相当于正则的.

使用通配符 _, 搜索条件为 prod_name LIKE '_ ton anvil'

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

使用正则表达式进行搜索

之前的 WHERE 子句 使用匹配, 比较, 通配操作符进行基本的数据过滤, 但还不够强大, 因为实际生产中需要进行复杂的数据过滤

REGEXP, 使用 MySQL 正则表达式

使用正则表达式 REGEXP, 搜索条件为 prod_name REGEXP '1000', 且指定列 prod_name 进行排序

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

使用正则表达式 REGEXP, 搜索条件为 prod_name REGEXP '.000', 且指定列 prod_name 进行排序

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

LIKE 和 REGEXP 区别

SELECT prod_name
FROM products
WHERE prod_name LIKE '1000'
ORDER BY prod_name;
# LIKE 不返回数据

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;
# REGEXP 返回一个数据

因为 LIKE 是匹配整个列值的, 只有列值完全相同才可以返回数据(除非使用通配符);

而REGEXP 也是匹配整个列值的, 但是列值部分相同就会返回数据(匹配整个列值需使用^和$定位符)

MySQL 的正则表达式匹配是默认不区分大小写, 可以使用 BINARY 关键字区分区分大小写

使用 BINARY 关键字, 区分大小写, 搜索条件为REGEXP BINARY 'JetPack .000'

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

使用'|' 进行 OR 匹配

使用'|' 进行 OR 匹配, 搜索条件为 '1000|2000'

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

使用 '[]' 匹配几个字符之一

使用 '[]' 匹配几个字符之一, 搜索条件为 REGEXP '[123] Ton'

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

等价于WHERE prod_name REGEXP '[1|2|3]Ton'

但不带括号的话 '1|2|3 Ton' 却不是所期望的,因为会匹配 '1' 或 '2' 或 '3 ton'

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

使用 - 匹配范围

[0-9] 等价于 [0123456789]

所以 [a-z] 会匹配任意字母字符

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name;

这里没有使用定位符, 所以会匹配到 .5 ton

匹配特殊字符 .、 |等

因为特殊字符 .、 |, 默认有其他功能, 需要使用转义符// 转义

一般正则表达式实现单个反斜杠转义特殊字符, 但是 MySQL 需要两个(MySQL 自己解释一个, 正则表达式库解释另一个)

元字符	说明
\\f	换页
\\n	换行
\\r	回车
\\t	制表
\\v	纵向制表

匹配字符类

类	说明
[:alnum:]	任意字母和数字
[:alpha:]	任意字符（同[a-zA-Z]）
[:blank:]	空格和制表（同[\t]）
[:cntrl:]	ASCII控制字符（ASCII 0到31和127）
[:digit:]	任意数字（同[0-9]）
[:graph:]	与[:print:]相同，但不包括空格
[:lower:]	任意小写字母（同[a-z]）
[:print:]	任意可打印字符
[:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]	包括空格在内的任意空白字符（同[\f\n\r\t\v]）
[:upper:]	任意大写字母（同[A-Z]）
[:xdigit:]	任意十六进制数字（同[a-fA-F0-9]）

使用重复元字符匹配多个实例

元字符	说明

• | 0个或多个匹配

• | 1个或多个匹配（等于{1,}）
  ? | 0个或1个匹配（等于{0,1}）
  {n} | 指定数目的匹配
  {n,} | 不少于指定数目的匹配
  {n,m} | 匹配数目的范围（m不超过255）

例子 1 :

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

解析: \\([0-9] sticks?\\)

\\(转义(
[0-9] 匹配任意数字
sticks? 匹配 stick 和 sticks (? 匹配前面的字符 0 次 或 1 次)
\\)转义)

例子 2 :

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

解析: [[:digit:]]{4} 任意 4 位数字

[:digit:] 匹配任意数字
[ ] 匹配一个集合
{4} 匹配前面的字符, 即任意数字出现 4 次

定位符

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

搜索一个数(包括以小数点开始的数)开始的所有产品

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

^[0-9\\.]只在.或任意数字为串中第一个字符时才匹配它们

创建计算字段

计算字段是运行时在SELECT语句内创建的。

使用 Concat() 函数拼接字段

拼接两个列

使用 Concat() 函数拼接字段, 拼接条件为 vend_name + ( + vend_country + ), 效果为 ACME(USA)

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

但是有个缺陷, 默认的 title 是 Concat(Vend_name, '(', vend_country, ')') 需要更改为其他 title 名

使用 AS 别名

使用 AS 别名代替原先的 title

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

执行算术计算

检索订单号 20005 中的所有物品信息

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

quantity 是数量, item_price 是单价, 可以算出总价并跟其他列一样显示

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

操作符	说明

• | 加

• | 减

• | 乘
  / | 除