《MySql必知必会》笔记整理

数据库基础

关键词：
数据库
表（表名唯一，取决多个因素，如不同数据库的表可以同名）
模式（关于数据库和表的布局及特性的信息）
列(表中的字段)
行[行(raw)和记录(record)很大程度可以等同，但行才是正确的术语]
数据类型（限制数据种类，帮助正确排序，磁盘优化方面的作用）
主键（primary key）: 一列，其值可以唯一区分表中的行。
SQL [（Structured Query Language）：结构化查询语言。]

主键条件：

每行都应有一个主键，所以其值不为null。
任意两行间的主键值不同。

主键通常是一列，但也可多列共同构成主键。

主键设置建议：

不更新主键列中的值；
不重用主键列的值；
不在主键列中使用可能会更改的值。

SQL命令执行：

命令在mysql>之后输入；
命令用 ；或g结束，仅按Enter不执行命令；
输入help或h获取帮助；
输入quit或exit退出程序。

基本语句：

myslq -u root -p;
use Database;

#SHOW相关
SHOW databases;
SHOW tables;
SHOW columns FROM tables;    -- 等于    describe "tables";
SHOW CREATE DATABASE db_name; 显示完整的建库语句
SHOW CREATE TABLE tbl_name;
SHOW [STORAGE] ENGINES

#SELECT相关
SELECT column_name1,column_name2 FROM table;
SELECT *FROM tables;
--  Distinct
-- 不能部分使用DISTINCT，其应用于所有列而不是其前置列
SELECT DISTINCT column_namw FROM table; 
-- Limit   从第零个开始后的5个    取的时候排列顺序是从零开始的。
SELECT column_name FROM table_name LIMIT 5;
-- 从第二个开始后的5个
SELECT column_name FROM table_name LIMIT 2,5;
-- OFFSET 限制两个，从第三为开始取
SELECT column_name FROM table_name  LIMIT 2 OFFSET 3; 
-- 使用全限定的表名    库：manxc    表：tags
SELECT tags.tid FROM manxc.tags;

排序检索数据

关键字：ORDER BY

SELECT column_name FROM table_name ORDER BY column_name;

默认升序，字母按A-Z排，数字从小到大；
注：排序中文时出现问题。
解决：https://www.jianshu.com/p/8a9135f9cd47?utm_campaign
升序（默认）：ASC 降序：DESC

过滤数据

关键字：WHERE（同时可与其它关键字组合）

SELECT * FROM manxc.tags WHERE tags.tid BETWEEN 2 AND 9 ORDER BY tid DESC,tagname;

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

eg:

mysql> SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1;
    -> 1, 0 
mysql> SELECT 1 BETWEEN 2 AND 3; 
    -> 0 
mysql> SELECT 'b' BETWEEN 'a' AND 'c'; 
    -> 1 
mysql> SELECT 2 BETWEEN 2 AND '3'; 
    -> 1 
mysql> SELECT 2 BETWEEN 2 AND 'x-3'; 
    -> 0

WHERE 匹配字符加‘’；且其在执行匹配时默认不区分大小写；

mysql> SELECT uid,username,state FROM manxc.user WHERE username='FLY';
+-----+----------+-------+
| uid | username | state |
+-----+----------+-------+
|   3 | fly      |     0 |
+-----+----------+-------+

空值检查：IS NULL (空值是无值和0和空格不同)

mysql> SELECT uid,username,state FROM user WHERE password IS NULL LIMIT 3;
+-----+----------+-------+
| uid | username | state |
+-----+----------+-------+
|   8 | dfdg     |  NULL |
|   9 | dgdg     |  NULL |
|  10 | gdg      |  NULL |
+-----+----------+-------+

数据过滤

关键词
操作符（operator） :用来联结或改变where子句的关键字。
AND 操作符

mysql> SELECT uid,username,state FROM USER WHERE state IS NULL AND uid <= 13;
+-----+-----------+-------+
| uid | username  | state |
+-----+-----------+-------+
|   8 | dfdg      |  NULL |
|   9 | dgdg      |  NULL |
|  10 | gdg       |  NULL |
|  11 | dgdgh     |  NULL |
|  12 | dgklds    |  NULL |
|  13 | dgkljdlkg |  NULL |
+-----+-----------+-------+

OR操作符：

