MySQL基础总结

1 基础增删改查语法

添加： insert into <表名> (指定要添加数据的字段名1,字段2…) values(‘对应数据1’,‘数据2’,…)
删除：DELETE FROM 表名称 WHERE 列名称 = 值
修改：UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值-----（指定的条件）
查询：select <字段名…> from <表名> where <条件>
给查询出的字段定义别名：select <字段名…> as 别名 from <表名> where <条件>

---

2 查看mysql数据库的端口号

show global variables like ‘port’;

---

3 mysql权限问题

为’root’@'%'授权

grant all privileges on . to ‘root’@‘%’ identified by “.”;
flush privileges;

---

4 查询为空数据: IS NULL

• mysql 查询 为空数据： is null；

select 字段名 from 表名 where 要查询的字段名 is null ；

---

5 查询不为空数据：IS NOT NULL

• MySQL查询不为空数据

select 字段名 from 表名 where 要查询的字段名 is not null ；

---

4 排序查询: order by

• MySQL排序查询【正序asc、倒序desc】：正序order by asc 倒序order by desc；

正序 select 字段名 from 表名 where 条件 order by 要查询的字段名 asc ;
倒序 select 字段名 from 表名 where 条件 order by 要查询的字段名 desc;

---

5 分组查询 group by

• mysql分组查询 group by; 关键字可以根据一个或多个字段对查询结果进行分组。
  group by 关键字可以和 group_concat() 函数一起使用。 group_concat() 函数会把每个分组的字段值都显示出来。

SELECT GROUP_CONCAT(分组字段对应内容字段),分组字段 FROM 表名 GROUP BY 分组字段

---

6 分组查询 :GROUP BY 与 WITH ROLLUP

• mysql分组查询 :GROUP BY 与 WITH ROLLUP;
  WITH POLLUP 关键字用来在所有记录的最后加上一条记录，这条记录是上面所有记录的总和，即统计记录数量。

  SELECT GROUP_CONCAT(分组字段对应内容字段),分组字段 FROM 表名 GROUP BY 分组字段 WITH ROLLUP;

---

7 范围查询：BETWEEN AND

• MySQL 范围查询：BETWEEN AND; 用来判断字段的数值是否在指定范围内。
  BETWEEN AND 需要两个参数，即范围的起始值和终止值。如果字段值在指定的范围内，则这些记录被返回。如果不在指定范围内，则不会被返回。
  NOT BETWEEN AND 即为不查询此范围

select 字段名,字段名… from 表名 where 要查询范围的字段名 between 范围的起始值 and 范围的终止值

---

8 模糊查询 :LIKE

• LIKE 关键字主要用于搜索匹配字段中的指定内容。
  LIKE 关键字支持百分号“%”和下划线“_”通配符。

查询表中所有姓张的人： select * from 表名 where 要模糊查询的字段名 like ‘张%’

---

9 MySQL使用 DISTINCT 过滤重复数据—去重

• distinct 关键字的主要作用就是对数据表中一个或多个字段重复的数据进行过滤，只返回其中的一条数据给用户。

  SELECT DISTINCT 字段名 FROM 表名;

其中，“字段名”为需要消除重复记录的字段名称，多个字段时用逗号隔开。

---

10 MySQL 限制查询结果的条数：LIMIT

• 当数据表中有大量数据时，一次性查询出表中的全部数据会降低数据返回的速度，同时给数据库服务器造成很大的压力。这时就可以用 LIMIT关键字来限制查询结果返回的条数。

  • LIMIT 是 MySQL 中的一个特殊关键字，用于指定查询结果从哪条记录开始显示，一共显示多少条记录。

  • LIMIT 关键字有 3 种使用方式，即指定初始位置、不指定初始位置以及与 OFFSET 组合使用。

指定初始位置：LIMIT 初始位置，记录数
其中，“初始位置”表示从哪条记录开始显示；“记录数”表示显示记录的条数。第一条记录的位置是 0，第二条记录的位置是 1。后面的记录依次类推。
注意：LIMIT 后的两个参数必须都是正整数。


                      select * from 表名 LIMIT 0，1；


