mysql查询语句

目录

特点

分类

二、常见函数

分组查询

 连接查询（多表查询）

99语法

分页查询

子查询

联合查询

---

 

基本语法：select 查询列表 from 表名;

特点

1、查询列表控又是：表中的字段、常量值、表达式、函数

2、查询的结果是一个虚拟表格

 

1、查询单个字段

SELECT 字段名 FROM 表名;

2、多个字段：上面的字段用，隔开

所有字段 字段名改成*

3、查询常量值 select 常量值;字符型加”

查询表达式select 表达式;

查询函数 select 函数名;

4、起别名 用于便于理解或同名情况

例如：

SELECT last_name AS 姓,first_name AS 名 FROM employees;

将lastname取名为姓firstname取名为名

5、去重SELECT DISTINCT …. FROM ….;

6、mysql中的+只能做运算符若其中一方是字符则会转换成数值再相加，若无法转换则会当做0来相加，若有一方是null则相加为null。“着重号用于区分

7、拼接函数CONCAT 拼接多个字段用例：

SELECT CONCAT(last_name,first_name) ……

若拼接字段中有null则会全显示null

IFNULL函数 IFNULL(字段名,0) 当字段为null时返回0。

8、显示表的结构DESC 表名;

9、条件查询语法：

SELECT

查询列表

FROM

表名

WHERE

筛选条件;

10、排序查询

ORDER BY

排序列表 DESC; //DESC为降序、ASC为升序 不加则默认为ASC。

ORDER BY一般放在查询语句的最后面LIMIT除外，支持单个字段、多个字段、表达式、函数别名。

按多个字段排序 在ORDER BY后面的字段用,隔开。

小知识：LENGTH（）函数是取字符长度，可以使用ORDER BY和LENGTH来进行字符长度排序。

分类

1、按表达式筛选

条件运算符： > < = != <> >= <=

!=建议用 <>

2、按逻辑表达式

逻辑运算符： && || ! and or not(与条件相反)

3、模糊查询 like 、 between and 、 in 、 is null

like 用法： like ‘%a%’;(包含a的字段)。

一般和通配符搭配使用%为任意多个字符包含0个字符，_为任意单个字符例如第三个字符为e第五个字符为a表达为’__e_a%’ 当需要查询带_的时在_的前面带上转义符\。也可在后面加上ESCAPE自定义一个转义符例如ESCAPE ‘$’ 将$定义为一个转义符

4、BETWEEN AND的用法：在。。。和。。。之间查询

可提高语句简洁度，且包含临界值。

例如employee_id BETWEEN 100 AND 120;

注意临界值不能调换顺序

5、IN的用法：用于判断某些字段值是否属于in列表中的某一项

IN(‘值’,’值’,’值’);

特点用IN 比用OR更简洁，IN列表的值类型必须一致或兼容，且不支持通配符因为是=判断。

6、IS NULL用法

字段 IS NULL;相反为IS NOT NULL

用于判断NULL值

注意IS只能搭配NULL或NOT NULL

7、安全等于<=>

例如salary<=>12000 用来判断=12000的salary

可以用来判断NULL

二、常见函数

1、单行函数

（1）、字符函数

#1 LENGTH() //返回字符长度，utf8下一个汉字占3个字节，一个字母占1个字节

#2 CONCAT拼接字符串

#3 UPPER()\LOWER()大小写变化

#4 SUBSTR()、SUBSTRING()功能一样截取字符串

例子SUBSTR(‘1234567’ ,5) 输出为567意为从5开始截取索引从1开始或者SUBSTR(‘1234567’ ,3,3)意为从3开始截取3个字符长度。

#5 INSTR()用于返回子串第一次在字符串中的起始索引位置

例如INSTR(‘123456’,’3)结果是3。

#6 TRIM()用于去掉前后空格

还能用于去除指定字符串

例如：TRIM(‘a’ FROM ‘a123a’)输出为123注意只能去前后字符不能去中间

#7 LPAD()指定长度的左填充

例如LPAD(‘123′,10,’*’) 为把123用*填充到10个字符123*******，若填字符长度填小于该字符串长度的数值则会截断为该长度。

例如LPAD(‘123′,2,’*’) 结果12。

#8 RPAD()右填充同理

#9 REPLACE(‘字符串’,’被代替’,’替代者’)

例如REPLACE(‘000456789000′,’000′,’123’)意为将该字符串中所有的000替换为123结果为123456789123。

（2）、数学函数

