MySQL必知必会手记——查看的基本知识

查看基本知识

第七章 数据信息过虑

组成where子句

MySQL容许得出好几个WHERE子句。这种子 句能够二种方法应用:以AND子句的方法或OR子句的方法应用。

AND运算符

可应用AND运算符给WHERE子句附带条件

-- 查找由1003生产制造且价钱不大于20美元的全部商品的名字和价钱
SELECT prod_id, prod_price, prod_name FROM products
WHERE vend_id = 1003 AND prod_price <= 10;

OR运算符

OR用于表明查找配对任一给出标准的行。

-- 查找由1002和1003生产制造的商品的名字和价钱
SELECT prod_name, prod_price FROM products
WHERE vend_id = 1002 or vend_id = 1003;

测算顺序

WHERE可包括随意数量的AND和OR运算符。容许二者融合以开展繁杂 和高級的过虑。

AND的优先高过OR

-- 列举价钱为20美元(含)之上且由1002或1003生产制造的全部商品
SELECT prod_name, prod_price FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;

可以用括弧括起來优先选择让某一部分先测算。

IN运算符

用于特定标准范畴,取合理合法值的由分号隔开的明细所有在圆括号中。

-- 查找经销商1002和1003生产制造的全部商品。
SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003)
ORDER BY prod_name;

以上句子中WHERE vend_id IN (1002, 1003)相当于WHERE vend_id=1002 OR vend_id=1003)

IN比OR实行更快,较大 的优势是能够包括别的SELECT句子,可以更动态性地创建WHERE子句。

  • 在应用长的合理合法选择项明细时,IN运算符的英语的语法更清晰且更形象化。
  • 在应用IN时,测算的顺序更非常容易管理方法(由于应用的运算符更少)。
  • IN运算符一般比OR运算符明细实行更快。
  • IN的较大 优势是能够包括别的SELECT句子,促使可以更动态性地建 立WHERE子句。

NOT运算符

-- 列举除1002,1003以外全部经销商供货的商品
SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003)
ORDER BY prod_name;

用使用通配符开展过虑

使用通配符(wildcard) 用于配对值的一部分的特殊符号。

LIKE运算符

LIKE标示MYSQL,后跟的检索方式运用使用通配符配对而不是立即相同配对开展较为。

百分号(%)使用通配符

表明一切标识符发生随意频次

-- 例:找到全部jet开始的商品
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';
-- 例:应用好几个使用通配符,配对一切部位包括anvil的值,无论它以前或以后发生哪些标识符
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%';
-- 例:找到s开始e末尾的全部商品
SELECT prod_name FROM products WHERE prod_name LIKE 's%e';

尽管好像%使用通配符能够配对任何东西,但有一个除外,即NULL。即便是WHERE prod_name LIKE '%'也不可以配对用值NULL做为产品名字的行。

下横线(_)使用通配符

-- 只配对单独标识符而不是好几个标识符
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';

使用通配符的方法

  • 不必过多使用通配符,假如别的运算符可以做到目地应当应用别的运算符
  • 在的确必须使用通配符时,除非是肯定必须,不然不必把他们用在检索的逐渐处。 把使用通配符放置检索方式的逐渐处检索起來是比较慢的。
  • 细心留意使用通配符的部位

第九章 用正则表达式开展检索

应用MySQL正则表达式

基本上标识符配对

MySQL 中应用 REGEXP 关键词特定正则表达式的标识符模式匹配

-- 例:查找prod_name包括文字1000的全部行
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和REGEXP的差别: LIKE '1000'配对全部列值,相当于'1000'时才会回到相对应行,而REGEXP '1000'在列值内开展配对,假如包括'1000'则会回到相对应行。

换句话说,LIKE 配对全部列,假如被配对的文字在列值中发生,LIKE 将不容易寻找它,相对应的行也不会被回到(除非是使用通配符)。而 REGEXP 在列值内开展配对,假如被配对的文字在列值中发生,REGEXP 可能寻找它,相对应的已然被回到,而且 REGEXP 能配对全部列值(与 LIKE 同样的功效)。

看个具体事例就行了解了。

它是orders表:

image-20210608205727457

大家应用like来查看:

select * from orders where cust_id like "100";

其結果以下,为空:

image-20210608213851428

应用regexp试验:

select * from orders where cust_id REGEXP "100";