不指定初始位置:LIMIT 记录数
LIMIT 关键字不指定初始位置时，记录从第一条记录开始显示。显示记录的条数由 LIMIT 关键字指定。
其中，“记录数”表示显示记录的条数。如果“记录数”的值小于查询结果的总数，则会从第一条记录开始，显示指定条数的记录。如果“记录数”的值大于查询结果的总数，则会直接显示查询出来的所有记录。


                      select * from 表名 LIMIT 3；


LIMIT和 OFFSET 组合使用:LIMIT 记录数 OFFSET 初始位置
参数和 LIMIT 语法中参数含义相同，“初始位置”指定从哪条记录开始显示；“记录数”表示显示记录的条数。


                      select * from 表名 LIMIT 3 OFFSET 1；

---

11 MySQL HAVING：过滤分组、having与where的区别

• 在 MySQL 中，可以使用 HAVING 关键字对分组后的数据进行过滤。

  • 使用 HAVING 关键字的语法格式如下：HAVING <查询条件>

  • 但是 WHERE 和 HAVING 关键字也存在以下几点差异：

    • 1，一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组。
    • 2，WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。
    • 3，WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤 。
    • 4，WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。
      也就是说，WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。
    • 5，WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名。

---

12 MySQL子查询

• 子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在 select、update 和 delete 语句中使用，而且可以进行多层嵌套。

  • 子查询在 WHERE 中的语法格式如下：where <表达式> <操作符> (子查询)

• 其中，操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。

  • （1）IN | NOT IN
    当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回值正好相反。
    （2）EXISTS | NOT EXISTS
    用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反。

  select  <父表要查询并显示结果的字段名> from <父表名> 
      where <与子表对应的字段名>  <操作符 IN 或者 EXISTS> 
   (select <子表要查询的字段名> from <子表名> where <子表条件字段名> = '<要查询的内容>')；
    
                      
              外层的 SELECT 查询称为父查询,圆括号中嵌入的查询称为子查询(子查询必须放在圆括号内).
              MySQL 在处理上例的 SELECT 语句时，执行流程为：先执行子查询，再执行父查询。
  

• 举个板栗

  • 使用子查询在 tb_students_info 表和 tb_course 表中查询学习 Java 课程的学生姓名，SQL 语句和运行结果如下。

    • SELECT name FROM tb_students_info
      WHERE course_id IN (SELECT id FROM tb_course WHERE course_name = ‘Java’);

  结果： ±------+
  | name |
  ±------+
  | Dany |
  | Henry |
  ±------+
  2 rows in set (0.01 sec)

---

MySQL子查询注意事项

12.MySQL子查询注意事项

1)子查询语句可以嵌套在 SQL 语句中任何表达式出现的位置

在 SELECT 语句中，子查询可以被嵌套在 SELECT 语句的列、表和查询条件中，即 SELECT 子句，FROM 子句、WHERE 子句、GROUP BY 子句和 HAVING 子句。

子查询在 SELECT 子句和 FROM 子句中的使用语法：

1. 嵌套在 SELECT 语句的 SELECT 子句中的子查询语法格式如下。

    SELECT (子查询) FROM 表名;
   

提示：子查询结果为单行单列，但不必指定列别名。

2.嵌套在 SELECT 语句的 FROM 子句中的子查询语法格式如下：

    SELECT * FROM (子查询) AS 表的别名;

注意：必须为表指定别名。一般返回多行多列数据记录，可以当作一张临时表。

2）只出现在子查询中而没有出现在父查询中的表不能包含在输出列中

多层嵌套子查询的最终数据集只包含父查询（即最外层的查询）的 SELECT 子句中出现的字段，而子查询的输出结果通常会作为其外层子查询数据源或用于数据判断匹配。

常见错误如下：

    SELECT * FROM (SELECT * FROM result);

这个子查询语句产生语法错误的原因在于主查询语句的 FROM 子句是一个子查询语句，因此应该为子查询结果集指定别名。正确代码如下:

    SELECT * FROM (SELECT * FROM result) AS Temp;

---

对MySQL子查询的简单改写优化

13.使用oracle或者其他关系数据库,数据库已经在子查询上做过优化，能够很好的选择驱动表执行，但mysql数据库子查询的处理上就不尽人意了。