mysql> SELECT uid,username,state FROM USER WHERE state IS NULL OR uid <= 13;
+-----+--------------+-------+
| uid | username     | state |
+-----+--------------+-------+
|   4 | test1        |     0 |
|   3 | fly          |     0 |
|   5 | test2        |     0 |
|   6 | test3        |     1 |
|   7 | 1            |     1 |
|   8 | dfdg         |  NULL |
|   9 | dgdg         |  NULL |
|  10 | gdg          |  NULL |
|  11 | dgdgh        |  NULL |
|  12 | dgklds       |  NULL |
|  13 | dgkljdlkg    |  NULL |
|  14 | fdjwe        |  NULL |
|  15 | gkdlkg       |  NULL |
|  16 | dgdlkjg      |  NULL |
|  17 | fdglkdjg     |  NULL |
|  18 | gkldssjgdsas |  NULL |
|  19 | dgjkljg      |  NULL |
|  20 | djglkdg      |  NULL |
|  21 | kgdlksgj     |  NULL |
+-----+--------------+-------+

混合使用时的顺序：
在有多个or和and同时使用时，优先处理and，可以使用()提高优先级。

mysql> SELECT uid,username,state FROM USER WHERE (state IS NULL OR state =1) AND uid <=10;
+-----+----------+-------+
| uid | username | state |
+-----+----------+-------+
|   6 | test3    |     1 |
|   7 | 1        |     1 |
|   8 | dfdg     |  NULL |
|   9 | dgdg     |  NULL |
|  10 | gdg      |  NULL |
+-----+----------+-------+
5 rows in set (0.00 sec)


mysql> SELECT uid,username,state FROM USER WHERE state IS NULL OR state =1 AND uid <=10;
+-----+--------------+-------+
| uid | username     | state |
+-----+--------------+-------+
|   6 | test3        |     1 |
|   7 | 1            |     1 |
|   8 | dfdg         |  NULL |
|   9 | dgdg         |  NULL |
|  10 | gdg          |  NULL |
|  11 | dgdgh        |  NULL |
|  12 | dgklds       |  NULL |
|  13 | dgkljdlkg    |  NULL |
|  14 | fdjwe        |  NULL |
|  15 | gkdlkg       |  NULL |
|  16 | dgdlkjg      |  NULL |
|  17 | fdglkdjg     |  NULL |
|  18 | gkldssjgdsas |  NULL |
|  19 | dgjkljg      |  NULL |
|  20 | djglkdg      |  NULL |
|  21 | kgdlksgj     |  NULL |
+-----+--------------+-------+
16 rows in set (0.00 sec)

建议：使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确的分组，不用过分依赖计算次序，使用括号没有坏处且能消除歧义。

IN 操作符：
where子句使用in操作符

mysql>  SELECT uid,username,state FROM user WHERE state IN (0,1);
+-----+----------+-------+
| uid | username | state |
+-----+----------+-------+
|   4 | test1    |     0 |
|   3 | fly      |     0 |
|   5 | test2    |     0 |
|   6 | test3    |     1 |
|   7 | 1        |     1 |
+-----+----------+-------+
5 rows in set (0.00 sec)

IN 和 OR 有类似作用，此句表示查询state是0或1的。

IN操作符的优点：

使用IN时，计算次序更容易管理（操作符少了，没那么多or）
IN操作符的语法更清楚且直观；
IN一般比OR的执行更快；
IN的最大优点时可以包含其它SELECT语句，使得能更动态的建立WHERE子句。

NOT操作符：
where子句中，not用来否定之后跟的条件。

mysql> select * from tags where tid NOT IN (1,2,3,4,5,6,7,8,9,10);
+-----+---------+
| tid | tagname |
+-----+---------+
|  11 | 猎奇    |
|  12 | 少女    |
|  13 | 魔法    |
|  14 | 历史    |
|  15 | 机战    |
|  16 | 神魔    |
|  17 | 运动    |
|  18 | 励志    |
|  19 | 音乐    |
|  20 | 推理    |
|  21 | 美食    |
|  22 | 催泪    |
|  23 | 职场    |
|  26 | 搞笑    |
+-----+---------+

注：MySQL支持使用NOT对IN，BETWEEN，EXISTS子句取反。

统配符过滤

关键字：like
统配符匹配：

百分号（%）通配符：表示任何字符出现任意次数，（任意字数的任意字符）
注：
A.由配置方式，搜索时可以区分大小写的；
B.注意尾空格，尾空格会干扰匹配，可在其前后都家%,或者使用函数。
下划线（_）: 任意的单个字符；
注：统配符的搜索处理比之前操作符的效率更低使用时：
1，不要过度使用，能用其他操作符的，尽量用。
2，除非有绝对必要，不要把通配符放在搜索模式的开始处，这样是最慢的。
3，注意统配符放的位置。

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

正则表达式搜索

关键字：REGEXP 其后跟正则表达式
与LIKE与REGEXP的区别：
LIKE匹配整个列，如果匹配的文本在列值中出现，LIKE不会找到它，相应行也不会返回（除非使用统配符），而REGEXP可以在列值中进行匹配：

