Mysql必知必会重点整理（4-10章）

2019-04-15 本文已影响0人 jiajia_W

chap4 检索数据

4.SELECT语句

4.1 检索单个列

SELECT prod_name FROM products;

注：养成习惯，对SQL关键字用大写，对所有的列名和表名用小写

4.2 检索多个列

在SELECT关键字后给出多个列，列名之间以逗号分隔

SELECT prod_id,prod_name,prod_price FROM products;

4.3 检索所有列

在实际列名位置使用*号来达到

SELECT * FROM products;

4.4 检索不同的行

DISTINCT关键字指示MySQL只返回不同的值

SELECT DISTINCT vend_id FROM products;

4.5 限制结果

SELECT 语句返回所有匹配的行，为了返回第一行或前几行，可使用LIMIT子句

SELECT prod_name FROM products LIMIT 5;

为得到下一个5行，可使用如下语句：

SELECT prod_name FROM products LIMIT 5,5;

以上语句表示从第6行开始的下5行

**LIMIT 3,4 等同于 LIMIT 4 OFFSET 3**

4.6 使用完全限定的表名

SELECT products.prod_name FROM products;

表名也可以是完全限定的，如下所示：

SELECT products.prod_name FROM `mysql必知必会`.products;

chap 5 排序检索数据

如何使用SELECT语句的ORDER BY句子，根据需要排序检索出数据
ORDER BY 子句取一个或多个列的名字，据此对输出进行排序

5.1 排序数据

SELECT products.prod_name FROM products ORDER BY prod_name;

5.2 按多个列排序

为了按多个列排序，只要指定列名，列名之间用逗号分开即可

SELECT products.prod_id,products.prod_price,products.prod_name FROM products ORDER BY prod_price,prod_name;       **首先按价格，其次按名称排序**

在按多个列排序时，排序完全按规定的顺序进行，如上文，仅在多个行具有相同的prod_price值时才对产品按prod_name进行排序，如果prod_price值都是唯一的，则不会按prod_name进行排序

5.3 指定排序方向

5.3.1 按单列降序

DESC表明按照降序排列，按单列降序，示例如下：

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

5.3.2 用多个列降序：

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

DESC关键字只应用到直接位于其前面的列名，因此上例只对prod_price进行了降序排序
如果想在多个列上进行降序排序，必须对每个列以降序排序
DES的相反为ASC即升序，升序为默认

3.3 最高或最低值查找

SELECT products.prod_price FROM products ORDER BY prod_price DESC LIMIT 1;

注：ORDER BY 子句应位于FROM子句之后，而LIMIT应位于ORDER BY之后

chap6 过滤数据

本章将讲授使用SELECT语句的WHERE子句指定搜索条件

6.1 使用WHERE子句

在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤
WHERE 子句在表名(FROM)之后给出

SELECT products.prod_name,products.prod_price from products WHERE prod_price=2.5;

注：在同时使用ORDER BY和WHERE子句时，应该让ORDER BY 位于WHERE 之后，否则将产生错误

6.2 WHERE子句操作符

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

6.2.1 检查单个值

SELECT products.prod_name,products.prod_price FROM products WHERE prod_name='fuses';  #单引号用来限定字符串
SELECT products.prod_name,products.prod_price FROM products WHERE prod_price<10;

6.2.2 不匹配检查

SELECT products.vend_id,products.prod_name FROM products WHERE vend_id <>1003;

6.2.3 范围值检查

检查某个范围的值，使用BETWEEN操作符

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

6.2.4 空值检查

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

chap7 数据过滤

7.1 组合WHERE子句

这些子句可以两种方式使用：以AND子句的方式或OR子句的方式使用、

7.1.1 AND操作符

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

7.1.2 OR操作符

OR操作符与AND操作符不同，它指示MySQL检索匹配任以条件的行 匹配任一条件

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

7.1.3 计算次序

在处理OR操作符前，优先处理AND操作符，因此需要使用圆括号来解决次序问题，比较下面两个结果：

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

7.2 IN操作符

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

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

7.3 NOT操作符

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

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

chap8 用通配符进行过滤

介绍什么是通配符、如何使用通配符以及怎样使用LIKE操作符进行通配搜索，以便对数据进行复杂过滤

LIKE 操作符

通配符：用来匹配值的一部分的特殊字符
通配符本身是WHERE子句中有特殊含义的字符
搜索模式：由字面值、通配符或两者组合构成的搜索条件
为在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示MySQL,后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