其結果以下:

image-20210608213822110

MySQL 的正则表达式配对(自3.23.4版本号后)不区别英文大小写(即英文大写和小写字母都配对)。为区别英文大小写,能够应用 BINARY 关键词

WHERE prod_name REGEXP BINARY 'JetPack .000';

开展OR配对

|为正则表达式的OR运算符,表明配对在其中之一

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

能够得出2个之上的OR标准1000|2000|3000

配对好多个标识符之一

[]表明配对[]中的一切单一标识符,能够了解为另一种方式的OR句子。

此外,[123][1|2|3]的简称

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

-- 結果

 ------------- 
| prod_name   |
 ------------- 
|1 ton anvil  |
|2 ton anvil  |
 ------------- 

还记得一定要加方括号,下列不是加方括号的結果

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

-- 結果

 ------------- 
| prod_name   |
 ------------- 
|1 ton anvil  |
|2 ton anvil  |
|JetPack 1000 |
|JetPack 2000 |
|TNT (1 stick)|
 ------------- 

不用方括号它配对的含意是1 OR 2 OR 3 Ton,因此 还会继续查找出JetPack 1000等不符合规定的行。

标识符结合还可以被否认,为否认一个字集,在结合的逐渐处置放^,比如[^123]配对除这种标识符的任何东西

配对范畴

配对0到9,可以用[0123456789],为了更好地简单化,可以用-来界定范畴,能够写出[0-9]。同样,a到z的范畴就可以写出[a-z]

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

-- 結果

 ------------- 
|  prod_name  |
 ------------- 
| .5 ton anvil|
| 1 ton anvil |
| 2 ton anvil |
 ------------- 

配对特殊符号

特殊符号,例如大家前文写到的

  • .:配对任一标识符
  • []:配对好多个标识符中是某一标识符
  • -:特定范畴

如下边的事例:

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

-- 結果

 --------------- 
|   vend_name   |
 --------------- 
| ACME          |
| Anvils R Us   |
| Furball Inc.  |
| Jet Set       |
| Jouets Et Ours|
| LT Supplies   |
 --------------- 

由于'.'为配对随意标识符,因此 配对的結果并不是大家要想的結果。

假如只想要配对带.的結果,务必用\\为流板。同样,配对别的特殊符号还要用\\为流板.

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

-- 結果

 --------------- 
|   vend_name   |
 --------------- 
| Furball Inc.  |
 --------------- 

正则表达式中具备独特实际意义的全部标识符都需要根据这类方法转义 \\也用于引入元字符

元字符 表明
\\f 换页
\\n 自动换行
\\r 回车键
\\t 造表
\\v 竖向造表
为了更好地配对\自身,必须应用\\\

配对标识符类

表明
[:alnum:] 随意英文字母和数据(同[a-zA-Z0-9])
[:alpha:] 随意标识符(同[a-zA-Z])
[:cntrl:] 空格符和造表(同[\t])
[:digit:] ASCII控制字符(ASCII)0到31和127
[:graph:] 随意数据(同[0-9])
[:lower:] 随意英文字母(同[a-z])
[:print:] 随意可打印出标识符
[:punct:] 既没有[:alnum:]又没有[:cntrl:]中的随意标识符
[:space:] 包含空格符以内的随意空白字符(同[\f\n\r\t\v])
[:upper:] 随意英文大写字母(同[A-Z])
[:xdigit:] 随意十六进制数据(同[a-fA-F0-9])

配对好几个案例

元字符 表明
* 0个或好几个配对
一个或好几个配对(相当于{1,})
0个或一个配对(相当于{0,1})
{n} 特定数量的配对
{n,} 不少于特定数量的配对
{n.m} 配对数量的范畴(m不超过255)

例:

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

-- 結果

 --------------- 
|   prod_name   |
 --------------- 
| TNT (1 stick) |
| TNT (5 sticks)|
 --------------- 

表明:

  • \\(表明配对左括弧
  • [0-9]表明配对0到9的随意数据
  • stick?配对'stick'和'sticks'
  • \\)表明配对右括弧

例:配对连在一起的4位数据

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

-- 結果

 --------------- 
|   prod_name   |
 --------------- 
| JetPack 1000  |
| JetPack 2000  |
 --------------- 
-- 还可以写出 '[0-9][0-9][0-9][0-9]'

精准定位符

