SQL语句学习

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.创建计算字段

1. 什么是计算字段？
   计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的。

Concat();

2. Concat()：在MySQL的SELECT语句中，可使用Concat()函数来拼接两个列。
   举例：SELECT ConCat(vend_name,' (',vend_country,')') FROM vendors ORDER BY vend_name;

RTrim();

3. RTrim():去掉值右边的所有空格
   举例：SELECT ConCat(vend_name,' (',RTrim(vend_country),')') FROM vendors ORDER BY vend_name;
LTrim():去掉值左边的所有空格
   Trim():去掉值两边的所有空格

别名:AS

4. AS：别名
   举例：SELECT ConCat(vend_name,' (',RTrim(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;

算术运算

5. 执行算术运算：
   SELECT prod_id,quantity,item_price,quantity*item_price AS expended_price FROM orderitems WHERE order_num = 20005;

操作符	说明
+	加
-	减
*	乘
/	除

如何测试计算？

• SELECT 3*2;
  返回6
• SELECT Trim('abc');
  返回abc
• SELECT 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（）

返回总和。