例如：生产系统上就由于碰到了这个问题：

     select i_id, sum(i_sell) as i_sell 
     from table_data 
     where i_id in 
     (select i_id from table_data where Gmt_create >= '2011-10-07 00:00:00′) 
     group by i_id; 

（备注：sql的业务逻辑可以打个比方：先查询出10-07号新卖出的100本书，然后在查询这新卖出的100本书在全年的销量情况）。

这条sql之所以出现的性能问题在于mysql优化器在处理子查询的弱点，mysql优化器在处理子查询的时候，会将将子查询改写。
通常情况下，我们希望由内到外，先完成子查询的结果，然后在用子查询来驱动外查询的表，完成查询；
但是mysql处理为将会先扫描外面表中的所有数据，每条数据将会传到子查询中与子查询关联，如果外表很大的话，那么性能上将会出现问题；

针对上面的查询，由于table_data这张表的数据有70W的数据，同时子查询中的数据较多，有大量是重复的，这样就需要关联近70W次，大量的关联导致这条sql执行了几个小时也没有执行完成，所以我们需要改写sql：

 SELECT t2.i_id, SUM(t2.i_sell) AS sold 
 FROM 
 (SELECT distinct i_id FROM table_data WHERE gmt_create >= '2011-10-07 00:00:00′) t1, 
 table_data t2 
 WHERE 
 t1.i_id = t2.i_id GROUP BY t2.i_id; 

我们将子查询改为了关联，同时在子查询中加上distinct，减少t1关联t2的次数；

改造后，sql的执行时间降到100ms以内。

---

MySQL 左连接又称为外连接 left join

14. left join子句允许您从两个或多个数据库表查询数据。left join子句是select语句的可选部分，出现在from子句之后。

    左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

    left join语法：select <字段名> from <表1> left join <表2> [on子句]

语法说明如下:

• 字段名：需要查询的字段名称。
• <表1><表2>：需要内连接的表名。
• LEFT OUTER JOIN ：左连接中可以省略 OUTER 关键字，只使用关键字 LEFT JOIN。
• ON 子句：用来设置内连接的连接条件。

例子：假设要从两个表t1和t2查询数据。以下语句说明了连接两个表的LEFT JOIN子句的语法：

SELECT 
    t1.c1, t1.c2, t2.c1, t2.c2
FROM
    t1
        LEFT JOIN
    t2 ON t1.c1 = t2.c1;
    
    当使用LEFT JOIN子句将t1表加入t2表时，如果来自左表t1的行与基于连接条件(t1.c1 = t2.c1)的右表t2匹配，则该行将被包含在结果集中。
    如果左表中的行与右表中的行不匹配，则还将选择左表中的行并与右表中的“假”行组合。“假”行对于SELECT子句中的所有相应列都包含NULL值。
    换句话说，LEFT JOIN子句允许您从匹配的左右表中查询选择行记录，连接左表(t1)中的所有行，即使在右表(t2)中找不到匹配的行也显示出来，但使用NULL值代替。

视图化LEFT JOIN子句的工作原理：
两个圆圈之间的交点是两个表中匹配的行，左圆的剩余部分(白色部分)是t1表中不存在t2表中任何匹配行的行。 因此，左表中的所有行都包含在结果集中

---

MySQL右连接 right join

15.右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。

    select * from Menu1 m1 right join Menu2 m2 on m1.id = m2.id;

---

MySQL内连接 inner join

16.查询结果必须满足条件，返回同时满足两个表的部分。

内连接的语法格式如下：

SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]

语法说明如下:

• 字段名：需要查询的字段名称。

• <表1><表2>：需要内连接的表名。

• INNER JOIN ：内连接中可以省略 INNER 关键字，只用关键字 JOIN。

• ON 子句：用来设置内连接的连接条件。

    例如：select * from Menu1 m1 inner join Menu2 m2 on m1.id = m2.id;
  

---

MySQL REGEXP：正则表达式

17.正则表达式主要用来查询和替换符合某个模式（规则）的文本内容。例如，从一个文件中提取电话号码，查找一篇文章中重复的单词、替换文章中的敏感语汇等，这些地方都可以使用正则表达式。正则表达式强大且灵活，常用于非常复杂的查询。

MySQL 中，使用 REGEXP 关键字指定正则表达式的字符匹配模式，其基本语法格式如下：

    属性名 REGEXP '匹配方式'

其中，“属性名”表示需要查询的字段名称；“匹配方式”表示以哪种方式来匹配查询。“匹配方式”中有很多的模式匹配字符，它们分别表示不同的意思。下表列出了 REGEXP 操作符中常用的匹配方式。

选项	说明	例子	匹配值示例
^	匹配文本的开始字符	‘^b’ 匹配以字母 b 开头的字符串	book、big、banana、bike
$	匹配文本的结束字符	‘st$’ 匹配以 st 结尾的字符串	test、resist、persist
.	匹配任何单个字符	‘b.t’ 匹配任何 b 和 t 之间有一个字符	bit、bat、but、bite
*	匹配零个或多个在它前面的字符	‘f*n’ 匹配字符 n 前面有任意个字符 f	fn、fan、faan、abcn
+	匹配前面的字符 1 次或多次	‘ba+’ 匹配以 b 开头，后面至少紧跟一个 a	ba、bay、bare、battle
<字符串>	匹配包含指定字符的文本	‘fa’ 匹配包含‘fa’的文本	fan、afa、faad
[字符集合]	匹配字符集合中的任何一个字符	‘[xz]’ 匹配 x 或者 z	dizzy、zebra、x-ray、extra
[^]	匹配不在括号中的任何字符	‘[^abc]’ 匹配任何不包含 a、b 或 c 的字符串	desk、fox、f8ke
字符串{n,}	匹配前面的字符串至少 n 次	‘b{2}’ 匹配 2 个或更多的 b	bbb、bbbb、bbbbbbb
字符串{n,m}	匹配前面的字符串至少 n 次， 至多 m 次	‘b{2,4}’ 匹配最少 2 个，最多 4 个 b	bbb、bbbb

1.查询以特定字符或字符串开头的记录

字符 ^ 用来匹配以特定字符或字符串开头的记录。

    1.例如：在 tb_students_info 表中，查询 name 字段以“J”开头的记录，SQL 语句和执行过程如下。
    
    SELECT * FROM tb_students_info  WHERE name REGEXP '^J';

执行结果：

id	name	age	sex	height	course_id
4	Jane	22	男	162	3
5	Jim	24	女	175	2
6	John	21	女	172	4

2.查询以特定字符或字符串结尾的记录

字符 $ 用来匹配以特定字符或字符串结尾的记录。

1.例如：在 tb_students_info 表中，查询 name 字段以“y”结尾的记录，SQL 语句和执行过程如下。

SELECT * FROM tb_students_info  WHERE name REGEXP 'y$';

执行结果：

id	name	age	sex	height	course_id
1	Dany	25	男	160	1
3	Henry	23	女	185	1
7	Lily	22	男	165	4

3.替代字符串中的任意一个字符

字符 . 用来替代字符串中的任意一个字符。

例如：在 tb_students_info 表中，查询 name 字段值包含“a”和“y”，且两个字母之间只有一个字母的记录，SQL 语句和执行过程如下。

SELECT * FROM tb_students_info  WHERE name REGEXP 'a.y';

执行结果：

id	name	age	sex	height	course_id
1	Dany	25	男	160	1
3	Henry	23	女	185	1
7	Lily	22	男	165	4

4.匹配多个字符

字符 ++++ 和 + 都可以匹配多个该符号之前的字符。不同的是，+表示至少一个字符，而可以表示 0 个字符。

1.例如：在 tb_students_info 表中，使用 * 查询 name 字段值包含字母“T”，且“T”后面出现字母“h”的记录，SQL 语句和执行过程如下。

SELECT * FROM tb_students_info WHERE name REGEXP '^Th*';

执行结果：

id	name	age	sex	height	course_id
9	Thomas	22	女	178	5

2.例如：在 tb_students_info 表中，使用 + 查询 name 字段值包含字母“T”，且“T”后面出现字母“h”的记录，SQL 语句和执行过程如下。

SELECT * FROM tb_students_info WHERE name REGEXP '^Th+';

执行结果：

id	name	age	sex	height	course_id
9	Thomas	22	女	178	5

5.匹配指定字符串

正则表达式可以匹配字符串。当表中的记录包含这个字符串时，就可以将该记录查询出来。指定多个字符串时，需要用 | 隔开。只要匹配这些字符串中的任意一个即可。

1.例如：在 tb_students_info 表中，查询 name 字段值包含字符串“an”的记录，SQL 语句和执行过程如下。

SELECT * FROM tb_students_info WHERE name REGEXP 'an';

执行结果

id	name	age	sex	height	course_id
1	Dany	25	男	160	1
4	Jane	22	男	162	3
8	Susan	23	男	170	5

2.例如：在 tb_students_info 表中，查询 name 字段值包含字符串“an”或“en”的记录，SQL 语句和执行过程如下。

SELECT * FROM tb_students_info WHERE name REGEXP 'an|en';

执行结果

id	name	age	sex	height	course_id
1	Dany	25	男	160	1
2	Green	23	男	158	2
3	Henry	23	女	185	1
4	Jane	22	男	162	3
8	Susan	23	男	170	5

注意：字符串与 | 之间不能有空格。因为，查询过程中，数据库系统会将空格也当作一个字符，这样就查询不出想要的结果。

6.匹配指定字符串中的任意一个

使用方括号 [ ] 可以将需要查询的字符组成一个字符集合。只要记录中包含方括号中的任意字符，该记录就会被查询出来。例如，通过“[abc]”可以查询包含 a、b 和 c 等 3 个字母中任意一个的记录。

例如：在 tb_students_info 表中，查询 name 字段值包含字母“i”或“o”的记录，SQL 语句和执行过程如下。

SELECT * FROM tb_students_info WHERE name REGEXP '[io]';

执行结果

id	name	age	sex	height	course_id
5	Jim	24	女	175	2
6	John	21	女	172	4
7	Lily	22	男	165	4
9	Thomas	22	女	178	5
10	Tom	23	女	165	5
11	LiMing	22	男	180	7

从查询结果可以看到，所有返回记录的 name 字段值都包含字母 i 或 o，或者两个都有。

方括号 [ ] 还可以指定集合的区间。例如，“[a-z]”表示从 a~z 的所有字母；“[0-9]”表示从 0~9 的所有数字；“[a-z0-9]”表示包含所有的小写字母和数字；“[a-zA-Z]”表示匹配所有字符。

7.匹配指定字符以外的字符

[^字符集合] 用来匹配不在指定集合中的任何字符。

例如：在 tb_students_info 表中，查询 name 字段值包含字母 a~t 以外的字符的记录，SQL 语句和执行过程如下。

 SELECT * FROM tb_students_info WHERE name REGEXP '[^a-t]' ;

执行结果

id	name	age	sex	height	course_id
1	Dany	25	男	160	1
3	Henry	23	女	185	1
7	Lily	22	男	165	4
8	Susan	23	男	170	5

8.使用{n,}或者{n,m}来指定字符串连续出现的次数

字符串 {n,} 表示字符串连续出现 n 次；字符串{n,m} 表示字符串连续出现至少 n 次，最多 m 次。

例如，a{2,} 表示字母 a 连续出现至少 2 次，也可以大于 2 次；a{2,4} 表示字母 a 连续出现最少 2 次，最多不能超过 4 次。

例如：

在 tb_students_info 表中，查询 name 字段值出现字母‘e’ 至少 2 次的记录，SQL 语句如下：

 SELECT * FROM tb_students_info WHERE name REGEXP 'e{2,}';

执行结果

id	name	age	sex	height	course_id
2	Green	23	男	158	2

例如：

在 tb_students_info 表中，查询 name 字段值出现字符串“i” 最少 1 次，最多 3 次的记录，SQL 语句如下：

SELECT * FROM tb_students_info WHERE name REGEXP 'i{1,3}';

执行结果

id	name	age	sex	height	course_id
5	Jim	24	女	175	2
7	Lily	22	男	165	4
11	LiMing	22	男	180	7

---