MYSQL常用语句

一、distinct查询不重复的数据

1. 格式：select distinct <字段名> FROM <表名>;

• 字段名为需要消除重复记录的字段名称，多个字段时用逗号隔开。
• 如果有多个字段需要去重，则会对多个字段进行组合去重，即只有多个字段组合起来完全是一样的情况下才会被去重。

2. 常用场景：

• 查看去重字段有哪几种值[返回值]

eg. select distinct age,name from student;

• 查看去重字段有多少个值[返回数量]

eg. select count(distinct name,age) from student;

二、AS设置别名

1. 格式：表名> [AS] <别名>

             <字段名> [AS] <别名>

• as可以省略，省略后需要将字段名和别名用空格隔开
• limit限制查询结果条数

2. 作用：用于指定查询结果从哪条记录开始显示，一共显示多少条记录

3. 使用方式 — 指定初始位置：

• 格式：limit 初始位置，记录数
• 初始位置从0开始

4. 使用方式 — 不指定初始位置：

• 格式：limit 记录数
• 默认初始位置就是第1条记录
• 记录数>查询结果的总数，返回所有记录

5. 使用方式 — limit和offset组合使用

• 格式：limit 记录数 offset 初始位置 （和指定初始位置用途一致）

三、 order by 排序

1. 作用： 允许对查询结果针对某个字段进行排序

2. 语法格式： order by <字段名>[ASC|DESC]

• asc（默认值） ： 表示字段按升序排序
• desc： 按降序排序
• order by 关键词后可以跟子查询
• 如果字段值为空， 则当最小值去处理
• 如果指定多个字段， 则按照字段顺序从左到右依次排序

3. 单字段排序

eg. select * from student order by id desc;

4. 多字段排序

eg. select name,height from student order by hegiht desc,name asc;

四、 like 模糊查询

1. 语法格式： [not] like ‘ 字符串 ’

• 字符串： 可以是精确字符串， 也可以是包含通配符的字符串
• like 支持%和_两个通配符

2. 带” %” 通配符：“%” 代表任意长度的字符串， 包括 0。 （“%” 通配符可以到匹配任意字符， 但是不能匹配 NULL 的记录）

• eg.查找所有以字母” a“ 开头， 以字母” b” 结尾的学生姓名

select name from student where name like ‘ a%b’ ;

• eg.查找所有包含” a” 字母的学生姓名

select name from student where name like ‘ %a%’ ;

• eg.查找所有不以字母 T 开头的学生姓名

select name from student where name not like ‘ T%’ ;

3. 带“ _” 通配符：“ _” 只能代表单个字符， 字符的长度不能为 0

• eg.查找所有以字母” z” 结尾， 且” z“ 前面有 3 个字母的学生姓名

select name from student where name like ‘ ____z’ ;

4. ” binary“ 区分大小写： 默认 like 关键字匹配字符是不区分大小写的

• eg.查找所有以” t” 开头的学生姓名

select name from student where name like binary ‘ t%’ ;

5. ” \“ 转义符：如果查询内容包含通配符， 可以使用“ \” 转义符。 （ 可运用）

• eg.查询以” %“ 结尾的学生姓名

select name from student where name like ‘ %\%’ ;

五、 between and 范围查询

1. 作用： 可以判断值是否在指定的范围内

2. 场景： 查询年龄段、 工资水平等

3. 语法格式： [not] between 范围的起始值 and 范围的终止值

4. 查询年龄在 20 到 25 之间的学生姓名和年龄

select name,age from student where age between 20 and 25;

5. 查询年龄不在 20 到 25 之间的学生姓名和年龄

select name,age from student where age not between 20 and 25;

6.查询出生日期在 2020-12-01 和 2021-01-01 之间的学生信息

select name,birth_date from student where birth_data between‘ 2020-12-01’ and ‘ 2021-01-01’ ;

六、 is null 空值查询

1. 作用： 用来判断字段的值是否为空值（ NULL） （ 空值不同于 0， 也不同于空字符串）

2. 场景：

• 如果字段的值是空值， 则满足查询条件， 该记录将被查询出来；
• 如果字段的值不是空值， 则不满足查询条件。

3. 语法格式： IS [NOT] NULL

4. 查询 age 字段为空的记录

select * from student where age is null;

5. 查询 age 字段不为空的记录

select * from student where age is not null;

一、order by 排序

1. 作用：允许对查询结果针对某个字段进行排序

2. 语法格式：order by <字段名>[ASC|DESC]

