MySQL 速查

MySQL 速查

数据检索

1. SELECT 列1,列2,列3,FROM 表
2. SELECT * FROM 表 检索所有列
3. SELECT DISTINCE 列 FROM 表 不显示重复数据
4. SELECT 列 FROM 表 LIMIT 5 只显示前五行
5. LIMIT 5,6 从行 5 开始的 6 行. 第一个数是位置,第二个数是行数
6. 数据库中第一行是 0 不是 1
7. 完全限定的表名

排序检索数据

1. 字句. 关键字 + 数据
2. ORDER BY 列 按照该列排序显示
3. ORDER BY 列1, 列2 先按列 1 排序再按列 2 排序
4. ORDER BY 列1,列2 DESC 降序排序,但只对 列 2 降序排序.只负责前面的一列
5. 多个列上降序排序,必须对梅格列都指定 DESC 关键字
6. ORDER BY 和 LIMIT 组合,能够显示最高/低的值. 排列后,只显示第一个

过滤数据

1. SELECT 列1 FROM 表 WHERE 列=值; 搜索特定值的行
2. WHERE 字句的操作符 BETWEEN 在两个数值之间,<>或!= 不等于,其余与其他编程语言一样
3. MySQL 执行匹配不区分大小写, WHERE = 'FUSES' 也可检索到 Fuses 所在行
4. 单引号限定特定字符串.数值与串类型进行比较,需要限定引号.与数值比较不需要引号
5. WHERE 列 BETWEEN 5 AND 10;
6. WHERE 列 IS NULL 空值检查

数据过滤

1. 操作符,用来联结或改变 WHERE 子句中的子句的关键字.也称逻辑操作符
2. AND ,给 WHERE 子句添加附加条件
3. OR,指示检索匹配任一条件的行(多条件满足一个就行)
4. 优先处理 AND 操作符,后处理 OR 操作符
5. () 可改变优先级顺序
6. IN 指定条件范围 WHERE 列 IN (值1,值2)
   1. 含义等于 WHERE 列 = 值1 OR 列 = 值2
7. NOT 操作符 ,否定之后跟的任何条件,类似取反

用通配符进行过滤

1. LIKE,使用通配符必须使用 LIKE 操作符
2. 通配符:用来匹配值的一部分的特殊字符
3. 搜索模式:由字面值\通配符或者两者组合构成的搜索条件
4. % 任何字符出现任意次数
   1. WHRER 列 LIKE 'jet%' jet 开头的值
   2. %anvil% 任意包含 %anvil% 的文本
   3. s%e s 开头 e 结尾
5. _ 只匹配单个字符
6. 通配符搜索处理比一般处理要慢
   1. 不要过度使用
   2. 尽量不要再搜索的最开始处使用(这样通配符搜索时,搜索范围就小了)
   3. 注意通配符的位置

正则表达式搜索

1. WHERE 列 REGEXP '1000' 包含文本 1000 的所有行
   1. REGEXP '正则表达式'
   2. LIKE 匹配整个串(列),如果匹配的文本在列值中出现,LIKE 不会找到他,相应的行也不会被返回(除非使用通配符)
   3. 通过 ^ 和$ 可以起到和 LIKE 相同的作用
2. '1000|2000|3000' 表示匹配 1000 或 2000 或 3000
3. [123] 匹配 1 或者 2 或者 3
4. [0-9] 匹配一个范围
5. \\. 匹配 . 特殊字符需要用 \\ 转意
6. 字符类 P58 特定类型的集合,
   1. [:alnum:] 任意字母和数字,等于 [a-zA-Z0-9]
7. 匹配多个实例
   1. * + ? 匹配 0 个或多个 一个或者多个 0 个或 1 个
   2. {n} 指定数目的匹配
   3. {n,} 不少于指定数目的匹配
   4. {n,m} 匹配数目的范围(m 不超过 255)
8. 定位符
   1. ^ 文本 的开始
      1. 也可以在集合中([和])用它来否定该集合
   2. $ 文本的结尾
   3. [[:<:]] 词的开始
   4. [[:>:]] 词的结尾

创建计算字段

1. 字段: 基本上与列（column）的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常用在计算字段的连接上。
2. 拼接（concatenate） 将值联结到一起构成单个值。
   1. 在 MySQL 的 SELECT 语句中，可使用 Concat()函数来拼接两个列。
   2. 多数 DBMS 使用 + 或 || 来实现拼接，MySQL 则使用 Concat()函数来实现。
   3. SELECT Concat(name,'(',country,')')
   4. 上述显示结果 Anvils (USA)
3. 过删除数据右侧多余的空格 RTrim()函数
   1. SELECT Concat(Rtrim(name),'(',Rtrim(country),')')
   2. Anvils (USA)