元字符 表明
^ 文字的逐渐
$ 文字的末尾
[:<:] 词的逐渐
[:>:] 词的末尾
例:找到以一个数(包含小数位开始)逐渐的全部商品
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name;

-- 結果

 --------------- 
|   prod_name   |
 --------------- 
| .5 ton anvil  |
| 1 ton anvil   |
| 2 ton anvil   |
 --------------- 

第十章 建立测算字段名

拼凑字段名

拼凑:将值联接到一起组成单独值

在SELECT句子中,可应用Concat()涵数来拼凑2个列。Concat()涵数必须一个或好几个特定的串,每个串中间用分号隔开。

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

-- 結果

 ----------------------------------------- 
| Concat(vendname,' (',vend_country,')') |
 ----------------------------------------- 
| ACME (USA)                              |
| Anvils R Us (USA)                       |
| Furball Inc. (USA)                      |
| Jet Set (England)                       |
| Jouets Et Ours (France)                 |
| LT Supplies (USA)                       |
 ----------------------------------------- 

删除字段不必要空格符

涵数 表明
Trim() 除掉两侧的空格符
LTrim() 除掉左侧的空格符
RTrim() 除掉右侧的空格符

实例:应用 RTrim()涵数删掉右边不必要的空格符。

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;

-- 結果

 ---------------------------- 
| vend_title                 |
 ---------------------------- 
| ACME (USA)                 |
| Anvils R Us (USA)          |
| Furball Inc. (USA)         |
| Jet Set (England)          |
| Jouets Et Ours (France)    |
| LT Supplies (USA)          |
 ---------------------------- 

实行算数测算

-- 归纳物件的价钱(价格乘于购买总数)
SELECT prod_id,
	   quantity,
       item_price,
       quantity * item_price AS expand_price
FROM orderitems
WHERE order_num = 20005;

-- 結果

 --------- ---------- ------------ ---------------- 
| prod_id | quantity | item_price | expanded_price |
 --------- ---------- ------------ ---------------- 
| ANV01   |       10 |       5.99 |          59.90 |
| ANV02   |        3 |       9.99 |          29.97 |
| TNT2    |        5 |      10.00 |          50.00 |
| FB      |        1 |      10.00 |          10.00 |
 --------- ---------- ------------ ---------------- 

如上编码的第四行中发生了quantity * item_price,我们可以对查找出的数据信息开展算数测算,常见的运算符以下:

运算符 表明
-
*
/

第十一章 应用数据处理方法涵数

文本检索涵数

常见的文本检索涵数

涵数 表明
Left() 回到串左侧的标识符
Length() 回到串的长短
Locate() 找到串的一个子串
Lower() 将串变换为小写字母
LTrim() 除掉串左侧的空格符
Right() 回到串右侧的标识符
RTrim() 除掉串右侧的空格符
Soundex() 回到串的SOUNDEX值
SubString() 回到子串的标识符
Upper() 将串变换为英文大写
-- 实例:将文字变换为英文大写
SELECT vend_name, Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;

以上中Soundex()的补充说明:

SOUNDEX是一个将一切文字变换为叙述其视频语音表明的英文字母数据方式的优化算法,促使能对串开展音标发音较为而不是英文字母较为。MySQL给予对SOUNDEX的适用。

看一下下边的事例

表格中有一个客户的名称为Y.Lee,根据select查看手机联系人时键入不正确为Y.Lie。这时Y.Lee这一行时不容易被查找出去的,但应用SOUNDEX查找,能够配对音标发音类似Y.Lie的联络名:

SELECT cust_name, cust_contact FROM customers
WHERE Soundex(cust_contact)= Soundex('Y Lie');

-- 結果

 ------------- -------------- 
| cust_name   | cust_contact |
 ------------- -------------- 
| Coyote Inc. | Y Lee        |
 ------------- -------------- 

日期和時间处理函数

涵数 表明
AddDate() 提升一个日期(天、周等)
AddTime() 提升一个時间(时、分等)
CurDate() 回到当今日期
CurTime() 回到获取当前时间
Date() 回到日期時间的日期一部分
DateDiff() 测算2个日期之差
Date_Add() 高宽比灵便的日期测算涵数
Date_Format() 回到一个恢复出厂设置的日期或時间串
Day() 回到一个日期的日数一部分
DayOfWeek() 针对一个日期,回到相匹配的星期几
Hour() 回到一个時间的钟头一部分
Minute() 回到一个時间的分鐘一部分
Month() 回到一个日期的月份一部分
Now() 回到当今日期和時间
Second() 回到一个時间的秒一部分
Time() 回到一个日期時间的时间部分
Year() 回到一个日期的年代一部分