8.1 百分号（%）通配符

%:表示任何字符出现的任意次数

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

'jet%':表明接受jet之后的任意字符，不管它有多少字符
注：搜索区分大小写
通配符可在搜索模式中任意位置使用，并且可以使用多个通配符

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

8.2 下划线（_）通配符

下划线只匹配单个字符而不是多个

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

chap9 用正则表达式搜索

WHERE ... REGEXP.....

9.1 正则表达式介绍

正则表达式是用来匹配文本的特殊的串。
可以从文本文件中提取电话号码，可以查找名字中间有有数字的所有文件，可以在一个文本块中找到所有重复的词，可以替换一个页面中的所有URL为实际的HTM链接。
所有种类的程序设计语言、文本编译器、操作系统等都支持正则表达式。

9.2 基本字符匹配

检索prod_name列包含文本1000的所有行:
SELECT products.prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;

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

.是正则表达式语言中一个特殊的字符，表示匹配任意一个字符
LIKE 与 REGEXP的区别：
LIKE 匹配整个列，而REGEXP在列值内进行匹配

9.3 进行OR匹配

为搜索两个串之一

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

|为正则表达式or的操作
两个以上的or 条件：‘1000|2000|3000’

9.4 匹配几个字符之一

如果想匹配特定的字符，可通过指定一组用[]括起来的字符来完成，[]也表示或的意思

[123]表示匹配1或2或3
SELECT products.prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;

[]是另一种形式的OR语句
字符串的否定：[^123]为匹配除这些字符外的任意东西

9.5 匹配范围

[123456789]=[1-9]

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

9.6 匹配特殊字符

9.6.1 "."匹配符

"."匹配任意字符，因此下面这个例子所有的行都被检索出来

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

9.6.2 “\”匹配符

为了匹配特殊字符，必须用“\\”为前导，\\-表示查找-，\\。表示查找.
下例为查找表中名字带.的名字按名字顺序排序

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

\也可以用来引用元字符(具有特殊含义的字符)
空白元字符：

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

9.7 匹配字符类

字符类：

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

9.8 匹配多个实例

使用重复元字符来实现多次出现的匹配
下表：重复元字符

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

元字符?举例：

SELECT products.prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name;
sticks?表示：匹配stick和sticks； ？表示匹配它前面任何字符的0次或1次出现

字符类[:digit:]举例：匹配任意数字

{4}：指定匹配为4位数

SELECT products.prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name;  #表明匹配连在一起的任意4位数字
代码同：
SELECT products.prod_name FROM products WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]' ORDER BY prod_name;

9.9 定位符

目前为止的所有例子都是匹配一个串中任意位置的文本，为了匹配特定位置的文本，需要使用定位符。
表：定位元字符

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

举例：

SELECT products.prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;    #找出以一个数(包括小数点开始的数)开始的所有产品

chap10 创建计算字段

10.1 计算字段定义

需要直接从数据库中检索出转换、计算或格式化过的数据；而不是检索出数据，然后再在客户机应用程序或报告程序中重新格式化

10.2 拼接字段

使用Concat()函数
拼接(concatenate)将值接到一起构成单个值，使用联接函数来拼接两个列
举例：

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

结果如下所示：

1.png

Concat()拼接串，即把多个串连接起来形成一个较长的串，并且用来连接多个时，中间用逗号隔开

使用RTrim()函数来删除右方多余的空格来整理数据
举例：

SELECT CONCAT(RTRIM(vendors.vend_name),'(',RTRIM(vendors.vend_country),')') FROM vendors ORDER BY vend_name;

结果如上图，与上图结果一样
注：RTrim()：去掉串右边的空格
LTrim（）：去掉串左边的空格

10.3 使用别名

别名用AS关键字赋予
一个未命名的列不能用于客户机应用中，因为客户机没办法引用它
别名(alias)是一个字段或值的替换名
举例：将该列用别名替代

SELECT CONCAT(RTRIM(vendors.vend_name),'(',RTRIM(vendors.vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;

结果如下图所示：

2.png

10.4 执行算术计算

计算字段的另一种常见用途是对检索的数据进行算术计算
举例：

检索订单号20005中的所有物品
SELECT products.prod_id,orderitems.quantity,orderitems.item_price FROM products,orderitems WHERE order_num=20005;