mysql> SELECT uid,username,password,state FROM user WHERE username LIKE "test";
Empty set (0.00 sec)

mysql> SELECT uid,username,password,state FROM user WHERE username REGEXP "test";
+-----+----------+----------+-------+
| uid | username | password | state |
+-----+----------+----------+-------+
|   4 | test1    | 123      |     0 |
|   5 | test2    | 123      |     0 |
|   6 | test3    | 123      |     1 |
+-----+----------+----------+-------+
3 rows in set (0.05 sec)

注：MYSQL中正则匹配不区分大小写，如需区分可使用BINARY关键字，如WHERE prod-name REGEXP BINARY 'JetPack'.

进行or匹配使用“|”

mysql> SELECT uid,username,password,state FROM user WHERE username REGEXP "1|lk";
+-----+-----------+----------+-------+
| uid | username  | password | state |
+-----+-----------+----------+-------+
|   4 | test1     | 123      |     0 |
|   7 | 1         | 1        |     1 |
|  13 | dgkljdlkg | NULL     |  NULL |
|  15 | gkdlkg    | NULL     |  NULL |
|  16 | dgdlkjg   | NULL     |  NULL |
|  17 | fdglkdjg  | NULL     |  NULL |
|  20 | djglkdg   | NULL     |  NULL |
|  21 | kgdlksgj  | NULL     |  NULL |
+-----+-----------+----------+-------+
8 rows in set (0.00 sec)

匹配字符：
使用“[]”匹配，相当于另一种形式的or；匹配其中的任意字符

mysql> SELECT uid,username,password,state FROM user WHERE username REGEXP "test";
+-----+----------+----------+-------+
| uid | username | password | state |
+-----+----------+----------+-------+
|   4 | test1    | 123      |     0 |
|   5 | test2    | 123      |     0 |
|   6 | test3    | 123      |     1 |
|  22 | test4    | NULL     |  NULL |
|  23 | test5    | NULL     |  NULL |
|  24 | test6    | NULL     |  NULL |
+-----+----------+----------+-------+
6 rows in set (0.00 sec)

mysql> SELECT uid,username,password,state FROM user WHERE username REGEXP "test[123]";
+-----+----------+----------+-------+
| uid | username | password | state |
+-----+----------+----------+-------+
|   4 | test1    | 123      |     0 |
|   5 | test2    | 123      |     0 |
|   6 | test3    | 123      |     1 |
+-----+----------+----------+-------+
3 rows in set (0.00 sec)

mysql> SELECT uid,username,password,state FROM user WHERE username REGEXP "[123]test";
Empty set (0.00 sec)

mysql> SELECT uid,username,password,state FROM user WHERE username REGEXP "[test]";
+-----+--------------+----------+-------+
| uid | username     | password | state |
+-----+--------------+----------+-------+
|   4 | test1        | 123      |     0 |
|   5 | test2        | 123      |     0 |
|   6 | test3        | 123      |     1 |
|  12 | dgklds       | NULL     |  NULL |
|  14 | fdjwe        | NULL     |  NULL |
|  18 | gkldssjgdsas | NULL     |  NULL |
|  21 | kgdlksgj     | NULL     |  NULL |
|  22 | test4        | NULL     |  NULL |
|  23 | test5        | NULL     |  NULL |
|  24 | test6        | NULL     |  NULL |
+-----+--------------+----------+-------+
10 rows in set (0.00 sec)

加上“^”表非

mysql> SELECT uid,username,password,state FROM user WHERE username REGEXP "test[^123]";
+-----+----------+----------+-------+
| uid | username | password | state |
+-----+----------+----------+-------+
|  22 | test4    | NULL     |  NULL |
|  23 | test5    | NULL     |  NULL |
|  24 | test6    | NULL     |  NULL |
+-----+----------+----------+-------+
3 rows in set (0.00 sec)

空格

mysql> SELECT uid,username,password,state FROM user WHERE username REGEXP "1|2 test";
+-----+----------+----------+-------+
| uid | username | password | state |
+-----+----------+----------+-------+
|   4 | test1    | 123      |     0 |
|   7 | 1        | 1        |     1 |
+-----+----------+----------+-------+
2 rows in set (0.00 sec)

匹配范围:如[1-3],[a-z];

mysql> SELECT uid,username,password,state FROM user WHERE username REGEXP "test[1-3]";
+-----+----------+----------+-------+
| uid | username | password | state |
+-----+----------+----------+-------+
|   4 | test1    | 123      |     0 |
|   5 | test2    | 123      |     0 |
|   6 | test3    | 123      |     1 |
+-----+----------+----------+-------+
3 rows in set (0.00 sec)

匹配特殊字符：使用 “\特殊字符”，即转义

