《Mysql必知必会》笔记（一）

---

一、数据库基本概念

数据库

数据库（database）保存有组织的数据的容器，通常是一个文件或一组文件。
数据库管理系统（DBMS）使用DBMS访问数据库，可理解为数据库软件。

表和模式

表（table） 某种特定类型数据的结构化清单。同一个数据库中表名唯一。
模式（schema） 关于数据库和表的布局及特性的信息。

列和数据类型

列（column） 表中的一个字段，所有表由一个或多个列组成。
数据类型（datatype） 数据库中每个列都有相应的数据类型，数值/字符/……。

行

行（row） 或称为数据库记录。

主键

主键（primary key） 一列或一组列，其值能够唯一区分表中每个行。任意两行都不具有相同的主键值，每个行都必须有一个主键值。
主键的好习惯：不更新主键列中的值；不重用主键列的值；不在主键列中使用可能会更改的值。

外键

SQL

Structured Query Language, 结构化查询语言。

二、Mysql

实用程序

• 命令输入在mysql>之后
• 命令用；或\g结束
• 输入help或\h获得帮助，help select获得使用SELECT语句的帮助
• 输入quit或exit推出命令行实用程序。

常用命令

USE crashcourse              //选择数据库
SHOW DATABASES               //返回可用数据库的一个列表
SHOW TABLES                  //返回当前选择的数据库内可用表的列表
SHOW COLUMNS FROM customers  //返回指定表名中每个字段的行
DESCRIBE customers           //SHOW COLUMNS FROM 语句的快捷方式
SHOW STATUS                  //用于显示广泛的服务器状态信息
SHOW CREATE DATABASE/TABLE   //创建特定数据库或表
SHOW GRANTS                  //显示授予用户的安全权限
SHOW ERRORS/WARNINGS         //显示服务器错误或警告消息

三、语句总结

1. 检索数据

FROM子句

SELECT [column_name] FROM [table_name]					  	     //检索单个列
SELECT [column_name1, column_name2, ...] FROM [table_name] 	   	 //检索多个列
SELECT * FROM [table_name]                             	      	 //检索所有列
SELECT DISINCT [column_name] FROM [table_name]          	     //唯一返回匹配的行，若有重复只返回一次
SELECT [column_name] FROM [table_name] LIMIT n                   //检索单个列，返回不多于n行
SELECT [column_name] FROM [table_name] LIMIT n,m                 //检索单个列，返回从n行开始的m行
SELECT [column_name] FROM [table_name] LIMIT m OFFSET n          //从行3开始取4行
SELECT [table_name.column_name] FROM [database_name.table_name]  //使用完全限定的表名

注意事项

1. 多条SQL语句必须以分号分隔，单条不需要，但通常加上；
2. SQL语句不区分大小写，有些版本的标识符可能不同；
3. 处理SQL语句时空格忽略，分行便于阅读和调试；
4. 不能部分使用DISTINCT，除非多列完全不同；
5. 行数从行0开始计；

2. 排序检索数据

ORDER BY子句

SELECT [column_name] FROM [table_name] ORDER BY [column_name]                                     //取一列或多列的名字，据此对输出排序
SELECT [column_name1, column_name2, ...] FROM [table_name] ORDER BY [column_name1, column_name3]  //取一列或多列的名字，据此对输出排序

DESC关键字

SELECT [column_name1, column_name2, ...] FROM [table_name] ORDER BY [column_name2] DESC                  //降序排列
SELECT [column_name1, column_name2, ...] FROM [table_name] ORDER BY [column_name2] DESC, [column_name1]  //降序排列，再按column_name1排列
SELECT [column_name1] FROM [table_name] ORDER BY [column_name1] DESC/ASC LIMIT 1                         //找出按column_name1排序的最大或最小值

注意事项

1. DESC关键字只应用到直接位于其前面的列名，若想多个列降序，必须对每个列指定DESC关键字。升序是ASC(ASCENDING)；
2. ORDER BY子句必须是SELECT语句中的最后一条子句。

3. 过滤数据

SELECT [column_name1, column_name2, ...] FROM [table_name] WHERE [搜索条件]                       //返回符合搜索条件的行
SELECT [column_name1, column_name2, ...] FROM [table_name] WHERE [column_name1] BETWEEN m AND n  //AND关键字
SELECT [column_name1, column_name2, ...] FROM [table_name] WHERE [column_name1] IS NULL          //IS NULL子句空值检查（不是指值为0）

WHERE子句操作符

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

AND操作符

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

OR操作符

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