应用日期文件格式的留意点:

  • 日期务必为文件格式yyyy-mm-dd

  • 有关datetime

    • SELECT cust_id, order_num FROM orders
      WHERE order_date = '2021-01-02';
      
    • sql语句中的order_date种类为datetime,它具备時间值00:00:00。转化成時间数据信息时,例如转化成"2021-01-02",转化成的数据信息除开年月日还会继续自动生成分秒,默认设置是00:00:00。

    • image-20210611162750885

    • 这时,假如你查找的数值2021-01-02 14:06:29,则上边的WHERE order_date = '2021-01-02'不容易查找出这一行

    • 要应用Date()涵数,才可以寻找数值2021-01-02 14:06:29的行

    • SELECT cust_id, order_num FROM orders
      WHERE Date(order_date) = '2021-01-02';
      

实例:查找出2005年9月下的全部订单信息

SELECT cust_id, order_num FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

或是

SELECT cust_id, order_num FROM orders
WHERE Year(roder_date) = 2005 AND Month(order_date) = 9;

标值处理函数

涵数 表明
Abs() 回到一个数的平方根
Cos() 回到一个视角的余弦
Exp() 回到一个数的指数
Mod() 回到除实际操作的余数
Pi() 回到圆周率
Rand() 回到一个随机数字
Sin() 回到一个视角的正弦函数
Sqrt() 回到一个数的平方根
Tan() 回到一个视角的正切值

第十二章 归纳涵数

集聚涵数

集聚涵数(aggregate function):运作内行组下,测算和回到单独值的涵数。

涵数 表明
AVG() 回到某列的均值
COUNT() 回到某列的个数
MAX() 回到某列的最高值
MIN() 回到某列的极小值
SUM() 回到某列值之和

VG()涵数

例:回到products表格中全部商品的均价

SELECT AVG(prod_price) AS avg_price FROM products;

例:回到特殊经销商所给予商品的均价

SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

COUNT()涵数

例:回到customer表格中顾客的数量

SELECT COUNT(*) AS num_cust FROM customers;

例:只对具备电子邮箱详细地址的顾客记数

SELECT COUNT(cust_email) AS num_cust
FROM customers;

MAX()涵数

例:回到products表格中较贵的物件价钱

SELECT MAX(prod_price) AS max_price
FROM products;

对非数值数据应用MAX() MySQL容许将它用于回到随意列中的最高值,包含回到文字列中的最高值。在用以文字数据信息时,假如数据信息按相对应的列排列,则MAX()回到最终一行。MAX()涵数忽视列值为NULL的行。

MIN()涵数

例:

SELECT MIN(prod_price) AS min_price FROM products;

SUM()涵数

回到特定列值的和(累计) 例:查找所购买物件的数量

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

例:累计测算值,累计每一项物件item_price*quantity,得到订单信息总额

SELECT SUM(item_price*quantity) AS total_price
FORM orderitems
WHERE order_num = 20005;

集聚不一样值(适用5后的版本号)

以上五个集聚涵数都能够以下应用:

  • 对全部的行实行测算,特定ALL主要参数或不给主要参数(ALL为默认设置)
  • 只包括不一样的值,特定DISTINCT主要参数
-- 物件的均价
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

假如特定字段名,则DISTINCT只有用以COUNT()。DISTINCT不可以用以COUNT(*),因而不允许应用COUNT(DISTINCT), 不然会造成不正确。相近地,DISTINCT务必应用字段名,不可以用以测算或关系式。

组成集聚涵数

SELECT句子可依据必须包括好几个集聚涵数

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;

-- 結果

 ----------- ----------- ----------- ----------- 
| num_items | price_min | price_max | price_avg |
 ----------- ----------- ----------- ----------- 
|        14 |      2.50 |     55.50 | 16.133571 |
 ----------- ----------- ----------- ----------- 

第十三章 排序数据信息

数据信息排序

排序容许把数据信息分成好几个逻辑性组,便于能对每一个组开展集聚测算。

建立排序