\.能够匹配.
\f换页
\n换行
\r回车
\t制表
\纵向制表

注意：
　　a)为了匹配本身，需要使用 \
　　b)在一般情况下正则表达式的转义加一个“”就可以了，在MySQL中需要加两个。

匹配字符类：

[:alnum:]=[a-zA-Z0-9]

[:alpha:]=[a-zA-Z]

[:digit:]=[0-9]

[:lower:]=[a-z]

[:upper:]=[A-Z]

[:xdigit:]=[a-fA-F0-9]

重复次数匹配将其加在之后：

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

匹配任意三个连续数字：
问：把其【【】】是一种更好习惯？还是有什么区别
答：所以这里的区别是[[:digit:]],[:digit:]有的

mysql> SELECT uid,username,password,state FROM user WHERE password REGEXP "[[:digit:]]{3}";
+-----+----------+----------+-------+
| uid | username | password | state |
+-----+----------+----------+-------+
|   4 | test1    | 123      |     0 |
|   3 | fly      | 123      |     0 |
|   5 | test2    | 123      |     0 |
|   6 | test3    | 123      |     1 |
|   8 | dfdg     | d124     |  NULL |
|   9 | dgdg     | 123r     |  NULL |
+-----+----------+----------+-------+
6 rows in set (0.00 sec)


mysql> SELECT uid,username,password,state FROM user WHERE password REGEXP "[:digit:]{3}";
+-----+----------+----------+-------+
| uid | username | password | state |
+-----+----------+----------+-------+
|   4 | test1    | 123      |     0 |
|   3 | fly      | 123      |     0 |
|   5 | test2    | 123      |     0 |
|   6 | test3    | 123      |     1 |
|   8 | dfdg     | d124     |  NULL |
|   9 | dgdg     | 123r     |  NULL |
+-----+----------+----------+-------+
6 rows in set (0.00 sec)

定位元字符：

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

示例:找出密码中以数字开头的记录：

mysql> SELECT uid,username,password,state FROM user WHERE password REGEXP "^[[:digit:]\.]";
+-----+----------+----------+-------+
| uid | username | password | state |
+-----+----------+----------+-------+
|   4 | test1    | 123      |     0 |
|   3 | fly      | 123      |     0 |
|   5 | test2    | 123      |     0 |
|   6 | test3    | 123      |     1 |
|   7 | 1        | 1        |     1 |
|   9 | dgdg     | 123r     |  NULL |
|  10 | gdg      | 1d23     |  NULL |
|  11 | dgdgh    | 1.23s    |  NULL |
|  12 | dgklds   | 2.31t    |  NULL |
+-----+----------+----------+-------+
9 rows in set (0.00 sec)

// 注意区别
mysql> SELECT uid,username,password,state FROM user WHERE password REGEXP "^[:digit:]\.";
+-----+----------+----------+-------+
| uid | username | password | state |
+-----+----------+----------+-------+
|  11 | dgdgh    | 1.23s    |  NULL |
|  12 | dgklds   | 2.31t    |  NULL |
+-----+----------+----------+-------+
2 rows in set (0.00 sec)

创建计算字段

Concat（）函数拼接字段
Concat（）拼接串，把多个串连接起来形成一个较长的串。各串之间用“，”分隔。
注:多数DBMS使用+或||来实现拼接,而mysql使用Concat（）函数
将用户名按用户名（用户id）拼接出来：

mysql> SELECT Concat(username,'(',uid,')') FROM user ORDER BY uid DESC LIMIT 4;
+------------------------------+
| Concat(username,'(',uid,')') |
+------------------------------+
| test6(24)                    |
| test5(23)                    |
| test4(22)                    |
| kgdlksgj(21)                 |
+------------------------------+
4 rows in set (0.00 sec)

RTrim( )函数：删除值右边的所有空格

mysql> SELECT Concat(Rtrim(username),'(',uid,')') FROM user ORDER BY uid DESC LIMIT 4;
+-------------------------------------+
| Concat(Rtrim(username),'(',uid,')') |
+-------------------------------------+
| test6(24)                           |
| test5(23)                           |
| test4(22)                           |
| kgdlksgj(21)                        |
+-------------------------------------+
4 rows in set (0.00 sec)

LTrim( )函数去掉串左边的空格，Trim( )函数去掉串两边的空格；

使用别名：
别名（alias）用AS关键字赋予，使用别名能让客户机更好的使用数据，别名有时也叫导出列；

mysql> SELECT Concat(username,'(',uid,')')
    -> AS uinfo
    -> FROM user LIMIT 4;
+----------+
| uinfo    |
+----------+
| test1(4) |
| fly(3)   |
| test2(5) |
| test3(6) |
+----------+
4 rows in set (0.00 sec)