//由供应商1003制造的任何价格为10美元（含）以上的产品，或者由供应商1002制造的任何产品
SELECT [prod_price, prod_name]
FROM [products]
WHERE [vend_id = 1002] OR [vend_id = 1003] AND [prod_price >= 10]; 

IN操作符
用来指定条件范围，范围中的每个条件都可以进行匹配，匹配条件在圆括号中逗号分隔，功能等同于OR。

SELECT [prod_price, prod_name]
FROM [products]
WHERE [vend_id] IN (1002, 1003)
order by [prod_name]; 

NOT操作符
NOT与IN联合使用便于找出与条件列表不匹配的行。

SELECT [prod_price, prod_name]
FROM [products]
WHERE [vend_id] NOT IN (1002, 1003)
order by [prod_name]; 

注意事项

1. 同时使用ORDER BY和WHERE子句时，ORDER BY位于WHERE之后；
2. MySQL匹配默认不区分大小写；
3. 单引号用来限定字符串；
4. 过滤选择出不具有特定值的行时，无法返回具有NULL值的行，因此要验证返回数据中确实给出了被过滤列具有NULL的行;
5. SQL在处理OR操作符之前，优先处理AND操作符。
6. IN操作符一般比OR操作符清单执行更快；
7. MySQL支持NOT对IN/BETWEEEN/EXISTS子句取反，与其他DBMS允许使用NOT对各种条件取反不同。

4. 用通配符进行过滤

通配符(wildcard) 用来匹配值的一部分的特殊字符。
搜索模式(search pattern) 由字面值、通配符或两者组合构成的搜索条件。
LIKE操作符 本质是谓词，指示MySQL后面跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

百分号(%)通配符
%表示任何字符出现任意次数，可以位于模式的两端或中间

//检索任意jet起头的词，%告诉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%';

下划线(_)通配符
下划线只匹配单个字符

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

注意事项

1. 根据MySQL的配置方式，搜索区分大小写；
2. 尾空格可能会干扰通配符匹配，例如在保存词anvil时，如果后面有一个或多个空格，则子句WHERE prod_name LIKE '%anvil‘ 将不会匹配他们。解决方法是在搜索模式最后加一个%(%可以匹配0个字符)，或使用函数去掉首尾空格。
3. 通配符置于搜索模式的开始处，搜索起来是最慢的。除非有必要否则不要用在搜索模式的开始。通配符不要过度使用。

5. 用正则表达式进行搜索

关键字REGEXP
REGEXP后面的内容作为正则表达式处理

//匹配出JetPack 1000和JetPack 2000
SELECT prod_name
FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;

OR匹配
为搜索两个串之一，使用 |

//匹配出JetPack 1000和JetPack 2000，多个OR条件可并入单个正则表达式
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;

匹配几个字符之一
匹配特定字符，可指定一组[和]括起来的字符完成。

//匹配出1 ton anvil和2 ton anvil
//[]是另一种形式的OR语句
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name;

//检索‘1’或‘2’或’3 ton'
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1|2|3 Ton'
ORDER BY prod_name;

^表示否定一个字符集，如

[^123] 匹配除123外的任何东西

匹配范围
如[1-3]和 [0-9]， [a-z]匹配任意字母字符。

匹配特殊字符
用\作为前导，如’\\-‘表示查找-，’\\.'表示查找.。

空白元字符

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

匹配字符类

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

匹配多个实例

重复元字符

元字符	说明
*	0个或多个匹配
+	1个或多个匹配，同{1,}
？	0个或1个匹配，同{0,1}
{n}	指定数目的匹配
{n,}	不少于数目的匹配
{n,m}	匹配数目的范围（m不超过225）

\\([0-9]sticks?\\)表示：\\(匹配(，[0-9]匹配任意数字，sticks?匹配stick和sticks,\\)匹配)
[[:digit:]]{4}表示：匹配连在一起的4位数字

定位符
匹配特定位置的文本

定位元字符

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

'^[0-9\\.]'表示：在.或任意数字为串中第一个字符时才匹配

注意事项

1. 匹配不区分大小写，为区分大小写可在匹配条件前使用BINARY关键字；
2. 如果被匹配的文本仅在列值中出现，LIKE将不会找到它，相应的行也不会被返回除非使用通配符。而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回；LIKE匹配整个串而REGEXP匹配子串；
3. MySQL要求两个反斜杠转义特殊字符；
4. ^在集合中用来否定该集合，否则用来指串的开始处；
5. 正则表达式测试：REGEXP检查返回0（不匹配）或1（匹配），如SELECT ‘hello’ REGEXP ’[0-9]’ 返回0，因为hello中没有数字。