#1 ROUND四舍五入 ROUND(值,保留位数)。

#2 CEIL()向上取整返回>=该参数的最小整数。

#3 FLOOR()向下取整返回<=该参数的最大整数。

#4 TRUNCATE()截断

用法:TRUNCATE(小数,小数点后位数)例如(1.5666,1)意为从1.5后面全不要

#5 MOD()取余数例如MOD(10,3)结果为1注意被除数如果是正则结果为正被除数如果是负结果就为负与除数无关。

（3）、日期函数

#1 NOW() 返回当前系统如期+时间

#2 CURDATE()返回当前系统日期不包含时间

#3 CURTIME()返回时间不返回日期

#4 用于获取指定部分年、月、日、小时、分钟、秒。

YEAR(NOW())、MONTH(NOW())、MONTHNAME(NOW())用于获取月名。其他依次类推

#5 STR_TO_DATE:将字符通过指定的格式转换成日期

例如STR_TO_DATE(‘9-13-1999′,’%m-%d-%y’);2019-09-13

可用于指定的日期查询条件

#6 DATA_FORMAT:将日期转换成字符

例如DATE_FORMAT(‘2018/6/6′,’%y年%m月%d日’);18年06月06日

可用于将日期输出为指定格式

序号	格式符	功能
1	%Y	四位的年份
2	%y	2位的年份
3	%m	月份(01,02…11,12)
4	%c	月份(1,2…11,12)
5	%d	日(01,02…)
6	%H	小时(24小时制)
7	%h	小时(12小时制)
8	%i	分钟(00,01…59)
9	%s	秒(00,01…59)

格式符功能列表

补充：DATEDIFF(,)用于求相差时间（天数）。

（4）、其他函数

#1 VERSION()查看版本号

#2 DATABASE()查看当前库

#3 USER()查看当前用户

（5）、流程控制函数

#1 IF(expr1,expr2,expr3)其中expr1为判断条件2为如果满足条件3为如果不满住条件。为三元运算符。

#2 CASE用法：

CASE 要判断的值n或表达式

WHEN 常量1 THEN 要显示的值1或语句1

WHEN 常量2 THEN 要显示的值2或语句2

...

ELSE 要显示的值n或语句n

END CASE;

案例：修改工资

SELECT salary 原始工资,department_id,

CASE department_id

WHEN 30 THEN salary1.1 WHEN 40 THEN salary1.2

WHEN 50 THEN salary*1.3

ELSE

salary

END AS 新工资

FROM employees;

#3 CASE用法2

CASE //区别在于CASE后面没有表达式，功能类似于switch

WHEN 条件1 THEN要显示的值1或语句1;

WHEN 条件2 THEN要显示的值2或语句2;

…

ELSE 要显示的值n或语句n

END

应用案例工资评级

SELECT salary,

CASE

WHEN salary>20000 THEN ‘A’

WHEN salary>15000 THEN ‘B’

WHEN salary>10000 THEN ‘c’

ELSE

‘D’

END AS 工资级别

FROM employees;

 

2、分组函数 又称聚合函数、统计函数。

分类：

#1 SUM()求和

#2 AVG()平均值

#3 MAX()最大值

#4 MIN()最小值

#5 COUNT()计算个数

1、简单使用：

函数(执行的字段)，每个函数可以用,隔开一起使用也可以搭配其他函数使用。

2、参数支持的类型

SUM和AVG一般处理数值型

MAX、MIN、COUNT可以处理任何类型

注意：它们使用时都忽略了NULL值。

3、和DISTINCT搭配，进行去重后操作

4、COUNT的详细介绍

COUNT(*)、COUNT(一个常量)用于统计行数 //第二种的原理是在列表每行都添加一个该常量并且统计该列中常量的个数，一般填1。效率上讲在MYISAM存储引擎下COUNT(*)效率高，在INNODB存储引擎下差不多。

5、和分组函数一同查询的字段要求是GROUP BY后的字段。

分组查询

应用例子：查询每个部门的平均工资。

一、分组查询语法

SELECT 分组函数，列（要求出现在GROUP BY的后面）

FROM 表

[WHERE 筛选条件]

GROUP BY 分组的列表

[ORDER BY 子句] //[]中表示可有可无

注意：查询列表必须特殊，要求是分组函数和GROUP BY后出现的字段

1、分组前筛选：

WHERE用于分组前的原始表的条件判断

2、分组后筛选：

HAVING关键字用于分组后的条件判断语句，例如

GROUP BY …..

HAVING COUNT(*)>1