4. 别名（alias）是一个字段或值的替换名
   1. 别名用 AS 关键字赋予
   2. SELECT Concat(Rtrim(name),'(',Rtrim(country),')') AS title
   3. 指示 SQL 创建一个包含指定计算的名为 title 的计算字段。
5. 算术计算 + - * / 加减乘除
   1. SELECT quantity*item_price AS expanded_price
   2. SELECT Trim('abc')

数据处理函数

1. 文本处理函数
   1. Upper()函数 SELECT vend_name, Upper(vend_name) AS vend_name_upcase 将文本转换为大写
   2. SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。

2. 日期和时间处理函数

   1. 不管是插入或更新表值还是用 WHERE 子句进行过滤，日期必须为格式 yyyy-mm-dd

   2. datetime。这种类型存储日期及时间值。yyyy-mm-dd 00:00:00

   3. Date(order_date)指示 MySQL 仅提取列的日期部分

      SELECT cust_id ,order_num
      FROM orders
      WHERE Date(order_date) = `2005-09-01`
      -- 单行注释：可以防止因不知道具体时间而导致的匹配失败
      # mysql中可以使用的单行注释
       /*
       多行注释
       */
      

   4. 如果你想要的仅是日期，使用 Date()是一个良好的习惯

      1. 匹配某个月所有订单

      SELECT cust_id,oder_num
      FROM orders
      WHERE Date(order_date) BETWEEN `2005-09-01` AND `2005-09-30`
      

   5. WHERE Year(order_date)= 2005 AND Month(order_date) = 9

      1. 检索出 order_date 为 2005 年 9 月的所有行

3. 数值处理函数

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

汇总数据

函数具体使用见 P 77

1. 聚集函数 - 需要汇总数据而不用把它们实际检索出来
   1. 对表中数据（而不是实际数据本身）汇总
   2. 返回实际表数据是对时间和处理资源的一种浪费

2. 聚集不同值 函数(DISTINCT 列名) 忽略相同的数值
3. 组合聚集函数

分组数据

1. GROUP BY 分组数据

   1. 句可以包含任意数目的列。

   2. 在建立分组时，指定的所有列都一起计算

   3. 列出的每个列都必须是检索列或有效的表达式

   4. 如果分组列中具有 NULL 值，则 NULL 将作为一个分组返回。

   5. GROUP BY 子句必须出现在 WHERE 子句之后，ORDER BY 子句之前

   6. 使用 WITH ROLLUP 关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值，

      SELECT vend_id ,COUNT(*) AS num_prods
      FROM products
      GROUP BY vend_id WITH ROLLUP;
      

2. 过滤分组
   1. WHERE 过滤的是行，而不是分组
   2. HAVING，过滤分组。 所有类型的 WHERE 字句都可以用 HAVING 代替
3. 分组和排序
   1. ORDER BY 和 GROUP BY 的区别
   2. 一般在使用 GROUP BY 子句时，应该也给出 ORDER BY 子句。这是保证数据正确排序的唯一方法。千万不要仅依赖 GROUP BY 排序数据。
4. SELECT 子句顺序

使用子查询

同时获取两个表的内容

1. 子查询（subquery），即嵌套在其他查询中的查询

对于 prod——id 为 TNT2 的所有订单物品，检索他的 order——num 列

SELECT order_num
FROM oderitems
WHRER prod_id = 'TNT2';

-- ----查询结果----
-- order_num    --
-- ---------------
-- 200005		--
-- 200007		--
------------------

查询这些 order_num 的 cust_id

SELECT cust_id
FROM oders
WHERE order_nunm IN((200005,20007));

-- ----查询结果----
-- cust_id      --
-- ---------------
-- 10001		--
-- 10004		--
------------------

关键字 IN ((20201228220400-x4e50b8 "Programming/MySQL/CrashCourse#数据过滤"))

把上述两个查询组合为一个查询

SELECT cust_id
FROM oders
WHERE oder_num(
	SELECT order_num
	FROM oderitems
	WHERE prod_id + 'TNT2');

但改代码难以阅读和调试，所以应该有适当的缩进和换行。

2. 作为计算字段使用子查询
   1. 使用了完全限定列名

SELECT cust_name,cust_state,(
	SELECT COUNT(*)
	FROM oders
	WHERE orders.cust_id = customers.cust_id)AS orders
FROM customers
ORDER BY cust_name;

3. 相关子查询 涉及外部查询的子查询。
   1. orders.cust_id = customers.cust_id

联结表

1. 关系表原因
   1. 重复信息既浪费时间又浪费存储空间
   2. 信息发生变化改动一次即可
   3. 如果有重复数据很难保证每次输入该数据的方式都相同