2 asc（默认值）：表示字段按升序排序；

2 desc：按降序排序

2 order by关键词后可以跟子查询

2 如果字段值为空，则当最小值去处理

2 如果指定多个字段，则按照字段顺序从左到右依次排序

3. 单字段排序

eg. select * from student order by id desc;

4. 多字段排序

eg. select name,height from student order by hegiht desc,name asc;

二、like模糊查询

1. 语法格式：[not] like‘ 字符串 ’

2 字符串：可以是精确字符串，也可以是包含通配符的字符串

2 like支持%和_两个通配符

2. 带”%”通配符

2 “%”代表任意长度的字符串，包括0。（“%”通配符可以到匹配任意字符，但是不能匹配 NULL的记录。）

eg.查找所有以字母”a“开头，以字母”b”结尾的学生姓名

select name from student where name like ‘a%b’;

eg.查找所有包含”a”字母的学生姓名

select name from student where name like ‘%a%’;

eg.查找所有不以字母T开头的学生姓名

select name from student where name not like ‘T%’;

3. 带“_”通配符

2 “_”只能代表单个字符，字符的长度不能为 0

eg.查找所有以字母”z”结尾，且”z“前面有3个字母的学生姓名

select name from student where name like ‘____z’;

4. ”binary“区分大小写

2 默认like关键字匹配字符是不区分大小写的

eg.查找所有以”t”开头的学生姓名

select name from student where name like binary ‘t%’;

5. ”\“转义符

2 如果查询内容包含通配符，可以使用“\”转义符。（可运用）

eg.查询以”%“结尾的学生姓名

select name from student where name like ‘%\%’;

三、between and范围查询 

1. 作用：可以判断值是否在指定的范围内

2. 场景：查询年龄段、工资水平等

3. 语法格式：[not] between 范围的起始值 and 范围的终止值

4. 查询年龄在20到25之间的学生姓名和年龄

select name,age from student where age between 20 and 25;

5. 查询年龄不在20到25之间的学生姓名和年龄

select name,age from student where age not between 20 and 25;

6. 查询出生日期在2020-12-01和2021-01-01之间的学生信息

select name,birth_date from student where birth_data between ‘2020-12-01’and ‘2021-01-01’;

四、is null空值查询

1. 作用：用来判断字段的值是否为空值（NULL）（空值不同于 0，也不同于空字符串）

2. 场景：

2 如果字段的值是空值，则满足查询条件，该记录将被查询出来；

2 如果字段的值不是空值，则不满足查询条件。

3. 语法格式：IS [NOT] NULL

4. 查询age字段为空的记录

select * from student where age is null;

5. 查询age字段不为空的记录

select * from student where age is  not null;

五、group by分组查询

1. 作用：可以根据一个或者多个字段对查询结果进行分组

2. 语法格式： group by <字段名>  （多个字段时用逗号隔开）

3. 单字段分组使用：查询结果会只显示每个分组的第一条记录

4. 多字段分组使用：

2 先按照第一个字段分组，如果第一个字段有相同值，则把分组结果再按第二个字段进行分组，以此类推；

2 如果第一个字段每个值都是唯一的，则不会按照第二个字段再进行分组了。

5. group by+group_concat()_函数：该函数会把每个分组的字段值都显示出来。

6. group by+聚合函数

2 count() 统计记录的条数、sum() 字段值的总和、max()、min()、avg()

eg. select sex,count(sex) from student group by sex;

eg. select count(*) from test group by department;

eg. select sum(age) from test group by department;

7. group by + with rollup函数

2 用来在所有记录的最后加上一条记录，显示上面所有记录每个字段的总和

eg. select sum(age) from test group by department with rollup;

六、having过滤分组

1. 语法格式：having <查询条件>

2 使用 HAVING 关键字可以对分组后的数据进行过滤，根据前面已经查询出的字段进行过滤。

2 WHERE 用于过滤数据行，根据数据表中的字段直接进行过滤，WHERE 查询条件中不可以使用聚合函数。

2. 使用having关键字查询表中身高大于150的学生姓名和性别

select name,sex from student having height > 150;  

报错：“having子句”中的列“height”未知”

3. 使用where关键字查询表中身高大于150的学生姓名和性别

select name,sex from student where height > 150;  

4. 根据 height 字段对表中的数据进行分组，查询分组后平均身高大于150的学生姓名、姓名和身高

select group_concat(name),sex,height from student group by height having avg(height)>150;

