MySQL必知必会
1.SELECT(检索数据)
1.检索单个列:SELECT prod_name FROM products;
2.检索多个列:SELECT prod_name,prod_id,prod_price FROM products;
3.检索所有列:SELECT * FROM products;
4.检索不同的行:SELECT DISTINCT vend_id FROM products;
注意:这里不能部分使用DISTINCT
,DISTINCT
关键字应用于所有列
5.限制结果:LIMIT
。
SELECT prod_name FROM products LIMIT 5,5;
从行5开始,取5行。
注意:这里第一行是行0
MySQL 5
支持另一种替换写法:LIMIT 4 OFFSET 3
=LIMIT 3,4
从行3开始,取4行。
6.可能会使用完全限定的列名、表名(如:SELECT products.prod_name FROM crashcourse.products;
)
2.ORDER BY(排序数据)
1.SELECT prod_name FROM products ORDER BY prod_name;
MySQL对prod_name
列以字母顺序排序数据
也可以通过非选择列进行排序
2.按多个列排序
SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price,prod_name;
在多个列排序时,排序会按照规定的顺序进行。
3.指定排序方向
ASC
:升序(默认)
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;
使用ORDER BY
和LIMIT
的组合,可以找到一个列的最高和最低的值:
SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;
3.WHERE(过滤数据)
SELECT prod_name,prod_price FROM products WHERE prod_price = 2.50;
ORDER BY
位于 WHERE
字句之后。
操作符 | 说明 |
---|---|
= | 等于 |
<> | 不等于 |
!= | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
BETWEEN | 在指定的两个值之间 |
1.检查单个值
SELECT prod_name,prod_price FROM products WHERE prod_name = 'fuses';
MySQL在执行匹配时默认不区分大小写。
2.不匹配检查(!=或者<>)
SELECT vend_id,prod_name FROM products WHERE vend_id <> 1003;
找出不是由供应商1003制造的所有商品
何时使用引号?
单引号用于限定字符串。如果将值与串类型的列进行比较,则需要限定引号。用来与数值列进行比较的值不用引号。
3.范围值检查–>使用BETWEEN
操作符
SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
BETWEEN
匹配范围中所有的值,包括指定的开始值和结束值。
4.空值检查(IS NULL)
IS NULL
:用来检查具有NULL
值的列
SELECT prod_name FROM products WHERE prod_price IS NULL;
NULL
:无值(no value
),与字段包含0、空字符串或仅仅包含空格不同。
NULL值与不匹配
:在匹配过滤或不匹配过滤时不会返回具有NULL
值的行。
4.数据过滤
组合WHERE子句(AND,OR,NOT)
1.AND
或OR
:
SELECT prod_id,prod_price,prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10;
SELECT prod_name,prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003;
注意:
SQL
在处理OR
操作符前,优先处理AND
操作符!!!
建议:多使用()圆括号,明确分组操作符。
2.IN
操作符:WHERE
子句中用来指定要匹配值的清单的关键字
SELECT prod_name,prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name;
上下两种写法是一样的
SELECT prod_name,prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 ORDER BY prod_name;
3.NOT
操作符
SELECT prod_name,prod_price FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name;
5.通配符过滤(LIKE)
通配符:用来匹配值的一部分的特殊字符。
为了在搜索子句中使用通配符,必须使用LIKE操作符
。LIKE
指示MySQL
后跟的搜索模式利用通配符匹配,而不是直接相等匹配进行比较。
1.%
百分号通配符:
%表示任何字符出现任意次数
SELECT prod_id,prod_name FROM products WHERE prod_name LIKE 'jet%';
检索任意以 jet 开头的词。
区分大小写:根据MySQL的配置方式,搜索可以区分大小写。SELECT prod_id,prod_name FROM products WHERE prod_name LIKE '%anvil%';
匹配任何位置包含文本 anvil 的值。
注意尾空格:尾空格可能会干扰通配符匹配。
注意NULL:即使是LIKE '%'
也不能匹配NULL
2._
下划线通配符
_:匹配单个字符
,不能多也不能少
关于通配符使用的技巧:
- 通配符一般时间比较长
- 不要过度使用通配符
- 除非有必要,否则不要把通配符置于搜索模式的开始处,这样是最慢的
- 注意通配符的位置
6.用正则表达式进行搜索(REGEXP)
正则表达式:用来匹配文本的特殊的串(字符集合)
1.基本字符匹配
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
匹配整个列,如果被匹配的文本在列值中出现,LIKE
将不会找到它,相应的行也不会返回(除非使用通配符)REGEXP
在列值内进行匹配,如果被匹配的文本在列值中出现,REGEXP
将会找到它,相应的行将被返回
匹配不区分大小写:
MySQL的正则表达式匹配不区分大小写。
为区分大小写,可以使用BINARY
关键字:
WHERE prod_name REGEXP BINARY 'JetPack .000';
2.进行OR匹配(|
)
SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;
1000|2000|3000
:表示匹配1000或2000或3000
3.匹配几个字符之一([和])
SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;
[123] Ton
:表示匹配 1 或 2 或 3(其实是[1|2|3] Ton
的缩写)
**字符的否定:**在字符集合的开始处放置一个^
[^123]
:表示匹配除这些字符外的任何东西
4.匹配范围
[0123456789] --> [0-9]
SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;
5.匹配特殊字符(\\作为前导)
SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;
特殊字符:’.’、’[]’、’|’、’-’
为了匹配\
本身,要使用\\\
元字符 | 说明 |
---|---|
\\f | 换页 |
\\n | 换行 |
\\r | 回车 |
\\t | 制表 |
\\v | 纵向制表 |
6.匹配字符类
字符类
:预定义的字符集
MySQL必知必会
:P58
7.匹配多个实例
字符 | 说明 |
---|---|
* | 0个或多个匹配 |
+ | 1个或多个匹配(等于{1,}) |
? | 0个或1个匹配(等于{0,1}) |
{n} | 指定数目的匹配 |
{n,} | 不少于指定数目的匹配 |
{n,m} | 匹配数目的范围(m不超过255) |
SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name;
说明:\\(
匹配(,[0-9]
匹配任何数字,sticks?
匹配stick和sticks(s后面的?使s可选,因为?匹配它前面的任何字符的0次或1次出现)SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name;
匹配连在一起的4位数字
上述等同于
SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]' ORDER BY prod_name;
8.定位符(^,$,[:<:],[:>:])
元字符 | 说明 |
---|---|
^ | 文本的开始 |
$ | 文本的结尾 |
[[:<:]] | 词的开始 |
[[:>:]] | 词的结尾 |
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;
找出以一个数(包括以小数点开始的数)开始的所有产品
^的两种用法:- 1.在集合中,[和],用^来否定该集合
- 2.指串的开始处
简单的正则表达式测试:
SELECT 'hello' REGEXP '[0-9]';
7.创建计算字段
- 什么是计算字段?
计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT
语句内创建的。
Concat();
Concat()
:在MySQL
的SELECT
语句中,可使用Concat()
函数来拼接两个列。
举例:SELECT ConCat(vend_name,' (',vend_country,')') FROM vendors ORDER BY vend_name;
RTrim();
RTrim()
:去掉值右边的所有空格
举例:SELECT ConCat(vend_name,' (',RTrim(vend_country),')') FROM vendors ORDER BY vend_name;
LTrim()
:去掉值左边的所有空格
Trim()
:去掉值两边的所有空格
别名:AS
AS
:别名
举例:SELECT ConCat(vend_name,' (',RTrim(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;
算术运算
- 执行算术运算:
SELECT prod_id,quantity,item_price,quantity*item_price AS expended_price FROM orderitems WHERE order_num = 20005;
操作符 | 说明 |
---|---|
+ | 加 |
- | 减 |
* | 乘 |
/ | 除 |
如何测试计算?
SELECT 3*2;
返回6SELECT Trim('abc');
返回abcSELECT Now();
返回当前日期和时间
8.使用数据处理函数
1.文本处理函数
RTrim()
:删除值右边的空格Upper()
:将文本转换为大写
SELECT vend_name,Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;
函数 | 说明 |
---|---|
Left() | 返回串左边的字符 |
Length() | 返回串的长度 |
Locate() | 找出串的一个子串 |
Lower() | 将串转换为小写 |
Ltrim() | 去掉串左边的空格 |
Right() | 返回串右边的字符 |
RTrim() | 去掉串右边的空格 |
SoundEX() | 返回串的SOUNDEX值 |
SubString() | 返回子串的字符 |
Upper() | 将串转换为大写 |
? . 聚集函数
1.AVG()
求平均值。
注意:1.只用于单列
2.忽略NULL值
2.COUNT()
用于计数。
1.如果是COUNT(*):查询所有的行数,包括NULL值
2.如果是COUNT(具体的列):忽略NULL值
3.MAX()
返回最大值。
对非数值数据进行MAX()时,返回最后一行。
忽略NULL值。
4.MIN()
略
5.SUM()
返回总和。