例:依据vend_id排序,对每一个排序各自测算数量

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

-- 結果

 --------- ----------- 
| vend_id | num_prods |
 --------- ----------- 
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
 --------- ----------- 

结果显示vend_id为1001有3个,vend_id为1002有两个,vend_id为1003有七个,vend_id为1005有两个。

在实际应用GROUP BY子句前,必须了解一些关键的要求。

  • GROUP BY 子句能够包括随意数量的列,促使能对排序开展嵌入,为数据信息排序给予更细腻的操纵
  • 假如GROUP BY子句中嵌入了排序,数据信息将在最终要求的排序上开展归纳。也就是说,在创建排序时,特定的全部列都一起测算(因此 不可以从某些的列取回来数据信息)。
  • GROUP BY子句中列举的每一个列都务必是查找列或合理的关系式(但不可以是集聚涵数)。假如在SELECT中应用关系式,则务必在GROUP BY子句中特定同样的关系式。不可以应用别称。
  • 除集聚测算句子外,SELECT句子中的每一个列都务必在GROUP BY子句中得出。
  • 假如排序列中具备NULL值,则NULL将做为一个排序回到。假如列中有几行NULL值,他们将分成一组。
  • GROUP BY子句务必发生在WHERE子句以后,ORDER BY子句以前。

过虑排序

WHERE特定的是行,并不是排序,WHERE沒有排序的定义

应用HAVING过虑排序

SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

-- 結果

 --------- -------- 
| cust_id | orders |
 --------- -------- 
|   10001 |      2 |
 --------- -------- 

WHERE失灵,由于过虑是根据排序集聚值而不是特殊行值的。

-- 列举具备两个(含)之上、价钱为10(含)之上的商品的经销商
SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >=2

-- 結果

 --------- ----------- 
| vend_id | num_prods |
 --------- ----------- 
|    1003 |         4 |
|    1005 |         2 |
 --------- ----------- 

排序和排列

尽管GROUP BY和ORDER BY常常进行同样的工作中,但他们是十分不一样的。

ORDER BY与GROUP BY的差别:

ORDER BY GROUP BY
排列造成的輸出 排序行。但輸出很有可能并不是排序的次序
随意列都能够应用(乃至 非挑选的列还可以应用) 只很有可能应用挑选列或关系式列,并且务必应用每一个挑选 列关系式
不一定必须 假如与集聚涵数一起应用列(或关系式),则务必应用

一般在应用GROUP BY子句时,应当也得出ORDER BY子句。它是确保数据恰当排列的唯一方式 。千万别仅依靠GROUP BY排列数据信息。

-- 查找累计订单信息价钱高于或等于50的订单信息的订单编号和累计订单信息价钱
SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50
ORDER BY ordertital;

SELECT子句次序

SELECT子句以及次序

子句 表明 是不是务必应用
SELECT 要回到的列或关系式
WHERE 从这当中查找数据信息的表 仅在从表挑选数据信息时应用
GROUP BY 排序表明 尽在按组测算集聚是应用
HAVING 组级过虑
ORDER BY 輸出排列次序
LIMIT 要查找的个数

以上子句应用时务必遵照该次序

第十四章 应用子查询

SELECT order_num FROM orderitems
WHERE prod_id = 'TNT2';

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

能够应用以下方法

SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

再加一个标准:

SELECT clust_name. cust_contact FROM customers WHERE cust_id IN (10001, 10004)

合拼为一句sql

SELECT cust_name, cust_contact FROM customers
WHERE cust_id IN(SELECT cust_id FROM orders
				 WHERE order_name IN(SELECT order_num FROM orderitems
                 					 WHERE prod_id ='TNT2'));
  • 在WHERE子句中应用子查询应确保SELECT句子有与WHERE子句中同样数量的列。
  • 这儿得出的编码合理并得到需要的結果。可是,应用子查询并不一直实行这类种类的文本检索的最有效的方式 。(即那样写实行的特性不一定最好是)

做为测算字段名应用子查询

# 对顾客10001的订单信息开展记数
SELECT COUNT (*) AS orders FROM orders WHERE cust_id = 10001;
# 为了更好地对每一个顾客实行COUNT(*)测算,应当将COUNT(*)做为一个子查询
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders
                              WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers ORDER BY cust_name;

评论(0条)

龙8唯一官方网站 游客评论