七、多表连接

八、inner join内连接

1. 作用：取两张表相互匹配到的数据，取交集

2. 语法格式：select <字段名> from <表1> inner join <表2> [on 子句]

2 on子句：用来设置内连接的连接条件。

2 可以省略 INNER 关键字，只用关键字 JOIN。

3. 查询学生姓名和相对应的课程名称

select s.name c.name from student as s join course as c on s.id =c.id;

l 在多表查询的时候，字段名都需要通过表名指定  表名.字段名

九、left/right外连接

1. 在使用外连接时，要分清查询的结果，是需要显示左表的全部记录，还是右表的全部记录

2 left join的主表是左表，从表是右表

2 right join的主表是右表，从表是左表

2. 语法格式：

select <字段名> from <表1> left outer join <表2> <on子句>

select <字段名> from <表1> right outer join <表2> <on子句>

2 outer可以省略

select s.name c.name from student s left join course c on s.id =c.id;

十、union全连接

1. 语法格式：[sql1] union [all | distinct] [sql2] union [all | distinct] [sql3] ...

2 all可选参数，返回所有结果集；

2 distinct可选参数，删除结果集中重复的数据（默认union删除重复数据）

2. union all使用

select s.name c.name from student s left join course c on s.id =c.id;

union all /union(去重)

select s.name c.name from student s right join course c on s.id =c.id;

2 使用union连接的多条sql，每个sql查询的结果集的字段名称要一致，并且每条sql指定的字段顺序最好一致，否则可能会出现数据错乱的问题。

2 使用union连接查询的结果集的字段顺序会以第一条Sql查询出来的字段顺序为基准。

十一、in/exists子查询

1. 定义：将一个sql查询语句嵌套在另一个sql查询语句中

2. 作用：通过子查询可以实现多表查询

3. 语法格式：where <表达式><操作符>（子查询）

2 操作符可以是比较运算符(=运算符、<>运算符)、in、not in、exists、not exists

4. 比较运算符

2 查询没有学习java课程的学生姓名

select name from student where id <>(select id from course where name =’java‘);

5. in | not in    相等/不相等

当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回值正好相反。

6. exists| not exists   存在/不存在

用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反。

7. exists关键词+and/or使用

2 查询课程表中是否存在id=1的课程，如果存在，就查询出学生表中age字段大于25的记录。

select * from student where age>25 and exists(select name from course where id=1);

8. 子查询可以嵌套在sql语句中任何表达式出现的位置

2 select <子查询> from <表名> where <查询条件>

2 select <字段> from <子查询> as <别名> where <查询条件>

2 select <字段> from <表名> where <子查询>

2 如果<表名>嵌套的是子查询，必须给表指定别名

十二、explain

1. 作用：可以用来查询语句或是表结构的性能瓶颈。可以查看：

2 表的读取顺序

2 数据读取操作的操作类型

2 哪些索引可以使用

2 哪些索引被实际使用

2 表之间的引用

2 每张表有多少行被优化器查询

2. 语法格式：explain select * from table_name limit 1000000,100

2 type：查询使用类型  system > const > eq_ref > ref > range > index > all

2 possible_keys： 查询涉及到的字段上若存在索引就会列出，但不一定会被用到

2 key：实际查询中用到的索引

2 rows：查询数据时扫描的行数，越少越好

十三、分析函数

1. count() over()  ：统计分区中各组的行数。

l partition by 可选  -- 分组计数

l order by 可选      -- 递加计数

2. sum() over()  ：统计分区中记录的总和，partition by 可选，order by 可选

3. avg() over()  ：统计分区中记录的平均值，partition by 可选，order by 可选

4. min() over() ：统计分区中记录的最小值，partition by 可选，order by 可选

5. max() over() ：统计分区中记录的最大值，partition by 可选，order by 可选

6. rank() over()  ：跳跃排序，partition by 可选，order by 必选

7. dense_rank() ：连续排序，partition by 可选，order by 必选

8. row_number() over() ：排序，无重复值，partition by 可选，order by 必选

9. ntile(n) over() ：partition by 可选，order by 必选

  n表示将分区内记录平均分成n份，多出的按照顺序依次分给前面的组

10. first_value() over() ：取出分区中第一条记录的字段值，partition by 可选，order by 可选

11. last_value() over() ：取出分区中最后一条记录的字段值，partition by 可选，order by 可选

MYSQL常用语句

原文：https://www.cnblogs.com/sea1214/p/14820861.html