2. 各表通过某些常用的值（即关系设计中的关系（relational））互相关联
3. 外键（foreign key） 外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
4. 可伸缩性（scale） 能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好（scale well）。
5. 联结 结是一种机制，用来在一条 SELECT 语句中关联表，因此称之为联结
6. 创建联结，规定要联结的所有表以及它们如何关联
   1. 指定的列在不同表中
   2. WHERE 语句正确联结

SELECT 列1， 列2 ，列3
FROM 表1 ，表2
WHERE 表1.id = 表2.id
ORDER BY 列1 ， 列2；

7. 笛卡儿积

   1. 由没有联结条件的表关系返回的结果为笛卡儿积。所以 WHERE 语句很重要
   2. 检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
   3. 叉联结

8. 内部联结

   1. 等值联结也成为内部链接
   2. 两个表之间的关系是 FROM 子句的组成部分，以 INNER JOIN 指定。
   3. 传递给 ON 的实际条件与传递给 WHERE 的相同。

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

9. 联结多个表

   SELECT prod_name, vend_name,prod_price,quantity
   FROM orderitems,products,vendors
   WHERE products.vend_id = vendors.vend_id
   	AND oderitems.prod_id = products.prod_id
   	AND order_num =20005
   

创建高级联结

1. 表别名，只能查询中使用

   [[CrashCourse#字段]]别名 AS

   FROM customers AS c ,orders AS o, orderitems AS oi
   

2. 其他三种联结

   1. 自联结

   2. 自然联结

   3. 外部联结

      1. OUTER JOIN

      在使用 OUTER JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定括其所有行的表（RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT 指出的是 OUTER JOIN 左边的表）。
      即从左边表拥有的行中,去挑出符合条件的行(右边的表可能范围小于左边表)

3. 使用带聚集函数的联结[[#汇总数据]]

4. 使用联结和联结条件

组合查询

• UNION 将多个 SELECT 语句的结果组合成单个结果集
• 使用情形
  • 在单个查询中从不同的表返回类似结构的数据
  • 对单个表执行多个查询，按单个查询返回数据
• 给出每条 SELECT 语句，在各条语句之间放上关键字 UNION
• UNION 规则
  • 由两条或两条以上的 SELECT 语句组成
  • UNION 中的每个查询必须包含相同的列、表达式或聚集函数
  • 列数据类型必须兼容
• 包含或取消重复的行
  • UNION 从查询结果集中自动去除了重复的行
  • 如果想返回所有匹配行，可使用 UNION ALL 而不是 UNION
• 对组合查询结果排序
  • 只能使用一条 ORDER BY 子句，必须出现在最后一条 SELECT 语句之后

全文本搜索

支持全文本搜索的特定数据库类型:"创建和操纵表"

"用通配符进行过滤"

,"正则表达式搜索"

的限制

• 性能
• 需要明确控制
• 没有智能化结果

使用方法

• 启用
  • 创建表时接受 FULLTEXT 子句 CREATE TABLE t1(... FULLTEXT(note_text) )
    • note_text 代表列
  • 不要在导入数据时使用 FULLTEXT
• 必须索引被搜索的列，而且要随着数据的改变不断地重新索引
• 索引之后，SELECT 可与 Match()和 Against()一起使用以实际执行
  • Match() 指定被搜索的列,其值必须与 FULLTEXT()中定义的相同
  • Against() 指定要使用搜索的表达式
• 除非使用 BINARY 方式（本章中没有介绍），否则全文本搜索不区分大小写。
• 全文搜索可以直接对搜索结果排序,优先返回具有较高等级的行

查询拓展

放宽所返回的全文本搜索结果的范围。

• 进行一个基本的全文本搜索
• 检查这些匹配行并选择所有有用的词
• 再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词

SELECT ....
WHERE Match(note_text) Against(`anvils` WITH QUERY EXPANSION)

布尔文本搜索

• 要匹配的词；
• 要排斥的词（如果某行包含这个词，则不返回该行，即使它包含其他指定的词也是如此）；
• 排列提示（指定某些词比其他词更重要，更重要的词等级更高）；
• 表达式分组；
• 另外一些内容

该搜索方式在 FULLTEXT 索引的情况下也可以使用. 是一种非常缓慢的操作.

需要指定布尔操作符,否则结果与默认搜索方式结果一致

# 匹配包含heavy但不包含任意以rope开始的词的行
SELECT ....
WHERE Match(note_text) Against(`heavy -rope*` IN BOOLEAN MODE)

布尔操作符

注意

• 短词被忽略且从索引中排除,(3 个或 3 个以下字符的词)
• 如果一个词出现在 50% 以上，则将它作为一个非用词忽略。(Boolean 模式除外)
• 表中的行数少于 3 行，则全文本搜索不返回结果
• 忽略词中的单引号。don't 索引为 dont。
• 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果。
• 仅在 MyISAM 数据库引擎中支持全文本搜索。