该判断条件在原始表中并没有*所以使用WHERE会报错。

注意

#1 分组函数要放在HAVING子句中。

#2 能用分组前筛选，就优先考虑使用分组。

二、按表达式或函数分组

1、GROUP BY 后面支持函数，且函数支持别名

例如用LENGTH()进行按长度分组。

三、按多个字段分组

在GROUP BY 后不同组用逗号“,”隔开。注意：顺序可以不同，显示结果按SELECT后面的顺序显示。且支持筛选、排序、别名。

 连接查询（多表查询）

错误情况(笛卡尔乘积现象)：

在同时查询两个表时用FROM和，的语句时会出现笛卡尔集的错误情况输出n*m行数据。

原因：没有有效的连接条件

避免：添加有效的连接条件

可以使用WHERE添加有效的条件，条件一定要和表一一对应，表中某一字段用:表.字段名表示。

在出现歧义的查询中可以在SELECT语句中加入限定也可以使用别名。

注意：FROM执行在前面，别名可以在FROM语句中起。

连接条件分类：

按年代分：

sql92标准（仅支持内连接）

sql99标准[推荐]：支持内连接+外连接（左外和右外）+交叉连接

按功能分：

内连接：

等值连接

非等值连接

自连接

外连接：

左外连接

右外连接

全外连接

交叉连接

一、sql92标准

1、等值连接

用WHERE条件语句进行等值连接，条件相同的匹配。

#1、为表起别名

提高语句的简洁度

区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定。

#2、两个表的顺序可以调换

#3、可以加筛选

多个筛选条件不能用多个WHERE要用AND

#4、可以使用分组

#5、可以加排序

#6、可以实现三表连接

特点：

1、多表等值连接的结果为多表的交集部分

2、n表连接 ，至少需要n-1个连接条件

3、多表的顺序没有需求

4、一般需要为表起别名

5、可以搭配前面介绍的所有子句使用，比如排序、分组、筛选。

2、非等值连接

连接条件WHERE并不按等值进行，而是按判断条件进行。

例如WHERE salary BETWEEN… AND…等关键字进行连接，从而出现一对多和多对一的结果。应用在评级等范围内连接。

3、自连接

“自己连接自己”

但在FROM语句后面给该表取不同的两个名，相当于将一张表分成两张一样的表并取不通的名。来作为两张表进行连接。

99语法

1、语法：

SELECT 查询列表

FROM 表1 别名【连接类型】

JOIN 表2 别名 ON 连接条件 //以上不同于92标准

【WHERE 筛选条件】

【GROUP BY 分组】

【HAVING 筛选条件】

【ORDER BY 排序列表】

2、分类（连接类型）：

内连接：INNER

外连接：

左外：LEFT[OUTER]

右外：RIGHT[OUTER]

全外：FULL[OUTER]

交叉连接：CROSS

其中又分为：等值、非等值、自连接

一、内连接例子

：SELECT 查询列表

FROM 表1 别名

INNER JOIN 表2 别名

ON连接条件

1、内连接的等值连接的特点

#1 可以添加排序、分组、筛选。

#2 INNER可以省略。

#3 筛选条件放在WHERE后面，连接条件放在ON 后面，提高了分离性 ，方便阅读。

#4 INNER JOIN连接和sql92语法的等值连接效果一样，都是多表的交集。

2、非等值连接

原理同92标准。

3、自连接

同92标准

二、外连接

原理：

两张表分为主、从表主表匹配从表里面的值，有对应的就连接，没有就写入null。

特点：

#1、外连接的查询结果为主表中的所有记录

如果从表中有和它匹配的，则显示匹配的值

如果没有匹配的，则显示null

外连接查询结果=内连接结果+主表中有而从表中没有的记录

#2、左外连接，LEFT JOIN左边的是主表

右外连接，RIGHT JOIN右边的是主表

#3、左外和右外交换两个表的顺序，可以实现同样的效果

注意：若将LEFT JOIN换成RIGHT JOIN则他们的表名也要交换。

#4、全外连接

全外连接就是互相映射，如果没有没有数据就用null填充。

全外连接=内连接+表1中有但表2没有的+表2中有但表1没有的

注意：mysql貌似不支持。

#5、交叉连接

两个表的笛卡尔积。

#6、sql92与sql99差别

功能：sql99支持的多

可读性：sql99实现连接条件和筛选条件分离，可读性高。

连接类型总结

 

资料来源b站BV12b411K7Zu

分页查询

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求

语法：

SELECT 查询列表

FROM 表

【JOIN TYPEJOIN 表2 //【】表示不一定有

ON 连接条件

WHERE 筛选条件

GROUP BY 分组字段

HAVING 分组后的筛选

ORDER BY 排序的字段】

LIMIT(分页关键词) offset,size;

//注：offset是要显示条目的起始索引（起始索引从0开始）

size是要显示的条目个数。

例如查询第11条到第25条则LIMIT 10,15

若不填入起始索引例如LIMIT 15则就是显示前15条。

特点：

1、LIMIT语句放在查询语句的最后，执行也是在最后。

2、公式

要显示的页数 page，每页的条目数size

SELECT 查询列表

FROM 表

LIMIT (page-1)*size ，size

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求

语法：

SELECT 查询列表

FROM 表

【JOIN TYPEJOIN 表2 //【】表示不一定有

ON 连接条件

WHERE 筛选条件

GROUP BY 分组字段

HAVING 分组后的筛选

ORDER BY 排序的字段】

LIMIT(分页关键词) offset,size;

//注：offset是要显示条目的起始索引（起始索引从0开始）

size是要显示的条目个数。

例如查询第11条到第25条则LIMIT 10,15

若不填入起始索引例如LIMIT 15则就是显示前15条。

特点：

1、LIMIT语句放在查询语句的最后，执行也是在最后。

2、公式

要显示的页数 page，每页的条目数size

SELECT 查询列表

FROM 表

LIMIT (page-1)*size ，size

子查询

含义：出现在其他语句中的SELECT语句，称为子查询或内查询。

外部的查询语句称为主查询或外查询。

分类：

按子查询出现的位置：

SELECT后面：

仅支持标量子查询

FROM后面：

支持表子查询

WHERE或HAVING后面：//重点

标量子查询

列子查询

行子查询//使用较少

EXISTS后面（相关子查询）：

表子查询

按结果集的行列数不同：

标量子查询（结果集只有一行一列）

列子查询（结果集只有一列多行）

行子查询（结果集有多行多列，一般为一行多列）

表子查询（结果集一般为多行多列）

一、WHERE或HAVING后面

特点：

#1、子查询放在小括号内

#2、子查询一般放在条件的右侧

#3、标量子查询，一般搭配着单行操作符使用

例如 >< >= <= = <>等

#4、列子查询，一般搭配着多行操作符使用

IN、ANY/SOME、ALL

#5、子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果。

1、标量子查询

基本语法：

SELECT *

FROM employees

WHERE salary>(

SELECT salary // 从小括号开始为标量子查询

FROM employees

WHERE last_name=’Abel’

);

也就是先分步写在嵌套。

例如查询工资最少的员工的信息可以

第一步：查询公司最低的工资

第二步查询所要信息，并要求salary=第一步

将第一步的代码写入第二步WHERE后的小括号中。

注意：非法使用标量子查询的情况：

子查询的结果不是一行一列。

2、列子查询（多行子查询）

常见多行操作符：

操作符	含义
IN/NOT IN	等于列表中的任意一个
ANY|SOME	和子查询返回的某一个值比较
ALL	和子查询返回的所有值比较

方法相同，条件语句要使用多行操作符，因为子结果不再是一行一列。

3、行子查询（一行多列或多行多列） //使用较少

方法相同，但是是把多个字段当做一行来使用，通俗来讲就是多对多的匹配。

一个例子：

SELECT *

FROM employees

WHERE (employee_id,salary)=( //两个条件

SELECT MIN(employee_id),MAX(salary) //两个字段

FROM employees

);

二、SELECT后面

子查询只能是一行一列，用于查询后需要额外一列的查询。

语法SELECT ..,(

SELECT ….

)…

三、FROM后面

将子查询结果充当一张表，要求必须起别名

FROM(

SELECT …

) AS …

四、EXISTS后面（相关子查询）

语法: EXISTS(完整的查询语句)

结果：（子查询表中有记录）1或0（无记录）

也就是存在就为1。

例如可以使用在查询有员工的部门名中。

联合查询

UNION 联合、合并：将多条查询语句的结果合并成一个结果

语法：

查询语句1

UNION

查询语句2

UNION

…

应用场景：查询的结果来自多个表并且没有连接关系，但查询的信息一致。

特点：

1、要求多条查询语句的查询列数是一致的。

2、要求多条查询语句的每一列的类型和顺序最好一致。

3、UNION关键字默认去重，如果使用UNION ALL可以包含重复项。